oracle学习笔记--day02

忘是亡心i 2022-06-17 14:13 397阅读 0赞

SQL  
表  
创建表  
1：  
create table 表名(  
列名1 列类型1,  
列名2 列类型2,  
...  
) \[tablespace 表空间名\]  
最后一条定义语句不能加,号  
2：  
create table 表名 as select语句   
根据查询结果创建一个新的表  
列与查询结果中的列相同  
数据与查询结果中的数据相同  
数据类型  
varchar2(n)  
长度可变的字符串  
n指定的是字符串的最大字节数，最大值是4000  
单位是字节  
必须指定n  
一个字符几个字节，和使用的编码有关：  
GBK：英文 1 byte；中文 2 byte；  
UTF-8：英文 1byte；中文 3 byte；  
UTF-16：中英文 2 byte；  
ASCII、iso-8859-1：只能表示英文 1byte。  
查看数据库字符编码：  
select \* from nls\_database\_parameters where parameter ='NLS\_CHARACTERSET';   
修改数据库字符编码：  
ALTER SYSTEM ENABLE RESTRICTED SESSION;   RESTRICTED有限制的  
ALTER SYSTEM SET JOB\_QUEUE\_PROCESSES=0;  queue 队列 process处理  
alter database character set internal\_use utf8;     ZHS16GBK  
操作要求数据库处于 RESTRICTED 模式下  
当其它会话处于活动状态时, 无法执行操作  
关闭、重启数据库生效  
长度可变 length()  
varchar(n)  
varchar和varchar2目前没有区别,  
底层也会转成varchar2,  
不过ocacle以后的版本可能不再支持varchar类型,  
如果想兼容新版本Oracle就不要使用varcha  
建议使用varchar2类型，可以保证更好的兼容性  
char(n)  
固定长度的字符串  
n指定的是字符串的字节数，最大值为2000  
单位是字节  
可以不指定n，默认值为1  
固定长度  length(),实际字节数不够指定的字节数，补空格  
number(p,s)  
整数/浮点数  
p数字的总位数  
s小数点后的位数  
p,s都不是必须的，默认值范围10^(-130) —— 10^126(不包括)   
变长类型，长度为0-22字节  
注意：  
没有指定小数点后的位数，表示整数，插入的数据如果带小数部分，则小数位四舍五入  
如果小数位数不足以保存插入的实际数据，从允许位数的下一位开始四舍五入  
如果指定的小数长度是负数，则表示整数，整数长度为p-s，小数位直接舍弃，不会四舍五入  
date  
日期时间类型  
精确到秒  
默认日期时间格式：日-月-年  
实际保存了时间数据，只不过默认显示格式没有显示  
使用默认日期时间格式的字符串，可以直接给date类型的字段赋值  
sysdate函数  
使用时不能加()  
返回date  
返回服务器前日期时间  
to\_char函数  
to\_char(date,format)  
将日期转换为格式化的字符串  
format:  
yyyy/yyy/yy/y/空  
mm简写月，mon全称月  
09 9月  
Sep September  
dd 当前月的第几天  
ddd 当前年的第几天  
hh24 24小时制，0-23  
hh12、hh 12小时制 1-12  
mi 分钟  
ss 秒数  
\-、空格、\_、\\、/、:普通字符原样输出  
to\_date函数  
to\_date(str,format)  
将一个日期时间格式的字符串转换成一个date类型的数据  
format参照to\_char的format  
mysql：  
date 日期类型，不能保存时间  
time 时间类型，不能保存日期  
datetime 日期时间类型，精确到秒  
timestamp  时间戳类型，精确到微秒  
  
now()系统当前日期和时间  
curdate()当前日期  
curtime()当前时间  
current\_timestamp()  
只能获得到秒  
目前版本还没有获得当前系统时间戳的函数  
可以自己转  
date\_format(date,format)  
date\_format(str, format)  
format:  
%y：年  
%c：月  
%d：日  
%h：小时  
%i：分钟  
%s：秒  
%f: 微秒  
timestamp  
时间戳类型  
精确到微秒  
显示时会显示日期和时间(微秒)及上下午标记 时区   
systimestamp函数  
使用时不能加()  
返回timestamp  
返回服务器前日期时间  
to\_char、to\_date函数  
format  
ff  
微秒  
to\_date不能用  
  
to\_timestamp(str,format)  
将一个日期时间格式的字符串转换成一个timestamp类型的数据  
ffn  n 1-9 应该解读为0.秒  
clob  
Character Large Object  
文本大对象  
就是字符串  
10g:4G  
11g:128TB  
blob   
Binary Large Object  
二进制大对象  
保存二进制数据  
二进制数据的插入依赖于编程语言  
10g:4G  
11g:128TB  
dual表  
哑表  
名称               是否为空? 类型  
\-------------------------- -------- ---  
DUMMY                       VARCHAR2(1)  
一次只能查询出一行，一次只能删一行，一次只能更新一行  
一次只说一句话  
据规定 ，dual表中的内容只有一行，不能多了也不能少了  
会导致：  
drop table t\_a  
ORA-00604: 递归 SQL 级别 1 出现错误  
ORA-01422: 实际返回的行数超出请求的行数  
  
为了测试时，语法的完整性而存在  
  
修改表  
alter table 表名字 add(列名1 类型1,...);添加新的列定义  
alter table 表名字 add(列名1 类型1 约束1,...);  
alter table 表名字 modify(列名1 类型1,...);修改列的类型  
alter table 表名字 modify(列名1 类型1 约束名1,...);  
alter table 表名字 rename column 旧列名 to 新列名;重命名列名  
不可以同时改多列  
alter table 表名 drop column 列名;删除表的列  
删除被外键参照的列时，要级联删除，  
alter table 表名 drop column 列名 \[cascade contraint\[s\]\];  
不会删除其他表的(原外键)列和数据  
rename 旧表名 to 新表名  
如果有字段被外键参照，外键约束参照的表名会自动更新  
删除表  
drop table 表名字 \[cascade contraint\]  
cascade contraint\[s\]  
级联删除(参照当前表的)外键约束  
不会删除其他表的(原外键)列和数据  
约束  
约定的限制  
作用  
保证数据的正确性  
列级约束：在定义列的同时，指定的约束  
表级约束：在列定义完后，再指定的约束  
  
添加  
约束 列级 表级  
主键约束 \[constrint 约束名\] primary key \[constrint 约束名\] primary key(列名)  
外键约束 \[constrint 约束名\] references 表名(列名) \[on delete cascade\] \[constrint 约束名\] foreign key (当前表列名) references 另一表名(列名) \[on delete cascade\]  
唯一约束 \[constrint 约束名\] unique \[constrint 约束名\] unique(列名)  
非空约束 \[constrint 约束名\] not null \[constrint 约束名\] 无  
检查约束 \[constrint 约束名\] check(条件) \[constrint 约束名\] check(条件)  
  
外键约束  
参照引用关系  
外键列的取值必须在被参照的列的值范围内  
  
外键只能参照pk、unique列  
  
on delete cascade  
指定在删除主表记录时，级联删除所有的关联的从表记录  
外键值在默认情况下，可以为null，可以重复  
not null  
unique 一对一关系  
  
非空约束  
只有表级写法  
即没有 not null(列名)的写法  
删除  
alter table 表名 drop constraint 约束名;  
  
表创建之后，添加约束  
列级约束：  
alter table 表名 modify(列名1 类型1 列级约束1,列名2 类型2 列级约束2,...)  
alter table 表名 modify (列名 类型 null)  
表级约束：  
alter table 表名 add (表级约束1,表级约束2,...)  
禁用约束  
 alter table 表名 disable constraint 约束名 \[cascade\]  
 cascade:用来关闭存在有完整性关系的约束，（外键)  
启用约束:  
 alter table 表名 enable constraint 约束名  
  
表数据操作  
增  
方式1：  
insert into 表名 (字段列表) values(值列表)  
如果插入的是所有列的值，字段列表可省略，值列表按列的默认顺序指定值  
一次只能插入一行数据  
方式2：  
根据查询结果，往指定表中插入数据：  
insert into 表名 select语句  
删  
delete from 表名 \[where 条件\]  
删除满足条件的记录(行)  
没有where子句，对于所有的行，条件都默认为true，会删除所有记录  
  
truncate table 表名  
截断表  
先删表，再创建表，会清空数据  
约束也还在  
是DDL  
改  
update 表名 set 字段1=值1,字段2=值2,... \[where 条件\]  
没有where子句，对于所有的行，条件都默认为true，会修改所有记录  
  
  
Oracle数据库中没有自动增长的列类型，如果要使用连续的整数作为逻辑主键的值，可借助于一个Oracle对象，序列  
序列  
sequence  
是Oracle对象  
用于生成连续的整数  
  
创建序列：  
create sequence 序列名  
\[start with num\]  
\[increment by num\]  
\[maxvalue num\]|\[nomaxvalue\]  
\[minvalue num\]|\[nominvalue\]  
\[cycle|nocycle\]  
\[cache num\]|\[nocache\]  
  
start with num  
指定序列从哪个值开始，  
升序默认1，  
降序默认-1  
increment by num  
每次增长的数值，默认值为1  
正数表示升序，  
负数表示降序  
maxvalue num  
指定序列最大能取到的值，最大值10^28-1，28个9  
默认值nomaxvalue  
没有最大值，实际是有限制的  
升序时值为10^27-1，27个9,  
降序时值为-1  
minvalue num  
指定该序列能取到的最小值，最小值-10^26 + 1，26个9  
默认值nominvalue，  
表示没有最小值，实际是有限制的  
升序时值为1  
降序时值为-10^26 + 1，26个9  
序列取值\[minvalue,maxvalue\]  
  
cycle,表示循环取值，  
升序，达到最大值后，从最小值开始重新取  
降序，达到最小值后，从最大值开始重新取  
默认值：nocycle，表示不循环取值，会报错  
CYCLE 必须指定 MINVALUE 为降序，没有最大值、最小值，无法循环  
cache num  
预先生成一些值，存放在缓存中，需要值的时候从缓存中取  
用于指定缓存的序列的值的个数，默认20  
  
属性  
nextval,用于获取序列的下一个值  
currval，用于获取序列的当前值  
调用currval之前，至少调一次nextval  
  
每个表都应该有自己的序列，用于生成自己的连续的逻辑主键值  
  
删除  
drop sequence 序列名;  
  
事务  
transaction  
一组不可再分的操作，由若干个一次操作组成  
做一件事情，需要分为若干个步骤  
一次操作，一条sql语句  
一组不可再分的操作：  
一次数据库操作  
多次数据库操作  
这些操作要成功都成功，要失败都失败  
银行转账：  
account1  
把余额减少  
account2  
把余额增加  
数据可能就不正确了  
事务特性  
ACID  
原子性 Atomicity ætəmˈɪsɪti  
不可再分  
一致性 Consistency kənˈsɪstənsi  
所有的操作要成功都成功，要失败都失败  
保证了数据的正确性  
隔离性 Isolation aɪsəˈleɪʃn  
两个事务中的操作互不干扰  
一个事务访问不到另一个事务的数据  
永久性 Durability djʊərə'bɪlətɪ  
一旦提交，不可回滚  
  
开启事务  
关闭自动提交:set autocommit off|on  就是开启事务  
提交事务  
commit  
回滚事务  
rollback  
回滚到最后一次commit后状态  
导致事务提交：  
commit;  
DDL  
断开连接  
truncate table 表名字;DDL，删除表的有数据，截断表;先删表，再建表  
  
delete可以根据条件删除某些记录，truncate删除所有记录  
delete只删除数据，表空间不删除，truncate删除数据并释放表空间  
delete删除的速度慢，truncate删除速度快  
delete需要提交才可以生效，truncate自动生效  
  
  
  
回滚点  
savepoint 回滚点名字  
rollback to 回滚点名字  
  
取数据  
取数据之前会判断，要取的数据是否正在被一个事务操作，  
就会从这个事务的回滚段中取数据。没有被事务操作，先内存后数据文件  
回滚段Undo Segment  
用于存放回滚数据 Undo Data  
修改之前的原数据   
回滚段在回滚表空间，回滚表空间数据文件:undoxxx.dbf  
一个事务开启，系统会为他分配对应的回滚段，事务提交或回滚后自动释放   
两把锁  
厕所例子  
表级锁  
行级锁  
表级共享锁  
一个事务，要操作某张表时，首先要获得该表的表级锁  
对于所有的事务  
DML、DQL操作，共享表级锁  
DDL操作，独占表级锁时，才能修改表结构  
不和其他事务共享表级锁时  
阻止修改表结构  
事务提交或回滚后会释放表级锁  
  
行级排他锁  
一个事务修改(udpate delete)某一行数据时，  
要先获得这一行的行级锁，之后才能修改这行数据  
阻止两个事务对同一行数据同时修改  
事务提交或回滚后会释放行级锁  
  
  
select ... for update  
for update，指定查询去获取最新的数据，   
没有任何人修改时，说明是最新的  
如果能获得到行级锁，说明这时没有人修改  
获取某一行数据之前，要获得数据行的行级锁  
  
为什么查询操作，也要放到一个事务中？