Mysql 单表查询where初识

75 阅读 0 评论 50 点赞

我是靠谱客的博主单纯白猫，最近开发中收集的这篇文章主要介绍Mysql 单表查询where初识，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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 ..聚合这些常用.