基于sqlserver的数据库复习

20220711 -20220724 数据库复习 + B站视频（基于sqlserver）

边看视频边随手记录的，可读性可能不太高，自用吧。

一、

1、为何要连接到数据库：数据库软件不是数据库，我们只是借助可视化的客户端工具（oracle，mysql，sqlserver）去查询和操作数据库。

2、为何一些命令不可以一起执行，一些却可以一起执行？->批处理

3、对内存数据操作是编程语言的强项，对硬盘数据操作是数据库的强项 -> 数据结构和数据库的区别；编程语言和数据库操作的区别

4、数据库是如何存储、操作、显示数据的？

存储：字段 记录 表 约束（主键 外键 唯一件 非空 check default 触发器）

操作： insert update delete T-SQL 存储过程 函数 触发器

显示：select ***

---

二、

0、建库：建库时会生成2个文件 ：(库).mdf & (库_log).Ldf

分离：将某库迁移到另一个数据库中时，删除该库，并把数据mdf拿出。（需要断开数据库连接）

附加：把对应的mdf加进新库中，连表带数据全都来了。

1、字段、记录

数据库表的列：字段/属性/列

数据库表的行：记录/元组

数据库表：记录的集合

2、建表删表

create table xxx ()

drop table xxx

3、约束：对属性的规定和限制

主键约束（PK）：可以唯一表示某一行记录，不可为空。主键只能有一个（一个字段或几个字段的组合）。

identity：自增 ID int primary key identity(1,1) 从1开始步长为1，无需用户给它修饰的字段（一般是主键）赋值。

如果需要人工介入自增的赋值，set identity_insert 完整表名 On

外键外键（FK）：通过外键约束从语法上保证事物所关联的其他事物一定存在。外键也可能来自本表（比如：同一员工ID可以指员工ID或直属领导ID）

​ 设计-关系-（有外键的表叫外键表，外键所属的表叫主键表。）

​ foreign key references 表名(表主键)

check约束：保证事物属性的取值在合法的范围之内。 xxx int check (xxx>=1000 and xxx<=8000),

default约束：保证事物属性一定会有一个值。 xxx varchar(50) default ‘男’，

unique约束：保证了事物属性的取值不允许重复，但允许其中有且只有一个为null。 xxx varchar(50) unique

not null(可能不算是约束，但是一种显示)：default相当于not null+赋默认值

主键和unique键的区别：

我的理解：主键唯一确定某一行，unique的列是唯一值。维度不同，前者是跟整张表比较，后者是跟该unique列里比较。

注意：不要用业务逻辑字段作为主键，一般用ID，自增主键。业务上的"主键"可以作为唯一键。

4、关系型数据库：用表来体现一对一、一对多、多对多的关系

一对一：挑一个表作为外键表，另一个就是主键表

一对多：在多表里加一个外键

多对多：需要加一个关系表，记录每一组对应关系。

---

三、

0、查询：select

计算列 select sal*12 as “年薪” from emp； 中文别名最好要用双引号（Oracle不允许用单引号）

distinct select distinct name from emp; 查询独特的列值（去重）

between 包括等于 等价于 >= &&<=

in in (a,b,c,…) 等价于 = a or =b or =c or…

top 取结果集的头几行 用于分页 select top x * from table (Oracle 用的是rownum)

前百分之几条记录 select top 15 percent * from table

null 不参与数学运算 为空 is null 非空 is not null

isNull(xxxx,0)函数：如果为空则赋值为0

order by : order by xxx (asc 默认)/desc

可以order by多字段,排序优先级按照字段顺序。如果每个字段升降序不同需要在每个字段后标注：

order by a desc ,b （a降序，如a相同则b升序）

order by a, b desc (a升序，如a相同则b降序)

模糊查询

like ‘%aaa%’ ----含有aaa的 xxxaaabbxx

like ‘aaa%’ —aaa开头的 aaaxxx

like ‘%aaa’ ----aaa结尾的 dxdxdxdxaaa

通配符%：任意0个或多个字符

通配符_:任意1个字符

like ‘_A%’ —第二个字符是A的

通配符[a-f]:a到f范围中的一个字符

like ‘_[a-f]%’ —第二个字符是a-f范围中的

通配符[a,f]:a或f

like ‘_[a,f]%’ —第二个字符是a或f的

通配符[ ^a-f]:第二个字符非a到f的一个字符

如果模糊查询内容包括下划线或百分号的需要转义：

like ‘%%%’ escape ‘’ 【% 这里代表百分号 而非通配符】—字段中包含百分号的

like ‘%\ _%’ escape ‘’ 【\ _这里代表下划线 而非通配符】 —字符中包括下划线的

聚合函数

函数的分类：单行函数——每一行返回一个值 ； 多行函数——多行返回一个值，聚合函数隶属于多行函数。

单行函数举例：select lower(name) from table;

多行函数举例：select max(salary) from table

聚合函数的分类：max() min() avg() count()

count():

select count(*) from table; —返回行数

select count(deptNo) from table; —返回非空的部门记录数

select count(distinct deptNo) from table; —返回非空部门种类数

group by 分组 group by a,b 先以a分组，a相同时以b分组 没有group by的时候用到聚合函数则代表以全体为一个分组

having 过滤中有聚合函数就不能用where要用having 并且和where放置位置不同 having在group by之后 where在group by之前

连接查询（分类：内连接、外连接、完全连接、交叉连接、自连接、联合）

内连接：

select … from A,B 笛卡尔积—— 结果集行数=A表行数*B表行数 ，结果集列数=A列数+B列数

select * from A,B where A.xx = B.yy 从结果上等价于 select * from A join B on A.xx = B.yy 推荐使用后者

A B互换位置结果一样：select * from A join B on A.xx=B.yy 等价于 select * from B join A on B.yy=A.xx

嵌套查询

内连接：（inner) join

外连接：left (outer) join，对比内连接，外连接会产生无效数据（没有联系的）。左外连接：没有联系的左表照常显示右表用null填补。一般查询的主要对象作为左表，附带内容为右表。左外连接的结果集行数>=左表行数（如果左表一行对应右表有多行就会显示多行），右外连接类似。

完全连接：full join，相当于左外连接+右外连接，左右表都有可能出现null填补。

自连接：一张表自己和自己连接查询。select * from table A join table B on

联合：union，纵向的方式连接表中的数据。条件：联合的几部分输出字段个数要一致、类型至少要兼容。

分页查询：select top n *from table A where A_id not in (select top (m-1)*n A_id from table)

---

四、视图

为何需要视图：有一段常用的可复用的查询逻辑可以用视图来当做一个封装好的临时表，不同场景用到这段逻辑可以在此基础上进行发挥。

什么是视图：一段查询的sql的结果集组成的虚拟表。

视图的格式：

create view viewName as select …(查询的sql语句) 点击执行就可以创建视图。

优点：

简化查询、可复用性up&避免了代码的冗余，增加数据的保密性。

其中，保密性：可能某表涉及敏感信息，此表对用户a不可见。在视图中查询了一些对a可见该表里不敏感可对外的字段，此视图可设置对a可见。这样a就可以查看到表里一些a可见的数据了。

缺点：

视图和涉及到的表是相互独立的关系，如果删了某表，该视图仍然存在。增加了数据库维护的成本；只是简化查询，并不能加快查询速度。

注意：

创建视图的sql必须为所有的计算列起别名。avg(xx) as xxxx

视图是虚拟表，非物理表。

---

五、事务

保证数据的合理性和并发处理的能力，避免数据处于不合理的中间状态，利用事务可时间多用户对共享资源的同时访问。

一个事务要么全部完成，要么全部回滚。

事务也是通过锁来的解决问题的。

每个sql语句都是一个事务，成功执行就自动提交，报错就自动回滚。

事务四大特性：原子性、一致性、隔离性、持久性。

原子性：事务是一个完整的操作，要么都执行要么都不执行。

一致性：事务完成时数据必须处于一直状态，转账：A减B加。数据不可处于中间状态（A减了B未加）

隔离性：当前事务与其他未完成的事务是隔离的。在不同隔离级别下事务的读取操作可以得到的结果是不同的。

持久性：事务完成后对数据库的修改被永久保持，事务日志可以保持事务的永久性。

---

触发器、存储过程等等未讲到。未完待续。