SQL语法分析和SQL解释实现.doc

《SQL语法分析和SQL解释实现.doc》由会员分享，可在线阅读，更多相关《SQL语法分析和SQL解释实现.doc（34页珍藏版）》请在沃文网上搜索。

1、SQL语法现状SQL语言的标准也经历了一系列的发展，大致有SQL86, SQL89, SQL92, SQL/CLI, SQL/PSM, SQL99几个过程，其中大部分商业数据库目前都支持SQL92。另外，商业数据库使用的SQL大多有自己的扩展。因此严格来说，SQL语法没有一个完全统一的标准。实现范围和方法即便是SQL92，完整的分析也是不可能做到的，目前我们仅能就一些基本的不太复杂的语法做一些有限度的分析。考虑到常用数据库的特点，可以以后增加某种数据库的特性支持。实现上，由于SQL语句的复杂性，无法采取完整分析的方法，因此考虑采用经典句型模式匹配加上简单分析的方法来提取一点基本语法单元。这样分

2、析出来的结果应该可以满足设计要求的。另外，分析程序不能够完全识别语法错误。能够识别词法错误以及基本句型错误。由于分析程序仅仅支持一些常见基本句型，因此一些提示(HINT)，选项(OPTION)，以及排序方式等细节将被有意忽略。而且复杂的句型有可能被误识别，导致解释错误。代码实现以状态图为准。部分SQL语法描述以下部分基于SQL Server 2000, Oracle 10g, MySQL 5, Sybase ASE 12.5等产品手册。语法描述的格式遵从各自的文档习惯。SQL Server 2000SQL Server联机丛书Transact-SQL参考Oracle 10gSQL Refere

3、nce- SQL StatementsMySQLMySQL Manual- SQL Statement SyntaxSybase Sybase Adaptive Server Enterprise参考手册第二卷：命令数据定义语言创建数据库语法SQL ServerCREATE DATABASE database_name ON ,.n , ,.n LOG ON ,.n COLLATE collation_name FOR LOAD | FOR ATTACH MySqlCREATE DATABASE IF NOT EXISTS db_namecreate_specification , creat

4、e_specification .OracleCREATE DATABASE database USER SYS IDENTIFIED BY password | USER SYSTEM IDENTIFIED BY password | CONTROLFILE REUSE | MAXDATAFILES integer | MAXINSTANCES integer | CHARACTER SET charset | NATIONAL CHARACTER SET charset | SET DEFAULT BIGFILE | SMALLFILE TABLESPACE | database_logg

5、ing_clauses | tablespace_clauses | set_time_zone_clause . ;Sybasecreate database database_nameon default | database_device = size, database_device = size.log on database_device = size, database_device = size.with override | default_location = pathnamefor load | proxy_update目前支持CREATE DATABASE db_nam

6、e 忽略不支持不支持Mysql IF NOT EXIST 语法；不支持Oracle CREATE DATABASE db_name 语法；状态图创建表语法SQL ServerCREATE TABLE database_name. owner .| owner. table_name ( | column_name AS computed_column_expression | := CONSTRAINT constraint_name | PRIMARY KEY | UNIQUE ,.n ) ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEF

7、AULT MySqlCREATE TEMPORARY TABLE IF NOT EXISTS tbl_name (create_definition,.) table_options select_statementOr: CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name ( LIKE old_tbl_name );OracleCREATE GLOBAL TEMPORARY TABLE schema. table (relational_properties) ON COMMIT DELETE | PRESERVE ROWS physical_prop

8、erties table_properties ;或者CREATE GLOBAL TEMPORARY TABLE schema. table OF schema. object_type object_table_substitution (object_properties) ON COMMIT DELETE | PRESERVE ROWS OID_clause OID_index_clause physical_properties table_properties ;或者CREATE TABLE GLOBAL TEMPORARY TABLE schema. table OF XMLTYP

9、E (oject_properties) XMLTYPE XMLType_storage XMLSchema_spec ON COMMIT DELETE | PRESERVE ROWS OID_clause OID_index_clause physical_properties table_properties ;Sybasecreate table database.owner.table_name(column_name datatypedefault constant_expression | user | nullidentity | null | not nulloff row |

10、 in row| constraint constraint_nameunique | primary keyclustered | nonclustered asc | descwith fillfactor = pct| max_rows_per_page = num_rows , reservepagegap = num_pages on segment_name| references database.owner.ref_table(ref_column)| check (search_con ion).| constraint constraint_nameunique | pri

11、mary keyclustered | nonclustered(column_name asc | desc, column_name asc | desc.)with fillfactor = pct| max_rows_per_page = num_rows ,reservepagegap = num_pages on segment_name|foreign key (column_name , column_name.)references database.owner.ref_table(ref_column , ref_column.)| check (search_con io

12、n) . , next_column | next_constraint.)lock datarows | datapages | allpages with max_rows_per_page = num_rows ,exp_row_size = num_bytes ,reservepagegap = num_pages on segment_name external table at pathname SQL92CREATE GLOBAL | LOCAL TEMPORARY TABLE ON COMMIT DELETE | PRESERVE ROWS 目前支持CREATE GLOBAL

13、| LOCAL TEMPORARY TABLE table_exp 忽略SQL Server / Sybase table_exp := database_name. owner .| owner. table_nameOracle table_exp := schema. table不支持不支持Mysql CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name状态图创建视图语法SQL ServerCREATE VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK

14、OPTION MySql OracleCREATE OR REPLACE NO FORCE VIEW schema. view (alias inline_constraint inline_constraint . | out_of_line_constraint, alias inline_constraint inline_constraint . | out_of_line_constraint.)| object_view_clause| XMLType_view_clauseAS subquery subquery_restriction_clause ;Sybasecreate

15、view owner.view_name(column_name , column_name.)as select distinct select_statementwith check optionSQL92CREATE VIEW AS WITH CHECK OPTION 目前支持CREATE ! VIEW view_exp 忽略不支持状态图部分DDL语法汇总状态图CREATE ! PROC | PROCEDURE | INDEX | TRIGGER obj_exp 忽略ALTER ! DATABASE | TABLE | VIEW | PROC | PROCEDURE | INDEX |

16、TRIGGER obj_exp 忽略DROP DATABASE | VIEW | PROC | PROCEDURE | INDEX | TRIGGER obj_exp,n 忽略或者DROP TABLE table_exp 忽略数据操作语言SELECT语法SQL Server SELECT select_list INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DE

17、SC MySql SELECTALL | DISTINCT | DISTINCTROW HIGH_PRIORITYSTRAIGHT_JOINSQL_SMALL_RESULT SQL_BIG_RESULT SQL_BUFFER_RESULTSQL_CACHE | SQL_NO_CACHE SQL_CALC_FOUND_ROWSselect_expr,.INTO OUTFILE file_name export_options| INTO DUMPFILE file_nameFROM table_referencesWHERE where_definitionGROUP BY col_name |

18、 expr | positionASC | DESC, . WITH ROLLUPHAVING where_definitionORDER BY col_name | expr | positionASC | DESC ,.LIMIT offset, row_count | row_count OFFSET offsetPROCEDURE procedure_name(argument_list)FOR UPDATE | LOCK IN SHARE MODEOracle SELECT hint DISTINCT | UNIQUE | ALLselect_listFROM table_refer

19、ence, table_reference . where_clause hierarchical_query_clause group_by_clause HAVING condition model_clause UNION ALL | INTERSECT| MINUS(subquery) order_by_clause Sybase select all | distinct select_listinto_clausefrom_clausewhere_clausehaving_clauseorder_by_clausecompute_clauseread_only_clauseisol

20、ation_clausebrowse_clauseplan_clause目前支持 SELECT select_list INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression 不支持 不支持更详尽的子句，如INTO子句，FROM子句等所有子句。 不支持UNION, EXCEPT, INTERSECTION状态图 INSERT语法 SQL Server INSERT INTO tabl

21、e_name WITH ( .n ) | view_name | rowset_function_limited ( column_list ) VALUES ( DEFAULT | NULL | expression ,.n ) | derived_table | execute_statement | DEFAULT VALUESMySql INSERT LOW_PRIORITY | DELAYED IGNORE INTO tbl_name (col_name,.) VALUES (expr | DEFAULT,.),(.),. ON DUPLICATE KEY UPDATE col_na

22、me=expr, . Or: INSERT LOW_PRIORITY | DELAYED IGNORE INTO tbl_name SET col_name=expr | DEFAULT, . ON DUPLICATE KEY UPDATE col_name=expr, . Or: INSERT LOW_PRIORITY | DELAYED IGNORE INTO tbl_name (col_name,.)SELECT .Oracle INSERT hint single_table_insert | multi_table_insert ;insert_into_clause values_

23、clause returning_clause | subquery ALL insert_into_clause values_clause insert_into_clause values_clause .| conditional_insert_clausesubquerySybase insert intodatabase.owner.table_name|view_name(column_list)values (expression , expression.)|select_statement plan abstract plan 目前支持 INSERT 忽略 INTO tab

24、le_exp | view_exp 忽略或者INSERT table_exp | view_exp 忽略注意：由于不能分析HINT，因此INSERT语法被分离成两种可能，如果不能匹配第一种，则假设为第二种，这有可能导致错误解释。不支持 不支持 INSERT HINT table_exp 忽略不支持 Oracle的多表insert不支持 更详尽的子句状态图 UPDATE语法 SQL Server UPDATE table_name WITH ( .n ) | view_name | rowset_function_limited SET column_name = expression | DE

25、FAULT | NULL | variable = expression | variable = column = expression ,.n FROM ,.n WHERE | WHERE CURRENT OF GLOBAL cursor_name | cursor_variable_name OPTION ( ,.n ) MySql UPDATE LOW_PRIORITY IGNORE tbl_name SET col_name1=expr1 , col_name2=expr2 . WHERE where_definition ORDER BY . LIMIT row_countMult

26、iple-table syntax: UPDATE LOW_PRIORITY IGNORE tbl_name , tbl_name . SET col_name1=expr1 , col_name2=expr2 . WHERE where_definitionOracle UPDATE hint dml_table_expression_clause | ONLY (dml_table_expression_clause) t_alias update_set_clause where_clause returning_clause ;Sybase update database.owner.

27、table_name | view_nameset database.owner.table_name.|view_name.column_name1 =expression1|NULL|(select_statement) |variable_name1 =expression1|NULL|(select_statement), column_name2 =expression2|NULL|(select_statement).|, variable_name2 =expression2|NULL|(select_statement).from database.owner.view_nam

28、e readpast|table_name readpast(index index_name | table_name prefetch size lru|mru),database.owner.view_name readpast|table_name readpast(index index_name | table_name prefetch size lru|mru).where search_conditionsplan abstract planupdate database.owner.table_name | view_nameset database.owner.table

29、_name.|view_name.column_name1 =expression1|NULL|(select_statement) |variable_name1 =expression1|NULL|(select_statement), column_name2 =expression2|NULL|(select_statement). |, variable_name2 =expression2|NULL|(select_statement).where current of cursor_name目前支持 UPDATE table_view_exp 忽略 不支持 不支持 hint 不支

30、持 MySQL 的多表 不支持 Oracle 的更复杂的语法 不支持 更详尽的子句 不支持 其他所有不符合此表达式的语法状态图 DELETE语法 SQL Server DELETE FROM table_name WITH ( .n ) | view_name | rowset_function_limited FROM ,.n WHERE | CURRENT OF GLOBAL cursor_name | cursor_variable_name OPTION ( ,.n ) MySql DELETE LOW_PRIORITY QUICK IGNORE FROM tbl_name WHERE

31、 where_definition ORDER BY . LIMIT row_countMultiple-table syntax: DELETE LOW_PRIORITY QUICK IGNORE tbl_name.* , tbl_name.* . FROM table_references WHERE where_definitionOr: DELETE LOW_PRIORITY QUICK IGNORE FROM tbl_name.* , tbl_name.* . USING table_references WHERE where_definitionOracle DELETE hin

32、t FROM dml_table_expression_clause | ONLY (dml_table_expression_clause) t_alias where_clause returning_clause ;Sybase delete fromdatabase.owner.view_name|table_namewhere search_conditionsplan abstract plandelete database.owner.table_name | view_namefrom database.owner.view_name readpast|table_name r

33、eadpast(index index_name | table_name prefetch size lru|mru), database.owner.view_name readpast|table_name readpast(index index_name | table_name prefetch size lru|mru) .where search_conditions plan abstract plandelete fromdatabase.owner.table_name|view_namewhere current of cursor_name目前支持 DELETE hi

34、nt FROM table_exp 忽略或者 DELETE table_exp 忽略注意：由于不能分析HINT，因此DELETE语法被分离成两种可能，如果不能匹配第一种，则假设为第二种，这有可能导致错误解释。不支持不支持 DELETE HINT table_exp 忽略不支持 MySQL 的多表 不支持 Oracle 的更复杂的语法 不支持 更详尽的子句 不支持 其他所有不符合此表达式的语法状态图 部分其它常用语法USE语法 SQL Server USE database MySql USE db_nameOracle 不支持Sybase use database_name目前支持 USE d

35、b_name不支持 状态图 GRANT语法 SQL Server语句权限：GRANT ALL | statement ,.n TO security_account ,.n 对象权限：GRANT ALL PRIVILEGES | permission ,.n ( column ,.n ) ON table | view | ON table | view ( column ,.n ) | ON stored_procedure | extended_procedure | ON user_defined_function TO security_account ,.n WITH GRANT O

36、PTION AS group | role MySql GRANT priv_type (column_list) , priv_type (column_list) . ON tbl_name | * | *.* | db_name.* TO user IDENTIFIED BY PASSWORD password , user IDENTIFIED BY PASSWORD password . REQUIRE NONE | SSL| X509 CIPHER cipher AND ISSUER issuer AND SUBJECT subject WITH GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR countOracle GRANT grant_system_privileges | grant_object_privileges ;grant_system_privileges := sy