数据库原理及应用教程宿舍分配实验

 

 

 

 

 

 

 

 

 

 

 

CREATE DATABASE MYBASE
CREATE TABLE COLLEGE
(CID VARCHAR(6) PRIMARY KEY,
CNAME VARCHAR(10),
CSNUM INT,
CTEL VARCHAR(15)
)
CREATE TABLE ROOM1
(RID VARCHAR(6) PRIMARY KEY,
TYPE1 VARCHAR(8),
LOCATION1 VARCHAR(11),
NORM VARCHAR(6)
)
CREATE TABLE ADMIN1
(AID VARCHAR(6) PRIMARY KEY,
ANAME VARCHAR(8),
ASEX VARCHAR(4) NOT NULL DEFAULT '男',
ATEL VARCHAR(14))
SELECT *
FROM COLLEGE
SELECT *
FROM ROOM1
SELECT *
FROM ADMIN1
CREATE TABLE MANAGE
(RID VARCHAR(6)FOREIGN KEY REFERENCES ROOM1(RID),
AID VARCHAR(6)FOREIGN KEY REFERENCES ADMIN1(AID),
DUTY VARCHAR(8),
PRIMARY KEY (RID,AID)
)
CREATE TABLE ASSIGN
(CID VARCHAR(6) FOREIGN KEY REFERENCES COLLEGE(CID),
RID VARCHAR(6) FOREIGN KEY REFERENCES ROOM1(RID),
ADATE DATE ,
PRIMARY KEY (CID,RID)
)
SELECT *
FROM MANAGE
SELECT *
FROM ASSIGN
INSERT INTO COLLEGE VALUES ('C001','信息学院',1500,'0743-8563673'),
('C002','数统学院',1200,'0743-8564884'),
('C003','外国语学院',1000,'0743-8563663'),
('C004','化工学院',600,'0743-8564126'),
('C005','物电学院',1300,'0743-8564899'),
('C006','资环学院',500,'0743-8564666')
INSERT INTO ROOM1 VALUES ('R001','女生宿舍','1栋201室','4人间'),
('R002','女生宿舍','3栋202室','8人间'),
('R003','男生宿舍','2栋101室','4人间'),
('R004','男生宿舍','2栋202室','6人间'),
('R005','男生宿舍','3栋301室','6人间'),
('R006','男生宿舍','3栋401室','6人间')
INSERT INTO ADMIN1 VALUES ('A001','张三','女','13578787436'),
('A002','刘宫美','女','13574338909'),
('A003','舒敏','女','15074331212'),
('A004','张子恒','男','13974335083'),
('A005','张俊杰','男','13574331218')
INSERT INTO MANAGE VALUES ('R001','A002','主责任人'),
('R002','A003','主责任人'),
('R002','A002','副责任人'),
('R003','A004','主责任人'),
('R004','A004','副责任人')
INSERT INTO ASSIGN VALUES ('C005','R002','2022-04-09'),
('C006','R001','2022-04-11'),
('C002','R004','2022-04-09'),
('C002','R005','2022-04-09'),
('C003','R006','2022-04-20')
SELECT *
FROM COLLEGE
SELECT *
FROM ROOM1
SELECT *
FROM ADMIN1
SELECT *
FROM MANAGE
SELECT *
FROM ASSIGN
/*连接查询*/
SELECT CNAME,CTEL
FROM COLLEGE,ROOM1,ASSIGN
WHERE TYPE1='女生宿舍' AND ASSIGN.CID=COLLEGE.CID AND ROOM1.RID=ASSIGN.RID
/*DISTINCT,消去重复行*/
SELECT DISTINCT NORM
FROM ROOM1
/**/
UPDATE ROOM1
SET NORM='4人间'
WHERE LOCATION1 LIKE '3栋%'
SELECT *
FROM ROOM1
ORDER BY DESC
ORDER BY DESC/*降序排序*/
SELECT ADMIN1.AID,ANAME,COUNT(RID) AS 管理间数/*COUNT统计个数*/
FROM MANAGE,ADMIN1
WHERE MANAGE.AID=ADMIN1.AID
GROUP BY ADMIN1.AID,ANAME/*GROUP BY分组统计*/

CREATE VIEW ASSIGNINF
AS SELECT ROOM1.RID,TYPE1,NORM,CNAME,CTEL
FROM COLLEGE,ROOM1,ASSIGN
WHERE ASSIGN.CID=COLLEGE.CID AND ASSIGN.RID=ROOM1.RID AND ADATE ='2022-04-09'
SELECT *
FROM ASSIGNINF
CREATE FUNCTION A1/*创建内联表值函数*/
(@ANAME VARCHAR(8))
RETURNS @D TABLE
(ANAME VARCHAR(8),
RID VARCHAR(12),
LOCATION1 VARCHAR(11),
DUTY VARCHAR(8)
)
AS
BEGIN
  INSERT INTO @D
  SELECT ROOM1.RID,LOCATION1,ANAME,DUTY
  FROM ROOM1,ADMIN1,MANAGE
  WHERE ROOM1.RID=MANAGE.RID AND MANAGE.AID=ADMIN1.AID AND @ANAME=ANAME
  RETURN
END
SELECT * FROM A1('刘宫美')WHERE DUTY='主责任人'

create procedure Myproc/*创建存储过程*/
(
@Cid varchar(20),
@Cname varchar(20),
@Csnum int=1000,
@Ctel varchar(20)
)
as insert into College values(@Cid,@Cname,@Csnum,@Ctel)
exec MyProc @Cid='C008',@Cname='软件工程学院',@Csnum=1600,@Ctel='0744-6256226'

 时间原因，部分题目代码未验证，如有错漏，请评论指出，不胜感激。