数据库系统概述
数据库系统由计算机软件、硬件、数据库、数据库管理系统、数据库应用程序、数据库管理员与用户组成
数据库是指储存在计算机内有组织可共享的数据集合
数据库管理系统是位于用户与操作系统之间用于管理数据库的系统软件
常见的数据库管理系统包括关系型和非关系型
关系型：ACCESS、SQL SERVER、ORACLE、SYBASE、MYSQL
非关系型：SQLITE、redis(如新浪微博)、mongo db
关系型和非关系型指的是数据模型
分类：概念层数据模型和组织型数据模型
3、域
实体中相应属性的取值范围
例如性别属性的域为男或女
4、联系
实体间的相互关系
一对一；一对多；多对多
组织层数据模型
分类：层次模型、网状模型、关系模型、面向对象模型
数据库的设计
创建和管理数据库
使用SSMS创建数据库
数据库的分类：系统数据库和用户数据库
系统数据库：
主数据库master：保存所有系统信息、所有数据库文件的位置，同时还记录了SQL SERVER的初始化信息
模板数据库model：作为新创建数据库的一种模板或原型
数据库msdb：用于安排sql server的周期活动，包括任务调动、异常处理和报警管理等。
临时数据库tempdb：用作系统的临时存储空间，主要作用是存储用户建立的临时表和临时存储过程
用户数据库scc
数据库的文件组成：主数据库文件、辅助数据文件、事务日志文件
文件组类型
主文件组：由sql server自动创建，名字叫primary，主文件组上包括主数据文件和所有没有被包括在其他文件组中的文件
用户自定义文件组：为了提高数据库的性能，用户自己创建的，它包括创建或修改数据库时使用filegroup关键字指定的文件组
使用数据库文件和文件组的几个问题：
一个文件或文件组只能用于一个数据库，不能用于多个数据库
同一个辅数据文件只能存放在一个文件组中
文件组不适用于事务日志文件
使用图形化界面创建数据库
数据库——属性——选项——限制访问设置，兼容级别、数据库是否为只读
MULTI_USER、SINGLE_USER、RESTRICTED_USER（只有管理员或特定的访问数据库）
文件组为默认的话，添加文件会把文件自动存储到对应文件组
使用sql语句创建数据库
使用模板资源管理器创建数据库
数据库的维护
修改数据库
不可以修改：主数据文件名、事务日志文件名、存放路径
可以修改：新增辅助数据文件或事务日志文件、修改配置选项
包括三个方面：在数据库中新增文件组和文件，删除文件组和文件
修改数据库文件或事务日志的容量
修改数据库名称
新增文件组和文件的语法格式：
alter database 数据库名
add filegroup newgroup
注意：1、一条alter database语句只能新增一个文件，不管是数据文件还是日志文件
2、如果是数据文件，只能是辅数据文件，因为一个数据库只能有一个主数据文件
alter database scc
add filegroup newgroup
alter database scc
add file
( name = scc_data3,
filename = ‘e:\db\scc_data3.ndf’
)
alter database scc
add log file
( name = scc_log3,
filename = ‘e:\db\scc_data3.ndf’
)
alter database scc
remove file scc_data3
alter database scc
remove filegroup newgroup
alter database scc
modify file
( name = ‘scc_data1’,
size = 20MB )
alter database scc
modify file
( name = ‘scc_log1’,
size = 16MB
)
数据库重命名两种方法：
alter database test
modify name = newtest
调用系统自带存储过程：sp_renamedb test,newtest
查看是否修改成功
EXEC SP_HELPDB TESTDB;
删除数据库
删除数据库的作用：可以以释放数据库所占用的磁盘空间，不能被删除数据库的三种情况：
用户正在使用数据库时
当数据库正在恢复时
当数据库正被复制时
语法：drop database 数据库名
drop database sales,newsales;
数据库的备份与还原，分离与附加
备份：差异备份(differential backup)和完整备份(full backup)
脱机与联机数据库
数据库的状态：https://www.cnblogs.com/firstdream/p/7273793.html
右键数据库——任务——脱机，这样就可以复制数据库文件
分离与附加数据库
附加数据库的时候必须指定MDF的名称和物理存放位置
USE master—指向当前所操作的数据库
GO
EXEC sp_detach_db @dbname=E_Market—分离数据库
GO
USE master
GO
EXEC sp_attach_db @dbname=E_Market,—附加数据库
@filename1=’H:\project\E_Market_data.mdf’,
@filename2=’H:\project\E_Market2_data.ndf’,
@filename3=’H:\project\E_Market_log.ldf’,
@filename4=’H:\project\E_Market2_log.ldf’
GO
收缩数据库
USE E_Market—指向当前所操作的数据库
GO
IF EXISTS(SELECT FROM sysobjects WHERE name=’Test’)
DROP TABLE Test
CREATE TABLE Test
(
UName nvarchar(50) NOT NULL,
UPwd nvarchar(50) NOT NULL,
UAddress nvarchar(50) NOT NULL
)
GO
—声明变量
DECLARE @x int ,@y int
—为变量赋初值
SET @x=100000
SET @y=0
WHILE(@y<@X)
BEGIN
INSERT INTO Test(UName,UPwd,UAddress)
VALUES(‘张三’+CONVERT(varchar(5),@x),’123456’,’北京市昌平区’)
SET @y=@y+1
END
GO
—查询表中的记录数
SELECT COUNT() FROM Test
—删除表中的所有数据
TRUNCATE TABLE Test
数据表的设计
三个方面：确定表名和属性名、选取字段的数据类型、设置数据完整性约束
确定表名和属性名
帕斯卡命名法：单词首字母大写，其他字母小写
驼峰法：除了第一个单词的首字母，其他单词首字母都是大写
注意：一般采用英文单词或缩写，避免使用拼音命名
选取字段的数据类型
设置数据完整性约束
数据表的创建
使用图形化界面SSMS创建数据表
标识列：能够自动为表生成行号，行号是按照指定的标识增量和标识种子排序
注意：1、一个表只能创建一个标识列
2、如果在创建标识列时没有指定标识增量和标识种子，那么采用默认值，默认值是（1,1）
3、标识列的数据类型只能使用整型中的bigint、int、smallint、tinyint。定点小数类型decimal、numeric也可以使用，但是不允许出现小数位数。
使用SQL语句创建数据表
create table 表名(
列名1 列属性,
列名2 列属性,
……,
列名n 列属性
)
列属性包括字段数据类型、长度、是否允许为空、字段默认值、是否为标识列等
注意：1、数据类型中，只有char、nchar、varchar、nvarchar数据类型必须指明长度，其它数据类型不用指明长度
2、decimal(p,s)和numeric(p,s)数据类型必须指明p(精度)和s(小数位数)。
3、标识列：使用identity(标识种子，标识增量)语法定义。
4、null：标识允许为空，字段定义时默认为允许空，可以省略。
not null：标识不允许为空
注意：
1、标点符号必须使用英文标点符号
2、在执行语句前，先要检查当前数据库是否为scc，否则会造成将当前表创建到其他数据库中
数据完整性约束
数据完整性分为四类：
如何实现数据完整性，可以通过为表的字段设置约束来保证表中数据完整性。
约束包括六种：
主键约束-primary key
非空约束- not null
检查约束-check
唯一约束-unique
外键约束-foreign key
默认值约束-default
主键约束
主键约束用于满足实体完整性，要求主键列数据唯一并且非空，主键约束的命名推荐使用“PK_表名”的命名格式，方便后续使用时进行识别
可以通过图形化界面和SQL语句两种方式设置
SQL：
方法一：在创建表时设置主键，使用列级约束。
create table student(
sno nvarchar(15) primary key,
sname nvarchar(10),
birth date)
使用这种方法，数据库会自动将主键字段名设置为PK_sno
组合主键
create table (
sno nvarchar(15),
cno nvarchar(10) primary key(sno,cno),
uscore numeric(4,1),
endscore numeric(4,1)
)
方法二：在创建表时设置主键，使用表级约束
create table score(
Sno nvarchar(15),
Cno nvarchar(10),
constraint PK_Score primary key(Sno,Cno)
)
这也是组合主键
方法三：在修改表时，设置主键
alter table student
add constraint PK_Student primary key (Sno)
组合主键：
alter table score
add constraint PK_Score primary key (sno,cno)
使用图形化界面设置主键，组合主键(ctrl选中多个字段)
删除主键约束
SQL:
alter table Student
drop constraint PK_Student
图形化界面：
外键约束和非空约束
外键约束
用于实现表和表之间的参照完整性，外键约束的核心是给表创建外键，外键的作用是关联两张二维表，使二维表所描述的实体建立联系。
外表可以理解为两张二维表含以上的公共字段，这个字段的字段名在两张表中可以不同，但是要注意，公共字段的两张表中的数据类型一定是相同的。
如果这个公共字段在一张表中是主键或主键之一，就把这张表叫做主键表，如果这个公共字段在一张表中不是主键，就把这张表叫做外键表，而且外键的创建需要在外键表上创建。
外键约束在创建时的命名规则为，FK外键表名主键表名。
SQL语句和图形化界面创建外键约束
在SCC中创建外键约束，关联student学生表和class班级表，实现两张表的参照完整性
方法1：在创建表时创建外键约束，使用列级约束
create table student(
sno nvarchar(15) primary key,
sname nvarchar(10),
sex nchar(1),
birth date,
classno nvarchar(10) foreign key references class(classno)
)
语法格式：foreign key references 主键表名(主键字段名)
注意：班级表class必须存在，在定义外键约束之前，必须先定义主键约束。在删除主键约束之前必须先删除外键约束。
方法2：在创建表时创建外键约束，使用表级约束
语法格式：基本上适用所有主流数据库
constraint 外键约束名 foreign key (外键字段名) references 主键表名(主键字段名)
create table student(
sno nvarchar(15) primary key,
sname nvarchar(10),
sex nchar(1),
birth date,
classno nvarchar(10),
constraint FK_Student_Class foreign key(classno) references class(classno)
)
方法三：在修改表时设置外键约束
alter table student add constraint FK_Student_Class foreign key(classno) references class(classno)
图形化界面
表——右击键——外键关系窗口————右侧常规选项——表和列规范
表设计器——insert和update规范——级联更新和级联删除
注意要单击保存，不然无法起作用
删除外键约束
SQL:
alter table student
drop constraint FK_Student_Class
图形化界面删除
非空约束
取空值和值为0或值为空字符串是不同的
(sno nvarchar(15) not null primary key,
sname nvarchar(10) not null,
sex nchar(1) not null,
birth date,
classno nvarchar(10) not null foreign key references class(classno)
)
检查约束、唯一约束、默认值约束
检查约束
为student学生表中的sex性别字段创建检查约束，性别字段的数值只能是男或女
命名规则：CK_字段名。在设置时可以使用逻辑表达式表示数据的有效性范围
创建表时列级约束
create table student(
sno varchar(15) not null primary key,
sname nvarchar(10) not null,
sex nchar(1) not null check (sex=’男’ or sex = ‘女’),
birth date,
classno nvarchar(10) not null foreign key references call(classno)
)
check(逻辑表达式),不能省略字段名
表级约束
create table student(
sno varchar(10) not null primary key,
snae nvarchar(10) not null,
sex nchar(1) not null,
birth date,
classno nvarchar(10) not null foreign key references class(classno) constraint CK_sex check(sex=’男’ or sex=’女’)
)
修改表时
alter table student
add constraint CK_Sex check(sex=’男’ or sex=’女’)
使用图形化界面创建检查约束
唯一约束
将department表的dname院部名称字段设置唯一约束
UQ_字段名
列级约束
create table department(
dno nvarchar(10) not null primary key,
dname nvarchar(30) not null unique
)
表级约束
create table department(
dno nvarchar(10) not null primary key,
dname nvarchar(30) not null,
constraint UQ_dname unique(dname)
)
修改表时
alter table department
add constraint UQ_dname unique(dname)
图形化界面设置
默认值约束
将course表中的teacher任课教师字段设置默认值约束为’待定’
只能使用列级约束进行定义
create table course(
cno nvarchar(10) not null,
cname nvarchar(30) not null,
teacher nvarchar(10) not null default ‘待定’,
limitnum int not null check(limitnum>0),
coursehour int
)
alter table course
alter column teacher set default ‘待定’
图形化界面设置
表设计器
数据表的修改
修改数据表结构
修改数据表包含的操作
修改数据表的方法
SQL语句：
1、修改表名
execute sp_rename 旧表名,新表名
2、修改列名
execute sp_rename ‘表名.旧列名’,’新列名’[,’column’];
或者exec sp_rename
execute sp_rename ‘student.cnoo’,’cno’,’column’;
—或者 execute sp_rename ‘student.cnoo’,’cno’;
3、修改列的数据类型
alter table 表名 alter column 列名 数据类型;
alter table student alter column cname varchar(50)
4、添加列
alter table 表名 add 新列名 新列名数据类型
5、删除列
alter table 列名 drop column 列名;
SSMS:
表数据的增删改
指定字段名：insert into 表名(列名1,列名2,`) values(值1,值2,);
必需列出所有字段名
不指定字段名：insert into 表名 values(值1,值2,);<br />添加的值得顺序必须和字段在表中定义的顺序相同<br /> <br /> 向表中指定列添加数据：insert into 表名(字段1,字段2,) values(值1,值2,)<br /> <br />从其他表copy数据：insert into 表名(字段) select 字段 from 表2<br /> <br />每个值得顺序、类型必须与对应的列相匹配<br />被省略的列不能是主键列或者有非空约束的列<br /> <br /><br /> <br /> <br />update 表名 set 字段名1=值1,字段名=值2, [where 条件表达式]
delete from 表名 [where 条件表达式];
删除表中所有的记录:delete from 表名
truncate [table] 表名
SSMS可视化删除
查询与统计数据
select语句的语法
select书写格式
不区分大小写
可以随意分行
没有语句结束标志
数据库的简单查询
use scc
select from student
select sno 学号,sname 姓名 from student
select sno as 学号,sname as 姓名 from student
select 学号=sno,姓名=sname from student
select cno,cname,teacher from course where cname like ‘%数据库%’
%表示任意长度的字符串
select from student where endscore is null
数据库的分组统计
使用聚合函数查询
select count() from student
select 字段列表 from 表名 where group by 分类字段 having 检索条件
group by将查询结果按某列或多列的值分组，值相等为一组，若未对查询结果分组，聚合函数将作用于整个查询结果；分组后聚合函数将作用于每一个组
select sex 性别,count() 人数 from student group by sex
select sno,avg(endscore) from score group by sno having avg(endscore) >=80
数据库的内连接查询
多表查询是指将多个表连接在一起的查询，也称连接查询，分为内连接和外连接
查询所有学生的学号、姓名、课程编号、平时成绩和期末成绩
select student.sno,sname,cno,uscore,endscore from student,score where student.sno = score.sno
或者
select student.sno,sname,cno,uscore,endscore from student inner join score on student.sno = score.sno
查询所有男生的学号、姓名、课程编号、平时成绩和期末成绩
select student.sno,sname,cno,uscore,endscore from student,score where student.sno = score.sno and sex = ‘男’
或者
select student.sno,sname,cno,uscore,endscore from student inner join score on student.sno = score.sno where sex = ‘男’
查询所有学生的序号、姓名、课程名称、课程成绩
select student.sno,sname,cname,uscore,endscore from student,course,score where student.sno = score.sno and course.con = score.cno
或者
select student.sno,sname,cname,uscore,endscore from student inner join score on student.sno = score.sno inner join course on course.cno = score.cno
查询每门课程的课程号、课程名及其选课人数
select score.cno,cname,count(*) as num from course,score where course.cno = score.cno group by score.cno,cname
数据库的外连接查询
外连接通常用于相连接的表中至少有一个表需要显示所有数据行的情况
外连接的结果集中不但包含满足连接条件的记录，还包含相应表中的所有记录，即某些记录即使不满足连接条件，但仍需要输出
外连接主要分为左外连接、右外连接和全连接
查询所有学生的选修课程情况，结果显示为学号、姓名、课程编号和课程成绩
1、使用内连接，只显示有选课记录的学生信息
select student.sno,sname,score.cno,uscore,endscore from student inner join score on student.sno=score.sno
2、使用左外连接，显示所有学生的信息，没有选课的学生，其成绩列显示为null
select student.sno,sname,score.cno,uscore,endscore from student left join score on student.sno=score.sno
数据库的子查询
带in谓词的子查询:
父查询 [not] in 子查询
select sno from score group by sno having count()>3
select sname from student
where sno in
(
select sno from score group by sno having count()>3
)
带any或all谓词的子查询:
子查询返回单值时可以用比较运算符，但返回多值时要用ANY(有的系统用SOME)或ALL谓词修饰符。而使用ANY或ALL谓词时则必须同时使用比较运算符。其语义为：
> ANY 大于子查询结果中的某个值
> ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
>= ANY 大于等于子查询结果中的某个值
>= ALL 大于等于子查询结果中的所有值
<= ANY 小于等于子查询结果中的某个值
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值
= ALL 等于子查询结果中的所有值（通常没有实际意义）
!=(或<>) ANY 不等于子查询结果中的某个值
!=(或<>) ALL 不等于子查询结果中的任何一个值
1、查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄
SELECT Sname,Sage FROM Student WHERE Sage < ANY(SELECT Sage FROM Student WHERE Sdept=’CS’ ) AND Sdept <> ‘CS’;
RDBMS 执行次查询时，首先处理子查询，找出CS系中所有学生的年龄，构成一个集合，然后处理父查询，找所有不是CS系且年龄小于该集合中年龄的学生。
带exists谓词的子查询:
Exists，NOT EXISTS：
1.含义：带有exists谓词的子查询不返回任何实际数据，它只产生逻辑真值true或逻辑假值false。
2.查询所有选修了c1号课程的学生姓名：
select sn from s where exists (select from sc where sno=s.sno and cno=’c1’)
注：若内查询结果（select from sc where sno=s.sno and cno=’c1’）为空，则外层的where子句返回真值，否则返回假值！
查询过程：步骤一：从外层查询中的s表的第一个元组，根据它，与内层查询相关的属性值（sno值）处理内层查询，若where查询子句返回值为真（及内层查询非空），则取此元组放入结果表；
步骤二： 在检查s表中下一个元组；
步骤三：重复这一过程，直至s表全部检查完毕为止！
3。查询所有未修c1课程的学生姓名：
select sn from s where not exists (select from sc where sno=s.sno and cno=’c1’)
4.查询与“王林”在同一系学习的学生的信息
select from s s1 where exists (select from s s2 where s2.dept=s1.dept and s2.sn=’王林’)
5.查询选修了全部课程的学生的姓名
select sn from s where not exists(select from c where not exists(select * from sc where sno=s.sno and cno=c.cno))
创建视图与索引
视图
编程和界面两种方式
注意：
1、视图名不能与现有的表名或视图名重复
2、组成视图的属性列，要么全部省略，要么全部指定
3、子查询也就是任意的select语句
4、对视图进行更新操作时强制要求满足子查询中的条件
视图名后面的属性列可以省略，隐含该视图子查询中select子句目标列中的字段组成。
下列情况属性列不能省略：
1、子查询目标列有聚集函数或列表达式
2、子查询为多表连接，并且选出了几个同名列作为视图的字段
3、需要在视图中为某个列使用别名
索引
概述
索引是对数据库表中的一个或多个列的值进行排序的结构，索引是依赖于表建立的，他提供了数据库中编排表中数据的内部方法
优点：
1、索引能提高查询表内数据的速度
2、能加快表与表之间的链接速度
缺点：
1、创建和维护索引增加了额外的时间开销
2、索引会占用一定的存储空间，占用空间过大时会影响数据库的性能
分类：根据索引页的顺序与数据也中行的物理存储顺序是否相同，可以把索引分为聚集索引和非聚集索引
聚集索引：索引逻辑顺序与记录物理顺序相同，由于表中的记录只能按一种物理顺序存储，所以一个表只有一个聚集索引
非聚集索引：是在不改变记录的物理顺序的基础上，通过顺序存放指向记录的指针来实现建立逻辑顺序的方法。索引逻辑顺序与记录物理顺序不同，不受物理顺序的影响，一个表的非聚集索引最多可以有249个
创建索引的基本原则：
1、定义有主键或外键的数据列一定要建立索引
2、对于经常查询的数据列最好建立索引
3、对于需要在指定范围内快速或频繁查询的数据列最好建立索引
4、经常使用where子句中的数据列最好建立索引
创建索引
SSMS：新建索引命令-选择非聚集索引-设置新建的索引参数-选择添加的索引键表列
SQL：
create index stu_name
on student(sname)
修改索引
数据库用户管理
创建登录名
对象资源管理器——安全性——登录名——右键新建登录名
登录名：查看当前电脑可以使用的用户——控制面板——用户账户
然后再连接界面可以用ctrl+alt+delete切换用户
要想创建的登录名能够操作数据库需要为数据库创建用户名
创建数据库用户名
创建数据库用户：选择一个数据库——安全性——用户——右键创建用户
数据库用户名可以和登录名相同或者不同
同一个用户可以同时访问多个数据库，要在创建数据库登录名的时候映射到对应数据库
同一个数据库可以拥有多个用户，映射数据库的时候会在对应数据库——安全性——用户的位置新建一个用户名
我们创建的这个用户他可以操作数据库但是不能对数据库的任何对象执行任何的权限
所以要为数据库使用者创建权限
为数据库用户赋予权限
双击某个登录名——安全对象——搜索
在安全对象——搜索——添加对象——特定对象——对象类型然后添加
注意要在有权限的登录名之下才能有权限执行SQL语句
—[1]创建一个登录名pbMaster 密码123456
USE master
GO
—创建登录名
CREATE LOGIN pbMaster WITH PASSWORD=’123456’
GO
—[2]创建数据库用户
USE E_Market
GO
—MasterDBUser是数据库用户名, pbMaster是登录名
CREATE USER MasterDBUser FOR LOGIN pbMaster
GO
—[3]赋予用户操作权限
USE E_Market
GO
—为表UserInfo赋予查看，新增，修改的操作权限 MasterDBUser数据库用户
GRANT SELECT,INSERT,UPDATE ON UserInfo TO MasterDBUser
GO
—[4]收回新增的操作权限
USE E_Market
GO
REVOKE INSERT ON UserInfo TO MasterDBUser
GO
服务器角色
public：所有数据库用户都是他的成员
可以同时登陆2个登录名
原因：该登录名没有创建数据库的权限
所以要为登录用户赋予dbcreator服务器角色
数据库用户角色
若有收获，就点个赞吧
0 人点赞
