0 引用

0.1 SQL语句的分类

DQL（Data Query Language）数据查询语言：select
DML（Data Manipulate Language）数据操作语言：insert 、update、delete
DDL（Data Define Languge）数据定义语言：create、drop、alter
TCL（Transaction Control Language）事务控制语言：commit、rollback

0.2 例题中用到的表

1 DQL

1.1 基础查询

查看指定数据库下指定表中的结构：
USE 数据库名
SELECT 查询列表FROM 表名;
①通过select查询完的结果是一个虚拟的表格，不是真实存在
②查询列表（要查询的东西）可以是：表中的常量值、表达式、字段、函数
1.查询表中的单个字段：SELECT last_name FROM employees;
2.查询表中的多个字段：依次双击要查询的字段（会自动加着重号``用来区分字段和关键字），不同字段之间用逗号隔开（末尾不加逗号）
3.查询表中的所有字段：SELECT * FROM employees; （顺序和原始表一样）
4.查询常量值：SELECT ‘john’;
5.查询表达式：SELECT 100%98;
6.查询函数：SELECT VERSION();

1.1.1 为字段起别名AS

① 便于理解；②如果要查询的字段有重名的情况，使用别名可以区分开来
方式一：使用AS：字段名 AS 别名
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
方式二：使用空格（省略AS）：字段名别名
SELECT last_name 姓,first_name 名 FROM employees;
#特例:别名中有特殊符号，需要使用单’’或双引号””把别名包起来
SELECT salary AS “out put” FROM employees;#案例：查询salary，显示结果为out put

1.1.2 去重DISTINCT

去重：在字段名的前面加上关键字DISTINCT
#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;

1.1.3 +号的作用

（1）java中的+号：
①运算符，两个操作数都为数值型
②连接符，只要有一个操作数为字符串
（2）mysql中的+号：
仅仅只有一个功能：运算符
select 100+90; #两个操作数都为数值型，则做加法运算
select ‘123’+90;#只要其中一方为字符型，试图将字符型数值转换成数值型。如果转换成功，则继续做加法运算；
select ‘john’+90;#如果转换失败，则将字符型数值转换成0。
select null+10;#只要其中一方为null，则结果肯定为null

1.1.4 拼接CONCAT(str1,str2,…)

案例：查询员工名和姓连接成一个字段，并显示为姓名
SELECT CONCAT(last_name,first_name) AS 姓名FROM employees;
#案例：显示出表employees的两列，各个列之间用逗号连接，列头显示成OUT
SELECT CONCAT(employee_id, ‘,’, first_name) AS OUT FROM employees;
#特例：在拼接的字段commission_pct中部分值存在NULL，则这部分拼接结果是NULL
SELECT CONCAT(id, ‘,’, commission_pct) AS OUT_PUT FROM employees;

1.1.5 IFNULL(expr1,expr2)

解决NULL：使用IFNULL(expr1,expr2)分情况讨论，字段expr1不为NULL的部分显示原来值，为NULL部分显示expr2。
SELECT IFNULL(commission_pct,0) AS 奖金率, commission_pct FROM employees;
SELECT CONCAT(id, ‘,’, IFNULL(commission_pct,0)) AS OUT FROM employees;
#查询员工号176的员工的年薪：
SELECT salary12(1+ IFNULL(commission_pct,0)) AS 年薪FROM employees;

1.2 条件查询

条件查询：根据条件过滤原始表的数据，查询到想要的数据（当筛选条件成立时）。
SELECT 查询列表 FROM 表名
WHERE 筛选条件;
注：WHERE一定放在FROM后面

1.2.1 筛选条件

根据筛选条件的不同分类：
一、条件运算符表达式
> < >= <= = !=或<>
示例：salary>10000
二、逻辑运算符表达式：用于连接条件表达式
&&和and：如果连接的条件同时成立，结果为true，否则为false
||或or：只要有一个条件成立，结果为true，否则为false
!或not：如果连接的条件本身为false，结果为true，反之为false
示例：salary>10000 && salary<20000
三、模糊查询
1.like：一般和通配符搭配使用。
案例：查询员工名中第三个字符为n，第五个字符为l：name like ‘__n_l%’
2.between and 和 not between and
between A and B：A和B之间，包含临界值，等价于>=A && <=B，
not between A and B：不在A和B之间，不包含临界值，等价于B，
A和B的类型要一致或兼容，且不能调换顺序。
案例：查询员工编号在100到120之间：employee_id BETWEEN 100 AND 120;
3.in
判断某字段的值是否属于in列表中的某一项，等价于=。
①in列表的值类型必须一致或兼容。②in列表中不支持通配符
案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES之一的员工：job_id IN( ‘IT_PROT’ ,’AD_VP’,’AD_PRES’);
4.is null和is not null
=或<>不能用于判断null值
is null或is not null 仅仅可以判断null值
5.<=>等价于等于，既可以判断null值，还可以判断普通类型的值

1.2.2 通配符

% ：任意多个字符,包含0个字符
_ ：任意单个字符
注：字符型的值必须要用’ ’引起来。

1.2.3 转义符

\或A ESCAPE ‘A’ 其中A为任意字符
作用：使得通配符可以当做普通的字符来使用。
案例：查询员工名中第二个字符为的员工名：name LIKE ‘$_%’ ESCAPE ‘$’

1.3 排序查询

1.3.1 ORDER BY

SELECT 查询列表 FROM 表名 WHERE 筛选条件
ORDER BY 排序列表【ASC| DESC】;
1、asc代表升序，可以省略；desc代表降序；如果都不写默认是升序
2、order by子句可以支持单个字段、多个字段、表达式、函数、别名（用逗号隔开）
3、order by子句一般放在查询语句的最后面，除了limit子句
#案例：按年薪降序查询员工信息和年薪：
SELECT ,salary12(1+IFNULL(commission_pct,0)) AS 年薪FROM employees
ORDER BY salary12(1+IFNULL(commission_pct,0)) DESC; #按表达式排序
或ORDER BY 年薪 ASC; #按别名排序
#案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT FROM employees
ORDER BY salary DESC,employee_id ASC;#按多个字段排序:逗号隔开

1.3.2 LENGTH(str) 长度函数

案例：查询员工名，并且按名字的长度降序
SELECT LENGTH(last_name) AS 字节长度,last_name FROM employees
ORDER BY LENGTH(last_name) DESC; #按函数排序
注：按名字长度即字节长度：length(name)
按名字即名字的字母顺序：name

1.4 常见函数

调用函数并将返回值显示出来：select 函数名(实参列表);
注：实参列表用到表中的字段时，在后面加上【from 表】;

1.4.1 单行函数

一、字符函数（参数的类型为字符类型）
1.concat(str1,str2,…) 拼接
2.substr/substring(str,pos) 截取从指定索引处及其后面的所有字符
2.substr/substring(str,pos,len) 截取从指定索引处及其后面的指定字符长度的字符串
注：sql中索引从1开始
3.upper(str) 转换成大写
3.lower(str) 转换成小写
4.trim(str) 去前后的空格
4.trim(remstr FROM str) 去前后指定的字符remstr
4.ltrim去左边空格
4.rtrim去右边空格
5.replace(str,from,to) 用指定字符替换字符中所有的指定字符
6.lpad(str,len,padstr) 用指定字符左填充得到指定总长度字符，原字符长度超过截断
6.rpad(str,len,padstr) 用指定字符右填充得到指定总长度字符，原字符长度超过截断
7.instr(str,substr) 返回子串第一次出现的索引，如果找不到返回0
8.length(str) 获取字节个数（utf8字符集一个字母一个字节，一个汉字三个字节）
9.ifnull(expr1,expr2) 字段1不为NULL的部分显示本身；为NULL部分显示2
#案例：查询所有员工邮箱的用户名（邮箱john@126.com，@前面的字符）
select substr(email,1,instr(email,’@’)-1) from employees;
二、数学函数（参数的类型为数字类型）
1.round(X) 四舍五入为整数
1.round(X,D) 四舍五入小数点后保留D位
2.rand 随机数
3.floor(X) 向下取整，返回<=该参数的最大整数
3.ceil(X) 向上取整，返回>=该参数的最小整数
4.mod(m,n) 取余，结果的正负和被除数的正负相同
5.truncate(X,D) 截断，小数点后保留D位，长度不够补0

三、日期函数（参数的类型为日期类型）
1.now() 返回当前系统日期时间
1.curdate() 当前系统日期，不包含时间
1.curtime() 当前系统时间，不包含日期
2.获取指定的部分，年、月、日、小时、分钟、秒
year(data) month(data) monthname(data) day hour minute second
3.str_to_date(str,format) 将日期格式的字符类型转换成指定格式的日期类型
STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’) #1999-09-13
案例：输入4-3 1992，查询入职日期为1992-4-3的员工信息
SELECT FROM employees
WHERE hiredate = STR_TO_DATE(‘4-3 1992’,’%c-%d %Y’);
3.date_format(date,format) 将日期转换成指定格式的字符
DATE_FORMAT(‘2018/6/6’,’%Y年%m月%d日’) #2018年06月06日
案例：查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,’%m月/%d日 %y年’) AS 入职日期
FROM employees WHERE commission_pct IS NOT NULL;
4.datediff(expr1,expr2) 两个日期相差天数
四、流程控制函数
1.if(expr1,expr2,expr3) 处理双分支（if else、三元运算符的效果）
其中expr1为条件表达式，条件成立返回expr2不成立返回expr3的值
2.case控制结构
（1）使用1：处理等值判断（switch case的效果）
java中：
switch(变量或表达式){
case 常量1：语句1;break;
…
default:语句n;break;
}
mysql中：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;（注：值不用加;）
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end
注：作为表达式和select搭配是，只能是要显示的值。
（2）使用2：处理条件判断（多重if的效果）
java中：
if(条件1){
语句1；
}else if(条件2){
语句2；
}
…
else{
语句n;
}
*mysql中：
case
when 条件1 then 要显示的值1或语句1; （注：值不用加;）
when 条件2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end
五、其他函数
1.version() 当前版本号
2.database() 当前库
3.user() 当前连接用户

1.4.2 分组函数(聚合函数)

用作统计使用，又称为聚合函数或统计函数或组函数

1.sum([distinct] expr) 求和

2.max([distinct] expr) 最大值

3.min([distinct] expr) 最小值

4.avg([distinct] expr) 平均值

5.count([distinct] expr) 计数

特点：
1、以上五个分组函数都只计算非null值，忽略null值；除了count(*)、count(常量值)
2、参数类型：

• sum和avg一般用于处理数值型
• max、min、count可以处理任何数据类型

3、都可以和distinct搭配使用，用于统计去重后的结果
4、count的参数可以支持：字段、*、常量值（一般设置为1）

• count(*)、count(常量值) 统计表的总行数/总个数/所有条数，包含null值
• count(字段) 统计表的总行数/总个数/所有条数，不计null值

1. 和分组函数一同查询的字段有限制：group by后的字段
   
   

   1.5 分组查询

   1.5.1 GROUP BY + HAVING

   语法
   SELECT 查询列表,分组函数(聚合函数)
   FROM 表名
   WHERE 分组前筛选条件
   GROUP BY 分组的列表
   HAVING 分组后筛选条件
   ORDER BY 排序列表【ASC| DESC】;
   注意
   1、使用group by子句将表中的数据分成若干组
   2、和分组函数一同查询的查询列表必须是group by子句中出现的字段。
   3、分组查询中的筛选分为两类：分组前筛选和分组后筛选

①分组函数做条件，肯定放在having子句中，属于分组后筛选
②能用分组前筛选的，优先考虑使用分组前筛选
4、group by子句支持单个字段、多个字段、表达式或函数分组（用逗号隔开）
5、group by子句、having子句都支持别名，where子句不支持别名
6、也可以添加排序，放在整个查询最后
7、出现在 select 后面的字段，要么是分组函数，要么是 group by 的
8、group by 一般和分组函数一起使用才有意义，比如 count、sum、avg等，单独查原始表中非 group by 的字段是没有意义的
题目：

• 有选课表course_relation(student_id, course_id)，其中student_id表示学号，course_id表示课程编号，获取每个学生所选课程的个数信息。

select student_id, count(course_id) from course_relation group by student_id;

• 查询每个位置的部门个数。

select count(department_id) from departments group by location_id;
或
select count(*), location_id from departments group by location_id;

• 查询每个领导手下的有奖金的员工的平均工资

select manager_id, avg(salary) from employees
where commission_pct is not null
group by manager_id ;

• 查询哪个部门的员工个数>5

select department_id, count() from employees group by department_id having count()>5;
①查询每个部门的员工个数
select department_id, count() from employees group by department_id;
②根据①结果筛选
select department_id, count() from employees group by department_id having count(*)>5;

• 每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

select job_id,max(salary) from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;

1.6 连接查询

1.含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
2.笛卡尔乘积现象：表1 有m行，表2有n行，结果=mn行。发生原因：没有有效的连接条件。如何避免：添加有效的连接条件（表名.字段名）。
3. 连接条件的分类：
①按年代分类：
sql92标准：仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
②按功能分类：
内连接（交集）：等值连接非等值连接自连接
外连接（不交集）：左外连接右外连接全外连接
交叉连接
*4.总结：

1.6.1 sql92：等值连接

sql92：
SELECT 各个表的查询列表（逗号隔开）,分组函数
FROM 各个表名（多表之间用逗号隔开）
WHERE 连接条件AND 连接条件AND 筛选条件
GROUP BY 分组的列表
HAVING 分组后的筛选条件
ORDER BY 排序列表【ASC| DESC】;
特点：
1.多表等值连接的结果为多表的交集部分：连接条件之间用and
2.n表连接，至少需要n-1个连接条件
3.多表的顺序没有要求，可以调换
4.一般需要为表起别名：
①反复用到表名来限定时，为表起别名（AS可省略）可以提高语句的简洁度
②区分多个重名的字段
③如果为表起了别名，则查询的字段就不能使用原来的表名去限定
5.可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

1.6.2 sql92：非等值连接

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

1.6.3 sql92：自连接

案例：查询员工名和直接上级的名称（把一张表看作两张表）
SELECT e.last_name 员工名,m.last_name 上级名称
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;#上级编号=上级的员工号

1.6.4 sql99：内连接

sql99：#通过join关键字实现连接
SELECT 各个表的查询列表（逗号隔开）,分组函数
FROM 表1 别名
【连接类型】JOIN 表2 别名ON 连接条件1
【连接类型】JOIN 表3 别名ON 连接条件2（多表之间用join on连接条件）
WHERE筛选条件AND 筛选条件
GROUP BY 分组的列表
HAVING 分组后的筛选条件
ORDER BY 排序列表【ASC| DESC】;
其中连接类型：
内连接：inner
外连接：左外：left 【outer】 右外：right 【outer】 全外：full【outer】
交叉连接：cross
内连接：
1.可以添加排序、分组、筛选
2.inner可以省略
3.筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
4.inner join连接和sql92语法中的连接效果是一样的
（1）等值连接
#查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
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;
（2）非等值连接
#查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(),grade_level
FROM employees e
INNER JOIN job_grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT()>20
ORDER BY grade_level DESC;
（3）内连接
#查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m ON e.manager_id= m.employee_id
WHERE e.last_name LIKE ‘%k%’;

1.6.5 sql99：外连接

1.外连接用于查询一个表中有，另一个表没有的记录。
2.外连接分为主从表，它的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接的查询结果=内连接结果+主表中有而从表没有的记录
3.左外连接，left join左边的是主表
右外连接，right join右边的是主表
4.左外和右外更换连接类型及两个表的顺序，就可以实现同样的效果
5.全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
（1）左外连接
#案例：查询哪个部门没有员工
SELECT d.department_id, d.department_name
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
WHERE e.department_id IS NULL;
（2）右外连接
SELECT d.department_id,d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id IS NULL

1.6.6 sql99：交叉连接

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行。

1.7 子查询

1.含义：出现在其他语句（增删改查语句）中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询
2.分类（其他语句以select查询语句为例）：
按子查询出现的位置：
select后面：仅仅支持标量子查询
from后面：支持表子查询
where或having后面：标量子查询、列子查询、行子查询
exists后面（相关子查询）：表子查询
按子查询结果集的行列数：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
3.特点：
①子查询都放在小括号内
②子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
③子查询优先于主查询执行，主查询使用了子查询的执行结果
④子查询根据查询结果的行数不同分为以下两类：

1.7.1 where或having后面

特点：
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
4.子查询不带封号; 分步写更容易
一、标量子查询
标量子查询，一般搭配着单行比较操作符> < >= <= = <>使用。
#案例：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary FROM employees
WHERE salary=(SELECT MIN(salary) FROM employees);
#案例：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(salary),department_id FROM employees
GROUP BY department_id
HAVING MIN(salary)>(SELECT MIN(salary) FROM employees
WHERE department_id = 50);
二、列子查询
列子查询，一般搭配着单行比较操作符> < >= <= = <>和多行比较操作符in、notin、any/some、all使用。
IN/NOT IN(子查询返回的列表)：等于列表中的任意一个
ANY|SOME(子查询返回的列表)：和子查询返回的某一个值比较
ALL(子查询返回的列表)：和子查询返回的所有值比较
注：IN和= ANY都表示等于里面任意一个
NOT TN和!= ALL或<>ALL都表示不等于里面任意一个
#案例：返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id FROM departments #去重：想要的仅仅是一个值
WHERE location_id IN(1400,1700));
#案例：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary FROM employees
WHERE salary#或salary<(SELECT MAX(salary) # <(①)的任意一个，即＜最大的
WHERE job_id = ‘IT_PROG’
) AND job_id<>’IT_PROG’;
二、行子查询
#案例：查询员工编号最小并且工资最高的员工信息
#③查询员工信息
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);

1.7.2 select后面

案例：查询员工号=102的部门名
SELECT (
SELECT department_name,e.department_id FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) 部门名;

1.7.3 from后面

将子查询结果充当一张表，要求必须起别名。
#案例：查询每个部门的平均工资的工资等级
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;
#②连接①的结果集和job_grades表
#筛选条件平均工资 between lowest_sal and highest_sal
#案例：查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT last_name,employee_id,salary
FROM employees
JOIN (SELECT AVG(salary) avgs,department_id FROM employees GROUP BY department_id) e #①查询每个各部门的平均工资
ON employees.department_id=e.department_id #②对应部门员工的工资：连接①结果集和employees表，进行筛选
WHERE salary>avgs

1.7.4 exists、not exists后面

exists(完整的查询语句)
结果：1或0，表示( )中的查询语句有没有值。
#案例：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id=e.department_id);
#用in实现
SELECT department_name
FROM departments d
WHERE d.department_id IN(
SELECT department_id
FROM employees)
#案例2：查询没有女朋友的男神信息
#not 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 bo.id=b.boyfriend_id

);

1.8 分页查询

SELECT 各个表的查询列表（逗号隔开）,分组函数
FROM 表1 别名
【连接类型】 JOIN 表2 别名 ON 连接条件1
【连接类型】 JOIN 表3 别名 ON 连接条件2
WHERE 筛选条件 AND 筛选条件
GROUP BY 分组的列表
HAVING 分组后的筛选条件
ORDER BY 排序列表【ASC| DESC】;
LIMIT 【OFFSET,】SIZE;
# offset要显示条目的起始索引（起始索引从0开始）；size 要显示的条目个数。
1.应用：实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
当要显示的数据，一页显示不全，需要分页提交sql请求
2.起始条目索引从0开始：第n条索引n-1
3.limit子句放在查询语句的最后
4.公式：要显示的页数 page，每页显示条目数size
select 查询列表 from 表
limit (page-1)*size,size;
比如：size=10
page
1 0
2 10
3 20

1.9 联合查询

SELECT 查询列表1【FROM 表】【WHERE 条件】UNION 【ALL】
SELECT 查询列表2 【FROM 表】【WHERE 条件】UNION 【ALL】
…..
SELECT 查询列表n【FROM 表】【WHERE 条件】
1.应用：将多条查询语句的结果上下合并成一个查询结果，其字段名是第一条查询语句的查询字段名。（联合是上下拼接；连接是左右拼接）
2.要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
3.要求多条查询语句的查询列数必须是一致的
4.要求多条查询语句的查询的每一列字段的类型和顺序最好一致
5. union关键字默认去重，如果使用union all 可以包含重复项

2 DML

2.1 插入（添加）语句

2.1.1 方式一：经典的插入

INSERT INTO 表名【(列/字段名1,…)】 VALUES(新值1,…), .., (新值1,…);
或INSERT INTO 表名【(列/字段名1,…)】SELECT查询语句;
1.新值1插入表名的列名1中（新值和列名要一一对应）
2.插入的值的类型要与列的类型一致或兼容
3.不可以为null的列必须插入值。
可以为null的列可以在对应列名处插入null，或列名和null都不写
4.列和新值的顺序可以调换，但是两者要一一对应
5.列数和值的个数必须一致
6.可以省略列名，默认所有列，此时列的顺序和表中列的顺序一致，因此新值得按表的顺序插入

2.1.2 方式二：

INSERT INTO 表名 SET 列名1=新值1,列名2=新值2,…列名N=新值N;

2.1.3 两种方式大pk

1.方式一支持插入多行,方式二不支持
VALUES(新值1,…), (新值1,…), (新值1,…), ….., (新值1,…);
2.方式一支持子查询，方式二不支持
INSERT INTO 表名【(列/字段名1,…)】SELECT查询语句;

2.2 修改

2.2.1 修改单表的记录

UPDATE 表名 SET 列/字段=新值,列/字段=新值,… 【WHERE 筛选条件】
1.修改的新值的类型要与列的类型一致或兼容
2.字符和日期型要加单引号
3.where 筛选条件：用来限制要修改表中的哪些行
筛选条件可以使用条件表达式、逻辑表达式、模糊查询

2.2.2 修改多表的记录：先连接成一个表

sql92语法：
UPDATE 表1 别名1,表2 别名2
SET 字段/列=新值,字段/列=新值,…
WHERE 连接条件 AND 筛选条件;
sql99语法：
UPDATE 表1 别名INNER|LEFT|RIGHT JOIN 表2 别名ON 连接条件 #先连接
SET 字段/列=新值,字段/列=新值,…
WHERE 筛选条件;

2.3 删除

2.3.1 方式1：delete语句

（1）单表的删除：
DELETE FROM 要删除的表名【WHERE 筛选条件】
1.筛选条件限制要删除表中的哪些行，不加就是删除所有行数据
2.删除的是整行
（2）多表的删除：先连接成一个表
sql92语法：
DELETE 要删除的表的别名
FROM 表1 别名,表2 别名
WHERE 连接条件 AND 筛选条件;
sql99语法：
DELETE 表1的别名1,表2的别名2
FROM 表1 别名1 INNER|LEFT|RIGHT JOIN 表2 别名2 ON 连接条件
WHERE 筛选条件;

2.3.2 方式2：truncate语句：清空表中全部数据

TRUNCATE TABLE 表名;

2.3.3 两种方式的区别【面试题】

1.delete 可以加where 条件，truncate不能加
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列/字段，
如果用delete删除后，再插入数据，自增长列的值从断点（删除前最后一个值后）开始，
而truncate删除后，再插入数据，自增长列的值从1开始
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚

3 DDL

3.1 库和表的管理

创建： create
修改： alter
删除： drop

3.1.1 库的管理：

一、创建库
CREATE DATABASE IF NOT EXISTS 库名;
二、修改库
①更改库名
RENAME DATABASE 旧库名 TO 新库名;
②更改库的字符集
ALTER DATABASE 库名 CHARACTER SET 字符集;
三、删除库
DROP DATABASE IF EXISTS 库名;

3.1.2 表的管理：

一、创建表
USE 库名;
CREATE TABLE IF NOT EXISTS 表名(
列/字段名列/字段的类型【(长度)】【列的约束】【标识列】,
…
列/字段名列/字段的类型【(长度)】【列的约束】【标识列】);
二、修改表
ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 列名【列类型约束】;
①修改表名
ALTER TABLE 旧表名 RENAME TO 新表名;
②修改列/字段名【以列的类型及】
ALTER TABLE 表名 CHANGE COLUMN 旧列名新列名列的类型;
③修改列/字段类型和列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名新的列类型 ;
④添加列/字段
ALTER TABLE 表名 ADD COLUMN 列名【列类型约束】;
⑤删除列/字段
ALTER TABLE 表名 DROP COLUMN 列名;
三、删除表
DROP TABLE IF EXISTS 表名;
四、表的复制
①仅仅复制表的结构（不包含数据）：CREATE TABLE 生成复制表的名 LIKE 表名;
②仅仅复制表的某些字段（不包含数据）：CREATE TABLE 生成复制表的名 SELECT 要复制的列1 ,…,列表n FROM 表名WHERE 1=0;
③复制表的结构+数据：CREATE TABLE 生成复制表的名 SELECT * FROM 表名;
④只复制部分字段+数据：CREATE TABLE 生成复制表的名 SELECT 要复制的列1 ,…,列表n FROM 表名WHERE 筛选条件;
注：1.可以跨库，但是要指明库名.表名
2.列类型包括：类型名(长度)

3.1.3 创建库、表通用的写法：

DROP DATABASE IF EXISTS 旧库名/旧表名;
CREATE DATABASE 新库名/表名();

3.2 常见数据类型

数值型：整型；小数（定点数；浮点数）
字符型：较短的文本（char、varchar）；较长的文本：（text、blob较长的二进制数据）
日期型
一、整型
分类：tinyint、smallint、mediumint、int/integer、bigint
字节：1 2 3 4 8
1.类型名(长度) 【unsigned有无符号】【zerofill】
2.如果不设置有符号还是无符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
3.如果插入的数值超出了整型的范围,会报out of range异常，并且插入了临界值
4.如果不设置长度，会有默认的长度。
长度代表了显示结果的最大宽度。如果不够可以用0在左边填充，但0填充必须搭配zerofill使用。一加上zerofill，默认变成了无符号。
二、小数
1.浮点型：float(M,D) double(M,D)
2.定点型：dec(M,D) decimal(M,D)
①M：整数部位+小数部位；D：小数部位
如果超过范围，则插入临界值
②M和D都可以省略
如果是decimal，则M默认为10，D默认为0；
如果是float和double，则会根据插入的数值的精度来决定精度
③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
三、字符型
1.较短的文本：char、varchar；binary和varbinary用于保存较短的二进制；enum用于保存枚举；set用于保存集合
2.较长的文本：text、blob(较大的二进制，如图片)
3.较短的文本的特点：

四、字符型
1.分类：
date保存日期；time 只保存时间；year只保存年
datetime保存日期+时间；timestamp保存日期+时间
2.特点

3.3 常见约束

USE 库名;
CREATE TABLE IF NOT EXISTS 表名(
列/字段名列/字段的类型【(长度)】【列级约束】,
…
列/字段名列/字段的类型【(长度)】【列级约束】,
表级约束);
1. 含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
2. 分类：六大约束
1) NOT NULL：非空，用于保证该字段的值不能为空。比如姓名、学号
2) DEFAULT 默认值：默认，用于保证该字段有默认值。比如性别
3) PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空。比如学号
4) UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。比如座位号
5) CHECK(输入条件)：检查（mysql不支持），只有满足条件的值才能添加。比如性别
CHECK(gender=’男’ OR gender =’女’)或CHECK(gender IN(‘男’,’女’))
6) FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。即，在从表添加外键约束，用于引用主表中某列的值（类型必须一致）。比如员工表的部门编号（从）和部门表（主）
3. 添加约束的时机：①创建表时；②修改表时
4. 约束的添加分类：
1) 列级约束：列的后面。六大约束语法上都支持，但外键约束没有效果。不可以起约束名
2) 表级约束：所有列的后面。除了非空、默认，其他的都支持。可以起约束名（主键没有效果）
5.结合下面两句查看约束：
SHOW INDEX FROM 表名：查看表中的所有索引，包括主键、外键、唯一
DESC 表名：查看表中的结构，看不到外键约束
6. 主键和唯一的大对比

注：两个列是否允许组合成一个：PRIMARY KEY /UNIQUE (列1,列2)，两列都一样才认为是不唯一
7. 外键的特点
1) 要求在从表设置外键关系
2) 从表的外键列的类型和主表的关联列的类型要求一致或兼容，字段名称无要求
3) 主表的关联列必须是一个key（一般是主键或唯一）。
注：key包括主键、唯一、外键、自定义key
4) 插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表

3.3.1 创建表时添加约束

一、添加列级约束
直接在字段名的类型后面添加约束类型即可，多个约束用空格隔开。
只支持：非空、默认、主键、唯一
二、添加表级约束
在最后一个字段,的最下面添加：【CONSTRAINT约束名】约束类型(要添加约束的字段名);
1.添加外键：【CONSTRAINT 约束名从表名主表名】 FOREIGN KEY(从表的字段名)
REFERENCES 主表名(主表的字段名)
2.主键约束改名字了也没有效果，仍是PRIMARY；其他约束不改名字默认为字段名
3.约束名不可以重复
4. 多个约束用逗号隔开，最后一个约束不用逗号。
三、通用的写法
CREATE TABLE IF NOT EXISTS 表名(
字段名字段类型约束（NOT NULL、DEFAULT默认值、PRIMARY KEY、UNIQUE）
CONSTRAINT 约束名从表名主表名 FOREIGN KEY(从表的字段名)

3.3.2 修改表时添加约束

一、添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 要修改的字段名字段类型新约束;
二、添加表级约束
ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】约束类型(要修改的字段名)
1.修改外键：ALTER TABLE 表名 ADD 【CONSTRAINT 约束名从表名主表名】 FOREIGN KEY(从表的字段名) REFERENCES 主表名(主表的字段名)

3.3.3 修改表时删除约束

1.删除非空/默认约束
ALTER TABLE 表名 MODIFY COLUMN 字段名字段类型;
2.删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
3.删除唯一
ALTER TABLE 表名 DROP INDEX 字段名;
4.删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

3.4 标识列（自增长列）

不用手动插入值。系统提供默认的序列值（默认从1开始，每次增长1即步长为1）。

3.4.1 创建表时设置标识列

字段名约束类型AUTO_INCREMENT
1.标识列不一定必须和主键搭配，但要求是一个key（主键、唯一、外键、自定义key）
2.一个表至多可以有一个标识列
3.标识列的类型只能是数值型
4.标识列可以通过 SET auto_increment_increment=步长;设置步长
5. 设置标识列后添加数据的方法：
1) INSERT INTO 表名(标识列) VALUES(NULL);
2) 可以通过手动插入值，设置起始值：
INSERT INTO 表名(标识列) VALUES(自定义起始值); #起始值自定义
INSERT INTO 表名(标识列) VALUES(NULL);#其他的仍用NULL
3) INSERT INTO 表名() VALUES(); #省略标识列

3.4.2 修改表时设置标识列

ALTER TABLE 表名 MODIFY COLUMN 要修改的字段名字段类型约束类型
AUTO_INCREMENT;

3.4.3 修改表时删除标识列

ALTER TABLE 表名 MODIFY COLUMN 要修改的字段名字段类型

4 创建模拟数据必需的存储函数

创建函数，假如报错：This function has none of DETERMINISTIC……