当前位置：文档库 › 数据库代码

数据库代码

(1) SELECT语句的基本使用
1) 查询图书表books中的每本图书的所有数据。
select * from books
2) 分别查询books表和BookType 表的全部信息。
select* from books,booktype
3) 在图书books 表中查询每本图书的书名和销售价格。
select bookname,saleprice from books
4) 在books 表中查询记录编号为“YBZT0003 ”的图书的书名和销售价格。
select bookname,saleprice from books where Id = 'YBZT0003'
5) 在 books 表中查询销售价格为 20 元的图书记录编号、书名和进货价格。
使用 AS 子句将结果中指定目标列标题分别指定为图书记录编号、图书名和进货价格。
select Id as 图书记录编号, bookname as 图书名, storeprice as 进货价格 from books where saleprice=20
6) 在books 表中查询出销售价格在 10～50 元之间的图书的书名和销售价格，
分别取别名为图书的书名和图书的销售价格。
select bookname as 图书的书名,saleprice as 图书的销售价格 from books where saleprice>10 and saleprice<50
7) 在books表中查询出书名中有一个“中”字的所有图书。
select * from books where bookname like '%中%'
8) 在books表中查询出版社有一个“人”字并且销售价格在 10～150 元之间的所有图书。
select * from books where bookname like '%人%' and saleprice>10 and saleprice<50

(2) 子查询的使用
1) 计算机类图书的平均销售价格。
select avg(saleprice) from books where typeid =
(select TypeId from BookType where typename='计算机')

2) 查询销售价格高于所有图书平均销售价格的图书记录编号，书名及出版社。
select Id,BookName,publisher from books where saleprice>
(select avg(saleprice) from books)

(3) 连接查询的使用 [?]
1) 查询图书的书名、销售价格和图书分类名称。
SELECT bookname,saleprice,BOOKS.TypeId FROM books,booktype
WHERE books.TypeId = booktype.TypeId

2) 查询订购时间在2010-8-25订购图书的用户姓名、地址、移动电话，所定图书的书名、作者，以及订购数量。
select username,address,mobile,bookname,author,OrderCount from users,books,basket
where https://www.wendangku.net/doc/3c16323381.html,ERID=https://www.wendangku.net/doc/3c16323381.html,ERID AND BOOKS.ID=BASKET.BOOKID
AND OrderTime='2010-8-25'

(4) 数据汇总
1) 在books表中查询“清华大学出版社”所出书的平均销售价格。
select avg(saleprice) from Books where publisher='清华大学出版社'

2) 查询books 表中一共有多少本书。
select COUNT(Id) from books

(5) GROUP BY、ORDER BY 子句的使用
1) 按出版社统计各个出版社所出书的平均销售价格。
SELECT AVG(SALEPRICE) FROM BOOKS GROUP BY PUBLISHER

2) 统计books 表中每个出版社一共有多少本书。[?]
SELECT COUNT(Id) FROM BOOKS GROUP BY PUBLISHER

(1) 使用局部变量、全局变量
1）定义及使用局部变量
① 定义一个tinyint的整型变量，并

分别给其赋值45、345，观察其执行结果。
DECLARE @local_tinyint_tinyint tinyint
SELECT @local_tinyint_tinyint=45
SELECT @local_tinyint_tinyint
SET @local_tinyint_tinyint =345
SELECT @local_tinyint_tinyint
GO

② 定义一个长度为9的可变长型字符变量，并分别给其赋值“Welcome to Beijing”及“Beijing”，
观察其执行结果。

DECLARE @local_variable_char nvarchar(9)
SET @local_variable_char =' Welcom to beijing'
SELECT @local_variable_char
SET @local_variable_char ='beijing'
SELECT @local_variable_char

GO

2）使用全局变量
① 返回当前SQL Server服务器的版本。

DECLARE @@local_variable_char nvarchar(9)
SET @@local_variable_char =' Welcom to beijing'
SELECT @@local_variable_char
GO

② 返回当天试图连接到本服务器的连接的数目。

DECLARE @@local_connections_char nvarchar(9)
SET @@local_connections_char =' Welcom to beijing'
SELECT @@local_connections_char
GO

（2）函数的使用
1）数学函数的使用
分别用函数求出-2、1.2的绝对值，16的平方根及5的平方。
SELECT ABS(-2)
SELECT ABS(1.2)
SELECT SQRT(16)
SELECT SQUARE(5)
2）字符串函数的使用
① 返回字符串“Abcd”第一个字符的ASCII码值。
SELECT ASCII('Abcd')
② 分别将字符串“china”、“MACHINE”转换成大写、小写字母。
SELECT UPPER('china')
SELECT LOWER('MACHINE')
③ 使用RTRIM和LTRIM函数分别去掉字符串“机械工业”右边、左边及左右两边的空格，再与“中国”及“出版社”连接起来。
SELECT'中国'+RTRIM('机械工业')
SELECT LTRIM(' 机械工业')+'出版社'
SELECT '中国'+LTRIM(RTRIM('机械工业'))+'出版社'

④ 使用SUBSTRING函数从字符串“中国机械工业出版社”中返回字符串“机械”，并使用REVERSE函数将字符串“机械”逆序返回。
SELECT REVERSE( SUBSTRING ('中国机械工业出版社',3,2))
⑤ 将字符串“abcdabcd”中的所有字符“a”换为字符“%”。
SELECT REPLACE('abcdabcd','a','%')
3）日期、时间函数的使用
① 使用getdate( )函数返回系统当前日期及当前日期的年份、月份及日期。
SELECT GETDATE(), YEAR(GETDATE()) , MONTH(GETDATE()) ,DAY(GETDATE())
② 使用datediff函数返回某个给定日期与当前系统日期相差的天数。
SELECT DATEDIFF(DAY,'2010-07-10',GETDATE())
4）系统函数的使用
① 使用系统函数user_name( )、db_name( )显示正在使用的用户名、数据库名。
SELECT USER_NAME(),DB_NAME()
② 用全局变量返回数据库服务器名称。
5）集合函数的使用
求出BookShop数据库中Books表中所有书销售价格的平均价格、最高价格、最低价格。
SELECT AVG(SALEPRICE),MAX(SALEPRICE),MIN(SALEPRICE) FROM BOOKS

（3）流程控制语句的使用
1）PRINT语句。
用PRINT语句在屏幕上显示“I AM A STUDENT”.
PRINT 'I AM A STUDENT'
2）用IF语句判断。
如果BookSh

op库中Books表中存在图书类型为“计算机类”的书，就显示“库中存在该类型图书”，否则显示“库中不存在该类型图书”。

IF EXISTS (SELECT BOOKS.* FROM books,BOOKTYPE
WHERE BOOKS.TYPEID=BOOKTYPE.TYPEID
AND TPYENAME='计算机类' )
PRINT '库中存在该类型图书'
else
PRINT'库中不存在该类型图书'

3）使用CASE语句。
① 从BookShop库Books表中，选取书名、出版社，
如果出版社为“清华大学出版社”则输出“中国最有实力的出版社之一”，
如果是“海南出版社”，则输出“海外影响力最强的出版社之一”。
SELECT BOOKS.BOOKNAME,'PUBLISHER' =
CASE PUBLISHER
WHEN '清华大学出版社'THEN '中国最有实力的出版社之一'
WHEN '海南出版社'THEN '海外影响力最强的出版社之一'
ELSE '无出版社'
END
FROM BOOKS
②从BookShop库Books表中查询所有书的销售价格情况，凡销售价格为空的输出“未录入销售价格”，
小于30元输出“价格适中”，30～100元输出“价格偏高”，100～300元输出“价格高”，300以上的输出“价格特别高”。[?]
select BOOKS.'SALEPRICE'=
case

when price < 30 then '价格适中'
when price >= 30 AND PRICE<= 100 then '价格偏高'
WHEN PRICE>100 AND PRICE<=300 THEN '价格高'
WHEN PRICE>300 THEN '价格特别高'

else '未录入销售价格'
end
from Books

4）设置在2分钟后进行一次查询操作。
begin
waitfor delay '00:02'
select * from BOOKS
end

5）用WHILE语句求1~100的和。
DECLARE @SUM INT,@COUNT INT
SELECT @SUM=0,@COUNT=1
WHILE @COUNT<=100
BEGIN
SET @SUM=@SUM+@COUNT
SET @COUNT=@COUNT+1
END
PRINT @SUM

第三章
(1) 通过对象资源管理器创建索引
1）创建索引
① 用对象资源管理器为表Users创建一个索引名为User_index的唯一性非聚簇索引，索引关键字为Username，升序，填充因子85%。
CREATE UNIQUE NONCLUSTERED INDEX User_index
ON Users( Userid ASC )
WITH
FILLFACTOR = 85

② 用对象资源管理器为表Books创建一个名为IX_PP的非聚簇复合索引，索引关键字为Publisher、SalePrice，升序，填充因子65%。
CREATE UNIQUE NONCLUSTERED INDEX IX_PP
ON Books( Publisher,SalePrice ASC )
WITH
FILLFACTOR = 65

③ 用对象资源管理器为表Board创建一个基于提交人和提交时间的索引IX_ Poster_t，其中提交人按降序排列，当提交人相同时，按提交时间升序排列。
CREATE UNIQUE NONCLUSTERED INDEX IX_Poster_tt
ON Board ( Poster desc,PostTime ASC )
WITH
FILLFACTOR = 65

2）重命名索引
用对象资源管理器将Users表索引文件User_index更名为User1_index。
sp_rename 'https://www.wendangku.net/doc/3c16323381.html,er_index','User1_index'

3）用对象资源管理器将Board表的索引文件IX_ Poster_t删除。
DROP INDEX Board.I

X_ Poster_tt

(2) 使用T-SQL语句创建、管理索引文件
1）创建索引
① 用T-SQL语句为表books创建一个索引名为book_index的唯一性非聚簇索引，索引关键字为bookname，升序，填充因子85%。
create UNIQUE NONCLUSTERED index book_index
ON Books ( Bookname ASC)
WITH
FILLFACTOR = 85

② 用T_SQL语句为表Basket创建一个名为IX_UB的非聚簇复合索引，索引关键字为UserId、BookId，升序，填充因子65%。
create UNIQUE NONCLUSTERED index IX_UB
ON Basket ( UserId ASC,BookId ASC)
WITH
FILLFACTOR = 65

③ 为表Complain创建一个基于订单号和投诉时间的索引IX_ord_t，其中订单号按降序排列，当订单号相同时，按投诉时间升序排列。
create UNIQUE NONCLUSTERED index IX_ord_t
ON Complain ( OrderNumber DESC,PostTime ASC)
WITH
FILLFACTOR = 65

2）重命名索引
将books表索引文件book_index更名为book1_index。
sp_rename 'Books.book_index','book1_index'
3）将Basket表的索引文件IX_UB删除。
drop index Basket.IX_UB

(1) 创建视图
1) 用对象资源管理器创建一个显示用户购买记录的视图(order_view)，
要求显示用户姓名、地址、移动电话、图书名称、销售价格、订购数量、提交时间。

2) 将books 表中所有出版社的记录定义为一个视图(printer_info_view)，
在“查询”窗口中输入并执行语句，并在“对象资源管理器”面板中显示其结果。
CREATE VIEW printer_info_view
AS
SELECT PUBLISHER FROM BOOKS
3) 对Users表定义一个能反映用户姓名、地址和移动电话的视图(users_info_view)，
在“查询”窗口中输入并执行语句，并在“对象资源管理器”面板中显示其结果。
CREATE VIEW USERS_INFO_VIEW AS SELECT UserName, Address, Mobile FROM USERS
(2) 使用视图
1) 将视图printer_info_view进行加密，然后在“查询”窗口中进行查询该视图。
ALTER VIEW printer_info_view WITH ENCRYPTION
AS
SELECT https://www.wendangku.net/doc/3c16323381.html,erName, https://www.wendangku.net/doc/3c16323381.html,ers.Address, https://www.wendangku.net/doc/3c16323381.html,ers.Mobile, dbo.Books.BookName, dbo.Books.SalePrice, dbo.Basket.OrderCount,
dbo.Basket.PostTime
FROM dbo.Basket INNER JOIN
dbo.Books ON dbo.Basket.Id = dbo.Books.Id INNER JOIN
https://www.wendangku.net/doc/3c16323381.html,ers ON https://www.wendangku.net/doc/3c16323381.html,erId = https://www.wendangku.net/doc/3c16323381.html,erId

2) 显示视图users_info_view的信息。
EXEC sp_helptext printer_info_view
3) 将视图order_view用对象资源管理器删除，视图printer_info_view用 SQL 语句删除。
DROP VIEW PRINTER_INFO_VIEW
4) 向视图users_info_view修改(UPDATE)和删除(DELETE)操作。
修改的数据为把用户姓名“张伟”的地址改为“中国大连”。删除的数据为把用户姓名为“刘冰”的用户删除。
UPDATE USERS_INFO_VIEW
SET ADDRESS='中国大连'
WHERE USERNAME='张伟'

DELETE FROM USERS_INFO_VIEW
WHERE USERNAME='刘冰'

3.2.3

存储过程
1）在bookShop 数据库中创建一个名为p_SalePrice_books 的存储过程。
该存储过程返回books 表中所有销售价格在 500～1000 的图书的记录。
并执行已经定义的存储过程SalePrice_books。
CREATE PROCEDURE p_SalePrice_books
AS SELECT * FROM BOOKS
WHERE SALEPRICE>500 AND SALEPRICE<1000

2）创建存储过程p_books_price，要求能根据用户给定的出版社，
统计该出版社的出书的平均销售价格，并将平均销售价格以输出变量返回给用户。
并执行已经定义的存储过程。
CREATE PROCEDURE p_books_price
@PUBLISHER VARCHAR(50),
@avgPRICE FLOAT=0 OUTPUT
AS
SELECT @AVGPRICE= AVG(SALEPRICE) FROM BOOKS
WHERE PUBLISHER=@PUBLISHER
PRINT @AVGPRICE
EXEC p_SalePrice_books '清华大学出版社'
3）查看存储过程p_SalePrice_books的定义。
sp_help 'p_SalePrice_books'
4）删除存储过程p_books_price。
DROP PROCEDURE p_books_price

3.2.4触发器
1）使用CREATE TRIGGER 命令创建一个触发器books_tril，
当向books 表中插入一条记录时，自动显示信息“数据插入成功”。
在查询分析器中输入触发器的代码并执行。触发器建立完毕后，
当向books表中插入一条记录后，观察窗口下方的消息变化。
CREATE TRIGGER BOOKS_TRIL ON BOOKS FOR INSERT
AS
PRINT '数据插入成功'

DECLARE @NOTE CHAR(50)
SELECT @NOTE='数据插入成功'FROM INSERTED
SELECT*FROM BOOKS
INSERT INTO BOOKTYPE
VALUES('1','JSJL')
INSERT INTO BOOKS (ID,TYPEID,STOREPRICE)
VALUES('1','1',36)

2）禁用或删除刚才创建的books_tri1 触发器。
DROP TRIGGER books_tril

3）使用CREATE TRIGGER 命令创建一个触发器books_insert_tril，
当向book2 表中插入一条记录时，自动显示 books 表中的记录.
可参考本题的代码和步骤运行操作。插入一条数据，并观察结果。
CREATE TRIGGER BOOKS_INSERT_TRIL ON BOOKS FOR INSERT
AS
4）使用系统存储过程 sp_helptext 查看触发器books_insert_tril 的定义文本信息。