day04

作者: 藏鋒1013 | 来源:发表于2019-06-20 19:40 被阅读0次

一、SELECT

1.1、作用

获取MySQL中的数据行

1.2、单独使用select

1.2.1 select @@port;

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

1.2.2 select 函数()

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

1.3、SQL标准的使用语法

1.3.1 select 语法执行顺序(单表)

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

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

1.3.2SQL语句应用

1.FROM

例子:

(1)查询city表中的所有数据:

mysql> use world;
mysql> select * from city;   ##----->适合表数据行较少的表,生产中使用较少。
mysql> select * from world.city;

(2)查询name和population的所有值:

mysql> select name,population from city;
mysql> select name,population from world.city;
单表查询练习环境,world数据库下的表介绍:

mysql> 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.3WHERE

例子:

where 配合 等值查询

格式:select * from where 列 xxx

(1)查询中国的城市情况

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

(2)查询美国的城市情况

mysql> select * from world.dity where countrycode='USA';
where 配合 不等值

(3)查询世界上人口小于100人的城市

mysql> select * from world.city where population<100;

(4)查询世界上人口大于10000000的城市

mysql> select * from world.city where population>10000000;
WHERE 配合 模糊

(5)查询国家代号是C开头的
注意:like 语句在MySQL中,不要出现%在前面的情况。因为效率很低,不走索引。

mysql> SELECT * FROM world.city WHERE countrycode LIKE 'C%';

WHERE 配合 逻辑连接符
AND
OR
UNION
UNION ALL

(5)查询城市人口在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

GROUP BY 配合 聚合函数使用
常用聚合函数:
AVG()
COUNT()
SUM()
MAX()
MIN()
GROUP_CONCAT()

例子:

(1)统计每个国家的总人口

SELECT countrycode,SUM(population) 
FROM city
GROUP BY countrycode;

(2)统计每个国家的城市个数

SELECT countrycode,COUNT(id)
FROM city
GROUP BY countrycode;

1.拿什么站队:group by countrycode
2.拿什么统计:城市id、name
3.统计的是什么:count(id)

(3)统计并显示每个国家的省名字列表

SELECT countrycode,GROUP_CONCAT(district)
FROM city
GROUP BY countrycode;

(4)统计中国每个省的城市名列表

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

(5)统计一下中国每个省的总人口数

SELECT district,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district;

(6) 统计一下中国每个省的平均人口

SELECT district,AVG(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district;

1.3.5 HAVING

例子:

(1) 统计中国每个省的总人口大于1000W的省及人口数

SELECT district,AVG(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>10000000;

说明:having后的条件是不走索引的,可以进行一些优化手段处理。

1.3.6 ORDER BY

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

例子:

(1) 查询中国所有的城市,并以人口数降序输出

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

1.3.7 LIMIT

mysql> select *
    -> from world.city
    -> where countrycode='CHN'
    -> order by population desc
    -> limit 10;

例子:

mysql> select *
    -> from world.city
    -> where countrycode='CHN'
    -> order by population desc
    -> limit 5,5;

mysql> select *
    -> from world.city
    -> where countrycode='CHN'
    -> order by population desc
    -> limit 9 offset 10;

说明:
limit M,N         跳过M行,显示N行
limit X offset Y  跳过Y行,显示X行

1.4 多表连接查询

1.4.1 介绍4张测试表的关系

略……

1.4.2 什么时候用

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

1.4.3 怎么去连接多张表

  1. 传统的连接
  2. 自连接(自己扩展)
  3. 内连接
  4. 外连接

1.传统的连接:基于where条件

(1) 找表之间的关系列
(2) 排列查询条件

例子:人口数量小于100人的城市,所在国家的国土面积(城市名,国家名,国土面积)
select world.city.name,country.name,country.surfacearea
from world.city,world.country
where world.city.countrycode=world.country.code
and world.city.population<100;

2.内连接

A B
a.x b.y
1.找表之间的关系列
2.将两表放在join左右
3.将关关联条件放到on后面
4.将所有的查询条件进行罗列
select a.m,b.m
from A
join B
on A.x=B.y
where
group by
order by
limit
例子:(1) 查询人口数量小于100人的国家名,城市名,国土面积
select world.country.name,world.city.name,world.country.surfacearea
from world.country
join world.city
on world.city.population=world.country.code
where world.city.population<100;
(2)查询oldguo老师和他教课程名称
mysql> select school.teacher.tname,school.course.cname
from school.teacher
join school.course
on schooll.teacher.tno=school.course.tno
where school.teacher.tname='oldguo';
或者:

mysql> select school.teacher.tname,school.course.cname
from school.teacher
join school.course
on schooll.teacher.tno=school.course.tno
where school.teacher.tname='oldguo';
(3) 统计一下每门课程的总成绩
mysql> select school.course.cname,sum(school.score.score)
from school.course
join school.score
on school.course.cno=school.score.cno
group by school.course.cname;
5.7版本会报错的情况,在sqlyog中以下操作没问题
但是在命令行中是会报错
mysql> select school.course.cno,school.course.cname,sum(school.score.score)
    -> from school.course
    -> join school.score
    -> on school.course.cno=school.score.cno
    -> group by school.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 
school.course.cno,school.course.cname,sum(school.score.score)
from school.course
join school.score
on school.course.cno=school.score.cno
group by school.course.cno;

3.外连接*****

练习:

(4) 查询oldguo老师教的学生姓名列表
(5) 查询所有老师教的学生姓名列表
(6) 查询oldboy老师教的不及格学生的姓名
(7) 统计zhang3,学习了几门课
(8) 查询zhang3,学习的课程名称有哪些
(9) 查询oldguo老师教的学生名
(10) 查询oldguo所教课程的平均分数
(11) 每位老师所教课程的平均分,并按平均分排序
(12) 查询oldguo所教的不及格的学生姓名
(13) 查询所有老师所教学生不及格的信息

(4): 查询oldguo老师教的学生姓名列表
mysql> select school.teacher.tname,group_concat(school.student.sname)
    -> from school.teacher
    -> join school.course
    -> on school.teacher.tno=school.course.tno
    -> join school.score
    -> on school.course.cno=school.score.cno
    -> join school.student
    -> on school.score.sno=school.student.sno
    -> where school.teacher.tname='oldguo'
    -> group by school.teacher.tname;
(5): 查询所有老师教的学生姓名列表
mysql> SELECT school.teacher.tname,GROUP_CONCAT(school.student.sname)
    -> FROM school.teacher 
    -> JOIN school.course
    -> ON school.teacher.tno = school.course.tno
    -> JOIN school.score
    -> ON school.course.cno = school.score.cno
    -> JOIN school.student 
    -> ON school.score.sno = school.student.sno
    -> GROUP BY school.teacher.tno;
(6): 查询oldboy老师教的不及格学生的姓名
mysql> SELECT teacher.tname,GROUP_CONCAT(student.sname)                                               
    -> FROM teacher 
    -> JOIN course
    -> ON teacher.tno = course.tno
    -> JOIN score
    -> ON course.cno = score.cno
    -> JOIN student 
    -> ON score.sno = student.sno
    -> WHERE teacher.tname='oldboy' AND score.score<60
    -> GROUP BY teacher.tno;
(7) 统计zhang3,学习了几门课
mysql> select student.sname,count(score.cno)
    -> from student
    -> join score
    -> on student.sno=score.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;

4.自连接(自己扩展)

相关文章

网友评论

      本文标题:day04

      本文链接:https://www.haomeiwen.com/subject/fycfqctx.html