//创建模式“S-T”
CREATE SCHEMA "S-T";
//在“S-T”模式下创建Student表,Sno是主键
CREATE TABLE "S-T".Student (
Sno character(6) primary key,
Sname character(12) unique,
Ssex character(2),
Sage integer,
Sdept character(12)
);
//在“S-T”模式下创建Course表,同时以Cpno为外键,对应的是自身的主键
CREATE TABLE "S-T".Course(
Cno character(4) primary key,
Cname character(12) unique,
Cpno character(4),
Ccredit integer,
foreign key (Cpno) references "S-T".Course(Cno)
);
//在“S-T”模式下 创建表SC,Sno、Cno即是主键、又是外键
CREATE TABLE "S-T".SC(
Sno character(6),
Cno character(4),
Grade integer,
primary key (Sno,Cno),
foreign key (Sno) references "S-T".Student(Sno),
foreign key (Cno) references "S-T".Course(Cno)
);
//创建索引
CREATE UNIQUE INDEX Sno ON Student(Sno DESC);
//删除索引
DROP INDEX Sno;
//创建视图
CREATE VIEW N_Student
AS SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student
WHERE Ssex='女';
//设置默认的模式 不设置则默认为:"$user",PUBLIC
SET search_path TO "S-T";
SET search_path TO "S-T",PUBLIC ;
SET search_path TO "$user",PUBLIC ;
//显示当前的模式
SHOW search_path ;
//删除模式
DROP SCHEMA "S-T" CASCADE;
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201301','张三','男',20,'IS');
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201302','李四','女',19,'CS');
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201303','王二','女',22,'MA');
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201304','赵六','男',21,'CS');
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201305','李明','女',20,'IS');
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201306','王五','女',20,'IS');
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201307','李六','女',20,'IS');
INSERT INTO "S-T".Course(Cno,Cname,Cpno,Ccredit) VALUES('0001','高等数学','0001',4);
INSERT INTO "S-T".Course(Cno,Cname,Cpno,Ccredit) VALUES('0002','数据结构','0001',3);
INSERT INTO "S-T".Course(Cno,Cname,Cpno,Ccredit) VALUES('0003','大学英语','0002',5);
INSERT INTO "S-T".Course(Cno,Cname,Cpno,Ccredit) VALUES('0004','软件工程','0003',3);
INSERT INTO "S-T".Course(Cno,Cname,Cpno,Ccredit) VALUES('0005','数据库原理','0004',5);
INSERT INTO "S-T".Course(Cno,Cname,Cpno,Ccredit) VALUES('0006','编译原理','0006',5);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201301','0001',90);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201301','0002',87);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201301','0003',98);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201301','0004',78);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201301','0005',63);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201302','0001',80);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201302','0002',97);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201302','0003',78);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201302','0004',79);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201302','0005',69);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201303','0001',89);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201303','0002',89);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201303','0003',78);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201303','0004',85);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201303','0005',86);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201304','0001',87);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201304','0002',69);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201304','0003',88);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201304','0004',88);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201304','0005',93);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201305','0001',68);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201305','0002',89);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201305','0003',79);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201305','0004',88);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201305','0005',66);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201306','0001',68);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201306','0002',89);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201306','0003',79);
//添加 S_enterance 列
ALTER TABLE Student ADD S_enterance date;
//删除 S_enterance 列
ALTER TABLE Student DROP S_enterance;
//修改 S_enterance 列的数据类型:character integer 不符合要求(postgreSQL)
ALTER TABLE Student ALTER COLUMN S_enterance TYPE character(12);
ALTER TABLE SC ALTER COLUMN Grade TYPE integer;
ALTER TABLE SC DROP Grade;
SELECT * FROM "S-T".Student;
SELECT * FROM "S-T".Course;
SELECT * FROM "S-T".SC;
SELECT Sname AS 姓名,2013-Sage "出生年月",LOWER(Sdept) AS "院系"
FROM Student;
SELECT * FROM Student
WHERE Sdept='IS';
SELECT * FROM Student
WHERE Sno LIKE '20133_1' ESCAPE '3';
SELECT * FROM Student
WHERE Sno LIKE '2013_1';
SELECT DISTINCT Sno FROM SC;
SELECT ALL Sno FROM SC;
SELECT Sno FROM SC;
SELECT Sno,Grade FROM SC
WHERE Cno='0004'
ORDER BY Grade DESC;
SELECT COUNT(DISTINCT Sno) FROM SC;
SELECT Cno,SUM(Grade) FROM SC
GROUP BY Cno
HAVING SUM(Grade)>=200;
SELECT S.*,SC.*
FROM Student S ,SC
WHERE S.Sno=SC.Sno
SELECT C.Cno,C.Cname,C.Cpno,CP.Cno,CP.Cname,C.Ccredit
FROM Course C,Course CP
WHERE C.Cpno=CP.Cno
//查询所有学生及其选课情况
SELECT S.Sno,S.Sname,SC.Cno,SC.Grade
FROM Student S RIGHT JOIN SC ON(S.Sno=SC.Sno);
//查询有一门课的分数大于 80 并且年龄大于 19 的全部学生
SELECT S.Sno,S.Sname,S.Sage,SC.Cno,SC.Grade
FROM Student S LEFT JOIN SC ON(S.Sno=SC.Sno)
WHERE Sage>19 AND SC.Grade>=80;
//查询有一门课的分数大于 70 并且年龄大于 19 的全部学生
SELECT Sno,Sname,Sage
FROM Student
WHERE SNO IN(
SELECT Sno
FROM SC
WHERE Grade>70
) AND Sname IN (
SELECT Sname
FROM Student
WHERE Sage>19
);
//查询和“王二”同系的全部学生
SELECT *
FROM Student
WHERE Sdept IN (
SELECT Sdept
FROM Student
WHERE Sname = '王二'
);
//查询大于某个学生的平均分的学生的学号、课程号
SELECT SC1.Sno, SC1.Cno
FROM SC SC1
WHERE SC1.Grade >= (
SELECT AVG(Grade)
FROM SC SC2
GROUP BY SC2.Sno
HAVING SC1.Sno=SC2.Sno
);
//查询大于某个学生的平均分的学生的学号、课程号
SELECT SC1.Sno, SC1.Cno
FROM SC SC1
WHERE SC1.Grade >= (
SELECT AVG(Grade)
FROM SC SC2
WHERE SC1.Sno=SC2.Sno
);
//查询不等于每个学生的平均分的学生的学号、课程号
SELECT SC1.Sno, SC1.Cno
FROM SC SC1
WHERE SC1.Grade <> ANY (
SELECT AVG(Grade)
FROM SC SC2
GROUP BY SC2.Sno
);
//查询大于每个学生的平均分的学生的学号、课程号
SELECT SC1.Sno, SC1.Cno
FROM SC SC1
WHERE SC1.Grade >= ALL (
SELECT AVG(Grade)
FROM SC SC2
GROUP BY SC2.Sno
);
//查询小于某一“IS”学生的年龄的非“IS”的学生
SELECT S1.Sname,S1.Sage
FROM Student S1
WHERE S1.Sage < ANY(
SELECT DISTINCT Sage
FROM Student S2
WHERE Sdept = 'CS'
) AND Sdept <> 'CS';
//查询年龄小于全部的 “IS” 学生的年龄的非 “IS” 的学生
SELECT S1.Sname,S1.Sage
FROM Student S1
WHERE S1.Sage < ALL(
SELECT DISTINCT Sage
FROM Student S2
WHERE Sdept = 'IS'
) AND Sdept <> 'IS';
//查询选修 “0001” 课程的学生
SELECT *
FROM Student
WHERE EXISTS (
SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='0001'
);
//查询没有选修 ”0001” 课程的学生
SELECT *
FROM Student
WHERE NOT EXISTS (
SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='0001'
);
//查询选修全部课程的学生
SELECT *
FROM Student
WHERE EXISTS (
SELECT Sno,COUNT(Cno)
FROM SC
GROUP BY Sno
HAVING Sno=Student.Sno AND COUNT(Cno) = (
SELECT COUNT(Cno)
FROM Course
)
);
//查询选修全部课程的学生
SELECT *
FROM Student S
WHERE NOT EXISTS (
SELECT *
FROM Course C
WHERE NOT EXISTS (
SELECT *
FROM SC
WHERE SC.Sno=S.Sno AND
SC.Cno=C.Cno
)
);
//查询选修了学号为“201306”的学生所选修的全部课程的学生(不包含学号为“201306”的学生)
SELECT DISTINCT Sno
FROM SC SC1
WHERE NOT EXISTS (
SELECT *
FROM SC SC2
WHERE SC2.Sno='201306' AND NOT EXISTS (
SELECT *
FROM SC SC3
WHERE SC3.Sno=SC1.Sno AND
SC2.Cno=SC3.Cno
)
) AND Sno NOT LIKE '201306';
SELECT * FROM N_Student;
DELETE FROM Course WHERE Cno='0006'
DROP TABLE student CASCADE;
DROP TABLE sc;
DROP TABLE course;
DROP VIEW N_Student;
CREATE SCHEMA "S-T";
//在“S-T”模式下创建Student表,Sno是主键
CREATE TABLE "S-T".Student (
Sno character(6) primary key,
Sname character(12) unique,
Ssex character(2),
Sage integer,
Sdept character(12)
);
//在“S-T”模式下创建Course表,同时以Cpno为外键,对应的是自身的主键
CREATE TABLE "S-T".Course(
Cno character(4) primary key,
Cname character(12) unique,
Cpno character(4),
Ccredit integer,
foreign key (Cpno) references "S-T".Course(Cno)
);
//在“S-T”模式下 创建表SC,Sno、Cno即是主键、又是外键
CREATE TABLE "S-T".SC(
Sno character(6),
Cno character(4),
Grade integer,
primary key (Sno,Cno),
foreign key (Sno) references "S-T".Student(Sno),
foreign key (Cno) references "S-T".Course(Cno)
);
//创建索引
CREATE UNIQUE INDEX Sno ON Student(Sno DESC);
//删除索引
DROP INDEX Sno;
//创建视图
CREATE VIEW N_Student
AS SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student
WHERE Ssex='女';
//设置默认的模式 不设置则默认为:"$user",PUBLIC
SET search_path TO "S-T";
SET search_path TO "S-T",PUBLIC ;
SET search_path TO "$user",PUBLIC ;
//显示当前的模式
SHOW search_path ;
//删除模式
DROP SCHEMA "S-T" CASCADE;
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201301','张三','男',20,'IS');
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201302','李四','女',19,'CS');
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201303','王二','女',22,'MA');
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201304','赵六','男',21,'CS');
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201305','李明','女',20,'IS');
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201306','王五','女',20,'IS');
INSERT INTO "S-T".Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201307','李六','女',20,'IS');
INSERT INTO "S-T".Course(Cno,Cname,Cpno,Ccredit) VALUES('0001','高等数学','0001',4);
INSERT INTO "S-T".Course(Cno,Cname,Cpno,Ccredit) VALUES('0002','数据结构','0001',3);
INSERT INTO "S-T".Course(Cno,Cname,Cpno,Ccredit) VALUES('0003','大学英语','0002',5);
INSERT INTO "S-T".Course(Cno,Cname,Cpno,Ccredit) VALUES('0004','软件工程','0003',3);
INSERT INTO "S-T".Course(Cno,Cname,Cpno,Ccredit) VALUES('0005','数据库原理','0004',5);
INSERT INTO "S-T".Course(Cno,Cname,Cpno,Ccredit) VALUES('0006','编译原理','0006',5);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201301','0001',90);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201301','0002',87);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201301','0003',98);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201301','0004',78);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201301','0005',63);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201302','0001',80);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201302','0002',97);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201302','0003',78);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201302','0004',79);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201302','0005',69);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201303','0001',89);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201303','0002',89);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201303','0003',78);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201303','0004',85);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201303','0005',86);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201304','0001',87);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201304','0002',69);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201304','0003',88);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201304','0004',88);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201304','0005',93);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201305','0001',68);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201305','0002',89);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201305','0003',79);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201305','0004',88);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201305','0005',66);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201306','0001',68);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201306','0002',89);
INSERT INTO "S-T".SC(Sno,Cno,Grade) VALUES('201306','0003',79);
//添加 S_enterance 列
ALTER TABLE Student ADD S_enterance date;
//删除 S_enterance 列
ALTER TABLE Student DROP S_enterance;
//修改 S_enterance 列的数据类型:character integer 不符合要求(postgreSQL)
ALTER TABLE Student ALTER COLUMN S_enterance TYPE character(12);
ALTER TABLE SC ALTER COLUMN Grade TYPE integer;
ALTER TABLE SC DROP Grade;
SELECT * FROM "S-T".Student;
SELECT * FROM "S-T".Course;
SELECT * FROM "S-T".SC;
SELECT Sname AS 姓名,2013-Sage "出生年月",LOWER(Sdept) AS "院系"
FROM Student;
SELECT * FROM Student
WHERE Sdept='IS';
SELECT * FROM Student
WHERE Sno LIKE '20133_1' ESCAPE '3';
SELECT * FROM Student
WHERE Sno LIKE '2013_1';
SELECT DISTINCT Sno FROM SC;
SELECT ALL Sno FROM SC;
SELECT Sno FROM SC;
SELECT Sno,Grade FROM SC
WHERE Cno='0004'
ORDER BY Grade DESC;
SELECT COUNT(DISTINCT Sno) FROM SC;
SELECT Cno,SUM(Grade) FROM SC
GROUP BY Cno
HAVING SUM(Grade)>=200;
SELECT S.*,SC.*
FROM Student S ,SC
WHERE S.Sno=SC.Sno
SELECT C.Cno,C.Cname,C.Cpno,CP.Cno,CP.Cname,C.Ccredit
FROM Course C,Course CP
WHERE C.Cpno=CP.Cno
//查询所有学生及其选课情况
SELECT S.Sno,S.Sname,SC.Cno,SC.Grade
FROM Student S RIGHT JOIN SC ON(S.Sno=SC.Sno);
//查询有一门课的分数大于 80 并且年龄大于 19 的全部学生
SELECT S.Sno,S.Sname,S.Sage,SC.Cno,SC.Grade
FROM Student S LEFT JOIN SC ON(S.Sno=SC.Sno)
WHERE Sage>19 AND SC.Grade>=80;
//查询有一门课的分数大于 70 并且年龄大于 19 的全部学生
SELECT Sno,Sname,Sage
FROM Student
WHERE SNO IN(
SELECT Sno
FROM SC
WHERE Grade>70
) AND Sname IN (
SELECT Sname
FROM Student
WHERE Sage>19
);
//查询和“王二”同系的全部学生
SELECT *
FROM Student
WHERE Sdept IN (
SELECT Sdept
FROM Student
WHERE Sname = '王二'
);
//查询大于某个学生的平均分的学生的学号、课程号
SELECT SC1.Sno, SC1.Cno
FROM SC SC1
WHERE SC1.Grade >= (
SELECT AVG(Grade)
FROM SC SC2
GROUP BY SC2.Sno
HAVING SC1.Sno=SC2.Sno
);
//查询大于某个学生的平均分的学生的学号、课程号
SELECT SC1.Sno, SC1.Cno
FROM SC SC1
WHERE SC1.Grade >= (
SELECT AVG(Grade)
FROM SC SC2
WHERE SC1.Sno=SC2.Sno
);
//查询不等于每个学生的平均分的学生的学号、课程号
SELECT SC1.Sno, SC1.Cno
FROM SC SC1
WHERE SC1.Grade <> ANY (
SELECT AVG(Grade)
FROM SC SC2
GROUP BY SC2.Sno
);
//查询大于每个学生的平均分的学生的学号、课程号
SELECT SC1.Sno, SC1.Cno
FROM SC SC1
WHERE SC1.Grade >= ALL (
SELECT AVG(Grade)
FROM SC SC2
GROUP BY SC2.Sno
);
//查询小于某一“IS”学生的年龄的非“IS”的学生
SELECT S1.Sname,S1.Sage
FROM Student S1
WHERE S1.Sage < ANY(
SELECT DISTINCT Sage
FROM Student S2
WHERE Sdept = 'CS'
) AND Sdept <> 'CS';
//查询年龄小于全部的 “IS” 学生的年龄的非 “IS” 的学生
SELECT S1.Sname,S1.Sage
FROM Student S1
WHERE S1.Sage < ALL(
SELECT DISTINCT Sage
FROM Student S2
WHERE Sdept = 'IS'
) AND Sdept <> 'IS';
//查询选修 “0001” 课程的学生
SELECT *
FROM Student
WHERE EXISTS (
SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='0001'
);
//查询没有选修 ”0001” 课程的学生
SELECT *
FROM Student
WHERE NOT EXISTS (
SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno='0001'
);
//查询选修全部课程的学生
SELECT *
FROM Student
WHERE EXISTS (
SELECT Sno,COUNT(Cno)
FROM SC
GROUP BY Sno
HAVING Sno=Student.Sno AND COUNT(Cno) = (
SELECT COUNT(Cno)
FROM Course
)
);
//查询选修全部课程的学生
SELECT *
FROM Student S
WHERE NOT EXISTS (
SELECT *
FROM Course C
WHERE NOT EXISTS (
SELECT *
FROM SC
WHERE SC.Sno=S.Sno AND
SC.Cno=C.Cno
)
);
//查询选修了学号为“201306”的学生所选修的全部课程的学生(不包含学号为“201306”的学生)
SELECT DISTINCT Sno
FROM SC SC1
WHERE NOT EXISTS (
SELECT *
FROM SC SC2
WHERE SC2.Sno='201306' AND NOT EXISTS (
SELECT *
FROM SC SC3
WHERE SC3.Sno=SC1.Sno AND
SC2.Cno=SC3.Cno
)
) AND Sno NOT LIKE '201306';
SELECT * FROM N_Student;
DELETE FROM Course WHERE Cno='0006'
DROP TABLE student CASCADE;
DROP TABLE sc;
DROP TABLE course;
DROP VIEW N_Student;