sql语句基本操作

创建基本表

CREATE TABLE <表名>
    (<列名>，<数据类型>[列级完整性约束条件]
    [， <列名>，<数据类型>[列级完整性约束条]]...
    [，<表级完整性约束条件>]);

1、建立一个“学生”表Student。

CREATE TABLE Student 
    (Sno CHAR(9) PRIMARY KEY,
     Sname CHAR(20) UNIQUE,
     Ssex CHAR(2),
     Sage SMALLINT,
     Sdept CHAR(20));

2、建立一个“课程”表Course

CREATE TABLE Course
    (Cno CHAR(4) PRIMARY KEY,
     Cname CHAR(40),
     Cpno CHAR(4),//先修课
     Ccredit SMALLINT,
     FOREIGN KEY Cpno REFERENCES Course(Cno));

3、建立学生选课表SC

CREATE TABLE SC
    (Sno CHAR(9),
     Cno CHAR(4),
     Grade SMALLINT,
     PRIMARY KEY (Sno,Cno),
     FOREIGN KEY (Sno) REFERENCES Student(Sno),
     FOREIGN KEY (Cno) REFERENCES Course(Cno)
    );

---

修改基本表

ALERT TABLE <表名>
    [ADD <新列名> <数据类型> [完整性数据]]
    [DROP <完整性约束名>]
    [ALERT COLUMN <列名> <数据类型>]

4、向Student表增加“入学时间”列，其数据类型为日期型。

ALERT TABLE Student ADD S_entrance DATE;
//无论基本表原来是否有数据，新增列均为空。

5、将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

ALERT TABLE Student ALERT COLUMN Sage INT;

6、增加课程名称必须取唯一值的约束条件。

ALERT TABLE Course ADD UNIQUE(Cname);

---

删除基本表

DROP TABLE <表名> [RESTRICT | CASCADE];
//RESTRICT:限制;CASCADE:级联

---

单表查询

SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]...
FROM <表名或视图名>[,<表名或试图名>]...
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]];

1、查询全体学生的姓名、出生年份和所在的院系（用大写字母表示）。

SELECT Sname 姓名,'Year of Birth:' ,2016-Sage 出生年份,UPPER(Sdept) 专业 FROM Student;

关键字：
1） DISTINCT（ALL）:去除重复行
2）（NOT）BETWEEN AND:确定范围
3）（NOT） IN:确定集合
4）（NOT） LIKE:字符匹配；%：任意长度，_：单个字符
5） IS （NOT） NULL:空值

2、查询考试成绩有不及格的学生的学号。

SELECT DISTINCT Sno FROM SC WHERE Grade<60; 

3、查询年龄在20~23岁（含20，23）之间的学生的姓名、系别和年龄。

SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23; 

4、查询计算机系（SC）、数学系（MA）、信息系（IS）学生的姓名和性别。

SELECT Sname,Ssex FROM Student WHERE Sdept IN('SC','MA','IS');

5、查询以“DB_”开头，且倒数第3个字符为i的课程详情。

SELECT * FROM Course WHERE Cname LIKE 'DB\_%i__';

6、查询没有参加选修课考试的学生学号和课程号。

SELECT Sno,Cno FROM SC WHERE Grade IS NULL
//这里的IS不能用=代替

7、查询选修了3号课程的学生学号及其成绩，查询结果按分数降序排列。

SELECT Sno,Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;

8、查询全体学生信息，结果按系号升序，同一系中的学生按年龄降序排列。

SELECT * FROM Student ORDER BY Sdept,Sage DESC;

聚集函数

1. COUNT:统计元祖或列中值的个数
2. SUM:计算一列值的总合
3. AVG:计算一列值的平均数
4. MAX:得到一列中最大值
5. MIN:得到一列中最小值

9、求各个课程号及相应的选课人数。

SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno; 

10、查询选修了3门以上课程的学生的学号。

SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(Cno)>3;

---

连接查询

11、查询选修2号课程且成绩在90分以上的所有学生。

SELECT * FROM Student,SC WHERE Student.Sno=SC.Sno AND Cno='2' AND SC.Grade>90 

12、查询每个学生的学号、姓名、选修的课程名及成绩。

SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno

---

嵌套查询

13、查询与“刘晨”在同一个系学习的学生。

SELECT * FROM Student WHERE Sdept IN
(SELECT Sdept FROM Student WHERE Sname='刘晨')

14、查询选修了课程名为“信息系统”的学生学号和姓名。

SELECT Sno,Sname FROM Student WHERE Sno IN
(SELECT Sno FROM SC WHERE Cno IN 
  (SELECT Cno FROM Course WHERE Cname='信息系统'
))

15、找出每个学生超过他选修课平均成绩的课程号。

SELECT Cno FROM SC x WHERE Grade >=
(SELECT AVG(Grade) FROM SC y WHERE x.Sno=y.Sno)

16、查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄。

SELECT  Sname,Sage FROM Student WHERE Sdept!='CS' AND Sage<ANY (SELECT Sage FROM Student WHERE Sdept='CS')

//聚集函数的效率比all和any高
SELECT Sname,Sage FROM Student WHERE Sdept!='CS' AND Sage< (SELECT MAX(Sage) FROM Student WHERE Sdept='CS' )

17、查询选修了全部课程的学生姓名。

SELECT Sname FROM Student WHERE EXISTS
(SELECT * FROM Course,SC WHERE Course.Cno=SC.Cno)

18、查询选修了全部课程的学生姓名。

SELECT Sname FROM Student WHERE NOT EXISTS
(SELECT * FROM Course WHERE NOT EXISTS
(SELECT * FROM SC WHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno))

19、查询至少选修了学生200215122选修的全部课程的学生号码。

SELECT DISTINCT Sno FROM SC x WHERE NOT EXISTS
(SELECT * FROM SC y WHERE y.Sno='200215122' AND NOT EXISTS
(SELECT * FROM SC z WHERE z.Sno=x.Sno AND z.Cno=y.Cno))

---

集合查询

集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同。
UNION:并操作
INTERSECT:交操作
EXCEPT:差操作
20、查询计算机科学系的学生及年龄不大于19岁的学生。

SELECT * FROM Student　WHERE Sage<=19
UNION 
SELECT * FROM Student WHERE Sdept='CS'