oracle命令集合

set linesize 140 设置一行140个字符
set pagesize 120设置页宽120行
quit 退出
host cls清屏
永久性设置把set linesize 和set pagesize 放入sql\admin\glogin.sql里面

• 数值型字段宽度修改
  col empno for 999

• 字符型字段宽度修改
  col ename for a20
  select * from emp
  from
  where
  edit 打开记事本，编辑sql
  / 执行sql

• 条件查询
  where 过滤
  - 比较运算符 > < >= <= between and !=
  - between and 闭区间， 从小到大
  - 查询员工编号为10的员工信息
  - select * from emp where deptno = 10
  - 查询中大小写敏感，日期格式敏感
  - 查找本地格式select * from v$nls_parameters
  - 可以修改日期格式alter session set NLS_DATE_FORMAT=‘yyyy-mm-dd’;

  • 逻辑运算符 写多个条件
    and
    or
    not
    不能使用=或者！=作为

• 写法：

  • 注意分号，关键字不能换行
  • select * ([distinct]) from table
  • distinct 去除重复行
  • desc emp:把emp表中所有信息列出
  • null的第一个结论，null与任何表达式运算都为null
  • 第二个结论，不能在not in 的集合中使用null
  • 第三个结论，NULL既不是空，也是不是数值0，为无穷大
  • nvl(a,b): 如果a为null,返回结果b,否则相反
  • 查询员工表不同的部门编号，select distinct deptno from emp;
  • 计算表达式和显示当前日期 select 3+20*5, sysdate from emp;
  • 伪表（dual, 数学计算， 取系统时间，函数使用），伪列（sysdate, 并不存在某张表，但是可以使用）
  • sqlplus是oracle提供的客户端执行程序，本身不会修改数据
  • sql是语言，可以对数据修改
  • 在集合中 in , not in 不能使用NULL
  • like 模糊匹配
  • % 匹配任意多个字符 _ 匹配任意一个字符
  • escape 指定转义字符 escape “”

• 排序 order by （column, number, alias,expr）

  • 关键字last,把NULL放最后
  • select * from emp order by comm desc nulls last
  • 按查询结果集的序号排序

• 单行函数 字符函数、数值函数、转换函数、日期函数

  • lower upper initcap首字母大写、 concat 字符拼接（|| 支持多参数）只支持两个参数、substr （str, pos, len）截取字符串，从pos开始，取len长度、instr(str, str2)判断str2是否在str中存在，返回首地址、lpad(str, len, char)左填充、rpad右填充、trim(str)去首尾空格，也可以这样写select trim(‘H’ from “HHHjdfsdfjdHHHfjdkdfjkHHH”) from dual;、replace(str, str1,str2)替换
  • 数值函数 round 四舍五入， trunc去掉后面trunc(45.92, 1) = 45.9，mod取余, ceil函数 向上取整，floor 向下取整
  • 转换函数 to_char, to_number, to_date

• 日期函数，months_between();计算月份差，精确计算 、add_months增加月份、last_day()指定日期所在月份的最后一天、 next_day(sysdate, ‘星期一’)指定日期的下一个星期一

  • 显示昨天，今天，明天， oracle日期型+ 1

• 条件表达式
  case expr WHEN expr1 THEN return 1
  WHEN expr2 THEN return 2
  ELSE expr3
  end
  decode(expr|column, search1,res1, search2,res2)等同于case用法

• 分组函数(组函数，聚合函数)

  • avg, sum, max, min, count,
  • 组函数自动过滤 null
  • group by 分组
  • 结论：在select 中出现的非组函数的列必须在group by 中出现
  • 查询平均薪水大于2000的部门
  • where字句后不能使用group by
  • having 对分组函数过滤
  • select deptno, avg(sal) from emp group by deptno hvaing avg(sal)>2000;
    多表查询
  • 笛卡尔集 select * from emp, dept
  • 行数 = 表1行数*表2行数
  • 列数 = 表1列数+表2列数
  • N张表的连接条件为N-1
  • 表名.列名
  • 别名.列名
  • 内链接 where e.deptn=s.deptn
  • 外链接 当等值连接条件不成立时，想保留等号一边的数据 where e.deptn(+)=s.deptn
  • 自连接 select * from emp e, emp b;

• 子查询

  • select * from emp where sal > (select sal from emp where ename=“scott”)
  • 本质是sql嵌套，解决一次不能返回的问题
  • 注意事项
    1.合理的书写风格
    2.小括号
    3.主查询和子查询可以是不同表，只要子查询返回的结果主查询可以使用即可。
    4.可以在主查询的where,select,having,from，order by后放置
    5.不可以再主查询的group by 后面放置子查询
    6.from后面放置的子查询为一个集合
    7.单行子查询只能使用单行操作符；多行子查询只能使用多行操作符（in any任意 all所有）
    8.子查询中的NULL值
    9.先执行子查询，再执行主查询；相关子查询除外
    10.一般不在子查询中使用order by,但在top_n分析问题中必须使用

• 集合运算

  • 并集，union 两个集合相同部分保留一半，union all两个集合相同部分都保留
  • 交集，intersect 仅保留相同部分
  • 差集，Minus 集合A减去集合AB相同部分
  • 集合运算需要注意的问题

  1.参与运算的集合必须列数相同且类型一致
  2.采用第一个集合的表头作为最终使用的表头
  3.可以使用括号（）先执行后面的语句

  • 报表显示设置
    break on deptno skip 2 打开，跳两行
    break on null 关闭

*数据处理 *

• DML 数据操作语言
• DDL 数据定义语言 create truncate
• DCL 数据控制语言 grant revoke
  • 新增数据 insert
    • insert into tablename[col1,…] values[value,…]
    • insert into dept(deptno, dname) values(&dpt, &dname)地址符使用
    • create table emp10 as select * from emp where 1=2;创建一个与emp相同的表，不拷贝数据。
  • 修改数据 update
    • update tablename set col1=val1, col2=val2,… where condition;
    • update dept set loc=’&loc’ where deptno=52;
  • 删除数据 delete
    • delete from tablename where condition;
    • 支持地址符操作（用户输入）
    • truncate 先摧毁表，再重建，DDL
    • delete 逐行删除，可以闪回，DML，不会释放空间
  • 事务，以DML语句开始，执行一系列数据修改的操作，要么一起成功，要么一起失败
  • 提交结束 commit
    • 显示提交 commit
    • 隐式提交 执行了DDL语句（create）

  • 回滚结束 rollback

  • (ACID)原子性，一致性，隔离性，持久性
  • 隔离级别：脏读，幻读，不可重复读
  • 读未提交数据
  • 读已提交数据
  • 可重复读
  • 串行化
  • savepoint保存点
    rollback to savepoint aa;
  • rownum 伪列 、集合创建时生成，只能用<,<=, 不能用大于。
  • 相关子查询
    • select e.empno, e.name, (select avg(sal) from emp where deptno= e.deptno from emp where e.sal > 10
  • sql优化的一个前提：尽量避免与数据库的交互，多表查询是空间换时间。
• 视图
  • 作用：简化复杂查询，隔离数据访问(需要权限 grant create view to 用户名)
  • 创建一个视图 create or replace view v_count as select d.deptno, d.dname, count(e.empno) count from emp as e, dept d where d.deptno=d.deptno(+) group by d.deptno, d.dname order by 1;
• 同义词
  • 作用是简化查询，隔离访问
• 索引
  
  drop index myindex;删除索引