数据库系统概论

数据库应用原理

1.数据库分类：
1）关系数据库。
2）非关系数据库。
3）键值数据库。
2.数据库结构
指在计算机的存储设备上合理存放的相关联的有结构的数据集合的结构。
3.作为信息系统核心和基础的数据库技术得到了越来越广泛的应用。
1）联机事务处理OLTP 针对小工作单元，快速创建单个事件。
2）联机分析处理OLAP 跨大型数据集进行快速分析。
3）计算机辅助设计与制造CAD/CAM。
4）计算机集成制造系统 CIMS。
4.概念（DB,DBMS,DBS）
1）数据库(DB)：
长期储存在计算机内，有组织，可共享的大量数据的集合。简单来说，是进行数据存储，管理的仓库。
2）数据库管理系统(DBMS)：
位于用户与操作系统之间的一层数据管理软件，和操作系统一样是计算机的基础软件。如sqlserver， mysql。
3）数据库系统(DBS)：
由数据库，数据库管理系统，应用程序和数据库管理员组成的存储、管理、处理和维护数据的系统。
5.数据库系统实现整体数据的结构化。
6.数据的安全性:
是指保护数据以防止不合法使用造成的数据泄密和破坏。
数据的完整性
是指数据的正确性，有效性和相容性。
7.三大模式
1）模式：也称逻辑模式
2）外模式：也称子模式或者用户模式。
3）内模式：也称存储模式。
8.二层映像
1）外模式/模式映像。
2）模式/内模式映像。
9.结构化查询语言Structured Query Language (SQL)
SQL语言是数据库管理系统进行数据管理的方法。
Query By Example (QBE)是通过例子进行查询的简称。
10.数据库三大完整性
1）实体完整性(主键设置PRIMARY KEY)
2）参照完整性(外键设置 FOREIGN KEY REFERENCES )
3）用户定义完整性
列值非空(NOT NULL)
列值唯一(UNIQUE)
CHECK,检查是否满足一个条件表达式
4）完整性约束命名子句
CONSTRAINT<完整性约束条件1>……
完整性约束条件包括(NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK等)
11.SQL语言功能
1）数据查询(DQL)：SELECT
2）数据定义(DDL)：用来定义数据对象，库，表，列等。创建、删除、修改库、表结构。CREATE、ALTER、DROP
3）数据操纵(DML)：INSERT,UPDATE,DELETE(可回滚)
4）数据控制(DCL)：GRANT、REVOKE
12.专门的关系运算：关系R和S
1）选择：使逻辑表达式为真的元组。
2）投影：关系R上的投影是从R中选择出的若干属性列组成新的关系
3）连接：等值连接：属性列值相等。 非等值连接：属性列值不相等。
自然连接：进行比较的分量必须是同名属性组，并且在结果中把重复的属性列去掉。
外连接：相当于RUS，没有数据的属性列上填空值。
左外连接：保留关系R中的所有值，没有数据的属性列填空值。
右外连接：保留关系S中的所有值，没有数据的属性列填空值。
13.结果集
通过查询语句查询出来的数据以表的形式展示，我们称这个表为虚拟结果集，放在内存中，查询返回的结果集就是一张虚表。
14.关系演算
1）检索操作：GET语句实现。
2）有范围变量的检索：RANGE语句实现。
3）修改：UPDATE ,HOLD语句实现。
4）插入：PUT语句实现。
5）删除：DELETE ,HOLD语句实现。
15.数据库设计步骤
需求分析设计
概念结构设计
逻辑结构设计
物理结构设计
数据库实施
数据库运行和维护
16.视图和索引
视图：虚拟表，动态生成sql结果
以虚拟表的形式展示，包含带有名称的行和列数据。不在数据库中以存储的数据集形式存在，在引用视图时动态生成。
表查询语句。
create view table1(列1、列2)
as select table1.列1,table2.列2, from table1,table2
where table1.列1=table2.列2
视图的优点：简化用户对数据的理解，简化操作。
安全，只能查询和修改可见的数据，数据库中的其他数据不可见也获取不到。
逻辑数据独立性，帮助用户屏蔽真实表结构变化带来的影响
索引：用来快速定位
一种分散的存储结构，针对表而建立，由数据页面以外的索引页面组成。
提供指向存储在表的指定列中的数据值的指针，数据库根据索引找到特定值，然后顺指针找到包含该值的行，可快速访问数据库表中特定的信息。
(查询id=某个特定值，没有索引，要遍历整个表，在id这一列创建索引，在索引中查找)
优点：快速读取数据、保证数据记录的唯一性，实现表与表之间的参照完整性，在group by 和 order by时，减少排序和分组的时间
唯一索引：不允许其中任何两行具有相同索引值的索引。
主键索引：在数据库关系图中为表定义主键将自动创建主键索引，是唯一索引的特定类型，要求主键中的每个值都唯一。
聚集索引：表中行的物理顺序和键值的索引(逻辑)顺序相同，制定CLUSTERED关键字，将为表产生唯一聚集索引。

SQL语句

1.定义模式
CREATE SCHEMN <模式名> AUTHORIZATION<用户名>
2.删除模式
DROP SCHEMN <模式名><CASCADE(级联) | RESTRTCT(限制)>
3.创建数据库
CREATE DATABASE<数据库名>
4.删除数据库
DROP DATABASE<数据库名>
5.定义基本表
CREATE TABLE <表名>(<列名><数据类型>[约束条件])
PRIMARY KEY(主键)
UNIQUE(唯一值)
FOREIGN KEY(<列名>)REFERENCES<表名>(<列名>)(外键)
6.修改基本表
ALTER TABLE<表名>
[ADD[COLUMN]]<新列名><数据类型>[完整性约束]]
7.删除基本表
DROP TABLE<表名>[RESTRICT | CASCADE]
8.建立索引
CREATE[UNIQUE][CLUSTER]INDEX<索引名>
ON <表名>(<列名><次序>)(唯一索引)
9.修改索引
ALTER INDEX<旧索引名>RENAME TO<新索引名>
10.删除索引
DROP INDEX<索引名>
11.数据查询
SETECT [ALL | DISTINCT]<目标列表达式>
FROM <表名或者视图名>(<SETECT语句>)AS <别名>
[WHERE<条件表达式>]
[GROUP BY<列名>]HAVING<条件表达式>
[ORDER BY<列名>][ASC (升序)| DESC(降序)];
12.单表查询
SETECT<列名>/表达式
FROM<表名>
13.去重查询
SETECT DISTINCT<列名>/表达式
FROM<表名>
14.查询所有列
SETECT *
FROM<表名>
15.WHERE子句常用查询条件

查询条件	谓词
确定范围	BETWEEN AND, NOT BETWEEN AND
确定集合	IN, NOT IN
字符匹配	LIKE ,IS NOT LIKE(%为任意长度，_为任意单个字符)
空值	IS NULL, IS NOT NULL
多重条件	AND, OR, NOT
16.聚集函数
COUNT(*)统计元组个数。
GROUP_CONCAT(COL)返回由属于一组的列值连接组合而成的结果。
COUNT([DISTINCT	ALL<列名>])统计一列中值的个数。
SUM([DISTINCT	ALL<列名>])统计一列中值的总和。
AVG([DISTINCT	ALL<列名>])统计一列中值的平均值。
MAX([DISTINCT	ALL<列名>])统计一列中值中的最大值。
MIN([DISTINCT	ALL<列名>])统计一列中值中的最小值。
聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。
17.连接查询
SEIECT<表1>.,<表2>.
FROM<表1>,<表2>
WHERE <表1>.<列>(连接谓词) = (连接条件) <表2>.<列>
18.外连接
SELECT <列1>,<列2>
FROM<表1>LIFT(左外连接) OUTER JOIN<表2>ON<表达式>

      INNER JOIN(内部连接)
      FULL JOIN(完整外部连接)

多表连接 WHERE……AND
19.嵌套查询
1)带有IN谓词的查询
WHERE……IN( )
2)带有比较运算符的子查询
WHERE……=/>=/<=( )
3)带有ANY(SOME)或ALL谓词的查询
WHERE……<ANY( )
4)带有EXISTS谓词的子查询
EXISTS代表存在量词，只产生逻辑true 或者false
WHERE……EXISTS( )
20.限制查询
SELETE TOP 10*
FROM
返回10条查询记录
WHERE ROW_NUM<15 AND ROW_NUM>=5
返回5——15条查询记录
21.集合查询，对各表查询结果进行操作
1）并操作UNION(不包含重复行)
eg:select 字段1 from table1 union select 字段2 from table2
union all (包含重复行，不进行去重操作)
2）交操作INTERSECT
3）差操作EXCEPT
22.子查询不仅可以出现子WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。
23.插入数据
INSERT
INTO<表名>[<列1>,<列2>,……]
VALUES(<常量1>,<常量2>,……)
如果没有给出列名，自动赋值为NULL.
24.修改操作
UPDATE<表名>
SET<列名> = <表达式>
[WHERE<条件>]
25.删除操作
DELETE
FROM<表名>
[WHERE<条件>]
26.空值处理
加了UNIQUE限制和码属性不能取空值。
27.建立视图
CREATE VIEW<视图名>[<列1>,<列2>,……]
AS<子查询>(子查询可以是SETECT,ORDER BY,DISTINCT)
WITH CHECK UPTION
28.删除视图
级联删除语句
DROP VIEW<视图名>[CASCADE]
29.查询视图
SETETE<列1>,<列2>
FROM<视图名>
WHERE<表达式>
WHERE子句不能用聚集函数
派生表在语句执行时临时定义，执行后即被删除。
30.更新视图
UPDATE<视图名>
SET <列1> = ‘ ’
WHERE<表达式>
并不是所有视图可以更新：
若视图由两个以上基本表导出，则不允许更新。
若视图字段来自字段表达式或常数，则不可执行插入和删除操作。
若视图字段来自聚集函数，则不允许更新。
若视图中含有GROUP BY子句或者DISTINCT短语，则不允许更新。
31.向用户授予或收回对数据的操作权限。
GRANT<权限1>,<权限2>
ON<对象类型><对象名>
TO<用户1>,<用户2>
[WITH GRANT OPTION]
Eg:
GRANT SELETE
ON TABLE STUDENTS
TO U1

REVOKE<权限1>,<权限2>
ON<对象类型><对象名>
FROM<用户1>,<用户2>
Eg:
REVOKE UPDATA(<列>)
ON TABLE STUDENTS
FROM U4(CASCADE)
32.数据库角色
角色创建
CREATE ROLE<角色名>
给角色授权
GRANT<权限>
ON<对象类型><对象名>
TO <角色1><角色2>
将一个角色授予其他的角色或者用户
GRANT<角色1><角色2>
TO<角色3>
WITH ADMIN OPTION
角色权限收回
REVOKE<权限1><权限2>
ON<对象类型><对象名>
FROM<角色1><角色2>
33.drop、delete、truncate的区别
1>delete和truncate是删除表中数据，drop删除表结构和数据
2>delete是DML语言，可回滚。drop和truncate是DDL语言，不能回滚，及时生效
3>速度：drop>truncate>delete

34.数据库的三大范式
1>列的原子性(同一列不能有多个值，实体属性不能重复)
2>第一范式基础上，实体的非主键属性完全依赖主键属性
3>第二范式的基础上，实体中不存在非主属性传递依赖主键
消除传递依赖(A依赖B，B依赖C，说A依赖C)，造成数据冗余

#数据库中的常用函数以及数据类型
1）数学函数

函数	含义
abs(x)	返回x的绝对值
bin(x)	返回x的二进制形式
oct(x)	返回x的八进制形式
hex(x)	返回x的十六进制形式
ceiling(x)	返回大于x的最小整数值
exp(x)	返回e的x次方
in(x)	返回x的自然对数
log(x)	返回x的以y为底的对数
mod(x)	返回x/y的模（余数）
round(x,y)	返回参数x的四舍五入的有y位小数的值
truncate(x,y)	返回数字x截短为y位小数的结果

2）字符串函数

函数	含义
concat_ws(sep,s1,s2,……,sn)	将s1——sn连接并用sep字符间隔
insert(str,x,y,str)	将字符str从x开始，y个字符长的子串替换为str并返回结果
find_in_set(str,list)	查找list中的str字符，返回str在list中的位置
reverse(str)	返回颠倒字符串str的结果
trim(str)	去掉str首部和尾部所有空格
strcmp(s1,s2)	比较字符串s1,s2
ucase(str)/upper(str)	返回str大写结果
lcase(str)/lower(str)	返回str小写结果
substr(str)	截取字符串str
3）日期和时间函数
函数	含义
–	–
curdate(time)()/current_date(time)()	返回当前日期（时间）
data_format(data,fmt)	依照指定fmt格式格式化日期date值
now()	返回当前日期和时间
format(x,y)	把x格式化为以逗号隔开的数字序列，y是结果小数位数
4）类型转换函数
mysql提供了cast()函数，将一个值转换为指定的数据类型
5）系统信息函数

函数	含义
database()	返回当前数据库名
found_rows()	返回最后一个select检索的总行数
user()	返回当前登录用户名
version()	返回mysql服务器版本
6）常用数据类型

数据类型	含义
CHAR.CHARACTER	长度为n的字符串
VARCHAR	最大长度为n的字符串
INT	长整型
BOOLEAN	逻辑布尔量
DATA	日期
TIME	时间

注意点：

 HIVING子句中的筛选字段必须是可以出现在分组结果中的字段。
 DISTINCT关键字可以对一个或多个 字段去重，也可以结合COUNT函数进行去重计数，但是DISTINCT关键字必须位于第一个字段的前面。
 order by可以按照从左至右顺序多层级排序。若前一个字段取值相同，再按照后一个字段排序。
 在SQL语句中，别名是在WHERE子句后计算的，所以WHERE子句中不能用列别名，FROM和JOIN子句中可以用表别名。
 UNION合并数据后去重，UNION ALL不去重。
 当WHERE子句的查询条件是聚合函数时，子查询不能替换为连接查询。