MySQL——查询

查询

创建数据库、数据表

– 创建数据库

create database python_test_1 charset=utf8;

– 使用数据库

use python_test_1;

– students表

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 '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

– classes表

create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);

准备数据
– 向students表中插入数据

insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,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);

– 向classes表中插入数据

insert into classes values (0, "python_01期"), (0, "python_02期");

条件查询

查询大于18岁的

select * from students where age>18

查询小于等于18岁的

select * from students where age<=18

不等于18岁

select * from students where age!=18

逻辑运算 not and or

18岁以上的女同学

select * from students where age>18 and gender = '女';

or 18岁以上 或者 身高超过180的

select * from students where age>18 or height >180;

not 除了18岁以上的女性 之外所有的数据

select * from students where not (age>=18 and gender = '女');

年龄小于等于18 并且是男同学

select * from students where age<=18 and gender = '男';

模糊查询

• like
• %表示任意多个任意字符
• _表示一个任意字符

1 查看名字以’‘小’'开头的数据

select * from students where name like '小%';

2 查看名字为2个字的数据

select * from students where name like '__';

3 rlike 正则

查询以周开头的姓名

select * from students where name rlike '^周';

查询以周开头 以伦结尾 的姓名

select * from students where name rlike '^周.*伦$';

范围查询

1 in

查询年龄为 18 或 34 或 12的数据

select * from students where age=18 or age=34 or age = 12;
select * from students where age in(18,34,12);

注意 18或者34 不是18到34

select * from students where age in(18,34);

2 not in

查询不是 18 、 34 、 12的数据

select * from students where age!=18 and age!=34 and age != 12;
select * from students where not age in(18,34,12);

3 between … and…

查询年龄18到34之间的数据

select * from students where age between 18 and 34;

查询年龄不在18到34之间的数据

• not between … and … 不要写成not (between … and …)

select * from students where age not between 18 and 34;

空判断

• 注意：null与’'是不同的
• 判空is null

查询没有填写身高的学生

select * from students where height is null;

• 判非空is not null

查询填写了身高的学生

select * from students where height is not null;

查询填写了身高的男生

select * from students where height is not null and gender=1;

升序asc ascending

查询18到34的男生，按照年龄从小到大排序

select * from students where (age between 18 and 34) and gender=1 order by age asc;

降序desc descend

查询18到34的女生，按照身高从高到矮排序

select * from students where (age between 18 and 34) and gender=2 order by height desc;

多个字段排序

查询所有数据 按照年龄升序，如果年龄相同 按照身高升序

select * from students order by age asc,height asc;

聚合函数

数量

• count(*)

  所有男同学的数量

  select count(*) from students where gender=1;
  

最大值 max

• 查询最大的年龄

  select max(age) from students;
  

最小值 min

平均值 avg

• 查所有年龄的平均值

  select avg(age) from students;
  
  保留2位小数
  select round(avg(age),2) from students; 
  

求和sum

select sum(age) from students;

group by 分组

• 按性别分组

select gender from students group by gender;

select name from students group by gender; 报错 按gender分组 就只能查gender字段

• 可以使用聚合函数

  按性别分组 
  查询每个性别中 所有年龄的平均值
  select gender,avg(age) from students group by gender;
  

group_concat

• 分组后 有些字段有多个数据，如果查询 需要用group_concat拼接

select gender,group_concat(name) from students group by gender;

select gender,group_concat(name,age) from students group by gender;