美工实战数据库设计——图书管理系统
一、数据库大致经历了以下几个阶段：
1.人工管理阶段；
2.文件系统阶段；
3.数据库系统阶段；
数据库系统有一下几个特点：
1）数据结构化
2）数据独立性高
指的是应用程序与磁盘存储数据之间的相互独立；
3）数据共享性高，冗余度低；
4）统一的数据控制；
其中包括了数据安全性（用口令检查用户的合法身份）、数据的完整性（指的是数据的正确性、有效性和兼容性）、数据库并发控制、数据库恢复。
4.高级数据库技术发展阶段；
1)分布式数据库系统；
2）面向对象数据库系统；
二、数据库的基本概念
1.数据（Data）
2.数据库（Data Base，DB）
数据库是长期存储在计算机内、有组织的、可共享的数据集合；
3.数据库管理系统（Data Base Management System，DBMS）
4.数据库系统（Data Base System，DBS）
三、数据模型
1.数据模型：一组描述数据库的概念；
数据模型均由以下三部分组成：数据结构、数据操作和数据约束条件；
（1）数据结构：用于描述系统的静态特性
（2）数据操作：用于描述系统的动态特性
（3）数据约束条件：一组完整性规则的集合
2.实体联系模型
3.常用的数据模型：
1）层次模型；
是用树形结构来表示实体及实体间的联系；而基于层次模型的描述可以看出，只能解决一对多的实体联系，不能直接表达多对多联系的复杂结构；
2）网状模型；
3）关系模型；
4）面向对象模型；
四、数据库系统结构
数据库的体系结构由三级构成：内部级、概念级和外部级。
五、数据模型
数据模型由以下三部分组成：数据结构、数据操作和数据约束条件；
1）数据结构：用于描述系统的静态特性；
2）数据操作：用于描述系统的动态特性；
3）数据约束条件：一组完整性规则的集合；
六、常用数据库管理软件
1.SQL Server；
2.Oracle 数据库；
包括Oraclette数据库服务器和客户端；
3.DB2数据库
IBM公司研制的一种关系型数据库系统；
4.MySQL数据库
小型关系型数据库管理系统，开发者为瑞典MySQL AB公司
5.Access 数据库
微软公司推出的基于Windows的桌面关系数据库管理系统（RDBMS),是office系列软件之一；
七、数据库开发概述
1.数据库系统访问技术；
数据库服务的主流标准接口主要有ODBC、OLE DB、ADO、JDBC.
1) 开放数据库连接（Open Database Connecitivity,ODBC）
ODBC是一种重要的访问数据库的应用程序编程接口（API）它的核心就是基于标准的SQL语句，因此，数据库必须支持SQL语句；
其有个重要的优点就是以统一的方式处理所有的数据库；
2）OLE DB
OLE DB 是Microsoft公司提供的关于数据库系统级程序的接口（System-Level Programming Interface），是微软公司数据库访问的基础。OLE DB对象本身是 COM（组件对象模型）。
3）动态数据对象（ADO)
ADO（Active Data Objects）是一种简单的对象模型，可以被开发者用来处理任何OLE DB数据，可以由脚本语言或高级语言调用；
4）Java数据库连接（JDBC）
JDBC(Java Database Connectivity）是Java应用中访问表类型数据源的应用编程接口，这些数据源包括基于SQL的数据库、存储在电子数据表单中的数据或存储在文件中的数据。
2.网络数据库系统编程技术
1)通用网关结构（CGI）编程
通用网关结构（Common Gateway Interface，CGI）是一种通信标准，它的任务是接受客户端的请求，经过辨认和处理，生成HTML文档并重新传回到客户端；它的弱点是速度慢和安全性差；
2）动态服务器页面（ASP）
动态服务器页面（Active Server Pages，ASP)是Mircrosoft公司推出的一种取代CGI的技术，是一种真正简便易学，功能强大的服务器编程技术；
3）JAVA服务器页面（JSP）
JAVA服务器页面（Java server Pages，JSP）是Sun公司倡导、许多公司参与建立的发布的一种动态网页技术标准；
八、结构化查询语言（Structured Query Language,SQL）
1. SQL的特点：
SQL功能丰富，语言简洁，集数据查询、数据操纵、数据定义和数据控制功能于一体；
（1）综合统一；
（2）高度非过程化；
（3）可移植性；
（4）以同一种语法结构提供两种使用方式；
（5）易学易用；
2.数据定义语言（Data Description Language，DDL）
常见的DDL语句包括CREATE,ALTER,DROP等。
CREATE语句
1）创建数据库
CREATE DATABASE database_name;
例如创建物流信息数据库
CREATE DATABASE SI(Logistics Information ）
2）创建表
在向数据库输入数据之前，必须县创建表；格式如下
CREATE TABLE Logistics；
建表时需要给出数据的数据类型及与该表完整性约束条件；
| 数据类型 | 含义 |
|
CHAR(n) |
长度为n的长字符串 |
| VARCHAR(n) | 最大长度为n的变长字符串（数字。字符。及字符等） |
| NVACHAR(n) | 最大长度为n的变长字符串（汉字） |
| INT | 长整数 |
| SMALLINT | 短整数 |
| NUMERIC(p，d） | 定点数，有p位数字组成，小数点后有d位数字 |
| REAL | 取决于机器精度的浮点数 |
| Double Precision | 取决于机器精度的双精度浮点数 |
| FLOAT(N) | 浮点数，精度至少为n位数字 |
| DATE | 日期，包含年、月、日，格式为YYYY-MM-DD浮点数 |
| TIME | 时间，包含一日的时、分、秒，格式为HH:MM:SS |
完整性约束条件
| 约束 | 说明 |
| PRIMARY | 主键，实体完整性，检查主键值是否唯一，主键的各个属性是否为空 |
| FOREIGNKEY | 外键，参照完整性，用REFERENCE指明外键参照了那些表的主键 |
| DEFAULT | 提供默认数据 |
| UNIQUE | 唯一 |
| NOT NULL | 非空 |
| CHECK | 检查，字段的取值范围 |
在设计建立一个数据库需要以下6个步骤：
step1. 需求分析；而在这一阶段最常用的是SA结构化分析法和表达用户需求的数据字典和DFD数据流图；
step2. 概念结构设计；而此阶段最主要的环节是画出E—R图；
step3. 逻辑结构设计；
step4. 数据库物理设计；
step5. 数据库实施和维护；
step6. 数据的载入；是个很复杂的阶段，可以手工输入，也可以转录，但是需要反复的校验；
下面就以一个图书馆信息管理来举例按照以下步骤创建一个简单的数据库的流程；
需求分析：来分析下图书馆信息管理通过这个名字大家就可以知道一般用来查询图书信息，借阅者信息；经过仔细分析之后列出以下信息
数目信息：ISBN，作者，书名，出版社，出版时间，分类等；
读者信息：卡号，姓名，性别，身份证号，头像等；
书信息： 状态，编号，位置等；
借阅信息：借阅时间，归还时间，是否归还等；
概念设计：E-R图如下
注：而作为联系的图书库存量本身也是有属性的；
先建立一个Reader（读者）表
CREATE TABLE Reader ( Id INT PRIMARY KEY, CardNum VARCHAR (50), FullName NVARCHAR (50), Gender CHAR (2), IdentityCard VARCHAR(20), Avatar IMAGE );
执行完以上代码之后会自动生成一个数据为空的表如下 其中CardNum为主键。
依次建立所需“Book”（书）表 其中设置主键为：
CREATE TABLE Book ( Id INT PRIMARY KEY, ContentId INT, [Status] NVARCHAR (50), Position NVARCHAR (200) );
建立“Content”（书目）表
CREATE TABLE Content ( Id INT PRIMARY KEY, ISBN VARCHAR (20), Author NVARCHAR (50), Title NVARCHAR (50), Publishing NVARCHAR (50), PublishTime DATETIME, Category NVARCHAR (50) );
建立“BorrowingInformation”（借阅信息）表
CREATE TABLE BorrowingInformation ( BookId INT, ReaderId INT, BorrowingTime DATETIME, ReturnTime DATETIME, IfReturn BIT, PRIMARY KEY(BookId,ReaderId,BorrowingTime) );
逻辑结构设计：E-R图转换为关系模型
3.创建视图
视图只存在定义，而不是存在真正的数据。其有5个优点：
a.视图可以简化用户操作；
b.视图使用户能以多种角度看待同一数据；
c.视图对重构数据库提供了一定的逻辑独立性；
d.视图能够对机密数据提供安全保护；
e.适当的利用视图可以清晰的表达查询；
创建视图的基本格式：
CREATE VIEW name AS SELECT name FROM name
[WITH CHECK OPTION];
创建”必修”课程的视图
CREATE VIEW RequiredCourses AS SELECT * FROM Course WHERE CourseType = \'必修\'
4.创建索引
基本格式如下：
CREATE INDEX index_name ON table-name;
根据Student表为Name列创建一个名为Student_name的索引：
CREATE INDEX Student_name ON Student(Name);
5.创建存储过程
基本格式如下：
CREATE PROCEDURE procedure_name @parameter_name AS SQL_statements;
存储过程中支持输入和输出两个参数并可以返回一个整数值用以进行状态检测。
例如创建一带输入和输出的存储过程（创建一个Number的存储过程）：
CREATE PROCEDURE Number @dep CHAR(20), --用来存储用户输入的院系的值（变量以@开头） @num INT OUT --用来存储SELECT语句的结果并输出给用户（变量以@开头） AS SET @num = (SELECT COUNT(StudentID) FROM Student WHERE Department= @dep);
6.触发器
是一种特殊类的存储过程，一般在运行插入、删除、更新时自动运行。
其语法格式如下：
CREATE TRIGGER trigger_name ON table-name FOR{INSERT ,UPDATE, DELETE} AS SQL-statements；
例创建一个简单的触发器，当更新Student表示显示一条信息：
CREATE TRIGGER Upd ON Student FOR UPDATE
AS
PRINT \’Student 已经被修改！\’
7.创建用户和角色
其格式如下:
CREATE USER user_name; 用户对自己建立的表和视图拥有全部的操作权限。
CREATE ROLE role_name; 角色是允许用户在数据库中执行特定功能的一个或一组权限。
注意 用户和角色的授权使用GRANT语句。
二）修改表
其格式如下：
ALTER TABLE table_name ADD column_name/DROP (完整性约束名)/MODIFY column_name;
例 向Course表中添加一个新列“Teacher”，其数据类型为CHAR型。
ALTER TABLE Course ADD Teacher CHAR(10); 不论表中是否有数据，新增加的列一律为空值；
修改Course表中Teacher列的数据类型为VARCHAR型
ALTER TABLE Course MODIFY Teacher VARCHAR(10);
DROP语句是用来删除数据库、表、视图、索引、存储过程和触发器。
a.删除数据库
DROP DATABASE database_name; 可以彻底删除数据库的数据和它在磁盘上的物理结构。
例 删除SI数据库
DROP DATABASE SI;
b. 删除数据表
DROP TABLE table_name; 可以从数据库中删除数据表。
例 删除Student表
DROP TABLE Student；
c. 删除视图
DROP VIEW view_name;
d. 删除索引
DROP INDEX index_name;
e.删除存储过程
DROP PROCEDURE procedure_name;
f.删除触发器
DROP TRIGGER trigger_name;
九、数据操作语言（Data Manipulation Language，DML）
(1)、INSERT 语句
顾名思义大家都知道这是向数据库中插入数据，其包含两种写法：一 插入一条记录，用INSERT VALUES;二 插入子查询的结果，用INSERT SELECT;
其基本语法如下：
INSERT INTO 表的名字 (col1，col2….)VALUES (value1,value2,value3….)
实例：向“student表中”插入学生信息元组（‘、、、、、、’）
INSERT INTO Student (StudentID,Name,Gender,Birthday,Origin,Department) VALUES (\'090202\',\'王玲\',\'女\',\'1986-03-4\',\'重庆\',\'音乐系\')
介绍另外一种基本语法如下：
INSERT INTO 表的名字 (col1，col2….) SELSCT col1，col2….FROM 表的名字 WHERE 插入查询结果的新表
实例：把Student表中中文系学生的信息插入到CDIS表；
CREATE TABLE CDIS ( StudentID CHAR (10) PRIMARY KEY, Name CHAR(20) , Gender CHAR(2), ); INSERT INTO CDIS (StudentID,Name,Gender) SELECT StudentID,Name,Gender FROM Student WHERE Department = \'中文系\';
(2)、SELECT 语句
数据库的查询是数据库的核心操作，所以现在要深一步讲解；
其基本语法格式如下：
SELECT [DISTINCT | ALL] 解释为每一个获得数据语句的开始，修正字DISTINCT可以去掉重复行；ALL是默认返回全部数据；* 通ALL相同；
FROM 表1|视图 解释为从一个表中或视图中；
WHERE 内容 解释为语句限制返回的行必须满足限定的条件；
GROUP BY 内容 HAVING 条件 解释为讲所有列名相同的行组织在一起，而HAVING必须在GROUP BY实行，来限制选择组的满足条件；
ORDER BY 内容； 解释为通过指定列来对内容进行排序；
实例如下：单表查询
A 查询学生的全部信息：
SELECT * FROM Student;
SELECT * FROM Grade; 成绩表 SELECT * FROM Course; 课程表
其显示结果如下：
成绩表全部信息显示如下：
课程表显示如下：
B 查询学生的学号、姓名和籍贯；
SELECT StudentID,Name,Origin FROM Student;
其显示结果如下：
C查询有哪些院系；
SELECT DISTINCT Department DISTINCT 去掉重复行的作用； FROM Student;
D查询经贸系的学生信息；
SELECT * FROM Student WHERE Department = \'经贸系\'; 也可以写成 WHERE Department IN(\'经贸系\');
其显示结果如下：
下面分享一些常用查询条件：
E 查询成绩大于70分的学生的学号，查询结果按学号升序排列；
SELECT DISTINCT StudentID FROM Grade WHERE Grade > 70 ORDER BY StudentID; 显示结果如下：
F查询成绩在50-70之间学生的学号；
SELECT DISTINCT StudentID FROM Grade WHERE Grade BETWEEN 50 AND 70; 显示结果如下：
G查找名叫“郭靖”学生的信息；
SELECT * FROM Student WHERE Name LIKE \'郭靖\'; ///WHERE Name LIKE \'王%\'; /代表了任意长度的字符串（所有姓王的信息）///WHERE Name LIKE \'_玲\';/代表了单个字符（只有字叫玲的姓名）
以下两张结果均为LIKE扩展而来；
H查询没有成绩的学生；
SELECT * FROM Grade WHERE Grade IS NULL; 为了验证次查询结果，我对数据库的数据作了一下改动；
J查询“中文系”是“上海”的学生信息；
SELECT * FROM Student WHERE Department = \'中文系\' AND Origin = \'上海\';
K查询选修了06号课程并且考试成绩为空的学生数量；
SELECT COUNT (StudentID) FROM Grade WHERE Grade IS NULL AND CourseID = \'06\';
常用聚集函数如下表：
| 函数 | 功能 |
| COUNT([DISTINCT | ALL]*) | 统计元组个数 |
| COUNT([DISTINCT | ALL]<列名>) | 统计一列中值的个数 |
| SUM([DISTINCT | ALL]<列名>) | 计算一列值得总和（此列必须是数值型） |
| AVG([DISTINCT | ALL]<列名>) | 计算一列值得平均值(此列必须是数值型) |
| MAX([DISTINCT | ALL]<列名>) | 求一列值中的大值 |
| MIN([DISTINCT | ALL]<列名>) | 求一列值中的小值 |
L查询选修了3门课的学生的学号
SELECT StudentID FROM Grade GROUP BY StudentID HAVING COUNT (*) = 4; HAVING与WHERE 类似只不过前者用于组中，而后者用于表或视图；
十、以上叙述全是分享的是单表查询，下面来跟大家分享一下连表查询，换句话说就是连接多个表查询数据；
M 查询考试不及格的学生的学号，姓名，课程和成绩；
SELECT Student.StudentID,Name,Course,Grade FROM Student,Course,Grade WHERE Student.StudentID = Grade.StudentID AND Course.CourseID = Grade.CourseID AND Grade < 60;
注：连接多表查询需要用 表名.列名 的格式来加以区分；
N 嵌套查询 查询05号课程的不及格的学生的学号和姓名。
SELECT StudentID,Name FROM Student WHERE StudentID IN (SELECT StudentID FROM Grade WHERE Grade <60 AND CourseID = \'05\');
十一、集合查询：
SELECT 语句的查询结果是元组的集合，所以对列数相同且数据类型也相同多的SELECT 语句的结果可以进行集合操作。
包含了三个 并（UNION）、交（INTERSECT）、差（EXCEPT）
O 查询信息系和音乐系的学生信息
SELECT * FROM Student WHERE Department = \'信息系\' UNION SELECT * FROM Student WHERE Department = \'音乐系\';
十二、UPDATE 语句；
其基本格式如下：
UPDATE table_name SET colun_name = value1 colun_name2 …..
WHERE 控制语句;
P 将04号课程改为必修；
UPDATE Course SET CourseType = \'必修\' WHERE CourseID = \'06\';
十三、DELETE 删除语句
注：只能删除一条语句，而不是删除表，如果删除表需要用DROP TABLE.
其基本格式如下：
DELETE FROM table_name
WHERE 控制语句
Q 删除张华的学生信息；
BEGIN TRANSACTION DELETE FROM Grade WHERE StudentID = \'090203\'; DELETE FROM Student WHERE Name = \'张华\'; ROLLBACK TRANSACTION
下面通过几个例子来跟大家分享一下控制语句的定义：
W 把查询Student表的权限授予所有用户；
GRANT SELECT ON Student TO PUBLIC;
运行成功之后就授予所有用户权限。
其基本定义格式如下：
GRANT 什么样的权限
ON 表名或者被授予权限的对象
TO 用户名字 PUBLIC;
还有另外一种形式为：
GRANT role
TO user_name
WITH ADMIN OPTION;
最后一句红色字母表示获得权限的角色或用户还可以把这种权限在授予其他的角色；
X 把角色 Y 授予用户张
GRANT Y TO 张;
Y 收回所有用户对Grade表的查询权限。
REVOKE SELECT ON Grade TO PUBLIC;
上述命令执行完毕；
可以看出授权(GRANT)与收回（REVOKE ）是相对应的但是语法是一致的；
而BEGIN TRANSACTION（开始运行的） 、COMMIT、 ROLLBACK（表示回滚）语句是SQL中定义事务的语句；其事务具有四个特性：原子性、一致性、隔离性、持续性。
在一般的应用中运用以下两种类似的语法来支持两类过程：
BEGIN TRANSACTION BEGIN TRANSACTION
statement1 statement1
statement2 statement2
statement3 statement3
ROLLBACK TRANSACTION COMMIT TRANSACTION
以上就是跟大家分享的成果；