#创建部门 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
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>