oracle数据库基础知识汇总—30天（二）

 1、新建数据库实例

ORA-OO922：选项缺失或无效；用户名和密码设置不合规，当时不会报错，安装时就会出现这个错误

标识符命名规则:
1、必须以字母开始。
2、长度不能超过30个单字节字符。
3、只能包括A-Z，a-z，0-9，_，$和#。
4、不能在相同用户下建立两个同名的对象。
5、不能使用保留字和关键字

 

在同一台机器上，可以同时启动多个数据库实例。

 SQL语句分类： DML数据操作语句（insert，update，delete）；DDL 数据定交语句（create table，drop table），DQL数据查询语句（select）,DCL数据控制语句（commit，rollback）

 桥连接：

oracle也提供jdbc-odbc桥连

配置数据源。管理工具——数据源

 数据行迁移

create table  temp# as select empno，ename from  emp where ename like 'S%'

一起更新多行 如：update emp set （sal，comm） =(select  sal ，comm from emp where ename ='smith');

 

 2、函数

注：在oracle中，字符串常量只能用单引号。

ASCII ：返回与指定字符对应的十进制数；//select ascii('a') a,ascii('b') b, ascii(dummy) from dual;

CHR：给出整数，返回对应的字符： //select CHR(54740)  M,ascii(65) A, ascii(dummy) from dual;

connect：该函数的作用和||的作用是相同的，拼接  //SELECT CONCAT (ENAME,'IS BEST ONE') FROM EMP;  select （ename || “IS BEST ONE) FROM EMP;

ititcap：返回字符串并将第一个字母大小；

instr(C1,C2,I,J) ：在一个字符串中搜索指定的字符，返回发现指定的字符位置；C1 被搜索的字符串，c2希望搜索的字符串，i 搜索开始位置，默认为1 ， j 第几次出现的位置，默认为1

length：返回字符串的长度，可以用在where语句里面。汉字在统计的时候也是1个长度。

lower/upper：返回字符串，并将所有的字条小写\大写

substr（string, start,length)：截取一段字符，start 是开始位置，第一个字符的索引是1，length是长度。

lpad是左填充，rpad是右填充： ，RPAD（‘gao，’10，“*”）不够的字符用*号来填充。

ltrim/rtrim/trim： 删除左边/右边/左右两边符合条件的的字符。如:rtrim('GAJIANDFAAAA','A')  结果‘GAJIANDF’。select trim( 'a' from 'aaabbbaacccaaa') from emp; //结理返回‘bbbaaccc'

replace（‘string’，‘s1’，‘s2’） string希望被替换的字符或变量；s1被替换的字符串；s2要替换的字符串。

 soundex：读音相当的。如 where soundex（ename）=soundex（‘smith’）；

abs：绝对值 

ceil：返回大于或等于给出数字的最小整数，向上取整 //ceil（3.1415926） 得4

floor：对给定的数字取整数，向下取整//ceil（3.999415926） 得3

mod（n1，n2）：返回n1除以n2余数。mod（5，2） 得1

 round和trunc：按照指定的精度进行舍入，round是四设五入。trunc（num，精度）是按精度截取数据，如果没有指定精度默认截取整数。// trunc（56，-1） 结果返回50

 

 exp（n）：返回一个数字e的n次方根

 add_months（column，n）：增加n个月

sysdate：获取系统时间 //select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

last_day：返回当前月份的最后一天 //select last_day(sysdate) from dual;

 months_between(date1,data2) ：计算两个时间相差多少个月

new_time(date,'this','other')：给出在this时区=other时区的日期和时间

next_day（date，’星期X')：给出日期date后的下一个星期x的日期 // select next_day(sysdate,'星期五'）from dual;

 chartorowid：将字会数据类型转换成rowid类型；//select rowid,rowidtochar（rowid）,ename from emp;

convert（c，dset，sset）: 将源字符串sset从一个语言字符集转换成另一个目的的dset字符集 //select convert(':+','we8hp','f7dec') "conversion" from dual;

hextoram ('char‘）：将一个十六进制转换成二进制;

to_char(date,'format')：将字符串转换成指字的字符串，日期格式可以指定显示如to_char(hiredate,'yyyy)  得1990；to_char(sal,'L99G999D99') 得  ￥1,250.00。

9：显示数字，并忽略前0 0：显示数字，如位数不足，则用0补齐 .：在指定位置显示小数点 ,：在指定位置显示逗号 L：在数字前加本地的货币符号 C：在数字前面加国际货币符号 G：在指定位置显示组分隔符 D：在指定位置显示小数点符号（.）

yy：两位数据的年份2004——>04 yyyy：四位数据的年份 2004年 mm：两位数字的月份 8月——>08 dd：2位数据的天  30号——>30 hh24：8点——>20 hh12：8点——>08 mi、ss——>显示分/秒

to_multi_byte：将字符串中的单字节字符转化为多字节字符

to_number：将给出的字符转换成数字

bfilename（dir，file）：指定的一个外部二进制文件（blob）；

convert('x','desc','source')：将x字段或变量的源source转换成desc；

decode：相当于一个swith语句；//select decode(deptno,10,'10号',20,'20号',30,'30号') from emp;

greatest/( char1,char2,char3)： 返回一组数据中较大的数据

UID： 相当于show user；  //select user/UID  dual；

AVG（distinct|ALL  column)：平均值.ALL 表示对所有的值求平均值，distict只对不同的值求平均值 。

distinct：过滤掉重复的值

max/min：最大/小值

sys_context('userenv','syschar' ) ：oracl提供了一套系统的相关信息  //select sys_context("userenv","db_name") from dual;

terminal：当前会话客户所对应的终端标识符

language：语言

db_name：当前数据库名称

nls_date_format：当前会话客户所对应的日期格式

session_user：当前会话客户所对应的数据库用户名

current_schema：当前会话客户所对应的默认方案名

host：返回数据库所在主机的名称

 3.事务transaction

数据库把一系列的对数据的操作（dml）看做一个整体，要么全部成功，要么全部失败，这样的现象就是事务现象。事务具有原子性。

事务和锁，当执行事务操作时（dml语句）

事务的提交commit（）事务先设置成不自动提交

保存点：savepoint（）；保存点只有一次回退的机会，如果回退过一次后保存点就自动消失了。在一个事务中可以保存多个保存点，可以使用rollback to 回退到任意一个保存点。设置保存点是有资源开销的，一旦提交了事务，刚不能回退到任何一个保存点。

 事务的隔离级别：隔离级别定义了事务与事务之间的隔离程度。ANSI/ISO SQL92标准定义了一些数据库操作的隔离级别（这是国际标准化组织定义的一个标准而已，不同的数据库在产现是有所不同。

• 脏读：当一个事务读另一个事读尚未提交的修改的信息时就叫脏读。oracl数据库是没有脏读这种情况的。
• 不可重复读：在同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果。
• 幻读：同一查询在同一事务中多次进行，由于其它事务提交的插入操作，造成本事务的查询结果不一致的现象。

 

隔离级别	脏读	不可重复读	幻读	说明
读未提交（read uncommitted）	可能	可能	可能
读已提交（read committed） 默认	不会	可能	可能
可重复读（repeatable read）	不会	不会	可能
可串行化（serializable)	不会	不会	不会

 

4.oracle中事务处理——事务隔离级别

oracle提供了SQL92标准中的 read committed和serializable，同时提供了非SQL92标准的read-only

oracle的read committed说明：

1. 这是ORACLE缺省的事务隔离级别
2. 保证不会脏读；但可能出现重复读和幻读

oracle的serializable说明：

1.  serializable就是使事务看起来像一个接着一个地顺序执行（从效果上可以这样理解）
2. 仅仅能看见在本事务开始前由其它事务提交的更改和在本事务中所做的更改
3. 保证不会出现脏读、不可重复读和幻读
4. serializable隔离级别提供了read-only画务所提供的读一致性（事务级的读一致性），同时又允许DML（update/insert/delete）操作

 

 5.oracle数据的完整性

在oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。

约束

not null （非空）

unique（唯一）：不能重复，可以为空（null或‘’），允许两个以上的空（null或‘’）

primary key（主键）：不能重复，也不可为空

 primary key和unique的区别：

• 一个表可以有多们unique，但是只能有一个主键；
• 我们每张表都应该有一个主键；
• unique的值可以为空但是primary key不能为空；
• primary key所在列会自动创建索引

foreign key（外键）

• 外键指向主键列；外键也可以指向unique列
•  被引用为外键的表不能删除，要先删除引用
• 外键列的值必须和主键列的值一致
• references  table1（id）
• 建表时，先建主表，再建从表，删除表时，先删除从表，再删除主表
• 外键的列必须在主键列中存在，外键列是可以为空null的

check（检查）

create table user1（id number，sal number check(sal>=1000 and sql<=2000) ,  sex char(2) check  in ('男' ， '女') default '男' ）；

 

增加约束

注意：用alter table命令为表增加约束时，增加 not null约束时，用modify选项，而增加其它约束使用add；

    alter table 表名 add constraint   自定义约束名 经束种类[check | unique | primary key | foreign key | check ]（字段）

    alter table 表名 modify 字段名 not null

    --  alter table tableinfo  add constraint  addcheck  check（address in （’海淀‘，’朝阳‘））

删除约束

alter table 表名 drop constraint 约束名称；主键比较特殊，一个表只有一个主键，所以可以写成： alter table 表名 drop constraint  primary key

特别说明：在删除主键约束时，可能有错误，比如： alter table 表名 drop primary key；  如果两张表存在主从关系，那么在删除主表的主键约束时，必须带上casecade 选项。  

alter table 表名 drop primary key casecade;

 

列级定义和表级定义

create table user（id primary key，name varchar 2 (40))； 列级定义

create table  user（id number，name varchar2(40),canstraint pk_id primary key  （id）) 表级定义