oracle 的常用SQL语句

1、创建表空间

create tablespace TBS

2、创建用户

create user etlhss_y identified by XXX default tablespace TBS

3、赋予权限

grant connect,resource,dba to etlhss_y

4、创建数据库

CREATE database database_name

5、创建表

CREATE TABLE 表名称(列名称1 数据类型,列名称2 数据类型,列名称3 数据类型,....)

例子：

CREATE TABLE om_test

(

serial_no number(18,0) DEFAULT 0 NOT NULL,

channel_id varchar2(50) DEFAULT ' ' ,

channel_name varchar2(100) DEFAULT ' ' ,

client_version varchar2(255) DEFAULT ' ' ,

create_time number(14,0) DEFAULT to_number(to_char(sysdate,'yyyymmddhh24miss')) ,

update_datetime number(14,0) DEFAULT to_number(to_char(sysdate,'yyyymmddhh24miss'))

) TABLESPACE TBS;

NOT NULL是表示该字段不能为空 约束。

DEFAULT 0表示默认值为 0 。

//添加主键

ALTER TABLE om_test ADD CONSTRAINT om_delivery_channel_idx1 PRIMARY KEY(serial_no);

//添加唯一索引

CREATE UNIQUE INDEX om_delivery_channel_idx2 ON om_test(channel_name ASC ,client_version ASC );

6、约束：UNIQUE

UNIQUE 约束唯一标识数据库表中的每条记录。

Id_P int NOT NULL UNIQUE,

 

每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

多个列定义唯一约束：

create table test{

。。。

CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

}

7、约束：primary key

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

Oracle：

CREATE TABLE Persons(Id_P int NOT NULL PRIMARY KEY,。。。)

MySQL：

CREATE TABLE Persons(

。。。 PRIMARY KEY,（Id_P）)

多个列定义 主键 约束：

create table test{

。。。

CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

}

SQL:

添加：

ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY(Id_P,LastName)

删除：

ALTER TABLE Persons DROP CONSTRAINT pk_PersonID

8、约束：foreign key

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

CREATE TABLE Orders(Id_O int NOT NULL,OrderNo int NOT NULL,Id_P int,PRIMARY KEY (Id_O), CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P))

SQL：

添加

ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)REFERENCES Persons(Id_P)

删除：

ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

9、约束： DEFAULT

DEFAULT 约束用于向列中插入默认值。

CREATE TABLE Persons(。。。City varchar(255) DEFAULT 'Sandnes')

SQL：

添加

ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES'

删除

ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT

10、索引：CREATE INDEX

表中创建索引，以便更加 快速高效地查询数据。

用户无法看到索引，它们只能被用来加速搜索/查询。

CREATE INDEX index_name ON table_name (column_name)

创建索引虽然大大增加了查询效率，但是 修改更新 效率会变差。

11、Drop

删除 索引 、 表 和 数据库 。

DROP INDEX index_name

DROP TABLE 表名称

DROP DATABASE 数据库名称

Mysql：

ALTER TABLE table_name DROP INDEX index_name

12、TRUNCATE 清空表数据

TRUNCATE TABLE 表名称

13、ALTER TABLE修改表结构

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

添加

ALTER TABLE table_name ADDcolumn_name datatype

删除

ALTER TABLE table_name DROP COLUMN column_name

修改

ALTER TABLE table_name ALTER COLUMN column_name datatype

14、CREATE VIEW 创建视图

视图是基于 SQL 语句的结果集的可视化的表。

数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

CREATE VIEW view_name AS SELECT column_name(s)FROM table_nameWHERE condition

例子：

CREATE VIEW [Category Sales For 1997] AS SELECT DISTINCT CategoryName,Sum(ProductSales)AS CategorySalesFROM [Product Sales for 1997]GROUP BY CategoryName

SELECT * FROM [Category Sales For 1997]

删除视图：

DROP VIEW view_name

15、条件 IS NOT NULL

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL

16、nvl()

若数据库数据为null，使用nvl()可返回0

oracle中 nvl()

SQL Server中 ISNULL()

Mysql中 IFNULL()
 

17-23 主要用于SELECT查询

 

17、Top()

TOP 子句用于规定要返回的记录的数目。

取前两条

SELECT TOP 2 * FROM Persons

Oracle

SELECT *FROM PersonsWHERE ROWNUM <= 5

Mysql

SELECT *FROM Persons LIMIT 5

取 百分之50

SELECT TOP 50 PERCENT * FROM Persons

18、Like 搜索匹配

在 WHERE 子句中搜索列中的指定模式

SELECT * FROM PersonsWHERE City LIKE 'N%'

SELECT * FROM PersonsWHERE City LIKE '%lon%'

19、通配符 搜索匹配

% 替代一个或多个字符

_ 字符列中的任何单一字符

20、in 搜索范围

IN 操作符允许我们在 WHERE 子句中规定多个值。

SELECT *FROM table_nameWHERE column_name IN (value1,value2,...)

21、BETWEEN搜索范围

操作符 BETWEEN ... AND 会选取介于 两个值之间的数据范围。这些值可以是数值、文本或者日期。

SELECT *FROM table_nameWHERE column_name BETWEEN value1 AND value2

取column_name在value1到value2之间的数值范围

22、join

用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

主键和外键

自连接

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons, OrdersWHERE Persons.Id_P = Orders.Id_P

等同于

内连接

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_PORDER BY Persons.LastName

• INNER JOIN: 如果表中有至少一个匹配，则返回行（只返回匹配的）
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行（返回匹配的和左表）
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行（返回匹配的和右表）
• FULL JOIN: 只要其中一个表中存在匹配，就返回行（返回匹配的和不匹配的）

23、UNION 并

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2

24、Select 查询

SELECT 列名称

FROM 表名称

WHERE 列 运算符 值

AND/OR 列 运算符 值

GROUP BY 列

HAVING 函数（列）

ORDER BY 列 DESC/ASC

 

ORDER BY 根据列 排序

GROUP BY 子句可以将 查询结果按照 某一列数据值进行 分组。

GROUP BY 子句通常与 统计函数联合使用

HAVING 因为where不能对函数，类似 sum（OrderPrice）一起使用。当做where使用

25、INSERT 插入

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

26、UPDATE 修改更新

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'

27、DETLLE 删除

DELETE FROM 表名称 WHERE 列名称 = 值

DELETE FROM Person WHERE LastName = 'Wilson'

28、查询 函数

1）AVG() 返回 平均值

2）COUNT()返回 总行数

3）FIRST() 返回指定的字段中第一个记录的值

4）LAST() 返回指定的字段中最后一个记录的值。

5）MAX() 返回指定的字段中的最大值

6）MIN() 返回指定的字段中的最小值

7）SUM() 返回指定的字段中值得和

8）UCASE() 返回字段的值转换为大写。

9）LCASE() 返回字段的值转换为小写。

29、分页查询

SELECT a2.*

FROM (SELECT a1.*, ROWNUM rn FROM (SELECT * FROM emp ORDER BY sal) a1

WHERE ROWNUM<=10) a2

WHERE rn>=6;

指定查询列、查询结果排序等，都只需要修改最里层的子查询即可。
 

30、clob转String

 

dbms_lob.substr(content)

 

31、获取oracle数据库时间

select to_char(sysdate ,'yyyymmdd') from dual

select to_char(sysdate ,'hh24miss') from dual

 

32、查询用户的所有表

select t.table_name,t.comments from user_tab_comments t

 

33、查询用户和表空间关系

select username,default_tablespace from dba_users;