连接符||图表 1多表查询的基本语法查一张以上的表,就叫做多表查询例子:查询出雇员名称,部门名称和部门所在地的(一般多表查询要用别名)统计记录数:查询emp有多少条纪录左右连接(重点)select e.empno,e.ename,d.deptno,d.dname,d.locfromemp e,dept d where e.deptno=d.deptno;部门一共四个,这里只查询出三个,因为在雇员表中没有指定40部门的雇员,所以在消除笛卡尔乘机的时候没有条件符合40,如果喜欢40部门显示出来,就要用左右连接了。
select e.empno,e.ename,d.deptno,d.dname,d.locfromemp e,dept d where e.deptno(+)=d.deptno;(+)在左边,表示以右边的表为准,表示右链接。
40部门出来了,所以此时就用到了有连接,证明以下规律(.+.)在左表示右连接........(.+.)在右表示左连接.........SQL:1999对SQL的支持(了解)范例:交叉连接(cross join)产生笛卡尔积select * from empt CROSS JOIN dept;查询结果产生笛卡尔积CREATE TABLE EMP10 AS SELECT * FROM EMP WHERE DEPTNO=10; select * from emp NATURAL JOIN dept; 自动进行匹配范例:USING子句,直接关联的操作列select * from emp e JOIN dept d USING (deptno) where deptno=30;把两张表的详细信息进行打印输出范例:ON子句自己编写连接条件select * from emp e JOIN dept d ON (e.deptno=d.deptno) where e.deptno=30;范例:左连接(左外连接)右连接(右外连接)LEFT JOIN RIGHT JOIN 组函数和分组统计(重点)组函数在SQL常用组函数有如下几个:COUNT()求全部记录数MAX()求最大记录数MIN()求最小记录数A VG()平均SUM()求和分组统计GROUP BYselect deptno,COUNT(empno) from emp GROUP BY deptno;算出部门表的平均工资:select A VG(sal) from emp ;算出每个部门的平均工资:Select deptno,A VG(sal) from emp ;之所以会出现这个错误是因为数据库不知道怎样在结果集中处理deptno列。
考虑一下:这个查询既试图使用AVG聚合函数对多行记录进行操作,却又试图从每行中获得deptno列的值;这两个操作是不可能同时完成的。
此时必须提供一个GROUP BY子句告诉数据库将deptno列相同的行分组在一起,然后数据库就可以将这些组中的行传递给AVG函数。
警告:如果查询中包含聚合函数,而所选择的列并不在聚合函数中,那么这些列就必须在GROUP BY子句中。
按部门分组,并显示部门名称,以及部门员工数select d.dname,count(e.empno) from dept d,emp ewhere d.deptno=e.deptnoGROUP BY d.dname;要求查出平均工资大于2000的部门编号和平均工资select deptno,A VG(sal) from emp WHERE A VG(sal) >2000 GROUP BY deptno;之所以会出现这个错误是因为W H E R E子句只能用来对单行而不是行组进行过滤。
要过滤行组,可以使用HA VING子句。
范例:显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于¥5000。
输出结果按月工资的合计升序排列1.显示全部的非销售人员:job<>’SALESMAN’select * from emp where job<>'salesman';2.按工作分组同时求出工资的总和Select job,SUM(sal) from emp WHERE job<>’SALESMAN’ GROUP BY job;3.对分组条件进行限制Select job,SUM(sal) from emp WHERE job<>’SALESMAN’GROUP BY job HA VING SUM(sal)>5000;4.使用排序,按升序排列Select job,SUM(sal) su from emp WHERE job<>’SALESMAN’GROUP BY job HA VING SUM(sal)>5000 order by su;分组的简单原则:只要一列上存在重复的内容才考虑用分组注意:分组函数可以嵌套使用,但是在组函数嵌套的时候不能再出现分组条件的查询语句范例:求出平均工资最高的部门错误代码:Select deptno,MAX(AVG(sal)) from emp GROUP BY deptno;Select MAX(A VG(sal)) from emp GROUP BY deptno;(正确)子查询范例:要求查询出比7654工资高的全部雇员信息首先:要知道7654雇员的工资是多少然后:以此查询结果为查询依据,只要其他工资大于sal,则表示符合条件首先:查询出比7654工资高的全部雇员信息select * from emp where sal>(select sal from emp where empno=7654);其次:与7788工作一样Select job from emp where empno=7788所以:select * from emp where sal>(select sal from emp where empno=7654) and job= (Select job from emp where empno=7788);;数据库更新操作数据库的主要操作分为两种:1..数据库的查询操作SELECT2..数据库的更新操作uUPDA TE,DELETE,INSERT此时为了保存原始的emp表的信息,在进行更新删除插入表前先将表复制一份Create table myemp AS select * from emp;此时数据已经复制出来添加数据Insert into emp(empno,ename,job,hiredate,sal ,deptno)Values (7899,’张三’,’清洁工’,’20-2月-2000’,9000, 40);使用简略写法(并不推荐),因为现在是要添加所有字段的内容,所以可以不写上任何字段名称,只要值的数量和顺序和数据库表中的顺序一致。
Insert into myemp values(7899,’张三’,’清洁工’,9000, 40);之前插入数据的时候,日期的格式是使用了表中固定好的格式,如果现在有这样一个日期”2009-10-10”日期格式,那么现在如何把这种格式的日期插入进去呢?使用TO_DATE()函数,将一个字符串类型的数据变为DATE类型的数据。
Insert into myemp(empno,ename,job,hiredate,sal ,deptno)Values (7899,’张三’,’清洁工’,TO_DA TE(‘2009-07-19’,’yyyy-mm-dd’),9000, 40);修改数据UPDATE 表名称set 要修改的字段=新值,要修改的字段=新值….;UPDATE 表名称set 要修改的字段=新值,要修改的字段=新值…WHERE 修改条件.;修改数据删除全部: DELETE FROM 表名称局部删除: DELETE FROM 表名称WHERE 删除条件;事物处理范例:创建一张只包含10部分雇员的一张临时表CREATE TABLE EMP10 AS SELECT * FROM EMP WHERE DEPTNO=10;打开一个oracle终端,进行删除操作DELETE FROM EMP10 WHERE SAL=2450;显示已经删除然后再打开另外一个oracle终端,查询到sal=2450这条数据还在,证明这条数据并没有被删除,这就是oracle事务的概念。
事务处理: 就是保证数据操作的完整性,所有的操作要么同时成功要么同时失败。
在ORACLE中对每一个连接到数据库中的窗口,都会与数据库建立一个Session。
一个Session对数据库所做得修改不会马上反应到数据库的真实数据之上。
是允许回滚的,当一个Session提交所有操作之后,数据库才真正做出修改。
进行同样的删除操作DELETE FROM EMP10 WHERE SAL=2450;结果它停住了,等待第一个终端操作结束再动。
----》这就是ORACLE死锁提交事务:COMMIT回滚:RoolbackORACLE常用命令查看所有表select table_name from user_tables;显示表结构describe nchar_tst(nchar_tst为表名)查询练习1.列出至少有一个员工的所有部门信息第一步: 列出所有部门的员工数量Select deptno ,count(empno) from emp group by deptno;第二步:列出员工大于1的部门Select deptno ,count(empno) from emp group by deptno HA VING COUNT(empno)>1;第三步:通过多表关联查,把子查询做为一个查询出来select d.*,ed.couFROM dept d,(SELECT deptno,COUNT(empno) cou FROM empGROUP BY deptno HA VING COUNT(empno)>1) edWHERE d.depno=ed.deptno ;2.列出薪金比SMITH多的所有员工第一步:求出SMITH的工资SELECT sal FROM EMP WHERE ENAME=’SMITH’;第二步:select ename from emp where sal>( SELECT sal FROM EMP WHERE ENAME=’SMITH’);3 列出所有员工姓名及其直接上级的姓名此程序属于自身关联查询SELECT e.ename, d.ename FROM emp e ,emp d where e.mgr=d.empno;4. 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称自身关联,查找mgr=empno的同时还要比较Hiredate第一步:SELECT e.empno,e.enameFROM emp e, emp m WHERE e.mgr=m.empno AND e.hiredate<m.hiredate;第二步:SELECT e1.empno,e1.ename,d.dnameFROM(SELECT e.empno,e.enameFROM emp e, emp m WHERE e.mgr=m.empno AND e.hiredate<m.hiredate) e1,dept d WHERE e1.deptno=d.deptno;ORA-00904 invalid identifier这个错误是因为字段名写错了检查下字段名,发现e1表的查询结果没有deptno字段,所以报错了!SELECT e1.empno,e1.ename,d.dnameFROM(SELECT e.empno,e.ename,e.deptnoFROM emp e, emp m WHERE e.mgr=m.empno AND e.hiredate<m.hiredate) e1,dept d WHERE e1.deptno=d.deptno;李兴华给出的标准答案是:SELECT e.empno,e.ename,d.dname FROM emp e, emp m, dept dWHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno;5. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门左右关联问题SELECT d.deptno,d.dname,e.ename,e.empnoFROM dept d, emp eWHERE d.deptno=e.deptno(+);查询结构少了40部门的信息,用连接操作SELECT d.deptno,d.dname,e.ename,e.empnoFROM dept d, emp eWHERE d.deptno=e.deptno(+);6. 列出所有”CLERK”(办事员)的姓名及部门名称, 部门的人数2.入手第一步:在emp表中查询出职位job为CLERK所在的部门名称(dept表)、○2…..SELECT e.ename ,d.dname FROM dept d,emp e WHERE e.job=’CLERK’ANDe.deptno=d.deptno ;3.部门人数肯定要用分组查询,如果是分组查询肯定要用GROUP BY,而上面的语句明显不能用GROUP BY了,因为查询字段太多。