第四章SQL语言.docx

第四章SQL语言.docx

《第四章SQL语言.docx》由会员分享，可在线阅读，更多相关《第四章SQL语言.docx（18页珍藏版）》请在冰点文库上搜索。

第四章SQL语言

第四章数据库查询语言SQL

4.1SQL语言概述

●数据查询命令（SELECT）

●数据定义命令（CREATE等）

●数据操纵命令（INSERT等）

●数据管理命令（GRANT等）

可以独立完成数据库的全部操作,包括建立数据库、建表、查询、更新、维护数据、数据库的重新构造、数据库安全性等。

注意：

第三章讲的命令只适用于VF。

SQL语言适合在任何语言（VB、VC等）中使用,应用更广泛,效率更高,是考试的重点（笔试和上机）。

4.2查询功能

难点：

写出查询条件（逻辑表达式）。

查询命令的基本格式：

SELECT[ALL|DISTINCT]字段名FROM表名WHERE条件

4.2.1简单查询

例1：

从学生表中检索所有学生的姓名。

SELECT姓名FROM学生

例2：

从学生表中检索所有学生姓名（不能重复）

SELECTDISTINCT姓名FROM学生

例3：

从学生表中检索所有学生的姓名、年龄、出生日期。

SELECT姓名,年龄,出生日期FROM学生

例4：

从学生表中检索所有学生（包括所有字段）

SELECT﹡FROM学生

例5：

从学生表中检索所有年龄小于21的学生。

SELECT﹡FROM学生WHERE年龄<21

例6：

从学生表中检索出生在1982年和1983年的所有学生。

SELECT﹡FROM学生WHERE

YEAR（出生日期）=1982.OR.YEAR（出生日期）=1983

SETCENTURYON显示4位年份

SETMARKTO"--"设置日期分隔符为“--”

SETDATETOYMD按年月日显示日期

例7：

从学生表中检索性别为男而且姓王的学生

SELECT﹡FROM学生WHERE

性别=“男”.AND.姓名=“王”

例8：

从学生表中检索不姓王的学生

SELECT﹡FROM学生WHERE

.NOT.姓名=“王”

4.2.2简单的连接查询（多表查询）

例1：

无条件连接查询。

SELECT*FROM学生,学院

例2：

查询每个学生所属学院名称。

（等值连接）

SELECT*FROM学生,学院

WHERE所在学院=学院编号

例3：

查询学生王平属于哪个学院。

SELECT学号,姓名,学院名称

FROM学生,学院WHERE

姓名=“王平”.AND.所在学院=学院编号

例4：

查询学生赵明的所有课程的成绩。

SELECT学生.学号,姓名,课程编号,成绩

FROM学生,成绩WHERE

姓名=“赵明”.AND.学生.学号=成绩.学号

注意：

若两个表中有相同的字段名,使用该字段时前面需加表名来区分（表名.字段名）。

4.2.3嵌套查询

SELECT语句中又包含了SELECT语句

查询任务需要执行两次SELECT,第二次（外层）查询以第一次查询（内层）的结果为基础。

例1：

查询选修课程编号02的学生姓名。

SELECT姓名FROM学生WHERE学号IN（SELECT学号FROM成绩WHERE课程编号="02"）

SELECT姓名FROM学生,成绩WHERE学生.学号=成绩.学号and课程编号="02"

例2：

查询学号0002的学生选修的课程名称。

SELECT课程名称FROM课程WHERE课程编号IN（SELECT课程编号FROM成绩WHERE学号="0002"）

例3：

查询计算机学院的所有学生学号、姓名。

SELECT学号,姓名FROM学生WHERE所在学院IN（SELECT学院编号FROM学院WHERE学院名称="计算机"）

例4：

查询不是计算机学院的所有学生学号、姓名。

SELECT学号,姓名FROM学生WHERE所在学院NOTIN（SELECT学院编号FROM学院WHERE学院名称="计算机"）

4.2.4几个特殊的运算符

例1：

查询年龄在21—23之间的学生的学号、姓名、年龄。

SELECT学号,姓名,年龄FROM学生WHERE年龄BETWEEN21AND23

等价于：

年龄>=21AND年龄<=23

例2：

查询姓李的学生的学号、姓名、年龄。

SELECT学号,姓名,年龄FROM学生WHERE姓名LIKE“李%”

例3：

查询不姓李的学生的学号、姓名、年龄。

SELECT学号,姓名,年龄FROM学生WHERE姓名NOTLIKE“李%”

SQL通配符：

%：

表示零个或多个任意字符。

__：

表示一个任意字符。

注意：

和前面介绍的VF通配符﹡、？

不能互相替代。

例4：

查询1986年出生的学生的学号、出生日期。

SELECT学号,出生日期FROM学生WHEREYEAR（出生日期）=1986

例5：

查询不是1986年出生的学生的学号、出生日期。

SELECT学号,出生日期FROM学生WHEREYEAR（出生日期）!

=1986

或：

SELECT学号,出生日期FROM学生WHERENOTYEAR（出生日期）=1986

4.2.5对查询结果排序

ASC：

升序DESC：

降序

默认为升序

例1：

查询所有性别为女的学生的学号、年龄,并按年龄排序（升序）。

SELECT学号,年龄FROM学生where

性别="女"ORDERBY年龄

例2：

查询所有学生的学号、姓名,年龄,并先

按姓名排序（升序）,姓名相同时再按年龄排序（降序）。

SELECT学号,姓名,年龄FROM学生ORDERBY姓名ASC,年龄DESC

注意：

ASC可省略。

4.2.6简单的计算查询

SQL语言对查询结果有一定计算能力,比如求最大值、最小值、平均值等。

例1：

查询男生的人数。

COUNT函数：

统计查询到的记录个数

SELECTCOUNT（学号）FROM学生WHERE性别=“男”

SELECTCOUNT（学号）AS学生人数

FROM学生WHERE性别=“男”

SELECTCOUNT（DIST姓名）AS学生人数FROM学生WHERE性别=“男”

注意：

加DIST是去掉重复值。

注意：

AS可省略。

例2：

查询选修课程02的人数。

SELECTCOUNT（＊）AS学生人数

FROM成绩WHERE课程编号=“02”

例3：

查询学号为0004的学生的总成绩。

SELECTSUM（成绩）AS总成绩

FROM成绩WHERE学号=“0004”

例4：

查询学号为0004的学生的总成绩和平均成绩。

SELECTSUM（成绩）AS总成绩,

AVG（成绩）AS平均成绩

FROM成绩WHERE学号=“0004”

例5：

查询高等数学的平均成绩。

SELECTAVG（成绩）AS平均成绩

FROM成绩WHERE课程编号IN

（SELECT课程编号FROM课程

WHERE课程名称=“高等数学”）

例6：

查询高等数学最高成绩。

SELECTMAX（成绩）AS最高成绩

FROM成绩WHERE课程编号IN

（SELECT课程编号FROM课程

WHERE课程名称=“高等数学”）

4.2.7分组与计算查询（GROUPBY）

例1：

求每门课程的最高成绩。

SELECT课程编号,MAX（成绩）AS

最高成绩FROM成绩GROUPBY课程编号

例2：

求选修两门以上课程的同学。

SELECT学号,COUNT（＊）FROM成绩GROUPBY学号HAVINGCOUNT（＊）>=2

例3：

求各个年龄值（年龄>13）的学生人数。

SELECT年龄,COUNT（＊）AS人数FROM学生GROUPBY年龄HAVING年龄>13

SELECT年龄,COUNT（＊）AS人数FROM学生WHERE年龄>13GROUPBY年龄

例4：

求各个年龄值（年龄>13）的女生人数。

SELECT年龄,COUNT（＊）AS人数FROM学生WHERE性别=“女”

GROUPBY年龄HAVING年龄>13

注意：

HAVING短语必须与GROUPBY短语同时使用。

使用HAVING短语的同时能使用WHERE短语。

先执行WHERE后的条件从表中过滤不符合条件的记录,再执行GROUP对剩下的记录分组，接着执行HAVING后的条件过滤不符合条件的分组，最后对每个分组执行SELECT后的函数计算。

4.2.8利用空值查询（NULL）

例1：

学生表中所在学院为空值NULL的同学。

SELECT学号,姓名FROM学生WHERE所在学院ISNULL

例2：

学生表中所在学院不为空值NULL的同学。

SELECT学号,姓名FROM学生WHERE所在学院ISNOTNULL

4.2.9别名与自连接查询

例1：

查询学生赵明的所有课程的成绩。

SELECT学生.学号,姓名,课程编号,成绩

FROM学生,成绩WHERE

姓名==“赵明”.AND.学生.学号=成绩.学号

SELECTX.学号,姓名,课程编号,成绩

FROM学生X,成绩CWHERE

姓名==“赵明”.AND.X.学号=C.学号

例2：

查询学生赵明的班长。

SELECTA.姓名,“的班长是”,B.姓名

FROM学生A,学生BWHERE

A．姓名==“赵明”.AND.A.班长=B.学号

SELECT姓名FROM学生WHERE学号IN（SELECT班长FROM学生WHERE姓名="赵明"）

4.2.10内外层互相关联嵌套查询

内层查询需要外层查询的值,外层查询需要内层查询的结果。

例1：

查询每个学生的最高成绩。

SELECT＊FROM成绩aWHERE成绩=（selectmax（成绩）from成绩bwhere

a.学号=b.学号）

（2）查询已经选修了课程的学生信息。

SELECT＊FROM学生aWHERE

学号in（select学号from成绩bwherea.学号=b.学号）

4.2.11使用量词和谓词的查询

例1：

EXISTS用来检查子查询是否有结果

（1）查询已经选修了课程的学生信息。

SELECT＊FROM学生aWHEREEXISTS（select学号from成绩bwherea.学号=b.学号）

（2）查询没有选修了课程的学生信息。

SELECT＊FROM学生aWHERE

NOTEXISTS（select学号from成绩bwherea.学号=b.学号）

（3）查询选修课程01的学生信息。

SELECT＊FROM学生aWHERE

EXISTS（select＊from成绩bwherea.学号=b.学号AND课程编号=”01”）

例2：

ANY或SOME用来检查子查询是否有一个结果使条件为真。

（1）查询其他院中比001学院某一学生年龄小的学生年龄、姓名。

SELECT姓名,年龄FROM学生WHERE所在学院<>”001”AND年龄

（2）查询其他院中比001学院所有学生年龄都小的学生年龄、姓名。

SELECT姓名,年龄FROM学生WHERE所在学院<>”001”AND年龄

等价于：

SELECT姓名,年龄FROM学生WHERE所在学院<>”001”AND年龄<（selectMIN（年龄）from学生where所在学院=”001”）

4.2.12超连接查询

（1）普通连接（JOIN或INNERJOIN）（只有满足条件的记录出现在结果中）

查询每个学生的选修课程情况

SELECTa.学号,姓名,课程编号FROM学生aJOIN成绩bONa.学号=b.学号

等价于：

SELECTa.学号,姓名,课程编号FROM学生a,成绩bWHEREa.学号=b.学号

（2）左连接（除满足条件的记录出现在结果中,第一个表中不满足条件的记录也出现在结果中）

SELECTa.学号,姓名,课程编号FROM学生aLEFTJOIN成绩bONa.学号=b.学号

（3）右连接（除满足条件的记录出现在结果中,第二个表中不满足条件的记录也出现在结果中）

查询所有学生所在学院名称

SELECT学号,姓名,学院名称FROM学生RIGHTJOIN学院ON所在学院=学院编号

（4）全连接（除满足条件的记录出现在结果中,两个表中不满足条件的记录也出现在结果中）

查询所有学生所在学院名称

SELECT学号,姓名,学院名称FROM学生FULLJOIN学院ON所在学院=学院编号

4.2.13集合的并运算

例1：

查询性别为女或年龄不大于19的学生。

SELECT＊FROM学生WHERE性别=“女”UNION

SELECT＊FROM学生WHERE年龄<=19

例2：

查询选修01或者02课程的学生。

SELECT＊FROM成绩WHERE课程编号=“01”UNION

SELECT＊FROM成绩WHERE课程编号=“02”

例3：

查询选修01和02课程的学生。

SELECT学号FROM成绩WHERE课程编号=“01”AND学号IN（SELECT学号FROM成绩WHERE课程编号=“02”）

4.2.14VF中的特殊选项

（1）只显示查询结果的前几条记录

前3条记录

SELECT＊TOP3FROM成绩ORDERBY学号

前30%的记录

SELECT＊TOP30PERCENTFROM成绩ORDERBY学号

注意：

TOP必须与ORDERBY一起使用。

（2）将查询结果保存到数组中。

SELECT＊FROM成绩INTOARRAYXX

把XX看作一个二维数组,每行存放一条记录。

XX（1,1）=”0001”XX（1,2）=”01”XX（1,3）=80

XX（2,1）=”0001”XX（2,2）=”02”XX（2,3）=90

（3）将查询结果保存到临时文件中。

SELECT＊FROM成绩INTOCURSORXX

把查询结果保存到临时的只读文件XX.DBF中,关闭文件时自动删除。

（4）将查询结果保存到永久表文件中。

SELECT＊FROM成绩INTOTABLEXX

或

SELECT＊FROM成绩INTODBFXX

把查询结果保存到XX.DBF中。

不带查询条件时,相当于表的复制（等价于COPYTO）。

（5）将查询结果保存到文本文件（.TXT）中。

SELECT＊FROM成绩TOFILEXX

把查询结果保存到XX.TXT中。

（6）将查询结果直接送打印机打印。

SELECT＊FROM成绩TOPRINTER

4.3操作功能（对记录操作）

4.3.1插入数据

（1）插入完整记录

INSERTINTO表名VALUES（表达式1,表达式2,…）

例如：

向成绩表增加一条新记录

INSERTINTO成绩VALUES（“0005”,“02”,61）

（2）插入不完整记录

INSERTINTO表名[字段名1,字段名2,…]VALUES（表达式1,表达式2,…）

例如：

向成绩表增加一条新记录,但没有成绩。

INSERTINTO成绩（学号,课程编号）VALUES（“0005”,“02”）

注意：

主关键字不能为空。

必须满足表的参照完整性。

（3）从数组插入记录

DECLAREX（3）

X

（1）=”0006”

X

（2）=”04”

X（3）=78

INSERTINTO成绩FROMARRAYX

4.3.2更新数据

UPDATE表名SET字段1=表达式1,字段2=表达式2,…WHERE条件

注意：

省略条件则更新所有记录

注意:

和VF命令REPLACE的区别

REPLACE省略条件则只更新当前记录

例1：

将每个学生的年龄增加1

UPDATE学生SET年龄=年龄+1

从第一条记录开始更新。

例2：

将所有课程01的成绩加5分

UPDATE成绩SET成绩=成绩+5WHERE课程编号=“01”

4.3.3删除数据（逻辑删除）

DELETEFROM表名WHERE条件

注意：

省略条件则删除所有记录

注意:

和VF命令DELETE的区别

DELETEFOR条件

例1：

DELETEFROM成绩

例2：

DELETEFROM成绩WHERE学号=“0006”

4.4定义功能（对表、字段操作）

4.4.1表的定义

（1）基本格式（建立数据库表）

CREATETABLE|DBF表名（字段名1类型（宽度,小数位数）,字段名2类型（宽度,小数位数）…）

例如：

CREATETABLE职工（职工编号C（10）,姓名C（10）,性别C

（2）,出生日期D）

（2）为字段指定有效性规则、信息、默认值

例如：

CREATETABLE职工（职工编号C（10）,姓名C（10）,性别C

（2）CHECK（性别=“男”OR性别=“女”）ERROR“数据错误”DEFAULT“女”,出生日期D）

（3）指定某个字段为主关键字（主索引）

CREATETABLE职工（职工编号C（10）PRIMARYKEY,姓名C（10）,性别C

（2）CHECK（性别=“男”OR性别=“女”）ERROR“数据错误”DEFAULT“女”,出生日期D）

（4）建立表的永久联系（指定“多”方的外部关键字,同时为其建立普通索引）

格式：

FOREIGNKEY外部关键字段TAG索引名REFERENCES表名

例如：

CREATETABLE订单（订单编号C（10）PRIMARYKEY,职工编号C（10）,产品名称C（50）,数量i,FOREIGNKEY职工编号TAG职工编号REFERENCES职工）

（5）建立自由表

CREATETABLE职工FREE（职工编号C（10）PRIMARYKEY,姓名C（10）,性别C

（2）,出生日期D）

4.4.2删除表

DROPTABLE表名

注意：

不要和DELETE混淆

注意：

和VF命令REMOVETABLE的区别

4.4.3修改表的结构

（1）增加新的字段,并允许指定字段有效性规则等。

格式：

ALTERTABLE表名ADD[COLUMN]字段名类型[宽度,小数位数][NULL|NOTNULL][CHECK…][PRIMARYKEY|UNIQUE]

例如：

ALTERTABLE职工ADD工资N（8,2）CHECK工资>0ERROR“数据错误”DEFAULT0

（2）修改已有字段

格式1：

修改已有字段的类型、宽度,字段有效性规则。

ALTERTABLE表名ALTER[COLUMN]

字段名字段类型[宽度,小数位数][NULL|NOTNULL][CHECK…][PRIMARYKEY|UNIQUE]

例如：

ALTERTABLE职工ALTER工资N（6,1）CHECK工资>500

格式2：

修改、删除已有字段的有效性规则

ALTERTABLE表名ALTER[COLUMN]

字段名[宽度,小数位数][NULL|NOTNULL][SETCHECK…][DROPDEFAULT]

[DROP　CHECK]

例如：

ALTERTABLE职工ALTER工资SETCHECK工资>100

例如：

ALTERTABLE职工ALTER工资

DROPCHECK

格式3：

修改字段名称、删除已有字段

ALTERTABLE表名[RENAMECOLUMN字段名TO新的名称][DROPCOLUMN字段名]

例如：

将字段“工资”改名为“月工资”

ALTERTABLE职工RENAMECOLUMN工资TO月工资

例如：

删除字段“出生日期”

ALTERTABLE职工DROPCOLUMN出生日期

4.4.4视图的定义

1、概念：

逻辑表、虚拟表，可以像使用表一样使用视图。

用户一般只需看到视图，而不是全部的表。

2、建立视图

格式：

CREATEVIEW视图名ASSELECT语句

例如：

单表视图

CREATEVIEW男生AS

SELECT*FROM学生WHERE性别=”男”

例如：

多表视图

CREATEVIEW赵明成绩AS

SELECT姓名,课程编号,成绩

FROM学生a，成绩bWHERE

姓名=”赵明”ANDa.学号=b.学号

例如：

视图中的虚字段

CREATEVIEW总成绩AS

SELECT姓名,SUM（成绩）总成绩

FROM学生a,成绩bWHERE

a.学号=b.学号GROUPBYb.学号

3.删除视图

DROPVIEW视图名

例如：

DROPVIEW男生

注意：

使用视图的好处：

●简化了查询操作

●视图中并不真正存放数据，保证了数据安全

●每个用户只能看到自己需要的数据

●视图对插入、更新、删除操作有限制

注意：

索引名与索引表达式的区别：

按索引表达式建立索引文件。

按索引名区分一个索引文件中的不同索引。

索引表达式只在建立索引文件时起作用。

以后要使用某个索引必须通过命令

SETORDERto索引名设置当前索引。

●通过SQL命令建立候选索引：

ALTERTABLE职工ADDUNIQUE职工编号TAG职工编号

●通过非SQL命令建立普通索引：

INDEXON职工编号TAG职工编号

●通过非SQL命令建立候选索引：

INDEXON职工编号TAG职工编号CANDIDATE

●通过非SQL命令建立唯一索引：

INDEXON职工编号TAG职工编号UNIQUE

使用连接查询：

查询结果涉及多个表。

使用嵌套查询或连接查询：

查询结果涉及一个表。