Day3 | 数据库操作DML
DML
DML（Data Manipulation Language），是通过对表中数据的插入、删除和修改等操作，实现对数据库的基本操作。 主要用途：对数据表进行数据插入、删除、修改、排序或检索操作。 操纵命令：INSERT、UPDATE、DELETE INSERT：把数据插入到数据表中。 UPDATE：修改数据记录。 DELETE：删除数据库中不必再继续保留的记录。
1、插入数据
形式一
INSERT INTO TABLE_NAME (字段名1,字段名2,.....) VALUES(数值1,数值2,....);
案例：
INSERT INTO user(user_id,user_name,age,remove,tel,sex)
VALUES(001,"A2Data",22,0,18511990011,0);形式二
INSERT INTO TABLE_NAME SET 字段名1 = 数值1, 字段名2 = 数值2,....;
案例：
INSERT INTO user SET user_id=001,user_name="A2Data",age=22,remove=0,tel=18511990011,sex=0;2、修改数据
UPDATE TABLE_NAME SET 字段名1 = 新数值1,字段名2 = 新数值2;
案例：
UPDATE USER SET age = 25 , sex = 1;3、解决重复记录的更新
使用on duplicate key update可以解决插入或更改重复主键，即当insert内容和原表内容存在重复记录时，执行update操作。
INSERT INTO table_name1 SELECT * FROM table_name2 ON DUPLICATE KEY UPDATE column_name = VALUES(column_name);注意：在使用on duplicate key update时，表中必须要存在至少一个unique key或者primary key。
案例：现有表v1和v2如下：
v1表与v2表同时存在id为005的行，且name字段值不一致。
我们使用on duplicate key update对v1表插入v2内容：
insert into v1 select name,tel,remove from v2 on duplicate key update name = values(name);查看执行后的v1表内容，可以看到id为005的记录，其name字段值已修改为v2的相应记录：
4、删除数据
方法一：DELETE
DELETE FROM TABLE_NAME
[WHERE 子句]
如不加WHERE子句则表中数据全部删除
案例：
DELETE FROM user;方法二：TRUNCATE
TRUNCATE优势：作为DDL语句，比DELETE运行速度更快，且使用资源更少
TRUNCATE TABLE TABLE_NAME
案例：
TRUNCATE TABLE user;5、数据库查询
5.1、基本查询语句
SELECT column_name FROM table_name
WHERE 条件语句
[LIMIT 限制行数]WHERE限制条件一览表：
限制条件类别 | 对应标识 | 备注 |
|---|---|---|
比较 | >、<、=、>=、<=、<> | |
集合 | in、not in | 无法获取null值 |
指定范围 | between and | 使用时需注意其结果包含边界值 |
空值判断 | is null、is not null | |
且 | and | |
或 | or |
案例：
SELECT user_id,user_name FROM user
WHERE age > 20 and tel IS NOT NULL
LIMIT 10;
别名
column_name AS 别名
table_name AS 别名注:WHERE子句中不可使用别名
GROUP BY：用于对查询结果进行数据分组
HAVING：用于对分组后的结果进行统计，其后可以跟聚合函数
案例：
user表记录了用户所在部门user_dept，筛选出部门平均年龄大于25的结果。
SELECT user_dept,AVG(age) as '年龄' FROM `user`
GROUP BY user_dept
HAVING AVG(age)>25;
HAVING语句用于对分组后的结果进行统计
ORDER BY：对查询结果进行排序,默认为ASC升序，DESC为降序
案例：
SELECT user_id,user_name FROM `user`
ORDER BY user_name ;
DISTINCT：取消重复的结果
案例：
SELECT DISTINCT user_dept FROM `user`;
5.2、子查询
当所需的结果儒法用简单SELECT-FROM-WHERE查询结构获得时，可以在查询语句中嵌套子查询，实现更复杂的查询操作。
标量子查询
标量子查询即子查询的返回是单一值。
举例：从学生表中筛选生日晚于学号为05的学生的学生信息。
select * from student where s_birth > (select s_birth from student where s_id = 05)
该子查询嵌套在WHERE语句中作为判断条件的一部分，并与比较运算符结合使用。
同理，子查询还可以出现在FROM语句中作为数据源。案例：使用子查询对数据源限定c_id字段为01：
select * from student, (select * from score where c_id = 01) as 01_score where student.s_id = 01_score.s_id关联子查询
如果要求子查询的返回值不止一个数值，需要使用关联子查询。
案例：从学生表中筛选出学习了01课程的学生。
select * from student where student.s_id in (select s_id from score where c_id='01' )子查询也可以连接：
ANY谓词——任一，只要满足任一项都可以匹配
ALL谓词——全部满足才匹配
IN|NOT IN谓词
EXISTS|NOT EXISTS谓词——其右侧关联子查询，子查询括号内为真即可
5.3、模糊查询
LIKE
SELECT column_name FROM table_name
WHERE column_name LIKE 模式字符串通配符 | 含义 |
|---|---|
% | 匹配任意长度（0或多个）字符串，不能匹配空值 |
_ | 匹配任意单个字符串，_表示1个长度 |
案例：
查询user表中所有姓名为A开头的人员信息。
SELECT * FROM user
WHERE user_name like 'A%';
RLIKE、REGEXP
这两个主要用于在MYSQL中进行正则表达式的书写。
SELECT column_name FROM table_name
WHERE column_name REGEXP/RLIKE 模式字符串;通配符 | 含义 |
|---|---|
. | 匹配任意单个字符 |
* | 匹配0个或多个前一个得到的字符 |
^ | 匹配开头，如^s匹配以s或者S开头的字符串。 |
$ | 匹配结尾，如s$匹配以s结尾的字符串。 |
{n} | 匹配前一个字符反复n次。 |
｜ | 多个条件间的“或”连接 |
案例：
查询user表中姓名存在字段bb的人员。
select * from user where user_name regexp 'b{2}';
查询user表中姓名为n结尾的人员。
select * from user where user_name rlike 'n$';
查询user表中姓名存在an字段的人员。
select * from user where user_name rlike 'an';
5.4、存储过程
存储过程基本结构
构造存储过程：
CREATE PROCEDURE 存储体名称(IN/OUT 参数名 参数类型)
BEGIN
存储内容
END;
调用存储过程：
CALL 存储体名称(参数)终端运行存储过程
案例：
构造存储体u1，筛选所有部门为R&D的员工。
首先将MYSQL默认结束符进行修改：
DELIMITER //
构造存储过程：
CREATE PROCEDURE U1()
BEGIN
SELECT * FROM user WHERE user_dept = 'R&D';
END
//
调用存储体：
CALL u1()//
5.5、多表查询
内连接
SELECT * FROM TABLE_A INNER JOIN TABLE_B
ON TABLE_A.COLUMN 比较运算符 TABLE_B.COLUMN;内连接用于比较并返回满足连接条件的数据行，结果取交集。
inner join示意图：
注意：如果两边的表行数不一致，可能会出现丢失数据。
外连接
SELECT * FROM TABLE_A LEFT|RIGHT|FULL JOIN TABLE_B
ON TABLE_A.COLUMN 比较运算符 TABLE_B.COLUMN;外连接的两个表有主从之分，主表全部保存，从表适配保存。
full outer join示意图：
left/right outer join示意图：
6、锁表
锁表；
LOCK TABLES table_name LOCK_TYPE
解锁：
UNLOCK TABLES;
Lock_type包含read和write等。
锁为read类型的表，可读但不可写；其他会话对该表可读，但写入需要等到解锁后：
锁为write类型的表，可读也可写；但其他会话对该表的读或写需要等到解锁以后：
7、MYSQL慢查询：EXPLAIN
explain语句用于分析MYSQL的查询性能，可以显示MySQL如何使用索引等来处理select语句以及连接表。
用explain查看查询是否使用索引：
EXPLAIN SELECT * FROM table_name WHERE 查询条件；
案例：
对表Sheet1建立索引i1后，用explain查看任意查询情况：
explain select * from Sheet1 where number = 1601;explain查询结果type为ref，即使用了索引查询，而非全表遍历查询all；所使用的索引(possible keys/key)为i1：
今日小练习
模拟练习场景：构建学生、课程、教师、成绩表，基于这四张基本表结合DML进行MYSQL练习。
公众号后台回复“DML练习”，获取下述练习题所需数据。
Q1: 统计每门课程的学生人数
Q2: 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
Q3: 查询平均成绩大于等于60分的学生的学生编号、学生姓名和平均成绩
Q4: 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
Q5: 查询每门课程成绩最好的前两名