[PPT制作技巧]第五章 SQL语言及应用.ppt

1,第5章 SQL语言及应用,在数据库应用中SQL语言无处不在，它已经不只是单纯的一种查询语言，更多的是连接人和数据库系统之间的一座桥梁，无论你是开发管理信息系统还是作为数据库系统的管理者，都离不开使用SQL语言来执行人与数据库之间的信息交流。本章我们将学习SQL语言的基础知识和应用实例。,2,SQL全称是“结构化查询语言(Structured Query Language)”，最早的是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEM R开发的一种查询语言，它的前身是SQUARE语言。SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年推出以来，SQL语言，得到了广泛的应用。如今无论是像Oracle,Sybase,Informix,SQL server这些大型的数据库管理系统，还是像Visual Foxpor,PowerBuilder这些微机上常用的数据库开发系统，都支持SQL语言作为查询语言。,3,第5章 SQL语言及应用,1.概述SQL是一种面向数据库的通用数据处理语言规范，能完成以下几类功能：提取查询数据，插入修改删除数据，生成修改和删除数据库对象，数据库安全控制，数据库完整性及数据保护控制。数据库对象包括表、视图、索引、同义词、簇、触发器、函数、过程、包、数据库链、快照等（表空间、回滚段、角色、用户）。数据库通过对表的操作来管理存储在其中的数据。,4,SQL语言具有如下主要特点：SQL是一种一体化的语言，它包括了数据定义、数据查询、数据操纵和数据控制等方面的功能，它可以完成数据库活动中的全部工作。SQL语言是一种高度非过程化的语言，它没有必要一步步地告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。SQL语言非常简洁。SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。VB是将SQL语言直接融入到自身的语言之中，使用起来更方便。,5,第5章 SQL语言及应用,2.SQL语言的组成SQL语言是由命令（函数）、子句、运算符、加总函数及通配符等组成，分述如下：1)、命令SQL的命令可分成数据定义语言与数据操作语言，数据定义语言可用来建立新的数据库、数据表、字段及索引等，本教程不予介绍；另一为数据操作语言，可用来建立查询表、排序、筛选数据、修改、增删等动作。数据定义语言命令常用的有选择、添加、删除和修改这四种：SELECT:用于找出合乎条件的记录 INSERT:用于增加一笔记录或合并两个数据表 UPDATE:用于更正合乎条件的记录 DELETE:用于删除合乎条件的记录,6,表5.1 SQL命令动词,7,第5章 SQL语言及应用,2)子句子句是用于设定命令要操作的对象（即参数），SQL所用的子句如下：、子句：FROM 中文意思：数据表说明：用于指定数据表、子句：WHERE中文意思：条件说明：用于设定条件、GROUP BY中文意思：分组（合并）说明：用于设定分组、ORDER BY中文意思：排序说明：用于设定输出的顺序及字段,8,第5章 SQL语言及应用,3）运算符子句参数中的运算符使子句构成不同的语法格式，如“字段1=100”、“字段1100”等。运算符又分逻辑运算符与比较运算符。逻辑运算符如下：、运算符：AND中文意思：并且说明：逻辑且、运算符：OR 中文意思：或者说明：逻辑非、运算符：NOT中文意思：取反说明：逻辑非或逻辑反,9,第5章 SQL语言及应用,比较运算符如下：、运算符：说明：大于、运算符：=说明：等于、运算符：说明：不等于、运算符：BETWEEN 说明：用于设定范围 中文意思：在.之间、运算符：LIKE 说明：用于通配设定 中文意思：如同、运算符：IN 说明：用于集合设定 中文意思：在.之内,10,第5章 SQL语言及应用,4）加总函数加总函数常常运用在命令的参数中，如：“SELECT SUM（数学），AVG（数学）FROM 成绩单”。、加总函数：AVG 中文意思：平均。说明：用于求指定条件的平均、加总函数：COUNT中文意思：数量说明：用于求指定的数量、加总函数：SUM中文意思：和说明：用于求指定条件的和、加总函数：MAX中文意思：最大值说明：用于求指定条件的最大值、加总函数：MIN中文意思：最小值说明：用于求指定条件的最小值,11,第5章 SQL语言及应用,5）通配符、通配符：%意义：任何长度的字符串（包括0）、通配符：_ 意义：下划线表示任何一个字符、通配符：意义：中括号表示某个范围内的一个字符,12,第5章 SQL语言及应用,3 数据库查询 1）用SELECT语句从表中提取查询数据。语法为 SELECT ALL|DISTINCT.,.FROM,WHEREANDGROUP BY,HAVINGORDER BYASC|DESC,ASC|DESC说明：命令中各参数的含义如下：SELECT是该命令的主要关键字。ALL|DISTINCT表示ALL和DISTINCT任选其 一，ALL表示所有的记录，DISTINCT表示 去掉重复记录。FROM 说明要查询的数据来自哪个或哪些表，可以对单个表或多个表进行查询；WHERE说明查询条件，即选择元组的条件；GROUP BY 短语用于对查询结果进行分组，可以利用它进行分组汇总；HAVING 短语必须跟随GROUP BY使用，它用来限定分组必须满足的条件；ORDER BY 短语用来对查询的结果进行排序,13,2）WHERE子句。(LIKE,IS,)WHERE子句中的条件可以是一个包含等号或不等号的条件表达式，也可以是一个含有IN、NOT IN、BETWEEN、LIKE、IS NOT NULL等比较运算符的条件式，还可以是由单一的条件表达通过逻辑运算符组合成复合条件。模糊查询举例：3）ORDER BY 子句 ORDER BY 子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDER BY 子句指定的表达式的值确定,14,第5章 SQL语言及应用,4）连接查询 利用SELECT语句进行数据库查询时，可以把多个表、视图的数据结合起来，使得查询结果的每一行中包含来自多个表达式或视图的数据，这种*作被称为连接查询。连接查询的方法是在SELECT命令的FROM子句中指定两个或多个将被连接查询的表或视图，并且在WHERE子句告诉数据库如何把多个表的数据进行合并。根据WHERE子句中的条件表达式是等还是不等式，可以把连接查询分为等式连接和不等式连接。5）子查询 如果某一个SELECT命令（查询1）出现在另一个SQL命令（查询2）的一个子句中，则称查询1是查询2的子查询。,15,1、从职工关系中检索所有工资值。SELECT 工资 FROM 职工可以看到在结果中有重复值，若要去掉重复值只需要指定DISTINCT短语。SELECT DISTINCT 工资 FROM 职工再比如有一张含firstname,lastname,city三个字段的数据表employee。select*from employee where firstname=May查询所有名称为May的行。select firstname,lastname,city from employee where firstname LIKE E%SQL语句将会查询所有名称以E开头的姓名,4 简单查询举例,16,2、查询仓库关系中的所有元组。SELECT*FROM 仓库,17,3、查询工资多于1230元的职工号。SELECT 职工号 FROM 职工 WHERE 工资 1230,18,4、查询哪些仓库有工资多于1210元的职工。SELECT DISTINCT 仓库号 FROM 职工表 WHERE 工资 1210,19,5、查询在仓库“wh1”或“wh2”工作，并且工资少于1250元的职工号。SELECT 职工号 FROM 职工表WHERE 工资1250 AND(仓库号=”wh1”OR仓库号=”wh2”),20,例：1、查询工资多于1230元的职工号和他们所在的城市。SELECT 职工号,城市 FROM 职工,仓库;WHERE(工资1230)AND(职工.仓库号=仓库.仓库号,注：由表可知，仓库和职工之间存在着一个一对多的联系。当FROM之后的多个关系中含有相同的属性名时，这时必须用关系前缀直接指明属性所属的关系，如职工.仓库号，“.”前关系名，后是属性名,5 简单的联接查询,21,2、找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市。SELECT 职工号,城市 FROM 仓库,职工表;WHERE(面积400)AND(职工表.仓库号=仓库.仓库号),22,SELECT嵌套查询是一种子查询，子查询的特征是能够将一个查询的结果作为另一个查询的一部分，子查询是对查询结果的查询。在SQL中，有些查询不用子查询无法表达。子查询要加括号，并且与SELECT语句的形式类似，也有FROM子句，以及可选择的WHERE、GROUP BY和HAVING子句等。子查询中的子句与SELECT语句中的子句格式相同，用于子查询时，它们执行正常的功能，但是子查询和SELECT语句还是有如下区别：,6、嵌套查询,23,（1）子查询通常必须生成单字段数据作为其查询结果，即必须是一个确定的项。若为一个集合，则需要使用谓词演算查询。（2）ORDER BY子句不能用于子查询，子查询结果只是被主查询内部使用，对用户是不可见的，所以对它们的任何排序都是没有意义的。,24,例1、哪些城市至少有一个仓库的职工工资为1250元？SELECT 城市 FROM 仓库 WHERE 仓库号 IN;(SELECT 仓库号 FROM 职工 WHERE 工资=1250),25,2.查询所有职工的工资都多于1210元的仓库的信息。此查询也可描述为：没有一个职工的工资少于或等于1210元的仓库的信息。SELECT*FROM 仓库 WHERE 仓库号 NOT IN;(SELECT 仓库号 FROM 职工表 WHERE 工资=1210),26,注：尽管在“武汉“的”wh4”仓库还没有职工，但该仓库的信息也被检索出来了，所以必须认真分析检索要求，写出正确的SQL命令。如果要求排除那些还没有职工的仓库，检索要求可以叙述为：检索所有职工的工资都多于1210元的仓库的信息，并且该仓库至少要有一名职工。SELECT*FROM 仓库 WHERE 仓库号 NOT IN;(SELECT 仓库号 FROM 职工表 WHERE 工资=1210);AND 仓库号 IN(SELECT 仓库号 FROM 职工表),27,3、找出和职工E4挣同样工资的所有职工。SELECT 职工号 FROM 职工表 WHERE 工资=;(SELECT 工资 FROM 职工表 WHERE 职工号=”e4”),28,7、几个特殊运算符 BETWEEN AND：指定查询的条件是在什么范围内（数值）LIKE（%_）：指定查询与通配符相匹配的字符。,29,例1、查询出工资在1220元到1240元范围内的 职工信息。SELECT*FROM 职工表 WHERE 工资 BETWEEN 1220 AND 1240(工资=1220 AND工资=1240),30,2、从供应商关系中检索出全部公司的信息，不要工厂或其他供应商的信息。这是一个字符串匹配的查询，显然应该使用LIKE运算符。SELECT*FROM 供应商 WHERE 供应商名 LIKE“%公司”,31,3、找出不在北京的全部供应商信息。SELECT*FROM 供应商 WHERE 地址!=“北京”SELECT*FROM供应商WHERE NOT(地址=“北京”)NOT的应用范围很广，比如，可以有NOT IN、NOT BETWEEN等。若提出和例1相反的请求，找出工资不在1220元和1240元之间的全部职工信息，可以用命令：SELECT*FROM 职工表 WHERE 工资 NOT BETWEEN 1220 AND 1240,32,8、排序使用SQL-SELECT可以将查询结果排序，排的短语是ORDER BY，具体格式如下：ORDER BY 字段1 ASCDESC,字段2 ASCDESC 由以上格式可看出，可以按升序（ASC）或降序（DESC）排序，允许按一列或多列排序。,33,1、按职工的工资值升序检索出全部职工信息。SELECT*FROM 职工表 ORDER BY 工资（升序）,34,2、先按仓库号排序，再按工资排序并输出全部职工信息。SELECT*FROM 职工表 ORDER BY 仓库号,工资（升序）,注：ORDER BY是对最终的查询结果进行排序，不可以在子查询中使用该短语。,35,9、简单的计算查询SQL命令不仅具有一般的检索能力，而且还有计算方式的检索，比如检索职工的平均工资、检索某个仓库中职工的最高工资值等。用于计算检索的函数有：（1）COUNT计数（2）SUM求和（3）AVG计算平均值（4）MAX求最大值（5）MIN求最小值这些函数可以用在SELECT短语中对查询结果进行计算。,36,1、找出供应商所在地的数目。SELECT COUNT(DISTINCT 地址)FROM 供应商,注：除非对关系中的元组个数进行计数，一般COUNT 函数应该使用DISTINCT。如：SELECT COUNT(*)FROM 供应商 将给出供应商关系中的记录数。,37,2、求支付的工资总数。SELECT SUM(工资)FROM 职工 这个结果是职工关系中的工资值的总和，它并不管是否有重复值。这时若使用命令：SELECT SUM(DISTINCT 工资)FROM 职工3、求北京和上海的仓库职工的工资总和。SELECT SUM(工资)FROM 职工表 WHERE 仓库号 IN;(SELECT 仓库号 FROM 仓库 WHERE 城市=”北京”OR城市=”上海”)4、求所有职工的工资都多于1210元的仓库的平均面积。SELECT AVG(面积)FROM 职工表 WHERE 仓库号 NOT IN;(SELECT 仓库号 FROM 职工表 WHERE 工资=1210),38,注：以上结果的运算包含了没有职工的wh4仓库。若要排除没有职工的仓库，就改为：SELECT AVG(工资)FROM 职工表 WHERE 仓库号 NOT IN;(SELECT 仓库号 FROM 职工表 WHERE 工资=1210);AND仓库号 IN(SELECT 仓库号 FROM 职工表),39,5、求在wh2仓库工作的职工的最高工资值。SELECT MAX(工资)FROM 职工表 WHERE 仓库号=”wh2”与MAX函数相对应的是MIN函数（求最小 值）。若求最低工资值可以有如下命令：SELECT MIN(工资)FROM 职工表 WHERE=”wh2”,40,另如有一张销售信息表SELECT store_name FROM Store Information WHERE Sales1000则可以查询销售额超过1000的商店信息。SELECT SUM(Sales)FROM Store_Information则查询Store_Information数据表中所有商店的总销售额。SELECT store_name,SUM(Sales)FROM Store_Information GROUP BY store_name则可以使用SQL语言的GROUP BY命令将销售额按照不同的商店进行分组，从而计算出不同商店的销售总额。,41,10、分组与计算查询利用GROUP BY子句进行分组计算查询，格式如下：GROUP BY 字段1,字段2HAVING 条件 可以按一列或多列分组，还可以用HAVING进一步限定分组的条件。,42,1、求每个仓库职工的平均工资SELECT 仓库号,AVG(工资)FROM 职工表;GROUP BY 仓库号 在这个查询中，首先按仓库号属性进行分组，然后再计算每个仓库的平均工资。GROUP BY子句一般跟在WHERE子句之后，没有WHERE子句时，跟在FROM子句之后。另外，还可以根据多个属性进行分组。在分组查询时，有时要求分组满足某个条件时才检索，这时可以用HAVING子句来限定分组。,43,2、求至少有两个职工的每个仓库的平均工资。SELECT 仓库号,COUNT(*),AVG(工资)FROM 职工表;GROUP BY 仓库号 HAVING COUNT(*)=2,注：HAVING子句总是跟在GROUP BY子句之后，不可以单独使用。HAVING子句和WHER子句不矛盾，在查询中是先用WHERE子句限定元组，然后进行分组，最后再用HAVING子句限定分组。,44,11、利用空值查询 SQL支持空值，当然也可以利用空值进行查询。IS NULLIS NOT NULL假设在订购单关系中，一名职工正在准备订购单，但尚未选定供应商，这样若把信息存入数据库，则供应商号和订购日期两个属性均为空值。,45,1、找出尚未确定供应商的订购单。SELECT*FROM 订购单 WHERE 供应商号 IS NULL,注：查询空值是要使用IS NULL，而NULL是无效的，因为空值不是一个确定的值，所以不能用”=”运算符进行比较。,46,2、列出已经确定了供应商的订购单信息。SELECT*FROM 订购单 WHERE 供应商号 IS NOT NULL,47,12、别名与自联接查询在联接操作中，经常需要使用关系名作前缀，有时这样显得很麻烦。因此，SQL允许在FROM短语中为关系名定义别名，格式为：,48,比如：如下的联接语句是一个基于4个关系的联接查询，其中必须使用关系名作前缀；SELECT 供应商 FROM 供应商,订购单,职工,仓库;WHERE 地址=”北京”AND城市=”北京”;AND 供应商.供应商号=订购单.供应商号;AND 订购单.职工号=职工.职工号;AND 职工.仓库号=仓库.仓库号,49,在上面的查询中，如果使用别名就会简单一些，如下是使用了别名的同样的联接查询语句。SELECT 供应商 FROM 供应商 S,订购单 P,职工 E,仓库 W;WHERE 地址=”北京”AND城市=”北京”;AND S.供应商号=P.供应商号;AND P.职工号=E.职工号;AND E.仓库号=W.仓库号,50,1、添加数据格式一：INSERT INTO(,)VALUES(,)格式二：INSERT INTO 表名 FROM ARRAY 数组名|FROM MEMVAR,13、SQL的操作功能：插入、更新、删除,51,格式一:是向指定的表中插入记录，当插入的不是完整的记录时，可以指定字段。VALUES用来给出具体的记录值格式二:FROM ARRAY说明从指定的数组中插入记录值，FROM MEMVAR说明根据同名的内存变量来插入记录值，如果同名的变量不存在，那么相应的字段为默认值或空。例：往订购单关系中插入元组(”e7”,”s4”,”or01”,2001/05/25）,52,可用如下命令：INSERT INTO 订购单 VALUES(”e7”,”s4”,”or01”,2001/05/25）其中，2001/05/25是日期型字段订购日期的值，请注意这里日期型数据的表示方式。对以上情况，假设供应商尚未确定，那么只能先插入职工号和订购单两个属性的值，这里可用以下命令：INSERT INTO 订购单(职工号,订购单号)VALUES(”e7”,”or01”）用以上语句是对某些属性进行插入，这里另外两个属性的值为空。,53,2.更新UPDATE SET=表达式WHERE;,54,一般使用WHERE子句指定条件，以更新满足条件的一些记录的字段值，并且一次可以更新多个字段，如果不使用WHERE子句，则更新全部记录。例如：给wh1仓库的职工提高10%的工资，可以用如下命令。UPDATE 职工表 SET 工资=工资*1.1;WHERE 仓库号=”wh1”又如：给所有学生的成绩加1分；UPDATE 学生 SET 成绩=成绩+1,55,3.删除 DELETE FROM WHERE FROM指定从哪个表中删除数据，WHERE指定被删除的记录所满足的条件,若不使用WHERE子句,则删除该表中的全部记录.例：要删除仓库关系中仓库号值是wh2的元组，可用命令：DELETE FROM 仓库 WHERE 仓库号=”wh2”,56,1、表的定义 定义表名CREATETABLEDBF 表名 NAME 长表名FREE定义字段(字段1 字段类型(字段宽度),字段精度是否允许空值NULLNOTNULL（默认允许空值）实体完整性PRIMARY KEYUNIQUE REFERENCES 表名2 TAG 索引文件名,14、表定义功能,57,域完整性CHECK表达式出错提示信息ERROR提示信息定义默认值DEFAULT表达式 字段2 字段类型(字段宽度)，字段精度 同上 FROM ARRAY 数组名,58,从以上句法格式可以看出来，用CREATE TABLE命令建立表可以完成用第四章介绍的表设计嚣完成的所有功能。除了建立表的基本功能外，它还包括满足实体完整性的主关键字（主索引）PRIMARY KEY、定义域完整性CHECK约束及出错提示信息ERROR、定义默认值的DEFAULT等。,59,例：用命令建立订货管理1数据库。CREATE DATABASE 订货管理1用SQL CREATE命令建立仓库1表：CREATE TABLE 仓库1(;仓库号 C(5)PRIMARY KEY,城市 C(10),;面积 I CHECK(面积0)ERROR“面积应该大于0！”),60,例：用SQL CREATE命令建立职工1表。CREATE TABLE 职工1(;仓库号 C(5),;职工号 C(5)PRIMARY KEY,;工资 I CHECK(工资1000 AND 工资=5000);ERROR“工资值的范围在10005000!”DEFAULT 1200,;FOREIGN KEY 仓库号 TAG 仓库号 REFERENCES 仓库1),61,2.表的删除 DROP TABLE 作用:在SQL语言中使用drop table命令删除某个表格以及该表格中的所有记录。Drop table命令的使用格式为：drop table tablename；例如：drop table employee;如果用户希望将某个数据库表格完全删除，只需要在drop table命令后输入希望删除的表格名称即可。Drop table命令的作用与删除表格中的所有记录不同。删除表格中的全部记录之后，该表格仍然存在，而且表格中列的信息不会改变。而使用drop table命令则会将整个数据库表格的所有信息全部删除。,62,3.表结构的修改 格式一：ALTER TABLE 表名 ADDALTERCOLUMN字段1 字段类型(字段宽度)，字段精度NULLNOTNULL（默认允许空值）CHECK字段1ERROR提示信息DEFAULT表达式 PRIMARYKEYUNIQUEREFERENCES 表名2 TAG 索引文件名1,63,该命令格式可以添加(ADD)新的字段或修改(ALTER)已有的字段，它的句法基本可以与CREATE TABLE的语句相对应。,64,例1、为订购单1表增加一个货币类型的总金 额字段。ALTER TABLE 订购单1ADD 总金额 Y CHECK 总金额0 ERROR“总金额应该大于0!”,65,2、将订购单1表的订购单号字段的宽度由原来的5改为6。ALTER TABLE 订购单1 ALTER 订购单号 C(6)从命令格式可以看出，该格式可以修改字段的类型、宽度、有效性规则、错误信息、默认值，定义主关键字和联系等；但是不能修改字段名，不能删除字段，也不能删除已经定义的规则等。,66,格式二：ALTER TABLE 表名 ALTERCOLUMN字段1 NULLNOTNULLSET DEFAULT 表达式1 SET CHECK表达式2ERROR 提示信息DROP DEFAULTDROP CHECK 从命令格式可以看出，该格式主要用于定义、修改和删除有效性规则和默认值定义。,67,例：1、修改或定义总金额字段的有效性规则。ALTER TABLE订购单1ALTER 总金额 SET CHECK 总金额100 ERROR“总金额应该大于100!”,68,格式三：ALTER TABLE 表名1 DROP COLUMN字段1 SET DEFAULT 表达式1 ERROR 提示信息 DROP CHECK ADD PRIMARY KEY 表达式 TAG 索引名FOR 条件 DROP PRIMARY KEY ADD UNIQUE表达式 TAG索引名 FOR条件 DROP UNIQUE TAG索引名 ADD FOREIGN KEY表达式 TAG索引名 FOR条件 REFERENCES 表名2 TAG 索引名 DROP FOREIGN KEY TAG索引名 RENAME COLUMN 字段名 TO 新字段名,69,例：1、将订购单1表的总金额字段名改为金额。ALTER TABLE 订购单1 RENAME COLUMN总金额 TO 金额2、删除订购单1表中的金额字段。ALTER TABLE 订购单1 DROP COLUMN 金额3、将订购单1表的职工号和供应商号定义为候选索引(候 选关键字)，索引名是emp_sup。TABLE 订购单1 ADD UNIQUE 职工号+供应商号 TAG emp_sup,70,4、删除订购单1表的候选索引emp_sup ALTER TABLE 订购单1 DROP UNIQUE TAG emp_sup,