MySQL

• 概念

  • MySQL的理論知識

    • 概念

      • 數據庫就是用來存儲和管理數據的倉庫！

    • 數據庫分類

      • 層次型數據庫

        • 樹型結構，一個子記錄可以有一個父記錄，一個父記錄可以有多個子記錄，類似一個二叉樹，但是一個父節點可以不止兩個子節點，可以有多個子節點

      • 網狀型數據庫

        • 以網裝結構的方式，一個節點可以有多個父節點或子節點

      • 關系型數據庫

        • 舉例mysql，數據就是以表格方式存儲，表與表之間通過外鍵維護關系。

    • MySQL語句分類

      • DDL

        • 數據定義語言，用來定義數據庫對象：庫、表、列等；

      • DML

        • 數據操作語言，用來定義數據庫記錄（數據）；

      • DCL

        • 數據控制語言，用來定義訪問權限和安全級別

      • DQL

        • 數據查詢語言，用來查詢記錄

    • 字符集編碼分類 （指數據的存儲格式）

      • utf8mb4_bin

        • 將字符串每個字符??進制數據編譯存儲，區分??寫，?且可以存?進制的內容。推薦使用

      • utf8mb4_general_ci：ci

        • 不區分??寫。沒有實現Unicode排序規則，在遇到某些特殊語?或者字符集，排序結果可能不?致。但是，在絕?多數情況下，這些特殊字符的順序并不需要那么精確。

      • utf8mb4_unicode_ci

        • 是基于標準的Unicode來排序和?較，能夠在各種語?之間精確排序，Unicode排序規則為了能夠處理特殊字符的情況，實現了略微復雜的排序算法。

      • utf8mb4_general_ci

        • 是?個遺留的 校對規則，不?持擴展，它僅能夠在字符之間進?逐個?較。

      • utf8_general_ci

        • 校對規則進?的?較速度很快，但是與使用 utf8mb4_unicode_ci的校對規則相比，比較正確性較差。

      • -修改數據庫編碼：

        • ALTER DATABASE mydb1 CHARACTER SET utf8;

      • 查看排序編碼

        • show variables like 'collation_%';

      • 查看字符集

        • show variables like 'character_set_%';

    • 數據類型分類

      • 文本類型（對應java的字符集）

        • CHAR,VARCHAR,TINYBOB，TEXT等

      • 數值類型（對應正數，小數）

        • INT DOUBLE DECIMAL等

      • 日期類型（對應Data類型

        • DATE,TIME,YEAR,DATETIMEDATETIME,TIMESTAMP等

      • 其他類型（二進制文件類型）

    • 數據庫命名規范

      • 在表的命名的過程中，最好在表的前面加個前綴tb，例如tb_user。

      • 表名由多個單詞構成時：a_b create_time

• sql語句的學習

  • DDL語句

    • 對數據庫的操作

      • 切換數據庫：USE mydb1，切換到mydb1數據庫；

      • 查看當前使用的數據庫的名稱: select database()；

      • 查看所有數據庫名稱：SHOW DATABASES

      • CREATE DATABASE [IF NOT EXISTS] 數據庫名稱；

      • 刪除數據庫：DROP DATABASE 數據庫名稱；

      • --修改數據庫編碼：ALTER DATABASE 數據庫名稱 CHARACTER SET 編碼格式

    • 對表的操作

      • 創建表： CREATE TABLE 表名( 列名 列類型, 列名 列類型, ...... );

      • --查看當前數據庫中所有表名稱： SHOW TABLES;

      • -查看指定表的創建語句： 查看emp表的創建語句； SHOW CREATE TABLE emp;

      • --查看表結構： DESC emp; --查看emp表結構；

      • --刪除表： DROP TABLE emp; --刪除emp表；

      • 修改表： --給表添加列：給stu表添加classname列： --1ALTER TABLE stu ADD (classname varchar(100));

      • --2. 修改之修改列類型：修改stu表的gender列類型為CHAR(2)： ALTER TABLE stu MODIFY gender CHAR(2);

      • --3. 修改之修改列名：修改stu表的gender列名為sex： ALTER TABLE stu change gender sex CHAR(2);

      • --4. 修改之刪除列：刪除stu表的classname列： ALTER TABLE stu DROP classname;

      • 5. 修改之修改表名稱：修改stu表名稱為student： ALTER TABLE stu RENAME TO student;

  • DML

    • 插入語句

      • INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

    • 修改語句

      • UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 條件]

    • 刪除數據

      • DELETE FROM 表名 [WHERE 條件]

      • TRUNCATE TABLE 表名 TRUNCATE TABLE stu;

      • 區別：TRUNCATE其實屬性DDL語句，因為它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE刪除的記錄是無法回滾的，但DELETE刪除的記錄是可以回滾的（回滾是事務的知識！）。

  • DCL

    • 查詢所有用戶

      • Select * from mysql.user

    • 創建用戶

      • CREATE USER 用戶名@地址 IDENTIFIED BY '密碼'; --user1用戶只能在localhost這個IP登錄mysql服務器

      • --user2用戶可以在任何電腦上登錄mysql服務器 CREATE USER user2@'%' IDENTIFIED BY '123';

    • 用戶權限操作

      • 給用戶授權

        • GRANT 權限1, … , 權限n ON 數據庫.* TO 用戶名;

      • 撤銷授權

        • REVOKE權限1, … , 權限n ON 數據庫.* FORM 用戶名;

      • 查看用戶權限

        • SHOW GRANTS FOR 用戶名; SHOW GRANTS FOR user1@localhost;

      • 刪除用戶

        • DROP USER 用戶名; DROP USER user1@localhost;

      • 修改用戶密碼

        • USE mysql; UPDATE USER SET authentication_string=PASSWORD('密碼') WHERE User='用戶名' and Host='IP'; FLUSH PRIVILEGES；例如UPDATE mysql.USER SET authentication_string=PASSWORD('123456') WHERE USER='zhangsan' AND HOST='localhost'; FLUSH PRIVILEGES;

  • DQL語句

    • DQL單表查詢

      • 單表查詢的順序

        • Select *(所有列)，列名1，列名2... from 表名 【where 字句】 條件查詢 【group by 列名】 #分組 【having 字句】 分組條件篩選 【order by 列名 排序方式】 #排序 【limit [x,y]】 #分頁順序

      • 分析的步驟

        • 1.找表 指定的 根據需要列找出對應的表

        • 2.找查詢條件

        • 3.找查詢的列

      • 聚合函數

        • COUNT() 統計指定列不為NULL的記錄行數

        • MAX() 計算指定列的最大值，如果指定列是字符串類型，那么使用字符串排序運算

        • MIN() 計算指定列的最小值，如果指定列是字符串類型，那么使用字符串排序運算

        • SUM() 計算指定列的數值和，如果指定列類型不是數值類型，那么計算結果為0

        • AVG() 計算指定列的平均值，如果指定列類型不是數值類型，那么計算結果為0

        • ifnull（字段,數據） 如果一個數據為空，則放回指定的數據

      • 注意：

        • where里面不能出現聚合函數

        • 在查詢的過程中null是一個特殊的值，任何與null運算都是null，任何與null比較都是false。

    • DQL多表查詢

      • 表與表的關系

        • 一對一

          • 通過主外鍵連結

        • 多對多

          • 使用中間表存儲兩張表的關系，命名規則：tb_A表名_B表名

        • 一對多

          • 在多方面的表創建外鍵，唯一表創建主鍵

      • 連結方式

        • 聯合查詢

          • 合并結果集就是把兩個select語句的查詢結果合并到一起！

            • 語句：UNION：去除重復記錄SELECT * FROM t1 UNION SELECT * FROM t2； UNION ALL：不去除重復記錄，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

        • 連結查詢

          • 連接查詢就是求出多個表的乘積，例如t1連接t2，那么查詢出的結果就是t1*t2。

            • 語句：SELECT * FROM t1,t2

        • 內連接

          • 一個表跟另外一個表通過join on連結

            • 語句：列如：SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

        • 外連結

          • 左連接

            • join on左邊的表顯示所有的內容，不管跟它連接的表它是否為null

              • 語句：SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件

          • 右連接

            • join on右邊的表顯示所有的內容，不管跟它連接的表它是否為null

              • 語句：SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ... ;

        • 自連接

          • 自己跟自己連接

            • 例子：select a.name , b.name from emp a , emp b where a.managerid = b.id;

        • 子查詢

          • 就是嵌套查詢

            • 例子：SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

  • 完整性約束

    • 主鍵約束

      • 唯一且不為空，每一張表只能有一個主鍵約束

        • 關鍵字：PRIMARY KEY

          • 注意：每張表都應該有一個主鍵，主鍵在非業務數據上不允許修改

    • 唯一約束

      • 每一張表都可以有多個唯一約束

        • 關鍵字：UNIQUE

    • 非空約束

      • 限制數據不為null

        • 關鍵字：NOT NULL

    • 外鍵約束

      • 用來讓兩張表的數據之間建立連接，保證數據的一致性和完整性

        • FOREIGN KEY

    • 檢查約束

      • 保證字段值滿足某一個條件

        • 關鍵字：CHECK

    • 默認約束

      • 保存數據時，如果未指定該字段的值，則采用默認值

        • 關鍵字：UNIQUE

• 系統函數

  • 數值函數

    • ABS

      • 求絕對值

    • SQRT

      • 求二次方根

    • MOD

      • 求余數

    • RAND

      • 生成一個0~1之間的隨機數，傳入整數參數是，用來產生重復序列

  • 字符串函數

    • LENGTH

      • 計算字符串長度函數，返回字符串的字節長度

    • CONCAT

      • 合并字符串函數，返回結果連結字符參數

    • INSERT

      • 替換字符串函數

    • LOWER

      • 將字符串中的字母轉換為小寫

    • UPPER

      • 將字符串中的字母轉換為大寫

  • 日期函數

    • WEEK

      • 獲取指定日期是一年中的第幾周

    • YEAR

      • 獲取年份，返回值范圍是 1970?2069

    • LAST_DAY

      • 獲取指定日期這個月的最后一天的日期

• 視圖

  • 概念：視圖只保存了查詢的SQL邏輯，不保存查詢結果可以簡化用戶對數據的理解，也可以簡化他們的操作。那些被經常使用的查詢可以被定義為視圖

  • 基本操作

    • 創建視圖

      • 語法：CREATE OR REPLACE VIEW emp_v_1 AS SELECT * FROM emp WHERE sal <= 2000;

    • 查詢

      • 語法：#查看創建視圖語句： SHOW CREATE VIEW 視圖名稱; #查看視圖數據： SELECT * FROM 視圖名稱 ...... ;

    • 修改

      • CREATE [OR REPLACE] VIEW 視圖名稱[(列名列表)] AS SELECT語句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

    • 刪除

      • DROP VIEW [IF EXISTS] 視圖名稱 [,視圖名稱] ...

• 索引

  • 概念：幫助提升查詢效率的數據結構，還維護著滿足 特定查找算法

  • 優勢及劣勢

    • 優勢

      • 提高數據檢索的效率，降低數據庫的IO成本，通過索引列對數據進行排序，降低數據排序的成本，降低CPU的消耗。

    • 劣勢

      • 降低了更新，插入的效率，索引列也是要占用空間的

  • 索引結構

    • B+Tree索引

      • 最常見的索引類型，大部分引擎都支持 B+ 樹索引（MySQL也是）

    • Hash索引

      • 底層數據結構是用哈希表實現的, 只有精確匹配索引列的查詢才有效, 不支持范圍查詢

    • R-tree(空間索引）

      • 空間索引是MyISAM引擎的一個特殊索引類型，主要用于地理空間數據類型，通常使用較少

    • Full-text(全文索引)

      • 是一種通過建立倒排索引,快速匹配文檔的方式。類似于Lucene,Solr,ES

  • 索引分類

    • 主鍵索引

      • 就是表中主鍵創造的索引。語法：PRIMARY

    • 唯一索引

      • 避免同一個表中某數據列中的值重復。語法：UNIQUE

    • 快速定位特定數據

      • 可以有多個

    • 全文索引

      • 全文索引查找的是文本中的關鍵詞，而不是比較索引中的值

  • 語句

    • 創建索引

      • CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;

    • 查看索引

      • SHOW INDEX FROM table_name ;

    • 刪除索引

      • DROP INDEX index_name ON table_name ;