数据库系统概述

数据与信息

信息

概念：是数据的内涵的人脑对事物之间联系的抽象反应
特征：

1. 源于物质和能量
2. 可以感知
3. 可以储存、加工、传递和再生

数据

概念：是信息的具体符号表示或载体

数据与信息的关系

信息包含数据，数据过滤信息

基本概念

数据库：
数据库管理系统：
数据库系统：
数据模型：
关系数据库：

数据库技术的产生、发展

人工管理阶段

1.数据不保存
2.没有专用软件进行管理
3.数据不共享
4.数据不具有独立性

文件系统阶段

1.数据已文件形式长期保持
2.由文件系统管理数据
3.查询与数据间有一定独立性
4.文件形式多样化
5.数据有一定共享性

数据库特点

1.结构化的数据
2.数据共享性高、冗余度低
3.数据独立性高
4.有统一的数据管理和控制功能

数据库管理系统的数据控制功能

1.安全性保护
2.完整性检查
3.并发控制
4.数据库恢复

数据库系统（DBS）的组成

数据库：DB

特点：

1. 集成性：将数据按照一定的结构形式进行存储
2. 共享性：多用户同时使用

用户：3种

1类用户：最终用户
2类用户：应用程序员
3类用户：数据库管理员

数据库系统

三级模式结构（外、中、内）

1. 外模式/子模式/用户模式：（可以有多个）

2. 模式/逻辑模式/概念模式：指全体数据的逻辑结构和特征描述

3. 内模式/储存模式/物理模式：（唯一的）
   对数据存储结构的描述

二级模式结构（外，内）

1. 外模式/模式映像：

2. 模式/内模式映像：

数据库管理系统：DBMS

数据定义语言：物理存取

负责数据的模式定义与数据的物理存取构建

数据操纵语言：增删改

负责数据的操纵，如查询与增、删、改等

数据控制语言：并发，故障恢复

负责数据完整性、安全性的定义与检查以及并发控制、故障恢复等

数据转换语言：

建立：初始数据的装入与数据转换
维护：转储、恢复、重组织与重构造、系统性能监视与分析

2.SQL

2.1 SQL的特点

1.非过程化语言（减轻用户负担，提高数据独立性）
2.面向集合的操作方式（接受集合输入输出，且运行SQL语句嵌套）
3.一体化的语言（集数据的 定义DDLD，查询DQLD，操纵DMLD，控制DCLD 于一体）
4.灵活的使用方式（自含式语言 与 嵌入式语言）
5.语言简洁，易学实用

2.2.1 数据库的设计思路

1.确定数据库的用途
2.确定数据库需要的表
3.确定表中的属性（需要多少列，每列的用途）
4.确定表的主键外键
5.确定属性的数据类型 及应用约束

2.2.2 数据类型

1. 字符串数据：CHAR 、VARCHAR

CHAR(n)：设定多少就占用多少字节
VARCHAR(n)：虽然设定是这么多，但是会根据实际字节数量调整占用空间

2. 数值数据：INT、DECIMAL、FLOAT、MONEY、BIT

INT(n)：整型数值
MONEY：币型数值，包含小数点后2位
BIT：二值数值，仅限（0,1，NULL）

浮点数

FLOAT[(n)]：n 设置存储小数点后的位数的个数

DECIMAL[ ( p [,s] ) ]：p 设置存储位数的个数(1~38)，s设置存储小数点后的位数（p > s > 0）

3. 时间数据

DATETIME()

3. 创建数据库

3.1 建库,访问库：CREATE DATABASE

CREATE DATABASE 库名;
USE 库名;

3.2 建表：CREATE TABLE

# 基础
CREATE TABLE 表名(
字段名 数据类型(字符长度), # 金额，日期不用设置字符串长度

# 加在后面
姓名 VARCHAT(20) NOT NULL， # NOT NULL：属性不可为空

# 日期
日期 DATETIME,
系统时间 DATETIME DEFAULT(GETDATE()), # DEFAULT()：为属性设置默认值，GETDATE()：获取系统时间的函数

# 约束条件
定金金额 MONEY CHECK(定金金额 >= 200), # CHECK：为属性设置约束条件（这里为：至少是200元）

# 主键
证件号 INT(18) PRIMART KEY, # 2选1
PRIMART KEY(字段名), # 设置指定属性为 主键

# 外键
职位 CHAT(8) FOREIGN KEY REFERENCES 职位信息表(职位), # 2选1
FOREIGN KEY(该表字段名) REFERENCES 引用外键的表(引用的字段), # FOREIGN KEY()：设置本表字段名， REFERENCES：引用至什么表的字段

# 外键的不同处理
房间号 CHAR(5) FOREIGN KEY REFERENCES 房号信息表(房号) ON DELETE SET NULL
# ON DELETE RESTRICT：（不可删）被引用时，不可删除引用表中的主键
# ON DELDTE CASCADE： （全都删）允许删除引用表中的主键，但有引用其外键的行将一起删除
# ON DELETE SET NULL：（删的改空）允许删除引用表中的主键，但删除后引用该主键的外键，属性该为空

# 唯一约束
UNIQUE(不可重复的属性),
);

3.3 索引：CTEATE INDEX

聚焦索引：按索引的属性值在表内排序
逻辑索引/非聚焦：由属性值和其对应的地址/地址集组成

# 索引需要多列时，用逗号隔开

# 在 旅客 表中的 工作单位 属性建立名为 查看旅客 的索引
# 在表上创建一个简单的索引。允许使用重复的值
CREATE INDEX index_name
ON table_name (column_name)

# 在表上创建一个唯一的索引。不允许使用重复的值（不能有两个行不可拥有相同的索引值）
CREATE UNIQUE INDEX index_name 
ON table_name (column_name)

# 聚焦索引？
CREATE CLUSTERED INDEX index_name 
ON table_name (column_name)

3.4 添加数据：INSERT

# 方法1：只提供被插入的值
INSERT INTO table_name
VALUES (value1,value2,value3,...);

# 方法2：指定列名及被插入的值
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

3.5 删除表数据：DELETE

# 不删除表情况下，清除所有数据
DELETE FROM table_name;
DELETE * FROM table_name;

# 删除符合条件的内容，WHERE 条件语句
DELETE FROM table_name
WHERE some_column = some_value;

3.6 撤销索引、表、库：DROP

# 撤销表
DROP TABLE 表名; # 表中数据一并删除

# 撤销索引
DROP INDEX 表名.索引名;

# 撤销库
DROP DATABASE 库名;

# 撤销视图
DROP VIEW 视图名;

# 撤销表中列
ALTER TABLE 表名
DROP COLUMN 字段名;

3.7 修改表中属性值：UPDATE

# UPDATE：指明需要更新的表，SET：将属性赋值给给要更新的属性列，WHERE：筛选条件
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

3.8 添加新属性/字段：ALTER

# 表中添加列
ALTER TABLE table_name
ADD column_name 数据类型(字符长度);

# 撤销表中列
ALTER TABLE 表名
DROP COLUMN 字段名;

4. 数据库的查询

需要显示的数据：SELECT

SELECT * FROM 表; # 查看表中所以数据

# 查看表中指定列的数据
SELECT column_name,column_name
FROM 表名;

# 使用单引号包括可以表示他为字符串表达式
SELECT column_name,'保底消费+而外消费=（打9折）',总金额 *0.9
FROM 表名;

取别名：AS

# 列的别名
SELECT column_name,column_name AS 起的别名
FROM table_name;

# 表的别名
SELECT column_name(s)
FROM table_name AS 起的别名;

空值判断：IS NULL

IS NULL：判断 为空
IS NOT NULL：判断 不为空

# 检索指定字段为空的所有行
SELECT *
FROM table_name
WHERE 离开时间 IS NULL; # 判断 属性 的 值 是否为空

排序：ORDER B（默认：升序）

升序：ASC
降序：DESC

# 第1种：常规用法
SELECT column_name,column_name
FROM table_name
ORDER BY No1_column,No2_column 排序规则; # 列名也可用 重命名

# 第2种：使用数字确定
SELECT 属性1,属性2,属性3
FROM table_name
ORDER BY 2,3 DESC; # 使用清单中 第2列 的 属性 用升序，第3列 用降序

条件设置

条件判断：WHERE

SELECT column_name,column_name
FROM table_name
WHERE column_name 条件语句 判断值; # 检索字符串时要用单引号括起来

运算符：EXISTS、NOT EXISTS

EXISTS：用于查看语句所查询的内容是否存在，存在返回 True，否则返回 False

不晓得是啥运算符：ALL、ANY、SOME

ALL：所有的都符合
ANY：至少符合其中一个值（与 IN 作用相似）
SOME：

NOT EXISTS：用于查看语句所查询内容所不返回的内容

SELECT column_name
FROM table_name
WHERE EXISTS
(SELECT column_name
FROM table_name
WHERE condition
);

逻辑运算符：AND、OR、NOT

可以用括号进行嵌套

AND（与）：两边都符合

WHERE 可住人数 = 2 AND 价格 <= 200; # 可住2人，且价格低于200

OR（或）：符合一个即可

WHERE 可住人数 = 2 AND 可住人数 = 3; # 可住2人，或者可住3人

NOT（非）：不符合

WHERE NOT 可住人数 = 2; # 可住不等于2人即可

操作符

多个OR的简化：IN、NOT IN

IN：返回符合的值，允许在 WHERE 子句中规定多个值
也可理解为替代多个OR以简化查询
NOT IN：返回不符合的值

SELECT column_name
FROM table_name
WHERE column_name IN (value1,value2,...); # 把符合IN中条件的都显示

获取区域内数据：BETWEEN

# BETWEEN：选取介于两个值之间的数据范围内的值。
# 值可以是数值、文本或者日期，也可以是条件语句
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

匹配字符：LIKE

SELECT column_name
FROM table_name
WHERE column_name LIKE pattern; # pattern：需要匹配的内容，配合通配符使用

关于通配符

_（下划线）：匹配单个字符
%：匹配0或多个字符集合
[charlist]：匹配字符列中的任何单一字符
[^charlist] 或 [!charlist]：匹配不在字符列中的任何单一字符

聚焦查询

返回不重复的值：DISTINCT

注意！：在 DISTINCT 中所有 NULL / 空值 都被认为是相同的值

# DISTINCT：显示 字段 中所有不重复的 值
SELECT DISTINCT column_name,column_name
FROM table_name;

各种函数

聚合函数：COUNT、AVG、MIN、MAX、SUM

统计记录数目：COUNT()

# COUNT(字段名)：返回指定列不为空的记录的条数（NULL 不计数）
SELECT COUNT(column_name)
FROM table_name;

# 查询所有记录的条数
SELECT COUNT(*)
FROM table_name;

# COUNT(DISTINCT 字段名)：返回指定列中不重复的记录条数
SELECT COUNT(DISTINCT column_name)
FROM table_name;

计算类：SUM()、AVG()、MIN()、MAX()

在最大/小值函数中：
NULL：不大于小于任何值
DISTINCT：可以共同使用，但是由于其只返回一个值，所以毫无意义

# 总数：SUM()
SELECT SUM(column_name) # 计算该列的数值的总和
FROM table_name;

# 平均值：AVG()
SELECT AVG(column_name) # 计算该列的数值的算数平均值
FROM table_name

# 最小值：MIN()
SELECT MIN(column_name) # 返回该列最小值
FROM table_name;

# 最大值：MAX()
SELECT MAX(column_name) # 返回该列最大值
FROM table_name;

分类聚合

聚合函数：GROUP BY

GROUP BY可以配合所有聚焦函数使用

# 结合聚合函数，根据一个或多个列对结果集进行分组

# 简单应用
SELECT column_name, 聚焦函数(表.字段) AS nums
FROM table_name
GROUP BY column_name;


# 组合用法：涉及 HAVING 子句
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

HAVING 子句

因为 WHERE 无法与聚合函数一起使用，所以通过 HAVING 子句可以让我们筛选分组后的各组数据

连接查询：JOIN

JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

INNER JOIN：如果表中有至少一个匹配，则返回行（JOIN的默认方法）

image.png

# 交集

# 用 JOIN
SELECT column_name
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

# 用 INNER JOIN
SELECT column_name
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

image.png

# 左连接
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

image.png

# 右连接
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

FULL JOIN：只要其中一个表中存在匹配，则返回行。其结合了 LEFT JOIN 和 RIGHT JOIN 的结果

image.png

# 合集
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

时间函数

# 获取系统时间的函数
GETDATE()

# 计算俩个日期型数据的差额
DATEDIFF(datapart,startdate,enddate) # datapart：设置计算哪部分的差额（Month，Day）

数据转换

# 转大写
UPPER(列名)
LOWER(列名)

视图：CREATE VIEW

视图的作用：

1、视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。
2、视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）
3、加强了安全性，使用户只能看到视图所显示的数据。
4、视图还可以被嵌套，一个视图中可以嵌套另一个视图。

视图的优点：

1.方便，不用每次查看数据都写一次查询语句
2.隐藏规范化效果

注意点：

1.视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据
2.不能包含 ORDER BY 排序语句
3.不能包含 INTO 关键字 插入数据
4.不能引用临时表或表变量

创建视图

# CREATE VIEW：不进行判断，若数据库中已经存在的话，则报错，说对象已存在；
# CREATE OR REPLACE VIEW：意思就是若数据库中已经存在这个名字的视图的话，就替代它，若没有则创建视图

# 创建视图：CREATE VIEW
CREATE VIEW [视图名称] (别名1,别名2,...) # 创建视图的命令，并为视图中的列设置别名（用逗号隔开）
AS 
SELECT column_name(s) # 定义视图的 SELECT 语句（无语句差别）
FROM table_name
WHERE condition

# 更新视图：CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW [需要更新的视图名称]
AS
SELECT column_name1,column_name2,...
FROM table_name
WHERE condition

第4章数据库理论