MySQL--多表的操作

#创建部门
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 字段列表 from 表1 inner|left|right join 表2 on 表1.字段=表2.字段

-- 查询所有人员和部门的所有信息
select * from dept,person;

内连接查询（只显示符合条件的数据）相当于交集

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

左连接查询（左边表的数据优先全部显示）

-- 数据符合条件的才会显示，不符合条件的会以null进行填充

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

右连接查询（右边表的数据优先全部显示）

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

全连接查询（显示左右表中全部数据）相当于交集

-- 全连接查询：是在内连接的基础上增加左右两边没有显示的数据；使用关键字union
-- 注意：mysql 并不支持全连接 FULL JOIN 关键字 | 但是mysql提供了union关键字，使用union可以间接实现full join功能

-- 查询人员和部门所有信息
select * from person left join dept on person.dept_id = dept.did
UNION
select * from person right join dept on person.dept_id =dept.did;

复杂条件多表查询    关联到Mysql练习题第一题
子语句查询  关联到Mysql练习题第一题

-- 子语句（嵌套查询）：查多次，多个select
-- 注意：第一次的查询结果可以作为第二次的查询的条件或者表名使用；
-- 子语句中可以包含：in、not in、any、all、exists 和 not exists等关键字，还可以包含比较运算符：=、！=、>、<等。
-- 作为表名使用
select * from (select * from person) as 表名;
-- 注意：一条语句中可以有多个这样的子查询，在执行时，最里层括号（sql语句）具有优先执行权。同时注意 as 后面的表名不能加引号（''）。

其他查询
临时表查询

-- 查询高于本部门平均工资的人员
select avg(salary) from person GROUP BY dept_id;
select avg(salary) as salary ,dept_id from person GROUP BY dept_id;
select * from person,(select avg(salary) as salary, dept_id from person GROUP BY dept_id) as s where person.dept_id = s.dept_id and person.salary > s.salary;

判断查询 if关键字

-- 语法
if(条件表达式,'结果为True时输出的结果','条件为False时输出的结果')

-- 根据工资高低，将人员划分为两个级别，分别为高管人群和低端人群。显示效果：姓名、年龄、性别、工资、级别
select name,age,sex,salary,if (person.salary <5000,'低端人群','高端人群') as lv from person ；

多条件判断

-- 语法
case when 条件1 then 结果1
        when 条件2 then 结果2
        when 条件3 then 结果3
        ...
        else 都不成立后输出什么结果 end
-- 注意： 一定要记得加上结束符end

-- 根据工资高低,统计每个部门人员收入情况,划分为 富人,小资,平民,吊丝 四个级别, 要求统计四个级别分别有多少人

select dname as '部门',
            SUM(case when salary < 5000 then 1 else 0 end ) as '屌丝',
            SUM(case when salary BETWEEN 5000 and 8000 then 1 else 0 end ) as '平民',
            SUM(case when salary BETWEEN 8000 and 10000 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.did GROUP BY person.dept_id

 SQL逻辑查询语句执行顺序

SELECT DISTINCT <select_list>

FROM <left_table>

<join_type> JOIN <right_table>

ON <join_condition>

WHERE <where_condition>

GROUP BY <group_by_list>

HAVING <having_condition>

ORDER BY <order_by_condition>

LIMIT <limit_number>

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>