day04

2019-06-20 本文已影响0人藏鋒1013

一、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.传统的连接：基于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;

day04

一、SELECT

1.1、作用

1.2、单独使用select

1.2.1 select @@port;

1.2.2 select 函数()

1.3、SQL标准的使用语法

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

1.3.2SQL语句应用

1.FROM

例子：

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

1.3.3WHERE

例子：

where 配合等值查询

where 配合不等值

WHERE 配合模糊

1.3.4 GROUP BY

例子：

1.3.5 HAVING

例子：

1.3.6 ORDER BY

例子：

1.3.7 LIMIT

例子：

1.4 多表连接查询

1.4.1 介绍4张测试表的关系

1.4.2 什么时候用

1.4.3 怎么去连接多张表

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

例子：人口数量小于100人的城市,所在国家的国土面积(城市名，国家名，国土面积)

2.内连接

例子：(1) 查询人口数量小于100人的国家名，城市名，国土面积

(2)查询oldguo老师和他教课程名称

(3) 统计一下每门课程的总成绩

3.外连接*****

练习：

4.自连接(自己扩展)

猜你喜欢

热点阅读

day04

一、SELECT

1.1、作用

1.2、单独使用select

1.2.1 select @@port;

1.2.2 select 函数()

1.3、SQL标准的使用语法

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

1.3.2SQL语句应用

1.FROM

例子：

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

1.3.3WHERE

例子：

where 配合 等值查询

where 配合 不等值

WHERE 配合 模糊

1.3.4 GROUP BY

例子：

1.3.5 HAVING

例子：

1.3.6 ORDER BY

例子：

1.3.7 LIMIT

例子：

1.4 多表连接查询

1.4.1 介绍4张测试表的关系

1.4.2 什么时候用

1.4.3 怎么去连接多张表

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

例子：人口数量小于100人的城市,所在国家的国土面积(城市名，国家名，国土面积)

2.内连接

例子：(1) 查询人口数量小于100人的国家名，城市名，国土面积

(2)查询oldguo老师和他教课程名称

(3) 统计一下每门课程的总成绩

3.外连接*****

练习：

4.自连接(自己扩展)

猜你喜欢

热点阅读

where 配合等值查询

where 配合不等值

WHERE 配合模糊