DQL 查询语句使用

1. select

1.1 作用
获取MySQL中的数据行

1.2 单独使用select

1.2.1 select @@xxxxxx；获取参数信息。
mysql root@localhost:school> select @@port;   -- 端口
mysql root@localhost:school> show variables like '%innodb%'\G 
select  函数();
mysql> select database(); 
mysql root@localhost:school> select now()
mysql root@localhost:mysql> select version() 

1.3 SQL92标准的使用语法

1.3.1 select 语法执行顺序（单表）
select开始---->
from子句---->
where子句---->
group by子句---->
select后执行条件---->
having字句---->
order by---->
limit  

1.3.2 from  
例子1： 查询city表中的所有数据
use world;
select * from city; --->适合表数据行较少，生产中使用较少。
例子2：查询name和population的所有值
mysql root@localhost:world> select name , population from city; 

1.3.3 单表练习环境：world 数据库下表介绍
mysql root@localhost:world> show tables from world;                        
city  （城市）
ID ：自增的无关列，数据行的需要
name： 城市名字
countrycode： 城市所在的国家代号
district：城市的所在的区域，中国是省的意思，美国是洲的意思
population：城市的人口数量
说明: 此表是历史数据，仅供学习交流使用。

熟悉业务

刚入职时，DBA的任务
1.搞清楚架构
通过公司架构图，搞清楚数据库的物理架构（1-2）天
逻辑结构：
（1） 成产库的信息（容易达到）
（2） 库下的表信息 （非常复杂）
1.开发和业务人员，搞好关系
2.搞到ER图（PD）
3.啥也没有怎么办？
  （1）找到建表语句，如果有注释，读懂注释。如果没有注释，只能根据列名翻译
  （2）找到表中部分数据，分析数据特点，达到了解列功能的目录

1.3.4 where 条件用法

例子：
where 配合 等值查询
查询美国的城市信息
mysql root@localhost:world> select * from city where countrycode='USA';

where 配合 不等值（> <  >= <= <>）
查询世界上人口下于100人的城市
mysql root@localhost:world> select * from city where Population<100;
查询世界上人口大于10000000的城市
mysql root@localhost:world> select * from city where Population>10000000;  

where 配合 模糊匹配（like）
注意：like 语句在MySQL中，不要出现%在前面的情况。因为效率很低，不走索引。
查询国家代号是C开头的城市
mysql root@localhost:world> select * from city where CountryCode like 'C%'; 

where 配合 逻辑连接符（AND OR）
AND:
查询城市人口小于10000 大于20000 
mysql root@localhost:world> select * from city where Population >10000 AND Population <20000; 
mysql root@localhost:world> select * from city where Population between 10000 AND 20000; 
OR:
查询中国或美国的城市信息
mysql root@localhost:world> select *from city where CountryCode='CHN' OR CountryCode='USA'; 
mysql root@localhost:world> select * from city where CountryCode IN ('CHN','USA'); 
建议改为以下语句：
select *
from city 
where CountryCode='CHN' 
union ALL
select * 
from city 
where countryCode='USA'; 

1.3.4 group by 配合聚合函数应用

常用聚合函数：
**max()**     ：最大值
**min()**      ：最小值
**avg()**      ：平均值
**sum()**      ：总和
**count()**    ：个数
group_concat() : 列转行
GROUP_CONCAT()
例子：
统计每个国家的总人口
select CountryCode,SUM(Population) from city group by CountryCode; 

统计每个国家的城市个数
select CountryCode,COUNT(name) from city group by CountryCode; 

统计并显示每个国家的省的名字列表
SELECT countrycode,GROUP_CONCAT(district)  FROM city GROUP BY countrycode;

统计中国每个省的城市列表
select  District,group_concat(population) from world.city where CountryCode='CHN' group by District;

统计中国每个省的总人口数
select District,SUM(Population) from city where CountryCode='CHN' group by District; 

1.3.5 HAVING 应用

例子：
统计中国的，每个省的总人口大于1000W的省及人口数
select District,SUM(Population) from city where CountryCode='CHN' group by District having SUM(Population)>10000000

1.3.6 ORDER BY

例子：
查询中国所有的城市，并以人口数降序输出
select * from city where CountryCode='CHN' order by Population desc;
                    

1.3.7 limit

显示第几行
select * from city where CountryCode='CHN' order by Population desc limit 10;
跳过M行，显示N行
select * from city where CountryCode='CHN' order by Population desc limit 5,5;

1.4 多表连接查询
环境准备

C3Z@_Q(}(FISB8`I6SAO7C5.png

1.4.1测试表的关系

1.4.2 什么时候用？
需要查询的数据是来自于多张表时。
1.4.3 怎么去多表连接查询
传统的连接： 基于whrer 条件
1. 找表之间的关系列
2. 排列查询条件


内连接
A   B

A.x   B.y 
1. 找表之间的关系列 
2. 将两表放在join左右
3. 将关联条件了放在on后面
4. 将所有的查询条件进行罗列

select A.m,B.n
from  
A  join  B
on A.x=B.y
where 
group by 
order by 
limit 

-- 例子：
-- 1.查询世界上人口小于100人的国家名，城市名，国土面积

SELECT country.name,city.name,country.surfacearea
FROM 
city JOIN country
ON city.countrycode=country.code
WHERE city.population<100;

--- 2. 查询oldguo老师和他教课程名称

SELECT teacher.tname ,course.cname
FROM teacher 
JOIN course 
ON teacher.tno=course.tno
WHERE teacher.tname='oldguo';

-- 3.统计以下每门课程的总成绩

select course.cname,sum(score.score)
from 
course join score 
on course.cno = score.cno 
group by course.cno,course.cname;

--- 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(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='oldboy' 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';

--- 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.tname;

--- 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';

--- 11.每位老师所教课程的平均分,并按平均分排序

SELECT teacher.tname ,course.`cname`,AVG(sc.`score`) 
FROM teacher
JOIN course
ON teacher.`tno`=course.`tno`
JOIN sc
ON course.`cno`=sc.`cno`
ORDER BY AVG(sc.`score`);

--- 12.查询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' AND sc.score<60
GROUP BY teacher.tno;

--- 13.查询所有老师所教学生不及格的信息

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 sc.score<60;

distinct 去重

SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city  ;

联合查询- union all

-- 中国或美国城市信息

SELECT * FROM city 
WHERE countrycode IN ('CHN' ,'USA');

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION     去重复
UNION ALL 不去重复

别名

表别名 ，列别名
SELECT 
a.Name AS an ,
b.name AS bn ,
b.SurfaceArea AS bs,
a.Population AS bp
FROM city AS a  JOIN country AS b
ON a.CountryCode=b.Code
WHERE a.name ='shenyang';