47.MySQL数据库4
今日内容概要
今日内容详细
create table emp( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, depart_id int ); #插入数据 #三个部门：教学部，销售部，运营部 insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('jason','male',18,'20170301','张江第一帅形象',7300.33,401,1), ('tom','male',78,'20150302','teacher',1000000.31,401,1), ('kevin','male',81,'20130305','teacher',8300,401,1), ('tony','male',73,'20140701','teacher',3500,401,1), ('owen','male',28,'20121101','teacher',2100,401,1), ('jack','female',18,'20110211','teacher',9000,401,1), ('jenny','male',18,'19000301','teacher',30000,401,1), ('sank','male',48,'20101111','teacher',10000,401,1), ('哈哈','female',48,'20150311','sale',3000.13,402,2), #以下是销售部门 ('呵呵','female',48,'20101101','sale',2000.35,402,2), ('西西','female',38,'20110312','sale',1000.37,402,2), ('乐乐','female',18,'20160513','sale',3000.29,402,2), ('啦啦','female',18,'20170127','sale',4000.33,402,2), ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',18000,403,3), ('程咬铜','male',18,'20150411','operation',19000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3); mysql> select * from emp; +----+-----------+--------+-----+------------+-----------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------------------+--------------+------------+--------+-----------+ | 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象 | NULL | 7300.33 | 401 | 1 | | 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 呵呵 | female | 48 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 11 | 西西 | female | 38 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | 啦啦 | female | 18 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 18000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 19000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+-----------+--------+-----+------------+-----------------------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) 当表的字段很多的时候，命令窗口不够宽，感觉数据错乱，怎么办？？？ 只需要在select * from emp后面加上 \G 即：select * from emp \G; 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象 你可以将字符编码统一设置成gbk select * from emp \G; mysql> select * from emp \G; *************************** 1. row *************************** id: 1 name: jason sex: male age: 18 hire_date: 2017-03-01 post: 张江第一帅形象 post_comment: NULL salary: 7300.33 office: 401 depart_id: 1 *************************** 2. row *************************** id: 2 name: tom sex: male age: 78 hire_date: 2015-03-02 post: teacher post_comment: NULL salary: 1000000.31 office: 401 depart_id: 1 *************************** 3. row *************************** id: 3 name: kevin sex: male age: 81 hire_date: 2013-03-05 post: teacher post_comment: NULL salary: 8300.00 office: 401 depart_id: 1 *************************** 4. row *************************** id: 4 name: tony sex: male age: 73 hire_date: 2014-07-01 post: teacher post_comment: NULL salary: 3500.00 office: 401 depart_id: 1 *************************** 5. row *************************** id: 5 name: owen sex: male age: 28 hire_date: 2012-11-01 post: teacher post_comment: NULL salary: 2100.00 office: 401 depart_id: 1 *************************** 6. row *************************** id: 6 name: jack sex: female age: 18 hire_date: 2011-02-11 post: teacher post_comment: NULL salary: 9000.00 office: 401 depart_id: 1 *************************** 7. row *************************** id: 7 name: jenny sex: male age: 18 hire_date: 1900-03-01 post: teacher post_comment: NULL salary: 30000.00 office: 401 depart_id: 1 *************************** 8. row *************************** id: 8 name: sank sex: male age: 48 hire_date: 2010-11-11 post: teacher post_comment: NULL salary: 10000.00 office: 401 depart_id: 1 *************************** 9. row *************************** id: 9 name: 哈哈 sex: female age: 48 hire_date: 2015-03-11 post: sale post_comment: NULL salary: 3000.13 office: 402 depart_id: 2 *************************** 10. row *************************** id: 10 name: 呵呵 sex: female age: 48 hire_date: 2010-11-01 post: sale post_comment: NULL salary: 2000.35 office: 402 depart_id: 2 *************************** 11. row *************************** id: 11 name: 西西 sex: female age: 38 hire_date: 2011-03-12 post: sale post_comment: NULL salary: 1000.37 office: 402 depart_id: 2 *************************** 12. row *************************** id: 12 name: 乐乐 sex: female age: 18 hire_date: 2016-05-13 post: sale post_comment: NULL salary: 3000.29 office: 402 depart_id: 2 *************************** 13. row *************************** id: 13 name: 啦啦 sex: female age: 18 hire_date: 2017-01-27 post: sale post_comment: NULL salary: 4000.33 office: 402 depart_id: 2 *************************** 14. row *************************** id: 14 name: 僧龙 sex: male age: 28 hire_date: 2016-03-11 post: operation post_comment: NULL salary: 10000.13 office: 403 depart_id: 3 *************************** 15. row *************************** id: 15 name: 程咬金 sex: male age: 18 hire_date: 1997-03-12 post: operation post_comment: NULL salary: 20000.00 office: 403 depart_id: 3 *************************** 16. row *************************** id: 16 name: 程咬银 sex: female age: 18 hire_date: 2013-03-11 post: operation post_comment: NULL salary: 18000.00 office: 403 depart_id: 3 *************************** 17. row *************************** id: 17 name: 程咬铜 sex: male age: 18 hire_date: 2015-04-11 post: operation post_comment: NULL salary: 19000.00 office: 403 depart_id: 3 *************************** 18. row *************************** id: 18 name: 程咬铁 sex: female age: 18 hire_date: 2014-05-12 post: operation post_comment: NULL salary: 17000.00 office: 403 depart_id: 3 18 rows in set (0.00 sec)
#书写顺序 select id,name from emp where id > 1; #执行顺序 from where select ... """ 虽然执行顺序和书写顺序不一致，你在写sql语句的时候不知道怎么写 你就按照书写顺序的方式写sql select * 先用*占位 之后再去补全后面的sql语句 最后将*替换成你想要的具体字段 """
# 作用：是对整体数据的一个筛选操作 # 1.查询id大于等于3小于等于6的数据 select id,name,age from emp where id >=3 and id <=6; select id ,name,age from emp where id between 3 and 6; #2.查询薪资是20000或者18000或者17000的数据 select * from emp where salary in (20000,18000,17000); select * from emp where salary = 18000 or salary=20000 or salary=17000; #3.查询员工姓名中包含字母o的员工姓名和 薪资 """ 模糊查询 like % 匹配任意多个字符， _匹配任意单个字符 """ select name,salary from emp where name like "%o%"; #4.查询员工姓名是由4个字符组成的姓名和薪资 select name,salary from emp where name like "____"; select name,salary from emp where char_length(name) = 4; #5.查询id小于小于3或者大于6的数据 select * from emp where id not between 3 and 6; #6.查询薪资不在20000,18000,17000范围的数据 select * from emp where salary not in (20000,18000,17000); #7.查询岗位描述为空的员工姓名和岗位 select name,post from emp where post_comment is null;
#分组实际应用场景非常多 男女比例 部门平均薪资 部门秃头率 国家之间的数据统计 # 1.按照部门分组 select * from emp group by post; 分组之后，最小可操作的单位应该还是组，而不再是组内的单个数据 上述命令在你没有设置严格模式的时候，是可以正常执行的，返回的是分组之后，
每个组的第一条数据，但是这个不符合分组的规范，分组之后不应该在考虑单个数据，而是以组为操作单位， 如果设置了严格模式，那么上述命令会直接报错 """ """ 如果sql_mode中的配置有：ONLY_FULL_GROUP_BY，执行上面的语句汇报错： ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'day47.emp.id' which is not functionally dependent on columns in GROUP
BY clause; this is incompatible with sql_mode=only_full_group_by 解决办法： 去掉sql_mode中的ONLY_FULL_GROUP_BY即可 1.执行 set global sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 2.查看模式：show variables like "%mode"; 3.重新执行select * from emp group by post;即可 """ #记得最后设置回去，因为分组之后，最小可操纵的单位应该是组，而不再是组内的某个数据 set global sql_mode ='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 设置严格模式后，分组默认只能拿到分组的依据 select post from emp group by post; 按照什么分组就能只能拿到分组，其他字段不能直接获取，需要借助一些方法(聚合函数) """ 练习题： #什么时候需要分组？？？ 关键字：每个，平均，最高，最低 #1.获得每个部门的最高薪资 select post,max(salary) from emp group by post; mysql> select post, max(salary) from emp group by post; +-----------------------+-------------+ | post | max(salary) | +-----------------------+-------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 张江第一帅形象 | 7300.33 | +-----------------------+-------------+ 4 rows in set (0.01 sec) select post as '部门' ,max(salary) as '最高薪资' from emp group by post; mysql> select post as '部门',max(salary) as '最高薪资' from emp group by post; +-----------------------+--------------+ | 部门 | 最高薪资 | +-----------------------+--------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 张江第一帅形象 | 7300.33 | +-----------------------+--------------+ 4 rows in set (0.01 sec) #as 可以给字段起别名，也可以直接省略不写，但是不推荐，因为省略的话语义不明确，容易错乱 #2.获取每个部门的最低薪资 select post as '部门',min(salary) as min_sal from emp group by post; #3.获取每个部门的平均薪资 select post as '部门',avg(salary) as '平均薪资' from emp group by post; #4.获取每个部门的总薪资 selece post as '部门', sum(salary) as '总薪资' from emp group by post; #5.获取每个部门的人数 select post as '部门',count(id) as '总人数' from emp group by post; select post as '部门',count(name) as '总人数' from emp group by post; select post as '部门',count(age) as '总人数' from emp group by post; select post as '部门',count(salary) as '总人数' from emp group by post; select post as '部门',count(post_comment) as '总人数' from emp group by post; # 不能对null进行计数，其他都可以 #6.查询分组之后的部门名称，和每个部门下所有员工的姓名 #group_concat 不单单支持你获取分组之后的其他字段，还支持拼接操作 select post as '部门',group_coucat(name) as '部门成员' from emp group by post; select post as '部门',group_concat(name,'_DSB') as '员工姓名' from emp group by post; select post,group_concat(name,':',salary) from emp group by post; #concat 不分组的时候使用 select concat('NAME:',name),concat("SAL:",salary) from emp; # 补充：as语法不单单可以给字段起别名，还可以给表取别名 select emp.id,emp.name from emp; select t1.id,t1.name from emp as t1; #7.查询每个人的年薪 12薪 select name,salary * 12 from emp;
""" from where group by """ #关键字where和group by 同时出现的时候，group by必须在where后面 where先对整个数据进行过滤，之后再分组操作 where筛选条件不能使用聚合函数 select id,name,salary from emp where max(salary) > 3000; mysql> select id,name,age from emp where max(salary) > 3000; ERROR 1111 (HY000): Invalid use of group function select max(salary) from emp; #不分组默认就是一组 #统计各部门年龄在30岁以上的员工平均工资 select post as '部门', group_concat(name) as '员工姓名', avg(salary) from emp where age > 30 group by post;
""" having 的语法是跟where是一致的，只不过having是在分组之后使用的过滤操作，即having是可以使用聚合函数的 """ #统计各部门年龄在30岁以上的员工的平均工资并且保留平均薪资大于10000的部门 select post,avg(salary) from emp where age > 30 group by post having avg(salary) >10000;
""" 一定要注意，必须是完全一样的数据才可以去重！！！！！ 一定不要将逐渐忽视了，有主键存在的情况下是一定不可能去重的 """ [ {'id':1,'name':'jason','age':18}, {'id':2,'name':'jason','age':18}, {'id':3,'name':'egon','age':18} ] ORM 对象关系映射 让不懂sql语句的人也能够非常牛逼的操作数据库 表 类 一条条数据 对象 字段对应的值 对象的属性 你再写类，就意味着在创建表 用类生成对象，就意味着在创建数据 对象.属性，就是在获取数据字段对应的值 目的就是减轻Python程序员的压力，只需要会Python面向对象的知识点就可以操作mysql """ select distinct id,age from emp; #带有主键，无法去重 select distince age from emp;
select * from emp order by salary; select * from emp order by salary asc; select * from emp order by salary desc; """ order by 默认是升序，asc 可以忽略不写 order by desc 降序 """ select * from emp order by age desc,salary asc; #先按照age降序排列，如果age相同，则再按照salary升序排列 # 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门，然后对平均工资进行排序 select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary) desc;
select * from emp; """ 针对数据过多的情况，我们通常是都是做分页处理 """ select * from emp limit 3; #只展示3条数据 select * from emp limit 0,5; select * from emp limit 5,5; #第一条数据表示起始位置 #第二条数据表示取得条数
select * from emp where name regexp '^j.*(n|y)$' #上面的正则表达式表示：以j开头，以n或者y结尾，中间任意多个字符
前期表准备 # 建表 create table dep( id int, name varchar(20) ); create table emp( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into dep values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into emp(name,sex,age,dep_id) values ('jason','male',18,200), ('egon','female',48,201), ('kevin','male',18,201), ('nick','male',28,202), ('owen','male',18,203), ('jerry','female',18,204);
select * from dep,emp; #结果叫笛卡尔积 mysql> select * from dep,emp; +------+--------------+----+-------+--------+------+--------+ | id | name | id | name | sex | age | dep_id | +------+--------------+----+-------+--------+------+--------+ | 200 | 技术 | 1 | jason | male | 18 | 200 | | 201 | 人力资源 | 1 | jason | male | 18 | 200 | | 202 | 销售 | 1 | jason | male | 18 | 200 | | 203 | 运营 | 1 | jason | male | 18 | 200 | | 200 | 技术 | 2 | egon | female | 48 | 201 | | 201 | 人力资源 | 2 | egon | female | 48 | 201 | | 202 | 销售 | 2 | egon | female | 48 | 201 | | 203 | 运营 | 2 | egon | female | 48 | 201 | | 200 | 技术 | 3 | kevin | male | 18 | 201 | | 201 | 人力资源 | 3 | kevin | male | 18 | 201 | | 202 | 销售 | 3 | kevin | male | 18 | 201 | | 203 | 运营 | 3 | kevin | male | 18 | 201 | | 200 | 技术 | 4 | nick | male | 28 | 202 | | 201 | 人力资源 | 4 | nick | male | 28 | 202 | | 202 | 销售 | 4 | nick | male | 28 | 202 | | 203 | 运营 | 4 | nick | male | 28 | 202 | | 200 | 技术 | 5 | owen | male | 18 | 203 | | 201 | 人力资源 | 5 | owen | male | 18 | 203 | | 202 | 销售 | 5 | owen | male | 18 | 203 | | 203 | 运营 | 5 | owen | male | 18 | 203 | | 200 | 技术 | 6 | jerry | female | 18 | 204 | | 201 | 人力资源 | 6 | jerry | female | 18 | 204 | | 202 | 销售 | 6 | jerry | female | 18 | 204 | | 203 | 运营 | 6 | jerry | female | 18 | 204 | +------+--------------+----+-------+--------+------+--------+ 24 rows in set (0.00 sec) #上面的数据不是我们想要的，我们想要直接把对应的部门添加到每个员工后面 select * from emp,dep where emp.dep_id = dep.id; mysql> select * from emp ,dep where emp.dep_id = dep.id; +----+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-------+--------+------+--------+------+--------------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 18 | 201 | 201 | 人力资源 | | 4 | nick | male | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 203 | 203 | 运营 | +----+-------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec) """ mysql也知道，你在后面查询数据的过程中，肯定会经常用到这样的操作 所以特定给我们提供了对应的方法 inner join 内连接 lefy join 左连接 right join 右连接 union 全连接 """ #inner join select * from emp inner join dep on emp.dep_id = dep.id; #只拼接两张表中共有的数据部分 mysql> select * from emp inner join dep on emp.dep_id = dep.id; +----+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-------+--------+------+--------+------+--------------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 18 | 201 | 201 | 人力资源 | | 4 | nick | male | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 203 | 203 | 运营 | +----+-------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec) #left join #left join select * from emp left join dep on emp.dep_id = dep.id; #左表所有的数据都显示出来，没有对应的数据用null代替 mysql> select * from emp left join dep on emp.dep_id = dep.id; +----+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-------+--------+------+--------+------+--------------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 18 | 201 | 201 | 人力资源 | | 4 | nick | male | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 203 | 203 | 运营 | | 6 | jerry | female | 18 | 204 | NULL | NULL | +----+-------+--------+------+--------+------+--------------+ 6 rows in set (0.05 sec) #right join select * from emp right join dep on emp.dep_id = dep.id; #右表所有的数据都显示出来，没有对应的数据用null代替 mysql> select * from emp right join dep on emp.dep_id = dep.id; +------+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+-------+--------+------+--------+------+--------------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 18 | 201 | 201 | 人力资源 | | 4 | nick | male | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 203 | 203 | 运营 | | NULL | NULL | NULL | NULL | NULL | 205 | 公关部 | +------+-------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec) #union select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id; mysql> select * from emp left join dep on emp.dep_id = dep.id -> union -> select * from emp right join dep on emp.dep_id = dep.id; +------+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+-------+--------+------+--------+------+--------------+ | 1 | jason | male | 18 | 200 | 200 | 技术 | | 2 | egon | female | 48 | 201 | 201 | 人力资源 | | 3 | kevin | male | 18 | 201 | 201 | 人力资源 | | 4 | nick | male | 28 | 202 | 202 | 销售 | | 5 | owen | male | 18 | 203 | 203 | 运营 | | 6 | jerry | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 205 | 公关部 | +------+-------+--------+------+--------+------+--------------+ 7 rows in set (0.01 sec)
""" 子查询就是我们平时解决问题的思路 分步骤解决问题 第一步 第二步 将一个查询的结果，当做另外一个查询语句的条件去使用 """ #查询部门是技术或者人力资源的员工信息 #1.先获取符合条件的部门id select id from dep where name='技术' or name = '人力资源部'; #2.再去员工表里筛选出对应的员工 select name from emp where emp.dep_id in (select id from dep where name='技术' or name = '人力资源部');
表的查询结果可以作为其他表的查询条件 也可以通过起别名的方式把他作为一张虚拟表跟其他表关联 """ 多表查询就两种： 先拼接表再查询 子查询：一步一步来查询 """
# 关键字 exists（了解）
只返回布尔值 True False
返回True的时候，外层查询语句执行
返回False的时候，外层查询语句不再执行
select * from emp where exists (select id from dep where id > 203);
mysql> select * from emp where exists (select id from dep where id > 203);
+----+-------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+-------+--------+------+--------+
| 1 | jason | male | 18 | 200 |
| 2 | egon | female | 48 | 201 |
| 3 | kevin | male | 18 | 201 |
| 4 | nick | male | 28 | 202 |
| 5 | owen | male | 18 | 203 |
| 6 | jerry | female | 18 | 204 |
+----+-------+--------+------+--------+
6 rows in set (0.00 sec)
select * from emp where exists (select id from dep where id > 206);
mysql> select * from emp where exists (select id from dep where id > 206);
Empty set (0.00 sec)
