Oracle学习笔记(1)

1.Oracle数据库简介

1.1数据库(DB)概述

数据库（DB）是一个按数据结构来存储和管理数据的计算机软件系统。

1.2数据库管理系统与数据库应用系统

1. 数据库管理系统（Database Management System）

数据库管理系统（DBMS）是专门用于管理数据库的计算机系统软件。数据库管理系统能够为数据库提供数据的定义、建立、维护、查询和统计等操作功能，并完成对数据完整性、安全性进行控制的功能。(数据定义功能DDL/提供对数据库数据进行操作DML/数据备份，恢复/数据访问控制DCL)

 

2.  数据库应用系统（DatabaseApplication System）

数据库应用系统(DAS)是使用数据库技术的系统；数据库应用系统有很多，基本上所有的信息系统都是数据库应用系统。它通常由软件、数据库和数据管理员组成。

 

3. 数据库管理员(DBA)

数据管理员(DBA)负责创建、监控和维护整个数据库，使数据能被任何有权使用的人有效使用。数据库管理员一般是由业务水平较高，资历较深的人员担任。

 

4. 数据库系统

一个数据库系统应由计算机硬件、数据库(DB)、数据库管理系统(DBMS)、数据库应用系统(DAS)和数据库管理员(DBA)五部分构成。

1.3数据库管理系统的功能结构

1. 数据定义功能

能够提供数据定义语言（DataDescription Language，简称DDL）和相应的建库机制。用户利用DDL可以方便地建立数据库。

 

2. 数据操纵功能

实现数据的插入、修改、删除、查询、统计等数据存取操作的功能称为数据操纵功能。数据操纵功能是数据库的基本操作功能，数据库管理系统通过提供数据操纵语言（Data Manipulation language，简称DML）实现其数据操纵功能。

 

3. 数据库的建立和维护功能

指数据的载入、转储、重组织功能及数据库的恢复功能;指数据库结构的修改、变更及扩充功能。(DCL)

 

4. 数据库的运行管理功能

包括并发控制、数据的存取控制、数据完整性条件的检查和执行、数据库内部的维护等。

1.4 Oracle数据库的特点

1)支持大数据量、多用户的高性能的事务处理

2)Oracle遵守数据存取语言、操作系统、用户接口和网络通

3)信协议的工业标准

4)实施安全性控制和完整性控制

5)支持分布式数据库和分布处理

6)具有可移植性、可兼容性和可连接性

7)全球化、跨平台的数据

1.5 Oracle 11g服务

G是Grid的缩写，网格计算。

OracleServiceORCL为orcl数据库的对应服务程序（必须启动）

OracleOraDb11g_home1TNSListener网络监听器，提供访问连接Oracle实例使用（必须启动）

1.6 Oracle 服务器

Oracle 服务器是一个数据管理系统(RDBMS)，它提供开放的, 全面的, 近乎完整的信息管理。

由一个 Oracle 实例和一个 Oracle 数据库组成。

1.7 Oracle数据库和Oracle 实例

1）Oracle 服务器由两大部分组成, Oracle 数据库和 Oracle实例。

 

2）Oracle 数据库: 位于硬盘上实际存放数据的文件,这些文件组织在一起, 成为一个逻辑整体, 即为 Oracle 数据库. 因此在 Oracle 看来, “数据库” 是指硬盘上文件的逻辑集合, 必须要与内存里实例合作, 才能对外提供数据管理服务。

 

3）Oracle 实例: 位于物理内存里的数据结构. 它由一个共享的内存池和多个后台进程所组成, 共享的内存池可以被所有进程访问. 用户如果要存取数据库(也就是硬盘上的文件) 里的数据, 必须通过实例才能实现, 不能直接读取硬盘上的文件。

 

4）区别: 实例可以操作数据库; 在任何时刻一个实例只能与一个数据库关联; 大多数情况下, 一个数据库上只有一个实例对其进行操作。（可以有多个实例）

2.使用Oracle 11g数据库

2.1连接Oracle

1)连接方式

廋客户端方式，客户只需要装一个驱动.Oracle的jdbc驱动jar。

jdbc:oracle:thin:@localhost:1521:orcl

胖客户端,通过oracle的客户机连接，除了jar包还要装oracle客户端，性能高。

jdbc:oracle:oci:@localhost:1521:orcl 

注意：Orcale 11g默认情况下通过网络来连接访问是有问题的(10g 不会)，11g的可以修改监听的主机(在listener.ora文件中修改，找到(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))将，HOST=localhost的localhost改为自己的IP地址)

文件路径：xxxx11g\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora

 

2)连接oracle常用的工具：

sqlplus--基于字符命令行的专门用来连接Oracle的客户端程序。

 

sqlplusw--有专门容器的sqlplus客户端。

 

oraclesql developer---图形化的工具，免费，oracle提供用来连接数据库客户端软件,通过jdbc去连接数据库。(实际各以连接各种数据库)

 

Oracle客户机OEM---(默认Oracle在服务器上装一个web版本)oracle公司开的，连接Oracle客户端程序。图开化管理Oracle数据库工具。

 

pl/sqldeveloper---第三方公开开发，专用来开始oracle程序客户端工具。

2.2 SQL*PLUS的基本功能

A、sqlplus命令(Oralce提供维护数据库的命名)，可以简写的。命名可以缩写，比如describe scott.emp缩写成(四个字母)DESC

scott.emp不能换行，回车表示执行。

B、SQL语句：标准或者oracle所支持sql语句。不能缩写。==>可以换行，使用";"表示结束。当有多行时，在最后一行单独输入/来执行命令。

基本特点：

1、Oracle独有的运行环境

2、有自己的一套Sqlplus命令

3、在各个平台都可以运行

4、Sqlplus命令与sql语句不一样

5、提供了标准SQL扩充命令的支持

2.3连接语句

在dos中：

sqlplus system/admin  连接本机上的oracle，使用帐号及密码

sqlplus / as sysdba  以管理员身份连接本机上的oracle(操作系统的用户验证来连接)

sqlplus system/admin@192.168.10.28:1521/orcl连接其它机器上的oracle

sqlplus /nolog 不登录只是进入sqlplus环境

在sqlplus中：

connect system/admin

conn / as sysdba

2.4解锁用户并修改密码

alter user scott account unlock;--解锁用户

alter user scott identified by tiger;修改密码

 

2.5常用命令

show user 显示当前连接的用户

desc emp 查看表结构

help index 查询所有sqlplus命名帮助

help xx 查询xx命令的帮助

select * from tab 查询当前用户所有的表

set linesize 100 设置一行显示的字符数

set pagesize 30 设置每页显示多少行数据

col sal for 9999 设置sal(数字)这一列显示的列宽

col ename for a6 指定字符型列宽长度

host cls 执行操作系统的cls命令,简写 ho cls(不用离开sqlpus环境执行操作系统命令)

 

spool记录在sqlplus中操作过程

spool c:\test.txt  开始记录(会清空以前内容）

spo c:\test.txt

..执行各处操作

spool off 保存记录

 

注释

单行注释:--  

--注释内容

 

多行注释:/*注释注释内容*/

 

使用edit(ED)可以用来修改最后一次输入sql的命令。

EDIT(ED)

修改保存以后，按/来执行。

3.使用简单的SQL语句

3.1 SQL语句简介

格式：select 列名from 表名

SQL 语言大小写不敏感。

SQL 可以写在一行或者多行。

关键字不能被缩写也不能分行。

各子句一般要分行写。

使用缩进提高语句的可读性。

给列区别时可以使用双引号“”，特别是别名有空格时。

select ename 名字, job "工 作" from emp;

3.2运算符

1）算术运算符

sql语句支持+,_,*,/运算符。

注意：任何数值与NULL(空值)运算时结果为NULL。

空值是无效的，未指定的，未知的或不可预知的值，空值不是空格或者0。

2）比较运算符

>、<、=、<>、!=、>=、<=

字符的比较，是按ASCII码来比较，而且是从第一位开始。不管字符的长度。

 

3）其它比较运算符

A、between，可以用在种数据类型上。

x between ..v1. and ..v2.(包含边界值)

x>=v1 and x<=v2

 

B、in及not in

notin查询时，注意集合中不能有null元素。

 

C、使用like进行模糊查询

字符串查询中可以使用"%"代表任意多个任意字符."_"代表任意1个字符.

如果查询的内容包括%及_,就需要进行转义

select * from emp where ename like '%\_%' escape'\';

 

D、空值NULL查询

 

使用is及is not

is null/is not null

 

E、逻辑运算符

NOT AND OR 注意使用括号来明确表示计算的顺序。

3.3字符串连接

在MySQL中，连接字符串使用concat函数,格式：concat(‘xxx’,’yyy’),在Oracle中也可以使用该函数。

select concat(ename, job) from emp;

Oralce特有的字符串连接方式是使用||，格式：’xxx’||’yyy’

select 'xxx'||'yyy' from dual;

注意：在sql语句中，别名用双引号“”引起，字符串常量用单引号‘’引起。日期类型也要用单引号‘’'来表示。

如果常量中要表示一个逗号‘,需要使用两个两个逗号‘即可.

SQL> select ‘‘’’from dual;(最外层的引号两个都是说明里面的内容是字符串常量，实际输出的是蓝色的引号)

''

--

'

3.4去重(distinct)

去除查询结果中重复的行，使用DISTINCT关键字.

DISTINCT作用于多列数据,如：

select DISTINCT deptno,job from EMP(只有当deptno和job联合起来一样才算是重复行，单独的depton列或job列可以有重复)

3.5虚表(dual)

Oracle提供了一个名为dual的虚表，该表只有一行一列，没有任何内容。主要是为了方便查询一些常用的数据或者进行一些简单的运算等。如：

查询当前系统时间：

select sysdate fromdual;

计算3+3的值：

select 3+3 from dual;

3.6过滤和排序数据

1）滤过

基本格式；SELECT *from 表名  WHERE 过滤条件(包含列名，包含其它表达式，关系运算符，逻辑运算符)

字符和日期要包含在单引号中。

字符大小写敏感，日期格式敏感。

select * from emp where sal > 2000;

select * from emp where ename = 'SCOTT';(若SOCTT改为小写则找不到符合条件的记录)

select * from emp where hiredate > '02-4月-81';

2）排序

Oracle默认将查询出来的结果按升序(ASC)排序，排序使用orderby 子句。

格式：select 列名 from 表名 order by 列名 [ASC,DESC]。

注意：order by 子句一定是在查询语句的最后面。

如果要按某列降序排序，要单独指定，如order by desc sal（按工资降序排序）。

如果有多个字段参与没有又没有指定排序的方式，则全部默认升序排序，可以单独指定某个字段的排序方式。如：

select * from emp order by sal, deptnodesc;（先按工资升序排序，工资一样的再按部门号降序排序）。

⊙对于有空值的字段情况.使用nulls LAST或者nulls First来指定空值行所排的位置。

select * from emp order by comm desc nulls last;

3.7单行函数

函数：包含单行函数和多行函数。可以没有参数，但必须有返回值。

单行函数：对于每一行数据都产生一个结果。

多行函数：对于多行数据只产生一个结果。

1）字符函数

A大小写转换

select lower('Hello world') 转小写,upper('Hello world') 转大写,initcap('Hello world') 首字母大

写 from dual;

B字符控制函数

CONCAT 连接字符串

select concat('xx','yy') form dual;

 

SUBSTR截取字符串

select substr('hello', 2) from dual;//ello,从第二个字符开始截取,截到最后，索引从1开始

select substr('hello world',2,6) from dual;//ellow,从第二个字符开始截取,截取6个字符

LENGTH 求长度

select length('hello world') from dual;//11

 

LENGTHB 求字节数

select lengthb('hello world你好') from dual; //17,中文用utf-8编码 11+6=17

 

INSTR查找子串

select instr('hello world','l') from dual;//3

select instr('hello world','a') from dual;//0

 

LPAD左填充

select lpad('hello',8) from dual; //在hello左边填充3个空格使其长度为8

select lpad('hello',8,'*') from dual;//***hello

 

RPAD右填充

select rpad('hello',8) from dual; //在hello右边填充3个空格使其长度为8

select lpad('hello',8,'*') from dual;/ /hello***

TRIM去除左右字符

select trim(' hello ') from dual; //hello 去除hello两边的空格，不管有多少个

select trim('h' from 'helloworldh') from dual;//ello world 去除'hello worldh两边的h字符，不管有多少个

 

REPLACE 替换

select replace('hello world','l','b') fromdual;//用b来替换l

2）数字函数

ROUND 四舍五入，过半则进1

select round(45.926) from dual;//46

select round(45.926,2) fromdual;//45.93保留两位小数

select round(45.926,1) from dual;//45.9 保留以为小数

select round(45.926,0) from dual;//46 保留到个位

select round(45.926,-1) from dual;//50 保留到十位

select round(45.926,-2) from dual;//0 保留到百位

TRUNC截断

直接去掉不要的数值，不会向前进位。

select trunc(45.926) from dual;//46

select trunc(45.926,2) from dual;//45.92

select trunc(45.926,1) from dual;//45.9

select trunc(45.926,0) from dual;//45

select trunc(45.926,-1) from dual;//40

select trunc(45.926,-2) from dual;//0

MODE求余数

select mod(900,350) from dual;// 余数为200

3）日期型函数

MONTHS_BETWEEN 两个日期相隔几个月

ADD_MONTHS 加若干月

NEXT_DAY 下一个星期几是几号

LAST_DAY 获得当前月所在的最后一天

ROUND  日期四余五入

TRUNC 截断

select add_months(sysdate,15) "15个月后",next_day(sysdate,'星期五') 下一个星期五,last_day(sysdate) 本月最后一天 from dual；

 

4）数据类型转换函数

to_char() 把数字或者日期转换成字符串

select to_char(sysdate,'YYYY-MM-DDHH24:MI:SS') from dual;

select to_char(15644.555,'0099999.99') fromdual;

to_number() 把字符转换成数字

 

to_date() 把字符转换成日期

select to_date('2012-01-09','yyyy-mm-dd')from dual;

 

5）滤空函数

NVL (expr1, expr2) 如果expr1为空，则取expr1，否则取expr2

NVL2 (expr1, expr2, expr3) exp1不为空则返回expr2,否则expr3

NULLIF (expr1, expr2) 如果两个相等则返回null,返回返回expr1

COALESCE(xx,yy) 找到第一个不为空的值。

select ename,sal 月薪,sal*12+comm 年收入 from emp;（有问题，数值和null运算结果为null）

select ename,sal 月薪,sal*12+nvl(comm,0) 年收入,sal*12+nvl2(comm,comm,0)，sal*12+coalesce(comm,0) from emp;

 

6)条件表达式

条件表达式，用来在sql语句中表示if then else.

A.    CASE 表达式--SQL99

格式：

CASE expr WHEN comparison_expr1 THENreturn_expr1

        [WHENcomparison_expr2 THEN return_expr2

         WHENcomparison_exprn THEN return_exprn

         ELSEelse_expr]

END

例子：

select ename,job,sal,sal+sal*(casejob when 'MANAGER' then 0.15

 2  when 'SALESMAN' then 0.1

 3  else 0.08

  4  end)from emp;

 

B.decode函数

格式：

DECODE(col|expression, search1,result1

                          [, search2, result2,...,]

                          [, default])

例子：

1 select ename,job,sal,

 2  sal+sal*decode(job,'MANAGER',0.15,'SALESMAN',0.1,0.08)

  3  * from emp