一、SQL语句
1、修改表格的字段(ALTER TABLE)
ALTER TABLE TABLE_NAME
[MODFIY|ADD|DROP]
[COLUMN COLUMN_NAME][DATATYPE|NULL NOT NULL][RESTRICT|CASCADE]
[ADD|DROP] [CONSTRAINT CONSTRAINT_NAME]
2、把事务保存到数据库(COMMIT)
COMMIT [TRANSACTION]
3、创建表格上的索引(CREATE INDEX)
CREATE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME)
4、创建一个数据库角色，它可以被分配一定的系统权限和对象权限(CREATE ROLE)
CREATE ROLE ROLE_NAME
[WITH ADMIN [CURRENT_USER|CURRENT_ROLE]]
5、创建数据库的一个表格(CREATE TABLE)
CREATE TABLE TABLE_NAME
(COLUMN1 DATA_TYPE [NULL|NOT NULL],
COLUMN2 DATA_TYPE [NULL|NOT NULL])
6、基于数据库的一个表创建另一个表(CREATE TABLE AS)
CREATE TABLE AS
SELECT COLUMN1,COLUMN2,...
FROM TABLE_NAME
[WHERE CONDITIONS]
[GROUP BY COLUMN1,COLUMN2,...]
[HAVING CONDITIONS]
7、创建自定义类型，可以用于定义表里的字段(CREATE TYPE)
CREATE TYPE typename AS OBJECT
(COLUMN1 DATA_TYPE [NULL|NOT NULL],
COLUMN2 DATA_TYPE [NULL|NOT NULL])
8、在数据库中创建一个用户账户(CREATE USER)
CREATE USER username IDENTIFIED BY password
9、创建表格的视图(CREATE VIEW)
CREATE VIEW AS
SELECT COLUMN1,COLUMN2,...
FROM TABLE_NAME
[WHERE CONDITIONS]
[GROUP BY COLUMN1,COLUMN2,...]
[HAVING CONDITIONS]
10、从表格里删除记录(DELETE FROM)
DELETE FROM TABLE_NAME [WHERE CONDITIONS]
11、删除表格里的索引(DROP INDEX)
DROP INDEX INDEX_NAME
12、从数据库里删除表(DROP TABLE)
DROP TABLE TABLE_NAME
13、从数据库里删除用户账户(DROP USER)
DROP USER user1 [,user2,..]
14、删除表的视图(DROP VIEW)
DROP VIEW VIEW_NAME
15、向用户授予权限(GRANT)
GRANT PRIVILEGE1,PRIVILEGE2,... TO USER_NAME
16、向表里插入新数据(INSERT INTO)
INSERT INTO TABLE_NAME [COLUMN1,COLUMN2,...]
VALUES('VALUE1','VALUE2',...)
17、基于一个表向另一个表插入新记录(INSERT INTO)
INSERT INTO TABLE_NAME
SELECT COLUMN1,COLUMN2,...
FROM TABLE_NAME
[WHERE CONDITIONS]
18、撤销用户的权限(REVOKE)
REVOKE PRIVILEGE1,PRIVILEGE2,... FROM USER_NAME
19、撤销数据库事务(ROLLBACK)
ROLLBACK [TO SAVEPOINT_NAME]
20、创建事务保存点以备回退(SAVEPOINT)
SAVEPOINT SAVEPOINT_NAME
21、从一个或多个表返回数据，用于创建查询(SELECT)
SELECT [DISTINCT] COLUMN1,COLUMN2,...
FROM TABLE1,TABLE2,...
[WHERE CONDITIONS]
[GROUP BY COLUMN1,COLUMN2,...]
[HAVING CONDITIONS]
[ORDER BY COLUMN1,COLUMN2,...]
22、更新表里的已有记录(UPDATE)
UPDATE TABLE_NAME
SET COLUMN1='VALUE1',
COLUMN2='VALUE2',...
[WHERE CONDITIONS]
二、SQL子句
1、定义要在查询输出里显示的字段(SELECT子句)
SELECT *
SELECT COLUMN1,COLUMN2,..
SELECT DISTINCT(COLUMN1)
SELECT COUNT(*)
2、定义要获取数据的表(FROM子句)
FROM TABLE1,TABLE2,...
3、定义查询里限制返回数据的条件(WHERE子句)
WHERE COLUMN1='VALUE1'
AND COLUMN2='VALUE2'
...
WHERE COLUMN1='VALUE1'
OR COLUMN2='VALUE2'
...
WHERE COLUMN IN ('VALUE1' [,'VALUE2'])
4、排序操作的一种形式，用于把输出划分为逻辑组(GROUP BY子句)
GROUP BY GROUP_COLUMN1,GROUP_COLUMN2,...
5、类似于WHERE子句，用于在GROUP BY子句里设置条件(HAVING子句)
HAVING GROUP_COLUMN1='VALUE1'
AND GROUP_COLUMN2='VALUE2'
...
6、用于对查询结果进行排序(ORDER BY子句)
ORDER BY COLUMN1,COLUMN2,...
ORDER BY 1,2,... 