表查询

目录

• 查表
  • select
  • where
  • group by
  • having
  • distinct
  • order by
  • limit
  • regexp
  • 多表操作

查表

前期表的准备

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
);

select

select id name from emp where id > 3;
#执行顺序
from，where，select
select * 先用*号占位，之后去不全后面的sql语句，最后将*号换掉你想要的具体字段

where

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

  select id,name,age from emp where id>=3 and id<=6;
  select id,name from emp where id between 3 and 6;
  
  

• 查询薪资是20000或者18000or17000的数据

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

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

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

• 查询员工姓名有四个字符组成的姓名和薪资

  select name,salary from emp where name like '____';
  select name,salary from emp where char_length(name)=4;
  

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

  select * from emp where id between 3 and 6; 
  

• 查询薪资不在20000，18000，17000范围的数据

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

• 查询岗位描述为空的员工姓名和岗位名，针对null不要等号，用id

  select name,post from emp where post_comment=NULL;
  Select name,post from emp where post_comment is NULL;
  

group by

# 分组实际应用场景  分组应用场景非常的多
	男女比例
    部门平均薪资
    部门秃头率
    国家之间数据统计

# 1	按照部门分组
select * from emp group by post;
"""
分组之后 最小可操作单位应该是组 还不再是组内的单个数据
	上述命令在你没有设置严格模式的时候是可正常执行的 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据)
	如果设置了严格模式 那么上述命令会直接报错 
"""
set global sql_mode = 'strict_trans_tables,only_full_group_by';

设置严格模式之后  分组 默认只能拿到分组的依据
select post from emp group by post;  
按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助于一些方法(聚合函数)


"""
什么时候需要分组啊？？？
	关键字 
		每个 平均 最高 最低 
	
	聚合函数
		max
		min
		sum
		count
		avg
"""
# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
select post '部门',max(salary) '最高薪资' from emp group by post;
# as可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱

# 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(age) 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),concat('SAL:',salary) 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;

# 查询每个人的年薪  12薪
select name,salary*12 from emp;

having

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

"""
having的语法根where是一致的
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
"""

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;
  select * from emp order by salary desc;
  
  """
  order by默认是升序  asc 该asc可以省略不写
  也可以修改为降序     desc
  """
  select * from emp order by age desc,salary asc;
  # 先按照age降序排  如果碰到age相同 则再按照salary升序排
  
  

• 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序

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

limit

• 使用：

  select * from emp;
  """针对数据过多的情况 我们通常都是做分页处理"""
  select * from emp limit 3;  # 只展示三条数据
  
  select * from emp limit 0,5;
  select * from emp limit 5,5;
  第一个参数是起始位置
  第二个参数是展示条数
  

regexp

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

多表操作

• 前期准备

  #建表
  create table dep(
  id int,
  name varchar(20) 
  );
  
  create table emp(
  id int primary key auto_increment,
  name varchar(20),
  sex enum('male','female') not null default 'male',
  age int,
  dep_id int
  );
  
  #插入数据
  insert into dep values
  (200,'技术'),
  (201,'人力资源'),
  (202,'销售'),
  (203,'运营');
  
  insert into emp(name,sex,age,dep_id) values
  ('jason','male',18,200),
  ('egon','female',48,201),
  ('kevin','male',18,201),
  ('nick','male',28,202),
  ('owen','male',18,203),
  ('jerry','female',18,204);
  

• 表查询

  select * from dep,emp;  # 结果   笛卡尔积
  """
  了解即可 不知道也没关系
  """
  
  select * from emp,dep where emp.dep_id = dep.id;
  
  """
  MySQL也知道 你在后面查询数据过程中 肯定会经常用到拼表操作 
  
  	inner join  内连接
  	left join   左连接
  	right join  右连接
  	union		全连接
  """
  # inner join  内连接
  select * from emp inner join dep on emp.dep_id = dep.id;
  # 只拼接两张表中公有的数据部分
  
  # left join   左连接
  select * from emp left join dep on emp.dep_id = dep.id;
  # 左表所有的数据都展示出来 没有对应的项就用NULL
  
  # right join  右连接
  select * from emp right join dep on emp.dep_id = dep.id;
  # 右表所有的数据都展示出来 没有对应的项就用NULL
  
  # union		全连接  左右两表所有的数据都展示出来
  select * from emp left join dep on emp.dep_id = dep.id
  union
  select * from emp right join dep on emp.dep_id = dep.id;
  

• 子查询

  """
  子查询就是我们平时解决问题的思路
  	分步骤解决问题
  		第一步
  		第二步
  		...
  将一个查询语句的结果当做另外一个查询语句的条件去用
  """
  # 查询部门是技术或者人力资源的员工信息
  	1 先获取部门的id号
      2 再去员工表里面筛选出对应的员工
      select id from dep where name='技术' or name = '人力资源';
      
      select name from emp where dep_id in (200,201);
      
      
      select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');