SQL Server数据库知识点整理

0、在SQLSERVER安装目录下【H:\DataBase\SQLServer\MicrosoftSQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\】找到需要还原的文件(*.mdf和*.LDF)，如BJ808-GB.mdf、BJ808-GB.LDF文件，将这两个文件复制到需要还原的数据库安装对应目录下，在登录数据刷新一下数据库即可出现已还原的数据库。

1、查询表格新增序号：ROW_NUMBER()OVER(ORDER BY LOGID)

  实例：SELECT ROW_NUMBER()OVER(ORDER BY A00) AS NUMBER FROM A01

2、查询指定行数据

SELECT * FROM(SELECT JXKH2202,NUMBER = ROW_NUMBER() OVER (ORDER BY JXKH2200) FROM JXKH22 WHERE JXKH2212 = '1')J22 WHERE NUMBER = 1

3、获取property文件值。

String dbtype = System.getProperty("dataBaseType");

4、日期转换，比如2021-01-17 21:42:40

SELECT CONVERT(VARCHAR(20),LOGTIME,120) FROM BAP_OPERATIONLOG;

Select CONVERT(varchar(100), GETDATE(), 0);--;-- 05 16 2006 10;--57AM 
Select CONVERT(varchar(100), GETDATE(), 1);-- 05/16/06 
Select CONVERT(varchar(100), GETDATE(), 2);-- 06.05.16 
Select CONVERT(varchar(100), GETDATE(), 3);-- 16/05/06 
Select CONVERT(varchar(100), GETDATE(), 4);-- 16.05.06 
Select CONVERT(varchar(100), GETDATE(), 5);-- 16-05-06 
Select CONVERT(varchar(100), GETDATE(), 6);-- 16 05 06 
Select CONVERT(varchar(100), GETDATE(), 7);-- 05 16, 06 
Select CONVERT(varchar(100), GETDATE(), 8);-- 10;--57;--46 
Select CONVERT(varchar(100), GETDATE(), 9);-- 05 16 2006 10;--57;--46;--827AM 
Select CONVERT(varchar(100), GETDATE(), 10);-- 05-16-06 
Select CONVERT(varchar(100), GETDATE(), 11);-- 06/05/16 
Select CONVERT(varchar(100), GETDATE(), 12);-- 060516 
Select CONVERT(varchar(100), GETDATE(), 13);-- 16 05 2006 10;--57;--46;--937 
Select CONVERT(varchar(100), GETDATE(), 14);-- 10;--57;--46;--967 
Select CONVERT(varchar(100), GETDATE(), 20);-- 2006-05-16 10;--57;--47 
Select CONVERT(varchar(100), GETDATE(), 21);-- 2006-05-16 10;--57;--47.157 
Select CONVERT(varchar(100), GETDATE(), 22);-- 05/16/06 10;--57;--47 AM 
Select CONVERT(varchar(100), GETDATE(), 23);-- 2006-05-16 
Select CONVERT(varchar(100), GETDATE(), 24);-- 10;--57;--47 
Select CONVERT(varchar(100), GETDATE(), 25);-- 2006-05-16 10;--57;--47.250 
Select CONVERT(varchar(100), GETDATE(), 100);-- 05 16 2006 10;--57AM 
Select CONVERT(varchar(100), GETDATE(), 101);-- 05/16/2006 
Select CONVERT(varchar(100), GETDATE(), 102);-- 2006.05.16 
Select CONVERT(varchar(100), GETDATE(), 103);-- 16/05/2006 
Select CONVERT(varchar(100), GETDATE(), 104);-- 16.05.2006 
Select CONVERT(varchar(100), GETDATE(), 105);-- 16-05-2006 
Select CONVERT(varchar(100), GETDATE(), 106);-- 16 05 2006 
Select CONVERT(varchar(100), GETDATE(), 107);-- 05 16, 2006 
Select CONVERT(varchar(100), GETDATE(), 108);-- 10;--57;--49 
Select CONVERT(varchar(100), GETDATE(), 109);-- 05 16 2006 10;--57;--49;--437AM 
Select CONVERT(varchar(100), GETDATE(), 110);-- 05-16-2006 
Select CONVERT(varchar(100), GETDATE(), 111);-- 2006/05/16 
Select CONVERT(varchar(100), GETDATE(), 112);-- 20060516 
Select CONVERT(varchar(100), GETDATE(), 113);-- 16 05 2006 10;--57;--49;--513 
Select CONVERT(varchar(100), GETDATE(), 114);-- 10;--57;--49;--547 
Select CONVERT(varchar(100), GETDATE(), 120);-- 2006-05-16 10;--57;--49 
Select CONVERT(varchar(100), GETDATE(), 121);-- 2006-05-16 10;--57;--49.700 
Select CONVERT(varchar(100), GETDATE(), 126);-- 2006-05-16T10;--57;--49.827 
Select CONVERT(varchar(100), GETDATE(), 130);-- 18 ???? ?????? 1427 10;--57;--49;--907AM

5、字符串转数字,使用函数CONVERT

  SELECT CONVERT(NUMERIC(10,2),JXKH0503) AS JXKH0503 FROM JXKH05

  如果值为空，则显示0

 SELECT ISNULL(CONVERT(NUMERIC(10,0),JXKH0503),0) AS SCORE FROM JXKH05

6、修改字段长度

ALTER TABLE JXKH_PSKHDCPD ALTER COLUMN XJNR VARCHAR(4000)

7、修改表中字段名称：EXEC SP_RENAME '表A.列','新列名','COLUMN';

 EXEC SP_RENAME 'JXKH_JXQJ.DESC','DESCS','COLUMN';

8、SQL自动生成UUID

    SELECT CAST(REPLACE(NEWID(), '-', '-') AS VARCHAR(36)) AS UUID

9、对比两个库中不相同的表，A库中10表，B库中8张表，找出其他2张表。

     SELECT * FROM A_GBZ_GB_QSTG_2020_PSKH.SYS.TABLES WHERE NAME NOT IN (SELECT NAME FROM A_GBZ_GB_QSTG_2020.SYS.TABLES)

10、把一个库中表添加到另一个库中，如下

      SELECT  'SELECT *  INTO  B.dbo.'+name+'  FROM A.dbo.'+name FROM sys.tables

11、查询身份证号码重复的数据

     SELECT * FROM A01 WHERE A0184 IN (SELECT A0184 FROM A01 GROUP BY A0184 HAVING COUNT(A0184) > 1);

12、SQL SERVER中数字小数保留后两位(四舍五入)，使用的函数CONVERT，示例如下

 SELECT 
    CONVERT(NUMERIC(10,2),A.DCSZBL) AS DCSZBL
FROM JXKH_CYRSSJTJ A

12、将数据库表结构及数据复制到另一张表中
(1)Oracle中，如果目标表不存在：
   CREATE TABLE 目标表 AS SELECT * FROM 原表;
   Oracle中，如果目标表存在：
   INSERT INTO 目标表 SELECT * FROM 原表;
(2)SQL SERVER中，如果目标表不存在：
   SELECT * INTO 目标表 FROM 原表;
   SQL SERVER中，如果目标表存在：
   INSERT INTO 目标表 SELECT * FROM 原表;

13、根据字段值拼接INSERT语句

SELECT 
      'INSERT INTO PSKH65(JXKH6500,B00,JXKH6501,JXKH6502,JXKH6504,PXH,ZDYXB0156)VALUES('''+CONVERT(varchar(50),B00)+''','''+CONVERT(varchar(50),B0101)+''','''+CONVERT(varchar(50),B0104)+''','''+CONVERT(varchar(50),B0144B)+''','''+CONVERT(varchar(50),BJXB0168)+''','''+CONVERT(varchar(50),B0131)+''')'';''' AS UPDATESA,
       B00, 
       B0101 AS JXKH6501,
       B0104 AS JXKH6502,
       B0144B AS JXKH6504,
       BJXB0168 AS PXH,
       B0131 AS ZDYXB0156
FROM B01
WHERE 
B0144B IN ('A109439F-DBBD-40E7-98F1-9EA44A3BB5B5','BBA35F16-5762-4B16-B50C-3A270D8FBD12')

14、sqlserver复制表
    sqlserver中通用的两种复制表的两种方法
  （1）目标表不存在
   select  * into 目标表 from 原表
   （2）目标表存在只复制原表中的数据
   insert into 目标表 select * from 原表

15、查询重复数据：

SELECT * FROM JXKH_PSKHDCPD WHERE A0184 IN (SELECT A0184 FROM JXKH_PSKHDCPD GROUP BY A0184 HAVING COUNT(*) > 1) ORDER BY A0184;

16、查询表死锁

SELECT  
      DISTINCT
      --REQUEST_SESSION_ID AS 进程,
      --OBJECT_NAME(RESOURCE_ASSOCIATED_ENTITY_ID) 被锁表名,
      'KILL '+ CAST(REQUEST_SESSION_ID AS NVARCHAR(36)) +';'  AS 执行死锁语句
FROM SYS.DM_TRAN_LOCKS 
WHERE RESOURCE_TYPE = 'OBJECT';