数据库原理及应用教程洗衣机销售实验

最新推荐文章于 2024-10-02 00:27:17 发布

Author_Wind

最新推荐文章于 2024-10-02 00:27:17 发布

阅读量296

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/Author_Wind/article/details/128208211

版权

CREATE DATABASE MYDB
CREATE TABLE CLIENT
(CID VARCHAR(8) PRIMARY KEY,
CNAME VARCHAR(8),
CSEX VARCHAR(4) DEFAULT '男',
CTEL VARCHAR(12)
)
CREATE TABLE WASHER
(WID VARCHAR(8) PRIMARY KEY,
WBRAND VARCHAR(8) NOT NULL,
WTYPE VARCHAR(6)
)
CREATE TABLE SUPPLIER
(SID1 VARCHAR(8) PRIMARY KEY,
COMPANY VARCHAR(12),
STEL VARCHAR(12)
)
SELECT *
FROM CLIENT
SELECT *
FROM WASHER
SELECT *
FROM SUPPLIER
CREATE TABLE BUY
(WID VARCHAR(8) FOREIGN KEY REFERENCES WASHER(WID),
SID1 VARCHAR(8) FOREIGN KEY REFERENCES SUPPLIER(SID1),
BNUM INT,
BPRICE INT,
BDATE DATE,
PRIMARY KEY(WID,SID1)/*表级定义主键*/
)
CREATE TABLE SELL
(CID VARCHAR(8) FOREIGN KEY REFERENCES CLIENT(CID),
WID VARCHAR(8) FOREIGN KEY REFERENCES WASHER(WID),
SNUM INT,
SPRICE INT,
SDATE DATE,
PRIMARY KEY(WID,CID)
)
SELECT *
FROM BUY
SELECT *
FROM SELL
INSERT INTO CLIENT VALUES ('C001','张三','男','13384847388'),
('C002','王利','男','13374335555'),
('C003','李玲','女','13573332222'),
('C004','张虎','男','15173166666'),
('C005','王明','男','18107310808'),
('C006','刘珊','女','13907445656')
INSERT INTO WASHER VALUES ('W001','美的','小号'),
('W002','统帅','小号'),
('W003','海尔','中号'),
('W004','海尔','小号'),
('W005','西门子','大号'),
('W006','小天鹅','中号')
INSERT INTO SUPPLIER VALUES ('S001','花市电器公司','13973339999'),
('S002','草市家电集团','15072226666'),
('S003','树市家电公司','18170008888')
INSERT INTO BUY VALUES ('W003','S002',25,3600,'2021-08-11'),
('W005','S002',10,5800,'2021-08-19'),
('W001','S001',12,2200,'2021-08-11'),
('W001','S003',25,1800,'2021-07-02'),
('W002','S001',16,2000,'2021-08-11'),
('W004','S001',20,2500,'2021-02-08'),
('W006','S001',30,3800,'2021-06-16')
INSERT INTO SELL VALUES ('C002','W003',1,4600,'2022-10-25'),
('C001','W005',1,6800,'2022-06-30'),
('C005','W001',2,2800,'2022-06-17'),
('C006','W001',2,3100,'2022-11-08'),
('C001','W004',1,3200,'2022-03-26')
SELECT *
FROM CLIENT
SELECT *
FROM WASHER
SELECT *
FROM SUPPLIER
SELECT *
FROM BUY
SELECT *
FROM SELL
SELECT CNAME,CSEX,CTEL
FROM WASHER,CLIENT,SELL
WHERE WBRAND='海尔' AND CLIENT.CID=SELL.CID AND WASHER.WID=SELL.WID
SELECT WID,SUM(BNUM*BPRICE)
FROM BUY
WHERE SID1 IN (
SELECT SID1
FROM SUPPLIER
WHERE COMPANY='花市电器公司')
GROUP BY WID
UPDATE SELL
SET SPRICE=0.8*SPRICE
WHERE SPRICE>=3500
SELECT CNAME,WBRAND,WTYPE,SPRICE
FROM WASHER,CLIENT,SELL
WHERE WASHER.WID=SELL.WID AND CLIENT.CID=SELL.CID
ORDER BY SPRICE DESC

SELECT BNUM,BUY.WID
FROM BUY,WASHER
WHERE WASHER.WID=BUY.WID AND WTYPE='小号'
CREATE VIEW BUYINFO
AS SELECT BUY.SID1,BUY.WID,WBRAND,BDATE
FROM WASHER,SUPPLIER,BUY
WHERE WASHER.WID=BUY.WID AND SUPPLIER.SID1=BUY.SID1 AND BDATE='2021-08-11'
SELECT *
FROM BUYINFO
CREATE FUNCTION A1(@BNUM INT)
RETURNS TABLE
AS
RETURN SELECT BNUM,BUY.SID1,COMPANY
FROM SUPPLIER,BUY
WHERE SUPPLIER.SID1=BUY.SID1 AND @BNUM<BNUM
SELECT *
FROM A1(15)
CREATE TRIGGER A2
ON WASHER
AFTER DELETE
AS
 DELETE FROM SELL
 WHERE SELL.WID
 IN(SELECT WID FROM deleted)
 GO
DELETE FROM WASHER
WHERE WID IN(
SELECT WID
FROM BUY
WHERE SID1 IN (SELECT SID1
FROM SUPPLIER
WHERE COMPANY='草市家电集团'))
SELECT *
FROM SELL,WASHER

时间有限，就不做解释了，如有错漏，请评论指出；如有疑问，请留言