Oracle 学习总结

余火骑士

已于 2022-02-28 19:27:45 修改

阅读量1k

点赞数

文章标签： oracle 学习数据库

于 2022-02-27 00:48:28 首次发布

本文链接：https://blog.csdn.net/weixin_44972908/article/details/123149663

版权

本文详细介绍了SQL的基本语句，包括DDL（数据定义语言）用于创建、修改和删除表格，DML（数据管理语言）涉及插入、更新和删除数据，以及DQL（数据查询语言）用于查询数据。内容涵盖表约束、聚合函数、分析函数、字符和数字函数、日期函数、类型转换以及条件转换函数等核心概念。此外，还讨论了序列、视图、存储过程和函数的创建，以及行转列和列转行的操作。

摘要由CSDN通过智能技术生成

3、修改表格
alter可以进行字段增加、约束的增加以及删除，需注意的是如果已有数据跟添加的约束有所冲突，那么就无法修改表格；
alter table 表格名 drop/add/modify （删除，增加，修改，）约束名修改内容
格式：
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(列名1[,列名2…])
ALTER TABLE 主表名 ADD CONSTRAINT 约束名 FOREIGN KEY(列名1[,列名2…]) REFERENCES 从表名(列名1[,列名2…])
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(条件)
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名)
ALTER TABLE 表名 MODIFY 列名 NOT NULL
ALTER TABLE 表名 DROP CONSTRAINT 约束名

4、表约束
可以在字段后面加上，
也可以在创建表的最后或者修改表的时候进行添加：
约束类型（字段名1，字段名2.。）
赋名格式： constraint 约束名约束类型

（1） primary key 主键约束

	constraint    约束名   primary key （字段名1，字段名2，。。。）
	CREATE TABLE t_china_id (
		ID_NO VARCHAR2(18),
		ID_NAME VARCHAR2(60),
		BIRTH_DAY DATE,
		ADDRESS VARCHAR2(200),
		TEL NUMBER(11),
		CONSTRAINT PR_ID_NO PRIMARY KEY(ID_NO)    ---有赋名
		或者：
		PRIMARY KEY(ID_NO)   ---没有赋名
	);

（2）foreign key 外键约束

	constraint    约束名  foreign key （子表字段名）  references  父表名（父表字段名）；

（3）check 检查约束（oracle特有）

	constraint    约束名    check （条件语句）

（4） unique 唯一约束
constraint 约束名 unique （字段名1、。。。）

（5） not null 非空约束
constraint 约束名 not null （字段名1.。。）

5、注释comment
格式：
注释表格：comment on table 表格名 is ‘注释内容’；
注释字段：comment on column 表格名.字段名 is ‘注释内容’；

2、DML 数据管理语句（insert、update、delete）

1、insert 插入数据
格式：insert into 表格名（字段名1、字段名2、、、）values（数据1、数据2、。。。）
如果没有写明那些字段，那么默认填写全部字段；
表格复制使用
insert into 新表格名 select 字段名1、字段名2.。 from 旧表名 where 条件；

2、update修改数据
格式：update 表格名 set 字段名1=数据1，字段名2=数据2，。。。。 where 条件；

3、delete删除数据
格式：delete from 表格名 where 条件；

3、DQL 数据查询语句（select）

执行顺序及格式：
select 。。。。。5
from .。。。。。1
where .。。。。。2
group by.。。。。3
having .。。。。。4
order by。。。。。6（asc升序，不填一般是升序排列、desc 降序排列）

”*“ 表示选择表格全部列；
where 进行条件筛选；

group by 可以单独用，不允许直接加聚合函数，分组后select 后面只能写分组字段或者聚合函数。
having 对分组之后的函数进行筛选，一定要和group by 一起用；

多表连接：
inner join 内连接 inner可以省略
left out join 左连接 out 可以省略
right out join 右连接 out 可以省略
full out join 全连接 out 可以省略

select 。。。
from 。。。。
left/right/full join 。。。。
on。。。。；

1、聚合函数：

sum求和
avg求平均数
count求计数
max最大值
min最小值

2、分析函数

（1）排序函数
row_number();派号排序
rank();跳跃排序
dense_rank()不跳跃排序

（2）位移函数：
lag（）上移
lead（）下移

语法格式：
函数名(列名) over(partition by 列名 order by 列名)

注意：partition by不是必填项，但必须有over（），聚合函数也能与之连用；

3、运算

关系运算：等于 =、大于>、小于<、不等于 <>/!= 、小于等于 <=,大于等于>=；
算术运算：加、减、乘、除：+，-、*、/ ；
逻辑运算：and 、or 、not 执行顺序： not>and>or;
逻辑运算多于其他连用：
not in、not null、between。。。and。。。；
集合运算：
交集：intersect；
并集：uniion（去重），union all（不去重）
补集：minus 前者有，后者无
使用时必须保证前后列数一致；

4、模糊查询：like 像

% 代替任意一个或者多个字符；
_代替任意一个字符；

5、null 空值，空值不可参与任何运算：

1、空值跟任何进行算术运算，得到的结果还是空值；
2、空值跟任何值进行任何的关系运算，得到的结果都为不成立；
3、空值不参与任何聚合运算；
4、排序的时候，空值永远为最大。

6、字符函数

（1）大小写控制函数：
lower（x）x转换为小写；
upper（x）x转换为大写；
initcap（x）x转换为首字母大写；

（2）字符控制函数：
1）连接字符串的两种方式：
双杠线：||，需注意用括号；
concat（x，y）；连接字符串x，y；需注意只能连接两个字符串，多的只能进行嵌套使用；

2）substr 字符串截取
substr（x，2,3）表示截取x字段，从第2位开始，截取3位，如果没有注明截取几位，那么会默认将从第二位开始截到最后；

3）length返回字符串长度：length（x）；

4）instr定位查询字符串
instr(‘hello’,‘o’)表示定位O在hello 中所在的位置；
select instr(‘BI大数据学习班’,‘BI’)from dual;
select instr(‘BI数据BI数据BI数据’,‘BI’) from dual;
select instr(‘BI数据BI数据BI数据BI数据BI数据’,‘BI’,2,3) from dual; --从第二位开始查找，找出第三出现的位置
注意：1、一个汉字占用的是两个字符位，
2、默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找；

5）lpad、rpad左补齐，右补齐
select rpad(‘abcd’,5) from dual结果在abcd后面有一个空格位；

6）trim 首尾截去空位
ltrim、rtrim，截去左空位，截去右空位；
select trim (’ 123 ’ ) from dual; --‘123’
select ltrim (’ 123 ’ ) from dual; --‘123 ’
select rtrim (’ 123 ’ ) from dual; --‘ 123’

7）replace 替换
replace（字符串，需替换字符，新的替换字符）如果没有填新的替换字符默认替换为空值；
select replace(‘BI数据BI数据BI数据BI数据BI数据’,‘BI’,‘AI’)from dual;

7、数字函数

（1）round四舍五入
round（数字，保留位数）
select round(‘30.1415926’,2) from dual; --3.14
select round(‘30.1415926’,4) from dual; --30.1416
select round(‘30.1415926’,-2) from dual; --0
select round(‘5.98’,-1) from dual; --10
（2）mod 求余 mod（x，y）x除以y后的余数；

（3）trunc(x,y) x在第y位截断到整数
select trunc(3.1495926) from dual; --3
select trunc(3.1495926,-2) from dual; – 3.14
select trunc(33.1495926,-1) from dual; – 30

	trunc一般配合日期使用
    select sysdate from dual;
      trunc(d,fmt):
      fmt:'DD'   截取到当月的当天
      fmt:'DDD'  截取到当月的当天(默认格式)
      fmt:'D'    截取到当周的第一天
      fmt:'MM'   截取到当月的第一天
      fmt:'Q'    截取到当季度的第一天
      fmt:'Y'    截取到当年的第一天
      
      select sysdate,
            trunc(sysdate,'DD'),
            trunc(sysdate,'DDD'),
            trunc(sysdate,'D'),
            trunc(sysdate,'MM'),
            trunc(sysdate,'Q'),
            trunc(sysdate,'Y')
            FROM DUAL;

8、日期函数

sysdate 返回的就是日期和时间，返回系统当前时间
1.在日期上加上或者减去一个数字结果仍为日期；
2.两个日期相减返回日期之间相差的天数；（日期不允许做加法，无意义）
3.可以用数字除24来向日期中加上或减去天数。
（1）add_month（x，y）向指定日期x加上y个月，返回计算后的新日期；

（2）month_between（x1，x2），计算x1和x2之间相差的月数；
select months_between(date’2022-02-18’,date’2019-3-28’) from dual; --34.67
select trunc(months_between(date’2022-02-18’,date’2019-03-28’)) from dual; --34

（3）last_day（x）返回指定日期当月的最后一天
（4）next_day 取下一个时间
select next_day(sysdate,2) from dual; --只能是1-7，代表周日至周六
select next_day(sysdate,‘星期六’) from dual; --离当前时间最近的一个星期六是几号

9、类型转换：

varchar2；
number；
date；
to_date/to_char/to_number

–隐式数据类型转换（oracle自动完成）
varchar2/char >>>>> number
varchar2/char >>>>> date
number >>>>> varchar2
date >>>>> varchar2

to_char
select hiredate
from emp
where to_char(hiredate,‘yyyy-mm-dd’)=‘1980-12-17’ /或-都可以，但是要保持一致

to_number
数字转换成字符串 to_char
select to_char(2345678654,‘FM999,999,999,999’) FROM DUAL; --2,345,678,654.00 字符格式，加上千位符
select to_number(‘￥2,345,678,654.00’,‘L999,999,999,999.00’) FROM DUAL; --2345678654

to_date 日期转换
select to_date(‘2019-08-17’,‘yyyy-mm-dd’),
to_date(20190817,‘yyyy-mm-dd’)
from dual;

–通用
nvl空值转换
nvl(X,Y) NVL(COMM,0)
NVL2(X,1,2) 如果X不为空，返回结果1,如果X为空返回结果2.
–对emp表中comm为空的加上1000，comm不为空的员工加上500.
select emp.ename, nvl2(emp.comm, emp.comm + 500, 1000) from emp;

10、条件转换函数

区别：
    decode：只能对单个字段进行等值判断。
	case when:可以对多个字段进行任意条件判断。

decode（列值，判断值1，返回值1，判断值2，返回值2，。。。。默认值）—默认值不给默认为空值；

–case when 条件转换
case when 条件1 then 返回值1
when 条件2 then 返回值2
else 默认值
end ; --条件都不成立，又没有给else就为空

11、常用函数（去重）

distinct（）
格式：select distinct 字段名1，字段名2。。。 from 表格名；
关于查询结果集的去重
select distinct 字段，字段 from emp;
distinct只能放在语句最前面。当然有些特殊用法，如下：
select count(distinct 字段) from emp;表示对该字段去重后再计算数量。

12、

FIRST_VALUES（）、LAST_VALUES（）

创建序列、视图、存储过程、函数、行转列、列转行

1、序列：

（1）创建序列
create sequence 序列名
start with number ------从某一个整数开始，升序默认值是1，降序默认值是-1；
increment by increment ----增长数
maxvalue num| nomaxvalue-----最大值，升序的最大值是：1027，降序默认值是-1
minvalue num | nominvalue------最小值，升序默认值是1，降序默认值是-1026。
cycle | nocycle-----------是否循环，默认NOCYCLE。
cache num | nocache – 是否缓存登录(预先在内存中生成序列号/不预先在内存中生成序列号)

序列使用(不可以直接select * from 序列，只有表和视图可以)，需要借助其他字段调用：
CREATE SEQUENCE s_1;----创建序列
SELECT s_1.NEXTVAL FROM DUAL; .nextval表示访问下一个值；
SELECT s_1.CURRVAL FROM DUAL; .currval表示访问当前值；

（2）序列修改
可以修改序列的增量，最大值，最小值，循环选项，是否使用缓存
alter sequence 序列名
MAXVALUE 10000 --最大值改成10000
MINVALUE 10 --最小值改成
NOCYCLE --改成不循环
nocache —改成不缓存
序列修改注意事项：1.必须是序列的拥有者或者对序列有ALTER权限；
2.只有之后再继续添加的序列值会被改变；
3.改变序列的初始值只能通过删除序列之后重建序列。
（3）删除序列
drop sequence 序列名；

（4）使用序列注意：
1.回滚；
2.系统异常；
3.多个表同时使用一个序列。
以上3点可能会使序列出现裂缝，就是序号断层。
如果不将序列的值装入内存(NOCACHE),可使用USER_SEQUENCE查看序列当前的有效值。
select * from user_sequences where sequence_name = ‘S_101’;

2、视图

视图（View）是一种虚表，视图建立在已有表基础上，视图赖以建立的这些表称为基表。
视图是将基表数据提供给用户的另一种表现形式。
从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的SELECT…FROM即可。

（1）创建视图create
CREATE OR REPLACE VIEW 视图名
AS
SELECT 语句；
（2）修改视图update
update 视图名set 字段名 = 修改值 where 条件；

（3）删除视图数据delete
delete from 视图名 where 条件；

其创建，修改，删除方式跟表格处理一样；

3、伪列

1、rowid 记录每一行的唯一标识符，插入数据的时候生产，记录该行的物理地址（用来去重）

每一条数据都有唯一的物理地址；

2、rownum 查询数据是生产、返回序号；

select s.*,rownum
from shiren
注意：他是先运行完表格后，在进行排序；

4、行转列、列转行

1、使用case when进行转换
case when 。。。then …
when。。。。then。。。
else 。。。。
end；
2、使用行转列函数pivot函数，
列转行函数unpivot函数，
oracle特有的

格式：pivot（填入的值 for 转换的列名 in（原字段数据新的字段名，原字段数据1 新的字段名1，。。。。））；

实例：
学生成绩表(t_stu_score)如下：
sname subject score
张三语文 81
张三数学 75
李四语文 76
李四数学 90
王五语文 81
王五数学 100

用一条SQL查出如下的结果：
姓名语文数学
张三 81 75
李四 76 90
王五 81 100*/

case when方法：
select sname ,sum(case when subject=‘语文’ then score else 0 end) 语文，
sum(case when subject=‘数学’ then score else 0 end) 数学
from t_stu_score
group by sname;

pivot方法

select *
from t_stu_score pivot(sum(score) for subject in(‘数学’ 数学,‘语文’ 语文));

格式：unpivot(新增值所在列的列名 for 新增列转为行后所在列的列名 in (需转为行的列名) )

实例：

姓名语文数学
张三 81 75
李四 76 90
王五 81 100

转换为：
sname subject score
张三语文 81
张三数学 75
李四语文 76
李四数学 90
王五语文 81
王五数学 100

使用如下语法：
select * from t_stu_score UNPIVOT(score FOR subject IN(语文 AS ‘语文’,数学 AS ‘数学’) );