SQL SERVER复习笔记04

--五,compute by,根据compute by 和order by 的by后面的列来进行分组,
select * from book1
order by 书名 desc
compute avg(定价) by 书名

--六.group by
select 编号 , COUNT(书名) from book1
group by 书名
/*报错
消息 8120，级别 16，状态 1，第 1 行
选择列表中的列 'book1.编号' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
说明,SELECT 出的列必须是GROUP BY里会包含的
*/
select 书名,COUNT(书名) as '本数' from book1
group by 书名

--七.having ，与WHERE意思相同,但是where更有效率,只是WHERE中不能使用聚合函数,而SELECT和HAVING可以
--平价定价大于60的书,这里就只能用having
select 书名,avg(定价) as '平均价格' from book1
group by 书名
having avg(定价)>60
--由此,我们可以看到WHERE是对原始记录进行过虑,面HAVING是对查询结果进行过虑

--八.嵌套查询,注意:子查询中不能用ORDER BY,它只能用于最终查询结果的排序;子查询只返回一列数据

select * from book1
where 定价>(select avg(定价) from book1)

/*
九.UNION,两个规则:1,所有查询中列数和列的顺序必须相同,表1第N列对应表2第N列;2,各列相对应数据类型必须
相兼容,即要么数据类型要同,要么SQL SERVER能从一种数据类型转换为另一种数据类型.列名是以表1为准的,所以
要进行更改列名要在表1更改
*/
select 编号 as 'BOOK1编号与BOOK2书名' from book1
union
select 书名 from book2
order by 书名
/*消息 104，级别 16，状态 1，第 4 行
如果该语句包含 UNION、INTERSECT 或 EXCEPT 运算符，则 ORDER BY 项必须出现在选择列表中。
显然这里0RDER BY 后面用表2的列是不对的*/
select 编号 as 'BOOK1编号与BOOK2书名' from book1
union
select 书名 from book2
order by 编号
--十,多表查询与连接.1.内连接:等值连接和自然连接;2.外连接:左外连接、右外连接、全连接;3.交叉连接

--1.笛卡尔积
select * from book1,teacher

--2.连接条件,一般来说,N个表连接要有N-1个条件,多表或视图可以在同一数据库,也可以不在
--向teacher表插入数据
exec sp_help teacher
insert into teacher values ('1011','张三','男',35,'233','10117')--完整表结构插入  
insert into teacher values ('0172','李四','男',35,'119','10172')--完整表结构插入  
insert into teacher values ('1012','小七','女',35,'112','1012')--完整表结构插入  
insert into teacher values ('1017','小五','女',35,'110','1017')--完整表结构插入 
insert into teacher values ('3424','何东','女',35,'3434','103417')
 
select * from teacher  
--(1)等值连接,连接列出现两次,则会出现同名的列
select book1.编号,book1.书名,book1.书名,book1.定价,teacher.职称 from book1,teacher
where book1.编号=teacher.编号
 --当多表中有同名列时,一定要用[table_name].[column_name]的格式
 
 select * from book1,teacher
 where book1.编号=teacher.编号

--(2)自然连接,可去掉重复列,即在等值连接上,用SELECT子句指定结果表中包含的列名
select book1.*,作者姓名,职称,联系电话 from book1,teacher
where book1.编号=teacher.编号

--(3)不等值连接
select book1.*,作者姓名,职称,联系电话 from book1,teacher
where book1.编号<>teacher.编号

select book1.编号,book1.书名,book2.编号,book2.书名 from book1,book2
where book1.编号<>book2.编号

--(4)自连接,一个表自身连接,这样能把一个表中的行与行之间联系起来
select a.编号, a.出版社 ,a.书名, a.定价, b.编号 as b编号
from book1 as a,book1 as b
where a.出版社=b.出版社 and a.编号<>'0172' and b.编号='101172'

--(5)左外连接,相当于先进行自然连接,然后将左表不匹配的行添加进自然连接结果集里,用空值来填充右表
select book1.*,作者姓名,职称,联系电话 from book1,teacher
where book1.编号*=teacher.编号
/*报错:
消息 4147，级别 15，状态 1，第 2 行
此查询使用的不是 ANSI 外部联接运算符("*=" 或 "=*")。若要不进行修改即运行此查询，
请使用 ALTER DATABASE 的 SET COMPATIBILITY_LEVEL 选项将当前数据库的兼容级别设置为 80。
极力建议使用 ANSI 外部联接运算符(LEFT OUTER JOIN、RIGHT OUTER JOIN)重写此查询。
在将来的 SQL Server 版本中，即使在向后兼容模式下，也不支持非 ANSI 联接运算符。
修改如下:
*/
select book1.*,作者姓名,职称,联系电话 from book1 LEFT OUTER JOIN teacher
ON book1.编号=teacher.编号

--(6)右外连接,相当于先进行自然连接,然后将右表不匹配的行添加进自然连接结果集里,用空值来填充左表
select book1.*,作者姓名,职称,联系电话 from book1 RIGHT OUTER JOIN teacher
ON book1.编号=teacher.编号

--(7)全外连接,左右表空值都填充进来
select book1.*,作者姓名,职称,联系电话 from book1 FULL  JOIN teacher
ON book1.编号=teacher.编号

--(8)交叉连接,列数为两表列数之和,行数为两表行之积
select * from book1,teacher

--十.exists和in
select 编号,书名,定价 from book1
where exists (select 编号 from teacher)

select * from teacher

select  编号,书名,定价 from book1
where 编号 in (select 编号 from teacher)