MySQL-基础部分

目录：

一、数据库及相关概念解释

二、基础查询

三、条件查询

一、数据库及相关概念解释

1.相关概念

DB:database数据库，存储一系列有组织数据的容器。

DBMS：Database Management System数据库管理系统，使用DBMS管理和维护DB。

SQL：Structure Query Language结构化查询语言，程序员用于和DBMS通信交互的语言。

2.MySQL服务的登录和退出

方式一：通过dos命令

mysql -h主机名 -P端口号 -u用户名 -p密码

注意：

如果是本机，则-h主机名可以省略

如果端口号是3306，则-P端口号可以省略

方式二：通过图形化界面客户端

通过sqlyog，直接输入用户名、密码等连接进去即可

3.MySQL的常见命令和语法规范

2.(1)常见命令

show databases 显示当前连接下所有数据库

show tables 显示当前库中所有表

show tables from 库名 显示指定库中所有表

show columns from 表名 显示指定表中所有列

use 库名 打开/使用指定库

　　(2)语法规范

① 不区分大小写

② 每条命令结尾建议用分号

③Mysql在使用函数查询时，函数名后面不能加空格

(3)注释：

# 单行注释

-- 单行注释

/*多行注释

多行注释

4.数据库系统表明解名

二、基础查询

SELECT 查询列表 
FROM 表名;

特点：

1、查询的结果集是一个虚拟表

2、SELECT后面跟的查询列表，可以有多个部分组成，中间用逗号隔开

例如：SELECT 字段1,字段2,表达式 
FROM 表;

3.查询列表可以是：字段、表达式、常量、函数等

4.执行顺序：

① FROM子句

② SELECT子句

基础查询案例：

一、查询常量
SELECT 100 ;

二、查询表达式
SELECT 100%3;

三、查询单个字段
SELECT `last_name` FROM `employees`;

四、查询多个字段
SELECT `last_name`,`email`,`employee_id` FROM employees;

五、查询所有字段
SELECT * FROM `employees`;

六、查询函数（调用函数，获取返回值）
SELECT DATABASE();
SELECT VERSION();
SELECT USER();

七、起别名
方式一：使用AS关键字
    SELECT USER() AS 用户名;
    SELECT USER() AS "用户名";
    SELECT USER() AS '用户名';
    SELECT last_name AS "姓 名" FROM employees;
方式二：使用空格
    SELECT USER()   用户名;
    SELECT USER()   "用户名";
    SELECT USER()   '用户名';
    SELECT last_name   "姓 名" FROM employees;

八、+的作用
mysql中+的作用：
1、加法运算
①两个操作数都是数值型
100+1.5

②其中一个操作数为字符型
将字符型数据强制转换成数值型,如果无法转换，则直接当做0处理
'张无忌'+100--->100

③其中一个操作数为NULL
NULL+NULL--->NULL
NULL+100--->NULL

#需求：查询 first_name 和last_name 拼接成的全名，最终起别名为：姓 名
使用CONCAT拼接函数
SELECT CONCAT(first_name,last_name) AS "姓 名"
FROM employees;

九、DISTINCT的使用(只返回不同的值)
distinct可加多个字段，只有当多个字段全部一样时才会去掉，有一个字段内容不一样都会保留
-- 需求：查询员工涉及到的部门编号有哪些，不重复
SELECT DISTINCT department_id FROM employees;

十、查看表的结构
①、DESC employees;
②、SHOW COLUMNS FROM employees;

十一、F12快捷键对齐格式
SELECT 
    `last_name`,
    `first_name`,
    `last_name`,
    `commission_pct`,
    `hiredate`,
    `salary` 
FROM
    employees ;

三、条件查询

SELECT 查询列表
FROM  表名
WHERE 筛选条件;

执行顺序：

①FROM子句

②WHERE子句

③SELECT子句

案例：

#查询工资大于20000的员工姓和名
SELECT last_name,first_name 
FROM employees 
WHERE salary>20000;

特点：

1.按关系表达式筛选

关系运算符：> 、<、 >=、 <=、 = 、<>（不等于）

#案例1：查询部门编号不是100的员工信息
SELECT *
FROM employees
WHERE department_id <> 100;

#案例2：查询工资<15000的姓名、工资
SELECT last_name,salary
FROM employees
WHERE salary<15000;

2.按逻辑表达式筛选

逻辑运算符：AND 、OR、 NOT

#案例1：查询部门编号不是 50-100之间员工姓名、部门编号、邮箱
方式1：
SELECT last_name,department_id,email
FROM employees
WHERE department_id <50 OR department_id>100;

方式2：
SELECT last_name,department_id,email
FROM employees
WHERE NOT(department_id>=50 AND department_id<=100);

#案例2：查询奖金率>0.03 或者 员工编号在60-110之间的员工信息
SELECT *
FROM employees
WHERE commission_pct>0.03 OR (employee_id >=60 AND employee_id<=110);

3.模糊查询

LIKE、 IN 、BETWEEN AND、 IS NULL

(1) LIKE

功能：一般和通配符搭配使用，对字符型数据进行部分匹配查询

常见的通配符：

_ 任意单个字符

% 任意多个字符,支持0-多个

LIKE / NOT LIKE

#案例1：查询姓名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%a%';

#案例2：查询姓名中包含最后一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%e';

#案例3：查询姓名中包含第一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE 'e%';

#案例4：查询姓名中包含第三个字符为x的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '__x%';

#案例5：查询姓名中包含第二个字符为_的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '_\_%';(这里的为转义符)

SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';(ESCAPE表示将$作为转义符)

(2)IN

功能：查询某字段的值是否属于指定的列表之内

a IN(常量值1,常量值2,常量值3,...)

a NOT IN(常量值1,常量值2,常量值3,...)

IN / NOT IN

#案例1：查询部门编号是30/50/90的员工名、部门编号
方式1：
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);

方式2：
SELECT last_name,department_id
FROM employees
WHERE department_id = 30
OR department_id = 50
OR department_id = 90;

案例2：查询工种编号不是SH_CLERK或IT_PROG的员工信息
#方式1：
SELECT *
FROM employees
WHERE job_id NOT IN('SH_CLERK','IT_PROG');
#方式2：
SELECT *
FROM employees
WHERE NOT(job_id ='SH_CLERK'OR job_id = 'IT_PROG');

(3) BETWEEN AND

功能：判断某个字段的值是否介于X，X之间

BETWEEN AND / NOT BETWEEN AND

#案例1：查询部门编号是30-90之间的部门编号、员工姓名
方式1：
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

方式2：
SELECT department_id,last_name
FROM employees
WHERE department_id>=30 AND department_id<=90;

#案例2：查询年薪不是100000-200000之间的员工姓名、工资、年薪

SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 
#(IFNULL(参数1，参数2)表示如果参数1不是NULL，就显示参数1，参数1是NULL，就显示参数2)
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0))<100000 OR salary*12*(1+IFNULL(commission_pct,0))>200000;

SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;

4.IS NULL / IS NOT NULL

功能：判断字段是否为空

#案例1：查询没有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NULL;

#案例2：查询有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;

-------------- = 与 IS 的对比------------

= 只能判断普通的内容
IS 只能判断NULL值
<=> 安全等于，既能判断普通内容，又能判断NULL值，不建议用，阅读性差

SELECT *
FROM employees
WHERE salary IS 10000;(报错)

四、排序查询

SELECT 查询列表
FROM 表名
WHERE 筛选条件
ORDER BY 排序列表

执行顺序：

①FROM子句

②WHERE子句

③SELECT子句

④ORDER BY 子句

SELECT last_name,salary
FROM employees
WHERE salary>20000
ORDER BY salary ;

特点：

1、排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合

2、升序，通过 ASC (默认行为)；降序，通过 DESC。

一、按单个字段排序
#案例1：将员工编号>120的员工信息进行工资的降序
SELECT * 
FROM employees 
WHERE employee_id>120 
ORDER BY salary DESC;

二、按表达式排序
#案例1：对有奖金的员工，按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

三、按别名排序
#案例1：对有奖金的员工，按年薪降序
SELECT *,salary*12*(1+commission_pct,0)  年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;

思考为什么WHERE不能用别名进行判断？
因为是代码运行顺序的问题，where在select运行之前。

四、按函数的结果排序
#案例1：按姓名的字数长度进行升序
SELECT last_name
FROM employees
ORDER BY LENGTH(last_name);

五、按多个字段排序
#案例1：查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序
SELECT last_name,salary,department_id
FROM employees
ORDER BY salary ASC,department_id DESC;

六、补充选学：按特定的列进行排序
SELECT * FROM employees 
ORDER BY 2 DESC;
SELECT * FROM employees 
ORDER BY first_name;

五、常见函数

函数：为了实现某个功能，将编写的一系列的命令集合封装在一起，对外仅仅显示方法名，供外部调用

1、自定义方法(函数)

2、调用方法(函数)★

函数名：叫什么

函数功能：干什么

单行函数

字符函数

CONCAT

SUBSTR

LENGTH（str）

CHAR_LENGTH

UPPER LOWER

TRIM

LEFT

RIGHT

LPAD

RPAD

INSTR

STRCMP

#一、字符函数
1、CONCAT 拼接字符
SELECT CONCAT('hello,',first_name,last_name)  备注 FROM employees;

2、LENGTH 获取字节长度，字节长度与数字编码格式有关
SELECT LENGTH('hello,郭襄');

3、CHAR_LENGTH 获取字符个数
SELECT CHAR_LENGTH('hello,郭襄');

4、SUBSTR 截取子串
/*
注意：起始索引从1开始！！！
substr(str,起始索引，截取的字符长度)
substr(str,起始索引)没有第三个参数的话就是截取索引之后所有的值
*/
SELECT SUBSTR('张三丰爱上了郭襄',1,3);--->张三丰
SELECT SUBSTR('张三丰爱上了郭襄',7);--->郭襄

5、INSTR获取字符第一次出现的索引
SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');--->3

6、TRIM去前后指定的字符，默认是去空格
SELECT TRIM(' 虚  竹    ')  AS a;--->虚  竹
SELECT TRIM('x' FROM 'xxxxxx虚xxx竹xxxxxxxxxxxxxxxxxx')  AS a;--->虚xxx竹

7、LPAD/RPAD  左填充/右填充，5代表总共的字符个数
SELECT LPAD('木婉清',5,'a');--->aa木婉清
SELECT RPAD('木婉清',5,'a');--->木婉清aa

8、UPPER/LOWER  变大写/变小写
#案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”
SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) "OUTPUT"
FROM employees;

9、STRCMP 比较两个字符大小
SELECT STRCMP('aec','aec');
参数1大于参数2显示1，小于显示-1，等于显示0

10、LEFT/RIGHT  截取子串
SELECT LEFT('鸠摩智',1);--->鸠
SELECT RIGHT('鸠摩智',1);--->智

数学函数

ABS

CEIL

FLOOR

ROUND

TRUNCATE

MOD

#二、数学函数
1、ABS 绝对值
SELECT ABS(-2.4);--->2.4

2、CEIL 向上取整  返回>=该参数的最小整数
SELECT CEIL(-1.09);
SELECT CEIL(0.09);
SELECT CEIL(1.00);

3、FLOOR 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-1.09);
SELECT FLOOR(0.09);
SELECT FLOOR(1.00);

4、ROUND 四舍五入
SELECT ROUND(1.8712345);
SELECT ROUND(1.8712345,2); 2代表小数点后几位

5、TRUNCATE 截断
SELECT TRUNCATE(1.8712345,1);参数2表示截断小数点后几位

6、MOD 取余
SELECT MOD(-10,3);

a%b = a-a/b*b
-10%3 = -10 - (-10)/3*3   = -1

SELECT -10%3;
SELECT 10%3;
SELECT -10%-3;
SELECT 10%-3;

日期函数

NOW

CURTIME

CURDATE

DATEDIFF

DATE_FORMAT

STR_TO_DATE

#三、日期函数
1、NOW 当前日期，时间
SELECT NOW();

2、CURDATE 当前日期
SELECT CURDATE();

3、CURTIME 当前时间
SELECT CURTIME();

4、DATEDIFF 两个日期差值，参数1减参数2
SELECT DATEDIFF('1998-7-16','2019-7-13');

5、DATE_FORMAT 将参数1按照转换成指定格式
SELECT DATE_FORMAT('1998-7-16','%Y年%m月%d日 %H小时%i分钟%s秒') 出生日期;
1998年07月16日 00小时00分钟00秒


6、STR_TO_DATE 按指定格式解析字符串为日期类型，这样字符串就能和日期比较
SELECT * FROM employees
WHERE hiredate<STR_TO_DATE('3/15 1998','%m/%d %Y');

流程控制函数

CASE

#四、流程控制函数
1、IF函数
SELECT IF(100>9,'好','坏');
如果 100>9，显示好，否则显示坏

#需求：如果有奖金，则显示最终奖金，如果没有，则显示0
SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct)  奖金
FROM employees;

2、CASE函数

情况1 ：实现等值判断
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
...
ELSE 结果n
END

案例：
部门编号是30，工资显示为2倍
部门编号是50，工资显示为3倍
部门编号是60，工资显示为4倍
否则不变
显示 部门编号，新工资，旧工资

SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END  newSalary
FROM employees;

②情况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 ：查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数

SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) 
FROM employees;

#案例2：添加筛选条件
 ①查询employee_id表中记录数：
 SELECT COUNT(employee_id) 
FROM employees;

②查询employee_id表中有佣金的人数：
SELECT COUNT(salary) 
FROM employees;

③查询employee_id表中月薪大于2500的人数：
SELECT COUNT(salary) 
FROM employees 
WHERE salary>2500;
    
④查询有领导的人数：
SELECT COUNT(manager_id) 
FROM employees;

count的补充介绍★

1、统计结果集的行数，推荐使用count(*)
SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees WHERE department_id = 30;

COUNT(1)表示给数据添加了一列内容为1的数据，然后计算数据为1的行数

SELECT COUNT(1) FROM employees;
SELECT COUNT(1) FROM employees WHERE department_id = 30;

2、搭配distinct实现去重的统计
#需求：查询有员工的部门个数
SELECT COUNT(DISTINCT department_id) FROM employees;

七、分组查询

SELECT 查询列表
FROM 表名
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表;

执行顺序：

①FROM子句

②WHERE子句

③GROUP BY 子句

④HAVING子句

⑤SELECT子句

⑥ORDER BY子句

特点：

①分组查询列表往往是聚合函数和被分组的字段

②分组查询中的筛选分为两类

顺序：WHERE——GROUP BY ——HAVING

③聚合函数做条件只可能放在HAVING后面

④只有GROUP BY 后面跟的字段名完全一致时才能分成一组，不管字段名有几个

简单分组案例：

#案例1：查询每个工种的员工平均工资

SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#案例2：查询每个领导的手下人数

SELECT COUNT(*),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;

可以实现分组前的筛选
#案例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;

分组后的筛选：

#案例1：查询哪个部门的员工个数>5
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的最低工资
SELECT MIN(salary) 最低工资,manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000 ;

实现排序：

#案例：查询没有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT MAX(salary) 最高工资,job_id
FROM employees
WHERE commission_pct IS  NULL
GROUP BY job_id
HAVING MAX(salary)>6000
ORDER BY MAX(salary) ASC;

按多个字段分组：

#案例：查询每个工种每个部门的最低工资,并按最低工资降序
提示：工种和部门都一样，才是一组
SELECT MIN(salary) 最低工资,job_id,department_id
FROM employees
GROUP BY job_id,department_id
ORDER BY MIN(salary) DESC;

八、连接查询

又称多表查询，当查询语句涉及到的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：

表1 有m行，表2有n行，

结果=m*n行

SELECT girl_name, boy_name
FROM beauty, boys;

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

分类：

    按年代分类：
    　　1、sql92标准:仅仅支持内连接
        　　内连接：
            　　等值连接
            　　非等值连接
            　　自连接
    　　2、sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
        　　按功能分类：
        　　内连接：
            　　等值连接
            　　非等值连接
            　　自连接
       　　 外连接：
            　　左外连接
            　　右外连接
            　　全外连接
        　　交叉连接

------------------------------sql92标准--------------------------------

一、内连接

语法:

SELECT 查询列表

FROM 表1 别名,表2 别名

WHERE 连接条件 AND 筛选条件

GROUP BY 分组列表

HAVING 分组后筛选

ORDER BY 排序列表

执行顺序：

1、FROM子句

2、WHERE子句

3、AND子句

4、GROUP BY子句

5、HAVING子句

6、SELECT子句

7、ORDER BY子句

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

1.查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

2、为表起别名
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;

3、两个表的顺序可以调换
#查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;

4、可以加筛选
#查询有奖金的员工名、部门名
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;

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

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

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
如果两个表有相同的字段名，在查询的时候需要指定查询的是哪个表的，
比如SELECT department_name,d.`manager_id`,MIN(salary)里的d.`manager_id`

SELECT department_name,d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`manager_id`;

6、可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

7、可以实现三表连接？
#案例：查询员工名、部门名和所在的城市

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`
ORDER BY department_name DESC;

二、非等值连接

#案例1：查询员工的工资和工资级别
(找salary对应的级别范围而已)
#查找员工级别为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';

工资级别源码：
select salary,employee_id from employees;
select * from job_grades;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);

工资级别源码

三、自连接

#案例：查询 员工名和上级的名称
(上级也是员工，所以都在employees表里面，所以就得自己连接自己。)

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

------------------------------sql99标准--------------------------------

一、内连接

SELECT 查询列表
FROM 表名1 别名
【INNER】 JOIN  表名2 别名      （INNER可省略）
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表;

SQL92和SQL99的区别：

SQL99，使用JOIN关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性！

一、等值连接

①简单连接
#案例：查询员工名和部门名
SELECT last_name,department_name
FROM departments d 
 JOIN  employees e 
ON e.department_id =d.department_id;

②添加筛选条件
#案例1：查询部门编号>100的部门名和所在的城市名
SELECT department_name,city
FROM departments d
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id`>100;

③添加分组+筛选
#案例1：查询每个城市的部门个数
SELECT COUNT(*) 部门个数,l.`city`
FROM departments d
JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY l.`city`;

④添加分组+筛选+排序
#案例1：查询部门中员工个数>10的部门名，并按员工个数降序
SELECT COUNT(*) 员工个数,d.department_name
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数>10
ORDER BY 员工个数 DESC;

二、非等值连接

#案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组
SELECT COUNT(*) 个数,grade
FROM employees e
JOIN sal_grade g
ON e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`
WHERE e.`department_id` BETWEEN 10 AND 90
GROUP BY g.grade;

三、自连接

#案例：查询员工名和对应的领导名
SELECT e.`last_name`,m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;

四、外连接

说明：查询结果为主表中所有的记录，如果从表有匹配项，则主表对应列显示匹配项；如果从表没有匹配项，则主表对应列显示NULL。

应用场景：一般用于查询主表中有但从表没有的记录

特点：

1、外连接分主从表，两表的顺序不能任意调换

2、左连接的话，LEFT JOIN 左边为主表

右连接的话，RIGHT JOIN 右边为主表

SELECT 查询列表
FROM 表1 别名
LEFT|RIGHT 【OUTER】 JOIN 表2 别名
ON 连接条件  （ON 后面只是连接条件，并不是筛选条件，所以即使主从表中有些值为NUII也不影响）
WHERE 筛选条件;

USE girls;
案例1：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null
#左连接
SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`;

#右连接
SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN  beauty b ON b.`boyfriend_id` = bo.`id`;

案例2：查哪个女神没有男朋友
#左连接
SELECT b.`name`
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id`  IS NULL;

#右连接
SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN  beauty b ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id`  IS NULL;

案例3：查询哪个部门没有员工，并显示其部门编号和部门名
SELECT department_id, department_nameFROM departments d
LEFT JOIN employees e 
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

关于连接查询注意的一个点：连接查询成功时（除外连接），当初from后列表只保留连接条件符合的数据。

九、子查询

当一个查询语句中又嵌套了另一个完整的SELECT语句，则被嵌套的SELECT语句称为子查询或内查询外面的SELECT语句称为主查询或外查询

分类：

按子查询出现的位置进行分类：

1、SELECT后面要求：子查询的结果为单行单列（标量子查询）

2、FROM后面要求：子查询的结果可以为多行多列

3、WHERE或HAVING后面 ★

要求：子查询的结果必须为单列

　　　　　　　　　　　　单行子查询

　　　　　　　　　　　　多行子查询

4、EXISTS后面要求：子查询结果必须为单列（相关子查询）

特点：

1、子查询放在条件中，要求必须放在条件的右侧

2、子查询一般放在小括号中

3、子查询的执行优先于主查询

4、单行子查询对应了单行操作符：> < >= <= = <>

　　多行子查询对应了多行操作符：ANY/SOME ALL IN

一、放在WHERE或HAVING后面的单行子查询

单行子查询
#案例1：谁的工资比 Abel 高?
①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name  = 'Abel'

②查询salary>①的员工信息
SELECT last_name,salary
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name  <> 'Abel'

);

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141

②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143

③查询job_id=① AND 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

②查询salary=①的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees

);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50


②查询各部门的最低工资，筛选看哪个部门的最低工资>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
);

二、放在WHERE或HAVING后面的多行子查询

IN:判断某字段是否在指定列表内

X IN(10,30,50)

ANY/SOME:判断某字段的值是否满足其中任意一个

X=ANY(10,30,50) 等价于 X IN(10,30,50)

ALL:判断某字段的值是否满足里面所有的

X >ALL(10,30,50) 等价于 X >MAX(10，30，50)

X <ALL(10,30,50) 等价于 X <MIN(10，30，50)

#案例1：返回location_id是1400或1700的部门中的所有员工姓名
①查询location_id是1400或1700的部门
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)

②查询department_id = ①的姓名
SELECT last_name
FROM employees
WHERE department_id IN(
    SELECT DISTINCT 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'
);
等价于

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(

    SELECT MAX(salary)
    FROM employees
    WHERE 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<ALL(
    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'
);

三、放在SELECT后面(相关子查询)

#案例；查询部门编号是50的员工个数
SELECT 
(
    SELECT COUNT(*)
    FROM employees
    WHERE department_id = 50
)  个数;

四、放在FROM后面(表子查询)

#案例：查询每个部门的平均工资的工资级别
①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM esemploye
GROUP BY department_id

②将①和sal_grade两表连接查询
SELECT dep_ag.department_id, dep_ag.ag, g.grade
FROM sal_grade g
JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) dep_ag 
ON dep_ag.ag BETWEEN g.min_salary AND g.max_salary;

五、放在EXISTS后面

#案例1 ：查询有无名字叫“Abel”的员工信息
有 结果为1
无 结果为0
SELECT EXISTS(
    SELECT * 
    FROM employees
    WHERE last_name = 'Abel'
) 有无Abel;

六、子查询中的排序问题

遇到例如平均工资最低，最高工资最小等需求时，解决办法时先按照平均工资排序，然后用LIMIT选取结果。
例如求员工平均工资最低的员工号

SELECT employee_id
FROM employees
ORDER BY AVG(salary) ASC
LIMIT 1;

十、分页查询

应用场景：当页面上的数据，一页显示不全，则需要分页显示

分页流程：分页查询的SQL命令请求数据库服务器——>服务器响应查询到的多条数据——>前台页面

SELECT 查询列表
FROM 表1 别名
JOIN 表2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组
HAVING 分组后筛选
ORDER BY 排序列表
LIMIT 起始条目索引,显示的条目数

执行顺序：

①FROM子句

②JOIN子句 3

③ON子句 4

④WHERE子句

⑤GROUP BY子句

⑥HAVING子句

⑦SELECT子句

⑧ORDER BY子句

⑨LIMIT子句

特点：

LIMIT(参数1,参数2)

①起始条目索引如果不写，默认是0

②LIMIT后面支持两个参数

参数1：显示的起始条目索引参数2：条目数

select *
from employees
limit (page-1)*size,size;
page    size=10
1                       limit 0,10
2            limit 10,10
3            limit 20,10
4            limit 30,10

页数与显示条目数的关系

#案例1：查询员工信息表的前5条
SELECT * FROM employees LIMIT 0,5;
#完全等价于
SELECT * FROM employees LIMIT 5;

#案例2：查询有奖金的，且工资较高的第11名到第20名
SELECT * 
FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC
LIMIT 10,10 ;

十一、联合查询

当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为UNION查询

SELECT 查询列表 FROM 表1  WHERE 筛选条件  
    UNION
SELECT 查询列表 FROM 表2  WHERE 筛选条件

特点：

1、多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致

2、UNION实现去重查询；UNION ALL 实现全部查询，包含重复项

#案例：查询所有国家的年龄>20岁的用户信息
SELECT * FROM usa WHERE uage >20 UNION
SELECT * FROM chinese WHERE age >20 ;


#案例2：查询所有国家的用户姓名和年龄
这样做语法没问题，但没有实际操作意义（两个查询的年龄，名字顺序相反，所以说没意义）
SELECT uname,uage FROM usa
UNION
SELECT age,`name` FROM chinese;

#案例3：union自动去重/union all 可以支持重复项
SELECT 1,'范冰冰' 
UNION ALL
SELECT 1,'范冰冰' 
UNION  ALL
SELECT 1,'范冰冰' 
UNION  ALL
SELECT 1,'范冰冰' ;

十二、DDL

DATA Define Language数据定义语言,用于对数据库和表的管理和操作

--------------------------------------------------库的管理------------------------------------------------------

一、创建数据库
CREATE DATABASE stuDB;
CREATE DATABASE IF NOT EXISTS stuDB;

二、删除数据库
DROP DATABASE stuDB;
DROP DATABASE IF EXISTS stuDB;

--------------------------------------------------表的管理------------------------------------------------------

创建表 ★
语法：
CREATE TABLE [IF NOT EXISTS] 表名(
    字段名  字段类型  【字段约束】,
    字段名  字段类型  【字段约束】,
    字段名  字段类型  【字段约束】,
    字段名  字段类型  【字段约束】,
    字段名  字段类型  【字段约束】
);

案例：没有添加约束
CREATE TABLE IF NOT EXISTS stuinfo(
    stuid INT ,
    stuname VARCHAR(20),
    stugender CHAR(1),
    email VARCHAR(20),
    borndate DATETIME
);

案例：添加约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
    stuid INT PRIMARY KEY,#添加了主键约束
    stuname VARCHAR(20) UNIQUE NOT NULL,#添加了唯一约束+非空
    stugender CHAR(1) DEFAULT '男',#添加了默认约束
    email VARCHAR(20) NOT NULL,
    age INT CHECK( age BETWEEN 0 AND 100),#添加了检查约束，mysql不支持
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id)#添加了外键约束

);

关于外键：

fk_stuinfo_major 为外键名字

majorid 为要设置外键的从表字段名

major(id) 为maior表里的id，即主表中被引用的字段名

一、数据类型

1、整型
TINYINT SMALLINT  INT  BIGINT 

2、浮点型
FLOAT(m,n)
DOUBLE(m,n) 
DECIMAL(m,n)--定点型
m和n可选
m表示整数位和小数点位之和，n表示小数点后的位数
m和d都可以省略，对于DECIMAL而言，M默认为10，D默认为0


3、字符型 CHAR(n):n可选 
VARCHAR(n)：n必选 
TEXT n表示最多字符个数 

4、日期型 
DATE 只显示日期 
TIME 只显示时间
DATETIME 显示日期和时间
TIMESTAMP 显示日期和时间（最大值到2038年的某一刻）

5、二进制型 BLOB 存储图片数据

二、常见约束

用于限制表中字段的数据的，从而进一步保证数据表的数据是一致的、准确的、可靠的。

NOT NULL 非空：用于限制该字段为必填项

DEFAULT 默认：用于限制该字段没有显式插入值，则直接显式默认值

PRIMARY KEY 主键：用于限制该字段值不能重复，设置为主键列的字段默认不能为空
    一个表只能有一个主键，当然可以是组合主键
    
UNIQUE 唯一：用于限制该字段值不能重复
        字段是否可以为空        一个表可以有几个     联合健
    主键           ×                1个           √
    唯一           √                n个           √

CHECK检查：用于限制该字段值必须满足指定条件
                    CHECK(age BETWEEN 1 AND 100)
        
FOREIGN KEY 外键:用于限制两个表的关系,要求外键列的值必须来自于主表的关联列
    要求：
    ①主表的关联列和从表的关联列的类型必须一致，意思一样，名称无要求
    ②主表的关联列要求必须是主键 
    ③在那个表添加唯一约束，那个表就是从表，被引用字段的表为主表

添加外键的几种方法：
#在sql语句末端添加外键
CREATE TABLE IF NOT EXISTS booktype(
id INT PRIMARY KEY,
NAME VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS book(
bid INT PRIMARY KEY,
bname VARCHAR(20) UNIQUE NOT NULL,
price FLOAT DEFAULT 10,
btypeid INT,
FOREIGN KEY (btypeid) REFERENCES booktype(id)
);

#2fk的使用
CREATE TABLE IF NOT EXISTS booktype(
id INT PRIMARY KEY,
NAME VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS book(
bid INT PRIMARY KEY,
bname VARCHAR(20) UNIQUE NOT NULL,
price FLOAT DEFAULT 10,
btypeid INT,
CONSTRAINT fk_book_booktype FOREIGN KEY (btypeid) REFERENCES booktype(id)
);

#3在建表后添加外键
ALTER TABLE book ADD FOREIGN KEY (btypeid) REFERENCES booktype(id);

在有外键的情况下要删除引用列的数据，先删除从表，在删除主表
可以通过以下两种方式来删除主表的记录
#方式一：级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

#方式二：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

三、修改表

语法：ALTER TABLE 表名 ADD|MODIFY|CHANGE|DROP COLUMN 字段名 字段类型 【字段约束】;

#1.修改表名
ALTER TABLE stuinfo RENAME TO students;

#2.添加字段
ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL;

#3.修改字段名
ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL;

#4.修改字段类型
ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ;

#5.删除字段
ALTER TABLE students DROP COLUMN birthday;

四、删除表

DROP TABLE IF EXISTS students;

五、复制表

#仅仅复制表的结构
CREATE TABLE newTable2 LIKE major;

#复制表的结构+数据
CREATE TABLE newTable3 SELECT * FROM girls.`beauty`;

#案例：复制employees表中的last_name,department_id,salary字段到新表 emp表，但不复制数据
CREATE TABLE emp 
SELECT last_name,department_id,salary 
FROM myemployees.`employees`
WHERE 1=2;#（这句判断不成立所以只会复制字段名，不会复制值）

十三、DML

DML(DATA Manipulation LANGUAGE)数据操纵语言

INSERT UPDATE DELETE 对表中的数据的增删改

一、数据的插入

语法：
插入单行：
    INSERT INTO 表名(字段名1,字段名2 ,...) 
    VALUES (值1，值2,...);
插入多行：
    INSERT INTO 表名(字段名1,字段名2 ,...) 
    VALUES(值1，值2,...),
        (值1，值2,...),
        (值1，值2,...);

特点：

①字段和值列表一一对应包含类型、约束等必须匹配

②数值型的值，不用单引号非数值型的值，必须使用单引号

③字段顺序无要求

#案例1：要求字段和值列表一一对应，且遵循类型和约束的限制
INSERT INTO stuinfo(stuid,stuname,stugender,email,age,majorid)
VALUES(1,'吴倩','男','wuqian@qq.com',12,1); 

INSERT INTO stuinfo(stuid,stuname,stugender,email,age,majorid)
VALUES(2,'李宗盛','男','wuqian@qq.com',45,2); 

#案例2：可以为空字段如何插入
方案1：字段名和值都不写
INSERT INTO stuinfo(stuid,stuname,email,majorid)
VALUES(5,'齐鱼','qiqin@qq.com',2); 

方案1：字段名写上，值使用NULL
INSERT INTO stuinfo(stuid,stuname,email,age,majorid)
VALUES(5,'齐鱼','qiqin@qq.com',NULL,2); 

#案例3：默认字段如何插入
方案1：字段名写上，值使用DEFAULT
INSERT INTO stuinfo(stuid,stuname,email,stugender,majorid)
VALUES(7,'齐小鱼','qiqin@qq.com',DEFAULT,2); 

方案2：字段名和值都不写
INSERT INTO stuinfo(stuid,stuname,email,majorid)
VALUES(7,'齐小鱼','qiqin@qq.com',2); 

#案例4：可以省略字段列表，默认所有字段
INSERT INTO stuinfo 
VALUES(8,'林忆莲','女','lin@126.com',12,3);

INSERT INTO stuinfo 
VALUES(NULL,'小黄','男','dd@12.com',12,3);

自增长列：

1、自增长列要求必须设置在一个键上，比如主键或唯一键

2、自增长列要求数据类型为数值型

3、一个表至多有一个自增长列

CREATE TABLE gradeinfo(
    gradeID INT PRIMARY KEY AUTO_INCREMENT,
    gradeName VARCHAR(20)
);
INSERT INTO gradeinfo VALUES(NULL,'一年级'),(NULL,'2年级'),(NULL,'3年级');
或者
INSERT INTO gradeinfo(gradename)VALUES('一年级'),('2年级'),('3年级');

二、数据的修改

修改单表记录：
    UPDATE 表名 SET 字段名 = 新值,
        字段名=新值,...
    WHERE 筛选条件;
修改多表记录：
    sql99语法：
    UPDATE 表一, 别名
    INNER|LEFT|RIGHT JOIN 表2, 别名
    ON 连接条件
    SET 字段名=新值,字段名=新值
    WHERE 筛选条件

UPDATE stuinfo 
SET majorid = 3,
    email='xx@qq.com'
WHERE age<20;
#案例2：修改张无忌的女朋友的手机号为114
UPDATE beauty, b
JOIN boys, bo
ON boy.id=b.boyfriend_id
SET b.phone=114
WHERE bo.name='张无忌'

三、数据的删除

方式1：DELETE语句
    语法：DELETE FROM 表名 WHERE 筛选条件;
方式2：TRUNCATE语句
    语法：TRUNCATE TABLE 表名;

#案例1：删除姓李所有信息
DELETE FROM stuinfo WHERE stuname LIKE '李%';

#案例2：删除表中所有数据
TRUNCATE TABLE stuinfo ;

#【面试题】delete和truncate的区别

1.delete可以添加WHERE条件
  TRUNCATE不能添加WHERE条件，一次性清除所有数据

2.truncate的效率较高

3.如果删除带自增长列的表，
  使用DELETE删除后，重新插入数据，记录从断点处开始
  使用TRUNCATE删除后，重新插入数据，记录从1开始 

4.delete 删除数据，会返回受影响的行数
  TRUNCATE删除数据，不返回受影响的行数
 
5.delete删除数据，可以支持事务回滚
  TRUNCATE删除数据，不支持事务回滚

【面试题】delete和truncate的区别

十四、事务

一、含义
事务：一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行

二、特点（ACID）
A 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行
C 一致性：一个事务可以使数据从一个一致状态切换到另外一个一致的状态
I 隔离性：一个事务不受其他事务的干扰，多个事务互相隔离的
D 持久性：一个事务一旦提交了，则永久的持久化到本地

三、事务的使用步骤 ★
隐式（自动）事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete

显式事务：具有明显的开启和结束

使用显式事务：
①开启事务
set autocommit=0;
start transaction;#可以省略

②编写一组逻辑sql语句
注意：sql语句支持的是insert、update、delete

设置回滚点：
savepoint 回滚点名;

③结束事务
提交：commit;
回滚：rollback;
回滚到指定的地方：rollback to 回滚点名;

四、并发事务
1、事务的并发问题是如何发生的？
多个事务同时操作同一个数据库的相同数据时

2、并发问题都有哪些？
脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据
不可重复读：一个事务多次读取，结果不一样
幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是其他事务“插入”的数据

3、如何解决并发问题
通过设置隔离级别来解决并发问题

4、隔离级别（√代表可避免，×代表不可避免）
脏读不可重复读幻读
read uncommitted:读未提交 × × ×
read committed：读已提交 √ × ×
repeatable read：可重复读 √ √ ×
serializable：串行化 √ √ √

五、事务相关语句

设置隔离级别：
    set session|global  transaction isolation level 隔离级别名;
查看隔离级别：
    SELECT @@tx_isolation;
查看支持的存储引擎：
    SHOW ENGINES;

六、注意事项与演示

#1.演示事务对于delete和truncate的处理的区别
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;（数据没有删除，支持回滚）
ROLLBACK;

SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;(数据已经删除了，写入磁盘了，不支持回滚)
ROLLBACK;

#2.演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点
SELECT * FROM account;（所以只会删除id=25的数据）

十五、视图

含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据

试图与表的区别：

一、创建视图

CREATE VIEW 视图名
AS
查询语句;

1.查询姓名中包含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.查询各部门的平均工资级别
#①创建视图查看每个部门的平均工资
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`;

二、视图的修改
方式一：

CREATE OR REPLACE VIEW  视图名
AS
查询语句;

将myv2的名字改为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 AVG(salary),job_id
FROM employees
GROUP BY job_id;

三、删除视图

语法：DROP VIEW 视图名,视图名,...;
DROP VIEW emp_v1,emp_v2,myv3;

四、查看视图

DESC myv3;
SHOW CREATE VIEW myv3;（查询结果不太清晰话）

五、视图的更新

CREATE OR REPLACE VIEW myv1  （如果myv1存在，那就将SQL语句更新，如果不存在，就创建myv1）
AS
SELECT last_name,email
FROM employees;

1.插入(原始数据表employees也会被插入)
INSERT INTO myv1 VALUES('张飞','zf@qq.com');

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

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

具备以下特点的视图不允许更新
①包含以下关键字的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;（报错）

②常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
#更新
UPDATE myv2 SET NAME='lucy';（报错）

③SELECT中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT MAX(salary) FROM employees) 最高工资
#更新
UPDATE myv3 SET 最高工资=100000;（报错）

④JOIN
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id  = d.department_id;
#更新
UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen';（没报错）
INSERT INTO myv4 VALUES('陈真','xxxx');（报错）

⑤FROM一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
更新
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;（报错）

⑥WHERE子句的子查询引用了FROM子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
    SELECT  manager_id
    FROM employees
    WHERE manager_id IS NOT NULL
);
#更新
SELECT * FROM myv6;
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';（报错）

十六、变量、存储过程、函数

一、变量

变量分为系统变量和自定义变量
系统变量：
　　全局变量
　　会话变量
自定义变量：
　　用户变量
　　局部变量

1.系统变量
说明：变量由系统定义，不是用户定义，属于服务器层面
注意：全局变量需要添加GLOBAL关键字，会话变量需要添加SESSION关键字，如果不写，默认会话级别
使用步骤：

1、查看所有系统变量
SHOW GLOBAL|【SESSION】VARIABLES;
2、查看满足条件的部分系统变量
SHOW GLOBAL|【SESSION】 VARIABLES LIKE '%char%';
3、查看指定的系统变量的值
SELECT @@global|【SESSION】系统变量名;
4、为某个系统变量赋值
方式一：
SET GLOBAL|【SESSION】系统变量名=值;
方式二：
SET @@global|【SESSION】系统变量名=值;

①全局变量

作用域：针对于所有会话（连接）有效，但不能跨重启

#①查看所有全局变量
SHOW GLOBAL VARIABLES;
#②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#③查看指定的系统变量的值
SELECT @@global.autocommit;
#④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

②会话变量

作用域：针对于当前会话（连接）有效

#①查看所有会话变量
SHOW SESSION VARIABLES;
#②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
#③查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
#④为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

2.自定义变量
说明：变量由用户自定义，而不是系统提供的
使用步骤：
1、声明
2、赋值
3、使用（查看、比较、运算等）

(1).用户变量
作用域：针对于当前会话（连接）有效，作用域同于会话变量
赋值操作符为 = 或 :=

①声明并初始化的三种方式

SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

②赋值（更新变量的值）

方式一：
    SET @变量名=值;
    SET @变量名:=值;
    SELECT @变量名:=值;
方式二：
    SELECT 字段 INTO @变量名
    FROM 表;

③使用（查看变量的值）

SELECT @变量名;

(2).局部变量
作用域：仅仅在定义它的BEGIN END块中有效
应用在 BEGIN END中的第一句话

①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;

②赋值（更新变量的值）
方式一：
    SET 局部变量名=值;
    SET 局部变量名:=值;
    SELECT 局部变量名:=值;
方式二：
    SELECT 字段 INTO 局部变量名
    FROM 表;

③使用（查看变量的值）
SELECT 局部变量名;

案例：声明两个变量，求和并打印
用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

局部变量（会报错，没在BEGIN END中）
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

用户变量和局部变量的对比：

二、存储过程

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

①.创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    存储过程体（一组合法的SQL语句）
END

注意：
1、参数列表包含三部分
参数模式参数名参数类型
举例：

IN stuname VARCHAR(20)

参数模式：
IN：该参数可以作为输入，也就是该参数需要调用方传入值
OUT：该参数可以作为输出，也就是该参数可以作为返回值
INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，BEGIN END可以省略
存储过程体中的每条SQL语句的结尾要求必须加分号。
存储过程的结尾符号可以使用 DELIMITER 重新设置
语法：

DELIMITER 结束标记

案例:

DELIMITER $

二、调用语法

CALL 存储过程名(实参列表);

1.空参列表

#案例：插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
    INSERT INTO admin(username,`password`) 
    VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
#调用
CALL myp1()$

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 myp4(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 admin.password = PASSWORD;
    SELECT IF(result>0,'成功','失败');#使用
END $
#调用
CALL myp3('张飞','8888')$

3.创建OUT 模式参数的存储过程

#案例1：根据输入的女神名，返回对应的男神名
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
    SELECT bo.boyname INTO boyname
    FROM boys bo
    RIGHT JOIN
    beauty b ON b.boyfriend_id = bo.id
    WHERE b.name=beautyName ;
END $

#案例2：根据输入的女神名，返回对应的男神名和魅力值
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT) 
BEGIN
    SELECT boys.boyname ,boys.usercp INTO boyname,usercp
    FROM boys 
    RIGHT JOIN
    beauty b ON b.boyfriend_id = boys.id
    WHERE b.name=beautyName ;
END $
#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$

4.创建带INOUT模式参数的存储过程

#案例1：传入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$

3.删除存储过程

#语法：drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#×（一次只能删除一个存储过程）

4.查看存储过程的信息

DESC myp2;×（报错）
SHOW CREATE PROCEDURE  myp2;

三、函数

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

区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果

1.创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END

注意：
　　1.参数列表包含两部分：参数名,参数类型
　　2.函数体：肯定会有RETURN语句，如果没有会报错
　　如果RETURN语句没有放在函数体的最后也不报错，但不建议
　　3.函数体中仅有一句话，则可以省略BEGIN END
　　4.使用 DELIMITER语句设置结束标记

2.调用语法

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.有参有返回
#案例1：根据员工名，返回它的工资
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('kevin') $

#案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS 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;

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

十七、流程控制结构与循环

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

一、分支结构
1.if函数
语法：IF(条件,值1，值2)
功能：实现双分支
执行顺序：如果条件成立，返回值1；条件不成立，返回值2
应用在任何地点（BEGIN END中或外面）

2.case结构
CASE结构作为表达式，可以应用在任何地方；CASE结构作为独立的语句，只能应用在BEGIN END中
语法：
情况1：

CASE 变量|表达式|字段
WHEN 值1 THEN 语句1;
WHEN 值2 THEN 语句2;
...
ELSE 语句n;
END CASE;

情况2：

CASE 
WHEN 条件1 THEN 语句1;
WHEN 条件2 THEN 语句2;
...
ELSE 语句n;
END CASE;

3.if结构
功能：类似于多重IF
只能应用在BEGIN END 中
语法：

IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
....
ELSE 语句n;
END IF;

#案例1：创建函数，实现传入成绩，如果成绩>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';
    ELSET ch='D';
    END IF;
    RETURN ch;
END $
SELECT test_if(87)$

#案例2：创建存储过程，如果工资<2000,则删除，如果5000>工资>2000,则涨工资1000，否则涨工资500
CREATE PROCEDURE test_if_pro(IN sal DOUBLE)
BEGIN
    IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal;
    ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal;
    ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal;
    END IF;    
END $
CALL test_if_pro(2100)$

二、循环结构
分类：
WHILE、LOOP、REPEAT
循环控制：
ITERATE类似于 CONTINUE，继续，结束本次循环，继续下一次
LEAVE 类似于 break，跳出，结束当前所在的循环
智只能应用在BEGIN END中

1.while
语法：

【标签:】WHILE 循环条件 DO
    循环体;
END WHILE【 标签】;

2.loop

可以用来模拟简单的死循环
语法：

【标签:】LOOP
    循环体;
END LOOP 【标签】;

3.repeat
语法：

【标签：】REPEAT
    循环体;
UNTIL 结束循环的条件
END REPEAT 【标签】;

#1.没有添加循环控制语句
#案例：批量插入，根据次数插入到admin表中多条记录
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=insertCount DO
        INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
        SET i=i+1;
    END WHILE;
END $
CALL pro_while1(100)$

2.添加LEAVE语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
CREATE PROCEDURE test_while2(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    a:WHILE i<=insertCount DO
        INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
        IF i>=20 THEN LEAVE a;
        END IF;
        SET i=i+1;
    END WHILE a;
END $
CALL test_while2(100)$


3.添加ITERATE语句
#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
CREATE PROCEDURE test_while3(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    a:WHILE i<=insertCount DO
        SET i=i+1;
        IF MOD(i,2)!=0 THEN ITERATE a;
        END IF;
        INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');    
    END WHILE a;
END $
CALL test_while3(100)$