本文提供了一组关于PolarDB云数据库的创建和数据操作的示例，包括DDL语句用于创建8张表，以及DML语句插入数据。涉及表间外键关系处理，以及如何通过SQL查询特定信息，如学生成绩单、学生电话、学生清单、学院信息、课程选修情况等。
实验题目：polarDB云数据库创建和数据操作
1.试验任务
- 写出所给8张表的DDL定义语句；（因为外键冲突建议用以下顺序建立表，当然，我是小白所以只能这样的本办法）建议一个表一个表建立，也就是下面的代码一块一块跑，然后可以右侧看一下有没有建好，报错在下面。
create table department
(
dno char(2) not null ,
`name` VARCHAR(20) not null ,
dean char(8) null ,
addr varchar(40) null ,
phone varchar(12) null ,
PRIMARY KEY (dno)
#FOREIGN KEY (dean)REFERENCES teacher(tno)
);create table teacher
(
tno char(8) not null ,
`name` varchar(8) not null ,
sex char(2) null ,
birthday DATE null ,
`rank` VARCHAR(8) null ,
salary int null ,
home varchar(40) null ,
zipcode char(6) null ,
tel varchar(12) null ,
email varchar(32) null ,
dno char(2) null,
PRIMARY KEY (tno),
FOREIGN KEY (dno)REFERENCES department(dno)
);create table cousetype
(
ctno char(1)not null ,
name varchar(16)not null ,
PRIMARY KEY (ctno)
);create table speciality
(
spno char(2) not null ,
dno char(2) not null ,
name varchar(20) not null ,
PRIMARY KEY (spno),
FOREIGN KEY (dno)REFERENCES department(dno)
);create table student
(
sno char(20) not null ,
sname varchar(8) not null ,
sex char(2) null ,
nation varchar(20) null ,
birthday date null ,
spno char(4) null ,
classno char(8) null ,
entime char(4) null ,
home varchar(40) null ,
tel varchar(40) null ,
dno char(2) null ,
PRIMARY KEY (sno),
FOREIGN KEY (spno)REFERENCES speciality(spno)
);create table course
(
cno char(10) not null ,
name VARCHAR(20) not null ,
spno char(20) null ,
ctno char(1) null ,
experiment smallint null ,
hours smallint null ,
semester smallint null ,
credit smallint null ,
`duty_tno` char(8) null ,
dno char(2) null ,
PRIMARY KEY (cno),
FOREIGN KEY (dno)REFERENCES department(dno),
FOREIGN KEY (spno)REFERENCES speciality(spno),
FOREIGN KEY (ctno)REFERENCES cousetype(ctno),
FOREIGN KEY (`duty_tno`)REFERENCES teacher(tno)
);create table teach
(
tno char(8)not null ,
cno char(10)not null ,
year_semester char(6)not null ,
classno char(1)not null ,
PRIMARY KEY (tno,cno,year_semester,classno),
FOREIGN KEY (tno)REFERENCES teacher(tno),
FOREIGN KEY (cno)REFERENCES course(cno)
);create table enroll
(
sno char(20) not null ,
cno char(20) not null ,
year_semester char(6) not null ,
tno char(8) null ,
classno char(1) null ,
score float null,
PRIMARY KEY (sno,cno,year_semester),
FOREIGN KEY (tno)REFERENCES teacher(tno),
FOREIGN KEY (sno)REFERENCES student(sno),
FOREIGN KEY (cno)REFERENCES course(cno),
FOREIGN KEY (tno)REFERENCES teacher(tno)
);这里呢，第一个表和第二个表互有来自对方的外键，所以在创建时不能直接给第一个表加外键，最后要加一句设置主键，但是也建议在加完一二两个表的数据之后再运行，不然你是插不进第一个表的数据的。
alter table department add foreign key (dean)REFERENCES teacher(tno);2. 写出所给8张表中数据行的添加DML语句；
（原来的数据有一些问题，我们的助教改了一些，也可以把有问题的数据删掉，我选的后面这种，删了不少）
INSERT INTO department(dno, name, dean, addr, phone) VALUES
('24','信息科学与工程学院','2011120','软件楼','8305424'),
('27','数学院','2007004','数学楼','8304551'),
('28','工商管理学院','2009007','MBA楼','8304530'),
('29','会计学院','1995008', '逸夫楼','8304578'),
('31','政治理论学院','20011024', '红叶楼','8304517'),
('32','机械学院','20001021','宇航楼','8304534'),
('33','工业设计学院','19971004','设计大楼','8305204');下面的格式有点乱，要是为了好看那你可以自己在idea上好好改一下！！！！电脑没电了，潦草一下吧。
INSERT INTO teacher(tno, name, sex, birthday, `rank`, salary,dno) VALUES
('2004124','刘东妮','女','1985-1-9','助教','7200','24'),
('2011120','万世平','男','1962-12-11','教授','9900','24'),
('2001009','周曼玲','女','1972-09-09','副教授','8600','24'),
('2000121','李平','男','1973-04-12','助教','7500','24'),
('1997030','刘小明','女','1979-03-30','教授','9700','24'),
('20041024','朱喜','男','1985-1-9','副教授','8500','24'),
('20111104','王林林','女','1962-12-11','助教','7900','24'),
('2007004','张中华','男','1972-09-09','教授','9850','27'),
('2009007','马力','女','1973-04-12','教授','8650','28'),
('1995008','陈浪','男','1979-03-30','教授','7880','29'),
('20011024','刘亚平','女','1972-09-09','教授','10800','31'),
('20001021','张国荣','男','1973-04-12','教授','8950','32'),
('19971004','赵子龙','男','1979-03-30','教授','9650','33'),
('20081003','马千里','男','1979-03-30','副教授','8850','24');
insert into cousetype(ctno, name) VALUES
('1','公共课'),
('2','专业基础课'),
('3','专业课'),
('4','选修课');insert into speciality(spno, name, dno) VALUES
('01','软件工程','24'),
('02','人工智能','24'),
('03','物联网','24'),
('04','网络安全','24'),
('11','电算会计','29'),
('08','工商管理','28'),
('09','市场营销','28');INSERT INTO student(sno, sname, sex, nation, birthday,classno,dno)VALUES
('20182401101','李毅','男','汉','1985-1-2','软件1班','24'),
('20182401102','厉超','男','苗','1984-3-8','软件1班','24'),
('20182401103','梁冰龙','男','壮','1984-7-9','软件1班','24'),
('20182401104','罗煦','女','维吾尔','1985-10-12','软件1班','24'),
('20182401105','马晓睿','男','汉','1983-11-1','软件1班','24'),
('20182401106','聂立业','男','壮','1984-6-3','软件1班','24'),
('20182401107','王实','男','汉','1984-2-11','软件1班','24'),
('20182401108','伍卉','女','汉','1985-1-3','软件1班','24'),
('20182401109','伍琼','男','汉','1985-12-11','软件1班','24'),
('20182401110','曾文超','男','汉','1984-7-5','软件1班','24'),
('20182401201','黄文杰','男','汉','1984-7-5','软件2班','24'),
('20182401202','龙淼','男','汉','1985-12-11','软件2班','24'),
('20182709101','何晓明','男','汉','1984-8-13','营销1班','27'),
('20182709102','刘寅','男','汉','1984-5-7','营销1班','27'),
('20182911101','邢晓静','女','汉','1984-8-13','电算1班','28'),
('20182911102','李明伟','女','汉','1985-3-9','电算1班','28'),
('20182911103','童熙','女','汉','1985-10-16','电算1班','28'),
('20182911104','朱俊华','男','汉','1985-12-3','电算1班','28');INSERT INTO course(cno, name, spno, ctno, hours, experiment, credit, semester, `duty_tno`, dno) VALUES
('H24030006','数据结构',null,'2','64','24','4','4','2004124','24'),
('H24030007','计算机组成原理',null,'2','64',null,'4','5','2011120','24'),
('H24030008','数据库系统',null,'3','48','24','3','5','2001009','24'),
('H24030009','操作系统',null,'3','48','24','3','4','2000121','24'),
('H24030010','计算机网络',null,'3','48','16','3','6','1997030','24'),
('C31010001','毛泽东思想概论',null,'1','32',null,'2','6',null,'31'),
('C31010002','邓小平理论',null,'1','62',null,'2','1',null,'31'),
('C24020005','面向对象程序设计',null,'2','32','24','2','5','20111104','24'),
('X27020012','高等数学',null,'1','64',null,'4','2','2007004','27'),
('X24040019','数据挖掘','02','4','32',null,'2','7',null,'24'),
('X24040022','网络安全概论','02','4','32',null,'2','7',null,'24');insert into teach( cno, year_semester,tno, classno) VALUES
#('H61030006','2019-1','2004124','A'),
#('H61030008','2020-1','2001009','A'),
('C31010001','2019-1','20011024','A'),
('C24020005','2019-2','2009007','A'),
('C31010001','2019-1','20011024','B');insert into enroll(sno, cno, year_semester, tno, classno, score) VALUES
('20182401101','H24030006','2019-1','2004124','A','89'),
('20182401102','H24030006','2019-1','2004124','A','98'),
('20182401103','H24030006','2019-1','2004124','A','86'),
('20182401104','H24030006','2019-1','2004124','B','76'),
('20182401101','H24030008','2020-1','2001009','A','83'),
('20182401102','H24030008','2020-1','2001009','A','66'),
('20182401103','H24030008','2020-1','2001009','A','55'),
('20182401104','H24030008','2020-1','2001009','B','87'),
('20182401202','C31010001','2019-1','20011024','A','88'),
('20182709101','C31010001','2019-1','20011024','A','90'),
('20182709102','C24020005','2019-2','2009007','A','83'),
('20182911101','C24020005','2019-2','2009007','A','55'),
('20182911101','C31010001','2019-1','20011024','B','87'),
('20182401101','C31010001','2019-1','20011024','A','88');别忘了执行一下alter语句
接下来就是做题了，这个还是要自己写一下，不然期中考很容易挂的。
3.对于如下业务需求，写出获取所须数据的SQL语句：
①输出信息科学与工程学院（学院编号“24”）2018级学生“李毅”的课程选修成绩单，输出项包括课程名称，学分，成绩三项；
SELECT c.name,c.credit,e.score
FROM
course AS c,
enroll AS e
WHERE
c.cno=e.cno
AND e.sno=(
SELECT sno
FROM student
WHERE dno='24'
AND sname='李毅'
AND sno LIKE '2018%'
)
;结果应该是
找出学生李毅（学号为 ‘20182401101’）的电话；
SELECT tel
FROM
student
WHERE
sno='20182401101'
AND sname='李毅'
;
输出信息科学与工程学院（学院编号为’24’)的学生清单，输出项包括姓名，学号，班级三列，按照班级和学号排序；
SELECT
sname,sno,classno
FROM
student
WHERE
dno='24'
ORDER BY
classno,sno; 
输出所有学院的清单，包括学院编号，名称，地址，电话，院长姓名；
SELECT
D.dno,D.name,D.addr,D.phone,T.name
FROM
department AS D,teacher AS T
WHERE
D.dean=T.tno;
在2020-1学期选修了周曼玲老师（工号为”2001009”)开设的“数据库系统”课程的学生名单，输出项为五项：教学班，姓名、学号、性别，行政班。要求先按照教学班，再按行政班排序；（注：一个老师在某个学期开的某门课，完全有可能有多个教学班）
SELECT
E.classno,S.sname,S.sno,S.sex,S.classno AS EXCLASSNO
FROM
student AS S,
enroll AS E,
course AS C
WHERE E.sno=S.sno
AND E.cno=C.cno
AND E.year_semester LIKE '2020-1%'
AND C.duty_tno='2001009'
AND C.name='数据库系统'
ORDER BY
E.classno,S.classno;
对于在2020-1学期选修了“数据库系统”课程的各行政班，求其平均分，并按照平均分从高到低排序
SELECT
S.classno,AVG(E.score)AS AVGSCORE
FROM
enroll AS E,
course AS C,
student AS S
WHERE
E.cno=C.cno
AND E.sno=S.sno
AND C.name='数据库系统'
AND E.year_semester LIKE '2020-1%'
GROUP BY
S.classno
ORDER BY
AVGSCORE DESC ; 
对于信息科学与工程学院（学院编号“24”）在2020-1学期开设的每门课程，输出其选修人数，输出项包括课程名称，选修人数；
SELECT
C.name,COUNT(DISTINCT E.sno) AS COUNT
FROM course AS C,
enroll AS E
WHERE C.cno=E.cno
AND C.dno='24'
AND E.year_semester='2020-1'
GROUP BY
C.name;
在2020-1学期，对于开设的“数据库系统”这门课程，在信息科学与工程学院（学院编号“24”）的2018级学生中，有哪些学生没有选修该课程，输出姓名，学号，行政班三项信息；
SELECT
sname,sno,classno
FROM
student
WHERE dno='24'
AND sno LIKE '2018%'
AND sno NOT IN (
SELECT
E.sno
FROM enroll AS E,
course AS C
WHERE
E.cno=C.cno
AND E.year_semester='2020-1'
AND C.name='数据库系统'
)
;
- 对于在2020-1学期开设的每门课程，输出其最高分、最低分，平均分；
SELECT C.name AS COURSENAME,MAX(E.score) AS MAXSCORE,MIN(E.score) MINSCORE,AVG(E.score) AVGSCORE FROM enroll AS E, course AS C WHERE E.cno=C.cno AND E.year_semester='2020-1' GROUP BY E.cno;
对于在2020-1学期开设的“数据库系统”课程，求选修了该课，但是没有及格的学生名单，输出姓名，学号，行政班，课程成绩四项信息；
-
SELECT S.sname,S.sno,S.classno,E.score FROM student AS S, enroll AS E WHERE S.sno=E.sno AND E.year_semester='2020-1' AND E.score<'60' AND E.cno= (SELECT course.cno FROM course WHERE name='数据库系统') ;
对于信息科学与工程学院（学院编号“24”）的每个教师，统计出其在2019年度的教学工作量；（提示：课程表中，每门课有课时量hours字段）
-
WITH TB0(CNO,HOURS,CLASSNO,TNO) AS (SELECT DISTINCT E.cno,C.hours,E.classno,E.tno FROM enroll AS E, course AS C WHERE E.cno=C.cno AND E.year_semester LIKE '2019%' AND E.tno IN ( SELECT T.tno FROM teacher AS T WHERE T.dno='24' ) ) SELECT TE.tno,SUM(HOURS) AS WORKSTIME FROM TB0 AS TT, teacher AS TE WHERE TT.TNO=TE.tno GROUP BY TNO;
对于信息科学与工程学院（学院编号“24”）的每个教师，统计在2019年度教学工作量未达到要求（小于150学时）的教师，输出教师姓名，工号，教学工作量三项；
WITH TB0(CNO,HOURS,CLASSNO,TNO) AS (SELECT DISTINCT E.cno,C.hours,E.classno,E.tno FROM enroll AS E, course AS C WHERE E.cno=C.cno AND E.year_semester LIKE '2019%' AND E.tno IN ( SELECT T.tno FROM teacher AS T WHERE T.dno='24' ) ) SELECT TE.name,TE.tno,SUM(HOURS) AS WORKSTIME FROM TB0 AS TT, teacher AS TE WHERE TT.TNO=TE.tno GROUP BY TNO HAVING WORKSTIME<150;
统计各个学院在2019年度的教学工作量；
WITH TB0(CNO,HOURS,CLASSNO,TNO) AS (SELECT DISTINCT E.cno,C.hours,E.classno,E.tno FROM enroll AS E, course AS C WHERE E.cno=C.cno AND E.year_semester LIKE '2019%' AND E.tno IN ( SELECT T.tno FROM teacher AS T ) ) SELECT TE.tno,SUM(TB.HOURS) FROM TB0 , teacher AS TE WHERE TB.TNO=TE.tno GROUP BY TE.tno ;
14.对于2016级学生，即将毕业，求出输出所得总学分不足172的学生，输出项为所属学院名称，学号，姓名，已得学
SELECT D.name,S.sno,SUM(C.credit) FROM course AS C, department AS D, student AS S WHERE D.dno=S.dno AND C.cno=( SELECT cno FROM enroll WHERE sno=( SELECT sno FROM student WHERE sno LIKE '2016%' ) ) GROUP BY S.sno HAVING SUM(C.credit)<172;分数这四项；
附加题emmm做的比较烂就不放这里
1540
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
