共计 17322 个字符，预计需要花费 44 分钟才能阅读完成。
自动写代码机器人，免费开通
这篇文章给大家分享的是有关 MySQL 之查询语句的基本操作是什么的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考。一起跟随丸趣 TV 小编过来看看吧。
一. 查询语句的基本操作
1. 查询语句的基本操作
- select
- from
- where: 约束条件
- group by: 分组
- having：过滤
- distinct：去重
- order by：排序
- limit：限制查询记录的数量
- 聚合函数: count（计数）max（最大值）min（最小值）avg（平均值）sum(求和)二. 单表查询 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
(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- select * from emp; # 若数据比较多，比较凌乱，可以在表后面 + \G
- select * from emp\G重点：
写 SQL 语句必须遵循两点:
- 书写顺序:
# 获取 id 为 4、5 的两条记录
select * from emp where id 3 and id
- select
- from
- where
- 执行顺序:
比如: 图书管理员，得先找到是哪一个图书馆 (哪张表),
再找这本书在图书馆的哪个位置 (哪一条记录), 最后查找这个本书中某一页 (哪些字段值);
select * from emp where id 3 and id
- from --- 找到图书馆
- where --- 找到书的位置
- select --- 找到书本中的某一页
注意: 必须记住 SQL 语句的 书写顺序 与 执行顺序 (*******);1.where(约束条件)
# PS: 根据执行顺序来书写 SQL 语句，一步一步来写;
# 1. 查询 id 大于等于 3 小于等于 6 的数据 (and: 与)
select * from emp where id = 3 and id
mysql SELECT * FROM EMP WHERE ID 3 AND ID
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 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)
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)
# 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)
# 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)
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);
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)2.group by(分组)
- 书写顺序:
- 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;
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)
select post, count(salary) from emp group by post;
select post, count(post_comment) from emp group by post;
查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex, avg(salary) from emp group by sex;
. 统计各部门年龄在 30 岁以上的员工平均工资:
# 步骤: 先找到表，再找年龄 30 岁以上，再根据部门分组，最后求平均薪资;
select post, avg(salary) from emp where age 30 group by post;总结: 聚合函数，必须跟在 group by 后面 (执行顺序);
# 聚合函数: 若没有 group by 分组，默认将查出来的数据当做一个分组, 也能使用;
select max(salary) from emp;# 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;
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;补充: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)3.having(过滤)
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)4.distinct(去重)
# 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)
+-----------------------------------+
5 rows in set (0.00 sec)5.order by(排序)
- 书写顺序:
- 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)
# 先按照 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);9.limit(限制查询记录的数量)
书写顺序:
- select
- from
- order by
- limit
执行顺序:
- from
- select
- order by
- limit
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)10. 正则
# 在编程中，凡是看到 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)三. 多表查询
多表查询
- 关联查询
- 子查询
创建表与插入数据准备
# 建表
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);
# 指到了如何根据表关系对字段进行拆分，目的是为了更好的管理，表数据都存放在硬盘中，存不是目的，目的是为了取，所以我们将数据从硬盘读到内存中，接下来我们因应该将他们拼成一张表来查询更加合理;
# 注意: 将拆分的表，再拼接到一起进行查询, 可以通过一张表查另一张表的数据;1. 关联查询
# 左表的一条记录与右表的一条记录都对应一遍称之为 -- 笛卡尔积 PS: 百度科普
# 将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据 举例: 通过之前准备的数据进行多表操作
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 = 技术 联表查询:
- 内链接: 只去两张表有对应关系的记录
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)
# 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;2. 子查询
# 子查询就是将一个查询语句的结果用括号括起来，当做另一个查询语句的条件去用
# 查询部门是技术或者人力资源的员工信息
先获取技术部和人力资源的 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.*
# 第二步
# 拼接了 t1（emp）与 t2（各部门最新入职的员工数据虚拟表）表的数据
emp as t1
inner join
# 第一步
# 子查询获取 emp 表中的 部门名称与最新入职的时间字段值 生成一张虚拟表
(select post, max(hire_date) as max_date from emp group by post) as t2
# 若 t1.post 的值与 t2.post 条件成立，则拼接对应的记录;
t1.post = t2.post
# 第三步
where t1.hire_date = t2.max_date;感谢各位的阅读！关于 MySQL 之查询语句的基本操作是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到吧！
向 AI 问一下细节
丸趣 TV 网 – 提供最优质的资源集合！
正文完
![Frank#smith[Kfyzeiuqujbpygze,2,5]](https://cravatar.cn/avatar/ebd2642d86a4b40100d29dc76ec89b0d?s=96&d=monsterid&r=r)
