Master the basic operations of MySQL query statements-Mysql Tutorial-php.cn

mysql video tutorialThe column introduces the operation of query statements

Master the basic operations of MySQL query statements

##Related free learning recommendations: mysql video tutorial

1. Basic operations of query statements

1.查询语句的基本操作
        - select
        - from
        - where:约束条件
        - group by:分组
        - having：过滤
        - distinct：去重
        - order by：排序
        - limit：限制查询记录的数量
        - 聚合函数: count（计数）
                   max（最大值）
                   min（最小值）
                   avg（平均值）
                   sum(求和)

Copy after login

2. Single table query

1. Preliminary table and data preparation

# 创建一张部门表
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
('tank','male',17,'20170301','张江第一帅形象代言部门',7300.33,401,1), # 以下是教学部
('egon','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('jason','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jerry','female',18,'20110211','teacher',9000,401,1),
('大饼','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


- select * from emp;  # 若数据比较多，比较凌乱，可以在表后面+ \G
- select * from emp\G

Copy after login

- select * from emp;  # 若数据比较多，比较凌乱，可以在表后面+ \G    
- select * from emp\G

Copy after login

Key points:

 写SQL语句必须遵循两点:
 - 书写顺序:
# 获取id为 4、5的两条记录
select * from emp where id > 3 and id < 6;

    - select
    - from
    - where
    
  - 执行顺序:

   比如: 图书管理员，得先找到是哪一个图书馆(哪张表),
   再找这本书在图书馆的哪个位置(哪一条记录), 最后查找这个本书中某一页(哪些字段值);

   select * from emp where id > 3 and id < 6;
    - from ---> 找到图书馆
    - where ---> 找到书的位置
    - select ---> 找到书本中的某一页
    
 注意: 必须记住SQL语句的 书写顺序 与 执行顺序(*******);

Copy after login

1.where(constraints)

# PS: 根据执行顺序来书写 SQL语句，一步一步来写;

# 1.查询id大于等于3小于等于6的数据(and:与)

select * from emp where id >= 3 and id <= 6;

mysql> SELECT * FROM EMP WHERE ID > 3 AND ID <= 6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name  | sex    | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|  4 | jason | male   |  73 | 2014-07-01 | teacher | NULL         | 3500.00 |    401 |         1 |
|  5 | owen  | male   |  28 | 2012-11-01 | teacher | NULL         | 2100.00 |    401 |         1 |
|  6 | jerry | female |  18 | 2011-02-11 | teacher | NULL         | 9000.00 |    401 |         1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
3 rows in set (0.00 sec)


#可以使用between()and()  :两者之间


mysql> select * from emp where id between 3 and 6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name  | sex    | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|  3 | kevin | male   |  81 | 2013-03-05 | teacher | NULL         | 8300.00 |    401 |         1 |
|  4 | jason | male   |  73 | 2014-07-01 | teacher | NULL         | 3500.00 |    401 |         1 |
|  5 | owen  | male   |  28 | 2012-11-01 | teacher | NULL         | 2100.00 |    401 |         1 |
|  6 | jerry | female |  18 | 2011-02-11 | teacher | NULL         | 9000.00 |    401 |         1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
4 rows in set (0.00 sec)

# 2.查询薪资是20000或者18000或者17000的数据
# or:  或者
select * from emp where salary=20000 or salary=18000 or salary=17000;
# in: 在什么里
select * from emp where salary in (20000, 18000, 17000);

mysql> select * from  emp where salary = 20000 or salary  = 18000 or salary = 17000;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         | 20000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation | NULL         | 18000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation | NULL         | 17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
3 rows in set (0.00 sec)

# 也可以使用in(3,4,5,6)
 select * from emp where id in(3,4,5,6);
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name  | sex    | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|  3 | kevin | male   |  81 | 2013-03-05 | teacher | NULL         | 8300.00 |    401 |         1 |
|  4 | jason | male   |  73 | 2014-07-01 | teacher | NULL         | 3500.00 |    401 |         1 |
|  5 | owen  | male   |  28 | 2012-11-01 | teacher | NULL         | 2100.00 |    401 |         1 |
|  6 | jerry | female |  18 | 2011-02-11 | teacher | NULL         | 9000.00 |    401 |         1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
4 rows in set (0.00 sec)


# 3.查询员工姓名中包含o字母 的 员工姓名和薪资
# like: 模糊匹配
# %: 匹配0个或多个任意字符(若前后都有%(%o%),查所有带有索引字符的记录,若头有%而尾没有(%o),则查所有尾带有索引字符的记录,若尾有%而头没有(o%),则查所有头带有索引字符的记录,若前后都没有%(o),则只会查询只有索引字符的字符)
# _: 匹配一个任意字符
select name, salary from emp where name like "%o%";
+-------+------------+
| name  | salary     |
+-------+------------+
| egon  | 1000000.31 |
| jason |    3500.00 |
| owen  |    2100.00 |
+-------+------------+
3 rows in set (0.00 sec)


mysql> select name, salary from emp where name like "o";
+------+--------+
| name | salary |
+------+--------+
| o    |   NULL |
+------+--------+
1 row in set (0.00 sec)


# 4.查找名字个数为3个的员工 名字 与 薪资
select name, salary from emp where name like "___";
+-----------+----------+
| name      | salary   |
+-----------+----------+
| 程咬金    | 20000.00 |
| 程咬银    | 19000.00 |
| 程咬铜    | 18000.00 |
| 程咬铁    | 17000.00 |
+-----------+----------+
4 rows in set (0.00 sec)

# 或者 # char_length(name): 计算名字字符的长度
select name, salary from emp where char_length(name) = 4;
    
mysql> select name, salary from emp where char_length(name) = 3;
+-----------+----------+
| name      | salary   |
+-----------+----------+
| 程咬金    | 20000.00 |
| 程咬银    | 19000.00 |
| 程咬铜    | 18000.00 |
| 程咬铁    | 17000.00 |
+-----------+----------+
4 rows in set (0.00 sec)


# 5.查询id小于3或者大于6的数据
# not in: 不再什么什么中
select * from emp where id not in (3, 4, 5, 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.查询岗位描述为空的 员工名 与 岗位名 post_comment
# 用等于号无法判定空字段
select name, post from emp where post_comment = null;

select * from emp where post_comment = null;
Empty set (0.00 sec)

# 注意: 针对null的值 需要使用 is
select name, post from emp where post_comment is null;

select name, post_comment from emp where post_comment is null;
+-----------+--------------+
| name      | post_comment |
+-----------+--------------+
| tank      | NULL         |
| egon      | NULL         |
| kevin     | NULL         |
| jason     | NULL         |
| owen      | NULL         |
| jerry     | NULL         |
| 大饼      | NULL         |
| sean      | NULL         |
| 歪歪      | NULL         |
| 丫丫      | NULL         |
| 丁丁      | NULL         |
| 星星      | NULL         |
| 格格      | NULL         |
| 张野      | NULL         |
| 程咬金    | NULL         |
| 程咬银    | NULL         |
| 程咬铜    | NULL         |
| 程咬铁    | NULL         |
| o         | NULL         |
+-----------+--------------+
19 rows in set (0.00 sec)

Copy after login

2.group by(group)

  - 书写顺序:
                - select
                - from
                - where
                - group by

            - 执行顺序:
                - from
                - where
                - group by
                - select
# 什么叫分组               
# 比如: 一张员工表中有性别字段，可以根据性别分组，一组是男性，一组是女性，或者是根据部门分组，有教学部、销售部等...    

 # 1.根据部门分组
 # 非严格模式下可以获取 分组条件post 以外的字段数据
select post, salary from emp group by post;

# 设置严格模式:
show variables like "%mode%";
# 全局设置: 永久有效
set global sql_mode="strict_trans_tables,only_full_group_by";


"""
设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，并且会报错,因为不分组就是对单个元素信息的随意获取
"""
mysql> select post, salary from emp group by post;
ERROR 1046 (3D000): No database selected
    
# 可以同聚合函数，间接获取其他字段数据
 聚合函数:
   count: 计数
   max: 最大值
   min: 最小值
   avg: 平均值
   sum: 求和
   group_concat(name): 可以将分组后的 所有名字获取并进行拼接
   
# 指定以:拼接
select post, group_concat(name) from emp group by post;
select post, group_concat(name, ':') from emp group by post;
    
  
 2.获取每个 部门 的最高工资
 select post,max(salary) from emp group by post;
mysql>  select post,max(salary) from emp group by post;
+-----------------------------------+-------------+
| post                              | max(salary) |
+-----------------------------------+-------------+
| NULL                              |        NULL |
| operation                         |    20000.00 |
| sale                              |     4000.33 |
| teacher                           |  1000000.31 |
| 张江第一帅形象代言部门            |     7300.33 |
+-----------------------------------+-------------+
5 rows in set (0.00 sec)

补充: as 别名: 可以给字段 加一个 别名
select post as '部门', max(salary) as '薪资'
from emp group by post;

# 也可以简写,但是不推荐
select post '部门', max(salary) '薪资'
from emp group by post;

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


3.每个部门的最低工资
select post, min(salary) from emp group by post;

4.每个部门的平均工资
select post, avg(salary) from emp group by post;

5.每个部门的工资总和
select post, sum(salary) from emp group by post;

6.每个部门的员工个数
# count(): 括号中可以填任意非空值
select post, count(salary) from emp group by post;
select post, count(post_comment) from emp group by post;


查询岗位名以及各岗位内包含的员工个数
select post, count(id) from emp group by post;
查询公司内男员工和女员工的个数
select sex, count(*) from emp group by sex;

查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex, avg(salary) from emp group by sex;

.统计各部门年龄在30岁以上的员工平均工资:
# 步骤: 先找到表，再找年龄30岁以上，再根据部门分组，最后求平均薪资;
select post, avg(salary) from emp where age > 30 group by post;

Copy after login

Summary: Aggregation function must follow group by (execution order);

# 聚合函数: 若没有group by 分组，默认将查出来的数据当做一个分组, 也能使用;
select max(salary) from emp;

Copy after login

 # group_concat(name): 可以将分组后的 所有名字获取并进行拼接
 # 默认以, 拼接
select post, group_concat(name) from emp group by post;
 # 指定以:拼接
select post, group_concat(name, ':') from emp group by post;
# 
select post, group_concat('Name: ', name) from emp group by post;


select post, group_coucat(name) from emp group by post;

mysql> select post, group_concat(name) from emp group by post;
+-----------------------------------+------------------------------------------------+
| post                              | group_concat(name)                             |
+-----------------------------------+------------------------------------------------+
| NULL                              | o                                              |
| operation                         | 程咬铁,程咬铜,程咬银,程咬金,张野               |
| sale                              | 格格,星星,丁丁,丫丫,歪歪                       |
| teacher                           | sean,大饼,jerry,owen,jason,kevin,egon          |
| 张江第一帅形象代言部门            | tank                                           |
+-----------------------------------+------------------------------------------------+
5 rows in set (0.00 sec)

# 给每个部门的员工名字前 + NB_
select post, group_concat('NB_', name) from emp group by post;
# 拼接部门员工名字+薪资
select post, group_concat(name, ":", salary) from emp group by post;

Copy after login

Supplement:concat

# 4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法并用
select concat('Name: ', name) as '名字', concat('Sal: ', salary) as '薪资' from emp;


mysql> select concat('name: ', name) as '名字',concat('sal: ',salary) as '薪资' from emp;
+-----------------+-----------------+
| 名字            | 薪资            |
+-----------------+-----------------+
| name: tank      | sal: 7300.33    |
| name: egon      | sal: 1000000.31 |
| name: kevin     | sal: 8300.00    |
| name: jason     | sal: 3500.00    |
| name: owen      | sal: 2100.00    |
| name: jerry     | sal: 9000.00    |
| name: 大饼      | 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   |
| name: o         | NULL            |
+-----------------+-----------------+
19 rows in set (0.00 sec)

Copy after login

3.having(filter )

1.having与where语法一样，只不过having必需要在group by后使用;
2.where 不能使用聚合函数，但having可以;

            - 书写顺序:
                - select
                - from
                - where
                - group by
                - having

            - 执行顺序:
                - from
                - where
                - group by
                - having
                - select

 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 |
+---------+---------------+
1 row in set (0.00 sec)

Copy after login

4.distinct(remove duplicates)

# distinct: 去重

- 书写顺序:
         - select
         - distinct
         - from
         - where
         - group by
         - having

执行顺序:
		- from
		- where 
		- group by 
		- having
		- select 
		- distinct
        
# 注意: 查询的字段值必须是重复的才有效，只要有一个字段值是不重复的就没有效果。

# 若所查字段内有不重复的字段记录就不会去重
select distinct id, post from emp;

mysql> select distinct id, post from emp;
+----+-----------------------------------+
| id | post                              |
+----+-----------------------------------+
|  1 | 张江第一帅形象代言部门            |
|  2 | teacher                           |
|  3 | teacher                           |
|  4 | teacher                           |
|  5 | teacher                           |
|  6 | teacher                           |
|  7 | teacher                           |
|  8 | teacher                           |
|  9 | sale                              |
| 10 | sale                              |
| 11 | sale                              |
| 12 | sale                              |
| 13 | sale                              |
| 14 | operation                         |
| 15 | operation                         |
| 16 | operation                         |
| 17 | operation                         |
| 18 | operation                         |
| 19 | NULL                              |
+----+-----------------------------------+
19 rows in set (0.00 sec)




select distinct post from emp;

mysql> select distinct post from emp;
+-----------------------------------+
| post                              |
+-----------------------------------+
| 张江第一帅形象代言部门            |
| teacher                           |
| sale                              |
| operation                         |
| NULL                              |
+-----------------------------------+
5 rows in set (0.00 sec)

Copy after login

5.order by(sort)

- 书写顺序:
        - select
        - from
        - where
        - group by
        - having
        - order by

执行顺序:
		- from
		- where 
		- group by 
		- having 
		- select 
    - order by  # 通过select 查出来的数据再进行排序
    
# order by 默认升序
# asc升序
# desc降序

# 1、根据薪资进行升序
select name, salary from emp order by salary;  # 默认升序

mysql> select name, salary from emp order by salary;
+-----------+------------+
| name      | salary     |
+-----------+------------+
| o         |       NULL |
| 丁丁      |    1000.37 |
| 丫丫      |    2000.35 |
| owen      |    2100.00 |
| 歪歪      |    3000.13 |
| 星星      |    3000.29 |
| jason     |    3500.00 |
| 格格      |    4000.33 |
| tank      |    7300.33 |
| kevin     |    8300.00 |
| jerry     |    9000.00 |
| sean      |   10000.00 |
| 张野      |   10000.13 |
| 程咬铁    |   17000.00 |
| 程咬铜    |   18000.00 |
| 程咬银    |   19000.00 |
| 程咬金    |   20000.00 |
| 大饼      |   30000.00 |
| egon      | 1000000.31 |
+-----------+------------+
19 rows in set (0.00 sec)



2.select * from emp order by salary asc;  # 指定升序

3.select name, salary from emp order by salary desc;  # 指定降序

 
mysql> select name, salary from emp order by salary desc;
+-----------+------------+
| name      | salary     |
+-----------+------------+
| egon      | 1000000.31 |
| 大饼      |   30000.00 |
| 程咬金    |   20000.00 |
| 程咬银    |   19000.00 |
| 程咬铜    |   18000.00 |
| 程咬铁    |   17000.00 |
| 张野      |   10000.13 |
| sean      |   10000.00 |
| jerry     |    9000.00 |
| kevin     |    8300.00 |
| tank      |    7300.33 |
| 格格      |    4000.33 |
| jason     |    3500.00 |
| 星星      |    3000.29 |
| 歪歪      |    3000.13 |
| owen      |    2100.00 |
| 丫丫      |    2000.35 |
| 丁丁      |    1000.37 |
| o         |       NULL |
+-----------+------------+
19 rows in set (0.00 sec)

# 先按照age升序，再按照salary降序
select age, salary from emp order by age asc, salary desc;


# 统计 各部门(分组) 年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行升序序
select post, avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);

Copy after login

9.limit(limit the number of query records)

 书写顺序:
                - select
                - from
                - order by
                - limit

            执行顺序:
                - from
                - select
                - order by
                - limit

                
                
# 1、从第一条开始，获取4条记录;
select * from emp limit 4;

mysql> select * from emp limit 4;
+----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+
| id | name  | sex  | age | hire_date  | post                              | post_comment | salary     | office | depart_id |
+----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+
|  1 | tank  | male |  17 | 2017-03-01 | 张江第一帅形象代言部门            | NULL         |    7300.33 |    401 |         1 |
|  2 | egon  | male |  78 | 2015-03-02 | teacher                           | NULL         | 1000000.31 |    401 |         1 |
|  3 | kevin | male |  81 | 2013-03-05 | teacher                           | NULL         |    8300.00 |    401 |         1 |
|  4 | jason | male |  73 | 2014-07-01 | teacher                           | NULL         |    3500.00 |    401 |         1 |
+----+-------+------+-----+------------+-----------------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)



# 2、limit可以有两个参数, 参数1:是限制的开始位置, 参数2:是从开始位置展示的条数;
select * from emp limit 0, 4;
select * from emp limit 4, 4;




mysql> select * from emp limit 4, 4;
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name   | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  5 | owen   | male   |  28 | 2012-11-01 | teacher | NULL         |  2100.00 |    401 |         1 |
|  6 | jerry  | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  7 | 大饼   | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
|  8 | sean   | male   |  48 | 2010-11-11 | teacher | NULL         | 10000.00 |    401 |         1 |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)

# 3、查询工资最高的人的详细信息
select * from emp order by salary limit 1;


mysql> select * from emp order by salary limit 1;
+----+------+------+-----+------------+------+--------------+--------+--------+-----------+
| id | name | sex  | age | hire_date  | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+------+--------------+--------+--------+-----------+
| 19 | o    | male |  28 | 0000-00-00 | NULL | NULL         |   NULL |   NULL |      NULL |
+----+------+------+-----+------------+------+--------------+--------+--------+-----------+
1 row in set (0.00 sec)

Copy after login

10. Regularity

# 在编程中，凡是看到reg开头的，基本上都是跟正则有关
  # *: 代表 0 或 多个
select * from emp where name regexp '^程.*(金|银|铜|铁)$';

mysql> select * from emp where name regexp '^程.*(金|银|铜|铁)$';
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         | 20000.00 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation | NULL         | 19000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation | NULL         | 18000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation | NULL         | 17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
4 rows in set (0.00 sec)

Copy after login

3.Multiple table query

Multiple table query

-Related query

-Subquery

Creating tables and inserting data preparation

#建表
create table dep2(
id int,
name varchar(20) 
);

create table emp2(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into dep2 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp2(name,sex,age,dep_id) values
('tank','male',17,200),
('egon','female',48,201),
('kevin','male',38,201),
('jason','female',28,202),
('owen','male',18,200),
('sean','female',18,204);



mysql> select * from dep2;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
4 rows in set (0.00 sec)



mysql> select * from emp2;
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | tank  | male   |   17 |    200 |
|  2 | egon  | female |   48 |    201 |
|  3 | kevin | male   |   38 |    201 |
|  4 | jason | female |   28 |    202 |
|  5 | owen  | male   |   18 |    200 |
|  6 | sean  | female |   18 |    204 |
+----+-------+--------+------+--------+

# 指到了如何根据表关系对字段进行拆分，目的是为了更好的管理，表数据都存放在硬盘中，存不是目的，目的是为了取，所以我们将数据从硬盘读到内存中，接下来我们因应该将他们拼成一张表来查询更加合理;

# 注意: 将拆分的表，再拼接到一起进行查询, 可以通过一张表查另一张表的数据;

Copy after login

1. Association query

# 左表的一条记录与右表的一条记录都对应一遍称之为 --> "笛卡尔积"   PS: 百度科普
# 将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据

Copy after login

举例:通过之前准备的数据进行多表操作
    
1、查询 员工 以及所在 部门 的信息;
# 通过where 约束条件
select * from emp2, dep2 where emp2.dep_id = dep2.id;

2、查询 部门 为 技术部 的 员工 及 部门信息

select * from emp2, dep2 where emp2.dep_id = dep2.id and dep2.name = '技术';

Copy after login

联表查询:
   -内链接:只去两张表有对应关系的记录
select * from emp2 inner join dep2 on emp2.dep_id = dep2.id;

mysql> select * from emp2 inner join dep2 on emp2.dep_id = dep2.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | tank  | male   |   17 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|  4 | jason | female |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    200 |  200 | 技术         |
+----+-------+--------+------+--------+------+--------------+
5 rows in set (0.05 sec)

2、left join
# 2、左连接: 在内连接的基础上保留左表没有对应关系的记录(以null补全)
select * from emp2 left join dep2 on emp2.dep_id = dep2.id;

mysql> select * from emp2 left join dep2 on emp2.dep_id = dep2.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | tank  | male   |   17 |    200 |  200 | 技术         |
|  5 | owen  | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|  4 | jason | female |   28 |    202 |  202 | 销售         |
|  6 | sean  | female |   18 |    204 | NULL | NULL         |
+----+-------+--------+------+--------+------+--------------+

   
# 3、右连接: 在内连接的基础上保留右表没有对应关系的记录
select * from emp2 right join dep2 on emp2.dep_id = dep2.id;


mysql> select * from emp2 right join dep2 on emp2.dep_id = dep2.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | tank  | male   |   17 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|    4 | jason | female |   28 |    202 |  202 | 销售         |
|    5 | owen  | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营         |
+------+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)


- 全连接: union
# 4、全连接: 在内连接的基础上 保留左、右表没有对应关系的记录
select * from emp2 left join dep2 on emp2.dep_id = dep2.id 
union 
select * from emp2 right join dep2 on emp2.dep_id = dep2.id;

Copy after login

2.Subquery

# 子查询就是将一个查询语句的结果用括号括起来，当做另一个查询语句的条件去用

# 查询部门是技术或者人力资源的员工信息
'''
先获取技术部和人力资源的id号，再去员工表里根据前面的id筛选出符合要求的员工信息;
'''
select * from emp2 where dep_id in (select id from dep2 where name='技术' or name='人力资源');

 # 2.每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询
 # 查第一张emp表
# 第四步
select t1.id, t1.name, t1.hire_date, t2.*
from
# 第二步
# 拼接了 t1（emp） 与 t2（各部门最新入职的员工数据虚拟表）表的数据

emp as t1
inner join

# 第一步
# 子查询获取emp表中的  部门名称与最新入职的时间字段值 生成一张虚拟表
(select post, max(hire_date) as max_date from emp group by post) as t2
on
# 若 t1.post 的值与 t2.post 条件成立，则拼接对应的记录;
t1.post = t2.post
# 第三步
where t1.hire_date = t2.max_date;

Copy after login

The above is the detailed content of Master the basic operations of MySQL query statements. For more information, please follow other related articles on the PHP Chinese website!