T-sql 从入门到放弃

2019-07-10 本文已影响0人鲸鱼酱375

T-SQL是SQL语言的一种版本，且只能在SQL SERVER上使用。它是ANSI SQL的加强版语言、提供了标准的SQL命令。另外，T-SQL还对SQL做了许多补允，提供了类似C、Basic和Pascal的基本功能，如变量说明、流控制语言、功能函数等。
T-SQL代表的Transact结构查询语言是微软的产品，是SQL语言的扩展。

1.数据类型

1.1 精确数值类型

精确数值类型

1.2近似数值类型

近似数值

1.3日期和时间类型

image.png

1.4字符串

字符串

1.5 unicode 字符串

unicode

1.6 二进制字符串

二进制

1.7 其他数据类型

QL_VARIANT -存储各种SQL Server支持的数据类型的值，text，ntext和timestamp除外。
TIMESTAMP -时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数。
UNIQUEIDENTIFIER -存储全局唯一标识符（GUID）。
XML -存储XML数据。您可以将XML实例存储在列或变量中（在SQL Server 2005中引入）。
CURSOR -对游标的引用。
TABLE -存储结果集以供稍后处理。
HIERARCHYID -用于表示层次结构中位置的可变长度的系统数据类型（在SQL Server 2008中引入）。

2.创建表格

CREATE TABLE table_name( 
   column1 datatype, 
   column2 datatype, 
   column3 datatype, 
   ..... 
   columnN datatype, 
   PRIMARY KEY( one or more columns ));

查看表格是否创建成功

exec sp_columns CUSTOMERS

3.删除表格

3.1 drop

DROP TABLE table_name;

3.2 delete

DELETE FROM table_name 
WHERE [condition];

delete删除是可以有选择性的，也可以不用where筛选删除整个

4.插入数据

4.1为制定列添加值

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]   
VALUES (value1, value2, value3,...valueN);

4.2 为表的所有列添加值

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

4.3 使用另一个表的数据添加值

INSERT INTO first_table_name  
   SELECT column1, column2, ...columnN  
      FROM second_table_name 
      [WHERE condition];

5.update用法

UPDATE table_name 
SET column1 = value1, column2 = value2...., columnN = valueN 
WHERE [condition];

5.1 update与subquery子查询连用

UPDATE table 
SET column_name = new_value 
[ WHERE OPERATOR [ VALUE ] 
   (SELECT COLUMN_NAME 
   FROM TABLE_NAME) 
   [ WHERE) ]

例子：对CUSTOMERS表中AGE大于或等于27的所有客户的SALARY乘以0.25。
CUSTOMERS_BKP (ID NAME AGE ADDRESS SALARY )

UPDATE CUSTOMERS 
   SET SALARY = SALARY * 0.25 
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 )

6.like用法

百分号（％）
下划线（_）
百分号表示零个，一个或多个字符。下划线表示单个数字或字符。符号可以组合使用。

网站上面有个例子没太想明白：
查找以2开头且长度至少为3个字符的所有值

WHERE SALARY LIKE'2 _％_％“

start with "a" and are at least 3 characters in length

WHERE CustomerName LIKE 'a_%_%'

7. join

在MS SQL Server中有不同类型的连接 --

INNER JOIN -当两个表中都有匹配项时返回行。
LEFT JOIN -返回左侧表中的所有行，即使右表中没有匹配项。
RIGHT JOIN -返回右表中的所有行，即使左表中没有匹配项。
FULL JOIN -在其中一个表中存在匹配项时返回行。
SELF JOIN -这用于将表连接到自身，就像该表是两个表，临时重命名MS SQL Server语句中的至少一个表。
CARTESIAN JOIN -返回两个或多个联接表中的记录集的笛卡尔乘积。

8.存储

Create procedure <procedure_Name> 
As 
Begin 
<SQL Statement> 
End 
Go

9.事务

事务控制命令仅与DML命令INSERT，UPDATE和DELETE一起使用。在创建表或删除它们时，不能使用它们，因为这些操作会在数据库中自动提交。

为了在MS SQL Server中使用事务控制命令，我们必须以“begin tran”或begin transaction命令开始事务，否则这些命令将不起作用。

10.索引

10.1 创建索引

CREATE INDEX index_name ON table_name

单列索引创建
单列索引是基于仅一个表的单列创建的索引

CREATE INDEX index_name 
ON table_name (column_name)

唯一索引

CREATE UNIQUE INDEX index_name 
on table_name (column_name)

复合索引

CREATE INDEX index_name on table_name (column1, column2)

10.2 不适合用索引的情况

不应在小表上使用索引。
经常进行大批量更新或插入操作的表不应设置索引。
不应在可能含大量NULL值的列上使用索引。
频繁操作的列不应设置索引。

11.t-sql中的函数用法

LINK

12.变量

DECLARE @variablename data_type

12.1 变量的数据类型

varchar(n)
int
decimal(p,s)
numeric(p,s)

12.2 变量的设置

变量的设置

13.t-sql中的循环

image.png

14.JOIN

14.1 derived table

The focus of this lesson is derived tables. You can use derived tables when you want to break down a complex query into smaller steps. A derived table is a query which is used in the place of a table. Derived tables are a great solution if you want to create intermediate calculations that need to be used in a larger query.

SELECT a.RecordId, a.Age, a.BloodGlucoseRandom,  b.MaxGlucose   # Select maximum glucose value (use colname from derived table)          
FROM Kidney a
# Join to derived table
JOIN (SELECT Age, MAX(BloodGlucoseRandom) AS MaxGlucose FROM Kidney GROUP BY Age) b
# Join on Age
ON a.Age=B.AGE

15.CTE

WITH CTEName (col1,col2)
AS
(SELECT col1,col2
 from TableName)

15.1 用法

例子1

WITH BloodPressureAge(Age,MaxBloodPressure)
AS
(SELECT age,max(BloodPressure) as MaxBloodPressure
FROM Kidney
GROUP BY age)

SELECT a.age,MIN(a.BloodPressure),b.MaxBloodPressure
from Kidney a
join BloodPressureAge b
on a.Age=b.Age
GROUP BY a.age, b.MaxBloodPressure

例子2:return all the information regarding the patient(s) with the maximum BloodPressure

# Create the CTE
WITH BloodPressure
AS (select max(BloodPressure) as MaxBloodPressure from kidney)

SELECT *
FROM Kidney a
# Join the CTE  
join BloodPressure b
on a.BloodPressure=b.MaxBloodPressure

16.windows function

聚合函数中的字句可以是PARTITION BY子句，但不可是ORDER BY子句
排序函数(列) OVER(选项)，这里的选项可以是ORDER BY子句，也可以是　OVER（PARTITION BY子句　ORDER BY子句），但不可以是PARTITION BY子句

16.1 lead()

16.2 LAG()

16.4 LAST_VALUE()

16.5 rank()

16.6 stdev()

17. cte与windows function连用

例子

WITH QuotaCount AS (
SELECT SalesPerson, SalesYear, CurrentQuota,
ROW_NUMBER()
OVER (PARTITION BY CurrentQuota ORDER BY CurrentQuota) AS QuotaList
FROM SaleGoal)
SELECT * FROM QuotaCount;

参考资料：https://www.cnblogs.com/csdbfans/p/3504845.html

reference:
https://bbs.csdn.net/topics/320018055
https://www.w3cschool.cn/t_sql/t_sql-n1461we8.html