Finish MySQL (all) 2 in half a day

Keywords: MySQL less SQL

;

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

  1. 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
  1. 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)
  1. 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)
  1. 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)

Finish MySQL (all) 1 in half a day

Posted by pella.d on Wed, 17 Jun 2020 23:46:05 -0700