数据库基础---->多表查询、子语句查询 第三十八天 2018.11.29

多表查询

多表联合查询

建表

#创建部门
CREATE TABLE IF NOT EXISTS dept (
    did int not null auto_increment PRIMARY KEY,
    dname VARCHAR(50) not null COMMENT '部门名称'
)ENGINE=INNODB DEFAULT charset utf8;


#添加部门数据
INSERT INTO `dept` VALUES ('1', '教学部');
INSERT INTO `dept` VALUES ('2', '销售部');
INSERT INTO `dept` VALUES ('3', '市场部');
INSERT INTO `dept` VALUES ('4', '人事部');
INSERT INTO `dept` VALUES ('5', '鼓励部');

-- 创建人员
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) DEFAULT '0',
  `sex` enum('男','女','人妖') NOT NULL DEFAULT '人妖',
  `salary` decimal(10,2) NOT NULL DEFAULT '250.00',
  `hire_date` date NOT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- 添加人员数据

-- 教学部
INSERT INTO `person` VALUES ('1', 'alex', '28', '人妖', '53000.00', '2010-06-21', '1');
INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', '男', '8000.00', '2011-02-21', '1');
INSERT INTO `person` VALUES ('3', 'egon', '30', '男', '6500.00', '2015-06-21', '1');
INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', '女', '6680.00', '2014-06-21', '1');

-- 销售部
INSERT INTO `person` VALUES ('5', '歪歪', '20', '女', '3000.00', '2015-02-21', '2');
INSERT INTO `person` VALUES ('6', '星星', '20', '女', '2000.00', '2018-01-30', '2');
INSERT INTO `person` VALUES ('7', '格格', '20', '女', '2000.00', '2018-02-27', '2');
INSERT INTO `person` VALUES ('8', '周周', '20', '女', '2000.00', '2015-06-21', '2');

-- 市场部
INSERT INTO `person` VALUES ('9', '月月', '21', '女', '4000.00', '2014-07-21', '3');
INSERT INTO `person` VALUES ('10', '安琪', '22', '女', '4000.00', '2015-07-15', '3');

-- 人事部
INSERT INTO `person` VALUES ('11', '周明月', '17', '女', '5000.00', '2014-06-21', '4');

-- 鼓励部
INSERT INTO `person` VALUES ('12', '苍老师', '33', '女', '1000000.00', '2018-02-21', null);

创建表和数据

笛卡尔乘积　　

select  字段1,字段2... from 表1,表2... [where 条件]
#查询人员和部门所有信息
select * from person,dept　
笛卡尔乘积公式 : A表中数据条数   *  B表中数据条数  = 笛卡尔乘积.

多表联合查询　　

多表联合查询语法
select  字段1,字段2... from 表1,表2... [where 条件]

select * from person p,dept d where p.did = d.did;    # 起别名
select * from person,dept where person.did = dept.did;    #未起别名

多表连接查询

多表连接查询语法(重点)

左连接查询---->左边的表内容都显示、左边匹配不上的显示但用空null、右边表没匹配上不显示

右连接查询

SELECT 字段列表
    FROM 表1  INNER|LEFT|RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

select * from person left join  dept  on  person.did =dept.did;

内连接查询和多表连接查询结果完全一致

#查询人员和部门所有信息
select * from person inner join dept  on person.did =dept.did;

全连接查询

全连接查询：是在内连接的基础上增加 左右两边没有显示的数据

注意: mysql并不支持全连接 full JOIN 关键字

注意: 但是mysql 提供了 UNION 关键字.使用 UNION 可以间接实现 full JOIN 功能

#查询人员和部门的所有数据
 
SELECT * FROM person LEFT JOIN dept ON person.did = dept.did
UNION
SELECT * FROM person RIGHT JOIN dept ON person.did = dept.did;

UNION ALL不去重复

子语句查询(嵌套)　　

子语句一定要先执行一次

子查询(嵌套查询): 查多次, 多个select

注意: 第一次的查询结果可以作为第二次的查询的 条件 或者 表名 使用

子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字. 还可以包含比较运算符：= 、 !=、> 、<等

最外部语句称为父语句

使用结果集作为表名查询

select * from (select * from person) as 表名;     欺骗MySQL(子语句内作为临时表使用)
ps:大家需要注意的是: 一条语句中可以有多个这样的子查询,在执行时,最里层括号(sql语句) 具有优先执行权.
注意: as 后面的表名称不能加引号('')

聚合函数最大值---->从整张表中挑一个最大的，如果加入多字段，只给第一条加上，不管是否与后面相匹配

关键字

any(有一个)和some功能完全一样

select ...from ... where a > any(...);
select ...from ... where a > result1 or a > result2 or a > result3;
select ...from ... where a > some(...);
select ...from ... where a > result1 or a > result2 or a > result3;

all(全部)

select ...from ... where a > all(...);
select ...from ... where a > result1 and a > result2 and a > result3;

EXISTS 和 NOT EXISTS 子查询语法如下(必须与其他子查询语句一起使用)：

SELECT ... FROM table WHERE  EXISTS (subquery)
该语法可以理解为：主查询(外部查询)会根据子查询验证结果（TRUE 或 FALSE）来决定主查询是否得以执行。
                 验证结果---->为空时假、不为空时真

练习　　

1.查询平均年龄在20岁以上的部门名
2.查询教学部 下的员工信息
3.查询大于所有人平均工资的人员的姓名与年龄

#1.查询平均年龄在20岁以上的部门名
SELECT * from dept where dept.did in (
    select dept_id from person GROUP BY dept_id HAVING avg(person.age) > 20
);

#2.查询教学部 下的员工信息
select * from person where dept_id = (select did from dept where dname ='教学部');

#3.查询大于所有人平均工资的人员的姓名与年龄
select * from person where salary > (select avg(salary) from person);