SQL select

--desc 表名 描述表的内容  
desc emp;
--加上数学表达式和列名  ""保持格式
select ename "name space", sal*12 year_sal from emp;   
select 2*3 from dual;
select sysdate from dual;
--空值的数学表达式 结果都是空值
select ename, sal*12 + comm from emp;  
--"||"字符串连接 单引号中的是字符串，字符串中的单引号，''表示
select ename||sal from emp;    
--distinct 修饰两个字段
select distinct deptno, job from emp;
select ename, sal from emp where sal between 800 and 1500;
select ename, sal from emp where sal >= 800 and sal <= 1500;
select ename, sal from emp where comm is null;
--一个事务开始于一条ddl语句，结束语rollback commit ddl数据定义语句 dcl数据控制语句语句
--正常断开连接 提交 非正常断开连接 回滚
select ename, sal from emp where sal not in (800 ,1500);
-- 模糊查询
select ename, sal from emp where ename like '_A%';
select ename, sal from emp where ename like '%\%%';
--转义字符
select ename, sal from emp where ename like '%$%%' escape '$';
--排序
select * from dept order by deptno desc;
select ename, sal from emp order by deptno asc, ename desc;
--函数
select lower(ename) from emp;
select substr(ename,1,3) from emp;
select round(23.652, 1) from dual;
select to_char(sal, '$99,999.999') from emp;
select to_char(sal, 'L000,000.000') from emp;
select to_char(hiredate, 'yyyy-mm-dd hh:mi:ss') from emp;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
select ename, hiredate from emp
    where hiredate > to_date('1982-09-11 12:44:44', 'YYYY-MM-DD HH24:MI:SS');
select sal from emp where sal > to_number('$1,250.000', '$9,999.000');
select ename,sal*12 + nvl(comm, 0) from emp;
--组函数  多行输入，只有一行输出 select ename, max(sal) from emp; 结果不能保证只有一行
select max(sal) from emp;
select sum(sal) from emp;
select count(distinct deptno) from emp;
--分组函数
select deptno,avg(sal) from emp group by deptno;
select deptno, job, max(sal