MySQL表的增删查改（进阶）

---

前言

---

一、新增-插入查询结果

语法

INSERT INTO table_name [(column [, coiumn ...])] SELECT ...

案例：创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的
学生数据复制进来，可以复制的字段为name、qq_mail

-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
   id INT primary key auto_increment,
   name VARCHAR(20) comment '姓名',
   age INT comment '年龄',
   email VARCHAR(20) comment '邮箱',
 sex varchar(1) comment '性别',
 mobile varchar(20) comment '手机号'
);
-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;

二、查询

OLTP（on-line transaction processing）翻译为联机事务处理， OLAP（On-Line Analytical Processing）翻译为联机分析处理，从字面上来看OLTP是做事务处理，OLAP是做分析处理。从对数据库操作来看，OLTP主要是对数据的增删改，OLAP是对数据的查询。

OALP 1.聚合查询 2.联表查询 3.子查询

1.聚合查询

1.1聚合函数

COUNT([DISTINCT] expr) 返回查询到的数据的 数量
SUM([DISTINCT] expr) 返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr) 返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr) 返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr) 返回查询到的数据的 最小值，不是数字没有意义

CREATE TABLE student (
    id INT,
    sn INT comment '学号',
    name VARCHAR(20) comment '姓名',
    qq_mail VARCHAR(20) comment 'QQ邮箱'
);

INSERT INTO student VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');

INSERT INTO student (id, sn, name) VALUES 
	(102, 20001, '曹孟德'),
	(103, 20002, '孙仲谋');
    
CREATE TABLE exam_result (
	id INT,
	name VARCHAR(20),
	chinese DECIMAL(3,1),
	math DECIMAL(3,1),
	english DECIMAL(3,1)
);

-- 插入测试数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
	(1,'唐三藏', 67, 98, 56),
	(2,'孙悟空', 87.5, 78, 77),
	(3,'猪悟能', 88, 98, 90),
	(4,'曹孟德', 82, 84, 67),
	(5,'刘玄德', 55.5, 85, 45),
	(6,'孙权', 70, 73, 78.5),
	(7,'宋公明', 75, 65, 30);
    
SELECT COUNT(*) FROM student;
SELECT COUNT(*) FROM exam_result;
SELECT count(1) from student;
select count(name) from student;
select count(qq_mail) from student;	-- 如果是 null，不算

-- sum/avg/max/min 只能用于 数字类型的字段
-- 聚合的意思是竖着计算的
select sum(math) from exam_result;
-- 要计算某个人的语文 + 数学 + 英语，作为对比，这种不叫聚合
select chinese + math + english from exam_result where id = 1;

select avg(math) from exam_result;
select max(math) from exam_result;
select min(math) from exam_result;

1.2GROUP BY 子句

可以多字段聚合

create table emp2(
	id int primary key auto_increment,
	name varchar(20) not null,
    company varchar(20) not null,
    depart varchar(20) not null,
	role varchar(20) not null,
	salary numeric(11,2)
);

insert into emp2(name, company, depart, role, salary) values
	('马云', '阿里巴巴', '服务部', '服务员', 1000.20),
	('牛云', '阿里巴巴', '服务部', '服务员', 1000.20),
	('虎云', '阿里巴巴', '服务部', '服务员', 1000.20),
	('龙云', '阿里巴巴', '服务部', '服务员', 1000.20),
	('兔云', '阿里巴巴', '服务部', '服务员', 1000.20),
	('蛇云', '阿里巴巴', '销售部', '销售员', 1000.20),
	('羊云', '阿里巴巴', '销售部', '销售员', 1000.20),    
	('马化腾', '腾讯', '陪玩部', '游戏陪玩', 2000.99),
	('牛化腾', '腾讯', '游戏部', '游戏开发', 2000.99),    
	('孙悟空', '西游记', '取经组', '游戏角色', 999.11),
	('猪无能', '西游记', '取经组', '游戏角色', 333.5),
	('沙和尚', '西游记', '取经组', '游戏角色', 700.33),
	('白骨精', '西游记', '妖怪组', '抢劫人员', 700.33),
	('隔壁老王', '启明星', '企划部', '董事长', 12000.66);
    
select company, count(*) from emp2 group by company;
select company, depart, count(*) from emp2 group by company, depart;
select company, depart, role, count(*) from emp2 group by company, depart, role;
//聚合之后也可以排序
select company, depart, role, count(*) a from emp2 group by company, depart, role order by a;

1.3HAVING关键字

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING

再聚合后的数据中，再做一次数据过滤
having再聚合之后，where在聚合之前

select 
	company, depart, role, count(*) c 
from emp2 
where salary > 500
group by depart, role, company
having c < 2
order by c;

2.联合查询

2.1联合查询-多表查询

多表查询是对多张表的数据取笛卡尔积

create table users (
	uid int primary key auto_increment,
    name varchar(45) not null
);

create table articles (
	aid int primary key auto_increment,
    author_id int not null,
    title varchar(45) not null
);

insert into users (name) values ('小红'), ('小李'), ('小张'), ('小王');

insert into articles (author_id, title) values
	(1, '论MySQL的使用'),
    (1, '论Java的使用'),
    (2, '疫情的生活'),
    (3, '红烧肉'),
    (5, '没有作者的一篇文章');
    
-- 在 from 后边直接跟 2 张表（2 张以上也可以）
-- 视为同时从 2 张表中查询数据
select * from users, articles;	-- 一共 20 条数据 = 4 * 5
select * from users join articles;

2.2内连接

-- 添加 联表 条件后，得到的结果才是有意义的
select * from users, articles where users.uid = articles.author_id and users.name = '小红';
select * from users, articles where uid = author_id and users.name = '小红';
select * from users join articles on uid = author_id where users.name = '小红';

-- 以上这些全部是内联
select * from users inner join articles on uid = author_id;		-- inner 可以省略

2.3外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完
全显示我们就说是右外连接。

-- 左外联
select * from users left outer join articles on uid = author_id; 
select * from users left join articles on uid = author_id; 

-- 右外联
select * from users right outer join articles on uid = author_id;
select * from users right join articles on uid = author_id;

2.3.1联合表也可以起名字

-- 给表起名字 
select * from users as u right join articles as a on u.uid = a.author_id;
select * from users as u right join articles a on u.uid = a.author_id;

2.4自连接

自连接是指在同一张表连接自身进行查询。（一张表和自己做连接查询）
案例：
显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

SELECT
 s1.* 
FROM
 score s1
 JOIN score s2 ON s1.student_id = s2.student_id
 AND s1.score < s2.score
 AND s1.course_id = 1
 AND s2.course_id = 3;

2.5子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
查询结果基础上再查询，完全可以把select结果逻辑上视为一张表，继续from这张表

-- 找到计算机比java分数高的同学
-- 1) 先找到所有人的计算机原理成绩
select * from score s join course c on s.course_id = c.id where c.name = '计算机原理';
-- 2) 再找到所有人的 Java 成绩
select * from score s join course c on s.course_id = c.id where c.name = 'Java';
-- 3) 把前两次查询的结果看作两张表做联表   1. 内联  2. student_id 相等

select 
	*
from (
	select student_id, score from score s join course c on s.course_id = c.id where c.name = '计算机原理'
) 计算机原理成绩
join (
	select student_id, score from score s join course c on s.course_id = c.id where c.name = 'Java'
) Java成绩
on 计算机原理成绩.student_id = Java成绩.student_id
where 计算机原理成绩.score > Java成绩.score;

select * from
(
	select 
		company, depart, role, count(*) c 
	from emp2 
	where salary > 500
	group by depart, role, company
) t
where c > 2
order by c;

2.5.1①单行子查询

单行子查询：返回一行记录的子查询
查询与“不想毕业” 同学的同班同学

-- select classes_id from student where name='不想毕业' 这条 sql 得到的结果一定是  1 行、1 列
-- 想象成：先去执行 内部 查询
-- 把查询的结果作为 where classes_id = ? 的具体值，再去执行外部查询
select * from student2 where classes_id=(select classes_id from student where name='不想毕业');

select classes_id from student2 where name='不想毕业';
select * from student2 where classes_id = 1;

2.5.2①多行子查询——[NOT] IN关键字

多行子查询：返回多行记录的子查询
案例：查询“语文”或“英文”课程的成绩信息

-- 内部的查询结果必须是 1 列，可以有多行
-- 先把内部查询完成，再去进行外部查询
select id from course where name='语文' or name='英文';
select * from score where course_id in (4, 6);

-- 使用IN
select * from score where course_id in (select id from course where name='语文' or name='英文');

-- 使用 NOT IN
select * from score where course_id not in (select id from course where name!='语文' and name!='英文');

2.5.2②多行子查询——[NOT] EXISTS关键字

-- 1) 先通过外部的 sql 得到结果
select * from score sco;
-- 2) 将每行的结果，代入到内部的 sql，进行查询
-- 2) 内部的查询，如果能得到结果（行数 > 0），说明满足了 exists 的条件
select cou.id from course cou where (name='语文' or name='英文') and cou.id = 1;
select cou.id from course cou where (name='语文' or name='英文') and cou.id = 2;
select cou.id from course cou where (name='语文' or name='英文') and cou.id = 3;
select cou.id from course cou where (name='语文' or name='英文') and cou.id = 4;
select cou.id from course cou where (name='语文' or name='英文') and cou.id = 5;
select cou.id from course cou where (name='语文' or name='英文') and cou.id = 6;

select * from score where course_id in (4, 6) order by id;

-- 使用 EXISTS
select * 
from score sco 
where exists (
	select sco.id from course cou where (name='语文' or name='英文') and cou.id = sco.course_id
) order by id;

-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou where (name!='语文' and name!='英文') and cou.id = sco.course_id);

2.6 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION
和UNION ALL时，前后查询的结果集中，字段需要一致。

union : 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
案例：查询id小于3，或者名字为“英文”的课程：

select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3 or name='英文';

union all : 该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：查询id小于3，或者名字为“Java”的课程

-- 可以看到结果集中出现重复数据Java
select * from course where id<3
union all
select * from course where name='英文';

练习

牛客——SQL必知必会

-- 2
//去重
select distinct(prod_id) from OrderItems;

-- 13
select order_num, prod_id, quantity
from OrderItems
where quantity >= 100 and prod_id in ('BR01', 'BR02', 'BR03');

-- 17
select prod_name, prod_desc
from Products
where prod_desc not like '%toy%'
order by prod_name;

-- 22
select
    cust_id,
    cust_name,
    -- 转大写
    upper(
        -- 拼接
		concat(
		    -- 截断，从1开始
			substr(cust_contact, 1, 2),
            substr(cust_city, 1, 3)
		)
	) user_login
from Customers;

-- 23
select order_num, order_date
from Orders
where year(order_date) = 2020
and month(order_date) = 1
order by order_date;

-- 24
select sum(quantity) items_ordered
from OrderItems;


--30
select order_num,sum(item_price*quantity) total_price
from OrderItems
group by order_num
having total_price >=1000
order by order_num;

-- 32
-- 单列多行
-- select order_num from OrderItems where item_price >=10
select cust_id
from Orders
where order_num in (
    select order_num from OrderItems where item_price >=10
);

-- 35
select cust_id,total_ordered
from Orders o
join(
    select order_num,sum(item_price *quantity) total_ordered
    from OrderItems
    group by order_num
)t
on o.order_num = t.order_num
order by total_ordered desc;

-- 37
select cust_name,order_num
from Customers c 
join Orders o 
on c.cust_id = o.cust_id
order by cust_name,order_num;

select cust_name,order_num
from Customers c ,Orders o 
where c.cust_id = o.cust_id
order by cust_name,order_num;

-- 43
select cust_name, order_num
from Orders o
right join Customers c 
on o.cust_id = c.cust_id
order by cust_name;

select cust_name, order_num
from Customers c 
left join Orders o
on o.cust_id = c.cust_id
order by cust_name;

-- 44
select prod_name, order_num
from Products p
left outer join OrderItems oi
on p.prod_id = oi.prod_id
order by prod_name;


-- 45
select prod_name, ifnull(orders, 0) orders
from Products p
left join
(
    select prod_id, count(*) orders
    from OrderItems
    group by prod_id
) oi
on p.prod_id = oi.prod_id
order by prod_name;

-- 47
select prod_id, quantity from OrderItems where quantity = 100
union
select prod_id, quantity from OrderItems where prod_id like 'BNBG%'
order by prod_id;

-- 48
select prod_id, quantity from OrderItems 
where quantity = 100 or prod_id like 'BNBG%'
order by prod_id;

-- 49
select prod_name from Products
union
select cust_name prod_name from Customers
order by prod_name;

-- 50
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL'
ORDER BY cust_name; 

总结