日度归档:2020年1月9日

mssql sqlserver sql脚本中 inner join、outer join和cross join 用法的区别分享


摘要:
下文使用举例的方式讲述inner join full join cross join 关键字之间的用法区别,如下所示;
实验环境:sql server 2008 R2


inner join、full join 、cross join 功能说明


例:
基础表 “A” “B”
1. A INNER JOIN B 产生A表和B表的交集
——————
2. A FULL join B(A full outer join B) 生成A表和B表的并集
——————
3. A left join B(A left outer join B) 返回生成表A的所有行,并且依次匹配B表的记录,存在匹配值时,返回B值,不存在时则使用NULL替代。
———————–
4. A right join B(A right outer join B) 返回生成表B的所有行,并且依次匹配A表的记录,存在匹配值时,返回A值,不存在时则使用NULL替代。
————————-
5. CROSS join 生成A表和B表进行笛卡尔积运算


inner join、full join 、cross join 关键字举例说明



create table [maomao365.comA]
(sortA varchar(10),qtyA int)
create table [maomao365.comB]
(sortB varchar(10),qtyB int)
 go
--插入基础数据
insert into [maomao365.comA]
(sortA,qtyA)values('a',2),('a',3),
('b',7),('c',8),
('b',10),('c',12),('d',15)
go

insert into [maomao365.comB]
(sortB,qtyB)values('a',100),('b',99),
('b',77),('c',888)
go

select * from 
[maomao365.comA] a 
inner join [maomao365.comB] b 
on a.sortA = b.sortB 


select * from 
[maomao365.comA] a 
full join [maomao365.comB] b 
on a.sortA = b.sortB 

select * from 
[maomao365.comA] a 
full outer join [maomao365.comB] b 
on a.sortA = b.sortB 


select * from 
[maomao365.comA] a 
left  join [maomao365.comB] b 
on a.sortA = b.sortB 

select * from 
[maomao365.comA] a 
left outer join [maomao365.comB] b 
on a.sortA = b.sortB 

select * from 
[maomao365.comA] a 
right  join [maomao365.comB] b 
on a.sortA = b.sortB 

select * from 
[maomao365.comA] a 
right outer join [maomao365.comB] b 
on a.sortA = b.sortB 


select * from 
[maomao365.comA] a 
cross join [maomao365.comB] b 
 
go
truncate table [maomao365.comB]
 drop     table [maomao365.comB]
 go
 truncate table [maomao365.comA]
 drop     table [maomao365.comA]
sqlserver join 举例说明

sqlserver join 举例说明

mssql sqlserver 如何在where条件中使用sum()呢?


摘要:
下文讲述在sqlserver中,查询数据时,在where检索条件 对聚合后的数据过滤,如下所示:
实验环境:sql server 2008 R2


实现思路:
在查询数据的后面,使用having 关键字,即可完成聚合数据的检索(having sum(***) >*** ),如下例所示:

例:

create table [maomao365.com]
(sort varchar(10),qty int)
 go
--插入基础数据
insert into [maomao365.com]
(sort,qty)values('a',2),('a',3),
('b',7),('c',8),
('b',10),('c',12),('d',15)
go
select sort,sum(qty)
 from [maomao365.com]
 group by sort 
 having sum(qty) >10 
 ---having 关键字起过滤sum的作用
 go

 truncate table [maomao365.com]
 drop     table [maomao365.com]
 go
 --------输出结果--------------
b	17
c	20
d	15

mssql sqlserver 如何判断当前数据库是否存在指定数据表呢?


摘要:
下文讲述sqlserver中判断数据库中是否存在指定数据表的多种方法分享,如下所示:
实验环境:sql server 2008 R2

例:
判断数据库中是否存在数据表 “maomao”

 IF EXISTS (SELECT 1 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_TYPE='BASE TABLE' 
           AND TABLE_NAME='maomao') 
    begin
     print '存在数据表maomao'
    end
go

IF OBJECT_ID (N'maomao', N'U') IS NOT NULL 
  begin
     print '存在数据表maomao'
  end
  go
IF EXISTS(SELECT 1 FROM sys.Objects WHERE  Object_id = OBJECT_ID(N'dbo.maomao') AND Type = N'U')
BEGIN
   PRINT '存在数据表maomao'
END
go

IF EXISTS(SELECT 1 FROM sys.Tables WHERE  Name = N'maomao' AND Type = N'U')
 BEGIN
      PRINT '存在数据表maomao'
 END
go