MySQL操作二(多表查询)_zhenliang_Horn的博客-程序员秘密

技术标签: mysql  

数据准备

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

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

#插入数据
insert into department values
(200,'挖矿小分队'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into staff(name,sex,age,dep_id) values
('程咬金','male',38,200),
('露娜','female',26,201),
('李白','male',38,201),
('王昭君','female',28,202),
('典韦','male',118,200),
('小乔','female',16,204)
;


#查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.06 sec)

mysql> desc staff;
+--------+-----------------------+------+-----+---------+----------------+
| Field  | Type                  | Null | Key | Default | Extra          |
+--------+-----------------------+------+-----+---------+----------------+
| id     | int(11)               | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)           | YES  |     | NULL    |                |
| sex    | enum('male','female') | NO   |     | male    |                |
| age    | int(11)               | YES  |     | NULL    |                |
| dep_id | int(11)               | YES  |     | NULL    |                |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.04 sec)

#表department与staff
mysql> select * from department;
+------+-----------------+
| id   | name            |
+------+-----------------+
|  200 | 挖矿小分队      |
|  201 | 人力资源        |
|  202 | 销售            |
|  203 | 运营            |
+------+-----------------+
4 rows in set (0.00 sec)

mysql> select * from staff;
+----+-----------+--------+------+--------+
| id | name      | sex    | age  | dep_id |
+----+-----------+--------+------+--------+
|  1 | 程咬金    | male   |   38 |    200 |
|  2 | 露娜      | female |   26 |    201 |
|  3 | 李白      | male   |   38 |    201 |
|  4 | 王昭君    | female |   28 |    202 |
|  5 | 典韦      | male   |  118 |    200 |
|  6 | 小乔      | female |   16 |    204 |
+----+-----------+--------+------+--------+
6 rows in set (0.00 sec)

连表查

  • 交叉连接 不适用任何匹配条件,生成笛卡尔积

  • select * from 表1,表2;

  • 内链接 (常用) 只连接匹配的行

  • select * from staff inner join department on 条件(表1.字段=表2.字段)

  • 小结: 找两张表共有的部分,利用条件从笛卡尔积结果中筛选出了正确的结果

  • 外连接

  • 左外连接(常用) 优先显示左表全部记录 left join

  • select * from staff left join department on 条件(表1.字段=表2.字段)

  • 右外链接 优先显示右表全部记录 right join

  • select * from staff right join department on 条件(表1.字段=表2.字段)

  • 全外连接 显示左右两个表全部记录

  • 查询语句 (mysql没有full join,可以有左外连接+右外连接来实现全外连接)

select * from 表1  left join 表2 on 条件((表1.字段=表2.字段))
union
select * from 表1  right join 表2 on 条件((表1.字段=表2.字段));

#全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意:mysql不支持全外连接 full join
#强调:mysql可以使用此种方式间接实现全外连接
#注意 union与union all的区别:union会去掉相同的纪录
  • 练习

1.找挖矿小分队的所有员工的信息
#答:
mysql> select * from staff inner join department on department.id=staff.dep_id where department.name='挖矿小分队';
+----+-----------+------+------+--------+------+-----------------+
| id | name      | sex  | age  | dep_id | id   | name            |
+----+-----------+------+------+--------+------+-----------------+
|  1 | 程咬金    | male |   38 |    200 |  200 | 挖矿小分队      |
|  5 | 典韦      | male |  118 |    200 |  200 | 挖矿小分队      |
+----+-----------+------+------+--------+------+-----------------+
2 rows in set (0.00 sec)

2.查找人力资源所有的员工名字
#答:  (名字太长可以起别名)
mysql> select staff.name  from staff inner join department as dep on dep.id=staff.dep_id where dep.name='人力资源';
+--------+
| name   |
+--------+
| 露娜   |
| 李白   |
+--------+
2 rows in set (0.00 sec)

3.找出年龄大于38的员工的姓名,及其所在的部门名称
#答:
mysql> select staff.name,dep.name  from staff  inner join department as dep on dep.id=staff.dep_id where age>38;
+--------+-----------------+
| name   | name            |
+--------+-----------------+
| 典韦   | 挖矿小分队       |
+--------+-----------------+
1 row in set (0.00 sec)

4.以内连接的方式查询 staff 和 department表,并且以age字段的升序方式显示
答:
mysql> select *  from staff  inner join department as dep on dep.id=staff.dep_id order by age;
+----+-----------+--------+------+--------+------+-----------------+
| id | name      | sex    | age  | dep_id | id   | name            |
+----+-----------+--------+------+--------+------+-----------------+
|  2 | 露娜      | female |   26 |    201 |  201 | 人力资源        |
|  4 | 王昭君    | female |   28 |    202 |  202 | 销售            |
|  1 | 程咬金    | male   |   38 |    200 |  200 | 挖矿小分队      |
|  3 | 李白      | male   |   38 |    201 |  201 | 人力资源        |
|  5 | 典韦      | male   |  118 |    200 |  200 | 挖矿小分队      |
+----+-----------+--------+------+--------+------+-----------------+
5 rows in set (0.05 sec)

5.找到部门为 挖矿小分队 和 人力资源 的所有员工的名字
#答: 
mysql> select staff.name from staff inner join department as dep on dep.id = staff.dep_id where dep.name in ('挖矿小分队','人力资源');
+-----------+
| name      |
+-----------+
| 程咬金    |
| 露娜      |
| 李白      |
| 典韦      |
+-----------+
4 rows in set (0.00 sec)

子查询

#1:子查询是将一个查询语句嵌套在另一个查询语句中。
#2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
#3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4:还可以包含比较运算符:= 、 !=、> 、<等
#5: 多用连表查,因为连表查询比子查询效率高

1.用子查询 找到部门是销售的所有员工的姓名
#解题思路
①先找department表部门为销售的部门的id
mysql> select id from department where name = '销售';
+------+
| id   |
+------+
|  202 |
+------+
1 row in set (0.00 sec)

②再找staff表中部门dep_id = 202
mysql> select name from staff where dep_id = 202;
+-----------+
| name      |
+-----------+
| 王昭君    |
+-----------+
1 row in set (0.00 sec)

③字表查
mysql> select name from staff where dep_id =(select id from department where name = '销售');
+-----------+
| name      |
+-----------+
| 王昭君    |
+-----------+
1 row in set (0.00 sec)

2.用子查询 找到部门为 销售 和 人力资源 的所有员工的名字
①先找department表部门为销售和人力资源的部门的id
mysql> select id from department where name = '销售' or name = '人力资源';
+------+
| id   |
+------+
|  201 |
|  202 |
+------+
2 rows in set (0.00 sec)

②子查询
mysql> select name from staff where dep_id in (select id from department where name = '销售' or name = '人力资源');
+-----------+
| name      |
+-----------+
| 露娜      |
| 李白      |
| 王昭君    |
+-----------+
3 rows in set (0.00 sec)

1. 带IN关键字的子查询
①查询平均年龄在28岁以上的部门名
select id,name from department
    where id in 
        (select dep_id from staff group by dep_id having avg(age) > 28);
#结果        
+------+-----------------+
| id   | name            |
+------+-----------------+
|  200 | 挖矿小分队      |
|  201 | 人力资源        |
+------+-----------------+
2 rows in set (0.00 sec)

②查看部门是挖矿小分队员工姓名
select name from staff
    where dep_id in 
        (select id from department where name='挖矿小分队');
#结果
+-----------+
| name      |
+-----------+
| 程咬金    |
| 典韦      |
+-----------+
2 rows in set (0.00 sec)

③查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from staff);
#结果
+--------+
| name   |
+--------+
| 运营   |
+--------+
1 row in set (0.02 sec)


2. 带比较运算符的子查询
#比较运算符:=、!=、>、>=、<、<=、<>

①查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from staff where age > (select avg(age) from staff);
+--------+------+
| name   | age  |
+--------+------+
| 典韦   |  118 |
+--------+------+
1 row in set (0.00 sec)


②查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from staff t1
inner join 
(select dep_id,avg(age) avg_age from staff group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age; 
#结果
+--------+------+
| name   | age  |
+--------+------+
| 李白   |   38 |
| 典韦   |  118 |
+--------+------+
2 rows in set (0.04 sec)


3. 带EXISTS关键字的子查询

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

#department表中存在dept_id=203,Ture
select * from staff
    where exists
    (select id from department where id=200);
#结果
+----+-----------+--------+------+--------+
| id | name      | sex    | age  | dep_id |
+----+-----------+--------+------+--------+
|  1 | 程咬金    | male   |   38 |    200 |
|  2 | 露娜      | female |   26 |    201 |
|  3 | 李白      | male   |   38 |    201 |
|  4 | 王昭君    | female |   28 |    202 |
|  5 | 典韦      | male   |  118 |    200 |
|  6 | 小乔      | female |   16 |    204 |
+----+-----------+--------+------+--------+
6 rows in set (0.00 sec)



#department表中存在dept_id=205,False
mysql> select * from staff
    where exists
    (select id from department where id=204);
Empty set (0.00 sec)
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_30312047/article/details/107701244

智能推荐

msf利用Metasploitable 2 漏洞之6667端口之unreal_ircd_3281_backdoor_乌鸦安全的博客-程序员秘密

Metasploitable漏洞演练系统 是Metasploit团队维护的一个集成了各种漏洞弱点的Linux主机(ubuntu)镜像,这方便大家对于msf的学,以下以6667端口的unreal_ircd_3281_backdoor进行攻击。可以在虚拟机中启动(登录密码和账号都是msfadmin)查看其ip:    ifconfig地址为:192.168.127.145然后在kali linux中进...

android中 EditText控件 日期选择输入_android 日期输入_小叮当不懒的博客-程序员秘密

XML文件&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto" xmlns:tools="http://schemas.andr

QTextEdit添加内容包含中文时显示乱码问题解决_qtextedit中文乱码_wangaolong的博客-程序员秘密

QTextEdit添加内容包含中文时显示乱码问题解决最简单的方法:#pragma execution_character_set(“utf-8”)QTextEdit *m_textShower = new QTextEdit();(1)设置字体的方法QFonttextFont;textFont.setPointSize(16);m_textShower-&gt;setFont(text...

kaldi在线语音识别bug解决_蓝鲸123的博客-程序员秘密

【问题描述】使用kaldi工具包进行在线语音识别,识别麦克风输入,输出识别结果。使用egs/voxforge/online_demo文件夹时,./run.sh –test-mode live输出portaudio failed to open the defalut stream【解决办法】分三步进行解决。1.首先检查linux系统录音功能是否可用:arecord命令,如are...

cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library_theskylife的博客-程序员秘密

如果遇到以上错误,大部分是因为没有正确安装驱动程序造成的。在这里重点讲述以下为什么会报错的原因:1.没有安装oracle的数据库驱动;2.在调用的时候,没有找到相应的路径;3.找到了相应的路径,却没有正确调用;4.找到了相应的路径,正确调用了,版本不对。解决方案如下:1、去oracle官网下载数据库驱动程序,数据库驱动链接2.将加压后的文件,添加到环境变量;3.将加压后文件下的d...

从入门到精通 - Fayson带你玩转CDH_Hadoop_SC的博客-程序员秘密

Fayson保持每天推一篇文章,本文随之每天更新。以下所有文章均为超链接,可以直接点击文章名跳转。还没附上超链接的文章是还没上传的,会再后续更新后陆续补上1.规划设计1.1.on-premise部署规划0001-《CDH网络要求(Lenovo参考架构)》0062-《如何为Hadoop集群选择正确的硬件》0158-《如何给Hadoop集群划分角色》1.2.on private c...

随便推点

免费迅睿CMS采集插件工具支持批量网站采集_讯瑞cms 文件批量导入_147SEO的博客-程序员秘密

怎么利用免费迅睿CMS采集器快速收录排名获得流量。最近一直有很多朋信给我抱怨,说用迅睿CMS插件根本同时管理不过来十个网站,每天都要检查状态,每天补写采集规则都要花大量的时间。怎么利用免费的迅睿CMS采集器批量快速收录排名获得流量怎么批量管理迅睿CMS网站:一、 有一款批量管理检测工具1、监控数据:软件上直接监控已发布数量、待发布数量、是否伪原创成功、发布状态(是否发布成功)、发布的网址、发布程序、发布时间等详细解答:不管是帝国、易优、迅睿、ZBLOG、织梦、WP、PB、

nginx中配置proxy_cache使用内存的方法_nginx proxy_cache 只保存到内存中_GoRustNeverStop的博客-程序员秘密

http://www.jbxue.com/article/3770.html nginx用作反向代理,可以缓存代理的内容,配置如下: 复制代码 代码如下:proxy_cache_path /data/nginx_cache/ levels=1:2 keys_zone=cache0:10m;proxy_temp_path /data/nginx_cache_tmp/;prox

SQL SERVER 数据库自动备份(清除指定天数前的备份文件)_sqlserver定时删除三天前的数据_neekerss的博客-程序员秘密

一、SQL SERVER 数据库自动备份打开SQL SERVER 2005代理-->作业--右键新建作业,将以下代码放到作业步骤中的命令里:declare @data_3ago nvarchar(50) declare @cmd varchar(50)  set @data_3ago =d:/TangGang_JZLIntDB(+convert(varchar(10),getdat

matlab能不能生成exe文件夹,matlab生成EXE文件_weixin_40002611的博客-程序员秘密

通常编 写的matlab程序只能在装有matlab的机器上执行,但是没有安装matlab的怎么办呢?这个可以使用matlab的自带工具-Deploy Tool。Matlab的Deploy Tools 可以方便的将matlab工程编译成 所支持的类型,例如 一般的Windows应用程序,Matlab for .NET/COM ,Matlab for Java,Matlab for Excel 等。...

指定swagger访问路径_swagger 指定_Mr_yiyang的博客-程序员秘密

通过nagix转发后的地址访问swagger,内部的接口访问路径并没有改变使用nohup java -jar -Dspringfox.documentation.swagger.v2.host=www.***.com/你的路径 模块名.jar &gt;/dev/null 2&gt;&amp;1 &amp;来改变访问地址

推荐文章

热门文章

相关标签