oracle数据库基础知识总结_oracle数据库常用sql语句

— 数据库存储数据

 

— 市面上主流的数据库有哪些

 

— 甲骨文  oracle   mysql

—  IBM  db2  金融

—  微软  sqlserver

 

–这些是关系型数据库。

 

–NOSQL  不仅仅是sql,典型 mongodb.

 

—————– —————————————-

–数据库的语言有哪些分类

— **************************************

/***

1.数据定义语言(DDL),create,alter,drop

2.数据操纵语言(DML),select,update,insert,delete

3.数据事务语言, commit,savepoint,rollback

事务特性:原子性，一致性，持久性，隔离型，

 

………..后面补充………….

 

在关系型数据库中数据是以table

(二维数据结构，行和列的形式)

来组织数据

 

table 就是表来组织数据，这个table的设计的规范是什么?

 

–三范式  *****************

 

–第一范式:数据库表中的字段应该最小单位，

是不能够分割(本地化)

–第二范式:数据库表中的一个字段

不能由另外一个字段通过计算得出。

 

age 

birthday

 

工资

养老保险费用

 

***/

 

——————————————————-

— 创建表  oracle  列名和列的类型来定义的

–1.数据类型：

–number 数值类型  number(长度),number(10)

–number(长度，小数点位数)   number(7,2)

–2.char类型   char(长度)，定长  char(6) 固定长度为6位

–3.varchar2类型  varchar2(20)

–4.date类型  日期类型

–5.int类型，表示一个正整数

— 大文本类型 blob ,clob

— 路径

 

— 首先我们来创建一张表   命名规范 t_

create   table   t_students

(

      sn  number(7)   primary  key ,–学号 — 主键（唯一不能重复），主键约束

      sname  varchar2(20) , –学生姓名

      spwd   char(6), — 学生密码

      sbirthday  date,–出生年月

      smoney      number(7,2),     –账户金额

      job      varchar2(20), –学生职位

      saddress  varchar2(20),–学生籍贯

      sphone  char(11)   –学生手机号码

 

)

 

–查询表

select   *   from  t_students

 

 

— 插入数据

insert  into   t_students  values(1, “赵蓉”,”12345″,

to_date(“1992-12-03″,”yyyy-MM-dd”),500.65,”学生会委员”,

“盐城”,”13913321089″);

commit;

 

 

insert  into   t_students  values(2, “秦日霞”,”123456″,

to_date(“1992-11-03″,”yyyy-MM-dd”),1500.65,”学生会委员”,

“盐城”,”13913321085″);

commit;

 

 

— 修改表

 

— 表增加一个字段

alter table  t_students  add  ssex  char(4);

 

–表字段类型修改

alter  table t_students  modify  saddress  varchar2(15)

 

–删除表的字段

alter  table t_students   drop  column ssex ;

 

— 删除整个表

drop  table t_students;

 

select  length(s.spwd) 

 from  t_students  s   where  s.sname=”赵蓉”

 

 

 

— 创建表

 

create  table  t_employees

(

    eid   number(6) primary key, — 员工编号  主键

    ename  varchar2(20),  –员工姓名

    epwd  char(5) , — 员工密码

    ebirthday  date, –出生年月

    esalary   number(7,2), –工资

    ejob  varchar2(20), –职位

    esex  char(4) –性别

 

)

 

— oracle序列机制，自增长机制    sequence

create  sequence  seq_employees

start  with 100 –开始编号

increment  by  2  –步长

nocache  –没有缓存，立即生成

 

–查看表结构

desc  t_employees

 

select  *  from  t_employees  for  update

 

 

select  *  from  t_employees  where

 

 

 

–数据操作语言（select ,insert,update,delete）DML

–select 操作 ,不需要commit  70%

–insert,update,delete  都需要commit  30%

 

–查询

select   *   from  t_employees

 

–插入

— 插入数据

insert  into  t_employees  values(seq_employees.nextval,

“胡瓜”,”1234″,to_date(“1992-12-30″,”yyyy-MM-dd”),10000.88,

“职工”,”男”);

 

insert  into  t_employees  values(seq_employees.nextval,

“李欣”,”11111″,to_date(“1992/08/30″,”yyyy/MM/dd”),12000.88,”组长”,”男”);

commit;

 

–更新数据

update  t_employees e set e.epwd=”12345″ ;

commit;

 

update t_employees  e  set  e.epwd=”00000″  where e.ename=”李欣”;

commit;

 

–删除数据

delete  from   t_employees

 

delete  from  t_employees  e  where e.eid=100;

commit;

 

 

 

 

–约束

 

–1.主键约束，唯一不能重复，数据唯一性  primary key

–2.非空约束 这个字段的取值不能为空   not null

–3.默认约束  这个字段取值如果不给值，取默认值  default

–4.检查约束  这个字段取值是一定范围内  check(字段取值范围)

–5.外键约束  一个字段取值从属于另外一个的值

 

drop  table  t_stus

 

create  table  t_stus

(

    sid  number(3)  primary key,

    sname  varchar2(20)   not null

   

 

)

 

insert  into  t_stus   values(seq_stus.nextval,null);

commit;

 

insert  into  t_stus   values(seq_stus.nextval,”李欣”,to_date(“1976-12-30″,”yyyy-MM-dd”));

commit;

 

insert  into  t_stus   values(seq_stus.nextval,”李欣1″,default);

commit;

 

insert  into  t_stus   values(seq_stus.nextval,”李欣1″,default,”男”);

commit;

 

 

select   *  from   t_stus

 

–default  是默认，sysdate是oracle默认时间的关键字

alter  table  t_stus  add  birthday  date  default sysdate;

 

alter  table  t_stus  add   sex  char(4)

 check(sex  in(“男”,”女”));

 

 

 — 创建一张表，树状菜单，自身关联表 一对多

 create  table  t_menu

 (

     fid   number(4)  primary  key, –父id

     fmenuname  varchar2(20)  not null, –父菜单名称

     cmenuuname  varchar2(20) not null,– 子菜单名称

     cid       number(4)   references t_menu(fid)  –子id  外键约束

 

 

 )

 

 select  *  from   t_menu

 

 

 

 

 

 

— oracle序列机制   自增长机制

create  sequence   seq_stus

start  with  1

increment  by 1

nocache;

 

–查询序列的当前值  dual是个系统表

select   seq_stus.currval  from  dual;

 

drop  sequence  seq_stus

 

 

 

 

insert  into  t_stus  values(seq_stus.nextval,”李欣”);

commit;

 

select   *  from   t_stus

 

delete  from  t_stus

 

–mysql自增长    auto_increment 从起始编号为1，每次增加1.

–sqlserver自增长  identity(10,1)

 

 

 

 

— oracle内置函数

 

–to_date(参数1，参数2)，把一个字符串类型的日期转换成date类型，

–参数2，按照什么日期格式

 

–length() 获取结果的长度

 

–to_char()转换成字符串类型

select   to_char(sysdate,”yyyy”)

-to_char(e.ebirthday,”yyyy”)  age

  from   t_employees  e

 

— 拼接字符串concat()

— 输出一段描述”胡瓜的职位是什么”

select concat(concat(e.ename,”职位是:”),e.ejob) 描述

 from  t_employees  e  where e.ename=”胡瓜”;

 

 

— ||连接

select  e.ename||”,职位是”||e.ejob  from   t_employees e

 

–nvl()函数,取值为null,给默认值

select *   from   t_employees e  for  update

 

–增加奖金字段

alter  table  t_employees  add  ecomm  number(5,2)

 

— 算出每个员工的收入

select  e.ename,e.esalary+e.ecomm    from   t_employees  e

select  e.ename,e.esalary+nvl(e.ecomm,0)    from   t_employees  e

 

 

–substr()截取

alter  table  t_employees  add  ephone  char(11) 

–号段

select  substr(e.ephone,0,3) from

 t_employees  e  where  e.ename=”胡瓜”

 

 –to_number()转换成数值的函数

 

 

–聚合函数 count(),max(),min(),sum(),avg()

select   count(*)  from  t_employees

select   max(e.esalary)  from  t_employees  e

 select   min(e.esalary)  from  t_employees  e

 select   avg(e.esalary)  from  t_employees e

–事务？？？？？

–手动事务，自动事务，可以设置

 

 

— 3查询技术  70%

 

— 3.1 条件查询   where关键字

 

— 查询姓名是李欣的所有信息

select   *  from  t_employees  e  where e.ename=”李欣”

 

–查询姓名是李欣，密码是12345的这个用户是不是合法用户

select  count(*) from  t_employees  e  where e.ename=”李欣”  

and e.epwd=”1111″

 

select  *  from  t_employees  e  where  e.esex=”男”

or  e.ename=”李欣”

 

–3.2  比较查询 >,<,>=,<=,!= <>

select  * from  t_employees e  where e.esalary!=5000

 

select  * from  t_employees e  where e.esalary<>5000

 

 

–3.3  模糊查询

 

–查询姓李的人员的信息

select   * from  t_employees  e  where  e.ename  like “李%”

 

–查询姓李的人员的，但是是两个字的人员信息

select   * from  t_employees  e  where  e.ename  like “李__”

 

–3.4  排序

 

–asc  desc

 

select   *  from  t_employees  e  order  by e.esalary asc

select   *  from  t_employees  e  order  by e.esalary desc

 

–3.5 分组

 

–group by  统计就是和聚合函数在一起使用。

 

–统计这个单位的男女 性别数量  分组条件是明确

select   e.esex,count(e.esex)

 from   t_employees  e  group  by e.esex

 

 —

 select  *  from    t_employees  for  update

 — 统计每个年龄员工的数量 select  count(*),   分组条件是计算的

 select  count(*),

 to_char(sysdate,”yyyy”)-to_char(e.ebirthday,”yyyy”) age

 from   t_employees  e  group by

 to_char(sysdate,”yyyy”)-to_char(e.ebirthday,”yyyy”)

 

 –统计每个职位的数量

 select  e.ejob,count(*)

 from   t_employees  e  group  by  e.ejob

 

 –统计学历，统计籍贯

 

 –统计每个年龄数量大于1  分组后带有限制条件

  select  count(*),

 to_char(sysdate,”yyyy”)-to_char(e.ebirthday,”yyyy”) age

 from   t_employees  e  group by

 to_char(sysdate,”yyyy”)-to_char(e.ebirthday,”yyyy”)

 having count(*)>1

 

 –统计每个年龄数量,数量按升序(先分组，后排序)分组带排序

   select  count(*),

 to_char(sysdate,”yyyy”)-to_char(e.ebirthday,”yyyy”) age

 from   t_employees  e  group by

 to_char(sysdate,”yyyy”)-to_char(e.ebirthday,”yyyy”)

  order  by count(*)

 

 

— 子查询  一个查询的结果作为另外一个查询的条件

 — 找出工资最高的人的姓名

 select   *  from   t_employees

 

 select  *  from  t_employees  e where e.esalary=

 (select  max(e.esalary)  from   t_employees e)

 

 

–3.6 in ,not in ,between and  …any  all关键字

 

— =只能等于一个值

select   * from  t_employees  e  where e.esalary=10000.88

— in可以是多个值

select   * from  t_employees  e  where e.esalary

in(10000.88,5000.00)

 

select   * from  t_employees  e  where e.esalary

not  in(10000.88,5000.00)

 

select  * from  t_employees e  where  e.esalary   between  4000

and  20000;

 

select  * from   t_employees e  where   e.esalary >=4000

and e.esalary <=20000

 

–any  >是比最小的大的信息，<是比最大的小信息

select  e.esalary,e.ename  from t_employees e  where e.esalary<any

(select  e.esalary    from t_employees  e where e.ejob=”职工”)

 

–all  >是比最大的大的信息，<是比最小的小信息

select  e.esalary,e.ename  from t_employees e  where e.esalary < all

(select  e.esalary    from t_employees  e where e.ejob=”职工”)

 

select   *  from  t_employees  for  update

 

 

— 查询1991年到1999年出生的员工的信息

 

select   *  from  t_employees

 

select   *  from  t_employees  e 

where  to_number(to_char(e.ebirthday,”yyyy”))

  between 1991 and  1999

 

 

 

 

–****

 

 

insert  into  t_stus   values(seq_stus.nextval,

“李二”,default,”男”)

 

 

select  *  from   t_employees  for  update

 

 

— oracle高级查询技术

 

— 1.子查询

 

— 逻辑，就是从一个条件出发去找关联的条件。

 

— 一个查询的结果作为另一个查询的条件

 

— 找出员工工资最高人的信息

select  *   from   t_employees  e  where e.esalary  in

(select  max(e.esalary)   from  t_employees  e)

 

–找出和李欣职位不同的员工的信息

 

select  *   from  t_employees  e  where e.ejob!=

(select  e.ejob  from  t_employees  e  where e.ename=”李欣”)

 

 

–找出比员工平均工资高的员工的信息

select  *   from  t_employees  e  where e.esalary>

(select  avg(e.esalary)  from   t_employees  e)

 

–找出收入最高的员工的信息

 

select  *  from   t_employees  e where e.esalary+nvl(e.ecomm,0)=

(select   max(e.esalary+nvl(e.ecomm,0))  from   t_employees  e )

 

 

–oracle分页查询 局限性 ，一条sql语句对应一张表，造成SQL冗余

 

–oracle特殊的列，伪列  rownum ,它始终在第一行，不能移动

 

select  rownum, e.*  from  t_employees  e

 

–查询前三条的数据

select rownum, e.*    from  t_employees  e  where rownum<=3

 

select rownum, e.*    from  t_employees  e  where rownum<=2

 

— 查询第2条到第4条之间的数据，查询出是空的

select rownum, e.*    from  t_employees  e  where rownum>=2

and rownum<=4;

 

select rownum, e.*    from  t_employees  e  where rownum

between 2  and  4

 

–rownum始终在第一行，导致你查询出来的是空的。

 

— 查询第2条到第4条之间的数据

–通过临时结果集过渡查询

select  *  from  

(select rownum  rm,e.*  from  t_employees  e  where rownum<=4)  tmp

where  tmp.rm>=2

 

select rownum, e.*    from  t_employees  e

 

–分页查询

 

— 最大的编号:<=第几页*每页条数

–起始编号:>（第几页-1）*每页条数

 

select  *  from  t_employees  e

 

–每页的条数是2条

–第一页数据  第一页，2条  out:结果集

select  *   from  

(select   rownum  rm,e.* from  t_employees  e  where  rownum<=1*2)

tmp  where tmp.rm>(1-1)*2

 

 

–第二页数据

select  *  from  

(select  rownum  rm,e.*   from  t_employees  e  where  rownum<=2*2)

tmp  where tmp.rm>(2-1)*2

–第三页数据

 

select  *  from  

(select  rownum  rm,e.*   from  t_employees  e  where  rownum<=3*2)

tmp  where tmp.rm>(3-1)*2

 

 

— 在实际的项目中的一个

–局限性 ，一条sql语句对应一张表，造成SQL冗余 ，

–比如;100张表需要100个sql吗?

 

 

 

–2.集合查询

 

–集合查询

— 多个结果集的查询

— 工资大于3000的和职位是组长的两个查询结果的合并，过滤重复

— 合并不过滤重复，查询的两个结果合并

select  e.ename,e.esalary   from 

t_employees  e  where e.ejob=”组长”

union all

select e.ename,e.esalary     from  t_employees  e 

where e.esalary>3000

 

–合并过滤重复

select  e.ename,e.esalary   from 

t_employees  e  where e.ejob=”组长”

union

select e.ename,e.esalary     from  t_employees  e 

where e.esalary>3000

 

–交集，两个查询结果集都有的

select  e.ename,e.esalary   from 

t_employees  e  where e.ejob=”组长”

intersect

select e.ename,e.esalary     from  t_employees  e 

where e.esalary>3000

 

–差集

select  e.ename,e.esalary   from 

t_employees  e  where e.ejob=”组长”

minus

select e.ename,e.esalary     from  t_employees  e 

where e.esalary>3000

 

–3.decode()函数查询  分支查询

 

select  *   from 

t_employees

 

–科长工资加1.2倍，组长工资加1.1倍,其它职位保持原有，

–请输出加薪后的所有的员工的

–信息

select  e.ename,e.ejob,e.esalary,

decode(e.ejob,”科长”,e.esalary*1.2,

“组长”,e.esalary*1.1,

esalary)  加薪后的工资

from  t_employees  e

 

— 等同于case..when

select  e.ename,e.ejob,e.esalary,

case e.ejob when “科长” then e.esalary*1.2

            when “组长” then e.esalary*1.1

            else  e.esalary  end

              

加薪后的工资       

from  t_employees  e

 

–4.多表查询(2表查询，第三范式)

 

–之前讲的都是单表查询，进入到多表(>1)查询

 

–第三范式

— 表中的列只能参照一个主键字段

 

— 员工表 员工id，员工姓名，部门名称，部门id, 拆分形成两个实体表

 

–实体表之间的关系，主要有三种:

–1.一对一关系  员工表和角色表

–2.一对多关系   部门表和员工表  商品分类表和商品表

–3.多对多关系    学生和课程表（中间关系表，第三方表，成绩表）

          

–实体表之间的关系图，ER图

 

–这个关系的定义是根据现实的业务来决定的。

select   *  from  t_employees

 

–建立一个部门表  部门和员工表之间的关系是1对多

create  table  t_depts

(

    did  int   primary  key,

    dname  varchar2(20) not null,

    daddress  varchar2(50),

    dphone   char(11)  not null

 

)

 

create  sequence  seq_depts

start with  10

increment  by 2

nocache;

 

 

insert  into  t_depts 

values(seq_depts.nextval,”质量部”,”南京江北新区”,”13913321089″);

insert  into  t_depts 

values(seq_depts.nextval,”技术部”,”南京江宁区”,”13913321086″);

insert  into  t_depts 

values(seq_depts.nextval,”人力资源部”,”南京鼓楼区”,”13913321085″);

commit;

 

 

select  *   from   t_depts  for  update

 

select  *  from  t_employees  for  update

 

— 员工和部门之间是有关系的

 

alter  table t_employees  add  deptid   int;

 

 

–外键约束？？？？？？？？？？？？

alter  table  t_employees  add  constraints  fk_emp_depts

 

 

— 表的设计，软件核心来自于数据

 

— 查询这两张表的数据  笛卡尔积 表1*表2

select  *  from  t_employees,t_depts

 

–等值连接

select   *  from    t_employees  e,t_depts  d

where e.deptid=d.did

 

 

–内连接查询inner  join  

select   *  from    t_employees  e  inner  join  t_depts  d

on e.deptid=d.did

 

— 查询员工李欣的部门名称

select  d.dname,e.ename from  t_employees   e  inner  join 

 t_depts  d

on   e.deptid=d.did  where  e.ename=”李欣”

 

–子查询 的效率比内连接查询要低

select  d.dname  from  t_depts  d where d.did=

(select  e.deptid   from  t_employees   e  where e.ename=”李欣”)

 

–左连接

select  d.dname,e.ename from  t_employees   e  left  join 

 t_depts  d

on   e.deptid=d.did 

 

 

–右连接

select  d.dname,e.ename from  t_employees   e  right  join 

 t_depts  d

on   e.deptid=d.did 

 

 

–全连接

 

select  d.dname,e.ename from  t_employees   e  full  join 

 t_depts  d

on   e.deptid=d.did

 

 

—

select   *  from  t_depts

 

—  统计技术部人员的工资的总和

 

select   sum(e.esalary) 工资总和  from  t_depts  d  inner  join  t_employees e  on d.did

=e.deptid  where d.dname=”技术部”

 

—  统计每个部门的人员的数量

select   count(e.ename) 数量, d.dname from  t_depts  d 

left  join  t_employees e  on d.did=e.deptid 

  group  by  d.dname

=e.deptid

 

–统计每个部门的人员的数量大于1的信息  group  by 限制条件  having

 

select   count(e.ename) 数量, d.dname from  t_depts  d 

left  join  t_employees e  on d.did=e.deptid 

group  by  d.dname

having count(e.ename)>1

  

 –统计每个部门的人员的数量按降序排列

select   count(e.ename) 数量, d.dname from  t_depts  d 

left  join  t_employees e  on d.did=e.deptid 

group  by  d.dname   order by   count(e.ename)  desc

 

 

——————————————————————

 

 

—pl-sql块

–块:一组SQL语句在一起运行，解决复杂的业务逻辑。

— 是不能够被编程语言所调用  java,python,c#

 

–块的基本结构

/**

 declare

 

            —定义的变量

 begin

  

 

             — 一组sql语句

 

 end;

 

 

 

**/

 

 

 

/**

变量v_

变量赋值:=

 

 

**/

 

— 计算两个数值类型的变量的和并输出

declare

   v_num1 number:=100;

   v_num2 number:=10;

   v_sum number;

begin

 

    

   v_sum:=v_num1+v_num2;

  

   dbms_output.put_line(“计算这两个数的和为”||v_sum);

  

end;

 

 

— 异常处理  计算两个数值类型的变量的商并输出

declare

   v_num1 number:=100;

   v_num2 number:=0;

   v_sum number;

begin

 

    

   v_sum:=v_num1/v_num2;

  

   dbms_output.put_line(“计算这两个数的和为”||v_sum);

  

–异常处理块

exception   –捕获异常

    when  others  then

        dbms_output.put_line(“v_num2这个数作为除数不能为零”);

  

end;

 

— 流程控制语句

— 一个数判断是奇数还是偶数,并输出信息

 

declare

v_num  number:=22;

 

begin

 

  — oracle不支持%取模，取模函数mod()

  if   mod(v_num,2)=0  then

  

       dbms_output.put_line(“v_num这个数是偶数”);

 

  else

       dbms_output.put_line(“v_num这个数是奇数”);

  end  if;

 

end;

 

 

–优化

declare

v_num  number:=22;

v_str  varchar2(100);

begin

 

  — oracle不支持%取模，取模函数mod()

  if   mod(v_num,2)=0  then

   v_str:=”v_num这个数是偶数”;

     

 

  else

     v_str:=”v_num这个数是奇数”;

      

  end  if;

 dbms_output.put_line(v_str);

end;

 

— 查询李欣这个员工的性别，如果是男的，奖金加1000，

–如果是女的，奖金加500，

— 输出他现在的收入输出

/**

1.赋值:=  直接给变量赋值

2.从sql语句查询的结果进行赋值。2.1查询出来的是一个值，into

2.2 如果是多个值，不能用into，要用游标遍历

 

**/

 

 

declare

v_esex  t_employees.esex%type;  –这个变量的类型参照表中的字段类型

v_usaraly  t_employees.esalary%type;

v_sum  number(10,2);

begin

 

  –1.查询李欣这个员工的性别

  select  e.esex   into   v_esex

   from  t_employees  e  where e.ename=”李欣”;

  

   –2.判断是男还是女

  

   if   v_esex=”男”   then

    

       v_usaraly:=1000;

  

   else

    

      v_usaraly:=500;

  

   end  if;

  

   –执行更新

   update   t_employees  e  set  e.esalary=e.esalary+v_usaraly

   where e.ename=”李欣”;

  

   commit;

  

  

   — 查询出现在的收入

  select  e.esalary+nvl(e.ecomm,0)  into  v_sum   from  t_employees  e where e.ename=”李欣”;

 

 

  dbms_output.put_line(“李欣这个员工的性别是:”||v_esex||”,他现在的收入为:”||v_sum);

  –select  e.ename from  t_depts  d  inner join  t_employees  e

  — on d.did=e.deptid  where d.dname=”质量部”

 

end;

 

 

— 查询李欣的部门名称,如果是质量部，加1000，如果是技术部加2000，

–如果是人力资源部加500，其它部门加100

 

declare

v_dname  t_depts.dname%type;

v_ecomm   t_employees.ecomm%type;

 

begin

 

 — 李欣的部门名称

 select  d.dname   into   v_dname  from  t_employees e   inner  join  t_depts d

 on e.deptid=d.did  where e.ename=”李欣”;

 

 if v_dname=”质量部” then

  

 v_ecomm:=500;

  

 elsif  v_dname=”技术部” then

 

   v_ecomm:=600;

 elsif v_dname=”人力资源部”  then

 

   v_ecomm:=200;

 else

      v_ecomm:=100;

 

 end   if;

 

 

    update   t_employees  e  set  e.ecomm=e.ecomm+v_ecomm

   where e.ename=”李欣”;

  

   commit;

 

end;

 

 

— 循环结构

 

–1.100之和并判断是奇数还是偶数

—  for  loop

declare

 

v_sum  number:=0;

 

begin

 

for v_i  in 0..100  loop

 

    v_sum:=v_sum+v_i;

   

end  loop;

 

  dbms_output.put_line(“和为”||v_sum);

 

end;

 

–while loop

 

 

declare

v_i  number:=0;

v_sum  number:=0;

 

begin

 

   while v_i<=100  loop

    

      v_sum:=v_sum+v_i;

     

      v_i:=v_i+2; –改变循环变量的值

   end  loop;

 

dbms_output.put_line(“和为”||v_sum);

 

end;

 

 

–循环结构使用

 

create  table   t_users

(

 

   id  int  primary key,

   tname  varchar2(20),

   tsex   char(6)

 

)

 

create  sequence  seq_users

start with  1

increment  by 1

nocache;

 

select  * from  t_users

 

–for循环批量数据

declare

begin

 

for  v_i  in 1..10000  loop

 

   if mod(v_i,2)=0  then

    

      insert  into  t_users  values(seq_users.nextval,”李”||v_i,”男”);

     

   else

       insert  into  t_users  values(seq_users.nextval,”王”||v_i,”女”);

  

   end  if;

 

end  loop;

 

commit;

end;

 

–while循环批量数据

declare

v_i  number:=1;

begin

 

while  v_i<=1000  loop

   insert  into  t_users  values(seq_users.nextval,”李”||v_i,”男”);

 

v_i:=v_i+1;

 

 

end loop;

 

commit;

 

end;

 

—into是只能附一个值

— 如果是多个值，就是游标，就是结果集，分为;cursor(显示游标),隐式游标

 

— 查询员工表的中的数据，并输出每个人的姓名和性别

— while..loop

declare

   –定义一个显示游标

   cursor  v_datas is select  *  from  t_employees;

   — 定义一行

   v_linedatas  t_employees%rowtype;

 

begin

 

   

   –打开这个游标

   open  v_datas;

  

   –遍历游标

   fetch  v_datas  into v_linedatas; –遍历第一行

  

     while  v_datas%found  loop — 如果有数据进入循环体

       

        dbms_output.put_line(v_linedatas.ename||”,性别是:”||v_linedatas.esex);

      

         fetch  v_datas  into v_linedatas;–移动到下一行

     end  loop;

  

  

   — 关闭游标

   close  v_datas;

end;

 

— for..loop

 

declare

   –定义一个显示游标

   cursor  v_datas is select  *  from  t_employees;

 

begin

 

   for v_linedatas in v_datas loop

  

    dbms_output.put_line(v_linedatas.ename||”,性别是:”||v_linedatas.esex);

   end  loop;

  

   — 关闭游标

end;

 

— 隐式游标

 

— 查询质量部员工的性别，是男加1000，是女加5000

 

declare

v_sex  t_employees.esex%type;

v_comm  t_employees.ecomm%type;

 

begin

     

   –质量部的员工的信息

  for  v_datas  in ( select  e.esex ,e.ename  from  t_employees  e   right  join  t_depts  d

   on  e.deptid=d.did  where d.dname=”质量部”)  loop

  

     dbms_output.put_line(v_datas.esex||v_datas.ename);

    

     if v_datas.esex=”男”  then

      

       v_comm:=200;

     else

       v_comm:=50;

     end  if;

    

     –执行更新

     update  t_employees e  set e.ecomm=e.ecomm+v_comm

     where e.ename=v_datas.ename;

     commit;

  

   end  loop;

 

end;

 

 

select   *  from   t_employees

 

 

—存储过程

 

— 存储(是以一个名字来存储)+过程(过程化的语句块)

 

— pl-sql块是不能够被编程语言直接调用,只能运行在数据库端

— 以一个名字命名，这个名字被编程语言call，这样形成交互

 

–预编译，编译一次，下次调用的话不需要再次编译，性能好，

–能够处理复杂的业务逻辑,

–可以有传入和输出参数,缺点:占用存储空间

–sql(dml语言，调用一次编译一次)，性能没有存储过程好

 

–基本结构

create  or  replace  procedure   存储过程的名字

(

 

      –传入和输出参数

 

)

as

 

 

 

begin

 

 

 

 

end;

 

—检查登录

create  or  replace  procedure   p_checklogin

(

 

      v_uname  in   varchar2, –传入参数不需要长度

      v_pwd   in   varchar2,

      v_msg  out   varchar2

)

as

v_count  int;

 

 

begin

 

   –检查登录

   select count(*) into  v_count  from  t_employees  e  where e.ename=v_uname

  

   and   trim(e.epwd)=v_pwd;

  

   

   –进行判断

   if  v_count>0 then

    

 

 

 

    v_msg:=”登录成功”;

   else

    

      v_msg:=”登录失败”;

     

   end  if;

  

   –记录

   insert  into  t_userlog  values(seq_userlog.nextval,v_uname,default,v_msg);

   commit;

  

  

end;

 

— 变更存储过程

 

create table  t_userlog

(

    ulid   int   primary key,

    uname varchar2(20), — 登录的人

    logintime  date  default  sysdate, –登录的时间

    loginresult  varchar2(20)   –登录的结果

 

)

 

create  sequence  seq_userlog

start  with 1

increment  by 1

nocache;

 

 

select  *  from  t_userlog

 

— 直接返回一个结果集(不在存储过程内部遍历)

–**部门的员工的数量和员工的姓名

create  or  replace  procedure  p_queryempDatas

(

 

    v_dname  in  varchar2,  –部门名称

    v_count  out  int,  –部门员工数量

    v_namedatas   out  sys_refcursor  –部门员工姓名

)

as

 

begin

 

  –1.通过部门名称得到部门的员工的数量

  select count(e.ename)  into  v_count   from  t_employees  e

   right  join  t_depts d

  on  e.deptid=d.did  where d.dname=v_dname;

 

 

  –2.员工姓名(不在存储过程内部遍历)

  open  v_namedatas   for select e.ename    from  t_employees  e 

  right  join  t_depts d

  on  e.deptid=d.did  where d.dname=v_dname;

 

 

end;

 

—-**部门的员工的数量和属于这个部门的员工的性别，

–如果是男，工资加250；

–女加100；

 

 

create   or  replace   procedure  p_querydeptupdatesalary

(

   v_dname  in   varchar2,

   v_count  out  int

)

as

v_salary  t_employees.esalary%type;

begin

 

    –1.通过部门名称得到部门的员工的数量

  –select count(e.ename)  into  v_count   from  t_employees  e

   –right  join  t_depts d

  –on  e.deptid=d.did  where d.dname=v_dname;

 

  –调用函数，减少冗余代码

  v_count:=f_querydeptempnum(v_dname);

 

  –2.这个部门的所有员工的信息

 

  for  v_linedatas  in (select  *     from  t_employees  e 

  right  join  t_depts d

  on  e.deptid=d.did  where d.dname=v_dname)  loop

 

  if v_linedatas.esex=”男” then

   

     v_salary:=250;

   

  else

   

      v_salary:=150;

 

  end  if;

 

 

  –执行更新

  update  t_employees  e  set  e.esalary=e.esalary+v_salary

  where  e.ename=v_linedatas.ename;

 

  commit;

 

 

  end  loop;

 

 

end;

 

 

 

select  *  from  t_employees

 

— 自定义函数  特殊的存储过程

— 自定义函数  1.关键字function  2.只能返回一个值

create   or  replace  function  f_querydeptempnum

(

   v_dname  in  varchar2

)

return int –切记这个地方不能加;

as

 

v_count  int;

begin

 

    select  count(e.ename)  into  v_count  from  t_employees e

    right  join  t_depts  d  on  e.deptid=d.did

    where  d.dname=v_dname;

   

   

    return v_count;

 

end;

 

— 查询一个表t_employees的条数  如果系统中的表很多，这样会造成冗余

create   or  replace  function  f_queryempcount

return  int

as

 

v_count  int;

begin

 

    select  count(*)   into  v_count from  t_employees ;

    return  v_count;

 

end;

 

–动态sql

 

— 一个值

create   or  replace  function  f_querytablecount

(

 

   v_tableName   in  varchar2

)

return  int

as

 

v_sql varchar2(1000);

 

v_count  int;

begin

 

   v_sql:=”select   count(*)  from  ” ||v_tableName;

  

  

   — 执行这个动态sql,是一个值

   execute immediate  v_sql  into v_count;

  

   return v_count;

 

end;

 

—多个值

create   or  replace  function  f_querytabledatas

(

 

   v_tableName   in  varchar2

)

return  sys_refcursor

as

 

v_sql varchar2(1000);

 

v_datas  sys_refcursor;

begin

 

   v_sql:=”select   *  from  ” ||v_tableName;

  

  

    — 执行这个动态sql,是一个结果集

    open v_datas  for v_sql;

   

    return v_datas;

   

end;

 

 

–sql语句分页  固定2条

 

select   *  from  t_employees

 

— sql分页，系统大了，会造成SQL冗余

select   * from (select  rownum  rm, 

e.* from  t_employees  e  where  rownum<=4)  tmp

where tmp.rm>2

 

 

–存储过程分页 一个存储过程可以对系统中的表都可以分页

— tablename  in  表名

–pagenum   in   每页几条

–currentnum  in  第几页

–datas  out  每页的结果集

–count  out  总条数

–pagesize  out  总页数

create   or   replace    procedure   p_pagemodel

(

 

    v_tablename  in   varchar2,

    v_pagenum   in    int,

    v_currentnum  in  int,

    v_datas  out  sys_refcursor,

    v_count   out   int,

    v_pagesize  out  int

 

)

as

v_endindex  int:= v_currentnum*v_pagenum;

 

v_startindex  int:=(v_currentnum-1)*v_pagenum;

 

v_sql  varchar2(1000);

begin

 

   –1.构建总的条数动态sql

   v_sql:=”select   count(*)   from     “||v_tablename;

  

   –2.执行动态sql

   execute  immediate  v_sql  into v_count;

  

   –3.获取总页数

   if   mod(v_count,v_pagenum)=0  then

    

     v_pagesize:=v_count/v_pagenum;

    

   else

      –除不尽

     v_pagesize:=floor(v_count/v_pagenum)+1;

    

   end  if;

  

   –获取分页结果集

   v_sql:=”select   *   from  “||”(select  rownum  rm, e.* from  “||v_tablename||

    ”   e  where  rownum<=”||v_endindex||”)   tmp” ||” where tmp.rm>”||v_startindex;

   

   dbms_output.put_line(v_sql);

  

   –执行动态sql，返回的是一个结果集

   open v_datas  for v_sql;

   

end;

 

— 面试的时候，什么是事务

–jdbc 事务  自动事务

–hiernate事务

–mybatis事务

 

–spring事务

 

–python事务  手动事务 

 

—  rollback回滚事务  commit提交事务  savepoint 设置事务保存点

–数据库事务

–事务就是一个工作单元，所谓的工作单元，就是不可分割的一个或多个SQL

— (insert ,update,delete)

 

–秦日霞借钱1000给李欣，并记录日志。

select  *  from  t_employees

 

 

–秦日霞借钱1000给李欣

–记录日志

create  or   replace  procedure   p_operatorsalary

(

 

   v_rname  in  varchar2,

   v_tname  in  varchar2,

   v_money  in  number,

   v_msg   out  varchar2

 

)

as

begin

 

    — 1.先把借钱人的钱扣掉

    update  t_employees  e  set

    e.esalary=e.esalary-v_money  where e.ename=v_rname;

   

    –2.加上给借钱人

     update  t_employees  e  set

    e.esalary=e.esalary+v_money  where e.ename=v_tname;

   

    –设置一个事务保存点  给个名字

   

   

    savepoint  a;

   

    –3.记录日志

    insert  into  t_userlog 

    values(seq_userlog.nextval,v_tname,”1997-12-30″,”借钱”);

   

    commit;

  

exception

 

when  others  then

  rollback to a;  –回滚到事务保存点a，提交

  commit;

  

end;

 

 

select   *   from  t_userlog

 

 

 

 

————-

 

–索引，job(定时任务)，视图，触发器

 

— 索引

— 怎么来提高查询的性能?

 

— 1.索引   2. SQL优化

 

— 索引就是来提高查询的性能。

— 打个比方来说:书的目录 聚集索引 ；

–书的页码:聚集索引

 

— 70W条的数据以上我们才考虑建立索引

 

— 建立一张表

create  table  t_stuinfo

(

 

    suid  number(7)  primary  key,

    sname  varchar2(100),

    sbirthday  date  default  sysdate,

    ssex  char(4)

 

)

 

 

 

–加载80W条数据

 

declare

begin

   for v_i  in 1..1200000 loop

    

       if mod(v_i,2)=0  then

        

      

         insert  into  t_stuinfo  

         values(v_i,”李”||v_i,default,”男”);

      

        

       else

     

  

         insert  into  t_stuinfo  

           values(v_i,”王华”||v_i,default,”女”);

   

       end  if;

   end  loop;

  

   commit;

 

end;

 

delete  from  t_stuinfo

 

select  count(*)  from  t_stuinfo

 

 

select  *  from  t_stuinfo

 

— 白盒测试 查询  王华67001 查这个人的信息

create  or  replace  procedure  p_queryinfo

(

    v_name  in  varchar2

 

)

as

v_begintime  varchar2(1000);

v_endtime   varchar2(1000);

v_birthday t_stuinfo.sbirthday%type;

begin

 

       v_begintime:=to_char(systimestamp,”yyyy-MM-dd hh24:mi:ss.ff”);

 

        select  s.sbirthday   into  v_birthday

        from   t_stuinfo s  where s.sname=v_name;

       

       

       v_endtime:=to_char(systimestamp,”yyyy-MM-dd hh24:mi:ss.ff”);

     

     

      dbms_output.put_line(“开始时间:”||v_begintime);

      dbms_output.put_line(“接受时间:”||v_endtime);

   

end;

 

—

/**

开始时间:2018-03-23 09:29:31.490000000

接受时间:2018-03-23 09:29:31.537000000    –47000000

 

开始时间:2018-03-23 09:32:05.235000000

接受时间:2018-03-23 09:32:05.250000000    –15000000

 

—

开始时间:2018-03-23 09:32:52.942000000

接受时间:2018-03-23 09:32:52.958000000 16000000

 

 

开始时间:2018-03-23 09:33:26.529000000

接受时间:2018-03-23 09:33:26.576000000  47

**/

 

— 建立索引

create  index    snameindex   on   t_stuinfo(sname);

 

drop  index  snameindex;

 

 

call   p_queryinfo(“李966002”);

 

— job定时任务  数据库定时任务

 

–自动化定时任务  对于表的历史数据的一个定时清理

 

— 1-10

 

— t_stuinfo ，每隔1分钟自动插入一条数据

delete  from   t_stuinfo;

commit;

 

create  sequence   seq_stuinfo

start  with 1

increment   by  1

nocache;

 

–建立一个存储过程

create  or  replace  procedure  p_timetaskadddata

as

begin

 

  insert  into  t_stuinfo  

         values(seq_stuinfo.nextval,”李欣”,default,”男”);

    

  commit;

 

end;

 

 

–建立一个任务

variable  job2018  number;

 

— 命令行窗口   提交这个定时任务

SQL> variable  job2018   number;

SQL> begin

  2  dbms_job.submit(:job2018,”p_timetaskadddata;”,

  sysdate,”sysdate+1/1440″);

  3  end;

  4  /

 

 

select   *   from   t_stuinfo

 

 

SQL> begin

  2  dbms_job.remove(:job2018);

  3  end;

  4  /

 

**PL/SQL procedure successfully completed

job2018

———**