SQL Server

介绍

1. 什么是SQL
   Microsoft SQL Server(微软结构化查询语言服务器)是由美国微软公司所推出的关系数据库解决方案，最新的版本是SQL Server 2019。

2. Windows Azure SQL Database
   SQL Azure是微软Windows Azure Platform上的云端关系数据库服务（Database as a Service），早期被称为SQL Server Data Services或SQL Services，应用程序可以直接利用TDS over SSL来访问SQL Azure中的数据库，同时SQL Azure基于Windows Azure平台之上，亦具有与Windows Azure相同的高可用性（High Availability），高延展性 (High Extensibility)以及高扩展性（High Scalability）。

3. 数据库管理工具

• SQL Server Management Studio
  SQL Server默认的数据库管理工具，随着SQL Server安装时勾选客户端的选项而安装。

• Navicat for SQL Server
  是一套专为Microsoft SQL Server设计的强大数据库管理及开发工具。它可以用于任何版本的 SQL Server数据库，并支持大部分 SQL Server的功能，包括触发器、索引、查看等。断点。

基本用法

1. Transact-SQL（又称T-SQL) :核心查询语言

• 数据查询语言（DQL）
  主条目:SELECT、FROM、WHERE、GROUP BY和ORDER BY

  SELECT [查询的字段，或是用"*"代表全部的字段]
  FROM [表] -- 可搭配JOIN做关联表。
  WHERE [过滤條件] -- WHERE可有可无。
  GROUP BY [分组]
  HAVING [分组条件] -- 要使用统计函数作为条件使用
  ORDER BY [排序] -- ASC正序 DESC 降序

• 数据定义语言（DDL）

  • 创建(CREATE)
    CREATE DATABASE ：创建数据库
    CREATE INDEX：创建数据表索引。
    CREATE PROCEDURE：创建存储程序。
    CREATE FUNCTION：创建用户函数。
    CREATE VIEW：创建查看表。
    CREATE TRIGGER：创建触发程序。

  • 修改(ALTER)
    ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL;--添加列

  • 删除(DROP)
    DROP TABLE table; -- 刪除表。
    DROP VIEW view; -- 刪除 视图

• 数据操纵语言（DML）
  [SELECT], [INSERT], [UPDATE], [DELETE]
  CRUD:增读(read)改查

• 数据控制语言（DCL）
  GRANT（赋与权限）
  REVOKE（撤消权限）
  GRANT [权限] ON [数据库] TO [使用者账户] WITH [授权选项]

2. ADO.NET
   在ASP.NET，可以经由ADO.NET对于SQL Server进行访问。
   ADO.NET是微软在.NET Framework中负责数据访问的类别库集，它可以让.NET上的任何编程语言能够连接并访问关系数据库与非数据库型数据源（例如XML，Excel或是文字档数据），或是独立出来作为处理应用程序数据的类别对象，其在.NET Framework中的地位是举足轻重。ADO.NET类封装在System.Data.dll中，并且与System.Xml.dll中的XML类集成。

• Connection: 对象提供与数据源的连接。
• Command:对象能够访问用于返回数据、修改数据、运行存储过程以及发送或检索参数信息的数据库命令。
• DataReader :对象从数据源中提供快速的，只读的数据流。
• DataAdapter: 对象提供连接 DataSet 对象和数据源的桥梁。DataAdapter 使用 Command 对象在数据源中执行 SQL 命令，以便将数据加载到 DataSet 中，并使对 DataSet 中数据的更改与数据源保持一致。
• Parameter: 对象用于参数化查询。
• ConnectionStringBuilder:提供一种用于创建和管理由 Connection 对象使用的连接字符串的内容的简单方法。 所有 ConnectionStringBuilder 对象的基类均为 DbConnectionStringBuilder 类。
• CommandBuilder:自动生成 DataAdapter 的命令属性或从存储过程中派生参数信息，并填充 Command 对象的 Parameters 集合。 所有 CommandBuilder 对象的基类均为 DbCommandBuilder 类。
• 离线数据模型
  离线数据模型是微软为了改良ADO在网络应用程序中的缺陷所设计的，能在离线状态下处理数据的好帮手，也可以透过连线数据源对象，支持将离线数据存回数据库的能力。离线数据模型由下列对象组成：DataSet, DataTable, DataRow, DataColumn, DataView, DataRelation, Constraint

SQL进阶

1. SQL分页

• 利用max(主键)

select top pageSize * 
from student 
where sNo>=
(select max(sNo) 
from (select top ((pageIndex-1)*pageSize+1) sNo
from student 
order by  sNo asc) temp_max_ids) 
order by sNo;

• row_number关键字(SQL server 2005版本以上)

select top pageSize * 
from (select row_number() 
over(order by sno asc) as rownumber,* 
from student) temp_row
where rownumber>((pageIndex-1)*pageSize);

• offset /fetch next(2012版本及以上才有)

select * from student
order by sno 
offset ((@pageIndex-1)*@pageSize) rows
fetch next @pageSize rows only;

2. 存储过程和函数

• 存储过程

存储过程是由SQL语句和控制语句构成的语句串（语句集合）。它不仅可以带有输入参数还可以带有输出参数，存储过程能够通过接收参数，向调用者返回结果集，结果集的格式由调用者确定。返回状态值给调用者，指明调用成功还是失败，包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。 另外存储过程好处不必多说，比如支持sql重用、执行速度快、减少网络流量、安全可靠。

create procedure procedure_name [;version number]
[{ @parameter data_type}
       [varying] [=default value][output]
] [,...n]
[with
        {recompile|encryption|recompile,encryption}]
  [for replication]
 as sql_statement [...n]

调用存储过程

declare @avgage int ,@maxage int
execute usp_select_teacher ' 计算机', @avgage output, @maxage output

• 函数

当要执行一段语句，产生单一值或表时，可使用自定义函数而不是存储过程。与存储过程不同，自定义函数只能返回单一值或表。此外，函数不能执行insert、update和delete操作。
语法

create function year_wage(@work_date datetime, @now_date datetime,@per_wage decimal)
returns decimal
as
begin
return (( year(@now_date )-year( @work_date)) * @per_wage)
end

调用

3. 数据库并发

4. 触发器

5. JOB计划任务
   创建步骤：https://www.cnblogs.com/wkslearner/p/5970925.html

性能优化

1. 索引
   SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间。
   建立索引的原则：

   • 定义主键的数据列一定要建立索引。
   • 定义有外键的数据列一定要建立索引。
   • 对于经常查询的数据列最好建立索引。
   • 对于需要在指定范围内的快速或频繁查询的数据列;
   • 经常用在WHERE子句中的数据列。
   • 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
   • 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
   • 对于定义为text、image和bit的数据类型的列不要建立索引。
   • 对于经常存取的列避免建立索引
   • 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
   • 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

2. 表的设计
   数据库设计的实用原则是在数据冗余和处理速度之间找到合适的平衡点

• 表的设计满足需满足前三范式:

  • 第一范式(1NF)：属性(字段)的原子性约束，要求属性具有原子性，不可再分割
  • 第二范式(2NF)：记录的惟一性约束，要求记录有惟一标识，每条记录需要有一个属性来做为实体的唯一标识
  • 第三范式(3NF)：属性(字段)冗余性的约束，即任何字段不能由其他字段派生出来，在通俗点就是：主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们，当然外键除外)

• 主键和外键的必要性

• 三少原则:①:数据库的表越少越好 ②:表的字段越少越好 ③:字段中的组合主键、组合索引越少越好

3. 字段设计原则

   • 数据类型尽量用数字型，数字型的比较比字符型的快很多。
   • 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。
   • 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。
   • 少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。
   • 自增字段要慎用，不利于数据迁移

4. 操作符优化

   • IN和NOT IN
     IN和EXISTS 性能有外表和内表区分的，但是在大数据量的表中推荐用EXISTS 代替IN。NOT IN 不走索引的是绝对不能用的，可以用NOT EXISTS 代替。

   • IS NULL和IS NOT NULL
     索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。

   • <> （不等于)
     不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 用其它相同功能的操作运算代替，如 a<>0 改为 a>0 or a<0

   • 用全文搜索搜索文本数据，取代like搜索
     用contains实现全文搜索，方法:https://www.cnblogs.com/ljhdo/p/5041605.html

5. SQL语句优化

   • 减少访问数据库的次数:程序设计中最好将一些常用的全局变量表放在内存中或者用其他的方式减少数据库的访问次数,如不要将读取数据库的操作放到循环中。
   • 在查询中尽量不要使用 select *
   • 尽量写WHERE子句
   • 对于聚合查询，可以用HAVING子句进一步限定返回的行,group by having
   • 在where子句中应把最具限制性的条件放在最前面。where子句中字段的顺序应和索引中字段顺序一致。

安全性

SQL注入

发生于应用程序与数据库层的安全漏洞。即在输入的字符串之中 注入SQL指令，在设计不良的程序当中忽略了字符检查，那么这些注入进去的恶意指令就会被数据库服务器误认为是正常的SQL指令而运行，因此遭到破坏或是入侵。
例如：

strSQL = "SELECT * FROM users WHERE (name = '" + userName + "') and (pw = '"+ passWord +"');"
恶意注入:userName = "1' OR '1'='1"  或者 passWord = "1' OR '1'='1";
实际运行:
strSQL = "SELECT * FROM users;"

避免方法:

• 在设计应用程序时，完全使用参数化查询（Parameterized Query）来设计数据访问功能。

SELECT * FROM myTable WHERE myID = @myID

ADO.NET写法:

        SqlCommand sqlcmd = new SqlCommand("INSERT INTO myTable (c1, c2, c3, c4) VALUES (@c1,)", sqlconn);
         sqlcmd.Parameters.AddWithValue("@c1", 1); // 设定参数@c1的值。
         sqlconn.Open();
        sqlcmd.ExecuteNonQuery();
        sqlconn.Close();

• 在组合SQL字符串时，先针对所传入的参数加入其他字符（将单引号字符前加上转义字符）
• 使用其他更安全的方式连接SQL数据库。例如已修正过SQL注入问题的数据库连接组件，例如ASP.NET的SqlDataSource对象或是 LINQ to SQL。
• 使用SQL防注入系统