DBA之路 4_MySQL_select及多表连接

1.select

1.1作用

获取MySQL中的数据行

1.2单独使用select

select variables;
查变量(内置变量)
mysql> show variables like '%inno%';
模糊匹配

1.2.1select @@xxx

获取参数信息
mysql> select @@port;
+--------+
| @@port |
+--------+
|   3307 |
+--------+
1 row in set (0.00 sec)

1.2.2select 函数（）


mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-06-18 09:26:55 |
+---------------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| school     |
+------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.00 sec)

1.3SQL92标准的使用语法

1.3.1select 语法执行顺序（单表）

select 开始----->from 字句------>where字句------>group by字句
------->执行select后写的执行条件--------->having字句------->order by ------->limit

1.3.2from

查看world表全部内容
mysql> use world;
mysql> select * from city;
此操作适合数据行少的表
select Name,population from city;
select Name,population from world.city;
查name和population列，可以加绝对路径

1.3.3单表查询练习环境，world数据库下表的介绍

show tables from world;
desc city;
id:自增的无关列，数据行的需要
name：城市名
countrycode城市所在国家的编号
district：中国是省的意思，在美国是州的意思
population：人口数量
刚入职时，DBA的任务
1.搞清楚架构
通过公司架构图，搞清楚数据库的物理架构
1-2天
逻辑结构：
（1）生产库的个数和信息（容易）
（2）库下表的信息（非常复杂）
     1.开发和业务人员打好关系
     2.搞到ER图（设计关系图）（PD）powerDesigner
     3. 啥都没有怎么办
        （1）找到建表语句，如果有注释，读懂注释，如果没有，只能根据列名翻译
        （2） 找到表中部分数据，分析数据特点，达到了解功能的目录

1.3.2where

例子：
1、where配合等值查询
查询city表中，中国城市信息
mysql> select * from city where CountryCode='CHN';
| 1898 | Chengdu             | CHN         | Sichuan        |    3361500 |
| 1899 | Nanking [Nanjing]   | CHN         | Jiangsu        |    2870300 |
| 1900 | Changchun           | CHN         | Jilin          |    2812000 |
| 1901 | Xi´an               | CHN         | Shaanxi        |    2761400 |
| 1902 | Dalian              | CHN         | Liaoning       |    2697000 |
| 1903 | Qingdao             | CHN         | Shandong       |    2596000 |
| 1904 | Jinan               | CHN         | Shandong       |    2278100 |
| 1905 | Hangzhou            | CHN         | Zhejiang       |    2190500 |
| 1906 | Zhengzhou           | CHN         | Henan          |    2107200 |
| 1907 | Shijiazhuang        | CHN         | Hebei          |    2041500 |
| 1908 | Taiyuan             | CHN         | Shanxi         |    1968400 |
查询美国所有城市
mysql> select * from city where CountryCode='USA';
+------+-------------------------+-------------+----------------------+------------+
| ID   | Name                    | CountryCode | District             | Population |
+------+-------------------------+-------------+----------------------+------------+
| 3793 | New York                | USA         | New York             |    8008278 |
| 3794 | Los Angeles             | USA         | California           |    3694820 |
| 3795 | Chicago                 | USA         | Illinois             |    2896016 |
| 3796 | Houston                 | USA         | Texas                |    1953631 |
| 3797 | Philadelphia            | USA         | Pennsylvania         |    15175

2、where配合不等值(>= <= <> > <)

mysql> select * from city where population<'100';
+------+-----------+-------------+----------+------------+
| ID   | Name      | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 2912 | Adamstown | PCN         | –        |         42 |
+------+-----------+-------------+----------+------------+
1 row in set (0.00 sec)
mysql> select * from city where population>'10000000';
+------+-----------------+-------------+-------------+------------+
| ID   | Name            | CountryCode | District    | Population |
+------+-----------------+-------------+-------------+------------+
| 1024 | Mumbai (Bombay) | IND         | Maharashtra |   10500000 |
+------+-----------------+-------------+-------------+------------+
1 row in set (0.00 sec)

3、where配合模糊(like)

mysql> select * from city where  countrycode like 'c%';
+------+-----------------------+-------------+---------------------+------------+
| ID   | Name                  | CountryCode | District            | Population |
+------+-----------------------+-------------+---------------------+------------+
| 1889 | Bangui                | CAF         | Bangui              |     524000 |
| 1810 | Montréal              | CAN         | Québec              |    1016376 |
| 1811 | Calgary               | CAN         | Alberta             |     768082 |
| 1812 | Toronto               | CAN         | Ontario             |     688275
----注意like语句在MySQL中%不能写到前面，如%C。这样效率很低，因为不走索引

4、where配合逻辑连接符(and or )

查询城市人口在2万到5万之间的
mysql> select * from city where population>2000 and population<5000;
+------+-----------------+-------------+-------------------+------------+
| ID   | Name            | CountryCode | District          | Population |
+------+-----------------+-------------+-------------------+------------+
|   33 | Willemstad      | ANT         | Curaçao           |       2345 |
|   54 | Fagatogo        | ASM         | Tutuila           |       2323 |
|  916 | Saint George´s  | GRD         | St George         |       4621 |
| 2256 | Bairiki         | KIR         | South Tarawa      |       2226 |
| 2727 | Yangor          | NRU         | –                 |       4050 |
| 3065 | Castries        | LCA         | Castries          |       2301 |
| 3170 | Serravalle      | SMR         | Serravalle/Dogano |       4802 |
| 3171 | San Marino      | SMR         | San Marino        |       2294 |
| 3423 | Cockburn Town   | TCA         | Grand Turk        |       4800 |
| 3424 | Funafuti        | TUV         | Funafuti          |       4600 |
+------+-----------------+-------------+-------------------+------------+
10 rows in set (0.00 sec)
mysql> select * from city where population between 2000 and 5000;
+------+-----------------+-------------+-------------------+------------+
| ID   | Name            | CountryCode | District          | Population |
+------+-----------------+-------------+-------------------+------------+
|   33 | Willemstad      | ANT         | Curaçao           |       2345 |
|   54 | Fagatogo        | ASM         | Tutuila           |       2323 |
|  916 | Saint George´s  | GRD         | St George         |       4621 |
| 2256 | Bairiki         | KIR         | South Tarawa      |       2226 |
| 2697 | Plymouth        | MSR         | Plymouth          |       2000 |
| 2727 | Yangor          | NRU         | –                 |       4050 |
| 3065 | Castries        | LCA         | Castries          |       2301 |
| 3170 | Serravalle      | SMR         | Serravalle/Dogano |       4802 |
| 3171 | San Marino      | SMR         | San Marino        |       2294 |
| 3423 | Cockburn Town   | TCA         | Grand Turk        |       4800 |
| 3424 | Funafuti        | TUV         | Funafuti          |       4600 |
+------+-----------------+-------------+-------------------+------------+
11 rows in set (0.01 sec)
mysql> select * from city where countrycode='CHN' or countrycode='USA';
mysql> select * from city where countrycode in ('CHN','USA');

以上语句改为UNION ALL

UNION
UNION ALL
如果有重复行，UNION会自动去重
UNION ALL
不会去重
mysql> select * from city where countrycode='USA' union all select * from city where countrycode='CHN';

1.3.2group by配合聚合函数

常用聚合函数：
**max()**      ：最大值
**min()**      ：最小值
**avg()**      ：平均值
**sum()**      ：总和
**count()**    ：个数
group_concat() : 列转行

应用

显示每个国家总人口数：
mysql> select countrycode,sum(population) from city group by countrycode;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| ABW         |           29034 |
| AFG         |         2332100 |
| AGO         |         2561600 |
| AIA         |            1556 |
| ALB         |          270000 |
| AND         |           21189 |
| ANT         |            2345 |
| ARE         |         1728336 |
| ARG         |        19996563 |
| ARM         |         1633100 |
| ASM         |            7523 |
| ATG         |           24000 |
显示每个国家的总城市个数
mysql> select countrycode,count(Name) from city group by countrycode;
显示中国每个省的城市名，列表形式
mysql> select district,group_concat(name) from city where countrycode='CHN' group by district;
运行结果如下图
显示中国每个省的总人口数
select district,sum(population)
排队，队后跟的内容
from city
从哪个表
where countrycode='CHN'
条件
group by district;
通过什么站队

image.png

1.3.2having

后过滤，跟where一样
统计中国的每个省的总人口数大于999999的省人口数
mysql> select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>9999999;
+--------------+-----------------+
| district     | sum(population) |
+--------------+-----------------+
| Heilongjiang |        11628057 |
| Liaoning     |        15079174 |
| Shandong     |        12114416 |
+--------------+-----------------+
3 rows in set (0.00 sec)
说明：Having语句后的条件不走索引，如果数据量大，则需要优化

1.3.2order by

从小到大
mysql> select district,sum(population) from city where countrycode='CHN' group by district order by sum(population);
从大到小
mysql> select district,sum(population) from city where countrycode='CHN' group by district order by sum(population) DESC;
显示中国城市信息和人口数
select *
from city
where countrycode='CHN'
order by population DESC;

1.3.2limit

limit m,n 跳过M行显示N行
limit x offset y 跳过y行显示x行
显示前几名：
select *
from city 
where countrycode='CHN' 
order by population DESC
LIMIT 5;
跳过五个开始显示后5个。
select *
from city 
where countrycode='CHN' 
order by population DESC
LIMIT 5,5;
同上
select *
from city 
where countrycode='CHN' 
order by population DESC
LIMIT 5 offset 5;

拓展：

统计行数：
select count(*) xxxx;
select count(1) xxxx;

1.4多表连接查询

1.4.1表结构关系（略）

1.4.2什么时候用

需要查询的数据来自于多张表时

1.4.3怎么多表连接查询

传统连接：基于where条件
1.找表之间的关系列
2.排列查询条件
SELECT
    city. NAME,
    country. NAME,
    country.surfacearea
FROM
    city,
    country
WHERE
    city.countrycode = country. CODE
AND city.population < 100;
自连接（自己了解）
内连接join on******
1.找关系列
2.将2表放在join左右
3.将关联条件放在On后面
4.将所有查询条件进行罗列
a.x b.y
a join b on a.x=b.y

例子1：
查询世界上人口数量小于100人的国家的名城市名和国土面积
SELECT
    city. NAME,
    country. NAME,
    country.surfacearea
FROM
    city JOIN country 
    ON city.countrycode = country. CODE
WHERE
    city.population < '100';
例子2：
查询oldguo老师所教课程，及教了几门课和课程名称

SELECT course.cno,course.cname,SUM(score.score)
FROM course  
JOIN score 
ON course.cno = score.cno
GROUP BY course.cname;

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and 
contains nonaggregated column 'school.course.cno' which is not functionally dependent on 
columns in GROUP BY clause; this is incompatible with 
sql_mode=only_full_group_by
mysql> 
原因1:在 select后面出现的列，不是分组的条件，并且，没有在函数内包裹
原因2:如果group by后是主键列，或者唯一列，则不会报错
---6.查询oldboy老师教的学生的不及格的成绩的学生名字



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 teacher.`tname`='oldguo'
AND score.`score`<60
GROUP BY teacher.`tno`;

---7. 8.统计zhang3,学习了几门课
SELECT
student.sname,
course.cname
from 
teacher
JOIN course ON teacher.tno = course.tno
JOIN score ON course.cno = score.cno
JOIN student ON score.sno = student.sno
WHERE student.sname='zhang3'
--9查询oldguo老师教的学生名
SELECT 
teacher.tname,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 teacher.tname='oldboy'
--- 10.查询oldguo所教课程的平均分数
SELECT
teacher.tname,course.cname,AVG(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
GROUP BY course.cno
HAVING teacher.tname='oldguo';


--- 11.每位老师所教课程的平均分,并按平均分排序
SELECT
teacher.tname,course.cname,AVG(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
GROUP BY course.cno

--- 12.查询oldguo所教的不及格的学生姓名
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
    teacher.tname = 'oldguo'
HAVING
  score.score<60;

--- 13.查询所有老师所教学生不及格的信息
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;

外连接*****

2.show

3.information_schema

最后编辑于：2019.06.22 07:18:47

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 229,885评论 6赞 541
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 99,312评论 3赞 429
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 177,993评论 0赞 383
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 63,667评论 1赞 317
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 72,410评论 6赞 411
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 55,778评论 1赞 328
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 43,775评论 3赞 446
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 42,955评论 0赞 289
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 49,521评论 1赞 335
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 41,266评论 3赞 358
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 43,468评论 1赞 374
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 38,998评论 5赞 363
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 44,696评论 3赞 348
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 35,095评论 0赞 28
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 36,385评论 1赞 294
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 52,193评论 3赞 398
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 48,431评论 2赞 378

DBA之路 4_MySQL_select及多表连接

1.select

1.1作用

1.2单独使用select

1.2.1select @@xxx

1.2.2select 函数（）

1.3SQL92标准的使用语法

1.3.1select 语法执行顺序（单表）

1.3.2from

1.3.3单表查询练习环境，world数据库下表的介绍

1.3.2where

以上语句改为UNION ALL

1.3.2group by配合聚合函数

1.3.2having

1.3.2order by

1.3.2limit

1.4多表连接查询

1.4.1表结构关系（略）

1.4.2什么时候用

1.4.3怎么多表连接查询

2.show

3.information_schema

推荐阅读更多精彩内容