SQL之数据查询语言

1.  DQL 介绍
select 
show 

2. select 语句的应用
2.1 select单独使用的情况***
mysql> select @@basedir;    #mysql安装目录
mysql> select @@port;        #mysql端口号
mysql> select @@innodb_flush_log_at_trx_commit;        #日志刷新策略
mysql> show variables like 'innodb%';    #模糊查看innodb开头的配置
mysql> select database();    #查看当前库名
mysql> select now();        #查看当前系统时间
mysql> select @@server_id;    #查看本实例id号，群集中不能重复

2.2 select 通用语法(单表) *****
select  显示的列名（多列逗号分开）   
from  表名（多个表逗号分开）   
where 过滤条件的列 
group by  分组的列
having   分组后的过滤聚合函数
order by 排序的列
limit  显示前几行

2.3 学习环境的说明
world数据库
city                城市表
country          国家表  
countrylanguage  国家的语言

city表结构
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

5 rows in set (0.00 sec)
mysql> 

ID          :      城市序号(1-...)
name        :     城市名字
countrycode :   国家代码,例如:CHN,USA
district    :   区域: 中国 省  美国 洲
population  :   人口数

如何熟悉数据库业务?

    快速和研发人员打好关系
    找到领导要ER图
    DESC ,show create table
    select * from city limit 5;

2.4 SELECT 配合 FROM 子句使用
-- select  列,列,列  from  表
--- 例子:
(1) 查询表中所有的信息(生产中几乎是没有这种需求的)
USE world ;
SELECT  id,NAME ,countrycode ,district,population   FROM  city;
或者:
SELECT  *   FROM city;

(2) 查询表中 name和population的值
SELECT  NAME ,population   FROM  city;

2.5  SELECT 配合  WHERE 子句使用 
-- select  列,列,列  from  表  where 过滤条件

-- where等值条件查询  *****
例子:
 查询中国所有的城市名和人口数
select name,population from city where countrycode='CHN';

-- where 配合比较判断查询(> < >= <=)  *****
例子:
 世界上小于100人的城市名和人口数
select name,population from city where population<100;

-- where 配合 逻辑连接符(and  or)
例子: 
(1) 查询中国人口数量大于800w的城市名和人口
select name,population from city where countrycode='CHN' and population>8000000;

(2) 查询中国或美国的城市名和人口数
select name,population from city where countrycode='CHN' or countrycode='USA';

(3) 查询人口数量在500w到600w之间的城市名和人口数
select name,population from city where population>=5000000 and population<=6000000;
或者:
select name,population from city where population between 5000000 and 6000000;

-- where 配合 like 子句 模糊查询 *****
例子:
 查询一下contrycode中带有CH开头,城市信息
select name,countrycode from city where countrycode like 'CH%';

注意:不要出现类似于 %CH%,前后都有百分号的语句,因为不走索引,性能极差
如果业务中有大量需求,我们用"Elasticsearch"来替代

-- where 配合 in 语句

例子: 
 查询中国或美国的城市信息.
select name,population from city where countrycode in ('CHN','USA');

2.5.2 GROUP BY 
将某列中有共同条件的数据行,分成一组,然后在进行聚合函数(sum,avg,count,max,min)操作.
例子: 

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

(2) 统计每个国家的总人口数.
select countrycode,sum(population) from city group by countrycode;

(3) 统计每个 国家 省 的个数(distinct 去除重复)
select countrycode,count(distinct district) from city group by countrycode;

(4) 统计中国 每个省的总人口数
select district as 省,sum(population) as  总人口 from city where countrycode='CHN' group by district;

(5) 统计中国 每个省城市的个数
select district as 省,count(name) as 城市个数 from city where countrycode='CHN' group by district;

(6) 统计中国 每个省城市的名字列表GROUP_CONCAT() #列转行
select district,group_concat(name) from city where countrycode='CHN' group by district;

(7) 小扩展(拼接，自定义分隔符)
anhui : hefei,huaian ....
SELECT CONCAT(district,":" ,GROUP_CONCAT(NAME))     FROM  city 
WHERE countrycode='CHN'
GROUP BY district ;

2.7 SELECT 配合 ORDER BY 子句

例子:
 统计所有国家的总人口数量,
将总人口数大于5000w的过滤出来,
并且按照从大到小顺序排列
select countrycode,sum(population) from city group by countrycode having sum(population)>50000000  
order by sum(population) desc;

注：默认为升序，asc ; 降序为desc

2.8 SELECT 配合 LIMIT  子句

例子:
 统计所有国家的总人口数量,
将总人口数大于5000w的过滤出来,
并且按照从大到小顺序排列,只显示前三名
select countrycode,sum(population) from city group by countrycode having sum(population)>50000000  
order by sum(population) desc limit 3;

LIMIT M,N     :跳过M行,显示一共N行
LIMIT Y OFFSET X: 跳过X行,显示一共Y行

2.9 练习题:
(1) 统计中国每个省的总人口数，只打印总人口数小于100w的
select district,sum(population) from city where countrycode='CHN' 
group by district having sum(population)<1000000;

(2) 查看中国所有的城市，并按人口数进行排序(从大到小)
select name,population from city where countrycode='CHN' order by population desc;

(3) 统计中国各个省的总人口数量，按照总人口从大到小排序
select district,sum(population) from city where countrycode='CHN' 
group by district order by sum(population) desc;

(4) 统计中国,每个省的总人口,找出总人口大于500w的,
并按总人口从大到小排序,只显示前三名
select district,sum(population) from city where countrycode='CHN'
group by district having sum(population)>5000000 
order by sum(population) desc 
limit 3 ;

2.10 把中国每个省城市个数大于10的列出前3名
select district,count(name) from city where countrycode='CHN'
group by district having count(name)>10 order by count(name) desc limit 3;

2.11 union 和 union all 
作用: 多个结果集合并查询的功能

需求: 查询中或者美国的城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';

改写为:
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL 
SELECT * FROM city WHERE countrycode='USA';

面试题: union 和 union all 的区别 ?
union all   不做去重复
union         会做去重操作

3. 多表连接查询(内连接)
分类：
    inner join    内连接，企业普遍使用，inner可以省略
    left  join  左外连接
    right join  右外连接
    full  join  完整外连接
    cross join  求笛卡尔积

3.1 多表连接基本语法

student ：学生表
===============
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
================
tno：     教师编号
tname：教师名字

course ：课程表
===============
cno：  课程编号
cname：课程名字
tno：  教师编号

sc  ：成绩表
==============
sno：  学号
cno：  课程编号
score：成绩

3.2 多表连接例子

-- 1. 统计zhang3,学习了几门课
select student.sname,count(sc.score) from student inner join sc 
on student.sno=sc.sno 
where student.sname='zhang3';

-- 2. 查询zhang3,学习的课程名称有哪些?
select student.sname,course.cname from student  join sc 
on student.sno=sc.sno 
join course on sc.cno=course.cno 
where student.sname='zhang3';

-- 3. 查询laoli老师教的学生名和个数.
select teacher.tname,group_concat(student.sname),count(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 teaccher.tname='laoli';

-- 4. 查询laoli所教课程的平均分数
select course.cname,avg(sc.score) 
from teacher join course  on teacher.tno=course.tno 
join sc on course.cno=sc.cno
where teacher.tname='laoli'
group by course.cname;

-- 5. 每位老师所教课程的平均分,并按平均分降序排列
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.tname 
order by avg(sc.score) desc;

-- 6. 查询laoli所教的不及格的学生姓名
select 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='laoli' and sc.score<60;

-- 7. 查询所有老师所教学生不及格的信息(扩展)
第一种方法：
select teacher.tname,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 sc.score<60;

第二种方法：
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 sc.score<60
GROUP BY teacher.tname;

-- 8.别名应用
表别名 : 
SELECT t.tname,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) 
FROM teacher as t
JOIN course as c
ON t.tno=c.tno
JOIN sc 
ON c.cno=sc.cno
JOIN student as st
ON sc.sno=st.sno
WHERE sc.score<60
GROUP BY t.tname;
 
 

表别名是全局调用的.

列别名:
SELECT t.tname as 讲师名 ,GROUP_CONCAT(CONCAT(st.sname,":",sc.score))  as 不及格的 
FROM teacher as t
JOIN course as c
ON t.tno=c.tno
JOIN sc 
ON c.cno=sc.cno
JOIN student as st
ON sc.sno=st.sno
WHERE sc.score<60
GROUP BY t.tno

列别名可以被  having 和 order by 调用

-- 9. 语句嵌套
create database hehe default charset utf8mb4 collate utf8mb4_bin;
use hehe
create table t1 (id int,name varchar(20));
insert into t1 values (1,'zhangsan'),(2,'lisi'),(3,'zhangsan'),(4,'wangwu'),(5,"zhangsan"),(6,'lisi');

要求：查找重复两次及以上的人员，按id号从大到小排列。
select t1.name,id from t1 
join (select name from t1 group by name having count(name)>1) as  t2 
on t1.name=t2.name order by id desc;