Oracle表的简单应用.doc

1、 1 / 13表的增删改查创建表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 数据类型DEF

2、AULT 表达式,列名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;名称 是否为空? 类型- - -I

3、D NUMBER(10)NAME VARCHAR2(20)2 / 13PASSWORD 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 (sa

4、l 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 t

5、1 modify (comm number(10);SQL desc t1;3 / 13名称 是否为空? 类型- - -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 tab

6、le 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以上版本适用 该列被设置成无用列后,并不是删除了而是设置成无用标记而已还

7、需要手工删除这个列 设置成无用列后,无法用sqlplus或是sql 语句看到的 oracle把设置成无用列当作删除列处理 可以适用DROP UNUSED (列名) 选项删除被设置成无用的列 因为该语句是DDL语句 没有恢复无用列的命令4 / 13然后 等系统不忙时在用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 ta

8、ble 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 删除表中的数据行后,立即释放磁盘空间如果只删除数

9、据,而保留数据结构 适用truncate 如果既要删除数据,也要删除表结构 适用DROP语法格式:DROP TABLE 表名特点: DROP删除表中的所有数据行和表的结构 DROP 也删除表的所有索引 提交所有挂起事务 所有基于被DROP的表的视图别名依然保留 但已经无效5 / 13对象表的重命名语法格式:RENAME 旧表名 TO 新表名SQL rename t1 to table1;SQL desc t1;ERROR:ORA-04043: 对象 t1 不存在SQL desc table1;名称 是否为空? 类型- - -ID NUMBER(10)NAME VARCHAR2(20)PASSW

10、ORD 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- - -6 /

11、 13DEPT 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 1

12、2342 bbUPDATE 语法格式：UPDATE 表名 SET 列名=值,列名=值 WHERE 条件;SQL select * from t1;ID NAME PASSWORD- - -1 aa 12342 bbSQL update t1 set password=5555 where id=1;已更新 1 行。SQL select * from t1;ID NAME PASSWORD- - -7 / 131 aa 55552 bb SQL update t1 set password=6666 where id=2;已更新 1 行。SQL select * from t1;ID NAME

13、PASSWORD- - -1 aa 55552 bb 6666DELETE 语法：DELETE FROM 表名 WHERE 条件;delete 语句是删除 表里的数据SQL select * from t1;ID NAME PASSWORD- - -1 aa 55552 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

14、t1 values (1,aa,11111);8 / 13SQL insert into t1 values (2,bb,22222);SQL select * from t1;ID NAME PASSWORD- - -1 aa 111112 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;（默认建在 SY

15、STEM 表空间下） 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、插入新记录 9 / 13SQL insert into a values(1); 10、查询记录 SQL select * from a; 11、更改记录 SQL update a set a=2; 12、删除记录 SQL delete from a; 13、回滚 SQL

16、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 “

17、 TO “user_id “; 用户密码设定: ALTER USER “CMSDB “ IDENTIFIED BY “pass_word “ 表空间创建: CREATE TABLESPACE “table_space “ LOGGING DATAFILE C:ORACLEORADATAdbstable_space.ora SIZE 5M - 1、查看当前所有对象 10 / 13SQL select * from tab; 2、建一个和 a 表结构一样的空表 SQL create table b as select * from a where 1=2; SQL create table b(b

18、1,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_

19、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_

20、ROLLBACK_SEGS; 5、查看数据文件放置的路径 SQL col file_name format a50 SQL select tablespace_name,file_id,bytes/1024/1024,file_name from 11 / 13dba_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,列

21、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,r

22、esource 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,NAM

23、E,PHONE,EMAIL, QQ as QQ2, /*qq 改为 qq2*/ ADDRESS from alist_table; 12 / 13drop 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); 修改已有列属性 alt

24、er 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,machine,l

25、ogon_time ,sql_text from v$session a,v$sqltext b where a.sql_address=b.address; 1.以 USER_开始的数据字典视图包含当前用户所拥有的信息, 查询当前用户所拥有的表信息: select * from user_tables; 2.以 ALL_开始的数据字典视图包含 ORACLE 用户所拥有的信息, 查询用户拥有或有权访问的所有表信息: select * from all_tables; 3.以 DBA_开始的视图一般只有 ORACLE 数据库管理员可以访问: 13 / 13select * from dba_ta

26、bles; 4.查询 ORACLE 用户： conn sys/change_on_install select * from dba_users; conn system/manager; select * from all_users; 5.创建数据库用户： CREATE USER user_name IDENTIFIED BY password; GRANT CONNECT TO user_name; GRANT RESOURCE TO user_name; 授权的格式: grant (权限) on tablename to username; 删除用户(或表): drop user(table) username(tablename) (cascade); 6.向建好的用户导入数据表 IMP SYSTEM/MANAGER FROMUSER = FUSER_NAME TOUSER = USER_NAME FILE = C:EXPDAT.DMP COMMIT = Y 7.索引 create index index_name on table_name( “column_name “)