本文主要是介绍数据库高级，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一：数据完整性

一：数据库完整性

1. 用来保证放在数据库中的数据的有效性和准确性。

2. 确保数据的完整性 = 在创建表时给表中添加约束

3. 完整性的分类：
- 实体完整性(行完整性):
- 域完整性(列完整性):
- 引用完整性(关联表完整性):

主键约束：primary key
唯一约束：unique [key]
非空约束：not null
默认约束：default
自动增长：auto_increment
外键约束: foreign key

建议这些约束应该在创建表的时候设置，多个约束条件之间使用空格间隔

4. 实体完整性

4.1）实体完整性的作用：标识每一行数据不重复。
4.2）约束类型：
*主键约束（primary key）*
*唯一约束(unique)*
*自动增长列(auto_increment)*

4.2.1）主键约束（primary key）：每个表中要有一个主键，数据唯一，且不能为null

例一：第一种添加方式：
create table student(stuid varchar(10) primary key,name varchar(10));

第二种添加方式：此种方式优势在于，可以创建联合主键
create table student(stuid varchar(10),name varchar(10),primary key(stuid));

第三种添加方式：
create table student(id varchar(10),name varchar(10));
alter table student add primary key(stuid);

4.2.2) 唯一约束（unique）：数据不能重复
create table student(id varchar(10) primary key,name varchar(10) unique);
4.2.3) 自增长列（auto_increment) ：给主键添加自动增长的数值，列只能是整数
create table student(id varchar(10) primary key auto_increment,name varchar(10) unique);

5. 域完整性

5.1）限制此单元格的数据正确。
5.2）不对照此列的其它单元格比较域代表当前单元格
5.3）域完整性约束：数据类型 非空约束（not null) 默认约束（default)
5.3.1）非空约束（not null)
create table student(id varchar(10) primary key , name varchar910）unique not null,sex char(1));
INSERT INTO student values(1,’tom’,null);
5.3.2) 默认值约束（default)
create table student(id varchar(10) primary key , name varchar910）unique not null,sex char(1) default('男'));
insert intostudent1 values(1,'tom','女');
insert intostudent1 values(2,'jerry',default);

6.数据类型：

6.1）数值类型

| 类型 | 大小 | 范围（有符号） | 范围（无符号 ） | 用途 |
| ------------- | ---------------- | ----------------------- | ---------------------- | ---------- |
| tinyint | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| smallint | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| mediumint | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT| 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| bigint | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744
073 709 551 615) | 极大整数值 |
| float | 4 字节 | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402
823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度浮点数值 |
| double | 8 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，
(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4
E-308，1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |

6.2）日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性
| 类型 | 大小(字节) | 范围 | 格式 | 用途 |
| --------- | ------ | ---------------------------------------- | ------------------- | ------------ |
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日
期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 *2147483647* 秒，北京时间 *2038-1-
19 11:14:07*，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和
时间值，时间戳 ,当更新数据的时候自动添加更新时间

6.3）字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET
| 类型 | 大小 | 用途 |
| ---------- | ----------------- | ------------------ |
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据

6.3.1）CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等
方面也不同。在存储或检索过程中不进行大小写转换。
6.3.2）BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字
符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基
于列值字节的数值值。
6.3.3）BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、
MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
6.3.4）有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的
最大长度和存储需求

7.引用完整性

7.1）外键约束（foreign key)
7.2) 应用场景：比如有张学生表和成绩表

 

数据完整性

一：数据库完整性

1. 用来保证放在数据库中的数据的有效性和准确性。

2. 确保数据的完整性 = 在创建表时给表中添加约束

3. 完整性的分类：
- 实体完整性(行完整性):
- 域完整性(列完整性):
- 引用完整性(关联表完整性):

主键约束：primary key
唯一约束：unique [key]
非空约束：not null
默认约束：default
自动增长：auto_increment
外键约束: foreign key

建议这些约束应该在创建表的时候设置，多个约束条件之间使用空格间隔

4. 实体完整性

4.1）实体完整性的作用：标识每一行数据不重复。
4.2）约束类型：
*主键约束（primary key）*
*唯一约束(unique)*
*自动增长列(auto_increment)*

4.2.1）主键约束（primary key）：每个表中要有一个主键，数据唯一，且不能为null

例一：第一种添加方式：
create table student(stuid varchar(10) primary key,name varchar(10));

第二种添加方式：此种方式优势在于，可以创建联合主键
create table student(stuid varchar(10),name varchar(10),primary key(stuid));

第三种添加方式：
create table student(id varchar(10),name varchar(10));
alter table student add primary key(stuid);

4.2.2) 唯一约束（unique）：数据不能重复
create table student(id varchar(10) primary key,name varchar(10) unique);
4.2.3) 自增长列（auto_increment) ：给主键添加自动增长的数值，列只能是整数
create table student(id varchar(10) primary key auto_increment,name varchar(10) unique);

5. 域完整性

5.1）限制此单元格的数据正确。
5.2）不对照此列的其它单元格比较域代表当前单元格
5.3）域完整性约束：数据类型 非空约束（not null) 默认约束（default)
5.3.1）非空约束（not null)
create table student(id varchar(10) primary key , name varchar910）unique not null,sex char(1));
INSERT INTO student values(1,’tom’,null);
5.3.2) 默认值约束（default)
create table student(id varchar(10) primary key , name varchar910）unique not null,sex char(1) default('男'));
insert intostudent1 values(1,'tom','女');
insert intostudent1 values(2,'jerry',default);

6.数据类型：

6.1）数值类型

| 类型 | 大小 | 范围（有符号） | 范围（无符号 ） | 用途 |
| ------------- | ---------------- | ----------------------- | ---------------------- | ---------- |
| tinyint | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| smallint | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| mediumint | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT| 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| bigint | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744
073 709 551 615) | 极大整数值 |
| float | 4 字节 | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402
823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度浮点数值 |
| double | 8 字节 | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，
(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4
E-308，1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |

6.2）日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性
| 类型 | 大小(字节) | 范围 | 格式 | 用途 |
| --------- | ------ | ---------------------------------------- | ------------------- | ------------ |
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日
期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 *2147483647* 秒，北京时间 *2038-1-
19 11:14:07*，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和
时间值，时间戳 ,当更新数据的时候自动添加更新时间

6.3）字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET
| 类型 | 大小 | 用途 |
| ---------- | ----------------- | ------------------ |
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据

6.3.1）CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等
方面也不同。在存储或检索过程中不进行大小写转换。
6.3.2）BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字
符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基
于列值字节的数值值。
6.3.3）BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、
MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
6.3.4）有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的
最大长度和存储需求

7.引用完整性

 

7.1）外键约束（foreign key)
7.2) 应用场景：比如有张学生表和成绩表，在学生表中主键为stuno，将主键作为成绩表的列，这样通过stuno就可以知道这个成绩代表那个学生，假设有一天这个人改                             名字了，但是我们只用修改学生表就可以了，而不用修改其他表，所以很方便。

 

 

 7.3）案例：

create table student (

   stuname varchar(10) unique,

   sex char(1) default('男'),

   age int default('男'),

   stuno varchar(10) primary key

   );

create table score(

   sid varchar(10),

   subject varchar(10) ,

   score int 

  stuid varchar(10);

//第一种：constraint abc foreign key(stuid) references student(stuno);

   );

第二种添加外键列的方法：ALTER TABLE 被修改表的名字 ADD CONSTRAINT 自定义的外键列的约束名字 FOREIGN KEY(外键列) REFERENCES 关联表名(关联表的                                              主键列);  （外键列：存储别的表主键的列）

alter table score add constranint abc foreign key(stuid) references student(stuno);

外键列的数据类型一定要与主键的类型一致  

八：多表查询

一：多表的关系：
一对一 多对一/一对多 多对多

1. 多对一/一对多
1.1）客户和订单，分类和商品，部门和员工 等。
1.2）一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.

2. 多对多：
2.1）学生和课程
2.2）多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

3.一对一：
3.1）一夫一妻
3.2）唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique

4.多表查询：

4.1）合并结果集：union union all

4.2) 连接查询：
4.2.1）内连接：[inner] join on
4.2.2) 外连接：outer join on
-左外连接 left [outer] join
-右外连接 right [outer] join

4.2.3) 自然连接：natural join

4.3) 子查询

 

5.合并结果集

5.1）作用：合并结果集就是把两个select语句的查询结果合并到一起

5.2）合并结果集有两种方式
—— UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2
—— UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2

5.3）注意：被合并的两个结果：列数、列类型必须相同

6.连接查询

6.1）连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2

6.2）连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),
(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况

6.3）通过条件过滤，去除重复的，不想要的记录。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积

6.4）使用主外键关系做为条件来去除无用信息。SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

7.内连接

7.1）语法：select 列名
from 表1
inner join 表2
on 表1.列名=表2.列名 //外键列的关系 where.....

等价于：select 列名
from 表1,表2
where 表1.列名=表2.列名 and ...(其他条件)

7.2） 注:
<1>表1和表2的顺序可以互换
<2>找两张表的等值关系时,找表示相同含义的列作为等值关系。
<3>点操作符表示“的”,格式:表名.列名
<4>可以使用as,给表名起别名,注意定义别名之后,统一使用别名

7.3） 示例：
//查询学生表中的学生姓名和分数表中的分数
select name,score
from student as s
inner join scores as c
on s.studentid=c.stuid

等价于：
select name,score
from student as s,scores as c
where s.studentid=c.stuid

7.4）三表联查

语法：
select 列名 from 表1
inner join 表2 on 表1.列名=表2.列名
inner join 表3 on 表1或表2.列名=表3.列名 where
等价于:
select 列名
from 表1,表2,表3
where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名

7.5）SQL标准的内连接为：
SELECT *
FROM emp e
INNER JOIN dept d
ON e.deptno=d.deptno;

7.6）内连接的特点：查询结果必须满足条件

八：外连接

8.1）包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

8.2）外连接分为主表和次表。
-主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null
-主表和次表不能随意调换位置
-左外联，显示左边的所有数据，右外联显示右边的所有数据

8.3）语法：
---左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
使用场景:一般会作为子查询的语句使用
select depname,name
from(select e.*,d.depname from department d left join employee e on e.depid=d.depid ) aa
a where aa.name is null;

--- 右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

 

九：连接查询心得

9.1）连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询

9.2）通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录

9.3）这个条件大多数情况下都是使用主外键关系去除

9.4）两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系

9.5）以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两
张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件

十：自然连接

10.1）概念：自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列

十一：子查询

11.1）一个select语句中包含另一个完整的select语句。

11.2）如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了

11.3）子查询出现的位置：

--- where后，作为条为被查询的一条件的一部分；
当子查询出现在where后作为条件时，还可以使用如下关键字：
any： 一个 all：所有
--- from后，作表；

 

十二：多行新增

12.1  insert into 表名(列名) values (列值),(列值),(列值)

十三：多表更新：

13.1）语法：

(1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件

(2)update 表1
inner join 表2
on 表1.列名=表2.列名
set 列名=列值
where 限定条件

十四：多表删除

14.1）语法： delete 被删除数据的表 from 删除操作中使用的表
where 限定条件

14.2） 注:多张表之间使用逗号间隔

十五： 日期运算函数

15.1）now() 获得当前系统时间
year(日期值) 获得日期值中的年份
date_add(日期,interval 计算值 计算的字段);

15.2） 注：计算值大于0表示往后推日期，小于0表示往前推日期
示例：date_add(now(),interval -40 year);//40年前的日期

 

十六：数据库优化

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引   2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫 描，如：select id from t where num is null 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库. 备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。   3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。   4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：    select id from t where num=10 or Name = 'admin'    可以这样查询：    select id from t where num = 10    union all    select id from t where Name = 'admin'   5.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)。对于连续的数值，能用 between 就不要用 in 了：    select id from t where num between 1 and 3。很多时候用 exists 代替 in 是一个好的选择。

 

十七：导入导出数据库

 

结语：排版有点乱，但是知识结构完整，加油吧。

 

这篇关于数据库高级的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！