多表查询
数据准备
drop table if exists students;
CREATE table students(
studentNo VARCHAR(10) PRIMARY key, /*学号，主键*/
name VARCHAR(10), /*名字*/
sex VARCHAR(2), /*性别*/
hometown varchar(20), /*家乡*/
age tinyint, /*年龄*/
class VARCHAR(10), /*班级*/
card VARCHAR(20) /*身份证*/
)
insert into students values
('001','林一','女','北京','25','1班','111111111111111111'),
('002','林二','男','北京','30','2班','111111111111111112'),
('003','黄三','女','武汉','15','3班','111111111111111113'),
('004','无名','男','长沙','25','2班','111111111111111114'),
('005','林檎杰','女','北京','35','2班','111111111111111115'),
('006','黄檎','男','长沙','25','1班',null),
('007','张杰','女','武汉','15','3班','');
create table courses (
courseNo int UNSIGNED PRIMARY key auto_increment,/*课程号，主键，自增长*/
coursename varchar(10) /*课程名称*/
)
insert into courses (coursename) values
('数据库'),
('html'),
('linux'),
('系统测试'),
('单元测试'),
('测试过程'),
('python');
create table scores (
id int UNSIGNED primary key auto_increment,/*id，主键自增长*/
courseNo int UNSIGNED ,/*课程号，与courses表中的courseNO相关联*/
studentNo varchar(10),/*学号。与students表中的studentNo相关联*/
score tinyint /*成绩*/
)
insert into scores (courseNo,studentNo,score) values
(1,'001',90),
(1,'002',75),
(2,'002',98),
(3,'001',86),
(3,'003',80),
(4,'004',79),
(5,'005',96),
(6,'006',80);
create table areas (
id int primary key,
name varchar(20),
pid int
);
insert into areas values
('130100','石家庄市',NULL),
('130400','邯郸市','130000'),
('130600','保定市','130000'),
('130700','张家口市','130000'),
('130800','承德市','130000'),
('440000','广东省',NULL),
('440300','深圳市','440000'),
('440100','广州市','440000'),
('440500','汕头市','440000'),
('440600','佛山市','440000'),
('440200','韶关市','440000');
连接概念
当查询结果来源于多张表时，需要将多张表连接成一个大的数据集，在选择合适的结果返回。
连接查询可以通过连接运算符（连接条件）可以实现多个表查询。
内连接：查询的结果只显示两个表中满足连接条件的部分。
左连接：查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null填充。
右连接：查询的结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null填充。
内连接
双表内连接
语法一、标准语法
select * from 表1 inner join 表2 on 表1.字段=表2.字段
找两个表中相同的部分
students表和scores表关联查询
select * from students
inner join scores on students.studentNo = scores.studentNo;
语法二、隐式内连接
select * from 表1，表2 where 表1.字段 = 表2.字段
select * from students,scores
where students.studentNo = scores.studentNo;
select name,courseNo,score from students
inner join scores on students.studentNo = scores.studentNo;
配合别名使用
select name 姓名,courseNo 课程号,score 分数 from students stu
inner join scores sc on stu.studentNo = sc.studentNo;
select * from scores sc
inner join courses c on sc.courseNo = c.courseNo
带有where的内连接
select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 where 条件
select name,courseNo,score from students stu
inner join scores sc on stu.studentNo = sc.studentNo
where stu.name = '林一';
带有逻辑运算符的内连接
select name,score from students stu
inner join scores sc on stu.studentNo = sc.studentNo
where stu.name = '林一'
and sc.score < 90;
多表内连接
写SQL三步法
1、搭框架
写出基本的select语句搭建起来，如果有多表，则把响应的多表也联合进来
2、看条件
决定where后面的具体条件
3、显示的字段
select后面到底要显示什么字段
select * from students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses c on sc.courseNo = c.courseNo;
select name 姓名,score 成绩,coursename 课程名 from students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses c on sc.courseNo = c.courseNo
where c.coursename = 'linux';
带有order by的内连接
select name 姓名,score 成绩,coursename 课程名 from students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses c on sc.courseNo = c.courseNo
where sex = '男'
order by score desc limit 1;
左连接
双表左连接
select * from 表1 left join 表2 on 表1.字段=表2.字段
左表特有的右表没有赋空值
select * from students stu
left join scores sc on stu.studentNo = sc.studentNo
右连接
select * from 表1 right join 表2 on 表1.字段=表2.字段
右表特有的左表没有赋空值
select * from scores sc
right join courses c on sc.courseNo = c.courseNo;
多表查询同名字段的处理
如果一条select要用到多个表，表中有同名字段，就需要以 表名.字段名 加以区分，否则会报错
select students.studentNo from students
inner join scores on students.studentNo = scores.studentNo;
自关联
同一张表做连接查询，一定要找到同一张表可关联的不同字段
select * from areas where pid is null;
select * from areas a1
inner join areas a2 on a1.id=a2.pid
where a1.name = '广东省';
子查询
在一个select语句中嵌入另一个select语句
子查询是嵌套到主查询里面的
子查询做为主查询的数据源或者条件
子查询是独立可以单独运行的查询语句
主查询不能独立运行，依赖子查询的结果
标量子查询——子查询返回结果只有一行一列
select * from students where age > (select avg(age) from students);
列子查询——查询返回一列多行
select * from scores
where studentNo in
(select studentNo from students where age = 30);
表级子查询——子查询结果返回多行多列
select * from
(select * from students where sex = '女') stu
inner join scores sc on stu.studentNo = sc.studentNo;
练习
create table departments (
deptid int(10) primary key, /*部门编号*/
deptname varchar(20) not null /*部门名称*/
)
insert into departments values
('1001','市场部'),
('1002','测试部'),
('1003','开发部')
create table employees (
empid int primary key , /*职工编号*/
empname varchar(20) not null, /*姓名*/
sex varchar(4) default null, /*性别*/
deptid int default null, /*部门编号*/
jobs varchar(20) default null, /*岗位名称*/
politicalstatus varchar(20) default null, /*政治面貌*/
leader int default null /*上级领导的职工编号*/
)
insert into employees values
('1','林一','女','1003','开发','群众','9'),
('2','林二','男','1003','开发','群众','9'),
('3','林三','男','1002','测试','团员','4'),
('4','林四','男','1002','测试经理','党员',null),
('5','林五','女','1002','测试','党员','4'),
('6','林六','女','1001','市场','党员','12'),
('7','林七','男','1001','市场','团员','12'),
('8','林八','女','1002','测试','群众','4'),
('9','林九','男','1003','开发经理','党员',null),
('10','林十','女','1003','开发','团员','9'),
('11','林十一','男','1002','测试','团员','4'),
('12','林十二','男','1001','市场经理','党员',null),
('13','林十三','男','1001','市场','群众','12');
create table salary(
sid int primary key,
empid int not null, /*职工编号*/
salary int not null /*工资*/
)
insert into salary values
('1','7','2100'),
('2','6','2000'),
('3','12','5000'),
('4','9','1999'),
('5','10','1900'),
('6','1','3000'),
('7','2','5500'),
('8','5','2000'),
('9','3','1500'),
('10','8','4000'),
('11','11','2600'),
('12','4','5300');
1、列出男职工的总数和女职工的总数
select sex,count(*) from employees group by sex;
2、列出非党员职工的总数
select count(*) from employees where politicalstatus != '党员';
3、列出所有职工工号，姓名以及所在部门名称
select empid 职工工号,empname 姓名, deptname 部门名称
from employees emly
inner join departments dept on emly.deptid = dept.deptid;
4、列出所有职工工号，姓名和对应工资
select em.empid 职工工号,empname 姓名,salary 工资
from employees em
inner join salary sa
on em.empid = sa.empid
order by em.empid;
5、列出领导岗的姓名以及所在部门名称
select empname 姓名,deptname 部门 from employees em
inner join departments dept
on em.deptid = dept.deptid
where leader is null;
6、列出职工总人数大于4的部门号和总人数
select deptid,count(*) from employees
group by deptid HAVING count(*) > 4;
7、列出职工总人数大于4的部门号和部门名称
select em.deptid,deptname from employees em
inner join departments dept
on em.deptid = dept.deptid
group by em.deptid having count(*) > 4;
8、列出开发部和测试部的职工号，姓名
select empid 职工号,empname 姓名,deptname 部门
from employees em
inner join departments dept
on em.deptid = dept.deptid
where deptname != '市场部';
9、列出市场部所有职工姓名和工资，包括没有工资的职工姓名
select em.empid 职工号,empname 姓名,deptname 部门,salary
from employees em
inner join departments dept
on em.deptid = dept.deptid
left join salary sa
on em.empid = sa.empid
where deptname = '市场部'
order by em.empid;
10、显示所有职工姓名和工资，包括没有工资的职工姓名
select empname,salary from employees em
left join salary sa
on em.empid = sa.empid
order by em.empid;
11、求名字包含‘十’的所有职工工资总和
select sum(salary) from employees em
inner join salary sa
on em.empid = sa.empid
where empname like '%十%';
756
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
