SQL 文法参考

如果编译原理学得不错的话，根据sql的文法就可以写个程序来解析了,最简单的莫过于自顶向下递归解析。

sql数据定义:表定义，更改语句，删除语句和模式定义

sql创建语句 
1
2

create_statement:
 create {role_def | table_def | view_def | type_def | func_def | index_def | trigger_def | sequence_def}
sql表定义的文法 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

table_def:
  CREATE [ TEMPORARY | LOCAL TEMPORARY | GLOBAL TEMPORARY ] TABLE table_name'(' table_content_source ')'
   [ ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS | ON COMMIT DROP ]

table_content_source:
   table_element ',' ... | column_name ',' ... AS select_query [WITH NO DATA | WITH DATA]

table_element:
    column_name data_type [column_option ...]
  | CONSTRAINT constraint_name table_constraint_type
  | column_name WITH OPTIONS '(' column_option ','... ')'
  | LIKE table_name
  | column_name SERIAL
  | column_name BIGSERIAL

table_constraint_type:
    UNIQUE '(' column_name ',' ... ')'
   | PRIMARY KEY '(' column_name ',' ... ')'
   | FOREIGN KEY '(' column_name ',' ... ')' REFERENCES table_name ['(' column_name ',' ... ')'] [ match_options ] [ ref_actions ]

column_option:
    DEFAULT default_value
   | [ constraint_name ] column_constraint_type
   | GENERATED ALWAYS AS IDENTITY [ '(' serial_parameters ')' ]
   | AUTO_INCREMENT

column_constraint_type:
    NOT NULL | NULL | UNIQUE | PRIMARY KEY
  | REFERENCES table_name [ '(' column_name ',' ... ')' ] [ match_options ] [ ref_actions ]

serial_parameters:
    [START WITH nonzero]
  | [RESTART | RESTART WITH subquery
  | RESTART WITH nonzero]
  | [INCREMENT BY nonzero]
  | [MINVALUE nonzero | NOMINVALUE] | [MAXVALUE nonzero | NOMAXVALUE ] | [CACHE nonzero ] | [CYCLE | NONCYCLE]

match_options:
    MATCH { FULL | PARTIAL | SIMPLE }

ref_actions:
    ON UPDATE { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT }
  | ON DELETE { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT }
  | { ON DELETE | ON UPDATE } { NO ACTION | CASCASDE | RESTRICT | SET NULL | SET DEFAULT }
  | { ON UPDATE | ON DELETE } { NO ACTION | CASCASDE | RESTRICT | SET NULL | SET DEFAULT }
sql表的标识列 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE table_name table_content_source

table_content_source:
    '(' table_element ',' ... ')'
  | ['('column_name ',' ...')'] AS select_query [WITH NO DATA | WITH DATA]

table_element:
     column_name data_type [ column_option ...]
   | column_name WITH OPTIONS '(' column_option ',' ... ')'

column_option:
     DEFAULT default_value
   | GENERATED ALWAYS AS IDENTITY [ '(' serial_parameters ')' ]
   | AUTO_INCREMENT

serial_parameters:
     [ START WITH nonzero ]
   | [ RESTART | RESTART WITH subquery
   | RESTART WITH nonzero ]
   | [INCREMENT BY nonzero ]
   | [MINVALUE nonzero | NOMINVALUE]
   | [MAXVALUE nonzero | NOMAXVALUE ]
   | [CACHE nonzero ]
   | [CYCLE | NONCYCLE]
sql表的限制 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

CREATE TABLE table_name table_content_source

table_content_source:
   '('  table_element ',' ...  ')'

table_element:
     column_name data_type [ column_option ... ]
   | CONSTRAINT constraint_name table_constraint_type
   | column_name WITH OPTIONS ( column_option ',' ... )

table_constraint_type:
     UNIQUE ( column_name ',' ...)
   | PRIMARY KEY '(' column_name ','  ... ')'
   | FOREIGN KEY '(' column_name ','  ... ')' REFERENCES table_name
   [ '(' column_name ',' ...')'] [ match_options ] [ ref_actions ]

column_option:
   [ constraint_name ] column_constraint_type

column_constraint_type:
     NOT NULL
   | NULL
   | UNIQUE
   | PRIMARY KEY
   | REFERENCES table_name [ '(' column_name ',' ...')' ] [match_options] [ ref_actions ]

match_options:
     MATCH { FULL | PARTIAL | SIMPLE }

ref_actions:
     ON UPDATE { NO ACTION | CASCASDE | RESTRICT | SET NULL | SET DEFAULT }
   | ON DELETE { NO ACTION | CASCASDE | RESTRICT | SET NULL | SET DEFAULT }
   | { ON DELETE | ON UPDATE } { NO ACTION | CASCASDE | RESTRICT | SET NULL | SET DEFAULT }
   | { ON UPDATE | ON DELETE } { NO ACTION | CASCASDE | RESTRICT | SET NULL | SET DEFAULT }
sql创建表索引 
1
2
3

index_def:
    [ UNIQUE | CLUSTERED ] INDEX ident ON qname '(' ident ','... ')'
   | CLUSTER ident ON qname '(' ident ','... ')
sql创建表的视图 
1

view_def: CREATE VIEW view_name ['(' column_name ',' ...')'] AS select_query [WITH CHECK OPTION]
sql更改语句 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

alter_statement:
     ALTER TABLE qname ADD [ COLUMN ] add_table_element
   | ALTER TABLE qname ALTER alter_table_element
   | ALTER TABLE qname DROP drop_table_element
   | ALTER TABLE qname SET READ ONLY
   | ALTER USER ident passwd_schema
   | ALTER USER ident RENAME TO ident
   | ALTER USER SET
     [ ENCRYPTED | UNENCRYPTED ] PASSWORD string USING OLD PASSWORD string
   | ALTER SEQUENCE name
     [ RESTART [ WITH [ poslng | subqeury ] ] ]
     [ INCREMENT BY nonzerolng ]
     [ MINVALUE nonzerolng | NO MINVALUE ]
     [ MAXVALUE nonzerolng | NO MAXVALUE ]
     [ CACHE nonzerolng ]
     [ [ NO ] CYCLE ]
   | ALTER SEQUENCE qname opt_alt_seq_params


alter_table_element: [ COLUMN ] ident SET [ NOT ] NULL | [ COLUMN ] ident SET DEFAULT [ default_value ]


drop_table_element: [ COLUMN ] ident [ RESTRICT | CASCADE ] | CONSTRAINT ident drop_action


constraint_type:
     UNIQUE '(' column_name ',' ...')'
   | PRIMARY KEY '('column_name ',' ...')'
   | FOREIGN KEY '('column_name ','...')' PEFERENCES table_name ['('column_name','...')']
      [MATCH [ FULL | PARTIAL | SIMPLE ] ] [ ref_action ]
   | CHECK '(' search_condition ')'


ref_action: ON {DELETE | UPDATE} {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}


passwd_schema: SET SCHEMA ident | WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD string [ SET SCHEMA ident ]
sql删除语句 
1
2
3
4
5
6
7
8
9
10
11
12
13

drop_statement:
     DROP TABLE qname [ RESTRICT | CASCADE ]
   | DROP FUNCTION qname '(' [ data_type ','... ] ')' [ RESTRICT | CASCADE ]
   | DROP PROCEDURE qname '(' [ data_type ','... ] ')' [ RESTRICT | CASCADE ]
   | DROP ALL FUNCTION qname [ RESTRICT | CASCADE ]
   | DROP ALL PROCEDURE qname [ RESTRICT | CASCADE ]
   | DROP VIEW qname [ RESTRICT | CASCADE ]
   | DROP ROLE qname
   | DROP USER ident
   | DROP INDEX qname
   | DROP CLUSTER qname
   | DROP TRIGGER qname
   | DROP SEQUENCE qname
sql创建模式 
1
2
3
4
5
6
7
8
9
10

schema:
   CREATE SCHEMA schema_name_clause
  [ DEFAULT CHARACTER SET ident ]
  [ PATH schema_name ','... ]
  [ schema_element ... ]
| DROP SCHEMA qname drop_action

schema_name_clause: ident | [ ident ] AUTHORIZATION

ident schema_element: grant | revoke | create_statement | drop_statement | alter_statement
sql用户验证 
1
2
3
4
5
6

user_stmt:
    CREATE USER "user_name" WITH PASSWORD 'user_password' NAME 'name' SCHEMA "schema_name"
  | ALTER USER "user_name" [ WITH [ ENCRYPTED | UNENCRYPYED ] PASSWORD 'user_password' ]  [ SET SCHEMA "new_schema_name" ]
  | ALTER USER SET [ ENCRYPTED | UNENCRYPYED ] PASSWORD 'new_password' USING OLD PASSWORD 'cur_password'
  | DROP USER "user_name"
  | ALTER USER "user_name" RENAME TO "new_user_name"
sql角色 
1
2
3
4
5

role_def:
    ROLE qname [ WITH ADMIN grantor ]
   | USER ident WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD string NAME string SCHEMA ident

grantor: CURRENT_USER | CURRENT_ROLE
sql授权 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

grant:
    GRANT privileges TO grantees [ WITH GRANT OPTION ] [ WITH ADMIN grantor ]
  | GRANT authid_list TO grantees [ WITH ADMIN OPTION ] [ WITH ADMIN grantor ]

revoke:
    REVOKE [ GRANT OPTION FOR ] privileges FROM grantees [ FROM grantor ]
  | REVOKE [ ADMIN OPTION FOR ] authid ','... FROM grantees [ FROM grantor ]

grantor: CURRENT_USER | CURRENT_ROLE

privileges: object_privileges ON TABLE [ ident | ident ]

object_privileges: ALL [ PRIVILEGES ] | operation ','...

operation: INSERT | DELETE | [ UPDATE | SELECT | REFERENCES ] opt_column_list
    | EXECUTE grantees: [ PUBLIC | authid ] ','...

数据操作：1.表更新 2.表操作 3.批量输入输出

sql表更新 
1
2
3
4
5
6
7
8

INSERT INTO table_name [ '(' column_name ',' ... ')' ] [ VALUES '('value_expression','...')' ',' ... | select_query ]

UPDATE table_name SET assignment ',' ... [WHERE search_condition ]

assignment : column_name '='  ( scalar_exp  | search_condition | NULL )


DELETE FROM table_name [WHERE search_condition ]
sql表join操作 
1
2
3
4
5
6
7
8
9
10
11
12
13
14

table_reference: simple_table
    | '(' select_query ')' [AS] table_name [ '(' column_name ',' ... ')' ]
    | '(' select_query ')'
    | joined_table | '(' joined_table ')' [AS] table_name [ '(' column_name ',' ... ')' ]

joined_table: '(' joined_table ')'
    | table_reference { CROSS | NATURAL } JOIN table_reference
    | table_reference { JOIN | UNIONJOIN} table_reference join_spec
    | table_reference join_type JOIN table_reference join_spec
    | table_reference NATURAL join_type JOIN table_reference

join_spec : ON search_condition | USING '(colunm_name ',' ...' )'

join_type: INNER | { LEFT | RIGHT | FULL } [OUTER]
sql表select操作 
1
2
3
4
5
6
7
8

select_query:
    SELECT [ ALL | DISTINCT ]  ( '*' | column_name ',' ... ) [ INTO column_name ',' ... ]
    [ FROM table_name ',' ... ]
    [ WHERE search_condition ]
    [ GROUP BY column_name ',' ... ]
    [ HAVING search_condition]
    [ ORDER BY ordering_spec ',' ... ]
    [ LIMIT posint ] [ OFFSET posint ]
sql表with操作 
1
2
3

with_clause :  WITH with_element ',' ... select_query

with_element: table_name '(' column_name ',' ... ')' AS '(' select_query ')'
sql表各种其他类型的操作 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

predicate:
    comparison_predicate
    | between_predicate
    | like_predicate
    | test_for_null
    | in_predicate
    | all_or_any_predicate
    | existence_test
    | '(' predicate ')'

comparison_predicate:
    pred_expr COMPARISON pred_expr
    | pred_expr = pred_exp between_predicate:
    pred_expr [NOT] BETWEEN [SYMMETRIC | ASYMMETRIC] pred_expr AND pred_expr like_predicate:
    pred_expr [NOT] LIKE atom_expr

test_for_null:
    column_name IS [NOT] NULL

in_predicate:
    pred_expr [NOT] IN '(' element_name ',' ... ')' all_or_any_predicate :
    pred_expr COMPARISON { ANY | ALL | SOME } subquery

existence_test:
    [NOT] EXISTS subquery pred_expr:
    scalar_expr
    | predicate

atom_expr:
    ['('] atom [')']
    | ['('] atom [')'] ESCAPE string
    | '?'
sql表string类型的操作 
1
2
3
4
5
6
7

string_funcs:
      SUBSTRING ( scalar_expr FROM scalar_expr FOR scalar_expr ')'
    | SUBSTRING '(' scalar_expr ',' scalar_expr ',' scalar_expr ')'
    | SUBSTRING '(' scalar_exprFROM scalar_expr ')'
    | SUBSTRING '(' scalar_expr ',' scalar_expr ')'
    | SUBSTRING '(' scalar_expr int_value scalar_expr ')'
    | scalar_expr '||' scalar_expr
sql过程定义 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

func_def:
     CREATE PROCEDURE qname '(' [ paramlist] ')' EXTERNAL NAME external_function_name
     | CREATE PROCEDURE qname '(' [ paramlist] ')' routine_body
routine_body:
      procedure_statement
     | BEGIN procedure_statement_list END
     | BEGIN ATOMIC procedure_statement_list END

procedure_statement:
       transaction_statement
     | update_statement
     | schema
     | grant
     | revoke
     | create_statement
     | drop_statement
     | alter_statement
     | declare_statement
     | set_statement
     | control_statement

sql程序模块定义：1.过程定义 2.函数定义 3.触发器定义 4.变量定义 5.控制流

sql函数定义 
1
2
3
4
5
6
7
8

unc_def:
     FUNCTION qname '(' [ paramlist] ')' RETURNS func_data_type EXTERNAL NAME external_function_name
   | FUNCTION qname '(' [ paramlist ] ')' RETURNS func_data_type routine_body
   | AGGREGATE qname '(' [ paramlist ] ')' RETURNS func_data_type EXTERNAL NAME external_function_name

func_data_type:
      TABLE '(' column data_type ','... ')'
    | data_type type_def: TYPE qname EXTERNAL NAME ident
sql触发器定义 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

trigger_def: TRIGGER qname trigger_action_time trigger_event ON ident opt_referencing_list triggered_action

trigger_action_time: BEFORE | AFTER

trigger_event: INSERT | DELETE | UPDATE | UPDATE OF ident ','...

opt_referencing_list: [ REFERENCING old_or_new_values_alias ... ]

old_or_new_values_alias:
      OLD [ ROW ] [ AS ] ident | NEW [ ROW ] [ AS ] ident
    | OLD TABLE [ AS ] ident | NEW TABLE [ AS ] ident

triggered_action: opt_for_each  [ WHEN search_condition ] triggered_statement

opt_for_each: /*default for each statement*/ | FOR EACH ROW | FOR EACH STATEMENT

triggered_statement:
     trigger_procedure_statement
   | BEGIN ATOMIC trigger_procedure_statement_list END

trigger_procedure_statement: transaction_statement | update_statement | grant | revoke | declare_statement | set_statement | control_statement | select_statement_single_row
sql变量定义 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

declare_statement:
     DECLARE
   | DECLARE table_def

variable_list:
     ident ','... data_type
   | variable_list ',' ident ','... data_type

set_statement:
     SET ident '=' simple_atom
   | SET SESSION AUTHORIZATION ident
   | SET SCHEMA ident
   | SET user '=' ident
   | SET ROLE ident
   | SET TIME ZONE LOCAL
   | SET TIME ZONE interval_expression

user: [ USER | SESSION_USER | CURRENT_USER ]
sql控制流 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

control_statement:
     CALL func_ref
   | CALL routine_name '(' argument_list ')'
   | while_statement
   | if_statement
   | case_statement
   | RETURN return_value

case_statement:
    CASE scalar_exp when_statement... [ ELSE procedure_statement_list ] END CASE
  | CASE when_search_statement... [ ELSE procedure_statement_list ] END CASE

when_statement: WHEN scalar_exp THEN procedure_statement_list

when_search_statement: WHEN search_condition THEN procedure_statement_list

if_statement: IF search_condition THEN procedure_statement_list if_opt_else END IF

if_opt_else:
      /* empty */
   | ELSE procedure_statement_list
   | ELSEIF search_condition THEN procedure_statement_list if_opt_else

while_statement:
    [ ident ':' ] WHILE search_condition DO procedure_statement_list END WHILE [ ident ]

return_statement: RETURN return_value

return_value:
      select_no_parens_orderby
    | search_condition
    | TABLE '(' select_no_parens_orderby ')' | NULL

SQL标准定义了三种弱一致性级别以及一致性的可串行化级别。违反可串行性的三种现象：不可重复读、读脏数据和读幻想。定义如下：

不可重复读（nonrepeatable read)。一个事务在执行过程中对同一对象读两次。第二次得到不同的值，尽管在此期间该事务并没有改变其值。

读脏数据（dirty read）。事务读由另一个尚未提交的事务所写的值。

读幻象（phantom read）。事务重新执行返回结果为满足某搜索条件的行集合的查询，发现满足条件的行集合已经改变，这是因为令一个事务最近提交了。

四种标准SQL隔离级别的定义:

隔离级别 |读脏数据 |不可重复读 |幻象
读未提交 |可能 可能 可能
读已提交 |可能 可能 可能
重复读 |可能 可能 可能
可串行化 |不可能 不可能 不可能

Comments