深入學習MySQL表數據操作

更新時間：2022年08月01日 10:16:10 作者：俗世游子???????

這篇文章主要介紹了深入學習MySQL表數據操作，基于表操作內容圍繞主題展開詳細介紹，具有一定的參考價值，需要的小伙伴可以參考一下

前言

這一節(jié)我們基于表來做數據方面的操作，同樣的，無非就是C(創(chuàng)建)R(讀取)U(修改)D(刪除)四種操作，但是在R上總能玩出多種花樣

正式開始操作之前，我們先來聊一聊它們的關鍵字：

INSERT
SELECT
UPDATE
DELETE

大家可以先通過help命令來查看一下相關的語法，提前預習一下，方便更深的理解

正式上菜

先來看看之前的表結構：

create table if not exists tb_user(
    id bigint primary key auto_increment comment '主鍵',
    login_name varchar(48) comment '登錄賬戶',
    login_pwd char(36) comment '登錄密碼',
    account decimal(20, 8) comment '賬戶余額',
    login_ip int comment '登錄IP'
) charset=utf8mb4 engine=InnoDB comment '用戶表';

插入數據

在插入之前，我們先來看看平常怎么使用的

insert into table_name[(column_name[,column_name] ...)] value|values (value_list) [, (value_list)]

其實最常用的就這么多，下面我們來舉個例子就明白了

全部字段插入單條數據

insert into tb_user value(1, 'admiun', 'abc123456', 2000, inet_aton('127.0.0.1'));

這樣就插入了一條數據：

auto_increment：自增鍵，在插入數據的時候可以不給當前列指定數據，而且默認情況下我們推薦給主鍵設置自增
inet_aton：ip轉換函數，相對應的還有inet_ntoa()

而且還需要注意一點，如果存在相同的主鍵，那么在插入的時候會出現錯誤

# 主鍵已重復
Duplicate entry '4' for key 'tb_user.PRIMARY'

指定字段插入多條數據

insert into tb_user(login_name, login_pwd) values('admin1', 'abc123456'),('admin2', 'abc123456')

可以看到數據已經插入進來，沒有填充數據的列已NULL填充，關于這一點，我們可以在創(chuàng)建表的時候通過DEFAULT來指定默認值，就是在這個時候使用的

alter table tb_user add column email varchar(50) default 'test@sina.com' comment '郵箱'

沒有什么比實際動手有說服力的了

ON DUPLICATE KEY UPDATE

這里還有一個點，用到的不是很多，但是相當實用：ON DUPLICATE KEY UPDATE

也就是說如果數據表中存在重復的主鍵，那么就進行更新操作，來看：

insert into tb_user(id, login_name, email) value(4, 'test', 'super@sina.com') on duplicate key update login_name = values(login_name), email = values(email);

對比上面的數據，很容易就會發(fā)現數據不一樣了

values(列名)：會取出前面插入的字段的數據

insert into tb_user(id, login_name, email) values(4, 'test', 'super@sina.com'),(5, 'test5', 'test5@sinacom') on duplicate key update login_name = values(login_name), email = values(email);

插入多條數據也是一樣的，就不貼圖了，大家自己動手試一下

修改數據

插入數據相對而言比較簡單，下面我們來看看修改數據

首先從update語法上來講，這個更簡單：

update table_name set column_name=value_list (,column_name=value_list) where condition

舉個栗子：

update tb_user set login_name = 'super@sina.com' where id = 1

這樣就修改了tb_user下編號為1的loign_name的數據

where后條件也可以多個，按照,分割

當然，如果沒有設置查詢條件的話，那么默認是會修改整張表的數據

update tb_user set login_name = 'super@sina.com',account = 2000

好了，修改數據到這里就結束了，很簡單

刪除數據

刪除數據分為：

刪除指定數據
清空整張表

如果只是想刪除某些數據，可以通過delete來刪除，還是來舉個栗子：

delete from tb_user where login_ip is null;

這樣就刪除了指定條件的數據

那么，如果我們執(zhí)行刪除條件，但是不設置條件呢？下面我們來看一看

先執(zhí)行insert操作插入幾條數據

delete from tb_user ;

可以看到，刪除了全部的數據

但其實還有一種方式可以清空整張表，就是通過truncate的方式，這種方式的效率更高

truncate tb_user;

最后就不貼圖了，肯定沒問題的

查詢數據

查詢數據分為多種情況，組合使用可以有N中存在，所以說這是最復雜的一種方式，下面我們一一來介紹

其實如果從語法上來看：查詢語法關鍵點只會包含如下幾點：

SELECT 
	[DISTINCT] select_expr [, select_expr] 
FROM table_name 
WHERE where_condition
GROUP BY col_name
HAVING where_condition
ORDER BY col_name ASC | DESC
LIMIT offset[, row_count]

記住這些關鍵點，查詢就相當簡單了，下面我們先來看個簡單的操作

簡單查詢

select * from tb_user;

-- 按照指定字段排序 asc: 正序 desc: 倒序
select * from tb_user order by id desc;

一共插入了44條數據，沒有全部截圖

當前SQL會查詢出表中全部數據，而跟在select后面的*表示：列出全部的字段，如果我們只是想列出某些列的話，那么將它換成指定的字段名就好：

select id, login_name, login_pwd from tb_user;

就是這么簡單

當然了，還記得這個關鍵字么：DISTINCT，我們來實驗一下：

select distinct login_name from tb_user;

意思已經很明顯了，沒錯，就是去重操作。

但是我要告訴大家的是，distinct關鍵字如果作用在多個字段的話，那么只有在多個字段組合的情況下重復才會進行生效，舉個栗子：

select distinct id,login_name from tb_user;

只有在 id + login_name有重復的時候會生效

聚合函數

在MySQL中內置的聚合函數，對一組數據執(zhí)行計算，并返回單條值，在特殊場景下有特殊的作用

可以加where條件

-- 查詢當前表中的數據條數
select count(*) from tb_user;

-- 查詢當前表中指定列最大的一條
select max(id) from tb_user;
-- 查詢當前表中指定列最小的一條
select min(id) from tb_user;

-- 查詢當前表中指定列的平均值
select avg(account) from tb_user;

-- 查詢當前表中指定列的總和
select sum(account) from tb_user;

除了聚合函數之外，還包含很多普通函數，這里就不一一列舉了，給出官方文檔，用的時候具體查

條件查詢

看到了第一個例子是不是感覺其實查詢沒有那么難。上面的例子都是查詢出全部數據，下面我們要加一些條件進行篩選，這里就用到了我們的where語句，記住一點：

條件篩選是可以有多個的

等值查詢

我們可以通過如下方式進行條件判斷

select * from tb_user where login_name = 'admin1' and login_pwd = 'abc123456';

很多情況下，column_name = column_value是我們用到更多的查詢方式，這種方式我們可以稱為等值查詢，

而且注意到，在條件之前我是通過and來進行關聯(lián)的，Java基礎不錯的小伙伴肯定也記得&&，都是表示并且的意

既然有and，那么與之相反的肯定就是or了，表示只要兩者滿足其中一條就好

select * from tb_user where login_name = 'admin1' or login_pwd = 'abc123456';

除了=匹配的方式，還有其他更多的方式，<，<=，>，>=

和我們認知中不一樣的是：<>表示不等于

不過這些使用方式都是一樣的

批量查詢

在某些特定的情況下，如果想要查詢出一批數據，可以通過in來進行查詢

select * from tb_user where id in(1,2,3,4,5,6);

在in中，相當于傳入的是一個集合，然后查詢指定集合的數據，在很多情況下，這條sql還可以這么寫

select * from tb_user where id in (
	select id from tb_user where login_name = 'admin1'
);

除了in，還有not in與之相反：表示要查詢出來的不包含這些指定的數據

模糊查詢

看完了等值查詢，我們再來看一個模糊查詢：

只要字段數據中包含查詢的數據，就能夠匹配到數據

select * from tb_user where login_name like '%admin%';
select * from tb_user where login_name like '%admin';
select * from tb_user where login_name like 'admin%';

like就是我們模糊查詢中的關鍵成員，而后面的查詢關鍵字分為三種情況：

%admin%：%夾著查詢關鍵字表示只要數據中包含admin就能匹配到
%admin：任意關鍵字開頭，只要是admin結尾的數據都能匹配到
admin%：必須是admin開頭，其他的隨意，這樣的數據就能匹配到

更多的推薦采用這種方式，如果查詢列設置了索引的話，其他方式會讓索引失效

非空判斷

查詢當前表會發(fā)現，數據中的某些列是NULL值，如果我們在查詢過程中向要過濾掉這些數據，我們可以這么做：

select * from tb_user where account is not null;
select * from tb_user where account is null;

is not null就是其中的關鍵點，與之相對的還有is null，意思正好相反

時間判斷

很多情況下，如果我們想要通過時間段來匹配查詢，那么我們可以這樣做：

tb_user表沒有時間字段，這里添加了一個字段：create_time

select * from tb_user where create_time between '2021-04-01 00:00:00' and now();

**now()**函數表示當前時間

between之后表示開始時間，and之后表示結束時間

行轉列

我從一個面試題來聊一聊這個查詢吧：

場景是一樣的，但是SQL不一樣 (關注重點，看題)

create table test(
   id int(10) primary key,
   type int(10) ,
   t_id int(10),
   value varchar(5)
);
insert into test values(100,1,1,'張三');
insert into test values(200,2,1,'男');
insert into test values(300,3,1,'50');

insert into test values(101,1,2,'劉二');
insert into test values(201,2,2,'男');
insert into test values(301,3,2,'30');

insert into test values(102,1,3,'劉三');
insert into test values(202,2,3,'女');
insert into test values(302,3,3,'10');

請寫出一條SQL展示如下結果：

姓名性別年齡
--------- -------- ----
張三男 50
劉二男 30
劉三女 10

對比常規(guī)查詢，可以說我們需要重新定義新的屬性列來展示，所以需要需要通過判斷來完成屬性列的轉換

case

先一步一步的來，既然需要判斷，那么就通過case .. when .. then .. else .. end來

SELECT
	CASE type WHEN 1 THEN value END '姓名',
	CASE type WHEN 2 THEN value END '性別',
	CASE type WHEN 3 THEN value END '年齡'
FROM
	test

看看，最終成了這個德行

再下一步，我們就需要對全部數據進行聚合，根據前面了解到的聚合函數，我們可以選擇使用max()

SELECT
	max(CASE type WHEN 1 THEN value END) '姓名',
	max(CASE type WHEN 2 THEN value END) '性別',
	max(CASE type WHEN 3 THEN value END) '年齡'
FROM
	test
GROUP BY
	t_id;

-- 第二種語法
SELECT
	max(CASE WHEN type = 1 THEN value END) '姓名',
	max(CASE WHEN type = 2 THEN value END) '性別',
	max(CASE WHEN type = 3 THEN value END) '年齡'
FROM
	test
GROUP BY
	t_id;

這樣我們就完成了行轉列，之后如果有遇到這樣的需求，我們也可以使用相同的方式來實現：

主要的是要找到其中數據的規(guī)律

如果單純的只是聚合的話，那么最終只能展示出一條數據，所以這里我們需要進行分組

GROUP BY不了解沒關系，后面我們會詳細聊到

if()

除了采用case之外，還有其他的方式我們來看看

SELECT
	max(if(type = 1, value, '')) '姓名',
	max(if(type = 2, value, '')) '性別',
	max(if(type = 3, value, 0)) '年齡'
FROM
	test
GROUP BY
	t_id

if()表示如果條件滿足，就返回第一個值，否則就返回第二個值

除此之外，如果我們想要給NULL值的數據查詢出默認值，可以通過ifnull()來操作

-- 如果`account`為`null`，那么顯示為0
select ifnull(account, 0) from tb_user;

分頁排序

常規(guī)分頁

現在上面的查詢都是匹配出符合條件的全部數據，如果在實際開發(fā)中數量很大的情況下這種方式很可能會將服務器拖垮，所以這里我們要將數據一頁一頁的顯示出來

在MySQL中，通過limit關鍵字來進行分頁

select * from tb_user limit 0,2

前一個參數表示開始位置，后一個參數表示顯示條數

分頁優(yōu)化

有這么一個場景：MySQL中有2000W的數據，現在要分頁顯示第1000W之后的10條數據，那么通過常規(guī)的方式是這樣的：

select * from tb_user limit 10000000,10

這里我們來說一說limit是如何進行分頁的

limit在分頁的時候會查詢到需要顯示的開始位置，然后丟棄掉查詢出的數據，從那個位置開始，繼續(xù)向后讀取顯示條數的數據
所以說如果開始位置越大，那么需要讀取的數據就越多，查詢時間也就越長

這里給出一個優(yōu)化方案：給定數據的查詢范圍，最好是索引列（索引列可以加快查詢效率）

select * from tb_user where id > 10000000 limit 10;
select * from tb_user where id > 10000000 limit 0 10;

limit后如果只跟一個參數，那么這個參數只表示顯示條數

關聯(lián)查詢

目前我們的查詢都是單表查詢，我們在工作中的查詢SQL基本上都涉及到多表間的操作，這樣我們就需要進行多表關聯(lián)查詢

下面我們再簡單創(chuàng)建一張表，然后再看看如果進行多表關聯(lián)查詢

create table tb_order(
	id bigint primary key auto_increment,
    user_id bigint comment '所屬用戶',
    order_title varchar(50) comment '訂單名稱'
) comment '訂單表';

insert into tb_order(user_id, order_title) values(1, '訂單-1'),(1, '訂單-2'),(1, '訂單-3'),(2, '訂單-4'),(5, '訂單-5'),(7, '訂單-71');

等值查詢

想要進行關聯(lián)查詢的話，SQL是這么操作的

select * from tb_user, tb_order where tb_user.id = tb_order.user_id;

等值查詢也就是說：兩個表中包含相同的列名，在查詢的時候匹配相同列名

對比等值查詢，還存在非等值查詢：兩個表中沒有相同的列名，但是某一個列在另一張表的列的范圍之中

范圍查詢我們已經介紹過了，通過 **between … and …**來查詢

子查詢

所謂的子查詢我們可以理解為：

嵌套在其他SQL語句中的完整SQL語句

還是上面的查詢，我們換一種方式

select * from tb_order where user_id = (select id from tb_user where id = 1);
select * from tb_order where user_id in ( select id from tb_user);

根據子查詢返回結果的不同，子查詢也可以分為不同類型

SQL1只返回了一條數據，而且在查詢的時候通過等值來判斷的，就可以稱為單行子查詢
SQL2很明顯，就是多行子查詢

子查詢除了用在where條件之后，也可以用在顯示列中

select od.*, (select login_name from tb_user where id = od.user_id ) from tb_order od;

左關聯(lián)

左關聯(lián)查詢已left join為主要關鍵點，兩表中的關鍵字段通過on來進行關聯(lián)，通過這種方式查詢出的數據已左側表為主，如果其關聯(lián)的表中不存在數據，那么就返回NULL

select 
	user.*, od.user_id, od.order_title 
from tb_user user 
left join tb_order od on user.id = od.user_id;

右關聯(lián)

右關聯(lián)已right join為主要關鍵點，數據已右側的關聯(lián)表為主，其他的操作方式和左關聯(lián)一樣

select
	user.*, od.user_id, od.order_title
from tb_user user 
right join tb_order od on user.id = od.user_id;

而且可以看出來，在數據的展示上，右側表沒有在左側表有對應數據的話，那么左側表的數據是不會顯示出來的

如果在實際工作中的查詢都是這么簡單的話，簡直不要太舒服

聚合查詢

前面聊到了聚合函數，聚合函數對一組數據執(zhí)行計算，并返回單條值。

很多情況下，如果我們想通過聚合函數對表中數據進行分組操作的話，那么就需要采用group by來進行查詢

就目前表中的數據，我們可以做一個場景：

計算出表中每個登錄賬號有多少條記錄

select count(*), login_name from tb_user group by login_name

其實每個查詢語法的使用都非常簡單

如果想要對聚合查詢出來的數據進行條件篩選，不能使用where來查詢，需要通過having來篩選

select count(*), login_name from tb_user group by login_name having login_name = 'admin1';

還需要注意的是：

當前列沒有通過group by 分組，那么無法通過having來查詢

語法問題

如果我們在操作的時候遇到了這樣的問題：這是由于顯示列中包含沒有分組的列，由sql_mode的模式來決定的。先來查看下默認設置

主要的是語法不規(guī)范

-- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
select @@sql_mode;

set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

根據提示修改就好

總結

到此這篇關于深入學習MySQL表數據操作的文章就介紹到這了,更多相關MySQL表數據操作內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

獲取MySQL的表中每個userid最后一條記錄的方法
這篇文章主要介紹了獲取MySQL的表中每個userid最后一條記錄的方法,并且針對userid不唯一的情況,需要的朋友可以參考下
2015-05-05
MySQL8.0設置遠程訪問權限的方法
這篇文章主要介紹了MySQL8.0設置遠程訪問權限的方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2020-11-11
MySQL性能優(yōu)化神器Explain的基本使用分析
這篇文章主要給大家介紹了關于MySQL性能優(yōu)化神器Explain的基本使用分析，文中通過示例代碼介紹的非常詳細，對大家學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧
2019-08-08
MySql 設置表格字段key中的值
本文主要介紹了MySql 設置表格字段key中的值,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
2024-05-05
Mysql中NTILE()函數的具體使用
NTILE()函數用于將分區(qū)中的有序數據分為n個等級,本文主要介紹了Mysql中NTILE()函數的具體使用,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
2024-07-07
mysql 5.6.23 winx64.zip安裝詳細教程
這篇文章主要介紹了mysql 5.6.23 winx64.zip安裝詳細教程,非常不錯，具有參考借鑒價值，需要的朋友可以參考下
2017-02-02
SQL Server 2005 安裝遇到的錯誤提示和解決方法
在安裝SQL Server 2005時有時會出現意想不到的問題，如IIS，性能計數器，OWC11,無法配置外圍應用的問題，下面筆者分享一下在安裝SQL Server 2005時常見問題解決方法
2014-01-01
MySQL8.0的WITH查詢詳情
這篇文章主要介紹了MySQL的WITH查詢詳情，需要的下伙伴可以參考下文
2021-08-08
mysql CPU高負載問題排查
這篇文章主要介紹了mysql CPU高負載問題排查的相關資料，幫助大家更好的理解和使用MySQL，維護數據庫，感興趣的朋友可以了解下
2020-11-11
mysql數據庫之count()函數和sum()函數用法及區(qū)別說明
這篇文章主要介紹了mysql數據庫之count()函數和sum()函數用法及區(qū)別說明，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2023-06-06