常用SQL语句速查
1.ALTER TABLE
更新现有表的结构
ALTER TABLE tablename
(
ADD|DROP column datatype [NULL|NOT NUILL] [CONSTRAINTS],
ADD|DROP column datatype [NULL|NOT NUILL] [CONSTRAINTS],
...
);
2.COMMIT
提交事务给DBMS
COMMIT [TRANSACTION];
3.CREATE INDEX
创建一个或多个索引
CREATE INDEX indexname
ON tablename (column, ...);
4.CREATE PROCEDURE
创建存储过程,Oracle语法有所不同
CREATE PROCEDURE procedurename [parameters] [options]
AS
SQL statement;
5.CREATE TABLE
建表
CREATE TABLE tablenmae
(
column datatype [NULL|NOT NULL] [CONSTRAINTS],
column datatype [NULL|NOT NULL] [CONSTRAINTS],
...
);
6.CREATE VIEW
创建视图
CREATE VIEW viewname AS
SELECT columns, ...
FROM tables, ...
[WHERE ...]
[GROUP BY ...]
[HAVING ...];
7.DELETE
从表中删除一行或多行,针对表数据
DELETE FROM tablename
[WHERE ...];
8.DROP
丢弃数据库对象,适用表,视图,存储过程etc
DROP INDEX|PROCEDURE|TABLE|VIEW
indexname|procedurename|tablename|viewname;
9.INSERT
向表中插入数据
INSERT INTO tablename [(columns, ...)]
VALUES(values, ...);
10.INSERT SELECT
输出重定向
INSERT INTO tablename [(columns, ...)]
SELECT columns, ... FROM tablename, ...
[WHERE ...];
11.ROLLBACK
事务回滚
ROLLBACK [TO savepointname];
或者:
ROLLBACK TRANSACTION;
12.SELECT
n表检索数据
SELECT columnnmae, ...
FROM tablename, ...
[WHERE ...]
[UNION ...]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...];
13.UPDATE
更新表中数据
UPDATE tablename
SET columnname = value, ...
[WHERE ...];
网友评论