基于postgreSQL的基本SQL语句

恒晖瑶

//创建模式“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;