SQL Server知识点归纳

一、认识、入门

SQL是一门高级查询语言

SQL Server是微软开发的一个开发工具

1.使用前必须先打开两个TCP/IP，然后再打开SQL Server(MSSQLSERVER)

2.数据库名称：1、. 2、127.0.0.1 3、localhost 都是代表主机的意思

3.四个系统数据库：master（核心数据库）、model（模型）、msdb（警告日志数据库）、tempdb（临时数据库）

4.数据库文件分为：.mdf/.ndf为数据库文件 和.ldf 为日志文件

5.数据库右键任务中的：（附加与分离）、（脱机与联机）、（导入数据与导出数据）就不详细讲解了，可以去看一下其他教程

6.如何创建数据库和删除数据库：两种方式（鼠标和代码）

鼠标即右键创建/删除即可，代码：create（创建）/drop（删除） database（数据库的意思） 数据库名

7.三个俗语：（1）精度：除小数点以外的数字个数 例如（123.456-->6）                                                             （2）小数位数：小数点后面的数字个数 例如（123.456-->3）                                                         （3）长度：字节长度 1个汉字=2个字节 例如（IKun打篮球--->10）

8.sql常用数据类型：int（整形）

小数（float、money）

字符串（varchar若超过8000个字节用text，nvarchar括号中的长度为字符长度，char则代表不管你输入什么，里面都是有这么长的长度，比如char(10)，你输入一个1，那里面也是有10字节长度，nchar则是代表字符长度，varchar和char括号中的都是字节长度，当然括号里面也可以填max，max代表8000个字节）

日期时间（datetime）

布尔（bit）

图像（image）

二、数据库的相关操作

1.两种方式创建/删除表

鼠标：即右键创建或删除

代码：create（创建）/drop（删除） table 表名

2.六大约束

（1）主键：pk(primary key)

设置主键后，该列同时拥有唯一索引和不为空约束，当然一个表只能有一个主键，但是这个主键可以由多个列组成，那么这个主键我们称为联合主键，语法：primary key(列名，列名)；若是鼠标创建即ctrl+鼠标左击想设置为主键的列然后再右键即可，删除也是如此

添加主键：alter table 表名 add constraint pk_(主键约束名) primary key(列名)

删除主键：alter table 表名 drop constraint 主键约束名（增加时用的是什么名，这里就用什么名）

（2）空值约束：鼠标右击表的设计，找到对应列名的不为null的勾上或取消

代码：建表的同时在列名 数据类型后面写上null/not null null可缺省

如果表已经创建好了，那么要用代码添加不为空约束的语法：

alter table 表名 alter column 列名 数据类型 not null

（3）默认值约束：设计中点击指定的列，下面有个默认值绑定，在对应的空填上你想要的默认设置即可无需单引号；代码：在建表的同时，在列名 数据类型后面写上default 关键字 后写你想要的默认值即可

如果表已经创建好了，那么要用代码添加默认值约束的语法：

alter table 表名 add constraint de_约束名 default '默认值' for 列名

删除默认值约束的语法：alter table 表名 drop constraint 约束名（创建时用的是什么，这里就填什么）

不管哪种方式，默认值都要与该列的数据类型匹配

 （4）外键约束：（Foreign key）

鼠标右键列名，关系中创建外键约束即可

注：一定是从表的外键对应主表的主键，另外两个列名的数据类型和长度都要一致

创建语法：1.可以直接在创建表的同时写上

 2.如果表已经存在，那么创建外键的语法：

alter table 从表名 add constraint fk_（外键约束名） Foreign key(从表列名) references 主表(主表主键)

删除语法：alter table 从表名 drop constraint 外键约束名（创建时写的是什么，这里就是什么）

（5）检查约束（check）

1.用于限制列中的值范围

2.如果对单个列做check约束，那么该列只可以输入特定数值

3.如果一个表定义check约束，那么此约束会在特定的列对值进行限制

4.鼠标：右击-->CHECK约束-->添加需要的条件语句 约束名可以用（sp_help 表名）查看

5.创建语法：

建表时：列名 数据类型 check（条件语句）

已有表时：alter table 表名 add constraint 约束名 check（条件）

删除：alter table 表名 drop constraint 约束名

（6）唯一约束（unique）

作用：约束值不能重复

主键中包括了唯一索引，但unique可以有多个，主键只能有一个

鼠标：右击-->索引/键-->选择唯一键

创建语法：

建表时：列名 数据类型 unique

已有表时：alter table 表名 add constraint 约束名 unique(列名)

删除：alter table 表名 drop constraint 约束名

三、增删改查（查为重点）

1.增加（insert into）

顾名思义就是向表中增加数据

单行插入时：

insert into 表名(列名)              或    insert    into 表名（列名）                                                            values(数据)                                   select 数据

多行插入时：

insert into 表名(列名)              或    insert    into 表名（列名）                                                            values(数据1),                                   select 数据1        union                                                            (数据2)                                             select 数据2

注：列名和数据要保持三个一致：顺序、个数、数据类型，如果你插入的是所有的列名，那么列名可以省略不写，按照建表时的顺序插入数据；如果有主外键关系，必须先插入主表数据，再插入从表数据

2.删除（truncate、delete）

delete 【from】表名 where 条件

truncate table 表名 （因为truncate是删除所有数据，所以后面不能接where）

当然delete也可以不接where字句来删除所有数据，但是那么效率很低，因为delete是一行一行地删

注意：如果有主外键关系，必须先删除从表数据，才能删除主表数据

3.改（update）

这里的改是指修改表中数据，语法为：update 表名 set 要改的表达式 where 条件 （通过条件找到对应的一行）；一定要注意修改是否违反了六大约束

4.查（select）

这里的查是指查询表中数据，语法为：select 列名 from 表名 

（1）top：指前几 例如 top 3* ，那么就是取前三的所有数据； percent：百分比 例如top 50 percent ，那么就是取这个表的一半数据

（2）distinct ：去除查询结果中重复的行

（3）order by 列名 asc/desc：根据指定的列名排序，asc（缺省，升序），desc（降序），若要根据班级先升序，再根据学号再降序，则为order by 班级列名 asc,学号列名 desc

（4）is (not) null:(不)是空 在查询列名为空的数据时，不能直接列名=null 而是列名 is null

（5）(not) in:（不）在...里 比如一个列名要等于多个值时，我们通常不用 列名=值1 or 列名=值2；而是用列名 in(值1,值2)

（6）(not)between...and:（不）在...和...之间，一般表示区间时，通常不用 列名>=值1 and 列名<=值2，表示在这两个值的范围内，我们一般用列名 between 值1 and 值2，当然上面说到in，若in里面的值是(12,13,14)，这种情况我们会优先考虑用between 12 and 14

（7）like '%_':%代表任意字符，_代表单个字符，比如查询姓李的学生，那么就是学生姓名 like '李%'；如果是查询名叫李子，姓可以是任意姓氏，那么就是 学生姓名 like '_李子'

（8）isnull(a,b)：系统自带函数，a若为null，则查询结果用b代替，要考虑数据类型，看是否需要将a转换成b对应的数据类型，还是将b转换成a对应的数据类型，视情况而定

四、内置函数（下标都是从1开始，一般配合select 使用）

1.字符串函数：

（1）len：字符串长度

（2）substring：三个参数，第一个是要截取的大字符串，第二个是指定下标开始截取，第三个是截取长度为几，返回一个截取得到的子字符串，例如substring（'abcd',2,3）-->bcd

（3）replace：三个参数，第一个是大字符串，第二个是大字符串中要被替换的子字符串，第三个是替换后的子字符串，例如replace('abcd','a','b')-->bbcd

（4）left/right:取左边/右边多少位的字符，例如left('abc',1)-->a,right('abc',1)-->c

（5）charindex：两个参数，第一个是要查找的子字符串，第二个是被查找的大字符串，返回子字符串第一次在大字符串出现的下标数，例如charindex（'a','abc'）-->1

（6）ltrim/rtrim：去除左/右边的空格，这个单纯用看不出效果，可以配合len嵌套查看字符串长度就可以看出效果了

（7）upper/lower：将英文字符串变得全大写/小写 例如：upper('abc')-->ABC lower('ABC'-->abc)

2.日期函数：指定年用YY，月用MM，日用DD

（1）getdate()：获取系统时间，和sysdatetime()差不多

（2）year/month/day：获取对应日期的年/月/日

（3）datepart(YY/MM/DD,日期)YY代表年，MM代表月，DD代表日

（4）datename:其他功能和datepart差不多，但是有一个datename(WEEKDAY,日期)可以求出该日期的星期数

（5）datediff：三个参数，可以求两个日期之差，第一个参数可以指定求年差/月差/日差，后面两个参数就是你要求的两个日期

（6）dateadd:三个参数，可以增加也可以减少（减少就填负数即可），也可以指定要增加年/月/日，第二个参数就是增加（减少）多少，第三个参数就是被增加（减少）的指定日期

3.数学函数：

（1）abs：返回一个数的绝对值

（2）sqrt：返回一个数的平方根

（3）power：返回一个数的多少次方，两个参数，例如power(1,2)-->1的2次方

（4）ceiling/floor：向上取整/向下取整，例如ceiling(12.3)-->13,floor(12.3)-->12

（5）sign：返回值只有三个“1”，“0”，“-1”，若传进去的参数为负数，得到的则为-1；正数则得到1；0则得到0

（6）round：四舍五入，两个或三个参数，一般用两个 round（数字，要保留几位小数）

4.系统函数：

（1）datalength：返回这个字符串的字节长度

（2）cast（列名 as 数据类型）/convert（数据类型，列名）：转换函数，convert有两到三个参数，第三个参数只有在转换时间类型的时候才能用到，可以去查阅其他资料获得相关信息，一般我们用cast用的最多

五、高级查询

1.统计查询：（用这些聚合函数时，会自动忽略为null的列，要么单独用，要么跟group by一起用，where 做分组前的筛选，having做分组后的筛选）

（1）max：获得该列最大值

（2）min：获得该列最小值

（3）sum：获得该列总和

（4）avg：获得该列平均值

（5）count：获得该列有多少行

2.联表查询：

（1）内连接：inner join

表1 as  a inner join 表2 as b on a表与b表之间的关系

（2）左外连接：left join

表1 as  a left join 表2 as b on a表与b表之间的关系

（3）右外连接：right join

表1 as  a right join  表2 as b on a表与b表之间的关系

（4）全外连接：full join

表1 as  a full join  表2 as b on a表与b表之间的关系

3.子查询：

将一个select语句查询的结果当做另一个select语句的条件使用

(not)in查询使用在返回的结果有多个时

若结果只有一个，可以使用关系运算符

any：至少；all：全部

六、视图（view）

一张虚拟的表，具有安全性，不暴露列名；方便，不需要再写多次select语句

直接将查询结果当做一张新的表给使用者查看

创建语法：create view 视图名称

                as

                select语句

查看语法：select * from 视图名称

删除语法：drop view 视图名称

视图和表的区别：

1.视图是已经编译好的sql语句，是基于SQL语句的结果集的可视化表，而表不是

2.视图（除过索引视图）没有实际的物理记录，而基本表有

3.表是内容，视图是窗口

4.表占物理空间，而视图只是逻辑概念的存在

5.视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全角度说，视图可以防止用户接触数据表，从而不知表结构

6.表属于全局模式的表，是实表；视图数据局部模式的表，是虚表。

7.视图的建立和删除只影响视图本身，不影响对应的基本表

8.视图不能包含order by子句，除非select语句的选择列表中还有个top子句

9.视图不能使用into关键字，视图不能包含option子句

10.视图不能对临时表或表变量的引用。视图最多可以有1024列

七、补充知识点：sp_rename 旧名，新名

这个语法可以更改表和视图等等的名字，但是不能修改数据库的名字

八、当然还有一个存储过程，存储过程和视图差不多，在这里我提一嘴

创建存储过程：create proc或procedure 存储过程名

                        as

                          select语句

   使用存储过程：exec 存储过程名

删除存储过程：drop proc或procedure 存储过程名

当然存储过程可以带有参数，上面只是无参存储过程，参数也分两种：输入参数和输出参数

我们称为input参数和output参数

输入参数：用于向存储过程传入值，类似java语言或c中的值传递

输出参数：用于调用存储过程后，参会结果，类似java的按引用传递

带一个输入参数的存储过程创建：（要求：列名与输入参数相等）

        create proc 存储过程名（@参数名 数据类型）

        as

        select 列名 from 表名 where 列名=@参数名 

带两个输入参数的存储过程：

        create proc 存储过程名（

        @参数名 数据类型，

        @参数名 数据类型

        ）

        as

        select 列名 from 表名 where 列名=@参数名 and 列名 = @参数名

带output参数的存储过程：

        创建带output参数的存储过程：

        create procedure 存储过程名（

        @参数名 数据类型，

        @参数名 数据类型 output

        ）

        as

        select output参数名=列名 from 表名 where 列名=输入参数名

        执行该存储过程，注意：要3行代码一起执行

        declare @参数名 数据类型

        exec 存储过程名 output参数的值（要与数据类型一致），@参数名（declare后面的参数名是什么，这就是什么）

        select @参数名（declare后面的参数名是什么，这就是什么） as 别名

当然你使用了存储过程时肯定会觉得和视图差不多，但其实差距非常大，可以去网上搜一搜它们的区别，不是我一两句话能写的完的

附：@代表局部变量，@@代表全局变量

九、索引（index）其实这个知识点并不重要，因为要几百条、几千条、甚至几万条数据才能看得出效果，感兴趣的可以了解一下

存在目的：为了提高查询效率

假设要查询姓张的学生

未建立索引时，查询指定的字段从a开始一直b、c、d.....到z 张

建立索引时，直接指定到z的序列去找

创建索引语法：create index 索引名称 on 表名（字段） 

删除索引：drop index 索引名称 on 表名