<四> DML语句(insert \ delete \update \select)

(1)insert语法

    insert into 表名（列名...） values(值列表...)

    --要注意细节：
     （1）在oracle中，没有自动增长列，后面我们采用序列来实现
     （2）考虑受约束的影响，尤其是default
          insert into student(sno,sname,sex,age) values(1,'张三','男',23);
          insert into student values(2,'李四','男',23,default);
     （3）要考虑类型的因素

    --第一种方式：表已经存在的情况下

     insert into student 
2     select 1,'唐僧','男',34,'东土大唐' from dual union
3     select 2,'孙悟空','男',34,'花果山' from dual union
4     select 3,'八戒','男',34,'高老庄'   from dual union
5     select 4,'沙僧','男',34,'流沙河'   from dual
6 
7     select * from student;
8     select * from student1;
9     drop table student1;

    --第二种方式：表不存在情况下

     create table student1 as select * from student;
2 
3     --复制表结构不复制数据
4     create table student1
5     as
6     select * from student where 1=0;
7 
8     --第三种方式：从其他表中插入数据
9     insert into student1 select * from student;

 (2)delete语句

    delete from 表名 where 条件

    delete from student where sno=4;
   

     delete from student;
2 
3     truncate table student;--注意受外键约束的影响，就算外键表中无数据，都不能执行
4 
5     alter table sc
6     drop constraint FK_SC_STUDENT_SNO;

 （2）比较delete 和 truncate 的区别:

    --(1) truncate语法：truncate table 表名 ;
          --delete语法： delete from 表名  where 条件
    --(2)truncate 相当于不带条件的delete语句。且truncate不能带条件，相当于将表头和表里面的数据一刀两断
    --(3)delete和truncate都会受到外键的影响。但是要注意影响的程度不同，delete是当有数据引用的时候才不能删除
         --就算外键表中无数据，都不能执行

(3)update语句

     update 表名 set 字段名=值,字段名=值 where 条件
2 
3     update student set age = 43 where sno=3;
4 
5     update student set age = 43,address='西天' where sno=3;

 (4)select语句

    select *,字段，表达式，函数（包括聚合函数），别名，distinct,常量
    from   表名，视图， 子查询（结果集）,别名
    where  条件表达式 ，子查询（结果集），单行函数（不能接聚合函数）,like '通配符'
    group by 字段
    having   函数，表达式
    order by 字段，表达式

    --聚合函数：max().min().count().sum().avg()

    --给字段取别名：

     --S:select sno as  '学号','姓名'=sname,age '年 龄' from student;
2     select sno as "学号" from student; 
3     select sno "学号" from student; 

    --给表取别名

     select * from student s;

    --在oracle中不使用top关键字

     select top 2 * from student;

    --可以使用distinct

     select distinct(deptno) from emp;
2     select distinct deptno from emp;

    --使用常量

     select sno,sname,age,sex,address,'取西经' as "工作"  from student;
2 
3     select t.*,'取西经' as "工作"  from student t;
4 
5     --order by后面接表达式
6     select * from emp order by sal+comm desc;

 

(5)通配符

    --% 任意多个字符
    --_ 单个字符
    --[] 在某某范围之内
    --[^] 不在某某范围之内

    --where和having之间的区别：
    --(1)where 是对表中记录进行筛选，having是对结果集进行筛选
    --(2)where 不可以接聚合函数，having可以
    --(3)where 不一定要和group by结合使用，having必须结合group by一起使用

 (6)分析函数(排位函数)

    rank()
    row_number()
    dense_rank()

--rank()相同的值排位相同，序号随着跳跃

 select empno,ename,job,mgr,sal,rank() over (order by sal desc) from emp

 

--row_number()相同的值排位不相同，序号不跳跃（连续）

     select empno,ename,job,mgr,sal,row_number() over (order by sal desc)
2     from emp

 

--dense_rank() 相同的值排位相同,序号连续

     select empno,ename,job,mgr,sal,dense_rank() over (order by sal desc) from emp

 

--partition by相当于group by 首先进行分组，在排序，最后排位

     select empno,ename,job,mgr,sal,deptno,dense_rank() over (partition by deptno order by sal desc)
2     from emp

 

--找出工资水平位于整个公司的第3名的员工信息

     select * 
2     from (
3          select empno,ename,job,mgr,sal,dense_rank() over (order by sal desc) as rankNum
4          from emp
5     ) e
6     where e.rankNum = 3;