德甲直播

 结构化查询语言SQL

[旧课复习]：

复习内容：

1
数据库的建立、编辑和维护。

2
数据库表索引的建立，使用及查询。

3
临时关联和永久关系的建立。

复习目的：进一步巩固学生对数据库和表基本操作方法。

复习时长：大约5分钟

[新课导入]：

导入方式：解读为什么要建立数据库，建立数据库的目的不仅仅是为了存储数据，更重要的是如何利用数据库技术来处理这些数据，以获得有用信息。而SQL语言是关系数据库的标准语言，是处理数据库的强有力手段。

导入目的：增强学生的学习积极性，初步了解SQL的作用。

导入时长：大约5分钟

[新课讲授]：

重点：利用 SQL对数据库进行增、删、改、查。

难点：SQL-select语句。

方法：运用多媒体辅助教学，采用案例教学和任务驱动等教学法。

3.1 SQL简介

1．SQL语言的特点

     概括起来，SQL语言的主要特点有如下几个特点：

（1
非过程化。

（2
以记录集合为操作对象。

（3
涵盖数据库操作的绝大部分功能。

（4
可与多种关系数据库程序协同工作。

（5）语言简捷，易学易用。

SQL语言功能极强，但由于设计巧妙，语言十分简捷，完成核心功能只用9个动词，如表5-1所示。另外，SQL语言非常接近英语口语，因此容易学习、容易使用。

                   表5-1 SQL命令动词

3.2  数据查询

SELECT基本结构

   SELECT  字段名

FROM  表名;

WHERE  条件;

GROUP BY  分组字段;

HAVING  分组筛选条件;

ORDER BY  排序字段;

INTO  输出目标

SELECT  score.学号,avg(成绩) as 平均分;

FROM  score inner join student on score.学号=student.学号;

WHERE  院系号='06';

GROUP BY  score.学号;

HAVING  平均分 > 60;

ORDER BY  平均分 desc

INTO  CURSOR AVG_06

SELECT命令基本用法

例： SELECT * FROM STUDENT

SELECT 学号,姓名 FROM STUDENT

SELECT AVG(金额) AS 每单平均金额, SUM(金额) AS 总金额 ；

FROM ORDER1

带条件的查询

SELECT * FROM STUDENT WHERE 性别=‘男’

IN运算符

IN运算符的操作对象是一个集合，作用是判断是否是集合中的元素

SELECT * FROM  xscj  WHERE 课程名称 IN （‘计算机基础’,’高等数学’）

BETWEEN运算符

?查询在2000-7-1和2001-4-1之间的订单

SELECT * FROM order1 ;

    WHERE 订单日期 BETWEEN {^2000-7-1} AND {^2001-4-1}

2 查询成绩在60到80之间的记录

SELECT * FROM xscj WHERE 分数 BETWEEN 60 AND 80

LIKE运算符

查询姓刘的同学的记录

SELECT * FROM xscj WHERE 姓名 LIKE ‘刘%’

SQL中使用 _ 和 % 作为通配符，分别与 ? 和 * 的作用相类似

IS NULL运算符

选择未填写分数的记录

SELECT  * FROM xscj WHERE 分数 IS NULL

选择已填写分数的记录

SELECT  * FROM xscj WHERE 分数 IS NOT NULL

注：”分数 IS NULL” 与 “分数 = NULL” 不等价

嵌套查询

一个SELECT无法完成查询任务，需要一个子SELECT的结果作为条件语句的条件

例：选择红太阳公司的订单

SELECT * FROM ORDER1;

    WHERE 客户编号 = ;

( SELECT 客户编号 FROM CUST ;

WHERE 公司名称 = ’红太阳’ )

IN谓词

选择北京客户的订单

SELECT * FROM ORDER1;

    WHERE 客户编号 IN ;

( SELECT 客户编号 FROM CUST ;

WHERE 所在地 = ’北京’ )

ANY（SOME）谓词

ANY(SOME)表示集合中任意(任选)一个元素，只要有一个满足条件就返回.T.

例：查询满足以下条件的女职员记录

–年龄只要比’06’号部门中任意一个人小

SELECT * ;

 FROM 雇员 ;

 WHERE 性别 = '女' AND 年龄 < ANY ;

 (SELECT 年龄 FROM 雇员 WHERE 部门号 = '06')

ALL谓词

ALL表示集合中所有一个元素，所有元素满足条件才返回.T.，只要有一个不满足就返回.F.

查询年龄比’06’号部门中所有人小的女职员记录

SELECT * ;

 FROM 雇员 ;

 WHERE 性别 = '女' AND 年龄 < ALL ;

 (SELECT 年龄 FROM 雇员 WHERE 部门号 = '06')

ORDER BY子句

ORDER BY子句用于对查询结果排序，排序选项可以使用字段名或数字，如使用数字2表示第2列

例1：显示ORDER1表所有记录，并按送货方式和金额降序排序

SELECT * FROM ORDER1 ORDER BY 送货方式, 金额 DESC

例2：显示ORDER1表的客户编号、订单日期、金额字段，并按金额降序排序

SELECT 客户编号,订单日期,金额 FROM ORDER1 ORDER BY 3 DESC

例3：查询各客户的送货方式，按送货方式排序

SELECT DISTINCT 客户编号, 送货方式 FROM ORDER1 ORDER BY 2

TOP 子句

TOP 子句在符合条件的所有记录中选取指定数量或百分比的记录，必须和ORDER BY一起使用。

例1：显示选修高等数学课程的前三名姓名，分数

SELECT TOP 3 姓名, 分数 FROM xscj ;

WHERE 课程名称 = '高等数学‘ ;

ORDER BY 分数 DESC

注：如需选择前30％的记录可使用 TOP 30 PERCENT

分组统计－GROUP BY子句

GROUP BY子句对查询结果进行分组汇总。

例：统计各门课程男生成绩的平均分

SELECT 课程名称,AVG(分数) ;

FROM xscj WHERE 性别 = ‘男’ ;

GROUP BY 课程名称

例：统计男女生各门成绩的平均分

SELECT 课程名称,性别,AVG(分数) ;

FROM xscj GROUP BY 课程名称,性别

筛。HAVING子句

HAVING子句是对分组统计结果的筛。蚨匦胗GROUP BY一起使用，不能单独使用。

例：查询该课程的男生平均分超过70分的课程名称和平均分

SELECT 课程名称,AVG(分数) ;

FROM xscj WHERE 性别 = ‘男’ ;

GROUP BY 课程名称 HAVING AVG(分数)>70

GROUP BY和HAVING使用规则

带有统计要求的（如平均值、总和、计数等）命题，往往要使用GROUP BY子句，后接分类字段。

命题中如果有筛选条件：

–与分组无关的条件放在WHERE子句后

–与统计值相关的条件接在HAVING子句后

注：WHERE子句后不可使用统计函数

查询各同学的不及格门数

SELECT 学号,count(成绩) as 不及格门数;

FROM score WHERE 成绩<60 GROUP BY 学号;

选择不及格门数最多的同学

SELECT top 1 学号,count(成绩) as 不及格门数;

FROM score WHERE 成绩<60;

GROUP BY 学号 ORDER BY 不及格门数 desc

练习题

?查询各送货方式的平均金额、总金额

?查询员工人数超过三个（不含三个）的部门，列出部门号、员工人数

?查询员工平均年龄<28岁的部门，列出部门号、该部门员工平均年龄和最年轻员工年龄

查询各送货方式平均金额、总金额

SELECT 送货方式, avg(金额), sum(金额)

FROM order1 GROUP BY 送货方式 HAVING avg(金额)>800

查询平均金额>800的各送货方式

SELECT 送货方式, avg(金额), sum(金额)

FROM order1 WHERE 金额>800

GROUP BY 送货方式 HAVING avg(金额)>1000

查询员工人数超过三个的部门

SELECT 部门号, COUNT(雇员号) FROM 雇员

GROUP BY 部门号 HAVING COUNT(雇员号)>3

查询女员工人数超过三个的部门

SELECT 部门号, COUNT(雇员号)  FROM 雇员 WHERE 性别 = ‘女’

GROUP BY 部门号  HAVING COUNT(雇员号)>3

查询员工平均年龄<28岁的部门

SELECT 部门号, AVG(年龄), MIN(年龄)

FROM 雇员  GROUP BY 部门号  HAVING AVG(年龄)<28

输出合并UNION

合并两个查询结果，两个查询结果必须列数和相应列的数据类型均相同。

注：UNION ALL表示全部合并,没有ALL则过滤重复记录

例：列出选修’001’或’003’课程的所有学生的学号

SELECT  学号 FROM score WHERE 课号 ＝ ‘001’ ;

  UNION SELECT  学号 FROM score WHERE 课号 ＝ ‘003’

重定向输出－INTO子句

INTO子句表示查询结果的输出，一般有三种选择：

–数组ARRAY

–临时表（游标–）CURSOR (临时表一旦关闭就被删除)

–表DBF | TABLE。

SELECT * FROM ORDER1

WHERE 金额>800

INTO ARRAY a

SELECT * FROM ORDER1

WHERE 金额>800

INTO CURSOR ORDER800

SELECT * FROM ORDER1

WHERE 金额>1000

INTO TABLE ORDER1000

多表查询－等值连接

例1：查询所有订单的公司名称、订单日期、金额

SELECT 公司名称,订单日期,金额;

FROM ORDER1,CUST;

WHERE ORDER1.客户编号=CUST.客户编号

SELECT b.公司名称,a.订单日期,a.金额;

FROM ORDER1 a,CUST b;

WHERE a.客户编号=b.客户编号

例2：查询选修大学语文且分数>80分的学生学号、姓名、分数

SELECT student.学号, 姓名, 分数;

FROM student, sc, course;

WHERE student.学号=sc.学号

and sc.课程编号=course.课程编号;

and 分数 > 80 and 课程名称 = '大学语文'

例3：查询平均分>80分的学生姓名和平均分

SELECT 姓名, avg(分数);

FROM student, sc, course;

WHERE student.学号=sc.学号

 and sc.课程编号=course.课程编号;

GROUP BY student.学号;

HAVING avg(分数) > 80

例4：查询以C++为直接先修课程的所有课程记录

SELECT a.* ;

FROM course a,course b ;

WHERE a.先修课号=b.课程号 and b.课程名 = 'C++'

连接查询

?内部连接（自然连接）

–只连接两张表的匹配项目

?外部连接

–两张表的不–匹配项目用NULL填充

内部连接－INNER JOIN

查询金额大于800元的订单的公司名称、订单日期、金额

SELECT 公司名称,订单日期,金额 ;

    FROM order1 INNER JOIN cust ON order1.客户编号 = cust.客户编号 ;

  WHERE 金额 > 800

（INNER JOIN 可缩略为 JOIN）

SELECT 姓名, avg(分数);

FROM student INNER JOIN sc INNER JOIN course

  ON sc.课程编号=course.课程编号 ON student.学号=sc.学号

超过两张表的连接，先做最内层的连接，并用内层连接的结果做为外层连接的数据源，依此类推。

外部连接

左外连接（左连接）－LEFT [OUTER] JOIN

–左表的记录全。C右表如无匹配项则用NULL填充

右外连接（右连接）－RIGHT [OUTER] JOIN

–右表的记录全。C左表如无匹配项则用NULL填充

全外连接（完全连接）－FULL [OUTER] JOIN

–左右表的记录都全。C一方如无匹配项则用NULL填充

练习题1

一个数据库STSC，其中有数据库表STUDENT、SCORE和COURSE，利用SQL语句查询选修了“C++”课程的学生的全部信息，并将结果按学号升序存放在CPLUS．DBF文件中。

select a.* ;

from student a,score b,course c;

where a.学号 = b.学号 and b.课程号 = c.课程号 and 课程名='C++' ;

order by a.学号 ;

into dbf cplus

练习题2

已有YUANGONG和ZHICHENG表。现在要给每个人增加工资，请计算YUANGONG表的新工资字段，方法是根据ZHICHENG表中相应职称的增加百分比来计算。

新工资=工资*（1+增加百分比/100）

use yuangong

scan

select 增加百分比 from zhicheng ;

  where 职称代码=yuangong.职称代码 ;

  into array a

replace 新工资 with 工资*(1+a(1,1)/100)

endscan

use

练习题3

查询各课程男生成绩>80的人数，列出课程、人数

SELECT 课程号, count(成绩)

FROM student, score

WHERE student.学号=score.学号

AND 成绩>80 AND 性别=‘男’

GROUP BY 课程号

查询平均分>80分的女生，列出学号、平均分

SELECT student.学号, avg(成绩)

FROM student, score

WHERE student.学号=score.学号 AND 性别=‘女’

GROUP BY student.学号 HAVING avg(成绩)>80

查询各地订单的平均金额，列出平均金额>1000的地区和平均金额

SELECT 所在地, avg(金额)

FROM cust, order1

WHERE cust.客户编号=order1.客户编号

GROUP BY 所在地

HAVING avg(金额)>1000

练习题4：

给定Student和score表，查询选课门数是3门以上（不包括3门）的每个学生的学号、姓名、平均成绩、最低分和选课门数，要求结果按平均成绩降序排列，列出前3名，并输出到数据表s1。

SELECT TOP 3 student.学号 AS 学号, 姓名,;

avg(成绩) AS 平均成绩, min(成绩) AS 最低分,;

count(成绩) AS 选课门数;

FROM student,score ;

WHERE student.学号 = score.学号;

GROUP BY student.学号 ;

HAVING count(成绩) > 3;

ORDER BY 平均成绩 DESC;

INTO CURSOR s1

3.3 数据操作

?插入记录（INSERT命令）

1  INSERT INTO student(学号,姓名) VALUES(‘010700035’,’丁一’)

2  INSERT INTO student VALUES(‘010700035’,’丁一’,‘男’)

若表名后无字段，则VALUES中应包含表中所有字段，且顺序与表定义中的字段顺序一致

?更新记录（UPDATE命令）

?更新所有记录

–UPDATE sc SET 平时=平时*0.3,期末=期末*0.7

?更新符合条件的记录

–UPDATE sc SET 综合 = 平时＋期末

–    WHERE 修读性质 = ‘初修’

?删除记录（DELETE命令）

?删除所有记录

–DELETE FROM score

?删除符合条件的记录

–DELETE FROM score WHERE  成绩 > 100 or 成绩 < 0

DELETE仅做逻辑删除，要彻底删除可用PACK

3.4数据定义

?新建表（CREATE  TABLE 命令）

格式：CREATE TABLE|DBF 表名（字段名1 字段类型（字段长度）[NOT NULL]

[CHECK]…）

?CREATE TABLE student(学号 C(15)  PRIMARY KEY,;

       姓名 C(10) NOT NULL, 性别 C(2) DEFAULT ‘男’, 出生年月 D)

?PRIMARY KEY为主键(主索引)

?UNIQUE 为候选索引

?NOT NULL为设置字段非空

?DEFAULT为设置默认值

?表的修改（ALTER TABLE命令）

?添加 ‘籍贯’ 字段

–ALTER TABLE XSCJ ADD COLUMN 籍贯 C(30)

?修改 ‘籍贯’ 字段

–ALTER TABLE XSCJ ALTER COLUMN 籍贯 C(50)

?删除 ‘籍贯’ 字段

–ALTER TABLE XSCJ DROP COLUMN 籍贯

?表的删除 （DROP  TABLE命令）

?删除student表

–DROP TABLE student

[教学总结]：

本章主要学习了数据库结构化查询语言SQL，实现对表的选择、投影与连接操作。WHERE子句对应选择操作（选择行），SELECT子句对应投影操作（选择列），FROM子句对应连接操作（多表操作）；SELECT语句中条件表达式用到的所有运算符（关系、逻辑、特殊）消除重复行DISTINCT子句，库函数，分组GROUP BY子句、分组条件HAVING 子句、对查询结果进行排序的ORDER BY 子句，嵌套查询连接查询等；数据操作语句INSERT、UPDATE、DELETE;数据定义语句CREATE、ALTER、DROP。

[作业布置]：

实验5.1

P101选择题1、3、5;  填空题1、2、3

P101选择题2、4、8;  填空题11、12、13