第11讲-建立和管理表

第11讲 建立和管理表1、使用create语句创建表1.1 在当前方案中建表例：建立一个部门表的备份表用于备份数据create table dept01( deptno number(2), dname varchar2(14), loc varchar2(14);查看dept01结构：SQL desc dept01;Name Type Nullable Default Comments - - - - - DEPTNO NUMBER(2) Y DNAME VARCHAR2(14) Y LOC VARCHAR2(14) Y 1.2 建表时指定默认值例：创建薪资表备份表（bonus01），奖金和工资缺省值为0create table bonus01( ename nvarchar2(10), job nvarchar2(9), sal number(7,2) default 0, comm number(7,2) default 0 ); 查看bonus01结构：SQL desc bonus01;Name Type Nullable Default Comments - - - - - ENAME NVARCHAR2(10) Y JOB NVARCHAR2(9) Y SAL NUMBER(7,2) Y 0 COMM NUMBER(7,2) Y 0 练习1：以scott用户登录test数据，建立表author，表结构如下：表名称列名数据类型缺省值注释AuthorIDNumber(3)NameVarchar2(10)SalNumber（6,2）0练习2：向表中添加2至3位作者的信息。1.3 使用子查询建表 例：使用子查询建立员工表备份表emp01 (empno,ename,job,sal,comm)create table emp01 (empno,ename,job,sal,comm) asselect empno,ename,job,sal,comm from emp where deptno=30;查询表结构：SQL desc emp01;Name Type Nullable Default Comments - - - - - EMPNO NUMBER(4) Y ENAME VARCHAR2(10) Y JOB VARCHAR2(9) Y SAL NUMBER(7,2) Y COMM NUMBER(7,2) Y 练习：使用子查询建立author表的备份表author01，建表的同时将author表中数据备份到author01表中。2、使用alter语句修改表2.1 增加列例：在emp01表中添加字段deptno（2位数字）和字段dname（14位字符）alter table emp01add ( deptno number(2), dname varchar2(14); 查看结构：SQL desc emp01;Name Type Nullable Default Comments - - - - - EMPNO NUMBER(4) Y ENAME VARCHAR2(10) Y JOB VARCHAR2(9) Y SAL NUMBER(7,2) Y COMM NUMBER(7,2) Y DEPTNO NUMBER(2) Y DNAME VARCHAR2(14) Y练习：为author表增加列名address，类型varchar2，长度100。2.2 修改列定义例：修改emp01表列定义，Job列修改为15位长度字符串，缺省值为CLERK，Sal和comm的缺省值为0.00alter table emp01modify ( job varchar2(15) default CLERK, sal number(7,2) default 0.00, comm number(7,2) default 0.00); 查看修改后emp01表列的结构：SQL desc emp01;Name Type Nullable Default Comments - - - - - EMPNO NUMBER(4) Y ENAME VARCHAR2(10) Y JOB VARCHAR2(15) Y CLERK SAL NUMBER(7,2) Y 0.00 COMM NUMBER(7,2) Y 0.00 DEPTNO NUMBER(2) Y DNAME VARCHAR2(14) Y 练习1：重新定义author表的列name和sal的数据类型和缺省值，Name类型varchar2长度15,列sal类型number，长度10，保留2位小数， 缺省值1000。练习2：添加两条记录。2.3 删除列例：删除表emp01列deptnoalter table emp01drop( deptno);查看删除列deptno后的结构：SQL desc emp01;Name Type Nullable Default Comments - - - - - EMPNO NUMBER(4) Y ENAME VARCHAR2(10) Y JOB VARCHAR2(15) Y CLERK SAL NUMBER(7,2) Y 0.00 COMM NUMBER(7,2) Y 0.00 DNAME VARCHAR2(14) Y 练习：删除author表的列address。2.4 修改列名例：将emp01表的dname字段修改为deptNamealter table emp01 rename column dname to deptName;SQL desc emp01;Name Type Nullable Default Comments - - - - - EMPNO NUMBER(4) Y ENAME VARCHAR2(10) Y JOB VARCHAR2(15) Y CLERK SAL NUMBER(7,2) Y 0.00 COMM NUMBER(7,2) Y 0.00 DEPTNAME VARCHAR2(14) Y 练习：将author表的列名sal修改为salary。2.5 修改表名称例：将dept01表名称修改为deptbakrename dept01 to deptbak;（1）使用数据字典视图user_tables显示当前用户的所有表SQL select table_name from user_tables; TABLE_NAME-DEPTEMPSALGRADEEMP01BONUS01DEPTBAKBONUS（2）使用数据字典视图user_objects可以显示所有数据库对象SQL select object_name from user_objects where object_type=upper(table); OBJECT_NAME-DEPTEMPBONUSSALGRADEEMP01BONUS01DEPTBAK2.6 增加注释（1） 增加表注释例：为emp01表增加注释SQLcomment on table emp01 is 备份雇员主要信息;使用数据字典视图user_tab_comments可以查看注释SQL select comments from user_tab_comments where table_name=upper(emp01); COMMENTS-备份雇员主要信息（2） 增加列名注释例：为emp01表的empno字段添加注释“雇员编号”SQL comment on column emp01.empno is 雇员编号;使用数据字典视图user_col_comments可以显示当前用户所有表的列视图SQL select comments from user_col_comments where table_name=upper(emp01) and column_name=upper(empno); COMMENTS-雇员编号练习1：为author表添加注释，注释信息为“作者表”，为ID、Name和Salary列分别增加注释“作者编号”、“作者姓名”和“作者工资”。练习2：显示当前用户的所有表名。练习3：显示author表的注释。练习4：显示author表中列的注释。3、删除和恢复表例：删除bonus01表SQL drop table bonus01;查询删除结果：SQL desc bonus01;Object bonus01 does not exist.删除的表可以使用flashback恢复SQL flashback table bonus01 to before dr