37.基本查询语句与方法 多表查询

1.单表查询:
    1.前期表准备:
        create table 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_commment 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), #以下是教学部
('egon','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tank','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jerry','female',18,'20110211','teacher',9000,401,1),
('nick','male',18,'19000301','teacher',30000,401,1),
('sean','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)
;
    ps: 如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

   2.语法执行顺序:
        0.初识查询语句:
            select id,name from emp where id>3 and id<=6;
        先后顺序:
            from 从表中获得数据
            where 根据约束条件匹配
            select 返回匹配结果
        1.from
         select * from emp\G;
            当表字段特别多的时候  结果排版可能会出现混乱现象,"\G"来规范查询结果
        2.where 当什什么时候

            # 1.查询id大于等于3小于等于6的数据
            select id,name from emp where id >= 3 and id <= 6;
            select *  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.group by 分组
            select * from emp group by post ;# 分组后取出的是每个组的第一条数据
            select id,name,sex from emp group by post;  # 验证
            """
            设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
            不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取
            """

            set global sql_mode='strict_trans_tables,only_full_group_buy';
            # 重新链接客户端
            select * from emp group by post;  # 报错
            select id,name,sex from emp group by post;  # 报错
            select post from emp group by post;  # 获取部门信息
            # 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名

            # 每个部门的最高工资
            select post,max(salary) from emp group by post;
            # 每个部门的最低工资
            select post,min(salary) from emp group by post;
            # 每个部门的平均工资
            select post,avg(salary) from emp group by post;
            # 每个部门的工资总和
            select post,sum(salary) from emp group by post;
            # 每个部门的人数
            select post,count(id) from emp group by post;
            # 3.查询分组之后的部门名称和每个部门下所有的学生姓名
            group_concat  （分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用
            select post,group_concat(name) from emp group by post;
            # 4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用
            select name as 姓名,salary as 薪资 from emp;
            select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as 薪资 from emp;

            # 补充as语法 即可以给字段起别名也可以给表起
            select emp.id,emp.name from emp as t1; # 报错  因为表名已经被你改成了t1
            select t1.id,t1.name from emp as t1;

            关键字where group by同时出现的情况下，group by必须在where之后
                # where先对整张表进行一次筛选，如何group by再对筛选过后的表进行分组
                # 如何验证where是在group by之前执行而不是之后 利用聚合函数 因为聚合函数只能在分组之后才能使用
            # 语法顺序
            select
            from
            where
            group by
            # 再识执行顺序
            from
            where
            group by
            select


        4.having 与分组配合时使用 与where作用相同
            select 查询字段1,..查询字段n from 表名 where 过滤条件 group by 分组依据 having 过滤条件;
            执行顺序
            from
            where
            group by
            having
            select
            ps:having的语法格式与where一致，只不过having是在分组之后进行的过滤，即where虽然不能用聚合函数，但是having可以！
            1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
            select post,avg(salary) from emp where age>30 group by post having avg(salary) >1000;
        5.distinct 去重
            作用: 对有重复展示的数据进行去重操作
            SQL语句:
             select distinct id ,age from emp;
            执行顺序:
                from
                where
                group by
                having
                select
                distinct
        6.order by 排序
            asc 升序:
            desc 降序:
            默认升序排
                select *from emp order by salary asc;
            降排序
                select * from emp order by salary desc;
                select post,avg(salary) from emp
                where age > 10
                group by post
                having avg(salary) > 1000
                order by avg(salary)
                ;
                执行顺序:
                from
                where
                group by
                having
                order by
                select
                distinct

        7.limit 翻页
        # 限制展示条数
        select *from emp limit 3;
        select * from emp order by salary desc limit 1;
        执行顺序:
                from
                where
                group by
                having
                order by
                limit
                select
                distinct
          select * from emp limit 10,5;

          ps:
                 1.当limit只有一个参数的时候
                 表示的是只展示几条
                 2.当limit有两个参数的时候
                 第一个参数表示的起始位置
                 第二个参数表示从起始位置开始往后展示的条数
        8.正则
            select * from emp where name regexp '^j.*(n|y)$';

2.多表查询
    1.开始创表前,即推导
    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',38,201),
        ('nick','female',28,202),
        ('owen','male',18,200),
        ('jerry','female',18,204)
        ;

        # 当初为什么我们要分表，就是为了方便管理，在硬盘上确实是多张表，但是到了内存中我们应该把他们再拼成一张表进行查询才合理

表查询


            select * from emp,dep;  # 左表一条记录与右表所有记录都对应一遍>>>笛卡尔积

            # 将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据

            # 查询员工及所在部门的信息
            select * from emp,dep where emp.dep_id = dep.id;
            # 查询部门为技术部的员工及部门信息
            select * from emp,dep where emp.dep_id = dep.id and dep.name = '技术';


    2.连表操作查询
        inner join 内连接
        left join 左连接
        right join 右连接
        union 全连接

        # 将两张表关联到一起的操作，有专门对应的方法
        # 1、内连接：只取两张表有对应关系的记录
            select * from emp inner join dep on emp.dep_id = dep.id;
         2、左连接: 在内连接的基础上保留左表没有对应关系的记录
            select * from emp left join dep on emp.dep_id = dep.id;

          3.右连接:在内连接的基础上保留右表没有对应关系的记录
            select  * from emp right join dep on emp.dep_id = dep.id;
          4、全连接：在内连接的基础上保留左、右面表没有对应关系的的记录
          select * from emp right join dep on emp.dep_id = dep.id;
    3.子查询
        将一个表的查询结果作为另一个查询的sql语句的查询条件
        1.查询部门是技术或者人力资源的员工信息
            """
            先获取技术部和人力资源部的id号，再去员工表里面根据前面的id筛选出符合要求的员工信息
            """
            select * from emp where dep_id in (select id from dep where name = "技术" or name = "人力资源");
            """
            记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询
            """
    别名
        可以给表起别名
        可以给查询出来的虚拟表起别名
        可以给字段起别名

        # exist(了解)
        EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，
        而是返回一个真假值，True或False。
        当返回True时，外层查询语句将进行查询
        当返回值为False时，外层查询语句不进行查询。
        select * from emp
            where exists
            (select id from dep where id > 203);