oracle笔记sql1999

desc tablename 描述表结构

dual         计算数学表达式的表 只有一行记录
select 2*3 from dual

select sysdate frome dual

select ename,sal*12 anuual_sal from emp;
select ename,sal*12 "anuual sal" from emp;//双引号保持原来的样式
select ename,sal*12 + comm from emp;任何含有空值的数学表达式都是空值


select ename || sal from emp;//字符串连接符
select ename || 'dfsfd' from emp;//字符串用单引号
select ename || 'df''sfd' from emp;//字符串中有单引号,用两个单引号来替代一个单引号

select distinct deptno, job from emp;//deptno job 两字段组合重复的去掉

select ename, sal, comm from emp where comm is null;
select ename, sal, comm from emp where comm is not null;
select ename, sal, comm from emp where sal in (800,1500,2000);
select ename, sal, comm from emp where ename in ('tang','li','jun');
select ename, sal, hiredate from emp where hiredate > '20-2月-81';(未用日期函数)将1981.2.20以后的列出来
select ename, sal, comm from emp where sal not in (800,1500);

模糊查询
select ename from emp where ename like '%all%';     %=0或多个 _=一个字母
select ename from emp where ename like '-a%';       第二个字母是a的
select ename from emp where ename like '%\%%';      含有%的,转义字符
select ename from emp where ename like '%$%%' escape '$';     escape '$'指出$是转义字符。自己制定转义字符
数据排序
select * from dept order by empno asc； 默认升序
select * from dept where deptno <> 10 order by empno asc
select ename,sal,deptno from emp order by deptno asc,ename desc;先按照dptno升序排列，dptono同的按照ename反字典顺序排列
常用的sql函数
单行函数lower upper substring
select lower(ename) from emp;   ename字段名，显示出来的名字全变小写
select ename from emp where lower(ename) like '_a%';
select ename from emp where ename like '_a%' or ename like '_A%';
select substr(ename,1,3) from emp;从第一个字符开始截，一共截三个
select chr(65) from dual;ascii码转换为字符
select ascii('A') from dual;
select round(23.652) from dual;四舍五入
round(23.652,2)     23.65;
round(23.652,-1) 20   四舍五入到十位数

to_char 数字或者日期转为字符
select to_char(sal,'$99,999.9999') from emp; 9代表一位数字 $800.0000    $1,600.0000
select to_char(sal,'$00,000.0000') from emp; 0代表一位数字 $00,800.0000    $1,600.0000
select to_char(sal,'L99,999.9999') from emp;   L代表本地货币


日期
select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp; HH12进制字符
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual; sysdate是系统时间

to_date() 字符串转为date
select ename,hiredate from where hiredate > to_date('1981-2-22 12:34:56','YYYY-MM-DD HH24:MI:SS');
to_num() 字符串转为数字
select sal from emp where sal > to_number('$1,250.00','$9,999.99');

nvl()处理空值
select ename, sal*12 + nvl(comm,0) from emp; 如果comm是空值，用0替代他，如果comm不是空值，则为本身值


select max(sal) from emp;
select min(sal) from emp;
select sum(sal) from emp;
select avg(sal) from emp;平均
select to_char( avg(sal), '99999999.99' ) from emp;
select to_char( round( avg(sal) ), 2) from emp; 四舍五入、精确到小数点后两位
select count(*) from emp where deptno = 10;
select count(ename) from emp where deptno = 10;
select count(comm) from emp where deptno = 10; //非空值字段
select count(distinct deptno) from emp;

group by 语句
select avg(sal) from emp group by deptno;分组之后，每个分组求平均salary
select deptno, avg(sal) from emp group by deptno;
select max(sal) from emp group by deptno, job;按两个字段的组合分组
select ename, max(sal) from emp; 错误原因：分组函数是多行输入，只有一行输出,max只有一个输出，ename可有好多个。很多人的sal可相同，且最多

select ename from emp where sal = (select max(sal) from emp); 子查询
select deptno, max(sal) from emp group by deptno; 每组的deptno唯一
规则：出现在select列表里的字段，没出现在组函数里必须出现在group by中
select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;平均薪水值大于200的组
having是对分组进行限制
having与分组条件有关,sql语句的Group by后只能跟Having条件语句,而不能用Where语句  
Where与筛选记录条件有关
Where是对源表中的现存字段或现存字段的一部分进行的筛选，
而Having是对分组以后的结果的筛选，一般情况下是对合计后产生的字段进行的筛选，
如求和、求平均、计数、求最大值最小值等，并且HAving子句要和Group by子句一起使用。


on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。  
   
根据上面的分析，可以知道where也应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。但也不是说having没用，因为有时在步骤3还没出来都不知道那个记录才符合要求时，就要用having了。  
   
在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。  
   
如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。  
   
在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里


select avg(sal) from emp where sal > 1200 group by deptno having avg(sal) > 1500 order by avg(sal) desc;
先where再group by后having where对数据进行过滤 group by 对数据分组 having 对分组进行限制 order by对产生的最后结果进行排序


子查询