Oracle-增删改查

增删改

处理日期

oracle 处理date 类型 必须使用 to_date 函数或 sysdate

?oracle ? 与  mysql 处理 date 的区别
?    
?    mysql 中的 date 类型 只 支持 年月日, 使用  '2000-10-01' 
?    oracle 中  date 类型  包含 年月日时分秒, ? 使用 to_date 函数
?     ? ? ? ? ? ? ? ? ? ? 
? ? ? ? ? ? ? ? ?  to_date('1999-10-15','yyyy-MM-dd')
??
? oracle 获取当前系统日期 用 sysdate
? mysql  获取当前系统日期 用  now()

?语法:
?insert into 表名 (列1,列2,.....)
?    values (值1,值2,....);
??
??
??
?注意事项:
?     ? a. ? 列的格式 必须与 值的 个数 相等
?     ? ?b. ? 值 必须 与 列定义的 数据类型 一致
?             ? ? ?数字 直接写,
?                        字符 ?用 单引号括起来
?                        日期 ?用 to_date() 函数 或 sysdate
?    
? ? ? ?c. ?oracle 中 自增 与 mysql 不同 ,
?             ? ?oracle 中 使用 序列 生成 自增 
?            
?     ? d. ?语法中的 列 可以不写, 但是 推荐 还是写       

-- 批量 插入: (从已有的表 中将 部分信息插入到 当前表) insert select

-- 语法: -- insert into 表名1(列1,列2) select 列21,列22 from 表名2

insert into emp1(ename,borndate,depid)

select ename,borndate,depid from emp;

?--批量 插入: ?(从已有的表 中将  部分信息插入到 当前表) ? insert ?select
??
?--  result : ? ? stuno,subno,examtime
??
?create table abc(
?  stuno VARCHAR2(50),
?    subno number,
?    examtime date
?)
??
??
??
?insert into abc(stuno,subno,examtime)
?select stuno,subno,examtime from result;
??
?-- 
?select * from  abc;
??
?-- 删除 abc 
??
?drop table abc;
??

union

dual 确实是一张表.是一张只有一个字段,一行记录的表.(Oracle提供的最小的工作表，只有一行一列，具有某些特殊功用)。

习惯上,我们称之为'伪表'.因为他不存储主题数据.

?insert into subject
?SELECT 3，'c语言'，1 FROM DUAL union
?SELECT 4，'go语言'，1 FROM DUAL 

?--  修改 ---------(带条件)
?    
?    -- update 表名  set 列名=值,列名2=值2 where 条件  [and 条件2  ]
?        
?    -- 改全部表 数据 ? ,  将  emp 的 sex 都改为 0
??
? ? -- 语法 :  update 表名  set 列名=新值; ?
?     
?     select * from emp;
?     
?     update emp set sex='0';
?    
?    
?    -- 带1条件的修改:  update 表名  set 列名=新值 where  条件;
?    
?    -- 将 员工编号为 1002 的员工 的性别 改为 1, ? ( 员工编号,  性别 -->  emp)
?     ? 
?    update emp set sex='1' where eno=1002;
?        
?    -- 将 员工编号为 1003 的 性别改为1 ,名字 改为 李四2;
?    
?    -- 修改 多列
? -- 语法:  update 表名  set 列名=值,列名2=值2,列名3=值3  where 条件;   
?    
?    
?    update  emp set sex='1' , ename='李四2' where eno=1003
?    
?    -- 将员工编号为 1006 且 性别为 0 的员工的 名字改为 lili, 出生日期为 2000-12-12
?    
?    update emp set ename='lii' , borndate=to_date('2000-12-12','yyyy-mm-dd') 
?    
?    where eno=1006 ?and sex='0' ;
?    
?    -- ? sql  中  且,并且 and ? ? 或,或者 or
?    
?    
?    --
??
?    -- 删除 ? ? (带条件--- 删除表的数据)
?         
?    -- ? 在 主从关系中, 先删 从表 再删主表
?    
?    --  delete from 表名 ; ?  -- 清空表,无数据 ,表结构依然在,
?    
?    -- delete from 表名 where 条件 [and 条件2]; ? --- 将符合条件的数据 删除
?    
?        delete from  emp ;
?        
?        
?    delete from  dept where depid=10;
?    
?    select * from emp;
?    
?        select * from dept;
?        
?        
?--  删除表
??
?--语法: ?drop table 表名; ? ? ?-- 清空表,表里数据会清空,同时 表结构也会删除
? 
? drop table emp;
? 
?        -- ? insert ?  delete ?  update ? ? select 
?    
?    -- 查询 ? ?
?    
?    -- 语法:  select * from  表名 ?  ; --- * 代表查询 全部列 
?    
?        select * from dept;
?    
?    
?    --语法: ? select 列名1 from 表名; --- 查询部分列
?    
?    select ename,sex from emp;
?    
?    -- 带条件查询
?    
?    select depname from  dept where depid =3;
?    
?    
?    
?    -- 查询 部门编号为2 的 员工信息
?    
?     ? select * from  emp where depid=2
?    
?    
?    -- 查询部门编号为2的员工姓名及性别
?    
?     ? select ename,sex from  emp where depid=2
?    
?    -- 模糊查询 ? like 
?    
? ?-- 通配符: ? ? %  匹配任意字符 ? ? _  下划线 只匹配单个字符
?    --
?    --  
?    -- 查询 姓王的员工信息 ,  王X ? 王XX  王XXX 王XXX
?    
?    
?     ?select ?* ?from  emp ?where ename ?like '王%';
?    
?    
?    
?    -- 查询 姓名包含 四 字的员工信息,  四XX ? X四 ? XX四
?    
?    select ?* ?from  emp ?where ename ?like '%四%';
?    
?    
?    -- 查询 姓李 并且 名字为 2个字的 学生信息  李X ? ,李XX  就不对
?    
?        -- 
?    
?    select ?* ?from  emp ?where ename ?like '李_';
?    
?    -- 查询 姓李 并且 名字为 3个字的 学生信息  李X 不对  ,李XX ?
?    
?    
?        select ?* ?from  emp ?where ename ?like '李__';
?    
?    
?    
?    -- 查询 员工编号为 1001 或 1002 或 1003 的员工姓名及性别
?    select  ename ?,sex
?    from  emp
?    where eno=1001 or eno = 1002 or eno=1003
?    
?    -- ? in ? ? not in 
?    
?    select  ename ?,sex,eno
?    from  emp
?    where eno in (1001,1002,1003)
?    
?    
?    
?        
?    select  ename ?,sex,eno
?    from  emp
?    where eno not ?in (1001,1002,1003)
?    
??
??
? -- 排序查询 ? ? ? 由小到大 叫  升序,  由大到小 叫 降序 
? 
? --  order by  列名 ?  desc (降序) asc (升序-- 默认的)
? 
? -- 语法: select * from 表名 where 条件 order by 列名 desc [asc]
? 
??
??
? -- 查询 部门编号为2 的员工信息, 并按照 员工编号进行 降序排列
? 
? select *
? from  emp
? where depid=2
? order by eno desc ;
? 
? 
? 
? -- 查询 部门编号为2 的员工信息, 并按照 出生日期进行 降序排列
? select *
? from  emp
? where depid=2
? order by borndate desc ;
??
??
??
? select *
? from  emp
? where depid=2
? order by eno asc ?;
??
??
??
? -- 去掉重复的数据 ? distinct
? 
? --
? select depid from  emp ;
??
? select ?distinct depid from  emp ;
??
?-- 查询 员工编号在 1001 与 1010 之间的员工信息 ?  员工编号 >=1001 且 员工编号 <= 1010
??
? -- 方法1:
? ?select * from emp where eno >=1001 and eno <= 1010;
??
? -- 方法2: between  and 
? ? ?select * from emp where eno between 1001 and 1010;
?

? ?--select * from emp where eno between 1010 and 1001; ?-- 无语法错误,但是 无匹配数据 无意义
?

?  语法: ? insert into 表名(列名1,列2, 列3,.....)
?                 select  值1,值2,值3,.... ?from dual ?union
?                 select  值1,值2,值3,.... ?from dual ? union
? ? ? ? ? select  值1,值2,值3,.... ?from dual ? union
?                 select  值1,值2,值3,.... ?from dual ? union
?                 select  值1,值2,值3,.... ?from dual ? 
??
??
??
?dual ? 表 是 oracle 特有的,  单行单列

?语法:
? ? ?update :
?         ? ? ?update 表名 set 列=新值,列2=新值2,列3=新值3 
?             ? ?[ where ? 子句        ]
?         ? ? ?
?         ? 如果update 时 没有 where 子句,则表里所有的数据都会被更新
?        
?        delete:
? ? ? ? ?
? ? ? ? ?delete from 表名 [where 子句];
?                
?                如果在删除时 没有where 子句 ,则 delete from 会清除表里的数据

查询

基础查询

?查询全部列 : ?select * from  表名;
?查询 部分列: ? select 列名1,列名2,..... ?from  表名;
?  列的重命名 需要使用 as, 也可不写
? ?select stuname as 姓名,phone 电话 from student;

条件查询

where 条件字句：检索数据中符合条件的值

?带条件的查询 ? SELECT * from ?where 子句;
?         ? ? ? ? ? ? ?  如果 where 有多个条件, 则 并且,且 用 and , 或,或者用 ?or        

??
?-- 查询 student 的全部数据
? select * from student;
? 
? -- 查询 所有学生的 姓名与电话
? select stuname,phone from student;
?-- 列 重命名
? select stuname as 姓名,phone 电话 from student;
??
??
?-- 查询学号为 1001 的学生姓名与邮箱
??
? select stuname,email from student where stuno='1001';
??
??
?-- 查询 性别为 女 (1)  的学生姓名 与出生日期
?select stuname,borndate from student where sex=1;
??
??
?-- 查询 成绩在  80-90之间,参加 科目编号为1的 学生的学号
?select stuno,score
?from result
?where score>=80 and score<=90 and subno=1
??

模糊查询

? like ? ? ? % ? ? 任意字符
?     ? ? ? ? ?  _ ? ?  任意一个字符

??

聚合函数

聚合函数查询: 5个 , 只有一个结果 count(*) 统计个数 max(列名) 对这列求 最大值 min(列名) 对这列求最小值 sum(列名) 对这列求 求和 avg(列名) 对这列求 平均值

count(*) 与 count(列名) 与 count(1) 的区别

count() 统计所有的数据的个数 包括 空行 null count(列名) 如果这个列名的值为null 则 不会进行统计 count(1) 与 count() 等同

分组查询

分组查询

?     select 聚合函数,被分组的列名
?     from  表名
?     [where 子句]
?     group by  列名
?     [having 子句]

?-- 模糊 与 聚合的 练习
??
?-- 统计 名字中带有  丽 字的学生 个数
??
?select ?count(*) 学生个数
?from  student
?where stuname ?like '%五%';
??
??
?select * from student;
??
??
?-- 求 科目编号为 1 的最高成绩
?select max(score) 最高成绩, min(score) 最低成绩
?from ? result
?where  subno=1
??
??
?select * from result;
??
??
?-- 求 参加了 科目编号为1 的考试的 总成绩与平均分
??
??
?select sum(score) 总分, avg(score) 平均分
?from ? result
?where  subno=1
??
??
??
??
?/*
?  1.统计一下年级编号为 2 的 年级一共开设多少门课
?    2.统计一下 姓张的同学的个数
?    3. 求 科目编号为 3 的 最高分
?    
?    4.将 学号为 1001 的学生的 姓名 改为 王丽丽, 邮箱为 uy@qq.com
?    
?    5. 删除 学号为 1001的 科目编号为1 的成绩
?    
?    6. 统计 年级编号为1 的学生个数
?    
??
?*/
??
??
?select * from  student;
??
?-- 分组查询
?select count(*) 女生个数 from  student ?where sex=1;
??
?select count(*) 男生个数 from  student ?where sex=0;
??
?--统计 男女个数
?select count(*), sex
?from ? student
?group by sex
??
??
??
?-- 统计 年级编号为 1 的 男女个数
? ? -- ? 先 筛选 年级编号为1 的 , 然后在分组 , 因此  用 where 
?     -- ?  where 是在分组前 进行数据的筛选
??
?select count(*), sex
?from ? student
?where  gid=1
?group by sex
?-- 分组练习
??
?--  统计 各个年级的学生个数
??
?-- 统计 科目编号为1 ,成绩高于 90分的学生个数
??
?-- 统计 参加 各个科目的考试人数
??
?-- 统计 成绩高于80分的, 各个科目的考试人数
??
??

连接查询

?

? 内连接: ? inner  join
??
?外连接 ?  left join /right  join
??
?内连接:  tableA        inner  join  tableB on 外键
?         ? ? ? tableA  与 tableB  在 内连接 地位 是 等价的, 谁在前谁在后  不影响 执行结果
??
?where  写法
?    
?    外连接: ? ? tableA  left join tableB  , tableA为主表,tableA里的全部数据都要显示,如果 匹配tableB 没有数据则以null填充
?     ? ? ? ? ?  tableA  right join tableB  , tableB为主表,tableB里的全部数据都要显示,如果 匹配tableA 没有数据则以null填充

子查询

? -- 查询 一年级 的学生信息
? -- inner join
? select stuname,phone,stuno
? from  student s ?inner join grade g on s.gid=g.id
? where  name='一年级';
? 
? 
? 
? --where 写法 
? 
? ?select stuname,phone,stuno
? from  student s ?, grade g ?
? where  name='一年级' and s.gid=g.id
? 
? -- 查询 一年级 的学生信息
? -- 子查询 
? select stuname,phone,stuno
? from student s 
? where gid in (select id from grade where name='一年级')

排序

? ? ? ? ? select 聚合函数,被分组的列名
?         from  表名
?         [where 子句]
?         group by  列名
?         having 子句
?         order by 列名[desc] 

其他

自增与序列

? 在oracle 有一张特殊的表 dual
??
?dual 是oracle 中一个伪表, 利用这张表 可以查看序列(自增)
?查看 一个表达式的值,执行函数
?dual 最经常用来 选择系统变量或查看一个表达式的值
??

?/* 语法: create sequence 序列名(自己起) 
? ? ? ? ? minvalue ? -- 最小值
?         maxvalue ? --最大值
?         start with  -- 初始值 ,默认1
? ? ? ? ? increment by  -- 步长  ,增长量
?         nocycle ?  -- 不循环 ? , cycle  -- 循环
?                 ? ? ? ? ?  -- 如果不循环,当达到最大值时,继续产生会发生错误
?                                        --如果循环,当递增序列达到最大值时,循环到最小值,
?                                        --如果循环,当递减序列达到最小值时,循环到最大值
?        cache ?  -- 存放序列的内存块的大小,默认值为20  , nocache  不缓存
?*/
??
?-- 1.创建 一个自增序列
? create sequence seq_dept_deptno
? start with ?1
? increment by 1
? nocycle
? nocache
??
??
?-- 2. 使用 序列名.nextval 获得 值
?insert into dept(depid,depname)
??
?values(seq_dept_deptno.nextval,'其他部12');
??
??
??
??

分页

?-- ? 分页查询, 规定 ,每页显示2条 记录 , ?  ROWNUM
?/*
?select * 
??
?from (
? ? ?  select A.*,ROWNUM rn
? ? ?  from (select * from 分页的表的名字) A
?          where ROWNUM<= 当前页*每页显示的条数
? ? ?  ) 
?where rn>(当前页-1)*每页显示的条数;
??
?*/
? ?-- ? 第1页,每页显示 2条
?select * 
?from (
? ? ? ?select A.*,ROWNUM rn
? ? ? ?from (select * from emp) A
?         ?where ROWNUM<= 2
? ? ? ?) 
??
?where rn>0;
??
?-- 第二页 ,每页显示2条
??
?select * 
?from (
? ? ? ?select A.*,ROWNUM rn
? ? ? ?from (select * from emp) A
?         ?where ROWNUM<= 4
? ? ? ?) 
??
?where rn>2;
??
??