数据库高级

数据库初级在这里！！https://blog.csdn.net/m0_60563771/article/details/123638612?spm=1001.2014.3001.5501

目录

数据库的完整性

一、实体完整性

 二、域完整性

 三、引用完整性

 四、navicat添加约束

五、多表查询 

 六、一对一

七、多表查询

八、扩展

 九、数据库优化（重点，面试爱问）

十、数据库的导入导出

---

数据库的完整性

为了保证存放到数据库中的数据是有效的,即数据的有效性和准确性，我们需要在创建表时给数据约束。

完整性的分类：
        - 实体完整性(行完整性):
        - 域完整性(列完整性):
        - 引用完整性(关联表完整性):
主键约束：primary key
唯一约束：unique [key]
非空约束：not null
默认约束：default
自动增长：auto_increment
外键约束: foreign key
这些约束应该在创建表的时候设置 ，多个约束之间用空格隔开

eg:studentno int primary key auto_increment,（其中primary key 是主键约束，auto_increament 是自动增长约束）

一、实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复。
约束类型：
*主键约束（primary key）*
*唯一约束(unique)*
*自动增长列(auto_increment)*

一、1：主键约束（primary key）

注：每个表中要有一个主键。
特点：数据唯一，且不能为null(比如说，不能用姓名作为主键，因为姓名可能重复，但是可以用学号，学号不会重复)

添加方式1：create table 表名(列名1 列数据类型1 主键约束 , 列名2 数据类型2等)；（常用）

添加方式2： create table 表名（列名1 列数据类型1，列名2 列数据类型2，列名n 列数据类型n，primary key(主键1，主键2) ）；（这种方式优势在于，可以创建联合主键：在一个表中有多个主键约束）

 添加方式3：创建好表后用alter去添加主键：（不常用）

eg：CREATE TABLE student( id int, name varchar(50) );
ALTER TABLE student ADD PRIMARY KEY (id);

二、唯一约束（unique）

 特点：数据不能重复，和主键约束很像。不同的是中间约束不允许为空，唯一约束允许一次为空。

 create table 表名(列名1 列数据类型1 约束1 , 列名2 数据类型2 unique )；unique 为唯一约束

三、自动增长列（auto_increment）（只能用于整数数据类型）

给某列加自增长约束，此列的值会自动增加，不用手动设置。

eg：给主键添加自增长的数值：

create table student2(id int primary key auto_increment,Name varchar(50));

查询结果：自动加了1、2、3、4（abc那一条我执行了两次） ，自己递增。

 

 二、域完整性

限制此单元格的数据准确，比如，邮箱的格式是必须正确的，人类的性别只能是男和女。

域完整性约束：数据类型 非空约束（not null） 默认值约束(default)
补充：check约束（mysql不支持）check(sex='男' or sex='女'

一、数据类型来约束

数值类型（了解）
| 类型 | 大小 | 范围（有符号） | 范围（无符号 ） | 用途 |
| ------------- | ---------------- | ----------------------- | ---------------------- | ---------- |
| 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) | 双精度浮点数值 |
日期类型:
表示时间值的日期和时间类型为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 | 混合日期和
时间值，时间戳 ,当更新数据的时候自动添加更新时间
字符串类型:
字符串类型指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字节 | 极大文本数据 |
CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB（0、1用来存音频视频等）是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、
MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的
最大长度和存储需求。

二、非空约束 not null（重点）

 如果我们给的数据，没有添加name则会报错：

 添加后，就不会报错：

三、默认值约束 default（重点）

 如果用户不赋值，那么系统给一个默认值:

student4 id为主键约束与自增长约束，姓名为非空约束，性别为默认值约束，默认为女

下面我们添加数据时，只添加了姓名，id自增长自动赋值，性别默认为女。 

 三、引用完整性

前面写到的实体完整性和域完整性都是关注单表存储的完整性。而引用完整性是关注表之间的完整性。

我们在开发中一般也会用到多张有相互关联的表。比如现有两张表：

表1为学生信息表，表中有：学生姓名，性别，年龄，学号（设为主键列）。

表2为学生成绩表，表中有：分数id值，学生id，科目，分数。

显然，这两张表中的数据是一一对应的关系。我们现在要将两张表建立关联。为了保证一一对应，我们应将表1的主键值引用成表2的列值。于是，我们让表2中的学生id这一列存入表1的学号，这样一个学号就对应表二中的一组数据。例如表2的stuid，第一行为2，对应表1就是“小红”的成绩！

但有一个问题：如果我们把表2的stuid改为5 或者 6 其实发现也不会报错：

其实这就是出现了引用完整性的问题。那么为了保证引用完整性，落实上面的操作，就要用到外键约束： foreign key

外键列：存其他表的主键值的列

 添加方式1：

创建完表后，修改添加：

alter table 被修改的表名 add constraint 自定义的外键约束名称  foreign key（外键列）references 关联表名（关联表的主键列）

此时，如果再在score的 stuid中，将数据改为student5的主键列中没有的数据时，就会报错：

 四、navicat添加约束

1、新建一张表，填入列名

2、添加主键：点击最后一列，有一个钥匙，则添加为了主键列，注意主键列不能为空，所以将“允许空值”选掉！

3、添加自增长约束:点击“id”列名，点击下方自动递增

 4、添加唯一约束：点击索引，自己编写唯一约束的名，点击栏位，选择要添加的列，索引类型选择unique即可。

 5、添加默认值：点击要添加默认值的列，然后点击下面“默认”输入即可：

6、添加外键列：现有两张表，表1为“哈哈哈”，内容如下：

 表2为“哈哈的关联表”，内容如下：

 添加外键列的步骤如下：

1、右键单击要添加外键列的表1，点设计表

 2、在要添加外键列的表1中添加一个新的列：fkid

 3、点击外键，填写相应的数据，保存即可（后两列，是保存后自动添加的，不用手动写）

 此时，再点开表1，外键列就有可以选择的数据了，表示已有外键约束。

五、多表查询 

 多表查询依然依靠外键约束来维护多表之间的关系。

多表的关系：

一对多/多对一（常见）：一个部门可以有多个员工（一对多），多个员工属于一个部门（多对一）。

此种情况的建表原则是：在多的一方创建一个字段，字段作为外键指向一的一方的主键。eg：上面的“哈哈哈”就可以看做是多的一方，而“哈哈的关联表”就是一的一方。

多对多：老师可以有多个学生，学生也可以有多个老师。

多对多的建表原则：需要创建第三张表，中间表至少包含两个字段，这两个字段分别作为外键指向其余两张表的主键。

eg：现有一张学生表：

一张老师表：

为了关联上面两张表的多对多关系，我们需要创建第三张表，取名为middle： 

接下来我们再向表中添加数据：stuid代表学生，teaid代表老师。

所以此表意思为：学生1小白，学了老师1、2、3的课，学生2小红学了老师1、3的课，以此类推。

那么反过来，也代表老师2魏老师有两个学生，分别为1小白和4小溪。

 我们在此案例中并没有创建外键约束，因为外键约束的优点是保证了数据的有效性，但是缺点是性能会降低。而开发过程中，数据量较大，使用外键列性能会有很大程度的降低。所以一般不建议使用外键约束，并且由于外键列的值是由我们程序员给定的，所以我们很清楚哪些值是正确的，哪些是错误的。所以可以不用创建外键列。

 六、一对一

一对一关系就像夫妻关系，一个丈夫只能配对一个妻子，那么就让双方的主键建立关系，将丈夫的主键设为妻子的外键，反之也可。或者是在一方中另外添加另一方的外键列，并加unique唯一约束。两种方法都可以。操作方法都和上面一样，我就不在此赘述了~

七、多表查询

多表查询有如下几种：
        1. 合并结果集:UNION 、 UNION ALL
        2. 连接查询（重点）
                2.1内连接 [INNER] JOIN ON
                2.2外连接 OUTER JOIN ON
                        -左外连接 LEFT [OUTER] JOIN
                        - 右外连接 RIGHT [OUTER] JOIN
                        - 全外连接（MySQL不支持）FULL JOIN
                2.3 自然连接 NATURAL JOIN
        3.子查询（重点）

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

现查询两张表：

其中重复数据有“小白”。 

 方法一：UNION：去除重复记录

eg：小白 只显示一次
方法二：UNION ALL：不去除重复记录

 eg：小白两次都显示

 

 二、连接查询

连接查询就是求出多个表的乘积，表1连接表2，查询结果就是：表1*表2。

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。两个表的数据一一匹配。

我们先创建两个表：emp1： dept1： 

 我们所谓的连接查询就是：select *from emp1,dept1;

得到的结果：

 可是很明显，我们的程序员的部门等数据都是错乱的，结果不合理，不是我们想要的。这时，我们就需要添加条件来过滤掉不想要的数据。

我们可以看到在雇员表中，是存在与部门表的关系列的，所以这就是我们要添加的限定条件！ 

 语法：select *from 表1，表2 where 限定条件：如下图蓝色部分

 这样查询出来的结果，就是正确的了！

查询部分信息：

语法：select 表1.列名，表2.列名 from 表1，表2 where 限定条件；

比如，我们要查询雇员姓名和所属部门名称。

 如图，显示的就是要查询的部分了。

说完去除无用信息之后，我们来介绍一下具体的连接方式：

一、内连接（内联查询）：

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

我们发现跟上面写的查询方法查询出来的结果相同，其实这个语法也只是上面那个写法的标准化而已。~其实内连接就是通过主外键来进行条件筛选，那么如果我们还想添加其他的条件怎么办？很简单，只要在后面加上where就可以继续添加条件啦！！

注:1、表1和表2的顺序可以互换
2、找两张表的等值关系时,找表示相同含义的列作为等值关系。 

3、有多表时，就是继续在inner join on 的后面继续inner join 表3 on 表1/表2.列名=表三.列名 

二、外连接（外联查询）

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

现在，我们把刚才的部门表添加一个数据：

 deptno 50是没有匹配的员工的，此时我们再使用内联查询是无法查询到部门aa的，因为内联查询只显示两者有关系的共同部分。那么如果我们想要显示所有的部门，此时就要用到外联查询。

左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
 1.主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null
 2.主表和次表不能随意调换位置 

此时，部门aa依然显示，只不过因为没有匹配数据，所以后面全为null。 

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

右外联查询的结果与左外联一致，只不过位置会发生改变：

 三、自然连接（了解）

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

我们可以看到自然连接查询到的结果将一个deptno删除了，这也就是它的特点：在结果中消除重复的属性列。 

 三、子查询（重要！）

 子查询就是：一个查询语句中包含另一个完整的查询语句。

子查询出现的位置：
1、 where后，作为条件为被查询的条件的一部分；
2、 from后；（我们知道from后面应该是表格，但是其实查询语句的结果就是一个虚拟表，所以子查询也会跟在from后面）

当子查询出现在where后作为条件时，还可以使用如下关键字：
1、 any
2、all 

 我们举个例子来理解子查询的应用：

例如刚刚的emp1表，我们要查询工资高于赵六的人的姓名。

此时，我们就应该把赵六的工资作为限定条件，但是得到赵六的工资要先查询到赵六的工资，此时这个查询语句就是“子查询”语句。 （括号中就是子查询语句）

SELECT emp1.` ename` from emp1 where emp1.` sal`>(SELECT emp1.` sal` from emp1 where emp1.` ename`='赵六');

结果：

 子查询没有什么书写顺序，按照需求，一步一步分析一步一步加条件即可。

八、扩展

一、多行新增：

insert into 表名(列名) values (列值1，列值2，、、),(列值1，列值2，、、),(列值1，列值2，、、)；

二、多表更新：

(1)update 表1,表2 set 列名  //（表1的表2的都可以）=列值  //（表1的表2的都可以） where 表1.列名=表2.列名 //两表关系 and 其他限定条件
(2)update 表1  inner join 表2 on 表1.列名=表2.列名 //两表关系  set 列名=列值
where 限定条件

eg：update student s,class c
set name='赵四',classip=1000
where s.stuid=c.claip and name='李四'

将李四的姓名改为赵四，课堂编号改为1000.

三、多表删除

语法：delete 被删除数据的表 from 删除操作中使用的表 where 限定条件
注:多张表之间使用逗号间隔

eg： //删除人事部的信息
delete d,e,s from department d,employee e,salary s where d.depid=e.depid and s.empid=e.empid and depname='人事部'

四、日期运算函数

now() 获得当前系统时间

year(日期值) 获得日期值中的年份
date_add(日期,interval 计算值 计算的字段);
注：计算值大于0表示往后推日期，小于0表示往前推日期

eg：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 是一个好的选择

十、数据库的导入导出

选中表后，选择转存sql文件或者导出向导都可以。

导入的话，直接将文件拖拽到数据库中即可，刷新后即可看到。