MySQL_sudo: mysql.server: command not found

参考版本:
mysql-installer-community-8.0.28.0 与 mysql-installer-community-5.7.35.0

此电脑->管理->服务和应用程序->服务- 可查看mysql服务

此电脑-属性-高级系统设置-环境变量-系统环境变量-Path-编辑-新建-粘入mysql安装路径(从bin开始;路径不能有中文与空格;若装了两个不同版本mysql系统只会识别环境变量中靠前的MySQL版本)

cmd-mysql --version查看MySQL版本号-若装了不同版本的MySQL(安装时注意更改端口号)-可根据端口号登入相应的MySQLmysql -uroot -p****** -hlocalhost -P13306

启动MySQL服务的两种方式:
一： 此电脑-管理-服务和应用程序-服务-随便点击一个服务输入m-选中MySQL-可选择"停止,暂停,重启,启动"操作-也可更改其启动类型"自动,手动,禁用"
二： 搜索框输入cmd-右键-以管理员身份运行-启动MySQL服务命令net start mysql80-停止MySQL服务命令net stop mysql80

MySQL用户登录指令 mysql -u root -P13306 -p
远程访问异地数据库指令mysql -u root -P3306 -p -h localhost

MySQL5.7版本造了一张表 - 往表里添加数据 - 添加失败(原因：5.7默认识别Latin,不能识别中文) - 解决方法(修改编码格式) :
步骤一：查看编码命令

 show variables like 'character_%';
 show variables like 'collation_%';
1
2

步骤二：修改MySQL的数据目录下的my.ini配置文件

[mysql] #大概在63行左右，在其下添加
default-character-set=utf8 #默认字符集
[mysql] #大概在76行左右，在其下添加
character-set-server=utf8
collation-server=utf8_general_ci
1
2
3
4
5

注意：建议修改配置文件使用notepad+ +等高级文本编辑器，使用记事本等软件打开修改后可能会导致文件编码修改为“含BOM头”的编码，从而服务重启失败。

有些图形界面工具，特别是旧版本的图形界面工具，在连接MySQL8时出现“Authentication plugin’caching_sha2_password’ cannot be loaded”错误。
出现这个原因是MySQL8之前的版本中加密规则是mysql_native_ password, 而在MySQL8之后,加密规则是caching_sha2_password。 解决问题方法有两种，第一种是升级图形界面工具版本，第二种是把MySQL8用户登录密码加密规则还原成mysqL_native_password。

第二种解决方案如下：

#使用mysql数据库
	USE mysql;
#修改'root'@'localhost'用户的密码规则和密码
	ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '******';
#刷新权限
	FLUSH PRIVILEGES; 
1
2
3
4
5
6

1.SQL的分类

DDL: 数据定义语言。CREATE \ ALTER \DROP \ RENAME \ TRUNCATE

DML: 数据操作语言。INSERT \ DELETE \ UPDATE \ SELECT（重中重）

DCL: 数据控制语言。COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE

MySQL导入数据库方式：
方式一：命令行输入：source D:\MySql\atguigudb.sql;
方式二：使用图形化界面导入： 例如SQLyog中选择工具–“执行SQL脚本”–选中×××.sql即可。

基本SELECT

最基本的SELECT语句：SELECT 字段1，字段2，…FROM 表名 ;

*:  表中的所有的字段（或列）
select * from atguigudb.employees;

列的别名： 
方式一：as 全称：alias(别名) 可以省略
SELECT first_name 名 FROM employees;
方式二：列的别名可以用""引起来
SELECT first_name "姓",last_name "名",department_id "部门ID" FROM atguigudb.employees ;

# 去除重复行
SELECT DISTINCT department_id FROM atguigudb.employees;

# 空值不等同于0 
SELECT last_name "名",salary "月薪",(1+IFNULL(commission_pct,0))*salary*12 "年薪" FROM atguigudb.employees;

# 着重号
SELECT * FROM `order`;

# 常数  '来自鱼仔的操作' 便是常数
SELECT '来自鱼仔的操作',salary,department_id FROM atguigudb.employees;

# 显示表的结构 
DESCRIBE atguigudb.employees ;
DESC employees ; -- 效果与DESCRIBE相同

# 过滤条件 
SELECT * FROM atguigudb.employees WHERE last_name='king' ;
# 练习题

-- 1.查询员工12个月的工资总和，并起名为ANNUAL SQLARY
SELECT first_name,(1+IFNULL(commission_pct,0))*12*salary "年薪" FROM atguigudb.employees;
-- 2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id FROM atguigudb.employees ;
-- 3.查询工资大于12000的员工姓名和工资
SELECT first_name,last_name,salary FROM atguigudb.employees WHERE salary>12000 ; 
-- 4.查询员工工号为176的员工的姓名和部门号
SELECT first_name,last_name,department_id FROM atguigudb.employees WHERE employee_id=176;
-- 5.显示表departments的结构，并查询其中的全部数据
DESC departments ;
SELECT * FROM atguigudb.departments ;
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

运算符

在SQL中，+没有连接作用，就表示加法运算。此时，会将字符串转换为数值(隐式转换)
SELECT 100+'1' FROM DUAL ;  -- 101
SELECT 100+'a' FROM DUAL ;  -- 100 此时'a'看作零处理 
SELECT 100+NULL FROM DUAL ; -- NULL NULL参与运算，结果为NULL

<=> 安全等于。记忆点：为NULL而生。
SELECT 1<=>NULL,0<=>NULL,NULL<=>NULL FROM DUAL; -- 0 0 1 

IS NULL \ IS NOT NULL \ ISNULL
SELECT employee_id,commission_pct FROM atguigudb.`employees` WHERE commission_pct IS NULL ;
SELECT employee_id,commission_pct FROM atguigudb.`employees` WHERE ISNULL(commission_pct); -- 查询employees表中commission_pct字段为NULL的员工ID。
SELECT employee_id,commission_pct FROM atguigudb.`employees` WHERE commission_pct IS NOT NULL ;	-- 查询employees表中commission_pct字段不为NULL的员工ID。

LEAST() \ GREATEST 最小的\最大的
SELECT LEAST('a','b','c','d'),GREATEST(4,5,6,9) FROM DUAL ; -- -- a 9

BETWEEN...AND  查询区间值,包含边界
SELECT employee_id,first_name,last_name,salary FROM atguigudb.`employees` WHERE salary BETWEEN 6000 AND 8000 ;  -- 查询工资在6000到8000的员工信息

in (set) \ not in (set)
SELECT employee_id,first_name,last_name,department_id FROM atguigudb.`employees` WHERE department_id=10 OR department_id=20 OR department_id=30 ; -- 查询部门号为：10，20，30的员工信息。
SELECT employee_id,first_name,last_name,department_id FROM atguigudb.`employees` WHERE department_id IN(10,20,30,40);
SELECT employee_id,first_name,last_name,salary FROM atguigudb.`employees` WHERE salary NOT IN(6000,7000,8000); -- 查询工资不是6000，7000，8000的员工信息。

LIKE: 模糊查询
%: 代表不确定个数的字符
SELECT last_name FROM atguigudb.`employees` WHERE last_name LIKE '%a%' ; -- 查询last_name中包含'a'的员工信息。
SELECT last_name FROM atguigudb.`employees` WHERE last_name LIKE 'a%' ; -- 查询lsat_name中以'a'开头的员工信息。
SELECT last_name FROM atguigudb.`employees` WHERE last_name LIKE '%a%' AND last_name LIKE '%e%' ; -- 查询last_name中包含'a'和'e'的员工信息。

_ : 代表一个不确定的字符
转义字符：\
SELECT last_name FROM atguigudb.`employees` WHERE last_name LIKE '_a%' ; -- 查询第二个字符是'a'的员工信息
SELECT last_name FROM atguigudb.`employees` WHERE last_name LIKE '_\_a' ; -- 查询第二个字符是'_'且第三个字符是'a'的员工信息。 

REGEXP \ RLIKE 正则表达式  
REGEXP运算符匹配字符串，语法格式为：expr REGEXP 匹配条件。 若expr满足，返回1；若不满足，返回0。若expr或匹配条件任意一个为NULL，结果为NULL
'^'匹配以该字符后面的字符开头的字符串。 '$'匹配以该字符前面的字符结尾的字符串。 '.'匹配任何一个单字符
SELECT 'atguigu' REGEXP 'ui', 'yuzai' REGEXP 'i','yuzai' REGEXP '[yi]' FROM DUAL ;1 1 1   

逻辑运算符
|| AND 逻辑与 ; && OR 逻辑或 ; ! NOT 逻辑非 ; XOR 逻辑异或  注意：AND的优先级高于OR
SELECT department_id FROM atguigudb.`employees` WHERE department_id =10 OR department_id=30 ; -- 查询department_id为10或30的员工信息
SELECT employee_id,first_name,last_name,salary FROM atguigudb.`employees` WHERE salary NOT BETWEEN 6000 AND 8000 ; -- 查询工资不在6000-8000区间的员工信息

XOR : 追求的'异'
SELECT employee_id,first_name,last_name,salary FROM atguigudb.`employees` WHERE salary !=6000 XOR salary >10000;  

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

练习题：

#  练习题
# 1.选择工资不在5000到12000的员工的姓名和工资
SELECT first_name,last_name,salary FROM atguigudb.`employees` WHERE salary NOT BETWEEN 5000 AND 12000 ;
# 2.选择在20或50号部门工作的员工姓名和部门号
SELECT first_name,last_name,department_id FROM atguigudb.`employees` WHERE department_id IN(20,50);
# 3.选择公司中没有管理者的员工姓名及job_ id
SELECT first_name,last_name,job_id FROM atguigudb.`employees` WHERE department_id NOT IN (manager_id);
# 4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT first_name,last_name,salary,commission_pct FROM atguigudb.`employees` WHERE commission_pct IS NOT NULL ;
# 5.选择员工姓名的第三个字母是a的员工姓名
SELECT first_name,last_name FROM atguigudb.`employees` WHERE first_name LIKE '__a%' ;
# 6.选择姓名中有字母a和k的员工姓名
SELECT first_name,last_name FROM atguigudb.`employees` WHERE first_name LIKE '%a%' AND first_name LIKE '%k%' ;
# 7.显示出表employees 表中first_ name 以'e'结尾的员工信息
SELECT * FROM atguigudb.`employees` WHERE first_name LIKE '%e' ;
# 8.显示出表employees 部门编号在80-100 之间的姓名、工种
SELECT first_name,last_name,job_id,department_id FROM atguigudb.`employees` WHERE department_id BETWEEN 80 AND 100 ;
# 9.显示出表employees 的manager. id是100,101,110 的员工姓名、工资、管理者id I
SELECT first_name,last_name,manager_id FROM atguigudb.`employees` WHERE manager_id IN(100,101,110);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

排序与分页

ORDER BY 排序
升序 ASC (ascend)
降序 DESC (descend)
# 按照salary从高到低的顺序显示员工信息
SELECT last_name,salary,job_id FROM atguigudb.`employees` ORDER BY salary DESC ; 
# 我们可以使用列的别名，进行排序 ; 注意：列的别名只能在ORDER BY 中使用，不能在WHERE中使用。
SELECT last_name,salary "年薪" FROM atguigudb.`employees` ORDER BY "年薪" DESC ;
# 二级排序  
# 练习：显示员工信息，按照department_id 的降序排列，salary的升序排列。
SELECT last_name,salary,department_id FROM atguigudb.`employees` ORDER BY department_id DESC , salary ASC;

LIMIT 分页显示
# 需求：每页显示pageSize条记录，此时显示第pageNo页； 公式： LIMIT (pageNo-1) * pageSize,pageSize;
MySQL8.0新特性: LIMIT ... OFFSET...  SELECT employee_id,first_name,salary FROM atguigudb.`employees` LIMIT 2 OFFSET 31 ;
# 练习：每页显示20条记录
SELECT last_name,email FROM atguigudb.`employees` LIMIT 0,20 ;
# LIMIT的格式：严格来说 LIMIT 位置偏移量，条目数 ；特殊的: LIMIT 0,条目数 等价于 "LIMIT 条目数"
SELECT employee_id,last_name,salary FROM atguigudb.`employees` WHERE salary BETWEEN 6000 AND 12000 ORDER BY employee_id ASC LIMIT 6 ;
# 练习：查看第32、33条数据。
SELECT employee_id,first_name,salary FROM atguigudb.`employees` LIMIT 31,2 ;
# 练习：查询员工表中工资最高的员工信息。
SELECT * FROM atguigudb.`employees` ORDER BY salary DESC LIMIT 1 ;

# 练习题
#1.查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序显示
SELECT first_name,last_name,department_id,salary*12 "年薪" FROM atguigudb.`employees` ORDER BY "年薪" DESC , last_name ASC ; -- 注: 二级排序中间用','隔开
#2.选择工资不在8000 到17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据
SELECT first_name,last_name,salary FROM atguigudb.`employees` WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC LIMIT 20,20 ;
#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT * FROM atguigudb.`employees` WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC , 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

多表查询

-- SQL92语法实现的多表查询  (SELECT 字段1,字段2 FROM 表1，表2 连接条件1 AND 连接条件2)
SELECT e.last_name,d.`department_name`,l.`city` FROM employees e,departments d,locations l WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`;
-- SQL99语法实现的多表查询  (JOIN ON)
SELECT e.`last_name`,d.`department_name`,loc.`city` FROM employees e JOIN departments d ON e.`department_id`=d.`department_id` JOIN locations loc ON d.`location_id`=loc.`location_id`;

 查询employees表中的employee_id及departments表中的depaertment_name。 连接条件是: employees与departments两表中的department_id 。
SELECT employee_id,department_name FROM employees,departments WHERE employees.`department_id` = departments.`department_id`;
 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。 建议：从SQL优化的角度，建议多表查询时，每个字段前都指明所在的表。
SELECT employees.`salary`,departments.`manager_id`,departments.`department_id` FROM atguigudb.`employees`,atguigudb.`departments` WHERE employees.`department_id` = departments.`department_id` ;
 可以给表起别名
SELECT emp.`email`,emp.`last_name`,depar.`department_id` FROM employees emp , departments depar WHERE emp.`department_id` = depar.`department_id` ; -- 语句从FROM开始执行。
 如果有n个表实现多表查询，则n-1个连接条件用AND连接。
# 练习:查询员工的employee_ id, last_ name , department_ name, city
SELECT emp.`employee_id`,emp.`last_name`,depar.`department_name`,loca.`city` FROM employees emp , departments depar , locations loca WHERE emp.`department_id`=depar.`department_id` AND depar.`location_id`=loca.`location_id`;
 内连接 VS 外连接(JOIN ... ON) 
 (以下都使用SQL99语法,MySQL不支持SQL92语法写外连接,以上都为SQL92语法编写的内连接)
 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。
 外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行。
-- 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接
SELECT emp.`last_name`,depar.`department_name` FROM employees emp LEFT OUTER  JOIN departments depar ON emp.`department_id`=depar.`department_id` ;
-- 右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接
SELECT emp.`last_name`,depar.`department_name` FROM employees emp RIGHT OUTER  JOIN departments depar ON emp.`department_id`=depar.`department_id` ;
-- 满外连接：返回两个表中所有数据
	（MySQL不支持SQL99语法中的 FULL OUTER JOIN 写法）
	UNION操作符返回两个查询的结果集的并集,去除重复的记录。
	UNION ALL操作符返回两个操作符的结果集的并集。但重复的数据不去重。
SELECT location_id FROM departments UNION ALL SELECT city FROM locations ;

 7种JOIN的实现
-- 左上图：(左外连接)
SELECT e.`last_name`,d.`department_name` FROM employees e LEFT OUTER JOIN departments d ON e.`department_id`=d.`department_id`;
-- 右上图: (右外连接)
SELECT e.`first_name`,d.`department_name` FROM employees e RIGHT OUTER JOIN departments d ON e.`department_id`=d.`department_id`;
-- 中心图：(内连接)
SELECT e.`last_name`,d.`department_name` FROM employees e JOIN departments d ON e.`department_id`=d.`department_id`;
-- 左中图: (查询仅左表有，右表没有的数据)
SELECT e.`last_name`,a.`department_name` FROM employees e LEFT OUTER JOIN departments a ON e.`department_id`=a.`department_id` WHERE a.`department_name` IS NULL ;
-- 右中图：(查询仅右表有，左表没有的数据)
SELECT e.`last_name`,d.`department_id` FROM employees e RIGHT OUTER JOIN departments d ON e.`department_id`=d.`department_id` WHERE e.`department_id` IS NULL ;
-- 左下图：(满连接)
SELECT e.`last_name`,d.`department_name` FROM employees e LEFT OUTER JOIN departments d ON e.`department_id`=d.`department_id`
UNION ALL
SELECT e.`last_name`,d.`department_id` FROM employees e RIGHT OUTER JOIN departments d ON e.`department_id`=d.`department_id` WHERE e.`department_id` IS NULL ;
-- 右下图： 
SELECT e.`last_name`,a.`department_name` FROM employees e LEFT OUTER JOIN departments a ON e.`department_id`=a.`department_id` WHERE a.`department_name` IS NULL 
UNION ALL
SELECT e.`last_name`,d.`department_id` FROM employees e RIGHT OUTER JOIN departments d ON e.`department_id`=d.`department_id` WHERE e.`department_id` 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

单行函数

数值函数

SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),FLOOR(-43.23),MOD(12,5)FROM DUAL; 
#取随机数
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1) FROM DUAL;
#四舍五入，截断操作
SELECT ROUND(123.556),ROUND(123.456,0),ROUND(123.456,1),ROUND(123.456,2),ROUND(123.456,-1),ROUND(153.456,-2) FROM DUAL;
SELECT TRUNCATE(123.456,0),TRUNCATE(123.496,1),TRUNCATE(129.45,-1) FROM DUAL;
#单行函数可以嵌套
SELECT TRUNCATE(ROUND(123.456,2),0) FROM DUAL;
#角度与弧度的互换
-- RADIANS(x) 将角度转化为弧度,参数x为角度值; DEGREES(x) 将弧度转化为角度,参数x为弧度值。
SELECT RADIANS(560) FROM DUAL; -- 度转弧
SELECT DEGREES(1) FROM DUAL; -- 弧转度
#三角函数
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)) FROM DUAL;
#指数和对数
SELECT POW(2,5),POWER(2,4),EXP(2) FROM DUAL;
SELECT LN(EXP(2)),LOG(EXP(2)),LOG10(10),LOG2(4) FROM DUAL;
#进制间的转换
SELECT BIN(10),HEX(10),OCT(10),CONV(10,10,8) FROM DUAL;
SELECT BIN(100),HEX(10),OCT(10),CONV(10,2,8) FROM DUAL; -- CONV二进制的10转换为八进制为：2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

字符串函数

# ASCII(S) 返回字符串s中第一个ASCII码值
# CHAR_LENGTH(s) 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同。
# TENGTH(s) 返回字符串s的字节数，和字符集有关
SELECT ASCII('abc'),CHAR_LENGTH('love'),LENGTH('love'),LENGTH('鱼仔'),CHAR_LENGTH('鱼仔') FROM DUAL; 
# CONCAT(S1,S2,...Sn) 连接s1,s2,...,sn为一个字符串
SELECT CONCAT(emp.`last_name`,'芜湖！起飞他是：',depar.`department_name`) '鱼仔查的' FROM employees emp JOIN departments depar ON emp.`department_id`=depar.`department_id` ;
# CONCAT_WS(X,S1,S2,...,Sn) S1,S2,..,Sn之间用X连接。
SELECT CONCAT_WS('-&-','Y','Z','a','i') FROM DUAL;
# INSERT(str,idx,len,replacestr) 将字符串str从idx的索引位置开始，len个字符长的子串替换为字符串replacestr
SELECT INSERT('yuzailove',1,5,'鱼仔') FROM DUAL;
# REPLACE(str,a,b) 用字符串b替换字符串str中所有出现的字符串a。
SELECT REPLACE('yuzai,yuzai','a','起飞') FROM DUAL;
# UPPER(s)将字符串s的所有字母转成大写字母 LOWER(s)将字符串s的所有字母转成小写字母
SELECT UPPER('yuzai'),LOWER('YUZAI') FROM DUAL;
# LEFT(str,n) 返回字符串str最左边的n个字符
SELECT LEFT('yuzai',3) FROM DUAL;
# RIGHT(str,n) 返回字符串str最右边的n个字符
SELECT RIGHT('yuzai',3) FROM DUAL;
# LPAD(str,len,pad) 用字符串pad对str最左边进行填充,直到str的长度为len个字符
SELECT LPAD(e.`salary`,10,' ') FROM employees e ;
# RPAD(str,len,pad) 用字符串pad对str最右边进行填充,直到str的长度为len个字符
SELECT RPAD(e.`salary`,10,'*') FROM employees e ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

日期和时间函数

# 日期和时间函数
# CURDATE()返回当前日期,只包含年、月、日；CURTIME()返回当前时间,只包含时、分、秒; NOW()/SYSDATE()返回当前系统日期和时间; UTC_DATE()返回UTC(世界标准时间)日期；UTC_TIME()返回UTC(世界标准时间)时间。
SELECT CURDATE(),CURTIME(),NOW(),UTC_DATE(),UTC_TIME() FROM DUAL;
# 日期与时间戳的转换 时间戳:毫秒数
# UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间。
SELECT UNIX_TIMESTAMP() FROM DUAL;
# UNIX_TIMESTAMP(date) 将时间date以UNIX时间戳的形式返回。
SELECT UNIX_TIMESTAMP('2026-10-16 12:56:03') FROM DUAL; 
# FROM_UNIXTIME(timestamp) 将UNIX时间戳的时间转换为普通格式的时间。
SELECT FROM_UNIXTIME(1645621655) FROM DUAL;
SELECT FROM_UNIXTIME(1645621655) FROM DUAL;
# 获取月份、星期、星期数、天数等函数。
   -- YEAR(date) 返回具体的日期值
SELECT YEAR(NOW()),YEAR('2066-10-10') FROM DUAL;
   -- HOUR(time) 返回具体的时间值
SELECT HOUR(NOW()),HOUR('2021-10-06 12:06') FROM DUAL;
   -- MONTHNAME(date) 返回月份: January, ....
SELECT MONTHNAME(NOW()),MONTHNAME('2029-12-06') FROM DUAL;
   -- DAYNAME(date) 返回星期几: MONDAY, TUESDAY... .SUNDAY
SELECT DAYNAME(NOW()) FROM DUAL;
   -- WEEKDAY(date) 返回周几，注意，周1是0，周2是1,。。。周日是6
SELECT WEEKDAY(NOW()) FROM DUAL;
   -- QUARTER(date) 返回日期对应的季度，范围为1 ~4
SELECT QUARTER(NOW()) FROM DUAL;
   -- WEEK(date) 返回一年中的第几周
SELECT WEEK(NOW()) FROM DUAL;
   -- DAYOFYEAR(date) 返回日期是一年中的第几天
SELECT DAYOFYEAR(NOW()) FROM DUAL;
   -- DAYOFMONTH(date) 返回日期位于所在月份的第几天.
SELECT DAYOFMONTH(NOW()) FROM DUAL;
   -- DAYOFWEEK(date) 返回周几，注意:周日是1，周一是2,。。。周六是7
SELECT DAYOFWEEK(NOW()) FROM DUAL;
# 日期操作函数
   -- EXTRACT(type FROM date) 返回指定日期中特定的部分,type指定返回的值。
SELECT EXTRACT(DAY FROM NOW()),EXTRACT(MINUTE FROM NOW()) FROM DUAL;

# 时间和秒钟转换的函数
   -- TIME_TO_SEC(time) 将time转化为秒并返回结果值。转化的公式为:小时*3600+分钟*60+秒
   -- SEC_TO_TIME(seconds) 将seconds描述转化为包含小时、分钟和秒的时间
SELECT TIME_TO_SEC(NOW()),SEC_TO_TIME(31340) FROM DUAL;

# 计算日期和时间的函数
   -- DATE_ADD(datetime,INTERVAL expr type) 返回与给定日期相差INTERVAL时间段的日期时间 （累加）
SELECT DATE_ADD(NOW(),INTERVAL 1 HOUR) FROM DUAL;
   -- 返回与date相差INTERVAL时间间隔的日期 （减少）
SELECT DATE_SUB(NOW(),INTERVAL 1 HOUR) FROM DUAL;
   --  ADDTIME(time1,time2) 返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
SELECT ADDTIME(NOW(),NOW()),ADDTIME(NOW(),301238),NOW() FROM DUAL;
   --  DATEDIFF(date1,date2)的时间间隔天数
SELECT DATEDIFF(NOW(),'2002-12-16') FROM DUAL;
   --  TIMEDIFF(time1,time2)的时间间隔
SELECT TIMEDIFF(NOW(),'2022-01-30 6:30:00') FROM DUAL;

# 日期的格式化与解析
 -- DATE_FORMAT(date,fmt) 按照字符串fmt格式化日期date值
SELECT DATE_FORMAT(NOW(),'%Y %b %e %H %i ') FROM DUAL; 
 -- TIME_FORMAT(time,fmt) 按照字符串fmt格式化时间time值
 -- GET_FORMAT(date_type,format_type) 返回日期字符串的显示格式
SELECT TIME_FORMAT(NOW(),GET_FORMAT(TIME,'ISO')) FROM DUAL;
 -- STR_TO_DATE(str,fmt) 按照字符串fmt对str进行解析，解析为一个日期
SELECT STR_TO_DATE('10:21:11',GET_FORMAT(TIME,'ISO')) FROM DUAL;

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

流程控制函数

-- IF(value,value1,value2) 如果value的值为TRUE，返回value1,否则返回value2。
  SELECT IF(NULL,'不是NULL','是NULL值') FROM DUAL;
  -- IFNULL(value1,value2) 如果value1不为NULL，返回value1,否则返回value2。
  SELECT IFNULL('不是NULL','是NULL') FROM DUAL;
  -- CASE WHEN ... THEN... WHEN...WHEN ...ELSE ...END ()
  SELECT last_name,hire_date,salary,CASE WHEN salary>10000 THEN '白骨精'
				      WHEN salary>=6000 THEN '高薪资'
				      WHEN salary<6000 THEN '低薪资'
				      ELSE '草根' END '等级'
  FROM employees ;
  -- CASE when then when then else end 
  SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary*1.1
	                                                               WHEN 20 THEN salary*1.2
	                                                               WHEN 30 THEN salary*1.5
	                                                               ELSE salary*100 END '工资翻倍BUFF'
  FROM employees;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

加密与解密函数

SELECT PASSWORD('yuzai') FROM DUAL; -- 在MySQL8.0中己被弃用。
SELECT MD5('yuzai'),SHA('yuzai'),SHA(SHA('yuzai')) FROM DUAL ; -- MD5与SHA加密函数都是不可逆的。
SELECT ENCODE('yuzailove','who'),DECODE(ENCODE('yuzailove','who'),'who') FROM DUAL; -- 在MySQL8.0已弃用。

1
2
3
4

mysql信息函数

-- VERSION()返回当前MySQL的版本号
  -- DATABASE()，SCHEMA()返回当前MySQL服务器的连接数
  -- USER() 返回当前连接MySQL的用户名，返回结果格式为“名@用户名”
  -- CHARSET(value)返回字符串value自变量的字符集
  -- COLLATION(value)返回字符串value的比较规则
SELECT VERSION(),CONNECTION_ID(),DATABASE(),USER(),CHARSET('a'),COLLATION('b') FROM DUAL;

1
2
3
4
5
6
7

其他函数

 -- FORMAT(value,n) 返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位.
  -- CONV(value,from,to) 将value的值进行不同进制之间的转换
  -- INET_ATON(ipvalue) 将以点分隔的IP地址转化为一个数字
  -- INET_NTOA(value) 将数字形式的IP地址转化为以点分隔的IP地址
  -- BENCHMARK(n,expr) 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
  -- CONVERT(value USINGchar_code) 将value所使用的字符编码修改为char_code
SELECT FORMAT(1236.356,1),CONV(100021,10,2),INET_ATON('192.168.1.130'),INET_NTOA('3232235906'),BENCHMARK(10000,VERSION()),CHARSET('yuzai'),CHARSET(CONVERT('yuzai'USING 'utf8mb4')) FROM DUAL;

1
2
3
4
5
6
7
8

# 课后练习题：
# 1.显示系统时间(注：日期+时间)
SELECT NOW() FROM DUAL; -- now() 返回当前系统日期和时间
# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果(new salary)  
SELECT e.`last_name` '名字',e.`employee_id` '工号',e.`salary` '工资',e.`salary`*1.2 'NEW SALARY' FROM employees e ; 
# 3.将员工的姓名按首字母排序，并写出姓名的长度(length)
SELECT e.`last_name`,LENGTH(e.`last_name`) FROM employees e ORDER BY e.`last_name` ASC ; -- ORDER BY ASC 单排升序 ; LENGTH() 函数
# 4.查询员工id, last_ name, salary， 并作为一个列输出，别名为OUT_PUT
SELECT CONCAT_WS('-',e.`employee_id`,e.`last_name`,e.`salary`) 'OUT_PUT'FROM employees e;  -- 可以使用字符串函数 CONCAT_WS 
# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT  e.`last_name`,e.`hire_date`,DATEDIFF(NOW(),e.`hire_date`)/365 'Year' , DATEDIFF(NOW(),e.`hire_date`) '天数'  FROM employees e ORDER BY 'Year' DESC  ; -- 时间函数 DATEIDFF(date1,date2)返回date1-date2的日期间隔天数  
SELECT emp.`last_name` , DATEDIFF(NOW(),emp.`hire_date`) "天数",DATEDIFF(NOW(),emp.`hire_date`)/365 "YEAR", TO_DAYS(NOW())- TO_DAYS(emp.hire_date) "天数二" FROM employees emp ORDER BY "YEAR" DESC ;
# 6.查询员工姓名，hire_date，department_id，满足以下条件:雇用时间在1997年之后，department_id为80或90或110，commission_pct不为空
SELECT e.`last_name`, e.`hire_date`,e.`department_id`,e.`commission_pct` FROM employees e WHERE  TO_DAYS(e.`hire_date`)>TO_DAYS(1997-0-0)  AND department_id=110  AND commission_pct IS NOT NULL OR department_id=80 OR department_id=90 AND commission_pct IS NOT NULL; 
SELECT e.`last_name`,e.`hire_date`,e.`department_id` FROM employees e WHERE e.`department_id` IN (80,90,110) AND e.`commission_pct` IS NOT NULL AND DATE_FORMAT(e.`hire_date`,'%Y')>='1997' ; 
# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT e.`last_name`,e.`hire_date`,DATEDIFF(NOW(),e.`hire_date`) '天数' FROM employees e WHERE DATEDIFF(NOW(),e.`hire_date`)>10000 ;
# 8.做一个查询，产生下面的结果
-- <last_name> earns <salary> monthly but wants <salary*3> 
SELECT CONCAT(e.`last_name`,' earns ',TRUNCATE (e.`salary`,0),' moonthly but wants ',e.`salary`*3) "Dream Salart"
FROM employees e ;
# 9.使用case-when，按照下面的条件： 
SELECT e.last_name,e.`job_id`,e.`salary`,     -- 注意： ,case when then else end "" form 
CASE WHEN e.`salary`>20000 THEN 'A'
     WHEN e.`salary`>15000 THEN 'B'
     WHEN e.`salary`>10000 THEN 'C'
     WHEN e.`salary`>8000 THEN 'D'
     WHEN e.`salary`>5000 THEN 'E'
     ELSE "草根" END "grade"
FROM employees e ;
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

聚合函数

#  聚合函数

# 1.常见的几个聚合函数
# AVG/SUM (只适用于数值类型的字段)
SELECT  CONCAT('员工的平均工资为：',ROUND(AVG(salary),0),' 元') FROM employees;
SELECT  CONCAT('每个月共需给员工分发：',ROUND(SUM(salary),0),' 元') FROM employees;

# 2.MAX/MIN (适用于数值类型、字符串类型、日期时间类型的字段或变量)
SELECT CONCAT("工资最高最低分别为：",MAX(salary),',',MIN(salary)) "工资对比" FROM employees;
SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date) FROM employees;

# 3.COUNT 计算指定字段在查询结果中出现的个数(不包含NULL值)
	  -- 计算表中有多少条记录，如何实现？COUNT(1) COUNT(*) COUNT(具体字段)； MySIAM存储引擎三者效率相同 InnoDB存储引擎 COUNT(*)=COUNT(1)>COUNT(字段)
SELECT COUNT(employee_id) FROM employees;
SELECT COUNT(1),COUNT(*),COUNT(5) FROM employees;

  -- 需求：查询公司中平均奖金率
  -- 易错点：count不计算NULL值，但平均值是需要null值的人头数来参与的 
  SELECT AVG(IFNULL(commission_pct,0)) FROM employees;
  SELECT SUM(commission_pct)/COUNT(*) FROM employees; 
 
# 4.GROUP BY (将表中的数据分成若干组)
    -- 注意点一：GROUP BY中未声明的字段不可以出现在SELECT中
    -- 注意点二：GROUP BY声明在FROM后面、WHERE后面、ORDER BY前面、LIMIT前面
    -- 注意点三：GROUP BY 新特性，在GROUP BY中使用WITH ROLLUP 会将整个一个主体分成一个分类为NULL
SELECT CONCAT(department_id, '部门平均工资为：',ROUND(AVG(salary),0),'元') "部门平均工资" FROM employees GROUP BY department_id  ;
SELECT job_id,AVG(salary) "avgdate" FROM employees GROUP BY job_id ORDER BY avgdate ASC ;

# 需求：查询各个department_id,job_id 的平均工资
SELECT department_id,job_id,AVG(salary) FROM employees GROUP BY department_id,job_id ;

# HAVING (用来过滤的) 
  -- 要求一：如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE，否则，报错。
  -- 要求二：HAVING必须声明在GROUP BY 后面。
  -- 要求三：开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。
    -- 查询各个部门中最高工资比10000高的部门信息 
SELECT department_id,MAX(salary),AVG(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;
  -- 结论：当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
  --       当过滤条件中没有聚合函数时，则此过滤条件可以声明在WHERE中或HAVING中。但是，建议声明在WHERE中(效率高)。
  /*
    WHERE 与 HAVING 的对比
   1. 从适用范围上来讲，HAVING的适用范围更广。( 当过滤条件中使用了聚合函数，必须声明在HAVING中 )     
   2. 如果过滤条件中没有使用聚合函数，首选WHERE，它的执行效率更高。 WHERE是先筛选后连接，HAVING是先连接后筛选。  
  */
  
/*   SQL语句的执行过程
# SQL92语法
SELECT ...,...(存在聚合函数) FROM ...,... WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件 GROUP BY 包含聚合函数的过滤条件 ORDER BY ...,...(ASC/DESC) LIMIT...,...

# SQL99 语法
SELECT ...,...(存在聚合函数) FROM ... (LEFT/RIGHT)JOIN ... ON 多表的连接条件 WHERE 不包含聚合函数的过滤条件 GROUP BY ... HAVING (包含聚合函数的过滤条件) ORDER BY ...(ASC/DESC) LIMIT ...,...

-- SQL语句的执行过程
-- FROM ...,... -> ON -> (LEFT/RIGNT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT
*/


# 课后练习题：
#1. where子句可否使用组函数进行过滤?
NO!
#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT CONCAT('工资最大值为：',ROUND(MAX(e.`salary`),0),"元") "最大值",CONCAT('工资最小值为：',ROUND(MIN(e.`salary`),0),"元") "最小值",CONCAT('工资平均值为：',ROUND(AVG(e.`salary`),0),"元") "平均值",CONCAT('工资总值为：',ROUND(SUM(e.`salary`),0),"元") "总值" FROM employees e ;
#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT e.`job_id`,MAX(salary) "maxd",MIN(salary),AVG(salary),SUM(salary) FROM employees e GROUP BY job_id ORDER BY maxd DESC;
#4.查询具有各个job_id的员工人数
SELECT job_id,COUNT(*) FROM employees e GROUP BY job_id ; 
#5.查询员工最高工资和最低工资的差距(DIFFERENCE )
SELECT CONCAT("工资差最大为：",MAX(salary)-MIN(salary)) "DIFFERENCE" FROM employees e;
#6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT e.`last_name`,MIN(salary) FROM employees e WHERE manager_id IS NOT NULL AND e.`salary`>=6000 GROUP BY e.`manager_id` ;
#7.查询所有部门的名字，location _id,员工数量和平均工资，并按平均工资降序
SELECT d.`department_name`,d.`location_id`,COUNT(employee_id) "员工数量",ROUND(AVG(salary),0) "avgd" FROM employees e LEFT JOIN departments d ON e.`department_id`=d.`department_id` GROUP BY d.department_name,d.`location_id` ORDER BY salary ASC ;
#8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT d.`department_name`,e.`job_id`,MIN(salary) FROM departments d LEFT JOIN employees e ON e.`department_id`=d.`department_id` GROUP BY e.`job_id`,d.`department_name` ;


 




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

子查询

# 子查询

SELECT first_name,salary FROM employees e WHERE  salary>(SELECT salary FROM employees WHERE last_name ="Faviet" ) ;

# 单行子查询 
-- 查询工资大于149号员工工资的员工信息。
SELECT * FROM employees WHERE salary > (SELECT salary FROM employees WHERE employee_id=149);
-- 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name,job_id,salary FROM employees WHERE job_id =( SELECT job_id FROM employees WHERE employee_id=141  )AND salary>( SELECT salary FROM employees WHERE employee_id=143 );
-- 返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary FROM employees WHERE salary=(SELECT MIN(salary) FROM employees);
-- 查询与141号的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id
SELECT employee_id,manager_id,department_id FROM employees 
WHERE manager_id=(SELECT manager_id FROM employees WHERE employee_id=141 )
AND department_id=(SELECT department_id FROM employees WHERE employee_id=141 ) AND employee_id <> 141 ;
-- 查询最低工资大于50号部门最低工资的部门ID和其最低工资
SELECT department_id,MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id=50);
-- 显示员工的employee_id,last_name和location。其中，若干员工department_id与location_id为1800的department_id相同，
-- 则location为'Canada',其余则为'USA'。
SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id=1800) THEN "Canada" ELSE "USA" END "location" FROM employees;

# 多行子查询
# 多行子查询操作符 
  -- IN 等于列表中的任意一个
  -- ANY 需要和单行操作符一起使用，和子查询返回的某一个值比较
  -- ALL 需要和单行操作符一起使用，和子查询返回的所有值比较
  -- SOME 实际上是ANY的别名，作用相同，一般常使用ANY
# IN:
SELECT employee_id,last_name FROM employees WHERE salary IN ( SELECT MIN(salary) FROM employees GROUP BY department_id );
# ANY / ALL :
# 返回其他job_id中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary FROM employees  WHERE  job_id<>'IT_PROG' AND salary < ANY ( SELECT salary FROM employees WHERE job_id='IT_PROG' ) ;                                                                                                            
# 返回其他job_id中比job_id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary FROM employees  WHERE  job_id<>'IT_PROG' AND salary < ALL ( SELECT salary FROM employees WHERE job_id='IT_PROG' ) ; 
# 查询平均工资最低的部门id
 -- MySQL中聚合函数是不能嵌套使用的
SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary)=(SELECT MIN(avg_sal) FROM (SELECT AVG(salary) "avg_sal" FROM employees GROUP BY department_id ) yuzai )   ;
SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary)<=ALL(SELECT AVG(salary)FROM employees GROUP BY department_id );
# 空值问题
 -- 在做子查询时，如果内查询结果有任一NULL值，子查询都查不出结果。
SELECT last_name FROM employees WHERE employee_id NOT IN ( SELECT manager_id FROM employees /*where manager_id is not null*/);

# 相关子查询
  --  查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
  -- 方式一： (使用相关子查询)
SELECT last_name,salary,department_id FROM employees em1 WHERE salary > ( SELECT AVG(salary) FROM employees em2 WHERE department_id = em1.`department_id`  );
  -- 方式二： 在FROM中声明子查询
SELECT em2.`last_name`,em2.`salary`,em2.`department_id` FROM employees em2,( SELECT em1.department_id,AVG(em1.salary) avg_sal FROM employees em1 WHERE em1.department_id IS NOT NULL GROUP BY em1.department_id ) table2 WHERE table2.department_id =em2.`department_id` AND salary > avg_sal  ;
  -- 查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary FROM employees e ORDER BY ( SELECT department_name FROM departments d WHERE e.`department_id`=d.`department_id` ) ASC ;

# 结论：在SELECT中，除了GROUP BY 和LIMIT之外,其他位置都可以声明子查询！

# 若employees表中employees_id和job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name,和其job_id。
SELECT employee_id,last_name,job_id FROM employees e WHERE 2<=( SELECT COUNT(*) FROM job_history j WHERE e.`employee_id`=j.`employee_id` ); 

# EXISTS与NOT EXISTS关键字

# 查询公司管理者的employee_id,last_name,job_id,department_id信息
SELECT employee_id,last_name,job_id,department_id FROM employees e WHERE employee_id IN (SELECT manager_id FROM employees );
# 方式一： (自连接)
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id FROM employees emp JOIN employees mgr ON emp.`manager_id`=mgr.`employee_id` ;
# 方式二： (子查询)
SELECT employee_id,last_name,job_id,department_id FROM employees e WHERE employee_id IN (SELECT manager_id FROM employees );
# 方式三： (使用EXISTS)
SELECT employee_id,last_name,job_id,department_id FROM employees e WHERE EXISTS ( SELECT * FROM employees e2 WHERE e.`employee_id`=e2.`manager_id` );

# 查询departments表中，不存在于employees表中的部门的department_id和department_name
SELECT department_id,department_name FROM departments d WHERE NOT EXISTS (SELECT department_id FROM employees e WHERE d.`department_id`=e.`department_id`);
# 方式二： 
SELECT d.department_id,d.department_name FROM employees e RIGHT JOIN departments d ON e.`department_id`=d.`department_id` WHERE e.`department_id` IS NULL ;

# 课后习题：
#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary FROM employees e1 WHERE e1.`department_id`=( SELECT department_id FROM employees e2 WHERE e2.`last_name`='Zlotkey' );
#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,last_name,salary FROM employees e WHERE salary >( SELECT AVG(salary) FROM employees );
#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name，job_id, salary
SELECT last_name,job_id,salary FROM employees WHERE salary > ALL ( SELECT salary FROM employees WHERE job_id='SA_MAN' );
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name FROM employees WHERE department_id IN ( SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%'  );
#5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id FROM employees e JOIN departments d ON e.`department_id`=d.`department_id` WHERE e.department_id IN (SELECT department_id FROM departments WHERE location_id=1700 ) ;
#6.查询管理者是King的员工姓名和工资
SELECT last_name,salary FROM employees e WHERE e.`manager_id`IN(SELECT employee_id FROM employees WHERE last_name='King');
#7.查询工资最低的员工信息: last_name，salary 
SELECT last_name,salary FROM employees e WHERE salary=( SELECT MIN(salary) FROM employees) ;
#8.查询平均工资最低的部门信息（难）
  --  方式一：四层嵌套子查询
SELECT * 
FROM departments
WHERE department_id=(
		      SELECT department_id 
		      FROM employees 
		      GROUP BY department_id 
		      HAVING AVG(salary)=(
		                           SELECT MIN(avg_sala) 
		                           FROM (
		                                  SELECT AVG(salary) 'avg_sala' 
		                                  FROM employees 
		                                  GROUP BY department_id ) yuzai ) );
  -- 方式二： 多行子查询 ( ALL / SOME )
SELECT * 
FROM departments
WHERE department_id=(
		      SELECT department_id 
		      FROM employees 
		      GROUP BY department_id 
		      HAVING AVG(salary) <=ALL ( SELECT AVG(salary) 'avg_sala' 
		                                  FROM employees 
		                                  GROUP BY department_id )
		      );
  -- 方式三:  LIMIT
SELECT * 
FROM departments
WHERE department_id=(
		      SELECT department_id 
		      FROM employees 
		      GROUP BY department_id 
		      HAVING AVG(salary)=(

					SELECT AVG(salary) 'avg_sala' 
					FROM employees 
					GROUP BY department_id 
					ORDER BY avg_sala ASC
					LIMIT 1 )
		     );
  -- 方式四：直接将department的信息表与最小平均值结果表连接
SELECT d.* FROM departments d,(
				SELECT department_id,AVG(salary) 'avg_sala' 
				FROM employees 
				GROUP BY department_id 
				ORDER BY avg_sala ASC
				LIMIT 1
			       ) yuzai
WHERE d.`department_id`=yuzai.department_id ;

#9.查询平均工资最低的部门信息和该部门的平均工资(相关子查询)
  --  方式一：四层嵌套子查询
SELECT d.*,( SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id` ) avg_d
FROM departments d
WHERE department_id=(
		      SELECT department_id 
		      FROM employees 
		      GROUP BY department_id 
		      HAVING AVG(salary)=(
		                           SELECT MIN(avg_sala) 
		                           FROM (
		                                  SELECT AVG(salary) 'avg_sala' 
		                                  FROM employees 
		                                  GROUP BY department_id ) yuzai ) );
  -- 方式二： 多行子查询 ( ALL / SOME )
SELECT d.*,( SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id` ) avg_d
FROM departments d
WHERE department_id=(
		      SELECT department_id 
		      FROM employees 
		      GROUP BY department_id 
		      HAVING AVG(salary) <=ALL ( SELECT AVG(salary) 'avg_sala' 
		                                  FROM employees 
		                                  GROUP BY department_id )
		      );
  -- 方式三:  LIMIT
SELECT d.*,( SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id` ) avg_d 
FROM departments d
WHERE department_id=(
		      SELECT department_id 
		      FROM employees 
		      GROUP BY department_id 
		      HAVING AVG(salary)=(

					SELECT AVG(salary) 'avg_sala' 
					FROM employees 
					GROUP BY department_id 
					ORDER BY avg_sala ASC
					LIMIT 1 )
		     );
  -- 方式四：直接将department的信息表与最小平均值结果表连接
SELECT d.*,( SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id` ) avg_d
FROM departments d,(
				SELECT department_id,AVG(salary) 'avg_sala' 
				FROM employees 
				GROUP BY department_id 
				ORDER BY avg_sala ASC
				LIMIT 1
			       ) yuzai
WHERE d.`department_id`=yuzai.department_id ;

#10.查询平均工资最高的job 信息
  -- 方式一:  多层嵌套子查询
SELECT * FROM jobs 
WHERE job_id= (
		SELECT job_id FROM employees GROUP BY job_id 
		HAVING AVG(salary)= (SELECT MAX(avg_sal) FROM ( SELECT AVG(salary)'avg_sal' FROM employees GROUP BY job_id ) yuzai ) 
	      );
  -- 方式二：多行子查询
SELECT * FROM jobs 
WHERE job_id= (
		SELECT job_id FROM employees GROUP BY job_id 
		HAVING AVG(salary) >=ALL ( SELECT AVG(salary)'avg_sal' FROM employees GROUP BY job_id ) 
	      );
 -- 方式三： LIMIT
SELECT * FROM jobs 
WHERE job_id= (
		SELECT job_id FROM employees GROUP BY job_id 
		HAVING AVG(salary) = ( SELECT AVG(salary)'avg_sal' FROM employees GROUP BY job_id ORDER BY avg_sal DESC LIMIT 1 ) 
	      );
  --  方式四：把结果作为表直接连接
SELECT j.* 
FROM jobs j,(SELECT job_id,AVG(salary)'avg_sal' FROM employees GROUP BY job_id ORDER BY avg_sal DESC LIMIT 1 ) yuzai
WHERE j.`job_id`=yuzai.job_id ; 

#11.查询平均工资高于公司平均工资的部门有哪些?

SELECT department_id FROM employees WHERE department_id IS NOT NULL GROUP BY department_id HAVING AVG(salary)>( SELECT AVG(salary) FROM employees s ) ;

#12.查询出公司中所有manager的详细信息
SELECT * FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL );

#13.各个部门中最高工资中最低的那个部门的最低工资是多少?
 -- 方式一： 嵌套子查询
SELECT MIN(salary) FROM employees WHERE department_id = (
							SELECT department_id 
							FROM employees 
							GROUP BY department_id 
							HAVING MAX(salary)= (SELECT MIN(max_sal) 
							                     FROM ( SELECT MAX(salary) max_sal 
							                            FROM employees 
							                            GROUP BY department_id ) yuzai 
								                   )
							);
							
 -- 方式二：ALL / SOME
SELECT MIN(salary) FROM employees WHERE department_id = (
							SELECT department_id 
							FROM employees 
							GROUP BY department_id 
							HAVING MAX(salary) <= ALL( SELECT MAX(salary) max_sal 
							                            FROM employees 
							                            GROUP BY department_id )
							);
 -- 方式三： LIMIT 
SELECT MIN(salary) FROM employees WHERE department_id = (
							SELECT department_id 
							FROM employees 
							GROUP BY department_id 
							HAVING MAX(salary) = ( SELECT MAX(salary) max_sal 
							                            FROM employees 
							                            GROUP BY department_id
							                            ORDER BY max_sal ASC
							                            LIMIT 1     
							                      )
							);
 -- 方式四： 结果表
 						

#14.查询平均工资最高的部门的manager 的详细信息: last_name，department_id, email, salary
 -- 方式一：
SELECT last_name,department_id,email,salary FROM employees WHERE employee_id IN (
									         SELECT DISTINCT manager_id 
									         FROM employees 
									         WHERE department_id=( 
													SELECT DISTINCT department_id 
													FROM employees 
													WHERE department_id IS NOT NULL
													GROUP BY department_id 
													HAVING AVG(salary)>=
													                  ALL( SELECT AVG(salary) 
													                       FROM employees 
															       GROUP BY department_id )
						                                                     ) AND manager_id IS NOT NULL
										) ;
  -- 方式二： 
SELECT last_name,department_id,email,salary FROM employees WHERE employee_id IN (
									         SELECT DISTINCT manager_id 
									         FROM employees 
									         WHERE department_id=( 
													SELECT DISTINCT department_id 
													FROM employees 
													WHERE department_id IS NOT NULL
													GROUP BY department_id 
													HAVING AVG(salary)>=
													                  ALL( SELECT AVG(salary) 
													                       FROM employees 
															       GROUP BY department_id )
						                                                     ) AND manager_id IS NOT NULL
										) ; 
										
#15.查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

 -- 方式一：NOT IN ( 通常NOT IN 的地方可以优化为 NOT EXISTS )
SELECT DISTINCT department_id FROM departments WHERE department_id NOT IN (
							SELECT DISTINCT department_id FROM employees WHERE job_id = 'ST_CLERK' 
							) ;
 -- 方式二：NOT EXISTS 
 SELECT department_id 
 FROM departments d
 WHERE NOT EXISTS (
		   SELECT * FROM employees e WHERE d.`department_id`=e.`department_id` AND e.`job_id`='ST_CLERK'
		   );

#16.选择所有没有管理者的员工的last_name
SELECT last_name FROM employees WHERE employee_id IN (
						      SELECT employee_id FROM employees WHERE manager_id IS NULL 
						     );
 -- 方式二:
 SELECT last_name 
 FROM employees emp 
 WHERE NOT EXISTS (
		    SELECT * FROM employees mag 
		    WHERE emp.`manager_id`=mag.`employee_id`
		  ) ;
						    						     
#17.查询员工号、姓名、雇用时间、工资，其中员工的管理者为'De Haan'
  -- 方式一： 使用IN的地方 可以优化为 EXISTS   
SELECT employee_id,last_name,hire_date,salary FROM employees WHERE employee_id IN(
										SELECT employee_id 
										FROM employees 
										WHERE manager_id = (
												    SELECT employee_id 
												    FROM employees 
												    WHERE last_name = 'De Haan'
												    )
										); 
  -- 方式二： EXISTS
SELECT employee_id,last_name,hire_date,salary FROM employees emp WHERE EXISTS (
									       SELECT * FROM employees mag 
									       WHERE emp.`manager_id`=mag.`employee_id`
									       AND mag.`last_name`='De Haan' 
									  );


#18.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资(相关子查询)
 -- 方式一：
SELECT employee_id,salary,last_name FROM employees emp WHERE salary>(
								      SELECT AVG(salary) 
								      FROM employees emp2
								      WHERE emp.`department_id`=emp2.`department_id`
								      GROUP BY department_id 		 
								    ) ;       
 -- 方式二：在FROM中声明子查询 
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
		  SELECT department_id,AVG(salary) avg_sal
		  FROM employees
   		  GROUP BY department_id
		 ) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal ;

#19.查询每个部门下的部门人数大于5的部门名称(相关子查询)
		       
SELECT department_name 
FROM departments d
WHERE 5 < (
	   SELECT COUNT(*)
	   FROM employees e
	   WHERE d.department_id = e.`department_id`
	  );
		       

#20.查询每个国家下的部门个数大于2的国家编号(相关子查询)
SELECT country_id  
FROM locations l
WHERE 2<(
	  SELECT COUNT(*)
	  FROM departments d
	  WHERE l.`location_id`=d.`location_id`
	) 




# 总结： 子查询的编写技巧
	--  如果子查询相对教简单，建议从外往里写。要是子查询较为复杂,建议从里往外写。
        --  如果是相关子查询，通常是从外往里写。

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
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378

创建和管理表

数据库相关操作

CREATE DATABASE IF NOT EXISTS mytest2 CHARACTER SET 'gbk' ; 
更改数据库字符集
ALTER DATABASE mytest2 CHARACTER SET 'utf8';
删除数据库
DROP DATABASE IF EXISTS mytest1 ;

①查看当前已连接的数据库
SHOW DATABASE ;
②查看当前数据库中的数据表
SHOW TABLES ;
③查看当前使用的是哪个数据库
SELECT DATABASE() FROM DUAL ;
④查看指定数据库下的数据表 
SHOW TABLES FROM atguigudb ;
⑤查看创建数据库结构
SHOW CREATE DATABASE atguigudb;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

表的操作

创建表

CREATE TABLE IF NOT EXISTS 表名 ( ) ;

创建数据表 
    方式一
CREATE TABLE IF NOT EXISTS myemp1( id INT,emp_name VARCHAR(15),hire_date DATE );
	方式二
CREATE TABLE myemp2 AS SELECT employee_id,last_name,salary FROM employees ;

查看表结构
DESC myemp1;
查看创建表的语句结构
SHOW CREATE TABLE myemp1; 
查看表数据
SELECT * FROM myemp1;

#练习一：创建一个表employees_copy,实现对employees表的复制,包括表数据。
CREATE TABLE employees_copy AS SELECT * FROM employees ;
#练习二：创建一个表employees_blank,实现对employees表的复制,不包括表数据
CREATE TABLE employees_blank AS SELECT * FROM employees WHERE 1=2 /*employee_id is null 有风险 */; 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

修改表

ALTER TABLE ADD/MODIFY/CHANGE/DROP COLUMN

添加一个字段
ALTER TABLE myemp1 ADD salary DOUBLE(10,2) ; /* 默认添加到表中的最后一个字段的位置 */
ALTER TABLE myemp1 ADD phone_number VARCHAR(20) FIRST; /* 添加至第一个字段 */
ALTER TABLE myemp1 ADD email VARCHAR (45) AFTER emp_name ; /* 添加至emp_name的后面 */
修改一个字段: 数据类型、长度、默认值(略)  
ALTER TABLE myemp1 MODIFY emp_name VARCHAR(25) ;
ALTER TABLE myemp1 MODIFY emp_name VARCHAR(35) DEFAULT 'aaa' ; 
重命名一个字段
ALTER TABLE myemp1 CHANGE salary monthly_salary DOUBLE (10,2) ;
ALTER TABLE myemp1 CHANGE email my_email VARCHAR(50); 
删除一个字段
ALTER TABLE myemp1 DROP COLUMN my_email; 
1
2
3
4
5
6
7
8
9
10
11
12

重命名表

RENAME TABLE 表名 TO 表名 ;

重命名表
  方式一：
RENAME TABLE myemp1 TO myemp11;
  方式二：
ALTER TABLE myemp2 RENAME TO myemp12;
1
2
3
4
5

删除表

DROP TABLE IF EXISTS 表名

删除表
  -- DROP 
DROP TABLE IF EXISTS myemp12;
1
2
3

清空表

TRUNCATE TABLE 表名

-- 清空表中所有数据,但表结构保留。(TRUNCATE)
TRUNCATE TABLE employees_copy;
1
2

COMMIT 与 ROLLBACK

COMMIT:提交数据。一旦执行COMMIT,则数据就被永久保存在数据库中,数据不可再回滚。
ROLLBACK:回滚数据。一旦执行ROLLBACK,则可以实现数据的回滚。回滚到最近的一次COMMIT之后。

对比 TRUNCATE 和 DELETE FROM

相同点：都可以实现对表中所有数据的删除,同时保留表结构。
不同点：
TRUNCATE TABLE: 一旦执行此操作,表数据全部清除。数据不可回滚。
DELETE FROM: 一旦执行此操作,表数据可以全部清除(不带WHERE)。同时,数据可以实现回滚

DDL 和 DML的说明

①DDL的操作一旦执行,就不可回滚。
(执行完DDL操作之后,会自动执行一次COMMIT)

②DML的操作默认情况,一旦执行,也是不可回滚(也会自动执行一次COMMIT)。但是,如果在执行DML之前,执行了 SET autocommit=FALSE (不会再执行COMMIT),则执行的DML操作就可以实现回滚。

演示：

# 演示：TRUNCATE TABLE
COMMIT;
SELECT * FROM myemp13;
TRUNCATE TABLE myemp13;
ROLLBACK; 
# 演示：DELETE FROM
COMMIT;
SELECT * FROM myemp13;
SET autocommit=FALSE;
DELETE FROM myemp13; 
ROLLBACK; -- 回滚操作

1
2
3
4
5
6
7
8
9
10
11
12

练习

#1.创建test01_office,指明字符集为UTF8。并在此数据库下执行下述操作。
CREATE DATABASE IF NOT EXISTS test01_office CHARACTER SET 'utf8' ;
#2.创建表dept01
/*
字段 类型
id   INT(7)
NAME VARCHAR(25)
*/
USE test01_office;
CREATE TABLE IF NOT EXISTS dept01( id INT(7),NAME VARCHAR(25));
#3.将表departments中的数据插入新表dept02中
CREATE TABLE dept02 AS SELECT * FROM atguigudb.`departments`;
#4.创建表emp01
/*
字段        类型
id          INT(7)
first_name  VARCHAR(25)
last_name   VARCHAR(25)
dept_id     INT(7)
*/
CREATE TABLE IF NOT EXISTS emp01 ( id INT(7),first_name VARCHAR(25),last_name VARCHAR(25),dept_id INT(7) );
#5.将列last_name的长度增加到50
ALTER TABLE emp01 MODIFY last_name INT(50); 
#6.根据表employees创建emp02
CREATE TABLE IF NOT EXISTS emp02 AS  SELECT * FROM atguigudb.`employees`;
#7.删除表emp01
COMMIT ;
SET autocommit=FALSE ;
DELETE FROM emp01; -- DELECT 为清空数据
DROP TABLE IF EXISTS emp01;
#8.将表emp02重命名为emp01
RENAME TABLE emp02 TO emp01;
#9.在表dept02和emp01中添加新列test_column,并检查所做的操作
ALTER TABLE dept02 ADD test_column VARCHAR(20);
ALTER TABLE emp01 ADD test_column VARCHAR(20);
#10.直接删除表emp01中的列test_cloumn 
ALTER TABLE emp01 DROP COLUMN test_column ;

# 练习2：
#1.创建数据库test02_market
CREATE DATABASE IF NOT EXISTS test02_market CHARACTER SET 'utf8' ;
#2.创建数据表customers
USE test02_market;
CREATE TABLE IF NOT EXISTS customers( c_num INT(10),c_name VARCHAR(50),c_contact VARCHAR(50),c_city VARCHAR(50),c_birth DATE ) ;
#3.将c_contact字段移动到c_birth字段后面
SELECT * FROM customers;
ALTER TABLE customers MODIFY c_contact VARCHAR(50) AFTER c_birth ;
#4.将c_name字段数据类型改为 varchar(70)
ALTER TABLE customers MODIFY c_name VARCHAR(70);
#5.将c_contact字段改名为c_phone 
ALTER TABLE customers CHANGE c_contact c_phone VARCHAR(50);
#6.增加C_gender字段到c_name后面,数据类型为char(1)
ALTER TABLE customers ADD c_gender CHAR(1) AFTER c_name;
#7.将表名改为customers_info 
RENAME TABLE customers TO customers_info;
#8.删除字段c_city 
DESC customers_info;
ALTER TABLE customers_info DROP COLUMN c_city;

# 练习三：
#1.创建数据库test03_company 
CREATE DATABASE test03_company CHARACTER SET 'utf8' ; 
#2.创建表offices 
USE test03_company;
CREATE TABLE offices ( officeCode INT,city VARCHAR(30),address VARCHAR(50),country VARCHAR(50),postalCode VARCHAR(25) );
DESC offices;
#3.创建表employees
CREATE TABLE IF NOT EXISTS employees(
empNum INT,
lastName VARCHAR(50),
firstName VARCHAR(50),
mobile VARCHAR(25),
`code` INT,
jobTitle VARCHAR(50),
birth DATE,
note VARCHAR(255),
sex VARCHAR(5)
);
#4.将表employees的mobile字段修改到code字段后面
DESC employees;
ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER CODE;
#5.将表employees的birth字段改名为birthday
ALTER TABLE employees CHANGE birth birthday DATE ;
#6.修改sex字段,数据类型为char(1)
ALTER TABLE employees MODIFY sex CHAR(1);
#7.删除字段note
ALTER TABLE employees DROP COLUMN note ;
#8.增加字段名favoriate_activity,数据类型为varchar(100)
ALTER TABLE employees ADD favoriate_activity VARCHAR(100) ;
#9.将表employees的名称修改为employees_info
RENAME TABLE employees TO employees_info;

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

数据处理之增删改

添加数据

INSERT INTO…VALUES…
方式一： 一条条添加数据

INSERT INTO emp1( id,`name`,hire_date,salary ) VALUES ( 1,'鱼仔','2002-12-16',45000 );
INSERT INTO emp1( id,salary,`name` ) VALUES ( 2,8000,'jack' ); 
INSERT INTO emp1( id,`name`,hire_date,salary )VALUES ( 3,'喜羊羊','1999-03-09',7600 ),(4,'美羊羊','1759-10-09',1000);
1
2
3

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

INSERT INTO emp1( id,`name`,hire_date,salary )
SELECT employee_id,last_name,hire_date,salary 
FROM employees 
WHERE department_id IN (70,60);
/*
 注意：
     emp1表中要添加数据的字段的长度不能低于employees表中查询的字段的长度。
    如果emp1表中要添加数据的字段的长度低于employees表中查询的字段的长度的话,就有添加不成功的风险
*/
1
2
3
4
5
6
7
8
9

更新数据 (修改数据)

UPDATE … SET … WHERE …
可以实现批量修改数据。

UPDATE emp1 SET hire_date=NOW()WHERE id=2 ; 	
UPDATE emp1 SET hire_date=NOW(),salary=10 WHERE id=2 ; -- 同时修改一条数据的多个字段

练习：将表中姓名中包含字符a的提薪20% 
UPDATE emp1 SET salary=salary*1.2 WHERE `name` LIKE '%a%' ;

-- 修改数据时,可能修改不成功(可能是由于约束的影响造成的)
 -- update employees set department_id=10000 where employee_id=100 ; deaprtments表中根本就没有10000号部门 
1
2
3
4
5
6
7
8

删除数据

DELETE FROM … WHERE

DELETE FROM emp1 WHERE id=204 ;

-- delete from departments where department_id=50; (因为约束影响,导致删除失败,如果将50号部门删除,employees表中的50号部门的员工的部门号便会受影响)
1
2
3

注意点

/*

DML操作默认情况下,执行完以后都会自动提交数据( COMMIT )。
如果希望不自动提交,可使用 SET autocommit=FALSE。
之后,关于DML的操作便可进行数据回滚 (ROLLBACK)。

*/

小结：
INSERT INTO ... VALUES  添加数据
UPDATE ... SET ... WHERE 更新数据
DELETE FROM ... WHERE  删除数据
1
2
3
4

MySQL8.0新特性 : 计算列

USE atguigudb;
CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL  #字段c即为计算列
);
INSERT INTO test1(a,b)VALUES(10,20);
SELECT * FROM test1;
UPDATE test1 SET a=100;
1
2
3
4
5
6
7
8
9

综合练习

#5.综合案例
# 1、创建数据库test01_library
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8' ;
# 2、创建表 books，表结构如下：
CREATE TABLE IF NOT EXISTS books( id INT,`name` VARCHAR(50),`authors` VARCHAR(100),price FLOAT,pubdate YEAR,note VARCHAR(100),num INT ) ; 
# 3、向books表中插入记录
	# 1）不指定字段名称，插入第一条记录
INSERT INTO books VALUES( 1,'钢铁是怎么练成的','Ami',62,'1996','nove1',136 );
	# 2）指定所有字段名称，插入第二记录
INSERT INTO books( id,NAME,AUTHORS,price,pubdate,note,num ) VALUES ( 2,"灰太狼抓喜羊羊",'jack',13,'2016','儿童类',199  );
	# 3）同时插入多条记录（剩下的所有记录）
INSERT INTO books( id,NAME,AUTHORS,price,pubdate,note,num ) VALUES (3,'Tol of AAA','Dickes',23,'1995','novel',11),
(4,'EmmaT','Jane lura',35,'1993','joke',11),
(5,'Story of Jane','Jane Tim',40,'2001','novel',0),
(6,'Lovey Day','George Byron',20,'2005','novel',30),
(7,'Old land','Honore Blade',30,'2021','law',0),
(8,'The Battle','Upton Sara',30,'1999','medicine',40),
(9,'Rose Hood','Richard haggard',28,'2008','cartoon',28);
# 4、将小说类型(novel)的书的价格都增加5。
UPDATE books SET price = price+5 WHERE note='novel' ;
# 5、将名称为EmmaT的书的价格改为40，并将书名改为drama。
UPDATE books SET price = 40,`NAME`='drama' WHERE `name`='EmmaT'; 
# 6、删除库存为0的记录。
COMMIT ;
SET autocommit=FALSE ;
DELETE FROM books WHERE num =0;
  -- rollback;
SELECT * FROM books;
# 7、统计书名中包含a字母的书
SELECT `name` '书名含a的书' FROM books WHERE `name` LIKE '%a%'; 
# 8、统计书名中包含a字母的书的数量和库存总量
SELECT COUNT(*) '书名含a的书数',SUM(num) '库存总量' FROM books WHERE `name` LIKE '%a%' ;
# 9、找出“novel”类型的书，按照价格降序排列
SELECT `name`,note,price FROM books WHERE note='novel' ORDER BY price DESC ;
# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
SELECT * FROM books ORDER BY num DESC,note ASC ; 
# 11、按照note分类统计书的数量
SELECT note,COUNT(*) FROM books GROUP BY note ; 
# 12、按照note分类统计书的库存量，显示库存量超过30本的
SELECT note,num FROM books GROUP BY note  HAVING SUM(num)>30 ; 
# 13、查询所有图书，每页显示5本，显示第二页 
SELECT * FROM books LIMIT 5,5 ;  

# 14、按照note分类统计书的库存量，显示库存量最多的
SELECT note,SUM(num) sum_num FROM books GROUP BY note ORDER BY sum_num DESC LIMIT 1; 
# 15、查询书名达到10个字符的书，不包括里面的空格
SELECT NAME FROM books WHERE  CHAR_LENGTH(REPLACE(NAME,' ','') ) >=10 ;
# 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，
#cartoon显示卡通，joke显示笑话
SELECT `name`,note,CASE WHEN note='novel' THEN '小说'
                        WHEN note='law'   THEN '法律'
                        WHEN note='medicine' THEN '医药'
                        WHEN note='cartoon' THEN '卡通'
                        WHEN note='joke' THEN '笑话'
                        ELSE 'fuck' END '类型'
FROM books ;
# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，
#显示畅销，为0的显示需要无货
SELECT NAME AS "书名",num AS "库存", CASE WHEN num > 30 THEN '滞销'
					  WHEN num > 0 AND num < 10 THEN '畅销'
					  WHEN num = 0 THEN '无货'
					  ELSE '正常'
					  END "显示状态"
FROM books;

# 18、统计每一种note的库存量，并合计总量
SELECT IFNULL(note,'合计库存总量') AS note,SUM(num) FROM books GROUP BY note WITH ROLLUP; 
# 19、统计每一种note的数量，并合计总量
SELECT IFNULL(note,'合计总量') AS note,COUNT(*) FROM books GROUP BY note WITH ROLLUP; 
# 20、统计库存量前三名的图书
SELECT * FROM books ORDER BY num DESC  LIMIT 3 ;
# 21、找出最早出版的一本书
SELECT `name`,pubdate FROM books ORDER BY pubdate ASC LIMIT 1 ;
# 22、找出novel中价格最高的一本书
SELECT *
FROM books
WHERE note = 'novel'
ORDER BY price DESC
LIMIT 0,1;
# 23、找出书名中字数最多的一本书，不含空格
SELECT *
FROM books
ORDER BY CHAR_LENGTH(REPLACE(NAME,' ','')) DESC
LIMIT 0,1;
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

增删改的专项练习

MySQL数据类型

# MySQL数据类型  
 -- 测试环境为MySQL5.7

#1. 关于属性: character set name

# 创建数据库时指明字符集
CREATE DATABASE IF NOT EXISTS dbtest12 CHARACTER SET 'utf8';
# 创建表时,指明表的字符集
CREATE TABLE temp ( id INT) CHARACTER SET 'utf8' ;
# 创建表时,指明表中的字段时,可以指定字段的字符集
CREATE TABLE temp1( id INT,NAME VARCHAR(15) ) CHARACTER SET 'gbk';  

SHOW VARIABLES LIKE 'character_%';
SHOW CREATE DATABASE dbtest12;
SHOW CREATE TABLE temp;

#2. 整型数据类型

-- 整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT
CREATE TABLE test_int1( f1 TINYINT,f2 SMALLINT,f3 MEDIUMINT, f4 INT,f5 BIGINT  );
DESC test_int1;

-- 整型类型的可选属性
-- 可选属性1. M
CREATE TABLE test_int2( f1 INT,f2 INT(5),f3 INT(5) ZEROFILL ); -- f3 INT(5) ZEROFILL 便使用了M属性,显示宽度为5,当insert的值不足5位时,使用0填充。②当使用ZEROFILL时,自动会添加UNSIGNED属性。
INSERT INTO test_int2 ( f1,f2 )VALUES(123,123),(123456,123456);
SELECT * FROM test_int2;
INSERT INTO test_int2(f3) VALUES(123),(123456);  
/*
M: 表示显示宽度，M的取值范围是(0, 255)。例如，int(5)：当数据宽度小于5位的时候在数字前面需要用
     字符填满宽度。该项功能需要配合“ ZEROFILL ”使用，表示用“0”填满宽度，否则指定显示宽度无效。
如果设置了显示宽度，那么插入的数据宽度超过显示宽度限制，会不会截断或插入失败？
    答案：不会对插入的数据有任何影响，还是按照类型的实际宽度进行保存，即 显示宽度与类型可以存储的
    值范围无关。
整型数据类型可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值。
从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。
*/

-- 可选属性2. UNSIGNED
CREATE TABLE test_int3 ( f1 INT UNSIGNED);
DESC test_int3;
INSERT INTO test_int3(f1) VALUES(56894) ;
/*
 UNSIGNED: 无符号类型（非负）,所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0。
          所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型。
 int类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)。 
*/

--  可选属性3. ZEROFILL
/*
ZEROFILL: 0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，
          用0在左边填充，如果超过M位，只要不超过数据存储范围即可。
原来,在int(M)中,M的值跟int(M)所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 bytes 的存储空间。
      也就是说，int(M)，必须和UNSIGNED ZEROFILL一起使用才有意义。如果整数值超过M位，就按照实际位数存储。只是无须再用字符 0 进行填充。
*/

# 适用场景
/*
TINYINT：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
SMALLINT：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
MEDIUMINT：用于较大整数的计算，比如车站每日的客流量等。
INT、INTEGER：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。
BIGINT：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

要注意的是，在实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此，
建议你首先确保数据不会超过取值范围，在这个前提之下，再去考虑如何节省存储空间。
*/

# 浮点类型
-- FLOAT 表示单精度浮点数； DOUBLE 表示双精度浮点数;
/*
 FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢？
 Float占用字节数少,取值范围小;DOUBLE 占用字节数多,取值范围大。
 为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分呢？
 MySQL 存储浮点数的格式为： 符号(S) 、 尾数(M) 和 阶码(E) 。因此，无论有没有符号，MySQL 的浮点数都会存储表示符号的部分。因此， 所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。
*/

/*
 对于浮点类型，在MySQL中单精度值使用 4 个字节，双精度值使用 8 个字节。
 MySQL允许使用 ‘非标准语法’ （其他数据库未必支持，因此如果涉及到数据迁移,则最好不要这么用）： FLOAT(M,D) 或 DOUBLE(M,D) 。这里，M称为 精度 ，D称为 标度 。
      (M,D)中 M=整数位+小数位，D=小数位。 D<=M<=255，0<=D<=30。例如，定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会报错。 
 FLOAT和DOUBLE类型在不指定(M,D)时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示
 说明：浮点类型，也可以加 UNSIGNED ，但是不会改变数据范围，例如：FLOAT(3,2) UNSIGNED仍然只能表示0-9.99的范围。
*/
-- 从MySQL 8.0.17开始，FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用，将来可能移除。
CREATE TABLE test_double1 ( f1 FLOAT,f2 FLOAT(5,2),f3 DOUBLE,f4 DOUBLE(5,2) );
DESC test_double1;
INSERT INTO test_double1(f1,f2) VALUE ( 123.45,123.45 ); -- 正常存储
SELECT * FROM test_double1;
INSERT INTO test_double1(f3,f4) VALUE ( 123.45,123.456 ); -- f4字段存储的123.456位数超出,会自动进行四舍五入。

# 测试FLOAT和DOUBLE精度问题
CREATE TABLE test_double2(f1 DOUBLE );
INSERT INTO test_double2 VALUES(0.47),(0.44),(0.19);
SELECT SUM(f1) FROM test_double2 ;
SELECT SUM(f1)=1.1,1.1=1.1 FROM test_double2; -- 为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。

# 定点数类型
/*
  MySQL中的定点数类型只有 DECIMAL 一种类型。
*/
# DECIMAL替换DOUBLE 体现精度
CREATE TABLE test_decimall( f1 DECIMAL,f2 DECIMAL(5,2) );
DESC test_decimall;
ALTER TABLE test_double2 MODIFY f1 DECIMAL(5,2) ;
DESC test_double2;
SELECT SUM(f1)=1.1,1.1=1.1 FROM test_double2; 

#  位类型：BIT
CREATE TABLE test_bit1( f1 BIT,f2 BIT(5),f3 BIT(64) );
DESC test_bit1;
INSERT INTO test_bit1(f1)VALUES(0),(1);
INSERT INTO test_bit1(f3)VALUES(32);
SELECT * FROM test_bit1; -- 结果以16进制显示
# 加0以后，可以以十进制的方式显示数据
SELECT f1+0,f2+0,f3+0 FROM test_bit1;

# 日期和时间类型
 # ① YEAR类型 
   -- YEAR类型用来表示年份,在所有的日期时间类型中所占用的存储空间最小,只需要1个字节的存储空间。
   -- 以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。
   -- 以2位字符串格式表示YEAR类型最小值为00，最大值为99。(从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用)
      /*当取值为01到69时，表示2001到2069；
	当取值为70到99时，表示1970到1999；
	当取值整数的0或00添加的话，那么是0000年；
	当取值是日期/字符串的'0'添加的话，是2000年。
      */
CREATE TABLE test_year ( f1 YEAR,f2 YEAR );
DESC test_year;
INSERT INTO test_year (f1) VALUES('2021'),('1996');
INSERT INTO test_year (f1) VALUES(0),('69'),('00'),('78'); 
SELECT * FROM test_year;

# ② DATE类型
  -- DATE类型表示日期,没有时间部分,格式为 YYYY-MM-DD,其中，YYYY表示年份，MM表示月份，DD表示日期。需要3个字节的存储空间。
  -- 以YYYY-MM-DD格式或者YYYYMMDD格式表示的字符串日期，其最小取值为1000-01-01，最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
  -- 以YY-MM-DD格式或者YYMMDD格式表示的字符串日期，此格式中,年份为两位数值或字符串满足YEAR类型的格式条件为: 当年份取值为00到69时,会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。
  -- 使用CURRENT_DATE()或者NOW()函数,会插入当前系统的日期。
CREATE TABLE test_date1(f1 DATE );
DESC test_date1;
INSERT INTO test_date1 VALUES ('2020-10-01'), ('20201001'),(20201001);
INSERT INTO test_date1 VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'),('99-01-01'), ('990101');
INSERT INTO test_date1(f1) VALUE (NOW());
SELECT * FROM test_date1;

# ③ TIME类型
  -- TIME类型用来表示时间,不包含日期部分。在MySQL中,需要3个字节的存储空间来存储TIME类型的数据,
  -- 可以使用“HH:MM:SS”格式来表示TIME类型,其中,HH表示小时,MM表示分钟,SS表示秒。
   /*
       在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式。
  （1）可以使用带有冒号的字符串,比如'D HH:MM:SS'、'HH:MM:SS'、'HH:MM'、'D HH:MM'、'D HH'或'SS'格式，
       都能被正确地插入TIME类型的字段中。其中D表示天,其最小值为0,最大值为34。
       如果使用带有D格式的字符串插入TIME类型的字段时,D会被转化为小时,计算格式为D*24+HH。
       当使用带有冒号并且不带D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10。
  
  （2）可以使用不带有冒号的字符串或者数字，格式为' HHMMSS '或者 HHMMSS 。
       如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储。
       比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10，而不是12:10:00。  
       
  （3）使用CURRENT_TIME() 或者 NOW(),会插入当前系统的时间。

   */
CREATE TABLE test_time1(f1 TIME );
INSERT INTO test_time1(f1) VALUES ( '20 6:45:03' );
INSERT INTO test_time1 VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');
INSERT INTO test_time1 VALUES ('123520'), (124011),(1210);
INSERT INTO test_time1 VALUES (NOW()), (CURRENT_TIME());
SELECT * FROM test_time1;

# ④ DATETIME类型

  /*

  DATETIME类型在所有的日期时间类型中占用的存储空间最大,总共需要 8 个字节的存储空间。
  在格式上为DATE类型和TIME类型的组合,可以表示为 YYYY-MM-DD HH:MM:SS ，
  其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。
  
  在向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件。 
  以YYYY-MM-DD HH:MM:SS格式,或者YYYYMMDDHHMMSS格式的字符串,插入DATETIME类型的字段时,最小值为1000-01-01 00:00:00,最大值为9999-12-03 23:59:59。
    以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时,会被转化为YYYY-MM-DD HH:MM:SS格式。
  使用函数 CURRENT_TIMESTAMP() 和 NOW(),可以向DATETIME类型的字段插入系统的当前日期和时间。

  */
CREATE TABLE test_datetime1( f1 DATETIME );
INSERT INTO test_datetime1(f1) VALUES( '2002-12-16 6:5:2' );
INSERT INTO test_datetime1 VALUES ('2021-01-01 06:50:30'), ('20210101065030');
INSERT INTO test_datetime1 VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'),('200101000000');
INSERT INTO test_datetime1 VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
INSERT INTO test_datetime1 VALUES (CURRENT_TIMESTAMP()), (NOW());
SELECT * FROM test_datetime1;

# ⑤ TIMESTAMP类型

/*
 TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，
 都是YYYY-MM-DD HH:MM:SS,需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，
 只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中,UTC表示世界统一时间，也叫作世界标准时间。
 
 存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。
 因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。
 
 向TIMESTAMP类型的字段插入数据时，当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS时,
 两位数值的年份同样符合YEAR类型的规则条件，只不过表示的时间范围要小很多。
  
 如果向TIMESTAMP类型的字段插入的时间超出了TIMESTAMP类型的范围，则MySQL会抛出错误信息。

*/
CREATE TABLE test_timestamp1( f1 TIMESTAMP );
INSERT INTO test_timestamp1 VALUES (' 1999-01-01 02:04:05 '),('19990101020405 '),('99-01-01 03:04:05'),('990101030405');
INSERT INTO test_timestamp1 VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');
INSERT INTO test_timestamp1 VALUES (CURRENT_TIMESTAMP()), (NOW());
#Incorrect datetime value 超出时间存储范围
INSERT INTO test_timestamp1 VALUES ('2038-01-20 03:14:07');
SELECT * FROM test_timestamp1;
--  对比TIMESTAMP和DATETIME
CREATE TABLE temp_time( f1 DATETIME,f2 TIMESTAMP );
INSERT INTO temp_time VALUES('2021-9-2 14:45:52','2021-9-2 14:45:52');
INSERT INTO temp_time VALUES(NOW(),NOW());
SELECT * FROM temp_time;
#修改当前的时区
SET time_zone = '+9:00';
SELECT * FROM temp_time;
/*
 TIMESTAMP和DATETIME的区别：

 1.TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
 2.底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
 3.两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
 4.TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。
*/

# 开发中经验
/*

 用得最多的日期时间类型,就是DATETIME。虽然MySQL也支持YEAR（年）、TIME（时间）、DATE（日期）,以及 TIMESTAMP 类型,
 但是在实际项目中,尽量用DATETIME类型。因为这个数据类型包括了完整的日期和时间信息,取值范围也最大,使用起来比较方便。
 毕竟,如果日期时间信息分散在好几个字段,很不容易记,而且查询的时候,SQL语句也会更加复杂。
 此外，一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用 时间戳 ，因为DATETIME虽然直观，但不便于计算。
   时间戳转换函数UNIX_TIMESTAMP()
*/

# 文本字符串类型
  -- MySQL中，文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、LONGTEXT 、 ENUM 、 SET 等类型。
  # CHAR与VARCHAR类型
  /*
     CHAR和VARCHAR类型都可以存储比较短的字符串。
     字符串(文本)类型    特点    长度    长度范围      占用的存储空间
     CHAR(M)           固定长度   M    0 <= M <= 255     M个字节
     VARCHAR(M)        可变长度   M    0 <= M <= 65535  (实际长度 + 1) 个字节    
  */
# char
-- CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符
-- 如果保存时，数据的实际长度比CHAR类型声明的长度小,则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。

CREATE TABLE test_char1( c1 CHAR,c2 CHAR(5) );	
INSERT INTO test_char1(c1) VALUES('a');
INSERT INTO test_char1(c2) VALUES('yuzai');
SELECT * FROM  test_char1;
# varchar
--  VARCHAR(M) 定义时， 必须指定 长度M，否则报错
--  检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。
CREATE TABLE test_char2( c1 VARCHAR(5),c2 VARCHAR(10) );
INSERT INTO test_char2( c2 ) VALUES ('love');
SELECT * FROM test_char2;

#  TEXT类型
   -- 在MySQL中,TEXT用来保存文本类型的字符串,总共包含4种类型，分别为TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT类型。
   -- 由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用CHAR(M)，或者 VARCHAR(M)。

CREATE TABLE test_text1( f1 TEXT );
INSERT INTO test_text1(f1) VALUES ('yzuai love   ');
SELECT CHAR_LENGTH(f1),f1 FROM test_text1;
/*
   TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR，VARCHAR来代替。还有TEXT类型不用加默认值，加了也没用。
   而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表。

*/

#  ENUM类型
-- ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。
CREATE TABLE test_enum( season ENUM( '春','秋','夏','冬','Fuck') );
INSERT INTO test_enum VALUES ('春'),('秋');
-- 可以使用索引进行枚举元素的调用
INSERT INTO test_enum VALUES(1),(4);
-- 忽略大小写
INSERT INTO test_enum VALUES('FUCK');
-- 没有限制非空的情况下,可以添加null值
INSERT INTO test_enum VALUES(NULL);
SELECT * FROM test_enum;

# SET类型
CREATE TABLE test_set( s SET('A','B','C'  ) );
INSERT INTO test_set(s) VALUES ('A'),('A,B');
#插入重复的SET类型成员时，MySQL会自动删除重复的成员
INSERT INTO test_set(s) VALUES ('A,B,C,A');
#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。
INSERT INTO test_set(s) VALUES ('A,B,C,D');
SELECT * FROM test_set;

-- ENUM与SET综合
CREATE TABLE temp_mul(gender ENUM('男','女'),hobby SET('吃饭','睡觉','打豆豆','写代码'));
INSERT INTO temp_mul VALUES('男','吃饭,打豆豆');
SELECT * FROM temp_mul ;

# 二进制类型
--  BINARY与VARBINARY类型
-- BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。
CREATE TABLE test_binary1( f1 BINARY,f2 BINARY(3),f3 VARBINARY(1),f4 VARBINARY(10));-- VARBINARY定义时必须指明m长度
INSERT INTO test_binary1(f2,f4) VALUES ('ab','ab');
  -- BINARY是固定长度的,VARBANARY是变长度的
SELECT LENGTH(f2),LENGTH(f4) FROM test_binary1; 

--  BLOB
 -- BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。
 -- MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象,比如图片、音频和视频等。
 -- 需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据,通常会将图片、音频和视频文件存储到服务器的磁盘上,并将图片、音频和视频的访问路径存储到MySQL中。 
CREATE TABLE test_blob1(id INT,img MEDIUMBLOB);
INSERT INTO test_blob1 (id) VALUES( 1001);


/*

TEXT和BLOB的使用注意事项：
   在使用text和blob字段类型时要注意以下几点，以便更好的发挥数据库的性能。
① BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值
会在数据表中留下很大的" 空洞 "，以后填入这些"空洞"的记录可能长度不同。为了提高性能，建议定期
使用 OPTIMIZE TABLE 功能对这类表进行 碎片整理 。

② 如果需要对大文本字段进行模糊查询，MySQL 提供了 前缀索引 。但是仍然要在不必要的时候避免检
索大型的BLOB或TEXT值。例如，SELECT * 查询就不是很好的想法，除非你能够确定作为约束条件的
WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。

③ 把BLOB或TEXT列 分离到单独的表 中。在某些环境中，如果把这些数据列移动到第二张数据表中，可
以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会 减少主表中的
碎片 ，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过
网络传输大量的BLOB或TEXT值。

*/

# JSON 类型
 -- JSON（JavaScript Object Notation）是一种轻量级的数据交换格式 。
CREATE TABLE test_json(js json);
INSERT INTO test_json (js)VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing","city":"beijing"}}');
SELECT * FROM test_json;
SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province' AS province, js -> '$.address.city' AS city FROM test_json;


# 空间类型 （不作要求，了解即可）
-- MySQL的空间数据类型（Spatial Data Type）对应于OpenGIS类，包括单值类型：GEOMETRY、POINT、LINESTRING、POLYGON以及集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION 。
-- ............

#  小结及选择建议
/*
  在定义数据类型时,如果确定是整数,就用’INT';如果是小数,一定用定点数类型DECIMAL(M,D);如果是日期与时间，就用DATETIME。
  这样做的好处是,首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的,可靠性好，并不意味着高效。比如，
  TEXT 虽然使用方便,但是效率不如CHAR(M)和VARCHAR(M)。
  
  关于字符串的选择，建议参考如下阿里巴巴的《Java开发手册》规范：
    阿里巴巴《Java开发手册》之MySQL数据库：
    1.任何字段如果为非负数，必须是 UNSIGNED
    2.【 强制 】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。 
       说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得
       到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并
       分开存储。
    3.【 强制 】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。
    4.【 强制 】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大
        于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。 

*/


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
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373

约束 constraint

not null 非空约束

not null 非空约束

# 如何查看表中的约束？
SELECT * FROM information_schema.`TABLE_CONSTRAINTS` WHERE table_name='employees';
# 非空约束 not null
-- 在CREATE TABLE 时添加约束
 CREATE TABLE test1( id INT NOT NULL , last_name VARCHAR(15)NOT NULL , email VARCHAR(25) , salary DECIMAL(10,2) );   DESC test1;
 -- 在ALTER TABLE 时添加约束
 ALTER TABLE test1 MODIFY email VARCHAR(25) NOT NULL ;
 -- 在ALTER TABLE 时删除约束 
 ALTER TABLE test1 MODIFY email VARCHAR(25) NULL ;

1
2
3
4
5
6
7
8
9
10

unique 唯一性约束

unique 唯一性约束

-- 在CREATE TABLE 时添加约束
CREATE TABLE test2( id INT UNIQUE /*列级约束*/ ,last_name VARCHAR(15) , email VARCHAR(25) UNIQUE,salary DECIMAL(10,2),CONSTRAINT uk_test2_email UNIQUE(email)/*表级约束*/  );  DESC test2;
SELECT *FROM information_schema.`TABLE_CONSTRAINTS` WHERE table_name='test2';
-- 在ALTER TABLE 时添加约束
  /*方式一*/ALTER TABLE test2 MODIFY id VARCHAR(15) UNIQUE ;
  /*方式二*/ALTER TABLE test2 ADD CONSTRAINT uk_test2_sal UNIQUE (salary);
1
2
3
4
5
6

复合唯一性约束

CREATE TABLE test3( id INT , NAME VARCHAR(15) , PASSWORD VARCHAR(25),CONSTRAINT uk_user_name_pwd UNIQUE (NAME,PASSWORD)); 
INSERT INTO test3 VALUES (1,'jack','Fuckdie' );

CREATE TABLE test4( id INT ,first_name VARCHAR(20) ,salary DECIMAL(10,2) ,UNIQUE( id,salary )   );
DESC test4;
INSERT INTO  test4 VALUES (1,'jack',69555  );
INSERT INTO  test4 VALUES (2,'Tom',69555  );
INSERT INTO  test4 VALUES (1,'jack',69555  );
INSERT INTO  test4 VALUES (1,'jack',69655  );
INSERT INTO  test4 VALUES (1,'jack',69555  ); -- 两个字段全部重复 添加失败
SELECT * FROM test4;

# 删除唯一性索引
SELECT * FROM information_schema.`TABLE_CONSTRAINTS` WHERE table_name='test4';
ALTER TABLE test4 DROP INDEX id ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

primary key 约束 （主键约束）

一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。
MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

 -- 列级约束
CREATE TABLE test5 ( id INT PRIMARY KEY , last_name VARCHAR(25),salary DECIMAL(10,2),email VARCHAR(25) );
 -- 表级约束
CREATE TABLE test6 ( id INT ,last_name VARCHAR(20),salary DECIMAL(10,2),email VARCHAR(25),CONSTRAINT pk_test5_id PRIMARY KEY(id)  ); 
 -- 在ALTER TABLE 时添加主键约束 
ALTER TABLE test8 ADD PRIMARY KEY ( last_name,email ) ;
 -- 删除主键约束
ALTER TABLE test8 DROP PRIMARY KEY ;
1
2
3
4
5
6
7
8
9
10
11
12

AUTO_INCREMENT 自增列

（1）一个表最多只能有一个自增长列
（2）当需要产生唯一标识符或顺序值时，可设置自增长
（3）自增长列约束的列必须是键列（主键列，唯一键列）
（4）自增约束的列的数据类型必须是整数类型
（5）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

-- 在建表时指定自增列
CREATE TABLE test9 (id INT PRIMARY KEY AUTO_INCREMENT,last_name VARCHAR(25));
-- 建表后指定自增列
alter table 表名称 modify 字段名 数据类型 auto_increment;
-- 删除自增约束
#alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除
1
2
3
4
5
6
7
8
9
10
11
12
13

关于AUTO_INCREMENT MySQL 8.0新特性

MySQL 8.0新特性—自增变量的持久化

在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1
在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1
这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。
例:
CREATE TABLE test13 (id INT PRIMARY KEY AUTO_INCREMENT);
INSERT INTO test13 VALUES (0),(0),(0),(0);
DELETE FROM test13 WHERE id=4;
SELECT * FROM test13;
INSERT INTO test13 VALUES (0); -- 此时分配的是5 (正常)
DELETE FROM test13 WHERE id=5; -- 将id为5的记录也进行删除 ( 如果随后再添加一条记录应分配id为6 )
-- 但此时如果重启MySQL服务 再添加数据 就会出现问题
#重启服务器
INSERT INTO test13 VALUES(0); --  此时分配的id值为4!,按重启前的逻辑,此处应该分配6 (不正常)
/* 出现上述结果的主要原因是自增主键没有持久化。在MySQL5.7系统中,对于自增主键的分配规则,是由InnoDB数据字典内部一个计数器来决定的,而该计数器只在内存中维护,
   并不会持久化到磁盘中。当数据库重启时,该计数器会被初始化。 */
----------------------------------------------------------------
-- 在MySQL8.0中,上述测试步骤最后一步的结果为6 (正常)
/*  
MySQL 8.0将自增主键的计数器持久化到重做日志中。每次计数器发生改变,都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。
 这便是8.0的新特性:自增变量的持久化。
  */
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

FOREIGN KEY （主从）约束

作用: 限定某个表的某个字段的引用完整性。
     例：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。
主表（父表）：被引用的表，被参考的表。
从表（子表）：引用别人的表，参考别人的表。

（1）从表的外键列，必须引用/参考主表的主键或唯一约束的列
（2）在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名。
（3）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
（4）删表时，先删从表（或先删除外键约束），再删除主表
（5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
（6）在“从表”中指定外键约束，并且一个表可以建立多个外键约束
（7）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can't create table'database.tablename'(errno: 150)”。
1
2
3
4
5
6
7
8
9
10
11
12

代码

create table 主表名称(字段1 数据类型 primary key,字段2 数据类型);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);

建表时构建主从表
create table dept( #主表
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(#从表
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) 
);

建表后构建
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

约束等级

Cascade方式：  在父表上update/delete记录时，同步update/delete掉子表的匹配记录
Set null方式： 在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
No action方式：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict方式： 同no action， 都是立即检查外键约束
Set default方式 （在可视化工具SQLyog中可能显示空白)：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别
  如果没有指定等级，就相当于Restrict方式。
  对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

演示:
CREATE TABLE dept3 ( dept_id INT PRIMARY KEY AUTO_INCREMENT , dept_name VARCHAR(20));
CREATE TABLE emp3 ( emp_Id INT PRIMARY KEY AUTO_INCREMENT ,emp_name VARCHAR(25),department_id INT,CONSTRAINT fk_emp3_department_id FOREIGN KEY emp3(department_id) REFERENCES dept3(dept_id) ON UPDATE CASCADE ON DELETE RESTRICT );
INSERT INTO dept3 VALUES (10,'IT'),(20,'美工'),(50,'后勤'); 
INSERT INTO emp3 VALUES (1,'小明',10),(2,'小强',10),(3,'小红',50),(4,'yuzai',20);
SELECT * FROM emp3;
SELECT * FROM dept3;
UPDATE emp3 SET department_id=50 WHERE emp_name	='小强'; 
UPDATE dept3 SET dept_id=30 WHERE dept_name='IT';

结论： 对于外键约束,最好是采用:  ON UPDATE CASCADE ON DELETE RESTRICT 的方式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

删除外键约束

(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名
ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;
（2）第二步查看索引名和删除索引。（注意，只能手动删除）
SHOW INDEX FROM 表名称; #查看某个表的索引名
ALTER TABLE 从表名 DROP INDEX 索引名;
1
2
3
4
5
6

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会 因为外键约束的系统开销而变得非常慢 。所以， MySQL 允许你不使用系统自带的外键约束，在 应用层面 完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。
1

开发规范

阿里开发规范
【 强制 】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发 ，不适合 分布式 、 高并发集群 ；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度 。
1
2
3

CHECK 约束

# check 约束
-- 作用：检查某个字段的值是否符号xx要求，一般指的是值的范围
-- 说明：MySQL5.7不支持,MySQL5.7 可以使用check约束,但check约束对数据验证没有任何作用。添加数据时,没有任何错误或警告。 但是MySQL 8.0中可以使用check约束。
（该约束测试环境为8.0）
CREATE TABLE test11 ( id INT,last_name VARCHAR(15),salary DECIMAL(10,2) CHECK(salary>2000) );
INSERT INTO test11 VALUES (1,'方一泽',1500); -- Check constraint 'test11_chk_1' is violated.
INSERT INTO test11 VALUES (1,'方一泽',2500);
SELECT * FROM test11; 
1
2
3
4
5
6
7
8

DEFAULT 约束

# DEFAULT 约束
-- 作用： 给某个字段/某列指定默认值,一旦设置默认值,在插入数据时,如果此字段没有显式赋值,则赋值为默认值。
CREATE TABLE test12( id INT , last_name VARCHAR(15), salary  DECIMAL(10,2) DEFAULT 2000 );
INSERT INTO test12(id,last_name) VALUES(1,'Tom' );
SELECT * FROM test12;
DESC test12;
-- 如何删除默认值约束
alter table 表名称 modify 字段名 数据类型 ;#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 not null; #删除默认值约束，保留非空约束
1
2
3
4
5
6
7
8
9

练习题

---

视图

VIEW

视图的理解

/*
视图的理解
1.视图,可以看做是一个虚拟表,本身是不存储数据的。视图的本质,就可以看做是存储起来的SELECT语句。
2.视图中SELECT语句中涉及到的表,称为基表。
3.针对视图做DML操作,会影响到对应的基表中的数据,反之亦然。
4.视图本身的删除,不会导致基表中数据的删除。
5.视图的应用场景:针对于小型项目,不推荐使用视图。针对于大型项目,可以考虑使用视图。
6.视图的优点:简化查询;控制数据的访问。
*/
1
2
3
4
5
6
7
8
9

创建视图

# 创建视图
CREATE VIEW vu_emp1 AS SELECT employee_id ,last_name,salary  FROM dbtest14.`emps` ;
CREATE VIEW vu_emp2 AS SELECT employee_id '员工号' ,first_name '姓名', salary 'mon_name' FROM emps; -- 查询语句中字段的别名会作为视图中字段的名字
CREATE VIEW vu_emp3(a,b,c,d) AS SELECT employee_id,first_name,salary,manager_id FROM emps WHERE salary>8000 ; -- 小括号内字段个数与SELECT中字段个数相同
CREATE VIEW vu_emp4 AS SELECT department_id,AVG(salary) avg_sal FROM emps WHERE department_id IS NOT NULL GROUP BY department_id;
CREATE VIEW vu_emp5 AS SELECT e.`employee_id`,e.`salary`,d.`department_name` FROM atguigudb.`employees` e JOIN atguigudb.`departments` d ON e.`department_id`=d.`department_id`;
-- 利用视图对数据进行格式化  CONCAT(str1,'(',字段二,')',...)
CREATE VIEW vu_emp6 AS SELECT  CONCAT(e.`employee_id`,'(',department_name,')') 'd_dname' FROM atguigudb.`employees` e JOIN atguigudb.`departments` d ON e.`department_id`=d.`department_id` ;
-- 基于视图创建视图
CREATE VIEW vu_emp7 AS SELECT v1.`last_name` '姓名',v2.`department_name` '部门名称' FROM vu_emp1 v1 JOIN vu_emp5 v2 ON v1.`employee_id`=v2.`employee_id` ;
1
2
3
4
5
6
7
8
9
10

查看视图

# 查看视图
-- 语法1:查看数据库的表对象、视图对象
SHOW TABLES ;
-- 语法2:查看视图的结构
DESCRIBE vu_emp5;
-- 语法3:查看视图的属性信息
SHOW TABLE STATUS LIKE 'vu_emp4';
-- 语法4:查看视图的详细定义信息
SHOW CREATE VIEW vu_emp1;
# 更新视图中的数据
-- 更新视图的数据,会导致基表中数据的修改 
UPDATE vu_emp1 SET salary=20000 WHERE employee_id=101; 
DELETE FROM vu_emp1 WHERE employee_id=101;
-- 同理,更新表中的数据,也会导致视图中的数据的修改
UPDATE emps SET salary=60000 WHERE employee_id=100;
-- 不可更新的视图
UPDATE vu_emp4 SET avg_sal=20000 WHERE department_id=60; -- The target table vu_emp4 of the UPDATE is not updatable
DELETE FROM vu_emp4 WHERE department_id=30; -- The target table vu_emp4 of the DELETE is not updatable
/*
要使视图可更新,视图中的行和底层基本表中的行之间必须存在一对一的关系。另外当视图定义出现如下情况时,视图不支持更新:
  在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作;
  视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作;
  在定义视图的SELECT语句中使用了JOIN联合查询,视图将不支持INSERT和DELETE操作;
  在定义视图的SELECT语句后的字段列表中使用了 数学表达式 或 子查询,视图将不支持INSERT,也不支持UPDATE使用了数学表达式、子查询的字段值;
  在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、UNION 等,视图将不支持INSERT、UPDATE、DELETE;
  在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、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

修改视图

# 修改视图
-- 方式一：OR REPLACE 
CREATE OR REPLACE VIEW vu_emp1 AS SELECT employee_id,last_name,salary,manager_id FROM emps;
-- 方式二：ALTER VIEW 
ALTER VIEW vu_emp1 AS SELECT employee_id,last_name,salary,manager_id,hire_date FROM emps WHERE salary>7000 ;
# 删除视图
SHOW TABLES;
DROP VIEW vu_emp2;
DROP VIEW vu_emp3,vu_emp4;
--  注意：说明:基于视图a、b创建了新的视图c,如果将视图a或者视图b删除,会导致视图c的查询失败。这样的视图c需要手动删除或修改，否则影响使用。
1
2
3
4
5
6
7
8
9
10

视图总结

# 视图总结(视图优缺点)
/*
视图优点
1. 操作简单
将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间
的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简
化了开发人员对数据库的操作。
2. 减少数据冗余
视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语
句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。
3. 数据安全
MySQL将用户对数据的 访问限制 在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用
户不必直接查询或操作数据表。这也可以理解为视图具有 隔离性 。视图相当于在用户和实际的数据表之
间加了一层虚拟表。
同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接
通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。
4. 适应灵活多变的需求 
当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。
5. 能够分解复杂的查询逻辑 
数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

视图缺点：
如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对
相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复
杂,可读性不好,容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包
含复杂的逻辑，这些都会增加维护的成本。
实际项目中，如果视图过多，会导致数据库维护成本的问题。
所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视
图，使系统整体达到最优。

*/
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

练习

# 课后练习
#1. 使用表employees创建视图employee_vu，其中包括姓名（LAST_NAME），员工号（EMPLOYEE_ID），部门号(DEPARTMENT_ID)
CREATE VIEW employee_vu AS SELECT last_name,employee_id,department_id FROM atguigudb.`employees` ; 
#2. 显示视图的结构
DESC employee_vu;
#3. 查询视图中的全部内容
SELECT * FROM employee_vu;
#4. 将视图中的数据限定在部门号是80的范围内
ALTER VIEW employee_vu AS SELECT last_name,employee_id,department_id FROM atguigudb.`employees` WHERE department_id=80; 
-- 练习二
#1. 创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1 AS SELECT last_name,salary,email FROM emps WHERE phone_number LIKE '011%' ;
#2. 要求将视图 emp_v1 修改为查询电话号码以‘011’开头的并且邮箱中包含 e 字符的员工姓名和邮箱、电话号码
CREATE OR REPLACE VIEW emp_v1 AS SELECT last_name,email,phone_number FROM emps WHERE phone_number LIKE '011%' AND email LIKE '%e%' ;
#3. 向 emp_v1 插入一条记录，是否可以？
DESC emps; -- 因为视图与实际数据表相关连,两边任一边更改数据都会相互影响,而实际数据表中有没有默认值设置的字段,所以当在emp_v1中插入一条记录时,在实际数据表中有字段没有赋值所以插入不成功。
INSERT INTO emp_v1 VALUES( 'Tom','tom@123.com','01215655' ); -- Field of view 'dbtest14.emp_v1' underlying table doesn't have a default value 
#4. 修改emp_v1中员工的工资，每人涨薪1000
CREATE OR REPLACE VIEW emp_v1 AS SELECT last_name,email,salary+1000,phone_number FROM emps ; 
UPDATE emp_v1 SET salary = salary + 1000;
#5. 删除emp_v1中姓名为Olsen的员工
DELETE FROM emp_v1 WHERE last_name = 'Olsen';  
#6. 创建视图emp_v2，要求查询部门的最高工资高于 12000 的部门id和其最高工资
CREATE VIEW emp_v2 AS SELECT department_id,salary FROM emps GROUP BY department_id HAVING MAX(salary)>12000; 
#7. 向 emp_v2 中插入一条记录，是否可以？
-- 不可以.
SELECT * FROM emp_v2;
INSERT INTO emp_v2( dep_id,salary_2) VALUES ( 10,30000 ); -- The target table emp_v2 of the INSERT is not insertable-into 实测,不能
#8. 删除刚才的emp_v2 和 emp_v1 
DROP VIEW emp_v2,emp_v1;
SHOW TABLES;
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

存储过程与函数

创建存储过程

存储过程的参数类型

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
存储过程体
END

说明：
1、参数前面的符号的意思
IN：当前参数为输入参数,也就是表示入参；
    存储过程只是读取这个参数的值。如果没有定义参数种类,默认就是IN,表示输入参数。
OUT：当前参数为输出参数，也就是表示出参；
     执行完成之后,调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
INOUT：当前参数既可以为输入参数,也可以为输出参数。
2、形参类型可以是 MySQL数据库中的任意类型。
3、 characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：
 1、没有参数（无参数无返回）
 2、仅仅带 IN 类型（有参数无返回）
 3、仅仅带 OUT 类型（无参数有返回） 
 4、既带 IN 又带 OUT（有参数有返回） 
 5、带 INOUT（有参数有返回）
注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

创建存储过程

# 创建存储过程
-- 举例1:创建存储过程select_all_data(),查看employees表的所有数据
DELIMITER $
CREATE PROCEDURE select_all_data() 
BEGIN 
	SELECT * FROM employees;
END $
DELIMITER ;
# 存储过程的调用
CALL select_all_data ;

-- #举例2:创建存储过程avg_employee_salary()， 返回所有员工的平均工资
DELIMITER $
CREATE PROCEDURE avg_employee_salary () 
BEGIN 
	SELECT AVG(salary) FROM employees ;
END $
DELIMITER;
CALL avg_employee_salary;

-- #举例3:创建存储过程show_max_salary(),用来查看"emps"表的最高薪资值。
DELIMITER $
CREATE PROCEDURE show_max_salary()
BEGIN 
	SELECT MAX(salary) FROM employees ;
END $
DELIMITER ;
CALL show_max_salary;

# 创建存储过程带OUT(无参数有返回)
-- 举例4:创建存储过程show_min_salary(),查看"emps"表的最低薪资值。并将最低薪资通过OUT参数"ms"输出。
DELIMITER //
CREATE PROCEDURE show_min_salary2(OUT ms DOUBLE)
BEGIN
	SELECT MIN(salary) INTO ms
	FROM employees;
END //
DELIMITER ;
#调用
CALL show_min_salary2(@ms); 
#查看变量值
SELECT @ms;

# 创建存储过程带IN(有参数无返回)
-- 举例5:创建存储过程show_someone_salary(),查看“emps"表的某个员工的薪资,并用IN参数empname输入员工姓名。
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20)) BEGIN  SELECT salary FROM employees WHERE last_name=empname; END //
DELIMITER ;
-- 调用方式一
CALL show_someone_salary('Abel');
-- 调用方式二
SET @empname='Abel';
CALL show_someone_salary (@empname);

# 创建存储过程 既带IN又带OUT(有参数有返回) 
-- 举例6:创建存储过程show_someone_salary2(),查看"emps"表的某个员工的薪资,并用IN参数empname输入员工姓名,用OUT参数empsalary输出员工薪资。
DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
BEGIN
	SELECT salary INTO empsalary
	FROM employees
	WHERE last_name = empname;
END //
DELIMITER ;
#调用
SET @empname = 'Abel';
CALL show_someone_salary2(@empname,@empsalary);
SELECT @empsalary;

# 创建存储过程 带INOUT(有参数有返回)
-- #举例7:创建存储过程show_mgr_name(),查询某个员工领导的姓名,并用INOUT参数"empname"输入员工姓名,输出领导的姓名。 
DROP PROCEDURE show_mgr_name2 ;
DELIMITER $ 
CREATE PROCEDURE show_mgr_name( INOUT empname VARCHAR(25)) BEGIN 
  SELECT last_name INTO empname FROM employees WHERE employee_id=( SELECT manager_id FROM employees WHERE last_name=empname ); END $ 
DELIMITER ;
-- 调用
SET @empname = 'Able';
CALL show_mgr_name(@empname);
SELECT @empname; 

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

创建存储函数

-- 举例1:创建存储函数,名称为email_by_name(),参数定义为空,该函数查询Abel的email,并返回,数据类型为字符串型。
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
	DETERMINISTIC
	CONTAINS SQL
	READS SQL DATA
BEGIN
	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;
-- 调用
SELECT email_by_name();
/*
注意：
若在创建存储函数中报错“ you might want to use the less safe log_bin_trust_function_creators variable ”，有两种处理方法：
方式1：加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”
方式2：mysql> SET GLOBAL log_bin_trust_function_creators = 1;
*/

-- 举例2: 创建存储函数,名称为email_by_id(),参数传入emp_id,该函数查询emp_id的email,并返回,数据类型为字符串型。
  -- 创建函数前执行此语句，保证函数的创建会成功
SET GLOBAL log_bin_trust_function_creators = 1;
-- 声明函数
DELIMITER//
CREATE FUNCTION email_by_id(emp_id INT) RETURNS VARCHAR(25)                                   
BEGIN
     RETURN(SELECT email FROM employees WHERE employee_id=emp_id);
END//
DELIMITER;
# 调用
SELECT email_by_id(100);

-- 举例3：创建存储函数count_by_id(),参数传入dept_id,该函数查询dept_id部门的员工人数,并返回,数据类型为整型。
DELIMITER //
CREATE FUNCTION count_by_id( dept_id INT )  RETURNS INT 
BEGIN
     RETURN(SELECT COUNT(*) FROM employees WHERE department_id=dept_id);  
END //
DELIMITER ;
-- 调用
SET @dept_id =60;
SELECT count_by_id(@dept_id);
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

对比存储过程和存储函数

# 对比存储函数和存储过程
/*
          关键字     调用语法        返回值             应用场景
存储过程  PROCEDURE  CALL存储过程()  理解为有0个或多个  一般用于更新
存储函数  FUNCTION   SELECT函数()    只能是一个         一般用于查询结果为一个值并返回时
此外,存储函数可以放在查询语句中使用,存储过程不行。反之,存储过程的功能更加强大,包括能够执行对表的操作(比如创建表，删除表等)和事务操作，这些功能是存储函数不具备的。
*/

1
2
3
4
5
6
7
8

存储过程和函数的查看、修改、删除

# 存储过程和函数的查看
--  SHOW CREATE 查看创建信息
   -- SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
SHOW CREATE PROCEDURE avg_employee_salary; -- 查看存储过程
SHOW CREATE FUNCTION email_by_id; -- 查看函数

-- SHOW STATUS  查看状态信息
   -- SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
SHOW PROCEDURE STATUS LIKE 'show_max_salary';
SHOW FUNCTION STATUS LIKE 'count_by_id'; 

-- 从information_schema.Routines表中查看存储过程和函数的信息
   -- SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
SELECT * FROM information_schema.Routines WHERE routine_name ='email_by_id' ;

#存储过程和函数的修改
-- 注意： 修改存储过程或函数,不影响存储过程或函数功能,只是修改相关特性。使用ALTER语句实现。 
-- ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...] 其中,characteristic指定存储过程或函数的特性,其取值信息与创建存储过程,函数时的取值信息略有不同。
/*
 { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
 | COMMENT 'string' 
 CONTAINS SQL ，表示子程序包含SQL语句，但不包含读或写数据的语句。
 NO SQL ，表示子程序中不包含SQL语句。
 READS SQL DATA ，表示子程序中包含读数据的语句。
 MODIFIES SQL DATA ，表示子程序中包含写数据的语句。
 SQL SECURITY { DEFINER | INVOKER } ，指明谁有权限来执行。
 DEFINER ，表示只有定义者自己才能够执行。
 INVOKER ，表示调用者可以执行。
 COMMENT 'string' ，表示注释信息。
*/
ALTER PROCEDURE show_max_salary SQL SECURITY INVOKER COMMENT '最高工资';

# 储存过程和函数的删除
-- DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名
DROP PROCEDURE show_min_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

关于存储过程使用的争议

# 关于存储过程使用的争议
/*
尽管存储过程有诸多优点，但是对于存储过程的使用，一直都存在着很多争议，比如有些公司对于大型
项目要求使用存储过程，而有些公司在手册中明确禁止使用存储过程，为什么这些公司对存储过程的使用需求差别这么大呢？

存储过程的优点:
1、存储过程可以一次编译多次使用。存储过程只在创建时进行编译,之后的使用都不需要重新编译,这就提升了SQL的执行效率。
2、可以减少开发工作量。将代码 封装 成模块，实际上是编程的核心思想之一,这样可以把复杂的问题拆解成不同的模块,然后模块之间可以 重复使用 ，在减少开发工作量的同时,还能保证代码的结构清晰。
3、存储过程的安全性强。我们在设定存储过程的时候可以 设置对用户的使用权限,这样就和视图一样具有较强的安全性。
4、可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可,这样就减少了网络传输量。
5、良好的封装性。在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句,可能要连接多次数据库才能完成的操作,现在变成了一次存储过程,只需要连接一次即可。

存储过程的缺点:
基于上面这些优点,不少大公司都要求大型项目使用存储过程,比如微软、IBM 等公司。但是国内的阿里并不推荐开发人员使用存储过程,这是为什么呢？
阿里开发规范:【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

1、可移植性差。存储过程不能跨数据库移植,比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程,在换成其他数据库时都需要重新编写。
2、调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。
3、存储过程的版本管理很困难。比如数据表索引发生变化了,可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
4、它不适合高并发的场景。高并发的场景需要减少数据库的压力,有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下,存储过程会变得难以维护,增加数据库的压力,显然就不适用了。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

存储过程练习

-- 练习的准备工作
CREATE DATABASE test15_pro_func;
USE test15_pro_func; 
-- 题目一的准备工作
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(15) NOT NULL,
pwd VARCHAR(25) NOT NULL
);
SELECT * FROM admin;
-- 题目二的准备工作
CREATE TABLE beauty(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(15) NOT NULL,
phone VARCHAR(15) UNIQUE,
birth DATE
);
INSERT INTO beauty(NAME,phone,birth)
VALUES
('朱茵','13201233453','1982-02-12'),
('孙燕姿','13501233653','1980-12-09'),
('田馥甄','13651238755','1983-08-21'),
('邓紫棋','17843283452','1991-11-12'),
('刘若英','18635575464','1989-05-18'),
('杨超越','13761238755','1994-05-11');
SELECT * FROM beauty;
#练习
#1. 创建存储过程insert_user(),实现传入用户名和密码，插入到admin表中
DELIMITER //
CREATE PROCEDURE insert_user(IN `name` VARCHAR(25),IN passw VARCHAR(25)) 
BEGIN
      INSERT INTO admin(user_name,pwd) VALUES (`name`,passw);
END //
DELIMITER; 
CALL insert_user('yuzai','123456');
#2. 创建存储过程get_phone(),实现传入女神编号，返回女神姓名和女神电话
DELIMITER //
CREATE PROCEDURE get_phone(IN nsnumb INT,OUT name1 VARCHAR(25),OUT phone1 VARCHAR(25) ) 
BEGIN
      SELECT `name`,phone INTO name1,phone1 FROM beauty WHERE id=nsnumb ;
END //
DELIMITER ;
CALL get_phone(1,@name,@phone);
SELECT @name,@phone;

#3. 创建存储过程date_diff()，实现传入两个女神生日，返回日期间隔大小 
DELIMITER //
CREATE PROCEDURE date_diff(IN id1 INT ,IN id2 INT,OUT date2 INT)  
BEGIN
     SELECT DATEDIFF(b1.birth,(SELECT b2.birth FROM beauty b2 WHERE id=id2)) '间隔天数' INTO date2 FROM beauty b1 WHERE id=id1 ;
END //
DELIMITER ;
DROP PROCEDURE date_diff;
CALL date_diff(1,2,@date); 
SELECT @date;  
#4. 创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回 
DELIMITER //
CREATE PROCEDURE format_date( IN date1 DATE , OUT date3 VARCHAR(25))
BEGIN
      SELECT DATE_FORMAT(date1,CONCAT('%Y','年','%m','月','%d','日')) INTO date3 FROM DUAL; 
END //
DELIMITER;
CALL format_date('2060-01-03',@data);
SELECT @data;
#5. 创建存储过程beauty_limit()，根据传入的起始索引和条目数，查询女神表的记录 
DELIMITER //
CREATE PROCEDURE beauty_limit(IN start_index INT, IN size INT)
BEGIN 
      SELECT * FROM beauty LIMIT start_index,size;
END //
DELIMITER ;
CALL beauty_limit(1,3);

#6. 传入a和b两个值，最终a和b都翻倍并返回 (创建带inout模式参数的存储过程)
DELIMITER // 
CREATE PROCEDURE fun1(INOUT a INT ,INOUT b INT) 
BEGIN
      SELECT  a*2,b*2 FROM DUAL;
END //
DELIMITER ;
SET @a1=2;
SET @b1=3;
CALL fun1(@a1,@b1);
#7. 删除题目5的存储过程
DROP PROCEDURE beauty_limit ;
#8. 查看题目6中存储过程的信息
SHOW CREATE PROCEDURE fun1; 
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

存储函数练习

#0. 准备工作
USE test15_pro_func;
CREATE TABLE employees AS SELECT * FROM atguigudb.`employees`;
CREATE TABLE departments AS SELECT * FROM atguigudb.`departments`;
--  存储函数练习 
#1. 创建函数get_count(),返回公司的员工个数 (无参有返回)
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE FUNCTION get_count() RETURNS INT
BEGIN
     RETURN(SELECT COUNT(*) FROM employees); 
END//
DELIMITER ;
SELECT get_count();
#2. 创建函数ename_salary(),根据员工姓名，返回它的工资 (有参有返回)
DELIMITER //
CREATE FUNCTION ename_salary(name1 VARCHAR(25)) RETURNS DOUBLE(10,2)
BEGIN
     RETURN(SELECT salary FROM employees WHERE first_name=name1 );
END//
DELIMITER;
SELECT ename_salary('Lex');
#3. 创建函数dept_sal(),根据部门名，返回该部门的平均工资
DELIMITER //
CREATE FUNCTION dept_sal(name1 VARCHAR(25)) RETURNS DOUBLE(10,2)
BEGIN
     RETURN(SELECT AVG(salary) FROM employees e JOIN departments d ON e.department_id=d.department_id WHERE d.department_name=name1 GROUP BY d.department_id );
END//
DELIMITER ;
SELECT dept_sal('IT'); 

#4. 创建函数add_float(),实现传入两个float，返回二者之和	
DELIMITER //
CREATE FUNCTION add_float( f1 FLOAT,f2 FLOAT ) RETURNS DOUBLE(10,2)
BEGIN
     RETURN(SELECT f1+f2 FROM DUAL);
END //
DELIMITER;
SELECT add_float(1.452,1.6);
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

变量、流程控制与游标

变量

系统变量(全局变量、会话系统变量) VS 用户自定义变量

查看系统变量

# 查看系统变量
  -- 查看全局系统变量
SHOW GLOBAL VARIABLES ; -- 624条
  -- 查看会话系统变量
SHOW SESSION VARIABLES ; -- 647条
  -- 默认查询的是会话系统变量 -- 647条
SHOW VARIABLES ;  
  -- 查看部分系统变量
SHOW VARIABLES LIKE 'character_%';  
/*
 在MySQL中有些系统变量只能是全局的,例如max_connections用于限制服务器的最大连接数;有些系统变量作用域既可以是全局又可以是会话，
 例如character_set_client用于设置客户端的字符集;有些系统变量的作用域只能是当前会话,例如pseudo_thread_id用于标记当前会话的MySQL连接ID。
*/  
  -- 查看指定系统变量
     -- SELECT @@global.变量名;
SELECT @@global.max_connections ;
  -- 查看指定的会话变量的值
     -- SELECT @@session.变量名;
SELECT @@session.pseudo_thread_id ;
  -- SELECT @@变量名; 先查询当前会话系统变量,再查询全局系统变量。
SELECT @@character_set_client;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

修改系统变量的值

-- 全局系统变量
  -- 方式一: (针对于当前的数据库实例是有效的,一旦重启MySQL服务,就失效了。)
SET @@global.max_connections=161;
  -- 方式二: (针对于当前的数据库实例是有效的,一旦重启MySQL服务,就失效了。)
SET GLOBAL max_connections=171;
  -- 会话系统变量
  -- 方式一 (针对于当前会话是有效的,一旦结束会话,重新建立起新的会话,就失效了。)
SET @@session.character_set_client='gbk';
  -- 方式二 (针对于当前会话是有效的,一旦结束会话,重新建立起新的会话,就失效了。)
SET SESSION character_set_client='gbk';
1
2
3
4
5
6
7
8
9
10

用户变量(会话用户变量,局部变量)

#用户变量
  /*
  MySQL中的用户变量以一个“@” 开头。根据作用范围不同,又分为会话用户变量和局部变量。
  会话用户变量：作用域和会话变量一样,只对当前连接会话有效。
  局部变量：只在BEGIN和END语句块中有效。局部变量只能在存储过程和函数中使用。
  */
  /* 1.变量的声明和赋值:   
  方式一: "="或":="
   set @用户变量 = 值;
   set @用户变量 := 值;
  方式二: ":="或INTO关键字
   select @用户变量 := 表达式[FROM 等字句];
   select 表达式 into @用户变量 [FROM 等字句];
     2.使用
   select @变量名 
  */
CREATE DATABASE dbtest16;
USE dbtest16;
CREATE TABLE employees AS SELECT * FROM atguigudb.`employees`;
CREATE TABLE departments AS SELECT *FROM atguigudb.`departments` ;
#测试
 -- 方式一
SET @a=1;
SET @b=3;
SET @sum=@a+@b; 
SELECT @sum;
 -- 方式二 
SELECT @numb:=COUNT(*) FROM employees; 
SELECT AVG(salary) INTO @avg_sal FROM employees ;
SELECT @avg_sal;

  -- 使用会话用户变量 
SET @a1=3;
SET @b1=4;
SET @c1=@a1+@b1;
SELECT @c1;

-- 举例3：创建存储过程“different_salary”查询某员工和他领导的薪资差距,并用IN参数emp_id接收员工id,用OUT参数dif_salary输出薪资差距结果。
DELIMITER //
CREATE PROCEDURE different_salary( IN emp_id INT,OUT dif_salary DOUBLE(10,2))
BEGIN
     SELECT salary-(SELECT salary FROM employees WHERE employee_id=( SELECT manager_id FROM employees WHERE employee_id=emp_id)) INTO dif_salary 
     FROM employees WHERE employee_id=emp_id ; 
END//
DELIMITER ;
SET @salary1=0;
CALL different_salary(102,@salary1);
SELECT @salary1;

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

局部变量

#局部变量
 /*
   局部变量必须: ①使用DECLARE声明 
                 ② 声明并使用在BEGIN...END中(使用在存储过程、函数中)
                 ③DECLARE的方式声明的局部变量必须声明在BEGIN中的首行的位置。
 */
 #声明格式：DECLARE 变量名 类型 [default 值]; (如果没有DEFAULT子句,初始值为NULL)
 #赋值:
   -- 方式一 (一般用于赋简单的值)
   -- SET 变量名=值;
   -- SET 变量名:=值;
   -- 方式二 (一般用于赋表中的字段值)
   -- SELECT 字段名或表达式 INTO 变量名 FROM 表;    
 #使用
  -- select 局部变量名;
  
#举例
DELIMITER //
CREATE PROCEDURE test_var()
BEGIN
      #声明局部变量 
      DECLARE a INT DEFAULT 1 ;
      DECLARE b INT ;
      DECLARE emp_name VARCHAR(25);
      #赋值
      SET a=1;
      SET b:=6;
      SELECT last_name INTO emp_name FROM employees WHERE employee_id=101;
      SELECT a,b,emp_name;
END//
DELIMITER ;
CALL test_var;

#举例1: 声明局部变量,并分别赋值为employees表中employee_id为102的last_name和salary
DELIMITER //
CREATE PROCEDURE fun1()
BEGIN
     DECLARE a DOUBLE(10,2) ;
     DECLARE name1 VARCHAR(25) ; 
     SELECT last_name,salary INTO name1,a FROM employees WHERE employee_id=102 ;
     SELECT name1,a;
END //
DELIMITER;
CALL fun1;
#举例2：声明两个变量,求和并打印(分别使用会话用户变量、局部变量的方式实现)
   -- 使用局部变量
DELIMITER //
CREATE PROCEDURE fun2()
BEGIN
      DECLARE a DOUBLE(10,2) DEFAULT 1;
      DECLARE b DOUBLE(10,2) DEFAULT 2;
      DECLARE c DOUBLE(10,2) ;
      SET c=a+b;
      SELECT c;
END //
DELIMITER;
CALL fun2;
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

对比会话用户变量与局部变量

# 对比会话用户变量与局部变量
/* 
               作用域               定义位置            语法
会话用户变量   当前会话             会话的任何地方      加@符号,不用指定类型
局部变量       定义它的BEGIN END中  BEGIN END的第一句话 一般不用加@,需要指定类型
*/   
1
2
3
4
5
6

定义条件和处理程序

定义条件

#定义条件
 -- 定义条件就是给MySQL中的错误码命名,这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。
 -- 定义条件格式: DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）
-- 举例1：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。
  -- 方式一: 使用MySQL_error_code 
DECLARE Field_Not_Be_NULL CONDITION FOR 1048 ;
  -- 方式二: 使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000'; 
-- 举例2：定义"ERROR 1148(42000)"错误,名称为command_not_allowed。 
  -- 方式一: 使用MySQL_error_code
DECLARE command_not_allowed CONDITION FOR 1148;
  -- 方式二: 使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000'; 
1
2
3
4
5
6
7
8
9
10
11
12
13

定义处理程序

# 定义处理程序
/*
  格式: DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
  处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。
           CONTINUE：表示遇到错误不处理,继续执行。   
           EXIT：表示遇到错误马上退出。
           UNDO：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
  错误类型(即条件)可以有如下取值：
           SQLSTATE '字符串错误码'：表示长度为5的sqlstate_value类型的错误代码;
           MySQL_error_code：匹配数值类型错误代码;
           错误名称：表示DECLARE...CONDITION定义的错误条件名称。
           SQLWARNING：匹配所有以01开头的SQLSTATE错误代码;
           NOT FOUND：匹配所有以02开头的SQLSTATE错误代码;
           SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码;
  处理语句: 如果出现上述条件之一,则采用对应的处理方式,并执行指定的处理语句。
            语句可以是像“ SET 变量 = 值 ”这样的简单语句,也可以是使用BEGIN...END编写的复合语句。
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

流程控制

分支结构之if

 -- 分支结构之if
 /*
   IF 表达式1 THEN 操作1
      [ELSEIF 表达式2 THEN 操作2]……
      [ELSE 操作N]
   END IF
 */
 
-- 举例1:  
DELIMITER //
CREATE PROCEDURE test_if()
BEGIN
      /* 情况一
      #声明局部变量
      declare stu_name varchar(15);
      if stu_name is null 
                then select 'stu_name is null';
      end if;
      */
      /* 情况二 (二选一)
      declare email varchar(25) default'yuzai';
      if email is null 
               then select 'email is null';
      else
               select 'email is not null';
      end if;
      */
      DECLARE age INT DEFAULT 19;
      IF age>40 THEN SELECT '中老年' ;
                ELSEIF age>18 THEN SELECT '青壮年' ;
                 ELSEIF age>8 THEN SELECT '少年' ;
                  ELSE SELECT '幼儿' ;
      END IF ;  
      
END//
DELIMITER ;
CALL test_if;

-- 举例2：声明存储过程“update_salary_by_eid1”,定义IN参数emp_id,输入员工编号。判断该员工薪资如果低于8000元并且入职时间超过5年,就涨薪500元;否则就不变。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
      IF (SELECT salary FROM employees WHERE employee_id=emp_id )<8000 AND (SELECT DATEDIFF(NOW(),hire_date)/365 FROM employees WHERE employee_id=emp_id) 
         THEN UPDATE employees SET salary=salary+500 WHERE employee_id=emp_id;
         END IF;
END//
DELIMITER;
CALL update_salary_by_eid1(104);
-- SELECT employee_id,salary FROM employees WHERE salary<8000  AND DATEDIFF(NOW(),hire_date)/365>5 ;

-- 举例3：声明存储过程“update_salary_by_eid2”,定义IN参数emp_id.输入员工编号。判断该员工薪资如果低于9000元并且入职时间超过5年,就涨薪500元;否则就涨薪100元。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid2(IN emp_id INT ) 
BEGIN
      DECLARE a DOUBLE(12,2);
      DECLARE b DOUBLE(10,2); 
      SELECT salary INTO a FROM employees WHERE employee_id=emp_id;
      SELECT DATEDIFF(NOW(),hire_date) INTO b FROM employees WHERE employee_id=emp_id ;
      IF a<9000 AND b>5 THEN UPDATE employees SET salary=salary+500 WHERE employee_id=emp_id;
          ELSE UPDATE employees SET salary=salary+100 WHERE employee_id=emp_id; 
      END IF ;
END//
DELIMITER;
CALL update_salary_by_eid2(100);
-- SELECT employee_id,salary FROM employees WHERE salary>9000  AND DATEDIFF(NOW(),hire_date)/365>5 ;

-- 举例4：声明存储过程“update_salary_by_eid3”,定义IN参数emp_id,输入员工编号。判断该员工薪资如果低于9000元,就更新薪资为9000元;
       -- 薪资如果大于等于9000元且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01；其他的涨薪100元。
       
DELIMITER //
CREATE PROCEDURE update_salary_by_eid3(IN emp_id INT)
BEGIN
      DECLARE a DOUBLE(10,2);
      DECLARE c DECIMAL; 
      SELECT salary INTO a FROM employees WHERE employee_id=emp_id;
      SELECT commission_pct INTO c FROM employees WHERE employee_id=emp_id;
      IF a<9000 THEN UPDATE employees SET salary=9000 WHERE employee_id=emp_id;
         ELSEIF a<10000 AND c IS NULL THEN UPDATE employees SET commission_pct=0.01 WHERE employee_id=emp_id ; 
               ELSE UPDATE employees SET salary=salary+100 WHERE employee_id=emp_id;
      END IF;
END//
DELIMITER ;
CALL update_salary_by_eid3();
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

分支结构之case

# 分支结构之CASE

-- 举例
DELIMITER //
CREATE PROCEDURE test_case()
BEGIN
      /*
        演示一: case...when...then..
      declare a int default 2;
      case a when 1 then select 'a=1';
             when 2 then select 'a=2';
             when 3 then select 'a=3';
      end case;
      */
      # 演示二: case when...then...
      DECLARE b INT DEFAULT 10;
      CASE WHEN b>=100 THEN SELECT '三位数';
           WHEN b>=10  THEN SELECT '两位数';
           ELSE SELECT '个位数';
      END CASE;
END//
DELIMITER ;
-- drop procedure test_case;
CALL test_case;

-- 举例2：声明存储过程“update_salary_by_eid4”,定义IN参数emp_id,输入员工编号。判断该员工薪资如果低于9000元,就更新薪资为9000元;
       -- 薪资大于等于9000元且低于10000的,但是奖金比例为NULL的,就更新奖金比例为0.01;其他的涨薪100元。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
     DECLARE a DOUBLE(10,2) ;
     DECLARE b DECIMAL;
     SELECT salary INTO a FROM employees WHERE employee_id=emp_id;
     SELECT commission_pct INTO b FROM employees WHERE employee_id=emp_id;
     CASE a WHEN a<9000 THEN UPDATE employees SET salary=9000 WHERE employee_Id=emp_id;
            WHEN a<10000 AND b IS NULL THEN UPDATE employees SET commission_pct=0.01 WHERE employee_Id=emp_id;
            ELSE UPDATE employees SET salary=salary+100 WHERE employee_id=emp_id;
     END CASE;
END//
DELIMITER;
CALL update_salary_by_eid4();

-- 举例4：声明存储过程update_salary_by_eid5,定义IN参数emp_id,输入员工编号。判断该员工的入职年限,如果是0年,薪资涨50;
       -- 如果是1年,薪资涨100;如果是2年,薪资涨200;如果是3年,薪资涨300;如果是4年,薪资涨400;其他的涨薪500。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT) 
BEGIN
      DECLARE a DOUBLE(10,2);
      DECLARE b INT ;
      SELECT salary INTO a FROM employees WHERE employee_id=emp_id;
      SELECT ROUND(DATEDIFF(NOW(),hire_date)/365,0) INTO b FROM employees WHERE employee_id=emp_id ;
      CASE b WHEN 0 THEN UPDATE employees SET salary=salary+50 WHERE employee_id=emp_id;
             WHEN 1 THEN UPDATE employees SET salary=salary+100 WHERE employee_id=emp_id;
             WHEN 2 THEN UPDATE employees SET salary=salary+200 WHERE employee_id=emp_id; 
             WHEN 3 THEN UPDATE employees SET salary=salary+300 WHERE employee_id=emp_id;
             WHEN 4 THEN UPDATE employees SET salary=salary+400 WHERE employee_id=emp_id;
             ELSE UPDATE employees SET salary=salary+500 WHERE employee_id=emp_id; 
      END CASE ;         
END //
DELIMITER;
CALL update_salary_by_eid5(100);
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

循环结构之LOOP

# 循环结构之LOOP
/*
   [loop_label:] LOOP
            循环执行的语句
   END LOOP [loop_label]
*/
-- 举例一:
DELIMITER //
CREATE PROCEDURE test_loop()
BEGIN
      DECLARE a INT DEFAULT 1;
      loop_name:LOOP 
                   SET a=a+1;
                   IF a>=10 THEN LEAVE loop_name; /* leave退出循环 */ 
                   END IF;
                END LOOP loop_name;
      SELECT a;
END//
DELIMITER;
CALL test_loop; 

-- 举例2：当市场环境变好时,公司为了奖励大家,决定给大家涨工资。声明存储过程“update_salary_loop()”,声明OUT参数num,
       -- 输出循环次数。存储过程中实现循环给大家涨薪,薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
  DECLARE avg_salary DOUBLE;
  DECLARE loop_count INT DEFAULT 0;
  SELECT AVG(salary) INTO avg_salary FROM employees;
l   abel_loop:LOOP
             IF avg_salary >= 12000 THEN LEAVE label_loop;
             END IF;
             UPDATE employees SET salary = salary * 1.1;
             SET loop_count = loop_count + 1;
             SELECT AVG(salary) INTO avg_salary FROM employees;
    END LOOP label_loop;
 SET num = loop_count;
END //
DELIMITER ;
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

循环结构之WHILE

/*
WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时,先对指定的表达式进行判断,如果为真,就执行循环内的语句,否则退出循环。WHILE语句的基本格式如下:

[while_label:] WHILE 循环条件 DO
 循环体
END WHILE [while_label];

*/
-- 举例一
DELIMITER //
CREATE PROCEDURE test_while()
BEGIN  /* 初始化条件 */
      DECLARE num INT DEFAULT 1;
      /* 循环条件 */ 
      WHILE num<=10 DO
            /* 循环体(略) */
            
            /* 迭代条件 */
            SET num = num +1 ;
      END WHILE;
      
      SELECT num;      
END// 
DELIMITER;
CALL test_while;

-- 举例2 市场环境不好时,公司为了渡过难关,决定暂时降低大家的薪资。声明存储过程“update_salary_while()”，
      -- 声明OUT参数num,输出循环次数。存储过程中实现循环给大家降薪,薪资降为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE ;
DECLARE while_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_sal FROM employees;
WHILE avg_sal > 5000 DO
UPDATE employees SET salary = salary * 0.9;
SET while_count = while_count + 1;
SELECT AVG(salary) INTO avg_sal FROM employees;
END WHILE;
SET num = while_count;
END //
DELIMITER ;

@a INT ;
CALL update_salary_while(@a);
SELECT @a;
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

循环结构之REPEAT

# 循环结构之REPEAT 
/*
  [repeat_label:] REPEAT
            循环体的语句
  UNTIL 结束循环的条件表达式
  END REPEAT [repeat_label]

*/

-- 举例一
DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN
      #声明变量
      DECLARE num INT DEFAULT 1;
      
      REPEAT 
              SET num=num+1; 
              UNTIL num>=10
      END REPEAT ;    
      
      SELECT num;    
END //
DELIMITER ;
CALL test_repeat;

-- 举例2 当市场环境变好时,公司为了奖励大家,决定给大家涨工资。声明存储过程“update_salary_repeat()”,声明OUT参数num,输出循环次数。
      -- 存储过程中实现循环给大家涨薪,薪资涨为原来的1.15倍。直到全公司的平均薪资达到13000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_repeat(OUT num INT) 
BEGIN
      DECLARE avg_sal6 DOUBLE(10,2);
      DECLARE a INT DEFAULT 0;
      
      SELECT AVG(salary) INTO avg_sal6 FROM employees;
      
      REPEAT 
            UPDATE employees SET salary=salary*1.15 ;
            SET a=a+1;
            SELECT AVG(salary) INTO avg_sal6 FROM employees;
      UNTIL avg_sal6>=13000
      END REPEAT ;
      SET num=a;
END//
DELIMITER ;

SET @b=0 ;
CALL update_salary_repeat(@b);
SELECT @b;

SELECT AVG(salary) FROM 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

对比三种循环结构

#  对比三种循环结构:
/*
1. 这三种循环都可以省略名称,但如果循环中添加了循环控制语句(LEAVE或ITERATE)则必须添加名称。
2. LOOP: 一般用于实现简单的“死"循环 
   WHILE:先判断后执行
   REPEAT:先执行后判断,无条件至少执行一-次 
*/

/*
  凡是循环条件,一定具备4个要数
  1.初始化条件
  2.循环条件
  3.循环体
  4.迭代条件
*/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

跳转语句之LEAVE

# 跳转语句之LEAVE语句
/*
  LEAVE语句：可以用在循环语句内,或者以BEGIN和END包裹起来的程序体内,表示跳出循环或者跳出程序体的操作。
             如果你有面向过程的编程语言的使用经验,你可以把LEAVE理解为break。
             
  基本格式如下：
  LEAVE 标记名
*/

/*
举例1：创建存储过程 “leave_begin()”，声明INT类型的IN参数num。给BEGIN...END加标记名，并在BEGIN...END中使用IF语句判断num参数的值。
       如果num<=0，则使用LEAVE语句退出BEGIN...END；
       如果num=1，则查询“employees”表的平均薪资；
       如果num=2，则查询“employees”表的最低薪资；
       如果num>2，则查询“employees”表的最高薪资。
       IF语句结束后查询“employees”表的总人数。
*/
DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
begin_name: BEGIN
     IF num<=0 THEN LEAVE begin_name;
        ELSEIF num=1 THEN SELECT AVG(salary) FROM employees;
         ELSEIF num=2 THEN SELECT MIN(salary) FROM employees;
           ELSEIF num>2 THEN SELECT MAX(salary) FROM employees;
     END IF;
      SELECT COUNT(*) FROM employees;       
     
END//
DELIMITER;

DROP PROCEDURE leave_begin ;
CALL leave_begin(0); 

/*
  举例2：
  当市场环境不好时,公司为了渡过难关,决定暂时降低大家的薪资。声明存储过程“leave_while()”,声明OUT参数num,输出循环次数,
  存储过程中使用WHILE循环给大家降低薪资为原来薪资的90%,直到全公司的平均薪资小于等于10000,并统计循环次数。
*/

DELIMITER //
CREATE PROCEDURE leave_while(OUT num INT)
BEGIN
      DECLARE avg_sal DOUBLE(10,2);
      DECLARE a INT DEFAULT 0;
      SELECT AVG(salary) INTO avg_sal FROM employees ;
      
while_name:WHILE avg_sal>=10000 DO
                 UPDATE employees SET salary=salary*0.9 ;
                 SELECT AVG(salary) INTO avg_sal FROM employees ;
                 SET a=a+1;
                 SET num = a; 
                 IF avg_sal<=10000 THEN LEAVE while_name;
                 END IF;
           END WHILE ;      
END//
DELIMITER;

CALL leave_while(@num);
SELECT @num;
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

跳转语句之ITERATE

# 跳转语句之ITERATE语句
/*
  ITERATE语句：只能用在循环语句(LOOP、REPEAT和WHILE语句)内,表示重新开始循环,将执行顺序转到语句段开头处。
               如果你有面向过程的编程语言的使用经验,你可以把 ITERATE 理解为 continue,意思为“再次循环”。
  
  语句基本格式如下：
  ITERATE label 
*/
/*
举例  定义局部变量num,初始值为0。循环结构中执行num+1操作。
      如果num<10,则继续执行循环；
      如果num>15,则退出循环结构；
*/

DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
       DECLARE num INT DEFAULT 0;
       
loop_name:LOOP 
              SET num=num+1;
              IF num<10 THEN ITERATE loop_name;
                 ELSEIF num>15 THEN LEAVE loop_name;
              END  IF;     
              SELECT 'love 鱼仔'; 
          END LOOP ;
END//
DELIMITER;
CALL test_iterate;
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

游标

# 游标
/*
  游标使用的步骤:
  ①声明游标
  ②打开游标
  ③使用游标(从游标中获取数据)
  ④关闭游标
*/
/*
   举例：
   创建存储过程“get_count_by_limit_total_salary()”,声明IN参数limit_total_salary,DOUBLE类型: 声明OUT参数total_count,INT类型。
   函数的功能可以实现累加薪资最高的几个员工的薪资值,直到薪资总和达到limit_total_salary参数的值,返回累加的人数给total_count。
*/
DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary( IN limit_total_salary DOUBLE,OUT total_count INT )
BEGIN
       DECLARE sum_sal DOUBLE DEFAULT 0.0;
       DECLARE emp_sal DOUBLE ; 
       DECLARE emp_count INT DEFAULT 0;
       
       #声明游标
       DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC ;
       #打开游标
       OPEN emp_cursor ;
       
       REPEAT 
       
              #使用游标
              FETCH emp_cursor INTO emp_sal;
              
              SET sum_sal=sum_sal+emp_sal; 
              SET emp_count=emp_count+1;
              
              UNTIL sum_sal>=limit_total_salary
        END REPEAT;
        
        SET total_count=emp_count;
       #关闭游标
       CLOSE emp_cursor;  
END //
DELIMITER;
CALL get_count_by_limit_total_salary(200000,@total_count);
SELECT @total_count;

# 游标小结
/*
游标是MySQL的一个重要的功能,为逐条读取结果集中的数据,提供了完美的解决方案。跟在应用层面实现相同的功能相比,游标可以在存储程序中使用,效率高,程序也更加简洁。
     但同时也会带来一些性能问题,比如在使用游标的过程中,会对数据行进行加锁,这样在业务并发量大的时候,不仅会影响业务之间的效率,还会消耗系统资源,造成内存不足,
     这是因为游标是在内存中进行的处理。
建议: 养成用完之后就关闭的习惯,这样才能提高系统的整体效率。
*/
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

MySQL8.0的新特性-全局变量的持久化

# 补充: MySQL8.0的新特性-全局变量的持久化
/*
 在MySQL数据库中,全局变量可以通过SET GLOBAL语句来设置。例如,设置服务器语句超时的限制,可以通过设置系统变量max_execution_time来实现: SET GLOBAL MAX_EXECUTION_TIME=2000;
 使用SET GLOBAL语句设置的变量值只会临时生效。数据库重启后,服务器又会从MySQL配置文件中读取变量的默认值。
 MySQL8.0版本新增了SET PERSIST命令。例如,设置服务器的最大连接数为1000: SET PERSIST global max_connections = 1000;
 MySQL会将该命令的配置保存到数据目录下的mysqld-auto.cnf文件中,下次启动时会读取该文件,用其中的配置来覆盖默认的配置文件。
*/
1
2
3
4
5
6
7

练习

待更新

触发器

概述: 触发器是由事件来触发某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。
所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序,当数据库执行这些语句时候,就相当于事件发生了,就会自动激发触发器执行相应的操作。当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。
1
2

触发器的创建

创建触发器的语法结构是：
  创建触发器的语法结构:  
 CREATE TRIGGER 触发器名称
 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
 FOR EACH ROW
 触发器执行的语句块;
 
 说明：
  表名: 表示触发器监控的对象。
  BEFORE|AFTER: 表示触发的时间。BEFORE表示在事件之前触发;AFTER 表示在事件之后触发。 
  INSERT|UPDATE|DELETE: 表示触发的事件。
  INSERT 表示插入记录时触发;
  UPDATE 表示更新记录时触发;
  DELETE 表示删除记录时触发;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

代码举例

0准备工作:
CREATE DATABASE dbtest17;
USE dbtest17;
CREATE TABLE test_trigger(id INT PRIMARY KEY AUTO_INCREMENT,t_not VARCHAR(30));
CREATE TABLE test_trigger_log( id INT PRIMARY KEY AUTO_INCREMENT,t_log VARCHAR(30));

举例1 :创建触发器: 创建名称为before_insert_test_tri的触发器,向test_trigger数据表插入数据之前,向test_trigger_log数据表中插入before_insert的日志信息。
DELIMITER //
CREATE TRIGGER before_insert_test_tri 
BEFORE INSERT ON test_trigger 
FOR EACH ROW 
BEGIN
       INSERT INTO test_trigger_log(t_log) VALUES('fucking die');
END //
DELIMITER;
INSERT INTO test_trigger(t_not) VALUES ('yuzai');
-- select * from test_trigger;
-- SELECT * FROM test_trigger_log;

举例二 : 创建名称为after_insert的触发器,向test_trigger数据表插入数据之后,向test_trigger_log数据表中插入after_insert的日志信息。
DELIMITER//
CREATE TRIGGER after_insert 
AFTER INSERT ON test_trigger 
FOR EACH ROW 
   BEGIN
         INSERT INTO test_trigger_log(t_log) VALUES ('after_insert');
   END //
DELIMITER;
INSERT INTO test_trigger(t_not) VALUES('LOVE');
-- select * from test_trigger;
-- SELECT * FROM test_trigger_log;

举例三 : 定义触发器“salary_check_trigger”,基于员工表“employees”的INSERT事件,在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资,如果大于领导薪资,则报sqlstate_value为'HY000'的错误,从而使得添加失败。
/*准备工作*/
CREATE TABLE employees AS SELECT * FROM atguigudb.`employees`;
CREATE TABLE departments AS SELECT * FROM atguigudb.`departments`;
DESC employees;

DELIMITER//
CREATE TRIGGER salary_check_trigger 
BEFORE INSERT ON employees 
FOR EACH ROW 
BEGIN
      DECLARE mgr_sal DOUBLE ;
      SELECT salary INTO mgr_sal FROM employees WHERE employee_id = new.manager_id;
      IF NEW.salary > mgr_sal THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT= '薪资高于领导薪资错误';
      END IF ;    
END //
DELIMITER;
/*测试*/
#添加成功: 依然触发了触发器salary check trigger的执行
INSERT INTO employees( employee_id,last_name,email,hire_date,job_id,salary,manager_id) VALUES( 300,'yuzai','yuzai@123.com',NOW(),'AD_VP',8000,103 ); 
#添加失败：触发了触发器的错误提示语句
INSERT INTO employees( employee_id,last_name,email,hire_date,job_id,salary,manager_id) VALUES( 300,'yuzai','yuzai@123.com',NOW(),'AD_VP',80000,103 ); 
SELECT * FROM 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

查看、删除触发器

# 查看触发器
-- 方式1：查看当前数据库的所有触发器的定义
SHOW TRIGGERS;
-- 方式2：查看当前数据库中某个触发器的定义 
   -- SHOW CREATE TRIGGER 触发器名
SHOW CREATE TRIGGER salary_check_trigger ;
-- 方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
   -- SELECT * FROM information_schema.TRIGGERS;

# 删除触发器
-- drop TRIGGERS 触发器名
1
2
3
4
5
6
7
8
9
10
11

练习题

#练习
#0. 准备工作
CREATE DATABASE test17_trigger ; 
CREATE TABLE emps AS SELECT employee_id,last_name,salary FROM atguigudb.`employees`;
SELECT * FROM emps;
#1. 复制一张emps表的空表emps_back，只有表结构，不包含任何数据
CREATE TABLE emps_back AS SELECT * FROM emps WHERE 1=2;
#2. 查询emps_back表中的数据
SELECT * FROM emps_back;
#3. 创建触发器emps_insert_trigger，每当向emps表中添加一条记录时，同步将这条记录添加到emps_back表中
DELIMITER//
CREATE TRIGGER emps_insert_trigger
AFTER INSERT ON emps
FOR EACH ROW
BEGIN 
      INSERT INTO emps_back(employee_id,last_name,salary) VALUES(new.employee_id,new.last_name,new.salary);  
END//
DELIMITER;
/* 获取新添加的记录,用NEW表示 */
SHOW TRIGGERS;
#4. 验证触发器是否起作用
INSERT INTO emps(employee_id,last_name,salary) VALUES (300,'yuzai',87000);
SELECT * FROM emps;
SELECT * FROM emps_back;

#练习2
#0. 准备工作：使用练习1中的emps表
#1. 复制一张emps表的空表emps_back1，只有表结构，不包含任何数据
CREATE TABLE emps_back1 AS SELECT * FROM emps WHERE 1=2;
#2. 查询emps_back1表中的数据
SELECT * FROM emps_back1;
#3. 创建触发器emps_del_trigger，每当向emps表中删除一条记录时，同步将删除的这条记录添加到emps_back1表中
DELIMITER//
CREATE TRIGGER  emps_del_trigger 
BEFORE DELETE ON emps
FOR EACH ROW
BEGIN
       #将emps表中删除的记录,添加到emps_back1表中。
       INSERT INTO emps_back1(employee_id,last_name,salary) VALUES (old.employee_id,old.last_name,old.salary);  
END//
DELIMITER;
/*  注意此时使用的是OLD！  */
#4. 验证触发器是否起作用
DELETE FROM emps WHERE employee_id =101 ;
SELECT * FROM emps;
SELECT * FROM emps_back1; 
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

MySQL8.0新特性

待更! 2022-03-12

窗口函数

公用表达式