transact---sql高级查询（上）
1:多表的查询和笛卡儿积
2:表格别名的用法
3:使用sql server的统计函数
4:用group by子句实现分组的查询
A:多表查询和笛尔儿积
到目前为止,我们所用的都是单个表查询,但是在更多的情况的下,需要对多个表进行同时查询,这时可以把多个表的名字全部填写在from子句中.
比如:查询出每个职工的姓名,学历,所在部门名称.由于我们需要的结果来自于两个表,所以必须用多表查询
select 姓名,学历,部门名称,负责人 from work,部门 [分析为什么是错误的]
原因:问题出在对表格连接条件的限制上.在上面的语句中,没能对表格连接条件作任何限制,所以sql会在work表中每取出一条记录,就与部门表中的所有记录组合一次,那么假设work表有m条记录,而部门表中有n条记录,则得出的结果为m*n条记录这就是笛尔儿积,所以笛尔儿积返回的大多数的结果是冗余的、无用的,所以应该避免笛尔儿积的产生.
解决笛尔儿积的方法:事实上由于笛尔儿积是因为两个表的连接条件没有限制造成的,所以只要我们对两个表的连接进行条件限制,就可以避免笛尔儿积的产生.可以通过一个where子句,来连接两个表的公共的字段就可以了.
所以将上面的语句改成:select 姓名,学历,部门名称,负责人 from work,部门 where work.部门编号=部门.部门编号
B:使用表格的别名
A:当使用多个表进行查询时,如果有两个表中有相同的列,应该指明选中的是哪个表中的列.
比如:在work表检索出在address表中都有的职工的职工号,姓名,学历,基本工资
select 职工号,姓名,学历,基本工资 from work,address where work.职工号=address.职工号
上面的语句是错误的,原因是对于work和address表都有职工号,姓名列,所以应该指明是哪个表的职工号和姓名.
改成:select work.职工号,work.姓名,学历,基本工资 from work,address where work.职工号=address.职工号
或者:select address.职工号,address.姓名,学历,基本工资 from work,address where work.职工号=address.职工号
想一想:为什么对于学历,基本工资没有指明表名:即:work.学历,work.基本工资[只有一个表有这些列]
B:允许使用别名来访问表.
格式:1:表名 as 别名
2:表名 别名
例如:上面的语句可改写成:
select w.职工号,w.姓名,学历,基本工资 from work as w,address as a where w.职工号=a.职工号
上面的语句中在from中引用两个表,并且为表work指明了别名w,为表address指明了别名a,所以就可以用w来代表work表,用a来代表address表.或者省略as直接改成:select w.职工号,w.姓名,学历,基本工资 from work w,address a where w.职工号=a.职工号
C:如果使用了别名,则以后所有查询语句中,都必须使用别名列
比如:select work.职工号,work.姓名,学历,基本工资 from work w,address a where w.职工号=a.职工号 [是错误的]
C:使用统计函数:
sql跟我们提供了以下几个统计函数:
sum:返回一个数字列的总和
avg:对一个数字列求平均值
min:对一个数字列求最小值
max:对一个数字列求最大值
count:返回满足select语句中指定的条件的记录个数
举列:1:求出work表中所有男职工的基本工资的和
select sum(基本工资) as 性别为男的基本工资 from work where 性别=\'男\'
2:求出work表中所有职称是经理的最高工资和最低工资,平均工资
select max(基本工资) as 最高工资,min(基本工资) as 最低工资,avg(平均工资) as 平均工资 from work
3:与统计函数一起使用distinct关键字[通常只与count函数使用]
例:1:检索出work表中学历的个数
select count(学历) from work
2:检索出work表中学历的种类的个数
select count(distinct 学历) from work
试一试:select distinct count(学历) from work 可行否?
3:有work和部门表,检索出在销售部工作的员工的个数
select \'销售部的人数\'=count(职工号) from work a,部门 b
where a.部门编号=b.部门编号 and b.部门名称=\'销售部\'
4:在work表中检索出其基本工资小于职工平均工资的人数
select count(职工号) as 人数 from work
where 基本工资<(select avg(基本工资) from work)
5:有学科表和学费表,从学费表检索出有多少个学网页设计的人
select count(学号) as 网页设计的人数 from 学费 a,学科 b
where a.所学专业代号=b.课程编号 and b.课程名称=\'网页设计\'
D:使用group by子句对结果进行分类[只用于统计函数]
举列:1:检索出work表各职称的人数.
select 职称,count(职称) as 职称人数 from work group by 职称
2:检索出各学历的平均工资.
select 学历,avg(基本工资) from work group by 学历
3:有学科表和学费表,要求统计出各学科的学生数目.
select 所学专业代号,count(所学专业代号) as 人数 into #abc from 学费 group by 所学专业代号
select 课程名称,人数 from #abc,学科 where 所学专业代号=课程编号
4:有职工表和商品销售表,要求检索出每个职工的职工号,姓名,销售总量.
select 职工号,sum(销售量) as 销售总量 into #abcd from 商品销售 group by 职工号
select 职工.职工号,姓名,销售总量 from 职工,#abcd where #abcd.职工号=职工.职工号
5:查询出每个部门最高的基本工资,显示部门名称和最高基本工资
select 部门名称,max(基本工资) from work group by 部门名称
说明:1:在group by中不支持对列名的分配的别名
select 学历 as 职工学历,count(学历) from work group by 职工学历 [错错]
改为:select 学历 as 职工学历,count(学历) from work group by 学历
2:select后面每一列数据除了在统计函数中的列以外都必须在group by子句出现
比如:select 学历,性别,sum(基本工资) from work group by 学历[错错]
改为:select 学历,性别,sum(基本工资) from work group by 学历,性别
意义:各学历各性别的基本工资之和
transact---sql高级查询（下）
5:使用having关键字来筛选结果
6:使用compute和compute by子句
7:使用嵌套查询
8:分布式查询
E:使用having关键字来筛选结果
当完成对数据结果的查询和统计后,可以使用having关键字来对查询和计算的结果进行一步的筛选
例:检索出work表中学历是大专或者是中专的人数
select 学历,count(学历) from work group by 学历 having 学历 in(\'大专\',\'中专\')
说明:1:having关键字都与group by用在一起.
2:having不支持对列分配的别名
例如:select 学历,\'大于5的人数\'=count(学历) from work group by 学历 having 大于5的人数>5 [错错]
改为:select 学历,\'大于5的人数\'=count(学历) from work group by 学历 having count(学历)>5
F:使用compute和compute by
使用compute子句允许同时观察查询所得到各列的数据的细节以及统计各列数据所产生的汇总列
select * from work [查询所得到的各列的数据的细节]
compute max(基本工资),min(基本工资) [统计之后的结果]
这个例子中没有使用by关键字,返回的结果是最后添加了一行基本工资的最大值和最小值,也可增加by关键字.
例:select * from work order by 学历
compute max(基本工资),min(基本工资) by 学历
比较:select 学历,max(基本工资),min(基本工资) from work group by 学历
说明:1:compute子句必须与order by子句用在一起
2:compute子句可以返回多种结果集.一种是体现数据细节的数据集,可以按分类要求进行正确的分类；另一种在分类的基础上进行汇总产生结果.
3:而group by子句对每一类数据分类之后只能产生一个结果,不能知道细节
G:使用嵌套查询
查询中再查询,通常是以一个查询作为条件来供另一个查询使用
例:有work表和部门表
A:检索出在部门表中登记的所有部门的职工基本资料
select * from work where 部门编号 in [not in](select 部门编号 from dbo.部门)
B:检索出在work表中每一个部门的最高基本工资的职工资料
select * from work a where 基本工资=(select max(基本工资) from work b where a.部门名称=b.部门名称)
说明:由外查询提供一个部门名称给内查询,内查询利用这个部门名称找到该部门的最高基本工资,然后外查询根据基本工资判断是否等于最高工资,如果是的,则显示出来.
相当于:select * from work,(select 部门名称,max(基本工资) as 基本工资 from work group by 部门名称 as t) where work.基本工资=t.基本工资 and work.部门名称=t.部门名称
C:用嵌套work表和嵌套部门表,在嵌套work表中检索出姓名和职工号都在嵌套部门存在的职工资料
select * from 嵌套work where 职工号 in (select 职工号 from 嵌套部门) and 姓名 in (select 姓名 from 嵌套部门) [察看结果,分析原因]
改:select * from 嵌套work a,嵌套部门 b where a.职工号=b.职工号 and a.姓名=b.姓名
改:select * from 嵌套work where 职工号=(select 职工号 from 嵌套部门) and 姓名=(select 姓名 from 嵌套部门) [行吗?为什么,分析原因?]
在嵌套中使用exists关键字[存在]
例:1:用嵌套work表和嵌套部门表,在嵌套work表中检索出姓名和职工号都在嵌套部门存在的职工资料
select * from 嵌套work a where exists (select * from 嵌套部门 b where a.姓名=b.姓名 and a.职工号=b.职工号)
2:在work表检索出在部门表没有的职工
select * from work where not exists (select * from 部门 where 部门.部门编号=work.部门编号)
能否改成:select * from work where exists (select * from 部门 where 部门.部门编号<>work.部门编号)
在列清单中使用select
例:1:在work1表和部门表中检索出所有部门的部门名称和基本工资总和
select 部门名称,(select sum(基本工资) from work1 b where a.部门编号=b.部门编号) from 部门 a
2:检索各部门的职工人数
select 部门编号,部门名称,(select count(职工号) from work1 a where a.部门编号=b.部门编号) as 人数 from 部门 b
3:在商品表和销售表中查询每一职工的姓名,所属部门,销售总量
select 姓名,所属部门,(select sum(销售量) from 商品销售 a where a.职工号=b.职工号) as 销售总量 from 嵌套部门 b
H:分布式查询
我们以前的查询都只是基于一个服务器中的一个数据库的查询,如果一个查询是要跨越一个服务器,像这样的查询就是分布式查询,那么我们以看到分布查询就是数据源自于两个服务器.要进行分布式查询必须先创建一个“链接服务器”,以便让本地的用户能够映射到过程服务器.
“链接服务器”的创立
A:在“链接服务器”里面输入以后为了方便访问该链接服务器的名称[任意]
B:在“提供程序名称”里面选择“Microsoft OLE DB Provider for SQL Server”
C:在“数据源”里面输入服务器的网络名
D:本地登录,远程用户和远程密码里面分别输入一个本地登录用户,远程登录和远程密码以便让本地SQL Server登录映射为链接服务器上的用户
E:访问方法:格式:链接服务器的名称.数据库名.dbo.表名
链接服务器有两个特点:
1:通过链接服务器不能删除链接源服务器的任何对像.
2:能过链接服务器可以对链接源服务器的表进行insert,updae,delete操作.
视图
1:什么是视图
2:视图和查询的区别
3:视图的优点
4:如何创建和管理视图
5:如何通过视图修改基本表的数据
6:如何通过视图实现数据的安全性
A:什么是视图:
视图(view):从一个或几个基本表中根据用户需要而做成一个虚表
1:视图是虚表,它在存储时只存储视图的定义,而没有存储对应的数据
2:视图只在刚刚打开的一瞬间,通过定义从基表中搜集数据,并展现给用户
B:视图与查询的区别:
视图和查询都是用由sql语句组成,这是他们相同的地方,但是视图和查询有着本质区别:
它们的区别在于:1:存储上的区别:视图存储为数据库设计的一部分,而查询则不是.
2:更新限制的要求不一样
要注意:因为视图来自于表,所以通过视图可以间接对表进行更新,我们也可以通过update语句对表进行更新,但是对视图和查询更新限制是不同的,以下我们会知道虽然通过视图可以间接更新表但是有很多限制.
3:排序结果:通过sql语句,可以对一个表进行排序,而视图则不行.
比如:创建一个含有order by子句的视图,看一下可以成功吗?
C:视图的优点:
为什么有了表还要引入视图呢？这是因为视图具有以下几个优点:
1:能分割数据,简化观点
可以通过select和where来定义视图,从而可以分割数据基表中某些对于用户不关心的数据,使用户把注意力集中到所关心的数据列.进一步简化浏览数据工作.
2:为数据提供一定的逻辑独立性
如果为某一个基表定义一个视图,即使以后基本表的内容的发生改变了也不会影响“视图定义”所得到的数据
3:提供自动的安全保护功能
视图能像基本表一样授予或撤消访问许可权.
4:视图可以间接对表进行更新,因此视图的更新就是表的更新
D:视图的创建和管理
视图的创建
1:通过sql语句
格式:create view 视图名 as select 语句
试一试:分别创建关于一个表或多个表的视图[因为视图可以来自于多表]
2:通过企业管理器
说明:1:在完成视图的创立之后,就可以像使用基本表一样来使用视图
2:在创建视图时,并非所有的select子查询都可用
如:compute和compute by,order by[除非与top一起连用]
3:但在查询时,依然都可以用在创建时禁用的select子查询
4:在视图创建时,必须为没有标题列指定标题[思考:能否不用select语句来创建一个视图]
视图的删除:
1:通过sql语句:drop view 视图名
2:通过企业管理器
说明:与删除表不同的是,删除视图后只是删除了视图了定义,并没有删除表中的数据.[查看相关性]
修改视图的定义
1:通过企业管理器
2:通过sql语句:
格式:alter view 视图名 as 新的select语句
浏览视图信息 sp_helptext 视图名 [查看视图创建的语句]
E:如何通过视图修改基本表的数据.
1:在视图上使用insert语句
通过视图插入数据与直接在表中插入数据一样,但视图毕竟不是基本表.因此在进行数据插入时还是有一定的限制
1:如果视图上没有包括基本表中属性为not null[不能为空]的列,那么插入操作会因为那些列是null值而失败.
2:如果某些列因为某些规则或约束的限制而不能直接接受从视图插入的列时,插入会失败
3:如果在视图中包含了使用统计函数的结果,或是包含计算列,则插入操作会失败
4:不能在使用了distinct语句的视图中插入值
5:不能在使用了group by语句的视图中插入值
2:使用update更新视图中的数据
1:更新视图与更新表格一样,但是在视图中使用了多个基本表连接的情况下,每次更新操作只能更新来自基本表的一个数据列
例如:创建以下视图:create view del as
select 职工号,姓名,部门名称,负责人 from work1,部门
where work1.部门编号=部门.部门编号
如果再执行下面的语句时:
update del set 职工号=\'001\',部门名称=\'wenda\' where 职工号=\'01\'[出现错误]
只能够改成:update del set 职工号=\'001\' where 职工号=\'01\'
update del set 部门名称=\'wenda\' where 职工号=\'01\'
2:不能在使用了distinct语句的视图中更新值
3:不能在使用了group by语句的视图中更新值
3:使用delete删除视图中数据.
通过视图删除数据最终体现为从基本表中删除数据
格式:delete 视图名 [where 条件]
说明:当视图由两个以上的基表构成时,不允许删除视图的数据
例如:建一个视图kk
create view kk as
select 职工号,姓名,性别,部门名称 from work1,部门 where work1.部门编号=部门.部门编号 [试着去删除]
使用with check option的视图
如果不了解视图定义内容,则常常会发生向视图中输入不符合视图定义的数据的情况.
比如:create view xm as
select * from work where 性别=\'男\'
完全可以插入insert xm values(\'001\',\'女\',23,\'2400\'....)
尽管从意义上来说是不合理的,但是上述语句是正确的.为了防止这种情况的发生,可以使用with check option子句来对插入的或更改的数据进行限制.
比如:create view xm as
select * from work where 性别=\'男\' with check option
使用schemabinding的视图[使用绑定到构架]
我们知道视图是依赖于表,如果在一个表中创建一个视图,今后如果这个表被删除了,则这个视图将不可再用了.为了防止用户删除一个有视图在引用的表,可以在创建视图的时候加上schemabinding关键字.
比如:create view 基本工资 with SCHEMABINDING
as select 姓名,性别,基本工资 from dbo.work
说明:1:不能使用“*”来创建此类型的视图
2:创建此类型的视图时,一定要加上dbo.表名.
3:如果在某个表中定义了此类视图,则用户将不能对表的结构进行修改,否则会删除这些绑定
4:如果用户对表的结构进行列改名,则会删除绑定而且视图不可用.
5:如果用户对表的结构进行列的类型或者大小修改,则会删除绑定但视图可用,此时用户可以删除视图所引用的表.
使用with encryption对视图进行加密
为了保护创建视图定义的原代码,可以对视图进行加密.
比如:create view kk with encryption
as select * from work where 职称=\'经理\'
用sp_helptext来查看一下.或用企业管理器查看一下.
说明:如果应用此项用户将无法设计视图
F:使用视图加强数据的安全
一般通过使用视图共有三种途径加强数据的安全性
A:对不同用户授予不同的使用权.
B:通过使用select子句限制用户对某些底层基表的列的访问
C:通过使用where子句限制用户对某些底层基表的行的访问
对不同用户授予不同的权限
连接查询
通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。
在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。
连接可以在SELECT 语句的FROM子句或WHERE子句中建立，似是而非在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。所以，在Transact-SQL中推荐使用这种方法。
SQL-92标准所定义的FROM子句的连接语法格式为：
FROM join_table join_type join_table
[ON (join_condition)]
其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。
join_type 指出连接类型，可分为三种：内连接、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。
外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。
交叉连接(CROSS JOIN)没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。
无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。例如：
SELECT p1.pub_id,p2.pub_id,p1.pr_info
FROM pub_info AS p1 INNER JOIN pub_info AS p2
ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)
(一)内连接
内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：
1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。
例，下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社：
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city=p.city
又如使用自然连接，在选择列表中删除authors 和publishers 表中重复列(city和state)：
SELECT a.*,p.pub_id,p.pub_name,p.country
FROM authors AS a INNER JOIN publishers AS p
ON a.city=p.city
(二)外连接
内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。
如下面使用左外连接将论坛内容和作者信息连接起来：
SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b
ON a.username=b.username
下面使用全外连接将city表中的所有作者以及user表中的所有作者，以及他们所在的城市：
SELECT a.*,b.*
FROM city as a FULL OUTER JOIN user as b
ON a.username=b.username
(三)交叉连接
交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等
于6*8=48行。
SELECT type,pub_name
FROM titles CROSS JOIN publishers
ORDER BY typ