外贸汽车网站制作,高碑店住房和城乡建设局网站,网站地图模板,广州网站建设第一公司1#xff0e; 判断a表中有而b表中没有的记录 select a.* from tbl1 a left join tbl2 b on a.key b.key where b.key is null 虽然使用in也可以实现#xff0c;但是这种方法的效率更高一些 2#xff0e; 新建一个与某个表相同结构的表 select * into b from a where 1 判断a表中有而b表中没有的记录 select a.* from tbl1 a left join tbl2 b on a.key b.key where b.key is null 虽然使用in也可以实现但是这种方法的效率更高一些 2 新建一个与某个表相同结构的表 select * into b from a where 11 3between的用法,between限制查询数据范围时包括了边界值,not between不包括 select * from table1 where time between time1 and time2 select a,b,c, from table1 where a not between 数值1 and 数值2 4. 说明包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 (select a from tableA ) except (select a from tableB) except (select a from tableC) 5. 初始化表可以将自增长表的字增长字段置为1 TRUNCATE TABLE table1 6多语言设置数据库或者表或者order by的排序规则 --修改用户数据库的排序规则 ater database dbname collate SQL_Latin1_General_CP1_CI_AS --修改字段的排序规则 alter table a alter column c2 varchar(50) collate SQL_Latin1_General_CP1_CI_AS --按姓氏笔画排序 select * from 表名 order by 列名 Collate Chinese_PRC_Stroke_ci_as --按拼音首字母排序 select * from 表名 order by 列名 Collate Chinese_PRC_CS_AS_KS_WS 7列出所有的用户数据表 SELECT TOP 100 PERCENT o.name AS 表名 FROM dbo.syscolumns c INNER JOIN dbo.sysobjects o ON o.id c.id AND objectproperty(o.id, NIsUserTable) 1 AND o.name dtproperties LEFT OUTER JOIN dbo.sysproperties m ON m.id o.id AND m.smallid c.colorder WHERE (c.colid 1) ORDER BY o.name, c.colid 8列出所有的用户数据表及其字段信息 SELECT TOP 100 PERCENT c.colid AS 序号, o.name AS 表名, c.name AS 列名, t.name AS 类型, c.length AS 长度, c.isnullable AS 允许空, CAST(m.[value] AS Varchar(100)) AS 说明 FROM dbo.syscolumns c INNER JOIN dbo.sysobjects o ON o.id c.id AND objectproperty(o.id, NIsUserTable) 1 AND o.name dtproperties INNER JOIN dbo.systypes t ON t.xusertype c.xusertype LEFT OUTER JOIN dbo.sysproperties m ON m.id o.id AND m.smallid c.colorder ORDER BY o.name, c.colid 9Leftright Join的另外一种简洁的写法 select * from a,b where a.id * b.id --* 相当于 LEFT JOIN select * from a,b where a.id * b.id --* 相当于 Right JOIN 10Update from 和 delete from 11得到表中最小的未使用的ID号 SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID 1) THEN MIN(HandleID) 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a) 12随机取得记录 SELECT TOP 10 * FROM T1 ORDER BY NEWID() 转载于:https://www.cnblogs.com/yukaizhao/archive/2007/03/27/essential_sql.html
