标签：常用 NAME -- SqlSever 视图查询 column SQL SELECT

sql视图
查询重复数据
锁表
- 查询锁表
循环
- 使用while循环插入
CTE
递归查询
条件查询
窗口函数
常用函数
查询表的字段
- 更多系统视图和函数
- 检查数据库中是否存在某个表

sql视图

SQL视图（View）是一种虚拟的表，它只是一个 SELECT 语句的命名结果集，与物理表不同，它没有存储在数据库中的数据。视图可以简化复杂的查询，隐藏表的结构，同时还可以限制用户对表数据的访问权限。

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

view_name 是要创建的视图的名称
table_name 是要查询的表的名称
column1, column2, ... 是要包含在视图中的列的名称
condition 是可选的查询条件。

使用视图时，只需要像访问表一样使用视图名称，就可以获取视图中的数据 SELECT * FROM view_name;

删除视图 DROP VIEW view_name;

查询重复数据

select * from (
    select row_number() over(partition by code order by code) as ono, * 
    from table1
) a 
where a.ono=1 --加条件去重

锁表

锁表的原理是数据库使用独占锁机制，当执行insert，update，delete的语句时，对表进行锁住，直到发生commite 或者 回滚 或者退出数据库用户。
insert时全表锁，因为要生成主键字段、索引等等，update是行级锁。

delete操作删除指定一条记录，SQLServer就会对该条记录进行了行锁定，这时如果再查询该条记录，就会造成死锁。

查询锁表

select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'

循环

DECLARE @counter INT
SET @counter = 0
WHILE @counter < 10
BEGIN
    SET @counter = @counter + 1
    PRINT 'Counter Value: ' + CAST(@counter AS VARCHAR(10))
END

使用while循环插入

DECLARE @index INT
DECLARE @arraySize INT
DECLARE @array TABLE (id INT IDENTITY(1,1), value INT)

--在数组表中插入数据
INSERT INTO @array (value) 
VALUES (1), (2), (3), (4), (5)

SET @index = 1
SET @arraySize = (SELECT COUNT(*) FROM @array)

WHILE @index <= @arraySize
BEGIN 
    --获取当前索引对应的数组元素
    DECLARE @currentValue INT
    SET @currentValue = (SELECT value FROM @array WHERE id = @index)
    --执行对数组元素的操作
    PRINT @currentValue
    SET @index = @index+1
END

CTE

CTE通用表达式：在 SQL 查询中使用的临时结果集的定义方式。CTE 可以被视为一个临时的命名查询，它可以在后续的查询中被引用和使用。

WITH CTE_Name (column1, column2, ...) AS (
    -- CTE 查询定义
    SELECT ...
    FROM ...
    WHERE ...
)
-- 在此处使用 CTE
SELECT ...
FROM CTE_Name
JOIN ...
WHERE ...

CTE vs 视图

CTE（Common Table Expression）和视图（View）是在 SQL 中用于组织和重用查询逻辑的两种不同的机制。它们有以下几个区别：

定义和使用方式：CTE 使用 WITH 关键字在查询中定义，可以在查询中立即使用。视图是一个已经定义好的查询，可以在查询中引用和使用。

作用范围：CTE 的作用范围仅限于定义它的查询，它在查询执行完后就消失了。视图是一个持久化的对象，可以在多个查询中引用和使用。

存储方式：CTE 不存储任何数据，它只是一个虚拟表，根据查询定义动态生成结果。视图是一个存储在数据库中的查询结果集，可以在需要时直接引用。

可写性：CTE 可以是可写的，可以在 CTE 中进行插入、更新和删除操作。视图在默认情况下是只读的，不能直接对视图进行写操作，但可以通过对视图基础表的操作来间接修改视图数据。

性能和优化：CTE 在查询执行期间是一个临时的结果集，可以在查询计划中进行优化。视图是一个存储的查询结果，数据库可以对视图进行优化并缓存结果。

总的来说，CTE 主要用于在查询中组织和重用临时结果集，它是一个临时的、动态生成的虚拟表。视图则是一个持久化的、存储在数据库中的查询结果集，可以在多个查询中引用和使用。

递归查询

用于处理具有层级结构的数据。允许在查询中使用自引用的表，以便在一个查询中递归地遍历和处理层级数据。

在 CTE 中，使用递归部分和非递归部分来定义递归查询。递归部分定义了递归查询的初始条件和递归步骤，而非递归部分定义了递归查询的终止条件。以下是一个使用递归查询的示例，假设有一个名为 Employees 的表，其中包含员工的 ID、姓名和经理的 ID：

WITH RecursiveCTE (EmployeeID, EmployeeName, ManagerID, Level) AS (
    -- 非递归部分：初始条件
    SELECT EmployeeID, EmployeeName, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- 递归部分：递归步骤
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, rc.Level + 1
    FROM Employees e
    INNER JOIN RecursiveCTE rc ON e.ManagerID = rc.EmployeeID
)
SELECT EmployeeID, EmployeeName, ManagerID, Level
FROM RecursiveCTE

条件查询

用case定义连接条件

SELECT column1, column2
FROM table1
LEFT JOIN table2 ON table1.id = table2.id
                AND CASE
                    WHEN table1.column = 'value1' THEN table2.column = 'condition1'
                    WHEN table1.column = 'value2' THEN table2.column = 'condition2'
                    ELSE table2.column = 'default_condition'
                    END

用IF EXISTS条件执行不同的查询

IF EXISTS(SELECT * FROM 表名 WHERE 条件)
    SELECT * FROM 表名 WHERE 条件
ELSE
    SELECT * FROM 表名 WHERE 其他条件

窗口函数

用于对查询结果集中的行进行分组排序、计算聚合值和分配行号的特殊函数。窗口函数可以在 SELECT 语句中使用，并通过 OVER 子句指定窗口的范围和排序方式。

ROW_NUMBER()：为每一行分配一个唯一的序号。

SELECT ROW_NUMBER() OVER (ORDER BY column) AS row_num FROM table;
RANK()：为每一行分配一个排名，相同的值会得到相同的排名，但是会有间隔。

SELECT RANK() OVER (ORDER BY column) AS rank FROM table;
DENSE_RANK()：为每一行分配一个密集的排名，相同的值会得到相同的排名，没有间隔。

SELECT DENSE_RANK() OVER (ORDER BY column) AS dense_rank FROM table;
NTILE()：将结果集划分为指定数量的桶，并将每一行分配到对应的桶中。

SELECT NTILE(4) OVER (ORDER BY column) AS bucket FROM table;
LAG()：返回指定列在当前行之前的指定偏移量处的值。

SELECT LAG(column, offset) OVER (ORDER BY column) AS lag_value FROM table;
LEAD()：返回指定列在当前行之后的指定偏移量处的值。

SELECT LEAD(column, offset) OVER (ORDER BY column) AS lead_value FROM table;

这些窗口函数可以与 PARTITION BY 子句一起使用，以便在每个分组内进行排序和计算。
PARTITION BY子句用于对查询结果集进行分组，以便在每个分组内进行排序、计算聚合值或分配序号。

例1：计算每个地区的销售总额 SELECT region, SUM(sales) OVER (PARTITION BY region) AS total_sales FROM sales_data
例2：在每个分组内按照指定列的升序对结果集进行排序，并为每一行分配一个唯一的序号 SELECT ROW_NUMBER() OVER (PARTITION BY column ORDER BY column) AS row_num FROM table。

常用函数

聚合函数：
- SUM(column)：计算指定列的总和。
- AVG(column)：计算指定列的平均值。
- COUNT(column)：计算指定列的行数。
- MAX(column)：找出指定列的最大值。
- MIN(column)：找出指定列的最小值。
字符串函数：
- LEN(string)：返回指定字符串的长度。
- LEFT(string, length)：返回指定字符串的左边指定长度的部分。
- RIGHT(string, length)：返回指定字符串的右边指定长度的部分。
- SUBSTRING(string, start, length)：返回指定字符串的从指定位置开始指定长度的部分。
- CONCAT(string1, string2)：将两个字符串连接起来。
- UPPER(string)：将字符串转换为大写。
- LOWER(string)：将字符串转换为小写。
- REPLACE(string, old_value, new_value)：将字符串中的指定值替换为新值。
- CHARINDEX(substring, string)：用于查找一个字符串在另一个字符串中的位置，返回第一个字符串在第二个字符串中的起始位置。CHARINDEX(','+LTRIM(b.role_id)+',', ','+a.role_id+',')
- LTRIM(string)：去除字符串左边的空格。
- RTRIM(string)：去除字符串右边的空格。
日期和时间函数：
- GETDATE()：返回当前日期和时间。
- DATEPART(datepart, date)：返回日期或时间的指定部分（例如年份、月份、小时等）。
- DATEDIFF(datepart, startdate, enddate)：计算两个日期之间的差异，以指定的日期部分为单位。

转换函数：

CAST(expression AS data_type)：将一个数据类型转换为另一个数据类型。

-- 将字符串转换为整数
SELECT CAST('123' AS INT) AS converted_value;

-- 将小数转换为整数
SELECT CAST(3.14 AS INT) AS converted_value;

-- 将整数转换为字符串
SELECT CAST(456 AS VARCHAR(10)) AS converted_value;

-- 将日期转换为字符串
SELECT CAST(GETDATE() AS VARCHAR(20)) AS converted_value;

CONVERT(data_type, expression, style)：将一个数据类型转换为另一个数据类型，可以指定样式。

条件函数：
- CASE WHEN condition THEN expression ELSE expression END：根据条件执行不同的表达式。
NULL 相关函数：
- ISNULL(expression, replacement)：如果表达式为空，则返回替代值。
- NULLIF(expression1, expression2)：如果两个表达式相等，则返回空值。
数学函数：
- ABS(number)：返回一个数的绝对值。
- ROUND(number, decimal_places)：将一个数四舍五入到指定的小数位数。
- CEILING(number)：返回不小于指定数的最小整数。
- FLOOR(number)：返回不大于指定数的最大整数。
- POWER(number, exponent)：返回一个数的指定次幂。
联接函数：
- INNER JOIN：返回两个表中匹配的行。
- LEFT JOIN：返回左表中的所有行，以及右表中匹配的行。
- RIGHT JOIN：返回右表中的所有行，以及左表中匹配的行。
- FULL JOIN：返回左表和右表中的所有行。
聚合函数：
- GROUP BY：按指定的列对结果进行分组。
- HAVING：筛选分组后的结果。
窗口函数：
- ROW_NUMBER()：为每一行分配一个唯一的数字。
- RANK()：为每一行分配一个排名。
- LEAD()：获取当前行后的指定行数的值。
- LAG()：获取当前行前的指定行数的值。

查询表的字段

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME ='表名';

其中，TABLE_NAME是要查询的表的名称，该语句会返回该表的所有字段名称、字段类型和字段大小。

COLUMN_NAME: 表示字段名称；
DATA_TYPE: 表示字段类型；
CHARACTER_MAXIMUM_LENGTH: 表示字段大小，但只适用于字符类型的字段，如果是数字类型则返回 NULL。
TABLE_CATALOG：表所属的数据库名称。
TABLE_SCHEMA：表所属的模式名称。
TABLE_NAME：表的名称。
ORDINAL_POSITION：列在表中的位置。
NUMERIC_PRECISION：数值类型列的精度。
NUMERIC_SCALE：数值类型列的小数位数。
IS_NULLABLE：列是否允许为空。
COLUMN_DEFAULT：列的默认值。

检查数据库中是否存在某个表

使用 INFORMATION_SCHEMA.TABLES 视图：

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'YourTableName'

如果查询结果返回行，则表示数据库中已存在该表。

使用 sys.objects 视图：

SELECT *
FROM sys.objects
WHERE name = 'YourTableName' AND type = 'U'

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('MPS_T_D_CompBasicInfo') AND type = 'U') DROP TABLE MPS_T_D_CompBasicInfo

如果查询结果返回行，则表示数据库中已存在该表。

使用 OBJECT_ID 函数：

IF OBJECT_ID('YourTableName', 'U') IS NOT NULL
    PRINT 'Table exists'
ELSE
    PRINT 'Table does not exist'

如果返回结果不为 NULL，则表示数据库中已存在该表。

标签：常用,NAME,--,SqlSever,视图,查询,column,SQL,SELECT
From： https://www.cnblogs.com/Jusaka-12/p/17610830.html

SQL常用(SqlSever)