Sql Sever知识点总结

傷城~ 2022-05-28 02:18 261阅读 0赞

# **1.数据** #

> 描述事物的符号记录。可以是数字、文字、图形、图像等

# **2.数据库的特点** #

## ***(1) 数据结构化*** ##

> 数据库面向全组织,  具有整体的结构化。 描述数据时不仅描述数据本身, 还描述
> 
> 数据之间的联系。

## ***(2) 数据的共享性高,冗余度低,易扩充*** ##

> 数据库从整体角度看待和描述数据,  数据面向整个系统，可被多个用户共享，减
> 
> 少了数据冗余,  节约了存储空间,  避免了数据之间的不一 致性。（数据的不一致
> 
> 性指同一数据不同拷贝的值不一样）**       **

## ***(3) 数据库由数据库管理系统统一管理和控制。*** ##

> 常见的网络DBMS:
> 
> Oracle(甲骨文)公司：Oracle，大型DBMS，市场占有率50%左右。
> 
> IBM公司：DB2，大型DBMS，市场占有率近10%左右。
> 
> MS(微软)公司：SQL Server，中小型DBMS,市场占有率约20%。
> 
> 常见的小型数据库（或称桌面数据库）DBMS：
> 
> MS(微软)公司：Visual Foxpro(VF),Office系列中的Access
> 
> 以前流行过的小型数据库（或称桌面数据库）DBMS：
> 
> Dbase，Foxbase

## ***(4) 数据独立性高。***  ##

> 用户程序与数据库结构相互独立，数据库结构变化不会影响程序。

# **3.DBMS的功能**： #

> （1）数据库的建立与维护
> 
> （2）数据存取操作
> 
> （3）数据库控制*（安全性保护、完整性检查、并发控制、数据库恢复）*

## ***（1）数据库的安全性控制*** ##

>              数据库的安全性是指保护数据库以防止不合法的使用所造成的数据泄露，更
> 
> 改或破坏。即不合法的用户不能使用数据库。

## ***（2）DB的完整性控制***  ##

>               数据库的完整性：合法用户更新数据库时，还有可能因各种原因出错。完整
> 
> 性约束条件确保合法用户对数据库的更新是正确的。

## ***（3）DB安全控制内容*** ##

>   **           **①定义用户及权限，记入数据字典
> 
>              ②合法权限检查

# **4.关系的性质** #

>      （1）列是同质的
> 
>      （2）列必须取原子值
> 
>      （3）列的次序无所谓
> 
>      （4）行的次序无所谓
> 
>      （5）任意两行不能完全相同

# **5.DB逻辑模型的组成要素** #

>       数据机构、数据操作、完整性约束

# **6.关系的查询操作** #

>       选择：选择满足给定条件的元组（行）
> 
>       投影：选择若干属性列组成新的关系
> 
>       连接运算：笛卡尔积中满足一定条件的元组

# **7.关系的完整性约束：** #

>       实体完整性：属性A是关系B的主属性，则不能为空 primary key
> 
>       参照完整性：外码列取值为空或者等于被参照关系中的主码值
> 
> foreign key references
> 
>       用户定义完整性：用户针对具体的数据库定义的约束条件
> 
> check/null/not null/unique/

## ***（1）DBMS完整性控制功能*** ##

>             ①定义：提供定完整性约束条件的机制
> 
>             ②检查：检查用户发出的操作请求是否违背了完整性约束条件
> 
>             ③纠正：发现违背约束时采取一定策略进行处理

## ***（2）违反参照完整性处理策略*** ##

>             ①拒绝执行（默认）
> 
>             ②级联操作（同时删除外键所在表对应的行）
> 
>             ③置空值：②中操作改为将对应的数据置为空

# **8.SQL特点** #

>       语言统一；高度非过程化；面向集合的操作方式；两种使用方式（自含，内嵌）；语
> 
> 言简洁，易学易用

# **9.框架** #

# **10.应用程序的作用：** #

> 应用程序用Java等计算机语言编写。它提供窗口接受用户的要求，根据要求构造SQL命令，将SQL命令（通过网络）发送给数据库服务器。服务器中的DBMS执行SQL命令，完成数据库操作，将查询数据（通过网络）发送给应用程序，应用程序以适当的方式使用数据，完成具体事务的处理。

# **11.数据库文件** #

>         主数据文件（.mdf）：每个DB均有一个，存储数据库中的表和数据
> 
>         日志文件(.ldf)：每个DB均有一个，存储对DB的各种修改信息，是维护数据库完
> 
> 整性的重要工具

# **12.命令汇总：** #

> Create database 库名： 建库
> 
> drop database 库名 ： 删除数据库
> 
> create table 表名（name varchar(20),）：建表(最后一行可不加逗号，括号后边可不加；)
> 
> constraint c1 约束：约束命名
> 
> primary key(学号，班号)：双列主键
> 
> alter table 表名：修改已经建立的表结构
> 
> create index 索引名 on 表名（列明）：索引建立
> 
> drop index 索引名：删除索引
> 
> insert… values…：向表中添加属性行
> 
> update 表名 set 修改数据where 条件：修改行数据
> 
> update 表名 set 修改数据：修改多行数据
> 
> delete from 表名 where 条件：删除数据行
> 
> select … from 表名 where 条件：\*表示全表查询
> 
> order by 列名 asc/desc：对查询结果排序（升序/降序）
> 
> group by 分组标准（列名）：对查询结果进行分组统计
> 
> having 分组筛选条件：对分组结果筛选
> 
> From 表1 inner join 表2on 表1.主码 = 表2.外码 ： 等值内连接
> 
> From 表1 left outer join 表2 on 表1.主码 = 表2.外码 : 等值外链接（左）
> 
> Create view 表名（属性1， …） as … : 建立视图

# **13.数据类型汇总：** #

>      Bit tinyint smallint int
> 
>      Real float
> 
>      Char(8) varchar(20)
> 
>      Smalldatetime(4) datetime(8)

# **14.SQL中表名、列名命名规则：** #

>        （1）区分大小写
> 
>        （2）首字母用字母、汉字、-、@、\#
> 
>        （3）其余字符：字母、汉字、-、@、\#、数字、$
> 
>        （4）不能使用保留字
> 
>        （5）不合规定标识符用\[ \]括起来

# **15.unique(用户定义完整性)与主键约束区别：** #

>        （1）用于非主键一列或者多列，确保数据唯一
> 
>        （2）允许该列我null
> 
>        （3）同一关系可有多个unique但只能有一个主键约束

# **16.索引** #

>        （1）DBMS一般自动为主键列建立索引
> 
>        （2）unique索引，聚簇索引
> 
>        （3）DBMS维护索引

# **17.数据字典** #

> 在DB中建立的各种表的结构、约束、索引结构、视图、用户存取权限等信息均存出来数据字典当中。数据字典以多个表的形式存在，由DBMA管理、维护。

## ***（1）元数据*** ##

>       在数据库系统中，需要create命令创建数据库、表、约束、视用图、用户、角色
> 
> 等。这些内容称为“元数据”，是描述数据库中的数据和数据库环境的的数据，
> 
> 存放在数据库中的数据字典中

# **18.数据库的基本操作** #

> （1）    插入数据行
> 
> （2）    删除数据行
> 
> （3）    修改数据行中数据
> 
> （4）    查询表中需要的数据
> 
> （5）    根据表中数据进行统计

# **19.数据库的更新包括：** #

> 更新时不能违背数据的完整性，特别是参照完整性（foreign key references）
> 
> （1）    向表中插入数据 insert 表名（列明） values（添加值）
> 
> （2）    修改表中数据   update 表名 set 修改数据 where 条件
> 
> （3）    删除表中数据

# **20.单表查询：** #

>      查询（统计）命令的关键词是select，查询结果不在数据库中存储，对数据库中的数据没有任何影响。

## ***（1）查询满足条件的行和列(where)*** ##

>      满足条件：=/<>/>/</!>/!<
> 
>      确定范围：between … and .. / not between … and …
> 
>      确定集合：in / not in
> 
>      模糊查询：列名 like 匹配串（% \_：用单引号引起来）
> 
>      涉及空值的查询：列号 is null
> 
>      多重条件查询：and or
> 
>      消除结果重复行：select distinct 学号 from 选修 where 成绩 > 60

## ***（2）对查询结果进行排序（order by … asc/desc降序）（where之后单独成句）*** ##

>      按某一列排序：order by 列名 asc/desc
> 
>      多列排序：order by 列名1 asc/desc，列名2 asc/desc

## ***（3）查询并计算*** ##

>      查询简单计算：selcet 姓名, 2017-年龄as 出生年份 from 学生
> 
>      数学函数计算:abs(-3) = 3   /  asci(‘d’) = 100 /  ower(2, 3) = 8
> 
>          as     Sqrt(4) = 2    /  sign(-3) = -1     / round(scores/10,2) 保留两位小数
> 
>      字符函数计算(as)：len(‘abc’) = 3 / left(‘abc’, 1) = a / right(‘abc’, 1) = c
> 
>                      Substring(‘abcdef’,2,4) =c / upper(‘ab’) = AB / lower(‘AB’)
> 
>                      Space(3) = 三个空格 /ltrim(‘  test’) =’ test’ / rtrim(‘test ’)
> 
>                      Charindex(‘abcdef’,’cd’,1)= 3
> 
>                      Str(123.456, 8, 1) =‘123.5000’
> 
>                      Char(65) = ‘A’
> 
>      使用日期函数计算：getdate() 返回当前系统时间
> 
>                        Datepart(部分名（year,month,day）,getdate())
> 
>                        Datename(部分名，getdate()) = March(英文显示)
> 
>                        Date(部分名，+3，getdate()) =27+3 = 30      (sql sever 2017无)
> 
>                        Datediff(month,‘2016/3/5’,’2016/5/5’) = 2
> 
>                        Day/month/year(日期) 求指定部分的值
> 
>      常用其他函数：isdate(日期)：判断日期的合法性   cast(表达式，类型)

## ***（4）统计函数处理查询结果*** ##

>                       Count(distinct \*)          统计元组个数
> 
>                       Count(distinct 列名)       统计指定列中值的个数
> 
>                       Sum(distinct 列名)        只算指定列和（数值）
> 
>                       Avg(distinct 列名)         计算指定列的平均值（数值）
> 
>                       Max(列名)               Min(列名)

## ***（5）对查询结果进行分组统计*** ##

>                 Group by 分组标准（列名）
> 
>                 执行步骤：from\\whereà  group  à select à order by
> 
>                 注意：只要select后面有一列使用了统计函数，则对其每一列，要么出现
> 
> 在统计函数中，要么同时出现在group by后面作为分类标准

## ***（6）分组统计结果进行筛选*** ##

>                 Having 筛选条件
> 
> 执行步骤：from\\where à  group  à select à having à order by
> 
> 注意：where作用与基本表或视图，having作用与组

# **21.连接查询** #

##    ***（1）等值（内）连接：*** ##

>                 Where表1.主码 = 表2.外码
> 
>                 From 表1inner join 表2 on 表1.主码 = 表2.外码
> 
>                 多表连接用and

##    ***（2）等值（外）连接：*** ##

>                 From 表1left outer join 表2 on 表1.主码 = 表2.主码
> 
>                 From 表1right outer join 表2 on 表1.主码 = 表2.主码
> 
>                From 表1 full outer join 表2 on 表1.主码 = 表2.主码

##    ***（3）表格别名(自身连接)*** ##

>                 Select \*
> 
>                 From 表1（as） s1, 表2 （as） s2
> 
>                 Where s1.列1 = s2.列2

# **22.嵌套查询** #

##    ***（1）定义：*** ##

>             一个查询嵌套在另一个查询的where子句或having短语条件中

##    ***（2）形式：*** ##

>             Select … from 学生where 85 <= (select avg(成绩))
> 
>                                         From 选修
> 
>                                         Where 学号 = 学生.学号)

##    ***（3）带有any all的子查询*** ##

>            【例】**查询其它班比信管1班某一学生年龄小的学生姓名和年龄**
> 
> Select 姓名 from 学生where 年龄 < any(select 年龄
> 
> From 学生
> 
> Where 班号 = ‘信管1’)
> 
>                                         And 班号 <> ‘信管1’

##    ***（4）带有exists not exists的子查询*** ##

>            【例】**查询信管班选修了’002’号课程的学生姓名**
> 
>            Select 姓名
> 
>            From 学生
> 
>            Where exists （select \*
> 
> From 选修
> 
> Where 学号 = 学生.学号 and 课号 = ‘002’）
> 
>                   And 班号 like ‘信管%’

##    ***（5）基于派生表的查询*** ##

>             【例】**找出某个学生选修课成绩超过自己选修课程平均分的课程号**
> 
>                Select 学号，课号
> 
>                From 选修，（select 学号，avg(成绩)
> 
> From 选修
> 
> Group by 学号） as 平均（学号，平均分）
> 
>                Where 选修.学号 = 平均.学号 and 成绩 > 平均分

# **23.集合查询：** #

> 每一个select命令的结果都是一个表，每个表都代表一个集合。

##     ***（1）并(union)运算*** ##

>              【**例】查询选修了01号课程，或者选修了03号课程的学生**
> 
>                  Select 学号 from 选修 where 课号 =‘01’
> 
>                  Union
> 
>                  Select 学号 from 选修 where 课号 =‘03’

## ***（2）交(intersect)运算*** ##

>           **【例】查询既选修了01号课程，又选修了03号课程的学生**
> 
>                  Select 学号 from 选修 where 课号 =‘01’
> 
>                  Intersect
> 
>                  Select 学号 from 选修 where 课号 =‘03’

##      ***（3）差(except)运算*** ##

>              【例】**查询选修了01号课程，但没有选修03号课程的学生**
> 
>                 Select 学号 from 选修 where 课号 =‘01’
> 
>                 Except
> 
>                 Select 学号 from 选修 where 课号 =‘03

# **24.嵌套查询更新数据库** #

##      ***（1）向表中插入子查询的结果*** ##

>               【例】**向选课表中插入信管1班所有同学选修’005’号课程的记录**
> 
>                     Insert 选修(学号，课号，成绩)
> 
>                     Select 学号，‘005’，null
> 
>                    From 学生
> 
>                     Where班号 = ‘信管1’

##      ***（2）带子查询的修改命令*** ##

>               【例】**将信管1班全体学生的’001’号课程成绩置零**
> 
>                     Update
> 
>                     Set 成绩 = 0
> 
>                     Where 课号 = ‘001’ and 学号 in (select 学号
> 
> From 学生
> 
> Where 班号 = ‘信管1’)

# **25.视图** #

##      ***（1）提出的愿意*** ##

> ①每个关系表中仅存储一类数据，故需要综合信息时，需要进行连接、嵌套等查
> 
> 询费时费力
> 
> ②数据库中一般不存储统计数据

##      ***（2）视图是一个虚表*** ##

##      ***（3）典型视图*** ##

>         行列子集视图：从单个表中导出，去掉某些行列，保留了码
> 
>         从多表导出的视图：
> 
>         带表达式的视图：select语句中含有表达式(例如：datepart(year,getdate()))
> 
>         分组视图：select语句中含有统计函数

##      ***（4）更新视图*** ##

>         ** **通过视图更新表中的数据，有的情况下是可行的，有的情况下，则因为对视图的
> 
> 更新不能转换成对表的更新而无法进行。例如：更新表出生年（出生年份）就会
> 
> 失败，因为出生年份是通过表达式计算出来的。一般来讲，行列子集视图是可更
> 
> 新的，表达式视图、分组视图就不允许更新，从多个表导出的视图则不一定。

##      ***（5）视图的作用*** ##

>          ①简化用户操作
> 
>          ②使用户多角度看待同一数据
> 
>          ③对重构数据库提供了一定的逻辑独立性
> 
>           ④对机密数据进行安全保护

# **26.DB安全性控制** #

##      ***（1）创建用户*** ##

>             Create user 用户名
> 
>             With 权限
> 
>            （connect:数据库连接  Resource:表视图创建(可授权他人)  dba:超级用户）

##      ***（2）授权*** ##

>             Grant 权限         （select insert update delete / all privileges / alterindex）
> 
>             On 对象类型 对象名 （table）
> 
>             To 用户
> 
>             With great option      (可传播权限)

##      ***（3）撤销权限命令*** ##

>             Revoke 权限
> 
>             On 对象类型 对象
> 
>             From 用户
> 
>             Cascade             （传播的权限一并撤销）

##      ***（4）角色*** ##

>             Create role 角色名
> 
>             Grant 权限
> 
>             On 对象类型 对象名
> 
>             To 角色名
> 
>             
> 
>             Grant 角色1,角色2，…
> 
>             To 角色,用户
> 
>  
> 
>             Revoke 权限
> 
>             On 对象类型 对象名
> 
>             From DB用户1, …

##       ***（5）SQLSever授权机制*** ##

>           在服务器级和数据库级采用基于“角色”的授权机制。即先将一系列权限授予
> 
> 不同的“角色”，然后再将角色享有的权限授予用户。角色授权机制能简化数
> 
> 据库的授权工作。

## ***     （6）SQL Sever安全机制*** ##