SqlServer基础学习笔记

1.基本概念
数据：描述事物的符号记录称为数据，是数据库中存储的基本对象，包括数字、图像、声音等。
数据库：按照数据结构来组织、储存和管理数据的仓库。它是计算机科学的重要分支，是信息技术的核心和基础。
关系模式：指对关系的描述，格式为“关系名(属性1…属性N)”，在数据库中称为表结构。
数据库管理系统（DBMS）：是对数据库系统中数据进行管理的软件。
2.基本操作
新建数据库：
打开SQL Server Management Studio（SSMS）链接数据库服务器。
右键点击“数据库”，然后选择“新建数据库”。
在弹出的窗口中，输入数据库名称后点击确定。
新建表：
展开刚建好的数据库，右键点击“表”，然后依次点击“新建”->“表”。
填写表的信息，包括列名（即表头）和数据类型。
如果某列信息不能为空，可以取消勾选“允许NULL值”。
填写完成后保存，并填写表名称。
3. SQL基本语法
建表格：使用CREATE TABLE语句。例如，CREATE TABLE table_name (column1 DATATYPE [NOT NULL], column2 DATATYPE, ...)。其中，DATATYPE是数据的类型，NOT NULL表示该列不允许为空。
更改表格：使用ALTER TABLE语句。例如，ALTER TABLE table_name ADD COLUMN column_name DATATYPE，用于向表中添加新列。
建立索引：使用CREATE INDEX语句。例如，CREATE INDEX index_name ON table_name (column_name)，用于提高查询速度。
3.常用语法
查看锁表：SELECT request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName FROM sys.dm_tran_locks WHERE resource_type='OBJECT'
不锁表查询：使用WITH (NOLOCK)提示，例如SELECT * FROM TBW_PTProductactivity WITH (NOLOCK)
单独解锁：使用exec ('kill spid')语句，其中spid是会话ID。
查看存储过程、触发器、视图的内容：使用exec sp_helptext '对象名'语句。
查看相关依赖关系：使用exec sp_depends '对象名'语句。
查看sysobjects表中的内容：使用SELECT * FROM sysobjects WHERE xtype='类型'语句，其中类型可以是'TR'（触发器）等。
4.数据类型
SQL Server支持多种数据类型，用于存储不同类型的数据。常见的数据类型包括：
整数类型：如TINYINT, SMALLINT, INT, BIGINT。
浮点数和实数类型：如FLOAT, REAL, DECIMAL, NUMERIC。
字符类型：如CHAR(n), VARCHAR(n), TEXT（注意：在SQL Server 2016及更高版本中，建议使用NVARCHAR(n)和NCHAR(n)来处理Unicode数据）。
日期和时间类型：如DATE, TIME, DATETIME, DATETIME2, SMALLDATETIME, TIMESTAMP（注意：TIMESTAMP在SQL Server中实际上是一个二进制数字，通常用于版本控制）。
二进制类型：如BINARY(n), VARBINARY(n), IMAGE（同样，IMAGE数据类型在SQL Server 2005及更高版本中已被VARBINARY(MAX)替代）。
其他数据类型：如BIT（用于存储0或1），UNIQUEIDENTIFIER（用于存储GUID），XML等。
5.SQL查询
SELECT：用于从数据库表中检索数据。
FROM：指定要从中选择数据的表。
WHERE：用于过滤结果集中的行。
ORDER BY：用于对结果集中的行进行排序。
GROUP BY：用于将结果集中的行组合成摘要行。
HAVING：用于过滤分组后的结果。
JOIN：用于根据两个或多个表之间的相关列之间的关系，从这些表中查询数据。常见的JOIN类型有INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN等。
6.聚合函数
COUNT()：计算行数。
SUM()：计算某列的总和。
AVG()：计算某列的平均值。
MAX() 和 MIN()：返回某列的最大值和最小值。
SQL Server函数
SQL Server提供了大量的内置函数，用于处理字符串、日期、数学运算等。例如：
字符串函数：如LEN(), UPPER(), LOWER(), REPLACE(), SUBSTRING()等。
日期函数：如GETDATE(), DATEADD(), DATEDIFF(), CONVERT(date, ...)等。
数学函数：如ROUND(), CEILING(), FLOOR(), ABS()等。
7.索引
聚集索引：表中各行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引。
非聚集索引：非聚集索引与数据分开存储，包含指向表中数据的指针。一个表可以有多个非聚集索引。
8.视图
视图是一个虚拟表，其内容由查询定义。通过视图，可以呈现基表中的数据，也可以只显示你所关心的某几列数据。
视图可以简化复杂的SQL操作，同时隐藏数据的复杂性。

例子：假设我们有一个名为Employees的表，它包含员工的各种信息，但我们只想向某些用户展示员工的姓名和部门信息。这时，我们可以创建一个视图来实现这一目的。

CREATE VIEW EmployeeView AS  
SELECT EmployeeName, Department  
FROM Employees;
9.存储过程
存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字和参数（如果有）来执行它。
存储过程可以简化应用开发人员的很多工作，减少数据在应用服务器和数据库服务器之间的传输。

例子：假设我们想要从Employees表中检索所有员工的姓名和薪水，并且我们想要将这个功能封装在一个存储过程中，以便多次使用。

CREATE PROCEDURE GetEmployeeDetails AS BEGIN SELECT EmployeeName, Salary FROM Employees; END;

调用存储过程：EXEC GetEmployeeDetails;
10.触发器
触发器是一种特殊的存储过程，它不由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行INSERT、UPDATE或DELETE操作时就会激活它并执行。
触发器可以强化约束，来维护数据的完整性和一致性，可以跟踪对数据库的操作等。

例子（BEFORE INSERT触发器）：假设我们有一个名为Orders的表，每当插入新的订单时，我们都想要记录订单插入的时间。为了实现这一功能，我们可以使用BEFORE INSERT触发器。

但请注意，SQL Server没有直接的BEFORE INSERT触发器。但我们可以使用AFTER INSERT触发器来实现类似的功能。不过，为了演示，假设的BEFORE INSERT的伪代码示例，并给出一个AFTER INSERT的实际示例。

伪代码（BEFORE INSERT）：

-- 注意：SQL Server没有BEFORE INSERT触发器，但以下是一个概念示例 CREATE TRIGGER trgBeforeInsertOrder ON Orders BEFORE INSERT AS BEGIN -- 这里我们可以设置订单的插入时间，但SQL Server不支持在BEFORE INSERT中直接设置 -- 因此，这只是一个概念示例 SET NEW.InsertTime = GETDATE(); END;

-- 注意：SQL Server没有BEFORE INSERT触发器，但以下是一个概念示例  
CREATE TRIGGER trgBeforeInsertOrder  
ON Orders  
BEFORE INSERT  
AS  
BEGIN  
    -- 这里我们可以设置订单的插入时间，但SQL Server不支持在BEFORE INSERT中直接设置  
    -- 因此，这只是一个概念示例  
    SET NEW.InsertTime = GETDATE();  
END;

实际示例（AFTER INSERT）：在插入新订单后，我们更新另一个表OrderHistory来记录这一事件。

CREATE TRIGGER trgAfterInsertOrder ON Orders AFTER INSERT AS BEGIN -- 假设Orders表有一个名为OrderID的列作为主键 -- OrderHistory表有一个名为OrderID和InsertTime的列 INSERT INTO OrderHistory (OrderID, InsertTime) SELECT i.OrderID, GETDATE() FROM inserted i; -- inserted是一个特殊的表，用于引用刚插入到Orders表中的行 END;

CREATE TRIGGER trgAfterInsertOrder  
ON Orders  
AFTER INSERT  
AS  
BEGIN  
    -- 假设Orders表有一个名为OrderID的列作为主键  
    -- OrderHistory表有一个名为OrderID和InsertTime的列  
    INSERT INTO OrderHistory (OrderID, InsertTime)  
    SELECT i.OrderID, GETDATE()  
    FROM inserted i; -- inserted是一个特殊的表，用于引用刚插入到Orders表中的行  
END;

当向Orders表中插入新的订单时，trgAfterInsertOrder触发器会自动执行，并在OrderHistory表中插入相应的记录。