第五章关系数据库标准语言SQL(一

1、第五章 关系数据库标准语言SQL一、SQL简介SQL（Structured Query Language）是结构化查询语言的缩写。SQL功能命令动词数据查询SELECT数据定义(表的定义、删除和表结构的修改)CREATE 、DROP 、 ALTER数据操纵(数据的插入、更新和删除)INSERT、UPDATE 、 DELETE数据控制例1：SQL是哪几个英文单词的缩写？( ) A）Standard Query Language B）Structured Query LanguageC）Select Query Language D）以上都不是二、数据查询1、 简单查询 格式：SELECT FRO

2、MSELECT：给出要在查询结果中显示的字段名、表达式或常量FROM：给出了查询的数据来源于哪个表例1：从student中查询所有学生的姓名、出生日期、入学分数例2：查询所有学生的详细信息备注：* 表示显示表中所有字段练习1：从职工中检索所有的工资值练习2：检索仓库关系中的所有元组例3：查询显示所有学生的姓名，年龄，院系号，入学分数和增加了5后的入学分数，并将增加了5%的入学分数显示为“新分数”SELECT 姓名,INT(DATE( )-出生日期)/365) 年龄,院系号,入学分数, ;入学分数*1.05 AS 新分数 FROM student备注： AS 的用法2、 条件查询 格式：SELE

3、CT FROMWHEREWHERE：给出查询的条件（1）WHERE条件中常见的关系运算例：查询显示入学分数在500分以上的学生的姓名和入学分数SELECT 姓名,入学分数 FROM student WHERE 入学分数578例：查询2系和4系中所有82年以后出生的非党员的学生的姓名，出生日期，入学分数SELECT 姓名,出生日期,入学分数 FROM student WHERE ;(院系号=2 OR 院系号=4) AND 出生日期=1982/1/1 and 党员=.F.备注：WHERE 的用法练习：检索工资多于2220元的职工号（2）WHERE条件中特殊运算符的应用 （p118）1、NOT ，！

4、= ， 例：查询所有不是83年出生的学生2、LIKE ：字符匹配运算符。%表示0个或多个连续的字符；_（下划线）表示一个任意字符例：查询STUDENT表所有姓王的学生信息例：查询COURSE 表中所有不是基础课的课程例：查询STUDENT表所有学号第2位为“0”的学生的信息3、between and：在什么之间，并包括边界值例：查询SC表中成绩在6080分之间的学生的信息4、不确定值的查询：IS NULL | IS NOT NULL例：查询SC表中成绩不确定的学生的学号5、IN ：在一个值列表中进行查询例：查询STUDENT表中所有院系号为2或3或4的学生的信息3、 计算与分组查询（1）计算函

5、数 （P120） COUNT ( DISTINCT )统计记录个数例:统计选修了课程的学生人数。SELECT COUNT(DISTINCT 学号) FROM sc例:在student表中统计83年以后（含83年）出生的学生人数。SELECT COUNT(*) FROM STUDENT WHERE YEAR(出生日期)=1983 SUM ( DISTINCT )求和例:计算所有学生的入学分数的总和SELECT SUM(入学分数) AS 总入学分数 FROM student AVG ( DISTINCT )求平均值例:计算选修了c2课程的学生的成绩的平均分SELECT AVG (成绩) AS 平均

6、成绩 FROM sc WHERE 课程号=c2 MAX ()求最大值例：查询所有学生的最高入学分数SELECT MAX(入学分数) AS 最高分数 FROM student MIN ()求最小值例：查询02级学生所选修课程的最低分SELECT MIN(成绩) AS 最低分 FROM sc WHERE LEFT(学号,2)=02说明：所有查询函数的计算结果都只有唯一的值SUM和AVG函数中必须是数值型MAX和MIN函数的参数中不能有DISTINCT计算函数不能嵌套使用如果使用了计算函数，在SELECT子句中的其他字段没有意义。(2)分组查询：GROUP BY HAVING GROUP BY:查询

7、结果依据什么进行分组例：查询每个学生选修的课程门数select 学号,count(*) as 选课门数 FROM SC GROUP BY 学号练习：查询每个学生最高成绩 例：查询每门课的选课人数SELECT 课程号,COUNT(*) AS 选课人数 FROM SC GROUP BY 课程号 HAVING: 给出分组结果的筛选条件，即只有符合条件的分组项才会在查询结果中出现例：查询至少选修了3门课的学生的学号，选课门数select 学号,count(*) as 选课门数FROM SC GROUP BY 学号 HAVING count(*)=3练习：查询至少选修了2门课的学生的学号和总成绩sele

8、ct 学号, SUM(成绩) as 总分 FROM SC GROUP BY 学号 HAVING count(*)=2例：查询每个年级的学生的人数SELECT LEFT(学号,4) AS 年级,COUNT(*) AS学生人数 FROM STUDENT GROUP BY 年级练习：查询每个年级的学生入学分数的平均值 WHERE 与HAVING的关系例：查询统计至少有2人及格的课程的总成绩SELECT 课程号,SUM(成绩) AS 总成绩 FROM SC WHERE 成绩=60 GROUP BY 课程号 HAVING COUNT(*)=2说明：1) 一般在查询要求中含有“各”、“每”字时要用到分组子

9、句2) 在使用分组子句时，SELECT子句中一般只能有分组字段和统计函数3) 分组字段可以是数据源或查询结果中的字段名或者字段表达式，也可以是查询结果中的列号4) WHERE条件和HAVING条件都是对条件的限定，但它们所限定的范围不同，WHERE条件是对整个查询的条件限定，限定哪些记录参与查询。HAVIING条件是对分组条件的限定，在使用时必须有GROUP BY语句，是对分组结果进行筛选5) WHERE条件中不能有统计函数，而HAVING条件中通常都有统计函数。例：使用 SQL语句进行分组检索时，为了去掉不满足条件的分组，应当（ ）。A）使用WHERE子句 B）在GROUP BY后面使用 H

10、AVING子句C）先使用WHERE子句，再使用HAVING子句D）先使用HAVING子句，再使用WHERE子句例：下面有关HAVING子句描述错误的是（ ）。A）HAVING子句必须与GROUP BY子句同时使用，不能单独使用B）使用HAVING子句的同时不能使用WHERE子句C）使用HAVING子句的同时可以使用WHERE子句D）使用HAVING子句的作用是限定分组的条件4、 排序查询格式：SELECTFROMWHERE GROUP BYHAVINGORDER BY（1）范围参数：ALL、DISTINCTALL:显示查询结果中的全部记录DISTINCT：在查询结果中剔除重复行例：查询所有的学

11、生的姓名，院系号和党员信息SELECT ALL 姓名,院系号,党员 FROM student例：查询哪些学生选修了C1或C2课程SELECT DISTINCT 学号 FROM sc where 课程号=c1 OR 课程号=c2（2）排序短语：ORDER BY , ASCORDER BY:对最终的查询结果进行排序,ASC表示升序，是默认值，DESC表示降序。例：按入学分数的降序查询显示所有学生的信息SELECT * FROM student ORDER BY 入学分数 DESC例：查询所有学生的信息，要求按院系号升序排序，院系号相同按出生日期的降序排序SELECT * FROM student

12、ORDER BY 院系号, 出生日期 DESC例：查询所有学生的姓名，年龄，入学分数和党员情况，查询结果按年龄的降序排序SELECT 姓名,YEAR(DATE( )-YEAR(出生日期) 年龄,入学分数,党员 FROM student;ORDER BY 年龄 DESC 例：查询所有党员的入学分数增加11后的学生信息，并按增加后的新入学分数升序显示SELECT * , 入学分数*1.11 FROM student where 党员=.T. ORDER BY 10说明：是指查询中字段的标题名,只能是SELECT后的字段名；ORDER BY 是对最终的查询结果进行排序,只能在主查询中使用.（3）显示

13、部分结果：TOP N、 TOP N PERCENT TOP N：显示排序结果中的前N条记录TOP N PERCENT:显示排序结果中的前N%条记录例：查询显示入学分数最高的3个学生的姓名，入学分数以及出生日期SELECT TOP 3 姓名,入学分数,出生日期 FROM student; ORDER BY 入学分数 DESC例：查询显示成绩最低的40的学生的学号、课程号和成绩SELECT TOP 40 PERCENT 学号,课程号,成绩 FROM sc ORDER BY 成绩说明：只有使用ORDER BY排序之后，才能用TOP n和TOP n PERCENT显示部分记录5、多表的等值联接查询SE

14、LECT FROM ；WHERE 查询条件 AND 联接条件例：查询所有学生的学号，姓名，出生日期，院系号以及学生所在相关院系的全部信息SELECT 学号,姓名,出生日期,DEP.院系号,DEP.* FROM STUDENT,DEP;WHERE STUDENT.院系号=DEP.院系号例：查询所有男生的姓名和他们的所选修的课程的课程号，课程名以及成绩SELECT 姓名,SC.课程号,课程名,成绩 FROM STUDENT,SC,COURSE;WHERE STUDENT.学号=SC.学号 AND SC.课程号=COURSE.课程号 AND 性别=男例：查询平均成绩在70分以上并且总成绩最高的课程的

15、课程名与总成绩SELECT TOP 1 课程名, sum(成绩) FROM sc, course ;WHERE sc.课程号=course.课程号;GROUP BY 课程名 HAVING AVG(成绩)70 ORDER BY 2 desc说明（P124）：（1）在SELECT子句中，如果涉及到的两个表有的相同表达式，则需要注明结果中的该表达式所从属的表名。说明格式为“表名.字段名”。（2）FROM ,：指定为查询提供数据源的各个表。（3）WHERE ： 给出表与表之间的联接条件和查询条件。联接条件一般是不同表的相同字段名或者相同值域的字段名相等。（4）联接时的执行过程是：从中的第1条记录开始，

16、用指定字段名的值与中每条记录的指定字段名的值依次比较，如果相等就组成一条新记录放在查询结果中，直到中的所有记录比较完为止。6、嵌套查询（P128）查询时可能查询的条件不能直接给出，而须先执行另一个查询，得到结果后才能给出条件，这时就要用到嵌套查询。嵌套查询是指WHERE子句中包含另一个SELECT FROM WHERE语句的查询。嵌套查询的运行机理是先执行内层查询，然后把获得的结果作为外层查询的条件。（1）=、!=、=、= 连接内外层查询 功能：当子查询的返回结果唯一时，主查询WHERE子句中指定的字段可与内层查询的查询结果进行比较运算。例：查询与“赵丽”同一个院系的学生的信息例：查询成绩高于

17、所有课程的平均成绩的学生的学号、课程号和成绩例：查询选修了“数据结构”的学生的学号和成绩（2）IN 、NOT IN联接内外层查询功能：当子查询的返回结果有多个时，可以用IN 和 NOT IN 联接内外层查询。例：查询与“王芳”，“赵丽”不是同一个院系的学生信息。例：查询选修了c1课程的学生的姓名，院系号和党员情况（3）用量词ANY，SOME，ALL联接内外层查询功能：当子查询的返回结果有多条时，可以使用量词联接内外层查询。ANY,SOME：在进行比较运算时，只要主查询条件与任意一个子查询结果比较时值为真，就符合主查询的条件；ALL:在进行比较运算时，只有主查询条件与所有子查询结果比较时值为真，

18、就符合主查询的条件。例：查询哪几门课程的成绩比c3课程的最低成绩要高例：查询哪几门课程的成绩比c3课程的最高成绩要高7、查询结果的输出（P133） INTO ARRAY INTO CURSOR INTO TABLE|DBF TO FILE 例：将查询到的学生信息存放在数组tmp中并显示：SELECT * FROM student INTO ARRAY tmpLIST MEMO LIKE tmp例：将查询到的学生信息存放在临时文件tmp中并显示；SELECT * FROM student INTO CURSOR TMPLIST例：将查询到的学生信息存放到表student_bak中并显示；SELE

19、CT * FROM student INTO TABLE student_bak 例：将查询到的学生信息存放在文本文件student1.txt中并显示；SELECT * FROM student TO FILE student1.txt例：一条没有指明去向的SQL SELECT语句执行之后，会把查询结果显示在屏幕上，要退出这个查询窗口，应该按的键是（ ）。A）ALT B) DELETE C）ESC D）RETURN例：在SQL SELECT 语句中将查询结果存放在一个表中应该使用 子句（关键字必须拼写完整）。三、数据定义（P105）1、建立自由表 CREAT TABLE 表名 FREE（字段名

20、1 字段类型 ，字段名2 字段类型 NULL | NOT NULL）例 建立自由表“学生成绩” ,其表结构为：学号C(8)，姓名 C(8),成绩 N(6,2),升级 L ，年龄且成绩字段允许为NULL值，以学号字段建立候选索引.creat table 学生成绩 FREE (学号 C(8) UNIQUE ,姓名 C(8),成绩 N(6,2) NULL,年龄 I,升级 L)2、建立基本数据库表 CREAT DATABASE 学生管理CREAT TABLE 表名（字段名1 字段类型 ，字段名2 字段类型 NULL | NOT NULL CHECK 规则表达式 ERROR “错误提示信息” DEFAU

21、LT 默认值）例： 先建立一个学生管理的数据库，然后再建STU表，表结构为：学号 C(6), 成绩 N(5,1);并且“成绩”字段的数据范围在0700之间，错误提示为“成绩的范围应在0-700 之间”，默认值为500，以学号建立主索引。CREAT TABLE STU2 (学号 C(8) PRIMARY KEY ,成绩 N(6,2) CHECK 成绩0 AND 成绩700 ERROR 成绩必须在0到700之间 DEFAULT 500)3、修改表结构（1）增加或修改已有字段的属性ALTER TABLE 表名 ADD 字段名1 字段类型增加字段 ALTER字段名1 字段类型修改字段例:为学生成绩表增

22、加一个字段性别,数据类型为C，宽度为2ALTER TABLE 学生成绩1 ADD 性别 C(2)例:将STU表中的成绩字段的宽度改为7,小数位数改为2ALTER TABLE STU ALTER 成绩 N(7,2)（2）修改删除已有字段的有效性规则或默认值ALTER TABLE 表名 ALTER字段名 SET CHECK ERROR 增加或修改已有字段的有效性规则 SET DEFAULT增加或修改已有字段的默认值DROP CHECK删除已有字段的有效性规则 DROP DEFAULT删除已有字段的默认值例：将学生成绩表添加到学生管理数据库中 ，为性别字段增加有效性规则：性别$”男女”，错误提示信息

23、为“性别只能为男或女”默认值为“男”。ALTER TABLE 学生成绩 ALTER 性别 SET CHECK 性别$男女 ERROR 性别只能为男或女ALTER TABLE 学生成绩 ALTER 性别 SET DEFAULT 男例：删除上题所建立的有效性规则和默认值（3）删除字段 DROP 字段名删除字段例:将学生成绩表中的成绩字段删除 修改字段名 RENAME 字段名1 to 字段名2给字段重命名例:将STU表中的成绩改为入学分数四、数据操纵（p112）对表中的记录数据进行处理，就是数据操纵。包括记录的插入、更新和删除三个方面1、插入记录VFP支持两种SQL插入命令的格式。格式1：用给出的表

24、达式值向表中插入记录。 INSERT INTO (,)；VALUES(,)功能：在表尾添加一条记录，在VALUES中给出各指定字段的数据。如果省略了字段名，那么必须按照表结构中定义的字段顺序来指定各个字段值。例：向sc表中插入记录。学号为“”，课程号为“c1”，成绩为78。INSERT INTO sc VALUES(,c1,78)例：向student表中插入记录。学号为“”，姓名为“王伟岸”，性别为“女”，党员为.T.，入学成绩为592.2，出生日期不确定。INSERT INTO student(学号, 姓名, 性别, 党员,入学成绩, 出生日期) VALUES(,王伟岸,女,.T., 592

25、.2,.NULL.)例：向student表中插入记录。学号为“”，姓名为“王伟”，党员为.F.，入学成绩为562.2，备注为“校级优秀学生干部”，出生日期为1986/6/12,其它字段空白。INSERT INTO student2(学号, 姓名,党员,入学成绩,备注,出生日期); VALUES(,王伟,.F., 562.2, 校级优秀学生干部,1986/6/12)说明： 新记录添加在文件尾，插入新记录后，记录指针指向新记录。 备注型字段的值可以用字符的形式直接添加。 如果表中有通用字段，则不能用缺省字段名的方式插入各字段值。在指定字段名时，应注意字段名与表达式值的类型匹配、位置对应、数量相等。格式2：由数组向表中插入记录。INSERT INTO FROM ARRAY 数组名 功