SQL语句学习手册实例
作者:方佳玮 文章来源:互联网 上传日期:2006-07-20
<script type=text/javascript> </script> |
SQL语句学习手册实例版
表操作
例
1
对于表的教学管理数据库中的表
STUDENTS
,可以定义如下:
CREATE TABLE STUDENTS
(SNO NUMERIC (6, 0) NOT NULL
SNAME CHAR (8) NOT NULL
AGE NUMERIC(3,0)
SEX CHAR(2)
BPLACE CHAR(20)
PRIMARY KEY(SNO))
例
2
对于表的教学管理数据库中的表
ENROLLS
,可以定义如下:
CREATE TABLE ENROLLS
(SNO NUMERIC(6,0) NOT NULL
CNO CHAR(4) NOT NULL
GRADE INT
PRIMARY KEY(SNO,CNO)
FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO)
FOREIGN KEY(CNO) REFERENCES COURSES(CNO)
CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100)))
例
3
根据表的
STUDENTS
表,建立一个只包含学号、姓名、年龄的女学生表。
CREATE TABLE GIRL
AS SELECT SNO, SNAME, AGE
FROM STUDENTS
WHERE SEX='
女
';
例
4
删除教师表
TEACHER
。
DROP TABLE TEACHER
例
5
在教师表中增加住址列。
ALTER TABLE TEACHERS
ADD (ADDR CHAR(50))
例
6
把
STUDENTS
表中的
BPLACE
列删除,并且把引用
BPLACE
列的所有视图和约束也一起删除。
ALTER TABLE STUDENTS
DROP BPLACE CASCADE
例
7
补充定义
ENROLLS
表的主关键字。
ALTER TABLE ENROLLS
ADD PRIMARY KEY (SNO,CNO)
;
视图操作(虚表)
例
9
建立一个只包括教师号、姓名和年龄的视图
FACULTY
。
(
在视图定义中不能包含
ORDER BY
子句
)
CREATE VIEW FACULTY
AS SELECT TNO, TNAME, AGE
FROM TEACHERS
例
10
从学生表、课程表和选课表中产生一个视图
GRADE_TABLE
,
它包括学生姓名、课程名和成绩。
CREATE VIEW GRADE_TABLE
AS SELECT SNAME,CNAME,GRADE
FROM STUDENTS,COURSES,ENROLLS
WHERE STUDENTS.SNO
=
ENROLLS.SNO AND
COURSES.CNO=ENROLLS.CNO
例
11
删除视图
GRADE_TABLE
DROP VIEW GRADE_TABLE RESTRICT
索引操作
例
12
在学生表中按学号建立索引。
CREATE UNIQUE INDEX ST
ON STUDENTS (SNO,ASC)
例
13
删除按学号所建立的索引。
DROP INDEX ST
数据库模式操作
例
14
创建一个简易教学数据库的数据库模式
TEACHING_DB
,属主为
ZHANG
。
CREATE SCHEMA TEACHING_DB AUTHRIZATION ZHANG
例
15
删除简易教学数据库模式
TEACHING_DB
。((
1
)选用
CASCADE
,即当删除数据库模式时,则本数据库模式和其下属的基本表、视图、索引等全部被删除。(
2
)选用
RESTRICT
,即本数据库模式下属的基本表、视图、索引等事先已清除,才能删除本数据库模式,否则拒绝删除。)
DROP SCHEMA TEACHING_DB CASCADE
单表操作
例
16
找出
3
个学分的课程号和课程名。
SELECT CNO, CNAME
FROM
COURSES
WHERE
CREDIT
=
3
例
17
查询年龄大于
22
岁的学生情况。
SELECT *
FROM STUDENTS
WHERE AGE
>
22
例
18
找出籍贯为河北的男生的姓名和年龄。
SELECT SNAME, AGE
FROM
STUDENTS
WHERE
BPLACE
=
'
河北
' AND SEX
=
'
男
'
例
19
找出年龄在
20
~
23
岁之间的学生的学号、姓名和年龄,并按年龄升序排序。
(ASC
(升序)或
DESC
(降序)声明排序的方式,缺省为升序。
)
SELECT SNO, SNAME, AGE
FROM STUDENTS
WHERE AGE BETWEEN 20 AND 23
ORDER BY AGE
例
20
找出年龄小于
23
岁、籍贯是湖南或湖北的学生的姓名和性别。(条件比较运算符=、<
和逻辑运算符
AND
(与),此外还可以使用的运算符有:>(大于)、>=(大于等于)、<=(小于等于)、<>(不等于)、
NOT
(非)、
OR
(或)等。
谓词
LIKE
只能与字符串联用,常常是
“
<列名>
LIKE pattern”
的格式。特殊字符
“_”
和
“%”
作为通配符。
谓词
IN
表示指定的属性应与后面的集合(括号中的值集或某个查询子句的结果)中的某个值相匹配,实际上是一系列的
OR
(或)的缩写。谓词
NOT IN
表示指定的属性不与后面的集合中的某个值相匹配。
谓词
BETWEEN
是
“
包含于
…
之中
”
的意思。)
SELECT SNAME, SEX
FROM STUDENTS
WHERE AGE
<
23 AND BPLACE LIKE'
湖%
'
或
SELECT SNAME, SEX
FROM STUDENTS
WHERE AGE
<
23 AND BPLACE IN
(
'
湖南
'
,
'
湖北
'
)
例
22
找出学生表中籍贯是空值的学生的姓名和性别。(在
SQL
中不能使用条件:<列名>=
NULL
。在
SQL
中只有一个特殊的查询条件允许查询
NULL
值:)
SELECT SNAME, SEX
FROM STUDENTS
WHERE BPLACE IS NULL
多表操作
例
23
找出成绩为
95
分的学生的姓名。(子查询)
SELECT SNAME
FROM
STUDENTS
WHERE
SNO
=
(SELECT SNO
FROM ENROLLS
WHERE GRADE
=
95)
例
24
找出成绩在
90
分以上的学生的姓名。
SELECT SNAME
FROM STUDENTS
WHERE SNO IN
(SELECT SNO
FROM ENROLLS
WHERE GRADE
>
90)
或
SELECT SNAME
FROM STUDENTS
WHERE SNO
=
ANY
(SELECT SNO
FROM ENROLLS
WHERE GRADE
>
90)
例
25
查询全部学生的学生名和所学课程号及成绩。(连接查询)
SELECT SNAME, CNO, GRADE
FROM STUDENTS, ENROLLS
WHERE STUDENTS.SNO
=
ENROLLS.SNO
例
26
找出籍贯为山西或河北,成绩为
90
分以上的学生的姓名、籍贯和成绩。(当构造多表连接查询命令时,必须遵循两条规则。第一,连接条件数正好比表数少
1
(若有三个表,就有两个连接条件
)
;第二,若一个表中的主关键字是由多个列组成,则对此主关键字中的每一个列都要有一个连接条件(也有少数例外情况))
SELECT SNAME, BPLACE, GRADE
FROM STUDENTS, ENROLLS
WHERE BPLACE IN (‘
山西
'
,
‘
河北
') AND GRADE
>=
90 AND
STUDENTS.SNO=ENROLLS.SNO
例
28
查出课程成绩在
80
分以上的女学生的姓名、课程名和成绩。(
FROM
子句中的子查询)
SELECT SNAME,CNAME, GRADE
FROM (SELECT SNAME, CNAME , GRADE
FROM STUDENTS, ENROLLS,COURSES
WHERE SEX
=
'
女
')
AS TEMP (SNAME, CNAME,GRADE)
WHERE GRADE
>
80
表达式与函数的使用
例
29
查询各课程的学时数。(算术表达式由算术运算符+、-、
*
、/与列名或数值常量所组成。)
SELECT CNAME,COURSE_TIME
=
CREDIT*16
FROM COURSES
例
30
找出教师的最小年龄。(内部函数:
SQL
标准中只使用
COUNT
、
SUM
、
AVG
、
MAX
、
MIN
函数,称之为聚集函数(
Set Function
)。
COUNT
函数的结果是该列统计值的总数目,
SUM
函数求该列统计值之和,
AVG
函数求该列统计值之平均值,
MAX
函数求该列最大值,
MIN
函数求该列最小值。)
SELECT MIN(AGE)
FROM TEACHERS
例
31
统计年龄小于等于
22
岁的学生人数。(统计)
SELECT COUNT(*)
FROM STUDENTS
WHERE AGE <
=
22
例
32
找出学生的平均成绩和所学课程门数。
SELECT SNO, AVG(GRADE), COURSES
=
COUNT(*)
FROM ENROLLS
GROUP BY SNO
例
34
找出年龄超过平均年龄的学生姓名。
SELECT SNAME
FROM STUDENTS
WHERE AGE
>
(SELECT AVG(AGE)
FROM STUDENTS)
例
35
找出各课程的平均成绩,按课程号分组,且只选择学生超过
3
人的课程的成绩。(
GROUP BY
与
HAVING
GROUP BY
子句把一个表按某一指定列(或一些列)上的值相等的原则分组,然后再对每组数据进行规定的操作。
GROUP BY
子句总是跟在
WHERE
子句后面,当
WHERE
子句缺省时,它跟在
FROM
子句后面。
HAVING
子句常用于在计算出聚集之后对行的查询进行控制。)
SELECT CNO, AVG(GRADE), STUDENTS
=
COUNT(*)
FROM ENROLLS
GROUP BY CNO
HAVING COUNT(*) >= 3
相关子查询
例
37
查询没有选任何课程的学生的学号和姓名。(当一个子查询涉及到一个来自外部查询的列时,称为相关子查询(
Correlated Subquery)
。相关子查询要用到存在测试谓词
EXISTS
和
NOT EXISTS
,以及
ALL
、
ANY
(
SOME
)等。)
SELECT SNO, SNAME
FROM STUDENTS
WHERE NOT EXISTS
(SELECT *
FROM ENROLLS
WHERE ENROLLS.SNO=STUDENTS.SNO)
例
38
查询哪些课程只有男生选读。
SELECT DISTINCT CNAME
FROM COURSES C
WHERE '
男
'
=
ALL
(SELECT SEX
FROM ENROLLS
,
STUDENTS
WHERE ENROLLS.SNO=STUDENTS.SNO AND
ENROLLS.CNO=C.CNO)
例
39
要求给出一张学生、籍贯列表,该表中的学生的籍贯省份,也是其他一些学生的籍贯省份。
SELECT SNAME, BPLACE
FROM STUDENTS A
WHERE EXISTS
(SELECT *
FROM STUDENTS B
WHERE A.BPLACE=B.BPLACE AND
A.SNO < > B.SNO)
例
40
找出选修了全部课程的学生的姓名。
本查询可以改为:查询这样一些学生,没有一门课程是他不选修的。
SELECT SNAME
FROM STUDENTS
WHERE NOT EXISTS
(SELECT *
FROM COURSES
WHERE NOT EXISTS
(SELECT *
FROM ENROLLS
WHERE ENROLLS.SNO
=
STUDENTS.SNO
AND ENROLLS.CNO
=
COURSES.CNO))
关系代数运算
例
41
设有某商场工作人员的两张表:营业员表
SP_SUBORD
和营销经理表
SP_MGR
,其关系数据模式如下:
SP_SUBORD (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)
SP_MGR (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)
其中,属性
SALPERS_ID
为工作人员的编号
, SALPERS_NAME
为工作人员的姓名
, MANAGER_ID
为所在部门经理的编号
, OFFICE
为工作地点。
若查询全部商场工作人员,可以用下面的
SQL
语句:
(SELECT * FROM SP_SUBORD)
UNION
(SELECT * FROM SP_MGR)
或等价地用下面的
SQL
语句:
SELECT *
FROM (TABLE SP_SUBORD UNION TABLE SP_MGR)
(
2
)
INTERSECT
(SELECT * FROM SP_SUBORD)
INTERSECT
(SELECT * FROM SP_MGR)
或等价地用下面的
SQL
语句:
SELECT *
FROM (TABLE SP_SUBORD INTERSECT TABLE SP_MGR)
或用带
ALL
的
SQL
语句:
(SELECT * FROM SP_SUBORD)
INTERSECT ALL
(SELECT * FROM SP_MGR)
或
SELECT *
FROM (TABLE SP_SUBORD INTERSECT ALL TABLE SP_MGR)
(
3
)
EXCEPT
(SELECT * FROM SP_MGR)
EXCEPT
(SELECT * FROM SP_SUBORD)
或等价地用下面的
SQL
语句:
SELECT *
FROM (TABLE SP_MGR EXCEPT TABLE SP_ SUBORD)
或用带
ALL
的
SQL
语句:
(SELECT * FROM SP_MGR)
EXCEPT ALL
(SELECT * FROM SP_SUBORD)
例
42
查询籍贯为四川、课程成绩在
80
分以上的学生信息及其成绩。(自然连接)
(SELECT * FROM STUDENTS
WHERE BPLACE=‘
四川
')
NATURAL JOIN
(SELECT * FROM ENROLLS
WHERE GRADE >=80)
例3.43
列出全部教师的姓名及其任课的课程号、班级。
(外连接与外部并外连接允许在结果表中保留非匹配元组,空缺部分填以
NULL
。外连接的作用是在做连接操作时避免丢失信息。
外连接有
3
类:
(
1
)左外连接(
Left Outer Join
)。连接运算谓词为
LEFT [OUTER] JOIN
,其结果表中保留左关系的所有元组。
(
2
)右外连接(
Right Outer Join
)。连接运算谓词为
RIGHT [OUTER] JOIN
,其结果表中保留右关系的所有元组。
(
3
)全外连接(
Full Outer Join
)。连接运算谓词为
FULL [OUTER] JOIN
,其结果表中保留左右两关系的所有元组。)
SELECT TNAME, CNO, CLASS
FROM TEACHERS LEFT OUTER JOIN TEACHING USING (TNO)
SQL
的数据操纵
例
44
把教师李映雪的记录加入到教师表
TEACHERS
中。(插入)
INSERT INTO TEACHERS
VALUES(1476
,
'
李映雪
'
,
44
,
'
副教授
')
例
45
成绩优秀的学生将留下当教师。
INSERT INTO TEACHERS (TNO
,
TNAME)
SELECT DISTINCT SNO
,
SNAME
FROM STUDENTS
,
ENROLLS
WHERE STUDENTS.SNO
=
ENROLLS.SNO AND GRADE
>=
90
例
47
把所有学生的年龄增加一岁。(修改)
UPDATE STUDENTS
SET AGE
=
AGE+1
例
48
学生张春明在数据库课考试中作弊,该课成绩应作零分计。
UPDATE ENROLLS
SET GRADE
=
0
WHERE CNO
=
'C1' AND
'
张春明
'
=
(SELECT SNAME
FROM STUDENTS
WHERE STUDENTS.SNO=ENROLLS.SNO)
例
49
从教师表中删除年龄已到
60
岁的退休教师的数据。(删除)
DELETE FROM TEACHERS
WHERE AGE
>=
60
SQL
的数据控制
例
50
授予
LILI
有对表
STUDENTS
的查询权。(表/视图特权的授予
一个
SQL
特权允许一个被授权者在给定的数据库对象上进行特定的操作。授权操作的数据库对象包括:表
/
视图、列、域等。授权的操作包括:
INSERT
、
UPDATE
、
DELETE
、
SELECT
、
REFERENCES
、
TRIGGER
、
UNDER
、
USAGE
、
EXECUTE
等。其中
INSERT
、
UPDATE
、
DELETE
、
SELECT
、
REFERENCES
、
TRIGGER
有对表做相应操作的权限,故称为表特权。)
GRANT SELECT ON STUDENTS
TO LILI
WITH GRANT OPTION
例
51
取消
LILI
的存取
STUDENTS
表的特权。
REVOKE ALL
ON STUDENTS
FROM LILI CASCADE
|