oracle用的表语句,oracle常用建表语句

--创建一张空表

CREATE TABLE department(

deptNo NUMBER(2) NOT NULL,

deptName VARCHAR2(20) NOT NULL,

LOCATION VARCHAR(20) DEFAULT '青岛'

);

--创建表同时，填充数据

CREATE TABLE emp_new(NAME,job,salary,hiredate)

AS

SELECT ename,job,sal,hiredate FROM emp WHERE deptno=30;

SELECT * FROM emp_new;

--给表重命名

RENAME emp_new TO emp_new_test;

--对表进行修改 ALTER TABLE department

--为表添加字段

ALTER TABLE department ADD(info VARCHAR2(400));

--修改表中的字段

ALTER TABLE department MODIFY(info VARCHAR2(800));

--删除表中的字段

ALTER TABLE department DROP(info);

--给表添加注释

COMMENT ON TABLE department IS '部门信息表';

--给表中的列添加注释

COMMENT ON COLUMN department.location IS '部门所在地';

SELECT * FROM emp_new_test;

DELETE  FROM emp_new_test ;

TRUNCATE TABLE emp_new_test;

DROP TABLE emp_new_test CASCADE CONSTRAINTS;

--单索引

CREATE INDEX idx_deptno ON department(deptno);

--复合索引

CREATE INDEX idx_deptno_deptname ON department(deptno,deptname);

--唯一索引

CREATE UNIQUE INDEX idx_uq_deptname ON department(deptname);

--重建索引

ALTER INDEX idx_uq_deptname REBUILD;

--删除索引

DROP INDEX idx_uq_deptname;

--约束的类型：

--1.not null

--约束可以在创建表的时候

CREATE TABLE employee(

eno NUMBER(5) NOT NULL,

ename VARCHAR2(10) NOT NULL,

age NUMBER(2) NOT NULL,

phone VARCHAR2(16)

);

--修改表的时候

ALTER TABLE employee MODIFY phone NOT NULL;

--2.unique

DROP TABLE employee;

CREATE TABLE employee(

eno NUMBER(5) NOT NULL,

ename VARCHAR2(10) NOT NULL UNIQUE,--1

age NUMBER(2) NOT NULL,

phone VARCHAR2(16)

);

DROP TABLE employee;

CREATE TABLE employee(

eno NUMBER(5) NOT NULL,

ename VARCHAR2(10) NOT NULL,

age NUMBER(2) NOT NULL,

phone VARCHAR2(16),

CONSTRAINT uq_phone UNIQUE(phone)  --2

);

ALTER TABLE employee ADD CONSTRAINT uq_phone UNIQUE(phone);--3

--3 primary key

DROP TABLE employee;

CREATE TABLE employee(

eno NUMBER(5) NOT NULL PRIMARY KEY,--1

ename VARCHAR2(10) NOT NULL,

age NUMBER(2) NOT NULL,

phone VARCHAR2(16)

);

DROP TABLE employee;

CREATE TABLE employee(

eno NUMBER(5) NOT NULL,

ename VARCHAR2(10) NOT NULL,

age NUMBER(2) NOT NULL,

phone VARCHAR2(16),

CONSTRAINT pk_eno PRIMARY KEY (eno)--2

);

DROP TABLE employee;

CREATE TABLE employee(

eno NUMBER(5) NOT NULL ,

ename VARCHAR2(10) NOT NULL,

age NUMBER(2) NOT NULL,

phone VARCHAR2(16)

);

ALTER TABLE employee ADD CONSTRAINT pk_eno PRIMARY KEY (eno); --3

--4 foreign key

DROP TABLE department;

CREATE TABLE department(

deptNo NUMBER(2) NOT NULL PRIMARY KEY,

deptName VARCHAR2(20) NOT NULL,

LOCATION VARCHAR(20) DEFAULT '青岛'

);

DROP TABLE employee;

CREATE TABLE employee(

eno NUMBER(5) NOT NULL ,

ename VARCHAR2(10) NOT NULL,

age NUMBER(2) NOT NULL,

phone VARCHAR2(16),

deptNo NUMBER(2) CONSTRAINT fk_department_deptno REFERENCES department(deptno) --1

);

DROP TABLE employee;

CREATE TABLE employee(

eno NUMBER(5) NOT NULL ,

ename VARCHAR2(10) NOT NULL,

age NUMBER(2) NOT NULL,

phone VARCHAR2(16),

deptNo NUMBER(2) ,

CONSTRAINT fk_department_deptno FOREIGN KEY(deptNo) REFERENCES department(deptno) --2

);

DROP TABLE employee;

CREATE TABLE employee(

eno NUMBER(5) NOT NULL ,

ename VARCHAR2(10) NOT NULL,

age NUMBER(2) NOT NULL,

phone VARCHAR2(16),

deptNo NUMBER(2)

);

ALTER TABLE employee ADD CONSTRAINT fk_department_deptno FOREIGN KEY(deptNo) REFERENCES department(deptno) --3

--check约束

DROP TABLE employee;

CREATE TABLE employee(

eno NUMBER(5) NOT NULL ,

ename VARCHAR2(10) NOT NULL,

age NUMBER(2) NOT NULL CHECK(age BETWEEN 18 AND 70),--1

phone VARCHAR2(16),

deptNo NUMBER(2)

);

DROP TABLE employee;

CREATE TABLE employee(

eno NUMBER(5) NOT NULL ,

ename VARCHAR2(10) NOT NULL CHECK(LENGTH(ename)<=5),

age NUMBER(2) NOT NULL,

phone VARCHAR2(16),

deptNo NUMBER(2),

CONSTRAINT ck_age  CHECK(age BETWEEN 18 AND 70) --2

);

DROP TABLE employee;

CREATE TABLE employee(

eno NUMBER(5) NOT NULL ,

ename VARCHAR2(10) NOT NULL,

age NUMBER(2) NOT NULL ,

phone VARCHAR2(16),

deptNo NUMBER(2)

);

ALTER TABLE employee ADD CONSTRAINT ck_age CHECK(age BETWEEN 18 AND 70); --3

ALTER TABLE employee RENAME CONSTRAINT ck_age TO ck_age_now;

ALTER TABLE employee DISABLE CONSTRAINT ck_age_now;

ALTER TABLE employee ENABLE CONSTRAINT ck_age_now;

ALTER TABLE employee DROP CONSTRAINT ck_age_now;

ALTER TABLE employee ADD CONSTRAINT pk_eno PRIMARY KEY (eno);

ALTER TABLE employee DROP CONSTRAINT pk_eno;

--数据操作

DROP TABLE department;

CREATE TABLE department(

deptNo NUMBER(2) NOT NULL PRIMARY KEY,

deptName VARCHAR2(20) NOT NULL,

LOCATION VARCHAR(20) DEFAULT '青岛'

);

DROP TABLE employee;

CREATE TABLE employee(

eno NUMBER(5) NOT NULL ,

ename VARCHAR2(10) NOT NULL,

age NUMBER(2)  DEFAULT 20 CHECK(age BETWEEN 18 AND 70) ,

salary NUMBER(7,2),

phone VARCHAR2(16),

hiredate DATE NOT NULL ,

deptNo NUMBER(2) CONSTRAINT fk_department_deptno REFERENCES department(deptno)

);

INSERT INTO department VALUES(10,'市场一部','青岛');

INSERT INTO department(deptno,deptName) VALUES(20,'市场二部');

INSERT INTO department(deptName,deptno) VALUES('市场三部',30);

INSERT INTO employee VALUES(10001,'张三',25,2300,'',SYSDATE,10);

INSERT INTO employee VALUES(10002,'李四',30,3800,'',to_date('2013-6-25','yyyy-MM-dd'),20);

INSERT INTO department VALUES(&deptno,'&deptName','&LOCATION');

INSERT INTO employee (eno,ename,salary,hiredate,deptno)

SELECT empno,ename,sal,hiredate,deptno FROM emp WHERE deptno=30;

UPDATE employee SET salary=salary*1.1 WHERE hiredateUPDATE employee SET age=trunc(dbms_random.value(18,70)) WHERE deptno=30 ;

UPDATE employee SET age=DEFAULT WHERE ename='ALLEN';

DELETE FROM employee WHERE eno=7900;

COMMIT;

ROLLBACK;

SELECT * FROM employee;

SELECT * FROM department;

SELECT * FROM emp;

SELECT dbms_random.value,dbms_random.value(0,100) FROM dual;

---序列

create sequence SEQ_EMPLOYEE

minvalue 1

maxvalue 999999999

start with 2

increment by 2

cache 20;

SELECT SEQ_EMPLOYEE.Nextval FROM dual;

SELECT SEQ_EMPLOYEE.CURRVAL FROM dual;

INSERT INTO employee VALUES(SEQ_EMPLOYEE.Nextval,'张三',25,2300,'',SYSDATE,10);

COMMIT;

SELECT * FROM employee;

DROP SEQUENCE SEQ_EMPLOYEE;

--视图

--简单视图

CREATE VIEW vw_emp  AS

SELECT empno,ename,hiredate FROM emp;

CREATE VIEW vw_emp1 AS

SELECT ename,hiredate,sal,comm FROM emp;

SELECT * FROM vw_emp;

SELECT * FROM vw_emp1;

--复杂视图

CREATE VIEW vm_emp3(deptno,ecount,avgsal) AS

SELECT deptno,COUNT(1),round(AVG(sal),2) FROM emp GROUP BY deptno;

RENAME vm_emp3 TO vw_emp3;

SELECT * FROM vw_emp3;

--连接视图

CREATE VIEW vw_emp4 AS

SELECT e.empno,e.ename,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno=d.deptno;

SELECT ename,dname FROM vw_emp4;

--只读视图

CREATE VIEW vw_emp5 AS

SELECT * FROM emp WITH READ ONLY;

SELECT * FROM vw_emp5;