USE shopexpm;
GO
/*创建DeptInfo表*/
CREATE TABLE DeptInfo
(
DeptID varchar(4) NOT NULL PRIMARY KEY,
DeptName varchar(20) NOT NULL
)
/*创建EmplInfo表*/
CREATE TABLE EmplInfo
(
EmplID varchar(4) NOT NULL PRIMARY KEY,
EmplName varchar(8) NOT NULL,
Sex varchar(2) NOT NULL,
Birthday date NOT NULL,
Native varchar(20) NULL,
Wages decimal(8,2) NOT NULL,
DeptID varchar(4) NULL
)
/*创建OrderInfo表*/
CREATE TABLE OrderInfo
(
OrderID varchar(6) NOT NULL PRIMARY KEY,
EmplID varchar(4) NULL,
CustID varchar(4) NULL,
Saledate date NOT NULL,
Cost decimal(10,2) NOT NULL
)
/*创建DetailInfo表*/
CREATE TABLE DetailInfo
(
OrderID varchar(6) NOT NULL,
GoodsID varchar(4) NOT NULL,
Sunitprice decimal(8,2) NOT NULL,
Quantity int NOT NULL,
Total decimal(10,2) NOT NULL,
Discount float NOT NULL,
Disctotal decimal(10,2) NOT NULL,
PRIMARY KEY(OrderID,GoodsID)
)
/*创建GoodsInfo表*/
CREATE TABLE GoodsInfo
(
GoodsID varchar(4) NOT NULL PRIMARY KEY,
GoodsName varchar(30) NOT NULL,
Classification varchar(20) NOT NULL,
Unitprice decimal(8,2) NULL,
Stockqty int NULL
)
/*插入数据到DeptInfo表*/
INSERT INTO DeptInfo
VALUES('D001','销售部'),
('D002','人事部'),
('D003','财务部'),
('D004','经理办'),
('D005','物资部');
/*插入数据到EmplInfo表*/
INSERT INTO EmplInfo
VALUES('E001','向浩然','男','1987-06-17','北京',4200.00,'D001'),
('E002','齐雨佳','女','1991-03-25','上海',3700.00,'D003'),
('E003','穆映雪','女','1992-10-04 ',NULL,3600.00,'D001'),
('E004','沈飞','男','1985-09-16','北京',4600.00,'D001'),
('E005','计海翔','男','1979-12-09','上海',7100.00,'D004'),
('E006','欧莉','女','1994-08-23','四川',3500.00,'D002');
/*插入数据到OrderInfo表*/
INSERT INTO OrderInfo
VALUES('S00001','E004','C001','2022-05-08',21677.40),
('S00002','E001','C002','2022-05-08',30294.00),
('S00003','E003','C003','2022-05-08',15978.60),
('S00004',NULL,'C004','2022-05-08',5659.20);
/*插入数据到DetailInfo表*/
INSERT INTO DetailInfo
VALUES('S00001','1001',6288.00,1,6288.00,0.1,5659.20), ('S00001','3001',8899.00,2,17798.00,0.1,16018.20),
('S00002','1002',8877.00,3,26631.00,0.1,23967.90),
('S00002','2001',7029.00,1,7029.00,0.1,6326.10),
('S00003','1002',8877.00,2,17754.00,0.1,15978.60),
('S00004','1001',6288.00,1,6288.00,0.1,5659.20);
/*插入数据到GoodsInfo表*/
INSERT INTO GoodsInfo
VALUES('1001','Microsoft Surface Pro 7','笔记本电脑',6288.00,5),
('1002','DELL XPS13-7390','笔记本电脑',8877.00,5),
('2001','Apple iPad Pro','平板电脑',7029.00,5),
('3001','DELL PowerEdgeT140','服务器',8899.00,5),
('4001','EPSON L565','打印机',1959.00,10);
GO
CREATE VIEW v_goodscondition AS
SELECT goodsid, goodsname, stockquantity
FROM goodsinfo;
SELECT * FROM v_goodscondition;
SELECT * FROM v_goodscondition
WHERE goodsname LIKE '%笔记本电脑%';
UPDATE v_goodscondition
SET stockquantity = 3
WHERE goodsid = 2001;
DROP VIEW IF EXISTS v_goodscondition;
CREATE VIEW v_ordercondition AS
SELECT o.orderid, o.customerid, d.goodsid, d.quantity
FROM orderinfo o
JOIN detailinfo d ON o.orderid = d.orderid;
SELECT * FROM v_ordercondition;
SELECT * FROM v_ordercondition
WHERE orderid = 's00001';
UPDATE v_ordercondition
SET customerid = 'c012'
WHERE orderid = 'S00001';
CREATE INDEX idx_goodsname ON goodsinfo (goodsname);
CREATE UNIQUE CLUSTERED INDEX idx_goodsid ON goodsinfo (goodsid);
CREATE INDEX idx_unitprice_goodsname ON goodsinfo (unitprice DESC, goodsname ASC);
-- 删除 goodsname 列的索引
DROP INDEX idx_goodsname ON goodsinfo;
-- 删除 goodsid 列的索引
DROP INDEX idx_goodsid ON goodsinfo;
-- 删除 unitprice 和 goodsname 列的组合索引
DROP INDEX idx_unitprice_goodsname ON goodsinfo;
这是一个MYSQL数据库的全部代码，但是出现报错现象，帮我修改一下，符合初学者的水平
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
