软件测试5班Sql Server课堂笔记（2019-09-25）

Sql server:微软，端口：1433
Mysql数据库：端口：3306
Oracle数据库：端口：1521

Sql server启动服务的二种方式：
在sql server配置管理器中启动服务
在计算机—管理工具—服务启动

实体：一条数据库中的记录。数据库表中存放格式相同的实体。
冗余：表中存在大量的重复数据。消除冗余会似表结构变的复杂，所以允许存在一定的冗余，一张表做一件事。
完整性：正确的数据输入到数据表中。

服务器类型：数据库引擎
服务器名称：数据库服务器的ip地址/数据库服务器的计算机名/localhost/.(本机)
身份验证：Windows身份验证/Sqlserver身份验证
登录名:Windows登录用户/sa(sqlserver管理员)
密码：******

数据库文件
mdf文件：数据库主文件，用来存储数据表，一个库有且仅有一个mdf文件。
ndf文件：数据库的辅文件，用来存储数据表，一个库可以有0-N个ndf。
ldf文件：数据库的日志文件，用来记录数据库操作日志。

完整性约束
1：数据类型的约束，规定了输入到表中的数据类型。
文本数据类型：
char类型（长度是8000）：固定长度的非unicode编码字符。（非unicode字符一个长度等于8位2进制，可以表示一个英文数字或者符号，两个可以用来表示双字节字符）
char(10):英文是10个，汉字是5个
varchar(长度)，可变长度非非unicode编码字符
nchar(长度，上限4000)，固定长度的unicode编码字符.(unicode字符一个长度等于16位2进制，不论什么字符都用16位二进制表示)
nvarchar(长度)，可变长度的unicode编码字符
text,非unicode长文本
ntext,unicode长文本
数字类型：
整型，int
浮点数,float

2：非空约束，规定字段值不允许为空（NULL）
语法：字段名 字段类型 not null

3：主键约束，规定字段值唯一，且非空，一张表只能有1个主键，但是可以将多个字段作为联合主键，主键约束字段会建立聚合索引。
索引：方便检索，提高查询效率（降低修改效率）
聚合索引：数据本身建立索引，数据会排序
非聚合索引：在数据库中建立索引空间，存放索引项
语法：字段名  字段类型 primary key
primary key(字段名,...)
设置联合主键：sid+name

4:标识列：非空，只能设置整型字段，标识列有种子（起始值）与增长量组成，标识列不能修改，会自动增长，增长不会退回，不会停止。
语法：字段名  字段类型 Identity(种子,增长量)

5：默认值绑定：在不输入数据的情况下会自动输入默认值。
语法：字段名 字段类型 Default '默认值'

6：唯一键约束：规定字段值唯一。
语法：字段名 字段类型 unique

7：外键约束：外键字段值引用于主表中的主键字段或者唯一字段。
建表，先建主表，再建外键表。
数据插入，先插主数据表，再插入外键数据表。
数据删除，先删除外键数据表，再删除主键数据表。
删除表，先删除外键数据表，再删除主键数据表。
语法：
字段名 字段类型 Foreign Key References [库名].dbo.[表名](字段名)
Foreign Key(外键字段名) References [库名].dbo.[表名](字段名)

8：检查约束(check约束)，自定义约束条件。
关系运算符，>,<,>=,<=,=,!=(<>)
逻辑运算符，and or not
语法：
字段名 字段类型 Check(表达式)

通配符：
[范围]：范围内的任意一个字符
[^范围]：范围外的任意一个字符
%：0-N个任意字符
_：1个任意字符
%_：1-N个任意字符

SQL: 结构化查询语言
DCL:数据库控制语言
--用来控制存取许可、存取权限等
--GRANT、REVOKE等

DDL:数据定义语言
--用来建立数据库，数据库对象和定义其列
--Create Table,Drop Table等

DML：数据操作语言
--查询、插入、删除和修改数据库中的数
--Select Insert  Update Delete

变量说明、流程控制、功能函数
--定义变量、判断、分支、循环结构
--日期函数、数学函数、字符函数、系统函数

DDL
建立数据库
--单行注释
Create DataBase [T015] On
(
Name=T015,
FileName='D:\1\T015.mdf',
Size=8MB,
MaxSize=UnLimited,
FileGrowth=64MB --增长百分比%
)

Log On
(
Name=T015_log,
FileName='D:\1\T015_log.ldf',
Size=8MB,
MaxSize=UnLimited,
FileGrowth=64MB --增长百分比%

)

删除数据库：Drop DataBase[库名]

切换当前数据库：Use [库名]

建立数据表：
Create Table [库名].dbo.[表名]
(
[字段名1] 字段类型 约束条件,
[字段名2] 字段类型 约束条件,
...
[字段名N] 字段类型 约束条件
)

删除数据表：
Drop Table [库名].dbo.[表名]

批处理命令：Go

DML
插入语句:insert
单行插入
语法：
insert [into] [库名].dbo.[表名]
[([字段名]),...]
values
(值,...)

多行插入第一种方式，被插入的表必须存在
语法：
insert [into] [库名].dbo.[表名]
[([字段名]),...]
select 字段名,...
from [库名].dbo.[源表名]

多行插入的第二种方式，被插入的表不能存在，插入的同时就会建立表，只能插一次
语法：
select 字段名,...
into [库名].dbo.[表名]
from [库名].dbo.[源表名]

多行插入的第三种方式，
语法：
insert [into] [库名].dbo.[表名]
[([字段名]),...]
select 值,...Union/Union All
....
select 值,...

Union与Union All的区别：

Union:完全相同的数据，只能插入1行
Union All:完全相同数据，会全部插入

更新语句:update
语法：
update [库名].dbo.[表名]
set 字段值=更新值,...
[Where 更新条件]

删除语句：delete
语法：
delete from [库名].dbo.[表名]
[where 删除条件]

Truncate Table [库名].dbo.[表名]
删除的数据不能恢复
删除数据的时候，会收缩数据库，会释放空间，delete不会
没有条件删除

查询
基本语法：
select <列名>
from <表名>
[where <查询条件表达式>]
[order by<排序的列名>][asc或desc]

查询全部的行和列
select * from 表名

查询部分行
使用As来命令列

空值查询：
is Null/is not Null

使用常量列

限制行查询：
select top 行数/百分数 percent --sql server
...limit 行数[起始行数,终止行数(-1到最后)]--mysql

查询不重复的内容：
select Dintinct

连接两次查询内容：
select...
Union(去重)/Union All
select...

模糊查询：
like 表达式(通配符)：查询时，字段中的内容，并不一定与查询内容完全匹配，只要字段中含有这些内容
is Null：把某一字段中内容为空的记录查询出来
between 值1 and 值2：把某一字段中内容在特定的范围内的记录查询出来
in(值1，值2...)：把某一字段中的内容与所列出来的查询内容列表匹配的记录查询出来

聚合函数：如果查询字段中包含聚合函数，则无法同时查询普通字段，可以和分组字段一起查询。字段中空值不做聚合。
sum(字段名),求和
avg(字段名),求平均值
Max(字段名),求最大值
Min(字段名),求最小值
count(*/字段名),求值的个数

分组查询：如果使用分组查询，则无法同时查询普通字段，可以与聚合函数一起查询。
select 分组字段
from 表
where 对源数据进行筛选
group by 分组字段
having分组后的筛选条件（分组字段，聚合函数）

创建字段索引：
语法：
Create index 索引名 on [库名].dbo.[表名](字段名[Asc/Desc],...)
Create  unique index 索引名 on [库名].dbo.[表名](字段名[Asc/Desc],...)

函数
字符串函数
charindex('查找内容','源字符串'[,起始位置]),返回值为查找到的内容首字母所在的位置，如果未找到返回0。
len('字符串')，返回字符串的长度
lower('字符串')，返回小写字母
upper('字符串')，返回大写字母
ltrim(字符串),返回去除字符串左边空格
rtrim(字符串),返回去除字符串右边空格
left('字符串',返回数)，返回从左边数起指定数量的字符串
right('字符串',返回数)，返回从右边数起指定数量的字符串
replace('源字符串','查找内容','替换内容')，返回值为替换以后的字符串
stuff('源字符串',起始位置,替换的数量,'替换内容')，返回值为替换以后的字符串

日期函数
getdate(),返回当前系统日期时间
dateadd(单位,数量,日期),返回在指定日期增加指定单位数量后日期
datediff(单位,前一个日期,后一个日期),返回两个日期指定单位差值
datename(单位,日期),返回日期指定单位字符串形式
datepart(单位,日期)，返回日期指定单位整数形式

数学函数
ABS,取数值表达式的绝对值
Ceiling, 返回大于或等于所给数字表达式的最小整数
Floor,取小于或等于指定表达式的最大整数
power,取数值表达式的幂值
Round,将数值表达式四舍五入为指定的精度
Sign，对于整数返回+1,对于负数返回-1，对于0则返回0
Sqrt,取浮点表达式的平方根

系统函数
convert(转换类型，表达式),返回转换类型后的值
cast(表达式 as 转换类型),返回转换类型后的值
datalength(表达式)，返回数据长度
isnull(表达式,值),返回值，如果表达式为空值，则返回指定值，否则返回表达式原本的值
Current_user,返回当前用户的名字
Host_Name,返回当前用户所登录的计算机名字
User_name,从给定的用户ID返回用户名

多表联结查询

内联结(inner join)
select 字段
from 表1 别名
inner join 表2 别名
on 联结条件
where 筛选条件

内连接另外一种更常用的写法：
select 字段
from 表1 别名,表2 别名...
where 联结条件
and 筛选条件

外联结
--左外联结(left join)
select 字段
from 表1 别名
left [outer] join 表2 别名
on 联结条件
where 筛选条件

--右外联结(right join)
select 字段
from 表1 别名
right [outer] join 表2 别名
on 联结条件
where 筛选条件

--完整外联结(full join)
select 字段
from 表1 别名
full [outer] join 表2 别名
on 联结条件
where 筛选条件

交叉联结(cross join)：笛卡尔积
select 字段
from 表1
cross join 表2

子查询：在一条sql语句中包含了另一条查询语句（嵌套查询）。子查询会产生结果值，结果值可用作筛选条件，也可用于插入值。当表达式使用关系运算符，必须保证子查询的值是1个，当子查询的值有多个时，使用in/not in来筛选。建议使用in来代替=，not in代替!=。

外面的查询成为父查询，括号中嵌入的查询称为子查询。
做大于、等于、小于的时候，子查询的结果必须只有一个值。

in后面的子查询可以返回多条记录
常用in替换等于(=)的比较子查询，not in替换不等于
如果父查询的内容来自多张表的内容，只能采用表联结来实现

 

视图：在sql中，视图是基于sql语句结果集的可视化的表。视图包含行和列，就像一个真实的表，视图中的字段就是来自一个或者多个数据库中的真实的表中的字段。

创建视图(view)
creat view [库名].dbo.[视图名] As
(
查询语句
)

视图是对表数据的映射，视图是不存储的数据的，数据来源数据表
表中的数据改变之后，视图中的数据也会随之改变
视图中的数据改变之后，表中的数据也会随之改变

Case语句：改变查询显示值，当满足指定条件，可对查询结果显示值进行改变，但不会实际修改表中的数据
select
case 字段名
when 值1 Then 显示值1
when 值2Then  显示值2
...
[Else显示值/字段名，表示字段原有值，如果没有Else,出现When以外的情况时，会显示空值]
End as 别名
From 表名

列转行pivot
pivot(聚合函数(新列值字段)for 列字段 in (新列字段名,...))AS别名

行转列
Unpivot(值行转列名 for 字段行转列名 in(列值,...)) As别名