赞
踩
一万两千字的博客,值的细细品味!
更多关于MySQL的内容,请关注本帅哥的MySQL专栏!
目录
案例——查询emp表的所有数据, 和对应的部门信息(左外连接)
案例——查询dept表的所有数据, 和对应的员工信息(右外连接)
案例—查询所有员工及其领导的名字,如果员工没有领导,也需要查询出来
案例 — 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
案例 — 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
案例 — 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表之间也存在着各种联系,基本上分为三种:
案例:部门与员工的关系
关系:一个部门对应多个员工,一个员工对应一个部门
实现:在员工表emp中建立外键,并指向部门表dept中的id。一般是多的一方指向少的一方。
案例:学生与课程的关系
关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
实现的SQL语句如下:
- -- 多对多
- CREATE TABLE student ( id INT auto_increment PRIMARY KEY 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 auto_increment PRIMARY KEY 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 auto_increment COMMENT '主键' PRIMARY KEY,
- 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 );
执行完上述SQL语句,多表之间的关系如下图所示:
打开Navicat >>> 开启数据库 >>> 点击上侧菜单栏的“表” >>> 点击查看 >>> 选择ER图表,便可得到下面的表结构关系图:
补充:ER图也称实体-联系图(Entity Relationship Diagram),提供了表示实体类型、属性和联系的方法,开发的时候往往需要有ER图。很多可视化软件都可以导出ER图,
案例:用户与用户详情的关系
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,并提升操作效率。
实现:在任意一方中加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)状态。
实现的SQL语句如下:
- -- 一对一
- CREATE TABLE tb_user (
- id INT auto_increment PRIMARY KEY COMMENT '主键ID',
- NAME VARCHAR ( 10 ) COMMENT '姓名',
- age INT COMMENT '年龄',
- gender CHAR ( 1 ) COMMENT '1: 男 , 2: 女',
- phone CHAR ( 11 ) COMMENT '手机号'
- ) COMMENT '用户基本信息表';
-
- CREATE TABLE tb_user_edu (
- id INT auto_increment PRIMARY KEY 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 ( id, NAME, age, gender, phone )
- VALUES
- ( NULL, '黄渤', 45, '1', '18800001111' ),
- ( NULL, '冰冰', 35, '2', '18800002222' ),
- ( NULL, '码云', 55, '1', '18800008888' ),
- ( NULL, '李彦宏', 50, '1', '18800009999' );
-
- INSERT INTO tb_user_edu ( id, degree, major, primaryschool, middleschool, university, userid )
- VALUES
- ( NULL, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1 ),
- ( NULL, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2 ),
- ( NULL, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3 ),
- ( NULL, '本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学', 4 );
运行上述SQL命令,可以得到下图所示的表关系:
1). 删除之前 emp, dept表的测试数据( if exists )
2). 执行如下脚本,创建emp表与dept表并插入测试数据
- -- 创建dept表,并插入数据
- CREATE TABLE dept ( id INT auto_increment COMMENT 'ID' PRIMARY KEY, NAME VARCHAR ( 50 ) NOT NULL COMMENT '部门名称' ) COMMENT '部门表';
-
- INSERT INTO dept ( id, NAME )
- VALUES
- ( 1, '研发部' ),
- ( 2, '市场部' ),
- ( 3, '财务部' ),
- ( 4, '销售部' ),
- ( 5, '总经办' ),
- ( 6, '人事部' );
-
- -- 创建emp表,并插入数据
- CREATE TABLE emp (
- id INT auto_increment COMMENT 'ID' PRIMARY KEY,
- NAME VARCHAR ( 50 ) NOT NULL COMMENT '姓名',
- age INT COMMENT '年龄',
- job VARCHAR ( 20 ) COMMENT '职位',
- salary INT COMMENT '薪资',
- entrydate date COMMENT '入职时间',
- managerid INT COMMENT '直属领导ID',
- dept_id INT COMMENT '部门ID'
- ) COMMENT '员工表';-- 添加外键
-
- ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY ( dept_id ) REFERENCES dept ( id );
- INSERT INTO emp ( id, NAME, age, job, salary, entrydate, managerid, dept_id )
- VALUES
- ( 1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5 ),
- ( 2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1 ),
- ( 3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1 ),
- ( 4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1 ),
- ( 5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1 ),
- ( 6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1 ),
- ( 7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3 ),
- ( 8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3 ),
- ( 9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3 ),
- ( 10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2 ),
- ( 11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2 ),
- ( 12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2 ),
- ( 13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2 ),
- ( 14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4 ),
- ( 15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4 ),
- ( 16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4 ),
- ( 17, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL );
执行上述SQL语句,表emp和表dept如下图所示:
dept表共6条记录,emp表共17条记录。
多表查询就是指从多张表中查询数据。
原来查询单表数据,执行的SQL形式为:select * from emp;
执行多表查询,就只需要使用逗号分隔多张表即可,如: select * from emp , dept ; 具体的执行结果如下:
此时,我们看到查询结果中包含了大量的结果集,总共102条记录,而这其实就是员工表emp所有的记录 (17) 与 部门表dept所有记录(6) 的所有组合情况,这种现象称之为笛卡尔积。接下来,就来简单 介绍下笛卡尔积。
笛卡尔积: 笛卡尔乘积是指在数学中,两个集合 A集合 和 B集合的所有组合情况。
而在多表查询中,我们是需要消除无效的笛卡尔积的,只保留两张表关联部分的数据。
在SQL语句中,如何来去除无效的笛卡尔积呢? 我们可以给多表查询加上连接查询的条件即可。
select * from emp , dept where emp.dept_id = dept.id;
而由于id为17的员工没有dept_id字段值,所以在多表查询时,根据连接查询的条件并没有查询 到。
连接查询:
子查询:
内连接查询的是两张表交集部分的数据 (绿色部分的数据)。内连接的语法分为两种: 隐式内连接、显式内连接。语法如下:
- -- 隐式内连接
- SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
-
- -- 显式内连接
- SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
- select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-
- -- 为每一张表起别名,简化SQL编写
- select e.name,d.name from emp e , dept d where e.dept_id = d.id;
查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ... ON ...
- select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-
- -- 简化SQL编写
- select e.name, d.name from emp e join dept d on e.dept_id = d.id;
表的别名:
- tablea as 别名1 , tableb as 别名2 ;
- tablea 别名1 , tableb 别名2 ;
注意事项:
一但为表起了别名,就不能再使用表名来指定对应的字段了,此时只能够使用别名来指定字段。
外连接分为两种,分别是:左外连接 和 右外连接。具体的语法结构为:
- -- 左外连接
- SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
-
- -- 右外连接
- SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
由于需求中提到,要查询emp的所有数据,所以是不能内连接查询的,需要考虑使用外连接查询。
- select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
-
- select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
由于需求中提到,要查询dept表的所有数据,所以是不能内连接查询的,需要考虑使用外连接查 询。
- select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
-
- select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;
注意事项:左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺 序就可以了。而我们在日常开发使用时,更偏向于左外连接。
自连接查询,顾名思义,就是自己连接自己,也就是把一张表连接查询多次。
自连接的查询语法:
select 字段列表 from 表A 别名A join 表A 别名B on 条件 ... ;
而对于自连接查询,可以是内连接查询,也可以是外连接查询。
select a.name, b.name from emp a, emp b where a.managerid = b.id;
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;
注意事项: 在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底是哪一张表的字段。
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
- SELECT 字段列表 FROM 表A ...
- UNION [ ALL ]
- SELECT 字段列表 FROM 表B ....;
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all 和 union 的区别:union all会将全部的数据直接合并在一起,union会对合并之后的数据自动去重。
将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来。
当前对于这个需求,我们可以直接使用多条件查询,使用逻辑运算符 or 连接即可。 那这里呢,我们 也可以通过union/union all来联合查询.
union all查询出来的结果,仅仅进行简单的合并,并未去重。
- SELECT * FROM emp WHERE salary < 5000
- UNION
- SELECT * FROM emp WHERE age > 50;
union 联合查询,会对查询出来的结果进行去重处理。
注意:
联合查询时,多条查询语句中的字段数量需要保持一致,否则会报错。
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
select * from t1 where column1 = ( select column1 from t2 ) ;
子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个。
根据子查询结果不同,分为:
根据子查询位置,分为:
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。
常用的操作符:= <> > >= < <=
查询 "销售部" 的所有员工信息
(1)查询 "销售部" 部门ID
select id from dept where name = '销售部';
(2)根据 "销售部" 部门ID, 查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');
完成这个需求时,我们可以将需求分解为两步:
(1)查询 方东白 的入职日期
select entrydate from emp where name = '方东白';
(2)查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白');
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL
操作符 | 描述 |
---|---|
IN | 在指定的集合范围之内,多选一 |
NOT IN | 不在指定的集合范围之内 |
ANY | 子查询返回列表中,有任意一个满足即可 |
SOME | 与ANY等同,使用SOME的地方都可以使用ANY |
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 = '市场部');
分解为以下两步:
(1)查询所有 财务部 人员工资
- select id from dept where name = '财务部';
-
- select salary from emp where dept_id =
- ( select id from dept where name = '财务部' );
(2)比 财务部 所有人工资都高的员工信息
- select * from emp where salary > all
- ( select salary from emp where dept_id =
- ( select id from dept where name = '财务部' ) );
分解为以下两步:
(1)查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');
(2)比研发部其中任意一人工资高的员工信息
- 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, managerid from emp where name = '张无忌';
(2)查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
- select * from emp where (salary,managerid) =
- ( select salary, managerid from emp where name = '张无忌' );
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:IN
分解为两步执行:
(1)查询 "鹿杖客" , "宋远桥" 的职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
(2)查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
- select * from emp where (job,salary) in
- ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );
分解为两步执行:
(1)入职日期是 "2006-01-01" 之后的员工信息
select * from emp where entrydate > '2006-01-01';
(2)查询这部分员工, 对应的部门信息
- select e.*, d.* from
- ( select * from emp where entrydate > '2006-01-01' ) e
- left join dept d on e.dept_id = d.id ;
数据环境准备
- -- 多表查询案例
- CREATE TABLE salgrade ( grade INT, losal INT, hisal INT ) COMMENT '薪资等级表';
-
- INSERT INTO salgrade
- VALUES
- ( 1, 0, 3000 );
- INSERT INTO salgrade
- VALUES
- ( 2, 3001, 5000 );
- INSERT INTO salgrade
- VALUES
- ( 3, 5001, 8000 );
- INSERT INTO salgrade
- VALUES
- ( 4, 8001, 10000 );
- INSERT INTO salgrade
- VALUES
- ( 5, 10001, 15000 );
- INSERT INTO salgrade
- VALUES
- ( 6, 15001, 20000 );
- INSERT INTO salgrade
- VALUES
- ( 7, 20001, 25000 );
- INSERT INTO salgrade
- VALUES
- ( 8, 25001, 30000 );
在这个案例中,我们主要运用上面所讲解的多表查询的语法,完成以下的12个需求即可,而这里主要涉及到的表就三张:emp员工表、dept部门表、salgrade薪资等级表。
(1)查询员工的姓名、年龄、职位、部门信息 (隐式内连接)
select e.name, e.age, e.job, d.name from emp e, dept d where e.dept_id = d.id;
(2)查询年龄小于30岁的员工的姓名、年龄、职位、部门信息(显式内连接)
- select e.name , e.age , e.job , d.name
- from
- emp e inner join dept d on e.dept_id = d.id
- where
- e.age < 30;
(3)查询拥有员工的部门ID、部门名称
select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id;
(4)查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出 来(外连接)
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ;
(5)查询所有员工的工资等级
- -- 方式一
- select e.* , s.grade , s.losal, s.hisal
- from
- emp e , salgrade s
- where
- e.salary >= s.losal and e.salary <= s.hisal;
-
- -- 方式二
- select e.* , s.grade , s.losal, s.hisal
- from
- emp e , salgrade s
- where
- e.salary between s.losal and s.hisal;
(6)查询 "研发部" 所有员工的信息及 工资等级
- select e.* , s.grade
- from
- emp e , dept d , salgrade s
- where
- e.dept_id = d.id and
- ( e.salary between s.losal and s.hisal ) and
- d.name = '研发部';
(7)查询 "研发部" 员工的平均工资
- select avg(e.salary)
- from
- emp e, dept d
- where
- e.dept_id = d.id and d.name = '研发部';
(8)查询工资比 "灭绝" 高的员工信息。
- -- 查询 "灭绝" 的薪资
- select salary from emp where name = '灭绝';
-
- -- 查询比她工资高的员工数据
- select * from emp where salary > ( select salary from emp where name = '灭绝' );
(9)查询比平均薪资高的员工信息
- -- 查询员工的平均薪资
- select avg(salary) from emp;
-
- -- 查询比平均薪资高的员工信息
- select * from emp where salary > ( select avg(salary) from emp );
(10)查询低于本部门平均工资的员工信息
- -- 查询指定部门平均薪资
- select avg(e1.salary) from emp e1 where e1.dept_id = 1;
- select avg(e1.salary) from emp e1 where e1.dept_id = 2;
-
- -- 查询低于本部门平均工资的员工信息
- select *
- from
- emp e2
- where
- e2.salary <
- ( select avg(e1.salary)
- from
- emp e1
- where
- e1.dept_id = e2.dept_id );
(11)查询所有的部门信息, 并统计部门的员工人数
- select
- d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数'
- from
- dept d;
(12)查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
- select s.name , s.no , c.name
- from
- student s , student_course sc , course c
- where
- s.id = sc.studentid and sc.courseid = c.id ;
备注: 以上需求的实现方式可能会很多, SQL写法也有很多,只要能满足我们的需求,查询出符合条 件的记录即可。
>>> 如有疑问,欢迎评论区一起探讨。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。