第四部分：查询和视图管理

最新推荐文章于 2023-06-20 20:58:58 发布

wei_xin

最新推荐文章于 2023-06-20 20:58:58 发布

阅读量760

点赞数

分类专栏： mssql server 2000 文章标签： insert sql server join null table sql

mssql server 2000 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

第四部分：查询和视图管理

一、 SQL Server查询语句

Select Distinct Top n (Percent) 字段 Into 表 From 表集 Where 条件集合

Group By 分组字段 Having 二次筛选条件

With Rollup/Cube Order By 排序集

注意：

Ø Distinct后可以加字段，也可以加“*”，表示消除完全相同的记录

Ø “Into 表”子句中的目标表必须是数据库里不存在的表，如果存在，则报错

Ø “Rollup”将对Group By的第一个字段进行汇总，“Cube”将对Group By的所有字段进行汇总

二、查询语法

1、使用WHERE 子句查询

select * from <table_name> where <条件>

2、使用ORDER BY 子句查询

ORDER BY 是对查询结果进行排序，ASC是升序，DESC是降序。

例如：SELECT * FROM authors ORDER BY age ASC

3、使用AS子句命名列

SELECT u_name AS 用户名 FROM user

4、使用TOP子句限制查询返回值

SELECT TOP num <column_name> FROM <table_name>

三、集合函数和分组查询

1、聚合函数

（1）SUM：求和

（2）AVG：求平均值

（3）COUNT：统计

（4）MAX：最大值

（5）MIN：最小值

2、使用GROUP BY进行分组

例：统计各用户的发帖数

select username, count (* ) from tb_Spenk group by username

其中，tb_Speak是发贴表，username是用户名

3、使用HAVING子句选择行，HAVING用于筛选分组汇总后的行。

例：统计用户的发帖数大于50帖的用户

select username, count (* ) from tb_Spenk group by username having count (*)>50

其中，tb_Speak是发贴表，username是用户名

4、某条件值为NULL 时，应用IS NULL。

5、模糊查询：LIKE

四、约束的命名规则：

1、主键：PK_表名

2、外键：FK_主表名_从表名

3、检查约束：CK_字段名

4、默认值：DF_表名_字段名

五、SQL Server的集合操作

Union方式

Select SQL Statement

Union (All)

Select SQL Statment

前提条件：

Ø 各SQL语句结果集的字段个数相同，并类型兼容

Ø 各SQL语句有效

结果集特点：

Ø 列名无效，用列序号描述列

Ø 结果集中自动执行DISTINCT动作取消重复

•

六、视图的概念

1、视图

命名的SQL语句，一种从多表取得数据的方法，无数据实体，定义部分存放在MASTER数据库中

优点：

Ø 提供列级的安全保证

Ø 隐藏数据的复杂性

Ø 简化查询语句

Ø 保存复杂的SQL语句

Ø 基表和应用程序的逻辑分离

2、视图分类

Ø NonJoin View

Ø Join View

Ø Partition View

3、视图的执行步骤

Ø 针对视图的查询语句和定义语句合并产生目标SQL语句

Ø 通过SQL JET编译目标SQL后得到二进制信息和执行计划

Ø 对列进行必要的索引替换

Ø 读取数据到Data_Buffer中

4、视图的创建

Create View 视图名With Encryption/schemabinding As SQL Statement With Check Option

为保证向视图中插入的数据能够体现出来，强制要求向视图中插入的数据满足视图定义语句的Where子句条件。

5、视图的创建的注意事项

Ø Master中保存视图的定义

Ø 建视图的用户必须具备创建视图（Create View）和访问视图所引用的基表的访问权限

Ø 视图为非独立实体，必须依赖与其他的实体，其依赖性由SQL Server自动维护

Ø 视图不可以引用临时表(TEMP数据库中，以##开头）

Ø 不能给视图字段绑定规则和默认值

Ø 不能在视图上创建触发器

Ø 不可以给视图创建全文索引，但可以创建一般索引

Ø 一般不建议向视图中插入数据，因为视图中只包含表的部分数据，插入动作可能违反表的约束

6、视图的查询

SysObjects 存放视图的基本信息

SysColumns 存放视图的定义列

SysDepends 存放视图的依赖关系

SysComments 存放视图的定义文本

-----------------------------------------------------------

Sp_HelpText 视图名显示视图的定义语句

Sp_Depends 视图名显示视图的依赖关系

7、视图的删除

Drop View 视图名

第四部分习题样例

/*普通会员

M_Code M_Name M_IDCard M_Tel */

use T607

create table Member

(

M_Code varchar(10) constraint pk_member_code primary key

on G_Index,

M_Name varchar(10) not null,

M_IDCard varchar(18) not null constraint uk_member_idcard unique,

M_Tel varchar(13)

)

on G_Normal

drop table Member

VIP

V_Code V_Name V_IDCard V_Telephone V_Address

create table Vip

(

V_Code varchar(10) constraint pk_vip_code primary key

on G_Index,

V_Name varchar(10) not null,

V_IDCard varchar(18) not null constraint uk_vip_idcard unique,

V_Telephone varchar(13),

V_Address varchar(30)

)

on G_Normal

书籍

编号书名作者类型单价

create table Book

(

B_Code varchar(15) constraint pk_book_code primary key

on G_Index,

B_Name varchar(30) not null,

B_Author varchar(10),

B_Type varchar(10),

B_Price float

)

on G_Change

/*罚单

罚单号记录号会员号罚款金额*/

create table Amerce

(

A_Code varchar(10) constraint pk_amerce_code primary key

on G_Index,

A_NoteCode varchar(10) not null,

A_MemberCode varchar(10) not null,

A_Money smallmoney

)

on G_Change

记录

记录号会员号书籍编号借书日期还书日期

create table Note

(

N_Code varchar(10) constraint pk_note_code primary key

on G_Index,

N_MemberCode varchar(10) not null,

N_BookCode varchar(15) not null,

N_BorrowDate smalldatetime not null,

N_ReturnDate smalldatetime

)

on G_Change

1、对书籍进行类别统计，并同时计算出每个类别书目的总数；*/

insert into Book values('b1','oracle','谭浩强','计算机',53)

insert into Book values('b2','sql server','谭浩强','计算机',42)

insert into Book values('b3','java','谭浩强','计算机',48)

insert into Book values('b4','一个好人','小也','文学',25)

select B_Type as 类别,count(*) as 总数 from Book group by B_Type

/*2、找出借过”oracle”这本书的所有会员的联系电话？*/

insert into Member values('M001','kenny','123456789123456789','0592')

insert into Vip values('V001','jacky','987654321987654321','0592','xm')

insert into Note values('N001','M001','b1','2006-02-06','2006-05-26')

insert into Note values('N002','V001','b1','2006-05-06','2006-07-26')

insert into Note values('N003','V001','b1','2006-07-06','2006-08-26')

insert into Note values('N005','V001','b1','2006-07-06','2006-08-26')

insert into Note values('N004','V001','b4','2006-07-06','2006-08-26')

insert into Note values('N013','V001','b1','2006-01-06','2006-08-26')

insert into Note values('N006','V001','b1','2006-12-06','2007-08-26')

insert into Note values('N007','V001','b1','2006-03-06','2006-08-26')

insert into Note values('N008','V001','b1','2006-04-06','2006-08-26')

insert into Note values('N009','V001','b1','2006-06-06','2006-08-26')

insert into Note values('N010','V001','b1','2006-08-06','2006-08-26')

insert into Note values('N011','V001','b1','2006-10-06','2007-08-26')

insert into Note values('N012','V001','b1','2006-11-06','2007-08-26')

insert into Note values('N014','V001','b1','2006-09-06','2007-08-26')

select distinct Member.M_Name as 姓名,Member.M_Tel as 电话 from (Member

inner join Note on Member.M_Code=Note.N_MemberCode)

inner join Book on Note.N_BookCode=Book.B_Code

where Book.B_Name='oracle'

union all

select distinct Vip.V_Name,Vip.V_Telephone from(Vip

inner join Note on Vip.V_Code=Note.N_MemberCode)

inner join Book on Note.N_BookCode=Book.B_Code

where Book.B_Name='oracle'

/*3、查看在7月份作者“谭浩强“没被人借过的书有哪些？*/

select B_Name as 书名,B_Author as 作者 from Book

left join Note on Book.B_Code=Note.N_BookCode and month(N_BorrowDate)=7

where B_Author='谭浩强' and N_Code is null

/*4、找出借书册数<50本的普通会员资料*/

select * from Member inner join

(select N_MemberCode,count(N_Code) as CountNumber from Note group by N_MemberCode) as BorrowBook

on Member.M_Code=BorrowBook.N_MemberCode

where BorrowBook.CountNumber<50

/*5、会员每次借书的平均时间是多少天? (借了没还的不计算)*/

select sum(datediff(day,N_BorrowDate,N_ReturnDate))/count(N_Code) as 平均天数

from Note where N_ReturnDate is not null

/*6、查看每个月份每个作者的书被借的情况*/

select BorrowDate as 月份,B_Author as 作者,count(*) as 借阅总数 from

(select Book.B_Author,Book.B_Code,Month(N_BorrowDate) as BorrowDate

from Book inner join Note on Book.B_Code=Note.N_BookCode) as B

group by B.BorrowDate,B.B_Author

/*7、备份书籍表中价格大于40的所有书的书名、作者和价格并注明备份日期。*/

select B_Name,B_Author,B_Price,getdate() as BackupDate into BookBackup

from Book where B_Price>40

select * from BookBackup

/*8、每个月都来借书的那些人最后一次借的最多的书是哪一本?*/

select * from Book where B_Code=(

select top 1 N_BookCode

from Note inner join

(select N_MemberCode,max(N_BorrowDate) as mtime from Note

where N_MemberCode in

(select N_MemberCode from

(select distinct cast(year(N_BorrowDate) as varchar(4))+cast(month(N_BorrowDate) as varchar(2)) as month_list,N_MemberCode

from Note) as B

group by N_MemberCode

having count(N_MemberCode)=(select datediff(month,min(N_BorrowDate),max(N_BorrowDate)) as monthcount from Note))

group by N_MemberCode) as x

on Note.N_MemberCode=x.N_MemberCode and Note.N_BorrowDate=x.mtime

group by N_BookCode

order by count(*) desc)

/*9、查看会员”V001”平均多少天来借一次书?*/

select N_MemberCode as 会员编号,datediff(day,min(N_BorrowDate),max(N_BorrowDate))/count(N_Code) as 平均借书天数

from Note

group by N_MemberCode having(N_MemberCode='V001')

/*10、找出借出的书籍数量相差超过50的月份有几对?*/

select * from Note

select count(A.月份) as 月份总对数 from

((select month(N_BorrowDate) as 月份,count(N_Code) as countMonth from Note group by month(N_BorrowDate)) as A

left join

(select month(N_BorrowDate) as 月份,count(N_Code) as countMonth from Note group by month(N_BorrowDate)) as B

on A.月份<>B.月份 and B.月份 is not null)

where (A.countMonth-B.countMonth>=50) or (B.countMonth-A.countMonth>=50)

/*11、最后一次是借计算机书的人有哪些*/

select A.N_MemberCode,max(A.N_BorrowDate) as LastTime from

(select N_MemberCode,N_BorrowDate from Book,Note

where Note.N_BookCode=Book.B_Code and B_Type='计算机') as A

group by A.N_MemberCode

/*12、查询3月借书量超过50册的所有VIP和普通会员资料*/

select * from Member

select * from Vip

select M_Code as 会员号,M_Name as 姓名,M_IDCard as 身份证号,M_Tel as 电话 from Member

inner join

(select N_MemberCode,count(N_Code) as Num from Note where Month(N_BorrowDate)=3 group by N_MemberCode)

as A

on Member.M_Code=A.N_MemberCode

where A.Num>=50

union

select V_Code,V_Name,V_IDCard,V_Telephone from Vip

inner join

(select N_MemberCode,count(N_Code) as Num from Note where Month(N_BorrowDate)=3 group by N_MemberCode)

as B

on Vip.V_Code=B.N_MemberCode

where B.Num>=50

wei_xin

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
第四部分：查询和视图管理

第四部分：查询和视图管理一、 SQL Server查询语句Select Distinct Top n (Percent) 字段 Into 表 From 表集 Where 条件集合 Group By 分组字段 Having 二次筛选条件 With Rollup/Cube Order By 排序集注意：Ø Distinct后可以加字段，也可以加“*”，表示消除完全相同
复制链接

扫一扫

专栏目录