Oracle表的简单应用.doc

表的增删改查创建表CREATE TABLE 用户名.表名(列名 数据类型 default 默认值 ,.);SQL create table t1 ( 2 id number(10), 3 name varchar2(20), 4 password varchar2(30);SQL create table tab1 as select * from emp where sal2000;SQL create table tab2 as select * from emp where 1=2;增加修改删除列增加一列ALTER TABLE 用户名.表名 ADD(列名1 数据类型DEFAULT 表达式,列名2 数据类型,.)SQL desc t1; 名称 是否为空? 类型 - - - ID NUMBER(10) NAME VARCHAR2(20) PASSWORD VARCHAR2(30)SQL alter table t1 add (sal number(10);SQL desc t1; 名称 是否为空? 类型 - - - ID NUMBER(10) NAME VARCHAR2(20) PASSWORD VARCHAR2(30) SAL NUMBER(10)SQL alter table t1 add (job varchar(10);SQL desc t1; 名称 是否为空? 类型 - - - ID NUMBER(10) NAME VARCHAR2(20) PASSWORD VARCHAR2(30) SAL NUMBER(10) JOB VARCHAR2(10)修改表已存列的数据类型与列名重命名语法格式:ALTER TABLE 表名MODIFY (列名1,数据类型 DEFAULT 表达式,列名2,数据类型,.)SQL desc t1; 名称 是否为空? 类型 - - - ID NUMBER(10) NAME VARCHAR2(20) PASSWORD VARCHAR2(30) SAL NUMBER(10) JOB VARCHAR2(10)SQL alter table t1 modify (sal varchar2(20);SQL desc t1; 名称 是否为空? 类型 - - - ID NUMBER(10) NAME VARCHAR2(20) PASSWORD VARCHAR2(30) SAL VARCHAR2(20) JOB VARCHAR2(10)SQL alter table t1 rename column job to comm;SQL desc t1; 名称 是否为空? 类型 - - - ID NUMBER(10) NAME VARCHAR2(20) PASSWORD VARCHAR2(30) SAL VARCHAR2(20) COMM VARCHAR2(10)SQL alter table t1 modify (comm number(10);SQL desc t1; 名称 是否为空? 类型 - - - ID NUMBER(10) NAME VARCHAR2(20) PASSWORD VARCHAR2(30) SAL VARCHAR2(20) COMM NUMBER(10)删除表里的某列ALTER TABLE 表名 DROP COLUMN 列名;SQL desc t1; 名称 是否为空? 类型 - - ID NUMBER(10) NAME VARCHAR2(20) PASSWORD VARCHAR2(30) SAL VARCHAR2(20) COMM NUMBER(10)SQL alter table t1 drop column comm;SQL desc t1; 名称 是否为空? 类型 - - - ID NUMBER(10) NAME VARCHAR2(20) PASSWORD VARCHAR2(30) SAL VARCHAR2(20)间接删除表里已存在的列删除表里的列特别是删除大表里的列是特耗资源的 所以 在删除一个列时 可以先把要删除的列打标记,标记成无用列 ,然后等系统不忙时在彻底删除这个列语法格式:ALTER TABLE 表名 SET UNUSED COLUMN 列名;把一个列设置成无用列 注意一下事项_ 必需在8i以上版本适用_ 该列被设置成无用列后,并不是删除了而是设置成无用标记而已还需要手工删除这个列_ 设置成无用列后,无法用sqlplus或是sql 语句看到的_ oracle把设置成无用列当作删除列处理_ 可以适用DROP UNUSED (列名) 选项删除被设置成无用的列_ 因为该语句是DDL语句 没有恢复无用列的命令然后 等系统不忙时在用ALTER TABLE 表名DROP UNUSED COLUMN;SQL alter table t1 set unused column sal;SQL desc t1; 名称 是否为空? 类型 - - - ID NUMBER(10) NAME VARCHAR2(20) PASSWORD VARCHAR2(30)SQL alter table t1 drop unused column;SQL desc t1; 名称 是否为空? 类型 - - - ID UMBER(10) NAME VARCHAR2(20) PASSWORD VARCHAR2(30)truncate table 截断表当一个表里的数据不再需要时,可以适用truncate table 全部删除表里的数据,该语句为DDL语句 无法适用rollback 来回滚数据语法格式:TRUNCATE TABLE 表名特点:_ 删除表中的数据行,但保留表的结构_ truncate 是DDL 语句,如果没有备份 数据无法恢复_ truncate 删除表中的数据行后,立即释放磁盘空间如果只删除数据,而保留数据结构 适用truncate 如果既要删除数据,也要删除表结构 适用DROP语法格式:DROP TABLE 表名特点:_ DROP删除表中的所有数据行和表的结构_ DROP 也删除表的所有索引_ 提交所有挂起事务_ 所有基于被DROP的表的视图别名依然保留 但已经无效对象表的重命名语法格式:RENAME 旧表名 TO 新表名SQL rename t1 to table1;SQL desc t1;ERROR:ORA-04043: 对象 t1 不存在SQL desc table1; 名称 是否为空? 类型 - - - ID NUMBER(10) NAME VARCHAR2(20) PASSWORD VARCHAR2(30)删除一个表DROP TABLE TABLENAMESQL drop table tab1;SQL select * from tab;TNAME TABTYPE CLUSTERID- - -DEPT TABLEEMP TABLEBONUS TABLESALGRADE TABLEBIN$DCVrueY1SDSmKp/kGnvFpA=$0 TABLETAB2 TABLETABLE1 TABLE清空闪回回收站SQL purge Recyclebin;回收站已清空。SQL select * from tab;TNAME TABTYPE CLUSTERID- - -DEPT TABLEEMP TABLEBONUS TABLESALGRADE TABLETAB2 TABLETABLE1 TABLE插入数据INSERT语法格式：INSERT INTO 表名 (列名，列名.) VALUES (数值,数值,.)SQL rename table1 to t1;SQL select * from t1;未选定行SQL insert into t1 values (1,aa,1234);SQL insert into t1 (id,name)values(2,bb);SQL select * from t1; ID NAME PASSWORD- - - 1 aa 1234 2 bbUPDATE 语法格式：UPDATE 表名 SET 列名=值,列名=值 WHERE 条件;SQL select * from t1; ID NAME PASSWORD- - - 1 aa 1234 2 bbSQL update t1 set password=5555 where id=1;已更新 1 行。SQL select * from t1; ID NAME PASSWORD- - - 1 aa 5555 2 bb SQL update t1 set password=6666 where id=2;已更新 1 行。SQL select * from t1; ID NAME PASSWORD- - - 1 aa 5555 2 bb 6666DELETE 语法：DELETE FROM 表名 WHERE 条件;delete 语句是删除 表里的数据SQL select * from t1; ID NAME PASSWORD- - - 1 aa 5555 2 bb 6666SQL delete from t1 where id=1;已删除 1 行。SQL select * from t1; ID NAME PASSWORD- - - 2 bb 6666SQL delete from t1;已删除 1 行。SQL select * from t1;未选定行SQL insert into t1 values (1,aa,11111);SQL insert into t1 values (2,bb,22222);SQL select * from t1; ID NAME PASSWORD- - - 1 aa 11111 2 bb 22222SQL delete from t1;已删除2行。SQL select * from t1;未选定行2、显示当前连接用户 SQL show user 3、查看系统拥有哪些用户 SQL select * from all_users; 4、新建用户并授权 SQL create user a identified by a;（默认建在SYSTEM表空间下） SQL grant connect,resource to a; 5、连接到新用户 SQL conn a/a 6、查询当前用户下所有对象 SQL select * from tab; 7、建立第一个表 SQL create table a(a number); 8、查询表结构 SQL desc a 9、插入新记录 SQL insert into a values(1); 10、查询记录 SQL select * from a; 11、更改记录 SQL update a set a=2; 12、删除记录 SQL delete from a; 13、回滚 SQL roll; SQL rollback; 14、提交 SQL commit; 用户授权: GRANT ALTER ANY INDEX TO user_id GRANT dba TO user_id ; ALTER USER user_id DEFAULT ROLE ALL 创建用户: CREATE USER user_id PROFILE DEFAULT IDENTIFIED BY DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK; GRANT CONNECT TO user_id ; 用户密码设定: ALTER USER CMSDB IDENTIFIED BY pass_word 表空间创建: CREATE TABLESPACE table_space LOGGING DATAFILE C:ORACLEORADATAdbstable_space.ora SIZE 5M - 1、查看当前所有对象 SQL select * from tab; 2、建一个和a表结构一样的空表 SQL create table b as select * from a where 1=2; SQL create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2; 3、察看数据库的大小，和空间使用情况 SQL col tablespace format a20 SQL select b.file_id文件ID, b.tablespace_name表空间, b.file_name物理文件名, b.bytes总字节数, (b.bytes-sum(nvl(a.bytes,0)已使用, sum(nvl(a.bytes,0)剩余, sum(nvl(a.bytes,0)/(b.bytes)*100剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes order by b.tablespace_name / dba_free_space -表空间剩余空间状况 dba_data_files -数据文件空间占用情况 4、查看现有回滚段及其状态 SQL col segment format a30 SQL SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS; 5、查看数据文件放置的路径 SQL col file_name format a50 SQL select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; 6、显示当前连接用户 SQL show user 7、把SQL*Plus当计算器 SQL select 100*20 from dual; 8、连接字符串 SQL select 列1 | |列2 from 表1; SQL select concat(列1,列2) from 表1; 9、查询当前日期 SQL select to_char(sysdate,yyyy-mm-dd,hh24:mi:ss) from dual; 10、用户间复制数据 SQL copy from user1 to user2 create table2 using select * from table1; 11、视图中不能使用order by，但可用group by代替来达到排序目的 SQL create view a as select b1,b2 from b group by b1,b2; 12、通过授权的方式来创建用户 SQL grant connect,resource to test identified by test; SQL conn test/test 13、查出当前用户所有表名。 select unique tname from col; - /* 向一个表格添加字段 */ alter table alist_table add address varchar2(100); /* 修改字段 属性 字段为空 */ alter table alist_table modify address varchar2(80); /* 修改字段名字 */ create table alist_table_copy as select ID,NAME,PHONE,EMAIL, QQ as QQ2, /*qq 改为qq2*/ ADDRESS from alist_table; drop table alist_table; rename alist_table_copy to alist_table /* 修改表名 */ 空值处理 有时要求列值不能为空 create table dept (deptno number(2) not null, dname char(14), loc char(13); 在基表中增加一列 alter table dept add (headcnt number(3); 修改已有列属性 alter table dept modify dname char(20); 注：只有当某列所有值都为空时，才能减小其列值宽度。 只有当某列所有值都为空时，才能改变其列值类型。 只有当某列所有值都为不空时，才能定义该列为not null。 例： alter table dept modify (loc char(12); alter table dept modify loc char(12); alter table dept modify (dname char(13),loc char(12); 查找未断连接 select process,osuser,username,mach