实验三 数据库完整性 (头歌)

实验三 数据库完整性 (头歌)

制作不易！点个关注！给大家创造更多的价值！

第一关：定义s表完整性

相关知识

MySQL约束概述

在 MySQL 中，约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。

例如，在数据表中存放年龄的值时，如果存入 200、300 这些无效的值就毫无意义了。因此，使用约束来限定表中的数据范围是很有必要的。

主键约束

主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。
主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。
1）设置单字段主键
在 CREATE TABLE 语句中，通过 PRIMARY KEY 关键字来指定主键。
在定义字段的同时指定主键，语法格式如下：
<字段名> <数据类型> PRIMARY KEY [默认值]
例 1
在 test_db 数据库中创建 tb_emp3 数据表，其主键为 id，SQL 语句和运行结果如下。

mysql> CREATE TABLE tb_emp3
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT
    -> );

或者是在定义完所有字段之后指定主键，语法格式如下：
[CONSTRAINT <约束名>] PRIMARY KEY [字段名]
例 2
在 test_db 数据库中创建 tb_emp4 数据表，其主键为 id，SQL 语句和运行结果如下。

mysql> CREATE TABLE tb_emp4
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp4;

2）在创建表时设置联合主键
所谓的联合主键，就是这个主键是由一张表中多个字段组成的。

比如，设置学生选课数据表时，使用学生编号做主键还是用课程编号做主键呢？如果用学生编号做主键，那么一个学生就只能选择一门课程。如果用课程编号做主键，那么一门课程只能有一个学生来选。显然，这两种情况都是不符合实际情况的。

实际上设计学生选课表，要限定的是一个学生只能选择同一课程一次。因此，学生编号和课程编号可以放在一起共同作为主键，这也就是联合主键了。

主键由多个字段联合组成，语法格式如下：
PRIMARY KEY [字段1，字段2，…,字段n]

注意：当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。
例 3
创建数据表 tb_emp5，假设表中没有主键 id，为了唯一确定一个员工，可以把 name、deptId 联合起来作为主键，SQL 语句和运行结果如下。

mysql> CREATE TABLE tb_emp5
    -> (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(name,deptId)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_emp5;

非空约束

非空约束用来约束表中的字段不能为空。例如，在学生信息表中，如果不添加学生姓名，那么这条记录是没有用的。

创建表时可以使用 NOT NULL 关键字设置非空约束，具体的语法格式如下：
<字段名> <数据类型> NOT NULL;

例如：创建数据表 tb_dept4，指定部门名称不能为空，SQL 语句和运行结果如下所示。

mysql> CREATE TABLE tb_dept4
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.37 sec)

默认值约束

默认值约束用来约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。

例如，在注册学生信息时，如果不输入学生的性别，那么会默认设置一个性别或者输入一个“未知”。

默认值约束通常用在已经设置了非空约束的列，这样能够防止数据表在录入数据时出现错误。

创建表时可以使用 DEFAULT 关键字设置默认值约束，具体的语法格式如下：
<字段名> <数据类型> DEFAULT <默认值>;

其中，“默认值”为该字段设置的默认值，如果是字符类型的，要用单引号括起来。

例如：创建数据表 tb_dept3，指定部门位置默认为 Beijing，SQL 语句和运行结果如下所示。

mysql> CREATE TABLE tb_dept3
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22),
    -> location VARCHAR(50) DEFAULT 'Beijing'
    -> );
Query OK, 0 rows affected (0.37 sec)

查看表中的约束

在 MySQL 中可以使用 SHOW CREATE TABLE 语句来查看表中的约束。

查看数据表中的约束语法格式如下：
SHOW CREATE TABLE <数据表名>;

例如：创建数据表 tb_emp8 并指定 id 为主键约束，name 为唯一约束，deptId 为非空约束和外键约束，然后查看表中的约束，SQL 语句运行结果如下。

mysql> CREATE TABLE tb_emp8
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) UNIQUE,
    -> deptId INT(11) NOT NULL,
    -> salary FLOAT DEFAULT 0,
    -> CHECK(salary>0),
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> SHOW CREATE TABLE tb_emp8 \G
*************************** 1. row ***************************
       Table: tb_emp8
Create Table: CREATE TABLE `tb_emp8` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `deptId` int(11) NOT NULL,
  `salary` float DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `deptId` (`deptId`),
  CONSTRAINT `tb_emp8_ibfk_1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.19 sec)

编程要求

请按下面s表的结构定义完整性； sno主码，sname非空、city缺省值为天津。

create table s(
sno char(2),
sname varchar(10),
status int,
city varchar(10)
);

create table s(
  sno char(2)  PRIMARY KEY,
  sname varchar(10) not null,
  status int,
  city varchar(10) DEFAULT '天津'
);
 

第二关：定义p表完整性

相关知识

MySQL检查约束（CHECK）

MySQL 检查约束（CHECK）是用来检查数据表中字段值有效性的一种手段，可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。设置检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入。

创建表时设置检查约束
语法格式如下：
CHECK(<检查约束>)

例如：在 test_db 数据库中创建 tb_emp7 数据表，要求 salary 字段值大于 0 且小于 10000，SQL 语句和运行结果如下所示。

mysql> CREATE TABLE tb_emp7
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25