图片 7

数据库第十三日

例1:通过存储过程查询数据库中的student表
我们知道在sql中,我们查询一个表,可以通过select*fromstudent进行查询,那在存储过程中该怎么写呢?
解: 首先我们打开查询分析器,;
然后我们先来创建一个存储过程以备等会使用,: createprocedureproc_stu as
select*fromstudent go 这样一个存储过程就创建好了,现在我们来执行一下
我们可以在查询分析器中输入:executeproc_stu 便可以看到效果
分析一下上面的例子,proc_stu为存储过程名,select*fromstudent很显然就是SQL语句了,执行的时候我们只需要execute存储过程名,就可以了.其中,procedure和execute可以分别缩写为proc和exec
以上演示了一个很简单的存储过程.下面我们来看一下带参数的存储过程
例2.查询student表中sno(学号)为’s1’的记录 createprocproc_stu
@ssnovarchar(10) as select*fromstudentwheresno=@ssno go
这样一个带参数的存储过程就OK了,"@变量名"是sql中用来表示用户自定义的参数的方法,有些朋友可能还看到过"@@变量名",这个是系统自带的.也就是说这个变量名是系统定义的,不能随便乱改.这样分析后,相信大家应该看得懂了吧.
执行带参数的存储过程,需要使用如下语句:execproc_stus1也可以不写参数,不过这样的话,必须首先在创建存储过程中加参数赋值,可以赋Null,否则,系统会报错.
最后我们来讲一下如何利用存储过程返回一个值: 例3.返回student表中学生数
方法一: createprocproc_stu @numintoutput–注明是外部变量 as
select@num=count(*)fromstudent go 现在我们来执行下:
要使用外部变量,我们先要进行声明:declare
@return我们用这个变量来接收存储过程中的外部变量
executeproc_stu,@num=@returnoutput
这样我们便得到了这个返回值,现在我们通过如下赋值语句来显示这个返回值
select’return’=@return 方法二: 注:return只能返回整形数据
createprocproc_stu @numint as select@num=count(*)fromstudent
return@num go 我们来执行下: declare@return exec@return=proc_stu
select’return’=@return

【随堂练习】–查询‘李数’老师教的数学成绩大于80分的学生的信息,
并且人数大于3的话,输出达标;否则输出不达标。

//计算日期时间差

图片 1

timestampdiff(显示形式,第一个日期,第二个日期);

存储过程–带参数的程序代码块—代表执行命令存储在数据库中,存储代码,没有调用

//第一个参数的形式

1、–创建存储过程

  1. FRAC_SECOND。表示间隔是毫秒

  2. SECOND。秒

  3. MINUTE。分钟

  4. HOUR。小时

  5. DAY。天

  6. WEEK。星期

  7. MONTH。月

  8. QUARTER。季度

  9. YEAR。年

create procedure–(可简写成proc) –+存储过程名称   —–存储过程格式

select timestampdiff(week,’2017-2-7′,’2017-3-7′) as diff;

 @bianliang1 –+数据类型   
—- as和存储过程名之间可以加参数形参(传递的形参)

//判断条件

 @bianliang2 –+数据类型

2.Case when用法:

 as

SELECT  字段1,

 Begin

case                  ————-如果

  可定义变量—-临时用的形参

when sex=’1′ then ‘男’ ————-sex=’1’,则返回值’男’

  语句

when sex=’2′ then ‘女’ ————-sex=’2’,则返回值’女’

 End

else  ’其他’                ————-其他的返回’其他’

2、–执行存储过程:

end

 方法1:可编程性–存储过程–执行存储过程

as 别名                  ————-结束

默认自动的定义变量接受返回值。

from  sys_user            ——–整体理解:
在sys_user表中如果sex=’1’,则返回值’男’如果sex=’2’,则返回值’女’
否则返回’其他’

【注意】不管成功还是失败,都会自带成功或失败的返回值,

select name,

返回0代表执行成功。

case

 方法2:如何写代码执行存储过程

when sex=1 then ‘男’

   Exec(执行,execute) procedure

when sex=2 then ‘女’

【注意】写代码同方法1的区别,是因为没有定义变量接受返回值就不会自动返回,想要得到变量结果,需要定义变量来接收存储过程才能打印。

else ‘其他’

3、–修改存储过程

end

Alter proc  firstproc  ——同修改表的格式

as sex

As

from staff;

Select code,chinese,math,english ,name from score,student where score,student–写一句存储过程( 查询一个表)可以不写begin..end

//索引:提高mysql执行效率

go

索引:书的目录,查找目录要比查找内容速度快

Exec firstproc

mysql索引:mysql->data

4、–查询多个表必须加begin..end

索引和数据是同步更新(insert,update,delete)的,若给表中所有字段加索引,反而查询速度会慢

create proc secondproc
as
  begin
     select *from score
     select *from student
     select *from teacher
  end
go
exec secondproc

mysql可以承受百万级别的数据:1200000

5、–加语句的存储过程
图片 2

家用台式机可执行2000/秒,配置较好的服务器可执行5000~8000/秒

6、–带参数的存储过程(重点)

1000000/2000 = 500秒=8分钟

格式:

mysql索引类型:

create proc fourproc   –创建存储过程 
@name varchar(20)   –有多个变量,数据类   型后加逗号  –可加参数
as
begin                        –begin相当于大括号的左边
 declare @counts int,@kecheng varchar(20)    –定义临时变量+数据类型
 select @counts=count(*) from teacher where name=@name    
–语句执行
 if @counts=0           –加入分支语句
 begin
  print ‘没有找到这个老师’
 end
 else
 begin
    select @kecheng=course from teacher where name=@name
    declare @count int
   if @kecheng=’语文’     
    begin
     select @count=count(*) from score where stucode in (
     select code from student where chteacher=(select code from teacher
where name=@name)
     ) and chiese>=80
    end
  if @kecheng=’数学’
    begin
     select @count=count(*) from score where stucode in (
    select code from student where mateacher=(select code from teacher
where name=@name)
    ) and math>=80
    end
  if @kecheng=’英语’
    begin
     select @count=count(*) from score where stucode in (
     select code from student where enteacher=(select code from teacher
where name=@name)
     ) and english>=80
  end
  if @count>=3
  begin
   print ‘达标’        –打印存储过程
  end
  else
  begin
   print ‘不达标’
  end
 end
end
go
exec fourproc @name=’莫言1′

(1)主索引(primary
key):主键属于索引,是索引的一种,设置主键的字段,会自动增长,非空且唯一。

【随堂练习】

(2)一般索引(index):任何字段都可以加

图片 3

(3)唯一索引(unique):加唯一索引的字段值不能重复

7、–使用return返回值得存储过程

(4)全文索引(fulltext):生成全文索引是一件特别消耗时间、磁盘空间的做法

图片 4

(5)组合索引:多个字段联合起来作为一个索引

【随堂练习1】

//添加索引(两种写法),对已经存在的表

图片 5

//(1)一个字段可以添加多个索引

【随堂练习2】

(2)加索引(unique)的字段值不允许为空

图片 6

+

8、–带返回值、返回参数、输入参数的存储过程

alter table 表名 add 索引类型 索引名称(字段);

图片 7

alter table books add index index_bName(b_name);

9、–删除存储过程

//两个字段作为组合索引

Drop proc  fiveproc (+存储过程名称)

alter table score add unique uni_stid_coid(stid,coid);

【课后练习】 — 练习题目–
/*
创建一个货物表:编号,货物名称,单位,价格,库存数量,备注
新增10条数据
之后,进货,如果已有此货,增加数量,否则,新增入数据库表中
出货,如果有人要货,判断数量是否充足,充足减库存,否则告诉不足
*/

create unique index uni_bName on books(b_name);

 

//创建表添加索引

 

create table staff2(

id int(11) not null auto_increment primary key,

name varchar(255) not null,

unique uni_name(name)

);

create table staff3(

id int(11) not null auto_increment primary key,

name varchar(255) not null unique

);

//主键,外键与索引

主外键自动索引

若要主外键的索引,必须先把主外键约束去掉

//查看索引

show create table 表名;

show index from 表名;