Oracle总结

一.数据库相关概念介绍

1、DB(Database):数据库

DBA(DataBase Administrator):数据库管理员

DBMS(DataBase Management System):数据库管理系统

2、数据库的种类:

oracle / sybase / db2(IBM)

sql server / mysql /

access

 

3、数据表结构:由行和列组成.

行: row / record

列: column / field

 

4、SQL分类:

DQL(数据库查询语言): select

DML(数据库管理语言): insert / update / delete

DDL(数据库定义语言): create / drop / alter / truncate

DCL(数据库控制语言): grant / revoke

TCL(事务控制语言): commit / rollback / savepoint

注:

 

5、远程登录到Oracle数据库服务器的方式:

登录到远程主机:

C:>telnet 192.168.0.26

用户名/密码: openlab/open123

这组是远程机器的用户名和密码

 

远程登录后连接数据库:sqlplus

sqlplus是Oracle数据库提供的命令行客户端工具

sunv210% sqlplus openlab/open123

这组是数据库的用户名和密码

 

6、了解练习常用到的数据表:

emp: 职员表

dept: 部门表

salgrade: 薪水等级表

 

desc命令:查看一个表的结构.

如:desc dept

 

  1. Oracle 数据库中常用的数据类型

    varchar2(长度) 可变长字符串,它按照字符串的实际长度存储。

    如:varchar2(10)表示最大长度为10的可变长字符串

    char(长度) 定长字符串,它存储不足的会用空格补齐,默认长度 1                     个字节 ,如:char(10),表示固定长度为10的字符串

    Number() 表示整数或者浮点数精度为 38位,取值范围 1~38之间

    number(7,2)表示数字类型,一共7位,小数点后两位,最大 99999.99

    number(5) 表示5位整数, 最大99999

    number 随便

    date 日期类型,

    blob 大对象类型,存二进制对象(声音、图片)

 

二、数据库查询:select

格式:select [列1,列2 别名,.......] from [表名] where [过滤条件];

说明:

1、-- *号表示查询全部列

select * from dept;    //查询dept表中的所有列

select ename, sal, comm from emp; //查询emp表中的ename、sal、comm列

 

2、-- 列别名:当对某一列取了别名后

 

3、列的算术表达式:

如:select ename,sal+comm total_sal from emp;

 

4、WHERE子句用来选择符合条件的记录。

?、= 表示结果符合等于指定条件

如://查询emp表中所有的职位为经理的员工名字和职位

elect ename, job from emp where job = ‘MANAGER‘;

 

?、between ... and ... 表示结果在这之间,between and 是一个闭区间

如://查询emp表中薪水在1000到2000之间的员工名字和薪水

Select ename ,sal from emp where sal between 1000 and 2000;

 

?、!=,<>,^= 这三个都可以表示不等于;

如://查询emp表中不在部门10工作的员工姓名和工作部门

Select ename ,deptno from emp where deptno !=10;

 

④、in (va1,val2,...) 判断结果是否在这个集合中存在

如://查询emp表中在部门10和20 工作的员工姓名和部门

Select ename ,deptno from emp where deptno in (10,20);

 

⑤、like ‘...‘ 表示字符串通配查询,‘%‘表示 0 或多个字符, ‘_‘ 表示一个字符;

如://查询emp表中 员工姓名中包含字母"M"的员工,列出其姓名

Select ename from emp where ename like ‘%M%‘;

注: escape转义的用法:like ‘S\_%‘ escape ‘\‘ ,表示查询时匹            配字符"_",而不是表示一个字符

⑥... and ...             表示只有两个条件同时满足;

如:// 查询emp表中在部门10中工作且薪水大于2000的员工,列出姓名

薪水,部门号

Select ename,sal,deptno from emp where sal>2000 and deptno=10;

⑦、... or ...             表示条件只要满足其中之一就可以;

⑧、all ...                 是要求都满足条件;

⑨、not .....             可以与以上的条件产生相反的效果;             注:空值与任何值比较结果都为空

⑩、... is null             用来判断值是否为空。

 

  1. ORDER BY子句

    格式:order by 目标列名(别名) 排序顺序(不写则默认为升序)

     

    说明:ORDER BY子句使得 SQL在显示查询结果时按指定行的指定顺序排列

    ASC(默认,升序) ,不怎么时默认

    DESC(降序)

    例如: //查询emp表中ename列,并按ename升序排序

    select ename from emp order by ename;

    //查询emp表中ename列,并按ename降序排序

    select ename from emp order by ename desc;

 

  1. oracle 数据库函数

    注意:dual 表(哑表)是专门用于函数测试和运算的.

    单行函数 :一个值输入,一个值输出

    组函数:多个值输入,一个值输出

    1、字符函数 (字符是大小写敏感 的 )

    转小写 lower(字段名)

    转大写 upper(字段名)

    首字母大写 initcap(字段名)

    字符串拼接 concat(字段 1, 字段 2)

    截取子串 substr(字段名, 起始位置,取字符个数)

    字符串长度 length(…)

    空值函数 nvl(…) ,常用

    例: select first_name,substr(first_name,2,2) sub from s_emp;(从名字的第二个字符

    开始取两个字符)

    select first_name,substr(first_name,-2,2) sub from s_emp; (从名字的倒数第二个

    字符开始取两个字符)

     

    2、数值函数

    ?、 四舍五入函数 round(数据,保留到小数点后几位)

    1 表示保留到小数点后一位,-1 表示保留到小数点前一位。

    例:select round(15.36,1) from dual; //结果为15.4

    ?、 截取数值函数 trunc(数据,保留到小数点后几位)

    例:select trunc(123.456,1) from dual; //结果为123.4

    截取到小数点后一位,注意:与 round 函数不同,不会四舍五入。

    ?、取余函数 mod(被除数,除数)

    列:select mod(16,5)from dual; //结果:1

     

  2. 日期函数

    ?、取系统时间: sysdate(Oracle独有的函数)

    select sysdate from dual;

    ?、日期的加减

    select sysdate-1,sysdate,sysdate+1 from dual; 取系统时间的前一天,当天和后一天

    select sysdate,sysdate+1/144 from dual; 延迟 10 分钟

     

    ④、1、months_between(sysdate,hiredate):月份在当前时间和入职时间之间。

    2、add_months(sysdate,6):在当前时间上加6个月。

    3、sysdate+6:当前时间加6天。

    4、next_day(sysdate,‘FRI‘):下一个周五(中文环境下要写"星期五")。

    5、next_day(sysdate,5):下一个周四(数字中英文通用)。

     

    4、不同数据类型间的转换

    ?、将日期转换为指定格式字符串 to_char(date,‘日期格式‘)

    //将当前系统时间转换为 年月日 时(24小时制)分秒的形式

    select to_char(sysdate,‘yyyy mm dd hh24:mi:ss‘)from dual;

    ?、将指定格式字符串转换为日期 to_date(‘字符串‘,‘日期格式‘)

    //如:将字符串"2011-03-20"转换为日期

    Select to_date(‘2011-03-20‘,‘yyyy-mm-dd‘) from dual;

    ?、to_number(‘$12345.67‘,‘$99999.99‘)

    :将字符串"$12345.67"转换成数字12345.67(后面$99999.99表示前面数字的显示格式,)

    ④、to_char(‘1234‘,‘9999.00‘):结果为:1234.00,后面的0表示不足位用0补。

     

  3. group by 分组函数

    group by 分组子句 对分组后的子句进行过滤还可以用having不能用where

    如://查询平均薪水大于2000的部门,并且按部门分组,列出部门号和平均薪水

    select deptno ,avg(sal) from emp group by deptno having avg(sal) >2000;

    --获得每个部门每个职位的平均薪水

    select deptno, job, avg(sal) from emp group by deptno, job order by deptno, job;

     

  4. 其他常用组函数

    Avg() 求平均值函数

    Count()统计数量的函数

    Max()求最大值函数

    Min()求最小值函数

    Sum()求和值函数

     

    注:所有组函数会忽略空值(可用空值处理函数解决), avg sum只能作用于数字类型

    //求emp表中所有人的奖金的平均值

    select avg(nvl(comm,0 ) ) from s_emp;

     

     

     

  5. 表连接

    概念区分:等值连接、非等值连接;内连接、外连接、自连接;

    1、等值连接、非等值连接:当连接运算符为=时,称为等值连接。使用其它运算符称为非等值        连接。

    如://查询员工名字和所在的部门名字

    select e.ename,d.dname from emp e ,dept d where e.deptno=d.deptno;

     

    1. 内连接、外连接、自连接
    2. 、自连接:连接操作不仅可以在两个表之间进行,也可以是一个表与其自己进行连接,这种连接称为表的自身连接
    3. 、内连接:相对于外连接而言,进行连接的两个表对应的相匹配的字段完全相同的连接。

      默认空值不匹配

    c) 、外连接:相对于内连接而言,匹配空值,分为左外连接、右外连接和全外连接

    三者分别表示:匹配左边为空的情况、匹配右边为空的情况、匹配左右任意一边为空的情况

    作用:

    例如:列出所有没有员工的部门

    select e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptno

    and e.deptno is null;

    列出所有没有部门的员工

    select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno(+)

    and d.deptno is null;

    列出所有员工及所有的部门,包括没有员工的部门和没有部门的员工

    Select e.ename d.dname from emp e full outer join dept d on(e.deptno=d.deptno);

    列出不是领导的员工

    select manager.ename from emp worker right outer join emp manager

    on worker.mgr = manager.empno where worker.empno is null;

     

  6. 子查询

    1、概念:可以嵌在 sql 语句中的 select 语句。

    Sql语句执行时会先执行嵌套的子查询语句,再将查询结果带到主查询中执行。

    1. 应用

      如:找出员工中薪水最低的员工,列出姓名及其薪水:

      Select ename ,sal from emp where sal =(select min(sal) from emp);

      查询谁的薪水比本部门的平均薪水高

      select e.ename e.sal a.avgsal from emp e ,(select deptno, avg(sal) from emp group by     deptno ) a where e.deptno=a.deptno and e.sal>a.avgsal;

     

    六、对表本身的操作

    1. 创建表
    2. 、create table mytable (列1 类型 约束,列2 类型 .....);
    3. 复制表(包括结构和数据)
      1. create table emp_copy as select * from emp;
      2. creare table emp_copy1 as select empno,ename,sal,deptno from emp;
      3. create table emp_copy as select * from emp where 1=0;

        // 后面where给的条件永远为false ,结果为只复制结构,没有数据。

       

    七、表中数据的操作

    1. insert 操作

      格式:insert into 表名(列名1,列名2,...) values (列1值,列2值,....);

      注:当插入的记录包括所有列时,不用再表名后注明列名

      如:假设有表student 只包含两列为stu_id,stu_name时,插入记录

      insert into student values (1001,‘张三‘);

    2. 、insert 一条记录

      Insert into emp(empno,ename,sal) values (1001,‘张三‘,1500);

       

    3. 、insert 多条记录:后面接上select的结果(要求结果和要插入的表结构一致)

      Insert into student select empno,ename from emp;

    4. update 更新数据

      格式:update 表名 set 列名=此列新的值 where 过滤条件

       

    5. 、将7788号员工的薪水加800元

      Update emp set sal =sal+800 where empno=7788;

       

    b) 、改7788号员工的薪水为1800同时改job 为salsman

    Update emp set sal = 1800,job = ‘salsman‘ where empno=7788;

    // 多个字段用","隔开

     

    1. delete 删除表中数据

      格式:delete 表名 where 过滤条件;

    2. 、删除emp表中部门号为10的数据

      delete emp where deptno=10;

      注意:当表中数据存在被子表应用时,是不能直接删除的 ,如:

      delete * from dept where deptno =10;//子表 emp中存在deptno=10的引用

      但如果子表中有on delete 约束时 就可以 如:

      create table student_wan(id number(10) primary key ,name varchar2(30) ,mid number(2), constraint stunwan_mid_fk foreign key (mid) references major_wan(mid) on delete set null);

      // 删除部门10 后 子表中相关数据将设置为空。

       

    3. 事务的概念:

      当开始执行DML操作时,就开始了一个事务。当事务开始后正在操作的数据是加锁的,别人不能同时操作(挂起)。

      事务的结束动作就是 commit; DDL, DCL语句执行会自动提交 commit。

      sqlplus 正常退出是会自动做提交动作的 commit;,当系统异常退出时,会执行回滚(撤销改动)操作 rollback;。

      一个没有结束的事务,叫做活动的事务 (active transaction),活动的事务中修改的数据时,只有本会话(session)才能看见。

      用处:操作过程中可以用savepoint保存状态,再用rollback to 返回设置的保存点。(前提是没有提交commit)

      如:delete from emp;//删除emp中数据

      Savepoint A;

      Rollback to A;//数据恢复了

       

    八、数据库的主要对象

    1. 视图:(很向软连接)
    2. 、创建视图:create [or replace] view_name as ......;

      Create or replace view v_emp_copy as select ename ,sal , from emp_copy;

      // 取emp_copy表的ename和sal字段创建视图 v_emp_copy;

      // 视图是虚的,如果没有就创建,如有就replace

       

      注:Create or replace view v_emp_copy as select deptno,avg(sal)from emp_copy group by deptno;// 错;

      // 视图的来源中的数据不是基表原始数据,而是计算后的数据,这样的视图叫复杂视图,复杂视图中avg(sal)不能作为列名,需要给出别名

      如下:

      Create or replace view v_emp_copy as select deptno,avg(sal) avg_sal from emp_copy group by deptno;

    b) 、和查询表一样查询视图

    Select * from v_emp_copy ;

    c) 、删除视图:

    Drop view view_name;

     

    注:此时往表base table 基表)中插入数据时:表中没有变化,示图

    中的数据发生改变,从示图中插数据时相对应的表会发生改变:往示图中

    插数据时,会直接插进基表中,查看示图中的数据时,相当于就是执行创

    建时的select语句。

     

    简单示图:能进行DML操作。

    复杂示图:来源于多张表,不能执行DML操作。

     

    1. 数据字典

      数据字典的种类:(**表示table、view等对象

      user_*****; 当前用户下的表、视图等对象

      all_*****;    当前用户的和可访问的其他用户下的表、视图等对象

      dba_*****;    数据库下所有用户下的表、视图等对象

      注:dba_***包含all_***,all_****包含user_*****;

    2. 、用户名下的数据表:user_tables(只读的,系统自动管理)

      Select count(*) from user_tables;//结果 n个表

      Select table_name from user_tables where rownum<20;

      //查询前19个表的表名

      b) 、视图表:user_views(只读的,系统自动管理)

      Select text from user_views where view_name=‘view_name‘ ;

      // 查询指定视图对应的查询语句(视图本身就相当于一条查询语句)

       

    3. 索引

      索引对系统的性能影响非常大,创建索引的目的就是为了加快查询的速度,让表中的数据在做insert时就进行排序,索引是DML语句触发的,系统自动运行,也可以强制使用索引。

    4. 、概念:FTS(full table scan)全表扫描,效率低

       

    5. 序列(sequence)

      创建序列:create sequence 序列名;

       

      (不带参数时默认为从 1 开始每次递增 1,oracle 中为了提高产生序列的

      般一次性产生 20 个序列放入当前会话的序列池中备用以加快效率)

     

    sequence 的参数:

    increment by n 递增量

    start with n 起始值

    maxvalue n 最大值

    minvalue n 最小值

    cycle|no cycle 循环

    cache n 缓存(第一次取时会一次取多少个 id 存起来)

     

    如:create sequence mysequence start with 10 increment by 10;

    对序列的操作:

    Desc mysequence;查看序列类型

    //查看当前用户下的所有序列,列出序列名,缓存大小,最大值

    select sequence_name , cache_size , last_number from user_sequences ;

    currval 当前的序列数

    nextval 下一个序列数,它会自动给当前的序列加一个步长值。

    drop sequence 序列名; //删除序列 sequence

     

     

    1. Oracle中约束

     

    针对表中的字段进行定义的。

    1、primary key(主键约束 PK) 保证实体的完整性,保证记录的唯一。

    主键约束,唯一且非空,并且每一个表中只能有一个主键,有两个字段联合作为主键时,将两个字段组合在一起唯一标识记录,叫做联合主键。

     

    主键约束的定义:

    第一种定义形式:

    create table test(c number primary key ); 列级约束

     

    第二种定义形式:

    create table test(c number , primary key(c) ) ; 表级约束

    create table test(c1 number constraints pk_c1 primary key ); 对约束指定名字为

    pk_c1

    create table test(c number , c1 number , primary key (c ,c1) ) ; 用表级约束可以实现

    联合主键

     

     

    1. foreign key(外键约束 FK)保证引用的完整性,外键约束,

外键的取值是受另外一张表中的主键或唯一值的约束,不能够取其他值,只能够引用        主键或唯一键的值,

被引用的表,叫做 parent table(父表),引用方的表叫做 child table(子表),

要想创建子表,就要先创建父表;记录的插入也是如此,先父表后子表;

删除记录,要先删除子表记录,后删除父表记录;

要修改记录,如果要修改父表的记录要保证没有被子表引用。

要删表时,要先删子表,后删除父表。(可以通过使用 cascade constraints 选项来                删除父表)

 

carete table parent(c1 number primary key ); 列级外键约束定义

create table child (c number primary key , c2 number references parent(c1));

表级外键约束定义

create table child( c number primary key , c2 number , foreign key(c2) references

parent(c1));

 

3、not null(非空约束NN)这是一个列级约束,在建表时,在数据类型的后面加上

not null ,也就是在插入时不允许插入空值。

例:create table student(id number primary key,name varchar2(32) not null,address

varchar2(32));

 

4、unique(唯一约束UK)

唯一约束,允许为空,要求插入的记录中的值是唯一的。

例:create table student(id number,name varchar2(32),address varchar2(32),primary

key (id),unique (address));

 

5、check 约束

检查约束,可以按照指定条件,检查记录的插入。check 中不能使用伪列,不能

使用函数,不能引用其他字段。

例:create table sal (a1 number , check(a1>1000));.

 

十、Oracle 中的伪列

1、伪列就像 Oracle 中的一个表列,但实际上它并未存储在表中。伪列可以从表中

查询,但是不能插入、更新或删除它们的值。

2、常用的伪列:rowid 和 rownum。

rowid:数据库中的每一行都有一个行地址,rowid 伪列返回该行地址。可以使用rowid             值来定位表中的一行。通常情况下,rowid 值可以唯一地标识数据库中的一行。

rowid 伪列有以下重要用途:

1)能以最快的方式访问表中的一行;

2)能显示表的行是如何存储的。

3)可以作为表中行的唯一标识。

如:SQL> select rowid, name from s_dept;

 

rownum:对于一个查询返回的每一行,rownum 伪列返回一个数值代表的次序。

rownum 伪列特点:

1)有个特点:要么等于 1要么小于某个值或小于等于某个值,不能直接等于某个

值, 不能大于某个值。

2)常用于分页显示。

返回的第一行的 rownum 值为 1,第二行的 rownum 值为 2,依此类推。通过使

用 rownum 伪列,用户可以限制查询返回的行数。

 

如:SQL>select * from s_emp where rownum<11; 从 s_emp 表中提取 10 条记录。

如:select rn,c1 from(select rownum rn, c1 from test where rownum <= 5)

where rn between 3 and 5; //提取第 3 条到第5 条的记录

注:between and 不能直接对rownum使用,但可以对象此句中那样将rownum作    为子    查询结果的一列(取别名),再在主查询中使用

如:select rownum ,table_name from user_tables where rownum between 3 and 5;

//此句永远显示返回数据为空

select rn,table_name from (select rownum rn,table_name from user_tables) where rn     between 3 and 5;

//这句就行

 

如:找出第二名

select * from (select rownum rn , a.* from (select max(s.name),sum(grade) sg

from stu s,grade g

where s.id=g.sid

group by s.id

order by sg desc ) a)

where rn=2

Oracle总结,布布扣,bubuko.com

Oracle总结

上一篇:Photoshop为室内性感美女加上柔美的韩系淡红色


下一篇:CAD图形对象特性选项卡有哪些功能?