DQL 数据查询语言

  • SELECT
  • SHOW

SELECT单独使用的情况

#数据库安装目录
select @@basedir;
#查看端口
select @@port;
#查看该参数值
select @@innodb_flush_log_at_trx_commit;
#查看相关参数
show variables like 'innodb%';
#当前数据库名称
select database();
#当前时间
select now();

SELECT通用语法

SELECT `列名`
FROM `表名`
WHERE `条件`
GROUP BY `条件`
HAVING `条件`
ORDER BY `条件`
LIMIT `条件`

学习环境的说明

world.sql

如何熟悉数据库业务

  1. 快速和研发人员打好关系
  2. 找到领导要ER图

SELECT配合FROM子句使用

  • 查询表中所有数据(生产环境不太可能出现这种需求,且特别消耗硬件资源)
SELECT * FROM city;
SELECT id,name,countrycode,district,population FROM city;
  • 查询表中id,和name的值
SELECT id,name FROM city;

SELECT配合WHERE子句使用

  • 查询中国所有的城市名和人口数
SELECT `Name`,Population FROM city WHERE CountryCode='CHN';
  • 世界上小于100人的城市名和人口数
SELECT `Name`,Population FROM city WHERE Population < 100;
  • 查询中国人口数量大于100w的城市名和人口
SELECT `Name`,Population FROM city WHERE Population > 1000000 AND CountryCode='CHN';
  • 查询中国或美国的城市名和人口数
SELECT `Name`,Population FROM city WHERE CountryCode='USA' OR CountryCode='CHN';
SELECT name,population FROM city WHERE countrycode IN ('CHN','USA');
  • 查询人口数量在500w到600w之间的城市名和人口数
SELECT name,population FROM city WHERE population BETWEEN 5000000 AND 6000000;
  • 查询一下contrycode中带有CH开头,城市信息

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

SELECT name FROM city WHERE CountryCode like 'CH%';

SELECT配合GROUP BY子句使用

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

  • 统计每个国家,城市的个数
SELECT CountryCode,COUNT(name) 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;
  • 统计中国每个省城市的名字列表GROUP_CONCAT()

SELECT CountryCode,GROUP_CONCAT(name) FROM city WHERE CountryCode='CHN' GROUP BY District;
SELECT CONCAT(District,':',GROUP_CONCAT(name)) FROM city WHERE CountryCode='CHN' GROUP BY District;

SELECT配合ORDER BY子句使用

DESC从大到小排序,ASC从小到大排序

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

SELECT 配合LIMIT子句

  • 统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列,只显示前三名
  • LIMIT M,N : 跳过M行,显示一共N行
  • LIMIT Y OFFSET X : 跳过X行,显示一共Y行
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC LIMIT 3;
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC LIMIT 3 OFFSET 0;

UNION和UNION ALL

  • 查询中或者美国的城市信息
#以下两条语句结果相同
SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA';
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
  • 面试题:UNION和UNION ALL的区别

UNION ALL不做去重复,UNION会做去重操作

多表连接查询

单表数据不能满足查询需求时

  • 查询世界上小于100人的城市,所在的国家名,国土面积,城市名,人口数
SELECT city.CountryCode,city.`Name`,city.Population,country.`Name`,country.SurfaceArea FROM city JOIN country ON city.`CountryCode`=country.`Code` WHERE city.population<100;

多表连接例子

准备环境

school.sql

  • 统计zhang3,学习了几门课
SELECT count(sc.cno) FROM student JOIN sc ON student.sno=sc.sno WHERE student.sname='zhang3';
  • 查询zhang3,学习的课程名称有哪些
SELECT course.cname FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno WHERE student.sname='zhang3';
  • 查询oldguo老师教的学生名和个数
SELECT COUNT(student.sname),GROUP_CONCAT(student.sname) FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno JOIN teacher ON teacher.tno=course.tno WHERE teacher.tname='oldguo' GROUP BY teacher.tname;
  • 查询oldguo所教课程的平均分数
SELECT course.cname,AVG(sc.score) FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno JOIN teacher ON teacher.tno=course.tno WHERE teacher.tname='oldguo' GROUP BY teacher.tname;
  • 每位老师所教课程的平均分,并按平均分排序
SELECT teacher.tname,course.cname,AVG(sc.score) FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno JOIN teacher ON teacher.tno=course.tno GROUP BY teacher.tname ORDER BY AVG(sc.score) DESC;
  • 查询oldguo所教的不及格的学生姓名
SELECT teacher.tname,student.sname,sc.score FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno JOIN teacher ON teacher.tno=course.tno WHERE teacher.tname='oldguo' AND sc.score<60;
  • 查询所有老师所教学生不及格的信息
SELECT teacher.tname,GROUP_CONCAT(student.sname,':',sc.score) FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno JOIN teacher ON teacher.tno=course.tno WHERE sc.score<60 GROUP BY teacher.tname;

别名应用

表别名是全局调用的,列别名可以被HAVING和ORDER BY调用

SELECT teacher.tname AS '教师',GROUP_CONCAT(student.sname,':',sc.score) AS '学生' FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno JOIN teacher ON teacher.tno=course.tno WHERE sc.score<60 GROUP BY '教师';