半天搞定MySQL（全）二（MySQL数据库教程）

半天搞定MySQL（全）

半天搞定MySQL（全）一
半天搞定MySQL（全）三
半天搞定MySQL（全）四
半天搞定MySQL（全）五
半天搞定MySQL（全）六
半天搞定MySQL（全）七（终章）

博主用的是8.0版本的MySQL，储存引擎是InnoDB，关于InnoDB这里不详细解释，需要的话推荐了解这篇博文（或者自行百度）https://www.jianshu.com/p/519fd7747137

• 目录
• MySQL增删改查操作
• where
• limit
• having
• like 模糊查询
• UNION 操作符
• order by
• group by

3A. MySQL增删改查操作

1. 插入数据

mysql> insert into city_journey(journey_ID,city_name,times,data)
    -> values(1,"海南",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,"夏威夷",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

2. 查询数据

SELECT column_name,column_name FROM table_name
[WHERE Clause]
[LIMITN]
[ OFFSET M]
select * : 返回所有记录
limit N : 返回 N 条记录
offset M : 跳过 M 条记录, 默认 M=0, 单独使用似乎不起作用
limit N,M : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录

mysql> select* from city_journey;
+------------+-----------+-------+------------+
| journey_ID | city_name | times | data       |
+------------+-----------+-------+------------+
|          1 | 海南      |     2 | 2020-06-18 |
|          2 | 夏威夷    |     1 | 2020-09-09 |
|          3 | 上海      |     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)

分页及排序查询

mysql> select* from city_journey order by data limit 0,5;
+------------+-----------+-------+------------+
| journey_ID | city_name | times | data       |
+------------+-----------+-------+------------+
|          1 | 海南      |     2 | 2020-06-18 |
|          3 | 上海      |     1 | 2020-06-18 |
|          2 | 夏威夷    |     1 | 2020-09-09 |
|          4 | Janpan    |     1 | 2021-06-04 |
|          5 | America   |     1 | 2022-06-06 |
+------------+-----------+-------+------------+
5 rows in set (0.00 sec)

where子句

查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。 你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。 WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。
执行顺序
select –>where –> group by–> having–>order by

操作符	描述
=	等号
<>, !=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于

mysql> select * from city_journey where times<=1;
+------------+-----------+-------+------------+
| journey_ID | city_name | times | data       |
+------------+-----------+-------+------------+
|          2 | 夏威夷    |     1 | 2020-09-09 |
|          3 | 上海      |     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 | 海南      |     2 | 2020-06-18 |
|          2 | 夏威夷    |     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)

3. updata跟新

同时更新一个或多个字段。
在 WHERE 子句中指定任何条件。
在一个单独表中同时更新数据。

mysql> update city_journey set times=2 where city_name='夏威夷';
Query OK, 1 row affected (0.01 sec)

批量修改某个字段

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 | 海南      |     2 | 2021-06-18 |
|          2 | 夏威夷    |     2 | 2021-09-09 |
|          3 | 上海      |     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)

4. 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子句

‘%a’ //以a结尾的数据
‘a%’ //以a开头的数据
‘%a%’ //含有a的数据
‘_a’ //三位且中间字母是a的(两边都是下划线)
‘_a’ //两位且结尾字母是a的

‘a_’ //两位且开头字母是a的

like 模糊查询

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 操作符

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

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    |
| 上海      |
| 东北      |
| 北京      |
| 夏威夷    |
| 新疆      |
| 海南      |
| 香港      |
+-----------+
10 rows in set (0.00 sec)

排序
order by

使用 asc 或 desc 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。

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 | 夏威夷    |     1 | 2020-09-09 |
|          1 | 海南      |     2 | 2020-06-18 |
|          9 | 香港      |     1 | 2020-06-18 |
|         10 | 香港      |     1 | 2020-06-18 |
+------------+-----------+-------+------------+
7 rows in set (0.00 sec)

分组
group by

mysql> select * from city_journey group by data;
+------------+-----------+-------+------------+
| journey_ID | city_name | times | data       |
+------------+-----------+-------+------------+
|          1 | 海南      |     2 | 2020-06-18 |
|          2 | 夏威夷    |     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

实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

mysql> select coalesce(city_name,'总数'),sum(data) as sameday from city_journey group by city_name with rollup;
+----------------------------+-----------+
| coalesce(city_name,'总数') | sameday   |
+----------------------------+-----------+
| America                    |  20220606 |
| Iceland                    |  20230605 |
| Janpan                     |  20210604 |
| 夏威夷                     |  20200909 |
| 海南                       |  20200618 |
| 香港                       |  40401236 |
| 总数                       | 141464578 |
+----------------------------+-----------+
7 rows in set (0.01 sec)

半天搞定MySQL（全）一
半天搞定MySQL（全）三
半天搞定MySQL（全）四
半天搞定MySQL（全）五
半天搞定MySQL（全）六
半天搞定MySQL（全）七（终章）