Sqlserver的存储过程+多表连接的使用

 

存储过程

1、什么是存储过程？

它的目的在于能够方便的从系统表中查询信息，或者完成与更新数据库表相关的管理任务和其他的系统管理任务.T_SQL语句是SQL Server数据库与应用程序之间的编程接口。在很多情况下，一些代码会被开发者重复编写多次，如果每次都编写相同功能的代码，不但繁琐，而且容易出错，而且由于SQL Server逐条的执行语句会降低系统的运行效率。

简述：存储过程就是一条或者多条SQL语句的集合,可以包含数据库操作语句变量逻辑等。

保存数据库中，通过名字可以重复使用

2、使用存储过程有什么优点和缺点？

1、优点

1、存储过程加快系统运行速度，存储过程只在创建时编译，以后每次执行时不需要重新编译。

2、存储过程可以封装复杂的数据库操作，简化操作流程，例如对多个表的更新，删除等。

3、可实现模块化的程序设计，存储过程可以多次调用，提供统一的数据库访问接口，改进应用程序的可维护性。

4、存储过程可以增加代码的安全性，对于用户不能直接操作存储过程中引用的对象，SQL Server可以设定用户对指定存储过程的执行权限。

5、存储过程可以降低网络流量，存储过程代码直接存储于数据库中，在客户端与服务器的通信过程中，不会产生大量的T_SQL代码流量。

—执行速度快

—-编码和应用程序可以独立运行

—提高系统的安全性

2、缺点

1、数据库移植不方便，存储过程依赖与数据库管理系统， SQL Server 存储过程中封装的操作代码不能直接移植到其他的数据库管理系统中。

2、不支持面向对象的设计，无法采用面向对象的方式将逻辑业务进行封装，甚至形成通用的可支持服务的业务逻辑框架.

3、代码可读性差，不易维护。不支持集群。

3、如何自定义存储过程？

 

1、什么是自定义存储过程

自定义存储过程即用户使用T_SQL语句编写的、为了实现某一特定业务需求，在用户数据库中编写的T_SQL语句集合，自定义存储过程可以接受输入参数、向客户端返回结果和信息，返回输出参数等。创建自定义存储过程时，存储过程名前加上"##"表示创建了一个全局的临时存储过程;存储过程前面加上"#"时，表示创建的局部临时存储过程。局部临时存储过程只能在创建它的回话中使用，会话结束时，将被删除。这两种存储过程都存储在tempdb数据库中。

简述：用户在SQLserver中通过采用SQL语句创建的存储过程，或者使用向导创建的存储过程。

 

1、自定义存储过程

1、创建测试类数据库

create database Book --创建数据库

--切换数据库
use Book            

--创建测试books表

create table books (

    book_id int identity (1,1) primary key,

    book_name varchar(20),

    book_price float,

    book_auth varchar(10)

);

 

2、插入测试数据

insert into books(book_name,book_price,book_auth)

values

('论语',25.6,'孔子'),

('天龙八部',25.6,'金庸'),

('雪山飞狐',32.7,'金庸'),

('平凡的世界',35.8,'路遥'),

('史记',54.8,'司马迁');

3、查询数据

select * from books    --查询表数据

--创建无参存储过程----查询

if(exists (select * from sys.objects where name ='getAllBooks'))--判断有没有这个存储过程

    drop proc proc_get_student

go

    create procedure getAllBooks        --创建存储过程

as

    select * from books;

go


--调用
exec getAllBooks;

go

4、创建筛选出金庸作者写的小说---带参存储过程

if(exists (select* from sys.objects where name ='selectBooks'))

    drop proc proc_select_student

go

    create procedure selectBooks

    @book_auth varchar(10)            --增加名字为金庸的参数

as

    select * from books where book_auth=@book_auth;    --带参数查询

go


--调用，执行存储过程

exec selectBooks '金庸';

go

5、修改图书价格---带参存储过程

if(exists (select* from sys.objects where name ='updateBooks'))

    drop proc proc_update_student

go

    create procedure updateBooks       

    @book_id int,

    @book_name varchar(20),

    @book_price float,

    @book_auth varchar(10)

as

update books set book_name=@book_name,book_price=@book_price,book_auth=@book_auth where book_id=@book_id

go

--调用修改存储过程

exec updateBooks 2,'天龙八部',30.7,'金庸'

go

6、添加一条数据---带多条参数存储过程

if(exists (select* from sys.objects where name ='AddBooks'))

    drop proc proc_add_student

go

    create procedure AddBooks

    @book_name varchar(20),

    @book_price float,
    
    @book_auth varchar(10)

as

    insert into books(book_name,book_price,book_auth) values (@book_name,@book_price,@book_auth);

go

--调用添加存储过程

    exec AddBooks '西游记',60.0,'吴承恩'

go    

7、删除一条数据---带参存储过程

if(exists (select* from sys.objects where name ='deleteBooks'))

    drop proc proc_delete_student

go

    create procedure deleteBooks

    @book_id varchar(10)

as

    delete books where book_id=@book_id;

go




--调用，执行删除存储过程

exec deleteBooks 6;

go

8、修改存储过程

alter procedure dbo.getAllBooks

as

    select book_auth from books

--调用，执行存储过程

    exec getAllBooks;

go

9、重命名存储过程

sp_rename getAllBooks,proc_get_allBooks;

--调用，执行存储过程

exec proc_get_allBooks;

go

10、删除存储过程

drop procedure updateBooks;

go

4、系统存储过程有哪些是常用的？

1、什么是系统自带的存储过程

系统存储过程是 SQL Server系统自身提供的存储过程，可以作为命令执行各种操作

2、常用的系统存储过程

exec sp_databases; --查看数据库

exec sp_tables; --查看表

exec sp_columns student;--查看列

exec sp_helpIndex student;--查看索引

exec sp_helpConstraint student;--约束

exec sp_stored_procedures;

exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句

exec sp_rename student, stuInfo;--修改表、索引、列的名称

exec sp_renamedb myTempDB, myDB;--更改数据库名称

exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库

exec sp_helpdb;--数据库帮助，查询数据库信息

exec sp_helpdb master;

 

 

5、有什么系统扩展的存储过程？

1、什么是系统扩展的存储过程

扩展存储过程是以在SQL SERVER环境外执行的动态连接(DLL文件)来实现的，可以加载到SQL SERVER实例运行的地址空间中执行，扩展存储过程可以用SQL SERVER扩展存储过程API编程，扩展存储过程以前缀"xp_"来标识，对于用户来说，扩展存储过程和普通话存储过程一样，可以用相同的方法来执行。

1、xp_cmdshell

可以执行DOS命令下的一些操作；

以文本行方式返回任何输出；

调用语法：EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

2、xp_logininfo

这个返回的是服务器windows 用户和windows 组的信息。

使用方法

EXEC master.sys.xp_logininfo

3、xp_msver

DECLARE @i VARCHAR(20) = 1,

@j varchar(50) = 'ajofen',

@s VARCHAR(80)

EXEC master..xp_sprintf @s OUTPUT,'there are two varaint one is %s and another is %s ',@i,@j

PRINT @s

4、xp_sscanf

这个扩展存储过程是对插入的字符串变量进行格式化取值。

DECLARE @i VARHAR(50) = 1,

@j varchar(50) ,

@s VARCHAR(80)

EXEC master..xp_sscanf '3 + 31 = 34','%s + %s =34',@i OUTPUT,@s OUTPUT

SELECT @i,@j,@s

 

 

 

6、多表连接的方式

1、1内连接-相等连接

解释：相等连接又叫等值连接，在连接条件这使用等号（=）运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

关键字：join on

用法：

select * from emp join dept on emp.deptno = dept.deptno

2、带条件的连接

解释：带选择条件的连接查询是在连接查询的过程中，通过添加过滤条件限制查询的结果，使查询的结果更加准确。

用法：

select * from emp join dept on emp.deptno = dept.deptno and emp.empno = 7369

3、自连接

解释：　如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表

关键字 inner join || on

用法

select * from emp inner join dept on emp.deptno = dept.deptno

4、左外连接

解释：左连接的结果包括LEFT OUTER JOIN关键字左边连接的表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集中右表的所有选择字段均为NULL

关键字：LEFT OUTER JOIN || ON

用法：

select * from emp LEFT OUTER JOIN dept on emp.deptno = dept.deptno

5、右外连接

解释:右连接将返回RIGHT OUTER JOIN关键字右边的表中的所有行。如果右表的某行在左表中没有匹配行，左表将返回NULL。

关键字:RIGHT OUTER JOIN || ON

用法：

select * from emp RIGHT OUTER JOIN dept on emp.deptno = dept.deptno

6、全外连接

解释：全外连接又称为完全外连接，该连接查询方式返回两个连接中所有的记录数据。根据匹配条件，如果满足匹配条件时，则返回数据；如果不满足匹配条件时，同样返回数据，但在相应的列中填入NULL，全外连接返回的结果集中包含了两个完全表的所有数据。全外连接关键字FULL OUTER JOIN。

关键字：FULL OUTER JOIN || ON

用法：

select * from emp FULL OUTER JOIN dept on emp.deptno = dept.deptno

7、exists连接

解释：作为一个连接条件，像字符串连接中的加号一样

关键字：exists

用法：

select * from emp where exists(select *from dept where emp.deptno = dept.deptno and emp.empno = '7369')