SQL server学习笔记一

多一种技能多一份自信与安全感！！！

前提：window 10系统。从Microsoft官网安装完成SQL Sever 2019以及SQL server management studio。

数据库

创建数据库

1、菜单式创建
登录时，服务器类型选择数据引擎，身份验证选择windows验证。

登录完成后在左侧列表第一个项目“数据库”一级主文件夹右键—新建数据库，填写数据库名称，以及调整数据库主文件primary和日志文件的参数。

2、SQL脚本代码创建：
在工具栏点击“新建查询”，然后编写代码，点击“√”进行分析，选中要执行的代码行，点击“执行(X)"运行。

create database test01
on primary(
name=test01_dat,
filename='D:\mytest\test01_da.mdf',
size=10mb,
maxsize=60mb,
filegrowth=5mb
)
log on (
name=test_log,
filename='D:\mytest\test01_da.ldf',
size=5mb,
maxsize=25mb,
filegrowth=1mb
)

修改和删除数据库

1、菜单栏，在test01数据库处右键选择属性可以修改，test01中两个文件mdf和ldf的属性，并且右键选择重命名可以修改数据库的名字。
2、脚本实现
重要：exec sp_helpdb dataname

--修改数据库的名字
alter database test02
modify name=test1
--修改数据库文件的属性
alter database test1
modify file(
name=test01_dat,
size=20mb,
maxsize=50mb,
filegrowth=10mb
);
--删除数据库
drop database test1
--查看数据库的详细信息
exec sp_helpdb test1

3、在要删除的数据库右键选择删除，在删除的同时会一并删掉mdf文件和ldf文件。

数据库备份和还原，分离以及附加

其中数据库的备份和还原是针对.bak后缀文件完成的。而分离和附加是针对.mdf文件来对数据库进行操作的。
备份及还原：
①备份：左侧列表在要修改的数据库名称右键—任务—备份—选择备份类型（完整、差异、事务日志），备份组件为数据库，选择备份到磁盘—选择备份路径设定文件名后缀为.bak。
②还原：在”数据库“大文件夹(一级总文件夹)，右键选择—还原数据库

分离和附加
①分离：要修改的数据库右键选择—任务—分离这种方式也能删除数据库，但是这种删除数据库时候，只会在系统中删除数据库个体，并不会删除mdf和ldf文件。后续可以利用现存的mdf文件进行数据库恢复。
②附加：在”数据库“大文件夹名字后面右键—选择附加

表格

新建表格

1、菜单式。
在选择要建表的数据库位置，点开小菜单，在表的位置右键—新建—表。
然后设置表的列名，列的数据类型，设定是否允许空值。
注：大多数表都有一个主键，类似于ID这种，每一个ID都是唯一的，即任何主键值都不能重复。
设置主键的方法：在某个要设为主键列的位置，右键选择—

2、脚本式
注意写脚本的时候，左上角的框框要显示当前要新加表的数据库

--通过primary key给表格设定主键
create table userinfo2(
ID int primary key not null,--这里not null表示不能为空
age int not null,
name varchar(10) null --表示这一列允许是空值
)

修改表格属性（字段名，字段类型，是否允许null，添加主键，添加字段）

重要：exec sp_rename 用于修改字段名或表名

--修改字段类型，字段类与是否允许空是连一起的
--如果只写字段类型，则默认对勾允许null
alter table userinfo1
alter column name int not null;

--添加主键
--这里constraint KID是给主键命了一个名字
--但是列名还是ID
alter table userinfo1
add constraint KID primary key (ID);

--添加字段
alter table userinfo1
add age int not null;

--修改字段名，即修改列名
--注意这里用的是exec开头的，就不要用alter table...否则一起执行会报错
exec sp_rename 'userinfo1.grade','usergrade','column';

--修改字段名，即修改列名
--这里也可以不加‘column’
exec sp_rename 'info1.grade','grade0';
--修改表名
exec sp_rename 'userinfo1','info1';

关于表格的主键（添加、查询、删除）

一个表只有一个主键，主键可以由一个字段或多个字段组成（单字段主键、多字段主键），比如给定三年级一班32号确定一条记录，这里三年级、一班、32号是三个字段一起决定一条记录。

创建主键的时候最好给主键一个名字，这里利用constraint KID给主键命名为KID，这样到时候删除主键时方便操作。不给主键命名的话，系统会给一个很长的主键名，可以利用命令查看主键的名称。
重要：exec sp_helpconstraint ...查询主键的名称

--添加主键
alter table info1
add constraint KID primary key(ID);

--查询主键的名称
--不是字段的名，是另外创建主键的时候给主键的名字
exec sp_helpconstraint 'info1' 

--删除主键
alter table info1
drop constraint KID

表格的主键和外键alter table… add constraint…

了解主键和外键前，先分辨主表和“从表”。
主表是有一个主键的表。
”从表“也是一个有唯一主键的表，只是在从表中额外设置了外键，这个外键链接到主表的主键上面，建立主表和“从表”之间的关系纽带。
先建立主表，再建立“从表”，再在”从表“里面添加外键进行联系。
注意：
①主表中一定要设置主键，”从表“中也要设置主键。
②主表中主键的字段类型和字段长度，与“从表”中的外键的字段类型与字段长度一定要一致。
如下代码中，主表中的主键info2.sysclass一定要与“从表”中外键infocong.class字段类型、长度一致。
建立外键的目的是保持数据一致性，完整性，控制存储在外键表中的数据【换句话来说，”从表”中外键的取值要靠主表中的主键的取值来修改、增加、删除等操作，不能直接在“从表”中修改外键对应的值】。

--给从表infocong添加外键关系
--外键连接到主表info2中的主键sysclass
--注意：这里info2.sysclass必须先设成主键
--且info2.sysclass的字段类型及长度必须与infocong.class一致
alter table infocong
add constraint FK_infocong_info2 
foreign key(class) references info2(sysclass);

数据导入

编辑前200行，表格填写

可以把表的第一个字段设成自动增长的形式，这样只用填后面三列的数据，第一列自动生成1，2，3，4…顺序数据

可以右键表格选择编辑前200行进行表格输入数据。注意：这种方式比较不好的一点就是，一旦关闭表格里面的数据就自动保存了，即便你没有手动提交保存什么的

脚本实现行记录输入insert into…values(…)

--查看表中所有内容
select * from student

--向表中插入一行数据
insert into student(userid,username,email)
values('202018','马萌','ma@qq.com')
--向表中插入多行数据
insert into student(userid,username,email)
values('202017','王来','qi@qq.com'),('202001','咳咳','kek@163.com')

从别的表导入数据insert into…select…from…

另外一张表格stu中有3条记录，现将这三条记录导入到student中去。

--从别的表导入数据
insert into student(userid,username,email)
select id,name,email from stu
--查看增添后的student表
select * from student

数据查询

查看前几行top

--只显示前5行,查看所有列
select  top 5 * from student

查询时，删除重复项distinct

--筛选剔除重复项distinct
--第一种，只有一个字段，如果有重复的就剔除并且按数值升序
select distinct userid from student

--第二种，两个字段
--记录1和记录2的userid和username完全一致才会被剔除
select distinct userid,username from student

按照某列升序asc或降序desc显示结果

--按照某一列升序asc
select * from student order by userid asc
--按照某一列降序desc
select * from student order by userid desc

--查询后的记录排序显示
--可以使用组合排序，先用classs字段排(未设置)默认asc
--若classs字段取值相同再用scores排序(设置desc)
select userid,username from student
order by classs,scores desc

条件查询where

• 精确限制条件

where 字段=值

--精确查询
select * from student
where userid=202015

• 模糊查询条件

where 字段 like ‘%值%’
这里%表示模糊的形式，%值表示以值结尾，值%表示以值开头。

--模糊查询
select * from student 
where userid like '%1%'
--查询id里面含有1的记录，不管1开头、结尾、中间

在范围内条件查询between

--范围查询
select * from student
where userid  between 202000 and 202010
--查询不在某范围内[202000,202010]的记录
select * from student
where userid not between 202000 and 202010

在集合内的记录查询in以及子查询

• 普通in查询

--查询在集合内的记录in
select * from student
where username in ('张妮','李斯')

--查询不在集合内的记录not in 
select * from student
where username not in ('张妮','李斯')

• 利用in实现子查询

--子查询
--其实就是查询另一张表的列，并将它放到in后面作为一个范围集合
select * from student
where userid in (select stuid from classinfo)

子查询exists

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False.

--查询主表student中的字段，这里classinfo是子表
--若主表中有存在于子表里面的记录，那么就返回对应记录的主表字段
select s.username, s.email,s.userid from student s
where exists (select * from classinfo c where s.userid=c.stuid)
--这里s.userid=c.stuid就是用来判断主表中是否有子表里面一致的内容

表记录（行）

修改表记录update…set…where

--修改表里面的某一条记录
update student
set username='张妮'
where userid=202015

删除表记录delete…where

--删除某一条记录，删除表student中id等于1的那行
delete student
where id=1

多表查询

关联查询

交叉关联inner join…on

只返回两张表中联结字段(示例中userid和stuid)相等的行。关于表的顺序就是先a表再b表的从左往右排。

--交叉关联inner join..on
select * from student a
inner join classinfo b
on a.userid=b.stuid

左关联left join…on…

注意：这里提到的left join …on…以及后面提到的right join…以及full join都是外连接(outer join)的类型，其实也可以写作 left outer join …on等形式
以左边的表为主表，返回主表的全部内容，以及与主表匹配的右表内容，右表中不匹配的用NULL填充。

--左关联left join ..on
select * from student a
left join classinfo b
on a.userid=b.stuid

右关联right join…on

以右表为主表，显示主表的全部内容，以及显示左表中匹配的内容，不匹配的用NULL填充。

--右关联right join ..on
select * from student a
right join classinfo b
on a.userid=b.stuid

全连接full join …on…

表格左右顺序同样是a表在左，b表在右
如果a和b中主键字段一致，则全部显示；如果a中有的主键值b中没有，则a中正常显示，b中对应位置用NULL填充；同理如果b中有的主键值a中没有，则b中的记录正常显示，a中对应位置用NULL填充。

三表联结

--三表交叉关联inner join..on
select * from student a
inner join classinfo b
on a.userid=b.stuid
inner join stu c
on c.name=a.username

SQL中的函数

聚合函数avg()、sum()

• avg()
  返回组中各值的平均值。其中忽略Null值。
  计算的字段类型必须为数字型（整数、小数）

--计算平均值
select avg(scores) from student
--给平均值的列，命别名,as可省略
select avg(scores) as avgscore from student

• sum()
  

两个字符串类型的字段相连

这里两个字段都要是字符串类型，可以使用’+‘相连，或’_'相连，或直接相连。同时可以给连接后的字段命名。

--字符串类型的字段相连
--连接之前
select username,email from student
--连接之后
select username+'+'+email as info from student

聚合函数min()、max()

返回表达式的最小值，忽略NULL值，计算字段可以是数字型也可以是字符型。其中字符型是按照abcd等英文字母，或者中文字符的首字母排列。

统计项数count()

返回组中总的记录数，忽略NULL值，返回结果是整型的数值。
如果总记录数超过了$2^{23}-1$条，则使用count_big()函数，这个函数与count()用法相同，只是作为大量数据的补充用法，因为数据量超标时count()函数会报错。
如果使用count(*)表示统计总行数，count(col)表示统计某一列中不含null的个数

--统计某一列中有真实值的项数，排除NULL项
select count(score) from student

字符数len()、字节数datalength()

返回指定字符串表达式的字符数，其中不包含尾随空格。

--查询字符串的字符数和字节数
select email,len(email) as '字符数',datalength(email) as '字节数' from student

随机数

• 在查询分析器中执行select rand()，可以随机得到一个小数，类似：0.836393…
• select floor(rand()*N)或者select ceiling(rand()*N)用于产生整数随机数。整数随机数更常用一些

--利用rand()产生0-1之间的随机小数
select rand()
--rand里面指定一个参数，设定随机数种子
--命名时省略了as
select rand(3) '种子为3'
--rand()*10表示产生随机小数后与10相乘
select rand(3)*10  '随机数10倍'
--向下取整floor
select floor(rand(3)*10) '向下取整'
--向上取整
select ceiling(rand(3)*10) '向上取整'

时间

获取当前时间getdate()

• getdate()返回当前数据库系统时间，返回值类型为datatime
• getutcdate()返回当前国际标准时间，返回值的类型为datatime
  

时间格式转换convert(…,…,…)

convert()函数把日期转换为新数据类型。convert(字段类型，转换对象，目标日期格式)
常用的日期格式参考表

style ID	style格式
100或0	mon dd yyyy hh:miAM
101	mm/dd/yy
103	dd/mm/yy
105	dd-mm-yy
106	dd mon yy
107	mon dd,yy
110	mm-dd-yy
111常用	yy/mm/dd
112	yymmdd
120常用	yyyy-mm-dd hh:mi:ss(24h)

select convert(varchar(50),getdate(),111) as '111时间格式'
select convert(varchar(50),getdate(),120) as '120时间格式'

时间差值datediff(…,…,…)，dateadd(…,…,…)

datediff函数返回两个日期之间的天数。dateadd函数在原时间上加上一段时间。

日期的部分获取datepart()、datename()

datepart函数常用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。返回类型是一个INT整型。
datename()这个函数也是用于返回日期、时间的单独部分。返回类型是一个varchar型。

日期的部分获取year()、month()、day()

--返回值是int型
select year('2021-03-17') 'year'
select month('2021-03-17') 'month'
select day('2021-03-17') 'day'

字符串

字符串位置查找charindex()、patindex()

• 字符串匹配查找charindex(…，…，…)
  charindex(目标，被找的字符串，开始查找位置)。如果省略开始查找位置，则默认从第一位开始查找。返回字符或字符串在另一个字符串中的起始位置。函数结果返回一个整数，返回的整数是要找的字符串在被找的字符串中的位置。假如charindex中没有找到目标字符串，则返回0.
• 字符串通配符查找patindex(…，…)
  这个函数也是返回字符或字符串在另一个字符串或者表达式中的起始位置，返回值类型是整数，但是它可以支持通配符%的使用，并且不用指定起始搜索位置。
  
• 举例。如何把字符串查找应用到数据表中

select userid,email,charindex('qq',email) as 'qq' from student

字符串拼接处理stuff()

stuff()函数用于删除指定长度的字符，并可以在指定的起点处插入另一组字符，返回值类型为字符串型。

字符串截取substring()

用于截取指定长度的字符串。

--从第5位开始截取3个字符
select substring('abbccddee',5,3)

--在数据表中实现，从第3位开始，截取两个字符
--如果截取长度超过字符串本身长度，则取到最后一个为止
select email,substring(email,3,2) as sub1,substring(email,3,100) as sub2 from student

字符串截取left()、right()

left()从左往右截取指定长度的字符，right()从右往左截取指定长度的字符。

字符串删除空格ltrim()、rtrim()

这两个函数都是用于删除字符串中的空格，ltrim()删除字符串左端开头的空格，rtrim删除字符串右端结尾的空格。

字符串大小写转换upper()、lower()

字符大小写转换只针对英文字符。

字符串替换replace(…，…，…)

用一个字符串替换所有出现过的字符

--某字符替换为新字符，所有符合都替换
select replace('aabbssrrxfsaa','aa','mu')
--将原字符串中某字符全部替换为新字符
select email,replace(email,'qq','163') as newemail from student

字符串复制replicate()、空格space()

其中replicate(字符串，n)将字符串复制n次。space(n)是给定n个相连的空格。

字符串倒置reverse()

实现字符串逆序输出形式，返回值仍然是一个字符串。

字符串类型转换称其他类型cast(…as…)

重要：这里cast函数中不是用逗号，而是用的as，很特殊！！！

--字符串转换成数值型
select cast('1234'as int) 'char_int'
--将数值型转换为字符串型
select 'abc'+cast(1 as varchar(10)) 'varchar'
--将浮点数转换为整型
select cast(13.8 as int)  'int'
--将浮点型调整小数位数,数字最长为10，小数点后3位
select cast(12.6 as decimal(10,3)) 'decimal'
--将字符串型转成时间类型
select cast('2021-03-04' as datetime)  'datetime'

条件判断转换函数case()

它会把满足条件的表达式转换为对应的结果，分为简单case函数和case搜索函数。

• case搜索函数可以写不等式的判断式，记得写end以及from tablename

• case搜索函数也能用in集合形式。
  

• 简单case函数，只能用等式的判断。
  

相关参考资料

SQL server安装博客参考https://blog.csdn.net/weixin_44340836/article/details/111307903
学习参考B站视频https://b23.tv/DqLHaw