Oracle 表操作
1、查看用户所拥有的对象，以及有关表的信息
Schema 模式
Oracle是多用户系统，每个用户都有一个schema，创建完用户后，这个用户就会自动拥有一个schema，schema就是一个用户所拥有的全部对象的集合，schema名与用户名相同
User_object 这个表记录用户的对象
select * from user_objects
SELECT u.object_name,u.object_type FROM user_objects u
当创建一个表的时候，会登记到这个表中。
--获得有关表的信息
user_tables 是当前用户所拥有的表
all_tables 是所有用户的所有表
dba_tables 是具有DBA权限的用户所拥有的表
--对表执行 describe 命令。
--查询user_tables
SELECT * FROM user_tables;
SELECT * FROM all_tables;
--获取表的列的信息
SELECT * FROM user_tab_columns;
SELECT count(*) FROM all_tab_columns;
SELECT column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'PRODUCTS';
2、创建表
创建联合主键的表
create table sc(
sno integer,
cno integer,
scgrade integer,
constraint con_pk primary key(sno,cno)
);
创建一个表，需要有创建表的权限，有表空间
常用数据类型
Varchar2（30） 可变长度的字符串
Varchar
Char（size）固定长度的字符串
Number（size） 数字
Date 类型
1、
create table emp(
emp_id number(20) primary key,
emp_name varchar2(20) not null,
emp_bir date
);
select * from emp;
查看表信息
SELECT u.object_name,u.object_type FROM user_objects u
SELECT column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'EMP';
插入数据
select * from emp;
insert into emp values(1,'ddddd',date'1990-01-01');
insert into emp values(2,'ddddd',null);
commit;
主键约束
insert into emp values(2,'ddddd',null)
数据类型不对
insert into emp values(4,'ddddd','19200101')
2、创建含有缺省值的列
drop table emp;
select * from emp;
create table emp(
emp_id number(20) primary key,
emp_name varchar2(20) not null,
emp_bir date default sysdate
);
select * from emp;
insert into emp(emp_id,emp_name) values(1,'liudehua');
3、修改表
--alter table
/*
添加、修改或删除列
添加或删除约束
启用或禁用约束
*/
1、添加列
ALTER TABLE emp ADD emp_height INTEGER;
ALTER TABLE emp_new ADD emp_endate DATE DEFAULT SYSDATE NOT NULL;
2、修改列
/*
修改列的长度
修改数字列的精度
修改列的数据类型
修改列的默认值
*/
1、修改列的长度
--只有在表中没有行或这列为空值时才可以减小列的长度
alter table emp modify emp_name VARCHAR2(30);
alter table emp modify emp_name varchar2(3);
2、修改数字列的精度
--只有在表中没有行或这列为空值时才可以减小列的精度
ALTER TABLE emp modify emp_id NUMBER(30);
ALTER TABLE emp modify emp_id NUMBER(1);
3、修改列的数据类型
--如果表中没有或这列为空值时，可以修改为任何类型，否则只能修改为兼容的数据类型
ALTER TABLE emp MODIFY emp_name char(15);
ALTER TABLE emp MODIFY emp_name number(15);
4、修改列的默认值
ALTER TABLE emp MODIFY emp_bir DEFAULT SYSDATE - 1;
5、删除列
alter table emp drop column emp_height;
4、添加约束
Constraint
保持数据的有效性，避免垃圾数据
比如性别只能是男女，年龄应该在1-100之间
常用的约束
Not null
Unique 在表中这一列不能有重复
Primary key 主键 非空并且不能重复
Foreign key 外键
Check
create table stu(
stu_id integer primary key,
stu_name varchar2(20),
stu_bir date
);
select * from stu
drop table stu
加约束名
create table stu(
stu_id integer constraint id_pk primary key,
stu_name varchar2(20),
stu_bir date
);
创建约束的时候，要取个有意义的名字，如果不取名字，oracle也会自动取名
在创建表的时候添加约束
或者在创建表后，通过alter添加约束
可以对一个列级别或者表级别创建约束
----------------------------------------------------------------------------------------------
约束 约束类型 意义
----------------------------------------------------------------------------------------------
CHECK C 指定一列或一组列的值必须满足特定的约束
----------------------------------------------------------------------------------------------
NOT NULL C 指定一列不允许存储空值。这实际上是一种强制的check约束
----------------------------------------------------------------------------------------------
PRIMARY KEY P 指定表的主键。主键由一列或多列组成。它唯一标识了表的一行
----------------------------------------------------------------------------------------------
FOREIGN KEY F 指定表的外键。外键引用另外一个表中的一列，在自引用情况下
则引用本表中的一列
----------------------------------------------------------------------------------------------
UNIQUE U 指定一列或一组列只能存储唯一的值
----------------------------------------------------------------------------------------------
(1)添加check约束
alter table emp add emp_sex varchar(10)
alter table emp add constraint emp_sex_ck
CHECK (emp_sex in ('man','women'));
INSERT INTO emp
(emp_id, emp_name, emp_bir,emp_sex)
VALUES
(1, 'xiao',null,'man');
ALTER TABLE emp ADD CONSTRAINT emp_id_ck
CHECK (emp_id > 0);
(2)添加not null约束
ALTER TABLE emp MODIFY emp_sex CONSTRAINT emp_sex_nn NOT NULL;
INSERT INTO emp
(emp_id, emp_name, emp_bir,emp_sex)
VALUES
(1, 'xiao',date'1990-01-01',null);
(3)添加primary key约束
CREATE TABLE table_name(
col_name TYPE PRIMARY KEY,
...
);
CREATE TABLE table_name(
col_name TYPE CONSTRAINT cons_name PRIMARY KEY,
...
);
ALTER TABLE table_name ADD CONSTRAINT col_name_pk PRIMARY KEY(col_name);
创建表 指定主键约束名
CREATE TABLE Order_Status2(
ID INTEGER CONSTRAINT Order_Status2_pk PRIMARY KEY,
status VARCHAR2(10),
last_modified DATE DEFAULT SYSDATE
);
create table emp(
emp_id integer,
emp_name varchar2(20),
emp_age integer
)
alter table emp add constraint ip_pk primary key(emp_id);
alter table emp modify emp_name constraint name_notnull not null;
创建联合主键的表
create table sc(
sno integer,
cno integer,
scgrade integer,
constraint con_pk primary key(sno,cno)
);
(4)添加foregin key约束
//新建两个表，建立外键关系，观察外界对表操作的影响。
create table stu
(
stu_id number(10) primary key,
stu_name varchar2(20),
stu_bir date,
stu_xi_id number(10)
);
create table xi
(
xi_id number(10) primary key,
xi_name varchar2(20),
xi_addr varchar2(30)
);
alter table stu add constraint xi_id_fk foreign key(stu_xi_id) references xi(xi_id);
select * from stu;
select * from xi;
insert into xi values(1,'yingyu',null);
insert into stu values(1,'aaa',null,1);
3、外键对插入的影响
INSERT INTO Order_Status2
VALUES(3,'PLACED1',SYSDATE,4);
INSERT INTO Order_Status2
VALUES(2,'PLACED2',SYSDATE,1);
INSERT INTO Order_Status2
VALUES(4,'PLACED3',SYSDATE,5);
delete from order_status2
select * from order_status2
select * from employees
4、外键对修改的影响
select * from order_status2
select * from employees
UPDATE Order_Status2
SET employee_id = 3
WHERE id = 2;
UPDATE Order_Status2
SET modified_by = 5
WHERE id = 2;
5、外键对删除的影响
SELECT * FROM Order_Status2;
SELECT * FROM employees;
DELETE employees
WHERE employee_id = 4;
--使用一个带有foreign key约束的on delete cascade子句，可以指定在父表中删除一行记录时，子表中匹配的所有行，也都将被删除。
ALTER TABLE Order_Status2 DROP COLUMN modified_by;
ALTER TABLE Order_Status2 ADD CONSTRAINT Order_Status2_modified_by_fk modified_by
REFERENCES employees(employee_id) ON DELETE CASCADE;
--当从employees表中删除一行记录时，Order_Status2表所匹配的行也将都被删除。
insert into employees values(5,3,'dtang','huahua','aaaa',120000)
insert into Order_Status2(id,modified_by) values(1,5)
DELETE employees WHERE employee_id = 5;
select * from employees
select * from Order_Status2
--使用一个带有foreign key约束的on delete set null子句，可以指定在父表中删除一行记录时，子表中匹配行的外键
--将被设置为空值
ALTER TABLE Order_Status2 DROP COLUMN modified_by;
ALTER TABLE Order_Status2 ADD CONSTRAINT Order_Status2_modified_by_fk modified_by
REFERENCES employees(employee_id) ON DELETE set null;
--当从employees表中删除一行记录时，Order_Status2表所匹配的行所在的外键列都被置为null。
DELETE employees
WHERE employee_id = 5;
(5)添加unique约束
Alter table Order_Status2 drop CONSTRAINT Order_Status2_status_uq
ALTER TABLE Order_Status2 ADD CONSTRAINT Order_Status2_status_uq UNIQUE(status);
可以插入null值，
两个null值无法比较
select * from order_status2;
insert into order_status2 values(1,'aaa',null,1)
insert into order_status2 values(2,'aaa',null,1)
insert into order_status2 values(4,null,null,1)
--删除约束
ALTER TABLE emp DROP CONSTRAINT emp_sex_nn;
--禁用约束
ALTER TABLE Order_Status2 ADD CONSTRAINT Order_Status2_status_uq UNIQUE(status) DISABLE;
ALTER TABLE Order_Status2 DISABLE CONSTRAINT order_status2_modified_by_nn;
--启用约束
--表中的所有行必须满足约束条件
ALTER TABLE Order_Status2 ENABLE CONSTRAINT Order_Status2_status_uq;
--对新数据启用约束
ALTER TABLE Order_Status2 ENABLE NOVALIDATE CONSTRAINT Order_Status2_status_uq;
创建表
CREATE TABLE Order_Status2(
ID INTEGER CONSTRAINT Order_Status2_pk PRIMARY KEY,
status VARCHAR2(10),
last_modified DATE DEFAULT SYSDATE
);
--获取约束的信息
SELECT * FROM User_Constraints;
SELECT * FROM all_Constraints;
SELECT constraint_name, constraint_type, status, deferrable, deferred
FROM user_constraints
WHERE table_name = 'ORDER_STATUS2';
--获取有关列约束的信息
SELECT * FROM User_Cons_Columns;
SELECT * FROM all_Cons_Columns;
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'ORDER_STATUS2';
SELECT ucc.column_name, ucc.constraint_name, uc.constraint_type, uc.status
FROM user_constraints uc, user_cons_columns ucc
WHERE uc.table_name = ucc.table_name
AND uc.constraint_name = ucc.constraint_name
AND ucc.table_name = 'ORDER_STATUS2';
--重命名表与字段
RENAME Order_Status2 TO order_state;
RENAME order_state TO Order_Status2;
alter table xi rename to xi1;
alter table xi1 rename to xi;
alter table xi rename column xi_name to xi_n
alter table xi rename column xi_n to xi_name;
--向表中添加注释
--注释有助于记住表或列的用途
COMMENT ON TABLE stu IS '用来存储学生的表';
COMMENT ON COLUMN stu.stu_id IS '学生的学号';
COMMENT ON COLUMN Order_Status2.last_modified
IS 'last_modified stores the date of the order was modified last';
--获取表的注释
SELECT * FROM user_tab_comments WHERE table_name = 'ORDER_STATUS2';
--获取列的注释
SELECT * FROM user_col_comments WHERE table_name ='STU'
5、删除表
Delete，drop，truncate 三者的区别
Delete from emp_new 删除后未提交前可以回滚，删除速度慢
TRUNCATE TABLE emp_new; 快速的删除表数据，并保留表结构，删除的数据不可以用rollback回滚，不能truncate一个带外键的表
DROP TABLE emp_new; 删除了表结构以及内容
6、创建序列
sequence通常被我们用来生成唯一键值的一种机制
在自己的本本上测试的，Oracle 11gR2. 单Instance数据库单会话循环不间断取1-4万个值。
nocache： 2.26s 10000
cache:20 0.46s 10000
cache:100 0.37s 10000
cache:1000 1.31s 40000
nocache： 9.33s 40000
CREATE SEQUENCE seq_name
[START WITH start_num]
[INCREMENT BY increment_num]
[{MAXVALUE Maxvalue_num|nomaxvalue}]
[{MINVALUE Minvalue_num|Minvalue}]
[{CYCLE|nocycle}]
[{CACHE cache_name|NOCACHE}]
START WITH start_num指定序列从哪个整数开始，默认值为1
INCREMENT BY increment_num指定序列每次增加的增量，默认值为1
MAXVALUE Maxvalue_num指定该序列的最大整数
MINVALUE Minvalue_num指定该序列的最小整数
CREATE SEQUENCE test_seq;
CREATE SEQUENCE test2_seq
START WITH 10 INCREMENT BY 2
MINVALUE 10 MAXVALUE 20
NOCYCLE CACHE 2;
drop sequence test2_seq
CREATE SEQUENCE test3_seq
START WITH 10 INCREMENT BY -1
MINVALUE 1 MAXVALUE 10
NOCYCLE CACHE 5;
--获取序列的信息
SELECT * FROM User_Sequences;
SELECT * FROM All_Sequences;
--使用序列
--序列生成一系列数字，一个序列中包含两个伪列currval , nextval，可以分别用来获取该序列的当前值和下一个值。
--在检索序列的当前值之前，必须通过检索序列的下一个值对序列进行初始化。
--初始化后，则可以使用currval来获取该序列的当前值了。
SELECT test3_seq.nextval FROM dual;
SELECT test2_seq.currval FROM dual;
--使用序列来填充主键
CREATE TABLE Order_Status2(
ID INTEGER CONSTRAINT order_status2_pk PRIMARY KEY,
status VARCHAR2(10),
last_modified DATE DEFAULT SYSDATE
);
CREATE SEQUENCE Order_Status2_seq NOCACHE;
INSERT INTO Order_Status2(ID,status,last_modified)
VALUES (Order_Status2_seq.nextval,'PLACED',SYSDATE);
INSERT INTO Order_Status2(ID,status,last_modified)
VALUES (Order_Status2_seq.nextval,'PENDING',SYSDATE);
select * from Order_Status2
--删除序列
DROP SEQUENCE test3_seq;
7、索引
--创建索引
CREATE [UNIQUE]INDEX index_name ON table_name(column_name[,COLUMN_name ...])
TABLESPACE tab_space;
/*
UNIQUE指定索引列中的值必须是唯一的
唯一性索引unique index和一般索引normal index最大的差异就是在索引列上增加了一层唯一约束。添加唯一性索引的数据列可以为空，但是只要存在数据值，就必须是唯一的。
*/
SELECT customer_id, first_name, last_name
FROM customers
WHERE last_name = 'Brown';
CREATE INDEX customers_last_name_idx ON customers(Last_Name);
CREATE UNIQUE INDEX customers_phone_idx ON customers(phone);
INSERT INTO customers VALUES (6, 'aa', 'bb', SYSDATE, '800-555-1214');
CREATE INDEX customers_first_last_name_idx ON employees(first_name,Last_Name);
select * from customers
--获取索引的信息
SELECT * FROM user_indexes;
SELECT * FROM all_indexes;
SELECT index_name, table_name, uniqueness, status
FROM user_indexes
WHERE table_name IN ('CUSTOMERS', 'EMPLOYEES');
--获取列索引的信息
SELECT * FROM User_Ind_Columns;
SELECT * FROM All_Ind_Columns;
SELECT index_name, table_name, column_name
FROM user_ind_columns
WHERE table_name IN ('CUSTOMERS', 'EMPLOYEES');
--删除索引
DROP INDEX customers_first_last_name_idx;
8、视图
--创建视图
CRE
alias_name为子查询中的表达式指定一个别名
subquery指定一个子查询，它对基表进行检索
with check option说明子查询检索的行才能被插入、修改或删除。
constraint_name指定with check option或read only约束的名称。
read only说明只能对基表中的行进行只读访问
*/
--简单视图，包含一个子查询，只从一个基表中检索数据
--复杂视图，包含一个子查询，从多个基表中检索数据，包含分组，函数调用等
--创建并使用简单视图
create or replace view cheap_products_view AS
SELECT * FROM products WHERE price < 15;
CREATE VIEW employees_view AS
SELECT employee_id,manager_id,first_name,last_name,title
FROM employees;
--对视图进行select操作
SELECT product_id,NAME,price
FROM cheap_products_view;
SELECT * FROM employees_view;
--对视图进行insert操作
--只能对简单视图执行DML操作，复杂视图不支持DML操作
INSERT INTO cheap_products_view
(product_id, product_type_id, NAME, price)
VALUES
(13, 1, 'Western Front', 13.50);
SELECT * FROM cheap_products_view;
--基表
select * from products
--没有with check option，可以插入、修改或删除子查询不能检索的行
CREATE OR REPLACE VIEW cheap_products_view2 AS
SELECT * FROM products
WHERE price < 15
--WITH check option CONSTRAINT cheap_products_view2_price;
select * from cheap_products_view2
INSERT INTO cheap_products_view2
(product_id, product_type_id, NAME, price)
VALUES
(15, 1, 'Southern Front', 16.50);
--创建具有check option约束的视图
--指定对视图的DML操作必须满足子查询的条件。
CREATE OR REPLACE VIEW cheap_products_view2 AS
SELECT * FROM products
WHERE price < 15 WITH check option CONSTRAINT cheap_products_view2_price;
select * from cheap_products_view2
INSERT INTO cheap_products_view2
(product_id, product_type_id, NAME, price)
VALUES
(15, 1, 'Southern Front', 16.50);
--创建具有read only约束的视图
--指定视图是只读的
CREATE OR REPLACE VIEW cheap_products_view3 AS
SELECT * FROM products
WHERE price < 15
WITH READ ONLY CONSTRAINT cheap_products_view3_read_only;
INSERT INTO cheap_products_view3
(product_id, product_type_id, NAME, price)
VALUES
(16, 1, 'Northern Front', 19.50);
--获取有关视图定义的信息
SELECT * FROM User_Views;
SELECT * FROM all_views;
--获取有关视图约束的信息
SELECT constraint_name, constraint_type, status, deferrable, deferred
FROM user_constraints
WHERE table_name IN ('CHEAP_PRODUCTS_VIEW2', 'CHEAP_PRODUCTS_VIEW3');
--删除视图
DROP VIEW cheap_products_view3;
