| 運算符 | 說明 |
|---|---|
| >, >=, , = | 大于，大于等于，小于，小于等于 |
| = | 等于，NULL 不安全，例如 NULL = NULL 的結果是 NULL |
| => | 等于，NULL 安全，例如 NULL => NULL 的結果是 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)，結果才是 TRUE(1) |
| OR | 任意一個條件為 TRUE(1), 結果為 TRUE(1) |
| NOT | 條件為 TRUE(1)，結果為 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;
關于優先級問題, and 比 or 更優先,
范圍查詢:
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
-- 查詢數學成績是 58 或者 59 或者 98 或者 99 分的同學及數學成績 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 結尾的字符串
%t% 找出包含 t 的
除了 % 之外, 還有 _ ，（_ 只能代表一個字符~)
select name from exam_result where name like 't__'; +------+ | name | +------+ | tom | | tim | +------+
通配符也能針對數字進行模糊查詢
select name, chinese from exam_result where chinese like '%8%'; +------+---------+ | name | chinese | +------+---------+ | jum | 87.5 | | lim | 88.0 | | tim | 82.0 | +------+---------+
注意:
模糊查詢看起來比較好用, 實際執行效率低下
NULL 的查詢: IS [NOT] NULL
select name from exam_result where id id not null;
2.8 分頁查詢: LIMIT
-- 最初數據表 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 | +------+------+---------+------+---------+
– 將總成績倒數前三的 3 位同學的數學成績加上 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 [修改內容1, 修改內容2, ....] where [條件];
刪除
delete from [表名] where [條件];
到此這篇關于MySQL表增刪改查的文章就介紹到這了,更多相關MySQL表增刪改查內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
