数据库的常见查询操作

–员工表信息
select *from emp;
–部门表的所有信息
select from dept;
–查询员工表的所有编号，名字，工资
select empno,ename,sal from emp;
–查询员工表的编号，名字，工资，年薪
select empno,ename,sal,sal12 from emp;
–as别名小名
select empno as 员工编号，ename as 员工姓名，sal as 员工工资 from emp;
–双引号的作用
select empno “en”from emp;
–连接符||
select ename ||’的月薪是’||sal from emp;
–去除重复的行
select distinct ename,sal from emp;
–order by 进行排序asc 升序desc降序
–查询所有数据对工资进行升序排序
select *from emp order by sal asc;
–降序
select *from emp order by sal desc;
–多字段排序分主次
select *from emp order by sal asc,empno desc;
select ename as 姓名，empno as 编号 ，sal as 工资 from emp sal order by 编号 desc, 工资 asc;
–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月-81’;
–大于
select *from emp where hiredate >’1-1月-81’;
–小于
select *from emp where hiredate <’1-1月-81’;
–查询工资在1250及以上的员工
select *from emp where sal>=1250;
–查询工资不等于800的员工
select *from emp where sal!=800;
select *from emp where sal<>800;
–查询工资在800-1000；
select *from emp where sal between 800 and 1000;
select *from emp where sal between 800 and 1000;
–in集合
–查询800-900-1000-1250的员工信息
select *from emp where sal in(800,1000,900,1250);
–查询奖金为空的员工信息
select *from emp where comm is null;
select *from emp where comm=”;
–查询奖金不为空的
select *from emp where comm is not null;
select *from emp where comm !=”;
–like模糊查询通配符
–查询以s开头的
select *from emp where ename like ‘S%’;
select *from emp where ename like ‘D__%’;
–用escape来强调是,
select *from emp where ename like ‘%%’escape’\’;
–and or not 运算符
–查询工资大于900并且以s开头
select from emp where sal>900 and ename like ‘S%’;
–或者以s
select from emp where sal>900 or ename like ‘S%’;
–常用字符函数
–查询员工表的姓名并且首字母大写
select initcap(ename)from emp;
–小写
select lower(ename)from emp;
–全大写
select upper(ename)from emp;
–左移除
select ltrim(‘scdn’,’sc’)from dual;
–右边移除
select rtrim(‘csdn’,’dn’)from dual;
–左右移除空格
select ltrim(rtrim(’ bfdhugf ‘))from dual;
–翻译
select translate(‘dfsff’,’df’,’lj’)from dual;
–替换
select replace(‘dfsfd’,’df’,’dss’)from dual;
–查找出现的位置
select instr(‘fdsafs’,’s’)from dual;
–截取
select substr(‘sgfyusg’,3,2)from dual;
–连接
select concat(‘dfdsfd’,’fdsfd’)from dual;
select ‘dfdf’||’dfdfa’from dual;
–绝对值
select abs(-15)from dual;
–x的y次幂
select power(2,3)from dual;
–向上取整
select ceil(12.5)from dual;
–向下取整
select floor(12.5)from dual;
–截断保留
select trunc(12.456,2)from dual;
–四舍五入
select round(122.456,2)from dual;
–开平方
select sqrt(4)from dual;
–取余数
select mod(10,3)from dual;
–取符号
select sign(-25)from dual;
select sign(12)from dual;
–两个日期间隔月份
select months_between(sysdate,’8-12月-1998’)from dual;
–修改月份
select add_months(sysdate,1)from dual;
select add_months(sysdate,-1)from dual;
–返回指定的一个星期几的日期
select next_day(‘16-8月-17’,’星期日’)from dual;
–日期的四舍五入
select round (sysdate,’year’)from dual;
select round (sysdate,’month’)from dual;
select round (sysdate ,’day’)from dual;
–日期转字符串
select to_char(sysdate,’yyyy-mm-dd’)from dual;
–字符串转日期
select to_date(‘2017年8月16日’,’yyyy”年”mm”月”dd”日”’)from dual;
–字符串转数值型
select to_number(‘123,549,654,12.789′,′123,549,654,12.789′,′999,999,999,99.999’)from dual;
select to_number(‘￥123,456,46.4567’,’L999,999,99.9999’)from dual;
–数值转字符串
select to_char(12345678.123,’L999,999,99.999’)from dual;
–时间比较问题
select from emp where hiredate>’1-1月-81’;
select from emp where hiredate>to_date(‘1981-1-1’,’yyyy-mm-dd’);
select from emp where to_char(hiredate,’yyyy-mm-dd’)>’1998-1-1’;
–空转数
select ename,nvl(comm,0)from emp;
–nvl2(e，n,m)e为空m不为空转n
select ename,nvl2(comm,comm,0)from emp;
select decode(sal,1250,’有钱’,5000,’真有钱’,’穷鬼’)from emp;
–case when else end (if else)
select (case
when sal >=5000 then ‘有钱’
when sal>=4000 then ‘1有钱’
when sal>=3000 then ‘2有钱’
else ‘穷’
end)from emp;
select (case sex when 0 then’男’when 1 ‘女’ end)from dual;
–sum求和 max最大值min最小值avg平均值count统计
select max(sal)from emp;
select min(sal)from emp;
select avg(sal)from emp;
select sum(sal)from emp;
select count(sal)from emp;
–多少员工
select count()from emp;
–聚合函数可以写在一行上
select max(sal),min(sal),sum(sal),avg(sal),count()from emp;
–统计所有（）
select count()from emp;
select count (distinct sal)from emp;
–查寻部门编号为10的员工信息
select from emp;
select from emp where deptno like ‘10%’;
–查寻年薪大于三万姓名雨部门编号
select ename,empno,sal12 from emp where sal12>30000;
–查询佣金为null姓名工资
select ename as 姓名,sal as 工资 from emp where comm is null;
–查询工资大于1500and有佣金的人
select ename as 姓名, sal as 工资 from emp where sal>1500 and comm is not null;
–查询工资大于1500or有佣金的人
select ename as 姓名, sal as 工资 from emp where sal>1500 or comm is null;
–查询姓名里有S的员工的信息工资名字
select ename as 姓名, sal as 工资 from emp where ename like ‘S%’;
–查询以j开头第二个字符是o 的
select ename as 姓名, sal as 工资 from emp where ename like ‘jo%_’;
selectfrom emp where ename like ‘%’;