Day1 初识数据库

1、什么是数据库

从字面上理解，数据库可以定义为存储数据的库；而从广义上说，数据库是一个长期存储在计算机内、有组织的、可共享的、统一管理的大量数据的集合。

数据库可分为关系型数据库、非关系型数据库；而SQL则是关系型数据库，并且具有数据定义、数据操纵和数据控制功能，是关系代数和关系演算的结合，也是关系型数据库的标准语言。因此，学好SQL可以为我们学习其他关系型数据库打好基础。

2、为什么需要数据库

1、几乎所有应用软件的后台都要用到数据库；

2、数据库存储数据占用空间小，容易持久保存；

3、存储比较安全；

4、容易维护和升级；

5、数据库可移植性较好；

6、简化了对数据的操作，便于用户使用等。

关于数据库的优点还有很多，总而言之，学好数据库十分重要。

Day2 SQL连接服务器

1、连接服务器

在连接之前一定要把SQLserver的服务打开，SQL的连接方式有两种：Windows身份验证和SQLserver身份验证；Windows身份验证需要登录电脑，就可以直接进行连接；而SQLserver身份验证则需要输入用户名和密码，适用于非本地访问数据库。

2、连接方式问题

在安装好的SQL中可能默认的验证方式只有Windows验证，如果想启用SQL验证方式，需要进行设置：首先用Windows验证方式连接服务器；然后右键服务器，找到属性，点击进入，再进入安全性，将设置更改为SQL server和Windows身份验证模式，并且保存更改即可。

3、SQL验证问题

如果忘记了SQLserver验证方式的密码或者想修改SQLserver验证方式的密码，则可以先用Windows验证方式连接，然后进行如图操作，就可以修改密码，修改需要重启SQL服务才能生效。（ps：不管你的密码是多少位，查看时显示都是15位，因为会自动产生一些乱码，所以不必惊慌）

如果修改密码并重启服务后仍然密码错误，那么可能是没有设置状态：如下图，将状态设置为授予连接到数据库、启用登录即可。

4、系统数据库

系统数据库是用来维护用户所建立的数据库，最核心的库是master库，因此不要对系统数据库作出任何修改。

Day3 界面操作-数据库、表

1、数据库

1.1、创建数据库

步骤如图：数据库文件包括后缀名为.mdf和后缀名为.ldf的两个文件，.ldf文件为日志文件。

1.2、数据库的删除

1.3、数据库的分离和附加

如果想对数据库进行移动，则要用到分离和附加操作，因为SQL是服务型数据库，当SQL处于服务使用时，不能对其内部的数据库进行移动。

分离：分离时要删除连接，并且更新系统。

附加：

1.4、生成脚本（架构+数据）

如果不想移动数据库文件，可以生成脚本，脚本文件后缀名是.sql，可以用记事本打开，用于存放操作数据库的代码，在其他电脑上直接运行某数据库的脚本代码即可得到与原数据库一模一样的数据库，就可以不用移动原数据库文件了。（在生成脚本时要在高级选项中将要编写脚本的数据的类型设置为架构与数据（见下图），脚本仅架构则仅生成创建表的代码，架构和数据则生成创建表和添加数据的代码）

2、表

2.1、表的新建

2.2、唯一标识

每个表都包含唯一标识，用来保证表中数据的唯一性，为了便于维护标识，我们将标识交给SQL进行维护；我们以将UserId作为标识为例，如下图设置标识（数据类型是int时才可以设置为标识），默认增量标识增量为1，起始值标识种子为1，在添加数据到该表中时，SQL自动维护标识的值。

2.3、主键

一般将唯一标识设置为主键。因为标识唯一，因此如果值重复将会报错；并且可以提高检索速度，因为键也属于索引，但是不强制设置。

2.4、第一个表

输入好数据，保存并输入表名，一个表就创建好了。

3、常用字段类型

3.1、字符串类型

SQL中没有string类型，SQL的字符串类型有：char/varchar/nvarchar；

是否含n的区别：

char和varchar表示采用非unicode编码，即如果是英文或阿拉比数字则占一个字节，如果是中文则占两个字节；nvarchar表示采用unicode编码，即无论是英文还是中文都占一个字节；一般来说数据中含中文就使用nvarchar。

是否含var的区别：

char表示长度固定，不可变，如果数据长度不够，会在末尾补空格；varchar和nvarchar则长度可变，即使数据长度不够，也不会补空格。

3.2、常用类型

浮点数类型decimal(a,b)：a用来设置小数的位数（不包括小数点），b用来设置精确度；由于decimal比较灵活，因此使用较多。

时间类型datetime：用来存放日期和时间，也可以分开存放日期、时间。

位类型bit：只能存放0和1，比如可以使用bit来表示男女性别存放到数据库。

Day4 数据库的约束

数据库的一个重要特点就是能够保证数据的有效性，而约束就是用来实现数据的有效性检查。

1、主键

主键的值用于唯一的标识表中的某一条记录；在两个表的关系中，主键用来在一个表中引用来自于另一个表中的特定记录；并且主键不能为空。

2、非空

非空即不允许值为空。

3、唯一

唯一即要求存入的数据不能重复；如果在设置了唯一键的那一列添加重复数据将会报错。

设置唯一键：

4、默认

默认即设置一个默认值，如果设置了默认值的那一列没有数据填入，将自动填入设置好的默认值。

5、检查

检查用来检查插入的数据是否满足要求。

设置检查步骤：约束表达式中的返回值是布尔类型，true则满足约束，可以插入，反之，不可以插入。（ps：year(regDate)用来取regDate中的年份）

6、外键

外键表示两个关系之间的联系，用来检查关系的有效性。

设置外键：

外键表即被决定的那个表，主键表则是起决定作用的那个表。

Day 5 脚本创建数据库（SQL语句）

1、前言

DDL：数据定义语言，用于进行各种数据库对象的创建，主要操作包括create、alter、drop；

DML：数据管理语言，用于对表数据的添加、修改、删除、查询，主要操作包括insert、update、delete、select；

DCL：数据控制语言，用于进行权限分配等。

注释：--单行注释、/**/多行注释。

2、创建数据库

Day6 SQL基本常用命令

1、创建表

示例:在数据库dbtest中创建表StudentInfo，主键primary key为sId，标识identity为sId，外键foreign key为cid。

2、数据插入insert

单一列插入、某几列插入、全部列插入、一次性插入多行：

ps：
into可以省略不写；
插入的列名数与数据数要一致。
示例多行插入：在表ClassInfo中插入四行数据

3、数据修改update

修改所有行的列数据、修改满足条件的行的列数据：

4、数据删除delete

ps：from可以省略不写。
示例标识的不连续：删除表ClassInfo中cId=4的那一行，再插入的一行标识cId为5

5、清空truncate

清空即重置表。

6、数据查询

6.1、查询列select（列筛选）

6.2、查询前n部分的数据top（行筛选）

6.3、排序order by

排序方法：
asc：从小到大
desc：从大到小

示例：查询表StudentInfo所有列的数据，结果按照cid从大到小排列；如果值重复，则按照sId从小到大排列

结果：

6.4、消除重复行distinct

示例：表StudentInfo中的数据为：

查询StudentInfo的列cid，并且去除重复行：

结果：

6.5、条件查询where（行筛选）

查询结果满足where后面的条件表达式，即表达式要返回true。

比较运算符：=、>、>=、<、<=、!=或<>；
逻辑运算符：and、or、not；
在一个连续的范围内：between...and...（闭区间）；
在一个非连续的范围内：in。
连续范围与不连续范围示例：

6.6、模糊查询like

模糊查询：不知道具体值时的查询，用于处理字符串类型的值。

示例1：在表StudentInfo中查询姓杨的同学

示例2：在表StudentInfo中查询姓杨的，并且名字两个字的同学

示例3：在表StudentInfo中查询sPhone是连续范围[1-3]中的数据开头的所有列

示例4：在表StudentInfo中查询sPhone是非连续范围[576]中的数据开头的所有列

示例5：在表StudentInfo中查询sPhone不以连续范围[1-3]中的数据开头的所有列

7、补充null、优先级

null的判断：使用is null、is not null（select 1+null结果为null，因为null表示不知道，而不是没有）
函数ISNULL(列名,字段）
示例：

优先级：小括号>not>比较运算符>逻辑运算符

Day7 SQL常用命令续集

1、连接查询join...on

连接查询适用于当需要的结果来自多张表时。
内连接：inner join，查询两表中完全匹配的数据；
左外连接：left outer join，查询两表中完全匹配的数据，以及左表特有的数据；
右外连接：right outer join，查询两表中完全匹配的数据，以及右表特有的数据；
完全外连接：full outer join，查询两表中完全匹配的数据，以及左表特有的数据，还有右表特有的数据；

示例：

2、聚合函数

聚合函数用于对行数据进行合并，一般是对数字类型的列进行操作，一条查询中可以写多个聚合函数（null不参与运算）。
常用聚合函数：sum（求和）、avg（求平均值）、count（计数）、max（求最大值）、min（求最小值）。

sum示例：计算表StudentInfo中列cid的和，并取别名为sum

avg示例：计算表StudentInfo中列cid的平均值

count示例：计算StudentInfo表中一共多少行，如果存在某行的值全为null，则不计数

max示例：查询表StudentInfo的列cid的最大值

min示例：查询表StudentInfo的列cid的最小值

3、开窗函数over()

开窗函数和聚合函数结合使用，用于将聚合结果还原至原数据，便于将聚合结果与原数据进行对比。
开窗函数还可以和排名函数ROW_NUMBER()结合使用。

结合聚合函数示例：计算表StudentInfo的列cid的平均值，并利用开窗函数与原cid进行对比

结果：

结合排名函数示例：将表StudentInfo中sGender=1的数据进行排序，并且进行排号

结果：

4、分组group by

聚合函数一般结合分组使用，进行分组内的数据统计；分组依据值相同的示例在一组，在结果列中只能出现分组依据列和聚合列。
ps：group by与where共存时，group by写where后面。

单一列依据分组示例：将表studentInfo按照sGender进行分组，并计算每组数量

结果：

多列分组示例：将表StudentInfo按照sGender和cid进行分组，并计算每组数量

结果：

5、分组结果筛选having

示例：将表StudentInfo按照cid进行分组，并且计算每组数量，筛选出数量大于1的组

6、联合查询union

联合查询：将多个查询的结果集合并成一个结果集。
联合要求：
结果集列数一致；
对应列的类型一致。

关键字：
union：将多个结果集的数据进行合并，并且消除重复行，按照第一列从小到大排序；
union all：将多个结果集进行合并，但不消除重复行，也不排序；
except：差集，A except B表示A结果集中的数据但不包括B结果集中的数据；
intersect：交集，结果集中都有的数据。

union示例：

结果：

union all示例：

结果：

except示例：

结果：

intersect示例：

结果：

7、快速备份

向未有表备份：select 列名 into 备份表名 from 源表名。
（备份表如果不存在将新建表，表的结构完全一致，但是不包含约束，如果想只包含结构不包含数据，可以加个top 0）
示例：将表ClassInfo的结构和数据快速备份到表test1，将自动新建表test1

向已有表备份：insert into 备份表名 select 列名 from 源表名。
示例：向已有表test2中备份表ClassInfo的列cTitle

8、内置函数

8.1、类型转换函数

cast(expression as date_type)：将任意类型转到任意类型
convert(date_type ,expression[,style])：将任意类型转到任意类型，如果目标类型是字符串，则style可以设置样式
示例：将89.0000转换成89.0

8.2、字符串函数

ascii：求字符的ascii码值；
char：根据ascii码转到字符；
left：自左开始往右截取字符串；
right：自右开始往左截取字符串；
substring：从任意位置开始截取字符串，函数参数：字符串、开始索引、截取数量（索引从1开始）；
len：返回字符串的长度；
lower：转小写；
upper：转大写；
ltrim：去除左侧空格；
rtrim：去除右侧空格；

示例：

8.3、日期函数

getDate：获取当前日期时间；
dateAdd：日期加；
dateDiff：日期差；
datePart：取日期的某部分
year：取年；
month：取月；
day：取日。
（返回值都是int类型）

示例：

Day8 SQL语句尾篇

1、视图

视图：将一个复杂的select语句进行封装，以更方便使用，实现了代码复用。
视图中存储的是select语句，而不是结果集数据；视图可以进行增、删、改，但是尽量不要对视图进行这些操作，视图主要进行查询操作。
语法：create/alter view 名称 as select ...

示例：创建视图，名称为StudentInfo_ClassInfo；使用、删除视图

2、子查询

将一个查询语句嵌入另一个查询语句中，称这种查询为子查询。
常用运算符：= in exists
in与exists的差别：数据比较大时，exists效率比in高。
示例：查询表ClassInfo中的列cId与表StudentInfo中cid匹配的数据

3、分页

已知：页大小、页索引
起始索引：(pageIndex-1) × pageSize+1
结束索引：pageIndex × pageSize
示例：已知页大小：4，页索引：2，则起始页：5，结束页：8

4、数据透视case

数据透视完成数据的行列转换。
示例：将视图StudentView中列cid对应的男、女列出来，如果值为null，则列出无，并且按照cid从小到大排序

结果：

5、Transact-SQL编程

t-sql编程是只适用于SQL server的语句，即SQL特有语句。

5.1、变量

声明：declare 变量名 类型（变量名要求以@开头）
设置：set/select 变量名=值
输出：print/select 变量名
示例：

内置全局变量：使用双@符号
@@version：数据库版本
@@identity：进行插入后调用，返回最近的insert语句的标识值
@@servername：服务器名称
@@error：返回执行的上一个Transact-SQL语句的错误号，如果没有返回0
@@rowcount：返回受上一句语句影响的行数

5.2、选择语句if

语法：
if 条件
begin
--满足条件执行
end
else begin
--不满足条件执行
end
示例：输出OK

5.3、循环语句while

语法：
while 逻辑表达式
begin
循环体
end
示例：

5.4、异常处理语句

语法：
begin try
--可能出错的语句
end
begin catch
--捕获后执行的语句
end catch
示例：

5.5、事务

事物保证了数据的准确性、有效性和可恢复性；当一个事件有多种操作，而其中一个操作出现了问题，那么事务可以让事件回到做之前的状态，保证了事件所有操作正确完成。
只有数据改变（增加、修改、删除）时才会引发事务，查询不会引发事务。

分类：
显式事务（需要手动控制）
隐式事务（不需要手动控制，默认采用此方式）
显式事务语法：
begin transaction --开始事务
commit transaction --提交，没错后执行
rollback transaction --回滚，出错后执行
示例：因为操作2出错，因此回滚事务，所以回到最开始，操作1也不会执行

锁模型

锁：当对数据进行修改操作时，将自动添加锁，防止多个人员同时操作数据产生数据错误。