mysql8 社区版下载 mysql8.0.22.0

mysql8.0.22

• 1. 数据库的相关概念
• 2. 数据库存储数据的特点
• 3. MySQL产品的安装

• 一、MySQL服务的启动和停止
• 二、MySQL服务的登录和退出
• 三、MySQL的常见命令和语法规范

• 4. DQL语言的学习

• 一、基础查询
• 二、条件查询
• 三、排序查询
• 四、常见函数
• 五、分组函数
• 六、分组查询
• 七、连接查询

• 1. sql92语法标准
• 2. sql99语法标准

• 八、子查询
• 九、分页查询
• 十、union联合查询

• 5. DML数据操作语言

• 一、插入： insert
• 二、修改： update
• 三、删除： delete

• 6. DDL数据定义语言

• 一、库的管理创建修改删除
• 二、表的管理 创建修改删除复制
• 三、常见约束

• 1. 六大约束
• 2. 创建表时添加约束
• 3. 修改表时添加和删除约束

• 四、标识列

1. 数据库的相关概念

1. DB：数据库，保存一组有组织的数据的容器
2. DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
3. SQL:结构化查询语言，用于和DBMS通信的语言

2. 数据库存储数据的特点

1. 将数据放到表中，表再放到库中
2. 一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
3. 表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
4. 表由列组成，称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
5. 表中的数据是按行存储的，每一行类似于java中的“对象”。

3. MySQL产品的安装

1. 下载mysql的压缩包 https://dev.mysql.com/downloads/
2. 找一个自己喜欢的文件夹，最好不要出现中文路径，解压mysql的zip文件。
3. 修改密码和配置环境变量请参考链接: .

一、MySQL服务的启动和停止

方式一：计算机——右击管理——服务

方式二：通过管理员身份运行

net start 服务名（启动服务）

net stop 服务名（停止服务）

注意事项 服务名未自己设置的mysql名，例如我的就是mysql80

二、MySQL服务的登录和退出

方式一：通过mysql自带的客户端
	只限于root用户
方式二：通过windows自带的客户端
	登录：
		mysql 【-h主机名 -P端口号 】-u用户名 -p密码
		如果是本机的话【】中的内容可以省略
	退出：
		exit或ctrl+C

三、MySQL的常见命令和语法规范

1. 查看当前所有的数据库 show databases;
2. 打开指定的库 use 库名
3. 查看当前库的所有表 show tables;
4. 查看其它库的所有表 show tables from 库名;
5. 查看表结构 desc 表名;
6. 查看服务器的版本

方式一：登录到mysql服务端 select version();
 方式二：没有登录到mysql服务端
 	mysql --version或mysql --V

4. DQL语言的学习

一、基础查询

/*
语法：
select 查询列表 from 表名;

特点：
1、查询列表可以是表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格


*/
# 1. 查询表中的单个字段
SELECT `last_name` FROM `employees`;

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

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

# 4. 查询常量值
SELECT 100;
SELECT 'join';

# 5. 查询表达式
SELECT 100*99;

# 6. 查询函数
SELECT VERSION(); # 显示当前数据库的版本

# 7. 起别名
/*
  便于理解
  如果要查询的字段有重名的情况，起别名可以区分开来
*/
# 方式一 使用as
SELECT 100*99 AS 结果;
SELECT `last_name` AS 姓 ,`first_name` AS 名 FROM `employees`;
# 方式二 使用空格
SELECT `last_name`  姓 ,`first_name`  名 FROM `employees`;

# 案例 查询salary 显示结果为 out put
SELECT `salary` AS "out put" FROM `employees`;

# 8. 去重
# 案例 查询员工表中涉及到的所有部门编号
SELECT DISTINCT `department_id` FROM `employees`;

# 9. +号的作用
/*
mysql中+号只是运算符
	select 100 + 90; 都为数值型直接进行加法运算
	select '123' + 10; 有一方为字符型，试图将其转化为数值型 如果转换失败则为0；
	select null+10; 只要有一方为null结果肯定为null
*/
# 案例 查询员工名和员工的姓连接成一个字段并显示为 ：姓名
SELECT CONCAT(last_name,first_name) AS 姓名 FROM `employees`;

# IFNULL(参数1,参数2) 参数1要查询的字段名，如果为null则显示参数2
SELECT IFNULL(`commission_pct`,0) AS 奖金率 FROM `employees`;

二、条件查询

/*
语法：
	select 
		查询列表
	from
		表名
	where
		筛选条件;
分类；
	一、按条件表达式筛选
	条件运算符：> < = != <> >= <=
	
	二、按逻辑表达式筛选
	逻辑运算符 
	作用：用于连接条件表达式
		&& || ！
		and or not
	三、模糊查询
		like
		between and
		in
		is null

*/
# 一、按条件表达式筛选
# 案例1 查询员工工资>12000的员工信息
SELECT
	* 
FROM
	`employees` 
WHERE 
	`salary` > 12000;
# 案例2 查询部门编号不等于90号的员工名和部门编号
SELECT
	`last_name`,
	`department_id` 
FROM
	`employees`
WHERE
	`department_id` <> 90;
	
# 二、按逻辑表达式筛选
# 案例1 工资在10000-20000之间的员工名、工资、奖金
SELECT
	`last_name`,
	`salary`,
	`commission_pct`
FROM 
	`employees`
WHERE
	`salary` BETWEEN 10000 AND 20000;
# 案例2 查询部门编号不是在90-110之间的，或者工作高于15000的员工信息
SELECT
	*
FROM 
	`employees`
WHERE
	NOT(`department_id` BETWEEN 90 AND 110)
	OR `salary` > 15000;
	
# 三、模糊查询
/*
like
	一般和通配符搭配使用
	通配符：
		% 任意多个字符 包含0
		_ 任意一个字符 就一个
between and
in
is null 
is not null
*/

#1. like
#案例1 查询员工名中包含a的员工信息 默认不区分大小写
SELECT * FROM `employees` WHERE `last_name` LIKE '%a%';
#案例2 查询员工名中第三个字符为n，第5个字符为l的员工名和工资
SELECT
	`last_name`,`salary`
FROM
	`employees`
WHERE
	`last_name` LIKE '__n_l%';
	
#案例3 查询员工名中第2个字符为_ 的员工名
SELECT
	`last_name`
FROM
	`employees`
WHERE
	#`last_name` like '_\_%';
	`last_name` LIKE '_$_%' ESCAPE '$';
	
#2. between and 包含临界值
#案例1 查询员工编号在100-120之间的员工信息
SELECT
	*
FROM 
	employees
WHERE
	employee_id BETWEEN 100 AND 120;
	
#3 in 判断某字段的值是否属于in列表中的某一项 不能使用通配符
#案例1 查询员工的工种编号是  AD_VP AD_PRES 中一个的员工名和工种编号
SELECT
	`last_name`,`job_id`
FROM
	`employees`
WHERE
	`job_id` IN ('AD_VP','AD_PRES');
	
#4 is null
#案例1 查询没有奖金的员工名和奖金率
SELECT
	`last_name`,`commission_pct`
FROM 
	`employees`
WHERE
	`commission_pct` IS NULL;
#案例2 查询有奖金的员工名和奖金率
SELECT
	`last_name`,`commission_pct`
FROM 
	`employees`
WHERE
	`commission_pct` IS NOT NULL;
	
#安全等于 <=>
#案例1 查询没有奖金的员工名和奖金率
SELECT
	`last_name`,`commission_pct`
FROM 
	`employees`
WHERE
	`commission_pct` <=> NULL;
#案例2；查询工资为12000的员工信息
SELECT
	*
FROM 
	`employees`
WHERE
	`salary` <=> 12000;

三、排序查询

/*
语法：
	select 查询列表			3
	from 表名                       1
	【where 查询条件】		2
	order by 排序列表 【asc|desc】;	4
特点：
	asc 升序 默认 desc 降序
	order by 子句中可以支持 单个字段、多个字段、表达式、函数、别名
	order by 子句一般是放在查询语句的最后面 limit子句除外
		
*/

#案例1： 查询呀员工信息要求工资从高到低排序
SELECT * FROM `employees` ORDER BY `salary` DESC;

#案例2：查询部门编号 >= 90 的员工信息 按入职时间的先后进行排序
SELECT
	* 
FROM 
	`employees` 
WHERE 
	`department_id` >= 90 
ORDER BY 
	`hiredate` ASC;
	
#案例3 {按表达式排序}按年薪高低显示员工的信息和年薪
SELECT
	* ,(`salary` * 12 * (1 + IFNULL(`commission_pct`,0))) AS 年薪
FROM 
	`employees`
ORDER BY
	年薪 DESC;

#案例4 {按函数排序}按姓名的长度显示员工的姓名和工资
SELECT
	LENGTH(`last_name`) 字节长度 ,last_name ,`salary`
FROM
	`employees`
ORDER BY
	字节长度 DESC;

#案例5 查询员工信息 先按工资排序，再按员工编号 降序排序
SELECT * FROM `employees`
ORDER BY `salary` ,`employee_id` DESC;

#案例6 查询部门编号 >= 90 的员工信息 按入职时间的先后进行排序
SELECT * FROM `employees`
WHERE `department_id` >= 90
ORDER BY `hiredate` ASC;

四、常见函数

/*
常见函数：
调用：select 函数名(实参列表) 【from 表】
特点：
	函数名
	函数功能
分类：
	1. 单行函数
	concat、length、ifnull
	2. 分组函数
	功能: 做统计使用，又称为 统计函数，聚合函数，组函数
*/
#一、字符函数

#1. length 获取参数的字节个数 字母一个字节 汉字3个字节
SELECT LENGTH('join de');
SELECT LENGTH('张 de');

SHOW VARIABLES LIKE '%char%'

#2. concat 拼接字符串
SELECT CONCAT(`last_name`,'_',`first_name`) AS 姓名 FROM `employees`;

#3. upper lower
SELECT UPPER('join');
SELECT LOWER('join');
#案例 姓大写，名小写然后拼接
SELECT CONCAT(UPPER(`last_name`),'_',LOWER(`first_name`)) AS 姓名 FROM `employees`;

#4. substr、substring 注意索引从1开始
SELECT SUBSTR('张星星奥莉安娜',4) AS 结果; #奥莉安娜
SELECT SUBSTR('张星星奥莉安娜刘诗诗',4,4) AS 结果; #奥莉安娜
#案例：姓名中首字母大写其余字母小写用_拼接 
SELECT CONCAT(UPPER(SUBSTR(`last_name`,1,1)),LOWER(SUBSTR(`last_name`,2)),'_',LOWER(`first_name`)) FROM `employees`;

#5 instr 返回子串第一次出现的起始索引 
SELECT INSTR('布隆迦娜','迦娜'); #3

#6. trim 去掉前后空格
SELECT LENGTH(TRIM('   zs李四       ')) AS out_put;
SELECT TRIM('c' FROM  'cccc zs李ccc四     cc') AS out_put;

#7. lpad 用指定的字符实验指定的长度，左填充
SELECT LPAD('诺克萨斯',10,'^') AS out_put;


#8. rpad 用指定的字符实验指定的长度，右填充
SELECT RPAD('诺克萨斯',10,'^') AS out_put;

#9. replace 替换
SELECT REPLACE('布隆索尔奥莉安娜','布隆','亚索') AS 结果;

#二. 数学函数

#round 四舍五入
SELECT ROUND(-1.4); #-1
SELECT ROUND(1.567,2);

#ceil 向上取整 
SELECT CEIL(-1.1); -1

#floor 向下取整
SELECT FLOOR(-1.1);

#truncate 截断
SELECT TRUNCATE(-1.111111,1); #-1.1

#mod 取模 mod(a,b) a - a/b * b
SELECT MOD(-10,3); #-1
SELECT MOD(10,-3); #1
SELECT MOD(-10,-3); #-1

#三. 日期函数
#now 返回当前系统时间加日期
SELECT NOW();

#curdate 返回当前日期
SELECT CURDATE();

#curtime 返回当前时间
SELECT CURTIME();

#获取指定的时间  year month day hour 
SELECT YEAR(NOW());

#str_to_date 将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('2001-1-19','%Y-%c-%d') AS out_put;

#date_format 将日期转化为字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS 结果;
#查询有奖金的员工名和入职日期
SELECT `last_name`,DATE_FORMAT(`hiredate`,'%m月/%d日 %y年') AS 入职日期
FROM `employees`
WHERE `commission_pct` IS NOT NULL;

#四、其他函数
SELECT VERSION();
SELECT DATABASE();# 查看当前数据库
SELECT USER();

#五、流程控制函数
#1.if
SELECT IF(10 > 5 ,'大','小');

SELECT `last_name`,`commission_pct`,IF(`commission_pct` IS NULL,'无','有') AS 备注 
FROM `employees`;

#2.case函数
/*
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
...
else 结尾 要显示的语句n
end
*/

#案例1 查询员工的工资要求：
/*
部门号=30 显示的工资为1.1倍
部门号=40 显示的工资为1.2倍
部门号=50 显示的工资为1.3倍
其他部门显示为原工资
*/
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
when 条件2 then 要显示的值2
...
else 要显示的n
end
*/
#案例 查询员工的工资情况
/*
工资大于 20000 A
工资大于 15000 B
工资大于 10000 C
否则显示 D
*/
SELECT `salary` 原始工资,
CASE
	WHEN `salary` > 20000 THEN 'A'
	WHEN `salary` > 15000 THEN 'B'
	WHEN `salary` > 10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM `employees`;

五、分组函数

#分组函数
/*
功能：用作统计使用
分类：
	sum avg max,min,count;
特点：
1. sum avg 数值型
   max min count 可以处理任何类型
2. 是否忽略null 
    以上5个都忽略null值
3. 可以和distinct搭配使用实现去重运算
4. count函数  
*/
#1. 简单使用
SELECT SUM(`salary`) FROM `employees`;
SELECT AVG(`salary`) FROM `employees`;
SELECT MAX(`salary`) FROM `employees`;
SELECT MIN(`salary`) FROM `employees`;
SELECT COUNT(`salary`) FROM `employees`;

SELECT SUM(`salary`) SUM, ROUND(AVG(`salary`),2) AVG,MAX(`salary`) MAX FROM `employees`;

#2. 参数支持哪些类型
# sum and avg 一般支持数值型
SELECT SUM(`last_name`) ,AVG(`last_name`) FROM `employees`; # 无实际意义

# max min 支持字符型 日期型
SELECT MAX(`last_name`) , MIN(`last_name`) FROM `employees`;
SELECT MAX(`hiredate`) , MIN(`hiredate`) FROM `employees`;

# count 计算不为null的个数
SELECT COUNT(`commission_pct`) ,COUNT(`last_name`) FROM `employees`;

#3. 是否忽略null 
SELECT SUM(`commission_pct`),AVG(`commission_pct`) FROM `employees`; # 忽略
SELECT MAX(`commission_pct`),MIN(`commission_pct`) FROM `employees`; # 忽略

#4. 和distinct搭配
SELECT SUM(DISTINCT `salary`),SUM(salary) FROM `employees`;

#5. count函数的详细介绍
SELECT COUNT(`salary`) FROM `employees`;
SELECT COUNT(*) FROM `employees`;

SELECT COUNT(100) FROM `employees`;

#6. 和分组查询一起的字段有限制

六、分组查询

# 分组查询
/*
语法：
	select 分组函数,列(要求出现在group by的后面)
	from 表名
	【where 筛选条件】
	group by 分组的列表
	【order by】
注意：
	查询列表中必须出现要分组的列
特点：
	1、分组查询中的筛选条件分为两类
			数据源			位置			关键字
	分组前		原始表			group by子句的前面	where
	分组后		分组后的结果集		group by子句的后面	having
	
	①分组函数做为条件放在having语句中
	②能放在where的最好放在where后面
	
*/
#案例 查询每个部门的平均工资
SELECT AVG(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`;

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

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

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

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

#添加分组后的筛选条件

#案例1 查询那个部门的员工个数大于2
# ①查询每个部门的员工个数
SELECT COUNT(*) AS 员工个数,`department_id`
FROM `employees`
GROUP BY `department_id`;
# ②根据①的结果筛选
SELECT COUNT(*) AS 员工个数,`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING COUNT(*) > 2;

#案例2 查询每个工种有奖金的员工工资，且最高工资大于12000
SELECT MAX(`salary`),`job_id`
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `job_id`
HAVING MAX(`salary`) > 12000;

#案例3 查询领导编号大于102的每个领导手下的最低工资大于5000 的领导编号 及其最低工资
SELECT MIN(`salary`) 工资,`manager_id` 
FROM `employees`
WHERE `manager_id` > 102
GROUP BY `manager_id`
HAVING 工资 > 5000;

#按表达式或者函数分组
#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些
SELECT COUNT(*) , LENGTH(`last_name`) len_name
FROM `employees`
GROUP BY len_name
HAVING COUNT(*) > 5;

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

#添加排序
#案例 查询每个部门每个工种的平均工资 从高到低
SELECT AVG(`salary`),`department_id`,`job_id`
FROM `employees`
GROUP BY `job_id`,`department_id`
ORDER BY AVG(`salary`) DESC;

七、连接查询

#连接查询
/*
	多表查询
分类：
	sql92：仅仅支持内连接
	sql99：不支持全外连接
	
	按功能：
	内连接：
		等值连接
		非等值连接
		自然连接
	外连接：
		左外连接
		右外连接
		全外连接
	交叉连接
	
*/

1. sql92语法标准

#一、 sql92标准
#1. 等值连接
#案例1 查询女生对应的男生名
SELECT b.`boyName`,be.`name`
FROM `boys` b,`beauty` be
WHERE b.id = be.`boyfriend_id`;

#案例2 查询员工名和对应的部门名
SELECT `last_name`,`department_name`
FROM `departments` d,`employees` e
WHERE e.`department_id` = d.`department_id`;

#2. 为表起别名
#查询员工名，工种号，工种名·
SELECT `last_name`,j.`job_id`,`job_title`
FROM `employees` e,`jobs` j
WHERE e.`job_id` = j.`job_id`;

#3. 两个表的顺序可以交换
#4. 添加筛选条件
#案例1 查询有奖金的员工名部门名
SELECT `last_name`,d.`department_name`
FROM `employees` e,`departments` d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL;

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

#5. 添加分组查询
#案例1 查询每一个城市的部门个数
SELECT l.city,COUNT(*) 个数
FROM `locations` l,`departments` d
WHERE l.`location_id` = d.`location_id`
GROUP BY l.`location_id`;

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

#添加排序
#案例 查询每个工种的工种名和员工个数，按员工个数降序
SELECT COUNT(*) 员工个数, `job_title`
FROM `employees` e, `jobs` j
WHERE e.`job_id` = j.`job_id`
GROUP BY e.`job_id`
ORDER BY 员工个数 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`;

#2. 非等值连接
#案例1 查询员工的工资和工资级别
SELECT `salary`,`grade_level`
FROM `employees` e,`job_grades` j
WHERE e.`salary` BETWEEN `lowest_sal` AND `highest_sal`;

#3、自连接
#案例 查询员工名和员工的领导名
SELECT e1.`last_name` 员工名,e2.`last_name` 领导名
FROM `employees` e1,`employees`e2
WHERE e1.`manager_id` = e2.`employee_id`;

2. sql99语法标准

#二、sql99语法
/*
语法：
	select 查询列表
	from 表1 别名 【连接类型】
	join 表2 别名 
	on 连接条件
	【where 筛选条件】
	【group by 分组】
	【having 分组后筛选】
	【order by 排序】
分类：
内连接：inner
	
外连接
	左外 left 【outer】
	右外 right 【outer】
	全外 full 【outer】
交叉连接 cross

*/

#一、内连接 inner 可以省略
#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 `last_name` LIKE '%e%';

#案例3 查询部门个数大于3的城市名和部门个数
SELECT COUNT(*) 部门个数,`city`
FROM `departments` d
INNER JOIN `locations` l
ON l.`location_id` = d.`location_id`
GROUP BY l.`location_id`
HAVING 部门个数 > 3;

#案例4 查询部门的员工个数大于3的员工个数和部门名 ，并按员工个数降序
SELECT COUNT(*) 员工个数,`department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id` = d.`department_id`
GROUP BY e.`department_id`
HAVING 员工个数 > 3
ORDER BY 员工个数 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;

#2、非等值连接
#案例1 查询员工的工资级别
SELECT `salary`,`grade_level`
FROM `employees` e
JOIN `job_grades` j
ON e.`salary` BETWEEN `lowest_sal` AND `highest_sal`;

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

#3、自连接
#案例1 查询员工名和员工的领导名
SELECT e.`last_name` ,m.`last_name`
FROM `employees` e
JOIN `employees` m
ON e.`manager_id` = m.`employee_id`;

#二、外连接
/*
应用场景： 查询一个表中有，另一个表中没有的字段
特点：
1.从表有则显示，无就为null
2.左外连接 left join左边是主表
  右外连接 right join右边是主表
3. 
*/
#案例 查询男朋友不在男生表中的女生名
SELECT be.`name`
FROM `beauty` be
LEFT JOIN `boys` b
ON be.`boyfriend_id` = b.`id`
WHERE b.id IS NULL;


SELECT be.`name`
FROM `boys` b
RIGHT JOIN  `beauty` be
ON be.`boyfriend_id` = b.`id`
WHERE b.id IS NULL;

#案例1 查询那个部门没有员工
#左外 
SELECT d.*,e.`employee_id`
FROM `departments` d
LEFT JOIN `employees` e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL; 
#右外
SELECT d.*,e.`employee_id`
FROM `employees` e
RIGHT JOIN  `departments` d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL; 

#全外连接 不支持
SELECT b.*, bo.*
FROM `beauty` b
FULL JOIN `boys` bo
ON b.`boyfriend_id` = bo.`id`;

#交叉连接 笛卡尔积
SELECT b.*, bo.*
FROM `beauty` b
CROSS JOIN `boys` bo;

八、子查询

#子查询
/*
含义：
出现在其他语句中的select语句
分类：
按子查询出现的位置：
	select后：
		标量子查询
	from后：
	查询
	where或having后：
		标量子查询 
		列子查询：
		
		行子查询
	exists后（相关子查询）：
	查询
按结果集的行列数：
	标量子查询：结果集一行一列
	列子查询：结果集一列多行
	行子查询：结果集一行多列
	查询：都可以
*/
#一、where或having后：
/*
特点：
	子查询放在小括号内
	子查询一般在条件的右侧
	标量子查询，一般搭配单行操作符使用 > < = ...
	列子查询，一般搭配多行操作符使用 in,any,some,all
	子查询优先与主查询
*/
#1. 标量子查询
#案例1 查询谁（员工姓名）的工资比 Abel 高
select `last_name` ,`salary`
from `employees` 
where `salary` > (SELECT `salary` FROM `employees` WHERE `last_name` LIKE 'Abel');

#案例2 查询job_id与141号员工相同，`salary`比143号员工多的员工 姓名，`job_id`和工资
select `last_name`,`job_id`,`salary`
from `employees`
where `job_id` = (
	select `job_id` from `employees` where `employee_id` = 141
)
and `salary` > (
	select `salary` from `employees` where `employee_id` = 143
);

#案例3 查询公司工资最少的员工的`last_name`,`job_id`,`salary`
select `last_name`,`job_id`,`salary`
from `employees`
where `salary` = (
	select min(`salary`) from `employees`
);

#案例4 查询最低工资大于50号部门最低工资的部门id和其最低工资
select `department_id`,min(`salary`)
from `employees`
group by `department_id`
having MIN(`salary`) > (
	select min(`salary`) from `employees` where `department_id` = 50
);

#2. 列子查询
/*
not in == <>any
in == (=any)
*/
#案例1 查询`location_id`是1400或1700的部门中的所有员工姓名
select `last_name`
from `employees` 
where `department_id` in (
	select `department_id` from `departments` where `location_id` in(1400,1700)
);

#案例2 查询其他工种中比`job_id`为IT_PROG的工种中任意一个员工工资低的员工信息：`employee_id`，`last_name`，`job_id`，`salary`
select `employee_id`,`last_name`,`job_id`,`salary`
from `employees`
where `job_id` <> 'IT_PROG'
and `salary` < any(
	select `salary` from `employees` where `job_id` = 'IT_PROG'
);

SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE `job_id` <> 'IT_PROG'
AND `salary` < (
	SELECT max(`salary`) FROM `employees` WHERE `job_id` = 'IT_PROG'
);

#案例3 查询其他工种中比`job_id`为IT_PROG的工种中所有员工工资低的员工信息：`employee_id`，`last_name`，`job_id`，`salary`
SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM `employees`
WHERE `job_id` <> 'IT_PROG'
AND `salary` < (
	SELECT min(`salary`) FROM `employees` WHERE `job_id` = 'IT_PROG'
);

#3、行子查询
#案例1 查询员工编号最小并且工资最高的员工信息
select * 
from `employees`
where (`employee_id`,`salary`) = (
	select min(`employee_id`),max(`salary`) 
	from `employees`
);

#二、select后
#案例1 查询每个部门的员工个数
select d.*,(
	select count(*) from `employees` e 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.`department_id` = d.`department_id`
	where e.`employee_id` = 102
) 部门名;

#三、from后面
#案例 查询每个部门的平均工资的工资等级
select avg(`salary`),`department_id`
from `employees`
group by `department_id` 

select ag_dep.*,j.`grade_level`
from (
	SELECT AVG(`salary`) ag,`department_id`
	FROM `employees`
	GROUP BY `department_id` 
) ag_dep
inner join `job_grades` j
on ag_dep.ag between `lowest_sal` and `highest_sal`;

#四、exists后面（相关子查询）
/*
语法：
exists(完整的查询语句)
结果： 0或1
*/
#案例1 查询有员工的部门名
select `department_name`
from `departments` d
where exists(
	select * from `employees` e where d.`department_id` = e.`department_id`
);

select `department_name`
from `departments`
where `department_id` in (
	select `department_id` from `employees`
);

九、分页查询

#分页查询
/*
应用场景：
	要显示的数据需要分页显示
语法：
	select 查询列表      	       7
	from 表1                       1
	【join】表2                    2
	on 连接条件		       3
	【where 筛选条件】             4
	group by 分组字段	       5
	having 分组后筛选              6
	order by 排序字段	       8
	limit 起始索引，显示条目数     9
	起始索引从0开始
特点：
	放在查询语句的最后，并且最后执行
	page size
	size = 10
	page
	1	0
	2	10
	起始索引 (page - 1) * size
	
*/
#案例1 查询前5条的员工信息
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 `salary` DESC
LIMIT 10;

十、union联合查询

#联合查询
/*
union 联合：将多条查询语句的结果合并成一个结果
语法：
	查询语句1
	union
	查询语句2
特点：
	要求多条语句的查询列是一致的
	查询的每一列的顺序要一致
	使用union会去重
	union all 不会去重
*/
#案例1 查询部门编号大于90，或者邮箱中包含a的员工信息
SELECT * FROM `employees` WHERE `department_id` > 90
UNION 
SELECT * FROM `employees` WHERE `email` LIKE '%a%';

5. DML数据操作语言

一、插入： insert

#一、插入语句
/*
语法：
insert into 表名（列名,...）
values(值1,...);

要求：
插入的值的类型要与列的类型一致或兼容
插入的列的顺序可以调换
列和值的个数必须一致

*/
#方式一
INSERT INTO `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`)
VALUES(14,'索尔','女','2000-1-1','1254562',NULL,3);

#支持多行插入
INSERT INTO `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`)
VALUES(15,'索尔','女','2000-1-1','1254562',NULL,3),
(16,'索尔','女','2000-1-1','1254562',NULL,3),
(17,'索尔','女','2000-1-1','1254562',NULL,3);

#支持子查询
INSERT INTO `beauty`(`id`,`name`,`phone`)
SELECT 18,'迦娜','9999111';

#方式二
/*
insert into 表名
set 列名=值, 列名=值...
*/
INSERT INTO `beauty`
SET id = 13,`name` = '奥莉安娜',`phone` = '123456';

二、修改： update

#二、修改语句
/*
1.修改单表记录
语法：
update 表名
set 列=值,列=值
where 筛选条件;

2.修改多表记录
语法：
sql92语法：
update 表1 别名,表2 别名
set 列=值,列=值...
where 连接条件
and 筛选条件;

sql99语法：
update 表1 别名
[inner,left,right] join 表2 别名
on 连接条件
set 列=值,列=值...
where 筛选条件;

*/
#1.修改单表记录
#案例1 修改`beauty`名字带‘索’的电话为123234
UPDATE `beauty` SET `phone` = '123234' WHERE `name` LIKE '%索%';

#2. 修改多表记录
#案例1 修改张无忌女朋友的手机号为114
UPDATE `boys` bo
INNER JOIN `beauty` b
ON b.`boyfriend_id` = bo.`id`
SET `phone` = '114'
WHERE bo.`boyName` = '张无忌';

#案例2 修改没有男朋友的女生的男朋友编号为2
UPDATE `beauty` b
LEFT JOIN `boys` bo 
ON bo.`id` = b.`boyfriend_id`
SET b.`boyfriend_id` = 2
WHERE bo.id IS NULL;

三、删除： delete

#三、删除语句
/*
方式一 delete
有返回值、可以回滚
语法：
1. 单表删除
delete from 表名 where 筛选条件

2.多表删除
sql92语法：
delete 别名【删除表1的记录就是表1的别名，都删就都写别名】
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;

sql99语法：
delete 别名【删除表1的记录就是表1的别名，都删就都写别名】
from 表1 别名
[inner,left,right] join 表2 别名
on 连接条件
where 筛选条件;

方式二 truncate
语法：
truncate table 表名;
删除整个表的数据，但不删除表；

特点：
删除自增长列后从1开始自增长
无返回值
不能回滚
*/
#单表删除
#案例1 删除手机号为9结尾的女生信息
DELETE FROM `beauty` WHERE `phone` LIKE '%9';

#多表删除
#案例1 删除张无忌女朋友的信息
DELETE b
FROM `boys` bo 
INNER JOIN `beauty` b ON bo.`id` = b.`boyfriend_id`
WHERE bo.`boyName` LIKE '张无忌';

#案例2 删除黄晓明和他女朋友的信息
DELETE b,bo
FROM `boys` bo 
INNER JOIN `beauty` b ON bo.`id` = b.`boyfriend_id`
WHERE bo.`boyName` LIKE '黄晓明';

#方式二：truncate语句
#案例：将魅力值大于100的男生删除
TRUNCATE TABLE `boys`; #不能添加筛选条件只能删除整个表的记录

6. DDL数据定义语言

一、库的管理创建修改删除

#一、库的管理

#1. 库的创建
/*
语法：
create database 库名;
*/
#案例1 创建库books
CREATE DATABASE IF NOT EXISTS books;

#2. 库的修改 
/*
一般不会去修改库
*/
#更改库的字符集
ALTER DATABASE `books` CHARACTER SET gbk;

#3. 库的删除
DROP DATABASE IF EXISTS `books`;

二、表的管理 创建修改删除复制

#二、表的管理
#1，表的创建
/*
create table 表名(
	列名 列的类型【(长度) 列的约束】,
	列名 列的类型【(长度) 列的约束】,
	...
	列名 列的类型【(长度) 列的约束】
);
*/
#案例1 创建表book
CREATE TABLE book(
	id INT,#编号
	bName VARCHAR(20),#书名
	price DOUBLE, #价格
	authorId INT, #作者编号
	publish DATETIME#出版日期
);
#查看表的结构
DESC `book`;

#案例2 创建表author
CREATE TABLE IF NOT EXISTS author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)
)

DESC author;

#2. 表的修改
/*
ALTER TABLE 表名 【add|change|drop|modify】 COLUMN 列名 【列的类型 约束 】;

*/

#①修改列名
#案例1 修改表`book`中的publish为pubDate
ALTER TABLE `book` CHANGE COLUMN publish pubDate DATETIME;

#②修改列的类型和约束
#案例1 修改表`book`中的pubDate类型为TIMESTAMP
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
DESC `book`;

#③添加列
#案例1 给author表中添加列 annual 年薪
ALTER TABLE author ADD COLUMN annual DOUBLE;
DESC `author`;

#④删除列
#案例1 删除author表列 annual 
ALTER TABLE author DROP COLUMN annual;

#⑤修改表名
ALTER TABLE author RENAME TO book_author;
DESC `book_author`;

#3. 表的删除
DROP TABLE IF EXISTS book_author;
#查看当前库的所有表
SHOW TABLES;

#4.表的复制
#先插入数据
INSERT INTO author VALUES(1,'刘慈欣','中国'),
(2,'大冰','中国'),(3,'辰东','中国'),(4,'海森伯','德国');

#①仅仅复制表的结构
CREATE TABLE copy LIKE author;

SELECT * FROM copy;

#②赋值表的结构加数据
CREATE TABLE copy2 
SELECT * FROM `author`;

SELECT * FROM copy2;

#③只复制部分数据
CREATE TABLE copy3
SELECT * FROM `author` WHERE `nation` LIKE '中国';

SELECT * FROM copy3;

#④仅仅复制部分结构不包括值
CREATE TABLE copy4 
SELECT id,au_name
FROM `author`
WHERE 0;

SELECT * FROM copy4;

三、常见约束

1. 六大约束

#常见约束
/*
含义：用于限制表中的数据，为了保证表中数据的一致性
分类：
	NOT NULL : 非空约束，该字段不能为空
	DEFAULT: 默认约束，该字段有默认值
	PRIMARY KEY :主键约束，非空且唯一
	UNIQUE:唯一约束，可以为空
	CHECK：检查约束，【mysql不支持】 比如，年龄或者性别
	FOREIGN KEY:外键约束，用于限制两个表的关系
		在从表添加外键约束，用于引用主表中的某一列值
	
添加约束的时机：
	1.一般在创建表的时候添加约束添加
	2.还可以在修改表的时候添加约束
	*一定在数据添加之前添加约束
约束的添加分类：
	列级约束：
		六大约束语法上都可以，但是外键约束无效果
	表级约束：
		处理非空约束NOT NULL 和默认约束DEFAULT 其他都支持
		
主键和唯一的对比：
		保证唯一性	是否允许为空	一个表中可以有多少	是否允许组合
	主键	是		否		至多一个		允许
	唯一	是		是		可以有多个		允许
外键特点：
	1. 要求在从表设置外键关系
	2. 从表外键类的类型要和主表关联列的类型一致或者兼容，名称不做要求
	3. 主表中的关联列必须是key 主键或者唯一
	4. 插入数据时 先给主表添加信息再给从表添加
	5. 删除时先删除从表，再主表

CREATE TABLE 表名(
	字段名 字段类型 列级约束,
	字段名 字段类型,
	表级约束
);
*/

2. 创建表时添加约束

#一、创建表时添加约束
CREATE DATABASE students;
USE students;

#1.添加列级约束
/*
直接在字段的类型后面追加 约束类型即可
	只支持 主键 PRIMARY KEY 、默认 DEFAULT、非空 NOT NULL、唯一 UNIQUE
*/
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 #reference major(id) #外键 也不支持
);
DESC stuinfo;
#查看表中所有的索引,包括主键、外键、唯一
SHOW INDEX FROM `stuinfo`;

#2.添加表级约束
/*
语法 在最后一个字段的后面
【CONSTRAINT 外键名】 约束类型(字段名)
*/
DROP TABLE IF EXISTS `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='女'),
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)
);
SHOW INDEX FROM `stuinfo`;

#通用写法
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuName VARCHAR(20) NOT NULL,
	gender CHAR(1),
	seat INT UNIQUE ,
	age INT DEFAULT 18,
	majorId INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)
);

3. 修改表时添加和删除约束

#二、 修改表时添加约束
/*
1. 添加列级约束
ALTER TABLE `表名 MODIFY COLUMN 字段名 字段类型 新约束;
2. 添加表级约束
ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 约束类型(字段名);

*/
DROP TABLE IF EXISTS `stuinfo`;
CREATE TABLE stuinfo(
	id INT,
	stuName VARCHAR(20) ,
	gender CHAR(1),
	seat INT ,
	age INT ,
	majorId INT
)
DESC `stuinfo`;

#1. 添加非空约束
ALTER TABLE `stuinfo` MODIFY COLUMN stuname VARCHAR(20) NOT 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` MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE `stuinfo` ADD UNIQUE(seat);

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

#三、修改表时删除约束
#1. 删除非空约束
ALTER TABLE `stuinfo` MODIFY COLUMN stuname VARCHAR(20);

#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` MODIFY COLUMN seat;
#②表级约束
ALTER TABLE `stuinfo` DROP INDEX seat;

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

四、标识列

#标识列
/*
又称为自增长列
含义：可以不用手动插入值，系统提供默认的序列值
特点：
1、标识列必须和主键搭配吗？
	不一定，主键、唯一、外键
2、一个表中只能有一个标识列。 至多一个
3、标识列的类型，只能是数值型 int,double..
4. 标识列可以通过 SET auto_increment_increment=3;设置步长
*/
#一、创建表时设置标识列
CREATE TABLE tab_identity(
	id INT ,
	NAME VARCHAR(20)
);
#可以多次执行
INSERT INTO tab_identity VALUES(NULL,'join');
SELECT * FROM tab_identity;

SHOW VARIABLES LIKE '%auto_increment%';

#二、修改表时设置标识列
ALTER TABLE `tab_identity` MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

#三、修改表时删除标识列
ALTER TABLE `tab_identity` MODIFY COLUMN id INT PRIMARY KEY;