ORA的操作语句

1.创建表

 
    ----表1 作者
    CREATE TABLE authors ( 
     id number(38) not null, 
     name varchar2(100), 
     birth_date date, 
     gender varchar2(30) 
    ); 

	 ----表2 出版物
	 CREATE TABLE author_books（
	 id number（38），
	 authorid number(38),
	 title varchar2（100），
	 publish_date date
	 ）;

2.添加索引

 
 --添加索引
 
 CREATE UNIQUE INDEX authors_uk1 on authors (name, birth_date, gender );
 
 CREATE INDEX author_books_k1 on author_books (title); 

3.创建唯一约束

 
ALTER TABLE authors 
                 ADD CONSTRAINT authors_uk1 
                 UNIQUE ( name, birth_date, gender ); 
 

4.添加主键

 
 ALTER TABLE authors ADD CONSTRAINT authors_pk 
 primary key ( id );
 
 --给出版物表添加主键
alter table author_books add constraint author_book_pk primary key (id);
 

5.添加表外键

 ALTER TABLE author_books ADD 
 CONSTRAINT author_books_fk1 
 FOREIGN KEY (author_id) 
 REFERENCES authors (id);

6.DML操作

插入：
 INSERT INTO authors ( id, name, birth_date, gender ) VALUES ( 100, 'Tom', to_date('19830823', 'YYYYMMDD'), 'MALE' ); 
 COMMIT; 
--DML操作 修改
更新：（一般都需要带入条件）
UPDATE authors SET name = upper(name); -- select * from authors

COMMIT; 
删除
delete from 表 where 条件

7.DUAL表

--对于dual表的了解
SELECT 1 + 1 FROM dual; 
SELECT length(NULL) FROM dual;
SELECT nvl(length(NULL), 0) as length FROM DUAL; 

8.DQL操作（select）

SELECT name FROM authors ORDER BY name; 
select * from authors order by name;
select * from authors a order by a.birth_date desc
俩表联查
select * from jys.authors a,jys.author_books b where a.id=b.author_id; 

9.创建视图

CREATE OR REPLACE VIEW authors_publications as 
SELECT authors.id, 
authors.name, 
author_books.title, 
author_books.publish_date 
FROM authors, 
author_books 
WHERE authors.id = author_books.author_id

select * from authors_publications

10.创建序列

create sequence EMP_SEQUENCE
minvalue 1
maxvalue 9999999999999999999999999999
start with 367851
increment by 1
cache 20;

select EMP_SEQUENCE.nextval from dual;

11.更改表名、列名

alter table 表名 rename to 新表名

alter table 表名 rename column 旧列名 to 新列

12.更改列属性、删除

alter table 表名 modify (
列1 新数据类型 [非空属性]
..
);
alter table 表名 drop column 列名

关于删除列的注意事项:
原则上,列都可以删
但,如果一个列作为主键出现
或者作为另1张表的外键,则不能删除