深入學(xué)習(xí)MySQL表數(shù)據(jù)操作
前言
這一節(jié)我們基于表來做數(shù)據(jù)方面的操作,同樣的,無非就是C(創(chuàng)建)R(讀取)U(修改)D(刪除)四種操作,但是在R上總能玩出多種花樣
正式開始操作之前,我們先來聊一聊它們的關(guān)鍵字:
- INSERT
- SELECT
- UPDATE
- DELETE
大家可以先通過help
命令來查看一下相關(guān)的語法,提前預(yù)習(xí)一下,方便更深的理解
正式上菜
先來看看之前的表結(jié)構(gòu):
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 '用戶表';
插入數(shù)據(jù)
在插入之前,我們先來看看平常怎么使用的
insert into table_name[(column_name[,column_name] ...)] value|values (value_list) [, (value_list)]
其實(shí)最常用的就這么多,下面我們來舉個(gè)例子就明白了
全部字段插入單條數(shù)據(jù)
insert into tb_user value(1, 'admiun', 'abc123456', 2000, inet_aton('127.0.0.1'));
這樣就插入了一條數(shù)據(jù):
auto_increment
:自增鍵,在插入數(shù)據(jù)的時(shí)候可以不給當(dāng)前列指定數(shù)據(jù),而且默認(rèn)情況下我們推薦給主鍵設(shè)置自增inet_aton
:ip轉(zhuǎn)換函數(shù),相對(duì)應(yīng)的還有inet_ntoa()
而且還需要注意一點(diǎn),如果存在相同的主鍵,那么在插入的時(shí)候會(huì)出現(xiàn)錯(cuò)誤
# 主鍵已重復(fù) Duplicate entry '4' for key 'tb_user.PRIMARY'
指定字段插入多條數(shù)據(jù)
insert into tb_user(login_name, login_pwd) values('admin1', 'abc123456'),('admin2', 'abc123456')
可以看到數(shù)據(jù)已經(jīng)插入進(jìn)來,沒有填充數(shù)據(jù)的列已NULL
填充,關(guān)于這一點(diǎn),我們可以在創(chuàng)建表的時(shí)候通過DEFAULT
來指定默認(rèn)值,就是在這個(gè)時(shí)候使用的
alter table tb_user add column email varchar(50) default 'test@sina.com' comment '郵箱'
沒有什么比實(shí)際動(dòng)手有說服力的了
ON DUPLICATE KEY UPDATE
這里還有一個(gè)點(diǎn),用到的不是很多,但是相當(dāng)實(shí)用:ON DUPLICATE KEY UPDATE
也就是說如果數(shù)據(jù)表中存在重復(fù)的主鍵,那么就進(jìn)行更新操作,來看:
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);
對(duì)比上面的數(shù)據(jù),很容易就會(huì)發(fā)現(xiàn)數(shù)據(jù)不一樣了
values(列名)
: 會(huì)取出前面插入的字段的數(shù)據(jù)
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);
插入多條數(shù)據(jù)也是一樣的,就不貼圖了,大家自己動(dòng)手試一下
修改數(shù)據(jù)
插入數(shù)據(jù)相對(duì)而言比較簡單,下面我們來看看修改數(shù)據(jù)
首先從update
語法上來講,這個(gè)更簡單:
update table_name set column_name=value_list (,column_name=value_list) where condition
舉個(gè)栗子:
update tb_user set login_name = 'super@sina.com' where id = 1
這樣就修改了tb_user
下編號(hào)為1的loign_name
的數(shù)據(jù)
where
后條件也可以多個(gè),按照,
分割
當(dāng)然,如果沒有設(shè)置查詢條件的話,那么默認(rèn)是會(huì)修改整張表的數(shù)據(jù)
update tb_user set login_name = 'super@sina.com',account = 2000
好了,修改數(shù)據(jù)到這里就結(jié)束了,很簡單
刪除數(shù)據(jù)
刪除數(shù)據(jù)分為:
- 刪除指定數(shù)據(jù)
- 清空整張表
如果只是想刪除某些數(shù)據(jù),可以通過delete
來刪除,還是來舉個(gè)栗子:
delete from tb_user where login_ip is null;
這樣就刪除了指定條件的數(shù)據(jù)
那么,如果我們執(zhí)行刪除條件,但是不設(shè)置條件呢?下面我們來看一看
先執(zhí)行
insert
操作插入幾條數(shù)據(jù)
delete from tb_user ;
可以看到,刪除了全部的數(shù)據(jù)
但其實(shí)還有一種方式可以清空整張表,就是通過truncate
的方式,這種方式的效率更高
truncate tb_user;
最后就不貼圖了,肯定沒問題的
查詢數(shù)據(jù)
查詢數(shù)據(jù)分為多種情況,組合使用可以有N中存在,所以說這是最復(fù)雜的一種方式,下面我們一一來介紹
其實(shí)如果從語法上來看:查詢語法關(guān)鍵點(diǎn)只會(huì)包含如下幾點(diǎ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]
記住這些關(guān)鍵點(diǎn),查詢就相當(dāng)簡單了,下面我們先來看個(gè)簡單的操作
簡單查詢
select * from tb_user; -- 按照指定字段排序 asc: 正序 desc: 倒序 select * from tb_user order by id desc;
一共插入了44條數(shù)據(jù),沒有全部截圖
當(dāng)前SQL會(huì)查詢出表中全部數(shù)據(jù),而跟在select
后面的*
表示:列出全部的字段,如果我們只是想列出某些列的話,那么將它換成指定的字段名就好:
select id, login_name, login_pwd from tb_user;
就是這么簡單
當(dāng)然了,還記得這個(gè)關(guān)鍵字么:DISTINCT,我們來實(shí)驗(yàn)一下:
select distinct login_name from tb_user;
意思已經(jīng)很明顯了,沒錯(cuò),就是去重操作。
但是我要告訴大家的是,distinct
關(guān)鍵字如果作用在多個(gè)字段的話,那么只有在多個(gè)字段組合的情況下重復(fù)才會(huì)進(jìn)行生效,舉個(gè)栗子:
select distinct id,login_name from tb_user;
只有在 id + login_name有重復(fù)的時(shí)候會(huì)生效
聚合函數(shù)
在MySQL中內(nèi)置的聚合函數(shù),對(duì)一組數(shù)據(jù)執(zhí)行計(jì)算,并返回單條值,在特殊場(chǎng)景下有特殊的作用
可以加where條件
-- 查詢當(dāng)前表中的數(shù)據(jù)條數(shù) select count(*) from tb_user; -- 查詢當(dāng)前表中指定列最大的一條 select max(id) from tb_user; -- 查詢當(dāng)前表中指定列最小的一條 select min(id) from tb_user; -- 查詢當(dāng)前表中指定列的平均值 select avg(account) from tb_user; -- 查詢當(dāng)前表中指定列的總和 select sum(account) from tb_user;
除了聚合函數(shù)之外,還包含很多普通函數(shù),這里就不一一列舉了,給出 官方文檔,用的時(shí)候具體查
條件查詢
看到了第一個(gè)例子是不是感覺其實(shí)查詢沒有那么難。上面的例子都是查詢出全部數(shù)據(jù),下面我們要加一些條件進(jìn)行篩選,這里就用到了我們的where
語句,記住一點(diǎn):
- 條件篩選是可以有多個(gè)的
等值查詢
我們可以通過如下方式進(jìn)行條件判斷
select * from tb_user where login_name = 'admin1' and login_pwd = 'abc123456';
很多情況下,column_name = column_value
是我們用到更多的查詢方式,這種方式我們可以稱為等值查詢,
而且注意到,在條件之前我是通過and
來進(jìn)行關(guān)聯(lián)的,Java
基礎(chǔ)不錯(cuò)的小伙伴肯定也記得&&
,都是表示并且的意
既然有and
,那么與之相反的肯定就是or
了,表示只要兩者滿足其中一條就好
select * from tb_user where login_name = 'admin1' or login_pwd = 'abc123456';
除了=
匹配的方式,還有其他更多的方式,<
,<=
,>
,>=
- 和我們認(rèn)知中不一樣的是:
<>
表示不等于
不過這些使用方式都是一樣的
批量查詢
在某些特定的情況下,如果想要查詢出一批數(shù)據(jù),可以通過in
來進(jìn)行查詢
select * from tb_user where id in(1,2,3,4,5,6);
在in
中,相當(dāng)于傳入的是一個(gè)集合,然后查詢指定集合的數(shù)據(jù),在很多情況下,這條sql還可以這么寫
select * from tb_user where id in ( select id from tb_user where login_name = 'admin1' );
除了in
,還有not in
與之相反:表示要查詢出來的不包含這些指定的數(shù)據(jù)
模糊查詢
看完了等值查詢
,我們?cè)賮砜匆粋€(gè)模糊查詢
:
- 只要字段數(shù)據(jù)中包含查詢的數(shù)據(jù),就能夠匹配到數(shù)據(jù)
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
就是我們模糊查詢中的關(guān)鍵成員,而后面的查詢關(guān)鍵字分為三種情況:
- %admin%:%夾著查詢關(guān)鍵字表示只要數(shù)據(jù)中包含
admin
就能匹配到 - %admin: 任意關(guān)鍵字開頭,只要是admin結(jié)尾的數(shù)據(jù)都能匹配到
- admin%:必須是admin開頭,其他的隨意,這樣的數(shù)據(jù)就能匹配到
更多的推薦采用這種方式,如果查詢列設(shè)置了索引的話,其他方式會(huì)讓索引失效
非空判斷
查詢當(dāng)前表會(huì)發(fā)現(xiàn),數(shù)據(jù)中的某些列是NULL
值,如果我們?cè)诓樵冞^程中向要過濾掉這些數(shù)據(jù),我們可以這么做:
select * from tb_user where account is not null; select * from tb_user where account is null;
is not null
就是其中的關(guān)鍵點(diǎn),與之相對(duì)的還有is null
,意思正好相反
時(shí)間判斷
很多情況下,如果我們想要通過時(shí)間段來匹配查詢,那么我們可以這樣做:
tb_user表沒有時(shí)間字段,這里添加了一個(gè)字段:create_time
select * from tb_user where create_time between '2021-04-01 00:00:00' and now();
- **now()**函數(shù)表示當(dāng)前時(shí)間
between
之后表示開始時(shí)間,and
之后表示結(jié)束時(shí)間
行轉(zhuǎn)列
我從一個(gè)面試題來聊一聊這個(gè)查詢吧:
場(chǎng)景是一樣的,但是SQL不一樣 (關(guān)注重點(diǎn),看題)
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');
請(qǐng)寫出一條SQL展示如下結(jié)果:
姓名 性別 年齡
--------- -------- ----
張三 男 50
劉二 男 30
劉三 女 10
對(duì)比常規(guī)查詢,可以說我們需要重新定義新的屬性列來展示,所以需要需要通過判斷來完成屬性列的轉(zhuǎn)換
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
看看,最終成了這個(gè)德行
再下一步,我們就需要對(duì)全部數(shù)據(jù)進(jìn)行聚合,根據(jù)前面了解到的聚合函數(shù),我們可以選擇使用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;
這樣我們就完成了行轉(zhuǎn)列,之后如果有遇到這樣的需求,我們也可以使用相同的方式來實(shí)現(xiàn):
- 主要的是要找到其中數(shù)據(jù)的規(guī)律
如果單純的只是聚合的話,那么最終只能展示出一條數(shù)據(jù),所以這里我們需要進(jìn)行分組
GROUP BY
不了解沒關(guān)系,后面我們會(huì)詳細(xì)聊到
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()
表示如果條件滿足,就返回第一個(gè)值,否則就返回第二個(gè)值
除此之外,如果我們想要給NULL
值的數(shù)據(jù)查詢出默認(rèn)值,可以通過ifnull()
來操作
-- 如果`account`為`null`,那么顯示為0 select ifnull(account, 0) from tb_user;
分頁排序
常規(guī)分頁
現(xiàn)在上面的查詢都是匹配出符合條件的全部數(shù)據(jù),如果在實(shí)際開發(fā)中數(shù)量很大的情況下這種方式很可能會(huì)將服務(wù)器拖垮,所以這里我們要將數(shù)據(jù)一頁一頁的顯示出來
在MySQL中,通過limit
關(guān)鍵字來進(jìn)行分頁
select * from tb_user limit 0,2
前一個(gè)參數(shù)表示開始位置
,后一個(gè)參數(shù)表示顯示條數(shù)
分頁優(yōu)化
有這么一個(gè)場(chǎng)景:MySQL
中有2000W的數(shù)據(jù),現(xiàn)在要分頁顯示第1000W之后的10條數(shù)據(jù),那么通過常規(guī)的方式是這樣的:
select * from tb_user limit 10000000,10
這里我們來說一說limit
是如何進(jìn)行分頁的
limit
在分頁的時(shí)候會(huì)查詢到需要顯示的開始位置,然后丟棄掉查詢出的數(shù)據(jù),從那個(gè)位置開始,繼續(xù)向后讀取顯示條數(shù)的數(shù)據(jù)- 所以說如果開始位置越大,那么需要讀取的數(shù)據(jù)就越多,查詢時(shí)間也就越長
這里給出一個(gè)優(yōu)化方案:給定數(shù)據(jù)的查詢范圍,最好是索引列(索引列可以加快查詢效率)
select * from tb_user where id > 10000000 limit 10; select * from tb_user where id > 10000000 limit 0 10;
limit
后如果只跟一個(gè)參數(shù),那么這個(gè)參數(shù)只表示顯示條數(shù)
關(guān)聯(lián)查詢
目前我們的查詢都是單表查詢,我們?cè)诠ぷ髦械牟樵僑QL基本上都涉及到多表間的操作,這樣我們就需要進(jìn)行多表關(guān)聯(lián)查詢
下面我們?cè)俸唵蝿?chuàng)建一張表,然后再看看如果進(jìn)行多表關(guān)聯(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');
等值查詢
想要進(jìn)行關(guān)聯(lián)查詢的話,SQL是這么操作的
select * from tb_user, tb_order where tb_user.id = tb_order.user_id;
等值查詢也就是說:兩個(gè)表中包含相同的列名,在查詢的時(shí)候匹配相同列名
對(duì)比等值查詢,還存在非等值查詢:兩個(gè)表中沒有相同的列名,但是某一個(gè)列在另一張表的列的范圍之中
范圍查詢我們已經(jīng)介紹過了,通過 **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);
根據(jù)子查詢返回結(jié)果的不同,子查詢也可以分為不同類型
- SQL1只返回了一條數(shù)據(jù),而且在查詢的時(shí)候通過等值來判斷的,就可以稱為單行子查詢
- SQL2很明顯,就是多行子查詢
子查詢除了用在where條件之后,也可以用在顯示列中
select od.*, (select login_name from tb_user where id = od.user_id ) from tb_order od;
左關(guān)聯(lián)
左關(guān)聯(lián)查詢已left join
為主要關(guān)鍵點(diǎn),兩表中的關(guān)鍵字段通過on
來進(jìn)行關(guān)聯(lián),通過這種方式查詢出的數(shù)據(jù)已左側(cè)表為主,如果其關(guān)聯(lián)的表中不存在數(shù)據(jù),那么就返回NULL
select user.*, od.user_id, od.order_title from tb_user user left join tb_order od on user.id = od.user_id;
右關(guān)聯(lián)
右關(guān)聯(lián)已right join
為主要關(guān)鍵點(diǎn),數(shù)據(jù)已右側(cè)的關(guān)聯(lián)表為主,其他的操作方式和左關(guā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;
而且可以看出來,在數(shù)據(jù)的展示上,右側(cè)表沒有在左側(cè)表有對(duì)應(yīng)數(shù)據(jù)的話,那么左側(cè)表的數(shù)據(jù)是不會(huì)顯示出來的
如果在實(shí)際工作中的查詢都是這么簡單的話,簡直不要太舒服
聚合查詢
前面聊到了聚合函數(shù),聚合函數(shù)對(duì)一組數(shù)據(jù)執(zhí)行計(jì)算,并返回單條值。
很多情況下,如果我們想通過聚合函數(shù)對(duì)表中數(shù)據(jù)進(jìn)行分組操作的話,那么就需要采用group by
來進(jìn)行查詢
就目前表中的數(shù)據(jù),我們可以做一個(gè)場(chǎng)景:
- 計(jì)算出表中每個(gè)登錄賬號(hào)有多少條記錄
select count(*), login_name from tb_user group by login_name
其實(shí)每個(gè)查詢語法的使用都非常簡單
如果想要對(duì)聚合查詢出來的數(shù)據(jù)進(jìn)行條件篩選,不能使用where
來查詢,需要通過having
來篩選
select count(*), login_name from tb_user group by login_name having login_name = 'admin1';
還需要注意的是:
- 當(dāng)前列沒有通過
group by
分組,那么無法通過having
來查詢
語法問題
如果我們?cè)诓僮鞯臅r(shí)候遇到了這樣的問題:這是由于顯示列中包含沒有分組的列,由sql_mode
的模式來決定的。先來查看下默認(rèn)設(shè)置
主要的是語法不規(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';
根據(jù)提示修改就好
總結(jié)
到此這篇關(guān)于深入學(xué)習(xí)MySQL表數(shù)據(jù)操作的文章就介紹到這了,更多相關(guān)MySQL表數(shù)據(jù)操作內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL數(shù)據(jù)庫安全設(shè)置與注意事項(xiàng)小結(jié)
現(xiàn)在很多朋友使用mysql數(shù)據(jù)庫,為了安全考慮我們就需要考慮到mysql的安全問題,例如需要將mysql以普通用戶權(quán)限運(yùn)行,就算出問題了有了root也不能控制系統(tǒng)2013-08-08查看 MySQL 已經(jīng)運(yùn)行多長時(shí)間的方法
查看MySQL啟動(dòng)時(shí)間以及運(yùn)行了多長時(shí)間的方法有利用show與updtime或在linux中直接使用grep mysql 相關(guān)參數(shù)來查看,下面我們一起來看看2014-01-01如何利用MySQL查詢varbinary中存儲(chǔ)的數(shù)據(jù)
varbinary 類型和char與varchar類型是相似的,他們是包含字節(jié)流而不是字符流,他們有二進(jìn)制字符的集合和順序,他們的對(duì)比,排序是基于字節(jié)的數(shù)值進(jìn)行的,本文給大家介紹如何利用MySQL查詢varbinary中存儲(chǔ)的數(shù)據(jù),感興趣的朋友一起看看吧2023-07-07Mysql數(shù)據(jù)庫百萬級(jí)數(shù)據(jù)測(cè)試索引效果
這篇文章主要為大家介紹了Mysql數(shù)據(jù)庫百萬數(shù)據(jù)測(cè)試索引效果,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2022-06-06mysql數(shù)據(jù)插入覆蓋和時(shí)間戳的問題及解決
這篇文章主要介紹了mysql數(shù)據(jù)插入覆蓋和時(shí)間戳的問題及解決,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-03-03