Oracle基础小结

1. 数据库

Oracle数据库是数据的物理存储，包括数据文件ORA或者DBF、控制文件、联机日志、参数文件。其实Oracle数据库的概念和其他数据库不一样，这里的数据库是一个操作系统，只有一个库，可以看作是Oracle就只有一个大数据库。

2.实例

 一个Oracle实例有一系列的后台进程和内存结构组成，一个数据库可以有n个实例

3.用户

用户是在实例下建立的，不同实例可以建相同名字的用户。

此处我们可以这样理解：MySQL中每个项目我们会去创建不同的数据库，Oracle中我们会创建新的用户，oracle中用户是管理表的基本单位。

4. oracle中创建用户并授权参考博客：https://blog.csdn.net/duan196_118/article/details/114027273 

创建表空间

create tablespace waterboss
datafile 'c:\waterboss.dbf'
size 100m
autoextend on
next 10m

waterboss 为表空间名称
datafile 用于设置物理文件名称
size 用于设置表空间的初始大小
autoextend on 用于设置自动增长，如果存储量超过初始大小，则开始自动扩容
next 用于设置扩容的空间大小

创建用户

create user wateruser
identified by itcast
default tablespace waterboss

wateruser 为创建的用户名
identified by 用于设置用户的密码
default tablesapce 用于指定默认表空间名称

用户赋权

grant dba to wateruser

解锁scott用户

解锁scott用户，需要超级管理员才有此权限

alter user scott account unlock; 

解锁解锁scott用户的密码【此处也可以用来重置密码】

alter user scott identified by tiger;

创建表

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

5.数据类型

5.1字符型

1. CHAR : 固定长度的字符类型，最多存储 2000 个字节
2. VARCHAR2 :可变长度的字符类型，最多存储 4000 个字节
3. LONG : 大文本类型。最大可以存储 2 个 G

5.2 数值型

NUMBER : 数值类型

例如：NUMBER(5) 最大可以存的数为 99999

NUMBER(5,2) 最大可以存的数为 999.99

5.3 日期型

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

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

5.4 二进制型（大数据类型）

CLOB : 存储字符,最大可以存 4 个 G
BLOB：存储图像、声音、视频等二进制数据,最多可以存 4 个 G

6. 序列

sequence 是oracle提供的用于产生一系列唯一数字的数据库对象。由于oracle中没有设置自增列的方法，所以我们在oracle数据库中主要用序列来实现主键自增的功能。

序列不真的属于任何一张表，但是可以逻辑和表绑定。

默认从1开始，依次递增，主要用来给主键赋值使用。

语法：create sequence 序列名

如：create sequence s_person;

select s_person.nextval from dual;

dual:续表，只是为了补全语法，没有任何意义。nextval：取得序列的下一个内容。currval：取得序列的当前内容。

修改表

增加字段
ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]…)

修改字段
ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]…)

修改字段名
ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名

删除字段名
ALTER TABLE T_OWNERS DROP COLUMN REMARK

删除表

drop table 表名称

数据增删改

INSERT INTO 表名[(列名 1，列名 2，…)]VALUES(值 1，值2，…)
UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，…WHERE 修改条件；
DELETE FROM 表名 WHERE 删除条件;
TRUNCATE TABLE 表名称 ---- 删除表

比较 truncat 与 delete 实现数据删除？

delete 删除的数据可以 rollback

delete 删除可能产生碎片，并且不释放空间

truncate 是先摧毁表结构，再重构表结构
 

三个删除

delete from user; 删除表中全部记录 

drop table user; 删除表结构

truncate table user; 先删除表，再创建表。效果等同于删除表中的全部记录。在数据量大的情况下，尤其在表中带有索引的情况下，该操作效率高。索引可以提供查询效率，但是会影响增删改的效率。

基于伪列的查询

ROWID 具体某一行数据的物理地址 select rowID,t.* from T_AREA t

ROWNUM 每一行的行号,查询后才会标注 select rownum,t.* from T_OWNERTYPE t

oracle中的函数

1. 字符函数

    select upper('yes') from dual; -- YES

    select lower('YES') from dual; --yes

    select replace('Scott','s','Boy') from dual; Boycott        replace(char,str1,str2) 字符串中所有str1换成str2

    select substr('ABCDEF',2,2) from dual; CD       substr(char,m,n) 取出从m字符开始的n个字符的子串　　

    select length('ACD') from dual; 3       length(char) 求字符串的长度　

　 select 'ABCD'||'EFGH' from dual; ABCDEFGH    || 并置运算符
 

2.数值函数

    select round(56.16,-2) from dual; --四舍五入，后面的参数表示保留，round(n,m) 将n四舍五入,保留小数点后m位　　

    select trunc(56.16, -1) from dual; --直接截取，不在看后面位数的数

   select mod(10, 3) from dual; --求余，mod(m,n) m除以n的余数,若n=0,则返回m

   select ceil(10.6) from dual; 11     ceil(n) 大于或等于数值n的最小整数

   select ceil(10.6) from dual; 10    floor(n) 小于等于数值n的最大整数　

   select power(3,2) from dual; 9    power(m,n) m的n次方　　　　　　　

  select sqrt(25) from dual ; 5        sqrt(n) n的平方根　　

   select sign(12) from dual; 1       sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1

3.日期函数

   sysdate 当前日期和时间 select sysdate from dual;

   last_day 　本月最后一天 select last_day(sysdate) from dual;

   add_months(d,n)　当前日期d后推n个月 select add_months(sysdate,2) from dual;

   months_between(d,n) 日期d和n相差月数 select months_between(sysdate,to_date('20020812','YYYYMMDD')) from dual;

   next_day(d,day) d后第一周指定day的日期 select next_day(sysdate,'Monday') from dual;

   DDD 当年第几天,一月一日为001 ,二月一日032 select to_char(sysdate,'DDD') from dual;

   DD 当月第几天 select to_char(sysdate,'DD') from dual;

   D 周内第几天 select to_char(sysdate,'D') from dual; 如　sunday

   DY 周内第几天缩写　　　　　　　select to_char(sysdate,'DY') from dual; 
 

日期可以直接加减：

select sysdate - birthday from user; --查询user出生距离现在多少天

select sysdate + 1 from dual; --算出明天此刻

select months_between(sysdate,birthday) from user; --查询user出生距离现在多少月

select round((sysdate-birthday)/7) from user; --查询user出生距离现在多少周

日期转字符串:

 select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;  --2021-03-09 05:53:19

select to_char(sysdate,'fm yyyy-mm-dd hh:mi:ss') from dual; --  2021-3-9 5:54:3

select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual; -- 2021-3-9 17:56:1

字符串转日期:

select to_date('2021-3-9 17:56:1','fm yyyy-mm-dd hh24:mi:ss') from dual;

hh24 24小时制小时数

Mi 分钟数

条件都不能使用别名，因为在SQL执行时where 条件的优先级高

分页查询

每次查询都会产生一个rownum,所以要子查询来实现分页

视图 

注意：

1.oracle中的number类型对应jdbc的double、long、Integer这些数据类型，但是具体实体使用哪种数据类型要看字段代表的意义。

2.oracle中  ：单引号代表的是字符串，双引号代表的是小写，双引号之间的代表字段或者表名。

oracle是否区分大小写

首先这个问题需要从多方面来回答：

1.在关键字中如selcet from 等这些是不区分大小写
2.在用户名/密码上，这个有设置，默认是区分大小写。可以通过设置将密码不区分大小写，但是用户名就不晓得，其实这个不需要可以去记，因为用户名密码本身就用的比较少，更改也比较少，记住就行了。多测试两下就行了
3，用在表中数据：查询表中数据时候是区分大小写的，如保存abc与 ABC是两个值。
4.用在字段和表上时，这个需要查看建表语句，建表语句中是否增加了双引号，如果不增加双引号，那么可以理解为不区分大小写，因为oracle会自动将不加双引号的转为大写。如果加双引号，需要看单个字段是否全为大写，如果该字段全为大写，那么该字段就不区分大小写。  (若在建表时，字段使用的是小写，那在插入时mybatis会自动转换为大写，导致插入数据不正确。)

oracle强制走索引：

SELECT /*+index(t pk_emp)*/* FROM EMP T   

--强制索引，/*.....*/第一个星星后不能有空格，里边内容结构为：加号index(表名 空格 索引名)。
--如果表用了别名，注释里的表也要使用别名。

如有不同意见，望不吝赐教。