Oracle笔记(一)

1.数据库的逻辑和物理结构

wps_clip_image-28078

  • 表空间由多个数据文件组成

  • 数据文件只能属于一个表空间

  • 表空间为逻辑概念，数据文件为物理概念

  • 段存在于表空间中

  • 段是区的集合

  • 区是数据块的集合

  • 数据块会被映射到磁盘块

2.用户登录

登录普通用户：

方式一：执行 sqlplus --> 输入用户名 --> 输入密码。

方式二：执行 sqlplus {用户名} --> 输入密码。

方式三：执行 sqlplus {用户名}/{密码}。

例子：

sqlplus --> 输入scott --> 输入tiger。

sqlplus scott --> 输入密码。

sqlplus scott/tiger。

登录管理员：

执行 sqlplus / as sysdba

退出：

exit

3.修改权限

¨ 解锁用户：

alter user 用户名 account unlock;

¨ 锁定用户：

alter user 用户名 account lock;

¨ 修改密码：

alter user 用户名 identified by 新密码;

¨ 修改管理员密码：

alter user sys identified by 新密码;

4.Oracle相关的服务

OracleService+服务名，该服务是数据库启动的基础，只有该服务启动了，Oracle数据库才能正常启动。这是必须启动的服务。

OracleOraDb10g_home1TNSListener，该服务是服务器端为客户端提供的监听服务，只有该服务在服务器上正常启动，客户端才能连接到服务器。该监听服务接收客户端发出的请求，然后将请求传递给数据库服务器。一旦建立了连接，客户端和数据库服务器就能直接通信了。

OracleOraDb10g_home1iSQL*Plus，该服务提供了用浏览器对数据库中数据操作的方式。该服务启动后，就可以使用浏览器进行远程登录并进行数据库操作了。

SQL可以分成以下几组：

  • DML（Data Manipulation Language） 数据操作语言，用于检索或者修改数据
  • DDL（Data Definition Language） 数据定义语言，用于定义数据的结构，创建、修改或者删除数据库对象
  • DCL（Data Control Language） 数据控制语言，用于定义数据库的权限

简单查询指的是查询出一张表中的所有的数据，简单查询的语法如下：

5.查询

一、简单查询

SELECT [DISTINCT] * | 字段 [别名] [,字段 [别名]]

FROM 表名称 [别名];

查询出来的内容中出现了重复的数据，而之所以数据会有重复，主要的原因是现在没有消除掉重复记录，可以使用DISTINCT消除掉所有的重复内容：

SELECT DISTINCT job FROM emp;

在简单查询中也可以使用“||”连接查询的字段。

范例：观察“||”的使用

SELECT empno || ',' || ename FROM emp;

二、限定查询

在之前的简单查询中，是将所有的记录进行显示，但是现在可以对显示的记录进行过滤的操作，而这就属于限定查询的工作了，限定查询就是在之前语法的基础上增加了一个WHERE子句，用于指定限定条件，此时语法如下：

SELECT [DISTINCT] * | 字段 [别名] [,字段 [别名]] FROM 表名称 [别名] [WHERE 条件(S)];

在WHERE子句之后可以增加多个条件，最常见的条件就是基本的关系运算：>、>=、<、<=、!=(<>)、BETWEEN、AND、LIKE、IN、IS NULL、AND、OR、NOT；

1、关系运算

范例：查询出所有职位是办事员的雇员信息

SELECT * FROM emp WHERE job='clerk';

这个时候没有返回相应的查询结果，主要原因是在Oracle数据库中，所有的数据都是区分大小写的，所以代码修改如下：

SELECT * FROM emp WHERE job='CLERK';

2、范围判断：BETWEEN…AND…

范例：要求查询出基本工资在1500~3000的雇员信息

SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;

范例：现在也可以对BETWEEN…AND…操作求反

SELECT * FROM emp WHERE NOT sal BETWEEN 1500 AND 3000;

“BETWEEN…AND…”操作符不光只是针对于数字有用，对于日期也同样有用。

3、判断是否为空：IS (NOT) NULL

使用此语法可以判断某一个字段上的内容是否是“null”，但是null和数字0以及空字符串是两个概念。

范例：查询出所有领取奖金的雇员信息

SELECT * FROM emp WHERE comm IS NOT NULL;SELECT * FROM emp WHERE NOT comm IS NULL;

范例：查询出所有不领取奖金的雇员

SELECT * FROM emp WHERE comm IS NULL;
4、指定范围的判断：IN操作符

5、模糊查询：LIKE子句

LIKE子句的功能是提供了模糊查找的操作，例如：某些程序上出现的搜索操作，都属于LIKE子句的实现，但是必须提醒的，搜索引擎上的查询可不是LIKE。

但是要想使用LIKE子句则必须认识两个匹配符号：

匹配单个字符：         _       ->  1个

匹配任意多个字符： %      ->  0个、1个、多个

优先级：

wps_clip_image-3049

空值（null）与对空值的处理

¨ 空值是无效的，未指定的，未知的或不可预知的值

¨ 空值不是空格或者0。

¨ 包含空值的数学表达式的值都为空值

处理字符串与日期

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

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

¨ 默认的日期格式是 DD-MON-RR。

三、数据的排序

SELECT [DISTINCT] * | 字段 [别名] [,字段 [别名]]

FROM 表名称 [别名]

[WHERE 条件(S)]

[ORDER BY 字段 [ASC|DESC] [,字段 [ASC|DESC],…]];

“ORDER BY”子句是写在所有的SQL语句最后的内容，而且对于排序有以下几点说明：

排序的时候可以指定多个排序的字段；

排序的方式有两种：

  • 升序（ASC）：默认，不写也是升序；
  • 降序（DESC）：用户需要指定，由大到小排序；

单行函数

wps_clip_image-4712

一、字符函数

字符函数的功能主要是进行字符串数据的操作，下面给出几个字符函数：

  • UPPER(字符串 | 列)：将输入的字符串变为大写返回；
  • LOWER(字符串 | 列)：将输入的字符串变为小写返回；
  • INITCAP(字符串 | 列)：开头首字母大写；
  • LENGTH(字符串 | 列)：求出字符串的长度；
  • REPLACE(字符串 | 列)：进行替换；
  • SUBSTR(字符串 | 列，开始点 [,结束点])：字符串截取；

wps_clip_image-5437

wps_clip_image-5319[4]

二、数字函数

  • ROUND(数字 | 列 [,保留小数的位数])：四舍五入的操作；
  • TRUNC(数字 | 列 [,保留小数的位数])：舍弃指定位置的内容；
  • MOD(数字 1，数字2)：取模，取余数；

三、日期函数

取得当前的日期，这个当前日期可以使用“SYSDATE”取得，代码如下：

SELECT SYSDATE FROM dual;

除了以上的当前日期之外，在日期中也可以进行若干计算：

  • 日期 + 数字 = 日期，表示若干天之后的日期；
SELECT SYSDATE + 3,SYSDATE + 300 FROM dual;
  • 日期 – 数字 = 日期，表示若干天前的日期；
SELECT SYSDATE - 3,SYSDATE - 300 FROM dual;
  • 日期 – 日期 = 数字，表示的是两个日期间的天数，但是肯定是大日期 – 小日期；

范例：求出每个雇员到今天为止的雇佣天数

SELECT ename,hiredate,SYSDATE-hiredate FROM emp;

而且很多的编程语言之中，也都会提出一种概念，日期可以通过数字表示出来。

除了以上的三个公式之外，也提供了如下的四个操作函数：

  • LAST_DAY(日期)：求出指定日期的最后一天；

范例：求出本月的最后一天日期

SELECT LAST_DAY(SYSDATE) FROM dual;
  • NEXT_DAY(日期，星期数)：求出下一个指定星期X的日期；

范例：求出下一个周一

SELECT NEXT_DAY(SYSDATE,'星期一') FROM dual;
  • ADD_MONTHS(日期，数字)：求出若干月之后的日期；

范例：求出四个月后的日期

SELECT ADD_MONTHS(SYSDATE,4) FROM dual;
  • MONTHS_BETWEEN(日期1，日期2)：求出两个日期之间所经历的月份；

范例：求出每个雇员到今天为止的雇佣月份

SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp;

在所有的开发之中，如果是日期的操作，建议使用以上的函数，因为这些函数可以避免闰年的问题。

四、转换函数

  • TO_CHAR(字符串 | 列，格式字符串)：将日期或者是数字变为字符串显示；
  • TO_DATE(字符串，格式字符串)：将字符串变为DATE数据显示；
  • TO_NUMBER(字符串)：将字符串变为数字显示；

wps_clip_image-7687

TO_CHAR()函数

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy') year, TO_CHAR(SYSDATE,'mm') month, TO_CHAR(SYSDATE,'dd') day FROM dual;
TO_CHAR(SY YEAR MO DA
---------- ---- -- --
2012-08-12 2012 08 12

但是这个时候的显示数据之中可以发现会存在前导0，如果要想消除掉这个0的话，可以加入一个“fm”。

SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd') day FROM dual;
DAY
----------
2012-8-12

正常人都加0，所以这个标记知道就行了，可是在Oracle之中，DATE里面是包含了时间的，但是之前的代码没有显示出时间，要想显示时间则需要增加标记：

SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss') day FROM dual;
DAY
-------------------
2012-8-12 16:13:38

一定要注意，使用TO_CHAR()函数之后，所有的内容都是字符串，不再是之前的DATE型数据，TO_CHAR()函数也可以用于数字的格式化上，这个时候每一个“9”表示一位数字的概念，而不是数字9的概念。

SELECT TO_CHAR(89078907890,'L999,999,999,999,999') FROM dual;
TO_CHAR(89078907890,'L999,999,
------------------------------
              ￥89,078,907,890

其中的字母“L”，表示的是“Local”的含义，即：当前的所在的语言环境下的货币符号。

wps_clip_image-7977

TO_DATE()函数

此函数的主要功能是将一个字符串变为DATE型数据。

SELECT TO_DATE('1989-09-12','yyyy-mm-dd') FROM dual;
TO_DATE('1989-
--------------
12-9月 -89

五、通用函数

通用函数主要有两个：NVL()、DECODE()

NVL()函数，处理null

NVL函数将空值转换成一个已知的值：

l 可以使用的数据类型有日期、字符、数字。

l 函数的一般形式:

  • NVL(commission_pct,0)
  • NVL(hire_date,'01-JAN-97')
  • NVL(job_id,'No Job Yet')

DECODE()函数：多数值判断

DECODE()函数非常类似于程序中的if…else…语句，唯一不同的是DECODE()函数判断的是数值，而不是逻辑条件。

例如，现在要求显示全部雇员的职位，但是这些职位要求替换为中文显示：

  • CLERK：办事员；
  • SALESMAN：销售；
  • MANAGER：经理；
  • ANALYST：分析员；
  • PRESIDENT：总裁；

这种判断肯定是逐行进行判断，所以这个时候就必须采用DECODE(),而此函数的语法如下：

DECODE(数值 | 列 ，判断值1，显示值1，判断值2，显示值2，判断值3，显示值3，…)

范例：实现显示的操作功能

SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') FROM emp;
 
 

多表查询

一、多表查询的基本概念

SELECT [DISTINCT] * | 字段 [别名] [,字段 [别名] ,…]

FROM 表名称 [别名], [表名称 [别名] ,…]

[WHERE 条件(S)]

[ORDER BY 排序字段 [ASC|DESC] [,排序字段 [ASC|DESC] ,…]];

笛卡尔集：学数学的都懂。。

二、连接

Oracle 连接:

Ø Equijoin：等值连接

Ø Non-equijoin：不等值连接

Ø Outer join：外连接

Ø Self join：自连接

SQL: 1999

Ø Cross joins

Ø Natural joins

Ø Using clause

Ø Full or two sided outer joins

1. 等值连接

wps_clip_image-11951

wps_clip_image-11991

使用表的别名

l 使用别名可以简化查询。

l 使用表名前缀可以提高执行效率。

2.连接多个表

l 连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。

3. 非等值连接

wps_clip_image-12608

wps_clip_image-12637

4.外连接(左、右连接)

wps_clip_image-12918

wps_clip_image-8470

  • (+)=：放在了等号的左边，表示的是右连接；
  • =(+)：放在了等号的右边，表示的是左连接；

但是不用去刻意的区分是左还是右，只是根据查询结果而定，如果发现有些需要的数据没有显示出来，就使用此符号更改连接方向。

SQL:1999语法

除了以上的表连接操作之外，在SQL语法之中，也提供了另外一套用于表连接的操作SQL，格式如下：

SELECT table1.column,table2.column FROM table1 [CROSS JOIN table2]| [NATURAL JOIN table2]| [JOIN table2 USING(column_name)]| [JOIN table2 ON(table1.column_name=table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name)];

以上实际上是属于多个语法的联合，下面分块说明语法的使用。

1、交叉连接（CROSS JOIN）：用于产生笛卡尔积

SELECT * FROM emp CROSS JOIN dept;

笛卡尔积本身并不是属于无用的内容，在某些情况下还是需要使用的。

2、自然连接（NATURAL JOIN）：自动找到匹配的关联字段，消除掉笛卡尔积

SELECT * FROM emp NATURAL JOIN dept;

但是并不是所有的字段都是关联字段，设置关联字段需要通过约束指定；

3、JOIN…USING子句：用户自己指定一个消除笛卡尔积的关联字段

SELECT * FROM emp JOIN dept USING(deptno);

4、JOIN…ON子句：用户自己指定一个可以消除笛卡尔积的关联条件

SELECT * FROM emp JOIN dept ON(emp.deptno=dept.deptno);

5、连接方向的改变：

  • 左（外）连接：LEFT OUTER JOIN…ON;
  • 右（外）连接：RIGHT OUTER JOIN…ON;
  • 全（外）连接：FULL OUTER JOIN…ON; --> 把两张表中没有的数据都显示
SELECT * FROM emp RIGHT OUTER JOIN dept ON(emp.deptno=dept.deptno);
左外联接

wps_clip_image-13783

右外联接

wps_clip_image-13937

满外联接

wps_clip_image-19637

在Oracle之外的数据库都使用以上的SQL:1999语法操作，所以这个语法还必须会一些（如果你一直使用的都是Oracle就可以不会了）。

再次强调：多表查询的性能肯定不高，而且性能一定要在大数据量的情况下才能够发现。

四、统计函数及分组查询

1、统计函数

在之前学习过一个COUNT()函数，此函数的功能可以统计出表中的数据量，实际上这个就是一个统计函数，而常用的统计函数有如下几个：

  • COUNT()：查询表中的数据记录；
  • AVG()：求出平均值；
  • SUM()：求和；
  • MAX()：求出最大值；
  • MIN()：求出最小值；

范例：测试COUNT()、AVG()、SUM()

统计出公司的所有雇员，每个月支付的平均工资及总工资。

SELECT MAX(sal),MIN(sal) FROM emp;

注意点：关于COUNT()函数

COUNT()函数的主要功能是进行数据的统计，但是在进行数据统计的时候，如果一张表中没有统计记录，COUNT()也会返回数据，只是这个数据是“0”。

SELECT COUNT(ename) FROM BONUS;

如果使用的是其他函数，则有可能返回null，但是COUNT()永远都会返回一个具体的数字，这一点以后在开发之中都会使用到。

2、分组查询

SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数

FROM 表名称 [别名], [表名称 [别名] ,…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2 ,…]]

[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]];

但是现在一旦分组之后，实际上对于语法上就会出现了新的限制，对于分组有以下要求：

  • 分组函数可以在没有分组的时候单独用使用，可是却不能出现其他的查询字段；

分组函数单独使用：

SELECT COUNT(empno) FROM emp;

错误的使用，出现了其他字段：

SELECT empno,COUNT(empno) FROM emp;

  • 如果现在要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他的字段不能出现：

正确做法：

SELECT job,COUNT(empno),AVG(sal)

FROM emp

GROUP BY job;

错误的做法：

SELECT deptno,job,COUNT(empno),AVG(sal)

FROM emp

GROUP BY job;

  • 分组函数允许嵌套，但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段。

此时如果要对分组后的数据再次进行过滤，则使用HAVING子句完成，那么此时的SQL语法格式如下：

SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数

FROM 表名称 [别名], [表名称 [别名] ,…]

[WHERE 条件(s)]

[GROUP BY 分组字段1 [,分组字段2 ,…]]

[HAVING 分组后的过滤条件（可以使用统计函数）]

[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]];

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno) mycount,NVL(AVG(e.sal),0) myavg
FROM dept d,emp e
WHERE d.deptno=e.deptno(+)
GROUP BY d.deptno,d.dname,d.loc
HAVING AVG(sal)>2000;

注意点：WHERE和HAVING的区别

  • WHERE：是在执行GROUP BY操作之前进行的过滤，表示从全部数据之中筛选出部分的数据，在WHERE之中不能使用统计函数；
  • HAVING：是在GROUP BY分组之后的再次过滤，可以在HAVING子句中使用统计函数；

五、子查询

子查询 = 简单查询 + 限定查询 + 多表查询 + 统计查询的综合体；

在之前强调过多表查询不建议大家使用，因为性能很差，但是多表查询最有利的替代者就是子查询，所以子查询在实际的开发之中使用的相当的多；

所谓的子查询指的就是在一个查询之中嵌套了其他的若干查询，嵌套子查询之后的查询SQL语句如下：

SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数 ,(

      SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数

      FROM 表名称 [别名], [表名称 [别名] ,…]

      [WHERE 条件(s)]

      [GROUP BY 分组字段1 [,分组字段2 ,…]]

      [HAVING 分组后的过滤条件（可以使用统计函数）]

      [ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]])

FROM 表名称 [别名], [表名称 [别名] ,…] ,(

      SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数

      FROM 表名称 [别名], [表名称 [别名] ,…]

      [WHERE 条件(s)]

      [GROUP BY 分组字段1 [,分组字段2 ,…]]

      [HAVING 分组后的过滤条件（可以使用统计函数）]

      [ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]])

[WHERE 条件(s) (

      SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数

      FROM 表名称 [别名], [表名称 [别名] ,…]

      [WHERE 条件(s)]

      [GROUP BY 分组字段1 [,分组字段2 ,…]]

      [HAVING 分组后的过滤条件（可以使用统计函数）]

      [ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]])]

[GROUP BY 分组字段1 [,分组字段2 ,…]]

[HAVING 分组后的过滤条件（可以使用统计函数）]

[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]];

理论上子查询可以出现在查询语句的任意位置上，但是从个人而言，子查询出现在WHERE和FROM子句之中较多；

以下的使用特点为个人总结，不是官方声明的：

  • WHERE：子查询一般只返回单行列、多行单列、单行多列的数据；
  • FROM：子查询返回的一般是多行的数据，当作一张临时表出现。

如果现在的子查询返回的是多行单列数据的话，这个时候就需要使用三种判断符判断了：IN、ANY、ALL；

1、 IN操作符：用于指定一个子查询的判断范围

这个操作符的使用实际上与之前讲解的IN是一样的，唯一不同的是，里面的范围由子查询指定了。

SELECT * FROM emp
WHERE sal in (
SELECT sal
FROM emp
WHERE job='MANAGER');

但是在使用IN的时候还要注意NOT IN的问题，如果使用NOT IN操作，在子查询之中，如果有一个内容是null，则不会查询出任何的结果。

2、 ANY操作符：与每一个内容想匹配，有三种匹配形式

  • =ANY：功能与IN操作符是完全一样的；
SELECT * FROM emp
WHERE sal=ANY (
SELECT sal
FROM emp
WHERE job='MANAGER');
  • >ANY：比子查询中返回记录最小的还要大的数据；
SELECT * FROM emp
WHERE sal>ANY (
SELECT sal
FROM emp
WHERE job='MANAGER');
  • <ANY：比子查询中返回记录的最大的还要小；

3、 ALL操作符：与每一个内容相匹配，有两种匹配形式：

  • >ALL：比子查询中返回的最大的记录还要大
SELECT * FROM emp
WHERE sal>ALL (
SELECT sal
FROM emp
WHERE job='MANAGER');
  • <ALL：比子查询中返回的最小的记录还要小
SELECT * FROM emp
WHERE sal<ALL (
SELECT sal
FROM emp
WHERE job='MANAGER');

以上的所有子查询都是在WHERE子句中出现的，那么下面再来观察在FROM子句中出现的查询，这个子查询一般返回的是多行多列的数据，当作一张临时表的方式来处理。

使用子查询的确要比使用多表查询更加节省性能，所以在开发之中子查询出现是最多的，而且在给出一个不成文的规定：大部分情况下，如果最终的查询结果之中需要出现SELECT子句，但是又不能直接使用统计函数的时候，就在子查询中统计信息，即：有复杂统计的地方大部分都需要子查询。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/zzzt/p/3687440.html