vb数据库编程（三）--SQL语言

命名规则——表名，字段名限制在30个字符内，而且只能用数字，字母和下划线，不要用中文！！且必须以字母开头。除记录内容可能用到的中文外，全部用英文：数据库名，表名，字段名，索引名，查询名等。另外，SQL语句关健字用的都是大写字母，如果出现小写字母的表名字段名等最好用中括号括起（不管是否包含空格）。？？？有问题？一点习惯.
不能在引号内用行连接符，如SQLStr=”SELEC _
T * FROM [invoice]”但是可以：SQLStr=”SELEC” & _
“T * FROM [invoice]”
SQL基本作用是查询，查询分为两类：选择查询——按一定查询条件从数据库中返回一个记录集；动作查询——对数据库进行建立，修改，删除等
-------------------------------------------
一，查询语言Select。（1）返回一个表中几个字段：
select 字段1，字段2… from 表名
select 姓名，行业，垫付 from BB
(2)返回多个表中的多个字段。几个表共同返回一个记录集，要用一个字段作标准，对不同表各选定字段按顺序进行组合，才能融合成1个记录集。如下：
select BB.姓名，AA.发票 from BB,AA where BB.ID=AA.ID
用Where子句将不同表记录以ID字段为标准进行组合。
-------------------------
Where子句：指定查询条件，用来筛选表中满足条件的记录。这样理解：用select可对不同字段进行筛选（纵向），而又用where子句，可对不同记录进行筛选（横向）。 如：select 姓名，行业，定额，备注 from BB Where 定额>30
返回定额在30元以上的记录，条件表达式中的运算符可以是<,>,=,<=,>=,<>,like，between,in.其中，Like是用于查找与指定字符串相匹配的字符串，可用通配符%与_，一个_只代表1个字符，一个%可代表多个字符（在DAO中通配符是*和?），注意：只在Like子句中允许用通配符。如：
select 姓名，行业 from BB where 姓名 like ‘李%’
注：where子句可同时接多个表达式，用And,Or,Not等连接，where工作的方式是：对每一条记录，将指定字段值送入表达式比较，如“定额>30”，某记录的“定额”字段值为25，则返回false，就不会进入查询结果集里。象这样一条一条检查表中的每条记录。
between运算符指定一个范围，返回在两个端点之间的记录，如下：
select 姓名，行业，定额 from BB where 定额 between 30 and 40
等价于select 姓名，行业，定额 from BB where 定额>=30 and 定额<=40
in运算符是指定几个值，如：
select 姓名，行业，定额 from BB where 行业 in('南杂','百货')
等价于select 姓名，行业，定额 from BB where 行业='南杂' or 行业='百货'
注：1，引用字符串的值时，用单引号,也可以在引用中用两个双引号表示一个引号,比如[name] LIKE “李春生”写到引用中是：SQLStr=”[name] LIKE “”李春生”””
2，字段名或表名中如果含有空格，该字段名或表名要用中括号[]括起，如：
select [all name] from students.
3,如果字段名或表名过长，可用As关健字改名，表名.字段名 as 新名。如：
select a.姓名，b.主要情况 as 简介 from 个体户管理 as a,详细资料 as b Where
a . ID=b . ID
此句改动了三处：表“个体户管理”改为a,表“详细资料”改为b，字段“主要情况”改为“简介”。
-----------------------------------------
ORDER BY子句：将查询结果集按某字段排序。选择升序或降序：在字段名后用DESC是降序，缺省情况是升序。如：
select * from BB order by 姓名 DESC
表示按姓名字段降序。
GROUP BY子句和HAVING子句：对源表记录按一个指定字段值为标准进行分组，凡是值相同的就合成单一‘记录(组)’返回。这是一个特殊结果集，因为源表中可能有多条记录在此字段上值相同，但在其他字段上的值不同，注意：其他字段不能直接从结果集中显示！除作为分组标准的字段可直接从结果集显示出来，其余只能与统计函数在一起才能显示，因此主要是进行计算，如：得到每种行业的人的个数：
select COUNT(BB.姓名)as num,行业 from BB group by 行业
返回“行业=南杂；num=6”、“行业=理发；num=2”…的结果。
再如合计每种行业收取的管理费多少，如下：
select SUM(BB.累计应交)as num,行业 from BB group by 行业
而不能写成SELECT 累计应交,行业from BB group by 行业，“累计应交”字段不能直接输出到用户。
可同时用多
个字段作分组标准，表示只有在多个字段值都相同时才把它们合成一条’记录组’，如SELECT [Name],[money] FROM [invoice] GROUP BY [Name],[money]。表示只有[Name]和[Money]都相同的记录才合并到同一组。
HAVING子句：返回组内指定条件的记录（where是指定表内符合条件的记录，having是指定用group by分组的结果集内的满足条件记录）。如：
select SUM(BB.累计应交)as num,行业 from BB group by 行业 having SUM(BB.累计应交)>200 返回单个行业合计管理费超过200元的“组记录”。
再如：SELECT [Name],[money] FROM [invoice] GROUP BY [Name],[money] HAVING COUNT(*)>1，返回用Name和money分组后每条记录组包含的原表记录数大于1个的记录（组）。好象不好理解，这里的*号实际上相当于其他字段，只能与统计函数一起使用，它返回的是每条记录（组）对应的源表记录数。慢慢理解吧。
---------------------------------------------
with owneraccess option 子句：对执行查询的用户查看查询结果的权限。一般用在多用户数据库中。
-------------------------------------
字段前的“谓词”。用在一个字段前面作限制,达到某些功能，一，distinct：忽略具有重复数据的记录。如 select DISTINCT name from photos.二，TOP：获取指定数目的记录。如获取表中后10条记录：select TOP 10 * from BB;再如，获取表中的前40%记录，用PERCENT联合：select TOP 40 PERCENT * From BB.
完整的select语句语法是：
select （多个）字段 from （多个）表 in 数据库
where 筛选记录的条件表达式
group by 字段 having 条件
order by 排序字段
with owneraccess option
变量当作查询值，要用PARAMETERS关健字定义，格式：
PARAMETERS 变量1 类型，变量2 类型，…
如下：
PARAMETERS lxn integer,ldd integer
lxn=30
ldd=50
select * from BB where 定额 between lxn and ldd
★ 将查询结果集生成一个独立表：select的into子句，格式：
select 多个字段 into新建表名 from 表名 where 条件 …
select 姓名，垫付 into outmoney from BB where 垫付>0
如果不指定条件，这条语句可以将整个表复制，达到备份的效果。
-----------------------------------------------------------
使用统计函数：对数据源中特定的字段进行一些统计。使用这些函数，通常返回包含单一记录的结果集（用GROUP BY分组的除外）。每个函数作为结果集的一个字段（用AS关健字定义字段名），可同时使用多个函数。
COUNT（）：返回指定字段中非NULL值的记录个数，而COUNT（*）返回全部记录个数，不进行空值检查。
SUM（）：返回指定数值型字段的总和。
AVG（）：返回指定数值型字段的平均值。
MIN（）：返回指定数值型字段的最小值。
MAX（）：返回指定数值型字段的最大值。
其参数是一个字段名。格式：
函数(字段名) AS 输出到结果的字段名
括号中的字段是你想在表中检查的字段，而输出结果的字段是你想保存输出结果的字段，一般它只含单一记录，它的名字用AS关健字自定，但不能与其他字段名重复。
如：select SUM(垫付) as lxn from Bb where 垫付>20
lxn字段的值就是表BB中垫付字段数值大于20元的和，如383（元）。只有这一个记录。再如 select count(*) as x ,AVG(定额) as y from BB ,x字段返回表BB的记录数。y字段返回定额字段的平均值，它构成了结果集中唯一记录的两个字段值。
-----------------------------------------------------------
使用表间连接来返回多个表中的记录：当从多个表中检索记录时，SQL语句的工作方式是：每个表中的一条记录都与其他表的所有记录搭配生成新记录，组成结果集。如：SELECT * FROM [invoice],[UnitRecord]，这样的话，返回的结果集相当庞大，是多个表记录数的乘积，比如表A有3条记录，表B有4条记录，表C有2条记录，那么SELECT * FROM A，B，C的结果就有3×4×2=24条记录，且每条记录都包含原三个表的所有字段，因此，我们通常用WHERE来限定一些条件，以只返回多表中合适的记录搭配，有一种比WHERE更好的办法，它可以满足我们更多需要，即采用表链接返回记录集。用JOIN关健字，语法：
SELECT 多个字段 FROM 表1 链接方式 JOIN 表2 ON 表1.字段1=表2.字段2
SELECT a1.name,a2.email FROM a1 INNER JOIN a2 ON a1.ID=a2.ID
在FROM子句中指明JOIN连接方式并用ON来指定条件。
链接方式有INNER和LEFT、RIGHT三种，：INNER——内链接。当两个字段的值相等时，就将两个表中的记录组合成一个新记录（其实用where子句也一样，如：
SELECT [UnitRecord].[Name],[UnitRecord].[Address], [invoice].[Money] FROM
[UnitRecord] INNER JOIN [invoice] ON [invoice].[Name]=[UnitRecord].[Name]其结果等同
SELECT [UnitRecord].[Name],[UnitRecord].[Address], [invoice].[Money] FROM
[UnitRecord],[invoice] WHERE [invoice].[Name]=[UnitRecord].[Name]，不过，如果使用的是LEFT或RIGHT关健字，采用左链接或右链接，结果就不同了，这也正是比WHERE更好的地方）。
LEFT：左链接。格式：SELECT 表1.字段，表2.字段…FROM 表1 LEFT JOIN 表2 ON 表1.某字段=表2.某字段。返回左侧表中的所有记录，返回右侧表与条件匹配的记录。它的结果是：INNER JOIN的结果集+左侧表中未满足ON条件而未筛选进入INNER JOIN的其余全部记录。
RIGHT：右链接，返回右侧表中全部记录，返回左侧表中与条件匹配的记录。其结果是INNER JOIN的结果集+右侧表中的其他全部记录。LEFT JOIN和RIGHT JOIN这两个关健字只要记一个就行了，因为把两个表的左右顺序换一下，就可以用同一个关健字达到两种效果。如：
SELECT [UnitRecord].[Name],[UnitRecord].[Address],[invoice].[Money] FROM [UnitRecor
d] LEFT JOIN [invoice] ON [invoice].[Name]=[UnitRecord].[Name] 等同于
SELECT [UnitRecord].[Name],[UnitRecord].[Address],[invoice].[Money] FROM [invoice] RIGHT JOIN [UnitRecord] ON [invoice].[Name]=[UnitRecord].[Name]
技巧：采用左链接或右链接时，其中一个表的所有记录都传到了结果集，它的一些记录值可能在另一个表中并没有关联记录（即匹配ON条件的记录），这时由于多表记录总是自动进行搭配，每条结果记录都包含原多表所有字段，因此出现某些字段值为空NULL的情况，利用此特性倒是很容易找出哪些记录在另一个表中没有匹配的记录，有时具有实际意义，如查找哪些个体户连一张发票都没交，加个WHERE检测结果集对应的原表字段是否为NULL就行了：
SELECT [UnitRecord].[Name],[UnitRecord].[Address],[invoice].[Money] FROM [invoice] RIGHT JOIN [UnitRecord] ON [invoice].[Name]=[UnitRecord].[Name] WHERE [invoice]
.[Money] IS NULL
------------------------------------------------------------------
使用IN关健字在WHERE子句中实现子查询：在一个SELECT语句里包含另一个SELECT查询语句，称为子查询，这个子查询并不是任意的，有限制，它只能返回一个字段，将这个返回字段与IN关健字前的字段进行逐一比较是否相等，作为满足WHERE条件。如：SELECT SUM([Money]) AS JIFU FROM [invoice] WHERE [Name] IN (SELECT [Name] FROM [UnitRecord] WHERE [UnitRecord].[Address] LIKE "%吉富%")，返回所有吉富个体户的已交管理费总和。先用子查询找出在吉富的个体户姓名，然后从发票表中找出这些人的发票进行累计。可看到，子查询只包含[Name]一个字段，且整个子查询语句包含在括号中。一目了然。
可以在IN前面加上关健字NOT，表示与指定字段不相等的情况时，视为满足WHERE条件，恰好反过来。如上面加上NOT后表示所有不在吉富的个体户管理费总和：SELECT SUM([Money]) AS JIFU FROM [invoice] WHERE [Name] NOT IN (SELECT [Name] FROM [UnitRecord] WHERE [UnitRecord].[Address] LIKE "%吉富%")。
------------------------------------------
在SQL语句中直接使用VB函数：严格地说，只在JET引擎的Access SQL中可以使用VB中的函数，如变为小写Lcase,变为大写Ucase，以及格式化（最常用）Format函数等。使用时也很简单，只要在返回字段时用“函数（字段名） AS 别名”就可以了，如：SELECT [name],Format([Money],”￥0.00”) AS VBFUN FROM [invoice]，注意要用AS关健字来重新定义返回字段名，否则ACCESS SQL将用缺省的名称Expr1001,Expr1002来命名用了函数的字段，而不会用原来的字段名。此外，如果函数有参数，不能使用VB内置的常量名形式，而直接使用数字，如：SELECT StrConv([Money],4) FROM [invoice]，而不能写SELECT StrConv([Money],vbWide) FROM [invoice]，这样不能识别。JET引擎毕竟是独立于VB之外的。
------------------------------------------------
★向表中添加记录：INSERT语句，格式：
INSERT INTO 表名 VALUES（完整的记录值）
INSERT INTO BB VALUES（"李新宁","复印",30,"六月",180,0,""）
也可以从其他表中复制记录。格式：
INSERT INTO 表名（字段1，字段2，……） SELECT 源字段 FROM 源表。如：
INSERT INTO [Test]([Name],[Money]) SELECT [name],[Money] FROM [Invoice] WHERE [money]>40，其实这种情形有点类似嵌套的SELECT。就象前面讲到的用IN创建SELECT子查询一样。都把一条完整的SELECT作为子句。
★删除满足条件记录：DELETE语句，可同时删除多条记录，格式：
DELETE FROM 表名 WHERE 条件表达式
delete from outplay where 垫付<40
将表outplay中“垫付”小于40的记录都删除。
★修改记录：UPDATE语句，可同时修改多条满足条件记录，格式：
UPDATE 表名 SET 字段=新值 WHERE 条件
UPDATE BB SET 垫付=0 WHERE BB.姓名="李春生"
可以同时更改多个字段，字段间用逗号隔开。对于数字型字段还可以使用算术符号，如：UPDATE [invoice] SET [Name]=”李某”,[Money]=[Money]*1.04 WHERE [Name] LIKE “李%” （闲话：一个%可以代替多个字符哦，别忘了，不要用星号*）
------------------------------------------
★ 在代码中创建表：SQL的CREATE TABLE语句，格式：
CREATE TABLE 表名（字段1名 类型(大小)，字段2 类型(大小)，……）
例如：
cn.Execute ("CREATE TABLE 通迅录(姓名 TEXT(14),地址 TEXT(40)，电话 long)")
（说明：cn是ADO对象中的connection对象变量）
注意：表名和字段名中只能包含字母数字和下划线，因此“通讯录”“姓名”等中文可能不能识别，会出错，如遇此情况，请将全部字段名改用英文！！
其中，类型指明字段的数据类型，如LONG(数字),TEXT(文本)等。大小——是指当数据类型为文本TEXT类型时，每条记录的字符串长度，要将它用括号括起，只当类型为文本时需要指定，其他类型时则不写。是个可选参数。注意：在SQL中，数居类型的写法与VB不完全一样，不同的有逻辑型Boolean写为BIT，日期型Date写为DATETIME，短整型Integer写为SHORT，此外，SQL还另有“备注”型写为LONGTEXT，“长二进制”型（如图片）写为LONGBINARY，“自动编号”型写为COUNTER（即自动增加字段！！ID），其他的与VB写法一致。如LONG，BYTE，CURRENCY等。
★ 删除表：很简单：“DROP TABLE 表名” 即可。如：
DROP TABLE 通迅录
就删除了“通迅录”这个表。
------------------
★ 添加索引：CONSTRAINT子句，它插入在create table创建表语句中，在需要建立索引的字段后面，为create table 表名（字段1 类型(大小) CONSTRAINT 索引名 索引类型，字段2 类型(大小)，……）
例如：CREATE TABLE 通迅录(姓名 TEXT(14) CONSTRAINT index1 UNIQUE，地址 TEXT (40)，电话 long)
上例的意思是指定“姓名”字段建立唯一索引。索引名称为index1,“索引类型”有以下三种：UNIQUE——指定该字段是唯一索引；PRIMARY KEY——指定该字段是主码(主关健字)；FOREIGN KEY——指定该字段是外码（外来关健字）。
也可以用专门的建立索引语句来建立索引：CREATE 索引类型 INDEX 语句，格式：
CREATE 类型 INDEX 索引名 ON 表名（字段名）
CREATE UNIQUE INDEX index1 ON 通迅录(姓名)
★ 添加/删除字段：ALTER TABLE语句。一次只能添加或删除一个字段，格式：
ALTER TABLE 表名 ADD [COLUMN] 字段 类型 ["大小"] [“索引”]
ALTER TABLE 表名 DROP [COLUMN] 字段名
其中，ADD后是要添加的字段列表，DROP后是要删除的字段。可选参数COLUMN指定字段在表中的排列顺序位置?——好象没什么意义。如从表“通迅录”中删除“电话”字段：
ALTER TABLE 通迅录 DROP 电话
添加字段如：ALTER TABLE [test] ADD [NewField] TEXT(5)，比较简单。
添加字段和删除字段时要一个一个地添加/删除，不能同时几个字段，如：alter table UnitRecord add Phone long和alter table UnitRecord add Other TEXT（40）不要写成alter table UnitRecord add Phone long,Other TEXT（40），删除时也一样一个一个来。
在实际中，我们常会遇到要对表中的字段进行更改，而直接更改是不能的，只能先删除需要更改的字段，再添加更改了的字段。
★ 删除索引：在ALTER TABLE中用CONSTRAINT子句，格式：
ALTER TABLE 表名 DROP CONSTRAINT 索引名 类型（字段名）
例如要删除通迅录表中建立在“姓名”字段上的索引：
ALTER TABLE 通迅录 DROP CONSTRAINT index1 UNIQUE(姓名)
★ 创建交叉表查询：我们查询得到的结果集中，一般还是以源数据表的某些字段来作为列标头，源数据的记录ID来作为行标头，最多只是顺序变化或筛选取舍而已，交叉表查询与此不同，在它的查询结果中，列标头和行标头信息都是查询语句从数据源记录中得到的任意数据，因而可显示特定目的的复杂查询结果。
创建交叉表查询使用关健字TRANSFORM和PIVOT，格式如下：
TRANSFORM 每个交叉点信息 SELECT 行标头信息 PIVOT 列标头信息
其中交叉点就是每个行标头和每个列标头的对应点，即表格内容所在。行标头和列标头各指定一个字段，其中，行标头可用一个完整的SELECT来限定返回值，而PIVOT直接指定一个字段，它将罗列该字段所有不同值产生多列。如：
TRANSFORM SUM([invoice].[Money]) AS [allmoney] SELECT [invoice].[Name] FROM [unitrecord] INNER JOIN [invoice] ON [invoice].[Name]=[unitrecord].[Name] WHERE [invoice].[money]>30 GROUP BY [invoice].[name] PIVOT [Invoice].[dateend]
这是一个交叉表查询。实际上，交叉表查询常用于合计与算术运算，统计数据。