常用SQL命令

一、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,...