！# 从小白到小黑 python学习之旅 日常总结 47（记录的相关操作）
单表查询
单表查询的语法
select distinct 字段1,字段2,... from 表名
where 分组之前的筛选条件
group by 分组条件
having 分组之后的筛选条件
order by 排序字段1 asc,排序字段2 desc
limit 5,5
关键字的执行优先级(重点)
重点中的重点：关键字的执行优先级 from where group by having select distinct order by limit
1.找到表:from 2.拿着where指定的约束条件，去文件/表中取出一条条记录 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 4.将分组的结果进行having过滤 5.执行select 6.去重 7.将结果按条件排序：order by 8.限制结果的显示条数
详细见：http://www.cnblogs.com/linhaifeng/articles/7372774.html
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',38,'20101101','sale',2000.35,402,2), ('西西','female',18,'20110312','sale',1000.37,402,2), ('乐乐','female',18,'20160513','sale',3000.29,402,2), ('拉拉','female',28,'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',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3); # 当表字段特别多 展示的时候错乱 可以使用\G分行展示 select * from emp\G; # 电脑在插入中文的时候还是会出现乱码或者空白的现象 你可以将字符编码统一设置成GBK
where筛选条件
作用:是对整体数据的一个筛选操作
where字句中可以使用： 1. 比较运算符：> < >= <= <> != 2. between 80 and 100 值在10到20之间 3. in(80,90,100) 值是10或20或30 4. like 'egon%' pattern可以是%或_， %表示任意多字符 _表示一个字符 5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
# 1.查询id大于等于3小于等于6的数据 select id,name,age from emp where id>=3 and id<=6; select id,name from emp where id between 3 and 6; 两者等价 # 2.查询薪资是20000或者18000或者17000的数据 select * from emp where salary=20000 or salary=18000 or salary=17000; select * from emp where salary in (20000,18000,17000); # 3.查询员工姓名中包含字母o的员工的姓名和薪资 """ 模糊查询 like % 匹配任意多个字符 _ 匹配任意单个字符 """ select name,salary from emp where name like '%o%'; # 4.查询员工姓名是由四个字符组成的 姓名和薪资 char_length() _ select name,salary from emp where name like '____'; select name,salary from emp where char_length(name) = 4; # 5.查询id小于3或者id大于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.查询岗位描述为空的员工姓名和岗位名 针对null不用等号 用is select name,post from emp where post_comment is NULL;
group by分组
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的 #2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等 #3、为何要分组呢？ 取每个部门的最高工资 取每个部门的员工数 取男人数和女人数 小窍门：‘每’这个字后面的字段，就是我们分组的依据 #4、大前提： 可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
# 按照部门分组 select * from emp group by post; """ 分组之后 最小可操作单位应该是组 还不再是组内的单个数据 上述命令在你没有设置严格模式的时候是可正常执行的 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据) 如果设置了严格模式 那么上述命令会直接报错 """ set global sql_mode = 'strict_trans_tables,only_full_group_by'; 设置严格模式之后 分组 默认只能拿到分组的依据 select post from emp group by post; 按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助于一些方法(聚合函数) """ 什么时候需要分组啊？？？ 关键字 每个 平均 最高 最低 聚合函数 max min sum count avg """
#查看MySQL 5.7默认的sql_mode如下： mysql> select @@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 #！！！注意 ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。 #设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)： mysql> 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';
# 1.获取每个部门的最高薪资 select post,max(salary) from emp group by post; select post as '部门',max(salary) as '最高薪资' from emp group by post; select post '部门',max(salary) '最高薪资' from emp group by post; # as可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱 # 2.获取每个部门的最低薪资 select post,min(salary) from emp group by post; # 3.获取每个部门的平均薪资 select post,avg(salary) from emp group by post; # 4.获取每个部门的工资总和 select post,sum(salary) from emp group by post; # 5.获取每个部门的人数 select post,count(id) from emp group by post; # 常用 符合逻辑 select post,count(salary) from emp group by post; select post,count(age) from emp group by post; select post,count(post_comment) from emp group by post; null不行 # 6.查询分组之后的部门名称和每个部门下所有的员工姓名 # group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作 select post,group_concat(name) from emp group by post; select post,group_concat(name,'_DSB') 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 emp.id,emp.name from emp as t1; 报错 select t1.id,t1.name from emp as t1; # 查询每个人的年薪 12薪 select name,salary*12 from emp;
# 关键字where和group by同时出现的时候group by必须在where的后面 where先对整体数据进行过滤之后再分组操作 where筛选条件不能使用聚合函数 select id,name,age from emp where max(salary) > 3000; select max(salary) from emp; # 不分组 默认整体就是一组 # 统计各部门年龄在30岁以上的员工平均薪资 1 先求所有年龄大于30岁的员工 select * from emp where age>30; 2 再对结果进行分组 select * from emp where age>30 group by post; select post,avg(salary) from emp where age>30 group by post;
#having的语法根where是一致的 #having与where不一样的地方在于！！！ #！！！执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而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} ] """ select distinct id,age from emp; #有主键 不能去重 select distinct 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 该asc可以省略不写 也可以修改为降序 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; # 只展示三条数据 #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条 select * from emp limit 0,5; #第一个参数是起始位置 第二个参数是展示条数 #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条 select * from emp limit 5,5; #第一个参数是起始位置 第二个参数是展示条数
""" 正则是一门独立的语言 在python中如果你想使用正则需要借助于re模块 面试题 1.re模块中常用的方法 findall:分组优先展示 ^j.*(n|y)$ 不会展示所有正则表达式匹配到的内容 而仅仅展示括号内正则表达式匹配到的内容 match:从头匹配 search:从整体匹配 2.贪婪匹配与非贪婪匹配 正则表达式默认都是贪婪匹配的 将贪婪变成非贪婪只需要在正则表达式后面加? .* 贪婪 .*? 非贪婪 """
select * from emp where name regexp '^j.*(n|y)$'; #j开头 n或y 结尾的
单表查询其他参考:https://www.cnblogs.com/linhaifeng/articles/7267592.html
多表查询
#建表 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,'运营'), (205,'公关'); 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 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
select * from dep,emp; #交叉连接：不适用任何匹配条件 结果 笛卡尔积 select * from emp,dep where emp.dep_id = dep.id; # 相对来说推荐用下面的inner join """ MySQL也知道 你在后面查询数据过程中 肯定会经常用到拼表操作 所以特地给你开设了对应的方法 inner join 内连接 left join 左连接 right join 右连接 union 全连接 """
# inner join 内连接：只连接匹配的行 select * from emp inner join dep on emp.dep_id = dep.id; # 只拼接两张表中公有的数据部分 # left join 左连接：优先显示左表全部记录 select * from emp left join dep on emp.dep_id = dep.id; # 左表所有的数据都展示出来 没有对应的项就用NULL # right join 右连接：优先显示右表全部记录 select * from emp right join dep on emp.dep_id = dep.id; # 右表所有的数据都展示出来 没有对应的项就用NULL # 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不支持全外连接 full JOIN #注意 union与union all的区别：union会去掉相同的纪录
#1：子查询是将一个查询语句嵌套在另一个查询语句中。 #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。 #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 #4：还可以包含比较运算符：= 、 !=、> 、<等
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
以下是in的演示 （其他的可以举一反三）
""" 子查询就是我们平时解决问题的思路 分步骤解决问题 第一步 第二步 ... 将一个查询语句的结果当做另外一个查询语句的条件去用 """ # 查询部门是技术或者人力资源的员工信息 1 先获取部门的id号 select id from dep where name='技术' or name = '人力资源'; 2 再去员工表里面筛选出对应的员工 select * from emp where dep_id in (200,201); select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');
# 查询平均年龄在25岁以上的部门名称 """只要是多表查询 就有两种思路 联表 子查询""" # 联表操作 1 先拿到部门和员工表 拼接之后的结果 2 分析语义 得出需要进行分组 select dep.name from emp inner join dep on emp.dep_id = dep.id group by dep.name having avg(age) > 25 ; """涉及到多表操作的时候 一定要加上表的前缀""" # 子查询 select name from dep where id in (select dep_id from emp group by dep_id having avg(age) > 25);
详细：https://www.cnblogs.com/linhaifeng/articles/7267596.html
总结
表的查询结果可以作为其他表的查询条件 也可以通过起别名的方式把它作为一个张虚拟表根其他表关联 """ 多表查询就两种方式 先拼接表再查询 子查询 一步一步来 """
浙公网安备 33010602011771号