MySQL是怎樣使用的，超全面的總結！
1. 認識 MySQL
1.1 認識 MySQL
MySQL 是一個關系型數據庫管理系統，主要用來存儲、管理數據。
官網：
https://www.mysql.com/
圖片
MySQL 是一個服務器-客戶端系統。
服務器是負責所有數據訪問和處理的一個軟件，也就是 DBMS。
客戶端是與 MySQL 進行數據庫通信的應用程序。客戶端發送 SQL 命令到 MySQL 服務器，然后接收服務器的響應。市面上有很多常用的客戶端軟件：SQLyog、Navicat 等。
我們說的安裝 MySQL，其實就是安裝 DBMS 服務器。
1.2 MySQL 結構
MySQL服務器 -> 數據庫 -> 表
我們安裝 MySQL 服務器之后，可以在它上面創建很多數據庫，然后在每一個數據庫上創建很多表
圖片
2. 安裝 MySQL
2.1 安裝服務端
這里我們在 Windows 系統下安裝 MySQL5.7，軟件已經放在百度云盤里，大家可以自行下載。
鏈接: https://pan.baidu.com/s/1bpyLRNVBV0ycO4tj43XyTg?pwd=zhif
提取碼: zhif雙擊程序安裝：
圖片
圖片
圖片
圖片
一直下一步直到安裝完成。
默認安裝位置：
C:\Program Files\MySQL\MySQL Server 5.72.2 安裝 Navicat
Navicat 是一個連接 MySQL 的客戶端。
官網:
https://www.navicat.com.cn/
圖片
這里我也給大家下載到百度云盤里了，大家自行安裝即可。支持正版，你懂得！
鏈接: https://pan.baidu.com/s/1L-2BQJTG9f3v4kivzK3n2g?pwd=1234
提取碼: 1234Navicat 連接 MySQL 數據庫。
圖片
圖片
3. SQL 語句
3.1 DDL：數據定義語句，創建數據庫、表
3.1.1 數據庫相關
1.創建數據庫db1
CREATE DATABASE IF NOT EXISTS db1;2.創建一個使用utf8字符集的db2數據庫
CREATE DATABASE IF NOT EXISTS db2 CHARACTER SET utf8;3.創建一個使用utf8字符集和排序規則的數據庫
CREATE DATABASE IF NOT EXISTS db3 CHARACTER SET utf8 COLLATE utf8_bin;4.查看所有的數據庫
SHOW DATABASES;5.刪除數據庫
DROP DATABASE db16.備份數據庫
mysqldump -u [username] -p[password] [database_name] > [backupfile.sql]例如：
mysqldump -u root -p123456 mydatabase > E:\\mydatabase.sql7.恢復數據庫
進入到 mysql 的可執行命令下執行
source E:\\mydatabase.sql3.1.2 表相關
1.創建表
create table table_name創建表時指定字符集和排序規則，如果不指定，默認按照設置數據庫時的字符集和排序規則。
CREATE TABLE `sys_user` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`status` tinyint(1) DEFAULT '0' COMMENT '狀態（1：正常 0：停用）',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用戶表';2.刪除表
drop table table_name;3.修改表名
rename table old_name to new_name;4.查看表結構
desc table_name;5.添加列
alter table table_name add col_name;
ALTER TABLE users ADD type datatype AFTER name;
ALTER TABLE users ADD type datatype BEFORE name;6.修改列
#修改列的數據類型
alter table table_name modify column col_name new_datatype;
#修改列名和數據類型
alter table table_name change old_col_name new_col_name new_datatype;7.刪除列
alter table table_name drop column col_name;3.1.3 Navicat 創建數據庫和表
其實在日常工作中，大部分人都是使用 MySQL 客戶端創建數據庫和表
1.新建數據庫
圖片
圖片
注：字符集我們常用的就是 utf8，排序規則常用的有 utf8_bin 和 utf8_general_ci。其中 utf8_general_ci 是不區分大小寫的，utf8_bin 是區分大小寫的。
2.新建表
3.備份和恢復數據庫
圖片
3.2 DML：數據操作語句，增刪改語句
3.2.1 新增語句
- 插入的數據的類型應該和列名的類型一致
- 插入的數據位置應該和列的位置一致
insert into table_name(column1,column2) values(1,"知否君");
#同時插入多條記錄
insert into table_name(column1,column2) values(1,"知否君"),(2,"李白");如果要插入所有列的數據，可以不用寫列名，例如：
INSERT INTO `users` VALUES(10, '知否君');表復制：將表中的數據再復制一遍
INSERT INTO `tableName01`
select * from `tableName01`3.2.2 修改語句
update table_name set colname1 = 值,colname2 = 值 where ...3.2.3 刪除語句
注：如果沒有where 條件， 會刪除表中所有數據!
delete from table_name where ...3.3 DQL：數據查詢語句
3.3.1 基本查詢語句
select * from tableName where注：select * 表示查詢所有列
3.3.2 distinct 去重
distinct: 要查詢的字段值全部相同，才會去重
SELECT DISTINCT name,age FROM users;3.3.3 別名：as
可以使用 as 表示別名
select name as thisName FROM `order`3.3.4 where 常用運算符
- 1.>、<、>=、<=、= 等比較運算符
- 2.BETWEEN ...AND 區間
- 3.IN 范圍內
- 4.LIKE 模糊查詢
- 5.IS NULL、IS NOT NULL 是否為 NULL
- 6.AND 和運算符
- 7.OR 或運算符
- 8.NOT 非
SELECT * FROM users WHERE `name`='知否君';
SELECT * FROM users WHERE `age`>=18;;
SELECT * FROM users WHERE age >18 AND id >101;
SELECT * FROM users WHERE age BETWEEN 18 AND 35;
SELECT * FROM users WHERE `name` like '%知否君';3.3.5 order by 排序
默認是升序，我們可以指定升序還是降序
- 1.ASC：升序
- 2.DESC: 降序
SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age DESC,status ASC3.3.6 group by 分組
按照字段分組
SELECT * FROM users group by name;having：分組查詢加入限制條件
SELECT * FROM users group by name having age>18注：在查詢數據時限定條件按照 w(where)、g(group)、h(having)、o(order)順序。
3.3.7 limit 分頁
limit 后面的兩個參數
1.初始值：（current-1）*size
2.每頁數量大小
例如：每頁數量為5，查詢第2頁的數據
#(2-1)*5，5
SELECT * FROM users limit 5,53.3.8 多表查詢
根據相關列查詢
select from emp.name,dep.name
from emp,dmp
where emp.dep_id = dep.id3.3.9 子查詢
子查詢又叫做嵌套查詢，從查詢的結果里面再次查詢
select * from emp
where emp_no=(
select emp_no from emp
where name="知否君”
) and age>183.3.10 合并查詢 union
union: 就是將兩個查詢結果合并在一起，并去重
select employee.name,employee.age
from employee
union
select employee.name,employee.age from employee3.3.11 外連接
左外連接: 左側的數據完全顯示
select employee.name,employee.age,dep.name depName
from employee
left join dep
on employee.dep_id=dep.id
圖片
右外連接: 右側的數據完全顯示
select employee.name,employee.age,dep.name depName
from employee
right join dep
on employee.dep_id=dep.id
圖片
4. 常用數據類型
MySQL 中常用的數據類型主要是數值、字符串、日期。
4.1 數值型
數值型就是用來表示數值大小的類型，例如表示年齡，表示金額等等。
數值型表示的范圍分為無符號和有符號。有符號指既可以存正數又可以存負數，無符號只能存大于等于 0 的數。
4.1.1 tinyint
占用 1個字節，帶符號的數值范圍是 -128 到 127。無符號的數值范圍是 0 到 255。
我們在設置年齡、狀態等數值在 100 以下的屬性都可以設置成 tinyint 的。
4.1.2 smallint
占用 2個字節，帶符號的數值范圍是 -2^15 到 2^15-1，無符號的數值范圍是 0 到 2^16 -1。
4.1.3 mediumint
占用 3個字節，帶符號的數值范圍是 -2^23 到 2^23-1 ,無符號的數值范圍是 0 到 2^24 -1。
4.1.4 int
占用 4個字節，帶符號的數值范圍是 -2^31 到 2^31-1，無符號的數值范圍是 0 到2^32-1。
4.1.5 bigint
占用 8個字節，帶符號的數值范圍是 -2^63 到 2^63-1，無符號的數值范圍是 0 到 2^64 -1。
4.1.6 float
占用 4個字節，單精度小數。
4.1.7 double
占用 8個字節，雙精度小數。
4.1.8 decimal（M，D）
可以設置更加精確的小數位，其中 M 是數值的總位數，最大 65 位。D 是小數點后面的數字位數，最大 30 位。
如果想要設置精度更高的數值，可以設置成 decimal 類型的。
4.1.9 注意
在 MySQL中，int(1)和int(10)的主要區別在于顯示寬度，而不是存儲范圍。 無論是指定為int(1)還是 int(10)，它們所能存儲的整數值范圍都是一樣的，都是-2^31 到 2^31-1。
當設置填充0時，兩者的區別更明顯：
圖片
圖片
4.2 字符串
4.2.1 char(size)
固定長度字符串，最大可以存儲 255 個字符。
4.2.2 varchar(size)
可變長度字符串，最大存儲 65535 字節，其中 3 個字節用來記錄存儲空間大小。
如果表的編碼是 utf8，則最多可以存儲 (65535-3) / 3 = 21844 個字符。
如果表的編碼是 gbk ，則最多可以存儲 (65535-3) / 2 = 32766 個字符。
4.2.3 text
最多可以存儲 2^16 個字符。
4.2.4 longtext
最多可以存儲 2^32 個字符。
4.3 日期型
日期型主要用來存儲和時間、日期相關的數值。
4.3.1 time
主要用來存儲時分秒格式的數據：17:53:32
4.3.2 date
主要用來存儲 yy-MM-dd 格式的數據
4.3.3 datetime
主要用來存儲 yy-MM-dd HH:mm:ss 格式的數據
4.3.4 timestamp
時間戳，其實格式也是 yy-MM-dd HH:mm:ss，新增或者修改數據時可以自動設置該值，一般默認值需要設置為 CURRENT_TIMESTAMP。
圖片
5. 函數
5.1 聚合函數
5.1.1 統計
count: 返回統計的行數，count(*) 返回總記錄數，count(列名) 返回某列不為 null 的總記錄數
語法：
select count(*)/count(列名) from tableName where 條件例1：返回訂單總數
select COUNT(*) from `order`例2：返回用戶名不為 NULL 的總訂單數
select COUNT(name) from `order`5.1.2 合計
SUM：計算某列的總和
select SUM(amount) from `order`5.1.3 平均值
AVG：計算某列的平均值
select AVG(amount) from `order`5.1.4 最大值
MAX：計算某列的最大值
select MAX(amount) from `order`5.1.5 最小值
MIN：計算某列的最小值
select MIN(amount) from `order`5.2 字符串
5.2.1 字符串長度
LENGTH：返回字符串的長度
select LENGTH("知否技術");
select LENGTH(name) from `order`;5.2.2 轉大寫
UPPER：英文小寫轉大寫
select UPPER("zhifoujishu");
select UPPER(name) from `order`;5.2.3 轉小寫
LOWER: 英文大寫轉小寫
select LOWER("ZHIFOUJISHU");
select LOWER(name) from `order`;5.2.4 拼接
CONCAT: 拼接字符串
SELECT CONCAT('知否', '&', '技術');5.2.5 替換
REPLACE：替換字符串
SELECT REPLACE('知否', '否', '否技術');5.2.6 截取
SUBSTRING：截取字符串。
- 第1個參數：要截取的字符串
- 第2個參數：起始位，默認從 1 開始
- 第3個參數：截取后字符串的長度
SELECT SUBSTRING('公眾號知否技術', 2, 5);
圖片
5.3 時間
5.3.1 獲取當前日期時間
select CURRENT_DATE(); #yyyy-MM-dd
select CURRENT_TIME();#HH:mm:ss
select NOW();#yyyy-MM-dd HH:mm:ss5.3.2 從日期中選擇出年份
select year("2024-08-18");5.3.3 從日期中選擇出月份
select MONTH("2024-08-18");5.3.4 從日期中選擇出天數
select DAY("2024-08-18");5.3.5 日期差：天數
select DATEDIFF("2024-09-18","2024-08-18")5.3.6 日期加減
select DATE_ADD("2024-09-18",INTERVAL 1 DAY);#日期相加-天數
select DATE_SUB("2024-09-18",INTERVAL 1 MONTH);#日期相減-月份6.表約束
6.1 主鍵
關鍵詞：primary key
解答：一個表中只能有一個主鍵，主鍵不能重復不能為 null，可以是單列主鍵或者是多列復合主鍵。
CREATE TABLE order_detail (
order_id INT PRIMARY KEY,
);如果一個表中同時設置多個列是主鍵，那么這幾個列聯合起來叫做復合主鍵。
例：這里設置 id 和 type 列是復合主鍵。那么 id 和 type 聯合起來不能重復
圖片
案例中，id 和 type 不能同時一樣。
圖片
6.2 唯一
關鍵詞： unique
解答：不能重復
6.3 外鍵
關鍵字：FOREIGN KEY
外鍵用來定義主表和從表之間的關系。首先主表的某個字段必須要有唯一約束，然后外鍵約束主要定義在從表上。
當定義外鍵約束之后，主表列必須存在或者為 null。
6.3.1 外鍵案例
新建部門表：id 是主鍵
CREATE TABLE `demp` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;新建員工表：dep_id 是外鍵
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`dep_id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`dep_id`) REFERENCES `demp` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;employee 中 dep_id 是 demp 表中已存在的 id，如果不存在可以設置成 null。
圖片
6.3.2 navicat 設置外鍵
圖片
用 Navicat 設置外鍵的時候，刪除時和更新時有四個值可以選擇：CASCADE、NO ACTION、RESTRICT、SET NULL
他們的區別如下：
- CASCADE：主表 delete、update 的時候，子表會 delete、update 掉關聯記錄;
- RESTRICT：如果想要刪除主表的記錄，而子表中有關聯主表的列，則不允許刪除父表中的記錄;
- NO ACTION：同 RESTRICT;
- SET NULL：主表 delete、update 的時候，子表會將外鍵字段所在列設為 null，所以注意在設計子表時外鍵不能設為 not null;
6.4 自增長
解答：我們一般在設置數值的時候會設置自增長，例如設置 id 自增長: AUTO_INCREMENT。自增長默認從 1 始。
CREATE TABLE `order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;我們也可以修改自增長的初始值從某個數字開始：
alter table table_name auto_increment = 1;7.索引
我們在參加面試的時候，面試官經常會問：如何提高數據庫查詢速度？
大部分人可能只知道一個答案：加索引。
索引就像字典的目錄一樣，可以提高查詢速度。
在 MySQL 中，索引的本質其實就是一個文件，它的結構是二叉樹：
圖片
沒有索引之前，MySQL 需要對整個表全部掃描一遍才能查到數據，所以速度非常慢。
有了索引之后，MySQL 直接去索引文件查數據，先從根節點查找，如果要查的數據比根節點小，那就進入左邊，如果比根節點大，那就進入右邊，以此類推，就像我們翻詞典目錄一樣，所以速度非常快。
MySQL 中常用的索引類型主要有以下幾種：
7.1 主鍵索引
任何加了主鍵約束的列默認都設置了主鍵索引。
我們在設計表的時候一般都會給 id 設置主鍵，所以跟據 id 查詢數據的時候會很快。這就是因為 id 默認設置了主鍵索引。
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT, -- 主鍵
);7.2 唯一索引
設置唯一索引之后，該列必須是唯一的。
例如：我們給 uname 屬性設置唯一索引
CREATE UNIQUE INDEX uname_index ON users (uname);
#或者
ALTER TABLE users ADD UNIQUE INDEX uname_index (uname);然后插入兩條記錄
INSERT INTO users (uname, email) VALUES ('zhifoujun', '124@qq.com');
INSERT INTO users (uname, email) VALUES ('zhifoujun', '456@qq.com');因為 uname 設置了唯一索引，所以插入失敗。
7.3 普通索引
普通索引：就是給任意列設置索引
CREATE INDEX uname_index ON users (uname);
#或者
ALTER TABLE users ADD INDEX uname_index (uname);7.4 組合索引
組合索引：一個索引包含多個列
CREATE INDEX indexName ON table_name (column_name1,column_name2,...);組合索引遵循最左前綴原則：就是如果你的 SQL 語句中用到了組合索引中的最左邊的索引，那么這個索引就有效。
舉個例子：我們創建一個用戶表：
CREATE TABLE `z_user`(
`id` INT,
`name` VARCHAR(50),
`age` INT
);然后創建索引，后面使用abc代表這三個索引字段
CREATE UNIQUE INDEX index_name ON z_user(id,name,age);索引有效的情況：abc、ab、ac、a
SELECT * FROM t_user WHERE id = 1 AND NAME = 'zhifoujun' AND age = 21;
SELECT * FROM t_user WHERE id = 1 AND NAME = 'zhifoujun';
SELECT * FROM t_user WHERE id = 1 AND age = 21;
SELECT * FROM t_user WHERE id = 1;索引失效情況：b、c、bc
SELECT * FROM t_user WHERE NAME = 'zhifoujun';
SELECT * FROM t_user WHERE age = 21;
SELECT * FROM t_user WHERE NAME = 'zhifoujun' AND age = 21;總結：前面我們給 abc 三個字段設置了組合索引，發現只要查詢條件中包含 a 字段，索引就有效，這就是“最左前綴”原則。
7.5 Navicat 設置索引
使用 Navicat 設置索引非常簡單，索引類型選擇 NORMAL，索引方法選擇 BTREE。
如果是組合索引，那就選擇多個字段。
圖片
8.用戶管理
8.1 創建用戶
CREATE USER 'zhifoujun'@'localhost' IDENTIFIED BY '123456';8.2 修改密碼
root 用戶修改其他用戶密碼
SET PASSWORD FOR 'zhifoujun'@'localhost' = PASSWORD('123456')自己修改自己的密碼
SET PASSWORD = PASSWORD('123456')8.3 刪除用戶
如果用戶 host 不是 % ,需要根據設置的 ip 刪除
DROP USER 'zhifoujun'@'localhost';如果用戶 host 是 % ,直接根據名字刪除
DROP USER zhifou9.權限管理
9.1 給用戶授權
圖片
grant 權限列表 on 數據庫.表 to '用戶名’@’登錄位置' 密碼給用戶 zhifou 設置 查詢、新增權限
GRANT SELECT, INSERT
ON testdb.users
TO 'zhifou'@'localhost';9.2 收回用戶權限
REVOKE SELECT, INSERT ON testdb.users FROM 'zhifou'@'localhost';
