SQL语句复习

 

--创建表

--[例5]-- 建立“学生”表Student，学号是主码，姓名取值唯一。

create table Student

(Sno char(9) primary key,

       /*列级完整性约束条件*/

Sname char(20) unique,

       /*Sname取唯一值*/

Ssex char(2),

Sage smallint,

Sdept char(20)

)

Go
--[例6]-- 建立一个“课程”表Course

create table Course

(Cno char(4) primary key,

Cname char(40),

Cpno char(4),        --先修课

Ccredit smallint,

foreign key(Cpno) references Course(Cno)

)

Go
--[例7]-- 建立一个“学生选课”表SC

create table SC

(Sno char(9),

Cno char(4),

Grade smallint,

primary key(Sno,Cno),

    /*主码由两个属性构成，

       必须作为表级完整性进行定义*/

foreign key(Sno)references Student(Sno),

    /* 表级完整性约束条件，Sno是外码，

       被参照表是Student */

foreign key(Cno)references Course(Cno)

       /* 表级完整性约束条件， Cno是外码，

       被参照表是Course*/

);

Go

 

----向表中添加数据

----向学生表中添加数据

insert student

       (Sno,Sname,Ssex,Sage,Sdept)

values

       ('200215121','李勇','男',20,'CS')

go

insert student

       (Sno,Sname,Ssex,Sage,Sdept)

values

       ('200215122','刘晨','女',19,'CS')

go

insert student

       (Sno,Sname,Ssex,Sage,Sdept)

values

       ('200215123','王敏','女',18,'MA')

go

insert student

       (Sno,Sname,Ssex,Sage,Sdept)

values

       ('200215125','张立','男',19,'CS')

go

----向课程表中添加数据

---按先修课的顺序添加课程，否则添加不进去！！！

insert course

       (Cno,Cname,Cpno,Ccredit)

values

       ('2','数学',null,2)

go

insert course

       (Cno,Cname,Cpno,Ccredit)

values

       ('6','数据处理',null,2)

go

insert course

       (Cno,Cname,Cpno,Ccredit)

values

       ('4','操作系统','6',3)

go

insert course

       (Cno,Cname,Cpno,Ccredit)

values

       ('7','Pascal语言','6',4)

go

insert course

       (Cno,Cname,Cpno,Ccredit)

values

       ('5','数据结构','7',4)

go

insert course

       (Cno,Cname,Cpno,Ccredit)

values

       ('1','数据库','5',4)

go

insert course

       (Cno,Cname,Cpno,Ccredit)

values

       ('3','信息系统','1',4)

go

----向学生选课表中添加数据

insert SC

       (Sno,Cno,Grade)

values

       ('200215121','1',92)

go

insert SC

       (Sno,Cno,Grade)

values

       ('200215121','2',85)

go

insert SC

       (Sno,Cno,Grade)

values

       ('200215121','3',88)

go

insert SC

       (Sno,Cno,Grade)

values

       ('200215122','2',90)

go

insert SC

       (Sno,Cno,Grade)

values

       ('200215122','3',80)

Go

 

---插入元组

--［例1］--将一个新学生元组

--（学号：200215128；姓名：陈冬；性别：男；

--所在系：IS；年龄：18岁）插入到Student表中。

 

INSERT

INTO Student

       (Sno,Sname,Ssex,Sdept,Sage)

VALUES

       ('200215128','陈冬','男','IS',18)

go

 

--［例2］

--将学生张成民的信息插入到Student表中。

INSERT

INTO Student

VALUES ('200215126','张成民','男',18,'CS')

go

--［例3］--插入一条选课记录( '200215128'，'1 ')。

INSERT

INTO SC(Sno,Cno)

VALUES ('200215128','1')

go

/*

INSERT

INTO SC

VALUES ('200215128 ','1 ',NULL)*/

 

--[例4] --对每一个系，求学生的平均年龄，并把结果存入数据库。

--第一步：建表

CREATE TABLE Dept_age

        (Sdept CHAR(15),   /* 系名*/

         Avg_age SMALLINT); /*学生平均年龄*/

 

--第二步：插入数据

INSERT

INTO Dept_age(Sdept,Avg_age)

SELECT Sdept,AVG(Sage)

FROM Student

GROUP BY Sdept

Go

 

--[例5]-- 将学生200215121的年龄改为22岁

UPDATE Student

SET Sage=22

WHERE Sno='200215121'

go

--[例6]-- 将所有学生的年龄增加1岁

UPDATE Student

SET Sage= Sage+1

Go

 

---数据查询

--选出所有属性列

--[例3] 查询全体学生的详细记录。

SELECT Sno,Sname,Ssex,Sage,Sdept

FROM Student;

go

--或者-->

SELECT *

FROM Student;

go

--查询经过计算的值

--[例4] 查全体学生的姓名及其出生年份。

SELECT Sname,2010-Sage    /*假定当年的年份为2010年*/

FROM Student;

Go

 

--[例6] --查询选修了课程的学生学号。

SELECT Sno   FROM SC

--     等价于：

SELECT ALL Sno FROM SC

 

--指定DISTINCT关键词，去掉表中重复的行

SELECT DISTINCT Sno FROM SC

go

--［例7］ --查询计算机科学系全体学生的名单。

SELECT Sname

FROM Student

WHERE Sdept='CS'

Go

 

--[例8] 

--查询所有年龄在20岁以下的学生姓名及其年龄。

SELECT Sname,Sage

FROM    Student   

WHERE Sage < 20

Go

 

--[例10] --查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄

SELECT Sname,Sdept,Sage

FROM     Student

WHERE   Sage BETWEEN 20 AND 23

Go

 

--[例12]--查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECT Sname,Ssex

FROM Student

WHERE Sdept IN ( 'IS','MA','CS' )

Go

可改写为：

SELECT Sname,Ssex

FROM   Student

WHERE Sdept='IS' OR Sdept= 'MA' OR Sdept= 'CS'

Go

 

--[例15]查询所有姓刘学生的姓名、学号和性别。

SELECT Sname,Sno,Ssex

FROM Student

WHERE Sname LIKE '刘%'

Go

 

--[例17] 查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECT Sname,Sno

FROM Student

WHERE Sname LIKE '__阳%'

Go

 

--[例21]--某些学生选修课程后没有参加考试，所以有选课记录，但没 有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECT Sno,Cno

FROM SC

WHERE Grade IS NULL

Go

 

--［例25］ --查询全体学生情况，查询结果按所在系的系号升序排列，

--同一系中的学生按年龄降序排列。

SELECT *

FROM Student

ORDER BY Sdept,Sage DESC

Go

 

--[例29] 查询选修1号课程的学生最高分数。

SELECT MAX(Grade)

FROM SC

WHERE Cno= '1'   

Go

 

--[例31] 求各个课程号及相应的选课人数。

SELECT Cno,COUNT(Sno)

FROM    SC

GROUP BY Cno

Go

 

--[例32] 查询选修了3门以上课程的学生学号。

SELECT Sno

FROM SC

GROUP BY Sno

HAVING COUNT(*)>3   go

 

--[例37]

查询选修2号课程且成绩在90分以上的所有学生SELECT Student.Sno,Sname

FROM Student,SC

WHERE Student.Sno= SC.Sno AND /* 连接谓词*/

SC.Cno= '2' AND SC.Grade > 90 /* 其他限定 */

go

 

--[例38]查询每个学生的学号、姓名、选修的课程名及成绩

SELECT Student.Sno,Sname,Cname,Grade

FROM    Student,SC,Course    /*多表连接*/

WHERE Student.Sno = SC.Sno

       and SC.Cno = Course.Cno

go

 

--[例39] 查询与“刘晨”在同一个系学习的学生

SELECT Sno,Sname,Sdept

FROM Student

WHERE Sdept IN

          (SELECT Sdept

          FROM Student

          WHERE Sname='刘晨')

go

 --用自身连接完成[例39]查询要求

SELECT S1.Sno,S1.Sname,S1.Sdept

FROM     Student S1,Student S2

WHERE S1.Sdept = S2.Sdept AND

       S2.Sname = '刘晨'

Go

 

--[例43] --查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

用聚集函数

SELECT Sname,Sage

FROM Student

WHERE Sage <

            (SELECT MIN(Sage)

             FROM Student

             WHERE Sdept='CS')

       AND Sdept <>'CS'

Go

 

--[例45] 查询没有选修1号课程的学生姓名。

SELECT Sname

FROM Student

WHERE NOT EXISTS

          (SELECT *

          FROM SC

          WHERE Sno = Student.Sno AND Cno='1')

go

 

--[例48] 查询计算机科学系的学生及年龄不大于19岁的学生。

--方法一：

SELECT *

FROM Student

WHERE Sdept= 'CS'

 

UNION --ALL --将多个查询结果合并起来时，保留重复元组

 

SELECT *

FROM Student

WHERE Sage<=19

go

 

--方法二：

SELECT DISTINCT *

FROM Student

WHERE Sdept= 'CS' OR Sage<=19

go

 

--[例49] 查询选修了课程1或者选修了课程2的学生。

SELECT Sno

FROM SC

WHERE Cno='1'

 

UNION

 

SELECT Sno

FROM SC

WHERE Cno='2'

go