概述:指从多张表中查询数据
笛卡尔积:笛卡尔乘积是指在数学中,两个集合A集合和B集合的所有组合情况。
一对多
关系:一个部门对应多个员工,一个员工对应一个部门
实现:在多的一方建立外键,指向一的一方的主键
多对多
关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
?
create table student (
id int primary key auto_increment comment '主键id',
name varchar(10) comment '姓名',
num varchar(10) comment '学号'
) comment '学生表';
insert into student values(null, 'wyx', '1234'), (null, 'sxx', '4321');
create table course (
id int primary key auto_increment comment '主键id',
name varchar(10) comment '课程名称'
) comment '课程表';
insert into course values(null, 'java'), (null, 'python'), (null, 'go'), (null, 'c++');
create table student_course(
id int primary key auto_increment comment '主键',
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);
?
一对一
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表上,其他详细字段放在另一张表中,以提示效率
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的unique
create table tb_user(
id int primary key auto_increment comment '主键id',
name varchar(10) comment '姓名',
age int comment '年龄',
gender char(1) comment '1:男, 2:女',
phone char(1) comment '手机号'
) comment '用户基本信息表';
insert into tb_user(id, name, age, gender, phone) VALUES (null, 'sxx', 45, '1', '1'),(null, 'wyx', 23, '2', '1');
create table tb_user_edu(
id int primary key auto_increment comment '主键id',
degree varchar(20) comment '学历',
major varchar(50) comment '专业',
primarysch varchar(50) comment '小学',
middlesch varchar(50) comment '中学',
universch varchar(50) comment '大学',
userid int unique comment '用户id',
constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';
insert into tb_user_edu(id, degree, major, primarysch, middlesch, universch, userid) values
(null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院',1 ),
(null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院',2 );
select * from tb_user_edu,tb_user where tb_user.id = tb_user_edu.id;
隐式内连接
select 字段列表 from 表1,表2 where 条件...;
显示内连接
select 字段列表 表1 [inner] join 表2 on 连接条件..;
#查询学生姓名以及学生的学校
select tb_user.name, tb_user_edu.universch from tb_user, tb_user_edu where tb_user.id = tb_user_edu.id;
#显示内连接查询
select t.name, e.universch from tb_user_edu e join tb_user t on e.userid = t.id;
左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件..;
相当于查询表1的所有数据包含表1和表2交集部分的数据
select tb_user.*,tb_user_edu.universch from tb_user left join tb_user_edu on tb_user.id = tb_user_edu.userid;
右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件..;
相当于查询表2的所有数据包含表1和表2交集部分的数据
select tb_user.name,tb_user_edu.* from tb_user right join tb_user_edu on tb_user.id = tb_user_edu.userid;
select 字段列表 from 表a 别名a join 表a 别名b on 条件...;
select a.id,b.name from tb_user a join tb_user b on a.id = b.phone;
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
select 字段列表 from 表A...
union [all]
select 字段列表 from 表B...;
#将薪资低于5000的员工,和年龄大于50岁的员工全部查询出来
select * from emp where salary < 5000
union
select * from emp where age > 50;
概念:SQL语句中嵌套select语句,称为嵌套查询,又称子查询
select * from t1 where column1 = (select column1 from t2);
标量子查询
子查询返回的结果是单个值,最简单的形式,这种子查询称为标量子查询。
#查询销售部门id
select id from emp where name = '销售部';
#根据销售部部门ID,查询员工信息
select * from emp where dept_id = (select id from emp where name = '销售部')
列子查询
子查询返回的结果是一列,这种子查询称为列子查询
常用操作符:in,not in ,any, some, all
#查询销售部和市场部的部门id
select id from dept where name = '销售部' or name = '市场部';
#根据id查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
行子查询
子查询返回的结果是一行,这种子查询称为行子查询
#查询wyx的薪资及直属领导相同的员工信息
a查询wyx的薪资及直属领导
select salary,managerid from emp where name = 'wyx' ;
b查询于wyx的薪资及直属领导相同的员工信息
select * from emp where (salsry,managerid) = (select salary,managerid from emp where name = 'wyx');
表子查询
子查询返回的结果是多行多列,这种子查询称为表子查询
常用操作符:in
#查询与wyx,sxx的职位和薪资相同的员工信息
select salary,job from emp where name = 'wyx' or name = 'sxx';
select * from emp where (job,salary) in (select salary,job from emp where name = 'wyx' or name = 'sxx');