SQL语句实例
欧阳光明(2021.03. 07)
表操作
例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)
*欧阳光明*创编2021.03.07
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子句)
*欧阳光明*创编2021.03.07
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
https://www.docsj.com/doc/074412467.html,O=https://www.docsj.com/doc/074412467.html,O
例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
*欧阳光明*创编2021.03.07
例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岁.籍贯是湖南或湖北的学生的姓名和性别。(条件比较运算符V和逻辑运算符AND (与),此外还可以使用的运算符有: > (大于). >=(大于等于). <=(小于等于). <> (不等于).NOT (非).OR (或)等。
谓词LIKE只能与字符串联用,常常是“V列名
> 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 (* 湖南1, * 湖北
1)
例22找出学生表中籍贯是空值的学生的姓名和性别。(在SQL 中不能使用条件:V列名> =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
*欧阳光明*创编2021.03.07
*欧阳光明*创编2021.03.07
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 C山西5 , 4河北J AND GRADE > =
90 AND STUDENTS.SNO=ENROLLS.SNO
例28查出课程成绩在80分以上的女学生的姓名.课程名和成
绩。(FROM子句中的子查询)
SELECT SNAME,CNAME, GRADE
FROM (SELECT SNAME, CNAME , GRADE
FROM STUDENTS, ENROLLS,COURSES
*欧阳光明*创编2021.03.07
*欧阳光明*创编
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
*欧阳光明*创编
*欧阳光明*创编2021.03.07
例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 COUNTS) >= 3
相关子查询
例37查询没有选任何课程的学生的学号和姓名。(当一个子查询涉及到一个来自外部查询的列肘,称为相关子查询(Correlated Subquery)
o相关子查询要用到存在测试谓词EXISTS和NOT EXISTS ,以及ALL . ANY ( SOME )等。)
*欧阳光明*创编2021.03.07
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二https://www.docsj.com/doc/074412467.html,O)
例39要求给出一张学生、籍贯列表,该表中的学生的籍贯省份, 也是其他一些学生的籍贯省份。
SELECT SNAME, BPLACE
FROM STUDENTS A
WHERE EXISTS
(SELECT 睿
FROM STUDENTS B
WHERE A.BPLACE二B.BPLACE AND
A.SNOo
B.SNO)
*欧阳光明*创编2021.03.07
例40找出选修了全部课程的学生的姓名。
本查询可以改为:查询这样一些学生,没有一门课程杲他不选修的。
SELECT SNAME
FROM STUDENTS
WHERE NOT EXISTS
(SELECT 睿
FROM COURSES
WHERE NOT EXISTS
(SELECT *
FROM ENROLLS
WHERE ENROLLS.SNO = STUDENTS.SNO
AND https://www.docsj.com/doc/074412467.html,O = https://www.docsj.com/doc/074412467.html,O))
关系代数运算
例41设有某克场工作人员的两张表:营业员表SP.SUBORD和营销经理表SP_MGR ,其关系数据模式如下:
SP_SUBORD (SALPERSJD, SALPERS_NAME, MANAGERJD, OFFICE)
SP_MGR (SALPERS_ID, SALPERS_NAME, MANAGERJD, OFFICE)
其中,属性SALPERSJD为工作人员的编号,
https://www.docsj.com/doc/074412467.html,为工作人员的姓名,MANAGERJD为所在部门经
理的编号,OFFICE为工作地点。
*欧阳光明*创编2021.03.07
*欧阳光明*创编2021.03.07
若查询全部商场工作人员,可以用下面的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
*欧阳光明*创编2021.03.07