MySQL基本语句（一）查询语句

查询所有字段

select * from 表名;

查询指定字段

select 列1，列2... from 表名;

使用as给字段起别名

select id as 序号， name as 名字，gender from 表名;

列名后跟as 跟“说明字体”

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

select id, name, gender from students;

–表名，字段名

select students.id, students.name, students.gender from students;

–可以通过as给表起别名

select s.id, s.name, s.gender from students as s;

–消除重复性
–distinct 字段

select distinct gender from students;

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

select * from students where age<18;

–>=
–<=
–查询小于或等于18岁的信息

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

select * from students where age=18;

– != 或者 <>
– 不等于

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

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

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

–18岁以上的女性

select * from students where age>18 and gender=2; 或者是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
– % 替换一个或者多个
– _ 替换一个

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

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

–查询有两个字的名字

select name from students where name like "__";

–查询有三个字的名字

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)表示在一个非联系的范围内

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

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

– not in 不非连续的范围之内
– 年龄不是在 18、34之间的信息

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

–between … and … 表示在一个连续的范围内
– 年龄在18到34之间的信息

select * from students where age between 18 and 34;

–not between … and … 表示不是在一个连续的范围内
–查询 年龄不在18到34岁之间的信息

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

– 空判断
–判空 si null

–查询身高为空的信息

select * from students where height is null;

– 判非空 is not null

select * from students where height is not null;

– 排序
– order by 字段
–desc 从大到小排序 即降序
– asc 从小到大排序 即升序

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

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

– 查询年龄在18到34岁之间的女性，身高从高到矮排序

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

– 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;

– 按照年龄从小到大、身高从高到矮的排序

select * from sutdents order by age asc,height desc;

– 聚合函数
– 总数
– count

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

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.12, 1)保留一位小数
– 计算所有人的平均年龄，保留2位小数 四舍五入

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

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

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

– 分组
– 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;

– having
– 查询每种性别中的人数多余两个的信息

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

– 查询平均年龄超过30岁的性别，以及姓名 having ang(age) > 30

select gender,gruop_concat(name) avg(age) from students gruop by gender having avg(age)>30;

– 分页
– 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个 第一个页面

select * from students limit 0,2;

– 每页显示两个，第二个页面

select * from students limit 2,2;

– 每页显示两个 第三个页面

select * from students limit 4,2;

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

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

select * from students order by age asc limit 10,2;

– 链接查询
– inner join … on

– select *from 表A inner join 表B;

select * from students inner join classes;

– 查询 有能够对应班级的学生以及班级信息

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

– 按照要求显示姓名、班级

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

– 给数据表起名字

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

– 查询 又能对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称

select s.*,c.name 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;

– 查询 有能够对应班级的学生以及班级信息，按照班级进行排序

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

– 当时同一个班级的时候，按照学生的id进行从小到排序

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

– left join

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

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

–查询没有对应班级信息的学生

select s.*,c.name from students as s left join classes as c on s.cls_id=c.id having c.name is null;

– right join
– 将数据表名字互换位置，用left join完成
–自关联

– 查询所有省份

select * from areas where pid is null;

– 查询出山东省有哪些市

select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle="山东省";

– 查询青岛市有哪些县城

select * from areas as city inner join areas as province on city.pid=province.aid where province.title="青岛市";

–子查询
– 标量子查询

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

select * from students where height > (select avg(height) from students);

–查询最高的男生信息

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

– 列级子查询

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

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