第四章SQL语言.docx
- 文档编号:24280629
- 上传时间:2023-05-26
- 格式:DOCX
- 页数:18
- 大小:54.12KB
第四章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 使用连接查询: 查询结果涉及多个表。 使用嵌套查询或连接查询: 查询结果涉及一个表。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第四 SQL 语言
![提示](https://static.bdocx.com/images/bang_tan.gif)