SQl server

第一章 数据库基础

1.1、 SQL Server软件的安装

(1)安装SQLServer前的准备工作：

扩展名为.iso的安装包文件，推荐使用虚拟光驱软件Daemon Tools进行安装（注意：安装Daemon

Tools时选择“免费许可”）。

(2)安装SQLServer时的注意事项：

功能选择“最小化安装”：数据库引擎服务（3个子项）、客户端连接工具、管理工具-基本（1个子项）。

1.2 、SQL Server软件的卸载

如何完全卸载SqlServer2012，参见以下网址：

https://jingyan.baidu.com/article/e4d08ffffd69b6330fd2f60d90.html

1.3、 数据库的分类

一般来说，数据库按照组织式分为两大类：关系型数据库和非关系型数据库。

当前主流的关系型数据库有Oracle、DB2、Microsoft SQL Server、MySQL、Microsoft Access等。

SqlServer：由Microsoft开发。

Oracle：由Oracle（甲骨文）开发 ，于1983年推出的世界上第一个开放式商品化关系型数据库管理系

统。

DB2：是美国IBM公司开发的一套关系型数据库管理系统，它主要应用于大型应用系统 。

MySQL：

由瑞典 MySQL AB 公司开发,目前属于 Oracle 旗下公司，一般用于中小型网站 的开发。

参见：

（1）https://www.php.cn/sql/421899.html

（2）https://www.zhihu.com/question/432781043/answer/1604375728

第二章 企业管理器

2.1 、数据库文件

数据库文件的扩展名：

主数据库文件： .mdf（ 是 primary data fifile 的缩写 ）

次数据库文件： .ndf（是Secondary data fifiles的缩写 ）

事务日志文件： .ldf（ 是Log data fifiles的缩写）

​ 主要数据文件包含数据库的启动信息，并指向数据库中的其他文件，所有表、视图、存储和数据都

​ 在这个文件。用户数据和对象可存储在此文件中，也可以存储在次要数据文件中。每个数据库有一个主 要数据文件。

​ 次要数据文件是可选的，由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上。另外，如果数据库超过了 小，可以使用次要数据文件，这 样数据库就能继续增长。

事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。

2.2 、数据库的常用操作

1、脱机：

对数据库名右击-->任务-->脱机

2、联机：

它是脱机的反操作。

2、分离：

对数据库名右击——>任务——>分离，注意勾选”删除连接“。

附加：他是分离的反操作，对“数据库”右击——>附加。

3、删除数据库：

对数据库名右击-->删除

2.3、 SQL Server的数据类型

SqlServer常用数据类型参考：https://www.cnblogs.com/jojoword/p/10543763.html

2.3.1 、文本数据类型

char：固定长度的非unicode字符(一个汉字占两个字节)。

varchar：可变长度的非unicode字符(一个汉字占两个字节)。

nchar：固定长度的unicode字符(一个汉字占一个字节)。

nvarchar：可变长度的unicode字符(一个汉字占一个字节)。

示例：

char(6):可以存6个字母或数字，但只能存3个汉字。

nchar(6):可以存6个字母、数字或汉字。

长文本类型：

text：

存储可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符。

ntext：

存储可变长度的Unicode数据。

注意：

nchar、nvarchar、ntext这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型

的字符。我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字需要两个字节存储，英文与

汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所

有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000

之间。和 char、varchar比较起来，nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；

而char、varchar最多能存储 8000个英文，或4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。 所以一般来说，

如果含有中文字符，用nchar/nvarchar，如果纯英文和数字，用char/varchar。

2.3.2、 整数数据类型

(1).bigint（相当于C#中的long）

每个bigint存储在8个字节中，其中一个二进制位表示符号位，其它63个二进制位表示长度和大

小，可以表示-2的63次方~2的63次方-1范围内的所有整数。

(2).int（相当于C#中的int）

int或者integer,每个int存储在4个字节中,其中一个二进制位表示符号位，其它31个二进制位表示长度

和大小，可以表示-2的31次方~2的31次方-1范围内的所有整数。

(3).smallint（相当于C#中的short）

每个smallint类型的数据占用了两个字节的存储空间，其中一个二进制位表示整数值的正负号，其它

15个二进制位表示长度和大小，，可以表示-2的15次方~2的15次方-1范围内的所有整数。

(4).tinyint（相当于C#中的byte）

每个tinyint类型的数据占用了一个字节的存储空间，可以表示0~255范围内的所有整数.

2.3.3、 浮点数据类型

浮点数据类型存储十进制小数，用于表示浮点数值数据的大致数值数据类型。浮点数据为近似值；浮点

数值的数据则Sql Server中采用了只入不舍的方式进行存储，即当且仅当要舍入的数是一个非零数时，

对其保留数字部分的最低有效位上加1，并进行必要的近位。

(1).real：相当于C#中的flfloat类型。

可以存储正的或者负的十进制数值，它的存储范围从-3.40E+38~-1.18E-38、0以及1.18E-

38~3.40E+38.每个real类型的数据占用4个字节的存储空间。

(2)flfloat[(n)]：相当于C#中的double类型。

其中为用于存储flfloat数值尾数的位数（以科学计数法表示），因此可以确定精度和存储大小。如果

指定了n它必须是介于1和53之间的某个值。n的默认值为53.

其范围从-1.79E+308_{-2.23E-308、0以及2.23E+308}1.79E-308。如果不指定数据类型flfloat的长

度，它占用8个字节的存储空间。flfloat数据类型可以写成flfloat(n)的形式，n为指定flfloat数据的精度，n

为1_{53之间的整数值。当n取1}24时，实际上定义了一个real类型的数据，系统用4个自己存储它。当n

取25~53时，系统认为其是flfloat类型，用8个字节存储它。

(3).decimal[(p[,s])]和numeric[(p[,s])：相当于C#中的decimal类型。

带固定精度和小数位数的数值数据类型。使用最大精度时，有效值从-10^38+1~1038-1。

numeric在功能上等价于decimal。

p(精度)指定了最多可以存储十进制数字的总位数，包括小数点左边和右边的位数，该精度必须是从

1到最大精度38之间的值，默认精度为18.

s(小数位数)指定小数点右边可以存储的十进制数字的最大位数，小数位数必须是从0到p之间的值，

仅在指定精度后才可以指定小数的位数。默认小数位数是0；因此，0<=s<=p。最大存储大小基于精度

而变化。例如：decimal(10,5)表示共有10位数，其中整数5位，小数5位。

2.3.4、 日期时间类型

datetime：年月日时分秒.毫秒

smalldatetime：年月日时分秒

date：年月日2.3.5 位数据类型

bit： 称为位数据类型，只取0或1为值，长度1字节。bit值经常当作逻辑值用于判断true(1)或false(0),输

入非0值时系统将其替换为1。

第三章 查询分析器

select*from 表名--查询表中所有的信息 select*from 表名where条件表达式--带条件查询 select*（可以换成单独的表中某个表名列名）from 表名where条件表达式--带条件查询 

3.1 、建库

语法：create database 数据库名

建库案例：

创建数据库StuInfo2的代码如下所示。

use master   go  --若存在数据库StuInfo2，则删除该库   if exists(select * from sysdatabases where name='StuInfo2')   begin   drop database StuInfo2   end   go  --创建数据库   create database StuInfo2   on primary --主数据库文件   (   name='StuInfo2',   filename='E:\Database\SophomoreYearDB\2020级\6班\StuInfo2.mdf',   size=5MB,   maxsize=unlimited,   filegrowth=1MB   )  log on --日志文件   (   name='StuInfo2_log',   filename='E:\Database\SophomoreYearDB\2020级\6班\StuInfo2_log.ldf',   size=5MB,   maxsize=unlimited,   filegrowth=1MB   )  go 

3.2 、建表

语法：create table 表名

建表案例：

在上面的案例代码的基础上，继续编写如下代码：

--使用数据库StuInfo2来建表

use StuInfo2  create table StuBase   (   StuNo bigint not null,   StuName nvarchar(50) not null,   Gender bit not null,   IdCard char(18) not null,   Age int not null,   Remark nvarchar(200) null   )  go  create table Score   (  StuNo bigint not null,   CSharpScore float not null   )  go 

3.3 、约束

3.3.1 、SQL Server常见的7种约束

(1)主键约束：primary key（PK）

(2)唯一约束：unique（UQ），在表里可以存在多个唯一约束，但一个唯一约束里面不能存在同样的

值。

(3)检查约束：check（CK），自定义的约束。

(4)默认值约束：default（DF）

(5)外键约束：foreign key（FK）

(6)非空约束：not null

(7)标识列：identity ，系统自动填值进去，它是从哪个值开始，每次增加多少。 identity（1,1）前面表

示从1开始，后者表示每次增长1。

3.4、加五种键约束案例：

在上面的案例代码的基础上，继续编写如下代码：

3.4.1、添加主键约束

alter table StuBase add constraint PK_StuNo primary key(StuNo);  

3.4.2、添加默认约束

--alter table StuBase add constraint DF_Gender default(1) for Gender;   alter table StuBase add constraint DF_Gender default 1 for Gender;  

3.4.3、添加唯一约束(表设计界面对任一列右击，选择“索引/键”可查看添加的约束)

alter table StuBase add constraint UQ_IdCard unique(IdCard);  

3.4.4、添加检查约束

alter table StuBase add constraint CK_Age check(Age>=18);  

3.4.5、添加外键约束

alter table Score add constraint FK_StuNo foreign key(StuNo) references   StuBase(StuNo);  

3.5、删除约束

alter table 表名 drop constraint 约束名;  alter table StuBase drop constraint d_Gender; 

第四单元

4.1、 T-SQL中的运算符

4.1.1、 比较运算符

运算符 含义

= 等于

> 大于

< 小于

>= 大于或等于

<= 小于或等于

<> 不等于

! 非

4.1.2 、逻辑运算符

逻辑表达式 说明 示例

AND 逻辑与 1 AND 1 = ; 1 AND 0 = 0; 0 AND 0 = 0;

OR 逻辑或 1 OR 1 = 1; 1OR 0 = 1; 0 OR 0 = 0;

NOT 逻辑非 NOT 1 = 0; NOT 0 = 1;

4.2 、简单查询数据

4.2.1、查表中所有记录：

select * from 表名  

4.2.2、带条件的查询：

select*from 表名 where 条件表达式  

4.3、删除表

语法：drop table 表名 

4.4、插入数据

语法：insert  into 表名（列名列表）values（值列表） 

注意：在插入数据时，若表中有自增列，则该列的值由系统自动分配，不需要程序员给定值。

4.5、更新数据行的语法：

update 表名 set 列名=新值 where 列名=旧值 

4.6、删除数据行：

delete from 表名 where 行名  delete from team where name=2； 

--Truncate table执行的速度更快，使用的系统资源和事务日志更少，不能删除主键外键关系的主键表中的数据，若要删除主键表中的数据，需要用delete来完成。

Truncate table Team。

Truncate table 表名。 

Truncate table可以删除存在主外键关系的外键表中的所有数据。

Truncate table Player

4.7、一次性插入多条数据：

insert select.（插入的数据）.union  

4.8、一次性多条数据行：

将一张表中的数据拷贝到另一张新的数据表中，使用SELECT...INTO语句
--将Team表中的数据行复制到Team2中.

select*into Team2 from Team;  

4.9、查询学生的学号，姓名，性别的三种方法

--给查询的列取别名的三种方式

select '学号'=StuNo,Name '姓名',(case when Gender=1 then '男' else '女' end) as '性别' from Baselnfo ;  select  '学号'=StuNo,'姓名'=Name ,'性别'=(case when Gender=1 then '男' else '女' end)  from Baselnfo;  select StuNo '学号',Name '姓名',(case when Gender=1 then '男' else '女' end) '性别' from Baselnfo;  select  StuNo as '学号',Name as '姓名',(case when Gender=1 then '男' else '女' end) as '性别'  from Baselnfo;  select Name+'-'+Idcard as'姓名-身份证号码' from Baselnfo;  

第五单元

5.1、关系运算符：

<>、!=

5.1.1、逻辑运算符：

and、or、not

5.2、过滤表中已有信息

5.2.1、过滤重复行（针对单列过滤）

select distinct Gender from  Baselnfo;  

5.2.2、过滤重复行（针对多列过滤）

select distinct Gender,Name from  Baselnfo;  select top 3 *from Baselnfo  select top 3 *from Baselnfo where 列名  

5.3 、使用TOP关键字仅显示前面若干条记录

top 整数：前多少条记录   top 整数 percent：前百分之多少条记录  

5.4、使用order by查询表中的正序排名：

select top 2 * from StuScore where Subject='C#'  order by Score desc ;  

5.5、查询链接在一起显示

select cast(StuNo as varchar) +'-'+Name as'学号-姓名' from Baselnfo;  

5.6、查询除了这个之外所有的数据：

select*from  表名where 列名<> '列名中的数据'  select*from  表名where 列名!='列名中的数据'  

5.7、查询多少到少之间的数据：

select*from StuScore where Subject='C#' and Score >= 40 and Score<=90;  

5.5、对查询结果筛选列表运算符in

in：用于匹配值列表

not in

5.5.1、in用于跟多个具体的值做等值比较，满足任意一个即可

select*from Baselnfo where StuNo  in(20209130501);  

5.5.2、not in:跟不等于这多个具体值的数据进行匹配

select*from Baselnfo where StuNo not in(20209130501,20209130503,20209130504);  

5.6、模糊查询

语法：like结合通配符（%、_、[]、[^]）使用

--查询姓‘哈’的学生信息 select*from Baselnfo where Name like '哈%';  select*from 表名 where 列名 like '查询的名字';  --查询不姓‘哈’的学生信息 select*from Baselnfo where Name not like '哈%';  select*from 表名where 列名 not like '查询的 字';  --查询姓名只有两个字，且第二个字不为‘哈’的同学 select*from Baselnfo where Name like'_[^哈]';  select*from 表名where 列名 not like '查询的 字';——下划线代表占一个字节  

5.8、order by字句可以依据多个字段排序

--查询成绩信息，将C#成绩按降序排序，成绩相同的按学号升序排序

select  * from StuScore where Subject='SQlSever'  order by Score desc, StuNo asc;--降序 select  * from StuScore where Subject='SQlSever'  order by Score desc ;--降序  select  * from 表名 where 列名='列里面添加的数据'  order by 列名desc ;--降序  

5.9、聚合函数

SUM()：用于统计数值型字段的总和，它只能用于数值型字段，而且 NULL 值将被忽略。

AVG()：用于计算一个数值型字段的平均值，该字段中的NULL 值在计算过程中将被忽略。

MAX()：用于返回字段的最大值，在计算过程中遇到 NULL 值时予以忽略。

MIN()：用于返回字段的最小值，在计算过程中遇到 NULL 值时予以忽略。

COUNT()：统计记录行数。

5.9.1、求总分

select '总分'=SUM(Score) from StuScore;  select '总分'=SUM(列名) from 表名;  

5.9.2、求每门课程的总分（各，每）

--在一个查询语句中可以使用任意多个字段对结果字段内的记录进行分组，字段列表中的每个输出字段必须在GROUP BY字句中出现或者用在某个聚合函数中。

select Subject '科目',SUM(Score)'总分' from StuScore group by Subject;  select 列名'科目',SUM(列名)'总分' from 表名group by 列名;  

5.9.3、求各科平均分，并按降序排

select Subject '科目',AVG(Score)'平均分' from StuScore group by Subject order by AVG(Score) desc;  

5.9.4、求各科最高分数

select Subject '科目',MAX(Score)'最高分' from StuScore group by Subject;  

5.9.5、求各科最低分数

select Subject '科目',MIN(Score)'最低分' from StuScore group by Subject;  

5.9.6、求各科参考人数

select Subject '科目',count(*)'实际参加考试的人数' from StuScore group by Subject;  

5.10、HAVING子句：筛选

select Subject '科目',AVG(Score)'平均分>=70' from StuScore group by Subject having AVG(Score)>=70 order by AVG(Score) desc;  

第六章

如果一个查询需要对多个表进行操作，就称为联接查询。联接查询的结果集或结果表，称为表之间的连

接。

联接查询分为内部联接查询，自联接查询，外部联接查询和复合条件联接查询等等。

6.1、内（部）链接

select bi.StuNo,Name,Gender,Age,Idcard,Subject,Score from Baselnfo bi inner join StuScore ss on bi.StuNo= ss.StuNo  

--两个表名中间使用逗号分隔，使用where子句写关联的字段

select bi.StuNo,Name,Gender,Age,IdCard,Subject,Score from Baselnfo bi inner join StuScore ss on bi.StuNo=ss.StuNo  

6.2、外（部）联接

6.2.1、左外联接：left [outer] join

--特点：以left join左边的表为主表，右边的表为从表，，优先显示主表的所有记录的所有记录，若从表中没有相匹配的记录，则用“NULL”填充。

select bi.StuNo,Name,Gender,Age,IdCard,Subject,Score from Baselnfo bi left join StuScore ss on bi.StuNo=ss.StuNo  

6.2.2、右外联接：right [outer] join

--特点：以right join右边的表为主表，左边的表为从表，，优先显示主表的所有记录的所有记录，若从表中没有相匹配的记录，则用“NULL”填充。

select bi.StuNo,Name,Gender,Age,IdCard,Subject,Score from Baselnfo bi right join StuScore ss on bi.StuNo=ss.StuNo;  

6.3、交叉联接：cross [outer] join

--特点：查询结果集的记录条数等于cross join左右两边表的记录条数的乘积。

select count(*) from Baselnfo cross join StuScore  select count(*)from Baselnfo--9 select count(*) from StuScore--7  

6.4、 自联接

联接不仅可以在表之间进行，也可以使一个表同其自身进行联接，这种联接称为自联接，相应的查询称

为自联接查询。

6.5 、更复杂的联接查询

SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 条件表达式1 INNER JOIN 表3 ON 条件表达式2   

6.6、集合运算

union和unionall--并集运算

intersect--交集运算

except--减集

6.6.1、 使用UNION和UNION ALL进行并集运算

UNION 和 UNION ALL都将两组查询进行了并集，唯一的区别在于，UNION不允许有重复列而UNION

ALL允许有重复的列。

UNION 返回的结果集的标题仅从第一个查询获得。

6.6.2、使用INTERSECT进行交集运算

交集同样的是对多个结果集合进行操作。交集将两个结果集中相同的记录取出来，形成一个新的集合。

6.6.3 、使用EXCEPT进行减集运算

减集是指比较2个结果集，将EXCEPT 关键字前的结果集除去交集部分而组成的新的集合。

注意：减集的最后获得的结果与2个结果集放的顺序有密切的关系。

Microsoft Visio 2010软件

​