频道栏目
首页 > 数据库 > Oracle > 正文
Oracle的常用SQL语句(建议收藏)
2017-11-24 10:09:25         来源:heshushun的博客  
收藏   我要投稿

1、创建表空间

create tablespace TBS

2、创建用户

create user etlhss_y identified by test123 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_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_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 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;

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

点击复制链接 与好友分享!回本站首页
上一篇:Oracle EBS程序重置密码教程
下一篇:oracle日期格式转换及日期截取,按月统计,按日统计详解
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站