day 47 如何查询表

今日内容概要

  • 如何查询表

    """
    select
    where
    group by
    having 
    distinct
    order by
    limit
    regexp
    like
    ...
    """
    

    今日内容详细

    前期表准备

    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
    );
    
    #插入记录
    #三个部门:教学,销售,运营
    insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
    ('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
    ('tom','male',78,'20150302','teacher',1000000.31,401,1),
    ('kevin','male',81,'20130305','teacher',8300,401,1),
    ('tony','male',73,'20140701','teacher',3500,401,1),
    ('owen','male',28,'20121101','teacher',2100,401,1),
    ('jack','female',18,'20110211','teacher',9000,401,1),
    ('jenny','male',18,'19000301','teacher',30000,401,1),
    ('sank','male',48,'20101111','teacher',10000,401,1),
    ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
    ('呵呵','female',38,'20101101','sale',2000.35,402,2),
    ('西西','female',18,'20110312','sale',1000.37,402,2),
    ('乐乐','female',18,'20160513','sale',3000.29,402,2),
    ('拉拉','female',28,'20170127','sale',4000.33,402,2),
    ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
    ('程咬金','male',18,'19970312','operation',20000,403,3),
    ('程咬银','female',18,'20130311','operation',19000,403,3),
    ('程咬铜','male',18,'20150411','operation',18000,403,3),
    ('程咬铁','female',18,'20140512','operation',17000,403,3);
    
    
    # 当表字段特别多 展示的时候容易错乱 可以使用\G分行展示
    select * from emp\G;
    

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

    # 书写顺序
    select id,name from emp where id > 3;
    # 执行顺序
    from 
    where
    select
    
    """
    虽然执行的顺序和书写的顺序不一致 先按照书写顺序的方式写sql
        select * 先用*号占位
        之后去补全后面的sql语句
        最后将*号替换成具体的字段
    
    """
    

    where筛选条件

    # 作用:是对整体数据的一个筛选操作
    # 1、查询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;  两者等价
    
    # 2、查询薪资是20000或者18000或者17000的数据
    select * from emp where salary=20000 or salary=18000 or salary=17000;
    select * from emp where salary in (20000,18000,17000);
    
    # 3、查询员工姓名中包含子没有o的员工的姓名和薪资
    """
    模糊查询
        like
            % 匹配任意多个字符
            - 匹配任意单个字符
    """
    select name,salary from emp where name like '%o%';
    
    # 4、查询员工姓名是由四个字符组成的 姓名和薪资  char_length()
    select name,salary from emp where name like '____';
    select name,salary from emp where char_length(name) =4;
    
    # 5、查询id小于3或者id大于6的数据
    select * from emp where id not between 3 and 6;
    
    # 6、查询薪资不在20000,18000,17000范围的数据
    select * from emp where salary not in (20000,18000,17000);
    
    # 7、查询岗位描述为空的员工姓名和岗位名 针对null不用符号 用is
    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';
    按照什么分组只能拿到分组 其他字段不能直接获取 需要借助一些方法(聚合函数)
    
    """
    聚合函数  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 '部门',msx(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;
    
    # 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 empp group by post;
    # concat不分组的时候用
    select concat('NAME:',name),concat('SAL:',salary) from emp;
    
    # 补充 as语法不单单可以给字段起别名 还可以给表临时起别名
    select emp.id,emp.name from emmp;
    select imp.id,emp.name from emp as t1;  报错
    select t1.id,t1.name from emp as t1;
    
    # 查询每个人的年薪
    select name,salary*12 from emp;
    
    
    

    分组注意事项

    # 关键字where和group by 同时出现的时候group by必须在where的后面
    where 先对整体数据进行过滤之后在分组操作
    where筛选条件不能使用聚合函数
    select id,name,age from emp where max(salary) > 3000;
    
    select max(salary) from emp;  # 不分组 默认整体就是一组
    
    # 统计各部门年龄在30岁以上的员工平局薪资
        1 先求所有年龄大于30岁的员工
            select * from emp where age>30;
        2 再对结果进行分组
            select * from emp where age>30 group by post;
        
        select post,avg(salary) from emp where age>30 group by post;
    

    having分组之后的筛选条件

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

    distinct 去重

    """
    一定要注意 必须是完全一样的数据才可以去重!!!
    有主键存在的情况下是不可能去重的
    """
    select distinct id,age from emp;
    select distinct age from emp;
    

    ofder 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,salry 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;
    第一个参数起始位置
    第二个参数是展示条数
    

    多表操作

    前期表准备

    #建表
    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 ='人力资源');
    
上一篇:47.自定义异常及经验小结


下一篇:E - 简单排序