SQL语句实例大全.docx

SQL语句实例大全.docx

《SQL语句实例大全.docx》由会员分享，可在线阅读，更多相关《SQL语句实例大全.docx（18页珍藏版）》请在冰豆网上搜索。

SQL语句实例大全

表操作

例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）,

CHECK（（GRADEISNULL）OR（GRADEBETWEEN0AND100））

）

例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子句）

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。

CREATESCHEMATEACHING_DBAUTHRIZATIONZHANG

例15删除简易教学数据库模式TEACHING_DB。

（

（1）选用CASCADE，即当删除数据库模式时，则本数据库模式和其下属的基本表、视图、索引等全部被删除。

（2）选用RESTRICT，即本数据库模式下属的基本表、视图、索引等事先已清除，才能删除本数据库模式，否则拒绝删除。

）

DROPSCHEMATEACHING_DBCASCADE

单表操作

例16找出3个学分的课程号和课程名。

SELECTCNO,CNAME

FROM　COURSES

WHERE　CREDIT＝3

例17查询年龄大于22岁的学生情况。

SELECT*

FROMSTUDENTS

WHEREAGE＞22

例18找出籍贯为河北的男生的姓名和年龄。

SELECTSNAME,AGE

FROM　STUDENTS

WHERE　BPLACE＝'河北'ANDSEX＝'男'

例19找出年龄在20～23岁之间的学生的学号、姓名和年龄，并按年龄升序排序。

（ASC（升序）或DESC（降序）声明排序的方式，缺省为升序。

）

SELECTSNO,SNAME,AGE

FROMSTUDENTS

WHEREAGEBETWEEN20AND23

ORDERBYAGE

例20找出年龄小于23岁、籍贯是湖南或湖北的学生的姓名和性别。

（条件比较运算符＝、＜和逻辑运算符AND（与），此外还可以使用的运算符有：

＞（大于）、＞＝（大于等于）、＜＝（小于等于）、＜＞（不等于）、NOT（非）、OR（或）等。

谓词LIKE只能与字符串联用，常常是“＜列名＞LIKEpattern”的格式。

特殊字符“_”和“%”作为通配符。

谓词IN表示指定的属性应与后面的集合（括号中的值集或某个查询子句的结果）中的某个值相匹配，实际上是一系列的OR（或）的缩写。

谓词NOTIN表示指定的属性不与后面的集合中的某个值相匹配。

谓词BETWEEN是“包含于…之中”的意思。

）

SELECTSNAME,SEX

FROMSTUDENTS

WHEREAGE＜23ANDBPLACELIKE'湖％'

或

SELECTSNAME,SEX

FROMSTUDENTS

WHEREAGE＜23ANDBPLACEIN（'湖南'，'湖北'）

例22找出学生表中籍贯是空值的学生的姓名和性别。

（在SQL中不能使用条件：

＜列名＞＝NULL。

在SQL中只有一个特殊的查询条件允许查询NULL值：

）

SELECTSNAME,SEX

FROMSTUDENTS

WHEREBPLACEISNULL

多表操作

例23找出成绩为95分的学生的姓名。

（子查询）

SELECTSNAME

FROM　STUDENTS

WHERE　SNO＝

（SELECTSNO

FROMENROLLS

WHEREGRADE＝95）

例24找出成绩在90分以上的学生的姓名。

SELECTSNAME

FROMSTUDENTS

WHERESNOIN

（SELECTSNO

FROMENROLLS

WHEREGRADE＞90）

或

SELECTSNAME

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

WHEREBPLACEIN（‘山西’，‘河北’）ANDGRADE＞＝90AND　STUDENTS.SNO=ENROLLS.SNO

例28查出课程成绩在80分以上的女学生的姓名、课程名和成绩。

（FROM子句中的子查询）

SELECTSNAME,CNAME,GRADE

FROM（SELECTSNAME,CNAME,GRADE

FROMSTUDENTS,ENROLLS,COURSES

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

例34找出年龄超过平均年龄的学生姓名。

SELECTSNAME

FROMSTUDENTS

WHEREAGE＞

（SELECTAVG（AGE）

FROMSTUDENTS）

例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

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.SNO<>B.SNO）

例40找出选修了全部课程的学生的姓名。

本查询可以改为：

查询这样一些学生，没有一门课程是他不选修的。

SELECTSNAME

FROMSTUDENTS

WHERENOTEXISTS

（SELECT*

FROMCOURSES

WHERENOTEXISTS

（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）

EXCEPTALL

（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

FROMSTUDENTS，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

WHEREAGE＞＝60

SQL的数据控制

例50授予LILI有对表STUDENTS的查询权。

（表／视图特权的授予

一个SQL特权允许一个被授权者在给定的数据库对象上进行特定的操作。

授权操作的数据库对象包括：

表/视图、列、域等。

授权的操作包括：

INSERT、UPDATE、DELETE、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和smalldatetime等类型查询。

　　可使用以下通配字符：

　　百分号%：

可匹配任意类型和长度的字符，如果是中文，请使用两个百分号即%%。

　　下划线_：

匹配单个任意字符，它常用来限制表达式的字符长度。

　　方括号[]：

指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

[^]：

其取值也[]相同，但它要求所匹配对象为指定字符以外的任一个字符。

　　例如：

　　限制以Publishing结尾，使用LIKE'%Publishing'

　　限制以A开头：

LIKE'[A]%'

　　限制以A开头外：

LIKE'[^A]%'

2.更改表格　

ALTERTABLEtable_name

ADDCOLUMNcolumn_nameDATATYPE

说明：

增加一个栏位（没有删除某个栏位的语法。

）

ALTERTABLEtable_name

ADDPRIMARYKEY（column_name）

说明：

更改表得的定义把某个栏位设为主键。

ALTERTABLEtable_name

DROPPRIMARYKEY（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;

4.外连接与内连接

有时候，即使在连接的表中没有相应的行，用户可能想从一张表中看数据，Oracle提供了外连接实现该功能。

内连接是指连接查询只显示完全满足连接条件的记录，即等值连接，外连接的查询结果是内连接查询结果的扩展。

外连接不仅返回满足连接条件的所有记录而且也返回了一个表中那些在另一个表中没有匹配行的记录。

外连接的操作符是“+”。

“+”号放在连接条件中信息不完全的那一边（即没有相应行的那一边）。

运算符“+”影响NULL行的建立。

建一行或多行NULL来匹配连接的表中信息完全的行。

外连接运算符“+”只能出现在where子句中表达式的一边。

假如在多张表之间有多个连接条件，外连接运算符不能使用or,in逻辑运算符与其它条件组合。

假如emp表中dep