＜mysql-数据库设计与实现＞学习总结

MySQL命名、设计及使用规范

MySQL表设计

Online Resources for
Kroenke
数据库处理——基础、设计与实现（第十三版）是我们学校的教科书，我之前没有耐心去看教科书。但细读之后发现，其实是一本系统性的、条理清晰的书籍。正如其书名而言，涵盖了数据库处理的关键基础与操作。系统学习这本书，会对数据库的理解会加深不少。

一、数据库设计基础理论

数据库系统由四个部分组成：用户，数据库应用程序，数据库管理系统（DBMS），数据库。

DBMS：负责创建、处理和管理数据库。

数据库将数据存放在表中，每个表包含不同类型事物的数据。事物的实例被存放在表的行中，实例的特性被存放在列中。数据库存储数据和数据间的联系。

数据库设计（作为一个过程）是指制定合理的表结构、合理的表之间的联系、恰当的数据约束和其他的结构化元素。

三种类型的数据库设计：

• 在已有的数据上进行数据库设计
• 在新系统的开发上进行数据库设计
• 在已有的数据库上进行数据库的重新设计

规范化可以被用来指导从已有数据设计数据库的过程。数据模型被用来作为创建系统需求的一个蓝图，这个蓝图接下来被转化为数据库设计。大多数数据模型使用实体-联系模型创建。当一个已经存在的数据被更改来支持新的或是变化的需求，或当两个或多个数据库被集成在一起时，则产生数据库的重设计。

数据库处理简史:
NoSQL运动、大数据、可视化和云计算是目前数据库处理的前沿领域。

三个完整性约束：

• 域完整性约束：域表示满足特定类型定义的一个分组内的数据，在关系中，列就是一个域，一列的所有取值都具有相同的数据类型。
• 实体完整性约束：无论主键是由一列还是多列组成，插入到表中的每一行数据都必须是唯一的。
• 参照完整性约束：每一个外键的值都与一个合法的主键值相匹配，创建这个约束来限制外键的取值。

定义这三种约束的目的，总体上来说是为了创建数据库约束，也就是说数据库中存储的数据是有用的、有意义的数据。

函数依赖： NumberOfBoxes→CookieCost
左边变量为决定因素。函数依赖不是等式。
组合函数依赖： 函数依赖的决定因素可以包含多个属性。(StudentName, ClassName)→Grade，这时，决定因素被称为组合决定因素。

关键字(key)

异常源：函数依赖

异常源：多值依赖

多值依赖：当决定因素与某特定的值集合相匹配时，就存在多值依赖。

4NF：将每个多值依赖都放置在它自己的表中。

规范化最好表示的是：既是BCNF的，又是4NF的。

异常源：数据完整性和奇特关系

5NF：一般一个关系是4NF，那它是5NF。
DK/NF：域/关键字范式，一个不存在修改异常的关系。每一个函数依赖的决定因素都是一个候选键。从实用目的来说，BCNF关系也就是DK/NF。

E-R模型中，联系可以根据它的粒度用一个表示“数量”的词来分类。
最大粒度表示一个联系实例涉及到的实体实例的最大数目。
最小粒度表示一个联系实例涉及到的实体实例的最小数目。

使用SQL视图
**一个SQL视图是从其他表或视图构造出的一个虚拟表。**一个视图本身没有数据，而是从其他表或视图中取得数据。视图使用SQL SELECT语句构造的，视图名可以像表名那样用在其他SQL SELECT语句中的WHERE子句中。用来构造视图的SQL语句的唯一限制是它不允许有ORDER BY子句，需要由处理视图的SELECT语句提供排序。

构造

CREATE VIEW CustomerNameView AS
	SELECT LastName AS CustomerLastName,
			FirstName AS CustomerFirstName
	FROM CUSTOMER;

使用

SELECT *
FROM CustomerNameView
ORDER BY CustomerLastName, CustomerFirstName;

视图的用途

• 隐藏字段或记录
• 显示计算结果
• 隐藏复杂的SQL语法
• 层次化内置函数
• 在表数据和用户视图数据之间提供隔离层
• 为同一张表的不同视图指派不同的处理许可
• 为同一张表的不同视图指派不同的触发器许可

使用SQL触发器
触发器是当特定的事件发生时，由DBMS执行的存储程序。
触发器是和一张表或一个视图关联的。当触发器所附着的表或视图上发生插入、更新、删除时，触发器程序将会被调用。

SQL触发器的应用

• 提供默认值
• 满足数据约束
• 更新视图
• 执行参照完整性行为

1.使用触发器提供默认值
DEFAULT关键字能为一个字段指定一个初始值，仅仅可用于简单的表达式，如果指定默认值时要求较为复杂的逻辑，就需要用一个触发器。

CREATE TRIGGER TRANS_AskingPriceInitialValue
	AFTER INSERT ON TRANS
DECLARE
	rowCount int;
	sumNetProfit Numeric(10,2);
	avgNetProfit Numeric(10,2);
BEGIN
	SELECT Count(*) INTO rowCount
	FROM TRANS AS T
	WHERE new:WorkID = T.workID;

	IF (rowcount = 1)
	THEN 
		SELECT SUM(NetProfit) into sumNetProfit
		FROM ArtistWorkNetView AMNV

使用存储过程
存储过程是存放在数据库中的程序，执行一些常用的数据库操作。存储过程可以接受输入参数并返回结果。

不同点：
触发器：

• 当INSERT,UPDATE和DELETE命令执行时，由DBMS调用的代码模块
• 指派到一张表或视图中
• 依赖于DBMS，每张表或视图可能有多个触发器
• 触发器也可能引发INSERT，UPDATE和DELETE命令，因而可能会激发其他触发器。

存储过程：

• 由用户或数据库管理员调用的代码模块
• 指派到一个数据库中，而不是一张表或视图
• 会引发INSERT,UPDATE和DELETE命令
• 用于重复性的管理任务或作为应用程序的一部分

存储过程的优点：

• 更高安全性
• 减少网络传输量
• SQL能被优化
• 代码共享
• 更少的工作量
• 标准化处理
• 开发者特殊化

CREATE PROCEDURE WORK_AddWorkTransaction
	(
	@ArtistID	Int,
	@Title	Char(25),
	

并发性控制：
并发性控制手段用来确保一个用户的工作不会不适当地影响其他用户的工作。

防止并发性处理问题最常用的一种方法时：通过对修改所要检索的数据进行加锁来阻止其被共享。

乐观型加锁是假设一般不会有冲突发生。读取数据，处理事务，发生修改更新命令，然后检查是否出现了冲突。如果没有，事务便宣告结束。如果有冲突出现，则重复执行该事务，直到不再出现冲突为止。

悲观型加锁则假设冲突很可能会发生。首先发出加锁命令，接着处理事务，最后再解锁。

---

二、问题解决

1.sql运行错误

Unknown collation: 'utf8mb4_0900_ai_ci'

将sql文件中的所有
utf8mb4_0900_ai_ci替换为utf8_general_ci
utf8mb4替换为utf8

三、实战

1.随身物品推销商使用的订单录入系统

MySQL必知必会的样例表。

1.1 任务

• 管理供应商；
• 管理产品目录；
• 管理顾客列表；
• 录入顾客订单。

1.2 SQL架构

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table creation scripts
########################################


########################
# Create customers table
########################
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

#########################
# Create orderitems table
#########################
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;


#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

#######################
# Create products table
#######################
CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

###########################
# Create productnotes table
###########################
CREATE TABLE productnotes
(
  note_id    int           NOT NULL AUTO_INCREMENT,
  prod_id    char(10)      NOT NULL,
  note_date datetime       NOT NULL,
  note_text  text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;


#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

1.3 模型

2.学生考勤签到系统

先简单实现，一步一步把它优化成一个完整的系统。

2.1 版本V1

只考虑记录学生签到时间

CREATE TABLE student
(
  stu_id INT NOT NULL AUTO_INCREMENT,
  stu_name CHAR(50) NOT NULL,
  class_name CHAR(50)  NOT NULL,
  PRIMARY KEY (stu_id)
) ENGINE=INNODB;

CREATE TABLE signin
(
	sign_id INT NOT NULL,
	stu_id INT NOT NULL,
	sign_time datetime NOT NULL,
	PRIMARY KEY (sign_id,stu_id)
) ENGINE=INNODB;

ALTER TABLE signin ADD CONSTRAINT fk_signin_student FOREIGN KEY (stu_id) REFERENCES student (stu_id);

2.2 版本V2

在这里插入代码片