SQL Server

1. 数据库创建

create database testdb01
--主文件设定
on primary (
--数据库主文件的名称
name=textdb01,
--存放路径
filename='D:\testdb\test01.mdf',
size=10MB,
maxsize=100MB,
filegrowth=5MB
)
log on(
name=textdb01_log,
filename='D:\testdb\test01_log.mdf',
size=10MB,
maxsize=100MB,
filegrowth=5MB
)

2. 数据库的修改

--查看testdb02
exec sp_helpdb testdb02;

--把数据库testdb01的名称改为testdb02
alter database testdb01 
modify name=testdb02;

--修改testdb02下的textdb01
alter database testdb02
modify file(
name='textdb01',
size=20MB,
maxsize=50MB,
filegrowth=10MB
);

3. 数据库的删除

--删除testdb数据库
drop database testdb

4. 数据库的备份与还原

• 差异备份
• 完整备份

备份：

还原：

5. 数据库的分离和附加

分离：

附加：

6. 数据类型

SQL Server 数据类型网页链接：https://www.w3school.com.cn/sql/sql_datatypes.asp

7. 新建表

create table userinfo2(
--primary key主键
ID int primary key not null,
name varchar(10) not null,
age int null
)

8. 修改、删除表结构

8.1 修改字段类型长度

--修改字段类型长度
--表名：userinfo
alter table userinfo
--字段名：name,改为varchar(100)
alter column name varchar(100)

8.2 修改字段类型

--修改字段类型
alter table userinfo
alter column age float

8.3 添加not null约束

--添加not null约束
alter table userinfo
alter column age float not null

8.4 设置主键

--设置主键
alter table userinfo
--语法：add constraint 主键名 primary key (字段名)
add constraint KID primary key (ID)

8.5 更改字段名

--更改字段名
--语法：exec sp_rename '表名.字段名','更改后的字段名','column'
exec sp_rename 'userinfo.age','userage','column'

8.6 添加字段名

--添加字段名
alter table userinfo
add grade varchar(10) not null

8.7 删除表

--删除表userinfo2
drop table userinfo2

9. 定义表主键、外键

主键和外键的作用：

1. 为了维护关系数据库的完整性

   主键是能确定一条记录的唯一标识，比如，一条记录包括身份证号，姓名，年龄。身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。

   外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。比如，A表中的一个字段，是B表的主键，那他就可以是A表的外键。

2. 起约束作用

   外键取值规则：空值或参照的主键值。

   • 插入非空值时，如果主键表中没有这个值，则不能插入。
   • 更新时，不能改为主键表中没有的值。
   • 删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。
   • 更新主键记录时，同样有级联更新和拒绝执行的选择。

--添加SQL Server 外键
--从sys_user_role表建立外键
alter table sys_user_role
--FK_user_role外键名称
--设定到RoleSysNo列上，RoleSysNo即从表上作为外键的那一个列名
--RoleSysNo依赖于sysno列
add constraint FK_user_role foreign key(RoleSysNo) references sys_role(sysno)

10. 新增表记录

10.1 插入单行数据

--查询
select * from userinfo

--插入单行数据记录
insert into userinfo(userid,username,email)
values('ganvezhang','张三','ganvezhang@qq.com')

10.2 插入多行数据

--插入多行数据
insert into userinfo(userid,username,email)
values('Tommywang01','王雪平','Tommywang01@qq.com'),
('johnli01','李四','johnli01@qq.com')

10.3 数据复制

--将Sys_User表中的a,b,c三列数据复制到userinfo表中的userid,username,email三列下
insert into userinfo(userid,username,email)
select a,b,c from Sys_User

11. 查询表记录

--查询userinfo表中的所有数据
select * from userinfo

--查询userinfo表中的userid,email这两列数据
select userid,email from userinfo

--查询userid列并去重
select distinct userid from userinfo

--查询前10行数据
select top 10 * from userinfo

12. 修改表记录

--修改userinfo表中ID为8的数据字段，将其userid字段值改为a3
update userinfo
set userid = 'a3'
where ID = 8

13. 删除表记录

--删除userinfo表中ID字段等于10的记录
delete from userinfo
where ID = 10

--简写：删除userinfo表中ID字段等于12的记录
delete userinfo
where ID = 12

14. 条件限制 where

• 精确限制条件

--查询userinfo表中userid等于a1的字段
select * from userinfo
where userid = 'a1'

• 模糊限制条件

--查询userinfo表中userid以a开头的字段
select * from userinfo
where userid like 'a%'

--查询userinfo表中userid以1结尾的字段
select * from userinfo
where userid like '%1'

--查询userinfo表中userid中含有a的字段
select * from userinfo
where userid like '%a%'

15. BETWEEN 语法

--查询ID在5到9的数据
select * from userinfo
where ID between 5 and 9

--查询ID不在5到9的数据
select * from userinfo
where ID not between 5 and 9

--查询userid在a1到a3的数据
select * from userinfo
where userid between 'a1' and 'a3'

--查询createTime在2021-01-01到2021-01-04的数据
select * from userinfo
where createTime between '2021-01-01' and '2021-01-04'

--查询createTime不在在2021-01-03到当前时间的数据
--GETDATE()得到系统服务器的当前时间
select * from userinfo
where createTime not between '2021-01-03' and GETDATE()

--查询服务器的当前时间
select getdate()

16. IN 语法

--查询ID数据是1,3,5的数据
select * from userinfo
where ID in (1,3,5)

--查询ID数据不是1,3,5的数据
select * from userinfo
where ID not in (1,3,5)

--查询username数据是李四、王雪平的数据
select * from userinfo
where username in ('李四','王雪平')

--查询userinfo表中的ID数据是userID表中的id_1的数据
select * from userinfo
where ID in (select id_1 from userID)

17. EXISTS 语法

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

EXISTS 指定一个子查询，检测行的存在。

--用a来代替userinfo这个表名
--同 select userinfo.email,userinfo.ID,userinfo.userid,userinfo.username,userinfo.createTime from userinfo
select a.email,a.ID,a.userid,a.username,a.createTime from userinfo as a
--若a表和b表都存在同一数据项，exists则返回true，上一条语句则返回a表的email、ID、userid、username、createTime等数据
where exists (select * from userID b where a.ID = b.id_1)

--exists非运算
select a.email,a.ID,a.userid,a.username,a.createTime from userinfo as a
where not exists (select * from userID b where a.ID = b.id_1)

18. 返回记录排序

--根据userID表中的id_1数据进行升序排序，默认为升序
select * from userID
order by id_1

--完整写法，根据userID表中的id_1数据进行升序排序
select * from userID
order by id_1 asc

--根据userID表中的id_1数据进行降序排序
select * from userID
order by id_1 desc

--优先根据userID表中的id_1数据进行升序排序，再按照id_2进行升序排序
select * from userID
order by id_1,id_2

--优先根据userID表中的id_1数据进行升序排序，再按照id_2进行降序排序
select * from userID
order by id_1,id_2 desc

19. 关联查询

• inner join（交叉关联）只返回两个表中联结字段相等的行。
• left join（左关联）返回包括左表中的所有记录和右表中联结字段相等的记录。
• right join（右关联）返回包括右表中的所有记录和左表中联结字段相等的记录。

--查询a表和b表中S#数据列相同的数据
select * from Student a
inner join SC b
on a.S# = b.S#

--查询a表的所有数据和b表中S#数据列相同的数据
select * from Student a
left join SC b
on a.S# = b.S#

--查询b表的所有数据和a表中S#数据列相同的数据
select * from Student a
right join SC b
on a.S# = b.S#

--关联多张表
select * from Student a
inner join SC b
on a.S# = b.S#
inner join Course c
on b.C# = c.C#
--附带where条件限制
where a.S# = 01

20. 聚合函数

20.1 聚合函数 AVG()

返回组中各值的平均值。其中忽略 NULL 值。

计算字段类型必须为数字型（整数、小数）。

--计算SC表中score字段的平均值
select AVG(score) from SC

20.2 聚合函数 SUM()

返回表达式中所有值的和。其中忽略 NULL 值。

SUM 只能用于数字列。

--计算SC表中score字段的和
--score字段命名为sum_score
select SUM(score) as sum_socre from SC as a

拼接：

--将Student表中Ssex、Sname两列拼接在一起显示
select Ssex+Sname from Student

--将Student表中Ssex、Sname两列拼接在一起显示
select Ssex+'_'+Sname from Student

20.3 聚合函数 MIN()

返回表达式最小值。

MIN 忽略任何 NULL 值。

计算字段类型可为数字型或字符型。

--求SC表中score字段的最小值
select MIN(score) from SC

20.4 聚合函数 MAX()

比较多个对象并返回最大值。

MAX 忽略任何 NULL 值。

计算字段类型可为数字型或字符型。

--求SC表中score字段的最大值
select MAX(score) as max_score from SC

21. COUNT()函数

• 返回组中的项数。
• 其中忽略 NULL 值。
• 计算字段返回类型为整型。

--返回组中的项数
select COUNT(score) from SC

--当项数大于2^23-1时使用COUNT_BIG()
select COUNT_BIG(score) from SC

22. LEN() 函数

• 返回指定字符串表达式的字符数。
• 其中不包含尾随空格。
• 若要返回用于表示表达式的字节数，请使用 DATALENGTH()

--查询字符串表达式的字符数
select LEN(Cname) as len_grade from Course

--查询字符串表达式的字节数
select DATALENGTH(Cname) as len_grade from Course

23. 随机数的产生

在查询分析器中执行：select rand()，可以随机的得到一个小数，类似：0.836393773069793，这种随机数在实用的过程中使用的并不多，一般我们会使用到的是随机整数。

select floor(rand()*N)

select ceiling(rand()*N)

• FLOOR 函数返回小于或等于所给数字表达式的最大整数。向下取整
• CEILING 函数返回大于或等于所给数字表达式的最小整数。向上取整

例如：floor(9.1782) = 9 ，ceiling(9.1782) = 10

--向下取整
select FLOOR(rand()*10)

--向上取整
select CEILING(rand()*10)

24. 获取时间

24.1GETDATE()

select getdate() 返回当前数据库系统时间值，返回值的类型为 datetime。

24.2 GETUTCDATE()

select getutcdate() 返回当前国际标准时间值，返回值的类型为 datetime。

25. CONVERT() 函数

• CONVERT() 函数是把日期转换为新数据类型的通用函数。
• CONVERT() 函数可以用不同的格式显示日期/时间数据。

CONVERT() 函数详情网页链接：https://www.w3school.com.cn/sql/func_convert.asp

--日期格式转换
select CONVERT(varchar(10),GETDATE(),110)

语法：

CONVERT(data_type(length),data_to_be_converted,style)

• data_type(length) 规定目标数据类型（带有可选的长度）。

• data_to_be_converted 含有需要转换的值。

• style 规定日期/时间的输出格式。

26. DATEDIFF() 函数

DATEDIFF() 函数返回两个日期之间的时间。

语法：

DATEDIFF(datepart,startdate,enddate)

startdate 和 enddate 参数是合法的日期表达式。

datepart参数见网页链接：

DATEDIFF() 函数详情网页链接：https://www.w3school.com.cn/sql/func_datediff.asp

--返回两个日期之间的时间
select DATEDIFF(DAY,'2021-01-01','2021-01-03')

27. DATEADD() 函数

DATEADD() 函数在日期中添加或减去指定的时间间隔。

语法：

DATEADD(datepart,number,date)

• date 参数是合法的日期表达式。

• number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。

datepart 参数见网页链接：

DATEADD() 函数详情网页链接：https://www.runoob.com/sql/func-dateadd.html

--在日期中添加或减去指定的时间间隔
select DATEADD(DAY,5,'2021-01-01')

--附带日期转换
select CONVERT(varchar(10),DATEADD(DAY,5,'2021-01-01'),120)

28. DATEPART() 函数

• DATEPART() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
• 返回类型是一个 int 整型。

语法：

DATEPART(datepart,date)

date 参数是合法的日期表达式。

datepart 参数见网页链接：

DATEPART() 函数详情网页链接：https://www.w3school.com.cn/sql/func_datepart.asp

--获取当前时间的年份
select DATEPART(YEAR,getdate())

29. DATENAME() 函数

• DATENAME() 函数同样是用于返回日期/时间的单独部分。
• 返回数型是一个 varchar 型。

• year(GETDATE())

• month(GETDATE())

• day(GETDATE())

上述三个是获取服务器当前日期的单独部分。返回值 int 类型。

--获取当前时间的日期
select DATEPART(DD,getdate())

--获取当前服务器时间的年份
select year(GETDATE())
--获取当前服务器时间的月份
select month(GETDATE())
--获取当前服务器时间的日期天数
select day(GETDATE())

30. CHARINDEX() 函数

• 返回字符或者字符串在另一个字符串中的起始位置。
• 返回一个整数，返回的整数是要找的字符串在被找的字符串中的位置。假如没有找到要找的字符串，那么函数返回0。

--在第二个字符串中查询第一个字符串的位置
select CHARINDEX('cc','aabbccdd')

--从第6位开始查找，第三个参数可省略
select CHARINDEX('cc','aabbccdd',6)

31. PATINDEX() 函数

• 返回字符或者字符串在另一个字符串或者表达式中的起始位置，PATINDEX 函数支持搜索字符串中使用通配符，这使 PATINDEX 函数对于变化的搜索字符串很有价值。

--在第二个字符串中查询第一个字符串的位置
select PATINDEX('%cc%','aabbccdd')

--查询第二个字符串是否是以aa开头
select PATINDEX('aa%','aabbccdd')

--查询第二个字符串是否是以dd结尾
select PATINDEX('%dd','aabbccdd')

32. STUFF() 函数

• STUFF() 函数用于删除指定长度的字符，并可以在指定的起点处插入另一组字符。

• 返回类型是一个字符串型。

语法：

stuff(列名,开始位置,长度,替代字符串)

--用ee代替
select STUFF('aabbccdd',5,2,'eeff')

--删除，即用空代替
select STUFF('aabbccdd',5,2,'')

33. SUBSTRING() 函数

SUBSTRING() 函数用于截取指定长度的字符串。

语法：

SUBSTRING(字符串,开始位置,截取长度)

--从字符串的第三位开始截取5位，并返回截取下的数据
select SUBSTRING('abcdefghijk',3,5)

34. LEFT() 函数

LEFT() 返回字符串中从左边开始指定个数的字符。

--从字符串左边开始，截取3个字符
select LEFT('abcdef',3)

35. RIGHT() 函数

RIGHT() 返回字符串从右边开始指定个数的字符。

--从字符串右边开始，截取4个字符
select RIGHT('abcdef',4)

36. LTRIM() 函数

LTRIM() 删除起始空格后返回字符表达式。

--删除起始空格后返回字符表达式
select LTRIM('   dashhsa sad  ')

37. RTTIM() 函数

RTTIM() 截断所有尾随空格后返回一个字符串。

--截断所有尾随空格后返回一个字符串
select RTRIM('   dashhsa sad  ')

--删除两边的空格
select RTRIM(LTRIM('   dashhsa sad  '))

38. UPPER() 函数

UPPER() 函数返回小写字符数据转换为大写的字符表达式。

--将字符串字母全部转换为大写
select UPPER('abcDEfG')

39. LOWER() 函数

LOWER() 函数返回大写字符数据转换为小写的字符表达式。

--将字符串字母全部转换为小写
select LOWER('abcDEfG')

40. REPLACE() 函数

用另一个字符串值替换出现的所有指定字符串值。

语法：

REPLACE(字符串,要被替换的字符串,用于替换的字符串)

--将abcdefg字符串中的cde替换为xxx
select REPLACE('abcdefgcdeasdcde','cde','xxx')

41. REPLICATE() 函数

REPLICATE() 函数以指定的次数重复字符表达式。

语法：

REPLICATE(要重复的字符串,要重复的次数)

--将abc重复输出5次
select REPLICATE('abc',5)

42. SPACE() 函数

SPACE() 函数用来返回指定个数的空格表达式。

--输出5个空格
select SPACE(5)   

--拼接
select 'a'+SPACE(5)+'a'

43. REVERSE() 函数

REVERSE() 函数用于倒置字符串中的各个字符的位置。

--倒置123456
select REVERSE('123456')

44. CAST() 函数

CAST() 函数用于将某种数据类型的表达式显式转换为另一种数据类型。

--把123转换为varchar类型
select CAST(123 as varchar(10))

--拼接
select 'abc' + CAST(1 as varchar(5))

--把12.5转换为int型
select CAST(12.5 as int)

--把12.5转换为两位小数
select CAST(12.5 as decimal(18,2))

--把字符串转换为日期
select CAST('2021-01-01' as datetime)

45. CASE() 函数

CASE() 函数是一种简单的条件判断转换的一个函数，它会把满足条件的表达式转换为对应的结果。

CASE具有两种格式：简单 CASE 函数和 CASE 搜索函数。

--CASE 搜索函数
select *,
case when score >= 90 then '优'
when score >= 80 and score < 90 then '良'
when score >= 70 and score < 80 then '中'
else '差' 
end
from SC

--CASE 搜索函数
--和in混合使用
select *,
case when score in(90.0) then '优'
when score >= 80 and score < 90 then '良'
when score >= 70 and score < 80 then '中'
else '差' end
from SC

--简单 CASE 函数
select *,
case C# when '01' then '语文'
when '02' then '数学'
else '英语'
end
from SC

46. ISNULL() 函数

isnull(参数1，参数2)，判断参数1是否为NULL，如果是，返回参数2，否则返回参数1。

--返回helloword字符串
select ISNULL(null,'helloword')

--返回 空串
select ISNULL('','helloword')