ORACLE数据库学习笔记1

Oracle数据库学习笔记，本文记录一些数据库常见的使用方法

1.SQL语言的特点

SQL全称是结构化查询语言，英文译作Structured Query Language，它是一种在关系型数据库中定义和操纵数据的标准语言。
SQL是一种非过程化语言，能让用户不用考虑诸如数据的存储格式和数据的存储路径等复杂问题就能按照自己的要求在高层数据结构上操作。SQL语句通常用于完成一些数据库的操作任务，具有增加、删除、修改、数据定义与控制等完整的数据库操作功能。在数据应用程序开发过程中，巧妙地使用SQL语句，可以简化编程，起到事半功倍的效果。通过SQL语句，程序员或数据库管理员可以进行如下的主要工作。

• 建立数据库的表格，包括设置表格可以使用的空间。
• 改变数据库系统环境设置。
• 针对某个数据库或表格，授予用户存取权限。
• 对数据库表格建立索引值。
• 修改数据库表格结构（新建、删除或修改表格字段）。
• 对数据库进行数据的新建。
• 对数据库进行数据的删除。
• 对数据库进行数据的修改。
• 对数据库进行数据的查询。

1.1 SQL语言的编写规则

SQL关键字不区分大小写，既可以使用大写格式，也可以使用小写格式，或者大小写格式混用。

select empno,ename,sal from scott.emp;
SELECT empno,ename,sal FROM scott.emp;
selECT empno,ename,sal From scott.emp;

分别执行这3条SELECT语句，会发现结果完全相同。对象名和列名不区分大小写，它们既可以使用大写格式，也可以使用小写格式，或者大小写格式混用。但是，字符值区分大小写。当在SQL语句中引用字符值时，必须给出正确的大小写数据，否则不能得到正确的查询结果。

select empno,ename,sal from scott.emp where job='SALESMAN';
SELECT empno,ename,sal FROM scott.emp job='salesman';

2.用户模式和模式对象

在Oracle数据库中，为了便于管理用户所创建的数据库对象（如数据表、索引、视图等），引入了模式的概念，这样某个用户所创建的数据库对象就都属于该用户模式。

2.1 模式与模式对象

模式是一个数据库对象的集合。模式为一个数据库用户所有，并且具有与该用户相同的名称，如system模式、scott模式等。在一个模式内部不可以直接访问其他模式的数据库对象，即使在具有访问权限的情况下，也需要指定模式名称才可以访问其他模式的数据库对象。模式对象是由用户创建的逻辑结构，用以存储或引用数据。例如，前面章节中所讲过的段（如表、索引等），以及用户所拥有的其他非段的数据库对象。这些非段的数据库对象通常包括约束、视图、同义词、过程以及程序包等。简而言之，模式与模式对象之间的关系就是拥有与被拥有的关系，即模式拥有模式对象；而模式对象被模式所拥有。

2.2 示例模式scott

Oracle提供的scott模式的目的，就是给用户提供一些示例表和数据来展示Oracle数据库的一些特性。scott模式拥有的模式对象（都是数据表）

在scott模式下，通过检索user_tables表来显示scott模式所拥有的4个数据表，代码如下。

SQL> connect scott/tiger
SQL> select table_name from user_tables;

3.检索数据的方法

用户对表或视图最常进行的操作就是检索数据，检索数据可以通过SELECT语句来实现，该语句由多个子句组成，通过这些子句可以完成筛选、投影和连接等各种数据操作，最终得到用户想要的查询结果。该语句的基本语法格式如下。

select {[DISTINCT |ALL] colums |*}
[INTO table_name]
FROM {tables | views| other select}
[WHERE conditions]
[GROUP BY columns]
[HAVING conditions]
[ORDER BY columns]

上面的语法中，共有7个子句，它们的功能分别如下。

• SELECT子句：用于选择数据表、视图中的列
• INTO子句：用于将原表的结构和数据插入新表中
• FROM子句：用于指定数据的来源，包括表、视图和其他SELECT语句
• WHERE子句：用于对检索的数据进行筛选
• GROUP BY子句：用于对检索结果进行分组显示
• HAVING子句：用于从使用GROUP BY子句分组后的查询结果中筛选数据行
• ORDER BY子句：用来对结果集进行排序(包括升序和降序)

3.1 简单查询

只包含SELECT子句和FROM子句的查询就是简单查询，SELECT子句和FROM子句是SELECT语句的必选项，即每个SELECT语句都必须包含这两个子句。其中，SELECT子句用于选择想要在查询结果中显示的列，对于这些要显示的列，即可以使用列名来表示，也可以使用星号（）来表示。在检索数据时，数据将按照SELECT子句后面指定的列名的顺序来显示；如果使用星号（），则表示检索所有的列，这时数据将按照表结构的自然顺序来显示。
检索所有列：如果要检索指定数据表的所有列，可以在SELECT子句后面使用星号（*）来实现。在检索一个数据表时，要注意该表所属的模式。如果在指定表所属的模式内部检索数据，则可以直接使用表名；如果不在指定表所属的模式内部检索数据，则不但要查看当前模式是否具有查询的权限，而且还要在表名前面加上其所属的模式名称。

select * from dept;

检索指定列

select column_name1, column_name2, column_name3,...,column_namen

在Oracle数据库中，有一个标识行中唯一特性的行标识符，该行标识符的名称为ROWID。行标识符ROWID是Oracle数据库内部使用的隐藏列，由于该列实际上并不是定义在表中，因此也称为伪列。伪列ROWID长度为18位字符，包含该行数据在Oracle数据库中的物理地址。用户使用DESCRIBE命令是无法查到ROWID列的，但是可以在SELECT语句中检索到该列。
查询日期列：日期列是指数据类型为DATE的列。查询日期列与查询其他列没有任何区别，但日期列的默认显示格式为DD-MON-RR。
(1)以简体中文显示日期结果。

SQL> alter session set nls_date_language = 'SIMPLIFIED CHINESE'；
SQL> select ename,hiredate from emp;

(2)以美国英语显示日期结果。

SQL> alter session set nls_date_language = 'AMERICAN'；
SQL> select ename,hiredate from emp;

(3)*以特定格式显示日期结果。
不同国家地区、不同民族、不同人员都具有不同的日期使用习惯，如果希望定制日期显示格式，并按照特定方式显示日期格式，那么可以设置会话的nls_date_format参数。

SQL> alter session set nls_date_language = 'YYYY''年''MM''月''DD''日'''；
SQL> select ename,hiredate from emp;

(4)使用TO_CHAR函数定时日期显示函数
除了可以使用参数nls_date_format设置日期显示格式外，也可以使用TO_CHAR函数将日期值转变为特定格式的字符串。

select to_char(sysdate,'YYYYMMDD') from dual;

带有表达式的SELECT语句：

select sal*(1+0.1), sal from emp;

为列指定别名：

select empno as "员工编号", ename as "员工名称",job as "职务"
from emp;

显示不重复记录:

select distinct job from emp;

处理NULL值:
NULL表示未知值，它既不是空格，也不是0。当插入数据时，如果没有为特定列提供数据，并且该列没有默认值，那么其结果为NULL。但是在实际应用程序中，NULL显示结果往往不能符合应用需求，在这种情况下需要使用函数NVL处理NULL，并将其转换为合理的显示结果。下面通过实例来说明不处理NULL的显示结果，以及处理NULL的具体方法。
使用NVL函数处理NULL

select ename, sal, comm, sal+nvl(comm,0) from emp;

当使用函数NVL(COMM,0)时，如果COMM存在数值，则返回其原有数值，如果COMM列为NULL，则函数返回0。
连接字符串：当执行查询操作时，为了显示更有意义的结果值，有时需要将多个字符串连接起来。连接字符串可以使用“||”操作符或者CONCAT函数。

select ename, ||''''||'s job is '|| job from emp;
select concat(concat(ename,'''s salary is '),sal) from emp;

3.2筛选查询

在SELECT语句中使用WHERE子句可以实现对数据行的筛选操作，只有满足WHERE子句中判断条件的行才会显示在结果集中，而那些不满足WHERE子句判断条件的行则不包括在结果集中。这种筛选操作是非常有意义的，通过筛选数据，可以从大量的数据中得到用户所需要的数据。在SELECT语句中，WHERE子句位于FROM子句之后，其语法格式如下。

select columns_list from table_name where conditional_expression
--columns_list：字段列表
--table_name：表名
--conditional_expression：条件表达式

使用特殊关键字筛选：
SQL语言提供了LIKE、IN、BETWEEN和IS NULL等关键字来筛选数据，这些关键字的功能分别是匹配字符串、查询目标值、限定值的范围和判断值是否为空等，下面将对这些关键字进行详细讲解。
(1)LIKE关键字。在WHERE子句中使用LIKE关键字查询数据的方式也称为字符串模式匹配或字符串模糊查询，LIKE关键字需要使用通配符在字符串内查找指定的模式，所以需要了解常用的通配符。通配符的英文原文为wildcard，该词的原意为扑克牌中的2或王，因为它们可以代替任何其他的牌，所以称为wildcard。LIKE关键字可以使用以下两个通配符。

• %：代表0个或多个字符。
• _：代表一个且只能是一个字符。
  例如，“K%”表示以字母K开头的任意长度的字符串，“%M%”表示包含字母M的任意长度的字符串，“_MRKJ”表示5个字符长度且后面4个字符是MRKJ的字符串。
  (2)IN 关键字
  当测试一个数据值是否匹配一组目标值中的一个时，通常使用IN关键字来指定列表搜索条件。IN关键字的格式是IN（目标值1，目标值2，目标值3，…），目标值的项目之间必须使用逗号分隔，并且括在括号中。

(3)BETWEEN关键字。
需要返回某一个数据值是否位于两个给定的值之间，可以使用范围条件进行检索。通常使用BETWEEN…AND和NOT…BETWEEN…AND来指定范围条件。使用BETWEEN…AND查询条件时，指定的第一个值必须小于第二个值。因为BETWEEN…AND实质是查询条件“大于等于第一个值，并且小于等于第二个值”的简写形式，即BETWEEN…AND要包括两端的值，等价于比较运算符（>=…<=）。

(4) IS NULL关键字。
空值（NULL）从技术上来说就是未知的、不确定的值，但空值与空字符串不同，因为空值是不存在的值，而空字符串是长度为0的字符串。因为空值代表的是未知的值，所以并不是所有的空值都相等。例如，student表中有两个学生的年龄未知，但无法证明这两个学生的年龄相等。这样就不能用“=”运算符来检测空值。所以SQL引入了一个IS NULL关键字来检测特殊值之间的等价性，并且IS NULL关键字通常在WHERE子句中使用。

3.3分组查询

---

数据分组的目的是用来汇总数据或为整个分组显示单行的汇总信息，通常在查询结果集中使用GROUP BY子句对记录进行分组。在SELECT语句中，GROUP BY子句位于FROM子句之后，其语法格式如下。

select columns_list
from table_name
[WHERE conditional_expression]
GROUP BY columns_list

GROUP BY 子句经常与聚合函数一起使用，使用GROUP BY子句和聚集函数，可以实现对查询结果中每一组进行分类统计，所以，在结果中每组数据都有一个与之对应的统计值。在ORACLE系统中，经常使用的统计函数为：

函数	说明
AVG	返回一个数字列或是计算列的平均值
COUNT	返回查询结果中的记录数
MAX	返回一个数字列或是计算列的最大值
MIN	返回一个数字列或是计算列的最小值
SUM	返回一个数字列或者计算列的总和

在使用GROUP BY子句时，要注意：
GROUP BY

• 在SELECT子句的后面只可以有两类表达式：统计函数和进行分组的列名。
• 在SELECT子句中的列名必须是进行分组的列，除此之外添加其他的列名都是错误的，但是GROUP BY子句后面的列名可以不出现在SELECT子句中。
• 在默认情况下，将按照GROUP BY子句指定的分组列升序排列，如果需要重新排序，可以使用ORDER BY子句指定新的排列顺序。GROUP BY子句中的列可以不在SELECT列表中。

使用HAVING子句限制分组结果
HAVING子句通常与GROUP BY子句一起使用，在完成对分组结果统计后，可以使用HAVING子句对分组的结果做进一步的筛选。如果不使用GROUP BY子句，HAVING子句的功能与WHERE子句一样。HAVING子句和WHERE子句的相似之处都是定义搜索条件。唯一不同的是HAVING子句中可以包含聚集函数，如常用的COUNT、AVG、SUM等；在WHERE子句中则不可以使用聚集函数。
如果在SELECT语句中使用了GROUP BY子句，那么HAVING子句将应用于GROUPBY子句创建的那些组。如果指定了WHERE子句，而没有指定GROUP BY子句，那么HAVING子句将应用于WHERE子句的输出，并且整个输出被看作一个组，如果在SELECT语句中既没有指定WHERE子句，也没有指定GROUP BY子句，那么HAVING子句将应用于FROM子句的输出，并且将其看作一个组。

在GROUP BY子句中使用ROLLUP和CUBE操作符
(1)默认情况下，当使用GROUP BY子句生成数据统计结果时，只会生成相关列的数据统计信息，而不会生成小计和总计统计。例如，当使用GROUP BY子句统计不同部门、不同岗位的平均工资时，会生成如下所示的统计结果。

在实际应用程序中，不仅需要获得以上统计结果，而且可能还需要取得横向、纵向小计统计以及总计统计，例如部门的平均工资、岗位的平均工资、所有雇员的平均工资等。为了取得更全面的数据统计，可以使用ROLLUP和CUBE操作符。
当使用ROLLUP操作符时，在保留原有统计结果的基础上，还会生成横向小计（部门平均工资）和总计（所有雇员平均工资）：

(2)当使用CUBE操作符时，在保留原有统计结果的基础上，还会生成横向小计（部门平均工资）、纵向小计岗位平均工资和总计（所有雇员平均工资），如下所示

(3)使用GROUPING函数
当使用ROLLUP或者CUBE操作符生成统计结果时，某个统计结果行可能用到一列或者多列，也可能没有使用任何列。为了确定统计结果是否使用了特定列，可以使用GROUPING函数。如果该函数返回0，则表示统计结果使用了该列；如果函数返回1，则表示统计结果没有使用该列。