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'));