oracle基础入门一(简单查询)
查询对象:emp表
–查询所有列
select * from emp;--速度较慢
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp; --速度较快,建议使用
–查询指定列
select empno,ename,job,mgr,hiredate,sal from emp;
–算数运算符
--null与任何值运算都为null
select ename,sal,sal+sal*0.2 from emp;
--nvl(参数一,参数二),参数一为查询列如果为空就为参数二
select ename,sal,nvl(comm,0),(sal*6)+(sal+sal*0.2+nvl(comm,0))*6 from emp;
–列别名
–as关键字
–空格隔开
select ename as name,deptno dept from emp
--区分大小写,空格,特殊字符,用双引号引住
select ename,
nvl(comm, 0) * 12 as "Sumcomm",
(sal * 6) + (sal + sal * 0.2) * 6 sumsal,
nvl(comm, 0) * 12 + ((sal * 6) + (sal + sal * 0.2) * 6) allsumsal
from emp;
–连接操作符
–一般用||连接
select ename||job as namejob from emp;
–原义字符(添加连接字符,一个数字,一个字母或单词)
–字符或日期必须用单引号引住
select ename||' make is '||job as namejob from emp;
select ename || '第一年的总工资为' || (nvl(comm, 0) * 12 +
((sal * 6) + (sal + sal * 0.2) * 6)) allsumsal
from emp;
–消除重复行(distinct)
select distinct deptno from emp;
select distinct job from emp;
–限定条件数据(数值,字符,日期)where关键字
select ename,sal,deptno from emp where deptno=10;
select ename,sal,deptno from emp where deptno<>10;--不等于
select ename,sal,deptno from emp where ename='JONES';--区分大小写
日期格式:--DD-MON月-YEAR
select ename,sal,deptno,hiredate from emp where hiredate='17-12月-1980';
select ename,sal,deptno,hiredate from emp where hiredate>'17-12月-80';
–特殊比较运算符(between and,and,in(值一,值二,值三…),is null,like ”)
--包括边界值
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal between 1000 and 5000;
--符合不为空的
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where comm is null;
--查不到数据,与null值比较都为null
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where comm=null;
select ename,sal,deptno,hiredate from emp where hiredate>'17-12月-1980' and hiredate<'17-12月-1988';
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal in(800,1000);
–模糊查询like:%,[],^,_四种不同的方式
–%表示任意0个或多个字符,匹配出现的字符
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like 'CL%';
–_匹配单个字符
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like '_LARK';
–[]指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like '[张李]四';
–^不匹配[^]中的字符
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like '[^张李]四';
–escape特殊字符查询 escape ‘特殊字符’,跳过特殊字符
--查询员工姓名为CLA_RK的员工信息
select * from emp where ename like 'CLA@_%' escape '@';
–逻辑运算符
–与(and)
select ename,job,sal,deptno from emp where sal>300 and ename='JONES';
–非(not)相当于取反
select ename,job,sal,deptno from emp where not sal between 1000 and 5000;
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename not like 'CL%';
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where comm is not null;
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal not in(800,1000);
–或(or)
select ename,job,sal,deptno from emp where sal>2000 or ename='JONES';
–运算符的优先级 not>and>or
–若调整优先级用()括起来
–排序order by
–升序:ASC(默认排序),降序:DESC
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by sal;
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by hiredate asc;
–根据别名排序
select empno,ename,job,mgr,hiredate,sal,comm,deptno,sal+sal*0.2 sumsal from emp order by sumsal asc;
–多列参与排序
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by deptno,ename;
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by deptno asc,ename desc;
–结果集排序(按列进行排序)
select ename,job,mgr,hiredate,sal,comm,deptno from emp order by deptno,ename;
–作业
–1.查询入职时间在1982-7-9之后,并且不从事SALESMAN工作的员工姓名、入职时间、职位。
select ename,job,hiredate from emp where hiredate>'9-7月-82' and not job='SALESMAN';
–2.查询员工姓名的第三个字母是a的员工姓名。
select ename from emp where ename like '__A%';
–3.查询除了10、20号部门以外的员工姓名、部门编号。
select ename,deptno from emp where deptno not in(10,20);
–4.查询部门号为30号员工的信息,先按工资降序排序,再按姓名升序排序。
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno=30 order by sal DESC,ename;
–5.查询没有上级的员工(经理号为空)的员工姓名。
select ename from emp where mgr is null;
–6.查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。
select ename,sal,deptno from emp where sal>=4500 and deptno in(10,20);