浅谈MySQL记录一下MySQL的入门基础知识，核心为DQL，其他为方便查阅。其中案例所用的表如下，如需可自取。代码

写在前面

记录一下MySQL的入门基础知识，核心为DQL，其他为方便查阅。
其中案例所用的表如下，如需可自取。
链接：pan.baidu.com/s/1ss17w6BU… 提取码：2pfh
代码都亲测有效，有误之处请指正，或者私聊我来改正。

一_数据库相关概念

数据库的好处：
- 持久化数据到本地
- 可以实现结构化查询，方便管理
DB：数据库，保存一组有组织的数据的容器
DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
SQL: 结构化查询语言，用于和DBMS通信的语言

二_数据库存储数据的特点

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

三_MySQL产品的介绍和安装

3.1_MySQL服务的启动和停止

方式一：计算机——右击管理——服务
方式二：通过管理员身份运行
- net start 服务名（启动服务）
- net stop 服务名（停止服务）

3.2_MySQL服务的登录和退出

方式一：通过mysql自带的客户端,只限于root用户（Command Line Client）
方式二：通过windows自带的客户端
- 登录： mysql 【-h主机名 -P端口号】-u用户名 -p密码
- 退出： exit或ctrl+C

3.3_MySQL的常见命令

1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(
	列名 列类型,
	列名 列类型,
	。。。
);
6.查看表结构
desc 表名;
7.查看服务器的版本
	方式一：登录到mysql服务端
	select version();
	方式二：没有登录到mysql服务端
	mysql --version
	或
	mysql --V

3.4_MySQL的语法规范

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

四_SQL的语言整体分类

DQL（Data Query Language）：数据查询语言 eg: select
DML(Data Manipulate Language) : 数据操作语言 eg: insert 、update、delete
DDL（Data Define Languge）：数据定义语言 eg: create、drop、alter
TCL（Transaction Control Language）：事务控制语言 eg: commit、rollback

五_DQL语言

进阶1：基础查询

语法： SELECT 要查询的东西【FROM 表名】;

一. 类似于Java中 :System.out.println(要打印的东西);
	特点：
	① 通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
	② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

二. 为了方便和便于理解，或者是和字段有冲突的时候，可以起别名，有两种方式：
	①: as
	②: 空格 
	eg: select last_name as 姓，first_name as 名 from employees;

三. 可以去重
	去重关键字为： distinct 
	示例：查询员工表中涉及到的所有的部门编号。
	eg： select distinct department_id from employees;
	
四. mysql中的+号仅仅就只有一个功能，那就是运算符，几种情况如下：
	①：select 100+90;  两个操作数都为数值型，则做加法运算
	②：select '123'+90; 只要其中一方为字符型，试图将字符型数值转换成数值型 如果转换成功，则继续做加		法运算
	③：select 'john'+90;	如果转换失败，则将字符型数值转换成0
	④：select null+10; 只要其中一方为null，则结果肯定为null

进阶2：条件查询

条件查询：根据条件过滤原始表的数据，查询到想要的数据
语法： select 要查询的字段|表达式|常量值|函数 from 表 where 条件 ;

分类：
一、条件表达式
	示例：salary>10000
	条件运算符：
	> < >= <= = != <>
	
二、逻辑表达式
	示例：salary>10000 && salary<20000

	逻辑运算符：
		and（&&）:两个条件如果同时成立，结果为true，否则为false
		or(||)：两个条件只要有一个成立，结果为true，否则为false
		not(!)：如果条件成立，则not后为false，否则为true  注意（）的使用
		
	示例：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
	select * from employees where 
	not(department_id >= 90 and department_id<=110) or salary>15000;

三、模糊查询
	3.1 like  一般和通配符搭配使用  通配符：① % 任意多个字符，包含0个字符 ② _ 任意单个字符
		示例： 查询员工名中第二个字符为_的员工名 （冲突解决:escape 转义）
		select last_name from employees where last_name like '_$_%' escape '$';
	
	3.2 between and 可以提高语句的简洁度
		注意： 包含临界值 并且 两个临界值不要调换顺序
		示例：查询员工编号在100到120之间的员工信息
		select * from employees where employee_id between 100 and 120;
	
	3.3 in
		注意： ① in列表中的值类型必须一致或兼容 ② in列表中不支持通配符
		示例：查询员工的工种编号是 IT_PROG ， AD_VP ， AD_PRES 中的一个的员工名和工种编号
		select last_name , job_id from employees where job_id in (IT_PROG,AD_VP,AD_PRES);
		
	3.4 is null
		注意：= 或 <> 不能用于判断null值  is null 或 is not null 用于判断null值
			在mysql中，null不是一个值，而是代表没有值，即判断等于或者不等于的时候，null无效
			那么可以使用ifnull(x,值)去操作
		示例：查询有奖金的员工名和奖金率，假设无奖金的都为null
		select last_name,commission_pct from employees where commission_pct is not null
		
		若非要用=操作null 可以使用 安全等于 <=>
		示例： 查询没有奖金的员工名
		select last_name from employees where commission_pct <=> null
		对比：
		is null 仅仅可以判断null值，可读性高，建议使用
		<=> 既可以判断null值，又可以判断普通的数值，可读性稍低

进阶3：排序查询

语法： select 要查询的东西 from 表 where 条件 order by 排序的字段|表达式|函数|别名【asc|desc】

一. 特点：
	①： asc 代表升序 是默认的 可以省略， desc 代表的是降序
	②： order by 字句可以支持单个字段，别名，表达式，函数，多个字段
	③： order by 字句 在查询语句的最后面，除了limit字句
二. 示例：
	2.1 单个字段 添加筛选条件在排序
	eg: 查询部门编号>=90的员工信息，并按员工编号降序
	select * from employees where department_id >= 90 order by employee_id desc;
	
	2.2 按表达式查询
	eg: 查询员工信息 按年薪降序 可以按别名
	select *,salary*12*(1+ifnull(commission_pct,0)) as 年薪 from employees 
	order by 年薪 desc;
	
	2.3 按函数排序
	eg: 查询员工名，并且按名字长度降序
	select length(last_name) l,last_name from employees order by l desc;
	
	2.4 按多个字段排序
	eg:查询员工信息，要求先按工资降序，再按employee_id升序
	select * from employees order by salary desc,employee_id asc;

进阶4：常见函数

优点：类似java的方法，隐藏实现细节，提高代码复用性。
调用： select 函数名（实参列表） [ from 表 ]；
分类：单行函数和分组函数（做统计使用，又称为统计函数，聚合函数，组函数）

4.1 单行函数

字符函数

concat拼接

select concat(last_name,'_','first_name') 姓名 from employees;
若单纯拼接两个字符串，不要中间的拼接符即可

substr (substring)截取子串，两个用法一样

select substr('横看成岭侧成峰',5) out_put; #侧成峰 注意:索引从1开始
select substr('横看成岭侧成峰',3,4);  #成岭侧成  注意：第二个参数是个数，并且超过不管，结果就是取到最后一位

upper转换成大写 , lower转换成小写

eg: 把姓变成大写，名变成小写，然后拼接
select concat(super(last_name),lower(first_name)) 姓名 from employees;

trim去前后指定的空格和字符, ltrim去左边空格, rtrim去右边空格
```
select trim("   le boy   ") out_put; # le boy 
```

replace替换，是全部替换

select replace('你知道吗，是吗！','吗','吧') as out_put; #你知道吧，是吧！

lpad左填充 , rpad右填充

select lpad('为什么',2,'@') out_put; # 为什
select lpad('为什么',5,'@') as out_put; #@@为什么
select rpad('为什么',5,'@') out_put; #为什么@@

instr返回子串第一次出现的索引

select instr('正视自己哦','自己') as outt_put; #3

length 获取字节个数
```
select length('leboy');
```

数学函数和 java差不多，做部分演示
- round 四舍五入
```
SELECT ROUND(-1.5); # -2
```
- rand 随机数
```
SELECT RAND(); # [0,1)
```
- floor向下取整
- ceil向上取整
```
SELECT CEIL(-1.02); # -1
```
- mod取余 mod(a,b) === a-a/b*b;
```
SELECT MOD(10,-3); # 1
```
- truncate截断
```
select truncate(1.599999,2); # 1.59
```

日期函数

now 当前系统日期+时间

  select now();  # yyyy-MM-dd HH:mm:ss

curdate 当前系统日期不包含时间
```
select curdate(); # yyyy-MM-dd
```
curtime当前系统时间
year() month() day() 获取年月日
```
select year(now()); # 2020
```

str_to_date 将字符转换成日期

#eg: 查询入职日期为1992-4-3的员工信息 若存入的时候是其他格式
select * from employees where hiredate = str_to_date('4-3 1992','%c-%d %Y');

date_format 将日期转换成字符

# eg:查询有奖金的员工名 和入职日期（xx月/xx日 xx年）
select last_name,date_format(hiredate,'%m月/%d日 %y年') 入职日期
from employees where commission_pct is not null;

其他函数
- version 版本
- database 当前库
- user 当前连接用户
```
select version();
select database();
select user();
```

流程控制函数

if 处理双分支，类似java的三元运算符

#eg: 查询用工名并对有奖金和没奖金的人备注不同的信息
select last_name,if(commission_pct is null , '没奖金','有奖金') as 备注 
from employees;

case语句处理多分支

情况1：处理等值判断 switch case的效果

语法如下：
case 要判断的字段或表达式
when 常量2 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end

# eg: 查询员工的工资，要求部门号=30显示的工资为1.1倍; 部门号= 40 显示的工资为1.2倍；部门号=50， 显示工资为1.3倍；其他部门显示工资为原工资。

select salary 原始工资,department_id 部门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;

情况2：处理条件判断类似于多重if else

语法：
case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

# eg: 查询员工的工资情况 如果工资>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;

4.2 分组函数

sum 求和
max 最大值
min 最小值
avg 平均值
count 计数

特点：
	1. sum 和 avg 一般用于处理数值型，max(字典顺序最高) min count(统计存在的) 可以处理任意类型。
	2. 以上的分组函数都忽略null值，除了count(*)【其实实质是：只要存在的行，总有不为null的字段】
	3. 都可以搭配distinct使用，用于统计去重后的结果
	4. count的参数可以支持字段，常量值（一般是1），* ；一般用count(*)统计行数
	5. 和分组函数一同查询的字段要求是group by后的字段
eg:	
①： 简单示例：
select sum(salary) 和,round(avg(salary),2) 平均, max(salary) 最高,min(salary) 最低,count(salary) 个数 from employees;

②：配合distince使用
select count(distinct salary) 工资种类数,count(salary) 工资个数 from employees;

③：关于count(1) 与 count(*) 的效率比较
	myisam 存储引擎下 count(*) 的效率高 （计数器）
	innodb 存储引擎下（mysql5.5用的），count(*) 和 count(1)的效率差不多，比count(字段)要高一些
	综合来看： 就用count(*) 即可。

进阶5：分组查询

一 语法：
    select 查询列表 
    from 表
    [ where 筛选条件 ]
    group by 分组的字段
    [ order by 排序的字段 ];
    
二 特点：
	1. 和分组函数一同查询的字段必须是group by 后出现的字段
	2. 筛选分为两类：分组前筛选 和 分组后筛选 情况如下：
				 针对的表			位置			  连接的关键字
  (分组前筛选：)   原始表           group by 前         where
  (分组后筛选：)group by 的结果集   group by 后		 having
  	一般情况下：能用分组前筛选的，尽量用分组前筛选，可以提高效率
  	若是分组函数作条件筛选肯定是分组后筛选，放在having字句中
  	3. 分组可以按单个字段也可以按多个字段
  	4. 可以搭配排序使用
三 示例：
	# 注：若和分组函数一同查询的字段 不是group by后的字段，表的行数都对不齐！！
	
	3.1 查询每个工种的员工平均工资
	select avg(salary),job_id
	from employees 
	group by job_id;
	
	3.2 查询每个位置的部门个数
	select count(*),location_id
	from departments
	group by location_id;
	
	# 注：可以实现分组前的查询
	
	3.3 查询邮箱中包含a字符的每个部门的最高工资
	select max(salary),department_id from employees
	where email like '%a%'
	group by department_id;
	
	3.4 查询有奖金的每个领导手下员工的平均工资
	select avg(salary),manager_id from employees
	where commission_pct is not null
	group by manager_id;
	
	# 注：可以实现分组后的查询
	
	3.5 查询哪个部门的员工个数>5 
	select count(*),department_id from employees
	group by department_id
	having count(*)>5; #对结果进行筛选
	
	3.6 每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
	select job_id,max(salary) from employees
	where commission_pct is not null #分组前就可以筛掉一部分
	group by job_id
	having max(salary)>12000; # 分组后对结果再一次筛选
	
	3.7 领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
	select min(salary),manager_id from employees
	where manager_id>102
	group by manager_id
	having min(salary)>5000;
	
	# 注：添加排序
	
	3.8 每个工种有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序
	select job_id,max(salary) from employees
	where commission_pct is not null
	group by job_id
	having max(salary)>6000
	order by max(salary) asc;
	
	# 注：按多个字段分组
	
	3.9 查询每个工种每个部门的最低工资，并按最低工资降序
	select min(salary),department_id,job_id from employees
	group by department_id,job_id # 这里顺序调换也可以
	order by min(salary) desc;

进阶6：连接查询

又称为多表查询，即：查询的字段来自多个表。

# eg: 查询男女对应关系
select name,boyName from boys,beauty;
笛卡尔乘积现象： 表1 m行 与 表2 n行 查询的结果为 m*n行
原因： 没有有效的连接条件
避免： 添加有效的连接条件
select name,boyName from boys,beauty where beauty.boyfriend_id = boys.id;

6.1 分类

按年代分类：
- sql92标准：在mysql中仅仅支持内连接，oracle中支持部分外连接
- sql99标准：在mysql中除了全外连接，其他都支持，故推荐使用
按功能分类：
- 内连接
  - 等值连接
  - 非等值连接
  - 自连接
- 外连接
  - 左外连接
  - 右外连接
  - 全外连接
- 交叉连接

6.2 sql92标准

1. 等值连接
#eg： 查询女神名和对应的男神名
select name,boyName from boys,beauty where beauty.boyfriend_id = boys.id;

#eg: 查询员工名和对应的部门名
select last_name,department_name from employees,departments
where employees.department_id = departments.department_id;

#1.1 可以为表起别名：①提高简洁度 ②区分多个重名的字段 注意：一旦起了别名，就都使用别名，因为先执行from
#eg: 查询员工名、工种号、工种名
select last_name,e.job_id,job_title from employees e, jobs j #表顺序无所谓
where e.job_id = j.job_id; 

#1.2 可以加筛选条件，用的是and
#eg: 查询有奖金的员工名、部门名
select last_name,department_name,commission_pct from employees e,departments d
where e.department_id = d.department_id
and e.commission_pct is not null;

#1.3 可以加分组
#eg:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select department_name,d.manager_id,min(salary) from employees e,departments d
where e.department_id = d.department_id
and commission_pct is not null
group by department_name,d.manager_id;

#1.4  可以加排序
#eg: 查询每个工种的工种名和员工的个数，并且按员工个数降序
select job_title,count(*) from employees e,jobs j
where e.job_id = j.job_id
group by job_title
order by count(*) desc;

#1.5 可以多表连接
#eg: 查询员工名、部门名和所在的城市，城市名以s打头，部门名降序排序
select last_name,department_name,city 
from employees e,departments d,locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and city like 's%'
order by department_name desc;

多表等值连接小结：
① 等值连接的结果 = 多个表的交集
② n表连接，至少需要n-1个连接条件
③ 多个表不分主次，没有顺序要求
④ 一般为表起别名，提高阅读性和性能
⑤ 都可以和前面介绍的字句搭配使用，比如 筛选 分组 排序

#------------------------------------------------------------------------------

2. 非等值连接
#eg: 查询员工的工资和工资级别，只需工资级别为A
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';

非等值连接一样可以和之前的各种字句搭配，参考等值连接即可，不一一举例。

#------------------------------------------------------------------------------

3. 自连接
#eg: 查询员工名和上级的名称
select e.last_name,e.employee_id,m.last_name,m.employee_id
from employees e,employees m
where e.manager_id = m.employee_id;

6.3 sql99标准

#注：在内连接，外连接和交叉连接中，重点为三个内连接和左外，右外。

语法：
select 查询列表
from 表1
【inner|left [outer]|right [outer]|cross】 join 表2 on  连接条件
【inner|left [outer]|right [outer]|cross】 join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

# 相比sql92,语句上，连接条件和筛选条件实现了分离，简洁明了！

1. 内连接 
	# 语法就是使用inner
	select 查询列表 from 表1 别名 
	inner join 表2 别名
	on 连接条件;
	
1.1 等值连接
	①： 查询员工名、部门名
	select last_name,department_name from employees e
	inner join departments d
	on e.department_id = d.department_id;
	
	②：查询名字中包含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的城市名和部门个数（分组加筛选）
	select count(*) city from departments d
	inner join locations l
	where d.location_id = l.location_id
	group by city
	having count(*) > 3;
	
	④：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
	select count(*) 员工个数,department_name 部门 from employees e
	inner join departments d
	on e.department_id = d.department_id
	group by department_name
	having count(*) > 3
	order by count(*) desc;
	
	⑤：查询员工名、部门名、工种名、并按部门名降序(添加三表连接)
	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.2 非等值连接
	①： 查询员工的工资级别
	select grade_level,salary from employees e
	inner join job_grades j 
	on salary between lowest_sal and highest_sal; 
	
	②：查询工资级别的个数>20的个数，并按工资级别降序
	select count(*) 级别个数,grade_level 级别 from job_grades j
	inner join employees e
	
	on e.salary between lowest_sal and highest_sal
	group by grade_level
	having count(*) > 20
	order by grade_level desc;
	
1.3 自连接
	①：查询员工的名字,上级的名字
	select e.last_name 员工名,m.last_name 上级名 from employees e
	inner join employees m
	on e.manager_id = m.employee_id;
	
	②：查询姓名中包含字符k的员工的名字,上级的名字
	select e.last_name 员工名 , m.last_name 上级名 from employees e 
	inner join employees m
	on e.manager_id = m.employee_id
	where e.last_name like '%k%';
	
	#-------------------------------------------------------------------------
	
2.外连接
	应用场景：用于一个表中有，另一个表中没有的记录
	特点：
       ①： 外连接的查询结果为主表中的所有记录，从表中有与其匹配的显示匹配的值，反之显示null
           即：外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录， 后按要求筛选
       ②： 左外连接： left左边的是主表 ； 右外连接： right 右边的是主表
       ③： 左外和右外交换两个表的顺序，可以实现同样的效果
       ④： 全外连接 = 内连接的结果+表一有而表2无 + 表2有而表1无， 后按要求筛选
   # eg：查询男友不在男神表的女神名
   ①采用左外
   select g.name 女神名 from beauty g
   left outer join boys b
   on g.boyfriend_id = b.id
   where b.id is null;#注意：从表连接条件最好是主键，因为它非空，不会影响结果。
   ②采用右外
   select g.name 女神名 from boys b
   right outer join beauty g
   on b.id = g.boyfriend_id
   where b.id is null;
   
   # eg: 查询哪个部门没有员工
   ①采用左外
   select department_name 部门名 from departments d
   left outer join employees e
   on d.department_id = e.department_id
   where e.employee_id is null;
   ②采用右外
   select department_name 部门名 from employees e
   right outer join departments d
   on e.department_id = d.department_id
   where e.employee_id is null;
   
   对于全外连接：sql99不支持
   
3. 交叉连接：就是一个笛卡尔乘积。

小结：sql92 与 sql99
- 功能：sql99支持的更多
- 可读性：sql99实现连接条件和筛选条件的分离，可读性更高

进阶7：子查询

含义：一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询在外面的查询语句，称为主查询或外查询。
分类：
- 按子查询出现的位置：
  - select 后面：仅支持标量子查询
  - from 后面：支持表查询，就是个结果集
  - where或having后面：标量（★常用），列（★常用），【行】
  - exists后面（相关字查询）：表，就是个结果集
- 按结果集的行列数不同（功能）：
  - 标量子查询（结果集只有一列一行）
  - 列子查询（结果集为一列多行）
  - 行子查询（结果集一行多列）
  - 表子查询（结果集一般为多行多列，前三个也可称为表子查询（比较特殊而已））

7.1  where 或 having 后面
	1.标量子查询（单行子查询）
		# ①谁的工资比Abel高？
		select salary from employees where last_name = 'Abel'  #其结果为单行单列
		# 在上一个结果的基础上去继续查询
		select * from employees
        where salary > (select salary from employees where last_name = 'Abel');
        
        #② 返回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;
        # 第三步：再查符合条件的结果
        select last_name 姓名,job_id,salary 工资 from employees
        where job_id = (select job_id from employees where employee_id = 141)
        and salary > (select salary from employees where employee_id = 143);
        
        #③ 返回公司工资最少的员工的last_name,job_id和salary
        select last_name,job_id,salary from employees
        where salary = ( select min(salary) from employees );
        
        #④ 查询最低工资大于50号部门最低工资的部门id和其最低工资
        #准备：
        #select min(salary) from employees where department_id = 50
    	#查询：
        select min(salary),department_id 部门id from employees
        group by department_id
        having min(salary) > ( 
            select min(salary) from employees # 结果只能是一个工资，不能多个
            where department_id = 50 # 结果必须要有，不能没有
        );  
    #------------------------------------------------------------------------------    
	2.列子查询（多行子查询）
        # ① 返回location_id 是1400或1700的部门中的所有员工姓名
        #准备
        # 可能存在一样的多个部门id,可以去重增加效率
        select distinct department_id from departments
        where location_id in (1400,1700);
        #查询
        select last_name from employees 
        where department_id in (
            select distinct department_id from departments 
            where location_id in (1400,1700)
        );

        # ② 返回其他工种中比job_id为‘IT_PROG’工种任一工资低的
        #   员工的员工号、姓名、job_id、以及salary
        #准备
        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 max(salary) from employees where job_id = 'IT_PROG';
		#那就不用加any或者some了
		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';
        
        #③ 返回其他工种中比job_id为‘IT_PROG’工种所有工资都低的
        #  员工名、姓名、job_id、salary.
        第一种解法： any 换成 all
        第二种解法： max 换成 min
	
	 # 注1： 主查询条件 in （子查询结果） 等同于 主查询条件 = any(子查询结果)
	 # 注2： 主查询条件 not in（子查询结果） 等同于 主查询条件 <> all(子查询结果)
	
	#------------------------------------------------------------------------------
	3.行子查询（一行多列或者多列多 行）
		#①查询员工编号最小并且工资最高的员工信息 (结果不一定存在，这里表中存在)
		#准备1
		select min(employee_id) from employees;
		#准备2
		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
        )；

#-------------------------------------------------------------------------------------
7.2 放在select后面 (仅支持标量子查询)
	#① 查询每个部门的员工个数
	select d.*,(
        select count(*) from employees e
        where e.department_id = d.department_id
    ) 员工个数
    from departments d;
#-------------------------------------------------------------------------------------
7.3 放在from后面
	#① 查询每个部门的平均工资的工资等级
	#准备
	select avg(salary),department_id from employees
	group by department_id
	#查询
	select ag_dep.*,g.grade_level
	from (
        select avg(salary) ag,department_id from employees
	group by department_id
    ) ag_dep #子查询结果充当一张表，需起别名
    inner join job_grades g
    on ag_dep.ag between lowest_sal and highest_sal;
#------------------------------------------------------------------------------------
7.4 放在exists后面（相关子查询）
	#语法： exists(完整的查询语句) 结果： 1 或 0 是否存在
	# 其实可以用之前的查询方式代替 in
	#① 查询有员工的部门名
	select department_name from departments d
	where exists(
    	select * from employees e
        where d.department_id = e.department_id
    );
	#exists后面的子查询不像其他子查询，先有结果后主查询，它是先查出主查询的结果再按子查询的结果去做相应的筛选（理解：每扫描一条记录，就判断exists是否返回一个结果集，如果会那么这条记录保留，不会则这条记录不保留）
	
	#in：
	select department_name from departments d
	where d.department_id in(
    	select department_id from employees
    );
    
    # ② 查询没有女朋友的男神信息
    #in:
    select b.* from boys b
    where b.id not in (
    	select boyfriend_id from beauty
    );
	#exists
	select b.* from boys b
	where not exists(
    	select * from beauty g
        where g.boyfriend_id = b.id
    );

小结子查询特点：
- 子查询都会放在小括号内
- 子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
- 子查询优先于主查询执行，主查询使用了子查询的执行结果
- 子查询根据查询结果的行数不同分为以下两类：
  - 单行子查询：结果集只有一行，一般搭配单行操作符使用：> < = <> >= <=
    - 非法使用情况：①子查询结果为一组值 ②子查询结果为空
  - 多行子查询：结果集为多行，一般搭配的操作符：any/some , all , in , not in ;
    - in是属于其中一个就行，any和all往往可以用其他查询代替

进阶8：分页查询

应用场景：web开发中需要根据用户的需求提交对应的分页查询sql语句（每页显示多少条数据）。

语法：
	select 查询列表 from 表
	[ join type join 表2 ] 
	[ on 连接条件 ]
	[ where 筛选条件 ]
	[ group by 分组字段 ]
	[ having 分组后的筛选 ]
	[ order by 排序的字段 ]
	limit  [offset,] size; 
	# 之前的查询都可选择性搭配使用，offset为要显示条目的起始索引（这里起始索引居然又是从0开始了）
	# size 为要显示的条目个数

#eg①：查询前五条员工的信息
	select * from employees limit 0,5;
	select * from employees limit 5; #从头开始的查询可以省略offset

#eg②：查询第11条到低25条的员工信息
	select * from employees limit 10,15;
	
#eg③：有奖金的员工信息，并且工资较高的前10名显示出来
	select * from employees
	where commission_pct is not null
	order by salary desc
	limit 10;

特点：

1. limit子句放在查询语句的最后
2. 查询可以总结为公式：
	①要显示的页数为page(从1开始),每页的条目数为size
select 查询列表 from  表 limit (page-1)*size,size;

进阶9：联合查询

union 联合、合并：将多个查询语句的结果合并成一个结果。

#eg:查询部门编号>90或邮箱包含a的员工信息
#之前的查询也可以查：
select * from employees
where department_id>90 or email like '%a%';
#使用联合查询
select * from employees where department_id>90
union 
select * from employees where email like '%a%';

语法为： 查询语句1 union 查询语句2 union ... ;
应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致。

#eg①:查询中国用户之中男性的信息以及外国用户之中男性的信息
select id,name,sex from t_ca where sex = '男'  #字段的显示
union
select t_id,t_name,t_sex from t_ua where t_sex = 'male';
#另外比如：热搜词的查询结果也有可能是来自于n张表

#特别注意：
	①：要求多条查询语句的查询列数是一致的
	②：要求多条查询语句的查询的每列顺序和每一列的类型最好一致(虽不报错但信息与字段不对等)
	③：union默认去重，使用union all 可以包含重复项
#整体的一个展示：
select 查询列表 [ from 表 ] [ where 条件 ] union [ all ]
select 查询列表 [ from 表 ] [ where 条件 ] union [ all ]
.....
select 查询列表 [ from 表 ] [ where 条件 ]

六_DML语言

数据操作语言
- 插入 insert
- 修改 update
- 删除 delete

插入

语法

1. 语法1(经典插入)
	insert into 表名(字段名，...) values(值1, ...);
	
	# ①： 插入的字段类型和值类型一致或兼容，而且一一对应
	insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
	values(13,'唐艺昕','女','1990-4-23','18999999999',null,2); #照片类型为blob,先用null占位
	
	# ②：可以为null的列的字段若为空，可以不用插入值或用null填充，不能为空的字段必须插入值
	#方式一：
	insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
	values(13,'唐艺昕','女','1990-4-23','18999999999',null,2);
	#方式二：
	insert into beauty(id,name,sex,phone)
	values(14,'江疏影','女','18999999999');
	
	# ③：列的顺序无所谓，但是要一一对应
	insert into beauty(name,id,phone,sex)
	values('王丽坤',15,'18999999990','女');
	
	# ④：可以省略列名，默认所有列，而且列的顺序和表中的顺序一致
	insert into beauty 
	values(18,'小乔','女',null,'13166666666',null,null);
 
 语法2
 	insert into 表名
 	set 列名=值,列名=值, ...;
 	#eg:
 	insert into beauty
 	set id=19,name='刘涛',phone='110';
 	
 #两种插入语法比较：
  	①: 语法1方便插入多行,语法2不支持
  	insert into 表 values(),(), ...;
  	#eg:
  	insert into boys (boyName,userCP)
  	values('刘备',100),('曹操',500);
  	②：语法1支持子查询，语法2不支持
  	#eg:
  	insert into beauty(id,name,phone)
  	select 20,'大乔','13798989898'; 
  	# 这里可以是符合条件的其他表的数据，将查询的结果集对应插入到beauty表中

小结

2. 特点：
    2.1、插入的字段类型和值类型一致或兼容，而且一一对应
    2.2、可以为空的字段，可以不用插入值，或用null填充
    2.3、不可以为空的字段，必须插入值
    2.4、字段个数和值的个数必须一致
    2.5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致

修改

修改单表语法：

 update 表名 
 set 字段=新值,字段=新值... #所有行都更新
 [ where 条件 ]; # 往往与where搭配使用，修改某些行
 
 #eg ①：修改beauty表中姓唐的电话为13899889999
 update beauty set phone='13899889999'
 where name like '唐%';
 
 #eg ②: 修改boys表中id号为2的男神名称为张飞，魅力值为10
 update boys set boyName = '张飞',userCP = 10 #顺序无所谓
 where id = 2;

修改多表语法( 级联修改 )：

#sql92语法：(仅内连接)
    update 表1 别名1,表2 别名2
    set 字段=新值，字段=新值...
    where 连接条件
    and 筛选条件;
#sql99语法：
	update 表1 别名1
	inner|left|right join 表2 别名2
	on 连接条件
	set 字段=新值,...
	where 筛选条件;
	
# 级联修改也可以在外键约束后面加上 on update cascade 或者 on delete cascade;
    alter table 表 drop foreign key 外键名;
    alter table 表 add constraint 外键名  foreign key(外键列) references 主表(主键) on update cascade on delete cascade;
	
#eg：① 修改张无忌的女朋友的手机号为114 （内连）
	update boys b
	inner join beauty g on b.id = g.boyfriend_id
	set g.phone = '114'
	where b.boyName = '张无忌';
	
#eg: ② 修改没有男朋友的女神的男朋友编号都为2号。
	update boys b
	right join beauty g on b.id = g.boyfriend_id
	set g.boyfriend_id = 2
	where b.id is null;

删除

方式一：delete语句

#注意：删除的都是整行
1. 单表的删除 ★

# 语法：
delete from 表 where 筛选条件;  #删除满足条件的行

#eg: 删除手机号以9结尾的女神信息
	delete from beauty where phone like '%9';

2. 多表的删除（ 级联删除 ）：

#语法：
# ① sql92语法：
	delete 别名[删哪张表就写哪张表的别名，都删都写上] from 表1 别名1,表2 别名2
	where 连接条件
	and 筛选条件;
	
# ② sql99语法：
	delete [表1别名,表2别名]的表 from 表1 别名1
	inner|left|right join 表2 别名2 on 连接条件
	where 筛选条件;
	
#eg①:删除张无忌的女朋友的信息
	delete g from beauty g
    inner join boys b on g.boyfriend_id = b.id
    where b.boyName = '张无忌';
    
#eg②：删除黄晓明的信息以及他女朋友的信息
	delete b,g 
	from beauty g
	inner join boys b on b.id = g.boyfriend_id
	where b.boyName = '黄晓明';

方式二：truncate语句

#注意： truncate语句不能使用where , 即 一删就空
# 语法：
truncate table 表名;

truncate table boys; # 清空boys表中的数据 ， 清空数据的效率高。

两种方式的区别
- truncate不能加where条件，而delete可以加where条件
- truncate的效率稍高一点
- truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始，delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
- truncate 删除没有返回值，delete删除有返回值（eg: truncate返回的是0行受影响）
- truncate删除不能回滚，delete删除可以回滚

七_DDL语言

数据定义语言

库和表的管理

创建 create、修改 alter 、删除 drop

第一大类：库
1.1 创建库
    create database [ if not exists ] 库名
    # eg: create database books; 若再重新执行一次，则库名重复报错 
    # 所以容错性的处理： create database if not exists books;

1.2 对于库的修改，一般不建议代码操作了（容易出问题），而且一般对库不会有什么改动操作，若要更名，一般在服务器端改文件名重启服务即可（此情况也很少）。
	# 可以更改库的字符集：
	alter database books character set gbk; # [ 最后加字符集即可 ]

1.3 删除库
	drop database [ if exists ] 库名; #同创建一样可以先判断
	#eg: drop database if exists books;
	
第二大类：表
2.1 表的创建
	create table [ if not exists ] 表名(
    	列名 列类型[ (长度) 列的约束 ],
        列名 列类型[ (长度) 列的约束 ],
        ...
         列名 列类型[ (长度) 列的约束 ]
    )
	#eg: 在books库中创建book表
	create table book(
    	id int, # 各种长度约束详见后面内容
        bName varchar(20),
        price double,
        # author varchar(20), 若作者著过很多书
        # 可以另外建表存储作者 实现分类存储 减少冗余 存储作者ID即可
        authorId int,
        publishDate datetime #出版日期
    )
    # 创建表author
	create table author(
    	id int,
        au_name varchar(20),
        nation varchar(10)
    )
    
2.2 表的修改
	语法：
	alter table 表名 add|drop|modify|change column 列名 [列类型，约束];
	
	#① 修改列名 
	alter table book change [ column ] publishDate pubDate datetime;#仅这个不加建议都加col
	
	#② 修改列的类型或约束
	alter table book modify column pubDate timestamp;
	
	#③ 添加列
	alter table author add column annual double; # 加 年薪 列
	
	#④ 删除列
	alter table author drop column annual;
	
	#⑤ 修改表名
	alter table author rename to book_author;

2.3 表的删除
	drop table [ if exists ] book_author;
	
    #通用的写法：想要按照自己的思路涉及库就用以下写法，当然，若想要旧的一些数据则不行。
    drop database if exists 库名;
    create database 库名;

    drop table if exists 表名;
    create table 表名();

2.4 表的复制
	#① ：仅仅复制表的结构
	create table copy like author;
	#② : 复制表的结构+数据
	create table copy2 select * from author;
	
	# 也可以只复制部分数据，根据需求而定
	create table copy3 
	select id,au_name
	from author
	where nation = '中国';
	
	# 可以只复制部分表的字段，无数据
	create table copy4
	select id,au_name
	from author
	where 0;

常见数据类型

数值型：
	整型
	小数： 定点数  浮点数
字符型：
	较短的文本：char  varchar 
	较长的文本: text  blob(较长的二进制数据)
日期型

整型

类型	Tinyint	Smallint	Mediumint	Int/Integer	Bigint
字节	1	2	3	4	8

drop table if exists tab_int;
create table tab_int(
	t1 int(7),
    t2 int unsigned # 定义无符号
);
insert into tab_int values(-321,-321); #默认有符号，无符号添加负数报警告，值置为0

特点：
	①：如果不设置有无符号，默认是有符号，设置无符号需使用unsigned关键字
	②：如果插入的数值超出了整型范围，会报out of range 异常，并插入临界值
	③：如果不设置长度，会有默认的长度
		注意：长度不是代表数值大小，大小还是按类型来看
			 长度代表显示的宽度，若不够会用0在左边填充，但必须与zerofill搭配使用！
			 一旦搭配了zerofill，则为unsigned的数值。

小数
- 前两个为浮点型，后一个为定点型
类型 float(M,D) double(M,D) dec(M,D)/decimal(M,D)

字节 4 8 M+2

类型	float(M,D)	double(M,D)	dec(M,D)/decimal(M,D)
字节	4	8	M+2

# 特点：
	# ①： 测试M和D
	create table tab_float(
    	f1 float(5,2),
        f2 double(5,2),
        f3 dec(5,2)
    );
    insert into tab_float values(789.21,789.21,789.21); #无区别
    insert into tab_float values(789.217,789.217,789.217);#警告 789.22
    insert into tab_float values(789.2,789.2,789.2); #789.20
    insert into tab_float values(7891.2,7891.2,7891.2);#警告 999.99 
    #小结：M: 整数部分+小数部分 D：小数部分 如果超过范围，则插入临界值
    
    # ②： M和D都可以省略，但是：
    #	 如果是decimal,则M默认为10，D默认为0
    #	 如果是float或double，则会根据插入数据的精度来决定精度
    #  ③：定点型精度较高，若要求插入数值的精度较高（比如：货币），则考虑使用dec
    
    # 原则：所选择的类型越简单越好,能保存数值的类型越小越好(省空间)

字符型

# 较短的文本： char(M) varchar(M)  最多字符数：M
	特点：          
	char(M)       固定长度的字符，M可以省略，默认为1     比较耗费空间      效率高点
	varchar(M)    变长的字符，不可以省略               比较节省空间     效率低点
	
# enum
create table tab_char(
	c1 enum('a','b','c') # 不区分大小写 其他的值不能插入
);
#set
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'); # 同样不区分大小写 但是可以用逗号隔开多个值	

#小结下：binary和varbinary用于保存较短的二进制
# 		enum用于保存枚举
#  		set用于保存集合

# 较长的文本： text blob(较大的二进制)

日期

类型 date datetime timestamp time year

字节 4 8 4 3 1

示例 1000-01-01 1000-01-01 00:00:00 19700101080001 -838:59:59 1901
- 特点：
  - ① date只保存日期 time只保存时间 year只保存年
  - ② datetime一旦插入数据，不受时区影响 timestamp要受时区影响

类型	date	datetime	timestamp	time	year
字节	4	8	4	3	1
示例	1000-01-01	1000-01-01 00:00:00	19700101080001	-838:59:59	1901

常见约束

约束含义：限制表中的数据，为了保证表中的数据的准确和可靠性（一致性）

create table 表名(
	字段名 字段类型 约束
);
# 约束的分类，六大约束：
	① not null	非空，保证该字段的值不能为空，eg:姓名、学号等
	② default   默认，保证该字段有默认值
	③ unique 	唯一，用于保证字段值唯一，可以为空 eg:座位号
	④ check     检查约束[mysql中不支持] eg:性别只准填男和女
	⑤ primary key 主键  字段值具有唯一性且非空 eg:学号，员工编号等等
	⑥ foreign key 外键  用于限制两个表的关系，该字段的值必须来自于主表的关联列的值
					   在从表添加外键约束，用于引用主表中某列的值
					   eg: 学生表的专业编号，员工表的部门编号，员工表的工种编号

# 添加约束的时机： ① 创建表时  ② 修改表时 （在数据添加之前）

# 约束的添加分类： ①列级约束 : 六大约束语法上都行 但外键约束没有效果(白写)
#				② 表级约束 ： 除了非空和默认 其他都行
	eg:
	create table 表名(
    	字段名  字段类型 列级约束,
        字段名  字段类型,
        ...
        表级约束
    );
    
# 简单示例
# 一 创建表时添加约束
	# 1. 添加列级约束
	# 直接在字段名和类型后面追加约束类型即可。只支持 默认 非空 主键 唯一
	create database students;
	use students;
	create table stuinfo(
    	id int primary key, #主键
        stuName varchar(20) not null, #非空
        gender char(1) check(gender='男' or gender='女'),#检查约束(mysql没效果)
        seat int unique,# 唯一
        age int default 18,#默认
        majorId int  references major(id) #外键 (无效果，用表级约束即可，忽略)
    );
	
	create table major(
    	id int primary key,
        majorName varchar(20)
    );
    
    #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)#外键
  		# 非空和默认不支持 
    );
	
	# eg: 通用的写法
	create table if not exists stuinfo(
    	id int primary key,
        stuname varchar(20) not null,
        gender char(1),
        age int default 18,
        seat int unique,
        majorid int,
        constraint fk_stuinfo_major foreign key(majorid) references major(id)
    );
    
    # 主键和唯一的对比										 primary key(id,stuname)
    		  保证唯一性	是否允许为空 			一个表中可以有多少个   是否允许组合 !!不推荐
    主键			√		×					   至多一个				√
    唯一			√		√(仅允许一个null)		可以多个			 √
	# 关于外键的注意点
		1. 要求在从表设置外键关系
		2. 从表的外键列的类型和主表的关联列的类型一致或兼容
		3. 要求主表的关联列必须是一个key（一般是主键 ）
		4. 插入数据时，应该先插入主表再插入从表，删除数据时应该先删除从表再删主表
		
# 二. 修改表时添加约束
	语法：
	# 1. 添加列级约束：
	alter table 表名 modify column 字段名 字段类型 新约束;
	# 2. 添加表级约束：
	alter table 表名 add [ constraint 约束名 ] 约束类型（字段名） [ 外键引用 ];
	
# 三. 修改表时删除约束
	#删除其他约束类似于修改
	#eg: 删除非空
	alter table 表名 modify column 字段名 字段类型 null;
	#删除主键：
	alter table 表名 drop primary key;
	#删除唯一：
	alter table 表名 drop index 唯一键;

标识列

又称为自增长列：可以不用手动插入值，系统提供默认的序列值。

# 一 创建表时设置标识列
create table tab_identity(
	id int primary key auto_increment,
    name varchar(20)
);
insert into tab_identity values(null,'john');
#或者 省略标识列
insert into tab_identity(name) values('luky');
# 在mysql中不支持偏移 但是支持步长 eg:
set auto_increment_increment = 3; # 慎用
#那偏移 可以在你的第一次插入时指定 eg: 10为起始值， 其他数据使用功能null占位标识列即可
insert into tab_identity values(10,'john');

标识列特点：
	# 1. 标识列不是必须和主键搭配，只要是key就行（主键 唯一 外键）
	# 2. 一个表中只能有一个标识列
	# 3. 标识列的类型只能是数值类型
	# 4. 标识列可以通过set auto_increment_increment设置步长，可以通过手动插入值设置起始值
	
# 二、修改表时设置标识列
	alter table tab_identity modify column id int primary key auto_increment;
# 三、修改表时删除标识列
	alter table tab_identity modify column id int;

八_TCL语言

Transaction Control Language 事务控制语言
事务：一个或一组sql语句组成的一个执行单元，这个执行单元要么全部执行要么全部不执行。一般通过一组逻辑操作单元（一组DML以及sql语句），将数据从一种状态切换到另一种状态。
存储引擎：mysql中的数据用各种不同的技术存储在文件（或内存）中。通过show engines;查看mysql支持的存储引擎。其中innodb支持事务（mysql5.5默认），而myisam,memory等不支持。
事务的ACID属性
- 原子性(Atomicity)：原子性指事务不可分割，要么执行，要么回滚。
- 一致性(Consistency): 从一个一致性状态变换到另一个一致性状态（eg: 转账前后你两总金额不变)。
- 隔离性(Isolation): 并发执行的各个事务之间不能互相干扰（具体情况设置隔离级别）。
- 持久性(Durability): 一个事务一旦提交（一个删除一个修改也是），数据库中数据的改变就是持久性的，其他操作和数据库故障不应该对其有任何影响，除非其他事务对其进行修改。
事务的创建
- 隐式的事务：没有明显的开启和结束标记。eg: insert update delete语句
- 显式事务：事务具有明显的开启和结束的标记。前提：必须先设置自动提交功能为禁用。

整体步骤：
# ① 开启事务
set autocommit = 0; #禁用自动提交 默认开启事务
start transaction; # 这个可选 禁用即开启
# ② 编写事务中的sql语句（select insert update delete DDL语言没有事务之说）
语句1;
[ savepoint 断点名 ] #可选
语句2;
...
# ③ 结束事务  可以理解为没结束修改都在内存 结束时要么提交 要么回滚
commit; #提交事务
rollback [ to 断点名 ] ;#回滚事务  

# delete 和 truncate在事务使用时的区别
	#delete table 表名 对比 truncate table 表名
	注意： delete支持回滚  truncate不支持

同时运行的多个事务访问数据库中相同的数据时，如果么有采取必要的隔离机制，机会导致各种并发问题，比如两个事务 T1和T2:
- 脏读: T1读取了已经被T2更新但还没提交的字段，若T2回滚，则T1读取的就是临时值且无效。
- 不可重复读：T1读取一个字段，T2接着就更新了该字段，T1再次读取该字段，值就不同了。
- 幻读：T1从表中读取了一个字段，然后T2在该表中插入了一些新的行，之后T1再次读取这个表，会多出几行。

# 查看隔离级别
select @@tx_isolation; 
 
# 设置隔离级别  对当前连接有效
set session transaction isolation level read uncommitted; #level 后面跟级别
#隔离级别：
read uncommitted  # 读未提交数据 啥都不能避免
read committed    # 读已提交数据 只能避免脏读
repeatable read   # 可重复读     能避免脏读和不可重复读   【默认】
serializable      # 串行化       并发问题都可以避免 但性能低下

#设置数据库系统全局的隔离级别
set global transaction isolation level [ read committed ];

九_视图

视图就是虚拟表，mysql5.1版本出现的新特性，是通过表动态生成的数据,只保存sql逻辑，不保存查询结果。

# 一 创建视图
    # create view 视图名 as 查询语句;
    1 eg：查询姓名中包含a字符的员工名、部门名和工种信息
    ①：创建：		
    create view myv1 as
    select last_name,department_name,job_title from employees e
    join departments d on e.department_id = d.department_id
    join jobs j on j.job_id = e.job_id;
	②：使用
	select * from myv1 where last_name like '%a%';
	
	2 eg: 查询各部门的平均工资级别
	①：创建
	create view myv2 as
	select avg(salary) ag,department_id from employees 
	group by department_id;
	②：使用
	select myv2.ag,g.grade_level from myv2
	join job_grades g
	on myv2.ag between g.lowest_sal and g.highest_sal;
	
	3. 查询平均工资最低的部门信息
	select * from myv2 order by ag limit 1;
	
	4. 查询平均工资最低的部门名和工资
	create view myv3 as select * from myv2 order by ag limit 1;
	select d.*,m.ag from myv3 m 
	join departments d
	on m.department_id = d.department_id;
	
	#好处：
	#① 重用sql语句
	#② 简化复杂的sql操作，不必知道它的查询细节
	#③ 保护数据，提高安全性
	
#二 视图的修改
	方式一
	create or replace view as 查询语句;
	方式二
	alter view 视图名 as 查询语句；
	
#三 视图的删除
	drop view myv1,myv2,myv3;
	
#四 查看视图
	desc myv3; # 方式一 
	show create view myv3; # 方式二
	
#五 视图的更新
	create or replace view myv1 as
	select last_name,email "annunl salary"
	from employees;
	
	#1 插入
	insert into myv1 values('张飞','zf@qq.com');
	#2 修改
	update myv1 set last_name = '张无忌' where last_name = '张飞';
	#3 删除
	delete from myv1 where last_name = '张无忌';
	
	#注意： 这些操作都会对原始表有影响 这里是简单的特殊视图
	# 一般是不能对视图进行更新的（绝大部分视图都是）eg:
		包含以下关键字的sql语句：分组函数 distinct group by having union union all
		常量视图
		select包含子查询
		join
		from 一个不能更新的视图
		where字句的子查询应用了from字句中的表
		# 如上 几乎包含了所有

# 视图和表的对比
			 创建	   	         是否实际占用物理空间                  使用
    视图	create view	  仅仅保存了sql逻辑（不含数据，几乎不占空间） 增删改查（一般不能增删改）
    表	 create table		    保存了数据				        增删改查

十_变量

系统变量
- 全局变量
- 会话变量
自定义变量
- 用户变量
- 局部变量

系统变量

由系统提供，不是用户定义，属于服务器层面

#一 系统变量
    # 1. 查看
    show [ global | session ]  variables;
    # 2. 查看满足条件的部分系统变量
    show [ global | session ]  variables like '%char%';
    # 3. 查看指定的某个系统变量的值
    select @@系统变量名;   #默认会话变量
    select @@global|session.系统变量名;
    # 4. 为某个系统变量赋值
    set [ global | session ] 统变量名 = 值; # 方式一
    set @@global|session.系统变量名 = 值; #方式二

    #注： 默认都是session

自定义变量

# 1. 用户变量
	# 用户自己定义  用在begin end里面 外面都行
	# 作用域： 针对于当前会话（连接）有效，等同与会话变量的作用越
	
	#① 声明并初始化  三种方式：
	set @用户变量名 = 值;
	set @用户变量名:=值;
	select @用户变量名:=值;
	eg: set @count:=1;
	
	#② 赋值（更新用户变量的值）
	方式一： 以声明并初始化的三种方式
	方式二： select 字段 into 变量名 from 表;
    eg: select count(*) into @count from employees;
	
	#③ 查看
	select @用户变量名;
	eg： select @count;
	
	# 2. 局部变量
	#作用域：仅仅定义在begin end 中 并且是第一句
	
	#① 声明：
	declare 变量名 类型;
	declare 变量名 类型 default 值;
	#② 赋值：
	方式一： 
        set 局部变量名 = 值;
        set 局部变量名:=值;
        select @局部变量名:=值; # 这个需要加@ 上面两个不用
	方式二： 
  	 select 字段 into 局部变量名 from 表;
  	#③ 使用
  	select 局部变量名;
		
# 对比用户变量和局部变量
			作用域  		   定义和使用的位置					语法
用户变量	当前会话			会话中的任何地方    	必须加@符号，不用限定类型

局部变量   begin end 中    只能在begin end中且第一句   一般不用加@ 需要限定类型

十一_存储过程和函数

存储过程

存储过程：一组预先编译好的sql语句的集合，类似用于java的方法。
优点：
- 提高了sql语句的重用性，减少了开发程序员的压力
- 简化操作，提高效率
- 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

一 创建
    create procedure 存储过程名(参数列表) begin
        存储过程体（一组合法的sql语句）
    end
    #注意
        # 1. 参数列表包含三部分：  参数模式  参数名  参数类型  eg: in stuname varchar(20)
        参数模式：
        in      该参数可以作为输入 即调用者传入的参数
        out 	该参数可以作为输出 即返回值
        inout   该参数既可以作为输入又可以作为输出
        # 2 如果存储过程体仅仅就一句话，begin end 可以省略
            ① 存储过程体重的每条sql语句必须加分号
            ② 存储过程结尾可以使用 delimiter重新设置，语法： delimiter 结束标记

二 调用
	call 存储过程名(实参列表);
	
三 简单示例
	#1 插入到admin 表中五条记录
	# ①创建
	delimiter $
	create procedure myp1()
	begin
		insert into admin(username,word) values
		('john1','0000'),
		('john2','0001'),
		('john3','0003'),
		('john4','0004'),
		('john5','0005');
	end $
	#② 调用
	call myp1()$  # 结束标记已被设置为$  在黑窗口中调用
	
	#  2. 带 in 模式参数的存储过程
	# 创建存储过程实现根据女神名查询对应的男神信息
	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('柳岩')$
	
	# 创建存储过程实现用户是否登录成功
	create procedure myp3(in username varchar(20),in password varchar(20))
	begin
		
	end
	
	#  3. 带 out 模式的存储过程
	# 案例 根据女神名 返回对应的男神名
	create procedure myp44(in beautyName varchar(20),out boyName varchar(20))
	begin
		select bo.boyName into boyName #直接返回给out模式的参数
        from boys bo
        inner join beauty b on bo.id = b.boyfriend_id
        where b.name = beautyName;
	end $
	#调用
	call myp44('柳岩',@Name)$  #给个变量接收
	select @Name$              #然后查看
	
	# 4. 带 inout 模式参数的存储过程
	# 案例 传入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$
	
四 删除存储过程
	drop procedure p1;
五 查看存储过程
	show create procedure myp2;

函数

和存储过程大同小异
区别
- 存储过程：可以无返回，也可以多个返回，适合做批量插入
- 函数：有且仅有一个返回（无返回也不行），处理数据后返回一个结果

# 一 创建
    create function 函数名(参数列表) returns 返回类型
    begin 
        函数体
    end
    #注意： 参数列表包含两部分： 参数名 参数类型
    # 函数体必有return语句，没有会报错，
    #如果return语句没有放到函数体最后不报错，但是不建议， return 值;
    # begin end 可以在函数体只有一句的时候省略
    #使用delimiter语句设置结束标记
    
# 二 调用
	select 函数名(参数列表) # 执行函数并显示返回值

# 三 简单演示
	#1 无参有返回
	# 返回公司的员工个数
	create function myf1() returns 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)) returns double
	begin
		set @sal=0; #定义了一个用户变量
		select salary into @sal #赋值
		from employees where last_name = empName;
		return @sal; 
	end $
	select myf2('kochhar')$ #调用
	
# 四 查看函数
	show create function myf3;
	
# 五 删除函数
	drop function myf3;

十二_流程控制结构

顺序结构
分支结构
循环结构

分支结构

1. if函数  实现简单的双分支
	语法：if(表达式1,表达式2,表达式3)
	执行顺序：如果表达式1成立就返回表达式2的值，否则返回表达式3的值
2. case 结构
	#情况1：类似于java中的switch语句，一般用于实现等值判断
		语法：   case 变量|表达式|字段
				when 要判断的值 then 返回的值1或语句1;
				when 要判断的值 then 返回的值2或语句2;
				...
				else 要返回的值n或语句n;
				end case;
	#情况2：类似于java中的if-else语句，一般用于实现区间判断
		语法：   case 
				when 要判断的条件1 then 返回的值1或语句1;
				when 要判断的条件2 then 返回的值2或语句2;
				...
				else 要返回的值n或语句n；
				end case;
	#case结构的特点：
	# 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方 begen end 里面外面都行
	# 可以作为独立的语句 只能放到begin end 中
	# 满足条件执行then后就结束了case
	# else可以省略，省略后若都不满足，则返回null
	
3. if 结构 : 实现多重分支
	语法：
    if 条件1 then 语句1;
    elseif 条件2 then 语句2;
    ....
    else 语句n;
    end if;
    #功能：类似于多重if
    #只能应用在begin end 中
    
    #创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
    create function test_if(score float) returns char
	begin
	declare ch char default 'A';
	if score>90 then set ch='A';
	elseif score>80 then set ch='B';
	elseif score>60 then set ch='C';
	elseif ch='D';
	end if;
	return ch;
	end $
	#调用
	select test_if(87)$

循环结构

分类：while loop repeat 必须放在begin end 里面
循环控制：
iterate 类似于continue;
leave 类似于 break;

#1 while
    语法：
    [ 标记 ] while 循环条件 do
        循环体;
   	 end while [ 标记 ];
   	 
#2 loop 
	语法：
    [ 标记 ] loop
        循环体;
    end loop [ 标记 ];
   # 可以用来模拟简单的死循环 搭配leave跳出循环
   
#3 repeat
	语法：
    [ 标记 ] repeat
        循环体;
    until 结束循环的条件
    end repeat [ 标记 ];
    
# 简单案例
	#  批量插入，根据次数插入到admin表中多条记录 
	create procedure pro_while(in insertCount int)
    begin
    	declare i int default 1;
    	a: while i <=insertCount do
    		insert into admin(username,word) values(concat('rose',i),'666');
    		set i = i+1;
    	end while a;
    end $
	#调用
	call pro_while(100)$
	
# 循环小结
	三种循环都在 begin end 中
	loop 是没有条件的死循环  while先判断后执行  repeat先执行一次再判断
	
# 案例
#已知表stringcontent,其中字段：id 自增长   content varchar(20)
#要求： 向该表中插入指定个数的，随机的字符串

	create table stringcontent(
    	id int primary key auto_increment,
        content varchar(20)
    );
    delimiter $
	create procedure test_randstr_insert(in insertCount int)
	begin
		declare i int default 1;#定义循环变量i
		declare str varchar(26) default 'abcdefghijklmnopqrstuvwxyz';
		declare startIndex int default 1;#代表起始索引
		declare len int default 1; # 截取的长度
		while i <= insertCount do
			set len =floor(rand()*20+1); # 产生随机整数，为截取的长度
			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)$ #在黑窗口中