一道实例让你学会Mysql基础查询(二)

1.接着我们上面那道案例我们继续对Mysql进行深入了解

表中数据也没有变，唯一变的就是加了身高

 2.sql命令

#计算男性和女性的人数
SELECT COUNT(*) FROM first_biao GROUP BY genders;

#显示性别对应的人数
SELECT genders '性别',NAME,COUNT(*) FROM first_biao GROUP BY genders;

#显示所有的姓名
SELECT genders '性别',GROUP_CONCAT(NAME),COUNT(*) FROM first_biao GROUP BY genders;

#总和
SELECT genders,COUNT(*) FROM first_biao GROUP BY genders WITH ROLLUP;

#显示总计
SELECT IFNULL(genders,'总和')AS '性别',COUNT(*) FROM first_biao GROUP BY genders WITH ROLLUP;

#查询组内的年龄姓名
SELECT genders AS '性别',GROUP_CONCAT(NAME,age) FROM first_biao GROUP BY genders;

#让年龄和姓名一一对应
SELECT genders AS '性别',GROUP_CONCAT(NAME,":",age) FROM first_biao GROUP BY genders;

#查询男性,女性,保密总数大于2
SELECT genders,COUNT(*) FROM first_biao GROUP BY genders HAVING COUNT(*)>2;

#查询男性,女性平均年龄超过18岁的性别,以及姓名（大于18并且是男性或者女性）
SELECT genders,AVG(age),GROUP_CONCAT(NAME) FROM first_biao GROUP BY genders HAVING AVG(age)>18 AND (genders=1 OR genders=2);

#添加height字段
ALTER TABLE first_biao ADD height FLOAT;

#查询年龄在18到26之间的男人物,按照年龄从小到大排序
SELECT * FROM first_biao WHERE (age BETWEEN 18 AND 26) AND genders=1 ORDER BY age;

SELECT * FROM first_biao WHERE (age BETWEEN 18 AND 26) AND genders=1 ORDER BY age ASC;

#查询年龄在18到21之间的男人物,排序年龄从大到小排序
SELECT * FROM first_biao WHERE (age BETWEEN 18 AND 21) AND genders=1 ORDER BY age DESC;

#查询年龄在18-23之间的男性,年龄从高到低降序,当年龄相同时,按照身高从高到低排序
SELECT genders,NAME,height,age FROM first_biao WHERE (age BETWEEN 18 AND 23) AND genders=1 ORDER BY age DESC, height DESC;


#limit限制
#显示id=1和2的人物
SELECT * FROM first_biao LIMIT 2;

#显示id为2,3,4,5的人物
SELECT * FROM first_biao LIMIT 1,4;

#一页显示3条数据,一共三页,
SELECT * FROM first_biao LIMIT 0,3;
SELECT * FROM first_biao LIMIT 3,3;
SELECT * FROM first_biao LIMIT 6,3;

#表与表连接
SELECT * FROM first_biao INNER JOIN sececond;

#查询人物中最高身高的男性信息
SELECT * FROM first_biao WHERE height=1.89;

#不确定身高情况下,查询最高男性信息
SELECT MAX(height) FROM first_biao WHERE genders=1;

#显示最高身高还有姓名
SELECT NAME,MAX(height) FROM first_biao WHERE id=2;

#子查询
SELECT NAME,height FROM first_biao WHERE height=(SELECT MAX(height) FROM first_biao WHERE genders=1);

#查询高于平均身高的学生信息
SELECT * FROM first_biao WHERE height>(SELECT AVG(height) FROM first_biao);

#查询年龄最大的男性
SELECT * FROM first_biao WHERE genders=1 AND age=(SELECT MAX(age) FROM first_biao);

#查询最大年龄的男性
SELECT * FROM first_biao WHERE genders=1 AND age=(SELECT MAX(age) FROM first_biao WHERE genders=1);

3.ok，今天就到这里