高级sql学习——null详解，字符格式详解，随机值查询！！！

一。null详解
null 是oracle 中一个特殊的值，它的含义是”unknown”。如果一个列的内容是null,则说这个列是空值。截止到oracle最新版本，oracle一直将null和空字符串’’<长度为0>同等对待<如’’ is null是true,’’=null为false,如果声明a varchar2:=’’,那么a is null为true,a=’’为false>。

 

null基本知识：

1.限制一个列不能是null 值，可以通过not null 和primary key（相当于not nullunique）约束实现，当然也可以通过check约束<比如a 列不能是null,则是check(a is notnull)>。如果修改一个表的not null 约束，只能通过alter table table_name modify column is null 或alter table table_name modify column is not null 来实现，只能对修改后的列有效。

 

2.null 和空字符串的比较,如果是变长字符类型，传入’’和null 一样，如果是定长的则分情况：在sql 中定长类型和变长一样当作null，但是在PL/SQL 中定长字符类型传入’’,则长度固定，不是null。

 

3.算术表达式和null 运算总为null,实际上所有的操作符除了||连接操作符外，只要有一个操作符为null,则结果为null。

--算术操作，只要有一个操作数为null,则结果为null select null+10,null*10,null-10,null/10 from dual; NULL+10 NULL*10 NULL-10 NULL/10 ---------- ---------- ---------- ---------- --连接操作符||除外，null相当于空字符串 select null||'abc', ''||'abc' from dual; NULL||'ABC' ''||'ABC' ----------- --------- abc abc

 

4.null 在函数中的使用，除了replace,concat,nvl,nvl2 中有参数为null,返回结果不一定是null外，其他的函数只要有参数为null,则返回null，另外组函数有时候会忽略null。

5.null在值比较中的使用。测试一个值是不是null,只能通过is null或is not null来比较。=null是错误的，null和其他任何值都不相等，包括null,如1=null,’a’=null,null=null,’’=null都是返回unkown。null的含义就是lack of data。

 

6.null 在条件中的使用。一个条件的结果是“unknow”相当于false。如select 语句的where条件如果返回UNKOWN,则结果返回0 行。虽然unknow相当于false,但是有一定的区别，比如not false返回true，not unkown还是返回unkown。

 

二。字符格式详解

字符格式
oracle中字符串的比较有两类
1.填补空格的语义比较
2.非填补空格的语义比较

 

填补空格的语义比较：
如果两个字符串有不同的长度，oracle首先会在短的字符串那边末尾添加空格，直到两个字符串长度相等。
oracle然后按字符顺序比较两个字符串的值，如果发现到不同的字符，则按字段顺序认定大小，如果没有发现不同字符，oracle则认为两个字符串相等。这个规则意味着，如果只是尾号中有不同数目的空格，那么则认为两个字符串是相等的。如'a '='a'.

 

非填补空格的语义比较：

oracle通过字符顺序比较两个字符串的值。如果发现到字符值大的则大。如果两个字符串长度不同，则发现到字符值大的则大，否则长度大的则大，比如'b'>'ab','ab'>'a'.如果长度相等，并且各个位置字符相同，则认定为相等。

 

三。随机值查询

DBMS_RANDOM.random        

返回[-2^^31, 2^^31)范围内的整数随机值

DBMS_RANDOM.value

DBMS_RANDOM.value (low IN NUMBER,high IN NUMBER)
无参数返回[0,1)精度最多为38 位的数字(返回小数)，经常使用此函数有参数返回为low<=result<high

DBMS_RADNOM.string(opt char,len number)

返回随机字符串。其中：
opt 是下列字符中的一个（长度只能是1）：
■ 'u', 'U' – 返回字符串是大写希腊字母
■ 'l', 'L' – 返回字符串是小写希腊字母
■ 'a', 'A' – 返回字符串混合大小写希腊字母
■ 'x', 'X' – 返回字符串混合大写希腊字母和数字
■ 'p', 'P' –返回字符串是任何可打印字符串
其他字母返回的是大写希腊字母
len 返回的字符串长度

DBMS_RANDOM.normal

返回标准正态分布结果

 

select dbms_random.random from dual; RANDOM ---------- -234313946 select dbms_random.value from dual; VALUE ---------- 0.49565669 select dbms_random.value(1,1000) from dual; DBMS_RANDOM.VALUE(1,1000) ------------------------- 605.230858839917 select dbms_random.string('X',10) from dual; DBMS_RANDOM.STRING('X',10) -------------------------------------------------------------------------------- LJZ2ODGIQ4 select dbms_random.string('p',10) from dual; DBMS_RANDOM.STRING('P',10) -------------------------------------------------------------------------------- A|xFI}yv75 select dbms_random.normal from dual; NORMAL ---------- -0.7099701

 

sys_guid函数：返回32 位的16 进制表示，RAW 类型。

select sys_guid() from dual; SYS_GUID() -------------------------------- C0E80736EFF349608A62F7E3239D68E8

 

sample 按行的百分比来读,sample 就是读全部，不加一样sample(n),n为[0.000001,100）当然读的也不是严格的百分比，有一定的误差。
sample block 按表的块来读sample block中的sample不可加值，block必须加值，和单独的sample不同sample block(n), n为[0.000001,100）。

select * from test; NAME SEX SAL -------------------------------------------------- ---------- ---------- 张三 1 800 李四 0 1800 王五 2800 select * from test sample; NAME SEX SAL -------------------------------------------------- ---------- ---------- 张三 1 800 李四 0 1800 王五 2800 select * from test sample(30); NAME SEX SAL -------------------------------------------------- ---------- ---------- 张三 1 800 王五 2800 select * from test sample block(30); NAME SEX SAL -------------------------------------------------- ---------- ---------- 张三 1 800 李四 0 1800 王五 2800

 

使用例子：

--随机在table t中取50 条记录 select * from (select * from t order by dbms_random.value) where rownum<51; --生成0 到40之间的整数数，因为默认是0 到1 select round((dbms_random.value)/25*1000,0) from dual; --2.sys_guid() 返回32 位的16进制表示，RAW 类型 select * from (select * from t order by sys_guid()) where rownum<51; --3.sample 大表比较快 另外sample block(n) 按照块数目扫描 --sample（10）是返回表中10%的数据 select * from (select * from t sample(10)) where rownum<51; --有两条数据，任意删除一条 --drop table test; --create table test(a number,b number); --insert into test values(1,2); --insert into test values(2,1); delete from test where (a,b)=( select a,b from ( select a,b from test order by dbms_random.value) where rownum<2 );