数据库的完整性
用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性
确保数据的完整性 = 在创建表时给表中添加约束
建议：
这些约束应该在创建表的时候设置
多个约束条件之间使用空格间隔
完整性的分类
实体完整性(行完整性):
实体：即表中的一行(一条记录)代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复。
约束类型：
*主键约束（primary key）*
注：每个表中要有一个主键。 特点：数据唯一，且不能为null
*唯一约束(unique)*
特点：数据不能重复。
*自动增长列(auto_increment)*
给主键添加自动增长的数值，列只能是整数类型
sqlserver数据库 (identity-标识列) ；oracle数据库(sequence-序列)
域完整性(列完整性):
域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较
域代表当前单元格
域完整性约束：
数据类型
| 数值类型 | ||||
| 类型 | 大小 | 范围（有符号） | 范围（无符号 ） | 用途 |
| 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是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、 MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。 有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的 最大长度和存储需求。 |
||
非空约束（not null）
默认值约束(default)
check约束（mysql不支持）
引用完整性(关联表完整性):
（参照完整性）
外键约束：FOREIGN KEY
语法：constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
外键列的数据类型一定要与主键的类型一致
多表查询
多表约束：外键列
多表的关系
一对多/多对一关系
一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.
多对多关系
多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一 方的主键.
一对一关系
在实际的开发中应用不多.因为一对一可以创建成一张表.
两种建表原则：
唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique.
主键对应：让一对一的双方的主键进行建立关系.
多表查询
1. 合并结果集:UNION 、 UNION ALL
作用：合并结果集就是把两个select语句的查询结果合并到一起
合并结果集有两种方式：
UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；
UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。
注意：被合并的两个结果：列数、列类型必须相同。
2. 连接查询
内连接 [INNER] JOIN ON
// 语法 select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名 //外键列的关系 where..... // 等价于 select 列名 from 表1,表2 where 表1.列名=表2.列名 and ...(其他条件) 注: <1>表1和表2的顺序可以互换 <2>找两张表的等值关系时,找表示相同含义的列作为等值关系。 <3>点操作符表示“的”,格式:表名.列名 <4>可以使用as,给表名起别名,注意定义别名之后,统一使用别名 // 三表联查： // 语法： 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.列名 // 标准的内连接为： SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno; 内连接的特点：查询结果必须满足条件。
外连接 OUTER JOIN ON
-左外连接 LEFT [OUTER] JOIN select 列名 from 主表 left join 次表 on 主表.列名=次表.列名 主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null 主表和次表不能随意调换位置 使用场景:一般会作为子查询的语句使用 - 右外连接 RIGHT [OUTER] JOIN select 列名 from 次表 right join 主表 on 主表.列名=次表.列名 先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。 - 全外连接（MySQL不支持）FULL JOIN 连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。 通常连接查询不可能需要 整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多 数情况下都是使用主外键关系去除。 两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，去除无用笛卡尔积，使用主外键关系作为条件来处理。 如果两 张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。
自然连接 NATURAL JOIN
自然连接是一种特殊的等值连接，他要求两个关系表中进行连
接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。
select * from emp e natural join dept d;
3.子查询
一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含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 限定条件
示例：update employee e,salary s set title='助理',salary=1000 where e.empid=s.empid and name='李四'
多表删除
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='人事部'
日期运算函数
now() 获得当前系统时间
year(日期值) 获得日期值中的年份
date_add(日期,interval 计算值 计算的字段);
注：计算值大于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 来连接条件，如果一个字段有索引，一个字段没有索引，将导致 引擎放弃使用索引而进行全表扫描，如：
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 是一个好的选择
posted on
浙公网安备 33010602011771号