第5章结构化查询语言SQL

1、5.1 SQL5.1 SQL语言概述语言概述5.2 SQL5.2 SQL的数据定义功能的数据定义功能5.3 SQL5.3 SQL的数据操纵功能的数据操纵功能5.4 5.4 数据查询数据查询5.5 5.5 小型案例实训小型案例实训第第5 5章章 结构化查询语言结构化查询语言SQLSQL掌握SQL的定义数据的基本命令及格式；掌握SQL对数据的操纵；掌握SQL的综合数据查询功能。5.1 SQL5.1 SQL语言概述语言概述1综合统一2高度非过程化3面向集合的操作方式4以同一种语法结构提供两种使用方式5语言简洁、易学易用SQLSQL语言的特点语言的特点: :5.2 SQL5.2 SQL的数据定义功能的

2、数据定义功能格式：格式：CREATE TABLE （，），）功能：功能：定义（也称创建）一个表。说明：说明：(1) (1) 是要创建的表文件名。是要创建的表文件名。(2) (2) TABLE 也可以用也可以用DBF代替代替。5.2.2 5.2.2 建立表结构建立表结构5【例】创建一个表STUD（学生信息表），它由以下字段组成：学号 （C，10）；姓名（C，8）；性别（C，2）；班级名（C，10）；系别代号（C，2）；地址（C，50）；出生日期 （D）；是否团员 （L）；备注 （M）。CREATE TABLE STUD(学号 C(10），姓名 C（10），性别 C（2），班级名 C（10）,系别

3、代号 C（2）,地址 C（50）,出生日期 D,是否团员 L,备注 M,照片 G）LIST STRUCTURE【例】创建一个表SC（课程成绩表），它由以下字段组成：学号（C，10）；课程号（C，2）。CREATE TABLE SC（学号 C（10），课程号 C（2）LIST STRUCTURE5.2.2 5.2.2 建立表结构建立表结构5.2 SQL5.2 SQL的数据定义功能的数据定义功能格式：ALTER TABLEADDALTERDROP 功能：修改表的结构。【例】在学生成绩表中增加一个总成绩字段，总成绩 N（5,1）ALTER TABLE 学生成绩 ADD 总成绩 N(5,1)LIST

4、STRUCTURE【例】将学生情况表的”姓名“字段的宽度改为8ALTER TABLE 学生情况 ALTER 姓名 C(8)LIST STRUCTURE5.2.3 5.2.3 修改数据表修改数据表7【例】删除学生情况表中的”简历“字段。Alter table 学生情况 drop 简历Modify structure 注：注： 关键字关键字alter alter 是修改字段；是修改字段；add add 关键字是增加关键字是增加 字段；字段； drop drop 关键字是删除字段。关键字是删除字段。 5.2 SQL5.2 SQL的数据定义功能的数据定义功能5.2.3 5.2.3 修改数据表修改数据表

5、8 数据操纵语言是完成数据操作的命令，一般分为两种类型的数据操纵，它们统称为DML： 数据检索（常称为查询）：寻找所需的具体数据。 数据修改：添加、删除和改变数据。数据操纵语言一般由INSERT（插入）、DELETE（删除）、UPDATE（更新），SELETE（检索，又称查询）等组成，由于SELETE比较特殊，所以一般又将它以查询（检索）语言单独出现。5.3 SQL5.3 SQL的数据操纵功能的数据操纵功能9【格式】INSERT INTO INSERT INTO ( () VALUES) VALUES（ ）【功能】在指定的表文件末尾追加一条记录。用表达式表中的各表达式值赋值给中的相应的各字段。

6、【说明】1、插入的字段名一定要与VALUES后面对应表达式的数据类型一致。2、如果插入的是表中依次完整依次完整的一条记录，则可以省略掉，否则必须指定。5.3 SQL5.3 SQL的数据操纵功能的数据操纵功能 5.3.1 5.3.1 插入记录插入记录103、：指定要追加的记录各个字段的值，可以是具体值也可以是表达式。【例】在学生情况.DBF文件的末尾追加一条记录。INSERT INTO 学生情况(学号，姓名，性别，出生日期，毕业否，专业，助学金) VALUES(“41110”，“李建国”，“男”，1984-10-14，.f.，计算机，40）因为追加的是一条完整的记录，所以字段名的指定可以省略IN

7、SERT INTO 学生情况 VALUES(“41110”，“李建国”，“男”，1984-10-14，.f.，计算机，40）5.3 SQL5.3 SQL的数据操纵功能的数据操纵功能 5.3.1 5.3.1 插入记录插入记录11【例】在学生情况.dbf末尾追加一条新记录 Insert into 学生情况(学号,姓名,性别,毕业否,专业,助学金) values(50008,王勇,男,.T.,数学,50)说明：因为追加的不是一条完整的记录，因此字段名的指定不可省略。5.3 SQL5.3 SQL的数据操纵功能的数据操纵功能 5.3.1 5.3.1 插入记录插入记录5.3 SQL5.3 SQL的数据操纵

8、功能的数据操纵功能格式：DELETE FROM WHERE功能：从指定表中，根据指定的条件逻辑删除记录。说明：如果要物理删除记录，在该命令后还必须用PACK命令。使用RECALL命令可以恢复逻辑删除的记录。【实例】 将学生情况.dbf中所有男生的记录逻辑删除。DELETE FROM 学生情况 WHERE 性别= 男 提示： DELETE只对表中的记录做逻辑删除，即添加删除标记，物理上并没有删除。 如果想彻底删除（物理删除）必须与PACK命令配合使用。5.3.2 5.3.2 删除记录删除记录5.3 SQL5.3 SQL的数据操纵功能的数据操纵功能格式：UPDATE SET =， =WHERE【实

9、例】将学生情况.dbf中，所有专业为工业会计的学生助学金增加50。 UPDATE UPDATE 学生情况学生情况 SET SET 助学金助学金= =助学金助学金+50 ;+50 ; WHERE WHERE 专业专业=“=“工业会计工业会计 提示： 一次可以更新一个字段，也可以更新多个字段。 作为更新条件的字段，不可以更新。5.3.3 5.3.3 更新记录更新记录145.4 5.4 数据查询数据查询SQLSQL数据查询语句各子句的功能数据查询语句各子句的功能SELECTSELECT子句子句：指定查询中要输出的字段。如果要输出全部 字段用 *。FROMFROM子句子句：指定查询的数据源，多个数据源

10、之间用逗号 隔开。WHEREWHERE子句子句：指定查询要满足的条件，条件中数据类型 一定要一致。GROUP BYGROUP BY子句子句：指定查询需要的分组字段。ORDER BYORDER BY子句子句 ：指定查询需要的排序字段，升序用ASC，系 统默认是升序，所以可以省去ASC，但是如 果是降序必须指定DESC。INTOINTO子句子句：指定查询的去向15【格式】SELECT SELECT * * | DISTINCT | DISTINCT | | FROM FROM 【功能功能】无条件查询。无条件查询。【说明】*： 表示显示全部查询记录，包括重复记录。DISTINCT：表示显示时去除重复

11、值，显示无重复结果的记录。5.4.1 5.4.1 简单查询简单查询16【例12-1】显示学生情况表中学号，姓名和专业字段的内容SELECT SELECT 学号学号, ,姓名姓名, ,专业专业 FROM FROM 学生情况学生情况【例12-2】显示学生情况.DBF中的所有记录。SELECT SELECT * * FROM FROM 学生情况学生情况命令中的*表示输出显示所有的字段，数据来源是学生情况表，表中的内容以浏览方式显示。【例12-3】查询学生情况表中专业数目。SELECT COUNTSELECT COUNT（DISTINCT DISTINCT 专业）专业）AS AS 专业个数专业个数 F

12、ROM FROM 学生学生情况情况5.4.1 5.4.1 简单查询简单查询17【例12-4】显示学生成绩表中的所有记录，并将成绩乘以0.7。SELECT SELECT 学号，姓名，外语学号，姓名，外语* *0.7 0.7 ，计算机，计算机* *0.7 FROM 0.7 FROM 学生成学生成绩绩SELECT SELECT 学号，课程号，外语学号，课程号，外语* *0.7 AS 0.7 AS 外语成绩，计算机外语成绩，计算机* *0.7 AS 0.7 AS 计算机成绩计算机成绩 FROM FROM 学生成绩学生成绩5.4.1 5.4.1 简单查询简单查询18【格式】SELECT SELECT *

13、 * | DISTINCT | DISTINCT FROM FROM WHERE WHERE 【功能】从一个表中查询满足条件的数据。【说明】的格式可以是教材表5-3中的运算符5.4.2 5.4.2 条件（条件（WHEREWHERE）查询）查询19【例12-5】显示学生情况表中所有男生记录的学号，姓名和性别字段值。SELECT SELECT 学号，姓名，性别学号，姓名，性别 ; ;FROM FROM 学生情况学生情况 WHERE WHERE 性别性别=男男 【例12-6】显示学生成绩表中计算机成绩在8090分之间的学生的学号，姓名，计算机成绩SELECT SELECT 学号，姓名，计算机学号，姓

14、名，计算机 ; ;FROM FROM 学生成绩学生成绩; ;WHERE WHERE 计算机计算机 BETWEEN 80 AND 90BETWEEN 80 AND 905.4.2 5.4.2 条件（条件（WHEREWHERE）查询）查询20注意：BETWEEN AND 表示在之间【例12-7】显示学生情况表中姓王的学生的学号，姓名，出生日期。SELECT SELECT 学号，姓名，出生日期学号，姓名，出生日期; ; FROM FROM 学生情况学生情况 ; ;WHERE WHERE 姓名姓名 LIKE “LIKE “王王%”%”注意：在注意：在SQLSQL语句中通配符语句中通配符% %代表任意个

15、任意字符。代表任意个任意字符。或者：或者：select select 学号学号, ,姓名姓名, ,出生日期出生日期; ;From From 学生情况学生情况 where left(where left(姓名姓名,2)=,2)=王王5.4.2 5.4.2 条件（条件（WHEREWHERE）查询）查询215.4.3 SQL的复杂查询的复杂查询1 1连接查询连接查询 【说明】在一个数据库中的多个表之间一般都存在着某些联系，在一个查询语句中同时涉及到两个或两个以上的表时，这种查询称之为连接查询（也称为多表查询）。在多表之间查询必须处理表与表之间的连接关系。 SELECT * | DISTINCT ；

16、FROM ，表2. ； WHERE 22【例12-8】查询并显示各个学生的姓名，专业，各科成绩。 SELECT SELECT 学生情况学生情况. .姓名姓名, , 专业专业, , 外语外语, ,计算机计算机; ; FROM FROM 学生情况学生情况, ,学生成绩学生成绩; ; WHERE WHERE 学生情况学生情况. .学号学号= =学生成绩学生成绩. .学号学号 或者或者SELECT SELECT 学生情况学生情况. .姓名姓名, , 专业专业, , 外语外语, ,计算机计算机; ;FROM FROM 学生情况学生情况 JOIN JOIN 学生成绩学生成绩 ON ON 学生情况学生情况.

17、 .学号学号= =学生成绩学生成绩. .学号学号 注：当数据源中多个表具有同一字段时，要指定该字段必须加以注：当数据源中多个表具有同一字段时，要指定该字段必须加以 限制！限制！1 1连接查询连接查询23【例12-9】查询并显示计算机专业学生外语成绩的情况。SELECT SELECT 学生情况学生情况. .学号学号, , 学生情况学生情况. .姓名姓名, , 外语外语; ;FROM FROM 学生情况学生情况, ,学生成绩学生成绩; ;WHERE WHERE 学生情况学生情况. .学号学号= =学生成绩学生成绩. .学号学号 and and 专业专业=计算机计算机注意：在FROM子句中可以为表指

18、定一个别名。格式：FROM 【例12-10】SELECT b.SELECT b.姓名，计算机，专业姓名，计算机，专业 ; ;FROM FROM 学生情况学生情况 a JOIN a JOIN 学生成绩学生成绩 b ON a.b ON a.学号学号=b.=b.学号学号 1 1连接查询连接查询24在SQL语句中，一个SELECTFROMWHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。一般情况是要查询输出的要查询输出的字段来自于一个表，而查询满足的条件则来自于另一个表，字段来自于一个表，而查询满足的条件则来自于另一个表，

19、这时就要用嵌套查询。有时也可以用连接查询做。【例12-11】显示计算机成绩大于80分的学生的学号，姓名和专业。 SELECT 学号,姓名,专业; FROM 学生情况; WHERE 学号 IN (SELECT 学号 FROM 学生成绩 WHERE 计算机80) 2 2嵌套查询嵌套查询255.4.45.4.4对查询结果排序对查询结果排序 如果没有指定查询结果的显示顺序，将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果。用户也可以用ORDER BY子句指定按照一个或多个属性列的升序（ASC）或降序(DESCDESC)重新排列查询结果，其中升序ASC为缺省值。【例12-12】查询所有学生信

20、息，并按出生日期排序。 SELECT SELECT * * FROM FROM 学生情况学生情况 ORDER BY ORDER BY 出生日期出生日期 26【例12-13】按专业降序，姓名升序显示学生情况表中的学号、姓名、专业。 SELECT SELECT 学号学号, ,姓名姓名, ,专业专业; ; FROM FROM 学生情况学生情况; ; ORDER BY ORDER BY 专业专业 DESCDESC，姓名，姓名5.4.45.4.4对查询结果排序对查询结果排序 275.4.55.4.5使用集函数使用集函数 主要集函数包括： COUNT ( DISTINCT |ALL COUNT ( DIS

21、TINCT |ALL * * | | 列名列名) ) 统计元组个数。 SUM ( DISTINCT | ALL SUM ( DISTINCT | ALL ) 计算一列值的总和（此列必须是数值型） AVGAVG（DISTINCT | ALL DISTINCT | ALL ） 计算一列值的平均值（此列必须是数值型） MAXMAX（DISTINCT | ALL DISTINCT | ALL ） 计算一列值的最大值 MINMIN（DISTINCT | ALL DISTINCT | ALL ） 计算一列值的最小值28 【例12-14】查询学生人数。 SELECT COUNTSELECT COUNT（学号

22、）（学号）AS AS 人数人数; ; FROM FROM 学生情况学生情况 【例12-15】查询专业为计算机的学生计算机平均成绩 SELECT SELECT 专业，专业，AVGAVG（计算机）（计算机）AS AS 平均成绩平均成绩; ; FROM FROM 学生情况，学生成绩学生情况，学生成绩; ; WHERE WHERE 学生情况学生情况. .学号学号= =学生成绩学生成绩. .学号学号 AND AND 专业专业=计算机计算机 5.4.55.4.5使用集函数使用集函数 29【格式格式】 SELECT SELECT 分组表达式，集函数分组表达式，集函数; ; FROM FROM 表名表名; ;

23、 WHERE WHERE ; GROUP BY GROUP BY HAVING HAVING GROUP BY ：表示分组查询，若查询到的数据里有多个记录的指定字段的值相同，只取一条记录作为查询结果。HAVING ：指定分组进一步满足的条件 ，只能跟在GROUP BY 后面使用，不可独立使用。 5.4.65.4.6分组与计算查询分组与计算查询30【例12-16】显示各专业总人数。 SELECT SELECT 专业专业,COUNT(,COUNT(* *) AS ) AS 人数人数; ; FROM FROM 学生情况学生情况; ; GROUP BY GROUP BY 专业专业【例12-17】统计男

24、女生人数 SELECT SELECT 性别性别,COUNT(,COUNT(学号学号) AS ) AS 人数人数; ; FROM FROM 学生情况学生情况; ; GROUP BY GROUP BY 性别性别5.4.65.4.6分组与计算查询分组与计算查询31【例12-18】统计每个专业学生计算机成绩的平均分SELECT SELECT 学生情况学生情况. .学号学号, ,学生情况学生情况. .姓名姓名,avg(,avg(计算计算机机) as ) as 平均成绩平均成绩; ; FROM FROM 学生情况学生情况, ,学生成绩学生成绩 ; ;WHERE WHERE 学生情况学生情况. .学号学号= =学生成绩学生成绩. .学号学号; ; GROUP BY GROUP BY 专业专业5.4.65.4.6分组与计算查询分组与计算查询32 默认情