数据操纵语言

数据操纵语言

select

基本语句

select…from

举例：select * from emp;

select…from…where

举例：select empno,ename,sal from emp where sal>2000; select…from…where order by

举例：select empno,ename,sal from emp where sal>2000 order by sal desc; select…from...where…group by

举例：select deptno,count(*) from emp group by deptno;

order by（asc,desc）

单列排序

asc

举例：select empno,ename,sal from emp where sal>2000 order by sal asc;

desc

举例：select empno,ename,sal from emp where sal>2000 order by sal desc; 多列排序

举例：select empno,ename,deptno,sal from emp order by deptno asc,sal desc; order by 后面既可以加原列名，也可以加别名

可以用算数运算符、可以用列别名进行排序

group by

举例：select deptno,count(*) from emp group by deptno;

运算符

算数运算符

+

举例：select empno,ename,sal+1000 from emp where sal<2000;

-

举例：select empno,ename,sal-1000 from emp where sal>2000;

*

举例：select empno,ename,sal*12 from emp;

/

举例：select empno,ename,sal/1000 from emp;

逻辑运算符

and

举例：select * from emp where hiredate>'01-1月-1980' and hiredate<'05-5月-1981'; or

举例：select * from emp where sal>2000 or comm>300;

not

举例：select empno,ename from emp where comm is not null;

关系运算符

>

举例：select empno,ename,sal from emp where sal>2000;

<

举例：select empno,ename,sal from emp where sal<1500;

=

举例：select ename,sal,job from emp where job='SALESMAN';

!= <>

举例：select ename,sal,job from emp where job!='SALESMAN';

举例：select ename,sal,job from emp where job<>'SALESMAN';

连接运算符

||

举例：select '员工的姓名是'||ename from emp;

date

to_date

举例：select * from emp where hiredate>to_date('1980-01-01','YYYY-MM-DD') and hiredate

to_char

举例：select ename,to_char(hiredate,'yyyy-mm-dd') from emp;

查询当前时间

select sysdate from dual;

select current_date from dual;

select systimestamp from dual;

聚合函数

1. 出现在select后面的除了聚合函数外的其他字段，必须同时出现在group by后面

2.聚合函数不能出现在where子句中

max

举例：select max(sal) from emp;

min

举例：select min(sal) from emp;

查询工资最低的人的工号，姓名，工资

select empno,ename,sal from emp where sal=(select min(sal) from emp);

avg

举例：求工资大于平均工资的员工信息

select empno,ename,sal from emp where sal>(select avg(sal) from emp);

count

举例：select deptno,count(*) from emp group by deptno;

sum

举例：select sum(sal) from emp;

as：别名

别名：as；别名中有空格，一定要加“”，其他情况可不加双引号

举例：select ename as 姓名,sal 工资,sal*12 年收入from emp;

rownum

例8：查询职员表中职员姓名、薪水和奖金，在算术表达式中包含NULL值

SELECT ename,sal,comm,sal+comm FROM emp;

例9：查询职员表中职员姓名、薪水和奖金，使用函数处理空值

SELECT ename,sal,comm,sal+nvl(comm, 0) FROM emp;

限制固定行数

select * from emp where rownum<5;

选择第2-3行

select rownumro,empno,ename from emp where rownum<=5;//先取前5行

select ro,empno,ename from (select rownumro,empno,ename from emp where rownum<=5) where ro>=3;