南京医疗网站建设,汕头cms建站,徐州建设企业网站,辽宁工程建设信息网诚信库怎么填1、多表关系
一对多#xff08;多对一#xff09; 案例: 部门 与 员工的关系 关系: 一个部门对应多个员工#xff0c;一个员工对应一个部门 实现: 在从表的一方建立外键#xff0c;指向主表一方的主键 多对多 案例: 学生 与 课程的关系 关系: 一个学生可以选修多门课程多对一 案例: 部门 与 员工的关系 关系: 一个部门对应多个员工一个员工对应一个部门 实现: 在从表的一方建立外键指向主表一方的主键 多对多 案例: 学生 与 课程的关系 关系: 一个学生可以选修多门课程一门课程也可以供多个学生选择 实现: 建立第三张中间表中间表至少包含两个外键分别关联两方主键 一对一 案例: 用户 与 用户详情的关系 关系: 一对一关系多用于单表拆分将一张表的基础字段放在一张表中其他详情字段放在另一张表中以提升操作效率 实现: 在任意一方加入外键关联另外一方的主键并且设置外键为唯一的(UNIQUE) 2、多表查询简介、笛卡尔积 多表查询就是指从多张表中查询数据。原来查询单表数据执行的SQL形式为select * from 表名;那么我们要执行多表查询就只需要使用逗号分隔多张表即可如 select * from 表1 , 表2; 在查询时我们看到查询结果中包含了表1所有的记录 与 表2所有记录的所有组合情况这种现象称之为笛卡尔积。 笛卡尔积: 笛卡尔乘积是指在数学中两个集合A集合 和 B集合的所有组合情况。 而在多表查询中我们是需要消除无效的笛卡尔积的只保留两张表关联部分的数据。 在SQL语句中如何来去除无效的笛卡尔积呢 我们可以给多表查询加上连接查询的条件即可。 3、内连接 相当于查询A、B交集部分数据。内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据 隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;显示内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;案例
A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现) 表结构: emp , dept 连接条件: emp.dept_id dept.id
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;B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) — INNER JOIN …ON … 表结构: emp , dept 连接条件: emp.dept_id dept.id
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;4、外连接
左外连接查询左表所有数据以及两张表交集部分数据右外连接查询右表所有数据以及两张表交集部分数据
左外连接
-- 左外连接相当于查询表1(左表)的所有数据当然也包含表1和表2交集部分的数据。
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;右外连接
-- 右外连接相当于查询表2(右表)的所有数据当然也包含表1和表2交集部分的数据。
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;案例
A. 查询emp表的所有数据, 和对应的部门信息。 由于需求中提到要查询emp的所有数据所以是不能内连接查询的需要考虑使用外连接查询。表结构: emp, dept、连接条件: emp.dept_id dept.id。
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;B. 查询dept表的所有数据, 和对应的员工信息(右外连接) 由于需求中提到要查询dept表的所有数据所以是不能内连接查询的需要考虑使用外连接查询。表结构: emp, dept、连接条件: emp.dept_id dept.id。
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中表结构的先后顺序就可以了。而我们在日常开发使用时更偏向于左外连接。 5、自连接 自连接查询顾名思义就是自己连接自己也就是把一张表连接查询多次。我们先来学习一下自连接的查询语法 SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;案例
A. 查询员工 及其 所属领导的名字
select a.name , b.name from emp a , emp b where a.managerid b.id;B. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
select a.name 员工, b.name 领导 from emp a left join emp b on a.managerid b.id;注意事项: 在自连接查询中必须要为表起别名要不然我们不清楚所指定的条件、返回的字段到底是哪一张表的字段。 6、子查询 SQL语句中嵌套SELECT语句称为嵌套查询又称子查询。 SELECT * FROM t1 WHERE column1 ( SELECT column1 FROM t2 );
-- 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。分类
根据子查询结果不同分为
A. 标量子查询子查询结果为单个值B. 列子查询(子查询结果为一列)C. 行子查询(子查询结果为一行)D. 表子查询(子查询结果为多行多列)
根据子查询位置分为
A. WHERE之后B. FROM之后C. SELECT之后
标量子查询 子查询返回的结果是单个值数字、字符串、日期等最简单的形式这种子查询称为标量子查询。 常用的操作符 案例:
A. 查询 “销售部” 的所有员工信息 ①. 查询 “销售部” 部门ID
select id from dept where name 销售部;②. 根据 “销售部” 部门ID, 查询员工信息
select * from emp where dept_id (select id from dept where name 销售部);B. 查询在 “方东白” 入职之后的员工信息 ①. 查询 方东白 的入职日期
select entrydate from emp where name 方东白;②. 查询指定入职日期之后入职的员工信息
select * from emp where entrydate (select entrydate from emp where name 方东白);列子查询 子查询返回的结果是一列可以是多行这种子查询称为列子查询。 常用的操作符IN 、NOT IN 、 ANY 、SOME 、 ALL 案例: A. 查询 “销售部” 和 “市场部” 的所有员工信息 ①. 查询 “销售部” 和 “市场部” 的部门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 市场部);B. 查询比 财务部 所有人工资都高的员工信息 ①. 查询所有 财务部 人员工资
select id from dept where name 财务部;
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 财务部) );C. 查询比研发部其中任意一人工资高的员工信息 ①. 查询研发部所有人工资
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 案例:
A. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ; ①. 查询 “张无忌” 的薪资及直属领导
select salary, managerid from emp where name 张无忌;②. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid) (select salary, managerid from emp where name 张无忌);表子查询 子查询返回的结果是多行多列这种子查询称为表子查询。 常用的操作符IN 案例:
A. 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息 ①. 查询 “鹿杖客” , “宋远桥” 的职位和薪资
select job, salary from emp where name 鹿杖客 or name 宋远桥;②. 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name 鹿杖客 or name 宋远桥 );B. 查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息 ①. 入职日期是 “2006-01-01” 之后的员工信息
select * from emp where entrydate 2006-01-01;②. 查询这部分员工, 对应的部门信息;
select e.*, d.* from (select * from emp where entrydate 2006-01-01) e left join dept d on e.dept_id d.id ;案例数据
-- 创建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 empadd constraint fk_emp_dept_id foreign key (dept_id) referencesdept (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);
