铜川商城网站建设,简单建站的网站,佛山网站建设设计公司哪家好,湖北省疾病预防控制中心多表关系
项目开发中#xff0c;在进行数据库表结构设计时#xff0c;会根据业务需求及业务模块之间的关系#xff0c;分析并设计表结构#xff0c;由于业务之间相互关联#xff0c;所以各个表结构之间也存在着各种联系#xff0c;基本上分为三种#xff1a;
一对多(多…多表关系
项目开发中在进行数据库表结构设计时会根据业务需求及业务模块之间的关系分析并设计表结构由于业务之间相互关联所以各个表结构之间也存在着各种联系基本上分为三种
一对多(多对一)多对多一对一
一对多
部门与员工的关系一个部门对应多个员工一个员工对应一个部门。
实现在多的一方建立外键指向一的一方的主键。
多对多
学生与课程的关系一个学生可以选修多门课程一门课程也可以供多个学生选择。
实现建立第三张中间表中间表至少包含两个外键分别关联两方主键。
一对一
用户与用户详情的关系一对一关系多用于单表拆分将一张表的基础字段放在一张表中其他详情字段放在另一张表中以提升操作效率。
实现在任意一方加入外键关联另外一方的主键并且设置外键为唯一的(UNIQUE)。
笛卡尔积
笛卡尔积笛卡尔乘积是指在数学中两个集合A集合 和 B集合的所有组合情况。
而在多表查询中需要消除无效的笛卡尔积只保留两张表关联部分的数据。
-- 设置过滤条件 Column id in where clause is ambiguous
select * from emp,dept where id5;
select * from emp,dept where emp.dept_id dept.id;
-- 查询员工和部门的名字
select emp.name, dept.name from emp,dept where emp.dept_id dept.id;多表查询分类 内连接相当于查询A、B交集部分数据 外连接 左外连接查询左表所有数据以及两张表交集部分数据右外连接查询右表所有数据以及两张表交集部分数据 自连接当前表与自身的连接查询自连接必须使用表别名 子查询
表连接
内连接
内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据)。 内连接的语法分为两种隐式内连接、显式内连接。
select 字段名 from 左表, 右表 where 条件隐式内连接看不到 join 关键字条件使用 where 指定。select 字段名 from 左表 [inner] join 右表 where 条件显式内连接可以省略 inner。 总结 内连接的数据表不一定必须有同名字段只要字段之间符合逻辑关系就可以。相同的数据表也可以做表连接。结果集也可以作为一张“表”来跟其他表连接。 -- 隐式内连接
select * from emp,dept where emp.dept_id dept.id;-- 确定表连接条件员工表.dept_id 部门表.id 的数据才是有效的
select * from emp e inner join dept d on e.dept_id d.id;-- 查询与SCOTT相同部门的员工都有谁
select e2.ename
from t_emp e1 join t_emp e2 on e1.deptno e2.deptno
where e1.ename SCOTT and e2.ename ! SCOTT-- 查询月薪超过公司平均月薪的员工信息
select e.*
from t_emp e
join (select avg(sal) avg from t_emp) t
on e.sal t.avg外连接
外连接与内连接的区别在于除了符合条件的记录之外结果集中还会保留不符合条件的记录。左外连接就是保留左表所有的记录与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录就用NULL与左表连接。右外连接也是如此。
内连接只保留符合条件的记录所以查询条件写在ON子句和WHERE子句中的效果是相同的。但是外连接里条件写在WHERE子句里不合符条件的记录是会被过滤掉的而不是保留下来。
select 字段名 from 左表 left [outer] join 右表 on 条件左外连接outer 可以省略用左边表的记录去匹配右边表的记录如果符合条件的则显示否则显示 NULL。 左外连接相当于查询表1(左表)的所有数据也包含表1和表2交集部分的数据。 select 字段名 from 左表 right [outer] join 右表 on 条件右外连接outer 可以省略用右边表的记录去匹配左边表的记录如果符合条件的则显示否则显示 NULL 右外连接相当于查询表2(右表)的所有数据也包含表1和表2交集部分的数据。 -- 在员工表中增加一个员工
insert into emp values (null, 沙僧,男,6666,2013-12-05,null);
select * from emp;
-- 使用内连接查询
select * from dept inner join emp on dept.id emp.dept_id;
-- 使用右外连接查询
select * from dept right join emp on dept.id emp.dept_id;自连接
自连接查询就是把一张表连接查询多次。可以是内连接查询也可以是外连接查询。
select 字段列表 from 表A 别名A join 表A 别名B on 条件 ... ;自连接查询 在自连接查询中必须要为表起别名要不然不清楚所指定的条件、返回的字段到底是哪一张表的字段。 -- 查询员工 及其 所属领导的名字
select a.name , b.name from emp a , emp b where a.managerid b.id;-- 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来表结构: emp a , emp b
select a.name 员工, b.name 领导 from emp a left join emp b on a.managerid b.id;联合查询
union查询就是把多次查询的结果合并起来形成一个新的查询结果集。
(查询语句) UNION (查询语句) UNION (查询语句) ...UNION关键字可以将多个查询语句的结果集进行合并 对于联合查询的多张表的列数必须保持一致字段类型也需要保持一致。union all 会将全部的数据直接合并在一起union 会对合并之后的数据去重。如果多条查询语句查询出来的结果字段数量不一致会报错。 -- 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来
-- 不去重
select * from emp where salary 5000
union all
select * from emp where age 50;-- 去重
select * from emp where salary 5000
union
select * from emp where age 50;子查询
子查询可以写在三个地方WHERE子句、FROM子句、SELECT子句但是只有FROM子句子查询是最可取的。
根据子查询结果不同分为
标量子查询(子查询结果为单个值列子查询(子查询结果为一列)行子查询(子查询结果为一行)表子查询(子查询结果为多行多列) 查询语句执行的时候要多次的依赖于子查询的结果这类子查询被称作相关子查询WHERE子查询和SELECT子查询都属于相关子查询。因为相关子查询要反复多次执行所以应该避免使用。 EXISTS关键字
EXISTS关键字是把原来在子查询之外的条件判断写到了子查询的里面。
select …… from 表名 where [not] exists ( 子查询 );
标量子查询(结果是一个值)
子查询结果只要是单行单列肯定在 WHERE 后面作为条件父查询使用比较运算符如 、、、 等。
select 查询字段 from 表 where 字段 (子查询);
-- 1) 查询最高工资是多少
select max(salary) from emp;
-- 2) 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary (select max(salary) from emp);-- 1) 查询平均工资是多少
select avg(salary) from emp;
-- 2) 到员工表查询小于平均的员工信息
select * from emp where salary (select avg(salary) from emp);列子查询(结果是多行单列)
子查询返回的结果是一列可以是多行这种子查询称为列子查询。
常用的操作符in、not in、any、some、all
操作符描述in在指定的集合范围之内多选一not in不在指定的集合范围之内any子查询返回列表中有任意一个满足即可some与ANY等同使用SOME的地方都可以使用ANYall子查询返回列表的所有值都必须满足
select 查询字段 from 表 where 字段 in (子查询);
-- 先查询大于 5000 的员工所在的部门 id
select dept_id from emp where salary 5000;
-- 再查询在这些部门 id 中部门的名字 Subquery returns more than 1 row
select name from dept where id (select dept_id from emp where salary 5000);
select name from dept where id in (select dept_id from emp where salary 5000);-- 先查询开发部与财务部的 id
select id from dept where name in(开发部,财务部);
-- 再查询在这些部门 id 中有哪些员工
select * from emp where dept_id in (select id from dept where name in(开发部,财务部));-- 查询比 财务部 所有人工资都高的员工信息
-- 1.查询所有 财务部 人员工资
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 张无忌);表子查询(结果是多行多列)
子查询返回的结果是多行多列这种子查询称为表子查询。子查询结果只要是多列一般在 from 后面作为表。
select 查询字段 from (子查询) 表别名 where 条件;
子查询作为表需要取别名否则这张表没有名称则无法访问表中的字段。
-- 查询与 鹿杖客 , 宋远桥 的职位和薪资相同的员工信息
-- 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 宋远桥 );-- 查询入职日期是 2006-01-01 之后的员工信息 , 及其部门信息
-- 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 ;子查询效率问题
WHERE子查询
这种子查询最简单最容易理解但是却是效率很低的子查询。
用表连接替代WHERE子查询
表连接的优点是子查询只执行一次查询效率特别高
FROM子查询
这种子查询只会执行一次所以查询效率很高。
SELECT子查询
这种子查询每输出一条记录的时候都要执行一次查询效率很低。
