多表查询、组合查询和约束

璃尔 °

已于 2022-07-14 10:49:04 修改

阅读量325

点赞数

分类专栏： MySQL 文章标签： mysql

于 2021-07-11 12:14:19 首次发布

本文链接：https://blog.csdn.net/qq_43492477/article/details/118596688

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、多表查询

在查询时，在满足数据库设计三范式的前提下，数据会存储在不同的表中，各个表通过字段产生关联，如果想在一次查询中将两个或多个表的信息一同显示,这个时候就需要使用连接查询，将多个表的数据连接在一起使用，进行结果的显示。

1.1 内连接

1.1.1 隐式内连接

看不到 JOIN 关键字，条件使用 WHERE 指定

语法

#表1与表2中的列名x所表示的数据相同
select 字段列表 from 表名1, 表名2 where 表名1.列名x=表名2.列名x
#学生表的班级编号=班级表中的班级编号
select * from student,class where student.classid=class.classid

自连接 / 全连接

在信息查询时需要进行对自身连接(自连接)，在自连接时需要使用表别名。
虽然两个表别名，但是同一张表。定义别名的目的是更方便在自身进行筛选。

# 查询"张三"的其他同班同学
select s1.studentname from syudent s1,student s2 where s1.studentid=s2.studentid and s2.studentname="张三"

1.1.2 显式内连接

使用inner join ... on ...进行联结。(inner可省略)
用inner join指定两个表之间的关系，联结条件写于on之后。
尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

语法

select 字段列表 from 表名1 [inner] join 表名2 on 条件

1.2 外连接

1.2.1 左连接

左外连接是以左边的表为主，右边的表为辅，查询的是左表所有数据以及其交集部分。

语法

select 字段列表 from 表1 left [outer] join 表2 on 条件

1.2.2 右连接

右外连接是以右边的表为主，左边的表为辅，查询的是右表所有数据以及其交集部分。

语法

select 字段列表 from 表1 right [outer] join 表2 on 条件

1.3 子查询

查询中嵌套查询，称嵌套查询为子查询。

子查询的结果是单行单列的

子查询可以作为条件，使用运算符去判断。运算符： > >= < <= =

# 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

子查询的结果是多行单列的

子查询可以作为条件，使用运算符in来判断

# 查询'财务部'和'市场部'所有的员工信息
# 普通查询
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
# 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

子查询的结果是多行多列的

子查询可以作为一张虚拟表参与查询

# 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
#子查询
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
# 普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'

二、组合查询

2.1 概念

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询。

使用 union或者union all操作符来组合数条SQL查询。利用union或者union all，可给出多条select语句，将它们的结果组合成单个结果集。

注意：两个要联合的SQL语句字段个数必须一样，而且字段类型要“相容”（一致）；

2.2 union

2.2.1 语法

union(或称为联合)的作用是将多个结果合并在一起显示出来。
union：会对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序。

[SQL 语句 1]
UNION
[SQL 语句 2]

2.2.2 union规则

UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔（因此，如果组合4条SELECT 语句，将要使用3个 UNION 关键字）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）

如果遵守了这些基本规则或限制，则可以将并用于任何数据检索任务

2.2.3 案例

第一条SQL：select检索价格不高于5的所有物品

select vend_id, prod_id , prod_price from products where prod_price <= 5;

在这里插入图片描述

第二天SQL：select使用in找出供应商1001和1002生产的所有物品

select vend_id , prod_id , prod_price from products where vend_id in(1001,1002)

在这里插入图片描述

合并：需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品

select vend_id, prod_id , prod_price from products where prod_price <= 5;
union 
select vend_id , prod_id , prod_price from products where vend_id in(1001,1002)

在这里插入图片描述

2.3 union all

union all是直接连接，取到得是所有值，记录可能有重复
union all：对两个结果集进行并集操作，包括重复行，不会对结果进行排序。

2.3.1 语法

[SQL 语句 1]
UNION ALL
[SQL 语句 2]

2.3.2 案例

select vend_id, prod_id , prod_price from products where prod_price <= 5 
 union all
select vend_id , prod_id , prod_price from products where vend_id in(1001,1002)

在这里插入图片描述

三、视图

3.1 概念

视图与每次sql语句查询的结果虚拟表类似，只不过与普通的虚拟表不同，视图可以通过创建的形式保存虚拟表，并使用视图把其当做一张已存在的表进行使用（视图表包含的是已存在表的动态数据，所以对存在表数据操作，视图的数据也会修改）

通常的做法是将经常使用的多表的关联创建视图进行使用

3.2 视图的规则和限制

与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
对于可以创建的视图数目没有限制。
为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
order by可以用在视图中，但如果从该视图检索数据select中也含有order by，那么该视图中的order by将被覆盖。
视图不能索引，也不能有关联的触发器或默认值。
视图可以和表一起使用。例如，编写一条联结表和视图的select语句。

四、约束

4.1 概念

对表中的数据进行限定，保证数据的正确性、有效性和完整性。

4.2 分类

主键约束：primary key
非空约束：not null
唯一约束：unique
外键约束：foreign key

4.3 主键约束(PRIMARY KEY)

主键约束是通过PRIMARY KEY定义的，它可以唯一标识表中的记录。

4.3.1 主键

将主键作为一张表中所有行数据的唯一标识符。
主键值必须唯一，可以在创建表的时候定义，也可以在表创建后再创建
主键只能使用不允许NULL值的列。

分类

单字段主键
单字段主键指的是由一个字段构成的主键
多字段主键（复合主键）
多字段主键指的是多个字段组合而成的主键

# 在创建表时，添加主键约束
create table stu(
	id int primary key,-- 给id添加主键约束
	name varchar(20)
);
create table stu(
	id int ,
	name varchar(20),
	primary key (id)-- 给id添加主键约束
);
# 创建完表后，添加主键
alter table stu modify id int primary key;

auto_increment 主键自动递增

如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长。
本列每当增加一行时自动增量。
每次执行一个insert操作时，MySQL自动对该列增量。
每个表只允许一个auto_increment列，而且它必须被索引指定默认值

#创建表时设置自动递增
create table `test`.`emp`  (
  `empid` int(5) not null auto_increment,
  primary key (`empid`)
);

# 添加自动增长
create table `emp`  (
  `empid` int(5) not null,
  primary key (`empid`)
);
alter table emp change empid empid int not null auto_increment;

#重置自动递增的初始值
alter table emp AUTO_INCREMENT = 1;

# 删除自动增长
alter table emp change empid empid int not null;

comment

设置注解

CREATE TABLE `emp`  (
  `empid` int(5) NOT NULL COMMENT '雇员id',
  `ename` varchar(15) NOT NULL COMMENT '雇员姓名',
  `age` int(2) NULL COMMENT '年龄，23-65 之间',
  `phone` varchar(11) NOT NULL COMMENT '手机号，这里使用了 varchar类型',
  `email` varchar(30) NOT NULL COMMENT '公司的电子邮箱',
  PRIMARY KEY (`empid`)
);

4.3 非空约束(NOT NULL)

非空约束指的是字段的值不能为NULL
非空约束通过NOT NULL定义。

# 创建表时添加约束
create table stu(
	id int,
	name varchar(20) not null -- name为非空
);

# 创建表完后，添加非空约束
create table stu modify name varchar(20) not null;

# 删除name的非空约束
create table stu modify name varchar(20);

4.4 唯一约束(UNIQUE)

唯一约束通过unique来定义，值不能重复
唯一约束用于保存数据表中字段的唯一性
一个表可以有多个唯一约束字段
注意：mysql中，唯一约束限定的列的值可以有多个null

语法

# 创建表时，添加唯一约束
create table stu(
	id int,
	phone_number varchar(20) unique -- 添加了唯一约束
);

# 删除唯一约束
alter table stu drop index phone_number;

# 在创建表后，添加唯一约束
alter table stu modify phone_number varchar(20) unique;

4.5 外键约束(FOREIGN KEY)

用foreign key定义外键。
外键既是某表中的一列，也是另一个表的主键。定义了两个表的关系。
一个表允许有多个外键，且外键只适用于InnoDB表。

语法

# 在创建表时，设置外键
create table 表名(
	....
	外键列
	constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);

# 删除外键
alter table 表名 drop foreign key 外键名称;

#创建表之后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);

4.6 默认约束(DEFAULT)

默认约束用于给数据表中的字段指定默认值，即当在表中插入一条新纪录时，如果没有给这个字段赋值，那么，数据库系统会自动为这个字段插入默认值。
默认值是通过DEFAULT关键字定义的**default**。
MySQL不允许使用函数作为默认值，只支持常量。
个表可以有多个默认约束字段

user_age int(2) not null default 18,

4.7 级联操作

添加级联操作
- 语法：

alter table 表名 add constraint  外键名称 
					foreign key (外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade;

分类：
- 级联更新：ON UPDATE CASCADE
- 级联删除：ON DELETE CASCADE

每日一点点进步
不进则退

璃尔 °

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
多表查询、组合查询和约束

这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入欢迎使用Markdown编辑器你好！这是你第一次使用 Markdown编辑器所展示的欢迎页。如果你想学习如何使用Mar
复制链接

扫一扫