数据库基本概念
数据库 表相关
-
数据库：数据库中存放的是表，一个数据库中可以存放多个表
-
表：表是用来存放数据的
-
关系：两个表的公共字段
-
行：也称记录，也称实体
-
列：也称字段，也称属性
-
理解
- 就表结构而言，表分为行和列
- 就表数据而言，表分为记录和字段
- 就面向对象而言，一个记录就是一个实体，一个字段就是一个属性
数据相关
-
数据冗余：相同的数据存储在不同的地方
- 冗余只能减少，不能杜绝
- 减少冗余的方法是分表
-
数据完整性：正确性+准确性=数据完整性
- 正确性：数据类型正确
- 准确性：数据范围要准确
数据库执行过程
- 客户端
- 连接数据库
- 发送SQL指令
- 返回结果
- 发送SQL指令
- 返回结果
- 关闭连接
- MySQL
MySQL数据库的目录
-
数据库保存的路径在安装MySQL的时候就配置好
-
在my.ini配置文件中更改数据库的保存地址
datadir="F:/wamp/PHPTutorial/MySQL/data/"
-
一个数据库就对应一个文件夹，在文件夹中有一个db.opt文件
- 在此文件中设置数据库的字符集和校对集
MySQL数据库报错
-
如果创建的数据库已存在，会报错
- 创建数据库的时候判断一下数据库是否存在，如果不存在再创建
-
如果数据库名是关键字和特殊字符，要报错
- 在特殊字符、关键字行加上反引号
-
创建数据库的时候可以指定字符编码
- 创建数据库如果不指定字符编码，默认和MySQL服务器的字符编码是一致的
SQL注释
-
单行注释
-- 单行注释# 单行注释
-
多行注释
/* 多行注释 */
连接服务器
-
浏览器键入
http://localhost/phpmyadmin/index.php
-
客户端
MySQL Workbench
-
命令行接入
- XAMPP中
Shell面板 - 命令行名词
host-h 主机port-p 端口号user-u 用户名password-p 密码
- 连接数据库
mysql -h127.0.0.1 -P3306 -uroot -proot明文mysql -uroot -proot明文 (本地数据库 端口号3306)mysql -uroot -p密文
- 退出登录
exitquitq
- XAMPP中
编码设置
- 语法
set names gbk;一次性设置客户端和服务器通讯的编码set character_set_client=gbk;更改接受客户端指令的编码- 设置什么编码取决于客户端的编码
校对集
- 语法
collate=utf8_general_ci;不区分大小写collate=utf8_bin;按二进制编码比较，区别大小写
mysql> create table stu1(
-> name char(1)
-> )charset=utf8 collate=utf8_general_ci;
# `Query OK, 0 rows affected (0.05 sec)`
mysql> create table stu2(
-> name char(1)
-> )charset=utf8 collate=utf8_bin;
# `Query OK, 0 rows affected (0.05 sec)`
mysql> insert into stu1 values ('a'),('B');
# `Query OK, 2 rows affected (0.00 sec)`
# `Records: 2 Duplicates: 0 Warnings: 0`
mysql> insert into stu2 values ('a'),('B');
# `Query OK, 2 rows affected (0.00 sec)`
# `Records: 2 Duplicates: 0 Warnings: 0`
数据库设计
实体之间的关系
-
一对多(1:N)
- 主表中的一条记录对应从表中的多条记录
- 实现一对多的方式
- 主键和非主键建关系
-
多对一(N:1)
- 多对一就是一对多
-
一对一(1:1)
- 如何实现一对一
- 主键和主键建关系
- 一对一两个表完全可以用一个表实现，为什么还要分成两个表？
- 表的垂直分割
- 在字段数量很多情况下，数据量也就很大
- 每次查询都需要检索大量数据，这样效率低下
- 将所有字段分成两个部分，“常用字段”和“不常用字段”
- 这样对大部分查询者来说效率提高了
- 表的垂直分割
- 如何实现一对一
-
多对多(N:N)
- 主表中的一条记录对应从表中的多条记录
- 从表中的一条记录对应主表中的多条记录
- 如何实现多对多
- 利用第三张关系表
-
小结
- 如何实现一对一：主键和主键建关系
- 如果实现一对多：主键和非主键建关系
- 如何实现多对多：引入第三张关系表
数据库设计
- 数据库设计的步骤
- 收集信息：与该系统有关人员进行交流、坐谈，充分理解数据库需要完成的任务
- 标识对象（实体－Entity）：标识数据库要管理的关键对象或实体
- 标识每个实体的属性（Attribute）
- 标识对象之间的关系（Relationship）
- 将模型转换成数据库
- 数据规范化
第一步：收集信息
- BBS论坛的基本功能
- 用户注册和登录，后台数据库需要存放用户的注册信息和在线状态信息
- 用户发贴，后台数据库需要存放贴子相关信息，如贴子内容、标题等
- 用户可以对发帖进行回复
- 论坛版块管理：后台数据库需要存放各个版块信息，如版主、版块名称、贴子数等
第二步：标识对象
- 实体一般是名词
- 用户对象
- 板块对象
- 帖子对象
- 跟帖对象
第三步：标识每个实体的属性
-
用户对象
- 昵称
- 密码
- 电子邮件
- 生日
- 性别
- 用户的等级
- 备注信息
- 注册日期
- 状态
- 积分
-
板块对象
- 板块名称
- 版主
- 本版格言
- 点击率
- 发帖数
-
帖子对象
- 发帖人
- 发帖表情
- 回复数量
- 标题
- 正文
- 发帖时间
- 点击数
- 状态
- 最后回复时间
-
跟贴对象
- 帖子编号
- 回帖人
- 回帖表情
- 标题
- 正文
- 回帖时间
- 点击数
第四步：标识对象之间的关系
-
绘制E-R图
- E-R（Entity－Relationship）实体关系图
-
符号及含义
□ 空方块实体，一般是名词○ 空圆属性，一般是名词◇ 空菱形关系，一般是动词
graph TD;
用户对象 --> 管理 --> 板块
用户对象 --> 昵称
用户对象 --> ...
- 将E-R图转成表
- 实体转成表，属性转成字段
- 如果没有合适的字段做主键，给表添加一个自动增长列做主键
第五步：将模型转化为数据库
- 用EA软件
第六步：数据规范化
-
数据库设计三范式
- 第一范式：确保每列原子性
- 确保每列的原子性，一个字段表示一个含义
- 第二范式：非关键字段必须依赖于关键字段
- 在满足第一范式的前提下，要求每个表只描述一件事情
- 第三范式：消除传递依赖
- 在满足第二范式的前提下，除了主键以外的其他列消除传递依赖
- 第一范式：确保每列原子性
-
反3NF
- 范式越高，数据冗余越少，但是效率有时就越地下，为了提高运行效率，可以适当让数据冗余。
-
例题说明
- 下面的设计不满足第三范式，但是高考分数表就是这样设计的
- 高考分数峰值访问量非常大，这时候就是性能更重要
- 当性能和规范化冲突的时候，我们首选性能
- 这就是“反三范式”
| 学号 | 姓名 | 语文 | 数学 | 总分 |
|---|---|---|---|---|
| 1 | 李白 | 77 | 88 | 165 |
- 小结
- 第一范式约束的所有字段
- 第二范式约束的主键和非主键的关系
- 第三范式约束的非主键之间的关系
- 范式越高，冗余越少，但表数业越多
- 规范化和性能的关系 ：性能比规范化更重要
数据库设计例题
-
需求
- 假设某建筑公司要设计一个数据库
- 公司的业务规则概括说明如下
- 公司承担多个工程项目，每一项工程有：工程号、工程名称、施工人员等
- 公司有多名职工，每一名职工有：职工号、姓名、性别、职务(工程师、技术员)等
- 公司按照工时和小时工资率支付工资，小时工资率由职工的职务决定(例如，技术员的小时工资率与工程师不同)
-
标识实体
- 工程表
- 职工表
- 职务表
- 工时表
-
数据库设计
- 工程表 --> 工程号 -> 工程名称
- 员工表 --> 职工表 -> 姓名 + 职务
- 职务表 --> 职务 -> 小时工资率
- 工时表 --> 工程号 + 职工号 -> 工时
更改定界符 delimiter
-
一般情况下的定界符
- 其实就是告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了
- 默认情况下,delimiter是分号;
- 在命令行客户端中,如果有一行命令以分号结束,那么回车后,mysql将会执行该命令
-
可能输入较多的语句,且语句中包含有分号时的定界符设置
- 默认情况下,不可能等到用户把这些语句全部输入完之后,再执行整段语句
- 因为mysql一遇到分号,它就要自动执行
- 这种情况下,就需要事先把delimiter换成其它符号,如//或$$
MariaDB [sel]> create table bank(
-> card char(4) primary key comment '卡号',
-> money decimal(10,2) not null
-> )charset=utf8;
# `Query OK, 0 rows affected (0.023 sec)`
MariaDB [sel]> insert into bank values ('1001',1000),('1002',10);
# `Query OK, 2 rows affected (0.012 sec)`
# `Records: 2 Duplicates: 0 Warnings: 0`
MariaDB [sel]> begin;
# `Query OK, 0 rows affected (0.000 sec)`
MariaDB [sel]> delimiter //
MariaDB [sel]> update bank set money=money-100 where card='1001';
-> update bank set money=money+90 where card='1002'//
# `Query OK, 1 row affected (0.008 sec)`
# `Rows matched: 1 Changed: 1 Warnings: 0`
Query OK, 1 row affected (0.008 sec)
# `Rows matched: 1 Changed: 1 Warnings: 0`
MariaDB [sel]> rollback //
# `Query OK, 0 rows affected (0.008 sec)`
MariaDB [sel]> select * from bank //
+------+---------+
| card | money |
+------+---------+
| 1001 | 1000.00 |
| 1002 | 10.00 |
+------+---------+
# `2 rows in set (0.000 sec)`