数据库复习

复习之前本科学过的数据库内容，直接从连接查询开始复习吧！

目录

一、连接查询

1.1 什么是连接查询？

1.2 连接类型

1.2.1 内连接

1.2.2 自连接

1.2.3 外连接

2.1 内连接

2.1.1 等值连接

2.1.2 非等值连接 

2.1.3 自连接

2.2 外连接

2.2.1 左外连接

2.3 三张表怎么连接查询？

---

一、连接查询

1.1 什么是连接查询？

连接查询是关系数据库中最主要的查询，主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。 在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，然后通过连接进行查询。

1.2 连接类型

内连接的连接查询结果集中仅包含满足条件的行，内连接是SQL Server缺省的连接方式，可以把INNERJOIN简写成JOIN，根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种；交叉连接的连接查询结果集中包含两个表中所有行的组合；外连接的连接查询结果集中既包含那些满足条件的行，还包含其中某个表的全部行，有3种形式的外连接：左外连接、右外连接、全外连接。

1.2.1 内连接

内连接是一种最常用的连接类型。内连接查询实际上是一种任意条件的查询。使用内连接时，如果两个表的相关字段满足连接条件，就从这两个表中提取数据并组合成新的记录，也就是在内连接查询中，只有满足条件的元组才能出现在结果关系中。

例如：要查询每个已经选课的学生的情况，查询语句为

SELECT*

FROM学生表INNER JOIN选课表ON学生表.学号=选课表.学号

分类：

根据比较方式分为：

1）等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

2）不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

3）自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

1.2.2 自连接

如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询就称为自连接查询。同一张表在FROM字句中多次出现，为了区别该表的每一次出现，需要为表定义一个别名。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

例如：要求检索出学号为20210的学生的同班同学的信息，查询语句为

SELECT学生表.*

FROM学生表JOIN学生表AS学生表1ON学生表.班级=学生表1.班级

WHERE学生表1.学号='20210'

1.2.3 外连接

内连接的查询结果都是满足连接条件的元组。但有时我们也希望输出那些不满足连接条件的元组信息。比如，我们想知道每个学生的选课情况，包括已经选课的学生（这部分学生的学号在学生表中有，在选课表中也有，是满足连接条件的），也包括没有选课的学生（这部分学生的学号在学生表中有，但在选课表中没有，不满足连接条件），这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件的连接方式。3种外连接：

1)左外连接（LEFTOUTER JOIN）

如果在连接查询中，连接管子左端的表中所有的元组都列出来，并且能在右端的表中找到匹配的元组，那么连接成功。如果在右端的表中，没能找到匹配的元组，那么对应的元组是空值（NULL）。这时，查询语句使用关键字LEFT OUTERJOIN，也就是说，左外连接的含义是限制连接关键字右端的表中的数据必须满足连接条件，而不关左端的表中的数据是否满足连接条件，均输出左端表中的内容。

例如：要查询所有学生的选课情况，包括已经选课的和还没有选课的学生，查询语句为

SELECT学生表.学号，姓名，班级，课程号，成绩

FROM学生表LEFT OUTER JOIN选课表ON学生表.学号=选课表.学号

左外连接查询中左端表中的所有元组的信息都得到了保留。

2）右外连接（RIGHTOUTERJOIN）

右外连接与左外连接类似，只是右端表中的所有元组都列出，限制左端表的数据必须满足连接条件，而不管右端表中的数据是否满足连接条件，均输出表中的内容。

例如：同上例内容，查询语句为

SELECT学生表.学号，姓名，班级，课程号，成绩

FROM学生表RIGHTOUTERJOIN选课表ON学生表.学号=选课表.学号

右外连接查询中右端表中的所有元组的信息都得到了保留。

3）全外连接（FULL OUTER JOIN）

全外连接查询的特点是左、右两端表中的元组都输出，如果没能找到匹配的元组，就使用NULL来代替。

例如：同左外连接例子内容，查询语句为

SELECT学生表.学号，姓名，班级，课程号，成绩

FROM学生表FULL OUTER JOIN选课表ON学生表.学号=选课表.学号

全外连接查询中所有表中的元组信息都得到了保留。

2.1 内连接

2.1.1 等值连接

最大特点：条件是等量关系！
eg1:查询每个员工的部门名称，要求显示出员工名和部门名。

首先我们看到这个例子的时候，就知道了最终要查询的数据是员工名和部门名。

（部门表）

（员工表）

从这两张图可知，员工名是在emp表上的， 部门名是在dept表上的，所以我们要从这两张表上去把数据拿到，使用等值连接的方式来拿。

 使用命令

SELECT e.ename,d.DNAME FROM emp e join dept d on e.DEPTNO=d.DEPTNO;

从这个命令中就可以看出为什么这个查询方式叫做等值连接了，因为我们最终查询的条件，就是最后那个等式，通过在员工表中姓名所对应的部门号，与部门表中部门号相连接，查询到部门名。

2.1.2 非等值连接 

最大特点：连接条件中的关系是非等量关系！
eg2:找出每个员工的工资等级，要求显示员工名、工资、工资等级.

我们首先查看每个员工的工资

 再查看工资等级对应的区间

 命令

SELECT e.ename,e.SAL,s.GRADE FROM emp e JOIN salgrade s on e.SAL BETWEEN s.LOSAL and s.HISAL;

从这个命令就可以看出查询的这个条件不是等量的，而是使用了between and 这个区间来查询。

2.1.3 自连接

最大特点：一张表看作两张表，自己连自己！

eg3:找出每个员工的上级领导，要求显示员工名和对应的领导名。

首先查找出每个员工的上级领导：

再查看领导到底是哪几位，将其单独看作一个表，由下图可看出领导是哪几位。

 代码如下：

SELECT a.ename as 员工名,b.ename as 领导名 from emp a join emp b on a.MGR=b.EMPNO;

这个案例的自连接的核心思想在于怎么巧妙地把一张表看成两张表，这个思想很重要，因为每个员工的领导都有一个领导号，但是每个领导再牛逼也是公司的员工吧，所以这个领导号也是这个领导的员工号，所以核心就在于员工的领导编号=领导的员工编号。

2.2 外连接

2.2.1 左外连接

依旧使用上一个例子来举例，你会发现上一个例子是13行，这一个例子是14行，这是为什么呢，这就是左连接。因为左边这个emp a表是主表，就算外连接以后查不到跟他相匹配的值，副表emp b也会自动模拟出null与之匹配。

代码如下：

select a.ename as 员工 ,b.ename as 领导 from emp a left join emp b on a.mgr=b.empno;

 右连接同理，自行推理。

在以后的实际使用中，外连接居多，需要熟练掌握！

案例：找出哪个部门没有员工？

代码如下

SELECT d.* from emp e RIGHT join dept d on e.deptno=d.DEPTNO where e.ENAME is null ;

 全连接使用较少。

2.3 三张表怎么连接查询？
 

案例：找出每一个员工的部门名称和工资等级。

  

 代码如下：

select e.ename as 员工名,d.dname as 部门名,s.grade as 薪资等级 from emp e join dept d on e.DEPTNO =d.DEPTNO join salgrade s on e.sal BETWEEN s.LOSAL and s.HISAL;

进阶案例：

找出每一个员工的部门名称和工资等级以及上级领导。（此时必须使用外连接，因为king的等级是最高的，他没有上级领导，但是查询结果必须有他，只不过为null，但是他必须存在，这就是数据查询的严谨性！）

查询结果如图：

代码如下：

select e.ename as 员工名,d.dname as 部门名,s.grade as 薪资等级, e1.ename as 上级领导 from emp e join dept d on e.DEPTNO =d.DEPTNO join salgrade s on e.sal BETWEEN s.LOSAL and s.HISAL left join emp e1 on e.mgr=e1.empno;

二、子查询

概念：select语句中嵌套select语句，被嵌套的select语句是子查询！

1.1 where子句中使用子查询

案例：找出高于平均薪资的员工。

第一步：找出平均薪资

第二步：用where条件过滤

综上，两个语句合并可以得知：

代码如下：

select * from emp where sal>(SELECT avg(sal) from emp);

1.2 from后面嵌套子查询

案例：找出每个部门的平均薪水的薪资等级。

第一步：找出每个部门的平均薪水。

代码段：

select DEPTNO,avg(sal) from emp  GROUP BY DEPTNO;

 第二步：将以上的查询结果当作临时表t，让t表与salgrade  s表连接。条件是t.avg(sal)between s,losal and s.hisal.(一定要掌握，这个非常重要，在以后的开发中会大量使用到！)

代码如下：

select t.*,GRADE from (select DEPTNO,avg(sal) as ave from emp  GROUP BY DEPTNO) t join salgrade s where t.ave BETWEEN s.LOSAl and s.HISAL;

案例：找出每个部门的平均薪资等级

第一步：找出每个员工的薪水等级

代码：

select e.ename ,e.sal,DEPTNO,s.GRADE from emp e join salgrade s where e.sal BETWEEN s.LOSAL and s.HISAL;

 第二步：基于以上结果，继续按照deptno分组，然后用分组函数求出平均值即可。

代码段：

select DEPTNO,avg(GRADE) from (select e.ename ,e.sal,DEPTNO,s.GRADE from emp e join salgrade s where e.sal BETWEEN s.LOSAL and s.HISAL) t  GROUP BY DEPTNO;

1.3  union(可以将查询结果集相加)

案例：找出工作岗位是salesman和MANAGER的员工。

代码如下：

select e.ename,e.job from emp e where e.job='salesman' union select e.ename,e.job from emp e where job='manager';

 1.4 limit(重点中的重点，分页查询的核心)

limit是mysql特有的，其他数据库没有，不通用。

语法机制：

limit startindex,length。 startindex表示起始位置，Length表示取几个。

案例：取出工资前五名的员工。（思路：降序取前面五个）

代码： 

select e.ename,e.sal from emp e  ORDER  BY sal DESC limit 0,5;

Limit是SQL语句最后执行的一个环节。

三、对表的操作

3.1 创建表

建表语句的语法格式:
create table 表名(
字段名1
数据类型,
字段名2
数据类型,
字段名3数据类型,
);

3.1.1 MySQL常用数据类型（重点）
 

整数型

数据类型	字节数
TINYINT	1	-128	127
INT	4	-2147483648	2147483647
BIGINT	8	-9223372036854775808	9223372036854775807

浮点型

	字节数		范围
float	4	单精度浮点型	最小非零值：±1.175494351E – 38
double	8	双精度浮点型	最小非零值：±2.2250738585072014E – 308
Decimal	8	定点数的精度	可变；其值的范围依赖于M 和D

字符串

	大小	用途	说明
CHAR	0-255字节	定长字符串	编码方式有关
VARCHAR	0-65535 字节	变长字符串	编码方式有关
TEXT	0-64kb	长文本数据	M>500，通常会使用文本字符串,varchar也会自动转为text

时间类型

		格式
date	3	yyyy-MM-dd	存储日期值
time	3	HH:mm:ss	存储时分秒
year	1	yyyy	存储年
datetime	8	yyyy-MM-dd HH:mm:ss	存储日期+时间
timestamp	4	yyyy-MM-dd HH:mm:ss	存储日期+时间，可作时间戳

char和vchar怎么选择？
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如:性别、生日等都是采用char.
当一个字段的数据长度不确定，例如:简介、姓名等都是采用varchar.

3.1.2 建表语句

create table t student (
no bigint,
name varchar (255) ,
sex char (1) ,
classno varchar (255) ,
birth char (10)
);

3.1.3  对数据进行操作

（1）insert语句插入数据

语法格式：

insert into表名(字段名1,字段名2,字段名3,....) values(值1,值2, 值3....)
要求:字段的数量和值的数量相同，并且数据类型要对应相同。
注：当一条insert语句执行成功之后，表格当中必然会多一行记录。
即使多的这一行记录当中某些字段是NULL,后期也没有办法在执行
insert语句插入数据了，只能使用update进行更新。
具体语法较简单，不在这里一一记录。

（2）update语句修改数据

语法格式：

update 表名 set 字段名1=值1,字段名2=值2... where条件;
注意:
没有条件整张表数据全部更新。
案例：将部门号为10的部门名改为renshibu，部门地点改为shanghai

 

代码段：

update dept set loc='shanghai',dname= 'renshibu' where DEPTNO=10;

 (3)delete语句删除数据

语法格式:
delete from 表名where条件;
注意:没有条件全部删除。
(4)约束

常见的约束类型：
非空约束（not null）：约束字段不能为空（null）

唯一约束（unique）：约束的字段不能重复

主键约束（primary key）：约束的字段不能为null，也不能重复

外键约束（foreign key）：

检查约束（check key）mysql没有检查约束，但是oracle有检查约束

主键约束：

主键是一个表中能标识唯一行的标志（也有其他方法表示唯一行，如唯一列）。

主键主要用在查询单调数据，修改单调数据和删除单调数据上。

一般做程序的时候，都将表的主键设置为int型的可自增的列，这样在编程的时候，很容易区分数据。

删除：delete from [表名] where id=1;

修改：update [表名] set col1='test' where id=1;

查询：select * from [表名] where id=1;

外键约束（很重要）：

MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。比如，一个水果摊，只有苹果、桃子、李子、西瓜等 4 种水果，那么，你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜，其它的水果都是不能购买的。

主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

定义外键时，需要遵守下列规则：

• 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
• 必须为主表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表的主键中列的数目相同。
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

四、事务

4.1 什么是事务

定义：数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

性质：

1、原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么全部不执行。 

2、一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序 串行执行的结果相一致。

3、隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。

4、持久性(Durability):对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。