提示: 实验中创建表时所使用的CHECK字段必须在MySQL8.0.2版本以上才会生效
数据库实验一
实验内容
已知某网店数据库（spxs）具有客户表（Client）、商品表（Commidity）、订单表（order）及订单明细表（Order_detail）。数据库表结构定义如下：
image.png
image.png
image.png
image.png
完成下列任务：
创建数据库spxs。
创建数据表客户表（Client）、商品表（Commidity）、订单表（order）及订单明细表（Order_detail）。
按数据表定义建立数据表约束。
设计并输入数据如下：
image.png
image.png
image.png
image.png
实验程序
- 测试表
CREATE TABLE `test` (
id int,
PRIMARY KEY (`id`),
CHECK (id>8)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
- 测试表
CREATE TABLE `test` (
id VARCHAR(8),
PRIMARY KEY (`id`),
CHECK (id REGEXP '[0-9]{8}')
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
- 创建数据库spxs
CREATE DATABASE spxs;
- 创建数据表客户表 Client
CREATE TABLE `Client` (
Client_id VARCHAR(8) NOT NULL PRIMARY KEY,
Name_c VARCHAR(10),
Gender_c CHAR(1),
Mobile_c VARCHAR(11),
Opendate_d DATE,
CHECK (CLient_id REGEXP '[0-9]{8}')
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO Client VALUES
('04130001','王平',0,'13004130022','2010-09-01'),
('04130002','李丽',0,'13004130112','2010-10-01'),
('04130003','张玉龙',1,'13004130898','2011-10-01'),
('04130004','李晓峰',0,'13004230111','2011-10-02'),
('04130005','金龙路',1,'13004130444','2011-10-05'),
('04130006','刘晓明',0,'13004130999','2011-10-10'),
('04130007','李金华',0,'13004132323','2010-11-01');
- 创建数据表商品表 Commidity
CREATE TABLE `Commidity` (
Goods_id VARCHAR(10) NOT NULL PRIMARY KEY,
Gname_c VARCHAR(30),
CityforProduction_c VARCHAR(30),
Gtype_c CHAR(2),
Gcount_n int,
PriceforUnit_n decimal(8,2),
Discount_n decimal(4,2),
CHECK (Gcount_n >0 AND PriceforUnit_n>0 AND 0.1<=Discount_n <=1)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO Commidity VALUES
('10001','咖啡','沈阳',1,100,20.00,0.90),
('10002','绿茶','上海',1,200,3.00,1.00),
('10003','可乐','武汉',1,300,6.00,1.00),
('10004','酸奶','抚顺',1,2000,40.00,1.00),
('20001','毛巾','上海',2,1000,20.00,0.80),
('20002','香皂','上海',2,1000,12.00,1.00),
('20003','牙膏','长沙',2,2010,11.00,1.00),
('30001','方便面','沈阳',3,1000,5.00,1.00),
('30002','香肠','抚顺',3,2000,10.00,1.00),
('30003','面包','抚顺',3,1010,10.00,1.00);
- 创建数据表订单表 Order_wss
CREATE TABLE `Order_wss` (
Order_id CHAR(10) NOT NULL PRIMARY KEY,
Client_id VARCHAR(8),
Orderdate_d DATE,
Orderitems_n INT,
TotalPrice_n DECIMAL(8,2),
FOREIGN KEY(Client_id) REFERENCES Client(Client_id),
CHECK (Orderitems_n>=0 AND TotalPrice_n>=0)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO Order_wss VALUES
('100001','04130001','2019-09-09',3,80.00),
('100002','04130002','2019-10-09',4,129.00),
('100003','04130003','2019-10-19',4,206.00),
('100004','04130005','2019-10-29',4,200.00);
- 创建数据表订单明细表Order_detail
CREATE TABLE `Order_detail` (
Seqnum_n INT PRIMARY KEY,
Order_id CHAR(10) NOT NULL,
Goods_id VARCHAR(10),
PriceforUnit_n DECIMAL(8,2),
Discount_n DECIMAL(8,2),
Amount_n INT,
Price_n DECIMAL(8,2),
FOREIGN KEY(Order_id) REFERENCES Order_wss(Order_id),
FOREIGN KEY(Goods_id) REFERENCES Commidity(Goods_id),
CHECK (PriceforUnit_n>=0 AND 0.1<=Discount_n<=1 AND Amount_n>=0 AND Price_n>=0)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO Order_detail VALUES
(100,'100001','10001',20.00,0.90,3,54.00),
(101,'100001','10002',3.00,1.00,2,6.00),
(102,'100001','30002',10.00,1.00,2,20.00),
(103,'100002','30001',5.00,1.00,1,5.00),
(104,'100002','30003',10.00,1.00,2,20.00),
(105,'100002','20001',20.00,0.80,2,32.00),
(106,'100002','10001',20.00,0.90,4,72.00),
(107,'100003','10004',40.00,1.00,4,160.00),
(108,'100003','20001',20.00,0.80,1,16.00),
(109,'100003','30001',5.00,1.00,2,10.00),
(110,'100003','30002',10.00,1.00,2,20.00),
(111,'100004','30002',10.00,1.00,2,20.00),
(112,'100004','30003',10.00,1.00,4,40.00),
(114,'100004','20001',20.00,0.80,5,80.00),
(115,'100004','20002',12.00,1.00,5,60.00);
