第6章SQL语言讲课用

《第6章SQL语言讲课用》由会员分享，可在线阅读，更多相关《第6章SQL语言讲课用（12页珍藏版）》请在装配图网上搜索。

1、文档供参考，可复制、编制，期待您的好评与关注！ 第6章 SQL语言6.1 SQL概述 SQL是结构化查询语言，由四部分构成，分别为数据定义，数据查询，数据操纵和数据控制。6.2 SQL的数据定义功能6.2.1 创建数据表 命令：CREATETABLEFREE（,小数位数NULL/NOT NULLCHECK ERROR DEFAULTPRIMARY KEY/UNIQUE, 功能：创建数据表结构create table 学生;(学号 c(8) primary key not null,;姓名 c(8) not null,;性别 c(2) default 男 check 性别=男 or 性别=女 e

2、rror 性别必须是男或女,;出生年月 d check (出生年月=1960-01-01),;入校总分 n(3),;三好生 l,;特长 m,;照片 g)Null子句定义字段可以为空；not null 子句定义字段不能为空值；primary key子句定义表的主索引；default定义字段的默认值，定义的默认值的类型应和字段的类型相同；check定义字段的有效性规则，有效性规则必须是一个逻辑表达式；error定义当表中的记录违反有效性规则时系统提示的出错信息。定义的出错信息必须是字符串表达式，字符串定界符不能省略。 注意：create table 是一条命令，分号（；）是续行号，字段之间用逗号（

3、，）隔开。6.2.2 修改数据表 命令：ALTER TABLE ADD/ALTER/drop/rename COLUMN,小数位NULL/NOT NULLCHECKERRORDEFAULTPRIMARY KEY/UNIQUE 功能：修改基本表结构 例：在“学生”表中增加“电话”字段，并设为候选索引。ALTER TABLE 学生;ADD COLUMN 电话 C(8)6.2.3删除数据表 当某个数据表无用时，可将其删除，删除后，该表中的数据和在此表上所建的索引都被删除。 命令：DROPTABLE表名 功能：删除数据表。 例：删除表“学生”DROPTABLE学生6.2 SQL的数据查询功能 SQL的

4、查询功能实际上是数据操纵语言（Data Manipulation Language 简称DML）的范畴。 数据查询是对数据库中的数据按指定内容和顺序进行检索输出 数据查询是数据库的核心操作。 SQL语言的数据查询只有一条SELECT语句，但用途最广泛，具有灵活的使用方法和丰富的功能。6.2.1 SELCET命令的格式 命令：SELECTALL/DISTINCTTOPPERCENT.AS,.ASFROM,INNER/LEFT/RIGHT/FULL JOINONINTO/TO FILE/TO PRINTER/TO SCREENWHEREANDAND/ORAND/ORGROUP BY,.HAVING

5、ORDER BYASC/DESC,ASC/DESC; 功能：实现数据查询。SELECT语句的执行过程： 根据WHERE 子句的连接和检索条件，从FROM 子句指定的基本表或视图中选取满足条件的元组 再按SELECT 子句中指定的列表达式，选出元组中的属性值形成结果表 如有GROUP 子句，则将查询结果按照指定列名相同的值进行分组 如果GROUP 子句后有HAVING 短语，则只输出满足HAVING 条件的元组 如果有ORDER 子句，查询结果还要按照指定列名的值进行排序。 SELECT语句中各子句的使用分为：投影查询、条件查询、分组查询、查询排序、连接查询、子查询。6.3.2 投影查询 例66

6、：查询学生的情况。SELECT学号，姓名，入校总分，性别，；出生年月FROM学生注：可以重新指定列的顺序。 例67：查询学生的全部情况。SELECT* FROM学生 例68：查询入校总分最低的五名学生基本情况，按入校总分由低到高进行排序，同时指定表中的字段在查询结果中的列标题。SELECT TOP 5 学号 AS 学生编号,姓名 AS 学生姓名,;性别 AS 学生性别,;入校总分 AS 高考分数;FROM 学生 ORDER BY 入校总分注：top 必须和order by 一起使用 例69：查询选课学生的学号。select distinct 学号 from 选课 例610：查询学生的学号并改名

7、显示为学生编号。select 学号 as 学生编号 from 学生6.3.3 条件查询 当要在数据表中找出满足某些条件的行时，则需使用WHERE子句指定查询条件 WHERE子句中，条件通常由三部份来描述： 列名 比较运算符 列名、常数 比较大小 多重条件查询 确定范围 确定集合： 部份匹配查询6.3.4 统计查询在应用中，有的需要在原有数据上通过计算输出统计结果。SQL提供了许多库函数，增强了检索的功能 例620：求学号为s0201109学生的总分和平均分（显示学号）。select 学号,sum(成绩) as 总分,avg(成绩) as 平均分;from 选课;where (学号= s0201

8、109 )6.3.5 分组查询 GROPU BY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。 例625：查询各位教师的教师号及其任课门数。select 教师号,count(*) as 任课门数;from 授课;group by 教师号注意：GROUPBY子句按教师号的值分组，所有具有相同教师号的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数。6.3.6 查询的排序 对查询结果使用ORDER BY子句对一个或多个属性进行排序，默认为升序。 ORDERBY子句必须放在其他子句之后。 例628：查询选修了课程“C140

9、”的学生学号和成绩，并按成绩降序排列。select 学号,成绩;from 选课;where 课程号=c140;order by 成绩 desc6.3.7 连接查询 连接操作的目的：通过加在连接字段上的条件将多个表连接起来，以便从多个表中查询数据。 连接查询包括：等值与非等值连接查询、自身连接查询、外连接查询。 表的连接方法： 方法1 ：表之间满足一定条件的行进行连接，此时FROM 子句中指明进行连接的表名，WHERE 子句指明连接的列名及连接条件。 方法2 ：利用关键字JOIN 进行连接。 方法2具体分为：INNERJOIN（内部连接），LEFTJION（左连接），RIGHTJOIN（右连接）

10、，FULLJOIN（全连接）1、等值连接与非等值连接例631：查询陈静教师所讲授的课程号select 教师.教师号,姓名,课程号;from 教师，授课；where 教师.教师号=授课.教师号 and 姓名=陈静 一般的连接格式：.列名2比较运算符主要有：= , , =, y.工资 and y.姓名=陈静3、外连接 例635：查询教师姓名、职称及课程名。select 姓名,职称,课程名;from 教师,课程,授课;where 教师.教师号=授课.教师号 and ;授课.课程号=课程.课程号6.2.8 嵌套查询 嵌套查询可将一系列简单查询构成复杂查询，增强查询功能。子查询嵌套层次最多可为255层。

11、嵌套查询执行时由里向外处理。1、返回一个值的子查询 当子查询返回一个值时，可以使用比较运算符将父查询和了查询连接起来。 例636：查询与“陈静”老师职称相同的老师姓名和职称。select 姓名,职称;from 教师;where 职称=;(select 职称;from 教师;where 姓名=陈静)2、返回一组值的子查询 如果返回一组值，不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY/IN或ALL。 例638：查询讲授课程号为“C140”的教师姓名。select 姓名;from 教师;where 教师号=any;(select 教师号;from 授课;where 课程号=C140

12、)6.3.9 查询结果输出使用INTO子句，可以将查询结果输出到一个新建的数据表或一个临时表。INTOTABLE表名：输出到数据表INTOCURSOR：输出到临时表例643：将每个同学选修的课程名及成绩输出到数据表（表名为学生成绩）select 姓名,课程名,成绩;from 学生信息,选课,课程;where 学生信息.学号=选课.学号 and 选课.课程号=课程.课程号;into table 学生成绩还有一些输出选项：TOFILE文件名：将查询结果输出到文件TOPRINT：将查询结果输出到打印机TOSCREEN：将查询结果输出到VF主窗口6.4 SQL的数据操纵功能6.4.1 插入数据1. 插

13、入数据记录 命令：INSERT INTO (,)VALUES(,) 功能：将新记录插入到指定的表中，分别用表达式1、表达式2等为字段名1、字段名2等赋值。 说明：表名是指要插入新记录的表；字段名是可选项，指定添加数据的列；VALUES子句指定待添加数据的具体值。例644：在学生表中插入一条学生记录insert into 学生 (学号,姓名,性别,出生年月,入校总分,三好生);values(s0201111,徐畅,女,1984-06-25,588,.T.)2、插入多行记录 插入多行记录用于表间的拷贝，将一个表中的数据抽取数行插入另一表中，可以通过子查询来实现。 命令：INSERT INTO (,

14、) 功能：插入数据1、先建立新表学生平均成绩，用来存放学生姓名和学生选修课的平均成绩。CREATETABLE学生平均成绩（姓名CHAR（8），平均成绩INT（3）2、利用子查询求出各学生的平均成绩，把结果存放在新表学生平均成绩中。INSERT INTO 学生平均成绩；SELECT 姓名，AVG（成绩）；FROM学生，选课；WHERE学生.学号选课.学号；GROUPBY学号6.4.2 修改数据记录 SQL语言可以使用UPDATE语句对表中的一行或多行记录的某些列值进行修改。 命令：UPDATE表名 SET =,=WHERE 功能：对表中一行或多行记录的某些列值进行修改 说明：表名指要修改的表，S

15、ET子句给出要修改的列及改后的值，WHERE子句指定待修改的记录应当满足的条件，WHERE子句省略时，则修改表中的所有记录。1、修改一行记录2、修改多行记录3、用子查询修改记录6.4.3 删除数据记录 命令：DELETE FROMWHERE1、删除一行记录例649：删除刘伟教师的记录DELETEFROM学生WHERE姓名刘伟2、删除多行记录例651：删除所有学生的成绩DELETEFROM选课3、利用子查询选择要删除的行例651：删除陈静教师授课的记录DELETEFROM授课；WHERE教师号；（SELECT教师号；FROM教师；WHERE姓名陈静）第七章小结1. SQL语言的四大功能包括数据定

16、义功能,数据查询功能,数据更新功能,数据控制功能.以及各种语句的使用.2. 查询语句的多种形式的使用12 / 12历届等级考试题选讲11、（33次单选第11题）从学生选课表XK中（其中有学号、课程、成绩3个字段）查询选修了6门以上课程的学生学号，实现该功能的SELECT语句是（26）A. SELECT 学号 FROM XK GROUP BY 学号 HAVING B. SELECT 学号 FROM XK GROUP BY 学号 HAVING count（*）5C. SELECT 学号 FROM XK GROUP BY 学号 HAVING sum（*）5D. SELECT 学号 FROM XK H

17、AVING课程5 GROUP BY 学号2、（33次读程序第2题）有如下的“学生”表(1) 执行“SELECT班级号，COUNT(*) FROM 学生 GROUP BY 班级号 ORDER BY 2 DESC”后，查询结果中共有（）条记录A 7 B 2 C. 3 D. 4（2）第一条记录是（）A. 11 3 B. 22 1 C. 33 2 D. 44 1（3）执行“SELECT MIN(年龄) FROM 学生 INTO ARRAY TMP”后，下列说法正确的是（ ）A. TMP中共有2个元素 B. TMP(1)的值为18C. TMP(0)的值为18 D. TMP(1)的值为224、（33次）在

18、教师管理数据库中有3个表：其表名和结构分别如下：教师（教师号C（6）,姓名C（8），职称C（10），年龄N（3），工资N（4）授课（教师号C（6），课程号 C（4）课程（课程号 C（4），课程名C（10），课时 N（3）（1） 为教师表的“教师号”建立候选索引，索引名为JSH_ TABLE 教师 ADD UNIQUE 教师号 TAG JSH（2） 将所有教师的工资上调20%_教师 SET 工资=工资*1.2（3） 查询所授课程的课时=80的教师的教师号、姓名和课程名SELECT 教师.教师号,课程名 FROM 教师 INNER JOIN 授课 INNER JOIN 课程；ON_ ON教师.教师

19、号=授课.教师号；_课时=801. （32次单选第11题）在SQL的查询语句中，（26）短语用于实现关系的投影操作A. WHERE B.SELECT C. FROM D. GROUP BY2.（32次填空第4题）利用结构化查询语言SQL（1）在给定的数据库表RS中有数值字段“工资”，将工资在原来基础上增加20%._RS_工资=工资*1.2（2）在教工数据表JG中，查询职称为“助教”或“讲师”人员的姓名，工资和部门，并按工资降序排序。 SELECT 姓名，工资，部门， FORM JG WHERE职称_；_BY 工资_3. （31次单选第11题）将教师表中所有职称为“教授”的教师工资增加300元的

20、SQL语句是（）（A）replace all 工资 with 工资+300 where 职称=“教授”（B）update 教师 set工资=工资+300 where 职称=“教授”（C）update 教师 set工资=工资+300 where 职称=教授（D）update 教师 工资 with 工资+300 where 职称=“教授”4. （31次读程序第4题） 阅读下面程序数据库“等级考试.DBC”中有“学生”和“成绩”两个数据表：学生（考号 C(6)，姓名 C(2)，性别C(2)，党团员L）成绩（考号 C(6) ，笔试成绩N（3），设计成绩N（3），总分N（5，1）程序如下：SELECT

21、TOP 3 学生.考号，学生.姓名，学生.性别，成绩.总分；FROM 等级考试!学生，成绩;ORDER BY 总分;WHERE 学生.考号=成绩.考号（1） 程序功能是（A）查询学生信息，并按部分升序排序（B）查询学生信息，并按部分降序排序（C）查询总分前3名的学生信息，并按部分降序排序（D）查询总分前3名的学生信息，并按部分升序排序（2） 去掉程序中的语句ORDER BY 总分，程序运行结果（）（A）不变（B）改变（C）不可运行（D）查询结果不排序（3） SELECT 后的学生.考号，学生.姓名，学生.性别，成绩.总分所对应的关系操作是（）（A）投影（B）连接（C）选择（D）合并5. （31

22、次填空第6题）有数据表文件学生（学号 C (8)，姓名c(6)，性别L）下面的SQL语句是在学生表中插入一条新记录，请完善：INSERT 学生（学号，姓名，性别） （“2008002345”，“李林”，.F.）6. （31次填空第7题）向学生表中增加一个字段“入校成绩 N （5，1）” 的SQL语句如下，请完善： TABLE 学生 入校成绩 N（5，1）7.（30次单选第8题） 在SELECT-SQL中，要将查询结果保存在数据表中的选项是（）A.into table B.to fileC.to screenD.into cursor8.（30次读程序第4题）现有数据“部门表”和“商品表”文件，

23、执行SQL查询命令后请选择正确的结果（）第（33）（35）题使用如下表的数据： （1） SQL语句SELECT 部门号,MAX(单价*数量) FROM 商品表GROUP BY 部门号查询结果有几条记录（）A.1B.4C. 3D.10（2） SQL语句SELECT 产地，COUNT(*) 提供的商品种类数；FROM 商品表；WHERE 单价200；GROUP BY 产地 HAVING COUNT(*)2查询结果的第一条记录的产地和提供商品种类数是（ ）A.北京，1B.上海，2C. 广东，4D.广东，6（3） SQL语句SELECT 部门表.部门号,部门名称,sum(单价*数量)；FROM 部门表

24、，商品表；WHERE 部门表.部门号=商品表.部门号；GROUP BY 部门表.部门号查询结果是（）A.各部门商品数量合计B.各部门商品金额合计C. 所有商品金额合计D.各部门商品金额平均值9.有以下两表学生（学号，姓名，性别，入校成绩，出生年月）成绩（学号，课程号，成绩）注：一个学生可以选多门课程；（1） 按入校成绩降序查询，并将结果放入文本文件TEMP.TEXT中的SELECT-SQL语句如下，请完善：SELECT * FROM 学生_ BY 入校成绩 DESC_（2） 查询并输出每个学生的课程平均成绩的SELECT-SQL语句如下，请完善；SELECT 姓名，_AS 平均成绩；FROM

25、学生，成绩；WHERE 学生.学号=_BY 学生.学号10.（29次单选第5题）在SQL查询命令中，要去掉查询结果中某字段的重得值，应该在SELECT后面该字段名前面使用（）A.DISTINCTB.WHEREC.HAVINGD.WHILE11.（29次填空第2题）在“教材订购“数据库中有3个表：其中表名和结构分别如下：教材（书号 C(6)，书名C(20)，作者 C(8)，出版社C(20)，价格 N(3)）班级（班级编号 C(2)，班名 C(10)）教材订购（书号 C(6)，班级编号 C(2)，数量 N(3)）（1） 在“教材证购“数据库中查询每个班级的平均订书量： SELECT 班名，_1_A

26、S 平均订书量 FROM 班级，教材订购；WHERE 班级.班级编号=教材订购.班级编号；GROUP BY_2_（2） 在“教材证购“数据库中查询订购了5种以上教材的班级编号和订购的教材种类数量。SELECT 班级编号，COUNT(*) FROM 教材订购；GROUP BY _3_ HAVING_4_12. （28次单选第7题）为了在选课表中查询选修了“C140”和“C160”课程的学号，SELECTSQL语句的WHERE子句的格式为（）（A）WHERE 课程号BETWEEN “C140” AND “C160”（B）WHERE 课程号=“C140” AND “C160”（C）WHERE 课程号

27、 IN（ “C140” ,“C160”）（D）WHERE 课程号 LIKE （“C140”，“C160”）13（28次读程序第6题）阅读下面程序：SELECT SB.名称 AS 设备名,SB.启用日期 FROM SB WHERE YEAR(SB.启用日期)=1995(1) 程序完成的功能是（）（A）查询SB.DBF中1995年启用的设备与日期（B）查询SB.DBF中1995年以前启用的设备与日期（C）查询SB.DBF中1995年以后启用的设备与日期（D）以上命令有错，不能实现查询（2）命令中的AS设备名，表示（）（A）当前表文件的别名 （B）保存设备名（C）为查询结果命名 （D）为查询结果的列

28、名重新命名（3）如果将SQL命令改写为：SELECT SB.名称,SB.启用日期 FROM SB WHERE SB.启用日期=1995，程序完成的功能是（）（A）查询SB.DBF中1995年启用的设备名称与日期（B）查询SB.DBF中1995年以前启用的设备名称与日期（C）查询SB.DBF中1995年以后启用的设备名称与日期（D）以上命令有错，不能实现查询14（28次填空第3题）有一人才管理数据库中有3个表：其表名和结构分别如下：基本情况（编号C(6),姓名 C(8),性别 C(8),出生年月 D, 工资 N(5)）专业（编号C(6),专业名称C(10),职称C(10)）成果（编号C(6),成果类别 C(8),成果名称C(20)）（1） 列出年龄在40岁以下的教授的姓名：SELECT姓名 FROM 基本情况,_;WHERE基本情况.编号=专业.编号;AND_=40 AND职称=“教授”（2） 查询成果在3项以上的人员姓名和成果数，并按成果数降序排列：SELECT 姓名,COUNT(*) AS成果数 FROM 基本情况,成果 WHERE基本情况.编号=成果.编号;GROUP BY 成果.编号HAVING_;ORDER BY _.