DML数据操作语言

新增

 

【语法1】
INSERT INTO table_name (column1,column2,...)
VALUES ( value1,value2, ...);
 
【示例1】
insert into emp (empno,ename) values(1111,'zx');
 
 
【语法2】
INSERT INTO <table_name> <SELECT 语句>;
 
【示例2】
create table t1 as select * from emp where 1=2;
insert into t1 select * from emp where sal>2000;

 

修改

 

【语法1】
UPDATE table_name SET column1=new value,column2=new value,...
WHERE <条件>;
 
【示例1】
update emp set sal=3000 where ename='zx';

 

查询

伪表dual

DUAL是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。以用它来做很多事情，如：

查看当前用户

select user from dual;

 

用来调用系统函数

--查询系统的当前时间并格式化
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

 

得到序列的下一个值或当前值

--获得序列seq的下一个值
select seq.nextval from dual;
 
--获得序列seq的当前值   
select seq.currval from dual;

 

可以用做计算器

select 2*8 from dual;

 

伪列rowid

rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录，同一条记录在不同查询中对应的rowid相同。

【用法】
SELECT ROWID,字段名... FROM 表名;
 
【示例】
select rowid, emp.* from emp;

 

伪列rownum

rownum是根据sql查询出的结果给每行分配一个逻辑编号；每次的查询都会有不同的编号。编号从1开始。

【用法】
SELECT ROWNUM,字段名... FROM 表名;
 
【注意】
ROWNUM 不能使用大于号“>”
即 select rownum, emp.* from emp where rownum > 2 是不对的，没有任何结果
 
【示例】
select rownum, emp.* from emp;
 
/* 关于分页：由于不能使用>，所以为了达到分页目的得如下执行；如获取第2页数据（每页3条）*/
select * from (select rownum r,emp.* from emp where rownum < 7) where r > 3;
 
 
/* 关于排序：由于rownum是查询结果的行编号，排序后这个编号便有可能被打乱，如果需要该编号和排序的结果列表序号保持一致可以如下执行*/
select rownum,t.* from (select empno,ename from emp order by empno desc) t;

 

 

连接查询

准备查询数据，将scott用户下的dept表复制到zx用户下。

使用sys用户登录系统；替zx用户创建dept表，表结构和数据来自scott.dept。
--执行语句如下
create table zx.dept as select * from scott.dept

;

 

等值查询 

--查询emp表中各用户对应的部门名称
select empno,ename,dname from emp,dept where emp.deptno=dept.deptno;
 
--练习：按部门统计员工的人数，要求显示部门号、部门名称、和部门人数
select d.deptno,d.dname,count(e.empno) from dept d,emp e
where d.deptno=e.deptno
group by d.deptno,d.dname;

 

左外/右外连接查询：左外连接是在等号左边的集合，无论条件是否成立均在结果集合，写法就是在等号右边使用(+)，这个写法是oracle专用的，如果需要全数据库类型通用应该使用left join）

--按部门统计员工的人数，要求显示部门号、部门名称、和部门人数，部门下没有人的也将显示
select d.deptno,d.dname,count(e.empno) from dept d,emp e
where d.deptno=e.deptno(+) group by d.deptno,d.dname;
 
--上述语句的通用数据库写法(left join方式)
select d.deptno,d.dname,count(e.empno) from dept d left join emp e
on d.deptno=e.deptno group by d.deptno,d.dname;

 

 

自连接查询：查询的2张表是同一张表，一般是该表的字段之间存在上下级关系

--查询员工和老板的上下级关系
select e.ename || ' 的老板是: '|| b.ename from emp e,emp b
where e.mgr=b.empno;
【注意】上述查询语句中的||表示为字符的连接

 

组合查询

计算部门工资总和，最高工资，最低工资

select deptno,sum(sal),max(sal),min(sal) from emp group by deptno;

 

 

部门平均工资

 

--查询部门的平均工资
select deptno,avg(sal) from emp group by deptno;
 
--查询平均工资大于2000的部门，并按照平均工资降序排序
select deptno,avg(sal) 平均工资 from emp
group by deptno
having avg(sal)>2000
order by 平均工资 desc ;
 
--查询除了20部门以外，平均工资大于2000的部门
select deptno,avg(sal) from emp
where deptno <> 20
group by deptno
having avg(sal)>2000;
【注意】SQL语句中的各子句执行顺序：
from->where->group by->having->select->order by

 

 

子查询：将子查询放入括号中；group by后不能使用子查询；select、from、where后面都可以使用子查询；可以将子查询看作一张新表

 

--select后面的子查询
select (select dname from dept where deptno=10),ename from emp where deptno=10;
 
 
--from后面的子查询
select * from (select ename,sal from emp);
 
 
--将子查询视为一个表
select e.ename,e.sal from (select ename,sal from emp) e;
 
 
--where后面的子查询；查询工资比10号部门员工中任意一个员工的工资低的员工信息
select * from emp where sal < (select min(sal) from emp where deptno=10);

 

 

其它查询

--查询姓名是5个字符的员工，且第二个字符是C，使用_只匹配一个字符并且不能标识0或多个字符 
select * from emp where ename like '_C___';
 
 
--查询员工姓名中含有‘_’的员工，使用\转义字符 
select * from emp where ename like '%\_%' escape '\';

 

 

 

删除

 

--根据条件删除表数据
delete from emp where empno=0000
 
 
--清空表数据（表还在），不写日志，省资源，效率高，属于数据定义语言
--先创建要清空数据的表
create table myemp as select * from emp; 
 
--清空表数据
truncate table myemp;