oracle创建表与增删改

1、- 数据准备：创建工资等级表CREATE TABLE salgrade_test ( grade NUMBER (2), lowsal NUMBER (7, 2), hisal NUMBER (7, 2);- 查看工资等级表结构DESC salgrade;- 查看工资等级表数据SELECT *FROM salgrade_test;- 插入数据INSERT INTO salgrade_testVALUES (1, 10001, 99999);INSERT INTO salgrade_testVALUES (2, 8001, 10000);INSERT INTO salgrade_testVALU

2、ES (3, 6001, 8000);INSERT INTO salgrade_testVALUES (4, 4001, 6000);INSERT INTO salgrade_testVALUES (5, 1, 4000);- 计算员工的薪水等级SELECT NAME, salary, gradeFROM emp_test, salgrade_testWHERE emp_test.salary BETWEEN salgrade_test.lowsalAND salgrade_test.hisal;- -SELECT NAME, salary, gradeFROM emp_testFULL JO

3、IN salgrade_test ON emp_test.salary BETWEEN salgrade_test.lowsalAND salgrade_test.hisal;- 复制表：只复制结构 , 不复制数据CREATE TABLE salgrade_copy AS ( SELECT * FROM salgrade_test WHERE 1 <> 1);SELECT *FROM salgrade_copy;- 复制表：复制一部分数据-通过设置别名的方式 , 指定新表中的列名(year_sal)CREATE TABLE emp_test_copy AS ( SELECT emp

4、_id, NAME, salary * 12 year_sal FROM emp_test WHERE dept_test_id = 10);SELECT *FROM emp_test_copy;- 复制表：复制一部分数据 - 新表中的列名CREATE TABLE emp_count (did, emp_num) AS ( SELECT dept_test_id, COUNT (*) FROM emp_test GROUP BY dept_test_id);SELECT *FROM emp_count;- 创建一个同 emp 表结构相同的空表 , 将部门号为 10 的员工信息放入该表- 如果有

5、一张表 emp 的数据量为一百万条 , 此时需要建立 1 张测试表只放入少量测试数据( 如 100条 ) , 执行步骤如下所示：- 第 1 步 创建一个空表CREATE TABLE emp_copy AS ( SELECT * FROM emp_test WHERE 1 <> 1);- 第 2 步 揑入少量测试数据INSERT INTO emp_copy ( SELECT * FROM emp_test WHERE dept_test_id = 10);SELECT *FROM emp_copy;- 把表中的数据换为部门 20 和 30 的员工记录DELETEFROM emp_co

6、py;INSERT INTO emp_copy ( SELECT * FROM emp_test WHERE dept_test_id IN (20, 30);SELECT *FROM emp_copy;- 向新表中揑入指定记录数的数据,比如前 8 条 DELETEFROM emp_copy;INSERT INTO emp_copy ( SELECT * FROM emp_test WHERE ROWNUM <= 8);SELECT *FROM emp_copy;- update( 更新数据 ) *- 语法结构：- update 表名 set 列名 = 新的列值 ,- 列名 = 新的列值

7、.- .- where 条件;- 注意：- ü 更新( update )数据表时 , 注意条件 , 如果丌加条件 , 修改的是全部表记录- ü rollback 回退 , commit 确认- 将员工号为 1012 的员工薪水改为 3500 , 职位改为 ProgrammerUPDATE emp_testSET salary = 3500, JOB = 'Programmer'WHERE emp_id = 1012;SELECT *FROM emp_test;- 部门 10 的员工薪水+1000UPDATE emp_testSET salary = sala

8、ry + 3500WHERE dept_test_id = 10;SELECT *FROM emp_test;- delete( 删除数据 ) *- 语法结构：- delete from 表名 where 条件 ;- 注意：- ü 如果删除语句中丌加 where 条件 , 将删掉表中的全部记录- ü rollback 回退 , commit 确认- ü drop table 会删除表结构和数据 ;truncate 删除表数据 , 保留表结构。Drop 和 truncate 都- 不可以回退。 delete 仅删除数据 , 可以回退- 创建表 emp_bak2 ,

9、只存放不重复的记录CREATE TABLE emp_copy2 AS ( SELECT * FROM emp_test WHERE 1 <> 1);- 如下语句执行 3 遍 , 揑入 3 条重复数据INSERT INTO emp_copy2 (emp_id, NAME, salary)VALUES (1015, 'amy', 4000);- 如下语句执行 2 遍 , 揑入 2 条重复数据INSERT INTO emp_copy2 (emp_id, NAME, salary)VALUES (1016, 'rory', 5000);- 如下语句执行 1

10、遍 , 揑入 1 条数据INSERT INTO emp_copy2 (emp_id, NAME, salary)VALUES (1017, 'river', 6000);SELECT *FROM emp_copy2;- 创建表 emp_copy3 , 只存放不重复的记录 , 利用 distinct 关键字CREATE TABLE emp_copy3 AS SELECT DISTINCT *FROM emp_copy2;SELECT *FROM emp_copy3;- - 将表改名语法区别：Oracle:RENAME tablename TO newtablename; mysq

11、l：RENAME TABLE tablename TO newtablename;- 把 emp_copy3 改名为 emp_bak4RENAME emp_copy3 TO emp_bak4;- 把 emp_bak4 改名为 emp_copy3RENAME emp_bak4 TO emp_copy3;-删除重复数据DELETEFROM emp_copy2WHERE ROWID NOT IN ( SELECT MAX (ROWID) FROM emp_copy2 GROUP BY emp_id, NAME, salary );-子查询：查询出 empno , ename , salary 相同的

12、 rowid 最大的记录-主查询：删除 rowid 不在子查询之列的重复数据- Transaction( 事务 ) *- 1) 事务是一组 DML 操作的逻辑单元 , 用来保证数据的一致性。- 2) 在一个事务内 , 组成事务的这组 DML 操作 , 或者一起成功提交 , 或者一起被撤销。- 3) 事务控制语言 TCL( Transaction Control Language )- ü commit 事务提交 将所有的数据改劢提交- ü rollback 事务回滚 回退到事务之初 , 数据的状态和事务开始之前完全一- 致- ü savepoint 事务保存点(

13、较丌常用 )- 3.1. 事务的开始和终止( 事务边界 )- 1) 事务开始- 事务开始于上一个事务的终止或者第一条 DML 语句- 2) 事务终止- ü 事务终止于 commit/rollback 显式操作( 即控制台输入 commit/rollback )- ü 如果连接关闭 , 事务( Transaction )将隐式提交- ü DDL 操作( 比如 create ) , 事务将隐式提交- ü 如果出现异常 , 事务将隐式回滚- 1) 事务内部的数据改变只有在自己的会话中能够看到- 2) 事务会对操作的数据加锁 , 丌允许其它事务操作- 3) 如果

14、提交( commit )后 , 数据的改变被确认 , 则- ü 所有的会话都能看到被改变的结果 ;- ü 数据上的锁被释放 ;- ü 保存数据的临时空间被释放- 4) 如果回滚( rollback ) , 则- ü 数据的改变被取消 ;- ü 数据上的锁被释放 ;- ü 临时空间被释放SELECT *FROM emp_copy3;UPDATE emp_copy3SET emp_id = 1015WHERE emp_id = 1014;CREATE TABLE mytemp_test (ID NUMBER(4);INSERT INTO

15、mytemp_testVALUES (3);SAVEPOINT A;- 设置保存点 , 名为 AINSERT INTO mytemp_testVALUES (4);SAVEPOINT B;- 设置保存点 , 名为 BINSERT INTO mytemp_testVALUES (5);ROLLBACK TO A;- 回滚到保存点 A , 注意：A 之后的保存点全部被取消SELECT *FROM mytemp_test;- 3 被揑入数据库,- 4、5 没有被揑入- truncate 保留表结构 , 删除表中所有数据truncate 操作丌需提交( commit ) , 没有回退( rollbac

16、k )的机会- 语法结构： truncate table 表名 ;TRUNCATE TABLE mytemp_test;- 增加列( 只能增加在最后一列 )ALTER TABLE mytemp_test ADD(name VARCHAR(10);ALTER TABLE mytemp_test ADD(password VARCHAR(10);- 修改列名 password 为 pwd 和MySql有区别：- Oracle:ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name ;- Mysql:ALTER

17、TABLE table_name CHANGE COLUMN old_column_name new_column_name typedefinition;ALTER TABLE mytemp_test RENAME COLUMN password TO pwd;- 修改列的数据类型为 pwd char(8)- MySQL:alter table 表名 modify column 字段名 类型;- Oracle:alter table 表名 modify (字段名 类型);ALTER TABLE mytemp_test MODIFY(pwd VARCHAR(8) ;- 删除列 pwdALTER

18、 TABLE mytemp_test DROP COLUMN pwd ;DESC mytemp_test;- 主键约束：PRIMARY KEY=不重复+不为NULL；- (列级约束条件) CREATE TABLE dept_dylan ( dept_id NUMBER (2) PRIMARY KEY, dept_name VARCHAR (20), dept_location VARCHAR (40);- 数据准备 INSERT INTO dept_dylanVALUES (10, 'developer', 'beijing');INSERT INTO dept

19、_dylanVALUES (10, 'market', 'shenzhen');- 表级约束条件:建议约束命名规则：表名_列名_约束条件的类型CREATE TABLE dept_dylan2( dept_id NUMBER (2), dept_name VARCHAR (20), dept_location VARCHAR (40), constraint dept_dylan2_pk PRIMARY KEY (dept_id);- 数据准备 INSERT INTO dept_dylan2VALUES (10, 'developer', '

20、;beijing');INSERT INTO dept_dylan2VALUES (10, 'market', 'shenzhen');SELECT *FROM dept_dylan2;- 非空约束( not null , 简称 NN ) * 非空约束只能定义在列级CREATE TABLE student_dylan ( id NUMBER (4) PRIMARY KEY, NAME VARCHAR (10) NOT NULL, age NUMBER (3);INSERT INTO student_dylanVALUES (1, 'zhangwe

21、i', 20);INSERT INTO student_dylanVALUES (2, 'zhangwei', 19);INSERT INTO student_dylanVALUES (3, NULL, 19);SELECT *FROM student_dylan;- 给非空约束命名DROP TABLE student_dylan;CREATE TABLE student_dylan ( id NUMBER (4) PRIMARY KEY, name VARCHAR (10) constraint student_dylan_name_nn NOT NULL, age NUMBER (3);- 视图可以使用 CREATE OR REPLACE 来创建或覆盖,并可查询视图的定义。 CREATEOR REPLACE VIEW v_emp_count AS SELECT dept_test_id, count(*) emp_n