7.7学习笔记

一、建表
CREATE table dept(dept1 VARCHAR(6),dept_name VARCHAR(20)) default charset=utf8;
INSERT into dept VALUES ('101','财务');
INSERT into dept VALUES ('102','销售');
INSERT into dept VALUES ('103','IT技术');
INSERT into dept VALUES ('104','行政');
CREATE table emp (sid VARCHAR(6),name VARCHAR(20),age TINYINT(2),woektime_start VARCHAR(10),incoming SMALLINT(10),dept2 VARCHAR(6))default charset=utf8;

insert into emp VALUES ('1789','张三',35,'1980/1/1',4000,'101');
insert into emp VALUES ('1674','李四',32,'1983/4/1',3500,'101');
insert into emp VALUES ('1776','王五',24,'1990/7/1',2000,'101');
insert into emp VALUES ('1789','十三',24,'1990/8/1',2000,'101');
insert into emp VALUES ('1568','赵六',57,'1970/10/11',7500,'102');
insert into emp VALUES ('1564','荣七',64,'1963/10/11',8500,'102');
insert into emp VALUES ('1777','十一',24,'1990/7/1',2000,'102');
insert into emp VALUES ('1778','十二',24,'1990/7/1',1000,'102');
insert into emp VALUES ('1879','牛八',55,'1971/10/20',7300,'103');
insert into emp VALUES ('1880','老九',55,'1971/10/20',7500,'105');
insert into emp VALUES ('1900','老十',64,'1990/8/1',2000,'106');

drop table dept ;
drop table emp ;
select * from dept;
select * from emp

二、分析表
（1）dept 表 ：部门表
dept1 ：部门编号
dept_name:部门名称

（2）emp 表：员工表
sid ：员工编号
name：员工姓名
age：年龄
woektime_start :入职时间
incoming：工资
dept2：部门编号

dept表和emp表 关联：dept1 ：部门编号和dept2：部门编号

三、多表
1、从2个表或者更多的表种查询我们需要的数据
2、多表连接的关系：
（1）内连接
（2）左连接
（3）右连接
（4）左独有数据
（5）右独有数据
（6）全外连接
比如:
a 表：123
b表： 124
内连接：a:12 ,b:12 连接 3，4不显示
左连接 a：123 b：12 4不显示 ，左表为主，全部显示
左连接 a：12 b：124 3不显示 ，右表为主全部显示
左独有数据 a:3
右独有的数据 b：4
全外连接 ：12合并数，34

3、语句

（1）内连接 （inner join on）
a、格式：select * from 表1 INNER JOIN 表2 on 表1.关联字段1=表2.关联字段2
如：select * from dept INNER JOIN emp on dept.dept1=emp.dept2

b、隐藏内连接
格式：select * from 表1 ,表2 where 表1.关联字段1=表2.关联字段1 ;
如：select * from dept ,emp where dept.dept1=emp.dept2 ;

（2）左连接 (left join on )
格式：select * from 表1 left JOIN 表2 on 表1.关联字段1=表2.关联字
如：select * from dept left JOIN emp on dept.dept1=emp.dept2

（3）右连接 ( right JOIN )
格式：select * from 表1 right JOIN 表2 on 表1.关联字段1=表2.关联字
如：select * from dept right JOIN emp on dept.dept1=emp.dept2
（4）左独有数据
格式：select * from 表1 left JOIN 表2 on 表1.关联字段1=表2.关联字 where 右表的字段 is null ；
如：select * from dept left JOIN emp on dept.dept1=emp.dept2 WHERE emp.name is null

（5）右独有数据
格式：select * from 表1 left JOIN 表2 on 表1.关联字段1=表2.关联字 where 左表字段 is null
如：
select * from dept right JOIN emp on dept.dept1=emp.dept2 WHERE
dept.dept1 is null
（6）全外连接 （union）
a、内连接+左独有+右独有
如：
select * from dept INNER JOIN emp on dept.dept1=emp.dept2
UNION
select * from dept left JOIN emp on dept.dept1=emp.dept2 WHERE
emp.name is null
UNION
select * from dept right JOIN emp on dept.dept1=emp.dept2 WHERE
dept.dept1 is null;

b、左连接+右独有
如：
select * from dept left JOIN emp on dept.dept1=emp.dept2
UNION
select * from dept right JOIN emp on dept.dept1=emp.dept2 WHERE
dept.dept1 is null

c、右连接+左独有
select * from dept right JOIN emp on dept.dept1=emp.dept2
UNION
select * from dept left JOIN emp on dept.dept1=emp.dept2 WHERE
emp.name is null
肖sir: 07-07 16:36:19
select * from dept INNER JOIN emp on dept.dept1=emp.dept2
UNION
select * from dept left JOIN emp on dept.dept1=emp.dept2 WHERE
emp.name is null
UNION
select * from dept right JOIN emp on dept.dept1=emp.dept2 WHERE
dept.dept1 is null;

总结：
多表：
普通内连接：select * from 表1 inner join 表2 on 表 1.关联字段=表2.关联字段
隐藏内连接：select * from 表1，表2 where 表 1.关联字段=表2.关联字段
左连接：select * from 表1 left join 表2 on 表 1.关联字段=表2.关联字段
右连接 ：select * from 表1 right join 表2 on 表 1.关联字段=表2.关联字段
左独有数据：select * from 表1 left join 表2 on 表 1.关联字段=表2.关联字段 where 表2中的字段 is null
右独有数据：select * from 表1 right join 表2 on 表 1.关联字段=表2.关联字段 where 表1 中的字段 isnull
全外连接：union
（1）左独有+右独有+内连接
（2）左连接+右独有
（3）右连接+左独有

练习：
1、求出财务部们收入总和？
结果：sum（incoming）
条件：dept_name=财务 dept表，sum（incoming） emp表
合并表：
select sum(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2 where dept_name="财务"

2、列出每个部门的平均收入以及部门
结果：dept_name，avg（incoming）
条件： group by dept_name
语句：
select dept_name,avg(incoming) from dept LEFT JOIN emp on dept.dept1=emp.dept2 group by dept_name
一、子查询
定义：一个查询中嵌套另一个查询

二、子查询的分类
（1）标量子查询
（2）列子查询
（3）行子查询
（4）表子查询

三、子查询的详解
（1）标量子查询 （返回的结果是一个值）
标量子查询：把一个sql执行返回的一个值，作为另一个sql的条件，得到的结果是一行一列，一般出现在where后面；
标量子量子查询允许使用比较运算符号:

,<,>=,<=,!=
案例：查询财务部门的收入总和
select dept1 from dept where dept_name="财务"; 返回的结果是101
如：select sum(incoming) from emp where dept2=(
select dept1 from dept where dept_name="财务"
)

（2）列子查询 （返回的结果是一列值）
定义：返回的值是一列值
案例：查询财务部门和销售部门的收入总和
select dept1 from dept where dept_name="财务" or dept_name="销售" 返回的结果一列多值
如：select sum(incoming) from emp where dept2 in (
select dept1 from dept where dept_name="财务" or dept_name="销售")

（3）行子查询
定义：返回的结果是一行多列，一般出出现在where的后面
案例：找出与王五年龄和工相同的员工姓名？

案例：select age,incoming from emp where name="王五"; 返回的结果：24，2000

select * from emp where(age,incoming) in (
select age,incoming from emp where name="王五"
)

（4）表子查询（重点）

定义：返回是多行多列,（多行多列就是一个表）一般接在from 后面
定义：临时表 as s

案例：合并表，当成一个临时表，进行查询
select sum(s.incoming) from (
select * from dept INNER JOIN emp on dept.dept1=emp.dept2 )as s where s.dept_name="财务"

案例2:列出每个部门中收入最高的员工姓名，部门名称，收入，并按照收入降序
select name,dept_name,incoming from dept INNER JOIN
emp on dept.dept1=emp.dept2 where (dept_name,incoming) in (select dept_name,max(incoming) from dept INNER JOIN emp on dept.dept1=emp.dept2 GROUP BY dept_name) ORDER BY incoming desc