Oracle入门级学习文档sql入门级学习文档，oracle常用函数，记录从前端小白到入门的笔记，后续还要结合着项目多写

基础

表创建

CREATE TABLE 表名称(  字段名 类型(长度) primary key,  字段名 类型(长度),  ....... );

数据类型：

1、字符型

（1）CHAR : 固定长度的字符类型，最多存储 2000 个字节

（2）VARCHAR2 :可变长度的字符类型，最多存储 4000 个字节

（3）LONG : 大文本类型。最大可以存储 2 个 G

2、数值型

NUMBER : 数值类型例如：NUMBER(5) 最大可以存的数为 99999 NUMBER(5,2) 最大可以存的数为 999.99

3、日期型

（1）DATE：日期时间型，精确到秒

（2）TIMESTAMP：精确到秒的小数点后 9 位

4、二进制型（大数据类型）

（1）CLOB : 存储字符,最大可以存 4 个 G

（2）BLOB：存储图像、声音、视频等二进制数据,最多可以存 4 个 G

修改表

--增加表字段
ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]...)
--修改表字段
ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]...)
--删除一个字段
 ALTER TABLE 表名称 DROP COLUMN 列名 
 --删除多个字段 
 ALTER TABLE 表名称 DROP (列名 1,列名 2...)

数据增删改查

INSERT INTO 表名[(列名 1，列名 2，...)]VALUES(值 1，值 2，...) 

UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，....WHERE 修改条件；

DELETE FROM 表名 WHERE 删除条件;

select top

SQL Server

SELECT TOP number|percent column_name(s) FROM table_name;

MySQL 语法

SELECT column_name(s) FROM table_name LIMIT number;

Oracle 语法

SELECT * FROM Persons WHERE ROWNUM <=5;

通配符

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符

join

group by

用于结合聚合函数，根据一个或多个列对结果集进行分组。

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

partition by 分组，但不聚合

select province, city, persons, row_number() over(partition by province order by persons) from xzq_person;

having

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

exists

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

SELECT Websites.name, Websites.url 
FROM Websites 
WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

行列转换

case when  then else end

常用函数

数值型函数

abs(x) 返回绝对值

select abs(100),abs(-100) from dual;
-- 100, 100

sign(x) 返回x的正负值

--若为正值返回1，负值返回-1，0返回0
select sign(100),sign(-100),sign(0) from dual;
-- 1, -1, 0

ceil(x) 返回大于等于x的最小整数值，向上取整

select ceil(3.1),ceil(2.8+1.3),ceil(0) from dual;
--返回4，5，0

floor(x) 返回小于等于x的最大整数值，向下取整

select floor(3.1),floor(2.8+1.3),floor(0) from dual;
--返回3，4，0

mod(x,y) 返回x除以y的余数

select mod(23,8),mod(24,8) from dual;
--返回：7, 0

Round(x, precision)

x是要进行四舍五入的数值或表达式

precision 指定要保留的小数位数。可以为正数（表示保留的小数位数）或负数（表示要进行四舍五入的整数位数），省略为整数

SELECT ROUND(1234.5678, 1) FROM dual;
-- 1234.6

字符函数

concat(c1,c2) 字符串拼接

 select concat('010-','88888888')||'转23' from dual;
 -- 010-88888888转23   ||也是拼接的功能

lower(c1) 字符串转小写

select lower('AaBbCcDd') from dual;
-- aabbccdd

upper(c1)字符串转大写

select upper('AaBbCcDd') from dual;
-- AABBCCDD

initcap(c1) 返回字符串并将字符串的第一个字母变为大写，其它字母小写

select initcap('smith abc aBC') upp from dual
-- Smith Abc Abc

ipad(c1,n[,c2]) 在字符串c1的左边用字符串c2填充，直到长度为n时为止，n追加后字符总长度

select lpad('gao',10,'*') from dual
-- *******gao

rpad(c1,n[,c2]) 在字符串c1的右边用字符串c2填充，直到长度为n时为止

select rpad('gao',10,'*a') from dual;
-- gao*a*a*a*

replace(c1,c2[,c3]) 字符表达式值中，部分相同字符串，替换成新的字符串

  select replace('he love you','he','i') test from dual
  -- i love you

substr(c1,n1[,n2]) 字符串截取，从1个字符计算

select substr('13088888888',1,3) test from dual;
-- 130

trim(c1 from c2) 删除左边和右边出现的字符串

select TRIM('X' from 'XXXgao qian jingXXXX'),TRIM('X' from 'XXXgaoXXjingXXXX') text from dual;
--返回：gao qian jing                gaoXXjing

SELECT TRIM(both ' ' FROM  '   Hello   ') AS trimmed_string FROM dual;
-- Hello

日期函数

sysdate 返回当前日期

select sysdate from dual;
-- 返回：2024-07-22

add_months(d1,n1) 返回在日期d1基础上再加n1个月后新的日期

select sysdate,add_months(sysdate,3)  from dual;
-- 返回：2024-07-22,2024-10-22

last_day(d1) 返回日期d1所在月份最后一天的日期

select sysdate,last_day(sysdate)  hz from dual;
-- 返回：2024-07-22，2024-07-31

round(day,c1) 给出日期d1按期间(参数c1)附近的日期

select sysdate --当时日期,
round(sysdate) --最近0点日期,
round(sysdate,'day') --最近星期日,
round(sysdate,'month') --最近月初,
round(sysdate,'q') --最近季初日期, 
round(sysdate,'year') --最近年初日期 
from dual;

trunc(d1[,c1]) 返回日期d1所在期间(参数c1)的第一天日期

select sysdate --当时日期,
trunc(sysdate) --今天日期,
trunc(sysdate,'day') --本周星期日,
trunc(sysdate,'month') --本月初,
trunc(sysdate,'q') --本季初日期, 
trunc(sysdate,'year') --本年初日期 
from dual;

select
trunc(sysdate)+(interval '1' second), --加1秒(1/24/60/60)
trunc(sysdate)+(interval '1' minute), --加1分钟(1/24/60)
trunc(sysdate)+(interval '1' hour), --加1小时(1/24)
trunc(sysdate)+(INTERVAL '1' DAY),  --加1天(1)
trunc(sysdate)+(INTERVAL '1' MONTH), --加1月
trunc(sysdate)+(INTERVAL '1' YEAR), --加1年
trunc(sysdate)+(interval '01:02:03' hour to second), --加指定小时到秒
trunc(sysdate)+(interval '01:02' minute to second), --加指定分钟到秒
trunc(sysdate)+(interval '01:02' hour to minute), --加指定小时到分钟
trunc(sysdate)+(interval '2 01:02' day to minute) --加指定天数到分钟
from dual;

to_date to_char 日期字符转换

-- 将日期字符串转换为日期
SELECT TO_DATE('2021-01-31', 'YYYY-MM-DD') FROM DUAL;
-- 将日期转换为字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-- 将日期时间字符串转换为日期时间
SELECT TO_DATE('2021-01-31 12:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
--将日期时间转换为字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
--将时间戳转换为日期时间
SELECT TO_TIMESTAMP('2021-01-31 12:30:00.123456', 'YYYY-MM-DD HH24:MI:SS.FF') FROM DUAL;
--将日期时间转换为时间戳
SELECT TO_TIMESTAMP('2023-01-31 12:30:00.123456', 'YYYY-MM-DD HH24:MI:SS.FF') FROM DUAL;

extract 从日期中提取指定的部分，如年、月、日等

select extract(year from sysdate) from dual;
-- 2024
select extract(month from sysdate) from dual;
-- 7
select extract(day from sysdate) from dual;
-- 23

聚合函数

聚合函数计算从列中取得的值，返回一个单一的值。

avg() - 返回平均值

count( * | [distinct|all]x) - 返回行数

SELECT COUNT(DISTINCT column_name) FROM table_name;

正序取第一个

SQL Server 语法

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name ASC;

Oracle 语法

SELECT column_name FROM table_name
ORDER BY column_name ASC
WHERE ROWNUM <=1;

取最后一个

SQL Server 语法

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;

Oracle 语法

SELECT column_name FROM table_name
ORDER BY column_name DESC
WHERE ROWNUM <=1;

MAX() - 返回最大值

MIN() - 返回最小值

SUM() - 返回总和

集合运算

Union all 并集
Union 并集去重
Intersect 交集
Minus 差集

其他函数

NULL 函数

因为如果值是 NULL 则 ISNULL() 返回 0

SQL Server

ISNULL()

  SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products

Oracle

NVL()

  SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0)) FROM Products

MySQL

IFNULL() 或 COALESCE()

  SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products

sum(...) over ...

sum(…) over( )，对所有行求和

sum(…) over( order by … )，连续求和

sum(…) over( partition by… )，同组内所行求和

sum(…) over( partition by… order by … )，同第1点中的排序求和原理，只是范围限制在组内

decode函数是一种条件表达式，用于实现类似于其他编程语言中的 `switch` 或 `case` 结构的功能

DECODE(value,if1,then1,if2,then2,if3,then3,...,else)

 IF 条件=值1 THEN
　　　　RETURN(value 1)
ELS IF 条件=值2 THEN
　　　　RETURN(value 2)
ELS IF 条件=值n THEN
　　　　RETURN(value 3)
ELSE
　　　　RETURN(default)
END IF

select decode(sex,1,'男生',0,'女生','其他')
select decode(sex,'男生',1,'女生',0,-1)

rank() over(...) 分析函数

值相同，排名相同，序号跳跃

Select rank() over( order by usenum desc) ,t.* from t_account t

值相同，排名相同，序号连续

dense_rank()

序号连续，不管值是否相同

row_number()

伪列序列

rowid ,rownum,

序列，sequence 自增

--创建序列
Create sequence name
increment by 10    --每次增长值
start with 10
maxvalue 300
minvalue 20

--查询下个序列号
select seq_name.nextval from dual
--查询当前序列号
select seq_name.currval from dual

视图

CREATE [OR REPLACE] [FORCE] VIEW view_name 
AS subquery 
[WITH CHECK OPTION ] 
[WITH READ ONLY]

OR REPLACE ：若所创建的试图已经存在，ORACLE 自动重建该视图；
FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图；
subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名；
WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；
WITH READ ONLY ：该视图上不能进行任何 DML 操作。

物化视图

Create materialized view view_name
[build immediate| build deferred 
Refresh [fast | complete | force ]
[
on 
[commit | demand|  start with
(start time) next (next time)
]
AS
subquery[一条完整的select语句]

build immediate 创建物化视图时就生成数据
Build deferred 创建时不生成数据，以后根据需要再生成 ,第一次初始化数据需要手动更新(默认为 BUILD IMMEDIATE)
Refresh 当基表发生DML操作后，物化视图何时采用哪种方式和基表进行同步，

FAST 刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE 刷新对整个物化视图进行完全的刷新。如果选择 FORCE 方式，则 Oracle 在刷新时会去判断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE 的方式。FORCE 是默认的方式。

on commit on demand 。on demand 指需要手动刷新物化视图（默认）。on commit 指在基表发生 commit 操作时自动刷新

手动更新

Begin 
    dbms_mview.refresh(“物化视图表名”，“C”)   --oracle自带的存储过程
end

自动刷新

Create materialized view view_name
Refresh [fast | complete | force ]
on commit

创建增量的物化视图

1、Refresh fast 创建时需要先创建雾化视图日志，记录基表发生了哪些变化，用这些记录去更新物化视图

Create materialized view log on table_name with rowid

2、创建物化视图中的语句，必须要有条件

PL/SQL

代码块

[declare --声明变量]
begin
--代码逻辑
[exception --异常处理]
end;

变量

--声明
变量名 类型(长度)
--赋值
变量名 := 变量值
-- 属性类型 
引用类型（表名.列名%type）
记录型（表名%rowtype）

条件判断

--第一种
if 条件  then
业务逻辑
end if;

--第二种
if 条件 then
业务逻辑
else
业务逻辑
end if;

--第三种
if 条件 then
业务逻辑
elsif 条件 then  --省略一个e
业务逻辑
else
业务逻辑
end if；

WHILE循环

WHILE  total  <= 25000  

LOOP
        total : = total + salary;
END  LOOP;

LOOP循环

exit后面的条件成立了才退出循环

Loop
   exit [when 条件成立];
   total:=total+salary;
end loop;

FOR循环

使用for循环显示20-30
declare
    i number(2) := 20;
begin
    for i in 20 .. 30
    loop
        dbms_output.put_line(i);
    end loop;
end;

游标

--声明
cursor 游标名称(参数) is sql语句
--使用
open 游标名称(参数)
loop 
    fetch 游标名称 into 变量
    exit when 游标名称%notfound
end loop;
close 游标名称

异常处理

exception
 when no_data_found then  -- select into没找到数据
 when too_many_rows then  -- select into返回多条数据
end;

索引

create index 索引名称 on 表名(列名)
--复合索引
create index 索引名称 on 表名(列名，列名)

存储函数

create [or replace] function 函数名称
(参数名称 参数类型，参数名称 参数类型...)
return 结果变量数据类型
is 
变量声明部分
begin
 逻辑部分
 return 结果变量
[exception 异常处理部分]
end;

存储过程

--声明
create [or replace] procedure 存储过程名称
(参数名称 [in|out|inout]参数类型，参数名称 参数类型...)  --- in out
return 结果变量数据类型
is|as 
变量声明部分
begin
 逻辑部分
 return 结果变量
[exception 异常处理部分]
end;

--调用不带传出参数的存储过程
call 存储过程名称

--调用带参数的存储过程
declare
声明返回变量
begin 
 存储过程名称(变量，变量，返回变量)
end;

触发器

Create [Or Replace] Trigger 触发器名
     Before | After
     [Delete ][[Or] Insert] [[Or]Update [Of 列名]]
     On 表名
     [For Each Row ][When(条件) ]
Declare
 ……
Begin
 Plsql 块
End ；

[for each row ] 行级触发器，每次sql语句操作多少行都会触发多少次

前置触发器（BEFORE）
后置触发器（AFTER）

Oracle入门级学习文档

基础

表创建

修改表