引言：
这里只是对一些简单的MySql语法做一些整理，增删改查，建表删表，存储过程等。
一、MySql常见命令
1、登录
mysql [-h主机名 -P端口号] -u用户名 -p密码
2、退出
exit 或者 ctlr+c
3、查看当前所有的数据库
show databases;
4、打开指定的数据库
use 库名;
5、查看当前库的所有表
show tables;
6、查看其它库的所有表
show tables from 库名;
7、创建表
create table 表名(
列明 列类型,
列明 列类型,
……
);
8、查看表的结构
desc 表名;
9、查看MySql服务器版本
select version();（适合于已登录到mysql服务端）mysql --version（适合于没有登录到mysql服务端）mysql --V（适合于没有登录到mysql服务端）
二、MySql的语法规范
1、不区分大小写，但是建议关键字大写，表名、列名小写；
2、每条命令最好用分号结尾；
3、每条命令根据需要，可以进行缩进或换行；
4、注释
单行注释：
#注释文字
单行注释：-- 注释文字
多行注释：/* 注释文字 */
注、本教程中用到的数据库脚本
员工信息数据库，sql脚本下载
男生女神数据库，sql脚本下载
三、DQL 数据查询语言
Data Query Language
A、基础查询
1、语法
select 查询列表
from 表名查询列表可以是：表中的字段、常量、表达式、函数，可以是它们的组合
查询的结果是一个虚拟的表格，并不是真实存在的
2、查询表中单个或多个字段
select last_name from employees;select last_name, salary, email from employees;
3、查询表中的所有字段
select * from employees;这种方式的弊端就是列名的顺序就是原始表中的列名顺序
4、查询常量值、表达式、函数
查询字符型或日期型的常量必须用单引号或双引号引起来，数值型不需要
select 常量值;
select 100;select 'jerry';select "Tom";
select 表达式;
select 100*98;select 100%99;
select 函数(实参列表);
select version();select database();
5、查询时为字段起别名
方式一（使用关键字AS）：
select 100%98 as 结果;select last_name as 姓, first_name as 名 from employees;方式二（直接使用空格）：
select last_name 姓, first_name 名 from employees;起别名好处：
- 便于理解
- 在某些关联查询中如果字段有重名的情况，使用别名可以很好地区分开来
案列：查询salary，显示结果为 out put
select salary as out put from employees;直接报错select salary as 'out put' from employees;成功执行select salary as "out put" from employees;成功执行- 当别名中存在特殊符号，比如空格#号时，要在别名上加上单引号或者双引号
6、查询去重
查询员工表中涉及到的所有部门编号
select distinct department_id from employees;
7、加号 “+” 的使用注意
查询员工的姓和名，并连接成一个字段显示为 姓名
select last_name+first_name as 姓名 from employees;结果全是0，和预想的完全不一样
- java中的 +号 可以作为运算符，也可以作为字符串的连接符，当左右两个操作数都是数值型时其作为运算符，而只要有一个操作数为字符串其就作为连接符。
- mysql中的 +号 只能作为运算符来使用。当左右两边的操作数不能装换为数值时，就默认转换为0
select 100+90;两个操作数都为数值型，则做加法运算select '123'+90;其中一方为字符型，则试图将字符型数值转换成数值型，再做加法运算select '123'+'456';两个操作数都为字符型，则一样试图做转换，再做加法运算select '123'+'abc';若字符型不能转换成数值型，则默认转换成0select 123+null;只要其中一方为null，则结果一定为null
8、使用 CONCAT 实现连接
SELECT CONCAT(字段1, 字段2, 字段3, ......);
案例1：查询员工的姓和名，并连接成一个字段显示为 姓名select concat(last_name, first_name) as 姓名 from employees;
CONCAT还可以连接多个字段，也就是说该函数具有可变数量个参数select CONCAT('a', 'b', 'c', last_name) as 结果 from employees;
特殊案例：null 拼接select CONCAT(null, last_name) as 结果 from employees;结果全为NULL，又和预想的不一样。
- null和任何其他字符拼接，结果都为null
- 可以使用 IFNULL(expr1, expr2) 做一下判断，判断字段是否为null，并返回指定的表达式值。其中expr1表示想要判断的字段，expr2表示如果expr1为null，该函数将返回expr2。如果expr1不为null，则返回expr1本身。
select commission_pct, ifnull(commission_pct, "0.0") as 奖金率 from employees;
9、IFNULL(expr1, expr2)函数
判断某字段或表达式是否为null，如果为null，则返回指定的表达式或字段值；否则返回原本的值
select commission_pct, ifnull(commission_pct,0) as 奖金率 from employees;
10、ISNULL(expr)函数
判断某字段或表达式是否为null，如果为null，则返回1；否则返回0
select commission_pct, isnull(commission_pct) from employees;
B、条件查询
1、语法
select 查询列表
from 表名
where 筛选条件;“筛选条件”分类：
- 按条件表达式筛选
简单条件运算符： “>” “>=” “<” “<=” “=” “!=” “<>” “<=>”- 按逻辑表达式筛选
逻辑运算符(作用就是用来连接条件表达式)： “&&” “||” “!”
逻辑运算符(推荐使用)： “and” “or” “not”- 模糊查询
特殊条件运算符： “like” “between and” “in” “is null” “is not null”
2、按条件表达式筛选
案例1：查询工资>12000的员工信息
select * from employees where salary>12000;案例2：查询部门编号不等于90号的员工名和部门编号
select concat(last_name, first_name) as 姓名
from employees
where department_id<>90;
2、逻辑表达式筛选
案例1：查询工资在10000到20000之间的员工名、工资以及奖金
select first_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 department_id not between 90 and 110 or salary>15000;
select *
from employees
where NOT(department_id>=90 and department_id<=110) or salary>15000
3、模糊查询
3.1 “like”
一般和通配符搭配使用，注意：当查询字段的值为NULL时，不能做出有效的判断
“%” 匹配任意多个字符（包含0个字符）
“_” 匹配任意单个字符
“\” 表示转义字符，当要匹配特殊字符时，比如就要匹配“%”或者“_”时使用案例1：查询员工名中包含字符’a’的员工信息
select *
from employees
where last_name like '%a%';案例2：查询员工名中第三个字符为’n’，第五个字符为’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 '$';案例4：查询员工的薪水中万位数字为’2’的员工名和薪水
select last_name, salary from employees where salary like '%2____.%';
（就是为了说明 like 不仅可以用来模糊字符型数据，甚至可以是数模糊数值型数据）3.2 “between and”
案例1：查询员工编号在100到120之间的员工信息
- select * from employees where department_id>=100 and department_id<=120;
- select * from employees where department_id between 100 and 120;
使用“between and”可以提高语句的简洁度，同时也可以看出“between and”是包括左右端点的，且左右端点值不能交换顺序，写成“between 120 and 100”就啥也查不出来。
3.3 “in”
判断某字段的值是否属于 in 列表中的某一项
案例1：查询员工的工种标号是 IT_PROG、AD_VP、AD_PRES 中的一种的员工编号- select job_id from employees where job_id="IT_PROG" or job_id='AD_VP' or job_id='AD_PRES';
- select job_id from employees where job_id in ('IT_PROG', "AD_VP", 'AD_PRES');
同样使用“in”可以提高语句的简洁度，in列表中的值类型必须一致或兼容，in列表中的值无法使用通配符。
3.4 “is null / is not null”
案例1：查询没有奖金的员工名和奖金率
- select last_name,commission_pct from employees where commission_pct=NULL; 结果和预想的不一样，因为“=”不能用来判断null。
- select last_name,commission_pct from employees where commission_pct is null; 正确执行。
案例2：查询有奖金的员工名和奖金率
- select last_name,commission_pct from employees where commission_pct !=null; 同上，结果和预想的不一样，因为“!=”不能用来判断null。
- select last_name,commission_pct from employees where commission_pct is not null; 正确执行。
注意：
- “=”或“<>”不能用来判断 NULL 值
- “is null”和“is not null”主要就是用来判断 NULL 值的
- is 后面只能接null或则not null，不能接其他的，比如where salary is 12000 是错误的用法
4、安全的等于“<=>”
“<=>”可以用来判断null值，也可以用来判断普通类型的值。但是可读性很差，不常用。
案例1：查询没有奖金的员工名和奖金率
select last_name, commission_pct
from employees
where commission_pct<=>null;案例2：查询工资为12000的员工名和奖金率
select last_name, commission_pct
from employees
where salary <=>12000;
C、排序查询
1、语法
select 查询列表
from 表名
[where 筛选条件]
order by 排序列表 [asc|desc]特点：
- asc代表的是升序，desc代表的是降序，如果不写，则默认是升序
- order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
- order by 字句一般是放在查询语句的最后面，limit字句除外
- 目前的执行顺序为：from –> where –> select –> order
2、案例
案例1：查询员工信息，要求工资从高到低排序（或则从低到高）
select * from employees order by salary DESC;降序select * from employees order by salary ASC;升序（默认）案例2：查询部门编号大于等于90的员工信息，按入职时间的先后进行排序【添加筛选条件】
select * from employees where department_id>=90 order by hiredate ASC;select * from employees where department_id>=90 order bu hiredate DESC;案例3：按年薪的高低显示员工的信息和 年薪【按表达式排序】
- select , salary12*(1+ifnull(commission_pct,0))
from employees
order by salary12(1+ifnull(commission_pct,0)) desc;案例4：按年薪的高低显示员工的信息和 年薪【按表别名排序】
- select , salary12*(1+ifnull(commission_pct,0)) as year_salary
from employees
order by year_salary desc;案例5：按姓名的长度显示员工的姓名和工资【按函数排序】
- select LENGTH(last_name) 字节长度, last_name, salary
from employees
order by 字节长度 ASC;案例6：查询员工信息，要求先按工资排序（降序），再按员工编号排序（升序）【按多个字段排序】
- selecy last_name, salary, employee_id
from employees
order by salary desc, employee_id asc;案例7：查询员工的姓名、部门、年薪，按年薪降序，按姓名升序
- select last_name, department_id, salary12(1+ifnull(commission_pct,0)) year_salary
from employees
order by year_salary DESC, last_name ASC;案例8：选择工资不在8000到17000的员工的姓名和工资，按工资降序
- select last_name, salary
from employees
where not between 8000 and 17000
order by salary desc;案例9：查询邮箱中包含’e’的员工信息，并先按邮箱的字节数降序，再按部门号升序
- select *
from employees
where email like '%e%'
order by LENGTH(email) DESC, department_id ASC;
D、常见函数
MySql中的函数类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。好处：①隐藏了实现细节 ②提高了代码的重用性
1、调用语法
select 函数名(实参列表) 【from 表名】;
分类：
- 单行函数：concat(str1, str2,…)、length(str)、ifnull(expr1, expr2)、isnull(expr)
- 分组函数：主要是做统计使用，又称为统计函数、聚合函数、主函数
单行函数:
- 字符函数
- 数学函数
- 日期函数
- 其他函数
- 流程控制函数
分组函数：
- sum 求和
- avg 平均值
- max 最大值
- min 最小值
- count 计算个数
2、字符函数
2.1、length(str) 获取参数值的字节个数
select length('john');select LENGTH("张三丰hahaha");跟字符集有关，utf8中汉字占三个字节，字母占一个字节2.2、concat(str1, str2, …) 拼接字符串
select concat(last_name, '_', first_name) from employees;2.3、upper(str) / lower(str) 字符大小写转换
select upper('john');select lower('Tom');select concat(upper(last_name), lower(first_name)) from employees;2.4、substr / substring 字符串截取。注意：以下的pos是从 1 开始，并非从 0 开始，也就是索引是从 1 开始的
- substr(str, pos) / substring(str, pos) 从指定索引处pos开始截取，一直到字符串末尾
select substr('李莫愁爱上了陆展元', 7) as out_put;返回结果“陆展元”select substr('李莫愁爱上了陆展元', -3) as out_put;pos可以为负数，表示倒着数- substr(str, pos, len) / substring(str, pos, len) 从指定索引处pos开始截取，截取固定字符长度len
select substr('李莫愁爱上了陆展元', 1, 3) as out_put;返回结果“李莫愁”select substr('李莫愁爱上了陆展元', -9, 3) as out_put;- substr(str FROM pos) / substring(str FROM pos)
select substr('李莫愁爱上了陆展元' FROM 7) as out_put;select substr('李莫愁爱上了陆展元' FROM -3) as out_put;- substr(str FROM pos FOR len) / substring(str FROM pos For len)
select substr('李莫愁爱上了陆展元' FROM 1 FOR 3) as out_put;select substr('李莫愁爱上了陆展元' FROM -9 FOR 3) as out_put;- 案例1：姓名中首字符大写，其他字符小写然后用’_’拼接，显示出来
select CONCAT(upper(substr(last_name,1,1)), '_', lower(substr(last_name,2))) as name from employees;2.5、instr(str, substr) 判断字符串中是否包含指定子串，返回 1 表示包含，0 表示不包含
select instr('杨不悔爱上了殷六侠', '杨不悔') as out_put;select instr('杨不悔爱上了殷六侠', '张无忌') as out_put;2.6、trim、ltrim、rtrim 常用来去掉首尾多余的空格（或指定字符）
- trim([remstr FROM] str) 去掉首尾指定的字符，不指定就默认去掉空格
- select trim("   张翠山  ") as result;
- select trim("a" FROM 'aa张aa翠山aaa') as result;
- select trim("aa" FROM 'aaa张aa翠山aaaa') as result;
- select trim("ab" FROM 'abab张ab翠山abab') as result;
- trim([{BOTH | LEADING | TRAILING} [remstr] FROM] str) 去掉首尾指定的字符，不指定就默认去掉空格，“BOTH”表示去掉首尾，“LEADING”表示只去首，“TRAILING”表示只去尾
- select trim(both from "   张翠山  ") as result;
- select trim(leading "a" FROM 'aa张aa翠山aaa') as result;
- select trim(both "aa" FROM 'aaa张aa翠山aaaa') as result;
- select trim(trailing "ab" FROM 'abab张ab翠山abab') as result;
- ltrim(str) 只能去掉首空格，并不能像上面一样去掉指定字符
- select trim("   张翠山  ") as result;
- rtrim(str) 只能去掉尾空格，并不能像上面一样去掉指定字符
- select trim("   张翠山  ") as result;
2.7、lpad(str, len, padstr) 用指定的字符实现左填充到指定长度，如果本身字符个数就超过了指定长度，那就类似于substr()直接截断
select lpad('殷素素', 10, '*') as result;结果“*******殷素素”select lpad('殷素素', 2, '*') as result;结果“殷素”select lpad('殷素素', 10, 'ab') as result;结果“abababa殷素素”2.8、rpad(str, len, padstr) 用指定的字符实现右填充到指定长度，如果本身字符个数就超过了指定长度，那就类似于substr()直接截断
select rpad('殷素素', 10, '*') as result;结果“殷素素*******”select rpad('殷素素', 2, '*') as result;结果“殷素”select rpad('殷素素', 10, 'ab') as result;结果“殷素素abababa”2.9、replace(str, from_str, to_str) 实现字符替换
select replace("张无忌爱上了周芷若", '周芷若', '赵敏') as result;select replace("张无忌爱上了周芷若、周芷若、周芷若", '周芷若', '赵敏') as result;select replace(012301230123, 1, 5) as result;结果为 “52305230523”select replace(012301230123, 01, 5) as result;结果为 “52305230523”select replace(012301230123, 01, 05) as result;结果为 “52305230523”select replace(012301230123, 1, 0) as result;结果为 “02300230023”
3、数学函数
3.1、round(X) / round(X, D) 四舍五入，参数D表示小数点后保留D位数字，若D为负数，则表示保留小数点前的数字。round(X)与round(X, 0)效果一样
select round(1.45) as result;结果为 1select round("1.45") as result;可以直接传入字符型select round(1.65) as result;结果为 2select round(-1.45) as result;结果为 -1select round(-1.65) as result;结果为 -2select round(1.45, 0) as result;结果为 1select round(1.65, 0) as result;结果为 2select round(1.567, 2) as result;结果为 1.57select round(-1.567, 2) as result;结果为 -1.57select round(123.567, -2) as result;结果为 100select round(-123.567, -2) as result;结果为 -100select round(154.567, -2) as result;结果为 200select round(-154.567, -2) as result;结果为 -200select round(300.567, -3) as result;结果为 0select round(500.567, -3) as result;结果为 1000select round(300.567, -4) as result;结果为 0select round(500.567, -4) as result;结果为 03.2、ceil(X) / ceiling(X) 向上取整，返回大于等于X的最小整数值
select ceil(1.45) as result;结果为 2select ceil(-1.45) as result;结果为 -13.3、floor(X) 向下取整，返回小于等于X的最大整数值
select floor(1.45) as result;结果为 1select floot(-1.45) as result;结果为 -23.4、truncate(X,D) 截断，参数D表示小数点后保留D为数字，若D为负数，则表示保留小数点前的数字。truncate(X, 0)表示取整
select truncate(1.99999, 3) as result;结果为 1.999select truncate(1.99999, 0) as result;结果为 1select truncate(1234.99999, -1) as result;结果为 1230select truncate(1234.99999, -2) as result;结果为 1200select truncate(1234.99999, -3) as result;结果为 1000select truncate(1234.99999, -4) as result;结果为 0select truncate(-1234.99999, -3) as result;结果为 -10003.5、mod(N, M) 取余。表示 N / M 的余数，不管N、M是正数还是负数，都满足“余数 = N - M*商”，因为余数就是这么定义的。其实也可以看被除数，被除数N为正，余数就为正；被除数N为负，余数就为负。
select mod(8, 5) as result;结果为 3select mod(8, -5) as result;结果为 3select mod(-8, 5) as result;结果为 -3select mod(-8, -5) as result;结果为 -3select mod(5, 8) as result;结果为 5
4、日期函数
4.1、now() 返回当前系统日期 + 时间
select now() as result;4.2、curdate() 返回当前系统日期，不包含时间
select curdate() as result;4.3、curtime() 返回当前系统时间，不包含日期
select curtime() as result;4.4、year(date)、month(date)、day(date)、hour(time)、second(time)、minute(time) 获取指定的部分：年、月、日、时、分、秒
select year(now()), year(curdate()), year(curtime());select year("2021-10-11 10:46:27"), year("2021-10-11")select month(now()), month(curdate()), month(curtime());select monthname(now()), monthname(curdate()), monthname(curtime());结果为月份的英文名select month("2021-10-11 10:46:27"), month("2021-10-11")select day(now()), day(curdate()), day(curtime());select day("2021-10-11 10:46:27"), day("2021-10-11");select hour(now()), hour(curtime());select hour("2021-10-11 10:46:27"), hour("10:46:27");select minute(now()), minute(curtime());select minute("2021-10-11 10:46:27"), minute("10:46:27");select second(now()), second(curtime());select second("2021-10-11 10:46:27"), second("10:46:27");- 案例1：查询员工的入职年份，并升序排序
select year(hiredate) from employees order by hiredate;4.5、str_to_date(str, format) 将日期格式的字符转换成指定格式的日期
select str_to_date('2021-10-11 10:46:27', '%Y-%m-%d');结果为“2021-10-11”select str_to_date('2021-10-11 10:46:27', '%Y-%m-%d %H:%i:%s');结果为“2021-10-11 10:46:27”
序号 格式符 功能 1 %Y 4位的年份 2 %y 2位的年份 3 %m 月份（01,02,…,11,12） 4 %c 月份（1,2,…,11,12） 5 %d 日（01,02,…） 6 %H 小时（24小时制） 7 %h 小时（12小时制） 8 %i 分钟（00,01,…,58,59） 9 %s 秒（00,01,…,58,59） - 案例1：查询入职日期为“1992-4-3”的员工信息
SELECT * FROM employees WHERE hiredate='1992-4-3';SELECT * FROM employees WHERE hiredate=str_to_date('4-3 1992', '%c-%d %Y');4.6、data_format(date, format) 将日期转换成指定格式的字符
select date_format(now(), '%Y年%m月%d日 %H时%i分%s秒');select date_format(now(), '%H:%i:%s秒 %m月%d日%Y年');select date_format(curtime(), '%Y年%m月%d日 %H时%i分%s秒');select date_format('2021-10-11 10:46:27', '%Y年%m月%d日 %H时%i分%s秒');- 案例1：查询有奖金的员工名和入职日期(xx月/xx日/ xx年)
select last_name, date_format(hiredate, '%m月/%d日 %y年') 入职日期 from employees where commission_pct is not null;
5、其他函数
- select version();
- select database();
- select user();
6、流程控制函数
6.1、if(expr1, expr2, expr3) 可以实现类似于if else的效果，与三元运算符很像。表达式expr1成立，则返回表达式expr2的值，否则返回表达式expr3的值
select if(10>5, "大", '小') as result;select if(10<5, "大", '小') as result;- 案例1：查询员工有无奖金，分别给有无奖金做个备注
select last_name, commission_pct, if(commission_pct is null, '没奖金，呵呵', '有奖金，嘻嘻') 备注 from employees;6.2 case 的使用
使用一：switch case 的效果
java中 mysql中 switch (变量或表达式) {
case 常量1: 语句1; break;
…
defualt: 语句n; break;
}case 要判断的字段、变量或表达式
when 常量1 then 要显示的值1或 语句1;
when 常量2 then 要显示的值2或 语句2;
…
else 要显示的值n或 语句n;
end案例1：查询员工的工资，要求①部门号=30，显示的工资为1.1倍；②部门号=40，显示的工资为1.2倍；③部门号=50，显示的工资为1.3倍；④其他部门，显示的工资为原工资
- 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;使用二：类似于 多重if
java中 mysql中 if (条件1) { 语句1; }
else if (条件2) { 语句2; }
…
else { 语句n; }case
when 条件1 then 要显示的值1或 语句1;
when 条件2 then 要显示的值2或 语句2;
…
else 要显示的值n或 语句n;
end案例2：查询员工的工资情况，要求①如果工资>2000，显示级别A；②如果工资>15000，显示级别B；③如果工资>10000，显示级别C；④其他工资显示级别D
- select salary,
(CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END) as 工资级别
from employees;
7、分组函数
sum([distinct] expr) 求和、avg([distinct] expr) 平均值、max([distinct] expr) 最大值、min([distinct] expr) 最小值、count(expr)/count(distinct expr,[expr…]) 计算个数
7.1、分组函数简单使用
select sum(salary) from employees as "sum";select avg(salary) from emploees as "avg";select max(salary) from employees as "max";select min(salary) from employees as "min";select count(salary) from emploees as "count";select count(distinct salary) from employees as "distinct count";select sum(salary), avg(salary), max(salary), min(salary), count(salary) from employees;- 案例1：查询员工表中的最大入职时间和最小入职时间的相差天数
select DATEDIFF(max(hiredate), min(hiredate)) as diffrence from employees;7.2、分组函数支持的参数类型
- 虽然不报错，但是结果无意义
select sum(last_name), avg(last_name) from employees;全部返回 0select sum(hiredate), avg(hiredate) from employees;- 结果有意义
select max(last_name), min(last_name), count(last_name) from employees;select max(hiredate), min(hiredate), count(hiredate) from employees;- 注意count()并不统计 null 值
select count(last_name), count(commission_pct) from employees;select count(*), count(commission_pct), count(distinct commission_pct) from employees;- 总结：
- sum、avg 一般用于处理数值型
- max、min 可以处理任何可排序类型
- count 可以处理任何类型，但是不能统计到 null 值，而且默认不去重，会重复统计，使用 “distinct” 可以实现去重统计
7.3、分组函数是否忽略 null 值
select sum(commission_pct), avg(commission_pct) from employees;select avg(commission_pct), sum(commission_pct)/count(*), sum(commission_pct)/count(commission_pct) from employees;select max(commission_pct), min(commission_pct) from employees;select count(*), count(commission_pct), count(distinct commission_pct) from employees;
- 计算sum()时，null值根本就不参与运算，因为 “null+任何” 结果都为null
- 计算avg()时，null值也不参与运算，忽略 null 值
- 计算max()和min时，null值也不参与运算，忽略 null 值
- 计算count()时，null值也不参与运算，忽略 null 值，而且默认不去重，会重复统计，使用 “distinct” 可以实现去重统计
- 总结：以上分组函数都会忽略 null 值
7.4、分组函数可以和 “distinct” 关键字搭配使用，实现去重
select sum(commission_pct), sum(distinct commission_pct) from employees;select avg(commission_pct), avg(distinct commission_pct), sum(commission_pct)/count(commission_pct), sum(distinct commission_pct)/count(distinct commission_pct)from employees;select max(commission_pct), max(distinct commission_pct) from employees;select min(commission_pct), min(distinct commission_pct) from employees;select count(*), count(commission_pct), count(distinct commission_pct) from employees;7.5、专门介绍一下 count()
- count(*) 有效统计总行数。每行数据中，总有一个字段不为 null
select count(*) from employees;- count(1) 也能有效统计总行数。相当于给每行多加了一列，这一列的值为 1，然后统计有多少个 1。
select count(1) from employees;select count(2) from employees;这样也是可以的selecy count("哈哈哈") from employees;- 行数统计效率
- MYISAM存储引擎下，count(*) 的效率最高，因为有记录行数的变量
- INNODB存储引擎下，count(*) 和 count(1) 的效率差不多，比 count(字段) 要高，因为 count(字段) 需要判断是否为 null 值，有个筛选的过程
- 一般使用 count(*) 统计行数
7.6、和分组函数一同查询的字段有限制
select AVG(salary), employee_id from employees;不报错，能执行，但是结果无意义- 和分组函数一同查询的字段要求是 group by 后的字段，其他的都不行
E、分组查询
1、语法
SELECT 分组函数, 其他列(要求出现在group by的后面)
FROM 表名
[WHERE 筛选条件]
GROUP BY 分组的列表
[ORDER BY 排序列表[asc|desc]]
where 一定要放在 from 后面
查询列表有点特殊，要求是分组函数和group by后出现的字段
2、简单使用
案例1：查询每个部门的平均工资
- select department_id, avg(salary)
from employees
group by department_id;案例2：查询每个工种的最高工资
- select job_id, max(salary)
from employees
group by job_id;案例3：查询每个位置上的部门个数
- select location_id, count(*)
from departments
group by location_id;
3、按多字段分组
案例1：查询每个部门每个工种的平均工资
- select avg(salary), department_id, job_id
from employees
group by department_id, job_id;- select avg(salary), department_id, job_id
from employees
group by job_id, department_id;
4、添加分组前筛选，其筛选源是 原始表，放在 group by 子句的前面，使用 where 关键字。能在分组前筛选的优先使用分组前筛选。
案例1：查询邮箱中包含 ‘a’ 字符的，每个部门的平均工资
- select avg(salary), department_id
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;
5、添加分组后筛选，其筛选源是 分组后的结果集，放在 group by 子句的后面，使用 having 关键字。筛选条件涉及到分组函数的，一定是使用分组后筛选，也就是使用 having 来筛选
案例1：查询哪些部门的员工个数 > 2
- select count(*), department_id
from employees
group by department_id
having count(*) > 2;案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
- select job_id, max(salary)
from employees
where commission_pct is not null
group by job_id
having max(salary) > 12000;案例3：查询领导编号 > 102的每个领导手下员工的最低工资 > 5000的领导编号是哪个，以及其员工最低工资
- select min(salary), manager_id
from employees
where manager_id > 102
group by manager_id
having min(salary) > 5000;
6、按函数分组
案例1：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数 > 5的有哪些
- select count(*), length(last_name) len_name
from employees
group by length(last_name)
having count(*) > 5;- select count(*) c, length(last_name) len_name
from employees
group by len_name
having c > 5;
7、添加排序
案例1：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示
- select avg(salary), department_id, job_id
from employees
group by department_id, job_id
order by avg(salary) desc;案例2：查询不为null的每个部门每个工种的员工的平均工资，并且按平均工资的高低显示
- select avg(salary), department_id, job_id
from employees
where department_id is not null and job_id is not null
group by department_id, job_id
order by avg(salary) desc;
F、连接查询
连接查询又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询。笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行。发生的原因：没有有效的连接条件。如何避免：添加有效的连接条件。
1、分类
按年代分类：
- sql92标准（在mysql中仅仅支持内连接）
- sql99标准【推荐】（在mysql中支持内连接、外连接(左外和右外)、交叉连接）
按功能分类：
- 内连接（等值连接、非等值连接、自连接）
- 外连接（左外连接、右外连接、全外连接）
- 交叉连接
2、sql92语法，内连接
2.1、语法
select 查询列表
from 表1, 表2, …
where 等值连接条件
[and 筛选条件]
[GROUP BY 分组的列表]
[ORDER BY 排序列表[asc|desc]]2.2、等值连接
① 简单等值连接
- 案例1：查询女神名和对应的男神名
- SELECT name, boyName
FROM beauty, boys
WHERE beauty.boyfriend_id = boys.id;② 表的顺序可以交换
- 案例1：查询员工名和对应的部门名
- SELECT name, boyName
FROM beauty, boys
WHERE beauty.boyfriend_id = boys.id;③ 为表取别名
- 案例1：查询员工名、工种号、工种名
- select last_name, emp.job_id, job_title
from employees emp, jobs j
where emp.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;⑥ 加排序
- 案例1：查询出每个工种的工种名和员工的个数，并且按员工个数降序
- select job_title, count(*)
from employees e, jobs j
where e.job_id = j.job_id
group by job_title
order by count(*) desc;⑦ 多表连接
- 案例1：查询员工名、部门名和所在城市
- 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;- 案例2：继续加条件和排序
- select last_name, department_name, city
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and city like "s%"
order by department_name desc;⑧ 等值连接总结
- 多表等值连接的结果为多表的交集部分
- n 表连接，至少需要 n-1 个连接条件
- 多表的顺序没有要求
- 一般需要为表起别名
- 可以搭配排序、分组、筛选等子句一同使用
2.3、非等值连接
① 简单非等值连接
- 案例1：查询员工的工资和工资级别
- select salary, grade_level
from employees e, job_grades g
where salary between lowest_sal and highest_sal;② 加入其它筛选条件
- 案例1：查询员工的工资级别为’A’或者’B’的工资
- select salary, grade_level
from employees e, job_grades g
where salary between lowest_sal and highest_sal
and grade_level in ('A', 'B');2.4、自连接
① 简单自连接
- 案例1：查询员工和上级的名称
- 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;
3、sql99语法，内连接
3.1、语法
select 查询列表
from 表1 别名 【连接类型】join 表2 别名 on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序字段 【asc|desc】】其中连接类型：
- 内连接：inner
- 左外连接：left [outer]
- 右外连接：right [outer]
- 全外连接：full [outer]
- 交叉连接：cross
3.2、等值连接（inner可省略）
案例1：查询员工名、部门名
- select last_name, department_name
from employees e
inner join departments d
on e.department_id = d.department_id;- select last_name, department_name
from departments d
inner join employees e
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 locations l
on d.location_id = l.location_id
group by city
where count(*) > 3;案例四：查询哪个部门的部门员工个数 >3 的部门名和员工个数，并按个数排序（排序）
- select e.department_id, d.department_name, count(*) 个数
from employees e
inner join departments d on e.department_id = d.department_id
group by department_id
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;3.3、非等值连接（inner可省略）
案例1：查询员工的工资级和工资级别
- select salary, grade_level
from employees e
inner join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;案例2：查询员工的工资级别为’A’或者’B’的工资（加入筛选条件）
- select salary, grade_level
from employees e
inner join job_grades g on salary between lowest_sal and highest_sal
where grade_level in ('A', 'B');案例3：查询每个工资级别的个数，并按照工资级别降序（加入分组排序）
- select grade_level, count(*)
from employees e
inner join job_grades g on salary between lowest_sal and highest_sal
group by grade_level
order by grade_level desc;3.4、自连接（inner可省略）
- 案例1：查询员工的名字、上级的名字
- select e.last_name 员工名, m.last_name 上级名
from employees e
inner join employees m on e.manager_id = m.employee_id;
4、外连接
应用场景：用于查询一个表中有，另一个表中没有的记录。特点：①、外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null。外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录。②、左外连接，left join 左边的是主表，右外连接，right join 右边的是主表。③、左外连接和右外连接交换连个表的顺序，可以实现同样的查询效果。④、全外连接 = 内连接的结果 + 表1中有但表2没有的 + 表2中有但表1中没有的。但是mysql不支持全外连接的语法。
案例1：查询男朋友 不在男神表的女神名
- SELECT b.name, bo.*
FROM beauty b
LEFT OUTER JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL;案例2：查询哪个部门没有员工
左外连接
- select d.*, e.employee_id
from departments d
left outer join employees e on d.department_id = e.department_id
where e.employee_id is null;右外连接
- select d.*, e.employee_id
from employees e
right outer join departments d on e.department_id = d.department_id
where e.employee_id is null;
5、交叉连接
sql99语法中的交叉连接类似于笛卡尔乘积。mysql中是支持交叉连接的语法的。
select b.*, bo.*
from beauty b
cross join boys bo;
6、总结
sql92 和 sql99 相比
- 功能：sql99支持的连接查询比较多
- 可读性：sql99实现了连接条件和筛选条件的分离，可读性较高
G、子查询
含义：出现在其他语句（包括增删改）中的 select 语句，称为子查询或内查询。内部嵌套了其他select语句的查询称为主查询或外查询。
1、分类
按照子查询出现的位置：
- select 后面
- from 后面
- where 或 having 后面
- exists 后面（相关子查询）
按照结果集的行数不同：
- 标量子查询（结果集只有一行一列，也叫作单行子查询）
- 列子查询（结果集只有一列多行，或叫做多行子查询）
- 行子查询（结果集有一行多列，多行多列其实也行）
- 表子查询（结果集就可以很随意了，一般为多行多列）
用法限制：
- select后面 仅仅支持标量子查询
- from后面 支持表子查询
- where或having后面 支持标量子查询(单行)、列子查询(多行)、行子查询（较少使用）
- exists后面 支持表子查询
用法特点：
- 子查询要放在小括号内部
- 子查询一般放在条件的右侧
- 标量子查询，一般搭配着单行操作符使用 “>”、“<”、“>=”、“<=”、“=”、“<>”
- 列子查询，一般搭配着多行操作符使用 “in”、“any”、“some”、“all”
- 子查询的执行会优先于主查询的执行，主查询的条件用到了子查询的结果
2、where或having 后面的标量子查询（单行子查询）
标量子查询返回的值就是一个单值，所以也称为单行子查询。
案例1：谁的工资比 “Abel” 高
- select *
from employees
where salary > (SELECT salary FROM employees WHERE last_name = "Abel");案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
- select last_name, job_id, salary
from employees
where job_id = (select job_id from employees where employee_id = 141)
and salary > (select salary from employees where employee_id = 143);案例3：返回公司工资最少的员工的last_name, job_id 和 salary
- select last_name, job_id, salary
from employees
where salary = (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);
3、where或having 后面的列子查询（多行子查询）
列子查询返回多行单列值，主查询会使用到一些多行比较操作符。当然包含这些多行操作符的查询往往可以使用其他的方式进行替换，使其可读性更高。
多行比较操作符 含义 替换 in / not in 等于列表中的任意一个 in()可以替换为“=any()”；not int()可以替换为“<>all()” any 或 some 和子查询返回的某一个值比较 “<any()”可以替换为“<max()”；“>any()”可以替换为“>min()”；“=any()”可以替换为“in()” all 和子查询返回的所有值比较 “<all()”可以替换为“<min()”；“>all()”可以替换为“>max()”
案例1：返回 location_id 是 1400或1700的部门中的所有员工姓名
- select last_name
from employees
where department_id in (
select department_id
from departments
where location_id in (1400, 1700)
);- select last_name
from employees
where department_id = any(
select department_id
from departments
where location_id in (1400, 1700)
);案例2：返回其他工种中比job_id为“IT_PROG”的工种任一工资低的员工的：工号、姓名、job_id、以及salary
- select employee_id, last_name, job_id, salary
from employees
where salary < any(
select distinct salary
from employees
where job_id = "IT_PROG"
) and job_id <> "IT_PROG";- select employee_id, last_name, job_id, salary
from employees
where salary < (
select max(salary)
from employees
where job_id = "IT_PROG"
) and job_id <> "IT_PROG";案例3：返回其他工种中比job_id为“IT_PROG”的工种所有工资低的员工的：工号、姓名、job_id、以及salary
- select 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";- select employee_id, last_name, job_id, salary
from employees
where salary < (
select min(salary)
from employees
where job_id = "IT_PROG"
) and job_id <> "IT_PROG";
4、where或having 后面的行子查询
行子查询一般返回一行多列值，也可以是多行多列值。行子查询一般很少使用。
案例1：查询员工编号最小并且工资最高的员工信息
- 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
);
5、select 后面的子查询
select后面的子查询仅仅只支持标量子查询，返回值只能是单值。
案例1：查询每个部门的员工个数
- select d.*, (
select count(*)
from employees e
where e.department_id = d.department_id
) as 员工个数
from departments d;案例1：查询员工号为102的员工的部门名
- select (
select department_name
from departments d
inner join employees e on d.department_id = e.department_id
where e.employee_id=102
) 部门名;
6、from 后面的子查询
from后面的子查询一般是表子查询。该子查询结果直接充当一个新的数据表来使用，要求必须要起别名。
案例1：查询每个部门的平均工资的工资等级
- select temp.*, grade_level
from (
select department_id, avg(salary) avg_sal
from employees
group by department_id
) as temp
left join job_grades g on temp.avg_sal between g.lowest_sal and g.highest_sal;
7、exists 后面的子查询
有时也称为相关子查询。用exists来判断后面的子查询是否存在结果，返回 1 或 0。
案例1：查询有员工名的部门名
- select department_name
from departments d
where exists(
select employee_id
from employees e
where e.department_id = d.department_id
);- select department_name
from departments d
where d.department_id in (
select distinct department_id
from employees
);案例2：查询没有女朋友的男神信息
- select bo.*
from boys bo
where not exists(
select id
from beauty
where boyfriend_id = bo.id
);- select bo.*
from boys bo
where bo.id not in (
select distinct boyfriend_id
from beauty
);
8、子查询案例
案例1：查询和‘Zlotkey’相同部门的员工姓名和工资
- select last_name, salary
from employees
where department_id = (
select department_id
from employees
where last_name = "Zlotkey"
);案例2：查询工资比公司平均工资高的员工的员工号，姓名和工资
- select employee_id, last_name, salary
from employees
where salary > (
select avg(salary)
from employees
);案例3：查询各部门中工资比本部门中平均工资高的员工的员工号、姓名和工资
- select employee_id, last_name, salary
from employees e
left join (
select department_id, avg(salary) avg_sal
from employees
group by department_id
) as temp on e.department_id = temp.department_id
where e.salary > temp.avg_sal;案例4：查询与姓名中包含字母‘u’的员工在相同部门的员工的员工号和姓名
- select employee_id, last_name
from employees
where department_id in (
select distinct department_id
from employees
where last_name like '%u%'
);案例5：查询在部门的location_id为1700的部门工作的员工的员工号
- select employee_id
from employees
where department_id = any(
select distinct department_id
from departments
where location_id = 1700
);案例6：查询管理者是K_ing的员工姓名和工资
- select last_name, salary
from employees
where manager_id in (
select employee_id
from employees
where last_name = "K_ing"
);案例7：查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列明为 姓·名
- select concat(last_name, first_name), salary
from employees
where salary = (
select max(salary)
from employees
);- select concat(last_name, first_name), salary
from employees
where salary = (
select salary
from employees
order by salary desc
limit 1
);
H、分页查询
非常具有应用场景，当要显示的数据，一页显示不全，就需要提交分页查询的sql请求。
1、语法
select 查询列表
from 表1 别名1
【连接类型】join 表2 别名2 on 连接条件
【where 筛选条件】
【group by 分组字段】
【having 筛选条件】
【order by 排序字段 【asc|desc】】
limit offset, size;
offset表示要显示条目的其实索引（起始索引从0开始）
size表示要显示的条目数特点：
- limit语句放在查询语句的最后，是最后被执行的语句
- 分页公式：假设要显示的页数为 page，每页的条目数为 size
select 查询列表 from 表 limit (page-1)*size, size;
2、例子
案例1：查询前五条员工的信息
- select * from employees limit 0, 5;
- select * from employees limit 5;（当offset为0时，可以省略）
案例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;
I、union联合查询
联合查询是将多条查询语句的结果合并成一个结果。union会自动去掉重复的结果，可以使用union all达到不去重的效果。
1、语法
查询语句1
union
查询语句2
union
查询语句3
……应用场景：
- 要查询的结果可能来自于多个表，且多个表没有直接的连接关系，但查询的信息一致
特点：
- 要求多条查询语句的查询列数是一致的
- 要求多条查询语句的查询列表中每一列的类型和顺序最好一致，这样才有意义
- union关键字默认是去重的，如果使用union all则可以包含重复项
2、例子
案例1：查询邮箱中部门编号>90或邮箱中包含a的员工信息
- select * from employees where department_id > 90
union
select * from employees where email like "%a%";- select *
from employees
where department_id > 90
or email like '%a%';
四、DML 数据操纵语言
Data Manipulation Language
DML主要涉及到数据的插入（insert）、修改（update）和删除（delete）。
A、插入语句
1、语法
- 方式一：
- 单行：insert into 表名(列名, …) values(值1, …);
- 多行：insert into 表名(列名, …)
values(值1, …),
(值2, …),
……
(值n, …);- 方式二：insert into 表名 set 列名1=值, 列名2=值, … ;
2、案例特点
- 插入的值的类型要与列的类型一致或兼容
- insert into beauty(id, NAME, sex, borndate, phone, photo, boyfriend_id)
values(13, '唐老鸭', '女', '1990-4-23', '13676542378', null, 2);- insert into beauty
set id=13, name='唐老鸭', sex='女', borndate'1990-4-23', phone='13676542378', photo=null, boyfriend_id=2;- 不可以为null的列必须插入值，可以为null的列可以插入null值，或直接不插入值
- insert into beauty(id, name, sex, borndate, phone, photo, boyfriend_id)
values(13, '唐老鸭', '女', '1990-4-23', '13676542378', null, 2);- insert into beauty(id, name, sex, borndate, phone, boyfriend_id)
values(13, '唐老鸭', '女', '1990-4-23', '13676542378', 9);- insert into beauty(id, name, sex, phone)
values(14, '米老鼠', '女', '12398776543');- insert into beauty
set id=14, name='米老鼠', sex='女', phone='12398776543';- 列的顺序可以调换，但是后面的值需要一一对应
- 列数和值的个数必须要一致
- 可以省略列名，省略后就是默认所有列，而且列的顺序和表中列的顺序一致
- insert into beauty
values(15, '张飞', '男', '1990-5-12', '12387656789', null, null);
3、插入方式对比
- values的方式支持多行插入，set的方式不支持多行插入
- insert into 表名(列明, …)
values(值1, …),
values(值2, …),
……
values(值n, …);- insert into beauty
values(16, '张飞', '男', '1990-5-12', '12387656789', null, null),
(17, '张飞', '男', '1990-5-12', '12387656789', null, null),
(18, '张飞', '男', '1990-5-12', '12387656789', null, null);- values的方式支持子查询，set的方式不支持子查询
- insert into beauty(id, name, sex, phone)
select 19, '貂蝉', '女', '12345687654';- insert into beauty(id, name, sex, phone)
select id+20, boyname, '男', '112'
from boys where id < 3;
B、修改语句
1、修改单表记录
- 语法：
update 表名
set 列1=新值, 列2=新值, …
where 筛选条件;- 案例1：修改beauty表中id大于等于13的记录的电话为12345678909
- update beauty
set phone='12345678909'
where id >= 13;
2、修改多表记录【补充】
- 语法：
- sql92语法：
update 表1 别名, 表2 别名
set 列1=新值, 列2=新值, …
where 连接条件
and 筛选条件;- sql99语法：
update 表1 别名
inner|left|right| join 表2 别名
on 连接条件
set 列1=新值, 列2=新值, …
where 筛选条件;- 案例1：修改张无忌的女朋友的手机号为114
- update boys bo
inner join beauty b on bo.id = b.boyfriend_id
set b.phone = '114'
where bo.boyName = "张无忌";- 案例2：修改没有男盆友的女神的男盆友编号都为2号
- update boys bo
right join beauty b on bo.id = b.boyfriend_id
set b.boyfriend_id = 2
where bo.id is null;
C、删除语句
1、语法
- 方式一：delete（可按照筛选进行删除）
- 单表删除：delete from 表名 where 筛选条件;
- 多表删除：
- sql92：
delete 别名1【,别名2】
from 表1 别名1, 表2 别名2
where 连接条件
and 筛选条件;- sql99：
delete 别名1【,别名2】
from 表1 别名1
inner|left|right join 表2 别名2 on 连接条件
where 筛选条件;- 方式二：truncate（不能筛选，也就是不能加where，只能整表删除）
- truncate table 表名;
2、案例
案例1：删除手机号以9结尾的女神信息
- delete from beauty where phone like '%9';
案例2：删除张无忌的女朋友的信息
- delete b
from beauty b
inner join boys bo on b.boyfriend_id = bo.id
where bo.boyName = "张无忌";案例3：删除黄晓明的信息以及他女朋友的信息
- delete b, bo
from beauty b
inner join boys bo on b.boyfriend_id = bo.id
where bo.boyName = "黄晓明";案例4：truncate清空数据
- truncate table boys;
3、delete 和 truncate 区别
- delete 可以加where条件，truncate 不能加where条件
- truncate 可以理解为清空全表，效率高一丢丢
- 假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始；而truncate删除后，在插入数据，自增长列的值从1开始
- truncate 删除没有返回值，而delete删除有返回值（受影响的行数）
- truncate 删除后不能回滚，delete删除后可以回滚
五、DDL 数据库模式定义语言
Data Definition Language，与DML语言不同，DML语言是在操作库中数据表中的数据，而DDL语言是操作库和表的结构。DML对应增删改（insert，delete，update）数据，DDL对应创建删除修改（create，drop，alter）库和表的结构。IF EXISTS或IF NOT EXISTS只能用在库或表的创建和删除时，新建列和删除列时不能用。
A、库和表的管理
1、库的管理（创建create、修改alter、删除drop）
库的创建：create database [if not exists] 库名;
案例1：创建Books库
- CREATE DATABASE Books;
案例2：创建Books库，但是创建前先判断是否存在
- create database if not exists Books;
库的修改：alter database 库名 修改操作;
一般来说数据库不修改。rename database books to 新库名;（已废弃，不够安全，有丢失数据的风险）案例1：更改库的字符集
- alter database books character set gbk;
库的删除：drop database [if exists] 库名;
案例1：删除库
- drop database if exists books;
2、表的管理（创建create、修改alter、删除drop）
表的创建 ★
语法：
- create table [if not exists] 表名(
列名 列的类型[(长度)] [约束],
列名 列的类型[(长度)] [约束],
列名 列的类型[(长度)] [约束],
……
列名 列的类型[(长度)] [约束]
);
案例1：创建表 Book
- create table book(
id INT,
bName VARCHAR(20),
price DOUBLE,
authorID INT,
publishDate DATETIME
);
案例2：创建表 Author
- create table if not exists author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
);
- create table [if not exists] 表名(
表的修改
- 修改列名
- 修改列的类型或约束
- 添加新的列
- 删除列
- 修改表名
语法：
- alter table 表名 add | drop | modify | change column 列名 [列类型 约束];
案例：
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;ALTER TABLE author ADD COLUMN annual DOUBLE;ALTER TABLE author DROP COLUMN annual;ALTER TABLE author RENAME TO book_author;
表的删除
语法：drop table [if exists] 表名
drop table if exists book_author;表的复制
- 仅复制表的结构
CREATE TABLE copy1_author LIKE author;
- 复制表的结构和全部数据
CREATE TABLE copy2_author
SELECT * FROM author; - 复制表的结构和部分数据
CREATE TABLE copy3_author
SELECT id, au_name, nation
FROM author
where nation = "中国"; - 复制部分表结构（仅仅复制某些字段）
CREATE TABLE copy4_author
select id, au_name
from author
where 0;
- 仅复制表的结构
3、建库建表的通常写法：
- DROP DATABASE IF EXISTS 库名;
CREATE DATABASE 库名; - DROP TABLE IF EXISTS 表名;
CREATE TABLE 表名(…);
B、常见数据类型介绍
- 数值型：
- 整型 ★
- 小数：
- 定点数
- 浮点数 ★
- 字符型：★
- 较短的文本：char、varchar
- 较长的文本：text、blob（二进制数据，比如图片）
- 日期型 ★
1、整型
tinyint、smallint、mediumint、int/integer、bigint
整数类型 字节 范围 Tinyint 1 有符号：-128 ~ 127
无符号：0 ~ 255Samllint 2 有符号：-(2)15 ~ 215-1
无符号：0 ~ 216-1Mediumint 3 有符号：-(2)23 ~ 223-1
无符号：0 ~ 224-1Int、Integer 4 有符号：-(2)31 ~ 231-1
无符号：0 ~ 232-1Bigint 8 有符号：-(2)63 ~ 263-1
无符号：0 ~ 264-1特点：
如果不设置无符号还是有符号，则默认是有符号的。如果想设置无符号，需要添加unsigned关键字。
如果插入的数值超出了该整数类型的数值范围，会报out of range异常，并且会插入临界值。
如果不设置长度，会有默认的长度。该长度代表了显示的最大宽度，如果不够会用0在左侧填充，但必须搭配zerofill关键字一起使用才有效果。使用zerofill后，就自动变为了无符号的整数类型。
drop table if exists tab_int;
create table tab_int(
c1 INT,
c2 INT(7) ZEROFILL,
c3 INT UNSIGNED,
c4 INT(8) UNSIGNED
);
2、小数
浮点数类型 字节 范围 float 4 ±1.75494351E-38 ~ ±3.402823466E+38 double 8 ±2.2250738585072014E-308 ~ ±1.7976931348623157E+308
定点数 字节 范围 dec(M, D)
decimal(M, D)M+2 最大取值范围与double相同，给定decimal的有效取值范围由M和D决定
- 浮点型 float(M, D)、double(M, D)
- 定点型 dec(M, D)、decimal(M, D)
特点：
- M 表示整部位 + 小数部位的长度。
- D 表示小数部位的长度。
- 如果超过范围，则插入临界值。
- M 和 D都可以省略。如果是decimal，则M默认为10，D默认为0；如果是float和double，则会根据插入的数值的精度来决定精度。
- 定点型的精度较高，如果要求插入数值的精度较高如货币运算等则考虑使用。
- drop table if exists tab_float;
create table tab_float(
f1 FLOAT(5, 2),
f2 DOUBLE(5, 2),
f3 DECIMAL(5, 2)
);insert into tab_float values(123.45, 123.45, 123.45);执行成功insert into tab_float values(123.456, 123.456, 123.456);四舍五入成123.46insert into tab_float values(123.4, 123.4, 123.4);结果为123.40insert into tab_float values(1523.4, 1523.4, 1523.4);如果能插入，插入的值为999.99
3、字符型
- 较短的文本：char、varchar
- 较长的文本：text、blob（较大二进制）
- binary和varbinary用于保存较短的二进制
- enum用于保存枚举
- set用于保存集合
字符串类型 最多字符数 描述及存储需求 char(M) M M为0~255之间的整数 varchar(M) M M为0~65535之间的整数 说明： - binary和varbinary类型类似于char和varchar类型，不同的是它们包含二进制字符串而不包含非二进制字符串。
- bit(M)表示位类型，占 1 ~ 8 个字节，范围为 bit(1) ~ bit(8)。
- enum类型，又称为枚举类型。要求插入的值必须为列表中指定的值之一。如果列表成员为1 ~ 255，则需要1个字节存储；如果列表成员为255 ~ 65535，则需要2个字节存储；最多需要65535个成员。
- drop table if exists tab_char;
create table tab_char(
c1 ENUM('a', 'b', 'c')
); insert into tab_char values('a');成功插入insert into tab_char values('m');报错- set类型，和enum类型类似，里面可以保存0 ~ 64个成员。和enum类型最大的区别是：set类型一次可以选取多个成员，而enum只能选取一个。根据成员个数的不同，存储所占的字节也不同。成员数1 ~ 8，字节数为1；成员数9 ~ 16，字节数为2；成员数17 ~ 24，字节数为3；成员数25 ~ 32，字节数为4；成员数33 ~ 64，字节数为8；
- drop table if exists tab_char;
create table tab_char(
s1 SET('a', 'b', 'c', 'd')
); insert into tab_char values('a');成功插入insert into tab_char values('A,B');成功插入insert into tab_char values('a,b,d');成功插入
char和varchar的特点：
- 语法相同char(M)、varchar(M)，其中M代表的含义也相同，表示最多字符数。
- char(M) 表示固定长度的字符，M可以省略，默认为1；varchar(M) 表示可变长度的字符，M不可以省略。
- char 比较耗费存储空间，varchar 比较节省存储空间。
- char 的效率高，varchar 的效率低。
4、日期型
日期和时间类型 字节 最小值 最大值 date 4 1000-01-01 9999-12-31 time 3 -838:59:59 838:59:59 year 1 1901 2155 datetime 8 1000-01-01 00:00:00 9999-12-31 23:59:59 timestamp 4 19700101080001 2038年的某个时刻 说明：
- date值保存日期，time只保存时间，year只保存年
- datetime保存日期 + 时间
- timestamp保存日期 + 时间
- timestamp支持的时间范围较小，取值范围：19700101080001 —— 2038年的某个时刻
- datetime的取值范围是：1000-01-01 00:00:00 —— 9999-12-31 23:59:59
- timestamp和实际时区有关，更能反映实际的日期，而datetime则只能反映出插入时的当地时区
- timestamp的属性受MySql版本和SQLMode的影响很大
- drop table if exists tab_date;
create table tab_date(
t1 DATETIME,
t2 TIMESTAMP
);insert into tab_date values(now(), now());
C、常见约束
约束是一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性。
- 分类：（六大约束）
- NOT NULL（非空，用于保证该字段的值不能为空）
- DEFAULT（默认，用于保证该字段有默认值）
- PRIMARY KEY（主键，用于保证该字段的值具有唯一性，并且非空）
- UNIQUE（唯一，用于保证该字段的值具有唯一性，可以为空）
- CHECK（检查约束，【mysql中不支持】，用于保证该字段满足一定条件）
- FOREIGN KEY（外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表中添加外键约束，用于引用主表中某列的值）
- 添加约束的时机：
- 创建表时
- 修改表时
- 约束的添加分类：
- 列级约束：六大约束语法上都支持列级约束，但外键约束和检查约束加在列级约束上没有效果
- 表级约束：除了非空和默认约束外，其他约束都支持，但不一定都有效果，比如check可以加，但没效果
- 主键和唯一的对比：
- 主键保证了唯一性，且不能为空。一个表中最多只能有一个主键。允许多个字段组合成一个主键，但是要满足唯一性要求。
- 唯一性约束也保证了唯一性，但是可以为空，但是只能有一个记录的值为空，多个记录为空那不就重复了吗。一个表中可以有多个唯一键。允许多个字段组合成一个唯一键，但是得满足唯一性要求。
- 外键说明：
- 要求在从表设置外键关系
- 从表的外键列的类型和主表的关联列的类型要求一直或兼容，名称无要求
- 主表的关联列必须是一个key（一般是主键或唯一）
- 插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表
1、列级约束（创建表时）
直接在字段名和字段类型后面追加 约束类型即可。只支持：default、not null、primary key、unique
案例：
- drop database if exists students;
create database students;
use students;
drop table if exists major;
create table major(
id INT primary key,
majorName VARCHAR(20) not null
);- use students;
drop table if exists stuinfo;
create table stuinfo(
id INT primary key,
stuName VARCHAR(20) not null,
gender CHAR(1) check(gender='男' or gender='女'),
seat INT unique,
age INT default 18,
majorId INT foreign key references major(id)
);- 使用
show index from stuinfo;可以查看该表上的索引，包括主键、外键、唯一键。主键(primary key)和唯一键(unique)会自动生成索引，主键会生成主键索引，整个表的数据是按照主键索引生成的树结构来组织的，唯一键会生成唯一索引，唯一索引不同于普通索引。这里使用show index from stuinfo;只能看到主键和唯一键，不能看到外键，因为外键约束加在列级约束上没有效果。
2、表级约束（创建表时）
在各个字段声明完成后的最下面，加上表级约束即可。语法：【constraint 约束名】 约束类型(字段名)
案例：
- drop table if exists stuinfo;
create table stuinfo(
id INT,
stuName VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorId INT,
CONSTRAINT pk PRIMARY KEY(id),
CONSTRAINT uq UNIQUE(seat),
CONSTRAINT ck CHECK(gender in ('男', '女')),
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) references major(id)
);- 这里使用
show index from stuinfo;可以查看该表上的索引，包括主键、外键、唯一键。主键的名称即使我们为其改名为”pk”也没有效果，主键名称就叫”PRIMARY”（MySql中的奇怪规则）。
3、一般的约束写法
主键约束、非空约束、唯一约束、默认约束可以作为列级约束直接追加到每列申明的后面，外键约束使用表级约束的方式添加。而检查约束在mysql中只支持语法，也就是不报错，但是没有任何效果。
create table if not exists stuinfo(
id INT primary key,
stuName VARCHAR(20) not null,
gender CHAR(1),
seat INT unique,
age int default 18,
majorId INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)
);
4、修改表时添加约束
- 添加列级约束
alter table 表名 modify column 字段名 字段类型 列级约束;
- 添加表级约束
alter table 表名 add [constraint 自定义约束名] 表级类型(字段名) [外键的引用];
建表：
- drop table if exists stuinfo;
create table stuinfo(
id INT,
stuName VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorID INT
);添加非空约束：
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 seat INT UNIQUE;列级约束ALTER TABLE stuinfo ADD UNIQUE(seat);表级约束添加外键：
ALTER TABLE stuinfo ADD FOREIGN KEY(majorId) REFERENCES major(id);表级约束ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id);表级约束
4、修改表时删除约束
删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;删除主键
ALTER TABLE stuinfo MODIFY COLUMN id INT;ALTER TABLE stuinfo DROP PRIMARY KEY;删除唯一约束
ALTER TABLE stuinfo DROP INDEX seat;删除外键
ALTER TABLE sutinfo DROP FOREIGN KEY fk_stuinfo_major;
D、自增长列（标识列）
标识列又称为自增长列，可以不用手动地插入值，系统提供默认的序列值。
1、创建表时设置标识列
- drop table if exists tab_identity;
create table tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);INSERT INTO tab_identity VALUES(NULL, 'Tom');id字段的值自动插入1INSERT INTO tab_identity VALUES(NULL, 'john');id字段的值自动增长到2INSERT INTO tab_identity(name) VALUES('jerry');id字段的值自动增长到3INSERT INTO tab_identity SET name='marry';id字段的值自动增长到4- 使用
delete from tab_identity;后再次添加数据时，id字段的值自动从断点处开始增长- 使用
truncate table tab_identity;后再次添加数据时，id字段的值又重新从1开始自增
2、自增长的系统参数设置
- show variables like '%auto_increment%';
Variable_name Value auto_increment_increment 1 auto_increment_offset 1 - auto_increment_offset 表示起始值是 1
- auto_increment_increment 表示每次增长的步长是 1
- MySql中不支持对 auto_increment_offset 的更改，即使改了也没效果
- 但是你可以在插入值的时候指定一个值，之后的插入就会以你指定的值开始增长
insert into tab_identity values(10, 'jack');id字段的值直接就是10了insert into tab_identity values(null, 'rose');id字段的值自动增长到11- auto_increment_increment 在MySql中是支持更改的，并且改了确确实实有效果
SET auto_increment_increment = 3;
3、使用特点
- 标识列不一定要和主键搭配，但是要求标识列一定是一个key（比如primary key, unique, foreign key）
- 一个表中最多只能有一个标识列
- 标识列的类型只能是数值型（比如int，float，double），一般是 int
- 标识列可以通过
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;
六、TCL 事务控制语言
Transaction Control Language
事务：一个或一组sql语句组成一个执行单元，这个执行单元作为一个不可分割的整体，要么全部执行，要么全部不执行。如果这个执行单元中某条SQL语句执行失败或产生错误，整个单元将会回滚，所有受到影响的数据将会返回到该事务开始以前的状态；如果单元中的的所有sql语句均执行成功，则事务被顺利执行。
在mysql中，数据用各种不同的技术存储在文件或内存中。通过 show engines; 可以查看mysql支持的存储引擎。在mysql中用的最多的存储引擎有：innodb、myisam、memory等。其中innodb支持事务，而myisam、memory等不支持事务。
A、事务的ACID属性
事务的ACID属性：
- 原子性（Atomicity）
- 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
- 一致性（Consistency）
- 事务必须使数据库从一个一致性状态变换到另一个一致性状态。
- 隔离性（Isolation）
- 事务的隔离性是指一个事务的执行不能被其他事务所干扰，即一个事务内部的操作及使用的数据对并发的其他事务来说是隔离的，并发执行的各个事务之间不能相互干扰。
- 持久性（Durability）
- 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。
隐式的事务： 事务没有明显的开启和结束标记。比如单独的 insert、update、delete语句（DML）。使用 show variables like 'autocommit' 可以看到默认的值为ON，表示开启了自动事务提交。
显式的事务： 事务具有明显的开启和结束标记。前提是必须先设置自动提交功能为禁用。set autocommit = 0; 该方法只对当前Session有效。多条sql语句要想以事务的方式执行，就可以使用隐式的事务，先禁用掉 autocommit。
B、多语句以事务的方式执行
多语句以事务的方式执行：
- 步骤一：开始显式的事务
set autocommit = 0;
start transaction;
步骤二：编写事务中的多条sql语句（select、insert、update、delete）;
语句1;
语句2;
……
步骤三：结束事务
commit;提交事务
rollback;回滚事务- 只是针对DQL语言和DML语言来说有事务，对于DDL语言（数据库和表结构的定义）来说，一般不谈论事务。
演示事务的使用步骤
- 先建个表：
- drop table if exists account;
create table account(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);- 插入数据进去：
- insert into account(username, balance)
values('A', 1000),('B', 1000);- 转账事务正常提交演示：
- SET autocommit=0;
START TRANSACTION;
update account set balance=balance-500 where username='A';
update account set balance=balance+500 where username='B;';
commit;- 转账事务回滚演示：
- SET autocommit=0;
START TRANSACTION;
update account set balance=balance-500 where username='A';
update account set balance=balance+500 where username='B;';
rollback;
C、并发事务控制
- 对于同时运行的多个事务，当这些事务访问数据库中的相同数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。
- 脏读： 对于两个事务T1，T2。事务T1读取了被T2更新但还没有被提交的字段，之后若T2回滚，那么T1读取的内容就是临时且无效的脏数据。
- 不可重复读： 对于两个事务T1，T2。事务T1读取了一个字段，然后T2更新了该字段，之后T1再次读取同一个字段，值就不同了。
- 幻读： 对于两个事务T1，T2。事务T1从表中读取了一个字段，然后T2在该表中插入了一些新的行，之后T1再次读取同一个表，就会多出几行。
- 数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题。
- 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了很多事务的隔离级别，不同的隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱。
- 数据库提供的4中事务隔离级别
- 读未提交（READ UNCOMMITTED）：允许事务读取其他事务提交的变更。脏读、不可重复读和幻读的问题都会出现。
- 读已提交（READ COMMITTED）：只允许事务读取已经被其他事务提交的变更。可以避免脏读，但是不可重复读和幻读的问题仍然可能出现。
- 可重复读（REPEATABLE READ）：确保事物可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新。可以避免脏读和不可重复读，但是幻读的问题仍然存在。
- 串行化（SERIALIZABLE）：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作。所有并发问题都可以避免，但是性能十分低下。
- Oracle 支持的2种事务隔离级别：READ COMMITTED，SERIALIZABLE。Oracle 默认的事务隔离级别为：READ COMMITTED
- MySql 支持以上4种事务的隔离级别。MySql默认的事务隔离级别为：REPEATABLE READ
- 每启动一个mysql程序，就会获得一个单独的数据库连接，每个数据库连接都有一个全局变量 @@tx_isolation ，表示当前的事务隔离级别
- 查看当前的隔离级别：
SELECT @@tx_isolation;- 设置当前mysql连接的隔离级别：
set session transaction isolation level read committed;- 设置数据库系统的全局的隔离级别：
set global transaction isolation level read committed;
D、回滚点（savepoint）
- savepoint 节点名; #设置保存点
- 演示 savepoint 的使用（只能搭配 rollback 使用）
- set autocommit=0;
start transaction;
delete from account where id = 1;
savepoint a;
delete from account where id = 2;
rollback to a;
E、事务中的delete和truncate
事务中使用delete时，能被正常回滚，数据不会被删除掉
- set autocommit=0;
start transaction;
delete from account;
rollback;事务中使用truncate时，不能回滚被删除的数据，数据会被真的删除掉
- set autocommit=0;
start transaction;
truncate table account;
rollback;总结：
- 事务中的delete能被正常回滚，记录只是被标记上删除，但实际上还并未被删除；而事务中的truncate没办法正常回滚，数据是真的被删除了。
- 再啰嗦一句：truncate删除全表数据后，再插入数据时，表中的自增长列会重新从1开始自增；而delete删除数据后，再插入数据时，表中的自增长列还是从原来的断点处继续自增。
- truncate只支持删除全表记录，不支持筛选条件。delete支持按照筛选条件删除。
七、DCL 数据库控制语言
Data Control Language
A、用户角色权限控制
待续。。。。。。
八、视图
视图其实就是一个虚拟的表，可以和普通的表一样使用。mysql5.1版本出现的新特性，是通过现有的的表动态生成的数据，并且是在使用视图时才动态生成，只保存了sql逻辑，不保存查询结果。当多个地方用到同样的查询结果或该查询结果使用的sql语句较为复杂时可以使用视图功能。
案例：查询姓张的学生名和专业名
- select stuname, majorname
from stuinfo s
inner join major m on s.majorid=m.id
where stuname like '张%';- create view v1
as
select stuname, majorname
from stuinfo s inner join major m on s.majorid=m.id;- select * from v1 where stuname like '张%';
A、视图的创建
语法：
- create view 视图名
as
查询语句;案例1：查询姓名包含a字符的员工名，部门名和工种信息
- create view myview1
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 e.job_id = j.job_id;- select * from myview1 where last_name like "%a%";
案例2：查询各部门的平均工资级别
- 创建视图查看各部门平均工资
create view myv2 as
select AVG(salary) avg_sal, department_id
from employees
group by department_id;- 使用视图
select m.avg_sal, g.grade_level
from myv2 m
join job_grades g on m.avg_sal between g.lowest_sal and g.highest_sal;案例3：查询平均工资最低的部门信息
- 直接使用以上创建好的视图myv2
select d.*
from departments d
where d.department_id = (
select department_id from myv2 order by avg_sal limit 1
);
- 视图可以重用sql语句
- 简化了复杂的sql操作，不必知道它的查询细节
- 保护数据，提高了安全性（因为不知道原表的情况）
B、视图的修改
方式一：
- create or replace view 视图名
as
查询语句;- 若视图存在，则替换；若视图不存在，则创建
create or replace view myv2 as select * from employees;方式二：
- alter view 视图名
as
查询语句;alter view myv2 as select * from departments;
C、视图的删除
语法：
- drop view 视图名, 视图名, …;
- drop view if exists 视图名, 视图名, …;
drop view if exists myview1, myv2;
D、查看视图的结构
类似于表的查看。
先创建一个视图
- create or replace view myview as
select * from employees;查看视图的结构
- desc myview;
- show create view myview; 能看到创建视图时的sql语句
E、视图的更新
这里视图的更新并不是指的视图的逻辑修改，而是指视图的逻辑不变，而视图中数据的更新。
1、先来创建两个视图
- create or replace view myview1 as
select last_name, email, salary*12*(1+IFNULL(commission_pct, 0)) annual_salary
from employees;- create or replace view myview2 as
select last_name, email
from employees;2、查看视图中的数据
- select * from myview1;
- select * from myview2;
3、插入数据到视图中
- insert into myview1 values('A', 'aaa@qq.com', 1000000), ('B', 'bbb@qq.com', 1100000);
报错：Column ‘annual_salary’ is not updatable. 并不是所有的字段都能更新。- insert into myview2 values('A', 'aaa@qq.com'), ('B', 'bbb@qq.com');
成功插入，并且会插入到原始表中。这个时候由于原始表有了新数据，视图myview1中的数据也会变多，因为myview1是由原始表动态生成的。4、修改视图中的数据
- update myview1 set last_name="AAAAA" where last_name="A";
成功修改，原始表中的数据也会被修改，本质上就是修改的原始表中的数据，因为视图就是一个sql逻辑而已，并不保存数据，数据都是在原始表上的。- update myview2 set last_name="A" where last_name="AAAAA";
同上，成功修改。- update myview1 set annual_salary=3000000 where last_name="A";
这个会报错：Column ‘annual_salary’ is not updatable。5、删除视图中的数据
- delete from myview1 where last_name in ("A", "B");
同样，删除的是原始表中的数据，会同时影响myview1和myview2。
注意：视图的可更新性与创建视图时定义的查询有关，以下类型的视图是不能更新的
- 包含以下关键字的sql语句：分组函数、distinct、group by、having、union、或者 union all
- 常量视图
create view v as select '常量1'; - select 中包含子查询
- join 关联查询
- from 一个不能更新的视图
- where 子句的子查询引用了from子句中的表
F、视图和表的对比
- 创建视图和表所使用的关键字不一样，
"create view""create table" - 视图只是保存了一组sql逻辑，不保存数据；数据都在原始的表中
- 视图也是能增删改的，但是有些情况下不能增删改，一般也不使用其增删改的功能
九、变量
- 系统变量
- 全局变量（针对于mysql服务器有效）
- 会话变量（只针对当前会话，或一次客户端连接有效）
- 自定义变量
- 用户变量（类似于java的全局变量）
- 局部变量（类似于java的局部变量）
A、系统变量（全局、会话）
由MySql系统为我们提供的变量，不是用户定义的，属于服务器层面的。分为全局变量和会话变量。因为这些变量由系统提供，所以并不需要我们自己定义，我们只需要学会如何去查看和赋值就行。如果是操作全局变量，则需要加global关键字；如果是操作会话变量，则可以加上session关键字；如果不加这两关键字，则默认操作会话变量。
对于全局变量来说，mysql服务器每次启动会为所有的全局变量赋初始值，可以修改全局变量的值，针对于所有的会话（连接）有效，但不能跨重启，也就是说每次mysql重启后，全局变量的值会被重新初始化。如果要想每次重启后都是修改后的值，那得去改配置文件了。
对于会话变量来说，会话变量仅仅针对于当前会话（连接）有效。实测后发现，会话变量比全局变量多。
1、查看所有的系统变量：
show global variables;global表示全局变量show session variables;session表示会话变量show variables;默认表示查看会话变量2、查看满足条件的系统变量（模糊查询）：
show global variables like '模糊查询条件';show session variables like '模糊查询条件';show variables like '模糊查询条件';- 比如：
show variables like '%character_set%';3、查看指定的某个系统变量的值：
select @@global.系统变量名;global表示全局变量select @@session.系统变量名;session表示会话变量select @@系统变量名;默认表示会话变量- 比如：
select @@character_set_client;4、为某个系统变量赋值：
- 方式一：
set global 系统变量名 = 值;set session 系统变量名 = 值;set 系统变量名 = 值;- 比如：
set autocommit = 0;- 方式二：
set @@global.系统变量名 = 值;set @@session.系统变量名 = 值;set @@系统变量 = 值;- 比如：
set @@autocommit = 0;
B、自定义变量（用户、局部）
用户自己定义的变量，不是由系统提供的。必须在用户声明、赋值后才能使用（查看，比较，运算等）。
1、用户变量
用户变量的作用域为：针对当前会话（连接）有效，等同于会话变量的作用域。可以应用在任何地方，begin end 里或则begin end外都行。
- ① 声明并初始化（用户变量在声明时必须初始化）
- 方式一： set @用户变量名 = 值;
- 方式二： set @用户变量名 := 值;
- 方式三： select @用户变量 := 值;
- 赋值的操作符为 “=” 或 “:=”。在使用set来声明变量时，”=” 或 “:=” 都可以当做赋值操作符来使用；但是在使用select来声明变量时，只能将 “:=” 当做赋值操作符来使用。
- ② 赋值（更新用户变量的值）
- 方式一：通过 SET 或 SELECT。同上（既能当声明用又能当赋值用）。
set @name = 'john';set @name = 100;set @count := 0;- 方式二：通过 SELECT INTO。
- select 字段 into @用户变量名 from 某表;
select count(*) into @count from employees;- ③ 简单使用（查看用户变量的值）
- select @系统变量名;
select @count;
2、局部变量
局部变量的作用域为：仅仅在定义它的begin end中有效。也就是说局部变量只能应用在begin end中，而且只能放在begin end中的第一句话中声明，生命周期很短。
- ① 声明
- DECLARE 变量名 类型;
- DECLARE 变量名 类型 DEFAULT 值;
- ② 赋值
- 方式一：通过 SET 或 SELECT。
- set 局部变量名 = 值;
- set 局部变量名 := 值;
- select @局部变量名 := 值;
- 方式二：通过 SELECT INTO。
- select 字段 into 局部变量名 from 某表;
select count(*) into aaa from employees;- ③ 简单使用
- SELECT 局部变量名;
3、用户变量和局部变量的对比
- 用户变量的作用域为当前会话；而局部变量的作用域为begin end中
- 用户变量可以使用在会话中的任何位置；而局部变量只能放在begin end的第一句话中声明
- 用户变量必须加 @ 符号；而局部变量一般不用加 @ 符号，当使用select赋值时需要加 @ 符号
- 用户变量不需要限定变量的类型；而局部变量需要在声明时限定变量的类型
4、使用案例
- 案例1：声明两个变量并赋值，求和，并打印
- 方式一：使用用户变量
set @m = 1;
set @n = 2;
set @sum = @m + @n;
select @sum;- 方式二：使用局部变量
declare m int default 1;
declare n int default 2;
declare sum;
set sum = m + n;
select sum;
十、存储过程和函数
存储过程和函数，类似于java中的方法。
A、存储过程
存储过程就是一组预先编译好的SQL语句的集合，批处理语句。提高了代码的重用性，简化操作，减少了编译次数并且减少了和数据库服务器的连接次数，提高效率。
1、创建存储过程
- create procedure 存储过程名(参数列表)
BEGIN
存储过程体(一组合法有效的sql语句)
END- 注意事项
- 参数列表包含三部分信息：参数模式、参数名、参数类型。比如
IN stuname VARCHAR(20)。- 其中参数模式有三种，分别是：IN，OUT，INOUT
- INT 参数模式表示该参数可以作为输入，也就是该参数需要调用方传入值。
- OUT 参数模式表示该参数可以作为输出，也就是该参数可以作为返回值。
- INOUT 参数模式表示该参数既可以作为输入，又可以作为输出，也就是该参数既需要传入值，又可以返回值。
- 如果省略了参数模式，增默认的参数模式为 IN
- 如果存储过程仅仅只有一句话，那么 BEGIN END 可以省略
- 存储过程中的每条SQL语句的结尾要求必须加 分号
- 存储过程的结尾可以使用 delimiter 关键字来重新设置
- 语法：
DELIMITER 结束标记- 比如：
delimiter $2、调用存储过程
- CALL 存储过程名(实参列表);
3、空参的存储过程
- 案例：插入到admin表中五条记录
- delimiter $
create procedure myp1()
begin
insert into admin(username, `password`)
values('A', '0000'), ('B', '0000'), ('C', '0000'), ('D', '0000'), ('E', '0000');
end $- 调用：call myp1();
4、带 IN 参数模式的存储过程
- 案例1：创建存储过程，根据女神名，查询对应的男神信息
- 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('赵敏');
- 调用方式二：（新建一个变量作为参数传入）
- set @beautyName='赵敏';
call myp2(@beautyName);- 案例2：创建存储过程，实现判断用户是否登录成功
- 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('A', '0000'); 返回”成功”
- 调用：call myp3('A', '1111'); 返回”失败”
5、带 OUT 参数模式的存储过程
- 案例1：根据女神名，返回对应的男神名
- delimiter $
create procedure myp4(in beautyName 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=beautyName;
end $- 调用：
- call myp4('赵敏', @bName);
- select @bName;
- 案例2：根据女神名，返回对应的男神名和男神魅力值
- delimiter $
create procedure myp5(in beautyName varchar(20), out boyName varchar(20), out userCp int)
begin
select bo.boyName,bo.userCp into boyName, userCp
from boys bo
inner join beauty b on bo.id=b.boyfriend_id
where b.name=beautyName;
end $- 调用
- call myp5('赵敏', @bName, @userPc);
- select @bName, @userPc;
6、带 INOUT 参数模式的存储过程
- 案例：传入a和b两个值，最终a和b都翻倍并返回
- delimiter $
create procedure myp6(inout a int, inout b int)
begin
set a = 2 * a;
set b = 2 * b;
end $- 调用：
- set | [select] @a:=2, @b:=3; #首先需要定义出变量
- call myp6(@a, @b);
- select @a, @b;
7、存储过程的删除
- drop procedure 存储过程名;
- drop procedure if exists 存储过程名;
8、存储过程的查看
- desc 存储过程名; 错误语法，desc只能用来查看表或视图的结构
- show create procedure 存储过程名; 正确语法
show create procedure myp6;- 查看所有的存储过程：show procedure status;
- 存储过程不能修改，要想修改，只能删掉重建。
B、函数
函数与存储过程非常的相似，存储过程可以有0个或多个返回值，函数有且只能有1个返回值。存储过程适合做批量插入，批量更新的操作；函数适合用来处理数据后返回一个结果。
1、函数的创建
- create function 函数名(参数列表) returns 返回类型
begin
函数体
end- 注意事项
- 参数列表包含两部分：参数名和参数类型。
- 函数体中肯定会有 return 语句，如果没有就会报错。如果return语句没有放在函数体的最后也不会报错，但是不建议这样用。
- 当函数体中只用一句话时，则可以省略begin end
- 存储过程中的每条SQL语句的结尾要求必须加 分号
- 存储过程的结尾可以使用 delimiter 关键字来重新设置
- 语法：
DELIMITER 结束标记- 比如：
delimiter $2、函数的调用
- select 函数名(实参列表);
- 执行函数中的所有语句，并且查询出返回值，函数一定要有返回值
案例1（无参有返回）：返回公司的员工个数
- delimiter $
create function myf1() returns int
begin
declare result int default 0; #定义局部变量
select count(*) into result from employees;
return result;
end $- 可能会报错
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)。需要设置一下全局变量的值：set @@global.log_bin_trust_function_creators=1;- 调用：select myf1();
案例2（带参有返回值）：根据员工名，返回他的工资
- delimiter $
create function myf2(empName varchar(20)) returns double
begin
set @sal=0.0; #也可以定义用户变量
select salary into @sal
from employees;
where last_name=empName;
return @sal;
end $- 调用：select myf2('Kochhar');
- 注意：当查询结果多于一条时会报错
Result consisted of more than one row案例3（带参有返回值）：根据部门名，返回该部门的平均工资
- delimiter $
create function myf3(deptName varchar(20)) returns double
begin
declare sal double; #定义局部变量
select avg(salary) into sal
from employees e
join departments d on e.department_id=d.department_id
where d.department_name=deptName;
return sal;
end $- 调用：select myf3('IT');
3、函数的删除
- drop function 函数名;
- drop function if exists 函数名;
4、函数的查看
- desc 函数名; 错误语法，desc只能用来查看表或视图的结构
- show create function 函数名; 正确语法
show create function myf1;- 查看所有的函数：show function status;
- 同样函数也不能修改，要想修改，只能删掉重建。
十一、流程控制结构
顺序结构是指程序从上往下依次执行。分支结构是指程序从两条或多条执行路径中选择一条去执行。循环结构是指程序在满足一定条件的基础上，重复执行一段代码。
A、分支结构
1、if函数（实现简单的双分支）
IF(表达式1, 表达式2, 表达式3);如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值。这个函数可以放在任何地方使用，比如 select 语句中，begin end 中也可以使用。
2、case结构（可实现多分支）情况一：类似于java中的switch语句，一般用于实现等值判断
java中 mysql中 switch (变量或表达式) {
case 常量1: 语句1; break;
…
defualt: 语句n; break;
}case 要判断的字段、变量或表达式
when 常量1 then 返回的值1或 语句1;
when 常量2 then 返回的值2或 语句2;
…
else 返回的值n或 语句n;
end case;情况二：类似于java中的多重if语句，一般用于实现区间判断
java中 mysql中 if (条件1) { 语句1; }
else if (条件2) { 语句2; }
…
else { 语句n; }case
when 条件1 then 返回的值1或 语句1;
when 条件2 then 返回的值2或 语句2;
…
else 返回的值n或 语句n;
end case;特点：
- 可以作为表达式嵌套在其他语句中使用，这时可以用在任何地方，begin end 中或 begin end外面。也可以作为独立的语句去使用，但只能放在begin end中使用。
- 如果 WHEN 中的值满足或条件成立，则执行对应的 THEN 后面的语句，并且结束 CASE；如果都不满足，则执行 ELSE 中的语句或值。
- ELSE 可以省略，如果 ELSE 省略了，并且所有的 WHEN 条件都不满足，则返回 null。
案例；创建存储过程，根据传入的成绩，来显示等级。（90——100 显示A，80——90 显示B，60——80 显示C，否则显示D）
- delimiter $
create procedure test_case(in score int)
begin
case
when score >= 90 then select 'A';
when score >= 80 then select 'B';
when score >= 60 then select 'C';
else select 'D';
end case;
end $- 调用：call test_case(85);
3、if结构（实现多重分支）
- if 条件1 then 语句1;
elseif 条件2 then 语句2;
……
[else 语句n;]
end if;- 只能应用在begin end中，比如存储过程、函数
- 案例：根据传入的成绩，来返回等级。（90——100 返回，80——90 返回，60——80 返回，否则返回D）
- delimiter $
create function test_if(score int) returns char
begin
if score >= 90 then return 'A';
elseif score >= 80 then return 'B';
elseif score >= 60 then return 'C';
else return 'D';
end if;
end $- 调用：select test_if(85);
B、循环结构
- 1、分类：
- while
- loop
- repeat
- 2、循环控制：
- iterate：类似于continue，结束本次循环，继续下一次
- leave：类似于brek，跳出，结束当前所在循环
- 3、while 循环的语法：（类似于java中的while）
- [标签:] while 循环条件 do
循环体;
end while [标签];- 4、loop 循环的语法：（没有条件的死循环）
- [标签:] loop
循环体
end loop [标签];- 可以用来模拟简单的死循环，搭配循环控制语句使用
- 5、repeat 循环的语法：（类似java中的do while）
- [标签:] repeat
循环体;
util 结束循环的条件
end repeat [标签];- 6、案例演示：
- 案例1（无循环控制语句）：批量插入，根据次数插入到admin表中多条记录
- delimiter $
create procedure test_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 test_while1(100);
- 案例2（带循环控制语句 leave）：批量插入，固定插入20条记录到admin表中
- delimiter $
create procedure test_while2()
begin
declare i int default 1;
label_a: while true do
insert into admin(username, `password`) values(concat('xiaohua_', i), '000');
if i>=20 then leave label_a; # 加了一条循环控制语句，必须带上标签
end if;
set i = i+1;
end while label_a;
end $- 调用：call test_while2();
- 案例3（带循环控制语句 iterate）：批量插入，传入次数，只插入偶数次记录到admin表中
- delimiter $
create procedure test_while3(in insertCount int)
begin
declare i int default 1;
label_b: while i<=insertCount do
if i%2=1 then
set i = i+1;
iterate label_b; # 加了一条循环控制语句，必须带上标签
else
insert into admin(username, `password`) values(concat('yczlab_', i), '000');
set i = i+1;
end if;
end while label_b;
end $- 调用：call test_while3(100);
C、经典案例
案例1：已知表 stringcontent(id int 自增长, content varchar(20))，向该表插入指定个数的随机字符串
- 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 str varchar(26) default 'abcdefghijklmnopqrstuvwxyz';
declare startIndex int default 1;
declare len int default 1;
while insertCount>=1 do
set startIndex = floor(rand()*26 + 1); #随机产生[1,26]的整数
set len = floor(rand()*(26-startIndex+1)+1); #随机产生[1, 26-startIndex+1]的整数
set len = if(len>20, 20, len); #保证长度最多为20
insert into stringcontent(content) values(substr(str, startIndex, len));
set insertCount = insertCount-1;
end while;
end $- call test_randstr_insert(100);
