学习资源
学习目录
学习笔记
- 一、DB、DBMS、SQL的理解
  - DB
  - DBMS
  - SQL
- 二、数据库安装
- 三、常用命令
注释
- 四、查询
①查询各部门的平均工资
②连接①结果集和employees表，进行筛选
1 部门location_id为1700的部门
2 查询在部门location_id为1700的部门工作的员工号
1 工资最高的员工
2 查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓，名
当从0开始可以省略起始索引
1. 查询各个部门平均工资
2. 查询各部门最低的平均工资
3 查询哪个部门的平均工资=2
4. 查询部门信息
1 查询每个job的平均工资
2 查询平均工资最高的 job 信息
1. 查询公司中所有 manager 的员工编号
2. 查询详细信息，满足 employee_id=1.
1. 平均工资最高的部门id
2. 查询指定部门id 内的 manager 的详细信息
- 10. 联合查询
五、DML 语言
- 1. 插入语句
- 2. 修改语句
- 3. 删除语句
  - 方式一：delete
  - 方式二：truncate
六、DDL 语言
- 1. 库的管理
- 2. 表的管理
- 3. 数据类型
  - 3.1. 数值型
    - 3.1.1 整形
    - 1.2 浮点型
  - 3.2. 字符型
    - 3.2.1 短文本
- 4. 约束
  - 4.1. 创建表时添加约束
    - 4.1.1 添加列级约束
    - 4.1.2 添加表级约束
查看指定表中所有的索引，包含主键、外键、唯一
- 5.2 修改表时设置标识列
- 5.3 修改表时删除标识列
七、TCL 事务控制语言
- 1. 事务特点（ACID）
- 2. 创建事务
- 3. 隔离级别
- 4. 保存节点
- 5. delete和truncate在事务使用时的区别
八、视图
- 1. 创建视图
创建视图
使用视图
创建视图: 查看每个部门的平均工资
使用视图
- 2. 修改视图
- 3. 删除视图
- 4. 查看视图
- 5. 练习
  - 1.2 会话变量
- 2. 自定义变量
  - 2.1 用户变量
十、存储过程和函数
- 1. 存储过程
  - 1.1. 创建
  - 1.2. 调用
    - 1.2.1 空参列表
    - 1.2.2 创建带 in 模式参数的存储过程
调用
- 1.2.3 创建带out模式的存储过程
- 1.2.4 创建带 inout 模式参数的存储过程
2.2 调用语法
2.3 练习
- 2.3.1 无参有返回
2.4 查看函数
2.5 删除函数
十一、流程控制结构
- 1. 分支结构
快捷键

学习资源

https://www.bilibili.com/video/BV1xW411u7ax
http://jsrun.net/YL6Kp

XShell

安装资源：software/xshell.zip

安装流程：

修改 hosts 文件，添加以下代码:

# xshell register
127.0.0.1 transact.netsarang.com
127.0.0.1 update.netsarang.com
127.0.0.1 www.netsarang.com
127.0.0.1 www.netsarang.co.kr
127.0.0.1 sales.netsarang.com

Linux安装mysql_
1. 下载宝塔
2. 在宝塔内安装mysql
3. 在阿里云服务器配置安全组放开宝塔和mysql的端口
4. 在宝塔内安全组放开mysql端口
Navicat安装
1. 安装资源：software/navicat.zip
2. 安装流程：
  1. 打开register机，patch，生成，复制验证码，生成激活码。。。。。。

学习目录

学习笔记

一、DB、DBMS、SQL的理解

DB

database 的简称，保存了一系列的数据

DBMS

数据库管理系统（Database Management System）。数据库通过DBMS创建和管理的。

SQL

结构化查询语言（Structure Query Language）：与数据库通信的语言

二、数据库安装

启动服务器：net start mysql0815
停止服务器：net stop mysql0815
连接mysql：
1. $ mysql -h localhost -P 3306 -u root -p (后面如果要输入密码，“-p”和密码之间不可以有空格，除非空格第一位字符是空格)
2. $ mysql -u root -pxxx (xxx是密码)
3. -h 主机名
4. -P 端口号（大写P）
5. -u 用户名
6. -p 密码（小写p）

三、常用命令

常用数据库命令：
1. myssql> ``show database 查看都有哪些数据库
2. myssql> ``use 【数据库名】打开指定数据库
3. myssql> ``show tables 查看数据库内有哪些表
4. myssql> ``show tables from 【数据库名】查看指定数据库内的表名
5. myssql> ``select database() 查看当前所在的数据库
6. myssql> ``desc 【表名】查询表中字段
7. myssql> ``select * from 【表名】查看表中所有元素
8. myssql> ``insert into 【表名】(xxx, yyy) values(xx, yy); 向指定表中插入数据
9. myssql> ``select version() 查看mysql版本
10. $ mysql --version 查看mysql版本
语法规范
1. 不区分大小写，关键字大写，表名列名小写
2. 每条命令使用分号结尾
3. 注释：
  1. 单行注释
    1. 注释
    2. — 注释
  2. 多行注释：/ 注释 /

四、查询

1. 基础查询

select 查询列表 from 表名;
特点：
1. 可以查询：字段、常量值、表达式、函数
2. 结果是一个虚假的表格
查询表中单个字段
1. sql> ``select xxx from 表名;
查询表中多个字段
1. sql>``select xxx,yyy,zzz from 表名;
2. 添加 `` 是为了区分关键字还是字段名
查询表中所有字段
1. sql>``select * from 表名;
查询常量值
sql> select 100;

sql> select 'john';

查询表达式

sql> select 100%98;

查询函数
sql> select version();
起别名
1. sql> select 100%98 as 结果;
2. sql> select last_name as 姓, first_name as 名 from 表名;
3. 更省略方式(使用空格)：sql> select last_name 姓， first_name as 名 from 表名;
4. 注意：当别名中间有空格时，需要在两边加上双引号 sql> select last_name "last name" from 表名
去重
sql> selectdistinctid from 表名
- 号：仅仅是运算符
1. 两个数值型相加为加法运算
2. 一方为字符型，试图将字符型转换为数值型；转换成功做加法，转换失败则将字符型变为0
3. 只要有一方为null，则结果为null
衔接使用 concat: sql> SELECT CONCAT( id, ',', NAME ) FROM 表名;
IFNULL(需要判断是否为空的参数一, 当参数一为空时设置为默认的参数二)

sql> select ifnull(name, '无名氏') as 姓氏, id from 表名

2. 条件查询

语法 select 查询列表 from 表名 where 筛选条件
分类
1. 条件运算符 > < = != <> >= <=
2. 逻辑表达式 && || ! and or not
3. 模糊 like between and in is null is not null
  1. select * from 表名 where name like '%a%'; # 字符必须使用单引号
  2. select * from 表名 where 字段名between100 and 120;
  3. between 包含临界值（两端都是闭区间），两个值可以调换位置
  4. 查询员工工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
    1. select * from 表名 where 字段名 in('aa', 'bb', 'cc');
    2. in列表中的值类型必须是统一或兼容的
  5. is
    1. select * from 表名 where 字段名 is null;
    2. = 或 <> 不能用于判断是否为 null 值
    3. is 只能接 null 或 not null
通配符
1. % 任意多个字符，包含0个字符
2. **_** 任意单个字符
3. 指定转义字符（指定后类似 \n 中 \ 的作用）：escape
  sql> ``select * from 表名 where 字段名 like '_$_%' escape '$';

3. 排序查询

语法：select * from 表名 where 筛选条件order by排序列表 asc/desc
asc 升序，desc 降序，都不写默认是升序（asc）
order by 一般是放在查询语句最后面，limit 除外
复杂查询
1. select *, salary * 12 * (1 + ifnull(price, 0)) 年薪 from 表名 order by ``salary * 12 * (1 + ifnull(price, 0)) desc;
2. 上面的查询语句太复杂，可以使用别名: select *, salary * 12 * (1 + ifnull(price, 0)) 年薪 from 表名 order by 年薪 desc;
3. 多个字段排序 order by 字段一 asc, 字段二 desc;
4. **not between 800 and 1000**
  4. 常见函数
隐藏了实现希捷，提高了代码重用性
select 函数名 (实参列表) from 表名
一个汉字 3 个字节

4.1 分类

4.1.1 单行函数
单行函数：concat、length、ifnull 等
1. upper
2. lower
3. substr (索引从 1 开始)（两个参数代表从索引开始到后面）（三个参数代表从索引开始到索引结束）
4. substring
5. instr 返回字串第一次出现的索引，找不到就返回0、
6. trim
  1. 另一种用法：去除前缀和后缀指定的字符 select trim('a' from 'aaaa123aaaa' from 表名;
7. lpad 使用指定的字符填充左边不足的长度: select lpad('hahah', 0, '***') as out_put;
  1. 参数一：原始字符串
  2. 参数二：目标字符串长度
  3. 参数三：替换填充的字符
8. rpad 右填充
9. replace 全局都替换
数学函数
1. round 四舍五入
2. cell 向上取整（>= 该参数的最小整数）
3. floor 向下取整（<= 该参数的最大整数）
4. truncate 截断（select truncate(1.69999, 1)）
5. mod 取余 ( a - a/b*b )
日期函数
1. now() 返回当前系统日期+时间
2. curdate 当前系统日期
3. curtime 当前系统时间
4. year 获取年
5. month 获取月
6. str_to_date 将字符通过指定的格式转换为日期： select str_to_date('1998-12-21', '%Y-%c-%d');
7. date_format 将日期转换为字符 select data_format(now(), '%y年%m月%d日') as out_put;
其他函数
1. version
2. database
3. user

流程控制函数

if(判断条件, 执行一，执行二) select if(10 < 5, ‘大’, ‘小’);

case

没有else时，可以省略

select *,
case 字段名
when 30 then 字段操作表达式
when 40 then salary * 1.2
else salary
end
from 表名;

select *,
case
when salary > 30 then 'a'
when salary > 20 then 'b'
else 'c'
end
as 工资级别
from 表名;

4.1.2 分组函数，统计使用（聚合函数，统计函数，组函数）

sum、avg、max、min、count、

sum、avg 处理 数值型
max、min、count 处理 任何类型
以上分组函数都忽略 null 值
可以和 distinct 联合使用，去重 select sum(distinct salary) from 表名;
统计行数: select count(*) from 表名;
和分组函数一同查询的字段要求是 group by 后的字段

5. 分组查询

语法： select 分组函数, 列（要求只能出现在 group by 的后面） from 表
select max(salary), job_id from 表名group byjob_id;
select count(*), location_id from 表名 group by location_id;

添加分组后查询： select count(*), department_id from employees group by department_id having count(*) > 2;

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

SELECT
MAX( salary ),
job_id 
FROM
employees 
WHERE
commission_pct IS NOT NULL 
GROUP BY
job_id 
HAVING
MAX( salary ) > 12000;

分组查询中的筛选条件分为两类：
1. 分组前筛选原始表 group by子句的前面 where
2. 分组后筛选分组后的结果集 group by子句的后面 having
  
  分组函数做条件肯定是放在 having 子句中能用分组前筛选的，就优先使用分组前筛选

添加排序

在 group by ... （有having就在having后面）后面添加 order by 即可

6. 连接查询

表名.字段名
表别名
1. 可以为表起别名 from 表名 as 表别名，表名1 表别名1
2. 如果表起了别名，则查询的字段就不能使用原来的表名去限定
总结
1. 多表连接结果是多表的交集部分
2. n表连接，至少需要 n - 1 个连接条件
3. 多表的顺序没有要求
4. 一般需要为表起别名

自连接

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`;

7. sql99 语法

select 查询列表
from 表1 别名 
[连接类型] join 表2 别名
on 连接条件
[where 筛选条件]
[
group by 分组
  [having 筛选条件]
]
[order by 排序列表]

连接类型
1. 内连接（inner）
2. 左外连接( left )
3. 右外连接( right )
4. 全外( full )( mysql 不支持 )
5. 交叉( cross )
内连接
1. 特点
  1. inner 可以省略
  2. 筛选条件放在where后面，连接条件放在on后面，提高了分离性，便于阅读
  3. 查询多表的交集
2. 语法
```
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
```
外连接
1. 特点
  1. 外连接的查询结果为主表中的所有记录
    1. 从表中有匹配的，显示匹配的值
    2. 从表中没有匹配的，显示null
    3. 外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录
2. 左外连接：left join 左边的是主表
3. 右外连接：right join 右边的是主表
4. 左外和右外交换两个表的顺序，返回同样结果
全外连接
1. 结果为 内连接 + 表1有表2没有 + 表2有表1没有
交叉连接
1. 笛卡尔乘积 CROSS JOIN
  8. 子查询
出现在其他语句内部的select语句，称为子查询或内查询
内部嵌套其他select语句的查询，称为外查询或主查询
分类
1. 位置
  1. select后面仅支持 标量子查询
  2. from 后面 表子查询
  3. where或having后面标量 / 列** / 行子查询 (重点)**
  4. exists后面 表子查询
2. 结果集行列数
  1. 标量子查询（结果：一行一列）
  2. 列子查询（一列多行）
  3. 行子查询（一行多列）
  4. 表子查询（多行多列）

demo:

select first_name 
from employees
where department_id
in (
--子查询 
select department_id
from departments
where location_id=1700
)

where 或 having 后面
1. 标量查询
  1. 特点
    1. 子查询放在小括号内
    2. 子查询一般放在条件的右边
    3. 标量子查询一般搭配单行操作符使用 > < >= <= <>
    4. 列子查询一般搭配多行操作符使用 in、any/some、all
    5. 子查询优先于主查询执行，主查询的条件用到了子查询的结果
  2. 案例
    1. 案例1：谁的工资比 Abel 高
      1. 查询 Abel 的工资 SELECT salary FROM employee WHERE last_name='Abel'
      2. 查询员工的信息，满足 salary>1查询出的结果
        SELECT * FROM employees WHERE salary>(SELECT salary FROM employees WHERE last_name='Abel');
    2. 案例2：返回job_id与141号员工相同，salary比143号员工多的员工的姓名、job_id和工资
      1. 查询141号员工的job_id
        SELECT job_id FROM employees WHERE employee_id=141;
      2. 查询143号员工的salary
        SELECT salary FROM employees WHERE employee_id=143;
      3. 查询job_id与141号员工相同，salary比143号员工多的员工的姓名、job_id和工资 SELECT 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. 案例3：返回工资最少的员工的last_name,job_id和salary
      1. 查询公司的最低工资
        SELECT MIN(salary) FROM employees
      2. 查询结果
        SELECT last_name,job_id,salary FROM employees WHERE salary=(SELECT MIN(salary) FROM employees);
    4. 案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
      1. 查询50号部门最低工资
        SELECT MIN(salary) FROM employees WHERE ``department_id=50
      2. 查询每个部门最低工资
        SELECT ``department_id,``MIN(salary) FROM employees GROUP BY ``department_id
      3. 查询最低工资大于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. 列子查询
  1. 特点
    1. 搭配多行操作符使用，in/not in , any, some, all
  2. 案例
    1. 案例1：返回location_id是1400或1700的部门编号
      1. 查询location_id是1400或1700的部门编号
        SELECT DISTINCT ``department_id FROM deployees WHERE location_id IN(1400, 1700)
      2. 查询员工姓名，要求部门号是1中的某一个
        SELECT last_name FROM employees WHERE department_id IN (``SELECT DISTINCT ``department_id FROM deployees WHERE location_id IN(1400, 1700));
    2. 案例2：返回其他部门中比job_id为‘IT_PROG’部门任一工资低的员工的工号、姓名、job_id和salary
      1. 查询job_id为‘IT_PROG’部门任一工资
        SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG'
      2. 查询其他部门比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 ';
    3. 案例3：返回其他部门中比job_id为‘IT_PROG’部门所有工资低的员工的工号、姓名、job_id和salarySELECT employee_id,last_name,job_id,salary FROM employees WHERE salary <ALL(``SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG'``) AND job_id <> 'IT_PROG ';
3. 行子查询（一行多列或多行多列）
  1. 案例
    1. 查询员工编号最小且工资最高的员工信息
      1. 查询最小员工编号 SELECT MIN(employee_id) FROM employees
      2. 查询最高工资 SELECT MAX(salary) FROM employees WHERE employee_id=(``SELECT MIN(employee_id) FROM employees``) AND salary=(SELECT MAX(salary) FROM employees);
      3. 另一种思路: SELECT * FROM employees WHERE (employee_id, salary)=(SELECT MIN(employee_id), MAX(salary) FROM employees);
select后面
1. 案例: 查询每个部门的员工个数
  SELECT d.*, (SELECT COUNT(*) FROM employee e WHERE e.department_id = d.department_id) 个数 FROM departments d;
2. 案例：查询员工号=102的部门名
  SELECT (SELECT department_name FROM departments d INNER JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = 102) 部门名;
from后面
1. 注意：将子查询的结果集当作一个表必须给这个表起别名
2. 案例：查询每个部门的平均工资的工资等级
  1. SELECT AVG(salary), department_id FROM employees GROUP BY department_id;
  2. SELECT ag_dep.*, g.grade_levelFROM (SELECT AVG(salary), 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;
exists后面（相关子查询）
1. exists语法：结果 1或0
2. 案例一：查询有员工的部门名SELECT department_name FROM departments d WHERE EXISTS(SELECT * FROM employees e WHERE d.department_id=e.```department_id```)
3. 案例二：查询没有女朋友的男神信息
  1. SELECT bo.* FROM boys bo WHERE NOT EXISTS (SELECT boyfrient_id FROM beauty b WHERE bo.id= b.boyfrient_id);
  2. 不使用exists: SELECT bo.* FROM boys bo WHERE bo.id NOT IN ( SELECT boyfriend_id FROM beauty);
练习
- 查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资 ```sql
  ①查询各部门的平均工资
  SELECT AVG(salary), department_id FROM employees GROUP BY department_id

②连接①结果集和employees表，进行筛选

SELECT employee_id, last_name, salary, e.department_id FROM employees e INNER JOIN ( SELECT AVG( salary ) ag, department_id FROM employees GROUP BY department_id ) ag_dep ON e.department_id = ag_dep.department_id;


   - 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
```sql
# 1 姓名中包含字母u的员工
SELECT
    DISTINCT department_id
FROM
    employees 
WHERE
    last_name LIKE '%u%';
# 2 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT
    employee_id,
    last_name 
FROM
    employees
WHERE
    department_id 
    IN ( 
        SELECT
            DISTINCT department_id
        FROM
            employees 
        WHERE
            last_name LIKE '%u%'
    );

查询在部门location_id为1700的部门工作的员工号 ```sql
1 部门location_id为1700的部门
SELECT DISTINCT department_id FROM employees WHERE location_id = 1700;

2 查询在部门location_id为1700的部门工作的员工号

SELECT employee_id FROM employees WHERE department_id = ANY ( SELECT DISTINCT department_id FROM employees WHERE location_id = 1700 );


   -  查询管理者是King的员工姓名和工资（不知道是多列还是单列的时候保险的做法是使用 IN）
```sql
# 1 管理者是King的员工id
SELECT
    employee_id 
FROM
    employees 
WHERE
    last_name = 'King';
# 2 查询管理者是King(管理者叫king的可能有很多，所以这里使用in)的员工姓名和工资
SELECT
    last_name,
    salary 
FROM
    employees 
WHERE
    manager_id IN (
        SELECT
            employee_id 
        FROM
            employees 
        WHERE
            last_name = 'King' 
    );

查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓，名 ```sql
1 工资最高的员工
SELECT MAX( salary ) FROM employees;

2 查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓，名

SELECT
CONCAT(first_name, last_name) “姓.名” FROM employees WHERE salary =( SELECT MAX( salary ) FROM employees );

<a name="jOzbQ"></a>
# 
<a name="RmNin"></a>
### 9. 分页查询
- 语法
```sql
SELECT
    查询列表
FROM
    表名
[
    JOIN TYPE JOIN 表2
  ON
    连接条件
  WHERE
    筛选条件
  GROUP BY
    分组字段
  HAVING
    分组后的筛选
  ORDER BY
    排序的字段
]
limit
    [offset,] size;

offset 条目的起始索引（索引从0开始）
size 条目个数
- 特点
limit 语句放在查询语句的最后
公式 LIMIT (page - 1) * size, size
- 示例
示例一：查询前5条员工信息 ```sql SELECT * FROM employees LIMIT 0, 5;

当从0开始可以省略起始索引

SELECT * FROM employees LIMIT 5;


   - 示例二：有奖金的员工信息，并且工资较高的前10名显示出来
```sql
SELECT
    *
FROM
    employees
WHERE
    commission_pct IS NOT NULL
ORDER BY
     salary DESC
LIMIT
    10;

示例三：查询平均工资最低的部门信息
- 方法一（复杂） ```sql
  1. 查询各个部门平均工资
  SELECT avg( salary ), department_id FROM employees GROUP BY department_id;

2. 查询各部门最低的平均工资

SELECT MIN( ag ) FROM ( SELECT avg( salary ), department_id FROM employees GROUP BY department_id ) ag_dep;

3 查询哪个部门的平均工资=2

SELECT
department_id FROM employees GROUP BY department_id HAVING AVG(salary)=( SELECT MIN( ag ) FROM ( SELECT avg( salary ), department_id FROM employees GROUP BY department_id ) ag_dep; );

4. 查询部门信息

SELECT d.* FROM departments d WHERE d.department_id=( SELECT
department_id FROM employees GROUP BY department_id HAVING AVG(salary)=( SELECT MIN( ag ) FROM ( SELECT avg( salary ), department_id FROM employees GROUP BY department_id ) ag_dep ) );


      - 方法二 （简便方法）
```sql
# 1. 获取平均工资最低的
SELECT
    department_id 
FROM
    employees 
GROUP BY
    department_id 
ORDER BY
    AVG( salary ) 
    LIMIT 1; # 排序（升序）后取第一项即可
# 2. 获取平均工资最低的部门信息
SELECT
    *
FROM
    departments
WHERE
    department_id=(
      SELECT
      department_id 
    FROM
      employees 
    GROUP BY
      department_id 
    ORDER BY
      AVG( salary ) 
      LIMIT 1
  );

示例四：查询平均工资最高的 job 信息 ```sql
1 查询每个job的平均工资
SELECT AVG(salary), job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1;

2 查询平均工资最高的 job 信息

SELECT * FROM employees WHERE job_id = ( SELECT AVG(salary), job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1 );


   - 示例五：查询平均工资高于公司平均工资的部门有哪些？
```sql
# 1. 查询公司平均工资
SELECT
    AVG(salary)
FROM
    employees;
# 2. 查询各部门平均工资
SELECT
    department_id,
    AVG(salary)
FROM
    employees
GROUP BY
    department_id;
# 3. 查询各部门平均工资高于公司平均工资
SELECT
    department_id,
    AVG(salary)
FROM
    employees
GROUP BY
    department_id
HAVING
    AVG(salary) > (
        SELECT
            AVG(salary)
        FROM
            employees;
    );

示例六：查询公司中所有 manager 的详细信息 ```sql
1. 查询公司中所有 manager 的员工编号
SELECT DISTINCT manager_id FROM employees;

2. 查询详细信息，满足 employee_id=1.

SELECT * FROM employees WHERE employee_id = ANY( SELECT DISTINCT manager_id FROM employees );


   - 示例七：各个部门中，最高工资中最低的哪个部门的最低工资是多少
```sql
 # 1. 查询各部门的最高工资中最低的那个
SELECT
    department_id
FROM
    employees
GROUP BY
    department_id
ORDER BY
    MAX(salary)
LIMIT
    1;
# 2. 找出1中各个部门最高工资中最低的工资
SELECT
    MIN(salary)
FROM
    employees
WHERE
    department_id=(
        SELECT
            department_id
        FROM
            employees
        GROUP BY
            department_id
        ORDER BY
            MAX(salary)
        LIMIT
            1
    );

示例八：查询平均工资最高的部门的 manager 的详细信息：last_name, department_id, email, salary ```sql
1. 平均工资最高的部门id
SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG( salary ) DESC LIMIT 1;

2. 查询指定部门id 内的 manager 的详细信息

SELECT last_name, depatment_id, email, salary FROM employees e INNER JOIN departments d ON d.manager_id = e.employee_id WHERE d.department_id=( SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG( salary ) DESC LIMIT 1; );


- 练习
![20201012111331.png](https://cdn.nlark.com/yuque/0/2020/png/339035/1602472434059-6592233f-2959-4f80-bb54-5d1dba23cd78.png#align=left&display=inline&height=421&margin=%5Bobject%20Object%5D&name=20201012111331.png&originHeight=421&originWidth=709&size=417575&status=done&style=none&width=709)
```sql
SELECT
    majorid,
    COUNT(*) 
FROM
    student 
GROUP BY
    majorid;

SELECT
    studentno,
    AVG(score),
    MAX(score)
FROM
    result
GROUP BY
    studentno;

# 1. 姓张的学生id
SELECT
    studentno,
    studentname
FROM
    student
WHERE
    studentname LIKE '张%';
# 2. 查找指定学生的最低分
SELECT
    r.studentno,
    s.studentname,
    r.score
FROM
    result r
    INNER JOIN student s 
    ON r.studentno=s.studentno
GROUP BY
    studentno
HAVING
    MIN(r.score) > 60
    AND
    r.studentno=ANY(
        SELECT
            studentno
        FROM
            student
        WHERE
            studentname LIKE '张%'
    );
 # 更简单的方法
 SELECT
    s.studentno,
    s.studentname,
    MIN(score)
FROM 
    student s
JOIN 
    result r
    ON
        s.studentno=r.studentno
WHERE
    s.studentname LIKE '张%'
GROUP BY
    s.studentno
    HAVING
        MIN(score)>60;

SELECT    
    m.majorname,
    s.studentname    
FROM
    student s
JOIN
    major m
ON
    s.majorid=m.majorid
WHERE
    DATEDIFF(borndate, '1988-1-1')>0;
# s.borndate>"1988-1-1";

SELECT
    majorid,
    sex,
    count(*) 
FROM
    student 
GROUP BY
    sex,
    majorid 
ORDER BY
    majorid,
    sex DESC;
 # 方法二：显示的更加详细
 SELECT
    majorid,
    (
        SELECT 
            COUNT(*) 
        FROM 
            student 
        WHERE 
            sex='男' 
            AND 
            majorid=s.majorid
    ) 男,
    (
        SELECT 
            COUNT(*) 
        FROM 
            student 
        WHERE 
            sex='女' 
            AND 
            majorid=s.majorid
    ) 女
FROM
    student s
GROUP BY
    majorid;

# 1. 查找张翠山的专业id(如果有重名的怎么办？)
SELECT
    majorid
FROM
    student
WHERE
    studentname='张翠山'
LIMIT
    1;
# 2. 查找指定专业的所有学生id
SELECT
    studentno
FROM
    student
WHERE
    majorid=(
        SELECT
            majorid
        FROM
            student
        WHERE
            studentname='张翠山'
        LIMIT
            1
    );
# 3. 查找指定的学生id中最低分
SELECT
    MIN(score)
FROM
    result
WHERE
    studentno=ANY(
        SELECT
            studentno
        FROM
            student
        WHERE
            majorid=(
                SELECT
                    majorid
                FROM
                    student
                WHERE
                    studentname='张翠山'
                LIMIT
                    1
            )
    );

# 1. 成绩大于60分的学生id
SELECT
    studentno
FROM
    result
WHERE
    score>60;
# 2. 查询指定学生id的姓名、密码、专业名
SELECT
    s.studentname,
    s.loginpwd,
    m.majorname
FROM
    student s
INNER JOIN
    major m
    ON
        m.majorid=s.majorid
WHERE
    s.studentno=ANY(
        SELECT
            studentno
        FROM
            result
        WHERE
            score>60
    );
# 方法二：
SELECT
    DISTINCT studentname,
    loginpwd,
    majorname
FROM
    student s
JOIN
    major m
    ON
        s.majorid = m.majorid
JOIN
    result r
    ON
        s.studentno = r.studentno
WHERE
    r.score>60;

# 邮箱位数分组
SELECT
    LENGTH(email) 邮箱位数,
    count(*) 学生数
FROM
    student
GROUP BY
    邮箱位数
ORDER BY
    邮箱位数;

# 多表联查，并列JOIN即可
SELECT
    s.studentname,
    m.majorname,
    r.score
FROM
    student s
JOIN
    major m
    ON
        m.majorid=s.majorid
LEFT JOIN # 为了显示一些没有专业的同学信息，所以这里使用 LEFT JOIN
    result r
    ON
        r.studentno=s.studentno;

# 左连接
SELECT
    majorname
FROM
    major m
LEFT JOIN
    student s
    ON
        s.majorid=m.majorid
WHERE
    s.studentno IS NULL;
# 右连接
SELECT
    majorname
FROM
    student s
RIGHT JOIN
    major m
    ON
        s.majorid=m.majorid
WHERE
    s.studentno IS NULL;

# 1. 查询有成绩的学生id
SELECT
    DISTINCT studentno
FROM
    result;
# 2. 查询不在有成绩学生id中的其他学生人数
SELECT
    count(*)
FROM
    student
WHERE
    studentno NOT IN(
        SELECT
            DISTINCT studentno
        FROM
            result
    );
# 方法二：
SELECT
    COUNT(*)
FROM 
    student s
LEFT JOIN 
    result r
    ON
      s.studentno = r.studentno
WHERE
    r.id IS NULL;

10. 联合查询

将多条查询语句的结果合并成一个结果
语法 查询语句一 UNION 查询语句二 [_**UNION**_ …]
应用场景：当查询结果来自于多个表，且这些表没有直接的连接关系，但是查询信息一致时，可使用联合查询
特点：
1. 查询列数一致
2. 查询每列的类型和顺序最好一致
3. 默认去重，使用 **UNION ALL** 可以包含重复项

DEMO演示：

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id > 90;
# ====>
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id > 90;

五、DML 语言

插入 _**insert**_，修改 update , 删除 delete
**

1. 插入语句

插入方式一
- 语法: INSERT INTO 表名 (列名, ...) VALUES (值1, ...)
- 要求：
  - 插入的值类型要和列的类型一致或兼容
  - 若无值要么不写，要么使用 NULL 填充
  - 列的顺序可以调换
  - 列和值的个数必须匹配
  - 可以省略列名，这时默认所有列和表中列的顺序一致
- 特点:
  - 支持插入多行，方式二不支持 INSERT INTO beauty(id, NAME, phone) SELECT 26,'宋茜','11809866'
  - 支持子查询，方式二不支持
插入方式二
- 语法：INSERT INTO 表名 SET 列名1=值1,**列名2=值2, ……**

2. 修改语句

语法: UPDATE 表名 SET 列1=新值1, 列2=新值2, ...... WHERE 筛选条件;
语法修改多表（SQL92）：UPDATE 表1 别名, 表2 别名**SET 列1=新值1, 列2=新值2, ...... WHERE 连接条件 AND 筛选条件**
语法修改多表（SQL99）：UPDATE 表1 别名 INNER|LEFT|RIGHT JOIN 表2 别名 ON 连接条件 SET 列1=值1, 列2=值2 ...... WHERE 筛选条件

3. 删除语句

方式一：delete
语法：DELETE FROM 表名 WHERE 筛选条件
多表删除（SQL92）：DELETE 别名 FROM 表1 别名1，表2 别名2 WHERE 连接条件 AND 筛选条件 （DELETE 后面的别名决定删除哪个表的，写哪个表的别名就删除哪个表达，要都删除的话就用逗号连接）
多表删除（SQL99）: DELETE 表1的别名, 表2的别名 FROM 表1 别名 INNER | LEFT | RIGHT JOIN 表2 别名 ON 连接条件 WHERE 筛选条件

方式二：truncate
语法：TRUNCATE TABLE 表名 （不允许添加 WHERE 筛选条件，清空列表）
特点：
- TRUNCATE 清空数据后，自增长列恢复从1开始，而 DELETE 删除数据后从断点开始
- TRUNCATE 不能回滚，DELETE 可以回滚
  六、DDL 语言
  数据定义语言
  1. 库的管理
  创建、修改、删除
  1.1 创建库
语法： CREATE DATABASE 库名
CREATE DATABASE IF NOT EXISTS 库名 创建数据库前先判断数据库是否存在，若不存在则创建

1.2 修改库
现在只能 更改库的字符集 （为了安全性）ALTER DATABSE 库名 CHARACTER SET

1.3 删除库
DROP DATABASE IF EXISTS 库名;

2. 表的管理

创建、修改、删除

2.1 创建表

CREATE TABLE 表名 (
  列名 列的类型  [(长度) 约束], 
  列名 列的类型  [(长度) 约束], 
  列名 列的类型  [(长度) 约束], 
  ......
)

2.2 修改表

alter table 表名change | add | modify | dropcolumn 列名

修改列名 ALTER TABLE 表名 CHANGE COLUMN 原始列名新列名数据类型;
修改列的类型或约束 ALTER TABLE 表名 MODIFY COLUMN 列名新数据类型;
添加新列 ALTER TABLE 表名 ADD COLUMN 新列名;
删除列 _ALTER TABLE 表名 DROP COLUMN 列名;
修改表名 ALTER TABLE 表名 RENAME TO 新表名;

2.3 删除表

DROP TABLE IF EXISTS 表名;

2.4 复制表

仅复制表的结构 CREATE TABLE 新表名 LIKE 表名
复制表结构+数据 CREATE TABLE 新表名 SELECT * FROM 表名
仅复制部分数据 CREATE TABLE 新表名 SELECT * FROM 表名 WHERE 筛选条件;
仅复制部分字段 CREATE TABLE 新表名 SELECT 字段名 FROM 表名 WHERE0;# 0代表false

3. 数据类型

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

3.1. 数值型

3.1.1 整形

tinyint、smallint、mediumint、int/integer、bigint

有符号（默认）
无符号 INTUNSIGNED
如果超出范围就会报错
位数不够左边填充0，需要搭配 zerofill 使用

1.2 浮点型
float(M, D)、double**(M, D)、dec/decimal(M, D)**
M 整数部分 + 小数部分长度（可省略）
D 小数部分长度（可省略）

3.2. 字符型

3.2.1 短文本

char ，耗费空间，效率高
varchar ，效率低
存储的字段变化不大比较固定推荐使用char，存储的字符长度变化较大推荐使用varchar

数据类型	定义方式	特点	空间耗费	效率	适用场景
char	char(M) M可以省略，默认为1	固定长度的字符，即使存储字符没有达到设定的最大长度，依然填充至最大长度	大	高	存储的字符长度比较固定
varchar	varchar(M) M不可以省略	可变长度字符，在没有达到最大长度时，存多少字符，长度就是多少，不额外填充	小	低	存储的字符长度无法固定，变化幅度大

枚举类型 ```sql CREATE TABLE tableEnum ( e1 ENUM(‘男’,’女’) );

INSERT INTO tableEnum VALUES(‘男’);

INSERT INTO tableEnum VALUES(‘女’);

INSERT INTO tableEnum VALUES(‘妖’); # 插入失败


<a name="N7wTm"></a>
#### 3.3. 日期型
- 要求日期必须用单引号括起来
- 分类
   - date 只有日期没有时间
   - time 只有时间
   - year 只有年
   - datetime 8字节 1000-9999 不受时区影响
   - timestamp 时间戳，4字节 1970-2038 受时区影响
```sql
DROP TABLE IF EXISTS table_time;
CREATE TABLE table_time (
    t1 DATETIME,
    t2 TIMESTAMP
);
INSERT INTO table_time VALUES (NOW(), NOW());
SELECT * FROM table_time;  
SHOW VARIABLES LIKE 'time_zone';
SET time_zone='+9:00';

4. 约束

分类（六大约束）
- NOT NULL 非空
- DEFAULT 默认
- PRIMARY KEY 主键（唯一且非空，只能有一个，允许组合（但不推荐使用组合））
- UNIQUE 唯一（可以为空，可以有多个，允许组合（但不推荐使用组合））
- CHECK 检查约束（MYSQL 不支持）
- FOREIGN KEY 外键
添加约束时机
- 创建表
- 修改表
添加分类
- 列级约束
  - 六大约束都支持，但外键约束无效果
- 表级约束
  - 除了非空、默认，其他都支持

4.1. 创建表时添加约束

4.1.1 添加列级约束

直接在字段名和类型后面追加约束类型即可
仅支持：默认、非空、主键、唯一

DROP TABLE
IF
    EXISTS stuinfo;
CREATE TABLE stuinfo (
    id INT PRIMARY KEY,
    stuName VARCHAR ( 20 ) NOT NULL,
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorId INT REFERENCES major(id)
);
DESC stuinfo;
# 查看指定表中所有的索引，包含主键、外键、唯一
SHOW INDEX FROM stuinfo;

4.1.2 添加表级约束

在各个字段的最下面
_** [ CONSTRAINT 约束名 ] 约束类型 ( 字段名 )**_ ```sql DROP TABLE IF EXISTS stuinfo;

CREATE TABLE stuinfo ( id INT, stuName VARCHAR ( 20 ), 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 major1(id) );

DESC stuinfo;

查看指定表中所有的索引，包含主键、外键、唯一

SHOW INDEX FROM stuinfo;


<a name="KDs6l"></a>
##### 4.1.3 外键
- 外键约束一般命名（因为约束名不可以重复）
_**`CONSTRAINT fk_主表名_从表名 FOREIGN KEY (主表内外键字段名) REFERENCES  从表名(从表内的主键名)`**_  （fk是 foreign key 的缩写）
- 特点
   - 从表设置外键关系
   - 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
   - 主表的关联列必须是一个key（一般是主键或唯一）
   - 插入数据时，先插入主表，再插入从表
   - 删除数据时，先删除从表，在删除主表
- 级联删除 _**`ON DELETE CASCADE`**_
- 级联置空 _**`ON DELETE SET NULL`**_
<a name="w4ilT"></a>
#### 4.2. 修改表时添加约束
<a name="Hq3LC"></a>
##### 4.2.1 添加非空约束
_**`ALTER TABLE 表名 MODIFY COLUMN 字段名 类型 NOT NULL;`**_
- 删除非空约束？ —— 不写 NOT NULL 即可<br />_**`ALTER TABLE 表名 MODIFY COLUMN 字段名 类型;`**_
<a name="Z4ZLx"></a>
##### 4.2.2 添加默认约束
_**`ALTER TABLE 表名 MODIFY COLUMN 字段名 类型 DEFAULT 默认值;`**_
<a name="J4gdR"></a>
##### 4.2.3 添加主键约束
- 列级约束<br />_**`ALTER TABLE 表名 MODIFY COLUMN 字段名 类型 PRIMARY KEY;`**_
- 表级约束<br />_**`ALTER TABLE 表名 ADD PRIMARY KEY(字段名);`**_
<a name="3q7tN"></a>
##### 4.2.4 添加唯一约束
- 列级约束<br />_**`ALTER TABLE 表名 MODIFY COLUMN 字段名 类型 UNIQUE;`**_
- 表级约束<br />_**`ALTER TABLE 表名 ADD UNIQUE(字段名);`**_
_
<a name="wzE4c"></a>
##### 4.2.5 添加外键
_**`ALTER TABLE 从表名 ADD FOREIGN KEY `**__**`(`**__**` 从表外键字段名 `**__**`)`**__**` REFERENCES 主表名(主表字段名);`**_<br />_
- 能否给约束添加名字? —— 可以<br />_**`ALTER TABLE 从表名 ADD `**__**`CONSTRAINT fk_主表名_从表名`**__**` FOREIGN KEY (从表外键字段名) REFERENCES 主表名(主表字段名);`**_<br />   
<a name="Kga6X"></a>
#### 4.3. 修改表时删除约束
<a name="pKChp"></a>
##### 4.3.1 删除非空约束
不写 not null 即可删除，因为 默认 是可以为空的 null<br />_**`ALTER TABLE 表名 MODIFY COLUMN 字段名 类型;`**_<br />_
<a name="2yE5O"></a>
##### 4.3.2 删除默认约束
不写默认约束即可<br />_**`ALTER TABLE 表名 MODIFY COLUMN 字段名 类型;`**_
<a name="YV3HL"></a>
##### 4.3.3 删除主键约束_
_**`ALTER TABLE 表名 `**__**`DROP PRIMARY KEY`**__**`;`**_
<a name="ZwqQO"></a>
##### 4.3.4 删除唯一约束
_**`ALTER TABLE 表名 MODIFY COLUMN 字段名 类型;`**_<br />_<br />不写主键约束即可，或者像下面的显式删除。<br />_**`ALTER TABLE 表名 `**__**`DROP INDEX 唯一约束名(没写的话就是字段名)`**__**`;`**_
<a name="USsaj"></a>
##### 4.3.5 删除外键约束
_**`ALTER TABLE 表名 `**__**`DROP FOREIGN KEY ( 外键约束名(没写的话就是外键字段名) )`**__**`;`**_
<a name="RCo31"></a>
### 5. 标识列
自增列，系统提供默认的从1开始的序列值 
<a name="SOA4q"></a>
#### 5.1 创建表时设置标识列
_**`AUTO_INCREMENT`**_
```sql
CREATE TABLE 表名 (
  字段名 数据类型 PRIMARY KEY AUTO_INCREMENT
);

自增长列可以设置 “步长”
SET auto_increment_increment=3;
在想插入的位置手动设置值，之后的位置都会在手动设置值之上继续递增
特点
- 标识列必须和主键搭配吗？ —— 不一定，但要求是一个key（就是说是个“唯一”约束的字段也可以设置为标识列）
- 一个表中只能有一个** **标识列
- 标识列类型只能为 数值型
- 标识列可以更改步长

5.2 修改表时设置标识列

ALTER TABLE 表名 MODIFY COLUMN 字段名数据类型 PRIMARY KEYAUTO_INCREMENT;

5.3 修改表时删除标识列

不写 autoincrement 即可
ALTER TABLE 表名 MODIFY COLUMN 字段名数据类型;_

七、TCL 事务控制语言

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

1. 事务特点（ACID）

原子性（Atomicity) 不可分割的最小单位
一致性（Consistency）
隔离性（Isolation）一个事务使用的数据对并发的其他事务是隔离的
持久性（Durability）一个事务一旦被提交，对数据的改变就是永久性的，其他操作和数据库故障不能有任何影响

2. 创建事务

隐式事务：事务没有明显的开启和结束的标记。比如：insert、update、delete 语句
显式事务：具有明显的开启和结束标记。
- 前提：设置自动提交功能为禁用

set autocommit=0; # 关闭自动提交功能

# 步骤一：开启事务
set autocommit=0;
start transaction; # 可选的，写了上面的 set autocommit=0 就已经算是开启事务了
# 步骤二：编写事务中的sql语句 (select insert update delete)
#......
# 步骤三：结束事务
commit; # 提交事务
rollback; # 或者执行 回滚事务

3. 隔离级别

分类 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | | :—-: | :—-: | :—-: | :—-: | | read uncommitted | ✔ | ✔ | ✔ | | read committed | ✘ | ✔ | ✔ | | repeatable read | ✘ | ✘ | ✔ | | serializable | ✘ | ✘ | ✘ |
默认事务隔离:
- mysql 默认的事务隔离： REPEATABLE READ (可重复读，确保事务读取一个字段时，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复读，但幻读的问题仍然存在)
- orcal 默认的事务隔离： read commited
查看事务隔离设置
- mysql5.3 使用 mysql> select @@tx_isolation;
- mysql8 使用 mysql> select @@global.transaction_isolation,@@transaction_isolation;
设置隔离级别：
- set session|global transaction isolation level隔离级别;

4. 保存节点

savepoint 节点名; 设置保存点

set autocommit=0;
start transaction;
delete from 表名 where 筛选条件;
SAVEPOINT a; # 设置保存点
delete from 表名 where 筛选条件;
rollback to a; # 回滚到保存点

5. delete和truncate在事务使用时的区别

delete支持回滚撤销，而truncate不支持回滚撤销

SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

八、视图

虚拟表，可以和普通表一样使用。5.1版本新特性，通过表动态生成的数据。

SELECT stuname, majorname
FROM stuinfo s
JOIN major m ON s.`majorid`=m.`id`
WHERE s.`stuname` LIKE '张%';
# 创建视图
CREATE VIEW v1
AS
SELECT stuname, majorname
FROM stuinfo s
JOIN major m ON s.`majorid`=m.`id`;
# 使用视图
SELECT * FROM v1 WHERE stuname LIKE '张%';

场景
- 同样的查询结果
- sql语句较复杂

1. 创建视图

1.1 语法
```
CREATE VIEW 视图名
AS
查询语句
```
1.2 演示
- 1.2.1 查询姓名中包含a字符的员工名、部门名和工种信息 ```sql
  创建视图
  CREATE VIEW v1 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 v1 WHERE last_name LIKE ‘%a%’; 1.2.2 查询各部门的平均工资级别sql

创建视图: 查看每个部门的平均工资
CREATE VIEW v2 AS SELECT department_id, AVG(salary) avgSalary FROM employees e GROUP BY department_id;

使用视图

SELECT v2.avg, g.grade_level FROM v2 JOIN job_grades g ON v2.avg BETWEEN g.lowest_sal AND g.highest_sal;


   - 1.2.3 查询平均工资最低的部门信息
```sql
SELECT MIN(avg) FROM v2;

1.3 优点
- 1.3.1 重用sql语句
- 1.3.2 简化复杂sql操作，不需要知道查询细节，封装
- 1.3.3 保护数据，提高安全性

2. 修改视图

2.1 方式一

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

2.2 方式二
```
ALTER VIEW 视图名
AS
查询语句;
```
3. 删除视图
DROP VIEW 视图名, 视图名1, ...; # 可以一次性删除多个视图

4. 查看视图

DESC 视图名;
SHOW CREATE VIEW 视图;

5. 练习

5.1 练习一：创建视图 emp_v2 ，要求查询部门的最高工资高于12000的部门信息 ```sql CREATE OR REPLACE VIEW emp_v2 AS SELECT MAX(salary) mx_dep, department_id FROM employees GROUP BY department_id HAVING MAX(salary)>12000;

SELECT d.*, m.mx_dep FROM departments d JOIN emp_v2 m ON m.department_id=d.department_id;


<a name="0SMNF"></a>
### 6. 视图里数据的更新
- 插入 _**`INSERT INTO 视图名 VALUES(新增值);`**_
- 修改 _**`UPDATE 视图名 SET 修改字段 WHERE 筛选条件;`**_
- 删除 _**`DELETE FROM 视图名 WHERE 筛选条件;`**_
对视图中数据的更新会影响视图和原始表的数据，所以为了安全起见，一般设定视图的权限仅可读不可写。
- 不允许更新的视图：
   - 分组函数、distinct、group by、having、union、union all、 常量视图
   - select 包含子查询
   - join
   - from 一个不能更新的视图
   - where 子句的子查询引用了 from 子句中的表
<a name="aVERl"></a>
### 7. 视图和表对比
视图不占用实际的物理空间，只保存了sql逻辑。
<a name="hTkMT"></a>
## 九、变量
<a name="fW7KQ"></a>
### 1. 系统变量
- 系统提供，服务器层面
- 全局级别加 `GLOBAL`，会话级别加 `SESSION`，不写默认 `SESSION`
- 语法
   1. 查看所有的系统变量 **`SHOW GLOBAL | [SESSION] VARIABLES;`**
   1. 查看满足条件的部分系统变量 **`SHOW GLOBAL | [SESSION] VARIABLES LIKE '%char%';`**
   1. 查看指定的某个系统变量的值 **`SELECT @@global | [session].系统变量名;`**
   1. 为某个系统变量赋值
      1. **`set global | [session] 系统变量名 = 值;`**
      1. **`set @@global | [session].系统变量名 = 值;`**
<a name="r3O1s"></a>
#### 1.1 全局变量
服务器在每次启动时为所有的全局变量赋初始值，针对所有会话（连接）有效，但不能跨重启
```sql
SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE '%char%';
SELECT @@global.autocommit;
SELECT @@transaction_isolation;
SET @@global.autocommit=0;
SELECT @@global.autocommit;

1.2 会话变量

仅仅针对当前会话（连接）有效

SHOW SESSION VARIABLES;
SHOW VARIABLES;
SHOW VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';
SELECT @@transaction_isolation;
SELECT @@session.transaction_isolation;
# way 1:
SET @@session.transaction_isolation='read-uncommitted';
# way 2:
SET SESSION transaction_isolation='read-committed';

2. 自定义变量

用户自定义变量，声明、赋值、使用（查看、比较、运算）

变量类型	作用域	定义和使用的位置	语法
用户变量	当前会话	会话中任何地方	必须加@符号，不限定类型
局部变量	BEGIN END 中	只能在BEGIN END中，且为第一句话	除SELECT外不用加@符号，需要限定类型

2.1 用户变量

针对于当前会话（连接）有效，同于会话变量的作用域

声明并初始化（操作符 = 或 :=）

SET @用户变量名 = 值；
SET @用户变量名 := 值;
SELECT @用户变量名 := 值;

赋值（更新用户自定义变量）

2.1 方式一（set / select）

SET @用户变量名 = 值；
SET @用户变量名 := 值;
SELECT @用户变量名 := 值;

2.2 方式二（select into）
```
SELECT 字段 INTO 变量名 FROM 表;
```

1. 使用（查看）
```
SELECT @用户变量名;
```
1. 演示 ```sql SELECT @count := 20; select @count 变量;

SELECT COUNT(*) INTO @count FROM stuinfo;

SELECT @count 变量count;

<a name="Id9DR"></a>
#### 2.2 局部变量
仅仅在 begin end 中有效<br />**应用于 begin end 中的第一句话**
- 1. 声明
```sql
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFALULT 值;

赋值

2.1 方式一（set / select）

SET 用户变量名 = 值；
SET 用户变量名 := 值;
SELECT @用户变量名 := 值;

2.2 方式二（select into）
```
SELECT 字段 INTO 变量名 FROM 表;
```

使用

SELECT 局部变量名;

练习：

SET @m:=1;
SET @n:=2;
SET @sum:= @m + @n;
SELECT @sum;

BEGIN
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

十、存储过程和函数

类似于编程语言中的方法

作用
- 提高代码重用性
- 简化操作
- 减少编译次数并减少和数据库服务器的连接次数，提高效率
  1. 存储过程
  1.1. 创建
```
CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
存储过程体（一组合法的SQL语句）
END
```
注意：
1. 参数列表包含三部分
  1. 参数模式
    1. IN 该参数作为输入，需要调用方传入值
    2. OUT 作为输出，可作为返回值
    3. INOUT 既可以输入也可以输出，既需要传入值，又可以返回值
  2. 参数名
  3. 参数类型
  4. 举例 IN stuname VARCHAR(20)
2. 如果存储过程体仅仅只有一句话，BEGIN 和 END 可以省略，存储过程体每条SQL语句的结尾都要求必须加分号，存储过程可以使用 DELIMITER 重新设置。

语法：

DELIMITER 结束标记
# 实际演示案例
DELIMITER $ 告诉mysql，sql语句输入已经结束了，可以开始执行了

1.2. 调用

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

1.2.1 空参列表

案例：插入到admin表中的5条记录

CREATE PROCEDURE myp1()
begin
  insert into admin(username, `password`) 
  values 
      ('john1', '0000'),
      ('john2', '0000'),
      ('john3', '0000'),
      ('john4', '0000'),
      ('john5', '0000');
end

1.2.2 创建带 in 模式参数的存储过程

创建存储过程实现根据女神名，查询对应的男神信息 ```sql delimiter $

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(‘柳岩’)$


- 创建存储过程实现，用户是否登录成功
```sql
delimiter $
create procedure myp3(
    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('张飞', '666666')$

1.2.3 创建带out模式的存储过程

案例1：根据女神名返回对应的男神名 ```sql delimiter $

CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20)) BEGIN SELECT bo.boyName INTO boyName FROM boys bo JOIN beauty b ON bo.id=b.boyfriend_id WHERE b.name=beautyName; END$

CALL myp5(‘小昭’, @bName)$ SELECT @bName$


- 案例2：根据女神名，返回对应的男神名和男神魅力值
```sql
delimiter $
CREATE PROCEDURE myp6(
    IN beautyName VARCHAR(20), 
    OUT boyName VARCHAR(20),
    OUT userCP INT
)
BEGIN
    SELECT bo.boyName, bo.userCP
        INTO boyName, userCP 
    FROM boys bo
    JOIN beauty b
        ON bo.id=b.boyfriend_id
    WHERE b.name=beautyName;
END$
CALL myp5('小昭', @bName, @usercp)$
SELECT @bName, @usercp$

1.2.4 创建带 inout 模式参数的存储过程

案例1：传入a和b两个值，最终a和b都翻倍并返回 ```sql delimiter $

CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT) BEGIN SET a=a2; SET b=b2; END $

SET @m=10; SET @n=20; CALL myp8(@m, @n)$ select @m, @n;

<a name="gq8Pn"></a>
##### 1.2.5 练习
1. 练习一：创建存储过程实现传入用户名和密码，插入到admin表中
```sql
delimiter $
CREATE PROCEDURE test_pro1(IN username VARCHAR(20), IN password VARCHAR(20))
BEGIN
    INSERT INTO admin(admin.username, admin.password) 
  VALUES(username, password);
END $
CALL test_pro1('测试者001', '123456')$

练习二：创建存储过程或函数实现传入女神编号，返回女神名称和女神电话 ```sql delimiter $

CREATE PROCEDURE test_pro2(IN id INT, OUT name VARCHAR(20), OUT phone VARCHAR(11)) BEGIN SELECT beauty.name, beauty.phone INTO name, phone FROM beautyd WHERE beauty.id=id; END $

SET @womanId=3$ CALL test_pro2(@womanId, @womanName, @womanPhone)$ SELECT @womanName, @womanPhone$


3. 练习三：创建存储过程或函数实现传入两个女神生日，返回大小
```sql
delimiter $
CREATE PROCEDURE test_pro3(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT)
BEGIN
    SELECT DATEDIFF(birth1, birth2) INTO result;
END $
CALL test_pro3('1998-12-1', now(), @result)$
SELECT @result$

练习四：创建存储过程或函数实现传入一个日期，格式化为xx年xx月xx日并返回 ```sql delimiter $

CREATE PROCEDURE test_pro4(IN date1 DATETIME, OUT dateStr VARCHAR(50)) BEGIN SELECT DATE_FORMAT(date1, ‘%Y年%m月%d日’) INTO dateStr; END $

CALL test_pro4(NOW(), @dateStr)$ SELECT @dateStr$


5. 练习五：创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串，如传入：小昭，返回 小昭 AND 张无忌
```sql
delimiter $
CREATE PROCEDURE test_pro5(IN woman
VARCHAR(20), OUT result VARCHAR(50))
BEGIN
    SELECT CONCAT(bea.name, ' AND ', IFNULL(boys.boyName, 'null'))  # 为了避免出现没有男神名的尴尬，这里IFNULL判断一下
    INTO result
    FROM beauty bea
    RIGHT JOIN boys
        ON bea.boyfriend_id=boys.id
    WHERE bea.name=woman;
END $
CALL test_pro5('小昭', @str)$
SELECT @str$

练习六：创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录 ```sql delimiter $

CREATE PROCEDURE test_pro6(IN current INT, IN size INT) BEGIN
SELECT *
FROM beauty LIMIT current,size; END $

CALL test_pro6(2,3)$


<a name="dVnBG"></a>
#### 1.3. 删除
_**`DROP PROCEDURE 存储过程名;`**_
> 注意：不支持删除多个，每个drop只能删除一个存储过程
<a name="v8hxP"></a>
#### 1.4. 查看存储过程信息
_**`SHOW CREATE PROCEDURE 存储过程名;`**_<br />_
<a name="TtWT8"></a>
### 2. 函数
- 存储过程和函数区别：
   - 存储过程：可以有 _**>=0**_ 个返回，批量插入、批量更新
   - 函数：必须有且只有 **1** 个返回，适合处理数据后返回一个值
<a name="adBF7"></a>
#### 2.1 创建语法
```sql
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END

参数列表
- 参数名
- 参数类型
函数体
- 必须有 return 值;
当函数体仅有一句话，就可以省略 begin end
使用 delimiter 语句设置结束标记
函数类型
- 报错解决方案：
  - 第一步，用root用户登录：mysql -u root -p
  - 第二步，设置参数log_bin_trust_function_creators为1：set global log_bin_trust_function_creators = 1;
1. DETERMINISTIC不确定的
2. NO SQL 没有SQl语句，当然也不会修改数据
3. READS SQL DATA 只是读取数据，当然也不会修改数据
4. MODIFIES SQL DATA 要修改数据
5. CONTAINS SQL 包含了SQL语句

2.2 调用语法

SELECT 函数名(参数列表); # 会显示返回值

2.3 练习

2.3.1 无参有返回

案例：返回公司的员工个数 ```sql delimiter $

CREATE FUNCTION myf1() RETURNS INT READS SQL DATA BEGIN DECLARE c INT DEFAULT 0; # 定义变量 SELECT COUNT(*) INTO c # 赋值 FROM employees; RETURN c; END $

SELECT myf1()$

<a name="IBvUk"></a>
##### 2.3.2 有参有返回
- 案例：根据员工名，返回工资
```sql
delimiter $
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') $

案例2：根据部门名，返回该部门平均工资 ```sql delimiter $

CREATE FUNCTION myf3 (deptName VARCHAR(20)) RETURNS DOUBLE BEGIN DECLARE sal1 DOUBLE; SELECT AVG(salary) INTO sal1 FROM employees e JOIN departments d ON e.department_id=d.department_id WHERE d.department_name=deptName; RETURN sal1; END $

SELECT myf3(‘IT’)$


- 传入两个float，返回二者之和
```sql
delimiter $
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)$

2.4 查看函数

SHOW CREATE FUNCTION 函数名;

2.5 删除函数

DROP FUNCTION 函数名;

十一、流程控制结构

1. 分支结构

1.1 if函数

IF (表达式1, 表达式2, 表达式3)
表达式1成立返回表达式2的值，否则返回表达式3的值

1.2 case结构

情况1：等值判断

CASE 变量 | 表达式 | 字段
WHEN 要判断的值 THEN 返回的值1或语句1;
WHEN 要判断的值 THEN 返回的值2或语句2;
......
ELSE 要判断的值 THEN 返回的值n或语句n;
END [CASE]; # 返回语句的时候需要添加case

情况2：区间判断

CASE 
WHEN 要判断的条件1 THEN 返回的值1或语句1;
WHEN 要判断的条件2 THEN 返回的值2或语句2;
......
ELSE 要判断的条件n THEN 返回的值1或语句n;
END [CASE]; # 返回语句的时候需要添加case

特点
- 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方
- 可以作为独立语句使用，只能放在 BEGIN END 中
- 如果WHEN中的值或条件成立，就执行对应THEN后面的语句，并结束CASE；都不满足就执行ELSE中的语句或值
- ELSE可以省略，如果ELSE省略了，并且所有WHEN条件都不满足，则返回NULL
案例
1. 创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100，显示A；80-90，显示B；60-80，显示C；否则显示D。 ```sql delimiter $

CREATE PROCEDURE test_case(IN score INT) BEGIN CASE WHEN score >= 90 AND score <= 100 THEN SELECT ‘A’; WHEN score >= 80 THEN SELECT ‘B’; WHEN score >= 60 THEN SELECT ‘C’; ELSE SELECT ‘D’; END CASE; END $

CALL test_case(95)$

<a name="TokZc"></a>
#### 1.3 if结构
实现多重分支
```sql
if 条件1 then 语句1;
elseif 条件2 then 语句2;
......
[else 语句n;]
end if;

只能应用在 begin end 中

创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100，显示A；80-90，显示B；60-80，显示C；否则显示D。 ```sql delimiter $

CREATE FUNCTION test_if(score INT) RETURNS CHAR BEGIN IF score >= 90 AND score <= 100 THEN RETURN ‘A’; ELSEIF score >= 80 THEN RETURN ‘B’; ELSEIF score >= 60 THEN RETURN ‘C’; ELSE RETURN ‘D’; END IF; END $

SELECT test_if(86)$


<a name="1DrfT"></a>
### 2. 循环结构
- 分类： while、loop、repeat
- 循环控制
   - _**`iterate`**_ 类似于continue
   - _**`leave`**_ 类似于break
<a name="u8Cir"></a>
#### 2.1 while
```sql
[标签:] WHILE 循环条件 DO 
    循环体;
END WHILE [标签]; # 如果头部加了标签的话，这里也得加上标签

2.2 loop

[标签:] LOOP
    循环体
END LOOP [标签]; # 可以用来描述简单的死循环

2.3 repeat

[标签:] REPEAT
    循环体
UNTIL 结束循环的条件
END REPEAT [标签];

批量插入，根据次数插入到admin表中多条记录 ```sql delimiter $

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)$


- 批量插入，根据次数插入到admin表中多条记录，如果次数>20就停止
```sql
delimiter $
CREATE PROCEDURE pro_while2(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
  a:WHILE i <= insertCount DO
      INSERT INTO admin(username, `password`) VALUES (CONCAT('Rose', i), '666');
    IF i>=20 THEN LEAVE a;
    END IF;
    SET i = i + 1;
    END WHILE a;
END $
CALL pro_while2(100)$

批量插入，根据次数插入到admin表中多条记录，只插入偶数的 ```sql delimiter $

CREATE PROCEDURE pro_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(‘Rose’, i), ‘666’);
END WHILE a; END $

CALL pro_while3(100)$


- 向数据库中插入随机字符串
```sql
DROP TABLE IF EXISTS stringcontent;
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;
  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 - startIndex + 1) + 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 $

快捷键

Navicat ctrl + R 运行

Mysql数据库学习笔记

学习资源

学习目录

学习笔记

一、DB、DBMS、SQL的理解

DB

DBMS

SQL

二、数据库安装

三、 常用命令

注释

四、查询

1. 基础查询

2. 条件查询

3. 排序查询

4. 常见函数

4.1 分类

4.1.1 单行函数

4.1.2 分组函数，统计使用（聚合函数，统计函数，组函数）

5. 分组查询

6. 连接查询

7. sql99 语法

8. 子查询

①查询各部门的平均工资

②连接①结果集和employees表，进行筛选

1 部门location_id为1700的部门