数据库原理及应用教程瑜伽实验

  部分借鉴了~晚风微凉~的数据库原理及应用教程上机实验考试_~晚风微凉~的博客-CSDN博客。

本文用于个人复习，所以仅给出代码，就不详细解释了，如有疑问，请评论，有空回复。

 

 

 

 

 

 

 

 

 

 

CREATE DATABASE YOGA
CREATE TABLE MEMBER3
(MID CHAR(4) PRIMARY KEY,
MNAME VARCHAR(6) NOT NULL,
MSEX VARCHAR(3) DEFAULT '男',
MAGE INT,
MTEL VARCHAR(11),
)

CREATE TABLE ROOM3
(RID CHAR(4) PRIMARY KEY,
RLOC VARCHAR(4),
RTYPE VARCHAR(6),
)

CREATE TABLE COACH3
(CID CHAR(4) PRIMARY KEY,
CNAME VARCHAR(6) NOT NULL,
CTEL VARCHAR(11),
)

CREATE TABLE UTILIZE3
(CID CHAR(4) PRIMARY KEY,
RID CHAR(4) FOREIGN KEY REFERENCES ROOM3(RID),
UDATE VARCHAR(5),
UHOURS INT,
)
ALTER TABLE UTILIZE3
ALTER COLUMN UDATE VARCHAR(6)
CREATE TABLE TRAIN
(CID CHAR(4) FOREIGN KEY REFERENCES COACH3(CID),
MID CHAR(4) PRIMARY KEY,
TDAYS INT,
TCOSTS INT,
)

INSERT INTO MEMBER3 VALUES ('M001','张三','男',20,'13768597338'),
('M002','陈兰','女',28,'13574339090'),
('M003','刘丽','女',38,'13273200222'),
('M004','赵君','男',46,'15073360123'),
('M005','张灵灵','女',39,'18607310909')
INSERT INTO ROOM3 VALUES ('R001','1楼','贵宾室'),
('R002','1楼','贵宾室'),
('R003','1楼','普通室'),
('R004','2楼','普通室'),
('R005','2楼','普通室')
INSERT INTO COACH3 VALUES ('C001','王敏','13574300016'),
('C002','周英','13507430018'),
('C003','李文莉','13807430019'),
('C004','王虹','18007430020'),
('C005','王依依','18107430021')
INSERT INTO UTILIZE3 VALUES ('C001','R001','星期一',8),
('C002','R002','星期二',10),
('C003','R004','星期五',8),
('C004','R003','星期五',6),
('C005','R002','星期五',4)
INSERT INTO TRAIN VALUES ('C001','M002',30,3000),
('C001','M003',20,2000),
('C002','M005',20,1000),
('C002','M001',40,2000),
('C005','M004',30,2500)

SELECT *
FROM MEMBER3
SELECT *
FROM ROOM3
SELECT *
FROM COACH3
SELECT *
FROM UTILIZE3
SELECT *
FROM TRAIN
/* 连接查询*/
SELECT CNAME,MNAME,MSEX,MAGE
FROM COACH3,TRAIN,MEMBER3
WHERE TRAIN.CID=COACH3.CID AND TRAIN.MID=MEMBER3.MID AND CNAME LIKE '王%'

/*分组查询 */
SELECT RTYPE AS 类型,COUNT(*) AS 个数
FROM ROOM3
GROUP BY RTYPE
/*打折降序显示，暂时不会合并 */
UPDATE TRAIN
SET TCOSTS=0.9*TCOSTS
WHERE TCOSTS>=1800
SELECT MNAME,TCOSTS,MTEL,CNAME,CTEL
FROM TRAIN,COACH3,MEMBER3
WHERE TRAIN.CID=COACH3.CID AND TRAIN.MID=MEMBER3.MID
/*子查询 */
SELECT CID,CNAME
FROM COACH3
WHERE CNAME NOT IN(SELECT CNAME
FROM COACH3
WHERE CID IN(SELECT CID
FROM UTILIZE3
WHERE RID='R002'))
/* 视图*/
CREATE VIEW VIPINF
AS SELECT ROOM3.RID,RTYPE,CNAME,MNAME,MTEL
FROM ROOM3,MEMBER3,UTILIZE3,TRAIN,COACH3
WHERE RTYPE='贵宾室' AND ROOM3.RID=UTILIZE3.RID AND UTILIZE3.CID=TRAIN.CID AND TRAIN.MID=MEMBER3.MID AND COACH3.CID=UTILIZE3.CID
SELECT *
FROM VIPINF
/* 内联表值函数*/
CREATE FUNCTION FUN1(@UHOURS INT)
RETURNS TABLE
AS
RETURN SELECT ROOM3.RID,RLOC,RTYPE,UHOURS 
FROM UTILIZE3,ROOM3 
WHERE ROOM3.RID=UTILIZE3.RID AND @UHOURS>=UHOURS
SELECT * FROM FUN1(7)
/* 触发器*/
CREATE TRIGGER TIGG
ON COACH3
AFTER DELETE
AS
 DELETE FROM TRAIN
 WHERE TRAIN.CID
 IN(SELECT CID FROM deleted)
 GO
ALTER TABLE TRAIN
DROP CONSTRAINT FK__TRAIN__CID__33D4B598/*撤除约束，可执行下列*/
DELETE FROM COACH3 
WHERE CID = (SELECT CID
FROM TRAIN
WHERE MID='M001')