SQL学习（基础）_sql语句创建用户名user_rose,登录名为rose_lib

目录

(二)、DQL语言 

1. 基础查询

 2. 条件查询

3. 排序函数

4. 常见函数的学习

单行函数

 分组函数

5. 分组查询

6. 连接查询

7. 子查询

8. 分页查询

9. 联合查询（尚硅谷-124节）

(三)、DML语言

 3.1 插入语句

 3.2 修改语句

3.3 删除语句 

(四)、DDL语言

4.1 数据定义语言——对库和表的管理

4.1.1 库的管理

4.1.2 表的管理

4.2 数据类型

4.3 常见约束

(五)、TCL语言

(六)、其他 

6.1 视图

6.2 变量

6.3 存储过程和函数

6.4 流程控制结构

---

(二)、DQL语言 

 
    # 交叉连接
    - 语法：
    select 查询列表
    from 表1 别名
    cross join 表2 别名;
    - 特点：
    类似于笛卡尔乘积:所有行数据两两连接

1. 基础查询

# 1、查询单个字段
select 字段名 from 表名;
 
# 2、查询多个字段
select 字段名，字段名 from 表名;
 
# 3、查询所有字段
select * from 表名;
 
# 4、查询常量
# 注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
select 常量值;
 
# 5、查询函数
select 函数名(实参列表);
 
# 6、查询表达式
select 100/1234;
 
# 7、起别名
# ① as
# ② 空格
 
# 8、去重
select distinct 字段名 from 表名;
 
# 9、+
# 作用：做加法运算
select 数值+数值;  # 直接运算
select 字符+数值;  # 先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算
select null+值;  # 结果都为null
 
# 10、【补充】concat函数
# 功能：拼接字符
select concat(字符1，字符2，字符3,...);
 
# 11、【补充】ifnull函数
# 功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值
select ifnull(commission_pct,0) from employees;
 
# 12、【补充】isnull函数
# 功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0

 2. 条件查询

# 1.条件查询的基本结构
select 查询列表 from 表名 where 筛选条件
 
# 2.简单条件运算符
> < = <> != >= <=  
<=>安全等于 # 可以判断null值
 
# 3. 逻辑运算符
&& and
|| or
!  not
 
# 4、模糊查询
between and # 左右边界不能颠倒顺序
between 120 and 100 # 等价于 >= 120 && <= 100 
 
in ('', '')   # in列表值类型必须统一,且不支持通配符
is null /is not null  # 只用于判断null值； =不能判断null
like   # 一般搭配通配符使用，可以判断字符型或数值型 ；不能判断null
 
# 5.通配符：
%：任意多个字符
_：任意单个字符
通配符转义 \ 或者 '_$_' ESCAPE '$'
 
 
			普通类型的数值	  null值		   可读性
is null		×			   √		      √
<=>		    √			   √		      ×

1. 两个查询语句的执行结果是否相同？ 

select * from employees;
select * from employees where name like '%%' and id like '%%';
#解：不一样，若name或者id有null值则查不出来
 
select * from employees;
select * from employees where commission_pct like '%%' or last_name id like '%%' or employee_id like '%%';
#解：一样，总归有一个条件不含null

 2. 查询员工号为176的员工的姓名和部门号和年薪

SELECT 
        last_name,
        department_id,
        salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM
        employees;

3. 查询employees表中，job_id不为“IT” 或者 工资为12000的员工信息

SELECT *
FROM employees
WHERE job_id <> 'IT'    # 或者 WHERE NOT job_id = 'IT'
OR salary=12000;

3. 排序函数

# 1. 排序查询的基本结构
select 查询列表
from 表
where 筛选条件
order by 排序列表 [asc}desc]
 
# 2. 特点
/*
1、asc ：升序，如果不写默认升序
   desc：降序
2、排序列表 支持 单个字段、多个字段、函数、表达式、别名
3、order by的位置一般放在查询语句的最后（除limit语句之外）
*/

 1. 查询员工信息，要求先按员工工资升序，再按员工编号降序【按多个字段排序】

SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;

2. 查询邮箱中包含e的员工信息，并按邮箱的字节数降序，再按部门号升序

SELECT *,LENGTH(email)
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;

4. 常见函数的学习

• 功能：类似于java、python中的方法，
• 好处：提高代码重用性和隐藏实现细节
• 调用：select 函数名(实参列表) 【from 表】;

单行函数

# 1、字符函数  参数为字符串类型
# concat:连接
# ifnull:判断是否为null，如果为null，则替换为...
# substr:截取子串，索引从1开始，截取某个值后边的信息
# upper:变大写
# lower：变小写
# replace：替换:把字符串中的x1全部替换为X2
# length：获取字节长度
# trim:去前后空格,或者前后某个字段
# lpad：左填充：用指定的字符实现左填充指定长度
# rpad：右填充
# instr:获取子串第一次出现的索引，如果找不到返回0
 
SELECT LENGTH('john');
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
SELECT UPPER('john');
SELECT LOWER('joHn');
SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 
FROM employees;
SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put;   
--截取索引7后边的所有字符：陆展元
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
--截取指定索引处指定长度的字符：李莫愁
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_put
--姓名中的首字符大写，其他字符小写然后用_拼接，显示出来
FROM employees;
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
SELECT LENGTH(TRIM('    张翠山    ')) AS out_put;
SELECT TRIM('aa' FROM 'aaaaaa张aaaa翠山aaaaa') AS out_put;
--去掉前后的aa，得到张aaaa翠山a
SELECT LPAD('殷素素',2,'*') AS out_put;
--素素
SELECT RPAD('殷素素',12,'ab') AS out_put;
--殷素素ababababa
SELECT REPLACE('周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
 
# 2、数学函数
# ceil:向上取整
# round：四舍五入
# mod(a,b):a/b 后取余
# floor：向下取整
# truncate:截断,保留n位小数
# rand:获取随机数，返回0-1之间的小数
 
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);
SELECT CEIL(-1.02);
SELECT FLOOR(-9.99);
SELECT TRUNCATE(1.69999,1);
#mod取余
/*
mod(a,b) ：  a-a/b*b
mod(-10,-3):-10- (-10)/(-3)*（-3）=-1
*/
SELECT MOD(10,-3);
SELECT 10%3;
 
# 3、日期函数
# now：返回当前日期+时间
# year(日期参数):返回日期参数中的年
# month：返回月
# day:返回日
# date_format:将日期转换成字符
# curdate:返回当前日期，不包含时间
# str_to_date:将字符通过指定的格式转换成日期
# curtime：返回当前时间
# hour:小时
# minute:分钟
# second：秒
# datediff:返回两个日期相差的天数
# monthname:以英文形式返回月
 
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT  YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
#查询入职日期为1992--4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
-- %c是1,2,3月份；%m是01,02,03月份。  %Y是4位年份，%y是两位年份。   %H是24小时制。
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;
 
# 4、其他函数
# version 当前数据库服务器的版本
# database 当前打开的数据库
# user当前用户
# password('字符')：返回该字符的密码形式
# md5('字符'):返回该字符的md5加密形式
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
 
#5、流程控制函数
/*
①if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
*/
SELECT IF(10<5,'大','小');
SELECT last_name,commission_pct,IF(commission IS NULL,"没奖金，呵呵","有奖金，嘻嘻") 备注
FROM employees；
 
/*
②case情况1 放在SELECT后，作为一个新的属性显示
case 要判断的变量或表达式或字段
when 常量1 then 要显示的值1；
when 常量2 then 要显示的值2；
...
else 要显示的值n；
end
*/
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情况2  多重if
case 
when 条件1 then 值1 【或语句1；】
when 条件2 then 值2 【或语句2；】
...
else 值n
end
*/
 
 
 
 
SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

1. 显示系统时间（注：日期+时间）

SELECT NOW()

2. 查询员工号，姓名，工资，以及工资提高百分之20后的结果

SELECT employee_id,last_name,salary,salary*1.2 "new salary"
FROM employees;

3. 将员工的姓名按首字母排序，并写出姓名的长度(Length )

SELECT last_name,LENGTH(last_name) 长度,SUBSTR(last_name,1,1) 首字符
FROM employees
ORDER BY 首字符 ASC;
--按last_name排序的话，如果首字母一样，则按第二个字母再排序
ORDER BY last_name ASC;

4. 做一个查询，产生效果：<last_name> earns <salary> monthly but wants <salary*3>

SELECT CONCAT(last_name,'earns',salary,'monthly but wants',salary*3) AS Dream Salary
FROM employees
WHERE salary=24000;

5. 要求使用case-when实现下面的条件

SELECT job_id AS job,
CASE job_id
WHEN AD_PRES THEN A
WHEN ST_MAN THEN B
WHEN IT_PROG THEN C
--else D
END AS 等级
FROM employees;

 分组函数

/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
特点：
1、sum、avg一般用于处理数值型
   max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算
4、count函数的单独介绍
   一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段，其它的都不行
*/
 
 
#1、简单 的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;
SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;
 
#2、参数支持哪些类型:数值型，
SELECT SUM(last_name) ,AVG(last_name) FROM employees; --错
SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;  --错
SELECT MAX(last_name),MIN(last_name) FROM employees; --对，因为last_name可以按字母排序，所以有最值
SELECT MAX(hiredate),MIN(hiredate) FROM employees;  --对
SELECT COUNT(commission_pct) FROM employees;  --对
SELECT COUNT(last_name) FROM employees;  --任何类型都支持：计算非空值得个数
 
#3、是否忽略null
SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees; --null没有参与运算
SELECT MAX(commission_pct) ,MIN(commission_pct) FROM employees; --忽略null
SELECT COUNT(commission_pct) FROM employees;  --忽略null
 
#4、和distinct搭配，实现去重运算
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees; 
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;  --一样工资得去掉
 
#5、count函数的详细介绍
SELECT COUNT(salary) FROM employees;  --所有salary这一列中非null字段
SELECT COUNT(*) FROM employees;  --统计非null行数
SELECT COUNT(1) FROM employees;  --相当于在原表中加了一列相同行数的1，统计1的个数
 
效率：
MYISAM存储引擎下  ，COUNT(*)的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些
 
#6、和分组函数一同查询的字段有限制
SELECT AVG(salary),employee_id  FROM employees; --没有意义，不是一个规则的表格

1. 查询公司员工工资的最大值，最小值，平均值，总和

SELECT MAX(salary) mx_sal ,MIN(salary) mi_sal ,ROUND(AVG(salary),2) ag_sal,SUM(salary) su_sal
FROM employees;

2. 查询员工表中的最大入职时间和最小入职时间的相差天数

--MAX(hiredate)和MIN(hiredate)都是时间，如何求相差天数？  函数DATEDIFF
SELECT DATEDIFF(MAX(hiredate),DATEDIFF(MIN(hiredate)))
SELECT DATEDIFF('2017-10-1','2017-9-29');

3. 查询员工部门编号为90的员工个数

SELECT count(*)
FROM employees
WHERE department_id = 90;

5. 分组查询

/*
语法：
        select column,group_function(column)
        from 表
        [where 筛选条件]
        group by 分组的字段
        [order by 排序的字段];
特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、分组查询中的筛选分为两类：分组前筛选和分组后筛选
           数据源       位置        关键字
分组前筛选  原始表    group by前	    where
分组后筛选 group by后的结果集  g_b后   having
(1)问题1：分组函数做筛选能不能放在where后面
答：不能。分组函数做条件肯定放在HAVING子句中
(2)问题2：where——group by——having
一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率
3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用，后面加order by
*/
 
 
 
#引入：查询每个部门的员工个数
SELECT COUNT(*) FROM employees GROUP BY department_id;
 
#1.简单的分组
#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
#案例2：查询每个位置的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
 
 
#2、可以实现分组前的筛选
#案例1：查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
#案例2：查询有奖金的每个领导手下员工的平均工资
SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
 
 
#3、分组后筛选
#案例1：查询哪个部门的员工个数>5
#①查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
#② 筛选刚才①结果，查询那个部门的员工数>5：需要使用 HAVING
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;
 
#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
 
#案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
manager_id>102
SELECT manager_id,MIN(salary)
FROM employees
GROUP BY manager_id
HAVING MIN(salary)>5000;
 
 
#4.添加排序
#案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;
 
 
#5.按多个字段分组，且可以配合排序使用
#案例：查询每个工种每个部门的最低工资,并按最低工资降序
SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id  --顺序不影响
ORDER BY MIN(salary) DESC;

#1.查询各job_id的员工工资的最大值、最小值、平均值、总和，并按job_id升序排序
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id ASC;
 
#2.查询最低工资和最高工资的差距（DIFFERENCE）
SELECT MAX(salary)-MIN(salary) AS DIFFERENCE
FROM employees;
 
#3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理员工的不能计算在内
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;
 
#4.查询所有部门的编号，员工数量和工资平均值，并按平均工资排序
SELECT department_id,COUNT(*),AVG(salary) AS a
FROM employees
GROUP BY department_id
ORDER BY a  DESC;
 
#5.选择具有各个job_id的员工人数
SELECT job_id, COUNT(*) 个数
FROM employees
GROUP BY job_id;

6. 连接查询

# 1.连接查询的含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
    SELECT name,boy_name FROM beauty,boys;
    笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
    发生原因：没有有效的连接条件
    如何避免：添加有效的连接条件
 
# 2.分类：
	# 按年代分类：
	sql92标准:仅仅支持内连接
	sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
	
	# 按功能分类：
		内连接：
			等值连接
			非等值连接
			自连接
		外连接：
			左外连接
			右外连接
			全外连接
		
		交叉连接
 
# 3. SQL92语法
	# 等值连接
	select 查询列表
	from 表1 别名,表2 别名
	where 表1.key=表2.key
	【and 筛选条件】
    【and 筛选条件2】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】
 
	① 一般为表起别名
	② 多表的顺序可以调换
	③ n表连接至少需要n-1个连接条件
	④ 等值连接的结果是多表的交集部分
 
#案例1：查询女神名对应的男神名
SELECT name, boyname
FROM beauty, boys
WHERE beauty. boyfriend_id = boys.id;
#案例2：查询员工名和对应的部门名
SELECT last_name, department_id
FROM employees, departments
WHERE employees.'department_id'=departments.'department_id';
#案例3：查询员工名，工种号，工种名
SELECT last_name, employees.job_id,job_title  --必须限定是employees.job_id还是jobs.job_id
FROM employees,jobs
WHERE employees.job_id = jobs.job_id;
--此时也可以不限定job_id,为表起别名
SELECT e.last_name, e.job_id,j.job_title  --必须使用别名，有别名之后，查询字段不能用原来的名
FROM employees AS e,jobs AS j
WHERE e.job_id = j.job_id;
 
    #可以加筛选？ 
#案例1：查询有奖金的员工名、部门名
SELECT last_name, department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id
AND e.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%";
 
    #可以加分组？
#案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数, city
FROM departments d, locations l
WHERE d.location_id = l.location_id 
GROUP BY l.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 d.department_id, d.manager_id;  --不确定manager_id和department_id是否一一对应，所以将这两个都作为分组条件。
 
 
    #可不可以加排序？
#案例1：查询每个工种的工种名和员工的个数，并且按员工个数排序
SELECT job_title, COUNT(*)
FROM jobs j, employees e
WHERE j.job_id=e.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;
 
    #是否可以实现三表连接？
#查询员工名、部门名和所在城市
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%";  也可以再加筛选分组排序
 
 
 
 
	# 非等值连接
	select 查询列表
	from 表1 别名,表2 别名
	where 非等值的连接条件
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】
 
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;
--再加一个筛选 AND g.grade_level="A";
 
 
 
	# 自连接:相当于等值连接，但是是自己连接自己
 
	select 查询列表
	from 表 别名1,表 别名2
	where 等值的连接条件
	【and 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选】
	【order by 排序字段】 
#案例1：查询员工名和上级的名称（查某个员工的领导编号，再根据领导编号在员工表里查编号）
SELECT e.employee_id , e.last_name, m.employee_id, m.last_name --员工id，员工名，领导id，领导名
FROM employees e, employees m  --e是员工表，m是领导表 
WHERE e.manager_id=m.employee_id;
 

#1.显示员工表的最大工资、平均工资
SELECT MAX(salary),AVG(salary)
FROM employees;
#2.查询员工表的employee_id,job_id,last_name,按department_id降序，salary升序
SELECT employee_id,job_id,last_name
FROM employees
ORDER BY department_id DESC,salary ASC;
#3.查询员工表的job_id中包含a和e的，并且a在e前面
SELECT job_id
FROM employees
WHERE job_id LIKE "%a%e%";
#4.要求查询姓名、年级名、成绩
SELECT s.name,g.name,score
FROM student s,grade g,result r
WHERE s.id=r.student 
AND g.id=s.gradeid;
#5.显示当前日期，以及去前后空格，截取子字符串的函数
SELECT now();
SELECT trim(' '  from '  sadjf    ');
SELECT substr(str,startIndex,length);

SQL99语法 

 
# 4.SQL99语法
    # 内连接
    - 语法：
    select 查询列表
    from 表1 别名
    【inner】 join 表2 别名 on 连接条件
    where 筛选条件
    group by 分组列表
    having 分组后的筛选
    order by 排序列表
    limit 子句;
 
    - 特点：
    ① 表的顺序可以调换
    ② 内连接的结果=多表的交集
    ③ n表连接至少需要n-1个连接条件
 
    - 分类：
    等值连接
    非等值连接
    自连接
 
#1.（一）等值连接
- 案例1：查询员工名、部门名（调换位置）
SELECT  last_name,department_name
FROM employees e
INNER JOIN department d
ON e.department_id =d.department_id;
- 案例2：查询名字中包含e的员工名和工种名
SELECT last_name,job_id
FROM employees e
INNER JOIN deaprtment d
on e.job_id=j.job_id
WHERE e.last_name LIKE "%e%";
# 案例3：查询部门个数>3的城市名和部门个数（筛选+分组）
SELECT city,COUNT(*) 个数
FROM employees e
INNER JOIN locations l
ON e.department_id = l.location_id
GROUP BY city
HAVING COUNT(*)>3;
# 案例4：查询哪个部门的部门员工个数>3的部门名 和 员工个数，并按个数降序
SELECT COUNT(*) 个数,department_name
FROM employees e 
INNER JOIN departments d
ON e.department_id=d.department_id
ORDER 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;
 
 
 
 
# (二)非等值连接
# 案例1：查询员工的工资级别
SELECT salary,grade_lavel
FROM employees e 
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;
# 案例2： 查询工资级别的个数>20的个数，并按工资级别降序
SELECT  grade_level,COUNT(*) 
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest AND g.highest
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
 
 
# (三)自连接
#案例1：查询员工的名字和上级的名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id;

    # 二、外连接
#用于查询一个表中有，一个表中没有的情况  
#主表中所有内容：两个表的交集(内连)+主表中有而附表中没有的部分(NULL)
 
- 特点：
    ① 查询的结果为主表中所有的行(记录)，
        如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null
    外连接查询结果=内连接结果+主表中有而从表中没有的记录
    ② left join  左边的就是主表
      right join 右边的就是主表
      full join  两边都是主表
    ③ 一般用于查询除了交集 部分的剩余的不匹配的行
 
    - 语法：
    select 查询列表
    from 表1 别名
    left|right|full【outer】 join 表2 别名 on 连接条件
    where 筛选条件
    group by 分组列表
    having 分组后的筛选
    order by 排序列表
    limit 子句;
 
#引入：查询男朋友不在男神表中的女神名
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo  
ON b.boyfrien_id = bo.id
WHERE bo.id IS NULL;
SELECT b.name
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.boyfrien_id = bo.id
WHERE bo.id IS NULL;
# 查询每个部门没有员工？
#左外
SELECT department
FROM departmnet d
LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;
# 右外
SELECT department
FROM employees e
RIGHT OUTER JOIN department d
ON e.department_id=d.department_id
WHERE e.employee_id IS NULL;
 
#全外连接
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id=bo.id;
--全外连接：交集部分(内连接结果)+beauty中有boys中没有的(LEFT外连接) + boys中有beautys没有的(RIGHT外连接)
 

#1. 查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有用NULL填充
SELECT b.id, b.name, bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE b.id > 3; 
#2. 查询哪个城市没有部门
SELECT city,d.* 
FROM locations l
LEFT OUTER JOIN departments d
ON l.location_id =d.location_id
WHERE d.department_id IS NULL;
#3. 查询部门名为SAL或者IT的员工信息
--部门中也有可能没有员工，因此内连接查不全，用外连接
SELECT e.*，d.department_name
FROM employees e
RIGHT OUTER JOIN department d   --部门必须全查出来，但是员工可能没有
ON e.department_id=d.department_id
WHERE d.department_name IN('SAL','IT');

7. 子查询

# 含义：
# 子查询或内查询：出现在其他语句中的SLELCT语句。增删改查
# 主查询或者外查询：内部嵌套其他select语句的查询
 
# 按子查询出现的位置：
            select后面：
                    仅仅支持标量子查询
            from后面：
                    支持表子查询
            where或having后面：★
                    标量子查询（单行）  √
                    列子查询（多行）    √
                    行子查询
            exists后面(相关子查询)：
                    表子查询
# 按结果集的行列数不同：
        标量子查询（结果集只有一行一列）
        列子查询（结果集只有一列多行）
        行子查询（结果集可以有一行多列）
        表子查询（结果集一般为多行多列）
 

一、WHERE和HAVING后面的子查询

# 一、WHERE和HAVING后面的子查询
# 1. 标量子查询（单行子查询）
# 2. 列子查询（多行子查询）
# 3. 行子查询（多列多行）
 
# 特点：
# ①：子查询放在小括号内
# ②：子查询一般放在条件右侧
# ③：标量子查询：一般搭配单行操作符使用。
# > < >= <= = <>
 
# 列子查询（多行）：一般搭配多行操作符使用
# IN、ANY/SOME、ALL
 
# ④：子查询执行优先于主查询执行。
 
#案例1：谁的工资比Abel高?
# ① 查询Abel的工资
SELECT salary 
FROM employees 
WHERE last_name = "Abel"
# ② 查询谁的工资大于它
SELECT *
FROM employees e
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号员工的salary
SELECT salary
FROM employees 
WHERE employee_id = 143;
# 查询员工的姓名，job_id和工资，要求job_id=①，salary>②
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;
# ② 查询last_name,job_id和salary，要求salsry=①
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) min_salary,department
FROM employees
GROUP BY department_id;
# ③ 筛选②，满足min_salary > ①
SELECT department_id，MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > ( SELECT MIN(salary) min_salary
FROM employees
WHERE department_id=50 );
 
 
# 非法使用标量子查询
1、子查询查出来的结果不是标量，不是一行一列

# 2. 列子查询（多行子查询，一列多行）

--NOT IN 和 <>ALL
--IN 和 = ANY
# 案例1：返回location_id 是1400或1700的部门中的所有员工姓名 
# ① 查询location_id是1400或1700的部门编号
SELECT department_id
FROM departments
WHERE location_id IN (1400,1700);
# ② 查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id IN (1400,1700) 
                        );
--或
SELECT last_name
FROM employees
WHERE department_id =ANY (
SELECT department_id
FROM departments
WHERE location_id IN (1400,1700) 
                        );
 
 
 
#案例2：返回其它工种中比job_id为“IT_PROG”工种任一工资低的员工的员工号、姓名、job_id以及salary
# ① job_id为IT_PROG部门的工资列表 ⭐⭐
SELECT DISTINCT salary
FROM employees
WHERE job_id = “IT_PROG”;
# ② 其他部门中的员工的信息，其工资大于列表①中的任一元素
SElECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (
SELECT DISTINCT salary
FROM employees
WHERE job_id = “IT_PROG”
                    ) AND job_id <> "IT_PROG" ;
--或
SElECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < (
SELECT  MAX(salary)
FROM employees
WHERE job_id = “IT_PROG”
                    ) AND job_id <> "IT_PROG" ;
 
#案例3：返回其它工种中比job_id为“IT_PROG”工种所有工资低的员工的员工号、姓名、job_id以及salary
# ① job_id为IT_PROG部门的工资列表
SELECT DISTINCT salary
FROM employees
WHERE job_id = “IT_PROG”;
# ② 其他部门中的员工的信息，其工资大于列表①中的任一元素
--或
SElECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < (
SELECT MIN(salary)
FROM employees
WHERE job_id = “IT_PROG”
                    ) AND job_id <> "IT_PROG" ;

3. 行子查询（一行多列，多行多列）
 
#案例：查询员工编号最小并且工资最高的员工信息⭐⭐
--具备这样条件的员工不一定存在
# 原来方法：
# ① 查询最小员工编号
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
);
 
 
# 行子查询：多个条件使用一样的操作符好
SELECT *
FROM employees
WHERE (employee_id, salary) = (
SELECT MIN(employee_id),MAX(salary)
FROM employees
)

二、SELECT后面

# SELECT后面放一行 一列 ！！
 
# 案例1：查每个部门的员工个数（在部门表中加一 个数 列）
SELECT d.*, (
 
        SELECT COUNT(*)
        FROM employees
        WHERE e.department_id = d.department_id
        
)
FROM departments d;
 
#案例2：查询员工号=102的部门名
SELECT (
 
SELECT d.department_name    --只能查一个属性：一列
FROM departments d
INNER JOIN employees e
ON e.departmnt_id = d.department_id
WHERE e.employee_id=102
) 部门名 ;

三、FROM后面

# FROM后面的子查询： 结果集是一个表
# 将子查询结果充当一张表，要求必须取别名
 
# 案例1：查询每个部门的平均工资的工资等级 ⭐⭐
# ① 查询每个部门的平均工资————得到了平均工资表ag_dep
SELECT AVG(salary) avg(salary) , department_id
FROM employees e
GROUP BY department_id;
 
# ② 根据登记表进行工资等级判断————表的连接:ag_dep和job_grades表
SELECT ag_dep.*, j.grade_lavel salary_level
FROM (
    SELECT AVG(salary) ag , department_id
    FROM employees e
    GROUP BY department_id
) ag_dep
INNER JOIN job_grades j
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal; 
 

四、EXISTS后面的子查询（相关子查询）

# 语法：
EXIST(完整的查询语句)
结果： 1或者0
 
SELECT EXISTS( SELECT employee_id FROM employees WHERE salary=300000);  #结果为0
 
# 案例1：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
        SELECT *
        FROM employees e 
        WHERE d.department_id = e.department_id   --满足这个条件的有没有
)
--或
# IN方法
SELECT department_name
FROM departments d
WHERE d.department_id IN (
        SELECT department_id
        FROM employees
)
 
# 查询没有女朋友的男神信息 ⭐⭐
SELECT *
FROM boys bo 
WHERE NOT EXISTS(
        SELECT boyfroend_id
        FROM beauty b
        WHERE bo.id = b.boyfriend_id
);
 
#IN方法
SELECT * 
FROM boys
WHERE boys.id NOT IN (
    SEKECT boyfriend_id
    FROM beauty 
);

--标量子查询
#1.查询和zlotkey相同部门的员工姓名和工资
SELECT last_name, salary
FROM employees 
WHERE department_id = (
    SELECT department_id 
    FROM employees 
    WHERE last_name ='zlotkey'
);
#2.查询工资比公司平均工资高的员工的员工号，姓名，工资 
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees
);

 
#多行多列子查询——from后面
#3.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资 ⭐⭐
SELECT employee_id, last_name, salary
FROM employees e
INNER JOIN (
    SELECT AVG(salary) ag_sal, department_id
    FROM employees
    GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE e.salary > ag_dep.sg_sal;

# 一列多行用 IN
#4.查询 和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 ⭐⭐
SELECT employee_id, last_name, department_id
FROM employees e
WHERE department_id IN( 
    SELECT DISTINCT department_id
    FROM employees
    WHERE last_name LIKE "%u%"
);

#5.查询在部门的location_id为1700的部门工作的员工的员工号。 ⭐⭐
SELECT employee_id
FROM employees
WHERE department_id = ANY(   -- =ANY可以换成IN
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id =1700
);

# 一列多行
#6.查询管理者为King的员工姓名和工资  ⭐⭐
SELECT last_name, salary
FROM employees
WHERE manager_id IN (
    SELECT employee_id
    FROM employees
    WHERE last_name="King"   --管理者King的员工号
) 

#7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓名⭐⭐
SELECT CONCAT(first_name, last_name) "姓.名"
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

8. 分页查询

# 应用场景
    当要显示的数据，一页显示不全，需要分页提交SQL请求
# 语法：
    select 查询列表
    from 表
    【join type】 join 表2
    on 连接条件
    where 筛选条件
    group by 分组字段
    having 分组后筛选
    order by 排序的字段
    limit offset,size;   --offset要显示条目的起始索引（起始索引从0开始）；size表示要显示的条目个数
 
#案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
#案例2：查询第11条-25条
SELECT * FROM employees LIMIT 10,15;
#案例3：查询有奖金的员工信息，并且工资较高的前十名
SELECT *
FROM employees
WHERE  commissiom_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;
 
    #特点
        ① LIMIT语句要放在查询语句的最后
        ② 公式：
            要显示的页数 page，每页的条目数是size
 
            SELECT 查询列表
            FROM 表
            LIMIT (page-1)*size,size；  --每一页的起始页数
 
size=10
page    (page-1)*size
1            0
2            10
3            20

已知表 stuinfo
id 学号
name 姓名
email 邮箱   john@126.com
gradeID 年级编号
sex 性别   男 女
age  年龄
 
已知表 grade
id  年纪编号
gradeName  年纪名称
 
#案例1：查询所有学员的邮箱的用户名（邮箱中@前面的字符）   SUBSTR函数、INSTR函数
SELECT SUBSTR(email,1,INSTR(email,'@')-1)
FROM stuinfo;
#案例2：查询男生和女生的个数
SELECT COUNT(*) 个数,sex
FROM stuinfo
GROUP BY sex;
#案例3：查询年龄>18岁的所有学生的姓名和年纪名称
SELECT name,gradeName
FROM stuinfo s
INNER JOIN grade g 
ON s.gradeID = g.id
WHERE age>18;
#案例4：查询哪个年纪的学生最小年龄>20岁：首先查询每个年级的最小年龄
SELECT min(age),gradeID 
FROM stuinfo
GROUP BY gradeID 
HAVING min(age)>20;
#案例5：试说出查询语句中涉及到的所有关键字，以及执行先后顺序
SELECT 查询列表      7
FROM 表              1
连接类型 JOIN 表2    2
ON 连接条件          3
WHERE 筛选条件       4
GROUP BY 分组列表    5
HAVING 分组后筛选    6
ORDER BY 排序列表    8
LIMIT 偏移，条目数   9

#示例
#1. 标量子查询
#案例：查询最低工资的员工姓名和工资
#① 最低工资
SELECT min(salary) FROM employees
#② 查询员工的姓名和工资，要求工资等于①
SELECT last_name, salary
FROM employees 
WHERE salary = (
    SELECT min(salary) FROM employees
);
 
#2.列子查询
#案例：查询所有时领导的员工姓名
① 查询所有员工的manager_id 
SELECT manager_id 
FROM employees;
#② 查询领导对应的名字
SELECT last_name
FROM employees
WHERE employee_id IN (
    SELECT manager_id 
    FROM employees
);

 下面第3、8题。

#1. 查询工资最低的员工信息：last_name, salary
SELECT last_name, salary
FROM  employees
WHERE salary = (
        SELECT min(salary) FROM employees;
);
 
#2. 查询平均工资最低的部门信息  
方法一：
① 各部门平均工资
SELECT AVG(salary) , department_id
        FROM employees
        GROUP BY department_id；  
② 最低平均工资；
SELECT min(AVG(salary))
FROM (
        SELECT AVG(salary) , department_id
        FROM employees
        GROUP BY department_id  
) ag_dep;
③ 查询哪个部门的平均工资=② 
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
        SELECT min(AVG(salary))
        FROM (
                SELECT AVG(salary) , department_id
                FROM employees
                GROUP BY department_id  
        ) ag_dep;
);
④ 查询部门信息
SELECT d.*
FROM departments
WHERE d.department_id =
(
        SELECT  department_id
        FROM employees
        GROUP BY department_id
        HAVING AVG(salary) = (
                    SELECT min(AVG(salary))
                FROM (
                        SELECT AVG(salary) , department_id
                        FROM employees
                        GROUP BY department_id  
                      ) ag_dep;
);
 
--方式二：
① 各部门平均工资
SELECT AVG(salary) , department_id
        FROM employees
        GROUP BY department_id；  
② 求出最低平均工资的部门编号
SELECT department_id
FROM employees 
GROUP BY department_id 
ORDER BY AVG(salary) ASC
LIMIT 1;
③ 查询部门信息
SELECT d.*
FROM departments d
WHERE department_id = (
    SELECT department_id
    FROM employees 
    GROUP BY department_id 
    ORDER BY AVG(salary) ASC
    LIMIT 1;
);
 
3. 查询平均工资最低的部门信息和该部门的平均工资
① 求每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
② 查询平均工资最低的部门编号和平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) ASC
LIMIT 1;
③ 查询部门信息和该部门的平均工资
SELECT d.*, ag
FROM departments d
INNER JOIN (
        SELECT AVG(salary) ag, department_id
        FROM employees
        GROUP BY department_id
        ORDER BY AVG(salary) ASC
        LIMIT 1
) ag_dep  
ON d.department_id=ag_dep.department_id;
 
#4. 查询平均工资最高的job信息
#① 每个job的平均工资
SELECT AVG(salary), job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1;
#② 查询job信息
SELECT *
FROM jobs
WHERE jobs.job_id=(
        SELECT job_id
        FROM employees
        GROUP BY job_id
        ORDER BY AVG(salary) DESC
        LIMIT 1
);
--如果需要给出平均工资最高的job信息和平均最高工资，需要用来连接
SELECT j.*，AVG(salary)
FROM jobs 
INNER JOIN (
        SELECT AVG(salary), job_id
        FROM employees
        GROUP BY job_id
        ORDER BY AVG(salary) DESC
        LIMIT 1
) ag_job
ON jobs.job_id=ag_job.job_id;
 
#5. 查询平均工资高于公司平均工资的部门有哪些？
#① 先查公司平均工资
SELECT AVG(salary)
FROM employees;
#② 查每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;
#③ 平均工资 > ①的部门
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
WHERE AVG(salary) > (
        SELECT AVG(salary)
        FROM employees
);
 
#6. 查询出公司中所有 manager 的详细信息
#① 查出所有manager的id
SELECT DISTINCT manager_id
FROM employees;
#② 根据manager_id查出manager的信息
SELECT * 
FROM employees
WHERE employee_id IN (   --或者  = ANY( )
        SELECT manager_id
        FROM employees
);
 
#7. 各部门中 最高工资中最低的那个部门的 最低工资时多少
#① 每个部门的最高工资
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id;
#② 所有部门的最高工资中最低的部门编号
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) ASC
LIMIT 1;
#③ 部门②中的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=(
        SELECT department_id
        FROM employees
        GROUP BY department_id
        ORDER BY MAX(salary) ASC
        LIMIT 1
);
 
#8. 查询平均工资最高的部门的manager的详细信息：lsat_name,department_id,email,salary
#① 查询平均工资最高的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1;
#② 将employees和departments表连接查询，筛选条件是①
SELECT lsat_name,department_id,email,salary
FROM employees e
INNER JOIN departments d
ON d.manager_id=e.employee_id
WHERE d.department_id=(
        SELECT department_id
        FROM employees
        GROUP BY department_id
        ORDER BY AVG(salary) DESC
        LIMIT 1
);

 第5\6\7\8题

#1. 查询每个专业的学生人数
SELECT majorid, COUNT(*)
FROM student
GROUP BY major ;
 
#2. 查询参加考试的学生中，每个学生的平均分，最高分
SELECT studentno, AVG(score), MAX(score)
FROM result
GROUP BY studentno;
 
#3. 查询姓张的每个学生的最低分大于60的学号、姓名
SELECT s.studentno, s.studentname, MIN(score)
FROM result r
INNER JOIN student s
ON r.studentno=s.studentno
WHERE s.studentname LIKE "张%"
GROUP BY studentno
HAVING MIN(score) > 60;
 
#4. 查询生日在“1988-1-1”后的学生的姓名、专业名称
SELECT studentname, loginpwd, majorname
FROM student s
JOIN major m ON s.majorid=m.majorid
JOIN result r ON s.studentno=r.studentno
WHERE r.score>60;
 
SELECT s.studentname, m.majorname
FROM student s
INNER JOIN major m
ON s.majorid=m.majorid
WHERE DATEDIFF(borndate,"1988-1-1") > 0;  --如果borndate大于后面的日期就返回正数
 
#5. 查询每个专业的男生人数和女生人数分别是多少
SELECT majorid, 
(SELECT COUNT(*) FROM student WHERE sex="男" AND majorid=s.majorid ) 男,
(SELECT COUNT(*) FROM student WHERE sex="女" AND majorid=s.majorid ) 女
FROM student s
GROUP BY majorid;
 
#6. 查询专业和张翠山一样的学生的最低分
SELECT majorid
                FROM student
                WHERE studentname = "张翠山";
 
SELECT studentno FROM student WHERE majorid=(
        SELECT majorid
        FROM student
        WHERE studentname = "张翠山"
);
 
SELECT MIN(score)
FROM result
WHERE studentno IN (
        SELECT studentno 
        FROM student 
        WHERE majorid=(
                SELECT majorid
                FROM student
                WHERE studentname = "张翠山"
        )
);
--或者
SELECT MIN(score)
FROM result r 
INNER JOIN student s 
ON r.studentno=s.studentno
WHERE s.majorid=(
        SELECT majorid
        FROM student
        WHERE studentname = "张翠山"
);
 
#7. 查询大于60分的学生的姓名，密码，专业名
SELECT studentname, loginpwd, majorid
FROM student 
INNER JOIN result
ON student.studentno=result.studentno
WHERE score > 60;
--或者
SELECT studentname, loginpwd, majorname
FROM (
        SELECT studentname, loginpwd, majorid
        FROM student 
        INNER JOIN result
        ON student.studentno=result.studentno
        WHERE score > 60
      ) ss
INNER JOIN major m
ON ss.majorid=m.majorid;
 
#8. 按邮箱位数分组，查询每组学生的个数
SELECT COUNT(*), LENGTH(email)
FROM student
GROUP BY LENGTH(email);
 
#9. 查询学生名，专业名，分数
SELECT studentname, score, majorid
FROM student s
JOIN result r ON s.studentno= r.studentno
JOIN major m ON s.majorid= m.majorid;
 
#10. 查询那个专业(主表)没有学生，分别用左连接和右连接实现
SELECT DISTINCT m.majorid, m.majorname, 
FROM major m
LEFT JOIN student s
ON m.majorid=s.majorid
WHERE s.studentno IS NULL;
--右连接
SELECT DISTINCT majirid
FROM student s
RIGHT JOIN major m
ON s.majorid=m.majorid
WHERE studentno IS NULL;
 
#11. 查询没有成绩的学生（主表）人数
SELECT COUNT(*)
FROM student s
JOIN result r
ON s.studentno=r.studentno
WHERE r.score = NULL;  

9. 联合查询（尚硅谷-124节）

# 1. 联合查询 UNION : 将多条查询语句的结果合并成一个结果
查询语句1
union 【all】
查询语句2
union 【all】
...
 
# 2. 应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的列一致时
 
# 3. 特点：★
3.1 要求多条查询语句的查询列数是一致的！
3.2 要求多条查询语句的查询的每一列的类型和顺序最好一致
3.3 union关键字默认去重，如果使用union all 可以包含重复项
 
# 4.案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;;
SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;
 
# 案例：查询中国用户中男性的信息以及外国用户中男性的信息(两个表中没有连接关系)
SELECT id, cname, csex FROM t_ca WHERE csex="男";
UNION
SELECT id, tname, tgender FROM t_ua WHERE tgender="male";

# 语法以及执行顺序
select 查询列表    ⑦
from 表1 别名       ①
连接类型 join 表2   ②
on 连接条件         ③
where 筛选          ④
group by 分组列表   ⑤
having 筛选         ⑥
order by排序列表    ⑧
limit 起始条目索引，条目数;  ⑨

 （三）DML语言

数据操作语言——对数据的操作

        插入：insert

        修改：update

        删除：delete

 3.1 插入语句

# 表已经存在，需要在表中插入信息（表名、列名、新值） 
 
# 语法：
INSERT INTO  表名(列名，...)  values(值1,...)；
# 特点
1、要求值得类型和字段得类型(列类型)一致或兼容
2、字段个数和顺序不一定要与原始表中得字段个数和顺序一致，但必须保证值和字段一一对应
3、假如表中有可以为NULL的字段，注意可以通过以下两种方式插入NULL值
    ①字段和值都省略
    ②字段写上，只是用null
4、字段和值得个数必须一致
5、字段名可以省略，默认所有列
 
# 1. 插入的值得类型要与列得类型一致或兼容
INSERT INTO beauty(id, NAME, sex, borndate, phone, photo,boyfriend_id) 
VALUES(13,"唐艺昕","女","1990-04-23","18988888888",NULL,2);   
--照片类型暂时不能插入，用NULL代替
# 2. 不可以为NULL的列必须插入值，可以为NULL的列如何插入值？ 
--方式一：列名写着，值用NULL填充
INSERT INTO beauty(id, NAME, sex, borndate, phone, photo,boyfriend_id) 
VALUES(13,"唐艺昕","女","1990-04-23","18988888888",NULL,2);  
--方式二：列名和值都省略
INSERT INTO beauty(id, NAME, sex, phone) 
VALUES(14,"金星","女", "13288888888");  
# 3. 列的顺序是否可以调换？  可以
INSERT INTO beauty(sex, phone, id, NAME)
VALUES("女", "13288888888", 14,"金星");  
# 4. 列数和值的个数必须一致。
INSERT INTO beauty(sex, phone, id, NAME, friend_id)
VALUES("女", "13288888888", 14,"金星");  
# 5. 可以省略列名，默认是所有列名，而且列名的顺序和表中的列顺序一致。
INSERT INTO beauty
VALUES(18, '张飞', '男', NULL, '119', NULL, NULL);
 
--方式二
#语法
INSERT INTO 表名 SET 列名=值，列名=值，...
 
#案例
INSERT INTO beauty
SET id=10, '刘涛', phone='999';  --出生日期有默认值，可以不写
 
 
# 两种方式大PK————插入多行、子查询
1、 方式一支持插入多行，方式二不支持
--INSERT INTO 表名【(字段名...)】 VALUES(值,...)
INSERT INTO beauty
VALUES (13,"唐艺昕","女","1990-04-23","18988888888",NULL,2)
, (13,"唐艺昕","女","1990-04-23","18988888888",NULL,2)
, (13,"唐艺昕","女","1990-04-23","18988888888",NULL,2);
 
2、方式一还支持子查询，方式二不支持
--INSERT INTO 表名 
INSERT INTO beauty(id, NAME, phone)
SELECT id, boyname, '19897376';
FROM friend WHERE name="宋茜";

 3.2 修改语句

# 1、修改单表的记录 ⭐
# 语法
UPDATE 表名
SET 列=新值，列=新值，...   【修改多列】【不加where条件的话，就是所有行都修改】
WHERE 筛选条件;    
 
2、修改多表的记录【补充】
# 语法：sql92（内连）
UPDATE 表1 别名，表2 别名
SET 列=值，...
WHERE 连接条件
and筛选条件
 
#语法：sql99
UPDATE 表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名
ON 连接条件
SET 列=值,...
WHERE 筛选条件;
 
#案例1:beauty表中 姓唐的女神的电话为232323
UPDATE beauty SET phone='232323'
WHERE name LIKE '唐%';
#案例2：修改boys表中id为2的名称为张飞， 魅力值为10
UPDATE boys SET boyname='张飞', usercp=10
WHERE id=2;
 
#案例1：修改张无忌的女朋友的手机号为114
UPDATE boys bo
INNER JOIN beauty b ON bo.id=b.boyfriend_id
SET b.phone='114'
WHERE bo.boyname='张无忌';
#案例2：修改没有男朋友的女神的男朋友的id都为2号
UPDATE boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
SET b.boyfriend_id=2
WHERE bo.id IS NULL;

3.3 删除语句 

# 方法一：DELETE 只能删除整行
# 语法：
 
1、单表的删除 ⭐
DELETE FROM 表名 WHERE 筛选条件 【LIMIT 条目数】
 
2、多表的删除【补充】
--sql92
DELETE 要删除表的别名
FROM 表1 别名，表2 别名
WHERE 连接条件
and 筛选条件; 
 
--sql99
DELETE 要删除表的别名, 要删除表的别名
FROM  表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名
ON 连接条件
WHERE 筛选条件;
 
# 方式二：TRUNCATE
# 语法：TRUNCATE TABLE 表名；
 
# 方式一案例：delete
# 1. 删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';
# 2. 多表的删除：删除张无忌的女朋友的信息(女朋友是存在的，可以用内连接)
DELETE b
FROM beauty b
INNER JOIN boys bo ON bo.id=b.boyfriend_id
WHERE bo.boyName = '张无忌';
# 3. 删除黄晓明的信息以及女朋友的信息
DELETE b, bo
FROM beauty b
INNER JOIN boys bo ON b.id=bo.boyfroiend
WHERE bo.boyname='黄晓明';    --【一共删除了两行，重新打开表看】
# 4.删除第一条信息 OR 删除某一条信息
DELETE FROM beauty LIMIT 1;
DELETE FROM beauty WHERE boyfriend_id=4 LIMIT 1;
 
# 方式二：truncate语句：清空数据表，删除多行
TRUNCATE TABLE boys;  将boys表中的数据都删除
 
 
#                      DELETE PK TRUNCATE   ⭐面试题
1.  delete可以加where筛选条件，truncate不能加
2.  truncate删除，效率高一丢丢
3.  如果delete删除后要再插入数据，自增长列的值从断点开始，【删除了1-5的数据，再插入数据，从6开始增加】
    而truncate删除后，再插入数据，自增长列的值从1开始。
4.  truncate删除没有返回值，delete删除可以返回值影响的行数
5.  truncate删除不能回滚，delete删除可以回滚。

# 测试题
# 运行以下脚本创建表my_employees
USE myemployees;
CREATE TABLE my_employees(
        ID INT(10),
        First_name VARCHAR(10),
        Last_name VARCHAR(10),
        Userid VARCHAR(10),
        Salary DOUBLE(10,2)
);
CREATE TABLE users(
        id INT,
        userid VARCHAR(10),
        department_id INT
);
 
# 显示表my_employees的结构
DESC my_employees;
 
# 向my_employees表中插入下列数据(一次性多行数据)
1    patel    Ralph    Rpatel    895
2    Dancs    Betty    Bdancs    860
3    Biri     Ben      Bbiri     1100 
4    Newman   Chad     Cnewman   750
5    Ropeburn   Audrey Aropebur  1550
 
--方式一：
INSERT INTO my_employees
VALUES (1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
--方式二：用联合查询
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION
SELECT 2,'Dancs','Betty','Bdancs',860 UNION
SELECT 3,'Biri','Ben','Bbiri',1100 UNION
SELECT 4,'Newman','Chad','Cnewman',750 UNION
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;
 
#向users表中插入数据
INSERT INTO users
VALUES(1,'Rpatel',10'),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40);
 

# 将3号员工的last_name修改为“drelxer”
UPDATE my_employees SET last_name='drelxer' WHERE id=3;
 
# 将所有工资少于900的员工的工资修改为1000
UPDATE my_employees SET salary=1000 WHERE salary<900;
 
# 将userid为Bbiri的user表和my_employees表的记录全部删除
DELETE u, m
FROM users u
INNER JOIN my_employees m on u.userid=m.Userid
WHERE u.userid='Bbiri';
 
# 删除所有数据
DELETE FROM my_employees;
DELETE FROM users;
 
# 检查所作的修正；
SELECT * FROM my_employees;
SELECT * FROM users;
 
# 清空表my_employees
TRUNCATE TABLE my_employees;

(四)、DDL语言

4.1 数据定义语言——对库和表的管理

一、库的管理

        创建、修改、删除

二、表的管理

        创建、修改、删除

创建：CREATE

修改：库和表的结构 ALTER

删除：DROP

4.1.1 库的管理

# 1、库的创建 CREATE
# 语法：
/*
CREATE DATABASE [IF NOT EXISTS] 库名 【CHARACTER SET 字符集名】；
*/
# 案例：创建库books
CREATE DATABASE IF NOT EXISTS books；
 
# 2、库的修改 ALTER
# 一般来说，库不进行修改，否则容易出现问题/错误；库名一般也不修改
# 以前有RENAME DATABASE books TO 新库名；不安全，现在不可以用
# 要想进行【库名的修改】，在文件夹中重命名
# 可以更改库的字符集 
    ALTER DATABASE books CHARACTER SET gbk;  [utf8 to gbk]
 
# 3、库的删除 DROP
/*
DROP DATABASE IF EXISTS books;
*/

4.1.2 表的管理

# 1、表的创建 ⭐
# 语法：
/*
CREATE TABLE [IF NOT EXISTS] 表名(
        列名 列的类型【(长度) 约束】,
        列名 列的类型【(长度) 约束】,
        ......
        列名 列的类型【(长度) 约束】
);
*/
 
# 案例：创建表Books
CREATE TABLE IF NOT EXISTS Books(
        id INT,    #编号
        b_name VARCHAR(20),    #书名（最大长度）
        price DOUBLE,    #价格
        author_id INT,    #作者
        publishDate DATETIME    #出版日期
)
DESC Books
# 创建authors表
CREATE TABLE authors(
        id INT,
        au_name VARCHAR(20),
        nation VARCHAR(10)
);
DESC authors;
 
# 2、表的修改
/*
ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE|RENAME TO|  COLUMN 列名 列类型 约束；
*/
#（1）修改列名
--ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;
-- 将Books中的publishDate列修改为
ALTER TABLE Books CHANGE COLUMN publishDate pubDate DATETIME;  --顺便修改类型
（2）修改列的类型或约束[默认值，长度]
--ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 【新约束】;
ALTER TABLE Books MODIFY COLUMN pubDate TIMESTAMP;
（3）添加列
--ALTER TABLE 表名 ADD COLUMN 列名 类型 【first|after 字段名】;
# 在authors表中列annual列
ALTER TABLE authors ADD COLUMN annual DOUBLE ;
（4）删除列
--ALTER TABLE 表名 DROP COLUMN 列名;
ALTER TABLE authors DROP COLUMN annual;
（5）修改表名
--ALTER TABLE 表名 RENAME 【TO】 新表名
ALTER TABLE authors RENAME TO book_author;
 
# 3、表的删除
/*
DROP TABLE 【IF EXISTS】book_author;
SHOW TABLES;   --查看当前库中的所有表
*/
 
# 4、表的复制
INSERT INTO authors VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
 
#1. 仅复制表的结构：复制列名和列类型，约束
--CREATE TABLE 表名 LIKE 旧表;
CREATE TABLE copy LIKE authors;   --authors中有数据，从copy中没有数据
#2. 复制表的结构+数据
--CREATE TABLE 表名 SELECT 查询列表 FROM 旧表 【WHERE 筛选条件】;
CREATE TABLE copy2
SELECT * FROM authors；    --从authors中复制结构和数据到copy2中
#3. 复制部分数据
--CREATE TABLE 表名 SELECT 查询列表 FROM 旧表 WHERE 筛选条件;
CREATE TABLE copy3
SELECT id,au_Name
FROM authors
WHERE nation='中国';
#4. 仅仅复制某些字段（部分结构）
--CREATE TABLE 表名 SELECT 查询列表 FROM 旧表 WHERE 不成立得条件/0;
# 复制authors中的id，au_name，但是没有任何数据
CREATE TABLE copy4 
SELECT id,au_Name
FROM author
WHERE 1=2;      --筛选条件都不成立，则没有任何数据。或者 筛选条件为0.

# 通用的写法： 为了创建自己的库和表，不确定之前有没有同名库和表
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名；
 
DROP TABLE IF EXISTS 旧库名;
CREATE TABLE 新表名();

# 1. 创建表dept1【在teat库中】
NAME        NULL？        TYPE
ID                        INT(7)
NAME                      VARCHAR(25)
 
USE test;
 
CREATE TABLE dept1(
        id INT(7),
        NAME VARCHAR(25)
);
# 2. 将表departments[myemployees库中]中的数据插入新表dept2中。【跨库进行表的创建】
--dept2不存在，必须先创建
CREATE TABLE dept2
SELECT departments_id, department_name
FROM myemployees.departments;
# 3. 创建表emp5
CREATE TABLE emp5(
        id INT(7),
        First_name VARCHAR(25),
        Last_jname VARCHAR(25),
        Dept_id INT(7)
);
# 4. 将列Last_name的长度增加到50,即修改表的类型
ALTER TABLE emp5 MODIFY COLUMN Last_name VARCHAR(50);
# 5. 根据表employees[myemployees库]创建employees2[test库]
CREATE TABLE employees2 LIKE myemployees.employees;
# 6. 删除表emp5
DROP TABLE IF EXISTS emp5;
# 7. 将表employees2重命名为emp5
ALTER TABLE employees2 RENAME TO emp5;
# 8. 在表dept和emp5中添加新列test_column,并检查所做的操作
ALTER TABLE emp5 ADD COLUMN test_column INT;
ALTER TABLE dept ADD COLUMN test_column INT;
# 9. 直接删除表emp5中的列test_column;
ALTER TABLE emp5 DROP COLUMN test_column;

4.2 数据类型

/*

数值型：

            整形

            小数：

                    定点数

                    浮点数

字符型：

             较短的文本：char、varchar

             较长的文本：text、blob(较长的二进制数据)

日期型：

*/

# 1、整型——Tinyint(1), Smallint(2)
/*
分类：
    tinyint、smallint、mediumint、int/integer、bigint
        1        2        3            4         5
特点：
①：可以设置无符号还是有符号，默认是有符号；UNSIGNED是无符号
②：如果插入的数值超出了范围，汇报out of range异常，并插入临界值
③：如果不设置长度，会有默认的长度；可以自己设置。 
   长度代表了最大宽度，如果不够会用0填充在左边，必须与ZEROFILL搭配使用  
*/
 
# 案例1：测试如何设置无符号（负数+正数）和无符号（不能是负数)
CREATE TABLE test_int(
    t1 INT(11) ZEROFILL              --有符号，可以为负数
    t2 INT(10) ZEROFILL UNSIGNED     --无符号
);
DESC tab_int;
 
INSERT INTO tab_int VALUES(-123456);
INSERT INTO tab_int VALUES(-123456,-123456);  --报错
INSERT INTO tab_int VALUES(214783648,4294967296); --报错，插入临界值int[]
INSERT INTO tab_int VALUES(123,123);  

# 二、浮点型
/*
浮点数： 
        4字节   float(M,D)
        8字节   double(M,D)
定点数：精确度更高
        DEC(M,D)
        DECIMAL(M,D)
*/
 
# 特点：
① M代表整数部位+小数部位个数
  D代表小数部位
②：M和D都可以省略
    如果是decimal，则(M,D)默认为(10,0)
    如果是float和double，则会随着插入的数值来决定精度
③：定点型的精确度较高，即如果要求插入数值的精度较高，如货币运算等则考虑使用decimal定点数
④：如果超出范围，则报out of range异常。
 
CREATE TABLE tab_float(
        f1 FLOAT(5,2),
        f2 DOUBLE(5,2),
        f3 DECIMAL(5,2)   
);
INSERT INTO tab_float VALUES(123.45,123.45,123.45);  --插入原数据
INSERT INTO tab_float VALUES(123.456,123.456,123.456);  --四舍五入有：插入123.46，123.46
INSERT INTO tab_float VALUES(123.4,123.4,123.4);    --插入123.40,123.40
INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4); --插入999.99
 
# 测试M和D
CREATE TABLE tab_float(
        f1 FLOAT,
        f2 DOUBLE,
        f3 DECIMAL  
);
INSERT INTO tab_float VALUES(123.45,123.45,123.45);  --f3异常，DECIMAL默认(10,0)
 
# 选择原则
/*
所选择的类型越简单越好，能保存数值的类型越小越好
*/

# 三、字符型

• 较短的文本：

        char(M) :固定长度的字符，M表示最多的字符数，省略M时默认为M=1
        varchar(M)：可变长度的字符

• 较长的文本：

        text
        blob（较大的二进制）

• 其他：

binary和varbinary用于保存较短的二进制
enum用于保存枚举，只能插入枚举出来的
set用于保存集合

CREATE TABLE tab_char(
    t1 ENUM('a','b','c','d')
 
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('e');  --报错！
 
SELECT * FROM tab_char;

CREATE TABLE tab_set(
    s1 SET('A','B','C','D')
 
);
 
INSERT INTO tab_set VALUES('A');
INSERT INTO tab_set VALUES('a,b');
INSERT INTO tab_set VALUES('A','C','D');
 
SELECT * FROM tab_set;

# 四、日期型

• 分类：
  date只保存日期
  time 只保存时间
  year只保存年

datetime保存日期+时间
timestamp保存日期+时间：比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间。

CREATE TABLE tab_date(
    t1 DATETIME,
    t2 TIMESTAMP
);
 
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;
--输出：2022-07-22 12:27:55    2022-07-22 12:27:55
 
SHOW VARIABLES LIKE 'time_zone';  --查看当前时区
SET time_zone='+9:00';    -- 更改时区
SELECT * FROM tab_date;
----输出：2022-07-22 12:27:55    2022-07-22 13:27:55

4.3 常见约束

含义：一种限制，用于限制表中的数据，为了保证表中数据的准确和可靠性。

CREATE TABLE 表名(
        字段名 字段类型 约束
);

一、分类：六大约束

• NOT NULL:非空，用于保证该字段的值不能为空，比如姓名、学号
• DEFAULT: 默认，用于保证该字段有默认值
• PRIMARY KEY: 主键，用于保证该字段的值具有唯一性且非空，比如学号、员工编号
• UNIQUE: 唯一，用于保证该字段的值具有唯一性，可以为null。比如座位号
• CHECK: 检查约束【mysql中不支持】
• FOREIGN  KEY: 外键，用于限制两个表的关系
  • 在从表添加外键约束，用于引用主表中某列的值。比如学生表的专业编号，员工表的部门编号，员工表的工种编号

添加约束的时机：

1. 创建表时
2. 修改表时

约束的添加分类：

1. 列级约束:
   1. 六大约束语法上都支持，(除了外键)
   2. 列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求
2. 表级约束
   1. 除了非空、默认，其他的都支持

一、创建表时添加约束 

CREATE TABLE 表名(
        字段名 字段类型 not null,
        字段名 字段类型 primary key,
        字段名 字段类型 unique,
        字段名 字段类型 default 值,
        constraint 约束名 foreign key(字段名) references 主表(被引用列)
);

# 1. 添加列级约束
/*
语法：直接在字段名和类型后面追加  约束类型即可。
只支持：默认、非空、主键、唯一
*/
CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo(
        id INT PRIMARY KEY, #主键:唯一且非空
        stuName VARCHAR(20) NOT NULL, #非空
        gender CHAR(1) CHECK(gender='男' OR =‘女’), #检查
        seat INT UNIQUE, #唯一
        age INT DEFAULT 18, #默认
        majorID INT
);
 
DESC stuinfo;
# 查看stuinfo中的所有索引，包括主键、外键、唯一。
SHOW INDEX FROM stuinfo;
 
# 2. 添加表级约束
/*
语法：在各个字段最下面
【constraint 约束名】 约束类型(字段名)
*/
DROP TABLE IF EIXSTS stuinfo;
CREATE TABLE stuinfo(
        id INT,
        id2 INT,
        stuname VARCHAR(20),
        gender CHAR(1),
        seat INT,
        seat2 INT,
        seat3 INT,
        age INT,
        majorid INT,
 
        CONSTRAINT pk PRIMARY KEY(id,id2), #两个列组合成一个主键
        CONSTRAINT uq UNIQUE(seat), #唯一键可以出现两次
        CONSTRAINT uq UNIQUE(seat2，seat3), #两个列组合成的唯一键
        CONSTRAINT ck CHECK(gender='男' OR ='女')， #检查
        CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
);

# 通用的语法：
CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo(
        id INT PRIMARY KEY, #主键:唯一且非空
        stuName VARCHAR(20) NOT NULL, #非空
        gender CHAR(1) CHECK(gender='男' OR =‘女’), #检查
        seat INT UNIQUE, #唯一
        age INT DEFAULT 18, #默认
        majorID INT,
        CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

主键和唯一的大对比：

• 两个列的组合作为主键：只要不是两个列都相等，就满足唯一性。
• 两个列的组合作为唯一键

外键：

1. 要求在从表设置外键关系
2. 从表的外键列的类型和主表的关联列的类型要求一直或者兼容，名称无要求
3. 主表的关联列必须时一个key（一般是主键或者唯一）
4. 插入数据时，先插入主表，再插入从表； 删除数据时，先删除从表，再删除主表

 可以通过以下两种方式来删除主表的记录

• 级联删除：  用这种方式添加外键时，可以直接删除主表中的行，从表中对应行也删除

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
DELATE FROM major WHERE id=3;

• 级联置空：删除了主表中的行，从表中的对应内容用NULL换

ALTER TABLE stuinfo ADD CONSTRACT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
DELETE FROM major WHERE id=2;

# stuinfo是从表，major是主表。major中的关联列id必须是主键或者唯一键。
CREATE TABLE major(
        id INT UNIQUE/PRIMARY KEY,
        majorNAME VARCHAR(20)
);
 
CREATE TABLE stuinfo(
        id INT PRIMARY KEY, #主键:唯一且非空
        stuName VARCHAR(20) NOT NULL, #非空
        gender CHAR(1) CHECK(gender='男' OR =‘女’), #检查
        seat INT UNIQUE, #唯一
        age INT DEFAULT 18, #默认
        majorID INT,
        CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);  

二、修改表时添加、删除约束

1. 添加列级约束

ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束；

2. 添加表级约束

ALTER TABLE 表名 ADD  [CONSTRAINT 约束名]   约束类型(字段名)   [外键的引用];

1、非空
--添加非空
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 NOT NULL;
--删除非空
ALTER TABLE 表名 MODITY COLUMN 字段名 字段类型;
 
2、默认
--添加默认
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 DEFAULT 值;
--删除默认
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;
 
3、主键
--添加主键
ALTER TABLE 表名 ADD 【constraint 约束名】PRIMARY KEY(字段名);
--删除主键
ALTER TABLE 表名 DROP PRIMARY KEY； 
 
4、唯一
--添加唯一
ALTER TABLE 表名 ADD 【constraint 约束名】 UNIQUE(字段名);
--删除唯一
ALTER TABLE 表名 DROP INDEX;
 
5、外键
--添加外键
ALTER TABLE 表名 ADD 【constraint 约束名】 FOREIGN KEY(字段名) REFERENCES 主表(被引用列)；
--删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

1. 添加非空约束、添加默认约束、添加主键

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
        id INT,
        stuName VARCHAR(20), 
        gender CHAR(1), 
        seat INT,
        age INT, 
        majorID INT
);
# 1. 非空
--添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
--删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;  --或者不写
 
# 2. 默认
--添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
 
# 3. 添加主键--支持列级和表级约束
# ①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
# ②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
 
# 4. 添加唯一键
# ①列级约束
ALTER TABLE stuinfo seat INT UNIQUE;
# ②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
 
# 5. 添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

    2. 修改表时删除约束

# 1. 删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;  --或者不写null
# 2. 删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
# 3. 删除主键
ALTER TABLE stuinfo MODIFY COLUMN id INT ;   
--或者
ALTER TABLE stuinfo DROP PRIMARY KEY;
# 4. 删除唯一
ALTER TABLE stuinfo DROP INDEX seat;【唯一键的名字用SHOW INDEX FROM stuinfo 查是seat】
# 5. 删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

测试 

#1.向表emp2的id列中添加PRIMARY KEY 约束（my_emp_id_pk）
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;  --列级约束不支持起名字
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);   --表级约束支持起名字
#2.向表dept2的id列中添加PRIMARY KEY 约束(my_dept_id_pk)
ALTER TABLE dept2 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE dept2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);
#3.向表emp2中添加列dept_id，并在其中定义FOREIGN KEY 约束，与之相关联的列是dept2中的id
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);

• 自增长列：又称为标识列

含义：可以不用手动的插入值，系统提供默认的序列值

特点：标识列有且只有一个，被标识的字段是数值类型，被标识的字段必须是一个key

• 标识列必须和主键搭配吗？  不一定，但是要求是一个key（主键，唯一，外键，自己定义一个key）
• 一个表中可以有多少个标识列？ 至多一个！
• 标识列的类型：数值型，INT、FLOAT、DOUBLE
• 标识列可以通过SET auto_increment_increment=3 设置步长，可以通过 手动插入值，设置起始值。

一、创建表时设置标识列

/*
CREATE TABLE 表(
                字段名 字段类型 约束 auto_increment
)
*/
CREATE TABLE tab_identity(
        id INT PRIMARY KEY,
        NAME VARCHAR(20)
);
INSERT INTO tab_identity VALUES(1,'John')   --数字为标识列，必须唯一不重复，可以用标识列
 
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
        id INT PRIMARY KEY AUTO_INCREMENT,  --添加自增长标识
        NAME VARCHAR(20)
);
TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity VALUES(NULL,'John');
INSERT INTO tab_identity(id, NAME) VALUES(NULL,'John');
INSERT INTO tab_identity(NAME) VALUES('John');
SELECT * FROM tab_identity;
 
--可以设置自增长列的步长
SHOW VARIABLES LIKE '@auto_increment%';  --查看步长
SET auto_increment_increment=3;   --设置当前表中的步长为3
INSERT INTO tab_identity VALUES(10,'John');   --更改起始值为10，第一次插入

 二、修改表时设置标识列

/*
ALTER TABLE 表 MODIFY COLUMN 字段名 字段类型 约束 auto_increment;
*/
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

三、修改表时删除标识列

/*
ALTER TABLE 表 MODIFY COLUMN 字段名 字段类型 约束
*/
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY;

(五)  TCL语言

Transaction Control Language 事务控制语言

5.1 事务的含义

        一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

5.2 事务的特性ACID： ⭐ 面试题

• 原子性：一个事务不可再分割，要么都执行要么都不执行.
• 一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态.
• 隔离性：一个事务的执行不受其他事务的干扰.
• 持久性：一个事务一旦提交，则会永久的改变数据库的数据.

# 案例：转账
 
# 张三丰  1000
# 郭襄  1000
 
update 表 set 张三丰的余额=500 where name='张三丰'
# 意外
update 表 set 郭襄的余额=1500 where name='郭襄';
--事务用于这种情况。在这个单元中，每个SQL语句都是相互依赖的，
整个单独单元作为一个不可分割的整体。
如果单元中的某一个SQL语句一旦执行失败或者发生错误，整个单元将会回滚， 
所有受到影响的数据将会返回到事务开始以前的状态。
如果单元中的所有SQL语句均执行成功，则事务被顺利执行。

存储引擎:

1. 概念: 在mysql中的数据用各种不同的技术存储在文件(或者内存)中.
2. 通过show engines;来查看mysql支持的存储引擎.
3. 在mysql中用的最多的存储引擎有: innodb, myisam, memory等. 其中innodb支持事务,而myisam和memory不支持事务.

 5.3 事务的使用

• 事务的使用

  • 隐式事务：事务没有明显的开启和结束的标记
    比如insert、update、delete语句

    ​ delete from 表 where id =1;
    

  • 显式事务：事务具有明显的开启和结束的标记
    前提：必须先设置自动提交功能 为 禁用 set autocommit=0; 这个关闭知针对这个十五有效。

# 开启事务的语句
update 表 set 张三丰的余额=500 where name='张三丰'
update 表 set 郭襄的余额=1500 where name='郭襄';
# 结束事务的语句;

• 显示事务的使用
  • 步骤一：开启事务
    • set autocommit=0; 
    • start transaction; 可选的
  • 步骤二：缩写事务中的sql语句（SELECT INSERT UPDATE DELETE），DDL语言没有事务一说
    • 语句1；
    • 语句2；
    • ......
  • 步骤三：结束事务
    • commit; 提交事务
    • rollback;  回滚事务
    • 回滚到指定的地方：rollback to 回滚点名;
• DELETE和TRUNCATE在事务使用时的区别
  • delete支持回滚
  • truncate不支持

SET autocommit=0；
START TRANSACTION;
DELETE FROM account;
ROLLBACK;   --回滚成功，撤销了删除操作
SELECT * FROM account;
 
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;   --回滚失败

# 演示事务的使用步骤
 
# 开启事务
SET AUTOCOMMIT=0;
START TRANSACTION;
# 编写一组事务的使用语句
UPDATE account SET balance=500 WHERE username='张无忌';
UPDATE account SET balance=500 WHERE username='郭襄';  --只是保存到内存，并没有提交到磁盘文件
# 结束事务
COMMIT;  --提交事务：提交到磁盘文件 
 
# 开启事务
SET AUTOCOMMIT=0;
START TRANSACTION;
# 编写一组事务的使用语句
UPDATE account SET balance=1000 WHERE username='张无忌';
UPDATE account SET balance=1000 WHERE username='郭襄';  --只是保存到内存，并没有提交到磁盘文件
# 结束事务
ROLLBACK; --回滚：撤销
 
SELECT * FROM account;

 5.4 并发事务

•  多个事务 同时 操作 同一个数据库的相同数据时。即对于同时运行的多个事务，当这些事务访问数据库中的相同数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。
• 并发问题有哪些？
  • 脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据
  • 不可重复读： T1读取了一个字段，然后T2更新了该字段，之后T1再次读取这个字段，值就不同了。
  • 幻读：T1读取了一个字段，然后T2再该表中插入了一些新的行，之后T1再次读取这个表，就会多处几行。
• 解决并发问题如何
  • 通过设置隔离级别来解决并发问题
• 事务的四种隔离级别：

# mysql中默认 第三个隔离级别 
repeatable read
# oracle中默认第二个隔离级别 
read committed
# 查看隔离级别
SELECT @@tx_isolation;
SHOW VARIABLES LIKE 'tx_isolation';
# 设置mySQL连接的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;
# 设置数据库系统的全局的隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;

#1.演示事务的使用步骤
# 开启事务
SET autocommit=0;
START TRANSACTION;
# 编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';
# 结束事务
ROLLBACK; # commit;
SELECT * FROM account;
 
#2.演示事务对于delete和truncate的处理的区别
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
 
#3.演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;    #设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;  #回滚到保存点:25删除，28没删除
SELECT * FROM account;

（六） 其他 

6.1 视图

含义：虚拟表，和普通的表一样使用

mysql15.1版本出现的新特性，是通过表动态生成的特性。

# 案例：查询姓张的学生名和专业名
SELECT stuname, majorname
FROM stuinfo s
INNER JOIN major m ON s.majorid= m.id
WHERE s.stuname LIKE '张%'; 
--用视图等价写为
CREATE VIEW v1
AS
SELECT stuname, majorname
FROM stuinfo s
INNER JOIN major m ON s.majorid = m.id;
 
SELECT * FROM v1 WHERE stuname LIKE '张%';

• 应用场景:

  • 多个地方用到 相同的查询结果
  • 该查询结果使用的 sql语句比较复杂

• 好处：

  • sql语句的重用
  • 简化复杂的sql操作，不必知道它的查询细节
  • 保护数据，提高安全性（只提供对方需要的信息）
• 创建视图

# 创建视图语法
/*
CREATE VIEW my_v1
AS
查询语句
*/
USE myemployees;
# 1.查询姓名中包含a字符的员工名，部门名，工种信息
--视图的创建
CREATE VIEW myv1
AS 
SELECT e.last_name, d.department_name, j.job_title
FROM employees e
INNER JOIN departments d ON e.department_id=d.department_id
INNER JOIN jobs j ON j.job_id=e.job_id;
--视图的使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
# 2.查询各部门的平均工资级别
CREATE VIEW myv2
AS
SELECT AVG(salary) ag, departmnet_id
FROM employees
GROUP BY department_id;
 
SELECT myv2.ag, g.grade_level
FROM myv2 
INNER JOIN job_grades g
ON myv2.ag BETWEEN g.lowest_sal AND g.highest_sal;
# 3.查询平均工资最低的部门编号
--每个部门的平均工资是myv2
SELECT *
FROM myv2
ORDER BY ag ASC 
LIMIT 1;
# 4.查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ASC LIMIT 1;
 
SELECT d.*, myv3.ag 
FROM myv3
JOIN departments d ON myv3.department_id=d.department_id 

•  视图的修改

# 方式一：
/*
CREATE OR REPLACE VIEW 视图名  --该视图不存在就创建，存在即修改代替
AS
查询语句;
*/
# 案例：修改myv3
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary), job_id
FROM employees
GROUP BY job_id;
 
# 方式二：
/*
ALTER VIEW 视图名
AS 
查询语句;
*/
ALTER VIEW myv3
AS 
SELECT * FROM employees;  

•  删除视图

/*
DROP VIEW 视图名,视图名,...
*/
DROP VIEW myv1, myv2, myv3;

• 查看视图

DESC myv3;
 
SHOW CREATE VIEW myv3；  --在cmd中查看

测试例题：

#1、创建视图emp_v1，要求查询电话号码以011开头的员工姓名和工资、邮箱。
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name, salary, email
FROM employees
WHERE phone_number LIKE "011%";
 
#2 创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息。
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary),department_id
FROM employees
GROUP BY department
HAVING emp_v2.MAX(salary) > 12000;
 
SELECT d.*, emp_v2.MAX(salary)
FROM emp_v2
INNER JOIN departments d ON emp_v2.department_id=d.department_id;

• 更新试图

# 创建视图myv1
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name, email, 
FROM employees;
SELECT * FROM myv1;
 
# 1.插入数据
INSERT INTO myv1 VALUES('张飞','zf@163.com'); 
# 2.修改数据
UPDATE myv1 SET last_name='张无忌' WHERE last_name='张飞';
# 删除数据
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 子句中的表

# 1.包含这些关键字的sql语句：分组函数，distinct,group by,having,union,union all
CREATE OR REPLACE VIEW myv1
AS 
SELECT MAX(salary) m, department_id
FROM employees
GROUP BY department_id;
#更新
UPDATE myv1 SET m=9000 WHERE department_id=10;   --更新失败，因为myv1中有group by关键字
 
# 2. 常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
#更新
UODATE myv2 SET NAME='lucy';  --更新失败，视图是常量视图
 
# 3. SELECT中包含子查询
CREATE OR REPLACE VIEW myv3
AS 
SELECT (SELECT MAX(salary) FROM employees) 最高工资;
# 更新
UPDATE myv3 SET 最高工资=100000；   --更新失败，视图中包含子查询
 
# 4. join
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name, department_name
FROM employees e
JOIN department d
ON e.department_id =d.department_id;
# 更新
UPDATE myv4 SET last_name='张飞' WHERE last_name='Whalen';  --可以更新
# 插入
INSERT INTO myv4 VALUES('陈真','xxxxx');   --不可以插入
 
# 5.FROM一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM (myv3);  
#更新
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;  --视图不能跟新
 
# 6.WHERE 子句的子查询引用了FROM 子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name, email, salary
FROM employees
WHERE employee_id IN(
        SELECT DISTINCT manager_id
        FROM employees
        WHERE manager_id IS NOT NULL
);              --领导的姓名，邮箱，工资
# 更新
UPDATE myv6 SET salary=10000 WHERE last_name='k_ing';  --更新失败
 

• 视图与表的对比

测试

1、创建BOOK表
CREATE TABLE BOOK(
    bib int primary key,
    bname varchar(20) unique not null,
    price float default 10,
    btypeID int ,
    foreign key(btypeID) references bookType(id)
);
 
2、开启事务，向表中插入一行数据，并结束
SET autocommit=0;
START transaction;
INSERT INTO BOOK(bib,bname,price,btypeID) VALUES(1,'小李飞刀',100,1)
 
ROLLBACK;
 
3、创建视图，实现查询价格大于100的书名和类型名
CREATE OR REPLACE VIEW myv1
AS 
SELECT bname, name
FROM BOOK b
JOIN BOOKTYPE t ON b.btypeID=t.id
WHERE price>100;
 
4、修改视图，实现查询价格在90-120之间的书名和价格
CREATE OR REPLACE VIEW 
AS
SELECT bname, price
FROM BOOK
WHERE price BETWEEN 90 AND 120;
 
5、删除我们所建的视图
DROP VIEW myv1;

6.2 变量

• 系统变量：
  • 全局变量global
  • 会话变量session

说明：变量时由系统提供的，不是用户定义，属于服务器层面的

• 自定义变量：
  • 用户变量
  • 局部变量

一、系统变量 

#1、查看所有系统变量
SHOW GLOBAL|【SESSION】ARIABLES;  --全局|会话变量
 
#2、查看满足条件的部分系统变量
SHOW GLOBAL|【SESSION】 VARIABLES LIKE '%char%';  --包含char字符的系统变量
 
#3、查看指定的某个系统变量的值
SELECT @@【SESSION.】系统变量名；  --查会话变量
SELECT @@GLOBAL.系统变量名;
 
#4、为某个具体的系统变量赋值
--例如设置取消自动提交
--方式一：
SET GLOBAL|【SESSION】 系统变量名 = 值;
--方式二：
SET @@GLOBAL|SESSION .系统变量名 = 值
#注意：如果是全局变量，则需要加global，如果是会话变量，则需要加session，如果什么都不加就默认是会话变量。

 示例：

# 1》 全局变量
/*
作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有会话（连接）有效，但不能跨重启（如果服务器重新启动，之前的赋值效果不存在）
*/
# ① 查看所有全局变量
SHOW GLOBAL VARIABLES;
# ② 查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
# ③ 查看指定的系统变量的值
SELECT @@global.autocommit;
# ④ 为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
 
#2》 会话变量
/*
作用域：针对于当前会话（连接）有效
*/
# ① 查看所有会话变量
SHOW VARIABLES;
SHOW SESSION VARIABLES;
# ② 查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
SHOW VARIABLES LIKE '%char%';
# ③ 查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
# ④ 为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

 二、自定义变量

# 说明：变量时用户定义的，不是有系统提供的
# 使用步骤：声明-赋值-使用（查看、比较、运算等）
 
#1》 用户变量
/*
作用域：针对于当前会话（连接有效），同于会话变量的作用域
应用在任何地方，也就是begin end 里|外边 都可以。
*/
                    赋值操作符： =或:=  
#① 声明并初始化
SET @用户变量名=值;   --或
SET @用户变量名:=值;  --或
SELECT @用户变量名:=值;
 
#② 赋值（更新用户变量的值）
--方式一：通过SET或SELECT
        SET @用户变量名=值;
        SET @用户变量名:=值;
        SELECT @用户变量名:=值;
--方式二：通过SELECT INTO
        SELECT 字段 INTO 变量名 FROM 表;  --查出来的字段必须是一个值才能赋值给变量，一组值不可以。
#③ 使用（查看用户变量的值）
SELECT @用户变量名;
--案例：
#声明并初始化
SET @name='john';
SET @name=100;
SET @count=1;
#赋值
SELECT COUNT(*) INTO @count 
FROM employees;  --将员工表的员工数赋值给name这个变量
#查看
SELECT @count;
 
#2》 局部变量
/*
作用域：仅仅定义在它的begin end中有效
应用在begin end中的第一句话！！！
*/
#① 声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
#② 赋值 SET或SELECT INTO
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 字段名 INTO @局部变量名 FROM 表;
#③ 使用
SELECT 局部变量名;

# 案例：生命两个变量并复制初始值，求和，并打印
#1. 用户变量
SET @m=1;
SET @n=2;
SET @sum = @m + @n;
SELECT @sum;   --输出3
 
#2.局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;   --报错：没有在begin end中使用。

6.3 存储过程和函数

• 存储过程和函数：类似于java中的方法
• 好处：
  1、提高代码的重用性
  2、简化操作

一、存储过程

• 含义：一组预先编译好的SQL语句的集合，理解成批处理语句
• 好处：
  1. 提高代码的重用性
  2. 简化操作
  3. 减少了编译次数，减少了和数据库服务器的连接次数，提高了效率

1、  创建语法 ⭐

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体（一组合法的SQL语句）
END
/*
    1、参数列表包含三部分
        参数模式  参数名  参数类型
        举例：
        IN stuname varchar(20)
        参数模式：
         in：该参数可以作为输入，也就是该参数需要调用方传入值,in可以省略
         out：该参数可以作为输出，也就是该参数可以作为返回值
         inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
    2、如果存储过程体仅仅只有一句话，begin end可以省略
       存储过程体中的每条sql语句的结尾要求必须加分号。
       存储过程的结尾可以使用 delimiter 重新设置
        语法：
            delimiter 结束标记
            案例：
            delimiter $
*/
 
# 二、调用语法
CALL 存储过程名(实参列表)
#----------------------案例演示-------------------------
# 1.空参列表
# 案例：插入到admin表中五条记录
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
        INSERT INTO admin(username, password) 
        VALUES('john1','0000'),('lily','0000'),('rose','0000'),('tom','0000'),('peter','0000');
END $
 
# 调用
CALL myp1()$;
--select * from admin$;  
#--------------------------------------------------------------
# 2.创建IN模式参数的存储过程（带传入值）
# 案例1：创建存储过程实现 根据女神名，查询对应的男神信息
--这里的女神名当作参数，需要传入
CREATE PROCEDURE myp2 (IN beautyName VARCHAR(20))
BEGIN
        SELECT bo.*
        FROM boys bo
        RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
        WHERE b.name=beautyName
END $
# 调用
CALL myp2('柳岩') $
 
#案例2：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE mtp3(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN
        DECLARE result INT DEFAULT 0;  #声明并初始化
        SELECT COUNT(*) INTO result     --赋值
        FROM admin 
        WHERE admin.username = username
        AND PASSWORD = PASSWORD;    
        SELECT IF (result>0, '成功', '失败');  --使用
END $
CALL mtp3('张飞','8888')$
#--------------------------------------------------------------
#3、创建带OUT 模式的存储过程（带返回值）
# 案例1：根据女神名，返回对应的男神名
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
        SELECT bo.boyName INTO boyName   --赋值
        FROM boys bo
        INNER JOIN beauty b ON bo.id=b.boyfriend_id
        WHERE b.name=beautyName;   --查询
END $
#调用
# 定义一个用户变量，可以再存储过程外(begin end外)进行使用
SET @bName$
CALL myp5('小昭', @bName)$
SELECT @bName    --查看返回值
 
#案例2：根据女神们，返回对应的男生名个男神魅力值
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
        SELECT bo.boyName bo.userCP INTO boyName,userCP  --赋值
        FROM boys bo
        INNER JOIN beauty b ON bo.id=b.boyfriend_id
        WHERE b.name=beautyName;   --查询
END $
# 调用
CALL myp6('小昭',@bName,@usercp)$
SELECT @bName,@usercp
#--------------------------------------------------------------
#4、创建带INOUT模式参数参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回  ——对a，b重新赋值（局部变量的赋值）
CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT)
BEGIN
        SET a=a*2;
        SET b=b*2;   --初始化一个局部变量
END $
#调用
--首先定义两个用户变量
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)
SELECT @m,@n

 案例

# 1、创建存储过程实现传入用户名和密码，插入到admin表中
CREATE PROCEDURE test_pro1(IN username VARCHAR(20), IN loginPWD VARCHAR(20))
BEGIN
        INSERT INTO admin(admin.username, PASSWORD)
        VALUES(username,loginpwd);
END $
 
CALL test_pro1('admin','0000')$
SELECT * FROM AFFECTED$
 
# 2、创建存储过程实现传入女神编号，返回女神名称和女神电话
CREATE PROCEDURE test_pro2(IN id INT, OUT beauty_name VARCHAR(20), OUT phone VARCHAR(20))
BEGIN
        SELECT b.NAME,b.pho@nne INTO beauty_name,phone
        FROM beauty b
        WHERE b.id=id;
END $
CALL test_pro2(1,@n,@p)$
SELECT @n,@p$
 
# 3、创建存储过程或函数实现传入两个女神的生日，返回大小
CREATE PROCEDURE test_pro3(IN birth1 DATETIME, IN berth2 DATETIME, OUT result INT)
BEGIN
       SELECT DATEDIFF(birth1, birth2) INTO result;  --birth1>birth2返回正数，< 返回负数 ，相等返回0
END $
CALL teat_pro3('1998-1-1',now(),@result)$
SELECT @result$

    2、 删除存储过程

# 语法：
/*
DROP PROCEDURE 存储过程名
*/
DROP PROCEDURE test_pro1;
DROP PROCEDURE test_pro2,test_pro3;  --错误，不能多个删除

   3、 查看存储过程的信息

DESC 存储过程名;   ×
SHOEW CREATE PROCEDURE 存储过程名;

案例

# 四、创建存储国臣或函数实现传入一个日期，格式化成xxx年xx月xx日并返回
CREATE PROCEDURE test_pro4(IN mydate DATETIME, OUT strDate VARCHAR(20))
BEGIN
        SELECT DATE_FORMAT(mydate, '%y年%m月%d日') INTO strDate;  --日期转化成字符用：DATE_FORMAT;  字符转化成日期用STR_TO_DATE
END $
CALL test_pro4(NOW(4：), @str) $
SELECT @str $
 
# 五、创建存储国臣或者函数是实现传入女神名称，返回：女神 and 男神 格式的字符串
--如 传入：小昭
--   返回： 小昭 and 张无忌
CREATE PROCEDURE teat_pro5(IN beautyName VARCHAR(20), OUT str VARCHAR(50))
BEGIN
        SELECT CONCAT(beautyName,'and',boyName) INTO str  --赋值
        FROM boys bo
        RIGHT JOIN beauty b ON b.boyfriend=bo.id
        WHERE b.name=beautyName;
END $
CALL teat_pro5('小昭', @str)
SELECT @str $   --成功，但是如果女神没有男朋友，则没有结果，为了出现 ‘女神名 and null’效果：boyName改成IFNULL(boyName, 'null')
CREATE PROCEDURE teat_pro5(IN beautyName VARCHAR(20), OUT str VARCHAR(50))
BEGIN
        SELECT CONCAT(beautyName,'and',IFNUKK(boyName,'NULL')) INTO str  --赋值
        FROM boys bo
        RIGHT JOIN beauty b ON b.boyfriend=bo.id
        WHERE b.name=beautyName;
END $
CALL teat_pro5('柳岩', @str)
SELECT @str $    --返回：柳岩 and null
 
# 六、创建存储过程或函数，根据传入的起始索和引条目数，查询beauty表的记录
CREATE PROCEDURE test_pro6(IN startIndex INT,IN size INT)
BEGIN
        SELECT * FROM beauty LIMIT startIndex, size;
END $
CALL test_pro6(3,5) $

二、函数

• 含义：一组预先编译好的SQL语句的集合，理解成批处理语句
• 好处：
  1. 提高代码的重用性
  2. 简化操作
  3. 减少了编译次数，减少了和数据库服务器的连接次数，提高了效率
• 区别：
  • 存储过程： 可以有0个返回，也可以有多个返回。适合做批量插入、批量更新
  • 函数： 有且仅有1个返回，适合做处理数据后返回一个结果

1、函数的创建和调用

/*
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN 
         函数体
END       
*/
# 注意：
# 1.参数列表  包含两部分：   参数名   参数类型
# 2.函数体：肯定会有return语句，否则会报错
#   如果return语句没有放在函数体最后也不会报错，但一般不建议。
         return 值;
# 3.函数体中仅有一句话，则可以省略begin end
# 4.使用delimiter语句设置结束标记 
        delimiter $;

 2、调用语法

 
# 二、调用语法
SELECT 函数名(参数列表)
#------------案例演示-------------------------------------
# 1.无参有返回的
# 返回公司的员工个数
CREATE FUNCTION myf1() RETURN INT
BEGIN
        DECLARE c INT DEFAULT 0; --定义局部变量
        SELECT COUNT(*) INTO c   --为变量赋值
        FROM employees;
        RETURN c;
END $
SELECT myf1()$
#=====----------------------------------------------------
# 2. 有参有返回的
# 根据员工名，返回他的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURN DOUBLE
BEGIN
        SET @sal=0;  --定义一个用户变量
        SELECT salary INTO @sal   --赋值
        FROM employees 
        WHERE last_name =empName;
        RETURN @sal;
END $
SELECT myf2('kochhar') $
# 根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RUTURN DOUBLE
BEGIN
        DECLARE sal DOUBLE;  --定义一个局部变量
        SELECT AVG(salary) INTO sal
        FROM employees e
        JOIN departments d ON e.department_id=d.department_id
        WHERE d.department_name=deptName;
        RETURN sal;
END $
SELECT myf3('IT')$

3、查看函数

SHOW CREATE FUNCTION myf3;

4、删除函数

DROP FUNCTION myf3;

案例

#1、创建函数，传入两个float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT, num2 FLOAT)
BEGIN
        DECLARE SUM FLOAT DEFAULT 0;
        SELECT SUM=num1+num2;
        RETURN SUM;
END $
SELECT test_fun1(1,2)$

6.4 流程控制结构

•  顺序结构：程序从上往下依次进行
• 分支结构：程序从两条或多条路径中选择一条去执行
• 循环结构：程序在满足一定条件的基础上，重复执行一段代码

一、分支结构 

•  IF函数：能实现简单的双分支，可以应用在任何地方

/*
IF (表达式1，表达式2，表达式3)
*/
#执行顺序：如果表达式1成立，则IF函数返回表达式2的值，否则返回表达式3的值。

• CASE结构
  • 情况1：类似于Java中的switch语句，一般用于实现等值判断

CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1   --不在存储过程和函数中使用：语句1;
WHEN 要判断的值 THEN 返回的值2                            --语句2;
...
ELSE 要返回的值n                                         --语句n;
END CASE;

• • 情况2：类似于Java中的多种IF语句，一般用于实现区间判断

CASE 
WHEN 要判断的条件1 THEN 返回的值1 或语句1;
WHEN 要判断的条件2 THEN 返回的值2 或语句2;
...
ELSE 要返回的值n 或语句n;
END CASE;

特点：

• ①  可以作为表达式（值），嵌套在其他语句使用，可以放在任何地方，BEIGN END中或BEGIN END 外边
  • 可以作为单独的语句（语句）去使用，只能放在BEGIN END 中使用
• ②   如果WHEN 中的值或者条件成立，则执行对应的THEN中的语句，并且结束CASE；如果都不满足，则执行ELSE中的语句或值。
• ③  ELSE可以省略，如果ELSE省略了，并且所有WHEN条件都不满足，则返回NULL

# 案例：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100，A；80-90，B；60-80，C；否则，D。
--区间判断：情况2  返回的是值
CREATE PROCEDURE test_case(IN score INT)
BEGIN
        CASE 
        WHEN score >=90 AND score<=100 THEN SELECT 'A';
        WHEN score >=80 THEN SELECT 'B';
        WHEN score >=60  THEN SELECT 'C';
        ELSE 'D';
        END CASE;
END $
CALL test_case(95) $

•  IF结构

功能：实现多重分支

IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
【ELSE 语句n】    --else语句可以省略
END IF;
#应用场合：在BEGIN END 中。

# 案例1：根据传入的成绩，来显示等级，比如传入的成绩：90-100为A，80-90为B，60-80为C，否则D
CREATE FUNCTION test_if1(score INT) RETURNS CHAR
BEGIN
        IF score>=90 AND score<=100 THEN RETURN 'A'
        ELSEIF score>=80 THEN RETURN 'B'
        ELSEIF score>=60 THEN RETURN 'C'
        ELSE RETURN 'D'
        END IF;
END $
SELECT test_if(86)$

二、循环结构

分类：while、loop、repeat

循环控制：iterate类似于continue，继续，结束本次循环，继续下一次循环

                  leave类似于break，跳出循环，结束循环。

1. while
【标签:】 WHILE 循环条件 do
        循环体;
END WHILE 【标签】;
# 联想在java中：
-- WHILE(循环条件){
--         循环体;
-- }
 
2. loop
【标签:】loop 
        循环体;
end loop 【标签】;
# 可以用来模拟简单的死循环
 
3. repeat
【标签:】repeat 
        循环体;
until 结束循环条件
end repeat【标签】;

案例

# 案例1：批量插入，根据次数插入到admin表中多条记录（没有添加循环控制语句）
-- 没有返回，建PROCEDURE
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
        DECALRE i INT DEFAULT 1;
        a:WHILE i<=inserCount DO
            INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('Rose',i),'666');
            SET i=i+1
        END WHILE a;
END $
CALL pro_while1(100)$  --插入100遍
SELECT * FROM admin$
/* JAVA中while使用
int i=1;
while(i<=insertCount){
      //插入
      i++;
}
*/
# ------------------添加leave语句----------------------------------------------
# 案例2：批量插入，根据次数插入到admin表中多条记录，如果次数大于20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while2(IN insertCount INT)
BEGIN
        DECLARE i INT DEFAULT 1;
        a: while i<=insertCount DO
                INSERT INTO admin(username,password) VALUES(CONCAT('XIAOHUA',i),'9999');
                IF i>20 THEN leave a;
                END IF;
                SET i=i+1;
        END while a;
END $
CALL pro_while2(100)$
SELECT * FROM admin$
# -------------------添加iterate语句--------------------------------------------
# 案例3.批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE admin$
DROP PROCEDURE pro_while2$
CREATE PROCEDURE pro_while3(IN insertCount INT)
BEGIN
        DECLARE i INT DEFAULT 0;
        b:WHILE i<=insertCount
                SET i=i+1;
                IF MOD(i,2) !=0 THEN iterate b;
                END IF;
                INSERT INTO admin(username,password) VALUES(CONCAT('lilyna', i),'9090');
        END WHILE b; 
END $
CALL pro_while3(100)$
SELECT * FROM admin$
--JAVA中写法
/*
int i=0;
while(i<+insertCount){
        i++;
        if(i%2 !=0){
            continue;
        }
        //插入
        
}
*/

案例

# 一、已知表stringcontent
# 其中字段：
# id 自增长
# content varchar(20)
 
# 向该表插入指定个数的，随机字符串
DROP TABLE IF EXISTS stringcontent;
CREATE TABLE srtingcontent(
        id INT PRIMARY KEY KEY AUTO_INCREMENT,
        content VARCHAR(20)
);
DELIMITER $
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
        DECLARE i INT DEFAULT 1;  --定义一个循环变量，表示插入次数
        DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';  #产生一个随机字符串
        DECLARE startIndex INT DEFAULT 1;   #产生一个随机的正数，代表其实索引1-26
        DECLARE len INT DEFAULT 1;  #代表截取的字符的长度
        WHILE i<=insertCount DO:
            SET len=FLOOR(RAND()*(19-startIndex)+1);  #产生一个随机的整数，代表截取的长度1到26-startIndex+1，但是content的长度最长为20，则将26换成20
            SET startIndex =FLOOR(RAND()*26+1); #产生一个随机的正数，代表其实索引1-26
            INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
            SET I=i+1; #循环变量更新
        END WHILE;
END $
CALL test_randstr_insert(10)$
SELECT * FROM stringcontent;

(七) 、窗口函数

function(args)  OVER([PARTITION BY expression][OEDER BY expression [ASC|DESC]][frame])

一、排序函数

• 例如：rank() over(partition by yy order by xx DESC)

案例1：如何写排序代码（不分组排序）

178. 分数排名 - 力扣（LeetCode）

案例2：如何写排序代码（分组排序）

184. 部门工资最高的员工 - 力扣（LeetCode）

利用开窗函数可以取每个部门最高，也可以取前二高，前三高，也可以只取第一第三。

 二、聚合函数配合窗口函数实现滑动窗口计算

常用的聚合函数：

count(col) -返回所有非空值的个数

count(*)

count(字段) -空值不计入 

avg(col) -返回平均值

sum(col) -返回平均值

max(col) -返回最大的值

min(col) -返回最小的值

first(col) -返回第一个记录的值

last(col) -返回最后一个记录的值 

{RANGE|ROWS} frame_start   

{RANGE|ROWS} BETWEEN frame_start AND frame_end

        UNBOUNDED PRECEDING  --从头开始

        expression PRECEDING  --往前推expression行

        CURRENT ROW   --当前行

        expression FOLLOWING  --往下推两行

        UNBOUNDED FOLLOWING  --到结束行

默认：BETWEEN unbounded preceding AND CURRENT ROW

# 滚动求近三个月的平均GMV?   --往上推两行
SELECT product
,year_month
,gmv
 
,avg(gmv) over(partition by department,product order by year_month ROWS 2 PRECEDING) AS avg_gmv
 
FROM product;

# 滚动求 从上架开始到本月 的平均GMV？  
--开始行到结束行：默认
SELECT product
,year_month
,gmv
 
,avg(gmv) over(PARTITION BY department,product ORDER BY year_month ) AS avg_gmv  
# 不写，默认是从开始到现在的平均值
FROM product;

三、lag、lead函数求环比

# 求每个产品的GMV的环比涨幅？(这一期-上一期)-1
例如：lag(gmv,1)  --取上一期的gmv值
 
select product
,year_month
,gmv
,lag(gmv,1) over(PARTITION BY department,product ORDER BY year_month) AS lag_gmv
# 对gmv列进行操作，取当前行的上一行
,cast(gmv as double) / lag(gmv,1) OVER (PARTITION BY department,product ORDER BY year_month) -1 as growth_rate
#求增长率：当期的值/上一期的值 - 1
# 上一期，当前期都是整形，但是为了让增长率是小数，将分子或者分母转化为double型。
FROM product;
 
Q&A
日期不连续怎么办？可以通过join万年历表解决
 
# 如果数据是以月为单位的，那么如何计算同比涨幅？
改为lag(gmv,12)

案例：如何求销量top10%的商品信息

select 
product
,precent_rank() over(order by sales desc) as 'percent_rank'
,cume_dist() over(order by sales) as 'cume_dist'
FROM sku_sales;

# 先对数据进行排序
WITH tmp AS (
SELECT 
        PRODUCT
        ,row_number() over(order by sales desc) as 'rank'
FROM sku_sales )
# 指定阈值rank_bar，在根据上一步的排名和阈值截取数据
SELECT * FROM tmp a
        LEFT JOIN(
SELECT ceiling(count(1)*0.1) as rank_bar
FROM sku_sales ) b on 1=1
        WHERE 'rank'<=rank_bar;

NTILE(n)函数

功能：将排序分区中的行 划分为特定数量的组。

对于每一行，NTILE()函数返回一个桶号，表示行所属的组。

ntile(n) over([partition by yy] [order by xx] [frame])

SELECT 
product
,ntile(10) over(order by sales desc) as ntile_rank  --表示组号。
FROM sku_sales;
# 分成十组，那么第一组就是前百分之10