MySQL练习(二):多表查询

2022-09-26 07:55:05

创建相应的表,以及添加数据:

CREATE TABLE `stu` (
  `sid` int(11) DEFAULT NULL,
  `sname` varchar(25) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(6) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `groupLeaderId` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

往 stu 表中添加数据:
INSERT INTO `stu` VALUES ('1001', '张三', '20', '男', '72', '1', '1003');
INSERT INTO `stu` VALUES ('1002', '李四', '15', '女', '78', '1', '1003');
INSERT INTO `stu` VALUES ('1003', '王五', '95', '男', '99', '1', '1010');
INSERT INTO `stu` VALUES ('1004', '赵六张', '65', '女', '60', '1', '1007');
INSERT INTO `stu` VALUES ('1005', '周七', '55', '男', '78', '3', '1007');
INSERT INTO `stu` VALUES ('1006', '茅十八', '75', '女', '96', '3', '1007');
INSERT INTO `stu` VALUES ('1007', '张三丰', '40', '男', '85', '3', '1010');
INSERT INTO `stu` VALUES ('1008', '李四方', '45', '女', '90', '2', '1010');
INSERT INTO `stu` VALUES ('1009', '艾三弗森', '45', '', '35', '4', '1008');
INSERT INTO `stu` VALUES ('1010', '三欧文', '35', '女', '49', '2', '1008');


创建 class 表:
CREATE TABLE `class` (
  `cid` int(11) DEFAULT NULL,
  `cname` varchar(255) COLLATE utf8_bin NOT NULL,
  `caddress` varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

在 class 中添加数据: 
INSERT INTO `class` VALUES ('1', 'BigData', '102');
INSERT INTO `class` VALUES ('2', 'HTML', '103');
INSERT INTO `class` VALUES ('3', 'VR', '104');
INSERT INTO `class` VALUES ('4', 'Java', '105');

一: 联合查询 - 合并结果集

将两表的查询结果纵向拼接在一起
union / union all
拼接的两个表的字段个数,类型,顺序保证一致
union : 在拼接时会去重
union all : 将两表的全部数据都拼接

select sid,sname from stu
union all
select cid,cname from class;

二: 连接查询

表和表之间的关系

连接查询就是将多个表多行数据相乘 —> 笛卡尔积

三: 内连接

内连接语法:

select * from 表一 inner join 表二 关联条件
内连接只会保留完全符合关联条件的数据

没有关联条件,会产生笛卡尔积
select * from stu inner join class;
select * from stu inner join class on stu.cid = class.cid;

查询每个学生的信息,包括班级的全部信息
查询学生名称,学生成绩,班级名称,班级地址
select sname,score,cname,caddress from stu inner join class on stu.cid = class.cid;

查询学号为1007的学生名称,学生成绩,班级名称,班级地址
给表取别名
select sname,score,cname,caddress from stu s inner join class c on s.cid = c.cid where s,sid = 1007;

内连接可以简写:
select * from stu innerjoin class on stu.cid = class.cid;
简写成
select * from stu,class where stu.cid = class.cid;

四: 外连接

外连接: 会保留不满足条件的信息

左外连接: left outer join on
作用: 会保留左表中不符合条件的数据

右外连接: right outer join on
作用: 会保留右表中不符合条件的数据

ps:一般outer关键字可以省略

查询全部学生的信息,如果有班级信息,一并查出:
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;

五: 子查询

子查询就是嵌套查询。
一般子查询出现在:
from后:当做一张表使用
where后:当做条件使用
select 后

查询与张三同一个班级的学生。
	1.查询张三的班级
	select cid from stu where sname ='张三';
	2.查村一班的学生
	select * from stu where cid =1;
	合并1,2SQL语句:
	select * from stu where cid =(select cid from stu where sname ='张三');

成绩高于3号班级所有人的学生信息
	1.找到三班最高分
	select MAX(score) from stu where cid = 3;
	2,找成绩比96高的学生	
	select * from stu where score > 96;
	合并:
	select * from stu where score > (select MAX(score) from stu where cid =3);


有2个以上直接组员的学生信息:
	1.根据组长编号分组,计算分组人数,保留 > 2 的组
	select grouppLeaderId,count(sid) from stu group by groupLeaderId having count(sid) >2;	
	2.查询学号1007,1010学生的信息
	select * from stu where sid in(1007,1010);
	合并1.2SQL	
	select * from stu where sid in(select groupLeaderId from stu group by groupLeaderId having count(sid) > 2);

自连接

自连接: 自己连接自己

s1挡组长,s2当做组员
select s1.sid,s1.sname from stu s1 ,stu s2 where s1.sid = s2.groupLeaderId = s2.sid group by s2.sid having count(s2.sid) >2;

stu s1 当做组员, stu s2 组长
select s2.sid,s2.sname from stu s1, stu s2 where s1.groupLeaderId = s2.sid group by s2.sid having count(s2.sid) > 2;

求1008学生编号.姓名,组长编号和组长姓名
	1.找到1008学生的组长的编号
	select groupLeaderId from stu where sid = 1008;
	2.找到1010的编号和姓名
	select sid,sname from stu where sid = 1010;
	3.找1008编号和姓名
	select sid,sname from stu where sid = 1008;
	合并:
	select s.sid,s.sname,b.sid,b.sname from stu s,(select sid,sname from stu where sid = (select groupLeaderId from stu where sid =1008))b where s.sid =1008;

自链接,s1 做学生表,s2座组长表
s1 的组长是s2中的学生
select s1.sid,s1.sname,s2.sid,s2.sname from stu s1, stu s2 where s1.groupLeaderId = s2.sid and s1.sid = 1008;

自连接,s1做组长,s2做学生
select s2.sid,s2.sname,s1.sid,s1.sname from stu s1,stu s2 where s2.groupLeaderId = s.sid and s2.sid = 1008;

码农公寓