第三章 sql查询

单表查询

1.ORDER BY

（1）将英雄按防御值从低到高排序

SELECT * FROM hero
ORDER BY defense ASC;

（2）将蜀国英雄按攻击值从高到低排序

SELECT * FROM hero 
WHERE country="蜀国"
ORDER BY attack DESC;

（3）将魏蜀两国中名字为三个字的英雄按防御值升序排序

DELECT * FROM hero
WHERE country="蜀国" OR country="魏国" AND name LIKE "___"
ORDER BY attack DESC;

2.LIMIT

（1）在蜀国英雄中，查找防御值倒数第二名至倒数第四名的英雄记录

SELECT * FROM hero
WHERE country ="蜀国"
ORDER BY defense DESC
LIMIT 1,3;

（2）在蜀国英雄中，查找攻击值前3名且名字不为NULL的英雄的名字，攻击值和国家

SELECT name,attack,country FROM hero
WHERE country="蜀国" AND name IS NOT NULL
ORDER BY attack DESC
LIMIT 3;

3.聚合函数

（1）所有英雄中，攻击力的最强值是多少

SELECT MAX(attack) FROM hero;

（2）计算蜀国英雄的总攻击力

SELECT SUM(attack) FROM hero
WHERE country="蜀国";

（3）统计id,name 两个字段分别有几条记录

SELECT COUNT(id),COUNT(name) FROM hero;

（4）统计蜀国英雄中攻击力>200的英雄的数量

SELECT COUNT(*) FROM hero
WHERE country="蜀国" AND attack > 200;

4.分组

（1）查询表中有哪些国家

SELECT country FROM hero
GROUP BY country;

（2）计算每个国家的平均攻击力

SELECT country,AVG(attack) FROM hero
GROUP BY country;

（3）查找所有国家中英雄数量最多的前2名的国家名称和英雄数量

SELECT country,count(id) FROM hero
GROUP BY country 
ORDER BY count(id) DESC
LIMIT 2;

结果：

5.HAVING

（1）找出平均攻击力>105的国家的前2名，显示国家名和平均攻击力

SELECT country,AVG(attack) FROM hero
GROUP BY country 
HAVING AVG(attack)>105
ORDER BY AVG(attack) DESC
LIMIT 2;

6.DISTINCT

（1）表中都有哪些国家

SELECT DISTINCT country FROM hero;

（2）计算表中一共有几个国家

SELECT COUNT(DISTINCT country) AS cou_num FROM hero;

7.查询表记录时做数学运算

（1）查询所有英雄的id,name,attack字段，并在查询时将所有英雄的攻击力翻倍

SELECT id,name,attack*2 FROM hero