文理学院实验报告
实验名称：修改数据表与添加数据完整性约束
课程名称：数据库系统
实验项目：修改数据表与添加数据完整性约束
一、实验目的
- 安装MySQL客户机并连接MySQL服务器
- 创建数据库表（重点）
- 修改数据库表
- 数据完整性约束（难点）
二、实验概论
- 创建数据表
create table 表
(字段名1 类型 [约束条件],
字段名2 类型 [约束条件],
......
)其它选项(例如存储引擎、字符集等选项);
- 修改表结构
（1） 对表添加字段：
alter table 表名 add 新字段名 数据类型 [新约束条件] [ first | after 旧字段名];
（2） 对表修改字段名：
alter table 表名 change 旧字段名 新字段名 数据类型
（3） 对表修改字段的数据类型：
alter table 表名 modify 字段名 数据类型
（4） 对表删除字段：
alter table 表名 drop 字段名
- 修改表名
rename table旧表名 to 新表名
- 表的约束
alter table 表名add constraint 约束名 约束类型(字段名)
三、实验设备、材料
安装了MySQL和navicat的主机
Navicat软件链接：
百度网盘链接：https://pan.baidu.com/s/1Zv6OgwDSRWw44-jufedX8Q?pwd=zyfd
提取码：zyfd
四、实验步骤
1.直接使用“Navicat”完成以下建库建表练习
（1）创建一个名为“Product”的数据库：新建查询–>输入创建数据库语句
CREATE DATABASE Product;
（2）刷新“Navicat”的树状列表，此时会看见已经创建出该数据库
（3）重新选择“product”数据库，展开树状列表找到“查询”，点击“新建查询”
（4）在该数据库中添加如下数据表：
①Product（产品表），该表包括如下字段：
ProductID（产品编号），char类型（长度为5），不允许为空
ProductName（产品名称），varchar类型（长度为50），不允许为空
Price（单价），decimal类型（长度为8，小数点后保留2位），不允许为空
Stocks（库存量），int类型，不允许为空
T****ype（规格），varchar类型（长度为100），可为空
MySQL代码如下：
在MySQL的"Product"数据库中创建名为"Product"的数据表（表格）并定义所需的字段：
CREATE TABLE Product(
ProductID CHAR(5) NOT NULL,
ProductName VARCHAR(50) NOT NULL,
price DECIMAL(8,2) NOT NULL,
Stocks int NOT NULL,
Type VARCHAR(100)
);
创建一个名为Product的数据库以及一个包含几个字段的表格：
-
CREATE DATABASE Product: 这条命令创建了一个名为Product的新数据库。这是数据库设计的第一步，用于组织数据。 -
CREATE TABLE Product(: 开始创建Product表的定义。 -
ProductID CHAR(5) NOT NULL: 定义了ProductID字段，它是一个长度为5的字符型（CHAR）数据，不允许为空（NOT NULL）。 -
ProductName VARCHAR(50) NOT NULL:ProductName字段允许存储最多50个字符的字符串，同样不能为空。 -
price DECIMAL(8,2) NOT NULL:price字段是十进制数，精度为8位，其中2位小数，不允许有空值。 -
Stocks int NOT NULL:Stocks字段是整型（int）数据，用于存储库存量，不允许为空。 -
Type VARCHAR(100): 类型字段可存储最长100个字符的文本信息。 -
)结束字段定义，然后用;结束整个表的创建语句。
②Customer（客户表），该表包括如下字段：
CustomerID（客户编号），char类型（长度为6），不允许为空
CustomerName（客户姓名），varchar类型（长度为20），不允许为空
Region（地区），varchar类型（长度为20），不允许为空
Charger（负责人），varchar类型（长度为20），不允许为空
Tel（手机号），char类型（长度为11），不允许为空
MySQL代码如下：
在MySQL中创建Customer表的SQL语句：
CREATE TABLE Customer(
CustomerID char(6) NOT NULL,
CustomerName VARCHAR(20) NOT NULL,
Region VARCHAR(20) NOT NULL,
Charger VARCHAR(20) NOT NULL,
Tel char(11) NOT NULL
);
这里我们定义了5个字段：
CustomerID：主键，用于唯一标识每个客户，不允许有空值。CustomerName：存储客户的姓名，最大长度为20字符，不能为空。Region：存储客户所在的地区，同样最大长度为20字符，不允许空值。Charger：存储负责人的名字，长度限制也为20字符，不能为空。Tel：存储客户的电话号码，格式为11位数字，不能为空。
③Sales（产品销售表），该表包括如下字段：
SalesID（销售编号），int类型，主键，自动编号，不允许为空（提示：主键拼写为primary key，自动编号拼写为auto_increment）
ProductID（产品编号），char类型（长度为5），不允许为空
CustomerID（客户编号），char类型（长度为6），不允许为空
SalesTime（销售时间），datetime类型，不允许为空
Amount（数量），int类型，不允许为空
SalesPrice（销售额），decimal类型（长度为8，小数点后保留2位），不允许为空
在MySQL中创建名为"Sales"的产品销售表的SQL语句如下：
CREATE TABLE Sales(
SalesId INT AUTO_INCREMENT PRIMARY KEY,
ProductId char(5) NOT NULL,
CustomerId char(6) NOT NULL,
SalesTime DATETIME NOT NULL,
Amount int NOT NULL,
SalesPrice DECIMAL(8,2) NOT NULL
);
这个命令解释如下：
CREATE TABLE用于创建新的表格。Sales是表格名。SalesID,ProductID,CustomerID,SalesTime,Amount, 和SalesPrice分别对应字段名。INT、CHAR(5)、CHAR(6)、DATETIME、INT和DECIMAL(8, 2)分别是对应的字段类型。NOT NULL表示这些字段都不允许有空值。PRIMARY KEY指定了SalesID为主键，同时设置AUTO_INCREMENT使其自增。
2.对表添加约束练习（注意复制创建代码到实训报告，标注题号）：
（1）主键约束：
①对于Product表，将ProductID设置为主键约束，且自增。
alter table product add PRIMARY key(ProductID);
添加了一个新的 PRIMARY KEY 约束到 “ProductID” 字段上。PRIMARY KEY 是一个数据库表中的关键约束，意味着 “ProductID” 字段现在将作为表的唯一标识，不允许有重复值，并且不能为空.
alter table product MODIFY ProductID INT auto_increment;
这条SQL命令是用来修改名为"product"的数据表结构的。它将ProductID列的数据类型从原来的某个非自动增长整数类型改为INT，并设置为自动递增(Auto Increment)。这意味着每次向产品表中插入新行时，ProductID字段会自动填充一个新的、唯一的整数值，无需手动指定。这在数据库设计中常用于主键字段，保证了数据的唯一性和完整性。
②对于Customer表，将CustomerID设置为主键约束，且自增。
alter table customer add PRIMARY key(CustomerID);
这在customer表中声明了一个主键（PRIMARY KEY）约束，指定CustomerID字段作为主键。主键的目的是确保每一行数据都是唯一的，并且不允许为空。这意味着CustomerID字段在整个表中不再允许有任何重复值，同时，对于插入新记录时，自动自增功能会被覆盖，因为主键通常需要一个确定的、唯一的标识符。
alter table customer MODIFY CustomerID INT auto_increment;
这句话改变了CustomerID字段的数据类型为整数（INT），并启用了自动增长（auto_increment）。这意味着每次向表中插入新的记录时，CustomerID字段的值会自动递增，无需手动指定。这通常用于满足主键的唯一性和顺序性的需求。
（2）外键约束
①对于Sales表，创建外键约束，使得该表中的ProductID字段成为外键，主键来源于Product表中的ProductID字段。
ALTER TABLE sales
ADD CONSTRAINT product
FOREIGN KEY (productID)
REFERENCES product(ProductID);
用于修改数据库表sales中的CustomerID字段的数据类型。这里将该字段从原来的某种数据类型改为了整数类型INT。这意味着后续对该字段的操作和存储都将按照INT类型的要求进行。
②对于Sales表，创建外键约束，使得该表中的CustomerID字段成为外键，主键来源于Customer表中的CustomerID字段。
ALTER table sales
ADD CONSTRAINT customer
FOREIGN KEY(CustomerId)
REFERENCES customer(CustomerID);
（3）唯一约束：
对于Customer表，创建唯一约束，使得该表中的CustomerName字段成为唯一值。
ALTER TABLE customer ADD CONSTRAINT cust UNIQUE (CustomerName);
3.对数据表结构进行修改
（1）对数据表Customer添加一个新的字段“Sex”（用于存放性别），char类型（长度为2），可为空。
ALTER TABLE Customer
ADD COLUMN Sex CHAR(2) NULL;
（2）重命名字段：将Customer表中的“Charger”字段改名为“Controller”，varchar类型（长度为30）。
ALTER TABLE Customer
CHANGE COLUMN Charger Controller VARCHAR(30);
（3）修改列的字段类型：将Customer表中的“CustomerName”字段类型改为varchar类型（长度为30）
ALTER TABLE Customer
MODIFY COLUMN CustomerName VARCHAR(30);
（4）删除数据表Product中的Type字段。
ALTER TABLE Product
DROP COLUMN Type;
（5）导出product数据库代码。
-- --------------------------------------------------------
-- 主机: 127.0.0.1
-- 服务器版本: 5.7.44-log - MySQL Community Server (GPL)
-- 服务器操作系统: Win32
-- HeidiSQL 版本: 12.8.0.6908
-- --------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
-- 导出 product 的数据库结构
CREATE DATABASE IF NOT EXISTS `product` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `product`;
-- 导出 表 product.customer 结构
CREATE TABLE IF NOT EXISTS `customer` (
`CustomerID` char(50) NOT NULL DEFAULT 'AUTO_INCREMENT',
`CustomerName` varchar(30) DEFAULT NULL,
`Region` varchar(20) NOT NULL,
`Controller` varchar(30) DEFAULT NULL,
`Tel` char(11) NOT NULL,
`Sex` char(2) DEFAULT NULL,
PRIMARY KEY (`CustomerID`),
UNIQUE KEY `cust` (`CustomerName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- 正在导出表 product.customer 的数据：~0 rows (大约)
-- 导出 表 product.product 结构
CREATE TABLE IF NOT EXISTS `product` (
`ProductID` char(50) NOT NULL DEFAULT 'AUTO_INCREMENT',
`ProductName` varchar(50) NOT NULL,
`price` decimal(8,2) NOT NULL,
`Stocks` int(11) NOT NULL,
PRIMARY KEY (`ProductID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- 正在导出表 product.product 的数据：~0 rows (大约)
-- 导出 表 product.sales 结构
CREATE TABLE IF NOT EXISTS `sales` (
`SalesId` int(11) NOT NULL AUTO_INCREMENT,
`ProductId` char(50) NOT NULL,
`CustomerId` char(50) NOT NULL,
`SalesTime` datetime NOT NULL,
`Amount` int(11) NOT NULL,
`SalesPrice` decimal(8,2) NOT NULL,
PRIMARY KEY (`SalesId`),
KEY `product` (`ProductId`),
KEY `customer` (`CustomerId`),
CONSTRAINT `customer` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`CustomerID`),
CONSTRAINT `product` FOREIGN KEY (`ProductId`) REFERENCES `product` (`ProductID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- 正在导出表 product.sales 的数据：~0 rows (大约)
/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;
4.删除约束条件
（1）删除Sales表的两个外键约束
ALTER table sales DROP FOREIGN KEY ProductId；
ALTER table sales DROP FOREIGN KEY CustomerId；
（2）删除Product、Customer的主键约束
ALTER table product DROP Primary KEY
ALTER table customer DROP Primary KEY
完整SQL代码（有错误，自辨）
CREATE TABLE Product(
ProductID CHAR(5) NOT NULL,
ProductName VARCHAR(50) NOT NULL,
price DECIMAL(8,2) NOT NULL,
Stocks int NOT NULL,
Type VARCHAR(100)
);
CREATE TABLE Customer(
CustomerID char(6) NOT NULL,
CustomerName VARCHAR(20) NOT NULL,
Region VARCHAR(20) NOT NULL,
Charger VARCHAR(20) NOT NULL,
Tel char(11) NOT NULL
);
CREATE TABLE Sales(
SalesId INT AUTO_INCREMENT PRIMARY KEY,
ProductId char(5) NOT NULL,
CustomerId char(6) NOT NULL,
SalesTime DATETIME NOT NULL,
Amount int NOT NULL,
SalesPrice DECIMAL(8,2) NOT NULL
);
alter table product add PRIMARY key(ProductID);
alter table product MODIFY ProductID INT auto_increment;
alter table customer add PRIMARY key(CustomerID);
alter table customer MODIFY CustomerID INT auto_increment;
alter table sales MODIFY CustomerId INT
ALTER TABLE sales
ADD CONSTRAINT product
FOREIGN KEY (productID)
REFERENCES product(ProductID);
ALTER table sales
ADD CONSTRAINT customer
FOREIGN KEY(CustomerId)
REFERENCES customer(CustomerID);
ALTER TABLE customer ADD CONSTRAINT cust UNIQUE (CustomerName);
ALTER TABLE Customer
ADD COLUMN Sex CHAR(2) NULL;
ALTER TABLE Customer
CHANGE COLUMN Charger Controller VARCHAR(30);
ALTER TABLE Customer
MODIFY COLUMN CustomerName VARCHAR(30);
ALTER TABLE Product
DROP COLUMN Type;
ALTER table sales
DROP FOREIGN KEY ProductId；
ALTER table sales
DROP FOREIGN KEY CustomerId；
ALTER table product DROP Primary KEY;
ALTER table customer DROP Primary KEY
附：结果数据库源码
/*
Navicat Premium Data Transfer
Source Server : 本机MySQL8.0
Source Server Type : MySQL
Source Server Version : 80038
Source Host : localhost:3306
Source Schema : product
Target Server Type : MySQL
Target Server Version : 80038
File Encoding : 65001
Date: 27/09/2024 23:48:28
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`CustomerID` int(0) NOT NULL AUTO_INCREMENT,
`CustomerName` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`Region` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`Controller` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`Tel` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`Sex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`CustomerID`) USING BTREE,
UNIQUE INDEX `cust`(`CustomerName`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`ProductID` int(0) NOT NULL AUTO_INCREMENT,
`ProductName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`price` decimal(8, 2) NOT NULL,
`Stocks` int(0) NOT NULL,
PRIMARY KEY (`ProductID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for sales
-- ----------------------------
DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
`SalesId` int(0) NOT NULL AUTO_INCREMENT,
`ProductId` char(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`CustomerId` int(0) NULL DEFAULT NULL,
`SalesTime` datetime(0) NOT NULL,
`Amount` int(0) NOT NULL,
`SalesPrice` decimal(8, 2) NOT NULL,
PRIMARY KEY (`SalesId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
扫一扫
1211
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
