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 | 和子查询返回的所有值比较 |