SQLServer复习笔记

第一章 关系数据库基础

1. 基本概念

• 数据(Data)：是描述事物的符号记录，是数据库中存储的基本对象。
  数据的种类:数字、文字、图形、图象、声音、视频等
• 数据库(Database):是长期存储在计算机内，有组织的、可共享的数据的集合。
• 数据库管理系统(DBMS):是使用户可以定义、创建和维护数据库以及提供数据库有限制访问的软件系统。负责对数据库的数据进行统一的管理和控制。
• 数据库系统(DBS):是采用数据库技术的计算机系统。一般由计算机软硬件系统、数据库、数据库管理系统及用户组成。本质上是一个用计算机存储数据的系统

1. 数据模型三要素：

• 数据结构
• 数据的约束条件
• 数据操作(增删改查)

1. 数据模型：是对现实世界数据特征的抽象
   
2. 关系模型是目前应用最广泛的数据库系统
3. 关系数据模型三要素：

• 数据结构：二维表
• 数据约束：不说了
• 数据操作：包括增(insert)删(delete)改(update)查(select)，操作对象是集合

1. 关系模型的数据完整性约束

• 实体完整性：主键非空，不重复
• 参照完整性：定义外键与主键之间的引用规则
• 用户定义的完整性：字段类型、长度

1. 外键一般符合：值为空或者等于锁应用的关系中的某个元祖的主键
2. SQL(结构化查询语言)
   

特别特别重要的(很可能考)
where和having的区别

• where作用于基本表和视图
• having作用于分组之后的数据

第二章 SQLServer基础

没有

第三章 数据库创建与管理

数据文件最大大小无限制：maxsize = unlimited
每次增加xxx：filegrowth = 2MB
文件组：filegroup GROUP2
添加新文件：alter database 数据库 add file
修改文件：alter database 数据库 modify file
添加新的日志文件：alter database 数据库 add log file
收缩数据库：dbcc shrinkfile(student_data1,4)
删除文件：alter database 数据库 remove file Students_data1

1. 数据库基本分类
   

2. 数据文件： ‘.mdf’,’.ndf’

3. 日志文件：’.ldf’

4. 特别注意：数据文件和日志文件必须都有一个，次要数据文件可以有多个，日志文件也可以有多个

5. 文件组只作用在数据文件上

6. 创建数据库

   create database
   on primary --基本文件组
   (
   name = students_data1, --逻辑文件名
   filename = 'F:\Data\student_data1.mdf', --主数据文件
   size = 5MB, --初始大小
   maxsize = unlimited --最大大小无限制
   ), --如果有次要数据文件，这里必须加逗号
   (
   name = students_data2,
   filename = 'D:\Data\students_data2.ndf',
   size = 8MB,
   maxsize = 20MB,
   filegrowth = 2MB --每次增加xxx
   ) --不加逗号
   log on
   (
   name = students_log,
   filename = 'E:\log\students_log.ldf',
   size = 2MB,
   maxsize = 6MB,
   filegrowth = 10%
   ),
   filegroup GROUP2 --新的文件组
   ...
   

7. 添加新的数据文件

   alter database 数据库
   add file
   (
   name = ...
   ....
   )
   

8. 修改数据文件的初始大小

   alter database 数据库
   modify file
   (
   name = Students,
   size = 8MB
   )
   

9. 添加新的日志文件

   alter database 数据库
   add log file
   (
   ...
   )
   

10. 收缩数据库

    dbcc shrinkfile(student_data1,4)
    

11. 删除文件

    alter database 数据库
    remove file Students_data1
    

12. 创建文件组

    alter database Students
    add filegroup Group1
    

13. 删除文件组

    alter database Students
    remove filegroup Group
    

14. 将文件组设为默认文件组

    alter database Students
    modify filegroup Group default
    

15. 删除数据库

    drop database Students
    

16. 分离数据库

    exec sp_detach_db 'Students','true'
    

17. 附加数据库

    create database Students
    on (filename = 'F:\Data\Students.mdf')
    for attach
    

第四章 SQL基础

1. 数据类型
   数值类型

   数据类型	说明	存储空间
   bigint	从–263到263-1范围的整数	8字节
   int	从–231到231-1范围的整数	4字节
   smallint	从–215到215-1范围的整数	2字节
   tinyint	从0到255之间的整数	1字节

   近似数值数类型

   数据类型	说明	存储空间
   float[(n)]	存储从-1.79E+308到-2.23E-308,0以及2.23E-308到1.79E+308范围的浮点数	当1<=n<=24,占四字节,当25<=n<=53,占8字节,默认n=53
   real	存储从–3.40E + 38到3.40E + 38范围的浮点型数	4字节

   字符串类型
   
   普通编码字符串类型

   数据类型	说明	存储空间
   char(n)	固定长度的普通编码字符串类型，n表示字符串的最大长度，取值范围为1～8000	n个字节。当实际字符串所需空间小于n时，系统自动在后边补空格
   varchar(n)	可变长度的字符串类型，n表示字符串的最大长度，取值范围为1～8000	字符数＋2字节额外开销
   text	最多可存储232-1个字符	每个字符1字节

   统一字符编码字符串类型

   数据类型	说明	存储空间
   nchar(n)	固定长度的统一编码字符串类型，n表示字符串的最大长度，取值范围为1～4000	2n字节。当实际字符串所需空间小于2n时，系统自动在后边补空格
   nvarchar(n)	可变长度的统一编码字符串类型，n表示字符串的最大长度，取值范围为1～4000	2*字符数＋2字节额外开销
   ntext	最多存储230-1个字符	每个字符2个字节

   日期时间类型

   数据类型	存储空间
   date	3字节
   time	3~5字节
   datetime	8字节

2. 标识符
   • 常规标识符
     规则
     第一个字符：字母,_,@,#
     其它：字母,数字,$,_,@,#
     不能是保留字
     不能有空格
     字符数必须在1~128之间
   • 分隔标识符
     不符合标识符基本规则时用这个(一般不会出现，除非老板特殊要求)

     select * from "Black in Table Name"
     select * from [Black in Table Name]
     

   1. 变量
      局部变量：@变量名 用户使用
      全局变量：@@变量名 系统用于记录信息

第五章 架构与基本表

主键：primary key
取值不重复：unique
默认值：default ‘x’
取值范围：check(Sage>=15 and Sage<=45)
表级主键：primary key(Sno,Cno)
表级外键：foreign key(Sno) references Student(Sno)
自增：identity(1,1)

1. 创建表

   create table SC(
   Sno char(7) not null,
   Cno char(6) not null,
   Grade tinyint,
   primary key (Sno,Cno),
   foreign key (Sno) references Student(Sno),
   foreign key (Cno) references Student(Cno)
   )
   

2. 创建有计算列的表

   create table Test(
   low int,
   high int,
   myavg as (low+high)/2
   )
   

3. 为已有表添加一个字段

   alter table 表名 add 列名 属性
   alter table SC add Type NCHAR(1) NULL
   

4. 为已有字段添加取值范围

   alter table SC
   add check (type in ('必修','重修','选修'))
   

5. 删除某字段

   alter table SC drop column 字段名
   

6. 删除表

   drop table 表名
   

第六章 数据操作语言

distinct 去除重复值
top 几 with ties
字符匹配
_：任意单个字符
%：0个或者多个任意字符
[]：里边的任意一个
[^]：除了里边的
只有count能出现*,sum,avg只能操作数值类型字段
insert into 表名 values (xxx)
update 表名 set 列名 = xxx
delete from 表名 where …

1. 查询年龄

   select Sname,year(getdate())-year(Birthdate) from Student
   

2. 去掉重复值

   select distinct Sno from SC
   

3. 在某个范围

   select Sno where Grade between 80 and 90
   

4. 查询信息系，机械系的学生

   select Sname from Student where Dept in ('信息系','机械系')
   

5. 字符串匹配

   select * from Student where Sname like '章%'
   

6. escape 转义

   -- 感叹号后边的一个字符作为普通字符处理，不作为通配符处理
   select * from num where num like '%30!%%' escape '!' 
   

7. 排序

   -- DESC是降序，默认是升序（ASC）
   select * from SC order by SCORE DESC
   

8. 聚合函数
   count(*)
   count(distinct Sno) 去重统计
   sum(列名)
   avg(列名)
   max(列名)
   min(列名)
9. 分组 一旦出现每xxx，一定会有分组

   select count(*) from SC group by CNO
   

   特别注意：分组之后只能返回分组对象和聚合函数
10. 内联
    比如有两张表T1和T2
    T1:

    TNO	TNUM
    001	one
    002	two

    T2:

    TNO	TNAME
    001	la
    002	lala
    003	lalala

    如果运行下边的语句，将会把两张表合成一张

    select * from T1 join T2 on T1.TNO = T2.TNO
    

    TNO	TNUM	TNAME
    001	one	la
    002	two	lala

    然后就可以从里边找东西了
11. 自连 ：通过给表取别名实现
    比如找和张小明同系的学生

    select S2.Sname 
    from Student S1 join Student S2
    on S1.Dept = S2.Dept 
    where S1.Sname = '张小明' and S2.Sname != '张小明' --这句一定要记得加
    

12. 外联：主表中的数据全部返回，从表中有的字段值为null
    没有选课的学生

    select * from Student left outer join SC 
    on Student.Sno = SC.Sno
    where SC.SNO is null
    

13. top ：一定要搭配order by子句

    select Top 3 with ties Sname from Student order by Grade desc
    

14. 将查询结果保存到新表中

    select xxx into 新表名 from 表名
    

15. 插入数据

    insert into 表名 values (xxx)
    insert into 表名(字段名) values (xxx)
    

16. 更新数据

    update 表名 set 列名 = xxx
    

17. 删除数据

    delete from 表名 where ...
    

第七章 高级查询

1. 子查询
   通常有以下几种形式

   where 列名 [NOT] IN (子查询语句)
   where 列名 比较运算符 (子查询语句)
   where exists (子查询语句)
   

   例如：查询选了C004课程的所有考试中，成绩高于此课程平均成绩的学生的学号和成绩

   select SNO,SCORE from SC
   where Cno = 'C004' and 
   Grade > (select avg(Grade) from SC where Cno = 'C004')
   

2. SOME和ALL
   当子查询返回一个结果集时，SOME表示符合子查询中的其中一个就行，ALL表示要符合子查询中的所有数据
   题目标志：有一次、全部
   例如：查询至少有一次成绩大于或等于90的学生的姓名、所修课程的课程号和成绩

   select Sname,Cno,Grade from Student S
   join SC on S.Sno = SC.Sno
   where S.Sno = SOME(
   select Sno from SC where Grade >=90
   )
   

3. 上边的都是不相关子查询，相关子查询是两个select语句存在一定的交流
   如：查询每门课程考试成绩最高的两个学生的学号以及相应的课程号和成绩。不包括没考试的课程

   select Sno,Cno,Grade from SC SC1
   where Sno in(
   select top 2 with ties Sno from SC SC2
   where SC1.Cno = SC2.Cno
   order by Grade desc
   )
   and Grade is not null
   order by Cno ASC,Grade DESC
   

4. exists
   存在则返回TRUE，不存在则返回FALSE
   如：查询选了C002课程的学生的姓名

   select Sname from Student
   where exists
   (
   select * from SC where Sno = Student.Sno and Cno = 'C002'
   )
   

5. 查询的交差并
   并

   select COL1 from T1
   union
   select COL3 from T2
   

   交：intersect
   差：except

第八章 索引

1. 聚集索引和非聚集索引的区别
   数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引，相当于字典中拼音查询的。
   非聚集索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引，相当于字典中的偏旁字典。
2. 创建聚集索引

   create clustered index Idx_Sname on Student(Sname)
   

3. 创建唯一非聚集索引

   create unique index AK_UnitMeasure_Name
   on Production.UnitMeasure(Name)
   

4. 删除索引

   drop index Idx_Sname on Student
   

5. 例题
   在Teacher表的TNAME列上按升序和AGE列上按降序创建非聚集索引IND_TNO_AGE

   create index IND_TNO_AGE on Teacher(TNAME ASC,AGE DESC)
   

   为Course表在CNAME列上按升序创建唯一索引IND_CN。

   create unique index IND_CN on Course(CNAME ASC)
   

   索引根据存储结构分为聚集索引和非聚集索引两类。
   索引在执行插入、修改、删除时节省时间？ ×
   索引加快数据的查询效率？ ×
   索引节省磁盘空间？ ×
   在数据库中建立的索引越多越能提高操作效率？ ×
   聚集索引一定是唯一索引？ ×

第九章 视图

只有单表时才能进行增删改
有函数或者连接时不能进行增删改
透过视图的操作也就是对基本表做的操作

1. 创建视图
   创建视图View_S，利用视图可以查询学生学号、姓名、性别和系别。

   create view View_S(SNO,SNAME,SEX,DEPT)
   as
   select SNO,SNAME,SEX,DEPT from Student
   

2. 修改视图View_S，利用视图可以查询学生学号、姓名和系别。

   alter view View_S(SNO,SNAME,DEPT)
   as
   select SNO,SNAME,DEPT from Student
   

3. 创建视图View_S_CS，通过视图只能对计算机系学生信息进行操作，其他系学生信息操作均非法。

   create view View_S_CS
   as
   select * from Student where DEPT = '计算机'
   with check option
   

4. 删除视图

   drop view View_S
   

补充

1. union
   union操作符用于合并两个或多个select语句的结果集
   union内部的select语句必须有相同数量的列，列也必须有相同的数据类型，同时，每条select语句中的列的顺序必须相同
2. 全局变量(@@)是系统内部的变量，不能自定义
3. 系统数据库不能分离
4. 在SQL Server服务器上，存储过程是一组预先定义并编译的T-SQL语句
5. 数据库中只存放视图的定义
6. 各个连接的区别
   比如有表a,b
   a表

   x	y	z
   1	2	3
   2	3	4
   3	4	5

   b表

   x	q	w
   1	2	3
   2	3	4
   3	4	5
   4	5	6

   内联
   返回表 最多返回条数是条数最小的那张表的条数

   a.x	y	z	b.x	q	w
   1	2	3	1	2	3
   2	3	4	2	3	4
   3	4	5	3	4	5

   外联
   返回条数是主表的条数，即主表的数据全部返回
   如a left outer join b返回结果为

   a.x	y	z	b.x	q	w
   1	2	3	1	2	3
   2	3	4	2	3	4
   3	4	5	3	4	5

   b left outer join a返回结果为

   b.x	q	w	a.x	y	z
   1	2	3	1	2	3
   2	3	4	2	3	4
   3	4	5	3	4	5
   4	5	6	NULL	NULL	NULL

   自联
   如查询a表中和x=1的w相等的数据
   select from a a1 join a a2 on a1.x = a2.x

   a.1x	a1.y	a1.z	a2.x	a2.y	a2.z
   1	2	3	1	2	3
   2	3	4	2	3	4
   3	4	5	3	4	5

   左边三列是数据源，右边三列是结果所在的位置，切记最后一定不要返回左边三列和右边三列完全相同的数据

课后习题答案：https://www.docin.com/p-1996215595.html