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优化的一个前提:尽量避免与数据库的交互,多表查询是空间换时间。
- 新增数据 insert
- 视图
- 作用:简化复杂查询,隔离数据访问(需要权限 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;删除索引