一、连接 MySQL

  1. 使用 MySQL 自带的连接客户端工具（只能连接 root 用户）

  2. 使用 cmd 命令行连接 MySQL

    css
    1
    2
    模板：mysql -h 主机ip -P 端口号 -u 用户名 -p密码
    例：mysql -h localhost -P 3306 -u root -proot

  3. 连接本机 MySQL（主机和 ip 可以省略）：

    apache
    1
    mysql 【-h localhost -P 3306】 -u root -proot

二、MySQL 常见命令

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
32
33
create database `数据库名`;# 简单创建数据库
show databases;# 查看数据库
use `数据库名`;# 使用数据库
show tables;# 查看指定数据库下面的所有表
show tables from `数据库名`; # 查看指定数据库下面的所有表
select database(); # 查看当前处于哪个数据库中

# 简单创建表
create table stu_info(
	id int,
    name varchar(20)
);

# 查看表结构
desc `表名`;

# 查询数据
SELECT *FROM `表名`;

# 添加数据
格式：INSERT INTO `表名`(`字段名`) VALUES(`字段值`);
例：INSERT INTO stu_info(id,name) VALUES(1,'张三');

# 修改操作
格式：UPDATE  `表名` SET `字段名` = '字段值';
例：UPDATE  stu_info SET name = '李四';

# 删除数据
格式：DELETE FROM `表名` WHERE 条件;
例：DELETE FROM stu_info WHERE id = 1;

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

三、MySQL 语法规范

  • 不区分大小写，但建议关键字大写，表名，列名小写
  • 每条命令最好用分号结尾
  • 每条命令根据需要，可以进行缩写，或换行
  • 注释：
    • 单行注释：# 注释文字
    • 单行注释：-- 注释文字
    • 多行注释：/* 注释文字 */

四、DQL（数据查询语言 DQL）

1、基础查询

知识点

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
# 进阶一：基础查询
/*
		语法：
		SELECT 查询列表 FROM 表名;
		
		类似于：
		System.out.println("打印东西");
		
		特点：
		1、查询列表可以是：表中的字段，常量值，表达式，函数
		2、查询的结果是一个虚拟的表格
		
		起别名
		1、便于理解
		2、如果多表查询的时候，出现字段名重复，可以使用起别名来区分
		
		+ 号的作用
		Java中：
			运算符，两个操作数都要为数值型
			连接符，只要有一个操作为字符串
		mysql中：
			运算符：只有一个功能，运算符
			连接符：使用CONCAT函数将字符串拼接
			
		IFNULL 函数
*/

查询例子

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、查询表中的单个字段
SELECT last_name FROM employees;

# 查询表中的多个字段
SELECT last_name,email FROM employees;

# 查询表中的所有字段
SELECT *FROM employees;

# 查询常量值
SELECT 100;
SELECT '1000';

# 表达式查询
SELECT 100*199;

# 查询函数
SELECT VERSION();# 查询数据库版本

# 方式一：使用 AS
SELECT 100*999 AS 结果;

# 方式二：使用空格
SELECT 100*999 结果;

# 字段去重：在字段前面加上 DISTINCT 编号，可以使查询出来的结果中，该字段不会出现重复的值
# 查询员工表中的涉及的所有的部门编号
SELECT DISTINCT department_id FROM employees;

# 查询员工和姓连接为一个姓名，使用CONCAT函数将字符串进行拼接
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

2、条件查询

语法

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 进阶2：条件查询
/*
		语法：
			SELECT 查询列表 FROM 表名 WHERE 筛选条件;
		分类：
			1、按条件表达式筛选
				条件运算符： > < = !=(<>) >= <=
			2、逻辑表达式筛选
				逻辑运算符：&& || ! （and or not）
			3、模糊查询
				LIKE
				BETWEEN AND
				IN
				IS NULL
			4、is null 和 <=>
				IS NULL：仅仅可以判断 NULL 值，可读性比较高，建议使用
				<=>：既可以判断 NULL，也可以判断普通的数值，可读性比较低
*/

案例

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
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
# 进阶2：条件查询
/*
		语法：
			SELECT 查询列表 FROM 表名 WHERE 筛选条件;
		分类：
			1、按条件表达式筛选
				条件运算符： > < = !=(<>) >= <=
			2、逻辑表达式筛选
				逻辑运算符：&& || ! （and or not）
			3、模糊查询
				LIKE
				BETWEEN AND
				IN
				IS NULL
			4、is null 和 <=>
				IS NULL：仅仅可以判断 NULL 值，可读性比较高，建议使用
				<=>：既可以判断 NULL，也可以判断普通的数值，可读性比较低
*/

# 1、按条件表达式查询

# 案例：查询工资>12000的员工信息
SELECT *FROM employees WHERE salary > 12000

# 查询部门编号不等于90号的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id<>90;

# 2、逻辑表达式筛选

# 查询工资在10000到20000的员工名，工资及奖金
SELECT last_name , salary FROM employees WHERE salary >= 10000 AND salary <= 20000;

# 查询工资低于10000或大于等于20000的员工名，工资及奖金
SELECT last_name , salary FROM employees WHERE salary <= 10000 OR salary >= 20000;

# 3、模糊查询

# 查询员工张名字字符包含a的员工信息
SELECT *FROM employees WHERE last_name LIKE '%a%'

# 查询员工名中第三个字符为n，第五个字符为l的员工名和工资
SELECT last_name,salary FROM employees WHERE last_name LIKE '%__n_l%';

# 查询员工中第二个字符为_的员工名，使用 \ 进行转义
SELECT last_name FROM employees WHERE last_name LIKE '%_\_%';

# 查询员工工资在10000-20000之间的员工信息
SELECT last_name ,salary FROM employees WHERE salary BETWEEN 10000 AND 20000;
# 相当于
SELECT last_name ,salary FROM employees WHERE salary >= 10000 AND salary <= 20000;

# in：查询员工编号在属于某个集合的员工信息
SELECT last_name , employee_id FROM employees WHERE employee_id IN (100,101,102,103,104);

# is null：查询 没有/有 奖金的员工名
SELECT last_name , commission_pct FROM employees WHERE commission_pct IS NULL;
SELECT last_name , commission_pct FROM employees WHERE commission_pct IS NOT NULL;

# 安全等于 <=>

# 查询没有奖金的员工名和奖金
SELECT last_name , commission_pct FROM employees WHERE commission_pct <=> NULL;

# 查询工资为12000的员工信息
SELECT last_name , salary FROM employees WHERE salary <=> 12000;

3、排序查询

语法

sql
1
2
3
4
5
6
7
8
/*
		进阶三：排序查询
			语法：
				SELECT 查询列表 FROM 表 【WHERE 筛选条件】 ORDER BY 排序列表 【ASC / DESC】
				ASC：升序。DESC：降序。如果不写，默认是升序
				ORDER BY 子句中可以支持单个字段，多个字段，表达式，函数，别名来进行排序
				ORDER BY 子句一般是放在查询语句的最后面，LIMIT子句放在 ORDER BY后面
*/

案例

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/*
		进阶三：排序查询
			语法：
				SELECT 查询列表 FROM 表 【WHERE 筛选条件】 ORDER BY 排序列表 【ASC / DESC】
				ASC：升序。DESC：降序。如果不写，默认是升序
				ORDER BY 子句中可以支持单个字段，多个字段，表达式，函数，别名来进行排序
				ORDER BY 子句一般是放在查询语句的最后面，LIMIT子句放在 ORDER BY后面
*/

# 查询员工信息，要求工资从高到低进行排序
SELECT *FROM employees ORDER BY salary DESC;

# 查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】
SELECT *FROM employees WHERE department_id >= 90 ORDER BY hiredate ASC;

# 按年薪的高低显示员工的信息和年薪【按表达式（别名）排序】
SELECT *,salary*12*(1 + IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC;

# 按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name) 名字长度 FROM employees ORDER BY 名字长度 ASC;

# 查询员工信息，要求先按员工工资排序升序，再按员工编号排序降序【多个字段排序】
SELECT *FROM employees ORDER BY salary ASC , employee_id DESC;

4、单行函数

4.1、常见函数

语法

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/*
		进阶四：常见函数
		功能：类似与Java中的方法，将一组逻辑语句将其封装再方法体中，对外暴露方法名
		好处：	
			1、隐藏了细节
			2、提高代码的重用性
		调用：SELECT 函数名() 【FROM 表（当用到表用的字段拼接该语句）】
		特点：
			1、叫什么：函数名
			2、干什么：函数功能
		分类；
			1、单行函数
				如：CONCAT，LENGTH，IFNULL等
			2、分组函数
				功能：做统计使用，又称为统计函数，聚合函数，组函数
*/

案例

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
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
/*
		进阶四：常见函数
		功能：类似与Java中的方法，将一组逻辑语句将其封装再方法体中，对外暴露方法名
		好处：	
			1、隐藏了细节
			2、提高代码的重用性
		调用：SELECT 函数名() 【FROM 表（当用到表用的字段拼接该语句）】
		特点：
			1、叫什么：函数名
			2、干什么：函数功能
		分类；
			1、单行函数
				如：CONCAT，LENGTH，IFNULL等
			2、分组函数
				功能：做统计使用，又称为统计函数，聚合函数，组函数
*/

# =====================================字符函数========================================

# LENGTH：字符串长度【一个英文占一个字节，常见中文占三个字节，复杂的中文字节数可能是三个以上】
SELECT LENGTH('aaa');
SELECT LENGTH('张三丰');

# 查看字符集
SHOW VARIABLES LIKE '%char%';

# CONCAT：字符串拼接
SELECT CONCAT(last_name,'-',first_name) FROM employees;

# UPPER(将小写转换为大写)、LOWER(将大写转换为小写)
SELECT UPPER('aaa');
SELECT LOWER('AAAb');

# 将姓变大写，将名变小写
SELECT CONCAT(UPPER(last_name),'-',LOWER(first_name)) 姓名 FROM employees;

# SUBSTR、SUBSTRING：字符串截取
SELECT SUBSTR('李莫愁',3);
SELECT SUBSTR('李莫愁',1,2) ;

# 姓名中首字符大写，其他字符小写然后用-拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'-',LOWER(SUBSTR(last_name,2))) FROM employees;

# INSTR：获取子串第一次出现的索引，如果找不到返回0
SELECT INSERT('杨不悔aaa',);

# TRIM：去除字符串前后的空格
SELECT LENGTH(TRIM('  aaaa    '));

# TRIM：去除字符串前后指定的字符
SELECT TRIM('a' FROM 'abaaaaaaaaba');

# LPAD：用指定的字符实现左填充指定长度
SELECT LPAD('aaa',10,'*');

# RPAD：用指定的字符实现右填充指定长度
SELECT RPAD('aaa',10,'*');

# REPLACE：将指定字符替换为新的字符
SELECT REPLACE('aaaaaddddddcccccc','d','b');

# ====================================数学函数=========================================================

# ROUND：四舍五入
SELECT ROUND(5.55555,2)；

# CEIL：向上取整，返回 >= 该参数的最小整数
SELECT CEIL(1.02);

# 向下取整，返回 <= 该参数的最大整数
SELECT FLOOR(1.02);

# TRUNCATE：截断
SELECT TRUNCATE(1.9999,1); # 输出；1.9

# MOD：取余
SELECT MOD(10,3)

# ====================================日期函数=========================================================

# NOW：返回当前系统日期+时间
SELECT NOW();

# CURDATE：返回当前系统日期，不包含时间
SELECT CURDATE();

# CURTIME：返回当前时间，不包含日期
SELECT CURTIME();

# 获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW());
SELECT YEAR('1999-1-1');
SELECT MONTH(NOW());
SELECT MONTH('1999-1-1');

# STR_TO_DATE：将日期字符串转换为日期
SELECT STR_TO_DATE('1999-10-8','%y-%c-%d');

# DATE_FORMAT：将日期转换为字符串
SELECT DATE_FORMAT(CURDATE(),'%y年%m月%d日');

# 查询有奖金的员工名和入职日期（xx月/xx日 xx年）
SELECT last_name , DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期 FROM employees;

# ====================================其他函数=========================================================

SELECT VERSION(); # 查询数据库版本
SELECT DATABASE(); # 查询数据库
SELECT USER(); # 查询用户

# ====================================流程控制函数=========================================================

SELECT IF(10>4,1,2);

SELECT last_name,commission_pct ,IF(commission_pct IS NULL,'没奖金','有奖金') 备注 FROM employees;

# CASE函数的使用一：类似于Java中的switch、case的效果

# 查询员工的工资，要求
# 部门=30，显示的工资为1.1倍
# 部门=40号，显示工资为1.2倍
# 部门=50，显示工资为1.2倍
# 其他部门，显示的工资为原工资
SELECT salary 原始工资,department_id,
CASE department_id 
		WHEN 30 THEN salary * 1.1 
		WHEN 40 THEN salary * 1.2
		WHEN 50 THEN salary * 1.3 
		ELSE salary 
END AS 新工资
FROM employees;

# CASE函数的使用二：类似于Java中的多重if

# 查询员工的工资情况
SELECT salary,
CASE
WHEN salary > 10000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 20000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

4.2、单行函数总结

函数总结

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
/*
常见函数总结：
	字符函数：
		length、concat、substr、instr、trim、upper、lower、lpad、rpad、replace
	数学函数：
		round、ceil、floor、truncate、mod
	日期函数：
		now、curdate、curtime、year、month、monthname、day、hour、minute、second、str_to_date、date_format、
	其他函数：
		version、database、user
	控制函数：
		if、case
*/

例

sql
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 NOW();

# 2、查询员工号、姓名、工资、以及工资提高20%之后的结果（new salary）
SELECT employee_id,last_name,salary,salary*1.2 "new salary" FROM employees;

# 3、将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT LENGTH(last_name),SUBSTR(last_name,1,1) 首字符 ,last_name FROM employees ORDER BY 首字符;

# 4、做一个查询，产生以下的结果
# <last_name> earns <salart> monthly but wants <salary*3>
SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS "Dream Salary" FROM employees;

# 5、使用case-when，按照下面的条件
SELECT job_id AS job,
CASE job_id
	WHEN 'AD_PRES' THEN 'A'
	WHEN 'ST_MAN' THEN 'B'
	WHEN 'IT_PR OG' THEN 'C'
	WHEN 'SA_PRE' THEN 'D'
	WHEN 'ST_CLERK' THEN 'E'
END AS Grade
FROM employees;

5、分组函数

5.1、常见函数

语法

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
32
33
34
35
36
# 分组函数
/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
	sum：求和、avg：平均值、max：最大值、min：最小值、count：计算不为null的个数
特点：
	1、sum、avg一般用于处理数值型
	2、max、min、count可以处理任何类型
	3、以上分组函数都忽略null值
	4、可以和 DISTINCT 搭配实现去重
	5、count函数的单独介绍：一般count(*)用作统计函数
	5、和分组函数一同查询的字段要求是group by 后的字段

*/

# 1、简单使用
SELECT SUM(salary) 求和 , AVG(salary) 平均值 , MIN(salary) 最小值 , MAX(salary) 最大值 , COUNT(salary) 统计 FROM employees; 

# 2、count函数的具体使用

# (1)用于统计指定字段中非空的总行数
SELECT COUNT(salary) FROM employees;

# (2)用于统计总行数
SELECT COUNT(*) FROM employees;

# (3) 结果一般和COUNT(*)一样
SELECT COUNT(1) FROM employees;
/*
效率：
MISAM存储引擎下：COUNT(*)的效率高
INNODB存储引擎下：COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
*/

# 3、和分组函数一同查询的字段有限制
SELECT AVG(salary) ,employee_id FROM employees;

案例

sql
1
2
3
4
5
6
7
8
# 1、查询公司员工的最大值，最小值，平均值，总和
SELECT MAX(salary) max_sal,MIN(salary) min_sal,ROUND(AVG(salary),2) avg_sal,SUM(salary) sum_sal FROM employees;

# 2、查询员工表中的最大入职时间和最小入职时间的相差天数(DATEDIFF)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE FROM employees;

# 3、查询部门编号为90的员工个数
SELECT COUNT(*) FROM employees WHERE department_id = 90;

5.2、分组查询

语法和案例

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
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
# 进阶查询：分组查询
/*
语法
	SELECT 分组函数 , 列（要求出现在group by的后面）
	from 表
	【where 筛选条件】
	group by 分组的列表
	【order by 子句】
注意：
	查询的列表必须特殊，要求是分组函数和group by 后出现的字段
特点：
	1、分组查询中的筛选按条件分为两类
					数据源				  	位置													关键字
		分组前筛选：  原始表				  	GROUP BY 子句前面										WHERE
		分组后筛选	 分组后的结果集    	  GROUP BY 子句的后面									HAVING
		分组函数做条件肯定是放在HAVING子句中
		能用分组前筛选的，就优先考虑使用分组前筛选
	2、GROUP BY 子句支持单个字段分组，多个字段分组（多个字段之间用逗号，没有顺序要求），表达式或函数（使用较少）
	3、可以添加排序（排序放在整个分组查询的后面）
*/

# 1、查询每个工种的最高工资
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;

# 2、查询每个位置上的部门个数
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;

# 3、添加筛选条件：

# 3.1、查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

# 3.2、查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

# 3.3、查询哪个部门的员工个数>2
# 步骤：
# 	①查询每个部门的员工个数
# 	②根据①的结果进行筛选，查询哪个部门的员工个数>2
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;

# 3.4、查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
# ①查询每个工种有奖金的员工的最高工资
# ②根据①的结果进行筛选，最高工资>12000
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000;

# 3.5、查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及最低工资
# ①查询每个领导手下的员工固定最低工资
# ②添加筛选条件：编号>102
# ③最低工资大于5000
SELECT MIN(salary),manager_id FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;

# 4、按表达式或函数进行分组
# 按员工姓名的长度分组，查询每一组员工个数，筛选员工个数>5的有哪些
# ①查询每个长度的员工个数
# ②添加筛选条件
SELECT COUNT(*),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5;

# 按多个字段分组
# 查询每个部门每个工种的员工的平均工资
SELECT AVG(salary) ,department_id ,job_id FROM employees GROUP BY job_id,department_id;

# 添加排序
# 查询每个部门工种的员工的平均工资，并且按平均工资的高低显示
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY job_id,department_id ORDER BY AVG(salary) DESC;\

# 添加排序
# 查询每个部门工种的员工的平均工资，并且按平均工资的高低显示
SELECT AVG(salary),department_id,job_id FROM employees GROUP BY job_id,department_id HAVING AVG(salary)>10000 ORDER BY AVG(salary) DESC;

6、连接查询

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/*
含义：又称多表查询，当查询的字段来自多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2 有n行，结果 = m*n行
发生笛卡尔积现象，没有有效的连接条件
如何避免：添加上有效的连接条件

分类：
	按年代分类：
		sql92标准：仅支持内连接
		sql99标准：支持 内连接+外连接（左外和右外）+交叉连接【推荐】
	按功能分类：
		内连接：用于查询多张表的交集
			等值查询
			非等值查询
		外连接：
			左外连接
			右外连接
			全外连接
		交叉连接：
*/

6.1、sql92

6.1.1、等值查询

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
# 连接查询

# 一、sql92标准

# 1、等值查询：
/*
	①多表等值连接的结果为多表的交集部分
	②n表连接 ，至少需要n-1个连接条件
	③多表的顺序没有要求
	④一般需要为表起别名
	⑤可以搭配前面所学的所有子句使用，比如排序，分组，筛选等
*/

# （1）查询女生名和对应的男生名 
SELECT `name`,boyName FROM boys,beauty WHERE boys.id = beauty.boyfriend_id;

# （2）查询员工名和对应的部门名
SELECT last_name,department_name FROM employees,departments WHERE employees.department_id=departments.department_id;

# 2、为表起别名【表名 [as] 别名】，提高语句的简洁度，区分多个重名字段

# （1）查询员工名，工种号。工种名
SELECT last_name,a.job_id,job_title FROM employees a,jobs b WHERE a.job_id=b.job_id;

# 3、添加筛选

# （1）查询有奖金的员工名，部门名
SELECT last_name,commission_pct,department_name FROM employees a,departments b WHERE a.department_id=b.department_id AND a.commission_pct IS NOT NULL;

# （2）查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city FROM departments d,locations l WHERE d.location_id = l.location_id AND city LIKE '_o%';

# 4、添加分组

# （1）查询每个城市的部门个数
SELECT COUNT(*) 个数 , city FROM departments d,locations l WHERE d.location_id = l.location_id GROUP BY city;

# （2）查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary) FROM departments d,employees e WHERE d.department_id=e.department_id
AND commission_pct IS NOT NULL GROUP BY department_name,d.manager_id;

# 5、添加排序

# （1）查询每个工种的工种名和员工个数， 并且按员工个数降序
SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e.job_id=j.job_id GROUP BY job_title ORDER BY COUNT(*) DESC;

# 6、实现三表连接

# （1）查询员工名、部门名和所在城市
SELECT last_name,department_name,city FROM employees e,departments d,locations l WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND city LIKE 's%' ORDER BY department_name DESC;

6.1.2、非等值查询

非等值查询一般为不是 = 来查询，使用 >、<等查询

sql
1
2
3
4
# 二、非等值查询

# 1、查询员工的工资和工资级别
SELECT e.salary,grade_level FROM employees e,job_grades g WHERE e.salary BETWEEN g.lowest_sal AND g.highest_sal AND g.grade_level='A';

6.1.3、自连接

和等值查询类似，自连接是自己连接自己，例如三级分类表

sql
1
2
3
4
# 自连接

# 1、查询员工名和上级
SELECT e.last_name ,m.last_name  FROM employees e,employees m WHERE e.manager_id=m.employee_id;

6.2、sql99

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 二、sql99语法
/*
语法
	SELECT 查询列表 
	FROM 表1 别名 【连接类型】 
	JOIN 表2 别名
	ON 连接条件
	WHERE 筛选条件
	
内连接（⭐）：INNER
外连接（⭐）
	左外（⭐）：LEFT【OUTER】
	右外（⭐）：RIGHT【OUTER】
	全外：FULL【OUTER】
交叉连接：CROSS

特点：
	①添加排序、分组、筛选
	②inner可以省略
	③筛选条件可以放在where后面，连接条件放在on后面，提高分离性，便于阅读
	④inner join 连接可以和sql92中的等值连接效果是一样的
*/

6.2.1、等值查询

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
# 1、内连接
/*	语法：
SELECT 查询列表 FROM 表1 别名  INNER JOIN 表2 别名 ON 连接条件；
分类：等值、非等值、自连接
*/

# （1）等值连接

# 1）查询员工名，部门名
SELECT last_name,department_name FROM employees e INNER JOIN departments d ON e.department_id=d.department_id;

# 2）查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title FROM employees e INNER JOIN jobs j ON e.job_id=j.job_id WHERE e.last_name LIKE '%e%';

# 3）查询部门个数>3的城市名和部门个数，（添加分组+筛选）
# ①查询每个城市的部门个数
# ②在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数 FROM departments d INNER JOIN locations l ON d.location_id=l.location_id GROUP BY city HAVING COUNT(*)>3;

# 4）查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
# ①查询每个部门的员工个数
# ②在①结果上筛选员工个数>3的记录，并排序
SELECT COUNT(*) , department_name FROM employees e INNER JOIN departments d ON e.department_id=d.department_id GROUP BY department_name HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC;

# 5）查询员工名、部门名、工种名，并按部门名降序
SELECT last_name,department_name,job_title FROM employees e INNER JOIN departments d ON e.department_id=d.department_id INNER JOIN jobs j ON e.job_id=j.job_id ORDER BY department_name DESC;

6.2.2、非等值查询

sql
1
2
3
4
5
6
7
# 非等值查询

# 1、查询员工的工资级别
SELECT salary,grade_level FROM employees e INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

# 2、查询每个工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),salary,grade_level FROM employees e INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal GROUP BY grade_level HAVING COUNT(*)>20 ORDER BY grade_level DESC;

6.2.3、自连接

和等值查询类似，自连接是自己连接自己，例如三级分类表

sql
1
2
3
4
# 自连接

# 1、查询员工名和上级
SELECT e.last_name ,m.last_name FROM employees e  INNER JOIN employees m ON e.manager_id=m.employee_id WHERE e.last_name LIKE '%k%';

6.2.4、外连接

  • 英文图示

image-20211029140510650

  • 中文图示

image-20211029140510650

应用场景：用于查询一个表中有， 另一个表没有的记录

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 外连接
/*
应用场景：用于查询一个表中有， 另一个表没有的记录
特点：
	1、外连接的查询结果为主表中的所有记录【主要想要哪张表中的记录，哪张表即为主表】
		如果从表中有和它匹配的，则显示匹配的值
		如果从表中没有和它匹配的，则显示null
		外连接查询结果 = 内连接结果 + 主表中有而从表没有的记录
	2、左外连接，left左边的是主表
	   右外连接，right join 右边的是主表
	3、主外和右外交换两个表的顺序，可以实现同样的效果
*/

# 查询没有男朋友的女生

# （1）左外连接
SELECT b.name,bo.* FROM beauty b LEFT OUTER JOIN boys bo ON b.boyfriend_id=bo.id WHERE bo.id IS NULL;
# （2）右外连接
SELECT b.name,bo.* FROM boys bo RIGHT OUTER JOIN beauty b ON b.boyfriend_id=bo.id WHERE bo.id IS NULL;

6.2.5、全外连接

由于 MySQL 不支持全外连接，需要了结上网查询

6.2.6、交叉连接

交叉连接就是使用 sql99 语法实现了笛卡尔乘积的效果

sql
1
2
# 交叉连接——使用了sql99语法实现的笛卡尔乘积的效果
SELECT *FROM beauty b CROSS JOIN boys bo;

6.3、sql92 和 sql99 总结

sql
1
2
3
4
5
# sql92和sql99
/*
功能：sql99支持的较多
可读性：sql99实现的连接条件和筛选条件的分离，可读性较高
*/

7、子查询

7.1、放在 where 后面

语法

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 子查询
/*
含义：出现在其他语句中的SELECT语句，成为子查询或内查询。外部的查询语句，成为主查询或外查询
分类：
	按子查询出现的位置：
		SELECT后面：
			【标量子查询】
		FROM后面：
			【表子查询】
		WHERE或HAVING后面：⭐
			【标量子查询⭐、列子查询⭐、行子查询】
		EXISTS后面（相关子查询）：
			【表子查询】
	按结果集的行列数不同：
		标量子查询（结果集只有一行一列）
		列子查询（结果集只有一列多行）
		行子查询（结果集只有一行多列）
		表子查询（结果集一般为多行多列）
*/

标量子查询

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
32
# 1、标量子查询

# （1）谁的工资比Abel高
# ①查询Abel的工资
SELECT salary FROM employees WHERE last_name ='Abel';
# 查询员工的信息，满足salary>①的结果
SELECT salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name ='Abel');

# （2）返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id和工资
# ①查询141号员工的job_id
SELECT job_id FROM employees WHERE employee_id=141;
# ②查询143号员工的工资
SELECT salary FROM employees WHERE employee_id=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);
 
 # （3）返回公司工资最少的员工的last_name,job_id和salary
 
 # ①查询最低工资
 SELECT MIN(salary) FROM employees;
 # ②根据①的结果查询需要的员工信息
 SELECT last_name,job_id,salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);
 
 # （4）查询最低工资大于50号部门最低工资的部门id和其最低工资
 # ①查询50号部门的最低工资
 SELECT MIN(salary) FROM employees WHERE department_id = 50;
 # ②查询每个部门的最低工资
 SELECT MIN(salary),department_id FROM employees GROUP BY department_id;
 # ③在②基础上，满足min(salary)>①
 SELECT MIN(salary),department_id FROM employees GROUP BY department_id HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id = 50);

列子查询

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 列子查询（多行子查询）【一列多行，比如id集合】

# （1）返回location_id是1400或1700的部门中的所有员工姓名
# ①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700);
# ②查询员工姓名，要求部门是①列表中的某一个
SELECT last_name FROM employees WHERE department_id IN (SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700));

# （3）返回其他部门中比job_id为`IT_PROG`部门任一工资都低的员工的员工号、姓名、job_id以及salary
# ①查询job_id为IT_PROG部门任一工资
SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG';
# ②查询员工工号、姓名、job_id以及salary，slary<①的任意一个
SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ANY( SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG');

# （3）返回其他部门中比job_id为`IT_PROG`部门所有工资都低的员工的员工号、姓名、job_id以及salary
 SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ALL( SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG');

行子查询

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 3、行子查询

# （1）查询员工编号最小并且工资最高的员工信息

# 1）使用非行子查询
# ①查询最小员工编号
SELECT MIN(employee_id) FROM employees;
# ②查询最高工资
SELECT MAX(salary) FROM employees;
# ③查询员工信息
SELECT *FROM employees WHERE employee_id=(SELECT MIN(employee_id) FROM employees) AND salary=(SELECT MAX(salary) FROM employees);

# 2）使用行子查询【使用不多】
SELECT *FROM employees WHERE(employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) FROM employees);

7.2、放在 select 后面

sql
1
2
3
4
5
6
7
# SELECT后面【仅仅支持标量子查询】

# 1、查询每个部门的员工个数
SELECT d.* , (SELECT COUNT(*) FROM employees e WHERE d.department_id=e.department_id) FROM departments d;

# 2、查询员工号=102的部门名
SELECT (SELECT d.department_name FROM departments d INNER JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id=102) 部门名;

7.3、放在 from 后面

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# FROM后面：FROM后面查询出来的数据是一张虚拟表，需要给起别名
/*
将子查询结果充当一张表， 要求必须起别名
*/
# 1、查询每个部门的平均工资的工资等级
# ①查询每个部门的平均工资
SELECT
	AVG( salary ) avg,
	department_id 
FROM
	employees 
GROUP BY
	department_id;
# ②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT
	ag_dep.*,
	g.grade_level 
FROM
	( SELECT AVG( salary ) avg, department_id FROM employees GROUP BY department_id ) ag_dep
	INNER JOIN job_grades g ON ag_dep.avg BETWEEN lowest_sal 
	AND highest_sal;

7.4、exists 后面（相关子查询）

sql
1
2
3
4
5
6
7
8
9
10
# EXISTS后面（相关子查询）
/*
EXISTS（完整的查询语句）
结果：1/0
如果子查询有结果，返回1。如果子查询为null，返回0
*/
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary =30000) 是否存在;

# 1、查询有员工的部门名
SELECT department_name FROM departments d WHERE EXISTS(SELECT *FROM employees e WHERE d.department_id=e.department_id);

8、分页查询

语法

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 分页查询
/*
应用场景：当要显示的数据，一页显示补全，需要分页提交sql请求
语法：
	SELECT 查询列表 FROM 表 
	【
		JOIN TYPE JOIN 表2 
		ON 连接条件
		WHERE 筛选条件
		GROUP BY 分组字段
		HAVING 分组后的筛选
		ORDER BY 排序字段
	】
	LIMIT offset,size;
	offset：从第几条数据开始查询，0是第一条
	size：查询几条数据
特点：
	①LIMIT语句放在查询语句的最后
	②公式：
		要显示的页数page，每页显示的条目数size
		LIMIT (page-1)*size , size
*/

案例

sql
1
2
3
4
5
6
7
8
# 1、查询前五条员工信息
SELECT *FROM employees LIMIT 0,5;

# 2、查询第11-25条
SELECT *FROM employees LIMIT 10,15;

# 3、查询有奖金的员工信息，并且工资较高的前10名显示出来
SELECT *FROM employees WHERE commission_pct IS NOT NULL ORDER BY commission_pct DESC LIMIT 0,10;

9、union 联合查询

语法

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 联合查询（union）
/*
union：联合、合并。将多条查询语句的结果合并成一个结果
语法：
	查询语句1
	UNION
	查询语句2
	UNION
	.......
应用场景：
	要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：
	1、要求多条查询语句的列数是一致的
	2、要求多条查询语句的查询的每一列的类型和顺序最好一致
	3、UNION关键字默认去重，如果使用 UNION ALL可以包含重复项
*/

案例

sql
1
2
3
4
5
6
7
8
9
10
11
12
# 1、查询部门编号>90或邮箱中包含a的员工信息

#（1）非联合查询
SELECT *FROM employees WHERE email LIKE '%a%' OR department_id>90;
#（2）联合查询——去重
SELECT *FROM employees WHERE email LIKE '%a%' 
UNION
SELECT *FROM employees WHERE department_id>90;
#（3）联合查询——非去重（UNION后面加上ALL关键字）
SELECT *FROM employees WHERE email LIKE '%a%' 
UNION ALL
SELECT *FROM employees WHERE department_id>90;

五、DML（数据操纵语言）

插入语句

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
32
# DML语言
/*
数据操纵语言：
	插入：INSERT
	修改：UPDATE
	删除：DELETE
*/

# 一、插入语句
/*
语法：
	方式一：
		①INSERT INTO 表名(列名,......) VALUES(值1,......);
		②INSERT INTO 表名(列名,......) VALUE(值1,......);
		③INSERT INTO 表名(列名,......) 
			VALUES(值1,......),
			VALUES(值1,......),
			VALUES(值1,......)
	方式二：
		INSERT INTO 表名 SET 列名=值,列名=值,......;
两种方式比较：
	（1）方式一可以插入多行，方式二不支持
	（2）方式一支持子查询，方式二不支持
	INSERT INTO 表(字段,字段,......) SELECT 字段,字段,...... FROM 表 条件
*/

# 1、插入的值的类型要与列的类型一致或兼容
# 2、不可以为null的列必须插入值
# 3、可以为null的列，可以将值设置为null。可以不写列名，列值，默认为null。
# 4、列的顺序可以调换
# 5、列数和值的个数必须一致
# 6、可以省略列名，默认所有列，而且顺序和表中列的顺序一致

修改语句

sql
1
2
3
4
5
6
7
8
9
10
11
12
# 二、修改语句
/*
1、修改单表记录⭐
语法：
	UPDATE 表名 SET 列名=数值,列名=数值,......【WHERE 筛选条件】
2、修改多表记录【补充】
语法【支持92和99语法】：
	（1）92语法
					UPDATE 表1 别名,表2 别名 SET 列=值,...... WHERE 连接条件 AND 筛选条件;
	（2）99语法
					UPDATE 表1 别名 INNER|LEFT|RIGHT JOIN 表2 别名 ON 连接条件 SET 列=值,...... WHERE 筛选条件
*/

删除语句

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/*
方式一：DELETE
语法：
	1、单表的删除【⭐】
		语法：
			DELETE FROM 表名 WHERE 筛选条件
	2、多表删除【补充】:清空数据,将指定表中的数据全部删除,相当于【DELETE FROM TABLE】
		语法：
			TRUNCATE TABLE 表名;
DELETE和TRUNCATE比较：
	1、DELETE 可以加 WHERE条件，TRUNCATE不能加
	2、TRUNCATE删除，效率高一点
	3、假如要删除的表中有自增长列，使用delete删除后，再插入数据，自增长列的值从断点开始。而 TRUNCATE删除后，再插入数据，自增长列的值从1开始
	4、TRUNCATE没有返回值，DELETE有返回值
	5、TRUNCATE删除不能回滚，DELETE删除可以回滚
*/

六、DDL（数据定义语言）

1、库和表的操作

定义

sql
1
2
3
4
5
6
7
8
9
10
11
/*
数据定义语言
库和表的管理
一、库的管理
	创建、修改、删除
二、表的管理
	创建、修改、删除
创建：CREATE
修改：ALTER
删除：DROP
*/

库的操作

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 一、库的管理

# 1、库的创建
/*
语法：
	CREATE DATABASE 库名;
*/
CREATE DATABASE IF NOT EXISTS books_test;

# 2、库的修改【在新版本的MySQL中，该语句不能使用】
RENAME DATABASE books_test TO book_test;

# 3、更改库的字符集
ALTER DATABASE books_test CHARACTER SET utf8mb4;

# 4、库的删除
DROP DATABASE IF EXISTS books_test;

表的操作

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
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
# 表的管理
# 1、表的创建（⭐）
/*
语法：
	CREATE TABLE 表名(
			列名 列的类型 【(长度) 约束】,
			列名 列的类型 【(长度) 约束】,
			列名 列的类型 【(长度) 约束】,
			......
	)
	
通用写法：先删除旧的，再建新的
	DROP DATABASE IF EXISTS 旧库名'
	CREATE DATABASE 新库名;
	DROP TABLE IF EXISTS 旧表名;
	CREATE TABLE 新表名(......);
*/

# 2、创建book表
CREATE TABLE IF NOT EXISTS `book` ( id INT, #编号
	book_name VARCHAR ( 20 ), #书名
	price DOUBLE, # 价格
	authId VARCHAR ( 20 ), # 作者编号
public_date DATETIME # 出版日期
);

# 3、查看表的信息
DESC `book`;
DESC `author`;

# 创建表author
CREATE TABLE `author` ( id INT, #编号
	auth_name VARCHAR ( 20 ), # 作者名字
	nation VARCHAR ( 10 ) # 国籍
);

# 4、表的修改
# ①修改列名
ALTER TABLE `book` CHANGE COLUMN authId auth_id INT;
# ②修改列的类型或约束
ALTER TABLE `book` MODIFY COLUMN price DECIMAL;
# ③添加新的列
ALTER TABLE author ADD COLUMN annual DOUBLE;
# ④删除列
ALTER TABLE author DROP COLUMN annual;
# ⑤修改表名
ALTER TABLE author RENAME TO book_author; 

# 5、表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;

# 6、表的复制
INSERT INTO author VALUES(1,'村上春树','日本'),(2,'莫言','中国'),(3,'金庸','中国');
# （1）只复制表全部结构
CREATE TABLE copy LIKE author;
# （2）复制表的结构和全部数据
CREATE TABLE copy2 SELECT *FROM author;
# （3）复制表的结构和部分数据
CREATE TABLE copy3 SELECT *FROM author WHERE nation='中国';
# （4）复制表的部分字段，0表示false
CREATE TABLE copy4 SELECT id,auth_name FROM author WHERE 0;

2、表的数据类型

2.1、整型

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 整型
/*
TINYINT、SMALLINT、MEDIUMINT、INT/INTEGER、BIGINT
	1					2					3						4						5
特点：
	1、如果不设置无符号还是有符号，默认是有符号。如果设置无符号，需要加UNSIGNED关键字。
	2、如果插入的数字超出了指定类型的范围，会报异常，且插入的值为指定类型的临界值
	3、如果不设置长度，会有默认的长度。长度显示的最大宽度，如果不够会用0在左边进行填充，但必须搭配 ZEROFILL 使用，如果加上了ZEROFILL之后，会默认为无符号。
*/
# 如何设置无符号和有符号
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
	t1 INT ,# 不加UNSIGNED关键字为有符号
	t2 INT UNSIGNED,# 加上UNSIGNED设置为无符号
	t3 INT ZEROFILL# 加上ZEROFILL后默认变为无符号
);

2.2、浮点型

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
# 浮点型

# 小数
/*
1、浮点型：
	FLOAT(M,D)、DOUBLE(M,D)
2、定点型
	DEC(M,D)、DECIMAL(M,D)
特点：
	1、
		M：整数部位+小数部位
		D：小数部位
		如果超过范围，则插入临界值
	2、
		M和D可以省略
		如果是DECIMAL，则M默认为10，D默认为0
		如果是FLOAT和DOUBLE，则会根据插入数值的精度来决定精度
	3、
		定点型的精度较高，如果要求插入数值的进度较高，如货币运算等考虑使用
		
原则：
	所选择的类型越简单越好，能保存数值的类型越小越好
*/

# 1、测试M和D
CREATE TABLE tab_float(
		f1 FLOAT(5,2),
		f2 DOUBLE(5,2),
		f3 DECIMAL(5,2)
)

2.3、字符型

image-20211029140510650

写法 M 的意思 特点 空间的耗费 效率
char char(M) 最大的字符数，可以省略，默认为 1 固定长度的字符 比较耗费 高
varchar varchar(M) 最大的字符数，不可以省略 可变长度的字符 比较节省 低
sql
1
2
3
4
5
6
7
8
9
10
11
# 字符型
/*
较短的文本：
	CHAR、VARCHAR
	其他：
		BINARY和VARBINARY用于保存较短的二进制
		ENUM用于保存枚举
		SET用于保存集合
较长的文本：
	TEXT、BLOB（较大的二进制，例如图片）
*/

2.4、日期类型

字节 范围 是否受时区等影响
DATETIME 8 1000-9999 不受
TIMESTAMP 4 1970-2038 受
sql
1
2
3
4
5
6
7
8
9
# 日期类型
/*
分类：
DATE：只保存日期
TIME：只保存时间
YEAR：只保存年
DATETIME：保存日期+时间
TIMESTAMP：保存日期+时间
*/

3、常见约束

3.1、创建表时添加约束

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
# 常见约束
/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类：六大约束
	NOT NULL：非空，用于保证该字段不能为空【比如姓名，学号等】
	DEFAULT：默认，用于保证该字段有默认值【比如性别】
	PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且具有非空【比如学号，员工编号等】
	UNIQUE：唯一，用于保证该字段的唯一性，可以为空【比如座位号】
	CHECK：检查约束【MySQL中不支持（SQL serve支持）。比如性别只能男和女，其他的旧不满足，比如用户的年龄范围为1-100，可以使用该约束条件】
	FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值，比如商品的类别编号，来自类别表中的编号
添加约束的时机：
	1、创建表时
	2、修改表时
约束的添加分类：
	列级约束：
		六大约束语法上都支持，但外键约束没有效果
	表级约束：
		除了非空、默认，其他都支持
外键：
	1、要求在从表设置外键关系
	2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
	3、要求主表关联列必须是一个key(一般是主键或唯一)
	4、插入数据时，先插入主表，再插入从表
	5、删除数据时，先删除从表，再删除主表
*/
CREATE TABLE 表名(
		字段名 字段类型 列级约束 列级约束 ... ,
		字段名 字段类型,
		表级约束
)

主键和唯一的对比

保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合【联合主键 】
主键索引 √ × 至多有一个 √【不推荐】
唯一索引 √ √ 可以有多个 √【不推荐】

列级约束

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
# 一、创建表时添加的约束

# 1、添加列级约束
/*
语法：
	直接在字段名和类型后面追加约束类型即可，只支持，默认，非空，主键，唯一
*/

CREATE TABLE major ( 
	id INT PRIMARY KEY, # 主键
	majorName VARCHAR ( 20 ) 
);

CREATE TABLE stuinfo (
	id INT PRIMARY KEY,# 主键
	stuName VARCHAR ( 20 ) NOT NULL,# 非空
	gender CHAR ( 1 ) CHECK ( gender = '男' OR gender = '女' ),# 检查约束（MySQL不支持，但是可以写，没有效果）
	seat INT UNIQUE ,# 唯一约束，座位号
	age INT DEFAULT 18,# 默认
	majorId INT REFERENCES major(id)# 外键
);

# 查询指定表的索引，包括主键，外键，唯一
SHOW INDEX FROM stuinfo;

表级约束

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
# 添加表级约束
/*
语法：
	在各个字段的最下面
		【CONSTRAINT 约束名】 约束类型(字段名)
*/

CREATE TABLE major ( 
	id INT PRIMARY KEY, # 主键
	majorName VARCHAR ( 20 ) 
);

DROP TABLE stuinfo;
CREATE TABLE stuinfo (
	id INT,
	stuname VARCHAR ( 20 ),
	gender CHAR ( 1 ),
	seat INT,
	age INT,
	majorId INT,
	# 添加表级约束
	CONSTRAINT pk PRIMARY KEY ( id ),# 主键
	CONSTRAINT uq UNIQUE ( seat ),# 唯一
	CONSTRAINT ck CHECK ( gender = '男' OR gender = '女' ),# 检查约束（MySQL不支持，但是可以写，没有效果） 
	CONSTRAINT `fk_stu_major` FOREIGN KEY(`majorId`) REFERENCES `major`(`id`)# 外键
);

# 查询指定表的索引，包括主键，外键，唯一
SHOW INDEX FROM stuinfo;

通用写法

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
32
33
#通用的写法：

CREATE TABLE major ( 
	id INT PRIMARY KEY, # 主键
	majorName VARCHAR ( 20 ) 
);

# 设置单个主键和唯一索引★
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

);

# 设置联合组件和联合索引【不推荐使用】
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT,
	stuname VARCHAR ( 20 ),
	gender CHAR ( 1 ),
	seat INT,
	age INT,
	majorId INT,
	# 添加表级约束
	CONSTRAINT pk PRIMARY KEY ( id,stuname ),# 联合组件
	CONSTRAINT uq UNIQUE ( seat,stuname ),# 联合唯一索引
	CONSTRAINT ck CHECK ( gender = '男' OR gender = '女' ),# 检查约束（MySQL不支持，但是可以写，没有效果） 
	CONSTRAINT `fk_stu_major` FOREIGN KEY(`majorId`) REFERENCES `major`(`id`)# 外键
);

3.2、修改表时添加约束

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
# 二、修改表时添加约束
/*
1、添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;

2、添加表级约束
ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 约束类型(字段名) 【外键的引用】;
*/

# 1、添加非空约束 
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

# 2、添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 20;

# 3、添加主键
# （1）列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
# （2）表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一
# （1）列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
# （2）表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);

#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

3.3、修改表时删除约束

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

4、标识列

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
32
33
34
# 标识列【AUTO_INCREMENT】
/*
又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值


特点：
	1、标识列必须和主键搭配吗？不一定，但要求是一个key
	2、一个表可以有几个标识列？至多一个！
	3、标识列的类型只能是数值型
	4、标识列可以通过 SET auto_increment_increment=3;设置步长
	5、可以通过 手动插入值，设置起始值


*/

#一、创建表时设置标识列
 
# 创建表，添加标识
CREATE TABLE tab_identity(
	id INT  ,
	NAME FLOAT UNIQUE AUTO_INCREMENT

);

# 清空表
TRUNCATE TABLE tab_identity; 

# 查看步长值
SHOW VARIABLES LIKE '%auto_increment%';


# 设置步长值【步长值：每次加多少】一般不该
SET auto_increment_increment=3;

七、TCL 语言（数据控制语言）

1、事务

  • 原子性：一个事务不可再分割，要么执行要么不执行
  • 一致性：一个事务执行会使数据从一个一致状态切换到另一个一致状态
  • 隔离性：一个事务的执行不受其他事务的干扰
  • 持久性：一个事务一旦提交，则会永久的改变数据库的数据
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
# TCL
/*
Transaction Control Language 事务控制语言
事务：
	一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
事务的属性(ACID)：
	原子性：一个事务不可再分割，要么执行要么不执行
	一致性：一个事务执行会使数据从一个一致状态切换到另一个一致状态
	隔离性：一个事务的执行不受其他事务的干扰
	持久性：一个事务一旦提交，则会永久的改变数据库的数据
事务的创建
	隐式事务，事务没有明显的开启和结束的标记
		比如单条的insert、update、delete语句
	显式事务：事务具有明显的开启和结束标记
		前提：必须先设置自动提交功能为禁用
	设置事务是否自动提交
		SET autocommit=0;
事务开启步骤：
	1、步骤一：
		SET autocommit=0;
		START TRANSACTION;可选的
	2、步骤二：
		编写事务中的sql语句（SELECT INSERT UPDATE DELETE）
	3、步骤三：
		commit; 提交事务
		rollback; 回滚事务
*/

# 查看MySQL支持的存储引擎
SHOW ENGINES;

# 查看事务自动提交状态
SHOW VARIABLES LIKE 'autocommit'

# 示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点

# 演示事务的使用步骤

# （1）开启事务
SET autocommit=0;
START TRANSACTION;#可选的

# （2）编写sql语句
INSERT INTO admin(username,`password`) VALUES('123123','123');

# （3）结束事务，提交和回滚选择一个
COMMIT;# 提交
ROLLBACK;# 回滚

2、delete 和 truncate 在事务使用时的区别

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# delete和truncate在事务使用时的区别

SELECT *FROM admin;

# 1、delete：可以回滚
SET autocommit=0;# 关闭自动提交
START TRANSACTION; # 开启事务
DELETE FROM admin;# 删除表中的所有数据
ROLLBACK;# 回滚，之前的操作无效

# 2、truncate：不可以回滚
SET autocommit=0;# 关闭自动提交
START TRANSACTION; # 开启事务
TRUNCATE TABLE admin;# 清空表
ROLLBACK;# 回滚

3、事务隔离级别

具体看 JDBC 笔记

八、视图的讲解

1、视图的增删改查

视图的好处

  • 重用 sql
  • 简化复杂的 sql 操作，不必知道它的查询细节
  • 保护数据，提高安全性

视图创建和使用

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
# 视图
/*
含义：一张虚拟的表，和普通表一样使用
mysql5.1版本出现新特性，是通过表动态生成的数据
比如
*/# 一、创建视图
/*
语法：CREATE VIEW 视图名 AS 查询语句;
*/# 1、查询邮箱中包含a字符的员工名、部门名和工种信息

# ①创建视图
CREATE VIEW view1 AS SELECT
last_name,
department_name,
job_title 
FROM
	employees e
	JOIN departments d ON e.department_id = d.department_id
	JOIN jobs j ON j.job_id = e.job_id;
	
# ②使用视图
SELECT
	* 
FROM
	view1 
WHERE
	last_name LIKE '%a%';

视图修改

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
32
33
34
35
36
37
38
39
40
41
42
43
44
# 视图的修改
/*
语法：
	方式一：
        CREATE OR REPLACE VIEW 视图名
        AS
        查询语句
	方式二：
		ALTER VIEW 视图名
		AS
		查询语句;
       
*/# 1、创建视图
CREATE VIEW view1 AS SELECT
last_name,
department_name,
job_title 
FROM
	employees e
	JOIN departments d ON e.department_id = d.department_id
	JOIN jobs j ON j.job_id = e.job_id;

# 2、查询视图
SELECT
	* 
FROM
	view1;
	
# 3.1、方式一：修改视图
CREATE 
	OR REPLACE VIEW view1 AS SELECT
	* 
FROM
	employees e 
WHERE
	employee_id < 130;
	
# 3.2、方式二：修改视图
ALTER VIEW view1 AS SELECT
* 
FROM
	employees e 
WHERE
	employee_id < 110;

删除视图

sql
1
2
3
4
5
6
# 删除视图
/*
语法：
	DROP VIEW 视图名，视图名,......;
*/
DROP VIEW view1;

查看视图

查看视图的创建语句和视图的结构

sql
1
2
3
4
5
6
7
# 查看视图

# 1、查看视图结构
DESC view1;

# 2、查看视图创建形式
SHOW CREATE VIEW view1;

视图更新

视图中数据的增删改，视图中的增删改会影响原始表中的数据。视图中的数据在进行增删改的操作时可能会执行失败

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
# 一、视图更新

# 1、创建视图
CREATE 
	OR REPLACE VIEW myv1 AS SELECT
	last_name,
	email 
FROM
	employees;

# 1、插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');

# 2、修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

# 3、删除
DELETE FROM myv1 WHERE last_name = '张无忌';

# 二、具备以下特点的视图不允许更新
# 1、包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
# 2、常量视图
# 3、SELECT中包含子查询
# 4、JOIN、多表连接
# 5、FROM一个不能更新的视图
# 6、WHERE子句的子查询引用了FROM子句中的表

2、视图和表的对比

视图和表的对比

创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了 sql 逻辑 增删改查。一般不能增删改查
表 create table 保存了数据 增删改查
  • 包含以下关键字的 sql 语句：
    • 分组函数、distinct、group by、having、union 或者 union all
    • 常量视图
    • SELECT 中包含子查询
    • JOIN、多表连接
    • FROM 一个不能更新的视图
    • WHERE 子句的子查询引用了 FROM 子句中的表

== 后面的后续学习 ==

九、变量

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
#变量
/*
系统变量：
	全局变量
	会话变量

自定义变量：
	用户变量
	局部变量
*/


#一、系统变量
/*
说明：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
使用步骤：
	1、查看所有系统变量
		show global|【session】variables;
	2、查看满足条件的部分系统变量
		show global|【session】 variables like '%char%';
	3、查看指定的系统变量的值
		select @@global|【session】系统变量名;
	4、为某个系统变量赋值
		方式一：
			set global|【session】系统变量名=值;
		方式二：
			set @@global|【session】系统变量名=值;

*/

十、存储过程和函数

十一、流程控制结构