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_idx2ON 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_PersonIDPRIMARY KEY(Id_P,LastName)
删除：
ALTER TABLE PersonsDROP 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_PerOrdersFOREIGN 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 PersonsALTER COLUMN CitySETDEFAULT 'SANDNES'
删除
ALTER TABLE PersonsALTERCOLUMN CityDROP 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_nameALTER 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 PersonsWHERE AddressIS 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 PersonsLIMIT 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_nameBETWEEN value1AND 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 PersonsINNER JOIN OrdersON 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;