• day47——查询表关键字:筛选、分组、排序;多表操作: 拼表、子查询


    一、如何查询表

    • 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 empG;
    
    # 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象 你可以将字符编码统一设置成GBK
    

    几个重要关键字的书写属性与执行顺序

    • 书写顺序

      select id,name from emp where id > 3;
      
    • 执行顺序

      from——》where——》select

      好比与去图书馆找书的步骤:先去该书所属类型的分区里面找(from 哪张表)、该分区哪个书架上(where 限制条件,限制在表里的哪个范围找)、以及找到书后要查什么内容(select 查name还是查id)。

    • 书写查询语句方法

      因为书写顺序跟执行顺序不一致,书写的sql语句的时候建议在不知道具体应该查什么内容的时候,select后面先用*占位,之后去补全后面的sql语句,最后将 *号换成具体要查询的字段。

    where筛选条件

    # where作用:对 整体数据的一个筛选操作
    
    # 1.查询id大于等于3小于等于6的数据
    select * from emp where id>=3 and id<=6;
    select * from emp where id between 3 and 6;  # 两者等价
    
    # 2.查询薪资是200000或者18000或者17000的员工姓名和薪资
    select name,salary from emp where salary=20000 or salary=18000 or salary=17000;
    select name, salary form emp where salary in (20000,18000,17000);  # 两者等价
    
    # 3.查询员工名字中包含字母o的员工的姓名和薪资
    """
    模糊查询
    	like
    		%  匹配任意多个字符
    		_  匹配任意多个字符
    """
    select name,salary from emp where name like '%o%';
    
    # 4.查询员工姓名是由四个字符组成的姓名和薪资
    select name,salary from emp where name like '____';
    select name,salary from emp where char_length(name);  # 两者等价
    
    # 5.查询id小于3或者id大于6的数据,就是不再3和6之间,可以取反。
    select * from emp where id not in between 3 and 6;
    
    # 6.查询薪资不等于20000、17000、18000的数据,取反。
    select * from emp where salary not in (20000,18000,17000);
    
    # 7.查询岗位描述位空的员工姓名和岗位,针对null不用等号用is
    select name,post from emp where post_comment = NULL  # 查询结果为空
    """Empty set (0.00 sec)"""
    select name,psot from emp where post_comment is NULL  # 查询到相应的数据
    

    group by 分组

    """
    分组的实际应用场景:
    	男女比例:分为男生组和女生组
    	部门平均薪资:按部门分组:财务部、人事部...
    	国家之间的数据统计:中国、美国..."""
    # 1.上述表按部门分组
    select * from emp group by post;
    select name from emp group by post;
    """
    +--------+
    | name   |
    +--------+
    | 僧龙   |
    | 哈哈   |
    | tom    |
    | jason  |
    +--------+
    分组之后拿到的应该是组,而不应该是上面的单个数据。
    
    上述命令在没有设置严格模式的时候是可以正常执行的,返回的是分组之后每个组的第一个数据,但是这不符合分组的规范:
    	分组之后不应该再去考虑单个的数据,儿应该以组为操作单位(分组之后没法直接获取组内的单个数据)
    	设置严格模式后,上述的命令就会直接报错"""
    
    # 设置严格模式
    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;
    

    分组注意事项

    • 关键字where和group by同时出现的时候group by必须跟在where的后面、

    • where先对整体数据进行过滤之后在分组操作

    • where筛选条件不能使用聚合函数

      select id,name,age from emp where max(salary) > 3000;  # 报错
      
      select max(salary) from emp;  # 不分组默认整体是一组
      

    where和group by组合使用

    # 统计各部门年龄在30岁以上的员工的平均薪资
    
    # 1.先求所有年龄大于30岁以上的员工平均工资
    	select * from emp where age > 30;
    # 2.在对结果按部门进行分组
    	select post from emp where age > 30 group by post;
    # 3.获取部门的平均薪资
    	select post,avg(salary) from emp where age > 30 group by post;
        
    

    having分组之后的筛选条件

    having的语法跟where是一致的,不同在于where是在分组之前进行筛选,而having是在分组之后进行筛选,过滤。

    # 统计各部门年龄在30岁以上员工的 平均薪资并且保留平均薪资大于10000的部门
    select post,avg(salary) from emp
    	where age>30
        group by post
        having avg(salary)>10000
        ;
    

    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
    """
    
    # 先按照age降序排,如果碰到age相同,则再按照salary升序排
    select * from emp order by age desc,salary asc;
    
    # 统计各部门年龄在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;
    """针对数据过多的情况,我们通常做分页处理,G"""
    # 一个参数,表示从零开始,展示后面的多少条数据
    select * from emp limit 3;  # 只展示三条数据
    
    # 两个参数,表示从第一个参数开始,展示后面的第二个参数的值的条数。
    select * from emp limit 0,5;  # 从零开始,往后展示5条数据
    select * from emp limit 5,5;  # 从5开始,往后展示5条数据
    

    正则

    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 = '人力资源');
    

    多表操作总结

    表的查询结果可以作为其他表的查询条件
    也可以通过起别名的方式把它作为一个张虚拟表根其他表关联
    
    """
    多表查询就两种方式
    	先拼接表再查询
    	子查询 一步一步来
    """
    
  • 相关阅读:
    sql优化
    es和solr
    RabbitMQ 整理
    redis分布式缓存
    redis集群
    drf-jwt第三方插件,DRF的三大认证的具体使用,多方式登陆的实现
    自定义路由组件,Django的admin后台管理,DRF的三大认证,jwt认证
    DRF视图家族
    导包补充,深度查询(深度序列化),十大接口
    三流,内部类,基表,表关系,断开表关联,外键字段属性
  • 原文地址:https://www.cnblogs.com/zhangtieshan/p/12906222.html
Copyright © 2020-2023  润新知