查询所有字段

    select * from 表名;
    1
    查询指定字段

    select 列1，列2… from 表名;
    1
    使用as给字段起别名

    select id as 序号， name as 名字，gender from 表名;
    1
    列名后跟as 跟“说明字体”

    可以通过as给表起别名
    –如果是单表查询，可以省略表名

    select id, name, gender from students;
    1
    –表名，字段名

    select students.id, students.name, students.gender from students;
    1
    –可以通过as给表起别名

    select s.id, s.name, s.gender from students as s;
    1
    –消除重复性
    –distinct 字段

    select distinct gender from students;
    1
    –条件查询
    –比较运算符
    –select …from 表名 where…
    –>
    –查询小于或等于18岁的信息

    select * from students where age<18;
    1
    –>=
    –<=
    –查询小于或等于18岁的信息

    – =
    –查询年龄为18岁的所有学生的名字

    select * from students where age=18;
    1
    – != 或者 <>
    – 不等于

    –逻辑运算符
    – and
    – 18到28岁之间所有学生的信息

    select from students where age>18 and age<28;
    1
    – 失败select     from students where age>18 and <28;

    –18岁以上的女性

    select * from students where age>18 and gender=2; 或者是gender=”女”;
    1
    – or
    –18岁以上或者身高超过180（包含）以上

    select * from students where age>18 or height>=180;
    1
    –not
    –不在18岁以上的女性，这个范围内的信息

    select * from students where not (age>18 and gender=2);
    1
    –年龄不是小于或者等于18 并且是女性

    select * from students where not (age<=18 and gender=2);
    1
    –模糊查询

    –like
    – % 替换一个或者多个
    – _ 替换一个

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

    select name from students where name like “小%”;
    1
    –查询有两个字的名字

    select name from students where name like “__”;
    1
    –查询有三个字的名字

    select name from students where name like “_“;
    1
    – 查询至少有2个字的名字

    select name from students where name like “__%”;
    1
    – 正则 rlike
    – 查询以 周开始的名字

    select name from students where name rlike “^周”;
    1
    –查询以 周开始 、伦结尾的名字

    select name from students where name rlike “^周.*伦$”;
    1
    – 范围查询
    – in(1, 3, 8)表示在一个非联系的范围内

    –查询年龄在18、34的名字

    select name from students where age in(18, 34);
    1
    – not in 不非连续的范围之内
    – 年龄不是在 18、34之间的信息

    select name,age from students where age not in (18, 34);
    1
    –between … and … 表示在一个连续的范围内
    – 年龄在18到34之间的信息

    select * from students where age between 18 and 34;
    1
    –not between … and … 表示不是在一个连续的范围内
    –查询 年龄不在18到34岁之间的信息

    select * from students where age not between 18 and 34;
    1
    – 空判断
    –判空 si null

    –查询身高为空的信息

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

    select * from students where height is not null;
    1
    – 排序
    – order by 字段
    –desc 从大到小排序 即降序,查看表结构的详细信息（desc table_name;）
    – asc 从小到大排序 即升序

    – 查询年龄在18到24岁之间的男性，按照年龄从小到大排序

    select * from students where (age between 18 and 24) and gender = 1 order by age asc;
    1
    – 查询年龄在18到34岁之间的女性，身高从高到矮排序

    select * from students where (age betweed 18 and 34) an gender = 2 order by desc;
    1
    – order by 多个字段
    – 查询年龄在18到34岁之间的女性，身高从高到低排序，如果身高相同的情况下按照年龄从小到大排序

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

    –查询年龄在18到34之间的女性，身高从高到矮排序，如果身高相同的情况下按照年龄从小到大排序，
    –如果年龄也相同那么按照id从大到小排序

    select * from students where (age between 18 and 34) and gender=2 order by height desc, age asc,id desc;
    1
    – 按照年龄从小到大、身高从高到矮的排序

    select * from sutdents order by age asc,height desc;
    1
    – 聚合函数
    – 总数
    – count

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

    select count() as 男性人数 from students where gender=1;
    select count(    ) as 女性人数 from students where gender=2;
    1
    2
    –最大值
    –max
    – 查询最大的年龄

    select max(age) from students;
    1
    –查询女性的最高 身高

    select max(height) from students where gender=2;
    1
    –最小值
    – min

    –求和
    – sum
    – 计算所有人的年龄总和

    select sum(age) from students;
    1
    – 平均值
    – avg
    – 计算平均年龄

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

    select sum(age)/count(*) from students;
    1
    – round(123.12, 1)保留一位小数
    – 计算所有人的平均年龄，保留2位小数 四舍五入

    select round (sum(age)/count(*), 2) from students;
    1
    – 计算男性的平均身高，保留2位小数

    select round(avg(height), 2) from students where gender=1;
    1
    – 分组
    – group by

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

    select gender from students group by gender;
    1
    – 计算每种性别中的人数

    select gender,count(*) from students group by gender;
    1
    – 计算男性的人数

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

    – 查询同种性别中的姓名

    select gender,group_concat(name) from students where gender=1 group by gender;
    1
    – having
    – 查询每种性别中的人数多余两个的信息

    select gender,group_concat(name) from students group by gender having count(*)>2;
    1
    – 查询平均年龄超过30岁的性别，以及姓名 having ang(age) > 30

    select gender,gruop_concat(name) avg(age) from students gruop by gender having avg(age)>30;
    1
    – 分页
    – limit start, count

    – 限制查询出来的数据格式

    select * from students where gender=1 limit 2;
    1
    – 查询前5个数据

    select * from students limit 0,5;
    1
    – 查询id6- 10（包含）的书序

    select * from students limit 5,5;
    1
    – 每页显示2个 第一个页面

    select * from students limit 0,2;
    1
    – 每页显示两个，第二个页面

    select * from students limit 2,2;
    1
    – 每页显示两个 第三个页面

    select * from students limit 4,2;
    1
    – 每页显示两个 第四个页面
    select from students limit 6,2; – ———-> limit (第N页-1)每个的个数，每页的个数

    – 每页显示2个，显示第6页的信息，按照年龄从小到大排序

    select * from students order by age asc limit 10,2;
    1
    – 链接查询
    – inner join … on

    – select *from 表A inner join 表B;

    select * from students inner join classes;
    1
    – 查询 有能够对应班级的学生以及班级信息

    select * from students inner join classes on students.cli_id=classes.id;
    1
    – 按照要求显示姓名、班级

    select students.name, classes.name from students inner join classes on students.cls_id=classes.id;
    1
    – 给数据表起名字

    select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;
    1
    – 查询 又能对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称

    select s.*,c.name from students as s inner join classes as c on s.cls_id=c.id;
    1
    – 在以上的查询中，将班级姓名显示在第1列

    select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id;
    1
    – 查询 有能够对应班级的学生以及班级信息，按照班级进行排序

    select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id;
    1
    – 当时同一个班级的时候，按照学生的id进行从小到排序

    select c.name, s.* from students as sinner join classes as c on s.cls_id=c.id order by c.name;
    1
    – left join

    – 查询美味学生对应的班级信息

    select * from students as s left join classes as c on s.cli_id=c.id;
    1
    –查询没有对应班级信息的学生

    select s.*,c.name from students as s left join classes as c on s.cls_id=c.id having c.name is null;
    1
    – right join
    – 将数据表名字互换位置，用left join完成
    –自关联

    – 查询所有省份

    select * from areas where pid is null;
    1
    – 查询出山东省有哪些市

    select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle=”山东省”;
    1
    – 查询青岛市有哪些县城

    select * from areas as city inner join areas as province on city.pid=province.aid where province.title=”青岛市”;
    1
    –子查询
    – 标量子查询

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

    select * from students where height > (select avg(height) from students);
    1
    –查询最高的男生信息

    select * from students where height = (select max(height) from students);
    1
    – 列级子查询

    – 查询学生的班级号能够对应的学生信息

    select * from students where cls_id in (select id from classes);

    原文链接：https://blog.csdn.net/Hair_ball/article/details/100539003