数据库

最新推荐文章于 2024-03-16 15:29:24 发布

浪得虚名慕容复

最新推荐文章于 2024-03-16 15:29:24 发布

阅读量277

点赞数

文章标签： mysql oracle

本文链接：https://blog.csdn.net/weixin_43091461/article/details/112192248

版权

这里写自定义目录标题

Oracl
欢迎使用Markdown编辑器

Oracl

一，常见函数

1，去重 Distinct

2，插入数据

	INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

3，Union

Union 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

4，排序 order by

ASC 升序排列

DESC 降序排列

5，分组 GROUP BY

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

6，连接 inner join / left join / right join

INNER JOIN（内连接,或等值连接）**：获取两个表中字段匹配关系的记录。

LEFT JOIN（左连接）：**获取左表所有记录，即使右表没有对应匹配的记录。

RIGHT JOIN（右连接）：** 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BK1EMh7b-1609755302808)(file:///C:/Users/Athena/AppData/Local/Temp/msohtmlclip1/02/clip_image001.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2DWB22UX-1609755302811)(file:///C:/Users/Athena/AppData/Local/Temp/msohtmlclip1/02/clip_image001.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GhRSFzoR-1609755302814)(file:///C:/Users/Athena/AppData/Local/Temp/msohtmlclip1/02/clip_image001.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Oj4ocKaD-1609755302817)(file:///C:/Users/Athena/AppData/Local/Temp/msohtmlclip1/02/clip_image001.png)]

7，中英文转换

把中文的括号转为成英文括号：

To_single_byte(trim(AKA070)) 这个是把AKA070的值的内容里面的括号转化为英文括号

UPDATE TEST_KA20 SET AKA070 = To_single_byte(trim(AKA070)), 
AKA074 = To_single_byte(trim(AKA074)), 
SKD052 = To_single_byte(trim(SKD052))

8， nvl(x,value)

如果 x 为空，返回 value，否则返回 x。

9，nvl2(value1, value2)

如果x非空，返回value1，否则返回value2
(x,value1,value2)
如果 x 非空，返回 value1，否则返回 value2。

10，in与exist , not in与not exist

1， in和exists
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
例如：表A（小表），表B（大表）1：select * from A where cc in (select cc from B)
效率低，用到了A表上cc列的索引；select * from A where exists(select cc from B where cc=A.cc)
效率高，用到了B表上cc列的索引。
相反的2：select * from B where cc in (select cc from A)
效率高，用到了B表上cc列的索引；select * from B where exists(select cc from A where cc=B.cc)
效率低，用到了A表上cc列的索引。

2，not in 和not exists

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG：

请看下面的例子：

create table t1 (c1 number,c2 number); 
create table t2 (c1 number,c2 number); 

insert into t1 values (1,2);  
insert into t1 values (1,3);  
insert into t2 values (1,2);  
insert into t2 values (1,null);  

select * from t1 where c2 not in (select c2 from t2);  
no rows found  
select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);

正如所看到的，not in 出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select语句的执行计划，也会不同。后者使用了hash_aj。  因此，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录，正如上面例子所示。除非子查询字段有非空限制，这时可以使用not in ,并且也可以通过提示让它使用hasg_aj或merge_aj连接

10，grant 赋权

在oracle多用户的情况下，如何能够让ansiyb用户使用ahsicp3下的表，并对其增删改查；或者是ahsicp3用户对ahsiyb用户下的表进行增删改查，这就得用到了同义词和赋权；
 在Synonyms下创建同义词，一般同义词名与需要使用的表的名称一致；
 执行 grant all on  ****  to  ahsicp3,如把ahsiyb下的KA51表授权给ahsicp3，执行下面的命令即可完成授权工作： grant all on ka51 to ahsicp3;

二，日期函数

日期函数对日期进行运算。常用的日期函数有：
1， SYSDATE:显示系统当前日期

select sysdate from dual
select to_char(sysdate, 'yyyyMMdd') xtdate from dual

2，ADD_MONTHS(date,n)
在某一个日期 date 上，加上指定的月数 n，返回计算后的新日期。date 表示日期， n 表示要加的月数。

select add_months(sysdate, 6) six_month_later from dual;

3，LAST_DAY(date)
返回指定日期当月的最后一天。

select last_day(sysdate) from dual;

4，NEXT_DAY(date,char)
返回date日期的下一个周几，周几是由char决定的。
select NEXT_DAY(SYSDATE, ‘星期三’) from dual;
5， MONTHS_BETWEEN(DATE1,DATE2)
计算date1和date2两个日期间的间隔。注意是date1-date2

select months_between(sysdate, ’17-1月-17’) from dual;

6，ROUND(d[,fmt])
返回一个以 fmt 为格式的四舍五入日期值， d 是日期， fmt 是格式
模型。默认 fmt 为 DDD，即月中的某一天。
如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。
如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。

select round(to_date(‘2009-09-25’, ‘yyyy-mm-dd’), ‘MONTH’) round_date from dual;
例：select round(to_date(‘2009-09-25’, ‘yyyy-mm-dd’), ‘YEAR’) round_date from dual;
例：select round(to_date(‘2009-09-25’, ‘yyyy-mm-dd’)) round_date from dual;

TRUNC(d[,fmt])
与 ROUND 对应的函数是TRUNC(d[,fmt])对日期的操作， TRUNC 与 ROUND 非常相似，只
是不对日期进行舍入，直接截取到对应格式的第一天。

select TRUNC(to_date(‘2009-09-25’, ‘yyyy-mm-dd’), ‘MONTH’) trunc_date from dual;
例：select TRUNC (to_date(‘2009-09-25’, ‘yyyy-mm-dd’), ‘YEAR’) trunc_date from dual;
例：select TRUNC (to_date(‘2009-09-25’, ‘yyyy-mm-dd’)) trunc_date from dual;

8，Extract(fmt FROM d)，提取日期中的特定部分。
fmt 为： YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与TIMESTAMP 类型匹配；但HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。
例：EXTRACT 函数示例

Select sysdate “date”,
Extract(year from sysdate) “year”
Extract(month from sysdate) “month”
Extract(day from sysdate) “day”
Extract(hour from systimestamp) “hour”
Extract(minute from systimestamp) “minute”
Extract(second from systimestamp) “second”
From dual;

9，计算两个日期(精确到年月日)的差值
oracle里面我想取两个日期之间的差值，但是都是varchar类型的我改如何sql？比如 20170628-20170301 如何让他的值等于88

select floor( to_date('2017062','yyyymmdd') - to_date('20170301','yyyymmdd')) from dual;
SELECT FLOOR (TO_DATE(AKC194, 'YYYYMMDD') - TO_DATE(AKC192 ,'YYYYMMDD'))FROM KC21_ZY

10，查询表中字段里数据是否有重复

1，查单个字段
SELECT TEST_NAME,COUNT(*) FROM T_TEST GROUP BY TEST_NAME HAVING COUNT(*) > 1
2，查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断
select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1)
3，删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录
DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);
4，查找表中多余的重复记录（多个字段）
select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)
5，删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

三，视图

1，作用

1)提供各种数据表现形式, 可以使用各种不同的方式将基表的数据展现在用户面前, 以便符合用户的使用习惯(主要手段: 使用别名)；
2)隐藏数据的逻辑复杂性并简化查询语句, 多表查询语句一般是比较复杂的, 而且用户需要了解表之间的关系, 否则容易写错; 如果基于这样的查询语句创建一个视图, 用户就可以直接对这个视图进行"简单查询"而获得结果. 这样就隐藏了数据的复杂性并简化了查询语句.这也是oracle提供各种"数据字典视图"的原因之一,all_constraints就是一个含有2个子查询并连接了9个表的视图(在catalog.sql中定义)；
3)执行某些必须使用视图的查询. 某些查询必须借助视图的帮助才能完成. 比如, 有些查询需要连接一个分组统计后的表和另一表, 这时就可以先基于分组统计的结果创建一个视图, 然后在查询中连接这个视图和另一个表就可以了；
4)提供某些安全性保证. 视图提供了一种可以控制的方式, 即可以让不同的用户看见不同的列, 而不允许访问那些敏感的列, 这样就可以保证敏感数据不被用户看见；
5)简化用户权限的管理. 可以将视图的权限授予用户, 而不必将基表中某些列的权限授予用户, 这样就简化了用户权限的定义。

2，创建视图

1权限: 要在当前方案中创建视图, 用户必须具有create view系统权限; 要在其他方案中创建视图, 用户必须具有create any view系统权限. 视图的功能取决于视图拥有者的权限.
2 语法:

create [ or replace ] [ force ] view [schema.]view_name
	                      [ (column1,column2,...) ]
	                      as 
	                      select ...
	                      [ with check option ]                
                          [ constraint constraint_name ]
	                      [ with read only ];

tips:
1 or replace: 如果存在同名的视图, 则使用新视图"替代"已有的视图
 2 force: "强制"创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限
 3 column1,column2,...：视图的列名, 列名的个数必须与select查询中列的个数相同; 如果select查询包含函数或表达式, 则必须为其定义列名.此时, 既可以用column1, column2指定列名, 也可以在select查询中指定列名.
 4 with check option: 指定对视图执行的dml操作必须满足“视图子查询”的条件即,对通过视图进行的增删改操作进行"检查",要求增删改操作的数据, 必须是select查询所能查询到的数据,否则不允许操作并返回错误提示. 默认情况下, 在增删改之前"并不会检查"这些行是否能被select查询检索到. 
 5 with read only：创建的视图只能用于查询数据, 而不能用于更改数据.

3，格式

create view 视图名(可以起别名) as

select…from …

【with read only】 --with read only 表示视图是一个只读视图，只能查询，不能修改。

create or replace view v_emp("编号","姓名‘) as select empno,ename from emp with read only;

四，存储过程

1， dbms_output的用法

dbms_output.put('a'); --写入buffer但不输出
dbms_output.put('b'); --写入buffer但不输出
dbms_output.new_line; --回车(换行)，输出                              
dbms_output.put_line('hello world!'); --输出并换行 
dbms_output.put('d'); --写入buffer不输出

2，存储过程结构

CREATE OR REPLACE PROCEDURE proc1 (       --创建语句
    para1 varchar2,                        ----------参数列表       
    para2 OUT varchar2, 
    para3 IN OUT varchar2
)
AS
    v_name varchar2(20);           ---------------声明变量
BEGIN
   v_name := 'zhangsf';     --从begin 关键字开始为过程的语句块
  para3 := v_name;
   dbms_output.put_line('para3:' || para3);
END;

3， for循环格式

DECLARE
       ls_sae009  varchar2(100);
       ls_skd004  varchar2(100);
    BEGIN
        FOR I IN (select * from ka20 where aae036 = '20200417215701') loop
            select max(aaz235) +1 into ls_skd004 from ka21_cxqm where aae036 like '202004%';
            INSERT INTO ka21_cxqm (aaz235,AKE001,aae140,AKA078,BKA005 , AAE036) VALUES
            (ls_skd004,
            I.AKE001,
            '390',
            '999',
            '0.3000',
            '20200417215701'
            );
        end loop;
END;

五，sql优化

1，分库分表

2，索引

第一，  通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，  可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，  可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，  在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，  通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

第一，  创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，  索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，  当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

3，合理使用，避免使用全局检索

1、在表中建立索引，优先考虑where、group by使用到的字段。
2、尽量避免使用select *，返回无用的字段会降低查询效率。如下：

SELECT * FROM t

优化方式：使用具体的字段代替*，只返回使用到的字段。
3、尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)
SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)

优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用exists代替。如下：

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

4、尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3

（PS：如果or两边的字段是同一个，如例子中这样。貌似两种方式效率差不多，即使union扫描的是索引，or扫描的是全表）

5、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t WHERE username LIKE '%li%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE 'li%'

6、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

7、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t2 WHERE score/10 = 9
SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li'

优化方式：可以将表达式、函数操作移动到等号右侧。如下：

SELECT * FROM t2 WHERE score = 10*9
SELECT * FROM t2 WHERE username LIKE 'li%'

8、当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE 1=1

优化方式：用代码拼装sql时进行判断，没where加where，有where加and。
9. 尽量使用列名(Oracle9i之后, *和列名一样)
在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替。

欢迎使用Markdown编辑器

你好！这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。

新的改变

我们对Markdown编辑器进行了一些功能拓展与语法支持，除了标准的Markdown编辑器功能，我们增加了如下几点新功能，帮助你用它写博客：

全新的界面设计 ，将会带来全新的写作体验；
在创作中心设置你喜爱的代码高亮样式，Markdown 将代码片显示选择的高亮样式 进行展示；
增加了 图片拖拽 功能，你可以将本地的图片直接拖拽到编辑区域直接展示；
全新的 KaTeX数学公式 语法；
增加了支持甘特图的mermaid语法¹ 功能；
增加了 多屏幕编辑 Markdown文章功能；
增加了 焦点写作模式、预览模式、简洁写作模式、左右区域同步滚轮设置 等功能，功能按钮位于编辑区域与预览区域中间；
增加了 检查列表 功能。

功能快捷键

撤销：Ctrl/Command + Z
重做：Ctrl/Command + Y
加粗：Ctrl/Command + B
斜体：Ctrl/Command + I
标题：Ctrl/Command + Shift + H
无序列表：Ctrl/Command + Shift + U
有序列表：Ctrl/Command + Shift + O
检查列表：Ctrl/Command + Shift + C
插入代码：Ctrl/Command + Shift + K
插入链接：Ctrl/Command + Shift + L
插入图片：Ctrl/Command + Shift + G
查找：Ctrl/Command + F
替换：Ctrl/Command + G

合理的创建标题，有助于目录的生成

直接输入1次#，并按下space后，将生成1级标题。
输入2次#，并按下space后，将生成2级标题。
以此类推，我们支持6级标题。有助于使用TOC语法后生成一个完美的目录。

如何改变文本的样式

强调文本 强调文本

加粗文本 加粗文本

标记文本

~~删除文本~~

引用文本

H₂O is是液体。

2¹⁰ 运算结果是 1024.

插入链接与图片

链接: link.

图片: Alt

带尺寸的图片:

居中的图片: Alt

居中并且带尺寸的图片:

当然，我们为了让用户更加便捷，我们增加了图片拖拽功能。

如何插入一段漂亮的代码片

去博客设置页面，选择一款你喜欢的代码片高亮样式，下面展示同样高亮的 代码片.

// An highlighted block
var foo = 'bar';

生成一个适合你的列表

项目
- 项目
  - 项目

项目1
项目2
项目3

计划任务
完成任务

创建一个表格

一个简单的表格是这么创建的：

项目	Value
电脑	$1600
手机	$12
导管	$1

设定内容居中、居左、居右

使用:---------:居中
使用:----------居左
使用----------:居右

第一列	第二列	第三列
第一列文本居中	第二列文本居右	第三列文本居左

SmartyPants

SmartyPants将ASCII标点字符转换为“智能”印刷标点HTML实体。例如：

TYPE	ASCII	HTML
Single backticks	`'Isn't this fun?'`	‘Isn’t this fun?’
Quotes	`"Isn't this fun?"`	“Isn’t this fun?”
Dashes	`-- is en-dash, --- is em-dash`	– is en-dash, — is em-dash

创建一个自定义列表

Markdown

Text-to- HTML conversion tool

Authors

John

Luke

如何创建一个注脚

一个具有注脚的文本。²

注释也是必不可少的

Markdown将文本转换为 HTML。

KaTeX数学公式

您可以使用渲染LaTeX数学表达式 KaTeX:

Gamma公式展示 $\Gamma(n) = (n-1)!\quad\forall n\in\mathbb N$ 是通过欧拉积分

$\Gamma(z) = \int_0^\infty t^{z-1}e^{-t}dt\,.$

你可以找到更多关于的信息 LaTeX 数学表达式here.

新的甘特图功能，丰富你的文章

关于 甘特图 语法，参考这儿,

UML 图表

可以使用UML图表进行渲染。 Mermaid. 例如下面产生的一个序列图：

这将产生一个流程图。:

关于 Mermaid 语法，参考这儿,

FLowchart流程图

我们依旧会支持flowchart的流程图：

关于 Flowchart流程图 语法，参考这儿.

导出与导入

导出

如果你想尝试使用此编辑器, 你可以在此篇文章任意编辑。当你完成了一篇文章的写作, 在上方工具栏找到 文章导出 ，生成一个.md文件或者.html文件进行本地保存。

导入

如果你想加载一篇你写过的.md文件，在上方工具栏可以选择导入功能进行对应扩展名的文件导入，
继续你的创作。

mermaid语法说明 ↩︎
注脚的解释 ↩︎

浪得虚名慕容复

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库

这里写自定义目录标题Oracl一，常见函数1，去重 Distinct2，插入数据3，Union4，排序 order by5，分组 GROUP BY6，连接 inner join / left join / right join7，中英文转换8， nvl(x,value)9，nvl2(value1, value2)10，in与exist , not in与not exist10，grant 赋权二，日期函数三，视图1，作用2，创建视图四，存储过程1， dbms_output的用法五，sql优化1，分库
复制链接

扫一扫