01.作用
SQL别名用于为表或表中的列提供临时名称
SQL别名通常用于使列名更具可读性
SQL一个别名只存在于查询期间
02.场景
查询涉及多个表
用于查询函数
需要把两个或更多的列放在一起
列名长或可读性差
03.列别名
SELECT column_name AS alias_name FROM table_name;
04.表别名
SELECT column_name(s) FROM table_name AS alias_name;
1.1.2 null用法
00.运算
如果null参与算术运算，则该算术表达式的值为null 例如：+，-，*，/ 加减乘除
如果null参与比较运算，则结果可视为false 例如：>=,<=,<> 大于，小于，不等于
如果null参与聚集运算，则聚集函数都置为null 使用isnull(字段,0)等方式可以避免这种情况）
除count(*), count(1), count(0)等之外，null行才会计算
注：（count(字段) 字段为null的行不参与计数）
如果在not in子查询中有null值的时候，则不会返回数据
01.如果是null，必须用is ，或is not
SQL> select * from emp where mgr is null;
EMPNO ENAME JOB MGR HIREDATE
7839 KING PRESIDENT 17-11月-81
02.任何数字和null结算，结果为null
SQL> select empno, ename, sal, comm, sal*12+comm 年薪 from emp where empno>=7400;
EMPNO ENAME SAL COMM 年薪
---------- -------------------- ---------- ---------- ----------
7654 MARTIN 1250 1400 16400
7698 BLAKE 2850
7839 KING 5000
7844 TURNER 1500 0 18000
03.not in包含null值
select * from user where uid not in (a,b,c,null);
这个sql不回返回任何结果，要避免not in的list中出现null的情况
04.需要对null进行处理：null->0
a.nvl:if
nvl(comm,0 )
SQL> select ename,comm from emp; SQL> select ename,nvl(comm,0) from emp;
ENAME COMM ENAME NVL(COMM,0)
-------------------- ---------- -------------------- -----------
SMITH SMITH 0
ALLEN 300 ALLEN 300
WARD 500 WARD 500
JONES JONES 0
MARTIN 1400 MARTIN 1400
BLAKE BLAKE 0
CLARK CLARK 0
SCOTT SCOTT 0
KING KING 0
b.nvl2:if...else
nvl2(comm,comm,0)
if(comm==null)
return 0
else
return comm
select ename,comm,nvl(comm,0),nvl2(comm,comm,0) from emp
ENAME COMM NVL(COMM,0) NVL2(COMM,COMM,0)
-------------------- ---------- ----------- -----------------
SMITH 0 0
ALLEN 300 300 300
WARD 500 500 500
JONES 0 0
MARTIN 1400 1400 1400
BLAKE 0 0
CLARK 0 0
SCOTT 0 0
KING 0 0
1.1.3 行列转换
01.行转列
a.数据
a.介绍
基础数据：有学生成绩表，包含学生id、学科、成绩
b.行
student_id subject score
001 语文 89
001 数学 95
001 英语 77
002 语文 92
002 数学 83
002 英语 97
003 语文 81
003 数学 94
003 英语 88
c.列
student_id yuwen shuxue yingyu
001 89 95 77
002 92 83 97
003 81 94 88
b.建表
a.行
CREATE TABLE scores (
student_id VARCHAR(10),
subject VARCHAR(10),
score INT,
PRIMARY KEY (student_id, subject)
);
-------------------------------------------------------------------------------------------------
INSERT INTO scores (student_id, subject, score) VALUES
('001', '语文', 89),
('001', '数学', 95),
('001', '英语', 77),
('002', '语文', 92),
('002', '数学', 83),
('002', '英语', 97),
('003', '语文', 81),
('003', '数学', 94),
('003', '英语', 88);
b.列
CREATE TABLE student_scores (
student_id VARCHAR(10) PRIMARY KEY,
yuwen INT,
shuxue INT,
yingyu INT
);
-------------------------------------------------------------------------------------------------
INSERT INTO student_scores (student_id, yuwen, shuxue, yingyu) VALUES
('001', 89, 95, 77),
('002', 92, 83, 97),
('003', 81, 94, 88);
c.MySQL
INSERT INTO student_scores
SELECT student_id,
SUM(CASE WHEN subject = '语文' THEN SCORE END) AS yuwen,
SUM(CASE WHEN subject = '数学' THEN SCORE END) AS shuxue,
SUM(CASE WHEN subject = '英语' THEN SCORE END) AS yingyu
FROM scores
GROUP BY student_id;
d.Dameng
SELECT student_id,
MAX(CASE WHEN subject = '语文' THEN score END) AS yuwen,
MAX(CASE WHEN subject = '数学' THEN score END) AS shuxue,
MAX(CASE WHEN subject = '英语' THEN score END) AS yingyu
FROM scores
GROUP BY student_id;
e.Dameng
WITH RankedScores AS (
SELECT
student_id,
subject,
score,
ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY subject) AS rn
FROM scores
)
SELECT
student_id,
MAX(CASE WHEN rn = 1 THEN score END) AS yuwen,
MAX(CASE WHEN rn = 2 THEN score END) AS shuxue,
MAX(CASE WHEN rn = 3 THEN score END) AS yingyu
FROM RankedScores
GROUP BY student_id;
02.列转行
a.数据
a.介绍
现有骑手id，订单id列表，订单配送距离列表，配送费列表，其中订单id、配送距离、配送费一一对应
b.列
rider_id order_list distance_list payment_list
r001 0001,0005,0008 8.05,2.32,4.35 17.50,5.00,15.00
r002 0002,0004,0006,0009 3.01,10.98,0.78,5.05,6.05 13.00,15.00,5.00,9.50,7.00
r003 0003,0007 4.12,8.11 13.50,8.00
r004 NULL NULL NULL
c.行
rider_id order_id distance payment
r001 0001 8.05 7.50
r001 0005 2.32 5.00
r001 0008 4.35 15.00
r002 0002 3.01 13.00
r002 0004 10.98 15.00
r002 0006 0.78 5.00
r002 0009 5.05 9.50
r002 0010 6.05 17.00
r003 0003 4.12 3.50
r003 0007 8.11 8.00
r004 NULL NULL NULL
b.建表
a.行
CREATE TABLE riders (
rider_id VARCHAR(10) PRIMARY KEY,
order_list TEXT,
distance_list TEXT,
payment_list TEXT
);
-------------------------------------------------------------------------------------------------
INSERT INTO riders (rider_id, order_list, distance_list, payment_list) VALUES
('r001', '0001,0005,0008', '8.05,2.32,4.35', '17.50,5.00,15.00'),
('r002', '0002,0004,0006,0009', '3.01,10.98,0.78,5.05,6.05', '13.00,15.00,5.00,9.50,7.00'),
('r003', '0003,0007', '4.12,8.11', '13.50,8.00'),
('r004', NULL, NULL, NULL);
b.列
CREATE TABLE rider_details (
rider_id VARCHAR(10),
order_id VARCHAR(10),
distance DECIMAL(10, 2),
payment DECIMAL(10, 2),
PRIMARY KEY (rider_id, order_id)
);
-------------------------------------------------------------------------------------------------
INSERT INTO rider_details (rider_id, order_id, distance, payment) VALUES
('r001', '0001', 8.05, 7.50),
('r001', '0005', 2.32, 5.00),
('r001', '0008', 4.35, 15.00),
('r002', '0002', 3.01, 13.00),
('r002', '0004', 10.98, 15.00),
('r002', '0006', 0.78, 5.00),
('r002', '0009', 5.05, 9.50),
('r002', '0010', 6.05, 17.00),
('r003', '0003', 4.12, 3.50),
('r003', '0007', 8.11, 8.00),
('r004', NULL, NULL, NULL);
c.MySQL
SELECT
r.rider_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(r.order_list, ',', n.n), ',', -1) AS order_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(r.distance_list, ',', n.n), ',', -1) AS distance,
SUBSTRING_INDEX(SUBSTRING_INDEX(r.payment_list, ',', n.n), ',', -1) AS payment
FROM
riders r
JOIN
(SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) n
ON
n.n <= LENGTH(r.order_list) - LENGTH(REPLACE(r.order_list, ',', '')) + 1
WHERE
r.order_list IS NOT NULL
AND r.distance_list IS NOT NULL
AND r.payment_list IS NOT NULL;
d.Dameng
SELECT rider_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(order_list, ',', numbers.n), ',', -1) AS order_id,
SUBSTRING_INDEX(SUBSTRING_INDEX(distance_list, ',', numbers.n), ',', -1) AS distance,
SUBSTRING_INDEX(SUBSTRING_INDEX(payment_list, ',', numbers.n), ',', -1) AS payment
FROM riders
JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers
ON CHAR_LENGTH(order_list) - CHAR_LENGTH(REPLACE(order_list, ',', '')) >= numbers.n - 1
WHERE order_list IS NOT NULL;
1.1.4 语法顺序、执行顺序
01.大小写
SQL不区分大小写
Linux区分大小写
02.MySQL语法顺序
SELECT
FROM
JOIN
ON
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
03.SQL执行顺序
(8) SELECT
(9) DISTINCT
(11) <Top Num> <select list>
(1) FROM [left_table]
(3) <join_type> JOIN <right_table>
(2) ON <join condition>
(4) WHERE <where condition>
(5) GROUP BY <group_by_list>
(6) WITH <CUBE | RollUP>
(7) HAVING <having_condition>
(10) ORDER BY <order by_list> ASC升序 | DESC降序
f o j w g w h s d o top
from on join where groupby with having select distinct orderby top
from 2张表生成一个笛卡尔积 生成“虚表1”
on 2张表进行一个关联匹配 生成“虚表2”
join 左/右关联，根据左/右表添加一个外部行 生成“虚表3”
where 行筛选 生成“虚表4”
groupby 列分组 生成“虚表5”
with 所选列中值的组合聚合 生成“虚表6”
having 符合分组条件进行筛选 生成“虚表7”
select 选择列 生成“虚表8”
distinct 删除重复数据行 生成“虚表9”
orderby 排序生成游标 生成“虚表10”
<Top 数字> 选择前几条 生成“虚表11”
<select list> 将执行list结果返回
01.ALTER
a.说明
列：增、删、改
b.示例
alter table mytab6 add myother varchar2(10) 增加新列
alter table mytab6 drop column myother2 删除列
alter table mytab6 rename column myother to myother3 重命名列 rename ... to ...
c.示例2
-- 修改列 modify
alter table mytab6 modify myother varchar2(20) 修改列的长度
alter table mytab6 modify myother number 修改列的类型
d.示例3
-- 注意：blob/clob不能修改 -> 先删除此列，重新追加
alter table mytab6 add myother2 blob
alter table mytab6 modify myother2 number
1.2.6 索引
01.CREATE
a.说明
创建数据库、表、视图、索引
b.索引分类
主键索引：不能重复 id不能是null
唯一索引：不能重复 id可以是null
单值索引：单列age 一个表可以多个单值索引
复合索引：多个列构成的索引 相当于二级目录(name,age)、(a,b,c,d,...,n)
c.创建索引
a.方式一：create 索引类型 索引名 on 表（字段）
a.主键
create table tb(
id int(4) auto_increment,
name varchar(5),
dept varchar(5),
primary key(id)
)ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
b.单值
create index dept_index on tb(dept);
c.唯一
create unique index name_index on tb(name);
d.复合索引
create index dept_name_index on tb(dept,name);
b.方式二：alter table 表名 索引类型 索引名（字段）
a.主键
alter table tb add primary key(id);
b.单值
alter table tb add index dept_index(dept);
c.唯一
alter table tb add unique index name_index(name);
d.复合索引
alter table tb add index dept_name_index(dept,name);
c.注意
如果一个字段是primary key，则改字段默认就是“主键索引”
d.删除索引
drop index 索引名 on 表名 ;
drop index name_index on tb ;
e.查询索引
show index from 表名;
show index from 表名\G
1.2.7 视图
00.回答
视图是一种虚拟表，它是基于SQL查询结果的，它本身并不存储数据，而是存储SQL查询
01.CREATE VIEW
a.说明
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。
但是，视图并不在数据库中以存储的数据值集形式存在。
视图总是显示最新数据！每当用户查询视图时，数据库引擎就使用视图的 SQL 语句重新构建数据
b.创建视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
c.更新视图
ALTER VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
d.查看视图
SELECT * FROM view_name
e.删除视图
DROP VIEW view_name
02.示例
a.创建一个只有广东省的视图
CREATE VIEW Customer_GD AS
SELECT *
FROM Customers
WHERE 省份='广东省
b.更新视图
ALTER VIEW Customer_GD AS
SELECT *
FROM Customers
WHERE 省份='广东省 AND 客户ID=6
c.查看视图
SELECT * FROM Customer_GD
d.删除视图
DROP VIEW Customer_GD
00.查询
范围查询 between
模糊查询 like 数字、字母、日期
分组查询+多行函数/聚合函数/数字函数/组函数 group by
排序查询 order by
2.1.4 [1]DISTINCT子句
01.DISTINCT子句
a.语法
SELECT DISTINCT 客户ID,日期 FROM Orders 多列去重，必须保证多列【客户ID+日期】都有相同的值
2.1.5 [2]SELECT
01.SELECT子句
a.语法
SELECT 客户ID,日期 FROM Orders 结果集为列表，返回【对象数组】
2.1.6 [3]WHERE
01.WHERE子句
a.语法
SELECT 列名称 FROM 表名 WHERE 列 运算符 值
=
!=
>
<
>=
<=
BETWEEN AND / NOT BETWEEN AND 在某个范围内，查找介于两个值之间的所有数据，包括开始值和结束值，等价于 >= AND <=
LIKE / NOT LIKE 搜索某种模式
_ 下划线表示单个字符
％ 百分号表示零个，一个或多个字符
WHERE 地址 LIKE 'a%' 查找以“a”开头的任何值
WHERE 地址 LIKE '%a' 查找以“a”结尾的任何值
WHERE 地址 LIKE '%or%' 在任何位置查找任何具有“or”的值
WHERE 地址 LIKE '_r%' 在第二个位置查找任何具有“r”的值
WHERE 地址 LIKE 'a_%_%' 查找以“a”开头且长度至少为3个字符的值
WHERE 地址 LIKE 'a%o' 找到以"a"开头，以"o"结尾的值
IN / NOT IN 指定针对某个列的多个可能值；允许在WHERE子句种指定多个值，可以理解为OR条件的简写
EXISTS 在子查询中匹配到符合条件的数据行
b.示例1
select* from emp where mgr = 7788 and job = 'CLERK'; where执行顺序：右->左。先执行job='CLERK'，再执行mgr=7788
c.示例2
SELECT * FROM Customers WHERE ID='5' 错误
SELECT * FROM Customers WHERE ID=5 数字用数值：若使用引号，会触发隐式转换，string字符类型变为int数值类型，查询性能下降，使用不到索引
d.示例3
SELECT * FROM Customers WHERE 城市=北京 错误
SELECT * FROM Customers WHERE 城市='北京' 文本值用双引号
e.示例4
SELECT * FROM Customers WHERE 地址 LIKE '花%' 以“花”开头的“地址”的所有用户
SELECT * FROM Customers WHERE 省份 LIKE '%省' 以“省”结尾的“省份”的所有用户
SELECT * FROM Customers WHERE 地址 LIKE '__路%' 地址在第3位是“路”的所有用户
SELECT * FROM Customers WHERE 地址 LIKE '花__%' 地址以“花”开头，且长度至少为3个字符的所有客户
SELECT * FROM Customers WHERE 地址 NOT LIKE '北%' 地址不是以“北”开头的所有用户
SELECT * FROM emp WHERE ename LIKE '_M%' 姓名中第二个字母是M的员工信息：
SELECT * FROM emp WHERE ename LIKE '%M%' 姓名中包含M的员工信息
SELECT * FROM emp WHERE ename LIKE '_______%' 姓名长度>6的员工信息：>6 >=7
f.示例5
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...)
SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT)
SELECT * FROM Customers WHERE ID IN (1,2) ID属于1、2的所有用户
SELECT * FROM Customers WHERE 省份 IN ('上海市', '北京市', '广东省') 查找省份位于'上海市', '北京市', '广东省'的所有客户
SELECT * FROM Customers WHERE 城市 IN (SELECT 城市 FROM Suppliers) 查找供应商(Suppliers)和客户来自同一城市的客户信息
g.示例6
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 范围查询：数字/日期 严格遵照“先小后大”
SELECT * FROM Products WHERE 价格 BETWEEN 30 AND 60 查找价格介于30到60之间的商品
SELECT * FROM Products WHERE 价格 NOT BETWEEN 30 AND 60 查找价格不介于30到60之间的商品
SELECT * FROM Orders WHERE 订单日期 BETWEEN '2018-06-28' AND '2018-09-28' 查找订单日期，介于2018-06-28和2018-09-28之间的所有订单
SELECT * FROM Orders WHERE 订单日期 BETWEEN '2018-06-28 00:00:00' AND '2018-09-28 00:00:00'
h.示例7
--范围查询：数字/日期 严格遵照“先小后大”
between 小 and 大
>=小 and <=大
--数字
SQL> select * from emp where sal between 1000 and 2000;
EMPNO ENAME JOB MGR HIREDATE
i.示例8
7499 ALLEN SALESMAN 7698 20-2月 -81
7521 WARD SALESMAN 7698 22-2月 -81
SQL> ed
已写入 file afiedt.buf
1* select * from emp where sal between 2000 and 1000
SQL> /
未选定行
--日期
SQL> select * from emp where hiredate between'08-9月 -81' and '22-2月 -81';
未选定行
2.1.7 [3]WHERE子句：AND、OR
01.WHERE子句AND、OR
a.语法
where子语句中，连接多个条件进行过滤
b.示例
SELECT * FROM Customers WHERE 姓名='张三' AND 城市='上海' 姓名为 "张三" 并且城市为 "上海" 的人
SELECT * FROM Customers WHERE 姓名='张三' OR 城市='北京' 姓名为 "张三" 或者城市为 "北京" 的人
SELECT * FROM Orders WHERE (客户ID=3 OR 客户ID=1) AND 发货ID=4 客户ID是1或者3，并且两个客户的发货ID都是4的订单信息
SELECT * FROM Orders WHERE 客户ID=3 OR 客户ID=1 AND 发货ID=4 错误
2.1.8 [4]GROUP BY子句
01.GROUP BY子句
a.语法
与聚合函数相结合，根据一个或多个列对结果集进行分组
b.示例
SELECT 列名, 聚合函数(列名) FROM 表名 WHERE 列 运算符 值 GROUP BY 列名
2 3 1
SELECT 城市，COUNT(*) AS 客户数量 FROM Customers GROUP BY 城市 查询居住在各个城市的客户数量，分别有多少个
SELECT 城市，MAX(客户ID) AS 最大客户ID FROM Customers GROUP BY 城市 不同城市最大的客户ID是多少
2.1.9 [4]GROUP BY 聚合函数/数字函数/多行函数/组函数
01.GROUP BY 函数
a.语法
GROUP BY 聚合函数 聚合函数 说明
/ count() 它返回行数，包括一组中具有NULL值的行
数字函数 sum() 它返回集合中的总和(非NULL)
/ avg() 它返回一个表达式的平均值
多行函数 min() 它返回一组中的最小值(最低)
/ max() 它返回集合中的最大值(最高)
组函数 groutp_concat() 它返回一个串联的字符串
first() 它返回表达式的第一个值
last() 它返回表达式的最后一个值
b.以下三种形式的count函数
count(*) 此函数使用SELECT语句返回结果集中的行数。结果集包含所有Non-Null，Null和重复行
count(expression) 该函数返回结果集，但不包含Null行作为结果
count(distinct) 此函数返回不包含NULL值作为表达式结果的不同行的计数。
c.示例1
SELECT COUNT(emp_name) FROM employees; 计算表中可用的员工姓名总数
SELECT COUNT(*) FROM employees WHERE emp_age>32; 该语句返回employee表中的所有行，并且 WHERE子句指定emp_age列中的值大于32的行
SELECT COUNT(DISTINCT emp_age) FROM employees; 使用COUNT(distinct expression)函数对emp_age列中的Non-Null和不同的行进行计数:
SELECT emp_name, city, COUNT(*) FROM employees GROUP BY city; 带有GROUP BY子句的MySQL Count()函数：返回每个城市的雇员人数
SELECT emp_name, emp_age, COUNT(*) FROM employees GROUP BY emp_age HAVING COUNT(*)>=2 ORDER BY COUNT(*); 带有HAVING和ORDER BY子句的MySQL Count()函数：给出至少两个相同年龄的员工姓名，并根据计数结果对他们进行排序
d.示例2
SELECT SUM(working_hours) AS "Total working hours" FROM employees; 计算表中所有员工的总工作时间
SELECT SUM(working_hours) AS "Total working hours" FROM employees WHERE working_hours>=12; 带有WHERE子句的MySQL sum()函数：查询以计算 working_hours> = 12 的员工的总工作时间
SELECT emp_id, emp_name, occupation, SUM(working_hours) AS "Total working hours" FROM employees; 带有GROUP BY子句的MySQL sum()函数：计算每个员工的总工作时间
SELECT emp_id, emp_name, occupation, SUM(working_hours) Total_working_hours FROM employees GROUP BY occupation HAVING SUM(working_hours)>24; 带有HAVING子句的MySQL sum()函数：计算所有员工的工作时间，并根据他们的职业对其进行分组，并返回结果，其结果为Total_working_hours> 24
SELECT emp_name, occupation, SUM(DISTINCT working_hours) Total_working_hours FROM employees GROUP BY occupation; 带有DISTINCT子句的MySQL sum()函数：删除表中work_hours列中的重复记录。员工表，然后计算总和
e.示例3
SELECT AVG(working_hours) Avg_working_hours FROM employees; 计算表中所有员工的 平均工作时间:
SELECT AVG(working_hours) Avg_working_hours FROM employees WHERE working_hours>=12; 计算 working_hours> = 12 的员工的平均总工作时间
SELECT emp_name, occupation, AVG(working_hours) Avg_working_hours FROM employees GROUP BY occupation; 带有GROUP BY子句的MySQL AVG()函数：计算每个员工的平均工作时间，然后将结果与GROUP BY子句分组
SELECT emp_name, occupation, AVG(working_hours) Avg_working_hours FROM employees GROUP BY occupation HAVING AVG(working_hours)>9; 带有HAVING子句的MySQL AVG()函数：计算所有员工的平均工作时间，根据他们的职业对他们进行分组，并返回结果 Avg_working_hours> 9
SELECT emp_name, occupation, AVG(DISTINCT working_hours) Avg_working_hours FROM employees GROUP BY occupation; 带有DISTINCT子句的MySQL AVG()函数：以删除employee表中的重复记录然后返回平均值
f.示例4
SELECT MIN(income) AS Minimum_Income FROM employees; 找到表中可用的员工的 最低收入
SELECT MIN(income) AS Minimum_Income FROM employees WHERE emp_age >= 32 AND emp_age <= 40; 带有WHERE子句的MySQL MIN()函数：从employee表中查找所有行中的最低收入，WHERE子句指定 emp_age列大于或等于32且小于或等于40的所有行
SELECT emp_age, MIN(income) AS Minimum_Income FROM employees GROUP BY emp_age; 带有GROUP BY子句的MySQL MIN()函数：查找employee表中每个emp_age组的所有行中的最低收入
SELECT city, MIN(income) AS Minimum_Income FROM employees GROUP BY city HAVING MIN(income) > 150000; 带有HAVING子句的MySQL MIN()函数：返回所有雇员的最低收入，根据他们的城市将他们分组，并返回MIN(收入)> 150000的结果
SELECT emp_name, city, MIN(DISTINCT income) AS Minimum_Income FROM employees GROUP BY city; 带有DISTINCT子句的MySQL MIN()函数：返回表中存在的唯一记录数的最小收入值
g.示例5
SELECT MAX(income) AS "Maximum Income" FROM employees; 找到表中可用的员工的最高收入:
SELECT MAX(income) AS "Maximum_Income" FROM employees WHERE emp_age > 35; 带有WHERE子句的MySQL MAX()函数：在employee表中的所有行中查找最大收入。 WHERE子句指定 emp_age列大于35的所有行
SELECT emp_age, MAX(income) AS "Maximum Income" FROM employees GROUP BY emp_age; 带有GROUP BY子句的MySQL MAX()函数：从employee表的所有行中找到每个emp_age组的最大收入
SELECT city, MAX(income) AS "Maximum Income" FROM employees GROUP BY city HAVING MAX(income) >= 200000; 带有HAVING子句的MySQL MAX()函数：返回所有雇员中的最大收入，并根据他们所在的城市对其进行分组，并返回其MAX(income)> = 200000的结果
SELECT city, MAX(DISTINCT income) AS "Maximum Income" FROM employees GROUP BY city; 带有DISTINCT子句的MySQL MAX()函数：以删除employee表的income列中的重复记录，按城市分组，然后返回最大值
SELECT * FROM employees WHERE emp_age = (SELECT MAX(emp_age) FROM employees); 子查询示例中的MySQL MAX()函数：子查询首先从表中找到雇员的最大年龄。然后，主查询(外部查询)返回的年龄等于从子查询返回的最大年龄以及其他信息。
01.HAVING子句
a.语法
对分组后的数据进一步过滤，找出符合分组条件的记录
b.示例
SELECT 列名, 聚合函数(列名) FROM 表名 WHERE 列 运算符 值 GROUP BY 列名 HAVING COUNT(1)>1
2 3 1 4
SELECT 城市, COUNT(*) AS 客户数量 FROM Customers GROUP BY 城市 HAVING COUNT (1)>1 查询居住在各个城市的客户数量，大于或等于2的所有记录
SELECT 城市, COUNT(*) AS 客户数量 FROM Customers GROUP BY 城市 HAVING COUNT (1)>1 AND 城市!='南京' 查询居住在各个城市(不包括南京)的客户数量，大于或等于2的所有记录
2.1.13 [6]ORDER BY子句
01.ORDER BY子句
a.语法
HAVING子句必须紧随GROUP BY子句，并出现在ORDER BY子句之前
对结果集进行排序，按照【省份】拼音首字母的ASCII规则排序；字母相同，则比较第二位字母，依次类推
b.示例
SELECT 列名, 聚合函数(列名) FROM 表名 WHERE 列 运算符 值 GROUP BY 列名 HAVING COUNT(1)>1 ORDER BY 列1 ASC升序, 列2 DESC降序
5 2 3 1 4 6 7
SELECT * FROM Customers 省份 ASC, 姓名 DESC 按照省份升序、姓名降序的规则，对客户进行排序
2.1.14 [7]TOP/LIMIT/ROWNUM
01.TOP子句
a.语法
SELECT TOP 数字 列名 FROM 表名
SELECT TOP 百分比 列名 FROM 表名
b.分类1
SELECT TOP 3 * FROM Customers 获取表的前3条
SELECT TOP 40 Percent * FORM Customers ORDER BY 客户ID DESC 获取表后40%的记录
SELECT TOP 40 Percent * FORM Customers ORDER BY NEWID() 获取随机40%的记录
b.分类2
SELECT TOP 5 * FROM Customers sqlserver是TOP 5
SELECT * FROM Customers LIMIT 5 mysql是limit 5
SELECT * FROM Customers WHERE ROWNUM <= 5 oracle是where rownum≤5
2.2 集合运算
2.2.1 [0]汇总：4个
01.集合运算
UNION（并集）：返回各个查询的所有记录，不包括重复记录
UNION ALL（并集）：返回各个查询的所有记录，包括重复记录
INTERSECT（交集）：返回两个查询共有的记录
MINUS（差集）：返回包含在第一个查询中，但不包含在第二个查询中的记录
2.2.2 并集、交集、差集
00.说明
组合两个或更多SELECT语句的结果集
使用前提：UNION中的每个SELECT语句必须具有相同的列数
1、这些列也必须具有相似的数据类型
2、每个SELECT语句中的列也必须以相同的顺序排列
01.示例1
SELECT column_name(s) FROM table1
UNION (ALL)
SELECT column_name(s) FROM table2
注意：UNION结果集中的列名总是等于UNION中第一个SELECT语句中的列名
02.示例2
SELECT 城市 FROM customers_bak 从“customers_bak”和“Suppliers”表中选择所有不同的城市（只有不同的值）
UNION
SELECT 城市 FROM Suppliers
03.示例3
SELECT 城市 FROM customers_bak 从“customers_bak”和“Suppliers”表中选择所有不同的城市（可以有重复值）
UNION ALL
SELECT 城市 FROM Suppliers
2.2.3 各个集合的列数、类型必须保持一致
01.各个集合的列数、类型必须保持一致
select empno,ename from emp --数字 字符串 2列
union
select deptno,job from emp; --数字 字符串 2列
SQL> select empno from emp
2 union
3 select deptno,job from emp;
select empno from emp
*
第 1 行出现错误:
ORA-01789: 查询块具有不正确的结果列数
2.3 多表连接
2.3.1 [0]汇总：6个
01.支持情况
不同的数据分析工具 支持的表连接方式
Oracle/ sql server/ Tableau/ Python 内连接(inner join)、左连接(left join)、右连接(right join)、全连接(full join)
MySQL 内连接(inner join)、左连接(left join)、右连接(right join)
Power BI 内连接、左连接、右连接、全连接、左反连接、右反连接
02.多表连接
1.交叉连接（笛卡儿积） CROSS JOIN
2.内连接 INNER JOIN
等值连接：ON A.id=B.id
不等值连接：ON A.id > B.id
3.外连接
左外连接 LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，简写成LEFT JOIN
右外连接 RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，简写成RIGHT JOIN
4.全外连接=左外连接+右外连接+去重 FULL JOIN
MySQL不支持全连接，可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用
5.自连接
02.JOIN连接
a.说明
JOIN 连接用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段
最常见的 JOIN 类型：INNER JOIN（简单的 JOIN）
INNER JOIN 从多个表中返回满足 JOIN 条件的所有行
b.语法
CROSS JOIN 交叉连接/笛卡儿积 其他4种都是以笛卡儿积为基准
INNER JOIN 内连接 如果表中有至少一个匹配，则返回行
LEFT JOIN 左连接 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN 右连接 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN 全连接 只要其中一个表中存在匹配，则返回行
c.交叉连接/笛卡儿积（CROSS JOIN）
SELECT column_name(s) SELECT A.NUM_A, B.NUM_B
FROM table1 FROM A
CROSS JOIN table2 CROSS JOIN B
ON table1.column_name = table2.column_name; ON A.Num_A = B.NUM_B
d.内连接（INNER JOIN）
SELECT column_name(s) SELECT A.NUM_A, B.NUM_B 等价于 SELECT A.NUM_A, B.NUM_B
FROM table1 FROM A FROM A,B
INNER JOIN table2 INNER JOIN B WHERE A.name = B.name
ON table1.column_name = table2.column_name; ON A.Num_A = B.NUM_B
e.左连接（LEFT JOIN）
SELECT column_name(s) SELECT A.NUM_A, B.NUM_B
FROM table1 FROM A A主表
LEFT JOIN table2 LEFT JOIN B
ON table1.column_name=table2.column_name; ON A.Num_A = B.NUM_B
f.右连接（RIGHT JOIN）
SELECT column_name(s) SELECT A.NUM_A, B.NUM_B
FROM table1 FROM A B主表
RIGHT JOIN table2 RIGHT JOIN B
ON table1.column_name = table2.column_name; ON A.Num_A = B.NUM_B
g.全连接（FULL JOIN）=左连接+右连接+去重
SELECT column_name(s) SELECT A.NUM_A, B.NUM_B
FROM table1 FROM A
FULL JOIN table2 FULL JOIN B
ON table1.column_name = table2.column_name; ON A.Num_A = B.NUM_B
2.3.2 [1]交叉连接（笛卡儿积）
01.语法1
select * from emp cross join dept;
02.语法2，二者等价
select * from emp, dept;
2.3.3 [2]内连接
01.语法
INNER JOIN: 表示为内连接，将两张表拼接在一起
on: 表示要执行某个条件
多张表通过相同字段进行匹配，只显示匹配成功的数据
02.示例
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
+------+--------+--------+------+-----------+
03.等值连接
a.select * from emp e ,dept d 只有where
where e.deptno = d.deptno;
b.select * from emp e 有inner join on
inner join dept d
on e.deptno = d.deptno
c.select * from emp e 只有join on
join dept d
on e.deptno = d.deptno
04.不等值连接（一般不用）
select * from emp e ,dept d
where e.deptno != d.deptno ;
2.3.4 [3]左连接
01.语法
完整显示左边的表 (person) ，右边的表如果符合条件就显示，不符合则补 NULL
LEFT JOIN 也叫做 LEFT OUTER JOIN，用这两种方式的查询结果是一样的。
以左表为基准（左表数据全部显示），去匹配右表数据
如果匹配成功，则全部显示；匹配不成功，显示部分（无数据部分 用NULL填充）
02.示例
SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
+------+--------+--------+------+-----------+
03.oracle独有
select * from emp e ,dept d
where e.deptno = d.deptno(+) ;
04.等价写法
select * from emp e
left join dept d
on e.deptno = d.deptno
2.3.5 [4]右连接
01.语法
完整显示右边的表 (card) ，左边的表如果符合条件就显示，不符合则补 NULL
以右表为基准（右表数据全部显示），去匹配左表数据
如果匹配成功，则全部显示；匹配不成功，显示部分（无数据部分 用NULL填充）
02.示例
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+--------+--------+------+-----------+
03.oracle独有
select * from emp e ,dept d
where e.deptno(+) = d.deptno;
04.等价写法
select * from emp e
right join dept d
on e.deptno = d.deptno
2.3.6 [5]全连接=左连接+右连接+去重
01.语法
MySQL 不支持这种语法的全外连接
SELECT * FROM person FULL JOIN card on person.cardId = card.id;
出现错误：
ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'
02.MySQL全连接语法，使用 UNION 将两张表合并在一起
SELECT * FROM person LEFT JOIN card on person.cardId = card.id
UNION
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+--------+--------+------+-----------+
2.3.7 [6]自连接
01.两层自连接
SELECT b.*
from shopping as a,shopping as b
where a.name='惠惠'
and a.price<b.price
order by b.id
02.三层自连接查询
SELECT c.dept_name area, b.dept_name as city, a.*
from sys_dept as a, sys_dept as b, sys_dept as c
where a.parent_id= b.dept_id
and b.parent_id= c.dept_id
and a.agency_id >= -1
order by b.dept_id
03.查询所有员工的编号、姓名，及其上级领导的编号、姓名, 显示结果按领导的年工资降序
a.要查询哪些字段、表
员工表e.empno, 员工表e.ename, 领导表b.empno, 领导表b.ename
emp e，emp b
b.连接条件
e.mgr = b.empno
order by b.sal*12+nvl(comm, 0) desc;
04.自连接
select e.empno, e.ename, b.empno, b.ename
from emp e, emp b
where e.mgr = b.empno
order by b.sal*12+nvl(b.comm, 0) desc;
2.4 子查询/内查询/嵌套查询
2.4.1 概念
01.什么是子查询
一种嵌套在【其他SQL查询】的【WHERE子句中的查询】
子查询用于为主查询返回其所需数据，或者对检索数据进行进一步的限制。
子查询可以在【SELECT、INSERT、UPDATE、DELETE】语句中，同=、<、>、>=、<=、IN、BETWEEN等运算符一起使用
02.子查询规则
1.子查询必须括在圆括号中
2.子查询的 SELECT 子句中只能有一个列，除非主查询中有多个列，用于与子查询选中的列相比较
3.子查询不能使用 ORDER BY，不过主查询可以。在子查询中，GROUP BY 可以起到同 ORDER BY 相同的作用
4.返回多行数据的子查询只能同多值操作符一起使用，比如 IN 操作符
5.SELECT 列表中不能包含任何对 BLOB、ARRAY、CLOB 或者 NCLOB 类型值的引用
6.子查询不能直接用在集合函数中
7.BETWEEN 操作符不能同子查询一起使用，但是 BETWEEN 操作符可以用在子查询中
2.4.2 分类
00.子查询分类
a.按照查询的返回结果
单行单列（标量子查询）：返回的是一个具体列的内容，可以理解为一个单值数据
单行多列（行子查询）：返回一行数据中多个列的内容
多行单列（列子查询）：返回多行记录之中同一列的内容，相当于给出了一个操作范围
多行多列（表子查询）：查询返回的结果是一张临时表
b.按子查询位置区分
select后的子查询：仅仅支持标量子查询，即只能返回一个单值数据。
from型子查询：把内层的查询结果当成临时表，供外层sql再次查询，所以支持的是表子查询
where或having型子查询：指把内部查询的结果作为外层查询的比较条件，支持标量子查询（单列单行）、列子查询（单列多行）、行子查询（多列多行）
一般会和下面这几种方式配合使用：
1.in子查询：内层查询语句仅返回一个数据列，这个数据列的值将供外层查询语句进行比较
2.any子查询：只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件
3.all子查询：内层子查询返回的结果需同时满足所有内层查询条件
4.比较运算符子查询：子查询中可以使用的比较运算符如 >、>=、<=、<、=、 <>
exists子查询：把外层的查询结果（支持多行多列），拿到内层，看内层是否成立，简单来说后面的返回true,外层（也就是前面的语句）才会执行，否则不执行
01.分类1
a.SELECT子查询语句
SELECT column_name [,column_name]
FROM table1 [,table2]
WHERE column_name OPERATOR
(SELECT column_name [,column_name]
FROM table1 [,table2 ]
[WHERE])
SELECT * FROM Customers
WHERE 客户 ID IN (
SELECT 客户 ID FROM Orders WHERE 员工 ID=9
);
b.INSERT子查询语句
INSERT INTO
table_name [(column1 [,column2])]
SELECT [*|column1 [,column2]
FROM table1 [,table2]
[WHERE VALUE OPERATOR]
INSERT INTO Customers_bak
SELECT * FROM Customers
WHERE 客户 ID IN (
SELECT 客户 ID FROM Orders WHERE 员工 ID=9
);
c.UPDATE子查询语句
UPDATE table
SET column_name = new_value
[WHERE OPERATOR [VALUE]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[WHERE)]
UPDATE Customers
SET 城市=城市+'市'
WHERE 客户 ID IN (
SELECT 客户 ID FROM Orders
);
d.DELETE子查询语句
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [VALUE]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[WHERE)]
DELETE FROM Customers
WHERE 客户 ID IN (
SELECT 客户 ID FROM Orders
)
02.分类2
a.SELECT语句中的子查询
a.标量子查询
SELECT column1, (SELECT MAX(column2) FROM table2) AS max_value
FROM table1;
b.列子查询
SELECT column1
FROM table1, (SELECT column2 FROM table2) AS subquery
WHERE table1.column3 = subquery.column2;
c.行子查询
SELECT column1, column2
FROM table1
WHERE (column1, column2) IN (SELECT column1, column2 FROM table2);
d.表子查询
SELECT *
FROM (SELECT column1, column2 FROM table1) AS subquery;
b.INSERT语句中的子查询
a.标量子查询
INSERT INTO table1 (column1)
VALUES ((SELECT MAX(column2) FROM table2));
b.列子查询
INSERT INTO table1 (column1)
SELECT column2 FROM table2;
c.UPDATE语句中的子查询
a.标量子查询
UPDATE table1
SET column1 = (SELECT MAX(column2) FROM table2)
WHERE column3 = 'some_value';
b.列子查询
UPDATE table1
SET column1 = (SELECT column2 FROM table2 WHERE table2.id = table1.id);
d.DELETE语句中的子查询
a.标量子查询
DELETE FROM table1
WHERE column1 = (SELECT MIN(column2) FROM table2);
b.列子查询
DELETE FROM table1
WHERE column1 IN (SELECT column2 FROM table2);
2.4.3 子查询中的null
00.NULL自身特性
如果!=NULL，则无法查询出任何数据
01.子查询的结果中不要有NULL
a.in: = or = or
select *from emp where mgr in (7566,7698); //有值
select *from emp where mgr in (7566,7698,NULL); //有值，null对结果无影响
理解：select *from emp where mgr =7566 or mgr=7698 or mgr = NULL;
b.not in: =and =and
select *from emp where mgr not in (7566,7698,NULL); //无值
理解：select *from emp where mgr!=7566 and mgr!=7698 and mgr!= NULL ;
02.子查询的结果中不要有NULL
a.说明
is null
is not null
= null //不正规
!= null //不正规
b.示例
SQL> select * from emp where empno is not NULL;
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- -------------- ------
7369 SMITH CLERK 7902 17-12月-80
7499 ALLEN SALESMAN 7698 20-2月 -81
SQL> select * from emp where empno!=NULL;
未选定行
2.4.4 主查询与子查询
01.介绍
a.说明
主查询和子查询可以是同一张表，也可以不同一张表
b.查询销售部的员工信息
// 1.现根据“销售部”查询 销售部的部门编号30
select deptno from dept where dname = 'DNAME' ;
// 2.根据部门编号30 查询员工信息
select * from emp
where deptno = (select deptno from dept where dname = 'DNAME' );
02.单行操作符（=,>,<）
a.查询工资比30号部门中 任意其中一个（存在）员工高的员工信息
// "只需要满足一个即可，存在一个就可以" -> any
select *from emp where sal > any(select sal from emp) ;
或
select *from emp where sal > (select min(sal) from emp) ;
b.查询工资比30号部门中全部员工高的（存在）员工信息
//"所有、全部" -> all
select *from emp where sal > all(select sal from emp) ;
或
select *from emp where sal > (select max(sal) from emp) ;
03.多行操作符(in): 查询销售部，财务部的员工信息
SQL> select * from emp where deptno in
(select deptno from dept where dname = 'SALES' or dname='ACCOUNTING');
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- -------------- ----
7782 CLARK MANAGER 7839 09-6月 -81
7839 KING PRESIDENT 17-11月-81
SQL> select deptno from dept where dname = 'SALES' or dname='ACCOUNTING';
DEPTNO
----------
10
30
SQL> select * from emp where deptno =
(select deptno from dept where dname = 'SALES' or dname='ACCOUNTING');
ORA-01427: 单行子查询返回多个行
3 DML：增、改、删
3.1 增：INSERT INTO
3.1.1 单条
01.INSERT INTO
a.说明
向表里面插入数据记录
b.语法
INSERT INTO table_name VALUES (value1, value2, value3, ...)
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...)
c.示例2
INSERT INTO Customers VALUES ('宋江','梁山路1号','济南','250000','山东省');
INSERT INTO Customers (姓名, 城市, 省份) VALUES ('武松', '邢台', '河北省');
INSERT INTO Customers (姓名, 地址, 城市, 邮编, 省份) VALUES ('宋江','梁山路1号','济南','250000','山东省');
3.1.2 批量
01.INSERT INTO
a.说明
向表里面插入数据记录
b.语法
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...),
(value1, value2, value3, ...),
(value1, value2, value3, ...),
(value1, value2, value3, ...);
02.SELECT INTO
a.说明
从一个表中复制数据，然后将数据插入到另一个新表中。这种用法只有SQL Server数据库才支持
b.语法
SELECT * INTO newtable [IN externaldb] FROM table1; 把所有的列都复制到新表中
SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;
c.示例
SELECT * INTO CustomersBak FROM Customers; 备份表
SELECT * FROM CustomerBak;
DROP TABLE CustomerBak;
d.示例
SELECT * INTO CustomersHis FROM Customers WHERE 1=0; 复制表结构
SELECT * FROM CustomersHis;
DROP TABLE CustomersHis;
03.INSERT INTO SELECT
a.说明
在旧表中插入（已存在的表）
从表中复制数据，并将数据插入现有的表中。目标表中的任何现有行都不会受到影响。这种写法在多个数据库平台都支持。
b.语法
INSERT INTO table2 SELECT * FROM table1; 将所有列从一个表中复制到另一个已经存在的表
INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1; 把想要的列复制到另一个现有的表
注意：table1和table2的表结构的列数和对应字段类型要一致
c.示例
INSERT INTO CustomersHis SELECT * FROM Customers; 从Customers中插入到CustomersHis
04.CREATE TABLE AS
a.说明
创建新表（批量插入之前不存在）
b.示例
create table mytab as select * from emp; 批量插入
create table mytab2 as select empno,ename,job from emp; 批量插入
create table mytab3 as select empno,ename,job from emp where sal < 6000; 批量插入
create table mytab4 as select * from emp where 1=0 ; 创建表结构
05.begin ... end /
begin
insert into emp values(1221,'LISI','MANAGER',7788,'19-9月 -88',9998,1000,10);
insert into emp values(1223,'LISI','MANAGER',7788,'19-9月 -88',9998,1000,10);
end;
/
06.海量数据
数据泵 / SQL Loader / 外部表
3.2 改：UPDATE
3.2.1 单条
01.UPDATE
a.说明
用于更新表中的现有记录
b.语法
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
UPDATE Customers SET 姓名= '鲁智深',城市= '平凉',省份='甘肃省' WHERE 姓名 ='张三'; 第一个客户（姓名=’张三’）更新了"姓名"，"城市"以及"省份"
c.示例
SELECT * INTO #Customers FROM Customers 备份表到#Customers
SELECT * FROM #Customers
UPDATE USERS SET NAME=FAX 把一个列的值复制成另一个列的值
UPDATE #Customers SET 姓名='宋江'; 危险操作，更新表里的所有信息
d.注意
要注意SQL UPDATE语句中的WHERE子句！
WHERE子句指定哪些记录需要更新。如果省略WHERE-子句，所有记录都将更新！
3.2.2 批量
01.MERGE INTO
MERGE INTO MW_APP.MWT_UD_TEST_CLASS t USING (
SELECT 'C001' AS CODE, '001' AS REMARK FROM DUAL UNION
SELECT 'C003' AS CODE, '003' AS REMARK FROM DUAL UNION
SELECT 'C005' AS CODE, '005' AS REMARK FROM DUAL
) f ON (t.CODE=f.CODE)
WHEN MATCHED THEN
UPDATE SET t.REMARK=f.REMARK
WHEN NOT MATCHED THEN
INSERT(OBJ_ID, OBJ_DISPIDX, CODE, REMARK)
VALUES(my_sys.newguid(), 0, f.CODE, f.REMARK);
02.replace into
replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');
03.insert into ... on duplicate key update
insert into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);
04.创建临时表，先更新临时表，然后从临时表中update
create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values (0,'gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;
05.使用mysql自带的语句构建批量更新
UPDATE tableName
SET orderId = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3)
01.DELETE
a.说明
用于删除表中现有记录
b.示例
DELETE FROM myorder; 清空某个表的所有数据
DELETE FROM table_name WHERE condition;
DELETE FROM #Customers WHERE 姓名='张三' 删除姓名为“张三”的用户信息
c.示例2
SELECT * #Customers FROM Customers; 备份表到#Customers
DELETE FROM #Customers 危险操作，删除表里的所有信息
d.注意
删除表格中的记录时要小心！
注意 SQL DELETE 语句中的 WHERE 子句！
WHERE 子句指定需要删除哪些记录。如果省略了 WHERE 子句，表中所有记录都将被删除！
00.无约束的表
create table student4(
stuno number(3) ,
stuname varchar2(10) ,
stuaddress varchar2(20) ,
subid number(3)
);
01.操作1
alter table 表名 add constraint 约束类型_字段名 约束类型(约束名)
alter table student4 add constraint UQ_stuaddress4 unique(stuaddress);
alter table student4 add constraint PK_stuno4 primary key(stuno);
alter table student4 add constraint CK_stuname4 check(length(stuname)>2);
alter table student4 add constraint FK_student4_sub foreign key(subid) references sub(sid);
02.操作2
-- alter table add 不适用于非空
alter table student4 add constraint NN_stuname not null(stuname);
ORA-00904: : 标识符无效
-- alter table 表名 motidy 字段名 constraint 约束类型_字段名 约束类型
alter table student4 modify stuname constraint NN_stuname4 not null ;
03.操作3
-- alter table add 不适用于默认
alter table student4 add constraint DF_stuname default 'hello';
ORA-00904: : 标识符无效
-- alter table 表名 motidy 字段名 约束类型(默认名)
alter table student4 modify stuname default '默认名字';
4.3.2 删除约束
01.操作1
-- alter table 表名 drop constraint 约束类型_字段名;
alter table student4 drop constraint UQ_stuaddress4;
alter table student4 drop constraint PK_stuno4;
alter table student4 drop constraint CK_stuname4;
alter table student4 drop constraint FK_student4_sub;
alter table student4 drop constraint NN_stuname4;
02.特殊情况：默认约束（删除默认约束：将默认约束置为null）
alter table student4 modify stuname default null;
01.添加：主键
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);
02.添加：复合主键
CREATE TABLE user (
id INT,
name VARCHAR(50),
age INT,
PRIMARY KEY(id, name)
);
03.添加：主键+自增约束
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age INT
);
04.添加：使用CONSTRAINT关键字、约束名称
CREATE TABLE sys_menu
(
id INT(64) AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
CONSTRAINT pk_sys_menu PRIMARY KEY (id)
);
05.添加
ALTER TABLE employees ADD PRIMARY KEY (id);
ALTER TABLE employees MODIFY COLUMN id INT PRIMARY KEY;
06.删除
ALTER TABLE employees DROP PRIMARY KEY;
01.添加
CREATE TABLE products (
id INT,
name VARCHAR(50) NOT NULL,
price DECIMAL(10, 2)
);
02.添加：使用CONSTRAINT关键字、约束名称
CREATE TABLE products
(
id INT,
name VARCHAR(50),
price DECIMAL(10, 2),
CONSTRAINT nn_products_name NOT NULL (name)
);
03.添加
ALTER TABLE products ADD CONSTRAINT products_name_nn NOT NULL (name);
ALTER TABLE products MODIFY COLUMN name VARCHAR(50) NOT NULL;
04.删除
ALTER TABLE products MODIFY COLUMN name VARCHAR(50) NULL;
4.4.5 默认约束（Default）
01.添加
CREATE TABLE customers (
id INT,
name VARCHAR(50),
email VARCHAR(50) DEFAULT 'N/A'
);
02.添加：使用CONSTRAINT关键字、约束名称
CREATE TABLE customers
(
id INT,
name VARCHAR(50),
email VARCHAR(50) DEFAULT 'N/A',
CONSTRAINT default_customers_email DEFAULT 'N/A' FOR email
);
03.添加
ALTER TABLE customers ALTER COLUMN email SET DEFAULT 'N/A';
ALTER TABLE customers MODIFY COLUMN email VARCHAR(50) DEFAULT 'N/A';
04.删除
ALTER TABLE customers ALTER COLUMN email DROP DEFAULT;
4.4.6 唯一约束（Unique）
01.添加
CREATE TABLE employees (
id INT,
email VARCHAR(50) UNIQUE,
name VARCHAR(50)
);
02.添加：使用CONSTRAINT关键字、约束名称
CREATE TABLE employees
(
id INT,
email VARCHAR(50),
CONSTRAINT uk_email UNIQUE (email)
);
03.添加
ALTER TABLE employees ADD UNIQUE (email);
ALTER TABLE employees MODIFY COLUMN email VARCHAR(50), ADD UNIQUE (email);
04.删除
ALTER TABLE employees DROP INDEX email;
4.4.7 检查约束（Check）
01.添加：使用CONSTRAINT关键字、约束名称
CREATE TABLE employees
(
id INT,
age INT,
CONSTRAINT ck_age CHECK (age >= 18 AND age <= 65)
);
02.添加
ALTER TABLE employees ADD CHECK (age >= 18 AND age <= 65);
ALTER TABLE employees MODIFY COLUMN age INT, ADD CHECK (age >= 18 AND age <= 65);
03.删除
ALTER TABLE employees DROP CHECK check_age;
4.5 外键删除
00.如果删除父表中外键所指向的列，2个策略
1.级联删除，on delete cascade
2.级联置空，on delete set null
01.ON DELETE操作
restrict(约束)：当在父表(即外键的来源表)中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除
no action：意思同restrict.即如果存在从数据，不允许删除主数据
cascade(级联)：当在父表(即外键的来源表)中删除对应记录时，首先检查该记录是否有对应外键，如果有则也删除外键在子表(即包含外键的表)中的记录
set null：当在父表(即外键的来源表)中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null(不过这就要求该外键允许取null)
02.ON UPDATE操作
restrict(约束)：当在父表(即外键的来源表)中更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新
no action：意思同restrict
cascade(级联)：当在父表(即外键的来源表)中更新对应记录时，首先检查该记录是否有对应外键，如果有则也更新外键在子表(即包含外键的表)中的记录
set null：当在父表(即外键的来源表)中更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null(不过这就要求该外键允许取null)
4.5.1 级联删除：on delete cascade
01.级联删除
当删除父表中的数据时，子表会跟着删除相对应的数据
02.操作
-- 先创建父表sub
create table sub(
sid number(3) unique,
sname varchar2(10)
);
insert into sub values(1,'java');
insert into sub values(2,'python');
-- 再创建子表student3，同时设置外键+级联删除
create table student3(
stuno number(3) ,
stuname varchar2(10) ,
stuaddress varchar2(20) ,
subid number(3) ,
constraint FK_student3_sub foreign key(subid) references sub(sid) on delete cascade
);
insert into student3(stuno,stuname,subid) values(1,'zs',1);
insert into student3(stuno,stuname,subid) values(2,'ls',1);
insert into student3(stuno,stuname,subid) values(3,'ww',2);
-- 子表存在subid=2数据的前提下，删除父表sid=2数据
SQL> delete from sub where sid=2;
已删除 1 行。
-- 级联删除后两表数据
SQL> select * from student3;
STUNO STUNAME STUADDRESS SUBID
---------- -------------------- ---------------------------------------- ----------
1 zs 1
2 ls 1
SQL> select * from sub;
SID SNAME
---------- --------------------
1 java
4.5.2 级联置空：on delete set null
01.级联置空
当删除父表中的数据时，子表会将相对应的那一字段的值设置为Null，其他字段不影响
02.操作
-- 先创建父表sub
create table sub(
sid number(3) unique,
sname varchar2(10)
);
insert into sub values(1,'java');
insert into sub values(2,'python');
-- 再创建子表student3，同时设置外键+级联删除
create table student3(
stuno number(3) ,
stuname varchar2(10) ,
stuaddress varchar2(20) ,
subid number(3) ,
constraint FK_student3_sub foreign key(subid) references sub(sid) on delete set null
);
insert into student3(stuno,stuname,subid) values(1,'zs',1);
insert into student3(stuno,stuname,subid) values(2,'ls',1);
insert into student3(stuno,stuname,subid) values(3,'ww',2);
-- 子表存在subid=2数据的前提下，删除父表sid=2数据
SQL> delete from sub where sid='2';
已删除 1 行。
-- 级联删除后两表数据
SQL> select * from student3;
STUNO STUNAME STUADDRESS SUBID
---------- -------------------- ---------------------------------------- ----------
1 zs 1
2 ls 1
3 ww
SQL> select * from sub;
SID SNAME
---------- --------------------
1 java