Oracle数据库

一：数据库管理系统

关系型数据库 Oracle ， MySQL ， SQL Server ， DB2 ， SQLite

非关系型数据库 MongoDB ， Redis

二：SQL

SQL (Structured Query Language) 的定义：

SQL 结构化查询语言，是一种数据库查询和程序设计语言， 用于存取数据以及查询、更新和管理关系数据库系统。 语句以分号结尾。

SQL 的分类

1. 数据查询语言 DQL （ Data Query Language ）

数据检索语句，用于从表中获取数据

SELECT < 字段名 > FROM < 表或视图名 > WHERE < 查询条件 >

2. 数据操纵语言 DML （ Data Manipulation Language ）

主要处理数据库中的数据内容。允许用户对数据库中的数据进行查询 ，插入，更新和删

除等操作

INSERT ， UPDATE ， DELETE

3. 数据定义语言 DDL （ Data Definition Language ）

创建数据库对象，修改数据库对象和删除数据库对象

CREATE ， ALTER ， DROP ， TRUNCATE

注：每一条 DDL 语句执行后，都将提交当前事务

4. 数据库控制语言 DCL （ Data Control Language ）

修改数据库结构的操作权限

GRANT ， REVOKE

数据库管理员操作

SQL 语句规范

1. SQL 关键字、对象名、和列名不区分大小写

2. 字符值和日期值要区分大小写

3. SQL*Plus 中的 SQL 语句以分号（ ; ）结束

三. 数据查询语言 DQL

3.1基础查询

查询所有列的记录----    select  * from 表名 ;

查询指定列的记录----  selct  列名 1, 列名 2, ... from 表名 ;

列数值计算

-- 从员工表 s_emp 中查询所有员工的用户名 userid ，工资 salary 和年薪 (12 个月的工资）

select  userid, salary, salary * 12 from  s_emp;

列字符串拼接

select concat(order_no, ' ', money) as info from shop_goods_order;

列的别名

select 列名1 as 别名1, 列名2 as 别名2, ... from 表名;

-- 列名后可以跟 AS 别名 来设置别名， AS 可以省略

-- 别名可用字母(常用），数字，空格和中文

去除重复记录

select distinct 列名 from 表名;

3.2 条件查询

select *|列名 from 表名 where 条件表达式;

3.2.1 精准查询

比较运算符

= ， > ， >= ， < ， <= ， != ， <> ， ^=

数值比较 比较值为数值类型不需要引号

字符串比较 比较值为字符串类型必须用 单引号 括起来

闭区间连续范围比较

BETWEEN 下边界 AND 上边界

成员操作符 多个值

IN ( 值 1, 值 2, ... 值 N) NOT IN( 值 1, 值 2, ... 值 N)

逻辑运算符

NOT 表示否定 AND 表示并且 OR 表示或者 优先级 NOT > AND > OR

空值比较

IS NULL 表示为空 IS NOT NULL 表示不为空

3.2.2 模糊查询

操作符 LIKE

select *|列名 from 表名 where 列名 like 字符串;

-- 通配符百分号 % 匹配 0 到多个任意字符

-- 通配符下划线 _ 匹配 1 个任意字符

3.3 排序查询

select * from 表名 order by 列名或列的别名或列的位置编号 asc 或 desc;

eg: select * from `student`.`shop_goods_order` where `freight` = '12' order by `freight` desc limit 0,1000;

3.4 分组查询

3.4.1 分组函数（聚合函数）

分组函数又称为统计函数，用于将查询结果分成几个组，出现在 SELECT 的列和 HAVING 子句中

COUNT 统计查询出的结果数量

AVG 求查询结果中某列的平均值

MAX 求查询结果中某列的最大值

MIN 求查询结果中某列的最小值

SUM 求查询结果中某列的总和--select sum(money) from shop_goods_order where id>=1 and id<=2;

分组函数 AVG 、 SUM 只能统计数值， MAX 、 MIN 可以统计字符串

分组函数 COUNT 统计所有类型，不统计空值

COUNT(*) 和 COUNT(1) 统计所有列，返回行数

COUNT( 字段名 ) 统计某字段

3.4.2 分组查询

GROUP BY 子句将查询结果细分为更小的组， GROUP BY 子句后只能跟字段名，该字段叫分组字

段

没有 GROUP BY 子句时， SELECT 子句可以出现分组函数，但不允许同时有其他字段

有 GROUP BY 子句时， SELECT 子句只允许出现分组函数和分组字段

在 GROUP BY 子句中若出现多列时是按照多列组合值进行分组

-- 从员工表查询每个部门的最高工资

SELECT dept_id, MAX ( salary ) FROM s_emp GROUP BY dept_id; -- SELECT 后允许出现

分组字段（ GROUP BY 表达式）

SELECT id, MAX ( salary ) FROM s_emp GROUP BY dept_id; -- 报错：不是 GROUP BY

表达式

3.4.3 分组条件过滤

HAVING 子句用来对 GROUP BY 的结果集进行条件过滤， 只允许出现分组函数和分组字段

WHERE 子句中不允许出现分组函数 ， HAVING 子句不允许使用列的别名

-- 从员工表查询最高工资大于 1400 的部门，输出信息包括部门编号，最高工资

SELECT dept_id, MAX ( salary ) FROM s_emp GROUP BY dept_id HAVING MAX ( salary ) >

1400 ;

-- 报错：此处不允许使用分组函数

SELECT dept_id, MAX ( salary ) FROM s_emp WHERE MAX ( salary ) > 1400 ;

-- HAVING 允许使用分组字段，效果和分组字段条件放在 WHERE 子句相同

SELECT dept_id, MAX ( salary ) FROM s_emp GROUP BY dept_id HAVING dept_id > 10 ;

SELECT dept_id, MAX ( salary ) FROM s_emp WHERE dept_id > 10 GROUP BY dept_id;

-- HAVING 不能出现非分组字段

-- 报错：不是 GROUP BY 表达式

SELECT dept_id, MAX ( salary ) FROM s_emp GROUP BY dept_id HAVING id > 10 ;

-- HAVING 不能单独使用，必须配合 GROUP BY

-- 报错：不是 GROUP BY 表达式

SELECT dept_id FROM s_emp HAVING dept_id > 10 ;

3.5 多表查询

3.5.2 等值连接

判断某两列是否相等，从而建立连接，通常是主键与外键进行连接

采用表名加上列名，来标示列名，从而防止出现列名相同的情况

表可以起别名，表的别名前不能加 AS 关键字。

一旦给表起了别名，标识列时只能通过表的别名标识，而不能通过原名标识。

-- 查询所有员工的名字，职位和部门名称

/*

数据源：名字和职位来自员工表，部门名称来自部门表

关联关系：部门编号 员工表的 dept_id 和部门表的 id

*/

SELECT first_name, title, name FROM s_emp, s_dept WHERE s_emp.dept_id =

s_dept.id;

-- 列名前加表名，明确是哪一张表的列

SELECT s_emp.id, s_emp.first_name, s_emp.title, s_dept.name FROM s_emp,

s_dept WHERE s_emp.dept_id = s_dept.id;

-- 表可以使用别名，不能用 AS ，一旦给表起了别名，必须使用别名，不能使用原表名

SELECT A.id, A.first_name, A.title, B.name FROM s_emp A, s_dept B WHERE

A.dept_id = B.id;

-- 查询 41 部门员工的名字，职位和部门名称

SELECT A.id, A.first_name, A.title, B.name

FROM s_emp A, s_dept B

WHERE A.dept_id = B.id

AND A.dept_id = 41 ;

3.5.3 内连接

INNER JOIN 内连接

用 INNER JOIN 连接表，关联条件写在 ON 后面， INNER 可省略

从多个表中返回满足关联条件的所有行

-- 内连接 INNER JOIN

-- 查询所有员工的名字，职位和部门名称

/*

数据源：名字和职位来自员工表，部门名称来自部门表

关联关系：部门编号 员工表的 dept_id 和部门表的 id

*/

SELECT A.id, A.first_name, A.title, B.name

FROM s_emp A

INNER JOIN s_dept B

ON A.dept_id = B.id

WHERE A.dept_id = 41 ;

-- 查询所有员工的名字，部门名称和地区名称

/*

数据源：员工名字来自 s_emp ，部门名称来自 s_dept ，地区名称来自 s_region

关联关系： s_emp.dept_id 和 s_dept.id ， s_dept_region_id 和 s_region.id

*/

-- 内连接 INNER 可以省略

SELECT a.first_name 名字 , b.name 部门名称 , c.name 地区名称

FROM s_emp a

JOIN s_dept b

ON a.dept_id = b.id

JOIN s_region c

ON b.region_id = c.id;

3.5.4 左外连接

LEFT OUTER JOIN 左外连接

从左表返回所有的行，即使右表中没有匹配。如果右表中没有匹配，则结果为 NULL 。 OUTER 可省略

-- 左外连接

-- 查询所有员工的名字，对应的客户的名字，即使该员工没有客户

SELECT * FROM s_customer;

SELECT * FROM s_emp;

/*

数据源：员工名字来自 s_emp ，客户名字来自 s_customer

关联条件： s_emp.id 和 s_customer.sales_rep_id

*/

-- 标准 SQL 语法

SELECT a.first_name, b.name

FROM s_emp a

LEFT OUTER JOIN s_customer b

ON a.id = b.sales_rep_id;

-- Oracle 特殊的左外连接语法

SELECT a.first_name, b.name

FROM s_emp a, s_customer b

WHERE a.id = b.sales_rep_id ( + ) ;

3.5.5 右外连接

RIGHT OUTER JOIN 右外连接

从右表返回所有的行，即使左表中没有匹配。如果左表中没有匹配，则结果为 NULL 。 OUTER 可省略。

-- 右外连接

-- 查询所有员工的名字，对应的客户的名字，即使该客户没有对应的销售

SELECT a.first_name, b.name

FROM s_emp a

RIGHT JOIN s_customer b -- OUTER 可以省略

ON a.id = b.sales_rep_id;

-- Oracle 特殊的右外连接语法

SELECT a.first_name, b.name

FROM s_emp a, s_customer b

WHERE a.id ( + ) = b.sales_rep_id;

3.5.6 全外连接

FULL OUTER JOIN 全外连接

只要左表和右表其中一个表中存在匹配，则返回行。 OUTER 可省略。

-- 全连接

-- 查询所有员工的名字，对应的客户的名字，即使该客户没有对应的销售或员工没有客户

SELECT a.first_name, b.name

FROM s_emp a

FULL JOIN s_customer b

ON a.id = b.sales_rep_id;

3.6 子查询（嵌套查询）

子查询是一个完整的 SELECT 语句，可以拥有 GROUP BY 、 HAVING 子句，可以使用组函数，可以

从多个表查询结果

子查询一般不包含 ORDER BY 子句，除非需要进行排名前几位的查询

如果子查询的外围语句是 SELECT 语句，则子查询内容必须用小括号界定，子查询里面不用分号结尾

子查询的作用：

1. 方便理解

2. 实现更复杂的查询

3. 提高查询效率

当直接查询某些数据很困难或办不到时，可以通过从 “ 查询结果集 ” 中再次提取数据集来实现复合查

询。

子查询的位置通常出现在 WHERE 子句、 HAVING 子句、 FROM 子句

3.6.1 WHERE 子句单行子查询

子查询返回结果是单行单列，使用单行比较符 = != > >= < <=

示例

-- 查询最低工资员工的名字，职位和工资

/*

1. 查询最低工资

2. 以最低工资为条件查询员工信息

*/

SELECT MIN ( salary ) FROM s_emp;

SELECT first_name, title, salary FROM s_emp WHERE salary = 750 ;

-- 合并

SELECT first_name, title, salary FROM s_emp WHERE salary = ( SELECT

MIN ( salary ) FROM s_emp ) ;

3.6.2 WHERE 子句多行子查询

子查询返回多行单列，使用多行比较符 IN ANY ALL

IN 等于列表中的任意一个

ALL

和子查询返回的所有值进行比较（小于运算符 “<” 时，小于子查询集合里最小的值；大于运

算符 ”>“ 时，大于子查询集合里最大的值）

ANY

和子查询返回的任意一个值进行比较（小于运算符 “<” 时，小于子查询集合里最大的值；大

于运算符 ”>“ 时，大于子查询集合里最小的值）

/*

1. 查询部门名称是 Sales 有哪些部门编号，从 s_dept 表查询

2. 以部门编号列表为条件查询员工的名字，职位

*/

SELECT id FROM s_dept WHERE LOWER ( name ) = 'sales' ;

SELECT first_name, title FROM s_emp WHERE dept_id IN ( 31 , 32 , 33 , 34 , 35 ) ;

-- 合并

SELECT first_name, title

FROM s_emp

WHERE dept_id IN ( SELECT id FROM s_dept WHERE LOWER ( name ) = 'sales' ) ;

-- 结果区别在哪里？

SELECT FIRST_NAME,TITLE

FROM S_EMP

WHERE DEPT_ID < ANY ( SELECT ID

FROM S_DEPT

WHERE LOWER ( NAME ) = 'sales' ) ;

SELECT LAST_NAME,FIRST_NAME,TITLE,DEPT_ID

FROM S_EMP

WHERE DEPT_ID < ALL ( SELECT ID

FROM S_DEPT

WHERE LOWER ( NAME ) = 'sales' ) ;

3.6.3 HAVING 子句子查询

子查询返回结果是单行单列或多行单列，也可以使用在 HAVING 子句

-- 查询部门平均工资比公司平均工资高的部门编号

/*

1. 查询公司平均工资

2. 在 HAVING 子句比较

*/

SELECT AVG ( salary ) FROM s_emp;

SELECT dept_id FROM s_emp GROUP BY dept_id HAVING AVG ( salary ) > ( SELECT

AVG ( salary ) FROM s_emp ) ;

3.6.4 FROM 子句子查询

子查询返回结果是多行多列，可以使用在 FROM 子句作为数据源

-- 查询工资从高到低排名前 5 的员工名字，职位和工资

/*

1. 按工资从高到低排序产生一张表

2. 从这张表查前 5 条记录

*/

SELECT first_name, title, salary FROM s_emp ORDER BY salary DESC ;

SELECT * FROM ( SELECT first_name, title, salary FROM s_emp ORDER BY salary

DESC ) WHERE ROWNUM <= 5 ;

3.6.5 子查询和多表查询结合

多行多列的子查询结果可以作为多表查询的数据源

-- 查询出每个部门名称，最高工资，人数

/*

1. 查询每个部门的部门编号，最高工资和人数

2. 以上一步作为 1 个数据源，加上部门表作为另一个数据源，以部门编号作为关联条件查询

*/

-- 子查询产生的表如果列有分组函数必须加别名

SELECT dept_id, MAX ( salary ) , COUNT ( id ) FROM s_emp GROUP BY dept_id;

select t.name,s.maxsal,s.counid

from s_dept t,

( SELECT dept_id , MAX ( salary ) maxsal, COUNT ( id ) counid FROM s_emp

GROUP BY dept_id ) s

where t.id = s.dept_id;

3.6.6 子查询嵌套

子查询可以多层嵌套

-- 查询工资比 Henry 所在部门平均工资高的所有员工的名字，职位和工资

/*

1. 查询 Henry 所在的部门编号

2. 查询该部门的平均工资

3. 查询符合条件的所有员工的名字，职位和工资

*/

SELECT dept_id FROM s_emp WHERE first_name = 'Henry' ; -- 第 1 步

SELECT AVG ( salary ) FROM s_emp WHERE dept_id = 32 ; -- 第 2 步

SELECT first_name, title, salary FROM s_emp WHERE salary > 1490 ; -- 第 3 步

-- 合并

SELECT first_name, title, salary FROM s_emp WHERE salary >

( SELECT AVG ( salary ) FROM s_emp WHERE dept_id = ( SELECT dept_id FROM s_emp

WHERE first_name = 'Henry' )) ;