MySQL_基础_笔记

最新推荐文章于 2024-05-09 00:15:00 发布

Z先生日常

最新推荐文章于 2024-05-09 00:15:00 发布

阅读量280

点赞数

分类专栏： MySQL-这才是专业文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43206491/article/details/114044040

版权

MySQL-这才是专业专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

数据库相关概念
CMD DOS命令行及SQL语句
SQL的语法规范
使用Navicat Premium
DQL语句
- 基础查询
- 条件查询（WHERE）
- 排序查询（ORDER BY）
- 常见函数
- 分组查询(GROUP BY)
- 连接查询/多表查询
- 子查询/内查询
- 分页查询（LIMIT）
- 联合查询（UNION）
DML语句
- 插入(INSERT)
- - 经典插入
  - SET插入
- 修改(UPDATE)
- - 单表修改
  - 多表修改
- 删除(DELETE)
DDL语句
- 创建(CREATE)
- - 表的创建
  - 表的复制
- 修改(ALTER)
- - 表的修改
- 删除(DROP)
常见数据类型
- 整型
- 小数
- - 浮点型
  - 定点型
- 字符串
- 日期型
常见约束
- 创建表时添加约束
- 修改表时添加约束
- 修改表时删除约束
- 标识列
TCL语句
- 事务（ACID）
- 隔离级别
- SAVEPOINT的使用
视图
- 创建视图
- 修改视图
- 删除视图
- 查看视图
变量
- 系统变量
- 用户变量
- 局部变量
存储过程
- 创建语法
- 调用语法
- 删除与查看
函数
- 函数的创建
- 函数调用
- 函数使用
- 函数的查看与删除
流程控制
- 分支结构
- 循环结构

数据库相关概念

数据库的好处
- 可以持久化数据到本地
- 结构化查询
数据库的常见概念
- DB:数据库，存储数据的容器
- DBMS:数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB
- SQL：结构化查询语言，用于数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言
数据库存储数据的特点
- 数据存放到表中，然后再放到库中
- 一个库中可以有多张表，每张表具有唯一的表名用来标识自己
- 表中有一个或多个列，列又称为”字段“，相当于Java中”属性“
- 表中的每一行数据，相当于Java中的”对象“
常见的数据库管理系统
- MySQL、Oracle、DB2、SQL Server

CMD DOS命令行及SQL语句

net start mysql :启动服务器

net stop mysql :关闭服务器

mysql -u root -p :运行mysql;默认为mysql -h 127.0.0.1 -p 3306 -u root -p

mysql --version :查看mysql版本

SQL语句（sql语句用;结束）

select version(); :查看mysql版本

show databases; :查看数据库

use 库名; ：打开指定的库

show tables; :查看当前库的所有表

show tables from 库名; :查看其他库的所有表

create table 表名(列名列类型,列名列类型,...); :创建表

desc 表名; :查看表结构

SQL的语法规范

不区分大小写，但建议关键字大写，表面、列名小写
每条命令最好用英文状态下的分号结束
每条命令根据需要。可以进行缩进和换行
注释
- 单行注释：#注释内容或 – 注释内容
- 多行注释：/* 注释内容 */

使用Navicat Premium

第一次连接报连接失败：Client does not support authentication protocol requested by server; consider upgrading MySQL client

解决方法：

使用CDM 进入mysql 使用sql命令修改一次密码：

USE mysql；

ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

FLUSH PRIVILEGES;

重启服务器即可

DQL语句

关于着重号（``）

当特殊情况下字段与关键字重名时，为了区分字段与关键字，可以使用着重号将关键字引起来（在键盘1的左边）

基础查询

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

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

#3.查询表中所有的字段
SELECT * FROM employees;
#3.1为了使数据更加灵活，一般使用自定义字段查询所有信息
SELECT 	employee_id,last_name,first_name,email,phone_number,job_id,salary,commission_pct,manager_id,hiredate FROM employees;

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

#5.查询表达式
SELECT 100%98;

#6.查询函数
SELECT version();

#7.起别名（AS关键字可以省略不写）当别名与关键字重复时可以使用（""）将别名引起来
SELECT version() AS 版本;
SELECT version() "版本";

#8.去重关键字（DISTINCT）;案例：查询employees表中所有的员工部门编号
SELECT DISTINCT department_id FROM employees;

/*
9.+号的使用：
	SELECT 100+100;两个操作数都为数值型，则做加法运算
	SELECT "123"+90;只要其中一方为字符型，试图将字符型值转换为数值型，
					转换成功则继续运算，转换失败则将字符型值置为0
   	SELECT null+100;只要其中一方为null，结果肯定为null
*/
#要想拼接字符值应该使用函数（CONCAT(str1,str2,...)）案例：查询员工名和姓连接成一个字段，并显示为姓名
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

#10.函数嵌套应用;案例：显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
#IFNULL(filed,newresult)函数：当字段内容不为空时，使用结果为原来数据，为空时则使用newresult
SELECT 
	CONCAT(employee_id,',',last_name,',',first_name,',',email,',',IFNULL(commission_pct,0)) AS "OUT_PUT"
FROM
	employees;

条件查询（WHERE）

语法：SELECT 查询列表 FROM 表名 WHERE 筛选条件；

一、按条件表达式筛选：
- 简单条件运算符：> < = != <> >= <=
二、按逻辑表达式筛选：
- 逻辑运算符： && 对应and 、||对应or、！对应 not
三、模糊查询
- like
  - %：表示任意多个字符
  - _：表示任意单个字符
  - \：表示转义符
- between 值 and 值
  - 包含临界值
  - 两个临界值不要调换顺序
- in ：判断某字段的值是否属于IN列表中的某一项
- is null | is not null
  - 使用原因=号不能用于判断NULL值
  - 安全等于：<=> 可以判断NULL值，可读性差

#一、按条件表达式筛选
#案例1：查询工资>12000的员工信息
SELECT * FROM employees WHERE salary > 12000;

#案例2：查询部门编号不等于90号的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id <> 90;

#二、按逻辑表达式筛选
#案例1：查询工资在10000到20000之间的员工名、工资及奖金

SELECT last_name,salary,commission_pct 
FROM employees
WHERE salary >= 10000 AND salary <=20000;

#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT * FROM employees
WHERE department_id < 90 OR department_id > 110 OR salary > 15000;

SELECT * FROM employees
WHERE NOT(department_id >= 90 AND department_id <= 110) OR salary > 15000;

#三、模糊查询
/*
LIKE
BETWEEN AND
IN
IS NULL | IS NOT NULL
*/
#1.LIKE;案例1：查询员工名中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';

#案例2：查询员工中第三个字符为n，第5个字符为l的员工名和工资
SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%';

#默认转义符;案例3：查询员工名中第二个字符为_的员工名
SELECT last_name FROM employees WHERE last_name LIKE '_\_%';

#自定义转义符（ESCAPE）
SELECT last_name FROM employees WHERE last_name LIKE '_@_%' ESCAPE '@';

#2.BETWEEN AND
#案例1：查询员工编号在100到120之间的所有的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120; 

#3.IN
#案例1：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个的员工名和工种编号
SELECT last_name,job_id FROM employees WHERE job_id ='IT_PROG' OR job_id ='AD_VP' OR job_id ='AD_PRES';

SELECT last_name,job_id FROM employees WHERE job_id IN('IT_PROG','AD_VP','AD_PRES');

#4.IS NULL | IS NOT NULL
#案例1：查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;

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

排序查询（ORDER BY）

语法：SELECT 查询列表 FROM 表【WHERE 筛选条件】 order by 排序列表【ASC | DESC】

默认为升序
ASC ：升序
DESC：降序
ORDER BY子句中可以支持单个字段、多个字段、表达式、函数、别名

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

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

#案例3：按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 
FROM employees 
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

#案例4：【按别名排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 
FROM employees 
ORDER BY 年薪 DESC;

#案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(last_name) 名字长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;

#案例6：查询员工信息，要求先按工资升序，再按员工编号降序
SELECT * FROM employees ORDER BY salary,employee_id DESC;

常见函数

语法：SELECT 函数名(实参列表) 【FROM 表】;

类似于Java的方法，将一组逻辑语言封装在方法体中，对外暴露方法名，直接调用即可
分类：
- 单行函数：如（concat、length、ifnull）等
- 分组函数（统计函数、聚合函数、组函数）
MySQL中索引是从1开始的

字符函数

#1.LENGTH(str)
SELECT LENGTH('Kanan');

SELECT LENGTH('我的Wanan');-- UTF-8一个汉字占3个字节

#2.CONCAT(str1,str2,...)：字符串拼接
SELECT CONCAT(last_name,'-',first_name) 姓名 FROM employees;

#3. UPPER(str)、LOWER(str)：大小写转换
SELECT UPPER('Kanan');
SELECT LOWER('Wanan');

#案例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),'-',LOWER(first_name)) 姓名 FROM employees;

#4.SUBSTR(str,pos)：从pos开始截取str
SELECT SUBSTR('买不起你的Kanan',6) 主角;

#SUBSTR(str,pos,len)：从pos开始截取str，截取长度为len
SELECT SUBSTR('买不起你的Kanan',1,3) 形象;

#案例：将姓的第一个字母大写，再和名拼接一起然后都小写
SELECT 
	CONCAT(
		UPPER(SUBSTR(last_name,1,1)),
		LOWER(SUBSTR(last_name,2)),
		LOWER(first_name)
	) 姓名
FROM employees;

#5.INSTR(str,substr)：返回substr第一次出现的索引位置，找不到则返回0
SELECT INSTR('买不起你的Kanan','Kanan') 索引;

#6.TRIM():去掉首位空格
SELECT LENGTH(TRIM('    Kanan   ')) 主角长度;

#TRIM([remstr FROM] str)：去掉str首位的remstr
SELECT TRIM('a' FROM 'aaaaKananaaaaa') 主角;

#7.LPAD(str,len,padstr)：用指定的padstr实现左填充指定的len长度
SELECT LPAD('Wanan',2,'-');

#8.RPAD(str,len,padstr):右填充
SELECT RPAD('Kanan',10,'-');

#9.REPLACE(str,from_str,to_str)：将str中的from_str替换为指定的to_str
SELECT REPLACE('做我的Kanan','Kanan','Wanan');

数学函数

#1.ROUND：四舍五入
SELECT ROUND(1.65);-- 2
SELECT ROUND(-1.65);-- -2
SELECT ROUND(1.567,2);-- 1.57

#2.CEIL：向上取整，返回>=该参数的最小整数
SELECT CEIL(1.002);-- 2

#3.FLOOR(X)；向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.01);

#4.TRUNCATE(X,D)；截断,截取小数点后D位
SELECT TRUNCATE(1.66669,2);

#5.MOD(N,M)：取模
SELECT MOD(10,3);
SELECT 10 % 3;

#6.RAND()：取0-1之间的随机数不包含1

日期函数

#1.NOW()：返回当前系统日期+时间
SELECT NOW();-- 2021-01-24 19:17:43

#2.CURDATE()：返回当前系统日期，不包含时间
SELECT CURDATE();-- 2021-01-24

#3.CURTIME()：返回当前时间，不包含日期
SELECT CURTIME();-- 19:21:51

#4.YEAR()、MONTH()、DAY()...：获取指定date中的年、月、日、时、分、秒
SELECT YEAR(NOW());

#5.STR_TO_DATE(str,format)：将日期格式的字符串转换成指定格式的日期
SELECT STR_TO_DATE('9-12-1504','%d-%m-%Y') 入职日期;

#6.DATE_FORMAT(date,format)：将日期转换成字符串
#案例：查询没有奖金的员工名和入职日期（xx月xx日 xx年）
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

#7.DATEDIFF(expr1,expr2)：返回两个日期相差的天数

其他函数

#VERSION()：查询服务器版本号
SELECT VERSION();

#DATABASE()：查询当前数据库
SELECT DATABASE();

#USER()：当前用户
SELECT USER();

#PASSWORD('字符')：返回该字符的密码形式
SELECT PASSWORD('Kanan');

#MD5('字符')：以MD5的方式加密

IF语句

语法:IF(表达式,expr2,expr3)：表达式结果为ture，返回expr2，false返回expr3

SELECT IF(10>5,'大','小');

SELECT 
	last_name,
	IF(
        commission_pct IS NULL,
        '没有奖金',
        commission_pct
    ) 奖金率
FROM employees;

CASE语句

语法：

CASE 字段或表达式

WHEN 常量1 THEN expr1

WHEN 常量2 THEN expr2

…

ELSE expr3

END

SELECT
	salary 原始工资,department_id,
	CASE department_id
		WHEN 30 THEN salary*1.1
		WHEN 40 THEN salary*1.2
		WHEN 50 THEN salary*1.3
		ELSE salary
	END AS 新工资
FROM	employees
ORDER BY 新工资 DESC;

分组函数

SUM、AVG一般用于处理数组型
MAX、MIN、COUNT可以处理任何类型
分组函数都忽略NULL值
都可以和DISTINCT搭配使用

#1.SUM(expr)：求expr这个字段的和
SELECT SUM(salary) 工资总和 FROM employees;

#2.AVG(expr):求expr这个字段的平均值
SELECT AVG(salary) 平均值 FROM employees;

#3.MIN(expr)、MAX(expr)：求expr这个字段的最小值、最大值
SELECT MIN(salary) 最小值 FROM employees;
SELECT MAX(salary) 最大值 FROM employees;

#4.COUNT(expr)：求expr这个字段的个数
SELECT COUNT(salary) 总个数 FROM employees;

#和DISTINCT搭配使用
SELECT COUNT(DISTINCT department_id) FROM employees;

#COUNT(expr)拓展使用：expr可以为任意常量值,或者 * ，这两种都比字段效率高
SELECT COUNT(1) FROM employees;-- 107
SELECT COUNT(*) FROM employees;-- 107

分组查询(GROUP BY)

简单的分组查询

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

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

#案例3：查询每个部门的总工资
SELECT department_id,SUM(salary)
FROM employees
GROUP BY department_id;

添加筛选条件的分组查询

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

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

引入HAVING

#案例1：查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2;

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

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

按表达式或函数分组

在MySQL中 GRPUP BY、HAVING 后面可以使用别名
注意 WHERE 后面不能使用别名

#案例1：按员工的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*) 员工个数,LENGTH(last_name) len_name
FROM employees
GROUP BY len_name
HAVING 员工个数 >5; 

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

#添加排序
#案例3：查询每个部门每个工种的平均工资，并按平均工资的高低显示
SELECT AVG(salary) 平均工资,department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY 平均工资 DESC;

连接查询/多表查询

笛卡尔积现象：当表1 有m行，表2有n行，结果为m*n行

等值连接

多表连接，至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名来方便使用

#案例1：查询女生名和对应的男生名
SELECT `name`,boyName FROM beauty,boys WHERE beauty.boyfriend_id = boys.id;

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

/*
	当表名过长，而要多次使用表面来修饰指定，那么可以跟表取别名，使用别名后，必须所有要使用表面的位置都使用别名
*/
#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM employees e,jobs j
WHERE e.job_id = j.job_id;

#案例1：查询有奖金的员工名、部门名、和奖金
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%';

多表的分组查询

#案例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)
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;

#案例3：查询每个工种的工种名和员工的个数，并按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.job_id = j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#案例4：查询员工名、部门名和所在的城市
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;

非等值连接

#案例：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
ORDER BY grade_level;

自连接

#案例：查询员工名和上级的名字
SELECT
	e.employee_id,e.last_name,
	m.employee_id 领导ID,m.last_name 领导姓名
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;

SQL99

语法：

SELECT *

FROM 表1 别名【连接类型】

JOIN 表2 别名

ON 连接条件

内连接（INNER）

INNER关键字可以省略不写

跟SQL92相比，将连接条件和筛选条件分离开，使得逻辑更加清晰

等值连接

#案例1：查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

#案例2：查询名字中包含e的员工名和工种名
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE last_name LIKE '%e%';

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

#案例4：查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序
SELECT department_name,COUNT(*)
FROM departments d
INNER JOIN employees e
ON e.department_id = d.department_id
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

#案例5：查询员工名、部门名、工种名，并按部门名降序
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;

非等值连接

#查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

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

自连接

#查询员工的名字、上级的名字
SELECT e.last_name,m.last_name '上级'
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id;

#查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name,m.last_name '上级'
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.last_name LIKE '%k%';

外连接

用于查询一个表中有，而另一个表中没有的记录

左外则左边的是主表，右外右边的是主表

左外（LEFT 【OUTER】）

#查询男朋友不在男神表的女神名
SELECT b.`name`
FROM beauty b
LEFT JOIN boys
ON b.boyfriend_id = boys.id
WHERE boys.id IS NULL;

#案例1：查询哪个部门没有员工
SELECT department_name
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

右外(RIGHT 【OUTER】)

#查询男朋友不在男神表的女神名
SELECT b.`name`
FROM boys
RIGHT JOIN beauty b
ON b.boyfriend_id = boys.id
WHERE boys.id IS NULL;

全外（FULL 【OUTER】）

交叉连接(CROSS)

相当于一个笛卡尔积

SELECT *
FROM beauty
CROSS JOIN boys;

子查询/内查询

子查询应写在小括号中

WHERE后面的子查询

标量子查询

#案例1：谁的工资比Abel高？
SELECT last_name,salary FROM employees
WHERE salary >(
	SELECT salary FROM employees WHERE last_name='Abel'
);

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT CONCAT(first_name,last_name) 姓名,job_id,salary
FROM employees
WHERE job_id IN (SELECT job_id FROM employees WHERE employee_id = 141)
AND salary >(SELECT salary FROM employees WHERE employee_id = 143);

#案例3：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary IN (
	SELECT MIN(salary) FROM employees
);

#案例4：查询最低工资大于50号部门最低工资的部门ID和最低工资
SELECT department_id,MIN(salary) FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary) FROM employees
	WHERE department_id = 50
);

多行子查询
- 涉及到的关键字
- IN/NOT IN
- ANY/ALL

#案例1：返回location_id是1400或1700的部门中的所有员工姓名
SELECT CONCAT(first_name,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
SELECT employee_id,CONCAT(first_name,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';
#ANY关键字不太容易理解可以使用MAX代替
SELECT employee_id,CONCAT(first_name,last_name) 姓名,job_id,salary
FROM employees
WHERE salary < (
	SELECT MAX(salary) FROM employees
	WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';

#案例3：返回其他工种中比job_id为 IT_PROG 部门所有工资低的员工的工号、姓名、job_id、salary
SELECT employee_id,CONCAT(first_name,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';
#由于ALL关键字不太容易理解可以使用MIN代替
SELECT employee_id,CONCAT(first_name,last_name) 姓名,job_id,salary
FROM employees
WHERE salary < (
	SELECT MIN(salary) FROM employees
	WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';

行子查询

#案例1：查询员工编号最小并且工资最高的员工信息
SELECT * FROM employees
WHERE (employee_id,salary)IN(
	SELECT MIN(employee_id),MAX(salary)
    FROM employees
);

SELECT后面的子查询

这种查询仅仅支持标量子查询

#案例1：查询每个部门的员工个数,并按个数降序
SELECT d.*,(
	SELECT COUNT(*) FROM employees e
	WHERE e.department_id = d.department_id
) 个数
FROM departments d
ORDER BY 个数 DESC;

#案例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后面的子查询

将子查询的结果充当一张表，要求必须起别名

#案例：查询每个部门的平均工资的工资等级
SELECT ag_dep.*,g.grade_level
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees e
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

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

能使用EXISTS关键字都可以使用IN代替使用

#案例1：查询没有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT * FROM employees e
	WHERE e.department_id = d.department_id
);

SELECT department_name
FROM departments d
WHERE d.department_id IN(
	SELECT department_id FROM employees
);

#案例2：查询有女朋友的男神信息
SELECT bo.*
FROM boys bo
WHERE EXISTS(
	SELECT * FROM beauty b
	WHERE bo.id = b.boyfriend_id
);

SELECT bo.*
FROM boys bo
WHERE bo.id  IN(
	SELECT boyfriend_id FROM beauty
);

当 B表的数据比 A表多时使用 exists
当 A表的数据比 B表多时使用 in

分页查询（LIMIT）

语法：LIMIT offset，size

特点：当要显示的页数为page，每页的条目数size

公式：LIMIT (page-1)*size,size;

offset要显示条目起始索引（注意此处起始索引从0开始）
size：要显示的条目个数
当offset是从0开始时，offset参数可以省略
LIMIT语句放在查询语句的最后

#案例1：显示前5条员工信息
SELECT * FROM employees
LIMIT 0,5;

#案例2：查询第11条到第25条员工信息
SELECT * FROM employees
LIMIT 10,15;

#案例3：查询有奖金的员工信息，并且显示工资较高的前10名
SELECT * FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

联合查询（UNION）

将多条查询语句的结果合并成一条结果并去掉重复信息，如果不想自动去重则使用 UNION ALL关键字即可

语法：查询语句1 UNION 查询语句2 UNION 。。。

#引入案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees
WHERE department_id > 90 OR email LIKE '%a%';
#使用联合查询后
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id > 90;

DML语句

数据操作语言

插入(INSERT)

插入的值的类型要与列的类型一致
可以为NULL的列可以使用NULL填充，或者不写添加列名
如果要插入所有列，则可以省略不写列名，且顺序和表中的顺序一致

经典插入

语法：INSERT INTO 表名(列名,…) VALUES(列名,…);

支持多行插入
支持子查询

#可以为NULL的列可以使用NULL填充
INSERT INTO beauty(id,`name`,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'Lisa','女','1990-4-23','1350368541',NULL,2);

#可以为NULL的列可以不写添加列名
INSERT INTO beauty(id,`name`,sex,borndate,phone,boyfriend_id)
VALUES(14,'Lisa','女','1990-4-23','1350368541',2);

#多行插入
INSERT INTO beauty(id,`name`,sex,borndate,phone,photo,boyfriend_id)
VALUES(15,'Lisa','女','1990-4-23','1350368541',NULL,2),
VALUES(16,'Lisa','女','1990-4-23','1350368541',NULL,2),
VALUES(17,'Lisa','女','1990-4-23','1350368541',NULL,2);

#如果要插入所有列，则可以省略不写列名，且顺序和表中的顺序一致
INSERT INTO beauty
VALUES(18,'梅梅','女','1990-4-23','1350368541',NULL,2);

#使用子查询
INSERT INTO beauty(id,`name`,sex,borndate,phone)
SELECT 20,'娜美','女','1900-1-1','415451';

SET插入

语法： INSERT INTO 表名 SET 列名=值，列名=值，…

不支持多行插入
不支持子查询

INSERT INTO beauty
SET id=19, `name`='刘涛',phone='9999999';

修改(UPDATE)

单表修改

语法：UPDATE 表名 SET 列=新值，列=新值，…WHERE 筛选条件；

#案例1：修改beauty表中姓热的phone为138888888
UPDATE beauty SET phone = '138888888'
WHERE `name` LIKE '热%';

多表修改

#修改张无忌的女朋友的手机号为114
UPDATE boys b
JOIN beauty be ON b.id = be.boyfriend_id
SET be.phone = 114
WHERE b.boyName = '张无忌';

#修改没有男朋友的女神的男朋友编号都为5
UPDATE boys b
RIGHT JOIN beauty be ON b.id = be.boyfriend_id
SET be.boyfriend_id = 5
WHERE b.id IS NULL;

删除(DELETE)

单表删除

语法：DELETE FROM 表名 WHERE 筛选条件

#案例：删除手机号以9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE '%9';

多表删除

语法：DELETE 表1的别名,表2的别名

FROM 表1 别名

INNER|LEFT|RIGHT| JOIN 表2 别名 ON 连接条件

WHERE 筛选条件

主键使用自增长，当删除数据后再添加时，不会重新增长
有返回值
可以回滚

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

#删除黄晓明的信息以及他女朋友的信息
DELETE b,bo FROM beauty b INNER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE bo.boyName = '黄晓明';

TRUNCATE语句

不能有where条件
主键使用自增长，当删除数据后再添加时，会重新增长
没有返回值
不能回滚

#清空表中数据
TRUNCATE TABLE boys;

DDL语句

数据定义语言主要涉及的是库和表的管理

创建(CREATE)

#案例：创建库books
CREATE DATABASE IF NOT EXISTS books;
-- IF NOT EXISTS 如果没有则创建

表的创建

CREATE TABLE 表名(
列名列的类型【(长度) 列的约束】,
列名列的类型【(长度) 列的约束】,
…
)

#创建book表
CREATE TABLE IF NOT EXISTS book( -- IF NOT EXISTS 没有此表则创建
	id INT, -- 编号
    bName VARCHAR(20), -- 书名
    price DOUBLE, -- 价格
    authorId INT, -- 作者编号
    publishDate DATETIME -- 出版日期
);

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

表的复制

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

#复制表的结构+数据
CREATE TABLE copy SELECT * FROM author;

#只复制部分数据
CREATE TABLE copy SELECT id,au_name FROM author WHERE nation = '中国';

#只复制部分结构
CREATE TABLE copy SELECT id,au_name FROM author
WHERE 1=2; -- 不满足所有条件，即不要数据

修改(ALTER)

库的名称一般不做修改，也没有现成的语句

#更改库的字符集
ALTER DATABASE books CHARACTER SET utf-8;

表的修改

ALTER TABLE 表名 ALL|DROP|MODIFY|CHANGE COLUMN 列名【列类型约束】

#修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;

#修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;

#添加新列，可以指定添加的位置【first|after 列名】 某某列后面
ALTER TABLE author ADD COLUMN annual DOUBLE;

#添加为第一个列名
ALTER TABLE author ADD COLUMN annual DOUBLE FIRST;

#删除列
ALTER TABLE author DROP COLUMN annual;

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

删除(DROP)

#库的删除
DROP DATABASE IF EXISTS book;
-- IF EXISTS 如果有则删除

#表的删除
DROP TABLE IF EXISTS book;

常见数据类型

整型

当插入的值超出了定义的类型范围，报异常且值会变成定义类型的最大范围值
当没有指定长度时，每个类型都有它的默认长度

整数类型	字节	范围
Tinyint	1	有符号：-128_{127；无符号：0}255
Smallint	2	有符号：-32768_{32767；无符号：0}65535
Mediumint	3	有符号：-8388608_{8388607；无符号：0}1677215
Int、integer	4	有符号：-2147483648_{2147483647；无符号：0}4294967295
Bigint	8	有符号：-9223372036854775808_{9223372036854775807；无符号：0}9223372036854775807*2+1

关键字
- UNSIGNED：设置为无符号
- ZEROFILL：左边使用0填充，当使用此关键字时则为无符号

#如何设置无符号和有符号，默认为有符号
DROP TABLE IF EXISTS tabl_int;
CREATE TABLE tab_int(
	t1 INT, -- 有符号
    t2 INT UNSIGNED -- 无符号
);

小数

浮点型

浮点数类型	字节	范围
float	4	±1.75494351E-38~±3.402823466E+38
double	8	±2.2250738585072014E-308~±1.7976931348623157e+308

定点型

定点数类型	字节	范围
DEC(M,D)/DECTIMAL(M,D)	M+2	最大取值范围与double相同，给定decimal的有效取值范围由M和D决定

CREATE TABLE tab_doub(
	doub1 FLOAT(5,2),
    doub2 DEC(5,2)
);

M：整数部位+小数部位
D：小数部位
超过范围，则插入临界值
M和D可以省略不写，如果是DECIMAL，则M默认为10，D默认为0，如果是FLOAT和DOUBLE，则会根据插入的数值的精度来决定

字符串

较短的文本
- char：固定长度字符
- varchar：可变长度字符

字符串类型	最多字符数	描述及储存需求
CHAR(M)	M	M为0~255之间的整数
VARCHAR(M)	M	M为0~

较长的文本
- text
- blob（较大的二进制）
枚举 ENUM

日期型

日期和时间类型	字节	最小值	最大值
date	4	1000-01-01	999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	4	19700101080001	2038年的某一个时刻
time	3	-838:59:59	838:59:59
year	1	1901	2155

常见约束

NOT NULL：非空，用于保证该字段的值不能为空
DEFAULT：默认，用于保证该字段有默认值
PRIMAPY KEY：主键，用于保证该字段具有唯一性，并且不能为空
UNIQUE：唯一，用于保证该字段具有唯一性，可以为空
FPREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
CHECK：检查约束【mysql中不支持】

创建表时添加约束

#列级约束创建方法，除了外键都支持
CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY, -- 主键
    stuName VARCHAR(20) NOT NULL, -- 非空
    gender CHAR(1), CHECK(gender IN('男','女')), -- 检查约束（不支持）
    seat INT UNIQUE, -- 唯一
    age INT DEFAULT 18, -- 默认为18
    majorId INT FOREIGN KEY REFERENCES major(id)-- 外键（不支持）
);

#表级约束创建方法，不支持非空和默认
CREATE TABLE stuinfo(
	id INT,
    stuName VARCHAR(20) NOT NULL,
    gender CHAR(1),
    seat INT,
    age INT DEFAULT 18,
    majorid INT,
    -- CONSTRAINT 名字可以省略不起名字
    CONSTRAINT pk PRIMARY KEY(id), -- 主键
    CONSTRAINT uq UNIQUE(seat), -- 唯一的
    CONSTRAINT ck CHECK(gender IN('男','女')) -- 检查（不支持）
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) -- 外键
);

修改表时添加约束

#添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL;

#添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

#添加主键-列级
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

#添加主键-表级
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

修改表时删除约束

#删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NULL;

#删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;

#删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#删除唯一（指明唯一键名，如果没有起名字默认为列名）
ALTER TABLE stuinfo DROP INDEX seat;

标识列

标识列又称为自增长列即表示可以不用手动的插入值，系统提供默认的序列值

#创建表时设置标识列
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);

#查看步长
SHOW VARIABLES LIKE '%AUTO_INCREMENT%';

#设置步长
SET AUTO_INCREMENT_INCREMENT = 3;

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

#修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;

标识列必须是一个key（主键，唯一，外键等）
一个表最多一个标识列
标识列的类型只能是数值型

TCL语句

事务（ACID）

是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）

事务的四大特性：

原子性：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。
隔离性：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
持续性：也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

#事务使用步骤
SET autocommit=0; -- 开启事务
START TRANSACTION;
-- 编写要执行的事务语句
UPDATE account SET balance = 500 WHERE userId = 1001;
UPDATE account SET balance = 1000 WHERE userId = 1002;
COMMIT; -- 结束事务
#回滚 ROLLBACK;

隔离级别

每启动一个mysql程序，就会获得一个单独的数据库连接，每个数据库连接都有一个全局变量@@tx_isolation，表示当前的事务隔离级别

mysql中默认隔离级别为repeatable read
事务隔离级别是否出现的情况

事务的隔离级别	脏读	不可重复读	幻读
read uncommitted	是	是	是
read committed	否	是	是
repeatable read	否	否	是
seriallizable	否	否	否

#查看当前的隔离级别
SELECT @@tx_isolation;

#设置当前mysql连接的隔离级别
SET transaction isolation level read committed;

#设置数据库系统的全局的隔离级别
SET global trancation isolation level read committed;

SAVEPOINT的使用

设置一个快照（保持点）使用回滚搭配使用

SET AUTOCOMMIT=0;
START TRANSACTION;
DELETE FROM account WHERE ID = 25;
SAVEPOINT a; -- 设置保持点（快照）
DELETE FROM account WHERE ID = 28;
ROLLBACK TO a; -- 回滚到保存点

视图

创建视图

CREAT VIEW 视图名 AS 查询语句；

#查询姓名中包含a字符的员工名、部门名、和工种信息
-- 创建视图
CREATE OR REPLACE VIEW myv1 AS -- OR REPLACE(不存在就创建，存在就修改)
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%';

修改视图

ALTER VIEW 视图名 AS 查询语句；

#方式一：
CREATE OR REPLACE VIEW myv1
AS
SELECT * FROM employees;

#方式二：
ALTER VIEW myv3
AS
SELECT * FROM employees;

删除视图

DROP VIEW 视图名，视图名，…；

DROP VIEW myv1,myv2;

查看视图

DESC myv3;

SHOW CREATE VIEW myv3;

变量

系统变量

变量由系统提供，不是用户定义的，属于服务器层面
如果是全局级别，则需要加global，如果是会话级别则加session，不加默认为session

#查看所有系统变量
SHOW  GLOBAL VARIABLES; -- 查看全局变量
SHOW SESSION VARIABLES; -- 查看会话变量（SESSION可以省略）

#查看满足条件的部分系统变量
SHOW  GLOBAL VARIABLES LIKE '%char%';

#查看指定的某个系统变量的值
-- SELECT @@global|【session】.变量名

#为某个系统变量复制
-- SET global|【session】 系统变量名 = 值;

-- SET @@global|【session】.系统变量名 = 值;

用户变量

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

#声明并初始化，更改用户变量名也可以用同样的语法实现
-- SET @用户变量名 = 值;
-- SET @用户变量名:=值;
-- SELECT @用户变量名:=值;

局部变量

作用域仅仅在定义它的begin end中有效

DECLARE result INT DEFAULT 0;
-- 		变量名  类型     默认值

存储过程

一组预先编译好的SQL语句，理解成批处理语句的方式，提高了代码的重用性，简化操作并且减少了编译次数和数据库服务器的连接次数，提高了效率；

创建语法

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

参数列表包含三部分

参数模式
- in：该参数可以作为输入，也就是说该参数需要调用方传入值
- out ：该参数可以作为输出，也就是说该参数可以作为返回值
- inout：既可以输入又可以作为输出
参数名
参数类型

#参数列表的内容：
in stuname VARCHAR(20)

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

-- DELIMITER 结束标记
#如：
DELIMITER $

调用语法

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

空参列表

#案例：插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`)
	VALUES
		('john1','100'),
		('john2','200'),
		('john3','300'),
		('john4','400'),
		('john5','500');
END $
-- 调用此函数
CALL myp1();

带参列表

#案例2：创建存储过程实现根据女神名，查询对应的男神信息
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('柳岩');

#案例3：
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('张飞','8888');

OUT模式

#根据女神名返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp4(IN `name` VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.`name` = `name`;
END $
CALL myp4('柳岩',@bName);
SELECT @bName;

INOUT模式

DELIMITER $
CREATE PROCEDURE myp5(INOUT n INT,INOUT m INT)
BEGIN
	SET n = n*2;
	SET m = m*2;
END $

SET @n = 10;
SET @m = 20;
CALL myp5(@n,@m);
SELECT @n,@m;

删除与查看

删除存储过程，每次只能删除一个

DROP PROCEDURE 存储过程名;

查看存储过程的信息

SHOW CREATE PROCEDURE 存储过程名;

函数

函数与存储过程含义相同只有一些微量的区别

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

函数的创建

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

参数列表只有2个部分
- 参数名
- 参数类型
函数体一定有return语句，如果没有会报错

函数调用

SELECT 函数名(参数列表);

函数使用

#返回公司的员工个数
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;
	SELECT COUNT(*) INTO c FROM employees;
	RETURN c;
END $
SELECT myf1();

#案例：根据员工名返回它的工资
DELIMITER $
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE DEFAULT 0;
	SELECT salary INTO sal FROM employees
	WHERE employees.last_name = empName;
	RETURN sal;
END $
SELECT myf2('De Haan');

#案例：根据部门名返回该部门的平均工资
DELIMITER $
CREATE FUNCTION myf3(departmentName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal_avg DOUBLE DEFAULT 0;
	SELECT AVG(salary) INTO sal_avg
	FROM employees
	WHERE employees.department_id IN (
		SELECT department_id FROM departments
		WHERE department_name = departmentName
	);
	RETURN sal_avg;
END $

SELECT myf3('IT');

注意创建函数可能会报错如下：

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决方法：

#查看log_bin_trust_function_creators变量，默认为0
SELECT @@global.log_bin_trust_function_creators;
#将变量值改为1即可
SET global log_bin_trust_function_creators=1;

函数的查看与删除

查看函数

SHOW CREATE FUNCTION 函数名;

删除函数

DROP FUNCTION 函数名;

流程控制

分支结构

循环结构

iterate：类似于continue，结束本次循环
leave：类似于break，结束当前循环

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

#loop，可以用来模拟简单的死循环
【标签:】LOOP
	循环体;
END LOOP 【标签】;

#repeat
【标签:】repeat
	循环体;
UNTIL 结束循环条件
END REPEAT 【标签】;

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