精华内容
下载资源
问答
  • oracle数据库基本语句

    2015-06-08 20:05:22
    oracle中几本的sql语句的汇总练习
  • oracle数据库基本语句一、Oracle数据库操作1、创建数据库create database databasename2、删除数据库drop database dbname3、备份数据库完全备份exp demo/demo@orcl buffer=1024 file=d:\back.dmp full=ydemo:...

    9edf6564098922d06ae53b7e97f05756.png

    oracle数据库基本语句

    一、Oracle数据库操作

    1、创建数据库create database databasename

    2、删除数据库drop database dbname

    3、备份数据库

    完全备份exp demo/demo@orcl buffer=1024 file=d:\back.dmp full=ydemo:用户名、密码

    buffer: 缓存大小

    file: 具体的备份文件地址

    full: 是否导出全部文件

    ignore: 忽略错误,如果表已经存在,则也是覆盖

    将数据库中system用户与sys用户的表导出exp demo/demo@orcl file=d:\backup\1.dmp owner=(system,sys)

    导出指定的表exp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

    按过滤条件,导出exp demo/demo@orcl file=d:\back.dmp tables=(table1) query=\" where filed1 like 'fg%'\"

    导出时可以进行压缩;命令后面 加上 compress=y ;如果需要日志,后面:log=d:\log.txt

    备份远程服务器的数据库exp 用户名/密码@远程的IP:端口/实例 file=存放的位置:\文件名称.dmp full=y

    4、数据库还原

    打开cmd直接执行如下命令,不用再登陆sqlplus。

    完整还原imp demo/demo@orcl file=d:\back.dmp full=y ignore=y log=D:\implog.txt

    指定log很重要,便于分析错误进行补救。

    导入指定表

    imp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

    还原到远程服务器imp 用户名/密码@远程的IP:端口/实例 file=存放的位置:\文件名称.dmp full=y

    二、Oracle表操作

    1、创建表create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

    根据已有的表创建新表:A:select * into table_new from table_old (使用旧表创建新表)

    B:create table tab_new as select col1,col2… from tab_old definition only

    2、删除表drop table tabname

    3、重命名表alter table 表名 rename to 新表名

    4、增加字段alter table 表名 add (字段名 字段类型 默认值 是否为空);

    例:alter table tablename add (ID int);

    5、修改字段alter table 表名 modify (字段名 字段类型 默认值 是否为空);

    6、重名字段alter table 表名 rename column 列名 to 新列名 (其中:column是关键字)

    7、删除字段

    说明:alter table 表名 drop column 字段名;

    8、添加主键alter table tabname add primary key(col)

    9、删除主键alter table tabname drop primary key(col)

    10、创建索引create [unique] index idxname on tabname(col….)

    11、删除索引drop index idxname

    注:索引是不可更改的,想更改必须删除重新建。

    12、创建视图create view viewname as select statement

    13、删除视图drop view viewname

    三、Oracle操作数据

    1、数据查询select from [where ] [order by [asc或desc]]

    2、插入数据insert into 表名 values(所有列的值);

    insert into test values(1,'zhangsan',20);

    insert into 表名(列) values(对应的值);

    insert into test(id,name) values(2,'lisi');

    3、更新数据update 表 set 列=新的值 [where 条件] -->更新满足条件的记录

    update test set name='zhangsan2' where name='zhangsan'

    update 表 set 列=新的值 -->更新所有的数据

    update test set age =20;

    4、删除数据delete from 表名 where 条件 -->删除满足条件的记录

    delete from test where id = 1;

    delete from test -->删除所有

    commit; -->提交数据

    rollback; -->回滚数据

    delete方式可以恢复删除的数据,但是提交了,就没办法了 delete删除的时候,会记录日志 -->删除会很慢很慢

    truncate table 表名

    删除所有数据,不会影响表结构,不会记录日志,数据不能恢复 -->删除很快

    drop table 表名

    删除所有数据,包括表结构一并删除,不会记录日志,数据不能恢复-->删除很快

    5、数据复制

    表数据复制insert into table1 (select * from table2);

    复制表结构create table table1 select * from table2 where 1>1;

    复制表结构和数据create table table1 select * from table2;

    复制指定字段create table table1 as select id, name from table2 where 1>1;

    四、数据库复制命令

    不同的数据库语法不同(SQL Server和Oracle为例),且复制包括目标表已存在和目标表不存在的情况,分别回答:

    SQL Server中,如果目标表存在:insert into 目标表select*from原表;

    SQL Server中,,如果目标表不存在:select*into 目标表from原表;(复制表结构和数据)

    select*from 目标表from原表where1=0(只复制表结构)

    Oracle中,如果目标表存在:insert into 目标表select*from原表;

    commit;

    Oracle中,如果目标表不存在:create table 目标表 as select * from 原表 where 1=0(只复制表结构)

    create table 目标表 as select * from 原表;复制表结构和数据

    推荐教程:Oracle教程

    展开全文
  • Oracle 数据库基本语句

    2017-02-25 18:50:00
    ---管理员登录conn sys/oracle@orcl as sysdba;--解锁scott方案alter user scott account unlock;--scott登录conn scott/tiger@orcl as normal; 例题: 创建表 temp id nubmer(8) names varchar2(20) age number...

    ---管理员登录
    conn sys/oracle@orcl as sysdba;
    --解锁scott方案
    alter user scott account unlock;
    --scott登录
    conn scott/tiger@orcl as normal;

     

    例题:

    创建表 temp id nubmer(8) names varchar2(20) age number(8)
    create table temp(
    id number(8),
    names varchar2(20),
    age number(8)
    );

    1 增加列 address(30)
    alter table temp add address varchar2(30);

    2 将地址列 长度修改为50
    alter table temp modify address varchar2(50);

    3 删除地址列
    alter table temp drop column address;

    4 增加一条数据 1,'张三',30
    insert into temp values(1,'张三',30);

    5 再增加一个数据 '王五'
    insert into temp(names) values('王五');

    6 将王五的年龄设置为25
    update temp set age=25 where names='王五';

    7 将标号是1的人的 姓名修改为李强,年龄修改为33
    update temp set names='天佑',age=60 where id=1;

    8 将王五删除
    delete from temp where names='王五';

    9 查询所有的数据
    select * from temp;

    10 删除temp表
    drop table temp;

     

    在Oracle 中效果如下:

     

    SQL> create table temp(
    2 id number(8),
    3 names varchar2(20),
    4 age number(8)
    5 );
    Table created

     

    SQL> select * from temp;
    ID NAMES AGE
    --------- -------------------- ---------

     

    SQL> alter table temp add address varchar2(30);
    Table altered

     

    SQL> select * from temp;
    ID NAMES AGE ADDRESS
    --------- -------------------- --------- ------------------------------

     

    SQL> alter table temp modify address varchar2(50);
    Table altered

     

    SQL> select * from temp;
    ID NAMES AGE ADDRESS
    --------- -------------------- --------- --------------------------------------------------

     

    SQL> alter table temp drop column address;
    Table altered

     

    SQL> select * from temp;
    ID NAMES AGE
    --------- -------------------- ---------

    SQL> insert into temp values(1,'张三',30);
    1 row inserted

    SQL> insert into temp(names) values('王五');
    1 row inserted

    SQL> select * from temp;
    ID NAMES AGE
    --------- -------------------- ---------
    1                   张三                        30
                         王五

    SQL> update temp set age=25 where names='王五';
    1 row updated

    SQL> select * from temp;
    ID NAMES AGE
    --------- -------------------- ---------
    1                       张三                30
                             王五                25

    SQL> update temp set names='天佑', age=60 where id=1;
    1 row updated

    SQL> select * from temp;
    ID NAMES AGE
    --------- -------------------- ---------
    1                       天佑                 60
                             王五                 25

    SQL> delete from temp where names='王五';
    1 row deleted

    SQL> select * from temp;
    ID NAMES AGE
    --------- -------------------- ---------
    1                      天佑                   60

    SQL> drop table temp;
    Table dropped

    SQL> select * from temp;

    select * from temp
    ORA-00942: 表或视图不存在

     

    转载于:https://www.cnblogs.com/mumuda/p/6442455.html

    展开全文
  • oracle数据库基本语句1、创建数据库2、删除数据库3、备份数据库完全备份demo:用户名、密码buffer: 缓存大小file: 具体的备份文件地址full: 是否导出全部文件ignore: 忽略错误,如果表已经存在,则也是覆盖将数据库...

    523cfc02cd4173a2cf9e42ac6b4032f9.png

    oracle数据库基本语句

    1、创建数据库

    2、删除数据库

    3、备份数据库

    完全备份

    demo:用户名、密码

    buffer: 缓存大小

    file: 具体的备份文件地址

    full: 是否导出全部文件

    ignore: 忽略错误,如果表已经存在,则也是覆盖

    将数据库中system用户与sys用户的表导出

    导出指定的表

    按过滤条件,导出

    导出时可以进行压缩;命令后面 加上 compress=y ;如果需要日志,后面:log=d:\log.txt

    备份远程服务器的数据库

    4、数据库还原

    打开cmd直接执行如下命令,不用再登陆sqlplus。

    完整还原

    指定log很重要,便于分析错误进行补救。

    导入指定表

    imp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

    还原到远程服务器

    二、Oracle表操作

    1、创建表

    根据已有的表创建新表:

    2、删除表

    3、重命名表

    4、增加字段

    5、修改字段

    6、重名字段

    7、删除字段

    说明:

    8、添加主键

    9、删除主键

    10、创建索引

    11、删除索引

    注:索引是不可更改的,想更改必须删除重新建。

    12、创建视图

    13、删除视图

    三、Oracle操作数据

    1、数据查询

    2、插入数据

    3、更新数据

    4、删除数据

    5、数据复制

    表数据复制

    复制表结构

    复制表结构和数据

    复制指定字段

    四、数据库复制命令

    不同的数据库语法不同(SQL Server和Oracle为例),且复制包括目标表已存在和目标表不存在的情况,分别回答:

    SQL Server中,如果目标表存在:

    SQL Server中,,如果目标表不存在:

    Oracle中,如果目标表存在:

    Oracle中,如果目标表不存在:

    推荐教程:Oracle教程

    展开全文
  • 操作手册 目录 TOC \o "1-3" \h \z \u 一表空间 5 1.创建表空间 5 2.增加表空间 5 3.删除表空间 6 4.查询表空间状态 6 5.查询数据文件路径 6 6.移动表空间中数据文件的路径 6 7.... 查询数据库系统上有多少用
  • ORACLE数据库为主提纲:第一部分、SQL语言基础 第一章:Oracle命令类别及sql简单语法介绍第二章:oracle的基本函数第三章:oracle的数据类型第四章:多表连接技术 第二部分、oracle基本对象及SQL优化 第一章:...

    以ORACLE数据库为主
    提纲:
    第一部分、SQL语言基础

    第一章:Oracle命令类别及sql简单语法介绍
    第二章:oracle的基本函数
    第三章:oracle的数据类型
    第四章:多表连接技术

    第二部分、oracle基本对象及SQL优化


    第一章:执行计划
    第二章:oracle表
    第三章:oracle索引
    第四章:oracle分区
    第五章:oracle事务和锁
    第六章:oracle hint

     

    第四部分、oracle数据库管理简介

    第一章:oracle体系结构
    第二章:oracle高水位线
    第三章:oracle监听
    第四章:flashback
    第五章:oracle删除和卸载

     


    ********************************************************
    第一章:
    Oracle命令类别:

    数据操纵语言:DML: select; insert; delete; update; merge.

    数据定义语言:DDL: create; alter; drop; truncate; rename; comment.

    事务控制语言:TCL: commit; rollback; savepoint.

    数据控制语言:DCL: grant; revoke.

    sql简单语法介绍

    1.简单查询语句执行顺序

    from, where, group by, having, order by, select

    2. 运算符及优先级

    算术运算符
    *,/,+,-,

    逻辑运算符
    not, and ,or

    SQL>select ename, job, sal ,comm from emp where job='SALESMAN' OR job='PRESIDENT' AND sal> 1500;
    等价于
    SQL>select ename, job, sal ,comm from emp where job='SALESMAN' OR (job='PRESIDENT' AND sal> 1500);
    括号的优先级最高,最好加括号便于理解

    比较运算符

    单行比较运算 =,>, >=,<,<=, <>
    多行比较运算 >any,>all,<any,<all,in,not in
    模糊比较 like(配合“%”和“_”)
    特殊比较 is null

    3.单引号的转义:连续两个单引号表示转义.

    SQL> select empno||' is Scott''s empno' from emp where empno=7788;
    /*
    EMPNO||'ISSCOTT''SEMPNO'
    --------------------------------------------------------
    7788 is Scott's empno
    */
    4.order by

    1)位置:order by语句总是在一个select语句的最后面。

    2)升序和降序,升序ASC(默认), 降序DESC。有空值的列的排序,缺省(ASC升序)时 null排在最后面

    3)混合排序,使用多个列进行排序,多列使用逗号隔开,可以分别在各列后面加升降序。

    SQL> select ename,deptno,job from emp order by deptno asc,job desc;

    注意
    默认不加order by查询时是按照物理地址rowid排序的,该顺序大多数时候和插入的先后顺序一致,
    但也有很大的可能不一致(如有删除载插入时),所以需要排序是,只有order by
    才能保证结果是正确的。

    ********************************************************

    第二章、oracle的基本函数

    (尽量使用数据库自身提供的函数)
    ************************
    2.1 单行函数与多行函数

    单行函数:一行返回一个结果,结果于原记录数相同。

    SQL>select empno,lower(ename) from emp;

    注意:where限定from后面的表或视图,限定的选项只能是表的列或列单行函数或列表达式,
    where后不可以直接使用分组函数
    SQL> select empno,job from emp where length(job)>5;
    分组函数放在having后面
    SQL> select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;

    多行函数:指多行数据输入,返回一个值的函数(典型的是聚合函数)

    SQL>select sum(sal) from emp;

    特殊的单行聚合函数(有叫窗口函数的):
    如:
    sum() over (partition by order by )
    count() over (partition by order by )
    min() over (partition by order by )
    max() over (partition by order by )
    avg() over (partition by order by )
    实例:
    select t.*,count(t.empno)over() from emp t; --不要count空字段
    select t.*,count(t.empno)over(partition by deptno) from emp t;
    加order by
    select t.*,count(t.empno)over(partition by deptno order by t.empno asc) from emp t;

    举例:查询所有员工及其所在部门的平均工资,并在部门内按工资由高到底排序

    select t.*,avg(t.sal)over(partition by deptno order by t.sal desc) from emp t ;

    select t.*,avg(t.sal)over(partition by deptno ) from emp t order by t.sal desc;

    select t.*,avg(t.sal)over(partition by deptno ) from emp t order by t.deptno,t.sal desc;

    ************************
    2.2几种有用的单行函数
    2.2.1字符函数
    lower('SQL Course')----->sql course 返回小写
    upper('sql course')----->SQL COURSE 返回大写
    initcap('SQL course')-----> Sql Course 返回首字母大写
    concat('good','string')---->good string 拼接 //只能拼接2个字符串
    substr('String',1,3)---->Str 从第1位开始截取3位数
    instr('t#i#m#r#a#n#','#',3) --->4 从第3位起始找#字符在那个绝对位置
    length('String')---->6 长度
    lpad('first',10,'$')左填充
    rpad(676768,10,'*')右填充
    replace('JACK and JUE','J','BL')---->BLACK and BLUE
    trim( ' hello ')---->hello
    ltrim( ' hello ')
    rtrim( ' hello ')
    trim('l' from 'lhelloll')---->hello
    translate('abcde','cf','12')----->ab1de2g 注意db2写法:translate('abcdefg','12','cf')

    与replace的区别,一个是字符串替换,一个是字符替换。

    强大的正则表达式函数
    ORACLE中的支持正则表达式的函数主要有下面四个: (db2暂时没有发现有内置该类函数)
    1,REGEXP_LIKE :与LIKE的功能相似
    2,REGEXP_INSTR :与INSTR的功能相似
    3,REGEXP_SUBSTR :与SUBSTR的功能相似
    4,REGEXP_REPLACE :与REPLACE的功能相似
    (需要用到的时候自己查找学习)
    示例一:
    将字符串 go12od ev3e4nin676g 中的所有数字去掉
    select replace(translate('go12od ev3e4nin676g','1234567890','1'),'1','') from dual;
    select regexp_replace('go12od ev3e4nin676g','[[:digit:]]','') from dual;
    如果只去掉连着的数字
    select regexp_replace('go12od ev3e4nin676g','[[:digit:]][[:digit:]]+','') from dual;
    示例二:
    变量截取,将字符串 sdifidso@s_sdfoijdsi12@eefdsfd@sdfds@ 中被@包围,以s_开头的字符串

    select regexp_substr('sdifidso@s_12@eefdsfd@s1223@','@s_.+?@') from dual;
    ?此处代表非贪心模式,否则
    select regexp_substr('sdifidso@s_12@eefdsfd@s1223@','@s_.+@') from dual;

    2.2.2 数值函数
    round 四舍五入
    mod(100,12) 取余数
    ceil向上取整
    floor向下取整
    trunc截断取整
    2.2.3日期函数
    日期转换函数 to_date等
    日期加减函数 add_month等
    日期时间差函数 可以精确到 年月日时分秒
    注意db2日期函数与oracle区别较大,使用时具体分析
    例如
    日期加一天 oracle date+1 db2 date + 1d
    2.2.4 进制转换函数
    1.十六进制转十进制
    select to_number('1b','xxx') from dual
    2.十进制转十六进制
    select to_char(27,'xxx') from dual
    2.2.5 空值转换函数
    nvl(ext1,ext2) ex1值为空则返回ex2,否则返回该值本身ex1
    nullif(ex1,ex2) 值相等返空,否则返回第一个值
    coalesce(ex1,ex2,...,)返回列表中第一个非空表达式
    nvl2(ex1,ex2,ex3) 如果ex1不为空,显示ex2,否则显示ex3

    空值问题:
    主要是匹配问题,因为空值不与任何值相等
    示例1:简单匹配
    SQL> select count(*) from emp;

    COUNT(*)
    ----------
    14

    SQL> select count(*) from emp t where t.comm=300;

    COUNT(*)
    ----------
    1

    SQL> select count(*) from emp t where t.comm<>300;

    COUNT(*)
    ----------
    3
    或者是查询 工资和福利不相等的

    SQL> select * from emp t1 where t1.sal <> t1.comm;

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
    7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
    7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
    7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
    7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
    注意 聚集函数会忽略空值
    示例2: not in 遇到空值
    SQL> update emp t set t.deptno='' where t.deptno=10;

    已更新3行。

    SQL> select * from emp;

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
    7369 SMITH CLERK 7902 17-12月-80 800 20
    7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
    7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
    7566 JONES MANAGER 7839 02-4月 -81 2975 20
    7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
    7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
    7782 CLARK MANAGER 7839 09-6月 -81 2450
    7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
    7839 KING PRESIDENT 17-11月-81 5000
    7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
    7876 ADAMS CLERK 7788 23-5月 -87 1100 20

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
    7900 JAMES CLERK 7698 03-12月-81 950 30
    7902 FORD ANALYST 7566 03-12月-81 3000 20
    7934 MILLER CLERK 7782 23-1月 -82 1300

    已选择14行。

    SQL> select * from dept;

    DEPTNO DNAME LOC
    ---------- -------------- -------------
    10 ACCOUNTING NEW YORK
    20 RESEARCH DALLAS
    30 SALES CHICAGO
    40 OPERATIONS BOSTON

    SQL> select * from dept t1 where t1.deptno not in (select deptno from emp);

    未选定行
    应该为(前提没有编号为0的部门):
    SQL> select * from dept t1 where t1.deptno not in (select nvl(deptno,0) from emp);

    DEPTNO DNAME LOC
    ---------- -------------- -------------
    10 ACCOUNTING NEW YORK
    40 OPERATIONS BOSTON
    或最好改为 not exists
    SQL> select * from dept t1 where not exists (select 1 from emp t2 where t1.deptno=t2.deptno);

    DEPTNO DNAME LOC
    ---------- -------------- -------------
    10 ACCOUNTING NEW YORK
    40 OPERATIONS BOSTON

    此外,表尾部的null列不占用空间,表设计时尽量将可能空值的列放到最后

    2.2.6其他函数
    1.CHR()函数和ASCII()函数
    chr()函数将ASCII码转换为字符: ASCII码 –》 字符

    ascii()函数将字符转换为ASCII码:字符 –》 ASCII码

    在oracle中chr()函数和ascii()是一对反函数。

    求字符对应的ASCII值
    SQL> select ASCII('A') FROM dual;

    ASCII('A')
    ----------
    65

    SQL> select chr(65) from dual;

    C
    -
    A
    2.decode函数与case when函数
    decode用法:
    SELECT job, sal,
    DECODE(job, 'ANALYST', SAL*1.1,
    'CLERK', SAL*1.15,
    'MANAGER', SAL*1.20,
    SAL)
    REVISED_SALARY
    FROM emp

    case when 写法:

    select job, sal,case job
    when 'ANALYST' then SAL*1.1
    when 'CLERK' then SAL*1.15
    when 'MANAGER' then SAL*1.20
    else sal end
    REVISED_SALARY
    from emp
    /

    select job, sal,
    case when job='ANALYST' then SAL*1.1
    when job='CLERK' then SAL*1.15
    when job='MANAGER' then SAL*1.20
    else sal end
    REVISED_SALARY
    from emp
    /
    不同是case when功能更强大,不过decode写法比较简单,
    select ename,sal,
    case when sal>=3000 then '高级'
    when sal>=2000 then '中级'
    else '低级' end
    级别
    from emp
    /

    第三章、oracle的数据类型

    3.1 四种基本的常用数据类型

    1、字符型, 2、数值型,3、日期型,4、大对象型

    3.1.1 字符型:
    char 固定字符,最长2000个
    varchar2 可变长字符,最长4000个,最小值是1 (在oracle中与varchar等价)
    nchar/nvarchar2 NCHAR/NVARCHAR2类型的列使用国家字符集
    nvarchar2(1)能存储一个字符具体暂用几个字节根据字符集而定,而上面两种是字节数
    raw和long raw 固定/可变长度的二进制数据长度 最长2G,可存放多媒体图象声音等。(老类型,逐步淘汰)
    LONG 可变长的字符串数据,最长2G,LONG具有VARCHAR2列的特性,一个表中最多一个LONG列。(老类型,逐步淘汰)。

    注意char和varchar2的区别:
    1.char是固定长度,varchar2是变长
    2.匹配时char也匹配空格
    示例:
    SQL> create table test(c1 char(10),c2 varchar2(10));

    表已创建。

    SQL> insert into test(c1,c2) values('a','a');

    已创建 1 行。

    SQL> select * from test;

    C1 C2
    ---------- ----------
    a a

    SQL> select * from test where c1=c2;

    未选定行
    需要改为
    SQL> select * from test where trim(c1)=c2;

    C1 C2
    ---------- ----------
    a a

    SQL> select * from test where c1=rpad(c2,10);

    C1 C2
    ---------- ----------
    a a
    第二种更好些,因为第一种很容易导致c1列上的索引失效

    3.varchar2比char节省空间,使用时尽量使用varchar2。

    4.行尾的null字段并不耗用任何的存储空间(对于char,varchar2,number都是的,所以在设计表尽量将可能为空的字段放到末尾)
    示例1:
    SQL> create table t1(c1 varchar2(20));
    SQL> create table t2(c1 char(20));
    SQL> select * from user_segments;
    未创建段,11g增加的段延迟创建功能。(注意:在用exp导出时,没有创建段的表不会被导出,可能会出现少表,最好用数据泵expdp导出)
    SQL>
    begin
    for i in 1..10000 loop
    insert into t1 values(null);
    end loop;
    end;
    /
    SQL>
    begin
    for i in 1..10000 loop
    insert into t2 values(null);
    end loop;
    end;
    /
    SQL> select * from user_segments;
    消耗的空间相同(主要为块头部和块预留的空闲本分占用)
    SQL> update t1 set c1='1';
    SQL> update t2 set c1='1';
    SQL> select * from user_segments;
    能看出char比varchar2消耗更多的空间,同时t2会发生行迁移,较多时会对性能影响较大。
    SQL> analyze table t2 compute statistics
    SQL> select pct_free,pct_used,avg_row_len,chain_cnt from user_tables where table_name='T2';

    PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT
    ---------- ---------- ----------- ----------
    10 29 9152

    发生了行迁移
    ppt -7
    t1不会因为t1可以利用块中的freespace
    SQL> analyze table t1 compute statistics
    2 /

    Table analyzed

    SQL> select pct_free,pct_used,avg_row_len,chain_cnt from user_tables where table_name='T1';

    PCT_FREE PCT_USED AVG_ROW_LEN CHAIN_CNT
    ---------- ---------- ----------- ----------
    10 5 0

    3.1.2 数值型:
    3中固有类型来存储数值
    NUMBER:精度可达38位,边长格式,长度为0-22字节,这是目前最为常用的数字类型。 范围 10(-130) 到10(126)之间的任何数
    BINARY_FLOAT:单精度浮点数,长度为5个字节,4个用于存储浮点数,一个用于存长度 -10(38.53) 到 +10(38.53) 精度6位
    BINARY_DOUBLE:双精度浮点数,长度为9字节,8位存浮点数,1位存长度 范围 -10(308.25) 到 +10(308.25) 精度13位

    number精度更大,但BINARY_DOUBLE可存储更大或更小的值
    支持非固定数据类型
    numetric(p,s) decimal(p,s) integer或int float double real
    底层都只是number类型

    3.1.3 日期型:

    date 日期的普通形式,表示精度只能到秒级。
    timestamp 日期的扩展形式,表示精度可达秒后小数点9位(10亿分之1秒)。
    timestamp with timezone 带时区
    timestamp with local timezone 时区转换成本地日期
    3.1.4 LOB型:大对象是10g 引入的,在11g中又重新定义,在一个表的字段里存储大容量数据,所有大对象最大都可能达到4G

    CLOB: 用来存储单字节的字符数据,包含在数据库内。
      NCLOB:用来存储多字节的字符数据 
      BLOB:用于存储二进制数据,包含在数据库内。
    BFILE:存储在数据库之外的二进制文件中,这个文件中的数据只能被只读访问。

     CLOB,NCLOB,BLOB都是内部的LOB类型,没有LONG只能有一列的限制
    保存图片或电影使用BLOB最好、如果是小说则使用CLOB最好。
     虽然LONG RAW也可以使用,但LONG是oracle将要废弃的类型,因此建议用LOB。

    第四章:多表连接技术

    交叉连接(笛卡尔积)
    非等值连接
    等值连接 (内连)
    外连接 (内连的扩展,左外,右外,全连接)
    自连接
    自然连接(内连,隐含连接条件,自动匹配连接字段)
    复合连接 (多个结果集进行并、交、差)

    范例:
    create table a (id int, name char(10));
    create table b (id int, loc char(10));

    insert into a values (1,'a');
    insert into a values (2,'b');
    insert into a values (2,'c');
    insert into a values (4,'d');

    insert into b values (1,'A');

    insert into b values (2,'B');
    insert into b values (3,'C');
    commit;

    SQL> select * from a;

    ID NAME
    ---------- ----------
    1 a
    2 b
    2 c
    4 d

    SQL> select * from b;

    ID LOC
    ---------- ----------
    1 A
    2 B
    3 C

    4.2.1 交叉连接(笛卡尔积)
    连接条件无效或被省略,两个表的所有行都发生连接,所有行的组合都会返回(n*m)

    SQL99写法:
    SQL> select * from a cross join b;

    oracle写法:
    SQL> select * from a,b;

    ID NAME ID LOC
    ---------- ---------- ---------- ----------
    1 a 1 A
    2 b 1 A
    2 c 1 A
    4 d 1 A
    1 a 2 B
    2 b 2 B
    2 c 2 B
    4 d 2 B
    1 a 3 C
    2 b 3 C
    2 c 3 C
    4 d 3 C

    已选择12行。

    非等值连接:(连接条件非等值,也属于内连范畴)

    SQL99写法:
    SQL> select empno,ename,sal,grade,losal,hisal
    from emp join salgrade
    on sal between losal and hisal;

    oracle写法:
    SQL> select empno,ename,sal,grade,losal,hisal
    from emp,salgrade
    where sal between losal and hisal;

    EMPNO ENAME SAL GRADE LOSAL HISAL
    ---------- ---------- ---------- ---------- ---------- ----------
    7369 SMITH 800 1 700 1200
    7900 JAMES 950 1 700 1200
    7876 ADAMS 1100 1 700 1200
    7521 WARD 1250 2 1201 1400
    7654 MARTIN 1250 2 1201 1400
    7934 MILLER 1300 2 1201 1400
    7844 TURNER 1500 3 1401 2000
    7499 ALLEN 1600 3 1401 2000
    7782 CLARK 2450 4 2001 3000
    7698 BLAKE 2850 4 2001 3000
    7566 JONES 2975 4 2001 3000
    7788 SCOTT 3000 4 2001 3000
    7902 FORD 3000 4 2001 3000
    7839 KING 5000 5 3001 9999


    4.2.2 等值连接,典型的内连接

    SQL99写法:
    SQL> select * from a inner join b on a.id=b.id;

    oracle写法:
    SQL> select * from a,b where a.id=b.id;

    ID NAME ID LOC
    ---------- ---------- ---------- ----------
    1 a 1 A
    2 b 2 B
    2 c 2 B

    4.2.3 外连接包括左外连接,右外连接,全外连接

    1)左外连接

    SQL99语法:
    SQL> select * from a left join b on a.id=b.id;

    oracle语法:
    SQL> select * from a,b where a.id=b.id(+);

    结果:

    ID NAME ID LOC
    ---------- ---------- ---------- ----------
    1 a 1 A
    2 c 2 B
    2 b 2 B
    4 d

    2)右外连接

    SQL99语法:
    SQL>select * from a right join b on a.id=b.id;

    oracle语法:
    SQL> select * from a,b where a.id(+)=b.id;

    结果

    ID NAME ID LOC
    ---------- ---------- ---------- ----------
    1 a 1 A
    2 b 2 B
    2 c 2 B
    3 C

    3)全外连接

    SQL99语法:
    SQL> select * from a full join b on a.id=b.id;

    ID NAME ID LOC
    ---------- ---------- ---------- ----------
    1 a 1 A
    2 b 2 B
    2 c 2 B
    4 d
    3 C

    oracle语法:
    SQL> select * from a,b where a.id=b.id(+)
    union
    select * from a,b where a.id(+)=b.id;

    ID NAME ID LOC
    ---------- ---------- ---------- ----------
    1 a 1 A
    2 b 2 B
    2 c 2 B
    4 d
    3 C

    4.2.4 自连接

    sql99语法:
    SQL> select * from a cross join a;

    oracle语法:
    SQL> select * from a a1,a a2;

    ID NAME ID NAME
    ---------- ---------- ---------- ----------
    1 a 1 a
    1 a 2 b
    1 a 2 c
    1 a 4 d
    2 b 1 a
    2 b 2 b
    2 b 2 c
    2 b 4 d
    2 c 1 a
    2 c 2 b
    2 c 2 c
    2 c 4 d
    4 d 1 a
    4 d 2 b
    4 d 2 c
    4 d 4 d

    已选择16行。

    4.2.5 自然连接(属于内连中等值连接)

    在oralce中使用natural join,也就是自然连接。

    先看自然连接:

    SQL> select * from a natural join b;

    ID NAME LOC
    ---------- ---------- ----------
    1 a A
    2 b B
    2 c B

    -----将两个表分别再加一个列ABC后,则有两个公共列(ID列和ABC列),添加数据后,再尝试自然连接如何匹配。

    SQL> select * from a;

    ID NAME ABC
    ---------- ---------- ----------
    1 a s
    2 b t
    2 c u
    4 d v

    SQL> select * from b;

    ID LOC ABC
    ---------- ---------- ----------
    1 A w
    2 B t
    3 C r

    SQL> select * from a natural join b;

    ID ABC NAME LOC
    ---------- ---------- ---------- ----------
    2 t b B

    在自然连接中可以使用using关键字:

    当使用natraul join关键字时,如果两张表中有多个字段,它们具有相同的名称和数据类型,那么这些字段都将被oracle自作主张的将他们连接起来。但如果名称相同,类型不同,或者当你需要在多个字段同时满足连接条件的情况下,想人为指定某个(些)字段做连接,那么可以使用using 关键字。

    在oracle连接(join)中使用using关键字

    SQL> select id,a.abc,name,loc from a join b using(id);

    ID ABC NAME LOC
    ---------- ---------- ---------- ----------
    1 s a A
    2 t b B
    2 u c B

    using里未必只能有一列

    SQL> select id,abc,name,loc from a join b using(id,abc);

    ID ABC NAME LOC
    ---------- ---------- ---------- ----------
    2 t b B
    在实际工作中看,内连接,左外连接用的较多

    左连接与内连接 示例:(查询每个部门的平均工资)
    方法一:内连接
    select t1.deptno, t1.dname, nvl(avg(t2.sal), 0)
    from dept t1, emp t2
    where t1.deptno = t2.deptno
    group by t1.deptno, t1.dname;

    DEPTNO DNAME NVL(AVG(T2.SAL),0)
    ------ -------------- ------------------
    10 ACCOUNTING 2916.66666666667
    20 RESEARCH 2175
    30 SALES 1566.66666666667

    (没有40号部门的统计数据)
    方法二:左连接
    select t1.deptno, t1.dname, nvl(avg(t2.sal),0)
    from dept t1
    left join emp t2
    on t1.deptno = t2.deptno
    group by t1.deptno, t1.dname;

    DEPTNO DNAME NVL(AVG(T2.SAL),0)
    ------ -------------- ------------------
    10 ACCOUNTING 2916.66666666667
    40 OPERATIONS 0
    20 RESEARCH 2175
    30 SALES 1566.66666666667


    4.3.复合查询(使用集合运算符)

    Union,对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;

    Union All,对两个结果集进行并集操作,包括所有重复行,不进行排序;

    Intersect,对两个结果集进行交集操作,不包括重复行,同时进行默认规则的排序;

    Minus,对两个结果集进行差操作,不包括重复行,同时进行默认规则的排序。


    4.3.子查询关联更新问题
    SQL> create table t1(id int,name varchar2(10));

    Table created

    SQL> create table t2(id int,name varchar2(10));

    Table created

    SQL> insert into t1(id,name) values (1,'a1');

    1 row inserted

    SQL> insert into t1(id,name) values (2,'b1');

    1 row inserted

    SQL> insert into t1(id,name) values (3,'c1');

    1 row inserted

    SQL> insert into t2(id,name) values (1,'a2');

    1 row inserted

    SQL> insert into t2(id,name) values (2,'b2');

    1 row inserted

    SQL> commit;

    Commit complete

    SQL> select * from t1;

    ID NAME
    --------------------------------------- ----------
    1 a1
    2 b1
    3 c1

    SQL> select * from t2;

    ID NAME
    --------------------------------------- ----------
    1 a2
    2 b2

    拿t2中的数据去更新t1

    SQL> update t1 set t1.name=(select name from t2 where t1.id=t2.id);

    3 rows updated

    SQL> select * from t1;

    ID NAME
    --------------------------------------- ----------
    1 a2
    2 b2
    3

    发现id=3的数据被更新成空

    SQL> rollback;

    Rollback complete

    SQL> update t1 set t1.name=(select name from t2 where t1.id=t2.id)
    where exists(select 1 from t2 where t1.id=t2.id);

    2 rows updated

    SQL> select * from t1;

    ID NAME
    --------------------------------------- ----------
    1 a2
    2 b2
    3 c1
    根据现实情况决定是否要加 where条件限制

    4.4多表连接中的三种表连接

    多表连接时,oracle一次只能连接两个表,优化器从一个表开始,将他与下一个表连接,再将中间结果与接下来的表相连接,直到结束返回结果。

    嵌套循环(nested loop join)
    排序合并(SORT MERGE JOIN)
    哈希连接(HASH JOIN)

    4.4.1.嵌套循环(nested loop join)
    原理:选定一张表做为驱动表也称为外部表,Oracle会遍历驱动表中的每一行,根据连接条件去匹配第二张表(内部表)中的行。

    注意点:
    1.适用于被连接的数据子集比较小的情况
    2.在内部表上最好有基于连接条件的索引且区分度较好。
    3.外部表与内部表的选择很重要,最好是小表最为外部表以减少IO
    4.使用oracle提示 USE_NL(TABLE1,table2) 强制使用该链接方式
    示例:
    select /*+use_nl(t1,t2)*/* from dept t1, emp t2 where t1.deptno = t2.deptno

    4.4.2排序合并(SORT MERGE JOIN)
    原理:(1)先对每个数据子集进行全表扫描并(2)对排序结果进行合并(从一个排序表中取出数据到另一个排序表中进行匹配)
    注意点:
    1.由于排序资源消耗较大,所以这种连接方式一般来所没有有hosh join经济,提别是大表连接时。
    2.档数据源已排序的情况下可能会选择该方式
    3.不等值连接(<>,<,>)和not like及like的情况,有可能。
    4.使用提示USE_MERGE(TABLE1,table2)强制使用该方式

    示例:
    select /*+use_merge(t1,t2)*/* from dept t1, emp t2 where t1.deptno = t2.deptno

    4.4.2.哈希连接(HASH JOIN)
    这是比较常用的连接方式
    原理:优化器首先扫描较小的表,利用连接键在内存中通过hash函数建立hash表(bitmap位图方式),然后扫描大表,每读到一条记录就通过该hash函数计算hash值来探测hash表,找出与hash表匹配的行。
    注意点:
    (1)如果是小表可以全部放到内存,则连接成本接近两个表全表扫描的成本之和。
    (2)如果表比较大,优化器就会将其分割为不同的分区,把不能放入内存的部分写入磁盘的临时段。临时段的内容需要再读入内存做hash连接。此时的代价接近
    全表扫描小表+分区数*全表扫描大表
    (3)适用于两大大表之间的连接或大表与小表之间的连接
    (4)hash连接不使用索引
    (5)使用提示USE_HASH(TABLE1,table2)强制使用该方式

    示例:
    select /*+use_hash(t1,t2)*/* from dept t1, emp t2 where t1.deptno = t2.deptno

    第二部分、oracle基本对象及SQL优化

    第一章:执行计划
    1.查看执行计划的几个要点
    select * from dept t1, emp t2,emp_bak t3 where t1.deptno = t2.deptno and t2.empno=t3.empno;
    第二章:oracle表

    1.堆表
    最常见的表
    2.临时表
    临时表存放在当前登录的临时表空间下,它被每个session单独使用,即:不同session看到的临时表中的数据不一样。
    两种模式:
    1)基于事务的临时段:在事务提交时,就会自动删除记录,on commit delete rows。
    create global temporary table tmp_test1(id int)
    on commit delete rows;
    2)基于会话的临时段:当用户退出session 时,才会自动删除记录, on commit preserve rows。
    create global temporary table tmp_test2(id int)
    on commit preserve rows;
    好处:
    1)session之间数据隔离,有利于并行处理互不影响
    2)只记录少量undo的redo日志,不计直接数据变动的redo日志

    3.索引组织表
    ppt
    如果表经常以主键查询,可以考虑建立索引组织表,加快表的访问速度.
    在索引组织表中,数据是有序排列的
    ppt-36
    示例:
    create table test_n(id int, name varchar2(50),
    constraint pk_test_n primary key (id))
    ;

    create table test_iot(id int, name varchar2(50),
    constraint pk_test_iot primary key (id))
    organization index ;

    insert into test_n select empno,ename from emp;
    insert into test_iot select empno,ename from emp;
    select * from user_segments; --索引组织表没有创建表段

    查看执行计划
    select * from iot_test1 t where t.id='7788';

    select * from iot_test2 t where t.id='7788';

    4.聚簇表
    5.外部表

    第三章:oracle索引

    建测试表:
    SQL> create table test_ind as select * from all_objects;
    create unique index PK_TEST_IND on TEST_IND (OBJECT_ID)
    按唯一性分有:唯一索引和非唯一索引 (唯一索引+not null=主键)
    按机制分有:
    1.b-tree索引
    ppt-35
    Index Scans:

    1)Full Index Scan
    扫描整个索引,有排序操作
    示例:
    select * from EMP T WHERE T.SAL>2000 ORDER BY T.EMPNO

    2)Fast Full Index Scan

    所需要的列都在索引里,不需要访问表
    示例:
    select object_id from test_ind

    3)Index Range Scan
    使用index rang scan的3种情况:
    (a) 在唯一索引列上使用了range操作符(> < <> >= <= between)
    (b) 在唯一组合索引上,对组合索引使用部分列进行查询(含引导列),导致查询出多行
    (c) 对非唯一索引列上进行的任何查询。不含‘布尔或’
    示例一:
    select * from test_ind t where t.object_id<100
    示例二:
    create index IND_TEST_IND_N on TEST_IND (OBJECT_TYPE)
    select object_id from test_ind t where t.object_type='SEQUENCE';
    4)Index Unique Scan

    精确访问效率最高
    示例一:
    select * from test_ind t where t.object_id=100


    5)Index Skip Scan
    适用于 条件没有前缀索引,但前缀索引区分度较小,但条件中的字段区分度较大
    示例:
    create index IND_TEST_SKIP on TEST_IND (OWNER, OBJECT_ID)
    select * from test_ind t where t.object_id='100'

    培训的时候没有演示成功,主要是由于统计信息不准确造成的,因为该索引扫描需要更详细的统计信息。
    由此也可见统计信息对oracle执行计划的重要性。
    对表做一下信息统计:
    begin
    dbms_stats.gather_table_stats(ownname => 'scott',
    tabname => 'TEST_IND',
    cascade => TRUE,
    degree => 4,
    estimate_percent => 100);
    end;
    再看执行计划:
    SQL> explain plan for select * from test_ind t where t.object_id='100';

    Explained

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 234563464
    --------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU
    --------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 104 | 33 (0
    | 1 | TABLE ACCESS BY INDEX ROWID| TEST_IND | 1 | 104 | 33 (0
    |* 2 | INDEX SKIP SCAN | IND_TEST_SKIP | 1 | | 32 (0
    --------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    2 - access("T"."OBJECT_ID"=100)
    filter("T"."OBJECT_ID"=100)

    否则:
    create index IND_TEST_SKIP2 on TEST_IND (OBJECT_ID,OWNER)
    select * from test_ind t where t.owner='SCOTT'
    发现全表扫描,没有使用Index Skip Scan,因为OBJECT_ID分区度大,OWNER区分度小。
    2.bit-map位图索引
    ppt-45
    适用于 重复数较少的情况,对于count运算效率最高
    不适用于并发环境
    示例:
    create bitmap index IND_TEST_BITMAP on TEST_IND (OBJECT_TYPE)
    select object_id from test_ind t where t.object_type='SEQUENCE';
    3.基于函数的索引
    字段加函数后索引一般会失去作用,由原理可知
    示例:
    create index IND_TEST_IND_N on TEST_IND (OBJECT_TYPE)
    select object_id from test_ind t where lower(t.object_type)='sequence';

    SQL> create index IND_TEST_IND_N2 on TEST_IND (lower(object_type));
    select object_id from test_ind t where lower(t.object_type)='sequence';

    总结:
    1.索引虽然会或多或少降低插入的数度,但却能大大提高查询的效率,一般来说建索引是值得的,特别对于OLTP系统,非常关键。
    2.如果可能尽可能避免访问原表
    3.根据不同的数据分布和使用条件来创建索引

    第四章:oracle分区
    分区表类型:
    单层分区
    range
    list
    hash
    多层分区
    range -- rang
    range -- list
    range -- hash
    list -- range 等所有组合
    hash

    举例:range
    CREATE TABLE time_range_sales
    ( prod_id NUMBER(6)
    , cust_id NUMBER
    , time_id DATE
    , channel_id CHAR(1)
    , promo_id NUMBER(6)
    , quantity_sold NUMBER(3)
    , amount_sold NUMBER(10,2)
    )
    PARTITION BY RANGE (time_id)
    (PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
    PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
    PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
    PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE)
    );
    ppt -46

    举例:list
    CREATE TABLE list_sales
    ( prod_id NUMBER(6)
    , cust_id NUMBER
    , time_id DATE
    , channel_id CHAR(1)
    , promo_id NUMBER(6)
    , quantity_sold NUMBER(3)
    , amount_sold NUMBER(10,2)
    )
    PARTITION BY LIST (channel_id)
    (PARTITION even_channels VALUES (2,4),
    PARTITION odd_channels VALUES (3,9)
    );

    ppt-47


    举例:hash

    CREATE TABLE hash_sales
    ( prod_id NUMBER(6)
    , cust_id NUMBER
    , time_id DATE
    , channel_id CHAR(1)
    , promo_id NUMBER(6)
    , quantity_sold NUMBER(3)
    , amount_sold NUMBER(10,2)
    )
    PARTITION BY HASH (prod_id)
    PARTITIONS 2;

     

    优点:1.物理上每个分区的数据存在一起,查询时能直接跳过不必要的分区
    2.便于清楚数据,可以直接清空分区 alter table table_name truncate partition p5;
    缺点:插入时消耗cpu

    第五章:oracle事务和锁

    一,什么是事务

    必须具备以下四个属性,简称ACID 属性:
    原子性(Atomicity): 事务是一个完整的操作。事务的各步操作是不可分的(如原子不可分);各步操作要么都执行了,要么都不执行。
    一致性(Consistency):1)一个事务结束之后,所有会话发起的查询所看到的该事务的结果都是一致的(commit后的查询有同样的结果)。
    2)一个查询的结果必须与数据库在查询开始时的状态保持一致(读不等写,写不等读)。
    隔离性(Isolation): 某个会话正在进行的事务所引起的变更对于其他会话来说必须不可见。
    持久性(Durability): 事务一旦提交完成后,数据库就不可以丢失这个事务的结果,数据库通过日志能够保持事务的持久性。

    10.2 事务的开始和结束

    10.2.1 事务采用隐性的方式,起始于session的第一条DML语句,

    10.2.2 事务结束于:
    1)COMMIT(提交)或ROLLBACK(回滚)
    2)DDL语句被执行(提交)
    3)DCL语句被执行(提交)
    4)用户退出SQLPLUS(正常退出是提交,非正常退出是回滚)
    5)服务器故障或系统崩溃(回滚)
    6)shutdowm immediate(回滚)

    二.锁类型:
    oracle读不会锁表
    1.DML锁
    (1)TX
    (2)TM
    2.DDL锁
    (1)排他DDL锁
    (2)共享DDL锁
    (3)可中断解析锁

    第六章:oracle hint
    Hint 是Oracle 提供的一种SQL语法,它允许用户在SQL语句中插入相关的语法,从而影响SQL的执行方式。
    因为Hint的特殊作用,所以对于开发人员不应该在代码中使用它,Hint 更像是Oracle提供给DBA用来分析问题的工具 。
    在SQL代码中使用Hint,可能导致非常严重的后果,因为数据库的数据是变化的,在某一时刻使用这个执行计划是最优的,
    在另一个时刻,却可能很差,这也是CBO 取代RBO的原因之一,规则是死的,而数据是时刻变化的,为了获得最正确的执行计划,
    只有知道表中数据的实际情况,通过计算各种执行计划的成本,则其最优,才是最科学的,这也是CBO的工作机制。
    在SQL代码中加入Hint,特别是性能相关的Hint是很危险的做法。
    在使用Hint时需要注意的一点是,并非任何时刻Hint都起作用。
    导致HINT 失效的原因有如下2点:
    (1) 如果CBO 认为使用Hint 会导致错误的结果时,Hint将被忽略。如索引中的记录因为空值而和表的记录不一致时,结果就是错误的,会忽略hint。
    (2) 如果表中指定了别名,那么Hint中也必须使用别名,否则Hint也会忽略。
    如:
    Select /*+full(a)*/ * from t a; -- 使用hint
    Select /*+full(t) */ * from t a; --不使用hint

    第七章:oracle表空间
    SQL> select * from dba_tablespaces;
    ppt-44
    1.system表空间
    主要存放数据字典信息(各种视图),存储过程,触发器,包等,是很重要的表空间,损坏后数据库即不可用
    2.SYSAUX
    是system表空间的辅助表空间,存储system表空间之外的元数据,如快照信息,awr报告,oem等使用该表空间存储信息

    3.undo表空间
    undo的作用
    1)使用undo tablespace 存放从datafiles 读出的数据块的前镜像,提供以下四种情况所需要的信息

    1)回滚事务:rollback
    2)读一致性:正在做DML操作的数据块,事务结束前,其他用户读undo里面的数据前镜像
    3)实例的恢复:instance recover(undo -->rollback)
    4)闪回技术 :flashback query、flashback table等
    2)可以建立多个undo表空间,但一个时刻只有一个处于active
    SQL> show parameter undo
    3)undo 数据的4种状态
    1)active: 表示transaction还没有commit,不可覆盖,
    2)unexpired:已经commit,但是还在undo_retention内,不可以覆盖(非强制),加GUARANTEE属性后强制undo_retention内不覆盖。
    3)expired: 已经commit,且时间超过了undo_retention,随时可以覆盖。
    4)free: 分配了但未使用过。
    undo retention参数和undo autoextend on特性
    undo retention参数规定了unexpired commit数据的保留期,它是保证一致性读,和大多数闪回技术成功的关键,
    将undo表空间设为autoextend on, 这是DBCA创建数据库时的缺省设置, 这一个特性将在undo空间不足时优先扩展新的空间,其次才是覆盖unexpired commit。
    如果undo retention=0,则Oracle会启用undo自动调优,它使用900秒作为undo retention的参考值。然后根据收集到的数据库中最长查询及撤销生成率自动调整undo retention。

    4.用户表空间
    存储用户数据
    5.临时表空间
    存储不能放入内存的排序数据,临时表数据,不计日志
    第四部分、oracle数据库管理简介

    第一章:oracle体系结构
    见ppt
    第二章:oracle高水位线
    高水位线(high-water mark,HWM)
    在数据库中,如果把表想象成从左到右依次排开的一系列块,高水位线就是曾经包含了数据的最右边的块。原则上HWM只会增大, 即使将表中的数据全部删除,HWM也不会降低。
    HWM不是好事,使用全表扫描时通常要读出HWM以下的所有数据块(尽管该表中可能仅有少量数据),这将白白耗费大量IO资源。
    2)两个解决办法可降低HWM:
    2.1)移动表,move方法, 将表从一个表空间移动到另一个表空间(也可以在本表空间内move)。
    语法:alter table t1 move [tablespace users];
    优点:可以清除数据块中的碎片,降低高水位线。
    缺点:move需要额外(一倍)的空间。
    move过程中会锁表,其他用户不能在该表上做DML或DDL操作。
    move之后,相关索引都不可用了,表上的索引需要重建。

    查看:
    select * from user_extents;

    delete 不降低高水位线,truncate可以
    2.2)收缩表,shrink 也叫段重组,表收缩的底层实现的是通过匹配的INSERT和DELETE操作。
    它分两个不同的阶段:压缩阶段和DDL命令阶段。(PPT-II-491)

    语法:alter table TEST_IND shrink space;

    没有演示成功问题是因为我在前面建了一个基于函数的索引,如果选择第一种move方法的话就不会有问题。

    删除索引后再次执行:

    SQL> alter table TEST_IND shrink space;

    Table altered

    shrink有以下几点限制
    ■ IOT mapping tables
    ■ Tables with rowid based materialized views
    ■ Tables with function-based indexes
    ■ SECUREFILE LOBs
    ■ Compressed tables


    优点:前后索引可用
    不需要额外的空间
    可以先执行压缩再在高峰期过后执行DDL(会有短暂的锁等待),可用性高
    缺点:产生较多的undo和redo日志

    (delete 不会降低高水位线truncate可以)

    第三章:oracle监听
    起连接作用,连接成功后即不再负责
    ppt-31 32
    session与connection的区别
    ppt-12
    连接池作用
    动态监听与静态监听
    1)动态监听:listener 采用的是默认端口(1521),当实例启动时,由pmon 每分钟自动将service name,本机的1521端口号注册到listener
    2)静态监听:当listener 一般使用的是非标准端口(如1522),在listener.ora的文件里手工注册(添加GLOBAL_DBNAME 和instance name)
    15.3.1 监听器的动态注册和静态注册
    静态注册:
    1)静态注册不需要数据库打开,通过读取listener.ora的静态注册描述完成监听器的注册,因为不需要数据库open,所以如果服务器端一旦启动了静态监听,便可以通过sqlplu以sys用户连接到服务器,实现远程启动/关闭数据库的任务。
    2)静态注册可以使用用户指定的端口号(非1521),相对隐蔽,安全。
    3)静态注册在一些特殊场合,如使用数据库复制技术时是很有用处的。
    动态注册:
    1)需要数据库打开才能注册成功,所以动态注册无法使用sysdba身份远程启动数据库。一般都是通过远程TELNET先以root登录服务器,这时你已经在服务器本地了,再转入oracle以sysdba身份打开数据库。之后监听器才可以进行动态注册。
    2)使用标准1521端口,自动注册。
    3)可以不使用listener.ora,因为动态注册由PMON后台进程自动注册信息,PMON每60秒查看listener进程是否启动,启动了就注册相关服务器信息。
    监听配置
    静态监听:
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = orcl)
    (ORACLE_HOME = d:\app\Administrator\product\11.2.0\dbhome_1)
    )
    )
    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    )
    动态监听:
    不用配置 istener.ora 文件
    第四章:flashback
    1.flashback包括:
    flashback query --闪回查询并不实际修改表的数据。
    flashback table --闪回表,恢复表中的数据。
    flashback database --闪回数据,可恢复db的数据 (备份恢复内容 暂不讲)
    .闪回查询
    select * from test as of timestamp sysdate - interval '5' minute;
    .闪回表(需要alter table enable row movement)
    flashback table tablename to timestamp systimestamp - interval '5' minute;
    flashback table tablename to before drop;

    该技术依赖于undo表空间
    第五章:oracle删除和卸载

    删除数据库:

    To drop a database with SQL*Plus:
    1.SQL> STARTUP RESTRICT FORCE MOUNT
    2.SQL> DROP DATABASE;
    数据文件和控制文件都会被删除
    3.用操作系统删除备份和归档日志(如果有的话)

    卸载数据库软件:

    Windows下ORACLE 完全卸载:

    使用OUI可以卸载数据库,但卸载后注册表和文件系统内仍会有部分残留。这些残留不仅占用
    磁盘空间,而且影响ORACLE的重新安装及系统性能。
    在WINDOWS下卸载ORACLE 10g的步骤:
    方法一:
    1, 关闭所有oracle的服务和程序
    2, 选择开始| 程序|oracle Installation Products命令,运行Universal Installer,弹出欢迎对话框
    3, 单击卸载产品 按钮,弹出Inventory对话框
    4, 选中Inventroy对话框中的所有节点,点击删除,确认即可
    5, 选择 开始|运行 输入regedit并按ENTER键,
    选择 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE,删除此象,
    然后选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services,滚动此列表,删除与oracle有关的所 有选项。
    6, 从桌面上、STARTUP和程序菜单中删除所有ORACLE的组和图标。
    7, 重启系统。
    8, 删除包括文件在内的安装目录。至此ORACLE的全部卸载完成。

    转载于:https://www.cnblogs.com/simon6623/p/8663805.html

    展开全文
  • 它们是select、update、insert、delete,这四条是用来对数据库里的数据进行操作的语言 DDL(data definition language) DDL比DML要多,主要命令有create、alter、drop等。DDL主要是用在定义或改变表的结构,数据类型...
  • oracle数据库基本sql语句 以下以在控制台内做演示: 1、请分别写出sys、system、scott三个账户的登录语句。 Sys: Win+r cmd 回车 sqlplus sys/chang_on_install as sysdba 【sys的初始密码是change_on_install】 ...
  • Oracle数据库语句基本操作 计算机科学与工程学院 《Oracle高级数据库开发》 实验项目 实验1 创建数据库和表 实验类别 操作性(√ ) 验证性() 设计性( ) 综合性( ) 其它( ) 实验目的及要求 1. 使用DBCA创建数据库,...
  • Oracle数据库 中的sql语句基本操作详细解释 有助初学者 学习
  • Oracle 数据库基本信息查询sql语句
  • Oracle数据库基本语句 一、对显示的数据的格式进行调整 1、设置每行显示的数据长度:SET LINESIZE 300; //300及30是可以自己设置的 2、设置每次显示的行数,设置分行:SET PAGESIZE 30; 3、针对某行进行格式操作:...
  • Oracle学习笔记 emp表的基础操作: SELECT * FROM emp; select * from emp where sal=3000 select * from emp where ename='SMITH' select * from emp where job<>'CLERK'; select * from emp where job!='...
  • 最好的学习就是实战!因此我的学习也就是从实战中展开的。在一切SQL语句中最基本的莫过于查询语句了,因此笔者也是从select语句开前言最好的...对于前期的学习,主要用到的是Oracle数据库中本就存在的一些表,这里笔...
  • Oracle数据库————SQL基本查询一、涉及内容 1.掌握SELECT 语句基本语法。 2.掌握常用函数的使用。二、具体操作(一)使用Scott方案下的emp表和dept表,完成以下操作: 1.查询部门编号是20的员工信息。 2.查询...
  • 一、对显示的数据的格式进行调整1、设置每行显示的数据长度:SET LINESIZE 300; //300及30是可以自己设置的2、设置每次显示...二、常用的数据库的操作1、查看当前用户:show user2、切换用户:conn 用户名【/密码】...
  • Oracle 数据库基本sql语句

    千次阅读 2018-03-31 11:00:08
    进入数据库中,首先创建一个数据表:CREATE TABLE TABLE——NAME(COLUMN-NAME COLUMN_TYPE,);举例说明:CREATE TABLE STUDENT(SNO CHAR(20) PRIMARY KEY,SNAME CHAR(40) NOT NULL,SAGE INT,SSEX CHAR(5));再来细讲...
  • Oracle数据库SQL语句操作记录

    千次阅读 2015-12-22 10:15:56
    Oracle数据库SQL语句操作,包括创建用户并分配表空间权限、表操作、触发器序列、SQL基本操作、一些基础函数的操作等。
  • 这篇论坛文章(赛迪网技术社区)详细的介绍了Oracle数据库SQL语句性能调整的基本原则,具体内容请参考下文。 一、问题的提出 在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写...
  • --单表查询 select * from emp; --查询表中指定字段的值 select empno,ename from emp; --给查询结果中的字段使用...--||为sql语句的字符连接符,使用在select和from之间 --字符链接格式为字段名||'字符'||字段名 -...
  • --甲骨文公司:大型的关系数据库 --对表的操作:增、删、改、查 --查询表:在关系数据库使用SELECT语句 --开始--&gt;运行--&...--在Oracle中导入脚本:@脚本的路径,...--基本SELECT语句 SELECT 列名[,列...
  • OracleSQL第一章SELECT查询本章目标写一条SELECT查询语句在查询中使用表达式运算符对空值的处理对查询字段起别名查询字段的连接SELECT查询基本语法SELECT <列名> FROM <表名> 请查询出s_emp表中所有的员工信息:SQL> ...
  • oracle数据库基本操作语句

    千次阅读 2015-02-08 20:08:53
    本文主要介绍了oracle数据库基本的管理数据库的语句及其概念

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,778
精华内容 1,111
关键字:

oracle数据库基本语句