Java学习笔记13

数据完整性

用来保证存放到数据库中的数据是有效的，即数据的有效性和准确性
确保数据的完整性 = 在创建表时给表中添加约束
完整性的分类：

1. 实体完整性：行完整性
2. 域完整性：列完整性
3. 引用完整性：关联表完整性
   1. 主键约束：primary key
   2. 唯一约束：unique [key]
   3. 非空约束：not null
   4. 默认约束：default（默认值）
   5. 自动增长：auto_increment
   6. 外键约束：foregin key
      建议在创建表的时候设置约束
      多个约束之间使用空格间隔

实体完整性

实体：即表中的一行数据（一条记录）代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复
约束类型：

1. 主键约束（primary key）
2. 唯一约束（unique）
3. 自动增长列（auto_increment）

主键约束（primary key）

每个表要有一个主键！！！
特点：数据唯一 且不能为空

第一种添加方式

create table 表名(列名 数据类型 primary key);

第二种添加方式

优势！！！：可以创建联合主键

create table 表名(列名 数据类型,primary key(`主键列名`));

第三种添加方式

create table 表名(列名 数据类型);
alter table 表名 add primary key(主键列名);

唯一约束（unique）

特点：数据不能重复，允许一次为空

create table 表名(列名 数据类型 unique);

自增长约束（auto_increment）

sqlserver数据库：identity(标识列)
oracle数据库：sequence（序列）
给主键添加自动增长的数值，列只能是整数类型

create table 表名(列名 数据类型 auto_increment);

域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其他单元格比较
域代表当前单元格
域完整性约束：数据类型、非空约束、默认值约束、check约束（MySQL不支持） check（sex=‘男’ or sex = ‘女’）

数据类型

数值类型

tinyint 1字节 -128-127 无符号表示0-255
smallint 2字节 -32768-32767 无符号表示0-65535
mediumint 3字节
int 4字节
bigint 8字节
float 4字节
double 8字节

日期类型

表示时间和日期的类型为datetime、date、timestamp、time、year
每个时间类型有一个有效值范围和零值，当只当不合法的 MySQL不支持的 值 时，使用零值
timestamp：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字节

字符串类型

char、varchar、binary、varbinary、blob、text、enum、set
char 0-255字节 定长字符串
varchar 0-65535字节 变长字符串
tinyblob 0-255字节 不超过255个字符的二进制字符串
tinytext 0-255字节 短文本字符串
blob 0-65535字节 二进制形式的长文本数据
text 0-65535字节 长文本数据
mediumblob 二进制形式的中等长度文本数据
mediutext 中等长度文本数据
longblob 二进制形式的极大文本数据
longtext 极大文本数据

区别

1. char和varchar类型类似，但他们保存和检索的方式不同，他们的最大长度和尾部空格是否保留也不同，在存储或检索的过程中不进行大小写转换

非空约束

not null

create table 表名(列名 数据类型 not null);

默认值约束

default

create table 表名(列名 数据类型 default 默认值);

引用完整性

外键约束，表与表之间的完整性约束
foreing key

第一种添加外键方式

create table 表名(
	列名 数据类型 primary key
	constraint fk_score_sid(自定义的外键名称) foreign key(外键列名) references 主键表名(主键列名)
);

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

第二种添加外键方式

alter table 被修改的表名 add constraint 自定义的外键名 foreign key(外键列名) references 主键表名(主键列名);

外键列：存储别的表的主键值 的列

多表查询

多表约束：外键列

多表的关系

一对多/多对一关系

在创建外键约束的时候，应尽量减少表内的重复数据，以此选择合适的外键创建
一对多建表原则：在多的一方创建一个字段，字段作为外键指向 多对一中一的一方的主键

多对多关系

多对多建表原则：需要创建第三张表，中间表至少两个字段，这两个字段分别作为外键指向各自一方的主键

一对一关系

一对一关系有两种建表原则

1. 唯一外键对应：假设一对一是 一个一对多的关系，在多的一方创建一个外键，指向一对一中 一 的一方的主键，将外键设置为unique
2. 主键对应：让一对一的双方的主键建立关系

多表查询

多表查询有以下几种：

1. 合并结果集：UNION、UNION ALL

UNION：去除重复记录
UNION ALL：不去除重复记录

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

---

2. 连接查询

连接查询就是求出多个表的乘积、连接查询会产生***笛卡尔积***
如何在笛卡尔积数的查询结果中，获取想要的数据
！使用主外键关系作为条件来去除无用信息

---

2.1内连接也叫内连查询：[INNER] JOIN ON

！！！注意：表1.列名和表2.列名的列，是同一列，意味着使用相同含义的列作为等之关系
！！！注意：表1和表2顺序可以互换
方法1

select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名 where 条件

方法2

select 列名 from 表1，表2 where 表1.列名=表2.列名 and （其他条件）

2.2外连接也叫外联查询：OUTER JOIN ON

查询出的结果存在不满足条件的可能

外连接又分

1. 左连接： LEFT [OUTER] JOIN

select 列名 from 主表 left join 次表 on 主表.列名=次表.列名

主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功显示null
主表和次表不能随意调换位置
2. 右连接：RIGHT [OUTER] JOIN

select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

3. 全连接：mysql不支持 FULL JOIN

select * from 表1 natural join 表2 

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

2.3自然连接：NATURAL JOIN

---

3. 子查询

一个select语句中包含另一个完整的select语句
子查询就是嵌套查询
子查询出现的位置：

• where后，作为条件的一部分
• from后，作为表

当子查询出现在where后作为条件时，可以使用

• any
• all

子查询结果集的形式：

• 单行单列（用于条件）
• 单行多列（用于条件）
• 多行单列（用于条件）
• 多行多列（用于表）

---

扩展

多行数据新增

insert into 表名（列名）values（列值），（列值）

多表数据更新

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

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

多表数据删除

delete 被删除的表 from 删除操作中使用的表 where 限定条件

日期运算函数

now（）获得当前系统时间
year（日期值）获得日期值中的年份
date_add（日期，interval 计算值 计算的字段）！计算值大于0表示往后推日期，小于0表示往前推日期

优化

1. 对查询进行优化，要尽量避免全表扫描，首先应该考虑在where以及order by 涉及的列上，建立索引
2. 应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引 而进行 全表扫描 比如：select id from 表名 where 列名 is not null。最好不要给数据库留null，尽可能的使用not null填充数据库
3. 应尽量避免在where子句中使用 != 或 < >操作符，否则，引擎将放弃使用索引，而进行全表扫描
4. 应尽量避免在where子句中使用or 来连接条件。如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
5. in和not in 也要慎用，否则会导致全表扫描。对于连续数值，能用between就不要用in。很多时候，用exists代替in也是一种好的选择

数据库留null，尽可能的使用not null填充数据库
3. 应尽量避免在where子句中使用 != 或 < >操作符，否则，引擎将放弃使用索引，而进行全表扫描
4. 应尽量避免在where子句中使用or 来连接条件。如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
5. in和not in 也要慎用，否则会导致全表扫描。对于连续数值，能用between就不要用in。很多时候，用exists代替in也是一种好的选择