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

小结

• 熟悉库表操作及维护更新数据这些基本操作是必备的
• 查询字段: 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 正则非常强大和灵活,而且各语言是通用的, 必须掌握