SQL语句大全实例.docx
《SQL语句大全实例.docx》由会员分享，可在线阅读，更多相关《SQL语句大全实例.docx（18页珍藏版）》请在冰点文库上搜索。
SQL语句大全实例
SQL语句实例
欧阳光明（2021.03.07）
表操作
例1对于表的教学管理数据库中的表STUDENTS,可以定义如
下：
CREATETABLESTUDENTS
（SNONUMERIC（6,0）NOTNULL
SNAMECHAR（8）NOTNULL
AGENUMERIC（3,0）
SEXCHAR
（2）
BPLACECHAR（20）
PRIMARYKEY（SNO））
例2对于表的教学管理数据库中的表ENROLLS,可以定义如下：
CREATETABLEENROLLS
（SNONUMERIC（6,0）NOTNULL
CNOCHAR（4）NOTNULL
GRADEINT
PRIMARYKEY（SNO,CNO）
FOREIGNKEY（SNO）REFERENCESSTUDENTS（SNO）
FOREIGNKEY（CNO）REFERENCESCOURSES（CNO）
*欧阳光明*创编2021.03.07
CHECK（（GRADEISNULL）OR（GRADEBETWEEN0AND
100）））
例3根据表的STUDENTS表，建立一个只包含学号.姓名.年龄的女学生表。
CREATETABLEGIRL
ASSELECTSNO,SNAME,AGE
FROMSTUDENTS
WHERESEX=女•;
例4删除教师表TEACHER。
DROPTABLETEACHER
例5在教师表中增加住址列。
ALTERTABLETEACHERS
ADD（ADDRCHAR（50））
例6把STUDENTS表中的BPLACE列删除，并且把引用
BPLACE列的所有视图和约束也一起删除。
ALTERTABLESTUDENTS
DROPBPLACECASCADE
例7补充定义ENROLLS表的主关键字。
ALTERTABLEENROLLS
ADDPRIMARYKEY（SNO,CNO）;
视图操作（虚表）
例9建立一个只包括教师号.姓名和年龄的视图FACULTY。
（在视图定义中不能包含ORDERBY子句）
*欧阳光明*创编2021.03.07
CREATEVIEWFACULTY
ASSELECTTNO,TNAME,AGE
FROMTEACHERS
例10从学生表.课程表和选课表中产生一个视图GRADE_TABLE,它包括学生姓名.课程名和成绩。
CREATEVIEWGRADE_TABLE
ASSELECTSNAME,CNAME,GRADE
FROMSTUDENTS,COURSES,ENROLLS
WHERESTUDENTS.SNO=ENROLLS.SNOAND
COURSES.CNO=ENROLLS.CNO
例11删除视图GRADE_TABLE
DROPVIEWGRADE_TABLERESTRICT索引操作
例12在学生表中按学号建立索引。
CREATEUNIQUEINDEXST
ONSTUDENTS（SNO,ASC）
例13删除按学号所建立的索引。
DROPINDEXST
数据库模式操作例14创建一个简易教学数据库的数据库模式TEACHING.DB,
属主为ZHANG。
CREATESCHEMA
TEACHING_DBAUTHRIZATIONZHANG
*欧阳光明*创编2021.03.07
例15删除简易教学数据库模式TEACHING_DB。
（
（1）选用CASCADE,即当删除数据库模式时，则本数据库模式和其下属的基本表.视图.索引等全部被删除。
（2）选用RESTRICT,即本数据库模式下属的基本表.视图.索引等事先己清除，才能删除本数据库模式，否则拒绝删除。
）
DROPSCHEMATEACHING_DBCASCADE
单表操作
例16找出3个学分的课程号和课程名。
SELECTCNO,CNAME
FROMCOURSES
WHERECREDIT=3
例17查询年龄大于22岁的学生情况。
SELECT*
FROMSTUDENTS
WHEREAGE>22
例18找出籍贯为河北的男生的姓名和年龄。
SELECTSNAME,AGE
FROMSTUDENTS
WHEREBPLACE='河北’ANDSEX='男’
例19找出年龄在20一23岁之间的学生的学号、姓名和年龄，并按年龄升序排序。
（ASC（升序）或DESC（降序）声明排序的方式，缺省为升序。
）
SELECTSNO,SNAME,AGE
*欧阳光明*创编
FROMSTUDENTS
WHEREAGEBETWEEN20AND23
ORDERBYAGE
例20找出年龄小于23岁.籍贯是湖南或湖北的学生的姓名和性别。
（条件比较运算符V和逻辑运算符AND（与），此外还可以使用的运算符有：
>（大于）.>=（大于等于）.<=（小于等于）.<>（不等于）.NOT（非）.OR（或）等。
谓词LIKE只能与字符串联用，常常是“V列名
>LIKEpattern-的格式。
特殊字符和“％”作为通配符。
谓词IN表示指定的属性应与后面的集合（括号中的值集或某个查询子句的结果）中的某个值相匹配，实际上是一系列的OR（或）
的缩写。
谓词NOTIN表示指定的属性不与后面的集合中的某个勺
相匹配。
谓词BETWEEN是“包含于…之中”的意思。
）
SELECTSNAME,SEX
FROMSTUDENTS
WHEREAGE<23ANDBPLACELIKE'湖％或
SELECTSNAME,SEX
FROMSTUDENTS
WHEREAGE<23ANDBPLACEIN（*湖南1,*湖北
1）
例22找出学生表中籍贯是空值的学生的姓名和性别。
（在SQL中不能使用条件：
V列名>=NULL。
在SQL中只有一个特殊的查询条件允许查询NULL值：
）
SELECTSNAME,SEX
FROMSTUDENTS
WHEREBPLACEISNULL
多表操作
例23找出成绩为95分的学生的姓名。
（子查询）
SELECTSNAME
FROMSTUDENTS
WHERESNO=
（SELECTSNO
FROMENROLLS
WHEREGRADE=95）
例24找出成绩在90分以上的学生的姓名。
SELECTSNAME
FROMSTUDENTS
WHERESNOIN
（SELECTSNO
FROMENROLLS
WHEREGRADE>90）
或
SELECTSNAME
*欧阳光明*创编2021.03.07
*欧阳光明*创编2021.03.07
FROMSTUDENTS
WHERESNO=ANY
（SELECTSNO
FROMENROLLS
WHEREGRADE>90）
例25查询全部学生的学生名和所学课程号及成绩。
（连接查询）
SELECTSNAME,CNO,GRADE
FROMSTUDENTS,ENROLLS
WHERESTUDENTS.SNO=ENROLLS.SNO
例26找出籍贯为山西或河北，成绩为90分以上的学生的姓名.籍贯和成绩。
（当构造多表连接查询命令时，必须遵循两条规则。
第一，连接条件数正好比表数少1（若有三个表，就有两个连接条件）；第二，若一个表中的主关键字是由多个列组成，则对此主关键字中的每一个列都要有一个连接条件（也有少数例外情况））
SELECTSNAME,BPLACE,GRADE
FROMSTUDENTS,ENROLLS
WHEREBPLACEINC山西5,4河北JANDGRADE>=
90ANDSTUDENTS.SNO=ENROLLS.SNO
例28查出课程成绩在80分以上的女学生的姓名.课程名和成
绩。
（FROM子句中的子查询）
SELECTSNAME,CNAME,GRADE
FROM（SELECTSNAME,CNAME,GRADE
FROMSTUDENTS,ENROLLS,COURSES
*欧阳光明*创编2021.03.07
*欧阳光明*创编
WHERESEX='女'）
ASTEMP（SNAME,CNAME,GRADE）
WHEREGRADE>80
表达式与函数的使用
例29查询各课程的学时数。
（算术表达式宙算术运算符+.
/与列名或数值常量所组成。
）
SELECTCNAME,COURSE_TIME=CREDIT*16
FROMCOURSES
例30找出教师的最小年龄。
（内部函数：
SQL标准中只使用COUNT.SUM、AVG.MAX.MIN函数，称之为聚集函数
（SetFunction）。
COUNT函数的结果是该列统计值的总数目，SUM函数求该列统计值之和，AVG函数求该列统计值之平均值,MAX函数求该列最大值，MIN函数求该列最小值。
）
SELECTMIN（AGE）
FROMTEACHERS
例31统计年龄小于等于22岁的学生人数。
（统计）
SELECTCOUNT（*）
FROMSTUDENTS
WHEREAGE<=22
例32找出学生的平均成绩和所学课程门数。
SELECTSNO,AVG（GRADE）,COURSES=COUNT（*）
FROMENROLLS
GROUPBYSNO
*欧阳光明*创编
*欧阳光明*创编2021.03.07
例34找出年龄超过平均年龄的学生姓名。
SELECTSNAME
FROMSTUDENTS
WHEREAGE>
（SELECTAVG（AGE）
FROMSTUDENTS）
例35找出各课程的平均成绩，按课程号分组，且只选择学生超过
3人的课程的成绩。
（GROUPBY与HAVING
GROUPBY子句把一个表按某一指定列（或一些列）上的值相等的原则分组，然后再对每组数据逬行规定的操作。
GROUPBY子句总是跟在WHERE子句后面，当WHERE子句缺省时，它跟在FROM子句后面。
having子句常用于在计算出聚集之后对行的查询逬行控制。
）
SELECTCNO,AVG（GRADE）,STUDENTS=COUNT（*）
FROMENROLLS
GROUPBYCNO
HAVINGCOUNTS）>=3
相关子查询
例37查询没有选任何课程的学生的学号和姓名。
（当一个子查询涉及到一个来自外部查询的列肘，称为相关子查询（CorrelatedSubquery）o相关子查询要用到存在测试谓词EXISTS和NOTEXISTS,以及ALL.ANY（SOME）等。
）
*欧阳光明*创编2021.03.07
*欧阳光明*创编
SELECTSNO,SNAME
FROMSTUDENTS
WHERENOTEXISTS
（SELECT*
FROMENROLLS
WHEREENROLLS.SNO=STUDENTS.SNO）
例38查询哪些课程只有男生选读。
SELECTDISTINCTCNAME
FROMCOURSESC
WHERE'男’=ALL
（SELECTSEX
FROMENROLLS,STUDENTS
WHEREENROLLS.SNO二STUDENTS.SNOAND
ENROLLS.CNO二C.CNO）
例39要求给出一张学生、籍贯列表，该表中的学生的籍贯省份,也是其他一些学生的籍贯省份。
SELECTSNAME,BPLACE
FROMSTUDENTSA
WHEREEXISTS
（SELECT睿
FROMSTUDENTSB
WHEREA.BPLACE二B.BPLACEAND
A.SNOoB.SNO）
水欧阳光明*创编
*欧阳光明*创编2021.03.07
例40找出选修了全部课程的学生的姓名。
本查询可以改为：
查询这样一些学生，没有一门课程杲他不选修的。
SELECTSNAME
FROMSTUDENTS
WHERENOTEXISTS
（SELECT睿
FROMCOURSES
WHERENOTEXISTS
（SELECT*
FROMENROLLS
WHEREENROLLS.SNO=STUDENTS.SNO
ANDENROLLS.CNO=COURSES.CNO））
关系代数运算
例41设有某克场工作人员的两张表：
营业员表SP.SUBORD和营销经理表SP_MGR,其关系数据模式如下：
SP_SUBORD（SALPERSJD,SALPERS_NAME,
MANAGERJD,OFFICE）
SP_MGR（SALPERS_ID,SALPERS_NAME,MANAGERJD,OFFICE）
其中，属性SALPERSJD为工作人员的编号，SALPERS.NAME为工作人员的姓名，MANAGERJD为所在部门经理的编号，OFFICE为工作地点。
*欧阳光明*创编2021.03.07
*欧阳光明*创编2021.03.07
若查询全部商场工作人员，可以用下面的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_SUBORDINTERSECTALLTABLE
SP_MGR）
（3）EXCEPT
*欧阳光明*创编2021.03.07
*欧阳光明*创编
（SELECT*FROMSP_MGR）
EXCEPT
（SELECT*FROMSP.SUBORD）
或等价地用下直的SQL语句：
SELECT*
FROM（TABLESP_MGREXCEPTTABLESP_SUBORD）或用带ALL的SQL语句：
（SELECT*FROMSP_MGR）
EXCEPTALL
（SELECT*FROMSP_SUBORD）
例42查询籍贯为四丿Ik课程成绩在80分以上的学生信息及其成绩。
（自然连接）
（SELECT*FROMSTUDENTS
WHEREBPLACEJ四川J
NATURALJOIN
（SELECT*FROMENROLLS
WHEREGRADE>=80）
例3.43列出全部教师的姓名及其任课的课程号.班级。
（外连接与外部并外连接允许在结果表中保留非匹配元组，空缺部分填以NULL。
外连接的作用是在做连接操作时避免丢失信息。
外连接有3类：
（1）左外连接（LeftOuterJoin）o连接运算谓词为LEFT[OUTER]JOIN,其结果表中保留左关系的所有元组。
*欧阳光明*创编2021.03.07
（2）右外连接（RightOuterJoin）o连接运算谓词为RIGHT[OUTER]JOIN,其结果表中保留右关系的所有元组。
（3）全外连接（FullOuterJoin）o连接运算谓词为FULL[OUTER]JOIN,其结果表中保留左右两关系的所有元组。
）
SELECTTNAME,CNO,CLASS
FROMTEACHERSLEFTOUTERJOINTEACHINGUSING
（TNO）
SQL的数据操纵
例44把教师李映雪的记录加入到教师表TEACHERS中。
（插入）
INSERTINTOTEACHERS
VALUES（1476,'李映雪’，44,'副教授'）
例45成绩优秀的学生将留下当教师。
INSERTINTOTEACHERS（TNO,TNAME）
SELECTDISTINCTSNO,SNAME
FROMSTUDENTS,ENROLLS
WHERESTUDENTS.SNO=ENROLLS.SNOANDGRADE>=90
例47把所有学生的年龄增加一岁。
（修改）
UPDATESTUDENTS
SETAGE=AGE+1
例48学生张春明在数据库课考试中作弊，该课成绩应作零分计。
UPDATEENROLLS
*欧阳光明*创编
SETGRADE=0
WHERECNO=*CrAND
•张春明1=
（SELECTSNAME
FROMSTUDENTS
WHERESTUDENTS.SNO二ENROLLS.SNO）
例49从教师表中删除年龄已到60岁的退休教师的数据。
（删除）
DELETEFROMTEACHERS
WHEREAGE>=60
SQL的数据控制
例50授予LILI有对表STUDENTS的查询权。
（表/视图特权的授予
—个SQL特权允许一个被授权者在给定的数据库对象上逬行
特定的操作。
授权操作的数据库对象包括：
表/视图、列、域等。
SELECT,REFERENCES.TRIGGER,UNDER,
USAGE,EXECUTE等。
其中INSERT,UPDATE,
DELETE,SELECT.REFERENCES.TRIGGER有对表做相应操作的权限，故称为表特权。
）
GRANTSELECTONSTUDENTS
TOLILI
WITHGRANTOPTION
水欧阳光明*创编
例51取消LILI的存取STUDENTS表的特权。
REVOKEALL
ONSTUDENTS
FROMLILICASCADE
不断补充中：
1.模糊查找:
它判断列值是否与指定的字符串格式相匹配。
可用于char、varchar、text、ntext、datetime^0smalldatetime等类型查询。
可使用以下通配字符：
百分号％:
可匹配
任意类型和长度的字符，如果是中文，请使用两个百分号即%%O下划线匹配单个任意字符，它常用来限制表达式的字符长度。
方括号[]:
指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
F]：
其取值也[]相同，但它要求所匹配对象为指定字符以外的任一个字符。
例如：
限制以Publishing结尾，使用LIKE^Publishing'限制以A开头：
LIKE[A]%，限制以A开头外：
LIKEFA]%2更改表
格ALTERTABLEtable_nameADDCOLUMNcolumn_n
ameDATATYPE说明：
增加一个栏位（没有删除某个栏位的语
法。
）ALTERTABLEtable_nameADDPRIMARYKEY（column_name）说明：
更改表得的定义把某个栏位设为主键。
ALTERTABLEtable.nameDROPPRIMARYKEY（column_name）说明：
把主键的定义删除。
3.groupby
在select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，号外，可以使用having子句限制返回的结果集。
groupby子句可以将查询结果分组，并返回行的汇总信息Oracle按照groupby子句中指定的表达式的值分组查询结果。
在带有groupby子句的查询语句中，在select列表中指定的列要么是groupby子句中指定的列，要么包含聚组函数
selectmax（sal）,jobempgroupbyjob;（注意max（sal）,job的job并非一定要出现，但有意义）
查询语句的select和groupby,having子句是聚组函数唯一出现的地方，在where子句中不能使用聚组函数。
selectdeptno,sum（sal）fromempwheresal>1200groupbydeptnohavingsum（sal）>8500orderbydeptno;
当在gropuby子句中使用having子句时，查询结果中只返回满足having条件的组。
在一个sql语句中可以有where子句和having子句。
having与where子句类似，均用于设置限定条件where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件显示特定的组，也可以使用多个分组标准进行分组。
查询每个部门的每种职位的雇员数selectdeptno,job,count（*）fromempgroupbydeptno,job;
*欧阳光明*创编2021.03.07
4•外连接与内连接
有时候，即使在连接的表中没有相应的行，用户可能想从一张表中看数据，Oracle提供了外连接实现该功能。
内连接是指连接查询只显示完全满足连接条件的记录，即等值连接，外连接的查询结果是内连接查询结果的扩展。
外连接不仅返回满足连接条件的所有记录而且也返回了一个表中那些在号一个表中没有匹配行的记录。
外连接的操作符是“+二“+”号放在连接条件中信息不完全的那一边（即没有相应行的那一边）。
运算符“+”影响NULL行的建立。
建一行或多行NULL来匹配连接的表中信息完全的行。
外连接运算符只能出现在where子句中表达式的一边。
假如在多张表之间有多个连接条件，外连接运算符不能使用or,in逻辑运算符与其它条件组合。
假如emp表中deptno=10的ename为空值，dept表中deptno=20的loc为空值：
1.
selectename,dept.deptno,locfromemp,deptwhereemp.depno（+）=dept.deptno;
如果在dept.deptno中有的数值在emp.deptno中没有值，则在做外连接时，结果中ename会产生一个空值。
（emp.deptno=10）
2.
selectename,dept.deptno,locfromemp,deptwhereemp.depno=dept.deptno（+）;
如果在emp
升级会员 