常用语句

一 背景

一些常用语句经常忘记，于是整合记录下来。

1.1 SQL版本

数据库：
Microsoft SQL Server 2019 (RTM-GDR) (KB5029377) - 15.0.2104.1 (X64)   Aug 16 2023 00:09:21   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home China 10.0 <X64> (Build 22000: ) 

 

二 表格操作

 1 SELECT @@VERSION   -- 查看版本信息
 2 
 3 use cnblogs
 4 
 5 -- 删表
 6 DROP TABLE IF EXISTS cnblogs.common.calendar;
 7 
 8 -- 建表
 9 CREATE TABLE cnblogs.common.calendar
10 (
11 [id] int constraint pk_id PRIMARY KEY identity ,
12 [date] date constraint un_date UNIQUE  ,
13 [year] int,
14 [month] int,
15 [weeknumber] int,
16 [weekday] int,
17 [day] int,
18 M_n varchar
19 )
20 ;
21 -- 插入数据
22 insert into cnblogs.common.calendar([date])
23 values ('2024-01-01');
24 
25 
26 -- 创建索引
27 CREATE INDEX index_day ON cnblogs.common.calendar([day]);
28 
29 
30 -- 查看索引
31 exec sp_helpindex 'cnblogs.common.calendar';
32 
33 -- 删除索引
34 drop index index_day on cnblogs.common.calendar
35 
36 
37 -- 查看约束，'common.calendar'中没有库名
38 exec sp_helpconstraint @objname = 'common.calendar'
39 
40 -- 删除约束
41 ALTER TABLE cnblogs.common.calendar DROP CONSTRAINT pk_id;
42 ALTER TABLE cnblogs.common.calendar DROP CONSTRAINT un_date;
43 
44 -- 删除字段
45 ALTER TABLE cnblogs.common.calendar DROP COLUMN [M_n];
46 
47 
48 -- 添加字段
49 
50 ALTER TABLE cnblogs.common.calendar ADD [M_n] NVARCHAR(20);
51 
52 -- 更改字段
53 ALTER TABLE cnblogs.common.calendar 
54 ALTER COLUMN [DATE] DATE NOT NULL;
55 
56 
57 -- 添加约束
58 ALTER TABLE cnblogs.common.calendar 
59 ADD CONSTRAINT pk_id2 PRIMARY KEY(id)
60 ALTER TABLE cnblogs.common.calendar 
61 ADD CONSTRAINT un_date2 UNIQUE([date])
62 ;
63 
64 -- 创建索引
65 CREATE INDEX index_day2 ON cnblogs.common.calendar([day]);
66 
67 
68 -- 循环插入数据
69 BEGIN
70   DECLARE @start_date DATE                --定义变量
71   DECLARE @end_date DATE 
72   DECLARE @day_num int 
73   DECLARE @id int 
74   SET @start_date = CAST('2024-01-02' AS DATE)
75   SET @end_date = CAST('2024-12-31' AS DATE)
76   SET @day_num = DATEDIFF(DAY,@start_date,@end_date)
77   SET @id = 0
78   WHILE @id <= @day_num
79     BEGIN
80       INSERT INTO cnblogs.common.calendar([date])
81       VALUES (DATEADD(DAY,@id,@start_date));
82       SET  @id = @id+1
83     END
84 
85 END;
86 
87 
88 SET DATEFIRST 1;   -- 设置周一（1）是一周的第一天，默认周日（7）是第一天
89 UPDATE cnblogs.common.calendar SET [year] = YEAR([date])               
90 UPDATE cnblogs.common.calendar SET [month] = month([date])
91 UPDATE cnblogs.common.calendar SET [weeknumber] = datepart(WEEK,[date])
92 UPDATE cnblogs.common.calendar SET [weekday] = DATEPART(WEEKDAY,[date])
93 UPDATE cnblogs.common.calendar SET [day] = day([date])
94 UPDATE cnblogs.common.calendar SET [M_n] = LEFT(date,7)
95 ;
96 -- SELECT * FROM cnblogs.common.calendar order by id

 

三 查询

3.1 开窗函数

参考：https://www.cnblogs.com/qianslup/p/15113955.html

SELECT  *, 
ROW_NUMBER() OVER (PARTITION BY [WEEKDAY] ORDER BY id) as RN, 
RANK() OVER (PARTITION BY [WEEKDAY] ORDER BY id) as RA,
DENSE_RANK() OVER (PARTITION BY [WEEKDAY] ORDER BY id) as DRA
FROM cnblogs.common.calendar

 3.2 跨表更新

 

ALTER TABLE cnblogs.common.calendar ADD [星期] NVARCHAR(20);

update A SET a.星期 = B.星期
FROM cnblogs.common.calendar AS A,
cnblogs.common.week_info B
WHERE A.[weekday] = B.[weekday]

 

posted @ 2025-03-10 10:58  qsl_你猜  阅读(12)  评论(0)    收藏  举报