数据库实验一

提示: 实验中创建表时所使用的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);