表查询关键字

数据准备

  1. create table emp(
  2.   id int primary key auto_increment,
  3.   name varchar(20) not null,
  4.   sex enum('male','female') not null default 'male',
  5.   age int(3) unsigned not null default 28,
  6.   hire_date date not null,
  7.   post varchar(50),
  8.   post_comment varchar(100),
  9.   salary double(15,2),
  10.   office int,
  11.   depart_id int
  12. );
  14. insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
  15. ('aa','male',18,'20170301','teacher',7300.33,401,1), 
  16. ('bb','male',38,'20150302','teacher',1000000.31,401,1),
  17. ('cc','male',31,'20130305','teacher',8300,401,1),
  18. ('dd','male',43,'20140701','teacher',3500,401,1),
  19. ('ee','male',28,'20121101','teacher',2100,401,1),
  20. ('ff','female',18,'20110211','teacher',9000,401,1),
  21. ('gg','male',18,'19000301','teacher',30000,401,1),
  22. ('hh','male',48,'20101111','teacher',10000,401,1),
  23. ('ii','female',48,'20150311','sale',3000.13,402,2),
  24. ('jj','female',38,'20101101','sale',2000.35,402,2),
  25. ('kk','female',18,'20110312','sale',1000.37,402,2),
  26. ('ll','female',18,'20160513','sale',3000.29,402,2),
  27. ('mm','female',28,'20170127','sale',4000.33,402,2),
  28. ('nn','male',28,'20160311','operation',10000.13,403,3), 
  29. ('oo','male',18,'19970312','operation',20000,403,3),
  30. ('pp','female',18,'20130311','operation',19000,403,3),
  31. ('qq','male',18,'20150411','operation',18000,403,3),
  32. ('rr','female',18,'20140512','operation',17000,403,3);

from

select用于指定查询的字段from用于指定查询的表

  1. select 字段,字段 from 数据库.表名;

where（筛选）

1.查询id大于等于3 小于等于6的数据

关键字：and、between

  1. select * from emp where id>=3 and id<=6;
  1. select * from emp where id between 3 and 6;

2.查询薪资是20000或者18000或者17000的数据

关键字：or 、in

  1. select * from emp where salary=20000 or salary=18000 or salary=17000;
  1. select * from emp where salary in (20000,18000,17000);

3.查询id小于3或者大于6的数据

关键字：not

  1. select * from emp where id not between 3 and 6;

4.查询薪资不在20000,18000,17000的数据

关键字：not in

  1. select * from emp where salary not in (20000,18000,17000);

5.查询岗位描述为空的数据

关键字：null

  1. select * from emp where post_comment=null;

6.查询员工姓名中包含字母o的员工姓名和薪资

关键字：like（模糊查询的关键字），关键符号：%（匹配任意个数的任意字符）、_（匹配单个个数的任意字符）

  1. select name,salary from emp where name like '%o%';

7.查询员工姓名是由两个字符组成的数据

  1. select * from emp where name like '__';
  1. select * from emp where char_length(name)=2;

group by（分组）

分组就是按照指定的条件，将不同个体组织成一个整体，之所以要分组是为了快速统计出数据

关键字：group by

  1. select * from emp group by post;
  1. ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在mysql5.7及以上版本中默认自带sql_mode=only_full_group_by，该模式要求的分组之后默认只可以直接获取分组的依据不能直接获取其他字段。原因是分组的目的就是按照分组的条件来管理诸多数据 最小单位应该是分组的依据而不是单个单个的数据

  1. select post from emp group by post;

聚合函数

函数 解释
max 最大值
min 最小值
sum 求和
count 计数
avg 平均值

1.统计每个部门的最高薪资

  1. select post,max(salary) from emp group by post;

2.统计每个部门的平均薪资

  1. select post,avg(salary) from emp group by post;

3.统计每个部门的员工人数

  1. select post,count(id) from emp group by post;

4.统计每个部门的月工资开销

  1. select post,sum(salary) from emp group by post;

5.统计每个部门最小的年龄数

  1. select post,min(age) from emp group by post;

6.统计每个部门下所有员工的姓名

关键字：group_concat()

  1. select post,group_concat(name) from emp group by post;

7.统计每个部门下所有员工的姓名和年龄

  1. select post,group_concat(name,age) from emp group by post;
  1. select post,group_concat(name,'|',age) from emp group by post;

补充：关键字：as可以给字段起别名

  1. select post,group_concat(name) as '姓名' from emp group by post;

having（过滤）

having与where的功能是一模一样的 都是对数据进行筛选，区别在于where用在分组之前的筛选 having用在分组之后的筛选。为了更好的区分所以将where说成筛选 having说成过滤

关键字：having

统计每个部门年龄在30岁以上的员工的平均薪资并且保留平均薪资大于10000的部门

  1. select post, avg(salary) as avg_salary from emp where age>30 group by post having avg_salary>10000;

补充：针对聚合函数，还需要在其他地方作为条件使用，可以先起别名

distinct（去重）

关键字：distinct

  1. select distinct age from emp;

注意：如果数据有主键肯定无法去重

order by（排序）

关键字：order by、asc（升序）、desc（降序）

1.按照薪资高低排序

  1. select * from emp order by salary;  -- 默认是升序(从小到大)
  2. select * from emp order by salary desc;  -- 关键字 desc 降序（从大到小）

2.先按照年龄升序排序 如果年龄相同 则再按照薪资降序排序

  1. select * from emp order by age asc,salary desc;

3.统计各部门年龄在10岁以上的员工平均工资 并且保留平均工资大于1000的部门并按照从大到小的顺序排序

  1. select post,avg(salary) as avg_salary from emp where age >10 group by post having avg_salary >1000 order by avg_salary desc;

limit（分页）

分页就是限制展示条数

关键字：limit

1.限制只展示五条数据

  1. select * from emp limit 5;

2.分页效果

  1. select * from emp limit 5,5;

3.查询工资最高的人的详细信息

  1. select * from emp order by salary desc limit 1;

regexp（正则）

  1. select * from emp where name regexp '^c.*(c)$';