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

day37基本查询语句和方法
1.基本查询语句与方法

""""""
"""
提前创表准备:
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), #以下是教学部
('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
"""

""""""
"""基本查询语句与方法"""
"""
1.from    从...中查询
    SQl语句: select * from emp;
    select id from emp where id>3 and id<6;

    例:
    mysql> select id from emp where id>3 and id<6;
    +----+
    | id |
    +----+
    |  4 |
    |  5 |
    +----+
    2 rows in set (0.00 sec)

***执行顺序
    from  从表中获得数据
    where   根据约束条件匹配
    select  返回匹配结果

***G
    select * from empG;    
    当表字段特别多的时候  结果排版可能会出现混乱现象,"G"来规范查询结果
"""

""""""
"""基本查询语句与方法"""
"""
2.where   当什什么时候
# 1.查询id大于等于3小于等于6的数据
SQL语句:select * from emp where id >= 3 and id <= 6;
# 2.查询薪资是20000或者18000或者17000的数据
SQL语句: 
    select * from  emp where salary = 20000 or salary = 18000 or salary = 17000;
    select * from  emp where salary in (20000,18000,17000)G;
# 3.查询员工姓名中包含o字母的员工姓名和薪资
SQL语句:
    分析包o字母 like '%o%'
    模糊匹配 like %:匹配所有任意字符
                  _:匹配一个任意字符
    select name,salary from emp where name like '%o%';
    例:
    mysql> select name,salary from emp where name like '%o%';
    +-------+------------+
    | name  | salary     |
    +-------+------------+
    | jason |    7300.33 |
    | egon  | 1000000.31 |
    | owen  |    2100.00 |
    +-------+------------+
    3 rows in set (0.00 sec)
# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
SQL语句:
    select name,salary from emp where char_length(name)=4;

    length:   是计算字段的长度一个汉字是算三个字符,一个数字或字母算一个字符;

    char_length:不管汉字还是数字或者是字母都算是一个字符;

    例:
    mysql> select name,salary from emp where char_length(name)=4;
    +------+------------+
    | name | salary     |
    +------+------------+
    | egon | 1000000.31 |
    | tank |    3500.00 |
    | owen |    2100.00 |
    | nick |   30000.00 |
    | sean |   10000.00 |
    +------+------------+
    5 rows in set (0.00 sec)
# 5.查询id小于3或者大于6的数据
SQL语句:
    select * from emp where id not between 3 and 6;
# 6.查询薪资不在20000，18000，17000范围的数据
SQL语句:
    select * from emp where salary not in(20000,18000,17000);
# 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
SQL语句:
    select name,post from emp where post_comment=NULL; # 空
    select name,post from emp where post_comment is NULL;
    select name,post from emp where post_comment is not NULL;
    Empty set (0.00 sec) # 空

    ps:"= 这里必须用 is "
       mysql 对字母大小不敏感
"""

""""""
"""基本查询语句与方法"""
"""
3.group by    分组
# 数据分组应用场景 :每个部门的平均薪资,男女比例等

#1.按部门分组:
# 分组后取出每个组的第一条数据
select * from emp group by post;
# 验证:
select id,name,sex from emp group  by post;

例:
mysql> select post from emp group by post;
+-----------------------------+
| post                        |
+-----------------------------+
| operation                   |
| sale                        |
| teacher                     |
| 张江第一帅形象代言          |
+-----------------------------+
4 rows in set (0.00 sec)

mysql> select id,name,sex from emp group  by post;
+----+--------+--------+
| id | name   | sex    |
+----+--------+--------+
| 14 | 张野   | male   |
|  9 | 歪歪   | female |
|  2 | egon   | male   |
|  1 | jason  | male   |
+----+--------+--------+
4 rows in set (0.00 sec)
# 
#2.
'''
sql_mode sql模式   only_full_group_by 只有完整组
设置sql_mode为only_full_group_by,意味着以后但凡分组,只能取到分组的依据
不应该再去取组里的单个元素的值, 那样的话,分组就没有意义了,因为不分组就是对单个元素信息的随意获取
'''
# 设置SQL模式
set global
sql_mode="strict_trans_tables,only_full_group_by";
ps:设置成功后,退出重新登陆
# 报错
select * from emp group by post;
select id,name,sex from emp group by post;
# 正确:获取部门信息 
select post from emp group by post;
ps: 只要分组了,就不能再"直接"找到单个字信息了,只能获取组名

# 3.获取每个部门的最高工资
# 以组为单位统计组内数据>>>>>>聚合查询(聚到一起合成为一个结果)
# 每个部门最高工资
select post,max(salary)from emp group by post;

mysql> select post,max(salary) from emp group by post;
+-----------------------------+-------------+
| post                        | max(salary) |
+-----------------------------+-------------+
| operation                   |    20000.00 |
| sale                        |     4000.33 |
| teacher                     |  1000000.31 |
| 张江第一帅形象代言          |     7300.33 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)

# 每个部门的最低工资
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(salary) from emp group by post;
'''
ps:
    在统计分组内个数的时候 填写任意非空字段都可以完成计数,推介使用能够标识唯一标识数据的字段,例:id

    聚合函数会自动将每个分组内的单个数据做想要的计算,无需你考虑
'''
# 4.查询分组之后的部门名称和每个部门下所有的学生姓名 
知识点补充:
    group_concat "连在一起的名字,组合"(分组之后用) 不仅可以用来显示除分组外字段还有拼接字符串的作用
    ps:group_concat()能够拿到分组后每一个数据指定字段(可以是多个)对应的值
SQL语句:    
    select post,group_concat(name) from emp group by post;
    例:
    mysql> select post,group_concat(name) from emp group by post;
    +-----------------------------+------------------------------------------------+
    | post                        | group_concat(name)                             |
    +-----------------------------+------------------------------------------------+
    | operation                   | 程咬铁,程咬铜,程咬银,程咬金,张野               |
    | sale                        | 格格,星星,丁丁,丫丫,歪歪                       |
    | teacher                     | sean,nick,jerry,owen,tank,kevin,egon           |
    | 张江第一帅形象代言          | jason                                          |
    +-----------------------------+------------------------------------------------+
    4 rows in set (0.00 sec)
    
    select post,group_concat(name,'_SB') from emp group by post;
    mysql> select post,group_concat(name,'_SB') from emp group by post;
    +-----------------------------+---------------------------------------------------------------+
    | post                        | group_concat(name,'_SB')                                      |
    +-----------------------------+---------------------------------------------------------------+
    | operation                   | 程咬铁_SB,程咬铜_SB,程咬银_SB,程咬金_SB,张野_SB               |
    | sale                        | 格格_SB,星星_SB,丁丁_SB,丫丫_SB,歪歪_SB                       |
    | teacher                     | sean_SB,nick_SB,jerry_SB,owen_SB,tank_SB,kevin_SB,egon_SB     |
    | 张江第一帅形象代言          | jason_SB                                                      |
    +-----------------------------+---------------------------------------------------------------+
    4 rows in set (0.00 sec)


    select post,group_concat(salary) from emp group by post;
    
    mysql> select post,group_concat(salary) from emp group by post;
    +-----------------------------+--------------------------------------------------------------+
    | post                        | group_concat(salary)                                         |
    +-----------------------------+--------------------------------------------------------------+
    | operation                   | 17000.00,18000.00,19000.00,20000.00,10000.13                 |
    | sale                        | 4000.33,3000.29,1000.37,2000.35,3000.13                      |
    | teacher                     | 10000.00,30000.00,9000.00,2100.00,3500.00,8300.00,1000000.31 |
    | 张江第一帅形象代言          | 7300.33                                                      |
    +-----------------------------+--------------------------------------------------------------+
    4 rows in set (0.00 sec)

# 5.补充concat(不分组时使用)拼接字符串达到更好的显示效果 as 语法使用
    SQL语句:select name as 姓名 ,salary as 薪资 from emp;
    select name as t1, salary as t2 from emp; 
例:
mysql> select name as t1, salary as t2 from emp;
+-----------+------------+
| t1        | t2         |
+-----------+------------+
| jason     |    7300.33 |
| egon      | 1000000.31 |
| kevin     |    8300.00 |
| tank      |    3500.00 |
| owen      |    2100.00 |
| jerry     |    9000.00 |
| nick      |   30000.00 |
| sean      |   10000.00 |
| 歪歪      |    3000.13 |
| 丫丫      |    2000.35 |
| 丁丁      |    1000.37 |
| 星星      |    3000.29 |
| 格格      |    4000.33 |
| 张野      |   10000.13 |
| 程咬金    |   20000.00 |
| 程咬银    |   19000.00 |
| 程咬铜    |   18000.00 |
| 程咬铁    |   17000.00 |
+-----------+------------+
18 rows in set (0.00 sec)

    SQL语句:select concat('NAME:',name)as 姓名,concat('SAL:',salary)as 薪资 from emp;
    select concat('NAME:',name)as t3,concat('SAL:',salary)as t4 from emp;
例:    
mysql> select concat('NAME:',name) as t3,concat('SAL:',salary) as t4 from emp;
+----------------+----------------+
| t3             | t4             |
+----------------+----------------+
| NAME:jason     | SAL:7300.33    |
| NAME:egon      | SAL:1000000.31 |
| NAME:kevin     | SAL:8300.00    |
| NAME:tank      | SAL:3500.00    |
| NAME:owen      | SAL:2100.00    |
| NAME:jerry     | SAL:9000.00    |
| NAME:nick      | SAL:30000.00   |
| NAME:sean      | SAL:10000.00   |
| NAME:歪歪      | SAL:3000.13    |
| NAME:丫丫      | SAL:2000.35    |
| NAME:丁丁      | SAL:1000.37    |
| NAME:星星      | SAL:3000.29    |
| NAME:格格      | SAL:4000.33    |
| NAME:张野      | SAL:10000.13   |
| NAME:程咬金    | SAL:20000.00   |
| NAME:程咬银    | SAL:19000.00   |
| NAME:程咬铜    | SAL:18000.00   |
| NAME:程咬铁    | SAL:17000.00   |
+----------------+----------------+
18 rows in set (0.00 sec)

补充as语法 既可以给字段起别名 也可以给表起别名
SQL语句:
    select emp.id,emp.name from emp as t1;
    # 报错,因为表名已经被你改成了t1
    # 正确
    select t1.id,t1.name from emp as t1;
    
    例:
    mysql> select t1.id,t1.name from emp as t1;
    +----+-----------+
    | id | name      |
    +----+-----------+
    |  1 | jason     |
    |  2 | egon      |
    |  3 | kevin     |
    |  4 | tank      |
    |  5 | owen      |
    |  6 | jerry     |
    |  7 | nick      |
    |  8 | sean      |
    |  9 | 歪歪      |
    | 10 | 丫丫      |
    | 11 | 丁丁      |
    | 12 | 星星      |
    | 13 | 格格      |
    | 14 | 张野      |
    | 15 | 程咬金    |
    | 16 | 程咬银    |
    | 17 | 程咬铜    |
    | 18 | 程咬铁    |
    +----+-----------+
    18 rows in set (0.00 sec)
    
***区别:
    concat 就是用来帮你拼接数据
    concat 不分组情况下使用
    group_concat 分组之后使用
"""
"""
# 6.查询四则运算
SQL语句:
    # 每个员工的月薪
    select name,salary from emp;
    # 年薪
    select name,salary*12 as annual_salary from emp;
    #简化 as 可省略
    select name,salary*12 annual_salary from emp;
    例:
mysql> select name,salary*12 as annual_salary from emp;
+-----------+---------------+
| name      | annual_salary |
+-----------+---------------+
| jason     |      87603.96 |
| egon      |   12000003.72 |
| kevin     |      99600.00 |
| tank      |      42000.00 |
| owen      |      25200.00 |
| jerry     |     108000.00 |
| nick      |     360000.00 |
| sean      |     120000.00 |
| 歪歪      |      36001.56 |
| 丫丫      |      24004.20 |
| 丁丁      |      12004.44 |
| 星星      |      36003.48 |
| 格格      |      48003.96 |
| 张野      |     120001.56 |
| 程咬金    |     240000.00 |
| 程咬银    |     228000.00 |
| 程咬铜    |     216000.00 |
| 程咬铁    |     204000.00 |
+-----------+---------------+
18 rows in set (0.00 sec)
"""
"""
练习题 
# 刚开始查询表，
一定要按照最基本的步骤，
1.先确定是哪张表，
2.再确定查这张表有没有限制条件，
3.再确定是否需要分类，
4.最后再确定需要什么字段对应的信息

1. 查询岗位名以及岗位包含的所有员工名字
    select post,group_concat(name) from emp group by post
    
    mysql> select post,group_concat(name) from emp group by post;
    +-----------------------------+------------------------------------------------+
    | post                        | group_concat(name)                             |
    +-----------------------------+------------------------------------------------+
    | operation                   | 程咬铁,程咬铜,程咬银,程咬金,张野               |
    | sale                        | 格格,星星,丁丁,丫丫,歪歪                       |
    | teacher                     | sean,nick,jerry,owen,tank,kevin,egon           |
    | 张江第一帅形象代言          | jason                                          |
    +-----------------------------+------------------------------------------------+
    4 rows in set (0.00 sec)

    
2. 查询岗位名以及各岗位内包含的员工个数
    select post,count(id) from emp group by post;
3. 查询公司内男员工和女员工的个数
    select sex,count(id) from emp group by sex;
    
mysql> select sex,count(id) from emp group by sex;
+--------+-----------+
| sex    | count(id) |
+--------+-----------+
| male   |        10 |
| female |         8 |
+--------+-----------+
2 rows in set (0.00 sec)
4. 查询岗位名以及各岗位的平均薪资
    select post,avg(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资
    select post,max(salary) from emp group by post;
6. 查询岗位名以及各岗位的最低薪资
    select post,min(salary) from emp group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
    select sex,avg(salary) from emp group by sex;

"""
"""
ps:
    # 关键字where group by同时出现的情况下，group by必须在where之后
    # where先对整张表进行一次筛选，如何group by再对筛选过后的表进行分组
    # 如何验证where是在group by之前执行而不是之后 利用聚合函数 因为聚合函数只能在分组之后才能使用
    select id,name,age from emp where max(salary) > 3000;  # 报错！

    select max(salary) from emp;  
    # 正常运行，不分组意味着每一个人都是一组，等运行到max(salary)的时候已经经过where，group by操作了，只不过我们都没有写这些条件
"""
"""
语法顺序:
    select 
    from
    where
    group by
    
执行顺序:
    from
    where
    group by
    select
"""
"""
8、统计各部门年龄在30岁以上的员工平均工资
    select post,avg(salary) from emp where age > 30 group by post;

#拆分
    # 对where过滤出来的虚拟表进行一个分组
        select *from emp where age > 30;
    # 还不明白可以分步执行查看结构
        select * from emp where age > 30 group by post
    # 基于上面的虚拟表进行分组

"""

""""""
"""基本查询语句与方法"""

"""
4.having 
    select 查询字段1,查询字段2,...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) > 10000;

mysql> select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
+---------+---------------+
| post    | avg(salary)   |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
# 如果不信你可以将having取掉，查看结果，对比即可验证having用法！

#强调：having必须在group by后面使用
select * from emp having avg(salary) > 10000;  # 报错
     ...
"""

""""""
"""基本查询语句与方法"""

"""
5.distinct 去重
作用: 对有重复展示的数据进行去重操作
    重必须数据是一模一样的才能去重
只要有一个不一样 都不能算是的重复的数据
SQL语句:
    select distinct id,age form 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; 
    
    例:
    #先按照age降序排，在年轻相同的情况下再按照薪资升序排
    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);

执行顺序:
    from
    where
    group by 
    having
    order by
    select
    ...
"""

""""""
"""基本查询语句与方法"""

"""
7.limit 翻页
# 限制展示条数
select * from emp limit 3;
# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;

mysql> select * from emp order by salary desc limit 1;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | egon | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+

# 分页显示
# 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 0,5;  
select * from emp limit 5,5;
select * from emp limit 10,5;
...
"""
"""
"""
"""
ps:
    1.当limit只有一个参数的时候  
    表示的是只展示几条
    2.当limit有两个参数的时候   
    第一个参数表示的起始位置 
    第二个参数表示从起始位置开始往后展示的条数
"""

""""""
"""
8.正则
select * from emp where name regexp '^j.*(nly)$';

例:
mysql> select * from emp where name regexp '^j.*(nly)$';
Empty set (0.00 sec)
"""

2.多表查询的方法 连表查询 子查询

""""""
"""
'''创建表'''
#建表
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)
;

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

"""
"""
表查询:
1.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 = '技术';
"""

""""""
"""连表操作"""
"""
    1.inner join 内连接
    只取两张表有对应关系的记录
    select * from emp inner join dep on emp.dep_id = dep.id;
    select * from emp inner join dep on emp.dep_id = dep.id where dep.name = '技术';
    
"""
"""
    2.left join 左连接
    在内连接的基础上保留左表没有对应关系的记录
    select * from emp left join dep on emp.dep_id = dep.id;
    select * from emp left join dep onemp.dep_id = dep.id where dep.name = '技术';

"""
"""
    3.right join 右连接
    在内连接的基础上保留右表没有对应关系的记录
    select * from emp right join dep on emp.dep_id = dep.id;
    select * from emp right join dep onemp.dep_id = dep.id where dep.name = '技术';
"""
"""
    4.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;
"""

""""""
"""子查询"""
"""
子查询
    将一个表的查询结果作为另一个查询的sql语句的查询条件
"""
"""
例:
# 1.查询部门是技术或者人力资源的员工信息
  select * from dep where where name = "技术" or name = "人力资源";
   
思路分析:
    1.先获取技术部和人力资源部的id号，
    2.再去员工表里面根据前面的id筛选出符合要求的员工信息
select * from emp where dep_id in (select id from dep where name = "技术" or name = "人力资源");
"""
"""
例:
# 2.每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询
select t1.id,t1.name,t1.hire_date,t1.post,t2.* from emp as t1
inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date
;

"""

# 可以给表起别名
# 可以给查询出来的虚拟表起别名
# 可以给字段起别名

readme

1.基本查询语句即方法
    from    从...中查询
    where   当什什么时候
    group by    分组
    having  与分组配合时使用 与where作用相同
    distinct  去重
    order by    排序
    limit   翻页
    ...
2.连表操作
    inner join 内连接
    left join 左连接
    right join 右连接
    union 全连接
3.子查询
    将一个表的查询结果作为另一个查询的sql语句的查询条件



    s