数据库原理与应用随堂笔记

第1课 介绍

1.1 数据库结构

DBMS->DB->Table->Column->Data

数据库管理系统->数据库->表->列->字段

1.2 课程设计

1. 认识数据库及概述
2. 数据库的创建与管理
3. 创建与管理数据表
   • 掌握表结构的创建、修改和查询，掌握添加、修改和删除表中的数据和完整性约束的实现。
4. 数据查询
   • 掌握基本查询、连接查询、数据汇总的使用，熟悉嵌套查询的使用。
5. 视图的创建与使用
6. MySQL编程
7. 数据库规划与设计
   • 掌握数据库设计的步骤，并了解范式对数据库设计进行判定并规范

1.3 SQL server体验

1.3.1 使用工具登录SQL server

服务器名就是电脑设备名

• Windows身份验证
  • 直接连接就可以
• SQL server身份验证
  • 通常都是用户名是sa,密码是123456。

忘记了密码可以通过Windows身份登录，双击安全性下登录名下的sa就可以修改密码

1.3.2 使用脚本

/****** 对象:  Database [xkgl]   
 脚本日期: 04/05/2012 14:43:32 ******/
CREATE DATABASE xkgl     
go
/*创建xkgl数据库*/
use xkgl        
GO 
/*使用xkgl数据库*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Department]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Department]
GO
--表 Department 的结构
Create table Department(
DepartmentID char(4) primary key,
/*创立DepartmentID为主键，主键是唯一的*/
DepartmentName varchar(20) NOT NULL unique,
DepartmentHeader varchar(8) NOT NULL,
TeacherNum int 
)
GO
--表 Department 的数据
 insert into Department([DepartmentID],[DepartmentName],[DepartmentHeader],[TeacherNum]) Values('Dp01','计算机系','罗浩然',120)
 insert into Department([DepartmentID],[DepartmentName],[DepartmentHeader],[TeacherNum]) Values('Dp02','信管系','李伶俐',null)
 insert into Department([DepartmentID],[DepartmentName],[DepartmentHeader],[TeacherNum]) Values('Dp03','英语系','李宏伟',10)


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Class]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Class]
GO
--表 Class 的结构
Create table Class(
ClassID   char(8) primary key,
ClassName varchar(20) not null,
Monitor  char(8),
StudentNum int check(StudentNum>0),
DepartmentID char(4) references Department(DepartmentID)
)
go
--表 Class 的数据
 insert into Class([ClassID],[ClassName],[Monitor],[StudentNum],[DepartmentID]) Values('Cs010901','09软件技术1班','方凯',34,'Dp01')
 insert into Class([ClassID],[ClassName],[Monitor],[StudentNum],[DepartmentID]) Values('Cs010902','09软件测试1班','林静',10,'Dp01')
 insert into Class([ClassID],[ClassName],[Monitor],[StudentNum],[DepartmentID]) Values('Cs010903','09数据库班','黄勇',28,'Dp01')
 insert into Class([ClassID],[ClassName],[Monitor],[StudentNum],[DepartmentID]) Values('Cs011104','11网络工程',null,23,'Dp01')
 insert into Class([ClassID],[ClassName],[Monitor],[StudentNum],[DepartmentID]) Values('Cs021001','10电子商务1班','李米',33,'Dp02')
 insert into Class([ClassID],[ClassName],[Monitor],[StudentNum],[DepartmentID]) Values('Cs021002','10电子商务2班',null,null,'Dp02')


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Student]
GO
--表 Student 的结构
Create table Student(
StudentID   char(12) primary key,
StudentName char(8) not null,
Sex  char(2) not null check(sex in('男','女')),
Birth  datetime not null,
HomeAddr varchar(80),
EntranceTime datetime default getdate(),
ClassID char(8) references Class(ClassID)
)
go
--表 Student 的数据
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109010001','张宏    ','男','1998-4-4','广州市天河区常保阁东 80 号','2016-9-10','Cs010901')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109010002','姜明凡  ','男','1997-12-7','汕头市天源路34号','2016-9-19','Cs010901')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109010003','张丽    ','女','1997-9-30',NULL,'2016-9-19','Cs010901')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109010004','赵新宇  ','男','1998-5-9','大连市沙河区承德西路 80 号','2016-9-10','Cs010901')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109010005','李宇凯  ','男','1996-11-27','大连市沙河区花园东街 90 号','2016-9-10','Cs010901')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109010006','黄丽莉  ','女','1997-8-15','佛山市南海区南海大道120号','2016-9-10','Cs010901')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109010007','胡雨樱  ','女','1997-6-23','佛山市南海区狮山20号','2016-9-10','Cs010901')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109010008','曹海华  ','男','1999-7-12','上海市黄石路 50 号','2016-9-10','Cs010901')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109010009','许田    ','男','1998-2-21','深圳市福田区福华路29号京海大厦18c','2016-9-10','Cs010901')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109020001','李平    ','男','1998-4-30','成都市都江堰红石路321号','2016-9-10','Cs010902')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109020002','王虎    ','男','1997-10-19','宜宾市宜宾大道231号','2016-9-19','Cs010902')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109020003','黄微    ','女','1997-7-18','鞍山市中山大道786号','2016-9-19','Cs010902')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0109020004','张凯芝  ','女','1996-5-19','广州市东山区34号','2016-9-19','Cs010902')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0111040001','立号    ','男','2000-11-4',NULL,'2017-9-13','Cs011104')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0111040002','姚钱    ','女','2000-4-2','成都市武侯区中山大道12号','2011-9-13','Cs011104')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0111040003','方雨丽  ','女','2000-10-4',NULL,'2017-9-13','Cs011104')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0210010001','李平    ','女','1999-12-3','广州市萝岗区32号','2017-9-12','Cs021001')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0210010002','周雅云  ','女','1998-4-23','大连市沙河区花园东街 91 号','2017-9-12','Cs021001')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0210010003','王建伟  ','男','1997-10-10','成都市中山路321号','2017-9-12','Cs021001')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0210010004','梁冬云  ','男','1998-5-4','成都市中山路322号','2010-9-12','Cs021001')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0210010005','黄树军  ','男','2000-4-8','佛山市顺德区五一大道120号','2017-9-12','Cs021001')
 insert into Student([StudentID],[StudentName],[Sex],[Birth],[HomeAddr],[EntranceTime],[ClassID]) Values('St0210010006','雷立    ','男','1999-9-2',NULL,'2017-9-12','Cs021001')


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Course]
GO
--表 Course 的结构
Create table Course(
CourseID   char(8) primary key,
CourseName varchar(60) not null,
BookNmae  varchar(80) not null,
period int not null,
credit int not null
)
go
--表 Course 的数据
 insert into Course([CourseID],[CourseName],[BookNmae],[period],[credit]) Values('Dp010001','JAVA程序设计','Java高级程序设计',60,4)
 insert into Course([CourseID],[CourseName],[BookNmae],[period],[credit]) Values('Dp010002','计算机网络','计算机网络',90,6)
 insert into Course([CourseID],[CourseName],[BookNmae],[period],[credit]) Values('Dp010003','数据库原理与应用','数据库技术与应用-SQL Server 2005',60,4)
 insert into Course([CourseID],[CourseName],[BookNmae],[period],[credit]) Values('Dp010004','计算机应用基础','计算机应用基础',30,2)
 insert into Course([CourseID],[CourseName],[BookNmae],[period],[credit]) Values('Dp020001','国际贸易实务','国际贸易实务',60,4)
 insert into Course([CourseID],[CourseName],[BookNmae],[period],[credit]) Values('Dp020002','电子商务物流管理','物流学概论',60,4)
 insert into Course([CourseID],[CourseName],[BookNmae],[period],[credit]) Values('Dp030001','大学英语(一)','大学英语综合一',90,6)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Grade]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Grade]
GO
--表 Grade 的结构
Create table Grade(
CourseID   char(8) references Course(CourseID),
StudentID char(12) references Student(StudentID),
Semester  int not null,
SchoolYear int ,
Grade numeric(5,1) check(grade>=0),
primary key(CourseID,StudentID)
)
go
--表 Grade 的数据
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0109010001',2,2009,'87')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0109010002',2,2009,'68')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0109010003',2,2009,'92')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0109010004',2,2009,'91')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0109010005',2,2009,'72')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0109010006',2,2009,'87')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0109010007',2,2009,'97')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0109010009',2,2009,'69')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0109020001',2,2009,'56')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0109020002',2,2009,'89')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0109020003',2,2009,'98')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0109020004',2,2009,'76')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0210010001',2,2010,'88')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0210010002',2,2010,'72')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010001','St0210010003',2,2010,'71')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010002','St0111040001',2,2011,'54')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010002','St0111040002',2,2011,'92')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010002','St0111040003',2,2011,'95')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010003','St0109010001',2,2009,'50')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010003','St0109010002',2,2009,'67')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010003','St0109010003',2,2009,'61')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010003','St0109010004',2,2009,'91')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010003','St0109010005',2,2009,'55')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010003','St0109010006',2,2009,'68')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010003','St0109010007',2,2009,'69')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010003','St0109010009',2,2009,'55')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010003','St0109020001',2,2009,'93')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010003','St0109020002',2,2009,'70')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010003','St0109020003',2,2009,'73')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010003','St0109020004',2,2009,'81')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0109010001',2,2009,'80')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0109010002',2,2009,'63')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0109010004',2,2009,'57')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0109010005',2,2009,'94')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0109010006',2,2009,'89')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0109010007',2,2009,'83')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0109010009',2,2009,'82')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0109020001',2,2009,'72')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0109020002',2,2009,'57')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0109020003',2,2009,'53')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0109020004',2,2009,'51')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0111040001',1,2012,'55')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0111040002',1,2012,'63')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0111040003',1,2012,'61')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0210010001',1,2011,'99')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0210010002',1,2011,'71')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp010004','St0210010003',1,2011,'56')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp020001','St0210010002',2,2010,'64')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp020001','St0210010003',2,2010,'82')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp020001','St0210010004',2,2010,'95')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp020001','St0210010005',2,2010,'86')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0109010001',2,2009,'75')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0109010002',2,2009,'53')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0109010003',2,2009,'64')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0109010004',2,2009,'84')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0109010005',2,2009,'72')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0109010006',2,2009,'93')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0109010007',2,2009,'98')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0109010009',2,2009,'64')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0109020001',2,2009,'77')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0109020002',2,2009,'61')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0109020003',2,2009,'63')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0109020004',2,2009,'51')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0111040001',2,2011,'96')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0111040002',2,2011,'63')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0111040003',2,2011,'71')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0210010001',2,2010,'77')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0210010002',2,2010,'67')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0210010003',2,2010,'91')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0210010004',2,2010,'68')
 insert into Grade([CourseID],[StudentID],[Semester],[SchoolYear],[Grade]) Values('Dp030001','St0210010005',2,2010,'73')


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Teacher]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Teacher]
GO
--表 Teacher 的结构
Create table Teacher(
TeacherID   char(8) primary key,
Teachername char(12) not null,
Sex  char(2) not null check(sex in('男','女')),
Brith datetime ,
Profession char(8) check(profession in('教授','副教授','讲师','助教')),
Telephone varchar(20),
HomeAddr varchar(50),
DepartmentID char(4) references Department(DepartmentID)
)
go
--表 Teacher 的数据
 insert into Teacher([TeacherID],[Teachername],[Sex],[Brith],[Profession],[Telephone],[HomeAddr],[DepartmentID]) Values('dep01001','王一平      ','男','1968-4-5','副教授  ','86684567','东软宿舍29栋309','Dp01')
 insert into Teacher([TeacherID],[Teachername],[Sex],[Brith],[Profession],[Telephone],[HomeAddr],[DepartmentID]) Values('dep01002','潘清菊      ','女','1976-9-3','讲师    ','86685633','东软宿舍21栋109','Dp01')
 insert into Teacher([TeacherID],[Teachername],[Sex],[Brith],[Profession],[Telephone],[HomeAddr],[DepartmentID]) Values('dep01003','陈鹤        ','男','1959-12-5','教授    ','86687656','东软宿舍12栋306','Dp01')
 insert into Teacher([TeacherID],[Teachername],[Sex],[Brith],[Profession],[Telephone],[HomeAddr],[DepartmentID]) Values('dep01004','周江瑞      ','男','1984-6-5','助教    ','86684332','东软宿舍3栋306','Dp01')
 insert into Teacher([TeacherID],[Teachername],[Sex],[Brith],[Profession],[Telephone],[HomeAddr],[DepartmentID]) Values('dep01005','朱于龙      ','男','1978-5-6','讲师    ','86687653','东软宿舍1栋206','Dp01')
 insert into Teacher([TeacherID],[Teachername],[Sex],[Brith],[Profession],[Telephone],[HomeAddr],[DepartmentID]) Values('dep02001','雷燕        ','女','1973-3-2','副教授  ','86687651','东软宿舍1栋106','Dp02')
 insert into Teacher([TeacherID],[Teachername],[Sex],[Brith],[Profession],[Telephone],[HomeAddr],[DepartmentID]) Values('dep02002','郭菊        ','女','1985-6-7','助教    ','86680975','东软宿舍1栋406','Dp02')
 insert into Teacher([TeacherID],[Teachername],[Sex],[Brith],[Profession],[Telephone],[HomeAddr],[DepartmentID]) Values('dep02003','刘悠然      ','男','1969-12-9','教授    ','86685645','东软宿舍1栋306','Dp02')
 insert into Teacher([TeacherID],[Teachername],[Sex],[Brith],[Profession],[Telephone],[HomeAddr],[DepartmentID]) Values('dep03001','胡丽        ','女','1968-6-7','副教授  ','86687904','东软宿舍21栋108','Dp03')
 insert into Teacher([TeacherID],[Teachername],[Sex],[Brith],[Profession],[Telephone],[HomeAddr],[DepartmentID]) Values('dep03002','刘芳        ','女','1976-8-6','讲师    ','86684312','东软宿舍21栋208','Dp03')
 insert into Teacher([TeacherID],[Teachername],[Sex],[Brith],[Profession],[Telephone],[HomeAddr],[DepartmentID]) Values('dep03003','雷珍锦      ','女','1978-5-6','讲师    ','86687954','东软宿舍3栋206','Dp03')


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Schedule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Schedule]
GO
--表 Schedule 的结构
Create table Schedule(
TeacherID char(8) references Teacher(TeacherID),
CourseID char(8) references Course(CourseID),
ClassID char(8)  references Class(ClassID),
Semester int not null,
SchoolYear int not null,
primary key(TeacherID,CourseID,ClassID)
)
--表 Schedule 的数据
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep01001','Dp010003','Cs010902',2,2009)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep01001','Dp010003','Cs010903',2,2009)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep01001','Dp010004','Cs010901',2,2009)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep01001','Dp010004','Cs010902',2,2009)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep01002','Dp010002','Cs011104',2,2011)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep01003','Dp010004','Cs011104',1,2012)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep01003','Dp010004','Cs021001',1,2011)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep01005','Dp010001','Cs010901',2,2009)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep01005','Dp010001','Cs010902',2,2009)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep01005','Dp010001','Cs021001',2,2010)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep02001','Dp020001','Cs021001',2,2010)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep03001','Dp030001','Cs010901',2,2009)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep03001','Dp030001','Cs010902',2,2009)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep03001','Dp030001','Cs011104',2,2011)
 insert into Schedule([TeacherID],[CourseID],[ClassID],[Semester],[SchoolYear]) Values('dep03003','Dp030001','Cs021001',2,2010)

1.3.3 单表查询和多表查询

书上p42

• 单表查询
  • 只从一张表中获取数据
• 多表查询
  • 从多张表中获取数据

1.4 数据查询

这里是基本查询语句，例子都是单表查询的语句

1.4.1 SELECT语句

通过SELECT语句查询是检索数据库信息的唯一方式，是SQL语句中使用频率最高的语句。

SELECT [ALL|DISTINCT]   [TOP n[percent]]<目标列表达式>   [，<目标列表达式>] …
FROM <表名或视图名>[， <表名或视图名> ] …
[ WHERE <条件表达式> ]/*有条件的*/
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
/*对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚合函数。*/
[ ORDER BY <列名2> [ ASC|DESC ] ]；
/*对查询结果表按指定列值进行升序或降序排序 */

select * from student
--查询student表中所有数据

select student as 姓名,sex as 性别,studentid from student
where sex='女'
--从student表中查找student,studentid以及sex列为'女'的数据

可以在select后加top 5来查询前5个数据，或top 5 percent来查询前5%的数据。

组后加order by desc可以降序排序。

在条件中可以使用AND连接条件语句实现多重判断。
where sex='女' or studentname like '李%'
%通配任意位数，_通配一位。

1.4.2 条件表达式中常用运算符号

1. 比较：>,>=,<,<=,=,!=,<>(,!>,!<)
2. 范围：between …… and …… 包含边界的
3. 集合：in, NOT IN
4. 匹配：like (%:任意个字符，_:单个字符，[]:匹配[]中的任意一个字符，[^ ]不匹配[]中的全部字符) NOT LIKE
5. 空值：is null；is not null
6. 逻辑：and，or，not
7. 取查询结果某几行(top n[percent])
8. 去重结果重复项(distinct)
9. 排序：ORDER BY (ASC升序（默认）、DESC降序）

select * from student
where studentname not like '王%'
    and sex='男'
/*查询所有不姓王的男生*/

1.4.3 小实践

查询所有学生的学号、姓名和年龄

select StudentID,StudentName,floor(DATEDIFF(YEAR,Student.Brith,GETDATE())) from Student

floor()/*向下取整*/
GetDate()/*获取当前时间*/
datediff(datepart,startdate,enddate)
/*返回两个日期之间相差的时间*/

课后整理

1. 取消取值重复的行

在SELECT子句中使用DISTINCT短语

• DISTINCT关键字可以去掉查询结果中的重复行（所有字段相同才为重复），放在select的后面，目标列的前面

2.BETWENN …… AND ……

BETWEEN包含边界值、NOT BETWEEN不包含边界值！

WHERE salary BETWEEN 2000 AND 3000
--工资在2000和3000之间，包括2000和3000的人

除了使用BETWEEN AND外，还可以使用>= <=的方法来进行范围筛选。

3. 确定集合IN

可以指定一个表，表中列出所有可能的值，当表达式与表中的任一个值匹配时，就返回TRUE，否则返回FALSE

WHERE salary IN (2000,1800,4000)
--工资是2000或1800或4000

除了使用IN外，还可以使用=来进行判断。

4. 模糊查询：字符匹配LIKE

如果条件不确定，必须使用LIKE关键字进行模糊查询：

WHERE name LIKE 李%
--定位姓李的

• 用LIKE进行字符串匹配，将该字符串叫匹配串。用它与属性值进行比较。
• 匹配串中的通配符包括：%、_

5.通配符

• %：零个或多个任意字符串
• _：任意单个字符
• [ ]：在指定范围内的单个字符
  • LIKE '[AB]%'以A或B开头的字符串
• [^ ]：不在指定范围内的单个字符
  • LIKE '[^AB]%'表示不以A或B开头的字符串

由于通配符的存在，在不使用通配符的情况下要加上转义字符\。

6. 空值查询：IS NULL/IS NOT NULL

WHERE salary IS NULL
WHERE salary IS NOT NULL

NULL不是数值，它的含义是没有，因此不能用=等运算符进行判断。

7. WHERE子句中的逻辑运算符

• AND： 连接两个条件，仅当条件都为真才返回
• OR： 连接两个条件，只要其中一个为真就返回
• NOT： 否定一个条件，只有条件不成立才返回

当一条语句中使用了多个逻辑运算符时，首先求NOT的值，然后求AND的值，最后再求OR的值。

8. 查询语句中的排序

在SQL语句中，通过ORDER BY子句，可以将查询结果进行排序显示

排序默认升序，即使用ASC关键字，如果想要降序排序可以使用DES。

当ORDER BY子句指定了多个排序列时，系统先按照第一个参数进行排列，当出现相同值时，在按照第二个参数进行排序，以此类推。

9. 统计操作

在<目标列表达式>中常使用的聚合函数:

• Count([distinct|all]*)
  • 统计元组个数
• Count([distinct|all]<列名>)
  • 统计一列中值的个数
• sum([distinct|all]<列名>)
  • 求一列值的总和
• avg([distinct|all]<列名>)
  • 求一列值的平均值
• max([distinct|all]<列名>)
  • 求一列值中的最大值
• min([distinct|all]<列名>)
  • 求一列值中的最小值

上述函数，除count(*)外，其他函数均忽略null值。

但是count()指定了字段，就会忽略null值。

例子：

Select count(*) from student where 系别='计科系'
--查询计算机系的学生人数
Select count(distinct(学号)) as 人数 from choice
--查询选修了课程的学生人数
Select max(成绩),min(成绩),avg(成绩) from choice where 课程号= 'Dp0101001'
--查询Dp0101001课程的最高分、最低分和平均成绩

10. 对查询结果进行分组

带有GROUP BY子句的查询语句中，SELECT子句后面的结果集只能包含分组依据(GROUP BY子句后面的列)和聚合函数。

SELECT …… group by <分组依据>
[HAVING <组提取条件表达式>] 

--having的作用对象时组，即表示对分组后的记录进行筛选。

例子：

Select 学号 from choice group by 学号 having count(*)>3
--查询选修了3门以上课程的学生学号
select 课程号,COUNT(学号) AS 考试人数, MAX(成绩), MIN(成绩),AVG(成绩),SUM(成绩)
from Choice
GROUP  by 课程号
HAVING(AVG(成绩)>75)
--统计出平均分在75分以上每门课程的课程号、考试人数、最高成绩、最低成绩、平均成绩及总成绩。

11. group by子句与with rollup选项

group by子句将结果集分为若干个组，使用聚合函数可以对每个组内的数据进行信息统计，有时需要对各个组进行汇总运算，则需要在每个分组后加上一条汇总记录，这个任务可以通过with rollup选项实现

select classid,count(*)   from student
GROUP BY  classid with ROLLUP
--在同一张表中各班有若干名学生，按照班级号分类并进行统计显示

网课内容

• 逻辑结构

  • 表：用于存放数据，由行和列组成
  • 视图：可以看成是虚拟表或存储查询
  • 索引：用于快速查找所需信息
  • 存储过程：用于完成特定功能的SQL语句集
  • 触发器：一种特殊类型的存储过程

• 物理结构

  • 主数据库文件(.mdf)：存放DB的启动信息、部分或全部数据和数据库对象
    • 必须要有
  • 辅助数据库文件(.ndf)：存放除数据库文件外的数据和数据库对象
    • 可以根据需要设置一个或多个
  • 事务日志文件(.ldf)：用来存放恢复数据库所需的事物日志信息，记录数据库更新情况
    • 至少一个

• 系统数据库

  • master
    • 对用户数据库和SQL Server的操作进行总体控制。
    • 主要记录与sql server相关的所有系统级信息，包括登录账号、系统配置、数据库位置及实例的初始化信息等。
  • model
    • 提供创建新用户数据库所需的模板和原型
  • tempdb
    • 保存临时表盒其他临时存储过程，是sql server上所有数据库共享的工作空间。
    • 每次sql server启动时，都会重新创建一个tempdb数据库以保证该数据库总是空的；当用户断开数据库连接时，系统会自动删除临时表和存储过程。
  • msdb
    • 提供调度信息和作业历史记录所需的存储区，用于代理程序调度警报和作业等。

第2课

数据库检索顺序

首先检索表名，其次检索WHERE，再检索字段，最后的最后ORDER BY

查询指定的列或全部列

使用以下的几种方式指定字段列表：

字段列表：

• *：字段列表为数据源的全部字段
• 表明.*：多表查询时，指定某个表的全部字段。
• 字段列表：指定所需要显示的列

SELECT sex,count(HomeAddr)
--指定了HomeAddr会直接过滤掉NULL
FROM student
GROUP BY sex

--等价于
SELECT sex,count(*)
--指定了HomeAddr会直接过滤掉NULL
FROM student
WHERE HomeAddr IS NOT NULL
GROUP BY sex

SELECT COUNT(StudentID) 选修人数,COUNT(StudentID)-COUNT(Grade) 缺考人数,(COUNT(StudentID)-COUNT(Grade))/COUNT(StudentID) 百分比 
FROM Grade
WHERE CourseID = 'Dp010001'

网课

用T-SQL语句创建数据库

数据库主体代码：
CREATE DATABASE 数据库名
ON [PRIMARY](……
……)
LOG ON
(……
……)

数据库参数：

NAME = xkgl_data1,--指定数据文件或日志文件的逻辑名称
FILENAME = 'd:\db\xkgl_data1.mdf',--指定物理文件或日志文件的物理名称，包括路径和含后缀的文件名。
SIZE = 6MB,--指定数据文件或日志文件的初始大小，默认单位是MB，主文件必须至少是5MB
MAXSIZE = 30MB,--指定数据文件或日志文件可以增长到最大容量，默认单位为MB
FILEGROWTH = 2MB--指定数据文件或日志文件的增长幅度，默认单位为MB,也可以按百分比增长。

数据库分为3步：

1. 定义数据库的名字
2. 定义数据文件
3. 定义日志文件

CREATE DATABASE teacher
ON PRIMARY    /*创建主要数据文件*/
(
  NAME = teacher_data,
  FILENAME = 'D:\db\teacher_data.mdf',
  SIZE = 5MB,--默认字节单位MB可以省略
  MAXSIZE = 10,-- 文件最大容量10MB
  FILEGROWTH = 15%-- 增长量为文件容量15%
)
LOG ON    /*创建日志文件*/
(
  NAME = teacher_log,
  FILENAME = 'D:\db\teacher_log.LDF',
  SIZE = 500KB,       /*初始容量，KB单位不能省略*/
  MAXSIZE = UNLIMITED,  /*日志文件最大容量不受限制*/
  FILEGROWTH = 500KB  /*增长量KB不能省略*/
)

删除数据库

1. 使用SSMS工具删除，直接右键数据库，选择删除进行删除。
2. 使用T-SQL语句进行删除

DROP DATABASE <数据库名> [[,<数据库名>]…]

数据库的相关操作

1. 分离数据库
   • 分离数据库就是将某个数据库从SQL Server数据库列表中删除，使其不再被SQL Server管理和使用，但该数据库的数据文件和对应的日志文件完好无损。分离成功后，我们就可以吧数据文件和对应的日志文件拷贝到其他磁盘中作为备份保存。
   • 使用SSMS工具分离：
     • 右键数据库选择任务下的分离命令进行分离
     • 只有在“使用本数据库的连接”数为0时，该数据库才能分离。
2. 附加数据库
   • 附加数据库就是将一个备份磁盘中的数据文件和对应的日志文件拷贝到需要的计算机，并将其添加到某个SQL Server数据库服务器中，由该服务器来管理和使用这个数据库。
   • 使用SSMS工具附加数据库：
     • 右键数据库结点，选择附加命令。
     • 出现附加数据库对话框，单机添加按钮，出现定位数据库文件对话框，在这个对话框里，默认只显示数据库的主要文件，即mdf文件
3. 改变数据库状态(脱机、联机)
   • 数据库有脱机与联网两种状态，当一个数据库处于可操作、可查询的状态时就是联机状态，而一个数据库尽管可以看到其名字出现在数据库节点中，但对其不能执行任何有效的数据库操作时就是脱机状态。
   • 用SSMS工具脱机：
     • 右键数据库，选择任务下的脱机命令。
     • 弹出使数据库脱机对话框，单击关闭即可完成。
   • 用SSMS工具联机：
     • 右键数据库，选择任务下的联机命令。
     • 弹出使数据库脱机对话框，单击关闭即可完成。

完整性约束

完整性约束是为保证数据库中数据的正确性和相容性，而对关系模型提出的某种约束条件或规则。

1. 取值为空或者非空的约束
   • 取值为空或者非空的约束是对表中某个字段的取值进行限制。
   • 取值为空表示字段可以取空值。
   • 取值为空表示字段不可以取空值。
   • 空值表示不知道不确定的意思，不同于0。
2. 唯一性约束
   • 唯一性约束是指基本表在一个字段或者多个字段的组合上的取值唯一。
   • 定义了唯一性约束的字段称为唯一键，唯一键可为空，最多只能取一个空值。
3. 主码约束
   • 主码约束是指将表中一个字段或者多个字段的组合定义为该表的主码，主码的取值不能为空，且表中任意两行在主码上的取值不能相同。(唯一且非空)
4. 外码约束
   • 外码约束是指将表中的一个字段定义为外码，外码的取值可以为空或者等于另外一张表中某个元组的主码值。
   • 含外码的这张表叫子表，另一张表叫父表。
5. 默认值约束
   • 默认值约束是指为某个字段指定默认值，当为某个字段指定默认值后，想表中输入数据时如果不为该字段指定其它值，系统将取默认值。
6. 列值约束
   • 列值约束是用来指定某个字段的取值范围。

主码约束和唯一性约束的区别：

1. 在一个基本表中只能定义一个主码约束，但可定义多个唯一性约束。
2. 被指定为主码的字段，不能出现空值，而被定义为唯一键的字段，则允许为空，但最多只能取一个空值。

创建表

表是数据库中最重要的对象，用于存储用户数据，

创建新标的实质是定义新表的结构，其中有三大要素需要确定，分别是：

• 列名(易于理解，不可重名，长度合适)
• 数据类型
• 约束条件

数据类型：

• 数值型
  • 整形
    • tinyint
    • smallint
    • int
    • bigint
  • 定点型
    • decimal
    • numeric
  • 浮点型
    • float
    • real
• 字符型
  • 普通字符型
    • char
    • varchar
    • text
  • Unicode字符型
    • nchar
    • nvarchar
    • ntext
• 日期时间型
  • datatime
  • smalldatetime
• 货币型
  • money
  • smallmoney
• 二进制字符型
  • binary
  • varbinary
  • image

1. 用SSMS工具来创建表

• 右键数据库后选择表中的表命令后，定义列名、数据类型以及设定是否允许Null值。
• 右键列名可以选择设置主键。
• 所有的特殊操作都右键操作。

2. 用T-SQL语句创建表

   CREATE TABLE <表名>(
     <列名><数据类型>[列级完整性约束的定义]
     [,<列名><数据类型>[列级完整性约束的定义]……]
     [,表级完整性约束的定义]
   )

完整性约束名

1. 取值为空或者非空约束
   • NULL/NOT NULL
   • 只能定义为“列级完整性约束”
   • sname char(10) NOT NULL
   • 默认是允许为空
2. 唯一性约束
   • UNIQUE
   • 可以定义为“列级完整性约束”，也可以定义为“表级完整性约束”。
     • 列级约束：在定义列时定义唯一约束
       • <列名><数据类型>unique
     • 表级约束：在定义完列之后定义唯一约束
       • unique(<列名序列>)
3. 主码约束
   • PRIMARY KEY
   • 可以定义为“列级完整性约束”，也可以定义为“表级完整性约束”。
     • 列级约束：在定义列时定义主码(仅用于主码由一个字段构成)
       • <列名><数据类型>PRIMARY KEY
     • 表级约束：在定义完列时定义主码(用于主码由一个字段或者多个字段构成)
       • PRIMARY KEY(<列名序列>)
   • 主码由一个字段或者多个字段构成时，只能定义为“表级完整性约束”。
4. 外码约束
   • 参照完整性
   • FOREIGN KEY
   • 可以定义为“列级完整性约束”，也可定义为“表级完整性约束”。
     • 列级约束：在定义列时定义外码
       • <列名> <数据类型> REFERENCES <父表名>(<父表的主码列名>)
     • 表级约束：在定义完列时定义外码
       • FOREIGN KEY (<列名>) REFERENCES <父表名> (<父表的主码列名>)
5. 默认值约束
   • DEFAULT
   • 只能定义为“列级完整性约束”
   • sex char(2) DEFAULT '女'
6. 列值约束：限制列的取值范围的约束
   • CHECK
   • 可以定义为“列级完整性约束”，也可以为“表级完整性约束”
     • 列级约束：在定义列时定义约束
       • <列名> <数据类型> CHECK <表达式>
     • 表级约束：在定义完列时定义约束
       • CHECK <表达式>

Create table Class(
  ClassID char(8) primary key,
  ClassName varchar(80) not null,
  Monitor char(8),
  StudentNum int check(StudentNum>0),
  DepartmentID char(4) references
  Department(DepartmentID)
)

第3课

JOIN

SELECT Course.CourseID,Course.CourseName,Student.StudentName,Grade.StudentID
FROM Course 
JOIN Grade 
ON Course.CourseID = Grade.CourseID
JOIN Student
ON Student.StudentID=Grade.StudentID

JOIN多表连到一起要分开JOIN ON

指定的列名前面要加表明避免产生二义性，为了化简可以简写表明

网课

用T-SQL语句修改表结构

在T-SQL中采用ALTER TABLE语句修改表结构，通过ALTER TABLE语句可以对表添加列、删除列、修改列的定义，也可以添加和删除约束。

ALTER TABLE <表名>
[ALTER COLUMN <列名> <新数据类型>] --修改列定义
|[ADD <列名> <数据类型> [列级完整性约束定义]]  --添加新列
|[DROP COLUMN <列名>] --删除列
|[ADD [CONSTRAINT <约束名>]约束定义]  --增加约束
|[DROP <约束名>]  --删除约束

删除数据表

1. 用SSMS工具删除数据表
   • 右键表，选择删除命令，点击确定即可删除
2. 用T-SQL语句删除数据表
   • DROP TABLE <表名>[[,<表名>]……]

数据库关系图

在SQL Server中，可以使用数据库关系图来创建和管理一个数据库中表之间的关系。数据库关系图作为数据库的一部分，存储在数据库中。关系图的建立可以使表间的关系以图形的方式加以显示，更加清晰的表现出表之间的关联。

1. 使用ssms工具创建关系图
   • 右击数据库下数据库关系图，选择新建数据库关系图命令后添加表即可。

数据的导入与导出

右键数据库选择导入数据导入就行了

常用字符串函数

• LTRIM()
  • 函数把字符串头部(左边)的空格去掉
• RTRIM()
  • 函数把字符串尾部(右边)的空格去掉
• LEFT()、RIGHT()、SUBSTRING()
  • 函数返回部分字符串

SELECT RTRIM(StundentName)
FROM student

SELECT SUBSTRING('abc',2,2) --取abc中第二个开始后的两个字符

SELECT RIGHT('abc',2) -- 与上同

UPDATAE <表名> SET <列名>

内连接

• 自连接是一种特殊的内连接，相互连接两张表在物理上的是同一张表，但必须用别名将其在逻辑上划分为两张表。
• 由于所有属性名都是同名属性，因此必须使用别名前缀。

外连接

• 内连接要求满足条件的记录才会显示，外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件的连接方式，外连接中的两张表的关系是不平等的。

外连接的三中类型：

1. 左外连接(LEFT OUTER JOIN)
   • 左外连接即在连接两表时，不管右表中是否有匹配数据，结果将保留左表中所有行。左表是主表，右边满足条件的记录才显示。
2. 右外连接(RIGHT OUTER JOIN)
   • 右连接原理同上
3. 全外连接(FULL OUTER JOIN)
   • 原理一样，没有数据的时候用NULL来代理

第4课

课程回顾

查询计算机系的教师所有信息

SELECT t.*
FROM Teacher t
JOIN Department d
on t.DepartmentID = d.DepartmentID
where DepartmentName='计算机系'

查询信管系的所有学生信息

SELECT s.*
FROM Student s
JOIN Class c
ON c.ClassID = s.ClassID
JOIN Department d
ON d.DepartmentID = c.DepartmentID
WHERE DepartmentName='信管系'

左外查询

左外连接确保左边的数据都会出现在表中，右外连接同理

FROM 1 LEFT JOIN 2 ON XXXX

查询所有学生的学号，姓名，课程编号，成绩

SELECT s.StudentID,StudentName,CourseID,Grade
FROM Student s
LEFT JOIN Class c
ON c.ClassID = s.ClassID
LEFT JOIN Grade g
ON g.StudentID = s.StudentID

条件转换

有时在查询中需要将结果以另外一种形式显示出来可以使用CASE子句指定数据的转换

SELECT Teachername,Profession,
CASE 
  WHEN Profession = '助教' THEN '初级'
  WHEN Profession = '讲师' THEN '中级'
  ELSE '高级'
END 等级
FROM Teacher

用CASE时候，SELECT结尾一定要加,！

上面是搜索型CASE函数，可以使用逻辑运算符。

下面一种是简单型CASE！无法使用逻辑运算符！

SELECT StudentID,Sex,
CASE Sex
	WHEN '男' THEN  '先生'
	WHEN '女' THEN '女士'
	ELSE '不详'
END 称呼
FROM Student

区别就是简单型CASE指定了列，可以每一个WHEN指定。

WHEN具有逻辑优先的！如果按类似分数分类，一定要分高的先WHEN！

子查询

不相关子查询

没有内层查询，外层依然能查询出数据，只是不够精确而已。

查询与张宏同日生的同学：

SELECT StudentID,StudentName,Birth
FROM Student
WHERE Birth = (
	SELECT Birth
	FROM Student
	WHERE StudentName = '张宏')

子查询部分不能使用ORDER BY排序。

交集 intersect

子查询的谓词

子查询的结果可以是单个属性值，也可以是一个集合

子查询只返回一个值时：用比较符或IN/NOT IN

子查询返回多个值时：

• (IN或NOT)：是或不是集合中的一个值
• (<>,<,>)ALL

没记完

带有EXISIT

依然没记完

网课

嵌套查询

• 子查询可以被用于：

  • 集合测试
  • 比较测试
  • 存在性测试

• 使用子查询需要注意

  • 子查询可以嵌套多层
  • 子查询需要圆括号()括起来
  • 子查询不能使用ORDER BY语句

• ANY

• ALL

带有EXISTS的子查询只返回布尔值

存在性子查询先进行父查询，然后进行子查询

网课

视图

CREATE VIEW 名称
WITH ENCRYPTION --加密
AS
SELECT ……
SELECT ……

WITH CHECK OPTION
--通过视图进行的修改，必须也能通过该视图看到修改后的结果。

索引

聚集索引

物理空间上连续

非聚集索引

变量及打印

变量类型

• 局部变量
  • 局部变量前加@，如@mystr
  • 局部变量由用户定义，使用时先声明，再赋值
• 全局变量
  • 全局变量前加@@，如@@error
  • 全局变量由系统应以和维护，我们只能读取，不能修改全局变量的值。

声明局部变量

• 基本格式

DECLARE @变量名 数据类型 [,……n]

注：一次可以声明多个变量，变量用逗号隔开，所有局部变量在声明后均初始化为NULL

DECLARE @stuName CHAR(8)
DECLARE @stuBirthday DATETIME
--等价于
DECLARE @stuName CHAR(8),@stuBirthday DATETIME

• 局部变量赋值
  • 局部变量赋值的方法有SET和SELECT两种，SET语句一次只能给一个变量赋值，SELECT语句可同时为多个变量赋值，语法如下：

SET @变量名 = 值
SELECT @变量名 = 值

SET @stuName = '张三'
SELECT @stuBirthday = '1998-10-12'
--等价于
SELECT @stuName = '张三',@stuBirthday = '1998-10-12'

PRINT @stuName
PRINT @stuBirthday

查询结果有多条时，只能接收一条！

全局变量

• 由系统提供且预先声明的变量

• 用户只能引用

• 使用@@

• 用户不能定义和全局变量同名的局部变量，否则报错

• 常用全局变量

  • @@ERROR
    • 最后一个T-SQL错误的错误号
  • @@IDENTITY
    • 最后一次插入的标识值
  • @@LANGUAGE
    • 当前使用的语言的名称
  • @@MAX_CONNECTIONS
    • 可以创建的同时连接的最大数目
  • @@ROWCOUNT
    • 受上一个SQL语句影响的行数
  • @@SERVERNAME
    • 本地服务器的名称
  • @@TRANSCONUNT
    • 当前连接打开的事务数
  • @@VERSION
    • SQL Server的版本信息

流程控制-IF

IF 布尔表达式
  BEGIN
    语句1
    语句2
    ……
  END
ELSE
  BEGIN
    语句1
    语句2
    ……
  END

• 允许嵌套
• 如果BEGIN……END中间只有一行程序，则可以省略BEGIN与ENDS

IF EXISTS

存在数据时执行，加上NOT则相反：

IF [NOT] EXISTS (SELECT查询语句)
  T-SQL语句|语句块
[ELSE]
  T-SQL语句|语句块

流程控制-CASE

• 简单型
• 查找型

前面记过了==、

流程控制-WHILE

WHILE <布尔表达式>
BEGIN
  <T-SQL语句|语句块>
  [BREAK]
  [CONTINUE]
  <T-SQL语句|语句块>
END

存储过程概述

• 优点

  • 执行速度更快——在数据库中保存的存储过程收拾编译过的
  • 允许模块化程序设计——类的方法的复用
  • 提高系统安全性——防止SQL注入
  • 减少网络流量——只需传输存储过程的名称

• 缺点

  • 增大了数据库压力

• 存储过程分类

  • 系统存储过程
    • 由系统定义，存放在master数据库中
    • 系统存储过程的名称都以sp_开头或xp_开头
  • 用户自定义存储过程
    • 由用户在自己的数据库中创建的存储过程

• 常用系统存储过程

  • sp_databases
    • 列出服务器上的所有数据库
  • sp_helpdb
    • 报告有关指定数据库或所有数据库的信息
  • sp_renamedb
    • 更改数据库的名称
  • sp_rename
    • 更改数据库的名称
  • sp_tables
    • 返回当前环境下课查询的对象的列表
  • sp_columns
    • 返回某个列的信息
  • sp_help
    • 查看某个表的所有信息
  • sp_helpconstraint
    • 查看某个表的约束
  • sp_helpindex
    • 查看某个表的索引
  • sp_stored_procedures
    • 列出当前环境中的所有存储过程
  • sp_passwd
    • 添加或修改登录账户的密码
  • sp_helptext
    • 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本

举个栗子：

EXEC sp_help student
--查看某个表的所有信息

存储过程的创建和执行方法

创建存储过程语法

CREATE PROC | PROCEDURE 存储过程名
[@参数名 数据类型 [=default] [OUTPUT]] [,……]
[WITH ENCRYPTION]
AS
BEGIN
  <SQL语句>[,……]
END

• 参数分为输入参数、输出参数两种，OUTPUT指示参数是输出参数
• 输入参数允许有默认值，DEFAULT设置参数的默认值
• ENCRYPTION：将CREATE PROCEDURE语句的原始文本加密
• AS：指明该存储过程将要执行的动作

执行存储过程

EXEC 存储过程名 [参数1] [,参数2] [,……]

带有默认值的存储过程同C++语法

• 带有输出参数的存储过程
  • EXEC 存储过程名 变量名 OUTPUT

第五课

放假休息

第六课

复习

带有EXISTS谓词的子查询

查询所有选修了Dp010001号课的学生姓名

SELECT StudentName
FROM Student
WHERE EXISTS(
  SELECT *
  FROM GRADE
  WHERE Student.StudentID = Grade.StudentID
  AND CourseID = 'Dp010001'
)

数据类型

• char类型指定了长度不够会自动补全

• varchar类型指定了长度不够会释放内存

• date

  • 日期

• time

  • 时间

• datetime

  • 日期和时间

• unique允许一行为null

网课

自定义函数-标量函数

• 内置系统函数
  • 聚合函数
  • 日期和时间函数
  • 数学函数
  • 字符串函数
• 用户自定义函数
  • 标量函数
    • 返回的是一个单值，可以在select和where子句进行函数调用
  • 表值函数
    • 返回table数据类型，只能在select子句的from子句中调用
    • 内嵌表值函数
    • 多语句表值函数

标量函数创建的语法

CREATE FUNCTION 函数名
  (@参数名 参数数据类型[=default][,……n])
  RETURNS 返回值数据类型
AS
  BEGIN
    T-SQL语句
    RETURN 表达式
  END

1. 只能是输入参数，不能有输出参数，所有的输入参数前都必须加@
2. create后的返回，单词是returns，而不是return
3. returns后面跟的不是变量，而是返回值的类型，如：int,char等
4. 在begin/end语句块中，是return

• 创建函数get_sname(),通过学号返回姓名，并利用该函数查询选修成绩不及格学生的姓名。

CREATE FUNCTION get_sname(@stuNO char(12))
RETURNS char(8)
AS
BEGIN
    DECLARE @sname char(8)
    SELECT @sname=StudentName from student
    WHERE StudentID=@stuNo
  RETURN @sname
END

--调用函数
SELECT dbo.get_sname(StudentID) AS 姓名 FROM Grade WHERE Grade<60

创建内嵌表值函数

• 内嵌表值函数创建的语法

CREATE FUNCTION 函数名(@参数名 参数数据类型[,……n])
RETURNS TABLE
AS
  RETURN(T-SQL语句)

1. 只能返回table，所以returns后面一定是TABLE
2. AS后没有begin/end，只有一个return语句来返回特定的记录。

• 创建一个自定义内嵌表值函数getStuInfo，返回指定班级的学生的学号，姓名，性别，班级名称。

CREATE FUNCTION getStuInfo(@classNo CHAR(8))
RETURNS TABLE
AS
RETURN(SELECT StudentID,StudentName,Sex,ClassName
  FROM Student,Class
  WHERE Class.ClassID=@classNo
  AND Student.ClassID=Class.ClassID
)

--调用方式
SELECT * FROM dbo.getStuInfo('Cs010902')

触发器

触发器的概念

触发器是一段由对数据的更改操作引发的自动执行的代码。通常用于保证业务规则和数据完整性，其主要优点是用户可以用编程的方式来实现复杂的处理逻辑和业务规则，增强了数据完整性约束的功能。

• 触发器的分类

  • DML触发器
    • 当数据库服务器中发生数据操作语言事件，如INSERT``ALTER``UPDATE等操作触发的触发器。
    • 后触发器 AFTER触发器
      • 操作完成后再被激活执行触发器里的SQL语句
    • 前触发器 INSTEAD OF触发器
      • 对记录的操作进行之前就被激活，执行触发器中的SQL语句，而不再执行原来的SQL操作。
    • 当触发器触发时，系统自动在内存中创建deleted表或inserted表，这两个表的结构同建立触发器的结构完全相同，这两张表是只读的，不允许修改，触发器执行完成后自动删除
  • DDL触发器
    • 当数据库服务器中发生数据定义语言事件，如CREATE``ALTER``DROP等操作出发的触发器

• 创建DML触发器

  • 触发器名称要符合命名规范，且必须唯一
  • FOR和AFTER均表示后触发器，INSTEAD OF表示前触发器
  • DELETE,INSERT和UPDATE表示引发触发器执行的操作，如果同时指定多个操作，各个操作用逗号分隔

CREATE TRIGGER 触发器名称
ON 表名
{FOR|AFTER|INSTEAD OF}
{[DELETE][,][INSERT][,][UPDATE]}
AS
  SQL语句|语句块

后触发器

CREATE TRIGGER tri_After
ON Class AFTER INSERT
AS
  SELECT * FROM Class
  SELECT * FROM inserted

• 在Grade表上创建一个后触发器，当成绩字段修改或新数据插入后触发，检查分数是否在0-100之间。

CREATE TRIGGER tri_grade
ON Grade
AFTER INSERT, UPDATE
AS
  IF EXISTS(SELECT * FROM inserted WHERE grade<0 OR grade>100)
BEGIN
  PRINT'输入的分数应该在0-100之间，请确认输入的考试分数！'
  ROLLBACK
END
ELSE
  PRINT'修改成功！'

前触发器

INSTEAD OF触发器是在对记录的操作进行之前就被激活，执行触发器中的SQL语句，而不再执行原来的SQL操作。

• 在student表上创建一个前触发器，在录入学生信息时，限制班级人数不能超过10人。

CREATE TRIGGER tri_Instead2
ON student
INSTEAD OF INSERT
AS
  DECLARE @classno char(8)
  SELECT @classno=ClassID
  FROM inserted
  IF(SELECT COUNT(*) FROM STUDENT WHERE ClassID=@classno)<10
  BEGIN
    INSERT INTO student SELECT * FROM inserted
    SELECT * FROM Student WHERE ClassID=@classno
  END
ELSE
  PRINT'班级人数已满'

安全管理

1. 身份验证
2. 操作权控制
3. 文件操作控制
4. 加密存储与冗余

• 登录账户来源

  • Windows授权用户
  • SQL Server授权用户

• 安全认证模式

  • Windows身份验证模式
  • 混合身份验证模式

• SQL Server登录账户

  • SQL Server的安全权限是基于用户登录标识符的，没有有效的登录ID，用户无法连接数据库服务器
  • SQL Server身份验证登录，默认的登录名是sa，拥有系统管理员权限

--使用SQL语句创建登录账号
CREATE LOGIN SQL_USERNAME 
WITH PASSWORD='XXXXXX'

---删除登录账号
DROP LOGIN login_name
--不能删除正在使用的登录用户，也不能删除拥有任何数据库和服务器级别对象的登录用户

--为某一数据库创建用户
CREATE USER SQL_USERNAME

--删除用户
DROP USER USER_NAME
--不能删除拥有对象的用户
--用户的删除，不影响登录账号的存在，反过来也一样

• 登录账户和数据库用户的关系

  • 一个登录账号可以映射为多个数据库用户，但一个登录名在每个数据库中只能映射一次

• 对象管理权限

  • 用户创建和管理数据库中表、视图等对象的权限
  • CREATE DATABASE
  • CREATE TABLE
  • CREATE VIEW
  • CREATE PROCEDURE
  • BACKUP DATABASE
  • BACKUP LOG

• 数据操作权限

  • 对数据库中表、视图中的数据进行查询、增加、删除和修改的权限
  • INSERT
  • DELETE
  • UPDATE
  • SELECT
  • EXECUTE

• 隐含权限

  • 内置权限，不需要再明确地授予这些权限

• 权限的管理

  • 授予权限
    • 授予用户或角色具有某种操作权
  • 收回权限
    • 收回(或撤销)曾经授予给用户或角色的权限
  • 拒绝权限
    • 拒绝某用户或角色具有某种操作权限。一旦拒绝了用户的某个权限，则用户从任何地方都不能获得该权限

--授权语句
GRANT 权限名[,……]
  TO{数据库用户名|用户角色名}[,……]

--收权语句
REVOKE 权限名[,……]
  FROM{数据库用户名|用户角色名}[,……]

--拒绝语句
DENY 权限名[,……]
  TO{数据库用户名|用户角色名}[,……]

• 角色
  • 为便于对角色及权限的管理，可以将一组具有相同权限的用户组织在一起，这一组具有相同权限的用户就称为角色。
  • 3类
    • 固定的服务器角色
      • bulkadmin
        • 具有执行BULK,INSERT语句的权限
      • dbcreator
        • 具有创建数据库的权限
      • diskadmin
        • 具有管理磁盘资源的权限
      • processadmin
        • 具有管理全部的连接以及服务器状态的权限
      • securityadmin
        • 具有管理服务器登录账户的权限
      • serveradmin
        • 具有全部配置服务器范围的设置
      • setupadmin
        • 具有更改任何链接服务器的权限
      • sysadmin
        • 系统管理员角色，具有服务器及数据库上的全部权限
      • public
        • 每个登录账户自动拥有，不能对其进行授权
    • 固定的数据库角色
      • db_accessadmin
        • 具有添加或删除数据库用户的权限
      • db_backupoperator
        • 具有备份数据库、日志的权限
      • db_datareader
        • 具有查询数据库中所有用户表数据的权限
      • db_datawriter
        • 具有更改数据库中所有用户表数据的权限
      • db_ddladmin
        • 具有建立、修改和删除数据库对象的权限
      • db_denydatareader
        • 不允许具有查询数据库中所有用户表数据的权限
      • db_denydatawriter
        • 不允许具有更改数据库中所有用户表数据的权限
      • db_owner
        • 具有数据库中的全部操作权限
      • db_securityadmin
        • 具有管理数据库角色和角色成员以及数据库中的对象管理和数据操作的全部权限
    • 用户自定义的角色
      • 用户自定义的角色属于数据库级别的角色
      • 用户的成员可以是数据库的角色，也可以是用户定义的角色

--在固定的服务器角色中添加成员用系统存储过程sp_addsrvrolemember:
sp_addsrvrolemember 登录名,角色名

--在固定的服务器角色中删除成员用系统存储过程sp_dropsrvrolemember:
sp_dropsrvrolemember 登录名,角色名

--创建新角色的语法
CREATE ROLE 新角色名 [AUTHORIZATION拥有者]
--新角色没有权限，要进行授权

--删除
DROP ROLE 名字
--不能删除拥有安全对象的角色
--不能从数据库中删除拥有成员的角色
--不能删除固定的数据库角色

网课

备份和恢复数据库

数据故障

• 系统故障

  • 造成系统停止运转的任何事件，是的系统要重新启动。
  • 特定类型的硬件错误(如CPU故障)
  • 操作系统故障
  • 数据库管理系统代码错误
  • 系统断电

• 事务故障

  • 某事物在未运行至正常终止点就夭折了，可以分为可预期的和不可预期的两类。
    • 可预期的事物故障：取款余额不足，买票以售完等。
    • 非预期的事物故障：运算溢出、并发事物发生死锁等。

• 介质故障

  • 磁盘损坏
  • 磁头碰撞
  • 瞬时强磁场干扰
  • 介质故障比前两类故障的可能性小得多，但破坏性大得多

• 故障影响

  • 数据库本身被破坏，需要通过备份的数据库还原数据库。
  • 数据库没有被破坏，但数据可能不正确，可以通过日志文件恢复。

备份数据库

• 数据库的安全性和可靠性必不可少，为了保障数据库的正常运行，就必须做好数据库备份。

• 备份数据库就是将数据库中的数据和保证数据库系统正常运行的相关信息保存起来，以备系统出现问题时，恢复数据库时使用。

• 备份内容

  • 系统数据库
  • 用户数据库

• 备份时间

  • 系统数据库：变化频率低，修改之后备份
  • 用户数据库：周期性备份，备份周期由用户需求决定，时间点选在数据操作少的时候进行。

备份策略

• 备份策略

  • 备份策略的制定
    • 确定备份类型
    • 频率
    • 备份所需硬件特征和速度
    • 备份的测试方法
    • 备份所存放的位置
  • 考虑的因素
    • 业务需求
    • 数据库特征
    • 对资源的约束

• 策略1：完整备份

  • 适合于数据库数据不是很大，而且数据更改不是很频繁地情况，会丢失部分数据。

• 策略2：完整备份+事务日志备份

  • 不希望经常地进行完成备份，则可以在完整备份中间加一些日志备份。

• 策略3：完整备份+差异备份+事务日志备份

  • 优点是备份和恢复的速度都比较快，而且当系统出现故障时，丢失的数据也比较少。

SQL Server的备份机制

• 备份设备

• 恢复模式

• 备份类型

• 实现备份

• 备份设备

  • SQL Server中备份数据库的场所被称为备份设备，逻辑备份设备是指数据库备份的逻辑名，物理备份设备是操作系统上数据文件。
  • 备份设备可以分为：
    • 永久备份设备需要在备份之前需要预先建立。
    • 临时备份设备不需要预先建立，在备份时直接使用。

• 创建备份设备

  • 创建备份设备可以使用系统存储过程sp_addumpdevice。
    • 语法规则:sp_addumpdevice 备份数倍类型,逻辑名,物理文件名
  • 例如建立一个名为bk2的此版备份设备，物理存储位置以及文件名为：D:\dump\bk.bak
    • sp_addumpdevice 'disk','bk2','D:\dump\bk2.bak'

• 恢复模式

  • 简单恢复
    • 不备份事物日志
    • 丢失风险高
    • 适用于测试开发
  • 完整恢复
    • 完整记录事务
    • 可恢复到故障点
    • 适用于实际应用
  • 大容量日志
    • 记录大容量操作
    • 有一定风险
    • 作为完整恢复模式的附加模式

• 备份类型

  • 数据库的恢复模式决定了可以使用的备份类型，数据库备份类型决定所备份的内容。
  • SQL Server中的备份类型包括：
    • 数据库备份(完整备份和差异备份)
    • 文件备份
    • 事务日志备份
  • 文件备份可使用分离和附加数据库的功能

• 数据库备份

  • 完整备份
    • 完整数据库备份是所有备份方法中最基本也是最重要的备份，也是差异备份的基准。
    • 备份数据库中的全部信息，包括数据文件、日志文件，文件存储的位置信息以及数据库全部对象。
    • 消耗较长时间和资源，但不影响用户使用。
  • 差异备份
    • 差异备份以前一次完整备份为基准点(差异基准)，备份从上次完整备份之后数据库的全部变化内容。
    • 差异备份通常速度快，耗时短，但较为复杂。差异备份的时间和大小取决于自建立差异基准后更改的数据量。通常，差异基准越旧，新的差异备份就越大。
  • 事务日志备份
    • 事务日志备份，不备份数据库本身，它只备份日志记录，而且只备份从上次备份之后到当前备份时间发生变化的日志内容。
    • 可将数据库恢复到故障点或特定的某个时间点。
    • 比完整备份和差异备份使用的资源少，但只能在完整恢复模式和大容量日志恢复模式下使用。

• SQL语句备份数据库

BACKUP DATABASE 数据库名
TO 备份设备名[WITH [DIFFERENTIAL][[,]{INIT|NOINIT}]]

• DIFFERENTIAL:差异备份

• INIT:本次备份将重写备份设备

• NOINIT:本次备份将追加到备份设备。默认项

• SQL语句备份事务日志

BACKUP LOG 数据库名 TO{<备份设备名>}
[WITH [{INIT|NOINIT}],NORECOVERY][{[,]NO_LOG|TRUNCATE_ONLY|NO_TRUNCATE}]

• NORECOVERY:尾部日志
• NO_LOG|TRUNCATE_ONLY:截断日志
• NO_TRUNCATE:不截断日志

恢复数据库

• 如果数据库没有毁坏，则应先对数据库的访问进行一些必要的限制。因为在恢复数据库的过程中，不允许用户操作数据库。

• 如果数据库的日志文件没有损坏，则为尽可能减少数据丢失，可在恢复之前对数据库进行一次尾部日志备份。

• 恢复数据库的顺序

  1. 最近的完整数据库备份
  2. 最近的差异数据库备份
  3. 按备份顺序还原日志

• SQL语句恢复数据库

RESTORE DATABASE 数据库名
FROM 备份设备名
[WITH FILE = 文件号,NORECOVERY|RECOVERY]

• FILE = 文件号：标识要还原的备份，文件号为1标识备份设备上的第一个备份

• NORECOVERY：表明对数据库的恢复还没有完成

• RECOVERY：表明对数据库的恢复已经完成

• SQL语句还原事务日志

RESTORE LOG 数据库名
FROM 备份设备名
[WITH FILE = 文件号,NORECOVERY|RECOVERY]

• 参数含义和上面那个一样的

网课第九单元

9.1 数据模型

• 实体

  • 实体名
  • 属性
    • 取值不可为空的属性，强制的*标注
    • 取值可以为空的属性，可选的o标注

• 实例

  • 实体中每个属性赋予具体的值，形成实例

• 唯一标识符(UID)

  • 对于所有实例，取值各不相同(唯一)的属性集，称为唯一标识符，用#标注
  • 每个实体有且仅有一个UID
  • UID并非总是属性，也可为属性集

• ER模型：

  • 描述世界的数据模型称为概念模型
  • ER模型为最常用的概念模型

9.2 联系

• 联系

  • 实体与实体之间的关联，用联系来表示
  • 联系描述实体之间的对应关系
  • 一对一比较常见
  • 其实就是函数的映射关系

• 阅读规则

  • 每个
  • 实体A
  • 必须/可以(实体A一侧的线为实线/虚线)
  • 联系动词(实体A一侧的联系动词)
  • 一个或多个/一个且仅有一个(实体A对侧的线 鸡爪线/单线)
  • 实体B

9.3 多对多

9.4 关系数据库的基本概念

• 笛卡尔积

• 关系

  • 就是二维表，满足如下性质：
    • 关系表中的每一列都是不可再分的基本属性
    • 表中个属性不能重名
    • 表中的行、列次序并不重要

• 属性

  • 二维表中的每个列称为一个属性(或叫字段)
  • 每个属性有一个名字，称为属性名。
  • 二维表中对应某一列的值称为属性值。

• 二维表中列的个数称为关系的元数。如果一个二维表有n个列，则称其为n元关系。

• 关系数据库：对应于一个关系模型的所有关系的集合称为关系数据库。

• 候选码：如果一个属性或属性集的值能够唯一标识一个关系的元组而又不包含多余的属性，则称该属性或属性集为候选码

  • 候选码也称为候选键或候选关键字
  • 在一个关系上可以有多个候选码

• 主码

  • 当一个关系中有多个候选码时，可以从中选择一个作为主码
  • 每个关系只能有一个主码
  • 主码也称为逐渐或主关键字，用于唯一确定一个元组
  • 主码可以由一个属性组成，也可以由多个属性共同组成

• 主属性和非主属性

  • 包含在任一候选码中的属性称为主属性
  • 不包含在任一候选码中的属性称为非主属性

9.5 函数依赖

9.6 关系规范化

• 数据冗余问题
• 异常
  • 更新异常(Update Anomalies)
  • 插入异常(Insert Anomalies)
  • 删除异常(Delete Anomalies)
• 关系规范化
  • 规范化的程度，可以分为：
    • 1NF
    • 2NF
    • 3NF
    • BCNF,4NF,5NF
  • 满足高阶范式的关系模式一定满足低阶范式
    • 譬如满足3NF,则一定已经满足1NF及2NF
    • 范式越高，规范化程度越好

9.7 1NF和2NF

1NF

定义：如果关系模式R中所有的属性都是基本属性，即每个属性都是不可再分的，则称R属于第一范式，简称1NF，记作R∈1NF

2NF

定义：如果关系模式R∈1NF，并且R中的每个非主属性都完全函数依赖于主码，则称R属于第二范式，简称2NF，记作R∈2NF

9.3 3NF

• 如何使关系模式达到2NF，分解步骤共三步：

  1. 将原关系模式主码属性集合的每一个子集作为主码分别构成相应的表。
  2. 将完全依赖于这些主码的属性放置到相应的表中
  3. 去掉只由原关系模式主码属性集的子集构成的表

• 定义：如果关系模式R∈2NF，非主属性之间不存在函数依赖，则称R属于第三范式，简称3NF，记作R∈3NF

• 如何使关系模式达到3NF，分解步骤共三步：

  1. 对于不是候选码的每个决定因子，从表中删去依赖于它的所有属性。
  2. 新建一个表，新标中包含该决定要因子以及原表中所有依赖于该决定因子的属性。
  3. 将决定因子作为新表的主码。

网课第十单元

10.1 事务与并发控制

• 事务的特性(ACID)

  • 原子性
    • 事务是一个完整的操作。事务的各步骤是不可分的；要么都执行，要么都不执行。
  • 一致性
    • 当事务完成时，数据必须处于一致状态。
  • 隔离性
    • 对数据进行修改的所有并发事物是彼此隔离的，这表明事物必须是独立的，它不应以任何方式依赖于或影响其他事务。
  • 永久性
    • 事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性。

• 事务的分类

  • 显性事务
    • 用BEGIN TRANSACTION(TRAN)明确指定事务的开始，这是最常用的事物类型
  • 隐性事务
    • 通过设置SET IMPLICIT_TRANSACTIONS ON语句，可启动阴性事务模式。当某个事务完成时，再下一个T-SQL语句又将启动一个新事务，隐性事务模式下，自动形成事务链。
  • 自动提交事务
    • 这是SQLServer的默认模式，它将每条单独的T-SQL语句视为一个事务，如果成功执行，则自动提交；如果错误，则自动回滚。

• 事务相关语句

  • BEGIN Transaction
    • 标记事务开始，只是显示事务中使用
  • COMMIT Transaction
    • 事务已经成功执行，数据已经处理妥当
  • ROLLBACK Transaction
    • 数据处理过程中出错，回滚到没有处理之前的数据状态，或回滚到事务内部的保存点。
  • SAVE Transaction
    • 事务内部设置的保存点，就是事务可以不全部回滚，只回滚到这里。

• 例子

BEGIN tran updateAccount
UPDATE bank SET Money = Money-1000
WHERE name='A'
SAVE tran before_update_2
UPDATE bank SET Money=Money+1000
WHERE name='B'
IF OK
  COMMIT tran updateAccount
ELSE
  ROLLBACK tran updateAccount

• 丢失修改过程

  • 丢失修改是指事务1与事物2从数据库中读入同一数据并修改
  • 事务2的提交结果破坏了事务1提交的结果导致事务1的修改被丢失

• 读“脏”数据过程

  • 事务1修改某一数据，并将其写回磁盘
  • 事务2读取同一数据后，事务1由于某种原因被撤销，这是事务1已修改过的数据恢复原值
  • 事务2独到的数据就与数据库中的数据不一致，是不正确的数据，又称为“脏”数据

• 不可重复读过程

  • 事务1读取某一数据后
    • 事务2对其做了修改，当事务1再次读取该数据时，得到与前一次不同的值。
    • 事务2删除了其中部分记录，当事务1再次读取数据时，发现某些记录神秘的消失了！
    • 事务2插入了一些记录，当事务1再次按相同条件读取数据时，发现多了一些记录。
  • 后两种不可重复读有时也称为幻影现象。

• 并发控制措施

  • 当许多人试图同时修改数据库内的数据时，必须执行控制系统以使某个人所做的修改不会对其他人产生负面影响，这称为并发控制。
  • 封锁(加锁)是实现并发控制的主要技术。

• 封锁的定义

  • 事务T在堆某个数据对象(例如表、记录等)操作之前，先向系统发出请求，对其加锁。加锁后，事务T对该数据对象有了一定的控制，在事务T释放它的锁之前，其它的事务不能更新此数据对象。
  • 一个事务对某个数据对象加锁后究竟拥有什么样的控制是由锁的类型决定的。

• 封锁的类型

  • 基本封锁类型有两种
    • 排它锁(简记为x锁、写锁)
      • 若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，知道T释放A上的锁。
    • 共享锁(简记类s锁、读锁)
      • 若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁

• 封锁协议

  • 在运用X锁和S锁对数据对象加锁时，需要约定一些规则，这些规则称为封锁协议
    • 何时申请X锁或S锁
    • 持锁时间
    • 何时释放
  • 对封锁方式规定不同的规则，就形成了各种不同的封锁协议
    • 一级封锁协议
      • 事务T在修改数据R之前不许先对其加X锁
      • 可防止丢失修改，不能保证可重复读和不读“脏”数据。
    • 二级封锁协议
      • 事务T对要修改数据必须先加X锁，知道事务结束才释放X锁；
      • 事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁
      • 防止丢失修改和读“脏”数据，不能保证可重复读。
    • 三级封锁协议
      • 事务T在读取数据R之前必须先对其加S锁，在修改数据之前必须先加上X锁，知道事务结束才释放所有锁。
      • 防止丢失修改、读脏数据和不可重复读。

• 活锁是一种某个事务长期等待的现象

• 死锁是两个或两个以上的事务之间的循环等待

• 解决活锁——先来先服务

  • 当多个事务请求封锁同一数据对象时，封锁子系统按请求封锁的先后次序对这些事务排队。该数据对象上的锁一旦释放，首先批准申请队列中第一个事务获得锁

• 解决死锁的方法

  • 预防死锁发生
    • 一次封锁法
      • 要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行；问题在于降低并发度。
    • 顺序封锁法
      • 预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实行封锁。存在的问题难于实现。
  • 允许发生，定期诊断，所有死锁则解锁
    • 超时法
      • 每个事务设定一个等待时限，如果等待时间超过了规定的时限，就认为发生了死锁。
      • 优点：实现简单
      • 缺点：有可能误判死锁
    • 等待图法
      • 周期性地检测事务等待图，如果发现图中存在回路，则表示系统中出现了死锁。

• 死锁的解除

  • 选择一个或多个处于死锁状态的事务将其撤销，释放它们持有的锁，使其它事务能继续运行下去。
  • 被撤销的事务对数据的修改必须加以恢复。
  • 为了降低处理死锁的代价，通常选取处理死锁代价最小的事务。

• 可串行化调度

  • 多个事务的并行执行是正确的，当且仅当其结果与按某一次序串行地执行它们时的结果相同，称这种调度策略为可串行化的调度。
  • 可串行化是并发事物正确调度的准则。

• 两端锁协议

  • 可串行性是并行调度正确性的唯一准则，两段锁(2PL)协议就是为保证并行调度可串行性而提供的封锁协议
  • 两段锁协议的要求所有事务必须分两个阶段对数据项加锁和解锁
    • 在对任何数据进行读、写操作之前，事务首先要申请并获得对该数据的封锁。
    • 在释放一个封锁之后，事务不再申请和获得任何其他封锁。

• “两段”锁的含义事务分为两个阶段：

  • 第一阶段是获得封锁，也称为扩展阶段
  • 第二阶段是释放封锁，也称为收缩阶段

• 遵循两段锁协议是并发执行结果正确的充分条件而并非必要条件