Mysql(linux版)SQL基础（第二节）

最新推荐文章于 2021-08-31 13:10:23 发布

ygpGoogle

最新推荐文章于 2021-08-31 13:10:23 发布

阅读量364

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/ygp12345/article/details/107716353

版权

mysql 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

DQL

select 语句的应用

select单独使用的情况***
mysql> select @@basedir;
mysql> select @@port;
mysql> select @@innodb_flush_log_at_trx_commit;
mysql> show variables like ‘innodb%’;
mysql> select database();
mysql> select now();

**select 通用语法(单表) *******
select 列
from 表
where 条件
group by 条件
having 条件
order by 条件
limit

学习环境的说明
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 : 人口数

SELECT 配合 FROM 子句使用

– select 列,列,列 from 表
— 例子:

查询表中所有的信息(生产中几乎是没有这种需求的)
USE world ;
SELECT id,NAME ,countrycode ,district,population FROM city;
或者:
SELECT * FROM city;

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

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)
例子:

查询中国人口数量大于1000w的城市名和人口
SELECT NAME,population FROM city
WHERE countrycode=‘CHN’ AND population>8000000;
查询中国或美国的城市名和人口数
SELECT NAME,population FROM city
WHERE countrycode=‘CHN’ OR countrycode=‘USA’;
查询人口数量在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 * FROM city
WHERE countrycode LIKE ‘CH%’;

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

where 配合 in 语句

例子:

查询中国或美国的城市信息.
SELECT NAME,population FROM city
WHERE countrycode=‘CHN’ OR countrycode=‘USA’;
或者:
SELECT NAME,population FROM city
WHERE countrycode IN (‘CHN’ ,‘USA’);

GROUP BY

将某列中有共同条件的数据行,分成一组,然后在进行聚合函数操作.
例子:

统计每个国家,城市的个数
SELECT countrycode ,COUNT(id) FROM city
GROUP BY countrycode;
统计每个国家的总人口数.
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode;
统计每个国家省的个数
SELECT countrycode,COUNT(DISTINCT district) FROM city
GROUP BY countrycode;
统计中国每个省的总人口数
SELECT district, SUM(population) FROM city
WHERE countrycode=‘CHN’
GROUP BY district ;
统计中国每个省城市的个数
SELECT district, COUNT(NAME) FROM city
WHERE countrycode=‘CHN’
GROUP BY district ;
统计中国每个省城市的名字列表GROUP_CONCAT()
guangdong guangzhou,shenzhen,foshan…

SELECT district, GROUP_CONCAT(NAME) FROM city
WHERE countrycode=‘CHN’
GROUP BY district ;

小扩展
anhui : hefei,huaian …
SELECT CONCAT(district,":" ,GROUP_CONCAT(NAME)) FROM city
WHERE countrycode=‘CHN’
GROUP BY district ;

SELECT 配合 ORDER BY 子句

例子:

统计所有国家的总人口数量,
将总人口数大于5000w的过滤出来,
并且按照从大到小顺序排列
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC ;

SELECT 配合 LIMIT 子句

例子:

统计所有国家的总人口数量,
将总人口数大于5000w的过滤出来,
并且按照从大到小顺序排列,只显示前三名

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3 OFFSET 0;

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3 OFFSET 3

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

2.9 练习题:

统计中国每个省的总人口数，只打印总人口数小于100w的
SELECT district ,SUM(population) FROM city
WHERE countrycode=‘CHN’
GROUP BY district
HAVING SUM(population)<1000000;
查看中国所有的城市，并按人口数进行排序(从大到小)
SELECT * FROM city WHERE countrycode=‘CHN’
ORDER BY population DESC;
统计中国各个省的总人口数量，按照总人口从大到小排序
SELECT district ,SUM(population) FROM city
WHERE countrycode=‘CHN’
GROUP BY district
ORDER BY SUM(population) DESC ;
统计中国,每个省的总人口,找出总人口大于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 小结
select disctrict , count(name) from city
where countrycode=‘CHN’
group by district
having count(name) >10
order by count(name) desc
limit 3;

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 会做去重操作

多表连接查询(内连接)

作用
单表数据不能满足查询需求时.
例子: 查询世界上小于100人的城市,所在的国家名,国土面积,城市名,人口数
city:
SELECT countrycode,NAME,population FROM city WHERE population<100;
PCN Adamstown 42

country
DESC country;
CODE
NAME
SurfaceArea

SELECT NAME ,SurfaceArea FROM country WHERE CODE=‘PCN’;
Pitcairn
49.00

多表连接基本语法

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

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

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

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

多表连接例子

– 1. 统计zhang3,学习了几门课
SELECT student.sname,COUNT(sc.cno)
FROM student JOIN sc
ON student.sno=sc.sno
WHERE student.sname=‘zhang3’;

– 2. 查询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 student.sname;

– 3. 查询oldguo老师教的学生名和个数.
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 teacher.tname=‘oldguo’
GROUP BY teacher.tname;

– 4. 查询oldguo所教课程的平均分数

SELECT teacher.tname,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 sc.cno;

– 5. 每位老师所教课程的平均分,并按平均分排序
SELECT teacher.tname,course.cname,AVG(sc.score)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
GROUP BY teacher.tname,course.cname
ORDER BY AVG(sc.score)

– 6. 查询oldguo所教的不及格的学生姓名
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 teacher.tname=‘oldguo’ AND sc.score<60

– 7. 查询所有老师所教学生不及格的信息(扩展)

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.tno

– 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.tno

表别名是全局调用的.

列别名:
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 调用