數據庫實驗一

提示: 實驗中創(chuàng)建表時所使用的CHECK字段必須在MySQL8.0.2版本以上才會生效

數據庫實驗一

實驗內容

已知某網店數據庫（spxs）具有客戶表（Client）挽荠、商品表（Commidity）、訂單表（order）及訂單明細表（Order_detail）盼樟。數據庫表結構定義如下：

image.png

完成下列任務：

? 創(chuàng)建數據庫spxs。
? 創(chuàng)建數據表客戶表（Client）愁铺、商品表（Commidity）余素、訂單表（order）及訂單明細表（Order_detail）。
? 按數據表定義建立數據表約束镜沽。
? 設計并輸入數據如下：

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;

創(chuàng)建數據庫spxs

CREATE DATABASE spxs;

創(chuàng)建數據表客戶表 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');

創(chuàng)建數據表商品表 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);

創(chuàng)建數據表訂單表 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);

創(chuàng)建數據表訂單明細表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);

最后編輯于：2019.10.09 10:01:08

?著作權歸作者所有,轉載或內容合作請聯系作者