Oracle 初级知识-SQL基础

最新推荐文章于 2024-07-01 10:26:34 发布

Minouio

最新推荐文章于 2024-07-01 10:26:34 发布

阅读量295

点赞数

分类专栏： SQL 文章标签： sql

本文链接：https://blog.csdn.net/qq_42012732/article/details/106905121

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

前言

Oracle数据库是什么？
Oracle Database，又名Oracle RDBMS，简称Oracle。是甲骨文公司推出的一款关系数据库管理系统。
Oracle和MySQL的区别？

Oracle是大型数据库而Mysql是中小型数据库，Oracle市场占有率达40%，Mysql只有20%左右，同时Mysql是开源的而Oracle价格非常高。
Oracle支持大并发，大访问量，是OLTP最好的工具。
安装所用的空间差别也是很大的，Mysql安装完后才152M而Oracle有3G左右，且使用的时候Oracle占用特别大的内存空间和其他机器性能。
Oracle也Mysql操作上的一些区别

Oracle学习链接：
实操：
https://www.w3cschool.cn/oraclejc/oraclejc-dxgu2qqt.html
理论：
http://zw1840.com/oracle/translation/concepts/index.htm

此博客的内容主要是慕课课程的笔记。
课程连接：https://www.imooc.com/learn/337

Oracle 概述

Oracle 数据库服务器由：数据库（一组存储数据的文件）、数据库实例（管理存储数据的内存结构）组成。
1. Oracle 数据库

1.1 物理存储结构：存储数据的纯文件
当create database 执行时，将创建一下文件：

数据文件：真实数据
控制文件：无数据，用于描述数据库名称，文件位置，物理结构
联机重做日志文件：由重做条目组成，记录下所有对数据所作的更改

1.2 逻辑存储结构：对磁盘空间使用进行精细控制

数据块：存储数据，对应于磁盘上的字节数
范围：逻辑连续数据块的具体数量
段：分配用于存储用户对象（eg.表/索引）的一组范围
表空间：逻辑存储单元（永久表空间、临时表空间、UNDO）

2. Oracle实例：用户和数据库的接口，组成：系统全局区（SGA)、程序全局区(PGA)、后台进程

3. Oracle基础语句

SELECT: SELECT col1_name,col2_name,… FROM table_name;
ORDER BY: 放在SELECT 最后一个子句
SELECT col_1, col_2 FROM table_name ORDER BY col_1 asc, col_2 desc;
1）default值为asc，先按col_1排序，再按col_2排序。列名可替换为列的序号。
2）含NULL值时，可指定NULL 排序位置： ORDER BY state asc NULLS LAST；
3）ORDER BY 可应用一个函数排序：ORDER BY UPPER(name)
DISTINCT: SELECT DISTINCT col_1,col_2,…,col_n （组合的唯一性)
1）Distinct 将NULL 视为重复值
WHERE: 过滤

1）先FROM → WHERE → SELECT
=, != , < , > , >= , <=
IN 等于值列表中的任何值，NOT IN 不等于值列表中的任何值
ANY / SOME / ALL 将值与列表或子查询进行比较，必须以另一运算符作为前缀，如（<, =)
[NOT] BETWEEN xxx AND yyy
[NOT] EXISTS
IS [NOT] NULL

2） AND, OR, NOT 组合条件
AND, OR 可以用IN代替。如 IN(20,21,22) 等价于 20 OR 21 OR 22
FETCH：限制查询返回的行数(替换SQL 中的Limit）
[ OFFSET offset ROWS]
FETCH NEXT [ row_count | percent PERCENT ] ROWS [ ONLY | WITH TIES ]
ONLY: 仅返回FETCH NEXT(或FIRST)后的行数或行数的百分比。
WITH TIES: 返回与最后一行相同的排序键。请注意，如果使用WITH TIES，则必须在查询中指定一个ORDER BY子句。如果不这样做，查询将不会返回额外的行。
IN
BETWEEN
WHERE order_date BETWEEN ‘2016-12-01’ AND ‘2016-12-13’
LIKE: 模式匹配
last_name LIKE ‘st%’ 以st开头的last_name
last_name LIKE ‘%er’ 以er结尾的last_name
last_name LIKE ‘%tr%’ 中间字符有tr的last_name
’je_i’ 其中下划线_代表任意字符

表空间

SYSTEM 系统信息
SYSAUX 作为example的辅助
UNDOTBSI 撤销信息
TEMP 临时
USERS 用户创建的对象
向表空间增加数据文件
ALTER TABLESPACE tablespace_name ADD DATAFILE ‘XX.dbf’ SIZE XXX
删除表空间
DROP TABLESPACE tablespace_name INCLUDING CONTENTS

表
最基本存储空间，2维结构（行和列），列具有相同数据类型，列名唯一，行数据唯一性。
数据类型：
a) 字符型：char(n)←max:2000, nchar(n) ←max:1000, varchar2←可变长度节省空间max:4000， nvarchar2←max：2000
b) 数值型：numbers(p,s)←（有效数字，小数点后的位数）， float(n)
c) 日期型：DATE精确到秒， timestamp 精确到小数秒
d) 其他：blob(可存放4GB,2进制) ，clob(可存放4GB,字符串)

管理表
管理表可分为三个部分：创建表，修改表，删除表
创建表
包含：所需字段、字段的类型
CREATE TABLE table_name(col_name datatype,…);
例如：CREATE TABLE userinfo(id number(6,0), user_name varchar2(20), reg_date date);
修改表

添加字段：alter table table_name add column_name datatype;
更改字段数据类型：最好无值前修改，alter table table_name modify column_name datatype; 设置默认值，modify email default ‘none’
删除字段：alter table table_name drop column col_name
修改字段名：alter table table_name rename column col_name to new_name
修改表名：rename table_name to new_name 注意这里没有table关键字

删除表
delete table table_name
truncate table table_name
drop table table_name

truncate和drop都是ddl语句（数据定义语句），执行后会自动提交
区别：
1、truncate和delete都不删除表结构，只删除表数据。drop语句会将表结构、被依赖的关系、触发器、索引都删除，保留依赖于该表的存储过程和函数，但是变为invalid状态。
2、delete是dml（数据库操作语言），这个操作会放到rollback segment中，会记录日志，可以通过回滚操作撤销删除的数据，必须通过提交才会真的删除数据。如果有相应的触发器trigger，则会触发。
truncate、drop都是ddl（数据库定义语言），操作不能回滚，不会记录到日志中。且不会触发trigger。
3、delete不影响表占用的表空间extent，高水线(high watermark)保持不动，即删除内容后，在插入别的语句时，表中的开始位置为删除前的最高位置。如删除前表中有三条数据，删除后插入新数据时，会从第四条开始插入。
drop 将表所占用的表空间全部释放。
truncate语句缺省情况下会将表空间释放到最小表空间minextents个extents，除非使用reuse storage;
truncate语句会将高水位线复位。
4、速度上，一般来说drop>truncate >delete。

操作表中的数据

添加数据
insert 语句： insert into table_name (col1_name,col2_name,…) values(col1_val,col2_val,…)
添加默认值 default
复制表数据
create table table_new as select col1,col2,… from table_old
在添加时复制
insert into table_new [(col1,col2,…)] select col1,col2… from table_old
修改数据
update table_name set col1=value1,… [where]
删除数据
delete table_name where …

约束

约束：定义规则，确保完整性。分为外键约束、唯一约束、检查约束。

非空约束：
在创建表时：create table table_name(col_name datatype NOT NULL,…);
在修改时：alter table table_name modify col_name datatype NOT NULL（也可以改成NULL）
主键约束：确保每一行的唯一性
在创建表时：create table table_name(col_name datatype PRIMARY KEY,…)
联合主键：所有字段写完后，CONSTRAINT constraint_name PRIMARY KEY(col1_name,col2_name,…)
在修改时：ADD CONSTRAINT constraint_name PRIMARY KEY(col_name,…)
删除主键约束：
禁用： DISABLE /ENABLE CONTRAINT constraint_name
删掉：DROP CONSTRAINT constraint_name;
DROP PRIMARY KEY [caseade]
约束字典
user_constraints
查询约束的名称：select constraint_name from user_constraints where table_name = ‘XXX’
外键约束
在创建表时:
create table table1 (col1 datatype REFERENCES table2(col2),…);
table1是从表，table2是主表，col2须为table2的主键，且col1和col2的数据类型应该一致。
CONSTRAINT constraint_name FOREIGN KEY (col1) REDERENCES table_name(col_name) [ON DELETE CASCADE]
constraint_name 要唯一

在修改表时：
alter table table_name ADD CONSTRAINT constraint_name FOREIGN KEY(col_name) REFERENCES table_name(col_name)
删除约束：
禁用：DISABLE /ENABLE CONSTRAINT con_name[ ON DELETE CASCADE];
删除：DROP CONSTRAINT con_name
On Delete和On Update都有Restrict，No Action, Cascade,Set Null属性。
restrict(约束): 当在父表（即外键的来源表）中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。
cascade 表示级联操作，就是说，如果主键表中被参考字段更新，外键表中也更新，主键表中的记录被删除，外键表中改行也相应删除
no action:意思同restrict.即如果存在从数据，不允许删除主数据。
set null：当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）
唯一约束
唯一约束可为NULL,可有多个。而主键约束必须是非空的、在每个表中只能有一个。
创建表时(列级创建)：create table table_name (col1_name datatype UNIQUE,…)
表级创建：constraint constraint_name UNIQUE(col1_name ) 每个唯一约束都要单独创建
在修改表时：add constraint constraint_name UNIQUE(col_name)
删除：禁用：distable/enable constraint constraint_name
删除：drop constraint constraint_name
检查约束
检查约束让表中的值具有实际意义。
创建表时：create table table_name check(expressions)
表级设置：constraint constraint_name check(expressions)
修改表时:add constraint constraint_name check(expressions)
删除：禁用：disable/enable constraint constraint_name
删除：drop constraint constraint_name
非空约束只能在列级设置，不能在表级设置，非空约束没有名字

查询

基本查询：select [DISTINCT] col_name,…|* from table_name where …
设置新的字段名: column(或者简写成col) col_name heading new_name
设置结果显示的格式：column col_name format dataformat
dataformat类型:
a10(字符类型长度为10,字符类型只能设置显示的长度)
999.9(用9来占位，数值型，当格式与实际值不符，显示#)
$99.9(美元)
清除格式：column col_name clear
查询所有字段：select * from table_name
查询指定字段：select col_name1,col_name2 from table_name
给字段设置别名: select col_name [as] new_name 与heading区别：一次可以设置多个
运算符和表达式：表达式= 运算符+操作数

算数运算符：+，-，*，/
比较运算符：>, >=, < , <=, = 返回FALSE/TRUE
逻辑运算符：and, not, or

带条件的查询：where, 用括号()放置较高的优先级, 运算符自身的优先级为 not>and>or
模糊查询：like，通配符的使用(_,%)←(代替一个字符，代替0到多个字符)
例子:
where col_name like ‘a%’ 以a开头的
where col_name like ‘_a%’ 第2个字符为a
where col_name like ‘%a%’ 含有字符a
范围查询： where salary >=900 and salary <= 2000
等价于between 900 and 2000 (闭区间)
IN/ NOT IN 列表查询
where user_name in (‘aaa’,‘bbb’)
对查询结果进行排序：select … from … [where…] order by col_name desc/asc [, col2_name desc/asc] 字符按ASCII码排序
条件查询：case when 语句

case col_name when val1 then result1,when … then …,… [ELSE resultn] end
case when col_name = value1 then result 1, … [ELSE resultn] end

decode函数：decode(col_name, value ,result1,…, default valuen)
ifnull函数：ifnulll(a,b) 判断a是否为null ，是则返回b，否则返回a
union：表的并联，union all即使遇到重复的记录也会保存
联合组成键值来分组：group by concat(x,’,’,y)

Oracle 函数

数值函数、字符函数、日期函数、转换函数

数值函数

round: 四舍五入 round(x,m) 保留数值x的m位小数， m>0：小数点后m位，m<0:小数点前m位
select round(23.4),round(23.45,1),round(23.45,-1) from dual
结果依次为：23,23.5,20 oracle里面必须接目标表dual
取整函数：ceil(n) 向上取整 floor(n) 向下取整
select ceil(23.45), floor(23.45) from dual
常用计算：
abs(n) 绝对值
mod(m,n) 若m/n为null，返回null
power(m,n) m^n
sqrt(n) 算数平方根
三角函数: sin(n),asin(n),cos(n),acos(n),tan(n),atan(n)

日期函数

系统时间：sysdate 默认格式DD-MON-YY
日期操作：
add_months(date,i)
next_day(date,char) 若char为’Monday’，返回date之后的下一个Monday
last_day(date) 返回date所在月份的最后一天
months_between(date1,date2) 两个日期间隔的月份
extract(date/year/hour from datetime)提取datetime的某个粒度年月日小时分等
datediff(date1,date2) 等价于date1-date2的值

字符函数

大小写转换：
upper(char)
lower(char)
initcap(char) 首字符变为大写
获取子字符串：substr(char,m,n)
char 表示原字符串，
m表示取子串的位置(0,1)均表示从首字符开始截取，m<0 表示尾部开始，向前算abs(m)个然后按顺序开始截取n个，若n>abs(m) 则n=abs(m),
n表示截取子串的长度
获取字符串长度：length(char) 包含空格长度
字符串连接函数：concat(char1,char2) 与||操作符一样
select ‘a’ || ‘b’ from dual

等价于 _select concatr(‘a’,‘b’) from dual

去除子串
a) 去除空格：
trim(char) :去除左右空格，中间不回去除
ltrim(char): 仅去除左侧空格
rtrim(char): 仅去除右侧空格
都无法去除全角空格
b) 去除指定字符串
trim函数用来去除一个字符串的开头或结尾（或两者）的字符。
trim(leading| trailing | both char1 from char2)
char1为单个字符
trim(leading ‘a’ from ‘aabbaaccaa’) 结果为：bbaaccaa
trim( trailing ‘a’ from ‘aabbaaccaa’) 结果为：aabbaacc
trim(both ‘a’ from ‘aabbaaccaa’) 结果为bbaacc
ltrim、rtrim
ltrim(string1,string2) string1为原始字符串，string2为要去除的字符串（可以为多个字符）
例子：
ltrim(‘abccba’,‘abc’) 结果：空串null
rtrim(‘abccba’,‘abc’) 结果：空串null
解析：若string2为多个字符，以单个字符开始匹配，直到遇到string1中出现string2中没有的字符。
另一个例子:
ltrim(‘abcxcba’,‘abc’) 结果：xcba（结果并不是只剩一个“x”，而是包括stirng1中“x”以及右侧的字符）
rtrim(‘abcxcba’,‘abc’)结果：abcx（结果并不是只剩一个“x”，而是包括stirng1中“x”以及左侧的字符）
解析：ltrim函数从string1左侧往右读，左侧前三个字符分别在string2中找到了对应的单个字符匹配，所以都被去除掉，读到“x”的时候，没有匹配上，所以从这开始到string1右侧都被保留并返回；相反，rtrim函数从string2右侧往左读，右侧前三个字符分别在string2中找到了对应的单个字符匹配，所以都被去掉，读到“x”的时候，没有匹配上，所以从这开始到string1左侧都被保留并返回。
由此类推，如果string1中左侧（右侧）第一个字符在string2中找不到对应，那么ltrim（rtrim）函数将会把string1字符串完整返回
替换函数
replace(char,string,newstring) 用newstring（default值为空格）替换char中的string

转换函数
a) 日期→字符：to_char(date,format,param) ←(日期，转换的格式，日期的语言)
常用格式:
YY,YYYY,YAER
MM,MONTH
DD,DAY
HH24,HH12
MI,SS
select to_char(sysdate,‘YYYY-MM-DD HH24:MI:SS’) from dual
结果:‘2020-04-20 21:24:41’
b) 字符→日期：to_date(char,format,params)
select to_date (‘2019-09-08’,‘YYYY-MM-DD’) from dual
结果：2019-SEP-08
c) 数字→字符：to_char(number,format)
常用格式：
9:显示数字并忽略前面的0
0：显示数字，位数不足，用0补齐
.或0：显示小数位
,或G：显示千位符
$：美元符号
s：加正负号（前后都可以）
d) 字符→数字：to_number(char,format)

Minouio

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle 初级知识-SQL基础

目录前言Oracle 概述表空间操作表中的数据前言Oracle数据库是什么？Oracle Database，又名Oracle RDBMS，简称Oracle。是甲骨文公司推出的一款关系数据库管理系统。Oracle和MySQL的区别？Oracle是大型数据库而Mysql是中小型数据库，Oracle市场占有率达40%，Mysql只有20%左右，同时Mysql是开源的而Oracle价格非常高。Oracle支持大并发，大访问量，是OLTP最好的工具。安装所用的空间差别也是很大的，Mysql安装完后才1
复制链接

扫一扫

专栏目录