MySQL查询

1. 数据的准备

   – 创建一个数据库

   create database test charset=utf8;

   – 使用一个数据库
   use test;

   – 显示当前使用的数据库
   select database();

   – 创建 students 数据表

   create table students(
   	id int unsigned primary key auto_increment not null,
   	name varchar(30) 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");

2. 基本查询

   • – 查询所有字段

     – select * from 表名;

     select * from students;

     select * from classes;

   • – 查询指定字段

     – select 列1, 列2,… from 表名;

     select name, age from students;

     select id, name from classes;

   • – 使用 as 给字段起别名

     – select 字段 as 别名… from 表名;

     select name as 姓名, age as 年龄 from students;

   • – 可以通过 as 给表起别名

     select students.name, students.age from students;

     select s.name, s.age from students as s;

     select s.name, s.age from students s;

     – 起了别名一定得用！select students.name, from students as s;

   • – 消除重复行

     – select distinct 字段 from students;

     select distinct gender from students;

3. 条件查询

   • – 比较运算符

     – select … from 表名 where …

     – 查询大于18岁的信息

     select * from students where age>18;

     – 查询小于18岁的信息

     select * from students where age<18;

     – 还有 >= <= = !=

   • – 逻辑运算符

     – and

     – 18到28之间的所有信息

     select * from students where age>18 and age <28;

     – 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=2);
     – 年龄不是小于或等于18，并且是女性
     select * from students where not age<=18 and gender=2;

   • – 模糊查询

     – like

     – % 替换0个或者多个

     – _ 替换一个

     – 查询姓名中 以“小”开始的名字

     select name from students where name like "小%";

     – 查询姓名中有“小”的所有名字

     select name from students where name like "%小%"

     – 查询有2个字的名字

     select name from students where name like "__";

     – 查询至少有2个字的名字

     select name from students where name like "__%";

     – rlike 正则

     – 查询以“周”开始的姓名

     select name from students where name rlike "^周.*";

     – 查询以“周”开始、“伦”结尾的姓名

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

   • – 范围查询

     – in (1, 3, 8)表示在一个非连续的范围内

     – 查询年龄为 12，18，34的信息

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

     – not in 不在一个非连续的范围内

     – 年龄不是 18，34 岁的信息

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

   • – 空判断

     – 判断空 is null

     – 查询身高为空的信息

     select * from students where height is null;

     – 判断非空 is not null
     select * from students where height is not null;

   • – 排序

     – order by 字段

     – asc：升序排列（默认）

     – desc：降序排列

     – 查询年龄在18到34岁之间的男性，按照年龄升序排列
     select * from students where (age between 18 and 34) and gender=1 order by age;
     – 查询年龄在18到34岁之间的女性，身高从高到低排序
     select * from students where (age between 18 and 34) and gender=2 order by height desc;
     – 查询年龄在18到34岁之间的女性，身高从高到低排序，在身高相同的情况下按照id从大到小排序
     select * from students where (age between 18 and 34) and gender=2 order by height desc, id desc;
     – 按照年龄从小到大、身高从高到低排序
     select * from students order by age, height desc;

4. 聚合函数

   • – 总数 count

     – 查询男性有多少人，女性有多少人

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

     select count(*) as 男性人数 from students where gender=1;

     select count(*) as 女性人数 from students where gender=2;

   • – 最大值 max

     – 查询最大的年龄

     select max(age) from students;

     – 查询女性的最高身高

     select max(height) from students where gender=2;

   • – 最小值 min

     – 参考最大值

   • – 求和 sum

     – 计算所有人的年龄的总和
     select sum(age) from students;

   • – 平均值 avg

     – 计算平均年龄

     select avg(age) from students;

     – 计算平均年龄 sum(age)/count(*)
     select sum(age)/count(*) from students;

   • – 四舍五入 round

     – (123.23, 1) 保留1位小数

     – 计算所有人的平均年龄，保留两位小数

     select round(sum(age)/count(*), 2) from students;

     – 计算男性的平均身高，保留2位小数

     select round(avg(height), 2) from students where gender=1;

5. 分组（必须和聚合函数一起用）

   • – group by

     – 按照性别分组，查询所有的性别

     select gender from students group by gender;

   • – 计算每种性别中的人数

     select gender, count(*) from students group by gender;

     – 计算男性的人数

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

   • – group_concat(…)

     – 查询同种性别中包含哪些人的姓名

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

     select gender, group_concat(name, age, id) from students where gender=1 group by gender;

     select gender, group_concat(name, "_", age, " ", id) from students where gender=1 group by gender;

   • – having

     – 查询平均年龄超过30岁的性别、姓名 having avg(age)>30

     select gender, group_concat(name), avg(age) from students group by gender having avg(age)>30;

     – 查询每种性别中的人数多于2个的信息

     select gender, group_concat(name) from students group by gender having count(*)>2;

6. 分页

   • – limit start, count

     – 限制查询出来的数据个数

     select * from students where gender=1 limit 2;

     – 查询前5个数据

     select * from students limit 0, 5;

     – 查询id6-10的数据

     select * from students limit 5, 5;

   • – 每页显示2个，第1个页面

     select * from students limit 0, 2;
     – 每页显示2个，第2个页面
     select * from students limit 2, 2;
     – 每页显示3个，第3个页面
     select * from students limit 4, 2;
     – 每页显示2个，第4个页面
     select * from students limit 6, 2;
     – 第5个界面，按照年龄进行排序
     select * from students order by age asc limit 8, 2;

   • – 显示女性，id1和id2，按照身高从高到低的顺序排列

     select * from students where gender=2 order by height desc limit 0, 2;

7. 连接查询

   • – select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

     – 使用内连接查询班级表与学生表

     select * from students inner join classes on students.cls_id = classes.id;

   • – 使用左连接查询班级表与学生表

     select * from students as s left join classes as c on s.cls_id = c.id;

   • – 使用右连接查询班级表和学生表

     select * from students as s right join classes as c on s.cls_id = c.id;

   • – 查询学生姓名及班级名称

     select s.name, c.name from students as s inner join classes as c on s.cls_id = c.id;

8. 自关联

   • – 从sql文件中导入数据

     source areas.sql;

     – 查询一共有多少个省

     select count(*) from areas where pid is null;

   • – 例1：查询省的名称为“山西省”的所有城市

     select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省;

     select province.atitle, city.atitle from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';

   • – 例2：查询市的名称为“广州市”的所有区县

     select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle='广州市';

9. 子查询

   – 标量子查询

   – 查询出高于平均身高的信息

   – 查询最高的男生信息

   select * from students where height=188;

   select * from students where height=(select max(height) from students);