Oracle课堂笔记

---------?? create建表 ??------------------ create table emp_tian(

empno number(4)delete, ename varchar(20), job varchar(20), salary number(7,2), bonus number(7,2), hiredate date,

manager number(4), deptno number(2) )

-----------------------------------------?? insert添加信息 ??------------------ insert into emp_tian values(1001,'zhangwuji','manager',10000,2000,'12-mar-10',1005,10); insert into emp_tian values(1002,'liucangsong','analyst',8000,1000,'01-apr-11',1001,10); insert into emp_tian values(1003,'liyu','analyst',9000,1000,'11-apr-10',1001,10); insert into emp_tian values(1004,'guoferong','programmer',5000,null,'01-jan-11',1001,10); insert into emp_tian values(1005,'zhangsanfeng','persident',15000,null,'15-may-08',null,20); insert into emp_tian values(1006,'yanxiaoliu','manager',5000,400,'01-feb-09',1005,20); insert into emp_tian values(1007,'luwushuang','clerk',4000,500,'01-feb-09',1006,20); insert into emp_tian values(1008,'huangrong','manager',4000,500,'01-may-09',1005,30); insert into emp_tian values(1009,'weixiaobao','salesman',4000,null,'20-feb-09',1008,30); insert into emp_tian values(1010,'guojing','salesman',4500,null,'10-may-09',1008,30); ---------------调节数据显示(仅在Oracle中试用，重登后无效)---------------------- 列宽调节

column empno for 9999 ---------------column 可用col代替 column ename for a10 ---------------format 可用for代替 column manager format 9999 column salary format 99999 column bonus for 9999 col job for a10

col ename format a10 col job format a12 翻页设置

set pagesize 100 set linesize 300

调节数据显示(仅在Oracle中试用，重登后无效) ---------?修改信息?------------------ update emp_tian set job = \where ename = 'jizyue';

commit; --------------提交 --------------拷贝--------------- create table emp_tian1 as

select*from emp_tian;

-------------查询-------------------------- select ename ,job from emp_tian

where lower(job) in('analyst','clerk','programer');

select ename ,deptno from emp_tian where lower(deptno) in(10,20);

select ename ,salary from emp_tian where salary >= 5000 and salary <= 8000;

-------------------------等价于 where salary betwen 5000 and 8000;

nvl(***,0) ----当***中内容为null时，则返回0，否则返回***的值 select ename.salary from emp_tian where nvl(salary,0)=0;

-----------找到内容为空的选项--------------

select ename from emp_tian where salary is null; -----------找到非空的选项---------

select ename,salary from emp_tian where salary is not null; -----------得到某个项目的数目---------------- select count(*) from user_tables;

show user -------显示当前用户

-------通配符%查找文件名为EMP+任意字符的文件-------------- select table_name from user_tables where table_name like 'EMP%';

select ename ,salary,

round(salary*0.12345678) as tax from emp_tian; ---------------round（num,xxx） 四舍五入---------------- ----------------- xxx(截取整数) ----------------- xx0(保留整十数)

----------------- xxx.xx(保留两位小数) select ename ,salary,

round(salary*0.12345678) as tax, round(salary*0.12345678,-1) as tax1, round(salary*0.12345678,2) as tax2 from emp_tian;

---------------trunc直接截取-------------------------------- select ename ,salary,

trunc(salary*0.12345678) as tax, trunc(salary*0.12345678,-1) as tax1, trunc(salary*0.12345678,2) as tax2 from emp_tian;

--------------得到系统日期-------------------dual 是一个虚表 单行单列的表 select sysdate from dual;

---------------------按照制定的格式显示------------------

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as mytime from dual; select to_char(sysdate,'year month mon day dy am') from dual; select to_char(sysdate,'YEAR MONTH mon day dy am') from dual; ---------------按照制定的格式显示某个时间量-------------------

select ename,hiredate,to_char(hiredate,'yyyy-mm-dd') hire from emp_tian;

一.Oracle中的日期处理 select sysdate from dual;

----------------计算两个日期之间的间隔------------------------

select ename,hiredate,sysdate-hiredate as days from emp_tian; ---------------计算间隔天数(整数)-----------------------------

select ename,hiredate,round(sysdate-hiredate) as days from emp_tian; -----------------计算间隔月份----------------------------------------

select ename,hiredate,months_between(sysdate,hiredate) as days from emp_tian; -----------------计算间隔月份整月份--------------------------------------------

select ename,hiredate, round(months_between(sysdate,hiredate)) days from emp_tian;

----------------------计算某月的最后一天------------------------------ select last_day(sysdate) from dual;

------------------------计算三个月后的日期----------------------------------- select add_months(sysdate,3) from dual;

------------------------计算10天前/后的日期------------------------------- select sysdate-10 from dual; select sysdate+10 from dual;

--------------------修改wanxiaofei的入职时间为12年2月1号--------------------- update emp_tian set hiredate = '01-FEB-12' where ename ='wanxiaofei'; ---------------------将字符串变成日期(按照特定格式)-------------------------

update emp_tian set hiredate = to_date('2012/03/01','yyyy/mm/dd') where ename = 'wanxiaofei';

----------------------增加职员：1012,'jerry','2012-01-12'----------------------- insert into emp_tian(empno,ename,hiredate)

values(1012,'jerry',to_date('2012/01/01','yyyy/mm/dd')); --------------------------------------------------

to_char to_number - 日期------------>字符----------->数字 - <----------- <---------- -

to_date tochar -

-------------------------------------------------- 数字 字符 数字 - 10000------------->$10,000.00--------->10000 -

to_char to_number - --------------------------------------------------

二.单行函数计算 -------- 一个数据对应一个结果 nvl(bonus,0)

upper(job) = 'ANALYST' round(salary,2)

to_char(sysdate,'yyyy-mm-ss') to_date('2012/03/01','yyyy/mm/dd')

------------如果有奖金发奖金，没奖金的发工资的一半，都没有的话发一百----------- select ename,salary,bonus,coalesce(bonus,salary*0.5,100) as finalbonus from emp_tian;

-----------------雅思计分-------------------------------- 姓名 听力 阅读 写作 口语 总分

叶凡 5 5 7 8 6.25->6.5

[0,0.25) [0.25,0.75) [0.75,1) 0 0.5 1

select*from ielts_tian;

-----------------------计算每人四项平均结果-----------------------------------

select name,scr1,scr2,scr3,scr4,(scr1+scr2+scr3+scr4) score from ielts_tian; -------------------------------取模------------------------------------------- 得到小数位 mod(7.25,1) -------结果 0.25 得到整数位 trunc(7.25) -------结果 7 case when ....then when ....then when ....then end

整数部分 trunc((scr1+scr2+scr3+scr4)/4) 小数部分 mod((scr1+scr2+scr3+scr4)/4,1)

----------------------雅思算分代码---------------------------------------

select name,scr1,scr2,scr3,scr4,trunc((scr1+scr2+scr3+scr4)/4)+ case when mod((scr1+scr2+scr3+scr4)/4,1)<0.25 then 0 when mod((scr1+scr2+scr3+scr4)/4,1)>=0.25

and mod((scr1+scr2+scr3+scr4)/4,1)<0.75 then 0.5 when mod((scr1+scr2+scr3+scr4)/4,1)>=0.75 then 1 end as ieltscore from ielts_tian;

-----------------------------数据库建函数-------------------------------------- create or replace function calculation_tian(score number) return number is

--定义变量

i number;--整数 j number;--小数

result number;--结果 begin --程序体

i := trunc(score);--数据库中赋值:= j := mod(score,1); if j<0.25 then result :=i+0;

elsif j>=0.25 and j<0.75 then result :=i+0.5; elsif j>=0.75 then result :=i+1; end if;

return result; end; / --执行

show errors --------------检查错误 语法

----------------用自己的函数代替繁杂代码-------------------------------- select name,scr1,scr2,scr3,scr4,(scr1+scr2+scr3+scr4)/4 as agv, calculation_tian((scr1+scr2+scr3+scr4)/4) as ieltsscore from ielts_tian;

select ename,salary,bonus from emp_tian order by bonus; ----------------------分别提薪---------------------方法一 select ename,job,salary,

case job when 'clerk' then nvl(salary,0)*1.05

when 'programmer' then nvl(salary,0)*1.1 when 'analyst' then nvl(salary,0)*1.15 else nvl(salary,0)

end as new_salary from emp_tian;

----------------------分别提薪---------------------方法二 select ename,job,salary,

case when job ='clerk' then nvl(salary,0)*1.05

when job = 'programmer' then nvl(salary,0)*1.1 when job = 'analyst' then nvl(salary,0)*1.15 else nvl(salary,0)

end as new_salary from emp_tian;

----------------------分别提薪---------------------方法三 select ename,job,salary,

decode(job,'clerk',nvl(salary,0)*1.05,'programer',nvl(salary,0)*1.1,'analyst', nvl(salary,0)*1.15,nvl(salary,0)) as newsarary from emp_tian;

三.分组函数计算 -------------- 处理多个数据，得到单个结果 count(*) 查记录

select count(*) from emp_tian; select count(*) from user_tables; select count(*) from user_objects;

----------------查询员工中赚钱最多/少的工资------------------------- select max(salary) from emp_tian; select min(salary) from emp_tian;

----------------计算雅思得分总分最大/小的得分----------------------- select max((scr1+scr2+scr3+scr4)) as totalmax from ielts_tian; select min((scr1+scr2+scr3+scr4)) as totalmin from ielts_tian; ----------------计算入职时间最早/最晚的时间------------------------- select min(hiredate) from emp_tian;

----------------求和每月人力总支出--------------------------------- select sum(nvl(salary,0)) from emp_tian;

----------------求平均值每月人力平均支出--------------------------------- select avg(nvl(salary,0)) from emp_tian; select sum(salary)/count(*) from emp_tian; -------------------忽略空值查询

select sum(salary)/count(salary) from emp_tian; select avg(salary) from emp_tian;

--------------------组函数(从一组数据中查找某个元素)--------------------------- ---------------------------sum/agv/min/max--------------------------------------

四.子查询---------------------在一个主查询语句中包好另一个主查询语句 ********************************************************************************

子查询一次性给出语句，减少I/O次数，提高访问效率 ******************************************************************************** ---------------查询员工中赚钱最多/少的工资的员工---------------------------

select ename,salary from emp_tian where salary = (select max(salary) from emp_tian); select ename,salary from emp_tian where salary = (select min(salary) from emp_tian);

----------查询每个部门的人数(分组计算)按什么分组，下面group by 要与之对应------- ---------------------group by 按照什么什么分组-------------------------------

select deptno,count(*) from emp_tian where deptno is not null group by deptno order by deptno;

---------------查询每个部门的人数---得到最多的人数-----------------------

select max(count(*)) as maxNum from emp_tian where deptno is not null

group by deptno ;

--------------查询每个部门的人数---得到最多的人数的部门编号-------------------- select deptno,count(*) from emp_tian where deptno is not null group by deptno having count(*) = 4 order by deptno;

update emp_tian set deptno = 20 where deptno is null;

select deptno,count(*) from emp_tian where deptno is not null group by deptno having count(*) = (

select max(count(*)) as maxNum from emp_tian where deptno is not null group by deptno ) order by deptno;

-----------查询部门最多的人数的部门编号对应的地理位置和部门民称---------------- 分步骤理解

select*from dept_tian;

select dname,loaction from dept_tian

where deptno = (select deptno from emp_tian group by deptno

having count(*) = (select max(count(*)) as maxNum from emp_tian group by deptno) );

------------哪些部门(部门编号)的总工资共和比整部门20的总工资高--------------- 首先：算出每个部门的工资共和

select deptno,sum(salary) from emp_tian group by deptno; 然后：得到部门20 的总工资

select deptno,sum(salary) from emp_tian where deptno = 20 group by deptno; select sum(salary) from emp_tian where deptno = 20 group by deptno;

--不显示部门编号 最后:组合

select deptno,sum(salary) from emp_tian group by deptno

having sum(salary) > (select sum(salary) from emp_tian where deptno = 20 group by deptno);

----------------------------哪些部门的员工人数大于5---------------------- 首先：查出每个部门的员工人数

select deptno,count(deptno) from emp_tian group by deptno; 最后:组合

select deptno,count(deptno) from emp_tian group by deptno having count(*)>5;

-------------------------------谁的工资比刘苍松高------------------------- 首先：列出类个人的工资

select ename,salary from emp_tian where salary is not null;

然后：得到刘苍松的工资

select ename,salary from emp_tian where salary is not null and ename ='刘苍松'; 最后组合：

select ename from emp_tian where salary is not null and salary > (select salary from emp_tian where salary is not null and ename ='');

如果有同名的人，就要注意，有两种解决办法 1.加限定条件

2.将>改为 all或者any ----all所有 any任意一个

------------------------谁和老疯子同部门------------------------------------- 首先：列出所有人以及其所属部门

select empno,ename,deptno from emp_tian;

----------------查询每个部门赚最多钱的人--------------------------- 首先：得到每个部门的最多的薪水

select deptno,max(salary) from emp_tian group by deptno; 最后：查找每个部门对应的最高工资的人名 select ename,deptno,salary from emp_tian

--where (A) in (B) ; 语法,将A中的信息和B中的信息比较

where (deptno,salary) in(select deptno,max(salary) from emp_tian group by deptno); ---------------------------更新信息------------------------------- update emp_tian

set ename = '张三丰' where ename = 'zhangsanfeng'; update emp_tian

set ename = '陆无双' where ename = 'luwushuang'; update emp_tian

set ename = '张无忌' where ename = 'zhangwuji'; update emp_tian

set ename = '刘祤' where ename = 'liyu'; update emp_tian

set ename = '郭芙蓉' where ename = 'guoferong'; update emp_tian

set ename = '叶瞳' where ename = 'yanxiaoliu'; update emp_tian

set ename = '姬紫月' where ename = 'huangrong'; update emp_tian

set ename = '姬皓月' where ename = 'guojing'; update emp_tian

set ename = '摇光' where ename = 'weixiaobao'; update emp_tian

set ename = '老疯子' where ename = 'wanxiaofei'; update emp_tian

set ename = '人魔老头' where ename = 'wanxiaofei'; update emp_tian

set ename = '庞博' where ename = 'jerry'; update emp_tian

set ename = '叶凡' where ename = 'jizyue'; update emp_tian

set ename = '刘苍松' where ename = 'liucangsong'; commit;

select*from emp_tian; /

drop --------------------删除表

select table_name from user_tables where table_name like '%_TIAN%';

select table_name from user_tables where table_name like 'EMP%';

drop TEMP_TIAN;

----------------------------------------------------------------------

脚本文件(建表,插表,提交等代码存放的文件) xxx.sql 扩展名约定为.sql -------------------作用：初始化数据库--------------------------------- ------------每个班分数加起来是否等于100-------------------------- select class_id,sum(scale) from t_assess_rule_tian group by class_id;

------------每个学生的总分？按总分高低排序------------------------- select student_id ,sum(test_score) from t_performance_tian group by student_id order by 2; ###################### 一.子##############################

-----------------------------谁的总成绩比一号学生成绩高------------------- 首先：得到每个同学的总成绩

select student_id,sum(task_score) from t_performance_tian group by student_id;

然后：得到一号同学的总成绩

select sum(task_score) from t_performance_tian where student_id = 1; 最后：组合

select student_id,sum(task_score) from t_performance_tian group by student_id

having sum(task_score)>(

select sum(task_score) from t_performance_tian where student_id = 1) order by sum(task_score) desc;

-----------------------------谁的薪水比公司的平均薪水低------------------- select deptno,ename,nvl(salary,0) salary from emp_tian where nvl(salary,0) < (

查询

select avg(nvl(salary,0)) from emp_tian);

-----------------------------谁的薪水比部门的平均薪水低--------------------

select deptno,ename,nvl(salary,0) salary from emp_tian x ----- x为别名 where nvl(salary,0)<(

select avg(nvl(salary,0)) from emp_tian

where deptno = x.deptno ); ---------x.deptno关联子查询 ----------------------------每个部门的平均薪水--------------------------------

select deptno,avg(nvl(salary,0)) avg_sal from emp_tian group by deptno;

delete emp_tian where deptno is null;

------------------------谁的薪水比同一个经理的人的平均薪水低------------------- select manager,ename,nvl(salary,0) salary from emp_tian x where nvl(salary,0)<(

select avg(nvl(salary,0)) from emp_tian where manager = x.manager );

----------------------每个部门同经理的人的平均薪水-------------------------

select manager,avg(nvl(salary,0)) avg_sal from emp_tian group by manager; -------------------------哪些员工是别人的经理------------------------------ select empno,ename from emp_tian x

where exists (select empno from emp_tian where manager = x.empno); select ename,manager from emp_tian;

--------------------------哪些员工不是别人的经理--------------------------- select empno,ename from emp_tian x

where not exists (select empno from emp_tian where manager = x.empno);

---------------------------哪些部门没有员工------------------------------- select deptno,dname,loaction from dept_tian x where not exists (select 1 from emp_tian where deptno = x.deptno);

--------------------------------------------------------------------------

select deptno from dept_tian --- -------- minus --- 集合操作 差集 -------- select distinct deptno from emp_tian; --- -------- --------------------------------------------------------------------------

select ename,salary,deptno from emp_tian --- -------- where salary < 9000 --- -------- union --去掉重复元素 /union all 不去重 --- 集合操作 合集-------- select ename,salary,deptno from emp_tian --- -------- where salary >= 8000; --- -------- ----------------------------------------------------------------------------

select ename,salary,deptno from emp_tian --- -------- where salary < 9000 --- -------- intersect --- 集合操作 交集--------

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

delete emp_tian where empno = 1234; delete emp_tian where deptno =10; delete emp_tian; rollback;

set salary = salary +10000 where deptno = 10;

select ename,salary from emp_tian where deptno = 10; rollback;

select ename,salary from emp_tian where deptno = 10;

----------------------------------------制造大表------------------------------------------------- ------------------------------------循环插入--自我复制--------------------------------------- insert emp_bak_tian (select*from emp_tian);

insert emp_bak_tian (select*from emp_bak_tian);

--------------------删除重复的数据 仅试用重复记录较多的表----------------------- create table emp_tian2 as(select distinct empno,ename,salary from emp_tian); select*from emp_tian2;

------------------------------------------重命名----------------------------------------------------- drop table emp_tian1;

rename emp_tian2 to emp_tian1;

---------------------删除重复的数据 重复记录较少的表 ----------------------- create table emp_tian3 as ( select*from emp_tian ); insert into emp_tian3(ename,salary) values('傻逼',2000); select*from emp_tian3;

---- 1 --------查询重复数据中的地址最大的记录----------------

select max(rowid) from emp_tian3 group by empno,ename,salary ---- 2 -------删除地址不等于最大地址值的重复记录---------------- select*from emp_tian3;

select empno,ename,salary, rowid from emp_tian3; delete emp_tian3 where rowid not in (

select max(rowid) from emp_tian3 group by empno,ename,salary );

select*from emp_tian3;

commit/rollback ----------和事务(Transaction)相关的语句 ---事务开始

DML:insert / update /delete --加锁 ---事务结束

commit/rollback --解锁

-------------------如果操作已经加锁的信息，会刮起，直到得到相应的锁----------------------------- ---------------------------开始事务---------------------------- update account

set money = menry - 500 ; where id = 'A';

update money = money + 500 where id = 'A';

if(都成功) commit; else

rollback;

-------------------------事务结束----------------------------- 正常退出会话： 自动commit 异常退出会话： 自动rollback DDL操作：提交之前的操作

set antocommit on ----设置自动提交(所有DML操作都会触发) savepoint a ---设置书签(标记)

create table fa (id number); -----事务的起点，不能在rollback中回滚 insert into fa values(1); insert into fa values(2); insert into fa values(3); insert into fa values(4); insert into fa values(5); insert into fa values(6); select *from fa;

rollback ; --表还在数据没了 select *from fa;

create table fa (id number); -----事务的起点，不能在rollback中回滚 insert into fa values(1); insert into fa values(2); savepoint a; --书签 insert into fa values(3); insert into fa values(4); savepoint b; --书签 insert into fa values(5); insert into fa values(6); savepoint c; --书签 insert into fa values(7); select *from fa;

rollback to a;--表中还剩下a之前的数据 select *from fa;

--假如执行 rollback to b,c点就不存在了，不能在rollback to c , a点仍然存在，可以继续

rollback to a

连接数据库的工具: --首选,命令行工具 sqlplus --图形工具

pl/sql developer(非官方,免费) toad(非官方,收费)

sql developer(官方,免费,11g以上)

补充练习: procedure 过程

PL/SQL: Procedure Language / SQL

函数(function) 过程(procedure) 包(package) 触发器(trigger)

---------------------------输入班号,输出最高分的学生名字和总成绩-------------------------------------- create or replace procedure cal_tian(

p_class_id in number, p_student_name out char, p_total_score out number ) is begin

select student_name, total_score into p_student_name, p_total_score from (

select stu.student_name,

sum(per.test_score) total_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 total_score desc) where rownum < 2; end; /

--------------------------打开输出,默认值是off SQL>set serveroutput on

---------------------匿名块,用来测试过程或函数 SQL>declare

p_student_name char(20); p_total_score number;

begin

cal_tian(&no, p_student_name, p_total_score); --向控制台输出变量值,System.out.print dbms_output.put_line(p_student_name); dbms_output.put_line(p_total_score); end; /

--可以用&abc符号表示在运行时输入变量值 select * from emp_tian where deptno = &abc;

----------------改进版,输入共多少个班,输出每个班的最高成绩的学生姓名和总分------------------ ------------假设是班号是1-p_class_num create or replace procedure cal_tian1( p_class_num in number) is

p_student_name char(20); p_total_score number; begin

for i in 1..p_class_num loop

select student_name, total_score into p_student_name, p_total_score from (

select stu.student_name,

sum(per.test_score) total_score

from t_student_tian stu join t_performance_tian per on stu.student_id = per.student_id where stu.class_id = i

group by stu.student_name order by total_score desc) where rownum < 2; dbms_output.put_line

(p_student_name || ', ' || p_total_score); end loop; end; /

SQL>exec cal_tian1(2); --2个班

------------------------------DDL操作 数据定义语言----------------------------------------------------- ----------------------------DDL:create /drop /alter / truncate--------------------------------------- alter :修改结构 drop :删除结构+数据(如果有) update:修改数据 delete:删除数据

==================================================================== 一.??????约束条件-----(数据表中数据必须遵循的规

则)????????????????????

1】关键(必要)的数据为空 学号，姓名，性别为空 2】用于个体区分的唯一数据重复 学号重复

3】数据不正常 年龄1000，只有3个班，班级写25，性别不为男女之一

--------------------------------------------------五大约束条件---------------------------------------------- primary key 主键 pk foreign key 外键 fk not null 非空 un unique 唯一 uk check 检查 ck

-------------------------------------------建表时，建立主键约束条件---------------------------------------- -------------------------------------------id列受限--------------------------------------------- create table student_tian( id number(4) primary key, name char(10) );

insert into student_tian(id,name) values(1001,'tian');

----报错 ORA-00001 unique constraint violated 唯一的约束被违反

------------------------------------constraint 约束------------------------------------------------- insert into student_tian(id,name) values(1001,'yang');

----报错 ORA-01400 cannot insert null into (\--------------------------------------------------------------登录用户-----------报名----------列号

insert into student_tian(name) values('asdas');

某些数据库：主键自增长(降低程序员负担) mysql / sql server Oracle数据库: 序列(主键发生器) 向外产生不重复的数字

-----------------------------------------name列 非空约束------------------------------------------ drop table student_tian; create table student_tian( id number(4) primary key, name char(10) not null );

insert into student_tian(id,name) values(1001,'tian');

---报错 ORA-01400 cannot insert null into (\------------------------------------------------------------登录用户-------------报名------------列号 insert into student_tian(id)

values(1002);

---------------------------------------------------emial列 unique---------------------------------------------- drop table student_tian; create table student_tian(

id number(4) primary key, name char(10) not null, email char(20) unique );

insert into student_tian(id,name,email) values(1001,'tian','123@tarena.com');

----报错 ORA-00001 unique constraint violated 唯一的约束被违反

insert into student_tian(id,name,email) values(1002,'yang','123@tarena.com');

----------------------------------------gender列 check-------------------------------------------------- ---gender: 只允许 'M' 男 'F' 女 drop table student_tian; create table student_tian(

id number(4) primary key, name char(10) not null, email char(20) unique,

gender char(1) check (gender in ('M','F')) );

insert into student_tian(id,name,email,gender) values(1001,'tian','123@tarena.com','F'); insert into student_tian(id,name,email) values(1002,'yang','1234@tarena.com');

---报错：ORA-02290 check constaint violated

insert into student_tian(id,name,email,gender) values(1002,'yang','1234@tarena.com','A');

desc student_tian -----------------------查看表结构 user_tables ------------------------查看用户表 user_objects ------------------------查看用户对象 user_procedure ------------------------查看用户过程 user_constraints -------------------------查看约束条件

select constraint_name,constraint_type from user_constraints where table_name = 'STUDENT_TIAN';

--------------------约束条件constraint 表名_列名_约束类------------------------ ---- 列级约束

drop table student_tian; create table student_tian(

id number(4) constraint stu_t_id_pk primary key, name char(10) constraint stu_t_name_nu not null,

email char(20) constraint stu_t_email_uk unique,

gender char(1) constraint stu_t_gender_ck check (gender in ('M','F')) );

---- 表级约束

drop table student_tian; create table student_tian(

id number(4) ,

name char(10) constraint stu_t_name_nu not null, email char(20) , gender char(1)

constraint stu_t_id_pk primary key (id)

constraint stu_t_email_uk unique (email)

constraint stu_t_gender_ck check (gender in ('M','F')) );

---- 建表，除了非空以外的约束条件，全部放在建表以后在建 drop table student_tian; create table student_tian(

id number(4) ,

name char(10) not null, email char(20) , gender char(1) );

--------------------------------------- 建完表以后添加约束条件-------------------------------------- alter table student_tian add

constraint stu_t_id_pk primary key (id);

alter table student_tian add

constraint stu_t_email_uk unique (email);

alter table student_tian add

constraint stu_t_gender_ck check (gender in ('M','F'));

----专业表

create table major_tian(

id number primary key, name char(20) not null );

insert into major_tian values(1,'Java'); insert into major_tian values(2,'Oracle'); insert into major_tian values(3,'C++'); insert into major_tian values(4,'android'); commit;

select * from major_tian;

create table stu_tian( id number(4),

name char(10) not null, mid number(2) );

alter table stu_tian add constraint stu_tian_id_pk primary key (id);

alter table stu_tian add constraint stu_tian_mid_fk foreign key (mid) references major_tian;

select constraint_name,constraint_type from user_constraints where table_name = 'STU_TIAN';

insert into stu_tian values(1001,'甲亢',1); insert into stu_tian values(1002,'乙亢',2);

----报错 ORA-02291 integrity constraint (OPENLAB.STU_TIAN_MID_FK) violated - parent key not found 父键找不到 insert into stu_tian values(1003,'抗体',10);

update stu_tian

set mid = 8 where mid = 1;

----报错 ORA-02291 integrity constraint (OPENLAB.STU_TIAN_MID_FK) violated - child key record found 子表中已有引用 delete major_tian where id=1;

------------------???????????????? 约束条件 ?????????????????---------------------- 主键 pk = not + null + unique 外键 fk :表间的一对多关系 非空 not null 唯一 unique 检查 check

-------------------- 联合主键: ???两者联合起来没有重复??----------------------------------?

? last_name first_name

张 三 张 三丰 Smith john

smith tom

create table student_tian( first_name char(10), last_name char(10), score number );

alter table stu_tian add

constraint stu_ln_fn_pk primary key (last_name,first_name); alter table stu_tian add

constraint stu_age_ck check ( age > 17 );

????------????? check 实际用的比较少，因为正常情况sql语句在程序中运行，check功能可以通过程序实现

insert into stu_tian values(1,'a',2); insert into stu_tian values(2,'b',2); insert into stu_tian values(3,'c',1);

alter table stu_tian drop

constraint stu_tian_mid_fk; alter table stu_tian add

constraint stu_tian_mid_fk foreign key (mid) references major_tian

on delete set null;----------将参照了被删除的键值的键值设置为null

---??删除主表id = 2的记录，成功，把子表中所有专业2的学生mid列设置为空

-----------??????????????????????????cascade : 级联,株连?????????????????? alter table stu_tian drop

constraint stu_tian_mid_fk; alter table stu_tian add

constraint stu_tian_mid_fk foreign key (mid) references major_tian on delete cascade;

---??删除主表id = 2的记录，成功，把子表中所有专业2的学生删除 ---???????????不复制约束条件，只复制表结构和数据

----------------------------------------???赋值结构 --------------------------------------------- create table stu_tian1 as

select * from stu_tian where 1 = 0 ;

----------------------------------?????给新表增加约束条件

----------------------------------? insert into stu_tian1 (select*from stu_tian where mid = 2);

建立约束条件的几种语法： 1】.建表时，列级 --???约束条件自定义 create table student_tian(

id number(4) primary key, name char(10) not null, email char(20) unique );

2】建表时，表级

create table student_tian( id number(4),

name char(10) not null, email char(20)

constraint student_id_pk primary key (id)

constraint student_email_nu unique (email) );

3】建表以后

create table student_tian( id number(4),

name char(10) not null, email char(20) );

alter table stu_tian add

constraint student_id_pk primary key (id); alter table stu_tian add

constraint student_email_nu not null (email);

===================????????????????????????????========================

------------------------------------------------脚本文件格式---------------------------------------------------- 1】删除外键约束 2】刪表 3】建表

4】添加约束 5】添加数据 commit;

===================????????????????????????????=============

===========

二.数据库的其他对象 index 索引 table 表 view 视图 sequence 序列

PL/SQL程序块: trigger 触发器 package body 包体 procedure 过程 package 包头 function 函数

synonym: 同义词 database link

user_tables user_objects

select distinct object_type from user_objects;

1】.视图 view

----???sql(select) 语句查询结果的映像

create view v_emp_tian as

select deptno,count(*) num from emp_tian where deptno is not null group by deptno order by deptno;

select*from v_emp_tian;

create or replace view v_emp_tian as

select empno,ename,deptno,job from emp_tian; --------------??视图?????????可用于隐藏数据，简化查询 ------------------------------------ --------------??视图中不包含数据，只是基表的映像 ------------------------------------------- create or replace view xxx--视图名称 as

sql语句

drop view v_emp_tian; select*from v_emp_tian;

--??创建视图，内容是每个部门的编码，名字，位置和这个部门的员工人数

=============================方法一================================== 匿名表/视图

create view v_dept_count as

select d.deptno,d.dname,d.loaction,e.count

from dept_tian d join ( select deptno,count(*) count from emp_tian group by deptno ) e on e.deptno = d.deptno;

select*from v_dept_count;

============================方法二=================================== create or replace view v_dept_count as

select d.deptno,d.dname,d.loaction,count(e.ename) count from dept_tian d join emp_tian e on e.deptno = d.deptno

group by d.deptno,d.dname,d.loaction;

select*from v_dept_count; 2】索引 Index

---??全表扫描 Full Table Scan (FTS) 遍历 ---???? 类比键值对 ???? 名字 位置

JAVA编程思想 三层211号架

1) 如果在某个列建立PK约束条件,索引自动建立 drop table stu_tian; create table stu_tian( id number(4) primary key, name char(10) );

---?索引自动创建 查询索引名称

select index_name from user_indexes where table_name = 'STU_TIAN';

insert into stu_tian values(1,'peter'); insert into stu_tian values(2,'tomas');

????? 建立如下索引中有如下结构的数据： ????? 1 某个地址

????? 2 某个地址

------------------如果按照id查找，自动使用索引--------------------------------------------- select*from stu_tian where id = 2;

-----------------如果按照name查找，不会自动使用索引---------------------------------------- select*from stu_tian where name = 'tomas';

-----查找主键约束的名字

select constraint_name from user_constraints where table_name = 'STU_TIAN'; ---?手动建立索引

---在经常做查询的列上手动创建缩影 create index idx_stu_t_name on stu_tian(name);

索引对查询有帮助，对DML操作是阻碍作用 索引由Oracle Server 自动维护

批量插入数据之前先删除索引，插完再重新建索引 drop index idx_stu_t_name; 3】Sequence 序列

create sequence seq_tian; ----银行排号机 要号： select seq_tian.nextval from dual;

insert into stu_tian

values(seq_tian.nextval,'Lilei'); select *from stu_tian;

drop sequence seq_tian;

create sequence seq_tian start with 1000 increment by 10;

-----????每次值增加1

select seq_tian.currval from dual; --------------------------查询当前

----------------------------------------Mysql主键自增长---------------------------------------- create table student_tian

(id int primary key auto_increment,--主键自增长 name char(10) );

insert into student_ning(name) values('peter');

id name --------------- 1 peter

本文来源：https://www.bwwdw.com/article/do0o.html