Day04-SQL基础应用+select练习

DQL使用

1. select

1.1 作用

对表中的数据行进行查询

1.2 单独使用select

1.2.1 select @@xxxx;获取简单参数信息

-- 查询my.cnf里面定义的端口

3306 [(none)]>select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

3306 [(none)]>show variables like '%innodb%'; #详细参数列表

1.2.2 select 函数( );

-- 查看当前时间

3306 [(none)]>select now();
+---------------------+
| now()               |
+---------------------+
| 2019-06-18 09:27:04 |
+---------------------+
1 row in set (0.00 sec)

-- 查看当前所在库

3306 [(none)]>use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
3306 [mysql]>select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

-- 查看数据库版本

3306 [mysql]>select version();
+------------+
| version()  |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.09 sec)

1.3 SQL 92标准的使用语法

1.3.1 select 语法执行顺序（单表）

select开始 ---》
from子句---》
where子句---》
group by子句---》
select后执行条件---》
having子句---》
order by子句---》
limit

1.3.2 from(select后面必须跟from)

-- 例1：查询city表中的所有数据

use world;
select * from city; #查所有数据，这种方法仅适合数据行较少的表（小表），生产中使用较少，可能会造成数据库查询缓慢。
select * from world.city;

-- 例2：查询name和population的所有值。（查看2列的值）

select name , population from city;
select name , population form world.city;  #推荐使用这种方法

1.3.3 单表查询练习环境：world数据库下的表介绍

查看库中所有表

show tables from world;
city:（城市）
desc city;
id：自增的无关列，数据行的需要
name：城市名字
countrycode：城市所在的代号，CHN（中国）、USA（美国）、JPN（日本）……
district：中国是省的意思,美国是洲的意思
population：城市人口数量
说明：该数据为历史数据，仅供学习交流使用。

刚入职时，如何熟悉业务：

刚入职时DBA的任务，就是熟悉业务。
1. 先搞清楚架构，通过公司的架构图，搞清楚数据库的物理架构。（多少台机器，使用什么样的软件），一般时间为1-2天。
2. 逻辑结构（2-3周内）
（1）生产库的信息（库的个数、库名）
（2）库下面表的信息（非常复杂）
1. 与业务人员和开发搞好关系，从他们口中得知详细信息。
2. 搞到ER图（实景关系图，PD）
3. 啥都没有怎么办？
（1）找到建表语句，如果有注释，读懂注释就可以了。如果没有注释，只能根据列名翻译。
（2）找到表中部分数据，分析数据特点，达到了解数据功能的目的。

1.3.3 where(相当于Linux中的grep)

-- 例子1（生产常用）：
-- where配合等值（=）查询
-- 查询中国的城市信息

select * from world.city where countrycode='CHN';

-- 查询美国的城市信息

select * from world.city where countrycode='USA';

-- where配置不等值（>、<、>=、<=、<>）
-- 查询世界人口数量小于100人的城市

select * from world.city where population<100;

-- 查询世界人口数量大于10000000人口的城市

select * from world.city where population>10000000;

-- where 配合模糊查询（like）

-- 查询国家代号是C开头的城市

select * from world.city where countrycode like 'C%';
--- 注意：like语句在MySQL中，不要出现%在前的情况。因为效率很低，不走索引。

-- where 配合逻辑连接符（and和or）
-- 查询城市人口在10000到20000之间的城市（and）

select * from city where population > 10000 and population < 20000;
select * from city where population between 10000 and 20000;

-- 查询中国或美国的城市信息（or）

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'; ##建议使用这种

面试题：union与union all的区别：

union [distinct]合并
union all 不合并（性能更好，不用排序）

1.3.4 group by(分组条件) 配合聚合函数应用

常用聚合函数：

avg()：平均值
count()：计算
sum()：总数
max()：最大
min()：最小
group_concat()：列转行

-- 统计全世界每个国家的总人口数

select countrycode,sum(population) from city group by countrycode;

解题思路
-- 统计每个国家的城市个数

1. 拿什么站队
国家（countrycode）--->goup by countrycode
2. 拿什么统计
城市ID或城市名name
3. 统计的是什么
count(id)

select countrycode,count(id) from city group by countrycode;

-- 统计并显示每个国家省的名字列表

select countrycode,group_concat(district) from city group by countrycode;

-- 统计中国每个省的城市列表名

select district,group_concat(name) from city where countrycode='CHN' group by district;

-- 统计中国每个省的总人口数

select district,sum(population) from city where countrycode='CHN' group by district;

1.3.5 having（二级过滤）

--- 统计中国每个省的总人口数大于1千万的省人口数

select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>10000000;
结论：having后的条件是不走索引的，可以进行一些优化手段。

1.3.6 order by

-- 中国每个省的总人口数总人口数从大到小显示

select District,sum(population) from city where CountryCode='CHN' group by District order by sum(population) desc;

-- 中国每个省的总人口数从小到大显示

select District,sum(population) from city where CountryCode='CHN' group by District order by sum(population);

--- 例子：查询中国所有的城市，并以人口数降序输出

SELECT * FROM city WHERE countrycode='CHN' ORDER BY  population DESC;

1.3.7 limit

-- 显示中国城市人口数排在前5的城市

SELECT * FROM city WHERE countrycode='CHN' ORDER BY  population DESC limit 5; 显示前5名

-- 显示中国城市人口数排在6-10名的城市

SELECT * FROM city WHERE countrycode='CHN' ORDER BY  population DESC limit 5,5;#跳过前5行。显示后5行

SELECT * FROM city WHERE countrycode='CHN' ORDER BY  population DESC limit 5 offset 5;
limit m，n 跳过m行，显示n行
limit x offset y 跳过y行，显示x行

1.4 多表连接查询

1.4.1 介绍测试表的关系

1.4.2 为什么要使用多表连接查询？

需要查询的数据时来自于多张表时

1.4.3 怎么去多表连接查询

（1）传统的连接：基于where条件（了解）
（2）自连接（了解、自行扩展）
（3）内连接#常用（超重点）
1. 找表之间的关系列
2. 排列查询条件
3. 将关联条件列放在on 后面（on A.X=B.X）
4. 将所有的查询条件进行罗列
select A jioin B on A.X=B.y where group by oreder by limit;

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

select country.name,city.name,country.surfacerea
from city join country
on city.countrycode=country.code
where city.population<100;

-- 查询oldguo老师和他教的课程名称。

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

-- 统计每门课程的总成绩

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

-- 5.7 版本会报错的情况,在sqlyog中以下操作没问题, 但是在命令行上是会报错

SELECT course.cno,course.cname,SUM(sc.score)
FROM course  
JOIN sc 
ON course.cno = sc.cno
GROUP BY course.cname;

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.course.cno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
（原因是：开了sql_mode=only_full_group_by导致的
1. 在select 后面出现的列cno，即不是分组条件（group by），也没有在函数内包裹（没有在函数括号内）出现。），所以报错了。
2. 如果group by后是主键列，或者是唯一列,就能行。如下：
SELECT course.cno,course.cname,SUM(sc.score)
FROM course  
JOIN sc 
ON course.cno = sc.cno
GROUP BY course.cno;

-- 例子：
--- 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(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 teacher.tname='oldguo' 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'
group by course.cno;

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

-- 10.查询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';

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

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.tno
order by avg(sc.score) desc;

-- 12.查询oldguo所教的不及格的学生姓名

select teacher.tname,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 student.sno;

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

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

最后编辑于：2019.06.20 05:51:32

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 199,711评论 5赞 468
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 83,932评论 2赞 376
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 146,770评论 0赞 330
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 53,799评论 1赞 271
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 62,697评论 5赞 359
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,069评论 1赞 276
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,535评论 3赞 390
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,200评论 0赞 254
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,353评论 1赞 294
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,290评论 2赞 317
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,331评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,020评论 3赞 315
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,610评论 3赞 303
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,694评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,927评论 1赞 255
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,330评论 2赞 346
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 41,904评论 2赞 341