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张表的外键,则不能删除