跳到主要内容

SQL

标准​

  • ANSI
  • SQL-92
  • SQL-99

DDL​

创建表​

CREATE TABLE <table_name> (
  <column_name> <type> [column_integrity_constraint]
  [, <...>]
  [, <table_integrity_constraint>]
);

修改表​

ALTER TABLE <table_name> 
  [ADD <new_column_name> <type> [column_integrity_constraint]]
  [DROP <table_integrity_constraint_name>]
  [MODIFY <column_name> <type>];

删除表​

DROP TABLE <table_name>;

建立索引​

CREATE <([UNIQUE],[CLUSTER])> INDEX <index_name> ON <table_name>{
  <column_name> [<order>]
  [,<column_name> [<order>]]
};

删除索引​

DROP INDEX <index_name>;

创建视图​

CREATE VIEW <view_name> [(column_alias[,column_alias])]
AS <SELECT_STATEMENT>
[WITH CHECK OPTION];

删除视图​

DROP VIEW <view_name>;

note​

UNIQUE
CLUSTER
ASC
DESC
WITH CHECK OPTION
非聚簇索引，使用B+树存储，叶子节点存储行的地址
聚簇索引，使用B+树存储，叶子节点存储行本身
升序
降序
修改视图数据时，修改的数据受到创建视图时的查询的条件的约束

DML​

查询​

SELECT * | [aggregation_function | <column_name> [AS <column_alias>],...]
  FROM <table_name> [[AS <table_alias>],<table_name> [AS <table_alias>],...]
  [WHERE_STATEMENT]
  [GROUP BY <column_name> [HAVING-STATMENT]]
  [ORDER_BY-STATEMENT];

连接查询​

SELECT * | [aggregation_function <column_name> [AS <column_alias>],...]
  FROM <table_name> [[AS <table_alias>],<table_name> [AS <table_alias>],...]
  [WHERE_REFERENCE_STATEMENT]
  [GROUP BY <column_name> [HAVING_STATMENT]]
  [ORDER_BY_STATEMENT];

子查询​

SELECT * | [aggregation_function <column_name> [AS <column_alias>],...]
  FROM (SELECT_STATEMENT);

插入​

INSERT INTO <table_name> [(<column_name>[,...])]
VALUES(const,[...]);

INSERT INTO <table_name> [(<column_name>[,...])]
SELECT_STATEMENT;

插入​

INSERT INTO <table_name> [(<column_name>[,...])]
VALUES(const,[...]);

INSERT INTO <table_name> [(<column_name>[,...])]
SELECT_STATEMENT;

删除​

DELETE FROM <table_name>
  [WHERE_STATEMENT];

修改​

UPDATE <table_name> SET <column_name> = <const>[,<column_name> = <const>,...]
  [WHERE_STATEMENT];

聚合函数​

COUNT([DISTINCT | ALL] <* | column_name>)
SUM([DISTINCT | ALL] <* | column_name>)
AVG([DISTINCT | ALL] <* | column_name>)
MAX([DISTINCT | ALL] <* | column_name>)
MIN([DISTINCT | ALL] <* | column_name>)

WHERE​

--- condition
[NOT] <sth> <[NOT] LIKE | [NOT] BETWEEN | < | <= | >= | > | = | != >  <ath>
<sth> IS [NOT] NULL
<sth> [NOT] IN <collection> | (<SELECT_STATEMENT>)
<sth> < | <= | >= | > | = | != >  [<ANY> | <ALL>] (<SELECT_STATEMENT>)

--- logical 
<AND | OR> <condition> [logical ...]

--- reference
<table_name.column_name> = <table_name.column_name>

--- WHERE
WHERE <condition> [logical ...];

--- JOIN
WHERE <reference> [logical ...];

HAVING​

--- aggregation extension for WHERE_STATEMENT

授权​

GRANT <permission>[,<permission>,...]
 [ON <obj-type> <obj-name>]
 TO PUBLIC | <user>[,<user>,...]
 [WITH GRANT OPTION];

撤权​

REVOKE <permission>[,<permission>]
  [ON <obj-type> <obj-name>]
  FROM <user>[,<user>,...];

note​

--- DATABASE
CREATETAB;
--- TABLE
ALTER,
INDEX
  --- COLUMN / VIEW
  SELECT,
  INSERT,
  UPDATE,
  DELETE,
  ALL PRIVILEGES
_
%
DISTINCT
COUNT(*)
COUNT<column>
obj-type
obj-name
PUBLIC
WITH GRANT OPTION
用于LIKE子句字符串匹配，表示任意一个字符
用于LIKE子句字符串匹配，表示任意一个字符串
用于排除重复的行
计算结果集行数
计算列的非空行个数
TABLE | DATABASE
TABLE_NAME | DATABASE MAME
表示全体用户
被授权用户拥有授权的能力

嵌入式​

EXEC SQL <SQL_STATEMENT> [END-EXEC];

SQL通信区​

向主语言传递SQL运行情况。

共享变量​

  • 查询结构传入变量。
  • 变量传入SQL。
DECLARE var1;
DECLARE var2;
DECLARE var3;
DECLARE var4;
SELECT c1,c2,c3
  INTO :var1,:var2,:var3
  FROM test
  WHERE c4=:var4;

游标​

使用游标遍历结果集。