查询关键字
'''
select
where
group by
having
distinct
order by
limit
regexp
like
'''
前期表准备及注意事项
create database day47;
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',100000.25,401,1),
('kevin','male',81,'20130305','teacher',8541.21,401,1),
('tony','male',12,'20140501','teacher',8745.21,401,1),
('owen','male',28,'20121201','teacher',9542.13,401,1),
('jack','female',18,'20120622','teacher',4300.43,401,1),
('jenny','male',18,'19001212','teacher',30000.33,401,1),
('sank','male',48,'20101101','teacher',6300.33,401,1),
('哈哈','female',58,'20110801','sale',9850.33,402,2),
('呵呵','female',27,'20110301','sale',12345.33,402,2),
('西西','female',38,'20120301','sale',23321.33,402,2),
('乐乐','female',48,'20130301','sale',9999.33,402,2),
('拉拉','female',58,'20140301','sale',8456.33,402,2),
('僧龙','male',28,'20090301','operation',12345.33,403,3),
('程咬金','male',29,'20090301','operation',13456.33,403,3),
('程咬银','female',38,'20100301','operation',14567.33,403,3),
('程咬铜','male',29,'20110301','operation',15678.33,403,3),
('程咬铁','female',40,'20120301','operation',16789.33,403,3);
# 当表字段特别多,cmd窗口展示的时候错乱,可以使用\G分行展示:select * from emp\G;
# 个别电脑在插入中文的时候还是会出现乱码或者空白的现象,你可以将字符编码统一设置成GBK
几个重要关键字的执行顺序
'''
书写顺序
select id,name from emp where id>3;
执行顺序
from
where
select
虽然执行顺序和书写顺序不一致,在写sql语句的时候可能不知道怎么写,就按照书写顺序的方式写sql
select * 先用*号占位
之后去补全后面的sql语句
最后将*号替换后你想要的具体字段
'''
where筛选条件
'''
作用:是对整体数据的一个筛选操作
1.查询id大于等于3小于等于6的数据
select * from emp where id>=3 and id<=6;
select * from emp where id between 3 and 6; # 两者等价
2.查询薪资是12345.33或者23321.33或者16789.33的数据
select * from emp where salary=12345.33 or salary=23321.33 or salary=16789.33;
select * from emp where salary in (12345.33,23321.33,16789.33);
3.查询员工姓名中包含字母o的员工的姓名和薪资
模糊查询:
like
%匹配任意多个字符
_匹配任意单个字符
select name,salary from emp where name like '%o%';
4.查询员工姓名是由四个字符组成的姓名和薪资
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.查询薪资不在12345.33,23321.33,16789.33范围的数据
select * from emp where salary not in (12345.33,23321.33,16789.33);
7.查询岗位描述为空的员工姓名和岗位名(针对null不能用=,用is)
select name,post from emp where post_comment is null;
select name,post from emp where post_comment is not null; # 相反不为空
'''
group by分组
'''
1.按照部门分组
select * from emp group by post;
分组之后,最小可操作单位应该是组,而不是组内的单个数据
上述命令在你没有设置严格模式的时候是可正常执行的,返回的是分组之后每个组的第一条数据,但是这不符合分组的规范:分组之后不应该考虑单个数据,而应该以组为操作单位(分组之后,没办法直接获取组内单个数据)
如果设置了严格模式,那么上述命令会直接报错
set global sql_mode = 'strict_trans_tables,only_full_group_by,pad_char_to_full_length';
设置严格模式之后,分组默认只能拿到分组的依据
select post from emp group by post;
按照什么分组就只能拿到分组,其它字段不能直接获取,需要借助于一些方法(聚合函数:max,min,avg,sum,count)
什么时候需要分组?--->关键字:每个,平均,最高,最低
1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post; # as可以给字段起别名,也可以直接省略不写(但是不推荐,因为省略的话语意不明确,容易错乱)
select post '部门',max(salary) '最高薪资' from emp group by post;
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(name) 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) as '名字',concat('sal:',salary) as '薪资' from emp;
# concat_ws如果多个字段之间的连接符号是相同的情况下,你可以直接使用concat_ws来完成
select concat_ws(':',name,age,sex) 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;
7.查询每个人的年薪(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; # 不分组,默认整体就是一组
1.统计各部门年龄在30岁以上的员工平均薪资
select post,avg(salary) from emp where age>30 group by post;
'''
having分组之后的筛选条件
'''
having的语法跟where是一致的,只不过having是在分组之后进行的过滤操作;即having是可以直接使用聚合函数的
1.统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于20000的部门
select post,avg(salary) from emp where age>30 group by post having avg(salary)>20000;
'''
distinct去重
'''
一定要注意,必须是完全一样的数据才可以去重!!!
一定不要将主键忽视了,有主键存在的情况下,是不可能去重的
[
{'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 distinct age from emp;
'''
order by排序
'''
select * from emp order by salary;
select * from emp order by salary asc;
order by默认是升序,asc可以省略不写;也可以修改为降序,desc
select * from emp order by salary desc;
select * from emp order by age desc,salary asc;
先按照age降序排,如果碰到age相同,则再按照salary升序排
1.统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于10000的部门,然后对平均工资降序排序
select post,avg(salary) from emp where age>10 group by post having avg(salary)>10000 order by avg(salary) desc;
'''
limit限制展示条数
'''
select * from emp;
针对数据过多的情况,通常都是做分页处理
select * from emp limit 3; # 只展示3条数据
select * from emp limit 0,5;
select * from emp limit 5,5;
第一个参数是起始位置,第二个参数是展示条数
'''
regexp正则
'''
select * from emp where name regexp '^j.*(n|y)$'; # 以j开头,n或者y结尾,中间任意数量字符(.*贪婪匹配)
'''