Oracle学习笔记1—SELECT查询

1.Oracle是个啥？
答：Oracle是一个大型的关系数据库；
2.啥是关系数据库？
答：用表保存数据，用表描述数据与数据之间关系的数据库；
3.数据库中对表的操作有四种：增，删，改，查。
4.Oracle导入脚本命令:@脚本路；

一、基本的SELECT语句

1. 基本的查询

SELECT 列名[,列名,……]|*FROM 表名

SELECT 5/2FROM DUAL;

DUAL是Oracle提供的虚拟表，一般在数据库运算或者查看时间时使用，因为我们并没有对数据库中的任意一个表进行操作，然而Oracle不允许省略FROM，所以用DUAL来代替。

需要注意的一些事项：

• Oracle数据中的SELECT语句，FROM子句不能省略。
• SQL语句不区分大小写。
• SQL语句可以写成一行或多行。
• 只能直接使用半角标点符号。
• 工作中不建议使用*，查询效率低。

例：查询employees表中所有员工的last_name,salary

SELECT LAST_NAME,SALARYFROM EMPLOYEES;

2.算术运算符：+，-，*，/

• Oracle中+只有加法运算的功能，而没有连接符的功能。

例：查询employees表中所有员工的last_name,salary,年薪

SELECT LAST_NAME,SALARY,SALARY*12FROM EMPLOYEES;

3.列别名
给列起别名有两种方式：

1. 列名 列别名
2. 列名 AS列别名（建议使用）

注意：

1. Oracle会自动将英文列名变为大写。
2. 当列别名区分大小写时，或者列别名中包含了特殊字符，或者列别名为关键字时，需要将列别名放在一对双引号中。
3. 关系数据库中双引号通常用于表示别名。
4. 关系数据库中单引号通常用于表示字符串。
5. WHERE子句中不能使用列别名

例：

SELECT LAST_NAME "USER",SALARY,SALARY*12 AS "Sal"FROM EMPLOYEES;

4.连接符
连接符：||

例：

SELECT FIRST_NAME||'.'||LAST_NAME AS NAMEFROM EMPLOYEES;

5.DISTINCT关键字
作用：屏蔽查询结果中重复的数据。（去重）。
例：

SELECT DISTINCT JOB_ID,SALARYFROM EMPLOYEES;

• 通常情况下，一个SELECT语句中只能出现一个DISTINCT关键字。
• 通常情况下，DISTINCT必须出现在SELECT与第一个列之间。

6.过滤与排序
语法：WHERE子句

SELECT       3FROM         1[WHERE 条件]  2 --条件结果的类型一定为boolean类型

(1)条件为数字类型

例：查询employees表中在50号部门工作的员工的last_name,department_id

SELECT LAST_NAME,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID=50;

(2)条件为字符串类型:

值必须放在一对单引号中，并且英文区分大小写。

例：查询employees表中last_name是fay的员工的last_name,hire_date

SELECT LAST_NAME,HIRE_DATEFROM EMPLOYEESWHERE LAST_NAME='Fay';

(3)条件为日期类型

值必须放在一对单引号中，格式必须为Oracle默认的日期格式(DD-MON-RR)。

例：

SELECT LAST_NAME,JOB_ID,HIRE_DATEFROM EMPLOYEESWHERE HIRE_DATE='7-6月-94';

7.比较运算符:>,>=,<,<=,=,!=,<>

8.特殊比较运算符
(1)BETWEEN..AND..:查询指定范围内的数据(包含边界值)

例1：查询employees表中工资在6000-12000之间员工的last_name,salary

SELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY BETWEEN 6000 AND 12000;

例2：查询employees表中97-99年入职的员工，显示：last_name,hire_date

SELECT LAST_NAME,HIRE_DATEFROM EMPLOYEESWHERE HIRE_DATE BETWEEN '1-1月-97' AND '31-12月-99';

(2)IN(值列表)：与任意一个值匹配，只能判断是否相等。

例：查询employees表中在20或50号部门工作员工的last_name,department_id

SELECT LAST_NAME,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID IN (20,50);

9.LIKE:模糊查询

1. %:任意长度的任意字符。
2. _:一个长度的任意字符。

例1：查询employees表中last_name包含a的员工的last_name

SELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE '%a%';

例2：

SELECT LAST_NAMEFROM EMPLOYEESWHERE LAST_NAME LIKE '%n_';

使用ESCAPE声明一个符号，通知LIKE查询内容中此符号后面的一个_或%不再是转义字符，而是查询的内容。

例3：查询employees表中job_id包含A_的员工的last_name,job_id

SELECT LAST_NAME,JOB_IDFROM EMPLOYEESWHERE JOB_ID LIKE '%A/_%' ESCAPE '/';

10.IS NULL/IS NOT NULL

NULL:关系数据库中需要提供一个与类型无关，表示未知或不确定的值。

例：

SELECT LAST_NAME,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID IS NULL;

11.逻辑运算符：AND,OR,NOT

例：查询employees表中工资在6000-12000之间员工的last_name,salary

SELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY>=6000 AND SALARY<=12000;

AND的优先级大于OR

12.排序
语法：ORDER BY子句

SELECT        3FROM          1[WHERE]       2[ORDER BY 列] 4

通常情况下，ORDER BY子句出现在SELECT语句的最后

• 升序：使用ASC关键字，默认为升序排序。
• 降序：使用DESC关键字。

ORDER BY 中可以使用列别名

• Oracle认为NULL是最大的。
• SQL Server认为NULL是最小的。
• NULLS FIRST/NULLS LAST:在不影响排序规则的前提下，将NULL值放在结果的最前/最后面。

13.单行函数
（1）字符类型单行函数

• LOWER(列名)–变为小写

• UPPER(列名)–变为大写

• SUBSTR(列名，pos)——从指定的位置开始截取字符串直到字符的末尾

• SUBSTR(列名，pos，length)——从指定的位置开始截取指定长度的字符串
• SUBSTR(列名，负数)——从倒数第N位开始截取字符串，直到字符串的末尾
• SUBSTR(列名，负数，length)——从倒数第N位开始截取指定长度的字符串
  INSTR():在第一个参数中查找第二个参数首次出现的位置，没找到返回0
• LENGTH(‘char’)——获得字符数
• LENGTHB(‘char’)——获得字节数
• TRIM(’ ABC ABC ‘)——去掉字符串两端的空格
• TRIM(‘A’ FROM ‘AAAABACAAAA’)——去掉字符串两端指定的字符
• LPAD()/RPAD():显示第一个参数的值，使用第二个参数设置第一个参数显示的长度，如果第一个参数长度不足，使用第三个参数在第一个参数的左/右面补齐长度。

(2)数字类型单行函数

• ROUND():四舍五入
  
• TRUNC():截取数字
  
• MOD():取余数

(3)日期类型单行函数

• SYSDATE:获得数据库服务器当前日期+时间
• Oracle中日期计算的方式
  1.日期+天数=日期
  2.日期-天数=日期
  3.日期-日期=天数
  4.日期不能加日期
• MONTHS_BETWEEN(date1,date2):获得两个日期相差的月数
• ADD_MONTHS(date1,date2):在指定的日期上加上N个月

(4)类型转换单行函数

1. 自动转换：效率低，不建议使用。
2. 手动转换：TO_CHAR(),TO_DATE(),TO_NUMBER()
3. 字符串可以与日期相互转换
4. 字符串可以与数字相互转换
5. 日期与数字不能相互转换

1.TO_CHAR(D,F):将日期类型D根据模板F转换成日期类型的值

2.TO_CHAR(N,F):将数字类型N根据模板F转换为字符类型的值

大写字母L表示本地的金钱符号。

3.TO_NUMBER(C,F):根据模板F将字符类型C转换为数字类型的值

14.通用单行函数

1. NVL():当第一个参数不为NULL，返回第一个参数。当第一个参数为NULL，返回第二个参数。两个参数的类型必须保持一致。
   

关系数据库中当算术运算中出现了NULL，结果一定为NULL。

1. NVL2()：当第一个参数不为NULL，返回第二个参数。当第一个参数为NULL，返回第三个参数。
   

15.分支选择：CASE表达式，DECODE()函数

1. CASE表达式：结果的类型必须保持一致。
2. DECODE():只能判断是否相等

二、表与常用数据类型

1.创建表基本语法

CREATE TABLE 表名(列名 数据类型[(长度)] [约束],列名 数据类型[(长度)] [约束],……);

2.Oracle中常用的数据类型

(1)数字类型:NUMBER

• 整数:NUMBER(6),长度可以省略，如果省略则默认为38位的数字。
• 浮点数:NUMBER(8,2),长度不能省略，第一个参数表示整个数字的长度，第二个参数表示小数位。

(2)字符类型:长度不能省略，默认单位字节

• CHAR:保存固定长度的字符串，如果内容长度不足，使用空格补齐长度。
• VARCHAR2:保存可变长度的字符串，如果内容长度不足，不使用空格补齐长度。
• VARCHAR:目前为止VARCHAR与VARCHAR2功能相同，但以后版本的Oracle中的VARCHAR可能会改为其它数据类型。
  (3)日期类型：不能设置长度
• DATE:日期+时间

例：创建一个表

CREATE TABLE TEST1(T_ID NUMBER(5),T_NAME VARCHAR2(20 CHAR),T_SEX CHAR(1 CHAR),T_BIRTH DATE);

3.数据操作
(1)添加数据：INSERT INTO语句

• 向表中添加一行新数据，并向新行中所有列赋值。
  格式:INSERT INTO 表名 VALUES(值,值,……);

(2)向表中添加一行新数据，并向新行中指定列赋值。

• INSERT INTO 表名(列名,列名,……) VALUES(值,值,……);

(3)将其它表中的数据导入到指定的表中。

• INSERT INTO 表名[(列名,列名,……)] SELECT语句;

(4)修改数据：UPDATE语句

• UPDATE 表名 SET 列名=值[,列名=值,……] [WHERE 条件];

(5)删除数据:DELETE语句

• DELETE [FROM] 表名 [WHERE 条件];

4.约束

1. 主键约束:PRIMARY KEY,通过主键可以在表中找到唯一的一行数据。不能为NULL，不能重复。
2. 非空约束:NOT NULL，不能为空。
3. 检查约束:CHECK,当向列中添加数据或修改列中的数据时，检查数据是否合法。
4. 默认值:DEFAULT，当没有向列中添加数据时，默认向列中添加的数据。
5. 唯一约束:UNIQUE,可以为NULL(Oracle认为NULL可以重复，SQL Server认为NULL也不能重复)，不能重复。
6. 外键约束:

CONSTRAINT 外键名  FOREIGN KEY(列名) REFERENCES 主表名（主表中的列名）

主外键添加数据：先添加主表，再添加子表；

主外键删除数据：先删除子表，再删除主表。
联合主键：一个表只能有一个主键，但一个主键可以由多个列组成。

5.SQL语句的分类

1. DML(数据操作语言):SELECT,INSERT,UPDATE,DELETE
2. DDL(数据定义语言):CREATE,DROP,ALTER,TRUNCATE等
3. DCL(数据控制语言):COMMIT,ROLLBACK,SAVEPOINT等

6.事务
含义：单位时间内一系列的操作，这些要么全部成功，要么全部失败。

1. COMMIT:提交事务，将对数据的操作保存到数据库中。
2. ROLLBACK:回退事务，将数据还原到最初或最后一次提交的状态。

(1)事务的组成

1. 可以有多个DML语句
2. 只能有一个DDL或DCL语句。

(2)注意：

1. 当异常结束Oracle会话时，Oracle会自动回退事务。
2. 当正常结束Oracle会话时，Oracle会自动提交事务。
3. 当执行DDL语句时，会自动提交事务。