我见过的最值得收藏的Oracle数据库知识点总结（III）

16.select语句和update语句
--select语句的基本用法
select * from myusers;

--修改李四的工资为5000
--修改李连杰的工资为10000
update myusers set salary=5000 where userid=8;
update myusers set salary=10000 where userid=4;

--修改刘亦菲的性别
update myusers set sex='女' where userid=3;

--修改刘备的密码为445566
update myusers set userpass='445566' where userid=2;

--修改李连杰的出生年月1989-05-12
update myusers set birthday='1989-05-12' where userid=4;

--查询工资大于3000的用户
select * from myusers where salary>=3000;

--查询工资大于3000的用户编号和姓名
select userid,username from myusers where salary>=3000;

--查询出生日期在1990-01-01到1999-12-31之间出生的用户
select from myusers where birthday>= '1990-01-01' and birthday <='1999-12-31';
--between 小值 and 大值
select from myusers where birthday between '1990-01-01' and '1999-12-31';

--dual表 查出来的结果是单行单列的值 sysdate 是系统时间
select sysdate from dual;
select 3*5 from dual;
select 100+1000000 from dual;
--查看当前用户
select user from dual;
--获得当前系统时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--获得主机名
select SYS_CONTEXT('USERENV','TERMINAL') from dual;
--获得当前locale
select SYS_CONTEXT('USERENT','language') from dual;
--获得一个随机数
select dbms_random.random from dual;
--获得系列your_sequence的下一个值
select your_sequence.nextval from dual;
--获得系列your_sequence的当前值
select your_sequence.currval from dual;
--查看dual表的结构
describe dual

--今天加100天等于几月几号
select sysdate+100 from dual;

--给myusers表添加一个address字段
alter table myusers
add address nvarchar2(50) default '银川';
--修改令狐冲住在华山
update myusers set address='华山' where userid=7;

--查找住址不在银川的用户(两种方法)
select from myusers where address !='银川';
select from myusers where address <> '银川';

--把性张的用户住址都改为武当山
update myusers set address='武当山' where username like '张%';

select * from myusers where username like '张%';

--把姓张的且第二个为三，且姓名是三个字的用户住址改为泰山
update myusers set address = '泰山' where username like '张三_';

--查找用户住址在银川或者在泰山的用户名
select username from myusers where address in('银川','泰山');
select username from myusers where address not in('银川','泰山');

select username from myusers where salary > any(1500,2300,4000);
select username from myusers where salary > all(1500,2300,4000);

--把李四的住址改为null
update myusers set address = null where username = '李四';

--查询住址为null的用户
select * from myusers where address is null;

--把午马的工资改为null
update myusers set salary = null where username = '午马';

--查询工资不是数字的用户
select * from myusers where salary is nan;

--修改李四的userid
update myusers set username = '李四' where userid=8;

--查询名字中带有%的用户
select * from myusers where username like '%\%%' ESCAPE '\';

select from myusers where exists (select from myusers);
select from myusers where exists (select from myusers where id=100);
select * from myusers where 1=1;

--如果住址为空，默认显示水帘洞，如果工资为空显示0
select userid,username,NVL(salary,0),NVL(address,'水帘洞') from myusers;

--查找用户名，用户住址
select username,address from myusers;

--统计用户住址个数 count只统计非空的值
select distinct address from myusers;
select count(distinct address) as 城市个数 from myusers;

--查找编号为3的用户 在窗口上输入userid号
select * from myusers where userid=&arg;

--查找salary为1000的用户
select * from myusers where salary=&arg;

--查找住在银川的用户
select * from myusers where address = &arg;

--给用户工资提高10%
select salary*1.1 from myusers;

--给字段起别名(两种方法)
select userid as 用户编号,username 用户名 from myusers;
select userid 用户编号,username 用户名 from myusers;

--字符串的拼接
select userid||username as 编号和用户名 from myusers;
select concat(userid,username) as 编号和用户名 from myusers;

--字符串的单行函数
--concat()函数实现字符串的拼接
select concat(userid,username) as 编号和姓名 from myusers;

--把员工表里的姓名首字母都变成大写
select * from emp;
select initcap(ename) from emp;

--寻找'三丰'在字符串中出现的位置 instr()函数中的位置从1开始
select instr(username,'三丰',1,1) from myusers;

--显示用户表里每个用户姓名的长度
select length(username) as 姓名长度 from myusers;

--显示员工的大写姓名和小写姓名
select lower(ename),upper(ename) from emp;

--把姓名都填充为10位，左边不够补齐号
select lpad(username,10,'') from myusers;
--把姓名都填充为10位，右边不够补齐+号
select rpad(username,10,'+') from myusers;

--去掉左边，右边，两边的空格
select
LTRIM(' Hello Gail Seymour!'),
RTRIM('Hi Doreen Oakley!abcabc','abc'),
TRIM('0' from '000Hey Steve Button!00000')
from dual;

--NVL2,如果有工资则显示'有工资',否则显示'无工资'
select NVL2(salary,'有工资','无工资') from myusers;

--把名字中的'三丰'全部替换为'三郎'
select replace(username,'三丰','三郎') from myusers;

select * from myusers;

--查找发音是wuma的员工
select username from myusers
where SOUNDEX(username) = SOUNDEX('liubei');

--查找发音是skɔt的员工。
SELECT ename FROM EMP
WHERE SOUNDEX(ename) = SOUNDEX('skɔt');

--求子串，显示每个人的名字,不包括姓
select substr(username,2,length(username)-1) from myusers;

单行函数：
单行函数只处理单个行，并且为每行返回一个结果。单行函数分为以下几种：
字符函数：处理字符串
数字函数：用于数学计算
转换函数：数据类型转换
日期函数：处理日期和时间
正则表达式函数：使用正则表达式搜索数据

函数：
CONCAT(x,y) 将x和y拼接起来，并返回新字符串
INITCAP(x) //字符串的首字母变成大写字母 将字母字符串转换为每个词首字母为大写，其他字母为小写

INSTR(x, find_string [, start] [, occurrence])
返回指定字符串find_string在x中数字位置。可以指定开始搜索的位置start，并提供该字符串出现的次数occurrence。start和occurrence默认为1，表示从字符串开始的位置开始搜索，并返回第一次出现的位置

LENGTH(x) 返回表达式中的字符数，同样可以计算数字、日期的长度
LOWER(column|expression) 将字母字符值转换为小写
UPPER(column|expression) 将字母字符值转换为大写
LPAD(x, width [, pad_string]) 在字符串左侧填充pad_string字符，以使总字符宽度为width
RPAD(x, width [, pad_string]) 在字符串右侧填充pad_string字符，以使总字符宽度为width

LTRIM(x [, trim_string])
从x字符串左侧去除所有的trim_string字符串，如果没有指定trim_string字符串，则默认为去除左侧空白字符
RTRIM(x [, trim_string])
从x字符串右侧去除所有的trim_string字符串，如果没有指定trim_string字符串，则默认为去除右侧空白字符
TRIM(trim_string FROM x)
从x字符串两侧去除trim_string字符串

NVL(x, value) 用于将一个NULL值转换为另外一个值。如果x是NULL值的话返回value值，否则返回x值本身
NVL2(x, value1, value2) 如果x不为NULL值，返回value1，否则返回value2
REPLACE(x, search_string, replace_string)
从字符串x中搜索search_string字符串，并使用replace_string字符串替换。并不会修改数据库中原始值。

SOUNDEX(x) 返回代表x字符串的语音的表示形式，可用于查找一些语音相同但是拼写不同的单词
SUBSTR(x, start [, length])
返回字符串中的指定的字符，这些字符从字符串的第start个位置开始，长度为length个字符；如果start是负数，则从x字符串的末尾开始算起；如果length省略，则将返回一直到字符串末尾的所有字符

函数名 说明 举例
ABS(value) 返回value的绝对值 SELECT ABS(10),ABS(-10) FROM dual;返回：10 和 10

CEIL(value) 返回大于或等于value的最小整数 SELECT CEIL(5.8),CEIL(-5.2) FROM dual;返回：6 和 -5

FLOOR(value) 返回小于或等于value的最大整数 SELECT FLOOR(5.8),FLOOR(-5.2) FROM dual;返回：5 和 -6

POWER(value,n) 返回value的n次幂 SELECT POWER(2,1),POWER(2,3) FROM dual;返回：2 和 8

MOD(m,n) 返回m和n取余数的结果 SELECT MOD(8,3),MOD(8,4)FROM dual;返回：2 和 0

SQRT(value) 对value进行开方 SELECT SQRT(25),SQRT(5) FROM dual;返回：5 和 2.23606798

TRUNC(value,n) 对value进行截断。如果n>0，保留n位小数；n<0，则保留-n位整数位；n=0，则去掉小数部分
SELECT TRUNC(5.75),TRUNC(5.75,1), TRUNC(5.75,-1) FROM dual;返回：5、5.7 和 0

ROUND(value[,n]) 对value进行四舍五入，保存小数点右侧的n位。如果n省略的话，相当于n=0的情况。
SELECT ROUND(5.75),ROUND(5.75,1),ROUND(5.75,-1) FROM dual;返回：6、5.8 和 10

TO_CHAR(x [, format])
将x转化为字符串。 format为转换的格式，可以为数字格式或日期格式
例如，将数字转换为字符串
SELECT TO_CHAR(12345.67) FROM dual;
将数字以指定格式输出
例如，以指定99,999.99格式输出12345.67
SELECT TO_CHAR(12345.67, '99,999.99‘) FROM dual;

元素 说明 示例
9 数字位置(9的个数确定了显示的宽度)
SELECT TO_CHAR(1234,’999999’) FROM dual;返回：__1234

0 显示前导0 SELECT TO_CHAR(1234,'099999') FROM dual;返回：001234

$ 浮动的美元符号 SELECT TO_CHAR(1234,'$999999') FROM dual;返回：$1234

L 浮动的当地货币符号 SELECT TO_CHAR(1234,‘L999999') FROM dual;返回：￥1234

. 指定位置的小数点 SELECT TO_CHAR(1234,'999999.99') FROM dual;返回：1234.00

, 指定位置的逗号 SELECT TO_CHAR(1234,'999,999') FROM dual;返回：1,234

EEEE 科学计数法(格式必须指定4个E) SELECT TO_CHAR(1234,'9999.99EEEE') FROM dual;返回：1.23E+03

TO_NUMBER(x [, format])
将x转换为数字。可以指定format格式
例如
SELECT TO_NUMBER('970.13') + 25.5 FROM dual;
SELECT TO_NUMBER('-$12,345.67', '$99,999.99‘) FROM dual;

CAST(x AS type) 将x转换为指定的兼容的数据库类型。
例如
SELECT
CAST(12345.67 AS VARCHAR2(10)),
CAST('05-7月-07' AS DATE),
CAST(12345.678 AS NUMBER(10,2))
FROM dual;

TO_DATE(x [,format])将x字符串转换为日期
例如
SELECT TO_DATE('2012-3-15','YYYY-MM-DD‘) FROM dual;

日期格式：
年
YYYY：4位数字的年，如：2008
YY：2位数字的年，如：08
月
MM：两位数字的月份，如：09
MONTH：月份的全称的大写形式
MON：3位的月份
天
DD：月份中日的2位表示形式
DAY：大写的星期几；Day：表示小写的星期几
小时
HH24：24小时进制 HH：12小时进制
分钟
MI：2位的分钟数
秒
SS：2位的秒数

聚集函数
亦称分组函数、聚合函数。聚集函数可以对行集进行操作，并且为每组给出一个结果。
聚集函数可以使用任何有效的表达式
NULL值在聚集函数中将被忽略
可以在聚集函数中使用DISTINCT关键字，排除重复值

聚集函数分为以下几种
AVG(x)：返回x的平均值
COUNT(x)：返回统计的行数
MAX(x)：返回x的最大值
MIN(x)：返回x的最小值
SUM(x)：返回x的总计值
以下是不常用的
MEDIAN(x)：返回中间值
STDDEV(x)：返回标准偏差
VARIANCE(x)：返回x的方差

单行函数
to_char(),to_number,to_date(),cast()
聚集函数
min(),Max(),avg(),count(),sum()
分组
where xxxx group by xxx having xxx.
select 后出现的字段如果不在聚合函数中，那么就必须在group by 子句中出现。
where 条件中不允许出现聚合函数，但是在having 子句中可以出现。
where\group by\having总结

数据完整性(Data Integrity)是指数据的精确性(Accuracy)和可靠性(Reliability)，它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据完整性分类：
实体完整性
域完整性
引用完整性(参照完整性)
自定义完整性

实体完整性
实体完整性要求每一个表中的主键字段都不能为空或者重复的值。
Primary Key
实现方法：唯一约束;主键约束;

域完整性
域完整性指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等
实现方法:限制数据类型;外键约束;默认值;非空约束

引用完整性
也称之为参照完整性，当更新、删除、插入一个表中的数据时，通过参照引用相互关联的另一个表中的数据，来检查对表的数据操作是否正确。
引用完整性要求关系中不允许引用不存在的实体。
引用完整性与实体完整性是关系模型必须满足的完整性约束条件。
实现方法:外键约束

自定义完整性
用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。
实现方法:存储过程;触发器

表约束的目的：确保表中数据的完整性
常用的约束类型：
主键约束(PRIMARY KEY)
唯一键约束(UNIQUE)
非空约束(NOT NULL)
默认约束(DEFAULT)
检查约束(CHECK)
复合主键
外键约束(FOREIGN KEY)

主键约束的特点：
唯一、不可重复。
非空NULL。
不可修改
其他特点
用户不关心。
对用户没有任何意义。但对程序开发人员，意义重大。

范式：
第一范式：对于表中的每一行，必须且仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性.
第二范式要求非主键列是主键的子集，非主键列活动必须完全依赖整个主键。主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建，主键无法改变，外键关联一个表的主键。主外键关联意味着一对多的关系.
第三范式要求非主键列互不依赖.
第四范式禁止主键列和非主键列一对多关系不受约束
第五范式将表分割成尽可能小的块，为了排除在表中所有的冗余.

第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
一句话：(必须有主键，列不可分)

第二范式（2NF）：表中的每一列都和主键相关，而不能只与主键的某一部分相关。也就是说一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
一句话：当一个表是复合主键时，非主键的字段不依赖于部分主键(即必须依赖于全部的主键字段)。

第三范式（3NF）：要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。（传递依赖）

主键与外键
主键与外键是维护多表关系的有效手段。
注意事项
当主表中没有对应的记录时，不能将记录添加到子表
不能更改主表中的值而导致子表中的记录孤立
子表存在与主表对应的记录，不能从主表中删除该记录
删除主表前，先删子表

用于建立和加强两个表数据之间连接的一列或多列。 通过将表中的主键列添加到另一个表中。可以创建两个表之间的连接。这个主键的列就称为第二个表的外键。外键约束就可以确保添加到外键表中的任何行都在主表中都存在相应的行

在创建外键约束时可以添加ON DELETE CASCADE选项，那么当主表的数据被删除时，子表对应的行同样也自动被删除。
在创建外键约束时可以添加ON DELETE SET NULL 选项，那么当主表的数据被删除时，
子表匹配的相关行的列会被设置为NULL值，而不是被删除

第一范式：有主键，字段都是原子的。
第二范式：去除局部依赖
第三范式：去除传递依赖

常见的有两类连接条件
等值连接：一般使用等于号（=）操作符
非等值连接：使用除了等于以外的运算符，一般使用<>、>、<、<=、>=、LIKE、IN、BETWEEN等运算符
常见的三种连接类型内连接(等值连接)
内连接(inner join on)
左外连接(left join on)
右外连接 (right join on)
全连接(Full outer join on)
交叉连接(cross join)

外连接:可以查询出即使列中包含NULL值的数据也可以查询出来
外部连接运算符 (+) 。在使用(+)的字段，可能包含NULL值

外连接可以分为：左外连接 右外连接

子查询是嵌入到另一个SELECT语句中的一个SELECT语句。通过使用子查询，可以使用简单的语句组成强大的语句。当需要从表中选择行，而选择条件却取决于该表自身中的数据时，子查询非常有用。
子查询主要分为以下两种基本类型
单行子查询
多行子查询
另外，还有以下3种类型
多列子查询
关联子查询
嵌套子查询

单行子查询
内部SELECT语句给外部的SELECT语句返回0行或1行。
可以在SELECT语句后面的WHERE、HAVING、FROM后面放置单行子查询

在单行子查询还可以使用其他比较运算符，如<>、<、>、<=和>=
HAVING是在分组统计后用于过滤行，同样在HAVING子句后面可以跟子查询。单行子查询将返回结果用于HAVING子句过滤分组统计的行

将子查询放置于FROM子句之后为其提供数据。
例如
SELECT empno,ename,dname
FROM
(SELECT e.empno,e.ename,d.dname FROM EMP e,DEPT d
where e.deptno=d.deptno);
使用单行子查询常见错误
如果子查询返回的结果是多行，而其使用的是比较运算符，将会出现错误
子查询不能包含ORDER BY子句，排序必须在父查询中进行

事务：
事务的特点 ACID：
原子性(atomicity)：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。
一致性(consistency)：在事务处理执行前后，数据库是一致的(数据库数据完整性约束)。
隔离性(isolucation)：一个事务处理对另一个事务处理的影响。
持续性(durability)：事务处理的效果能够被永久保存下来 。
一个事务只会有一个结果：要么都成功、要么都失败。

事务的开始DML语句的执行即开始一个事务。
以下情况之一为事务的结束：
显式的结束：执行了commit或是rollback;
隐式的提交：执行了DDL,DCL语句，或是exit退出。
隐式的回滚：系统异常关闭，死机，掉电。

提交或回滚前的数据状态
改变前的数据状态是可以恢复的
执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正
其他用户不能看到当前用户所做的改变，直到当前用户结束事务。
DML语句所涉及到的行被锁定， 其他用户不能操作。

存储过程(procedure),在数据库服务器里保存SQL脚本。
优点：执行速度快，效率高。
缺点：把大量的业务逻辑相关的代码写入存储过程，不便于程序维护和扩展。
也不便于系统移植（跨平台性比较差）。

提交后的数据状态
数据的改变已经被保存到数据库中。
改变前的数据已经丢失。
所有用户可以看到结果。
锁被释放， 其他用户可以操作涉及到的数据。
所有保存点被释放。

函数（Function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

建立存储函数的语法：

CREATE [OR REPLACE] FUNCTION 函数名(参数列表)
RETURN 函数值类型
AS
PLSQL子程序体；

一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。
In类型的参数，只可以接收值，不能再给in类型的参数设置新的值。
但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个。

原则：
如果只有一个返回值，用存储函数；否则，就用存储过程。

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。
触发器的类型
语句级触发器
在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。
行级触发器（FOR EACH ROW）
触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。

触发器可用于
数据确认 
实施复杂的安全性检查
做审计，跟踪表上所做的数据操作等
数据的备份和同步

函数必须有且仅有一个返回值，过程可以没有返回值，也可以有多个返回值。
触发器：用在表上，针对表的操作，触发器会自动执行。