MySQL数据库基础--权限管理与备份、规范数据库设计详解

权限管理与备份

用户管理

1. SQLyog 可视化管理

注意：主机选择的是什么连接的时候就是什么

2. SQL命令操作

   用户表：mysql.user

   本质：对这张表进行增删改查

   -- 创建用户  create user 用户名 identified by '密码'
   CREATE USER Sucker IDENTIFIED BY '123456' -- 创建一个用户并定义密码
   
   -- 修改密码（修改当前用户密码）
   SET PASSWORD = PASSWORD('123456')
   
   -- 修改密码（修改指定用户密码）
   SET PASSWORD FOR Sucker = PASSWORD('111111')
   
   -- 重命名  rename user 原名 to 新名
   RENAME USER Sucker TO Sucker2
   
   -- 用户授权  all privileges  全部的权限 on  库.表
   -- ALL PRIVILEGES 除了给别人授权，其他都能干，可以在管理员界面将grant勾选上
   GRANT ALL PRIVILEGES ON *.* TO Sucker
   
   -- 查询权限
   SHOW GRANTS FOR Sucker  -- 查看指定用户的权限
   SHOW GRANTS FOR root@localhost
   
   -- 撤销权限 REVOKE 哪些权限 在哪个库撤销 给谁撤销
   REVOKE ALL PRIVILEGES ON *.* FROM Sucker
   
   -- 删除用户
   DROP USER Sucker
   

MySQL备份

1. 备份原因：

   • 保证重要的数据不丢失
   • 数据转移

2. MySQL数据库备份的方式

   • 直接拷贝物理文件

   • 在SQLyog这种可视化工具中手动导出

     • 在想要导出的表或库中右键备份或导出，选择SQL转储

   • 使用命令行 mysqldump

     -- mysqldump -h主机 -u用户名 -p密码  数据库 表名 > 物理磁盘位置/文件名
     mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
     
     -- mysqldump -h主机 -u用户名 -p密码  数据库 表1 表2 表3 > 物理磁盘位置/文件名
     mysqldump -hlocalhost -uroot -p123456 school student result >D:/a.sql
     
     -- mysqldump -h主机 -u用户名 -p密码  数据库 > 物理磁盘位置/文件名
     mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql
     
     -- 导入
     -- 登录的情况下，切换到指定的数据库
     -- source 备份文件
     source D:/a.sql
     
     -- 未登录
     mysql -u用户名 -p密码 数据库名 <文件路径
     

规范数据库设计

设计原因

当数据库比较复杂时，需要设计

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会麻烦、异常【物理外键】
• 程序性能差

良好的数据库设计：

• 节省内存空间
• 保证数据库完整性
• 方便开发系统

软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库需求
• 概要设计：设计关系图 E-R图

设计数据库的步骤：（个人博客）

• 收集信息，分析需求
  • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建的）
  • 文章表（文章的信息）
  • 评论表
  • 友链表（友链信息）
  • 自定义表（系统信息，某个关键的字，或者一些主字段）
• 标识实体（把需求落地到每个字段）
• 标识实体之间的关系
  • 写博客：user -> blog
  • 创建分类：user -> category
  • 关注：user -> user
  • 友链：links
  • 评论：user -> user -> blog

三大范式

数据规范化的原因

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 丢失有效的信息

三大范式

参考博客：

1. 第一范式（1NF）

   • 原子性：保证每一列不可再分
     • 例如学校信息列内不能分为硕士，研二或本科，大二

2. 第二范式（2NF）

   • 前提：满足第一范式
   • 第二范式需要确保数据库的每一列都与主键相关，而不能只与其部分相关（主要针对联合主键）
     • 例如联合主键：同一个订单可能包含不同产品，所以订单号和产品号为联合主键，而产品数量、产品折扣、产品价格都和订单号与产品号有关，但是订单金额和订单时间只与订单号有关，违反第二范式

3. 第三范式（3NF）

   • 前提：满足第一范式和第二范式

   • 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

     

     上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，

     而不是主键“学号”，所以需做如下调整：

     

     ps:如果把上表中的班主任姓名改成班主任教工号可能更确切，更符合实际情况，不过只要能理解就行。

4. 规范性和性能的问题

   • 考虑商业化的需求和目标 （成本，用户体验） 数据库的性能更加重要
   • 在规范性能的问题时，要适当考虑 规范性！
   • 故意给某些表增加一些冗余字段 （从多表查询变为单表查询）
   • 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）