mysql
一、DDL数据定义语言
库的管理
创建：create database 【if not exists】库名
修改：(更改库的字符集)alter database 库名 character set (如gbk）
删除：drop database 【if exists】库名
表的管理
创建、修改、删除、复制
（一）创建：create
CREATE TABLE 表名（
列名 列的类型【长度约束】,
列名 列的类型【长度约束】,
...
列名 列的类型【长度约束】）;
（二）修改：alter
alter table 表名 add/drop/modify/change 列名 【列的类型 约束】
1.修改列名：alter table 表名 change column 旧列名 新列名 列的类型
2.修改列的类型或约束：alter table 表名 modify column 列名 列的类型
3.添加新列:alter table 表名 add column 列名 列的类型
4.删除列:alter table 表名 drop column 列名
5.修改表名:alter table 表名 rename to 新表名
（二）修改：alter
alter table 表名 add/drop/modify/change 列名 【列的类型 约束】
1.修改列名：alter table 表名 change column 旧列名 新列名 列的类型
2.修改列的类型或约束：alter table 表名 modify column 列名 列的类型
3.添加新列:alter table 表名 add column 列名 列的类型
4.删除列:alter table 表名 drop column 列名
5.修改表名:alter table 表名 rename to 新表名
（三）删除：drop
drop table if exists 表名;
create table 表名;
drop database if exists 旧库名;
create database 新库名;
（四）表的复制
1.仅仅复制表结构
create table 新表名 like 旧表名;
2.复制表的结构和数据
create table 新表名 select* from 旧表名;
3.只复制部分数据
create table 新表名 select 字段名 from 旧表名 where 筛选条件;
4.只复制部分结构
create table 新表名 select 字段名 from 旧表名 where（+不满足的条件，如1=2）;
二、常见数据类型
数值型
整型：1.默认是无符号，添加unsigned
2.如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
3.如果不设置长度，会使用默认长度
4.长度代表显示的最大宽度，若要用0填充，要加zerofill约束，此时为无符号。
小数：定点数DEC(M,D),decimal(M,D)
浮点数float(M,D)（字节4）、double(M,D)(8)
特点：1.M和D ：M整数部位+D小数部位
2.M和D都可省略，decimal的M默认为10，D为0，float和double会根据插入数值的精度确认精度
3.定点型的精度较高，如果要求插入的数值精度较高，如货币运算等则考虑定点
字符型
较短的文本：char（M）（M最大字符数）（固定长度的字符）、 varchar（M）（可变长度的字符）
其他：binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本：text、blob（较长的二进制数据）
日期型
date（只保存日期）/time（只保存时间）/year（只保存年）
datetime(日期+时间)1000-9999
timestamp(日期+时间)1970-2038、受时区影响
三、常见约束
分类：
NOT NULL：非空，保证该字段的值不能为空，比如姓名、学号等
DEFAULT：默认，保证该字段有默认值，比如性别
PRIMARY KEY：主键，保证该字段的值具有唯一性，且非空
UNIQUE：唯一，保证该字段的值具有唯一性，可以为空
CHECK:检查约束【mysql不支持】
FOREIGN KEY:限制两个表的关系，用于保证该字段的值必须来自于主表关联列的值，在从表中添加外键约束，用于引用主表中某列的值。
添加约束的时机：
1.创建表时
2.修改表时
约束的添加分类：
列级约束：六大约束语法上都支持，但外键约束没有效果
表级约束：除了非空、默认，其他都支持
(一)创建表时添加约束
1.添加列级约束
直接在字段名和类型后面追加约束类型即可
只支持默认、非空、主键、唯一
2.添加表级约束
在各个字段的最后面
[CONSTRAINT 约束名] 约束类型（字段名）
3.主键和唯一的区别：
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多有一个 √ （不推荐）
唯一 √ √ 可以有多个 √（不推荐）
4.外键：
1.要求在从表设置外键关系
2.从表列的类型和主表关联列的类型要求一致或兼容，名称无要求
3.主表的关联列必须是一个key，一般是主键或唯一
4.插入数据时，先插入主表，再插入从表 删除时先删除从表
(二)修改表时添加约束
1.添加列级约束（主键、唯一、默认、非空）
ALTER TABLE 表名 MODIFY column 字段名 字段类型 约束
2.添加表级约束（添加主键、唯一）
ALTER TABLE 表名 ADD 【constraint 约束名】约束类型(字段名)
3.添加外键
ALTER TABLE 表名 ADD FOREIGN KEY(字段名) REFERENCES 主表（字段）
(三)修改表时删除约束
1.删除主键约束
alter table 表名 drop primary key(字段名)
2.删除唯一约束
alter table 表名 drop index 字段名
(四)标识列
含义：可以不用手动插入值，系统提供默认的序列值，又称为自增长列
1.创建表时设置标识列（auto_increment）
特点：
标识列不一定和主键搭配，但要求是一个key
一个表至多有一个标识列
标识列的类型必须是数值型
标识列可以通过set auto_increment_increment=3,设置步长通过手动插入值，改变起始值
2.修改表时设置标识列
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 约束 标识
3.修改表时删除标识列
ALTER TABLE表名MODIFY COLUMN 字段名 字段类型【去除auto_increment】
四、DML数据操作语言
(一)插入insert
方式一:
insert into 表名（列名,...） values(值1,...)
特点：插入值的类型要与列的类型一致
可以为null的列如何插入值？没有插入的默认为null
插入的字段顺序可以调换，但要一一对应
可以省略列名，默认所有列，而且列的顺序和表中的顺序一致
方式二:
insert into 表名 set 列名=值,列名=值
两种方式比较：方式一支持插入多行，方式二不支持
方式一支持子查询，方式二不支持
(二)修改update
1.修改单表的记录
update 表名
set 列=新值，...
where 筛选条件
2.修改多表记录
sql92语法:
update 表1 别名，表2 别名
set 列=值,...
where 连接条件
and 筛选条件
sql99语法：
update 表1 别名
inner/left/right join 表2 别名
on 连接条件
set 列=值，...
where 筛选条件
(三)删除
方式一：delete
单表：
delete from 表名 where 筛选条件
多表：
（sql92）
delete 表1的别名，表2的别名
from 表1 别名，表2 别名
where 连接条件 and 筛选条件
（sql99）
delete 表1的别名，表2的别名
from 表1 别名
inner/left/right join 表2 别名
on 连接条件
where 筛选条件
方式二:truncate（全部清空）
TRUNCATE TABLE 表名
比较：
1.delete删除可以加where条件，truncate不能加
2.truncate删除效率略高
3.如果删除的表中有自增长列，delete删除后再插入数据，自增长列的值从断点开始；truncate删除后再插入数据，自增长列的值从1开始
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚
#方式一支持子查询，方式二不支持
INSERT INTO beauty (id,name,sex,phone) VALUES(13,'hebe','女','15925169091');
INSERT INTO beauty (id,name,sex,phone)
SELECT 14,'hebe','女','15925169091';
五、分组函数
功能：用作统计使用，又称作聚合函数或统计函数、组函数
分类：sum求和、avg平均值、max最大值、min最小值、count计数
特点：1.sum、avg一般处理数值型
2.max、min、count适用于任何
3.以上分组函数都忽略null值
4.可以和distinct搭配实现去重
5.count(*)用作统计行数
6.和分组函数一同查询的字段要求是group by 后的字段
1.简单使用
SELECT sum(salary) FROM employees;
2.参数支持哪些类型
SELECT SUM(last_name),AVG(last_name) FROM employees;(没有意义)
SELECT max(last_name),min(last_name) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
3.是否忽略null
sum、avg忽略null值
4.和distinct搭配使用
SELECT sum(DISTINCT salary),sum(salary) FROM employees;
SELECT COUNT(DISTINCT salary),count(salary) FROM employees;
5.count函数的详细介绍
SELECT COUNT(salary) FROM employees;（只要有一行不为空，就统计）
SELECT COUNT(*) FROM employees;(效果一样，都是统计行数)
SELECT count(1) FROM employees;
SELECT count(2) FROM employees;
SELECT count('') FROM employees;(效果都一样)
效率 ：MYISAM存储引擎下，count(*)的效率高
INNODB存储引擎下，count(*)和count(1)的效率差不多，比count(字段)高
6.和分组函数一同查询的字段有限制
#测试：查询员工表中最大入职时间和最小入职时间的相差天数（DATEDIFF(expr1,expr2)）
SELECT DATEDIFF(max(hiredate),min(hiredate)) FROM employees;
六、分组查询
语法：
SELECT 分组函数，列（要求出现在group by 后面）
FROM 表
WHERE 筛选条件
GROUP BY 分组列表
ORDER BY字句
数据源 位置 关键字
特点：1.分组前筛选： 原始表 GROUP BY字句前面 WHERE
2.分组后筛选： 分组后的结果集 GROUP BY字句后面 HAVING
注意点 1.分组函数做条件一定是放在having字句中
2.能用分组前筛选的就优先考虑
3.GROUP BY字句支持单个字段分组、多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数（用得较少）
4.排序函数添加到最后
案例：
1.查询每个工种的最高工资
SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
2.查询每个位置上的部门个数
SELECT COUNT(*),location_id FROM departments GROUP BY location_id;
添加筛选条件
3.查询邮箱中包含a字符的每个部门的平均工资
SELECT AVG(salary) FROM employees WHERE email LIKE '%a%' GROUP BY department_id;
4.查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees WHERE (commission_pct is NOT NULL) GROUP BY manager_id;
添加复杂筛选条件
5.查询哪个部门的员工个数大于2
SELECT COUNT(*),department_id FROM employees WHERE COUNT(*) GROUP BY department_id;#错误
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;#分组后筛选
6.查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
#我的错误写法，条件全部添加在分组后：
SELECT job_id,MAX(salary)
FROM employees
GROUP BY job_id
HAVING (SELECT commission_pct FROM employees) IS NOT NULL and MAX(salary)>12000;
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct is NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
7.查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
按表达式或函数分组
8.按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
SELECT COUNT(*) s,LENGTH(last_name) len
FROM employees
GROUP BY len
HAVING s>5;
按多个字段分组
9.查询每个部门、每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;
添加排序
10.查询每个部门、每个工种的员工的平均工资，并且按平均工资的高低显示
SELECT AVG(salary) 平均工资,department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY 平均工资;
添加条件
SELECT AVG( salary ) 平均工资,department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING 平均工资 > 10000
ORDER BY 平均工资;
11.查询员工最高工资和最低工资的差距
SELECT MAX(salary)-MIN(salary) difference FROM employees;
七、连接查询
-
含义：又称多表查询，
笛卡尔乘积现象：表一有n行，表二有m行，结果有n*m行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件 -
分类：
按年代分类：sql92标准：仅支持内连接
sql99标准（推荐）：支持内连接+外连接（左外和右外）+交叉连接
按功能分类：
内连接：等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
(一)sql92标准（内连接）
1.等值连接
多表等值连接的结果为多表的交集部分
n表连接，至少需要n-1个连接条件
多表的顺序没有要求
一般需要为表起别名
可以搭配所有字句使用：如排序、分组、筛选
如：查询员工名和对应的部门名
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE
employees.department_id = departments.department_id;
为表起别名
#(提高语句简洁度、区分多个重名的字段)，如果起了别名，就不能使用原表名去限定
如：查询员工名、工种号、工种名
SELECT
last_name,
e.job_id,
job_title
FROM
employees AS e,
jobs AS j
WHERE
e.job_id = j.job_id;
加筛选
(1)查询有奖金的员工名和部门名
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE
employees.department_id = departments.department_id
AND commission_pct IS NOT NULL;
(2)查询城市名中第二个字符为o的部门名和城市名
SELECT
department_name,
city
FROM
departments,
locations
WHERE
departments.location_id = locations.location_id
AND city LIKE '_o%';
加分组
（1）查询每个城市的部门个数
SELECT
COUNT( * ) 个数,
city
FROM
departments,
locations
WHERE
departments.location_id = locations.location_id
GROUP BY
city;
（2）查询有奖金的每个部门的部门名和部门的领导编号以及该部门的最低工资
SELECT
department_name,
d.manager_id,
MIN( salary )
FROM
employees e,
departments d
WHERE
e.department_id = d.department_id
AND commission_pct IS NOT NULL
GROUP BY
e.department_id;
加排序
（1）查询每个工种的工种名和员工的个数，并按员工个数降序
SELECT job_title,count(e.employee_id) 员工个数
FROM jobs j,employees e
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY count(*) DESC;
实现三表连接
#查询员工名、部门名和所在的城市
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.非等值连接
案例：
（1）查询员工的工资和工资级别
#创建表：
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);
INSERT INTO job_grades VALUES ('A',1000,2999);
INSERT INTO job_grades VALUES ('B',3000,5999);
INSERT INTO job_grades VALUES ('C',6000,9999);
INSERT INTO job_grades VALUES ('D',10000,14999);
INSERT INTO job_grades VALUES ('E',15000,24999);
INSERT INTO job_grades VALUES ('F',25000,40000);
SELECT
salary,
grade_level
FROM
employees e,
job_grades j
WHERE
salary BETWEEN j.lowest_sal
AND highest_sal;
3.自连接
案例：查询员工名以及其上级名字
SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
FROM employees e,employees m;
WHERE e.manager_id=m.employee_id;
(二)sql99语法
语法：
SELECT 查询列表
FROM 表1 别名【连接类型】
JOIN 表2 别名
on 连接条件
【WHERE 筛选条件】
【GROUP BY 分组】
【HAVING 筛选条件】
【ORDER BY 排序列表】
分类：
内连接（inner）
外连接:
左外：left【outer】
右外：right【outer】
全外：full【outer】
交叉连接：cross
1.内连接
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
on 连接条件
- 分类：等值
非等值
自连接 - 特点：
可添加排序、分组、筛选
inner可以省略
筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
inner join连接和aql92语法中的等值连接效果一样，都是查询多表的交集
等值连接
#案例
（1）查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;
(2)添加筛选：查询名字中包含e的员工名和工种名
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE last_name LIKE '%e%';
(3）添加分组和筛选：查询部门个数>3的城市名和部门个数
SELECT city,COUNT(*) 部门个数
FROM locations l
INNER JOIN departments d
ON l.location_id=d.location_id
GROUP BY city
HAVING COUNT(*)>3;
（4）添加排序：查询部门员工个数>3的部门名和员工个数，并按个数降序
SELECT department_name,COUNT(*) 员工个数
FROM departments d
INNER JOIN employees e
ON e.department_id=d.department_id
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
（5）查询员工名、部门名、工种名，并按部门降序
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id=d.department_id
INNER JOIN jobs j ON e.job_id=j.job_id
ORDER BY department_name DESC;
非等值连接
(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))查询每个工资级别的个数>20，并且按工资级别降序(添加筛选)
SELECT grade_level,COUNT(*) 个数
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING 个数>20
ORDER BY grade_level DESC;
自连接
#查询员工的名字以及上级名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id;
2.外连接
-
查询一个表有，而另一个表没有
-
特点：
1.外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值； 如果没有和它匹配的，则显示null。外连接查询结果=内连接查询结果+主表中有而从表中没有的记录.
2.左外连接:left join左边的是主表；右外连接，right join右边的是主表
3.左外和右外交换两个表的顺序，可以实现同样的效果。全外连接=内连接结果+表1有而表2没有+表2有而表1没有案例：
（1）查询哪个部门没有员工#左外：
SELECT d.*,e.employee_id FROM departments d LEFT 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 JOIN departments d ON d.department_id=e.department_id WHERE e.employee_id is NULL;
3.全外
4.交叉连接
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
测试
（1）查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有则用null填充
SELECT b.id,b.`name`,bo.*
FROM beauty b
LEFT JOIN boys bo
ON bo.id=b.boyfriend_id
WHERE b.id>3;
（2）查询哪个城市没有部门
SELECT l.city,d.department_id
FROM locations l
LEFT JOIN departments d
ON l.location_id=d.location_id
AND department_id is NULL;
#(3)查询部门名为SAL或IT的员工信息
SELECT e.*,d.department_name
FROM departments d
LEFT JOIN employees e
ON d.department_id=e.department_id
WHERE department_name='SAL' OR department_name='IT';
八、分页查询
- 应用场景：当要显示的数据一页显示不全，需要分页提交sql请求
- 语法：
SELECT 查询列表
from 表
【连接类型 join 表2
on 连接条件
where 筛选条件
GROUP BY 分组字段
having 分组后筛选
ORDER BY 排序字段】
limit offset，size；
offset显示条目的起始索引（起始索引从0开始）
size为要显示的条目个数
-
特点：
limit放在查询语句的最后（语法及执行均在最后）
公式：page要显示的页数
size要显示的条目数SELECT 查询列表 FROM 表 limit （page-1）*size,size；
如size=10：
page offset
1 0
2 10
3 20
- 案例：
（1）显示前五条员工信息
SELECT* from employees LIMIT 0,5;
SELECT* from employees LIMIT 5;(从起始开始，可省略0)
（2）显示第11到25
SELECT* from employees LIMIT 10,15;
（3）
SELECT* FROM employees
WHERE commission_pct is NOT NULL
ORDER BY salary DESC
LIMIT 10;
九、联合查询
合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
...
应用场景：
要查询的结果来自多个表且多个表没有直接的连接关系，但查询的信息一致时
注意事项：
要求多条查询语句的查询列数一致
要求多条查询语句查询的每一列的类型和顺序最好一致
使用union关键字默认去重，union all可以包含重复项
十、子查询
含义：出现在其他语句中的select语句称为子查询或内查询；外部的查询语句，称为主查询或外查询
分类：
① 按子查询出现的位置：
select后面
（仅仅支持标量子查询）
from后面
（支持表子查询）
where或having后面
（标量子查询、列子查询、行子查询）
exists后面
（相关子查询）
②按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集为多行多列）
一、where或having后面
特点：标量子查询一般搭配着单行操作符使用> < ＜＞ ＞＝ ＜＝
列子查询一般搭配多行操作符使用 IN/SOME/ALL
子查询的执行优于主查询
1.标量子查询
案例
(1).谁的工资比Abel高？
SELECT last_name
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)
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(SELECT MIN(salary) FROM employees);
(4)查询最低工资大于50号部门的最低工资的部门编号及最低工资
SELECT department_id,MIN(salary) 最低工资
FROM employees
GROUP BY department_id
HAVING 最低工资>(SELECT MIN(salary) FROM employees WHERE department_id=50);
非法使用标量子查询
2.列子查询
（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));
（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';
3.行子查询（结果有一行多列或多列多行）
（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);
二、select后面(仅仅支持标量子查询)
案例：
（1）查询每个部门的员工人数
SELECT d.*,(SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.department_id) 人数
FROM departments d;
（2）查询员工号等于102的部门名
SELECT department_name,employee_id
FROM employees e,departments d
WHERE employee_id=102
AND e.department_id=d.department_id;
SELECT(
SELECT department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
WHERE e.employee_id=102
) 部门名;
三、FROM后面
案例：查询每个部门的平均工资的工资等级
①SELECT AVG(salary),department_id FROM employees GROUP BY department_id
②SELECT* FROM job_grades
SELECT ag_dep.*,g.grade_level
FROM (SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
四、exist后面（相关子查询）
语法：exist（完整的查询语句），结果为0或1
案例：(1)查询有员工名的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(SELECT* FROM employees e WHERE e.department_id=d.department_id);
SELECT department_name
FROM departments d
WHERE d.department_id IN (SELECT department_id FROM employees);
(2)查询没有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN (SELECT boyfriend_id
FROM beauty);
#EXISTS
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS (SELECT boyfriend_id
FROM beauty b
WHERE b.boyfriend_id=bo.id);
测试
（1）
SELECT last_name,salary
FROM employees e
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)查询各部门中工资比本部门平均工资高的员工的员工号和姓名(FROM后子查询)
SELECT AVG(salary),department_id FROM employees GROUP BY department_id;
SELECT e.employee_id,e.last_name
FROM employees e
INNER JOIN (SELECT AVG(salary) 部门平均工资,department_id
FROM employees
GROUP BY department_id) ag_dep
ON e.department_id=ag_dep.department_id
WHERE e.salary>部门平均工资;
（4）
SELECT employee_id,last_name
FROM employees
WHERE department_id in (SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%');
(5)
SELECT employee_id
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
WHERE d.location_id=1700;
(6)查询管理者是king的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id in (SELECT employee_id FROM employees WHERE last_name='k_ing');
(7)
SELECT CONCAT(last_name,'.',first_name)
FROM employees
WHERE salary=(SELECT MAX(salary) FROM employees);
十一、存储过程
存储过程和函数：类似于java中的方法
好处：提高代码的重用性
简化操作
减少了编译次数和数据库的连接次数，提高了效率
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
(一)创建
CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
存储过程体（一组合法的SQL语句）
END
注意：
1、参数列表包含三部分：参数模式 参数名 参数类型
如：in stuname VARCHAR(20)
- 参数模式
in:该参数可以作为输入，也就是该方法需要调用方法传入值
out：该参数可以作为输出，也就是可以作为返回值
inout：该参数既可以作为输入，也可以作为输出，也就是既需要传入值 ，也需要返回值
2、如果存储过程体仅仅只有一句话，BEGIN END 可以省略
存储过程体中每条SQL语句结尾必须加分号
存储过程结尾可以使用delimiter重新设置：delimiter 结束标记
(二)调用
CALL 存储过程名（实参列表）;
1.空参列表的存储过程
案例：插入
创建带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`=bo.boyName
END $
CALL myp2('小昭')
(2)创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result VARCHAR(20) DEFAULT '';#声明并初始化
SELECT COUNT(*) INTO result#赋值
FROM admin
WHERE admin.username=username
AND admin.`password`=PASSWORD;
SELECT if(result>0,'成功','失败');#使用
#SELECT result;
END $
#调用
CALL myp4('张飞'，8888)
创建带out模式的存储过程
(1)根据女神名，返回对应的男神名
CREATE PROCEDURE myp5(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 $
#调用
#SET @bName$ #定义用户变量
call myp5('小昭',@bName)$
SELECT @bName$
(2)根据女神名，返回对应的男神名和男神魅力值
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
SELECT bo.boyName,bo.userCP INTO boyName,userCP
FROM boys bo
INNER JOIN beauty b ON bo.id=b.boyfriend_id
WHERE b.`name`=beautyName
END
#调用
call myp6('小昭',@bName,@usercp)$
SELECT @bName,@usercp$
创建带inout模式的存储过程
(1)传入a与b两个值，最终a和b都翻翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGIN
SET a=a*2;#局部变量赋值
SET b=b*2;
END $
#定义用户变量
SET @m=10$
SET @n=20$
#调用
CALL myp8(@m,@n)$
SELECT @m,@n$
#习题
1.创建存储过程实现传入用户名和密码，插入到admin中（用户登录同步至数据库）
CREATE PRECISION test(IN username VARCHAR(20),IN loginpwd VARCHAR(20))
BEGIN
INSERT INTO admin(admin.username,PASSWORD) VALUES(username,loginpwd);
END $
CALL test('admin','0000')$
2.创建存储过程实现传入女神编号，返回女神名称和女神电话
CREATE PROCEDURE test_2(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))
BEGIN
SELECT b.`name`,b.phone INTO id,phone
FROM beauty b
WHERE b.id=id;
END $
CALL test_2(1,@m,@p)$
SELECT @m,@p
3.创建存储过程或函数实现传入两个女神生日，返回大小
CREATE PROCEDURE test_p3(IN birth1 datetime,IN birth2 datetime,OUT result INT);
BEGIN
SELECT DATEDIFF(birth1,birth2) INTO result;
END $
CALL test_p3('1998-1-1',now(),@result)$
SELECT @result$#查看
删除存储过程
语法： DROP PROCEDURE 存储过程名;
查看存储过程的信息
show CREATE PROCEDURE 存储过程名;
十二、函数
区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且只有一个返回，适合做处理数据后返回一个结果
(一)创建
CREATE FUNCTION 函数名（参数列表）RETURNS 返回类型
BEGIN
函数体
END
注意：
1.参数列表包含两部分 参数名+参数类型
2.函数体：肯定会有return语句，如果没有会报错
3.函数体中仅有一句话，可以省略begin end
4.delimiter语句设置结束标记
(二)调用
SELECT 函数名（参数列表）
案例
1.无参有返回：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义（局部）变量
SELECT count(*) INTO c#赋值
FROM employees;
RETURN c;
END $
SELECT myf1()$
2.有参有返回：根据员工名返回工资
CREATE FUNCTION myf2( empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0;#定义用户变量
SELECT salary INTO @sal
FROM employees
WHERE last_name=empName;
RETURN @sal;
END $
SELECT myf2('Kochhar')$
根据部门名返回该部门的平均工资
DROP FUNCTION myf3$
CREATE FUNCTION myf3(depName VARCHAR(20)) RETURNS double
BEGIN
SET @avgSal=0;
SELECT AVG(salary) INTO @avgSal
FROM employees e
INNER JOIN departments d ON e.department_id=d.department_id
WHERE department_name=depName;
RETURN @avgSal;
END $
SELECT myf3('IT')$
(三)查看函数
SHOW CREATE FUNCTION myf3;
(四)删除函数
DROP FUNCTION myf3$
#案例
1.创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE sum FLOAT DEFAULT 0;#定义局部变量
SET sum=num1+num2;
return sum;
END $
SELECT test_fun1(1,2)$
十三、流程控制结构
分类：
顺序结构：程序从上往下以此执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码
(一)分支结构
-
if函数
功能：实现简单的双分支
语法：IF(表达式1，表达式2，表达式3)
执行顺序：如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值
应用：任何地方 -
case结构
情况一：类似于java中的switch语句，一般用于实现的等值判断
语法：CASE 变量/表达式/字段 WHEN 要判断的值 THEN 返回的值1 when 要判断的值 then 返回的值2 ... ELSE 要返回的值n或语句n END case;情况二：类似于java中的多重if语句，一般用于实现区间判断
CASE WHEN 要判断的条件1 THEN 返回的值1或语句1 when 要判断的条件2 then 返回的值2或语句2 ... ELSE 要返回的值n END特点：
1.可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，BEGIN END 中或begin end的外面
2.可以作为独立的语句去使用，只能放在begin end中
3.如果when中的值满足或条件成立，则执行then后面的语句，并且结束case，如果都不满足，则执行else中的语句或值
4.else可以省略，如果else省略了，并且所有的when条件都不满足 ，则返回null案例：创建存储过程，根据传入的成绩来显示等级
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(98)$ -
if结构
功能：实现多重分支
语法：IF 条件1 THEN 语句1; ELSEIF 条件2 THEN 语句2； ... 【else 语句n；】应用：应用在begin end中
案例：根据传入的成绩来返回等级
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
IF score>=90 AND score<=100 then RETURN 'A';
IF score>=80 then RETURN 'B';
if score>=60 THEN RETURN 'C';
END IF;
END$
SELECT test_if(89)$
(二)循环结构
分类：
while、loop、repeat
循环控制：
iterate类似于continue，结束本次循环，继续下一次
leave类似于break，跳出，结束当前所在的循环
-
while
语法：【标签：】WHILE 循环条件 do 循环体 END WHILE 【标签】； 联想java： while（循环条件）{ 循环体； } -
LOOP
语法：【标签：】loop 循环体； END LOOP 【标签】； #可以用来模拟简单的死循环 -
repeat
语法：【标签：】repeat 循环体 until 结束循环的条件 end repeat【标签】；
案例：
1.没有添加循环控制语句：批量插入，根据次数插入到admin表中的多条记录
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('rose',i),'666');
SET i=i+1;
END WHILE;
END $
CALL pro_while1(10)$
2.添加leave语句：批量插入，根据次数插入到admin表中的多条记录（如果次数大于20则停止）
TRUNCATE TABLE admin$
create PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
iNSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL test_while1(100)$
3.添加iterate语句：根据次数插入到admin表中的多条记录，只插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=insertCount DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
END WHILE a;
END $
CALL test_while1(100)
十四、视图
含义：虚拟表，和普通表一样使用
(一)创建视图
create view 视图名
as
查询语句
use myemployees
#创建
CREATE VIEW v1
as
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;
#使用
select* FROM v1
WHERE last_name LIKE '%a%';
(二)视图的修改
方式一：
CREATE OR REPLACE VIEW 视图名
AS
查询语句
方式二：
ALTER VIEW 视图名
AS
查询语句
(三)删除视图
DROP VIEW 视图名,视图名;
(四)查看视图
DESC 视图名;
(五)视图的更新
十五、事务控制语言TCL
(TRANSACTION control LANGUAGE)
**含义：**一个或一组sql语句组成的一个执行单元，这个单元要么全部执行，要么都不执行
事务的创建：
隐式事务：事务没有明显的开始、结束标志，如insert、delete、update
显式事务：事务有明显的开始、结束标志
事务必须设置自动提交功能为禁用
步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务的sql语句(select,insert,update,delete)
语句1；
语句2；
…
步骤3：结束事务
commit;提交事务
rollback;回滚事务
事务的隔离级别（见笔记）
查看引擎：
SHOW ENGINES
1714
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
