Oracle笔记收集整理(1)
Oracle笔记收集整理(1)
使用的用户: 用户名:scott 密码 :tiger 登录: sqlplus scott/tiger 退出: exit 查看当前用户有哪些表: select * from tab; ==================== 显示当前用户 SQL> show user USER 为 "SYS" 切换用户 SQL> connect scott/tiger 已连接。 命令可以使用缩写 conn[ect] user 执行上一条sql语句 / 设置行宽 set linesize 150默认为80 设置页面大小(一页显示的数据) set pagesize 100默认为14 显示参数的值 show 参数名 如: show linesize 永久保存配置 修改 glogin.sql 文件。 注释: 单行注释:-- 多行注释: /* */ 查看表结构 SQL> desc dept ==================================== 清屏: Windows中:host cls Linux中 :host clear 设置某列的宽度: column job format a15或 col job for a15 column SAL format 9999或 col salfor 9999 格式: col[umn] 列名 for[mat] 格式 对于字符串: a20,20是一个数据,表示20个字符的宽度。 对于数字 9表示一位,有几个,就是多宽。 ==================================== 空值的处理 不是一个有效的值。不是0,也不是空字符串。 null != null is null is not null 含有null的表达式结为null 修改上一条SQL语句 ed[it] 注意:后面不要写分号 滤空函数 nvl(表达式, 当表达式为空时使用的值) select empno, ename, sal, sal * 12 as 年薪, nvl(comm, 0), (sal * 12 + nvl(comm,0) ) 总收入 from emp ==================================== 字符串 是匹分大小写的,在使用时要加引号。 在指定别名时,引号可以加,也可以不加。 当含有空格、特殊字符时,一定要加引号。 不加引号时,显示都为大写,加上引号后,就是按所写的显示了。 在使用字符串时,要使用单引号。 在写别名时,要使用双引号。 去掉重复的行 -- 作用于一列 SQL> select distinct job from emp; -- 作用于多个列,所有列的值加一起重复才算重复的记录 SQL> select distinct job, deptno from emp; 如果只查询一个表达式,没有用到任何表的数据,这时也必须得写from... 可以写成 from dual dual是一个虚表,本身就存在的,可以直接使用。 如: select 3+2 select 'Hello' || 'World' from dual; 字符串连接符 SQL> select ename || '的薪水是' || sal from emp; ENAME||'的薪水是'||SAL ---------------------------------------------------------- SMITH的薪水是800 ALLEN的薪水是1600 WARD的薪水是1250 JONES的薪水是2975 MARTIN的薪水是1250 BLAKE的薪水是2850 CLARK的薪水是2450 SCOTT的薪水是3000 KING的薪水是5000 TURNER的薪水是1500 ADAMS的薪水是1100 JAMES的薪水是950 FORD的薪水是3000 MILLER的薪水是1300 也可以使用函数 concat(): SQL> select concat('Hello', 'World') from dual;
==================================== LIKE 在使用like时,可以使用 % 与 _,分别表示任意数量的任意字符或任意一个字符。 要想表达%或_本身,需要使用转义符,例: SQL> select * from emp where ename like 'KI\%%' escape '\'; BETWEEN 包含两个边界。 一定是小值写到前面,大值写到后面,否则没有结果。 IN where .. in (.., .., .., ...) 如果含有null,没有影响。 例:查询所有是经理的员工 SQL> select * from emp where empno in (select mgr from emp); where .. not in (.., .., ...) 如果含有null,则不返回任何结果。 例:查询所有不是经理的员工 SQL> select * from emp where empno not in (select mgr from emp where mgr is not null); ========================================= MySQL中日期类型:date, time, datetime Oracle中只有Date. 对日期的处理 1,查询指定日期后入职的员工信息 SQL> select * from emp where hiredate>'31-12月-81'; 2,修改日期的格式 SQL> select * from v$nls_parameters; SQL> alter session set nls_date_format='yyyy-mm-dd'; // 只对当前session有效 3,使用日期函数的方式: SQL> select * from emp where hiredate>to_date('1981-12-31', 'yyyy-MM-dd'); 或 SQL> select * from emp where to_char(hiredate, 'yyyy-MM-dd') > '1981-12-31'; 语法:TO_CHAR(date, 'format_model') 语法:TO_DATE(str, 'format_model') 格式字符串不区分大小写: 获取当前时间: SQL> select to_char(sysdate,'YYYY-MM-DD') from dual; TO_CHAR(SY ---------- 2013-07-30 SQL> select to_char(sysdate,'yyyy-mm-dd') from dual; TO_CHAR(SY ---------- 2013-07-30 SQL> select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2013-07-30 10:23:31 =========================================
排序:
order by 列名, ...
可以作用在:数字、日期、字符串。
可以使用列名,表达式,别名,序号(表示select中的第几个列)
升序、降列
按一个列排列,按多个列排序
当order by所在的列中有null,会:
升序时,null的在下面。
降序时,null的在上面。
我们希望,不管升序还是降序,null值的始终在下面
order by 列名, ...
可以作用在:数字、日期、字符串。
可以使用列名,表达式,别名,序号(表示select中的第几个列)
升序、降列
按一个列排列,按多个列排序
当order by所在的列中有null,会:
升序时,null的在下面。
降序时,null的在上面。
我们希望,不管升序还是降序,null值的始终在下面
方式一:SQL> select * from emp order by comm desc nulls last; 方式二: select empno, ename, job, hiredate, sal, nvl(comm, 0) from emp order by 6 desc =====================================================
1,组函数 select max(sal) 最高工资, min(sal) 最低工资, avg(sal) 平均工资, sum(sal) 所有员工的工资和, count(sal) 领工资的员工数量 from emp; 2,组函数对null的处理 例,查询所有员工的平均奖金(有人的奖金为null) select sum(comm)/count(*) 平均奖金 from emp 组函数会自动过滤掉null值。 在使用avg()时要注意处理null值: select avg( nvl(comm, 0) ) from emp; 函数可以嵌套使用
3,分组
Group by,写在FROM后,如果有WHERE,就在WHERE后面。
查询的列一定要是:
在group by中出现的列(在Select中不一定全写上)
或是使用组函数
按一个列分组
按多个列分组
参与分组的多个列有一个不相同就是不同的组。
4,分组结果过滤
Having,是分完组后再进行过滤,只显示符合条件的结果。
在Group by与Having中都不可以使用别名。
与Where的区别
Having是是分完组后再进行过滤。
Where是先过滤,再进行分组操作。
如果可以,尽量写Where条件,不写Having。
Group by,写在FROM后,如果有WHERE,就在WHERE后面。
查询的列一定要是:
在group by中出现的列(在Select中不一定全写上)
或是使用组函数
按一个列分组
按多个列分组
参与分组的多个列有一个不相同就是不同的组。
4,分组结果过滤
Having,是分完组后再进行过滤,只显示符合条件的结果。
在Group by与Having中都不可以使用别名。
与Where的区别
Having是是分完组后再进行过滤。
Where是先过滤,再进行分组操作。
如果可以,尽量写Where条件,不写Having。
Select ... From ... Where ... Group by ... Having ... Order by ... ================================================
子查询:
当一步不能求解时,可以使用子查询。
分为:
单行子查询
多行子查询
可以在主查询的select, from, where, having 都可以放子查询
不可以在主查询的group by 放子查询
单行操作符对应单行子查询,多行操作符对应多行子查询
在select中放子查询时,要求只能是单行子查询。
IN:
ANY:
小于某集合中的任意一个值,就是小于集合中的最大值。
大于某集合中的任意一个值,就是大于最小值。
ALL:
小于某集合中的所有值,就是小于最小值。
大于某集合中的所有值,就是大于最大值。