MySQL 查询语句
MySQL 查询语句
欢迎来到 来到大浪涛天的博客 !
一、MySQL 查询语句
1. DQL 介绍
MySQL 最多的操作就是查询,搜索数据,因此查询语句非常重要,怎样查询效率最高,代价最低,值得好好商榷。
如何快速熟悉数据库业务:
- 快速和研发人员打好关系
- 找到领导要ER图
- DESC ,show create table
- 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. 学习环境的说明
- world数据库
city 城市表
country 国家表
countrylanguage 国家的语言
- 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)
- 各个字段的备注如下:
mysql>
ID : 城市序号(1-...)
name : 城市名字
countrycode : 国家代码,例如:CHN,USA
district : 区域: 中国 省 美国 洲
population : 人口数
2-3-2. SELECT 配合 FROM 子句使用
- 语法如下
select 列,列,列 from 表
1. 查询表中所有的信息(生产中几乎是没有这种需求的)
USE world ;
SELECT id,NAME ,countrycode ,district,population FROM city;
或者:
SELECT * FROM city;
2. 查询表中 name和population的值
SELECT NAME ,population FROM city;
- 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 |
+-----------+------------+
- where 配合比较判断查询(> < >= <=) *****
例如:
- 世界上小于100人的城市名和人口数
mysql> SELECT Name,Population FROM city WHERE Population<100;
+-----------+------------+
| Name | Population |
+-----------+------------+
| Adamstown | 42 |
+-----------+------------+
1 row in set (0.01 sec)
- 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 |
+----------------+------------+
- 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语句
将某列中有共同条件的数据行,分成一组,然后在进行聚合函数操作.
例如以下语句:
- 统计每个国家,城市的个数
mysql> SELECT Countrycode,count(ID) FROM city GROUP BY Countrycode limit 3;
+-------------+-----------+
| Countrycode | count(ID) |
+-------------+-----------+
| ABW | 1 |
| AFG | 4 |
| AGO | 5 |
+-------------+-----------+
- 统计每个国家的总人口数.
mysql> SELECT Countrycode,SUM(Population) FROM city GROUP BY Countrycode limit 3;
+-------------+-----------------+
| Countrycode | SUM(Population) |
+-------------+-----------------+
| ABW | 29034 |
| AFG | 2332100 |
| AGO | 2561600 |
+-------------+-----------------+
- 统计中国省的个数
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)
- 统计中国 每个省的总人口数
mysql> SELECT District,SUM(Population) FROM city
-> WHERE CountryCode='CHN'
-> GROUP BY District
-> limit 3;
+-----------+-----------------+
| District | SUM(Population) |
+-----------+-----------------+
| Anhui | 5141136 |
| Chongqing | 6351600 |
| Fujian | 3575650 |
+-----------+-----------------+
- 统计中国 每个省城市的个数
mysql> SELECT district,COUNT(ID) FROM city
-> WHERE countrycode='CHN'
-> GROUP BY district
-> limit 3;
+-----------+-----------+
| district | COUNT(ID) |
+-----------+-----------+
| Anhui | 16 |
| Chongqing | 1 |
| Fujian | 12 |
+-----------+-----------+
- 统计中国每个省城市的名字列表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 |
+-----------+------------------------------------------------------------------------------------------------------------------------------+
- 扩展统计中国每个省城市的名字列表,按下列要求展现出来
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行
例如:
- 统计所有国家的总人口数量,将总人口数大于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 |
+-------------+-----------------+
- 统计中国每个省的总人口数,只打印总人口数小于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 |
+----------+-----------------+
- 查看中国所有的城市,并按人口数进行排序(从大到小)
mysql> SELECT name,population FROM city
-> WHERE countrycode='CHN'
-> ORDER BY population DESC;
+---------------------+------------+
| name | population |
+---------------------+------------+
| Shanghai | 9696300 |
| Peking | 7472000 |
| Chongqing | 6351600 |
| Tianjin | 5286800 |
- 统计中国各个省的总人口数量,按照总人口从大到小排序
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 |
- 统计中国,每个省的总人口,找出总人口大于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 |
+-----+------+-------+
- 统计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)
- 查询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 |
+--------+---------------------------------------------+------------------+
- 查询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 |
+--------+------------------+
- 每位老师所教课程的平均分,并按平均分排序
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 |
+--------+------------------+
- 查询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 |
+--------+-----------------------------+
- 查询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 |
+--------+-------+-------+
- 查询所有老师所教学生不及格的信息
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 |
+-----------+--------------------+