第4章关系数据库标准语言SQL

第四章关系数据库标准语言SQLSQL是structurequerylanguage（结构化查询语言）的缩写4.1SQL概述主要特点SQL是一种一体化的语言；SQL语言是一种高度非过程化的语言；SQL语言非常简洁；SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。SQL功能作用命令动词数据查询查询SELECT数据操纵插入INSERT更新UPDATE删除DELETE数据定义新建CREATE删除DROP修改ALTER4.2查询功能常用的SQL命令1．SELECT短语：用于说明要查询的数据。2．FROM短语：用于说明要查询的数据来自哪个或哪些表，可对单个表或多个表进行查询。3．WHERE短语：用于说明查询条件；如果是多表查询还可通过该子句指明表与表之间的联接条件，进行联接。4．GROUPBY短语：用于对查询进行分组，可利用它进行分组汇总。5．HAVING短语：跟随在GROUPBY之后使用，它是用来限定分组，要求只有满足分组条件的记录才能进行分组查询。6．ORDERBY短语：用于对查询的结果进行排序。7．AS短语：用于指定查询结果中字段的新名称；AS前的表达式可以是一个字段名、表达式、函数等。4．DISTINCT短语：用于说明该短语将在查询结果中去掉重复值。9．INTOTABLE短语：用于说明查询结果保存何处。等函数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元得职工SELECTDISTINCT仓库号FROM职工WHERE工资1210例4.5给出在仓库”WH1”或”WH2”工作并且工资少于1250元的职工号SELECT职工号FROM职工；WHERE工资1250AND(仓库号=”WH1”OR仓库号=”WH2”)4.2.2简单的连接查询基于简单的查询，增加一个连接条件进行查询例4.6找出工资多于1230元得职工号和他们所在的城市SELECT职工号，城市FROM职工，仓库；WHERE工资1230AND职工.仓库号=仓库.仓库号例4.7找出工作面积大于400的仓库的职工号以及这些职工工作的城市SELECT职工号，城市FROM仓库，职工；WHERE面积400AND职工.仓库号=仓库.仓库号4.2.3嵌套查询例4.8那些城市至少有一个仓库的职工工资为1250元SELECT城市FROM仓库WHERE仓库号IN;(SELECT仓库号FROM职工WHERE工资=1250)例4.9查询所有职工的工资都多于1210元的仓库信息SELECT*FROM仓库WHERE仓库号NOTIN;(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工资BETWEEN1220AND1240例4.12从供应商关系中检索出全部公司的信息（不要工厂或其他gis的信息）SELECT*FROM供应商WHERE供应商名LIKE“%公司”这里LIKE是字符串匹配运算符，通配符“%”表示0个或者多个字符，另外还有一个通配符“_”表示一个字符例4.13找出不在北京的全部供应商信息SELECT*FROM供应商WHERE地址！=”北京”在SQL中“不等于”用“！=”表示4.2.5排序将查询结果排序升序（ASC）或者降序(DESC)可以按照一个或者多个字段排序例4.14按照职工的工资升序检索出全部职工信息SELECT*FROM职工ORDERBY工资例4.15先按照仓库号排序，在按工资排序输出全部职工信息SELECT*FROM职工ORDERBY仓库号，工资4.2.6简单的计算查询用于计算函数的函数有：COUNT(计数)、SUM(求和)、AVG(计算平均值)、MAX(最大值)、MIN(最小值)4.16找出供应商所在地的数目SELECTCOUNT(DISTINCT地址)FROM供应商注：DISINCT去除重复结果例4.17求支付的工资总数SELECTSUM(工资)FROM职工4.2.7分组与计算查询利用GROUPBY对查询结果进行分组同时利用HAVING进一步限定分组条件例4.21求每个仓库的职工的平均工资SELECT仓库号，AVG(工资)FROM职工GROUPBY仓库号例4.22求至少有两个职工的每个仓库的平均工资SELECT仓库号，COUNT(*),AVG(工资)FROM职工；GROUPBY仓库号HAVINGCOUNT(*)=2注：HAVING子句总是跟在GROUPBY之后，而不可以但都使用、4.2.8利用空值查询例4.23找出尚未确定供应商的订购单SELECT*FROM订购单WHERE供应商号ISNULL注：查询空值时要使用ISNULL，而-NULL是无效的，因为空值不是一个确定的值，所以不能用“=”这样的运算进行比较例4.24列出已经确定了供应商的订购单信息SELECT*FROM订购单WHERE供应商号WHERE供应商号ISNOTNULL4.2.9别名与自连接查询4.2.10内外互相相关嵌套查询这两部分不是很重要，大家想了解可以看下书，我在这里就不讲了。4.2.11使用量词和谓词的查询ANY|ALL|SOME的用法SELECT…FROM…；WHERE表达式比较运算符[ANY|ALL|SOME]（SELECT…FROM…[WHERE…]）EXISTS的用法SELECT…FROM…；WHERE[NOT]EXISTS（SELECT…FROM…[WHERE…]）例4.27检索那些仓库中还没有职工的仓库信息SELECT*FROM仓库WHERENOTEXISTS;(SELECT*FROM职工WHERE仓库号=仓库.仓库号)这个命令等价于SELECT*FROM仓库WHERE仓库号NOTIN;(SELECT仓库号FROM职工)例4.28检索那些仓库中至少已经有一个职工的仓库信息SELECT*FROM仓库WHEREEXIST;(SELECT*FROM职工WHERE仓库号=仓库.仓库号)注意：在此处WHERE后面没有相应的字段，而且内层查询也是以“*”表示例4.29检索有职工的工资大于或等于WH1仓库中任何一名职工的工资的仓库这里可以用ANY或者SOMESELECTDISTINCT仓库号FROM职工WHERE工资=ANY;(SELECT工资FROM职工WHERE仓库号=”WH1”)它等价于：SELECTDISTINCT仓库号FROM职工WHERE工资=；（SELECTMIN(工资)FROM职工WHERE仓库号=”WH1”）例4.30检索有职工的工资大于或等于WH1仓库中所有职工的工资的仓库号SELECTDISTINCT仓库号FROM职工工资=ALL;(SELECT工资FROM职工WHERE仓库号=”WH1”)它等价于：SELECTDISTINCT仓库号FROM职工工资=;(SELECTMAX(工资)FROM职工WHERE仓库号=”WH1”)4.2.12超链接查询这里只讲普通连接INNERJOINSELECT仓库.仓库号，城市，供应商名，地址；FROM供应商(A)INNERJOIN订购单(B)INNERJOIN职工(C)JOIN仓库(D)ON职工(C).仓库号=仓库(D).仓库号；ON订购单(B).职工号=仓库(C).职工号；ON供应商(A).供应商号=订购单(B).供应商号4.2.13集合的并运算4.2.14VisualFoxproSQLSELECT的几个特殊运算1.只显示前几项记录例4.35显示工资最高的3位职工信息SELECT*TOP3FROM职工ORDERBY工资DESC例4.36显示工资最低的那30%的职工信息SELECT*TOP30PERCENTFROM职工ORDERBY工资将查询结果存放在各个去向ARRAY数组INTOCUSOR临时表TABLE自由表TOFILE到文本文件PRINTER到打印机4.3操作功能4.3.1插入数据INSERT基本格式：格式一：INSERTINTO表名(字段1，字段2)VALUES(值1，值2)格式二：INSERTINTO表名FROMARRAY数组名|FROMMEMVAR例题为三合一套题中第一套题的基本操作题见后面“S8”,”新冶钢”,“黄石“4.3.2更新数据SQL的更新命令格式如下:UPDATE表名SET字段=新的字段值或表达式WHERE更新条件REPLACE字段WITH新的字段值或表达式FOR更新条件4.3.3删除数据SQL的更新命令格式如下:DELETEFROM表名WHERE删除条件DELETEFOR删除条件例题如下题目答案如下：1.INSEINTO零件信息VALUES(p7,PN7,1020)2.DELEFROM零件信息WHERE单价6003.UPDATE零件信息SET单价=1090WHERE零件号=p4补充部分：1．表结构的复制：USE表1COPYSTRUCTURETO表2&&将表1的结构复制到表22．表记录的复制：USE表2APPENDFROM表1&将表1的记录复制到表2SELECT*FROM表1INTOTABLE表24.4定义功能4.4.1表的定义定义的基本格式：CREATETABLE表名(字段1字段类型（字段宽度），字段2字段类型（字段宽度）;[CHECK表达式&&【设定有效性规则】]ERROR“字符串”&&【错误提示信息】；DEFAULT值&&【设置默认值】；PRIMARYKEY|UNIQUE&&主索引和候选索引；FOREIGNKEY&&普通索引索引类型+字段名【索引名】+TAG【索引表达式】4.4.2表的删除删除的基本格式：DROPTABLE表名注意：如果是自由表则从磁盘上删除对应的表文件；如果是属于数据库表则，从数据库中移除表。4.4.3表结构的修改（重点掌握）表结构的修改⑴．添加字段：altertable表名add字段名类型（长度）check()default⑵．删除字段：altertable表名drop字段名⑶．修改字段：altertable表名alter字段名新类型（长度）⑷．重命名字段：altertable表名rename旧字段名to新字段名⑸．建立有效性规则：altertable表名alter字段setcheck字段条件⑹．删除有效性规则：altertable表名alter字段dropcheck⑺．建立默认值：altertable表名alter字段setdefault条件error/default/check⑻．删除默认值：altertable表名alter字段dropdefault⑼．建立唯一索引：altertable表名addunique字段tag索引名⑽．删除唯一索引：altertable表名dropunique字段tag索引名⑾．建立主索引：altertable表名addprimarykey字段tag索引名⑿．删除主索引：altertable表名dropprimarykey字段tag索引名⒀．建立普通索引：altertable表名addforeignkey字段tag索引名⒁．删除普通索引：altertable表名dropforeignkey字段tag索引名