DQL 数据查询语言 select

1.select

1.select 单独使用
（1） 查询数据库的参数
查看端口：
select @@port;

查看数据路径
select @@datadir;

（2）调用内置函数
查看当前库
select database();

CONCAT 将内容拼接
select concat(user,host) from mysql.user;
select concat(user,'@',host) from mysql.user;

列转行
select group_concat(xid) from student;

求和
SELECT SUM(xid) FROM student;

(3) 简易计算器
select 4*5;


2.select 配合其他子句使用
select 查询表的内容
(1) 子句列表介绍
from    查询对象是谁（表，视图）
where    过滤字句
group by    分组子句（类似于数组，统计分析类）
having    后过滤子句
order by    排序子句
limit    限制子句（分页子句）

select 配合 from 使用
1.查询表中所有的数据（cat）
select * from city;

2.查询name和population列信息
select name,population from city;


select 配合 from where 使用

-- 1.查询中国所有的城市信息
SELECT * FROM city
WHERE CountryCode='CHN';

-- 2. 查询ID为100的城市信息
SELECT * FROM city
WHERE ID=100;

-- 3. 查询 中国河北省的城市信息
SELECT * FROM city
WHERE CountryCode='CHN' AND district='hebei';

-- 4. 查询 中国或美国的城市信息
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';

-- where 配合范围查询

-- 例子 :
-- 1. 查询人口数量小于100人的城市
SELECT * FROM city
WHERE Population<100;

-- 2. 查询人口数量在100w-200w之间的城市
SELECT * FROM city
WHERE Population>=1000000 AND Population<=2000000;

或者

SELECT * FROM city
WHERE Population BETWEEN 1000000 AND 2000000;

-- 3.查询国家代号是CH开头的城市
SELECT * FROM city
WHERE CountryCode LIKE 'CH%';

-- 注意 % 放在字符前，匹配时不走索引，比较慢，所以，我们通常都放在字符后面

-- where 配合范围查询

-- 例子 :
-- 1. 查询人口数量小于100人的城市
SELECT * FROM city
WHERE Population<100;

-- 2. 查询人口数量在100w-200w之间的城市
SELECT * FROM city
WHERE Population>=1000000 AND Population<=2000000;

或者

SELECT * FROM city
WHERE Population BETWEEN 1000000 AND 2000000;

-- 3.查询国家代号是CH开头的城市
SELECT * FROM city
WHERE CountryCode LIKE 'CH%';

-- 注意 % 放在字符前，匹配时不走索引，比较慢，所以，我们通常都放在字符后面
 


-- 1.2.4 group by 分组子句 配合  聚合函数应用
-- 什么是聚合函数？
COUNT() -- 计数
SUM()   -- 求和
AVG()   -- 平均值
MAX()   -- 最大值
MIN()   -- 最小值
GROUP_CONCAT  -- 聚合列值

-- MySQL结果集现实的特点:
在结果集显示时，必须是 一对一的方式，不能是一对多的形式。

-- 例子 :
-- 1. 统计一下每个国家的人口总数
SELECT CountryCode,SUM(Population)
FROM city
GROUP BY (CountryCode);

-- 2. 统计中国每个省的人口总数
SELECT District,SUM(Population) 
FROM city
WHERE CountryCode='CHN'
GROUP BY (District);

-- 3. 统计一下中国每个省的城市个数，及城市名
SELECT District,COUNT(NAME),GROUP_CONCAT(NAME) FROM city
WHERE CountryCode='CHN'
GROUP BY (District);

-- 4. 统计每个国家的城市个数

SELECT CountryCode,COUNT(District)
FROM city
GROUP BY (CountryCode);

-- 1.2.6 having 后判断 

-- 1. 统计中国每个省的人口总数,只显示总人口数大于500w的省信息.
SELECT  district,SUM(population)  FROM  city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)  >=5000000;


-- 1.2.7  order by 排序子句

-- 例子:
-- 1. 查询中国所有城市信息,人口数从大到小排序输出.
SELECT * FROM city
WHERE countrycode='CHN'
ORDER BY population DESC ;

-- 2. 查询中国所有城市信息,按城市名排序.
SELECT * FROM city
WHERE countrycode='CHN'
ORDER BY NAME;

-- 3. 查询中国所有省的总人口,并按总人口数从大到小排序输出.

SELECT  district,SUM(population)  FROM  city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC;

多表查询

-- 1. 查询oldguo老师教的学生名.
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
GROUP_CONCAT(student.`xname`) AS "学生列表"
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
JOIN student 
ON score.`xid`=student.`xid`
WHERE teacher.tname='oldguo'
GROUP BY teacher.tid,teacher.tname;


SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.cname AS "课程名",
AVG(score.score) AS "平均分"
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
WHERE teacher.tname='oldguo'
GROUP BY teacher.tid,teacher.tname,course.cname;

-- 3. 每位老师所教课程的平均分,并按平均分排序.
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.cname AS "课程名",
AVG(score.score) AS "平均分"
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY teacher.tid,teacher.tname,course.cname;

-- 4. 查询白龙马,学习的课程名称有哪些.
SELECT 
CONCAT(student.xname,"_",student.`xid`) AS "学生姓名",
GROUP_CONCAT(course.`cname`) AS "课程列表"
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.`cid`=course.`cid`
WHERE student.xname='白龙马'
GROUP BY student.xid,student.xname;

-- 5.1 统计每位老师,每门课程,分别的收入情况
SELECT 
CONCAT(teacher.`tname`,"_",teacher.`tid`) AS "讲师名",
course.`cprice`*COUNT(score.xid) AS "课程收入"
FROM teacher
JOIN course
ON teacher.`tid` = course.`tid`
JOIN score
ON course.`cid` = score.`cid`
GROUP BY teacher.`tid`,teacher.`tname` , course.`cid`;

-- 7. 查询oldboy老师教的学生不及格的学生名单. 
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(student.xname,":",score.score))
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
JOIN student 
ON score.`xid`=student.`xid`
WHERE teacher.`tname`='oldboy' AND score.`score`<60
GROUP BY teacher.tid,teacher.tname;


-- 8. 统计每位老师不及格学生名单. 
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(student.xname,":",score.score))
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
JOIN student 
ON score.`xid`=student.`xid`
WHERE score.`score`<60
GROUP BY teacher.tid,teacher.tname;

-- 9. 每位老师所教课程的平均分,并按平均分排序.
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.`cname`,
AVG(score.`score`)
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY teacher.tid,teacher.tname,course.`cid`;


-- 10. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分.

SELECT course.`cid`,MAX(score.`score`),MIN(score.`score`)
FROM course
JOIN score
ON course.`cid`=score.`cid` 
GROUP BY course.`cid`;

-- 11. 查询平均成绩大于60分的同学的学号和平均成绩.
SELECT 
CONCAT(student.`xname`,"_",student.`xid`),
AVG(score.`score`)
FROM student
JOIN score
ON student.`xid`=score.`xid`
GROUP BY student.`xname`,student.`xid`
HAVING AVG(score.`score`)>60
ORDER BY AVG(score.`score`) DESC ;


-- 12. 统计各位老师,所教课程的及格率

SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.`cname`,
CONCAT(COUNT(CASE WHEN score.score>60 THEN 1  END)/COUNT(score.xid)*100,"%")
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY teacher.tid,teacher.tname,course.`cid`;

-- 13. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表

SELECT 
course.`cname` AS 课程名称,
GROUP_CONCAT(CASE WHEN score.`score` >= 85 THEN student.xname END )  AS "优秀",
GROUP_CONCAT(CASE WHEN score.`score` >=70 AND score.`score` < 85  THEN student.xname  END) AS "良好",
GROUP_CONCAT(CASE WHEN score.`score` >=60  AND score.`score` <70 THEN  student.xname  END  )AS "一般",
GROUP_CONCAT(CASE WHEN score.`score` <60  THEN  student.xname END  ) AS "不及格"
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.`cid`=course.`cid`
GROUP BY course.`cid`;

1.5 left join

一般应用在强制驱动板时，强制小结果集驱动大表
生产中可以使用left join 强制驱动表，尽量减少next loop的出现
为什么要强制？ inner join 优化器 自动选择，按照索引选择的几率比较大

1.6 别名

1.列别名
作用：展示数据时，可以随心的展示想要的列名
例如：CONCAT(teacher.tname,"_",teacher.tid) AS "教师名"
列别名尽量有意义，根据select子句的执行逻辑，只能在having之后才能调用


2.表别名