oracle基础一

1.理解oracle中的数据库，表空间，表，数据

 将数据库与现实生活中的储物柜进行类比，则
 数据库相当于一个柜子，
 表空间相当于柜子中的抽屉，
 数据文件相当于抽屉中的文件夹，
 表相当于文件夹中的纸，
 数据相当于写在纸上的信息；

2.理解数据库与实例
 
 数据库相当于一个仓库，存储了各类货物；
 实例相当于出入仓库的搬运工，负责将货物搬入搬出；

3.oracle中三种用户
 sys  超级管理员 维护系统信息管理实例
 system  默认管理员 dba权限，管理用户，权限，存储等
 scott  普通账户 oracle的数据库示范账号

 注意:
  建议日常的管理任务使用system用户
  若需执行备份，恢复，更改数据库的任务，则必须是sys用户

4.oracle卸载
 
 1）如果数据库配置了自动存储管理(ASM)，应该先删除聚集同步服务CSS(Cluster Synchronization Services).
 删除CSS服务的方法是在DOS命令行中执行如下命令：
   localconfig delete

 2）在“服务”窗口中停止oracle的所有服务。

 3）在“开始”菜单中依次选择
 “程序” / “Oracle-OraDb11g_home1” / "Oracle Installation Products" /
 "Universal Install",打开Oracle Universal Installer(OUI)窗口。

 4）单击“卸载产品”按钮，打开“产品清单”窗口。选中要删除的Oracle产品，
  单击“删除”按钮，打开“确认删除”对话框。

 5）在“确认删除”对话框中单击“是”按钮，开始删除选择的Oracle产品。

 6）删除自动存储管理（ASM），在DOS命令行中执行如下命令：
   oracle -delete -asnsid +asm

 7）运行regedit命令，打开注册表窗口。删除注册表中与Oracle相关的内容，具体如下。
 ★删除HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE目录。
 ★删除HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services中所有以oracle或OraWeb为开头的键。
 ★删除HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Eventlog/Application中所有以oracle开头的键。
 ★删除HKEY_CLASSES_ROOT目录下所有以Ora、Oracle、Orcl或EnumOra为前缀的键。
 ★删除HKEY_CURRENT_USER/Software/Microsoft/Windows/CurrentVersion/
  Explorer/MenuOrder/Start Menu/Programs中所有以oracle开头的键。
 ★删除HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI中
  除Microsoft ODBC for Oracle注册表键以外的所有含有Oracle的键。
 其中有些注册表项可能在卸载Oracle产品的时候已经被删除。

 8）删除环境变量中的PATH和CLASSPATH中包含Oracle的值。删除ORACLE_HOME系统变量

 9）删除“开始” / “程序” 中所有Oracle的组和图标。

 10）删除所有和ORACLE相关的目录。
  ★C:\app\asus\oradata
  ★C:\Program Files (x86)\Oracle
  ★C:\Users\asus\AppData\Roaming\PLSQL Developer
  ★ORACLE_BASE目录
  ★C:DOcuments and Setting \系统用户名\LocalSettings\Temp目录下的临时文件。

 11）删除系统安装磁盘中的program files\oracle目录（如C:\Program Files\Oracle）。如果忘记删除，则新安装时会出错。

 注意：在删除安装目录下oracle相关文件的时候可能存在个别文件无法删除的现象，比如说BIN目录下的oci.dll文件，这个是软件 安装后（不管成功与否）自动加载到系统中 

的。解决这个问题的唯一方法就是重启系统，然后再次对此文件进行删除。这样就算是 彻底删干净oracle软件了。

5.sql语言分类
 
 1）DDL数据定义语言
  
  用于创建，修改，删除数据库对象如 create table，alter table，drop table 是自动提交事务

 2）DML数据操纵语言

  用于操纵数据库，包括insert，delete，update，select等

 3）DCL数据控制语言

  用于执行授予权限和撤销权限，包括grant，revoke 且是自动提交事务

 4）TCL事务控制语言

  用于维护数据的唯一性，包括commit,rollback,savepoint

6.SQL语句编写规则
 
 SQL关键字不区分大小写，既可以使用大写格式，又可使用小写格式，或者大小写混用  对象名与列名不区分大小写
字符值与日期值区分大小写  每条SQL语句要以分号结束

。如果语句比较长，可以将语句文本分布到多行上，最后以分号结束

7.数据类型
 
 字符型char,varchar,clob
 char 定长 最大2000 eg:char(10) '小张' 前四个字符放小张后六个字符空格填充 如'小张      ' 10个字符一起全部匹配速度快
 varchar2 变长 最大4000 varchar2(10) '小张' 只分配4个字符 这样节省空间 但是是一个一个匹配的所以速度慢
 clob字符型大对象 最大4g

 数字型number 负10的38次方到 10的38次方，可表示整数可表示小数

 number(5,2) 表示一个小数有5位有效数，2位小数 范围-999.99 到999.99
 number(5) 表示一个5位数，范围-99999 到 99999

 日期类型
 date
 timestamp 能精确到毫秒数

 blob 大二进制类型 存放图片/声音 4g

8.alter table
 
 添加一个字段
 alter table student add 列 数据类型;
 eg：alter table student add classid number(20);

 修改字段的长度
 alter table student modify xm char(30);

 修改字段的类型、名字（前提是不能有数据）
 alter table student modify xm char(30);

 删除一个字段
 alter table student drop column sal;
 
 修改表名字
 rename student to stu;
 
 删除表
 drop table student;

9.oracle中默认的日期格式为“DD-MON-YY”如“09-06月-1990”
 
 改日期的默认格式
 alter sessionset nls_date_format='yyyy-mm-dd'
 修改后可以用我们熟悉的格式添加日期
 insert into student valuse(1,'tom','男','1990-09-08');
 
 也可以使用to_char()函数
 select stuname,to_char(birthdate,'yyyy-mm-dd') "Date" from student;

10.删除数据
 
 delete from student;
 删除所有记录，表结构还在，写日志，可以恢复数据，速度慢
 eg：
 创建保存点
 savepoint a;
 
 delete from student;
 
 回滚
 rollback to a;

 drop table student;删除表结构和数据

 truncate table student;
 删除表中所有记录，表结构还在，不写日志即不使用事务处理，不使用回滚事务，删除的记录无法找回，速度快

11.常用sql语句
 
 desc dept;查看表结构
 clear;清屏
 select distinct deptno,job from emp;取消重复行
 alter user lisi account unlock;解锁账户
 alter user lisi identified by lisi;设置新密码

 登录
 cmd
 sqlplus / as sysdba   sqlplus 用户名/密码

 启动监听
 isnrctl start 监听名

 启动实例
 oradim -starup -sid orcl

 显示用户
 show user;

 创建用户
 create user lisi identified by 密码;

 授予权限分两种

 1）系统权限
 grant create session to lisi;--会话权限 用于登录
 
 grant create table to lisi;--创建表
 
 grant unlimited tablespace to lisi;--赋予表空间
 
 当一个表有创建表和使用表空间的权限时，当前用户对该表也同时有增删改查的权限了

 撤销系统权限
 revoke create session from lisi;
 revoke create table from lisi;
 revoke unlimited tablespace from lisi;

 查看用户有哪些系统权限
 select * from user_sys_privs; 

 2）对象权限
 grant select on tablename to lisi;--赋予当前用户tablename这张表的查询权限给了lisi
 
 grant insert[列] on tablename to lisi;
 
 grant update[列] on tablename to lisi;
 
 grant delete on tablename to lisi;

 grant all on tablename to lisi;

 

 撤销对象权限
 revoke select on tablename from lisi;
 revoke insert[列] on tablename from lisi;
 revoke update[列] on tablename from lisi;
 revoke delete on tablename from lisi;

 revoke all on tablename from lisi;

 

 grant create session to pulic;
 grant create any table to public

 查看用户有哪些对象权限
 select * from user_tab_privs;

 对象权限可以控制到列，但只有添加和修改才能控制到列，查询和删除不能控制到列
 
 select * from user_col_privs;

 show linesize
 set linesize 400 --设置dos窗口行的宽度
 show pagesize
 set pagesize 设置每页显示的行数目

 
 连接
 disc[onnect] 断开连接
 conn [用户/密码 as sysdba] 用户名/密码@网络服务名
 sqlplus /nolog
 startup
 passw[ord]修改密码，若要修改其他用户密码，需要sys/system登录
 exit断开连接同时退出
 @ d:\a.sql 或 start d:\a.sql
 edit d:\a.sql;
 spool 将sqlplus屏幕上的内容输出到指定文件中
 spool d:\b.sql 并输入spool off结束
 
 & 可替代变量，而该变量在执行时，需要用户输入
 select * from emp where job='&job'

 --赋予允许连接 和程序操作
 --grant connect,resource to lisi;

 --删除账号
 drop user lisi;
 drop user lisi cascade;--当用户lisi有其他对象时

 权限的传递
 grant alter any table to lisi with admin option;

 grant select on tablename to lisi with grant option;

 角色就是一个权限的集合
 create role myrole;
 grant create session to myrole;
 grant unlimited tablespace to myrole;
 drop role myrole;

 create table  create any table
 alter table  alter any table
 drop table  drop any table

 表是属于某一个用户的，但是角色不属于某个用户的。角色是共用的。

 alter user scott identified by tiger;--修改密码

 数据库三种验证机制
 1.操作系统验证
 2.密码文件验证
 先把
 F:\app\lz\product\11.2.0\dbhome_1\database\PWDorcl.ora路径下PWDorcl.ora备份然后删掉
 orapwd file=F:\app\lz\product\11.2.0\dbhome_1\database\PWDorcl.ora password=sys entries=10;
 sqlplus 用户/密码 as sysdba
 select * from v$pwfile_users;
 3.数据库验证

 在oracle中“||”拼接合并字符串

12.子查询
 
 分类
 
 单行子查询
  
  单行运算符 = 等
  
 多行子查询
  
  可以向父查询返回多行记录，
  多行运算符 in ,not in ,exists,not exists,all,any等 

 多列子查询
 相关子查询

 在ddl，dml中使用子查询
 1）在create table 中使用子查询
 利用已经存在的表创建新表
 create table emp(empId,ename,hireDate,deptid)
 as
 select a.employeeId,a.employeeName,a.hireDate,a.departmentId from employee a where a.departmentId in(18,38);

 2)在insert中使用子查询
 新表结构预先存在
 insert into tongXunLu(tName,tSex)
 select sName,sSex
 from student;
 
 新表结构不预先存在
 select sName,sSex
 into tongXunLu
 from student
 where 1=1;

13.dual 
 是oracle中的一个虚表，有一行一列，所有者是sys用户，可以别所有用户使用，但用户不能向该表插入数据
 但可以使用该表选择系统变量或计算一个表达式的值
 select sysdate from dual;
 
 单行函数
  日期函数
 
ADD_MONTHS函数：用于返回指定的日期加上指定的月数后的日期值
 
MONTHS_BETWEEN函数：用于返回两个日期之间的月份数
 
LAST_DAY函数：用于返回指定日期对应月份的最后一天
 
NEXT_DAY函数：用于返回指定下一个星期几的日期
 
EXTRACT函数：用于提取日期中的特定部分

 字符函数
 LOWER（c） 小写转换 select lower('SccE') from dual; scce
 UPPER(c) 大写转换 select upper('scce') from dual; SCCE
 LTRIM（c1 [,c2]） 去掉c1左边所包含的c2中的
任何字符。当遇到不是c2中
的字符时结束，并返回剩余
字符串。若c2省略，则默认
为空格 select
ltrim('adminscce','ad') f rom dual; minscce
 RTRIM（c1 [,c2]） 去掉c1右边所包含的c2中的
任何字符。当遇到不是c2中
的字符时结束，并返回剩余
字符串。若c2省略，则默认
为空格 select
rtrim   

 ('adminscce','scce')
from dual; admin 

 REPLACE(c1,c2 [,c3]) 将c1中出现的c2都替换成
c3，并返回剩余的字符串。
c3默认为null。如果c3为
null，则所有出现c2的地方
的字符都被删除；如果c2为
null，则返回c1；如 

 果c1为
null，则返回null select replace('a*b*','*') from dual; ab
 INSTR(c1,c2) 在c1中搜索c2首次出现的位
置，如果找不到则返回零 select instr('abcdbc','bc') from dual; 2
 SUBSTR(c,m [,n]) 返回c的子串，其中m是子串的开始位置，n是子串的长度。如果省略n，则表示截取从m位置开始的所有子串 select substr('scce',2,2) from dual;  cc
 CONCAT(c1,c2) 连接字符串 select concat('scc','e') from dual; scce
 LENGTH(c) 返回字符串长度 select length('scce') from dual; 4

 数学函数
 ABS 取绝对值 select abs(-1) from dual; 1
 CEIL 向上取整 select ceil(1.33) from dual; 2
 FLOOR 向下取整 select floor(1.33) from dual; 1
 POWER 求幂  select power(2,3) from dual; 8
 MOD 取余  select mod(10,3) from dual; 1
 ROUND 四舍五入 select round(12.126,2) from dual; 12.13
 TRUNC 截断  select trunc(12.126,2) from dual; 12,12

 转换函数
 
TO_CHAR：用于将日期或数字以指定的格式转换为VARCHAR2数据类型的值
语法
 
TO_CHAR(d|n [,fmt])
d表示日期，n表示数字，fmt是指定日期或数字的格式

 TO_DATE：将字符类型转换为日期数据类型
语法
TO_DATE(char [,fmt])
fmt指定需要转换的日期格式

 TO_NUMBER：将包含数字的字符串转换为NUMBER数据类型，从而可以对该数据类型执行算术运算。
语法
TO_NUMBER(char)
通常不需要进行转换，因为Oracle可以对数字字符串进行隐式 

 转换

 其他函数
 
NVL：用于将空值替换为指定值
语法
NVL(expr1,expr2)
如果expr1不是NULL，则返回expr1；否则返回expr2
向HR方案中的employees表中添加一个关于奖金的字段bonus，该字段充许为 空

 NVL2：与NVL类似，只是参数有所不同
语法
NVL2(expr1,expr2,expr3)
如果expr1不是NULL，则返回expr2的值；否则返回expr3的值 
 
 DECODE：DECODE函数相当于一条件语句（IF），它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值 
 语法
DECODE(input_value,value,result[,value,result…][,default_result]);
 
input_value表示试图处理的数值。DECODE函数将该数值与一系列的序偶相比较，决定最后的返回结果 
 value表示一组成序偶的数值。如果输入数值与之匹配成功，相应的结果将被返回 
 result表示一组成序偶的结果值 
 default_result表示未能与任何一序偶匹配成功时函数返回的默认值