数据库SQL server知识点

第二章：

2.1 数据模型的概念：

1.什么是数据模型？

答：对现实世界数据特征的模拟和抽象，用来描述数据是如何组织，存储和操作的。

2.数据模型需要满足的3个条件？

答：（1）能比较真实的模拟出现实世界

       （2）能够很容易的让人们理解

       （3）能够容易在计算机上实现

3.数据模型的分类：

（1）概念模型：按照用户的观点来对数据和信息进行建模，主要用于数据库的设计，针对的是信息世界，模型有ER模型，是第一层抽象。

（2）逻辑模型：按照计算机的观点来对数据进行建模，用于DBMS的实现，针对机器世界，模型有，网状模型，层次模型和关系模型等

（3）物理模型：数据在具体的DBMS产品中的物理存储方式和存取方法，针对机器世界，是具体实现。

4.数据模型的组成要素：

（1）数据结构（静态特性）信息：（2018*******,李明，软件工程）

（2）数据操作（动态特性）对信息进行操作，查询，修改，删除

（3）数据完整性约束条件    其中性别只能是男或女，年龄一般是在15岁~40岁.......

2.2 概念模型：

1.实体:客观存在并且可以相互区别的事物   eg  学生

2.属性：实体所具有的特性   eg  学号，姓名，性别

3.码：唯一表示实体  eg 学号

4. 域：属性的取值范围  eg 性别只能是男或女

5. 实体集：同一类型实体的集合：eg  学生（学号，姓名，院系，专业，年龄，电话）

6.实体型：用实体名以及属性名集合来抽象和刻画同类实体。个、eg  学生实体型 ：学生（学号，姓名，院系，专业，年龄，电话）

7.联系：实体与实体之间产生某个动作。实体内部的联系：实体各属性之间的联系  实体之间的联系：不同实体之间的联系。联系可分为1:1,1:n,m:n.

 2.3 概念模型的ER表示法

1.对概念模型的基本要求

（1）较强的语义表达能力

（2）能够方便，直接地表达应用中各种语义知识

（3）简单清晰易于用户理解

2.ER方法

是p.p.s.chen于1976年提出的，是目前最常用的概念模型描述方法，其使用的工具称之为ER图，描述的结果称为ER模型。

3.矩形表示实体，椭圆表示属性，菱形表示联系,联系的本身也是一种实体，也可以有属性

2.5  逻辑模型——关系模型

1.概念模型必须转化成逻辑模型，才能在DBMS中实现，因此逻辑模型既要面向用户又要面向系统 。

2.逻辑模型：从数据的组织方式来描述数据，即用什么样的数据结构来组织数据。

3.逻辑模型主要分为三类：

（1）非关系模型：包括层次模型和网状模型，二者在20世纪70,80年代初非常流行，在数据库系统的初期起了重要作用。

（2）关系模型：关系模型的出现逐渐取代了非关系模型，其以严格的数学理论为基础：谓词逻辑，集合论，目前主流的数据库系统都是基于“关系模型”。

（3）面向对象模型：是目前数据库技术的研究方向

4.关系模型的基本概念

1.关系模型的提出： 

1970年，IBM的研究员E.F.Codd博士发表《大型共享数据银行的关系模型》一文，提出了关系模型的概念，奠定了关系数据库的基础，关系数据库系统采用关系模型作为数据的组织方式。目前，计算机厂商新推出的数据库管理系统几乎都支持关系。关系模型用二维表来组织关系，一个二维表即为一个关系，关系数据库其实就是表的集合。

2.表中的一行即为一个元组，也成一条记录。表中的一列即为一个属性，也称为一个字段。

3.也可以给关系取一个名字，用关系名（属性列表）的方式来表达一个关系，称为关系模式。

2.6 数据库系统的三级模式

1.数据库的三级模式的提出：1975年ANSI/SPARC（美国国家标准协会/标准规划和需求委员会）提出的。

2.数据库的三级模式即：

外模式，概念模式，和内模式。

外模式：面向用户和引用程序员的用户级。数据库用户看见和使用的局部数据的逻辑结构和特征描述，一个数据库可以有多个外模式。外模式是保证数据库安全的一个有力措施。eg 教务系统分别为教师，家长，学生提供了不同的登录。

概念模式：面向建立和维护数据库人员。是数据库中全体数据的逻辑结构和特征的描述，所有用户的公共视图模式，综合了所有用户的需求，一个数据库只有一个模式。eg:教务系统

内模式：面向系统程序员。是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。eg:记录的存储方式采用顺序存储或hash方法存储，索引按照什么方式组织，数据是否压缩或加密。一个数据库只有一个内模式。

3.三级模式的优点：有利于高效的组织管理数据，提高了数据库的逻辑独立性和物理独立性，同时也是保证数据库安全的一个重要措施。

2.7数据库的二级映像

1.为什么要提出二级映像？

为了能够在数据库系统内部实现三级映像模式的联系和转换，数据库管理系统在三个模式之间提出了二级映像。以便用户能够逻辑地，抽象地处理数据，而不必关心数据在计算机中的具体表示方法和存储方式。

2.外模式/概念模式映像：

一个概念模式通常对应多个外模式。因此，对于每一个外模式，数据库系统都有一个外模式/模式映像，它定义了该外模式与概念模式之间的对应关系。这些映像的定义通常包含在各自外模式的描述中。

例如：当概念模式改变时，比如需要增加新的关系，给某个关系增加或者删除属性。，可以由数据库管理员调整各外模式与概念模式之间的映像，使外模式保持不变。而外模式的应用程序是基于外模式编写的，因此也不用修改应用程序，因此可以保证程序与数据的逻辑独立性。

3.概念模式/内模式映像：

定义了数据全局逻辑结构与存储结构之间的对应关系。比如，说明逻辑记录和字段在内部是如何表示的。数据库中概念模式和内模式都是唯一的，所以数据库中概念模式/内模式的映像是唯一的。该映像定义通常包含在概念模式描述中，当数据库的存储结构改变了，如：引进新的存储设备，改变数据的存储位置，可以由数据库管理员对概念模式/内模式映像做相应的改变，可以使概念模式保持不变。因此保证了数据的物理独立性。

第三章：关系数据库

3.1 .1关系数据库结构：

1.什么是关系？

指人与人之间，人与事物之间，事物与事物之间的相互联系，采用自然语言直接进行描述。eg：张三是李四的老师，则张三和李四之间是师生关系。

2.能说关系是一种表，但不能说表就是关系，因为关系是一种特殊的表。

3.注意区分关系和联系的区别，联系是实体之间的，1:1 1:n,m:n

4.对关系的描述一般称为关系模式，一般表示为： 关系名（属性1，属性2，属性3...）

3.1.2关系数据库结构：

1.什么是关系数据库？

建立在关系理论基础上的数据库，在关系数据库中，实体和实体之间的联系均以关系（表格）的形式进行描述。

2.数据库相关概念：

（1）外码：外码必须与其所参照的主码具有相同的域，且外码的取值只能引用参照表中主码的值或使用空值。

3.2 关系的完整性：

1.什么是关系的完整性约束？

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

2.完整性可分为以下几类：

（1）域完整性：属性值域的完整性，包括数据类型，格式，取值范围，是否包括空值。

（2）实体完整性：关系数据库中所有的表都必须有主码，而且表中不允许存在以下两种情况的记录，A:无主码值的记录 B：与其他记录的主码值相同的记录。

（3）参照完整性：是指外码的取值必须参照主码的取值，参照完整性要求关系中不允许引用不存在的记录。若关系R中的属性A参照关系S中的属性B，则值域R中的每个元组在属性A上的值必须为下面两种情况之一：A 空值，B S中某个元组的属性B的取值。

（4）用户定义完整性：针对某一具体关系数据库的约束条件，他反应某一具体应用所涉及的数据必须满足语义要求。eg：一个人口登记系统添加一个公民，其出生日期不能晚于当前日期，银行存取款的取款金额不能大于当前余额。

3.3 关系代数：

1.什么关系代数？

关系代数是指用代数的方式对关系进行运算。

2.关系代数运算符包括以下几类：

（1）集合运算符：

并，交，差，笛卡尔积

并：运算中重复的元组会被消除

差：

交：交运算可由差运算导出R并S=R-（R-S）或 R并S=S-（S-R）

笛卡尔积：作用于两个关系，R（n1,n2,n3,....nn）*S(m1,m2,m3,....mm)=Q，则Q具有n+m个属性，和n*m个元组。

（2）专门的关系运算符

选择，投影，连接

选择：从行的角度进行的运算

投影：从列的角度进行运算

连接运算：是笛卡尔积运算的导出运算，二者主要的区别是，两个关系所有元组的组合都会出现在笛卡尔积的结果中，而只有负荷连接条件的元组才会出现在连接结果中。

（3）算数比较运算符:

大于，大于等于，小于，小于等于，等于，不等于

（4）逻辑运算符：

与，或，非

3.关系代数操作的一个序列形成关系代数表达式，其运算对象是关系，运算结果也是一个关系。

 

第四章：关系数据库标准语言

4.1 SQL概述：
1.SQL的产生：

1974年，SQL语言的雏形最早由美国IBM公司的Boyce和Chamberlin提出。

1975-1979年，在system R上首次实现，有IBM San Jose研究室研制，称为SEQUEL

1986年推出了SQL-86标准，正式命名为SQL

1989年推出SQL-89标准，他是数据库语言SQL的标准集合

1992年进一步推出了SQL-92标准，也是SQL2，是SQL-89的超集，增加了许多新特性。

1999年推出了SQL-99标准，也成为SQL3，对面向对象的一些特性予以支持。

2014年6月SC32在北京全会上，批准了4想为大数据提供标准化支持的新工作项，其中SQL对JSON的支持由中国专家担任编辑。 （SQL32是指数据管理与交换分技术委员会）

 2.SQL的特点：

（1）综合统一

（2）高度非过程化

（3）面向集合的操作方式

（4）语言简洁，易学易用

（5）以一种语法结构提供两种使用方式，独立和嵌套

3.SQL方言：

SQL server的T-SQL

Oracle的PL/SQL

4.SQL语言的功能组件：

DML DDL DCL 

5.SQL 基本数据类型：

（1）数值类型（最常用的SQL数据类型）：

分为：准确型和近似型两种。

准确型：

 

 decimal和numeric都可以用来存储小数，但是更建议使用decimal.

近似型：

 

 

（2）文本类型：即被当作字符串存储，一般不参与数学运算的类型

按照编码可分为普通字符编码（每个英文字符占一个字节存储，每个汉字占两个字节存储）和Unicode编码（每个英文字符和汉字都占用一个两个字节的存储空间）

普通字符编码：

 

char(12):可以存放12个英文字符和6个汉字，不管存储多少个字节都占用12个字节。

varchar(12):可以存放12个英文字符和6个汉字，但是单反只存放1个字符时，只占用1个字节。

超过8000的字符的数据在存储时选择text类型

Unicode字符编码：

（3）时间日期类型（最常用的是datetime类型）：

 

 

（4)二进制类型（存储office文档，图片，声音等二进制信息）

 

 

 SQL server的未来版本将删除text,ntext,image数据类型，可用varbinary(max)代替image数据类型。

4.2模式的定义与删除

学生表：

 

 教师表：

 

课程表：

 

选课表：

 

 1.首先什么是模式？

数据库模式是一种逻辑分组对象，数据库模式是数据库对象的几何，这个集合包含了各种对象如：表，视图，存储过程，索引等。可以想象一个模式作为对象的容器，数据库模式可以作为一个命名空间，能防止来自不同模式的对象命名冲突。

2.创建模式常用的语法：

create schema <模式名>  authorization <用户名>

定义模式实际上就是定义一个命名空间，在这个命名空间中可以进一步定义该模式包含的数据库对象，例如 ：基本表，视图，索引等。

3.用sa账户登录，创建一个StudentDB的数据库，然后创建名为Sch1的模式：

准备工作：

create database Student DB

use StudentDB

go 

create login zhao with password='123'         //创建一个登录名，并映射为数据库用户 

go 

create user zhao for login zhao

create schema Sch1 authorization zhao  //创建一个名为Sch1的模式

4.删除模式的语法：

drop schema Sch1

 

4.3 表的创建：

建立好表是后续使用驶入，存储过程的基础。

1.创建表的SQL语法的简化版：

create table 表名

(

字段1 数据类型1，

字段2  数据类型2

....

）

注意：不是所有的类型都需要长度，一般字符类型需要指定长度日期时间类型，货币类型不需要指定长度，逗号用来分隔开多个字段

2.创建一个名为Users的表，包含用户名vUserName和密码vPassword两个字段，用户名最大允许长度18个字符，密码 最大允许长度20个字符。

create table Users

(

vUserName char(18) Not Null,

vPassword char(20)  Not Null

)

复杂一点的例子

create table Student

(

Sno char(10) not null constraint PK_stu_no primary key,    //constraint 约束名 primary key将字段名指定为关键字

Sname char(20) not null,

Ssex  char(2) not null constraint CK_stu_sex check(Ssex in('男',‘女’)), //constraint 约束名 check(字段名 in 枚举列表)，除了in还可以使用between and及关系运算符

Sdept char(20 not null),

Sage tinyint constraint CK_stu_age check (Sage between1 and 80),

Tel char(15) not null

)

3.修改学生表结构

删除Sage 字段，增加dBirth字段

将Smajor字段从目前的char(20)修改为vchar(20)

alter table student drop constraint CK_stu_age //先删除Sage字段上面的约束，之后才能删除这个字段

alter table Student drop column Sage   //使用drop column删除列

alter table Student add dBirth datetime   //使用add添加列

alter table Student alter column Smajor varchar(20)  //修改表

drop  table Student  //删除这张表，当有多个表之间有关系的时候，不能直接删除这一张表

4.4单表查询

单表查询寻是对数据库的最常见的核心操作、

1.查询语句的基本结构

select select_list [into new_table]

[from table_source]

[where search_condition]

[group by group_by_expression]

[having search_condition]

[order by order_expression [ASC|DESC]]

2.例题：

（1）select * from Student       

//可以让用户快速了解一张表的数据样式。但一般不建议在商用系统中使用，对某些表检索时，不过滤行和列，会严重影响系统的性能。

select Sno,Sname,Ssex,Sage,Smajor from Student 

//投影运算（从列的角度），从Student表中检索上面的五列信息。

（2）友好列标题：

作用：1是：给原来为英文的列标题显示为中文的更好懂的列标题。2是：对一些统计或计算字段指定对应的列标题。

说明：在原始字段名之后跟上as 然后就可以给出一个友好列标题了。

select Sno as 学号,Sname as 姓名,Ssex as 性别,Sage as 年龄,Smajor as 专业 from student    //要求使用友好列标题从Student表中所见以上五项信息，

（3）top 关键字：

使用top  n语法可以只显示前n条数据。

如果检索前百分之多少，可以使用 top n percent

select top 3* from student   //从student表中检索前三行数据，

(4)选择运算

select  * from Student where Sage=20  //从student表中检索年龄为20岁的学生。 

说明：用where  条件来实现行上的选择，此处需要使用比较运算符，当满足的条件有多个的时候，可以使用and 或者 or来连接

select * from Student where Sage>21 and Ssex='女'

select * from Student where Smajor='计算机应用'  or Smajor ='石油工程'

//此处，自己的想法是否可以这样写？ select * from Student  where Smajor in('计算机应用'，‘石油工程’)

(5)between ...and

要比较的值介于某个范围，并且包含边界值，值可以是字符串，也可以是数字，

可以使用在between and 前面加not取反，

select * from Student where Sno between ‘J2016001’  and 'J2016004'

select * from student where Sage not between 19 and 21   //年龄小于等于18岁或大于等于22岁。

（6）in

要检索的信息是一系列取值列表可以使用in

select * from Teacher where Tprot in ('教授','副教授')    //从teacher 表中检索支撑为教授或者副教授的教师信息。

（7）distinct

检索时显示不重复的信息

select distinct Tprot from Teacher

注意select distinct from 只适用于单个列或一系列相关的列，此句不能用于不想管的列。

（8）like 

进行模糊查询，实际用模糊查询时，关键是选择合适的通配符，常用的是百分号和下划线。

select * from Student where Sname like ‘刘%’

select * from Student where Sdept like ‘%学%’

select * from Student where EMAIL like '----@%'

（9）is null

如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下，插入新纪录或更新已有的记录，这意味着该字段将以NULL值保存。若要确定表达式的值是否为NULL，那需要使用IS NULL或者IS  NOT NULL ，而不要使用其他比较运算符，比如等号或者不等号。

select * from Student where Sage is null

select * from Student where Sage is not null

(10) oeder by（默认升序）

对显示的数据进行排序

select * from Course order by  Cname Desc    //按照课程名称的降序显示

select * from Course order by Ccredit Desc,Cno Asc   //显示Course表的所有行，按照课程学分的降序排列，学分相同时再按照课程号的升序排列

（11）算术表达式

select 语句之后，可以是算术表达式或者函数

select 3*5，sqrt(2)

select Cno,Cname,Ccredit,Ccredit*16 as 学时 from  Course

说明：从Course表中检索数据时，额外增加一个原来不存在的列是通过计算得到的。

（12）count函数

说明：count()函数返回皮飞指定条件的行数。

count(*)返回表中的记录数

count(column)返回指定列的值的数目（NULL不计入）

select count(*) from Teacher where Tprot='教授'

//统计Teacher表中教授的数量

（13）集合函数

select max(Tage),min(Tage),avg(Tage) from Teacher.

select sum(Ccredit) from course where xklb='必修'

（14）group by

select xklb as 类别,count(Cname) as 数量 from Course Group by xklb

//对course表，按照必修和选修进行分类，统计每种类别的课程数量

select  Smajor,Ssex,count(Sno) from student group by Smajor SSex,Ssex order by count(Sno) desc

 //对Student表，按照专业和性别进行分组，显示每个专业，每种性别的学生数量，按照学生数量的降序显示结果。

select Tprot，count(Tprot) from Teacher group by Tprot having count(Tprot)>=5

//对于Teacher表，显示职称以及对应人数，要求只有统计人数大于等于5人才显示

注意：having 对于分组语句进行筛选，having与where的区别是：

where 对分组前的元组数据进行筛选，having对于分组后的元组进行筛选，所以必须有group by才能出现having。

4.5 多表查询

1.多表连接查询实际上是通过各个表之间共同列的关联性来查询数据的，他是关系数据库查询最主要的特征。在SQL中连接是用连接条件来表达的。一般格式为表名1.列名1   比较运算符 表名2.列名2

内连接也可以被理解为从结果表中删除与其他被连接表中没有匹配的所有行

2.分类：

（1）内连接：最典型最常用的连接查询，它根据表中共同的列来进行匹配，只有满足匹配条件的数据才能被调查出来。

（2）外连接：在内连接中，只有满足连接条件的元组才能作为结果输出，但有时我们也希望输出哪些不满足连接条件的元组信息，这时就需要使用外连接。即外连接操作以指定表为连接主体，将主体表中不满足连接条件的行一并输出。

外连接可以分为左连接，右连接，完全外连接

左连接 left join 或 left outer join        :结果包含左表的所有行+左右表匹配的数据

右连接 right  join 或right outer join     ：结果包含右表的所有行+左右表匹配的数据

完全外连接 full  join 或 full outer join     ：左右表匹配的数据+左表没有匹配的数据+右表没有匹配的数据

（3）交叉连接：表之间没有任何关联条件，查询将返回左表与右表逐个连接的所有行，就是左表与右表的所有行一一组合，相当于两个表相乘（其实就是笛卡尔积）。没有where 子句的交叉连接将产生连接所涉及的表的笛卡尔积。

3.例子

（1）内连接

select Student.Sno,Student.Sname,SC.Cno,SC.Grade from SC join Student on SC.Sno=Student.Sno

语法要点：from 表1 join 表2 on 条件

或这下面这样写

select Student.Sno,Student.Sname,SC.Cno,SC.Grade  from SC,Student where SC.Sno=Student.Sno

//对SC和Student表进行内连接，显示学生的学号，姓名，课程号，分数。

三张表的连接：显示学生的学号，姓名，课程名，考试分数

select  Student.Sno,Student.Sname,Course.Cname,SC.Grade from SC join Student on SC.Sno=Student.Sno join Course on SC.Cno=Course.Cno.

语法要点：表1 join 表2 on 条件1 join 表3  on 条件2

或者下面这样写：

select Student.Sno,Student.Sname,Course.Cname,SC.Grade from SC,Student,Course where SC.Sno=Student.Sno and SC.Cno=Course.Cno

（2）左外连接

select Student.Sno,Student.Sname,SC.Sno,SC.Grade from Student left outer join SC on Student.Sno=SC.Sno

//让Student表和SC表进行左外连接，即不管是否有选修课程，该学生的信息都会显示出来。

语法要点：from 表1 left  outer  join  表2，此时表1的所有行都会被显示出来

（3）右外连接

//让SC表和Teacher表进行右外连接，显示教师编号，教师姓名，僵尸教授的课程号

select Teacher.Tno,Teacher.Tname,SC.Cno from SC right outer join Teacher on SC.Tno=Teacher.Tno

即使某老师没有讲授过任何一门课程，他的信息也会出现在检索结果中。

（4）全外连接

让SC表和Teacher表进行全外连接，显示教师编号教师姓名，讲师教授的课程号、

select Teacher.Tno,Teacher.Tname,SC.Cno from SC full outer join Teacher on SC>Tno=Teacher.Tno

(5) 交叉连接

对学生和课程两张表进行交叉连接

select * from  Student cross join Course

Student 表中有21条记录，课程表有14条记录，则交叉连接返回的记录数是21*14，相当于 排列组合

交叉连接的作用之一就是利用已有的数据生成行数较大的表，用来做某些测试。

4.6 嵌套查询

1.分类：

相关子查询：子查询的查询条件依赖于外层父查询的某个属性值，带exists的子查询就属于相关子查询

不相关子查询：子查询的查询条件不依赖于父查询

2.例子：

（1）不相关查询in的用法

子查询返回结果后，外部查询将利用这些结果。

可以用all 或any关键字修改引入子查询的比较运算符，some是与any等效的ISO标准

 

使用exsit关键字引入子查询后，子查询的作用就相当于进行存在测试

外部查询的where子句测试子查询返回的行是否存在

一般来说，in适合B表比A表数据小的情况，例如

select * from A where ID in  select ID from B   即外表大內表小

而exists适合B表比A表数据大的情况

 

//查询有某科目考试分数为48分的学生信息

select * from student where Sno=(select Sno from SC where Grade=48)

注意如果子查询返回的是一个集合就不能再使用=，否则查询会报错

 

//查询SC表中选修了课程的学生信息，子查询得到学生的学号，外部查询根据学号找到学生

select  * from student where Sno in (select Distinct Sno from SC)

 

//查询没有选修过任何课程的学生信息

select * from student where Sno not in (select distinct from SC)

 

//在教师表中，检索比任何一个女教师年龄都大的男教师的信息

select * from Teacher where Tage >all(select Tage from Teacher where Tsex='女')

 

//查询选修了B004课程的学生的基本信息

select * from student where exists (select * from SC where Sno=Student.Sno and Cno='B004')

说明：exists现在外层查询中取学生表的第一行记录，用该记录在内层where子句中给定的相关属性值处理内层属性值，若外层的where子句返回True，则将这条记录放回表中。然后再取下一行记录，重复上述过程，直到外层表的记录全部遍历一遍为止

 

//查询没有选修x001课程的学生的基本信息

select * from student where not exists (select * from SC where Sno=Student.Sno and Cno='x001')

 

//查询与王国在同一个专业学习的所有学生的基本信息

select Sno,Sname,Smajor from student where student s1 where exists (select * from student s2 where s1.Smajor=s2.Smajor and S2.Sname='王国')

不知道下面这样写可不可以，有待验证，还可以使用exists来写,但是需要给同一张表取不同的别名

select * from student where Smajor =(select Smajor from student where Sname='王国'）

select * from student where exists (select Smajor from student where Snam3='王国' )

 

4.7 集合查询

T-SQL支持三种集合查询

这三种运算能够进行的前提是，select语句必须拥有相同数量的列，而且类型兼容

并集：实际运算中比较常用

//将学生表的学号，姓名，与教师的教工号，姓名在一个检索结果中显示出来

select Sno,Sname from student unoin select Tno,Tname from Teacher.

注意：并运算有unoin和union all两种用法。unoin：将多个查询结果合并起来时，系统自动去掉重复元组。union all将多个查询结果合并起来时，保留重复元组

交集：

//对专业名以计算机开头的学生，及年龄是21的学生，采用交运算求二者的交集

select * from student where Smajor like '计算机%'  intersect  select * from student where Sage=21

差集

//对专业名以计算机开头的学生，及年龄是21的学生，采用差运算求二者的差集

select * from student where Smajor like '计算机%'  expect  select * from student where Sage=21

 

4.8数据更新

三项基本数据

（1）插入数据

插入数据分为插入单个元组，插入子查询结果以及从查询结果创建新表

//在course中插入一行数据，四项数据为（‘x004’,'计算机前沿',2,'选修'）

insert into course (Cno,Cname,Ccreidit,xklb) values(‘x004’,'计算机前沿',2,'选修')

说明：如果是对表中所有的字段赋值，则可以省略字段名，如果不是对表中所有的字段都给出值，或者改变列出现的顺序时，都必须加上字段名

 

//将学生表中的学号，姓名，性别，年龄，电话，电子邮箱抽取出来，插入到Teacher表中，所有新插入的数据，职称为讲师

insert  into Teacher (Tno,Tname,Tsex,Tdept,Tage,Tprot,tel,email)  select Sno,Sname,Ssex,Sdept,Sage,'讲师',tel,email from student

思路：在insert into表名字段列表后直接加上一个select检索语句即可

 

//将Teacher表中职称为教授的信息存入到一章目前还不存在的experts表

select  *  into experts from teacher where Tprot='教授'

语法：select 选择列表 into 新表名  from 原表名

（2）修改（更新）数据

更新数据也叫做修改数据，修改数据分为修改符合一定条件元组的值，修改所有元组的值及带子查询的修改。

//修改符合一定条件元组的值。语法：

update table set column=data where search_condition

//将course表中编号是B002的课程，学分修改为3分

update course set  Ccredit=3 where Cno='B002'

//修改数据（修改所有元组的值），不指定where子句即可

 

//带子查询的修改

//对学生表，将现有的专业字段，用来存放该学生选修的第一门课程编号。

update student set Smajor=(select top 1 Cno from SC where SC.Sno=Student.Sno)

说明：要能成功执行，Smajor必须允许非空

（3）删除数据

删除分为删除负荷一定条件的元组，删除所有元组，带子查询的删除

//删除符合一定条件的元组 

//删除Course表中编号为B009的记录

delete from course where Cno='B009'

说明：根据主关键字，找到特定的行，并进行删除

//删除所有元组

delete from course

//带子查询删除

//对course表中，没有任何学生选修过的课程，执行删除操作

delete from Course where Cno not in (select Cno from SC)

 

4.9 视图

在实际开发中经常用到视图，使用视图可以简化前端程序员的检索代码，使用视图可以进行更好的安全控制。视图是一种数据库对象，是从一个或多个数据库表或视图中导出的虚表。也可以理解为视图就是封装的一段SQL代码

1.定义视图：

利用create view view_name as select_statement

注意：创建视图的select的限制

（1）定义视图的用户必须对所参照的表或视图有查询权限，即可执行select语句

（2）不能使用compute或compute by子句

（3）不能使用order by 子句

（4）不能使用into子句

（5）不能在临时表或表变量上创建视图

//创建一个名为vwScs的视图，是将学生生表中院系是计算机科学学院的学生的学号，姓名，性别，专业四个字段显示出来。

create view vwScs as select Sno,Sname,Ssex,Tel,email from student where Sdept='计算机科学学院'

//创建名为vwScore的视图，显示学生的学号，姓名，课程号，考试分数。

create view vwScore as select Student.Sno,Student.Sname,SC.Cno,SC.Grade  from SC,Student where Student.Sno=SC.Sno

 

所以想要写好创建视图的语句最重要的是学好封装的select语句。

2.更新视图

更新视图是指通过视图对基本表的数据进行修改。在更新的时候是有一些限制的。

//通过vwScs视图进行数据更新，将氧化的电话修改为13966667777

update vwScs set tel='13966667777' where Sname='杨华'

 

3.查询视图：

视图建立完后就可以像访问表一样访问视图了。

select * from vwScs where Grade<85

4.视图的优点

（1）视图隐藏了底层的表结构，简化了数据访问操作

（2）因为隐藏了底层的表结构，所以大大加强了安全性

（3）使用视图，方便了权限管理，让用户对视图有权限而不是对底层表有权限进一步加强了安全性

4.视图修改与加密

对于已经创建的视图，要修改封装在其中的SQL代码，使用“alter view 视图名 as......”的语法即可

视图要进行加密只需在创建视图的时候，在视图名称之后使用‘with encryption’即可

//创建名为vwScore2的视图，能显示学生的学号，姓名，课程名，分数。要求创建属兔的源代码要加密

create view vwScore2 with encryption as select student.Sno,Student.Sname,Course.Cname,SC.Grade from SC,Student,Course where SC.Sno=Student.Sno and SC.Sno=Course.Sno

4.10 索引：

在关系数据库中，索引是一种单独的，物理的对数据库表中一列或多列的值进行排序的一种存储结构。

很明显，通过索引可以加快检索速度，但是索引需要额外的存储空间。

1.建立索引的一般原则：

（1）如果某属性或属性组经常出现在查询条件中，则考虑为该属性建立索引

（2）如果某个属性经常作为分组的依据列，则考虑为该属性建立索引

（3）如果某属性和属性组经常出现在连接操作的连接条件中，则考虑为该属性或属性组建立索引

2.索引分类：

（1）主键索引：当创建表示指定的主键列，会自动创建主键索引，并且拥有唯一的特性

（2）唯一索引：每一行的索引值都是唯一的（创建了唯一约束，系统将自动创建唯一索引）

（3）聚集索引：聚集索引就相当于使用字典的拼音查找，因为聚集索引存储记录是物理上连续存在的，即拼音a过了后面肯定是b一样。，对于一张表来说，聚集索引只有一个。

大多数情况下，主键索引可以是聚集索引，但也可以是非聚集索引

（4）非聚集索引：相当于使用字典的部首查找，非聚集索引是逻辑上连续，物理存储不连续。

3 创建索引

create index 

//对course2表，将Cno字段设置为聚集索引和主关键字字段

alter table Course2 add  constraint PK_Course2_Cno primary key Clustered(Cno)

说明：通过修改表结构的语句，来指定主关键字的字段名称

//对course2表，将Cname字段设置为唯一索引

create unique index inxCourseName on Course2(Cname)

4.删除索引

drop  index

//对Course2表，删除创建的idxCourseNname

drop index idxCourseName on Course2

或者

drop index Course2.inxCourseName

 

 

第五章：关系数据库理论

5.1关系模式设计中的问题

已知一组数据或属性，如何设计数据模式呢？

关系模型是建立在严格的数学理论基础上，因此人们就以关系模型作为研究对象，形成一个有力的数据库设计工具，那就是数据库的规范化理论。

下面一张表存在以下问题：

 

 

1.数据冗余度大，学生每选修一门新课程，有关他个人的信息和课程的信息都要输入和存放一次，造成了极大的输入工作量和数据存储冗余。

2.插入异常，因为主关键字为(sno,cno),没有学生选修的课程信息无法插入，没有选修课程的学生信息也无法插入。

例如想插入一门新的课程人工智能，但是因为没有学生选修，所以不能插入，这是不符合常理的

例如想插入新生某某，但是某某还没有选修课程，所以也不能将新生信息插入，这样也是不符合常理的。

3.删除异常

例如：学生刘强因为某种原因退学，需要删除学生刘强的信息，因为只有刘强一个学生选修了操作系统课程，那么操作系统课程也会被删除，数据库中就没有操作系统这门课程了。甚至，刘强学生的学习记录也被删除了，如果以后再像查找，就找不到了。

4.更新异常：因为数据冗余度太大，学生的基本信息，课程的基本信息更新工作量变的特别大。

所以得到结论：

 

 

 

解决办法：可以通过函数依赖将关系jiaowu分解为一下三个关系

S(SNO,SNAME,DNAME,AGE)

C(CNO,CNAME,PRE_CNO)

SC(SNO,CNO,SCORE) 

 

5.2常用的函数依赖

1.函数依赖定义：

 

 

 例子：

 

 

 如果知道一个学生的学号，就一定知道这个学生的姓名。所以姓名函数依赖于学号。

2.函数依赖又可分为平凡函数依赖，非平凡函数依赖，

从性质上还可以分为完全函数依赖和部分函数依赖和函数传递依赖。

 也就是一组属性能够决定他的所有子集。

 

 

 

 

 也就是说y依赖于x的全部，x的任何真子集都不能决定y

  

 

 

 

 

5.3常用范式与模式分解实例

 

 

 

 

 

6.3常用凡是与关系模式规范化

 

 

 

 

 

 

 

 

 

 例题：

 

首先观察这张表可以看到：主关键字是（病人编号，药品编号）

 这张表存在一下问题：

1.用法用量不具有属性的原子性

2.病人信息，药品信息存在着数据冗余，由于数据冗余的存在，所以这个关系模式肯定存在插入，删除，更新异常。

因此“病人-药品”关系模式需要规范化，才能达到第三范式要求，函数依赖是关系模式分解和优化的依据。

开始处理：

第一步：考察关系的属性的原子性，有符合属性的，首先拆分复合属性

用法用量属性是一个复合属性，我们拆分为两个属性：用法，用量

得到关系PM（病人编号，病人姓名，药品名称，药品规格，生产企业，电话，用法，用量）

此时达到第一范式的要求

第二步：将所有的属性分为主属性和非主属性两大类

 

 第三步：找出关系PM中的函数依赖

函数依赖是关系模式分解和优化的依据

   

 

通过函数依赖关系，我们也再一次确认了PM主关键字为：(病人编号，药品编号)

其中函数依赖1和函数依赖2都是部分函数依赖，因为属性只依赖于关键字中的一部分

因此PM中存在部分函数依赖，需要模式分解达到2NF的要求

第四步：模式分解，属性重组，消除部分函数依赖

 

第五步：继续分析病人，药品，用药三个关系

病人，用药不存在插入异常，不存在删除异常，不存在更新异常，数据冗余度低

但药品关系中，随着药品的增加，生产企业，电话会不断重复。

第六步：对药品模式继续重复找主关键字，写函数依赖过程

药品模式的主关键字任然是药品编号，药品编号→药名，规格，生产企业，电话，生产企业→电话

所以药品→生产企业→电话存在着传递依赖，不属于第三范式，需要继续处理，采用上面同样的属性分解与重组步骤，又得到两个模式药药，药企。

 

观察没有各种异常并且冗余度还低，所以最后得到：

总结： 

 

 

 

 

 

第七章：数据库设计：