SQL语句学习手册实例版.docx

SQL语句学习手册实例版.docx

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

SQL语句学习手册实例版

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.更改表格　

        ALTER TABLE table_name 

        ADD COLUMN column_name DATATYPE 

        说明：

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

）

        ALTER TABLE table_name

        ADD PRIMARY KEY （column_name）

        说明：

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

        ALTER TABLE table_name

        DROP PRIMARY KEY （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.外连接与内连接

有时候，即使在连接的表中没有相