• SQL 笔记

    sql常见函数：


    字符函数：
        length:    获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
        concat：    拼接字符串
        substr或者substring        截取字符串（具体怎么使用百度，从1开始）
        instr    返回子串在字符串中第一次出现的索引（没有返回0）
        trim    删除字符串前后空格（也可以自定义删除某个字符，百度）
        upper    字符串转换成大写
        lower    字符串转换成小写
        lpad    用指定的字符实现左填充指定的长度
        rpad    用指定的字符实现右填充指定的长度
        replace    字符串替换
        
    数学函数：
        round    四舍五入
        ceil    向上取整（返回>=该参数的最小整数）
        floor    向下取整（返回<=该参数的最大整数）
        truncate    截断，保存小数点后几位（用法百度）
        mod(a,b)    取余(结果和a的正负号相同)
        
    日期函数：
        now    返回当前系统日期
        curdate    返回当前系统日期，不包含时间
        curtime    返回当前时间，不包含日期
        year    返回当前时间，年
        month    返回当前时间，月
        monthname    返回当前时间，月份(英文)
        day    返回当前时间，天
        hour    返回当前时间，小时
        minute    返回当前时间，分钟
        second    返回当前时间，秒
        （重要）str_to_date        string类型转换成日期    
        （重要）date_format    日期转换为想要的string类型格式

    ____________________________________________________________________________

    1.保留指定位数（2）的数字

    1. select 
    2.     Convert(decimal(18,2),数字);

    2.获得当前时间向后推迟8个小时，转换成varchar，以111（2005/04/25）这种格式显示出来

    1. select
    2. 	convert(varchar(100),dateadd(hour,8,getdate()),111) 日期;
    3.  
    4. 其他常用的格式：
    5. Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
    6.  
    7. Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
    8.  
    9. Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
    10.  
    11. Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
    12.  
    13. Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
    14.  
    15. Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
    16.  
    17. Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
    18.  
    19. Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
    20.  
    21. Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
    22.  
    23. Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
    24.  
    25. Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
    26.  
    27. Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
    28.  
    29. Select CONVERT(varchar(100), GETDATE(), 12): 060516
    30.  
    31. Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
    32.  
    33. Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
    34.  
    35. Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
    36.  
    37. Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
    38.  
    39. Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
    40.  
    41. Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
    42.  
    43. Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
    44.  
    45. Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
    46.  
    47. Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
    48.  
    49. Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
    50.  
    51. Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
    52.  
    53. Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
    54.  
    55. Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
    56.  
    57. Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
    58.  
    59. Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
    60.  
    61. Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
    62.  
    63. Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
    64.  
    65. Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
    66.  
    67. Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
    68.  
    69. Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
    70.  
    71. Select CONVERT(varchar(100), GETDATE(), 112): 20060516
    72.  
    73. Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
    74.  
    75. Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
    76.  
    77. Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
    78.  
    79. Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
    80.  
    81. Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
    82.  
    83. Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM
    84.  
    85. Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

    3.CONVERT()函数，格式化日期

    https://blog.csdn.net/desertworm/article/details/126086145

    4.保留两位小数，多余的变成0

    1. select 
    2.     Round(12.321,2);

    5.        5.1根据查询出来字段的值显示成自已想展示的数据：

    1. select
    2.    case
    3.        dateadd(hour,7,getdate())
    4.        when getdate() then N'真的'
    5.        when dateadd(hour,8,getdate()) then N'假的'
    6.        when dateadd(hour,7,getdate()) then N'公的'
    7.        when dateadd(hour,6,getdate()) then N'母的'
    8.    end 我是列名字

              5.2判断查询结果是否为空来显示自定义值

    1. select 
    2.     case 
    3. 		when cpe.DTableFileName is null 
    4. 		then N'未维护' 
    5. 		else N'已维护' 
    6. 	end DTableFileName
    7. from 
    8. 	Cus_Product_Extend cpe

            5.3某字段不为空是展示该字段值，为空时根据其他字段的值来选择展示某值

    1. select
    2.     ISNULL(cpih.Shift,
    3.         case 
    4. 		    when DATENAME(hh,cpih.StartedTime) BETWEEN 8 AND 20   
    5. 			then N'白班' 
    6. 			else N'夜班' 
    7. 		end) Shift111
    8. from 
    9. 	Cus_Production_Issue_Header cpih

    6.查询出的结果进行拼接：

    1. SELECT 
    2.     t.ProductNo + '@' + t.ProductName key_,
    3.     t.ProductNo + '@' + t.ProductName Value_ 
    4. FROM 
    5.     v_product t
    6. ORDER BY t.ProductNo;

    7.MYSQL进行列和字符串的拼接（有结果为null的总体结果值不为null）

    1. select 
    2.     concat(列1,列2,列3...) 
    3. from
    4.     ... 

    8.MYSQL中插入’(单引号),用''''来插入

    select ''''        --结果为    ’

    9.Mysql省略小数点后面没用的0

    1. select 
    2.     convert(float,10.0000);

    10.计算周别、日期为一周的第几天、日期为周几？

    1. set datefirst 1     --设置周1为第一天
    2. select datepart(wk,convert(datetime,'2023-1-8'))    --输出日期的周别
    3. select datepart(weekday,convert(datetime,'2023-1-7'))    --输出日期为一周的第几天
    4. select datename(dw,convert(datetime,'2022-12-11'))    --输出日期为周几

    11.将行转换成列

    1. -- 建立一个全局临时表。临时表还是挺好用的哈，不用写入数据库中，可以用来学习且不用切换数据库
    2. CREATE TABLE ##TEST( 
    3. 	project VARCHAR(10), -- 课程 
    4. 	student VARCHAR(20), -- 学生
    5. 	score DECIMAL        -- 成绩
    6. )
    7.  
    8. INSERT INTO ##TEST VALUES(N'语文',N'小林',85)
    9. INSERT INTO ##TEST VALUES(N'语文',N'小龙',89)
    10. INSERT INTO ##TEST VALUES(N'语文',N'小龙',98)
    11. INSERT INTO ##TEST VALUES(N'数学',N'小林',95)
    12. INSERT INTO ##TEST VALUES(N'数学',N'小龙',90)
    13. INSERT INTO ##TEST VALUES(N'英语',N'小明',99)
    14. INSERT INTO ##TEST VALUES(N'英语',N'小林',97)
    15. INSERT INTO ##TEST VALUES(N'英语',N'小龙',98)
    16.  
    17. SELECT * FROM ##TEST 
    18.  
    19. SELECT * FROM ##TEST
    20. AS p  -- 将选中的字段作为原表，对应的t表就是新表。
    21. PIVOT(SUM(score) FOR project IN([语文],[数学],[英语])) AS t
    22.  
    23. DROP TABLE ##TEST

    12.将多行转换成一行，多行串联起来

    1. --stuff((select ';' + ep_name from ep_detail where ep_classes = a.ep_classes for xml path('')),1,1,'')
    2.  
    3. 举例：
    4. select ep_classes, ep_name = (stuff((select ',' + ep_name from ep_detail where ep_classes = 
    5.     a.ep_classes for xml path('')),1,1,'')) from ep_detail a group by ep_classes
    6.  
    7. create table ep_detail(
    8. ep_classes nvarchar(40),
    9. ep_name nvarchar(40)
    10. )
    11.  
    12. insert into ep_detail(ep_classes,ep_name) values('1','11')
    13. insert into ep_detail(ep_classes,ep_name) values('1','12')
    14. insert into ep_detail(ep_classes,ep_name) values('2','22')
    15.  
    16. select 
    17. 		ep_classes,
    18. 		ep_name = (stuff((select ';' + ep_name from ep_detail where ep_classes = a.ep_classes for xml path('')),1,1,'')) 
    19. from 
    20. 		ep_detail a 
    21. group by ep_classes

    13.关于使用order by排序出现“另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效”的解决办法 

    1. --使用    top 99.99 Percent 可保留排序，
    2.         --top 100 percent  不可保留排序
    3. select 
    4. 	top 99.99 Percent
    5. 	--top 100 percent
    6. 	csssds.WeekDay ,--星期,
    7. 	cd.ShiftDate ,--日期,
    8. 	cssss.SetDescr ,--默认班次,
    9. 	cssss.ShiftIDs
    10. from 
    11. 	Cus_Date2Period cd
    12. 	left join Cus_SHIFT_Shortcut_scheduling_Default_Set csssds ON datename(dw,convert(datetime,cd.ShiftDate)) = csssds.WeekDay
    13. 	left join Cus_SHIFT_Shortcut_scheduling_set cssss ON csssds.ShiftIDs = cssss.ShiftIDs
    14. where 
    15. 	cd.YearCode = 2023
    16. 	and cd.WeekCode = N'w2'
    17. --{conn}
    18. order by cd.ShiftDate desc
    19.  
    20.

    14.sql截取字符串

    1. SELECT 
    2. 	TOP 1 VALUE
    3. FROM 
    4. 	STRING_SPLIT ( N'1@2@3@4@5@6', '@' )
    5.  
    6. 结果：value
    7.         1
    8. -------------------------------------------------
    9. SELECT 
    10. 	*
    11. FROM 
    12. 	STRING_SPLIT ( N'1@2@3@4@5@6', '@' )
    13.  
    14. 结果：value
    15.         1
    16.         2
    17.         3
    18.         4
    19.         5
    20.         6

    15. 查询的结果中筛选掉另一个查询的结果

    1. SELECT 
    2. 	ROW_NUMBER() OVER(ORDER BY t.EquipmentCoding) rn,
    3. 	t.Id,
    4. 	t.EquipmentCoding Equipment,
    5. 	t.EquipmentName ,
    6. 	t.UserDepartment Department,
    7. 	t.Productionline Position
    8. FROM 
    9. 	v_Equipment_MasterDate t
    10. WHERE 
    11. 	NOT EXISTS (SELECT * FROM Cus_ESD_InspectionPlan_GoodsDetail a WHERE a.InspectionPlanId = 29 AND a.GoodsNo = t.EquipmentCoding)

    16.复杂的 update 语句怎么写？ 

    1. --1.先把想要修改的字段(作为表的字段1)和要改成的值(作为表的字段2)组成一张表(表tt)。
    2. --2.把要改的表和tt表链接起来查询一下验证是否正确。（如下sql所示）
    3. --3.把【select *】改成【update 表名 set 字段=值】就可以了。
    4. update aaa set aaa.ProductionIssueId = tt.ID
    5. from 
    6. 	Cus_Equipment_RepairOrder aaa
    7. 	right join (
    8. 		select 
    9. 			cer.ProductionIssueId,
    10. 			cpih.ID,
    11. 			cer.RepairOrderNo,
    12. 			cer.FaultDesc
    13. 		from 
    14. 			Cus_Equipment_RepairOrder cer 
    15. 			left join Cus_Production_Issue_Header cpih ON  cer.FaultDesc = cpih.IssueDescription 
    16. 		where
    17. 			cer.Active = 1
    18. 			and cpih.DataFrom is null
    19. 			and cer.RepairOrderNo != N'WX2302250002'
    20. 			and cpih.ID is not null
    21. 	) tt ON aaa.RepairOrderNo = tt.RepairOrderNo

    17.sql server 按照创建时间倒排的前提下让设备编号为SC开头的排在前面 怎么写？  

             在order by 下面用case when来自定义排序规则。列子：

    1. select 
    2. 		top 99.99 Percent
    3. 		Row_Number() over(order by 
    4. 			case 
    5. 				when left(cem.EquipmentCoding,2) = N'SC' then 0
    6. 				else 9
    7. 			end ,
    8. 			cem.CreatedOn desc) as rowid,    --序号
    9.         ,cem.EquipmentCoding                 --设备编号
    10.         ,cem.CreatedOn                       --创建时间
    11. from 
    12.     cus_equipment_MasterDate cem
    13. order by 
    14. 	case 
    15. 		when left(cem.EquipmentCoding,2) = N'SC' then 0
    16. 		else 9
    17. 	end,
    18. 	cem.CreatedOn desc

    18.Sql Server表明前加#和##号是什么意思？

    1. //加#是创建一个本地临时表：
    2. //    只可在本地使用，当用户断开连接时没有除去零时表，sql server会帮忙自动清楚。
    3. //加##是创建一个全局临时表：
    4. //    所有连接上都能看到全局临时表,只要创建全局临时表 的连接断开，全局临时表即被除去。

    19.Select into 和 Insert into 的用法和区别。

    1. select 
    2. 	EquipmentCoding,CreatedOn
    3. into #Equipment20230509
    4. from cus_equipment_MasterDate
    5.  
    6. --Select into 是将查询的结果复制到目标表里(表事先不存在，会创建一个新表)
    7.  
    8. -----------------------------------------------------------------------
    9. -----------------------------------------------------------------------
    10.  
    11. insert into #Equipment20230509
    12. select EquipmentCoding,CreatedOn from cus_equipment_MasterDate
    13.  
    14. --insert into 是将查询的结果插入到目标表里(表实现要已存在)

     20.Delete , truncate ，Drop 的用法和区别。

    1. --delete     可以逐行删除，删除可以增加where条件，可以回滚，自增id不会清零
    2. --truncate   只能删除表内所有数据，不能回滚，自增id清零
    3. --Drop       删除表
    4.  
    5. 语法：delete from 表名；
    6.  
    7. 语法：truncate table 表名；
    8.  
    9. 语法：drop table 表名;

    21.SQL Server 修改字段长度 阻止保存要求重新创建表的更改 

    1. 1.需要选择SQL Server Management Studio 的 工具 -> 选项
    2. 2.点击选项后，在打开的界面里面选择 Designers -> 表设计器和数据库设计器
    3. 3.将阻止保存要求重新创建表的更改 前面的勾取消，点击确认即可解决问题了
    4. 4.确认成功后，即可以正常修改表字段的长度了

    22.随机抽取N条数据

    1. select 
    2.     Top N
    3.     * 
    4. from 
    5.     [表]
    6. order by NEWID();
    7.  
    8. --    order by NEWID()可以随机排序；

    23.插入换行符

    1. //插入Char(13)来换行
    2.  
    3. update cus_equipment_MasterDate 
    4.     set Remark = N'111'+CHAR(13)+'111' 
    5. where 
    6.     EquipmentCoding = N'SC-SMT-00001'

    24.两个字段值相加其中某个字段存在Null时怎么处理？

    1. select
    2.     IsNull(Hours1,0)+Isnull(Hours2,0) SumHours
    3. from
    4.     Table;

    25.怎么实现4位数不足4位前面补0

    select right(10000+10,4) Number

    26.事务的使用

    1. begin TransAction      --开始事务
    2. delete HSX_Department_Ext where Department = N'Test2024-03-27 23:04:25'
    3. delete HSX_Department_Ext where Department = N'Test2024-03-27 00:00:00'
    4.  
    5. ROLLBACK TRANSACTION   --回滚事务
    6. COMMIT TRANSACTION     --提交事务

    27.使用SQL Server链接远程服务器进行查询

    select * from [AAA].[BBB].[dbo].[表名];

    28.将值组合成一个表 

    SELECT value FROM STRING_SPLIT(N'A|B|C|D', '|')

    29.表的创建和索引的创建 

    1. --怎么创建表
    2. create table 表名 (
    3. 	ID				int	IDENTITY(1,1) PRIMARY KEY	NOT NULL,  --IDENTITY(1,1) 自增；PRIMARY KEY主键；NOT NULL 必填
    4. 	CustomerCode	nvarchar(40)	NOT NULL,
    5. 	PlantId			nvarchar(64)	,
    6. 	PassRateTarget	decimal(18,6)	NOT NULL,
    7. 	ProgressStatus	int				NOT NULL
    8. );
    9.  
    10. --创建一般索引(Index)
    11. CREATE INDEX 索引名
    12. ON 表名 (column1, column2, ...);
    13.  
    14. --创建唯一索引(Unique Index)
    15. CREATE UNIQUE INDEX 索引名
    16. ON 表名 (column1, column2, ...);

    30.SQL Server递归并增加递归深度

    1. WITH EmployeeHierarchy AS
    2. (
    3.     -- 锚点成员：获取没有经理的员工（即公司的顶级领导）
    4.     SELECT EmployeeID, Name, ManagerID, 1 AS Level
    5.     FROM Employees
    6.     WHERE ManagerID IS NULL
    7.     UNION ALL
    8.     -- 递归成员：根据经理与员工的关系，获取下一层级的员工，并增加层级数
    9.     SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1 AS Level
    10.     FROM Employees e
    11.     JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
    12. )
    13. SELECT * FROM EmployeeHierarchy
    14. --设置最大递归深度
    15. --OPTION (MAXRECURSION 0)

    ———————————————————————————————————————————

    1.给表添加字段

    1. alter table 
    2.     表名 
    3. add 
    4.     字段名 数据类型

    2.修改表的字段id为自增

    1. --先删除字段id
    2.  
    3. 增加字段id并设置为自增
    4. alter table Cus_ESD_IncomingInspection add id int IDENTITY (1,1)
    5.  
    6. 增加字段id并设置为自增主键
    7. alter table Cus_ESD_IncomingInspection add id int IDENTITY (1,1) PRIMARY KEY

    3.sql server修改数据库表报错(不允许保存更改)

    原因: 

    解决办法：

  • 相关阅读:
    C++学习笔记之四（标准库、标准模板库、vector类）
    一篇文章彻底理解 HDFS 的安全模式
    Python第一次作业练习
    LeetCode 251：展开二维向量
    【资损】资损防控的系统规范-服务接口类设计
    18-Linux系统服务
    如何为谷歌seo打好基础？
    Real-Time Rendering——9.9.4 Rough-Surface Subsurface Models粗糙表面地下模型
    SpringBoot入门教程：浅聊POJO简单对象(VO、DTO、Entity)
    openresty 性能优化
  • 原文地址：https://blog.csdn.net/DTJ990226/article/details/127092255