SQL 常用操作

1 SELECT COUNT(*)与SELECT COUNT(column_or_expression)的区别

SELECT COUNT(*):包括 NULL值;

SELECT COUNT(column_or_expression):不包括NULL值;

如果这两个都可以满足一需求时，并且在column上无Index时，建义用COUNT(*), 因为COUNT(*)能用到表上任意一个Index.

2, SQL JOINS（转）

感觉这篇文章，对初学SQL的人会有一些帮助原文：Visual Representation of SQL Joins

3, SQL SERVER　通过 FOR XML PATH子句，将多行转成一列（以逗号隔开）（转）

原方地址：Exploring Database Schemas on SQL Server

SELECT  t.TABLE_SCHEMA,
        t.TABLE_NAME,
        STUFF(( SELECT  ',' + COLUMN_NAME
                FROM    INFORMATION_SCHEMA.COLUMNS AS c
                WHERE   c.TABLE_SCHEMA = t.TABLE_SCHEMA
                        AND c.TABLE_NAME = t.TABLE_NAME
                ORDER BY c.COLUMN_NAME
              FOR
                XML PATH('')
              ), 1, 1, '') AS Columns
FROM    INFORMATION_SCHEMA.TABLES AS t

备注：Here we look up a list of tables and do a correlated subquery on the COLUMNS view to find out all the columns contained in that table. FOR XML PATH(‘’) causes all the results to be concatenated into a single value. The STUFF function simply removes the leading comma that would otherwise appear at the start of the list.

4, 获取数据库所有外键的SQL

SELECT 
  OBJECT_SCHEMA_NAME(f.parent_object_id) AS TableNameSchema, -- this
  OBJECT_NAME(f.parent_object_id) AS TableName,
  COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
  OBJECT_SCHEMA_NAME(f.referenced_object_id) AS ReferenceTableNameSchema,  -- this
  OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
  COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName,
  f.name AS ForeignKey
FROM
  sys.foreign_keys AS f
  INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
  INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

5,SQL Server 临时禁用和启用所有外键约束

    --获得禁用所有外键约束的语句  
    select  'ALTER TABLE ['  + b.name +  '] NOCHECK CONSTRAINT ' +  a.name +';' as  禁用约束     
    from  sysobjects  a ,sysobjects  b       
    where  a.xtype ='f' and  a.parent_obj = b.id  
      
    --获得启用所有外键约束的语句  
    select  'ALTER TABLE [' + b.name +  '] CHECK CONSTRAINT ' +  a.name +';' as  启用约束       
    from  sysobjects  a ,sysobjects  b       
    where  a.xtype ='f' and  a.parent_obj = b.id  

 

6,SQL在线格式化工具

Instant SQL Formatter

7,SQLSERVER恢复删除的数据（转dudu实战 SQL Server 2008 数据库误删除数据的恢复)

总共三个步骤：

• 备份当前数据库的事务日志：BACKUP LOG [数据库名] TO disk= N'备份文件名' WITH NORECOVERY
• 恢复一个误删除之前的完全备份：RESTORE DATABASE [数据库名] FROM DISK = N'完全备份文件名' WITH NORECOVERY,  REPLACE
• 将数据库恢复至误删除之前的时间点：RESTORE LOG [数据库] FROM  DISK = N'第一步的日志备份文件名' WITH   STOPAT = N'误删除之前的时间点' , RECOVERY

总的来说，在误删除数据后，做的第一件是就是备份数据库操作日志，并记下删除操作时间；然后恢复完全备份的数据库（所以在开发时也要养成备份数据库的习惯）；最后恢复操作日志到删除操作时间之前。

8, SQL UPDATE语句与INNER JOIN 结合

UPDATE T2SET ID=REPLACE(T.ID,'Start','TKStart') 
FROM T2
    INNER JOIN T ON T2.ID=T.ID

9, sql server 查询表结构（转自reglong sql server 查询表结构 ）

--1：获取当前数据库中的所有用户表
 select Name from sysobjects where xtype='u' and status>=0
 --2：获取某一个表的所有字段
 select name from syscolumns where id=object_id('表名')
 --3：查看与某一个表相关的视图、存储过程、函数
 select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
 --4：查看当前数据库中所有存储过程
 select name as 存储过程名称 from sysobjects where xtype='P'
 --5：查询用户创建的所有数据库
 select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
 或者
 select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
 --6：查询某一个表的字段和数据类型
 select column_name,data_type from information_schema.columns
where table_name = '表名' 
 --6.1 EXAMPLE
SELECT TABLE_NAME,column_name,NUMERIC_SCALE,data_type
FROM information_schema.columns
WHERE DATA_TYPE='decimal'
ORDER BY TABLE_NAME