环境准备：

world库备份下载：网页连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> desc country;
+----------------+--------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field          | Type                                                                                  | Null | Key | Default | Extra |
+----------------+--------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code           | char(3)                                                                               | NO   | PRI |         |       |
| Name           | char(52)                                                                              | NO   |     |         |       |
| Continent      | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO   |     | Asia    |       |
| Region         | char(26)                                                                              | NO   |     |         |       |
| SurfaceArea    | float(10,2)                                                                           | NO   |     | 0.00    |       |
| IndepYear      | smallint(6)                                                                           | YES  |     | NULL    |       |
| Population     | int(11)                                                                               | NO   |     | 0       |       |
| LifeExpectancy | float(3,1)                                                                            | YES  |     | NULL    |       |
| GNP            | float(10,2)                                                                           | YES  |     | NULL    |       |
| GNPOld         | float(10,2)                                                                           | YES  |     | NULL    |       |
| LocalName      | char(45)                                                                              | NO   |     |         |       |
| GovernmentForm | char(45)                                                                              | NO   |     |         |       |
| HeadOfState    | char(60)                                                                              | YES  |     | NULL    |       |
| Capital        | int(11)                                                                               | YES  |     | NULL    |       |
| Code2          | char(2)                                                                               | NO   |     |         |       |
+----------------+--------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)

mysql> desc countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | float(4,1)    | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

DQL查询：

SELECT

(1)作用:获取MySQL中的数据行

(2)单独使用select

1
2
3
select @@xxxx;获取参数信息。
mysql> select @@port;  
mysql> show variables like '%innodb%';  //模糊查询

(3)select 函数();

1
2
3
mysql> select database();  //当前库
mysql> select now();  //当前时间
mysql> select version();  //当前数据库版本

SQL92标准的使用语法

select语法执行顺序（单表）*为必带。

1
2
3
4
5
6
7
8
select开始 ----> 
from子句* --->
where子句---> 
group by子句--->
select后执行条件--->
having子句 ----> 
order by ---->
limit

select 执行顺序

SELECT 语句应用

声明：

  • 以下部分语句仅为演示作用，不代表生产操作。

单表查询练习环境：world数据库下表介绍

SHOW TABLES FROM world;

  • #city(城市): DESC city;
  • #id: 自增的无关列，数据行的需要
  • #NAME: 城市名字
  • #countrycode：城市所在的国家代号，CHN,USA,JPN。。。。
  • #district: 城市的所在的区域，中国是省的意思，美国是洲的意思
  • #population: 城市的人口数量
  • #说明: 此表是历史数据，仅供学习交流使用。

SELECT *

#适合表数据行较少，生产中使用较少。

1
SELECT * FROM city;

#例子：查询name和population的所有值

1
2
SELECT name,Population FROM city;
SELECT NAME,population FROM world.city;

where

(1).WHERE 配合等值查询

#查询中国的城市信息

1
2
3
SELECT * 
FROM world.city 
WHERE CountryCode='CHN';

#查询美国的城市信息

1
2
3
SELECT * 
FROM world.city 
WHERE CountryCode='USA';

#安徽各市人口查看

1
2
3
SELECT name,Population 
FROM world.city 
WHERE District='Anhui';
(2).WHERE 配合不等值查询(> < >= <= != <>)

#查询人口小于等于1000的城市

1
2
3
SELECT * 
FROM world.city 
WHERE Population<=1000;

(3).WHERE 配合模糊(LIKE)

#查询国家代号以C开头的

1
2
3
SELECT * 
FROM world.city 
WHERE CountryCode LIKE 'C%';

#生产中不允许%出现在前面，效率很低。不走索引。

1
2
3
#SELECT * 
FROM world.city 
WHERE CountryCode LIKE '%C%';
(4).WHERE 配合逻辑连接符(AND OR)

#查询城市人口10000-20000的城市。

1
2
3
4
5
6
SELECT * 
FROM world.city 
WHERE Population >= 10000 AND Population <= 20000;
SELECT * 
FROM world.city 
WHERE Population BETWEEN 10000 AND 20000;

#查询中国或美国的城市信息

1
2
3
SELECT * 
FROM world.city 
WHERE CountryCode='CHN' OR CountryCode='USA';`
(5).UNION/UNION ALL

建议改写为（聚合）:

1
2
3
4
5
SELECT * FROM world.city WHERE CountryCode='CHN'
UNION ALL
SELECT * FROM world.city WHERE CountryCode='USA';
#UNION //用于聚合语句输出的结果去重
#UNION ALL //不去重

GROUP BY 配合聚合函数应用

聚合函数：
1
2
3
4
5
6
7
AVG() //计算平均值
COUNT()  //c
SUM()  //所有列之和
MAX() //最大值
MIN()  //最小值
GROUP_CONCAT()  //列转行
   distinct()  //去重
(1):SUM() 所有列之和

#统计一下世界上每个国家的总人口数

1
2
3
SELECT CountryCode,SUM(population) 
FROM world.city 
GROUP BY CountryCode;

#统计一下中国每个省的总人口数

1
2
3
4
SELECT District,SUM(Population) 
FROM world.city 
WHERE CountryCode='CHN' 
GROUP BY District;

#统计一下中国每个省的总人口数

(2):COUNT() 某列下数据行个数

#统计每个国家的城市个数

1
2
3
4
5
6
7
SELECT CountryCode,COUNT(id) FROM world.city GROUP BY CountryCode;
	 #1.拿什么站队
	 GROUP BY CountryCode;  //按国家
	 #2.统计对象
	 城市id或name 
	 #3.统计什么
	 COUNT(id)
(3):GROUP_CONCAT() 列转行

#统计显示每个国家省的名字列表 

1
2
3
SELECT CountryCode,GROUP_CONCAT(district) 
FROM world.city 
GROUP BY CountryCode;

#统计中国每个省的城市名列表

1
2
3
4
SELECT District,GROUP_CONCAT(Name) 
FROM world.city 
WHERE CountryCode='CHN' 
GROUP BY District;
(4):AVG() 计算平均值

#统计一下中国每个省的平均口数

1
2
3
4
SELECT District,AVG(Population) 
FROM world.city 
WHERE CountryCode='CHN' 
GROUP BY District;
(5)HAVING 对结果集进行再次过滤

#中国总人口数大于1000万的

1
2
3
4
5
SELECT District,SUM(Population) 
FROM world.city 
WHERE CountryCode='CHN' 
GROUP BY District
HAVING SUM(Population)>10000000;
(6)ORDER BY 再排序

#中国城市人口数，降序排序

1
2
3
4
SELECT name,Population 
FROM world.city 
WHERE CountryCode='CHN' 
ORDER BY Population DESC;
1
2
3
4
5
6
#中国每个省的人口数，降序排序
 SELECT District,SUM(Population) 
 FROM world.city 
 WHERE CountryCode='CHN' 
 GROUP BY District 
 ORDER BY SUM(Population) DESC;
(7)指定结果集范围：LIMIT
1
2
LIMIT 5,5; #跳过五名，显示五名。
#或者 LIMIT 5 OFFSET 5; 显示5行，跳过五行。

#中国城市人口数，降序排序的前五名

1
2
3
4
5
SELECT name,Population 
FROM world.city 
WHERE CountryCode='CHN' 
ORDER BY Population DESC
LIMIT 5;

#中国城市人口数，降序排序的6-10名

1
2
3
4
SELECT name,Population 
FROM world.city 
WHERE CountryCode='CHN' 
ORDER BY Population DESC
(8)distinct 去重复

#检查某列是否可建立唯一索引
check uniq

(9)将对结果集进行再查询
1
2
3
4
5
select sum(单价*数量) from (select 牌子,单价,数量  from 啤酒 
union all 
select 牌子,单价,数量  from 饮料
union all 
select 牌子,单价,数量  from 矿泉水);

别名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
表别名 
SELECT a.tname  ,GROUP_CONCAT(d.sname) 
FROM teacher AS a 
JOIN course AS b 
ON a.tno = b.tno
JOIN sc as c
ON b.cno = c.cno
JOIN student AS d
ON c.sno = d.sno
WHERE a.tname='oldguo' AND c.score<60
GROUP BY a.tno;

列别名
select count(distinct(name)) as 个数  from world.city;