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;
-----------------------------------------test*练习---------------------------------------------------------------
CREATE TABLE emp_first(fNAME,fjob,fsalary,hiredate)
AS SELECT ename,job,sal,hiredate FROM emp WHERE deptno=10; 
SELECT *FROM emp_first
ALTER TABLE emp_first ADD(info VARCHAR2(400));

COMMENT ON TABLE emp_first IS '员工信息新表';
 ALTER TABLE emp_first DROP(info)
 CREATE INDEX idx_fname ON emp_first(fname);
 SELECT fname FROM emp_first
----------------------------------------test*练习----------------------------------------------------------------
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;




CREATE TABLE department(
   deptNo NUMBER(2) NOT NULL PRIMARY KEY,
   deptName VARCHAR2(20) NOT NULL,
   LOCATION VARCHAR(20) DEFAULT '青岛'
);
--fk_department_deptno FOREIGN KEY(deptNo) REFERENCES department(deptno)
CREATE TABLE employee(
eno NUMBER(5) NOT NULL,
ename VARCHAR2(20) NOT NULL,
deptNO NUMBER(2),
CONSTRAINT fk_department_deptno FOREIGN KEY(deptNo) REFERENCES department(deptno)
);
DROP TABLE employee;
CREATE TABLE employee(
eno NUMBER(5) NOT NULL,
ename VARCHAR2(20) NOT NULL,
deptNO NUMBER(2) CONSTRAINT fk1_department_deptno REFERENCES department(deptno)

);

SELECT * FROM employee
SELECT * FROM department
--数据操作
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)
);
SELECT * FROM employee
UPDATE employee SET phone='13546885698'WHERE deptno=10;

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 employee VALUES(10001,'又大',22,3799,'',to_date('2014-8-5','yyyy-MM-dd'),10);

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;