Mysql中常用操作语句（2）

最新推荐文章于 2023-08-08 21:59:23 发布

二楼后座Tansen

最新推荐文章于 2023-08-08 21:59:23 发布

阅读量433

点赞数 1

分类专栏： Mysql 文章标签： mysql sql

本文链接：https://blog.csdn.net/qq_39072649/article/details/104548344

版权

Mysql 专栏收录该内容

21 篇文章 2 订阅

订阅专栏

（1）简单select查询

=====SELECT the first=====

--------字句  SELECT *----------
--------字句  FROM    *----------

1:最简单的例子，SELECT 选择列,  FROM  选择表
//select 为选择要显示的列信息，select *表示显示所有列，from表示从那张表进行查询

SELECT  au_id, au_lname, au_fname
FROM authors

 

2:* 表示所有列，显示这个表中的所有列的数据信息
SELECT *
FROM authors

 








3:as 列的别名
//as相当于为列名起一个别名，可以将英文转换为自己懂的中文

SELECT au_id  as  '作者编号', au_lname  as  '姓', au_fname  as  '名'
FROM authors

 

4:常量列
//' 畅销书作者:'  as  ' '表示新增加一列显示信息

SELECT ' 畅销书作者:'  as  ' '  , au_lname  as  '姓', au_fname  as  '名'
FROM  authors

 


5:运算列

--字符串运算，相当于可以把几列合并为一列
//' ' 中间显示加一个空格
SELECT  au_lname + ' ' + au_fname  as '作者姓名'
FROM  authors
 

--数值列运算
SELECT  title, price  as  '原价',  price*0.9  as  '九折'
FROM  titles

 

//求出销售总额price*ytd_sales
SELECT  title, price, ytd_sales, price*ytd_sales  as  '总销售额'
FROM  titles

 


-----------显示前面部分行 top 3(num)

--只看前面部分行top 3 *，显示所有前三行的所有列信息
SELECT  TOP 3 *
FROM  titles

 


--显示销售总额排名前5的书名，价格等信息
SELECT  TOP 5 title, price, ytd_sales, price*ytd_sales  as  '总销售额'
FROM  titles

 


----只显示所有前10%行的信息
SELECT  TOP 10 percent *
FROM title



--DISTINCT  显示不一样的行，单独显示某行，并合并相同的信息

SELECT  DISTINCT  type
FROM  titles

 

----会综合type, pub_id单独显示
SELECT  DISTINCT  type, pub_id
FROM titles

 


----INTO  字句----
把查询出来的数据放入一个新表（自动创建）
//将titles里前3行的信息查询出来放入新表Top3Titles中
SELECT  TOP 3 *
INTO  Top3Title
FROM titles

//从新建表中查询
SELECT  * 
FROM Top3Title

 

SELECT  distinct type
FROM Top3Title
 

--------------字句  WHERE  --------------------
//满足条件的意思
1.= 等于

SELECT  *
FROM  titles
WHERE  pub_id = 1389
 

SELECT  *
FROM  titles
WHERE  price = 19.99

SELECT  *
FROM  titles
WHERE  type = 'business'

 

SELECT  *
FROM  titles
WHERE  title = 'You Can Combat Computer Stress!'
 

2. >   <    >=    <=

SELECT  *
FROM  titles
WHERE  15<= price 
 

SELECT  *
FROM  titles
WHERE  price < 10

SELECT  *
FROM  titles
WHERE  price > 20

3. ><,    !=

SELECT  *
FROM  titles
WHERE  price !=  19.99

//类型除了mod_cook外的数   <>相当于not in (‘mod_cook’)
SELECT  *
FROM  titles
WHERE  type <> 'mod_cook'   
 


4. is null, is not null

SELECT *
FROM titles
WHERE price < 20
order by price desc
//根据价格降序排列
 

---价格为空的
SELECT  *
FROM  titles
WHERE  price is null
 

---价格不为20的（除了20以外其他的）
SELECT  *
FROM  titles
WHERE  price <> 20
 


5.BETWEEN ..... AND   查询之间
---价格在15到20之间
SELECT *
FROM titles
WHERE price BETWEEN 15 AND 20

 

---价格不在15到20之间
SELECT *
FROM titles
WHERE price not BETWEEN 15 AND  20
 




6.满足多条件查询    and,  or,  not
//价格大于10并且类型为business的书籍信息
SELECT *
FROM titles
WHERE price > 10 and  type = 'business'

 

//价格大于10或者类型为business的书籍信息
SELECT *
FROM titles
WHERE price >10 or type = 'business'


 

//满足三个任一条件即可
SELECT *
FROM titles
WHERE price >10 or type = 'business' or ytd_sales > 10000


//两两条件结合
SELECT *
FROM titles
WHERE (price > 10 and type = 'business') or (price > 10 and type = 'mod_cook')
 

//not条件非
SELECT *
FROM titles
WHERE not (price > 10 and type = 'business')
 


//价格不大于10且类型为business
SELECT *
FROM titles
WHERE not price > 10 and type = 'business'
 

SELECT *
FROM titles
WHERE  type = 'mod_cook' or type = 'business'





7. in ,  not in   满足多条件

SELECT *
FROM titles
WHERE  type in ('business', 'mod_cook')     //type是business或mod_cook

 

//与下面的等效
SELECT *
FROM titles
WHERE  type = 'mod_cook' or type = 'business'

//type不是business或mod_cook
SELECT *
FROM titles
WHERE  type not in ('business', 'mod_cook')

 


//价格是20.00或者19.99的
SELECT *
FROM titles
WHERE  price in (20.00, 19.99)
 

//价格是20.00或者19.99的并且ytd_sales > 1000
SELECT *
FROM titles
WHERE  price in (20.00, 19.99)  and ytd_sales > 1000




8.模糊匹配
like %  //全长度分配，title书名含computer字样
SELECT *
FROM  titles
WHERE title like '%computer%'
 


like  %   //title书名含s字样
SELECT  *
FROM  titles
WHERE title like '%s%'

 

SELECT au_lname
FROM authors
WHERE au_lname  like '%s'    //字母s结尾 
 

SELECT au_lname
FROM authors
WHERE au_lname  like 's%'     // 字母s开头
 

like % -----不含computer
SELECT *
FROM  titles
WHERE title  not like '%computer%'
 


like _      //_限定一个字符，一个_表示一个字符，2个__表示两个字符

select *
from authors
//含有nn的，且只含一个
SELECT  au_fname, au_lname    
FROM authors
WHERE au_fname like '_nn'
 

SELECT  au_fname, au_lname
FROM authors
WHERE au_fname like '_h_n'

SELECT  *
FROM authors
WHERE au_id like '721-____-____'

like []      //括号中限定范围

SELECT  au_fname, au_lname
FROM authors
WHERE au_fname like '[a-h]hery'

（2）聚合函数

------SELECT 3 FUNCTION-----

1.聚合函数

--Count(*)---计数器

//计算作者个数
SELECT COUNT(*)
FROM authors
 

SELECT COUNT(*) au_fname
FROM authors

//计算au_fname 中含字母C的作者个数
SELECT COUNT(*)
FROM authors
WHERE au_fname LIKE '%C%'
 


--sum, MAX, MIN, AVG--
SELECT SUM(price)     //所有书单价总和
FROM titles
 

 //所有书单价最大值
SELECT MAX(price)    
FROM titles
 

//所有书单价的平均值
SELECT AVG (price) as '平均值'
FROM titles
 


select sum(price), max(price), min(price), avg(price)
from titles
 

//business类书籍的单价
SELECT MAX(price) as '最高单价', MIN(price) as '最低单价', AVG(price) as '单价平均值'
FROM titles
WHERE type = 'business'
 


SELECT SUM(ytd_sales)  as '销售总额'
From titles
 

//ASCII码排序
SELECT MIN(au_lname) as '最低字符串'
FROM authors
 


2.GROUP by 字句----显示类型有多少种

SELECT avg(price) as '平均单价'
FROM titles
WHERE type = 'business'

SELECT avg(price) as '平均单价'
FROM titles
WHERE type = 'popular_comp'

//在第一行添加type, 并通过type将书分为5类
//GROUP by type   通过type聚类，显示所有的类型
SELECT type, avg(price) as '平均单价'       
FROM titles
GROUP by type
 


SELECT type, MAX(price) as '最高单价', MIN(price) as '最低单价', AVG(price) as '单价平均值', COUNT(*) as '类数'
FROM titles
GROUP by type
 

//type, pub_id嵌套分组查询，通过type, pub_id聚类查询

SELECT pub_id, type, MAX(price) as '最高单价', MIN(price) as '最低单价', AVG(price) as '单价平均值', COUNT(*) as '类数'
FROM titles
GROUP by type, pub_id
 


3.having 子句
SELECT pub_id, type, MAX(price) as '最高单价', MIN(price) as '最低单价', AVG(price) as '单价平均值', COUNT(*) as '类数'
FROM titles
WHERE price > 10
GROUP by type, pub_id
HAVING AVG(price) < 20      
//having 过滤处理后的结果   不能直接用<>限定范围
 

//不能直接用<>限定范围，where中不能出现函数
SELECT type, avg(PRICE) AS '平均单价', MAX(price), COUNT(*)
FROM titles
WHERE price > 10 and AVG(price) < 20
GROUP by type
 


//通过pub_id聚类，再找出总数大于5的
SELECT pub_id, COUNT(*) as '出版商总数', sum(ytd_sales) as '销售总额'
FROM titles
GROUP by pub_id
having COUNT(*) > 5
 

4.order by 子句
-----desc 降序
-----asc  升序

//排序，ORDER by type, pub_id   按 type, pub_id    进行排序
SELECT pub_id, type, MAX(price) as '最高单价', MIN(price) as '最低单价', AVG(price) as '单价平均值', COUNT(*) as '类数'
FROM titles
GROUP by type, pub_id
ORDER by type, pub_id     
    

//按pub_id, price降序排列
select *
from titles
order by pub_id, price desc
 

//按价格降序排列
select *
from titles
order by price desc
 


//按价格升序排列
select *
from titles
order by price asc

（3）多表查询

SELECT title, pub_id
From titles

SELECT pub_id, pub_name
FROM publishers

-----找到两张表之间的联系
//两个表中的pub_id相对应，主外键相连
SELECT title, pub_name
FROM titles, publishers
WHERE titles.pub_id = publishers.pub_id


SELECT title, pub_name,publishers.pub_id
FROM titles, publishers
WHERE titles.pub_id = publishers.pub_id
 


SELECT *
FROM titleauthor

//通过表titleauthor建立联系
SELECT titleauthor.au_id, au_fname, au_lname, titleauthor.title_id, title
FROM titleauthor,authors, titles
WHERE  titleauthor.au_id = authors.au_id and titleauthor.title_id = titles.title_id

 

//把各个表添加别名titleauthor as te
SELECT ta.au_id, au_fname, au_lname, ta.title_id, title
FROM titleauthor as ta,authors as au, titles as ti
WHERE  ta.au_id = au.au_id and ta.title_id =ti.title_id


//添加条件：price < 10
SELECT te.au_id, au_fname, au_lname, te.title_id, title, price
FROM titleauthor as te,authors as au, titles as ti
WHERE  te.au_id = au.au_id and te.title_id =ti.title_id and ti.price < 10
 

//找出Green写的price < 100的书
SELECT te.au_id, au_fname, au_lname, te.title_id, title, price
FROM titleauthor as te,authors as au, titles as ti
WHERE  te.au_id = au.au_id and te.title_id =ti.title_id and ti.price < 100 and (au.au_fname = 'Green' or au.au_lname = 'Green')
 

---请你们找出所有有‘computer’字眼的书，的书名、价格、出版社名字

---请你们找出所有有‘computer’字眼的书，的书名、价格、出版社名字、第一作者的名字

SELECT title, price, pub_id
FROM titles
WHERE title like '%computer%'

SELECT pub_id, pub_name
FROM publishers

SELECT t.title, t.price, t.pub_id, p.pub_name
FROM titles as t, publishers as p
WHERE t.pub_id = p.pub_id and t.title like '%computer%'
 


SELECT au_id, au_lname, au_fname
FROM authors

SELECT au_id,au_ord
FROM titleauthor
WHERE au_ord = 1

SELECT t.title, t.price, t.pub_id, a.au_fname, a.au_lname
FROM titles as t, publishers as p, authors as a, titleauthor as ta
WHERE t.pub_id = p.pub_id and 
      ta.au_id = a.au_id and
      ta.title_id = t.title_id and
      t.title like '%computer%' and
      ta.au_ord = 1

（4）嵌套查询

-------嵌套查询-------

（方法：将条件分开来分别查询，并找到两张表之间的相同对应关系）

--多表查询来做：查询商业书籍的名字和出版社名字

SELECT title, pub_id
FROM titles
WHERE type = 'business'

SELECT pub_id, pub_name
FROM publishers

SELECT title, pub_name
FROM titles, publishers
WHERE titles.pub_id= publishers.pub_id and  type = 'business' 

----消除重复的出版社名称
SELECT distinct pub_name
FROM titles, publishers
WHERE titles.pub_id= publishers.pub_id and  type = 'business' 
 


-------利用嵌套查询来完成
--请找出所有出版过商业书籍的出版社的名字

SELECT pub_name
FROM publishers
WHERE pub_id in ('1389', '0736')

SELECT pub_id 
FROM titles
WHERE type = 'business'


SELECT pub_name
FROM publishers
WHERE pub_id in 
(
	SELECT pub_id
	FROM titles
	WHERE type = 'business'
)
 


---查询销量超过平均销量的书的名字和销量

SELECT title, ytd_sales
FROM titles
WHERE ytd_sales >
(
	SELECT AVG(ytd_sales)
	FROM titles
)
 

--找出在1996-7-1到1996-7-10期间有过销售记录的美国员工的名字

SELECT LastName, FirstName 
FROM Employees
WHERE Country = 'USA' and EmployeeID in
(
	SELECT EmployeeID
	FROM Orders
	WHERE OrderDate between '1996-7-1' and '1996-7-10'
)

（5）连接查询

-------连接查询-------

SELECT * 
FROM stores

SELECT *
FROM discounts

SELECT stores.stor_name, discounts.discounttype, discounts.discount
FROM stores, discounts
WHERE stores.stor_id = discounts.stor_id
 


----连接查询，stores中的信息会完全显示
SELECT stores.stor_name, discounts.discounttype, discounts.discount 
FROM stores left join discounts on (stores.stor_id = discounts.stor_id)
 

-------join-------

--交叉连接--6*3=18rows  两张表中的信息相乘

SELECT stores.stor_name, stores.stor_id, discounts.stor_id, discounts.discounttype, discounts.discount
FROM stores, discounts
 
 
 



--内连接

==where写法实际上是先交叉连接再过滤
SELECT stores.stor_name, discounts.discounttype, discounts.discount
FROM stores, discounts
WHERE stores.stor_id = discounts.stor_id
 

--------内连接inner join  ------------和上面where效果相同
SELECT stores.stor_name, discounts.discounttype, discounts.discount
FROM stores inner join discounts on (stores.stor_id = discounts.stor_id)
 



---------左向外连接------包含所有stores.stor_name（包含所有store表中信息）
SELECT stores.stor_name, discounts.discounttype, discounts.discount 
FROM stores left outer join discounts 
on (stores.stor_id = discounts.stor_id)
 

--右向外连接-----包含所有discounts.discounttype（包含所有discounts表中信息）

SELECT stores.stor_name, discounts.discounttype, discounts.discount 
FROM stores right outer join discounts 
on (stores.stor_id = discounts.stor_id)
 

--全部外连接（完整连接）--------两张表中的信息都有

SELECT stores.stor_name, discounts.discounttype, discounts.discount 
FROM stores full outer join discounts on (stores.stor_id = discounts.stor_id)
 

SELECT * 23rows
FROM authors

SELECT * 8rows
FROM publishers

SELECT a.au_fname, a.au_lname, p.pub_name, a.city, a.state
FROM authors as a inner join publishers as p 
on (a.state = p.state and a.city = p.city)

SELECT a.au_fname, a.au_lname, p.pub_name, a.city, a.state
FROM authors as a left join publishers as p 
on (a.state = p.state and a.city = p.city)

SELECT a.au_fname, a.au_lname, p.pub_name, a.city, a.state
FROM authors as a full outer join publishers as p 
on (a.state = p.state and a.city = p.city)




-----练习-------

SELECT *
FROM authors


select *
from publishers

-------作者和出版社在相同州相同市
SELECT publishers.pub_name, publishers.state, publishers.city, authors.au_lname, authors.au_fname
FROM publishers inner join authors on (publishers.state = authors.state and publishers.city = authors.city)
 

-----包含所有作者，authors在前
SELECT publishers.pub_name, publishers.state, publishers.city, authors.au_lname, authors.au_fname
FROM authors  left outer join publishers on (publishers.state = authors.state and publishers.city = authors.city)
 

SELECT publishers.pub_name, publishers.state, publishers.city, authors.au_lname, authors.au_fname
FROM publishers  full outer join authors on (publishers.state = authors.state and publishers.city = authors.city)

（6）DML语句

-------DML语言（数据操纵语言） 千万要小心数据完整性

--------------------DELETE--------------------

----删除表里面全部行（即删除所有信息）

DELETE sales

---删除配合条件

DELETE sales
WHERE qty > 50

---多表信息配合的删除

DELETE sales
WHERE title_id in
(
	SELECT title_id
	FROM titles
	WHERE type = 'business'
)

DELETE sales
FROM sales inner join titles on (sales.title_id = titles.title_id)
WHERE type like '%c%'




--------------------INSERT-----------------

---写全列信息，插入多行用逗号分隔

Insert publishers (pub_id, pub_name, city, state, country)
Values ('9990', '9990', 'Changsha', 'HN', 'CHN'),
('9991', '99901, 'WuHan', 'HB', 'CHN')


select * 
from publishers

---完全不写列，publishers后信息省略不写，注意信息完整性

Insert publishers 
Values ('9991', '9991', 'Changsha', 'HN', 'CHN')

---写一部分列，特别注意完整性

Insert publishers (pub_id)
Values ('9992')


INSERT INTO students 
VALUES ('s5','102','ww',18,'woman','no notes')

-----插入一行数据，用不完整的列（列的部分信息不写）
INSERT INTO students (sid, personid, name, age)
VALUES ('s6','103','ww',18)


--insert 与 select 混合使用，创建新表
------将publishers插入到新表mypub中
SELECT *
INTO mypub
FROM publishers

-----删除mypub表中所有信息
DELETE mypub

-----插入publishers中所有信息
INSERT mypub
SELECT * 
FROM publishers

SELECT * 
FROM mypub

------将publishers中的信息全改为'0000', '0', 'cs', 'cs', 'cs' 并插入到mypub中
INSERT mypub
SELECT '0000', '0', 'cs', 'cs', 'cs' 
FROM publishers
 

------将publishers中的信息全改为pub_id不变, '0', 'cs', 'cs', 'cs' 并插入到mypub中
INSERT mypub
SELECT pub_id, '0', 'cs', 'cs', 'cs' 
FROM publishers
 


-----插入publishers中city为'changsha'的行
INSERT mypub
SELECT * 
FROM publishers
WHERE city = 'changsha'
 




---------------------UPDATE----------------

--按列更新值
-----将表 publishers 中 country 列的值全改为'CSU'
UPDATE publishers
SET country = 'CSU'
 

SELECT * 
FROM publishers

----多行数据同时更改
UPDATE titles
SET price = 100, royalty =100, advance = 100



--用where控制更新的行（将 city = 'changsha'那一行的country 改为 'CHN'）

UPDATE publishers
SET country = 'CHN'
WHERE city = 'changsha'
 


--带多表信息的查询（把type = 'business'的city 都改为 'changsha'）
UPDATE publishers
SET city = 'changsha'
WHERE pub_id in
(
  SELECT pub_id  
  FROM titles
  WHERE type = 'business'
)
 

----和上面效果类似
UPDATE publishers
SET city = 'central'
FROM publishers as p inner join titles as t on (p.pub_id = t.pub_id)
WHERE type = 'business'
 



--3: 修改数据时要用到多个表的信息

SELECT * FROM stores

'Eric the Read Books'

把这个商店卖过的书的price涨10%
--提示：与delete类似，当要修改的数据的信息分布在不同表里面的时候
--这时，这个sql语句要先用select的思维模式写好

SELECT * FROM Sales

SELECT * FROM stores
WHERE stor_name = 'Eric the Read Books'

SELECT title_id FROM sales
WHERE stor_id in 
(
  SELECT stor_id FROM stores
  WHERE stor_name = 'Eric the Read Books'
)


SELECT * FROM titles

UPDATE titles
SET Price = price * 1.1 
WHERE title_id in 
(
SELECT title_id FROM sales
WHERE stor_id in 
(
  SELECT stor_id FROM stores
  WHERE stor_name = 'Eric the Read Books'
))

（7）基本语法（如声明一个变量）

---------基本语法----------
------------------变量daclare声明------------

--变量直接人工赋值，声明p1,p2两个变量，并赋相应值
Declare
  @p1 varchar(100),
  @p2 varchar(100);
  
  SET @p1 = 'business';
  SET @p2 = 'Com';
  
  SELECT * FROM titles
  WHERE type = @p1 and title like '%' + @p2 + '%';
  -----相当于where type = 'business' and title like '%Com%'
 


--变量用查询结果赋值

题目：找到'BU1111'这本书的出版社名字

DECLARE
  @mypubid varchar(100);

SELECT @mypubid = pub_id 
FROM titles
WHERE title_id = 'BU1111';
---将查询出的pub_id赋值给变量@mypubid

SELECT * 
FROM publishers
WHERE pub_id = @mypubid;
 


------------------IF-ELSE--------------------
----程序模块
IF ()
BEGIN

END
ELSE
BEGIN


END;

我想看看小于平均价格的商业书籍，
如果没有符合条件的请给我推荐三本销量最好的书；
----利用if-else即可

DECLARE   ---定义三个变量
   @myavgprice float,
   @mytype varchar(100),
   @mycount int;
   
SET @mytype = 'business';   ---给变量赋值
SELECT @myavgprice = AVG(price)    ---查询赋值
FROM titles;

SELECT @mycount = COUNT(*)   ----求其满足条件的书籍数量
FROM titles 
WHERE type = @mytype and price < @myavgprice  ----是商业书籍并且价格小于平均价格

IF @mycount >0   --要保证有满足条件的书（小于平均价格的商业书籍）
BEGIN
	SELECT * 
	FROM titles 
	WHERE type = @mytype and price < @myavgprice; 
END

ELSE
BEGIN
	print '对不起，没有找到你要的书，看看下面几本畅销书您喜欢吗？';
  
	SELECT top 3 * ---推荐三本销量最好的书
	FROM titles  
	order by ytd_sales desc;
END
 




------------------WHILE----------------------

WHILE ()
BEGIN

END

---书涨价，每次10%，直到平均价格大于30元

WHILE (SELECT AVG(price) FROM titles) < 30
BEGIN
	UPDATE titles
	SET price = price * 1.1;   ---每次涨10%
  
	SELECT AVG(price) FROM titles;  ---显示平均价格
END
 






书涨价，每次10%，直到平均价格大于50元，
如果有书单价超过70,就不涨了

WHILE (SELECT AVG(price) FROM titles) < 50   ---平均价格<50
BEGIN
	UPDATE titles
	SET price = price * 1.1;   ---每次上涨10%
  
	IF (SELECT MAX(price) FROM titles) > 70  ----由最高价格>70
	BEGIN
		SELECT '我跳出来了';
		BREAK;     ----跳出循环，停止上涨
	END;
  
	SELECT AVG(price) FROM titles;   -----显示平均价格
END
 




------------------CASE----------------

SELECT au_lname, au_fname, city, 
       CASE state     ----state信息选择
		   WHEN 'CA' THEN '勇士队'   ---符合条件则转换为对应信息
		   WHEN 'TN' THEN '篮网队'
		   WHEN 'OR' THEN '魔术队'
		   WHEN 'MI' THEN '雄鹿队'
		   WHEN 'IN' THEN '步行者'
           ELSE '不知道'
       END as 'NBA'  ---将state列信息改为别名NBA
FROM authors

（8）存储过程

---------存储过程Procedure---------
----------------------------------------------------------------------------------------
/*

存储过程：是SQL Server中一组预先定义并编译好的SQL语句（相当于编写一个函数一样），
          可以接受参数、返回状态值和参数值，并且可以嵌套使用。 

使用存储过程的优点：

   在许多应用程序中，不同的程序经常运行一些相同的操作，将其转换成存储过程，以便简化和加快应用程序。
   共享存储过程。程序员共享，无需自己编写，避免潜在的错误。减少开发时间：
   程序员无需了解其内部结构，只需了解存储过程的名称、参数、返回值和作用即可。（模块性） 
   简化软件分发	存储过程将应用程序逻辑转移到服务器上，可独立于软件的升级；
   使用内存高速缓存	SQL Server 可以将存储过程、数据和索引页都放到高速缓存中，从而避免从磁盘中读取。这种节省是相当可观的；
   减少在服务器和客户端之间的数据交换	网络传输的仅是请求和计算结果，处理过程均在服务器端进行。

*/
----------------------------------------------------------------------------------------
	运行存储过程用exec p_test @PPPrice = 100
	删除存储过程用drop procedure p_ProductQuery
	修改存储过程用alter procedure p_ProductQuery



--------------------------存储过程-----------------------------

SELECT title, price, au_lname, au_fname, pub_name
FROM titleauthor as te, titles as t, authors as a, publishers as p
WHERE te.title_id = t.title_id
      and te.au_id = a.au_id
      and t.pub_id = p.pub_id
      
-----------------创建最简单的存储过程-----------
---将上面的查询定义为一个存储过程，下次就可以直接用它了

CREATE PROCEDURE p_favorite_query     ----新建一个存储过程，名称叫做p_favorite_query
AS     ----存储过程p_favorite_query 相当于以下代码段
BEGIN     
	SELECT title, price, au_lname, au_fname, pub_name
	FROM titleauthor as te, titles as t, authors as a, publishers as p
	WHERE te.title_id = t.title_id
		  and te.au_id = a.au_id
		  and t.pub_id = p.pub_id
END

EXEC p_favorite_query;     ------使用exec来执行存储过程，相当于执行一个函数

 
 
---得到的效果和上面的代码查询效果相同









-----------------带一个参数的存储过程--------------
-----如果第一次创建一个存储过程，要用create，第二次还是用和第一同名的再create会出错，所以改用alter（改变，变更的意思，即改变函数的功能）
ALTER PROCEDURE p_favorite_query      ------@mytitle varchar(20)为一个形参，相当于函数中的形参一样
      @mytitle varchar(20)
AS    ----书写代码块
BEGIN
	SELECT title, price, au_lname, au_fname, pub_name
	FROM titleauthor as te, titles as t, authors as a, publishers as p
	WHERE te.title_id = t.title_id
		  and te.au_id = a.au_id
		  and t.pub_id = p.pub_id
		  and title like '%' + @mytitle + '%'     ----这个变量的用途 '%cook%'的书籍
END
EXEC p_favorite_query 'cook';     ------执行存储过程，并且带一个参数（给参数赋值为'cook'）

EXEC p_favorite_query @mytitle = 'cook';    -----参数赋值有两种写法，这种比前面更加清晰（@mytitle = 'cook'）


 

 
-----------------带多个参数的存储过程--------------

ALTER PROCEDURE p_favorite_query     -----创建了一个（因为与前面同名所以使用alter)带三个参数(形参)的存储过程
      @mytitle varchar(20),
      @mypricemin float,
      @mypricemax float
AS    ------函数功能代码块
BEGIN
	SELECT title, price, au_lname, au_fname, pub_name
	FROM titleauthor as te, titles as t, authors as a, publishers as p
	WHERE te.title_id = t.title_id
		  and te.au_id = a.au_id
		  and t.pub_id = p.pub_id
		  and title like '%' + @mytitle + '%'       -----title中包含cook字段的
		  and price between @mypricemin and @mypricemax    -----价格在0-200之间的
END

EXEC p_favorite_query 'cook', 0 ,200;   ----执行函数，并以此给参数赋值

 
 






-----------------带默认参数值的存储过程----------------

ALTER PROCEDURE p_favorite_query
      @mytitle varchar(20),
      @mypricemin float = 10,    -----形参定义时已有默认值10
      @mypricemax float =30     -----形参定义时已有默认值30
AS    ----定义函数功能
BEGIN   
	SELECT title, price, au_lname, au_fname, pub_name
	FROM titleauthor as te, titles as t, authors as a, publishers as p
	WHERE te.title_id = t.title_id
		  and te.au_id = a.au_id
		  and t.pub_id = p.pub_id
		  and title like '%' + @mytitle + '%'
		  and price between @mypricemin and @mypricemax
END

exEC p_favorite_query 'COMPUTER', 0      -----只改变了参数@mypricemin的值，@mypricema依然使用默认值30

 
 




-----------------在存储过程中使用if else语句-------

example：IF exit then out  else reture top sales books

alter PROCEDURE p_favorite_query
      @mytitle varchar(20),
      @mypricemin float = 10,    ----带默认参数值
      @mypricemax float =30
AS
BEGIN
  DECLARE @YOU INT;       ------定义一个变量@YOU
  SET @YOU =     ------给变量@YOU赋值=满足条件的书籍数
  (
	SELECT COUNT(*)   ----满足条件的书籍个数
	FROM titleauthor as te, titles as t, authors as a, publishers as p
	WHERE te.title_id = t.title_id
		  and te.au_id = a.au_id
		  and t.pub_id = p.pub_id
		  and title like '%' + @mytitle + '%'    -----title条件
		  and price between @mypricemin and @mypricemax    ----price条件
  )

	IF (@YOU) > 0    -----存在满足条件的书籍，则输出相应的信息(满足条件的书籍)
	BEGIN
		SELECT title, price, au_lname, au_fname, pub_name
		FROM titleauthor as te, titles as t, authors as a, publishers as p
		WHERE te.title_id = t.title_id
			  and te.au_id = a.au_id
			  and t.pub_id = p.pub_id
			  and title like '%' + @mytitle + '%'
			  and price between @mypricemin and @mypricemax
	END

	ELSE      -----不存在则满足条件的书籍则输出从销售量前三的书籍信息
	BEGIN
		SELECT top 3 title, price, au_lname, au_fname, pub_name    ----取前3
		FROM titleauthor as te, titles as t, authors as a, publishers as p
		WHERE te.title_id = t.title_id
			  and te.au_id = a.au_id
			  and t.pub_id = p.pub_id
	    order by ytd_sales desc    -----根据ytd_sales（到目前为止销售量）来降序排列
	END
 
END

 

--嵌套if else
create procedure p_Test2    ----新建存储过程，带一个形参
       @Choice int
as
if @Choice > 0  ----如果@Choice > 0 ,输出result 为 1
begin 
   select 1 as 'result'
end
else if @Choice < 0  ----如果@Choice < 0 ,输出result 为 -1
begin
   select -1 as 'result'
end
else    ----如果@Choice = 0 ,输出result 为 0
begin
  select 0 as 'result'
end

exec p_test2 0
 


--不嵌套if else 不用return
create procedure p_Test3 
       @Choice int
as
print '========================================1     y'
if @Choice > 0 
begin
   select 1 as 'result'
end;
print '========================================2     y'
if @Choice < 0  
begin
   select -1 as 'result'
end
print '========================================3     n'
if @Choice = 0 
begin
  select 0 as 'result' 
end

exec p_test3 554

 

--不嵌套if else  用return
alter procedure p_Test2 
       @Choice int
as
print '========================================1     y'
if @Choice > 0 
begin
   select 1 as 'result'
   return 
end;
print '========================================2     y'
if @Choice < 0  
begin
   select -1 as 'result'
   return 
end
print '========================================3        n'
if @Choice = 0 
begin
  select 0 as 'result' 
  return 
end

exec p_Test2 -45

 



--直接用return
alter procedure p_Test2 
       @Choice int
as
if @Choice > 0
	 return 1   ----直接用return返回值，但不会直接输出，可以返回赋值给其他变量
else if @Choice < 0  
	return -1
else 
	return 0


DECLARE @return_status int

EXEC @return_status = p_Test2 -232 ------输出值为-1，赋给变量@return_status

SELECT 'Return Status' = @return_status   ---------输出显示信息
 


-----------------------------------------------------------------------------------------
--用存储过程调用另外一个存储过程 并且利用被调用的存储过程的 返回值
-----------------------------------------------------------------------------------------
------也就是嵌套存储过程
create procedure p_Test2_father    -----新建存储过程
       @Choice int
as

DECLARE @return_status int
EXEC @return_status = p_Test2 @Choice  -----调用p_Test2，并给@return_status赋值

if @return_status = 1 
begin
   select 'is 正数' as 'result'
   return 
end;
if @return_status = -1  
begin
   select 'is 负数' as 'result'
   return 
end
if @return_status = 0 
begin
  select 'is 0' as 'result' 
  return 
end

exec p_Test2_father 23232  -----传值给 @Choice ，并传给p_Test2 @Choice
 




----------------------------------------------------------------------------------------------------------------------
--OUTPUT参数
-----------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE mathtutor 
             @m1 smallint, @m2 smallint, @result smallint OUTPUT
AS 
   SET @result = @m1 * @m2   -----定义结果为两数相乘

DECLARE @answer smallint   ----给结果定义一个参数

EXECUTE mathtutor 5,6,@answer OUTPUT  ------把参数传进去
SELECT '结果是：', @answer
 



-----------------Error控制机制-----------------
----------------------------------------------------------------------------------------------------------------
--Error控制
--@@ERROR 返回最后执行的 Transact-SQL 语句的错误代码。
-----------------------------------------------------------------------------------------------------------------
--@@ERROR 检测一个特定错误
--下面的示例用 @@ERROR 在一个 UPDATE 语句中检测限制检查冲突。

--用 @@ERROR 有条件地退出一个过程
--在此示例中，IF...ELSE 语句在存储过程中的 INSERT 语句后检测 @@ERROR。@@ERROR 变量的值将决定传给调用程序的返回值，以指示此过程的成功与失败。

USE pubs
GO

-- Create the procedure.
alter PROCEDURE add_author       -----次存储过程含有9个形参
	@au_id varchar(11),@au_lname varchar(40),
	@au_fname varchar(20),@phone char(12),
	@address varchar(40) = NULL,@city varchar(20) = NULL,
	@state char(2) = NULL,@zip char(5) = NULL,
	@contract bit = NULL
AS

-- Execute the INSERT statement.功能代码块
INSERT INTO authors(au_id,  au_lname, au_fname, phone, address, city, state, zip, contract)  -----对应的列名
	 values(@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract) ---对应的列值

-- Test the error value.
IF @@ERROR <> 0   ----不为0，插入出错
BEGIN
   -- Return 99 to the calling program to indicate failure.
   PRINT 'An error occurred loading the new author information'  ----输出提示信息，出入信息出错
   RETURN(99)
END

ELSE   -----为0，插入成功
BEGIN
   -- Return 0 to the calling program to indicate success.
   PRINT 'The new author information has been loaded'  -----输出提示信息，插入信息已保存
   RETURN(0)
END
GO

exec add_author '111-11-1111', 'Tang', 'Tang', '011 111-1111', 'changsha', 'Hunan', 'CN', '11111', '1';  -----执行插入操作

select *
from authors


 
 











/*
--用Raiserror抛出一个异常 RAISERROR ('添加该明细凭证失败,请检查您提交的数据,稍后再试!',16,1)

RAISERROR
返回用户定义的错误信息并设系统标志，记录发生错误。通过使用 RAISERROR 语句，
客户端可以从 sysmessages 表中检索条目，或者使用用户指定的严重度和状态信息动态地生成一条消息。
这条消息在定义后就作为服务器错误信息返回给客户端。

语法
RAISERROR ( { msg_id | msg_str } { , severity , state }
    [ , argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

*/

USE pubs
GO

-- Create the procedure.
alter PROCEDURE add_author     ----定义带9个参数的存储过程
	@au_id varchar(11),@au_lname varchar(40),
	@au_fname varchar(20),@phone char(12),
	@address varchar(40) = NULL,@city varchar(20) = NULL,
	@state char(2) = NULL,@zip char(5) = NULL,
	@contract bit = NULL
AS

-- Execute the INSERT statement.
INSERT INTO authors(au_id,  au_lname, au_fname, phone, address, city, state, zip, contract)   ------添加信息操作，外部的实参传进来
values(@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)

-- Test the error value.
IF @@ERROR <> 0    -----不为0，插入失败
BEGIN
   -- Return 99 to the calling program to indicate failure.
   RAISERROR ('数据插入失败',16,1)    -----使用raseeror语句
   RETURN(99)
END
GO

exec add_author '101','1111','11', '11', '111', '111111', '111111', '111111', '11111'

 




-------------------------embed-----------------
-----------------嵌套存储过程，将p_son嵌套在p_mother中执行
create PROCEDURE p_son    ----新创建存储过程
      @mytitle varchar(20),
      @mypricemin float = 10,     -----带默认值的形参
      @mypricemax float =30,
      @yououtput int output    ------定义输出信息
AS
BEGIN      -----存储过程功能代码块
  DECLARE @YOU INT;      ----定义变量@YOU  ，为整数类型
  SET @YOU =    ----赋值
  (
	SELECT COUNT(*)
	FROM titleauthor as te, titles as t, authors as a, publishers as p
	WHERE te.title_id = t.title_id
		  and te.au_id = a.au_id
		  and t.pub_id = p.pub_id
		  and title like '%' + @mytitle + '%'
		  and price between @mypricemin and @mypricemax 
  )
  
  SET @yououtput = @YOU       ---将输出变量直接赋值为@YOU
END

/*
-----验证上述程序完整性
DECLARE
  @yououtput int;
  
exec p_son 'computer99999999', 10, 30, @yououtput output;
print @yououtput;
*/

create PROCEDURE p_mother
      @mytitle varchar(20),
      @mypricemin float = 10,
      @mypricemax float =30
AS
BEGIN
  DECLARE @YOU INT;     ------自定义变量@YOU
  exec p_son @mytitle, @mypricemin, @mypricemax, @YOU output;   ------将存储过程p_son嵌套在p_mother中，并给形参相应的赋值，其中@YOU的值来自于p_son中

	IF (@YOU) > 0    ----如果存在满足条件的则直接输出书籍具体信息
	BEGIN
		SELECT title, price, au_lname, au_fname, pub_name
		FROM titleauthor as te, titles as t, authors as a, publishers as p
		WHERE te.title_id = t.title_id
			  and te.au_id = a.au_id
			  and t.pub_id = p.pub_id
			  and title like '%' + @mytitle + '%'
			  and price between @mypricemin and @mypricemax
	END

	ELSE    -----如果不存在则输出销量排名前三的书籍信息
	BEGIN
		SELECT top 3 title, price, au_lname, au_fname, pub_name
		FROM titleauthor as te, titles as t, authors as a, publishers as p
		WHERE te.title_id = t.title_id
			  and te.au_id = a.au_id
			  and t.pub_id = p.pub_id
	    order by ytd_sales desc
	END
 
END

exec p_mother 'com2222puter', 10,20;    ------执行此存储过程



 



------------------------insert update delete and procedure-----------
-----------------在存储过程过程中使用删除插入更新等操作
---完整性
---异常控制
  
-------输入并删除一个作者，如果存在，且没写过书，则执行删除；其他则输出相应提示信息
create procedure p_delauthor       ----新建存储过程
  @fnameinput varchar(20), 
  @lnameinput varchar(20),
  @errorcode int output,        -----错误编号 
  @errormessage varchar(100) output    ----错误输出信息
as   ---功能代码块
begin
  
  Declare
    @titlecount int,    -----自定义两个变量
    @authorcount int;   
  
  SET @authorcount = (    ------定义有这个作者
     SELECT COUNT(*)
     FROM authors 
     WHERE au_fname = @fnameinput
           and au_lname = @lnameinput  
  )
  
  IF @authorcount <= 0    ------如果不存在这个作者（即数量为0）
  BEGIN
    SET @errorcode = 1;     ------给变量赋值
    SET @errormessage = '亲，你是不是输入错了作者的姓名';
    return;
  END
  
  IF @authorcount > 1     ------存在多个作者
  BEGIN
    SET @errorcode = 2;
    SET @errormessage = '亲，找到了多个作者，都删了吗？';
    return; 
  END
 
  ------------------------------------------
  SET @titlecount =       ----作者写的书的数量
  (
     SELECT COUNT(*)
     FROM titleauthor as te, authors as a
     WHERE te.au_id = a.au_id
           and au_fname = @fnameinput
           and au_lname = @lnameinput  
  )
  
  IF @titlecount > 0    ---作者写了书
  BEGIN
    SET @errorcode = 3;
    SET @errormessage = '亲，这个作者不能删，他写了书？';
    return ;
  END
  
  -----------------------------------------
  delete authors     ------把输入的作者执行删除操作
  where au_fname = @fnameinput and au_lname = @lnameinput
  
end;


declare     -----定义两个实参自变量，主要这是为了错误信息按预定的输出
  @errorcodeout int,
  @errormessageout varchar(100)

exec p_delauthor 'Johnson', 'White', @errorcodeout output, @errormessageout output   -----执行删除 'Johnson', 'White'这个人

select @errorcodeout, @errormessageout    ----输出删除后操作的信息


 


-----------------练习-----------------------
-------------------------------------------------------------------------------
/*
练习:

在Northwind中建立一个存储过程p_ProductQuery
输入一个产品的名称,就给出这个产品的产品名称ProductName,供应商的名称CompanyName,单价UnitPrice

EXEC p_ProductQuery 'Hot Pepper'

ProductName                              CompanyName                              UnitPrice             
---------------------------------------- ---------------------------------------- --------------------- 
Louisiana Fiery Hot Pepper Sauce         New Orleans Cajun Delights               21.0500

select *
from Suppliers

select*
from Products

alter procedure p_ProductQuery 
			@ProductName varchar(10)    ----注意要写变量的数据类型
as
begin
	select p.ProductName, s.CompanyName, p.UnitPrice
	from Products as p inner join Suppliers as s on (p.SupplierID = s.SupplierID)
	where ProductName = @ProductName
end

exec p_ProductQuery 'Chang'



练习:

在Northwind中建立一个存储过程p_CustomLastOrderDetail
输入一个顾客Customer的编号CustomerID,就给出这个顾客最近一次订单的清单(产品名称,订货个数)

exec p_CustomLastOrderDetail 'TOMSP'

CustomerID ProductName                              Quantity OrderID     OrderDate                                              
---------- ---------------------------------------- -------- ----------- ------------------------------------------------------ 
TOMSP      Teatime Chocolate Biscuits               12       10967       1998-03-23 00:00:00.000
TOMSP      Maxilaku                                 40       10967       1998-03-23 00:00:00.000

提示：
      创建存储过程用CREATE Procedure p_ProductQuery
                                    @InputProductName varchar(50)
		   AS
		   ...............................

      运行存储过程用exec p_test @PPPrice = 100
      删除存储过程用drop procedure p_ProductQuery
      修改存储过程用alter procedure p_ProductQuery


------------------------答案--------------------------

CREATE Procedure p_ProductQuery
       @InputProductName varchar(50)
AS
SELECT dbo.Products.ProductName, dbo.Suppliers.CompanyName, dbo.Products.UnitPrice
FROM dbo.Products INNER JOIN dbo.Suppliers ON dbo.Products.SupplierID = dbo.Suppliers.SupplierID
WHERE dbo.Products.ProductName LIKE '%'+@InputProductName+'%'   ----模糊查询

------------------------执行实例----------------------------
EXEC p_ProductQuery 'Hot Pepper'


-----------------------答案A  子查询---------------------------

ALTER PROCEDURE p_CustomLastOrderDetail 
  @InputOrderID VarChar(20)
AS
SELECT dbo.Orders.CustomerID, dbo.Products.ProductName, dbo.[Order Details].Quantity, 
      dbo.Orders.OrderID, dbo.Orders.OrderDate
FROM dbo.[Order Details] INNER JOIN
      dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID INNER JOIN
      dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID     ----连接三张表
WHERE dbo.[Order Details].OrderID = 
      (
			SELECT OrderID 
			FROM Orders 
			WHERE CustomerID = @InputOrderID  AND OrderDate =    ----日期为指定最近的日期
			(
				  SELECT MAX(OrderDate) 
				  FROM Orders 
				  WHERE CustomerID = @InputOrderID 
			 )
      )

------------------------执行实例----------------------------
EXEC p_CustomLastOrderDetail 'ALFKI'


-----------------------答案B 分步骤编写代码---------------------------

create procedure p_FindLastOrderDetail
       @InputCustomerID varchar(30)
as
declare
   @LastDatetime datetime,
   @FindOrderID int

SET @LastDatetime =(select Max(orderdate) from orders where customerID = @InputCustomerID )

SET @FindOrderID = (select OrderID from orders 
                   where orderdate = @LastDatetime and customerID = @InputCustomerID)

SELECT dbo.Orders.CustomerID, dbo.Products.ProductName, dbo.[Order Details].Quantity, 
      dbo.Orders.OrderID, dbo.Orders.OrderDate
FROM dbo.[Order Details] INNER JOIN
      dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID INNER JOIN
      dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID
where dbo.[Order Details].orderid = @FindOrderID

------------------------执行实例----------------------------
exec p_FindLastOrderDetail 'TOMSP'
*/
------------------------------------------------------------------------------





----------------------------------------------------------------------------------------
/*

练习:

在Northwind中建立一个存储过程p_CustomOrder,练习使用默认值

输入一个顾客Customer的编号和一个开始日期和一个结束日期
那么根据这个输入的时间段 显示出该客户该时间段内所有的订单基本信息(订单编号,雇员名称,订购日期)

要求使用默认值的时间,也就是用户如果没有输入开始时间和结束时间,也要能运行

CustomerID OrderID     OrderDate                   LastName             FirstName  
---------- ----------- ----------------------------------------------- ---------- 
VINET      10248       1996-07-04 00:00:00.000     Buchanan             Steven
VINET      10274       1996-08-06 00:00:00.000     Suyama               Michael
VINET      10295       1996-09-02 00:00:00.000     Fuller               Andrew
VINET      10737       1997-11-11 00:00:00.000     Fuller               Andrew
VINET      10739       1997-11-12 00:00:00.000     Leverling            Janet


-----------------------答案---------------------------
alter procedure p_CustomOrder
  	@InputCustomerID varchar(23),
        @BeginDate datetime = '1910-1-1',   -----使用默认值
        @EndDate datetime = '2200-1-1'     
as

SELECT dbo.Orders.CustomerID, dbo.Orders.OrderID, 
      dbo.Orders.OrderDate, dbo.Employees.LastName, dbo.Employees.FirstName
FROM dbo.Orders INNER JOIN
      dbo.Employees ON dbo.Orders.EmployeeID = dbo.Employees.EmployeeID   ----连接两张表
where dbo.Orders.CustomerID like '%'+ @InputCustomerID + '%' AND dbo.Orders.OrderDate >= @BeginDate
      AND dbo.Orders.OrderDate <= @EndDate   -----等于输入的CustomerID，并且在指定日期内

-----------------------运行实例---------------------------
exec p_CustomOrder 'VINET'
exec p_CustomOrder 'VINET', '1996-8-1','1999-1-1'
exec p_CustomOrder 'VINET', '1996-8-1'
exec p_CustomOrder @InputCustomerID='VINET', @EndDate='1996-8-1'

*/
----------------------------------------------------------------------------------------






---------------------------------------------------------------------------------------------------------------------
/*

练习:流程控制

在Northwind中建立一个存储过程p_CustomLastAllMoney
输入一个顾客Customer的编号和一个开始日期和一个结束日期
返回这个顾客这个期间中的所有订单的总金额(所有订单中所有产品都要UnitPrice*Quantity,并且求和)
如果这个顾客没有任何销售记录,就返回0


------------------------答案A----------------------------
create procedure p_CustomLastAllMoney
	@CustomerID varchar(30),
	@BeginDate datetime,
	@EndDate datetime

AS
begin
	declare 
		@control int
		
set @control = (  -----含有满足条件的数量
	select count(*)
	from orders 
	where CustomerID like '%' + @CustomerID + '%' and orderDate between @BeginDate and @EndDate)

if @control > 0  -----存在则输出满足的顾客信息
begin
	select sum(UnitPrice*Quantity) as '您的消费总额'
	from [Order Details]
	where OrderID in   ----嵌套查询，因为CustomerID不在表Order Details中，所以用多表查询
	(
		select orderID
		from orders
		where CustomerID like '%' + @CustomerID + '%' and orderDate between @BeginDate and @EndDate
	)
end

else -----没有满足条件的顾客
begin
	select 0 as '您的消费总额'
end
end

exec p_CustomLastAllMoney '10248', '1990-01-01', '1999-01-01'

select *
from orders






----------------------------------------------------------------
练习:

在Northwind中建立一个存储过程p_MaxOrderProduct

输入一个开始日期和一个结束日期
返回这个期间销量最大的产品名称和总销量

------------------------答案A----------------------------
create procedure p_MaxOrderProduct ------根据输入确定参数变量
	@BeginDate datetime,
	@EndDate datetime

as
begin
	declare   ----自定义变量，在下面会用到，并且要自己进行赋值
		@MaxProductID int,
		@MaxQ int

	----从表中查询给变量赋值
	select Top 1 @MaxProductID = productid, @MaxQ = sum(quantity) 
	from [Order Details]
	where orderid in   -----嵌套查询，发生在两张表中
		(
			select orderid
			from orders
			where orderdate between @BeginDate and @EndDate
		)
	group by productid   ------根据id聚类
	order by sum(quantity) desc  -----根据销量排名，取第一



	select ProductName as '产品名', @MaxQ as '最大销量'    -----显示销量最大的产品信息
	from Products
	where ProductID = @MaxProductID

end

exec p_MaxOrderProduct '1991-1-1','1999-1-1'
 
















-----------------练习1，注意保证输入各项参数信息的正确性
/*----------------------------------------------------------------------------------------------------
--在titles表中添加一本书的存储过程

--注意判断各个输入值的正确性
--假设书必须由出版社出版 添加书的时候 实际中用户录入的一定是出版社名称 而数据库titles表中存的是title-id，必须把用户录入的出版社名称转化为出版社编号

--执行实例
exec p_addtitles '222222','无所谓','business','New Moon Books',1.1,1.1,32,23,'34','2003-3-2'

select * from titles
select * from publishers
----------------------------------------------------------------------------------------------------*/

--如果已经存在这个存储过程就先删除
if exists 
(select * from dbo.sysobjects 
where id = object_id(N'[dbo].[p_addtitles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_addtitles]
GO

--创建存储过程
create procedure p_addtitles   ----需要添加的参数信息
       @title_id varchar(6),
       @title varchar(80) = '未命名',   -----参数已添加默认值
       @type varchar(20) = 'UNDECIDED' ,
       @pub_name varchar(40),                   ------------------特殊处理
       @price money = 0,
       @advance money = 0,
       @royalty int = 0,
       @ytd_sales int = 0,
       @notes varchar(200) = '无',
       @pubdate datetime = '2005-6-21'
as

-------添加信息有误的情况---------

--书编号是主键，不能为空
If @title_id is NULL
begin
  select '请填写title_id' as 'Result'
  return 
end

--书编号是主键，不能重复
if (select count(*) from titles where title_id = @title_id) > 0
begin
  select '请重新填写title_id，重名了哥们！' as 'Result'
  return 
end

--各种价格和总额不能为负数
if @price < 0 OR @advance < 0 OR @royalty < 0 OR @ytd_sales < 0
begin
  select '数据不能小于0，请重新填，哥们！' as 'Result'
  return   
end


--假设书必须由出版社出版 添加书的时候 实际中用户录入的一定是出版社名称 而数据库titles表中存的是title-id，必须把用户录入的出版社名称转化为出版社编号
declare 
   @pub_id char(4)

if (    -----检测到无此出版社名称
	select count(*)    
	from publishers 
	where pub_name = @pub_name ) <= 0    
begin
  select '请重新填写pub_name，没有这个出版社！' as 'Result'
  return   
end

SET @pub_id =    ------将pub_name 转换为对应pub_id
	(
		select pub_id 
		from publishers 
		where pub_name = @pub_name
	)  


--所有准备工作做好了 最后插入一行数据
insert into titles       -------执行程序后所输入的参数值便会插入到表中，并且将输入的@pub_name转为相应的@pub_id
values(@title_id,@title,@type,@pub_id,@price,@advance,@royalty,@ytd_sales,@notes,@pubdate)

GO


------- @pub_name要保证已有的才行，@title_id不能重复
exec p_addtitles 'PC1111', 'Computer Science', 'popular_comp', 'New Moon Books', '66.00', '3000.00', '20', '4000', 'Hello', '2019-6-17'


select *
from titles

select *
from publishers




 
 

 











-----------------练习2，注意保证输入作者信息无误
---------------------------------------------------------------------------------------
/*输入一个作者的姓名，如果没有，提示删除失败；
如果找到多个人，提示删除谁；如果已经写过书，提示不能删除*/
----------------------------------------------------------------------------------------
create procedure p_delauthors -----创建含两个参数的存储过程，输入作者的姓名
       @au_lname varchar(40),
       @au_fname varchar(20)
as
begin
	declare     ------自定义变量
		@author_num int

	set @author_num =    -----给变量赋值，以此来确定是否有此作者
		(
			select count(*) 
			from authors 
			where au_lname = @au_lname and au_fname = @au_fname
		)
		
	If @au_lname is NULL or @au_fname is NULL   ------未填入参数信息
	begin
	  select '删除失败：因为你没有告诉我要删谁？' as 'Result'
	  return 
	end

	if  @author_num <= 0   ------没有此人
	begin
	  select '没有找到这个人，您是不是已经删除了，或者录入错了？' as 'Result'
	  return   
	end
	else if @author_num > 1   ------有多个此人
	begin
	  select '同时找到了多个同名的作者，您可以从界面上选择。。。。按钮来确定删除？' as 'Result'
	  return   
	end


	declare @au_id varchar(40)  

	SET @au_id = 
		(
			select au_id 
			from authors 
			where au_lname = @au_lname and au_fname = @au_fname
		)

	if (select count(*) from titleauthor where au_id = @au_id) > 0   ------如果发现此人已写过书
	begin
	  select '删除失败：这个作者已经写了书了？' as 'Result'
	  return     
	end

	delete authors    ------执行删除操作
	where au_id = @au_id 
end

exec p_delauthors 'aa','aa'

（9）视图view

----------视图View-------------------
--------------------------------------------------------------------------------------------------
--创建一个视图 方便用户查询一些复杂的数据关系
--接着利用参数来从视图中查询数据
--------------------------------------------------------------------------------------------------
--------存储过程相当于新建一个函数，而视图则是把一些代码块整合为一个视图，方便查询的书写
create View View_Test   ------创建视图，名称叫做View_Tset
as   ----功能代码块，没有begin ..end.. ；功能就是把这三张表连接起来，方便多次使用
SELECT dbo.authors.au_lname AS 姓, dbo.authors.au_fname AS 名, dbo.titles.title AS 书名, 
      dbo.titles.price * 0.9 AS 打折单价, dbo.publishers.pub_name AS 出版社
FROM dbo.authors INNER JOIN
      dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN
      dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id INNER JOIN
      dbo.publishers ON dbo.titles.pub_id = dbo.publishers.pub_id



declare @MyPrice money    -----定义声明变量
--
Set @MyPrice = 10     -----给变量赋值
--
select *      ----执行视图查询；视图相当于简化了from表的查询过程，在where中输入一些参数即可查询
from view_test   
where 打折单价 > @MyPrice    ----打折单价是dbo.titles.price 的代称



 
 





--------------------------------------------------------------------------------------------------
/*使用带有复杂 SELECT 语句的简单存储过程  把视图的功能给实现了  注意实现的机制和方法不同*/
-----------------------------------------------------------------------------------------------------
------view的函数就是可以根据需要修改查询信息，而存储过程则只能根据参数查询
create procedure p_Test   ----新建存储过程
as    -----功能把三张表连接起来
begin   
	SELECT dbo.authors.au_lname AS 姓, dbo.authors.au_fname AS 名, dbo.titles.title AS 书名, 
		  dbo.titles.price * 0.9 AS 打折单价, dbo.publishers.pub_name AS 出版社
	FROM dbo.authors INNER JOIN
		  dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN
		  dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id INNER JOIN
		  dbo.publishers ON dbo.titles.pub_id = dbo.publishers.pub_id
end

exec p_test    ----执行存储过程

（10）触发器Trigger

-----------触发器Trigger-------------
/*1：自动执行的存储过程
2：在增加删除和修改三个常见操作发生时自动执行
3：触发器是建立在表上的
4：有增加、删除和修改触发器三种类型*/


---------------------------------------------------------------------------
--触发器的基本语法
---------------------------------------------------------------------------
CREATE TRIGGER <trigger_name, sysname, trig_test>   -----创建触发器的基本格式
ON <table_name, sysname, pubs.dbo.sales>
FOR DELETE, INSERT, UPDATE   -----执行何种操作以触动触发器
AS 
BEGIN
	RAISERROR (50009, 16, 10)
END



------------------删除触发器----------------
--------------------------------------------------------------------------
--简单的删除触发器
--------------------------------------------------------------------------
--建立一个简单的和其他表没有任何关系的表，避免删除时和其他表的联系
SELECT * 
INTO AAA    ----新建AAA表，将titles中数据全都拷贝过来
FROM titles


create trigger t_DelAAADoSomething   -----建立一个触发器
ON AAA   ---在表AAA中
FOR Delete   ----是为了执行删除触发
AS   ----删除触发下面语句
	print('Just Now You Del a Recorder of AAA table')

--运行删除语句就会激发上面这个触发器(输出一次print，影响1行)
delete aaa
where title_id = 'bu1032'



 
 

 

create trigger t_DelAAADoSomethingAndDoAgain  ----重新建立触发器
ON aaa  
FOR Delete  ---删除触发
AS
	print('Just now you run del and run  t_DelaaaDoSomethingAndDoAgain')


--运行删除语句就会激发上面这二个触发器， t_DelAAADoSomething和t_DelAAADoSomethingAndDoAgain
delete aaa
where title_id = 'bu1032'

/*
Just Now You Del a Recorder of AAA table
Just now you run del and run  t_DelaaaDoSomethingAndDoAgain

（所影响的行数为 1 行）
*/


 


 



--还原aaa表数据 
insert into aaa
select * from titles



------------------插入或更新触发器-----------------

--------------------------------------------------------------------------
--简单的更新和插入触发器
--------------------------------------------------------------------------
create trigger t_UpdateAAADosomthing
ON aaa   ----在表AAA中进行操作
FOR Update,Insert  -----执行更新和插入触发
AS

	print('Just now you Update or insert aaa table')



--执行一行更新语句，即会触发更新触发器
update aaa
set price = price + 0.1

/*
Just now you Update or insert aaa table

（所影响的行数为 18 行）

*/

 





--------------------------------------------------------------------------
--从临时删除表中读取刚才删除的纪录信息 SELECT title FROM deleted

--每当对某个表执行delete时都会生成和某个表结构一样的一个临时表deleted
--每当对某个表执行update，insert时都会生成和某个表结构一样的一个临时表inserted
--可以利用这二个临时表来活动最近一次delete，insert，update的数据
--------------------------------------------------------------------------

create trigger t_DelAAADoDetail    -----创建触发器
ON AAA
FOR Delete   ----删除即触发
AS
	print('Just Now You Del a Recorder of AAA table')

Declare  ---定义自变量
   @title varchar(80)

SET @title = (SELECT title FROM deleted)   -----为变量赋值为title，每当对某个表执行delete时都会生成和某个表结构一样的一个临时表deleted

PRINT '" ' + @title + ' " : is your just del book name '   ----删除输出信息，这个是你刚刚删除的

--删除一行纪录
delete aaa
where title_id = 'PC1035' 

/*
Just Now You Del a Recorder of AAA table    -----前面两个删除触发器依然存在
Just now you run del and run  t_DelaaaDoSomethingAndDoAgain
Just Now You Del a Recorder of AAA table

（所影响的行数为 1 行）

" But Is It User Friendly? " : is your just del book name 
*/

 


--删除多行纪录，有问题，游标，同时删除多行记录会出现问题
delete aaa
where type = 'business' 

/*
Just Now You Del a Recorder of AAA table
Just now you run del and run  t_DelaaaDoSomethingAndDoAgain
Just Now You Del a Recorder of AAA table
服务器: 消息 512，级别 16，状态 1，过程 t_DelAAADoDetail，行 10
子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
语句已终止。
*/



 




--------------------------------------------------------------------------
--每次添加一本书的销售纪录就要修改这本书的总销量
--------------------------------------------------------------------------

CREATE trigger t_AddSalesANDAddYtd_Sales
ON Sales   ----在Sales表中执行触发器操作
FOR Insert   ----更新即触发
AS
 
declare ---定义变量
   @NewYtdSales int,
   @NewSalesTitleID varchar(30)

   SET @NewYtdSales = (SELECT qty FROM Inserted)  ----每当对某个表执行update，insert时都会生成和某个表结构一样的一个临时表inserted，为变量赋值
   SET @NewSalesTitleID = (SELECT title_id From Inserted)

   UPDATE Titles    -----执行更新操作
   SET Ytd_Sales = Ytd_Sales + @NewYtdSales  ----销量更新
   WHERE title_id = @NewSalesTitleID   ----新销售书的编号

---------------------------------------------------------
INSERT sales  ---执行插入新销售记录，触发上面的触发器
Values(6380,23456,'1999-1-1',1000,'Net 60','BU1032')

 




/*--------------------------------------------------------------------------
--察看触发器的方法：
	触发器总是和表相关联的
	在企业管理器中选中一个表，从右键菜单的所有任务中选择“管理触发器”

--通过修改表的性质 允许和禁止触发器
--------------------------------------------------------------------------*/

use pubs

ALTER TABLE employee   ----变更表
      DISABLE Trigger employee_insupd   -----不允许使用触发器employee_insupd

ALTER TABLE employee
      ENABLE Trigger employee_insupd   -----允许使用触发器employee_insupd














--------------------------------------------------------------------------
------------------练习-----------------------
--每次添加一本书的销售纪录就要给写书的作者1元钱的版权费
--------------------------------------------------------------------------

CREATE trigger t_AddSalesANDAddRoyaltyper   -----创建新触发器
ON Sales  ----在表中sales使用
FOR Insert   ----插入即触发
AS
 
declare   ----自定义插入信息
   @NewYtdSales int,
   @NewSalesTitleID varchar(30)

   SET @NewYtdSales = (SELECT qty FROM Inserted)  ----每当对某个表执行update，insert时都会生成和某个表结构一样的一个临时表inserted，为变量赋值
   SET @NewSalesTitleID = (SELECT title_id From INSERTED)

   UPDATE TitleAuthor    ------更新插入后的信息
   SET royaltyper = royaltyper + @NewYtdSales   ----每次添加一本书的销售纪录就要给写书的作者1元钱的版权费
   WHERE title_id = @NewSalesTitleID   -----写该书的作者写的此书
----------------------------------------------------------------------

INSERT sales   -----插入数据即触发上面的触发器
Values(6380,2349,'1999-1-1',1000,'Net 60','TC7777')









 















--------------------------------------------------------------------------
--附加练习（SQL Server的pubs数据库自带例子）（带逻辑、带错误检验、带事务控制）
--添加一个雇员的时候使用触发器控制用户录入的雇员的job_lvl必须在max_lvl与min_lvl中间
--------------------------------------------------------------------------
drop trigger employee_insupd    ----删除触发器


CREATE TRIGGER employee_insupd
ON employees   -----在此表中进行操作
FOR insert
AS
	--声明变量.
  declare	@min_lvl tinyint,
   					@max_lvl tinyint,
   					@emp_lvl tinyint,
   					@job_id smallint
  --为变量赋值，就是刚才获取insert的这个雇员，用户输入的信息
	select @min_lvl = min_lvl,   -----从用户新增的信息给变量赋值
  			 @max_lvl = max_lvl,
   			 @emp_lvl = i.job_lvl,
   			 @job_id = i.job_id
	from employee as e, jobs  as j, inserted as i
	where e.emp_id = i.emp_id AND i.job_id = j.job_id

	--使用Raiserror，发出错误信息，放弃本次添加，就是回滚事物，本次添加无效
	IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
	begin
   	raiserror ('The level for job_id:%d should be between %d and %d.',
      	16, 1, @job_id, @min_lvl, @max_lvl)
   	ROLLBACK TRANSACTION
	end




select *
from employees



--不发出错误信息，直接把用户错误的输入纠正为最大值或最小值
CREATE trigger t_AddSalesCheckJob_lvl
ON employees
FOR Insert   -----插入即触发
AS

declare
   @job_id int,
   @job_lvl int,
   @emp_id varchar(10),
   @min_lvl int,
   @max_lvl int
  
  SET @job_id = (SELECT job_id FROM INSERTED)   ------从新增的数据给变量赋值
  SET @job_lvl = (SELECT job_lvl FROM INSERTED)
  SET @emp_id = (SELECT emp_id FROM INSERTED)
  SET @Min_lvl = (SELECT min_lvl FROM jobs WHERE job_id = @job_id)
  SET @Max_lvl = (SELECT max_lvl FROM jobs WHERE job_id = @job_id)

  If @job_lvl <@Min_lvl
  BEGIN
    UPDATE employees     -----更新数据信息
    SET job_lvl = @Min_lvl    -----强制纠正
    WHERE emp_id = @emp_id 
  END

  If @job_lvl >@Max_lvl
  BEGIN
    UPDATE employees
    SET job_lvl = @Max_lvl     -----强制纠正
    WHERE emp_id = @emp_id 
  END


INSERT employees     ------新增数据及触发上面的触发器
Values('PPP42628F','aaa','M','bbb',13,1,'0877','1992-1-1')


-- =============================================
-- 使用存在检测功能
-- =============================================

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = 'aaabbb'
	   AND 	  type = 'P')
    DROP PROCEDURE [dbo].[aaabbb]   -----删除存储过程
GO


IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'<trigger_name, sysname, trig_test>' 
	   AND 	  type = 'TR')
    DROP TRIGGER <trigger_name, sysname, trig_test>  ----删除触发器
GO


IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'<procedure_name, sysname, proc_test>' 
	   AND 	  type = 'P')
    DROP PROCEDURE <procedure_name, sysname, proc_test>   -----删除存储过程
GO


IF EXISTS(SELECT name 
	  FROM 	 sysobjects 
	  WHERE  name = N'<table_name, sysname, test_table>' 
	  AND 	 type = 'U')
    DROP TABLE <table_name, sysname, test_table>  -----删除表
GO


USE pubs
IF EXISTS (SELECT name FROM sysobjects 
      WHERE name = 'au_info2' AND type = 'P')
   DROP PROCEDURE au_info2   ----删除存储过程
GO


USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'reminder' AND type = 'TR')
   DROP TRIGGER reminder   ----删除触发器
GO

（11）游标Cursor

------------游标Cursor----------------
/*--------------------------------------------------
---游标---
关系数据库中的操作会对整个行集产生影响。
由 SELECT 语句返回的行集包括所有满足该语句 WHERE 子句中条件的行。
由语句所返回的这一完整的行集被称为结果集。
应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。
这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。 
-------------------------------------------------------*/


---------从表中的部分几行进行查询，即在一个查询结果上再进行查询挑选

---------------------------------------------------------------------------------
--游标基本语法
---------------------------------------------------------------------------------

DECLARE Employee_Cursor CURSOR FOR    ------定义游标Employee_Cursor
	SELECT LastName, FirstName    -----是为了从表中查询这些信息
	FROM Northwind.dbo.Employees   -----查询表

OPEN Employee_Cursor   ----打开游标

FETCH NEXT FROM Employee_Cursor   -----从游标中逐行（下一行）进行取值

WHILE @@FETCH_STATUS = 0   ----如果取值状态正常
BEGIN
   FETCH NEXT FROM Employee_Cursor    -----从游标中逐行（下一行）进行取值
END

CLOSE Employee_Cursor    -----关闭游标，不然会一直存在，直到断开连接
DEALLOCATE Employee_Cursor   ----释放清空资源，进行重新分配

 





---------------------------------------------------------------------------------
--游标读取行的值 
---------------------------------------------------------------------------------
-- Declare the variables to store the values returned by FETCH.
DECLARE @au_lname varchar(40), @au_fname varchar(20)   ----定义全局标量


DECLARE authors_cursor CURSOR FOR
	SELECT au_lname, au_fname FROM authors   ----进行如下查询操作
	WHERE au_lname LIKE 'B%'   ---au_lname中包含B字母的
	ORDER BY au_lname, au_fname

OPEN authors_cursor   ----打开游标

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. 

FETCH NEXT FROM authors_cursor    ----逐行取值
INTO @au_lname, @au_fname

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0   ----取值状态正常
BEGIN

   -- Concatenate and display the current values in the variables.
   PRINT 'Author: ' + @au_fname + ' ' +  @au_lname   ----输出取值结果信息

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM authors_cursor   ----移到下一行取值
   INTO @au_lname, @au_fname   ----把取到的值赋给变量
END

CLOSE authors_cursor   -----关闭游标
DEALLOCATE authors_cursor   ----解除分配
GO



 













/*---------------------------------------------------------------------------------
--查询所有商业书籍在1993年的销售量 输出2列  书名和1993年销售量

商业书                                                                  1993年销售量    
------------------------------------------------------------------       ----------- 
Cooking with Computers: Surreptitious Balance Sheets                             25
Straight Talk About Computers                                                    15
The Busy Executive's Database Guide                                              15
You Can Combat Computer Stress!                                                  35
---------------------------------------------------------------------------------*/
DECLARE authorcursor CURSOR FOR  	--在所有商业书的查询结果上建立游标
	SELECT title_id,title  ----在商业书籍中进行查询
	FROM titles 
	WHERE type = 'business' 
	ORDER BY title

OPEN authorcursor		--打开游标

DECLARE 
   @Mytitle_id varchar(100),	--声明一些变量存储临时结果
   @Mytitle varchar(100),@SumQty int

CREATE TABLE #Tempcursor (	--创建临时表存储临时结果
	[title] [varchar] (80) COLLATE Chinese_PRC_CI_AS NOT NULL ,
        [sumqty] [int]) ON [PRIMARY]

FETCH NEXT FROM authorcursor 	--启动游标读第一条纪录，把纪录的值放入变量
      INTO @Mytitle_id,@mytitle

WHILE @@FETCH_STATUS = 0	--循环读取纪录
BEGIN          			--对每次读取的书的编号，到另外的表中求汇总值
  SET @SumQty = (SELECT Sum(qty) FROM sales WHERE title_id = @MyTitle_id AND ord_date between '1991-1-1' AND '1999-12-31')
 				--把每次算出的一个书的结果插入临时表
  INSERT INTO #tempcursor VAlues(@Mytitle,@SumQty)       --再向下读取另外一本书
  FETCH NEXT FROM authorcursor INTO @Mytitle_id,@mytitle   ----将取到的值给变量赋值
END

CLOSE authorcursor		--关闭游标
DEALLOCATE authorcursor		--释放游标
			--把最后所有中间算出的每本书的结果从临时表中读取出来，返回结果
Select title as '商业书', sumqty as '1993年销售量' from #tempCursor -----显示查询结果信息
drop table #tempcursor		--删除临时表

GO


 


/*---------------------------------------------------------------------------------
--查询所有商业书籍在1993年的销售量 输出2列  书名和1993年销售量

商业书                                                                  1993年销售量    
------------------------------------------------------------------       ----------- 
Cooking with Computers: Surreptitious Balance Sheets                             25
Straight Talk About Computers                                                    15
The Busy Executive's Database Guide                                              15
You Can Combat Computer Stress!                                                  35
---------------------------------------------------------------------------------*/
DECLARE authorcursor CURSOR FOR  	--在所有商业书的查询结果上建立游标
	SELECT title_id,title  ----在商业书籍中进行查询
	FROM titles 
	WHERE type = 'business' 
	ORDER BY title

OPEN authorcursor		--打开游标

DECLARE 
   @Mytitle_id varchar(100),	--声明一些变量存储临时结果
   @Mytitle varchar(100),@SumQty int

CREATE TABLE #Tempcursor (	--创建临时表存储临时结果
	[title] [varchar] (80) COLLATE Chinese_PRC_CI_AS NOT NULL ,
        [sumqty] [int]) ON [PRIMARY]

FETCH NEXT FROM authorcursor 	--启动游标读第一条纪录，把纪录的值放入变量
      INTO @Mytitle_id,@mytitle

WHILE @@FETCH_STATUS = 0	--循环读取纪录
BEGIN          			--对每次读取的书的编号，到另外的表中求汇总值
  SET @SumQty = (SELECT Sum(qty) FROM sales WHERE title_id = @MyTitle_id AND ord_date between '1991-1-1' AND '1999-12-31')
 				--把每次算出的一个书的结果插入临时表
  INSERT INTO #tempcursor VAlues(@Mytitle,@SumQty)       --再向下读取另外一本书
  FETCH NEXT FROM authorcursor INTO @Mytitle_id,@mytitle   ----将取到的值给变量赋值
END

CLOSE authorcursor		--关闭游标
DEALLOCATE authorcursor		--释放游标
			--把最后所有中间算出的每本书的结果从临时表中读取出来，返回结果
Select title as '商业书', sumqty as '1993年销售量' from #tempCursor -----显示查询结果信息
drop table #tempcursor		--删除临时表

GO

二楼后座Tansen

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql中常用操作语句（2）

（1）简单select查询=====SELECT the first=====--------字句 SELECT *------------------字句 FROM *----------1:最简单的例子，SELECT 选择列, FROM 选择表//select 为选择要显示的列信息，select *表示显示所有列，from表示从那张表进行查询SELECT au...
复制链接

扫一扫

专栏目录