SQL语句大全实例.docx
《SQL语句大全实例.docx》由会员分享,可在线阅读,更多相关《SQL语句大全实例.docx(14页珍藏版)》请在冰豆网上搜索。
![SQL语句大全实例.docx](https://file1.bdocx.com/fileroot1/2022-12/31/b05666e9-1a6d-4d68-aa09-42d2ef025efb/b05666e9-1a6d-4d68-aa09-42d2ef025efb1.gif)
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
CNO CHAR(4) NOTNULL
GRADE INT
PRIMARYKEY(SNO,CNO)
FOREIGNKEY(SNO)REFERENCESSTUDENTS(SNO)
FOREIGNKEY(CNO)REFERENCESCOURSES(CNO)
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,CNO);
视图操纵(虚表)
例9 成立一个只包含教师号、姓名和年龄的视图FACULTY.(在视图定义中不克不及包含ORDERBY子句)
CREATEVIEWFACULTY
ASSELECTTNO,TNAME,AGE
FROMTEACHERS
例10 从学生表、课程表和选课表中产生一个视图GRADE_TABLE,它包含学生姓名、课程名和成绩.
CREATEVIEWGRADE_TABLE
ASSELECT SNAME,CNAME,GRADE
FROM STUDENTS,COURSES,ENROLLS
WHERE STUDENTS.SNO=ENROLLS.SNOAND
COURSES.CNO=ENROLLS.CNO
例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个学分的课程号和课程名.
SELECTCNO,CNAME
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,CNO,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,CNAME,GRADE
FROM (SELECTSNAME,CNAME,GRADE
FROM STUDENTS,ENROLLS,COURSES
WHERE SEX='女')
ASTEMP(SNAME,CNAME,GRADE)
WHERE GRADE>80
表达式与函数的使用
例29 查询各课程的学时数.(算术表达式由算术运算符+、-、*、/与列名或数值常量所组成.)
SELECT CNAME,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子句经常使用于在计较出聚集之后对行的查询进行控制.)
SELECTCNO,AVG(GRADE),STUDENTS=COUNT(*)
FROMENROLLS
GROUPBYCNO
HAVINGCOUNT(*)>=3
相关子查询
例37 查询没有选任何课程的学生的学号和姓名.(当一个子查询涉及到一个来自外部查询的列时,称为相关子查询(CorrelatedSubquery).相关子查询要用到存在测试谓词EXISTS和NOTEXISTS,以及ALL、ANY(SOME)等.)
SELECTSNO,SNAME
FROM STUDENTS
WHERE NOTEXISTS
(SELECT*
FROMENROLLS
WHEREENROLLS.SNO=STUDENTS.SNO)
例38 查询哪些课程只有男生选读.
SELECTDISTINCTCNAME
FROM COURSESC
WHERE'男'=ALL
(SELECTSEX
FROM ENROLLS,STUDENTS
WHEREENROLLS.SNO=STUDENTS.SNOAND
ENROLLS.CNO=C.CNO)
例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.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*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,CNO,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
WHERECNO='C1'AND
'张春明'=
(SELECTSNAME
FROMSTUDENTS
WHERESTUDENTS.SNO=ENROLLS.SNO)
例49 从教师表中删除年龄已到60岁的退休教师的数据.(删除)
DELETEFROMTEACHERS
WHERE AGE>=60
SQL的数据控制
例50 授予LILI有对表STUDENTS的查询权.(表/视图特权的授予
一个SQL特权允许一个被授权者在给定的数据库对象上进行特定的操纵.授权操纵的数据库对象包含:
表