- DECLARE @dt datetime
- SET @dt=GETDATE()
- DECLARE @number int
- SET @number=3
- --1.指定日期该年的第一天或最后一天
- --A. 年的第一天
- SELECT CONVERT(char(5),@dt,120)+'1-1'
- --B. 年的最后一天
- SELECT CONVERT(char(5),@dt,120)+'12-31'
- --2.指定日期所在季度的第一天或最后一天
- --A. 季度的第一天
- SELECT CONVERT(datetime,
- CONVERT(char(8),
- DATEADD(Month,
- DATEPART(Quarter,@dt)*3-Month(@dt)-2,
- @dt),
- 120)+'1')
- --B. 季度的最后一天(CASE判断法)
- SELECT CONVERT(datetime,
- CONVERT(char(8),
- DATEADD(Month,
- DATEPART(Quarter,@dt)*3-Month(@dt),
- @dt),
- 120)
- +CASE WHEN DATEPART(Quarter,@dt) in(1,4)
- THEN '31'ELSE '30' END)
- --C. 季度的最后一天(直接推算法)
- SELECT DATEADD(Day,-1,
- CONVERT(char(8),
- DATEADD(Month,
- 1+DATEPART(Quarter,@dt)*3-Month(@dt),
- @dt),
- 120)+'1')
- --3.指定日期所在月份的第一天或最后一天
- --A. 月的第一天
- SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')
- --B. 月的最后一天
- SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')
- --C. 月的最后一天(容易使用的错误方法)
- SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))
- --4.指定日期所在周的任意一天
- SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
- --5.指定日期所在周的任意星期几
- --A. 星期天做为一周的第1天
- SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)
- --B. 星期一做为一周的第1天
- SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
- ---系统表说明
- syscolumns
- 每个表和视图中的每列在表中占一行,存储过程中的每个参数在表中也占一行。该表位于每个数据库中。
- 列名 数据类型 描述
- name sysname 列名或过程参数的名称。
- id int 该列所属的表对象 ID,或与该参数关联的存储过程 ID。
- xtype tinyint systypes 中的物理存储类型。
- typestat tinyint 仅限内部使用。
- xusertype smallint 扩展的用户定义数据类型 ID。
- length smallint systypes 中的最大物理存储长度。
- xprec tinyint 仅限内部使用。
- xscale tinyint 仅限内部使用。
- colid smallint 列或参数 ID。
- xoffset smallint 仅限内部使用。
- bitpos tinyint 仅限内部使用。
- reserved tinyint 仅限内部使用。
- colstat smallint 仅限内部使用。
- cdefault int 该列的默认值 ID。
- domain int 该列的规则或 CHECK 约束 ID。
- number smallint 过程分组时(0 表示非过程项)的子过程号。
- colorder smallint 仅限内部使用。
- autoval varbinary(255) 仅限内部使用。
- offset smallint 该列所在行的偏移量;如果为负,表示可变长度行。
- status tinyint 用于描述列或参数属性的位图:
- 0x08 = 列允许空值。
- 0x10 = 当添加 varchar 或 varbinary 列时,ANSI 填充生效。保留 varchar 列的尾随空格,保留 varbinary 列的尾随零。
- 0x40 = 参数为 OUTPUT 参数。
- 0x80 = 列为标识列。
- type tinyint systypes 中的物理存储类型。
- usertype smallint systypes 中的用户定义数据类型 ID。
- printfmt varchar(255) 仅限内部使用。
- prec smallint 该列的精度级别。
- scale int 该列的小数位数。
- iscomputed int 表示是否已计算该列的标志:
- 0 = 未计算。
- 1 = 已计算。
- isoutparam int 表示该过程参数是否是输出参数:
- 1 = 真。
- 0 = 假。
- isnullable int 表示该列是否允许空值:
- 1 = 真。
- 0 = 假。
- 排序问题
- CREATE TABLE [t] (
- [id] [int] IDENTITY (1, 1) NOT NULL ,
- [GUID] [uniqueidentifier] NULL
- ) ON [PRIMARY]
- GO
- 下面这句执行5次
- insert t values (newid())
- 查看执行结果
- select * from t
- 1、 第一种
- select * from t
- order by case id when 4 then 1
- when 5 then 2
- when 1 then 3
- when 2 then 4
- when 3 then 5 end
- 2、 第二种
- select * from t order by (id+2)%6
- 3、 第三种
- select * from t order by charindex(cast(id as varchar),'45123')
- 4、 第四种
- select * from t
- WHERE id between 0 and 5
- order by charindex(cast(id as varchar),'45123')
- 5、 第五种
- select * from t order by case when id >3 then id-5 else id end
- 6、 第六种
- select * from t order by id / 4 desc,id asc
- 一条语句删除一批记录
- 首先id列是int标识类类型,然后删除ID值为5,6,8,9,10,11的列,这里的cast函数不能用convert函数代替,而且转换的类型必须是varchar,而不能是char,否则就会执行出你不希望的结果,这里的"5,6,8,9,10,11"可以是你在页面上获取的一个chkboxlist构建成的值,然后用下面的一句就全部删
- 除了,比循环用多条语句高效吧应该。
- delete from [fujian] where charindex(','+cast([id] as varchar)+',',','+'5,6,8,9,10,11,'+',')>0
- 还有一种就是
- delete from table1 where id in(1,2,3,4 )
- --动态SQL基本语法:
- 1 :普通SQL语句可以用exec执行
- Select * from tableName
- exec('select * from tableName')
- exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N
- 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL
- declare @fname varchar(20)
- set @fname = 'FiledName'
- Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
- exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格
- 当然将字符串改成变量的形式也可
- declare @fname varchar(20)
- set @fname = 'FiledName' --设置字段名
- declare @s varchar(1000)
- set @s = 'select ' + @fname + ' from tableName'
- exec(@s) -- 成功
- exec sp_executesql @s -- 此句会报错
- declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
- set @s = 'select ' + @fname + ' from tableName'
- exec(@s) -- 成功
- exec sp_executesql @s -- 此句正确
- 3. 输出参数
- declare @num int, @sqls nvarchar(4000)
- set @sqls='select count(*) from tableName'
- exec(@sqls)
- --如何将exec执行结果放入变量中?
- declare @num int, @sqls nvarchar(4000)
- set @sqls='select @a=count(*) from tableName '
- exec sp_executesql @sqls,N'@a int output',@num output
- select @num
- 1 :普通SQL语句可以用Exec执行 例: Select * from tableName
- Exec('select * from tableName')
- Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N
- 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL
- 错误:declare @fname varchar(20)
- set @fname = 'FiledName'
- Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
- 正确: Exec('select ' + @fname + ' from tableName') -- 请注意加号前后的单引号的边上加空格
- 当然将字符串改成变量的形式也可
- declare @fname varchar(20)
- set @fname = 'FiledName' --设置字段名
- declare @s varchar(1000)
- set @s = 'select ' + @fname + ' from tableName'
- Exec(@s) -- 成功
- exec sp_executesql @s -- 此句会报错
- --注:@s参数必须为ntext或nchar或nvarchar类型,必须将declare @s varchar(1000) 改为declare @s Nvarchar(1000)
- 如下:
- declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
- set @fname = 'FiledName' --设置字段名
- set @s = 'select ' + @fname + ' from tableName'
- Exec(@s) -- 成功
- exec sp_executesql @s -- 此句正确
- 3. 输入或输出参数
- (1)输入参数:
- declare @QueryString nvarchar(1000) --动态查询语句变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
- declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
- declare @input_id int--定义需传入动态语句的参数的值
- set @QueryString='select * from tablename where id=@id' --id为字段名,@id为要传入的参数
- set @paramstring='@id int' --设置动态语句中参数的定义的字符串
- set @input_id =1 --设置需传入动态语句的参数的值为1
- exec sp_executesql @querystring,@paramstring,@id=@input_id
- 若有多个参数:
- declare @QueryString nvarchar(1000) --动态查询语句变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
- declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
- declare @input_id int--定义需传入动态语句的参数的值,参数1
- declare @input_name varchar(20)--定义需传入动态语句的参数的值,参数2
- set @QueryString='select * from tablename where id=@id and name=@name' --id与name为字段名,@id与@name为要传入的参数
- set @paramstring='@id int,@name varchar(20)' --设置动态语句中参数的定义的字符串,多个参数用","隔开
- set @input_id =1 --设置需传入动态语句的参数的值为1
- set @input_name='张三' --设置需传入动态语句的参数的值为"张三"
- exec sp_executesql @querystring,@paramstring,@id=@input_id,@name=@input_name --请注意参数的顺序
- (2)输出参数
- declare @num int, @sqls nvarchar(4000)
- set @sqls='select count(*) from tableName'
- exec(@sqls)
- --如何将exec执行结果放入变量中?
- declare @QueryString nvarchar(1000) --动态查询语名变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
- declare @paramstring nvarchar(200) --设置动态语句中的参数的字符串(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
- declare @output_result int--查询结果赋给@output_result
- set @QueryString='select @totalcount=count(*) from tablename' --@totalcount 为输出结果参数
- set @paramstring='@totalcount int output' --设置动态语句中参数的定义的字符串,多个参数用","隔开
- exec sp_executesql @querystring,@paramstring,@totalcount=@output_result output
- select @output_result
- 当然,输入与输出参数可以一起使用,大家可以自己去试一试。
- 另外,动态语句查询的结果集要输出的话,我只想到以下用临时表的方法,不知各位有没有更好的方法.
- IF object_id('[tempdb].[dbo].#tmp') IS NOT NULL --判断临时表#tmp是否存在,存在则删除
- drop table #tmp
- select * into #tmp from tablename where 1=2 --创建临时表#tmp,其结构与tablename相同
- declare @QueryString nvarchar(1000) --动态查询语名变量(注:必须为ntext或nchar哐nvarchar类型,不能是varchar类型)
- set @QueryString='select * from tablename '
- insert into #tmp(field1,field2,...) exec(@querystirng)
- 1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用)
- 法一:select * into b from a where 1 <>1
- 法二:select top 0 * into b from a
- 2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用)
- insert into b(a, b, c) select d,e,f from a;
- 3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
- insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
- 例子:..from b in '"&Server.MapPath(".")&"/data.mdb" &"' where..
- 4、说明:子查询(表名1:a 表名2:b)
- select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
- 5、说明:显示文章、提交人和最后回复时间
- select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
- 6、说明:外连接查询(表名1:a 表名2:b)
- select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
- 7、说明:在线视图查询(表名1:a )
- select * from (SELECT a,b,c FROM a) T where t.a > 1;
- 8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括
- select * from table1 where time between time1
- 9、说明:in 的使用方法
- select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
- 10、说明:两张关联表,删除主表中已经在副表中没有的信息
- delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
- 11、说明:四表联查问题:
- select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
- 12、说明:日程安排提前五分钟提醒
- SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
- 13、说明:一条sql 语句搞定数据库分页
- select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
- 14、说明:前10条记录
- select top 10 * form table1 where 范围
- 15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
- select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
- 16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
- (select a from tableA ) except (select a from tableB) except (select a from tableC)
- 17、说明:随机取出10条数据
- select top 10 * from tablename order by newid()
- 18、说明:随机选择记录
- select newid()
- 19、说明:删除重复记录
- Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
- 20、说明:列出数据库里所有的表名
- select name from sysobjects where type='U'
- 21、说明:列出表里的所有的列
- select name from syscolumns where id=object_id('TableName')
- 22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。
- select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
- 显示结果:
- type vender pcs
- 电脑 A 1
- 电脑 A 1
- 光盘 B 2
- 光盘 A 2
- 手机 B 3
- 手机 C 3
- 23、说明:初始化表table1
- TRUNCATE TABLE table1
- 24、说明:选择从10到15的记录
- select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
- declare @a varchar(100),@b varchar(20)
- select @a='abcdefbcmnbcde',@b='bc'
- select (len(@a)-len(replace(@a,@b,'')))/len(@b)
- 说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
- select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
- 一年中所有为星期二的日期
- select dateadd(day,x,col),'星期二' from
- (
- select cast('2006-1-1' as datetime) as col
- )a cross join
- (
- SELECT top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x
- FROM(SELECT 0 i UNION ALL SELECT 1) b0
- CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1
- CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2
- CROSS JOIN(SELECT 0 i UNION ALL SELECT 8) b3
- CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4
- CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5
- CROSS JOIN(SELECT 0 i UNION ALL SELECT 64) b6
- CROSS JOIN(SELECT 0 i UNION ALL SELECT 128) b7
- CROSS JOIN(SELECT 0 i UNION ALL SELECT 256) b8
- order by 1
- )b
- where datepart(dw,dateadd(day,x,col))=3
- /*功能:2000当中绘画日历 */
- DECLARE @Year nvarchar(4)
- DECLARE @YearMonth nvarchar(7) --月份
- DECLARE @strTop nvarchar(200)
- DECLARE @ForI INT,@ForYear INT ,@MaxDay INT
- DECLARE @RowX INT --行位置
- DECLARE @strWeekDayList nvarchar(20)
- DECLARE @strPrint nvarchar(300)
- -- ======================================
- SET @Year='2008' --请在这里输入年份
- -- ======================================
- SET @strTop= '日'+char(9)+'一'+char(9)+'二' +char(9)+'三'++char(9)+'四'++char(9)+'五'++char(9)+'六' +char(13)+
- '───────────────────────────'
- SET @strWeekDayList='日一二三四五六'
- SET @ForYear=1
- WHILE @ForYear<=12 --1月份至12月份
- BEGIN
- SET @YearMonth=@Year + '-' +CAST( @ForYear AS nvarchar(2))
- SET @MaxDay=DAY(DATEADD(Day,-1,DATEADD(Month,1,@YearMonth+'-01')))
- SET @RowX=CHARINDEX(RIGHT(DATENAME(WeekDay,@YearMonth+'-01'),1),@strWeekDayList)-1
- SET @strPrint=''
- SET @ForI=1
- WHILE @ForI<=@RowX --构造1号的位置
- BEGIN
- SET @strPrint=@strPrint+CHAR(9)
- SET @ForI=@ForI+1
- END
- SET @ForI=1
- WHILE @ForI<=@MaxDay --构造2号到月底的位置
- BEGIN
- SET @strPrint=@strPrint+CAST(@ForI AS nvarchar(2)) +Char(9)
- SET @RowX=@RowX+1
- SET @ForI=@ForI+1
- IF (@RowX%7=0)
- BEGIN
- SET @RowX=0
- SET @strPrint=@strPrint+CHAR(13)
- END
- END
- SET @ForYear=@ForYear+1
- -- 打印输出一个月的结果
- PRINT '━━━━━━━━━━━━━━━━━━━━━━━━━━━'
- PRINT +Char(9)++Char(9)+' '+@YearMonth+CHAR(10)
- PRINT @strTop
- PRINT @strPrint+CHAR(10)
- END
- 1. 查看数据库的版本
- select @@version
- 2. 查看数据库所在机器操作系统参数
- exec master..xp_msver
- 3. 查看数据库启动的参数
- sp_configure
- 4. 查看数据库启动时间
- select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
- 查看数据库服务器名和实例名
- print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
- print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)
- 5. 查看所有数据库名称及大小
- sp_helpdb
- 重命名数据库用的SQL
- sp_renamedb 'old_dbname', 'new_dbname'
- 6. 查看所有数据库用户登录信息
- sp_helplogins
- 查看所有数据库用户所属的角色信息
- sp_helpsrvrolemember
- 修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程
- 更改某个数据对象的用户属主
- sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'
- 注意: 更改对象名的任一部分都可能破坏脚本和存储过程。
- 把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本
- 7. 查看链接服务器
- sp_helplinkedsrvlogin
- 查看远端数据库用户登录信息
- sp_helpremotelogin
- 8.查看某数据库下某个数据对象的大小
- sp_spaceused @objname
- 还可以用sp_toptables过程看最大的N(默认为50)个表
- 查看某数据库下某个数据对象的索引信息
- sp_helpindex @objname
- 还可以用SP_NChelpindex过程查看更详细的索引情况
- SP_NChelpindex @objname
- clustered索引是把记录按物理顺序排列的,索引占的空间比较少。
- 对键值DML操作十分频繁的表我建议用非clustered索引和约束,fillfactor参数都用默认值。
- 查看某数据库下某个数据对象的的约束信息
- sp_helpconstraint @objname
- 9.查看数据库里所有的存储过程和函数
- use @database_name
- sp_stored_procedures
- 查看存储过程和函数的源代码
- sp_helptext '@procedure_name'
- 查看包含某个字符串@str的数据对象名称
- select distinct object_name(id) from syscomments where text like '%@str%'
- 创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数
- 解密加密过的存储过程和函数可以用sp_decrypt过程
- 10.查看数据库里用户和进程的信息
- sp_who
- 查看SQL Server数据库里的活动用户和进程的信息
- sp_who 'active'
- 查看SQL Server数据库里的锁的情况
- sp_lock
- 进程号1--50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.
- spid是进程编号,dbid是数据库编号,objid是数据对象编号
- 查看进程正在执行的SQL语句
- dbcc inputbuffer ()
- 推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句
- sp_who3
- 检查死锁用sp_who_lock过程
- sp_who_lock
- 11.收缩数据库日志文件的方法
- 收缩简单恢复模式数据库日志,收缩后@database_name_log的大小单位为M
- backup log @database_name with no_log
- dbcc shrinkfile (@database_name_log, 5)
- 12.分析SQL Server SQL 语句的方法:
- set statistics time {on | off}
- set statistics io {on | off}
- 图形方式显示查询执行计划
- 在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形
- 文本方式显示查询执行计划
- set showplan_all {on | off}
- set showplan_text { on | off }
- set statistics profile { on | off }
- 13.出现不一致错误时,NT事件查看器里出3624号错误,修复数据库的方法
- 先注释掉应用程序里引用的出现不一致性错误的表,然后在备份或其它机器上先恢复然后做修复操作
- alter database [@error_database_name] set single_user
- 修复出现不一致错误的表
- dbcc checktable('@error_table_name',repair_allow_data_loss)
- 或者可惜选择修复出现不一致错误的小型数据库名
- dbcc checkdb('@error_database_name',repair_allow_data_loss)
- alter database [@error_database_name] set multi_user
- CHECKDB 有3个参数:
- repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误,
- 以及删除已损坏的文本对象,这些修复可能会导致一些数据丢失。
- 修复操作可以在用户事务下完成以允许用户回滚所做的更改。
- 如果回滚修复,则数据库仍会含有错误,应该从备份进行恢复。
- 如果由于所提供修复等级的缘故遗漏某个错误的修复,则将遗漏任何取决于该修复的修复。
- 修复完成后,请备份数据库。
- repair_fast 进行小的、不耗时的修复操作,如修复非聚集索引中的附加键。
- 这些修复可以很快完成,并且不会有丢失数据的危险。
- repair_rebuild 执行由 repair_fast 完成的所有修复,包括需要较长时间的修复(如重建索引)。
- 执行这些修复时不会有丢失数据的危险。
- ---join的用法
- DECLARE
- @TA TABLE (IDA INT,VA VARCHAR(10))
- DECLARE
- @TB TABLE (IDB INT,VB VARCHAR(10))
- INSERT INTO @TA
- SELECT
- 1,'AA' UNION SELECT
- 2,'BC' UNION SELECT
- 3,'CCC'
- INSERT INTO @TB
- SELECT
- 1,'2' UNION SELECT
- 3,'58' UNION SELECT
- 4,'67'
- --内联接简单写法
- SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A,@TB B
- WHERE A.IDA=B.IDB
- --内联接
- SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A INNER JOIN @TB B
- ON A.IDA=B.IDB
- SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A JOIN @TB B
- ON A.IDA=B.IDB
- --左外联接
- SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A LEFT JOIN @TB B
- ON A.IDA=B.IDB
- SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A LEFT OUTER JOIN @TB B
- ON A.IDA=B.IDB
- --右外联接
- SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A RIGHT JOIN @TB B
- ON A.IDA=B.IDB
- SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A RIGHT OUTER JOIN @TB B
- ON A.IDA=B.IDB
- --完整外联接
- SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A FULL JOIN @TB B
- ON A.IDA=B.IDB
- SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A FULL OUTER JOIN @TB B
- ON A.IDA=B.IDB
- --交叉联接
- SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A CROSS JOIN @TB B
- --自联接
- SELECT A.IDA,A.VA,B.IDA,B.VA FROM @TA A,@TA B WHERE A.IDA=B.IDA+1
- 查询分析器中执行:
- --建表table1,table2:
- create table table1(id int,name varchar(10))
- create table table2(id int,score int)
- insert into table1 select 1,'lee'
- insert into table1 select 2,'zhang'
- insert into table1 select 4,'wang'
- insert into table2 select 1,90
- insert into table2 select 2,100
- insert into table2 select 3,70
- 如表
- -------------------------------------------------
- table1|table2|
- -------------------------------------------------
- idname|idscore|
- 1lee|190|
- 2zhang|2100|
- 4wang|370|
- -------------------------------------------------
- 以下均在查询分析器中执行
- 一、外连接
- 1.概念:包括左向外联接、右向外联接或完整外部联接
- 2.左连接:left join 或 left outer join
- (1)左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值(null)。
- (2)sql语句
- select * from table1 left join table2 on table1.id=table2.id
- -------------结果-------------
- idnameidscore
- ------------------------------
- 1lee190
- 2zhang2100
- 4wangNULLNULL
- ------------------------------
- 注释:包含table1的所有子句,根据指定条件返回table2相应的字段,不符合的以null显示
- 3.右连接:right join 或 right outer join
- (1)右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
- (2)sql语句
- select * from table1 right join table2 on table1.id=table2.id
- -------------结果-------------
- id name id score
- ------------------------------
- 1 lee 19 0
- 2 z hang 2100
- 4 NULL NULL 370
- ------------------------------
- 注释:包含table2的所有子句,根据指定条件返回table1相应的字段,不符合的以null显示
- 4.完整外部联接:full join 或 full outer join
- (1)完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
- (2)sql语句
- select * from table1 full join table2 on table1.id=table2.id
- -------------结果-------------
- idnameidscore
- ------------------------------
- 1lee190
- 2zhang2100
- 4wangNULLNULL
- NULLNULL370
- ------------------------------
- 注释:返回左右连接的和(见上左、右连接)
- 二、内连接
- 1.概念:内联接是用比较运算符比较要联接列的值的联接
- 2.内连接:join 或 inner join
- 3.sql语句
- select * from table1 join table2 on table1.id=table2.id
- -------------结果-------------
- idnameidscore
- ------------------------------
- 1lee190
- 2zhang2100
- ------------------------------
- 注释:只返回符合条件的table1和table2的列
- 4.等价(与下列执行效果相同)
- A:select a.*,b.* from table1 a,table2 b where a.id=b.id
- B:select * from table1 cross join table2 where table1.id=table2.id (注:cross join后加条件只能用where,不能用on)
- 三、交叉连接(完全)
- 1.概念:没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。(table1和table2交叉连接产生3*3=9条记录)
- 2.交叉连接:cross join (不带条件where...)
- 3.sql语句
- select * from table1 cross join table2
- -------------结果-------------
- idnameidscore
- ------------------------------
- 1lee190
- 2zhang190
- 4wang190
- 1lee2100
- 2zhang2100
- 4wang2100
- 1lee370
- 2zhang370
- 4wang370
- ------------------------------
- 注释:返回3*3=9条记录,即笛卡尔积
- 4.等价(与下列执行效果相同)
- A:select * from table1,table2