SQL 单表查询的一些常用语句和函数及单表的插入修改删除操作

select * from emp;
select * from dept;
select * from salgrade;
select * from bonus;

--查询员工表中员工编号，姓名，工资
select empno,ename,sal from emp;
--查询员工表中员工编号，姓名，工资，年薪
select empno,ename,sal,sal*12 from emp;
-- as（别名）小名，可省略
select empno as 员工编号,ename 员工姓名,sal 月薪,sal*12+5000 年薪 from emp;
--为表添加小名
select empno,ename,sal from emp p;
--|| 连接符 ，相当于java中的+号
--查询员工表中“XXX的月薪是XXX”的信息
select ename||'的月薪是'||sal 简介 from emp;
--distinct 去除重复行 
--去除员工表中月薪相同的行
select distinct sal from emp;
--去除员工表中名字和月薪全部相同的行
select distinct ename,sal from emp;
--order by 按字段排序，asc 升序（默认），desc 降序
--查询员工表按升序排列
select * from emp order by sal;
--查询员工表按降序排列
select * from emp order by sal desc;
--多字段排序,分主次
--查询员工表中员工编号，姓名，月薪并先按月薪升序排列，相同时再按编号降序排列
select empno,ename,sal from emp order by sal,empno desc;
--可以用别名排序，不建议
select empno 员工编号,ename 员工姓名,sal 月薪 from emp order by 月薪，员工编号 desc;
--where 条件查询
--查询名字叫scott的员工信息
select * from emp where ename='SCOTT';
--查询工资为1250的员工信息
select * from emp where sal=1250;
--查询入职日期为1981-2-22的员工信息
select * from emp where hiredate='22-2月-1981';
--查询入职日期大于1980-1-1的员工信息
select * from emp where hiredate > '1-1月-1980';
--查询工资在1250元及以上的员工信息
select * from emp where sal >= 1250;
--查询工资不等于800的员工信息
select * from emp where sal != 800;
select * from emp where sal <> 800;
--between A and B  在A和B之间（包含）
--查询工资在800和1000之间的员工信息
select * from emp where sal between 800 and 1000;
select * from emp where sal >= 800 and sal <= 1000;
--in（集合）属于集合中的一个或多个
select * from emp where sal in(800,900,1000,1250);
select * from emp where sal =800 or sal =900 or sal =1000 or sal =1250;
--查询佣金为空的员工信息
--表格里没有填过
select * from emp where comm is null;
--表格里填过又删除
selset * from emp where comm = '';
select * from emp where comm is null or comm = '';
--查询佣金不为空的员工信息
select * from emp where comm is not null;
select * from emp where comm != '';
select * from emp where comm is not null or comm != '';
--like 模糊查询 % 代表所有 _代表一个
--查询名字以S开头的员工信息
select * from emp where ename like 'S%';
--查询名字以SCOT开头且共五位的员工信息
select * from emp where ename like 'SCOT_';
--查询名字以S开头且共五位的员工信息
select * from emp where ename like 'S____';
--查询名字中有L并且L后面至少有一位的员工信息
select * from emp where ename like '%L_%';
--查询名字中第二位为L的员工信息
select * from emp where ename like '_L%';
--查询名字中有/的员工信息
select * from emp where ename like '%/_%' escape '%';
--and or not 逻辑运算符
--查询工资大于1000并且名字以S开头的员工信息
select * from emp where sal>1000 and ename like 'S%';
--查询工资大于1000或者名字以S开头的员工信息
select * from emp where sal >1000 or ename like 'S%';
--查询工资不是800,900，1250的员工信息
select * from emp where sal not in(800,900,1250);
--查询名字不是以S开头的员工信息
select * from emp where ename not like 'S%';
--常用字符函数
--initcap 首字母大写
select initcap('asdf') from dual;
--查询员工表中的名字，首字母转为大写
select initcap(ename) from emp;
--查询员工表中的名字，全部转为大写
select upper(ename) from emp;
--查询员工表中的名字，全部转为小写
select lower(ename) from emp;
--ltrim左剪裁（移除）
select ltrim('123abc','123') from dual;
--右剪裁（移除）rtrim
select rtrim('123abc','abc') from dual;
--单个字符翻译 translate
select translate('sorry','asdfy','12345') from dual;
--字符串替换 replace
select replace('sorry','r','tt') from dual;
--查找字符串位置 
select instr('asfdfas','d') from dual;
--取字符串
select substr('sdfagdasfg',3,5) from dual;--从第3位开始取，往后取5位
--连接字符串
select concat('123','abc') from dual;
select '123'||'abc' from dual;
--常用数值函数
--abs 绝对值
select abs(-12) from dual;
--power X的Y次幂
select power(2,3) from dual;
--ceil 向上取整
select ceil(12.5) from dual;
--floor 向下取整
select floor(12.5) from dual;
--trunc 截断
select trunc(12.125，2) from dual;
--round 四舍五入
select round(12.125,2) from dual;
--sqrt 开平方
select sqrt(4) from dual;
--mod 取余数
select mod(10,3) from dual;
--sign 取符号
select sign(-12) from dual;
select sign(0) from dual;
select sign(12) from dual;
--常用日期函数
--months_between 两个日期之间相隔的月份
select months_between(sysdate,'1-1月-2000') from dual;
--add_months 修改月份
select add_months(sysdate,1) from dual;
select add_months(sysdate,-1) from dual;
--next_day 返回指定日期的下一个星期几的日期
select next_day(sysdate,'星期六') from dual;
--last_day 返回指定日期的这个月的最后一天的日期
select last_day(sysdate) from dual;
--round 日期做四舍五入
select round(sysdate,'year') from dual;--超过6月返回下一年的第一天
select round(sysdate,'month') from dual;--超过月的一半返回下一月的第一天
select round(sysdate,'day') from dual;--超过星期的一半返回下一星期的第一天（周日）
--trunc 日期做截断
select trunc(sysdate,'year') from dual;--本年的第一天
select trunc(sysdate,'month') from dual;--本月的第一天
select trunc(sysdate,'day') from dual;--本周的第一天
--to_char 日期转字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--to_date 字符串转日期
select to_date('2017年8月16日 18时50分00秒','yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;
--to_char 数值转字符串
select to_char(234234.25,'L999,999,999.99') from dual;
--to_number 字符串转数值 
select to_number('$1,234,245.544','$999,999,999.999') from dual;
select to_number('￥234,522.45','L999,999.99') from dual;
--时间比较问题（三种写法）
--默认值
select * from emp where hiredate > '1-1月-81';
--to_date
select * from emp where hiredate > to_date('1981-1-1','yyyy-mm-dd');
--to_char
select * from emp where to_char(hiredate,'yyyy-mm-dd') > '1981-1-1';
--nvl 空转数
select ename,nvl(comm,0) from emp;
--nvl2(e,n.m) e为空转为m,不为空转n
select ename,nvl2(comm,'有','无') from emp;
--decode
select decode(sal,3000,'有钱',5000,'很有钱','穷鬼') from emp;
--case when then else end
--if else
select (case 
            when sal>=5000 then 
              '富豪'
            when sal>=4000 then 
              '很有钱' 
            when sal>=3000 then 
              '有钱'
            when sal>=2000 then
              '一般'
            when sal>=1000 then 
              '没钱'
            else
              '穷鬼' 
        end)
        from emp;
--switch case
select (case sex when 0 then '男' when 1 then '女' end) from dual;
--常用多行函数
--max 最大
--查询最大工资
select max(sal) from emp;
--min 最小
--查询最小工资
select min(sal) from emp;
--sum 求和
--查询所有员工工资的和
select sum(sal) from emp;
--avg 求平均值
--查询平均工资
select avg(sal) from emp;
--count 统计 
--count(*) 统计所有的个数
select count(*) from emp;
--count(exp) 统计非空的exp的个数
select count(sal) from emp;
--count(distinct exp) 统计非空不重复的exp的个数
select count(distinct sal) from emp;
--多行函数可以写在一行上
select max(sal),min(sal),sum(sal),avg(sal),count(sal) from emp;
--多行函数不可以和单行函数写在一行上
select ename,max(sal) from emp;--这是错误的写法
--group by 分组函数
--查询各部门的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno;
--查询各部门的部门编号，工作和平均工资
select deptno,job,avg(sal) from emp group by deptno,job;
--having 分组后的条件，必须写在group by 后面
--查询平均工资大于2000的部门的平均工资
select avg(sal) from emp group by deptno having avg(sal) > 2000;
--查询部门编号为10的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having deptno = 10;
select deptno,avg(sal) from emp where deptno = 10 group by deptno;
--统计人数小于4的部门的平均工资
select avg(sal) from emp group by deptno having count(*) < 4;
--统计各部门的最高工资，排除最高工资小于3000的部门
select max(sal) from emp group by deptno having max(sal) >= 3000;






--insert 插入
--向员工表中插入一条数据（全部列）
insert into emp values(1234,'957','上单',957,sysdate,10000,null,10);
--插入不能为null值的部分列
insert into emp(empno,ename,job,sal) values(9876,'condy','打野',12000);
select * from emp;
--创建一个表的临时表
create table temp
as 
select * from emp where 1=2;
--查询结果插入
insert into temp select * from emp;
select * from temp;
--update 修改
--把名叫condy的员工姓名改为clearlove
update temp set ename='clearlove' where ename='condy';
--把员工编号为9876的员工的空的信息补全
update temp set mgr=1111,hiredate='1-1月-2000',deptno=20 where empno=9876;
--把部门为20的所有员工工资加500
update temp set sal=sal+500 where deptno =20;
--删除临时表中员工编号为9876的员工
delete temp where empno=9876;
--删除临时表中的所有信息
delete temp;
--删除表（包含数据）
drop table temp;--慎重，不能恢复