第四章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
使用连接查询:
查询结果涉及多个表。
使用嵌套查询或连接查询:
查询结果涉及一个表。