数据库基本知识（一）

**关联类型
•一对一关系
•一对多关系
•多对多关系
一对一关系
一对一关系是指两个关系表中的字段一一对应，即一个关系表中的一行只能与另一个表中的一行相匹配，反之亦然，其对应关系如图15-13所示。
一对多关系
一对多关系是指关系表中某表中的一行可以和另一表的多行相匹配，而另一个表中一行只对应其一行，这种关系称为一对多，例如图15-14所示的表A和表B的关系，A中一行对应B中多行，而B中一行对应A中一行。
多对多关系
多对多关系是指两个关系表中，一个关系表中的一行可以对应另一表的多行。这种关系一般要通过第3个表定义这种关系。其多对多的关系如图15-15所表示的表A与表B的关系**。

正规化
第一范式
第二范式
第三范式
第一范式
          第一范式是关系数据库的基本要求，其内容是数据库表的每一列都是不可分割的基本数据项，即要求同一列不能有多个值，即表中不能再含有表，下面通过一个实例讲解其范式，创建一个数据表如下所示：
           MySQL>CREATE TABLE students( 
              -> name varchar(50),  
              -> numb  varchar(10)  
            -> course varchar(10),  
             ->); 
            数据表中有三个字段，分别存放姓名、学号和课程，现在表中的数据如表15-2所示，其没有相同的属性，但要再插入一些数据，结果如表15-3所示，其出现了大量相同成员。
第二范式
   第二级正规化形式需要做如下优化：
      这个数据库表不满足第二范式，因为存在如下关系：
     （课程名称）→（学分）
     （姓名）→（学号，年龄）
        其可能会出现如下问题。
第三范式
           第三规范化形式需要消除不依赖于该关键字的字段，如课程和年龄、姓名和学分等都是没有关系的字段，因此应将它们从表中分离出来，建立具有姓名—学号—年龄、姓名—课程—分数、课程—学分关系的三个表。

**唯一约束
唯一约束是指一列数据或几列数据的组合值在数据表中唯一，其可以保证主键以外的字段值不重复。一个表中可以定义多个唯一约束。
例如表15-13所示的供货商表中，不但可以指定供货商ID为主键，若还需保证供货商厂家地址、账户各字段的值不能重复，则可以设置为唯一约束。 外键约束
通过外键约束使数据库中的多个数据表之间建立关联，如果一个表中某个字段的数据和另一个表中的数据有着密切关联，则要为该字段设置外键约束，外键可以是单一字段，也可以是多个字段的组合。通过外键约束可以建立一对多的逻辑关系。
例如：要设置一个从百货公司进货的表单，使其含有ID、进货价格、进货日期，要与百货公司的售货表单（如表15-14所示）建立联系，则要为进货表单设置外键，设计的表单如表15-15所示。 默认约束
默认值约束是指在没有给定字段值时，该字段可以赋给该属性一个默认的初始值，这对于事先不知道数据又不允许为空的字段很有帮助，使用默认值约束尤其方便并可提高效率；若设置了默认值约束的字段，在给字段指定数据时，要以指定的数据为准。例如创建一个buy表，存储购买的价格，如果不指定，就返回空。**

一、建表指令

比如创建一个学生表student，它由学号Sno，姓名Sname，性别Ssex，年龄Sage，所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。

CREATE TABLE Student

(Sno CHAR(10) NOT NULL UNIQUE,

Sname CHAR(20) UNIQUE,

Ssex char(2),

Sage INT,

Sdept char(15)

)

二、增加列、删除列、修改列

1、增加列Stel

Alter table Student ADD Stel Char(12)

2、删除列Stel

Alter Table Student DROP COLUMN Stel

3、修改列Sdept

ALTER Table Student ALTER COLUMN Sdept CHAR(8) Sno CHAR(8)

三、建立与删除索引

1、在表Student中建立按年龄Sage升序建立索引

建立索引：Create INDEX S_INDEX ON Student(Sage)

2、删除索引

DROP INDEX Student S_INDEX

四、连接查询。

在对表进行连接时，最常用的连接条件是等值连接，也就是使两个表中对应列相等所进行的连接，通常一个列是所在表的主键，另一个列是所在表的主键或外键，只有这样的等值连接才有意义。

比如说有两张表分别为courses表（cno，cname，credit）和enrolls表（sno，cno，grade）。

查询所有学生所选的课程名称：

Select sno, enrolls.cno, cname, grade from enrolls, courses WHERE enrolls.cno = courses.cno

五、单表查询时，去掉重复行

比如查询Student表中所有系的名称，去掉重复行

Select distinct department From student

六、常用条件表达式运算符IN,NOT IN；between,and,not like.

在上面的Student表和enrolls表中，查询成绩在80分以上的的学号和姓名。

Select sno, sname From Student WHERE sno IN (select sno FROM enrolls Where grade > 80)

上面的SQL语句也是嵌套查询。

七、有个需用到having字句的，具体题目忘了。

Having子句，筛选出只满足指定条件的组。注意的是，该子句只能同GROUP BY子句配合使用，筛选出符合条件的分组信息。

类似题目如下：查询Student表中每个系有三个以上的学生的所在系。

Select department From Student Group BY department Having COUNT(*) >= 3。

八、插入数据

1、单行插入，比如在上面的Student表中插入学生王强的信息。

Insert into Student(Sno,Sname,Ssex,Sage,Sdept)

Values(‘2005012’,’王强’,’男’,18,’计算机’)

2、多行插入，比如每个学生都要修操作系统c2这门课，将选课信息加入表enrolls中。

INSERT INTTO enrolls(sno,cno)

SELECT Sno, ‘c2’ FROM Student

九、修改数据

比如给enrolls这个表中选修了操作系统这门课的学生的成绩修改为60分。

UPDATE enrolls

SET grade = 60

WHER cno IN

(SELECT cno FROM courses WHERE cname = ‘操作系统’)

十、删除数据

比如删除Student表中年龄在20岁以上的学生

Delete from Student where Sage > 20

删除整张表的数据 delete from Student

十一、存储过程（两个参数，根据输入的参数查询好数据后返回给输出的参数）

比如创建一个存储过程procGetDepName，它带有1个输入参数@sno，还带有1个输出参数@DepartmentName，功能：根据输入的学号，找到该生所在的院系，输出院系名称。

create procedure procGetDepName

@sno nvarchar(10),

@DepartmentName nvarchar(20) output

as

begin

select @DepartmentName = DepartmentName

from Department d, Student s

where d.DepartmentID = s.DepartmentID and

s.sno = @sno

end

十二、数据库常用数据类型和作用。

第一大类:整数数据

bit:bit数据类型代表0，1或NULL,就是表示true,false.占用1byte.

int:以4个字节来存储正负数。可存储范围为:-2^31至2^31-1。

smallint:以2个字节来存储正负数。存储范围为:-2^15至2^15-1。

tinyint: 是最小的整数类型，仅用1字节,范围:0至此^8-1。

第二大类:精确数值数据

numeric:表示的数字可以达到38位,存储数据时所用的字节数目会随着使用权用位数的多少变化。

decimal:和numeric差不多。

第三大类:近似浮点数值数据

float:用8个字节来存储数据.最多可为53位。范围为:-1.79E+308至1.79E+308。

real:位数为24，用4个字节，数字范围:-3.04E+38至3.04E+38。

第四大类:日期时间数据

datatime:表示时间范围可以表示从1753/1/1至9999/12/31，时间可以表示到3.33/1000秒.使用8个字节。

smalldatetime:表示时间范围可以表示从1900/1/1至2079/12/31，使用4个字节。

第五大类:字符串数据

char:长度是设定的，不可变的。最短为1字节,最长为8000个字节.不足的长度会用空白补上。

varchar:长度是可变的。最短为1字节,最长为8000个字节，尾部的空白会去掉。

text:长宽也是设定的，最长可以存放2G的数据。

第六大类:Unincode字符串数据

nchar:长度是设定的，最短为1字节,最长为4000个字节。不足的长度会用空白补上，储存一个字符需要2个字节。

nvarchar:长度是设定的，可变的。最短为1字节，最长为4000个字节.尾部的空白会去掉。储存一个字符需要2个字节。

ntext:长度是设定的,最短为1字节,最长为2G.尾部的空白会去掉,储存一个字符需要2个字节。

第七大类:货币数据类型

money:记录金额范围为:-92233720368577.5808至92233720368577.5807.需要8 个字节。

smallmoney:记录金额范围为:-214748.3648至214748.36487.需要4个字节。

第八大类:标记数据

timestamp:该数据类型在每一个表中是唯一的!当表中的一个记录更改时,该记录的timestamp字段会自动更新.

uniqueidentifier:用于识别数据库里面许多个表的唯一一个记录.

整性约束主要有三种子句：主键子句（primary key）、外键子句（foreignkey）和检查子句（check）。
2) 修改表
alter table <基本表名> add <列名> <类型>
alter table <基本表名> drop <列名> <类型> [cascade | restrict]（cascade表示所有引用到该列的视图和约束也要一起自动删除；restrict表示在没有视图或约束引用该属性时，才能在本表中删除该列，否则拒绝删除。）
alter table <基本表名> modify <列名> <类型>
3) 撤销表
drop table <基本表名> [cascade | restrict]
9. SQL查询
where子句中可以使用下列运算符：
l 算术运算符
l 逻辑运算符
l 字符串匹配运算符，包括like，not like
l 集合成员资格运算符，包括in，not in
l 谓词，包括exists，all，some，unique
l 聚合函数，包括avg，min，max，sum和count
l 还可以是另一个select语句
select语句完整语法：
select 目标表的列名或列表达式序列
from 基本表名和（或）视图序列
[where 行条件表达式]
[group by 列名序列]
[having 组条件表达式]
[order by 列名[asc | desc]]
整个语句的执行过程如下：
1) 读取from子句中基本表、视图的数据，执行笛卡尔积操作；
2) 选取满足where子句中给出的条件表达式的元组；
3) 按group子句中指定列的值分组，同时提取满足having子句中组条件表达式的那些组；
4) 按select子句中给出的列名或列表达式求值输出；
5) order子句对输出的目标表进行排序，按附加说明asc升序排列，或按desc降序排列。
10. 视图
创建视图：
create view <视图名> [<列名> <列名>…]
as <子查询>
[with check option]
with checkoption表示对视图进行增删改是要保证操作的行满足视图定义中的谓词条件（即子查询中的条件表达式）。
视图最终是定义在基本表之上的，对视图的一切操作最终也要转换为对基本表的操作。视图的好处：
l 视图能够简化用户的操作
l 视图是用户能以多种角度看待同一数据
l 视图对重构数据库提供了一定程度的逻辑独立性
l 视图能够对机密数据提供安全保护
11. 事务
事务是一系列的数据库操作，是数据库应用程序的基本单元，是反映现实世界需要以完整单位提交的一项工作。事务是用户定义的一个数据库操作序列。
事务的四个特征：原子性、一致性、隔离性和持久性。
事务处理包括数据库恢复和并发控制。数据库恢复有两个目的：保证事务的原子性和使数据库能恢复到正确状态。
数据恢复的原理概括为冗余，建立冗余数据最常用的技术是数据转储和登录日志文件。数据转储是由DBA定期地将整个数据库复制到磁盘或另一个磁盘上保存起来的过程。
12. 触发器
一个触发器用来定义一个条件以及在该条件为真时需要执行的动作。通常，触发器的条件以断言的形式定义。动作以过程的形式定义。

删除表的关联性drop table Student cascade;//基本表一旦被删除，不仅表中的数据和此表的定义将被删除而且此表上建立的索引、视图、触发器等有关对象一般也都将被删除。因此执行删除基本表操作一定要小心。

若表上建有视图，选择restrict时表不 能删除，cascade时可以删除表。

student表上建立视图
create view IS_Student
as
select sno,snmae,sage
from student
where sdept="IS";

删除Student表
drop table Student restrict;
会报错，系统返回错误信息，存在依赖该表的对象，此表不能被删除

drop table student cascade;
系统返回提示，此表上的视图也被删除

建立索引:提高查询效率
create unique index stusno on student(sno);
DROP INDEX 表名.索引名|视图名.索引名[ ,...n ]
建立聚簇索引
create cluster index Stuname on student(Sname);

SET STATISTICS IO on – 查看磁盘IO
set statistics time on – 查看sql语句分析编译和执行时间
SELECT * FROM site_user – 查看效果
利用dbcc进行数据分析：
DBCC SHOW_STATISTICS(‘site_user’,’name_index4’)
DBCC SHOW_STATISTICS(‘site_user’,’name_index5’)