数据库基本理论

Reference

数据库基础

Navicat 和 idea 连接数据库
多种数据库SQL和NoSQL
数据库模型
- 关系型数据库模型现实世界特定场景下关系的抽象
- ER图表示
数据库创建

数据库规范化

设计优良的数据库
第一范式
- 所有项必须是为不可分割的项
- 关系型数据库的最基本要求
第二范式
- 表中必须存在主键
第三范式
- 在满足第二范式的基础上，所有的属性都不传递依赖于主键
- 举例：
  
  学生借书情况（借阅编号，学生学号，书籍编号，书籍名称，书籍作者）
  
  实际上书籍编号依赖于借阅编号，而书籍名称和书籍作者依赖于书籍编号，因此存在传递依赖的情况，我们可以将书籍信息进行单独拆分为另一张表：
  - 学生借书情况（借阅编号，学生学号，书籍编号）
  - 书籍（书籍编号，书籍名称，书籍作者）
  这样就消除了依赖
BCNF
- BCNF作为第三范式的补充
- 举例
  
  假设仓库管理关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：
  - (仓库ID, 存储物品ID) →(管理员ID, 数量)
  - (管理员ID, 存储物品ID) → (仓库ID, 数量)
  所以，(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：
  - (仓库ID) → (管理员ID)
  - (管理员ID) → (仓库ID)
  即存在关键字段决定关键字段的情况，如果修改管理员ID，那么就必须逐一进行修改，所以其不符合BCNF范式。

数据类型

char(n)可以存储任意字符串，但是是固定长度为n，如果插入的长度小于定义长度时，则用空格填充。
varchar(n)也可以存储任意数量字符串，长度不固定，但不能超过n，不会用空格填充。

以下数据类型用于存储数字：

smallint用于存储小的整数，范围在 (-32768，32767)
int用于存储一般的整数，范围在 (-2147483648，2147483647)
bigint用于存储大型整数，范围在 (-9,223,372,036,854,775,808，9,223,372,036,854,775,807)
float用于存储单精度小数
double用于存储双精度的小数

以下数据类型用于存储时间：

date存储日期
time存储时间
year存储年份
datetime用于混合存储日期+时间

DDL

创建数据库

-- 创建数据库
create database 数据库名

-- 为了能够支持中文，我们在创建时可以设定编码格式：
CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

-- 删除数据库
drop database 数据库名

创建表

-- 创建表
create table 表名(列名 数据类型[列级约束条件],
             列名 数据类型[列级约束条件],
             ...
             [,表级约束条件])
-- 修改表
ALTER TABLE 表名[ADD 新列名 数据类型[列级约束条件]]
							 [DROP COLUMN 列名[restrict|cascade]]
							 [ALTER COLUMN 列名 新数据类型]
-- 删除表            
DROP TABLE 表名[restrict|cascade]

创建约束条件

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

DML

-- 插入数据
INSERT INTO 表名 VALUES(值1, 值2, 值3)
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2)
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2), (值1, 值2), (值1, 值2)

-- 修改数据
UPDATE 表名 SET 列名=值,... WHERE 条件

-- 删除数据
DELETE FROM 表名
DELETE FROM 表名 WHERE 条件

DQL

-- 指定查询某一列数据
SELECT 列名[,列名] FROM 表名
-- 会以别名显示此列
SELECT 列名 别名 FROM 表名
-- 查询所有的列数据
SELECT * FROM 表名
-- 只查询不重复的值
SELECT DISTINCT 列名 FROM 表名

-- where
SELECT * FROM 表名 WHERE 条件

一般的比较运算符，包括=、>、<、>=、<=、!=等。
是否在集合中：in、not in
字符模糊匹配：like，not like
多重条件连接查询：and、or、not

-- 查询结果进行排序
SELECT * FROM 表名 WHERE 条件 ORDER BY 列名 ASC|DESC
-- 多个排序
SELECT * FROM 表名 WHERE 条件 ORDER BY 列名1 ASC|DESC, 列名2 ASC|DESC

聚集函数一般用作统计，包括：

count([distinct]*)统计所有的行数（distinct表示去重再统计，下同）
count([distinct]列名)统计某列的值总和
sum([distinct]列名)求一列的和（注意必须是数字类型的）
avg([distinct]列名)求一列的平均值（注意必须是数字类型）
max([distinct]列名)求一列的最大值
min([distinct]列名)求一列的最小值

SELECT count(distinct 列名) FROM 表名 WHERE 条件 

-- 分组分页
SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名
-- 添加having来限制分组
SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 约束条件
-- 限制查询数量
SELECT * FROM 表名 LIMIT 数量
-- 分页
SELECT * FROM 表名 LIMIT 起始位置,数量

-- 多表查询
SELECT * FROM 表1, 表2
SELECT * FROM 表1, 表2 WHERE 条件

-- 自身连接查询
SELECT * FROM 表名 别名1, 表名 别名2

-- 嵌套查询
SELECT * FROM 表名 WHERE 列名 = (SELECT 列名 FROM 表名 WHERE 条件)

DCL

-- 创建用户
CREATE USER 用户名 identified by 密码;
-- 不带密码创建
CREATE USER 用户名;

-- 登录用户
-- 添加一个环境变量
login -u 用户名 -p

show databases;

-- 用户授权
grant all|权限1,权限2...(列1,...) on 数据库.表 to 用户 [with grant option]

-- 收回权限
revoke all|权限1,权限2...(列1,...) on 数据库.表 from 用户

视图

CREATE VIEW 视图名称(列名) as 子查询语句 [WITH CHECK OPTION];

drop view apptest

索引

hash和二叉树实现
在数据庞大时加快查询速度

-- 创建索引
CREATE INDEX 索引名称 ON 表名 (列名)
-- 查看表中的索引
show INDEX FROM student

-- 删除
drop index 索引名称 on 表名

触发器

检查内容安全性

CREATE TRIGGER 触发器名称 [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON 表名/视图名 FOR EACH ROW DELETE FROM student WHERE student.sno = new.sno
-- 查看触发器
SHOW TRIGGERS
-- 删除此触发器
DROP TRIGGER 触发器名称

事务

inodb引擎默认支持，但是可以切换其他的数据库引擎，完成一些列操作的
事务特性
- **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

语句

begin;   #开始事务
...
rollback;  #回滚事务
savepoint 回滚点;  #添加回滚点
rollback to 回滚点; #回滚到指定回滚点
...
commit; #提交事务
-- 一旦提交，就无法再进行回滚了！

函数

复用查询代码

数据库基本理论

Reference

数据库基础

数据库规范化

数据类型

DDL

DML

DQL

DCL

视图

索引

触发器

事务

函数

存储过程