| 運算符 | 說明 |
|---|---|
| >, >=, , = | 大于，大于等于，小于，小于等于 |
| = | 等于，NULL 不安全，例如 NULL = NULL 的結(jié)果是 NULL |
| => | 等于，NULL 安全，例如 NULL => NULL 的結(jié)果是 TRUE(1) |
| !=, > | 不等于 |
| BETWEEN a0 AND a1 | 范圍匹配，[a0, a1]，如果 a0 = value = a1，返回 TRUE(1) |
| IN (option, …) | 如果是 option 中的任意一個，返回 TRUE(1) |
| IS NULL | 是 NULL |
| IS NOT NULL | 不是 NULL |
| LIKE | 模糊匹配。% 表示任意多個（包括 0 個）任意字符；_ 表示任意一個字符 |
邏輯運算符:
| 運算符 | 說明 |
|---|---|
| AND | 多個條件必須都為 TRUE(1)，結(jié)果才是 TRUE(1) |
| OR | 任意一個條件為 TRUE(1), 結(jié)果為 TRUE(1) |
| NOT | 條件為 TRUE(1)，結(jié)果為 FALSE(0) |
注意:
實例:
基本查詢：
-- 查詢英語不及格的同學及英語成績 ( 60 ) select name, english from exam_result where english 60; -- 查詢語文成績好于英語成績的同學 select name, chinese, english from exam_result where chinese > english; -- 查詢總分在 200 分以下的同學 select name, chinese + math + english as total from exam_result where chinese + math + english 200;
AND 與 OR：
-- 查詢語文成績大于80分，且英語成績大于80分的同學 select * from exam_result where chinese > 80 and english > 80; -- 查詢語文成績大于80分，或英語成績大于80分的同學 select * from exam_result where chinese > 80 or english > 80;
關(guān)于優(yōu)先級問題, and 比 or 更優(yōu)先,
范圍查詢:
1.BETWEEN … AND …
-- 查詢語文成績在 [80, 90] 分的同學及語文成績 select name, chinese from exam_result where chinese BETWEEN 80 AND 90; select name, chinese, from exam_result where chinese >= 80 and chinese = 90;
IN
-- 查詢數(shù)學成績是 58 或者 59 或者 98 或者 99 分的同學及數(shù)學成績 select name, math from exam_result where math in (58, 59, 98, 99);
模糊查詢: LIKE
select name from exam_result where name like 't%'; +------+ | name | +------+ | tom | | tim | +------+
% 是一個通配符, 可以用來代替任意多個字符
t% 找出以 t 開頭的字符串
%t 找出以 t 結(jié)尾的字符串
%t% 找出包含 t 的
除了 % 之外, 還有 _ ，（_ 只能代表一個字符~)
select name from exam_result where name like 't__'; +------+ | name | +------+ | tom | | tim | +------+
通配符也能針對數(shù)字進行模糊查詢
select name, chinese from exam_result where chinese like '%8%'; +------+---------+ | name | chinese | +------+---------+ | jum | 87.5 | | lim | 88.0 | | tim | 82.0 | +------+---------+
注意:
模糊查詢看起來比較好用, 實際執(zhí)行效率低下
NULL 的查詢: IS [NOT] NULL
select name from exam_result where id id not null;
2.8 分頁查詢: LIMIT
-- 最初數(shù)據(jù)表 select * from exam_result; +------+------+---------+------+---------+ | id | name | chinese | math | english | +------+------+---------+------+---------+ | 1 | tom | 67.0 | 98.0 | 56.0 | | 2 | jum | 87.5 | 78.0 | 77.0 | | 3 | lim | 88.0 | 98.0 | 90.0 | | 4 | tim | 82.0 | 84.0 | 67.0 | | 5 | huy | 55.5 | 85.0 | 45.0 | | 6 | sun | 70.0 | 73.0 | 78.5 | | 7 | ming | 75.0 | 65.0 | 30.0 | +------+------+---------+------+---------+ -- 前三條記錄 select * from exam_result limit 3; +------+------+---------+------+---------+ | id | name | chinese | math | english | +------+------+---------+------+---------+ | 1 | tom | 67.0 | 98.0 | 56.0 | | 2 | jum | 87.5 | 78.0 | 77.0 | | 3 | lim | 88.0 | 98.0 | 90.0 | +------+------+---------+------+---------+ -- 從第三條開始的三條記錄 select * from exam_result limit 3 offset 3; +------+------+---------+------+---------+ | id | name | chinese | math | english | +------+------+---------+------+---------+ | 4 | tim | 82.0 | 84.0 | 67.0 | | 5 | huy | 55.5 | 85.0 | 45.0 | | 6 | sun | 70.0 | 73.0 | 78.5 | +------+------+---------+------+---------+
offset 表示從第幾條開始查找, offset 可以省略
select * from exam_result limit 3 , 4; +------+------+---------+------+---------+ | id | name | chinese | math | english | +------+------+---------+------+---------+ | 4 | tim | 82.0 | 84.0 | 67.0 | | 5 | huy | 55.5 | 85.0 | 45.0 | | 6 | sun | 70.0 | 73.0 | 78.5 | | 7 | ming | 75.0 | 65.0 | 30.0 | +------+------+---------+------+---------+
– 將總成績倒數(shù)前三的 3 位同學的數(shù)學成績加上 30 分
update exam_result set math = math + 30 order by chinese + math + english limit 3;
update 不加條件, 就可以針對所有
delete from [表名];
-- 刪除 ming 同學的考試成績 delete from exam_result where name = 'ming'; -- 刪除整張表 delete from exam_result;
如果不指定條件, 此時就把整個表刪除掉了, (與 drop 刪除表還有不同)
delete 刪除后表為 null, drop 刪除后表就不存在了
-- 單行插入 insert into [表名] (字段1, ..., 字段N) values (value1, ...,value N); -- 多行插入 insert into [表名](字段1, ..., 字段N) values (value1, ...), (value2, ...), (value3, ...);
查詢
--全表查詢 select * from [表名]; --指定列查詢 select [列名1, 列名2,...] from [表名]; --查詢表達式字段 select [表達式1, 表達式2,...] from [表名]; --別名 select --去重 DISTINCT select distinct [字段] from [表名]; -- 排序ORDER BY select * from [表名] order by [排序字段]; -- 條件查詢WHERE -- (1)比較運算符 (2)BETWEEN ... AND ... (3)IN (4)IS NULL (5)LIKE (6)AND (7)OR (8)NOT select * from [表名] where [條件];
修改
update [表] set [修改內(nèi)容1, 修改內(nèi)容2, ....] where [條件];
刪除
delete from [表名] where [條件];
到此這篇關(guān)于MySQL表增刪改查的文章就介紹到這了,更多相關(guān)MySQL表增刪改查內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
