命名规则——表名,字段名限制在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]
这是一个交叉表查询。实际上,交叉表查询常用于合计与算术运算,统计数据。
vb数据库编程(三)--SQL语言
最新推荐文章于 2024-07-04 14:13:43 发布