MySQL完整学习笔记_mysql ocp超详细学习笔记

前言

MySQL的相关概念

DB:数据库(Database),是用于保存一系列有组织的数据的容器
DBMS:数据库管理系统(Database Management System),MySQL即为一种数据库管理系统
SQL:结构化查询语言(Structure Query Language),SQL适用于所有的数据库    
1
2
3

MySQL的特点

1.MySQL用于持久化存储数据
2.数据存入表中，表存入库中，一个库可包含多个表
3.每个表都具有唯一标识名称，不可重复
4.每个表都具有一定的特性，这些特性定义了表中数据的存储方式，类似于Java中的类
5.表中的数据按行存储，每一行类似于Java中的一个对象
6.表中的行由列组成，一行的每一列类似于Java中对象的一个属性，在数据库中称为字段    
1
2
3
4
5
6

MySQL的安装和配置(使用ZIP Archive免安装方式)

MySQL的安装

1.下载压缩包

MySQL官方社区版下载

2.解压

  解压至自定义目录，解压后有以下文件：

你的安装目录\
	bin
	docs
	include
	lib
	share
	LICENSE
	README    
1
2
3
4
5
6
7
8

3.添加配置文件

  在安装目录下添加my.ini文件(MySQL的配置文件，新版MySQL不提供此配置文件)，并在my.ini中添加以下内容：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
 
[mysqld]
#skip-grant-tables 
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
 
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
 
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
    
# 设置mysql的安装目录     
basedir ="D:\Mysql"   
# 设置mysql数据库的数据的存放目录，文件夹名必须是data，但路径可以修改 
datadir ="D:\Mysql\data"    
  
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
 
#服务端的编码方式
character-set-server=utf8mb4
[client]
#客户端编码方式，最好和服务端保存一致
loose-default-character-set=utf8mb4
 
[WinMySQLadmin]  
# 修改为自定义的安装目录
Server = "D:\Mysql\bin\mysqld.exe"   
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
44
45

4.在配置文件中配置的数据存放目录中新建data文件夹。

5.在系统环境变量中的Path中添加MySQL安装路径中的bin文件夹路径。

6.以管理员身份运行命令行窗口，输入命令mysqld --initialize-insecure --user=mysql,等待运行完成(之后data文件夹中会生成一些文件)。

8.输入命令mysqld -install,命令行返回Service successfully installed表明安装成功。

MySQL基本操作

1.MySQL服务的启动和停止

  注意：必须以管理员身份运行命令行窗口。

# 启动服务
net start 服务名 
# 例：
net start MySQL
# 停止服务
net stop 服务名 
# 例：
net stop MySQL
1
2
3
4
5
6
7
8

2.MySQL服务的登录和退出

  注意：必须以管理员身份运行命令行窗口，登陆前必须先开启服务。登录本机的主机名为localhost，用户名默认为root，密码默认为空。

# 登录MySQL服务
mysql -h主机名 -P端口号 -u用户名 -p密码
# 例：
mysql -hlocalhost -P3306 -uroot -p12345678
1
2
3
4

3.修改MySQL用户名

  注意：必须以管理员身份运行命令行窗口，必须先登录MySQL。登录本机的主机名为localhost，用户名默认为root，密码默认为空。

# 修改用户名
rename user 旧用户名@主机名 to 新用户名@主机名;
# 例：
rename user root@localhost to Lishaoyin@localhost;
1
2
3
4

4.修改MySQL用户密码

  注意：必须以管理员身份运行命令行窗口，必须先登录MySQL。登录本机的主机名为localhost，用户名默认为root，密码默认为空。

# 修改密码
alter user 用户名@主机名 identified by '新密码';
# 例：
alter user Lishaoyin@localhost identified by '123456789';
1
2
3
4

5.查看MySQL版本

  注意：必须以管理员身份运行命令行窗口，必须先登录MySQL。

select version();
1

  也可以在未登录状态下使用命令行输入：

mysql --version
# 或：
mysql -V    
1
2
3

MySQL基本语法

MySQL语法规范

1.MySQL语句字母不区分大小写，但建议关键字大写；
2.每条命令使用';'或'\g'结尾，但建议使用';'结尾；
3.MySQL语句不区分单字符和字符串，都使用''(单引号)包括；
4.注释语法：
#单行注释方式1(#+注释内容)
-- 单行注释方式2(-- + 空格 + 注释内容)
/*多行注释，
与Java语法相同*/
1
2
3
4
5
6
7
8

常用命令

关于库

create database 库名; # 新建数据库
drop database 库名; # 删除数据库
show databases; # 查看当前主机所有数据库
use 库名; # 打开指定数据库
show tables; # 查看所在数据库的所有表
show tables from 库名; # 从所在数据库查看来自另一数据库的所有表
select database(); # 查看当前所在库
1
2
3
4
5
6
7

关于表

desc 表名; # 查看表结构

# 新建表：
create table 表名(
    字段1 数据类型 [约束],
    字段2 数据类型 [约束],
    ...
    字段n 数据类型 [约束]
);
# 例：
create table person(
    id int,
    first_name varchar(16),
    last_name varchar(16),
    height float,
    weight int
);

drop table 表名; # 删除指定表
select * from 表名; # 查看表中所有数据

# 插入数据到表中，字段列表应与值列表对应，多个字段之间以','隔开
insert into 表名 (属性列表) values(值列表);
# 例：
insert into person (id,first_name,last_name,height,weight) values(1,'Tony','Stark',1.75,140);
# 也可以缺省：
insert into person (id,first_name,weight) values(1,'Tony',140);

# 修改表中某行的数据
update 表名 set 目标字段=新值 where 筛选条件;
# 例：
update person set height=1.75 where id=1; # 基础修改
update person set first_name='Steven',height=1.80,weight=150 where id=1; # 多个修改(以','隔开)
update person set first_name='Jhon',weight=160 where id=2 and height>1.80; # 多个条件以and和or隔开
# 匹配where后面查询条件的所有行都将被修改，例：
update person set first_name=NULL where id>=1; # 所有id>=1行中的first_name都将被修改为空

# 删除表中的某行
delete from 表名 where 查询条件;
# 例：
delete from person where height<1.70 or weight>150; # 匹配where后面查询条件的所有行都将被删除
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

DQL语法

  DQL是Data Query Language的缩写，是SQL的一个分支，用于数据库数据查询。例有下表：

基础查询

# 基本查询语法(查询的结果是一个虚拟的表)
select 查询列表 from 表名; # 查询列表可以是字段、常量、表达式、函数

# 查询字段
select id from person; # 查询单个字段
select id,first_name,height from person; # 查询多个字段，字段顺序随意
select * from person; # 查询表中所有字段
select id*10,first_name,last_name from person; # 先查询字段再运算
# 注意：查询字段时，每个字段可以使用``(着重号)标记，但也可省略，例如：
select `id`,`name` from person_ch; # name是MySQL中的关键字，使用``方便区分

# 查询常量
select 100; # 查询整型
select 'Tony'; # 查询字符串

# 查询表达式
select 100*3+2;
select 100%23;

# "+"的使用注意：
# 1.表达式中包含字符串，先试图将字符串转换为数值，在运算，再查询
select '123.45'+100; # 查询223.45.数值形式的字符串可转换为相应数值
select 'abcdef'+123; # 123.查询非数值形式的字符串转换为0
# 2.表达式中包含null，则表达式的结果为null
select 123456+null; # 查询null

# 查询函数
select version(); # 查询数据库版本

# 为字段起别名，注意：如果别名中包含特殊字符(空格、#、+等)，应使用""或''(推荐使用"")包括
select last_name as 姓,first_name as 名 from person; # 方式一，使用as关键字
select last_name 姓,first_name 名 from person; # 方式二，省略as
select 1+1 "1 + 1"; # 别名中包含特殊字符

# 去重 distinct 关键字
select distinct last_name from person; # 去除表中查询字段值重复的行

# 字符串连接
select concat(str1,str2,str3,...); # 查询concat()返回的连接结果
select concat('abc','def','ghi'); # 查询字符串'abcdefghi'
select concat('abc',123); # 查询字符串'abc123'.如果参数中包含数字，则转换为字符串
select concat('abc',null); # 查询null.如果元素中包含null，则连接结果为null
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

条件查询

# 基本查询语法
select 查询列表 from 表名 where 筛选条件;

# 条件运算符
>,<,=,!=,<>(等价于!=),>=,<=
# 例：查询身高大于1.7的人的所有信息
select * from person where height>1.7; 
# 例：查询身高小于1.7的人的名和体重
select first_name as 名,weight as 体重 from person where height<1.7; 

# 逻辑运算符(两种等价)
&&(and),||(or),!(not)
select * from person where height>1.75 or weight<130;
# 条件表达式可以使用()包括
select first_name from person where not(height>=1.7 and height<=1.85); 

# 模糊查询涉及的关键字：
like,between and,in,is null,is not null

# like关键字：
select * from person where first_name like '%abc%'; # 查询名字中包含'abc'的人的所有信息
# 通配符说明：%表示任意多个字符(包含0个字符)，_(下划线)表示任意单个字符
select * from person where first_name like '_x__y%'; # 查询名字第2个字符为'x'，第5个字符为'y'的信息
# 当查询的字符串中包含通配字符时，可以通过转义去通配
select first_name from person where first_name like '%\_%'; # 查询含有'_'(下滑线)的名字
/* 使用escape关键字。escape的作用是指定一个字符代替转义符的作用，字符串中所有的与该替代字符相同的字符都将被认为是为转义符 */
# 将'x'作为转义符，查询含有'_'的名字
select first_name from person where first_name like '%x_%' escape 'x'; 

# between and关键字：
select 查询列表 from 表名 where 字段 between 下限值 and 上限值; 
/* 注意：
1.查询结果包含边界值，该表达式完全等价于: 
select 查询列表 from 表名 where 字段>=下限值 and 字段<=上限值; 
2.between后面的值必须小于或等于and后面的值 */
# 例：
select * from person where id between 100 and 200; # 查询id在[100,200]范围内的人的全部信息
# 完全等价于：
select * from person where id>=100 and id<=200;

# in关键字：
select 查询列表 from 表名 where 字段 in(值1,值2,值3,...,值n);
/* 该表达式完全等价于：
select 查询列表 from 表名 where 字段=值1 or 字段=值2 or 字段=值3 or...or 字段=值4; */
# 例：
select * from person where first_name in('Tony','Steven','Jhon');
# 完全等价于：
select * from person where first_name='Tony' or first_name='Steven' or first_name='Jhon';

# is null/is not null
select 查询列表 from 表名 where 字段 is null; # 或 is not null
# 注意：不能使用条件运算符判断null值，所以使用 is null 或 is not null
# 错误示例：
select * from person where first_name=null; # 不可使用=,!=,<>等判断null值
# 正确示例：
select * from person where first_name is null;

# 安全等于：<=>
select * from person where first_name<=>null;
# 等价于：
select * from person where first_name is null;
# <=>也可用于普通判断：
select * from person where height<=>1.75;
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

排序查询

# 注意：order by 子句一般置于最后(limit除外)

# 基础语法：
select 查询列表 from 表名 where 查询条件 排序规则列表;
# 例：
# 使用 order by 关键字，asc 表示按目标字段值 升序 排列
select * from person order by id asc;
# 使用 order by 关键字，desc 表示按目标字段值 降序 排列
select * from person where id between 100 and 200 order by height desc;
# 省略排序条件则默认按目标字段值 升序 排列
select * from person where id between 100 and 200 order by height;

# 按表达式排序
select * from person order by height*100-weight desc;

# 按别名排序：可以使用字段或者表达式的别名排序
select *,height*100-weight as 体型值 from person order by 体型值 asc;

# 按函数排序
select * from person order by length(first_name); # length()函数用于得到字符串的 字节 长度

# 多个排序条件
select 查询列表 from 表名 where 查询条件 order by 排序1,排序2,...,排序n;
/* 按照条件列表中的排序规则，先按靠前的规则排序，若出现相等情况，再按照靠后的规则排序 */
select * from person order by length(first_name) asc,height asc,weight desc;
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

常见函数

  MySQL中的函数类似于Java中的方法，封装一系列SQL语句。调用方式：

select func();
# 若参数中带有表中的字段：
select func() from 表名;
1
2
3

字符函数

# 返回字符串的 字节 数，可传入常量和字段
length(str);
# 例：
select length(first_name) as 名字长度 from person where id=100;

/* 拼接字符串。
   注意：
   1.参数中的非字符串将被转换为字符串后连接
   2.参数中含有值为null的参数，则连接结果为null */
concat(str1,str2,...,strn);
# 例：
select concat(first_name,'_',last_name) as 姓名 from person;

# 将字符串中的字符全部变成大写
upper(str);
# 将字符串中的字符全部变成小写
lower(str);
#例：
select concat(lower(first_name),'_',upper(last_name)) as `name` from person where id=100;

# 从pos索引位置开始(包含pos位置)提取子字符串。注意：SQL语言的索引从 1 开始
substr(str,pos); # 与substring(str,pos)等价
# 例：
select substr('abcdefg',5) as 'a string'; # 结果为'efg'
# 从pos索引位置开始(包含pos位置)提取长度为len的子字符串。注意：SQL语言的索引从 1 开始
substr(str,pos,len); # 与substring(str,pos,len)等价
# 例：
select substr('0123456789',3,5) as '一个字符串'; # 结果为'23456'

/* 返回子字符串substr在字符串str中第一次出现的索引位置，若str中不包含substr，返回0.
   注意：SQL语言的索引从 1 开始 */
instr(str,substr);
# 例：
select instr('123456789','456') as 所在位置; # 结果为4
select instr('abcdefghj','dec') as 所在位置; # 结果为0

# 去掉 前后 空格
trim(str);
# 例：
select trim('   ab  cd  ') as out_put; # 结果为'ab  cd'
# 去掉 前后 子字符串
trim(substr from str);
# 例：
select trim('123' from '123456a123bc123123') as out_put; # 结果为'456a123bc'

/* 使用指定的字符串lstr左填充字符串str，使结果字符串长度为len.
   注意：
   1.若被填充字符串str的长度小于len，则str被右截断 
   2.若填充字符串过多，则过多部分被右截断 */
lpad(str,len,lstr);
# 例：
select lpad('test',10,'12') as out_put; # 结果为'121212test'
# 若被填充字符串str的长度小于len，则str被右截断 
select lpad('test',3,'12') as out_put; # 结果为'tes'
# 若填充字符串过多，则过多部分被右截断
select lpad('test',8,'123456') as out_put; # 结果为'1234test'
# 若填充字符串过多，则过多部分被右截断
select lpad('test',10,'1234') as out_put; # 结果为'123412test'

/* 使用指定的字符串rstr右填充字符串str，使结果字符串为len.
   注意：
   1.若被填充字符串str的长度小于len，则str被右截断
   2.若填充字符串过多，则过多部分被右截断 */
rpad(str,len,rstr);
# 例：
select rpad('test',8,'1234') as out_put; # 结果为'test1234'
# 若被填充字符串str的长度小于len，则str被右截断
select rpad('test',3,'1234') as out_put; # 结果为'tes'
# 若填充字符串过多，则过多部分被右截断
select rpad('test',8,'123456789') as out_put; # 结果为'test_1234'
# 若填充字符串过多，则过多部分被右截断
select rpad('test',10,'1234') as out_put; # 结果为'test123412'

# 将字符串str中所有的from_str子字符串替换为to_str子字符串
replace(str,from_str,to_str);
# 例：
select replace('I love you','you','she') as out_put; # 结果为'I love she'
select replace('he love he and he','he','she') as out_put; # 结果为'she love she and she'
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

数学函数

# 对参数x四舍五入后取整
round(x);
# 例：
select round(1.75) as 'output'; # 结果为2
select round(1.45) as 'output'; # 结果为1
select round(-1.45) as 'output'; # 结果为-1
# 对参数x四舍五入，小数点后保留d位数
round(x,d);
# 例：
select round(1.75,1) as 'output'; # 结果为1.8
select round(3.1415926,5) as 'π'; # 结果为3.14159

# 向上取整：返回大于或等于参数x的最小整数
ceil(x);
# 例：
select ceil(1.002) as 'output'; # 结果为2
select ceil(1.000) as 'output'; # 结果为1
select ceil(-1.99) as 'output'; # 结果为-1

# 向下取整：返回小于或等于参数x的最大整数
floor(x);
# 例：
select floor(1.999) as 'output'; # 结果为1
select floor(1.000) as 'output'; # 结果为1
select floor(-1.01) as 'output'; # 结果为-2

# 截断参数x，小数点后保留d位数，小数不足d位补0
truncate(x,d);
# 例：
select truncate(3.1415926,4) as 'π'; # 结果为3.1415
select truncate(3.14,5) as 'π'; # 结果为3.14000

# 取余，等价于n%m
mod(n,m);
# 例：
select mod(10,3) as 'output'; # 结果为1
select mod(10,-3) as 'output'; # 结果为1
select mod(-10,-3) as 'output'; # 结果为-1
select mod(-10,3) as 'output'; # 结果为-1
select mod(10,20) as 'output'; # 结果为10
select mod(10,0) as 'output'; # 结果为null
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

日期函数

# 返回当前系统日期
curdate();
# 例：
select curdate() as 'date'; # 结果为2021-01-12
# 返回当前系统时间
curtime();
# 例：
select curtime() as 'time'; # 结果为12:26:32
# 返回当前系统日期+时间
now();
# 例：
select now() as 'now'; # 结果为2021-01-12 12:26:32
# 获取指定的年
year(da);
# 例：
select year(now()) 'year'; # 结果为2021
select year('2021-01-12') 'year'; # 结果为2021
select year('2021-01-12 12:26:32') 'year'; # 结果为2021
# 获取指定的月
month(da);
# 例：
select month(now()) as 'month'; # 结果为1
select month('2021-01-12') as 'month'; # 结果为1
# 获取指定月名
monthname(da);
# 例：
select monthname('2021-09-20 11:11:11') as 'month'; # 结果为September

# 将给定的字符串str转换为指定格式(由字符串format_str指定)的日期
str_to_data(str,format_str);
# 例：%d表示日期，%m表示月份，%y表示年份
select str_to_date('12-01-2021','%d-%m-%y') as 'date'; # 结果为2021-01-12
select str_to_date('01-12 2021','%m-%d %y') as 'date'; # 结果为2021-01-12

# 将给定日期da按照格式字符串format_str格式化转换为字符串
date_format(da,format_str);
# 例：
select date_format('2021/01/12','%Y年%m月%d号') as 'date'; # 结果为2021年01月12号
select date_format('2021-01-12','年：%Y 月：%m 日：%d') as 'date'; #结果为年：2021 月：01 日：12
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

日期格式符及其功能：

其它函数

# 查看数据库版本
version();
# 查看当前数据库
database();

# 查看当前用户
user();
# 例：
select user() as 'user'; # 结果为root@localhost

# 如果exp_1为null，返回exp_2;如果exp_1不为null，返回exp_1
ifnull(exp_1,exp_2);
# 例：
select ifnull(first_name,'未录入') as 'name' from person where id=100; # 结果为'未录入'
1
2
3
4
5
6
7
8
9
10
11
12
13
14

流程控制函数

if函数

# 如果exp_1为真，返回exp_2，否则返回exp_3
if(exp_1,exp_2,exp_3);
# 例：结果为'Y'
select if(10>5,'Y','N') as 'out';
# 例：结果为'tall'
select if(height>=1.75,'tall','short') as 'out' from person where first_name='Tony';
1
2
3
4
5
6

case函数

/* 用法1，类似于Java中的switch语句：
   case语句整体作为select语句查询列表中的查询表项，符合条件的then语句之后的
   常量或表达式的结果为该表项的查询结果
   注意：
   1.可以为case语句起别名
   2.else后的语句在所有when条件不符合时执行，无需求时else可省略 
   3.then之后如果为常量或表达式，语句结束不加分号；如果是语句，则需要加分号 */
case 字段或表达式或变量1
when 常量1 then 常量或表达式或语句1;
when 常量2 then 常量或表达式或语句2;
...
else 常量或表达式或语句n;
end
# 例：
select id,
case id%2
when 0 then concat(first_name,'的id是偶数')
when 1 then concat(first_name,'的id是奇数')
else 'error'
end as `output`
from person;

/* 用法2，类似于Java中的多重if语句： 
   case语句整体作为select语句查询列表中的查询表项，when表达式为真的对应then语句之后的
   常量或表达式的结果为该表项的查询结果 
   注意：
   1.可以为case语句起别名
   2.else后的语句在所有when条件不符合时执行，无需求时else可省略 
   3.then之后如果为常量或表达式，语句结束不加分号；如果是语句，则需要加分号 */
case
when 逻辑表达式1 then 常量或表达式或语句1;
when 逻辑表达式2 then 常量或表达式或语句2;
...
else 常量或表达式或语句n;
end
# 例：
select *,
case
when height>1.75 then 'tall'
when height between 1.60 and 1.75 then 'normal'
else 'short'
end as '身高评估'
from person;
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

分组函数

/* 分组函数又称聚合函数、统计函数或组函数，用于实现对表中数据的统计 */
sum([distinct] arg); # 求和，仅支持数值型参数，忽略NULL值
avg([distinct] arg); # 求平均，仅支持数值型参数，忽略NULL值
max([distinct] arg); # 求最大，支持所有类型参数，忽略NULL值
min([distinct] arg); # 求最小，支持所有类型参数，忽略NULL值
count([distinct] arg); # 支持所有类型参数
# 简单使用：
select sum(id) from person; # 求表中所有id的总和
select avg(height) from person; # 求表中所有height的平均值
select max(weight) from person; # 求表中所有weight的最大值
select min(weight) from person; # 求表中所有weight的最小值
select count(last_name) from person; # 求表中所有不为NULL的last_name的个数	

# 去重统计
select avg(distinct height) from person;

# count()的其它用法
select count(*) from person; # 查询所有不为空的行(有一个字段不为空，则该行不为空)
select count('常量') from person; # 获得总行数。相当于在查询结果表中插入新列，并且值全为该常量
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

分组查询

  分组查询是将表中的数据按照某一分组规则划分为若干个部分，再对每个部分使用分组函数进行统计。

/* 简单分组查询的基本用法：
   注意：
   1.分组查询必须配合分组函数使用
   2.先分组，再分别统计
   3.group by关键字之后的分组列表必须也出现在查询列表中
   4.group by关键字之后的分组列表完全一致的查询结果行被分为同一组 */
select 分组函数,分组列表(group by关键字之后的 分组列表 必须在此出现) 
from 表名
[where条件子句]
group by 分组列表(可为字段、表达式、函数、别名，多个表项之间使用','隔开) 
[order by子句]

# 例：按first_name的长度进行分组统计
select count(*),length(first_name) 
from person 
group by length(first_name) 
order by length(first_name) asc;
# 例：按first_name和last_name的长度进行分组统计，两者均相同的查询结果行被分为一组
select count(*),length(first_name) as a,length(last_name) as b 
from person 
group by a,b 
order by a asc,b asc;
# 例：按first_name的长度进行分组，再查询每组的人数和最高身高，查询结果按照first_name长度升序排序
select count(*),max(height),length(first_name) as len
from person
group by len
order by len asc;
# 例：按身高是否超过1.75m进行分组，并查询每组的人数和最大体重
select count(*) as 'Count',max(weight) as 'Max',(height>=1.75) as '>=1.75?'
from person
group by (height>=1.75);

/* 分组筛选的用法： 
   注意：
   1.分组筛选的作用是对分组之后的查询结果进行筛选
   2.where子句置于group by子句之前，having子句置于group by子句之后 
   3.having关键字之后的筛选条件必定是关于分组函数的逻辑判断 */
select 分组函数,分组列表(group by关键字之后的 分组列表 必须在此出现) 
from 表名
[where条件子句]
group by 分组列表(可为字段、表达式、函数、别名，多个表项之间使用','隔开) 
having 筛选条件(关于分组函数的逻辑判断)
[order by子句]

# 例：按first_name的长度进行分组，并筛选出仅有1人的first_name的长度
select count(*),length(first_name)
from person
group by length(first_name)
having count(*)=1;
# 例：按BMI标准(体重指数,体重[Kg]/身高[m]^2，标准为1.84)分组，并查询出大于标准的BMI均值和人数
select 
count(*) as 'Count',
avg(weight/(height*height)) as 'average',
weight/(height*height) as BMI
from person
group by BMI
having BMI>1.84;
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57

连接查询

概述

  连接查询用于多表查询。例有以下表：

employees(员工信息表)

departments(部门信息表)

salarys(薪资信息表)

grades(薪资级别表)

多表查询：

# 多个表中有同名字段时，应使用 表名.字段 的形式加以限定
select id from employees,departments; # 出现歧义，无法查询

/* 仅有简单的限定，会出现笛卡尔乘积现象：
   tb_1中有m条记录，tb_2中有n条记录，对于查询语句：
   select tb_1.f1,tb_2.f2 from tb_1,tb_2;
   将会得到m*n条无意义的查询结果，这是由于没有添加有效的多表连接条件 */
select 
employees.id,departments.id 
from 
employees,departments; # 可以查询，但会出现笛卡尔乘积现象

/* 添加有效的多表连接条件，将得到有意义的查询结果。
   笛卡尔乘积现像的根本原因是，多表连接时，所有表的所有字段拼接成为一个表，
   在没有限定条件时，数据库为了确保信息的完整性，会对来自不同表中的所有记录进行完全组合，
   例如：
   表1的记录1 + 表2的记录1 -> 新表的记录1;
   表1的记录1 + 表2的记录2 -> 新表的记录2;
   ...
   表1的记录1 + 表2的记录n -> 新表的记录n;
   ...
   ...
   表1的记录m + 表2的记录n -> 新表的记录m*n.
   添加有效的筛选条件，相当于在m*n条记录中筛选出有效的记录 */
select
employees.first_name as 'first name',departments.id as 'department ID'
from
employees,departments
where
employees.department_id=departments.id
order by
departments.id desc,length(employees.first_name) asc;

/* 当表名过长时，可为表起别名
   注意：
   1.在from之后的列表中起别名，例如：from tb_1 as a,tb_2 as b
   2.为表起别名之后，语句中所有位置都应使用别名替代表名 */
select
e.first_name as 'first name',e.id as 'department ID'
from
employees as e,departments as d
where
e.department_id=d.id
order by
d.id desc,length(e.first_name) asc;


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
44
45
46
47

SQL92语法

/* 内连接-等值连接：使用表中字段进行等值筛选
   注意：
   1.多表等值连接的结果为多表字段等值的交集部分
   2.n表连接，至少需要n-1个等值条件 */
# 例：按部门领导分组，求员工数大于1的部门的员工人数、领导名字、部门编号以及楼层编号，并按员工数升序排列
select 
count(*) as '员工人数',d.manager as '领导名字',d.id as '部门编号',d.floor_id as '楼层编号'
from 
employees as e,departments as d
where 
e.department_id=d.id
group by
d.id
having
count(*)>1
order by
count(*) asc;

# 内连接-非等值连接
# 例：查询每个员工姓名、部门领导、技能、薪资、薪资级别，并按薪资由高到低排序
select 
concat(e.first_name,' ',e.last_name) as '姓名',
d.manager as '领导',
e.skill as '技能',
s.salary as '薪资',
g.grade as '级别'
from
employees as e,
departments as d,
salarys as s,
grades as g
where
e.department_id=d.id
and
e.first_name=s.first_name
and
e.last_name=s.last_name
and
s.salary between g.min_salary and g.max_salary
order by
s.salary desc;

/* 内连接-自连接：表自身与自身连接 */
# 例：对于某种特殊的需求，需要查询employees表中员工编号与部门编号相同的员工姓名和部门编号
select 
concat(t1.first_name,' ',t1.last_name),t2.department_id
from 
employees as t1,employees as t2
where 
t1.id=t2.department_id
order by
t1.id asc;
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
44
45
46
47
48
49
50
51
52

SQL99语法

内连接

/* SQL99标准将连接条件置于on关键字之后，筛选条件置于where关键字之后，提高分离性。
   SQL99基本连接查询语法([...]表示可选内容)：*/
select 查询列表
from 表1 [别名] 
[连接类型] join 表2 [as 别名]
on 表1表2的连接条件(逻辑表达式)
[连接类型] join 表3 [as 别名]
on 表2表3的连接条件(逻辑表达式)
...
[连接类型] join 表n [as 别名]
on 表n-1表n的连接条件(逻辑表达式)
[where 筛选条件]
[group by 分组条件]
[having 分组筛选条件]
[order by 排序]

# 连接类型有：
inner : 内连接
left outer 或 left(可省略outer) : 左外连接
right outer 或 right(可省略outer) : 右外连接
full outer 或 full(可省略outer) : 全外连接
cross : 交叉连接

/* 内连接-等值连接，注意：
   1.内连接指具有互相关联内容的多表连接，即查询多表的交集
   2.内连接的多表顺序可变，但应保证每个 inner join 前后的两表具有互相关联的字段
   3.对于内连接而言，inner关键字可省略 */
# 例：查询部门编号小于5的员工姓名、员工部门编号以及部门领导，并按照员姓名长度升序排列
select
concat(e.first_name,' ',e.last_name) as '员工',
e.department_id as '部门',
d.manager as '领导'
from
employees as e
inner join
departments as d
on e.department_id=d.id
where
e.department_id<5
order by
length(concat(e.first_name,' ',e.last_name)) asc;

/* 内连接-非等值连接，注意：
   1.内连接指具有互相关联内容的多表连接，即查询多表的交集
   2.内连接的多表顺序可变，但应保证每个 inner join 前后的两表具有互相关联的字段
   3.对于内连接而言，inner关键字可省略 */
# 例：查询员工姓名、员工部门编号、员工领导、员工薪资和员工薪资级别，并按照薪资降序排列
select 
concat(e.first_name,' ',e.last_name) as '员工姓名',
e.department_id as '部门编号',
d.manager as '部门领导',
s.salary as '员工薪资',
g.grade as '薪资级别'
from
departments as d 
inner join 
employees as e
on d.id=e.department_id
inner join 
salarys as s
on e.first_name=s.first_name and e.last_name=s.last_name
inner join 
grades as g
on s.salary between g.min_salary and g.max_salary
order by
s.salary desc;

/* 内连接-自连接，注意：
   1.内连接指具有互相关联内容的多表连接，即查询多表的交集
   2.内连接的多表顺序可变，但应保证每个 inner join 前后的两表具有互相关联的字段
   3.对于内连接而言，inner关键字可省略 */
# 例：对于某种特殊的需求，需要查询employees表中员工编号与部门编号相同的员工姓名和部门编号
select 
concat(t1.first_name,' ',t1.last_name) as '员工姓名',
t2.department_id as '部门编号'
from
employees as t1
join
employees as t2
on t1.id=t2.department_id
order by
t1.id asc;
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

外连接

/* 外连接中的表分为 主表 和 从表 。对于左连接而言，left outer join 左边 的表为主表，
   对于右连接而言，right outer join 右边 的表为主表。
   注意：
   1.外连接的查询结果包含主表中的所有记录
   2.外连接的查询结果是 主表与从表的交集 + 主表中的补集，补集部分从表的字段值全为NULL
   4.左连接和右连接仅为写法不同，同时调换关键字和连接顺序，查询结果相同 */
   
# 例：查询薪资级别表中的各个级别对应的员工姓名、薪资和薪资级别，并按薪资降序排序
/* 分析：
   1.主表为grades表，从表为salarys表，因此可使用：
     from grades left outer join salarys
     或：
     from salarys right outer join grades
   2.查询结果包含grades表中的所有记录
   3.grades表中匹配不到salarys表的记录对应的salarys字段值为NULL，
     即在grades表中某个薪资区间中没有员工的，对应的员工姓名和薪资为NULL */
# 使用左连接：     
select
concat(s.first_name,' ',s.last_name) as '员工姓名',
s.salary as '员工薪资',
g.grade as '薪资级别'
from
grades as g
left outer join
salarys as s
on s.salary between g.min_salary and g.max_salary
order by
s.salary desc;
# 使用右连接：
select
concat(s.first_name,' ',s.last_name) as '员工姓名',
s.salary as '员工薪资',
g.grade as '薪资级别'
from
salarys as s
right outer join
grades as g
on s.salary between g.min_salary and g.max_salary
order by
s.salary desc;

# 例：查询没有员工的薪资级别和薪资区间
select
s.first_name,
g.grade as '薪资级别',
g.min_salary as '最低最低薪资',
g.max_salary as '最高薪资'
from
grades as g
left outer join
salarys as s
on s.salary between g.min_salary and g.max_salary
where s.first_name is null;

/* 全外连接(MySQL不支持)：
   注意：
   1.全外连接不分主从表，可以任意调换表的顺序
   2.全外连接的查询结果包含所有表的所有记录
   3.全外连接的查询结果是：表1与表2的交集 + 表1中的补集 + 表2中的补集，
     表1的补集部分，表2的字段值全为NULL；表2的补集部分，表1的字段值全为NULL */
# 例：查询在所有薪资级别之外的员工的姓名、薪资 和 没有员工的薪资级别、薪资区间
select
concat(s.first_name,' ',s.last_name) as '员工姓名',
s.salary as '员工薪资',
g.grade as '薪资级别',
g.min_salary as '最低薪资',
g.max_salary as '最高薪资'
from
salarys as s
full outer join
grades as g
where
(s.first_name is null) or (g.grade is null);
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

交叉连接

/* 交叉连接即查询多表的笛卡尔乘积，得到的查询结果为多表之间每一条记录的完全组合 
   例如：
   表1的记录1 + 表2的记录1 -> 新表的记录1;
   表1的记录1 + 表2的记录2 -> 新表的记录2;
   ...
   表1的记录1 + 表2的记录n -> 新表的记录n;
   ...
   ...
   表1的记录m + 表2的记录n -> 新表的记录m*n. */
# 例：
select
concat(s.first_name,' ',s.last_name) as '姓名',
s.salary as '薪资',
g.grade as '级别'
from
salarys as s
cross join
grades as g;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

子查询

  出现在其它语句中的select语句，称为子查询或内查询，外部的查询语句称为主查询或外查询。

where/having之后的子查询

/* 标量子查询(单行子查询)：
   注意：
   1.子查询应使用()包括
   2.标量子查询(单行子查询)的子查询结果是单列单行记录，因此子查询select列表中必须只有一个字段，
     且子查询中必须具有单行筛选条件
   3.主查询应当搭配 单行逻辑操作符(>,<,=,>=,<=,<>) 与子查询进行逻辑判断
   4.子查询的执行先于主查询的执行，主查询根据子查询的结果进行查询，因此应当分开考虑主查询与子查询 */
# 例：查询比 Ellen Lee 薪资高的员工姓名和薪资
select
concat(first_name,' ',last_name) as '姓名',salary as '薪资'
from salarys
where
salary>(
    select salary
    from salarys
    where first_name='Ellen' and last_name='Lee'
);

# 例：查询薪资在5号员工与4号员工之间的员工编号、姓名和薪资，并按薪资降序排列
select
id as '编号',concat(first_name,' ',last_name) as '姓名',salary as '薪资'
from salarys
where salary
between (
    select salary
    from salarys
    where id=5
)
and (
    select salary
    from salarys
    where id=4
)
order by
salary desc;

# 例：查询部门编号与 Jhon Ben 相同且薪资比 Ellen Lee 高的员工的姓名、薪资与部门，按薪资降序排列
select
concat(e.first_name,' ',e.last_name) as '姓名',
s.salary as '薪资',
e.department_id as '部门'
from
employees as e
inner join
salarys as s
on e.id=s.id
where e.department_id=(
    select department_id
    from employees
    where first_name='Jhon' and last_name='Ben'
)
and
s.salary>(
    select salary
    from salarys
    where first_name='Ellen' and last_name='Lee'
)
order by
s.salary desc;

# 例：按部门编号分组，查询员工数比领导Luis所在部门多的部门编号、员工人数、部门领导，并按照员工数降序排列
select
d.id as '部门编号',
count(*) as '员工人数',
d.manager as '员工领导'
from
employees as e
inner join
departments as d
on
e.department_id=d.id
group by
e.department_id
having count(*)>(
    select count(*)
    from
    employees
    inner join
    departments
    on
    employees.department_id=departments.id
    where departments.manager='Luis'
)
order by
count(*) desc;

/* 列子查询(多行子查询)：
   注意：
   1.子查询应使用()包括
   2.列子查询(多行子查询)的子查询结果是单列多行记录，因此子查询select列表中只有一个字段
   3.主查询应当搭配 多行逻辑操作符 与子查询进行逻辑判断
   4.子查询的执行先于主查询的执行，主查询根据子查询的结果进行查询，因此应当分开考虑主查询与子查询
   5.列子查询中常搭配 distinct 关键子对查询结果去重，提高整体查询效率 */
# 多行逻辑操作符：
字段 in (列表) : 列表中包含该字段值，表达式值为真，否则为假
字段 not in (列表) : 列表中不包含该字段值，表达式值为真，否则为假
字段 > any (列表) : 列表中包含使该逻辑判断成立的，表达式值为真，否则为假
字段 > some (列表) : 与 any 的功能一致
字段 > all (列表) : 该字段值大于列表中所有的字段值，表达式为真，否则为假

# 例：查询员工编号在从2到8中的部门领导和部门编号，并按部门编号升序排列
select 
manager as '部门领导',
id as '部门编号'
from departments
where id in (
    select distinct department_id
    from employees
    where id between 2 and 8
)
order by 
id asc;

# 例：查询比领导Dan所在部门中所有员工薪资都低的员工姓名、员工部门、员工薪资和员工技能，并按薪资降序排列
select
concat(e.first_name,' ',e.last_name) as '员工姓名',
e.department_id as '员工部门',
s.salary as '员工薪资',
e.skill as '员工技能'
from
employees as e
inner join
salarys as s
on e.first_name=s.first_name and e.last_name=s.last_name
where s.salary<all(
    select distinct s.salary
    from 
    salarys as s
    inner join
    employees as e
    on s.first_name=e.first_name and s.last_name=e.last_name
    inner join
    departments as d
    on e.department_id=d.id
    where d.manager='Dan'
)
order by
s.salary desc;

/* 例：按部门编号分组，查询最最高员工薪资比领导Luis所在部门最高员工薪资高的
       员工人数、最高薪资、部门编号和部门领导，并按最高薪资降序排列 */
select 
count(*) as '员工人数',
max(s.salary) as '最高薪资',
e.department_id as '部门编号',
d.manager as '部门领导'
from
departments as d
inner join
employees as e
on e.department_id=d.id
inner join
salarys as s
on e.first_name=s.first_name and e.last_name=s.last_name
group by e.department_id
having max(s.salary)>all(
    select s.salary
    from
    departments as d
    inner join
    employees as e
    on e.department_id=d.id
    inner join
    salarys as s
    on e.first_name=s.first_name and e.last_name=s.last_name
    where d.manager='Luis'
)
order by
max(s.salary) desc;

/* 行子查询：
   注意：
   1.子查询应使用()包括
   2.行子查询的子查询结果是单行多列或多行多列记录
   3.子查询的执行先于主查询的执行，主查询根据子查询的结果进行查询，因此应当分开考虑主查询与子查询 */

# 例：查询薪资比最低薪资高5000往上且部门编号大于最小部门编号的员工信息，并按薪资降序排列
select
e.*
from
departments as d
inner join
employees as e
on e.department_id=d.id
inner join
salarys as s
on e.first_name=s.first_name and e.last_name=s.last_name
where (s.salary,e.department_id)>(
    select min(s.salary)+5000,min(e.department_id)
    from
    employees as e
    inner join
    salarys as s
    on e.first_name=s.first_name and e.last_name=s.last_name
)
order by
s.salary desc;
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197

select之后的子查询

/* select之后的子查询，相当于主查询每查询一条记录时，都会进行一次子查询。
   因此要求子查询添加合适的筛选条件，使得主查询的每一条记录，都能对应子查询的一条子记录。
   注意：
   1.子查询应使用()包括
   2.子查询应添加合适的筛选条件，使得子查询的每一条记录，都对应主查询的一条记录，
     因此要求子查询的表必须与主查询的表有关联字段
   3.可为子查询起别名 
   4."select之后的子查询" 是除了 "连接查询" 外的多表查询的另一种实现方式 */
   
# 例：查询所有的员工姓名、员工性别、员工技能、部门编号和员工薪资，并员工姓名长短升序排列
select
concat(e.first_name,' ',e.last_name) as '员工姓名',
e.sex as '员工性别',
e.skill as '员工技能',
e.department_id as '部门编号',
(
    select s.salary
    from salarys as s
    where e.first_name=s.first_name and e.last_name=s.last_name
) as '员工薪资'
from employees as e
order by
length(concat(e.first_name,' ',e.last_name)) asc;

# 例：按部门编号分组，查询每个部门的员工人数、最高薪资、部门编号和部门领导，并按部门编号升序排列
select
count(*) as '员工人数',
(
    select max(salary)
    from salarys as s
    where s.department_id = e.department_id
) as '最高薪资',
department_id as '部门编号',
(
    select d.manager
    from departments as d
    where e.department_id=d.id
) as '部门领导'
from employees as e
group by e.department_id
order by
e.department_id asc;
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

from之后的子查询

/* from之后的子查询，相当于将子查询的结果当作新表，主查询在新表中进行查询
   注意：
   1.子查询应使用()包括
   2.必须为from之后的子查询起别名，否则无法使用子查询结果表中的字段
   3.主查询列表中的字段必须在子查询列表中出现 */
   
# 例：按部门编号分组，查询员工人数、最高薪资、部门编号、部门领导，并按部门编号升序排列
select 
count(*) as '员工人数',
max(tb.salary) as '最高薪资',
tb.department_id as '部门编号',
tb.manager as '部门领导'
from (
    select s.salary,e.department_id,d.manager
    from departments as d
    inner join
    employees as e
    on e.department_id=d.id
    inner join
    salarys as s
    on e.first_name=s.first_name and e.last_name=s.last_name
) as tb
group by
tb.department_id
order by
tb.department_id asc;

/* 主查询中必须使用子查询的别名访问from之后子查询结果表中的字段，
   不可使用子查询中表的别名或原表名 */ 
# 以下为错误用法(使用子查询中的别名)：(主查询中必须使用子查询的别名访问字段)
select 
count(*) as '员工人数',
max(s.salary) as '最高薪资',
e.department_id as '部门编号',
d.manager as '部门领导'
from (
    select s.salary,e.department_id,d.manager
    from departments as d
    inner join
    employees as e
    on e.department_id=d.id
    inner join
    salarys as s
    on e.first_name=s.first_name and e.last_name=s.last_name
) as tb
group by
e.department_id
order by
e.department_id asc;
# 以下为错误用法(使用原表名)：(主查询中必须使用子查询的别名访问字段)
select 
count(*) as '员工人数',
max(salarys.salary) as '最高薪资',
employees.department_id as '部门编号',
departments.manager as '部门领导'
from (
    select s.salary,e.department_id,d.manager
    from departments as d
    inner join
    employees as e
    on e.department_id=d.id
    inner join
    salarys as s
    on e.first_name=s.first_name and e.last_name=s.last_name
) as tb
group by
employees.department_id
order by
employees.department_id asc;

/* 对于from之后子查询的结果，原表名及子查询中表的别名都将失效，
   因而from之后的子查询中，查询不同表的同名字段，必须起别名加以区别，否则将提示重复列 */
# 以下为错误用法：e.id与d.id为重复列，必须起别名加以区别
select 
count(*) as '员工人数',
max(salarys.salary) as '最高薪资',
employees.department_id as '部门编号',
departments.manager as '部门领导'
from (
    /* e.id与d.id为重复列，必须起别名加以区别 */
    select s.salary,e.department_id,d.manager,e.id,d.id
    from departments as d
    inner join
    employees as e
    on e.department_id=d.id
    inner join
    salarys as s
    on e.first_name=s.first_name and e.last_name=s.last_name
) as tb
group by
employees.department_id
order by
employees.department_id asc;

# 正确用法：
# 例：按部门编号分组，查询员工人数、最高薪资、最小员工编号和部门编号，并按部门编号升序排列
select
count(*) as '员工人数',
max(tb.salary) as '最高薪资',
/* 调用别名 */
min(tb.id_emp) as '最小编号',
/* 调用别名 */
tb.id_dep as '部门编号'
from (
    /* 为e.id和d.id起别名 */
    select s.salary,e.id as id_emp,d.id as id_dep
    from
    departments as d
    inner join
    employees as e
    on e.department_id=d.id
    inner join
    salarys as s
    on e.first_name=s.first_name and e.last_name=s.last_name
) as tb
group by
tb.id_dep
order by
tb.id_dep asc;
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

exists之后的子查询(相关子查询)

/* exists与select查询搭配使用，用于判断查询结果是否不为空，exists表达式的值是真假逻辑值 
   注意：
   1.子查询应使用()包括
   2.exists表达式中的子查询必须与主查询相关联，因此也称为相关子查询 */

# 例：以下exists表达式结果为1
select exists(
    select concat(first_name,' ',last_name) as '员工姓名'
    from employees
    where id>0
) as '是否存在?';

# 例(假定不存在id超过1000000的员工)：以下exists表达式结果为0
select exists(
    select concat(first_name,' ',last_name) as '员工姓名'
    from employees
    where id>1000000
) as '是否存在?';

# 例：查询部门不在部门表中的员工信息
select *
from employees e
where not exists(
    select *
    from departments d
    where e.department_id=d.id
);

# 例：查询员工薪资不在薪资级别范围内的员工信息
select e.*
from 
employees e
inner join
salarys s
on e.first_name=s.first_name and e.last_name=s.last_name
where not exists(
    select *
    from grades g
    where s.salary between g.min_salary and g.max_salary
);
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

分页查询

/* 应用场景：
   由于表中的记录过多，当前操作无法完全容纳所有查询结果时，可以使用分页查询 
   语法形式： */
select 查询列表
from 表
[where]
[group by]
[having]
[order by]
limit off,size;

/* 注意：
   1.limit子句之后的参数分别为：起始查询索引(off),所需查询记录条数(size)
   2.limit子句之后的查询索引从0开始，即对于limit子句而言，表中的第一条记录索引为0
   3.起始查询索引(off)为0时，可省略起始查询索引，仅保留所需查询记录条数(size)
   4.limit子句置于一次查询中所有DDL语句的末尾
   5.limit子句在所有其它子句之后执行，因此同时具有排序子句(order by)和分页查询子句(limit)
     时，查询结果是排序之后的分页查询结果 */

# 例：查询前5条员工的全部信息
select *
from employees
limit 0,5;
# 或：起始查询索引(off)为0时，可省略起始查询索引，仅保留所需查询记录条数(size)
select *
from employees
limit 5;

# 例：查询 最后5条 员工的全部信息
select *
from employees
order by
id desc
limit 0,5; # 先执行降序排列，再执行分页查询

/* 注意：limit子句中的索引并不针对任何字段值，而是对于表中记录的排列顺序 */
# 例：结果与上相同
select *
from employees
order by
id desc
limit 5; # 先执行降序排列，再执行分页查询
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

联合查询

/* 使用场景： 
   语法形式：*/
select 字段1,字段2,...,字段n from 表1 ...
union [all]
select 字段1,字段2,...,字段n from 表2 ...
...
union [all]
select 字段1,字段2,...,字段n from 表m ...

/* 注意：
   1.所有分查询的查询列数必须一致
   2.所有分查询的对应查询列数据类型最好一致
   3.联合查询默认对结果进行去重处理，如果不希望去重，可在 union 关键字之后添加 all 关键字 
   4.联合查询结果的字段列表与第一个分查询的字段列表相同 */
   
# 例：查询员工编号小于5或员工薪资大于50000的员工编号和员工姓名
select 
id,concat(first_name,' ',last_name) as `name` 
from employees
where id<5
union
select 
id,concat(first_name,' ',last_name) as `name` 
from salarys
where salary>50000;

# 例：分别查询员工编号小于5，员工薪资大于50000的员工编号和员工姓名，并联合查询结果(不去重)
select 
id,concat(first_name,' ',last_name) as `name` 
from employees
where id<5
union all
select 
id,concat(first_name,' ',last_name) as `name` 
from salarys
where salary>50000;

# 例：查询员工编号小于5或员工薪资大于50000的员工编号和员工姓名，并按员工编号降序排列
select tb.*
from (
    select 
    id,concat(first_name,' ',last_name) as `name` 
    from employees
    where id<5
    union
    select 
    id,concat(first_name,' ',last_name) as `name` 
    from salarys
    where salary>50000
) as tb
order by
tb.id desc;
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
44
45
46
47
48
49
50
51
52

DML语法

  DML是Data Manipulation Language的缩写，是SQL的一个分支，用于数据库数据操作。主要涉及数据的插入(insert)，数据的修改(update)和数据的删除(delete)。

数据的插入

/******************** 插入语法方式1 ********************/

# 基本插入语法：
insert into 表名(字段名列表) values
(字段值列表1),
(字段值列表2),
...
(字段值列表n);

/* 注意：
   1.字段顺序可以调换，但插入字段值列表值类型应与字段名列表对应一致或兼容
   2.对于声明为非空的字段，既不可在字段名列表中省略，也不可插入NULL值
   3.对于可以为空的字段，既可以在字段名列表中省略(默认值为NULL)，也可以插入NULL值
   4.可省略字段名列表，此时默认插入所有字段值，且插入顺序与原表字段顺序一致
   5.支持一次插入多行 */

# 例：
insert into 
employees(id,first_name,last_name,sex,skill,department_id)
values(11,'Vin','Diesel','male','Java',2);

# 可省略字段名列表，此时默认插入所有字段值，且插入顺序与原表字段顺序一致
insert into 
employees
values(11,'Vin','Diesel','male','Java',2);

insert into
employees(id,first_name,last_name,sex)
values
(12,'Deal','Vacon','male'),
(13,'Ally','Wooz','female'),
(14,'Jhon','Weak','male'),
(15,'Nikula','Ess','male'),
(16,'Moly','Evy','female');

/* 高级插入语法：可搭配子查询使用，即将子查询结果插入作为表中的新记录
   注意：子查询中的查询列表应与插入列表匹配 */
insert into 表名(字段名列表)
select 字段名列表 ... ;

# 例：将employees表中的salarys表中不存在的员工编号、员工姓名插入到salarys表中
insert into salarys(id,first_name,last_name)
select id,first_name,last_name
from employees
where id not in(
    select id
    from salarys
);

/******************** 插入语法方式2 ********************/

insert into 表名
set 字段1=新值1,字段2=新值2,...,字段n=新值n;

/* 注意：
   1.字段顺序可以调换，但插入字段值列表值类型应与字段名列表对应一致或兼容
   2.对于声明为非空的字段，既不可不插入，也不可插入NULL值
   3.对于可以为空的字段，既可以不插入(默认值为NULL)，也可以插入NULL值
   4.不支持一次插入多行 */

# 例：
insert into
employees
set id=11,first_name='Vin',last_name='Diesel',sex='male',skill='Java',department_id=2;

/* 特别注意：
   如果表中有自增列，使用 delete 语句删除表中记录之后再插入新记录，自增列的值从 断点值 开始自增!!! */
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

数据的修改

/******************** 修改单表数据 ********************/

# 基本语法：
update 表名 
set 字段1=新值,字段2=新值,...,字段n=新值
where 筛选条件;

/* 注意：不加筛选条件将修改表中的所有记录 */
   
# 例：
update employees
set skill='Java',department_id=2
where (skill is NULL) and (department_id is NULL);

/******************** 修改多表数据 ********************/

# 修改多表数据的关键在于多表连接，因此分为SQL92语法和SQL99语法
/* 注意：
   1.多表连接修改数据，不是仅仅修改虚拟表中的数据，而是修改原表数据
   2.可为表起别名 */

# SQL92语法：
update 表1 [别名],表2 [别名],...,表n [别名]
set 字段1=新值,字段2=新值,...,字段m=新值
where 连接条件+筛选条件;

# 例：将技能为'Java'的员工部门领导换成'Tesla'
update employees as e,departments as d
set d.manager='Tesla'
where e.department_id=d.id and e.skill='Java';

# SQL99语法：
update 
表1 [别名] 
[连接类型] join 表2 [别名] on 连接条件
[连接类型] join 表3 [别名] on 连接条件
...
[连接类型] join 表n [别名] on 连接条件
set 字段1=新值,字段2=新值,...,字段m=新值
where 筛选条件;

# 例：将技能为'Java'的员工部门领导换成'Tesla'
update 
employees e 
inner join
departments d
on e.department_id=d.id
set d.manager='Tesla'
where e.skill='Java';
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
44
45
46
47
48
49

数据的删除

/******************** 删除方式1 ********************/

# 单表删除
delete from 表名 where 筛选条件;

# 例：删除员工编号大于10的员工信息
delete from employees where id>10;

# 多表删除

# SQL92语法：
delete 表名(别名)
from 表1 [别名],表2 [别名],...,表n [别名]
where 连接条件 and 筛选条件;

# 注意：应将需删除记录的表名或别名置于 delete 关键字之后，否则互相连接的多表中的匹配记录都将删除

# 例：删除薪资最低的员工的领导信息
delete d
from salarys as s,employees as e,departments as d
where 
s.first_name=e.first_name 
and s.last_name=e.last_name 
and e.department_id=d.id
and s.salary=(
    select min(salary)
    from salarys
);

# SQL99语法：
delete 表名(别名)
from
表1 [别名] 
[连接类型] join 表2 [别名] on 连接条件
[连接类型] join 表3 [别名] on 连接条件
...
[连接类型] join 表n [别名] on 连接条件
where 筛选条件;

/* 特别注意：
   1.如果表中有自增列，使用 delete 语句删除表中记录之后再插入新记录，自增列的值从 断点值 开始自增!!!
   2.delete删除可以回滚 */

# 例：删除薪资最低的员工的领导信息
delete d
from
departments d
inner join
employees e
on e.department_id=d.id
inner join
salarys s
on e.first_name=s.first_name and e.last_name=s.last_name
where s.salary=(
    select min(salary)
    from salarys
);

/******************** 删除方式2 ********************/
truncate table 表名;

/* 注意：
   1.truncate语句不可搭配where语句使用，也不可进行多表连接
   2.该方式只能用于清空表中的所有数据(不删除表)，无法进行筛选删除
   3.该方式的执行效率比delete语句更高 */
   
/* 特别注意：
   1.如果表中有自增列，使用 truncate 语句清空表中记录之后再插入新记录，自增列的值从 1 开始自增!!! 
   2.truncate删除不能回滚 */   
   
# 例：清空员工表中的数据
truncate table employees;
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

DDL语法

  DDL是Data Definition Language的缩写，用于库或表的创建(create)、修改(alter)、删除(drop)。

库的管理

/******************** 库的创建 ********************/

# 基本语法：
create database 库名;
# 容错语法：
create database if not exists 库名;
# 设置字符集
create database if not exists 库名 character set 字符集;

# 例：
create database company; # company库已存在，将提示错误
create database if not exists company; # 不提示错误

# 例：设置字符集
create database if not exists test1 character set utf8;
create database if not exists test2 character set gbk;
create database if not exists test3 character set ascii;

/******************** 库的修改(修改字符集) ********************/

alter database 库名 character set 字符集;
# 例：
alter database test1 character set utf8;

/******************** 库的删除 ********************/

# 基本语法：
drop database 库名;
# 容错语法：
drop database if exists 库名;

# 例：
drop database if exists test1;
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

表的管理

/******************** 表的创建 ********************/

# 基本语法：
create table 表名(
    字段名1 类型(长度) 约束,
    字段名2 类型(长度) 约束,
    ...
    字段名n 类型(长度) 约束,
);

# 容错语法：
create table if not exists 表名(
    字段名1 类型(长度) 约束,
    字段名2 类型(长度) 约束,
    ...
    字段名n 类型(长度) 约束,
);

# 从select子句创建：
create table if not exists 表名
select ... ;

# 例：
create table if not exists employees(
    id int,
    first_name varchar(16),
    last_name varchar(16),
    sex varchar(16),
    department_id int,
    skill varchar(16)
);

/* 例：将employees,salarys,departments和grades表合并，
   创建新表，表的内容包含：员工编号、员工姓名、部门编号、部门领导、员工薪资、薪资级别 */
create table if not exists emp_info
select 
e.id as emp_id,
concat(e.first_name,' ',e.last_name) as `name`,
e.department_id as dpt_id,
d.manager as dpt_manager,
s.salary as emp_salary,
g.grade as sly_grade
from
departments as d
inner join
employees as e
on e.department_id=d.id
inner join 
salarys as s
on e.first_name=s.first_name and e.last_name=s.last_name
inner join
grades as g
on s.salary between g.min_salary and g.max_salary;

/******************** 表的修改 ********************/

# 1.修改列名：
alter table 表名 change column 原列名 新列名 新列类型 [新列约束];
# 例：
alter table employees change column sex gender varchar(16);

# 2.修改列类型或约束：
alter table 表名 modify column 列名 新类型 [新约束];
# 例：
alter table employees modify column id tinyint;

# 3.添加新列：
alter table 表名 add column 新列名 类型 [约束];
# 例：
alter table employees add column manager varchar(20);

# 4.删除列：
alter table 表名 drop column 列名;
# 例：
alter table employees drop column manager;

# 5.修改表名
alter table 表名 rename to 新表名;
# 例：
alter table employees rename to emp;

/******************** 表的删除 ********************/

# 基本语法：
drop table 表名;
# 容错语法：
drop table if exists 表名;

# 例：
drop table if exists employees;

/******************** 表的复制 ********************/

# 1.仅仅复制表的结构(不复制表的数据)
create table if not exists 新表名 like 源表;
# 例：
create table if not exists emp_1 like employees;

# 2.复制表的结构和数据
create table if not exists 新表名
select 字段列表 
from 源表
where 筛选条件;

# 例：复制employees表中的所有数据
create table if not exists emp_2
select *
from employees;

# 例：复制employees表中部门编号为2的数据
create table if not exists emp_3
select *
from employees 
where department_id=2;

# 例：复制employees表中部门编号为2的员工编号、员工姓名
create table if not exists emp_4
select id,first_name,last_name
from employees
where department_id=2;

# 例：仅仅复制employees表中的id、first_name和last_name结构(不复制数据)
create table if not exists emp_5
select id,first_name,last_name
from employees
where 0;
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126

数据类型

整型数据

tinyint : 1byte
smallint : 2byte
mediumint : 3byte
int : 4byte
integer : 4byte
bigint : 8byte

/* 注意：
   1.建表时使用以上数据类型默认为有符号型，在以上数据类型之后添加unsigned关键字可声明为无符号型
   2.指定整形长度仅对零填充有效(必须搭配zerofill关键字)，所指定的长度为零填充之后十进制的数位长度 
   3.使用zerofill关键字之后，数据类型变为无符号型 */

# 例：默认为有符号型
create table test(
    f1 tinyint,
    f2 smallint,
    f3 mediumint,
    f4 int,
    f5 bigint
);

# 例：添加unsigned关键字可声明为无符号型
create table test(
    f1 tinyint unsigned,
    f2 smallint unsigned,
    f3 mediumint unsigned,
    f4 int unsigned,
    f5 bigint unsigned
);

# 例：零填充
create table test(
    f int(8) zerofill  
);
insert into test values(123);
select * from test; # 插查询结果为：00000123
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

小数数据

# 浮点型：
float(m,d) : 4byte
double(m,d) : 8byte
# 定点型
dec(m,d) : (M+2)byte
decimal(m,d) : (M+2)byte

/* 注意：
   1.m指定总有效位数(整数位数+小数位数)，d指定小数部分有效位数(多余四舍五入，不足补0) 
   2.超出范围以临界值代替，例double(5,2)插入1234.56，将以999.99代替
   3.可省略(m,d)，对于float和double省略(m,d)，精度取决于实际数据；
     对于dec或decimal，默认m=10,d=0 
   4.dec/decimal的精度相对较高 */

# 例：
create table test(
    f1 float(5,2),
    f2 dec(6,3)
);
insert into test values(12.5,12.5); # 实际插入：f1=12.50,f2=12.500
insert into test values(123.567,123.5678); # 实际插入：f1=12.57,f2=12.568
insert into test values(1234.5,1234,56); # 实际插入：f1=999.99,f2=999.999
select * from test;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

串型数据

/******************** 较短串型数据 ********************/

# 固定长度字符，占用空间取决于m。m范围为0~255，m可省略(默认为1)
char(m) : m字符 
# 可变长度字符，占用空间取决于实际字符。m范围为0~65535，m不可省略
varchar(m) : m字符
# 固定二进制串，m可省略
binary(m)
# 可变二进制串，m不可省略
varbinary(m)
# 字符枚举型，字段值从枚举列表中选择一个，枚举成员只能是字符，字符不区分大小写
enum(e1,e2,...,en)
# 字符集合型，字段值从集合列表中选择一个或多个，集合成员只能是字符，字符不区分大小写
set(s1,s2,...,sn)

# 例：
create table test_1(
    e enum('1','2','3','4','5'),
    s set('a','b','c','d','e')
);
insert into test_1 values('3','a,b,e');
insert into test_1 values('3','a,B,e');
insert into test_1 values('8','a,b,e'); # 仅能选择enum列表中的一个插入
insert into test_1 values('3','a,b,f'); # 仅能选择set列表中的一个或多个插入

/******************** 较长串型数据 ********************/

# 较长文本数据(文本文件)
text
# 较长二进制数据(音乐、图片等)
blob
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

日期数据

  注意：日期型数据必须使用''(单引号)包括。

# 只能保存日期，不能保存时间。最小值：'1000-01-01'，最大值：'9999-12-31'
date : 4byte
# 只能保存时间，不能保存日期。最小值：'-838:59:59'，最大值：'838:59:59'
time : 3byte
# 只能保存年份。最小值：'1901'，最大值：'2155'
year : 1byte	
# 可以保存日期和时间。最小值：'1000-01-01 00:00:00'，最大值：'9999-12-31 23:59:59'
datetime : 8byte
# 时间戳。最小值：19700101080001
timestamp : 4byte
1
2
3
4
5
6
7
8
9
10

约束

  可在创建表或修改表时为字段添加约束。约束分为列级约束和表级约束。形式为：

create table 表名(
    字段1 类型 列级约束,
    字段2 类型 列级约束,
    ...
    字段n 类型 列级约束,
    表级约束
);
1
2
3
4
5
6
7

常见约束及使用：

# 非空约束，用于保证字段值不为空-列级约束
not null
# 默认约束，用于指定字段的默认值-列级约束
default
# 主键约束，用于保证字段值的非空性和唯一性(字段值不重复)-表级/列级约束
primary key
# 唯一约束，用于保证字段值的唯一性，但不保证非空性，一个表中只能有一个主键-表级/列级约束
unique
# 外键约束，用于保证字段值必须来自于主表的关联字段-表级约束
foreign key

/******************** 列级约束的使用 ********************/

# 常见列级约束有：
not null
default
primary key
unique

# 例：创建表时添加列级约束
create table if not exists emp_info(
    id int primary key, # 员工编号，主键
    first_name varchar(16) not null, # 员工名，非空
    last_name varchar(16) not null, # 员工姓，非空
    sex char not null default 'm', # 员工性别，非空，默认为男
    seat int unique, # 员工座位号，不重复
    department_id int # 部门编号
);

# 例：修改表时修改列级约束

# 将first_name修改为可为空
alter table emp_info modify column first_name varchar(16) null;
# 可直接省略null：
alter table emp_info modify column first_name varchar(16);
# 将员工性别修改为默认为女
alter table emp_info modify column sex char not null default 'f';

/******************** 表级约束的使用 ********************/

# 常见表级约束有：
primary key
unique
foreign key

# 添加表级约束的基本语法：
[constraint 键名] 约束类型(字段名)

/* 注意：
   1.创建表时的表级约束置于所有字段声明的末尾
   2.可以不为表级约束起键名，除主键外，默认键名为字段名
   3.主键的键名为primary，不可更改 */

# 例：创建表时添加表级约束
create table if not exists emp_info(
    id int,
    first_name varchar(16) not null,
    last_name varchar(16) not null,
    sex char not null default 'm',
    seat int,
    department_id int,
    primary key(id), # 指定id为主键
    constraint uqk unique(seat), # 指定seat值唯一，键名为 uqk
    constraint fgk foreign key(department_id) references departments(id) # 外键
);

# 例：修改表时修改表级约束

# 添加主键
alter table emp_info add constraint prk primary key(id);
# 删除主键
alter table emp_info drop primary key;

# 添加唯一键
alter table emp_info add constraint uqk unique(id);
# 删除唯一键
alter table emp_info drop index uqk;

# 添加外键
alter table emp_info add constraint fgk foreign key(department_id) references dep_info(id);
# 删除外键
alter table emp_info drop foreign key fgk;

/******************** 外键的使用 ********************/

# 创建表时添加外键的基本语法：
constraint 键名 foreign key(本表字段) references 主表名(主表字段)

/* 注意：
   1.要求设计主从表外键关系
   2.从表外键列与主表关联列的字段名可不同，但数据类型应一致或相同
   3.主表的关联列必须是一个键(一般是主键或唯一键)
   4.插入数据时，先插入主表数据，再插入从表数据
   5.删除数据时，先删除从表数据，再删除主表数据 */
   
# 例：
create table if not exists dep_info(
    id int,
    manager varchar(16) not null unique,
    floor_id int,
    primary key(id)
);

create table if not exists emp_info(
    id int,
    first_name varchar(16) not null,
    last_name varchar(16) not null,
    sex char not null default 'm',
    seat int,
    department_id int,
    primary key(id), # 指定id为主键
    constraint uqk unique(seat), # 指定seat值唯一，键名为 uqk
    constraint fgk foreign key(department_id) references dep_info(id) # 外键
);

/* 先插入主表数据，再插入从表数据 */
insert into dep_info values(1,'Tom',5);
insert into emp_info values(1,'Tony','Stark','m',10,1);

/* 先删除从表数据，再删除主表数据 */
delete from emp_info where department_id=1;
delete from dep_info where id=1;

/******************** 标识列(自增长列) ********************/
# 基本用法：
create table 表名(
    字段名 类型 约束 auto_increment,
    ...
);
/* 注意：
   1.标识列必须搭配键使用(unique或primary key)
   2.一个表只能有一个标识列
   3.标识列的类型只能是数值型
   4.使用auto_increment标识的字段(包括主键字段)，可以不插入或插入NULL值，默认值从1开始，每次增加1
   5.使用 set auto_increment_offset=x 语句可设置自增长默认起始值
   6.使用 set auto_increment_increment=y 语句可设置自增长步长 */

# 例：创建表时添加标识列
create table test_1(
    id int primary key auto_increment,
    first_name varchar(16) not null,
    last_name varchar(16) not null
);
# 设置初始值
set auto_increment_offset=50;
# 设置自增长步长
set auto_increment_increment=50;
insert into test_1 values(null,'Tony','Stark'); # 可以插入null
insert into test_1 values(null,'Steven','Evens');
insert into test_1(first_name,last_name) values('Judy','Dan'); # 可以不插入

select * from test_1;
# 查询结果：
|  id  | first_name | last_name |
---------------------------------
|  50  |    Tony    |   Stark   |
---------------------------------
|  100 |   Steven   |   Evens   |
---------------------------------
|  150 |    Judy    |    Dan    |

# 例：更改表时添加标识列
alter table test_2 modify column id int primary key auto_increment;
# 例：更改表时删除标识列
alter table test_2 modify column id int primary key;
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165

TCL语法

  TCL是Transaction Control Language(事务控制语言)的缩写。

事物简介

  事务由单独单元的一个或多个SQL语句组成，在这个单元中，每条SQL语句都是相互依赖的。如果某条SQL语句发生错误，整个单元将会回滚，所有受到影响的数据将返回到事务开始前的状态；如果单元中所有的SQL语句均执行成功，则事务成功执行。事务的ACID属性：

1.原子性(Atomicity)
    事务是一个不可分割的工作单位，事务中的操作或全部执行或全部不执行。
2.一致性(Consistency)
    事务必须使数据库从一个一致性状态变换到另一个一致性状态。
3.隔离性(Isolation)
    事务的执行不被其它事务干扰。
4.持久性(Durability)
    事务对数据库的影响是永久的。
1
2
3
4
5
6
7
8

事务的创建

示例表：

set auto_increment_offset=1;
set auto_increment_increment=1;
create table if not exists account_info(
    id int primary key auto_increment,
    username varchar(100) not null unique,
    account varchar(100) not null unique,
    `password` varchar(100) not null,
    balance bigint
);
1
2
3
4
5
6
7
8
9

隐式事务

  隐式事务没有明显的开启或结束标志。insert,update,delete等语句均为隐式事务，多条隐式事务之间不相互依赖，任然可能发生意外使得操作不能完整执行。

显式事务

  显式事务具有明显的开启和结束标志。

# 显式事务的基本创建语法：
set autocommit=0; # 关闭自动提交
start transaction; # 开启事务，此语句可省略
事务内容(select,insert,update,delete); # 事务的内容主要为增删改查SQL语句
commit; # 执行成功则提交事务
rollback; # 出现异常可回滚事务

/* 注意：
   1.每次开启事务前必须使用 set autocommit=0 语句关闭自动提交
   2.start transaction 语句可省略 */
   
# 例：提交事务
set autocommit=0;
start transaction;
update account_info set balance=1000-500 where username='Tony';
update account_info set balance=1000+500 where username='Steven';
commit;

# 例：回滚事务
set autocommit=0;
start transaction;
update account_info set balance=1000-500 where username='Tony';
update account_info set balance=1000+500 where username='Steven';
rollback;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

并发运行事务的问题

1.脏读
    对于两个并发运行的事务T1,T2，T1更改自身的字段值但未提交时，T2读取该字段值，T1回滚将
  导致T2读取的为无效值。这种现象称为脏读。
2.不可重读
    对于两个并发运行的事务T1,T2，T1读取了T2的字段，T2改变该字段值将导致T1再次读取该字段
  值不相同。这种现象称为不可重读。
3.幻读
    对于两个并发运行的事务T1,T2，T1读取了T2，之后T2插入或删除记录，导致T1再次读取时记录
  增多或减少。这种现象称为幻读。
1
2
3
4
5
6
7
8
9

隔离级别

# MySQL支持4种隔离级别：
read uncommitted - 读未提交，不可避免脏读、不可重读、幻读问题
read committed - 读已提交，可避免脏读问题，但不可避免不可重读、幻读问题
repeatable read - 可重读，可避免脏读、不可重读问题，但不可避免幻读问题，默认级别
serializable - 序列化

# 查询当前隔离级别：
select @@transaction_isolation; # @@xxx表示系统变量
# 设置当前连接隔离级别：
set session transaction isolation level 隔离级别;
# 例：
set session transaction isolation level read committed;
# 设置全局隔离级别
set global transaction isolation level 隔离级别;
# 例：
set global transaction isolation level serializable;

/* read uncommitted
   最低隔离级别。事务正在运行未提交时，其它事务可读取在内存中更改的字段，事务回滚后，已读值为无效值 */
set session transaction isolation level read uncommitted;

/* read committed
   其它事务只能读取已提交事务更改的字段值。此隔离级别可避免脏读，但不能避免不可重读和幻读 */
set session transaction isolation level read committed;   

/* repeatable read
   事务中任何时刻读取到的字段值都为本次事务开启后第一次读取到的已提交值，在本次事务未结束前，
   即使其它事务更改该字段值，之后本次事务再次读取依然为第一次读取到的已提交值，再次开启事务，
   可读取新更改的值。此隔离级别可避免脏读、不可重读问题，但不能避免幻读问题 */
set session transaction isolation level repeatable read;  

/* serializable
   最高隔离级别。在本次事务执行期间，禁止其它事务对该表进行插入、更改、删除操作，类似于多线程
   中的加锁，可避免一切并发运行问题，但执行效率最低 */ 
set session transaction isolation level serializable;  
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

回滚点的使用

  savepoint(回滚点)在事务执行过程中设置。对于事务中的特殊状态，例如删除重要数据之前，或插入重要数据之后，可使用savepoint(回滚点)标记，便于意外发生时事务回滚到指定状态。

# 回滚点的基本使用
set autocommit=0;
start transaction;
语句组1;
savepoint point_1;
语句组2;
savepoint point_2;
...
语句组n;
savepoint point_m;
意外发生;
rollback point_n;

/* 注意：
   1.savepoint(回滚点)必须搭配 rollback to 使用
   2.rollback to point_n 表示使事务回滚到 point_n 标记之前的状态 */
   
# 例：
set autocommit=0;
start transaction;
insert into account_info(username,account,`password`,balance) values
('Evi','51321555','12345678',1500),
('Hans','03513232','10000001',1700),
('Joy','51211222','19990999',2000),
('Edy','54132021','5201314',1400),
('Sarah','21542154','00000000',1800);
savepoint point_insert_1;
delete from account_info where username='Hans';
rollback to point_insert_1;

/* 注意：delete删除事务可回滚，truncate清空事务不可回滚!!! */
set autocommit=0;
start transaction;
truncate account_info;
savepoint truncate_point;
rollback to truncate_point; # truncateshi'wu回滚无效!!!
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

视图

  View(视图)用于保存SQL逻辑，不保存实际数据。对于某些复杂或重用性高的SQL逻辑，可以使用View(视图)代替。View(视图)类似于一个虚拟表，可以直接将视图当作表进行查询和修改。
  View(视图)与表的最大区别是：视图不占用实际的物理空间。

/******************** 视图的创建 ********************/

# 创建视图的基本语法：
create view 视图名
as
查询语句;
# 视图的调用：
select 查询列表 from 视图名 where 筛选条件;

# 例：查询员工姓名、员工技能、员工部门、部门领导、员工薪资和薪资级别
# 创建视图
create view view_emp
as
select
concat(e.first_name,' ',e.last_name) as emp_name,
e.skill as emp_skill,
e.department_id as emp_dpt,
d.manager as emp_mng,
s.salary as emp_slry,
g.grade as emp_grd
from
departments as d
inner join
employees as e
on e.department_id=d.id
inner join 
salarys as s
on e.first_name=s.first_name and e.last_name=s.last_name
inner join
grades as g
on s.salary between g.min_salary and g.max_salary;
# 查询薪资级别为A的员工姓名、员工技能和员工部门
select 
emp_name,emp_skill,emp_dpt 
from view_emp 
where emp_grd='A'; 
# 查询技能为Java的员工姓名和员工薪资
select
emp_name,emp_slry
from view_emp
where emp_skill='Java';

/******************** 视图的修改 ********************/
# 方式一：
create or replace view 视图名
as
查询语句;

# 例：将视图view_emp的功能变为按照部门编号分组，查询每组的员工人数、最高薪资、平均薪资和部门编号
create or replace view view_emp
as 
select
count(*) as emp_cnt,
max(s.salary) as emp_maxslry,
avg(s.salary) as emp_avgslry,
e.department_id as emp_dpt
from
employees as e
inner join
salarys as s
on e.first_name=s.first_name and e.last_name=s.last_name
group by emp_dpt;
# 查询员工人数最多的信息：
select *
from view_emp
where emp_cnt>=all(
    select emp_cnt
    from view_emp
);

# 方式二：
alter view 视图名
as 
查询语句;

# 例：将视图view_emp的功能变为按照薪资级别分组，查询员工人数、最高薪资、最低薪资和薪资级别
alter view view_emp
as 
select 
count(*) as emp_cnt,
max(s.salary) as emp_maxslry,
min(s.salary) as emp_minslry,
g.grade as emp_grd
from
salarys as s
inner join
grades as g
on s.salary between g.min_salary and g.max_salary
group by
g.grade;
# 查询最低薪资最低的信息
select *
from view_emp
where emp_minslry<=all(
    select emp_minslry
    from view_emp
);

/******************** 视图的删除 ********************/
# 基本语法：
drop view 视图1,视图2,...,视图n;

# 例：
drop view view_1,view_2,view_3;

/******************** 视图的查看 ********************/
desc 视图名;
# 或：
show create view 视图名;

# 例：
desc view_emp;
# 或：
show create view view_emp;

/******************** 视图的更新 ********************/
# 可通过对视图进行插入(insert),修改(update),删除(delate)等操作影响源表中的数据
# 示例试图：
create view vtest
as
select id,first_name,last_name,skill
from employees;

/* 注意：
   1.视图的更新只能用于较简单的视图
   2.更新视图的数据可影响源表中的数据
   3.更新视图要求用户具有必要的权限 */

# 插入数据：
insert into vtest values(null,'Andy','Smith','Java');

# 修改数据：
update vtest set skill='C++' where id=17;

# 删除数据：
delete from vtest where id=17;
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136

关键语法

变量

系统变量

/******************** 全局变量 ********************/
/* 全局系统变量由系统提供，属于服务器层面，作用域是整个服务器，可跨连接使用，
   服务器重启后，全局系统变量恢复默认值 */

# 查看所有的全局系统变量：
show global variables;
# 查看指定全局系统变量的值：
select @@global.变量名;
# 为指定全局系统变量赋值方式一：
set global 变量名=值;
# 为指定全局系统变量赋值方式二：
set @@global.变量名=值;

# 例：
show global variables;
select @@global.autocommit;
set global autocommit=0;
set @@global.autocommit=0;
set global transaction_isolation='repeatable-read';
set @@global.transaction_isolation='repeatable-read';

/******************** 会话变量 ********************/
# 会话系统变量由系统提供，属于连接层面，作用域仅为当前连接

# 查看所有的会话系统变量
show session variables; # session可省略
# 查看指定会话系统变量的值
select @@session.变量名; # session可省略
# 为指定会话系统变量赋值方式一：
set session 变量名=值; # session可省略
# 为指定会话系统变量赋值方式二：
set @@session.变量名=值;

# 例：
show session variables;
select @@session.autocommit;
set session autocommit=0;
set @@session.autocommit=0;
set session transaction_isolation='repeatable-read';
set @@session.transaction_isolation='repeatable-read';

# 或省略session：
show variables;
select @@autocommit;
set autocommit=0;
set @@autocommit=0;
set transaction_isolation='repeatable-read';
set @@transaction_isolation='repeatable-read';
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
44
45
46
47
48

自定义变量

/******************** 用户变量 ********************/

# 声明语法(三种)：
set @变量名=初始值;
set @变量名:=初始值;
select @变量名:=初始值;

# 赋值语法方式一(与声明变量相同用法)：
set @变量名=新值;
set @变量名:=新值;
select @变量名:=新值;
# 赋值语法方式二(从字段赋值)：
select 字段 into 变量名
from 表名
where 筛选条件;

/* 注意：
   1.声明用户变量时必须对其初始化
   2.用户变量必须添加@前缀
   3.用户变量可以在一个查询的任何位置定义并使用
   4.用户变量是弱类型，可以为用户变量赋不同类型的数据
   5.用户变量针对于当前连接有效，相当于一个连接中的全局变量 */
   
# 例：用户变量的声明   
set @v1=10;
set @v1:='Tony';
select @vi:=1.75;
# 例：用户变量的赋值
set @v1=11;
set @v1:='HelloWorld';
select @v1:=3.1415;
# 例：由字段更新
select salary into @v1
from salarys
where first_name='Max' and last_name='Hans';
# 例：由字段更新
select max(salary) into @v1
from salarys;
# 例：求两数的积
set @m:=10;
set @n:=20;
set @o:=@m*@n;
select @o;

/******************** 局部变量 ********************/

# 声明语法：
declare 变量名 类型 [default 初始值];

# 赋值语法方式一：
set 变量名=值;
set 变量名:=值;
select @变量名:=值;
# 赋值语法方式二(由字段赋值)：
select 字段 into 变量名
from 表名
where 筛选条件;

/* 注意：
   1.局部变量只能在begin and中声明和使用，且必须在begin and中的第一句声明
     局部变量的生命周期也限于begin and中
   2.局部变量声明时必须指定其数据类型 
   3.局部变量声明时可以不初始化，也可以使用default关键字初始化
   4.局部变量不加@前缀
   5.局部变量主要在存储过程和函数中使用 */
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

存储过程和函数

存储过程

# 基本创建语法：
create procedure 存储过程名(参数列表)
begin
	存储过程体(一组SQL语句);
end 存储过程结束标志

# 参数列表包含三部分：
参数模式 参数名 参数类型

# 参数模式：
in : 使用in修饰的参数类似于函数的参数，必须由调用方传入值
out : 使用out修饰的参数类似于函数的返回值，可将值传给调用方
inout : 使用inout修饰的参数既可由调用方传入值，也可将值传给调用方

# 基本调用语法：
call 存储过程名(实参列表);

/* 注意：
   1.如果存储过程体中仅有一条SQL语句，可省略begin and
   2.如果存储过程体中有多条SQL语句，每条SQL语句必须使用";"结尾
   3.由于";"是一条SQL语句结束的标志，存储过程结束的标志符需另外定义：delimiter 结束标记符
   4.如果参数列表中有多个参数，每个参数定义之间使用","隔开
   5.使用out和inout修饰的参数调用时需使用用户变量获取返回值 */

# 例：插入3条记录到employees表中，再插入3条记录到salarys表中
# 创建：
delimiter $ # 自定义存储过程结束标志符
create procedure prcd_1()
begin
	insert into employees(first_name,last_name,skill) values
	('Allen','Mike','C++'),
	('Jhon','Ludas','Java'),
	('Ais','Badon','JavaScript'); # 每条SQL语句以";"结尾
    insert into salarys(first_name,last_name,salary) values
	('Allen','Mike',40000),
	('Jhon','Ludas',50000),
	('Ais','Badon',35000); # 每条SQL语句以";"结尾	
end $ # 使用自定义结束标志符结束存储过程
# 调用：
call prcd_1();

# 例：根据员工姓名查询员工薪资级别
delimiter $
create procedure prcd_2(in first_name varchar(16),in last_name varchar(16))
begin
	select g.grade
	from
	salarys as s
	left join
	grades as g
	on s.salary between g.min_salary and g.max_salary
	where s.first_name=first_name and s.last_name=last_name;
end $
# 调用：
call prcd_2('Jhon','Ben');

# 例：根据薪资级别查询员工人数、最高薪资和平均薪资
delimiter $
create procedure prcd_3(in grade char,out cnt int,out max_slry int,out avg_slry int)
begin
	select count(*),max(s.salary),avg(s.salary) into cnt,max_slry,avg_slry
	from
	salarys as s
	inner join
	grades as g
	on s.salary between g.min_salary and g.max_salary
	where g.grade=grade;
end $
# 调用：
set @cnt:=0;
set @max_slry:=0;
set @avg_slry:=0;
call prcd_3('A',@cnt,@max_slry,@avg_slry);
select @cnt,@max_slry,@avg_slry;

# 例：计算比指定级别最高薪资低指定元的薪资值属于哪个级别
delimiter $
create procedure prcd_4(inout grade char,in off int)
begin
	declare salary_tmp int default 0; # 声明局部变量
	set salary_tmp:=(
        select min(s.salary)
        from
        salarys as s
        inner join
        grades as g
        on s.salary between g.min_salary and g.max_salary
        where g.grade=grade
    );
    set grade:=(
        select g.grade
        from grades as g
        where (salary_tmp-off) between g.min_salary and g.max_salary
    );
end $
# 调用：计算比A薪资级别最高薪资低10000元的薪资属于哪个薪资级别
set @grade:='A';
call prcd_4(@grade,10000);
select @grade;

# 查看存储过程
show create procedure 存储过程名;
# 例：
show create procedure prcd_4;

# 删除存储过程(一次只能删除一个存储过程)
drop procedure 存储过程名;
# 例：
drop procedure prcd_1;
drop procedure if exists prcd_2;
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110

函数

# 基本创建语法：
create function 函数名(参数列表) returns 返回类型
begin
	函数体(一组SQL语句);
	return 返回值;
end 函数结束标志

# 参数列表包含两部分：
参数名 参数类型

# 基本调用语法：
select 函数名(实参列表)
from 表名;

/* 注意：
   1.如果函数体中仅有一条SQL语句，可省略begin and
   2.如果函数体中有多条SQL语句，每条SQL语句必须使用";"结尾
   3.由于";"是一条SQL语句结束的标志，函数体的标志符需另外定义：delimiter 结束标记符
   4.如果参数列表中有多个参数，每个参数定义之间使用","隔开 
   5.函数体中必须存在return语句返回函数的结果
   6.创建函数前必须信任函数创建者，否则不能创建函数，即使用语句：
     set global log_bin_trust_function_creators=1;
     或：
     set @@global.log_bin_trust_function_creators=1; */
     
# 例：查询员工人数
set global log_bin_trust_function_creators=1; # 信任函数创建者
delimiter $
create function func_1() returns int
begin
	declare cnt_tmp int;
	set cnt_tmp=(
        select count(*)
        from employees
    );
    return cnt_tmp;
end $
# 调用
select func_1();
   
# 例：查询给定姓名员工的薪资级别
set global log_bin_trust_function_creators=1; # 信任函数创建者
delimiter $
create function func_2(first_name varchar(16),last_name varchar(16)) returns char
begin 
	return (
        select g.grade
        from
        salarys as s
        inner join
        grades as g
        on s.salary between g.min_salary and g.max_salary
        where s.first_name=first_name and s.last_name=last_name
    );
end $
# 调用：
select func_2('Nancy','Exim');

# 查看函数：
show create function 函数名;
# 例：
show create function func_1;

# 删除函数
drop function 函数名;
# 例：
drop function func_1;
drop function if exists func_2;
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

流程控制结构

分支结构

# if函数：简单的双分支，如果exp_1为真，返回exp_2的结果，如果exp_1为假，返回exp_3的结果
if(exp_1,exp_2,exp_3)

# 例：任意输入两个员工编号，返回薪资更高的员工的编号
set global log_bin_trust_function_creators=1; # 信任函数创建者
delimiter $
create function func_1(id_1 int,id_2 int) returns int
begin
	declare salary_1 int default 0;
	declare salary_2 int default 0;
	set salary_1=(
        select s.salary
        from
        employees as e
        inner join
        salarys as s
        on e.first_name=s.first_name and e.last_name=s.last_name
        where e.id=id_1
    );
    set salary_2=(
        select s.salary
        from
        employees as e
        inner join
        salarys as s
        on e.first_name=s.first_name and e.last_name=s.last_name
        where e.id=id_2
    );
    return if(salary_1>=salary_2,id_1,id_2);
end $
# 调用：
select func_1(3,5);

# if结构：多分支结构，只能用在begin end中!!!
if 条件1 then 语句1;
elseif 条件2 then 语句2;
elseif 条件3 then 语句3;
...
[else 语句n;] # 在所有条件均不满足时执行此语句，此语句可省略
end if;

/* 注意：if分支结构必须添加end if语句结束，即使只有1条if语句!!! */

# 例：假设部门1~5的基础薪资分别是60000,50000,40000,30000,20000，实现根据员工姓名查询员工薪资是否合格
delimiter $
create procedure prcd_1(in first_name varchar(16),in last_name varchar(16),out rtn bit)
begin
	declare slry int default 0;
	declare dpt_id int default 1;
	select s.salary,e.department_id into slry,dpt_id
	from
	employees as e
	inner join
	salarys as s
	on e.first_name=s.first_name and e.last_name=s.last_name
	where e.first_name=first_name and e.last_name=last_name;
	if dpt_id=1 and slry>=60000 then set rtn=1;
	elseif dpt_id=2 and slry>=50000 then set rtn=1;
	elseif dpt_id=3 and slry>=40000 then set rtn=1;
	elseif dpt_id=4 and slry>=30000 then set rtn=1;
	elseif dpt_id=5 and slry>=20000 then set rtn=1;
	else set rtn=0;
	end if; # 不要忘记end if
end $
# 调用：
set @result:=0;
call prcd_1('Tina','Vint',@result);
select @result as '是否合格';

# case表达式：整体case表达式返回结果值，一般搭配select语句使用
case 表达式
when 要判断的表达式1 then 返回值1
when 要判断的表达式2 then 返回值2
when 要判断的表达式3 then 返回值3
...
else 返回值n
end

/* 注意：
   1.case语句作为表达式时具有返回值
   2.可为case表达式起别名
   3.then语句之后为常量值或表达式时，不加";" 
   4.case表达式必须使用end结束 */

# 例：根据薪资级别查询对应薪资级别的上限薪资
set @@global.log_bin_trust_function_creators=1;
delimiter $
create function func_2(grade char) returns int
begin
	return (
        select
        case grade
        when 'A' then 59999
        when 'B' then 49999
        when 'C' then 39999
        when 'D' then 29999
        when 'E' then 19999
        end as case_exp
    );
end $
# 调用：
select func_2('B') as '上限薪资';

# case选择结构用法一：
case 表达式
when 要判断的表达式1 then 语句1;
when 要判断的表达式2 then 语句2;
when 要判断的表达式3 then 语句3;
...
else 语句n;
end case; # 使用end case结尾，而并非end

/* 注意：case选择语句必须使用end case语句结束，即使只有1条case语句!!! */

# 例：根据薪资级别查询对应薪资级别的上限薪资和下限薪资
delimiter $
create procedure prcd_2(in grade char,out min_slry int,out max_slry int)
begin
	case grade
	when 'A' then select 50000,59999 into min_slry,max_slry;
	when 'B' then select 40000,49999 into min_slry,max_slry;
	when 'C' then select 30000,39999 into min_slry,max_slry;
	when 'D' then select 20000,29999 into min_slry,max_slry;
	when 'E' then select 10000,19999 into min_slry,max_slry;
	end case;
end $
# 调用：
set @min_slry=0;
set @max_slry=0;
call prcd_2('C',@min_slry,@max_slry);
select @min_slry '下限薪资',@max_slry '上限薪资';

# case选择结构用法二：
case
when 逻辑表达式1 then 语句1;
when 逻辑表达式2 then 语句2;
when 逻辑表达式3 then 语句3;
...
else 语句n;
end case; # 使用end case结尾，而并非end

/* 注意：case选择语句必须使用end case语句结束，即使只有1条case语句!!! */

# 例：根据薪资级别查询对应薪资级别的上限薪资和下限薪资
delimiter $
create procedure prcd_3(in grade char,out min_slry int,out max_slry int)
begin
	case
	when grade='A' then select 50000,59999 into min_slry,max_slry;
	when grade='B' then select 40000,49999 into min_slry,max_slry;
	when grade='C' then select 30000,39999 into min_slry,max_slry;
	when grade='D' then select 20000,29999 into min_slry,max_slry;
	when grade='E' then select 10000,19999 into min_slry,max_slry;
	end case;
end $
# 调用：
set @min_slry=0;
set @max_slry=0;
call prcd_3('B',@min_slry,@max_slry);
select @min_slry '下限薪资',@max_slry '上限薪资';

/* 注意：
   1.使用case选择结构时，then之后的语句需要使用";"结尾
   2.case选择结构使用end case结束，而不使用end结束
   3.end case语句同样需要使用";"结尾 */
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165

循环结构

# 循环结构的分类：
while : 条件循环
loop : 无限循环
repeat : 条件循环

# 循环控制语句：
iterate : 类似于continue,结束本次循环，跳转到下次循环
leave : 跳出循环

# while语法：
[循环标签:] while 循环条件
do
循环体;
[iterate 循环标签;]
[leave 循环标签;]
end while [循环标签];

/* 注意：
   1.如果定义了循环标签，必须在end while之后加上循环标签
   2.循环控制语句(iterate或leave)必须搭配循环标签使用
   3.while循环语句必须置于begin end中 */

# 例：创建薪资级别表，插入10条记录，薪资级别ABC...对应50000~54999,45000~49999,40000~44999...
create table if not exists slrygrd_info(
    grade char not null unique,
    min_salary int,
    max_salary int
);
set global log_bin_trust_function_creators=1;
delimiter $
create function func_1() returns bit
begin
	declare str varchar(10) default 'ABCDEFGHIJ';
	declare s int default 55000;
	declare t tinyint default 1;
	w1:while t<11
	do
	insert into slrygrd_info values(substr(str,t,1),s-5000,s-1);
	set s=s-5000;
	set t=t+1;
	end while w1;
	return 1;
end $
# 调用：
select func_1();

# loop语法：
[循环标签:] loop
循环体;
[iterate 循环标签;]
[leave 循环标签;]
end loop [循环标签];

/* 注意：
   1.如果定义了循环标签，必须在end loop之后加上循环标签
   2.循环控制语句(iterate或leave)必须搭配循环标签使用
   3.loop循环语句必须置于begin end中
   4.loop循环语句如果不搭配循环控制语句(iterate或leave)，则为无限循环 */

# 例：创建薪资级别表，插入10条记录，薪资级别ABC...对应50000~54999,45000~49999,40000~44999...
create table if not exists slrygrd_info(
    grade char not null unique,
    min_salary int,
    max_salary int
);
set global log_bin_trust_function_creators=1;
delimiter $
create function func_2() returns bit
begin
	declare str varchar(16) default 'ABCDEFGHIJ';
	declare s int default 55000;
	declare t tinyint default 1;
	l:loop
	insert into slrygrd_info values(substr(str,t,1),s-5000,s-1);
	set s=s-5000;
	set t=t+1;
	if t=11 then leave l;
	end if; # if分支结构必须使用end if结束
	end loop l;
	return 1;
end $
# 调用：
select func_2();

# repeat语法：
[循环标签:] repeat
循环体;
[iterate 循环标签;]
[leave 循环标签;]
until 结束循环的条件 # 注意：until语句之后不加";"
end repeat [循环标签];

/* 注意：
   1.如果定义了循环标签，必须在end repeat之后加上循环标签
   2.循环控制语句(iterate或leave)必须搭配循环标签使用
   3.repeat循环语句必须置于begin end中
   4.until语句之后不加";" */

# 例：创建薪资级别表，插入10条记录，薪资级别ABC...对应50000~54999,45000~49999,40000~44999...
create table if not exists slrygrd_info(
    grade char not null unique,
    min_salary int,
    max_salary int
);
set global log_bin_trust_function_creators:=1;
delimiter $
create function func_3() returns bit
begin
	declare str varchar(16) default 'ABCDEFGHIJ';
	declare s int default 55000;
	declare t tinyint default 1;
	r:repeat
	insert into slrygrd_info values(substr(str,t,1),s-5000,s-1);
	set s:=s-5000;
	set t:=t+1;
	until t=11
	end repeat r;
	return 1;
end $
# 调用：
select func_3();
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121