SQL语句

SQL语言是数据库编程中最常使用的，功能非常强大，在delphi中只有通过Query组件使用，所以心铃用一讲的篇幅着重讲一下。在网上这方面的内容很多，还有专门的书籍讲解SQL,如〈SQL21天自学通〉，建议大家阅读一下。本讲中主要讲解编程中最常用的一些语句，由于初学者对语法不熟悉，所以这里以例句的形式给出，这些例句都是精选的最常用的例句，经过心铃亲自验证过的，下一讲将在编程中具体运用。 

关于SQL语言，心铃从三个方面介绍，这也是SQL的精华所在。大家这么理解这三个方面吧：两种SQL语言类型和函数等基础内容。这两种SQL语句分别是：数据定义语言 ＤＤＬ（Data Definition Language) ，主要用来创建、修改数据表、建立索引、删除数据表等功能；数据操纵语言ＤＭＬ （Data Manipulation Language)，主要用来对数据进行添加、查询、更改、删除等。函数则是对数据字段使用，便于进行各种需要对数据进行处理后使用或得到结果，下面分别介绍。

一、函数

    在用SQL语句操作字段时，经常需要对字段值进行各种运算，SQL中提供了多种函数可供使用，下面列出几种类型的函数，供大家参考，这些以及下面的两种类型的SQL语句在delphi的帮助中都是无法找到的，但对数据库编程非常有用，希望大家能仔细阅读。注意：讲座中采用的ACCESS97数据库打开时调用的数据库引擎并不能支持下面所有函数，心铃没有对这些函数逐个验证，如果你在使用时提示没有定义则表示不支持此函数，微软的SQL SERVER基本上都支持。所以使用时必须注意。

1 数值函数。主要是数值型字段操作。主要介绍如下：

  ABS(n)　返回n的绝对值；MOD(a,b)  返回a/b的余数；POWER(a,b)  返回a的b次方；ROUND(a,b)　返回a的四舍五入值，保留小数位数为b；SIGN(n) 　如果n<0，返回-1，如果n=0，返回0，如果n>0，返回1；SQRT(n)　返回n的平方根。返回值是个实数型；TRUNC(n,m)　把n的小数部分截断成m位小数。M被省略时或为0则截去所有小数；M为负数时，则把小数点前的整数部分置为零。

例句：select abs(aa)  from tmp ,从数据表tmp中把所有字段aa的绝对值查询出来

2 字符函数。主要对字符型字段操作。

    CHR(n)  返回代号为n的ASCII字符；CONCAT(char1,char2)　把字串连接起来，相当于连字符”||”。；INITCAP(char)　把字串的首字母转换为大写；LOWER(char)　把字串全部转换成小写；UPPER(char)　把字串全部转换成大写；LTRIM(char)  去掉字串左边的空格；RTRIM(char)  去掉字串右边的空格；REPLACE(char1,char2,char3)　找出char1中的char2，用char3替换；SUBSTR(char,m,n)  返回字串char中从第m个字开始的n 个字，如果数据库支持汉字，则按一个个汉字截取。如果数据库只支持字母，则按一个个字母截取。如果m是负数，则从字串尾部往前数位置；SUBSTRB(char,m,n)  与SUBSTR(char,m,n) 的唯一不同时，它只能按字节截取；ASCII(char)  返回ASCII代码；INSTR(char1,char2,n,m)　从char1的第n个字符开始，找出char2出现第m次的位置，找到则返回该位置的数值，找不到则返回0，如果n为负数，则表示从字串尾部往前找；INSTRB(char1,char2,n,m) 与INSTR(char1,char2,n,m)唯一的不同是，它按字节来数位置；LENGTH(char)　返回字串的长度。如果是汉字则返回汉字的个数，如果是字母与汉字混合，则返回汉字个数与字母个数之和；LENGTHB(char) 返回字串长度，按字节计算。 

3 日期函数。

    ADD＿MONTHS(d,n)  返回d这一日期加上n个月所得的日期，它可以推算几个月以前的日期，只要n为负数即可。LAST_DAY(d)  返回d这一日期所在月的最后一天，ROUND(d,fmt)  返回离d这一日期最近的年（如果fmt是’YEAR’）的第一天，或月（如果fmt是’MONTH’）的第一天；SYSDATE()系统日期；MONTHS_BETWEEN（d1,d2）返回在给定的两个日期中有多少个月。

4 转换函数

    TO_CHAR(d,fmt)日期转换为字符，也可省略fmt；TO_CHAR(n,fmt)数字转换为字符，也可省略fmt；

5 分组函数

     在分组函数中，除了COUNT(*)之外，其他函数都忽略空值。AVG(n) 返回某一列的平均值平均数；COUNT(expr)  计数；MAX(expr)  最大值；MIN(expr)  最小值；SUM(n)　 返回某一列的所有数值的和。

二、数据操纵（ＤＭＬ）语句

    数据操纵（ＤＭＬ）语句包括查询和修改删除等，下面先介绍查询语句。查询语句最典型的是格式是：Select 列表  from 数据表  where 条件。列表一般是由一组字段名、星号、表达式、变量等构成。数据表指定数据的来源，条件则是多种多样的。

1 SELECT gzbh,ffzq FROM gzlb WHERE lbmc='胶靴'

解释：将所有发放“胶靴”的工种编号、发放周期查询出来，如果将其改为星号（*），则查询的数据为符合条件的所有字段内容。这里查询结果集合中数据的排列顺序与字段排列顺序相同。

2 select gzbh  as 工种名称 from gzlb

解释：在查询的同时修改字段名称，这样做主要是在用DBGRID等显示查询到的数据时可以让表头直接显示为中文名称方便阅读。

3 Select DISTINCT * from gzlb where lbmc='胶靴'

解释：使用DISTINCT选项时，对于所有重复的数据在SELECT返回的结果集合中只保留一行。也就是：如果有重复的记录，只显示一个。

4 select top 10 gzbh from gzlb

5 select top 10 PERCENT gzbh from gzlb

解释：第4句：只返回查询到的前10条记录；第5句：返回查询到的前10%的记录。

6 Select gzlb.gzbh,gzmc,lbmc from gz,gzlb where gz.gzbh=gzlb.gzbh

解释：从工种库和工种劳保库中列出工种编号、工种名称、劳保名称，这里从两个表中读数据，最多可256个数据表，由于两个数据表中有相同的字段(gzbh)，所以需要在其前面加上数据表的名称来限定从哪个数据表中读数据。

7 Select g.gzbh,gzmc from gzlb g ,gz where gz.gzbh=g.gzbh

解释：在FROM子句中可用: 表名 别名 这种方式为数据表gzlb指定别名g,主要用于数据表名复杂时简化程序书写。

8 Select * from gzlb where ffzq>24

解释：将发放周期大于24个月的所有记录列出。这里where后是查询条件，常用的条件如下：

比较运算符(大小比较)：>、>=、=、<、<=、<>

范围运算符：BETWEEN…AND…，NOT BETWEEN…AND…，

如：Select lbmc from gzlb where ffzq between 24 and 60，检索出发放周期在>＝24到<=60个月之间的劳保用品。

列表运算符：IN (项1,项2……)，NOT IN (项1,项2……)

如：Select * from gzlb where ffzq in (24,60)，将发放周期为24和60的记录查询出来。

模式匹配符(判断值是否与指定的字符通配格式相符):LIKE、NOT LIKE, 常用于模糊查找，它判断列值是否与指定的字符串格式相匹配。对于Access数据库来说，用的是微软的JET数据库引擎，通配字符是这样的：＊代表任意多个字符，？代表单个字符。而在其他数据库系统中可能是这样的：百分号%可匹配任意类型和长度的字符，如果是中文，请使用两个百分号即%%，下划线_：匹配单个任意字符，它常用来限制表达式的字符长度。心铃验证发现：在本讲座采用的环境下即ACCESS97中文版，中文WIN98下，如果字段内容为中文，用一个？可代表一个汉字，如果字段内容为西文字符，一个？代表一个西文字符。

如：Select * from gzlb where gzbh  like '?0'，将所有工种编号中第二个字符为0的记录列出，

Select * from gzlb where lbmc  like '?皂'，将所有劳保名称中第二个汉字为“皂”的劳保用品如肥皂、香皂列出，如果用’??皂’就得不到正确结果。

其他条件还有：

空值判断符(判断表达式是否为空)：IS NULL、NOT IS NULL，逻辑运算符(用于多条件的逻辑连接)：NOT、AND、OR，逻辑运算符的优先级为NOT、AND、OR。

9 Select * from gzlb order by gzbh Desc

解释：按工种编号的降序排列查询到的数据，Desc表示降序，ASC表示升序，为默认值，可省略。

10 Select gzbh from gzlb union  select gzbh from gz

解释：将两个数据表中的工种编号查询出来放在一起，去掉重复记录

11 Select gzbh from gzlb union all select gzbh from gz

解释：将两个数据表中的工种编号查询出来放在一起，不去掉重复记录。第10、11句称为联合查询，使用的是union、union all运算符。在使用UNION 运算符时，应保证每个联合查询语句的列表字段是相同的,联合查询可多个进行联合。

12 Select gz.gzmc,gzlb.gzbh from gzlb,gz, gz inner join gzlb on gzlb.gzbh=gz.gzbh

13 Select gz.gzmc,gzlb.gzbh from gzlb,gz, gz left join gzlb on gzlb.gzbh=gz.gzbh

14 Select gz.gzmc,gzlb.gzbh from gzlb,gz, gz right join gzlb on gzlb.gzbh=gz.gzbh

解释：上面三句称为连接查询，使用的格式是：表1 inner(left,right) join 表2 on 字段1＝（>,<） 字段2,on后可以有更多的条件。其中第12句和下面的语句相同：

 Select gz.gzmc,gzlb.gzbh from gzlb,gz where gzlb.gzbh=gz.gzbh

所以我们不考虑inner join了。连接查询是什么意义呢？作为本讲座的程序来说，有两个数据表，一个是gz(工种)，内有工种编号、工种名称两个字段；gzlb(工种劳保)，内有工种编号、劳保名称、发放周期三个字段。现在我们做一个例子：假设工种数据表中有这么一条记录：999、备用，而工种劳保数据表中没有工种编号为999的记录；同时假设工种劳保数据表中有这么一条记录：888、胶靴、48,而工种数据表中没有工种编号为888的工种记录。用DBGRID显示查询结果，执行第13句，将会在网格中看到这么一条与众不同的记录：备用、空白，如果执行第14句，则有这么一条记录出现：空白、888。通过上述结果，我们可以这样理解连接查询：连接查询的两个数据表必须有相同的字段作为连接桥梁，常用于防止数据库产生大量冗余数据，对于以left join连接方式，以左边的数据表为基准，若右边的数据表没有对应的数据则显示空白，对于以right join连接方式，以右边的数据表为基准。连接查询是很灵活的，需要在使用中摸索其使用技巧。

15 select lbmc from gzlb where gzbh in (select gzbh from gz where gzbh like '?0')

解释：从GZ数据表中将编号以0结尾的工种编号查询出来，然后在GZLB数据表中查询工种编号属于在前面查询得到的数据集中的工种编号对应的劳保名称。此语句的目的是告诉大家IN后面可以是一个子查询，in前面还可以加上NOT来限制不属于这个数据集。

16 select lbmc from gzlb where ffzq > any (select ffzq from gzlb where ffzq>24)

解释：这条语句没有什么实际意义，可用简单的方式完成，但这里这样写是告诉大家如何使用any这个运算符，any前可以使用<,>,<=,>=，=等比较运算符，而在IN前面则不可以使用。

17 select gzbh,sum(ffzq) from gzlb group by gzbh

解释：以工种编号分组，计算发放周期之和。这里是group by的应用，字段列表不能使用＊，前面的单个字段和group by之后的字段必须相同，字段列表除单个字段外其余必须是运算式。

下面介绍数据修添加改删除语句：

18 Insert into gz values (888,备用)

解释：向数据表中增加一条工种编号为888、工种名称为“备用”的记录。也可以经过一个子查询把别的数据表的数据添加到数据表中，如：INSERT INTO 表1 select * from 表2,这里要考虑字段的对应。这种语句的执行需要使用ExecSQL方法，下同。

19 update gzlb set gzmc=’临时’ where gzmc=’备用’

解释：将工种劳保库中的所有“备用”工种名称改为“临时”。若没有条件则所有的工种名称都会被更改，这可以用来更改具有相同内容的字段。

20 delete from gzlb where gzmc=’临时’

解释：删除所有工种名称为“临时”的记录。类似语句：delete from gzlb where ffzq>24

三、数据定义（ＤＤＬ）语句

    没有查询语句用的多，主要用于动态生成数据表。动态生成数据表时要给定足够的参数，如数据表名称，字段名称名称、类型、长度、是否允许空值等

1 create table  tmp (a text(6) NOT NULL CONSTRAINT indxa primary key,b integer)

解释：上面的句子创建了一个新表名字为tmp,其中有两个字段:a,文本、长度6、不允许空值、关键字段、主索引名称为indxa,字段b，整型数

2 alter table  tmp add column c text(6) NOT NULL

解释：给tmp增加一个字段c,文本，长度为6,不允许空值。

3 ALTER TABLE tmp DROP column b

解释：删除字段b,若此字段有索引需要先删除索引再删除此字段。

4 CREATE INDEX indexb ON tmp (b)

解释：为数据表tmp中的字段b建立索引indexb　

5 DROP index indxa on tmp

解释：删除tmp表中的索引indxa

6 alter table  tmp  ADD CONSTRAINT indxc PRIMARY KEY (c)

解释：设定字段C为关键字段，主索引名称为：indxc

7、DROP table tmp

解释：删除数据表tmp