一些sqlplus的指令
- host cls 清屏
- show user 查看当前用户
- select * from tab;查看当前用户下的表
- desc emp;员工表结构
- show linesize 查看行宽
- set linesize 修改行宽
- col for sal 9999 设置行宽为150
- col for sal a8 设置行宽为a8
- c命令修改
1.基本查询
- select * from emp从员工表中查看所有员工信息
- null值的问题
- 列的别名:在列后面加as,可以省略
- ed:进入记事本编辑命令
- distinct:取出重复的记录——作用于后面的所有列
- concat(a,b):连接符——等同于||
- dual: 伪表
- 伪列:例如后面学到的rowid
2.过滤和排序
- 加入where条件:
- 条件中的字符串区分大小写——SQL语句不区分大小写
- 条件中的日期格式敏感
- 修改日期格式:
- alter session set NLS_DATE_FORMAT=”yyyy-mm-dd”
- between and
- not in(集合)
- null(可以存在not in中,但是不能存在in中)——因为null代表的是不确定
- order by +列、表达式、别名、序号(选择的列的序号)
- 作用于后面的所有列,desc只作用于离他最近的列
- 排序中null的值最大
3.单行函数——字符操作
- 大小写转换:upper、lower
- 截取字符串:substr
- 查找指定字符串在目标中的位置:instr
- 长度:length
- 左右填充:lpad、rpad
- 去掉:trim()
- 替换:replace
- 四舍五入:round
- 当前时间:sysdate
- 日期相差的月份数:mouth_between(a,b)
- 日期加上月份:add_mouth()
- 上一个星期几:last_day:
- 下一个星期几:next_day
- nv12(a,b,c):当a=null时,返回c,否则返回b
- nullif(a,b):a=b时,返回null,否则返回a
- coalesce:从左到右找到第一个不为null的值
4.多行函数——聚合函数
- sum
- avg
- count
- having 后面才可以使用聚合函数作为条件的值,where不行
5.多表查询
- 等值连接
- 不等值连接
- 左外连接和右外连接
- 自然连接:通过链接,将同一张表视为多张表
- 自然链接不适合操作大表,因为很可能产生笛卡尔积
6.子查询——规范
- 括号
- 书写合理
- 可在where——select——having—— from后面
- 不可以在group by中使用
- 强调from后面的子查询
- 主查询和子查询可以不是同一张表
- 一般不存在子查询中排序,但在top-n问题中需要排序
一般先执行子查询,但是在相关子查询中例外
- 相关子查询:每差一条记录,都要重新进行一次子查询
单行子查询只能使用单行操作符,多行子查询(使用到集合)只能使用多行子查询
- 单行子查询操作符:= >
7.集合运算
- group by增强——rollup(…..)
- union——并集
- 注意的问题:
- 参与运算的各个集合必须列数相同且类型一致
- 采用第一个集合作为最后的表头
- order by 永远在最后
必须知道的例子(略)
- select deptno,job,sum(sal) from emp group by deptno,job
- union
- select deptno,sum(sal) from emp group by deptno
- union
- select sum(sal) from emp;
select deptno,sum(sal) from emp group by deptno
等价于
- select deptno,job,sum(sal) from emp group by rollup(deptno,job);
8.处理数据——DML、DDL
- SQL的类型:DML(insert)、DDL(create)、DCL(grant)
- insert等DML语句中使用&,等价于prepareStatement
- insert into (。。。、)values(&1,&2.。。、)
- &1:aaaa
- 批处理
- 批量赋值表的结构,不需要数据:create table emp1 as select * from emp where 1=2
- 批量处理表的结构,同时包含数据:create table emp1 as select * from emp
- 海量数据拷贝:
- 数据泵(datapump):plsql
- SQL*Loader
- (数据仓库)外部表
- 可传输的表空间
delete和truncate的区别:
事务:
- 事务开启的标识:第一条DML语句的执行
结束标识:
提交
- 显示:commit
- 隐式:正常退出、DDL、DCL
回滚:
- 显示:rollback;
- 隐式:非正常退出、掉电、宕机
- savepoint:保存点
- 事务的隔离级别:ACID
9.创建和管理表
- 行地址 rowid——伪列
- 创建表:create
- 修改表:alter
- 删除表:drop
- Oracle的回收站–注意:管理员没有回收站
- show recyclebin
- purge recyclebin;
- 闪回:flashback table TESTSAVEPOINT to before drop;
10.数据库对象
- 视图
- create view empinfoview
- as
- plsql。。。
- 序列
- create sequence myseq;
- 索引
- 同义词
11.PLSQL
- declare
- 说明部分(变量、光标、例外、说明)
- begin
- 语句序列(DML语句)
- exception
- 例外处理语句
- end;
12.其他对象
- 光标:Cursor
- 存储过程
- 存储函数——存储过程只有一个返回值时使用。
- 包头
- 包体
- 触发器