SQL子查询

SQL子查询

-- 查询比张三高的工资
​
select money from t_employee 
where name="张三"
select money from t_employee  
where money>"张三的工资"
​
-- 完整的语句
select money from t_employee  
where money>
(select money from t_employee 
where name="张三")

子查询：就是在查询语句嵌套一个查询语句

子查询是嵌套在查询语句的查询语句，就像语句里嵌套语句

标准结构语句：

select 列1,列2,(子查询) as 列别名 from 表面

员工表（t_employee）：

pk_id f_name f_money f_deptid

(外键，与部门dept的主键关联)

部门表（t_dept）：

pk_deptid（部门主键） pk_deptName（职位）

select中嵌套子循环

查询员工的名字和职位

select f_name ,
(select pk_deptName 
from t_dept  where 
f_deptid = pk_deptid ) 
from t_employee;

注意：嵌套在SELECT语句中SQL语句要求查询的值只能是单行和单列，不能一次性多行或者多列查询

在from中嵌套子循环

-- 标准结构：select 列1, 列2, 列n from (子查询) as 别名
-- 查询员工工资大于1000的名字
select s.f_name  from 
(select *  from t_employee 
where f_money>1000 ) s
-- 这里注意的是子查询查出来的数据就是外表

嵌套在from 子句中的子查询，必须给一个表别名。 FROM中嵌套的SQL不受任何限制，子查询结果可以为多行多列，作为外表。

WHERE中嵌入子查询

如果子查询返回单行单列

可以使用比较运算符（>、<、=、>=、<=、!=）

-- 标准结构：SELECT 列1,列2 FROM 表 WHERE 列=(子查询)
-- 如果子查询返回单行单列，才能使用比较运算符（>、<、=、>=、<=、!=）
-- 查询和张三同一部门的员工信息
select s1.f_name from t_employee s1 where s1.f_deptid=
(select s.f_deptid from t_employee s where s.f_name = "张三" )
​

如果子查询返回多行单列时

1.使用in或not in运算符。

-- 查询张三和李四所在部门的员工信息
select s1.f_name from t_employee s1 where s1.f_deptid in
(select s.f_deptid from t_employee s where s.f_name = "张三" 
or s.f_name = "李四" )

2.all 运算符

和子查询的结果逐一比较，必须全部满足时表达式的值才为真

-- 查询比所有张姓员工的工资都高的人
select s1.f_name from t_employee s1 where s1.f_money > all
(select s.f_money from t_employee s where s.f_name like "张%" )

any 运算符

和子查询的结果逐一比较，其中一条记录满足条件则表达式的值就为真。

-- 查询比某个张姓员工的工资高的员工
select s1.f_name from t_employee s1 where s1.f_money > any
(select s.f_money from t_employee s where s.f_name like "张%" )
-- 这里的查询是有问题的，子查询查出来的某一个姓张的同学会不会比下一个张的的高呢？
-- 如果张三1的工资是1000，张三2的工资是2000，张三3的工资是3000，那么
-- 张三2与张三3是不是比张三1的工资大，是不是满足了比某个张姓的工资高。
-- 所以要去重，最开始就在外表去掉姓张的人的工资
select s1.f_name, s1.f_money from t_employee s1 where s1.f_money > any
(select s.f_money from t_employee s where s.f_name like "张%" )
and s1.f_name not like "张%"
​

exists 运算符

exists判断子查询是否存在数据，如果存在则表达式为真，反之为假。not exists 相反

-- 查询与张姓员工一样的工资的人
select s1.f_name, s1.f_money from t_employee s1 where exists
(select s.f_money from t_employee s where s.f_name like "张%" and
s1.f_money = s.f_money )
-- 这里的查询是有问题的，这里的判断把外表的查询与子表的查询放在里子表里面，出现外表的张三1的钱跟子表的张三1比较。
-- 所以要去重，最开始就在外表去掉姓张的人的工资
select s1.f_name, s1.f_money from t_employee s1 where exists
(select s.f_money from t_employee s where s.f_name like "张%" and
s1.f_money = s.f_money ) and s1.f_name not like "张%"

GROUP BY (不常用)

GROUP BY中嵌套和SELECT中嵌套类似，要求子查询只能返回单行 单列值。

HAVING (不常用)

HAVING中嵌套子查询和WHERE中嵌套子查询类似，只是能用到聚 合函数。

-- 查询部门平均工资高于全部员工的平均工资的部门
select s.f_deptid ,sum(f_money)/count(*) from t_employee s  group by  f_deptid
having sum(f_money)/count(*)>(select sum(s1.f_money)/count(*)  from t_employee s1)
​

ORDER BY(不常用)

ORDER BY中嵌套子查询和SELECT中嵌套子查询类似，要求子查询 只能返回单行单列值。 上面三个位置嵌套子查询实际作用不大，因此很少会用到

-- 以工资从小到大排列
select s.f_name, s.f_money from (select * from t_employee s order by f_money) s

组合查询

UNION运算符 UNION是一种联合两条或以上查询的运算符，类似多条查询结果相组合的效果

标准结构： SELECT 列1 , 列2 FROM 表1 UNION SELECT 列3 , 列4 FROM 表2;

相关子查询

在主查询中，每查询一条记录，需要重新做一次子查询，这种称为相关子查询。相关子查询的执行，依赖于外部查询的数据，外部查询返回一行，子查询就执行一次

-- 查找每个员工的部门
select s.f_name, (select  s1.pk_deptName from t_dept s1  where s.f_deptid = s1.pk_deptid ) pk_deptName from   t_employee s
-- 每查询一次名字就要查询一次职位，效率慢

非相关子查询

在主查询中，子查询只需要执行一次，子查询结果不再变化。子查询结果，供主查询使用，这种查询方式称为非相关子查询。

-- 查询工资最高人的名字
select s.f_name from t_employee s 
where s.f_money=(select max(s1.f_money) from t_employee s1)
​

联表查询

如果数据来自多个表，那么可以采用联接查询的方式来实现

表联接采用的是笛卡尔乘积，称之为横向联接。 笛卡尔乘积是指将两张表的所有数据相连，最后联接的结果数为两张表数量的乘积。

JOIN关键字

在数据库中将多表相连需要使用JOIN关键字

-- 标准结构：
-- SELECT 列1,列2 FROM 表1 JOIN 表2

on关键字

笛卡尔乘积出来的结果数量太多，其中有不少数据是没用的，所以在表联接时 就要根据其外键来过滤没用的数据。使用ON关键字来确定其是否匹配。

-- 标准结构
-- SELECT 列1,列2 FROM 表1 JOIN 表2 ON 表1.外键=表2.主键

-- 查询所有员工的信息和部门
select  *  from t_employee s 
join t_dept s1  on s.f_deptid = s1.pk_deptid
-- 此时的员工表和部门表已经连在一起了

内联接

内连接是从结果表中删除与其他被连接表中没有匹配行的所有行。所以内连接可能会丢失信息。 内联接的标准语法是INNER JOIN，INNER可以省略。

-- 查询所有员工的信息和部门
select  *  from t_employee s join t_dept s1  on s.f_deptid = s1.pk_deptid
-- 如果员工表的员工还没有分配到部门，那么就会被on的判断给去除掉，但你不能说没有这个员工把

--   另外一种内连接写法
select  * from   t_employee s, t_dept s1 where s.f_deptid = s1.pk_deptid

外联接

外联接是指不管有没有匹配，被定义了外联接的表数据都要出现在结果中。

外联接分为：

左外联接：LEFT OUTER JOIN 或 LEFT JOIN在LEFT JOIN左边的表就被定义为外联接，那么此表中所有数据 都会出现在查询结果中。

右外联接：RIGHT OUTER JOIN 或 RIGHT JOIN在RIGHT JOIN右边的表就被定义为外联接，那么此表中所有数 据都会出现在查询结果中

select  *  from t_employee s left join t_dept s1  on s.f_deptid = s1.pk_deptid
-- 以左边的表为连接表，即使左边的有员工没有分配部门，也会被查询出来
-- 以员工区查询部门，即使有的员工没有部门，也会被查询出来
select  *  from t_employee s right join t_dept s1  on s.f_deptid = s1.pk_deptid
-- 以右边的表为连接表，即使空部门也会被查询出来
-- 以部门查询员工，即使是空部门，也能被查询出来

自联接

自联接其实就是内联接或外联接的一种特例，同样可以使用INNER JOIN 或 OUTER JOIN。自联接所联接的表是来自于同一张表

-- 查找员工的领导(领导也是员工，也在员工表里)   f_leadid是员工的领导表列
select  s.f_name, s1.f_name   from t_employee s join t_employee s1 on s.f_leadid = s1.pk_id  
​
-- 每一位的员工的领导列要指向主键列
-- 如果张三的领导是在领导列的数字5，就要用张三的领导列找到第五行的人就是他的领导

视图

视图可以看做是SQL语句的封装，可以看做是临时表，视图查询的结果会随着真实表数据的变化而变化。视图只提供查询功能，不提供数据修改的功能。

-- 创建视图：
-- create  view  视图名  as  查询SQL语句
-- 删除视图：
-- drop  view  视图名
​
create  view  demo as 
(select  s.f_name 员工 , s1.f_name 老板 from t_employee s join t_employee s1 on s.f_leadid = s1.pk_id  ) 
-- 这里的select 后面要加别名，因为他们的列名是一样的，会报错
drop view demo

数据规范化

仅有好的RDBMS并不足以避免数据冗余，必须在数据库的设计中创建好的表结构。

在具有关系的实体之间,存在着函数依赖(插入异常,删除异常,修改时数据冗余)。

Dr E.F.codd 最初定义了规范化的三个级别，范式是具有最小冗余的表结构

第一范式

在所有范式中，第一范式是最重要的。它提供了建立其它范式的基础， 并且代表了表的核心特性。 为遵从第一范式，一个表必须满足如下需求：

1、一个行的每个列必须是原子性的，即任何给定行的列只能包含一个值。

2、表中的每一行必须包含相同数量的列。假如每个列只能包含一个值，意味着每行必须包含相同数量的值。

3、一个表中的所有行必须是不同的。虽然行可能包含相同的值，但是每一行做为一个整体必须在表中是唯一的。

第二范式

第二范式在第一范式的基础上进行了扩展。 为遵从第二范式，一个表必须满足如下需求：

1、表必须符合第一范式。

2、表中的所有非主键列必须依赖于整个主键列。第二范式要求每个表只描述一件事情(一个实体)。

第三范式

如果一个关系满足第二范式，并且除了主键以外的其他列都不互相依赖，则满足第三范式

三范式小结

范式一： 列不可再分(值必须唯一),行不可重复(定义主键)

范式二： 非主键列必须依赖于主键列(非主依主)

范式三： 非主键列之间必须相互独立(非主独立)