mysql 合并查询成绩,MySQL练习(二):多表查询

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

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;

标签：多表,cid,s2,MySQL,查询,stu,sid,where,select

来源： https://blog.csdn.net/a1422655169/article/details/112389380