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对产生的最后结果进行排序
子查询
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对产生的最后结果进行排序
子查询