(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