SQL Server数据库基础知识汇总

1) 数据库可以结构化存储大量的数据信息，方便用户进行有效的检索和访问。
数据库可以对数据进行分类保存，并且能够提供快速的查询。例如，我们平时使用百度搜索内容时，百度也是基于数据库和数据分类技术来达到快速搜索的目的。

2) 数据库可以有效地保持数据信息的一致性、完整性、降低数据冗余。
可以很好地保证数据有效、不被破坏，而且数据库自身有避免重复数据的功能，以此来降低数据的冗余。

3) 数据库可以满足应用的共享和安全方面的要求，把数据放在数据库中在很多情况下也是出于安全的考虑。
例如，如果把所有员工信息和工资数据都放在磁盘文件上，则工资的保密性就无从谈起。如果把员工信息和工资数据放在数据库中，就可以只允许查询和修改员工信息，而工资信息只允许指定人（如财务人员）查看，从而保证数据的安全性。

4) 数据库技术能够方便智能化地分析，产生新的有用信息。
例如，超市中把物品销售信息保存在数据库中，每个月销售情况的排名决定了下半月的进货数量。数据库查询的结果实际上产生了新的数据信息。
数据挖掘、联机分析等技术近年来发展非常快，其核心意义在于从一堆数据中分析出有用的信息。

简单理解：安全，高效，便捷

1.3 数据库发展历史

1 层次数据库和网状数据库技术阶段，使用指针来表示数据之间的联系

2 关系型数据库技术阶段，代表的DBMS有：Oracle、DB2、MySQL、SQL Server、SyBase等

3 后关系型数据库技术阶段，由于关系型数据库中存在数据模型、性能、拓展伸缩性差的缺点，所以出现了ORDBMS（面向对象数据库技术），NoSQL（非关系数据库）。

1.4 数据库分类

一般分为关系型数据库和非关系数据库（NOSQL)

关系型数据库常见的有Sqlserver,Mysql,Sqlite,Oracle等

非关系型数据库（NOSQL）有redis、MongoDB,HBase、BigTable、CouchDB、Neo4J等

1.5 DBMS数据库管理系统

数据库通常离不开完备的数据库软件程序，也就是数据库管理系统 (DBMS)。

DBMS 充当数据库与其用户或程序之间的接口，允许用户检索、更新和管理信息的组织和优化方式。此外，DBMS 还有助于监督和控制数据库，提供各种管理操作，例如性能监视、调优、备份和恢复。

常见的数据库软件或 DBMS 有 MySQL、Microsoft Access、Microsoft SQL Server、FileMaker Pro、Oracle Database 和 dBASE。我们常说的数据库，大多都是说的DBMS。

二、 SQL(Structured Query Language)

2.1 定义

结构化查询语句，是一项标准，适用于关系型数据库，虽说是一项标准，但是不同数据库在一些地方还是由自己的sql标准，好在大多还是遵循SQL中的标准。

2.2 SQL分类

1 DCL 数据控制语言，一般使用GRANT或REVOKE关键字获得许可，确定单个用户或用户组对数据库对象的对象的访问

2 DDL 数据定义语言，使用CREATE,DROP,ALTER,操作数据库，表，视图，索引，函数，存储过程，触发器等

3 DML数据操作语言，使用INSERT,UPDATE,DALETE,对数据库数据进行操作

4 DQL数据查询语言，使用SELECT对数据进行查询操作，最常用

5 TPL 事务处理语言，它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

6 CCL 指针控制语言，DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作

2.3 SQL与T-SQL

SQL与T-SQL,SQL是T-SQL都是语言，不过T-SQL是SQL在Microsoft SQL Server上的增强版，他允许用户使用变量，申明常量，运行函数，储存过程等进行数据库编程

编写SQL须知：不同于C#，SQL是不区分大小写，没有双引号的使用

三、SqlServer下载与安装

1、SqlServer2019（下载Developer版本）
下载路径：https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads

2、SQL Server Management Studio（数据库管理工具）
下载路径：https://learn.microsoft.com/zh-cn/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

四、数据库表各类操作

1 创建数据库登录用户

--创建数据库登录用户（仅登录权限）
exec sp_addlogin 'username','password'

--赋予登录用户连接数据的权限
exec sp_addgrantdbaccess 'username','dbname'  

--注：文字涉及到username，password,dbname等均为可以自定义的内容，可以自行修改

2 使用DCL赋予数据库用户操作数据表的权限

--赋予用户创建数据表的权限
grant create table to uesrname

--赋予用户删除数据表的权限
grant drop table to username

--同时赋予数据表创建和删除的权限
grant create,drop table to username

--赋予用户在数据表中插入数据的权限
grant insert on table to username

--赋予用户在数据表中更新数据的权限
grant update on table to username

--赋予用户在数据表中删除数据的权限
grant delete on table to username

--赋予用户在数据表中查询数据的权限
grant select on table to username

--同时赋予多个数据表操作权限
grant insert,update,delete,select on table to username

3 使用DDL创建数据库表等对象

3.1创建/删除数据库

--指定默认连接的数据
use [dbname]

--批处理,应将当前的SQL批处理语句发送给SQLServer的信号
go

--创建数据库(默认方式)
create database [dbname]

--创建数据库（指定存储数据库文件和日志文件的方式）
create database [dbname]
on
(
name='db_name',--数据库文件名称
filename='d:\xx\db_name_data.mdf',--目录
size=1mb,--初始大小
maxsize=10mb,--最大限制
filegrowth=10%--增长方式，每次增长10%
)log on
(
name='dblog_name',--数据库日志文件名称
filename='d:\xx\dblog_name_log.ldf',--目录
size=1mb,--初始大小
maxsize=10mb,--最大限制
filegrowth=10%--增长方式，每次增长10%
); 

--删除数据库
drop database [daname]

--一般平常可以在创建数据库之前判断一下，具体情况具体操作
if exists (select * from sys.databases where name='dbname')
drop database [daname]

3.2创建/删除/修改数据表

--创建数据表
create table [tbname]
(
id int not null,--[字段名] 数据类型 约束条件
name varchar(50) unique,--[字段名] 数据类型 约束条件
age tinyint,
);

--建表的时候，一般格式是字段名，数据类型，约束条件，
--但是大多数只会定义字段名和数据类型，最多定义非空约束，
--关于约束都是单独定义和维护，后文中会讲解

--删除数据表
drop table [tbname]

--修改表，添加一列
alter table [tbname]
add [columnname] int not null--同理add 后面接上 字段名，数据类型，约束条件（可单独添加）

--修改表，修改一列的数据类型
alter table [tbname]
alter column [columnname] varchar(50) --这里是将上面的int 修改为varcahr

--修改表，删除一列
alter table [tbname]
drop column [columnname]

3.3了解数据类型

--数据类型SQL Server 中分为4个大类
--【string类】
char(n),varchar(n),text(n),
nchar(n),nvarchar(n),ntext(n),
bit,binary(n),varbinary,image

--【number类】
tinyint,smallint,int,bigint,
decimal(p,s),numeric(p,s),
smallmoney,money,
float(n),real(n)

--【date类】
datetime,datetime2,smalldatetime,
date,time,datetimeoffset,timestamp

--【其他】：
sql_variant,uniqueidentifier,xml,cursor,table

--注意，varchar如果不填写长度，默认长度为1
--这里大概了解，具体还是需要落实到使用中去熟悉各类数据的特性

3.4约束条件

--数据表中保存的数据，数据必须满足数据完整性的要求；
--而为了实现数据完整性就需要用约束条件来实现

--【数据完整性】
--1 实体完整性，规定数据表中的数据必须是唯一的实体
--2 值（域）完整性，是指数据库表中的列必须满足某种特定的数据类型或约束
--3 参照完整性，是指两个表的主关键字和外关键字的数据应对应一致
--4 自定义完整性，是针对某个特定关系数据库的约束条件，
--它反映某一具体应用所涉及的数据必须满足的语义要求

--【约束条件】记忆口诀：NPC服（F）毒(DU)
N=>not null        --非空约束
P=>primary key     --主键约束=>简写PK
C=>check           --检查约束=>简写CK
F=>foreign key     --外键约束=>简写PK
D=>default         --默认约束=>简写DF
U=>unique          --唯一约束=>简写UQ

--约束名命名规则
[约束简写_表名_列名]，如[pk_user_id],表示是为user表中id这一列添加了主键

--添加主键约束（示例）
alter table [user] 
add constraint [pk_user_id] primary key(id);

--添加外键约束（示例）
alter table [user] 
add constraint [fk_user_stuId] foreign key(stuId) references [stuTb] (id);

--添加检查约束
alter table [user]
add constraint [ck_user_age] check(age>10 and age<20)

--添加唯一约束
alter table [user]
add constraint [uq_user_logid] unique(logid)

--添加默认约束
alter table [user]
add constraint [df_user_sex] default '保密' for sex

--非空约束无法添加，建表的时候没有创建非空约束，可以在编辑界面修改或者通过以下命令
alter table [user]
alter column id int not null--通过修改列去添加

--删除约束
drop constraint [constraintname]

--【注意】
--1 约束只有创建和删除，没有修改
--2 约束最好单独定义，单独定义有利于后期的维护和扩展
--3 不在建表的时候添加过多的约束条件

4 数据表数据的操作（INSERT,UPDATE,DELETE）

--插入数据
insert into [tbname] values ('value1','value2',...);

--插入指定列的数据
insert into [tbname](column1,column2,...)values('value1','value2',...)

--更新数据
update [tbname] set columnname=value where ....
update [tbname] set age=10, sex='保密' where id=1;--示例

--删除数据
delete from [tbname] where ...--from 可省略

--更新和删除语句都会配合where条件，具体where的使用会在select中说明


--复制新表(直接将userinfo整个表复制一份命名为userinfo2)
--一般用于测试语句，但是又不想线上数据收到影响
select * into userinfo2 from userinfo

演变：
select * from userinfo --查出所有数据
select * into userinfo2 from userinfo --将userinfo中查到的所有数据插入到userinfo2
--使用的时候记住，查出来，然后into至对应的表即可

--两个表结构相同的表，需要将表1的数据插入到表2中
insert into userinfo2 values select * from userinfo--values 可省略

5 数据表数据的查询操作

5.1 基本查询

--查询所有数据（示例）
select * from userinfo--查询所有userinfo表的信息

--查询指定列的信息（示例）
select [id],[name],[sex] from userinfo --查询id,name,sex 三列的信息

5.2 关键字的查询应用

--指定别名（均以userinfo示例）
--1 使用 =
select [编号]=[id] from userinfo

--2 省略 =
select [id] [编号] from userinfo

--3 使用as (推荐使用)
select [id] as [编号]，name from userinfo

-------------------------------------------------------------

--as 关键字的使用
--1 申明变量
declare @name as varchar(50)  --这里的as 可以省略

--2 指定别名
select [id] as [编号]，name from userinfo--指定列别名

select * from userinfo as uinfo--指定表别名

select * from (select id,name from userinfo) as uuinfo--为结果集指定别名

--3 一次指定多列别名(注意要字段一一对应号)
select * from (select id,name,sex from userinfo as uinfo(编号,姓名,性别))  



--where 关键字的使用
--where 后可让用户自定义各种条件，条件得到true,获取，条件得到false,排除；
select * from userinfo where id=1;

--top 获取指定条数的数据
select top 1 * from userinfo
select top 1 [id],[name] from userinfo--获取指定条数指定列

--distinct 去重复
select distinct(name) from userinfo 

--in /not in  包括/不包括
select * from userinfo where id in (1,2,3)--如同查询 where id=1 or id=2 or id =3
select * from userinfo where id not in (1,2,3)

--between...and... 指定范围内
select * from userinfo where age between 10 and 20




--like 模糊查询
--模糊查询通常与通配符 % 占位符_ 选择符[] 排除符[^] 使用
--% 通配符
select * from userinfo where name like '张%'--查询所有姓张的
select * from userinfo where name like '%和%'--查询名称中含和的
select * from userinfo where name like '%军'--查询以军结尾的人名

--占位符_
select * from userinfo where name like '_欢' --查以欢结尾，且只有两个字的名字

--选择符[]
select * from userinfo where name like '[张王李]%' --查询姓张王李中的任何一个

--排除符[^]
select * from userinfo where name like '[^张王李]%'--查询排除张王李的人员信息

--is null/is not null 是否为空，用于对数据null值的判断
--数据库中只能用 is null 判断空值数据，is not null同理
select * from userinfo where name is null 

--order by [columnname] asc/desc 按照某一列，或者多列排序
select * from userinfo order by sex,age asc --按照性别和姓名排序 

--group by--分组，一般配合聚合函数使用
select sex, count(*) as 人数 from userinfo group by sex--统计男女分别有多少人

--having，对分组进行筛选
--筛选总分大于200分的学生的名字
select name,sum(score) as 总分 from userinfo group by name having sum(score)>200

--[注意]having 和where 都是用来筛选用的 having 是筛选组 而where是筛选记录 ; 
--用having就一定要和group by连用，用group by不一有having
--SQL中增加having 子句的原因是，where关键字无法与聚合函数一起使用

--case when..then判断
--用法1，使用case when 判断分数，新增一列 等级

select id ,name,course,score,
              case when score >=90 then '优秀'
                   when score <90 and score>60 then '及格'
                   else '不及格'
              end as 评级 
from userinfo order by score asc

--注意case when ... then...end是配套使用的

--用法2,将指定列的值进行判断，新增一列 文理方向
select id ,name ,course,score,
               case course when '数学' then '理科'
                           when '语文' then '文科'
                           else '不知道'
               end as '文理方向'
from userinfo order by score asc

with 的使用
with tempuserinfo as (select * from userinfo where age>50)
select * from tempuserinfo 
--with的作用，可以将查询结果作为一个临时结果集，提供查询

5.3 系统函数（重点聚合函数）

聚合函数

--聚合函数，都是用于统计数值使用，相对使用起来比较简单
--1 count()  统计数量
--2 sum()    统计和
--3 avg()    统计平均值
--4 max()    统计最高分
--5 min()    统计最低分

select count(*),sum(score),avg(score),max(score),min(score) from userinfo

NEWID生成唯一标识

#newid 无参数
#返回一个GUID(全局唯一表示符）值
例如：select newid
返回：2E6861EF-F4DB-4FFE-86EB-637482FE982J2

--将id为1 的用户信息id 更改为唯一标识
update userinfo set id =newid() where id =1;

isnumeric（任意表达式)

#isnumeric（任意表达式)
#判断表达式是否为数值类型或者是否可以转换成数值。
#是：返回1，不是：返回0
select isnumeric(11)             --返回 1
select isnumeric('123rr')        --返回 0 
select isnumeric('123')          --返回 1

isdate(任意表达式)

#isdate（任意表达式）
#确定输入表达式是否为有效目期或可转成有效的日期:
#是：返回1，不是：返回0
例如：
setect isdate(getdate())        --返回 1
select isdate('2013-01-02')     --返回 1
select isdate('198')            --返回 0

ISNULL函数用法

ISNULL(value1,value2) 如果value1为空，则将value2的值返回，不为空，返回自己的值

--实例，查询人员表，将性别为NULL的均赋值为保密
select name,isnull(sex,'保密') from userinfo

--注意：
--value1 :一般是字段名
--value1 要和value2的数据类型一致

ROW_NUMBER() 排名函数

row_number()就是给每一条记录分配一个数字，从1 开始递增，用于生产序号

--示例1 
row_number() over (order by createtime desc)
#根据创建时间降序，再为降序以后的每条记录返回一个序号

--示例2 
row_number() over (partition by sex order by createtime desc)
#根据sex分组，在分组内部根据createtime排序，
#而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

--示例3select
select *,row_number() over (order by createtime desc) as 序号 from userinfo
#将userinfo 表格查询结果集中新增一列序号，并且该结果集按照创建时间降序排列

--示例4
select *,row_number() over (partition by sex order by createtime desc) as 序号 
from userinfo
#将userinfo首先按照性别分组，然后按照时间排序为结果集新增一序号列
#注意：分组后，每组的序号均是从1开始的

OFFSET/FETCH NEXT

1 在在SQL Server 2012版本中，T-SQL在Order By子句中新增 Offset-Fetch子句
2 用于从有序的结果集中，跳过一定数量的数据行，获取指定数量的数据行，从而达到数据行分页的目的
3 分页效率比row_number()高
4 Offset子句必须在Order By 子句以后执行，Fetch子句必须在Offset子句以后执行；

--示例
--使用Offset跳过指定数目的数据行
select * from userinfo order by id offset 2 rows
--查询用户名中按照id 排序，跳过前2条记录的所有数据

--使用Offset-Fetch跳过指定数目的数据行以后，获取指定数目的数据行
select * from userinfo order by id offset 10 rows  fetch next 10 rows only
--查询用户表中按照id排序，跳过前10条数据，获取后面的10条数据

5.4 子查询

--什么叫子查询
--在查询语句中的查询语句，称之为子查询

--子查询分别两种
--1 嵌套子查询，位于form 前面
select *,（select max（[score]） from [scoretb]） from [student];


--相关子查询，位于where后面
--查询成绩表，只要前5名中的3,4,5
select top 3 * from [scoretb] where [stuid] not in 
(select top 2 [stuid] from [scoretb] order by [score] desc) 
order by [score] desc;

--all(),any的使用
--all()和any()基本都用于子查询中，其作用相当于某些聚合函数的作用
--all相当于 max; any 相当于 min
--all和any的括号中只能放子查询语句
--使用all
select * from [user1] where [age] >all (select [age] from [user2] );
--使用max
select * from [user1] where [age] >(select max(age) from [user2])

--exists 和 not exists
--1 exists 和not exixts 后面括号中放 子查询语句，
--用于检测子查询语句的查询结果是否存在，如果存在返回true,反之，false
select * from user1 where 
exists(select * from user1 where stu_name='123');


--where 1=1 多用于sql 拼接时的占位
select * from user1 where 1=1 --后面接其他条件

5.5 分页实例

#【分页方式1】必须带有主键Id，且主键Id是标识列，必须是自增的

declare @pagesize int; 
select @pagesize=10;--每页10条

declare @pageindex int;
select @pageindex=3;--当前页码 

select top (@pagesize) * from userinfo where id not in
( select top (paesize*(paeindex-1) id from userinfo order by id )
order by id

#【分页方式2】没有主键的时候，就需要使用row_number将表中所有的数据加上一列序号
# 然后按照该序号排序，进行分页（只支持2005版本以上的）

declare @pagesize int; 
select @pagesize=10;

declare @pageindex int; 
select @pageindex=3;

select top (@pagesize) * from
( select row_number() over(order by id) as rownumber,* from userinfo ) as temptb
where rownumber>((@pagesize)*((@pageindex)-1))


#【分页方式3】 要求必须在sq1server2012版本之后方可支持

declare @pagesize int: 
select @pagesize=10;

declare @pageindex int; 
select @pageindex=10:

select * from userinfo order by id  offset(@pagesize*(@pageindex-1)) rows 
fetch next (@pagesize) rows only

5.5 行转列，列转行

#【行转列】
#比如有这样的一张表，记录的是学生所有学课的分数
#数据列是 id name course score ，课程有语文，数学，物理(学课有限)
#正常查询这张表，则是可以根据name 排序，但是无法将同一个学习的成绩并到一行展示
#这时候就需要用到行转列

select name,
isnu11(sum(case course when '语文' then score end),0) as '语文', 
isnu11(sum(case course when '数学' then score end),0) as '数学', 
isnu11(sum(case course when '物理' then score end),0) as '物理', 
from score group by name

--通过case when ..then 的条件进行判断和赋值，创建学课的新列，
--再利用group by 和sum(),去除 多余无用的数据


#【列转行】
# 比如有这样的一张表，记录的是学生所有学课的分数
#数据列是 id name yuwen_score shuxue_score wuli_score  
#如果我们想查询各个学课的成绩，那么就需要列转行（与上面需求相反）

with temptb as
(
select id,[name],course='语文',score=[yuwen_score] from score
union a11
select id,[name],course='数学',score=[shuxue_score] from score
union a11
select id,[name],course='物理',score=[wuli_score] from score
)

select * from temptb where score<>0 order by name desc

5.6 多表查询

1 联合查询（又称简易内连接），多张表同时操作，内存消耗大，但是速度快

--示例
select * from student as a,score as b where a.id=b.stuid

--查询指定列
 select id,name,score,class from student as a,score as b where a.id=b.stuid

--student 显示所有，score显示指定字段
 select student.*,score from student as a,score as b where a.id=b.stuid

2 连接查询,一张张表进行操作，内存消耗少，但是耗时

#【连接查询】

#【内连接】
# inner join ...on....  无主从之分，只显示连接表中有关联的信息
# inner join ...on... 可以省略inner 直接使用join ...on...
select * from student inner join score on student.id=score.stuid

#外连接-左连接  有主表从表之分，主表显示所有信息，从表显示关联信息;左边主表
select * from student left join score on student.id=score.stuid

#外连接-右连接 同理，右边主表
select * from student right join score on student.id=score.stuid

#全连接，无主从之分，所有的信息均显示，没有的信息使用null 补上
select * from student full outer join score on student.id=score.stuid

3 union/union all

--union/union all
--union 使用的时候默认会去除重复的数据
--union all 不会去除重复的数据
--要求union的表结构需要一一对应
select * from teacher
union
select * from emp

#当有多张表的时候，后面直接接着 使用 union即可
#注意：如果使用union，表1，表2 分别有3条数据，其中有1条重复，那么结果就是5条；
#      如果使用union all 结果就是会6条

4 注意：select 语句执行顺序大致是：

where（数据查询） -> group by（数据编组） -> having（结果过滤）

-> order by（排序）====》最后使用聚合函数将数据结果进行统计

5.7递归查询

#菜单目录表 有id ,menuName,parentId三个字段
--输入id,得出下面的子节点

--案例1
declare @menuid int;
set @menuid=2; --查询菜单id 为2的所有子节点


with Con(Id,MenuName,ParentId,level )as
(
select Id,MenuName,ParentId,level=1 from [menuinfo] WHERE Id=@menuid 
union a11
select a.Id,a.MenuName,a.ParentId,level=level+1 from [menuinfo] as a 
join Con on a.ParentId=con.Id
)
select Id,MenuName,ParentId,level from Con


--由父项递归下级
with cte(Id,MenuName,ParentId)
as
(
--父项
select Id,MenuName,ParentId from menuinfo where ParentId = 450
union all
--递归结果集中的下级
select t.Id,t.ParentId,t.MenuName from menuinfo as t
inner join cte as c on t.ParentId= c.Id
)
select Id,MenuName,ParentId from cte


------------------------------


--由子级递归父项
with cte(Id,MenuName,ParentId)
as
(
--下级父项
select Id,MenuName,ParentId from menuinfo where Id = 450
union all
--递归结果集中的父项
select t.id,t.parentid,t.text from treeview as t
inner join cte as c on t.Id = c.ParentId
)
select Id,MenuName,ParentId from cte

递归查询可参考：https://blog.csdn.net/qq_40640228/article/details/106588313