1. select
1.1 作用
对表中的数据行进行查询
1.2 单独使用select
1.2.1 select @@xxxx;获取简单参数信息
-- 查询my.cnf里面定义的端口
3306 [(none)]>select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec)
3306 [(none)]>show variables like '%innodb%'; #详细参数列表
1.2.2 select 函数( );
-- 查看当前时间
3306 [(none)]>select now();
+---------------------+
| now() |
+---------------------+
| 2019-06-18 09:27:04 |
+---------------------+
1 row in set (0.00 sec)
-- 查看当前所在库
3306 [(none)]>use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
3306 [mysql]>select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
-- 查看数据库版本
3306 [mysql]>select version();
+------------+
| version() |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.09 sec)
1.3 SQL 92标准的使用语法
1.3.1 select 语法执行顺序（单表）
select开始 ---》
from子句---》
where子句---》
group by子句---》
select后执行条件---》
having子句---》
order by子句---》
limit
1.3.2 from(select后面必须跟from)
-- 例1：查询city表中的所有数据
use world;
select * from city; #查所有数据，这种方法仅适合数据行较少的表（小表），生产中使用较少，可能会造成数据库查询缓慢。
select * from world.city;
-- 例2：查询name和population的所有值。（查看2列的值）
select name , population from city;
select name , population form world.city; #推荐使用这种方法
1.3.3 单表查询练习环境：world数据库下的表介绍
world.sql下载链接：https://pan.baidu.com/s/1eR5Yo0-IcF8QspcRxOPHbA
提取码：paik
查看库中所有表
show tables from world;
city:（城市）
desc city;
id：自增的无关列，数据行的需要
name：城市名字
countrycode：国家代号，CHN（中国）、USA（美国）、JPN（日本）……
district：中国是省的意思,美国是洲的意思
population：城市人口数量
说明：该数据为历史数据，仅供学习交流使用。
刚入职时，如何熟悉业务：
刚入职时DBA的任务，就是熟悉业务。
1. 先搞清楚架构，通过公司的架构图，搞清楚数据库的物理架构。（多少台机器，使用什么样的软件），一般时间为1-2天。
2. 逻辑结构（2-3周内）
（1）生产库的信息（库的个数、库名）
（2）库下面表的信息（非常复杂）
1. 与业务人员和开发搞好关系，从他们口中得知详细信息。
2. 搞到ER图（实景关系图，PD）
3. 啥都没有怎么办？
（1）找到建表语句，如果有注释，读懂注释就可以了。如果没有注释，只能根据列名翻译。
（2）找到表中部分数据，分析数据特点，达到了解数据功能的目的。
1.3.3 where(相当于Linux中的grep)
-- 例子1（生产常用）：
-- where配合等值（=）查询
-- 查询中国的城市信息
select * from world.city where countrycode='CHN';
-- 查询美国的城市信息
select * from world.city where countrycode='USA';
-- where配置不等值（>、<、>=、<=、<>）
-- 查询世界人口数量小于100人的城市
select * from world.city where population<100;
-- 查询世界人口数量大于10000000人口的城市
select * from world.city where population>10000000;
-- where 配合模糊查询（like）
-- 查询国家代号是C开头的城市
select * from world.city where countrycode like 'C%';
--- 注意：like语句在MySQL中，不要出现%在前的情况。因为效率很低，不走索引。
-- where 配合 逻辑连接符（and和or）
-- 查询城市人口在10000到20000之间的城市（and）
select * from city where population > 10000 and population < 20000;
select * from city where population between 10000 and 20000;
-- 查询中国或美国的城市信息（or）
select * from city where countrycode='CHN' or countrycode='USA';
select * from city where countrycode in ('CHN','USA');
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA'; ##建议使用这种
面试题：union与union all的区别：
union [distinct]合并重复值
union all 不合并重复值（性能更好，不用排序）
1.3.4 group by(分组条件) 配合聚合函数应用
常用聚合函数：
avg()：平均值
count()：计算
sum()：总数
max()：最大
min()：最小
group_concat()：列转行
-- 统计全世界每个国家的总人口数
select countrycode,sum(population) from city group by countrycode;
解题思路
-- 统计每个国家的城市个数
1. 拿什么站队
国家（countrycode）--->goup by countrycode
2. 拿什么统计
城市ID或城市名name
3. 统计的是什么
count(id)
select countrycode,count(id) from city group by countrycode;
-- 统计并显示每个国家省的名字列表
select countrycode,group_concat(district) from city group by countrycode;
-- 统计中国每个省的城市列表名
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;
1.3.5 having（二级过滤）
--- 统计中国每个省的总人口数大于1千万的省人口数
select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>10000000;
结论：having后的条件是不走索引的，可以进行一些优化手段。
1.3.6 order by
-- 中国每个省的总人口数总人口数从大到小显示
select District,sum(population) from city where CountryCode='CHN' group by District order by sum(population) desc;
-- 中国每个省的总人口数从小到大显示
select District,sum(population) from city where CountryCode='CHN' group by District order by sum(population);
--- 例子：查询中国所有的城市，并以人口数降序输出
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
1.3.7 limit
-- 显示中国城市人口数排在前5的城市
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC limit 5; 显示前5名
-- 显示中国城市人口数排在6-10名的城市
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC limit 5,5;#跳过前5行。显示后5行
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC limit 5 offset 5;
limit m，n 跳过m行，显示n行
limit x offset y 跳过y行，显示x行
1.4 多表连接查询
1.4.1 介绍测试表的关系
1.4.2 为什么要使用多表连接查询？
需要查询的数据时来自于多张表时
1.4.3 怎么去多表连接查询
（1）传统的连接：基于where条件（了解）
（2）自连接（了解、自行扩展）
（3）内连接#常用（超重点）
1. 找表之间的关系列
2. 排列查询条件
3. 将关联条件列放在on 后面（on A.X=B.X）
4. 将所有的查询条件进行罗列
select A join B on A.X=B.y where group by oreder by limit;
-- 例子：
-- 1. 查询世界上人口数量小于100人的国家名、城市名、国土面积
select country.name,city.name,country.surfacerea
from city join country
on city.countrycode=country.code
where city.population<100;
-- 查询oldguo老师和他教的课程名称。
SELECT teacher.tname ,course.cname
FROM teacher
JOIN course
ON teacher.tno=course.tno
WHERE teacher.tname='oldguo';
-- 统计每门课程的总成绩
select course.cname,sum(sc.score)
from course
join sc
on course.cno = sc.cno
group by course.cno,course.cname;
-- 5.7 版本会报错的情况,在sqlyog中以下操作没问题, 但是在命令行上是会报错
SELECT course.cno,course.cname,SUM(sc.score)
FROM course
JOIN sc
ON course.cno = sc.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
（原因是：开了sql_mode=only_full_group_by导致的
1. 在select 后面出现的列cno，即不是分组条件（group by），也没有在函数内包裹（没有在函数括号内）出现。），所以报错了。
2. 如果group by后是主键列，或者是唯一列,就能行。如下：
SELECT course.cno,course.cname,SUM(sc.score)
FROM course
JOIN sc
ON course.cno = sc.cno
GROUP BY course.cno;
-- 例子：
--- 4. 查询oldguo老师教的学生姓名列表
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE teacher.tname='oldguo'
GROUP BY teacher.tname;
-- 5. 查询所有老师教的学生姓名列表
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
GROUP BY teacher.tno;
-- 6. 查询oldboy老师教的不及格学生的姓名
select teacher.tname,group_concat(concat(student.sname,":",sc.score))
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='oldguo' and sc.score<60
group by teacher.tno;
-- 7. 统计zhang3,学习了几门课
SELECT student.`sname` ,COUNT(sc.`cno`)
FROM student
JOIN sc
ON student.`sno`=sc.`sno`
WHERE student.sname='zhang3';
-- 8. 查询zhang3,学习的课程名称有哪些?
select student.sname,group_concat(course.cname)
from student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
where student.sname='zhang3'
group by course.cno;
-- 9. 查询oldguo老师教的学生名.
select teacher.tname,group_concat(student.sname)
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='oldguo'
group by teacher.tno;
-- 10.查询oldguo所教课程的平均分数
select teacher.tname,course.cname,avg(sc.score)
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
where teacher.tname='oldguo'
GROUP BY course.cno;
-- 11.每位老师所教课程的平均分,并按平均分排序
select teacher.tname,avg(sc.score)
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
group by teacher.tno
order by avg(sc.score) desc;
-- 12.查询oldguo所教的不及格的学生姓名
select teacher.tname,student.sname
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='oldguo' and sc.score < 60
group by student.sno;
-- 13.查询所有老师所教学生不及格的信息
SELECT teacher.tname,student.sname
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE sc.score<60;