DML数据操作语言的理解及SQL语句的使用(左外/右外连接查询，自连接查询，​​​​​​​组合查询等)

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;