數據庫基礎
1.什么是數據庫？
存儲數據用文件就可以了，為什么還要弄個數據庫？
文件保存數據有以下幾個缺點：
- 文件的安全性問題
- 文件不利于數據查詢和管理
- 文件不利于存儲海量的數據
- 文件在程序中控制不方便
數據庫存儲介質：
- 磁盤
- 內存
為了解決上述問題，專家們設計出更加利于管理數據的東西 —— 數據庫 ，它能更有效的管理數據。
2.主流數據庫
- SQL Sever： 微軟的產品，.Net程序員的最愛，中大型項目。
- Oracle： 甲骨文產品，適合大型項目，復雜的業務邏輯，并發一般來說不如MySQL。
- MySQL：世界上最受歡迎的數據庫，屬于甲骨文，并發性好，不適合做復雜的業務。主要用在電商，SNS，論壇。對簡單的SQL處理效果好。
- PostgreSQL :加州大學伯克利分校計算機系開發的關系型數據庫，不管是私用，商用，還是學術研究使用，可以免費使用，修改和分發。
- SQLite： 是一款輕型的數據庫，是遵守ACID的關系型數據庫管理系統，它包含在一個相對小的C庫中。它的設計目標是嵌入式的，而且目前已經在很多嵌入式產品中使用了它，它占用資源非常的低，在嵌入式設備中，可能只需要幾百K的內存就夠了。
- H2：是一個用Java開發的嵌入式數據庫，它本身只是一個類庫，可以直接嵌入到應用項目中。
3.基本使用
連接
mysql -h 127.0.0.1 -P 3306 -u root -p- 如果沒有寫-h? 127.0.0.1默認是連接本地
- 如果沒有寫-P 3306默認是連接3306號端口
服務器，數據庫，表的關系
創建數據庫
create database school;使用數據庫
use school;創建數據庫表
create table student(id int,name varchar(32),gender varchar(2)
);表中插入數據
insert into student (id, name, gender) values (1, '張三', '男');
insert into student (id, name, gender) values (2, '李四', '女');
insert into student (id, name, gender) values (3, '王五', '男');查詢表中的數據
select * from student;4.MySQL架構
MySQL 是一個可移植的數據庫，幾乎能在當前所有的操作系統上運行，如 Unix/Linux 、 Windows
、Mac 和 Solaris 。各種系統在底層實現方面各有不同，但是 MySQL 基本上能保證在各個平臺上的物理體 系結構的一致性。
5.SQL分類
- DDL：數據定義語言，用來維護存儲數據的結構，代表指令: create, drop, alter
- DML：數據操縱語言，用來對 數據進行操作，代表指令： insert ， delete ， update
- DCL： 數據控制語言，主要負責權限管理和事務，代表指令： grant ， revoke ， commit
6.存儲引擎
存儲引擎是：數據庫管理系統如何存儲數據、如何為存儲的數據建立索引和如何更新、查詢數據等技術 的實現方法。
MySQL 的核心就是插件式存儲引擎，支持多種存儲引擎。
數據庫的操作
1.創建數據庫
語法：
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,
create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name- 大寫的是關鍵字
- []表示可選
- CHARACTER SET：指定字符集
- COLLATE：指定校驗規則
舉例：
- 創建一個db1的數據庫
create database db1;- 創建一個使用utf8字符集的db2數據庫
create database db2 charset=utf8;- 創建一個使用utf字符集，并攜帶校對規則的數據庫
create database db3 charset=utf8 collate utf8_general_ci;2.字符集和校驗規則
查看一個默認的字符集和校驗規則
字符集主要是控制用什么語言。比如 utf8 就可以使用中文。
校驗規則主要控制如何比較和排序這些字符。比如用utf8_ general_ ci不區分大小寫，用utf8_ bin區分大小寫。
3.操作數據庫
查看數據庫
show databases;顯示創建語句
show create database 數據庫名；修改數據庫
語法：
ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...]
alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name- 對數據庫的修改主要是修改數據庫的字符集，校驗規則
比如：
alter database mytest charset=gbk;數據庫刪除
DROP DATABASE [IF EXISTS] db_ name刪除之后
- 數據庫內部看不到對應的數據庫
- 對應的數據庫文件夾被刪除，級聯刪除，里面的數據表被刪
備份
語法：
# mysqldump -P3306 -u root -p 密碼 -B 數據庫名 > 數據庫備份存儲的文件路徑比如：
mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql還原：
source D:/mysql-5.7.22/mytest.sql;注意：
- 可以只備份一張表
mysqldump -u root -p 數據庫名 表名1 表名2 > D:/mytest.sql- 可以同時備份多個數據庫
mysqldump -u root -p -B 數據庫名1 數據庫名2 ... > 數據庫存放路徑表的操作
創建表
CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
) character set 字符集 collate 校驗規則 engine 存儲引擎;- field 表示列名
- datatype 表示列的類型
- character set 字符集，如果沒有指定字符集，則以所在數據庫的字符集為準
- collate 校驗規則，如果沒有指定校驗規則，則以所在數據庫的校驗規則為準
不同的存儲引擎，創建的表的文件不一樣。
查看表的結構
desc 表名;修改表
在項目實際開發中，經常修改某個表的結構，比如字段名字，字段大小，字段類型，表的字符集類型， 表的存儲引擎等等。我們還有需求，添加字段，刪除字段等等。這時我們就需要修改表。
語法
ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column
datatype]...);
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column
datatype]...);
ALTER TABLE tablename DROP (column);刪除表
語法
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...數據類型
表的約束
表的約束又很多，主要是null/not null,default, comment, zerofill，primary key，auto_increment，unique key
空屬性
- 兩個值： null （默認的）和 not null( 不為空 )
- 數據庫默認字段基本都是字段為空，但是實際開發時，盡可能保證字段不為空，因為數據為空沒辦法參與運算。
默認值
默認值：某一種數據會經常性的出現某個具體的值，可以在一開始就指定好，在需要真實數據的時候， 用戶可以選擇性的使用默認值。
數據在插入的時候不給這個字段賦值，就是用默認值
列描述
列描述： comment ，沒有實際含義，專門用來描述字段，會根據表創建語句保存，用來給程序員或 DBA 來進行了解。
不能通過desc查看，但是可以通過show
比如這個表的名字叫做User
desc User;show create table User\G;zerofill
剛開始學習數據庫時，很多人對 數字類型 后面的長度很迷茫。通過 show 看看 tt3 表的建表語句：
mysql> show create table tt3\G
***************** 1. row *****************
Table: tt3
Create Table: CREATE TABLE `tt3` (
`a` int(10) unsigned DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set(0.00 sec) 可以看到 int(10), 這個代表什么意思呢？整型不是 4 字節碼？這個 10 又代表什么呢？其實沒有 zerofill 這個屬性，括號內的數字是毫無意義的。a 和 b 列就是前面插入的數據，如下：
mysql> insert into tt3 values(1,2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt3;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+ 但是對列添加了 zerofill 屬性后，顯示的結果就有所不同了。修改 tt3 表的屬性：
mysql> alter table tt3 change a a int(5) unsigned zerofill;
mysql> show create table tt3\G
*************************** 1. row ***************************
Table: tt3
Create Table: CREATE TABLE `tt3` (
`a` int(5) unsigned zerofill DEFAULT NULL, --具有了zerofill
`b` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)mysql> select * from tt3;
+-------+------+
| a | b |
+-------+------+
| 00001 | 2 |
+-------+------+ zerofill：寬度小于設定的寬度，會自動填充0。
這里數據庫內部存儲的還是 1,00001， 只是設置了 zerofill 屬性后的一種格式化輸出而已。
主鍵
主鍵： primary key 用來唯一的約束該字段里面的數據，不能重復，不能為空，一張表中最多只能有一個主鍵；
主鍵所在的列通常是整數類型。
- 主鍵約束：逐漸對應的字段不能重復，一旦重復，操作失敗
- 當表創建好以后但是沒有主鍵的時候，可以再次追加主鍵
alter table 表名 add primary key(字段列表)- 刪除主鍵
alter table 表名 drop primary key;- 復合主鍵
在創建表的時候，在所有字段之后，可以使用primary key來創建主鍵，如果有多個字段作為主鍵，可以使用復合主鍵。
mysql> create table tt14(
-> id int unsigned,
-> course char(10) comment '課程代碼',
-> score tinyint unsigned default 60 comment '成績',
-> primary key(id, course) -- id和course為復合主鍵
-> );
Query OK, 0 rows affected (0.01 sec)mysql> desc tt14;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | 0 | | <= 這兩列合成主鍵
| course | char(10) | NO | PRI | | |
| score | tinyint(3) unsigned | YES | | 60 | |
+--------+---------------------+------+-----+---------+-------+
自增長
auto_increment ：當對應的字段，不給值，會自動的被系統觸發，系統會從當前字段中已經有的最大值+1操作，得到一個新的不同的值。通常和主鍵搭配使用，作為邏輯主鍵。
自增長的特點 :
- 任何一個字段要做自增長，前提是本身是一個索引
- 自增長必須是整數
- 一張表最多只能有一個自增長
索引：
在關系數據庫中， 索引是一種單獨的、物理的對數據庫表中一列或多列的值進行排序的一種存儲結 構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。 索引的作用相當于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。
索引提供 指向存儲在表的指定列中的數據值的指針，然后根據您指定的排序順序對這些指針排序。 數據庫使用索引以找到特定值，然后順指針找到包含該值的行。這樣可以使對應于表的 SQL 語句執行得 更快，可快速訪問數據庫表中的特定信息。
唯一鍵
一張表中有往往有很多字段需要唯一性，數據不能重復，但是一張表中只能有一個主鍵：唯一鍵就可以
解決表中有多個字段需要唯一性約束的問題。
唯一鍵的本質和主鍵差不多，唯一鍵允許為空，而且可以多個為空，空字段不做唯一性比較。
外鍵
外鍵用于定義主表和從表之間的關系：外鍵約束主要定義在從表上，主表則必須是有主鍵約束或 unique 約束。當定義外鍵后，要求外鍵列數據必須在主表的主鍵列存在或為null 。
語法
foreign key (字段名) references 主表(列)個人理解，從表中和主表相關聯的字段，需填入主表中出現過的值或空值。
表的增刪改查
1.CREATE
INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...可能由于主鍵或者唯一鍵對應的值已經存在而導致插入失敗。可以這樣設置--如果表中有沖突數據的話，就改為更新操作。
INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...替換
-- 主鍵 或者 唯一鍵 沒有沖突，則直接插入；
-- 主鍵 或者 唯一鍵 如果沖突，則刪除后再插入
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞞');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中沒有沖突數據，數據被插入
-- 2 row affected: 表中有沖突數據，刪除后重新插入2.Retrieve
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...舉個例子來介紹一下查詢語句
-- 創建表結構
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同學姓名',
chinese float DEFAULT 0.0 COMMENT '語文成績',
math float DEFAULT 0.0 COMMENT '數學成績',
english float DEFAULT 0.0 COMMENT '英語成績'
);
-- 插入測試數據
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孫悟空', 87, 78, 77),
('豬悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('劉玄德', 55, 85, 45),
('孫權', 70, 73, 78),
('宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0 全列查詢
SELECT * FROM exam_result;
+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)指定列查詢
SELECT id, name, english FROM exam_result;
+----+-----------+--------+
| id | name | english |
+----+-----------+--------+
| 1 | 唐三藏 | 56 |
| 2 | 孫悟空 | 77 |
| 3 | 豬悟能 | 90 |
| 4 | 曹孟德 | 67 |
| 5 | 劉玄德 | 45 |
| 6 | 孫權 | 78 |
| 7 | 宋公明 | 30 |
+----+-----------+--------+
7 rows in set (0.00 sec)查詢字段為表達式
SELECT id, name, chinese + math + english FROM exam_result;
+----+-----------+-------------------------+
| id | name | chinese + math + english |
+----+-----------+-------------------------+
| 1 | 唐三藏 | 221 |
| 2 | 孫悟空 | 242 |
| 3 | 豬悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 劉玄德 | 185 |
| 6 | 孫權 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+-------------------------+
7 rows in set (0.00 sec)為查詢結構指定別名
SELECT id, name, chinese + math + english 總分 FROM exam_result;
+----+-----------+--------+
| id | name | 總分 |
+----+-----------+--------+
| 1 | 唐三藏 | 221 |
| 2 | 孫悟空 | 242 |
| 3 | 豬悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 劉玄德 | 185 |
| 6 | 孫權 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+--------+
7 rows in set (0.00 sec)結果去重
SELECT DISTINCT math FROM exam_result;
+--------+
| math |
+--------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+
6 rows in set (0.00 sec)where條件
| 運算符 | 說明 |
| >, >=, <, <= | 大于，大于等于，小于，小于等于 |
| = | 等于， NULL 不安全，例如 NULL = NULL 的結果是 NULL |
| <=> | 等于， NULL 安全，例如 NULL <=> NULL 的結果是 TRUE(1) |
| !=, <> | 不等于 |
| BETWEEN a0 AND a1 | 范圍匹配， [a0, a1] ，如果 a0 <= value <= a1 ，返回 TRUE(1) |
| IN (option, ...) | 如果是 option 中的任意一個，返回 TRUE(1) |
| IS NULL | 是 NULL |
| IS NOT NULL | 不是 NULL |
| LIKE | 模糊匹配。 % 表示任意多個（包括 0 個）任意字符； _ 表示任意一個字符 |
邏輯運算符
| 運算符 | 說明 |
| AND | 多個條件必須都為 TRUE(1) ，結果才是 TRUE(1) |
| OR | 任意一個條件為 TRUE(1), 結果為TRUE(1) |
| NOT | 條件為 TRUE(1) ，結果為 FALSE(0) |
結果排序
-- ASC 為升序（從小到大）
-- DESC 為降序（從大到小）
-- 默認為 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];分頁查詢
-- 起始下標為 0
-- 從 s 開始，篩選 n 條結果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
-- 從 0 開始，篩選 n 條結果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
;
-- 從 s 開始，篩選 n 條結果，比第二種用法更明確，建議使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;3.UPDATE
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]4.DELETE
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]截斷表
TRUNCATE [TABLE] table_name這個操作慎用
- 只能對整表操作，不能像DELETE一樣針對部分數據操作
- 實際上MySQL不對數據操作，所以比DELETE更快，但是TRUNCATE在刪除數據的時候，并不經過真正的事物，所以不能回滾
- 會重置AUTO_INCREMENT項
5.聚合函數
| 函數 | 說明 |
| COUNT([DISTINCT] expr) | 返回查詢到的數據的 數量 |
| SUM([DISTINCT] expr) | 返回查詢到的數據的 總和，不是數字沒有意義 |
| AVG([DISTINCT] expr) | 返回查詢到的數據的 平均值，不是數字沒有意義 |
| MAX([DISTINCT] expr) | 返回查詢到的數據的 最大值，不是數字沒有意義 |
| MIN([DISTINCT] expr) | 返回查詢到的數據的 最小值，不是數字沒有意義 |
6.group by子句的使用
select column1, column2, .. from table group by column;小結：
SQL查詢中各個關鍵字的執行先后順序 from > on> join > where > group by > with > having > select > distinct > order by > limit
函數
日期函數
| 函數名稱 | 描述 |
| current_date() | 當前日期 |
| current_time() | 當前時間 |
| current_timestamp() | 當前時間戳 |
| date(datetime) | 返回datetime參數的日期部分 |
| date_add(date,interval d_value_type) | 在date中添加日期或事件interval后數值單位可以是：year minute second day |
| date_sub(date,interval d_value_type) | 在date中減去日期或事件interval后的數值可以是：year minute second day |
| datediff(date1,date2) | 兩個日期的差，單位是天 |
| now() | 當前日期時間 |
字符串函數
| charset(str) | 返回字符串字符集 |
| concat(string2 [,...]) | 連接字符串 |
| instr(string,substring) | 返回substring在string中出現的位置，沒有返回0 |
| ucase(string2) | 轉換成大寫 |
| lcase(string2) | 轉換成小寫 |
| left(string2,length) | 從string2中的左邊起取length個字符 |
| length(string) | string的長度 |
| replace(str,search_str,replace_str) | 在str中用replace_str替換search_str |
| strcmp(string1,string2) | 逐字符比較兩字符串大小 |
| substring(str,position,[,...length]) | 從str的postion開始，取length個字符 |
| ltrim(string), rtrim(string), trim(string) | 去除前空格或后空格 |
數學函數
| 函數名稱 | 描述 |
| abs(number) | 絕對值函數 |
| bin(decimal_number) | 十進制轉換成二進制 |
| hex(decimalNumber) | 轉換成十六進制 |
| conv(number,from_base,to_base) | 進制轉換 |
| ceiling(number) | 向上取整 |
| floor(number) | 向下取整 |
| format(number,decimal_places) | 格式化，保留小數位數 |
| hex(decimalNumber) | 轉換成十六進制 |
| rand() | 返回隨機附帶念書，范圍[0.0,1.0) |
| mod(number,denominator) | 取模，求余 |
表的內連和外連
內連接
內連接實際上就是利用where子句對兩種表形成的笛卡爾積進行篩選，前面用到的都是內連接，也就是在開發過程中使用最多的連接查詢
select 字段 from 表1 inner join 表2 on 連接條件 and 其他條件；外連接
外連接分為左外連接和右外連接
左外連接
如果聯合查詢，左側的表完全顯示我們就說是左外連接。
舉個栗子
-- 建兩張表
create table stu (id int, name varchar(30)); -- 學生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成績表
insert into exam values(1, 56),(2,76),(11, 8);也就是這樣
mysql> select * from stu;
+------+------+
| id | name |
+------+------+
| 1 | jack |
| 2 | tom |
| 3 | kity |
| 4 | nono |
+------+------+
4 rows in set (0.00 sec)mysql> select * from exam;
+------+-------+
| id | grade |
+------+-------+
| 1 | 56 |
| 2 | 76 |
| 11 | 8 |
+------+-------+
3 rows in set (0.00 sec)使用這條查詢
select * from stu left join exam on stu.id=exam.id;結果是
mysql> select * from stu left join exam on stu.id=exam.id;
+------+------+------+-------+
| id | name | id | grade |
+------+------+------+-------+
| 1 | jack | 1 | 56 |
| 2 | tom | 2 | 76 |
| 3 | kity | NULL | NULL |
| 4 | nono | NULL | NULL |
+------+------+------+-------+
4 rows in set (0.00 sec)
右外連接
如果聯合查詢，右側的表完全顯示就是我們所說的右外連接
select 字段 from 表名1 right join 表名2 on 連接條件；比如
mysql> select * from exam right join stu on exam.id=stu.id;
+------+-------+------+------+
| id | grade | id | name |
+------+-------+------+------+
| 1 | 56 | 1 | jack |
| 2 | 76 | 2 | tom |
| NULL | NULL | 3 | kity |
| NULL | NULL | 4 | nono |
+------+-------+------+------+
4 rows in set (0.00 sec)
索引
索引：提高數據庫的性能，索引是物美價廉的東西了。不用加內存，不用改程序，不用調 sql ，只要執行正確的 create index ，查詢速度就可能提高成百上千倍。但是天下沒有免費的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價的，這些寫操作，增加了大量的IO 。所以它的價值，在于提高一個 海量數據的檢索速度。
常見的索引分為：
- 主鍵索引(primary key)：唯一并且非空，一張表只能有一個
- 唯一索引(unique)：值唯一，可以有多個
- 普通索引(index)：最常用，可以有重復值
- 全文索引(fulltext)：用于文本檢索，MyISAM引擎支持，默認不支持中文
磁盤結構基礎
- 扇區：磁盤最小存儲單位，默認是512字節，部分新磁盤為4K
- 柱面、磁道、磁頭：磁盤物理尋址方式，系統通過LBA線性地址轉換為CHS尋址
- IO效率關鍵：隨機訪問效率低于連續訪問，應減少IO次數
MySQL與磁盤交互
InnoDB引擎：IO基本單位是16KB（page），通過Buffer Poll緩存數據，減少磁盤IO。
Page結構：每個Page存多條記錄，通過page_prev和page_next組成雙向鏈表，內部數據按主鍵排序。
索引的數據結構——B+樹
非葉子結點只存鍵值和指針，葉子節點存數據，樹高更低，IO次數少
葉子結點之間相連，方便范圍查詢
聚簇索引與非聚簇索引
聚簇索引：
- 主鍵索引與數據存儲在一起，葉子結點存放完整數據
- 輔助索引(普通索引)的葉子節點存放主鍵，查詢需要“回表”（先查輔助索引得到主鍵，在查主鍵得到數據）
非聚簇索引：
- 索引和數據分離，葉子節點都存放數據地址
- 輔助索引和主鍵索引結構類型，查詢效率一致
索引的創建方式
主鍵索引：
-- 創建表時指定
CREATE TABLE user1(id INT PRIMARY KEY, name VARCHAR(30));
-- 表定義后添加
ALTER TABLE user3 ADD PRIMARY KEY(id);唯一索引：
CREATE TABLE user4(name VARCHAR(30) UNIQUE);
ALTER TABLE user6 ADD UNIQUE(name);普通索引：
CREATE TABLE user8(index(name)); -- 表定義時
ALTER TABLE user9 ADD INDEX(name); -- 表創建后
CREATE INDEX idx_name ON user10(name); -- 指定索引名全文索引：
CREATE TABLE articles(FULLTEXT(title, body)) ENGINE=MyISAM;
-- 查詢使用MATCH...AGAINST
SELECT * FROM articles WHERE MATCH(title,body) AGAINST('database');索引的查詢和刪除
查詢索引：
SHOW KEYS FROM 表名; -- 詳細索引信息
SHOW INDEX FROM 表名;
DESC 表名; -- 簡略信息刪除索引：
ALTER TABLE 表名 DROP PRIMARY KEY; -- 刪除主鍵索引
ALTER TABLE 表名 DROP INDEX 索引名; -- 刪除普通索引
DROP INDEX 索引名 ON 表名; 索引創建的原則：
- 頻繁作為查詢條件的字段創建索引
- 唯一性差、更新頻繁的字段不適合創建索引
- 不在where子句中出現的字段不創建索引
事務
事務的必要性
比如在買票的過程中，多個客戶端同時查詢并修改數據，可能導致同一張表被重復售賣，這也絕對不允許的。根本原因就是CURD沒有加事務控制，導致非原子性操作。
事務的屬性(ACID)：
- 原子性：事務內操作要么全部成功，要么全部回滾
- 一致性：事務執行前后數據庫狀態必須一致
- 隔離性：并發事務之間互相不干擾
- 持久性：事務提交之后數據永遠保存
事務基礎
- 引擎支持：InnoDB支持事務，MyISAM不支持事務（可用show engines驗證）
- 提交方式
- 自動提交（默認）：單條SQL自動提交
- 手動提交：begin開啟事務，comit提交事務，rollback回滾事務
- 設置：set autocommit=0/1關閉/開啟自動提交
- 事務操作關鍵點：
- 沒有提交的事務即使崩潰，MySQL也會自動回滾
- 已經提交的事務崩潰之后也不會丟失（持久性）
- begin會隱式禁用當前事務的自動提交
事務隔離級別
| 隔離級別 | 臟讀 | 不可重復的 | 幻讀 | 加鎖方式 |
| 讀未提交 | √ | √ | √ | 不加鎖 |
| 讀已提交 | × | √ | √ | 不加鎖 |
| 可重復讀 | × | × | × | 不加鎖 |
| 串行化 | × | × | × | 加鎖 |
MySQL的RR級別通過Next-Key鎖解決幻讀問題
- 讀未提交（RU）：事務可讀到其他事務未提交的數據（臟讀），生產環境禁用
- 讀已提交（RC）：只讀到已提交的數據，但同一事務多次查詢結果可能不同
- 可重復讀（RR）：同一事物內多次查詢結果一致，通過快照讀實現
- 串行化：所有操作串行執行，效率低，極少使用
MVCC：
多版本并發控制（MVCC）解決讀寫沖突，核心機制：
隱藏字段：
- DB_TRX_ID：最近修改事務ID
- DB_ROLL_PTR：指向undo log歷史版本的指針
undo log：
- 存儲數據的歷史版本，形成版本鏈
Read View：
- 事務快照讀時生成，包含：
- m_ids：活躍事務ID列表
- up_limit_id：最小活躍事務ID
- low_limit_id：下一個待分配的事務ID
- 可見性規則
- DB_TRX_ID < up_limit_id：可見（事務已提交）
- DB_TRX_ID >= low_limit_id：不可見（事務尚未開始）
- DB_TRX_ID在m_ids中：不可見（事務未提交）
RR和RC的本質區別：
- RR：事務首次快照讀時候生成Read View，后續服用該視圖。
- RC：每次快照讀都生成新的Read View，導致不可重復讀。
視圖
視 圖是一個虛擬表，其內容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行數據。視 圖的數據變化會影響到基表，基表的數據變化也會影響到視圖。
基本使用
create view 視圖名 as select語句; 刪除視圖
drop view 視圖名;視圖規則和限制：
- 和表一樣，不能出現同名視圖
- 創建視圖數目無限制，但要考慮復雜查詢創建為視圖之后的性能影響
- 視圖不能添加索引，也不能有關聯的觸發器或者默認值
- 視圖可以提高安全性，必須具有足夠的訪問權限
- order by 可以用在視圖中，但是如果從該視圖檢索數據 select 中也含有 order by ,那么該視圖 中的 order by 將被覆蓋
- 視圖可以和表一起使用
用戶管理
用戶存儲位置
- 所有用戶信息存儲在mysql.user系統表中
- 關鍵字段：
- host：允許登錄的主機（losthost表示僅主機）
- user：用戶名
- authentication_string：加密后的密碼
- *_priv：權限字段
創建用戶
CREATE USER '用戶名'@'登錄主機' IDENTIFIED BY '密碼';刪除用戶
DROP USER '用戶名'@'主機名'; -- 必須指定主機名！修改密碼
#自己修改
SET PASSWORD = PASSWORD('新密碼');#root修改其他用戶
SET PASSWORD FOR '用戶'@'主機' = PASSWORD('新密碼');權限管理
授權語法
GRANT 權限列表 ON 數據庫.對象 TO '用戶'@'主機';舉例
GRANT SELECT ON test.* TO 'user'@'localhost'; -- 授權test庫所有表的查詢權限權限作用域
| 作用范圍 | 示例 | 說明 |
| 全局權限 | *.* | 所有數據庫的所有對象 |
| 單庫權限 | 數據庫名.* | 指定數據庫的所有的視圖/表 |
| 單表權限 | 數據庫名.表名 | 指定表的權限 |
查看權限
SHOW GRANTS FOR '用戶'@'主機'; -- 示例：SHOW GRANTS FOR 'user'@'localhost';回收權限
REVOKE 權限列表 ON 數據庫.對象 FROM '用戶'@'主機';舉例
REVOKE ALL ON test.* FROM 'whb'@'localhost'; -- 回收test庫所有權限注意
- 最小權限原則：用戶只擁有完整工作所需要的最小的權限，避免直接使用root賬戶操作日常業務
- 主機限制：創建用戶時嚴格限制host
- 權限刷新：授權/回收之后執行刷新使權限生效
FLUSH PRIVILEGES;4. 權限列表參考
| 常用權限 | 使用對象 |
| SELECT | 表/視圖 |
| INSERT | 表 |
| CREATE | 數據庫/表 |
| DROP | 數據庫/表 |
| EXECUTE | 存儲過程 |
| GRANT OPTION | 允許給其他用戶授權 |
典型流程示例
-- 1. 創建用戶（僅允許本機登錄）
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'StrongPwd123!';-- 2. 授權mytest庫的所有權限
GRANT ALL ON mytest.* TO 'zhangsan'@'localhost';-- 3. 查看權限
SHOW GRANTS FOR 'zhangsan'@'localhost';-- 4. 回收刪除權限
REVOKE DROP ON mytest.* FROM 'zhangsan'@'localhost';-- 5. 刪除用戶
DROP USER 'zhangsan'@'localhost';
![P8784 [藍橋杯 2022 省 B] 積木畫](http://pic.xiahunao.cn/P8784 [藍橋杯 2022 省 B] 積木畫)
)
