Oracle基础知识及查询优化

本文来自于同事的学习总结。

DML(Data Manipulation Language)数据操纵语言):适用范围,对数据库中的数据进行一些简单操作,如insert,delete,update,select等。
DDL(Data Definition Language)数据定义语言:适用范围,对数据库中的某些对象(例如,database,table)进行管理,如Create,Alter和Drop。

Oracle基础知识总结

一、基础知识(DML )

1、Select语句

SELECT
  *,/*查询所有字段*/
  column_1, /*字段1*/
  column_2, /*字段2*/
  column_3 as 别名,/*给字段取别名*/
  ...
FROM
  table_name;/*表名*/

2、Order By 、Group By子句

SELECT
    column_1,
    column_2,
    column_3,
    ...
FROM
    table_name
ORDER BY
    column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST]/*ASC 升序可省略,默认。DESC降序*/
    ,column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST]/*NULLS FIRST 空值在前;NULLS LAST空值在后*/
having: 只在在group by后面, 分组后对结果再次过滤

3、Distinct子句

    SELECT DISTINCT column_1,
    		/*可以用来过滤结果集中的重复行,确保SELECT子句中返回指定的一列或多列的值是唯一的*/
    column_2,
        ...
FROM
    table_name;

4、Where子句

SELECT
    column_1,
    column_2,
    ...
FROM
    table_name
WHERE
    search_condition/*where后面指定SELECT语句返回符合搜索条件的行记录*/
ORDER BY
    column_1,
    column_2;
编号运算符描述
1=等于
2!=,<>不等于
3>大于
4<小于
5>=大于或等于
6<=小于或等于
7IN等于值列表中的任何值
8ANY/SOME/ALL将值与列表或子查询进行比较。它必须以另一个运算符(例如:=><)作为前缀。
9NOT IN不等于值列表中的任何值
10[NOT] BETWEEN n AND m相当于[Not] >= n 且 <= y
11[NOT] EXISTS如果子查询返回至少一行,则返回true
12IS [NOT] NULL测试NULL的值

5、And子句

在SELECT,DELETE和UPDATE语句的WHERE子句中使用AND来形成匹配数据的条件

SELECT 
	order_id, 
	customer_id, 
	status
FROM 
	orders
WHERE  
	status = 'Pending'  /*优先级:括号>And>Or*/
	AND customer_id = 2	/*表示并且*/
TRUEFALSENULL
TRUETRUEFALSENULL
FALSEFALSEFALSEFALSE
NULLNULLFALSENULL

6、Or子句

OR运算符组合了布尔表达式,如果其中一个表达式为真(true),则返回true。

SELECT
    order_id,
    customer_id,
    status
FROM
    orders
WHERE
    status = 'Pending'
    OR status = 'Canceled'/*表示或者*/
TRUEFALSENULL
TRUETRUETRUETRUE
FALSETRUEFALSENULL
NULLTRUENULLNULL

7、 Fetch子句

FETCH子句在Oracle中可以用来限制查询返回的行数

[ OFFSET offset ROWS/*跳过行数*/]
 FETCH  NEXT [  row_count/*行数*/ | percent PERCENT/*百分比*/  ] ROWS  [ ONLY/*不重复*/ | WITH TIES/*可重复*/ ]
OFFSET子句

​ OFFSET子句指定在行限制开始之前要跳过行数。OFFSET子句是可选的。 如果跳过它,则偏移量为0,行限制从第一行开始计算。

​ 偏移量必须是一个数字或一个表达式,其值为一个数字。偏移量遵守以下规则:

  • 如果偏移量是负值,则将其视为0。
  • 如果偏移量为NULL或大于查询返回的行数,则不返回任何行。
  • 如果偏移量包含一个分数,则分数部分被截断。
FETCH子句

FETCH子句指定要返回的行数或百分比。

为了语义清晰的目的,您可以使用关键字ROW而不是ROWS,FIRST而不是NEXT。 例如,以下子句的行为和产生的结果相同:

FETCH NEXT 1 ROWS
FETCH FIRST 1 ROW

8、IN子句

Oracle IN运算符可以用来确定值是否与列表或子查询中的任何值相匹配

expression [NOT] IN (v1,v2,...)/*直接输入值*/
expression [NOT] IN (subquery)/*子查询语句*/

9、Between子句

BETWEEN运算符可以用来在Oracle中选择值在一个范围内的行数据

expression [ NOT ] BETWEEN low AND high
  • low 和high - low和hight指定要测试的范围的下限值和上限值。low和hight值可以是文字或表达式。
  • expression - 是low和hight定义的范围内测试的表达式。 为了能够比较,expression,low和hight的数据类型必须是相同的。
  • AND - AND运算符充当占位符来分隔low和hight的值。

10、Like子句

LIKE运算符在Oracle可以用来测试列中的值是否与指定的模式匹配

expresion [NOT] LIKE pattern [ ESCAPE escape_characters ]
  • expression:该表达式是一个列名称或一个表达式,要针对该模式(pattern)进行测试。
  • pattern:该模式是在表达式中搜索的字符串。此模式包含以下通配符:%(百分号)匹配零个或多个字符的任何字符串。_(下划线)匹配任何单个字符。
  • escape_character:escape_character是出现在通配符前面的字符,用于指定通配符不应被解释为通配符而是常规字符。
通配符

% 表示匹配任意多个字符

_表示一个字符

2、Insert语句

insert into 表名 (1,2..) values (1,2)    /* 一一对应即可*/
insert into 表名 values (1,2, ..)                 /* 和表的列一一对应,不建议*/

3、Update语句

update table_name set column_1='name', column_2='woman' where 条件;   /* 不加where子句, 会全部修改*/

4、Delete语句

delete from 表名 where 条件

二、DDL语句(数据定义语言,Data Definition Language)

1、数据库创建

    CREATE DATABASE IF NOT EXISTS dbName CHARACTER SET gbk;
  • 创建数据库: CREATE DATABASE 数据库名称;
  • 创建数据库,判断是否存在,不存在则创建:CREATE DATABASE IF NOT EXISTS 数据库名称;
  • 创建数据库并指定其字符集: CREATE DATABASE 数据库名称 CHARACTER SET 字符集;

2、数据库删除

DROP DATABASE IF EXISTS 数据库名称;/*判断数据库存在,存在则删除,物理删除*/

3、创建表

    CREATE TABLE tableName(
        列名1 数据类型1,
        列名2 数据类型2,
        ....
        列名n 数据类型n,
        [添加约束...]
    );
    复制表: 
    CREATE TABLE 表名 like 被复制的表名;

4、修改表

修改表名:
ALTER TABLE 表名 RENAME TO 新表名;
修改表的字符集:
ALTER TABLE 表名 CHARACTER SET 字符集名称;
添加一列:
ALTER TABLE 表名 ADD 列名 数据类型
修改列名 类型:
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
只修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
删除列:
ALTER TABLE 表名 DROP 列名;

5、删除表

DROP TABLE IF EXISTS 表名;

6、截断表(彻底释放表占用空间)

TRUNCATE TABLE 表名称

7、闪回

在oracle10g之后,为了预防用户的误删除表操作,专门提供了回收站的功能,用户所删除的表默认情况下会在一个回收站之中保存,而用户也可以通过回收站进行表的恢复,所以此技术称为闪回(FLASHBACK)。

查看回收站

SHOW RECYCLEBIN;

这个时候可以发现所有已经删除的表都在回收站之中保存,那么下面就可以使用如下的语法进行表的恢复;

FLASHBACK TABLE 表名称 TO BEFORE DROP;

8、约束

表建立完成后,并不能检查表中的数据是否合法,如果想要针对表中的数据做一些过滤的话,则可以通过约束完成,约束的主要功能是保证表中的数据合法性,按照约束的分类,一共有五种约束:

非空约束

唯一约束

主键约束

检查约束

外键约束

非空约束/NOT NULL/NK

在建表时创建约束:

CREATE TABLE MEMBER(
MID NUMBER,
NAME VARCHAR2(50) NOT NULL,
SEX VARCHAR2(10) NOT NULL,
AGE NUMBER(3),
CONSTRAINT PK_MID PRIMARY KEY (MID),
CONSTRAINT CK_SEX CHECK (SEX IN (‘NAN’,’NV’,’QITA’)),
CONSTRAINT CK_AGE CHECK (AGE BETWEEN 0 AND 250)
);

在创建表后创建约束:

ALTER TABLE MEMBER ADD CONSTRAINT PK_MID PRIMARY KEY(MID);/*添加约束*/

9、视图

在我们之前学过的所有的SQL语法之中,查询操作是最麻烦的,为了更好更快地使用数据库,我们需要创建视图,视图中包含了一些复杂的SQL语句。视图相当于封装SQL语句。

视图创建语法:
CREATE [OR REPLACE] VIEW 视图名称
AS 子查询
WITH READ ONLY/*设置为只读*/

删除视图
DROP VIEW MYVIEW;

三、索引

​ 如何某表的某个字段有主键约束和唯一性约束,则Oracle 则会自动在相应的约束列上建议唯一索引。数据库索引主要进行提高访问速度。

1、建设原则

1、索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询,并且检索行数小于总表行数的5%。则应该考虑。

2、对于两表连接的字段,应该建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。

3、不应该在小表上建设索引。

优缺点:
 1、索引主要进行提高数据的查询速度。 当进行DML时,会更新索引。因此索引越多,则DML越慢,其需要维护索引。 因此在创建索引及DML需要权衡。

2、创建索引

单一索引:

Create Index <Index-Name> On <Table_Name>(Column_Name);

复合索引:

Create Index i_deptno_job on emp(deptno,job);>在emp表的deptno、job列建立索引。

select * from emp where deptno=66 and job='sals' ->走索引。

select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引

select * from emp where deptno=66 ->走索引。

select * from emp where job='sals' ->进行全表扫描、不走索引。

如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引,将会进行全表扫描。

四、SQL优化

当Oracle数据库拿到SQL语句时,其会根据查询优化器分析该语句,并根据分析结果生成查询执行计划。

也就是说,数据库是执行的查询计划,而不是Sql语句。

查询优化器有rule-based-optimizer(基于规则的查询优化器) 和Cost-Based-optimizer(基于成本的查询优化器)。

其中基于规则的查询优化器在10g版本中消失。

对于规则查询,其最后查询的是全表扫描。而CBO则会根据统计信息进行最后的选择。

1、执行顺序

1、先执行From ->Where ->Group By->Order By

2、执行From 字句是从右往左进行执行。因此必须选择记录条数最少的表放在右边。这是为什么呢?

3、对于Where字句其执行顺序是从后向前执行、因此可以过滤最大数量记录的条件必须写在Where子句的末尾,而对于多表之间的连接,则写在之前。因为这样进行连接时,可以去掉大多不重复的项。

2、避免使用 ‘*’

1、SELECT子句中避免使用(‘*’)ORACLE在解析的过程中, 会将*依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

3、索引失效的情况

1、Not Null/Null 如果某列建立索引,当进行

Select * from emp where depto is not null/is null/*索引失效*/

2、索引列上不要使用函数

SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = 'ABC' 
或者
SELECT Col FROM tbl WHERE name LIKE '%ABC%'/*索引失效*/

SELECT Col FROM tbl WHERE name LIKE 'ABC%' /*会使用索引*/

3、索引列上不能进行计算

SELECT Col FROM tbl WHERE col / 10 > 10/*索引失效*/

应该改成

SELECT Col FROM tbl WHERE col > 10 * 10

4、索引列上不要使用NOT ( != 、 <> )

SELECT Col FROM tbl WHERE col ! = 10 

应该 改成:

SELECT Col FROM tbl WHERE col > 10 OR col < 10

4、用UNION替换OR(适用于索引列)

	union:是将两个查询的结果集进行追加在一起,它不会引起列的变化。 由于是追加操作,需要两个结果集的列数应该是相关的。

​ 并且相应列的数据类型也应该相当的。union 返回两个结果集,同时将两个结果集重复的项进行消除。 如果不进行消除,用UNOIN ALL。

​ 用UNION替换WHERE子句中的OR将会起到较好的效果。 对索引列使用OR将造成全表扫描。 注意, 以上规则只针对多个索引列有效,如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低

5、合理利用:EXISTS、IN、NOT EXISTS、NOT IN

​ 参考《MySQL高性能优化规范建议》,这里没有对Oracle数据库下的情况做验证。

五、Oracle常用函数

1、SUBSTR截取字符串函数

 substr(string , a,  b);

注:string为字符串类型,a和b为int类型
1、string 需要截取的字符串
2、a 截取字符串的开始位置(注:当a等于0或1时,都是从第一位开始截取)
3、b 要截取的字符串的长度

2、NVL函数处理空值

nvl(str1,str2)
如果str1为空则返回str2
nvl(str1,str2,str3)
如果str1位空则返回str3,如果不为空则返回str2

3、TRIM函数字符串去空格

LTRIM(str):将字符串左边的空格移除。
RTRIM(str): 将字符串右边的空格移除。
TRIM(str): 将字符串首尾两端的空格移除。

4、数字处理函数

语法:
	ROUND(number,num_digits)
其中Number是需要进行四舍五入的数字;Num_digits为指定的位数,按此位数进行四舍五入
	ABS(n)取绝对值
	CEIL(n)向上取整
	SIN(n)正弦
	COS(n)余弦
	SIGN(n)取符号
	FLOOR(n)向下取整
	POWER(m,n)m的n次幂
	MOD(m,n)取余
	SQRT(n)平方根
	

5、TRUNC截取日期/截取数字类型

--语法格式:
--TRUNC(date[,fmt])
--其中:date 一个日期值;fmt 日期格式。
select trunc(sysdate,'YY-MM-DD') from dual
--语法格式:
--TRUNC(number[,decimals])
--其中: number 待做截取处理的数值;decimals 指明需保留小数点后面的位数,可选项,忽略它则截去所有的小数部分。
--注意:截取时并不对数据进行四舍五入。
select trunc(123.567,2) from dual;--123.56,将小数点右边指定位数后面的截去;
select trunc(123.567,-2) from dual;--100,第二个参数可以为负数,表示将小数点左边指定位数后面的部分截去,即均以0记;
select trunc(123.567) from dual;--123,默认截去小数点后面的部分;

6、逻辑判断

(case 
 	when 判断1 then 结果1 else 结果2 
	when 判断2 then 结果1 else 结果2 
...
end )
--decode(条件,值1,赋值1,值2,赋值2,...赋值n,赋值n,x)
SELECT ID,DECODE(inParam,'para1','值1',
    'para2','值2',
    'para3','值3',
    'para4','值4',
    'para5','值5',
    'para6') name FROM bank
--对inParam进行判断,如果值为'para1'则重新赋值为‘值1’
--如果不在判断范围内的值则赋值为‘para6’

7、分组排序函数

row_number() over()

--row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,
--而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。
select t.*,row_number() over(partition by accno order by createDate) row_number from Test t 

rank() over()

--rank()是跳跃排序,有两个第二名时接下来就是第四名。
select t.*,rank() over(partition by accno order by createDate) rank from Test t 

dense_rank() over()

--dense_rank()也是连续排序,有两个第二名时仍然跟着第三名。
select t.*,dense_rank() over(partition by accno order by createDate) dense_rank from Test t 

8、字符串处理函数

--upper('字符串'|列名称)将输入的字符串转换成大写
--lower('字符串'|列名称)将输入的字符串转换成小写
--initcap('字符串'|列名称),首字母大写
--length('字符串'|列名称);字符串长度
--replace('字符串'|列名称);进行替换

9、时间处理函数

--months_between:获取两个日期之间的月份
select trunc(months_between('1-2月-2017',sysdate)) from dual;

--add_months:几个月之后的日期(加减月份)
select add_months(sysdate, 4) from dual;

--next_day:下一次出现星期X的日期
select next_day(sysdate,'星期日') from dual;

--last_day:获取当月最后一天
select last_day(sysdate) from dual

--to_char:把日期(任意类型)转换成字符串
select to_char(sysdate,'yyyy-mm-dd') from dual;

--to_date:把任意类型转成时间日期
select to_char(to_date('1999-1-1 20:20:20','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual;
©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页