oracel数据库

--创建一张空表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.uniqueDROP 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 keyDROP 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 keyDROP 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); --3ALTER 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 hiredate<to_date('2012-1-1','yyyy-MM-dd');UPDATE 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;