数据完整性

完整性

  • 完整性作用：
    保证数据的有效性和准确性
  • 分类：
    实体完整性(行完整性)
    域完整性(列完整性)
    引用完整性(关联表完整性)
  • 约束：

  • image.png确保数据的完整性，需要在创建表时给表中添加约束

    1.  多个约束条件之间**使用空格间隔**

示例：
create table student(
studentno int primary key auto_increment,
loginPwd varchar(20) not null default ‘123456’,
studentname varchar(50) not null,
sex char(2) not null,
gradeid int not null,
phone varchar(255) not null,
address varchar(255) default ‘学生宿舍’,
borndate datetime,
email varchar(50)
);

实体完整性(行完整性)

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

主键约束（primary key）

每个表中至少有一个主键

特点：数据非空且唯一

添加主键约束：

1、建表时直接添加

CREATE TABLE student( id int primary key, name varchar(50) );

2、建表的最后直接添加

CREATE TABLE student( id int, name varchar(50), primary key(id) );

可添加联合主键

CREATE TABLE student( classid int, stuid int, name varchar(50), primary
key(classid,stuid) );

3、使用alter语句

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

唯一约束(unique)

特点：数据不重复
添加唯一约束：
CREATE TABLE student( Id int primary key, Name varchar(50) unique );

自动增长列(auto_increment)

给主键添加自动增长的数值，列只能是整数类型

CREATETABLE student( Id int primary key auto_increment, Name varchar(50));
INSERT INTO student(name) values(‘tom’);

域完整性(列完整性)

  • 域：
    当前单元格

  • 作用：
    限制此单元格的数据正确，不对照此列的其它单元格比较

  • 约束类型：
    非空约束（not null）
    默认值约束(default)
    check约束【check(sex=‘男’ or sex=‘女’)】（mysql不支持）

非空约束

关键词：not null

添加非空约束：

CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sex varchar(10) );
INSERT INTO student values(1,’tom’,null);

默认值约束

关键字：default

添加默认值约束：

CREATE TABLE student( Id int primary key, Name varchar(50) not null, Sex varchar(10) default ‘男’ );
insert into student1 values(1,’tom’,’女’);
insert into student1 values(2,’jerry’,default); #此时默认为男

引用完整性(关联表完整性)

  • 约束类型：
    外键约束（FOREIGN KEY）

    外键约束

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

constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
添加外键约束：
1、直接添加

CREATE TABLE student(id int primary key, name varchar(50) not null, sex varchar(10) default ‘男’ );
create table score(
id int primary key,
score int,
sid int ,
constraint fk_score_sid foreign key(sid) references student(id) );

2、使用alter语句添加
ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES stu(id);

多表查询

多个表之间是有关系的，那么关系靠谁来维护? 多表约束：外键列

多表关系

四种关系：
1.一对多关系
2.多对一关系
3.多对多关系
4.一对一关系

一对多/多对一关系

在多（例如：订单）的一方创建一个字段，字段(列)作为外键指向一（例如：客户）的一方的主键列
客户和订单：一个客户可以有多个订单 （一对多）
多个订单可以指向同一个客户（多对一）

多对多关系

  • 建表原则：
    需要创建第三张表，至少两个字段，这两个字段分别作为外键指向各自一方的主键
  • 例如：
    学生和课程

一对一关系

  • 建表原则：
    唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique
    主键对应：让一对一的双方的主键进行建立关系

多表查询

多个表之间是有关系的，那么多个表之间需要进行多表约束，即通过关键列

一、 合并结果集

  • 作用：
    把两个select语句的查询结果合并到一起
  • tips：
    被合并的两个结果：列数、列数据类型必须相同

UNION 、 UNION ALL

image.png
ELECT FROM t1 UNION SELECT FROM t2；

二、 连接查询

笛卡尔积：
假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

2.1 内连接

关键字： [INNER] JOIN ON

tips：
表的顺序可以互换
找两张表表示相同含义的列作为等值关系。
表名.列名
使用as给表名起别名（定义别名之后统一使用别名）

代码示例:
查询学生表中的学生姓名和分数表中的分数
select name,score
from student s inner join scores c
on s.studentid=c.stuid；

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

三表联查：
select 列名 from 表1
inner join 表2 on 表1.列名=表2.列名
inner join 表3 on 表1或表2.列名=表3.（表一和表二有关系，表二和表三有关系，但是表一和表三不一定有关系）

等价于:

select 列名 from 表1,表2,表3
where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名

[

](https://blog.csdn.net/m0_50609545/article/details/119540291)

2.2 外连接

关键字：OUTER JOIN ON


  • 特点
    查询出的结果存在不满足条件的可能
  • tips
    主表数据全部显示，次表数据匹配显示，匹配成功显示数据，匹配不成功显示null
    主表和次表不能随意调换位置

左外连接
关键字： LEFT [OUTER] JOIN
先查询出左表（即以左表为主表），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL
select 列名 from 主表 left join 次表 on 主表.列名=次表.列名；

右外连接
关键字：RIGHT [OUTER] JOIN
先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL
select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

自然连接

关键字：NATURAL INNER JOIN
一种特殊的等值连接，要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列
代码示例：
select * from emp e natural join dept d

三、 子查询

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

第四章:扩展

多表更新

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

示例：update employee e,salary s set title=’助工’,salary=1000 where e.empid=s.empid and name=’李四’ ；
把李四调味助攻并且工资为1000；

多表删除

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

注:多张表之间使用逗号间隔
示例：
//删除人事部相关所有的信息
delete d,e,s from department d,employee e,salary s
where d.depid=e.depid and s.empid=e.empid and depname=’人事部’

第五章：数据库优化

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 来连接条件，如果一个字段有索引，一个字段没有索引，将导致 引擎放弃使用索引而进行全表扫描，

  1. in 和 not in 也要慎用，否则会导致全表扫描
    对于连续的数值，能用 between 就不要用 in 了

[

](https://blog.csdn.net/m0_50609545/article/details/119540291)

[

](https://blog.csdn.net/m0_50609545/article/details/119540291)

[

](https://blog.csdn.net/m0_50609545/article/details/119540291)