(03)oracle新人笔记------表的相关操作

创建表

CREATE TABLE dossier(

 id NUMBER(4),

 cname VARCHAR2(20),

 birthday DATE,

 stature NUMBER(3),

 weight NUMBER(5,2),

 country_code CHAR(2) DEFAULT '01');

 

插入一条数据

INSERT INTO dossier

(id,cname,birthday,stature,weight)

VALUES

(2,'姚明',TO_DATE('1980.09.12','YYYY.MM.DD'),226,134);

 

删除一行数据

DELETE FROM emp_temp WHERE empno=1005;

COMMIT;

 

使用默认值进行修改

UPDATE dossier SET country_code=DEFAULT WHERE id=2;

 

使用子查询语法创建表

CREATE TABLE emp_test

AS

SELECT empno,ename,sal+1000 newSalary  //表达式需要起别名，不然报错

FROM emp

WHERE deptno=10;

 

引用另一个用户的表

SELECT * FROM scott.emp;

 

修改表定义

1.添加

  ALTER TABLE dossier ADD (sex CHAR(1));

2.修改

  ALTER TABLE dossier MODIFY (sex CHAR(2));

  修改时添加默认值

  ALTER TABLE dossier MODIFY (sex DEFALUT'男');

3.删除

  ALTER TABLE emp DROP COLUMN sex;

  ALTER TABLE dept10 DROP (列)

4.重命名表

  RENAME old_name TO new_name;

  RENAME emp TO emp1;

 

5.截断表

  TRUNCATE TABLE table;

 

 

 

 

 

 

练习

 

• CREATETABLE emp_temp(

   empno NUMBER(4),

   ename VARCHAR2(20),

   job VARCHAR(15),

   salary NUMBER(7,2),

   bonus NUMBER(7,2),

   hiredate DATE,

   mgr NUMBER(4),

   deptno NUMBER(10));  

 

 

 

 

 

• //属性全写可以不要那一行属性标题

INSERT INTO emp_temp

VALUES

(1001,'张无忌','Manager',10000,2000,

TO_DATE('2012.4.10','yyyy-mm-dd'),1005 ,10);

 

• INSERT INTO emp_temp

(empno,ename,job,salary,bonus,hiredate,mgr,deptno)

VALUES

(1002,'陆小凤','Analyst',8000,1000,

TO_DATE('2001.4.11','yyyy-mm-dd'),1001,10);

 

• INSERT INTO emp_temp(empno,ename,job,

salary,bonus,hiredate,mgr,deptno)

VALUES

(1003,'李易','Analyst',9000,1000,TO_DATE('2011.4.10','yyyy-mm-dd'),1001,10);

 

• INSERT INTO emp_temp

(empno,ename,job,salary,hiredate,mgr,deptno)

VALUES

(1004,'郭芙蓉','Programmer',5000,TO_DATE('2001.1.10','yyyy-mm-dd'),1001 ,10);

 

• INSERTINTO EMP_TEMP

(empno,ename,job,salary,hiredate,deptno)

VALUES

(1005,'张三丰','President',15000,TO_DATE('2015.5.10','yyyy-mm-dd'),20);

 

• INSERT INTO emp_temp

(empno,ename,job,salary,bonus,hiredate,mgr,deptno)

VALUES

(1006,'蔡小六','Manager',5000,400,TO_DATE('2001.2.09','yyyy-mm-dd'),1005 ,20);

 

• INSERT INTO emp_temp

(empno,ename,job,salary,bonus,hiredate,mgr,deptno)

VALUES

(1007,'陆无双','Clerk',4000,500,TO_DATE('2001.2.9','yyyy-mm-dd'),1009 ,20);

 

• INSERT INTO emp_temp

(empno,ename,job,salary,bonus,hiredate,mgr,deptno)

VALUES

(1008,'黄蓉','Manager',5000,800,TO_DATE('2001.5.09','yyyy-mm-dd'),1005 ,30);

 

• INSERT INTO emp_temp

(empno,ename,job,salary,hiredate,mgr,deptno)

VALUES

(1009,'韦小宝','Saleman',4000,TO_DATE('2002.2.9','yyyy-mm-dd'),1008 ,30);

 

• INSERT INTO emp_temp

(empno,ename,job,salary,hiredate,mgr,deptno)

VALUES

(1010,'郭靖','Saleman',4500,TO_DATE('2010.5.9','yyyy-mm-dd'),1008 ,30);

 

1. CREATETABLE dept_temp(deptno NUMBER(2),dname CHAR(20),location CHAR(20));
2. INSERTINTO dept_temp VALUES (10,'研发部','北京');
3. INSERTINTO dept_temp VALUES (20,'财务部','上海');
4. INSERTINTO dept_temp VALUES (30,'销售部','广州');
5. INSERTINTO dept_temp VALUES (40,'后勤部','天津');