MySQL数据管理二

一，数据完整性

数据库中的数据是从外界输入的，而数据的输入由于种种原因，会发生输入无效或错误信息。保证输入的数据符合规定，成为了数据库系统，尤其是多用户的关系数据库系统首要关注的问题。
它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据完整性 = 数据的精确性 + 数据的可靠性

 数据完整性主要分为三类：实体完整性、域完整性、引用完整性。

1.实体完整性：实体完整性是指保证表中所有的航唯一。

2.域完整性：域完整性是指数据库表中的列必须满足某种特定的数据类型或约束。其中约束又包括取值范围、精度等规定。

3.引用完整性：引用完整性是指保证主关键字（被引用表）和外部关键字（引用表）之间的参照关系。它涉及两个或两个以上表数据的一致性维护。

二，使用约束实现数据完整性

数据库采用多种方法来保证数据完整性，包括约束，规则和触发器。

 三，在数据表上添加约束

语法：

ALTER TABLE 表名 

ADD CONSTRAINT 约束名  约束关键字  具体的约束说明

 约束名的取名推荐采用：约束类型_约束字段
主键（Primary Key）约束：如 PK_stuNo
唯一（Unique ）约束：如 UQ_stuID
外键(Foreign Key)约束：如 FK_stuNo 

四，设置唯一约束

使用场景：该字段的值是唯一的。

语法：

ALTER TABLE 表名 

ADD CONSTRAINT 约束名  UNIQUE(列名)

 示例：

ALTER TABLE student
ADD CONSTRAINT uq_stuid  UNIQUE(identitycard) 

五，主键（primary key）

1.数据库主键：指的是一个列或多列的组合，其值能唯一地标识表中的每一行，通过它可强制表的实体完整性。
数据库主键的作用：
（1）保证实体的完整性;
（2）加快数据库的操作速度；
（3）添加新记录时，DBMS会自动检查新记录的主键值，不允许该值与其他记录的主键值重复；
（4）DBMS自动按主键值的顺序显示记录。如果没有定义主键，则按输入记录的顺序显示记录。

 1：一个表只能有一个主键，并且主键列不允许出现空值。

2：尽管有的表中允许没有主键，但是通常情况下，建议为表设置主键。

2.数据库主键的选择规则
1）最少性是指列数最少的键。如果可以从单个主键和复合主键中选择，应该选择单个主键，这是因为操作一列比操作多列要快。当然该规则也有例外，例如，两个整数类型的列的组合比一个很大的字符类型的列操作要快。
2）稳定性是指列中数据的特征。由于主键通常用来在两个表之间建立联系，所以主键的数据不能经常更新。理想情况下，应该永远不变。 

如果两列或多列组合起来唯一地标识表中的每个行，则该主键叫做“复合主键”。

 3.设置主键约束

使用场景：如果一个字段即要求唯一又不能为null，可使用主键约束。

语法：

ALTER TABLE 表名  
ADD CONSTRAINT 约束名  PRIMARY KEY(列名1,列名2,…列名n)

示例：

ALTER TABLE student
ADD CONSTRAINT pk_studentno  PRIMARY KEY (studentno) 

六，外键

在数据库设计中，学生的信息和学生的考试成绩是存放在不同的数据表中。在成绩表中，可以存储学生的学号来表示是哪个学生的考试成绩，这又引发一个问题：“如果成绩表中输入的学号根本不存在或者把学号写错了” ，该怎么办 ？
这个时候，就应当建立一种“引用”的关系，确保“从表”中的某个数据项在“主表”中必须存在，以避免上述错误发生。
“外键”就是用来达到这个目的的，它是相对于主键而言的，就是“从表”中对应于“主表”中的列，在从表中称为外键或引用键，它的值要求与主表的主键相对应。
“外键”用来强制引用完整性。一个表可以有多个外键。

1.设置外键约束

语法：

ALTER TABLE 从表名
ADD CONSTRAINT 约束名  FOREIGN KEY(从表的列名)
REFERENCES 主表名(主表的列名) 

示例：

ALTER TABLE result
ADD CONSTRAINT fk_studentno FOREIGN KEY(studentno)
REFERENCES student(studentno)

1.主表中的字段必须为主键。
2.从表中的外键列和主表中的主键列的数据类型要保持一致，和字段名无关。
3.主表和从表的表类型为InnoDB。

 七，删除约束

语法：

ALTER TABLE 表名  DROP PRIMARY KEY;   #删除主键约束
ALTER TABLE 表名  DROP KEY 唯一约束名;  #删除唯一约束
ALTER TABLE 表名  DROP FOREIGN KEY 外键约束名;  #删除外键约束

示例：

ALTER TABLE student 
DROP FOREIGN KEY fk_studentno

使用场景：如果错误地添加了约束，还可以删除约束 。

八，DML语言

 DML（数据操作语言）：用于操作数据库对象中所包含的数据。
INSERT  ( 添加数据语句 )
UPDATE ( 更新数据语句 ) 
DELETE  ( 删除数据语句 )

1.添加数据（插入单行）

INSERT INTO  表名  [ ( 字段1, 字段2, 字段3, … ) ]  VALUES  ( 值1, 值2, 值3, …);

1.表的字段是可选的，如果省略，则依次插入所有的字段。
2.如果插入的是表中部分列的数据，字段名列表必须填写。
3.多个字段和多个值之间使用逗号分隔。
4.值列表必须和字段名列表数量相同且数据类型相符（字符串和日期类型的值要加单引号）。
5.值列表中的数据必须符合数据完整性的要求。

2.插入多行数据

INSERT INTO  表名  [ ( 字段1, 字段2, 字段3, … ) ]  VALUES  ( 值1, 值2, 值3, …)，( 值1, 值2, 值3, …),…;

 3.将查询结果插入到新表中

CREATE TABLE  新表名  ( SELECT 字段1, 字段2, 字段3, … FROM 原表)

以上示例在执行查询操作的同时创建新表stuhistory，无须提前创建。

4.修改数据（update命令）

语法：

 UPDATE   表名   
 SET   column_name = value  [ ,  column_name2 = value2, …. ]  [ WHERE   condition ];

1.column_name 为要更改的字段名。
2.value 为修改后的数据，可以为变量、具体值、表达式或者嵌套的SELECT结果。
3.多个字段之间用逗号隔开。
4.提供的修改数据必须符合数据完整性的要求。
5.condition为筛选条件，如不指定则修改该表的所有列数据。

5.删除数据（delete命令）

1.语法：

 DELETE FROM   表名   [ WHERE   condition ];

1.DELETE语句删除的是整条记录，不会只删除单个列。
2.condition为筛选条件，如不指定则修改该表的所有列数据。

2.truncate table命令

语法：

TRUNCATE TABLE   表名;

1.TRUNCATE TABLE 删除表中所有的行。
2.TRUNCATE TABLE不能用于有外键约束引用的表。

 九，SQL运算符

1.算数运算符

 2.比较运算符

3.逻辑运算符