SQL 语句
教学数据库中有三个基本表:
S(S#, SNAME, AGE, SEX)
SC(S#, C#, GRADE)
C(C#, CNAME, TEACHER)
1、基本表的创建,可用下列语句来创建:
CREATE TABLE S
(S# CHAR(4) NOT NULL, #长度为4的定长字符串
SNAME CHAR(8) NOT NULL,
AGE SMALLINT, #短整型,INT为长整型
SEX CHAR(1),
PRIMARY KEY(S#) );
#SQL中允许列值为空,不允许某一列的值为空时,可加上关键字"NOT NULL"
CREATE TABLE C
(C# CHAR(4),
CNAME CHAR(10) NOT NULL,
TEACHER CHAR(8),
PRIMARY KEY(C#) );
CREATE TABLE SC
(S# CHAR(4),
C# CHAR(4),
GRADE SMALLINT,
PRIMARY KEY(S#, C#),
FOREIGN KEY(S#)REFERENCES S(S#),
FOREIGN KEY(C#)REFERENCES C(C#),
CHECK(GRADE BETWEEN 0 AND 100) );
#上面的CHECK检查子句,指出成绩GRADE的值应在0~100之间
2、基本表结构的修改
(1)在基本表S中增加一个地址列(ADDRESS):#新增加的列不能定义为NOT NULL
ALTER TABLE S ADD ADDRESS VARCHAR(30); #VARCHAR(30)最大长度为30的字符串
(2)在基本表S中删除年龄列(AGE),并且把引用该列的所有视图和约束也一起删除:
ALTER TABLE S DROP AGE CASCADE;
#CASCADE表示在基本表中删除某列时,所有引用到该列的视图和约束也一起自动删除;
#RESTRICT方式表示在没有视图或约束引用到该属性时,才能删除该列,否则拒绝删除操作。
(3)在基本表S中,S#的长度修改为6:
ALTER TALBE S MODIFY S# CHAR(6);
3、基本表的撤消
(1)撤消基本表S:
DROP TALBE S RESTRICT; ##CASCADE,RESTRICT同上
4、索引表操作
(1)对基本表S的列S#建立索引,索引键名为S#_INDEX:
CREATE INDEL S#_INDEX ON S(S#);
(2)对基本表SC的(S#, C#)建立索引:
CREATE UNIQUE INDEX SC_INDEX ON SC(S# ASC, C# DESC);
(3)撤消索引S#_INDEX和SC_INDEX:
DROP INDEX S#_INDEX, SC_INDEX;
5、SQL的数据查询
(1)检索学习课程号为C2的学生学号与成绩:
SELECT S#, GRADE
FROM SC
WHERE C# = 'C2';
(2)检索学习课程号为C2的学生学号与姓名:
SELECT S.S#, SNAME
FROM S, SC
WHERE S.S = SC.S AND C# = C2;
或者:
SELECT S#, SNAME
FROM S
WHERE S# IN (SELECT S#
FROM SC
WHERE C# = 'C2';);
(3)检索选修课程名为MATHS的学生学号与姓名:
SELECT S.S#, SNAME
FROM S, SC,C
WHERE S.S# = SC.S# AND SC.C# = C.C# AND CNAME = 'MATHS';
或者:
SELECT S#, SNAME
FORM S
WHERE S# IN (SELECT S#
FROM SC
WHERE C# IN (SELECT C#
FROM C
WHERE CNAME = 'MATHS'));
(4)检索选修课程号为C2或C4的学生学号:
SELECT S#
FROM SC
WHERE C# = 'C2' OR C# = 'C4';
(5)检索至少选修课程号为C2和C4的学生学号:
SELECT X.S#
FROM SC AS X, SC AS Y
WHERE X.S# = Y.S# AND X.C# = 'C2' AND Y.C# = 'C4';
(6)检索不学C2课程的学生姓名与年龄:
SELECT SNAME, AGE
FROM S
WHERE S# NOT IN(SELECT S#
FROM SC
WHERE C# = 'C2');
或者:
SELECT SNAME, AGE
FROM S
WHERE NOT EXISTS (SELECT *
FROM SC
WHERE SC.S# = S.S# AND C# = 'C2');
(7)检索学习全部课程的学生姓名:
SELECT SNAME FROM S
WHERE NOT EXISTS
(SELECT * FROM C
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE SC.S# = S.S# AND SC.C# = C.C#));
(8)统计每一年龄选修课程的学生人数:
SELECT AGE, COUNT(DISTINCT S.S#)
FROM S,SC
WHERE S.S# = SC.S#
GROUP BY AGE;
(9)求基本表Sk 男同学的每一年龄组(超过50人)有多少人?要求查询结果按人数升序排列,人数相同按年龄降序排列:
SELECT AGE, COUNT(S#)
FROM S
WHERE SEX = 'M'
GROUP BY AGE
HAVING COUNT(*) > 50
ORDER BY 2, AGE DESC #2表示对SELECT子句中的第2个属性值进行排序
(10)
6、SQL的数据更新
(1)往基本表SC中插入一个元组(S4, C6, 90):
INSERT INTO SC(S#, C#, GRADE)
VALUES('S4', 'C6', 90)
(2)在基本表SC中,把平均成绩大于80分的男学生的学号和平均成绩存入另一个已知的基本表S_GRADE(S#, AVG_GRADE):
INSERT INTO S_GRADE(S#, AVG_GRADE)
SELECT S#, AVG(GRADE)
FROM SC
WHERE S# IN
(SELECT S# FROM S WHERE SEX = 'M')
GROUT BY S#
HAVING AVG(GRADE) > 80
(3)把课程名为MATHS的成绩从基本表SC中删除
DELETE FROMS SC
WHERE C# IN (SELECT C# FROM C
WHERE CNAME = 'MATHS');
(4)把C4课程中小于该课程平均成绩的成绩元组从基本表SC中删除:
DELETE FROM SC
WHERE C# = 'C4'
AND GRADE < (SELECT AVG(GRADE) FROM SC
WHERE C# = 'C4')
(5)把C5课程的任课教师姓名改为WU:
UPDATE C SET TEACHER = 'WU'
WHERE C# = 'C5';
(6)把女同学的成绩提高10%:
UPDATE SC SET GRADE = GRADE * 1.1
WHERE S# IN (SELECT S# FROM S
WHERE SEX = 'F')
(7)当C4课的成绩低于该门课程平均成绩时,提高5%:
UPDATE SC SET GRADE = GRADE * 1.05
WHERE C# = 'C4'
AND GRADE < (SELECT AVG(GRADE) FROM SC
WHERE C# = 'C4')
(8)在C中,把课程号为C5的元组修改为(C5, DB, LIU)
UPDATE C
SET ROW = (C5, DB, LIU)
WHERE C# = 'C5';
7、视图的创建
(1)对于教学数据库中基本表S, SC, C,用户经常要用到S#, SNAME, CNAME 和 GRADE等列的数据,那么可用下列语句创建视图:
CREATE VIEW SUTDENT_GRADE(S#, SNAME, CNAME, GRADE)
AS SELECT S.S#, SNAME, CNAME, GRADE
FROM S, SC, C
WHERE S.S# = SC.S# AND SC.C# = C.C#;
(2)撤消SUTDENT_GRADE视图:
DROP VIEW SUTDENT_GRADE;
(3)如果视图是从单个基本表中只使用选择、投影操作导出且包含了基本表的主键,则这样的视图称为“行列子集视图”,且可以被执行更新操作。
允许用户更新的视图在定义时必须加上“WITH CHECK OPTION”短语。