select ename,salary,deptno from emp_tian --- -------- where salary >= 8000; --- -------- -----------------------------------------------------------------------
################# 二.多表联合查询 ############################## join 关联 关联查询——将两个表通过对应关系拼起来
-----------------------把emp_tian和dept_tian两个表拼起来------------------- select distinct emp_tian.*,dept_tian.* from emp_tian join dept_tian
on emp_tian.deptno = dept_tian.deptno;
select e.ename,e.job,d.dname,d.loaction from emp_tian e join dept_tian d on e.deptno = d.deptno;
emp_tian 主键(Primary Key) -----员工编号 dept_tian 主键(Primary Key) -----部门编号
dept_tian 的主键 参照emp_tian 中的员工部门编号
--主键: Primary Key = PK --列值是唯一的,不重复的 --主表 / 父表
--外键: Foreign Key = FK --列值参照某个主键列值 --从表 / 子表
--被参照的为主键 参照的外键
--主键(PK)所在表为主表(父表) 外键(FK)所在的表称为从表(子表) -----------------查询学生表的学生成绩--------------------------------
select stu.student_name,per.test_score,per.subject_id,sub.subject_name from t_student_tian stu join t_performance_tian per on stu.student_id = per.student_id join t_subject_tian sub
on sub.subject_id = per.subject_id;
-----------------查询1班学生表的学生成绩--------------------------------
select stu.student_name,per.test_score,per.subject_id,sub.subject_name from t_student_tian stu join t_performance_tian per on stu.student_id = per.student_id join t_subject_tian sub
on sub.subject_id = per.subject_id where stu.class_id = 1;
-----------------查询1班学生表的学生各科成绩总分(直接加)排-------------------- select stu.student_name,sum(per.test_score) total from t_student_tian stu join t_performance_tian per on stu.student_id = per.student_id
where stu.class_id = 1
group by stu.student_name order by total desc;
-------------------------查找某个员工的经理的名字---------------------------
------------- 丢掉不匹配元素 部门是null的员工不会被查出来 ------------ ---------------------- 没有员工的部门也不会被查出来 ------------ ---------------------------- 内连接 ------------------------- select e.ename,d.dname
from emp_tian e join dept_tian d on e.deptno = d.deptno;
-------------------查找某个员工的经理的名字 无损失------------------------ --------------------- 外连接 -------------------------------- select e.ename,d.dname
from emp_tian e left outer join dept_tian d on e.deptno = d.deptno;
--------------------- 左外连接 -------------------------------- select e.ename,d.dname
from dept_tian d right outer join emp_tian e on e.deptno = d.deptno;
--------------------- 右外连接 -------------------------------- 外连接的结果集 = 内连接的结果集 +
驱动表中在匹配表中没有对应记录的记录和空值的组合 谁做驱动表
------------------------------------------------------------------------------ select e.ename,d.dname
from dept_tian d full outer join emp_tian e on e.deptno = d.deptno;
-------------------- 全外连接 --------------------------------- --哪些部门没有员工? --1.关联子查询实现 select dname, location from dept_tian x where not exists (
select 1 from emp_tian where deptno = x.deptno) --2.集合
select deptno from dept_tian minus
select distinct deptno from emp_tian; --3.外连接
--where 匹配表的pk is null = 驱动表中匹配不上的记录. --相当于过滤掉内连接的结果集.
select e.empno, e.ename, d.deptno, d.dname, d.loaction from emp_tian e right outer join dept_tian d
on e.deptno = d.deptno where e.empno is null;
-----------------------Top-N分析 查找最XX的谁--------------------------------- ----------------------------薪水最高的三个------------------------------------- -----伪列rownum
-------------------------有问题
select rownum,ename,nvl(salary,0) salary from emp_tian where rownum <= 3
order by nvl(salary,0) desc; --期望:先排序，在选前三 --实际:先选前三，再排序 -----------------------正确代码 select *from (
select ename,nvl(salary,0) salary from emp_tian order by nvl(salary,0) desc) where rownum <= 3;
--------------- 查找1班总分第一的人 --------------------- select *from(
select stu.student_name, sum(per.test_score) tol_score from t_student_tian stu join t_performance_tian per on stu.student_id = per.student_id where stu.class_id = 1
group by stu.student_name order by tol_score desc) where rownum = 1;
-----------------------------函数方法------------------------------------ create or replace function max_tian(p_class_id number) return number is
v_total_score number; begin
--v_total_score 赋值指定为对应班级最高分
select tol_score into v_total_score --得到值的同时赋值 from(
select sum(per.test_score) tol_score
from t_student_tian stu join t_performance_tian per on stu.student_id = per.student_id where stu.class_id = p_class_id group by stu.student_name order by tol_score desc )
where rownum = 1; return v_total_score; end;
/
create or replace function max_tian1(p_class_id number) return number is
v_student_id number; begin
--v_total_score 赋值指定为对应班级最高分 select id into v_student_id from(
select stu.student_id id,stu.student_name, sum(per.test_score) tol_score from t_student_tian stu join t_performance_tian per on stu.student_id = per.student_id where stu.class_id = p_class_id
group by stu.student_name,stu.student_id order by tol_score desc )
where rownum = 1; return v_student_id; end; /
select max_tian(1) from dual; select max_tian(2) from dual;
--------------------------得到拿最高分的人----------------------------- select stu.student_name,sum(per.test_score) sum_score from t_student_tian stu join t_performance_tian per on stu.student_id = per.student_id where stu.class_id = 1
group by stu.student_name
having max_tian(1) = sum(per.test_score);
DML:insert / update /delete 表内容的修改 DDL:create /drop /alter / truncate 表结构的修改 DCL: grant /revoke 权限的赋予和删除
一.DML 1.insert
------------------------全信息插入----------------------------------- insert into dept_tian
values(55,'market','beijing');
=============================== 等价 ============================== insert into dept_tian ( deptno , dname , loaction ) values( 55 ,' market ' , ' beijing ' );
----------------------------常见错误---------------------------- -------too manay values
insert into dept_tian ( deptno , dname , loaction ) values( 55 ,' market ' , ' beijing ','下沙');
-------not enough values
insert into dept_tian ( deptno , dname , loaction ) values( 55 ,' market ');
--------------------------------插入包含时间的信息------------------------------------------------------ insert into emp_tian(empno,ename,salary,hiredate)
values(1234,'圣皇子',5356, to_date('2012/01/02' , 'yyyy/mm/dd') );
-----------------------------------------------建测试表(批量复制)---------------------------------------- create table emp_bak_tian as( select*from emp_tian where deptno = 10 );
select*from emp_bak_tian;
---------测试完一部分数据后清空数据
delete from emp_bak; ------------------清空数据，但是保留表的格式 select count(*) from user_objects; select count(*) from all_objects;
------------------------------------------------建一个大表--------------------------------------------- create table myobjects_tian as ( select*from user_objects where rownum < 500 ); select*from myobject_tian; delete myobject_tian;
select*from myobject_tian;
insert into myobject(ename) values('傻逼')
2.update update 表名
set 列i = 新值i..... where 条件
rollback ------------------取消之前(上一次commit之前)的操作 select ename,salary from emp_tian where deptno = 10;
3.delete