Oracle50条语句突破技术边界

创建表

1、创建表：CREATE TABLE table_name (column1 datatype, column2 datatype, …);

2、添加列：ALTER TABLE table_name ADD column_name datatype;

3、删除列：ALTER TABLE table_name DROP COLUMN column_name;

4、修改列：ALTER TABLE table_name MODIFY column_name datatype;

5、重命名表：RENAME table_name TO new_table_name;

6、删除表：DROP TABLE table_name;

插入数据

7、插入单行数据：INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

8、插入多行数据：INSERT ALL INTO table_name (column1, column2, …) VALUES (value1, value2, …) INTO temp SELECT column1, column2, … FROM another_table;

9、从其他表中复制数据：INSERT INTO table_name (column1, column2, …) SELECT column1, column2, … FROM another_table;

查询数据

10、查询所有数据：SELECT * FROM table_name;

11、查询指定列：SELECT column1, column2, … FROM table_name;

12、查询满足条件的数据：SELECT * FROM table_name WHERE condition;

13、排序查询结果：SELECT * FROM table_name ORDER BY column1 ASC/DESC;

14、分组查询：SELECT column1, column2, … FROM table_name GROUP BY column1;

15、连接查询：SELECT * FROM table1 INNER/LEFT/RIGHT/FULL JOIN table2 ON table1.column = table2.column;

更新数据

16、更新单行数据：UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;

17、更新多行数据：UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;

18、使用子查询更新数据：UPDATE table_name SET column1 = (SELECT column1 FROM another_table WHERE condition), column2 = (SELECT column2 FROM another_table WHERE condition) WHERE condition;

删除数据

19、删除单行数据：DELETE FROM table_name WHERE condition;

20、删除多行数据：DELETE FROM table_name WHERE condition;

21、删除表中所有数据：DELETE FROM table_name;

索引和约束

22、创建索引：CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2, …);

23、删除索引：DROP INDEX index_name;

24、主键约束：ALTER TABLE table_name ADD PRIMARY KEY (column1);

25、外键约束：ALTER TABLE table_name ADD FOREIGN KEY (column1) REFERENCES another_table(column2);

26、NOT NULL约束：ALTER TABLE table_name ALTER COLUMN column1 NOT NULL;

27、CHECK约束：ALTER TABLE table_name ADD CONSTRAINT check_constraint check_condition;

事务处理

28、BEGIN:开始事务处理；

29、SAVEPOINT savepoint_name:设置保存点；

30、ROLBACK TO savepoint_name:回滚到保存点；

31、COMMIT:提交事务；

32、ROLLBACK:回滚事务。

存储过程和函数

33、创建存储过程：CREATE PROCEDURE procedure_name (parameter1 datatype, parameter2 datatype, …) IS/AS BEGIN … END;

34、调用存储过程：CALL procedure_name (value1, value2, …);

35、创建函数：CREATE [OR REPLACE] function_name (parameter1 datatype, parameter2 datatype, …) RETURN datatype IS/AS BEGIN … RETURN result; END;

36、调用函数：result := function_name (value1, value2, …);

触发器

37、创建触发器：CREATE [OR REPLACE] [TRIGGER] trigger_name {BEFORE |AFTER} {INSERT |UPDATE |DELETE} ON table_name FOR EACH row [WHEN condition] BEGIN … END;

38、删除触发器：DROP [TRIGGER] trigger_name;

39、禁用触发器：DISABLE [TRIGGER] trigger_name;

40、启用触发器：ENABLE [TRIGGER] trigger_name;

视图和物化视图

41、创建视图：CREATE [OR REPLACE] [VIEW] view_name AS select_statement;

42、删除视图：DROP [VIEW] view_name;

43、创建物化视图：CREATE MATERIALIZED [VIEW] view_name [WITH NOCACHE] AS select_statement;

44、删除物化视图：DROP MATERIALIZED [VIEW] view_name;

45、刷新物化视图：REFRESH MATERIALIZED [VIEW] view_name [COMPLETE|FORCE];