表和数据：
-- 创建部门 CREATE TABLE dept ( id int not null AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10) not null COMMENT '部门名称' ) -- 添加部门数据 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', '鼓励部'); -- 创建人员 CREATE TABLE `person` ( id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(50) NOT NULL, age tinyint(4) DEFAULT '0', gender enum('男','女') NOT NULL DEFAULT '女', salary decimal(10,2) NOT NULL DEFAULT '2500.00', hire_date date NOT NULL, dept_id int(11) DEFAULT NULL, CONSTRAINT fk_per_dept FOREIGN KEY(dept_id) REFERENCES dept(id) ) -- 添加人员数据 -- 教学部 INSERT INTO `person` VALUES ('1', '小A', '28', '女', '5300.00', '2010-06-21', '1'); INSERT INTO `person` VALUES ('2', '小B', '23', '男', '8000.00', '2011-02-21', '1'); INSERT INTO `person` VALUES ('3', '小C', '30', '男', '6500.00', '2015-06-21', '1'); INSERT INTO `person` VALUES ('4', '小D', '18', '女', '6680.00', '2014-06-21', '1'); -- 销售部 INSERT INTO `person` VALUES ('5', '小E', '20', '女', '3000.00', '2015-02-21', '2'); INSERT INTO `person` VALUES ('6', '小F', '20', '女', '2000.00', '2018-01-30', '2'); INSERT INTO `person` VALUES ('7', '小G', '20', '女', '2000.00', '2018-02-27', '2'); INSERT INTO `person` VALUES ('8', '小H', '20', '女', '2000.00', '2015-06-21', '2'); -- 市场部 INSERT INTO `person` VALUES ('9', '小I', '21', '女', '4000.00', '2014-07-21', '3'); INSERT INTO `person` VALUES ('10', '小J', '22', '女', '4000.00', '2015-07-15', '3'); -- 人事部 INSERT INTO `person` VALUES ('11', '小K', '17', '女', '5000.00', '2014-06-21', '4'); -- 鼓励部 INSERT INTO `person` VALUES ('12', '小L', '33', '女', '100000.00', '2018-02-21', null);
一、多表联合查询
-- 多表查询语法 select 字段1,字段2 from 表1,表2 [where 条件]; 注意: 如果不加条件直接进行查询，则会出现以下效果，这种结果我们称之为"笛卡尔乘积" 笛卡尔乘积公式 : A表中数据条数 * B表中数据条数 = 笛卡尔乘积 -- 查询人员和部门所有信息 select * from person,dept;
id name age gender salary hire_date dept_id id1 name1 1 A 28 女 53000 2010-06-21 1 1 教学部 1 A 28 女 53000 2010-06-21 1 2 销售部 1 A 28 女 53000 2010-06-21 1 3 市场部 1 A 28 女 53000 2010-06-21 1 4 人事部 1 A 28 女 53000 2010-06-21 1 5 鼓励部 2 B 23 男 8000 2011-02-21 1 1 教学部 2 B 23 男 8000 2011-02-21 1 2 销售部 2 B 23 男 8000 2011-02-21 1 3 市场部 2 B 23 男 8000 2011-02-21 1 4 人事部 2 B 23 男 8000 2011-02-21 1 5 鼓励部 3 C 30 男 6500 2015-06-21 1 1 教学部 3 C 30 男 6500 2015-06-21 1 2 销售部 3 C 30 男 6500 2015-06-21 1 3 市场部 3 C 30 男 6500 2015-06-21 1 4 人事部 3 C 30 男 6500 2015-06-21 1 5 鼓励部 4 D 18 女 6680 2014-06-21 1 1 教学部 4 D 18 女 6680 2014-06-21 1 2 销售部 4 D 18 女 6680 2014-06-21 1 3 市场部 4 D 18 女 6680 2014-06-21 1 4 人事部 4 D 18 女 6680 2014-06-21 1 5 鼓励部 5 E 20 女 3000 2015-02-21 2 1 教学部 5 E 20 女 3000 2015-02-21 2 2 销售部 5 E 20 女 3000 2015-02-21 2 3 市场部 5 E 20 女 3000 2015-02-21 2 4 人事部 5 E 20 女 3000 2015-02-21 2 5 鼓励部 6 F 20 女 2000 2018-01-30 2 1 教学部 6 F 20 女 2000 2018-01-30 2 2 销售部 6 F 20 女 2000 2018-01-30 2 3 市场部 6 F 20 女 2000 2018-01-30 2 4 人事部 6 F 20 女 2000 2018-01-30 2 5 鼓励部 7 G 20 女 2000 2018-02-27 2 1 教学部 7 G 20 女 2000 2018-02-27 2 2 销售部 7 G 20 女 2000 2018-02-27 2 3 市场部 7 G 20 女 2000 2018-02-27 2 4 人事部 7 G 20 女 2000 2018-02-27 2 5 鼓励部 8 H 20 女 2000 2015-06-21 2 1 教学部 8 H 20 女 2000 2015-06-21 2 2 销售部 8 H 20 女 2000 2015-06-21 2 3 市场部 8 H 20 女 2000 2015-06-21 2 4 人事部 8 H 20 女 2000 2015-06-21 2 5 鼓励部 9 I 21 女 4000 2014-07-21 3 1 教学部 9 I 21 女 4000 2014-07-21 3 2 销售部 9 I 21 女 4000 2014-07-21 3 3 市场部 9 I 21 女 4000 2014-07-21 3 4 人事部 9 I 21 女 4000 2014-07-21 3 5 鼓励部 10 J 22 女 4000 2015-07-15 3 1 教学部 10 J 22 女 4000 2015-07-15 3 2 销售部 10 J 22 女 4000 2015-07-15 3 3 市场部 10 J 22 女 4000 2015-07-15 3 4 人事部 10 J 22 女 4000 2015-07-15 3 5 鼓励部 11 K 17 女 5000 2014-06-21 4 1 教学部 11 K 17 女 5000 2014-06-21 4 2 销售部 11 K 17 女 5000 2014-06-21 4 3 市场部 11 K 17 女 5000 2014-06-21 4 4 人事部 11 K 17 女 5000 2014-06-21 4 5 鼓励部 12 L 33 女 1000000 2018-02-21 1 教学部 12 L 33 女 1000000 2018-02-21 2 销售部 12 L 33 女 1000000 2018-02-21 3 市场部 12 L 33 女 1000000 2018-02-21 4 人事部 12 L 33 女 1000000 2018-02-21 5 鼓励部
注意：多表查询时，一定要找到两个表中相互关联的字段，并且作为条件使用
-- 查询人员和部门所有信息 select * from person,dept where person.dept_id = dept.id;
id name age gender salary hire_date dept_id id1 name1 1 小A 28 女 5300 2010-06-21 1 1 教学部 2 小B 23 男 8000 2011-02-21 1 1 教学部 3 小C 30 男 6500 2015-06-21 1 1 教学部 4 小D 18 女 6680 2014-06-21 1 1 教学部 5 小E 20 女 3000 2015-02-21 2 2 销售部 6 小F 20 女 2000 2018-01-30 2 2 销售部 7 小G 20 女 2000 2018-02-27 2 2 销售部 8 小H 20 女 2000 2015-06-21 2 2 销售部 9 小I 21 女 4000 2014-07-21 3 3 市场部 10 小J 22 女 4000 2015-07-15 3 3 市场部 11 小K 17 女 5000 2014-06-21 4 4 人事部
二、多表连接查询
-- 多表连接查询语法(重点) select 字段列表 from 表1 inner|left|right join 表2 on 表1.字段 = 表2.字段;
内连接查询 （只显示符合条件的数据）
-- 查询人员和部门所有信息 select * from person inner join dept on person.dept_id = dept.id;
效果：内连接查询与多表联合查询的效果是一样的。
全连接查询（显示左右表中全部数据）
- 全连接查询：是在内连接的基础上增加，左右两边没有显示的数据。
- 注意：mysql并不支持全连接 full JOIN 关键字
- 注意：但是mysql提供了UNION关键字，使用 UNION 可以间接实现 full JOIN 功能
-- 查询人员和部门所有信息 select * from person left join dept on person.dept_id = dept.id union select * from person right join dept on person.dept_id = dept.id;
三、子语句查询
- 子查询(嵌套查询)：查多次, 多个select；
- 注意：第一次的查询结果可以作为第二次的查询的 条件 或者 表名 使用；
- 子查询中可以包含：in、not in、any、all、exists 和 not exists 等关键字，还可以包含比较运算符 = 、 !=、> 、<等。
作为表名使用
select * from (select * from person) as 表名;
注意：一条语句中可以有多个这样的子查询，在执行时，最里层括号(sql语句)具有优先执行权，as 后面的表名称不能加引号。
求最大工资那个人的姓名和薪水
select name,salary from person where salary=(select max(salary) from person);
求工资高于所有人员平均工资的人员
-- 求平均工资 select avg(salary) from person; -- 工资大于平均工资的 人的姓名、工资 select name,salary from person where salary>平均工资; -- 合并 select name,salary from person where salary>(select avg(salary) from person);
关键字
假设any内部的查询语句返回的结果个数是三个，如:result1,result2,result3,那么， select ...from ... where a > any(...); -> select ...from ... where a > result1 or a > result2 or a > result3;
ALL关键字与any关键字类似，只不过上面的or改成and。即: select ...from ... where a > all(...); -> select ...from ... where a > result1 and a > result2 and a > result3;
some关键字和any关键字是一样的功能。所以: select ...from ... where a > some(...); -> select ...from ... where a > result1 or a > result2 or a > result3;
EXISTS 和 NOT EXISTS 子查询语法如下： SELECT ... FROM table WHERE EXISTS (subquery) -- 该语法可以理解为：主查询(外部查询)会根据子查询验证结果（TRUE 或 FALSE）来决定主查询是否得以执行。 SELECT * FROM person WHERE NOT EXISTS (SELECT * FROM dept WHERE id=5); -- 此处内层循环并没有查询到满足条件的结果，因此返回false，外层查询不执行。 -- EXISTS刚好与之相反。
四、其他查询
临时表查询
需求：查询高于本部门平均工资的人员
解析思路:
- 先查询本部门人员平均工资是多少
- 再使用人员的工资与部门的平均工资进行比较
-- 先查询部门人员的平均工资 select dept_id,avg(salary) as "平均工资" from person group by dept_id; -- 再使用人员的工资与部门的平均工资进行比较 select * from person as p1, (select dept_id,avg(salary) as "平均工资" from person group by dept_id) as p2 where p1.dept_id=p2.dept_id and p1.salary >p2.平均工资; -- PS：在当前语句中，我们可以把上一次的查询结果当前做一张表来使用，因为p2表不是真是存在的，所以称之为 临时表。 -- 临时表：不局限于自身表，任何的查询结果集都可以认为是一个临时表。
判断查询（if关键字）
需求1：根据工资高低，将人员划分为两个级别，分别为 高端人群 和 低端人群。显示效果：姓名、年龄、性别、工资、级别。
select p1.name,p1.age,p1.salary, if (p1.salary>5000,"高端人群","低端人群") as "级别" from person p1; -- 语法: if (条件表达式,"结果为true",'结果为false');
需求2：根据工资高低，统计每个部门人员收入情况，划分为 富人、小资、平民、屌丝 四个级别，要求统计四个级别分别有多少人。
-- 语法一 select case when state = '1' then '成功' when state = '2' then '失败' else '其他' end from 表; -- 语法二: select case age when 23 then '23岁' when 27 then '27岁' when 30 then '30岁' else '其他岁' end from 表;
select dept.name as '部门', sum(case when salary >50000 then 1 else 0 end) as '富人', sum(case when salary between 29000 and 50000 then 1 else 0 end) as '小资', sum(case when salary between 10000 and 29000 then 1 else 0 end) as '平民', sum(case when salary <10000 then 1 else 0 end) as '屌丝' from person,dept where person.dept_id = dept.id group by dept_id;
