一、創(chuàng)建表

在sql server中使用create table來(lái)創(chuàng)建新表。

create table Customers(
id int primary key identity(1,1),
name varchar(5)
)

該表名為Customers其中包含了2個(gè)字段，分別為id（主鍵）以及name。

1、數(shù)據(jù)類型

整數(shù)類型：

tinyint（1字節(jié)，從 0 到 255）

smallint（2字節(jié)，從 -32,768 到 32,767）

int（四字節(jié)，從 -2,147,483,648 到 2,147,483,647）

bigint（八字節(jié)，從 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807）

浮點(diǎn)數(shù)類型：

float（單精度或雙精度浮點(diǎn)數(shù)）

real（精確到 7 位小數(shù)的近似數(shù)值數(shù)據(jù)類型）

字符類型：

char(x)（固定存儲(chǔ)，只能存儲(chǔ)x個(gè)字符長(zhǎng)度，最大長(zhǎng)度為 8000 個(gè)字符）

varchar(x)（可變存儲(chǔ)，可以存儲(chǔ)0到x的字符長(zhǎng)度，最大長(zhǎng)度為 8000 個(gè)字符，如果x為max，那么可以存儲(chǔ)2^31-1 個(gè)字符）

nchar(x)（固定長(zhǎng)度的 Unicode 字符序列，最大長(zhǎng)度為 4000 個(gè)字符）

nvarchar(x)（可變長(zhǎng)度的 Unicode 字符序列，最大長(zhǎng)度為 4000 個(gè)字符）

日期類型：

date（僅存儲(chǔ)日期部分）

time（僅存儲(chǔ)時(shí)間部分）

datetime（存儲(chǔ)日期和時(shí)間部分）

datetime2（高精度的datetime）

二進(jìn)制類型：

binary(x)（固定長(zhǎng)度的二進(jìn)制數(shù)據(jù)）

varbinary()（可變長(zhǎng)度的二進(jìn)制數(shù)據(jù)）

?如果數(shù)據(jù)中有中文字符、日文、韓文等多字節(jié)字符，建議存放數(shù)據(jù)類型為nvarchar，可以有效避免亂碼。

二、定義函數(shù)

函數(shù)通常使用BEGIN...END塊來(lái)定義函數(shù)體，使用CREATE FUNCTION創(chuàng)建函數(shù)。以力扣177題第n高的薪水舉列子：

表:?Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
在 SQL 中，id 是該表的主鍵。
該表的每一行都包含有關(guān)員工工資的信息。

查詢?Employee?表中第?n?高的工資。如果沒(méi)有第?n?個(gè)最高工資，查詢結(jié)果應(yīng)該為?null?。

定義了一個(gè)名getNthHighestSalary的函數(shù)，該函數(shù)接收一個(gè)int類型參數(shù)N并且返回一個(gè)int類型值，在BEGIN后開(kāi)始我們的邏輯，RETURN具體要返回的值，以END標(biāo)志結(jié)尾。

CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGINRETURN (SELECT isnull((SELECT newsalary.salary FROM (SELECT DENSE_RANK() OVER (ORDER BY salary DESC) AS DenseRank ,salary FROM Employee GROUP BY Salary) AS newsalary WHERE newsalary.DenseRank=@N),null));
END
GO

調(diào)用這個(gè)函數(shù)SELECT dbo.getNthHighestSalary(要傳參的值);

擴(kuò)展：?

DENSE_RANK()窗口函數(shù)

DENSE_RANK()函數(shù)為數(shù)據(jù)集中的每一行分配一個(gè)連續(xù)的排名，這些排名是基于指定的排序順序進(jìn)行計(jì)算的，會(huì)保留排名的連續(xù)性。也就是說(shuō)，如果有兩個(gè)或多個(gè)相同的值，它們會(huì)獲得相同的排名，并且下一個(gè)不同值的排名會(huì)緊接著前一個(gè)排名的下一個(gè)整數(shù)，而不會(huì)跳過(guò)任何數(shù)字。

基本語(yǔ)法：DENSE_RANK() OVER (PARTITION BY [column] ORDER BY [column] [ASC|DESC])

ISNULL()函數(shù)

用于檢查指定的表達(dá)式是否為NULL，并在表達(dá)式為NULL時(shí)返回一個(gè)指定的替代值。

基本語(yǔ)法：ISNULL(expression, replacement_value)

expression：要檢查是否為NULL的表達(dá)式。

replacement_value：在expression為NULL時(shí)返回的替代值。

三、索引?

聚集索引

表中只能有一個(gè)，它決定了表中數(shù)據(jù)的物理存儲(chǔ)順序，在sql server中通常會(huì)默認(rèn)依據(jù)主鍵創(chuàng)建一個(gè)聚集索引。

列如我們創(chuàng)建一個(gè)表cs，對(duì)其中的id字段不設(shè)置主鍵，那么我們對(duì)這個(gè)表創(chuàng)建完成之后，不會(huì)有默認(rèn)的聚集索引。

create table cs(
id int
)

?

但是如果我們對(duì)該表cs的id字段添加primary key設(shè)置為主鍵，那么在創(chuàng)建表的同時(shí)會(huì)默認(rèn)根據(jù)id這個(gè)主鍵創(chuàng)建一個(gè)索引。

create table cs(
id int primary key
)

?

查看該索引的屬性，我們可以看到，是根據(jù)id字段進(jìn)行創(chuàng)建的該索引。?

非聚集索引

聚集索引一張表中只能存在一個(gè)，而非聚集索引一張表可以存在多個(gè)，它與表中的數(shù)據(jù)分開(kāi)存儲(chǔ)，包含指向表中數(shù)據(jù)的指針。

列如創(chuàng)建一個(gè)school表，其中包含三個(gè)字段，現(xiàn)在我用name列創(chuàng)建一個(gè)名idx_school的非聚集索引，創(chuàng)建完成之后，我們對(duì)school表中的name字段進(jìn)行查詢。

--創(chuàng)建表school
create table school(
id int identity(1,1),--id
name varchar(5),--姓名
age int--年齡
)--創(chuàng)建非聚集索引
CREATE NONCLUSTERED INDEX idx_school  
ON school(name);--查詢
select name from school

我們可以在執(zhí)行計(jì)劃中看到Index Scan,這表示我們成功使用了索引進(jìn)行查詢，使用的索引是[school].[idx_school]索引（就是school表下的idx_school索引），就是我們所創(chuàng)建的非聚集索引。

復(fù)合索引

由多個(gè)列組成的索引，根據(jù)列的順序來(lái)決定查詢速度的快慢，本質(zhì)上也是非聚集索引。

同樣使用非聚集索引的表，如果我們要使用非聚集索引查詢年齡為多大的姓名，在使用idx_school索引時(shí)，是不會(huì)觸發(fā)索引查詢的。

select name from school where age=12

?我們?cè)趫?zhí)行計(jì)劃中可以看到Table Scan這表示為表掃描，對(duì)整張表[school]進(jìn)行掃描查詢。

在這種情況下，我們就需要?jiǎng)?chuàng)建復(fù)合索引，創(chuàng)建完復(fù)合索引之后再執(zhí)行相同的查詢語(yǔ)句。

--創(chuàng)建復(fù)合索引
CREATE NONCLUSTERED INDEX idx_name_age
ON school (name ASC, age ASC);--ASC為升序，DESC為降序--查詢
select name from school where age=12

現(xiàn)在我們?cè)趫?zhí)行計(jì)劃中就看到Index Scan，使用的是[school].[idx_name_age]索引。?

刪除索引：DROP INDEX 索引名 ON 表名

四、視圖?

視圖是一種虛擬的表，其內(nèi)容由查詢定義，本身不存儲(chǔ)數(shù)據(jù)，而是根據(jù)查詢結(jié)果動(dòng)態(tài)生成數(shù)據(jù)，其每次查詢視圖時(shí)都會(huì)執(zhí)行背后的SQL查詢。

創(chuàng)建視圖

對(duì)school表中的name和age字段創(chuàng)建一個(gè)名newschool的視圖。

create view newschool as
select name,age from school

使用視圖?

和查詢普通表一樣進(jìn)行查詢視圖。

select * from newschool

?

?