............................................................................................................................... 1
作者身份 ..................................................................................................................................... 1
语言参考更新 .................................................................................................. 1
这本厚书的酝酿 ...................................................................................................... 2
贡献者........................................................................................................................... 2
致谢 .......................................................................................................................... 3
2. SQL语言结构 .................................................................................................................... 5
Firebird的SQL语言背景 ......................................................................................... 5
SQL 特性 ........................................................................................................................ 5
SQL 方言 ......................................................................................................................... 6
出错条件 .................................................................................................................... 7
基本元素: 语句,子句,关键字 .............................................................................. 7
标识符 ...................................................................................................................................... 8
文本 .......................................................................................................................................... 9
操作符与特殊字符 .................................................................................................... 9
注释 .................................................................................................................................... 10
3. 数据类型与子类型 ................................................................................................................. 12
整数类 .......................................................................................................................... 14
小整型 ........................................................................................................................... 14
整型 ............................................................................................................................. 14
大整型................................................................................................................................ 14
整数的十六进制格式 ............................................................................. 15
浮点类 ................................................................................................................ 15
浮点型 ................................................................................................................................. 16
双精度型 ............................................................................................................ 16
定点类 .................................................................................................................... 16
数值型 ............................................................................................................................ 17
小数型 ............................................................................................................................. 17
日期时间类 ................................................................................................... 17
日期型 ................................................................................................................................... 18
时间型 ................................................................................................................................... 19
时间戳型 ......................................................................................................................... 19
日期时间所使用的操作符 ............................................................................... 19
字符类型 ................................................................................................................... 20
统一码Unicode ............................................................................................................................... 21
客户端字符集 ............................................................................................................. 21
特殊字符集合 .......................................................................................................... 21
排序规则 ............................................................................................................... 21
字符索引 ................................................................................................................. 23
字符串类型明细 ..................................................................................................... 24
二进制数类型 ....................................................................................................................... 25
BLOB子类型 ................................................................................................................... 25
BLOB特性 .................................................................................................................... 26
数组类型 ....................................................................................................................... 26
特殊数据类型 ...................................................................................................................... 28
SQL_NULL数据类型 .......................................................................................................... 28
数据类型转换 ............................................................................................................ 29
显式数据类型转换 ............................................................................................. 29
隐式数据类型转换............................................................................................. 33
自定义数据类型--域(Domains) ..................................................................................................... 35
域属性 ................................................................................................................ 35
域覆盖 .................................................................................................................. 35
创建和管理域 .................................................................................... 36
4. 通用语言元素 ............................................................................................................ 38
表达式 .................................................................................................................................. 38
常量 ............................................................................................................................. 39
SQL操作符 ..................................................................................................................... 42
条件表达式 ....................................................................................................... 45
NULL在表达式中 ............................................................................................................ 46
子查询 ........................................................................................................................... 47
判断式 ..................................................................................................................................... 49
断言 ............................................................................................................................ 49
比较判断式 ......................................................................................................... 49
存在判断式 ............................................................................................................ 60
量化子查询判断式 ................................................................................................. 63
5.数据定义语句 .................................................................................................... 65
数据库(DATABASE) .................................................................................................................................. 65
创建数据库(CREATE DATABASE)语句......................................................................................................... 65
修改数据库(ALTER DATABASE)语句 .............................................................................................................. 69
删除数据库(DROP DATABASE)语句 ................................................................................................................ 72
镜像库(SHADOW) .................................................................................................................................... 74
建立镜像库(CREATE SHADOW) ............................................................................................................... 74
删除镜像库(DROP SHADOW) ................................................................................................................... 75
域(DOMAIN) ...................................................................................................................................... 75
创建域(CREATE DOMAIN) ................................................................................................................ 75
修改域(ALTER DOMAIN) .................................................................................................................. 80
删除域(DROP DOMAIN) .................................................................................................................... 83
表(TABLE) ......................................................................................................................................... 85
创建表(CREATE TABLE) ................................................................................................................... 85
修改表(ALTER TABLE) ..................................................................................................................... 98
删除表(DROP TABLE) ..................................................................................................................... 104
重建表(RECREATE TABLE) ............................................................................................................. 105
索引(INDEX) ....................................................................................................................................... 106
创建索引(CREATE INDEX) .................................................................................................................. 106
修改索引(ALTER INDEX) .................................................................................................................... 109
删除索引(DROP INDEX) ...................................................................................................................... 110
集合统计(SET STATISTICS) ................................................................................................................ 111
视图(VIEW) ......................................................................................................................................... 113
创建视图(CREATE VIEW) ................................................................................................................... 113
修改视图(ALTER VIEW) ..................................................................................................................... 115
创建或修改视图(CREATE OR ALTER VIEW) .................................................................................................. 116
删除视图(DROP VIEW) ....................................................................................................................... 117
重建视图(RECREATE VIEW) ............................................................................................................... 118
触发器(TRIGGER) ................................................................................................................................... 120
创建触发器(CREATE TRIGGER) .............................................................................................................. 120
修改触发器(ALTER TRIGGER) ................................................................................................................ 125
创建或修改触发器(CREATE OR ALTER TRIGGER) ............................................................................................ 127
删除触发器(DROP TRIGGER) .................................................................................................................. 128
重建触发器(RECREATE TRIGGER )......................................................................................................... 128
存储过程(PROCEDURE) .............................................................................................................................. 129
创建存储过程(CREATE PROCEDURE) ........................................................................................................ 129
修改存储过程(ALTER PROCEDURE) ........................................................................................................... 134
创建或修改存储过程(CREATE OR ALTER PROCEDURE) ....................................................................................... 137
删除存储过程(DROP PROCEDURE) ............................................................................................................ 137
重建存储过程(RECREATE PROCEDURE) .................................................................................................... 138
外部函数(EXTERNAL FUNCTION) ............................................................................................................... 140
声明外部函数(DECLARE EXTERNAL FUNCTION) ....................................................................................... 140
修改外部函数(ALTER EXTERNAL FUNCTION) ........................................................................................... 143
删除外部函数(DROP EXTERNAL FUNCTION) ............................................................................................. 143
过滤器(FILTER) ....................................................................................................................................... 144
声明过滤器(DECLARE FILTER) .............................................................................................................. 144
删除过滤器(DROP FILTER) ..................................................................................................................... 146
序列生成器SEQUENCE (GENERATOR) ......................................................................................................... 147
创建序列生成器(CREATE SEQUENCE) ........................................................................................................... 148
修改序列生成器(ALTER SEQUENCE) ............................................................................................................. 148
设置序列生成器(SET GENERATOR) ............................................................................................................... 149
删除序列生成器(DROP SEQUENCE) ............................................................................................................... 149
异常(EXCEPTION) ............................................................................................................................... 150
创建异常(CREATE EXCEPTION) .......................................................................................................... 150
修改异常(ALTER EXCEPTION) ............................................................................................................ 151
创建或修改异常(CREATE OR ALTER EXCEPTION) ........................................................................................ 152
删除异常(DROP EXCEPTION) .............................................................................................................. 153
重建异常(RECREATE EXCEPTION) ..................................................................................................... 154
排序规则(COLLATION) ............................................................................................................................... 155
创建排序规则(CREATE COLLATION) ......................................................................................................... 155
删除排序规则(DROP COLLATION) ............................................................................................................. 157
字符集(CHARACTER SET) ....................................................................................................................... 159
修改字符集(ALTER CHARACTER SET) ................................................................................................... 159
角色(ROLE) ......................................................................................................................................... 160
创建角色(CREATE ROLE) ................................................................................................................... 160
修改角色(ALTER ROLE) ..................................................................................................................... 160
删除角色(DROP ROLE) ....................................................................................................................... 160
注释(COMMENTS) ............................................................................................................................... 162
COMMENT ON语句................................................................................................................... 162
6. 数据操纵语句(DML) ............................................................................................. 163
SELECT语句 ...................................................................................................................................... 163
FIRST, SKIP子句 ....................................................................................................................... 164
The SELECT Columns List ................................................................................................. 166
The FROM clause ............................................................................................................... 169
Joins .................................................................................................................................. 175
The WHERE clause ............................................................................................................. 183
The GROUP BY clause ........................................................................................................ 185
The PLAN clause ................................................................................................................ 190
UNION ............................................................................................................................... 194
ORDER BY ......................................................................................................................... 195
ROWS ................................................................................................................................ 199
FOR UPDATE [OF] .............................................................................................................. 201
WITH LOCK ....................................................................................................................... 202
INTO .................................................................................................................................. 205
Common Table Expressions (“WITH ... AS ... SELECT”) ........................................................ 206
INSERT ...................................................................................................................................... 210
INSERT ... VALUES ............................................................................................................ 211
INSERT ... SELECT ............................................................................................................. 211
INSERT ... DEFAULT VALUES ............................................................................................ 213
The RETURNING clause ..................................................................................................... 213
Inserting into BLOB columns .............................................................................................. 214
更新语句UPDATE .......................................................................................................................... 214
使用别名 .................................................................................................................... 215
SET子句 .................................................................................................................. 216
WHERE子句 ............................................................................................................ 216
ORDER BY 和 ROWS子句 .................................................................................... 218
RETURNING 子句 ..................................................................................................... 219
更新BLOB列 ..................................................................................................... 219
UPDATE OR INSERT .................................................................................................................. 220
The RETURNING clause ..................................................................................................... 221
DELETE ..................................................................................................................................... 221
Aliases ............................................................................................................................... 222
WHERE .............................................................................................................................. 222
PLAN ................................................................................................................................. 223
ORDER BY and ROWS ........................................................................................................ 223
RETURNING ....................................................................................................................... 225
MERGE ...................................................................................................................................... 225
执行存储过程EXECUTE PROCEDURE .............................................................................................................. 227
“Executable” Stored Procedure ............................................................................................ 228
EXECUTE BLOCK ...................................................................................................................... 229
Input and output parameters ................................................................................................ 231
Statement Terminators ........................................................................................................ 232
7. 程序 SQL语句 (PSQL) ................................................................................................ 233
PSQL的元素 ...................................................................................................................... 233
DML语句和参数 ....................................................................................... 233
事务 ....................................................................................................................... 233
模块结构 ................................................................................................................ 234
存储过程 ....................................................................................................................... 236
存储过程的好处 ............................................................................................. 236
存储过程的类型 ................................................................................................ 236
创建一个存储过程 ................................................................................................ 237
Modifying a Stored Procedure ............................................................................................. 237
Deleting a Stored Procedure ................................................................................................ 238
Stored Functions ......................................................................................................................... 238
PSQL Blocks ............................................................................................................................. 239
触发器 ..................................................................................................................................... 239
触发器顺序(执行的顺序) ....................................................................................... 240
DML触发器 .................................................................................................................... 240
数据库触发器 ............................................................................................................... 241
创建触发器................................................................................................................ 241
修改触发器 ............................................................................................................ 242
删除一个触发器 .............................................................................................................. 243
Writing the Body Code ............................................................................................................... 243
Assignment Statements ....................................................................................................... 244
DECLARE CURSOR ............................................................................................................ 245
DECLARE VARIABLE ......................................................................................................... 247
BEGIN ... END .................................................................................................................... 250
IF ... THEN ... ELSE ............................................................................................................. 251
WHILE ... DO ...................................................................................................................... 253
LEAVE ............................................................................................................................... 254
EXIT语句 .................................................................................................................................. 256
SUSPEND语句 ........................................................................................................................... 256
EXECUTE STATEMENT ...................................................................................................... 257
FOR SELECT ...................................................................................................................... 262
FOR EXECUTE STATEMENT ............................................................................................... 265
OPEN ................................................................................................................................. 266
FETCH ............................................................................................................................... 268
CLOSE ............................................................................................................................... 269
IN AUTONOMOUS TRANSACTION ...................................................................................... 270
POST_EVENT ..................................................................................................................... 271
Trapping and Handling Errors ..................................................................................................... 272
System Exceptions ............................................................................................................. 272
Custom Exceptions ............................................................................................................. 273
EXCEPTION ....................................................................................................................... 273
WHEN ... DO ...................................................................................................................... 275
8.内置函数和变量 ...................................................................................................... 279
上下文变量 ........................................................................................................................ 279
变量CURRENT_CONNECTION ................................................................................................... 279
变量CURRENT_DATE ................................................................................................................ 279
变量CURRENT_ROLE ................................................................................................................ 280
变量CURRENT_TIME ................................................................................................................. 280
变量CURRENT_TIMESTAMP ...................................................................................................... 282
变量CURRENT_TRANSACTION .................................................................................................. 283
变量CURRENT_USER ................................................................................................................ 283
变量DELETING ......................................................................................................................... 284
变量GDSCODE .......................................................................................................................... 284
变量INSERTING ........................................................................................................................ 284
变量NEW .................................................................................................................................. 285
变量'NOW' ................................................................................................................................. 284
变量OLD ................................................................................................................................... 285
变量ROW_COUNT ..................................................................................................................... 285
变量SQLCODE ........................................................................................................................... 286
变量SQLSTATE ......................................................................................................................... 287
变量'TODAY' ............................................................................................................................. 287
变量'TOMORROW' ..................................................................................................................... 288
变量UPDATING ......................................................................................................................... 288
变量'YESTERDAY' ..................................................................................................................... 289
变量USER ................................................................................................................................. 289
标量函数Scalar Functions ......................................................................................................................... 290
用于处理上下文变量的函数 ........................................................................................................... 290
Mathematical Functions ...................................................................................................... 293
Functions for Working with Strings ..................................................................................... 305
Date and Time Functions .................................................................................................... 321
Type Casting Functions ...................................................................................................... 325
Functions for Bitwise Operations ........................................................................................ 328
Functions for Working with UUID ...................................................................................... 331
Functions for Working with Generators (Sequences) ............................................................ 333
条件函数......................................................................................................... 334
Aggregate Functions ................................................................................................................... 338
AVG() ................................................................................................................................ 338
COUNT() ............................................................................................................................ 339
LIST() ................................................................................................................................. 340
MAX() ................................................................................................................................ 341
MIN() ................................................................................................................................. 342
SUM() ................................................................................................................................ 343
9. 事务控制 ........................................................................................................................ 344
事务语句 ............................................................................................................... 344
设置事务 ............................................................................................................ 344
提交事务 ............................................................................................................................ 350
回滚事务 ........................................................................................................................ 351
SAVEPOINT ....................................................................................................................... 353
RELEASE SAVEPOINT .................................................................................................... 354
Internal Savepoints ............................................................................................................. 354
Savepoints and PSQL ......................................................................................................... 355
10. 安全 ........................................................................................................................................ 358
用户验证 .................................................................................................................... 358
特权用户 .................................................................................................. 358
RDB$ADMIN角色 .............................................................................................................. 360
管理员用户 .................................................................................................................... 363
用户管理的SQL语句 ................................................................................ 364
SQL Privileges ........................................................................................................................... 366
The Object Owner .............................................................................................................. 366
Statements for Granting Privileges ...................................................................................... 366
Statements for Revoking Privileges ..................................................................................... 372
附录 A: 补充信息 ............................................................................................. 377
The RDB$VALID_BLR Field ....................................................................................................... 377
How Invalidation Works ..................................................................................................... 377
A Note on Equality .................................................................................................................... 379
附录 B: 异常代码和提示信息 ....................................................................................... 380
SQLSTATE错误代码和描述 .................................................................................. 380
SQLCODE and GDSCODE Error Codes and Descriptions ........................................................... 388
附录 C: 保留字和关键字 ....................................................................................... 422
保留字 .......................................................................................................................... 422
关键字 ................................................................................................................................... 423
附录 D: 系统表 ............................................................................................................... 427
RDB$BACKUP_HISTORY ........................................................................................................ 428
RDB$CHARACTER_SETS ........................................................................................................ 428
RDB$CHECK_CONSTRAINTS ................................................................................................. 429
RDB$COLLATIONS ................................................................................................................. 430
RDB$DATABASE ..................................................................................................................... 430
RDB$DEPENDENCIES ............................................................................................................. 431
RDB$EXCEPTIONS .................................................................................................................. 432
RDB$FIELDS ............................................................................................................................ 432
RDB$FIELD_DIMENSIONS ...................................................................................................... 436
RDB$FILES ............................................................................................................................... 436
RDB$FILTERS .......................................................................................................................... 437
RDB$FORMATS ....................................................................................................................... 437
RDB$FUNCTIONS .................................................................................................................... 438
RDB$FUNCTION_ARGUMENTS ............................................................................................. 439
RDB$GENERATORS ................................................................................................................ 440
RDB$INDICES .......................................................................................................................... 440
RDB$INDEX_SEGMENTS ........................................................................................................ 442
RDB$LOG_FILES ..................................................................................................................... 442
RDB$PAGES ............................................................................................................................. 442
RDB$PROCEDURES ................................................................................................................. 443
RDB$PROCEDURE_PARAMETERS ......................................................................................... 444
RDB$REF_CONSTRAINTS ....................................................................................................... 445
RDB$RELATIONS .................................................................................................................... 446
RDB$RELATION_CONSTRAINTS ........................................................................................... 447
RDB$RELATION_FIELDS ........................................................................................................ 448
RDB$ROLES ............................................................................................................................. 449
RDB$SECURITY_CLASSES ..................................................................................................... 450
RDB$TRANSACTIONS ............................................................................................................. 450
RDB$TRIGGERS ....................................................................................................................... 451
RDB$TRIGGER_MESSAGES .................................................................................................... 452
RDB$TYPES ............................................................................................................................. 452
RDB$USER_PRIVILEGES ........................................................................................................ 453
RDB$VIEW_RELATIONS ......................................................................................................... 454
附录 E: 监控表 .......................................................................................................... 457
MON$ATTACHMENTS ............................................................................................................ 458
Using MON$ATTACHMENTS to Kill a Connection ........................................................... 459
MON$CALL_STACK ................................................................................................................ 459
MON$CONTEXT_VARIABLES ................................................................................................ 458
MON$DATABASE .................................................................................................................... 459
MON$IO_STATS ....................................................................................................................... 460
MON$MEMORY_USAGE ......................................................................................................... 461
MON$RECORD_STATS ............................................................................................................ 462
MON$STATEMENTS ................................................................................................................ 462
Using MON$STATEMENTS to Cancel a Query ..................................................................... 463
MON$TRANSACTIONS ............................................................................................................ 463
附录 F: 字符集和排序 ........................................................................... 465
附录 G: 许可声明 ................................................................................................................ 471
附录 H: 文档历史 .......................................................................................................... 472