DBA之路 4_MySQL_select及多表连接
2019-06-19 本文已影响0人
Linux_淡忘
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;
外连接*****
