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 正则非常强大和灵活,而且各语言是通用的, 必须掌握