思享工具箱

MySQL 查询语句

摘要：
一、MySQL查询语句1.DQL介绍2.select语句的应用2-1.select单独使用的情况,不针对任何数据库，查看MySQL的具体参数配置情况2-2.select通用语法(单表)2-3.具体查询实例如下2-3-1.学习环境的说明2-3-2.SELECT配合FROM子句使用2-3-3.SELECT配合WHERE及GROUPBY语句2-3-4.SELECT配合ORDERBY子句2-3-5.SELECT配合LIMIT子句2-3-6.union和unionall2-3-7.多表连接查询(内连接)2-3-8.别名应用一、MySQL查询语句1.DQL介绍MySQL最多的操作就是查询，搜索数据，因此查询语句非常重要，怎样查询效率最高，代价最低，值得好好商榷。
MySQL 查询语句
查询单表查询多表查询DQLSELECTSHOWWHEREGROUP BYHAVINGORDER BYLIMITASUNIONUNION ALLDISTINCT

欢迎来到 来到大浪涛天的博客 ！

一、MySQL 查询语句

1. DQL 介绍

MySQL 最多的操作就是查询，搜索数据，因此查询语句非常重要，怎样查询效率最高，代价最低，值得好好商榷。
如何快速熟悉数据库业务：

  1. 快速和研发人员打好关系
  2. 找到领导要ER图
  3. DESC ,show create table
  4. select * from city limit 5;
    查询语句主要分以下两种：
  • select
  • show

2. select 语句的应用

2-1. select单独使用的情况,不针对任何数据库，查看MySQL的具体参数配置情况

mysql> select @@basedir;
mysql> select @@port;
mysql> select @@innodb_flush_log_at_trx_commit;
mysql> show variables;(查看所有参数)
mysql> show variables like 'innodb%';
mysql> select database();
mysql> select now();

2-2. select 通用语法(单表)

单表查询的语句执行顺序如下：

select  列   
from  表   
where 条件  
group by  条件 
having   条件 
order by 条件
limit

2-3. 具体查询实例如下

2-3-1. 学习环境的说明
  1. world数据库
city           	 城市表
country          国家表  
countrylanguage  国家的语言
  1. city表结构
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)
  1. 各个字段的备注如下：
mysql> 

ID  		:  	城市序号(1-...)
name		: 	城市名字
countrycode :   国家代码,例如:CHN,USA
district    :   区域: 中国 省  美国 洲
population  :   人口数
2-3-2. SELECT 配合 FROM 子句使用
  1. 语法如下
select  列,列,列  from  表
1. 查询表中所有的信息(生产中几乎是没有这种需求的)
USE world ;
SELECT  id,NAME ,countrycode ,district,population   FROM  city;
或者:
SELECT  *   FROM city;

2. 查询表中 name和population的值
SELECT  NAME ,population   FROM  city;
  1. SELECT 配合 WHERE 子句使用
-- select  列,列,列  from  表  where 过滤条件
-- where等值条件查询

例如:

  • 查询中国所有的城市名和人口数
mysql> SELECT Name,Population FROM city WHERE CountryCode='CHN' limit 3;
+-----------+------------+
| Name      | Population |
+-----------+------------+
| Shanghai  |    9696300 |
| Peking    |    7472000 |
| Chongqing |    6351600 |
+-----------+------------+
  1. where 配合比较判断查询(> < >= <=) *****
    例如：
  • 世界上小于100人的城市名和人口数
mysql> SELECT Name,Population FROM city WHERE Population<100;
+-----------+------------+
| Name      | Population |
+-----------+------------+
| Adamstown |         42 |
+-----------+------------+
1 row in set (0.01 sec)
  1. where 配合 逻辑连接符(and or)
    例如：
  • 查询中国人口数量大于800w的城市名和人口
mysql> SELECT Name,Population FROM city WHERE Countrycode='CHN' AND Population>8000000;   
+----------+------------+
| Name     | Population |
+----------+------------+
| Shanghai |    9696300 |
+----------+------------+
1 row in set (0.00 sec)
  • 查询中国或美国的城市名和人口数
mysql> SELECT Name,Population FROM city WHERE Countrycode='CHN' OR Countrycode='USA' limit 3;
+-----------+------------+
| Name      | Population |
+-----------+------------+
| Shanghai  |    9696300 |
| Peking    |    7472000 |
| Chongqing |    6351600 |
+-----------+------------+
3 rows in set (0.00 sec)
  • 查询人口数量在500w到600w之间的城市名和人口数
mysql> SELECT Name,Population FROM city WHERE Population >5000000 AND Population<6000000;
+----------------+------------+
| Name           | Population |
+----------------+------------+
| Rio de Janeiro |    5598953 |
| Tianjin        |    5286800 |
| Kinshasa       |    5064000 |
| Lahore         |    5063499 |
+----------------+------------+
4 rows in set (0.00 sec)

或者用BETWEEN来匹配

mysql> SELECT Name,Population FROM city WHERE Population BETWEEN 5000000 AND 6000000;
+----------------+------------+
| Name           | Population |
+----------------+------------+
| Rio de Janeiro |    5598953 |
| Tianjin        |    5286800 |
| Kinshasa       |    5064000 |
| Lahore         |    5063499 |
+----------------+------------+
  1. where 配合 like 子句 模糊查询
    例如：查询一下contrycode中带有CH开头的城市信息
mysql> SELECT * FROM city WHERE Countrycode LIKE 'CH%' limit 3;
+------+---------+-------------+-------------+------------+
| ID   | Name    | CountryCode | District    | Population |
+------+---------+-------------+-------------+------------+
| 3245 | Zürich  | CHE         | Zürich      |     336800 |
| 3246 | Geneve  | CHE         | Geneve      |     173500 |
| 3247 | Basel   | CHE         | Basel-Stadt |     166700 |
+------+---------+-------------+-------------+------------+

注意:不要出现类似于 %CH%,前后都有百分号的语句,因为不走索引,性能极差,如果业务中有大量需求,我们用"ES"来替代.
6. where 配合 in 语句
例如：查询中国或美国的城市信息.

mysql> SELECT * FROM city WHERE Countrycode IN ('CHN','USA') limit 3;
+------+-----------+-------------+-----------+------------+
| ID   | Name      | CountryCode | District  | Population |
+------+-----------+-------------+-----------+------------+
| 1890 | Shanghai  | CHN         | Shanghai  |    9696300 |
| 1891 | Peking    | CHN         | Peking    |    7472000 |
| 1892 | Chongqing | CHN         | Chongqing |    6351600 |
+------+-----------+-------------+-----------+------------+
3 rows in set (0.01 sec)
或者
mysql> SELECT * FROM city WHERE Countrycode='CHN' OR Countrycode='USA' limit 3;
+------+-----------+-------------+-----------+------------+
| ID   | Name      | CountryCode | District  | Population |
+------+-----------+-------------+-----------+------------+
| 1890 | Shanghai  | CHN         | Shanghai  |    9696300 |
| 1891 | Peking    | CHN         | Peking    |    7472000 |
| 1892 | Chongqing | CHN         | Chongqing |    6351600 |
+------+-----------+-------------+-----------+------------+
3 rows in set (0.00 sec)
2-3-3. SELECT配合WHERE及GROUP BY语句

将某列中有共同条件的数据行,分成一组,然后在进行聚合函数操作.
例如以下语句：

  1. 统计每个国家,城市的个数
mysql> SELECT Countrycode,count(ID) FROM city GROUP BY Countrycode limit 3;
+-------------+-----------+
| Countrycode | count(ID) |
+-------------+-----------+
| ABW         |         1 |
| AFG         |         4 |
| AGO         |         5 |
+-------------+-----------+
  1. 统计每个国家的总人口数.
mysql> SELECT Countrycode,SUM(Population) FROM city GROUP BY Countrycode limit 3;
+-------------+-----------------+
| Countrycode | SUM(Population) |
+-------------+-----------------+
| ABW         |           29034 |
| AFG         |         2332100 |
| AGO         |         2561600 |
+-------------+-----------------+
  1. 统计中国省的个数
mysql> SELECT CountryCode,COUNT(DISTINCT District) FROM city WHERE CountryCode='CHN' GROUP BY CountryCode;  
+-------------+--------------------------+
| CountryCode | COUNT(DISTINCT District) |
+-------------+--------------------------+
| CHN         |                       31 |
+-------------+--------------------------+
1 row in set (0.00 sec)
  1. 统计中国 每个省的总人口数
mysql> SELECT District,SUM(Population) FROM city
    -> WHERE CountryCode='CHN'
    -> GROUP BY District
    -> limit 3;
+-----------+-----------------+
| District  | SUM(Population) |
+-----------+-----------------+
| Anhui     |         5141136 |
| Chongqing |         6351600 |
| Fujian    |         3575650 |
+-----------+-----------------+
  1. 统计中国 每个省城市的个数
mysql> SELECT district,COUNT(ID) FROM city
    -> WHERE countrycode='CHN'
    -> GROUP BY district
    -> limit 3;
+-----------+-----------+
| district  | COUNT(ID) |
+-----------+-----------+
| Anhui     |        16 |
| Chongqing |         1 |
| Fujian    |        12 |
+-----------+-----------+
  1. 统计中国每个省城市的名字列表GROUP_CONCAT()
    guangdong guangzhou,shenzhen,foshan....
mysql> SELECT district,GROUP_CONCAT(Name) FROM city
    -> WHERE countrycode='CHN'
    -> GROUP BY district
    -> limit 3;
+-----------+------------------------------------------------------------------------------------------------------------------------------+
| district  | GROUP_CONCAT(Name)                                                                                                           |
+-----------+------------------------------------------------------------------------------------------------------------------------------+
| Anhui     | Hefei,Huainan,Bengbu,Wuhu,Huaibei,Ma´anshan,Anqing,Tongling,Fuyang,Suzhou,Liu´an,Chuzhou,Chaohu,Xuangzhou,Bozhou,Huangshan   |
| Chongqing | Chongqing                                                                                                                    |
| Fujian    | Fuzhou,Amoy [Xiamen],Nanping,Quanzhou,Zhangzhou,Sanming,Longyan,Yong´an,Fu´an,Fuqing,Putian,Shaowu                           |
+-----------+------------------------------------------------------------------------------------------------------------------------------+
  1. 扩展统计中国每个省城市的名字列表,按下列要求展现出来
    anhui : hefei,huaian ....
mysql> SELECT CONCAT(district,":",GROUP_CONCAT(Name)) FROM city
    -> WHERE countrycode='CHN'
    -> GROUP BY district
    -> limit 3;
+------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT(district,":",GROUP_CONCAT(Name))                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------+
| Anhui:Hefei,Huainan,Bengbu,Wuhu,Huaibei,Ma´anshan,Anqing,Tongling,Fuyang,Suzhou,Liu´an,Chuzhou,Chaohu,Xuangzhou,Bozhou,Huangshan   |
| Chongqing:Chongqing                                                                                                                |
| Fujian:Fuzhou,Amoy [Xiamen],Nanping,Quanzhou,Zhangzhou,Sanming,Longyan,Yong´an,Fu´an,Fuqing,Putian,Shaowu                          |
+------------------------------------------------------------------------------------------------------------------------------------+
2-3-4. SELECT 配合 ORDER BY 子句

ORDER BY子句会自动将上述语句排序，默认是从小到大排序，如果加上DESC后会从大到小排序。另外如果在GROUP BY分组后的数据统计过滤不能使用WHERE来过滤了，必须使用HAVING（功能和WHERE是一致的，但是摆放顺序不一样）
例如：统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从小到大顺序排列

mysql> SELECT countrycode,SUM(population) FROM city
    -> GROUP BY countrycode
    -> HAVING SUM(population)>50000000
    -> ORDER BY SUM(population) 
    -> LIMIT 10;
+-------------+-----------------+
| countrycode | SUM(population) |
+-------------+-----------------+
| MEX         |        59752521 |
| RUS         |        69150700 |
| JPN         |        77965107 |
| USA         |        78625774 |
| BRA         |        85876862 |
| IND         |       123298526 |
| CHN         |       175953614 |
+-------------+-----------------+
2-3-5. SELECT 配合 LIMIT 子句

LIMIT的优先级最低，所以摆放在最后，功能是限制打印的行数，OFFSET是指偏离，OFFSET为0时只不偏离。
LIMIT M,N :跳过M行,显示一共N行
LIMIT Y OFFSET X: 跳过X行,显示一共Y行
例如：

  1. 统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列,只显示前三名
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC 
LIMIT 3 OFFSET 0;

如果现实四到六名，则可以采用OFFSET 3来实现，意思是limit3到了3行后再向后面偏离3行，前面的不显示，就正好是四五六名，如下：

mysql> SELECT countrycode,SUM(population) FROM city 
    -> GROUP BY countrycode
    -> HAVING SUM(population)>5000000
    -> ORDER BY SUM(population) DESC
    -> LIMIT 3 OFFSET 3;
+-------------+-----------------+
| countrycode | SUM(population) |
+-------------+-----------------+
| USA         |        78625774 |
| JPN         |        77965107 |
| RUS         |        69150700 |
+-------------+-----------------+
  1. 统计中国每个省的总人口数，只打印总人口数小于100w的
mysql> SELECT district,SUM(population) FROM city 
    -> WHERE countrycode='CHN'
    -> GROUP BY district
    -> HAVING SUM(population) <1000000;
+----------+-----------------+
| district | SUM(population) |
+----------+-----------------+
| Hainan   |          557120 |
| Ningxia  |          802362 |
| Qinghai  |          700200 |
| Tibet    |          120000 |
+----------+-----------------+
  1. 查看中国所有的城市，并按人口数进行排序(从大到小)
mysql> SELECT name,population FROM city
    -> WHERE countrycode='CHN'
    -> ORDER BY population DESC;
+---------------------+------------+
| name                | population |
+---------------------+------------+
| Shanghai            |    9696300 |
| Peking              |    7472000 |
| Chongqing           |    6351600 |
| Tianjin             |    5286800 |
  1. 统计中国各个省的总人口数量，按照总人口从大到小排序
mysql> SELECT district,SUM(population) FROM city
    -> WHERE countrycode='CHN'
    -> GROUP BY district
    -> ORDER BY SUM(population) DESC;
+----------------+-----------------+
| district       | SUM(population) |
+----------------+-----------------+
| Liaoning       |        15079174 |
| Shandong       |        12114416 |
| Heilongjiang   |        11628057 |
| Jiangsu        |         9719860 |
  1. 统计中国,每个省的总人口,找出总人口大于500w的,
    并按总人口从大到小排序,只显示前三名
mysql> SELECT district,SUM(population) FROM city
    -> WHERE countrycode='CHN'
    -> GROUP BY district
    -> HAVING SUM(population)>5000000
    -> ORDER BY SUM(population) DESC
    -> LIMIT 3;
+--------------+-----------------+
| district     | SUM(population) |
+--------------+-----------------+
| Liaoning     |        15079174 |
| Shandong     |        12114416 |
| Heilongjiang |        11628057 |
+--------------+-----------------+
2-3-6. union 和 union all

作用: 多个结果集合并查询的功能，常用语语句改写，因为OR和IN性能太差了
union all 不做去重复
union 会做去重操作
例如：查询中或者美国的城市信息

mysql> SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA' LIMIT 3;
+------+-----------+-------------+-----------+------------+
| ID   | Name      | CountryCode | District  | Population |
+------+-----------+-------------+-----------+------------+
| 1890 | Shanghai  | CHN         | Shanghai  |    9696300 |
| 1891 | Peking    | CHN         | Peking    |    7472000 |
| 1892 | Chongqing | CHN         | Chongqing |    6351600 |
+------+-----------+-------------+-----------+------------+

mysql> DESC SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA' LIMIT 3;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | range | CountryCode   | CountryCode | 3       | NULL |  637 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)


mysql>  SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA' LIMIT 3;    
+------+-----------+-------------+-----------+------------+
| ID   | Name      | CountryCode | District  | Population |
+------+-----------+-------------+-----------+------------+
| 1890 | Shanghai  | CHN         | Shanghai  |    9696300 |
| 1891 | Peking    | CHN         | Peking    |    7472000 |
| 1892 | Chongqing | CHN         | Chongqing |    6351600 |
+------+-----------+-------------+-----------+------------+

mysql> DESC SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA' LIMIT 3;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
|  2 | UNION       | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  274 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
2-3-7. 多表连接查询(内连接)

单表数据不能满足查询需求时.
例如下列学生成绩统计信息表查询
查询环境如下：

student ：学生表
===============
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
================
tno：     教师编号
tname：教师名字

course ：课程表
===============
cno：  课程编号
cname：课程名字
tno：  教师编号

score  ：成绩表
==============
sno：  学号
cno：  课程编号
score：成绩

=====================
创建过程如下
=====================
mysql> create table student(
    -> sno int primary key not null default 0 auto_increment comment '学号',
    -> sname varchar(250) not null default 0 comment '学生姓名',
    -> sage tinyint not null default 0 comment '学生年龄',
    -> ssex enum('男','女','中') default '中' comment '学生性别')engine innodb charset utf8mb4 comment '学生表';
mysql> desc student;
+-------+-------------------------+------+-----+---------+----------------+
| Field | Type                    | Null | Key | Default | Extra          |
+-------+-------------------------+------+-----+---------+----------------+
| sno   | int(11)                 | NO   | PRI | NULL    | auto_increment |
| sname | varchar(250)            | NO   |     | 0       |                |
| sage  | tinyint(4)              | NO   |     | 0       |                |
| ssex  | enum('男','女','中')    | YES  |     | 中      |                |
+-------+-------------------------+------+-----+---------+----------------+

mysql> create table teacher(
    -> tno int primary key not null auto_increment comment '教师编号',
    -> tname varchar(250) not null default 0 comment '教师名字')engine innodb charset utf8mb4 comment '教师表';
Query OK, 0 rows affected (0.01 sec)
mysql> 
mysql> desc teacher;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| tno   | int(11)      | NO   | PRI | NULL    | auto_increment |
| tname | varchar(250) | NO   |     | 0       |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> create table course(
    -> cno int primary key not null auto_increment comment '课程编号',
    -> cname varchar(250) not null comment '课程名字',
    -> tno int not null comment '教师编号')engine innodb charset utf8mb4 comment '课程表';
Query OK, 0 rows affected (0.01 sec)

mysql> create table score(
    -> sno int not null comment '学生编号',
    -> cno int not null comment '课程编号',
    -> score int not null comment '学生成绩')engine innodb charset utf8mb4 comment '成绩表';
Query OK, 0 rows affected (0.01 sec)

mysql> desc course;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| cno   | int(11)      | NO   | PRI | NULL    | auto_increment |
| cname | varchar(250) | NO   |     | NULL    |                |
| tno   | int(11)      | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc score;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sno   | int(11) | NO   |     | NULL    |       |
| cno   | int(11) | NO   |     | NULL    |       |
| score | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

mysql> INSERT INTO student(sno,sname,sage,ssex) VALUES (1,'zhang3',18,'男');
mysql> INSERT INTO student(sno,sname,sage,ssex)
    -> VALUES
    -> (2,'zhang4',18,'男'),
    -> (3,'li4',18,'男'),
    -> (4,'wang5',19,'中');
Query OK, 3 rows affected (0.01 sec)
mysql> INSERT INTO student
    -> VALUES
    -> (5,'zh4',18,'男'),
    -> (6,'zhao4',18,'男'),
    -> (7,'ma6',19,'中');
Query OK, 3 rows affected (0.00 sec)
mysql> INSERT INTO student(sname,sage,ssex)
    -> VALUES
    -> ('oldboy',20,'男'),
    -> ('oldgirl',20,'女'),
    -> ('oldp',25,'女');
mysql> INSERT INTO teacher(tno,tname) VALUES
    -> (101,'oldboy'),
    -> (102,'hesw'),
    -> (103,'oldguo');
Query OK, 3 rows affected (0.00 sec)
mysql> INSERT INTO course(cno,cname,tno)
    -> VALUES
    -> (1001,'linux',101),
    -> (1002,'python',102),
    -> (1003,'mysql',103);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> INSERT INTO score(sno,cno,score)
    -> VALUES
    -> (1,1001,80),
    -> (1,1002,59),
    -> (2,1002,90),
    -> (2,1003,100),
    -> (3,1001,99),
    -> (3,1003,40),
    -> (4,1001,79),
    -> (4,1002,61),
    -> (4,1003,99),
    -> (5,1003,40),
    -> (6,1001,89),
    -> (6,1003,77),
    -> (7,1001,67),
    -> (7,1003,82),
    -> (8,1001,70),
    -> (9,1003,80),
    -> (10,1003,96);
Query OK, 17 rows affected (0.00 sec)
mysql> select * from student;
+-----+---------+------+------+
| sno | sname   | sage | ssex |
+-----+---------+------+------+
|   1 | zhang3  |   18 | 男   |
|   2 | zhang4  |   18 | 男   |
|   3 | li4     |   18 | 男   |
|   4 | wang5   |   19 | 中   |
|   5 | zh4     |   18 | 男   |
|   6 | zhao4   |   18 | 男   |
|   7 | ma6     |   19 | 中   |
|   8 | oldboy  |   20 | 男   |
|   9 | oldgirl |   20 | 女   |
|  10 | oldp    |   25 | 女   |
+-----+---------+------+------+
mysql> select * from teacher;
+-----+--------+
| tno | tname  |
+-----+--------+
| 101 | oldboy |
| 102 | hesw   |
| 103 | oldguo |
+-----+--------+
3 rows in set (0.00 sec)
mysql> select * from course;
+------+--------+-----+
| cno  | cname  | tno |
+------+--------+-----+
| 1001 | linux  | 101 |
| 1002 | python | 102 |
| 1003 | mysql  | 103 |
+------+--------+-----+
3 rows in set (0.00 sec)
mysql> select * from score;
+-----+------+-------+
| sno | cno  | score |
+-----+------+-------+
|   1 | 1001 |    80 |
|   1 | 1002 |    59 |
|   2 | 1002 |    90 |
|   2 | 1003 |   100 |
|   3 | 1001 |    99 |
|   3 | 1003 |    40 |
|   4 | 1001 |    79 |
|   4 | 1002 |    61 |
|   4 | 1003 |    99 |
|   5 | 1003 |    40 |
|   6 | 1001 |    89 |
|   6 | 1003 |    77 |
|   7 | 1001 |    67 |
|   7 | 1003 |    82 |
|   8 | 1001 |    70 |
|   9 | 1003 |    80 |
|  10 | 1003 |    96 |
+-----+------+-------+
  1. 统计zhang3,学习了几门课
mysql> SELECT student.sname,COUNT(score.cno) FROM student
    -> JOIN score ON student.sno=score.sno
    -> WHERE student.sname='zhang3';
+--------+------------------+
| sname  | COUNT(score.cno) |
+--------+------------------+
| zhang3 |                2 |
+--------+------------------+
1 row in set (0.01 sec)

-- 2. 查询zhang3,学习的课程名称有哪些?

mysql> SELECT student.sname,GROUP_CONCAT(course.cname) FROM student
    -> JOIN score ON student.sno=score.sno
    -> JOIN course ON score.cno=course.cno
    -> WHERE student.sname='zhang3'
    -> GROUP BY student.sname;
+--------+----------------------------+
| sname  | GROUP_CONCAT(course.cname) |
+--------+----------------------------+
| zhang3 | linux,python               |
+--------+----------------------------+

如果要串连方式显示可以这样：

mysql> SELECT CONCAT(student.sname,":",GROUP_CONCAT(course.cname)) FROM student
    -> JOIN score ON student.sno=score.sno
    -> JOIN course ON score.cno=course.cno
    -> WHERE student.sname='zhang3'
    -> GROUP BY student.sname;
+------------------------------------------------------+
| CONCAT(student.sname,":",GROUP_CONCAT(course.cname)) |
+------------------------------------------------------+
| zhang3:linux,python                                  |
+------------------------------------------------------+
1 row in set (0.02 sec)
  1. 查询oldguo老师教的学生名和个数.
mysql> SELECT teacher.tname,GROUP_CONCAT(student.sname),count(student.sno) FROM teacher
    -> JOIN course ON teacher.tno=course.tno
    -> JOIN score ON course.cno=score.cno
    -> JOIN student ON score.sno=student.sno
    -> WHERE teacher.tname='oldguo'
    -> GROUP BY teacher.tname;
+--------+---------------------------------------------+------------------+
| tname  | GROUP_CONCAT(student.sname)                 | count(score.sno) |
+--------+---------------------------------------------+------------------+
| oldguo | zhang4,li4,wang5,zh4,zhao4,ma6,oldgirl,oldp |                8 |
+--------+---------------------------------------------+------------------+
  1. 查询oldguo所教课程的平均分数
mysql> SELECT teacher.tname,AVG(score.score) FROM teacher
    -> JOIN course ON teacher.tno=course.tno
    -> JOIN score ON course.cno=score.cno
    -> WHERE teacher.tname='oldguo'
    -> GROUP BY teacher.tname;
+--------+------------------+
| tname  | AVG(score.score) |
+--------+------------------+
| oldguo |          76.7500 |
+--------+------------------+
  1. 每位老师所教课程的平均分,并按平均分排序
mysql> SELECT teacher.tname,AVG(score.score) FROM teacher 
    -> JOIN course ON teacher.tno=course.tno    
    -> JOIN score ON course.cno=score.cno
    -> GROUP BY teacher.tname;
+--------+------------------+
| tname  | AVG(score.score) |
+--------+------------------+
| hesw   |          70.0000 |
| oldboy |          80.6667 |
| oldguo |          76.7500 |
+--------+------------------+
  1. 查询oldguo所教的不及格的学生姓名
mysql> SELECT teacher.tname,GROUP_CONCAT(student.sname) FROM teacher
    -> JOIN course ON teacher.tno=course.tno
    -> JOIN score ON course.cno=score.cno
    -> JOIN student ON score.sno=student.sno
    -> WHERE score.score <60
    -> AND teacher.tname='oldguo'
    -> GROUP BY teacher.tname;
+--------+-----------------------------+
| tname  | GROUP_CONCAT(student.sname) |
+--------+-----------------------------+
| oldguo | li4,zh4                     |
+--------+-----------------------------+
  1. 查询lodguo所教不及格学生姓名及分数
mysql> SELECT teacher.tname,student.sname,score.score FROM teacher
    -> JOIN course ON teacher.tno=course.tno
    -> JOIN score ON course.cno=score.cno
    -> JOIN student ON score.sno=student.sno
    -> WHERE score.score <60
    -> AND teacher.tname='oldguo';

+--------+-------+-------+
| tname  | sname | score |
+--------+-------+-------+
| oldguo | li4   |    40 |
| oldguo | zh4   |    40 |
+--------+-------+-------+
  1. 查询所有老师所教学生不及格的信息
mysql> SELECT teacher.tname,GROUP_CONCAT(CONCAT(student.sname,":",score.score)) FROM teacher
    -> JOIN course ON teacher.tno=course.tno
    -> JOIN score ON course.cno=score.cno
    -> JOIN student ON score.sno=student.sno
    -> WHERE score.score <60
    -> GROUP BY teacher.tname;
+--------+-----------------------------------------------------+
| tname  | GROUP_CONCAT(CONCAT(student.sname,":",score.score)) |
+--------+-----------------------------------------------------+
| hesw   | zhang3:59                                           |
| oldguo | li4:40,zh4:40                                       |
+--------+-----------------------------------------------------+

注意:GROUP_CONCAT是分组后的整合，而CONCAT是直接串连，例如我们上面这个例子，我们要显示出不及格同学的名字和分数，我们必须先把显示个格式串联出来，然后再分组整合
所以是GROUP_CONCAT(CONCAT(student.sname,":",score.score))。

2-3-8. 别名应用

表别名，在表处用as后加上替代的别名符号，列别名是指在列名后带上as加上替代的别名符号，表别名是全局调用的，列别名可以被having 和 order by 调用，如下事例
查询所有老师所教学生不及格的信息
表别名:

mysql> SELECT t.tname,GROUP_CONCAT(CONCAT(st.sname,":",s.score)) FROM teacher as t
    -> JOIN course as c ON t.tno=c.tno
    -> JOIN score as s ON c.cno=s.cno
    -> JOIN student as st ON s.sno=st.sno
    -> WHERE s.score <60
    -> GROUP BY t.tno;
+--------+--------------------------------------------+
| tname  | GROUP_CONCAT(CONCAT(st.sname,":",s.score)) |
+--------+--------------------------------------------+
| hesw   | zhang3:59                                  |
| oldguo | li4:40,zh4:40                              |
+--------+--------------------------------------------+

列别名:

mysql> SELECT t.tname as '教师名',GROUP_CONCAT(CONCAT(st.sname,":",s.score)) as '不及格的同学' FROM teacher as t
    -> JOIN course as c ON t.tno=c.tno
    -> JOIN score as s ON c.cno=s.cno
    -> JOIN student as st ON s.sno=st.sno
    -> WHERE s.score <60
    -> GROUP BY t.tno;
+-----------+--------------------+
| 教师名    | 不及格的同学       |
+-----------+--------------------+
| hesw      | zhang3:59          |
| oldguo    | li4:40,zh4:40      |
+-----------+--------------------+

标签：#mysql | 浏览：135 | 发布日期：

免责声明：文章转载自《MySQL 查询语句》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇Spring MVC-表单（Form）标签-单选按钮集合（RadioButtons）示例（转载实践）imei 算法下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

Linux下C连接MySql数据库

目录： 一、解决小的问题； 二、大问题，如果你不小心把/usr/lib的所属用户改了导致sudo命令用不了： 三、C连接MySql编程本身： 其实写这个程序真的很简单，十多分钟的事情，只是以前没在Linux下用纯C连过Mysql，想试一下，竟然搞了一整天，而且不是由于编码本身，是因为其他的一些周边问题，所以很有必要做一下笔记。 一、解决小的问题： 1、...

使用homebrew安装mysql

2.使用homebrew安装mysql及遇到的问题 安装命令 brew install mysql 安装完成之后，本地命令行输入mysql命令，发现无此命令 commond not found 首先，检查是否是安装了 重新执行一遍 brew install mysql 命令行提示： Warning: mysql-5.7.10 already in...

Mac安装Mysql

目录 一、安装 二、环境变量 2.1 MySQL服务的启停和状态的查看 三、启动 四、初始化设置 4.1 退出sql界面 五、配置 5.1 检测修改结果 一、安装 第一步：打开网址，https://www.mysql.com ，点击downloads之后跳转到https://www.mysql.com/downloads 选择Community选...

ORM操作mysql

创建表和添加数据import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy.o...

product of大数据平台搭建------CM 和CDH安装

一、安装说明       CM是由cloudera公司提供的大数据组件自动部署和监控管理工具，相应的和CDH是cloudera公司在开源的hadoop社区版的基础上做了商业化的封装的大数据平台。        采用离线安装模式，虽然在线安装比较简单，但是速度感人，原因大家都懂，这里我使用的各软件版本信息为： 操作系统：CentOS6.8 CDH版本：5.7...

MYSQL8.0+ 使用JDBC查询中文乱码的问题

在建表时，附加一句  DROP TABLE IF EXISTS `sys_table`;CREATE TABLE `sys_table` ( ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8;set names gb2312; navicat 如果出现中文乱码，可以查看mysql的字符集和navicat字符集是否统一，使用 ...

最新文章

随机推荐

思享工具箱导航