2020-08-10

2020-08-10
mysql

竖向拼接(联合查询):

union :将俩表数据联合输出,去除重复数据
union all:将俩表的数据全部输出,无论重复不重复

使用注意事项:
1)要拼接的俩虚拟表的字段数量要一致
2)要拼接的拉虚拟表字段的数据类型要一致
表a

表b

SELECT a.aid,a.aname FROM a UNION ALL SELECT b.bid,b.bname FROM b;

运行结果:

连接查询 - 横向拼接

内连接
SELECT 字段1,字段2,... FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段；
说明:on后的字段是 俩表关联关系字段
特点:
1)将俩表数据横向拼接在一张虚拟表中
2)只会保留on后关联的数据,其他不保留
3)如果没有on后的条件,会出现[笛卡尔积],即出现一些不符合需求的数据,
所以一定要有on后的条件,过滤掉其他数据
简写格式

SELECT 字段1,字段2,...FROM 表1,表2 WHERE 表2.字段 = 表1.字段；

-- 内连接
SELECT * FROM sty INNER JOIN class ON stu.id = class.cid;
-- 简化写法
SELECT * FROM stu,class WHERE stu.cid = class.cid;
-- 使用别名
SELECT * FROM stu s,class c WHERE s.cid = c.cid;
-- 查询学生id,姓名,班级名称和地址
SELECT
	s.sid,
	s.sname,
	s.cid,
	c.caddress
FROM
	stu s,
	class c
WHERE
	s.cid = c.cid;

– 查询成绩大于60的学生id,姓名,成绩,班级名称和地址,并按照班级编号排序

SELECT
	s.sid,
	s.sname,
	s.cid,
	c.caddress
FROM
	stu s,
	class c
WHERE
	s.cid = c.cid
AND s.score > 60
ORDER BY
	cid;

stu表

class表

外连接
内连接是只保留符合on后关联田间的数据,但是这样会损失一些数据.
比如class表的[5 UI 106]
外连接就可以实现 保留某些部分和on后关联条件的数据

– 外连接 :保留某些不符合on后关联条件的数据
– 左外连接:

SELECT * FROM 表1 LEFT OUTER JOIN 表2 表1.字段 = 表2.字段;

关键词左侧[表1]中如果没有数据不满足on后的条件,也可以保留
右外连接

SELECT * FROM 表1 RIGHT OUTER JOIN 表2 on 表1.字段 = 表2.字段;

关键词右侧表[表2]中如果没有数据不满足on后的条件,也可以保留

-- 左外连接
SELECT * FROM stu s LEFT OUTER JOIN class c on s.cid = c.cid;
-- 右外连接
SELECT * FROM stu s RIGHT OUTER JOIN class c on s.cid = c.cid;
-- 简写 :可以省略outer
SELECT * FROM stu s LEFT JOIN class c ON s.cid = c.cid;

左外连接

右外连接

– 查询学生id,姓名,班级名称和地址,如果学生没有对应的班级,学生信息保留

SELECT
	s.sid,
	s.sname,
	s.cid,
	c.caddress
FROM
	stu s
LEFT JOIN class c ON s.cid = c.cid;

– 查询成绩大于60的学生id,姓名,成绩,班级名称和地址,并按照班级编号排序

SELECT
	s.sid,
	s.sname,
	s.score,
	c.cname,
	c.caddress
FROM
	stu s
LEFT JOIN
	class c
ON
	s.cid  = c.cid
WHERE
	s.score > 60
ORDER BY
	s.cid ;

子查询-嵌套查询
将一个select语句运行结果做一张表,作为一个新的表去使用,获得当作条使用,
1)查询出的虚拟表可以放在from后面当表用
2)查询出的虚拟表可以放在where后面当条件用
– 虚拟表当作表,放在from后面使用

SELECT
	*
FROM
	(
		SELECT
			sid,
			sname,
			score
		FROM
			stu
		WHERE
			gender = '男'
	) a
WHERE
	a.score > 80;

– 虚拟表当作条件,放在where后面使用

SELECT * FROM stu WHERE sid IN(SELECT sid FROM stu WHERE gender = '男');

SELECT * FROM stu WHERE sid = (SELECT sid FROM stu WHERE sname = '周七');

子查询可以当作条件,放在where后使用
但是需要注意,where的运算符
如果是= >等等符号后必须跟一个值的,那么子查询就必须是返回一个值
如果是in那么子查询就可以返回多个值

---

子查询：嵌套查询

– 查询男生成绩大于80的id,姓名,成绩

SELECT sid,sname,score FROM stu WHERE score>80 AND gender = '男';

– 子查询完成
– 1）先找出男生的id,姓名,成绩

SELECT sid,sname,score FROM stu WHERE gender = '男';

– 2）从上述表中找到成绩大于80的

SELECT
	*
FROM
	(
		SELECT
			sid,
			sname,
			score
		FROM
			stu
		WHERE
			gender = '男'
	) a
WHERE
	score > 80;

– 选出编号为1005的学生信息

SELECT * FROM stu WHERE sid = 1005;

– 选出姓名为周七的所有信息

SELECT * FROM stu WHERE sname = '周七'; 
SELECT * FROM stu WHERE sid IN(SELECT sid FROM stu WHERE gender = '男');
select * FROM stu where sid = (SELECT sid FROM stu WHERE sname = '周七');

– 查询与张三同一个班级的学生

SELECT * FROM stu WHERE cid = (SELECT cid FROM stu WHERE sname = '张三');

– 1找到张三的班级编号

SELECT cid FROM stu WHERE sname = '张三';

– 2根据班级编号涨到其他学生

SELECT * FROM stu WHERE cid = 1;

– 合并

SELECT * FROM stu WHERE cid = (SELECT cid FROM stu WHERE sname = '张三');

– 成绩高于3号班级所有人的学生信息
– 1查询3号班级的最高成绩

SELECT MAX(score) FROM stu WHERE cid = 3;

– 查询成绩大于3号班级的学生信息

SELECT * FROM stu WHERE score > (SELECT MAX(score) FROM stu WHERE cid = 3);

– 有两个组员以上的的学生信息
– 1 找到所有组长

SELECT groupLeaderId FROM stu GROUP BY groupLeaderId;

– 找到组员数>2的组长的学生编号

SELECT groupLeaderId FROM stu GROUP BY groupLeaderId HAVING COUNT(groupLeaderId)>2; 

– 查询组员数>2的组长的学生信息

SELECT
	*
FROM
	stu
WHERE
	sid IN (
		SELECT
			groupLeaderId
		FROM
			stu
		GROUP BY
			groupLeaderId
		HAVING
			COUNT(groupLeaderId) > 2
	);

– 求1008学生编号,姓名,组长编号和组长姓名
– 查询1008学生编号的组长编号

SELECT groupLeaderId FROM stu WHERE sid = 1008;

– 查询 组长id 为1010的学生姓名

SELECT sid,sname from stu WHERE sid = 1010;

– 查询学生编号为1008的学生姓名

SELECT sid,sname from stu WHERE sid =1008;

– 合并 从学生编号为1008的表中选取 sid sname,
– 从学生编号为1008的学生组长的表中选取组长sid,sname

SELECT
	s.sid,
	s.sname,
	g.sid,
	g.sname
FROM
	(
		SELECT
			sid,
			sname
		FROM
			stu
		WHERE
			sid = 1008
	) s,
	(
		SELECT
			sid,
			sname
		FROM
			stu
		WHERE
			sid = 1010
	) g
WHERE
	s.sid = 1008;

– 查询每个学生成绩大于60且成绩总和小于200的班级编号以及成绩和并根据成绩和降序
– 查询每个班级中成绩的最小值

SELECT
	cid,
	SUM(score)
FROM
	stu
GROUP BY
	cid
HAVING
	SUM(score) < 200
AND MIN(score) > 60
ORDER BY
	SUM(score) DESC;

每日美文：我们努力奋斗，不是为了改变世界，——是为了不让世界改变我们