MySQL | INSERT_DELETE_UPDATE_SELECT《SQL必知必会》

SQL语言主要分为三大类：

DDL（data definition language，数据定义语言）
DML（data manipulation language，数据操作语言）
DCL（data control language，数据控制语言）

另外的一些分类：
DQL数据查询语言
TCL（transaction control language，事务控制语言）：COMMIT、ROLLBACK

SQL语句每条以;、\G、\g结束。

一、查询（基础）

1、SELECT FROM

# 列出表中的指定的一列
SELECT 列名
FROM 表名

.

# 列出表中的指定的多列（各列之间用`,`隔开）
SELECT 列名1, 列名2, 列名3
FROM 表名

.

# 列出表中的所有列（一般情况不建议使用）
SELECT *
FROM 表名

2、起别名 空格 AS ""

# 结果集result set
# 给列`起别名`的三种方法
# AS
SELECT 列名 AS 别名
FROM 表名

# 用空格间隔
SELECT 列名 别名
FROM 表名

# 用双引号""引起来。注意：不要使用''单引号。
SELECT 列名 "别名"
FROM 表名

3、DISTINCT数据去重

# 对列1和列2组成的整体去重，保证这两列的组合是独一无二的。
# 去重后的结果eg: 100 34、100 35、100 36
SELECT DISTINCT 列1, 列2
FROM 表

4、null的理解

null表示空，不等同于''（空字符串）和0 。

包含null的运算一定为null。

如果想避免null参与运算，可以用IFNULL(可能为null的变量)。

NULL不能和数字比较。
.
例如：当查询的字段包含NULL值时，想要输出结果不包含2（隐含意思：保留“不包含2的”与“为null的”），不能简单地写为WHERE 字段名<>2，而应该写为WHERE 字段名 IS NULL OR 字段名<>2。相关题目：leecode584：寻找用户推荐人

挖个坑：关于NULL的那些坑
（待更……）

5、着重号``：当命名与关键字重合时的解决办法

# 着重号``。当表名或列名与关键字重复时，用``把名字框起来，可以避免歧义。
SELECT *
FROM `ORDER`  # 这里的ORDER与关键字重复了。

查询常数，会根据其他列的数量自动填充

6、DISCRIBE显示表结构

# 显示表结构，显示表中字段的相关信息。
# 方法1
DISCRIBE 表名;
# 方法2
DESC 表名  

7、运算符

算数运算符+ - * / %

在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数
值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。

在JAVA语言中，100+‘1’的结果是1001，+ 表示连接。
在JAVA语言中，100+‘a’的结果是100，a 按0处理。
在JAVA语言中，如果可以除尽，则除法运算返回整型，否则返回浮点型。

在SQL中，会将字符串隐式转换成数值，100+‘1’的结果是101
在SQL中，除法运算返回值为浮点型
在SQL中，分母如果为0，结果为null
在SQL中，取模%运算的结果符号与被模数的符号相同。eg: 12 % 3 = 0；12 % -5 = 2；-12 % 5 = -2；-12 % -5 = -2。(%同MOD)

比较运算符

=：

0 = 'a'返回值为1，a被隐式转换为0。
'b' = 'a'返回值为0，因为二者都是字符串，所以不存在隐式转换，直接字符串进行比较。
1 = null返回值为null。
null = null返回值为null。
=两边只要有null，结果就为null。 <> != >= <=同理
WHERE price = null语句的执行不会有任何结果，也就是说，过滤之后的结果为空，即全部都过滤了。

安全等于<=>：

在没有null参与运算时，与=作用相同。
<=>是为null而生，当有null参与运算时，用<=>
1 <=> null返回值为0。
null <=> null返回值为1。
WHERE price <=> null语句的执行结果：查出所有price为null的数据。
注：只有在<=>存在时，null正常运算，其他情况下，运算结果都为null，过滤表示过滤全部信息。

IS NULL：关键字。
ISNULL：函数 --注： ISNULL等同于IS NULL。平时用IS NULL就行。

适用情况：选出没有……的信息

.

IS NOT NULL：关键字

SELECT LEAST('a', 'b', 'c'), GREASTEST('a', 'b', 'c') --取最小值，取最大值
FROM price
.
获取更短的那个
LEAST(LENGTH(first_name, last_name)) --取最长的值
FROM price

BETWEEN ... AND ...
NOT BEWTEEN ... AND ...

IN(set)：在…区间。IN(20, 50)表示在20或50。表示“是20, 50”
NOT IN(set)：不在…区间

逻辑运算符

非：NOT !
且：AND 优先级比OR高
或：OR ||
NOR

位运算符

&：按位与
|：按位或
^：按位或
~：按位取反
>>：右移，作用相当于 /2
<<：左移 ，作用相当于 ×2

8、查询

LIKE

LIKE模糊查询
①%
LIKE '%a%' – 查询包含a的列。%表示不确定数量的字符。
last_name LIKE '%a%b%' or last_name LIKE '%b%a%' – 查询既包含a又包含b的内容
last_nameLIKE '%a%' AND '%b%' – 查询既包含a又包含b的内容
LIKE '_a' – 查询第二个字符为a的列。
.
②\
查询第二个字符是_且第三个字符是a的员工信息。需要使用转义字符。
WHERE last_name LIKE '_\_a%';

正则表达式REGEXP

例如：SELECT ‘SHKSTART’ REGEXP ‘^shk’;

^：表示以 该字符后面的字符 开头的字符串。理解记忆：小荷才露尖尖角。
$：表示以 该字符前面的字符 结尾的字符串。理解记忆：硬币扔到水里就沉底了。

SELECT ‘SHKSTART’ REGEXP 'e$'; – 以e结尾的

.匹配任何一个单字符。例如：匹配's..k'表示匹配’s’'k’中间有两个字符的内容。
[...]：匹配在方括号中的任何字符。

例如：[a, b, c]表示匹配“a”或“b”或“c”。
例如：[a-z]匹配任何字母。
例如：[0-9]匹配任何数字。

9、排序ORDER BY DESC

ORDER BY... – 按…升序排列
ORDER BY ... DESC – 按…降序排列

注意：列的别名只能在ORDER BY中使用，不能在WHERE中使用。

# SQL程序的执行顺序
SELECT ...  -- 执行顺序3
FROM ...  -- 执行顺序1
WHERE ...  --执行顺序2
ORDER BY ... --执行顺序4

# 因为执行WHERE时，还未读到列的别名，所以会报错。

二级排序

ORDER BY 一级排序参考的列, 二级排序参考的列

在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第
一列数据中所有值都是唯一的，将不再对第二列进行排序。

10、分页操作LIMIT——显示

严格的格式：LIMIT 位置偏移量， 条目数
若偏移量为0，则LIMIT 0, 条目数 等价于 LIMIT 条目数

适用背景：显示

1. 记录太多，只想显示指定记录
2. 记录太多，向分页显示

优点：

1. 减少数据表的网络传输量
2. 提升查询效率

# 每页显示20条记录，此时显示第一页
SELECT employee_id, last_name
FROM employee
LIMIT 0, 20  -- 0表示偏移量，意思是从当前位置开始；20表示显示的总数

# 每页显示20条记录，此时显示第二页
SELECT employee_id, last_name
FROM employee
LIMIT 20, 20  -- 20表示偏移量，意思是从当前位置+20开始；20表示显示的总数

# 需求：每页显示pagesize条记录，此时显示第pageNO页
# 公式：LIMIT (pageNO-1) * pagesize, pagesize

例：查询员工表中工资最高的员工信息

SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC  -- 对薪水进行排序
LIMIT 1;  --显示薪水排名第一的信息

LIMIT 条目数 OFFSET 偏移量
例：表里有107条数据，只想显示第32、33条数据：

SELECT employee_id, last_name
FROM employees
LIMIT 2 OFFSET 31  

例：查询邮箱中包含e的员工信息，并先按邮箱的字节数(LENGTH())降序，再按部门号升序

SELECT employee_id, last_name, department_id
FROM employees
WHERE email LIKE "%e%"
# WHERE email REGEXP '[e]' 这种写法也可以
ORDER BY LENGTH(email) DESC, department_id

11、WHERE过滤条件

适用于选择……的情况。

例：选择工资不在8000到17000的员工的姓名和工资，按工资降序，显示第21到40位置的数据

SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
LIMIT 20，20 -- 第21条数据的偏移量是20

WHERE…ORDER BY…LIMIT的声明顺序（不是执行顺序！注意区分）

SELECT employee_id, last_name, salary
FROM employees
WHERE salaty > 6000
ORDER BY salary DESC
LIMIT 10;

目前为止学过的知识：

SELECT ..., ..., ...
FROM ...
WHERE ... AND / OR / NOT...
ORDER BY ... (ASC / DESC), ...
LIMIT ..., ...

2、多表查询（也称关联查询）

笛卡尔积又称为交叉连接。列出X集合与Y集合的所有可能匹配情况，即为笛卡尔积。

# 出现笛卡尔积的错误
SELECT employee_id, department_name -- 两个列，分属于两个表
FROM employees, department -- 两个表

# 错误的原因：缺乏多表的连接条件 或 连接条件不起作用（写错了） 或 所有表中的所有行都互相连接 
WHERE employees.department_id = departments.department_id;
# 从sql优化的角度，建议如果查询语句中出现了多个表中都存在的字段（同名），则必须指明此字段所在的表
# 使用完全限定表名

完全限定的表名：表名.列名

可以给表起别名

结论：如果有N个表实现多表查询，则至少需要N-1个连接条件

SELECT employee_id, department_name
FROM employees t1, departments t2 -- 执行顺序1，后续将根据这一步骤定义的别名进行操作
WHERE t1.department_id = t2.department_id; -- 如果给表起了别名，若在SELECT或WHERE中必须使用表的别名

查询员工的employee_id, last_name, department_name, city

SELECT t1.employee_id, t2.department_name
FROM employees t1, departments t2 -- 执行顺序1，后续将根据这一步骤定义的别名进行操作
WHERE t1.department_id = t2.department_id; -- 如果给表起了别名，若在SELECT或WHERE中必须使用表的别名

演绎问题：提出问题1 —> 解决问题1 —> 提出问题2—> 解决问题2 —>
归纳式：总 —> 分

多表连接相当于嵌套for循环，非常消耗资源，会让sql查询性能下降得很严重。因此，要避免不必要的连接，超过三个表禁止join。

角度1：等值连接 vs 非等值连接 均属于非自连接

等值连接：一个表的主键【唯一、非空】与外键一一对应。即：自然连接。

非等值连接：

# 非等值连接的例子
SELECT *
FROM job_grades;
SELECT last_name, salary, grade_level
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

角度2：自连接（自己跟自己连） vs 非自连接

自连接：table1和table2本质时同一张表，只是通过取别名的方式，虚拟成两张表。

例子：

# 练习：查询员工姓名及其管理者的id和姓名
SELECT wmp.employee_id, wmp.last_name, mgr.employee_id, mgr.last_name
FROM employees wmp, employees mgr
WHERE emp.manager_id = mgr.employee_id;

角度3：内连接 vs 外连接

内连接：合并具有同一列的两个以上的行，结果集中不包含一个表与另一个表不匹配的行
外连接：合并具有同一列的两个以上的行，结果集中除了包含一个表与另一个表匹配的行之外，还包含 左表（ 左外连接 ） 或 右表（ 右外连接 ） 中不匹配的行。

外连接的分类：左外连接，右外连接，满外连接

左外连接：两个表在连接过程中，除了返回满足条件的行外，还返回左表中不满足条件的行。【主表（左表），从表（右表）】
右外连接：两个表在连接过程中，除了返回满足条件的行外，还返回右表中不满足条件的行。【主表（右表），从表（左表）】
满外连接：

查询所有的员工的last_name，department_name信息

题解：所有的一定是一个外连接题目。

# 内连接
SELECT employee_id, department_name, city
FROM employee e INNER JOIN department d -- INNER可以省略
ON e.department_id = d.department_id

JOIN location1
ON d.location_id = l.location_id;

# 左外连接
SELECT employee_id, department_name, city
FROM employee e LEFT OUTER JOIN department d -- OUTER可以省略
ON e.department_id = d.department_id

# 右外连接
SELECT employee_id, department_name, city
FROM employee e RIGHT OUTER JOIN department d -- OUTER可以省略
ON e.department_id = d.department_id

# 满外连接（取指定列的并集）: mysql不支持FULL
SELECT employee_id, department_name, city
FROM employee e FULL OUTER JOIN department d -- OUTER可以省略
ON e.department_id = d.department_id
JOIN location1
ON d.location_id = l.location_id;

UNION

将多条SELECT语句的结果合并成一个结果集。
注：合并时，两个表对应的列数和数据类型必须相同，并且相互对应。

SELECT ...
FROM ...

UNION

SELECT ...
FROM ...

UNION返回两个查询结果的并集，去除重复数据
UNION ALL返回两个查询结果的并集，不去除重复数据。

UNION ALL的查询效率比UNION高。如果明确知道数据中不包含重复数据，尽量使用UNION ALL，提升数据查询效率。

SQL标准：最重要的是SQL92（92年提出的标准）和SQL99（99年提出的标准）。SQL99内容更多，可读性更强。但是，我们只需要掌握一些核心的功能，满足日常工作的需求即可。

函数

函数：把经常使用的代码封装起来，需要的时候直接调用。
优点：使用函数可以提高代码的效率，提高可维护性。提高用户对数据库的管理效率。

CEILING()：天花板函数，返回高于或等于天花板（当前值）的值
FLOOR()：地板函数，返回小于或等于地板（当前值）的值

CEILING(-43.33)：-43
FLOOR(-43.33) = -44

RAND(种子)：种子可以保证每次取的随机数的结果是相同的。
eg：RAND(10)

底层运行原理

所谓底层运行的原理，就是执行顺序，如下：

SELECT (DISTINCT)  -- 执行顺序5
FROM TABLE1 JOIN/LEFT JOIN/RIGHT JOIN TABLE2  -- 执行顺序1
(ON) 连接条件
WHERE IN/NOT/BETWEEN...AND...  -- 执行顺序2

GROUP BY  -- 执行顺序3
HAVING  -- 执行顺序4

ORDER BY DESC/ASC  -- 执行顺序6
LIMIT  -- 执行顺序7

二、增——添加数据INSERT INTO

储备工作

USE datasets_name

CREATE TABLE IF NOT EXISTS empl(
id INT, 
`name` VARCHAR(15),
hire_date DATE,
salary DOUBLE(10, 2)
);

DESC empl;

SELECT *
FROM empl;

方式1：白手起家——INSERT INTO 表名 VALUES (字段1, 字段2……)

①插入一条数据（推荐“指明字段”）

INSERT INTO empl(id, hire_date, salary, `name`)
VALUES (1, '2000-12-21', 3400, 'Tom');

说明：没有指明的字段为null。

# 没有指明的hire_date为null
INSERT INTO empl(id, salary, `name`)
VALUES (1, 3400, 'Tom');

注：不指明字段时，一定要按照声明的顺序进行添加。

INSERT INTO empl
VALUES (1, 'Tom', '2000-12-21', 3400);

如果没有值，用NULL就行：

INSERT INTO pet
VALUE
('Fluffy', 'harold', 'Cat', 'f', '2003',NULL);

②同时插入多条数据

INSERT INTO empl(id, hire_date, salary, `name`)
VALUES 
(1, '2000-12-21', 3400, 'Tom'),
(2, '2000-12-21', 3400, 'alice'),
(3, '2000-12-21', 3400, 'joy');

方式2：将查询结果插入到表中

查询的字段一定要与添加到的表一一对应。
注意：要添加的表中的数据字段的长度不能低于查询的表中的字段的长度，否则会有添加不成功的风险。

INSERT INTO empl()
SELECT...
FROM...
WHERE...

DESC empl;

三、删——DELETE FROM 表名 WHERE 过滤条件

DELETE FROM empl
WHERE id = 1;

删除表中的所有数据

DELETE FROM empl

删除数据时，有可能由于约束的影响导致失败。

练习：将userid为Bbiri的user表和my_employees表的记录全部删除
方式1：每个表单独删

DELETE FROM my_employees
WHERE userid='Bbiri';

DELETE FROM user
WHERE userid='Bbiri';

方式2：将两个表格连接起来，然后一起操作

# 将userid为Bbiri的user表和my_employees表的记录全部删除
DELETE u,e
FROM users u
JOIN my_employees e ON u.`userid`=e.`Userid`
WHERE u.`userid`='Bbiri';

四、改——更新（修改）数据UPDATE ... SET ... WHERE ...

修改一条数据的一个字段

UPDATE EMPL
SET hire_date = CURDATE()
WHERE id=5;  -- 如果不添加筛选条件，则所有hire_date都会被修改

修改一条数据的多个字段

UPDATE emp;
SET hire_date = CURDATE(), salary = 6000
WHERE id=5;  -- 如果不添加筛选条件，则所有hire_date都会被修改

题目：将表中姓名包含字符a的提薪20%

UPDATE empl
SET salary = 120% * salary
WHERE `name`LIKE '%a%'

注意：修改数据时，可能由于约束的影响导致不成功。
当要修改的字段不存在时，会报错。

---

DML操作在默认情况下，执行完后会自动提交数据。
如果希望执行完以后不自动提交数据，则需要使用SET COMMIT=FALSE

在实际开发当中，可以在操作前后都查看一下表DESC 表名，确保操作有效。

检查所作的修正

SELECT *
FROM 表名

------------------------------高级篇--------------------------------------

事务

提交数据 COMMIT

一旦执行COMMIT，数据就被永久地保存在数据库当中，数据不可以回滚。

如果后悔了，只能通过其他方式取改回来。

回滚数据 ROLLBACK

可以理解为“撤回”。

一旦执行ROLLBACK，则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

DDL的操作：一旦执行，不可以回滚。因为DDL执行完之后，会自动执行一次COMMIT，而这个COMMIT操作是不受SET操作影响的。
DML的操作：默认情况下，一旦执行，不可以回滚。但是，可以通过修改参数实现回滚。

如果在执行DML之前，执行SET COMMIT=FALSE，则，执行的DML操作就可以回滚了。
注意：SET COMMIT=FALSE指令对DDL不起作用。

COMMIT

SELECT *
FROM biao;

SET autocommit = FALSE;
DELETE FROM biao;

ROLLBACK;  -- 回滚到COMMIT步骤之后