1. 認識 MySQL

1.1 認識 MySQL

MySQL 是一個關系型數(shù)據(jù)庫管理系統(tǒng)，主要用來存儲、管理數(shù)據(jù)。

官網(wǎng)：

https://www.mysql.com/

圖片

MySQL 是一個服務器-客戶端系統(tǒng)。

服務器是負責所有數(shù)據(jù)訪問和處理的一個軟件，也就是 DBMS。

客戶端是與 MySQL 進行數(shù)據(jù)庫通信的應用程序?？蛻舳税l(fā)送 SQL 命令到 MySQL 服務器，然后接收服務器的響應。市面上有很多常用的客戶端軟件：SQLyog、Navicat 等。

我們說的安裝 MySQL，其實就是安裝 DBMS 服務器。

1.2 MySQL 結(jié)構(gòu)

MySQL服務器 -> 數(shù)據(jù)庫 -> 表

我們安裝 MySQL 服務器之后，可以在它上面創(chuàng)建很多數(shù)據(jù)庫，然后在每一個數(shù)據(jù)庫上創(chuàng)建很多表

圖片

2. 安裝 MySQL

2.1 安裝服務端

這里我們在 Windows 系統(tǒng)下安裝 MySQL5.7，軟件已經(jīng)放在百度云盤里，大家可以自行下載。

鏈接: https://pan.baidu.com/s/1bpyLRNVBV0ycO4tj43XyTg?pwd=zhif 
提取碼: zhif

雙擊程序安裝：

圖片

圖片

圖片

圖片

一直下一步直到安裝完成。

默認安裝位置：

C:\Program Files\MySQL\MySQL Server 5.7

2.2 安裝 Navicat

Navicat 是一個連接 MySQL 的客戶端。

官網(wǎng):

https://www.navicat.com.cn/

圖片

這里我也給大家下載到百度云盤里了，大家自行安裝即可。支持正版，你懂得！

鏈接: https://pan.baidu.com/s/1L-2BQJTG9f3v4kivzK3n2g?pwd=1234 
提取碼: 1234

Navicat 連接 MySQL 數(shù)據(jù)庫。

圖片

圖片

3. SQL 語句

3.1 DDL：數(shù)據(jù)定義語句，創(chuàng)建數(shù)據(jù)庫、表

3.1.1 數(shù)據(jù)庫相關

1.創(chuàng)建數(shù)據(jù)庫db1

CREATE DATABASE IF NOT EXISTS db1;

2.創(chuàng)建一個使用utf8字符集的db2數(shù)據(jù)庫

CREATE DATABASE IF NOT EXISTS db2 CHARACTER SET utf8;

3.創(chuàng)建一個使用utf8字符集和排序規(guī)則的數(shù)據(jù)庫

CREATE DATABASE IF NOT EXISTS db3 CHARACTER SET utf8 COLLATE utf8_bin;

4.查看所有的數(shù)據(jù)庫

SHOW DATABASES;

5.刪除數(shù)據(jù)庫

DROP DATABASE db1

6.備份數(shù)據(jù)庫

mysqldump -u [username] -p[password] [database_name] > [backupfile.sql]

例如：

mysqldump -u root -p123456 mydatabase > E:\\mydatabase.sql

7.恢復數(shù)據(jù)庫

進入到 mysql 的可執(zhí)行命令下執(zhí)行

source  E:\\mydatabase.sql

3.1.2 表相關

1.創(chuàng)建表

create table table_name

創(chuàng)建表時指定字符集和排序規(guī)則，如果不指定，默認按照設置數(shù)據(jù)庫時的字符集和排序規(guī)則。

CREATE TABLE `sys_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `status` tinyint(1) DEFAULT '0' COMMENT '狀態(tài)（1：正常 0：停用）',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
  `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.查看表結(jié)構(gòu)

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.修改列

#修改列的數(shù)據(jù)類型
alter table table_name modify column col_name new_datatype;
#修改列名和數(shù)據(jù)類型
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 創(chuàng)建數(shù)據(jù)庫和表

其實在日常工作中，大部分人都是使用 MySQL 客戶端創(chuàng)建數(shù)據(jù)庫和表

1.新建數(shù)據(jù)庫

圖片

圖片

注：字符集我們常用的就是 utf8，排序規(guī)則常用的有 utf8_bin 和 utf8_general_ci。其中 utf8_general_ci 是不區(qū)分大小寫的，utf8_bin 是區(qū)分大小寫的。

2.新建表

3.備份和恢復數(shù)據(jù)庫

圖片

3.2 DML：數(shù)據(jù)操作語句，增刪改語句

3.2.1 新增語句

• 插入的數(shù)據(jù)的類型應該和列名的類型一致
• 插入的數(shù)據(jù)位置應該和列的位置一致
  

insert into table_name(column1,column2) values(1,"知否君");
#同時插入多條記錄
insert into table_name(column1,column2) values(1,"知否君"),(2,"李白");

如果要插入所有列的數(shù)據(jù)，可以不用寫列名，例如：

INSERT INTO `users` VALUES(10, '知否君');

表復制：將表中的數(shù)據(jù)再復制一遍

INSERT INTO `tableName01` 
select * from `tableName01`

3.2.2 修改語句

update table_name set colname1 = 值,colname2 = 值 where ...

3.2.3 刪除語句

注：如果沒有where 條件， 會刪除表中所有數(shù)據(jù)!

delete from table_name where ...

3.3 DQL：數(shù)據(jù)查詢語句

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 區(qū)間
• 3.IN 范圍內(nèi)
• 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 ASC

3.3.6 group by 分組

按照字段分組

SELECT * FROM users group by name;

having：分組查詢加入限制條件

SELECT * FROM users group by name having age>18

注：在查詢數(shù)據(jù)時限定條件按照 w(where)、g(group)、h(having)、o(order)順序。

3.3.7 limit 分頁

limit 后面的兩個參數(shù)

1.初始值：（current-1）*size

2.每頁數(shù)量大小

例如：每頁數(shù)量為5，查詢第2頁的數(shù)據(jù)

#(2-1)*5，5
SELECT * FROM users limit 5,5

3.3.8 多表查詢

根據(jù)相關列查詢

select from emp.name,dep.name
from emp,dmp
where emp.dep_id = dep.id

3.3.9 子查詢

子查詢又叫做嵌套查詢，從查詢的結(jié)果里面再次查詢

select * from emp
where emp_no=(
  select emp_no from emp
  where name="知否君”
) and age>18

3.3.10 合并查詢 union

union: 就是將兩個查詢結(jié)果合并在一起，并去重

select employee.name,employee.age
from employee 
union 
select employee.name,employee.age from employee

3.3.11 外連接

左外連接: 左側(cè)的數(shù)據(jù)完全顯示

select employee.name,employee.age,dep.name  depName
from employee
left join dep
on employee.dep_id=dep.id

圖片

右外連接: 右側(cè)的數(shù)據(jù)完全顯示

select employee.name,employee.age,dep.name  depName
from employee
right join dep
on employee.dep_id=dep.id

圖片

4. 常用數(shù)據(jù)類型

MySQL 中常用的數(shù)據(jù)類型主要是數(shù)值、字符串、日期。

4.1 數(shù)值型

數(shù)值型就是用來表示數(shù)值大小的類型，例如表示年齡，表示金額等等。

數(shù)值型表示的范圍分為無符號和有符號。有符號指既可以存正數(shù)又可以存負數(shù)，無符號只能存大于等于 0 的數(shù)。

4.1.1 tinyint

占用 1個字節(jié)，帶符號的數(shù)值范圍是 -128 到 127。無符號的數(shù)值范圍是 0 到 255。

我們在設置年齡、狀態(tài)等數(shù)值在 100 以下的屬性都可以設置成 tinyint 的。

4.1.2 smallint

占用 2個字節(jié)，帶符號的數(shù)值范圍是 -2^15 到 2^15-1，無符號的數(shù)值范圍是 0 到 2^16 -1。

4.1.3 mediumint

占用 3個字節(jié)，帶符號的數(shù)值范圍是 -2^23 到 2^23-1 ,無符號的數(shù)值范圍是 0 到 2^24 -1。

4.1.4 int

占用 4個字節(jié)，帶符號的數(shù)值范圍是 -2^31 到 2^31-1，無符號的數(shù)值范圍是 0 到2^32-1。

4.1.5 bigint

占用 8個字節(jié)，帶符號的數(shù)值范圍是 -2^63 到 2^63-1，無符號的數(shù)值范圍是 0 到 2^64 -1。

4.1.6 float

占用 4個字節(jié)，單精度小數(shù)。

4.1.7 double

占用 8個字節(jié)，雙精度小數(shù)。

4.1.8 decimal（M，D）

可以設置更加精確的小數(shù)位，其中 M 是數(shù)值的總位數(shù)，最大 65 位。D 是小數(shù)點后面的數(shù)字位數(shù)，最大 30 位。

如果想要設置精度更高的數(shù)值，可以設置成 decimal 類型的。

4.1.9 注意

在 MySQL中，int(1)和int(10)的主要區(qū)別在于顯示寬度，而不是存儲范圍。 無論是指定為int(1)還是 int(10)，它們所能存儲的整數(shù)值范圍都是一樣的，都是-2^31 到 2^31-1。

當設置填充0時，兩者的區(qū)別更明顯：

圖片

圖片

4.2 字符串

4.2.1 char(size)

固定長度字符串，最大可以存儲 255 個字符。

4.2.2 varchar(size)

可變長度字符串，最大存儲 65535 字節(jié)，其中 3 個字節(jié)用來記錄存儲空間大小。

如果表的編碼是 utf8，則最多可以存儲 (65535-3) / 3 = 21844 個字符。

如果表的編碼是 gbk ，則最多可以存儲 (65535-3) / 2 = 32766 個字符。

4.2.3 text

最多可以存儲 2^16 個字符。

4.2.4 longtext

最多可以存儲 2^32 個字符。

4.3 日期型

日期型主要用來存儲和時間、日期相關的數(shù)值。

4.3.1 time

主要用來存儲時分秒格式的數(shù)據(jù)：17:53:32

4.3.2 date

主要用來存儲 yy-MM-dd 格式的數(shù)據(jù)

4.3.3 datetime

主要用來存儲 yy-MM-dd HH:mm:ss 格式的數(shù)據(jù)

4.3.4 timestamp

時間戳，其實格式也是 yy-MM-dd HH:mm:ss，新增或者修改數(shù)據(jù)時可以自動設置該值，一般默認值需要設置為 CURRENT_TIMESTAMP。

圖片

5. 函數(shù)

5.1 聚合函數(shù)

5.1.1 統(tǒng)計

count: 返回統(tǒng)計的行數(shù)，count(*) 返回總記錄數(shù)，count(列名) 返回某列不為 null 的總記錄數(shù)

語法：

select count(*)/count(列名) from tableName where 條件

例1：返回訂單總數(shù)

select COUNT(*) from `order`

例2：返回用戶名不為 NULL 的總訂單數(shù)

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("知否技術(shù)");
select LENGTH(name) from `order`;

5.2.2 轉(zhuǎn)大寫

UPPER：英文小寫轉(zhuǎn)大寫

select UPPER("zhifoujishu");
select UPPER(name) from `order`;

5.2.3 轉(zhuǎn)小寫

LOWER: 英文大寫轉(zhuǎn)小寫

select LOWER("ZHIFOUJISHU");
select LOWER(name) from `order`;

5.2.4 拼接

CONCAT: 拼接字符串

SELECT CONCAT('知否', '&', '技術(shù)');

5.2.5 替換

REPLACE：替換字符串

SELECT REPLACE('知否', '否', '否技術(shù)');

5.2.6 截取

SUBSTRING：截取字符串。

• 第1個參數(shù)：要截取的字符串
• 第2個參數(shù)：起始位，默認從 1 開始
• 第3個參數(shù)：截取后字符串的長度

SELECT SUBSTRING('公眾號知否技術(shù)', 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:ss

5.3.2 從日期中選擇出年份

select year("2024-08-18");

5.3.3 從日期中選擇出月份

select MONTH("2024-08-18");

5.3.4 從日期中選擇出天數(shù)

select DAY("2024-08-18");

5.3.5 日期差：天數(shù)

select DATEDIFF("2024-09-18","2024-08-18")

5.3.6 日期加減

select DATE_ADD("2024-09-18",INTERVAL 1 DAY);#日期相加-天數(shù)
select DATE_SUB("2024-09-18",INTERVAL 1 MONTH);#日期相減-月份

6.表約束

6.1 主鍵

關鍵詞：primary key

解答：一個表中只能有一個主鍵，主鍵不能重復不能為 null，可以是單列主鍵或者是多列復合主鍵。

CREATE TABLE order_detail (  
        order_id INT PRIMARY KEY, 
    );

如果一個表中同時設置多個列是主鍵，那么這幾個列聯(lián)合起來叫做復合主鍵。

例：這里設置 id 和 type 列是復合主鍵。那么 id 和 type 聯(lián)合起來不能重復

圖片

案例中，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

他們的區(qū)別如下：

• CASCADE：主表 delete、update 的時候，子表會 delete、update 掉關聯(lián)記錄;
• RESTRICT：如果想要刪除主表的記錄，而子表中有關聯(lián)主表的列，則不允許刪除父表中的記錄;
• NO ACTION：同 RESTRICT;
• SET NULL：主表 delete、update 的時候，子表會將外鍵字段所在列設為 null，所以注意在設計子表時外鍵不能設為 not null;

6.4 自增長

解答：我們一般在設置數(shù)值的時候會設置自增長，例如設置 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;

我們也可以修改自增長的初始值從某個數(shù)字開始：

alter table table_name auto_increment = 1;

7.索引

我們在參加面試的時候，面試官經(jīng)常會問：如何提高數(shù)據(jù)庫查詢速度？

大部分人可能只知道一個答案：加索引。

索引就像字典的目錄一樣，可以提高查詢速度。

在 MySQL 中，索引的本質(zhì)其實就是一個文件，它的結(jié)構(gòu)是二叉樹：

圖片

沒有索引之前，MySQL 需要對整個表全部掃描一遍才能查到數(shù)據(jù)，所以速度非常慢。

有了索引之后，MySQL 直接去索引文件查數(shù)據(jù)，先從根節(jié)點查找，如果要查的數(shù)據(jù)比根節(jié)點小，那就進入左邊，如果比根節(jié)點大，那就進入右邊，以此類推，就像我們翻詞典目錄一樣，所以速度非?？臁?/p>

MySQL 中常用的索引類型主要有以下幾種：

7.1 主鍵索引

任何加了主鍵約束的列默認都設置了主鍵索引。

我們在設計表的時候一般都會給 id 設置主鍵，所以跟據(jù) id 查詢數(shù)據(jù)的時候會很快。這就是因為 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 語句中用到了組合索引中的最左邊的索引，那么這個索引就有效。

舉個例子：我們創(chuàng)建一個用戶表：

CREATE TABLE `z_user`(
 `id` INT,
 `name` VARCHAR(50),
 `age` INT
);

然后創(chuàng)建索引，后面使用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;

總結(jié)：前面我們給 abc 三個字段設置了組合索引，發(fā)現(xiàn)只要查詢條件中包含 a 字段，索引就有效，這就是“最左前綴”原則。

7.5 Navicat 設置索引

使用 Navicat 設置索引非常簡單，索引類型選擇 NORMAL，索引方法選擇 BTREE。

如果是組合索引，那就選擇多個字段。

圖片

8.用戶管理

8.1 創(chuàng)建用戶

CREATE USER 'zhifoujun'@'localhost' IDENTIFIED BY '123456';

8.2 修改密碼

root 用戶修改其他用戶密碼

SET PASSWORD FOR 'zhifoujun'@'localhost' = PASSWORD('123456')

自己修改自己的密碼

SET PASSWORD = PASSWORD('123456')

8.3 刪除用戶

如果用戶 host 不是 % ,需要根據(jù)設置的 ip 刪除

DROP USER 'zhifoujun'@'localhost';

如果用戶 host 是 % ,直接根據(jù)名字刪除

DROP USER zhifou

9.權(quán)限管理

9.1 給用戶授權(quán)

圖片

grant 權(quán)限列表 on 數(shù)據(jù)庫.表 to '用戶名’@’登錄位置' 密碼

給用戶 zhifou 設置 查詢、新增權(quán)限

GRANT SELECT, INSERT
 ON testdb.users
 TO 'zhifou'@'localhost';

9.2 收回用戶權(quán)限

REVOKE SELECT, INSERT ON testdb.users FROM 'zhifou'@'localhost';