[MySQL] 基础知识储备

[renjie-run]

1.常用数据类型

• INT，存储整数
• VARCHAR，存储字符，可以指定长度
• FLOAT/DOUBLE，存浮点型数字
• DATE：存日期 2023-05-27
• TIME：存时间 10:13
• DATETIME：存日期和时间 2023-05-27 10:13
• TIMESTAMP 存日期时间，会转为中央时区 UTC 的时间来存储

[renjie-run]

2.常用查询语法和函数

database 相关

创建 database

CREATE DATABASE `test-mysql`;

删除 database

DROP DATABASE `test-mysql`;

切换 database

USE `test-mysql`;

table 相关

创建表

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Id',
    name VARCHAR(255) NOT NULL COMMENT '学生名',
    gender VARCHAR(255) NOT NULL COMMENT '性别',
    age INT NOT NULL COMMENT '年龄',
    class VARCHAR(255) NOT NULL COMMENT '班级名',
    score INT NOT NULL COMMENT '分数'
) CHARSET=utf8mb4

删除表

DROP TABLE `student`;

清空表

TRUNCATE `student`;

增加数据

INSERT INTO student (name, gender, age, class, score)
    VALUES 
        ('张三', '男', 18, '一班', 90),
        ('李四', '女', 19, '二班', 85),
        ('王五', '男', 20, '三班', 70),
        ('赵六', '女', 18, '一班', 95),
        ('钱七', '男', 19, '二班', 80),
        ('孙八', '女', 20, '三班', 75),
        ('周九', '男', 18, '一班', 85),
        ('吴十', '女', 19, '二班', 90),
        ('郑十一', '男', 20, '三班', 60),
        ('王十二', '女', 18, '一班', 95),
        ('赵十三', '男', 19, '二班', 75),
        ('钱十四', '女', 20, '三班', 80),
        ('孙十五', '男', 18, '一班', 90),
        ('周十六', '女', 19, '二班', 85),
        ('吴十七', '男', 20, '三班', 70),
        ('郑十八', '女', 18, '一班', 95),
        ('王十九', '男', 19, '二班', 80),
        ('赵二十', '女', 20, '三班', 75);

修改数据

UPDATE `student` SET age= 18 WHERE (`id` = 2);

删除数据

DELETE FROM `student` WHERE (`id` = 10);

查询数据

查询所有

SELECT * FROM `student`;

所有数据如下，后续数据都将在此基础上进行操作

查询指定列

SELECT name, score FROM `student`;

使用 AS 重命名查询的列

SELECT name AS 姓名, score AS 分数 FROM `student`;

使用条件查询

单个条件

SELECT name, score, gender FROM `student` WHERE gender = '男';

使用 AND/OR 多条件

SELECT name, score, gender FROM `student` WHERE gender = '男' AND score >= 80;

SELECT name, score, gender FROM `student` WHERE gender = '男' OR score >= 80;

模糊查询

SELECT name, score, gender FROM `student` WHERE name LIKE '郑%'; # 模糊查询

SELECT name, score, gender FROM `student` WHERE name NOT LIKE '%郑%'; # 不匹配模糊查询

使用 in 集合查询

SELECT name, score, class FROM `student` WHERE class IN ('一班', '二班'); # 查询在集合中的

SELECT name, score, class FROM `student` WHERE class NOT IN ('一班', '二班'); # 查询不在集合中的

使用 between and 区间查询

SELECT name, score, age FROM `student` WHERE age BETWEEN 19 AND 20;

分页查询

SELECT * FROM `student` LIMIT 0, 5; # 0 表示 start，5 表示 offset

排序

SELECT * FROM `student` ORDER BY age DESC, score ASC;

分组统计

分组统计使用的是 GROUP BY 语法。一般会结合一些内置方法来使用。

求平均数 AVG

SELECT class AS 班级, AVG(score) AS 平均成绩 FROM `student` GROUP BY class ORDER BY 平均成绩 DESC;

统计总数 COUNT

SELECT class AS 班级, COUNT(*) AS count FROM `student` GROUP BY class ORDER BY count DESC;

求和 SUM

SELECT class, SUM(score) AS total FROM `student` GROUP BY class;

最大值 MAX

SELECT class, MAX(score) AS total FROM `student` GROUP BY class;

最小值 MIN

SELECT class, MIN(score) AS total FROM `student` GROUP BY class;

使用 HAVING 进行分组统计中的过滤

SELECT class AS 班级, AVG(score) AS 平均成绩 FROM `student` GROUP BY class HAVING 平均成绩 > 90;

字符函数

字符连接 CONCAT

SELECT CONCAT('a', name, 'c') FROM `student`;

字符截取 SUBSTR

SELECT SUBSTR(name, 2， 3) FROM `student`;

字符长度 LENGTH

SELECT LENGTH(name) FROM `student`;

将字符转为大写 UPPER

SELECT UPPER('aa'); # "AA"

将字符转为大写 LOWER

SELECT LOWER('AA'); # "aa"

数值函数

四舍五入 ROUND

SELECT ROUND(1.234567, 2);  # 1.23

向上取整 CEIL

SELECT CEIL(1.234567);  # 2

向下取整 FLOOR

SELECT FLOOR(1.234567);  # 1

取绝对值 ABS

SELECT ABS(-1.234567);  # 1.234567

取模 MOD

SELECT MOD(5, 2);  # 1

日期函数

日期 DATE

SELECT DATE('2024-10-16 22:06:03'); # 2024-10-16

时间 TIME

SELECT TIME('2024-10-16 22:06:03'); # 22:06:03

年 YEAR

SELECT YEAR('2024-10-16 22:06:03'); # 2024

月 MONTH

SELECT MONTH('2024-10-16 22:06:03'); # 10

日 DAY

SELECT DAY('2024-10-16 22:06:03'); # 16

条件函数

IF

SELECT name, score, if(score >= 60, 'pass', 'reject') AS res FROM student;

CASE

SELECT name, score, CASE WHEN score >=90 THEN '优秀' WHEN score >=60 THEN '良好' ELSE '差' END AS '档次' FROM student;

查询去重

SELECT DISTINCT class FROM `student`;

索引

这里假设分别有 user 和 id_card 两张表。

创建索引

INDEX 是建立索引，索引名是 card_id_idx，用于加速 user_id 的访问

CREATE UNIQUE INDEX `card_id_idx` on `id_card` (`id` ASC);

删除索引

DROP INDEX `card_id_idx` on `id_card`;

外键

创建外键

ALTER TABLE
  `id_card`
ADD
  CONSTRAINT `id_card_relation_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION;

删除外键

ALTER TABLE
  `id_card`
DROP
  FOREIGN KEY `id_card_relation_user`;

相关 SQL

base.sql.zip

[renjie-run]

3.关联查询：一对一

分别准备 user 和 id_card 两张表。其中 user 与 id_card 通过 user.id、id_card.user_id 保持一一对应。

user：

id_card：

使用 JOIN ON 查询

JOIN ON 其实默认就是 INNER JOIN ON。

SELECT user.id, name, id_card.id as card_id, card_name FROM user
  JOIN id_card ON user.id = id_card.user_id;

将 id_card 表中最后两行的 user_id 设置为空后，再次执行上面的查询。会发现少了两条数据，也就是刚才 user_id 设置为空的那两条。这是因为 INNER JOIN 是只返回两个表中能关联上的数据。

使用 RIGHT JOIN ON 查询

这里说明一个概念，在 FROM 后的是左表，JOIN 后的表是右表。

SELECT user.id, name, id_card.id as card_id, card_name
  FROM user
  RIGHT JOIN id_card ON user.id = id_card.user_id;

当使用 RIGHT JOIN 时，会额外返回右表中没有关联的数据。

使用 LEFT JOIN ON 查询

SELECT user.id, name, id_card.id as card_id, card_name
  FROM user
  LEFT JOIN id_card ON user.id = id_card.user_id;

当使用 LEFT JOIN 时，会额外返回左表中没有关联的数据。

相关 SQL

base2.sql.zip

[renjie-run]

4.关联查询：一对多

常见的一对多关系有：订单与商品、部门与员工之间等。

分别准备 department 与 employee 两张表。其中 department.id 与 employee.department_id 保持一对多的关系。

department：

employee：

使用 JOIN ON 查询

select department.id as department_id, department.name as department_name, employee.id as employee_id, employee.name as employee_name, employee.department_id as employee_department_id
  from department
  join employee on department.id = employee.department_id;

使用 LEFT JOIN ON 查询

select department.id as department_id, department.name as department_name, employee.id as employee_id, employee.name as employee_name, employee.department_id as employee_department_id 
  from department
  left join employee on department.id = employee.department_id;

使用 RIGHT JOIN ON 查询

select department.id as department_id, department.name as department_name, employee.id as employee_id, employee.name as employee_name, employee.department_id as employee_department_id 
  from department
  right join employee on department.id = employee.department_id;

相关 SQL

base3.sql.zip

[renjie-run]

5.关联查询：多对多

常见的多对多关系有：图书与标签、学生与课程、用户与角色之间等。通常，会添加一个中间表来存储两张表的关联关系。例如，这里通过文章、标签的关系来进行描述。

分别准备 article、tag、article_tag 三张表，article_tag 表中存储 article_id、tag_id 来分别关联对应的 article.id、tag.id。

article：

tag：

article_tag：

使用 JOIN ON 查询

select * from article a
  join article_tag act on a.id = act.article_id
  join tag t on t.id = act.tag_id;

这样就可以查到各个文章关联的所有标签了

相关 SQL

base4.sql.zip

[renjie-run]

6.复杂的查询

子查询

子查询一般用于较为复杂的查询情况。例如，

需求一

查找 student 表中，分数最高的学生的信息。在使用子查询之前复习下前面的简单查询。

首先，查询分数为 95（95 为最高分）的学生信息：

select * from student where score = 95;

然后，通过 SQL 查找出来最高分：

select max(score) from student; # 95

其实上面的两个查询结合一下就可以实现功能了，这也就是子查询：

select * from student where score =(select max(score) from student);

需求二

查询高于平均分的学生信息。同需求一的实现，这里使用求平均的语法。

select * from student where score > (select avg(score) from student);

其他的需求也类似，使用对应的语法即可。

注意，不光在 select 语句中可以使用子查询，在 update、insert、delete 里也同样的可以使用。

EXISTS/NOT EXISTS 查询

顾名思义，也就是根据是否存在的条件来查询的。

需求一

仅查询可匹配到员工的部门名称。

select name from department
  where exist (
    select department.id where department.id=employee.department_id
  );

需求二

针对需求一的情况进行反向查找。

select name from department
  where exist (
    select department.id where department.id=employee.department_id
  );

相关 SQL

base5.sql.zip

[renjie-run]

7.综合练习

基于 practice 数据库，创建 customers、orders、order_items 三张表，这些表的关系是一个顾客可以有多张订单，一个订单可以有多个订单项。三个表的情况如下

customers：

orders：

order_items：

注：这里订单项跟订单两张表的一些数据可能不太对应，例如，订单项的总金额与订单总金额不对应，后面的查询可能会有些困惑，这里可以先忽略。

练习 1：查询每个客户的订单总金额

select customers.name, sum(orders.total_amount) as total_amount from orders
  join customers on orders.customer_id = customers.id
  group by orders.customer_id;

根据订单总金额的从大到小顺序来看

select customers.name, sum(orders.total_amount) as total_amount from orders
  join customers on orders.customer_id = customers.id
  group by orders.customer_id
  order by total_amount desc;

查找订单总金额最高的前三个

select customers.name, sum(orders.total_amount) as total_amount from orders
  join customers on orders.customer_id = customers.id
  group by orders.customer_id
  order by total_amount desc
  limit 0, 3;

练习 2: 查询每个客户的订单总金额，并计算其占比

select customers.name, sum(orders.total_amount) as customer_total_amount, (select sum(orders.total_amount) from orders) as total_amount,
  CONCAT(ROUND(sum(orders.total_amount) / (select sum(orders.total_amount) from orders) * 100, 2), '%') as per
  from orders
  join customers on orders.customer_id = customers.id
  group by orders.customer_id;

这里主要是为了练习子查询等，先不考虑每次都算一遍总金额等的性能问题。

练习 3：查询每个客户的订单以及订单项

select customers.name, orders.order_date, orders.total_amount,
  order_items.product_name, order_items.quantity, order_items.price
  from customers
  join orders on customers.id = orders.customer_id
  join order_items on orders.id = order_items.order_id
  order by customers.name, orders.order_date;

这里主要练习了关联第三张表。

使用模糊查询某些用户的订单

基于上面的查询结果进行过滤

select customers.name, orders.order_date, orders.total_amount,
  order_items.product_name, order_items.quantity, order_items.price
  from customers
  join orders on customers.id = orders.customer_id
  join order_items on orders.id = order_items.order_id
  where customers.name like '张%'
  order by orders.order_date;

使用 BETWEEN AND 查询在一段日期内的订单

select customers.name, orders.order_date, orders.total_amount,
  order_items.product_name, order_items.quantity, order_items.price
  from customers
  join orders on customers.id = orders.customer_id
  join order_items on orders.id = order_items.order_id
  where orders.order_date between '2022-01-03' and '2022-01-05'
  order by orders.order_date;

查询每个用户鞋子的订单总额、订单总项数

这里主要练习 group by，附带练习 group_concat。

select customers.name, group_concat(order_items.product_name separator ', '), count(order_items.id) as order_count, sum(orders.total_amount) as total_amount
  from customers
  join orders on customers.id = orders.customer_id
  join order_items on orders.id = order_items.order_id
  where order_items.product_name like '%鞋%'
  group by customers.name
  order by total_amount desc
  limit 3;

练习4：修改某个用户的订单总金额（打 9 折）

update orders set orders.total_amount = orders.total_amount * 0.9 
  where orders.customer_id in (
    select id from customers where name = '王磊'
  );

修改前

修改后

相关 SQL

base6.sql.zip

[renjie-run]

8.并发问题

在数据库系统中，并发操作可能会引起以下几类常见问题:

• 丢失更新(Lost Update)：当两个或多个事务同时更新同一行数据时，由于每个事务都不知道其他事务的操作，会导致一些事务的更新被其他事务的更新覆盖
• 脏读(Dirty Read)：一个事务读取了另一个事务还未提交的更新数据。如果另一个事务回滚，那么第一个事务读取到的数据是脏数据
• 不可重复读(Non-Repeatable Read)：一个事务多次读取同一行数据，但每次读取的值不同，导致多次读取同一行出现不一致
• 幻读(Phantom Read)：一个事务按相同的查询条件重新读取数据，发现其他事务插入了满足其查询条件的新数据，导致多次查询结果不一致
• 死锁(Deadlock)：多个事务在争用锁资源时，由于请求锁的顺序不一致，导致事务陷入等待环路，若不解除，这些事务都将无法继续执行下去

主要的解决方案包括：锁机制、多版本并发控制(MVCC)、事务隔离级别等。应根据应用场景选择合适的机制来处理。

事务

我们在使用 update 更新数据后是无法撤销的，如果想支持撤销操作的话，此时就使用到事务了。

START TRANSACTION;

UPDATE order_items SET quantity=1 WHERE order_id=3;

UPDATE orders SET total_amount=200 WHERE id=3;

ROLLBACK;

COMMIT;

通过 START TRANSACTION 开启事务，在执行完修改操作后，如果想撤销操作，那么执行 ROLLBACK 即可，这样上面的两个修改操作就都可撤销掉了。

如果确定执行的修改操作，那么执行 COMMIT 即可。

回滚到某个节点

START TRANSACTION;

savepoint aaa;

UPDATE order_items SET quantity=1 WHERE order_id=3;

savepoint bbb;

UPDATE orders SET total_amount=200 WHERE id=3;

rollback to savepoint bbb;

通过 savepoint 节点名称 定义节点，然后通过 rollback to savepoint 节点名称 即可撤销到指定的节点。

查询当前的事务隔离级别

select @@transaction_isolation;

一般都使用默认的事务隔离级别。

相关 SQL

base7.sql.zip

[renjie-run]

9.视图、存储过程和函数