SQLServer从入门到高级(知识点)

SQLServer从入门到高级(知识点)

SQLServer最大限制

1 每个数据库最多有20亿张表

2 每个表（视图）最多有1024列

3 表名和列名的最大长度位128个字符

4 一个对象最多可以有1000个分区

5 一个索引最多可以使用16列，且索引键最大为 900 字节

提高sql查询效率的方法

1、流水表，基础表    用连接去做  流水表 left join ji

2、 连接的效率 >> 子查询

取值范围

between 1 and 3  -----> [1, 3]

rand()  -----> [0, 1)
               
top 30	-- 1-3包括第30条记录

删除重复行

• 方法一

delete from t_setlinfo 
where id not in(
            select MIN(id) -- id在此表中是自增列，主键
            from t_setlinfo t
            group by fixmedins_code,mdtrt_sn
            )

根据fixmedins_code, mdtrt_sn两个唯一的字段分组确定重复的行，在每组重复的行中选出id值最小的那个，

最后删除掉除最小的这个id值（即删除重复的行，保留一行）

• 方法二

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY key_value
              ORDER BY (SELECT NULL)
            )
FROM original_table
) AS T
WHERE DupRank > 1

此脚本按给定顺序执行以下操作：

• 使用 ROW_NUMBER 函数根据 key_value（可能是以逗号分隔的一列或多列）对数据进行分区。
• 删除所有收到大于 1 的 DupRank 值的记录。 此值指定记录是重复项。

由于 (SELECT NULL) 表达式的原因，脚本不会根据任何条件对分区数据进行排序。 如果删除重复项的逻辑需要根据其他列的排序顺序选择要删除和保留的记录，则可以使用 ORDER BY 表达式来执行此操作。

随机抽取50条数据

需求：从Question表中随机抽取50道题

select top 50 *
from Question 
order by NEWID()

1 sql语句执行顺序

sql执行顺序为from——>where——>group by——>having——>select——>order by

2 基本类型

整形：smallint、 int、 bigint 、tinyint、bit

浮点型：real、float

字符型：char、 nchar、varchar、nvarchar、

日期：datetime、smalldatetime

图片：imag

货币类型：money、smallmoney

长文本：text 、ntext

numeric（10.2）意思是：保留十位有效数字精确到小数点后两位

-- char 定长， char(10) 无论存储的数据是否有10字节都会占10字节，不够自动空格补齐
-- varchar 变长，varchar(10)最多占用10个。 varchar存放字节的范围1-8000
-- text 长文本
-- NCHAR、NVARCHAR、NTEXT 

	NCHAR、NVARCHAR、NTEXT。它表示存储的是Unicode数据类型的字符。
	我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。
	与char、varchar比较起来，nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

3 聚合函数

sql中分组函数在使用时必须先进行分组，然后才能用，如果没有对数据进行分组，则默认整张表为一组。
分组函数：

• count()计数
• sum()求和
• avg()求平均
• max()
• min()

sqlserver函数：SqlServer函数_百度百科 (baidu.com)

4 日期函数

4.1 取出日期

• year(RegisterDate) 取出年份

• month(RegisterDate) 取出月份

• day(RegisterDate) 取出日

• datename(datepart, date-expression)

  datepart类似，但返回值为nvarchar类型

• datepart(datepart, date-expression)

  日期/时间函数，返回表示日期/时间表达式指定部分的值的整数（int类型）。参数列表：

Date Part	Abbreviations	Return Values
year	yyyy, yy	0001-9999
quarter	qq, q	1-4
month	mm, m	1-12
week	wk，ww	1-53
weekday	dw	1-7 (Sunday,…,Saturday)
dayofyear	dy, y	1-366
day	dd, d	1-31
hour	hh	0-23
minute	mi，n	0-59
second	ss,s	0-59
millisecond	ms	0-999 (with precision of 3)
microsecond	mcs	0–999999 (with precision of 6)
nanosecond	ns	0–999999999 (with precision of 9)
sqltimestamp	sts	SQL_TIMESTAMP: yyyy-mm-dd hh:mm:ss

例如
-- 返回年
select datepart(YY, getdate()) -- 2023
select datepart(mm, getdate()) -- 2023

参考链接：第四十五章 SQL函数 DATEPART_yaoxin521123的博客-CSDN博客

4.2 日期的加减

• 对日期的加减dayteadd(month, +1, RegisterDate)
  三个参数：要操作类型, 加多少天, 对谁加

dateadd函数：DateAdd(interval, number, date)返回的是一个日期数据，函数的三个形参都是必要。
interval表示时间的间隔可以是yy（表示年），mm（表示月），dd（表示日），qq（表示季度），ww（表示周）；
number表示间隔的数值，正数表示日期数据date的未来，负数表示日期数据大特的从前。
date为日期数据，一般就是数据表里面的某个日期字段如birthday生日字段。
示例 Select dateadd(dd, 3, ‘2008-10-05’) 得到的结果就是2008-10-08.

4.3 计算日期间隔

• datediff函数： 计算两个时间的间隔

  DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]]) 返回的是一个数值，这个数值表示date1和date2之间相隔时间间隔interval的数值。

  interval表示时间间隔，可以是yy（表示年），mm（表示月），dd（表示日），qq（表示季度），ww（表示周），hh（表示小时），n（表示分钟），s（表示秒）

  date1, date2是计算中进行计算的两个日期

  Firstdayofweek 可选。指定一个星期的第一天的常数。如果未予指定，则以星期日为第一天。

  firstweekofyear 可选。指定一年的第一周的常数。如果未予指定，则以包含 1 月 1 日的星期为第一周。

  select datediff(year, 开始日期,结束日期);     --两日期间隔年 
  select datediff(quarter, 开始日期,结束日期); --两日期间隔季 
  select datediff(month, 开始日期,结束日期); --两日期间隔月 
  select datediff(day, 开始日期,结束日期); --两日期间隔天 
  select datediff(week, 开始日期,结束日期); --两日期间隔周 
  select datediff(hour, 开始日期,结束日期); --两日期间隔小时 
  select datediff(minute, 开始日期,结束日期); --两日期间隔分 
  select datediff(second, 开始日期,结束日期); --两日期间隔秒
  

4.4 计算某一天有多少条记录(方法总结)

-- 计算某一天有多少条记录(方法总结)
-- 方法一 利用year，month，day函数
select * from test2 where YEAR(ftime)=2023 and month(ftime)=6 and day(ftime) = 16;
-- 方法二 利用字符串比较
select * from test2 where CONVERT(varchar(10),ftime, 120 ) = '2023-06-16';
-- 方法三 between and
select * from test2 where ftime between '2023-06-16' and '2023-06-17'

4.5 获取月初月末

declare @time1 datetime;
declare @time2 datetime;
declare @time3 datetime;
declare @time4 datetime;
set @time1 = DATEADD(mm,DATEDIFF(m,0,getdate())-1,0);   -- 上月第一天
set @time2 = DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate()),0)); -- 上月最后一天
set @time3 = cast(convert(date, DATEADD(day,DATEDIFF(d,0,getdate())-1,0), 23) as varchar)+' 00:00:00';  -- 昨天开始
set @time4 = cast(convert(date, DATEADD(day,DATEDIFF(d,0,getdate())-1,0), 23) as varchar)+' 23:59:59'; -- 昨天结束

select  DATEADD(mm,DATEDIFF(m,0,getdate()),0);   -- 本月第一天

select DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m, -1, getdate()),0)); -- 本月最后一天

select @time1 上个月第一天, @time2 上个月最后一天, @time3 昨天开始, @time4 昨天结束
---------- 输出结果 ----------
    -- 上个月第一天
    2023-05-01 00:00:00.000
    -- 上个月最后一天
    2023-05-31 23:59:59.997
    -- 昨天开始
    2023-06-28 00:00:00.000
    -- 昨天结束
    2023-06-28 23:59:59.000

5 行列转换（不常用！！）

5.1 行转列PIVOT

PIVOT 操作可以将行数据转换为列，适用于已知列值的情况。它基于聚合函数对某一列进行分组，并将这些分组的结果作为新的列。下面是一个简单的示例：

SELECT *
FROM (
    SELECT Column1, Column2
    FROM TableName
) AS SourceTable
PIVOT (
    SUM(Column2)
    FOR Column1 IN (Value1, Value2, Value3) -- 列的取值列表
) AS PivotTable;

上述示例中，Column1 列的不同取值(Value1, Value2, Value3)会被转换为 PivotTable 的列。

完整语法：

table_source
 
PIVOT(
 
聚合函数（value_column）
 
FOR pivot_column
 
IN(<column_list>)