通过Oracle数据库，学SQL语句。

最新推荐文章于 2023-07-28 09:17:32 发布

csdn_SirLiu

最新推荐文章于 2023-07-28 09:17:32 发布

阅读量3.1k

点赞数 4

分类专栏： SQL语句—数据库

本文链接：https://blog.csdn.net/CSDN_SamLiu/article/details/78092771

版权

SQL语句—数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Top

1. 数据库原理

1.1. 数据库简介

1.1.1. 文件存储

对数据的存储需求一直存在。保存数据的方式，经历了手工管理、文件管理等阶段，直至数据库管理阶段。

文件存储方式保存数据的弊端：

缺乏对数据的整体管理,数据不便修改；
不利于数据分析和共享;
数据量急剧增长,大量数据不可能长期保存在文件中。

数据库应运而生，是人们存放数据、访问数据、操作数据的存储仓库。

1.1.2. DB和DBMS

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

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

数据库建立了数据之间的联系，使用结构化的方式组织和处理数据，能够统一、集中及独立的管理数据，使数据的存取独立于使用数据的程序，实现了数据共享。

数据库的典型特征包括：数据的结构化，数据间的共享，减少数据的冗余度，以及数据的独立性。

数据库成为数据的知识仓库，并对这些数据的存储、访问、安全、数据一致性、并发操作及备份恢复负责。

图-1 DB和DBMS

1.1.3. 关系数据库简介

关系是一个数学概念，描述两个元素间的关联或对应关系。所以关系型数据库，即是使用关系模型把数据组织到数据表(Table)中。现实世界可以用数据来描述。

主流数据库产品：

Oracle（Oracle）
DB2（IBM）
SQL Server（MS）
MySQL（Oracle）

1.1.4. 表的概念

在关系数据库中，数据被存放于二维数据表(Table)中。

一个关系数据库由多个数据表组成，数据表是关系数据库的基本存储结构，由行和列组成，行（Row）也就是横排数据，也经常被称作记录(Record)，列（Column）就是纵列数据，也被称作字段(Field)。表和表之间是存在关联关系的。

1.2. 主流关系型数据库

1.2.1. Oracle数据库概述

Oracle是当今著名的Oracle(甲骨文)公司的数据库产品，它是世界上第一个商品化的关系型数据库管理系统，也是第一个推出和数据库结合的第四代语言开发工具的数据库产品。

Oracle采用标准的SQL结构化查询语言，支持多种数据类型，提供面向对象的数据支持，具有第四代语言开发工具，支持UNIX、WINDOWS、OS/2等多种平台。Oracle公司的软件产品丰富，包括Oracle服务器产品，Oracle开发工具和Oracle应用软件。其中最著名的就是Oracle数据库，目前最新的版本是Oracle 12c。

1.2.2. DB2数据库概述

DB2是IBM的关系型数据库管理系统，DB2有很多不同的版本，可以运行在从掌上产品到大型机不同的终端机器上。DB2 Universal Database Personal Edition和DB2 Universal Database Workgroup Edition分别是为OS/2和Windows系统的单用户和多用户提供的数据库管理系统。

DB2在高端数据库的主要竞争对手是Oracle。

1.2.3. Sybase数据库

Sybase是美国Sybase公司研制的一种关系型数据库系统，是较早采用C/S技术的数据库厂商，是一种典型的UNIX或Windows NT平台上客户机/服务器环境下的大型数据库系统。 Sybase通常与Sybase SQL Anywhere用于客户机/服务器环境，前者作为服务器数据库，后者为客户机数据库，采用该公司研制的PowerBuilder为开发工具，在国内大中型系统中具有广泛的应用。

SYBASE主要有三种版本，一是UNIX操作系统下运行的版本，二是Novell Netware环境下运行的版本，三是Windows NT环境下运行的版本。对UNIX操作系统目前广泛应用的为SYBASE 10 及SYABSE 11 for SCO UNIX。

2010年Sybase被SAP收购。

1.2.4. SQL Server数据库概述

Microsoft SQL Server是运行在Windows NT服务器上，支持C/S结构的数据库管理系统。它采用标准SQL语言，微软公司对它进行了部分扩充而成为事务SQL（Transact-SQL）。

SQL Server最早是微软为了要和IBM竞争时，与Sybase合作所产生的，其最早的发展者是Sybase，和Sybase数据库完全兼容。在与Sybase终止合作关系后，微软自主开发出SQL Server 6.0版，往后的SQL Server即均由微软自行研发。最新的版本是SQL Server 2012，上一版本是2008。

Microsoft SQL Server几个初始版本适用于中小企业的数据库管理，但是后来它的应用范围有所扩展，已经触及到大型、跨国企业的数据库管理。

1.2.5. MySQL数据库概述

MySQL是一个开放源码的小型关系型数据库管理系统，开发者为瑞典MySQL AB公司。目前MySQL被广泛地应用在Internet上的中小型网站中。

与其它的大型数据库例如Oracle、IBM DB2等相比，MySQL自有它的不足之处，如规模小、功能有限等，但对于一般个人使用者和中小型企业来说，MySQL提供的功能已经绰绰有余，而且由于MySQL是开放源码软件，因此可以大大降低总体拥有成本，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

2008年1月16日，Sun正式收购MySQL。2009年4月20日，SUN被Oracle公司收购。目前的最新版本是MySQL5.6.

1.3. SQL概述

1.3.1. 结构化查询语言

SQL(Structured Query Language) 是结构化查询语言的缩写。

SQL是在关系数据库上执行数据操作、检索及维护所使用的标准语言,可以用来查询数据，操纵数据，定义数据，控制数据，所有数据库都使用相同或者相似的语言。

SQL可分为:

数据定义语言（DDL） : Data Definition Language
数据操纵语言（DML） : Data Manipulation Language
事务控制语言（TCL）：Transaction Control Language)
数据查询语言（DQL）：Data Query Language
数据控制语言（DCL） : Data Control Language

执行SQL语句时，用户只需要知道其逻辑含义，而不需要知道SQL语句的具体执行步骤。

1.3.2. 数据定义语言（DDL）

用于建立、修改、删除数据库对象，包括创建语句(CREATE)、修改语句(ALTER)、删除语句(DROP)，比如使用CREATE TABLE创建表，使用ALTER TABLE修改表，使用DROPTABLE删除表等动作。这类语言不需要事务的参与，自动提交。

1.3.3. 数据操作语言（DML）

用于改变数据库数据，包括INSERT、UPDATE、DELETE三条语句。其中，INSERT语句用于将数据插入到数据库中，UPDATE语句用于更新数据库中已存在的数据，DELETE用于删除数据库中已存在的数据。DML语言和事务是相关的，执行完DML操作后必须经过事务控制语句提交后才真正的将改变应用到数据库中。

1.3.4. 事务控制语言（TCL）

用来维护数据一致性的语句，包括提交(COMMIT)、回滚(ROLLBACK)、保存点(SAVEPOINT)三条语句，其中COMMIT用来确认已经进行的数据库改变， ROLLBACK语句用来取消已经进行的数据库改变，当执行DML操作后(也就是上面说的增加、修改、删除等动作)，可以使用COMMIT语句来确认这种改变，或者使用ROLLBACK取消这种改变。SAVEPOINT语句用来设置保存点，使当前的事务可以回退到指定的保存点，便于取消部分改变。

1.3.5. 数据查询语言（DQL）

用来查询所需要的数据。使用最广泛，语法灵活复杂。

1.3.6. 数据控制语言（DCL）

用于执行权限的授予和收回操作、创建用户等，包括授予(GRANT)语句，收回(REVOKE)语句，CREATE USER语句，其中GRANT用于给用户或角色授予权限， REVOKE用于收回用户或角色已有的权限。DCL语句也不需要事务的参与，是自动提交的。

1.4. Oracle数据库安装和访问

1.4.1. Oracle数据库安装（Windows）

打开Oracle首页http://www.oracle.com/，通过Downloads进入Oracle数据库的下载页面。

安装过程（以本地安装为例）：

步骤1：解压，双击setup.exe，启动安装向导；

图-2 Oracle数据库的安装页面

在此步骤的设置如下：

选择基本安装，指定要安装的Oracle数据库的基位置和主目录位置；
安装类型为企业版；
创建启动数据库（在安装数据库软件同时创建数据库，也可以在安装完毕后单独创建，此处选择默认值同时创建）；
指定全局数据库名称为orcl，以及口令，需要记住自己的密码，这里设置为oracle。

然后点击“下一步”按钮，

步骤2：进入检查安装环境的步骤，用来检查软硬件环境是否符合Oracle11g的安装要求，如图-3。如果满足安装要求，点击“下一步”按钮。

图-3 Oracle数据库的安装环境检查

步骤3：检查通过后，进入安装概要页面，如图-4。在这步可以检查之前的设置，包括安装类型和要安装的组件，如果有问题，点击“上一步”按钮回去修改，如果确认没问题，点击“安装”按钮，进入安装阶段。

图-4 Oracle数据库的安装概要

步骤4：提示正在安装。根据机器的软硬件配置不同，这个步骤可能耗费不同的时间。图略。

步骤5：进入配置助手步骤，这个过程不需要用户干涉。图略。

步骤6：创建数据库，图略。

步骤7：数据库创建完成后，提示配置助手界面，显示数据库的初始信息。图略。

在此步骤中可以点击“口令管理”按钮进行数据库初始用户的口令管理，这个步骤此时可以忽略，待安装完成后再进行口令管理。完成后，点击“确定”按钮离开口令管理界面。

步骤8：弹出安装成功窗口，图略。

步骤9：在安装成功界面上点击“下一步”按钮，提示安装结束。如图-5。

图-5 Oracle数据库安装结束

1.4.2. Oracle数据库安装（Linux）

在Oracle官网下载所需的软件包，检查软硬件环境是否符合要求，规划空间和目录结构，安装数据库并创建配置。

安装过程略。

1.4.3. 远程登录数据库服务器

Oracle数据库作为市场以及技术上领先的数据库产品，提供了丰富和简单易用的数据库开发和管理工具。有两种主要工具，一是命令行工具，Oracle自带的SQL*PLUS，二是Oracle提供的图形界面工具：SQL Developer。

这些工具可能和数据库在同一台服务器上，也可能安装在另一台机器中。

图-6 数据库和客户端

1.4.4. 使用SQL*PLUS

SQL*Plus是Oracle数据库提供的和数据库进行交互的工具，是被系统管理员和开发人员广泛使用的开发和管理工具，功能强大，使用简单，可以运行在任何Oracle运行的平台上，默认和数据库一起安装。SQL*Plus是一个基于传统的C/S结构的SQL开发工具，客户端和服务器端可以在同一台主机或不同主机上。

在图-6中，我们主要使用最上面那种访问方式。

首先，远程登录到数据库所在的机器上，输入远程机器操作系统的帐号和密码：

图-7 远程登录数据库服务器

然后在%提示符下，输入SQLPlus命令，并输入数据库的帐号和密码：

图-8 运行sqlplus，登录数据库

如果出现SQL提示符，则表示登录成功：

图-9登录数据库成功

退出时，在SQL>后输入exit即可：

图-10退出SQL*Plus

再输入exit，即退出远程服务器。

1.4.5. 使用Oracle SQL Developer

Oracle SQL Developer是Oracle官方出品的免费图形化开发工具，相对SQL*Plus来说，图形化的界面便于操作，不必记忆大量的命令，输出结果美观。它的基本功能包括结果的格式化输出，编辑器自动提示，代码美化，显示SQL的执行计划，监控会话，编写以及调试存储过程等。官方和免费两个特征让这个工具极具吸引力。

SQL Developer工具的主界面，如图-11所示。

图-11SQL Developer打开后的界面

第一次进入SQL Developer，需要设置连接参数。首先在窗口左边的Connection上按右键，开启一个新的连接，输入远程数据库的参数，界面如图-12。

图-12在SQL Developer中设置连接用户信息

成功建立了连接之后，在连接上按右键点击Connect，将打开对应用户方案中的数据库对象。如图-13。

图-13在SQL Developer中打开用户scott

在这个界面中，即可操作SQL语句了。

2. SQL（DDL、DML）

2.1. Oracle数据类型

2.1.1. NUMBER

NUMBER表示数字类型，经常被定义成NUMBER（P，S）形式，其中：

P表示数字的总位数
S表示小数点后面的位数

例如在表Emp中的Sal列的定义如下：


    
    Sal  NUMBER(6,2)

表示Sal列中的数据，整数位最大为4位，小数位最大位数是2位，也就是最大取值：9999.99。

2.1.2. CHAR

CHAR表示固定长度的字符类型，经常被定义成CHAR（N）形式, N表示占用的字节数，N的最大取值是2000。

例如在表Emp中的Ename列的定义如下：


    
        Ename CHAR(20)；

表示Ename列中最多可存储20个字节的字符串，并且占用的空间是固定的20个字节。

2.1.3. VARCHAR2

VARCHAR2表示变长的字符类型，定义格式是VARCHAR2（N）, N表示最多可占用的字节数，最大长度是4000字节。

例如在表Emp中的JOB列的定义如下：


    
        JOB VARCHAR2(100)；

表示JOB列中最多可存储长度为100个字节的字符串。根据其中保存的数据长度，占用的空间是变化的，最大占用空间为100个字节。

2.1.4. DATE

DATE用于定义日期时间的数据，长度是7个字节，默认格式是：DD-MON-RR, 例如：“11-APR-71”。如果是中文环境，是“11-4月-71”这种形式。

例如在表Emp中的Hiredate列的定义如下：


    
        Hiredate DATE；

表示Hiredate列中存放的是日期数据。

2.2. 创建表

2.2.1. CREATE语句

创建表的语法是：


    
    CREATE TABLE [schema.]table_name(
    column_name datatype[DEFAULT expr][,…]
);

创建职员表的例子：


    
    --创建表emp 
CREATE TABLE emp( 
    id NUMBER(10), 
    name VARCHAR2(20), 
    gender CHAR(1), 
    birth DATE,
    salary NUMBER(6,2),
    job VARCHAR2(30),
    deptid NUMBER(2) 
);

2.2.2. DESC语句

DESC是查看表的数据结构，语法是：


    
    DESC table_name;

查看emp表的结构，将会得到的结果如图-14所示：

图-14查看数据表的结构

2.2.3. DEFAULT

可以通过DEFAULT子句给列指定默认值，例如，在emp表中给gender列赋默认值‘M’,如果插入数据时没有指定性别的员工，则默认是男性。

创建表emp1：


    
    CREATE TABLE emp1(
    id NUMBER(10),
    name VARCHAR2(20),
    gender CHAR(1) DEFAULT ‘M’,
    birth DATE,
    salary NUMBER(6,2),
    job VARCHAR2(30),
    deptid NUMBER(2)
);

2.2.4. NOT NULL

默认情况下，任何列都允许有空值。非空(Not Null)是一种约束条件，用于确保字段值不为空，当某个字段被设置了非空约束条件，这个字段中必须存在有效值，也就是说，当执行插入数据的操作时，必须提供这个列的数据，当执行更新操作时，不能给这个列的值设置为NULL。

创建表emp2，设置name字段为非空，也即是表emp2中的每条记录，name列都必须有值。


    
    CREATE TABLE emp2( 
    id NUMBER(10), 
    name VARCHAR2(20) NOT NULL, 
    gender CHAR(1), 
    birth DATE,
    salary NUMBER(6,2),
    job VARCHAR2(30),
    deptid NUMBER(2) 
);

2.3. 修改表

2.3.1. 修改表名

在建表后如果希望修改表名，可以使用RENAME语句实现，语法如下，将改变表名old_name为new_name：


    
    RENAME old_name TO new_name;

举例说明，如果要将表名emp修改为employee，使用如下语法：


    
    RENAME emp TO employee;

2.3.2. 增加列

在建表之后，要给表增加列可以使用ALTER TABLE的ADD子句实现。语法如下：


    
        ALTER TABLE table_name ADD
     (column datatype [DEFAULT expr] [, column datatype…])

注意一点，列只能增加在最后，不能插入到现有的列中。下例给表employee增加一列hiredate，并设置默认值为当前日期。


    
    ALTER TABLE employee ADD (hiredate DATE DEFAULT sysdate);

2.3.3. 删除列

在建表之后，使用ALTER TABLE的DROP子句删除不需要的列。语法如下：


    
        ALTER TABLE table_name DROP (column);

删除字段需要从每行中删掉该字段占据的长度和数据，并释放在数据块中占据的空间，如果表记录比较大，删除字段可能需要比较长的时间。

下例删除表employee的列hiredate：


    
    ALTER TABLE employee DROP (hiredate);

2.3.4. 修改列

建表之后，可以改变表中列的数据类型、长度和默认值，注意这种修改仅对以后插入的数据有效，另外如果表中已经有数据的情况下，把长度由大改小，有可能不成功，比如原来的类型是VARCHAR2(100),其中已经存放了100个字节长度的数据，如果要改为VARCHAR2（80），则不会修改成功。

语法如下：


    
        ALTER TABLE table_name MODIFY
    (column datatype [DEFAULT expr] [, column datatype…])

下例修改表employee的列job，并增加默认值的设置:


    
    ALTER TABLE employee
MODIFY(job VARCHAR2(40) DEFAULT 'CLERK' );

2.4. DML语句

在做后续练习之前，先熟悉本部分使用的样例表emp和dept的结构和数据：


    
    DROP TABLE dept;
CREATE TABLE dept
       (deptno NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    dname VARCHAR2(14) ,
    loc VARCHAR2(13) ) ;
DROP TABLE emp;
CREATE TABLE emp
       (empno NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ename VARCHAR2(10),
    job VARCHAR2(15),
    mgr NUMBER(4),
    hiredate DATE,
    sal NUMBER(7,2),
    comm NUMBER(7,2),
    deptno NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO dept VALUES
    (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES 
   (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES
    (30,'SALES','CHICAGO');
INSERT INTO dept VALUES
    (40,'OPERATIONS','BOSTON');
 SELECT * FROM dept;
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,to_date('19-4-87','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,to_date('23-5-87','dd-mm-yyyy'),1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

2.4.1. INSERT语句

INSERT语句用来给数据表增加记录，每次增加一条记录。语法如下：


    
        INSERT INTO table_name[(column[, column…])] 
    VALUES(value[, value…]);

需要注意的一点，所有的DML操作，都需要再执行事务提交语句commit，才算真正确认了此操作。

向employee表插入一条记录的例子如下：


    
    INSERT INTO employee(id, name, job, salary) 
VALUES(1001, 'rose', 'PROGRAMMER', 5500);

Oracle中的日期数据比较特殊，如果插入的列有日期字段，需要考虑日期的格式。Oracle的默认的日期格式‘DD-MON-RR，你或者按照默认格式插入数据，或者自定义日期格式，用TO_DATE函数转换为日期类型的数据。


    
    --使用默认日期格式插入记录
INSERT INTO employee(id, name, job,birth) 
VALUES(1002, 'martha', 'ANALYST', '01-9月-89');
--使用自定义日期格式插入记录
INSERT INTO employee(id, name, job, birth) 
VALUES(1003, 'donna', 'MANAGER', 
TO_DATE('1978-09-01', 'YYYY-MM-DD'));

2.4.2. UPDATE语句

UPDATE语句用来更新表中的记录，语法如下：


    
        UPDATE table_name 
    SET column = value [, column = value]…
    [WHERE condition];

其中WHERE子句是可选的，但是如果没有WHERE子句，则全表的数据都会被更新，务必小心。

下例更改职员ROSE的薪水为8500:


    
    UPDATE employee SET salary = 8500 WHERE name = 'ROSE';

2.4.3. DELETE语句

DELETE语句用来删除表中的记录，语法如下：


    
        DELETE [FROM] table_name [WHERE condition];

和UPDATE语句一样，WHERE子句是可选的，但是如果没有WHERE子句，则全表的数据都会被删除，务必确认后再予以删除。

下例将删除职位是空的员工记录:


    
     DELETE FROM employee WHERE job is null;

Top

1. Oracle字符串操作

1.1. 字符串类型

1.1.1. CHAR和VARCHAR2类型

CHAR和VARCHAR2类型都是用来表示字符串数据类型，用来在表中存放字符串信息, 比如姓名、职业、地址等。

CHAR存放定长字符，如果数据存不满定长长度，则补齐空格；

VARCHAR2存放变长字符，实际数据有多少长度则占用多少。

如保存字符串’HELLOWORLD’，共10个英文字母：

CHAR(100)： 10个字母，补齐90个空格，实际占用100个字节。
VARCHAR2(100) ：10个字母，实际占用10个字节。

CHAR类型浪费空间换取查询时间的缩短，VARCHAR2节省空间查询时间较CHAR类型要长。字符串按照自然顺序排序。

1.1.2. CHAR和VARCHAR2的存储编码

字符串在数据库中存储的默认单位是字节，也可显式指定为字符。如：

CHAR(10)，等价于 CHAR(10 BYTE)
如果指定单位为字符：CHAR(10 CHAR)，20个字节
VARCHAR2(10)，等价于VARCHAR2（10 BYTE）
指定单位为字符：VARCHAR2(10 CHAR)，20个字节

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

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

1.1.3. CHAR和VARCHAR2的最大长度

CHAR类型的最大取值为2000字节，也就是定义为CHAR（2000）。其中最多保存2000个英文字符，1000个汉字（GBK）。

VARCHAR2最大取值为4000字节，也就是VARCHAR2（4000），最多保存4000个英文字符，2000个汉字（GBK）。

CHAR如果不指定长度，默认为1个字节， VARCHAR2必须指定长度。

1.1.4. LONG和CLOB类型

LONG类型可以认为是VARCHAR2的加长版，用来存储变长字符串，最多达2GB的字符串数据，但是LONG类型有诸多限制，所以不建议使用：

每个表只能有一个LONG类型列；
不能作为主键；
不能建立索引；
不能出现在查询条件中等

CLOB用来存储定长或变长字符串，最多达4GB的字符串数据，ORACLE建议开发中使用CLOB替代LONG类型，比如如下方式定义数据表：


    
        CREATE TABLE student(
id NUMBER(4),
name CHAR(20),
detail CLOB);

1.2. 字符串函数

1.2.1. CONCAT和“||”

CONCAT是字符串连接函数，语法是：


    
    CONCAT(char1, char2)

用于返回两个字符串连接后的结果，两个参数char1、char2是要连接的两个字符串。concat只能有两个参数，所以如果连接三个字符串时，需要两个concat函数。比如连接emp表中的name列和salary列，中间用“：”隔开：


    
    SELECT CONCAT(CONCAT(name, ' : '), sal)FROM emp;

图-1 CONCAT连接字符串的结果

在连接两个以上操作符时并不是很方便。concat的等价操作是连接操作符”||”。当多个字串连接时，用||符号更直观。下述SQL语句实现相同的效果：


    
       SELECTename || ' : ' || sal FROMemp;

在连接时，如果任何一个参数是NULL，相当于连接了一个空格。

1.2.2. LENGTH

LENGTH(char)用于返回参数字符串的长度。如果字符类型是VARCHAR2，返回字符的实际长度，如果字符类型是CHAR，长度还包括后补的空格。例如：


    
    SELECTename, LENGTH(name) FROM emp;

将列出name和name的字符串长度：

图-2Lenth函数的运行结果

1.2.3. UPPER、LOWER和INITCAP

这三个函数全部是英文的大小写转换函数，用来转换字符的大小写：

UPPER(char)用于将字符转换为大写形式
LOWER(char)用于将字符转换为小写形式
INITCAP(char)用于将字符串中每个单词的首字符大写，其它字符小写，单词之间用空格和非字母字符分隔

如果这三个函数的输入参数是NULL值，仍然返回NULL值。例如：


    
        SELECT UPPER('hello world'), LOWER('HELLO WORLD'), INITCAP('hello world') 
    FROM DUAL;

将列出参数“hello world”的大写、小写和首字符大写的形式。一般用来查询数据表中不确定大小写的情况。查询结果如图-3所示。

图-3大小写转换函数的运行结果

1.2.4. TRIM、LTRIM、RTRIM

这三个TRIM函数的作用都是截去子字符串。语法形式及解释：

TRIM(c2 FROM c1) 表示从c1的前后截去c2
LTRIM(c1[, c2]) 表示从c1的左边（Left）截去c2
RTRIM(c1[, c2]) 表示从c1的右边（Right）截去c2

在后两个函数中，如果没有参数c2，就去除空格。例如：


    
     SELECT TRIM('e' from 'elite') AS t1,
LTRIM('elite', 'e') AS t2,
RTRIM('elite', 'e') AS t3 
FROM DUAL;

其中最常用的是TRIM，经常用来去掉字符串前后的空格。运行结果如图-4所示。

图-4TRIM函数的运行结果

1.2.5. LPAD、RPAD

PAD意即补丁，LPAD和RPAD两个函数都叫做补位函数，LPAD表示LEFT PAD，在左边打补丁，RPAD表示RIGHT PAD，在右边打补丁。语法如下：

LPAD(char1, n, char2) 左补位函数
RPAD(char1, n, char2) 右补位函数

参数的含义：在字符串参数char1的左端或右端用char2补足到n位，其中参数char2可重复多次。例如在EMP表中使用左补位，将sal用$补齐6位，运行结果如图-5所示。

图-5补位函数的运行结果

1.2.6. SUBSTR

SUBSTR表示在一个字符串中截取子串，语法是：


    
    SUBSTR(char, [m[, n]])

用于返回char中从m位开始取n个字符的子串，字符串的首位计数从1开始。参数含义如下：

如果m = 0，则从首字符开始，如果m取负数，则从尾部开始
如果没有设置n，或者n的长度超过了char的长度，则取到字符串末尾为止

例如：


    
       SELECT 
SUBSTR('Doctor Who travels in TARDIS', 8, 25)
   FROM DUAL;

图-6取子串函数的运行结果

1.2.7. INSTR

用来返回在一个字符串中子串的位置。语法是：


    
    INSTR(char1, char2[, n [, m]])

参数的含义：

返回子串char2在源字符串char1中的位置
从n的位置开始搜索，没有指定n，从第1个字符开始搜索
m用于指定子串的第m次出现次数，如果不指定取值1
如果在char1中没有找到子串char2 ，返回0

例如：


    
        SELECT INSTR('Doctor Who', 'Who') words FROM DUAL;

结果将返回8.

2. Oracle数值操作

2.1. 数值类型

2.1.1. NUMBER（p）表示整数

数据表中的数值类型用NUMBER表示，完整语法是：


    
    NUMBER(precision ，scale)

可以用来表示整数和浮点数。如果没有设置参数s，则默认取值0，即NUMBER(p)用来表示整数。P表示数字的总位数，取值为1-38。一般用来在表中存放如编码、年龄、次数等用整数记录的数据。例如建表时指定学生编码是4位数字：


    
    CREATE TABLE student (
id NUMBER(4), 
name CHAR(20));

2.1.2. NUMBER（P,S）表示浮点数

如果NUMBER(precision ，scale)的两个参数全部显式定义，则表示浮点数：

precision：NUMBER可以存储的最大数字长度（不包括左右两边的0）
scale：在小数点右边的最大数字长度（包括左侧0）

如果指定了s但是没有指定p，则p默认为38，例如：


    
    列名 NUMBER（*，S）

NUMBER（p,s）经常用来做表中存放金额、成绩等有小数位的数据。例如创建学生表，指定成绩整数位最多3位，小数位最多2位：


    
    CREATE TABLE student (
id NUMBER(4), 
name CHAR(20),
score NUMBER(5, 2));

NUMBER的变种数据类型：内部实现是NUMBER，可以将其理解为NUMBER的别名，目的是多种数据库及编程语言兼容

NUMERIC(p,s)：完全映射至NUMBER(p,s)
DECIMAL(p,s)或DEC(p,s)：完全映射至NUMBER(p,s)
INTEGER或INT：完全映射至NUMBER(38)类型
SMALLINT：完全映射至NUMBER(38)类型
FLOAT(b)：映射至NUMBER类型
DOUBLE PRECISION：映射至NUMBER类型
REAL：映射至NUMBER类型

2.2. 数值函数

2.2.1. ROUND

数值函数指参数是数值类型的函数。常用的有ROUND、TRUNC、MOD、CEIL和FLOOR。其中ROUND用来四舍五入，语法如下：

ROUND(n[, m])用于将参数n按照m的数字要求四舍五入。其中：

参数中的n可以是任何数字，指要被处理的数字
m必须是整数
m取正数则四舍五入到小数点后第m位
m取0值则四舍五入到整数位
m取负数，则四舍五入到小数点前m位
m缺省，默认值是0

例如：


    
    SELECT ROUND(45.678, 2) FROM DUAL; --45.68
SELECT ROUND(45.678, 0) FROM DUAL;--46
SELECT ROUND(45.678, -1) FROM DUAL;--50

2.2.2. trunc

TRUNC(n[, m])的功能是截取，其中n和m的定义和ROUND(n[, m])相同，不同的是功能上按照截取的方式处理数字n。例如：


    
    SELECT TRUNC(45.678, 2) FROM DUAL; --45.67
SELECT TRUNC(45.678, 0) FROM DUAL;--45
SELECT TRUNC(45.678, -1) FROM DUAL;--40

2.2.3. MOD

MOD(m, n)是取模函数，返回m除以n后的余数，如果n为0则直接返回m。例如：


    
    --薪水值按1000取余数
SELECTename, sal, MOD(sal, 1000) FROM emp;

图-7薪水列按1000取模后的运行结果

2.2.4. CEIL和FLOOR

CEIL(n)、FLOOR(n)这两个函数顾名思义，一个是天花板，就是取大于或等于n的最小整数值，一个是地板，就是取小于或等于n的最大整数值。比如数字n = 4.5，那么它的CEIL是5.0，它的FLOOR是4.0。在SQL语句中的例子如下：


    
    SELECT CEIL(45.678) FROM DUAL; --46
SELECT FLOOR(45.678) FROM DUAL;--45

3. Oracle日期操作

3.1. 日期类型

3.1.1. DATE

DATE和TIMESTAMP是ORACLE中最常用的日期类型。DATE用来保存日期和时间。表示范围从是公元前4712年1月1日至公元9999年12月31日。

DATE类型在数据库中的实际存储固定为7个字节，格式分别为：

第1字节：世纪+100
第2字节：年
第3字节：月
第4字节：天
第5字节：小时+1
第6字节：分+1
第7字节：秒+1

3.1.2. TIMESTAMP

TIMESTAMP表示时间戳，与DATE的区别是不仅可以保存日期和时间，还能保存小数秒，可指定为0-9位，默认6位，最高精度可以到ns(纳秒)级别。

数据库内部用7或者11个字节存储，精度为0时，用7字节存储，与DATE功能相同，精度大于0则用11字节存储。格式为：

第1字节-第7字节：和DATE相同
第8-11字节：纳秒，采用4个字节存储，内部运算类型为整型

用日期类型建表的例子：


    
        CREATE TABLE test(
c1 DATE,
c2 TIMESTAMP(9));

3.2. 日期关键字

3.2.1. SYSDATE

SYSDATE本质是一个Oracle的内部函数，用来返回当前的系统时间，精确到秒，默认显示格式是DD-MON-RR，只有年月日并不显示时间。例如：


    
    SELECT SYSDATE FROM DUAL;

如果数据库是英文环境，将显示“03-MAY-14”的形式，如果是中文环境，将显示“03-5月-14”的形式。如果想显示时分秒，需要将格式转换一下：


    
    SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd day hh24:mi:ss') FROM DUAL;

运行结果是：

图-8显示当前的系统时间

在建表时，可以将系统时间SYSDATE作为某一列的默认值，当插入新的记录，将会取当时的系统时间，作为数据表的一列数据保存起来。例如学生表，学生的注册时间列即默认取值数据记录插入的时间：


    
        CREATE TABLE student (id NUMBER(4),
name CHAR(20),
registerDate DATE DEFAULT SYSDATE);

3.2.2. SYSTIMESTAMP

SYSTIMESTAMP也是Oracle的内部日期函数，返回当前系统日期和时间，精确到毫秒。例如：


    
    SELECT SYSTIMESTAMP FROM DUAL;
SELECT TO_CHAR(SYSTIMESTAMP,'SSSS.FF') FROM DUAL;

3.3. 日期转换函数

3.3.1. TO_DATE

日期数据有时需要和字符串数据相互转换，需要用到日期转换函数，包括TO_CHAR和TO_DATE。

TO_DATE的功能是将字符串按照定制格式转换为日期类型，语法格式是：


    
    TO_DATE(char[, fmt[, nlsparams]])

其中：char是要转换的字符串，fmt是转换格式，nlsparams是指定日期语言。其中比较重要的是格式，常用的日期格式如下：

表-1 常用日期格式

例子：查询2002年以后入职的员工：


    
      SELECTename, hiredate
  FROM emp
  WHERE hiredate>
 TO_DATE(‘2002-01-01',
  'YYYY-MM-DD');

3.3.2. TO_CHAR

TO_CHAR的作用是将其它类型（日期，数值）的数据转换为字符类型，主要应用在日期类型上。语法格式：


    
    TO_CHAR(date[, fmt[, nlsparams]])

其中fmt是格式，将日期类型数据date按照fmt格式输出字符串，nlsparams用于指定日期语言。例如：


    
      SELECTename,TO_CHAR(hiredate, 'YYYY"年"MM"月"DD"日"') 
  FROM emp;

3.4. 日期常用函数

3.4.1. LAST_DAY

LAST_DAY(date)：返回日期date所在月的最后一天，一般是在按照自然月计算某些业务逻辑，或者安排月末周期性活动时很有用处。例子：


    
    SELECT LAST_DAY(SYSDATE) FROM DUAL;--查询当月的最后一天
SELECT LAST_DAY('20-2月-09') FROM DUAL;--查询09年2月的最后一天

3.4.2. ADD_MONTHS

ADD_MONTHS(date, i)：返回日期date加上i个月后的日期值.

其中：

参数i可以是任何数字，大部分时候取正值整数
如果i是小数，将会被截取整数后再参与运算
如果i是负数，则获得的是减去i个月后的日期值

例如计算职员入职20周年纪念日：


    
        SELECTename, ADD_MONTHS(hiredate, 20 * 12) "20周年" 
    FROM emp;

3.4.3. MONTHS_BETWEEN

MONTHS_BETWEEN(date1, date2)：计算date1和date2两个日期值之间间隔了多少个月，实际运算是date1-date2，如果date2时间比date1晚，会得到负值。

除非两个日期间隔是整数月，否则会得到带小数位的结果，比如计算2009年9月1日到2009年10月10日之间间隔多少个月，会得到1.29个月。例如计算职员入职多少个月：


    
        SELECTename, MONTHS_BETWEEN(SYSDATE, hiredate) hiredate FROM emp；

3.4.4. NEXT_DAY

NEXT_DAY(date, char)：返回date日期数据的下一个周几，周几是由参数char来决定的。在中文环境下，直接使用”星期三”这种形式，英文环境下，需要使用”WEDNESDAY”这种英文的周几。为避免麻烦，可以直接用数字1-7表示周日-周六。

需要注意的是NEXT_DAY不要按字面意思理解为明天。查询下个周三是几号：


    
    SELECT NEXT_DAY(SYSDATE, 4) next_wedn FROM DUAL;

3.4.5. LEAST、GREATEST

比较函数LEAST和GREATEST语法如下：


    
    GREATEST(expr1[, expr2[, expr3]]…)
LEAST(expr1[, expr2[, expr3]]…)

两个函数都可以有多个参数值，但参数类型必须一致，返回结果是参数列表中最大或最小的值。

在比较之前，在参数列表中第二个以后的参数会被隐含的转换为第一个参数的数据类型，所以如果可以转换，则继续比较，如果不能转换将会报错。


    
    SELECT LEAST(SYSDATE, '10-10月 -08') FROM DUAL;

3.4.6. EXTRACT

EXTRACT直译是抽取或提取：

EXTRACT(date FROM datetime)：从参数datetime中提取参数date指定的数据，比如提取年、月、日。例如取出当前日期的年：


    
    SELECT EXTRACT(YEAR FROM SYSDATE) current_year FROM DUAL;

取出指定时间的小时：


    
    SELECT EXTRACT(HOUR FROM TIMESTAMP '2008-10-10 10:10:10') 
FROM DUAL;

4. 空值操作

4.1. NULL的含义

NULL是数据库里的重要概念，即空值。当表中的某些字段值，数据未知或暂时不存在，取值NULL。

Java中的简单数据类型是不能取值NULL的，在数据库中，任何数据类型均可取值NULL。

4.2. NULL操作

4.2.1. 插入NULL值

在数据表中插入记录时，如果要插入NULL值，可以用显式指定NULL值的方式，或者不插入某个字段值，即隐式表示NULL值。例如表student中：


    
    CREATE TABLE student(id NUMBER(4), name CHAR(20), gender CHAR(1));
INSERT INTO student VALUES(1000, '李莫愁', 'F');
INSERT INTO student VALUES(1001, '林平之', NULL);--显式插入NULL值
INSERT INTO student(id, name) VALUES(1002, '张无忌');--隐式插入NULL值

4.2.2. 更新成NULL值

把数据表的某个字段更新为NULL值，和更新为其他数据的语法是相同的。比如更新


    
    UPDATE student SET gender = NULL; --全表都被更新

注意这种更新只有在此列没有非空约束的情况下才可操作。如果gender列有非空约束，则无法更新为NULL值，上述语句会报错。

4.2.3. NULL条件查询

在条件查询中，因为NULL不等于任何值，所以不能用“列名=NULL”这种形式查询。必须用“列名 IS NULL”来判断，或者用“列名 IS NOT NULL”来查询非空数据。


    
    SELECT * FROM student WHERE gender IS NULL;

4.2.4. 非空约束

非空(NOT NULL)约束是约束条件的一种，用于确保数据表中某个字段值不为空。

因为在默认情况下，任何数据类型的列都允许有空值，但系统的业务逻辑可能会要求某些列不能取空值。这时需要在建表时指定该列不允许为空。

一旦某个字段被设置了非空约束条件，这个字段中必须存在有效值。即：当执行插入数据的操作时，必须提供这个列的数据，当执行更新操作时，不能给这个列的值设置为NULL。


    
    --创建数据表student，其中gender列不允许为空
CREATE TABLE student
    (id NUMBER(4),
name CHAR(20), 
gender CHAR(1) NOT NULL);

4.3. 空值函数

4.3.1. NVL

NVL(expr1, expr2)：将NULL转变为非NULL值。如果expr1为NULL，则取值expr2, expr2是非空值。

其中expr1和expr2可以是任何数据类型，但两个参数的数据类型必须是一致的。

计算员工月收入，如果comm列为空值的话，最终计算结果将是空，不符合逻辑，所以先将取NULL值的comm列转换为0，再相加。


    
    SELECTename, sal, comm,sal + nvl(comm, 0) salary
FROM emp;

4.3.2. NVL2

NVL2(expr1, expr2, expr3)：和NVL函数功能类似，都是将NULL转变为非空值。NVL2用来判断expr1是否为NULL，如果不是NULL，返回expr2，如果是NULL，返回expr3。


    
        SELECTename, sal, comm, 
nvl2(comm, sal + comm, sal) salary
    FROM emp;

Top

1. SQL(基础查询)

1.1. 基本查询语句

1.1.1. FROM子句

SQL查询语句的语法如下：


    
    SELECT <*, column [alias], …> FROM table;

其中：SELECT用于指定要查询的列，FROM指定要从哪个表中查询。如果要查询所有列，可以在SELECT后面使用*号，如果只查询特定的列，可以直接在SELECT后面指定列名，列名之间用逗号隔开。例句如下，查询dept表中的所有记录：


    
    SELECT * FROM dept;

1.1.2. 使用别名

在SQL语句中可以通过使用列的别名改变标题的显示样式，或者表示计算结果的含义，使用语法是列的别名跟在列名后，中间可以加或不加一个“AS”关键字。例如：


    
    SELECT empno AS id ,ename "Name", sal * 12 "Annual Salary"  FROM emp;

别名可以直接写，不必用双引号引起来。但是如果希望别名中区分大小写字符，或者别名中包含字符或空格，则必须用双引号引起来。

1.1.3. WHERE子句

在SELECT语句中，可以在WHERE子句中使用比较操作符限制查询结果，是可选的。

当查询条件中和数字比较，可以使用单引号引起，也可以不用，当和字符及日期类型的数据比较，则必须用单引号引起。例如查询部门10下的员工信息：


    
    SELECT * FROM empWHERE deptno = 10;

查询职员表中职位是’SALESMAN’的职员：


    
    SELECT ename, sal, job FROM emp WHERE job = 'SALESMAN';

图-1 用where子句定义查询条件

1.1.4. SELECT子句

如果只查询表的部分列，需要在SELECT后指定列名，例如：


    
    SELECT empno, ename, sal, job FROM emp;

图-2查询指定的列

1.2. 查询条件

1.2.1. 使用>, <, >=, <=, !=, <>, =

在WHERE子句中的查询条件，可以使用比较运算符来做查询。比如：查询职员表中薪水低于2000元的职员信息：


    
    SELECT ename, sal FROM emp WHERE sal< 2000;

查询职员表中不属于部门10的员工信息（!=等价于<>）：


    
        SELECT ename, sal, job FROM emp WHERE deptno != 10;

查询职员表中在2002年1月1号以后入职的职员信息，比较日期类型数据：


    
       SELECT ename, sal, hiredate FROM emp
    WHERE hiredate>to_date('2002-1-1','YYYY-MM-DD');

1.2.2. 使用AND，OR关键字

在SQL操作中，如果希望返回的结果必须满足多个条件，应该使用AND逻辑操作符连接这些条件，如果希望返回的结果满足多个条件之一即可，应该使用OR逻辑操作符连接这些条件。例如：查询薪水大于1000并且职位是’CLERK’的职员信息：


    
    SELECT ename, sal, job FROM emp
WHERE sal> 1000 AND job = 'CLERK';

查询薪水大于1000或者职位是’CLERK’的职员信息：


    
    SELECT ename, sal, job FROM emp
WHERE sal> 1000 OR job = 'CLERK';

1.2.3. 使用LIKE条件（模糊查询）

当用户在执行查询时，不能完全确定某些信息的查询条件，或者只知道信息的一部分，可以借助LIKE来实现模糊查询。LIKE需要借助两个通配符：

%：表示0到多个字符
_：标识单个字符

这两个通配符可以配合使用，构造灵活的匹配条件。例如查询职员姓名中第二个字符是‘A’的员工信息：


    
    SELECT ename, job FROM emp WHERE ename LIKE '_A%';

图-3 模糊查询的结果

1.2.4. 使用IN和NOT IN

在WHERE子句中可以用比较操作符IN(list)来取出符合列表范围中的数据。其中的参数list表示值列表，当列或表达式匹配于列表中的任何一个值时，条件为TRUE，该条记录则被显示出来。

IN页可以理解为一个范围比较操作符，只不过这个范围是一个指定的值列表，NOT IN(list) 取出不符合此列表中的数据记录。例如查询职位是MANAGER或者CLERK的员工：


    
    SELECT ename, job FROM emp WHERE job IN ('MANAGER', 'CLERK');

查询不是部门10或20的员工：


    
    SELECT ename, job FROM emp WHERE deptno NOT IN (10, 20);

1.2.5. BETWEEN…AND…

BETWEEN…AND…操作符用来查询符合某个值域范围条件的数据，最常见的是使用在数字类型的数据范围上，但对字符类型和日期类型数据也同样适用。例如查询薪水在1500-3000之间的职员信息：


    
        SELECT ename, sal FROM emp
    WHERE sal BETWEEN 1500 AND 3000;

1.2.6. 使用IS NULL和IS NOT NULL

空值NULL是一个特殊的值，比较的时候不能使用”=”号，必须使用IS NULL，否则不能得到正确的结果。例如查询哪些职员的奖金数据为NULL：


    
    SELECT ename, sal, comm FROM emp
WHERE comm IS NULL;

1.2.7. 使用ANY和ALL条件

在比较运算符中，可以出现ALL和ANY，表示“全部”和“任一”，但是ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。其中：

> ANY : 大于最小
< ANY：小于最大
> ALL：大于最大
< ALL：小于最小

例如，查询薪水比职位是“SALESMAN”的人高的员工信息，比任意一个SALESMAN高都行：


    
    SELECT empno, ename, job, sal, deptno
FROM emp
WHERE sal> ANY (
SELECT sal FROM emp WHERE job = 'SALESMAN');

1.2.8. 查询条件中使用表达式和函数

当查询需要对选出的字段进行进一步计算，可以在数字列上使用算术表达式(+、-、*、/)。表达式符合四则运算的默认优先级，如果要改变优先级可以使用括号。

算术运算主要是针对数字类型的数据，对日期类型的数据可以做加减操作，表示在一个日期值上加或减一个天数。

查询条件中使用字符串函数UPPER，将条件中的字符串变大写后再参与比较：


    
    SELECT ename, sal, job FROMempWHERE ename = UPPER('rose');

查询条件中使用算数表达式，查询年薪大于10w元的员工记录：


    
    SELECT ename, sal, job FROM empWHERE sal * 12 >100000;

1.2.9. 使用DISTINCT过滤重复

数据表中有可能存储相同数据的行，当执行查询操作时，默认情况会显示所有行，不管查询结果是否有重复的数据。当重复数据没有实际意义，经常会需要去掉重复值，使用DISTINCT实现。例如查询员工的部门编码，包含所有重复值：


    
    SELECT deptno FROM emp;

查询员工的部门编码，去掉重复值：


    
    SELECT DISTINCT deptno FROM emp;

DISTINCT后面的列可以组合查询，下例查询每个部门的职位，去掉重复值。注意是deptno和job联合起来不重复：


    
    SELECT DISTINCT deptno, job FROM emp;

图-4用distinct去掉重复的列

1.3. 排序

1.3.1. 使用ORDER BY字句

对查询出的数据按一定规则进行排序操作，使用ORDER BY子句。语法如下：


    
    SELECT <*, column [alias], …>
FROM table 
[WHERE condition(s)] 
[ORDER BY column [ASC | DESC]] ;

注意，ORDER BY必须出现在SELECT中的最后一个子句。下例对职员表按薪水排序：


    
    SELECT ename, sal
FROM emp
ORDER BY sal;

图-5用ORDER BY将查询结果排序

1.3.2. ASC和DESC

排序时默认按升序排列，即由小及大，ASC用来指定升序排序，DESC用来指定降序排序。

因为NULL值视作最大，则升序排列时，排在最后，降序排列时，排在最前。如果不写ASC或DESC，默认是ASC，升序排列。例如，按员工的经理升序排序：


    
        SELECT empno, ename, mgr FROM emp
    WHERE deptno = 10 ORDER BY mgr;

降序排列，必须指明，按员工的薪水倒序排序：


    
        SELECT ename, sal FROM emp
    ORDER BY sal DESC;

1.3.3. 多个列排序

当以多列作为排序标准时，首先按照第一列进行排序，如果第一列数据相同，再以第二列排序，以此类推。多列排序时，不管正序还是倒序，每个列需要单独设置排序方式。

下例对职员表中的职员排序，先按照部门编码正序排列，再按照薪水降序排列：


    
        SELECT ename, deptno, sal FROM emp
    ORDER BY deptno ASC, sal DESC;

1.4. 聚合函数

1.4.1. 什么是聚合函数

查询时需要做一些数据统计，比如：查询职员表中各部门职员的平均薪水，各部门的员工人数。当需要统计的数据并不能在职员表里直观列出，而是需要根据现有的数据计算得到结果，这种功能可以使用聚合函数来实现，即：将表的全部数据划分为几组数据，每组数据统计出一个结果。

因为是多行数据参与运算返回一行结果，也称作分组函数、多行函数、集合函数。用到的关键字：

GOURP BY 按什么分组
HAVING 进一步限制分组结果

1.4.2. MAX和MIN

用来取得列或表达式的最大、最小值，可以用来统计任何数据类型，包括数字、字符和日期。例如获取机构下的最高薪水和最低薪水，参数是数字：


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

计算最早和最晚的入职时间，参数是日期：


    
    SELECT MAX(hiredate) max_hire, MIN(hiredate) min_hire
FROM emp;

1.4.3. AVG和SUM

AVG和SUM函数用来统计列或表达式的平均值和和值，这两个函数只能操作数字类型，并忽略NULL值。例如获得机构下全部职员的平均薪水和薪水总和：


    
    SELECT AVG(sal) avg_sal, SUM(sal)  sum_sal  FROM emp;

1.4.4. COUNT

COUNT函数用来计算表中的记录条数，同样忽略NULL值。例如获取职员表中一共有多少名职员记录：


    
        SELECT COUNT(*) total_num  FROM emp;

获得职员表中有多少人是有职位的（忽略没有职位的员工记录）


    
        SELECT COUNT(job) total_job  FROM emp;

1.4.5. 聚合函数对空值的处理

聚合函数忽略NULL值。即当emp表中的某列有NULL值，比如某新入职员工没有薪水，比较两条语句的结果：


    
    SELECT AVG(sal)  avg_sal FROM emp;
SELECT AVG(NVL(sal,0)) avg_sal FROM emp;

1.5. 分组

1.5.1. GROUP BY子句

上面的例子都是以整个表作为一组。如果希望得到每个部门的平均薪水，而不是整个机构的平均薪水，需要把整个数据表按部门划分成一个个小组，每个小组中包含一行或多行数据，在每个小组中再使用分组函数进行计算，每组返回一个结果。语法如下：


    
    SELECT <*, column [alias], …>
FROM table [WHERE condition(s)] 
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column [ASC | DESC]] ;

其中划分的小组有多少，最终的结果集行数就有多少。

1.5.2. 分组查询

图-6分组查询

1.5.3. HAVING字句

HAVING子句用来对分组后的结果进一步限制，比如按部门分组后，得到每个部门的最高薪水，可以继续限制输出结果。必须跟在GROUP BY后面，不能单独存在。例如查询每个部门的最高薪水，只有最高薪水大于4000的记录才被输出显示：


    
    SELECT deptno, MAX(sal) max_sal FROM emp
GROUP BY deptno HAVING MAX(sal) >4000;

1.6. 查询语句的执行顺序

当一条查询语句中包含所有的子句，执行顺序依下列子句次序：

FROM 子句：执行顺序为从后往前、从右到左。数据量较少的表尽量放在后面。
WHERE子句：执行顺序为自下而上、从右到左。将能过滤掉最大数量记录的条件写在WHERE 子句的最右。
GROUP BY：执行顺序从左往右分组，最好在GROUP BY前使用WHERE将不需要的记录在GROUP BY之前过滤掉。
HAVING 子句：消耗资源。尽量避免使用，HAVING 会在检索出所有记录之后才对结果集进行过滤，需要排序等操作。
SELECT子句：少用*号，尽量取字段名称。ORACLE 在解析的过程中, 通过查询数据字典将*号依次转换成所有的列名, 消耗时间。
ORDER BY子句：执行顺序为从左到右排序，消耗资源。

2. SQL（关联查询）

2.1. 关联基础

2.1.1. 关联的概念

实际应用中所需要的数据，经常会需要查询两个或两个以上的表。这种查询两个或两个以上数据表或视图的查询叫做连接查询，连接查询通常建立在存在相互关系的父子表之间。语法如下：


    
    SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

或者：


    
    SELECT table1.column, table2.column
FROM table1JOIN table2
ON(table1.column1 = table2.column2);

2.1.2. 笛卡尔积

笛卡尔积指做关联操作的每个表的每一行都和其它表的每一行做组合，假设两个表的记录条数分别是X和Y，笛卡尔积将返回X * Y条记录。当两个表关联查询时，不写连接条件，得到的结果即是笛卡尔积。例如：


    
    SELECT COUNT(*) FROM emp; --14条记录
SELECT COUNT(*) FROM dept; --4条记录
SELECT emp.ename, dept.dnameFROM emp, dept;--56条记录

2.1.3. 等值连接

等值连接是连接查询中最常见的一种，通常是在有主外键关联关系的表间建立，并将连接条件设定为有关系的列，使用等号”=”连接相关的表。例如查询职员的姓名、职位以及所在部门的名字和所在城市，使用两个相关的列做等值操作：


    
    SELECT e.ename, e.job, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;

2.2. 关联查询

2.2.1. 内连接

内连接返回两个关联表中所有满足连接条件的记录。例如查询员工的名字和所在部门的名字：


    
    SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno

上面的语法也可以写为：


    
    SELECT e.ename, d.dname
FROM emp e JOIN dept d
ON(e.deptno = d.deptno);

2.2.2. 外连接

内连接返回两个表中所有满足连接条件的数据记录，在有些情况下，需要返回那些不满足连接条件的记录，需要使用外连接，即不仅返回满足连接条件的记录，还将返回不满足连接条件的记录。比如把没有职员的部门和没有部门的职员查出来。外连接的语法如下：


    
    SELECT table1.column, table2.column
FROM table1 [LEFT | RIGHT | FULL] JOIN table2
ON table1.column1 = table2.column2;

了解驱动表的概念。

图-7左外连接

图-8右外连接

外连接查询的例子,Emp表做驱动表:


    
    SELECT e.ename, d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;

Dept表做驱动表:


    
    SELECT e.ename, d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;

2.2.3. 全连接

全外连接是指除了返回两个表中满足连接条件的记录，还会返回不满足连接条件的所有其它行。即是左外连接和右外连接查询结果的总和。例如：


    
    SELECT e.ename, d.dname
FROM emp e FULL OUTER JOIN dept d
ON e.deptno = d.deptno;

图-9 全外连接

2.2.4. 自连接

自连接是一种特殊的连接查询，数据的来源是一个表，即关联关系来自于单表中的多个列。表中的列参照同一个表中的其它列的情况称作自参照表。

自连接是通过将表用别名虚拟成两个表的方式实现，可以是等值或不等值连接。例如查出每个职员的经理名字，以及他们的职员编码：


    
    SELECT worker.empnow_empno, worker.enamew_ename, manager.empnom_empno, manager.enamem_ename
FROM emp worker join emp manager
ON worker.mgr = manager.empno;

Top

SQL(高级查询)

1. SQL(高级查询)

1.1. 子查询

1.1.1. 子查询在WHERE子句中

在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果。为了给查询提供数据而首先执行的查询语句叫做子查询。

子查询：嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中。子查询嵌入的语句称作主查询或父查询。主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句。

根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查询。

图-1子查询

例如查找和SCOTT同职位的员工：


    
    SELECT e.ename, e.job
FROM emp e
WHERE e.job = 
  (SELECT job FROM emp WHERE ename = 'SCOTT')；

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


    
    SELECT deptno, ename, sal
FROM emp e
WHERE sal> (SELECT AVG(sal) FROM emp);

如果子查询返回多行，主查询中要使用多行比较操作符，包括IN、ALL、ANY。其中ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。例如查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息：


    
    SELECT empno, ename, job, sal, deptno
FROM emp
WHERE deptno IN 
(SELECT deptno FROM emp WHERE job = 'SALESMAN')
AND job <> 'SALESMAN';

在子查询中需要引用到主查询的字段数据，使用EXISTS关键字。EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE。如果子查询没有结果，则返回FALSE。例如列出来那些有员工的部门信息：


    
    SELECT deptno, dname FROM dept d
WHERE EXISTS 
                (SELECT * FROM emp e
                 WHERE d.deptno = e.deptno);

1.1.2. 子查询在HAVING部分

子查询不仅可以出现在WHERE子句中，还可以出现在HAVING部分。例如查询列出最低薪水高于部门30的最低薪水的部门信息：


    
    SELECT deptno, MIN(sal) min_sal
FROM emp
GROUP BY deptno
HAVING MIN(sal) >
(SELECT MIN(sal) FROM emp WHERE deptno = 30);

1.1.3. 子查询在FROM部分

在查询语句中，FROM子句用来指定要查询的表。如果要在一个子查询的结果中继续查询，则子查询出现在FROM 子句中，这个子查询也称作行内视图或者匿名视图。这时，把子查询当作视图对待，但视图没有名字，只能在当前的SQL语句中有效。

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


    
    SELECT e.deptno, e.ename, e.sal
FROM emp e,
(SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) x
WHERE e.deptno = x.deptno
ande.sal>x.avg_sal
ORDER BY e.deptno;

1.1.4. 子查询在SELECT部分

把子查询放在SELECT子句部分，可以认为是外连接的另一种表现形式，使用更灵活：


    
    SELECT e.ename, e.sal, e.deptno,
           (SELECT d.deptno FROM dept d 
            WHERE d.deptno = e.deptno)  deptno
FROM emp e;

1.2. 分页查询

1.2.1. ROWNUM

ROWNUM被称作伪列，用于返回标识行数据顺序的数字。例如：


    
    SELECT ROWNUM, empno, ename, sal
FROM emp;

ROWNUM只能从1计数，不能从结果集中直接截取。下面的查询语句将没有结果：


    
    SELECT ROWNUM, empno, ename, sal
FROM emp
WHERE rownum> 3;

如果利用ROWNUM截取结果集中的部分数据，需要用到行内视图：


    
    SELECT  * FROM 
  (SELECT  ROWNUMrn , e.* FROM emp e )  
 WHERE rn BETWEEN 8 AND 10;

也就是将ROWNUM先作为行内视图的一个列，在主查询中就可以使用这个列值作为条件。

1.2.2. 使用子查询进行分页

分页策略是指每次只取一页的数据。当每次换页，取下一页的数据。在ORACLE中利用ROWNUM的功能来进行分页。

假设结果集共105条，每20条分为一页，则共6页：

Page1： 1 至 20

Page2： 21 至40

…

PageN: (n - 1) * pageSize + 1 至 n * pageSize

1.2.3. 分页与ORDER BY

按薪水倒序排列，取出结果集中第8到第10条的记录：


    
    SELECT  * FROM 
  (SELECT  ROWNUMrn , t.* FROM 
    (SELECT empno,ename,sal FROM emp
     ORDER BY sal DESC)  t
  )  
 WHERE rn BETWEEN 8 AND 10;

根据要查看的页数，计算起点值（(n - 1) * pageSize + 1）和终点值（n * pageSize），替换掉BETWEEN和AND的参数，即得到当前页的记录。

1.3. DECODE函数

1.3.1. DECODE函数基本语法

DECODE函数的语法如下：

DECODE (expr, search1, result1[, search2, result2…][, default])

它用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值。default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。

查询职员表，根据职员的职位计算奖励金额，当职位分别是’MANAGER’、’ANALYST’、’SALESMAN’时，奖励金额分别是薪水的1.2倍、1.1倍、1.05倍，如果不是这三个职位，则奖励金额取薪水值：


    
    SELECT ename, job, sal,
DECODE(job, 'MANAGER', sal * 1.2,
               'ANALYST', sal * 1.1,
              'SALESMAN', sal * 1.05,
sal
     ) bonus
FROM emp;

和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作。


    
    SELECT ename, job, sal,
     CASE job WHEN 'MANAGER' THEN sal * 1.2
      WHEN 'ANALYST' THEN sal * 1.1
     WHEN 'SALESMAN' THEN sal * 1.05
                    ELSE sal END
bonus
FROM emp;

1.3.2. DECODE函数在分组查询中的应用

DECODE函数可以按字段内容分组，例如：计算职位的人数，analyst/manager职位属于vip，其余是普通员工operation,这种功能无法用GROUP BY简单实现。用decode的实现方式：


    
    SELECT DECODE(job, 'ANALYST', 'VIP', 
                       'MANAGER', 'VIP', 
                       'OPERATION') job,
COUNT(1) job_cnt
FROM emp
GROUP BY DECODE(job, 'ANALYST', 'VIP', 'MANAGER', 'VIP', 'OPERATION');

图-2DECODE函数的运行结果

DECODE函数也可以按字段内容排序，例如：Dept表中按”研发部”、“市场部”、“销售部”排序，用普通的select语句，无法按照字面数据排序：


    
    SELECT deptno, dname, loc
FROM dept
ORDER BY 
      DECODE(dname, '研发部',1,'市场部',2,'销售部',3), loc;

1.4. 排序函数

1.4.1. ROW_NUMBER

ROW_NUMBER语法如下：


    
    ROW_NUMBER() OVER(
          PARTITION BY col1 ORDER BY col2)

表示根据col1分组，在分组内部根据col2排序。此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一。

ROWNUM是伪列， ROW_NUMBER功能更强，可以直接从结果集中取出子集。

场景：按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码


    
    SELECT deptno, ename, empno,
 ROW_NUMBER()
   OVER (PARTITION BY deptno ORDER BY empno)     AS emp_id
   FROM emp;

1.4.2. RANK

RANK函数的语法如下：


    
    RANK() OVER(
          PARTITION BY col1 ORDER BY col2)

表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。特点是跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名。

和ROW_NUMBER的区别是有结果有重复值，而ROW_NUMBER没有。

场景：按照部门编码分组，同组内按薪水倒序排序，相同薪水则按奖金数正序排序，并给予组内等级，用Rank_ID表示


    
    SELECT deptno, ename, sal, comm,
RANK() OVER (PARTITION BY deptno
   ORDER BY sal DESC, comm) "Rank_ID"
   FROM emp；

1.4.3. DENSE_RANK

DENSE_RANK函数的语法如下：


    
    DENSE_RANK() OVER(
          PARTITION BY col1 ORDER BY col2)

表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。特点是连续排序，如果有并列第二，下一个排序将是三，这一点是和RANK的不同，RANK是跳跃排序。

场景：关联emp和dept表，按照部门编码分组，每组内按照员工薪水排序，列出员工的部门名字、姓名和薪水：


    
    SELECT d.dname, e.ename, e.sal, 
            DENSE_RANK()
      OVER (PARTITION BY e.deptno ORDER BY e.sal)     
            AS drank
   FROM emp e join dept d
one.deptno = d.deptno;

1.5. 高级分组函数

1.5.1. ROLLUP

ROLLUP、CUBE 和 GROUPING SETS 运算符是 GROUP BY 子句的扩展，可以生成与使用 UNION ALL 来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询。语法形式如下：

GROUP BY ROLLUP(a, b, c)
GROUP BY CUBE(a, b, c)
GROUP BY GROUPING SETS ( (a), (b))

假设有表test，有a、b、c、d四个列。


    
    SELECT a,b,c,SUM(d) FROM test GROUP BY ROLLUP(a,b,c);

等价于：


    
    SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
UNION ALL
SELECT a,b,null,SUM(d) FROM test GROUP BY a,b
UNION ALL
SELECT a,null,null,SUM(d) FROM test GROUP BY a
UNION ALL
SELECT null,null,null,sum(d) FROM test;

对ROLLUP的列从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组(也就是全表分组)。对于n个参数的ROLLUP，有n+1次分组。

表－1 数据样例表

准备数据：


    
    SQL>DROP TABLE sales_tab;
SQL>CREATE TABLE sales_tab (
year_id   NUMBER NOT NULL,
month_id   NUMBER NOT NULL,
day_id   NUMBER NOT NULL,
sales_value NUMBER(10,2) NOT NULL
);
SQL>INSERT INTO sales_tab
SELECT TRUNC(DBMS_RANDOM.value(low => 2010, high => 2012)) AS year_id,
TRUNC(DBMS_RANDOM.value(low => 1, high => 13)) AS month_id,
TRUNC(DBMS_RANDOM.value(low => 1, high => 32)) AS day_id,
ROUND(DBMS_RANDOM.value(low => 1, high => 100), 2) AS sales_value
FROM   dual
CONNECT BY level <= 1000;
SQL>COMMIT;

复习组函数的用法：


    
    SQL>SELECT SUM(sales_value) AS sales_value FROM   sales_tab;
SQL>SELECT year_id, COUNT(*) AS num_rows,
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY year_id
ORDER BY year_id;
SQL>SELECT year_id, month_id,
COUNT(*) AS num_rows,
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY year_id, month_id
ORDER BY year_id, month_id;

图-3 在测试表中使用组函数的运行结果

图-4 在测试表中使用组函数的运行结果

ROLLUP函数的用法：


    
    SELECT year_id, month_id, 
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY 
ROLLUP (year_id, month_id)
ORDER BY year_id, month_id;

图-5 在测试表中使用ROLLUP函数的运行结果


    
    SELECT year_id, month_id, day_id, SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY ROLLUP (year_id, month_id, day_id)
ORDER BY year_id, month_id, day_id;

图-6 在测试表中使用ROLLUP函数的运行结果

1.5.2. CUBE

CUBE函数的语法形式：


    
    GROUP BY CUBE(a, b, c)

对cube的每个参数，都可以理解为取值为参与分组和不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合。对于n个参数的cube，有2^n次分组。

如果GROUP BY CUBE(a,b,c)，首先对(a,b,c)进行GROUP BY，然后依次是(a,b)，(a,c)，(a)，(b,c)，(b)，(c)，最后对全表进行GROUP BY操作，所以一共是2^3=8次分组。


    
    SELECT a,b,c,SUM(d) FROM test GROUP BY CUBE(a,b,c);

等价于：


    
    SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
UNION ALL
SELECT a,b,NULL,SUM(d) FROM test GROUP BY a,b
UNION ALL
SELECT a,NULL,c,SUM(d) FROM test GROUP BY a,c
UNION ALL
SELECT a,NULL,NULL,SUM(d) FROM test GROUP BY a
UNION ALL
SELECT NULL,b,c,SUM(d) FROM test GROUP BY b,c
UNION ALL
SELECT NULL,b,NULL,SUM(d) FROM test GROUP BY b
UNION ALL
SELECT NULL,NULL,c,SUM(d) FROM test GROUP BY c
UNION ALL
SELECT NULL,NULL,NULL,SUM(d) FROM test ;

等价于只是方便理解，其内部运行机制并不相同，其效率远高于UNION ALL。

在sales_value表中使用cube函数：


    
    SELECT year_id, month_id,
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY CUBE (year_id, month_id)
ORDER BY year_id, month_id;

图-7在测试表中使用CUBE函数的运行结果


    
    SELECT year_id,  month_id, day_id,
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY CUBE (year_id, month_id, day_id)
ORDER BY year_id, month_id, day_id;

1.5.3. GROUPING SETS

GROUPING SETS 运算符可以生成与使用单个 GROUP BY、ROLLUP 或 CUBE 运算符所生成的结果集相同的结果集，但是使用更灵活。

如果不需要获得由完备的 ROLLUP 或 CUBE 运算符生成的全部分组，则可以使用 GROUPING SETS 仅指定所需的分组。GROUPING SETS 列表可以包含重复的分组。

GROUPING SETS示例：


    
    SELECT year_id, month_id, SUM(sales_value)  
FROM sales_tab
GROUP BY CUBE (year_id,month_id)
order by 1, 2; 
SELECT year_id, month_id, SUM(sales_value)  
FROM sales_tab
GROUP BY GROUPING SETS (  (year_id), (month_id))
order by 1, 2

其中分组方式示例如下：

使用GROUP BY GROUPING SETS(a,b,c)，则对(a),(b),(c)进行GROUP BY
使用GROUP BY GROUPING SETS((a,b),c), 则对(a,b),(c)进行GROUP BY
GROUPING BY GROUPING SET(a,a) , 则对(a)进行2次GROUP BY, GROUPING SETS的参数允许重复

1.6. 集合操作

1.6.1. UNION、UNION ALL
图-8 集合操作

为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、交、差。

集合操作符包括UNION、UNION ALL、INTERSECT和MINUS。多条作集合操作的SELECT语句的列的个数和数据类型必须匹配。

ORDER BY子句只能放在最后的一个查询语句中。

集合操作的语法如下：


    
    SELECT statement1
[UNION | UNION ALL | INTERSECT | MINUS]
SELECT statement2;

UNION和UNION ALL用来获取两个或两个以上结果集的并集：

UNION操作符会自动去掉合并后的重复记录。
UNION ALL返回两个结果集中的所有行，包括重复的行。
UNION操作符对查询结果排序，UNION ALL不排序。

例如，合并职位是’MANAGER’的员工和薪水大于2500的员工集合，查看两种方式的结果差别：


    
    --Union
SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
SELECT ename, job, sal FROM emp
WHERE sal> 2500;
--Union all
SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

1.6.2. INTERSECT

INTERSECT函数获得两个结果集的交集，只有同时存在于两个结果集中的数据，才被显示输出。使用INTERSECT操作符后的结果集会以第一列的数据作升序排列。

例如：显示职位是’MANAGER’的员工和薪水大于2500的员工的交集：


    
    SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
INTERSECT
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

1.6.3. MINUS

MINUS函数获取两个结果集的差集。只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能够被显示出来。也就是结果集一减去结果集二的结果。

例如：列出职位是MANAGER但薪水低于2500的员工记录：


    
    SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
MINUS
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

Top

1. 视图、序列、索引

1.1. 视图

1.1.1. 什么是视图

视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。

视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

图-1 视图和表

创建视图的语法：


    
    CREATE [OR REPLACE] VIEW view_name[(alias[, alias…])]
AS subquery ;

视图创建后，可以像操作表一样操作视图，主要是查询操作。

语法中的Subquery是SELECT查询语句，对应的表被称作基表。

根据视图所对应的子查询种类分为几种类型:

SELECT语句是基于单表建立的，且不包含任何函数运算、表达式或分组函数，叫做简单视图，此时视图是基表的子集；
SELECT语句同样是基于单表，但包含了单行函数、表达式、分组函数或GROUP BY子句，叫做复杂视图；
SELECT语句是基于多个表的，叫做连接视图。

1.1.2. 视图的作用

如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询；

视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，对基表中的其它列起到安全和保密的作用，可以限制数据访问。

1.1.3. 授权创建视图

创建视图的DDL语句是CREATE VIEW，用户必须有CREATE VIEW系统权限，才能创建视图。如果没有权限，创建视图时会提示：权限不足。

管理员可以通过DCL语句授予用户创建视图的权限。下例中管理员给用户tarena创建视图的权限：


    
    GRANT CREATE VIEW  TO tarena;

1.1.4. 创建简单视图（单表）

创建一个简单视图V_EMP_10，来显示部门10中的员工的编码、姓名和薪水：


    
    CREATE VIEW v_emp_10
AS
SELECT empno, ename, sal, deptno 
FROM emp 
WHERE deptno = 10;

查看视图结构：


    
    DESC v_emp_10;

1.1.5. 查询视图

查询视图和查询表的操作相同：


    
    SELECT * FROM v_emp_10;

此时视图的列名，和创建视图时的列名一致，不一定是原列名：


    
    SELECT id, name, salary FROM v_emp_10;

1.1.6. 对视图进行INSERT操作

视图本身并不包含数据，只是基表数据的逻辑映射。所以当对视图执行DML操作时，实际上是对基表的DML操作。对视图执行DML操作的基本原则：

简单视图能够执行DML操作，下列情况除外：在基表中定义了非空列，但简单视图对应的SELECT语句并没有包含这个非空列，导致这个非空列对视图不可见，这时无法对视图执行INSERT操作；
如果视图定义中包含了函数、表达式、分组语句、DISTINCT关键字或ROWNUM伪列，不允许执行DML操作；
DML操作不能违反基表的约束条件。

对简单视图执行INSERT操作，成功插入数据到基表中:


    
    INSERT INTO v_emp_10 
VALUES(1234, 'DOCTOR', 4000, 10);

简单视图可以通过DML操作影响到基表数据。

1.1.7. 创建具有CHECK OPTION约束的视图

语法如下：


    
    CREATE [OR REPLACE] VIEW view_name[(alias[, alias…])]
AS subquery 
[WITH CHECK OPTION];

其中：WITH CHECK OPTION短语表示，通过视图所做的修改，必须在视图的可见范围内：

假设INSERT，新增的记录在视图仍可查看
假设UPDATE，修改后的结果必须能通过视图查看到
假设DELETE，只能删除现有视图里能查到的记录

创建带有CHECK OPTION约束的视图：


    
    CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno id, ename name, sal salary, deptno 
FROM emp 
WHERE deptno = 10
WITH CHECK OPTION;

下述DML语句操作失败，因为部门20不在视图可见范围内：


    
    INSERT INTO v_emp_10 VALUES(1008,‘donna’,5500, 20); 
UPDATE v_emp_10 SET deptno = 20 WHERE id = 7782;

1.1.8. 创建具有READ ONLY约束的视图

对简单视图进行DML操作是合法的，但是不安全的。如果没有在视图上执行 DML 操作的必要，在建立视图时声明为只读来避免这种情况，保证视图对应的基表数据不会被非法修改。加入READ ONLY约束的视图语法如下：


    
    CREATE [OR REPLACE] VIEW view_name[(alias[, alias…])]
AS subquery 
[WITH READ ONLY];

创建视图，带有READ ONLY约束：


    
    CREATE OR REPLACE VIEW v_emp_10
AS
SELECT empno, ename, sal, deptno FROM emp 
WHERE deptno = 10
WITH READ ONLY;

此时对只读视图执行DML操作，将会失败：


    
    INSERT INTO v_emp_10 
VALUES(1258, 'DONNA', 3000, 10);

得到如下结果：

ERROR 位于第 1 行:

ORA-01733: 此处不允许虚拟列

1.1.9. 通过查询USER_VIEWS获取相关信息

和视图相关的数据字典有：

USER_OBJECTS
USER_VIEWS
USER_UPDATABLE_COLUMNS

例一：在数据字典USER_OBJECTS中查询所有视图名称：


    
    SELECT object_name FROM user_objects 
WHERE object_type = 'VIEW';

例二：在数据字典USER_VIEWS中查询指定视图：


    
    SELECT text FROM user_views 
WHERE view_name = 'V_EMP_10';

例三：在数据字典USER_UPDATABLE_COLUMNS中查询视图：


    
    SELECT column_name, insertable, updatable, deletable 
FROM user_updatable_columns
WHERE table_name = 'V_EMP_10';

1.1.10. 创建复杂视图（多表关联）

复杂视图指在子查询中包含了表达式、单行函数或分组函数的视图。此时必须为子查询中的表达式或函数定义别名。

例如，创建一个视图V_EMP_SALARY，把职员表的数据按部门分组，获得每个部门的平均薪水、薪水总和、最高薪水和最低薪水：


    
    CREATE VIEW v_emp_salary
AS
SELECT d.dname, avg(e.sal) avg_sal, sum(e.sal) sum_sal, 
max(e.sal) max_sal, min(e.sal) min_sal 
FROM emp e join dept d
ON e.deptno = d.deptno
GROUP BY d.dname;

查询复杂视图：


    
    SELECT * FROM v_emp_salary;

复杂视图不允许DML操作，会报错。

当不再需要视图的定义，可以使用DROP VIEW语句删除视图，语法如下：


    
    DROP VIEW view_name;

例如删除视图v_emp_10：


    
    DROP VIEW v_emp_10;

视图虽然是存放在数据字典中的独立对象，但视图仅仅是基于表的一个查询定义，所以对视图的删除不会导致基表数据的丢失，不会影响基表数据。

1.2. 序列

1.2.1. 什么是序列

序列(SEQUENCE)是一种用来生成唯一数字值的数据库对象。序列的值由Oracle程序按递增或递减顺序自动生成，通常用来自动产生表的主键值，是一种高效率获得唯一键值的途径。

序列是独立的数据库对象，和表是独立的对象，序列并不依附于表。

通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值。

1.2.2. 创建序列

创建序列的语法：


    
    CREATE SEQUENCE [schema.]sequence_name
    [ START WITH i ] [ INCREMENT BY j ]
    [ MAXVALUE m | NOMAXVALUE ]
    [ MINVALUE n | NOMINVALUE ]
    [ CYCLE | NOCYCLE ][ CACHE p | NOCACHE ]

其中：

sequence_name是序列名，将创建在schema方案下
序列的第一个序列值是i，步进是j
如果j是正数，表示递增，如果是负数，表示递减
序列可生成的最大值是m，最小值是n
如果没有设置任何可选参数，序列的第一个值是1，步进是1
CYCLE表示在递增至最大值或递减至最小值之后是否继续生成序列号，默认是NOCYCLE
CACHE用来指定先预取p个数据在缓存中，以提高序列值的生成效率，默认是20

1.2.3. 使用序列

举例说明，创建一个序列，起始数据是100，步进是10：


    
    CREATE SEQUENCE emp_seq 
    START WITH 100
    INCREMENT BY 10;

当序列被创建后，第一个序列值将是100，将要生成的序列号分别是110、120、130等。

序列中有两个伪列：

NEXTVAL：获取序列的下个值
CURRVAL：获取序列的当前值

当序列创建以后，必须先执行一次NEXTVAL，之后才能使用CURRVAL。

获取序列的第一个值，并且使用序列值为EMP表插入新的记录：


    
        SELECT emp_seq.NEXTVAL FROM DUAL;
    INSERT INTO emp(empno, ename)      
                VALUES(emp_seq.NEXTVAL, 'donna');

查询刚刚生成的记录，主键值将是110：


    
    SELECT empno, ename FROM emp
 WHERE ename = 'DONNA';

此时查询序列的当前值，会得到110的数字。


    
    SELECT emp_seq.CURRVAL FROM DUAL;

在序列的使用过程中，比如执行了一条语句：


    
    SELECT emp_seq.NEXTVAL FROM DUAL

则浪费了一个序列值，会导致表的主键值不连续。而CURRVAL的使用不会导致序列值的递进。

1.2.4. 删除序列

删除序列的语法如下：


    
    DROP SEQUENCE sequence_name;

删除序列emp_seq：


    
    DROP SEQUENCE emp_seq;

1.3. 索引

1.3.1. 索引的原理

索引是一种允许直接访问数据表中某一数据行的树型结构，为了提高查询效率而引入，是独立于表的对象，可以存放在与表不同的表空间（TABLESPACE）中。索引记录中存有索引关键字和指向表中数据的指针（地址）。对索引进行的I/O操作比对表进行操作要少很多。

索引一旦被建立就将被Oracle系统自动维护，查询语句中不用指定使用哪个索引，是一种提高查询效率的机制。

图-2 Oracle B-tree索引的结构

ROWID: 伪列，唯一标识一条数据记录，可理解为行地址。

1.3.2. 创建索引

创建索引的语法：


    
    CREATE [UNIQUE] INDEX index_name
        ON table(column[, column…]);

其中：

index_name表示索引名称
table表示表名
column表示列名，可以建立单列索引或复合索引
UNIQUE表示唯一索引

在EMP表的ENAME列上建立索引：


    
    CREATE INDEX idx_emp_ename ON emp(ename);

复合索引也叫多列索引，是基于多个列的索引。如果经常在ORDER BY子句中使用job和salary作为排序依据，可以建立复合索引：


    
    CREATE INDEX idx_emp_job_sal ON emp(job, sal);

当做下面的查询时，会自动应用索引idx_emp_job_sal


    
    SELECT empno, ename, sal, job FROM emp 
ORDER BY job, sal;

1.3.3. 创建基于函数的索引

如果需要在emp表的ename列上执行大小写无关搜索，可以在此列上建立一个基于UPPER函数的索引:


    
        CREATE INDEX emp_ename_upper_idx 
    ON emp(UPPER(ename));

当做下面的查询时，会自动应用刚刚建立的索引:


    
        SELECT * FROM emp 
        WHERE UPPER(ename) = 'KING';

1.3.4. 修改和删除索引

如果经常在索引列上执行DML操作，需要定期重建索引，提高索引的空间利用率，语法如下：


    
        ALTER INDEX index_name REBUILD;

重建索引idx_emp_ename：


    
        ALTER INDEX idx_emp_ename REBUILD;

当一个表上有不合理的索引，会导致操作性能下降，删除索引的语法：


    
        DROP INDEX index_name;

删除索引idx_emp_ename：


    
        DROP INDEX idx_emp_ename;

1.3.5. 合理使用索引提升查询效率

为提升查询效率，创建和使用索引的原则：

为经常出现在WHERE子句中的列创建索引
为经常出现在ORDER BY、DISTINCT后面的字段建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
为经常作为表的连接条件的列上创建索引
不要在经常做DML操作的表上建立索引
不要在小表上建立索引
限制表上的索引数目，索引并不是越多越好
删除很少被使用的、不合理的索引

2. 约束

2.1. 约束概述

2.1.1. 约束的作用

约束（CONSTRAINT）的全称是约束条件，也称作完整性约束条件。约束是在数据表上强制执行的一些数据校验规则，当执行DML操作时，数据必须符合这些规则，如果不符合则无法执行。

约束条件可以保证表中数据的完整性，保证数据间的商业逻辑。

2.1.2. 约束的类型

约束条件包括：

非空约束(Not Null)，简称NN
唯一性约束(Unique)，简称UK
主键约束(Primary Key)，简称PK
外键约束(Foreign Key)，简称FK
检查约束(Check)，简称CK

2.2. 非空约束

2.2.1. 建表时添加非空约束

非空约束用于确保字段值不为空。默认情况下，任何列都允许有空值，但业务逻辑可能会要求某些列不能取空值。当某个字段被设置了非空约束条件，这个字段中必须存在有效值，即：

当执行INSERT操作时，必须提供这个列的数据
当执行UPDATE操作时，不能给这个列的值设置为NULL

建表时添加非空约束：


    
    CREATE TABLE employees (
eid NUMBER(6),
name VARCHAR2(30) NOT NULL,
salary NUMBER(7, 2),
hiredate DATE 
      CONSTRAINT employees_hiredate_nn NOT NULL
);

2.2.2. 修改表时添加非空约束

可以在建表之后，通过修改表的定义，添加非空约束：


    
    ALTER TABLE employees 
MODIFY (eid NUMBER(6) NOT NULL);

2.2.3. 取消非空约束

如果业务要求取消某列的非空约束，可以采用重建表或者修改表的方式：


    
    ALTER TABLE employees 
MODIFY (eid NUMBER(6));

2.3. 唯一性约束

2.3.1. 什么是唯一性约束

唯一性(Unique)约束条件用于保证字段或者字段的组合不出现重复值。当给表的某个列定义了唯一约束条件，该列的值不允许重复，但允许是NULL值。

唯一性约束条件可以在建表同时建立，也可以在建表以后再建立。

2.3.2. 添加唯一性约束

在建表employees的同时，在eid、email列上创建唯一约束条件，并在建表后在name列上建立一个名为employees_name_uk的唯一约束条件：


    
    DROP TABLE employees ; --将表删掉重新创建
CREATE TABLE 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 employees 
ADD CONSTRAINT employees_name_uk UNIQUE(name);

2.4. 主键约束

2.4.1. 主键的意义

主键(Primary Key)约束条件从功能上看相当于非空（NOT NULL）且唯一（UNIQUE）的组合。主键字段可以是单字段或多字段组合，即：在主键约束下的单字段或者多字段组合上不允许有空值，也不允许有重复值。

主键可以用来在表中唯一的确定一行数据。一个表上只允许建立一个主键，而其它约束条件则没有明确的个数限制。

2.4.2. 主键选取的原则

主键应是对系统无意义的数据
永远也不要更新主键，让主键除了唯一标识一行之外，再无其他的用途
主键不应包含动态变化的数据，如时间戳
主键应自动生成，不要人为干预，以免使它带有除了唯一标识一行以外的意义
主键尽量建立在单列上

2.4.3. 添加主键约束

在建表时添加主键约束条件：


    
    CREATE TABLE employees2 (
eid NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2),
hiredate DATE
);

建表后创建主键约束条件，并自定义约束条件名称：


    
    CREATE TABLE employees3 (
eid NUMBER(6),
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7, 2),
hiredate DATE
);
ALTER TABLE employees3 
ADD CONSTRAINT 
         employees3_eid_pk PRIMARY KEY (eid);

2.5. 外键约束

2.5.1. 2.5.1 外键约束的意义

外键约束条件定义在两个表的字段或一个表的两个字段上，用于保证相关两个字段的关系。比如emp表的deptno列参照dept表的deptno列，则dept称作主表或父表，emp表称作从表或子表。

2.5.2. 2.5.2 添加外键约束

先建表，在建表后建立外键约束条件：


    
    CREATE TABLE employees4 (
eid NUMBER(6),
name VARCHAR2(30),
salary NUMBER(7, 2),
deptno NUMBER(4)
);
ALTER TABLE employees4
ADD CONSTRAINT employees4_deptno_fk 
FOREIGN KEY (deptno) REFERENCES dept(deptno);

2.5.3. 2.5.3 外键约束对一致性的维护

外键约束条件包括两个方面的数据约束：

从表上定义的外键的列值，必须从主表被参照的列值中选取，或者为NULL；
当主表参照列的值被从表参照时，主表的该行记录不允许被删除。


    
    --成功DML语句：
INSERT INTO employees4(eid, name, deptno) 
    VALUES(1234, ‘rose tyler’, 40);--成功
INSERT INTO employees4(eid, name, deptno) 
    VALUES(1235, ‘martha jones’, NULL); --成功
--失败DML语句：
INSERT INTO   employees4(eid, name, deptno) 
    VALUES(1236, 'donna noble', 50);
    --失败，不存在部门50
DELETE FROM dept WHERE deptno  = 40;
    --失败，40被参照，不允许删除

2.5.4. 2.5.4 外键约束对性能的降低

如果在一个频繁DML操作的表上建立外键，每次DML操作，都将导致数据库自动对外键所关联的对应表做检查，产生开销，如果已在程序中控制逻辑，这些判断将增加额外负担，可以省去。

另外外键确定了主从表的先后生成关系，有时会影响业务逻辑。

2.5.5. 2.5.5 关联不一定需要外键约束

如果业务逻辑要求保证数据完整性，可由程序或触发器控制，不一定需要外键约束。

另外为了简化开发，维护数据时不用考虑外键约束，以及大量数据DML操作时不需考虑外键耗费时间。

2.6. 检查约束

2.6.1. 2.6.1 什么是检查约束

检查(Check)约束条件用来强制在字段上的每个值都要满足Check中定义的条件。当定义了Check约束的列新增或修改数据时，数据必须符合Check约束中定义的条件。

2.6.2. 2.6.2 添加检查约束

员工的薪水必须大于2000元，增加检查约束：


    
    ALTER TABLE employees4
ADD CONSTRAINT employees4_salary_check
CHECK (salary > 2000);

当插入大于2000的数据，操作成功：


    
    INSERT INTO employees4(eid, name, salary, deptno) 
VALUES(1236, 'donna noble', 2500, 40);

试图修改职员的薪水为1500元，更新失败：


    
    UPDATE employees4 SET salary = 1500
 WHERE eid = 1236;

学习笔记：

一、数据库简介

1、数据库（Database，简称DB）：是按照数据结构来组织、储存和管理数据的仓库。

数据库管理系统（简称DBMS）：管理数据库的软件。

数据库对象: 表，视图，索引，序列。

伪表（dual）：伪表不是一张真是存在的表，当查询的内容与任何表数据无关时，可以使用伪表。（伪表只在oracle数据库中用）

例：SELECT SYSDATE FROM dual //查看当前时间来自伪表

2、表的概念

一个关系数据库由多个数据表（Table）组成，数据表示关系数据库的基本储存结构。

表是二维的，有行和列组成。行是横排数据，也被称作记录。列是横排数据，也称字段。

表与表之间存在关联关系！

二、SQL结构化查询语言：

1、SQL概念:

结构化查询语句，是用来操作数据库的语言，所有的数据库都支持表示准的SQL语句。

2、SQL语句包含:

①数据定义语言DDL、

②数据操控语言DML、

③事务控制语言TCL、

④数据查询语言DQL、

⑤数据控制语言DCL、

① DDL语句： DDL语句是用于增、删、改数据库对象的。

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

AKTER:修改表；

DROP:删除表；

TRUNCATE:删除表数据，保留表结构

例：创建表:

CREATE TABLE employee_xxx(

id NUMBER(4),

name VARCHAR2(20),

gender CHAR(1),

birth DATE,

salary NUMBER(6,2),

job VARCHAR2(30),

deptno NUMBER(2)

)

1）、查看表结构: DESC +表名

例： DESC employee

2）、删除表: DROP TABLE +表名

例： DROP TABLE employee

SQL语句是不区分大小写的，但是字符串的值(直接量)是区分大小写的，字符串的直接量是使用单引号括起来的。数据库中所有数据类型的默认值都是NULL，在创建表时，可以使用DEFAULT为字段单独指定默认值。

例：CREATE TABLE employee(

id NUMBER(4),

name VARCHAR2(20),

gender CHAR(1)DEFAULT 'M',

birth DATE,

salaryNUMBER(6,2) DEFAULT 5000,

jobVARCHAR2(30) DEFAULT 'CLERK',

deptnoNUMBER(2)

);

DESC employee

非空约束：当一个字段被NOT NULL修饰后，该字段在任何情况下值不能为NULL。非空约束可以在查看表结构中体现出来。

例：

CREATE TABLE employee(

id NUMBER(4),

name VARCHAR2(20)NOT NULL,

gender CHAR(1)DEFAULT 'M',

birth DATE,

salaryNUMBER(6,2) DEFAULT 5000,

Job VARCHAR2(30)DEFAULT 'CLERK',

deptnoNUMBER(2)

);

DESC employee;

修改表：

修改表中现有字段，修改字段可以修改字段的类型、长度、默认值、非空约束。但是表中若已经存在数据，那么修改字段的时候，尽量不修改类型。若修改长度尽量不要缩小，否

则可能导致修改失败。

1、修改表名:

RENAME old_name TO new_name

例：将employee表改名为myemp：RENAME employee TO myemp

2、修改表结构:

（1）添加新字段

例：向表myemp中添加字段hiredate： ALTER TABLE myemp ADD( hiredate DATE)

（2）删除表中现有字段:

例：将myemp表中的hiredate字段删除： ALTER TABLE myemp DROP(hiredate)

（3）修改字段

例：ALTER TABLE myemp MODIFY(job VARCHAR2(40) DEFAULT 'CLERK') ;

② DML语句：用来对表中数据进行相关操作。包括:增，删，改。

INSERT:将数据插入到数据库中

UPDATE:更新数据表中已存在的数据

DELETE:删除数据表中的数据

1、插入数据

例：INSERT INTO myemp (id,name,salary,deptno) VALUES (1,'JACK',3000,10)

插入数据时，忽略字段名则是全列插入。

例：INSERT INTO myemp VALUES (2,'ROSE','F',SYSDATE,5000, 'MANAGER',20)

插入日期时，使用TO_DATE函数

例：INSERT INTO myemp

(id,name,birth)

VALUES

(3,'JACKSON',TO_DATE('1992-08-02','YYYY-MM-DD'))

2、修改数据

UPDATE语句用于修改表中数据，需要使用WHERE添加条件以修改满足条件的记录，若

不添加WHERE则是全表所有数据修改! UPDATE更新

例：将ROSE的工资改为6000,部门号改为30：

UPDATE myemp SET salary=6000,deptno=30 WHERE name='ROSE'

3、删除表中数据

DELETE语句用于删除表中记录，通常需要使用WHERE添加条件来删除满足条件的记录。若不添加WHERE是清空表操作!

例：DELETE FROM myemp WHERE name='ROSE'

③ DQL语句：用于查询数据库中的数据。

DQL必须包含两个子句: SELECT , FROM

SELECT子句用来指定要查询的字段，可以是表中的字段，函数和表达式。

FROM子句用来指定数据来源的表

例：查看emp表中的所有数据：SELECT * FROM emp

例：只查看字段ename,job,sal,deptno：SELECT ename,job,sal,deptno FROM emp

例：查看20号部门的员工信息：SELECT ename,job,sal,deptno FROM emp WHERE deptno=20

DQL中也可以使用WHERE子句来添加过滤条件，这样只会将满足条件的记录查询出来。

SELECT子句中也可已使用函数或表达式。

例：查看公司每个员工的年薪是多少：SELECT ename,sal,sal*12 FROM emp

一、字符串函数：

1:CONCAT(char1,char2)

将两个参数字符串连接在一起返回

例：SELECT CONCAT(ename,sal) FROM emp

SELECT CONCAT(CONCAT(ename,','),sal) FROM emp

"||" 可以连接字符串

例：SELECT ename||','||sal FROM emp

2:LENGTH(char)

返回指定字符串的长度

例：SELECT ename,LENGTH(ename) FROM emp

伪表 :dual

伪表不是一张真是存在的表，当查询的内容与任何表数据无关时，可以使用伪表。

例：SELECT SYSDATE FROM dual

3:UPPER,LOWER,INITCAP

将字符串转换为大写，小写，首字母大写

例：SELECT UPPER('helloworld'),LOWER('HELLOWORLD'), INITCAP('HELLO WORLD') FROM dual

查看scott的信息?

SELECT ename,sal,job,deptno FROM emp WHERE ename=UPPER('scott')

4: TRIM, LTRIM, RTRIM

去除字符串两端的指定字符

例：

SELECT TRIM('e' FROM 'eeeliteee') FROM dual;

SELECT LTRIM('eddsdsesliteee','esd') FROM dual;

SELECT RTRIM('eeeliteddsdses','esd') FROM dual;

5: LPAD, RPAD补位函数

将指定字符串显示指定长度，当不足时，补充若干个指定字符以达到该长度

例：SELECT ename,RPAD(sal,5,'$') FROM emp

6:SUBSTR(char,m[,n])

截取指定字符串，从m处开始连续截取n个字符。n若不指定或超过实际可截取的长度，则都是截取到字符串末尾。m若为负数，则是从倒数位置开始截取数据库中下标都从1开始。

例：SELECT SUBSTR('thinking in java',-7,2) FROM dual

7:INSTR(char1,char2[,m[,n]])

查看char2在char1中的位置。m为从哪里开始查找，不写默认为1；n为第几次出现，不写默认为1。

例：SELECT INSTR('thinking in java','in',4,2) FROM dual

二、数字函数

1:ROUND(n,m)

四舍五入保留n小数点后m位；若m不写或0则表示保留到个位；若是负数则是保留到十位以上的数字。

例：

SELECT ROUND(45.678, 2) FROM DUAL

SELECT ROUND(45.678, 0) FROM DUAL

SELECT ROUND(55.678, -2) FROM DUAL

2:TRUNC函数

与ROUND参数意义一致，作用是截取数字。

例：

SELECT TRUNC(45.678, 2) FROM DUAL

SELECT TRUNC(45.678, 0) FROM DUAL

SELECT TRUNC(55.678, -1) FROM DUAL

3:MOD(m,n) 函数

求余数。n为0则直接返回m

例：SELECT ename,sal,MOD(sal,1000) FROM emp

4: CEIL, FLOOR

向上取整与向下取整

例：

SELECT CEIL(45.678) FROM DUAL; --46?

SELECT FLOOR(45.678) FROM DUAL;--45

日期类型：

两个常用关键字：

SYSDATE:对应数据库一个内置函数，返回一个DATE类型数据，表示当前系统时间

SYSTIMESTAMP:返回一个时间戳类型的当前系统时间。

例：

SELECT SYSDATE FROM dual

INSERT INTO emp (empno,ename,hiredate) VALUES (1,'jack',SYSDATE)

SELECT SYSTIMESTAMP FROM dual

日期转换函数

1:TO_DATE()

可以将给定字符串按照指定的日期格式转换为DATE类型值。

例：SELECT TO_DATE('1992-08-03 15:22:33', 'YYYY-MM-DD HH24:MI:SS') FROM dual

在日期格式字符串中凡不是英文，符号的其他字符都需要使用双引号括起来

例：SELECT TO_DATE('1992年08月03日15时22分33秒','YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒"') FROM dual

TO_CHAR函数

可以将DATE按照给定的日期格式转换为字符串

例：

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM dual

SELECT TO_CHAR(TO_DATE('58-08-03','RR-MM-DD'), 'YYYY-MM-DD') FROM dual

日期类型是可以计算的，对一个日期加减一个数字等同于加减天数；两个日期做减法，差为相差的天数。

例：

查看明天的日期?SELECT SYSDATE+1 FROM dual

查看每个员工入职至今多少天了? SELECT ename,SYSDATE-hiredate FROM emp

日期函数:

1:LAST_DAY(date)

返回给定日期所在月的月底日期

例：查看当月月底? SELECT LAST_DAY(SYSDATE) FROM dual

2:ADD_MONTHS(date,i)

对指定日期加上指定月；若i为负数，则是减去指定的月数。

例：查看每个员工的转正日期?SELECT ename,ADD_MONTHS(hiredate,3) FROM emp

3:MONTHS_BETWEEN(date1,date2)

计算两个指定日期之间相差的月

例：查看每个员工入职至今多少个月?

SELECT ename,MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp

4:NEXT_DAY(date,i)

返回给定日期第二天开始一周内指定周几的日期，i可以是1-7，分别表示周日，周一...周六

例：SELECT NEXT_DAY(SYSDATE,5) FROM dual

5:LEAST,GREATEST

求最小值与最大值；对于日期而言，最大值为最晚的日期；最小值为最早的日期。

例：SELECT LEAST(SYSDATE, TO_DATE('1998-08-06', 'YYYY-MM-DD') ) FROM DUAL;

6:EXTRACT函数

获取一个日期中指定时间分量的值

例：

查看今年是哪年？ SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual

查看1980年入职的员工?

SELECT ename,hiredateFROM emp WHERE EXTRACT(YEAR FROM hiredate)=1980

空值操作：

例：

CREATE TABLE student(

id NUMBER(4),

name CHAR(20),

gender CHAR(1) NOT NULL

);

INSERT INTO student VALUES(1000, '李莫愁', 'F');

INSERT INTO student VALUES(1001, '林平之', NULL);

INSERT INTO student(id, name) VALUES(1002, '张无忌');

更新NULL：

UPDATE student SET gender=null WHERE id=1000；

SELECT * FROM student；

判断是否为NULL：

判断要用IS NULL或IS NOT NULL

DELETE FROM student WHERE gender IS NULL

NULL的运算：

NULL与字符串连接等于什么都没做，NULL与数字运算结果还是NULL。

例：SELECT ename||NULL FROM emp

查看每个员工的收入(工资+绩效)？SELECT ename,sal,comm,sal+comm FROM emp

空值函数

1:NVL(arg1,arg2)

当arg1为NULL时，函数返回arg2的值，否则返回arg1自身

该函数意义:将NULL值替换为非NULL值

例：

查看每个员工的收入(工资+绩效)？SELECT ename,sal,comm,sal+NVL(comm,0) FROM emp

2:NVL2(arg1,arg2,arg3)

当arg1不为NULL时，函数返回arg2；若为NULL，则函数返回arg3。

例：

查看每个员工是否有绩效？（即：有绩效的显示"有绩效"，为NULL的则显示为"没有绩效"）

SELECT ename,comm,NVL2(comm,'有绩效','没有绩效') FROM Emp；

列别名：

当一个SELECT子句中包含函数或者表达式时，查询的结果集对应的该字段就是使用这个函数或者表达式作为字段名，可读性差。为此可以为这样的字段添加别名。

若别名使用双引号，那么别名就可以区分大小写并且包含空格。

SELECT ename,sal*12 salFROM emp

AND的优先级高于OR。

SELECT ename,job,sal FROM emp WHERE sal>1000 AND (job='SALESMAN' OR job='CLERK')

LIKE关键字：

LIKE用于模糊匹配字符串；它支持两个通配符比较: _:表示单一的一个字符

%:表示任意个字符(0-多个)

例：查看名字第二个字母是A的第四个字母是T的员工？

SELECT ename FROM emp WHERE ename LIKE '_A_T%'

IN(list)与NOT IN(list)：判断在列表中或不在列表中。IN和NOT IN常用在子查询的判断中。

例：查看职位是CLERK或SALESMAN的员工?

SELECT ename,job,deptno FROM emp WHERE job IN ('CLERK','SALESMAN')

BETWEEN...AND...：判断在一个范围内

例：查看工资在1500到3000之间的员工?

SELECT ename,job,sal FROM emp WHERE sal BETWEEN 1500 AND 3000

ANY(list) 和 ALL(list)：

ANY与ALL是配合>,>=,<,<=使用的。

>ANY(list):大于列表之一；

>ALL(list):大于列表所有；

<ANY(list):小于列表之一；

<ALL(list):小于列表所有常用在子查询中。

DISTINCT关键字：去除重复行

例：查看公司有哪些职位? SELECT DISTINCT job FROM emp

多字段去重，这几个字段值的组合没有重复行

例：SELECT DISTINCT job,deptno FROM emp

排序结果集：ORDER BY子句用来对结果集按照指定的字段排序。

排序有两种方式:升序(ASC):不写默认就是升序；降序(DESC):从大到小，需要单独指定。

ORDER BY子句必须写在DQL语句的最后一个子句上。

例：查看公司中工资的排名:SELECT ename,sal,deptno FROM emp ORDER BY sal DESC

多字段排序，有优先级，首先按照第一个字段排序，当第一个字段有重复值时才按照第二个字段排序，依次类推。

例：SELECT ename,deptno,sal FROM emp ORDER BY deptno DESC,sal DESC

NULL被认作为最大值

例：SELECT ename,comm FROM emp ORDER BY comm DESC

聚合函数：

聚合函数又称为:多行函数，分组函数。作用是对结果集的指定字段进行统计，然后得出一个结果.

例：

查看公司的最高工资与最低工资? SELECT MAX(sal),MIN(sal) FROM emp

查看公司的平均工资与工资总和? SELECT AVG(sal),SUM(sal) FROM emp

COUNT函数：是对记录数的统计

例：查看公司共多少人?SELECT COUNT(ename) FROM emp

聚合函数忽略NULL值：

SELECT SUM(comm),AVG(comm) FROM emp

SELECT AVG(NVL(comm,0)) FROM emp

分组:

用GROUP BY 子句；GROUP BY可以将结果集按照给定字段值一样的记录进行分组，配合聚合函数可以对不同的分组分别统计结果。

SELECT AVG(sal),deptno FROM emp GROUP BY deptno

SELECT MAX(sal),job FROM emp GROUP BY job

多字段分组：这些字段值都一样的记录看做一组

例：同部门，同职位的员工的平均工资?

SELECT AVG(sal),deptno,job FROM emp GROUP BY deptno,job

例：查看每个部门的最低工资是多少?（前提是该部门的最低工资要高于1000）

SELECT MIN(sal),deptno FROM emp WHERE MIN(sal)>1000 GROUP BY deptno

上面的SQL语句会报错: 此处不允许使用分组函数。

HAVING子句：

HAVING子句必须跟在GROUP BY子句之后，作用是添加过滤条件来过滤GROUP BY的分组，它可以将不满足条件的分组去除。HAVING子句可以使用聚合函数作为过滤条件。

例：SELECT MIN(sal),deptno FROM emp GROUP BY deptno HAVING MIN(sal)>1000

查看平均工资高于2000的部门的最低工资?

SELECT MIN(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>2000

查看最低工资高于1000的那些职位的平均工资?

SELECT AVG(sal),job FROM emp GROUP BY job HAVING MIN(sal)>1000

关联查询：

查询数据是从多张表中关联查询一个结果集关联查询的重点是添加连接条件。连接条件的作用是告知数据库表与表之间的数据是怎样对应的。关联查询通常都要添加连接条件，否则会产生笛卡尔积，通常是一个无意义的结果集。

例：查看每个员工的名字以及其所在部门的名字?

SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno

当关联查询的表中有同名字段，需要通过表名或表别名来指定该字段所属表。

在关联查询中过滤条件必须与连接条件同时成立。

查看RESEARCH部门的员工信息?

SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND d.dname='RESEARCH'

不加链接条件会产生笛卡尔积;

例：SELECT e.ename,d.dname FROM emp e,dept d;

内连接

内连接也是关联查询的一种

例：SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;

SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE d.dname='RESEARCH';

关联查询忽略不满足连接条件的记录

外链接：

外链接在关联查询时还可以将不满足连接条件的记录也查询出来。

外链接分为:左外连接，右外连接，全外连接

左外连接:以JOIN左侧表作为驱动表驱动表中所有数据都要列出来，那么当该表

某条记录不满足连接条件时，那么来自右侧表的字段值全部为NULL。

例：SELECT e.ename,d.dname FROM emp e LEFT|RIGHT|FULL OUTER JOIN dept d

ON e.deptno=d.deptno

自连接：

当当前表中的一条记录可以对应当前表的其他记录时，这种设计称为自连接。

例：查看每个员工以及其上司的名字?

SELECT e.ename,m.ename FROM emp e,emp m WHERE e.mgr=m.empno

SELECT e.ename,m.ename FROM emp e JOIN emp m ON e.mgr=m.empno

查看SMITH的上司是谁?他在哪个城市工作?

子查询：

子查询是一条查询语句，它是嵌套在其他SQL语句当中的，目的是为了外层查询提供

数据的。

例：查看谁的工资高于CLARK?

SELECT ename,sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='CLARK')

SELECT ename,job FROM emp WHERE job=(SELECT job FROM emp WHERE ename='SMITH')

查看谁的工资高于公司平均工资?

SELECT ename,sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp)

在DDL语句中使用子查询，可以根据一个查询结果集快速构建一张表

empno,ename,sal,job,deptno,dname,loc

CREATE TABLE employees AS SELECT e.empno,e.ename,e.sal,e.job, d.deptno,d.dname,d.loc

FROM emp e,dept d WHERE e.deptno=d.deptno(+);

SELECT * FROM employees

DML语句中使用子查询，将SMITH所在部门的员工工资上浮10%?

UPDATE emp SET sal=sal*1.1 WHERE deptno=(SELECT deptno FROM emp WHERE ename='SMITH');

SELECT * FROM emp;

DELETE FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='CLARK')

当子查询为多行单列时，那么在用作判断条件中时要搭配IN,ANY,ALL使用

例：查看与职位是SALESMAN同部门的其他职位员工?

SELECT ename,sal,deptno,job FROM emp WHERE deptno IN(SELECT deptno

FROM emp WHERE job='SALESMAN') AND job <> 'SALESMAN'

查看比职位是SALESMAN和CLERK工资都高的员工信息?

SELECT ename,sal FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE job IN('SALESMAN','CLERK'))

EXISTS关键字

EXISTS关键字后面跟一个子查询，当该子查询可以查询出至少一条记录时，EXISTS条件成立。

例：

查看有员工的部门有哪些?

SELECT d.deptno,d.dname,d.loc FROM dept d WHERE EXISTS( SELECT * FROM emp eWHERE e.deptno=d.deptno)

查看哪些人是别人的领导?

SELECT empno,ename,job,deptno FROM emp m WHERE EXISTS(SELECT * FROM emp e WHERE e.mgr=m.empno)

查看部门的最低薪水，前提是该部门的最低薪水要高于30号部门的最低薪水?

SELECT MIN(sal),deptno FROM emp GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal) FROM emp WHERE deptno=30)

在FROM子句中使用子查询通常是将子查询的结果当做一张表看待，基于该查询结果进行二次查询使用。

例：查看谁的工资高于其所在部门的平均工资?

SELECT e.ename,e.sal,e.deptno FROM emp e, (SELECT AVG(sal) avg_sal, deptno FROM emp GROUP BY deptno) t WHERE e.deptno=t.deptno AND e.sal>t.avg_sal

SELECT e.ename, e.sal, (SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) dname FROM emp e

分页查询：

分页查询就是将数据分段查询出来，一次只查询数据的一部分。这样做可以减少系统资源开销，减少数据量可以提高网络传输速度。

分页在不同的数据库中的SQL语句是不同的。ORACLE中提供了一个伪列:ROWNUM：

ROWNUM字段不存在于任何一张表中，但是每张表都可以查询该字段。该字段的值是结果集中每条记录的行号。ROWNUM字段的值是动态生成的，伴随查询过程。只要可以查询出一条记录，ROWNUM就会为该条记录生成行号，从1开始每次递增1。

由于ROWNUM是在查询表的过程中进行编号的，所以在使用ROWNUM对结果集编行号的查询过程中不要使用ROWNUM做大于1以上数字的判断，否则结果集没有任何数据。

SELECT * FROM (SELECT ROWNUM rn,empno,ename,sal,deptno FROM emp) WHERE rn BETWEEN 6 AND 10

查看公司工资排名的第6-10名：

SELECT * FROM(SELECT ROWNUM rn,t.* FROM(SELECT empno,ename,sal,deptno FROM emp ORDER BY sal DESC) t) WHERE rn BETWEEN 6 AND 10

SELECT * FROM(SELECT ROWNUM rn,t.* FROM(SELECT empno,ename,sal,deptno FROM emp

ORDER BY sal DESC) t WHERE ROWNUM <=10) WHERE rn >=6

pageSize(每页显示的条目数)； page(页数)； start:(page-1)*pageSize+1； end:pageSize*page

DECODE函数，可以实现分支效果。

SELECT ename, job, sal, DECODE(job, 'MANAGER', sal * 1.2, 'ANALYST', sal * 1.1, 'SALESMAN', sal * 1.05, sal) bonus FROM emp;

将MANAGER与ANALYST看做一组，其他职位看做另一组，分别统计两组人数?

SELECT COUNT(*), DECODE(job, 'MANAGER','VIP', 'ANALYST','VIP', 'OTHER') FROM emp

GROUP BY DECODE(job, 'MANAGER','VIP', 'ANALYST','VIP', 'OTHER')

排序函数:

排序函数允许将结果集按照指定字段分组在组内按照指定字段排序，然后该函数为

每组生成一个行号。

ROW_NUMBER(): 生成组内连续且唯一的数字

查看每个部门的工资排名?

SELECT ename,deptno,sal, ROW_NUMBER() OVER( PARTITION BY deptno ORDER BY sal DESC ) rank FROM emp

RANK函数:

生成组内不连续也不唯一的数字

SELECT ename,deptno,sal, RANK() OVER( PARTITION BY deptno ORDER BY sal DESC ) rank FROM emp

DENSE_RANK函数:

生成组内连续但不唯一的数字

SELECT ename,deptno,sal, DENSE_RANK() OVER( PARTITION BY deptno ORDER BY sal DESC) rank

FROM emp

SELECT year_id,month_id,day_id,sales_value

FROM sales_tab

ORDER BY year_id,month_id,day_id

查看每天的营业额?

SELECT year_id,month_id,

day_id,SUM(sales_value)

FROM sales_tab

GROUP BY year_id,month_id,day_id

ORDER BY year_id,month_id,day_id

每月的营业额？

SELECT year_id,month_id,

SUM(sales_value)

FROM sales_tab

GROUP BY year_id,month_id

ORDER BY year_id,month_id

每年的营业额?

SELECT year_id,SUM(sales_value)

FROM sales_tab

GROUP BY year_id

ORDER BY year_id

总共的营业额?

SELECT SUM(sales_value)

FROM sales_tab

SELECT year_id,month_id,

day_id,SUM(sales_value)

FROM sales_tab

GROUP BY year_id,month_id,day_id

UNION ALL

SELECT year_id,month_id,

NULL,SUM(sales_value)

FROM sales_tab

GROUP BY year_id,month_id

UNION ALL

SELECT year_id,NULL,

NULL,SUM(sales_value)

FROM sales_tab

GROUP BY year_id

UNION ALL

SELECT NULL,NULL,

NULL,SUM(sales_value)

FROM sales_tab

高级分组函数：

1、ROLLUP(a[,b,c...]) ：

GROUP BY ROLLUP(a,b,c)等同于

GROUP BY a,b,c

UNION ALL

GROUP BY a,b

UNION ALL

GROUP BY a

UNION ALL

全表

查看每天，每月，每年以及所有营业额?

SELECT year_id,month_id,

day_id,SUM(sales_value)

FROM sales_tab

GROUP BY

ROLLUP(year_id,month_id,day_id)

2:CUBE()：

CUBU的分组策略为每个参数的组合进行一次分组。

GROUP BY CUBE(a,b,c)等同于

a,b,c

a,b

b,c

a,c

全表

SELECT year_id,month_id,

day_id,SUM(sales_value)

FROM sales_tab

GROUP BY

CUBE(year_id,month_id,day_id)

ORDER BY year_id,month_id,day_id

GROUPING SETS()

该函数允许自行指定分组策略，然后将这些分组统计的结果并在一起。函数的每个参数

为一种分组方式。

例：查看每天与每月的营业额?

SELECT year_id,month_id, day_id,SUM(sales_value) FROM sales_tab GROUP BY GROUPING SETS( (year_id,month_id,day_id), (year_id,month_id)) ORDER BY year_id,month_id,day_id

视图：

视图也是数据库对象之一。在SQL语句中体现的角色与表一致，但视图只是对应一个查询语句的结果集。

创建视图:

CREATE VIEW v_emp_10

SELECT empno, ename, sal, deptno

FROM emp

WHERE deptno = 10;

视图也可以查看结构：

DESC v_emp_10;

SELECT * FROM v_emp_10

视图根据对应的SQL语句不通，分为:简单视图，复杂视图，连接视图；

连接视图算作复杂视图的一种。

当对应的子查询不含有函数，表达式，分组，去重，关联查询的视图称为简单视图，相反就是复杂视图。连接视图指子查询使用了关联查询。

视图对应的子查询的查询字段可以使用别名，那么该字段的名字就是这个别名。若字段含有函数或表达式，那么该字段必须指定别名。

例：CREATE OR REPLACE VIEW v_emp_10

SELECT empno id,ename name,sal*12 sal,deptno FROM emp WHERE deptno=10;

DESC v_emp_10;

SELECT * FROM v_emp_10;

对视图进行DML操作：

对视图进行DML就是对视图数据来源的基础表进行的操作。只能对简单视图进行DML操作。复杂视图不可以。

INSERT INTO v_emp_10

(empno,ename,sal,deptno)

VALUES

(1001,'JACK',2000,10)

SELECT * FROM v_emp_10

SELECT * FROM emp

UPDATE v_emp_10

SET sal=3000

WHERE empno=1001

DELETE FROM v_emp_10

WHERE empno=1001

对视图进行DML操作不当会污染基础表数据。

INSERT INTO v_emp_10

(empno,ename,sal,deptno)

VALUES

(1002,'JACKSON',2000,10)

SELECT * FROM emp

SELECT * FROM v_emp_10

UPDATE v_emp_10

SET deptno=20

可以为视图添加检查选项，来保证对视图进行DML操作时不会对基表数据污染。

WITH CHECK OPTION

当视图添加了检查选项后，视图要求对视图中数据进行DML操作后，视图必须对该记录可见，否则不允许操作。

CREATE OR REPLACE VIEW v_emp_10

SELECT empno,ename,sal,deptno

FROM emp

WHERE deptno=10

WITH CHECK OPTION

为视图添加只读选项:当一个视图添加了只读选项后，该视图不能进行DML操作。

WITH READ ONLY

CREATE OR REPLACE VIEW v_emp_10

SELECT empno,ename,sal,deptno

FROM emp

WHERE deptno=10

WITH READ ONLY

查看数据字典，有助于了解曾经创建过的数据库对象

SELECT * FROM user_objects

WHERE object_name LIKE '%_FANCQ'

SELECT * FROM user_views

SELECT * FROM user_tables

复杂视图:

复杂视图不能进行DML操作。

创建一个部门工资信息的视图:

CREATE VIEW v_dept_sal AS SELECT MIN(e.sal) min_sal,MAX(e.sal) max_sal,AVG(e.sal) avg_sal,SUM(e.sal) sum_Sal, d.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.deptno,d.dname

SELECT * FROM v_dept_sal

查看谁的工资高于自己所在部门平均工资?

SELECT e.ename,e.sal,e.deptno

FROM emp e,v_dept_sal v

WHERE e.deptno=v.deptno

AND e.sal>v.avg_sal

删除视图:(DROP VIEW v_emp_10);删除视图不会影响基表数据。

序列：

序列也是数据库对象之一。作用是根据指定的规则生成一系列数字。一般用于为表的每一条记录的主键字段提供值。

创建序列: CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1

序列支持两个伪列:

NEXTVAL:获取序列下一个数字，序列会根据序列最后生成的数字加上步进来得到。NEXTVAL会导致序列发生步进，序列是不能回退的。

CURRVAL:获取序列最后一次生成的数字。需要注意的是，新创建的序列必须在使用NEXTVAL生成一个数字后才可以使用CURRVAL。

SELECT seq_emp_id.NEXTVAL FROM dual;

SELECT seq_emp_id.CURRVAL FROM dual;

SELECT * FROM emp;

INSERT INTO emp (empno,ename,sal,job,deptno) VALUES (seq_emp_id.NEXTVAL,'ROSE', 5000,'CLERK',10);

删除序列：DROP SEQUENCE seq_emp_id

索引：

索引是数据库对象之一。索引是加快查询效率的机制。索引的建立以及应用是数据库自行完成的。

约束：

1、唯一性约束：

唯一性约束要求该字段每条记录的值不能重复，NULL除外。

例：

CREATE TABLE employees1 (

eid NUMBER(6) UNIQUE,

name VARCHAR2(30),

email VARCHAR2(50),

salary NUMBER(7, 2),

hiredate DATE,

CONSTRAINT employees1_email_uk UNIQUE(email)

);

SELECT * FROM employees1;

INSERT INTO employees1 (eid,name,email) VALUES (NULL,'JACK',NULL)

2、主键约束：

主键约束要求该字段的值为空且唯一。主键约束只能在一张表的一个字段上建立。

主键:使用该字段的值可以唯一定位表中的一条记录。

CREATE TABLE employees2 (

eid NUMBER(6) PRIMARY KEY,

name VARCHAR2(30),

email VARCHAR2(50),

salary NUMBER(7, 2),

hiredate DATE

);

INSERT INTO employees2

(eid,name)

VALUES

(NULL,'JACK')

csdn_SirLiu

关注

4
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
通过Oracle数据库，学SQL语句。

Oracle数据库一、数据库简介1、数据库（Database，简称DB）：是按照数据结构来组织、储存和管理数据的仓库。数据库管理系统（简称DBMS）：管理数据库的软件。数据库对象: 表，视图，索引，序列。伪表（dual）：伪表不是一张真是存在的表，当查询的内容与任何表数据无关时，可以使用伪表。（伪表只在oracle数据库中用）例：SELECT SYSDATE FROM d
复制链接

扫一扫

专栏目录