ORACLE学习记录贴 （oracle表列名可以随意大小写，实参不能随意大小写）
对于单引号和双引号，应用于别名时用双引号，应用于where后的字符串时，用单引号
oracle文件类型
数据文件： .dbf
控制文件： .ctl
日志文件： .log
dos命令
dos登录：sqlplus sys/sys as sysdba; 超级管理员登录
sqlplus scott/ren007 普通登录
用户切换：conn sys/sys as sysdba;
conn scott/ren007;
显示当前用户名： show user；
显示所有数据库名字：select name from v$database;
显示指定用户下的表：select * from all_tables a where a.OWNER='SCOTT'; //此处scott是用户名，需要大写并用单引号括起来
使用超级管理员解锁用户：alter user 用户名 account unlock;
使用超级管理员加锁用户：alter user 用户名 account lock;
使用超级管理员来更改用户密码：alter user 用户名 identified by 密码;
oracle五种语句
1. DQL 查询语句 关键字select
2. DML对表增删改 关键字insert，delete，update
3.DDL对表结构进行改变，关键字，drop，create，alter，rename，truncate
4.TCL对事物进行处理，commit，rollback，savepoint。
5.DCL对权限进行操作，grant，revoke
需注意 oracle的sql语句如果包含对null值的运算，得出结果是null值
以下例子年收入包含了奖金，有些奖金是null，得出年收入也是null
关于字段的别名，如果想要限定大小写或者是加上空格，需要用双引号包含住别名
举例：select ename "Ename",sal+comm "年薪 Salay" from emp;
连接操作，用 || 连接字段，结果是字符串表达式。
SELECT ename||job 连接操作 from emp;
SELECT ename||' is a '||job 员工详细信息 from emp;
关键字 distinct用于去重，加在字段前
举例： select distinct deptno from emp;
限定关键字 where 运行解析
对select+where语句解析：
select ename,job,sal from emp where deptno=10;
1.首先根据 from emp查找到表。
2.根据where deptno=10，顺序从上到下排除不满足条件的列。
3.根据 name，job，sal字段选出剩余行中的列
4.如果有orderby和 groupby，则最后执行
字符串和日期
select ename,job,sal from emp where job=’CLICK‘; 条件表达式里单引号内的字符串大小写是敏感的。！！！！
字符串和日期要用单引号括起来，字符串是大小写敏感的。
日期的缺省格式是：'DD-MON-YY''05-JAN-2023'符合要求√'05-01-2023'不符合要求×
yyyy-MM-dd HH:mm:ss SSS Date类型是navicat前端修饰的形式，本质还是'DD-MON-YY'
比较运算符（也可作为单行运算符）
大于：>小于：<等于：=小于等于：<=大于等于：>=不等于：<>
null值不参与运算，结果记录里不包含设计null运算的记录
其他比较运算符
1.between。。and。。
举例：select ename,sal from emp where sal between 1000 and 1500;
结果：
2. in
举例：select * from emp where mgr in(7902,7566,7788);
结果集：
3.like关键字
select * from emp where ename like 'S%';
通配符 包括 % 和_ %表示零或任意长度的字符串，_表示一个长度的任意字符
转义字符
select * from emp where ename like ' %A_B%'; 此时没有转义
使用转义字符：
select * from emp where ename like '%A_B%' ESCAPE '\'; escape '\' 定义转义字符，使\变成转义字符
is null
判断字段是否是null
select * from emp where comm is null;
逻辑比较符
and查找工作是CLERK并且工资大于1000的
联合条件 例子：select * from emp where job='CLERK' and sal >=1000;
or查找工作时CLERK或者工资大于1000
或者条件 例子： select * from emp where job='CLERK' or sal>=1000;
not查找工作不是CLERK。。。的
非条件 例子：select * from emp where job not in ('CLERK','MANGER','ANALYST');
比较符的优先级 (括号可改变优先级)
1. * /+-
2.and
3.or
order by 排序关键字 (asc 升序 desc 降序)
select ename,job,deptno,hiredate from emp order by hiredate asc;
order by 可同时排序两个字段，用逗号隔开
例子：先按照部门号升序，在按照薪资降序
select ename,deptno,sal from emp order by deptno,sal desc;
单组函数（一个输入，产生一个输出）
字符函数：大小写转化函数：lower转化为小写upper转化为大写 initcap
字符处理函数：concat(ob1,ob2)连接 substr（string，first，last）截取length（string） 长度instr（String，目标字符）查询目标字符在string第一次出现的位置lpad/rpad用空格补充字符串
举例： 无视大小写查找blank：select * from emp where lower(ename)='blank';//转化为小写进行比较
select * from emp where upper(ename)='blank';//转化为大写进行比较
select ename,CONCAT(ename,job),LENGTH(ename) ,INSTR(ename,'A') from emp where job like 'SALES%';
select ename,CONCAT(ename,job),LENGTH(ename) ,INSTR(ename,'A') from emp where substr(job,1,5)='SALES';
数字函数：
floor（x）返回小于等于x的最大整数，例子： floor（5.8）=5select floor(2.75) from dual;dual是一个虚表，用于构成完整语句（语法需求）。
mod（x,y） 返回x除以y的余数，例子：mod(8,3)=2
round(x,[y])取整函数例子：round(2.57)=3y表示可选地，整数表示小数点后四舍五入,表示小数点前整数进行对10取四舍五入，对100进行四舍五入。。。。。
例子：round(5.7)=6round(5.78,1)=5.8round(5.8,-1)=10round(4.8,-1)=0round(5.8,-2)=0
trunc（x，[y]）截取，原理同round（）不同点是不考虑四舍五入，直接截取
例子：trunc(5.78)=5
日期函数：
add_months(x,y) 返回x加上y个月的结果，如果是负数，则是减去y个月
举例:select add_months('08-JAN-18',5) from dual;
last_day(x)返回当月最后一天
举例：select last_day('08-JAN-18') from dual;
NEXT_DAY(x,'SATURDAY') 返回x日期的下一个指定天，此处是周六
举例：SELECT NEXT_DAY('08-AUG-2022','SATURDAY') FROM dual;
sysdate 返回现在的时间
举例：select sysdate from dual;
转换函数
隐式转化：即自动类型转换
目前已知：varchar2/char可转化成number/datenumber/date也可以转化为varchar2/char
以下是number/date转化为字符
例子：select * from emp where 1='1';该例子中，‘1’转化为了number型的1，所以等号成立
DML的upadte 举例
字符串转化为日子（隐式）：
update emp set date='09-JAN-09' where ename = 'blake';
字符串转化为数字（隐式）：
update emp set sal=‘1000’ where ename='blake';此例子中，‘1000’隐式转化为number型的1000
显示转化：to_char to_date to_char可用于日期格式转化，删除指定部分，如年月日时分秒，在通过to_date函数转化为日期
/update empab set hiredate=(select to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') from dual) where empno=7196;
字符串转化为日期：
UPDATE EMPAB SET JOB=TO_DATE('1985-12-31', 'yyyy-mm-dd') WHERE ENAME='BLAKE'; 此例子中，香江
to_date()函数，将字符串转化为日期例子：to_date('1999-08-01','yyyy-mm-dd')
日期转化为字符串：
select ename, to_char(hiredate,'fmDD MM YYYY') 上班日期 from emp ;（标准形式）
select * from emp where to_char(hiredate,'YYYY')>1985; （此处形参只有YYYY，只生成年份）
对于日期型的比较，可以以emp表中的hiredate直接比较：
例子：
hiredate<'31-MON-1999'即比较
/此处DD表示两位日期，二号即02，MM表示两位月份，二月即02，YYYY表示四位月份
fm特殊，表示取消补0，即之前的02将会展示为2；
尚补充模块：
空值转化函数：nvl()nvl2()
nvl（）方法，
对数字型： nvl（comm，0）如果comm是空值，返回0
对字符串型号： nvl（comm,to_char(comm),"no commsier"）如果不是空值，返回comm的隐式字符（自动从number转化为varchar2），如果是空值，则返回字符串“no commsier”
//上面此处comm数number类型，所以用to_char转化，如果本身是varchar2则不用
对日期型：nvl(hiredate,'31-DEC-99')如果不是空值，返回日期，如果是空值，返回'31-DEC-99‘日期
nvl2（表达式1，表达式2，表达式3）方法
如果表达式1为空，返回值为表达式3的值。如果表达式1不为空，返回值为表达式2的值。
例子：nvl2(comm,comm+100,100)
instr方法
（字符串，字符）返回字符在字符串第一次出现的位置，如果没有，返回0，注意：下标从1开始
举例：select instr('asdasdasd','a') from dual; 此结果返回1， 因为在第一个位置
trim方法：
参数：
leading 表示删去开头字符
trailing 表示删除结尾字符
both 表示删除 开头和结尾字符
例子1：select trim(leading 'x' from 'xdylan') "test_trim" from dual;
结果：test_trim|
--------------------
dylan|
例子2 select trim(trailing 'x' from 'dylanx') "test_trim" from dual;
结果：
test_trim
--------------------
dylan
例子3 select trim('x' from 'xdylanx') "test_trim" from dual; //删除开头和结尾的指定字符
select trim(both from ' dylan ') "test_trim" from dual; //删除开头和结尾的指定字符
结果：
test_trim
--------------------
dylan
replace(表达式1，表达式2，表达式3) 从表达式1中将表达式2替换成表达式3.
例子：replace(ename,'A','*'); 将
decode()函数：DECODE(条件，值 1，返回值 1，值 2，返回值 2， ...值 n,返
回值 n，默认值)
例子：select ename,job,sal,DECODE(job, 'ANALYST',sal*1.2,'CLERK',sal*1.3,'MANAGER',1.15*sal, sal) "new sal"from emp
ORDER BY "new sal" desc;//此例子中，通过岗位（varchar2类型）比较，执行加薪操作
结果：
嵌套函数
举例：select empno,ename,job,nvl(to_char(mgr),'NO Manager'),hiredate,sal,nvl(to_char(mgr),'NO COMM'),deptno from emp where mgr is null;
嵌套使用了nvl和to_char
多表连接
固定格式： 以左连接举例：from 表1 left join 表2 on 表1.字段=表2.字段 【where】
默认四个链接：左连接，右连接，全连接，内连接
左连接left join以左表为基准，如果右表没有匹配的字段，进行空值补充
右连接right join以右表为基准表，如果左表没有匹配字段，进行空值补充
内连接inner join当存在两个表的等值字段，返回数据
全连接full join返回两个表的等值字段和不等值字段，不等值字段没匹配上的用null值补充
其他链接： 自连接 select e1.empno,e1.ename,e1.mgr,e2.ename mgrname from emp e1,emp e2 where e1.MGR=e2.EMPNO;
对同一张表起了两个不同名字。通过将领导编号和员工编号相连接查询出结果。
组函数
定义
一组数据返回一条结果
分组函数分组函数默认省略null，即实参如果是null，不会参与计算
avg()求平均数
sun（）求和
count（）计数count（comm） 返回的是非空记录数 ,即comm不为空的记录
max（）求最大值 对max（avg（））和group by的搭配，返回的是一个值 例子： select max(avg(sal)) from emp group by deptno;
min（）求最小值
分组关键字（直观）
group byselect后面的col字段只能是group by后面出现的col字段或者是having后出现的函数
例子：select * from emp group by deptno;
having关键字用于对group by分组后的数据进行约束过滤
例子：select deptno,round(avg(sal),2) from emp group by deptno having avg(sal)>2000;对分组后的结果进行工资过滤
order by 放在group by后面，用于排序
关键字完整执行流程 **************************
1.from
2.where
3.group by
4.having
5.select
6.order by
多行子查询 (即子查询返回结果是多行)
1.多行比较运算符
2.比较运算符的使用：
（1）any关键字例子：获取薪资小于任意job是clerk的员工信息
select * from emp where sal < any(select sal from emp where job='CLERK') and job<> 'CLERK';
（2）all关键字例子：查询大于所有平均工资的结果：
select empno,ename,job ,sal from emp where sal>all(select avg(sal) from emp group by deptno);
（3）in关键字例子：获取没有员工的部门号
select deptno from dept where deptno not in(select distinct deptno from emp);
3.子查询结果为null，则sql语句执行结果为null
单行运算符（子查询只返回一条）
单行运算符：=，<,>,<=,>=,<>
注意：单行运算符只能接收一条子查询的返回语句，如果子查询返回多行，就会报错。
大
什么时候用子查询：查询目标数据不明确时，使用子查询非常有用
什么时候用group by，当需要用到组函数如sum（），max（） 等方法时。
DML（insert delete update）
在一张表中插入（拷贝）其他表数据：不用values关键字了
例子：insert into bonus(ename,job,sal,comm) select ename ,job,sal,comm from emp;
insert:例子： insert into empab(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7196,'GREEN','SALESMAN',7782,SYSDATE,2000,null,10);
update： 修改多列例子：update empab set (job,deptno) =(select job,deptno from emp where empno=7499 )
where empno=7698;
delete:删除记录 格式：delete [from] table where....;
例子：删除bonus表中所有记录 delete bonus;(from 可有可无)
删除empab表中部门编号是10的记录：delete from empab where deptno=10;
删除数据如果涉及约束性条件（外键，表与表之间的关联关系），删除会不成功（存有这种情况）
事务 （TCL对事务进行操作）
常见操作：commit，rollback，savepoint
数据库事务的组成：可以是一组dml，一句ddl或者一句dcl语句
什么时候事务自动提交：
1.执行ddl语句时
2.执行一个dcl语句
3.从sql*plus正常退出时
当sqlplus被强制退出或者系统失败时，系统自动回滚。
需注意：当一个用户对一条记录进行操作时，默认对该记录施加，其他用户无法对其进行操作。commit后释放锁，所有回滚点失效。
commit：只有执行（commit；），执行的sql才会在数据表生效。
savepoint： 例子： savepoint a1; 设置了回滚点 a1；
rollback: 例子：回滚到a1 rollback to savepoint a1;
当commit后，事务已经结束，此时用rollback已经没用
DDL（操作数据库对象）
引用其他用户表：
以sys为例： select * from scott.emp;
查看当前用户下所有的表：
select * from user_tables;此处user_tables是数据字典，是一个虚拟的表，同dual一样。
查看该用户下的数据库对象(包括表，索引，视图)：
select * from user_objects;
创建的对象名字只能由数字，字母，下划线，$，# 且只能用字母开头
数据类型
/
创建表的同时复制表 create table[列名] .... as....
create table emp03 as
select * from emp where deptno=30;
create table emp04 as select empno,ename,sal*12+nvl(comm,0) annsl from emp; 创建新表的同时为列重命名
仅仅复制表结构： create table empclone as select * from emp where 1=0; 加了条限制条件，是的返回结果没有数据
复制表结构和内容： create table empcloneall as select * from emp [where 1=1];where 1=1可有可无
以上复制方式不会复制外键
修改表：alter table
增加列：alter table dept03 add(header varchar2(20));
修改列结构：alter table dept03 modify( header varchar(30) default 'HZT'); 将header列的长度改为30，同时设置默认值，只有以后新增的列会生效
修改列名：alter table dept03 rename column header to dheader; 将header列名改为dheader
HWM水位，创建表结构和数据时，水位hwm会上涨
删除表：delete from 表名 ；只会删除内容（部分或者全部）可以回滚 ，不释放表空间（HWM不会复位）
drop table 表名；同时删除内容和表结构因为是ddl语句，删除的同时表空间被释放，所有的索引被删除，不能被回滚，事务已经自动被提交
只有创建者和有drop any table权限的用户可以删除。drop已经删光内容和表结构，所以没有HWM复位一说
修改表的名字 ：rename emp03 to emp003;仅表的创建者能改
truncate table emp003; 也是删除表，但只删除表数据，同时不支持部分删，只能全删,同时释放表空间（HWM会复位）（HWM可以称为水位）不能回滚，也是DDL语句
加注释：comment on table emp2 is 'employes informations' 给表emp2加了注释employes informations，可以通过user_tab_comments查看注释。
select *from user_tab_comments where table_name='emp2'
comment on column emp2.name is 'aaa';
select * from user_col_comments where table_name='emp2' and column_name='ENAME';
238
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
