SQL实践
1. NULL值的使用
RDSQL中字段缺省值为空；并且对数值型的0和空值，以及字符型的空白和空值区别对待。
数值表达式中某个变为空，则整个表达式值为空；
聚合函数中，对空值忽略不计，若全部为空值，除COUNT（*）返回0外，其余返回空值。
布尔表达式中，结果可能为“未知”（见下表）。如TRUE AND NULL结果为 “未知”，
对“未知”结果在RDSQL中看作不符合查询条件。
| and | T | F | ? |
| or | T | F | ? |
| not |
|
| T | T | F | ? |
| T | T | T | T |
| T | F |
| F | F | F | ? |
| F | T | F | ? |
| F | T |
| ? | ? | F | ? |
| ? | T | ? | ? |
| ? | ? |
结合上表，分析下列子句 ，其中n1=20；n2为空；n3=30。结果如右。
where n1*n2< 1000 and n3 = 30;结果：不符合查询条件
where n1*n2< 1000 or n3 = 30;结果：符合查询条件
ORDER BY子句中的空值，每一个空值为一组。
INSERT或UPDATE时，可使用关键字NULL/null表示空值。
字段是否可以为空，由CREATETABLE语句中是否有NOT NULL指定或由ALTER修改。
Q：select count(*) from t1和selectcount(c1) from t1是否一样?
2. 字符查找，主要使用LIKE和MATCHES。
| LIKE | MATCHES | 意义 |
| % | * | 匹配0或多个字符 |
| - | ? | 匹配一个字符 |
| \ | \ | 转义字符 |
| 无 | [] | 选择匹配 |
例：matches ‘*Sp’；匹配以任何字符开始，以Sp结束的字段值
matches ‘?l*’； 匹配第一个字符任意，第二个字符为l，其余字符任意的字段值
matches ‘[A-N]*’； 匹配以A到N的字符开始，其余字符任意的字段值
matches ‘*[sS]*’； 匹配含有s或S的字段值，扩展以下可用于case insensitive查询
like ‘%\%%’； 匹配含有%的字段值
3. 用SQL语句求表一中的关于name有多少不同的num，结果如表二。
4. 表一： 表二：
5. id name num name count1
6. 1 AA 1 CC 2
7. 2 AA 2 BB 2
8. 3 AA 3 AA 3
9. 4 AA 1
10. 5 AA 2
11. 6 BB 4
12. 7 BB 5
13. 8 BB 4
14. 9 BB 5
15. 10 CC 6
16. 11 CC 6
17. 12 CC 7
SQL语句如下：
create table t1
(
id smallint,
name char(10),
num smallint
);
insert into t1values(1,'AA',1);
insert into t1values(2,'AA',2);
insert into t1values(3,'AA',3);
insert into t1values(4,'AA',1);
insert into t1values(5,'AA',2);
insert into t1values(6,'BB',4);
insert into t1values(7,'BB',5);
insert into t1values(8,'BB',4);
insert into t1values(9,'BB',5);
insert into t1values(10,'CC',6);
insert into t1values(11,'CC',6);
insert into t1values(12,'CC',7);
A：select name ,count(distinct num) from t1 group by name;
4）使用旋转矩阵，将表一中关于id在不同月份的费用，由纵向变为横向。
其中表一对一个id某个月份的记录数可能>1。表一：
id d1 fee费用（分）
1 2000-01-24100
1 2000-04-24100
2 2000-02-24200
2 2000-06-24200
3 2000-04-24400
4 2000-04-24400
5 2000-05-24500
6 2000-06-24600
7 2000-09-24900
8 2000-11-241100
表二：
id 1月份费用 2月份费用 ……… …12月份费用
1 100 0 0 100 0 0 0 0 0 0 0 0
2 0 200 0 0 0 200 0 0 0 0 0 0
3 0 0 0 400 0 0 0 0 0 0 0 0
4 0 0 0 400 0 0 0 0 0 0 0 0
5 0 0 0 0 500 0 0 0 0 0 0 0
6 0 0 0 0 0 600 0 0 0 0 0 0
7 0 0 0 0 0 0 0 0 900 0 0 0
8 0 0 0 0 0 0 0 0 0 0 1100 0
SQL语句：
create table t3
(
id smallint,
d1 datetimeyear to day,
fee int
);
insert into t3values(1,"2000-01-24", 100);
insert into t3values(1,"2000-04-24", 100);
insert into t3values(2,"2000-02-24", 200);
insert into t3values(2,"2000-06-24", 200);
insert into t3values(3,"2000-04-24", 400);
insert into t3values(4,"2000-04-24", 400);
insert into t3values(5,"2000-05-24", 500);
insert into t3values(6,"2000-06-24", 600);
insert into t3values(7,"2000-09-24", 900);
insert into t3values(8,"2000-11-24", 1100);
create table t4–旋转矩阵
(
m_code smallint,
y1 smallint,
y2 smallint,
y3 smallint,
y4 smallint,
y5 smallint,
y6 smallint,
y7 smallint,
y8 smallint,
y9 smallint,
y10 smallint,
y11 smallint,
y12 smallint
);
insert into t4values(1, 1,0,0,0,0,0,0,0,0,0,0,0);
insert into t4values(2, 0,1,0,0,0,0,0,0,0,0,0,0);
insert into t4values(3, 0,0,1,0,0,0,0,0,0,0,0,0);
insert into t4values(4, 0,0,0,1,0,0,0,0,0,0,0,0);
insert into t4values(5, 0,0,0,0,1,0,0,0,0,0,0,0);
insert into t4values(6, 0,0,0,0,0,1,0,0,0,0,0,0);
insert into t4values(7, 0,0,0,0,0,0,1,0,0,0,0,0);
insert into t4values(8, 0,0,0,0,0,0,0,1,0,0,0,0);
insert into t4values(9, 0,0,0,0,0,0,0,0,1,0,0,0);
insert into t4values(10,0,0,0,0,0,0,0,0,0,1,0,0);
insert into t4values(11,0,0,0,0,0,0,0,0,0,0,1,0);
insert into t4values(12,0,0,0,0,0,0,0,0,0,0,0,1);
--方法一
selectid,month(d1) month,sum(fee) fei from t3 group by 1,2 into temp aa;
select id,
sum(y1*fei)y1,sum(y2*fei) y2,sum(y3*fei) y3,sum(y4*fei) y4,
sum(y5*fei)y5,sum(y6*fei) y6,sum(y7*fei) y7,sum(y8*fei) y8,
sum(y9*fei)y9,sum(y10*fei) y10,sum(y11*fei) y11,sum(y12*fei) y12
from aa, t4where aa.month = t4.m_code
group by idorder by id
--方法二
select id,
sum(y1*fee)y1,sum(y2*fee) y2,sum(y3*fee) y3,sum(y4*fee) y4,
sum(y5*fee)y5,sum(y6*fee) y6,sum(y7*fee) y7,sum(y8*fee) y8,
sum(y9*fee)y9,sum(y10*fee) y10,sum(y11*fee) y11,sum(y12*fee) y12
from t3, t4where month(d1) = t4.m_code
group by idorder by id
方法一和方法二的结果一样，但有所区别：
方法一中是先对id某个月的钱进行累加，然后进行旋转；
方法二中在表一对一个id某个月份的记录数可能>1的情况时，先对每条记录进行旋转，然后在
累加求和。
SQL实践(2)
19. 用SPL写自己的UPPER函数?
INFORMIX中没有UPPER函数,用存储过程实现如下
例:方法一:
=================== SPL: Upper()===================
--
-- Procedure: GetCharAt()
-- Get a character from a string at aspecified position.
--
DROP PROCEDURE GetCharAt;
CREATE PROCEDURE GetCharAt(strVARCHAR(255), pos INTEGER)
RETURNING VARCHAR(1);
DEFINE i INTEGER;
IF pos < 1 THEN
FOR i = 2 TO pos
LET str = str[2,255];
END FOR;
END IF
RETURN str[1,1];
END PROCEDURE;
--
-- Procedure: Upper()
-- Convert a string to uppercase.
--
DROP PROCEDURE Upper;
CREATE PROCEDURE Upper(strVARCHAR(255))
RETURNING VARCHAR(255, 0);
DEFINE i INTEGER;
DEFINE len INTEGER;
DEFINE retstr VARCHAR(255);
IF str IS NULL THEN
RETURN NULL;
ELSE
LET len = LENGTH(str);
LET retstr = '';
FOR i = 1 TO len
LET retstr =retstr||ToUpper(GetCharAt(str, i));
END FOR;
LET retstr = retstr[2,255]; -- BUG:req'd to strip off leading blank
RETURN retstr;
END IF;
END PROCEDURE;
--
-- Procedure: ToUpper()
-- Convert a single character to uppercase.
--
DROP PROCEDURE ToUpper;
CREATE PROCEDURE ToUpper(fromcharVARCHAR(1,1))
RETURNING VARCHAR(1,1);
IF fromchar = 'a' THEN
RETURN 'A';
ELIF fromchar = 'b' THEN
RETURN 'B';
ELIF fromchar = 'c' THEN
RETURN 'C';
ELIF fromchar = 'd' THEN
RETURN 'D';
ELIF fromchar = 'e' THEN
RETURN 'E';
ELIF fromchar = 'f' THEN
RETURN 'F';
ELIF fromchar = 'g' THEN
RETURN 'G';
ELIF fromchar = 'h' THEN
RETURN 'H';
ELIF fromchar = 'i' THEN
RETURN 'I';
ELIF fromchar = 'j' THEN
RETURN 'J';
ELIF fromchar = 'k' THEN
RETURN 'K';
ELIF fromchar = 'l' THEN
RETURN 'L';
ELIF fromchar = 'm' THEN
RETURN 'M';
ELIF fromchar = 'n' THEN
RETURN 'N';
ELIF fromchar = 'o' THEN
RETURN 'O';
ELIF fromchar = 'p' THEN
RETURN 'P';
ELIF fromchar = 'q' THEN
RETURN 'Q';
ELIF fromchar = 'r' THEN
RETURN 'R';
ELIF fromchar = 's' THEN
RETURN 'S';
ELIF fromchar = 't' THEN
RETURN 'T';
ELIF fromchar = 'u' THEN
RETURN 'U';
ELIF fromchar = 'v' THEN
RETURN 'V';
ELIF fromchar = 'w' THEN
RETURN 'W';
ELIF fromchar = 'x' THEN
RETURN 'X';
ELIF fromchar = 'y' THEN
RETURN 'Y';
ELIF fromchar = 'z' THEN
RETURN 'Z';
END IF;
RETURN fromchar;
END PROCEDURE;
方法二:
=================== SPL: Upper()===================
--
-- Procedure: Upper()
-- Convert a string to uppercase.
--
DROP PROCEDURE upper;
CREATE PROCEDURE upper (strVARCHAR(255)) RETURNING VARCHAR(255);
DEFINE i INTEGER;
DEFINE l INTEGER;
DEFINE retstr VARCHAR(255);
IF str IS NULL THEN
RETURN NULL;
ELSE
LET l = LENGTH(str);
LET retstr = '';
FOR i = 1 TO l
IF str[1,1] BETWEEN "a" AND"z" THEN
IF str[1,1] = 'a' THEN
LET retstr = retstr || 'A';
ELIF str[1,1] = 'b' THEN
LET retstr = retstr || 'B';
ELIF str[1,1] = 'c' THEN
LET retstr = retstr || 'C';
ELIF str[1,1] = 'd' THEN
LET retstr = retstr || 'D';
ELIF str[1,1] = 'e' THEN
LET retstr = retstr || 'E';
ELIF str[1,1] = 'f' THEN
LET retstr = retstr || 'F';
ELIF str[1,1] = 'g' THEN
LET retstr = retstr || 'G';
ELIF str[1,1] = 'h' THEN
LET retstr = retstr || 'H';
ELIF str[1,1] = 'i' THEN
LET retstr = retstr || 'I';
ELIF str[1,1] = 'j' THEN
LET retstr = retstr || 'J';
ELIF str[1,1] = 'k' THEN
LET retstr = retstr || 'K';
ELIF str[1,1] = 'l' THEN
LET retstr = retstr || 'L';
ELIF str[1,1] = 'm' THEN
LET retstr = retstr || 'M';
ELIF str[1,1] = 'n' THEN
LET retstr = retstr || 'N';
ELIF str[1,1] = 'o' THEN
LET retstr = retstr || 'O';
ELIF str[1,1] = 'p' THEN
LET retstr = retstr || 'P';
ELIF str[1,1] = 'q' THEN
LET retstr = retstr || 'Q';
ELIF str[1,1] = 'r' THEN
LET retstr = retstr || 'R';
ELIF str[1,1] = 's' THEN
LET retstr = retstr || 'S';
ELIF str[1,1] = 't' THEN
LET retstr = retstr || 'T';
ELIF str[1,1] = 'u' THEN
LET retstr = retstr || 'U';
ELIF str[1,1] = 'v' THEN
LET retstr = retstr || 'V';
ELIF str[1,1] = 'w' THEN
LET retstr = retstr || 'W';
ELIF str[1,1] = 'x' THEN
LET retstr = retstr || 'X';
ELIF str[1,1] = 'y' THEN
LET retstr = retstr || 'Y';
ELSE
LET retstr = retstr || 'Z';
END IF;
ELSE
LET retstr = retstr || str[1,1];
END IF;
LET str = str[2,255];
END FOR;
LET retstr = retstr[2,255]; -- BUG:req'd to strip off leading blank
RETURN retstr;
END IF;
END PROCEDURE;
说明:
某些INFORMIX版本中,语句
DEFINEretstr VARCHAR(255);
LET retstr ='';
LET retstr =retstr || 'A';
将返回' A'.两个字符,如果确定你的系统没有这个问题
将以上SPL中的语句 LET retstr = retstr[2,255];
-- BUG:req'd to strip off leading blank去掉
20. 将一个表中重复键值的记录删除?
假定"keycol"是唯一的(即关于某个键值若有>=2条以上的记录时,只保留一条),
且表未分区,没有别的用户正在删除这个表的记录,使用如下语句
delete fromsometable as a
where rowid<> (select min(rowid) from sometable where keycol = a.keycol)
如以上条件不满足或没有ROWID,可以使用如下语句
BEGIN WORK;
SELECTDISTINCT * FROM Table INTO TEMP Temp1;
DELETE FROMTable WHERE 1 = 1;
INSERT INTOTable SELECT * FROM Temp1;
COMMIT WORK;
说明:适用于记录数不多,且临时空间足够的情况.
21. 怎样在语句错误时停止使用SQL脚本?
例:
$ dbaccess<database> <sql-file>
<sql-file>:
BEGIN WORK;
INSERT INTOhistory
SELECT *
FROM current
WHERE momth= 11;
DELETE FROMcurrent
WHERE month= 11;
COMMIT WORK;
插入语句错误(字段momth没有找到),脚本还是会继续执行,这回带来严重的错误.
可以通过设置环境变量DBACCNOIGN=1,使错误发生后,立即停止继续运行.
22. 设置运算结果的返回位数?
例:未使用DBDLTMASK环境变量时,
CREATE TEMPTABLE t
(
col_aDECIMAL(8,4) NOT NULL,
col_bDECIMAL(8,4) NOT NULL,
col_cDECIMAL(8,4) NOT NULL
);
INSERT INTOt VALUES(1.2345, 3.4567, 5.6789);
SELECT(col_a + col_b) / col_c AS value FROM t;
设置环境变量DBDLTMASK前 value 0.82607547236261
设置环境变量DBDLTMASK=6后 value 0.826075
Sql连接查询
连接查询
通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。
在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。
连接可以在SELECT语句的FROM子句或WHERE子句中建立，似是而非在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。所以，在Transact-SQL中推荐使用这种方法。
SQL-92标准所定义的FROM子句的连接语法格式为：
FROMjoin_table join_type join_table
[ON(join_condition)]
其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。
join_type指出连接类型，可分为三种：内连接、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。
外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。
交叉连接(CROSS JOIN)没有WHERE子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
连接操作中的ON (join_condition)子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。
无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。例如：
SELECTp1.pub_id,p2.pub_id,p1.pr_info
FROMpub_info AS p1 INNER JOIN pub_info AS p2
ONDATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)
(一)内连接
内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：
1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。
例，下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社：
SELECT *
FROM authorsAS a INNER JOIN publishers AS p
ONa.city=p.city
又如使用自然连接，在选择列表中删除authors和publishers表中重复列(city和state)：
SELECTa.*,p.pub_id,p.pub_name,p.country
FROM authorsAS a INNER JOIN publishers AS p
ONa.city=p.city
(二)外连接
内连接时，返回查询结果集合中的仅是符合查询条件( WHERE搜索条件或 HAVING条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。
如下面使用左外连接将论坛内容和作者信息连接起来：
SELECTa.*,b.* FROM luntan LEFT JOIN usertable as b
ON a.username=b.username
下面使用全外连接将city表中的所有作者以及user表中的所有作者，以及他们所在的城市：
SELECTa.*,b.*
FROM city asa FULL OUTER JOIN user as b
ONa.username=b.username
(三)交叉连接
交叉连接不带WHERE子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等
于6*8=48行。
SELECTtype,pub_name
FROM titlesCROSS JOIN publishers
ORDER BYtype
145
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
