第一篇：黑马程序员c语言教程：ORACLE 常用的SQL语法和数据对象
ORACLE 常用的SQL语法和数据对象 一.数据控制语句(DML)部分
1.INSERT(往数据表里插入记录的语句)INSERT INTO 表名(字段名1, 字段名2, ……)VALUES(值1, 值2, ……);INSERT INTO 表名(字段名1, 字段名2, ……)SELECT(字段名1, 字段名2, ……)FROM 另外的表名;字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’
如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个单引号''.字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验.日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒
或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC.年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型, 方法借用ORACLE里自带的DBMS_LOB程序包.INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号
CREATE SEQUENCE 序列号的名称(最好是表名+序列号标记)INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6), 最大值为999999 INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL 2.DELETE(删除数据表里记录的语句)DELETE FROM表名 WHERE 条件;注意：删除记录并不能释放ORACLE里被占用的数据块表空间.它只把那些被删除的数据块标成unused.如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间
TRUNCATE TABLE 表名;此操作不可回退.3.UPDATE(修改数据表里记录的语句)UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;值N超过定义的长度会出错, 最好在插入前进行长度校验..注意事项: A.以上SQL语句对表都加上了行级锁, 确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效, 否则改变不一定写入数据库里.如果想撤回这些操作, 可以用命令 ROLLBACK 复原.B.在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围，应该把它限定在较小(一万条记录)范围内,.否则ORACLE处理这个事物用到很大的回退段.程序响应慢甚至失去响应.如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成, 其间加上COMMIT 确认事物处理.二.数据定义(DDL)部分
1.CREATE(创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)ORACLE常用的字段类型有
CHAR 固定长度的字符串 VARCHAR2 可变长度的字符串
NUMBER(M,N)数字型M是位数总长度, N是小数的长度 DATE 日期类型
创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面 创建表时可以用中文的字段名, 但最好还是用英文的字段名 创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE 这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间 创建表时可以给字段加上约束条件
例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY 2.ALTER(改变表, 索引, 视图等)改变表的名称
ALTER TABLE 表名1 TO 表名2;在表的后面增加一个字段
ALTER TABLE表名 ADD 字段名 字段名描述;修改表里字段的定义描述
ALTER TABLE表名 MODIFY字段名 字段名描述;给表里的字段加上约束条件
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(字段名);ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(字段名);把表放在或取出数据库的内存区 ALTER TABLE 表名 CACHE;ALTER TABLE 表名 NOCACHE;3.DROP(删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)删除表和它所有的约束条件
DROP TABLE 表名 CASCADE CONSTRAINTS;4.TRUNCATE(清空表里的所有记录, 保留表的结构)TRUNCATE 表名;三.查询语句(SELECT)部分
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 条件;字段名可以带入函数
例如: COUNT(*), MIN(字段名), MAX(字段名), AVG(字段名), DISTINCT(字段名), TO_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS')NVL(EXPR1, EXPR2)函数
解释: IF EXPR1=NULL RETURN EXPR2 ELSE RETURN EXPR1 DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数 解释: IF AA=V1 THEN RETURN R1 IF AA=V2 THEN RETURN R2..… ELSE RETURN NULL LPAD(char1,n,char2)函数 解释: 字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位 字段名之间可以进行算术运算 例如:(字段名1*字段名1)/3 查询语句可以嵌套
例如: SELECT …… FROM(SELECT …… FROM表名1, [表名2, ……] WHERE 条件)WHERE 条件2;两个查询语句的结果可以做集合操作
例如: 并集UNION(去掉重复记录), 并集UNION ALL(不去掉重复记录), 差集MINUS, 交集INTERSECT 分组查询
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] GROUP BY字段名1 [HAVING 条件];两个以上表之间的连接查询
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 表名1.字段名 = 表名2.字段名 [ AND ……];SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 表名1.字段名 = 表名2.字段名(+)[ AND ……];有(+)号的字段位置自动补空值
查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESC SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] ORDER BY字段名1, 字段名2 DESC;字符串模糊比较的方法
INSTR(字段名, ‘字符串’)>0 字段名 LIKE ‘字符串%’ [‘%字符串%’] 每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性.四.ORACLE里常用的数据对象(SCHEMA)1.索引(INDEX)CREATE INDEX 索引名ON 表名(字段1, [字段2, ……]);
ALTER INDEX 索引名 REBUILD;一个表的索引最好不要超过三个(特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况, 也可以建立多字段的组合索引和基于函数的索引
ORACLE8.1.7字符串可以索引的最大长度为1578 单字节 ORACLE8.0.6字符串可以索引的最大长度为758 单字节--MartriWang@gmail.com 10/05/2007--（1）*Tree索引。
Create index indexname on tablename(columnname[columnname...])（2）反向索引。
Create index indexname on tablename(columnname[columnname...])reverse（3）降序索引。
Create index indexname on tablename(columnname DESC[columnname...])（4）位图索引。
Create BITMAP index indexname on tablename(columnname[columnname...])（5）函数索引。
Create index indexname on tablename(functionname(columnname))注意：创建索引后分析要索引才能起作用。
analyze index indexname compute statistics;
2.视图(VIEW)CREATE VIEW 视图名AS SELECT ….FROM …..;ALTER VIEW视图名 COMPILE;视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化.3.同义词(SYNONMY)CREATE SYNONYM同义词名FOR 表名;CREATE SYNONYM同义词名FOR 表名@数据库链接名;4.数据库链接(DATABASE LINK)CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘数据库连接字符串’;--MartriWang@gmail.com 10/05/2007--create database mynewdb user sys IDENTIFIED BY sys_id user system IDENTIFIED BY system_id LOGFILE GROUP 1('/u01/oracle/oradata/mynewdb/redo01.log')SIZE 100M, GROUP 2('/u01/oracle/oradata/mynewdb/redo02.log')SIZE 100M, GROUP 3('/u01/oracle/oradata/mynewdb/redo03.log')SIZE 100M MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXINSTANCES 100 MAXINSTANCES 1 CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16 DATAFILE '/u01/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE EXTENT MANAGEMENT LOCAL SYSAUT DATAFILE '/u01/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE tbs_1 DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE undotbs DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf' SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED--MartriWang@gmail.com 10/05/2007--emctl start dbconsole emctl stop dbconsole--http://servername:port/em--path:$ORACLE_HOME/install/portlist.ini isqlplusctl start isqlplusctl stop--http://servername:port/isqlplus 数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义.数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样 数据库全局名称可以用以下命令查出 SELECT * FROM GLOBAL_NAME;查询远端数据库里的表
SELECT …… FROM 表名@数据库链接名;五.权限管理(DCL)语句 1.GRANT 赋于权限
常用的系统权限集合有以下三个: CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)常用的数据对象权限有以下五个: ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名, DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名
GRANT CONNECT, RESOURCE TO 用户名;GRANT SELECT ON 表名 TO 用户名;GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;2.REVOKE 回收权限
REVOKE CONNECT, RESOURCE FROM 用户名;REVOKE SELECT ON 表名 FROM 用户名;REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2;
查询数据库中第63号错误：
select orgaddr,destaddr from sm_histable0116 where error_code='63';查询数据库中开户用户最大提交和最大下发数： select MSISDN,TCOS,OCOS from ms_usertable；
查询数据库中各种错误代码的总和：
select error_code,count(*)from sm_histable0513 group by error_code order by error_code;查询报表数据库中话单统计种类查询。
select sum(Successcount)from tbl_MiddleMt0411 where ServiceType2=111 select sum(successcount),servicetype from tbl_middlemt0411 group by servicetype
原文地址：http://www.xiexiebang.com/viewthread.php?tid=60293 //创建一个控制文件命令到跟踪文件
alter database backup controlfile to trace;//增加一个新的日志文件组的语句 connect internal as sysdba alter database add logfile group 4(’/db01/oracle/CC1/log_1c.dbf’, ’/db02/oracle/CC1/log_2c.dbf’)size 5M;alter database add logfile member ’/db03/oracle/CC1/log_3c.dbf’ to group 4;//在Server Manager上MOUNT并打开一个数据库: connect internal as sysdba startup mount ORA1 exclusive;alter database open;//生成数据字典 @catalog @catproc //在init.ora 中备份数据库的位置
log_archive_dest_1 = ’/db00/arch’ log_archive_dest_state_1 = enable log_archive_dest_2 = “service=stby.world mandatory reopen=60” log_archive_dest_state_2 = enable //对用户的表空间的指定和管理相关的语句
create user USERNAME identified by PASSWORD default tablespace TABLESPACE_NAME;alter user USERNAME default tablespace TABLESPACE_NAME;alter user SYSTEM quota 0 on SYSTEM;alter user SYSTEM quota 50M on TOOLS;
create user USERNAME identified by PASSWORD default tablespace DATA temporary tablespace TEMP;alter user USERNAME temporary tablespace TEMP;//重新指定一个数据文件的大小 : alter database datafile ’/db05/oracle/CC1/data01.dbf’ resize 200M;//创建一个自动扩展的数据文件: create tablespace DATA datafile ’/db05/oracle/CC1/data01.dbf’ size 200M autoextend ON next 10M maxsize 250M;//在表空间上增加一个自动扩展的数据文件: alter tablespace DATA add datafile ’/db05/oracle/CC1/data02.dbf’ size 50M autoextend ON maxsize 300M;//修改参数: alter database datafile ’/db05/oracle/CC1/data01.dbf’ autoextend ON maxsize 300M;//在数据文件移动期间重新命名: alter database rename file ’/db01/oracle/CC1/data01.dbf’ to ’/db02/oracle/CC1/data01.dbf’;alter tablespace DATA rename datafile ’/db01/oracle/CC1/data01.dbf’ to ’/db02/oracle/CC1/data01.dbf’;alter database rename file ’/db05/oracle/CC1/redo01CC1.dbf’ to ’/db02/oracle/CC1/redo01CC1.dbf’;alter database datafile ’/db05/oracle/CC1/data01.dbf’ resize 80M;//创建和使用角色: create role APPLICATION_USER;grant CREATE SESSION to APPLICATION_USER;grant APPLICATION_USER to username;//回滚段的管理
create rollback segment SEGMENT_NAME tablespace RBS;
alter rollback segment SEGMENT_NAME offline;drop rollback segment SEGMENT_NAME;alter rollback segment SEGMENT_NAME online;//回滚段上指定事务 commit;set transaction use rollback segment ROLL_BATCH;insert into TABLE_NAME select * from DATA_LOAD_TABLE;commit;//查询回滚段的 大小和优化参数 select * from DBA_SEGMENTS where Segment_Type = ’ROLLBACK’;select N.Name, /* rollback segment name */ S.OptSize /* rollback segment OPTIMAL size */ from V$ROLLNAME N, V$ROLLSTAT S where N.USN=S.USN;//回收回滚段
alter rollback segment R1 shrink to 15M;alter rollback segment R1 shrink;//例子
set transaction use rollback segment SEGMENT_NAME alter tablespace RBS default storage(initial 125K next 125K minextents 18 maxextents 249)create rollback segment R4 tablespace RBS storage(optimal 2250K);alter rollback segment R4 online;select Sessions_Highwater from V$LICENSE;grant select on EMPLOYEE to PUBLIC;//用户和角色
create role ACCOUNT_CREATOR;grant CREATE SESSION, CREATE USER, ALTER USER to ACCOUNT_CREATOR;alter user THUMPER default role NONE;alter user THUMPER default role CONNECT;alter user THUMPER default role all except ACCOUNT_CREATOR;alter profile DEFAULT limit idle_time 60;create profile LIMITED_PROFILE limit FAILED_LOGIN_ATTEMPTS 5;create user JANE identified by EYRE profile LIMITED_PROFILE;grant CREATE SESSION to JANE;
alter user JANE account unlock;alter user JANE account lock;alter profile LIMITED_PROFILE limit PASSWORD_LIFE_TIME 30;alter user jane password expire;//创建操作系统用户
REM Creating OPS$ accounts create user OPS$FARMER identified by SOME_PASSWORD default tablespace USERS temporary tablespace TEMP;REM Using identified externally create user OPS$FARMER identified externally default tablespace USERS temporary tablespace TEMP;//执行ORAPWD ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users create role APPLICATION_USER;grant CREATE SESSION to APPLICATION_USER;create role DATA_ENTRY_CLERK;grant select, insert on THUMPER.EMPLOYEE to DATA_ENTRY_CLERK;grant select, insert on THUMPER.TIME_CARDS to DATA_ENTRY_CLERK;grant select, insert on THUMPER.DEPARTMENT to DATA_ENTRY_CLERK;grant APPLICATION_USER to DATA_ENTRY_CLERK;grant DATA_ENTRY_CLERK to MCGREGOR;grant DATA_ENTRY_CLERK to BPOTTER with admin option;//设置角色
set role DATA_ENTRY_CLERK;set role NONE;//回收权利: revoke delete on EMPLOYEE from PETER;revoke all on EMPLOYEE from MCGREGOR;//回收角色: revoke ACCOUNT_CREATOR from HELPDESK;drop user USERNAME cascade;grant SELECT on EMPLOYEE to MCGREGOR with grant option;grant SELECT on THUMPER.EMPLOYEE to BPOTTER with grant option;revoke SELECT on EMPLOYEE from MCGREGOR;create user MCGREGOR identified by VALUES ’1A2DD3CCEE354DFA’;alter user OPS$FARMER identified by VALUES ’no way’;//备份与恢复
使用 export 程序
exp system/manager file=expdat.dmp compress=Y owner=(HR,THUMPER)exp system/manager file=hr.dmp owner=HR indexes=Y compress=Y imp system/manager file=hr.dmp full=Y buffer=64000 commit=Y--MartriWang@gmail.com 14/05/2007--1 将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中
exp system/manager@TEST file=d:daochu.dmp full=y 2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:daochu.dmp owner=(system,sys)3 将数据库中的表table1、table2导出
exp system/manager@TEST file=d:daochu.dmp tables=(table1,table2)4 将数据库中的表table1中的字段filed1以“00”打头的数据导出
exp system/manager@TEST file=d:daochu.dmp tables=(table1)query=“ where filed1 like '00%'”--MartriWang@gmail.com 14/05/2007--1 将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中
exp system/manager@TEST file=d:daochu.dmp full=y 2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:daochu.dmp owner=(system,sys)3 将数据库中的表table1、table2导出
exp system/manager@TEST file=d:daochu.dmp tables=(table1,table2)4 将数据库中的表table1中的字段filed1以“00”打头的数据导出
exp system/manager@TEST file=d:daochu.dmp tables=(table1)query=“ where filed1 like '00%'”
//备份表
exp system/manager FILE=expdat.dmp TABLES=(Thumper.SALES)//备份分区
exp system/manager FILE=expdat.dmp TABLES=(Thumper.SALES:Part1)//输入例子
imp system/manager file=expdat.dmp imp system/manager file=expdat.dmp buffer=64000 commit=Y exp system/manager file=thumper.dat owner=thumper grants=N indexes=Y compress=Y rows=Y imp system/manager file=thumper.dat FROMUSER=thumper TOUSER=flower rows=Y indexes=Y imp system/manager file=expdat.dmp full=Y commit=Y buffer=64000 imp system/manager file=expdat.dmp ignore=N rows=N commit=Y buffer=64000 //使用操作系统备份命令 REM TAR examples tar-cvf /dev/rmt/0hc /db0[1-9]/oracle/CC1
tar-rvf /dev/rmt/0hc /orasw/app/oracle/CC1/pfile/initcc1.ora tar-rvf /dev/rmt/0hc /db0[1-9]/oracle/CC1 /orasw/app/oracle/CC1/pfile/initcc1.ora //离线备份的shell脚本
ORACLE_SID=cc1;export ORACLE_SID ORAENV_ASK=NO;export ORAENV_ASK.oraenv svrmgrl <
//在Server Manager上设置为archivelog mode: connect internal as sysdba startup mount cc1;alter database noarchivelog;alter database open;archive log list select Name, Value from V$PARAMETER where Name like ’log_archive%’;//联机备份的脚本 # # Sample Hot Backup Script for a UNIX File System database # # Set up environment variables: ORACLE_SID=cc1;export ORACLE_SID
ORAENV_ASK=NO;export ORAENV_ASK.oraenv svrmgrl <
REM of the tablespaces.REM REM REM Step 2.备份归档日志文件.archive log stop REM REM Exit Server Manager, using the indicator set earlier.exit EOFarch1 # # Record which files are in the destination directory.# Do this by setting an environment variable that is # equal to the directory listing for the destination # directory.# For this example, the log_archive_dest is # /db01/oracle/arch/CC1.# FILES=`ls /db01/oracle/arch/CC1/arch*.dbf`;export FILES # # Now go back into Server Manager and restart the # archiving process.Set an indicator(called EOFarch2 # in this example).# svrmgrl <
# # 备份控制文件到磁带.# tar-rvf /dev/rmt/0hc /db01/oracle/CC1/CC1controlfile.bck # # End of hot backup script.//自动生成开始备份的脚本
set pagesize 0 feedback off select ’alter tablespace ’||Tablespace_Name||’ begin backup;’ from DBA_TABLESPACES where Status <> ’INVALID’ spool alter_begin.sql / spool off //自动生成备份结束的脚本
set pagesize 0 feedback off select ’alter tablespace ’||Tablespace_Name||’ end backup;’ from DBA_TABLESPACES where Status <> ’INVALID’ spool alter_end.sql / spool off //备份归档日志文件的脚本.REM See text for alternatives.# Step 1: Stop the archiving process.This will keep # additional archived redo log files from being written # to the destination directory during this process.# svrmgrl <
# /db01/oracle/arch/CC1.# FILES=`ls /db01/oracle/arch/CC1/arch*.dbf`;export FILES # # Step 3: Go back into Server Manager and restart the # archiving process.Set an indicator(called EOFarch2 # in this example).# svrmgrl <
//监视器的管理 lsnrctl start lsnrctl start my_lsnr lsnrctl status lsnrctl status hq 检查监视器的进程
ps-ef | grep tnslsnr //在 lsnrctl 内停止监视器 set password lsnr_password stop //在lsnrctl 内列出所有的服务 set password lsnr_password services //启动或停止一个NT的listener net start Oracle
(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = server1.fld.com)(PORT = 1521)))(CONNECT_DATA =(SID = fld1)))//操作系统网络的管理 telnet host_name ping host_name /etc/hosts 文件
130.110.238.109 nmhost 130.110.238.101 txhost 130.110.238.102 azhost arizona //oratab 表项
loc:/orasw/app/oracle/product/8.1.5.1:Y cc1:/orasw/app/oracle/product/8.1.5.1:N old:/orasw/app/oracle/product/8.1.5.0:Y //创建一个控制文件命令到跟踪文件
alter database backup controlfile to trace;//增加一个新的日志文件组的语句 connect internal as sysdba alter database add logfile group 4(’/db01/oracle/CC1/log_1c.dbf’, ’/db02/oracle/CC1/log_2c.dbf’)size 5M;alter database add logfile member ’/db03/oracle/CC1/log_3c.dbf’ to group 4;//在Server Manager上MOUNT并打开一个数据库: connect internal as sysdba startup mount ORA1 exclusive;alter database open;//生成数据字典 @catalog @catproc //在init.ora 中备份数据库的位置
log_archive_dest_1 = ’/db00/arch’ log_archive_dest_state_1 = enable log_archive_dest_2 = “service=stby.world mandatory reopen=60” log_archive_dest_state_2 = enable
//对用户的表空间的指定和管理相关的语句
create user USERNAME identified by PASSWORD default tablespace TABLESPACE_NAME;alter user USERNAME default tablespace TABLESPACE_NAME;alter user SYSTEM quota 0 on SYSTEM;alter user SYSTEM quota 50M on TOOLS;create user USERNAME identified by PASSWORD default tablespace DATA temporary tablespace TEMP;alter user USERNAME temporary tablespace TEMP;//重新指定一个数据文件的大小 : alter database datafile ’/db05/oracle/CC1/data01.dbf’ resize 200M;//创建一个自动扩展的数据文件: create tablespace DATA datafile ’/db05/oracle/CC1/data01.dbf’ size 200M autoextend ON next 10M maxsize 250M;//在表空间上增加一个自动扩展的数据文件: alter tablespace DATA add datafile ’/db05/oracle/CC1/data02.dbf’ size 50M autoextend ON maxsize 300M;//修改参数: alter database datafile ’/db05/oracle/CC1/data01.dbf’ autoextend ON maxsize 300M;//在数据文件移动期间重新命名: alter database rename file ’/db01/oracle/CC1/data01.dbf’ to ’/db02/oracle/CC1/data01.dbf’;alter tablespace DATA rename datafile ’/db01/oracle/CC1/data01.dbf’ to ’/db02/oracle/CC1/data01.dbf’;alter database rename file ’/db05/oracle/CC1/redo01CC1.dbf’ to ’/db02/oracle/CC1/redo01CC1.dbf’;alter database datafile ’/db05/oracle/CC1/data01.dbf’ resize 80M;//创建和使用角色:
create role APPLICATION_USER;grant CREATE SESSION to APPLICATION_USER;grant APPLICATION_USER to username;//回滚段的管理
create rollback segment SEGMENT_NAME tablespace RBS;alter rollback segment SEGMENT_NAME offline;drop rollback segment SEGMENT_NAME;alter rollback segment SEGMENT_NAME online;//回滚段上指定事务 commit;set transaction use rollback segment ROLL_BATCH;insert into TABLE_NAME select * from DATA_LOAD_TABLE;commit;//查询回滚段的 大小和优化参数 select * from DBA_SEGMENTS where Segment_Type = ’ROLLBACK’;select N.Name, /* rollback segment name */ S.OptSize /* rollback segment OPTIMAL size */ from V$ROLLNAME N, V$ROLLSTAT S where N.USN=S.USN;//回收回滚段
alter rollback segment R1 shrink to 15M;alter rollback segment R1 shrink;//例子
set transaction use rollback segment SEGMENT_NAME alter tablespace RBS default storage(initial 125K next 125K minextents 18 maxextents 249)create rollback segment R4 tablespace RBS storage(optimal 2250K);alter rollback segment R4 online;select Sessions_Highwater from V$LICENSE;grant select on EMPLOYEE to PUBLIC;//用户和角色
create role ACCOUNT_CREATOR;grant CREATE SESSION, CREATE USER, ALTER USER to ACCOUNT_CREATOR;alter user THUMPER default role NONE;alter user THUMPER default role CONNECT;alter user THUMPER default role all except ACCOUNT_CREATOR;alter profile DEFAULT
limit idle_time 60;create profile LIMITED_PROFILE limit FAILED_LOGIN_ATTEMPTS 5;create user JANE identified by EYRE profile LIMITED_PROFILE;grant CREATE SESSION to JANE;alter user JANE account unlock;alter user JANE account lock;alter profile LIMITED_PROFILE limit PASSWORD_LIFE_TIME 30;alter user jane password expire;//创建操作系统用户
REM Creating OPS$ accounts create user OPS$FARMER identified by SOME_PASSWORD default tablespace USERS temporary tablespace TEMP;REM Using identified externally create user OPS$FARMER identified externally default tablespace USERS temporary tablespace TEMP;//执行ORAPWD ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users create role APPLICATION_USER;grant CREATE SESSION to APPLICATION_USER;create role DATA_ENTRY_CLERK;grant select, insert on THUMPER.EMPLOYEE to DATA_ENTRY_CLERK;grant select, insert on THUMPER.TIME_CARDS to DATA_ENTRY_CLERK;grant select, insert on THUMPER.DEPARTMENT to DATA_ENTRY_CLERK;grant APPLICATION_USER to DATA_ENTRY_CLERK;grant DATA_ENTRY_CLERK to MCGREGOR;grant DATA_ENTRY_CLERK to BPOTTER
第二篇：黑马程序员c语言教程：过滤和排序数据
过滤和排序数据
在查询中过滤行
过滤
字符和日期
字符和日期要包含在单引号中。字符大小写敏感，日期格式敏感。默认的日期格式是 DD-MON-RR。
比较运算
其它比较运算
第三篇：黑马程序员c语言教程：过滤和排序数据
ORDER BY子句
降序排序
按别名排序
多个列排序
ORDER BY子句
排序的规则
可以按照select语句中的列名排序 可以按照别名列名排序
可以按照select语句中的列名的顺序值排序 如果要按照多列进行排序，则规则是先按照第一列排序，如果相同，则按照第二列排序；以此类推
Desc:
总结
第四篇：黑马程序员c语言教程：Oracle简介
9.通过子查询建表 通过子查询建表的例子
SQL>CREATE TABLE emp_41 AS SELECT id, last_name, userid, start_date FROM s_emp WHERE dept_id = 41;SQL> CREATE TABLE A as select * from B where 1=2;只要表的结构.10.用子查询建表的注意事项
可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。
用子查询方式建立的表，只有非空NOT NULL的约束条件能继承过来, 其它的约束条件和默认值都没有继承过来.根据需要，可以用alter table add constraint ……再建立其它的约束条件，如primary key等.11.Foreign Key的可选参数ON DELETE CASCADE 在创建Foreign Key时可以加可选参数: ON DELETE CASCADE它的含义是如果删除外键主表里的内容，子表里相关的内容将一起被删除.如果没有ON DELETE CASCADE参数，子表里有内容，父表里的主关键字记录不能被删除掉.12.如果数据库表里有不满足的记录存在，建立约束条件将不会成功.13.给表创建和删除同义词的例子 SQL> CREATE SYNONYM d_sum 2 FOR dept_sum_vu;SQL> CREATE PUBLIC SYNONYM s_dept 2 FOR alice.s_dept;SQL> DROP SYNONYM s_dept;
十、ORACLE里的数据字典
1.什么是数据字典?ORACLE的数据字典是数据库的重要组成部分之一，它随着数据库 的产生而产生, 随着数据库的变化而变化, 体现为sys用户下所有的一些表和视图.2.数据字典里存了以下内容： 用户信息
用户的权限信息
所有数据对象信息表的约束条件统计分析数据库的视图等 不能手工修改数据字典里的信息.16
J2EE @ zxw 3.常用的数据字典
Dictionary 存放所有数据表，视图，同义词名称和解释 Dict_columns 数据字典里字段名称的和解释 Dba_users 用户 Dba_tablespaces 表空间
Dba_data_files 数据库的文件 Dba_free_space 空闲表空间 Dba_rollback_segs 回滚段
User_objects 数据对象 User_constraints 约束条件 User_sequences 序列号 User_views 视图 User_indexes 索引 User_synonyms 同义词
Session_roles 用户的角色 User_role_privs 用户的角色权限 User_sys_privs 用户的系统权限 User_tab_privs 用户的表级权限 V$session 实时用户情况 V$sysstat 实时系统统计 V$sesstat 实时用户统计 V$sgastat 实时SGA使用 V$locked_object 实时锁 V$controlfile 控制文件 V$logfile 日志文件 V$parameter 参数文件 4.数据字典的分类 数据字典四大类别
User_ 用户下所有数据库对象
All_ 用户权限范围内所有的数据库对象 Dba_ 所有的数据库对象
V$Content$nbsp;统计分析数据库的视图 赋于oem_monitor权限非DBA用户也可查询V$*视图
5.查询数据字典
SQL> select * from dictionary where instr(comments,'index')>0;SQL> select constraint_name, constraint_type, 2 search_condition, r_constraint_name 3 from user_constraints 4 where table_name = ‘&table_name';十一.控制数据、INSERT(往数据表里插入记录的语句)SQL> insert into 表名(字段名1, 字段名2, ……)values(值1, 值2, ……);SQL> insert into 表名(字段名1, 字段名2, ……)select(字段名1, 字段名2, ……)from 另外的表名 where 条件;可以用&标记变量的方法多次输入记录
快速插入数据的方法, 一般用于大于128M的数据转移 SQL> insert /*+ append */ into 表名
select * from 另外的用户名.另外的表名 WHERE 条件;SQL> commit;注意事项：
用INSERT /*+ APPEND */ 的方法会对target_tablename产生级别为6的独占锁，如果运行此命令时还有对target_tablename的DML操作会排队在它后面, 对OLTP系统在用的表操作是不合适的。17
J2EE @ zxw 2.插入字符串类型的字段的注意事项: 字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’
如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个 单引号’ ’
字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验 ‘’ 标记是NULL, user 标明当前用户
日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒
用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC.年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS NSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型, 方法借用ORACLE里自带的DBMS_LOB程序包.3、UPDATE(修改数据表里记录的语句)SQL> UPDATE 表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;值N超过定义的长度会出错, 最好在插入前进行长度校验.新功能，可以修改子查询后的结果集
例子：SQL> update(select * from s_dept)set id=50 where id=60;
4、DELETE(删除数据表里记录的语句)SQL> DELETE FROM 表名 WHERE 条件;注意：删除记录并不能释放ORACLE里被占用的数据块表空间.它只把那些 被删除的数据块标成unused.如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间
SQL> TRUNCATE TABLE 表名;此操作不可回退.5、SQL语句的分类 数据定义语言(DDL)：create、alter、drop（创建、修改结构、删除）（其他：rename）数据操纵语言(DML)：insert、delete、select、update（增、删、查、改）（其他：truncate）
数据控制语言(DCL)：grant、revoke（授权、回收）、set role 事务控制：commit、rollback、savepoint（其他：lock table、set constraint、set transaction）
审计控制：audit、noaudit 系统控制：alter system 会话控制：alter session 其他语句：comment（添加注释）、explain plan、analyze、validate、call
6、ORACLE里事务控制 Commit 提交事务 Rollback 回退事务
Savepoint 设置断点, 在事务中标记位置, 事务结束, 断点释放
事务结束的情况遇到commit或者rollback遇到DDL和DCL语句发现错误，如死锁用户退出
SQL*PLUS系统重启或崩溃 7.DML操作的注意事项 18
J2EE @ zxw 以上SQL语句对表都加上了行级锁, 确认完成后, 必须加上事物处理结束的命令COMMIT 才能正式生效, 否则改变不一定写入数据库里.行级锁也未能得到释放.如果想撤回这些操作, 可以用命令 ROLLBACK 复原.在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围, 应该把它限定在较小(一万条记录)范围内,.否则ORACLE处理这个事物用到很大的回退段.程序响应慢甚至失去响应.如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成, 其间加上COMMIT 确认事物处理.太过频繁的commit不好
第五篇：黑马程序员c语言教程：DML语言强化
sql语言的类型
数据语言实现数据的crud
DML语句(Data Manipulation Language)数据库操作语言
insert update delete select DDL语言 data definition Lanaguage
create table
create view index sequence synonym同义词
truncate table DCL语言 data control language数据语言
commit rollback savetpointe
OCA认证 OCP(dba)一起考, 参加oracle的培训 2w Oracle数据库管理员认证专员（OCA）：Oracle Certified Associate Oracle数据库管理员认证专家（OCP）： Oracle Certified Professional Oracle数据库管理员认证大师（OCM）： Oracle Certified Master
语法: INSERT INTO table [(column [, column...])] VALUES(value [, value...]);
--1 向表中插入所有列
insert into emp values(1, 'tom_111', 'enginerr', 7839, sysdate, 5000, 100, 10)--2 向表中插入部分列
insert into emp(empno, ename, job, hiredate)values(2, 'tom_2222', 'enginerr', sysdate)
--3 隐式插入空值: 没有写出的列,默认为null值--4 显示插入空值:--5 sql插入语言的地址符 & 取地址符后面相当于一个变量
mysql语言: preparedStatement语言 SQL> insert into emp(empno, ename, job, hiredate)values(&empno, &ename, &job, &hiredate);输入 empno 的值: 3
输入 ename 的值: 'tom_ccc' 输入 job 的值: 'job' 输入 hiredate 的值: '22-2月-81' 原值 1: insert into emp(empno, ename, job, hiredate)values(&empno, &ename, &job, &hiredate)新值 1: insert into emp(empno, ename, job, hiredate)values(3, 'tom_ccc', 'job', '22-2月-81')
&地址符, 可以在任何sql语言中使用.在select 语言中也可以使用地址符
SQL> select ename, job, &tt from emp;输入 tt 的值: deptno 原值 1: select ename, job, &tt from emp 新值 1: select ename, job, deptno from emp
--7 批量插入数据--创建一张表
create table emp10 as
select * from emp where 1=2;--一次性的将emp中所有10号部门的员工插入到emp10中
--在insert语言中使用子查询 子查询 不光用在查询套查询, 也可用在DML语句套select语句
insert into emp10--列完全一样,可以不写
select * from emp where deptno=10;delete from emp10;
有关update更新语句
UPDATE table SET column = value [, column = value,...] [WHERE condition];--列子
SQL> update emp10 set sal=2300 where empno=7934;
有关删除数据
DELETE [FROM] table [WHERE condition];
delete和truncate表区别
delete逐条删除 truncate先摧毁表再重建delete语言是DML语言 truncate是DDL
DML语言可以闪回 做错的并且提交了.可通过闪回,撤销操作
DDL语言不可以闪回 flashback
delete是逐条删除,会产生碎片, truncate不会产生碎片
行移动功能: 要开启闪回功能,必须要开启行移动功能delete不会释放空间 truncate会delete可以回滚 truncate不可以
oracle delete快 mysql truncate快.实验: 从文件中导入数据, 通过命名delete 和 truncate删除表数据 实验
从文件中导入数据
SQL> set timing off;SQL> set feedback off;SQL> drop table testdelete purge;SQL> @c:Sql.sql;SQL> SQL> set timing on;//最后删除数据表的时候,把时间打开,记录时间
SQL> delete from testdelete;已用时间: 00: 00: 00.03
从文件中导入数据
SQL> set timing off;@c:Sql.sql;
set timing off;
select count(*)from testdelete;set timing on;//最后删除数据表的时候,把时间打开,记录时间
truncate table testdelete;已用时间: 00: 00: 00.39
事务基本概念
概念
一个或者多个DML语言组成特点
要么都成功,要么都失败
特性
原子性 一致性 隔离性 持久性
事物的隔离性 多个客户端同时操作数据库的时, 要隔离他们的操作
否则:脏读 不可重复读 幻读
设置不同的搁置级别来解决oracle中的事务
事务起始标志 DML语言(oracle默认事务似是打开的)2 事务的结束标志
提交: 显示提交commit
隐式提交
1)执行DDL语言
eg create table语言 还有I个隐式的功能
提交之前的没有提交的DML语言(insert update)
2)正常退出
回滚: 显示 rollback
隐式 掉电/宕机/非正常退出==系统出错了 oracle事务控制--保存点
create table testsavepoint(tid number, tname varchar2(20));set feedback on
insert into testsavepoint values(1, 'tom1');insert into testsavepoint values(2, 'tom2');savepoint a;//创建一个保存点
insert into testsavepoint values(3, 'tom3a);rollback to savepoint a;
注意: 回退到savepoint a 前面插入的两条sql语句还没有提交;仍然还在一个事务里面;让事务结束的方法 显示 隐式 SQL> rollback / commit oracle数据的隔离级别问题
read only ,数据库几乎不做串行化操作,增加了read only SQL99
点击咨询 