Oracle

1.基础内容

1.基础配置

1.下载安装

1. 基础知识:

   1. Oracle分为服务端和客户端两种;
   2. 安装了服务端就不需要安装客户端,服务端内包含客户端;

2. 下载数据库:

   这里选择的是oracle11g快捷版,与普通版相比缺少个别不常用功能,但是却节约内存和磁盘空间,适用于开发使用;

3. 安装数据库:

   设置密码时不能使用标点符号;

2.启动关闭

1. 使用软件自带的bat脚本;

2. 使用自制数据库脚本,用管理员权限运行;

   @echo off
   for /f "skip=3 tokens=4" %%i in ('sc query OracleXETNSListener') do set "zt=%%i" &goto :next
   :next
   if /i "%zt%"=="RUNNING" (
   echo 已经发现OracleXETNSListener在运行，现在已经停止运行
   net stop OracleXETNSListener
   ) else (
   echo OracleXETNSListener现在处于停止状态,将进行启动
   net start OracleXETNSListener
   )
   pause
   
   for /f "skip=3 tokens=4" %%i in ('sc query OracleServiceXE') do set "zt=%%i" &goto :next
   :next
   if /i "%zt%"=="RUNNING" (
   echo 已经发现OracleServiceXE在运行，现在已经停止运行
   net stop OracleServiceXE
   ) else (
   echo OracleServiceXE现在处于停止状态,将进行启动
   net start OracleServiceXE
   )
   pause

3.配置文件

与mysql不同的是oracle远程连接需要单独的配置文件,并将配置文件的地址写在环境变量中

1.基础配置文件

oci.dll文件位置:oracle\product\11.2.0\server\bin\oci.dll

2.三大配置文件

配置文件位置:oracle\product\11.2.0\server\network\ADMIN\

1. listener.ora

   1. 事例

      LISTENER_CSA =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP)(HOST = ip地址)(PORT = 1521))
            )
          )
        )
      SID_LIST_LISTENER_CSA =
        (SID_LIST =
          (SID_DESC =
            (ORACLE_HOME = oracle根目录)
            (SID_NAME = 实例名称)
          )
        )

2. sqlnet.ora

   可以删除,删除后默认使用tnsnames.ora内的配置

3. tnsnames.ora

   1. 作用:

      配置基础的远程连接的端口号和ip地址

   2. 实例

      自定义服务名 =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 1521))
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = 自定义服务名)
          )
        )

2.基础知识

1.注意事项

1. 在oracle中除了别名外都用单引号,别名用双引号或者直接写.

2. 别名用as连接或空格连接

3. create or replace:有则创建,无则修改:视图/触发器/存储/触发器

4. 虚表:dual;伪列:自己写的列

2.体系结构

结构	作用	注意事项
数据库	一个数据库可以有多个实例;
事例	不同的实例可以创建相同的用户名;
用户	一个用户至少有一个表空间	类似于mysql里的库
表空间	ora或者dbf的数据文件在oracle中宏观
数据	oracle存储文件:ora或者dbf格式文件

3.基础用户

在安装过程会提示重新修改密码sys和system等默认开启的账户密码

用户	默认密码	作用	注意事项
sys	change_on_install	拥有最高权限dba,不能用normal登录,执行数据库的管理任务，实例的数据字典都在SYS下	默认开启
system	manager	没有最高权限,只能用normal登录,用于执行数据库管理任务，有少量的实例对象	默认开启
scott	TIGER	测试账户,用户普通用户权限	没有scott用户则,可以手动执行:官方脚本创建账户
internal	oracle
anmonymous	ANONYMOUS	用于访问ORACLE XML DB知识库的帐户。
dbsnmp	DBSNMP	具有NNECT\RESOUCEHE\SNMPAGENT 权限的角色，可用CATNSMP.SQL脚本删除此用户和角色
sysman	OEM_TEMP	企业管理器OEM密码
outln	OUTLN	有CONNECT和RESOURCE角色

4.权限角色

oracle自创的权限集合

角色	作用	注意事项
connect	连接角色	基本角色
resource	开发者角色
dba	超级管理员角色

3.其他内容

1.基本数据类型

1. 基本数据类型

   类型	含义	存储描述	备注
   VARCHAR2	可变长度的字符串,	0-4000bytes,可做索引的最大长度749	常用:oracle特有类型,考虑兼容性用varchar
   NUMBER(m,n)	数字类型	m为总位数,n为小数位,m-n为整数位	常用:既可以存储整数,又可以存储小数
   DATE	日期（日-月-年）	DD-MM-YY(HH-MI-SS)	常用:
   CHAR	固定长度字符串	0-2000bytes
   INTEGER	整数类型	小的整数
   LONG	超长字符串	最大长度2G,足够存储大部头著作
   FLOAT	浮点数类型	NUMBER(38)，双精度
   TIMESTAMP	日期（日-月-年）	DD-MM-YY(HH-MI-SS:FF3)
   DECIMAL(P,S)	数字类型	P为整数位，S为小数位
   REAL	实数类型	NUMBER(63)，精度更高
   NCHAR	根据字符集而定的固定长度字符串	0-2000bytes
   NVARCHAR2	根据字符集而定的可变长度字符串	0-4000bytes	oracle特有类型,考虑兼容性用varchar

2. 二进制数据类型

   类型	含义	存储描述	备注
   CLOB	字符数据	最大长度4G
   BLOB	二进制数据	最大长度4G
   NCLOB	根据字符集而定的字符数据	最大长度4G
   BFILE	存放在数据库外的二进制数据	最大长度4G
   RAW	固定长度的二进制数据	最大长度2000bytes	可存放多媒体图象声音等
   LONG RAW	可变长度的二进制数据	最大长度2G	可存放多媒体图象声音等
   ROWID	数据表中记录的唯一行号	10bytes	\..\格式，*为0或1
   NROWID	二进制数据表中记录的唯一行号	最大长度4000bytes

2.sql语句

名称	名称	语句	注意事项
DDL	数据定义语言	create/drop/alter
DML	数据操纵语言	insert/delete/update/select
DCL	数据控制语言	grant/revoke/事务/安全

3.约束

详见mysql相关约束

2.基础命令

1.表空间

3. 创建表空间

   create tablespace 表空间名 datafile '路径\文件名.dbf' size 空间大小 [autoextend on next 10m];

4. 创建用户并指定表空间

   create user 用户名 identidfied by 密码 defalult tablespace 表空间名;

5. 给予用户权限

   grant 角色 to 用户名;

6. 删除表空间

   drop tablespace 表空间名;

2.表操作

1. 创建表

   create table 表名(字段名1 约束,字段名2 约束...);

2. 从其他表拷贝结构

   用户名.表名:从其他用户中导入

   create table 表名 as select查询表结构;

3. 删除表

   1. 彻底删除:

      drop table person;

   2. 初始化表:计数器重置,索引删除,不能用于视图,相当于删除以后重建

      truncate table person;

4. 查看当前用户下的所有表

   select table_name from user_tables

3.记录操作

默认使用了事务,建议使用commit

1. 增加记录

   • 标准写法

     insert into 表名 (列名1,列名2...) values (值1,值2...)[,(值1,值2...)...];

   • 简单写法

     insert into 表名 values (值1,值2...)[,(值1,值2...)...];

2. 修改记录

   update 表名 set 列名1=值1 [,值2=值2...] [where 条件];

3. 删除记录

   不加条件默认删除所有记录

   delete from 表名 [where 条件];

4.列操作

可以通过(,)进行分割添加修改多个字段

1. 添加记录

   alter table 表名 add 列名 列类型;

2. 修改类型

   alter table 表名 modify 原列名 新列类型

3. 修改名称

   alter table 表名 rename column 原列名 to 新列名

4. 删除字段

   alter table 表名 drop column 列名

3.查询操作

1.查询函数

1.字符函数

函数	作用	注意事项
upper(‘字符’)	转换为大写字母	只处理英文字符,其他字符不做处理直接输出
lower(‘字符’)	转换为小写字母	只处理英文字符,其他字符不做处理直接输出

2.数字函数

函数	作用	注意事项
round(num,n)	四舍五入num,并保留n位	负数代表整数位,不写n代表0保存到个位,
trunc(num,n)	截取到有效位数	负数代表整数位,不写n代表0保存到个位,
mod(num,n)	求余数	一般不用

3.日期函数

可以直接在时间上进行算数操作

函数	作用	注意事项
sysdate	当前时间	单位天
months_between(d1,d2)	d2与d1之间时间差	单位月

4.转换函数

函数	作用	注意事项
to_char(时间,格式化)	将时间类型转化为char类型
to_date(‘时间’,格式化)	将char类型转化为时间类型

格式化

不区分大小写

格式化	作用	注意事项
YYYY	年	几个Y显示几位数
MM	月
DD	日	一个D:周几,三个D:一年中第几天
HH12/HH	12小时
HH24	24小时
MI	分钟
SS	秒

5.通用函数

函数	作用	注意事项
nvl(a,b)	如果a为null则显示b

6.多行函数

聚合函数:需要查询1条或多条记录

函数名	作用	注意事项
count()	统计记录数
max()	最大值
min()	最小值
avg()	平均值
sum()	求和

2.条件表达式

1.case表达式

1. 当查询结果有数值1时用替代值1代替

   select [字段,]
   	case 字段
   		when '数值1' then '替代值1'
   			when '数值2' then '替代值2'
   				....
   					[else '替代值3']
   					end
   from 表名;

2. 当查询结果在某个范围时用替代值代替

   select [字段,]
   	case
   		when 字段<x then '替代值1'
   			when 字段<y then '替代值1'
   				...
   					[else '替代值3']
   						end
   from 表名;

2.decode函数[不推荐]

oracle专用函数,要与sign联合使用才能使用范围

select [字段,]
	decode(
    	字段,
		'数值1','替代值1',
			'数值2','替代值2',
				....
					'替代值3'
    ) [[as] 别名]
from 表名;

3.关联查询

​ 可以使用A.*=B.*代表一个表的所有信息；

1.等值连接[不推荐]

内部会生成笛卡尔积的表,增加负担,查询结果跟内连接相同,都是现实两表相关联的数据(不存在的不显示)

select * from A,B where A.x=B.x;

2.内连接

1. 无关联关系

   先书写A表数据,依次与B表字段交叉

   select * from A join B;

2. 有关联关系

   select * from 表1 [inner] join 表2 on 表1.x=表2.x;

3.外连接

1. sql通用语法

   select * from 表1 [left/right] join 表2 on 表1.x=表2.x;

2. oracle专属语法

   1. 左连接

      select * from 表1,表2 where 表1.x = 表2.x(+);

   2. 右连接

      select * from 表1,表2 where 表1.x(+) = 表2.x;

4.自关联

表内字段1与字段2存关联关系,虚拟两张以字段1和字段2为主键的表

select * from 表名1 别名1 join 表名1 别名2 on 别名1.字段1 = 别名2.字段2;

5.笛卡尔积[无实际意义]

select * from 表1,表2;

4.其他查询

1.分组查询

oracle中的注意事项:mysql无需注意

1. 只有group by后的列名和被聚合函数修饰的列名,才能出现在select后面;

   select * from 表名 group by 列名;

2. 条件不能使用列名的别名

   select 列名1 别名1 from 表名 group by 列名 having 别名1>200;

3. where只能在表名后,having只能在group by 列名后使用

   select 列名,聚合函数(列名) from 表名 [where] group by 列名 [having];

2.子查询

1. 通过子查询创建表

   create table 表名 as [(]select * from 表名[)];

3.分页查询

4.其他功能

1.序列

1. 创建序列

   create sequence 序列名 [选项1,选项2...];

   选项	作用	默认值	注意事项
   increment by n	设置增量为n	1
   start with n	设置起始位置n	MINVALUE
   minvalue n	设置最小值n	1
   maxvalue n	设置最大值n	最大值
   cache n	设置预缓存个数	20	当目前为a时,缓存到a+n
   cycle/nocycle	是否循环		当设置minvalue和maxvalue时使用,如果设置cycle到极限循环,反之报错
   order/noorder	是否按照顺序

2. 获得序列内容

   dual:虚表,用于补全查询语法无实际作用

   1. 获取下一个内容

      在添加记录时使用序列名.nextval替代原先手写的序列值

      select 序列名.nextval from dual

   2. 获取当前内容

      初始序列不能直接查看当前值,因为当前值还没有创建,必须先获取下一内容然后再查询否则会报错

      select 序列名.currval from dual

3. 删除序列

   drop sequence 序列名;

2.视图

创建一个通过查询语句创建出来的同步原表的虚表(实际不存在),并因此修改视图就是在修改原表可用于同步/屏蔽表中关键字段

1. 创建视图

   1. 创建读写视图

      create [or replace] view 视图名 as 查询语句

   2. 创建只读视图

      create [or replace] view 视图名 as 查询语句 with read only;

2. 删除视图

   drop view 视图名;

3. 修改视图[不推荐]

   update 视图名 set 列名=列值 [条件];

4. 查看所有视图

   select * from user_views;

3.索引

索引就是在表的列上构建一个二叉树,达到大幅度提高查询速率的目的,但是索引会影响增删改的效率

1. 创建单列索引

   1. 创建语法

      create index 索引名 on 表名(列名);

   2. 触发语法

      单行函数,模糊查询都会影响索引的触发.只有列名相同且"x"是该列的值

      select * from where 列名="x";

2. 创建复合索引

   1. 创建语法

      create index 索引名 on 表名(列名1,列名2[,...])

   2. 触发语句

      列名1为优先索引,查询条件只有包含列名1=”列名值”才能触发,且x包含在列值中

      or时不触发索引

      select * from 表名 where 列名1="x" and 列名2="y";

3. 删除索引

   drop index 索引名;

4. 查看索引

   select * from user_indexes;

5.数据库管理

1.用户

要在dba角色权限之下进行操作

1. 创建用户

   create user 用户名 identidfied by 密码 defalult tablespace 表空间名;
   grant 角色 to 用户名;

2. 删除用户

   drop user 用户名 cascade;

3. 查看用户信息

   用户的信息存储在dba_users表中

   select * from dba_users;

4. 锁用户

   alter user scott account unlock;

5. 重置用户密码

   alter user scott indetidied by tiger;

2.sqlplus

oracle自带的命令行客户端

1. 启动sqlplus

   sqlplus /nolog

2. 连接数据库

   1. 常规输入

      conn 用户名/密码@地址 as sysdba;

   2. 防偷窥无密码输入

      conn 用户名/@地址 as sysdba

3. 退出连接

   exit

4. 查看表结构

   desc[ribe] 表名

3.pl/sql

1.基础语法

1. 声明方法

   declare
          i number(2) :=10;  	---直接赋值
          ena emp.ename%type;	---引用型变量
          emprow emp%rowtype;	---记录型变量
   begin
          dbms_output.put_line(i);---输出
          select ename into ena from emp where empno = 7369;
          dbms_output.put_line(ena);
          select * into emprow from emp where empno = 7369;
          dbms_output.put_line('姓名:' || emprow.ename || '工作' || emprow.job);   
   end;

2. if语句

   declare
          i number(3) := ⅈ
   begin
     if i<18 then
       dbms_output.put_line('未成年');
       elsif i<40 then
         dbms_output.put_line('中年人');
         else
           dbms_output.put_line('老年人');
           end if;
   end;

3. loop循环

   1. while循环

      declare
              i number(2) := 1;
      begin
              while i<11 loop
                    dbms_output.put_line(i);
                    i:= i+1;
              end loop;
      end;

   2. exit退出循环

      declare
              i number(2) := 1;
      begin
              loop
                exit when i>10;
                dbms_output.put_line(i);
                i:= i+1;
              end loop;
      end;

   3. for循环

      declare
             
      begin
               for i in 1..10 loop
                   dbms_output.put_line(i);
               end loop;
      end;

4. 游标:对象

   declare
            cursor yb is select * from emp;
            emprow emp%rowtype;    
   begin
            open yb;
                 loop
                      fetch yb into emprow;
                      exit when yb%notfound;
                      dbms_output.put_line(emprow.ename);
                 end loop;
            close yb;
   end;

   declare
            cursor c2(eno emp.deptno%type) 
            is select empno from emp where deptno =eno;
            en emp.empno%type;
   begin
            open c2(10);
                 loop
                   fetch c2 into en;
                   exit when c2%notfound;
                   update emp set sal=sal+100 where empno=en;
                   commit;
                 end loop;
            close c2;
   end;

2.存储过程

类似于无返回值方法

1. 创建存储过程

   默认in,被into或:=赋值必须使用out

   create [or replace] procedure 存储过程名 [out/in] [(参数,参数)]
   is
   	声明;
   begin
   	pl/sql语句;
   end;

2. 调用存储过程

   declare
   	声明;
   begin
   	存储过程名(参数);
   end;

3.存储函数

类似有返回值方法

1. 创建存储函数

   create or replace function 存储函数名[(参数)] return 返回类型
   is
   	声明;
   begin
   	return 返回值;
   end;

2. 调用存储函数

   select 存储函数(参数) from 表名;

4.触发器

就是执行一个规则,在我们做增删改操作的时候,只要满足该规则,自动触发;

1. 创建语句触发器

   create [or replace] trigger 触发器名
   after/before
   insert/update/delete
   on 表名
   declare
   	声明;
   begin
   	操作;
   end;

2. 创建行级触发器

   raise_application_error函数,可以输出-200001~-209999错误编号,和错误信息

   create [or replace] trigger 触发器名
   after/before
   insert/update/delete
   on 表名
   for each row
   declare
   begin
      if :old.sal>:new.sal then
        raise_application_error(-200001,'不能给员工降薪');
      end if;
   end;

   触发语句	:old	:new
   Insert	null	插入后内容
   Update	更新前内容	更新后内容
   Delete	删除前内容	null

3. 实现自增主键[案例]

   ---触发器
   create or replace trigger 触发器名
   before
   insert
   on 表名x
   for each row
   declare
   begin
   	select 触发器名.nextval into 表名x.主键名 from dual;
   end;
   ---触发语句
   insert into 表名x value(主键随机写);

最后更新： 2019年11月06日 11:36

原始链接： https://airbash.cn/2019/11/01/%E6%95%B0%E6%8D%AE%E5%BA%93/Oracle/

ZSP

赏

× 请我吃糖~