SQL Server

SQL Server

INSERT INTO

INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');

INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');

JOIN

A inner join B 取交集。
A left join B 取 A 全部，B 没有对应的值为 null。
A right join B 取 B 全部 A 没有对应的值为 null。
A full outer join B 取并集，彼此没有对应的值为 null。
对应条件在 on 后面填写，eg：
select table1.a, table2.b from **table1 inner join table2 on** table1.a = table2.a

select p.FirstName,p.LastName,a.City,a.State
from
person p left join address a
on
p.personid=a.personid
-- 两个 join（牛客：SQL100 确定最佳顾客的另一种方式（二））
select distinct
    Customers.cust_name,
    sum(OrderItems.item_price * OrderItems.quantity) as total_price
from
    Orders
    inner join OrderItems on Orders.order_num = OrderItems.order_num
    inner join Customers on Orders.cust_id = Customers.cust_id
group by
    Customers.cust_name
having
    sum(OrderItems.item_price * OrderItems.quantity) >= 1000
order by
    total_price

-- union left & right == full outer join 
select
    v.vend_id,
    count(p.prod_id) as prod_id
from
    Vendors v
    full outer join  Products p on p.vend_id = v.vend_id
where v.vend_id is not null
group by
    v.vend_id
order by vend_id
-- == 等价于
select
    v.vend_id,
    count(p.prod_id) as prod_id
from
    Vendors v
    left join Products p on p.vend_id = v.vend_id
group by
    v.vend_id
union
select
    v.vend_id,
    count(p.prod_id) as prod_id
from
    Products p
    right join Vendors v on p.vend_id = v.vend_id
group by
    v.vend_id

order by vend_id

UNION

1，SQL UNION 操作符合并两个或多个 SELECT 语句的结果
2，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
3，UNION 操作符去重，如果允许重复的值，用 UNION ALL

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

SELECT * INTO

从一个表复制数据，然后把数据插入到另一个新表中

SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;

SELECT *
INTO newtable
FROM table1
WHERE 1=0;

UPDATE

update examination_info set tag= 'Python'
where tag = 'PYTHON'

INSERT INTO SELECT

1，从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响
2，select into from 和 insert into select 都是用来复制表
两者的主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from 要求目标表存在。

INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps
WHERE id=1;
-- year() ：有自增ID所以要null 或者 select不要查询自增ID
INSERT INTO
    exam_record_before_2021
SELECT
    null,
    uid, 
    exam_id,
    start_time,
    submit_time,
    score
FROM
    exam_record
WHERE
    year (submit_time) < 2021

CREATE DATABASE

CREATE DATABASE max_1;

CREATE TABLE

CREATE TABLE Persons
(
  PersonID int,
  LastName varchar(255),
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255)
);

insert into Persons values (0001, 'LILY', 'JACK','海淀', 'BEIJING')
insert into Persons(PersonID, LastName, FirstName) values (0001, 'MACK', 'YOYO')

表的创建、修改与删除：

• 1.1 直接创建表：

CREATE TABLE
[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过
(column_name1 data_type1 -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键
   | FOREIGN KEY -- 外键，引用其他表的键值
   | AUTO_INCREMENT -- 自增ID
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

• 1.2 从另一张表复制表结构创建表： CREATE TABLE tb_name LIKE tb_name_old
• 1.3 从另一张表的查询结果创建表： CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options
• 2.1 修改表：ALTER TABLE 表名 修改选项 。选项集合：

    { ADD COLUMN <列名> <类型>  -- 增加列
     | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
     | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
     | MODIFY COLUMN <列名> <类型> -- 修改列类型
     | DROP COLUMN <列名> -- 删除列
     | RENAME TO <新表名> -- 修改表名  EXEC sp_rename 'exam_record', 'exam_records'
     | CHARACTER SET <字符集名> -- 修改字符集
     | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

• 3.1 删除表：DROP TABLE [IF EXISTS] 表名1 [ ,表名2]。

SQL 约束（Constraints）

NOT NUL

指示某列不能存储 NULL 值
ALTER TABLE Persons MODIFY Age int NOTNULL; MYSQL可以用modify，sql server不能这样改

ALTER TABLE Persons1 ALTER column Age int NOT NULL;

UNIQUE （唯一值）

保证某列的每行必须有唯一的值

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL
)

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

ALTER TABLE Persons
ADD UNIQUE (P_Id)

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

-- alter table 表名 add constraint 约束名称 约束类型（列名）
--【约束名称是自己命名的，可以设置为列名+属性，不可重复】

PRIMARY KEY （主键）

NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
--1，创建表时只设置一个主键
--创建时在 "P_Id" 列上创建 PRIMARY KEY 约束
LastName varchar(255) NOT NULL,
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
--2，创建表时一个主键但是由两列组成
--只有一个主键 PRIMARY KEY（pk_PersonID）。
--pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的
)

s_id int identity(1001,1) primary key not null,

-- 定义单列的 PRIMARY KEY 约束
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
-- 定义多个列的 PRIMARY KEY 约束
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
-- 撤销 PRIMARY KEY 约束
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

FOREIGN KEY （外键）

主表要先设置主键，子表才能建立外键
保证一个表中的数据匹配另一个表中的值的参照完整性
预防破坏表之间连接的行为，防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
-- 在 "Orders" 表创建时在 "P_Id" 列上创建 FOREIGN KEY 约束
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
-- 定义多个列的 FOREIGN KEY 约束
)

-- 定义多个列的 FOREIGN KEY 约束
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
-- 定义多个列的 FOREIGN KEY 约束：constraint 约束名称 约束类型（列名）
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
-- 撤销 FOREIGN KEY 约束
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

CHECK （约束）

1. 保证列中的值符合指定的条件
2. CHECK 约束用于限制列中的值的范围
3. 如果对单个列定义 CHECK 约束，那么该列只允许特定的值
4. 如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制
5. 当数据表中有数据时，增加约束会出现报错“ALTER TABLE 语句与 CHECK 约束冲突”

-- 一个CHECK
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
)
-- 多个CHECK
CREATE TABLE Persons
(
P_Id int NOT NULL,
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

-- 修改一个
ALTER TABLE Persons
ADD CHECK (P_Id>0)
-- 修改多个
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
-- 撤销
ALTER TABLE Persons
DROP CONSTRAINT chk_Person

DEFAULT （默认值）

如果没有规定其他的值，那么会将默认值添加到所有的新记录

-- 建立默认值约束
CREATE TABLE Persons
(
    P_Id int NOT NULL,
    City varchar(255) DEFAULT 'Sandnes'
)
-- 建立函数取值约束
CREATE TABLE Orders
(
    O_Id int NOT NULL,
    OrderDate date DEFAULT GETDATE()  -- GETDATE()函数取当前日期
)

-- 增加约束
ALTER TABLE Persons
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City
-- 撤销约束
-- ALTER TABLE Persons
-- ALTER COLUMN City DROP DEFAULT  这个语句有问题
ALTER TABLE Persons_DEFAULT 

DROP constraint  DFE_ID

INDEX（索引）

在表中创建索引，在不读取整个表的情况下，可以更快地查找数据。

-- 允许有重复值
CREATE INDEX index_name
ON table_name (column_name)
-- 不允许有重复值【UNIQUE 唯一值】
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
-- 创建多个列的索引
CREATE INDEX index_name
ON table_name (column_name0, column_name1, column_name2,)

-- 在duration列创建普通索引idx_duration
-- 在exam_id列创建唯一性索引uniq_idx_exam_id
-- 在tag列创建全文索引full_idx_tag。
CREATE INDEX idx_duration
ON examination_info (duration);
CREATE UNIQUE INDEX uniq_idx_exam_id
ON examination_info (exam_id);
CREATE FULLTEXT INDEX full_idx_tag
ON examination_info (tag);

判断索引是否存在
if exists(select * from sysindexes where name=‘clus1’)
drop index clus1 on test1 with (online=off);
go
–添加索引
create
–[unique] --指定聚集索引是否唯一
clustered --指定为聚集索引
index clus1 --索引名称
on test1 --索引添加在哪个表
(name asc,sex desc) --索引添加在哪个数据列
with(
–pad_index:指定索引填充
–pad_index=on:FILLFACTOR 指定的可用空间百分比应用于索引的中间级页。
–pad_index=off或未指定 fillfactor:考虑到中间级页上的键集，可以将中间级页几乎填满，但至少要为最大索引行留出足够空间。
pad_index=off,

–statistics_norecompute:指定是否重新计算统计信息。
–statistics_norecompute=on:过时的统计信息不会自动重新计算。
–statistics_norecompute=off:启用自动统计信息更新。
statistics_norecompute=off,

–sort_in_tempdb:指定是否将排序结果存储在 tempdb 中。
–sort_in_tempdb=on:在tempdb中存储用于生成索引的中间排序结果。如果tempdb与用户数据库不在同一组磁盘上，就可缩短创建索引所需的时间。但是，这会增加索引生成期间所使用的磁盘空间量。
–sort_in_tempdb=off:中间排序结果与索引存储在同一数据库中。
sort_in_tempdb=off,

–ignore_dup_key:指定在插入操作尝试向唯一索引插入重复键值时的响应类型。 IGNORE_DUP_KEY 选项仅适用于创建或重新生成索引后发生的插入操作。 当执行 CREATE INDEX、ALTER INDEX 或 UPDATE 时，该选项无效。 默认为 OFF。
–ignore_dup_key=on:打开，将重复键值插入唯一索引时会出现警告消息。只有违反唯一性的行为才会失败。
–ignore_dup_key=off:关闭,将重复键值插入唯一索引时会出现错误消息。回滚整个INSERT操作。对于对视图创建的索引、非唯一索引、XML 索引、空间索引以及筛选的索引，IGNORE_DUP_KEY 不能设置为 ON
ignore_dup_key=off,

–drop_existing：表示如果这个索引还在表上就 drop 掉然后在 create 一个新的。 默认为 OFF。
–drop_existing=on:指定要删除并重新生成现有索引，其必须具有相同名称作为参数 index_name。
–drop_existing=off:指定不删除和重新生成现有的索引。 如果指定的索引名称已经存在，SQL Server 将显示一个错误。
drop_existing=off,

–online:指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。 默认为 OFF。 REBUILD 可作为 ONLINE 操作执行。
–online=on:在索引操作期间不持有长期表锁。 在索引操作的主要阶段，源表上只使用意向共享 (IS) 锁。
–这使得能够继续对基础表和索引进行查询或更新。
–操作开始时，在很短的时间内对源对象持有共享 (S) 锁。
–操作结束时，如果创建非聚集索引，将在短期内获取对源的 S（共享）锁；
–当联机创建或删除聚集索引时，以及重新生成聚集或非聚集索引时，将在短期内获取 SCH-M（架构修改）锁。 但联机索引锁是短的元数据锁，特别是 Sch-M 锁必须等待此表上的所有阻塞事务完成。
–在等待期间，Sch-M 锁在访问同一表时阻止在此锁后等待的所有其他事务。 对本地临时表创建索引时，ONLINE 不能设置为 ON。
–online=off:在索引操作期间应用表锁。这样可以防止所有用户在操作期间访问基础表。
–创建、重新生成或删除聚集索引或者重新生成或删除非聚集索引的脱机索引操作将对表获取架构修改 (Sch-M) 锁。
–这样可以防止所有用户在操作期间访问基础表。 创建非聚集索引的脱机索引操作将对表获取共享 (S) 锁。 这样可以防止更新基础表，但允许读操作（如 SELECT 语句）。
online=off,

–aloow_row_locks:指定是否允许行锁。
–allow_row_locks=on:访问索引时允许行锁。数据库引擎确定何时使用行锁。
–allow_row_locks=off:不使用行锁。
allow_row_locks=on,

–allow_page_locks:指定是否允许使用页锁。
–allow_page_locks=on:访问索引时允许页锁。数据库引擎确定何时使用页锁。
– allow_page_locks=off:不使用页锁。
allow_page_locks=on,

–fillfactor=n：指定一个百分比，指示在数据库引擎创建或修改索引的过程中，应将每个索引页面的叶级填充到什么程度。 指定的值必须是 1 到 100 之间的整数。 默认值为 0。
fillfactor=1

–maxdop=max_degree_of_parallelism:在索引操作期间替代 max degree of parallelism 配置选项。 有关详细信息，请参阅 配置 max degree of parallelism 服务器配置选项。 使用 MAXDOP 可以限制在执行并行计划的过程中使用的处理器数量。 最大数量为 64 个处理器。
–max_degree_of_parallelism 可以是：
–1 - 取消生成并行计划。
–>1 - 将并行索引操作中使用的最大处理器数量限制为指定数量。
–0（默认值）- 根据当前系统工作负荷使用实际数量的处理器或更少数量的处理器。
–有关详细信息，请参阅 配置并行索引操作。
–maxdop=1,
–data_compression=row:为指定的表、分区号或分区范围指定数据压缩选项。 选项如下所示：
–none
–不压缩表或指定的分区。 仅适用于行存储表；不适用于列存储表。
–row
–使用行压缩来压缩表或指定的分区。 仅适用于行存储表；不适用于列存储表。
–page
–使用页压缩来压缩表或指定的分区。 仅适用于行存储表；不适用于列存储表。

–columnstore
–适用范围： SQL Server 2014 (12.x) 到 SQL Server 2017。
–仅适用于列存储表。 COLUMNSTORE 指定对使用 COLUMNSTORE_ARCHIVE 选项压缩的分区进行解压缩。 还原数据时，将继续通过用于所有列存储表的列存储压缩对 COLUMNSTORE 索引进行压缩。

–columnstore_archive
–适用范围： SQL Server 2014 (12.x) 到 SQL Server 2017。
–仅适用于列存储表，这是使用聚集列存储索引存储的表。 COLUMNSTORE_ARCHIVE 会进一步将指定分区压缩到更小。 这可用于存档，或者用于要求更少存储并且可以付出更多时间来进行存储和检索的其他情形
–data_compression=row
–on partitions ( { <partition_number_expression> | } [ ,…n ] ) 适用范围： SQL Server 2008 到 SQL Server 2017。
–指定对其应用 DATA_COMPRESSION 设置的分区。 如果表未分区，ON PARTITIONS 参数将生成错误。 如果不提供 ON PARTITIONS 子句，DATA_COMPRESSION 选项将应用于已分区表的所有分区。
–可以按以下方式指定 <partition_number_expression>：
–提供一个分区号，例如：ON PARTITIONS (2)。
–提供若干单独分区的分区号并用逗号将它们隔开，例如：ON PARTITIONS (1, 5)。
–同时提供范围和单个分区，例如：ON PARTITIONS (2, 4, 6 TO 8)。
– 可以指定为以单词 TO 隔开的分区号，例如：ON PARTITIONS (6 TO 8)。
–，请多次指定 DATA_COMPRESSION 选项
–on partitions(1-2)
)
on [primary];–数据空间规范
go
–添加注释
execute sp_addextendedproperty N’MS_Description’,N’第一个聚集索引’,N’schema’,N’dbo’,N’table’,N’test1’,N’index’,N’clus1’;
go

DROP

删除索引、表和数据库。

-- 删除数据库
DROP DATABASE database_name
-- 删除表
DROP TABLE table_name
-- 删除索引（不能同时删除多个）
DROP INDEX table_name.index_name
drop index uniq_idx_exam_id on examination_info; 

TRUNCATE

-- 删除表表中所有记录，并重置自增主键，并不删除表本身
TRUNCATE TABLE table_name

ALTER（修改列）

在已有的表中添加、删除或修改列

在表中添加列:
ALTER TABLE table_name
ADD column_name datatype
删除表中的列（某些数据库系统不允许这种在数据库表中删除列的方式）：
ALTER TABLE table_name
DROP COLUMN column_name
要改变表中列的数据类型：
ALTER TABLE table_name
ALTER COLUMN column_name datatype

AUTO INCREMENT（IDENTITY 自动赋值主键）

会在新记录插入表中时生成一个唯一的数字

-- 创建主键字段
CREATE TABLE Students
(
ID int IDENTITY(1,1) PRIMARY KEY, -- 把 "Students" 表中的 "ID" 列定义为 auto-increment 主键字段
--（MySQL 使用 AUTO_INCREMENT 关键字来执行，eg:ID int NOT NULL AUTO_INCREMENT）
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

-- 要在 "Students" 表中插入新记录，我们不必为 "ID" 列规定值（会自动添加一个唯一的值）：
INSERT INTO Students (FirstName,LastName)
VALUES ('Lars','Monsen')

-- 要让 AUTO_INCREMENT 序列以其他的值起始，使用下面的 SQL 语法：
ALTER TABLE Persons AUTO_INCREMENT=100

视图

可视化的表

-- 创建视图
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued= 'No'
-- 查询视图
SELECT * FROM [Current Product List]

使用SQL Server Management Studio创建视图

使用SQL语句创建视图

　　CREATE VIEW [ schema_name . ] view_name  [ (column [ ,...n ] ) ] 
　　 [ WITH <view_attribute> [ ,...n ] ]  
　　AS 
　　 select_statement  
　　[ WITH CHECK OPTION ]   
　　<view_attribute> ::=  
　　{ [ ENCRYPTION ]    [ SCHEMABINDING ]  [ VIEW_METADATA ]    }

WITH CHECK OPTION

CREATE VIEW V_PRICE_270
AS 
select PROD_ID, PROD_PRICE from  products
WHERE PROD_PRICE < 260 WITH CHECK OPTION
-- 更新插入
UPDATE V_PRICE_270 SET PROD_PRICE = PROD_PRICE + 50
INSERT INTO V_PRICE_270 VALUES(10025,300)

视图加密（ with encryption ）

create view stud_view 
with encryption -- 加密
as
select s_name
from student 
where age = 12

视图维护

查看属性

sp_help stud_view 或者 右键查看

查看视图的文本信息

sp_helptext [@objname=]'name'[,[@columnname=]computed_column_name]

参数说明：
（1）[@objname=]‘name’：表示要查看定义信息的对象名。
（2）[@columnname=]computed_column_name：表示计算列的列名。

查看视图与其他对象的依赖关系

sp_depends stud_view

修改和删除视图

-- 更新视图
ALTER VIEW [Current Product List] AS
SELECT ProductID,ProductName,vend_id
FROM Products
WHERE Discontinued='No'
-- 删除视图
DROP VIEW view_name

通过视图操纵表数据

使用insert插入数据

使用视图插入数据与在基表中插入数据一样，都可以通过INSERT语句来实现。插入数据的操作是针对视图中的列的插入操作，而不是针对基表中的所有的列的插入操作。由于进行插入操作的视图不同于基表，所以使用视图插入数据要满足一定的限制条件。
（1）使用INSERT语句进行插入操作的视图必须能够在基表中插入数据，否则插入操作会失败。
（2）如果视图上没有包括基表中所有属性为NOT NULL的行，那么插入操作会由于那些列的NULL值而失败。
（3）如果在视图中使用集合函数的结果，或者是包含多个列值的组合，则插入操作不成功。
（4）不能在使用了DISTINCT、GROUP BY或HAVING语句的视图中插入数据。
（5）如果创建视图的CREATE VIEW语句中使用了WITH CHECK OPTION，那么所有对视图进行修改的语句必须符合WITH CHECK OPTION中的限定条件。
（6）对于由多个基表连接而生成的视图来说，一次插入操作只能作用于一个基表上。

INSERT INTO 学生_view VALUES('11101004', '张三',1)

使用update更新数据

如果通过视图修改存在于多个基表中的数据，则对不同的基表要分别使用UPDATE语句来实现，这是因为每次只能对一个基表中的数据进行更新。
USE 学生选课 GO UPDATE 学生_view SET 性别=0 WHERE姓名='张三' GO

使用delete删除数据

在视图中删除的数据同时在基表中也被删除。当一个视图连接了两个以上的基表时，对数据的删除操作则是不允许的。
DELETE FROM 学生_view WHERE 姓名='张三'

存储过程 （PROCEDURE）

（1）它能够包含执行各种数据库操作的语句，并且可以调用其他的存储过程。
（2）能够接受输入参数，并以输出参数的形式将多个数据值返回给调用程序（Calling Procedure）或批处理（Batch）。
（3）向调用程序或批处理返回一个状态值，以表明成功或失败（以及失败的原因）。

存储过程的类型

1. 系统存储过程

在master数据库中创建，由系统管理。所有系统存储过程的名字均以“sp_”开始。

2. 本地存储过程

是用户自行创建并存储在用户数据库中的存储过程，是由用户创建的、能完成某一特定功能的可重用代码的模块或例程。
Transact-SQL存储过程是指保存的Transact-SQL语句集合。
CLR存储过程指对Microsoft.NET Framework公共语言运行时（CLR）方法的引用，可以接收和返回用户提供的参数。

3. 临时存储过程

局部临时存储过程只能由创建该过程的连接使用。局部临时存储过程在当前会话结束时自动除去。局部临时存储过程的命名以“#”号开头
全局临时存储过程则可由所有连接使用。全局临时存储过程在使用该过程的最后一个会话结束时除去。全局临时存储过程的命名以“##”开头

4. 远程存储过程

位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。

5. 扩展存储过程

使用编程语言（如C语言）创建的外部例程，是指Microsoft SQL Server的实例可以动态加载和运行的DLL。扩展存储过程直接在SQL Server实例的地址空间中运行，可以使用SQL Server扩展存储过程API完成编程。为了区别，扩展存储过程的名称通常以“xp_”开头。扩展存储过程一定要存储在系统数据库master中。

存储过程的作用

（1）通过本地存储、代码预编译和缓存技术实现高性能的数据操作。
（2）通过通用编程结构和过程实现编程框架。如果业务规则发生变化，可以通过修改存储过程来适应新的业务规则，而不必修改客户端的应用程序。这样所有调用该存储过程的应用程序就会遵循新的业务规则。
（3）通过隔离和加密的方法提高数据库的安全性。数据库用户可以通过得到权限来执行存储过程，而不必给予用户直接访问数据库对象的权限，这些对象将由存储过程来执行操作。另外，存储过程可以加密，这样用户就无法阅读存储过程中的Transact-SQL语句。这些安全特性将数据库结构和数据库用户隔离开来，这也进一步保证了数据的完整性和可靠性。

创建和执行存储过程

创建和执行不带参数的存储过程

　　CREATE PROC[EDURE] procedure_name [; number] 
　　[ { @parameter data_type }[VARYING ][=default][OUTPUT]][,...n]  
　　[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]  
　　[ FOR REPLICATION ]  
　　AS sql_statement [ ...n ]

参数说明：
（1）procedure_name：存储过程的名称。过程名必须符合标识符命名规则，且对于数据库及其所有者必须唯一。要创建局部临时存储过程，在过程名前面加一个编号符（#），要创建全局临时存储过程，在过程名前面加两个编号符（##）。完整的过程名称（包括#或##）不能超过128个字符。指定过程所有者的名字是可选的。
（2）number：可选的整数，用来对同名的过程分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起删除。例如，名为orders的应用程序使用的存储过程可以命名为orderproc;1、orderproc;2等。DROP PROCEDURE orderproc语句将删去整个过程组中所有的过程。如果过程名称中包含定界标识符，则数字不应包含在标识符中，只应在过程名前后使用适当的定界符。
（3）@parameter：过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有2 100个参数。使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其他过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其他数据库对象的名称。
（4）data_type：参数的数据类型。所有数据类型（包括text、ntext和image）均可以用作存储过程的参数。不过，cursor数据类型只能用于OUTPUT参数。如果指定的数据类型为cursor，必须同时指定VARYING和OUTPUT关键字。
（5）VARYING：指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。
（6）default：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或NULL。如果过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符（%、_、[]和[^]）。
（7）OUTPUT：表明参数是返回参数。使用OUTPUT参数可将信息返回给调用过程。text、ntext和image数据类型的参数也可用作OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。
（8）n：表示最多可以指定2 100个参数的占位符。
（9）{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}：RECOMPILE表明SQL Server不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，可使用RECOMPILE选项。ENCRYPTION用于指定存储过程文本加密，使用ENCRYPTION可防止将过程作为SQL Server复制的一部分发布。
（10）FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。使用FOR REPLICATION选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和WITH RECOMPILE选项一起使用。
（11）AS：指定过程要执行的操作。
（12）sql_statement：过程中包含的任意数目和类型的Transact-SQL语句。

• 创建不带参数的存储过程：

CREATE PROCEDURE stu_chin 
AS 
SELECT * FROM students WHERE chinese = 80

• 执行不带参数的存储过程：

EXEC stu_chin

• 在“可编程性”右键查看存储过程属性和修改存储过程的定义

创建和执行带输入参数的存储过程

• 创建带输入参数的存储过程

CREATE PROCEDURE Chinese_score
@输入成绩 int
AS 
SELECT * FROM students WHERE chinese = @输入成绩

• 执行带输入参数的存储过程

1. 使用参数名传递参数值（可读性强）

EXEC Chinese_score @输入成绩= 80

2. 按位置传递参数

EXEC Chinese_score 70

创建和执行带输出参数的存储过程

• @parameter_name datatype[=default] OUTPUT
• 创建存储过程

CREATE PROCEDURE english_score
@输入成绩 int,@sutnum_80 smallint output
as 
select @sutnum_80 = COUNT(*) from students where english = 80

• 执行存储过程

DECLARE @abc smallint 
EXEC english_score 80,@abc output 
SELECT @abc

-- 创建存储过程
-- 根据用户给定的性别，统计“教师”表性别为该值的教师人数
USE 学生选课 
GO 
CREATE PROCEDURE p_jiaoshi4 
(@in_sex char(2),@out_num int OUTPUT) 
WITH RECOMPILE 
AS 
  BEGIN 
    IF @in_sex='男' 
      SELECT @out_num=COUNT(性别) FROM 教师 
      WHERE 性别=1 
    ELSE 
      SELECT @out_num=COUNT(性别) FROM 教师 
      WHERE 性别=0 
   END 
  GO
-- 执行存储过程
DECLARE @abc INT
EXEC p_jiaoshi4 '男',@abc OUTPUT 
SELECT @abc

管理存储过程

查看存储过程

（1）sp_help 用于显示存储过程的参数及其数据类型。
（2）sp_helptext 用于显示存储过程的定义语句。

修改存储过程

 ALTER PROCEDURE p_jiaoshi1 
  @模糊学历 varchar(10) 
  WITH ENCRYPTION 
AS 
  SELECT * FROM 教师 WHERE 学历 LIKE '%'+@模糊学历+'%' 
GO

删除存储过程

DROP PROCEDURE p_jiaoshi2

存储过程的重命名

SP_RENAME p_jiaoshi3 p_j s3
GO

触发器 （ TRIGGER ）

触发器是一种特殊类型的存储过程，一般存储过程通过调用而执行，而触发器不需要使用EXEC命令调用，而在某个指定的事件发生时被激活。
触发器通常可以指定一定的业务规则，用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能的限制。

DDL 触发器

主要包括 create alter drop

DML 触发器的类型

（1）AFTER 触发器：这种类型的触发器将在数据变动（UPDATE、INSERT或DELETE操作）完成后才被激发。这种触发器可以用来对变动的数据进行检查，如果发现错误，将拒绝或回滚变动的数据。
（2）INSTEAD OF 触发器：INSTEAD OF触发器是自SQL Server 2000版本后增加的功能。这种类型的触发器将在数据变动之前被激发，并取代变动数据的操作（UPDATE、INSERT或DELETE操作），转而去执行触发器定义的操作。

触发器的优点

（1）强制比CHECK约束更复杂的数据完整性。在数据库中要实现数据的完整性约束，可以使用CHECK约束或触发器来实现。但是在CHECK约束中不允许引用其他表中的列，而触发器可以引用其他表中的列来完成数据的完整性约束。
（2）使用自定义的错误提示信息。用户有时需要在数据完整性遭到破坏或其他情况下，使用预先自定义好的错误提示信息或动态自定义的错误提示信息。通过使用触发器，用户可以捕获破坏数据完整性的操作，并返回自定义的错误提示信息。
（3）触发器可以通过数据库中的相关表进行级联更改。例如，可以在学生表的“学号”列上写入一个删除触发器，其他关联表中也有和学生表中“学号”相同的列，则可以实现当在学生表删除一个学生时，触发器自动在其他表中的匹配行采取删除操作，以实现相关联的表中数据保持参照完整性。
（4）比较数据库修改前后数据的状态。触发器提供了访问由INSERT、UPDATE或DELETE语句引起的数据前后状态变化的能力，用户可以在触发器中引用由于修改所影响的记录行。
（5）维护规范化数据。用户可以使用触发器来保证非规范数据库中的低级数据的完整性。维护非规范化数据与表的级联是不同的，表的级联指的是不同表之间的主外键关系，维护表的级联可以通过设置表的主键与外键的关系来实现。而非规范数据通常是指在表中派生的、冗余的数据值，维护非规范化数据应该通过使用触发器来实现。

创建触发器 ( TRIGGER )

CREATE TRIGGER trigger_name  
ON { table | view }  
[ WITH ENCRYPTION ]  
{ 
    { { FOR | AFTER | INSTEAD OF } { [DELETE] [,INSERT] [,UPDATE] } 
        [ NOT FOR REPLICATION ] 
        AS 
        [ { IF UPDATE ( column ) 
            [ { AND | OR } UPDATE ( column ) ] 
                [ ...n ] 
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) 
        { comparison_operator } column_bitmask [ ...n ] 
        } ]  
        sql_statement [ ...n ]  
    }  
}

INSERT 触发器

来验证被触发器监控的字段中的数据是否满足要求的标准，以确保数据完整性。
AFTER 类型触发器是在系统执行到INSERT语句时被触发，在INSERT语句执行完毕后再去执行触发器的相关操作；
INSTEAD OF 类型触发器是在系统执行到INSERT语句时被触发，但在INSERT语句执行前即执行触发器相关操作，而该INSERT语句则不再执行。

CREATE TRIGGER stu_trig
ON student        
AFTER INSERT 
AS
PRINT '已成功向学生表中添加一条记录！'
GO
-- insert 插入数据完成后 触发触发器 
insert into student(s_name, age) values ('jay',13)

CREATE TRIGGER stu_trig_insread
ON student        
INSTEAD OF INSERT
AS
PRINT '您未被授权执行插入操作！'
GO
-- insert 插入数据之前 触发触发器 
insert into student(s_name, age) values ('jay',13)

UPDATE 触发器

CREATE TRIGGER stu_after_update
ON student
AFTER UPDATE
AS 
IF UPDATE(s_name)
BEGIN
PRINT '禁止修改学生姓名'
ROLLBACK
END
GO
-- update s_name 不允许，触发触发器
update student set s_name = 'mike' where s_id = 1003
-- update age 被允许，不触发
update student set age = 12  where s_id = 1003

CREATE TRIGGER stu_inst_update ON student
INSTEAD OF UPDATE
AS
PRINT '您未被授权对表进行更新！' 
-- update student 表不被允许，执行前触发
update student set s_name = 'mike' where s_id = 1003

DELETE 触发器

CREATE TRIGGER stu_after_delete
ON student       
AFTER DELETE     
AS 
BEGIN 
RAISERROR('对不起，您不能执行删除操作',10,1)
ROLLBACK TRANSACTION
END
-- delect student 表不被允许，触发 stu_after_delete
DELETE FROM student
WHERE s_name = 'lisi'

RAISERROR():
第一个参数：{ msg_id | msg_str | @local_variable }
msg_id：表示可以是一个sys.messages表中定义的消息代号；
使用 sp_addmessage 存储在 sys.messages 目录视图中的用户定义错误消息号。
用户定义错误消息的错误号应当大于 50000。
msg_str：表示也可以是一个用户定义消息，该错误消息最长可以有 2047 个字符；
（如果是常量，请使用N’xxxx’，因为是nvarchar的）
当指定 msg_str 时，RAISERROR 将引发一个错误号为 5000 的错误消息。
@local_variable：表示也可以是按照 msg_str 方式的格式化字符串变量。
第二个参数：severity
用户定义的与该消息关联的严重级别。（这个很重要）
任何用户都可以指定 0 到 18 之间的严重级别。
[0,10]的闭区间内，不会跳到catch；
如果是[11,19],则跳到catch；
如果[20,无穷)，则直接终止数据库连接；
第三个参数：state
如果在多个位置引发相同的用户定义错误，
则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。
介于 1 至 127 之间的任意整数。（state 默认值为1）
当state 值为 0 或大于 127 时会生成错误！
第四个参数：argument
用于代替 msg_str 或对应于 msg_id 的消息中的定义的变量的参数。
第五个参数：option
错误的自定义选项，可以是下表中的任一值：
LOG ：在错误日志和应用程序日志中记录错误；
NOWAIT：将消息立即发送给客户端；
SETERROR：将 @@ERROR 值和 ERROR_NUMBER 值设置为 msg_id 或 50000；

CREATE TRIGGER stud_after_delete1
ON student      
INSTEAD OF DELETE 
AS 
  PRINT '您未被授权对表删除操作！' 
-- delect student表不被允许，触发stu_after_delete
DELETE FROM student WHERE s_name = 'lisi'

INSERTED 表和 DELETED 表

DELETED 表用于存储 DELETE 和 UPDATE 语句所影响的行的副本。在执行 DELETE 或 UPDATE 语句时，相关行从触发器表中删除，并传输到 DELETED 表中。DELETED 表和触发器表通常没有相同的行。
INSERTED 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新操作中，新建行被同时添加到INSERTED表和触发器表中。INSERTED 表中的行是触发器表中新行的副本。

（1）INSERT 操作：插入到表中的新行被复制到 INSERTED 表中。

create TRIGGER order_trig on orderitems
for insert
as
begin 
declare @num int
select @num = INSERTED.order_num from INSERTED   -- INSERTED表
insert into orders(order_num) values (@num)
PRINT ('订单信息已添加至orders表中')
end
-- 往 orderitems 表插入数据，orders 表也一起更新
insert into orderitems values (20888, 9, 'LOOP', 50, 3.65)

（2）DELETE 操作：从表中删除的行转移到了 DELETED 表中。

CREATE TRIGGER stud_after_delete
ON student       
AFTER DELETE     
AS 
BEGIN 
RAISERROR('对不起，您不能执行删除操作',10,1)
ROLLBACK TRANSACTION
END
-- DELETE 操作
DELETE FROM student WHERE s_name = 'lisi'

（3）UPDATE 操作：先从表中删除旧行，然后向表中插入新行。其中，删除后的旧行转移到 DELETED 表中，插入表中的新行被复制到 INSERTED 表中。

-- 在 orderitems 表中设置触发器
CREATE TRIGGER order_trig_update on orderitems
for update
as
begin 
declare @p_id varchar(255) ,@p_pr float(3)
select @p_id = DELETED.prod_id FROM DELETED 
select @p_pr = INSERTED.item_price FROM INSERTED 
UPDATE products 
SET prod_price = @p_pr
where prod_id = @p_id
PRINT ('已修改orders表中的order_num信息')
end
-- 更新 orderitems 表中数据，products 表中数据也一起更新
UPDATE orderitems set item_price = 78.77 where prod_id = 'BR01'

管理触发器

查看触发器的定义

1. 使用系统存储过程
   （1）通过 sp_help 系统存储过程，可以了解触发器的一般信息（名字、属性、类型、创建时间）。
   （2）通过 sp_helptext 能够查看触发器的定义信息。
   （3）通过 sp_depends 能够查看指定触发器所引用的表或指定的表所涉及的所有触发器。
2. 使用系统表

SELECT name FROM sysobjects WHERE type='TR'

3. 在“对象资源管理器”中查看触发器

修改触发器

1. 使用 sp_rename 命令修改触发器的名字

sp_rename oldname,newname

2. 使用 SQL Server Management Studio 查询分析器窗口修改触发器定义
3. 通过 ALTER TRIGGER 命令修改触发器的定义文本
4. 禁止和启用触发器

禁用触发器：
ALTER TABLE 表名称 DISABLE TRIGGER 触发器名称
再次恢复启用触发器：
ALTER TABLE 表名称 ENABLE TRIGGER 触发器名称
禁用或启用某个表上的所有触发器：
ALTER TABLE 表名称 DISABLE TRIGGER
ALL ALTER TABLE 表名称 ENABLE TRIGGER ALL

删除触发器

DROP TRIGGER xuesheng_tri2

1. 删除触发器所在的表时，该表上所有的触发器将被一并删除。
2. 进入“对象资源管理器”面板，找到相应的触发器并用鼠标右键单击，在弹出的菜单中，选择“删除”命令即可直接删除触发器。

DATE 日期

函数	描述
GETDATE()	返回当前的日期和时间
DATEPART()	返回日期/时间的单独部分
DATEADD()	在日期中添加或减去指定的时间间隔
DATEDIFF()	返回两个日期之间的时间
CONVERT()	用不同的格式显示日期/时间

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式：YYYY-MM-DD
• DATETIME - 格式：YYYY-MM-DD HH:MM:SS
• SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式：唯一的数字

NULL

-- 筛选 NULL 值
WHERE Address IS NULL
-- 筛选非 NULL 值
WHERE Address IS NOT NULL

NULL 函数

ISNULL()【SQL Server / MS Access】、NVL()【Oracle】、IFNULL() 和 COALESCE() 【MySQL】

SELECT ProductName,UnitPrice*(ISNULL(UnitsOnOrder,0))
FROM Products

数据类型

SQL 通用数据类型

数据类型	描述
CHARACTER(n)	字符/字符串。固定长度 n。
VARCHAR(n) 或
CHARACTER VARYING(n)	字符/字符串。可变长度。最大长度 n。
BINARY(n)	二进制串。固定长度 n。
BOOLEAN	存储 TRUE 或 FALSE 值
VARBINARY(n) 或
BINARY VARYING(n)	二进制串。可变长度。最大长度 n。
INTEGER§	整数值（没有小数点）。精度 p。
SMALLINT	整数值（没有小数点）。精度 5。
INTEGER	整数值（没有小数点）。精度 10。
BIGINT	整数值（没有小数点）。精度 19。
DECIMAL(p,s)	精确数值，精度 p，小数点后位数 s。例如：decimal(5,2) 是一个小数点前有 3 位数，小数点后有 2 位数的数字。
NUMERIC(p,s)	精确数值，精度 p，小数点后位数 s。（与 DECIMAL 相同）
FLOAT§	近似数值，尾数精度 p。一个采用以 10 为基数的指数计数法的浮点数。该类型的 size 参数由一个指定最小精度的单一数字组成。
REAL	近似数值，尾数精度 7。
FLOAT	近似数值，尾数精度 16。
DOUBLE PRECISION	近似数值，尾数精度 16。
DATE	存储年、月、日的值。
TIME	存储小时、分、秒的值。
TIMESTAMP	存储年、月、日、小时、分、秒的值。
INTERVAL	由一些整数字段组成，代表一段时间，取决于区间的类型。
ARRAY	元素的固定长度的有序集合
MULTISET	元素的可变长度的无序集合
XML	存储 XML 数据

numeric & decimal 区别：
numeric 必须与其定义的一样精确，因此，如果定义了4个小数位，则必须始终存储4个小数位
只能在指定了精度的情况下指定小数位，最大存储大小基于精度而变化
小数位数必须是从0到m之间的值(0 <= n<= m)，要求固定精度和小数位一样精确
decimal 必须至少与定义一样精确。 这意味着数据库实际上可以存储比指定更多的数字（由于后台存储空间可用于额外的数字）
这意味着如果定义了4个小数位，数据库可能存储1.00005而不是1.0000 ，影响将来的计算
小数位长度不一定，可能会大于或等于固定精度m的长度，即小数位的长度在存储的时候比实际的长，只是我们看不到

SQL 数据类型快速参考手册

数据类型	Access	SQLServer	Oracle	MySQL	PostgreSQL
boolean	Yes/No	Bit	Byte	N/A	Boolean
integer	Number (integer)	Int	Number	Int
Integer	Int
Integer
float	Number (single)	Float
Real	Number	Float	Numeric
currency	Currency	Money	N/A	N/A	Money
string (fixed)	N/A	Char	Char	Char	Char
string (variable)	Text (<256)
Memo (65k+)	Varchar	Varchar
Varchar2	Varchar	Varchar
binary object	OLE Object Memo	Binary (fixed up to 8K)
Varbinary (<8K)
Image (<2GB)	Long
Raw	Blob
Text	Binary
Varbinary

SQL Server 数据类型

String 类型：

数据类型	描述	存储
char(n)	固定长度的字符串。最多 8,000 个字符。	Defined width
varchar(n)	可变长度的字符串。最多 8,000 个字符。	2 bytes + number of chars
varchar(max)	可变长度的字符串。最多 1,073,741,824 个字符。	2 bytes + number of chars
text	可变长度的字符串。最多 2GB 文本数据。	4 bytes + number of chars
nchar	固定长度的 Unicode 字符串。最多 4,000 个字符。	Defined width x 2
nvarchar	可变长度的 Unicode 字符串。最多 4,000 个字符。
nvarchar(max)	可变长度的 Unicode 字符串。最多 536,870,912 个字符。
ntext	可变长度的 Unicode 字符串。最多 2GB 文本数据。
bit	允许 0、1 或 NULL
binary(n)	固定长度的二进制字符串。最多 8,000 字节。
varbinary	可变长度的二进制字符串。最多 8,000 字节。
varbinary(max)	可变长度的二进制字符串。最多 2GB。
image	可变长度的二进制字符串。最多 2GB。

Number 类型：

数据类型	描述	存储
tinyint	允许从 0 到 255 的所有数字。	1 字节
smallint	允许介于 -32,768 与 32,767 的所有数字。	2 字节
int	允许介于 -2,147,483,648 与 2,147,483,647 的所有数字。	4 字节
bigint	允许介于 -9,223,372,036,854,775,808 与 9,223,372,036,854,775,807 之间的所有数字。	8 字节
decimal(p,s)	固定精度和比例的数字。
允许从 -10^38 +1 到 10^38 -1 之间的数字。
p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。
s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。	5-17 字节
numeric(p,s)	固定精度和比例的数字。
允许从 -10^38 +1 到 10^38 -1 之间的数字。
p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。
s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。	5-17 字节
smallmoney	介于 -214,748.3648 与 214,748.3647 之间的货币数据。	4 字节
money	介于 -922,337,203,685,477.5808 与 922,337,203,685,477.5807 之间的货币数据。	8 字节
float(n)	从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。
n 参数指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53。	4 或 8 字节
real	从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。	4 字节

Date 类型：

数据类型	描述	存储
datetime	从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。	8 字节
datetime2	从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。	6-8 字节
smalldatetime	从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。	4 字节
date	仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。	3 bytes
time	仅存储时间。精度为 100 纳秒。	3-5 字节
datetimeoffset	与 datetime2 相同，外加时区偏移。	8-10 字节
timestamp	存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 值基于内部时钟，不对应真实时间。每个表只能有一个 timestamp 变量。

其他数据类型：

数据类型	描述
sql_variant	存储最多 8,000 字节不同数据类型的数据，除了 text、ntext 以及 timestamp。
uniqueidentifier	存储全局唯一标识符 (GUID)。
xml	存储 XML 格式化数据。最多 2GB。
cursor	存储对用于数据库操作的指针的引用。
table	存储结果集，供稍后处理。

函数

AVG() 平均值

SELECT AVG(column_name) FROM table_name

COUNT() 计数

-- 返回匹配指定条件的行数
SELECT COUNT(count) AS nums FROM access_logWHERE site_id=3;
-- 函数返回表中的记录数
SELECT COUNT(*) AS nums FROM access_log;
-- 函数返回指定列的不同值的数目
SELECT COUNT(DISTINCT site_id) AS nums FROM access_log; 
--
select
count(start_time) as total_pv, 
count(score) as complete_pv, 
(select count(distinct exam_id) from exam_record where score is not null) as complete_exam_cnt
from exam_record

FIRST()

只有 MS Access 支持 FIRST() 函数

SELECT TOP 1 prod_name FROM Products
ORDER BY prod_id ASC;

LAST()

只有 MS Access 支持 LAST() 函数

SELECT TOP 1 prod_name FROM Products
ORDER BY prod_id DESC;

MAX()

SELECT MAX(prod_price) as max_price from Products 

MIN()

SELECT MIN(prod_price) as min_price from Products 

SUM()

SELECT SUM(prod_price) as sum_price from Products 

GROUP BY

用于结合聚合函数，根据一个或多个列对结果集进行分组，非聚合的字段必须用聚合函数包起来比如sum, avg, min, max

SELECT sum(prod_price) as sum_price, vend_id  FROM Products group by vend_id 
-- 多表链接排序
SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;	
-- 聚合函数
select
    order_num,
    sum((item_price * quantity)) as total_price
from
    OrderItems
group by order_num
having sum((item_price * quantity)) >= 1000

HAVING

WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。
where 和having之后都是筛选条件，但是有区别的：
1.where在group by前， having在group by 之后
2.聚合函数（avg、sum、max、min、count），不能作为条件放在where之后，但可以放在having之后

SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200 
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

EXISTS

SELECT Websites.name, Websites.url
FROM Websites 
WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

-- NOT EXISTS查找出不符合查询语句的记录

UCASE() /** UPPER() **

SELECT UPPER(name) AS site_title, url
FROM Websites;

-- 拼接 concat()
select cust_id, cust_name, 
concat(UPPER(mid(cust_name,1,2)),upper(mid(cust_city,1,3))) as user_login
from Customers

LCAS() / LOWER()

SELECT LOWER(name) AS site_title, url
FROM Websites;

MID() / SUBSTRING()

SELECT SUBSTRING(name,1,2) AS ShortTitle
FROM Websites;

LEN()

SELECT name, LEN(url) as LengthOfURL
FROM Websites;

ROUND()

SELECT ROUND(alexa,2) as rou_alexa FROM Websites
-- 转换一个常数
SELECT ROUND(-1.23,0)
-- 截断平均值（去掉一个最大值和一个最小值后的平均值）
select tag, difficulty,
round((sum(score) - max(score) - min(score))/(count(score)-2),1) as clip_avg_score
from examination_info ei join exam_record er
on ei.exam_id = er.exam_id
where tag = 'SQL' and difficulty = 'hard'

NOW() / getDate()

SELECT name, url, GetDate() AS date
FROM Websites;

FORMAT()

select order_num,order_date
from Orders
where date_format(order_date,'%Y-%m')='2020-01'
order by order_dateSELECT name, url, FORMAT(GetDate(),'dd/MM/yyyy') AS date
FROM Websites;
-- 数字格式
SELECT FORMAT(123456789,'###-##-####')
-- mysql
date_format(order_date,'%Y-%m')='2020-01'

REPLACE()

-- REPLACE ( original-string, search-string, replace-string ) 
-- REPLACE("被搜索的字符串","被替换的字符串","替换的字符串")
-- 将表[dbo].[Products]中的Content字段中的 abc 替换为 ddd 
 update [dbo].[Products] SET Content=replace(Content,'abc','ddd')
-- 不支持 text,ntext类型字段的替换，可以写成：
update [dbo].[Products] SET Content=replace(cast(Content AS varchar(8000)),'abc','ddd') 

-- REPLACE INTO 等同于先删除后插入
REPLACE INTO examination_info
VALUES(NULL,9003,'SQL','hard',90,'2021-01-01 00:00:00');
-- 先删除后插入
DELETE FROM examination_info
WHERE exam_id=9003;
INSERT INTO examination_info
VALUES(NULL,9003, 'SQL','hard', 90, '2021-01-01 00:00:00')

时间函数

DATEDIFF() 函数返回两个日期之间的时间。

select * FROM exam_record
WHERE DATEDIFF(MINUTE, start_time, submit_time) < 5
AND score < 60;

getdate() 函数从 SQL Server 返回当前的时间和日期。
datepart() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等

select datepart(yyyy,getdate()) as Year,
datepart(mm,getdate()) as Month,
datepart(dd,getdate()) as Day

datepart	缩写
年	yy,yyyy
季度	qq,q
月	mm,m
年中的日	dy,y
日	dd,d
周	wk,ww
星期	dw,w
小时	hh
分钟	mi,n
秒	ss,s
毫秒	ms
微秒	mcs
纳秒	ns

进阶练习

update A01 
set A0177 = case A0190
when '5' then 8889999
when '7' then 8889955
when '61' then 8889966
else A0177
end
where A0190 in('5','7','61')

select "E".ename "员工", "E".sal "薪水", "S".grade "薪资等级"
    from emp "E"
    join dept "D"
    on "E".deptno = "D".deptno
    join salgrade "S"
    on "E".sal >= "S".losal and "E".sal <= "S".hisal;

变量

(1)局部变量：定义方式 declare @变量名 数据类型
(2)全局变量：
@@ERROR返回最后执行的sql是否报错，如果报错，这个变量就不为0 ，这边变量经常用来判断上一句是否执行成功。
@@ROWCOUNT则返回上一句执行所影响的行数，常用来判断INSERT 是否成功。

begin transaction
declare @errorSum int
set @errorSum = 0
update bank set currentMoney = currentMoney - 1000
    where customerEname='Tom'
set @errorSum = @errorSum + @@error
update bank set currentMoney = currentMoney + 1000
    where customerEname='Jack'
set @errorSum = @errorSum + @@error
if(@errorSum<>0)
    begin
        print '转账失败'
        rollback transaction
    end

select (case when a>b then a else b end ), (case when b>c then b esle c end)
from table_name 

DATEDIFF(datepart,startdate,enddate)

DATEDIFF() 函数返回两个日期之间的时间

select * from tb where datediff(dd,SendTime,getdate())=0

select name, 
(case when chinese>=80 then '优秀'
when chinese>=60 then '及格'
else '不及格' end)as 语文,
(case when math>=80 then '优秀'
when math>=60 then '及格'
else '不及格' end) as 数学,
(case when english>=80 then '优秀'
when english>=60 then '及格'
else '不及格' end) as 英语
from Students 

**条件查询 (constraints) **

Operator（关键字）	Condition（意思）	SQL Example(例子）
=, !=, < , <=, >, >=	Standard numerical operators 基础的 大于，等于等比较	col_name != 4
BETWEEN … AND …	Number is within range of two values (inclusive) 在两个数之间	col_name BETWEEN 1.5 AND 10.5
NOT BETWEEN … AND …	Number is not within range of two values (inclusive) 不在两个数之间	col_name NOT BETWEEN 1 AND 10
IN (…)	Number exists in a list 在一个列表	col_name IN (2, 4, 6)
NOT IN (…)	Number does not exist in a list 不在一个列表	col_name NOT IN (1, 3, 5)

Operator（操作符）	Condition（解释）	Example（例子）
=	Case sensitive exact string comparison (notice the single equals)完全等于	col_name = “abc”
!= or <>	Case sensitive exact string inequality comparison 不等于	col_name != “abcd”
LIKE	Case insensitive exact string comparison 没有用通配符等价于 =	col_name LIKE “ABC”
NOT LIKE	Case insensitive exact string inequality comparison 没有用通配符等价于 !=	col_name NOT LIKE “ABCD”
%	Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) 通配符，代表匹配0个以上的字符	col_name LIKE “%AT%”
(matches “AT”, “ATTIC”, “CAT” or even “BATS”) “%AT%” 代表AT 前后可以有任意字符
_	Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) 和% 相似，代表1个字符	col_name LIKE “AN_”
(matches “AND”, but not “AN”)
IN (…)	String exists in a list 在列表	col_name IN (“A”, “B”, “C”)
NOT IN (…)	String does not exist in a list 不在列表	col_name NOT IN (“D”, “E”, “F”)

where prod_desc like'%toy%' and prod_desc like'%carrots%'

查询结果Filtering过滤 和 sorting排序

DISTINCT 语法会直接删除重复的行； GROUP BY 语句会返回唯一的行，不过可以对具有相同的 属性值的行做一些统计计算。（ASC/DESC）
LIMIT 和 OFFSET 子句通常和ORDER BY 语句一起使用，当我们对整个结果集排序之后，我们可以 LIMIT来指定只返回多少行结果 ,用 OFFSET来指定从哪一行开始返回。
ORDER BY xxx esc, xxx desc

-- 排重正序排列
SELECT distinct Director ASC FROM movies;

SELECT * FROM movies ORDER BY Year DESC limit 4 offset 0 ;
-- 第3长的电影
SELECT Title FROM movies where Director = 'John Lasseter'  
ORDER BY Length_minutes ASC 
limit 1 offset 2 ;

用JOINs进行多表联合查询

SELECT * FROM movies  
JOIN Boxoffice ON movies.Id = Boxoffice.Movie_id
WHERE Domestic_sales<International_sales;

**

左连接LEFT JOIN,右连接RIGHT JOIN 和 全连接FULL JOIN

SELECT DISTINCT Buildings.Building_name FROM employees 
LEFT join Buildings 
ON  employees.Building = Buildings.Building_name
WHERE employees.Building IS NOT NULL;

特殊关键字 NULLs

比如, 当你计算一些行的平均值的时候，如果是0会参与计算导致平均值差错，是NULL则不会参与计算

SELECT Building_name FROM Buildings
WHERE Building_name  NOT IN 
(SELECT DISTINCT Building FROM Employees WHERE Building IS NOT NULL )

在查询中使用表达式

-- 销售总额(以百万美元为单位计算)
SELECT Id, Title, (Domestic_sales+International_sales)/1000000 as sum_sales 
FROM movies left join Boxoffice on Id = Boxoffice.Movie_id;
-- 偶数年份
SELECT Id, Title, Year
FROM movies left join Boxoffice on Id = Boxoffice.Movie_id 
where (Year)%2 == 0
-- 每部电影每分钟值多少钱（前三名）
SELECT  Title, (Domestic_sales+International_sales)/Length_minutes as worth
FROM movies left join Boxoffice on Id = Boxoffice.Movie_id 
where Director = 'John Lasseter' order by worth desc limit 3

在查询中进行统计

统计函数：

Function	Description
COUNT(*), COUNT(column)	COUNT(*) 统计数据行数，COUNT(column) 统计column非NULL的行数
MIN(column)	找column最小的一行
MAX(column)	找column最大的一行
AVG(column)	对column所有行取平均值
SUM(column)	对column所有行求和

SELECT Building, count(	Building) as sum_person FROM employees
where Building is not null group by Building

– where后面不能跟聚合函数，having后面可以跟聚合函数

SELECT Role, count(	Role) as sum_person ,building is not null as hav_build
FROM employees group by Role ,building is not null

SELECT role,'0' AS '有无办公室' ,COUNT(role) as sum_role
FROM employees
WHERE building IS NOT NULL
GROUP BY role
union
SELECT role,'1' AS '有无办公室' ,COUNT(role) as sum_role
FROM employees
WHERE building IS NULL
GROUP BY role
ORDER BY role ASC

查询执行顺序

1. FROM 和 JOINs
   FROM 或 JOIN会第一个执行，确定一个整体的数据范围. 如果要JOIN不同表，可能会生成一个临时Table来用于 下面的过程。总之第一步可以简单理解为确定一个数据源表（含临时表)
2. WHERE
   我们确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选col属性 只能来自FROM圈定的表. AS别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式
3. GROUP BY
   如果你用了 GROUP BY 分组，那GROUP BY 将对之前的数据进行分组，统计等，并将是结果集缩小为分组数.这意味着 其他的数据在分组后丢弃.
4. HAVING
   如果你用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选。AS别名也不能在这个阶段使用.
5. SELECT
   确定结果之后，SELECT用来对结果col简单筛选或计算，决定输出什么数据.
6. DISTINCT
   如果数据行有重复DISTINCT 将负责排重.
7. ORDER BY
   在结果集确定的情况下，ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了。此时可以用AS别名.
8. LIMIT / OFFSET
   最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据.

SELECT DISTINCT Director,
sum(Boxoffice.Domestic_sales)+sum(Boxoffice.International_sales) as sum_sale 
FROM movies join Boxoffice on Boxoffice.Movie_id = Movies.Id group by Director

-- 按导演分组计算销售总额,求出平均销售额冠军
--（统计结果过滤掉只有单部电影的导演，列出导演名，总销量，电影数量，平均销量) 
select sum(Domestic_sales +International_sales) AS sum_sales,Director,
count(Director) AS mov_num,
sum(Domestic_sales +International_sales)/count(Director)  AS sum_sales
from Movies join Boxoffice on Movies.id = Boxoffice.Movie_id
group by Director having count(Director) >1
order by (sum_sales/count(Director)) DESC LIMIT 1

-- 找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额
select Title,
(select sum(Domestic_sales +International_sales) as sum_box from Boxoffice
group by Movie_id ORDER BY sum_box DESC LIMIT 1 )
-sum(Domestic_sales +International_sales)
 as sales_sub
from Movies join Boxoffice on Movies.id = Boxoffice.Movie_id
group by Title
-- 使用子查询
select prod_name, 
(select sum(quantity) from OrderItems o where o.prod_id = p.prod_id
group by prod_id ) as quant_sold
from Products p