第4章关系数据库标准语言SQL.doc
第四章 关系数据库标准语言SQLSQL是structure query language （结构化查询语言）的缩写4.1 SQL概述主要特点 v SQL是一种一体化的语言；v SQL语言是一种高度非过程化的语言；v SQL语言非常简洁；v SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。SQL功能作用命令动词数据查询查询SELECT数据操纵插入INSERT更新UPDATE删除DELETE数据定义新建CREATE 删除DROP修改ALTER4.2 查询功能常用的SQL命令1SELECT 短语：用于说明要查询的数据。 2FROM 短语：用于说明要查询的数据来自哪个或哪些表，可对单个表或多个表进行查询。3WHERE 短语：用于说明查询条件；如果是多表查询还可通过该子句指明表与表之间的联接条件，进行联接。4GROUP BY 短语：用于对查询进行分组，可利用它进行分组汇总。5HAVING 短语：跟随在GROUP BY 之后使用，它是用来限定分组，要求只有满足分组条件的记录才能进行分组查询。6ORDER BY 短语：用于对查询的结果进行排序。7AS短语：用于指定查询结果中字段的新名称；AS前的表达式可以是一个字段名、表达式、函数等。4DISTINCT短语：用于说明该短语将在查询结果中去掉重复值。9INTO TABLE短语：用于说明查询结果保存何处。等函数4.2.1 简单查询格式： SELECT 选择字段 FROM 表 WHERE 条件例4.1从职工关系中检索所有工资值 SELECT 工资 FROM 职工DISTINCT 的作用是去除查询结果中的重复值例4.2检索仓库中的所有元组SELECT * FROM 仓库 *：表示通配符，代表表中所有的字段例4.3检索工资多于1230元的职工号 SELECT 职工号 FROM 职工 WHERE 工资>1230例4.4检索那些仓库有工资多于1210元得职工 SELECT DISTINCT 仓库号 FROM 职工 WHERE 工资>1210例4.5给出在仓库”WH1”或”WH2”工作并且工资少于1250元的职工号 SELECT 职工号 FROM 职工 ；WHERE 工资<1250 AND (仓库号=”WH1” OR仓库号=”WH2” )4.2.2 简单的连接查询基于简单的查询，增加一个连接条件进行查询例4.6找出工资多于1230元得职工号和他们所在的城市 SELECT 职工号，城市 FROM 职工，仓库；WHERE 工资>1230 AND 职工.仓库号=仓库.仓库号例4.7找出工作面积大于400的仓库的职工号以及这些职工工作的城市 SELECT 职工号，城市 FROM 仓库，职工；WHERE 面积>400 AND 职工.仓库号=仓库.仓库号4.2.3 嵌套查询例4.8那些城市至少有一个仓库的职工工资为1250元SELECT 城市 FROM 仓库 WHERE 仓库号 IN ;(SELECT 仓库号 FROM 职工WHERE 工资=1250) 例4.9查询所有职工的工资都多于1210元的仓库信息SELECT * FROM 仓库 WHERE 仓库号 NOT IN ;(SELECT 仓库号 FROM 职工 WHERE 工资<=1210)例4.10找出和职工E4挣同样工资的所有职工SELECT 职工号 FROM 职工 WHERE 工资=；（SELECT 工资 FROM 职工WHERE 职工号=”E4”）4.2.4 几个特殊运算符BETWEEN AND 和 LIKE 等例4.11检索出工资在1220元到1240元范围内的职工信息SELECT * FROM 职工 WHERE 工资 BETWEEN 1220 AND 1240例4.12从供应商关系中检索出全部公司的信息（不要工厂或其他gis的信息）SELECT * FROM 供应商 WHERE 供应商名 LIKE “%公司”l 这里 LIKE 是字符串 匹配运算符，通配符 “%”表示0个或者多个字符，另外还有一个通配符“_”表示一个字符例4.13找出不在北京的全部供应商信息SELECT * FROM 供应商 WHERE 地址！=”北京”l 在SQL 中“不等于”用 “！=”表示4.2.5 排序将查询结果排序升序（ASC）或者降序(DESC) 可以按照一个或者多个字段排序例4.14按照职工的工资升序检索出全部职工信息SELECT * FROM 职工 ORDER BY 工资例4.15先按照仓库号排序，在按工资排序输出全部职工信息 SELECT * FROM 职工 ORDER BY 仓库号，工资4.2.6 简单的计算查询用于计算函数的函数有：COUNT(计数)、SUM(求和)、AVG(计算平均值)、MAX(最大值)、MIN(最小值)4.16找出供应商所在地的数目SELECT COUNT(DISTINCT 地址) FROM 供应商注：DISINCT 去除重复结果例4.17求支付的工资总数SELECT SUM(工资) FROM 职工4.2.7 分组与计算查询利用 GROUP BY 对查询结果进行分组 同时利用HAVING 进一步限定分组条件例4.21求每个仓库的职工的平均工资SELECT 仓库号，AVG(工资) FROM 职工 GROUP BY 仓库号例4.22求至少有两个职工的每个仓库的平均工资SELECT 仓库号 ，COUNT(*),AVG(工资) FROM 职工；GROUP BY 仓库号 HAVING COUNT(*)>=2注：HAVING 子句总是跟在GROUP BY 之后，而不可以但都使用、4.2.8 利用空值查询例4.23找出尚未确定供应商的订购单SELECT * FROM 订购单 WHERE 供应商号 IS NULL注：查询空值时要使用 IS NULL ，而-NULL是无效的，因为空值不是一个确定的值，所以不能用“=”这样的运算进行比较例4.24列出已经确定了供应商的订购单信息SELECT * FROM 订购单 WHERE 供应商号 WHERE 供应商号 IS NOT NULL4.2.9别名与自连接查询4.2.10 内外互相相关嵌套查询这两部分不是很重要，大家想了解可以看下书，我在这里就不讲了。4.2.11 使用量词和谓词的查询ANY|ALL|SOME 的用法SELECTFROM；WHERE<表达式><比较运算符>ANY|ALL|SOME（SELECTFROMWHERE）EXISTS 的用法SELECTFROM；WHERE NOT EXISTS（SELECTFROMWHERE）例4.27检索那些仓库中还没有职工的仓库信息SELECT * FROM 仓库 WHERE NOT EXISTS;(SELECT * FROM 职工WHERE 仓库号=仓库.仓库号)这个命令等价于SELECT * FROM 仓库 WHERE 仓库号 NOT IN ;(SELECT 仓库号 FROM 职工)例4.28检索那些仓库中至少已经有一个职工的仓库信息SELECT * FROM 仓库 WHERE EXIST ;(SELECT * FROM 职工 WHERE 仓库号=仓库.仓库号)注意：在此处WHERE 后面没有相应的字段，而且内层查询也是以“*”表示例4.29检索有职工的工资大于或等于WH1 仓库中任何一名职工的工资的仓库这里可以用 ANY 或者 SOME SELECT DISTINCT 仓库号 FROM 职工 WHERE 工资>=ANY;(SELECT 工资 FROM 职工 WHERE 仓库号=”WH1”)它等价于：SELECT DISTINCT 仓库号 FROM 职工 WHERE 工资>=；（SELECT MIN(工资) FROM 职工 WHERE 仓库号=”WH1”）例4.30检索有职工的工资大于或等于WH1 仓库中所有职工的工资的仓库号SELECT DISTINCT 仓库号 FROM 职工 工资 >= ALL;(SELECT 工资 FROM 职工 WHERE 仓库号=”WH1”)它等价于：SELECT DISTINCT 仓库号 FROM 职工 工资 >= ;(SELECT MAX(工资) FROM 职工 WHERE 仓库号=”WH1”)4.2.12超链接查询这里只讲普通连接INNER JOIN SELECT 仓库.仓库号，城市，供应商名，地址； FROM 供应商(A) INNER JOIN 订购单(B) INNER JOIN 职工(C) JOIN 仓库(D)ON 职工(C).仓库号=仓库(D).仓库号； ON 订购单(B).职工号=仓库(C).职工号； ON 供应商(A).供应商号=订购单(B).供应商号4.2.13 集合的并运算4.2.14 Visual Foxpro SQL SELECT 的几个特殊运算1.只显示前几项记录例4.35 显示工资最高的3位职工信息SELECT * TOP 3 FROM 职工 ORDER BY 工资 DESC例4.36显示工资最低的那30%的职工信息SELECT * TOP 30PERCENT FROM 职工 ORDER BY 工资 将查询结果存放在各个去向 ARRAY 数组 INTO CUSOR 临时表TABLE 自由表TO FILE 到文本文件PRINTER 到打印机4.3操作功能4.3.1插入数据INSERT 基本格式：格式一：INSERT INTO 表名(字段1，字段2) VALUES (值1，值2)格式二：INSERT INTO 表名 FROM ARRAY 数组名 |FROM MEMVAR例题为三合一套题中第一套题的基本操作题 见后面“S8”,”新冶钢”,“黄石“4.3.2 更新数据SQL 的更新命令格式如下:UPDATE 表名 SET 字段=新的字段值或表达式 WHERE 更新条件REPLACE字段 WITH 新的字段值或表达式 FOR 更新条件4.3.3 删除数据SQL 的更新命令格式如下:DELETE FROM 表名 WHERE 删除条件DELETE FOR删除条件例题如下题目答案如下：1. INSE INTO 零件信息 VALUES("p7","PN7",1020)2. DELE FROM 零件信息 WHERE 单价<6003. UPDATE 零件信息 SET 单价=1090 WHERE 零件号="p4"补充部分：1表结构的复制： USE 表1 COPY STRUCTURE TO 表2 &&将表1的结构复制到表22表记录的复制：USE 表2APPEND FROM 表1 &将表1的记录复制到表2SELECT * FROM 表1 INTO TABLE 表24.4 定义功能4.4.1表的定义定义的基本格式： CREATE TABLE 表名 (字段1 字段类型（字段宽度），字段2 字段类型（字段宽度）;CHECK 表达式 &&【设定有效性规则】 ERROR “字符串” &&【错误提示信息】；DEFAULT 值 &&【设置默认值】；PRIMARY KEY | UNIQUE &&主索引和候选索引；FOREIGN KEY &&普通索引索引类型 +字段名【索引名】+TAG 【索引表达式】4.4.2表的删除删除的基本格式：DROP TABLE 表名注意：如果是自由表则从磁盘上删除对应的表文件； 如果是属于数据库表则，从数据库中移除表。4.4.3 表结构的修改（重点掌握）表结构的修改添加字段：alter table 表名add 字段名 类型（长度）check () default 删除字段：alter table 表名 drop 字段名修改字段：alter table 表名 alter 字段名 新类型（长度）重命名字段：alter table 表名 rename 旧字段名 to 新字段名建立有效性规则：alter table 表名alter 字段 set check 字段条件删除有效性规则：alter table 表名alter 字段 drop check建立默认值：alter table 表名 alter 字段 set default 条件 error/ default/ check删除默认值：alter table 表名 alter 字段 drop default建立唯一索引：alter table 表名add unique 字段 tag索引名删除唯一索引：alter table 表名drop unique字段tag 索引名建立主索引：alter table 表名add primary key字段 tag索引名删除主索引：alter table 表名drop primary key字段 tag索引名建立普通索引：alter table 表名add foreign key字段 tag索引名删除普通索引：alter table 表名drop foreign key字段tag索引名
