SQL 数据库知识总结

最新推荐文章于 2021-04-25 21:41:34 发布

小小菜鸟cs

最新推荐文章于 2021-04-25 21:41:34 发布

阅读量393

点赞数 1

文章标签：存储过程 sqlserver MMSQLSERVER

sqlserver

基础

1 创建别名和自定义类型

create type AS ( attribute_name data_type [, ... ] )

2 系统类型表

每个系统类型和用户定义类型都在表中对应一行。

sys.types

3 数据库架构表

sys.schemas (Transact-SQL)

每个数据库架构都对应一行。

架构是形成单个命名空间的数据库实体的集合。命名空间是一个集合，其中每个元素的名称都是唯一的。

比如dbo

6 系统表

sysobjects
在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行。只有在 tempdb 内，每个临时对象才在该表中占一行。

7 对象的唯一ID

OBJECT_ID

返回架构范围内对象的数据库对象标识号。

系统给表分配的唯一ＩＤ

object_id就是根据对象名称返回该对象的id.

8 数据库架构对象的相关信息

objectproperty (Transact-SQL)

返回当前数据库中架构范围内的对象的相关信息。有关架构范围内对象的列表，请参阅 sys.objects (Transact-SQL)。不能将此函数用于不属于架构范围内的对象，如数据定义语言 (DDL) 触发器和事件通知。

OBJECTPROPERTY ( id , property )

9 使 SQL Server 遵从关于引号分隔标识符和文字字符串的 ISO 规则

SET quoted_identifier (Transact-SQL)

当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。当 SET QUOTED_IDENTIFIER 为 OFF 时，标识符不可加引号，且必须遵守所有 Transact-SQL 标识符规则。”

10 指定Null 值使用

SET ANSI_NULLS ON

指定在 SQL Server 2017 中与 Null 值一起使用等于 (=) 和不等于 (<>) 比较运算符时采用符合 ISO 标准的行为。

当 ANSI_NULLS 为 ON 时，即使包含空值，使用 WHERE column_name = NULL 仍返回零行。

当 ANSI_NULLS 为 OFF 时，使用 WHERE column_name = NULL 的返回包含空值的行。

11 设置传入存储过程参数为只读

READONLY

CREATE PROCEDURE a(   @a VARCHAR(10) READONLY)

12 设置默认参数

CREATE PROCEDURE a( @a VARCHAR(10)='')

13 加密

with encryption

进行加密，加密以后，别人看不到触发器或存储过程里面的t-sql语句内容

14 sys.all_parameters Transact-SQL

显示属于用户定义对象或系统对象的所有参数的并集。

扩展：

1 在sys.schemas中

schema_id ：架构的 ID。在该数据库中是唯一的。
name：名称

在sys.sysobjects中

xtype：对象类型 V = 视图　　X = 扩展存储过程

在sys.all_parameters中

name 参数名，object_id：唯一ID,user_type_id:类型ID,is_output：参数为输出值

在sys.types中

user_type_id:类型 ID。系统数据类型: user_type_id = system_type_id;.name：类型名称,string之类；is_table_type：是否为表格类型

name 参数名

3 N

object_id(N'表名')中N' 代表 Unicode类型.可以支持不同语种的对象名

4 判断某值存在

IF  EXISTS ( )
BEGIN
END

5 在OBJECTPROPERTY 中

id
是表示当前数据库中对象 ID 的表达式。 id 的数据类型为 int，并假定为当前数据库上下文中的架构范围内的对象。

property
一个表达式，提供 id 指定的对象的返回信息。

IsProcedure 过程

6 删除存储过程

drop procedure

7 去除字符串右边多余空格

RTRIM()函数,用于去除字符串右边多余的空格

8 查找字符串在目标字符串位置

通过CHARINDEX如果能够找到对应的字符串，则返回该字符串位置，否则返回0。

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

起始位置从1开始

9 截取一个栏位资料中的其中一部分。

substring('abdcsef',1,3)

起始位置从1开始

10 sql的left()函数表示的是从字符表达式最左边一个字符开始返回指定数目的字符

11 转化字符串为int: CONVERT(INT,str)

12 SET NOCOUNT
使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。

13 取日期

select day(createtime),month(createtime),year(createtime) from product --取时间字段的天值,月值,年值

14 转化数据类型

CAST (expression AS data_type)

expression：任何有效的SQServer表达式。AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

15 between

在sqlerver 等价于 a>=min and a<=max

16 自定义类型table必须要有对应字段的table传入

比如SQL CREATE TYPE A AS TABLE(code NVARCHAR(255))

C#: DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("code", Type.GetType("System.String")));

17 更改数据库的兼容级别

ALTER DATABASE CM2017 SET COMPATIBILITY_LEVEL = 100

18 ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。

19 row_number() OVER (PARTITION BY COL1 ORDER BY COL2)

表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

20 SELECT id ,COUNT(1) count FROM table group by id

= SELECT id ,COUNT(id) count FROM table group by id

partition by关键字是分析性函数的一部分，它和聚合函数（如group by）不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录，

partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。

partition by 与group by不同之处在于前者返回的是分组里的每一条数据，并且可以对分组数据进行排序操作。后者只能返回聚合之后的组的数据统计值的记录。

21 ROUND() – 四舍五入一个正数或者负数，结果为一定长度的值。长度为1，就11.10
　　 CEILING() - 返回最小的整数，使这个整数大于或等于指定数的数值运算。 11.05 变为12
　　 FLOOR() - 返回最大整数，使这个整数小于或等于指定数的数值运算。 11.05 变为11

22 DATENAME(YEAR,GETDATE()) 获取日期年份

DATENAME(MONTH,GETDATE()) 获取月份

动态SQL执行

普通SQL语句：(1)exec('select * from Student')

(2)exec sp_executesql N'select * from Student'--此处一定要加上N，否则会报错

2.带参数的SQL语句　(1)declare @sql nvarchar(1000) ,@userId varchar(100)
set @userId='0001' set @sql=N'select * from Student where UserID=@userId'
exec sp_executesql @sql,N'@userId varchar(100)'

(3)带输出参数的SQL语句 declare @sql nvarchar(1000)

set @sql=N'select @userName=UserName from Student where UserId=1'

exec sp_executesql N'@userName varchar(100) output',@userName output ; select @userName

索引

索引提升查询速度

索引分类

唯一索引（UNIQUE）：索引值唯一的（创建了唯一约束，系自动创建唯一索引）

主键索引：主键列，自动创建主键索引。

聚集索引（CLUSTERED）：存储记录物理上连续，表的物理存储顺序。

非聚集索引（NONCLUSTERED）：存储记录物理上不连续，逻辑上连续。

UNIQUE：唯一索引。 clustered：聚集索引。nonclustered：非聚集索引。

创建索引：CREATE CLUSTERED INDEX Typeid ON #dtype(typeid)

查询

设计同一部门的员工的时候编码可以设计为前几位相同，比如有两行数据前几位相同

00003
0000300001

使用like 加join联查出前几位相同编号员工

SELECT a.typeId FROM dbo.Department a JOIN Department b ON a.typeId LIKE b.typeId+'%'

使用分页：

1 ROW_NUMBER() OVER()方式

select * from ( select *, ROW_NUMBER() OVER(Order by ArtistId ) AS RowId from ArtistModels ) as b where RowId between 10 and 20

2 offset fetch next方式（SQL2012以上的版本才支持：推荐使用）

select * from ArtistModels order by ArtistId offset 4 rows fetch next 5 rows only

3 --top not in方式（适应于数据库2012以下的版本）

select top 3 * from ArtistModels where ArtistId not in (select top 15 ArtistId from ArtistModels)

个人使用过ROW_NUMBER加Top方式：

确定开始页码的下标以及分页大小

select top 50 * from (select row_number()over(order by id)rownumber,* from pagetest)a where rownumber>9900

使用场景

1 创建自定义字段类型使用方法：

如果不存在某自定义类型，就创建某自定义类型

/*
	描述：编码临时表
	
	由于编码过多时,使用f_split函数创建表很慢,
	所以由程序后台直接生成此临时表传入存储过程使用
*/
IF NOT EXISTS (SELECT TOP 1 1 FROM sys.types st JOIN sys.schemas ss 
ON st.schema_id = ss.schema_id WHERE st.name = N'mst' AND ss.name = N'dbo')
BEGIN
	CREATE TYPE TbType_mst_Code AS TABLE
	(
		code NVARCHAR(255)
    )
END

2 判断是否存储过程，存在则删除

IF EXISTS ( SELECT  TOP 1 1
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[msp_Total]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
    DROP PROCEDURE [dbo].msp_InspecYearTotal;

3 用分隔符分割字符串的一部分

ALTER FUNCTION [dbo].[f_split]
(
	@SourceSql varchar(MAX),
	@StrSeprate varchar(10)
)
returns @temp TABLE(a varchar(MAX))
AS 
BEGIN
    DECLARE @i int
    SET @SourceSql=RTRIM(LTRIM(@SourceSql))
    SET @i=charindex(@StrSeprate,@SourceSql)
    WHILE @i>=1
	    BEGIN
	        INSERT @temp VALUES(LEFT(@SourceSql,@i-1))
	        SET @SourceSql=SUBSTRING(@SourceSql,@i+1,len(@SourceSql)-@i)
	        SET @i=charindex(@StrSeprate,@SourceSql)
	    END
    IF @SourceSql<>'' 
       INSERT @temp VALUES(@SourceSql)
    RETURN 
END

4 对ROW_NUMBER() OVER( PARTITION BY name ORDER BY name ASC )的使用

统计出不同ID下不同name的个数（name可能重复）

常规写法，两次分组，第一次分组，对id,name进行去重，第二次对不同ID分组，每一组统计出不同name的个数。

SELECT TS.id ,COUNT(name) count  FROM (
SELECT   id,name FROM     table a  
WHERE    GROUP BY id,name
)TS  GROUP BY id,name

第一次用PARTITION by 对name进行分组再排序，第二次只取排序等于1的，就去重了，再分组统计所有不同name个数（错误）

错误原因：只对name分组了，要同时对name,id一起分组，才能找到不同id下不同name个数

SELECT  TS.id ,COUNT(id) count  FROM (
SELECT   ROW_NUMBER() OVER( PARTITION BY name ORDER BY name ASC )rn,a.id 
FROM    table a    
)TS WHERE TS.rn = 1 GROUP BY TS.id

PARTITION by 只适应于一次分组，统计数据，比如统计不同客户，订单个数，同一个订单订单号会重复，

如果一个订单只针对一个客户。这样写就不会错。针对多个客户就会错。

SELECT  customerid ,COUNT(orderid) qty FROM (
SELECT   ROW_NUMBER() OVER( PARTITION BY a.orderid ORDER BY orderid ASC )rn,a.customerid AS customerid FROM     table
)TS WHERE TS.rn = 1 GROUP BY customerid

如果既要统计账户余额，又要统计订单个数。可以用union all, 最后 group by sum

SELECT customerid,SUM(balance) balance,SUM(qty) qty FROM ( SELECT  customerid ,balance,0 FROM customer
UNION ALL SELECT  customerid ,0,COUNT(orderid) qty FROM (
SELECT   ROW_NUMBER() OVER( PARTITION BY a.orderid ORDER BY orderid ASC )rn,a.customerid AS customerid FROM     table
)TS WHERE TS.rn = 1 GROUP BY customerid) GROUP BY customerid

4 动态分页，利用sp_executesql，CEILING，数据库row_numer从1开始排序

		  SET @sqlsearch = '
				SELECT  @rowcount = COUNT(1)
				FROM    ('+@sql+') g'
		  EXEC sp_executesql @sqlsearch,
					N'@rowcount INT OUT',@rowcount OUTPUT; 		  
		  IF @rowcount = 0 
		  RETURN
	      SELECT @pageCount = CEILING(1.0 * @rowcount / @pageSize)
		  SELECT @StartRecord = (@PageIndex-1) * @PageSize + 1 ,@EndRecord =  @PageSize * @PageIndex   
		  SET @sql=' select * from ( 
             Select *, ROW_NUMBER() OVER(Order by BtypeId ) AS RowId 
             from ('+@sql+') t5
                 ) as t6 WHERE RowId between '''+@StartRecord+''' and '''+@EndRecord+''''

错误记录：

1 EXEC sp_executesql @sqlsearch @sqlsearch的类型为VARCHAR(8000)错误，修改为NVARCHAR(MAX)

2 EXEC(‘’+@a); @a类型为int错误，转化为字符串，修改为 EXEC(''+CAST (@a AS VARCHAR(100)));

5 DATENAME的应用：查询当前日期当年数据并按照月份分组

select * FROM    table 
WHERE   DATENAME(YEAR, AddDate) = DATENAME(YEAR, @year)
        AND BillDate <= @year and a.Audited=1
GROUP BY DATENAME(MONTH, AddDate)
ORDER BY DATENAME(MONTH, AddDate);

6 通过存储过程名字查询所有参数

通过sys.objects的name查出存储过程，
通过all_parameters和object_id联查，查询出参数名
通过sys.types和user_type_id联查，查询类型

SELECT  param.name AS ParamName ,usrt.name AS [DataType] 
        FROM    sys.objects AS sp
                LEFT OUTER JOIN sys.all_parameters AS param ON param.object_id = sp.object_id
                LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id              
        WHERE   sp.type = 'P'
             --  AND sp.name = 'CheckExpression'

参考：

https://docs.microsoft.com

系统表：https://www.cnblogs.com/atree/p/SQL-Server-sysobjects.html

OBJECT_ID： https://www.cnblogs.com/AngelLee2009/p/3342730.html

partition by 开窗语句总结：https://www.lanhusoft.com/Article/170.html

分页：https://www.cnblogs.com/fengxiaojiu/p/7994124.html

动态SQL执行：https://www.cnblogs.com/railgunman/p/5770456.html

小小菜鸟cs

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL 数据库知识总结

sqlserver基础1 创建别名和自定义类型create typeAS (attribute_name data_type[, ... ] )2 系统类型表每个系统类型和用户定义类型都在表中对应一行。sys.types3 数据库架构表sys.schemas (Transact-SQL)每个数据库架构都对应一行。架构是形成单个命名空间的数据库实体的...
复制链接

扫一扫