MySQL单表查询
关键字执行的优先级
from # 先找到表from where # where约束条件,在文件中找记录,不能和聚合函数合用 group by # 对记录进行分组,如果没有,就所有字段是一组 select # 执行select distinct # 如果没有group by,就用distinct去重,放在select之后 having # 进行having过滤,只有group by使用时才用having过滤 order by # 按条件进行排序 limit # 显示查询结果显示的条数
建表和数据准备
1、建表
字段名 数据类型 说明 员工id:id int primary key,auto_increment 姓名:name varchar not null 性别:gender enum("male","female") not null,default "male" 年龄:age int(3) not null,default 24,unsigned 入职日期:hire_date date not null 岗位:post varchar(50) 职位描述:post_comment varcahr(100) 薪水:salary double(15,2) 办公室:office int 一个部门一间办公室 部门编号:depart_id int
2、插入数据
insert into employee(name, gender, age, hire_date, post, post_content, salary, office, depart_id) values ('小赵','male',78,'20150302','sale',13000.31,401,1), ('小钱','male',81,'20130305','sale',9000,401,1), ('小孙','male',73,'20140701','sale',9600,401,1), ('小李','male',28,'20121101','sale',8600,401,1), ('小周','female',18,'20110211','hr',6000,403,2), # 人力资源 ('小吴','female',18,'19000301','hr',6500,403,2), ('小郑','female',48,'20101111','hr',6000,403,2), ('小王','female',48,'20101111','hr',7000,403,2), ('小冯','male',48,'20101111','it',200000,405,3), # 程序猿 ('小陈','male',48,'20101111','it',16000,405,3), ('小褚','male',48,'20101111','it',18000,405,3), ('小卫','male',48,'20101111','it',14000,405,3),
简单查询
# 查询指定字段 select name,hire_date,post,salary,office,depart_id from employee; # 用*查询表中所有数据 select * from employee; # 避免重复distinct select distinct post from employee; # 通过四则运算查询 select name, salary*12 from employee; # 计算出年薪并作为一个新字段显示 select name, salary*12 as annual_salary from employee; # 查询年薪 select name, salary, annual_salary from employee; # 定义显示格式 # concat(),用于连接字符串 select concat('姓名:', name,' 薪资:', salary) from employee; # concat_ws(),第一个参数是分割符 select concat_ws(':', name, salary) as annual_salary from employee; # 结合case语句使用: select
( case # case,开始一个条件语句 when name = '小周' then name # when 条件1 then 字段操作 when name = '小王' then concat(name,'_BIGSB') else concat(name, 'SB') # else, 字段操作 end # 结束条件语句 ) as new_name from employee;
where约束
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
比较运算符:> < >= <= != <> # 对值进行判断 between a and b # 值在a-b之间, 对范围进行判断,是一个闭区间 in(10,20,30) # 值是10,20或30 like '%' # 通配符可以是%(任意多个字符),_(一个字符) like 'a%' # 以a开头的字符 like '%a' # 以a结尾的字符 like '%a%' # 中间包含a的字符 like '_a' # 任意一个字符+a like 'a_' # a+任意一个字符
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# 单条件查询 select name from employee where post='sale'; # 多条件查询 select name, salary from employee where post='sale' and salary>7000; # 关键字between and select name, salary from employee where salary between 10000 and 16000; select name, salary from employee where salary not between 10000 and 16000; # 关键字is null select name,post_content from employee where post_content is null; select name,post_content from employee where post_content is not null; select name,post_content from employee where post_content =''; 注意''是空字符串,不是null ps:需要执行 update employee set post_comment='' where id=2;之后,再用上条查看,就会有结果了 # 关键字in select name,salary from employee where salary=10000 or salary=16000 or salary=18000; select name,salary from employee where salary in (8600,16000,18000); select name,salary from employee where salary not in (8600,16000,18000); # like模糊查询 # 通配符% select * from employee where name like '小%'; # 通配符_ select * from employee where name like '小_';
group by
# 单独使用group by select post from employee group by post; # 按照post字段分组,select查询的字段只能是post,要获取其他信息,需要借助函数来实现; # group by 和 group_concat()联合使用 select post, group_concat(name) from employee group by post; # 按照岗位分组,并展示组内成员 select post, group_concat(name) as emp_members from employee group by post; # 按照岗位分组,将组内成员作为新字段展示 # group by和 聚合函数 一起使用 select post, count(id) as count from employee broup by post; # 按照岗位分组,并查看每个组中有多少人 # ps:如果用unique字段作为分组依据,则每条记录自成一组,这样的分组没有意义
聚合函数
# 聚合函数聚合的是组的内容,如果没有分组,默认是一组 select count(*) from employee; select count(id) from employee where dapart_id=3; select max(salary) from employee; select min(salary) from employee; select avg(salary) from employee; select sum(salary) from employee; select sum(salary) from employee where dapart_id=1;
having过滤
# having和where不一样的地方在于: 执行优先级:where > group by > having # where 发生在group by之前,可以有任意字段,但绝对不能使用聚合函数; # having函数发生在group by之后,可以使用分组的字段,不能取到其他字段,可以使用聚合函数;
order by排序
# 按单列进行排序 select * from employee order by salary; select * from employee order by salary asc; # 升序 select * from employee order by salary desc; # 降序 # 按多列排序:先按age排序,如果年纪相同,按薪资排序 select * from employee order by age, salary desc;
limit限制查询的记录
select * from employee order by salary desc limit 3;# 默认初始位置0 select * from employee order by salary desc limit 0,5; # 从0开始,先查询第一条,包含这一条在内往后查询5条; select * from employee order by salary desc limit 5,5; # 从5开始,先查询第六条,往后查询5条;