前期表准备

  1. create table emp(
  2.     id int not null unique 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 
  17. emp(name, sex, age, hire_date, post, salary, office, depart_id) 
  18. values
  19. ('Jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1),#以下是教学部 
  20. ('tom','male',78,'20150302','teacher',1000000.31,401,1),
  21. ('kevin','male',81,'20130305','teacher',8300,401,1),
  22. ('tony','male',73,'20140701','teacher',3500,401,1),
  23. ('owen','male',28,'20121101','teacher',2100,401,1),
  24. ('jack','female',18,'20110211','teacher',9000,401,1),
  25. ('Jenny','male',18,'19000301','teacher',30000,401,1),
  26. ('sank','male',18,'20101111','teacher',10000,401,1),
  27. ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部
  28. ('呵呵','female',38,'20101101','sale',2000.35,402,2),
  29. ('西西','female',18,'20110312','sale',1000.37,402,2),
  30. ('乐乐','female',18,'20160513','sale',3000.29,402,2),
  31. ('拉拉','female',28,'20170127','sale',4000.33,402,2),
  32. ('僧龙','male',28,'20160311','operation',10000.13,403,3),#以下是运营部门
  33. ('程咬金','male',18,'19970312','operation',20000,403,3),
  34. ('程咬银','female',18,'20130311','operation',19000,403,3),
  35. ('程咬铜','male',18,'20150411','operation',18000,403,3),
  36. ('程咬铁','female',18,'20140512','operation',17000,403,3);

几个重要关键字的执行顺序

  1. #书写顺序
  2. select id,name from emp where id>3;
  3. #执行顺序
  4. from -> where -> select
  6. """
  7. 书写方式
  8.   先按照书写顺序的方式写sql
  9.     select * 先用*号占位
  10.     之后去补全后面的sql语句
  11.     最后将*号替换成想要的具体字段
  12. """

where筛选条件

  1. #作用：对整体数据的一个筛选操作
  2.  #1.查询id大于等于3小于等于6的数据
  3.   select id,name,age from emp where id>=3 and id<=6;
  4.  或：select id,name from emp where id between 3 and 6 ;
  6.  #2.查询薪资是2000或者18000或者17000的数据
  7.   select * from emp where salary=20000 or salary=18000 or salary=17000;
  8. 或：select * from emp where salary in (20000,18000,17000);
  10.   #3.查询员工姓名中包含字母o的员工姓名和薪资
  11.   """
  12.    模糊查询 like
  13.       % 匹配任意多个字符
  14.       - 匹配任意单个字符
  15.   """
  16.   select name,salary from emp where name like '%o%';
  18.  #4.查询员工姓名是由4个字符组成的 姓名和薪资
  19.     select name,salary from emp where name like '____';
  20.    #或： char_length()
  21.     select name,salary from emp where char_length(name)=4;
  23.  #5. 查询id小于3或者id大于6的数据
  24.   select * from emp where id not between 1 and 6;
  26.  #6.查询薪资不在20000，18000，17000范围的数据
  27.    select * from emp where salary not in (20000,18000,17000);
  29.  #7.查询岗位描述为空的员工姓名和岗位名  针对null不用等号，用is
  30.     select name,post from emp where post_comment is null;

group by 分组

  1. #分组实际应用场景
  2.     男女比例、部门平均薪资、国家之间数据统计。。。
  4. #1.按照部门分组
  5. select * from emp group by post;
  6. """
  7. 分组后最小可操作单位应该是组，而不再是组内的单个数据
  8.   上述命令在没有设置严格模式的时候是可以正常执行的，返回的是分组之后 每个组的第一条数据， 但是这不符合分组的规范：分组之后不应该考虑单个数据，而应该以组为单位操作（分组后不能获取组内单个数据）
  9.   如果设置了严格模式，上述命令会直接报错
  10. """
  11. #设置严格模式和分组配置
  12. set global sql_mode='strict_trans_tables,only_full_group_by';
  14.  设置严格模式后 分组 默认只能拿到分组的依据
  15.    select post from emp group by post;
  16.   #按照什么分组就只能拿到分组，其他字段不能直接获取，需要借助于一些方法(聚合函数)
  17. """
  18. 什么时候需要分组？
  19.   关键字： 每个、 平均 、最高 、最低等
  20.   聚合函数： 
  21.           max
  22.           min
  23.           avg
  24.           sum
  25.           count
  26. """
  27.  #1.获取每个部门的最高薪资
  28.   select post,max(salary) from emp group by post;
  29.   select post as '部门',max(salary) as '最高薪资' from emp group by post;
  30.    # as 可以给字段起别名，也可以直接省略不写， 但不推荐 语义不明确
  31.  #2.获取每个部门最低薪资
  32.    select post,min(salary) from emp group by post;
  33.  #3.获取每个部门的平均薪资
  34.     select post,avg(salary) from emp group by post;
  35.  #4.获取每个部门的工资总和
  36.      select post,sum(salary) from emp group by post;
  37.  #5.获取每个部门的人数
  38.       select post,count(id) from emp group by post;
  39.      #count查计数使用唯一的值就准确了，不止可以使用id；只有null值不行
  41.  #6.查询分组之后的部门名称和每个部门下所有的员工姓名
  42.    # group_concat 支持获取分组之后的其他字段值，还支持拼接
  43.  select post,group_concat(name) from emp group by post;
  44.  select post,group_concat(name,'DSB') emp group by post;
  45.  select post,group_concat(name,':',salary) emp group by post;
  47.    #concat 不分组的时候用concat拼接
  48.   select concat('NAME',name),concat('SAL',salary) from emp;
  49.    #concat_ws 如果多个字段之间连接符相同，可使用concat_ws
  50.   select concat_ws(':',name,age,sex) from emp;#使用:号作分隔
  52.  #7.查询每个人的年薪 12薪
  53.  select name,salary*12 from emp;
  55. #补充 as语法不单单可以给字段起别名，还可以给表临时起别名
  56. select * from emp as t1;

聚合函数

  1. max 最大
  2. min 最小
  3. avg 平均
  4. sum 求和
  5. count 总数

分组注意事项

  1. #关键字where和group by同时出现的时候 group by必须在where的后面
  2.  where先对整体数据进行过滤之后再分组操作
  3.  where筛选条件不能使用聚合函数
  5. select id,name,age from emp where max(salary)>3000; #报错:非法聚合
  6. select max(salary) from emp ;  #不分组 默认整体就是一组
  8. #统计各部门年龄再30岁以上的员工平均薪资
  9.   1.先求所有年龄大于30岁的员工
  10.     select * from emp where age>30;
  11.   2.再对结果进行分组(按部门)
  12.     select * from emp where age >30 group by post;
  13.   3.合并查询，加上聚合函数
  14.   select post，avg(salary) as '平均薪资' from emp where age>30 group by post;

having 分组之后筛选条件

  1. """
  2. having 的语法跟where是一致的
  3. 只不过having是在分组之后进行的过滤操作
  4. 即having是可以直接使用聚合函数的
  5. """
  6. #统计各部门年龄在30岁以上的员工平均工资，并且保留平均薪资大于10000的部门
  7. select post,avg(salary) from emp 
  8.         where age>30 
  9.         group by post
  10.         having avg(salary)>10000 ;

distinct 去重

  1. """
  2. 必须是完全一样的数据才可以去重
  3. 不要将主键忽视了，查询时有主键存在是不可能去重的
  4. """
  5. select distinct id,age from emp; #有主键存在，所有数据都会列出来
  6. select distinct age from emp;

order by 排序

  1. select * from emp order by salary;
  2. select * from emp order by salary asc;
  3. """
  4. order by 默认是 升序   asc  该asc可以省略不写
  5. 也可以修改为     降序   desc  
  6. """
  7. select * from emp order by salary desc;
  8. #可以设置次要条件排序
  9. select * from emp order by age desc,salary asc;
  10.     #先按照age降序排，如果碰到age相同 则按照salary升序排
  12. # 统计各部门年龄10岁以上的员工平均薪资并且保留平均薪资大于1000的部门，然后平均薪资降序排序
  13.   select post,avg(salary) from emp
  14.         where age>10
  15.         group by post
  16.         having avg(salary)>1000
  17.         order by avg(salary) desc ;

limit限制展示条数

  1. select * from emp limit 5,5;
  2. #第一个参数是其实位置，第二个参数是展示条数

正则

  1. select * from emp name regexp '^j.*(n|y)$';

联表查询

建表

  1. #建部门表
  2. create table dep(
  3. id int,
  4. name varchar (20)
  5. );
  7. #建员工表
  8. create table emp(
  9. id int primary key auto_increment,
  10. name varchar(20),
  11. sex enum('male','female') not null default 'male' ,
  12. age int,
  13. dep_id int
  14. );
  16. #插入数据
  17. insert into dep values
  18. (200,'技术'),
  19. (201,'人力资源'),
  20. (202,'销售'),
  21. (203,'运营');
  23. insert into emp (name,sex,age,dep_id) values
  24. ('Jason','male',18,200),
  25. ('egon','female',48,201),
  26. ('kevin','male',18,201),
  27. ('nick','male',28,202),
  28. ('owen','male',18,203),
  29. ('jerry','female',18,204);

查询

  1. select * from dep,emp; #笛卡尔积 不是我们需要的
  2. select * from dep,emp where emp.dep_id=dep.id; #可以实现一一对应
  4. """
  5. mysql知道我们会经常用到拼表操作，所以也特地开设了对应的方法
  6.     inner join  内连接
  7.     left join   左连接
  8.     right join  右连接
  9.     union       全连接
  10. """
  11. # inner join 内连接
  12.  select * from emp inner join dep on emp.dep_id=dep.id;
  13.   #内连接，特点是只拼接两张表中共有的数据部分
  15. #left join 左连接
  16.   select * from emp left join dep on emp.dep_id=dep.id;
  17.   #左连接，左表所有的数据都展示出来，没有对应的项就用null
  19. #right join 右连接
  20.   select * from emp right join dep on emp.dep_id=dep.id;
  21.   #右表作为主表，所有数据都展示出来
  23. #union 全连接； 左连接+右连接 中间加union关键字
  24. select * from emp left join dep on emp.dep_id=dep.id
  25.  union
  26.  select * from emp right join dep on emp.dep_id=dep.id;
  27.  #左右两张表所有数据都展示出来

子查询

  1. """
  2. 子查询就是我们平时解决问题的思路
  3.   分步骤解决问题
  5. 将一个查询语句的结果当作另外一个查询语句的条件去使用
  6. """
  7. #例 查询部门是技术或者人力资源的员工信息
  8.   1.先筛选部门表获取部门id号
  9.   2.再去员工表里面筛选出对应的员工
  10.   select id from dep where name='技术' or name='人力资源'；
  11.     #假设结果为200、201
  12.   select name from emp where dep_id in (200,201);
  14. #可以直接拼接
  15.   select name from emp where dep_id in 
  16.     (select id from dep where name='技术' or name='人力资源');

知识点补充

  1. #查询平均年龄在25岁以上的部门名称    
  3. #方式1 联表操作
  4.     1.先拿到部门和员工表拼接之后的结果
  5.     select * from emp inner join dep on emp.dep_id=dep.id;
  6.     2.分析语义， 需要进行分组
  7.     select dep.id,dep.name,avg(age) from emp inner join dep on emp.dep_id=dep.id 
  8.       group by emp.name 
  9.       having avg(age)>25;
  10.   """涉及到多表操作时，一定要加上表的前缀"""
  12. #方式2 子查询
  13.   select name from dep where id in
  14.     ( select dep_id from emp group by dep_id 
  15.       having avg(age)>25 );
  17. #关键字 exist （了解）
  18.     只返回布尔值 True/False
  19.     返回True的时候外层查询语句执行
  20.     返回False的时候外层查询语句不再执行
  21.     select * from emp where exists
  22.         (select id from dep where id<3); #正常执行
  24.     select * from emp where exists
  25.         (select id from dep where id>300); #不执行