Oracle实操

建表

CREATE TABLE ucr_crm1.xdd_test10
( name VARCHAR2(30),
serial_number VARCHAR2(30),
fenlei VARCHAR2(30),
xinyongdu VARCHAR2(30),
CONSTRAINT pk_staff4 PRIMARY KEY(serial_number)
)

插入数据

【单行插入】

INSERT INTO customer（First_Name,Last_Name,Birth_Date,AGE）VALUES（’jhon’,’smith’,to_date(‘1999-09-05’,’yyyy-mm-dd’),25)

【多行插入】

INSERT ALL INTO CUSTOMER（FIRST_NAME, LAST_NAME, BIRTH_DATE, AGE）VALUES（ ‘jhon’, ‘smith’, TO_DATE
(‘1999-09-05’, ‘yyyy-mm-dd’), 25) INTO CUSTOMER（FIRST_NAME, LAST_NAME, BIRTH_DATE, AGE）VALUES（ ‘jason’, ‘hobby’, TO_DATE
(‘1989-01-25’, ‘yyyy-mm-dd’), 35)
SELECT * FROM DUAL（没有实际意义，但是不可缺省）

变更表名：

ALTER TABLE customer RENAME TO customers

【变更列名】：

ALTER TABLE t_staff2 RENAME COLUMN is_disabled TO staff_status;

【添加字段】

ALTER TABLE t_staff2 ADD(is_disabled NUMBER(1) DEFAULT(0)); – DEFAULT 的括号可以省略

ALTER TABLE customer ADD (gender VARCHAR2(30), age integer default 22 not null)

【修改字段】（执行之后无法恢复）

ALTER table customer modify Addr char(30) #将 “Addr” 栏位的资料种类改为 char(30)
ALTER TABLE customer DROP PRIMARY KEY #删除主键
ALTER TABLE ucr_crm1.xdd_test6 modify serial_number_2 NULL; #可为空
ALTER TABLE t_staff2 MODIFY(staff_status DEFAULT(‘1’)); #修改默认值

ALTER TABLE t_staff2 DROP(height,weight); #删除字段

备份表

CREATE TABLE shohin AS SELECT * FROM customer –备份到新表

INSERT INTO shohin SELECT * FROM customer –备份到已有表

【备注】insert语句的select语句中，可以使用where子句或者group by子句等任何sql语法。（但是使用order by子句并不会产生任何效果）

删除行记录

DELETE FROM customer WHERE first_name = ‘jhon’
如果想要删除的数据表和另外一张表中存在外键约束的条件，那么当要删除其中的主表时，需要在删除语句中使用CASCADE CONSTRAINTS。
DROP TABLE table_name CASCADE CONSTRAINTS

更新数据

UPDATE shohin SET age = 26 WHERE FIRST_name = ‘jhon’
列检查约束（CONSTRAINT后跟定义的约束名称，如不需要定义，则可去掉CONSTRAINT）

ALTER TABLE customer ADD CHECK (gender LIKE ‘[MF]’)

如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，请使用下面的 SQL 语法：

ALTER TABLE Persons ADD CONSTRAINT chkPerson CHECK (IdP>0 AND City=’Sandnes’)
加主键

ALTER TABLE OrderItems ADD CONSTRAINT PKOrderItems PRIMARY KEY (ordernum, order_item);

ALTER TABLE Customer ADD PRIMARY KEY (SID);
CREATE TABLE Customer (SID integer PRIMARY KEY, LastName varchar(30), FirstName varchar(30));

加外键

1、禁止删除，也是 Oracle 默认行为。
2、参照主表把从表中对应的数据一同删除。适合强耦合关系。
ALTER TABLE t_field_enum ADD CONSTRAINT fk_field_code FOREIGN KEY(field_code) REFERENCES t_field(field_code) ON DELETE CASCADE;
3、参照主表把从表中对应的数据设置为空。适合弱耦合关系。
ALTER TABLE t_field_enum ADD CONSTRAINT fk_field_code FOREIGN KEY(field_code) REFERENCES t_field(field_code) ON DELETE SET NULL;
ALTER TABLE OrderItems ADD CONSTRAINT FKOrderItemsOrders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
CREATE TABLE ORDERS (OrderID integer primary key, OrderDate date, Customer_SID integer references CUSTOMER(SID), Amount double);
举例来说，假设我们有两个表格：一个 CUSTOMER 表格，里面记录了所有顾客的资料； 另一个 ORDERS 表格，里面记录了所有顾客订购的资料。在这里的一个限制，就是所有的 订购资料中的顾客，都一定是要跟在 CUSTOMER 表格中存在。在这里，我们就会在 ORDERS 表格中设定一个外来键，而这个外来键是指向 CUSTOMER 表格中的主键。这样 一来，我们就可以确定所有在 ORDERS 表格中的顾客都存在 CUSTOMER 表格中。换句 话说，ORDERS 表格之中，不能有任何顾客是不存在于 CUSTOMER 表格中的资料。

视图

语法：create or replace VIEW 视图名称 视图中字段名 as select语句
备注：中文字段名称必须用双引号
授权：grant create any view to scott

CREATE OR REPLACE VIEW shohin_view (“姓名”，”年龄”) AS SELECT trim(first_name)||’ ’ ||trim(last_name),birth_date FROM shohin ORDER BY first_name DESC [with check option]
DROP VIEW shohin_view
在视图中删除一条数据记录需要满足的基本原则为：定义的视图中不能含有distinct关键字、group by子句以及集合函数。
create VIEW AvgTankaByBunrui AS
SELECT SHOHIN_ID,
SHOHIN_MEI,
SHOHIN_BUNRUI,
HANBAI_TANKA,
(SELECT AVG(HANBAI_TANKA) AS AVG_HANBAI_TANKA
FROM SHOHIN S1
WHERE S1.SHOHIN_BUNRUI = S2.SHOHIN_BUNRUI
GROUP BY SHOHIN_BUNRUI)
FROM SHOHIN S2

建表语句中加入注释

create table scott.DM_IND_ALGORITHM_INFO
(
algorithm_desc VARCHAR2(200),
algorithm_beanid VARCHAR2(50),
algorithm_type VARCHAR2(1),
algorithm_name_cn VARCHAR2(100),
algorithm_name VARCHAR2(100),
uu_id VARCHAR2(32) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 160K
next 32K
minextents 1
maxextents unlimited
);
comment on table scott.DM_IND_ALGORITHM_INFO
is ‘指标算法表’;
comment on column scott.DM_IND_ALGORITHM_INFO.uu_id
is ‘算法唯一标识’;

索引

索引的命名并没有一个固定的方式。通常会用的方式是在名称前加一个字首，例如”IDX_”

在表CUSTOMER的Last_Name 这个栏位上建一个索引：

CREATE INDEX IDX_CUSTOMER_LAST_NAME on CUSTOMER (Last_Name)

在 City 及 Country 这两个栏位上建一个索引：

CREATE INDEX IDX_CUSTOMER_LOCATION on CUSTOMER (City, Country)
CASE[详细示例见《SQL语句教程43页》]
CASE store_name

WHEN ‘Los Angeles’ THEN Sales * 2

WHEN ‘San Diego’ THEN Sales * 1.5

ELSE Sales

END “New Sales” —END后边为新字段名称