SQL 基础知识

常规操作之笔记摘要！！

本文用于总结SQL Server数据库中最常用、最基础的语法和用法，查缺补漏便于日常使用

SQL--事务回滚，SQL语句写在中间，不会执行。

begin tran  
rollback tran --》作用：数据库执行更新语句用于回滚

SQL-使用子查询写入数据

  begin tran
	 INSERT INTO your_table (列1, 列2)SELECT 列1, 列2 FROM your_table WHERE 条件
 rollback tran

SQL--判定是否存在表并写入数据(例如：删除数据时，需要提前把数据备份到表，方便后期还原)，此方法无需创表SQL语句

IF not EXISTS (SELECT * FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_NAME = '自定义表_bak')
BEGIN
select *into 自定义表_bak From 数据库表    //此处自定义表===数据库表方便识别
END

SQL--创建索引

SQL Server基础知识之创建索引 CREATE INDEX 索引名称 ON 数据表(数据表字段); 

SQL--判定删除数据库字段

ALTER TABLE 表名DROP COLUMN SYSName 

SQL--判定添加数据库字段

if NOT EXISTS (SELECT * FROM dbo.syscolumns WHERE id=OBJECT_ID(N'[表名]') AND name='字段')
begin
    alter table 表名 add   字段 nvarchar(50);
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'字段中文描述', @level0type=N'SCHEMA',@level0name=N'dbo',
    @level1type=N'TABLE',@level1name=N'表名',
     @level2type=N'COLUMN',@level2name=N'字段';
End

SQL--判定数据库是否存在表

IF EXISTS ( SELECT 1  FROM sys.tables  WHERE name = '表名称')
   --如果表存在
    begin 
        IF EXISTS (select Count(*) from 表名称 )
        --表中有数据
        select '11'
        ELSE
        --没有数据
        select '22'
    end
ELSE
    --表不存在
    begin
       select '1111'
    end

SQL--数据库连表更新数据

【SQL SERVER】
UPDATE a 
SET a.字段=b.字段 
FROM 表1 a  INNER JOIN 表1 b  
ON  a.字段=b.字段

【MYSQL】
UPDATE  表1 a  
INNER JOIN 表2 b ON a.字段 = b.字段
SET a.字段 = b.字段
WHERE a.字段=''

SQL--判定修改数据格式

if EXISTS (SELECT * FROM dbo.syscolumns WHERE id = OBJECT_ID(N'[表名称]') AND name='表字段')
begin
  alter table 表名称 alter column 表字段 nvarchar(200);
end
go

SQL--SELECT name FROM SysColumns查询数据库列名称字段顺序注意事项

在sql2008是按照顺序输出的，在sql2019中是乱序的，需要加【order by colorder】
SELECT name FROM SysColumns WHERE id=Object_Id('表名称') order by colorder

SQL--STUFF用法以及输出时有双引号

加双引号
 STUFF((select distinct ','''+CONVERT(varchar(100),表字段)+'''' from 表名称 BG   where 表字段=关联表字段 for xml path('')),1,1,'') aaaa

SQL中SUBSTRING函数(截取)，参照以下例子

例子1：截取2023年10月05日～2023年11月02日的第二个日期
    DECLARE @yourString NVARCHAR(MAX) = '2023年10月05日～2023年11月02日';
    -- 找到第一次出现年份的位置
    DECLARE @firstYearIndex INT = CHARINDEX('年', @yourString);
    -- 找到第二次出现年份的位置，从第一次出现年份之后开始搜索
    DECLARE @secondYearIndex INT = CHARINDEX('年', @yourString, @firstYearIndex + 1);
    -- 提取第二次出现年份之后的部分
    DECLARE @afterSecondYear NVARCHAR(MAX) = SUBSTRING(@yourString, @secondYearIndex-4, @secondYearIndex + 1);
    -- 提取第二次出现年份的前四位数字
    DECLARE @secondYear NVARCHAR(4) = LEFT(@afterSecondYear, 4);
    SELECT @afterSecondYear AS second_year;
例子2：截取2023年10月05日～2023年11月02日的第二个年之前的全部
    DECLARE @yourString NVARCHAR(MAX) = '2023年10月05日～2023年11月02日';
    -- 找到第一次出现年份的位置
    DECLARE @firstYearIndex INT = CHARINDEX('年', @yourString);
    -- 找到第二次出现年份的位置，从第一次出现年份之后开始搜索
    DECLARE @secondYearIndex INT = CHARINDEX('年', @yourString, @firstYearIndex + 1);
    -- 提取第二次出现年份之前的部分
    DECLARE @extractedPart NVARCHAR(MAX) = SUBSTRING(@yourString, 1, @secondYearIndex - 1);
    SELECT @extractedPart AS extracted_part;

SQL 语句查询【Test】字段中以逗号隔开并且存在多个的情况的数据，然后取最小的一个值

SELECT 
    Test,
    MIN(CAST(x.value('.', 'INT') AS INT)) AS min_value
FROM 【数据表】
CROSS APPLY (
    SELECT CAST('<x>' + REPLACE(Test, ',', '</x><x>') + '</x>' AS XML) AS xml_data
) AS t
CROSS APPLY xml_data.nodes('/x') AS split(x)
WHERE Test LIKE '%,%'  -- 确保有多个值
GROUP BY Test;

SQL SERVER 获取表字段中逗号的数量

select 
LEN(字段) - LEN(REPLACE(字段, ',', '')) AS CommaCount
,LEN(字段) - LEN(REPLACE(字段, ',', '') + 'x') + 1 AS CommaCount_XML  -- 使用XML方法计算逗号数量
,*  from  表