mysql-04

上节重点难点回顾：

1. 数据类型 
2. 列属性
primary  key 
unique 
not null 
default 
auto_increment 
unsigned 
comment 

3. 各种SQL语句使用场景
DDL: create database  create table drop database drop table alter database alter table truncate 
DML : insert  update  delete 

insert into  oldguo(name,age,gender) 
values
('zhangsan',18,'m'),
('zhs',18,'m');
update  where
delete  where 

4. 命令记不住
命令太长

collation 校对规则
大小写是否敏感

select
1.1 作用

获取MySQL中的数据行

1.2 单独使用select
1.2.1 select @@xxxx;获取参数信息。

mysql> select @@port;
mysql> show variables like '%innodb%';

1.2.2 select 函数();

mysql> select database();
mysql> select now();
mysql> select version();

1.3 SQL92标准的使用语法
1.3.1 select语法执行顺序（单表）

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

--- 1.3.2 FROM

--- 例子：查询city表中的所有数据
USE world;
SELECT * FROM city;   --->适合表数据行较少，生产中使用较少。
SELECT * FROM world.city;
--- 例子： 查询name和population的所有值
SELECT NAME , population FROM city;
SELECT NAME , population FROM world.city;

单表查询练习环境：world数据库下表介绍
SHOW TABLES FROM world;
city(城市): 
DESC city;
id: 自增的无关列，数据行的需要
NAME: 城市名字 
countrycode：城市所在的国家代号，CHN,USA,JPN。。。。
district : 城市的所在的区域，中国是省的意思，美国是洲的意思
population: 城市的人口数量
说明: 此表是历史数据，仅供学习交流使用。

熟悉业务：
刚入职时，DBA的任务
1. 搞清楚架构 
通过公司架构图，搞清楚数据库的物理架构
1-2天
逻辑结构：
（1）生产库的信息（容易达到）
（2）库下表的信息（非常复杂）
    1. 开发和业务人员，搞好关系
    2. 搞到ER图（PD）
    3. 啥都没有怎么怎么办？
       （1） 找到建表语句，如果有注释，读懂注释。如果没有注释，只能根据列名翻译
       （2） 找到表中部分数据 ，分析数据特点，达到了解列功能的目录

1.3.3 where
--- 1.3.3 WHERE

--- 例子：
--- 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的城市
略。

--- WHERE 配合 模糊(LIKE)
--- 查询国家代号是C开头的城市
SELECT *  
FROM   world.city  
WHERE countrycode 
LIKE 'C%';

--- 注意：like 语句在MySQL中，不要出现%在前面的情况。因为效率很低，不走索引。
--- 错误的里
SELECT *  
FROM   world.city  
WHERE countrycode 
LIKE '%C%';

--- WHERE 配合 逻辑连接符(AND OR)
--- 查询城市人口在1w到2w之间的城市
SELECT * 
FROM city
WHERE population >= 10000 
AND Population <= 20000;

SELECT * 
FROM city 
WHERE population 
BETWEEN 10000 AND 20000;

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

--- 1.3.4 GROUP BY 配合聚合函数应用

常用聚合函数：
AVG()
COUNT()
SUM()
MAX()
MIN()
GROUP_CONCAT()

--- 统计每个国家的总人口 
SELECT   countrycode,SUM(population) FROM city GROUP BY countrycode ;
--- 统计每个国家的城市个数
1.拿什么站队
GROUP 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 

--- 统计一下中国,每个省的平均人口
SELECT  district ,AVG(population) FROM city 
WHERE countrycode='CHN'
GROUP BY district

--- 1.3.5 HAVING

--- 统计中国，每个省的总人口大于1000w的省及人口数
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 * FROM city WHERE countrycode='CHN' ORDER BY  population DESC;

--- 1.3.7 LIMIT

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

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

SELECT * 
FROM city 
WHERE countrycode='CHN' 
ORDER BY  population DESC
LIMIT 5,3;

SELECT * 
FROM city 
WHERE countrycode='CHN' 
ORDER BY  population DESC
LIMIT 3 OFFSET 5;

LIMIT M,N     跳过M行，显示N行
LIMIT X OFFSET Y 跳过Y行，显示X行

1.4 多表连接查询
1.4.1 介绍4张测试表的关系

略。

1.4.2 什么时候用？

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

1.4.3 怎么去多表连接查询

（1）传统的连接：基于where条件
1. 找表之间的关系列 
2. 排列查询条件
select name,countrycode from city whrere population<100;
PCN
select name,surfacearea from country where code='PCN'
--- 人口数量小于100人的城市,所在国家的国土面积(城市名，国家名，国土面积)
select city.name,country.name ,country.surfacearea 
from city,country 
where city.countrycode = country.code 
and city.population<100

（2）内连接 *****
A   B
A.x   B.y 
1. 找表之间的关系列 
2. 将两表放在join左右
3. 将关联条件了放在on后面
4. 将所有的查询条件进行罗列
select A.m,B.n
from  
A  join  B
on A.x=B.y
where 
group by 
order by 
limit

--- 例子：

--- 1. 查询人口数量小于100人的国家名，城市名，国土面积
SELECT country.name,city.name,country.surfacearea
FROM 
city JOIN country
ON city.countrycode=country.code
WHERE city.population<100;
--- 2. 查询oldguo老师和他教课程名称
SELECT teacher.tname ,course.cname
FROM teacher 
JOIN course 
ON teacher.tno=course.tno
WHERE teacher.tname='oldguo';

SELECT teacher.`tname` ,course.`cname`
FROM teacher 
JOIN course 
ON teacher.`tno`=course.`tno`
WHERE teacher.`tname`='oldboy';
--- 3. 统计一下每门课程的总成绩
SELECT course.cname,SUM(sc.score)
FROM course  
JOIN sc 
ON course.cno = sc.cno
GROUP BY 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;

mysql> 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

1. 在select后面出现的列，不是分组条件，并且没有在函数中出现。
2. 如果group by 后是主键列或者是唯一条件列，不会报出错误。如下：
SELECT
course.cno,course.cname,SUM(sc.score)                                                                    FROM course  
JOIN sc 
ON course.cno = sc.cno
GROUP BY course.cno;

（3）外链接 ****
自连接（自己了解）
--- 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(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='oldboy' 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';


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

--- 10.查询oldguo所教课程的平均分数

SELECT teacher.tname ,course.`cname`,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 ,course.`cname`,AVG(sc.`score`)
FROM teacher
JOIN course
ON teacher.`tno`=course.`tno`
JOIN sc
ON course.`cno`=sc.`cno`
ORDER BY AVG(sc.`score`);

--- 12.查询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' AND sc.score<60
GROUP BY teacher.tno;

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

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 sc.score<60;

2. show  
3. Information_schema

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

mysql-04

推荐阅读更多精彩内容