SQL语句大全实例.docx
《SQL语句大全实例.docx》由会员分享，可在线阅读，更多相关《SQL语句大全实例.docx（19页珍藏版）》请在冰豆网上搜索。
SQL语句大全实例
SQL语句实例
表操作
例1 对于表的教学管理数据库中的表STUDENTS，可以定义如下：
CREATE TABLE STUDENTS
（SNO NUMERIC（6,0）NOTNULL
SNAME CHAR（8）NOTNULL
AGE NUMERIC（3,0）
SEX CHAR
（2）
BPLACE CHAR（20）
PRIMARYKEY（SNO））
例2 对于表的教学管理数据库中的表ENROLLS，可以定义如下：
CREATE TABLE ENROLLS
（SNO NUMERIC（6,0） NOTNULL
O CHAR（4） NOTNULL
GRADE INT
PRIMARYKEY（SNO,O）
FOREIGNKEY（SNO）REFERENCESSTUDENTS（SNO）
FOREIGNKEY（O）REFERENCESCOURSES（O）
CHECK（（GRADEISNULL）OR（GRADEBETWEEN0AND100）））
例3 根据表的STUDENTS表，建立一个只包含学号、、年龄的女学生表。
CREATETABLEGIRL
ASSELECTSNO,SNAME,AGE
FROMSTUDENTS
WHERESEX='女';
例4 删除教师表TEACHER。
DROP TABLE TEACHER
例5 在教师表中增加住址列。
ALTERTABLETEACHERS
ADD（ADDRCHAR（50））
例6 把STUDENTS表中的BPLACE列删除，并且把引用BPLACE列的所有视图和约束也一起删除。
ALTERTABLESTUDENTS
DROPBPLACECASCADE
例7 补充定义ENROLLS表的主关键字。
ALTERTABLEENROLLS
ADDPRIMARYKEY（SNO,O）；
视图操作（虚表）
例9 建立一个只包括教师号、和年龄的视图FACULTY。
（在视图定义中不能包含ORDERBY子句）
CREATEVIEWFACULTY
ASSELECTTNO,TNAME,AGE
FROMTEACHERS
例10 从学生表、课程表和选课表中产生一个视图GRADE_TABLE，它包括学生、课程名和成绩。
CREATEVIEWGRADE_TABLE
ASSELECT SNAME,AME,GRADE
FROM STUDENTS,COURSES,ENROLLS
WHERE STUDENTS.SNO＝ENROLLS.SNOAND
COURSES.O=ENROLLS.O
例11 删除视图GRADE_TABLE
DROPVIEWGRADE_TABLERESTRICT
索引操作
例12 在学生表中按学号建立索引。
CREATE UNIQUE INDEX ST
ONSTUDENTS（SNO,ASC）
例13 删除按学号所建立的索引。
DROPINDEXST
数据库模式操作
例14 创建一个简易教学数据库的数据库模式 TEACHING_DB，属主为ZHANG。
CREATESCHEMATEACHING_DB AUTHRIZATION ZHANG
例15 删除简易教学数据库模式TEACHING_DB。
（
（1）选用CASCADE，即当删除数据库模式时，则本数据库模式和其下属的基本表、视图、索引等全部被删除。
（2）选用RESTRICT，即本数据库模式下属的基本表、视图、索引等事先已清除，才能删除本数据库模式，否则拒绝删除。
）
DROPSCHEMATEACHING_DBCASCADE
单表操作
例16 找出3个学分的课程号和课程名。
SELECTO,AME
FROM COURSES
WHERE CREDIT＝3
例17 查询年龄大于22岁的学生情况。
SELECT *
FROM STUDENTS
WHERE AGE＞22
例18 找出籍贯为的男生的和年龄。
SELECTSNAME,AGE
FROM STUDENTS
WHERE BPLACE＝'' AND SEX＝'男'
例19 找出年龄在20～23岁之间的学生的学号、和年龄，并按年龄升序排序。
（ASC（升序）或DESC（降序）声明排序的方式，缺省为升序。
）
SELECTSNO,SNAME,AGE
FROM STUDENTS
WHERE AGEBETWEEN20AND23
ORDER BY AGE
例20 找出年龄小于23岁、籍贯是或的学生的和性别。
（条件比较运算符＝、＜和逻辑运算符AND（与），此外还可以使用的运算符有：
＞（大于）、＞＝（大于等于）、＜＝（小于等于）、＜＞（不等于）、NOT（非）、OR（或）等。
谓词LIKE只能与字符串联用，常常是“＜列名＞ LIKE pattern”的格式。
特殊字符“_”和“%”作为通配符。
谓词IN表示指定的属性应与后面的集合（括号中的值集或某个查询子句的结果）中的某个值相匹配，实际上是一系列的OR（或）的缩写。
谓词NOTIN表示指定的属性不与后面的集合中的某个值相匹配。
谓词BETWEEN是“包含于…之中”的意思。
）
SELECTSNAME,SEX
FROM STUDENTS
WHERE AGE＜23 AND BPLACE LIKE'湖％'
或
SELECTSNAME,SEX
FROM STUDENTS
WHERE AGE＜23 AND BPLACE IN （''，''）
例22 找出学生表中籍贯是空值的学生的和性别。
（在SQL中不能使用条件：
＜列名＞＝NULL。
在SQL中只有一个特殊的查询条件允许查询NULL值：
）
SELECTSNAME,SEX
FROM STUDENTS
WHERE BPLACEISNULL
多表操作
例23 找出成绩为95分的学生的。
（子查询）
SELECTSNAME
FROM STUDENTS
WHERE SNO＝
（SELECTSNO
FROM ENROLLS
WHERE GRADE＝95）
例24 找出成绩在90分以上的学生的。
SELECT SNAME
FROM STUDENTS
WHERE SNOIN
（SELECTSNO
FROMENROLLS
WHEREGRADE＞90）
或
SELECT SNAME
FROM STUDENTS
WHERE SNO＝ANY
（SELECTSNO
FROMENROLLS
WHEREGRADE＞90）
例25 查询全部学生的学生名和所学课程号及成绩。
（连接查询）
SELECT SNAME,O,GRADE
FROM STUDENTS,ENROLLS
WHERE STUDENTS.SNO＝ENROLLS.SNO
例26 找出籍贯为或，成绩为90分以上的学生的、籍贯和成绩。
（当构造多表连接查询命令时，必须遵循两条规则。
第一，连接条件数正好比表数少1（若有三个表，就有两个连接条件）；第二，若一个表中的主关键字是由多个列组成，则对此主关键字中的每一个列都要有一个连接条件（也有少数例外情况））
SELECT SNAME,BPLACE,GRADE
FROM STUDENTS,ENROLLS
WHERE BPLACEIN（‘’，‘’） AND GRADE＞＝90 AND STUDENTS.SNO=ENROLLS.SNO
例28 查出课程成绩在80分以上的女学生的、课程名和成绩。
（FROM子句中的子查询）
SELECT SNAME,AME,GRADE
FROM （SELECTSNAME,AME,GRADE
FROM STUDENTS,ENROLLS,COURSES
WHERE SEX＝'女'）
ASTEMP（SNAME,AME,GRADE）
WHERE GRADE＞80
表达式与函数的使用
例29 查询各课程的学时数。
（算术表达式由算术运算符＋、－、*、／与列名或数值常量所组成。
）
SELECT AME,COURSE_TIME＝CREDIT*16
FROM COURSES
例30 找出教师的最小年龄。
（部函数：
SQL标准中只使用COUNT、SUM、AVG、MAX、MIN函数，称之为聚集函数（SetFunction）。
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 BYSNO
例34 找出年龄超过平均年龄的学生。
SELECTSNAME
FROMSTUDENTS
WHEREAGE＞
（SELECT AVG（AGE）
FROM STUDENTS）
例35 找出各课程的平均成绩，按课程号分组，且只选择学生超过3人的课程的成绩。
（GROUPBY与HAVING
GROUPBY子句把一个表按某一指定列（或一些列）上的值相等的原则分组，然后再对每组数据进行规定的操作。
GROUPBY子句总是跟在WHERE子句后面，当WHERE子句缺省时，它跟在FROM子句后面。
HAVING子句常用于在计算出聚集之后对行的查询进行控制。
）
SELECTO,AVG（GRADE）,STUDENTS＝COUNT（*）
FROMENROLLS
GROUPBYO
HAVINGCOUNT（*）>=3
相关子查询
例37 查询没有选任何课程的学生的学号和。
（当一个子查询涉及到一个来自外部查询的列时，称为相关子查询（CorrelatedSubquery）。
相关子查询要用到存在测试谓词EXISTS和NOTEXISTS，以及ALL、ANY（SOME）等。
）
SELECTSNO,SNAME
FROM STUDENTS
WHERE NOTEXISTS
（SELECT*
FROMENROLLS
WHEREENROLLS.SNO=STUDENTS.SNO）
例38 查询哪些课程只有男生选读。
SELECTDISTINCTAME
FROM COURSESC
WHERE'男'＝ALL
（SELECTSEX
FROM ENROLLS，STUDENTS
WHEREENROLLS.SNO=STUDENTS.SNOAND
ENROLLS.O=C.O）
例39 要求给出一学生、籍贯列表，该表中的学生的籍贯省份，也是其他一些学生的籍贯省份。
SELECT SNAME,BPLACE
FROM STUDENTSA
WHERE EXISTS
（SELECT*
FROMSTUDENTSB
WHEREA.BPLACE=B.BPLACEAND
A.SNO<>B.SNO）
例40 找出选修了全部课程的学生的。
本查询可以改为：
查询这样一些学生，没有一门课程是他不选修的。
SELECT SNAME
FROM STUDENTS
WHERE NOTEXISTS
（SELECT*
FROMCOURSES
WHERE NOTEXISTS
（SELECT*
FROMENROLLS
WHEREENROLLS.SNO＝STUDENTS.SNO
ANDENROLLS.O＝COURSES.O））
关系代数运算
例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*FROMSP_SUBORD）
UNION
（SELECT*FROMSP_MGR）
或等价地用下面的SQL语句：
SELECT*
FROM（TABLESP_SUBORDUNIONTABLESP_MGR）
（2）INTERSECT
（SELECT*FROMSP_SUBORD）
INTERSECT
（SELECT*FROMSP_MGR）
或等价地用下面的SQL语句：
SELECT*
FROM（TABLESP_SUBORDINTERSECTTABLESP_MGR）
或用带ALL的SQL语句：
（SELECT*FROMSP_SUBORD）
INTERSECTALL
（SELECT*FROMSP_MGR）
或
SELECT*
FROM（TABLESP_SUBORDINTERSECTALLTABLESP_MGR）
（3）EXCEPT
（SELECT*FROMSP_MGR）
EXCEPT
（SELECT*FROMSP_SUBORD）
或等价地用下面的SQL语句：
SELECT*
FROM（TABLESP_MGREXCEPTTABLESP_SUBORD）
或用带ALL的SQL语句：
（SELECT*FROMSP_MGR）
EXCEPT ALL
（SELECT*FROMSP_SUBORD）
例42 查询籍贯为、课程成绩在80分以上的学生信息及其成绩。
（自然连接）
（SELECT*FROMSTUDENTS
WHEREBPLACE=‘’）
NATURALJOIN
（SELECT*FROMENROLLS
WHEREGRADE>=80）
例3.43 列出全部教师的及其任课的课程号、班级。
（外连接与外部并外连接允许在结果表中保留非匹配元组，空缺部分填以NULL。
外连接的作用是在做连接操作时避免丢失信息。
外连接有3类：
（1）左外连接（LeftOuterJoin）。
连接运算谓词为LEFT[OUTER]JOIN，其结果表中保留左关系的所有元组。
（2）右外连接（RightOuterJoin）。
连接运算谓词为RIGHT[OUTER]JOIN，其结果表中保留右关系的所有元组。
（3）全外连接（FullOuterJoin）。
连接运算谓词为FULL[OUTER]JOIN，其结果表中保留左右两关系的所有元组。
）
SELECTTNAME,O,CLASS
FROMTEACHERSLEFTOUTERJOINTEACHINGUSING（TNO）
SQL的数据操纵
例44 把教师映雪的记录加入到教师表TEACHERS中。
（插入）
INSERTINTOTEACHERS
VALUES（1476，'映雪'，44，'副教授'）
例45 成绩优秀的学生将留下当教师。
INSERTINTOTEACHERS（TNO，TNAME）
SELECTDISTINCTSNO，SNAME
FROM STUDENTS，ENROLLS
WHERESTUDENTS.SNO＝ENROLLS.SNOANDGRADE＞＝90
例47 把所有学生的年龄增加一岁。
（修改）
UPDATESTUDENTS
SETAGE＝AGE+1
例48 学生春明在数据库课考试中作弊，该课成绩应作零分计。
UPDATEENROLLS
SETGRADE＝0
WHEREO＝'C1'AND
'春明'＝
（SELECTSNAME
FROMSTUDENTS
WHERESTUDENTS.SNO=ENROLLS.SNO）
例49 从教师表中删除年龄已到60岁的退休教师的数据。
（删除）
DELETEFROMTEACHERS
WHERE AGE＞＝60
SQL的数据控制
例50 授予LILI有对表STUDENTS的查询权。
（表／视图特权的授予
一个SQL特权允许一个被授权者在给定的数据库对象上进行特定的操作。
授权操作的数据库对象包括：
表/视图、列、域等。
授权的操作包括：
INS
升级会员 