结构化查询语言SQL.ppt

第四章 结构化查询语言SQL SQL的数据定义 SQL的数据查询功能 SQL的数据更新功能 用查询设计器建立查询 SQL SQL 是结构化查询语言是结构化查询语言 Structured Query LanguageStructured Query Language的缩写的缩写 SQLSQL 语言定义功能语言定义功能 SQL语言功能包括数据定义、数据查询、数据操纵数据定义、数据查询、数据操纵 和数据控制和数据控制四个方面，总共有个命令. SQLSQL功能功能命命 令令 数据定义语言数据定义语言 CREATECREATE、 、 ALTER ALTER、 、 DROP DROP 数据查询语言数据查询语言 SELECT SELECT 数据操纵语言数据操纵语言 INSERTINSERT、 、UPDATEUPDATE、 、DELETE DELETE 数据控制语言数据控制语言 GRANTGRANT、 、REVOKEREVOKE 数据定义语言数据定义语言- -用于定义数 据表的结构表的结构，如创建、修 改或删除数据表； 数据查询语言数据查询语言- -用于查询数查询数 据据； 数据操纵语言数据操纵语言- -用于修改数数 据据，如插入、修改、删除 数据； 数据控制语言数据控制语言- -用于控制用 户对数据表的访问权限等 SQLSQL的基本功能和命令的基本功能和命令 SQLSQL数据定数据定义语义语义语义语言言（Date Definition Language， 简称DDL）实现其数据定义功能，可对数据库用户、基本 表、视图、索引进行定义和撤消。数据表操作有关的命令 。 命命 令令 功功 能能 CREATECREATE TABLE TABLE 创建一个新的数据表（自由表 ） ALTERALTER TABLE TABLE 添加、修改和删除字段 DROP DROP TABLE TABLE 删除一个数据表（自由表）删除一个数据表（自由表） DDL的命令及功能 一、一、 SQL SQL数据定数据定义义义义 1 1、创建创建数据表数据表 CREATE TABLE命令主要用于创建一个新的数据 表。如果在创建数据表时已经打开了一个数据库，则所创建的 数据表将自动添加到该数据库中，否则将生成自由表。 【命令】【命令】 CREATE TABLE FREE FREE （1 ，小数位 NULL / NOT NULL CHECK ERROR DEFAULT PRIMARY KEY / / UNIQUE ，2 ) 【功能】【功能】 创建数据表结构。 字段的有效性规则 【说明】【说明】 FREE 指定创建自由表, 当数据库没有打开时, 不必指定 该项 命令中常见的字段数据类型和字段宽度及小数位数可参照过 去 NULL / NOT NULL 表示是否允许字段值为空值。 CHECK 用于指定字段的有效性规则。 ERROR 指定当输入的字段值违反有效性规则时，显示 提示信息。 DEFAULT 用于设置字段的默认值默认值。 PRIMARY KEY PRIMARY KEY 用于设置字段为主索引用于设置字段为主索引，一个表中只能 有一个主索引，UNIQUE 用于设置唯一唯一索引，不允许出 现NULL值 ,同一个字段不能既是主索引, 又是唯一唯一索引. 也就是说，PRIMARY KEY和UNIQUE和不能同时出现在一个字段的定义中。 CREATE TABLE CREATE TABLE 学生学生1 1 free free ; C(2), ; 出生年月出生年月 D , D , 入校总分入校总分 N (3) , N (3) , 三好生三好生 L , ; L , ; 特长特长 M, M, 照片照片 G ) G ) CREATE TABLE CREATE TABLE 学生学生2 ; C(2), ; 出生年月出生年月 D , D , 入校总分入校总分 N (3) , N (3) , 三好生三好生 L , ; L , ; 特长特长 M, M, 照片照片 G ) G ) 【例1】 创建创建学生基本情况数据表 设在创建数据表时已经打开了一个数据库，则所创建 的数据表将自动添加到该数据库中 【例2】 创建创建学生基本情况数据表。 CREATE TABLE CREATE TABLE 学生学生3 ;3 ; ( ( 学号学号 C(8) PRIMARY KEY NOT NULL,; C(8) PRIMARY KEY NOT NULL,; 姓名姓名 C(8) NOT NULL,; C(8) NOT NULL,; 性别性别 C(2) DEFAULT C(2) DEFAULT 男男 ; ; CHECK CHECK 性别性别=男男 .OR. .OR.性别性别=女女 ; ; ERROR ERROR 性别必须是男或女性别必须是男或女, ;, ; 出生年月出生年月 D CHECK D CHECK 出生年月出生年月=1970-01-01=1970-01-01,; ,; 入校总分入校总分 N (3) ,; N (3) ,; 三好生三好生 L, ; L, ; 特长特长 M, M, 照片照片 G ) G ) 注：由于有字段有效性 规则：必须是数据库表库表 DEFAULT 400 check 入校总分=400 ERROR 必须大于400, ; 2 2、修改修改数据表数据表 由于应用环境和应用需求的变化，经常需要修改基本表的结 构，不仅可以增加、删除和修改字段，还可以增加、删除和修改 如主索引、侯选索引、有效性规则等结构。SQL语言使用 ALTER TABLE命令来完成这一功能。该命令主要有两种格式。 【命令【命令1 1】 ALTER TABLE ADDADD / ALTER COLUMN ，小数位 NULL / NOT NULL CHECK ERROR DEFAULT PRIMARY KEY / UNIQUE 【功能】【功能】 修改基本表的结结构-用于添加或修改字段 【说明】【说明】 ADD用于增加新列 ALTER用于修改某列 用于添加字段， 修改某列，有效 规则等 【例4】修改修改“学生”数据表，在表中增加增加一个电话列列。 ALTERALTER TABLE 学生学生 ADDADD COLUMN 电话 C(8); unique 【例5】修改修改“学生”数据表，把“学生”表中姓名字段加字段加 宽宽到16位字符 ALTER ALTER TABLE 学生学生 alter alter 姓名 C (16) 【例6】修改修改“学生”数据表，在表中增加字段“入校总分” 的有效性规则有效性规则为为“ “入校总分入校总分=500”=500” ，当违反规则 时，提示信息 “入校总分必须大于500”。 ALTERALTER TABLE 学生 ALTER COLUMN 入校总分 N(3) ; CHECKCHECK 入校总分=500; ERROR “入校总分必须大于500“ defa 500; 修改已有的字段 【命令【命令2 2】 ALTER TABLEALTER TABLE DROPDROP COLUMN SET DEFAULT DROP DEFAULT SET CHECK ERROR DROPDROP CHECK ADD PRIMARY KEY DROPDROP PRIMARY KEY ADD UNIQUE DROPDROP UNIQUE TAG RENAMERENAME COLUMN TO 【说明】【说明】 DROP用于删除列、默认值、有效性规则、主键、候选索引 SET 用于为已有的字段 来设置默认值和有效性规则。 ADD 用于添加主索引和候选索引。 RENAME 用于修改表中的一个字段名。 用于删除字段、修改字段名，定制修改和删除 的有效规则等 【例7】修改修改“学生”数据表，删除表中“电话”字段。 ALTER TABLE ALTER TABLE 学生学生; ; DROPDROP COLUMN COLUMN 电话电话; ; DROP UNIQUE TAG dh 【例8】修改修改“学生”数据表，将表中“入校总分”字段 改名为“高考成绩”。 ALTER TABLE ALTER TABLE 学生学生; ; RENAME RENAME COLUMN COLUMN 入校总分入校总分 TO TO 高考成绩高考成绩 3 3、 删除数据表删除数据表 当某个数据表无用时，可将其删除。删除后，该 表中的数据和在此表上所建的索引都被删除。 【命令】【命令】 DROP TABLE 【功能】【功能】 删除数据表。 【例9】 删除表“学生”。 DROP TABLE 学生 二、二、 SQL SQL的数据的数据查询查询查询查询功能功能 P127 P127 实际上是属于数据操纵语言（Data Manipulation Language，简称DML）的范畴。数据查询是对数据库数据查询是对数据库 中的数据中的数据按指定内容和顺序按指定内容和顺序 进行检索输出。进行检索输出。它可以对数 据源进行各种组合，有效地筛选记录筛选记录、管理数据管理数据，并对 结果进行排序；它可以让用户以需要的方式显示数据表中的数据，可 以控制显示数据表中的哪些字段、哪些记录以及显示记录的顺序等。数数 据查询是数据库的据查询是数据库的核心操作核心操作。SQL语言的数据查询只有 一条SELECT语句，但是该语句却是用途最广泛的一条语 句，具有灵活的使用方法和丰富的功能。 SQLSQL的数据修改功能（的数据修改功能（4.2-4.2-数据修改）数据修改）P125P125 1. SQL 1. SQL简简简简介介 SQL 是结构化查询语言Structured Query Language 的缩写。 Select-SQL是从SQL语言移植过来的查询命令,具有强大 的单表与多表查询功能。其基本形式为 SELECT-FROM-WHERESELECT-FROM-WHERE SQL命令可以在命令窗口命令窗口、程序程序和事件代码事件代码中执行，也 允许通过一种称为“查询查询设计设计器器”的窗口的窗口(P91)(P91)来设计 查询步骤、生成查询文件生成查询文件,然后运行定制的查询。还可 将查询结果以图形的形式输出结果以图形的形式输出。 SELECT-SQL SELECT-SQL 查询查询 2 2、 、查询查询查询查询命令格式：命令格式： SELECTSELECT 命令的命令的格式格式 SELECT ALL | DISTINCT SELECT ALL | DISTINCT TOPTOP PERCENT PERCENT . AS , AS FROM FROM , INNER |LEFT/RIGHT| FULL JOIN ON INTO | | TO FILE | |TO PRINTER | |TO SCREEN WHEREWHERE AND AND|ORAND|OR GROUP BYGROUP BY , HAVING ORDER BY ORDER BY ASC|DESCASC|DESC , , ASC/DESCASC/DESC 【功能】【功能】 实现数据查询实现数据查询。 SELECTSELECT语句的执行过程为语句的执行过程为： 根据WHERE子句的连接 和检索条件，从FROM子句指定的基本表或视图中选取 满足条件的元组，再按照SELECT子句中指定的列表达 式，选出元组中的属性值形成结果表。如果有GROUP子 句，则将查询结果按照指定相同的值进行分组； 如果GROUP子句后有HAVING短语，则只输出满足 HAVING条件的元组；如果有ORDER子句，查询结果还 要按照指定的值进行排序。 【说明】【说明】 ALL/ DISTINCT：-此两项分别代表显示全部满足条件 的记录或消除重复的记录。 TOPPERCENT：-指定查询结果包括特定数 目的行数，或者包括全部行数的百分比。使用TOP子句 时必须同时使用ORDER BY 子句。 .AS ： -可以是 FROM子句中指定数据表（可用引用）中的字段名，也 可以是表达式。AS 表示可以给查询结果的列名重新命 名。 FROM ：-列出查询要用到的所有数 据表。指定包含该表的非当前数据库。 INNER/LEFT/RIGHT/FULL JOIN ON ：-INNER JOIN 指定查询结果中只包括 那些多个表中都有的行；LEFT JOIN指定查询结果中包括JOIN 关键字左边表中所有的行，和JOIN关键字右边表中与这些行匹 配的行；RIGHT JOIN指定查询结果中包括JOIN关键字右边表中 所有的行，和JOIN关键字左边表中与这些行匹配的行；FULL JOIN指定查询结果中包括所有表中的所有匹配与不匹配的行； ON 指定表的连接条件。 INTO /TO FILE /TO PRINTER/TO SCREEN：-指定查询结果存放的地方，可以是数据表或是文 本文件或输出到打印机或在屏幕显示。 WHERE AND AND/ORAND/OR： -在多表查询时，WHERE 用于指定数据表之间 联结的条件； -WHERE 指定在查询结果中的记录必须满足的 条件。 GROUP BY,HAVING ： -把查询结果中的行按一个或多个列的值分组；可以指定在 查询结果中的各组应满足的条件。 ORDER BY ASC/DESC, ASC/DESC : -用于指定一个或多个字段数据作为排序的基准，ASC为升序 ；DESC为降序，默认为升序。没有此项，查询结果不排序。 3. SQL3. SQL数据数据查询查询查询查询功能例表功能例表 SELECT-SQLSELECT-SQL命令主要组成部分命令主要组成部分 定义 数据源 指定数据源表 FROMFROM子句 确定源表间的联接JOINON子句 定义 结果 筛选源表记录 WHEREWHERE子句 指定输出字段字段、函数和表达式的列表或* * 指定输出类型-去向INTO子句 和 TO子句 定义记录的分组分组GROUP BY子句 指定结果顺序结果顺序ORDER BY子句 组中筛选结果记录HAVING 子句 指定有无重复记录ALL|DISTINCT 指定结果的范围TOP nExprPERCENT 用SELECI-SQL命令直接查询直接查询-分解分解 在命令窗口发一条SELECT-SQL命令,即可按命令的要求执行 一次查询。 格式： SELECT ALL | DISTINCT . AS ,. AS 该短语说明要查询的数据 无重复记录 SELECT选择的表达式 TOP PERCENT 符合条件的记录中选取的记录数 FROMFROM ! as INNERINNER|LEFTOUTER|RIGHTOUTER|FULLOUTER JOINJOIN ! ONON INTO | TO FILEADDITIVE |TO PRINTER PROMPT |TO SCREEN PREFERENCE NOCONSOLE PLAIN NOWAIT 说明要查询的数据来自哪 个表并可指定联接类型 指定查询结果的输出去向 临时表、dbf表等 用 于 联 接 其 左 右 两 个 表 表的暂用名 WHERE AND AND|OR AND|OR GROUP BY , HAVING UNION ALL ORDER BY ASC|DESC, ASC|DESC 说明查询条件 对查询记录按 值分组，用于分组统计 指定对查询的结果进行排序 ,关键字表达式为排序选项 记录查询的 分组限制条件 嵌入另一个查询命令 1 1、投影查询、投影查询 【 例例7 7 】 查询学生的基本情况。查询学生的基本情况。 SELECT 学号, 姓名, 入校总总分, 性别别,出生年月 FROM 学生 查询结查询结 果： 下面将SELECT语句中各子句的使用分为投影查询、条件 查询、分组查询、查询排序、连接查询、子查询介绍如下： 注：注： 在SELECT 语句中可以 重新指定列 的顺序。 4. 4. 查询实查询实查询实查询实例：例： 【例【例8 8（4-54-5）】）】查询学生基本情况，如图查询学生基本情况，如图 SELECT 学生.学号, 学生.姓名, 学生.性别, 学生.入校总分; FROM 学生选课成绩报表用数据库!学生; ORDER BY 学生.学号 DESC 查询结果：查询结果： 【例【例9 9】查询入校总分最低的】查询入校总分最低的五名五名学生基本情况，按入校学生基本情况，按入校 总分由低到高进行排序总分由低到高进行排序，同时指定表中的字段在，同时指定表中的字段在 查询结果中的列标题查询结果中的列标题。 SELECT SELECT TOP 5TOP 5 学号学号 AS AS 学生编号学生编号, ,姓名姓名 AS AS 学生姓名学生姓名; ; , , 性别性别 AS AS 学生性别学生性别, , 入校总分入校总分 AS AS 高考分数高考分数; ; FROM FROM 学生学生 ORDER BY ORDER BY 入校总分入校总分 查询结果：查询结果： 注：注：使用使用TOPTOP子句时，同时要使用子句时，同时要使用ORDER BY ORDER BY 子句。子句。 【例【例1010】 查询选修了课程的学生号（选课选课）。 （要求查询结果中的重复行被去掉） SELECTSELECT DISTINCT 学号 FROM FROM 选课选课选课选课 查询结果: 【例【例1111】 查询教师表的全部信息。 SELECTSELECT * * FROM 教师教师 注：注：用“*”表示数据表的全部列名，而不必逐一列出 。 思考：无选项DISTINCT的情况？ 2 2、条件查询、条件查询- -筛选查询筛选查询 当要在数据表中找出满足某些条件的行时，则需使当要在数据表中找出满足某些条件的行时，则需使 用用WHEREWHERE子句指定查询条件。子句指定查询条件。 WHEREWHERE子句中，条件通常通过三部分来描述：子句中，条件通常通过三部分来描述： 列名；列名； 比较运算符；比较运算符； 列名、常数。列名、常数。 常用的比较运算符如表所示。常用的比较运算符如表所示。 运运 算算 符符含含 义义 =, , =, 比较大小 AND，OR，NOT多重条件 BETWEEN AND确定范围 IN确定集合 LINK字符匹配 常用比较运算符 【例【例1313】 查询成绩高于成绩高于9090分分的学生的学号、课程号和成绩 查询结果: 1). 1). 比较大小比较大小 【例12】 查询选修课程号为“C140”的学生的学号和成绩 SELECT 学号学号, ,成绩成绩 FROM 选课选课 WHERE WHERE 课程号课程号=c140=c140 查询结果: SELECT 学号学号, ,课程号课程号, ,成绩成绩 FROM 选课 WHERE WHERE 成绩成绩=90=90 当WHERE子句需要指定一个以上的查询条件时， 则需要使用逻辑运算符AND，OR和NOT将其连接成复 合的逻辑表达式。其优先级由高到低为：NOT，AND， OR，用户可以使用括号改变优先级。 【例【例1414】 查询选修课程号为查询选修课程号为“C120”“C120”或或“C140”“C140”且且分数大于分数大于 等于等于8080分学生分学生的学号、课程号和成绩。的学号、课程号和成绩。 SELECT 学号,课程号,成绩 FROM 选课 WHERE WHERE ( ( 课程号课程号=c120 OR =c120 OR 课程号课程号=c140) AND =c140) AND 成绩成绩=80=80 查询结果查询结果: : 2). 2). 多重条件查询多重条件查询 3).3).确定范围确定范围 【例14】 查询工资在1500至2000之间的教师的教师号、 姓名及职称。 SELECT SELECT 教师号教师号, , 姓名姓名, , 职称职称 FROM FROM 教师教师; ; WHERE WHERE 工资工资 BETWEEN 1500 AND 2000BETWEEN 1500 AND 2000 等价于：等价于： SELECT SELECT 教师号教师号, , 姓名姓名, , 职称职称 FROM FROM 教师教师; ; WHERE WHERE 工资工资=1500 AND =1500 AND 工资工资=80=80 此语句也可以使用逻辑运算符“OR”“OR”实现： SELECT SELECT 学号，课程，成绩学号，课程，成绩 FROM FROM 选课；选课； WHEREWHERE（课程号（课程号=c140 =c140 OROR 课程号课程号= c160= c160）AND AND 成绩成绩=80=80 查询结果: != NOT IN 之外 3 3、 统计查询统计查询-补充 在很多应用中，并不是 只要求将表中的记录原样取 出就行了，而是要在原有数原有数 据的基础上通过计算，输出据的基础上通过计算，输出 统计结果统计结果。SQL提供了许多 库函数，增强了检索的功能 。 说明：说明：以上函数中，如果 指定了DISTINCT短语， 则表示在计算时取消指定列 中的重复值，如果不指定 DISTINCT短语或ALL短语 ，则取默认值ALL，表示不 取消重复值。 表 常用库函数及其功能 函数名称函数名称功功 能能 AVG 按列计算 平均值 SUM 按列计算 值的总和 COUNT 按列值 统计个数 MAX 求一列中的 最大值 MIN 求一列中的 最小值 【例21】 求选修课程号为“C140”学生的最高分、最低分 及之间相差的分数（显示课程号）。 SELECT SELECT 课程号课程号 , , MAX(MAX(成绩成绩) AS ) AS 最高分最高分 , , MIN(MIN(成绩成绩) AS ) AS 最低分最低分 , ,; ; MAX( MAX(成绩成绩) - MIN() - MIN(成绩成绩) AS ) AS 相差分数相差分数 FROM FROM 选课选课; ; WHERE WHERE 课程号课程号 = c140 = c140 查询结果 【例20】求学号为“s0201109”学生的总分和平均分(显示学号 ） SELECT SELECT 学号学号, , SUM(SUM(成绩成绩) AS ) AS 总分总分,AVG(,AVG(成绩成绩) AS ) AS 平均分平均分; ; FROM FROM 选课选课; ; WHERE WHERE 学号学号 =“S0201109“ =“S0201109“ 注意：注意：函数SUM和AVG只能对数值型字段进行计算。 查询结果 【例23】 统计选课表中有多少门课。 SELECT COUNT(DISTINCT SELECT COUNT(DISTINCT 课程号课程号) AS ) AS 选课表中课程数选课表中课程数 ; ; FROMFROM 选课 查询结果: 注意：注意：加入关键字DISTINCT后表示消去重复行，可计算字段 课程号不同值的数目。 【例22】 求入校总分在580分以上的学生的人数。 SELECT COUNT(SELECT COUNT(学号学号) AS ) AS 入校总分在入校总分在580580分以上的人数分以上的人数 ; ; FROM FROM 学生学生 WHERE WHERE 入校总分入校总分=580=580 查询结果: 【例24】 利用特殊函数COUNT(*)求教师表中“教授”和 “副教授”的人数。 SELECT COUNT(* *) AS 教授和副教授的人数； FROM 教师； WHERE 职称 IN ( 教授, 副教授 ) 查询结果: 注意：注意： COUNT（*）用来统计元组的个数，不消除重复行， 不允许使用DISTINCT关键字。 略法1 SELECT COUNT(*) AS 教授和副教授的人数; FROM 教师; WHERE 职称 =“教授“or 职称 =“副教授“ 法2 SELECT COUNT(职称) AS 教授和副教授的人数; FROM 教师; WHERE “教授“$职称 法3 COUNT(职称) 4 4、分组查询分组查询 GROUP BY GROUP BY 子句可以将查询结果按属性列或属性列 组合在行的方向上进行分组，每组在属性列或属性列组合 上具有相同的值。 【例25】 查询各位教师的教师号及其任课的门数。 SELECT SELECT 教师号教师号, COUNT(, COUNT(* *) AS ) AS 任课门数任课门数; ; FROM FROM 授课授课; ; GROUP BYGROUP BY 教师号教师号 说明：说明：1 1）GROUP BY子句按教师号的 值分组，所有具有相同教师号的元组为 一组，对每一组使用函数COUNT进行计 算，统计出各位教师任课的门数。 2 2）若在分组后还要按照一定的条 件进行筛选，则需使用HAVING子句 查询结果： 或：COUNT(教师号) 【例26】 查询选修两门以上课程的学生学号和选课门数。 SELECT SELECT 学号学号 AS AS 选课在两门以上学生的学号选课在两门以上学生的学号, , ; ; COUNT(COUNT(* *) AS ) AS 选课门数选课门数 FROM 选课 ; ; GROUP BY GROUP BY 学号学号 HAVING COUNT( HAVING COUNT(* *)=2)=2 说明：说明： 1). 1). GROUP BY子句按学号的值分组（所有具有相同学号的元组为一组，对每一组 使用函数COUNT进行计算)，统计出每位学生选课的门数。 2).2). HAVING子句去掉不满足选课门数在两门以上的组。 3).3). 当在一个SQL查询中同时使用WHERE子句、GROUP BY子句 和HAVING子句时, 其顺序是WHERE, GROUP BY, HAVING。 4).4). WHERE与HAVING子句的根本区别在于作用对象不同： WHERE子句作用于基本表或视图, 从中选择满足条件的元组； HAVING子句作用于组, 选择满足条件的组, 必须用于GROUP BY子句之后，但GROUP BY子句可以没有HAVING子句。 查询结果：查询结果： 略 COUNT(学号) 【例27】在课程“C120”、“C140”、“C150”和“C160” 中查询学生平均成绩在80分以上课程的学生的平均分（显 示课程号）。 SELECT 课程号,AVG(成绩) AS 平均分; FROM 选课; WHERE 课程号 IN(“c120“,“c140“,“c150“,“c160“); GROUP BY GROUP BY 课程号课程号; ; HAVING AVG(成绩)=80 注意注意: : 1)WHERE子句在选课表中筛选出课程号为“C120”、“C140”、“C150” 和 “C160”的记录， 2)GROUP BY子句按课程号的值分组，具有相同课程号的记 3) 录为一组, 对每一组的成绩使用函数AVG进行计算, 最后得 4) 到平均成绩在 80分以上的“C120”和“C160”两门课程学生 5) 平均分。 查询结果查询结果: : 7 7、查询的排序查询的排序 当需要对查询结果排序时， 可以使用ORDER ORDER BYBY子句子句对查询 的结果按照一个或多个属性列的 升序升序( (ASCASC) )或降序降序( (DESCDESC) )排列， 默认值为升序。ORDER BY子句必 须出现在其他子句之后。 【例【例2828】 查询选修了课程查询选修了课程“C140”“C140” 的学生的学生学号学号和和成绩成绩，并，并 按成绩按成绩降序降序排列。排列。 SELECT 学号,成绩 FROM 选课; WHERE 课程号=c140; ORDER BY ORDER BY 成绩成绩 DESC DESC 查询结果查询结果 【例【例2929】查询选修】查询选修“C120”“C120”、“C130”“C130”和和“C150”“C150”课程学生课程学生 的学号、课程号和成绩，查询结果按的学号、课程号和成绩，查询结果按课程号升序课程号升序 排列，课程号相同再按排列，课程号相同再按成绩降序排列成绩降序排列。 SELECT 学号, 课程号, 成绩; FROM 选课; WHERE 课程号=“c120“ or 课程号=“c130“ ; or 课程号=“c150“; ORDER BY 课程号, 成绩 DESC 【例【例3030】 求选课在求选课在三门三门以上且各门以上且各门课程均及格课程均及格的学生的的学生的 学号及其平均成绩，查询结果按学号降序列出。学号及其平均成绩，查询结果按学号降序列出。 SELECT SELECT 学号学号, AVG(, AVG(成绩成绩) ) ASAS 平均成绩；平均成绩； FROM FROM 选课；选课； WHERE WHERE 成绩成绩=60=60； GROUP BY GROUP BY 学号学号 HAVING COUNT(*)=3 HAVING COUNT(*)=3 ORDER BY ORDER BY 学号学号 DESC DESC 此语句为分组排序，执行过程如下： （FROM）取出整个选课数据表。 （WHERE）筛选出成绩=60的记录。 （GROUP BY）将选出的记录按学号分组。 （HAVING）筛选选课三门以上的分组。 （SELECT）从剩下的组中提取学号平均成绩。 （ORDER BY）将选取结果按学号排序。 5 5、 连接查询连接查询 存放在一个数据库中的各个表既是相互独立的, 又是 有一定联系的, 用户经常需要用多个表中的数据来组合， 综合得到所需的信息。前面的查询都是针对一个表进行的 ，当一个查询同时涉及两个以上的表时，称为当一个查询同时涉及两个以上的表时，称为连接查询连接查询。 连接查询实际上是通过各个表之间连接查询实际上是通过各个表之间共同属性列共同属性列的关联来查的关联来查 询数据的询数据的，数据表之间的联系是通过表的字段值来体现的 ，这个字段称为连接字段连接字段。连接操作的目的就是通过加在 连接字段上的条件将多个表连接起来，以便从多个表中查 询数据。连接查询是关系数据库中最主要的查询连接查询是关系数据库中最主要的查询，包括等 值与非等值连接查询、自身连接查询、外连接查询等。 查询查询查询查询命令格式：命令格式： SELECT ALL | DISTINCT SELECT ALL | DISTINCT TOPTOP PERCENT PERCENT . AS , AS FROM FROM , INNER |LEFT | RIGHT| FULL JOIN JOIN ONON INTO | | TO FILE | |TO PRINTER | |TO SCREEN WHEREWHERE AND AND|ORAND|OR GROUP BYGROUP BY , HAVING ORDER BY ORDER BY ASC|DESCASC|DESC , , ASC/DESCASC/DESC 表的连接方法有两种： 方法方法1 1：表之间满足一定条件的行行进行连接： FROM FROM 子句子句 中指明进行中指明进行 连接的表名连接的表名; ; WHEREWHERE子句子句 指明指明 连接的列名及其连接条件连接的列名及其连接条件。 。 方法方法2 2：利用关键字 JOIN 进行连接: 具体分为以下几种： INNER JOIN JOIN ：显示符合条件的记录，此为默认值； LEFT JOIN JOIN：显示符合条件的数据行以及左边表中不符合 条件的数据行，(此时右边数据行会以NULL来显示)，此称为左连接左连接 RIGHT JOIN JOIN：显示符合条件的数据行以及右边表中不符合 条件的数据行, (此时左边数据行会以NULL来显示), 此称为右连接右连接 FULL JOIN JOIN：显示符合条件的数据行以及左边表和右边表 中不符合条件的数据行, 此时缺乏数据的数据行会以NULL来显示 当将JOINJOIN 关键词放于FROMFROM子句子句中时，应有关键词ONON与 之相对应，以表明连接的条件。 【例【例3131】 查询陈静老师所讲授的课程号。查询陈静老师所讲授的课程号。 方法方法1 1： SELECT SELECT 教师教师. .教师号教师号, , 姓名姓名, , 课程号课程号; ; FROM FROM 教师教师, , 授课授课; ; WHERE WHERE 教师教师. .教教师师师师号号=授课授课. .教教师师师师号号 ANDAND 姓名姓名=“=“陈陈陈陈静静“ “ 这里: “姓名=陈 静”为 查询条件查询条件， “教师.教师号 =授课.教师号” 为 连接条件连接条件(“教师号” 为连接字段) 连接条件的一般格式为：连接条件的一般格式为： . 其中,比较运算符主要有：、！。 上面的操作是将教师表中的教师号和授课表中的教师号相等的行连接相等的行连接，同时选取姓名“陈静” 的行，然后再在“教师号”、“姓名”和“课程号”列上投影，这是连接、选取和投影的操作组合。 查询结果 表之间满足一定条件的行行进行连接： FROM FROM 子句子句 中指明进行中指明进行 连接的表名 连接的表名; ; WHEREWHERE子句子句 指明指明 连接的列名及其连接条件连接的列名及其连接条件 。 方法方法2 2： SELECT SELECT 教师教师. .教师号教师号, , 姓名姓名, , 课程号课程号; ; FROM 教师教师 INNER JOIN 授课授课; ON ON 教师教师. .教师号教师号= =授课 授课. .教师号教师号 AND 姓名=“陈静“ 本方法采用的是内部连接 -内部连接 【例32】 查询选修了课程“数据库”或“电子商务”学生的 学号、姓名、选课名称及成绩。 本例涉及三个表，WHERE子句中有两个连接条件和一个筛两个连接条件和一个筛 选条件选条件。当有两个以上的表进行连接时，称为多表连接。多表连接。 【例32】 查询选修了课程“数据库”或“电子商务”学生的 学号、姓名、选课名称及成绩。 查询结果查询结果: : 本例涉及三个表，WHERE子句中有两个连接条件和一个筛两个连接条件和一个筛 选条件选条件。当有两个以上的表进行连接时，称为多表连接。多表连接。 SELECT 学生.学号, 姓名, 课程名, 成绩; FROM 学生, 课程 , 选课; WHERE 学生学生. .学号学号= =选课选课选课选课. .学号学号 AND 选课选课选课选课. .课课课课程号程号= =课课课课程程. .课课课课程号程号; AND (课程名=“数据库“ OR 课程名=“电子商务“) 【例35】 查询教师姓名、职称及课程名。 酬情 可略 查询教师姓名、职称及课程名。 SELECT SELECT 姓名姓名, , 职称职称, , 课程名课程名; ; FROM FROM 教师教师, , 课程课程, , 授课授课; ; WHERE WHERE 教师教师. .教师号教师号=授课授课. .教师号教师号 AND ; AND ; 授课授课. .课程号课程号=课程课程. .课程号课程号 6 6、查询结果输出、查询结果输出（生成永久表或临时表） 使用INTOINTO子句子句，可以将查询结果输出到一个新建的数据表或 一个临时表。 INTO TABLE ：表示输出到数据表； INTO CURSOR： 表示输出到临时表临时表只存储在内存中，关机自动消失 【例】将每个同学选修的课程名课程名及成绩成绩输出到数据表 SELECT SELECT 学生学生. .姓名姓名, , 课程名课程名, , 成绩成绩; ; FROM FROM 学生学生, , 选课选课, , 课程课程; ; WHERE WHERE 学生学生. .学号学号= = 选课选课. .学号学号 AND ; AND ; 选课选课. .课程号课程号= = 课程课程. .课程号课程号; ; INTO TABLE INTO TABLE 学生成绩学生成绩 在当前的目录下可以看到新增加一个数据表 另外还有一些输出可选项： To FILE ：将查询结果输出到文本文件。 To PRINT：将查询结果输出打印机。 To SCREEN：将查询结果输出VF主窗口。 （表名为学生成绩） 查询结果输出查询结果输出（生成永久表或临时表） 【例】将每个同学选修的课程名课程名及成绩成绩输出到数据表 SELECT SELECT 学生学生. .姓名姓名, , 课程名课程名, , 成绩成绩; ; FROM FROM 学生学生, , 选课选课, , 课程课程; ; WHERE WHERE 学生学生. .学号学号= = 选课选课. .学号学号 AND ; AND ; 选课选课. .课程号课程号= = 课程课程. .课程号课程号; ; INTO TABLE INTO TABLE 学生成绩学生成绩 在当前的目录下可以看到新增加一个数据表 （表名为学生成绩） 三、三、 SQLSQL的数据修改功能的数据修改功能（ （4.2-4.2-数据修改）数据修改） SQL语言的数据操纵主要包括插入数据、更新数据和删 除数据三种语句。 1 1、数据记录的插入数据记录的插入 插入数据是把新记录插入到一个存在的表中。插入数据使 用语句INSERT INTO, 可插入一条记录，也可插入多条记录 1 1 ）. . 插入一行新记录插入一行新记录 【命令】【命令】 INSERT INTO ( , ) VALUES ( , ) 【功能】【功能】将新记录插入到指定的表的末尾中, 分别用表达式1、 表达式2等为字段名1、字段名2等赋值。 【说明】【说明】 是指要插入新记录的表； 是可选项，指定待添加数据的列； VALUES子句指定待添加数据的具体值待添加数据的具体值。 INSERT INTO 学生学生 (学号学号, , 姓名姓名, , 性别性别, ,出生年月出生年月, ,入校总分入校总分, , 三好生三好生) ; VALUES VALUES ( s0201111, 徐 畅, 女, 1984-06-25, 588, .T. ) 注注 意：意：各列名和数据必须用逗号分开，表达式的值必须是常量 。 【例】 在学生表中插入一条学生记录。 1 用菜单方式插入新记录？ 2 插入多行记录？ 2 2、数据记录的更新数据记录的更新 SQL语言可以使用UPDATE语句对表中的一行或多行 记录的某些列值进行修改。 【命令】【命令】UPDATE SETSET = ,= WHERE 【功能】【功能】 对表中的一行或多行记录的某些列值进行修改。 【说明】【说明】 是指要修改的表； SET子句给出要修改的列及其修改后的值； WHEREWHERE子句子句 指定待修改的记录应当满足的条件；指定待修改的记录应当满足的条件； 即更新条件即更新条件 WHERE子句省略时，则修改表中的所有记录。 1 1）. . 修改一行记录修改一行记录 【例【例4-34-3】 见书P126 【例】【例】把“陈静”教师的职称（讲师）改成副教授。 UPDATE 教师; SET 职称=“副教授“; WHERE 姓名= “陈静“ 2 2）. . 修改多行记录修改多行记录 【例】【例】 把教师表中工资小于或等于2000元的教师的工 资提高20%。 UPDATE 教师; SET SET 工资工资=1.2 =1.2 * *工资工资; ; WHERE 工资 WHERE 【功能】【功能】 删除表中的一行或多行记录。 【说明】【说明】 是指要删除数据的表。 WHERE子句 指定待删除的记录应当满足的条件 WHERE子句 省略时，则删除表中的所有记录。 1 1）. .删除一行记录删除一行记录 【例】 删除学生徐畅的记录。 DELETE FROM DELETE FROM 学生学生 WHERE WHERE 姓名姓名= = “ “ 徐畅 “ “ 2 2）. . 删除多行记录删除多行记录 【例】 删除所有学生的成绩。 DELETE FROM DELETE FROM 选课选课 执行此语句后，选课表即为一个空表。 转4. 3 数据查询 3 3）. . 利用子查询选择要删除的行利用子查询选择要删除的行 (略) 【例】 删除陈静教师授课的记录。 DELETE FROM DELETE FROM 授课授课 ； WHERE WHERE 教师号教师号= =； ( SELECT ( SELECT 教师号教师号 FROM FROM 教师教师 WHERE WHERE 姓名姓名=陈陈 静静) ) 四、四、 用用查询设计查询设计查询设计查询设计器建立器建立查询查询查询查询 P91 P91 使用使用SELECT-SQLSELECT-SQL语言进行数据查询外，语言进行数据查询外，VFPVFP还提供还提供 了更直观的数据查询方法，即使用了更直观的数据查