oracle基本SQL查询（上）

oracle数据库

oracle数据库的概念

平常所说的oracle 或 oracle 数据库指的是oracle数据库管理系统。oracle数据库管理系统是管理数据库访问的计算机软件，它由oracle数据库 和 oracle 实例构成。

oracle数据库：一个相关的操作系统文件（即存储在计算机硬盘的文件）集合，这些文件组织在一起，成为一个逻辑整体，即为oracle数据库。

oracle用它来存储和管理相关的信息。oracle数据库必须要与内存里实例合作，才能对外提供数据管理服务。

dual 是一个“‘伪表’，可以用来测试函数和表达式

数据库管理

oracle客户端 {服务器连接公司的}

工具 ：plsqldev.exe

sql 归类

SQL语句分为三种类型：

DML : 数据操纵语言 CRUD

DDL : 数据定义语言

DCL : 数据控制语言

SQL大小写不敏感

DML

DML用于查询与修改数据记录，包括如下SQL语句：

insert ： 添加数据到数据库中

update ： 修改数据库中的数据

delete ： 删除数据库中的数据

select ： 选择（查询）数据

DDL

create table ： 创建数据库表

alter table : 更改表结构，添加，删除，修改列长度

drop table : 删除表

create index : 在表上建立索引

drop index : 删除索引

DCL

crant : 授权访问权限

revoke ： 撤销访问权限

commit ： 提交事务处理

rollback ： 事务处理回退

savepoint ： 设置保存点

lock ： 对数据库的特定部分进行锁定

SQL 查

select

desc + 表名： 输出表结构（列名+数据类型）

select 列名1 , 列名2 , 列名n from 表名 ：查询表中多列数据（查询表中全部数据列名改为： *）

算数运算符

数字(number)和日期(date)使用的算数运算符。+ - * /

别名

法一：select 列名1 别名1 , 列名2 别名2 ,列名3 别名3 from 表名 ；

法二：select 列名1 as 别名1 , 列名2 as 别名2 ,列名3 as 别名3 from 表名 ；

法三：select 列名1 “别名1” , 列名2 “别名1” from 表名 ；–用途：双引号内字符大小写不变

在Oracle里面只有这里用了双引号，其他位置不用。

例如：select menpaihao 门牌号,yezhuxingming 姓名,zhiye 职业 from yezhuxinxi;

输出：

门牌号	姓名	职业
数据	数据	数据
数据	数据	数据

连接符

把列与列，列与字符连接在一起。

用 || 表示。

可以用来 ‘合成’ 列。

格式：（MySQL不行）

select 列名1 || ‘要连接的字符’ || 列名2 as from 表名;

连接符+别名格式：

select 列名1 as 别名1 || ‘要连接的字符’ || 列名2 as 别名2 from 表名;

扩展：

mysql 的连接函数concat（）可以多个字符串连接，而在oracle里面只能两个字符串连接

select concat(‘drop table ‘,t.table_name,’ ;’) col from information_schema.tables t where t.table_schema=‘account_check’;

字符串

字符串可以是select 列表中的一个字符，数字，日期。

日期和字符只能在单引号中出现。

每当返回一行时，字符串被输出一次。

去重：distinct

select distinct 列名 from 表名;

SQL语句 与 SQL * plus语句

SQL	SQL * Plus
一种语言	一种环境
ANSI标准	Oracle的特性之一
关键字不能缩写	关键字可以缩写
使用语句控制数据库中的表的定义信息和表中的数据	命令不能改变数据库中的数据的值
	集中运行

总结

过滤和排序数据

筛选条件：where

select 列名 from 表名 where 列名（1~n其中一个） 判定条件 ;

例如：

select * from yezhuxinxi where menpaihao > 1003 ; – = <

日期筛选 date

日期格式： 日-月-年 例如：21年9月11号 书写 ：11-9月-2021

select last_date from employees where hire_date = ‘11-9月-2021’；–不管系统怎么显示，查询格式都是这个

比较运算

操作符	含义
=	等于（不是 ==）
>	大于
>=	大于，等于
<	小于
<=	小于，等于
<>	不等于（也可以是 !=）

赋值使用 := 符号。

其他比较运算

操作符	含义
between … and …	在两值之间（包含边界）
in(set)	等于值列表中的一个 in(选项1，选项2，选项n)
like	模糊查询
is null	空值
or	或者 （与in(set)类似）

like:

含有a的：like ’ %a% ’

a为最后元素：like ’ %a ’

a为第二个元素：like ‘_a%’ 注：下划线是一个占位符

查询带有—下划线元素：like ‘%_%’ escape ‘反斜杠（这里打不出来）’ 注：转义字符

排序

排序：可以是列名也可以是列名的别名

降序：

order by 列名 desc

升序：

order by 列名 asc

默认：升序

order by 列名

多重排序：

order by 列名1 desc , 列名2 asc 注：先按照列名1排序好，在第一列相同时再按照列名2排序

单行函数

五类单行函数：

字符、数值、日期、转换、通用

字符：

lower：转换为小写

upper:转换为大写

initcap：转换为首字母大写，其他小写

应用场景：查询字符时不确定字符的大小写（字符串大小写敏感），可以把数据库中的要查找字符全部转换为小写（真实数据库数据不变）去比对。

select * from 表名 where lower(列名) = ‘要查询字符’

字符控制函数：

函数	结果	含义
concat(‘Hello’,‘World’)	HelloWorld	拼接字符串
substr(‘HelloWorld’,1,3)	Hel	从第1个数，输出3个。（SQL中字符串从1开始数）
length(‘HelloWorld’)	10	字符串有多少位
instr(‘HelloWorld’,‘W’)	6	W在字符串中位置
lpad(salary,10,’@’)	@@@@@24000	补齐字符串，使长度为10。在前面加@
rpad(salary,10,’@’)	24000@@@@@	补齐字符串，使长度为10。在后面加@
trim(‘H’ from ‘HHHHelloHWorldHHHH’)	elloHWorld	去掉字符串首尾的H
replace(‘bbabcdbb’,‘b’,‘m’)	mmamcdmm	替换掉字符串中全部的b

round：四舍五入

round(45.9468 , 2) ---------------------------------> 45.95 保留两位小数（位数由自己控制）

trunc ：截断

trunt (45.9468,2) ---------------------------------> 45.94 剩余2位小数

mod ：求余

mod(1600,300) ---------------------------------> 100

日期：

在日期上加上或减去一个数字结果仍为日期。

两个日期相减返回日期之间相差的天数。–日期不允许做加法运算，无意义。

可以用数组除以24来向日期中加上或减去天数。

日期函数：

函数	描述
months_between	两个日期相差的月数
add_months	想指定日期中加上若干月数
next_day	指定日期的下一个星期对应的日期
last_day	本月的最后一天
rount	日期四舍五入
trrnc	日期截断

转换函数

隐式转换：

源数据类型	目标数据类型
varchar2 or char	number
varchar2 or char	date
number	varchar2
date	varchar2

显式转换：转换时需要辅助

应用：查询某天的数据，需要转换类型。

通用函数

这些函数适用于任何数据类型，同时也使用与空值：

nvl(expr1,expr2) 当expr1为null时用expr2替代。

例如：

输出last_name , dapartment_id ,当department_id 为null 时，显示”没有部门“

select last_name ,nvl(to_char(department_id,‘999999’),’ 没有部门 ') from employees

nvl2(expr1,expr2,expr3) 当expr1 不为空，返回expr2；为null，返回expr3。

nullif(expr1,expr2) 当expr1与expr2 相等返回null，不等返回expr1 。

coalesce(expr1,expr2,expr3,exprn) 当expr1 为空，返回expr2；当expr2 为空，返回expr3。以此类推。

条件表达式

在SQL语句中使用if-then - else 逻辑、

使用两种方法： case 表达式； decode函数

case格式（在select和from之间）:

case 列名 when 第一个判断 then 表达式1 （不加标点）

​ when 第二个判断 then 表达式2

​ ,

​ when 第n-1个判断 then 表达式n-1

​ else 第n个判断 then 表达式n end 列的别名

decode格式（在select和from之间）：

decode ( 列名 , 第一个判断, 表达式1, (必须加标点)

​ 第二个判断, 表达式2,

​ ，，，

​ 第n-1个判断, 表达式n-1 ,

​ 表达式n

) 列的别名

多表查询

等值连接和不等值连接

内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。

等值连接：

select 表名1.列名1 ，表名1.列名2，表名2.列名1 from 表名1 别名1 ,表名2 别名2 where 别名1.列名2 = 别名2.列名2

**连接n个表，至少需要n-1个连接条件。**例如：连接三个表，至少需要两个连接条件。

非等值连接：

select 表名1.列名1 ，表名1.列名2，表名2.列名1 from 表名1 别名1 ,表名2 别名2 where 别名1.列名2 between 别名2.列名3 and 别名2.列名7

注：就是表名1中的列名2 取值范围在表名2的列名3 与 列名3 7 取值范围之间。

外连接

查询不满足连接条件的数据。

两个表在连接过程中除了返回满足条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时，结果表中相应的列为空（null）。

外连接的 where 子句条件类似与内部连接，但连接条件中没有匹配行的表的列后面要加外连接运算符，即用圆括号括起来的加号（+）。

左外连接：（在右边加圆括号括起来的加号（+）。）

select 表名1.列名1 ，表名1.列名2，表名2.列名1 from 表名1 别名1 ,表名2 别名2 where 别名1.列名2 = 别名2.列名2 (+)

右外连接**：（在左边加圆括号括起来的加号（+）。）

select 表名1.列名1 ，表名1.列名2，表名2.列名1 from 表名1 别名1 ,表名2 别名2 where 别名1.列名2 (+) = 别名2.列名2

自连接

自己连自己。可以拆成两个查询语句。

总结

分组函数

子查询

单行子查询

**注意事项：**子查询要包含在括号内。

​ 将子查询放在比较条件的右侧

​ 单行操作符对应单行子查询，多行操作符对应多行子查询。

例如：谁的工资比Abel 高？

之前：select salary from employees where last_name = ‘Abel’ ; --salary 工资 结果11000

​ select last_name , salary from employees where salary > 11000

子查询：select last_name , salary from employees where salary > (select salary from employees

​ where last_name = ‘Abel’ ) ; --返回括号内查询到的数据给外层使用

多行子查询

返回多行。

使用多行比较操作符。

操作符	含义
in	等于列表中的任意一个
any	和子查询返回的某一个值比较
all	和子查询返回的所有值比较