SQL常用语句笔记

SQL笔记

1.常用MySQL命令

# 查看所有数据库
SHOW DATABASES;
# 切换指定数据库
USE test;
# 查看当前库中所有的表
SHOW TABLES;
# 查看表结构
DESC departments;
# 查看当前所处的数据库
SELECT DATABASE();
# 查看当前登陆用户
SELECT USER();
# 查看版本
SELECT VERSION();
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.语法规范

关键字不区分大小写，但建议关键字大写
表名、列名建议小写
每条命令最好用分号结尾
每条命令根据需要，可以进行缩进或换行
最好是关键字单独占一行

3.语句分类

数据查询语言（Data Query Language, ）DQL
负责进行数据查询而不会对数据本身进行修改的语句。

数据定义语言 (Data Definition Language,）DDL
负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成

数据操纵语言（Data Manipulation Language,）DML
负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。

数据控制语言 (Data Control Language)
它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。

数据查询语言

基础查询

# 查单个字段
select dept_name from departments;
# 查多个字段
select name, email from employees;
# 查所有字段
select * from departments;
# 使用表达式
select date, employee_id, basic+bonus from salary;
# 查询函数，统计salary共有多少行记录
select count(*) from salary;
# 使用别名，字段名和别名之间可以用空格或关键字AS与as指定别名
select dept_id 部门编号, dept_name AS 部门名 from departments;
# 去重 distinct
select dept_id from employees;
select distinct dept_id from employees;
# 使用concat函数进行字符串拼接
select concat(name, '-', phone_number) from employees;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

条件查询

select * from departments where dept_id>3; 
select * from departments where dept_id<3;
select * from departments where dept_id=3;
select * from departments where dept_id!=3;
select * from departments where dept_id>=3;
select * from departments where dept_id<=3;

select * from departments where dept_id>1 and dept_id<5;
select * from departments where dept_id<3 or dept_id>6;
select * from departments where not dept_id<=6;
1
2
3
4
5
6
7
8
9
10

模糊查询

like: 包含
between x and y : 在x和y之间的
in：在列表中的
is null：为空，相当于python的None
is not null：非空
%匹配0到多个任意字符
_匹配一个字符

select name, email from employees where name like '张%';

select name, email from employees where name like '张_';

select * from departments where dept_id between 3 and 5;

select * from departments where dept_id in (1, 3, 5, 8);
# 匹配部门名为空的记录
select * from departments where dept_name is null;
# 查询部门名不为空的记录
select * from departments where dept_name is not null;
1
2
3
4
5
6
7
8
9
10
11

排序（默认升序）

select name, birth_date from employees where birth_date>'19980101';
# 默认升序排列
select name, birth_date from employees where birth_date>'19980101' order by birth_date;
# 降序排列
select name, birth_date from employees where birth_date>'19980101' order by birth_date desc;
1
2
3
4
5

函数

字符函数

LENGTH(str)：返字符串长度，以(字节)为单位

select length('abc');
select length('你好');
select name, email, length(email) from employees where name='李平';
1
2
3

CONCAT(s1,s2，…): 返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL

select concat(dept_id, '-', dept_name) from departments;
1

**UPPER(str)**和UCASE(str): 将字符串中的字母全部转换成大写

select name, upper(email) from employees where name like '李%';
1

**LOWER(str)**和LCASE(str):将str中的字母全部转换成小写

SUBSTR(s, start, length): 从子符串s的start位置开始，取出length长度的子串，位置(从1)开始计算

select substr('hello world', 7);
# 取子串，下标从7开始取出3个
select substr('hello world', 7, 3);
1
2
3

INSTR(str,str1)：返回str1参数，在str参数内的位置

# 子串在字符串中的位置
select instr('hello world', 'or');
select instr('hello world', 'ol');
1
2
3

TRIM(s): 返回字符串(s删除了两边空格之后的字符串)

select trim('  hello world.  ');
1

LEFT(str, length) ：从左开始截取字符串，length 是截取的长度。

group_concat语法

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
1

数学函数

ABS(x)：返回x的绝对值

select abs(-10);
1

MOD(x,y): 返回x被y除后的余数

select mod(10, 3);
1

CEIL(x)、CEILING(x): 返回不小于x的最小整数

select ceil(10.1);
1

FLOOR(x): 返回不大于x的最大整数

select floor(10.9);
1

ROUND(x)、ROUND(x,y): 前者返回最接近于x的整数，即对x进行四舍五入；后者返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位

select round(10.6666);返回最接近于x的整数，即对x进行四舍五入
select round(10.6666, 2);返回最接近x的数，其值保留到小数点后面y位
1
2

日期和时间函数

CURDATE()、CURRENT_DATE(): 将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定

select curdate();当前日期按照"YYYY-MM-DD"
select curdate() + 0;格式根据函数用在字符串或是数字语境中而定
1
2

NOW(): 返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定

select now();式为"YYYY_MM-DD HH:MM:SS"
select now() + 0;具体格式根据函数用在字符串或数字语境中而定
1
2

UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date): 前者返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数，后者返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数

select unix_timestamp();
1

FROM_UNIXTIME(date): 和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间

select from_unixtime(0);
1

MONTH(date)和MONTHNAME(date):前者返回指定日期中的月份，后者返回指定日期中的月份的名称

select month('20211001120000');返回指定日期中的月份
select monthname('20211001120000');返回指定日期中的月份的名称
1
2

DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d): DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二

select dayname('20211001120000');返回星期*
select dayname('20211001');
1
2

WEEK(d): 计算日期d是一年中的第几周

select week('20211001');
1

DAYOFYEAR(d)、DAYOFMONTH(d)：前者返回d是一年中的第几天，后者返回d是一月中的第几天

select dayofyear('20211001');
1

YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time): YEAR(date)返回指定日期对应的年份，范围是1970到2069；QUARTER(date)返回date对应一年中的季度，范围是1到4；MINUTE(time)返回time对应的分钟数，范围是0~59；SECOND(time)返回制定时间的秒值

select year('20211001');返回指定日期对应的年份
select quarter('20211001');回date对应一年中的季度
1
2

datediff(日期1, 日期2)：得到的结果是日期1与日期2相差的天数。
如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负。

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
1
SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
-31
1
2
3
4

流程控制函数

IF(expr,v1,v2): 如果expr是TRUE则返回v1，否则返回v2

select if(3>0, 'yes', 'no');
select name, dept_id, if(dept_id=1, '人事部', '非人事部')  from employees where name='张亮';
1
2

IFNULL(v1,v2): 如果v1不为NULL，则返回v1，否则返回v2

select dept_id, dept_name, ifnull(dept_name, '未设置') from departments;
insert into departments(dept_id) values(9);
select dept_id, dept_name, ifnull(dept_name, '未设置') from departments; 
1
2
3

CASE expr (WHEN v1)( THEN r1) [WHEN v2 THEN v2] [ELSE rn] END: 如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn

select dept_id, dept_name,
case dept_nam
when '运维部' then '技术部门'
when '开发部' then '技术部门'
when null then '未设置'
else '非技术部门'
end as '部门类型'
from departments;
1
2
3
4
5
6
7
8

select dept_id, dept_name,
case 
when dept_name='运维部' then '技术部门'
when dept_name='开发部' then '技术部门'
when dept_name is null then '未设置'
else '非技术部门'
end as '部门类型'
from departments;
1
2
3
4
5
6
7
8

分组函数

用于统计，又称为聚合函数或统计函数

# sum/min/count/avg
select employee_id, max(basic+bonus) from salary where employee_id=10 and year(date)=2018;
1
2

分组查询

语法格式

查询列表必须是分组函数和出现在（GROUP BY）后面的字段
通常而言，分组前的数据筛选放在where子句中，分组后的数据筛选放在having子句中

SELECT 字段名1(要求出现在group by后面)，分组函数(),……
FROM 表名
WHERE 条件
GROUP BY 字段名1，字段名2
HAVING 过滤条件
ORDER BY 字段;
1
2
3
4
5
6

查询每个部门的人数
select dept_id, count(*) from employees group by dept_id;

查询每个部门中年龄最大的员工
select dept_id, min(birth_date) from employees group by dept_id;

查询每个部门入职最晚员工的入职时间
select dept_id, max(hire_date) from employees group by dept_id;

统计各部门使用tedu.cn邮箱的员工人数
select dept_id, count(*) from employees where email like '%@tedu.cn' group by dept_id;
+---------+----------+
| dept_id | count(*) |
+---------+----------+
|       1 |        5 |
|       2 |        2 |
|       3 |        4 |
|       4 |       32 |
|       5 |        7 |
|       6 |        5 |
|       7 |       15 |
|       8 |        1 |
+---------+----------+
8 rows in set (0.00 sec)

查看员工2018年工资总收入，按总收入进行降序排列
select employee_id, sum(basic+bonus) as total from salary where year(date)=2018 group by employee_id order by total desc;

查询部门人数少于10人
select dept_id, count(*) from employees where count(*)<10 group by dept_id;
ERROR 1111 (HY000): Invalid use of group function
 
select dept_id, count(*) from employees group by dept_id having count(*)<10;
+---------+----------+
| dept_id | count(*) |
+---------+----------+
|       1 |        8 |
|       2 |        5 |
|       3 |        6 |
|       6 |        9 |
|       8 |        3 |
+---------+----------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

连接查询

也叫多表查询。常用于查询字段来自于多张表

如果直接查询两张表，将会得到笛卡尔积
select name, dept_name from employees, departments;

通过添加有效的条件可以进行查询结果的限定
select name, dept_name from employees, departments where employees.dept_id=departments.dept_id;
1
2
3
4
5

语法格式

SELECT 字段... 
FROM 表1 [AS] 别名 [连接类型]
JOIN 表2 [AS] 别名
ON 连接条件
WHERE 分组前筛选条件
GROUP BY 分组
HAVING 分组后筛选条件
ORDER BY 排序字段
1
2
3
4
5
6
7
8

内连接

select 查询列表
from 表1 别名
inner join 表2 别名 on 连接条件
inner join 表3 别名 on 连接条件
[where 筛选条件]
[group by 分组]
[having 分组后筛选]
[order by 排序列表]
1
2
3
4
5
6
7
8

等值连接

查询每个员工所在的部门名，使用别名。两个表中的同名字段，必须指定表名

select name, d.dept_id, dept_name
from employees as e
inner join departments as d
on e.dept_id=d.dept_id;
1
2
3
4

查询2018年总工资大于30万的员工，按工资降序排列

select name, sum(basic+bonus) as total from employees as e
inner join salary as s
on e.employee_id=s.employee_id
where year(s.date)=2018
group by name
having total>300000
order by total desc;
1
2
3
4
5
6
7

非等值连接 between … and …（前面包括后面不包括）

创建表语法：

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)
1
2
3
4
5
6
7

mysql> use test;
mysql> create table age_grade
    -> (
    -> id int, #主键。仅作为表的行号
    -> grade char(1), #工资级别，共ABCDE五类
    -> low int, #该级别最低工资
    -> high int, #该级别最高工资
    -> primary key (id));
1
2
3
4
5
6
7
8

向表中插入数据语法：

INSERT INTO 表名称 VALUES (值1, 值2,....);
1

insert into age_grade values
(1, 'A', 5000, 8000),
(2, 'B', 8001, 10000),
(3, 'C', 10001, 15000);
1
2
3
4

查询2018年12月员工各基本工资级别的人数

select grade, count(*)
from salary as s
inner join wage_grade as g
on s.basic between g.low and g.high
where year(date)=2018 and month(date)=12
group by grade;
1
2
3
4
5
6

查询2018年12月员工基本工资级别，员工需要显示姓名

select name, date, basic, grade
from salary as s
inner join employees as e
on s.employee_id=e.employee_id
inner join wage_grade
on basic between low and high
where date='20181210'
order by grade, basic;
1
2
3
4
5
6
7
8

自连接

将一张表作为两张使用
每张表起一个别名

查看哪些员的生日月份与入职月份相同

select e.name, e.hire_date, em.birth_date
from employees as e
inner join employees as em
on month(e.hire_date)=month(em.birth_date)
and e.employee_id=em.employee_id;
1
2
3
4
5

外连接的概述

常用于查询一个表中有，另一个表中没有的记录
如果从表中有和它匹配的，则显示匹配的值
如要从表中没有和它匹配的，则显示NULL
外连接查询结果=内连接查询结果+主表中有而从表中没有的记录
左外连接中，left join左边的是主表left outer join
右外连接中，right join右边的是主表right outer join
左外连接和右外连接可互换，实现相同的目标

左外连接

语法

SELECT tb1.字段..., tb2.字段
FROM table1 AS tb1
LEFT OUTER JOIN table2 AS tb2 
ON tb1.字段=tb2.字段
1
2
3
4

查询所有部门的人员以及没有员工的部门

select d.*, e.name
from departments as d
left outer join employees as e
on d.dept_id=e.dept_id;
1
2
3
4

右外连接

查询所有部门的人员以及没有员工的部门

select d.*, e.name
    -> from employees as e
    -> right outer join departments as d
    -> on d.dept_id=e.dept_id;
1
2
3
4

交叉连接 cross join

返回笛卡尔积

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
1

子查询

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式

子查询返回的数据分类

单行单列：返回的是一个具体列的内容，可以理解为一个单值数据
单行多列：返回一行数据中多个列的内容
多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围
多行多列：查询返回的结果是一张临时表

子查询常出现的位置

select之后：仅支持单行单列
from之后：支持多行多列
where或having之后：支持单行单列、单行多列、多行单列

单行单列

查询运维部所有员工信息

#  首先从departments表中查出运维部的编号
select dept_id from departments where dept_name='运维部';
+---------+
| dept_id |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)
# 再从employees表中查找该部门编号和运维部编号相同的员工
select *
from employees
where dept_id=(
   select dept_id from departments where dept_name='运维部'
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查询每个部门的人数

# 查询所有部门的信息
select d.* from departments as d;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
|       3 | 运维部    |
|       4 | 开发部    |
|       5 | 测试部    |
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
|       9 | NULL      |
+---------+-----------+
9 rows in set (0.00 sec)
# 查询每个部门的人数
select d.*, (
select count(*) from employees as e
   where d.dept_id=e.dept_id
) as amount
from departments as d;
+---------+-----------+--------+
| dept_id | dept_name | amount |
+---------+-----------+--------+
|       1 | 人事部    |      8 |
|       2 | 财务部    |      5 |
|       3 | 运维部    |      6 |
|       4 | 开发部    |     55 |
|       5 | 测试部    |     12 |
|       6 | 市场部    |      9 |
|       7 | 销售部    |     35 |
|       8 | 法务部    |      3 |
|       9 | NULL      |      0 |
+---------+-----------+--------+
9 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

多行单列

单行多列

查找2018年12月基本工资和奖金都是最高的工资信息

# 查询2018年12月最高的基本工资
select max(basic) from salary
where year(date)=2018 and month(date)=12;
+------------+
| max(basic) |
+------------+
|      25524 |
+------------+
1 row in set (0.00 sec)

# 查询2018年12月最高的奖金
select max(bonus) from salary
where year(date)=2018 and month(date)=12;
+------------+
| max(bonus) |
+------------+
|      11000 |
+------------+
1 row in set (0.00 sec)

mysql> select * from salary
    -> where year(date)=2018 and month(date)=12 and basic=(
    ->   select max(basic) from salary
    ->   where year(date)=2018 and month(date)=12
    -> ) and bonus=(
    ->   select max(bonus) from salary
    ->   where year(date)=2018 and month(date)=12
    -> );
+------+------------+-------------+-------+-------+
| id   | date       | employee_id | basic | bonus |
+------+------------+-------------+-------+-------+
| 6368 | 2018-12-10 |         117 | 25524 | 11000 |
+------+------------+-------------+-------+-------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

多行多列

查询3号部门及其部门内员工的编号、名字和email

# 查询3号部门和员工的所有信息
select d.dept_name, e.*
from departments as d
inner join employees as e
on d.dept_id=e.dept_id;

# 将上述结果当成一张临时表，必须为其起别名。再从该临时表中查询
mysql> select dept_id, dept_name, employee_id, name, email
    -> from (
    ->   select d.dept_name, e.*
    ->   from departments as d
    ->   inner join employees as e
    ->   on d.dept_id=e.dept_id
    -> ) as tmp_table
    -> where dept_id=3;
+---------+-----------+-------------+-----------+--------------------+
| dept_id | dept_name | employee_id | name      | email              |
+---------+-----------+-------------+-----------+--------------------+
|       3 | 运维部    |          14 | 廖娜      | liaona@tarena.com  |
|       3 | 运维部    |          15 | 窦红梅    | douhongmei@tedu.cn |
|       3 | 运维部    |          16 | 聂想      | niexiang@tedu.cn   |
|       3 | 运维部    |          17 | 陈阳      | chenyang@tedu.cn   |
|       3 | 运维部    |          18 | 戴璐      | dailu@tedu.cn      |
|       3 | 运维部    |          19 | 陈斌      | chenbin@tarena.com |
+---------+-----------+-------------+-----------+--------------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

分页查询

# 按employee_id排序，取出前15至20号员姓名
select employee_id, name from employees
order by employee_id
limit 15, 5;
+-------------+--------+
| employee_id | name   |
+-------------+--------+
|          16 | 聂想   |
|          17 | 陈阳   |
|          18 | 戴璐   |
|          19 | 陈斌   |
|          20 | 蒋红   |
+-------------+--------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

联合查询UNION

作用：将多条select语句的结果，合并到一起，称之为联合操作。

语法：( ) UNION ( )

要求查询时，多个select语句的检索到的字段数量必须一致
每一条记录的各字段类型和顺序最好是一致的
UNION关键字默认去重，可以使用UNION ALL包含重复项

查询1972年前或2000年后出生的员工

select name, birth_date from employees
where year(birth_date)<1972 or year(birth_date)>2000;
+-----------+------------+
| name      | birth_date |
+-----------+------------+
| 梁伟      | 1971-08-19 |
| 张建平    | 1971-11-02 |
| 窦红梅    | 1971-09-09 |
| 温兰英    | 1971-08-14 |
| 朱文      | 1971-08-15 |
| 和林      | 1971-12-10 |
+-----------+------------+
6 rows in set (0.01 sec)


mysql> (
    -> select name, birth_date from employees
    ->   where year(birth_date)<1972
    -> )
    -> union
    -> (
    ->   select name, birth_date from employees
    ->   where year(birth_date)>=2000
    -> );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

插入语句

不指定列名的插入

语法格式：

INSERT INTO 表名称 VALUES (值1, 值2,....)
1

需要为所有列指定值
值的顺序必须与表中列的顺序一致

指定列名的插入

语法格式：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
1

列和值的顺序要一致
列名先后顺序不重要

主键由于是自动增长的，也可以不指定主键的值

支持子查询

mysql> insert into employees
    -> (name, hire_date, birth_date, email, phone_number, dept_id)
    -> (
    ->   select name, hire_date, birth_date, email, phone_number, dept_id
    ->   from employees
    ->   where name='张三'
    -> );
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
1
2
3
4
5
6
7
8
9

使用set语句

语法格式：

INSERT INTO 表名 SET 列名1=列值1, 列名2=列值2, ...
1

修改语句

修改单表记录

语法：

UPDATE 表名称 SET 列名称=新值, 列名称=新值, ... WHERE 筛选条件
1

修改多表记录

语法：

UPDATE 表1 AS 表1别名
INNER | LEFT | RIGHT JOIN 表2 AS 表2别名
ON 连接条件
SET 列=值, 列=值, ...
WHERE 连接条件
1
2
3
4
5

# 修改李四所在部门为企划部
update departments as d
inner join employees as e
on d.dept_id=e.dept_id
set d.dept_name='企划部'
where e.name='李四';
1
2
3
4
5
6

删除记录

删除单表记录
语法：

DELETE FROM 表名 WHERE 筛选条件;
1

删除的是满足条件的整行记录，而不是某个字段

删除重复的电子邮箱

表: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id是该表的主键列。
该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。
1
2
3
4
5
6
7
8

编写一个 SQL 删除语句来删除所有重复的电子邮件，只保留一个id最小的唯一电子邮件。

以 任意顺序 返回结果表。（注意：仅需要写删除语句，将自动对剩余结果进行查询）

查询结果格式如下所示。

示例 1:

输入: 
Person 表:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
输出: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
解释: john@example.com重复两次。我们保留最小的Id = 1。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

delete p1.* 
from Person p1,Person p2 
where p1.email=p2.email and p1.id>p2.id
1
2
3

删除多表记录

语法：

DELETE 表1别名, 表2别名
FROM 表1 AS 表1别名
INNER | LEFT | RIGHT JOIN 表2 AS 表2别名
ON 连接条件
WHERE 筛选条件
1
2
3
4
5

# 删除9号部门中所有的员工
delete e
from employees as e
inner join departments as d
on e.dept_id=d.dept_id
where d.dept_id=9;
Query OK, 2 rows affected (0.00 sec)
1
2
3
4
5
6
7

清空表

语法：

TRUNCATE TABLE 表名
TRUNCATE不支持WHERE条件
1
2

自增长列，TRUNCATE后从1开始；DELETE继续编号
TRUNCATE不能回滚，DELETE可以
效率略高于DELETE

数据库管理

创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];
1
2
3

[ ]中的内容是可选的
<数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。
IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
[DEFAULT] COLLATE：指定字符集的默认校对规则。
MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式。

修改数据库

语法：

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}
1
2
3

ALTER DATABASE 用于更改数据库的全局特性。
使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
数据库名称可以忽略，此时语句对应于默认数据库。
CHARACTER SET 子句用于更改默认的数据库字符集。

删除数据库

语法：

DROP DATABASE [ IF EXISTS ] <数据库名>
1

<数据库名>：指定要删除的数据库名。
IF EXISTS：用于防止当数据库不存在时发生错误。
DROP DATABASE：删除数据库中的所有表格并同时删除数据库。
如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

关系数据库的规范化

第一范式（1NF）

在一个关系中，消除重复字段，且各字段都是最小的逻辑存储单位。即，要满足原子性。
第一范式是第二和第三范式的基础，是最基本的范式。第一范式包括下列指导原则。（1）数据组的每个属性只可以包含一个值。
（2）关系中的每个数组必须包含相同数量的值。
（3）关系中的每个数组一定不能相同。
在任何一个关系数据库中，第一范式是对关系模式的基本要求，不满足第一范式的数据库就不是关系型数据库。

第二范式（2NF）

第二范式是在第一范式的基础上建立起来的，即满足第二范式必先满足第一范式（1NF）。
第二范式要求数据库表中的每个实体（即各个记录行）必须可以被唯一地区分。
为实现区分各行记录通常需要为表设置一个“区分列”，用以存储各个实体的唯一标识。这个唯一属性列被称为主关键字或主键。
第二范式要求实体的属性完完全依赖于主关键字，即不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

第三范式（3NF）

第三范式是在第二范式的基础上建立起来的，即满足第三范式必先满足第二范式。
第三范式要求关系表不存在非关键字列对任意候选关键字列的传递函数依赖，也就是说，第三范式要求一个关系表中不包含已在其他表中包含的非主关键字信息。
除主键外，其他字段必须依赖主键。

修改表

修改列名

语法：

ALTER TABLE 表
CHANGE [COLUMN] 列表 数据类型
1
2

修改列的类型或约束

语法：

ALTER TABLE 表
MODIFY [COLUMN] 列名 类型
1
2

添加新列

语法：

ALTER TABLE 表
ADD [COLUMN] 列名 类型
1
2

删除列

语法：

ALTER TABLE 表
DROP [COLUMN] 列名
1
2

修改表名

语法：

ALTER TABLE 表名
RENAME TO 新表名
1
2

删除表

语法：

DROP TABLE [IF EXISTS] 表名
1

表复制

仅复制表结构

语法：

CREATE TABLE 待创建的表名 LIKE 已有表名
1

复制表结构及数据

语法：

CREATE TABLE 待创建的表名
SELECT 字段, ... FROM 已有表名
1
2

约束

约束分类

PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。
NOT NULL ：非空，用于保证该字段的值不能为空。
DEFAULT：默认值，用于保证该字段有默认值。
UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。
FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。

约束可应用在列级或表级。列表所有约束均支持，但外键约束没有效果；表级约束可以支持主键、唯一、外键约束。

删除约束

语法：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
1

事务控制语言

事务

数据库事务指的是一组数据操作。
主要用于处理操作量大，复杂度高的数据。
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

事务必须满足的4个条件

原子性(Atomicity)：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性(Consistency)：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性(Isolation)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性(Durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

参考链接

https://blog.csdn.net/kali_yao/article/details/120209248?spm=1001.2014.3001.5506

https://leetcode.cn/problems/delete-duplicate-emails/

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff

相关阅读:
应对出海安全合规挑战，兆珑科技为什么选择了亚马逊云科技？
CMAKE使用记录
 Eureka高可用集群服务端和客户端配置
 激活函数总结（三十三）：激活函数补充(QReLU、m-QReLU)
自己动手2小时学会配置游戏辅助
 [模型]多目标规划模型
 HTTP各版本差异
 微擎小程序获取不到头像和昵称解决方案
 难得五年来第一次暑假没有出海，即使最终没有逃过8月份的CPT外业
 SpringBoot SpringBoot 原理篇 1 自动配置 1.16 自动配置原理【2】
原文地址：https://blog.csdn.net/qq_45832050/article/details/126019548

SQL常用语句 笔记

SQL笔记

1.常用MySQL命令

2.语法规范

3.语句分类

数据查询语言

基础查询

条件查询

模糊查询

排序（默认升序）

函数

字符函数

数学函数

日期和时间函数

流程控制函数

分组函数

分组查询

连接查询

内连接

等值连接

非等值连接 between … and …（前面包括后面不包括）

创建表语法：

向表中插入数据语法：

自连接

外连接的概述

左外连接

右外连接

交叉连接 cross join

子查询

单行单列

多行单列

单行多列

多行多列

分页查询

联合查询UNION

插入语句

不指定列名的插入

指定列名的插入

使用set语句

修改语句

删除记录

删除重复的电子邮箱

删除多表记录

清空表

数据库管理

创建数据库

修改数据库

删除数据库

关系数据库的规范化

第一范式（1NF）

第二范式（2NF）

第三范式（3NF）

修改表

修改列的类型或约束

添加新列

删除列

修改表名

删除表

表复制

复制表结构及数据

约束

约束分类

删除约束

事务控制语言

事务

事务必须满足的4个条件

参考链接

SQL常用语句笔记