DML总结

--数据更新：insert、update、delete

--insert数据插入

/*
insert into 表名（字段1，···，字段n） values(,···,);
*/


insert into grade values(1,'一班');


insert into student(sid,sname) values(6,6);


/*
       to_date();把字符串转换成Date类型进行存储
       （‘符合日期格式的字符串’，‘yyyy-mm-dd hh:mi:ss’）
*/
insert into student(sid,sname,sdate)
       values(7,7,to_date('2017-01-01 08:08:08','yyyy-mm-dd hh:mi:ss'));
insert into student(sid,sname,sdate)
       values(7,7,to_date('2017-01-01 14:08:08','yyyy-mm-dd hh24:mi:ss'));


/*
to_timestamp(符合日期格式的字符串’，‘yyyy-mm-dd hh24:mi:ss.ff3’)
*/


insert into student(sid,sname,sborn)
       values(8,8,to_timestamp('2017-01-01 14:08:08.233','yyyy-mm-dd hh24:mi:ss.ff3'));


/*

       数据修改

       update 表名 set 字段1=新值，字段n=新值 where 条件
*/


--修改学生姓名,年龄，入学日期
update student set sname=18，sage=30,
       sdate=to_date('2017-01-15 18:08:08','yyyy-mm-dd hh24:mi:ss')where sid=6;
       
/*

     数据删除

     应该做备份
     delete from 表名 where 条件
*/
delete from student where sid=7;


/*

       查询所有学生信息

*/
select * from student;
select sage from student where sid = 6;
--distinct 去除重复的行
select distinct sname from student;


select * from student where sage>15 and sdate>to_date('2017-02-15 12:00:08','yyyy-mm-dd hh24:mi:ss');


/*

asc:默认值，升序
desc:降序

*/
select * from student order by sid desc;


select * from student order by sdate desc; 

--利用学生表创建学生备份表

create table student_back as select * from student;
select * from student_back;

--使用列别名

select sborn as 出生日期 from student;

--如何使用多列的内容在一个单元格中显示

--使用连接符||
select sid||sname||sdate from student;

--取值范围：between

--查询年龄在15-20之间的学生
select * from student where sage>=15 and sage<=20;

--等价于
select * from student where sage between 15 and 20;

--集合成员测试：in

--查询学号为6或9的学生信息
select * from student where sid=6 or sid=9;
--等价于
select * from student where sid in(6,7,8,9);


/*

模糊查询 :like
%:匹配任意长度，任意字符组成的字符串

*/
select * from grade where gname like '%班';  --班%


--查询名字中含有‘四’的班级信息
select * from grade where gname like '%四%';
--查询名字中含有%的名字信息lpp
/*

escape:用来定义转义符，转义符通常使用‘\’或者其他字符来表示，
其次还需要将定义的转义符置于被转义符的前面，此时，被转义符被当作普通字符串来执行

*/
select * from grade where gname like '%\%%' escape '\';

--空值判断：is null

select * from student where sname is null;


/*
oracle 中的函数主要划分为
1、自定义函数
2、内置函数：
       a、单行函数（每次针对一行来操作）
       b、聚合函数
       

       单行函数可以划分为：

              日期函数：
                      to_date('日期格式字符串'，‘格式’);
                      to_timestamp('日期格式字符串'，‘格式’);
                      add_months(日期，月份);给指定的日期添加月份
                      last_day(日期字段)：获取每月最后一天


*/
--插入6的学生信息，在入学日期中获取系统日期并之后两个月
insert into student (sid,sdate) values(7,add_months(sysdate,2));
--查询每月最后一天入学的学生
select last_day(sysdate) from dual;  --当前月份的最后一天
select * from student where sdate=last_day(sdate);


--查询每个月第一天入学的学生
select * from student where sdate=（last_day(add_months(sdate,-1))+1）;
select * from student where sdate=（last_day(sdate-1)+1）;
select * from student where substr(to_char(sdate,'yyyy-mm-dd'),9,2) = '01';


select last_day(add_months(sysdate,-1))+1 from dual;  --当前月的第一天




/*
       数学函数
       abc(字段)：求绝对值
       round（字段）：四舍五入
       mod(字段，除数)：取模函数
       to_number();将符合字符串格式的数字转换为数字类型
       
*/
--查询年龄取模操作
select mod(sage,5) from student;


insert into student(sid) values(to_number('12'));


/*
       字符串函数
       lower():转换小写
       upper():转大写
       substr(字段，1，长度);截取字符串
       
*/
select lower(sname) from student;
--查询所有学生的“姓”；
select substr(sname,1,1) from student;


/*

聚合函数： 针对一组行返回单个结果

avg(字段)：求平均
max(字段)：
min(字段)：
sum(字段)：
count(字段)：统计字段值不为空的记录数
*/
--查询学生表的平均年龄(对不为空的字段使用)
select avg(sage) from student;


--查询学生表中年龄最大的学生信息
select max(sage) from student;


select * from student where sage = (  --嵌套子查询
       select max(sage) from student
);




--给学生表添加成绩字段，然后统计学生表中的成绩总和；
select sum(sal) from student;


--统计学生表中的总记录数（5）
select count(sid) from student;

select count(*) from student;

/*

  分组查询：按照属性（字段）将表中的数据划分为不同的数据单元
  语法：
      select 字段, 聚合函数（字段） from 表名
      [where 条件]  group by 分组依据 [having 条件] 
      先按照where筛选再分组筛选，最后用having筛选

  语法：
      select 字段, 聚合函数（字段） from 表名
      [where 条件]  group by 分组依据 [having 条件] 
      先按照where筛选再分组筛选，最后用having筛选
*/


--查询员工表中的每个部门的工资总额
select deptno, sum(sal) from emp group by deptno;


--查询员工表中每个部门的工资最高的工资
select deptno,max(sal) from emp group by deptno;


--查询员工表中入职日期大于1981-2-20 每个部门的工资总额
select deptno, sum(sal) 

from emp 

where hiredate > to_date('1981-2-20','yyyy-mm-dd') group by deptno;




--查询员工表中每个部门的工资总额>9000的工资总额
select sum(sal) from emp group by deptno having sum(sal)>9000;


/*

   子查询：嵌入在其他查询语句中的select语句
   子查询划分为：嵌套子查询和相关子查询
   嵌套子查询（不相关子查询）：不依赖于父查询
   执行过程：
       （1）先执行子查询语句，其结果不会被显示，而是把查询结果集作为外部查询（父查询）的条件使用
       （2）父查询在根据子查询的结果进行查询，并显示查询结果集

   子查询划分为：嵌套子查询和相关子查询
   嵌套子查询（不相关子查询）：不依赖于父查询
   执行过程：
       （1）先执行子查询语句，其结果不会被显示，而是把查询结果集作为外部查询（父查询）的条件使用
       （2）父查询在根据子查询的结果进行查询，并显示查询结果集
*/


--emp表中，查询与“SCOTT”在同一部门的雇员信息
select * from emp where deptno=(
       select deptno from emp where ename='SCOTT');




--查询比“SCOTT”工资高的雇员信息
select * from emp where sal >(select sal from emp where ename='SCOTT');


--查询员工表中每个部门的工资最高的工资的员工信息
select * from emp where (sal,deptno) in(
       select max(sal),deptno from emp  group by deptno); 
       s
/*

   相关子查询:通常情况下是指子查询的条件中引用了父查询的列值
   执行过程：
       （1）外部查询取出第一条记录（元组），将元组的列值给了内层查询
       （2）内层查询根据该列值进行条件查询，然后得到结果集
       （3）外层查询再将取出的第一条记录根据内层查询的结果进行匹配，匹配成功则显示该记录，不成功则不显示数据
       （4）外层查询再取出第二条记录重复123步骤直到元组全部被提取完毕

   执行过程：
       （1）外部查询取出第一条记录（元组），将元组的列值给了内层查询
       （2）内层查询根据该列值进行条件查询，然后得到结果集
       （3）外层查询再将取出的第一条记录根据内层查询的结果进行匹配，匹配成功则显示该记录，不成功则不显示数据
       （4）外层查询再取出第二条记录重复123步骤直到元组全部被提取完毕
*/


--EMP表中，找出每个雇员超过他所在部门平均工资的雇员编号、雇员名称、薪水、部门编号
select empno,ename,sal,deptno
from emp e1
where sal > (select avg(sal)
             from emp e2
             where e1.deptno=e2.deptno );
             
--查询员工表中每个部门的工资最高的工资
select  *  

from emp e1
where sal = (

select max(sal)
                from emp e2
             where e1.deptno=e2.deptno );
--每次取出一条员工信息，查询他的工资是否是部门工资最高，是显示，否则不显示


select empno,ename,sal,deptno from emp e1 where sal >(
       select avg(sal) from emp e2 where e1.deptno = e2.deptno
       );
--eg:
select * from emp 
where deptno in ( select distinct deptno from emp order by deptno desc);


select count(distinct deptno) from emp;


--查询其他部门中比30号部门某一雇员薪水少的雇员信息
select * from emp where sal <（
       select max(sal) from emp where deptno=30)
       and deptno <> 30;


select * from emp e1 where sal <（
       select max(sal) from emp e2 where deptno=30 group by deptno having e1.deptno<>e2.deptno);


/*

       在数据中<any 等价于 <max()

*/
select * from emp e1 where sal <any（
      select max(sal) from emp where deptno=30)
       and deptno <> 30;
       
--查询雇员薪水大于3000的部门名称
select dname from dept where deptno in (
       select deptno from emp where sal >3000);
       
/*

   exists(子查询)： 用户检测子查询是否查询记录，如果有则返回值TRUE，没有返回false

   执行过程：
                父查询取出第一条记录，然后将部门编号传递给子查询，子查询根据deptno和sal>3000的
                条件进行查询，只要查询出至少一条记录，则exist返回TRUE，此时父查询将根据where后的
                TRUE和false来显示该记录，为false则不显示，然后父查询再取出下一条记录重复上述过程，直到完、
*/
select dname from dept d where exists(
       select * from emp e where sal>3000 and e.deptno = d.deptno);




--查询比员工编号7654工资高，同时与7788从事相同工作的全部员工信息
select * from emp where sal > (select sal from emp where empno=7654)
and job=(select job from emp where empno=7788);


--查询和姓名中含有字母‘s’的员工在相同部门的员工工号和姓名
select * from emp where deptno in (select deptno from emp where ename like '%S%');


--查询至少含有一个员工的部门信息
select * from dept where deptno in(
       select deptno from emp where NOT(empno is null));  --条件表达式
       
select * from dept where deptno in(
       select deptno from emp group by deptno having count(deptno)>=1);


/*

   子查询语句可放在from后面

*/


--找出最高的部门平均工资
select max(avgsal)
 from (select deptno, avg(sal) as avgsal from emp group by deptno) ;
 
select max(avg(sal)) from emp group by deptno; --嵌套
 


--找出平均工资最高的部门名称
select dname from dept where deptno=
       (select deptno from (select deptno, avg(sal) as avgsal from emp group by deptno) 
               where avgsal = (select max(avgsal)
                     from (select deptno, avg(sal) as avgsal from emp group by deptno) ));


--select dname from dept d 
--       where exists(select max(avg(sal)) from emp e group by deptno having e.deptno=d.deptno); 
       
/*

   连接查询：内连接、自然连接、外连接
   内连接：等值连接和不等值连接
   语法：
         select * from R1 inner join S s on r.属性=s.属性  （等值连接的语法）
           select * from R1 inner join S s on r.属性<>s.属性   （不等值连接的语法）
         select * from R,S where R.属性=S.属性
   内连接：等值连接和不等值连接

   语法：
         select * from R1 inner join S s on r.属性=s.属性  （等值连接的语法）
           select * from R1 inner join S s on r.属性<>s.属性   （不等值连接的语法）
         select * from R,S where R.属性=S.属性
*/
--查询员工姓名及其对应部门信息（内连接与生成笛卡尔积有关，效率不够高）
select ename,dname，e.deptno

   from emp e inner join dept d 

on  e.deptno=d.deptno;

等价于===>
select  ename,dname，emp.deptno 

from emp,dept 

where emp.deptno=dept.deptno;


/*

   自然连接：属于特殊的等值连接，要求两张表或多张表具有相同的列名
   语法：
        select * from R natural join S

   语法：
        select * from R natural join S
*/
select * from emp e natural join dept; --自动去重复


select ename,dname，deptno from emp e natural join dept;
    
/*

   外连接：左（外）连接和右（外）连接以及全（外）连接
   语法：
   左（外）连接：使用left join 关键字，查询内容为：两张表的数据加上基表的全部数据，在附表中找不到记录显示为空s
   右（外）连接：使用right join 关键字，查询内容为：两张表的数据加上基表的全部数据，在附表中找不到记录显示为空

   语法：
   左（外）连接：使用left join 关键字，查询内容为：两张表的数据加上基表的全部数据，在附表中找不到记录显示为空s
   右（外）连接：使用right join 关键字，查询内容为：两张表的数据加上基表的全部数据，在附表中找不到记录显示为空
*/


--使用外连接：查询部门信息及其对应的员工信息，同时显示没有员工的部门信息
select d.deptno,d.dname,e.ename from dept d left join emp e on d.deptno = e.deptno; 


--使用外连接：查询部门信息及其对应的员工信息，同时显示没有部门的员工信息
select d.deptno,d.dname,e.ename from dept d right join emp e on d.deptno = e.deptno; 


--查询部门信息及其对应的员工信息，并且要求员工入职日期在1981-2-1以后
select d.deptno,d.dname,e.empno,e.ename 

from dept d  inner join emp e on d.deptno = e.deptno

where  e.hiredate>to_date('1981-2-1','yyyy-mm=dd');


--等价于
select d.deptno,d.dname,e.empno,e.ename 

from dept d,emp e 
        where d.deptno = e.deptno and e.hiredate>to_date('1981-2-1','yyyy-mm=dd');


--查询员工表并显示第一条记录
select * from emp where rownum = 1;
/*

   rownum:伪列，常用来限制结果集的查询
  其原理如下：
            当使用select从表中检索数据时，每次从表中提取出一条记录后，
            oracle自动使用rownum对该条元组添加序列，此时rownum=1，如果
            在where语句后使用rownum进行比较，结果为TRUE，则将提取的记录
            显示在结果集，然后提取下一条记录，此时，rownum为2，然后再次
            比较，结果为假，则不显示到结果集。  

  其原理如下：
            当使用select从表中检索数据时，每次从表中提取出一条记录后，
            oracle自动使用rownum对该条元组添加序列，此时rownum=1，如果
            在where语句后使用rownum进行比较，结果为TRUE，则将提取的记录
            显示在结果集，然后提取下一条记录，此时，rownum为2，然后再次
            比较，结果为假，则不显示到结果集。  
*/
select * from(select rownum as rn, e.* from emp e) where rn >= 2 and rn <=5;   --可以做分页


--给出页数，和每页显示的条数，实现oracle的分页语句
select * from (select rownum as rn, e.* from emp e)
        where rn>(页数-1)*条数 and rn <= 页数*条数 ;  --真分页


--SELECT * FROM think_message LIMIT （页数-1）*条数，每页显示的条数;  --MySQL