一、My SQL基础介绍
数据库作用：可以持久化数据库至本地；实现结构化查询。
数据库常见概念：DB——数据库；DBMS——数据库管理系统；SQL——结构化查询语言。
数据库存储特点：数据库存储至表中，表再存放在数据库；一个表含多个表，每个表具有唯一的标识；表中含一个或多个列，列又称为字段。
常见的数据库管理系统：MySQL,Oracle,SQLsever
MySQL优点：开源，成本低，体积小，移植性好，属于C/S软件。
学习目录
- 一、My SQL基础介绍
- 1.1 My SQL的启动和登录
- 1.2 My SQL常见命令
- 1.3 My SQL语法规范
- 二、DQL部分(Data Query Language)
- 2.1 基础查询
- 2.1.1 查询表中的单个字段
- 2.1.2 查询表中的多个字段
- 2.1.3 查询表中所有字段
- 2.1.4 查询常量值
- 2.1.5 查询表达式
- 2.1.6 查询函数
- 2.1.7 查询结果起别名
- 2.1.8 查询去重
- 2.1.9 +号的作用
- 2.2 条件查询
- 2.2.1 按条件运算符筛选
- 2.2.2 按逻辑表达式筛选
- 2.2.3 模糊查询
- 2.3 排序查询
- 2.4 函数
- 2.4.1 单行函数
- 2.4.2 分组函数
- 2.3 分组查询
- 2.4 连接查询（多表查询）
- 2.4.1 内连接（sql92）
- 2.4.1.1 等值连接
- 2.4.1.2 非等值连接
- 2.4.1.3 自连接
- 2.4.2 连接（sql99)
- 2.4.2.1 内连接
- 2.4.2.2 外连接
- 2.5 子查询
- 2.5.1 where或having后面
- 2.5.2 select后面
- 2.5.3 from后面
- 2.5.4 exists后面
- 2.6 分页查询
- 2.7 Union联合查询
- 三、DML语言（Data Manipulation Language）
- 3.1 插入语句
- 3.2 修改语句
- 3.2.1 修改单表的记录
- 3.2.2 修改多表的记录
- 3.3 删除语句
- 四、DDL语言(Data Definition Language)
- 4.1 库的管理
- 4.2 表的管理
- 4.3 常见数据类型
- 4.4 常见约束
- 4.4.1 创建表时添加约束
- 4.4.2 修改表时添加和删除约束
- 4.4 标识列（自增长列）
- 五、TCL语言（Transaction Control Language)
- 5.1 事务基本概念
- 5.2 事务的创建
- 5.3 事务的隔离级别
- 5.4 delete 和truncate 在事务使用时的区别
- 六、视图
- 6.1 视图的创建
- 6.2 视图的查看
- 6.3 视图的修改
- 6.4 视图的删除
- 6.5 视图的更新
- 七、变量
- 7.1 系统变量
- 7.2 自定义变量
- 八、存储过程和函数
- 8.1 存储过程
- 8.2 创建语法
- 8.3 调用语法
- 8.4 应用
- 8.4.1 空参的存储过程
- 8.4.2 带In模式的存储过程
- 九、函数
- 9.1 创建函数
- 9.2 调用函数
- 9.3 查看函数
- 9.4 删除函数
- 十、流程控制结构
- 10.1 分支结构
- 10.1.1 if函数
- 10.1.2 case结构
- 10.1.3 if结构
- 10.2 循环结构
1.1 My SQL的启动和登录
在以管理员打开的cmd命令下：
1、启动 My SQL：
net start mysql（服务名）2、登录 My SQL：
mysql -h localhost -P 3306 -u root -p3、退出 My SQL：
exit或者 CTRL+C
4、关闭 My SQL：
net stop mysql（服务名）1.2 My SQL常见命令
1、查看所有的数据库：
show databases;2、打开指定的库:
use 库名3、查看当前的库的所有表：
show tables;4、查看其它库的所有表：
show tables from 库名；5、创建表：
creat table 表名(列名 列类型，列名 列类型);6、查看表结构：
desc 表名;7、查看服务器的版本:
- 方式一：登录My SQL服务端
select version();- 方式二：在cmd界面
mysql --version
mysql --V1.3 My SQL语法规范
1、不区分大小写，但建议关键字大写，其它（表名、列名）小写
2、每条命令用分号“;"结尾
3、命令比较长，可进行缩进或者换行（回车）
4、注释：
单行注释：#注释文字 – 注释文字（注意空格）
多行注释：/* 注释文字 */
二、DQL部分(Data Query Language)
2.1 基础查询
语法：
use myemployees; //先指定库名
select
查询列表 //包括：表中的字段、常量、表达式、函数;
from
表名；2.1.1 查询表中的单个字段
select last_name from employees;
2.1.2 查询表中的多个字段
select last_name,salary,email from employees;
2.1.3 查询表中所有字段
select * from employees;
几个查询注意事项：
- 用select语句查询前，先用"use 库名"指定库名；
- 为区分关键字和查询字段，可用``（着重号进行区分）；
- 执行时和格式化时选中相应的语句。
2.1.4 查询常量值
select 100;
select ‘john’;
2.1.5 查询表达式
select 100%98;
2.1.6 查询函数
select version();
2.1.7 查询结果起别名
如查询字段有重名情况，可进行区分。
如别名中含有特殊符号，需要用引号。
1、方式一：使用as
select 查询字段 as 别名 from 表名；
select 100%98 as 结果;
select last_name as 姓,first_name as 名 from employees;2、方式二：使用空格
select last_name 姓,first_name 名 from employees;2.1.8 查询去重
语法：
select
distinct expression[,expression...]
from
tables说明：
- distinct 要放在所有字段的前面；
- 如果去重的字段大于一个，则会进行组合去重，只有多个字段组合起来相同时才会被去重。
举例：
SELECT DISTINCT department_id FROM employees;2.1.9 +号的作用
- 在My SQL中，“+”号只用作运算符；
select 100+90; //两个操作数均为数值型，则做加法运算；
select '123'+10; //如其中一个操作数为字符型，则转换为数值型，然后做加法运算；如果字符型转换失败，则将字符型数值转换为0，继续做加法运算；如其中一方为NULL，则结果为NULL；- My SQL的字符拼接
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;- 应用concat函数，如其中值为null，则拼接后结果为null。需要使用ifnull函数。
SELECT
CONCAT(`first_name`,',',`last_name`,',',`email`,',',`salary`,',',
IFNULL(commission_pct,0)) AS OUT_PUT
FROM
employees;2.2 条件查询
select
查询列表
from
表名
where
筛选条件2.2.1 按条件运算符筛选
（大于>）（小于<）(不等于<>) (大于等于>=) （小于等于<=）（等于 =）
// 查询工资>12000的员工信息
SELECT
*
FROM
employees
WHERE
salary>12000// 查询部门编号不等于100的员工名字和部门编号
SELECT
`first_name`,`last_name`,`department_id`
FROM
employees
WHERE
`department_id`<> 100;2.2.2 按逻辑表达式筛选
&&（and) ||(or) ！(not)
SELECT
*
FROM
employees
WHERE
NOT(department_id>=90 AND department_id <=110) AND salary > 10000;2.2.3 模糊查询
（1）like
和通配符搭配使用：
% 表示包含任意多个字符，也可含0个字符；
_ 表示包含任意单个字符；
\ 为转义字符（或者可ESCAPE指定转义字符）。
// 查找员工姓名中第二个字符为_的员工名
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_\_%'；//查找部门编号以1开头的员工名字和部门
SELECT
last_name,`department_id`
FROM
employees
WHERE
department_id LIKE '1__'// 查找员工姓名中第二个字符为_的员工名，使用自定义转义字符
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_$_%' ESCAPE '$';（2）between and
注意事项：相比and逻辑运算更加简单；包含临界值；临界值不可以颠倒顺序；
SELECT
*
FROM
employees
WHERE
salary BETWEEN 10000 AND 20000;SELECT
last_name,salary
FROM
employees
WHERE
salary NOT BETWEEN 8000 AND 17000
ORDER BY
salary DESC;（3）in
用于判断某字段的值是否是In列表的某一项。
特点：使用In比逻辑运算更加简洁；不支持模糊搜索
// 查询员工工种编号是AD_VP, IT_PROG,AD_PRES中的一个员工名和工种编号
SELECT
last_name
FROM
employees
WHERE
`job_id` IN('AD_VP', 'IT_PROG','AD_PRES');（4）is null/ is not null
// 查询没有奖金的员工名和奖金率
SELECT
last_name,commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL;（5）安全等于 <=>
SELECT
last_name,commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;比较：is null 仅用来判断null值；安全等与<=>既可以判断null值，也可用来判断数值.
SELECT
last_name,department_id,
`salary`*12*(1+IFNULL(commission_pct,0)) AS 'annual salary'
FROM
employees
WHERE
department_id <= 100;2.3 排序查询
语法格式：
select //执行3
查询列表
from //执行1
表
where //执行2
筛选条件
order by //执行4
排序字段 asc/desc //asc-ascend; desc-descend排序字段可包括：单个字段、多个字段、表达式、函数、别名；order by 一般放在查询语句的最后，但limit子句除外
示例：
// 查询部门编号>=90的员工信息，按入职顺序进行排列
SELECT
*
FROM
employees
WHERE
department_id >= 90
ORDER BY
hiredate ASC;// 按表达式排序（年薪）
SELECT
`employee_id`,last_name,
12*salary*(1+IFNULL(commission_pct,0)) AS 'annual salary'
FROM
employees
ORDER BY
'annual salary' DESC;// 按函数排序
SELECT
last_name,
LENGTH(last_name) AS 'namelen'
FROM
employees
ORDER BY
LENGTH(last_name) DESC;// 按照多个字段排序
SELECT
*
FROM
employees
ORDER BY
salary ASC,`employee_id`DESC;2.4 函数
语法：
select
函数名（）
from
表分类：
- 单行函数
- 分组函数：用于统计，又称为统计函数、聚合函数、组函数
2.4.1 单行函数
1、字符函数
length(str) //计算字符串长度
concat(str1,str2,…) // 拼接字符串
upper(str)\lower(str) //大小写
substr(str,pos) /substring(str,pos) //截取str中索引从pos开始的字符串
substr(str,frompos,len)/substring(str,frompos,len) //截取从frompos开始，长度为len的字符串
instr(str,substr) //返回substr在str中首次出现的位置
SELECT TRIM(‘f’ FROM ‘fffffemailfff’) //字符串首尾去掉字符f
lpad(str,len,padstr) //用指定的字符实现左填充至指定长度
rpad(str,len,padstr) //用指定的字符实现右填充至指定长度
replace(str,from_str,to_str) //将str中的from_str替换为to_str
// 将员工姓名按首字母排序，并写出姓名长度
SELECT
last_name,LENGTH(last_name) AS namelen,
SUBSTR(last_name,1,1) AS firlet
FROM
employees
ORDER BY firlet;2、数学函数
round（X）/ round(X,D) // 四舍五入
ceil(X) //向上取整
floor(X) //向下取整
truncate(X,D) //截断 SELECT TRUNCATE(1.52669,2); 返回：1.52
mod(a,b) //取余 a%b a-a/b*b
3、日期函数
now() //返回当前系统日期+时间
curdate() //返回系统日期
curtime() //返回系统当前时间
year() 如：SELECT YEAR(hiredate) FROM employees;
month() 如：SELECT MONTH(hiredate) FROM employees;
day() 如：SELECT DAY(hiredate) FROM employees;
str_to_date(str,format); //将字符串转换为日期格式
date_format(date,format) //将日期转换为字符
format 格式：
格式符 | 功能 |
%Y | 四位数的年份 |
%y | 二位数的年份 |
%m | 月份(01,02,…,12) |
%c | 月份(1,2,…,12) |
%d | 日(01,02,…) |
%H | 小时(24小时制) |
%h | 小时(12小时制) |
%i | 分钟(00,01,…,59) |
%s | 秒(00,01,…,59) |
SELECT
*
FROM
employees
WHERE
hiredate=STR_TO_DATE('04-03 1992','%m-%d %Y');4、其它函数
select version();
select database();
select user();
5、流程控制函数
（1）if else函数
SELECT
last_name,commission_pct,
IF(commission_pct IS NULL,'NO','YES') AS 'attached info'
FROM
employees
ORDER BY 'attached info';(2)case函数
- 使用一：相当于switch case
语法：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
...
else 要显示的值n或语句n;
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 'new salary'
FROM employees;- 使用二：类似于多重if
语法：
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end示例：
SELECT
last_name,salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 'degree'
FROM employees
ORDER BY degree ASC;2.4.2 分组函数
常用函数包括：sum、avg、count、max、min（均会自动忽略null值），可与distinct联用，计算不重复值的函数值；和分组函数一同查询的字段要求是group by后的字段。
示例：
SELECT SUM(12*salary*(1+commission_pct)) FROM employees; //此处加ifnull(commission_pct,0)会报错
SELECT AVG(12*salary*(1+commission_pct)) FROM employees;
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;count函数：
// 统计行数
SELECT COUNT(*) FROM employees; //在MYISAM存储引擎下，count(*)效率高
SELECT COUNT(1) FROM employees; //在INNODB存储引擎下，count(*)和count(1)效率差不多，但比count(字段)效率高// 查询员工表中最大入职时间和最小入职时间的天数
SELECT
DATEDIFF(MAX(`hiredate`),MIN(`hiredate`)) AS difference //datediff函数
FROM
employees;2.3 分组查询
引入：查询每个部门的平均工资
语法一：分组前的筛选查询
select
column,group_function(column) //分组函数和依据分组的列
from
table
where
condition
group by
group_by_expression //可为列(单个字段或者多个字段)、表达式或函数
order by
column;语法二：分组后的筛选查询
select
column,group_function(column) //分组函数和依据分组的列
from
table
group by
group_by_expression //可为列(单个字段或者多个字段)、表达式或函数
having
condition
order by
column;示例：
// 查询员工数大于2的部门
SELECT
COUNT(*),department_id
FROM
employees
GROUP BY
department_id //筛选条件为某一列
HAVING
COUNT(*) > 2;# 查询有奖金且最高工资高于12000的工种
SELECT
MAX(salary),job_id
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
job_id //筛选条件为某一列
HAVING
MAX(salary)>12000;# 按员工姓名的长度分组，查询每一组的员工个数，筛选员工数大于5的分组
SELECT
LENGTH(last_name),COUNT(*)
FROM
employees
GROUP BY
LENGTH(last_name) //按函数分组
HAVING
COUNT(*)>5;// 按照多个字段进行分组
# 查询每个部门每个工种的员工平均工资
SELECT
AVG(salary),department_id,job_id
FROM
employees
GROUP BY
department_id,job_id; //按照多个字段分组2.4 连接查询（多表查询）
笛卡尔乘积现象：表1有m行，表2有n行，结果为m*n行；
发生原因：没有有效的连接条件；
如何避免：添加有效的连接条件。
- 连接条件按照年代分类：
- sql92标准（仅支持内连接）；
- sql99标准（支持所有内连接、支持左外+右外）、交叉连接。
- 连接条件按功能分类：
- 内连接（等值连接、非等值连接、自连接）;
- 外连接（左外连接、右外连接、全外连接）;
- 交叉连接。
2.4.1 内连接（sql92）
2.4.1.1 等值连接
特点：
- 多表等值连接的结果为多表的交集部分；
- n个表连接，需要n-1个连接条件；
- 多张表的顺序没有要求；
- 一般需要为表起别名；
- 连接可以搭配所有查询子句使用。
SELECT
last_name,department_name
FROM
employees,departments
WHERE
employees.`department_id`=departments.`department_id`；# 查询员工名，工种号、工种名
SELECT
last_name,emp.`job_id`,job_title //执行3，进行筛选
FROM
employees AS emp,jobs // 执行1，先为原表起别名
WHERE
emp.`job_id`=jobs.`job_id`; //执行2，进行等值连接# 查询有奖金的员工名及其部门名
SELECT
last_name,department_name,commission_pct
FROM
employees AS emp,departments AS dep
WHERE
emp.`department_id`=dep.`department_id`
AND
commission_pct IS NOT NULL //多表查询，添加筛选条件时，用AND连接
ORDER BY
commission_pct DESC;# 查询每个城市的部门个数
SELECT
COUNT(*),city
FROM
departments AS dep,locations AS loc
WHERE
dep.`location_id`=loc.`location_id`
GROUP BY
city; //分组的依据可以是连接的另一个表的字段# 查询员工名、部门名以及所在城市——多张表连接
SELECT
last_name,department_name,city
FROM
employees AS emp,departments AS dep,locations AS loc
WHERE
emp.`department_id`= dep.`department_id`
AND
dep.`location_id`= loc.`location_id`;2.4.1.2 非等值连接
# 查询员工的工资和工资级别
SELECT
salary,grade_level
FROM
employees AS emp,job_grades AS jg
WHERE
salary BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;2.4.1.3 自连接
# 查询员工名和上级的名称
SELECT
e.employee_id AS em_num,e.last_name AS em_name,m.employee_id AS ma_num,m.last_name AS ma_name
FROM
employees AS e,employees AS m
WHERE
e.`manager_id`=m.`employee_id`;2.4.2 连接（sql99)
语法：
select 查询列表
from 表1 别名
【连接类型】join 表2 别名 //内连接：inner 左外连接：left [outer] 右外连接：right[outer] 全外连接:full[outer] 交叉连接：cross（注：笛卡尔积）
on 连接条件
where 筛选条件
group by 分组条件
having 筛选条件
order by 排序列表2.4.2.1 内连接
1、等值连接
//内连接
# 查询员工名、部门名
SELECT
last_name,department_name
FROM
employees AS emp
INNER JOIN
departments AS dep
ON
emp.`department_id`=dep.`department_id`;# 查询部门个数大于等于3的城市名和部门个数
SELECT
COUNT(*) AS dep_num,city
FROM
departments AS dep
INNER JOIN
locations AS loc
ON
dep.`location_id`=loc.`location_id`
GROUP BY
city
HAVING
dep_num >= 3;//多表连接
# 查询员工名、部门名、工种名，并按部门名降序
SELECT
last_name,department_name,job_title
FROM
employees AS emp
INNER JOIN departments AS dep ON emp.`department_id`=dep.`department_id`
INNER JOIN jobs ON emp.`job_id`=jobs.`job_id`
ORDER BY
department_name DESC;2、非等值连接
# 查询员工的工资级别
SELECT
salary,grade_level
FROM
employees AS emp
INNER JOIN
job_grades AS jg
ON
emp.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;# 查询每个工资级别的员工个数(大于等于2)，并按照工资级别降序
SELECT
COUNT(*) AS g_num,grade_level
FROM
employees AS emp
INNER JOIN
job_grades AS jg
ON
emp.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`
GROUP BY
grade_level
HAVING
g_num >= 2
ORDER BY
grade_level DESC;3、自连接
#查询员工名及其经理名
SELECT
emp.last_name,emp.employee_id,man.last_name,man.employee_id
FROM
employees AS emp
INNER JOIN
employees AS man
ON
emp.`manager_id`= man.`employee_id`;2.4.2.2 外连接
应用场景：用于查询一个表中有，另一个表中没有的记录
特点：
- 外连接的查询结果为主表中的所有记录，如从表有匹配项，则显示匹配结果，如从表中无匹配项，则显示null；
- 左外连接：左边是主表，右外连接：右边是主表；
示例：
# 查询没有员工的部门
# 右外连接
SELECT
department_name,employee_id
FROM
employees AS emp
RIGHT OUTER JOIN
departments AS dep
ON
emp.`department_id`=dep.`department_id`
WHERE
emp.`employee_id` IS NULL;
#左外连接
SELECT
department_name,employee_id
FROM
departments AS dep
LEFT OUTER JOIN
employees AS emp
ON
emp.`department_id`=dep.`department_id`
WHERE
emp.`employee_id` IS NULL;2.5 子查询
1、含义： 出现在其它语句中的select语句，称为子查询或者内查询
2、分类：
（1）按子查询出现的位置分类：
- select后面：仅支持标量子查询
- from后面：支持查询
- where或having后面：支持标量子查询（单行子查询），列子查询（多行子查询），行子查询
- exists后面（相关子查询）：支持查询
（2）按结果集的行列数不同： - 标量子查询（一行一列）
- 列子查询（一列多行）
- 行子查询（主要是一行多列）
- 查询（一般为多行多列）
2.5.1 where或having后面
1、特点：
- 子查询放在小括号内，小括号查询结果即为结果集；
- 子查询一般放在条件的右侧；
- 标量子查询，一般搭配单行操作符使用（如条件运算符<、>等）
- 列子查询，一般搭配多行操作符使用（如IN、any/suome、all)
2、标量子查询
# 查询谁的工资比Abel高
SELECT
salary,last_name
FROM
employees
WHERE
salary > (SELECT salary FROM employees WHERE last_name = 'Abel'); //括号中的查询结果为标量，下同# 查询job_id与141号员工相同，salary比143号员工高的员工
SELECT
employee_id,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);# 查询公司工资最少的员工
SELECT
last_name,job_id,salary
FROM
employees
WHERE
salary = (SELECT MIN(salary) FROM employees);# 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT
department_id,MIN(salary)AS min_s
FROM
employees
WHERE
department_id IS NOT NULL
GROUP BY
department_id
HAVING
min_s > (SELECT MIN(salary) FROM employees WHERE department_id = 50);3、列子查询
多行操作符：
操作符 | 含义 |
IN/NOT IN | 等于列表中的任意一个 |
ANY/SOME | 和子查询返回的某一个值比较 |
ALL | 和子查询返回的所有值比较 |
# 返回location_id是1400或1700的部门所有员工的姓名
// 用内连接方式
SELECT
last_name,department_name,location_id
FROM
employees AS emp
INNER JOIN
departments AS dep
ON
emp.`department_id`=dep.`department_id`
WHERE
location_id IN(1400,1700);
// 用子查询方式
SELECT
last_name
FROM
employees
WHERE
department_id IN (SELECT department_id FROM departments WHERE location_id IN(1400,1700));# 返回比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';# 查询和姓名中含u的员工在相同部门的员工的员工号和姓名
SELECT
employee_id,last_name
FROM
employees
WHERE
department_id IN(SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%');# 查询管理者是K_ing的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE
manager_id=ANY(SELECT employee_id
FROM employees
WHERE last_name ='K_ing');4、行子查询
特点：多个筛选条件具有相似性，可用相同的筛选表达式表示
# 查询员工编号最小且工资最高的员工信息
# 用列查询
SELECT
*
FROM
employees
WHERE
employee_id <= ALL(SELECT employee_id FROM employees)
AND
salary >= ALL(SELECT DISTINCT salary FROM employees);
# 用标量查询
SELECT
*
FROM
employees
WHERE
employee_id = (SELECT MIN(employee_id) FROM employees)
AND
salary = (SELECT MAX(salary) FROM employees);
# 用行查询
SELECT
*
FROM
employees
WHERE
(employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) FROM employees);2.5.2 select后面
# 查询每个部门的员工个数
# 采用GROUP BY的方法
SELECT
COUNT(*),department_id
FROM
employees
GROUP BY
department_id; # 本方法仅能查询出有员工的部门，漏掉了无员工的部门
# 采用子查询的方法，可查询出所有部门名称
SELECT
dep.*,
(SELECT COUNT(*) FROM employees AS emp
WHERE dep.`department_id`=emp.`department_id`) AS em_num
FROM departments AS dep;2.5.3 from后面
示例：
# 查询每个部门的平均工资的工资等级
SELECT
department_id,avs,grade_level
FROM
(SELECT ROUND(AVG(salary),2) AS avs,
department_id FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id) AS avst //将子查询结果作为一张表，必须起别名
INNER JOIN
job_grades AS jg
ON
avs BETWEEN jg.`lowest_sal`
AND jg.`highest_sal`
ORDER BY
grade_level;# 查询各部门中工资比本部门平均工资高的员工号、姓名和工资
SELECT
employee_id,last_name,salary,avs,emp.`department_id`
FROM
employees AS emp
INNER JOIN
(SELECT ROUND(AVG(salary),2)AS avs,department_id FROM employees
WHERE department_id IS NOT NULL GROUP BY department_id) AS avst
ON emp.`department_id`= avst.`department_id`
WHERE salary > avs ;2.5.4 exists后面
语法：
exists(完整的查询语句)
输出结果：1或0示例：
# 查询有员工的部门名
SELECT department_name
FROM departments AS dep
WHERE EXISTS(SELECT * FROM employees AS emp
WHERE dep.`department_id`=emp.`department_id`);2.6 分页查询
应用场景： 需要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表 #执行7
from 表1 #执行1
【join type】join 表2 #执行2
on 连接条件 #执行3
where 筛选条件 #执行4
group by 分组字段 #执行5
having 分组后筛选 #执行6
order by 排序字段 #执行8
limit [offset],size; //offset 要显示起始索引，索引从0开始；size为要显示的索引条数 #执行9# 查询有奖金的员工信息，并且显示工资较高的前10名
SELECT * FROM employees
WHERE
commission_pct IS NOT NULL
ORDER BY
salary DESC
LIMIT 10;# 显示页数为page,每页的条目数为size
select 查询列表
from 表
limit (page-1)*size,size;示例：综合应用
# 查询平均工资最低的部门信息
# 普通方法
SELECT
dep.*
FROM
departments AS dep
WHERE department_id =
(SELECT
department_id
FROM
employees
GROUP BY department_id
HAVING AVG(salary) =
(SELECT
MIN(avs)
FROM
(SELECT
department_id,
AVG(salary) AS avs
FROM
employees
GROUP BY department_id) AS avst)) ;
# 采用分页查询方法
SELECT
*
FROM
departments
WHERE department_id =
(SELECT
department_id
FROM
employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1) ;# 查询平均工资最低的部门信息和该部门的平均工资
SELECT DISTINCT *
FROM departments AS dep
INNER JOIN
(SELECT AVG(salary) AS avs,department_id
FROM employees
GROUP BY department_id) AS avst
ON dep.`department_id`=avst.`department_id`
ORDER BY avs ASC
LIMIT 1;# 查询平均工资高于公司平均工资的部门有哪些？
SELECT
department_id,AVG(salary)
FROM
employees
GROUP BY department_id
HAVING AVG(salary) >
(SELECT
AVG(salary)
FROM
employees );# 查询出公司中所有manager的详细信息
SELECT *
FROM employees
WHERE
employee_id=ANY(
SELECT DISTINCT manager_id FROM employees);# 查询平均工资最高的部门的manager的详细信息
SELECT
*
FROM
employees
WHERE employee_id =
(SELECT
manager_id
FROM
departments
WHERE department_id =
(SELECT
department_id
FROM
employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1)) ;2.7 Union联合查询
**应用：**将多条查询语句的结果合并为一个结果。
语法：
查询语句1
union
查询语句2特点：
- 多条查询语句的字段名一致（查询结果表头一致）；
- union 查询结果会自动去重，可以使用UNION ALL包含重复项。
三、DML语言（Data Manipulation Language）
3.1 插入语句
语法一：
insert into
表名(列名，...)
values(值1,...)注意事项：
- 插入值的类型要与列的类型一致或者兼容；
- 可以为NULL的值可以省略列名；
- 列的顺序可以改变，但值要与输入的列一一对应；
- 可以省略列名，但默认所有列，且顺序与现有表格一致。
语法二：
insert into 表名
set 列名=值，列名=值，...应用：
- 方式一支持插入多行；
insert into employees
values(值序列1),(值序列2);- 方式一支持子查询；
insert into employees(employee_id,last_name,salary)
select 209,'zhang',120000;3.2 修改语句
3.2.1 修改单表的记录
语法：
update 表名
set 列=新值,列=新值,...
where 筛选条件； //筛选更新哪条数据示例：
# 修改beauty表中姓唐的女生名电话为110
UPDATE beauty
SET phone='110'
WHERE NAME LIKE '唐%' ；# 修改boys表中id号为2的名称为张飞，魅力值为10
UPDATE boys
SET boyname='张飞',usercp=10
WHERE id=2;3.2.2 修改多表的记录
语法：
# sql92语法
update 表1 别名，表2 别名
set 列=值,...
where 连接条件
and 筛选条件；
# sql99语法
update 表1 别名
inner|left outer|right outer join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件；示例：
# 修改张无忌的女朋友的手机号为114
UPDATE boys b
INNER JOIN beauty be ON b.'id'=be.'id'
SET be.'phone'='114'
WHERE b.'boysname'='张无忌'3.3 删除语句
方式一：
delete from 表名 //按照整行进行删除
where 筛选条件方式二：
truncate table 表名； //删除整张表应用：
- delete 可以加筛选条件where。
- truncate 效率更高。
- 假如要删除的表含自增长列，如用delete删除后，再插入数据，自增长列从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始。
- truncate删除无返回值，delete删除有返回值。
- truncate删除不能回滚，delete删除可以回滚。
# 删除手机号以9结尾的女生信息
DELETE FROM beauty
WHERE phone LIKE '%9';# 删除张无忌的女朋友的信息
DELETE be //表示删除beauty表
FROM beauty as be
INNER JOIN boys as b ON b.'id'=be.'id'
WHERE be.'boyname'='张无忌';四、DDL语言(Data Definition Language)
4.1 库的管理
包括：库的创建、库的修改、库的删除
CREATE DATABASE IF NOT EXISTS books; //创建books库
ALTER DATABASE books CHARACTER SET utf8mb4; //更改库的字符集
DROP DATABASE IF EXISTS books; //库的删除4.2 表的管理
1、表的创建
语法：
create table 表名（
列名，列的类型[(长度)约束],
列名，列的类型[(长度)约束],
列名，列的类型[(长度)约束],
...)示例：
# 创建book表
CREATE TABLE IF NOT EXISTS book(
id INT,#编号
bName VARCHAR(12),#书名
price DOUBLE,#价格
author_id INT,#作者编号
publishDate DATETIME#出版日期
);
SELECT * FROM book;
DESC book;
# 创建作者表
CREATE TABLE IF NOT EXISTS author(
id INT,
au_name VARCHAR(6),
nation VARCHAR(10)
);
DESC author;2、表的修改
应用：
- 修改列名
- 修改列的类型或约束
- 添加新列
- 删除列
- 修改表名
# 修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
DESC book;
# 修改列的类型
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
DESC book;
# 添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
DESC author;
# 删除列
ALTER TABLE author DROP COLUMN annual;
DESC author;
# 修改表名
ALTER TABLE author RENAME TO b_authors;3、表的删除
# 删除表
DROP TABLE IF EXISTS b_authors;
SHOW TABLES;4、表的复制
# 首先在表中插入数据
INSERT INTO author VALUES
(1,'余秋雨','中国'),
(2,'史铁生','中国'),
(3,'鲁迅','中国');
SELECT * FROM author;
# 表的复制
# 仅仅复制表的结构
CREATE TABLE copy_author1 LIKE author;
SELECT * FROM copy_author1;
# 复制表的结构和数据
CREATE TABLE copy_author2
SELECT * FROM copy_author2;
# 复制部分数据
CREATE TABLE copy_author3
SELECT id,au_name
FROM author
WHERE `au_name`='鲁迅';
SELECT * FROM copy_author3;
# 复制部分字段和数据
CREATE TABLE copy_author4
SELECT id,au_name
FROM author;
SELECT * FROM copy_author4;
# 仅复制部分字段
CREATE TABLE copy_author5
SELECT id,au_name
FROM author
WHERE 0;
SELECT * FROM copy_author5;4.3 常见数据类型
1、分类：
（1）数值型
- 整型： tinyint(1字节),smallint(2字节),mediumint(3字节),int/integer(4字节),bigint(8字节)
# 设置无符号和有符号整型
CREATE TABLE tab_int(
t1 INT # 默认设置为有符号（可插入负值）
t2 INT UNSIGNED #设置无符号型（插入负值会报错）
t3 INT(7) ZEROFILL); #int(7)表示显示最大宽度为7位，使用ZEROFILL关键字时，位数不够会用0进行填充- 小数：
- 定点数：decimal(M,D)/dec(M,D)
- 浮点数：float(M,D)(4字节)、double(M,D)(8字节)
注意：
- M表示整数部位+小数部位长度；D表示小数部位长度；如(5,2)表示数值总长度为5位，小数部分为2位
- M和D均可省略，decimal默认为（M,D）-（10，0）,float 和double会根据数值精度确定
- 定点型相对浮点型精确度更高，如货币运算时可考虑decimal类型。
（2）字符型
- 较短文本：char(M)、varchar(M) //M表示最大的字符数
区别： char表示固定字符长度，varchar表示可变字符长度。char比较耗费存储空间，但效率高；varchar比较节省空间，相对效率低。
- Enum 类型（枚举型）
# 创建表并插入枚举型数据
CREATE TABLE tab_char(
c1 ENUM('a','b','c'),#c1取值范围为('a','b','c')
c2 ENUM('Q1','Q2','Q3','Q4'));
DESC tab_char;
INSERT INTO tab_char VALUES('a','q1'),('b','q2');
SELECT * FROM tab_char;- set类型（集合型）
# 创建表并插入集合型的数据
CREATE TABLE tab_set(
s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a,b');
INSERT INTO tab_set VALUES('c,d'),('a');
SELECT * FROM tab_set;- 较长文本：text、blob(较长的二进制数据)
（3）日期型
- date(4字节)
- datetime(8字节)
- timestamp(4字节)
- time(3字节)
- year(1字节)
datetime与timestamp区别：
- timestamp支持的时间范围比较小，取值范围1970.01.01.08：00：01-2038年的某个时间；datetime的取值范围：1000-1-1————9999-12-31
- timestamp更能反映实际的日期，会受实际时区的影响。
4.4 常见约束
1、含义：
一种限制，专门用于限制表中的数据，为了保证表中数据的准确和可靠性。
2、约束分类：
- 非空约束（NOT NULL）：非空，用于保证该字段不能为空；
- 默认约束（DEFAULT）；
- 主键约束（PRIMARY KEY)：唯一且非空；
- 唯一约束（UNIQUE）：唯一但可以为空；
- 检查约束（CHECK）：mysql不支持，应用：如年龄、性别；
- 外键约束（FOREIGN KEY）：用于限制两个表的关系，保证该字段的值必须来自于主表的关联列的值。
主键（PRIMARY KEY）和唯一(UNIQUE)的对比
类型唯一性是否允许为空个数是否允许多列组合主键是否至多1个允许,但不推荐唯一键是是可以多个允许，但不推荐
外键的注意事项
- 在从表添加外键约束，用于引用主表中某列的值，如员工表的部门编号、工种编号。
- 主表的关联列必须是一个key(一般是主键或唯一键)。
*插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表。
3、添加约束的时机：
- 创建表时；
- 修改表时。
4、约束的添加分类：
- 列级约束：六大约束语法上都支持，但“外键约束”无效果；
- 表级约束：除“非空约束”、“默认约束”，其它约束都支持。
4.4.1 创建表时添加约束
1、添加列级约束
语法示例： 直接在字段名和类型名后面追加约束类型即可
# 先创建学生信息库
CREATE DATABASE students;
USE students;
# 创建学生信息表
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL,# 非空
gender CHAR(1) CHECK(gender='男' OR '女'),#检查约束,不支持
seat INT UNIQUE,#唯一约束
age INT DEFAULT 18#默认约束
# major_id int foreign key references major(id)#外键，不支持
);
# 创建专业信息表
CREATE TABLE major(
id INT PRIMARY KEY,#主键
major_name VARCHAR(20)
);2、添加表级约束
**语法示例：**在各个字段的最下面，采用"[constraint 约束名] 约束类型(字段名)"
# 添加表级约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuName VARCHAR(20) NOT NULL,#列级别约束
gender CHAR(1),
seat INT,
age INT DEFAULT 18,#列级别约束
major_id INT,
CONSTRAINT pk PRIMARY KEY(id),#主键，此处采用表级别的约束
CONSTRAINT uq UNIQUE(seat),#唯一键，此处采用表级别的约束
CONSTRAINT ck CHECK(gender='男' OR '女'),#检查键，不支持
CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES major(id) #外键
);
DESC stuinfo;
SHOW INDEX FROM stuinfo;3、添加约束的通用写法
语法示例：
# 添加约束的通用写法
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,#主键,列级约束
stuName VARCHAR(20) NOT NULL,#非空键，列级约束
gender CHAR(1),
age INT DEFAULT 18,#默认键，列级约束
seat INT UNIQUE,#唯一键，列级约束
major_id INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES major(id)#外键，表级约束
);
SHOW INDEX FROM stuinfo;4.4.2 修改表时添加和删除约束
语法：
# 添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 约束类型;
# 添加表级约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型（字段名） 外键的引用;应用示例：
# 修改表时添加和删除约束
# 先创建表
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20),
gender CHAR(1),
age INT DEFAULT 18,#默认键，列级约束
seat INT UNIQUE,#唯一键，列级约束
major_id INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES major(id)#外键，表级约束
);
DESC stuinfo;
# 删除约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;#删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT;#删除唯一约束
ALTER TABLE stuinfo DROP INDEX;#删除唯一键
ALTER TABLE stuinfo DROP PRIMARY KEY;#删除主键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;#删除外键
SHOW INDEX FROM stuinfo;
# 添加约束
# 添加非空约束（仅能添加列级约束）
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL;
# 添加唯一约束（包括列级约束和表级约束）
# (1) 添加列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
# (2) 添加表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
# 添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES major(id);4.4 标识列（自增长列）
1、创建表时设置标识列
应用示例：
USE books;
CREATE TABLE tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT,#创建自增长列
NAME VARCHAR(20)
);
INSERT INTO tab_identity VALUES(NULL,'john');
INSERT INTO tab_identity VALUES(NULL,'lucy');
SELECT * FROM tab_identity;
SHOW VARIABLES LIKE '%auto_increment%'; #显示自增长列信息（起始值和步长）
SET auto_increment_increment=3; #设置步长为3注意事项：
- 标识列需要是key列（主键列或唯一列）；
- 一个表仅能有一个标识列；
- 标识列的类型仅能是数值型。
2、修改表时设置标识列
# 修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;3、修改表时删除标识列
# 修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;五、TCL语言（Transaction Control Language)
5.1 事务基本概念
1、事务：
事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个SQL语句是相互依赖的。整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事务开始以前的状态。如果单元中的所有SQL语句执行成功，则事务被顺利执行。
2、事务的ACID属性
（1）原子性（atomicity)：事务操作是不可分割的工作单位；
（2）一致性(consistency)：事务必须使数据库从一个一致性状态变换到另一个一致性状态；
（3）隔离性（isolation):并发执行的各个事务之间不能互相干扰；
（4）持久性(durability):事务一旦提交，改变即为永久性的。
3、事务的分类
（1）隐式事务：事务没有明显的开启和结束标记，如insert、update、delete语句；
（2）显式事务：事务具有明显的开启和结束的标记（前提：必须先设置自动提交功能为禁用）。
5.2 事务的创建
语法：
# 步骤1：开启事务
SET autocommit=0;
START TRANSACTION; #可选
# 步骤2：编写事务中的sql语句（select\insert\update\delete)
#语句1；
#语句2；....
# 步骤3：结束事务
COMMIT;#提交事务
ROLLBACK;#回滚事务应用示例：
# 应用示例:转账--------------------------------------
# 创建表格
USE books;
CREATE TABLE IF NOT EXISTS account(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
INSERT INTO account VALUES(NULL,'July',1000),(NULL,'lily',1000);
SELECT * FROM account;
# 开启事务
SET autocommit=0;
START TRANSACTION;
# 编写一组事务的语句
UPDATE account SET balance =500 WHERE username='July';
UPDATE account SET balance =1500 WHERE username='lily';
# 结束事务
COMMIT;
SELECT * FROM account;5.3 事务的隔离级别
1、多个事务并发运行可能产生的问题
（1）脏读：对于两个事务T1和T2，T1读取了已经被T2更新但尚未提交(COMMIT)的字段之后，如果T2回滚(ROLLBACK)，T2读取的内容就是临时且无效的。
（2）不可重复读：对于两个事务T1和T2，T1读取了一个字段，然后T2提交(COMMIT)更新了该字段后，T1再次读取同一个字段，两次读取结果不同。
（3）幻读：对于两个事务T1和T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行之后，如果T1再读取同一个表，则会多出几行。
2、隔离级别
# 查询当前隔离级别
SELECT @@tx_isolation;
# 设置当前隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;隔离级别 | 脏读 | 可重复读 | 幻读 |
READ UNCOMMITTED | 是 | 是 | 是 |
READ COMMITTED | 否 | 是 | 是 |
REPEATABLE READ | 否 | 否 | 是 |
SERIALIZABLE | 否 | 否 | 否 |
注：
- My SQL默认为REPEATABLE READ；Oracle默认为READ COMMITTED。
3、回滚点
# savepoint的使用
SET autocommit=0;
START TRANSACTION; #开启事务
DELETE FROM account WHERE id=2;
SAVEPOINT a; #设置保存点
DELETE FROM account WHERE id=3;
ROLLBACK TO a;#回滚到保存点5.4 delete 和truncate 在事务使用时的区别
# delete 和truncate在事务使用中的区别
# 使用delete删除
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK; #删除结果可以回滚，即删除可以撤销
#使用truncate删除
SET autocommit=0;
START TRANSACTION;
TRUNCATE account;
ROLLBACK;#删除结果不能回滚，即删除不能撤销六、视图
6.1 视图的创建
语法：
create view 视图名
as
查询语句；应用示例：
# 1、查询姓名中包含a字符的员工名、部门名和工种信息
USE myemployees;
# (1)先创建视图
CREATE VIEW em_dep_j AS
SELECT last_name,department_name,job_title
FROM employees AS em
INNER JOIN departments AS dep ON em.`department_id`=dep.`department_id`
INNER JOIN jobs ON em.`job_id`=jobs.`job_id`;
# (2)使用视图
SELECT * FROM em_dep_j WHERE last_name LIKE '%a%';
# 2、查询各部门的平均工资的级别
#(1)先创建视图查看每个部门的平均工资
CREATE VIEW avst AS
SELECT AVG(salary) AS avs,department_id
FROM employees
GROUP BY department_id;
# (2)使用连接查询
SELECT avs,grade_level
FROM avst
INNER JOIN job_grades AS jg
ON avs BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;
# 3、查询平均工资最低的部门信息
SELECT *
FROM departments
WHERE department_id=
(SELECT department_id FROM avst ORDER BY avs LIMIT 1);视图优点：
- 实现了sql语句的重用；
- 简化复杂的sql操作；
- 保护数据，提高安全性。
6.2 视图的查看
示例：
DESC em_dep_j;
SHOW CREATE VIEW em_dep_j;6.3 视图的修改
语法：
# 方式一：
CREATE OR REPLACE VIEW 视图名
AS
查询语句;
# 方式二：
ALTER VIEW 视图名
AS
查询语句应用示例：
# 视图修改方式一
CREATE OR REPLACE VIEW avst
AS
SELECT AVG(salary) AS avs,job_id
FROM employees
GROUP BY job_id;
SELECT * FROM avst;
# 视图修改方式二
ALTER VIEW avst
AS
SELECT AVG(salary) AS avs,job_id
FROM employees
GROUP BY job_id;
SELECT * FROM avst;6.4 视图的删除
语法：
DROP VIEW 视图名，视图名,...；6.5 视图的更新
具备以下特点的视图不允许更新：
- 包含以下关键字的SQL语句：分组函数、distinct、group by、having、union、union all；
- 常量视图；
- select 中包含子查询；
- 含连接；
- from后跟一个不能更新的视图；
- where 子句的子查询引用了from 子句中的表。
七、变量
7.1 系统变量
变量由系统提供，属于服务器层面。1、变量分类
- 全局变量：服务器每次启动将为所有的全局变量赋初始值，针对所有会话（连接）均有效，但不能跨重启。
- 会话变量：仅仅针对当前会话（连接）有效。
2、查看系统变量
# （1）查看所有的系统变量
SHOW GLOBAL VARIABLES; #查看全局变量
SHOW SESSION VARIABLES;#查看会话变量
# (2)查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';#查看全局变量
SHOW SESSION VARIABLES LIKE '%char%';#查看会话变量
# (3)查看指定的某个系统变量的值
SELECT @@global.autocommit;#查看全局变量
SELECT @@session.autocommit;#查看会话变量
# （4）为系统变量赋值
# 方式一：
SET GLOBAL autocommit=0;#为系统变量赋值
SET SESSION autocommit=0;#为会话变量赋值
#方式二：
SET @@global.autocommit=0;#为全局变量赋值
SET @@session.autocommit=0;#为会话变量赋值7.2 自定义变量
用户定义的变量。1、用户变量：
针对当前会话（连接）有效，同会话变量的作用域。
语法：
(1)声明并初始化
set @用户变量名=值；#方式一
set @用户变量名:=值；#方式二
select @用户变量名:=值; 方式三
（2）赋值(更新用户变量的值)
方式一：通过set或select
set @用户变量名=值；#方式一
set @用户变量名:=值；#方式二
select @用户变量名:=值; 方式三
方式二：通过select into
select 字段 into @变量名 from 表；
（3）查看变量
select @用户变量名；应用示例：
#自定义变量
SET @name:='john';
SET @count:=0;
USE myemployees;
SELECT COUNT(*) INTO @count FROM employees;
SELECT @name,@count;2、局部变量：
应用在begin end中的第一句话，且仅仅在定义它的begin end中有效。
语法：
(1)声明变量
declare 变量名 类型;
declare 变量名 类型 default 值；
（2）赋值
方式一：通过set或select
set 局部变量名=值；
set 局部变量名:=值；
select @局部变量名:=值；
方式二：通过select into
select 字段 into 局部变量名 from 表；
（3）查看
select 局部变量名；八、存储过程和函数
8.1 存储过程
含义：预先编译好的SQL语句的集合。
作用：
- 提高代码的重用性；
- 简化操作；
- 减少了编译次数并减少了数据库服务器的连接次数，提高了效率。
8.2 创建语法
语法：
create procedure 存储过程名（参数列表）
begin
存储过程体（一组合法的SQL语句）
end注明：
- 参数列表包含三部分：参数模式(in\out\inout)、参数名、参数类型；
- 如果存储过程体只有一句话，begin end可以省略。存储过程体的每条SQL语句的结尾必须加分号；存储过程的结尾可以使用delimeter重新设置。
8.3 调用语法
语法：
call 存储过程名(实参列表);8.4 应用
8.4.1 空参的存储过程
示例：
# 插入到account表中五条记录
USE books;
SELECT * FROM account;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO account VALUES(NULL,'john',1000),(NULL,'lily',2000),(NULL,'jill',1500),(NULL,'july',500),(NULL,'jack',2500);
END $
# 调用
CALL myp1()$
SELECT * FROM account;8.4.2 带In模式的存储过程
示例：
# 创建存储过程实现根据女神名，查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyname VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.name=beautyname;
END $
# 调用存储过程
CALL myp2('lily')$# 创建存储过程实现用户是否登录成功
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 $;九、函数
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1个返回，适合数据处理
9.1 创建函数
语法：
create function 函数名（参数列表：参数名、参数类型）returns 返回类型
begin
函数体
end9.2 调用函数
语法：
select 函数名（参数列表）应用示例：
#返回公司员工的个数
USE myemployees;
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT COUNT(*) INTO c FROM employees;
RETURN c;
END $
SELECT myf1()$ #本函数仅能在命令行界面运行，在SQLYOG页面运行会报错
# 根据部门名，返回该部门的平均工资
CREATE FUNCTION myf4(depaName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE avgs DOUBLE DEFAULT 0;
SELECT AVG(salary) INTO avgs
FROM employees AS emp
INNER JOIN departments AS dep
ON emp.department_id=dep.department_id
WHERE dep.department_name=depaName;
RETURN avgs;
END $
SELECT myf3('IT') $#创建函数，实现输入两个float，返回二者之和
CREATE FUNCTION add_fun(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE add_num FLOAT DEFAULT 0;
SET add_num=num1+num2;
RETURN add_num;
END $
SELECT add_fun(1,2) $9.3 查看函数
语法：
show create function myf3;9.4 删除函数
语法：
drop function myf3；十、流程控制结构
顺序结构：程序从上往下依次执行
分支结构：程序从两条或者多条路径中选择一条执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码
10.1 分支结构
10.1.1 if函数
**功能：**实现简单的双分支
语法：
if(表达式1，表达式2，表达式3)
执行顺序：
表达式1为真，返回表达式2的值，否则返回表达式3的值10.1.2 case结构
情况1： 等值判断
语法：
case 变量|表达式|字段
when 要判断的值 then 返回值1或语句1；
when 要判断的值 then 返回值2或语句2；
...
else 返回值n或语句n;
end case;情况2： 区间判断
语法：
case 变量|表达式|字段
when 要判断的条件 then 返回值1或语句1;
when 要判断的条件 then 返回值2或语句2;
...
else 返回值n或语句n;
end case;特点： 可以作为表达式，在其它语句中使用；也可以在begin end中作为独立语句使用。
10.1.3 if结构
功能： 实现多重分支
语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n；
end if;应用示例：
# 根据传入的成绩，显示等级
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(75) $10.2 循环结构
分类：
while，loop，repeat
循环控制：
iterate，类似于continue，结束本次循环，继续下次循环
leave，类似于break，结束当前循环while语法：
[标签]while 循环条件 do
循环体;
end while[标签]；loop语法： 用来模拟简单的死循环
[标签]loop
循环体；
end loop[标签]；repeat 语法：
[标签]repeat
循环体；
until 结束循环条件
end repeat[标签];应用示例：
# 批量插入数据
CREATE PROCEDURE pro_while(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_while(100) $
# 批量插入输入（次数超过20则停止）
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,'password') VALUES(CONCAT('jack',i),'888')
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL pro_while(100) $
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
