MySQL数据库之基本概念与设置及数据库设计

数据库基本概念

数据库 表相关

  • 数据库：数据库中存放的是表，一个数据库中可以存放多个表

  • 表：表是用来存放数据的

  • 关系：两个表的公共字段

  • 行：也称记录，也称实体

  • 列：也称字段，也称属性

  • 理解

    • 就表结构而言，表分为行和列
    • 就表数据而言，表分为记录和字段
    • 就面向对象而言，一个记录就是一个实体，一个字段就是一个属性

数据相关

  • 数据冗余：相同的数据存储在不同的地方

    • 冗余只能减少，不能杜绝
    • 减少冗余的方法是分表

  • 数据完整性：正确性+准确性=数据完整性

    • 正确性：数据类型正确
    • 准确性：数据范围要准确

数据库执行过程

  • 客户端
    • 连接数据库
    • 发送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 密文
    • 退出登录
      • exit
      • quit
      • q

编码设置

  • 语法
    • 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)`
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/SharkJiao/p/14137656.html