Mysql 單表查詢where初識 準備數據 數據基本測試 where 條件過濾 比較運算符 , 邏輯運算符, 範圍判斷, 空判斷, 模糊查詢 邏輯運算符: and, or, not Null 判斷 is null; is not null 範圍查詢 in; between...and in 用於離 ...
Mysql 單表查詢where初識
準備數據
-- 創建測試庫
-- drop database if exists student_db;
create database student_db charset=utf8;
use student_db;
-- 創建測試表-學生表
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default "",
age tinyint unsigned default 0,
height decimal(5,2),
gender enum ("男", "女", "未填寫") default "未填寫",
class_id int unsigned default 1,
is_delete bit default 0
);
-- 班級表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(20) not null
);
-- 插入測試數據, 都是偶像, 沒有其他意思哈.
insert into students values
(0,'愛因斯坦',18,180.00,1,1,0),
(0,'居裡夫人',18,180.00,2,2,1),
(0,'小王子',14,185.00,1,1,0),
(0,'李銀河',59,175.00,1,2,1),
(0,'黃蓉',38,160.00,2,1,0),
(0,'冰心',28,150.00,2,2,1),
(0,'王祖賢',18,172.00,2,1,1),
(0,'周傑倫',36,NULL,1,1,0),
(0,'王小波',57,181.00,1,2,0),
(0,'林徽因',25,166.00,2,2,0),
(0,'小星',33,162.00,3,3,1),
(0,'張愛玲',12,180.00,2,4,0),
(0,'馮唐',12,170.00,1,4,0),
(0,'胡適',34,176.00,2,5,0);
insert into classes values
(0, "科學"),
(0, "藝術");
-- 偶像查詢-測試
mysql> select * from students;
+----+----------+-----+--------+--------+----------+-----------+
| id | name | age | height | gender | class_id | is_delete |
+----+----------+-----+--------+--------+----------+-----------+
| 1 | 愛因斯坦 | 18 | 180.00 | 男 | 1 | 0 |
| 2 | 居裡夫人 | 18 | 180.00 | 女 | 2 | 1 |
| 3 | 小王子 | 14 | 185.00 | 男 | 1 | 0 |
| 4 | 李銀河 | 59 | 175.00 | 男 | 2 | 1 |
| 5 | 黃蓉 | 38 | 160.00 | 女 | 1 | 0 |
| 6 | 冰心 | 28 | 150.00 | 女 | 2 | 1 |
| 7 | 王祖賢 | 18 | 172.00 | 女 | 1 | 1 |
| 8 | 周傑倫 | 36 | NULL | 男 | 1 | 0 |
| 9 | 王小波 | 57 | 181.00 | 男 | 2 | 0 |
| 10 | 林徽因 | 25 | 166.00 | 女 | 2 | 0 |
| 11 | 小星 | 33 | 162.00 | 未填寫 | 3 | 1 |
| 12 | 張愛玲 | 12 | 180.00 | 女 | 4 | 0 |
| 13 | 馮唐 | 12 | 170.00 | 男 | 4 | 0 |
| 14 | 胡適 | 34 | 176.00 | 女 | 5 | 0 |
+----+----------+-----+--------+--------+----------+-----------+
14 rows in set (0.08 sec)
mysql> select * from classes;
+----+------+
| id | name |
+----+------+
| 1 | 科學 |
| 2 | 藝術 |
+----+------+
2 rows in set (0.07 sec)數據基本測試
-- 查詢所有欄位
select * from students limt 2;
+----+----------+-----+--------+--------+----------+-----------+
| id | name | age | height | gender | class_id | is_delete |
+----+----------+-----+--------+--------+----------+-----------+
| 1 | 愛因斯坦 | 18 | 180.00 | 男 | 1 | 0 |
| 2 | 居裡夫人 | 18 | 180.00 | 女 | 2 | 1 |
+----+----------+-----+--------+--------+----------+-----------+
-- 查詢指定欄位
select name, age from students limit 2;
+----------+-----+
| name | age |
+----------+-----+
| 愛因斯坦 | 18 |
| 居裡夫人 | 18 |
+----------+-----+
-- as 給查詢集欄位取別名
select name as "姓名", age as "年齡"
from students
where id in (1,2);
+----------+------+
| 姓名 | 年齡 |
+----------+------+
| 愛因斯坦 | 18 |
| 居裡夫人 | 18 |
+----------+------+
-- as 給查詢集表取別名
select s.name, s.age
from students as s
where s.gender = "女";
+----------+-----+
| name | age |
+----------+-----+
| 居裡夫人 | 18 |
| 黃蓉 | 38 |
| 冰心 | 28 |
| 王祖賢 | 18 |
| 林徽因 | 25 |
| 張愛玲 | 12 |
| 胡適 | 34 | -- 故意寫錯的
+----------+-----+
-- 過濾重覆行
select distinct gender
from students
+--------+
| gender |
+--------+
| 男 |
| 女 |
| 未填寫 |
+--------+where 條件過濾
比較運算符, 邏輯運算符, 範圍判斷, 空判斷, 模糊查詢
-- 比較運算符: <, <=, =, >, >=, !=
-- 年齡小於20的信息
-- 年齡小於或等於20歲
select *
from students
where age <= 20;
-- 年齡大於或等於20
select *
from students
where age >= 20;
-- 年齡等於20
select * from students where age = 20;
-- 年齡不等於20
select *
from students
where age !=20;邏輯運算符: and, or, not
-- 年齡在20-30間的學生信息
select *
from students
where (age >= 18) and (age <= 30);
-- 30歲以下的女生
select *
from students
where (age < 30) and (gender = "女");
-- or
-- 身高超過180 或者 年齡在25以上的 男生 姓名和班級
select name, class_id as "班級"
from students
where ((height > 180) or (age > 25))
and (gender = "男");
+--------+------+
| name | 班級 |
+--------+------+
| 小王子 | 1 |
| 李銀河 | 2 |
| 周傑倫 | 1 |
| 王小波 | 2 |
-- not
-- 不在 20歲以上的女生姓名和身高
select name, height
from students
where not (age >= 20 and gender = "女");
Null 判斷 is null; is not null
-- 身高為空值的人的姓名年齡和身高
select name, age, height
from students
where height is null;
+--------+-----+--------+
| name | age | height |
+--------+-----+--------+
| 周傑倫 | 36 | NULL |
+--------+-----+--------+
-- 非空 is not null
select name, age, height
from students
where height is not null;範圍查詢 in; between...and
in 用於離散型, beween...and 用於連續型, 閉區間
-- in, 離散型: 年齡是18, 22, 24, 27 歲的女生姓名和身高
select s.name, s.height
from students s
where (age in (18, 22, 24, 27))
and (gender = "女");
-- 不在, 即改為, not in 即可
+----------+-----+
| name | age |
+----------+-----+
| 居裡夫人 | 18 |
| 王祖賢 | 18 |
+----------+-----+
-- between..and.連續型: 年齡在18到35歲之間的女生姓名及身高
select s.name, s.height
from students as s
where (age between 18 and 35)
and gender = "女";
+----------+--------+
| name | height |
+----------+--------+
| 居裡夫人 | 180.00 |
| 冰心 | 150.00 |
| 王祖賢 | 172.00 |
| 林徽因 | 166.00 |
-- 年齡不在在18到27之間的的女生姓名
select s.name
from students s
where (not (age between 18 and 27))
and gender = "女";
模糊查詢 like, regexp
就通配符和正則表達式兩種形式, 關於正則表達式, 以後再寫吧.
-- like
-- % 替換任意個; _ 替換1個, _ _ 替換2個;
-- 姓名中,以"王"開頭的所有名字及其年齡
select s.name, s.age
from students s
where name like "王%";
+--------+-----+
| name | age |
+--------+-----+
| 王祖賢 | 18 |
| 王小波 | 57 |
+--------+-----+
2 rows in set (0.06 sec)
-- 姓名中, 帶有"王"的所有名字
select s.name
from students s
where s.name like "%王%";
+--------+
| name |
+--------+
| 小王子 |
| 王祖賢 |
| 王小波 |
+--------+
-- 姓名只有2個字的名字
select s.name
from students as s
where s.name like "__";
+------+
| name |
+------+
| 黃蓉 |
| 冰心 |
| 小星 |
| 馮唐 |
| 胡適 |
+------+
5 rows in set (0.05 sec)
-- 姓名最至少有2個字的名字
select s.name
from students s
where s.name like "__%";
-- 靈活: 正則表達式
-- 名字的第二個字是 "王"
select s.name
from students as s
where s.name regexp ".王.*";
+--------+
| name |
+--------+
| 小王子 |
+--------+
1 row in set (0.11 sec)正則表達式還有更加豐富而靈活的用法, 後面有空整吧, 後面再弄一波select的排序呀, group by ..聚合這些常用.
小結
- 熟悉庫表操作及維護更新數據這些基本操作是必備的
- 查詢欄位: select 欄位1, 欄位2 .. from 表名
- where 常用過濾, 常用在**比較運算符, 邏輯運算符, NULL判斷, 模糊查詢, 範圍查詢
- 比較運算符
- <, <=, =, >, >=, <> , !=
- 邏輯運算符
- and
- or
- not
- 優先順序是 ( ) < not < 比較運算符 < and < or
- Null判斷
- is null
- is not null
- 範圍查詢
- in 表示離散型
- between .. and ... ; not (欄位 between...and....)
- 模糊查詢 like
- % 通配符
- _, __,
- %__%
- regexp 正則非常強大和靈活,而且各語言是通用的, 必須掌握
