一、单表查询准备

  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. #插入记录
  15. #三个部门：教学，销售，运营
  16. insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
  17. ('YLY','male',18,'20210101','CN帅giegie代言人',6.33,401,1), #以下是教学部
  18. ('tom','male',78,'20150302','teacher',1000000.31,401,1),
  19. ('kevin','male',81,'20130305','teacher',8300,401,1),
  20. ('tony','male',73,'20140701','teacher',3500,401,1),
  21. ('owen','male',28,'20121101','teacher',2100,401,1),
  22. ('jack','female',18,'20110211','teacher',9000,401,1),
  23. ('jenny','male',18,'19000301','teacher',30000,401,1),
  24. ('sank','male',48,'20101111','teacher',10000,401,1),
  25. ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
  26. ('呵呵','female',38,'20101101','sale',2000.35,402,2),
  27. ('西西','female',18,'20110312','sale',1000.37,402,2),
  28. ('乐乐','female',18,'20160513','sale',3000.29,402,2),
  29. ('拉拉','female',28,'20170127','sale',4000.33,402,2),
  30. ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
  31. ('程咬金','male',18,'19970312','operation',20000,403,3),
  32. ('程咬银','female',18,'20130311','operation',19000,403,3),
  33. ('程咬铜','male',18,'20150411','operation',18000,403,3),
  34. ('程咬铁','female',18,'20140512','operation',17000,403,3);

二、查询关键字

  1. 1、select
  2. 2、from
  3. 3、where
  4. 4、group by
  5.     聚合函数
  6.     having
  7. 3、distinct
  8. 4、order by

1、select命令

  1. select
  2.     控制查询表中的哪些字段对应的数据

2、from

  1. from
  2.     控制查询的表

3、where (分组前筛选)

  1. # where其实就是对数据进行筛选
  2.     格式：
  3.         where [字段] in ();
  4.         where [字段] not in ();
  5.         where [字段] between num_a and num_b;
  6.             条件:有or和and。
  7.         where [字段] like "% _";
  8.         where [字段] >、<、= num_c;
  9.         where [字段] is null;
  10.         where [字段] char_length(num_d);
  13. 案例1.查询id大于等于3小于等于6的数据
  14.     select id,name from emp where id >= 3 and id <= 6;
  15.     select *  from emp where id between 3 and 6;  
  17. 案例2.查询薪资是20000或者18000或者17000的数据
  18.     select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
  19.     select * from emp where salary in (20000,18000,17000);  # 简写
  21. """
  22. 模糊查询
  23.     关键字       like
  24.     关键符号
  25.                 %:匹配任意个数的任意字符
  26.                 _:匹配单个个数的任意字符
  27. """
  28. 案例3.查询员工姓名中包含o字母的员工姓名和薪资
  29.     select name,salary from emp where name like '%o%';
  31. 案例4.查询员工姓名为四个字符组成的员工姓名和薪资
  32.     select name,salary from emp where name like '____';
  33.     select name,salary from emp where char_length(name) = 4;
  35. 案例5.查询id小于3或者大于6的数据
  36.     select *  from emp where id not between 3 and 6;
  38. 案例6.查询薪资不在20000，18000，17000范围的数据
  39.     select * from emp where salary not in (20000,18000,17000);
  41. 案例7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
  42.     select name,post from emp where post_comment = NULL;  # 查询为空！
  43.     select name,post from emp where post_comment is NULL;
  44.     select name,post from emp where post_comment is not NULL;

4、group by

  1. 分组
  2.     按照某个指定的条件将单个单个的数据分为一个个整体
  3.          eg:
  4.             咱班按照座位横向分组
  5.              咱班按照年龄分组
  6.              咱班按照省份分组
  7.     应用场景
  8.         eg:
  9.             求每个部门的平均薪资
  10.             求每个国家的人均GDP
  11.             求男女平均薪资
  13. 格式：
  14.     select [分组的信息] from [表名] [where筛选] group by [分组字段] [having过滤];
  15. """
  16. 分组之后不再以单个个体为研究对象 也无法直接再获取单个个体的数据
  17. 研究对象应该是分组的整体
  18.     分组之后默认只能直接获取到分组的依据 其他字段数据无法直接获取
  20. 如果需要实现上述要求 还是修改sql_mode
  21.     set global sql_mode='only_full_group_by';
  22. """
  23. 案例1.获取每个部门的最大薪资
  24.     select post,max(salary) from emp group by post;
  26. 案例2.统计每个部门的人数
  27.     select post,count(id) from emp group by post;
  29. 案例3.获取每个部门的员工姓名
  30.     select post,group_concat(name,'|',salary) from emp group by post;
  31.     # group_concat用于分组之后获取分组以外的字段数据并支持拼接
  32.     # concat用于分组之前的拼接操作
  33.         select id,concat(name,'|',salary) as '嘿嘿' from emp;
  34.     # concat_ws当多个字段连接符相同的情况下推荐使用
  35.         select id,concat_ws('|',name,sex,salary,age) from emp;
  37. """
  38. 在查看结果的时候可以给字段起别名
  39.     select post as '部门',max(salary) as '最高薪资' from emp group by post;
  40.     as可以省略但是为了语义更加明确建议不要省略
  41. """

4.1 聚合函数

  1. max()
  2. min()
  3. sum()
  4. count()
  5. avg()
  6. # 上述聚合函数都是在分组之后使用 用于操作整体数据

4.2 having (分组后过滤)

  1. where与having都是用来筛选数据的
  2.     但是where用于分组之前的筛选、having用于分组之后的筛选
  3.     为了人为的区分开 我们将where用筛选来形容 having用过滤来形容
  5. # 统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
  6.     select post,avg(salary) from emp where age > 30 group by post having avg(salary)>10000;
  8. """
  9. 将一个复杂的查询题拆分成多个简单的小题
  10. """

3、distinct去重

  1. """
  2. 去重的前提示是存在一模一样的数据
  3.     如果存在主键肯定无法去重
  4. """
  5. # 对有重复的展示数据进行去重操作 一定要是重复的数据
  6. select distinct id,age from emp;
  7. select distinct post from emp;

4、order by排序

  1. order by默认是升序  默认的关键字是asc
  2.     select * from emp order by salary asc;
  3.     也可以改为降序 desc
  4.     select * from emp order by salary desc;
  6. # order by排序支持多个字段组合(第一个不行 就往后继续排)
  7.     select * from emp order by age,salary;
  8.     select * from emp order by age asc,salary desc;

三、单表查询总结

1、写单表查询语句顺序

  1. 当查询比较复杂的时候，一步步写，先where筛选，后having过滤，最后再排序,去重。

2、关键词总结

  1. 单表查询的格式:
  2.     SELECT [查询字段] FROM [表名] [where筛选语句] [显示规则] [having过滤条件]
  3. ----------------------------------------------------
  4. where
  5.     like "% _"
  6.     between
  7.     in / not in
  8.     and / or
  9.     is / is not
  10.     char_length(字段)=num
  12. group by
  13.     修改sql_mode
  14.     set global sql_mode='only_full_group_by';
  15.     group_concat("")    #将多个字段值进行拼接
  17. distinct
  18.     select distinct id,age from emp;
  19. order by
  20.     select * from emp order by age,salary;
  21. ----------------------------------------------------
  22. 函数方法：
  23. 1、
  24.     concat(str1,str2,...)    将多个字段进行拼接
  25.         用法:
  26.          select id,concat(name,'|',salary) from emp;
  27.     concat_ws(separator，str1,str2,...)        用分隔符将多个字段进行拼接
  28.         用法:
  29.          select id,concat_ws('|',name,salary,age,post) from emp;
  30.     group_concat(str1,str2,..)  分组之后的拼接
  31.         用法：
  32.          select group_concat("|",name,"-",age,"-",post,"|") from emp group by post;
  33. 2、
  34.     max(str)
  35. 3、
  36.     min(str)
  37. 4、
  38.     sum(str1)
  39. 5、
  40.     count(id)        # 计数有多少个不重复的数据
  41. 6、
  42.     avg(str1)

四、上述案例集合

  1. 1.查询id大于等于3小于等于6的数据
  2. 2.查询薪资是20000或者18000或者17000的数据
  3. 3.查询员工姓名中包含o字母的员工姓名和薪资
  4. 4.查询员工姓名为四个字符组成的员工姓名和薪资
  5. 5.查询id小于3或者大于6的数据
  6. 6.查询薪资不在20000，18000，17000范围的数据
  7. 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
  10. 1.获取每个部门的最大薪资
  11. 2.统计每个部门的人数
  12. 3.获取每个部门的员工姓名
  14. 1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门

五、测验

  1. 1. 查询岗位名以及岗位包含的所有员工名字
  2. 2. 查询岗位名以及各岗位内包含的员工个数
  3. 3. 查询公司内男员工和女员工的个数
  4. 4. 查询岗位名以及各岗位的平均薪资
  5. 5. 查询岗位名以及各岗位的最高薪资
  6. 6. 查询岗位名以及各岗位的最低薪资
  7. 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

答案：

  1. 1、select post as "岗位",group_concat(name) as "姓名" from emp group by post;
  2. 2、select post as "岗位",count(id) as "员工个数" from emp group by post;
  3. 3、select post as "岗位",count(sex="male") as "男员工个数",count(sex="female") as "女员工个数" from emp group by post;
  4. 4、select post as "岗位",avg(salary) as "平均薪资" from emp group by post;
  5. 5、select post as "岗位",max(salary) as "最高薪资" from emp group by post;
  6. 6、select post as "岗位",min(salary) as "最低薪资" from emp group by post;
  7. 7、select sex,avg(salary) from emp group by sex;