資料庫操作

HuDu發表於2020-07-27
資料庫
系列文章：
MySQL - 初識MySQL 運算元據庫 修改刪除表 建立資料庫表 MySQL 的資料管理及 DML 語句 DQL（Date Query Language）資料庫查詢語句 事務 索引 JDBC 資料庫操作 事務

資料庫許可權管理和備份

使用者管理

使用者表：mysql.user
本質：對這張表進行增刪改查

-- 建立使用者，不指定host預設是%
CREATE USER hudu IDENTIFIED BY '123456';
-- CREATE USER 'username'@'host' IDENTIFIED BY 'password'

-- 修改指定使用者密碼
-- 舊版本使用上面的
-- SET PASSWORD FOR 'hudu'@'%' = PASSWORD('111111');
ALTER USER 'hudu'@'%' IDENTIFIED BY '111111'

-- 重新命名
RENAME USER hudu TO alex;

-- 使用者授權    ALL PRIVILEGES 全部的許可權，庫.表
-- 除了給別人授權，其它許可權都有
GRANT ALL PRIVILEGES ON *.* TO hudu;
--  也可以加上
GRANT ALL PRIVILEGES ON *.* TO 'hudu'@'%' WITH GRANT OPTION;

-- 檢視許可權
SHOW GRANTS FOR hudu;

-- 回收許可權 revoke 哪些許可權，在哪個庫撤銷，給誰撤銷
REVOKE ALL PRIVILEGES ON *.* FROM 'hudu'@'%';

-- 刪除使用者
DROP USER hudu;

資料庫備份

  • 保證重要的資料不丟失
  • 資料轉移 A—–>B

MySQL備份的方式

  • 直接拷貝物理檔案
  • 在視覺化工具中手動匯出
  • 使用命令列匯出 mysqldump命令列使用

視覺化工具navicate備份資料庫

資料庫操作

資料庫操作

資料庫操作

資料庫操作

資料庫操作

  • 資料庫定期備份

資料庫操作

資料庫操作

資料庫操作

  • 命令列匯出

mysqldump -h主機名 -u使用者名稱 -p密碼 資料庫 [表1 表2 表3...] > 物理磁碟位置/檔名

$ mysqldump -hlocalhost -uroot -p123456 school student > ~/File/mysql_backup/1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
  • 匯入
# 先進入資料庫
$ mysql -uroot -p123456
-- 如果是備份的表
use school;
source ~/File/1.sql

直接匯入也是可以的

mysql -u使用者名稱 -p密碼 庫名 < 備份檔案

規範資料庫設計

當資料庫比較複雜的時候，就要設計
糟糕的資料庫設計：

  • 資料冗餘，浪費空間
  • 資料插入和刪除都會麻煩/異常【遮蔽物理外來鍵】
  • 程式的效能差

良好的資料庫設計：

  • 節省記憶體空間
  • 保證資料庫的完整性
  • 方便開發系統

軟體開發中，關於資料庫的設計：

  • 分析需求：分析業務和需要處理的資料庫的需求
  • 概要設計：設計關係圖 E-R圖

設計資料庫的步驟：（個人部落格）

  • 收集資訊，分析需求
    • 使用者表（使用者登陸登出，使用者個人資訊，寫部落格，建立分類）
    • 分類表（文章分類，誰建立的）
    • 文章表（文章的資訊）
    • 評論表
    • 友連結串列（友情資訊）
    • 自定義表（系統資訊，某個關鍵的字，或者一些主欄位）key：value
  • 標識實體類（把需求落地到每個欄位）
create database blog_test character set utf8 collate utf8_general_ci;

-- 使用者資料庫
CREATE TABLE `user`(
    `id` int(11) NOT NULL auto_increment,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(50) NOT NULL,
    `phone` VARCHAR(11) DEFAULT NULL,
    `sex` VARCHAR(2) NOT NULL,
    `age` INT(3) NOT NULL,
    `sign`VARCHAR(200),
    PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 分類資料庫
CREATE TABLE `catrgory`(
    `id` int(10) NOT NULL,
    `category_name` VARCHAR(30) NOT NULL,
    `create_user_id` INT(10) NOT NULL,
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 文章資料庫
CREATE TABLE `blog`(
    `id` int(10) NOT NULL,
    `title` VARCHAR(100) NOT NULL,
    `author_id` int(10),
    `category_id` int(10),
    `content` text NOT NULL,
    `create_time` datetime,
    `update_time` datetime,
    `like` int(10),
    PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

-- 評論表
CREATE TABLE `comment`(
    id int(10) NOT NULL,
    blog_id int(10) NOT NULL,
    user_id int(10) NOT NULL,
    content VARCHAR(2000) NOT NULL,
    create_time datetime NOT NULL,
    user_id_parent int(10) NOT NULL COMMENT '回覆的人的id',
    PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8


-- 友連結串列
CREATE TABLE `links`(
    `id` int(10) NOT NULL,
    `links` VARCHAR(50) NOT NULL COMMENT '網站名稱',
    `href` VARCHAR(2000) NOT NULL COMMENT '網站連結',
    sort int(10) NOT NULL COMMENT '排序',
    PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
  • 標識實體之間的關係
    • 寫部落格：user –> blog
    • 建立分類：user –> category
    • 關注：user –> user
    • 友鏈：links
    • 評論：user-user-blog

三大正規化

  • 資訊重複
  • 更新異常
  • 插入異常
    • 無法顯示資訊
  • 刪除異常
    • 丟失有效的資訊

第一正規化（1NF）

原子性：保證每一列不可再分

第二正規化（2NF）

前提：滿足第一正規化
每張表只描述一件事情

第三正規化（3NF）

前提：滿足第一正規化和第二正規化
第三正規化需要確保資料表中每一列資料都和主鍵直接相關，而不能間接相關。

規範資料庫的設計

規範性和效能的問題
關聯查詢的表不得查過三張表

  • 考慮商業化的需求和目標，成本，使用者體驗，資料庫效能更為重要
  • 在規範效能的問題的時候，需要適當考慮規範性
  • 故意給某些欄位增加一些冗餘的欄位。（從多表查詢中變為單表查詢）
  • 故意增加一些計算列（大資料量降低為小資料量的查詢：索引）
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章