数据操纵语言

数据操纵语言

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;

like

通配符

_

举例：select empno,ename from emp where ename like ‘____’; %

举例：select empno,ename from emp where ename like '%A%'; [ ]

举例：

escape

转义字符：可以自己定义

‘%30\%%’ escape ‘\’:含有30%的字符串‘%30b%%’ escape ‘b’：含有30%的字符串举例：

insert into emp(empno,ename) values(7888,’zhao_hua’); select * from emp where ename like ‘%\_%’ escape ‘\’;

select * from emp where ename like ‘%b_%’ escape ‘b’;

nvl：处理空值：

举例：查询员工中工资小于2000的员工的工号，姓名，工资和奖金的总额

select empno,ename,sal+nvl(comm,0) from emp where sal<2000; distinct：处理重复项

举例：查询公司所有部门的部门号

select distinct deptno from emp;

between and

包含两边界值

举例：select empno,ename,job,sal from emp where sal between 3000 and 5000; in

举例：select ename,job,sal from emp where job in ('PRESIDENT','ANALYST');

is null

查询emp表中哪些职员的奖金为null

SELECT ename, sal, comm FROM emp WHERE commis null;

插入单行数据

insert into 表名(字段名1,字段名2,…) values(……);

举例：insert into emp(empno) values(7080);

通过子查询插入多条数据

1.只复制表，不复制数据

create table emp_1 as select * from emp where 1=0;

2.通过子查询插入多条数据，指定列名

INSERT INTO emp_1(empid, name, job) SELECT empno, ename, job FROM emp WHERE deptno=20;

一条语句插入多条数据

Insert into emp(empno,ename) select 7011,”zhaohua1” from dual

union select 7012,”zhaohua2” from dual;

update

基本语法:

update 表名set 列名=value[,column=value]…[where…]

基本语法：

delete from 表名[where condition]