Menphis_茂峰

业精于勤荒于嬉,行成于思毁于随

Oracle基础整理

一、数据库原理

1. 数据库的基本概念:

    数据库(Database,简称DB)是按照数据结构来组织、存储和管理数据的仓库。

    数据库管理系统(DatabaseManagement System,简称DBMS)管理数据库的软件。

2. 关系数据库

    关系:描述两个元素间的关联或对应关系

    使用关系模型把数据组织到二维数据表中

    产品化:Oracle、DB2、Sybase、SQL server、MySQL

3. 表的概念

    一个关系数据库由多个数据表组成,数据表是数据库的基本存储结构。

    表是二维的,由行和列组成。

    表的行(row)是横排数据,也被称作记录(Record)

    表的列(Column)是纵列数据,也被称作字段(Field)

    表和表之间存在关联关系。

 

二、主流关系型数据库

1. Oracle数据库

   Oracle(甲骨文)公司的数据库产品;

    世界上第一个商品化的关系型数据库管理系统;

    采用标准SQL(结构化查询语言),支持多种数据类型,提供面向对象的数据支持;

    支持多种平台。

 

2. DB2数据库

   IBM公司的关系型数据库管理系统;

    很多不同版本,可以运行的从掌上产品到大型机不同的终端机器上

   DB2 Universal Database Personal Edition 和

   DB2 Universal Database Workgroup Edition分别是单用户和多用户系统,

    可以运行在OS/2和windows上

   DB2是oracle的主要竞争对手

 3. Sybase数据库

    美国Sybase公司的关系型数据库系统

    较早采用C/S技术的数据库厂商

    典型的UNIX或Windows NT平台上客户机/服务器环境下的大型数据库系统。

   Sybase通常与Sybase SQL Anywhere用于客户机/服务器环境,前者作为服务器数据库,

    后者作为服务器数据库,后者为客户机数据库,采用PowerBuilder作为开发工具,

    国内大中型系统中具有广泛的应用;

   2010年被SAP收购

 

4. SQL server数据库

    微软产品,运行在WindowNT服务器上

    广泛适用于大型、跨国企业的数据库管理

 

5. MySQL数据库

    开源的小型关系型数据库管理系统,广泛用于中小型网站中;

    成本低、规模较Oracle和DB2小;

   2008年1月16日,被Sun收购MySQL,2009年4月20日,Sun被Oracle收购;

 

三、SQL结构化程序语言

1. 数据定义语言(DDL)

   Create:创建表或其他对象的结构

   Alter:修改表或其他对象的结构

    Drop: 删除表或其他对象的结构

   Truncate:删除表数据,保留表结构

2. 数据操作语言(DML)

   Insert:将数据插入到数据表中

   Update:更新数据表中已经存在的数据

   Delete:删除表中的数据

3. 事务控制语言(TCL)

    Commit: 提交,确认已经进行的数据改变

   RollBack:回滚,取消已经进行的数据改变

   Savepoint:保存点,是当前事务可以回退到指定的保存点,便于取消部分改变

4. 数据查询语言(DQL)

   Select:查询所需要的数据

5. 数据控制语言(DCL)

   Grant:授予,用于给用户或角色授予权限

   Revoke:用于收回用户或角色已有的权限

   Create user:创建用户

6. 相关概念(非重点)

   SQL*Plus是Oracle提供的数据库交互工具。基于传统C/S结构的SQL开发工具。

 

四、Oracle数据类型

1. NUMBER

    数字类型,NUMBER(P , S)形式,P:表示数字的总位数;S:表示小数点后面的位数

    例:

   Sal number(6,2)  》最大取值为9999.99

2. CHAR

    固定长度的字符类型,CHAR(N)形式,N:表示占用的字节数

    例:

   ename char(20)   》最多存储20个字符串,占用空间是固定长度20个字节

3. VARCHAR2

    变长的字符类型,VARCHAR2(N),N:表示最多可占用的字节数

    例:

   Job varchar2(100)  》最多存储100个字符串,占空间变化,最多100个字节空间

4. DATE

    用于定义日期时间的数据,长度是7个字节,默认格式DD-MON-RR

 

五、数据定义语言(DDL)与一些SQL语句

1. create语句

   Create table 表名(

    列名 列的数据类型[定义默认值或约束],

    列名 列的数据类型

    )

例:

    创建表employee

   Create table employee(

   Id number(4),

   Name varchar2(20),

   Gender char(1),

   …..

    )

2. DESC语句

   Desc 表名;查看表结构

    例:

    查看employee表结构

   Desc employee;

3. DEFAULT语句

    用于给列指定默认值

    例:

    给gender指定默认值‘M‘

    创建表employee

   Create table employee(

   Id number(4),

   Name varchar2(20),

   Gender char(1) default ‘M’,

   …..

    )

4. NOT NULL

    非空约束,确保字段值(列值)不为空

    例:

    定义id非空约束

    创建表employee

   Create table employee(

   Id number(4) NOT NULL,

   Name varchar2(20),

   Gender char(1) default ‘M’,

   …..

    )

5. 修改表

   Rename修改表名

   Rename 旧表名 to 新表名;

   Alter 表名 add  》给表增加列

    例:

    给表employee增加列hiredate

   Alter table employee add( hiredate date default sysdate);

 

   Alter 表名 drop(列名) 》给表增加列

    例:

    给表employee删除列hiredate

   Alter table employee drop( hiredate);

 

    Alter表名 modify(列名列的数据类型) 》给表增加列

    例:

    修改表employee列hiredate

   Alter table employee modify( hiredate char(7));

 

六、DML(数据操作语言)语句

1. insert语句

    给数据表增加记录

   Insert into 表名[(列名,列名….)] values(对应列的数据,对应列的数据);

    例:

    插入一条记录:

   Insert into employee(id,name,job,salary)values(1001,’rose’,’good’,100000);

2. update语句

    更新表中的记录

   Update 表名 set  列名=新数据 where 条件

    例:

    更新rose薪水为250;

   Update employee set salary=250 where id=1001;

3. delete语句

    删除表中的记录

   Delete from 表名 where 条件;

    例:

    删除id=1001的记录

   Delete from employee where id=1001;

 

七、Oracle字符串操作

1.字符串类型

   Char和VARCHAR的默认单位为字节,可指定为字符

   CHAR:定长字符,存不满不空格;最大取值为2000字节,不指定长度默认人值为1,

   VARCHAR2:变长字符,存多少占用多少;最大取值为4000字节,必须指定长度

    每个英文字符占用一个字节,每个中文按编码不同占用2-4个字节

   ZHS16GBK:2个字节,UTF-8:2-4个字节

    例:

   Char(10),等价于char(10 byte),指定单位为字符char(10 char)

   Long和clob类型

   Varchar的加长版,存储变长;

   long最多存储2gb的字符数据;clob最多存储4gb(常用)

 

2.字符串函数

   Concat和‘||’

   Concat(char1,char2)返回两个字符串连接后的结果,

    例:

   Select concat(concat(ename,’:’),sal) from emp;

    多个字串连接,用||更直观

   Select ename ||’:’|| sal from emp;

 

   Length(char):用于返回字符串的长度。

    如果是varchar2返回实际长度,如char,返回定长(含空格)

    例:

   Select ename,length(ename) from emp;

 

   Upper、lower和initcap

   Upper:转换成大写形式;lower转换成小写形式;initcap用于将字符串中首位大写

 

   Trim、Ltrim、Rtrim

    用于截去子串;

   Trim(c2 from c1):从c1的前后截去c2

   Ltrim(c1,c2):从c1的左边截去c2

   Rtrim(c1,c2):从c1的右边截去c2

    例:

   Select trim(‘e’ from ‘elite’) as “t1”, Ltrim(‘elite’,’e’) as “t2”,Rtrim(‘elite’,’e’)as “t3” from dual;

    输出结果为:

   T1  T2   T3 

   ---  ----  ----

   lit  lite  elit

 

   Lpad(char1,n,char2)、Rpad(char1,n,char2)

    补位函数,用于在字符串左(Lpad)或右(Rpad)补足n位,char2可重复多次

    例:

   Select ename,Lpad(sal,6,’$’)as salary from emp;

    结果为$$4000,$10000

 

   Substr(char,m,n)

    获取字符串的字串,从1开始,第m个字符开始,,n长度。

    例:

   Select substr('youou',3,2) from dual;

    结果为:

 

 

   Instr(char1,char2,m,n)

    用于返回子串char2在原字符串char1中的位置,从1开始,第m个字符开始,n为第几次出现。

    例:

   Select instr(‘youou’ ,'o’,1,2) from dual;

    返回结果为4

 

八、Oracle数值操作

1.数值类型

   Number(p)表示整数,number(p,s)表示浮点数

 

   Round(n[,m]):用于四舍五入;m为0取整,m为负数则四舍五入到小数点前m位

    例:

   Round(5456.45,-1)from dual;---------5460

 

   Trunc(n[,m]):非四舍五入,而是截取数值;

    例:

   Trunc(5456.45,-1)from dual;---------5450

 

   Mod(m,n):返回m除以n后的余数,n为0则直接返回m

 

   Ceil(n)和floor(n)取n的最大或最小整数值;

    例:

   Select ceil(45.678) from dual;--------46

   Select floor(45.678) from dual;--------45

 

九、Oracle日期操作

1. 日期类型

   Date类型在数据库中的存储固定为7个字节

 

   Timestamp:Oracle常用的日期类型,最高精准度可以到达ns(纳秒),7或11个字节;

 

2. 日期关键字

   Sysdate本质是一个内部函数,返回系统当前时间,精准到秒;格式:DD-Mon-RR

    例:

   Select sysdate from dual;-------------- 23-7月 -15 

 

   Systimestamp 本质是内部函数,精准到毫秒

    例:

   Select systimestamp from dual ;---------- 23-7月 -1510.34.26.893000000 上午 +08:00

   Select to_char(systimestamp,’ssss.FF’) from dual;-------5050.922000

 

3. 日期转换函数

   To_date:将字符串按照定制格式转换为日期类型

   To_char:将其他类型的数据转换为字符类型

 

4. 日期常用

   Last_day:返回日期date所在月的最后一天;

   Add_months:返回日期date加上i个月后的日期值

    例:

   Select ename,add_months(hiredate,20*12) as “20周年” from emp;

 

   Months_between(date1,date2):计算date1和date2两个日期之间隔了多少个月

    例: 

   Select ename, months_between(sysdate,hiredate) from emp;

 

   Next_day(date, char):返回date日期数据的下一个周几;

    例:

    查询下个周三是几号;

   Select next_day(sysdate,4) from dual;

 

   Least、greatest:比较函数,返回结果是参数中最大或最小的值

    例:

   Select least(sysdate,’10-10月-08’) from dual;

 

   Extract:从参数datetime中提取参数date指定的数据,比如年,月,日;

    例:

   Select extract(year from sysdate) current_year from dual;-----------2015

   Select extract(hour from timestamp ‘2008-11-1 10:16:14’) fromdual;--------10

 

 

 

十、空值操作

1.null的操作

    更新成null值

    例:

   Update student set gender=null;

 

    条件查询null

    例:

   Select * from student where gender is null;

 

    非空约束

    例:

   Create table student(

   Gender char(1) not null

   );

2.空值函数

   NVL(expr1,expr2)与NVL2(expr1,expr2,expr3)

    NVL如果expr1为空,则返回expr2

   NVL如果expr1不为空,则返回expr2;如果expr1为空,则返回expr3;

 

十一、SQL基础查询

1.基本查询语句

   From子句

    例:

   Select * from dept;

 

    使用别名

    例:

   Select empno as id, ename “name”, sal*12 “annual salary” from emp;

 

   Where 子句

    例:

   Select * from emp where deptno=10;

 

   Select 子句

    例:

   Select enamel,sal from emp;

2.查询条件

    使用>,<,>=,<=,!=,<>,=

    使用and,or关键字

    使用like条件(模糊查询),通配符:%:表示0 到多个字符 ;_:表示单个字符

    例:

   Select ename,job from emp where ename like‘_A%’;

 

    使用in和not in比较操作符IN(list)用来取出符合列表范围中的数据

    例:

   Select ename,job from emp where job in(‘manager’,’clerk’);

 

    使用between…and…操作符用来查询符合某个值域范围条件的数据

    例:

   Select ename,sal from emp where sal between 1500 and 3000;

 

    使用is not null和is null

    例:

   Select ename,sal,comm from emp where comm is null;

 

    使用any和all,不能单独使用 

    例:

   Select empno,ename,job,sal,deptno from emp where sal>any(3500,4500);

 

    使用distinct过滤重复

   Select distinct deptno from emp;

 

3.排序

    使用order by子句

    例:

   Select ename,deptno from emp order by deptno;

 

   ASC和DESC

    默认排序是ASC升序,DESC来指定降序

    例:

   Select empno,ename,mgr from emp where deptno=10 order by mgr;

 

4. 聚合函数

   Max和min表示取列的最大、最小值

    例:

   Select max(sal),min(sal) from emp;

 

   Avg和sum用来统计平均值和总值

    例:

   Select avg(sal),sum(sal) from emp;

 

   Count用来统计表中的记录条数

    例:

   Select count(job) from emp;

 

 

 

5. 分组

   Group by子句:用于分组

    例:

    每个部门的最高薪水

   Select deptno,max(sal) from emp group by deptno;

 

   Having子句:使用分组时的过滤条件

    例:

   Select deptno,max(sall) from emp group by deptno havingmax(sal)>4000;

 

6.查询语句顺序

   1. from子句:从后往前,数据量较少的表尽量放在后面

   2. where子句:自下而上,从右往左,将能过滤最大记录数量的条件放在右边

   3. group by子句:从左往右分组,group前最后使用where过滤不需要的记录

   4. having子句:消耗资源,尽量避免使用

   5. select子句:少使用*号,尽量取字段名称,通过数据字典转换成列名,消耗时间

   6.order by子句:从左往右,消耗资源;

 

十二、SQL(关联查询)

1.关联基础

    需要查询两个或两个以上数据表或视图的查询叫连接查询

    例:

    Select table1.column, table2.column fromtable1,table2

   where table1.column1=table2.column2;

 

    笛卡尔积:关联操作每个表的每一行都和其他表的每一行做组合,两表记录xy,返回x*y

 

    等值连接:通常实在主外键关联关系的表间建立;

    例:

   Select e.name ,e.job, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno;

2.关联查询

    内连接where或join….on….:内连接所有满足连接条件的记录;

    例:

   Select e.name ,e.job, d.dname, d.loc from emp e, dept d wheree.deptno=d.deptno;

    等价于

   Select e.name,e.jon, d.dname, d.loc from emp e join dept d one.deptno=d.deptno;

 

    外连接Left|Right|Full[outer] join…on…

    例:

   Select e.name, e.job, d.dname, d.loc from emp e Left|Right|Full [outer]join dept d

   On d.deptno=e.deptno;

 

    自连接:略

 

十三、SQL高级查询

1.子查询

    子查询在where子句中

    例:

    查询和scott同职位的员工

   Select e.ename,e.job from emp e where e.job=(Select job from emp whereename=‘scott’);

    查找薪水比整个机构平均薪水高的员工

   Select deptno,ename,sal from emp where sal>(select avg(sal) from emp);

    查询部门中有salesman但职位不是salesman的员工信息

   Select empno,ename from emp where deptno in (Select deptno from empwhere job=’salesman’)and job <> ‘salesman’;

    列出那些有员工的部门信息

   Select deptno, dname from dept d where exists(Select * from emp e whered.deptno=e.deptno)

 

    子查询在having子句中

    查询最低薪水高于部门30的最低薪水的部门信息

   Select deptno, min(sal) from emp group by deptno havingmin(sal)>(Select min(sal)Select min(sal) from emp where deptno=30);

 

    子查询在From部分

    查询出薪水比本部门平均薪水高的员工信息

   Select e.deptno,e.ename, e.sal from emp e join (Select deptno,avg(sal)avg_sal from emp group by deptno) x

   on e.deptno=x.deptno and e.sal>avg_sal order by e.deptno;

 

    子查询在select部分

   Select e.ename,e.sal,(select d.dname from dept d whered.deptno=e.deptno) dname

   From emp e;

    等价于

   Select e.ename,e.sal,d.dname from emp e left join dept d one.deptno=d.deptno;

2. 分页查询

   Rownum:被称作伪列,用于返回标识行数据的数字,

   Select rownum, empno,ename, sal from emp;

    不能从结果集中直接截取

   Select rownum,empno, ename,sal from emp where rownum >3;------查不到结果

    正确实例:

   Select * from (select rownum rn,e.* from emp e) where rn between 8 and10;---示3行结果

3. decode函数

   Decode(expr, search1, result1[,search2,result2…][,default]):用于比较参数expr的值,如

    果匹配到search条件,返回对应的result结果;没有提供default参数值,返回null;

    例:

   Select ename,job,sal decode(job, ‘manager’ ,sal*1.2,’analyst’,sal*1.1,sal) bonus from emp;

    等价于(有case语句)                                               from emp;γ

   Select ename,case job when ‘manager’ then sal*1.2,when ‘analyst’ thensal*1.1 else sal end

    分组中的应用

    场景:计算职位的人数,analyst/manager属于vip,其余是普通员工operation,无法使

    用group By简单实现。

   Select decode(job,’analyst’,’vip’,’manager’,’vip’,’operation’)job,count(1) job_cnt from emp

   Group by decode((job,’analyst’,’vip’,’manager’,’vip’,’operation’);

    场景:Dept表中按”operation”, ”accounting”, “sales”排序。

   Select deptno,dname,loc from dept order by decode(dname,”operation”,1,”accounting”,2 ,“sales”,3);

4.排序函数

   Row_number() over(partition by col1 order by col2):表示根据col1分组,在分组内根据col2排序。

    场景:按照部门编号分组显示,然后每组按照职员编号排序,并赋予组内编码。

   Select deptno,ename,empno,row_number() over(partition by deptno order byempno) as Emp_id from emp;

 

 

   Rank() over(partition by col1 order by col2):表示根据col1分组,在分组内给予等级标识。

    和row_number的区别是:row_number没有重复值,而rank有重复值,跳跃排序。

   Dense_rank() over(partition by col1 order by col2):连续排序

5.集合操作

 

   Union、union all:用来获取两个或两个以上结果集的并集。

   Union操作符会自动去掉合后的重复记录

   Union返回所有记录,包括重复行

   Union操作对查询结果排序,union all不排序;

   Intersect获取两个结果集的交集

   Minus获取两个结果集的差集,只有在第一个结果集中存在,而第二个不存在才显示

    例:

   Select ename, job, sal from emp

   Where job=’ manager’

   Minus

   Select ename, job, sal from emp

   Where sal>=2500;

6.高级分组函数

    Rollup、cube和groupingsets运算符是Group by子句的扩展,

 

 

 

 

    (此部分重点在于:多多练习书上例题)

 

 

十四、视图、序列、索引

1.视图

    视图也称作虚表,是一组数据的逻辑表示。作用:简化复杂查询;限制数据访问。

    授权创建视图:grantcreate view to tarena;

 

    创建简单视图

    例:

   Create view v_emp_10

   As

   Select empno,ename,sal,deptno from emp where deptno=10;

    查看视图:descv_emp_10

 

    对视图进行DML操作,会影响基表。

 

    创建具有withcheck option约束的视图,对可见范围内的视图修改,否则导致对视图进

    行DML操作失败

   Create or replace view v_emp_10

   As

   Select empno,ename,sal,deptno from emp where deptno=10;

   With check option;

   20部门不在视图内,所有相关20的DML操作都会失败

 

    创建具有with readonly 约束的视图,所有操作都会失败

 

    与视图相关的数据字典:user_objects、user_views、user_update_columns

    场景:查询user_objects中所有视图名称

   select object_name from user_objects where object_type=’view’;

    场景:查询指定视图

   Select text from user_views where view_name=’v_emp_10’;

    场景:数据字典中user_updatable_columns查询视图

   Select column_name,insertable,updatable,deletable from user_updatable_columns

   From user_updatable_columns

   Where table_name=’v_emp_10’;

 

    删除视图:dropview view_name;

2.序列

    序列是一种用来生成唯一数字值的数据库对象;

    使用序列

    场景:创建一个序列,起始数据是100,递增10;

   Create sequence emp_seq

    Start with 100

   Increment by 10;

 

    序列中有两个伪列

   Nextval:获取序列的下个值

   Currval:获取序列的当前值

    场景:插入新记录到emp 

   Select emp_seq.nextval from dual;

   Insert into emp(empno,ename)values(emp_seq.nextval, ‘donna’);

 

    删除序列

   Drop dequence emp_seq;

3.索引

    索引是一种允许直接访问数据表中某一行的树形结构。

    索引记录中存有 索引关键字和指向表中数据的指针(地址)

   Rowid:伪列,唯一标识一条记录,可理解为地址。

 

    创建索引

   Create [unique] index index_name on table(column[,column….]);

   Index_name表示索引名称

   Table表示表名

   Column表示列名,可以建立单列索引或符合索引

   Unique 表示唯一索引

    场景:创建emp表的ename上建立索引

   Create index idx_emp_ename on emp(ename);

    场景:创建多列索引

   Create index idx_emp_job_sal on emp(job,sal);

    场景:创建基于函数的索引

   Creat index emp_ename_upper_idx on emp(upper(ename));

    当做下面的查询自动应用索引

   Select * from emp where upper(ename)=’KING’;

 

    修改和删除索引

   Alter index index_name rebuild;

   Drop index index_name ;

 

十五、约束

1.约束

    非空约束:not null

    唯一约束:unique

    主键约束:primarykey

    外键约束:Foreignkey

    检查约束:check

   **以下实例多为省略建表时添加约束**

    非空约束:

    修改/添加非空约束(建表后)

   Alter table employees modify(列名 列类型 not null/null)

 

    添加唯一性约束

   Create tablle employees(

   Eid number(6) unique,

   Name varchar2(30),

   Email varchar2(50),

   Salary number(7,2),

   Hiredate date,

   Constraint employees_email_uk unique(email)

   );

    建表后增加

   Alter table empleyees add constraint employees_name_uk unique(name);

 

    添加主键约束(建表后添加)

   Alter table employees add constraint employees_eid_pk primary key(eid);

 

    添加外键约束(建表后添加)

   Alter table employees add constraint employees_deptno_fk foreignkey(deptno) references dept(deptno);

 

    添加检查约束(建表后添加)

   Alter table employees add constraint employees_salary_check check(salary>2000);

            
阅读更多
文章标签: 数据库
个人分类: Oracle
想对作者说点什么? 我来说一句

java基础整理笔记超详细

2017年07月17日 959KB 下载

没有更多推荐了,返回首页

不良信息举报

Oracle基础整理

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭