在Oracle中，执行sql语句对数据库进行增删改的时候会自动启动一个事务，需要手动 commit 或者 rollback
1 Oracle 数据库的表操作
| 数据类型 | 描述 | |
|---|---|---|
| 字符串 | char | 固定长度字符串 |
| 字符串 | varhcar2 | 可变长度字符串 |
| 字符串 | nchar和nvarhcar2 | 存储Unicode字符串 |
| 数值 | number | 存储整数或者浮点型,格式为number(p,s) |
| 日期时间 | date | 存储日期和时间 |
| 日期时间 | timestamp | 秒值精确到小数点后6位 |
| 大对象 | blob | 存储二进制对象 |
| 大对象 | clob | 存储字符格式的大型对象 |
1.1.1 伪列
伪列是数据库默认给表添加的两个列，不可以对伪列进行增删改操作，只能查询，伪列属于数据类型，查询使用rowid的速度会比主键速度快
-
rowid 唯一标识，相当于数据的坐标
-
rownum 行号，用于分页
-- 查看rowid select rowid,empno from emp; -- 自然排序 根据rowid select * from (select rownum r,e.* from emp e) where r between 1 and 5 -- 添加排序 select * from ( select rownum r,e.* from (select * from emp ORDER by sal) e) where r between 1 and 5
2 SQL简介
特殊字体用双引号包住，比如列的别名中间要加空格："姓 名"
-
数据定义：create alter drop truncat
-
数据操纵语言： insert delete update select
-
事务控制语言
-
commit 提交事务
-
savepoint 存档
-
rollback 回滚
-
-
数据控制语言：grant revoke
desc 查看表结构 desc tableName
2.1 根据表的数据创建新表（备份）
create table stuinfo002 as select stuno,stuname from stuinfo
2.2 Having 关键字
having 表示在 group by 之后使用的条件
select stuName,stuage from stuinfo group by stuname,stuage having count(1) = 1
2.3 distinct 关键字
distinct 表示去重复
-- 在stuinfo中查出stuname和stuage不重复的行 select distinct stuname,stuage from stuinfo;
2.4 日期格式
date类型不识别yyyy-MM-dd格式的时间，需要 date 函数转换一下
insert into emp values('1001','测试1','管理员','22',date '2020-02-29','1243.23',null,'30');
2.5 order 排序
升序排序使用 order by 字段 降序排序使用 order by 字段 desc
-- 升序排序a，如果a有相同的值的话，降序排列b select * from t order by a,b desc
2.6 拼接sql语句
将两个字符串拼接到一起用||,在Oracle中不能用+
select 'fdsaf' || '书法大赛' from dual
将两个查询结果拼接到一起可以用 union 或者 nuion all
-
union 两个查询结果中有重复的行，则不显示
-
union all 显示查询结果中重复的行
获取两个数据集中相同的行使用： intersect 获取两个数据集中不同的行使用： minus
select * from emp where empno = '1000' union all select * from emp where empno = '1001' union select * from emp where empno = '1001' -- intersect select * from emp where empno = '1000' intersect select * from emp where empno = '1000' -- minus select * from emp where empno = '1000' minus select * from emp where empno = '1001'
2.7 group 分组
语法： group by 字段
-- 删除数据库中重复行，保留一个 delete stuinfo where rowid not in (select max(rowid) from stuinfo group by stuname,stuage);
2.8 savepoint 存档使用
对表进行增删改操作的时候，Oracle默认启动一个事务，需要手动commit才能真正修改数据，可以将修改的状态进行存档，然后当数据库进行操作之后回滚到存档的位置
存档： savepoint 存档名称
回滚到存档的位置： rollback to savepoint 存档名称
提交： commit
3 函数
3.1 单行函数
单行函数需要执行的语法：select 单行函数 from dual;
当前日期函数： sysdate
使用
sysdate函数的时候列不够宽时，显示只有日期，只有列足够宽的时候才会显示日期+时间 可以使用as给列起别名让列足够宽
随机数函数： dbms_random.value
四舍五入函数： round()
获取总数： count() ， count(*) 的效率没有 count(1) 效率高
-- 随机排序 select * from emp order by dbms_random.value; -- 对年份进行四舍五入 select round(TO_CHAR((select sysdate from dual),'yyyy')) from dual
3.1.1 转换函数
转换字符串： To_Char()
-- 将时间类型转换为char类型，并指定格式为yyyy-MM-dd select empno,to_char(hiredate,'yyyy-MM-dd') from emp; -- 只显示年份，并且后跟年 select empno,to_char(hiredate,'yyyy"年"') from emp
3.1.2 其他函数
nvl(a,b) ：判断值a是否为null，如果是就返回b的值，不是null就返回a的值
nvl2(a,b,c) ：判断值a是否为null，如果为null就返回b的值，不为null就返回c的值 nvl2
select empno,nvl(comm,0) from emp; select empno,nvl2(comm,0,1) from emp;
3.2 分析函数
对一组数字进行名次划分可以使用以下函数
-
rank遇到相同的成绩，名次一致，后面的成绩跳过相同成绩的名次 -
dense_rank遇到相同的成绩名次一致，后面的成绩不跳过相同成绩的名次 -
row_number相同成绩名次不一致，按顺序排序
rank 函数
select empno,ename,sal,rank() over(order by sal desc) 名次 from emp;
运行结果：
EMPNO ENAME SAL 名次 ----- ---------- --------- ---------- 1000 测试 1 7839 KING 5000.00 2 7902 FORD 3000.00 3 7788 SCOTT 3000.00 3 7566 JONES 2975.00 5 7698 BLAKE 2850.00 6 7782 CLARK 2450.00 7 7844 TURNER 1500.00 8 7934 MILLER 1300.00 9 7654 MARTIN 1250.00 10 7521 WARD 1250.00 10 1001 测试1 1243.23 12 7876 ADAMS 1100.00 13 7900 JAMES 950.00 14
dense_rank 函数
select empno,ename,sal,dense_rank() over(order by sal desc) 名次 from emp;
运行结果：
EMPNO ENAME SAL 名次 ----- ---------- --------- ---------- 1000 测试 1 7839 KING 5000.00 2 7902 FORD 3000.00 3 7788 SCOTT 3000.00 3 7566 JONES 2975.00 4 7698 BLAKE 2850.00 5 7782 CLARK 2450.00 6 7844 TURNER 1500.00 7 7934 MILLER 1300.00 8 7654 MARTIN 1250.00 9 7521 WARD 1250.00 9 1001 测试1 1243.23 10 7876 ADAMS 1100.00 11 7900 JAMES 950.00 12
row_number 函数
select empno,ename,sal,row_number() over(order by sal desc) 名次 from emp;
运行结果：
EMPNO ENAME SAL 名次 ----- ---------- --------- ---------- 1000 测试 1 7839 KING 5000.00 2 7902 FORD 3000.00 3 7788 SCOTT 3000.00 4 7566 JONES 2975.00 5 7698 BLAKE 2850.00 6 7782 CLARK 2450.00 7 7844 TURNER 1500.00 8 7934 MILLER 1300.00 9 7654 MARTIN 1250.00 10 7521 WARD 1250.00 11 1001 测试1 1243.23 12 7876 ADAMS 1100.00 13 7900 JAMES 950.00 14