第6章 结构化查询语言SQL.ppt

1、第6章 结构化查询语言SQL,SQL概述 SQL的定义功能 SQL的操作功能 SQL的查询功能,6.0 复习,1. 查询的建立与维护 建立查询的方式 查询设计器 查询向导 结构化查询语言 在命令窗口中执行查询DO 查询文件名.qpr 查询修改 命令：MODIFY QUERY 查询文件名 用文本编辑器直接修改查询文件,6.0 复习,补充例题1： 在“图书管理”数据库中有STUDENT、BORROW两个表。请按班级统计查询男生的借书的数目，并按班级排序，其输出字段有：班级、所借书数目、借书日期。分组条件是有2006年5月1日之前的记录 每做一步运行一次 当没有连接条件时是自然连接 当有统计函数时，

2、输出记录数是分组字段值的个数 分组条件不能写到筛选条件中,6.0 复习,2. 视图的建立与维护 建立视图的方式 查询设计器 查询向导 结构化查询语言 浏览视图与表相似 修改视图 用命令调出视图设计器：MODIFY VIEW 视图名 用菜单调出视图设计器 视图更名与删除见P114,6.0 复习,补充例题2：创建数据库gz，添加表zgda，然后提取zgda中的男职工记录，用姓名、职称、基本工资3个字段组成一个名为vwgcs的视图。并设置SQL更新条件：关键字段为姓名，可更新的字段为职称。注意：发生冲突时，用TABLEREVERT()放弃修改，或者用TABLEUPDATE(0,.T.)强制修改,6.

3、1 SQL语言概述,SQL语言是一种一体化的语言，提供了完整的数据定义、操作、控制功能 SQL语言具有完备的查询功能 SQL语言非常简洁，易学易用 SQL语言是一种高度非过程化的语言 SQL语言既能以交互方式直接使用，也能嵌入到各种高级语言中使用 SQL语言不仅能对数据表进行各种操作，也可对视图进行操作,6.2 SQL的定义功能,6.2.1 建立表结构 格式：CREATE TABLE|DBF NAME FREE( (字段宽度,小数位数)NULL|NOT NULLCHECK ERRORDEFAULT PRIMARY KEY|UNIQUEREFERENCES TAG ,NOCPTRANS , ,P

4、RIMARY KEY TAG |,UNIQUE TAG ,FOREIGN KEY TAG NODUP REFERENCES TAG ,CHECK ERROR) |FROM ARRAY ,命令结构： CREATE TABLE ; ( ;(字段宽度,小数位数),; (字段宽度,小数位数), ; ),类型 宽度 小数位数 说明C n 字符型，长度为nD 日期型 T 日期时间型N n d 数值型，宽为n，d位小数F n d 浮点型，宽为n，d位小数I 整型B d 精度为d的双精度型Y 货币型L 逻辑型M 备注型G 通用型,【例6-1】创建一个名为“通信录”的自由表，含有姓名、生日、电话号码、手机号码4

5、个字段。 CREATE TABLE 通信录 FREE (;姓名 C(8),;生日 D,;电话号码 C(14),;手机号码 C(12),【例6-2】创建一个“商品管理”数据库，在其中创建一个“供应商”表，包含供应商号、供应商名称、地址、电话、传真5个字符型字段，并以“供应商号”字段为关键字创建一个主索引 CREATE DATABASE 商品管理 CREATE TABLE 供应商 (;供应商号 C(8) PRIMARY KEY,;供应商名称 C(16),;地址 C(24),;电话 C(14),;传真 C(8),【例6-3】在“商品管理”数据库中创建一个“商品”表，包含商品号、商品名称、单价、数量、

6、供应商号5个字段；以“商品号”为关键字创建一个主索引并为其设定一个默认值，为“数量”建立一个取值范围；再以“供应商”表为父表通过共有的“供应商号”为关键字建立两表间的永久关系。,OPEN DATABASE 商品管理 CREATE TABLE 商品 (;商品号 C(8) DEFAULT “JP_10109“ PRIMARY KEY,;商品名称 C(16), ;单价 N(8,2),;数量 N(4) CHECK 数量=10 AND 数量5000 ERROR “数量应在10到5000之间！“,;供应商号 C(8),;FOREIGN KEY 供应商号 TAG 供应商号 REFERENCES 供应商),【

7、综合例题】创建如图所示数据库“学生系统”。,数据库“学生系统”创建“学生”表：学号 C(12)；姓名 C(20)；出生日期 D；年龄 I，年龄要求大于0；性别 C(2)，只能输入“男”或“女”，籍贯 C(40)，班级 C(20)用学号建立主索引，索引标识为学号，用班级和姓名建立候选索引，标识为bjxm创建“综合测评”表：学号；学习测评 I，在0-60之间取值；其它测评 I，在0-40之间；用学号建立主索引，并以“学生”为父表建立一对一的关系创建“出勤”表：班级，姓名，旷课 I，请假 I，用班级与姓名建索引，与“学生”建立一对多的关系创建“借书”表：学号 C(12)，书号 C(20)，书名 C(

8、40)，借书日期 D，用学号建立索引，并与“学生”表建立一对多的关系,CREATE DATABASE 学生系统 CREATE TABLE 学生 (;学号 C(12) NOT NULL,;姓名 C(20) NOT NULL,;出生日期 D,;年龄 I CHECK 年龄0 ERROR “年龄应大于0!“,;性别 C(2) DEFAULT “男“ CHECK 性别$“男女“ ;ERROR “性别只能为男或女“,;籍贯 C(40),;班级 C(20),;PRIMARY KEY 学号 TAG 学号,;UNIQUE 班级+姓名 TAG bjxm),CREATE TABLE 综合测评 (;学号 C(12)

9、NOT NULL;PRIMARY KEY REFERENCE 学生 TAG 学号,;学习测评 I DEFAULT 0 CHECK 学习测评=0;AND 学习测评=0 ;AND 其它测评=40 ERROR;“其它测评在0到60分之间“),CREATE TABLE 出勤 (班级 C(20),;姓名 C(20),;旷课 I DEFAULT 0,;请假 I DEFAULT 0,;FOREIGN KEY 班级+姓名 TAG bjxm;REFERENCE 学生 TAG bjxm ) CREATE TABLE 借书 (学号 C(12),;书号 C(20) NOT NULL,;书名 C(40),;借书日期 D

10、 NOT NULL,;FOREIGN KEY 学号 TAG 学号;REFERENCE 学生 TAG 学号),6.2.2 修改表结构,1命令格式1 格式：ALTER TABLE ADD|ALTER COLUMN (字段宽度,小数位数)NULL|NOT NULL CHECK ERROR DEFAULT PRIMARY KEY|UNIQUE REFERENCES TAG NOCPTRANS,功能：为指定的表增加指定的字段，或者修改指定的字段 【例6-4】为例6-1创建的“通信录”表添加一个宽度为24的“家庭住址”字段，并将其“姓名”字段的宽度改为10 ALTER TABLE 通信录 ADD 家庭住址

11、 C(24) ALTER TABLE 通信录 ALTER 姓名 C(10),2命令格式2,格式：ALTER TABLE ALTER COLUMN NULL|NOT NULL SET DEFAULT SET CHECKERRORDROP DEFAULTDROP CHECK 功能：设置或删除指定表中指定字段的默认值和（或）约束条件 格式1与格式2的差别：格式2只改有效性规则，而格式1在改字段定义时，可同时修改有效性规则与参照完整性，格式1可增加字段定义,【例6-5】在例6-3创建的“商品”表中，为“单价”字段设置一个默认值“888.88”，并删除“数量”字段的条件约束。 OPEN DATABASE

12、 商品管理 ALTER TABLE 商品 ALTER 单价 SET DEFAULT 888.88 ALTER TABLE 商品 ALTER 数量 DROP CHECK,3命令格式3,格式：ALTER TABLEDROP COLUMNSET CHECKERRORDROP CHECKADD PRIMARY KEY TAG DROP PRIMARY KEYADD UNIQUE TAG DROP UNIQUE TAG ,ADD FOREIGN KEY TAG REFERENCES TAG DROP FOREIGN KEY TAG SAVE RENAME COLUMN TO NOVALIDATE,功能：

13、删除指定表中的指定字段、设置或删除指定表中指定字段的约束条件、增加或删除主索引、候选索引、外索引，以及对字段名重新命名等。,允许违反完整性,【例6-6】在例6-1创建的“通信录”表中，删除“家庭住址”字段，并将其“电话号码”字段更名为“家庭电话” ALTER TABLE 通信录 DROP COLUMN 家庭住址 ALTER TABLE 通信录 RENAME COLUMN 电话号码 TO 家庭电话,6.2.3 建立视图,格式：CREATE VIEW (字段名1,字段名2) AS 注意：应该先打开数据库,【例6-7】创建zg数据库，并添加表zgda，然后创建一个视图zcpjgz，记录为不同职称的平

14、均工资。 CREATE DATABASE zg ADD TABLE zgda CREATE SQL VIEW zcpjgz AS;SELECT 职称,AVG(基本工资) AS 平均工资;FROM zgda GROUP BY 职称,【例6-8】在“商品管理”数据库中，创建一个名为“贵重商品”的视图，其记录为“商品”表中单价大于1000元的商品记录构成。 OPEN DATABASE 商品管理 CREATE SQL VIEW 贵重商品 AS;SELECT * FROM 商品 WHERE 单价1000 USE 贵重商品 BROWSE,6.2.4 删除表,格式：DROP TABLE 注意：删除数据表时，

15、应该打开数据库，否则只删除数据表本身，它在数据库字典中的登记信息并未删除 【例6-10】删除“商品管理”数据库中的“供应商”表 OPEN DATABASE 商品管理 ALTER TABLE 商品 DROP FOREIGN KEY TAG 供应商号 DROP TABLE 供应商,6.3 SQL的操作功能,6.3.1 插入纪录 1命令格式1 格式：INSERT INTO (,)VALUES(,) 功能：在指定表的尾部添加一条新记录，并将指定的值赋给对应的字段。,2命令格式2,格式：INSERT INTO FROM ARRAY |FROM MEMVAR 功能：由指定数组或内存变量的值在指定表的尾部添

16、加一条新记录。 插入的数据的类型与对应的字段类型应该一致 由内存变量插入时，内存变量名与字段变量名应该一致,【例6-11】创建数据表t1；字段x，字符型，长度为4；字段y，数值型，长度为7，小数位数为2。然后：(1)直接插入一条记录；(2)由内存变量插入一条记录；(3)由数组变量插入一条记录 CREATE TABLE t1 (x c(8),y n(7,2) INSERT INTO t1 VALUES(0001,1050) x=0002y=2000INSERT INTO t1 FROM MEMVAR DIMENSION v(2)v(1)=0003v(2)=3000.50INSERT INTO t

17、1 FROM ARRAY v,6.3.2 更新数据,格式：UPDATE SET = ,= WHERE 功能：对于指定的表中符合条件的记录，用指定的表达式值来更新指定的字段值。缺省where子句时，是对所有记录进行更新。,【例6-12】将zgda复制为zgbak，再用SQL命令将zgbak中的年龄增加1岁，基本工资增加20%；然后再给女职工的基本工资增加80元 USE ZGDA COPY TO ZGBAK UPDATE zgbak SET 年龄=年龄+1,;基本工资=基本工资*1.2 UPDATE zgbak SET 基本工资=基本工资+80;WHERE 性别=女,6.3.3 删除纪录,格式：D

18、ELETE FROM WHERE 功能：对指定表中符合条件的记录，进行逻辑删除 【例6-13】用SQL命令删除zgbak中的职称为助工的记录 DELETE FROM zgbak WHERE 职称=助工 PACK,6.4 SQL的查询功能,6.4.1 SQL查询命令 格式：SELECT ALL | DISTINCTTOP PERCENTAS , AS FROM ! AS INTO | TO FILE | TO PRINTER | TO SCREEN,WHERE AND AND | OR AND | OR GROUP BY ,HAVING ORDER BY ASC | DESC, ASC | DE

19、SC 功能：根据指定的条件从一个或多个表中检索并输出数据,基本结构：SELECT 检索项;FROM 数据表;WHERE 连接条件或筛选条件;,主结构,GROUP BY 字段表 HAVING 分组条件;ORDER BY 排序项,6.4.2 简单查询,【例6-16】查询Student表中所有外地（籍贯非北京）女生的姓名、年龄、出生日期与籍贯。 SELECT 姓名, 年龄, 出生日期, 籍贯 ;FROM Student;WHERE 性别=“女“ AND 籍贯!=“北京“ 【例6-17】查询Student表中有哪些班级的学生（不重复显示相同的班级）。 SELECT DISTINCT 班级 FROM S

20、tudent,【例6-18】查询zgda表中基本工资最高的四名职工的记录 SELECT * TOP 4 FROM zgda;ORDER BY 基本工资 DESC 【例6-19】查询zgda中职称为工程师的记录，并把姓名、职称、基本工资保存在表gcs.dbf SELECT 姓名,职称,基本工资 FROM zgda;WHERE 职称=工程师 INTO TABLE gcs,6.4.3 特殊运算符,使用BETWEEN、LIKE、IN和NOT IN等特殊运算符，使查询更为方便灵活。 通配符有两种：%：通配0个或多个_：通配一个字符,【例6-21】在zgda表中查询年龄在30到35之间的记录，并按出生日期

21、排序后显示出来。 SELECT * FROM zgda;WHERE 年龄 BETWEEN 30 AND 35;ORDER BY 出生日期,【例6-22】在zgda表中查询并输出所有姓李的记录；查询并输出除姓王的以外的所有记录；查询并输出所有姓李的且为单名的记录 SELECT * FROM zgda;WHERE 姓名 LIKE 李% SELECT * FROM zgda;WHERE 姓名 NOT LIKE 王% SELECT * FROM zgda ;WHERE 姓名 LIKE 李_,【例6-23】在Student表中查询所有籍贯为“内蒙古”或“山东”的学生记录。 SELECT * FROM S

22、tudent;WHERE 籍贯 IN (“内蒙古“,“山东“),6.4.4 嵌套查询,在一个SELECT查询命令的WHERE短语中，包含另一个SELECT查询命令。 【例6-25】依据“图书管理”数据库中Student表和Borrow表的数据，列出未曾借过书的男生记录。 SELECT * FROM Student;WHERE 性别=“男“ AND 学号;NOT IN (SELECT 学号 FROM Borrow),6.4.5 多表查询,基于多个相关联数据表的查询。 【例6-26】 依据Student表和Borrow表的数据，查询所有有借书记录的男学生，并输出姓名、所在班级、书名、借书日期。 S

23、ELECT Student.姓名, Student.班级,;borrow.书名, Borrow.借书日期;FROM Student, Borrow;WHERE Student.学号= Borrow.学号 AND ;student.性别= “男“,下面的解答是否正确？SELECT Student.姓名, Student.班级,;borrow.书名, Borrow.借书日期;FROM Student, Borrow;WHERE student.性别= “男“ AND ; Student.学号 IN (SELECT 学号 FROM borrow),【例6-27】 依据Student表和Borrow表

24、的数据，查询借阅“大学英语”书的学生的学号、姓名、所在班级、借书日期，并借书日期排序。 SELECT Student.学号, Student.姓名,;Student.班级, Borrow.借书日期;FROM Student, Borrow;WHERE Student.学号= Borrow.学号 AND ;Borrow.书名=“大学英语“;ORDER BY borrow.借书日期,6.4.6 超联接查询,通过包含在SELECT-SQL命令中的FROM JOIN ON 短语来实现基于多个相关数据表的查询 格式：SELECT FROM INNER | LEFT | RIGHT | FULL JOIN

25、 ON WHERE ,INNER JOIN即JOIN，内部联接，查询结果中仅包含两个表中与联接字段值相匹配的记录。 LEFT JOIN为左联接，查询结果中包含左侧表中的所有记录，以及两个表中与联接字段值相匹配的记录 RIGHT JOIN为右联接，查询结果中包含右侧表中的所有记录，以及两个表中与联接字段值相匹配的记录。 FULL JOIN为完全联接，查询结果中包含两个表中与联接字段值相匹配的和不相匹配的所有记录。 ON 短语用来指定两个表之间的联接条件。,学号 姓名,学号 书名 借书日期,SELECT 姓名,书名 FROM tmp1 INNER JOIN tmp2 ON tmp1.学号=tmp2

26、.学号,SELECT 姓名,书名 FROM tmp1 LEFT JOIN tmp2 ON tmp1.学号=tmp2.学号,SELECT 姓名,书名 FROM tmp1 RIGHT JOIN tmp2 ON tmp1.学号=tmp2.学号,SELECT 姓名,书名 FROM tmp1 FULL JOIN tmp2 ON tmp1.学号=tmp2.学号,【例6-26】依据Student表和Borrow表的数据，查询所有有向图书馆借书的男生记录，并输出姓名、所在班级、书名、借书日期。 SELECT Student.姓名, Student.班级,;borrow.书名, Borrow.借书日期;FROM

27、 Student JOIN Borrow ON;Student.学号= Borrow.学号 WHERE;student.性别= “男“,【例6-27】依据Student表和Borrow表的数据，查询借阅“大学英语”书的学生的学号、姓名、所在班级、借书日期，并借书日期排序。 SELECT Student.学号, Student.姓名,;Student.班级, Borrow.借书日期;FROM Student JOIN Borrow ON;Student.学号=Borrow.学号 ;WHERE Borrow.书名=“大学英语“;ORDER BY borrow.借书日期,6.4.7 统计查询,通过如

28、下几个库函数实现对查询结果的统计。 SUM( ) 计算指定数值列的总和 AVG( ) 计算指定数值列的平均值 MAX( ) 求指定（数值、字符、日期）列的最大值 MIN( ) 求指定（数值、字符、日期）列的最小值 COUNT( ) 求查询结果数据的行（记录）数,【例6-32】查询Student表中年纪最小学生的生日；然后统计男生的平均年龄。 SELECT MAX(出生日期) FROM Student SELECT AVG(年龄) FROM Student WHERE 性别=“男“,【例6-33】查询Student表中籍贯为“山东”的学生人数。 SELECT COUNT(*) AS 山东学生人数

29、 FROM Student WHERE 籍贯=“山东“ 【例6-34】查询Student表中的学生来自几个不地区。 SELECT COUNT(distinct 籍贯) AS;不同地区数 FROM Student,6.4.8 分组查询,通过GROUP BY短语将检索得到的数据依据某个字段的值划分为多个组后输出。 【例6-35】依据Student表中的数据，分别统计各个班级的人数。 SELECT 班级, COUNT(*) AS 人数;FROM Student GROUP BY 班级,【例6-36】分别统计Student表中来自北京、江西、浙江的人数，输出籍贯、人数。 SELECT 籍贯, COUNT(*) AS 人数;FROM Student GROUP BY 籍贯;HAVING 籍贯 IN (“北京“,“江西“,“浙江“) 【例6-37】按职称统计zgda中的平均基本工资、总基本工资，并输出职称、平均工资、总工资 SELECT 职称,AVG(基本工资) AS 平均工资,;SUM(基本工资) AS 总工资;FROM zgda GROUP BY 职称,