(1)查看表结构:desc stu;
(2)查询所有列: select * from stu;
(3)查询指定列: select ename,sal,job from stu;
(4)取消重复行: select distinct deptno,job from stu;
注意:
(1)set timing on;//打开时间记录,
(2)oracle中的select语句大小写不区分,但是内容是区分的,比如说
select deptno,job,sal from emp where ename='SMITH';
比如说ename='smith',是查不出来的;但是select JoB是可以的
(5)使用算数表达式: select sal*13,ename from emp;
结果是:
SAL*13 ENAME
---------- ----------
10400 SMITH
20800 ALLEN
16250 WARD
38675 JONES
16250 MARTIN
37050 BLAKE
31850 CLARK
39000 SCOTT
65000 KING
19500 TURNER
14300 ADAMS
12350 JAMES
39000 FORD
16900 MILLER
(6)使用列的别名:像上边的表的列名: SAL*13很不好看,我们设置一下
select sal*13 "年工资",ename from emp;
结果是:
年工资 ENAME
---------- ----------
10400 SMITH
20800 ALLEN
16250 WARD
38675 JONES
16250 MARTIN
37050 BLAKE
31850 CLARK
39000 SCOTT
65000 KING
19500 TURNER
14300 ADAMS
12350 JAMES
39000 FORD
16900 MILLER
(7)如何处理null值:nvl函数
select sal*13+nvl(comm,0)*13 "年工资",ename,comm from emp;
select sal*13+comm*13 "年工资",ename from emp;//这样会出现值为空的
年工资 ENAME
---------- ----------
SMITH
24700 ALLEN
22750 WARD
JONES
34450 MARTIN
BLAKE
CLARK
SCOTT
KING
19500 TURNER
ADAMS
JAMES
FORD
MILLER
select sal*13+nvl(comm,0)*13 "年工资",ename,comm from emp;
年工资 ENAME COMM
---------- ---------- ---------
10400 SMITH
24700 ALLEN 300.00
22750 WARD 500.00
38675 JONES
34450 MARTIN 1400.00
37050 BLAKE
31850 CLARK
39000 SCOTT
65000 KING
19500 TURNER 0.00
14300 ADAMS
12350 JAMES
39000 FORD
16900 MILLER
(8)连接字符串:||
select ename || 'is a'||job from emp;
(9)使用where子句:
显示工资高于3000的员工:select ename,sal from emp where sal>3000;
显示 1982年1月1日之后入职的员工:select HIREDATE,ename from emp where HIREDATE>'1-1月-1982';
显示工资在2000到2500之间的员工: select ename,sal from emp where sal<=2500 and sal>=2000;
(10)like操作符
%:表示任意0到多个字符
— :表示任意单个字符
显示首字母为S的员工姓名和工资:select ename,sal from emp where ename like 'S%';
显示第三个字符为大写O的员工对的姓名和工资: select ename,sal from emp where ename like '__O%';
(11)在where条件中使用in
显示empno为123和234和456....的员工信息:select * from emp where empno in(123,234,345);
(12)使用is null的操作符
显示没有上级的雇员的信息: select * from emp where MGR is null;
只能这样写,不能写mgr='';
(13)使用逻辑操作符号: and ,or
查询工资高于500 或是岗位为manager的雇员,并且他们的姓名首字母为大写的J:
select * from emp where (sal>500 or job='manager') and ename like 'J%';
(14)使用order by(asc/desc)
按照工资从低到高的顺序:select * from emp order by sal (asc);
按照工资从高到底的顺序:select * from emp order by sal desc;
部门号升序而雇员工资降序 : select * from emp order by deptno asc ,sal desc;
部门号升序、入职时间降序:
(15)使用列的别名排序:必须用双引号
对员工的年薪进行排序: select ename,(sal+nvl(comm,0))*12 as "年薪" from emp order by "年薪";
select ename,(sal+nvl(comm,0))*12 "年薪" from emp order by "年薪";
(16)分页查询