1、多表关系

概述:项目开发中,在进行数据库表结构操作设计时,会根据业务需求及业务模板之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:

一对多(多对一)

多对多

一对一

 · 一对多(多对一)

案例:部门与员工的关系(一个部门对应多个员工,一个员工对应一个部门)

实现:在多的一方建立外键,指向一的一方的主键

· 多对多

案例:学生与课程的关系(一个学生可以选修多门课程,一门课程可以共多个学生选择)

实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键

create table student(

id int primary key auto_increment comment '主键ID',

name varchar(10) comment '姓名',

no varchar(10) comment '学号'

)comment '学生表';

insert into student values(null,'黛丽丝','2000100101'),(null,'谢逊','2000100102'),(null,'殷天正','2000100103'),(null,'韦一笑','2000100104');

create table course(

id int primary key auto_increment comment '主键ID',

name varchar(10) comment '课程名称'

)comment '课程表';

insert into course values(null,'java'),(null,'php'),(null,'mysql'),(null,'hadoop');

create table student_course(

id int primary key auto_increment comment '主键ID',

studentid int not null comment '学生ID',

courseid int not null comment '课程ID',

constraint fk_courseid foreign key (courseid) references course (id),

constraint fk_studentid foreign key (studentid) references student (id)

)comment '课程中间表';

insert into student_course values(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

·  一对一

案例:用户与用户详情的关系(一对一关系多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率)

实现:在任一方加入外键,关联另一方的主键,并且设置外键为唯一的(UNIQUE)

create table tb_user(

id int primary key auto_increment comment '主键ID',

name varchar(10) comment '姓名',

age int comment '年龄',

gender varchar(1) comment '性别',

phone char(11) comment '电话'

)comment '用户基本信息表';

create table tb_user_edu(

id int primary key auto_increment comment '主键ID',

degree varchar(20) comment '学历',

major varchar(50) comment '专业',

primaryschool varchar(50) comment '小学',

middleschool varchar(50) comment '中学',

university varchar(50) comment '大学',

userid int unique comment '用户ID',

constraint fk_userid foreign key (userid) references tb_user(id)

)comment '用户教育信息表';

insert into tb_user values

(null,'黄渤',45,'1','18903944771'),

(null,'冰冰',32,'2','18903955771'),

(null,'马云',55,'1','17719224870'),

(null,'李彦宏',50,'1','15538655111');

insert into tb_user_edu values

(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),

(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),

(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),

(null,'本科','应用数学','阳泉区第一小学','阳泉区第一中学','清华大学',4);

2、多表查询概述 

 概述:指从多张表中查询数据

案例:我们用员工与所属部门来作为案例

select * from emp,dept;

 但问题是我们明明只有五条数据却显示了25行信息,这种现象称为笛卡尔积。

笛卡尔积:在数学中两个集合,A集合和B集合的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)

select * from emp,dept where emp.dept_id=dept.id;

 多表查询分类

· 连接查询

        内连接:相当于查询A、B交集部分的数据

        外连接:

                   左外连接:查询左表所有数据,以及两张表交际部分数据

                   右外连接:查询右表所有数据,以及两张表交际部分数据

        自连接:当前表与自身的连接查询,自连接必须使用表别名

· 子查询

3、内连接查询 

内连接查询语法:

隐式内连接:

 SELECT 字段列表 FROM 表1,表2 WHERE 条件 ...;

显示内连接: 

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;

内连接查询的是两张表的交集部分 

#内连接演示

#1、查询每一个员工的姓名,及关联部门的名称(隐式内连接实现)

#连接条件:emp.dept_id=dept.id

select emp.name,dept.name from emp,dept where emp.dept_id=dept.id;

#2、查询每一个员工的姓名,及关联部门的名称(显示内连接实现)--关键字:INNER JOIN ... ON ...

select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;

4、外连接查询 

外连接查询语法:

左外连接:

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;

查询的是左表的所有数据包含两表交集部分的数据 

右外连接: 

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;

查询的是右表的所有数据包含两表交集部分的数据 

#左外连接和右外连接

#1、查询emp表的所有数据,和对应部门的信息(左外连接)

select * from emp left outer join dept on emp.dept_id = dept.id;

#2、查询dept表的所有数据,和对应员工信息(右外连接)

select * from dept right outer join emp on dept.id = emp.dept_id;

5、自连接查询

自连接查询语法:

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...; 

自连接查询,可以是内连接查询,也可以是外连接查询。 

#自连接

#1、查询员工及其所属领导的名字

select a.name,b.name from emp a,emp b where a.managerid=b.id;

#2、查询所有员工 emp 及其领导的名字 emp,如果员工没有领导也要查询出来

select a.name,b.name from emp a left join emp b on a.managerid=b.id;

6、联合查询 

 联合查询-union,union all

对于联合查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...

UNION [ALL]

SELECT 字段列表 FROM 表B ...;

#联合查询

#将薪资低于10000的员工,和年龄大于50岁的员工全部查询出来

select * from emp where salary<10000

union all

select * from emp where age>50;

#对查询结果去重

select * from emp where salary<10000

union

select * from emp where age>50;

 总结:对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致;

union all会将全部的数据直接合并在一起,union会对合并之后的数据去重。

7、子查询

 概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。

SELECT * FROM t1 WHERE column1=(SELECT column1 FROM t2);

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT 的任何一个。

根据子查询的结果不同,分为:

标量子查询(子查询结果为单个值)

列子查询(子查询结果为一列)

行子查询(子查询结果为一行)

表子查询(子查询结果为多行多列)

根据子查询位置,分为:WHERE之后、FROM之后、SELECT之后。

标量子查询 

子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。

常用的操作符:=  <>  >  >=  <  <=

#标量子查询

#1、查询研发部所有员工信息

#第一步:查询研发部部门ID

select id from dept where name='研发部';

#第二步:根据研发部部门ID查询员工信息

select * from emp where dept_id=1;

select * from emp where dept_id=(select id from dept where name='研发部');

#2、查询在”杨逍“入职之后的员工信息

#第一步:查询”杨逍“的入职日期

select entrydate from emp where name='杨逍';

#第二步:查询指定日期之后入职员工的信息

select * from emp where entrydate > '2000-11-03';

select * from emp where entrydate >(select entrydate from emp where name='杨逍');

列子查询 

子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。

常用操作符:IN、NOT IN、ANY、SOME、ALL

操作符描述IN在指定的集合范围之内,多选一NOT IN不在指定的集合范围之内ANY子查询返回列表中,有任意一个满足即可SOME与ANY等同,使用SOME的地方都可以使用ANYALL子查询返回列表的所有值都必须满足

#列子查询

#1、查询”总经办“和”研发部“所有员工的信息

#第一步:查询总经办和研发部的部门ID

select id from dept where name='总经办'||name='研发部';

#第二步:根据部门ID,查询员工信息

select * from emp where dept_id in (5,1);

select * from emp where dept_id in(select id from dept where name='总经办'||name='研发部');

#2、查询比研发部所有员工工资都高的员工信息

#第一步:查询研发部部门工资

select id from dept where name='研发部';

select salary from emp where dept_id=1;

select salary from emp where dept_id=(select id from dept where name='研发部');

#第二步:比研发部所有员工工资都高的员工信息

select * from emp where salary > all(select salary from emp where dept_id=(select id from dept where name='研发部'));

#3、查询比研发部其中任意一人工资高的员工信息

#第一步:查询研发部所有员工的工资

select salary from emp where dept_id=(select id from dept where name='研发部');

#第二步:比研发部其中任意一人工资高的信息

select * from emp where salary > any(select salary from emp where dept_id=(select id from dept where name='研发部'));

行子查询 

 子查询的结果是一行(可以是多列),这种查询就称行子查询。

常用操作符:= 、<>、IN、NOT IN

#行子查询

#1、查询与”张无忌“的薪资及所属领导相同的员工信息

#第一步:查询张无忌的薪资及直属领导

select salary,emp.managerid from emp where name='张无忌';

#第二步:查询与”张无忌“的薪资及所属领导相同的员工信息

select * from emp where salary=(select salary from emp where name='张无忌')&& emp.managerid=(select managerid from emp where name='张无忌');

#上面这种方法是标量子查询

select * from emp where (salary,managerid)=(select salary,emp.managerid from emp where name='张无忌');

表子查询 

子查询返回的结果是多行多列,这种子查询称为表子查询。

常用操作符:IN

#表子查询

#1、查询与杨逍与韦一笑的职位和薪资相同的员工信息

#第一步:查询与杨逍与韦一笑的职位和薪资

select job,salary from emp where name='杨逍'||name='韦一笑';

#第二步:查询与杨逍与韦一笑的职位和薪资相同的员工信息

select * from emp where (job,salary) in(select job,salary from emp where name='杨逍'||name='韦一笑');

#2、查询入职日期是”2002-01-01“之后的员工信息,及其部门信息

#第一步:入职信息是”2002-01-01“之后的员工信息

select * from emp where entrydate > '2002-01-01';

#第二步:查询这部分员工,对应的部门信息

select e.*,d.* from (select * from emp where entrydate > '2002-01-01') e left join dept d on e.dept_id=d.id;

 多表查询案例

#多表查询案例

#1、查询员工的姓名、年龄、职位、部门信息。(隐式内连接)

select emp.name,emp.age,emp.job,dept.name from emp,dept where emp.dept_id=dept.id;

#2、查询年龄小于30岁的员工姓名、年龄、职位、部门信息(显示内连接)

select emp.name,emp.age,emp.job,dept.name from emp inner join dept on emp.dept_id=dept.id where emp.age<30;

#3、查询拥有员工的部门ID、部门名称

select distinct dept.id,dept.name from emp,dept where emp.dept_id=dept.id;

#4、查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来

select emp.*,dept.name from emp left outer join dept on emp.dept_id = dept.id where emp.age>40;

#5、查询所有员工的工资等级

#准备工作

create table salarygrade(

grade int,

losal int,

hisal int

)comment'工资等级表';

insert into salarygrade values(1,0,3000),

(2,3001,5000),

(3,5001,8000),

(4,8001,10000),

(5,10001,15000),

(6,15001,20000);

select emp.name,salarygrade.grade from emp,salarygrade where emp.salary>=salarygrade.losal and emp.salary<=salarygrade.hisal;

select emp.name,salarygrade.grade from emp,salarygrade where emp.salary between salarygrade.losal and salarygrade.hisal;

#6、查询研发部所有员工信息及工资等级

select e.*,s.grade from emp e,salarygrade s,dept d where e.dept_id=d.id and e.salary between s.losal and s.hisal and d.name='研发部';

#7、查询研发部员工的平均工资

select avg(emp.salary) from emp,dept where emp.dept_id=dept.id and dept.name='研发部';

#8、查询工资比韦一笑高的员工信息

select * from emp where salary>(select salary from emp where name='韦一笑');

#9、查询比平均薪资高的员工信息

select * from emp where salary >(select avg(salary) from emp where id);

#10、查询低于研发部门平均工资的员工信息

select * from emp where salary<(select avg(salary) from emp,dept where dept.name='研发部' and emp.dept_id=dept.id);

#11、查询所有部门信息,并统计部门员工人数

select d.id,d.name,(select count(*) from emp e where e.dept_id=d.id)'人数' from dept d;

精彩文章

评论可见,请评论后查看内容,谢谢!!!
 您阅读本篇文章共花了: