MySQL基本操作(CRUD)詳解

本篇文章給大家帶來了關于mysql的相關知識，其中主要介紹了關于mysql的一些基本操作，SQL的基本操作一般是指對數據庫，數據表，數據的增刪改查，下面一起來看一下，希望對大家有幫助。

程序員必備接口測試調試工具：立即使用
Apipost = Postman + Swagger + Mock + Jmeter
Api設計、調試、文檔、自動化測試工具
后端、前端、測試，同時在線協作，內容實時同步

推薦學習：mysql視頻教程

總的來說，SQL分為四大類，分別是數據定義語言DDL，數據操作語言DML，數據查詢語言DQL和數據控制語言DCL。而SQL的基本操作一般是指對數據庫，數據表，數據的增刪改查。

1. DDL- 操作數據庫

首先要學習的是使用DDL來對數據庫進行操作，主要是對數據庫的增刪改查操作。

1.1 查詢

查詢所有的數據庫：

show databases;

例如：

1.2 創建數據庫

創建新的數據庫：

create database 數據庫名稱;

使用上面的方式創建新的數據庫時，如果該數據庫已經存在，則會出現錯誤，所以我們在創建新的數據庫時一般會判斷該數據庫是否存在，如果已存在，則不會創建。

創建新的數據庫(判斷數據庫是否已經存在)：

create database if not exists 數據庫名稱;

例如：

1.3 刪除數據庫

刪除數據庫：

drop database 數據庫名稱;

和前面創建新的數據庫相同，為了避免出現錯誤，我們一般會先判斷該數據庫是否存在，如果不存在，則不會刪除。

刪除數據庫(判斷數據庫是否已經存在)：

drop database if exists 數據庫名稱;

例如：

1.4 使用數據庫

現在我們已經成功創建了新的數據庫，接下來，我們要在數據庫中創建數據表，首先我們要明白是對哪一個數據庫進行操作，此時我們要先學會使用數據庫，才能對數據庫中的表進行操作。

使用數據庫：

use 數據庫名稱;

查詢當前正在使用的數據庫：

select database();

例如：

2. DDL- 操作數據表

同樣，對數據表的操作無非就是增刪改查，在學習相關操作之前，我們先要熟悉一下MySQL的數據類型。

2.1 數據類型

MySQL支持多種數據類型，但是大致可以分為3中，分別是數值型，日期型和字符串型。下面是比較常用的幾種數據類型：

數值：

日期：

字符串：

定長字符串和變長字符串的區別：字符串是我們在數據庫中經常使用的數據類型，使用變長字符串，如果字符的長度沒有達到指定的長度，那么實際的長度是多少就占用幾個字符，這樣的做法顯然是使用時間換空間，而使用定長字符串雖然有時會出現浪費空間的情況，但是一般儲存性能比較高。

2.2 查詢表

查詢當前數據庫下所有的表：

show tables;

查詢表結構：

desc 表名稱;

例如：

2.3 創建表

創建一個新的表：

create table 表名稱( 		字段名1 數據類型, 		字段名2 數據類型, 		... 		字段名n 數據類型  #這里是不需要加上,的);

例如：

2.4 刪除表

刪除表：

drop table 表名;

刪除表(判斷表是否存在)：

drop table if exists 表名;

例如：

2.5 修改表

修改表名：

alter table 表名 rename to 新的表名;

增加一列：

alter table 表名 add 列名 數據類型;

修改數據類型：

alter table 表名 modify 列名 新數據類型;

修改列名和數據類型：

alter table 表名 change 列名 新列名 新數據類型;

刪除列：

alter table 表名 drop 列名;

例如：

3. 實戰案例詳解

需求：設計包含如下信息的學生表，請注重數據類型、長度的合理性。

• 編號

• 姓名，姓名最長不超過10個漢字

• 性別，因為取值只有兩種可能，因此最多一個漢字

• 生日，取值為年月日

• 成績，小數點后保留兩位

• 地址，最大長度不超過 64

• 學生狀態（用數字表示，正常、休學、畢業…）

在完成這樣一個案例前，首先要創建一個學生數據庫，在數據庫中創建一張新的表，創建表時注意語法格式，數據類型和長度的合理性。

以管理員身份運行命令提示符cmd，啟動Mysql服務，登錄MySQL：

創建學生信息數據庫：

create database if not exists student;

使用student數據庫：

use student;

創建數據表：

create table stu( 		id int ,-- 編號 		name varchar(10),-- 姓名 		gender char(1),-- 性別 		birthday date,-- 生日 		score double(5,2) ,-- 分數 		addr varchar(50),-- 地址 		status tinyint-- 狀態);

現在，我們已經學會了寫SQL來操作數據庫，但是我們在命令行中寫SQL時，往往有體驗感差，效率低等問題，今天開始我們就要學習在MySQL的圖形化客戶端Navicat中執行SQL語句。

Navicat 為數據庫管理、開發和維護提供了一款直觀而強大的圖形化界面，大大的提高了工作效率，建議在學習中也使用這款開發工具。接下來，在Navicat中新建數據庫，新建查詢，我們就可以編寫SQL并且執行SQL語句了。

4. DML- 增刪改數據

4.1 添加數據

給指定列添加數據：

insert into 表名(列名1,列名2...) values(值1,值2...);

給全部列添加數據：

insert into 表名 values(值1,值2...);

批量添加數據：

insert into 表名(列名1,列名2...) values(值1,值2...),(值1,值2...)，(值1,值2...)...;

批量添加數據(省略字段名)：

insert into 表名 values(值1,值2...),(值1,值2...)，(值1,值2...)...;

在開發過程中添加數據時是不建議省略字段名的，這樣降低了代碼的可讀性，使效率下降。例如：

查詢表中的所有數據的方法是：

select * from 表名;

后面會用到的。

需求：往下面的tb_user表中添加一條數據。

insert into tb_user(id,name) values(2,'李四');

添加成功：

4.2 修改數據

修改表的數據：

update 表名 set 列名1=值1,列名2=值2...[where 條件];

在修改數據時，也可以不使用where條件，此時的操作是修改整列數據，這樣的操作是很危險的。

需求：把下面tb_user表中的張三的密碼改為abc23

update tb_user set passwor d ='abc123' where name='張三';

修改成功：

4.3 刪除數據

刪除表的數據：

delete from 表名 [where 條件];

在刪除某條數據時，如果不使用where條件，將會導致刪除整個表的數據。

需求：刪除tb_user表中的李四記錄。

delete from tb_user where name='李四';

操作成功：

5. DQL- 數據的查詢操作

查詢是數據操作至關重要的一部分，比如說在所有商品中查找出價格在規定范圍內的所有商品，要想把數據庫中的數據在客戶端中展示給用戶，一般都進行了查詢的操作。

在實際開發中，我們要根據不同的需求，并且考慮查詢的效率來決定怎樣進行查詢，學習查詢前，可以先看看查詢的完整語法：

SELECT 	字段列表FROM 	表名列表WHERE 	條件列表GROUP BY 	分組字段HAVING 	分組后條件ORDER BY 	排序字段LIMIT 	分頁限定

根據查詢的完整語法中的關鍵字，我們分別來學習基礎查詢，條件查詢，排序查詢，分組查詢和分頁查詢。

下面的練習中使用以下的案例學習單表查詢：

-- 刪除stu表drop table if exists stu;-- 創建stu表CREATE TABLE stu (id int, -- 編號name varchar(10), -- 姓名age int, -- 年齡gender varchar(5), -- 性別math double(5,2), -- 數學成績english double(5,2) -- 英語成績);-- 添加數據INSERT INTO stu(id,name,age,gender,math,english)VALUES(1,'小張',23,'男',66,78),(2,'小李',20,'女',98,87),(3,'小陳',55,'男',56,77),(4,'小樊',20,'女',76,65),(5,'小馬',20,'男',86,NULL),(6,'小趙',57,'男',99,99);

在Navicat中選中SQL并執行：

5.1 基礎查詢

1.1 基礎查詢語法

查詢多個字段：

select 字段列表 from 表名;

查詢全部字段：

select * from 表名;

去除重復記錄：

select distinct 字段列表 from 表名;

起別名操作：

select 字段名 別名 from 表名;

1.2 基礎查詢練習

使用學生表進行基礎查詢練習：

查詢多個字段的練習：

select name,math from stu;

起別名操作練習：

select name,english 英語成績 from stu;

5.2 條件查詢

2.1 條件查詢語法

一般語法：

select 字段列表 from 表名 where 條件列表;

條件查詢一般配合運行符進行，下面是常見的幾個運算符：

2.2 條件查詢練習

使用學生表進行條件查詢練習：

查詢年齡大于20的學生信息：

select * from stu where age>20;

查詢年齡等于18歲 或者 年齡等于20歲 或者 年齡等于21歲的學生信息：

select * from stu where age in(18,20,21);

模糊查詢使用like關鍵字，可以使用通配符進行占位:

• _ : 代表單個任意字符
• % : 代表任意個數字符

查詢姓名中含有張的學生信息：

select * from stu where name like '%張%';

5.3 排序查詢

3.1 排序查詢語法

select 字段列表 from 表名 order by 排序字段名1 [排序方式]...;

注：排序方式有兩種：分別是升序ASC和降序DESC，默認情況下是升序ASC。

3.2 排序查詢練習

使用學生表進行排序查詢練習：

查詢學生信息，按照數學成績降序排列：

select * from stu order by math DESC;

5.4 聚合函數

4.1 聚合函數語法

什么是聚合函數呢？在進行查詢操作時，往往需要對一整列進行運算，例如可以計算一整列成績數據的平均值，我們就要使用聚合函數。下面是常見的聚合函數：

一般語法：

select 聚合函數 from 表名;

注：NULL值不參與聚合函數運算。

4.2 聚合函數練習

使用學生表進行聚合函數的練習：

統計該表中一共有幾個學生：

select count(id) from stu;

上面我們使用某一字段進行運算，這樣做可能面臨的問題是某一個值可能是NULL，所以我們一般使用 * 進行運算，因為一行中不可能所有的字段都是NULL。

select count(*) from stu;

查詢數學成績的平均分:

select avg(math) from stu;

5.5 分組查詢

5.1 分組查詢語法

select 字段列表 from 表名 [where 分組前的條件限定] group by 分組字段名 [having 分組后的條件過濾]

注：分組之后，查詢的字段為聚合函數和分組字段，查詢其他字段無任何意義。

5.2 分組查詢練習

使用學生表進行分組查詢練習：

查詢男同學和女同學各自的數學平均分，以及各自人數，要求：分數低于70分的不參與分組:

select gender, avg(math),count(*) from stu where math > 70 group by gender;

查詢男同學和女同學各自的數學平均分，以及各自人數，要求：分數低于70分的不參與分組，分組之后人數大于2個的:

select gender, avg(math),count(*) from stu where math > 70 group by gender having count(*) > 2;

注：where 和 having 執行時機不一樣：where 是分組之前進行限定，不滿足where條件，則不參與分組，而having是分組之后對結果進行過濾。所以，where 不能對聚合函數進行判斷，having 可以。

5.6 分頁查詢

6.1 分頁查詢語法

在我們的印象中，網頁在展示大量的數據時，往往不是把數據一下全部展示出來，而是用分頁展示的形式，其實就是對數據進行分頁查詢的操作，即每次只查詢一頁的數據展示到頁面上。

select 字段列表 from 表名 limit 查詢起始索引,查詢條目數;

在 limit 關鍵字中，查詢起始索引這個參數是從0開始的。

6.2 分頁查詢練習

使用學生表進行分頁查詢練習：

從0開始查詢，查詢3條數據：

select * from stu limit 0,3;

起始索引 = (當前頁碼 - 1) * 每頁顯示的條數

推薦學習：mysql視頻教程