Oracle:基础串记
字符串函数
-- lpad()函数用于左补全字符串；
-- 当原字符串的长度大于预期长度时，实际上进行的是截取字符串操作
select lpad('21',6,'0') from dual;
-- 与之相反的是rpad
select rpad('abc',10,'*') from dual;
--注意：lpad()和rpad()都用于填充字符串，lpad()从左端进行填充，而rpad()从右端进行填充，但是，二者在最终截取字符串时，都是从左端开始截取的
-- lower()返回小写字符串
select lower('ABc') from dual;
-- upper() 返回大写字符串
select upper('abcS') from dual;
-- initcap()单词首字母大写
-- initcap()函数并不能自动识别单词，而是将整个字符串当做了一个单词
-- length()返回字符串长度
select length('abcd ') from dual;
-- substr()截取字符串,
-- 如果不指定长度，那么substr()函数将获取起始位置参数至字符串结尾处的所有字符
select substr('123456789',5,4) from dual;
-- instr()获取字符串出现的位置
select instr('big big big','big') from dual;
-- ltrim()函数——删除字符串首部空格
-- 需要注意的是，空白符不仅仅包括了空格符，还包括Tab键、回车符和换行符。
select ltrim(' abdd') from dual;
-- rtrim()函数——删除字符串尾部空格
select rtrim('asad ') from dual;
-- trim删除字符串首尾空格
select trim(' adada ') from dual;
--to_char
-- 当使用9 来代表数值位时，数值参数中某位为0，则会忽略此位上的数据。此时，应该使用0来代替9
select to_char(0.96,'9.99'),to_char(0.96,'0.00') from dual;
-- chr将ascii码转化为字符串
select chr(65) from dual;
-- translate()替换字符
-- 用于依次替换字符串“56338”的每个字符。替换规则为，从第二个参数“1234567890”中查找欲替换的字符，若无法找到，则不执行任何操作，否则记录其位置，并根据该位置在第三个字符串“'avlihemoqr'”中获得对应的字符
select translate('56338','1234567890','avlihemoqr') result from dual;
数学函数
select abs(-2.1) from dual;
select round(12.11313,2) from dual;
select ceil(21.78) from dual;
select floor(21.78) from dual;
select mod(5,2) from dual;
-- sign 返回数字的正负性
select sign(8) from dual;
select sqrt(4) from dual;
select power(2,3) from dual;
select trunc(224.1213,2) from dual;
--vsize返回数据的存储空间
select vsize('abc1213') from dual;
聚合函数注意事项
所谓聚合函数是指针对多条记录的函数。Oracle最常用的聚合函数包括，max()、min()、avg()、sum()和count()函数。需要注意的是，聚合函数往往返回记录集的统计值，因此，不能与其中的单条记录同时出现.
- count()函数可以有三种方式来进行计数：
count(*)——计算行数、count(column)——计算某列和count(1)——累加1。 - cast 强制转换数据类型
select '123'+200 result from dual;
-- 最常用的场景是转换列的数据类型，以创建新表
create table new_emp as
select cast(sal_id as varchar2(20) ) salary_id,
cast(employee_id as varchar2(20)) employee_id from t_salary
/
查询关键字
Between：取值范围。
● In：集合成员测试。
● Like：模式匹配。
● is null：空值判断。
● all，some，any：数量判断。
● exists：存在性判断。
-- 如何利用exists判式判断记录的存在性
select * from emp e
where exists(select * from t_salary where employee_id=e.employee_id);
联合语句
联合语句是指两个或多个select语句是并列关系，并且对这些select语句所捕获的记录集进行集合操作，以获得最终的结果集。这些联合语句包括以下几种：union查询、union all查询、intersect查询和minus查询。
- union
union查询是指两个查询结果集进行并集操作，并将重复记录剔除，即相当于并集操作之后，再执行一次distinct操作.此外，union操作的各结果集，只要保证列数相同，并且各列的数据类型一致即可，并不要求其具有统一的列名。并集操作的最终结果的列名将统一使用第一个查询语句所获得的列名 - union all
union all查询与union同为并集操作，但union all查询并不删除最终结果集中的重复记录，因此union all的操作要快于union. - intersect
intersect查询用于获得两个结果集的交集 - minus
minus查询可以看做集合间的减法运算，该操作的第一个集合看做被减数，而第二个集合看做减数，那么minus操作将返回第一个结果集中存在，而第二个结果集中不存在的记录
案例：集合并的用法
/* Write your PL/SQL query statement below */
select accepter_id id, count(distinct(requester_id)) num
from (
select accepter_id requester_id,requester_id accepter_id,accept_date
from request_accepted
union all
select * from request_accepted)
group by accepter_id
having count(distinct(requester_id))>=all(
select count(distinct(requester_id)) num
from (
select accepter_id requester_id,requester_id accepter_id,accept_date
from request_accepted
union all
select * from request_accepted)
group by accepter_id);
案例2：579. 查询员工的累计薪水 - 力扣（LeetCode）
-- /* Write your PL/SQL query statement below */
select id,month,
sum(salary) over(partition by id order by month rows between 2 preceding and current row) salary
from (
select * from employee
minus
(select id,month,salary
from (
select id,month,salary,dense_rank() over(partition by id order by month desc) rank_
from employee)
where rank_=1)
)
order by id,month desc;
层次化查询
层次化查询。数据库中的数据具有平面的特点，即每条记录都是独立存在的、相互间的关系是平等和并列的。而层次化是指同一个数据表中多条数据之间存在着父子关系，进而形成了树状结构。
create table market(
market_id number,
market_name varchar2(10),
parent_market_id number
);
insert into market values(1,'全球',0);
insert into market values(2,'亚洲',1);
insert into market values(3,'欧洲',1);
insert into market values(4,'美洲',1);
insert into market values(5,'中国',2);
insert into market values(6,'韩国',2);
insert into market values(7,'朝鲜',2);
insert into market values(8,'英国',3);
insert into market values(9,'德国',3);
insert into market values(10,'法国',3);
insert into market values(11,'美国',4);
insert into market values(12,'墨西哥',4);
insert into market values(13,'巴西',4);
insert into market values(14,'北京',5);
insert into market values(15,'天津',5);
insert into market values(16,'上海',5);
利用connect by进行层次化操作
Oracle 9i及以后版本都提供了connect by查询。connect by可以按照某种规则，来获得节点路径上的节点集合。
-- 查询‘北京’的所有父级市场
select * from market
start with market_id=14
connect by prior parent_market_id=market_id;
MARKET_ID MARKET_NAM PARENT_MARKET_ID
---------- ---------- ----------------
14 北京 5
5 中国 2
2 亚洲 1
1 全球 0
该过程实际是一个递归的过程。当前记录，通过某种条件，来获得下一条记录。新获得的记录，再次利用相同的条件来获得新的记录，直至不再满足条件。
对于connect by来说，关键字prior是必不可少的，否则，将无法执行递归查询动作。但是，无论递归是否正常执行，结果集合中，都至少包含start with条件所获得记录
sys_connect_by_path()函数的使用
sys_connect_by_path()函数与connect by子句有着非常密切的关系。只有含有connect by子句的查询中才可以使用sys_connect_by_path()函数。connect by子句为每条起始记录，通过递归条件，生成一个结果集合，而sys_connect_by_path()则可以将这些结果集合的列值串联为字符串。
select m.*,sys_connect_by_path(market_name,'/') market_path
from market m
start with market_name='天津'
connect by prior parent_market_id=market_id;
给表设置默认值
alter table people modeify(status varchar2(10) default 'ACT');
更新
update table_name set colum=values
删除的是整条数据，所以不需要指定列名
delete from table where
truncate命令删除数据和delete命令删除数据主要有三点不同。
● truncate命令属于DDL（数据库定义语言）范畴，而delete命令是DML（数据库操作语言）范畴。
● truncate命令将一次性删除数据表的所有数据，而delete语句将对数据表中所有记录进行循环处理。
● truncate命令删除的数据将不能回滚，而delete语句在事务提交之前，仍然可以回滚操作。
truncate table table_name;
数据提交与回滚
Oracle中的回滚段是指当DML修改数据库时，用于存储原数据影像的存储空间。当DML修改数据库中的数据（例如，update和delete命令）之后，执行提交之前，如果执行了回滚操作，Oracle将利用回滚段中的数据影像将数据库恢复到修改前的状态。
回滚动作：
(1)用户在提交动作之前，手动执行rollback命令，已放弃该事务对数据库的修改；
(2)事务执行失败，数据库自动执行rollback命令，来恢复事务对数据库的修改。
select * from books;
insert into books(book_id,book_name) values(9,'回滚');
select * from books;
rollback;
select * from books;
提交动作是指将数据库的修改操作永久反映到数据库，并不允许使用回滚操作.
commit；
利用delete命令与truncate命令删除数据有何区别？
delete命令是DML操作，利用该命令删除数据之后，可以利用rollback命令回滚删除操作；而truncate命令是DDL操作，执行该命令将删除某个数据表的所有数据，并且不可回滚。
简述提交动作与回滚动作的实现机制。
Oracle中存在着回滚段的概念。也就是说，尝试修改数据表的数据之后，虽然这种修改已经反映到了实际的数据块，但是数据库也会记录原始数据，以便用户回滚，取消数据修改。提交动作相当于确认数据的修改，并删除了相应的回滚段信息，因此，不能再通过回滚来恢复数据修改。
