mysql之单表查询(group by/having/order by/limit/聚合函数)与多表联查(内连接/左连接/右连接/全连接/子查询)

最新推荐文章于 2024-05-22 00:05:02 发布
最新推荐文章于 2024-05-22 00:05:02 发布
阅读量1k 收藏 5
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_40406241/article/details/90236053
版权

一、单表查询

先准备好实验用的表

create table emp(
  id int primary key 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、初始sql的执行顺序

select id,name from emp where id >= 3 and id <= 6;
# 先后顺序
from       # 先确定是哪张表
where    # 再确定是否有过滤条件
select    # 最后确定要过滤出来的数据的哪些字段

2、where约束条件

2.1、查询id大于等于3小于等于6的数据

# select id,name from emp where id >= 3 and id <= 6;
# select *  from emp where id between 3 and 6;  

mysql> select * from emp where id>=3 and id <=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 | tank  | 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)

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 | tank  | 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.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);  # 简写

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.04 sec)

mysql> select * from emp where salary in (20000,18000,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.06 sec)

2.3、查询员工姓名中包含o字母的员工姓名和薪资

mysql> select name,salary from emp where name like '%o%';
+-------+------------+
| name  | salary     |
+-------+------------+
| jason |    7300.33 |
| egon  | 1000000.31 |
| owen  |    2100.00 |
+-------+------------+

在刚刚开始接触mysql查询时，建议按照查询的优先级顺序拼接处sql语句。

先是查哪张表   from emp

接着是根据什么条件查   where name like '%o%'

再是对查询出来的数据筛选展示部分   select name,salary

2.4、查询员工姓名是由四个字符组成的员工姓名和其薪资

mysql> select name,salary from emp where name like '____';
+------+------------+
| name | salary     |
+------+------------+
| egon | 1000000.31 |
| tank |    3500.00 |
| owen |    2100.00 |
| nick |   30000.00 |
| sean |   10000.00 |
+------+------------+
5 rows in set (0.00 sec)

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 |
+------+------------+

2.5、查询id小于3或者大于6的数据

select *  from emp where id not between 3 and 6;

2.6、查询薪资不在20000，18000，17000范围的数据

select * from emp where salary not in (20000,18000,17000);

2.7、查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is

mysql> select name,post from emp where post_comment=null;
Empty set (0.00 sec)

mysql> select name,post from emp where post_comment is null;
+-----------+-----------------------------+
| name      | post                        |
+-----------+-----------------------------+
| jason     | 张江第一帅形象代言          |
| egon      | teacher                     |
| kevin     | teacher                     |
| tank      | teacher                     |
| owen      | teacher                     |
| jerry     | teacher                     |
| nick      | teacher                     |
| sean      | teacher                     |
| 歪歪      | sale                        |
| 丫丫      | sale                        |
| 丁丁      | sale                        |
| 星星      | sale                        |
| 格格      | sale                        |
| 张野      | operation                   |
| 程咬金    | operation                   |
| 程咬银    | operation                   |
| 程咬铜    | operation                   |
| 程咬铁    | operation                   |
+-----------+-----------------------------+
18 rows in set (0.00 sec)

mysql> select name,post from emp where post_comment is not null;
Empty set (0.00 sec)

3、group by + 分组依据

数据分组的应用场景：每个部门的平均薪资，男女比例等

3.1、按部门分组

mysql> select * from emp group by post;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'day41.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select id,name,sex from emp group by post;  # 验证
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'day41.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

如果没有设置严格模式的话，上面的查询语句会执行，输出的是每个组的第一条数据，那么我们需要设置严格模式

设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取

# 设置严格模式
set global sql_mode="strict_trans_tables,only_full_group_by";

设置完后重新连接客户端

mysql> select post from emp group by post;
+-----------------------------+
| post                        |
+-----------------------------+
| operation                   |
| sale                        |
| teacher                     |
| 张江第一帅形象代言          |
+-----------------------------+

需要强调的是，一旦分组了，就不能再直接查找到单个的数据信息了，只能获取到组名。

3.2、获取每个部门的信息（运用聚合函数）

以组为单位统计组内数据>>>>>>聚合查询（聚集到一起合成为一个结果）

3.2.1、每个部门的最高工资

select post,max(salary) from  emp group by post;

3.2.2、每个部门的最低工资

select post,min(salary) from emp group by post;

3.2.3、每个部门的平均工资

select post,avg(salary) from emp group by post;

3.2.4、每个部门的工资总和

select post,sum(salary) from emp group by post;

3.2.5、每个部门的人数

select post,count(id) from emp group by post;

3.3、查询分组之后的部门名称和每个部门下的所有姓名（聚合函数）

group_concat(分组之后用)不仅可以用来显示除分组外字段还有拼接字符串的作用

mysql> select post,group_concat(name) from emp group by post;  # 查询部门内人的名字
+-----------------------------+------------------------------------------------+
| post                        | group_concat(name)                             |
+-----------------------------+------------------------------------------------+
| operation                   | 张野,程咬金,程咬银,程咬铜,程咬铁               |
| sale                        | 歪歪,丫丫,丁丁,星星,格格                       |
| teacher                     | egon,kevin,tank,owen,jerry,nick,sean           |
| 张江第一帅形象代言          | jason                                          |
+-----------------------------+------------------------------------------------+
4 rows in set (0.00 sec)

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                     | egon_SB,kevin_SB,tank_SB,owen_SB,jerry_SB,nick_SB,sean_SB     |
| 张江第一帅形象代言          | jason_SB                                                      |
+-----------------------------+---------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select post,group_concat(name,':',salary) from emp group by post;  # 查出 姓名：工资
+-----------------------------+---------------------------------------------------------------------------------------------------+
| post                        | group_concat(name,':',salary)                                                                     |
+-----------------------------+---------------------------------------------------------------------------------------------------+
| operation                   | 张野:10000.13,程咬金:20000.00,程咬银:19000.00,程咬铜:18000.00,程咬铁:17000.00                     |
| sale                        | 歪歪:3000.13,丫丫:2000.35,丁丁:1000.37,星星:3000.29,格格:4000.33                                  |
| teacher                     | egon:1000000.31,kevin:8300.00,tank:3500.00,owen:2100.00,jerry:9000.00,nick:30000.00,sean:10000.00 |
| 张江第一帅形象代言          | jason:7300.33                                                                                     |
+-----------------------------+---------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select post,group_concat(salary) from emp group by post;  # 查出部门内的工资
+-----------------------------+--------------------------------------------------------------+
| post                        | group_concat(salary)                                         |
+-----------------------------+--------------------------------------------------------------+
| operation                   | 10000.13,20000.00,19000.00,18000.00,17000.00                 |
| sale                        | 3000.13,2000.35,1000.37,3000.29,4000.33                      |
| teacher                     | 1000000.31,8300.00,3500.00,2100.00,9000.00,30000.00,10000.00 |
| 张江第一帅形象代言          | 7300.33                                                      |
+-----------------------------+--------------------------------------------------------------+

3.4、补充concat（不分组的时候用）：拼接字符串达到更好的显示效果，as语法

mysql> select name as 姓名,salary as 薪资 from emp;  # as起别名
+-----------+------------+ 
| 姓名      | 薪资       |
+-----------+------------+
| 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.13 sec)

mysql> select concat('NAME:',name) as 姓名,concat('SAL:',salary) as 薪资 from emp;  # 拼接显示
+----------------+----------------+
| 姓名           | 薪资           |
+----------------+----------------+
| 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   |
+----------------+----------------+

3.5、补充as语法：可以给字段或表起别名

mysql> select emp.id,emp.name from emp as t1;  # 报错是因为表名已经暂时用了别名t1
ERROR 1054 (42S22): Unknown column 'emp.id' in 'field list'
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 | 程咬铁    |
+----+-----------+

3.6、查询四则运算

查询每个人的年薪

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.07 sec)

mysql> select name,salary*12 annual_salary from emp;  # as可以省略
+-----------+---------------+
| 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 |
+-----------+---------------+

3.7、练习

刚开始查询表，一定要按照最基本的步骤，先确定是哪张表，再确定查这张表也没有限制条件，再确定是否需要分类，最后再确定需要什么字段对应的信息
 

--1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;
--2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from emp group by post;
--3. 查询公司内男员工和女员工的个数
select sex,count(id) from emp group by sex;
--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;

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

mysql> select id,name,age from emp where max(salary)>3000;  # 报错
ERROR 1111 (HY000): Invalid use of group function
mysql> select max(salary) from emp;
+-------------+
| max(salary) |
+-------------+
|  1000000.31 |
+-------------+
# 正常运行，不分组意味着每一个人都是一组，等运行到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过滤出来的虚拟表进行一个分组

4、having+限制条件

having的语法格式与where一致，只不过having是在分组之后进行的过滤，即where虽然不能用聚合函数，但是having可以！

4.1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于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)

# 强调：having必须在group by后面使用
mysql> select * from emp having avg(salary) > 10000;  # 报错
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'day41.emp.id'; this is incompatible with sql_mode=only_full_group_by

5、distinct：去重

对有重复的展示数据进行去重操作

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

mysql> select distinct post from emp;
+-----------------------------+
| post                        |
+-----------------------------+
| 张江第一帅形象代言          |
| teacher                     |
| sale                        |
| operation                   |
+-----------------------------+

6、order by：排序，asc--升序   desc--降序       默认是asc升序

select * from emp order by salary asc;  --默认升序
select * from emp order by salary desc; --以工资降序

select * from emp order by age 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)
    ;

7、limit

--限制展示条数
select * from emp limit 3;

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

--分页显示
select * from emp limit 0,5;  --第一个参数表示起始位置,不包含写的0，第二个参数表示的是条数，不是索引位置

select * from emp limit 5,5; --不包含5

8、正则

.*   --贪婪匹配

.*?  --非贪婪匹配

mysql> select * from emp where name regexp '^j.*(n|y)$';
+----+-------+--------+-----+------------+-----------------------------+--------------+---------+--------+-----------+
| id | name  | sex    | age | hire_date  | post                        | post_comment | salary  | office | depart_id |
+----+-------+--------+-----+------------+-----------------------------+--------------+---------+--------+-----------+
|  1 | jason | male   |  18 | 2017-03-01 | 张江第一帅形象代言          | NULL         | 7300.33 |    401 |         1 |
|  6 | jerry | female |  18 | 2011-02-11 | teacher                     | NULL         | 9000.00 |    401 |         1 |
+----+-------+--------+-----+------------+-----------------------------+--------------+---------+--------+-----------+

9、书写顺序

select distinct * from 表名
where 限制条件  
group by 分组依据 
having 过滤条件
order by  排序依据
limit 限制起始位置和展示条数

执行顺序
from
where
group by
having
order by
limit
distinct
select

10、exists

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，
而是返回一个真假值，True或False。
当返回True时，外层查询语句将进行查询
当返回值为False时，外层查询语句不进行查询。

select * from emp where exists (select * from dep where 1=2);
select * from emp where exists (select * from dep where id >1);

11、查出部门中工资大于部门平均工资的员工及工资

select name,salary from 
(select name,salary,post from emp) as t1
    inner join
    (select post as post1,avg(salary) as avg from emp group by post) as t2
    on t1.post=t2.post1
    where salary>avg;

二、多表查询

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)
;

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

2、表查询

select * from emp,dep;

 左表一条记录与右表所有记录都对应一遍>>>笛卡尔积

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

# 查询员工及所在部门的信息

mysql> select * from emp;
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | jason | male   |   18 |    200 |
|  2 | egon  | female |   48 |    201 |
|  3 | kevin | male   |   38 |    201 |
|  4 | nick  | female |   28 |    202 |
|  5 | owen  | male   |   18 |    200 |
|  6 | jerry | female |   18 |    204 |
+----+-------+--------+------+--------+
6 rows in set (0.00 sec)

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

mysql> select * from emp,dep where emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|  4 | nick  | female |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    200 |  200 | 技术         |
+----+-------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)

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

mysql> select * from emp,dep where emp.dep_id=dep.id and dep.name='技术';
+----+-------+------+------+--------+------+--------+
| id | name  | sex  | age  | dep_id | id   | name   |
+----+-------+------+------+--------+------+--------+
|  1 | jason | male |   18 |    200 |  200 | 技术   |
|  5 | owen  | male |   18 |    200 |  200 | 技术   |
+----+-------+------+------+--------+------+--------+

像上面的方法也可以完成我们的需求，但是mysql中有专门对应的方法。

2.1、内连接：只取到两张表有对应关系的记录

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

mysql> select * from emp inner join dep on emp.dep_id=dep.id where dep.name='技术';
+----+-------+------+------+--------+------+--------+
| id | name  | sex  | age  | dep_id | id   | name   |
+----+-------+------+------+--------+------+--------+
|  1 | jason | male |   18 |    200 |  200 | 技术   |
|  5 | owen  | male |   18 |    200 |  200 | 技术   |
+----+-------+------+------+--------+------+--------+

 dep表中没有id=204的数据，因此只取到两张表共有对应的记录

2.2、左连接：在内连接的基础上保留左表没有对应关系的记录，右表对应的给null

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

2.3、右连接：在内连接的基础上保留右表没有对应关系的记录，左表给null

mysql> select * from emp right join dep on emp.dep_id=dep.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   38 |    201 |  201 | 人力资源     |
|    4 | nick  | female |   28 |    202 |  202 | 销售         |
|    5 | owen  | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营         |
+------+-------+--------+------+--------+------+--------------+

2.4、全连接：在内连接的基础上保留左、右两表所有记录（没有对应关系的也保留）

mysql> 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;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术          |
|    5 | owen  | male   |   18 |    200 |  200 | 技术          |
|    2 | egon  | female |   48 |    201 |  201 | 人力资源      |
|    3 | kevin | male   |   38 |    201 |  201 | 人力资源      |
|    4 | nick  | female |   28 |    202 |  202 | 销售          |
|    6 | jerry | female |   18 |    204 | NULL | NULL          
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营          |
+------+-------+--------+------+--------+------+--------------+

3、子查询

就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用

3.1、查询部门是技术或者人力资源的员工信息

首先获取技术部和人力资源部的id号，再去员工表里筛选出符合要求的员工信息

 select * from emp where dep_id in (select id from dep where name in ('技术','人力资源'));
mysql> select * from emp where dep_id in (select id from dep where name in ('技术','人力资源'));
+----+-------+--------+------+--------+
| id | name  | sex    | age  | dep_id |
+----+-------+--------+------+--------+
|  1 | jason | male   |   18 |    200 |
|  2 | egon  | female |   48 |    201 |
|  3 | kevin | male   |   38 |    201 |
|  5 | owen  | male   |   18 |    200 |
+----+-------+--------+------+--------+

3.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
;

记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询

BigMasterSun
关注
  • 0
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL union 和 order by 一起使用需要注意的问题
我的博客
11-08 3838
MySQL union 和 order by 一起使用需要注意的问题，例如union 和 多个order by 是否 可以同时使用，如果可以使用条件是什么？
mysql连接查询排序_MySQL查询优化:连接查询排序limit(join、order by、limit语句)
weixin_39665379的博客
01-28 228
本文链接：https://blog.csdn.net/xiao__gui/article/details/8616224不知道有没有人碰到过这样恶心的问题：两张表连接查询并limit，SQL效率很高，但是加上order by以后，语句的执行时间变的巨长，效率巨低。情况是这么一个情况：现在有两张表，team表和people表，每个people属于一个team，people中有个字段team_id。下...
Group by、Having使用方法
最新发布
2301_77836489的博客
05-22 359
Group by、Having使用方法
mysql连接查询group by_mysql连接查询
weixin_32099703的博客
01-28 328
sql知识一，两表外连接查询1，A表和B表左连接先将左表(A)数据查出，然后根据on后面的条件，将右表中凡是id与左表id相等的记录都查出来，与匹配的左表记录依次排成一行或多行，若无匹配的记录，则显示null。2，A表和B表右连接先将右表(B)数据查出，然后根据on后面的条件，将左表中凡是id与右表id相等的记录都查出来，与匹配的左表记录依次排成一行或多行，若无匹配的记录，则显示null3，A表和...
连接（join）group by、order by、where的执行顺序
热门推荐
mine_song的博客
04-15 3万+
连接（join）group by、order by、where的执行顺序
mysql多表关联 group by + order by 优化
v神
08-27 3640
最近发现服务器里mysql对CPU的占用明显提高了，昨天晚上把慢查询日志打开，今天过来看到了一个反复出现的慢查询，贴上原SQL： SELECT c.id, c.vin, c.license_plate, c.owner_name, c.model, c.file_id, c.path, count( w.id ) AS count, count( IF ( w.type = 1, TRUE, NULL ) ) AS type_1, count( IF ( w.type = 2, TR
MySQL单表查询操作实例详解【语法、约束、分组、聚合、过滤、排序等】
09-08
MySQL单表查询是数据库操作中最基础也是最常用的功能之一，它涉及到多种操作和概念，包括语法、约束、分组、聚合、过滤和排序等。本文将通过实例详细解析这些知识点。 **一、查询语法** MySQL的单表查询基本语法...
mysql踩坑之limit与sum函数混合使用问题详解
09-09
在MySQL数据库中，`LIMIT` 与聚合函数如 `SUM()` 混合使用时，可能会引发一些预期之外的问题。本文将深入探讨这个问题，并提供解决方案。 首先，让我们回顾一下问题的背景。假设我们有一个名为 `order` 的订单表，...
mysql使用GROUP BY分组实现取前N条记录的方法
09-10
在MySQL数据库中，GROUP BY语句通常用于对数据进行分组，以便对每个分组执行聚合函数，如COUNT(), SUM(), AVG(), MAX(), MIN()等。然而，有时我们需要在分组后获取每个分组的前N条记录，这在处理统计分析或排名问题...
MySql基本查询、连接查询、子查询、正则表达查询讲解
08-24
3.2、一般情况下group需与统计函数（聚合函数）一起使用才有意义 先准备一些数据： [sql] view plain copy create table EMPLOYEES( EMP_NAME CHAR(10) NOT NULL, EMP_SALARY INT unsigned NOT NULL, EMP_DEP ...
数据库-left join与多个order by排序实例
爱玩的安哥
03-07 2408
有这个么需求，一些练习会有人评论，在列出练习时要将有评论的练习排在前面，这时候可以使用left join与多个order by的办法来达到要求。 首先，有practice_id为3539、3715、3514三个练习是有人评论的，3514评论时间为最新： SELECT * FROM comments WHERE practice_id IN ( 3514, 3715, 3527, 3539 ) 使用left join SELECT `practices`.* FROM `practices
Oracle→分组函数、GROUP BY、HAVING、多表查询、连接、外连接、自连接、子查询单行多行、ROWNUM、ANY、ALL、NOT IN(...IS NOT NULL)、条件求和、DUAL
实干、效率、武功、心法
03-05 202
分组函数 GROUP BY HAVING GROUP BY ROLLUP() 多表查询 等值连接 不等值连接 外连接 自连接 层次查询 子查询 子查询ROWNUM NOT IN(…IS NOT NULL) ROWNUM分页 执行计划 操作字段条件求和 DUAL临时表 ...
MySQL(三) 数据库表的查询操作【重要】
weixin_30299539的博客
12-10 1025
　　　　　　序言 　　　　　　　　　　1、MySQL表操作(创建表，查询表结构，更改表字段等)， 　　　　　　　　　　2、MySQL的数据类型(CHAR、VARCHAR、BLOB,等)， 　　　　　　　　　　本节比较重要，对数据表数据进行查询操作，其中可能大家不熟悉的就对于INNER JOIN(内连接)、LEFT JOIN(左连接)、RIGHT JOIN(右连接)等一些复杂查询。 通过本节的...
解决：[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause 的问题 MySQ
Allen . Liu
08-28 2732
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY ...
mysql 多表排序_mysql多表查询及其 group by 组内排序
weixin_33210666的博客
01-19 297
//多表查询：得到最新的数据后再执行多表查询SELECT *FROM `students` `st` RIGHT JOIN(SELECT * FROM(SELECT * FROM goutong WHERE goutongs='asdf' ORDER BY time DESC) AS gtt GROUP BY gtt.name_id ORDER BY gtt.goutong_time DESC )...
MySQL数据表查询group by的用法及distinct区别
Johnny's Lab
12-27 1296
分组聚合 最常用的场景就是分组聚合，由多条变成比较少记录的过程是分组聚合，既然需要分组，就要考虑使用哪个字段进行分组最好，一般来说，主键才是group by 后面的分组字段 主键（PRIMARY KEY）的完整称呼是“主键约束”，分为单字段主键和多字段联合主键，这两种主键约束都可以创建、修改和删除 使用主键应注意以下几点： 每个表只能定义一个主键。 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主- 键值的两行数据。这是唯一性原则。 一个字段名只能在联合主键字段表中出现一次。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值