概述

• 關(guān)系型數(shù)據(jù)庫——由表來存儲相關(guān)的數(shù)據(jù)，MySQL，SQL Server，Oracle等都是關(guān)系型數(shù)據(jù)庫；
• 元數(shù)據(jù)：用于集成并管理數(shù)據(jù)。

• MySQL數(shù)據(jù)庫

  • 用戶數(shù)據(jù)庫：用戶根據(jù)需求創(chuàng)建數(shù)據(jù)庫
  • 系統(tǒng)數(shù)據(jù)庫
    • information_schema：主要存儲系統(tǒng)中的一些數(shù)據(jù)庫對象信息，如用戶信息、列信息、權(quán)限信息、字符集信息和分區(qū)信息等
    • performance_schema：存儲數(shù)據(jù)可以服務(wù)器性能參數(shù)
    • mysql：系統(tǒng)的用戶權(quán)限信息
      • test

• MySQl的數(shù)據(jù)對象

  • 表
    • 欄位：該表的列（也叫屬性或者字段）的名字和數(shù)據(jù)類型
    • 索引：根據(jù)指定的數(shù)據(jù)庫表列建立起來的順序，提供快速訪問的路徑且可以監(jiān)督表的數(shù)據(jù)。
  • 視圖
  • 存儲過程（手動調(diào)用，無返回值）
  • 函數(shù)（手動調(diào)用，必須有返回值）
  • 觸發(fā)器（自動調(diào)用）
  • 事件

• 數(shù)據(jù)庫的命名規(guī)則

  • 由字母、數(shù)字、下劃線、@、#、$符號組成，首字母不能是數(shù)字和$符號，標(biāo)識符不允許是MySQL的保留字，不允許有空格和特殊字符，長度小于128位。

• MySQL版本

  • MySQL Community Server（社區(qū)版）：完全免費，官方不提供技術(shù)支持
  • MySQL Enterprise Server（企業(yè)版服務(wù)器）：他能夠以很高性價比為企業(yè)提供數(shù)據(jù)倉庫應(yīng)用，支持ACID事務(wù)處理，提供完整的提交、回滾、崩潰恢復(fù)和行級鎖定功能。但是該版本付費，官方提供電話支持。

• MySQL的優(yōu)勢：

  • 速度：運行速度快。
  • 價格：MySQL對大多數(shù)個人來說是免費的。
  • 容易使用：與其他大型數(shù)據(jù)庫的設(shè)置和管理相比，其復(fù)雜程度較低，易于學(xué)習(xí)。
  • 可移植性：能夠工作在眾多不同的系統(tǒng)平臺，例如：Windows、Linux、Unix、Mac OS。
  • 豐富的接口：提供了用于C、C++、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等語言的API。
  • 支持查詢語言：MySQL可以利用標(biāo)準(zhǔn)SQL語法和支持ODBC的應(yīng)用接口。
  • 安全性和連接性：十分靈活和安全的權(quán)限和密碼系統(tǒng)，允許基于主機的驗證。連接到服務(wù)器時，所有的密碼傳輸均采用加密形式，從而保證了密碼安全。并且由于MySQL是網(wǎng)絡(luò)化的，因此可以在因特網(wǎng)上的任何地方訪問，提供數(shù)據(jù)共享的效率。

存儲引擎

• 存儲引擎決定數(shù)據(jù)庫對象表的類型，即如何如何存儲、索引數(shù)據(jù)、是否支持事務(wù)，也決定了表在計算機中的存儲方式。
• 數(shù)據(jù)類型，決定數(shù)據(jù)庫對象表可以存儲數(shù)據(jù)的類型。
• 存儲引擎類別(9)
  • InnoDB（默認(rèn)）

    支持事務(wù)，支持具有提交、回滾、崩潰恢復(fù)能力的事務(wù)安裝，比MyISAM占磁盤空間大。

  • MyISAM

    不支持事務(wù)，也不支持外檢，訪問速度較快

  • MEMORY

    使用內(nèi)存來存儲數(shù)據(jù)，訪問速度快，但安全上無保障

  • FEDERATED
  • MRG_MYISAM：
  • BLACKHOLE
  • CSV
  • ARCHIVE
  • PEFORMANCE_SCHEMA

• 相關(guān)命令

  • 查看DBNS版本

    1	SELECT version();

  • 查詢默認(rèn)存儲引擎

    1	SHOW　VARIABLE LIKE 'storage _engine%';

• 修改默認(rèn)存儲引擎通過修改my.ini文件中的"default_storage-engine"參數(shù)

數(shù)據(jù)類型(每個字節(jié)8為)

• 數(shù)值
  • 整型
    • TINYINT（1字節(jié)）
    • SMALLINT（2字節(jié)）
    • MEDIUMINT（3字節(jié)）
    • INT/INTEGER（4字節(jié)）
    • BIGINT（8字節(jié)）
  • 浮點型
    • FLOAT（4字節(jié)）
    • DOUBLE（8字節(jié)）
  • 定點型：DEC/DECIMAL(M/D)

    其中M是小數(shù)點左右數(shù)字之和，D是右邊數(shù)字個數(shù)，M<=38

• 字符串型

  • CHAR（0~255字節(jié)） ：固定字長
  • VARCHAR（0~65535字節(jié)）：可變長度

• 日期時間類型

  • YEAR：年份，1字節(jié)
  • TIME：時分秒，3個字節(jié)
  • TIMESTAMP：經(jīng)常插入或者更新日期為當(dāng)前系統(tǒng)時間，4字節(jié)
  • DATATIME：年月日時分秒，8字節(jié)
  • DATE：年月日，4字節(jié)

查看表結(jié)構(gòu)

• 表的操作

  1 2	DESCRIBE table_name //查看定義 SHOW CREATE TABLE table_name; //查看表詳細定義

• 操作表的約束

  • 通過表的約束來完成對表中所存儲的數(shù)據(jù)是否合法、是否具有完整性，進行檢查。

• 約束

  • 單列約束：每個約束只約束一列數(shù)據(jù)。
  • 多列約束：每個約束約束多列數(shù)據(jù)。

• 完整性約束

  • NOT NULL ：非空
  • DEFLAULT ：設(shè)置字段的默認(rèn)值
  • UNIQUE KEY：設(shè)置字段的值是唯一
  • PRIMARY KEY（PK）：約束字段為表的主鍵，只作為表的唯一標(biāo)識
  • AUTO_INCREMENT ：約束字段自動增加
  • FOREIGN KEY（FK）：約束字段為表的外鍵

• 注意

  • 多字段主鍵：主鍵有多個字段組合而成。
  • 字段自動增加（AUTO_INCREMENT）：MySQL唯一擴展的完整性約束，當(dāng)為數(shù)據(jù)庫表中插入新記錄時，字段上的值會自動生成唯一的ID。一個數(shù)據(jù)表中只能有一個字段使用該約束，且字段數(shù)據(jù)類型一定是整型，由于其唯一的ID，該字段經(jīng)常會設(shè)置為PK主鍵。
  • 設(shè)置外鍵約束（FK）：外鍵約束保證多個表（通常兩個）之間參照完整性，即構(gòu)建兩個字段之間的參照關(guān)系。設(shè)置外鍵的兩個表之間具有父子關(guān)系，即子表中某個字段的取值范圍由父表所決定。如全年級的成績表，以學(xué)號作為總表的主鍵，而班級作為另一個表的主鍵，則班級為總表的外鍵，也就是總表的子表。

索引的操作

• 表：包含數(shù)據(jù)庫的所有數(shù)據(jù)的數(shù)據(jù)庫對象，數(shù)據(jù)庫對象表是存儲和操作數(shù)據(jù)的邏輯結(jié)構(gòu)。
• 索引：一種有效組合數(shù)據(jù)的方式。通過索引對象，可以快速查詢到數(shù)據(jù)庫對象表中的特殊記錄，是一種提高性能的常用方式，一個索引會包含表中安排一定順序排序的一列或多列字段。
• 根據(jù)索引的存儲類型，可分為：
  • B型樹索引（BTREE）<- InnoDB支持
  • 哈希索引（HASH）<- MEMORY支持

• 索引作用

  • 提高查找數(shù)據(jù)的速度
  • 實現(xiàn)數(shù)據(jù)庫對象表的完整性

• MySQL支持6種索引

  • 普通索引： 在創(chuàng)建索引時，不附加任何限制條件（如唯一、非空等），可創(chuàng)立在任何數(shù)據(jù)類型的字段上。
  • 唯一索引：在創(chuàng)建索引時，限制索引的值必須是唯一的，可更快查詢某記錄。
  • 全文索引
    • 主要是關(guān)聯(lián)在數(shù)據(jù)類型為CHAR、VARCHAR、TEXT的字段上，以便能夠更加快速地查詢數(shù)據(jù)量較大的字符串類型字段。
    • 全文索引只能在存儲引擎為MYISAM的數(shù)據(jù)表上創(chuàng)建。
  • 單列索引
  • 多列索引
  • 空間索引

• 適用于創(chuàng)建索引的情況

  • 經(jīng)常查詢的字段，即在where子句中出現(xiàn)的字段。
  • 在分組的字段，即group By子句中出現(xiàn)的字段。
  • 存在依賴關(guān)系的子表和父表之間的聯(lián)合查詢，即關(guān)鍵/外鍵字段。
  • 設(shè)置唯一完整性約束的字段。

• 不適用于創(chuàng)建索引的情況

  • 在查詢中很少被使用的字段
  • 擁有許多重復(fù)值的字段

注意，過多的創(chuàng)建索引則會占據(jù)許多磁盤空間。

視圖的操作

• 視圖：通過視圖的操作不僅可以實現(xiàn)查詢的簡化，而且可以提高安全性。所謂的視圖，本質(zhì)上是一種虛擬表，其內(nèi)容與真實表相似包含一系列帶有名稱的列和行數(shù)據(jù)，但是視圖并不在數(shù)據(jù)庫中以存儲的數(shù)據(jù)值形式存在。行和列數(shù)據(jù)來自定義視圖的查詢所引用基本表，并且在具體引用視圖時動態(tài)生成。

• 視圖的特點

  • 視圖的列可以來自不同的表，是表的抽象和在邏輯意義上建立的新關(guān)系。
  • 視圖時由基本表（實表）產(chǎn)生的虛表。
  • 視圖的建立和刪除不影響基本表。
  • 對視圖內(nèi)容的更新（添加、刪除和修改）直接影響基本表。（相當(dāng)于對基本表操作的工具）
  • 當(dāng)視圖來自多個基本表時，不允許添加和刪除數(shù)據(jù)。

• 創(chuàng)建視圖語法

  1	CREATE VIWE 'database_name'.'VIWE_name' AS (SELECT * FROM ...);

  database_name為當(dāng)前的數(shù)據(jù)庫名稱，VIWE_name為新建視圖名稱，“*”處填寫要查詢的列，用“,”隔開，“…”處寫表名稱。

• 由于視圖的功能實際上是封裝查詢語句，分為以下幾類

  • (1）封裝實現(xiàn)查詢常量語句的視圖，即所謂的常量視圖

    1	CREATE VIWE view_test1(新建視圖的名稱) AS SELECT 3.1415926;

    可將上面的語句輸入到MySQL5.7 command line client 中。

  • (2)封裝使用聚合函數(shù)(SUM、MIN、MAX、COUNT等)，查詢語句的視圖。

    1	CREATE VIWE view_test2 AS SELECT COUNT(col_name) FROM table_name;

  • (3)封裝了實現(xiàn)排列功能（ORDER BY）查詢語句的視圖

    1	CREATE VIWE view_test3 AS SELECT col_name FROM table_name ORDER BY id DESC; //排列方式

  • (4)封裝了實現(xiàn)表內(nèi)連接查詢語句的視圖

    1	CREATE VIWE view_test4 AS SELECT col_name FROM table_name as s.t_group as g WHERE s.group_id=g.id AND g.id=2;

  • (5)封裝了實現(xiàn)表外連接（LEFT JOIN和RIGHT JION）查詢語句的視圖

    1	CREATE VIWE view_test5 AS SELECT col_name FROM table_name as s LEFT/RIGHT/ALL JION t_group as ON s.group=g.id WHERE g.id=2;

  • (6)封裝實現(xiàn)子查詢相關(guān)查詢語句的視圖

    1	CREATE VIWE view_test6 AS SELECT col_name FROM table_name AS s WHERE s.group_id=g,id IN (SELECT id FROM t_group);

  • (7)封裝了實現(xiàn)記錄聯(lián)合（UNION和UNION ALL）查詢語句的視圖

    1	CREATE VIWE view_test7 AS SELECT id,name FROM table_name UNION ALL SELECT id,name FROM　t_group;

• 查看視圖

  1 2	USE database_name; SHOW TABLES;

• 查看視圖詳細信息

  1 2	SHOW TABLE STATUS [FROM db_name]|[LIKE 'pattern'] SHOW　TABLE STATUS FROM db_name \G

• 查看視圖定義信息

  1	SHOW CREATE VIWE view_name;

• 查看視圖設(shè)計信息

  1	DESCRIBE/DESC view_name;

• 通過系統(tǒng)表查看視圖信息

  1 2	USE infoemation_schema; SELECT * FROM views WHERE table_name='view_selectproduct' \G

  SQLyog軟件中直接“工具”—>“信息”

觸發(fā)器的操作

• 觸發(fā)器用來實現(xiàn)由一些表事件觸發(fā)的某種操作，是與數(shù)據(jù)庫對象表關(guān)聯(lián)最緊密的數(shù)據(jù)庫對象之一。在DBS中，當(dāng)執(zhí)事件間時，則會激活觸發(fā)器，從而執(zhí)行其包含的操作。觸發(fā)器類似于編程語言中的函數(shù)。總的來說就是表發(fā)生更改時，自動進行一些處理。

• MySQL的觸發(fā)器語句（事件引導(dǎo)）：

  • DELETE語句：刪除
  • INSERT語句：添加
  • UPDATE語句：修改

    其他SQL語句不會激發(fā)觸發(fā)器，會經(jīng)常使用觸發(fā)器數(shù)據(jù)庫對象，是由于該對象能夠加強數(shù)據(jù)庫表中數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。

• 創(chuàng)建觸發(fā)器

  1 2 3 4 5 6 7 8 9 10 11 12

  DELIMITER $ //固有格式，當(dāng)有多個觸發(fā)器語句時使用 CREATE TRIGGER t_afterinsert_on_doorlog AFTER INSERT ON t_doorlog FOR EACH ROW BEGIN DECLARE jobno VARCHAR(30); DECLARE time1 VARCHAR(20); SET jobno = (SELECT job_num FROM tb_park_user_base WHERE card_num = new.cardNo); SET time1 = DATE_FORMAT(new.operateTime,'%Y%m%d%H%i%s'); INSERT INTO t_attendance (job_no, operateTime) VALUES (jobno,time1); END$

• DELIMITER $ 意思是告訴mysql下文執(zhí)行語句以“$”結(jié)束，如果沒有這句，下面語句會出錯，mysql無法判斷哪句結(jié)束。

• CREATE TRIGGER t_afterinsert_on_doorlog AFTER INSERT ON t_doorlog FOR EACH ROW 創(chuàng)建觸發(fā)器的語法 其中AFTER同級關(guān)鍵字有BEFORE，而 INSERT同級關(guān)鍵字還有DELETE，UPDATE

• BEGIN和END之間的是觸發(fā)器執(zhí)行語句。DECLARE jobno VARCHAR(30) 意思是聲明觸發(fā)器SQL中的局部變量，SET jobno=….　　意思是給局部變量賦值，=右邊可為查詢語句或函數(shù)方法

單表數(shù)據(jù)記錄查詢

• 四則運算：+(加)、-(減)、*(乘)、/(DIV除)、%(取余)。->字段間運算

  1	SELECT ename(字段) sal*12 FROM table_name; //查詢年薪

• 對運算結(jié)果/某字段修改字段名，使顯示更直觀

  1 2	SELECT ename(字段),sal*12 yearsalary FROM table_name; SELECT ename(字段),sal*12 AS yearsalary FROM table_name;

• 設(shè)置數(shù)據(jù)顯示格式，以便用戶瀏覽所查詢到的數(shù)據(jù). 在MySQL軟件中提供函數(shù)CONCAT()來連接字符串，從而實現(xiàn)設(shè)置顯示數(shù)據(jù)的格式，語句如下：

  1	SELECT CONCAT|ename ,'雇員的年薪為：', sal*12) yearsalary FROM table_name;

結(jié)果：SMITH雇員的年薪為：9600.00

多表數(shù)據(jù)記錄查詢

• 多表數(shù)據(jù)記錄查詢

  • 內(nèi)連接查詢：笛卡爾積內(nèi)連接表后查詢

  • 外連接查詢：笛卡爾積外連接表后查詢

  • 子查詢

    進行連接查詢中，首先要對兩張或兩張以上表進行連接操作。多表數(shù)據(jù)記錄查詢是關(guān)系數(shù)據(jù)操作中專門用于數(shù)據(jù)庫操作的關(guān)系運算。有并、笛卡爾積、連接（UNION）。

• 查詢語法

實現(xiàn)特殊功能比較運算符

• 正則表達式匹配

  • ^：匹配字符串開始部分，如REGEXP '^c'

  • $:匹配字符串結(jié)束部分，如REGEXP 'g$'

  • .：匹配字符集合中任意一個字符，如REGEXP '^c···g$'

  • [字符集合]：匹配字符集合中任意一個字符，如REGEXP '[abc]'

  • [^字符集合]：匹配字符集合外任意一個字符，如REGEXP '^[abc]'

  • str1|str2|str3：匹配str1、str2、str3中任意一個字符

  • *：匹配字符，包括0個或1個，如REGEXP 'a*g'

  • +：匹配字符，包括1個，如REGEXP 'a+g'

  • 字符串{N}：字符串出現(xiàn)N次

  • 字符串{M,N}：字符串出現(xiàn)至少M次，至多N次

• 位運算符

  • &：按位與

  • |：按位或

  • ~：按位取反

  • ^：按位異或

  • <<：按位左移

  • >>：按位右移

  • 例子：

    1 2	SELECT 5&6, BIN (5&6) 二進制； SELECT 4&5&6, BIN (4&5&6) 二進制；

結(jié)果：

MySQL常用函數(shù)

存儲過程和函數(shù)的操作

MySQL事務(wù)

MySQL安全性機制

總結(jié)

以上是生活随笔為你收集整理的数据库设计笔记——MySQL基础知识（四）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。