目录
一、表与表之间的关系
多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
-
一对多(多对一)
-
多对多
-
一对一
①一对多(多对一)
案例:部门与员工的关系
关系:一个部门对应多个员工,一个员工对应一个部门
实现:在多的一方建立外键,关联另一方的主键
②多对多
案例:学生与课程的关系
关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
③一对一
案例:用户与用户详细的关系
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率。
实现:在任意一方加入一个外键,关联另一方的主键,并且设置外键为唯一约束
二、笛卡尔积和正确的多表查询
多表查询
前面我们使用的查询,只是对单表进行查询,在具体的应用中,经常需要实现在一个查询语句中显示多张数据表的数据,这就是所谓的多表联合查询。
在具体实现连接操作时,首先将两个或两个以上的表按照某个条件连接起来,然后再查询到所要求的数据记录。
注意:如果在一个语句中指定了别名,原来的表名在当前的sql语句中就不能使用了。(建议要起别名--->有时候表名会很长,书写不方便!)
三、等值连接和不等值连接
①等值连接
在连接条件中使用等于号(=)运算符比较被连接列的列值,
查询员工及员工部门信息
#隐式连接 SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno; #显示连接 SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
等值连接可以使用USING来自动关联两表中相同的列
SELECT * FROM emp INNER JOIN dept USING(deptno);
②非等值连接
在连接条件使用除等于运算符以外的其它比较运算符比较被连接的 列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>等。
查询员工工资级别
#隐式连接(losal和hisal分别是两个字段,介于两者之间筛选) SELECT * FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; #显示连接 SELECT * FROM emp e INNER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;注意:①这边case的应用,将原来表中等级1、2、3直接替换成对应字母
②想给其取别名必须在整个case---end之后 加上as '别名'
四、内连接和外连接
1.内连接
在表关系的笛卡尔积数据记录中,保留表关系中所有匹配的数据记录,舍弃不匹配的数据记录。按匹配的条件可以分成等值连接和不等值连接。
有两种,显式的和隐式的,返回连接表中符合连接条件和查询条件的数据行。(所谓的链接表就是数据库在做查询形成的中间表)。
连接条件不成立的时候,条件为NULL或匹配不到数据的时候,左表数据不显示.
隐式内连接(上面三的等值连接就是隐式的内连接)
SELECT * FROM 表1,表2 WHERE 条件;
显示内连接(使用关键字INNER JOIN)
SELECT * FROM 表1 [INNER] JOIN 表2 ON 条件;
2.外连接
在表关系的笛卡尔积中,不仅保留表关系中所有匹配的数据记录,而且还保留部分不匹配的记录。按照保留不匹配条件数据记录来源可以分为左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)和全外连接(FULL OUTER JOIN)。
外连接使用语法如下:(outer 可省)
SELECT * FROM 表1 LEFT|RIGHT|FULL [OUTER] JOIN 表2 ON 条件;
①左外连接
在表关系的笛卡尔积中,出了选择相匹配的数据记录,还包含关联左边表中不匹配的数据记录。
插入一条部门编号为NULL的数据(关于插入后面会讲)
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES(6666,'顽石','teacher',7369,CURDATE(),800,100,NULL);查询员工及对应的部门信息(没有部门的员工也显示出来,没有员工的部门不显示)
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;②右外连接
在表关系的笛卡尔积中,出了选择相匹配的数据记录,还包含关联右边表中不匹配的数据记录。
查询员工及对应的部门信息(没有部门的员工不显示,没有员工的部门显示)
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;③全连接
在表关系的笛卡尔积中,出了选择相匹配的数据记录,还包含关联左右两边表中不匹配的数据记录。
查询员工及对应的部门信息(没有部门的员工显示,没有员工的部门显示)
#mysql不支持全连接,但是可以用过集合运算来实现 #SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
五、七种JOIN操作
详细操作如下
六、自连接和并集运算
1.回顾一下前面两种内连接+补充新的USING 语法:
2.自连接
自连接就是指表与其自身进行连接
查询每个员工对应的领导姓名。
SELECT e.ename,e.mgr,me.ename 领导 FROM emp e,emp me WHERE e.mgr=me.empno;
3.并集运算
MySQL支持并集运算。
并集即两个集合所有部分
UNION DISTINCT
UNION ALL 会删除重复行
相同的行在结果中只出现一次。
SELECT * FROM emp UNION SELECT * FROM emp WHERE deptno=10;UNION ALL
UNION 不会会删除重复行
相同的行在结果中可能出现多次
SELECT * FROM emp UNION ALL SELECT * FROM emp WHERE deptno=10;
Attention: 前面对表中的某一个字段取了别名之后,本句再想用本字段必须得用别名!!!
要求
(1)输入的查询不能包含ORDER BY字句,可以为整个集合运算结果选择性地增加一个ORDER BY字句;(在整个UNION连接后的语句加上order by即可)
(2)两个查询必须包含相同的列数;
(3)相应列必须具有兼容的数据类型。兼容个的数据类型:优先级较低的数据类型必须能隐式地转换为较高级的数据类型。比如输入的查询1的第一列为int类型,输入的查询2的第一列为float类型,则较低的数据类型int类型可以隐式地转换为较高级float类型。如果输入的查询1的第一列为char类型,输入的查询2的第一列为datetime类型,则会提示转换失败:从字符串转换日期和/或时间时,转换失败;
(4)集合运算结果中列名由输入的查询1决定,如果要为结果分配结果列,应该在输入的查询1中分配相应的别名;(在哪起别名--/\--)
(5)集合运算时,对行进行比较时,集合运算认为两个NULL相等;
七、多表查询综合练习
需求
查询出雇佣日期在1981年的所有员工的编号、姓名、雇佣日期、工作、领导姓名、雇佣月工资、雇佣年工资(基本工资+奖金),工资等级、部门编号、部门名称、部门位置,并且求这些员工的月基本工资在1500~3500之间,将最后的结果按照年工资的降序排列,如果年工资相等,则按照工作进行排序。
需求很多,逐步分析
确定所需要的数据表
emp:编号、姓名、雇佣日期,工作、月工资、年薪
emp:领导姓名
dept:部门编号、名称、位置
salgrade:工资等级
确定一致的关联字段
员工和领导:e.mgr=e1.empno
员工和部门:e.deptno=dept.deptno
员工和工资等级:e.sal BETWEEN s.losal AND s.hisal
一步步分析 (自连接\内连接....)
一个细节: null与一个数相加仍然为null,想要实现正常的相加减-->IFNULL(字段,0) 表示如果此字段为null,就为0,否则就是此字段的值即可。