建网页和建网站,互联网营销师报考费用,上海做一个公司网站多少钱,电商网站建设讯息目录
1. 多表关系
1.1 一对多#xff08;多对一#xff09;
1.2 多对多 1.3 一对一
2. 多表查询概述
2.1 熟悉表
2.2 笛卡尔积
2.3 消除笛卡尔积
2.4 多表查询分类
3. 内连接
3.1 隐式内连接
3.2 显式内连接
4. 外连接
4.1 左外连接
4.2 右外连接
5. 自连接
…目录
1. 多表关系
1.1 一对多多对一
1.2 多对多 1.3 一对一
2. 多表查询概述
2.1 熟悉表
2.2 笛卡尔积
2.3 消除笛卡尔积
2.4 多表查询分类
3. 内连接
3.1 隐式内连接
3.2 显式内连接
4. 外连接
4.1 左外连接
4.2 右外连接
5. 自连接
5.1 自连接查询
5.2 外连接查询
6. 联合查询
6.1 union all
6.2 union
6.3 使用联合查询条件
7. 嵌套/子查询
7.1 标量子查询
7.2 列子查询
1 in 的使用
2all 的使用
3 any 的使用
7.3 行子查询
7.4 表子查询
8. 多表查询案例
9. 总结
9.1 多表关系 9.2 多比查询
编辑 10 源代码(复制自取) 1. 多表关系 1.1 一对多多对一 1.2 多对多 1.3 一对一 2. 多表查询概述 2.1 熟悉表
在接下来的学习中我们暂且需要使用这两张表因此十分有必要熟悉表的结构及其内容;
1. 员工表 2. 部门表 2.2 笛卡尔积 2.3 消除笛卡尔积
在上述多表查询中可以查到这样的元组。 显然有许多信息是我们不需要的接下来就需要过滤信息消除笛卡尔积 2.4 多表查询分类 3. 内连接 内连接又分为隐式内连接和显示内连接 3.1 隐式内连接
先执行 from 因此表取别名后仅允许使用表名 3.2 显式内连接
显示内连接中 innet 常省略不写 4. 外连接
外连接分为左外连接和右外连接 此外左外连接可以改为右外连接右外连接可以改为左外连接
4.1 左外连接
左外连接中out 通常省略 4.2 右外连接 5. 自连接 5.1 自连接查询
其使用如下案例所示 5.2 外连接查询 6. 联合查询 6.1 union all
这是薪资低于 5000 的员工信息 这是年龄大于 50 的员工信息 这是将两个条件的查询结果联合起来的表 6.2 union union 可以去除重复; 6.3 使用联合查询条件 7. 嵌套/子查询 7.1 标量子查询 7.2 列子查询 1 in 的使用
下面举例演示 2all 的使用 3 any 的使用 7.3 行子查询 7.4 表子查询 再看第二个例子 8. 多表查询案例
注意以下查询并非只有一种方式 1. 查询员工的姓名年龄职位部门信息 2. 查询年龄小于30岁的员工的姓名年龄职位部门信息 3. 查询拥有员工的部门ID部门名称6号部门没有员工 4. 查询所有年龄大于40岁的员工及其所属部门名称如果员工没有分配部门也需要展示出来 5. 查询所有员工的工资等级 6. 查询研发部所有员工的信息及工资等级; 7. 查询“研发部“员工的平均工资 8. 查询工资比”灭绝“高的员工信息 9. 查询比平均工资高的员工信息 10. 查询低于本部门平均工资的员工信息 11. 查询所有部门信息并统计部门员工人数
9. 总结
9.1 多表关系 9.2 多表查询 此外较惋惜的是本篇未涉及 having,group by ,order by 的使用以及SQL 语句的执行顺序; 10 源代码(复制自取)
--创建数据库
create database mul_table_demo;--使用数据库
use mul_table_demo;--创建表 department
create table department(de_id smallint primary key, --部门idde_name varchar(20) --部门名称
);--插入表 department
insert into department values(1,研发部),(2,市场部),(3,财务部),(4,销售部),(5,总经办),(6,人事部);--创建表 employee
create table employee (em_id smallint primary key, --员工IDem_name varchar(20), --员工名字age smallint check (age between 18 and 65), --年龄job varchar(20), --工作salary int not null, --薪水entrydate date, --入职日期manager_id smallint, --员工的领导IDdept_id smallint foreign key references department(de_id)--部门ID
);--插入表 employee
insert into employee values --在将 varchar 值 * 转换成数据类型 int 时失败。表示数据类型弄错了
(1, 金庸, 65,总裁 ,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,会计 ,4800, 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, 2000-01-01,10,2),
(14,张三丰,65,销售总监,14000,2009-02-12,1, 4),
(15,俞莲舟,38,销售, 4600, 2004-10-12,14,4),
(16,宋远桥,40,销售, 4600, 2004-10-12,14,4), -- 4 or 4:加引号不加引号都是可以的
(17,陈友谅,42,null, 2000, 2010-01-01,1, null); --null不属于任何数据类型因此外键可以插入 null char/int 数据类型都可以插入 null--多表查询笛卡尔积
select * from employee,department;--消除笛卡尔积
select * from employee,department where employee.dept_id department.de_id;--内连接
--隐式内连接
--查询每个员工的姓名及关联的部门名称
select em.em_name as 员工姓名,de.de_name as 部门名称
from employee as em ,department as de
where em.dept_id de.de_id;--显式内连接
--查询每个员工的姓名及关联的部门名称
select em.em_name,de.de_name
from employee as em
join department as de
on em.dept_id de.de_id--外连接
--左外连接
--查询 emp 表的全部数据及对应的部门信息
select em.*,de.de_name
from employee as em
left join department as de
on em.dept_id de.de_id--右外连接
--查询 dept 表的所有信息和对应的员工信息
select de.de_id,de.de_name,em.*
from employee as em
right join department as de
on em.dept_id de.de_id--自连接
select * from employee;
--查询员工及其领导的名字
select b.em_name as 员工姓名, a.em_name as 领导姓名
from employee as a,employee as b
where a.em_id b.manager_id;--查询所有员工及其领导的名字如果员工没有领导也要查询出来
select * from employee;
selectb.em_name as 员工姓名 ,a.em_name as 领导姓名
from employee as a
right join employee as b
on a.em_id b.manager_id; --联合查询
--查询薪资低于 5000 或年龄大于 50 岁的员工的信息union allselect * from employee as e where e.salary 5000
union all
select * from employee as e where e.age 50--查询薪资低于 5000 或年龄大于 50 岁的员工的信息(union)
select * from employee as e where e.salary 5000
union
select * from employee as e where e.age 50select * from employee as e where e.salary 5000 or e.age 50;--子查询
--标量子查询
--查询“销售部”所在员工的全部信息--1. 查询“销售部”部门ID
select de_id from department as de where de.de_name 销售部;
--2. 根据部门ID查询“员工表”员工信息
select * from employee as em where em.dept_id 4;
--使用子/嵌套查询将上述结果整合起来
select * from employee as em
where em.dept_id (select de.de_id from department as de where de.de_name 销售部
);--查询“方东白”入职之后的员工信息
--方东白的入职日期
select e.entrydate from employee as e where e.em_name方东白;
--根据方东白的入职日期查询员工信息
select * from employee as em where em.entrydate 2000-01-01;
----使用子/嵌套查询将上述结果整合起来
select * from employee as em where em.entrydate (select e.entrydate from employee as e where e.em_name方东白);--列子查询
--查询“销售部”和“市场部”的员工信息
--1. 查询“销售部”和“市场部”的部门ID
select de.de_id from department as de where de.de_name in (销售部,市场部);
--2. 查询根据“销售部”和“市场部”的部门ID查询其员工信息
select * from employee as em where em.dept_id in (2,4);
--使用子/嵌套查询将上述结果整合起来
select * from employee as em where em.dept_id in (select de.de_id from department as de where de.de_name in (销售部,市场部)
);--查询比财务部所有人员工资都高的员工信息
--1. 查询财务部的人员工资
select e.salary
from employee as e
where e.dept_id in (select d.de_id from department as d where d.de_name 财务部);
--2. 根据财务部的人员工资查询比其工资都高的员工信息
select * from employee as e where e.salary 8500;
--使用子/嵌套查询将上述结果整合起来all
select * from employee as e where e.salary all(select e.salary from employee as e where e.dept_id in (select d.de_id from department as d where d.de_name 财务部)
);--查询比研发部其中任意一人工资高的员工信息
--1. 查询研发部人员的工资
select e.salary
from employee as e
where e.dept_id in (select d.de_id from department as d where d.de_name 研发部);
--2.查询比其任意一人工资高的员工信息
select * from employee as e where e.salary 6600;
----使用子/嵌套查询将上述结果整合起来 any
select * from employee as e where e.salary any (select e.salary from employee as e where e.dept_id in (select d.de_id from department as d where d.de_name 研发部)
);--行子查询
--查询 张无忌的薪资及直属领导相同 的员工信息
--1.查询张无忌的薪资
select e.salary,e.manager_id from employee as e where e.em_name 张无忌;
--2.根据查询结果查询员工信息
select * from employee as e where e.salary 12500 and e.manager_id 1;
--使用子/嵌套查询将上述结果整合起来
select * from employee as e where (e.salary,e.manager_id) (select e.salary,e.manager_id from employee as e where e.em_name 张无忌);--表子查询
--查询“鹿仗客”“宋远桥”的职位和薪资相同的员工信息
select * from employee as e where (e.salary,e.job) in (select e.salary,e.job from employee as e where e.em_name in (鹿仗客,宋远桥));--查询入职日期是“2006-01-01”之后的员工信息及其部门信息
select e.*,d.*
from ( select * from employee as e where e.entrydate 2006-01-01) as e ,department as d
wheree.dept_id d.de_id;--案例巩固
select * from employee;
select * from department;
select * from salgrade;
--在创建一张表
create table salgrade(grade int, --薪资等级losal int, --最低薪资hisal int --最高薪资
)
insert into salgrade values(1,0,3000),(2,3001,5000),(3,5001,8000),(4,8001,10000),(5,10001,15000),(6,15001,20000),(7,20001,25000),(8,25001,3000);--1. 查询员工的姓名年龄职位部门信息
select e.em_name,e.age,e.job,d.de_name
from employee as e
left join department as d
on e.dept_id d.de_id ;
--2. 查询年龄小于30岁的员工的姓名年龄职位部门信息
select e.em_name,e.age,e.job,d.de_name
from employee as e
left join department as d
on e.dept_id d.de_id
where e.age 30;--3. 查询拥有员工的部门ID部门名称6号部门没有员工
--方法1子查询
select distinctd.de_id,d.de_name
from (select e.dept_id from employee as e) as em ,department as d
where em.dept_id d.de_id;
--方法2内连接
select distinctd.de_id,d.de_name
from employee as e,department as d
where e.dept_id d.de_id; --4. 查询所有年龄大于40岁的员工及其所属部门名称如果员工没有分配部门也需要展示出来
select e.*,d.de_name
from employee as e
left join department as d
on e.dept_id d.de_id
where e.age 40; --5. 查询所有员工的工资等级薪水等级表没有外键如何连接表是关键
select *
from employee as e ,salgrade as s
where e.salary between s.losal and s.hisal; --6. 查询研发部所有员工的信息及工资等级
select e.*,s.grade
from (select * from employee as e join department as d on e.dept_id d.de_id where d.de_name 研发部) as e ,salgrade as s
where e.salary between s.losal and s.hisal;--7. 查询“研发部“员工的平均工资
select avg(e.salary)
from employee as e
join department as d
on e.dept_id d.de_id
whered.de_name 研发部--8. 查询工资比”灭绝“高的员工信息
select * from
employee as e
where e.salary ( select e.salary from employee as e where e.em_name 灭绝);--9. 查询比平均工资高的员工信息
select * from
employee as e
where e.salary ( select avg(salary) from employee);--10. 查询低于本部门平均工资的员工信息
select e2.*,(select avg(e1.salary) from employee as e1 where e1.dept_id e2.dept_id ) as 平均
from employee as e2
where e2.salary (select avg(e1.salary) from employee as e1 where e1.dept_id e2.dept_id ) ;--11. 查询所有部门信息并统计部门员工人数
select count(*) from employee as e where e.dept_id 6;
select d.de_id,d.de_name,(select count(*) from employee as e where e.dept_id d.de_id) as 人数
from department as d;
