Finish MySQL in half a day (all)
Finish MySQL (all) 1 in half a day
The blogger uses MySQL version 8.0, and the storage engine is InnoDB. We don't explain InnoDB in detail here. If necessary, we recommend you to know This article Blog (or Baidu) https://www.jianshu.com/p/519fd7747137
3A. Add, delete, modify and query MySQL
- insert data
mysql> insert into city_journey(journey_ID,city_name,times,data) -> values(1,"Hainan",2,NOW()); Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> insert into city_journey(journey_ID,city_name,times,data) -> values(2,"Hawaii",1,'2020-9-9'); Query OK, 1 row affected (0.01 sec)
mysql> insert into city_journey(city_name,times,data) -> values("Janpan",1,'2021-06-04'),("America",1,'2022-6-6'),("Iceland",1,'2023-6-5'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0
- Query data
SELECT column_name,column_name FROM table_name
[WHERE Clause]
[LIMITN]
[ OFFSET M]
select * : Return all records
limit N : return N Records
offset M : skip M Records, default M=0, It doesn't seem to work alone
limit N,M : amount to limit M offset N , From the N Records start, return M Records
mysql> select* from city_journey; +------------+-----------+-------+------------+ | journey_ID | city_name | times | data | +------------+-----------+-------+------------+ | 1 | Hainan | 2 | 2020-06-18 | | 2 | Hawaii | 1 | 2020-09-09 | | 3 | Shanghai | 1 | 2020-06-18 | | 4 | Janpan | 1 | 2021-06-04 | | 5 | America | 1 | 2022-06-06 | | 6 | Iceland | 1 | 2023-06-05 | +------------+-----------+-------+------------+ 6 rows in set (0.00 sec)
Paging and Sorting Query
mysql> select* from city_journey order by data limit 0,5; +------------+-----------+-------+------------+ | journey_ID | city_name | times | data | +------------+-----------+-------+------------+ | 1 | Hainan | 2 | 2020-06-18 | | 3 | Shanghai | 1 | 2020-06-18 | | 2 | Hawaii | 1 | 2020-09-09 | | 4 | Janpan | 1 | 2021-06-04 | | 5 | America | 1 | 2022-06-06 | +------------+-----------+-------+------------+ 5 rows in set (0.00 sec)
where clause
In the query statement, you can use one or more tables, comma between tables, partition, and WHERE statement to set query conditions. You can specify any condition in the WHERE clause.
You can use AND OR to specify one OR more conditions. The WHERE clause can also be used for SQL DELETE OR UPDATE commands.
The WHERE clause is similar to the if condition in the program language, and reads the specified data according to the field value in the MySQL table.
Execution sequence
select –>where –> group by–> having–>order by
| Operator | describe |
|---|---|
| = | Equal sign |
| <>, != | Not equal to |
| > | greater than |
| < | less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
mysql> select * from city_journey where times<=1; +------------+-----------+-------+------------+ | journey_ID | city_name | times | data | +------------+-----------+-------+------------+ | 2 | Hawaii | 1 | 2020-09-09 | | 3 | Shanghai | 1 | 2020-06-18 | | 4 | Janpan | 1 | 2021-06-04 | | 5 | America | 1 | 2022-06-06 | | 6 | Iceland | 1 | 2023-06-05 | +------------+-----------+-------+------------+ 5 rows in set (0.00 sec)
group by mysql> select * from city_journey group by times; +------------+-----------+-------+------------+ | journey_ID | city_name | times | data | +------------+-----------+-------+------------+ | 1 | Hainan | 2 | 2020-06-18 | | 2 | Hawaii | 1 | 2020-09-09 | +------------+-----------+-------+------------+ 2 rows in set (0.00 sec)
having
mysql> select * from city_journey where times<=1 having city_name in ("Janpan","America","Iceland"); +------------+-----------+-------+------------+ | journey_ID | city_name | times | data | +------------+-----------+-------+------------+ | 4 | Janpan | 1 | 2021-06-04 | | 5 | America | 1 | 2022-06-06 | | 6 | Iceland | 1 | 2023-06-05 | +------------+-----------+-------+------------+ 3 rows in set (0.00 sec)
- Update updata
Update one or more fields at the same time.
Specify any conditions in the WHERE clause.
Update data in a single table at the same time.
mysql> update city_journey set times=2 where city_name='Hawaii'; Query OK, 1 row affected (0.01 sec)
Batch modify a field
mysql> update city_journey set data=replace(data,'2020','2021'); Query OK, 3 rows affected (0.01 sec) Rows matched: 6 Changed: 3 Warnings: 0 mysql> select * from city_journey; +------------+-----------+-------+------------+ | journey_ID | city_name | times | data | +------------+-----------+-------+------------+ | 1 | Hainan | 2 | 2021-06-18 | | 2 | Hawaii | 2 | 2021-09-09 | | 3 | Shanghai | 1 | 2021-06-18 | | 4 | Janpan | 1 | 2021-06-04 | | 5 | America | 1 | 2022-06-06 | | 6 | Iceland | 1 | 2023-06-05 | +------------+-----------+-------+------------+ 6 rows in set (0.00 sec)
- Delete delete
mysql> delete from city_spots where city_name="American"; Query OK, 1 row affected (0.01 sec)
mysql> delete from city_journey where journey_ID<=8; Query OK, 8 rows affected (0.01 sec)
like clause
'%a' //Data ending in a
'a%' //Data starting with a
'%a%' //Data with a
'a' //Three digits and the middle letter is a
'a' //Two digit and ending with a
'a' //Two digit with a starting letter
like fuzzy query
mysql> select * from city_journey where times<=1 having city_name like "_a%"; +------------+-----------+-------+------------+ | journey_ID | city_name | times | data | +------------+-----------+-------+------------+ | 4 | Janpan | 1 | 2021-06-04 | +------------+-----------+-------+------------+ 1 row in set (0.00 sec)
mysql> select * from city_journey where journey_ID>1 having city_name like '%a%'; +------------+-----------+-------+------------+ | journey_ID | city_name | times | data | +------------+-----------+-------+------------+ | 11 | Janpan | 1 | 2021-06-04 | | 12 | America | 1 | 2022-06-06 | | 13 | Iceland | 1 | 2023-06-05 | +------------+-----------+-------+------------+ 3 rows in set (0.00 sec)
UNION operator
The MySQL UNION operator is used to connect the results of more than two SELECT statements into a result set. Multiple SELECT statements delete duplicate data.
SELECT expression1, expression2, ... expression_n FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n FROM tables
[WHERE conditions];
mysql> select city_name from city_spots -> union -> select city_name from city_journey -> order by city_name; +-----------+ | city_name | +-----------+ | America | | Iceland | | Janpan | | Shanghai | | northeast | | Beijing | | Hawaii | | Xinjiang | | Hainan | | Hong Kong | +-----------+ 10 rows in set (0.00 sec)
sort
order by
Use the asc or desc keyword to set whether the query results are sorted in ascending or descending order. By default, it is arranged in ascending order.
mysql> select * from city_journey order by data desc; +------------+-----------+-------+------------+ | journey_ID | city_name | times | data | +------------+-----------+-------+------------+ | 13 | Iceland | 1 | 2023-06-05 | | 12 | America | 1 | 2022-06-06 | | 11 | Janpan | 1 | 2021-06-04 | | 2 | Hawaii | 1 | 2020-09-09 | | 1 | Hainan | 2 | 2020-06-18 | | 9 | Hong Kong | 1 | 2020-06-18 | | 10 | Hong Kong | 1 | 2020-06-18 | +------------+-----------+-------+------------+ 7 rows in set (0.00 sec)
grouping
group by
mysql> select * from city_journey group by data; +------------+-----------+-------+------------+ | journey_ID | city_name | times | data | +------------+-----------+-------+------------+ | 1 | Hainan | 2 | 2020-06-18 | | 2 | Hawaii | 1 | 2020-09-09 | | 11 | Janpan | 1 | 2021-06-04 | | 12 | America | 1 | 2022-06-06 | | 13 | Iceland | 1 | 2023-06-05 | +------------+-----------+-------+------------+ 5 rows in set (0.00 sec)
group by+ with rollup
Realize the same statistics (SUM,AVG,COUNT...) on the basis of grouping statistics.
mysql> select coalesce(city_name,'total'),sum(data) as sameday from city_journey group by city_name with rollup; +----------------------------+-----------+ | coalesce(city_name,'total') | sameday | +----------------------------+-----------+ | America | 20220606 | | Iceland | 20230605 | | Janpan | 20210604 | | Hawaii | 20200909 | | Hainan | 20200618 | | Hong Kong | 40401236 | | total | 141464578 | +----------------------------+-----------+ 7 rows in set (0.01 sec)