DBA第五天

1.DQL 介绍(查询语句)(高要求)

select
show

2.select语句的应用

2.1 select 单独使用的情况
比如在数据库中查看一下端口
mysql> select @@port;
比如basedir的路径
mysql>select @@basedir;
查看当前所在的库
mysql>select database();
查看当前所在的时间
mysql>select now();
(这都是一些简单的语句)
接下来到了重点

2.2 select 通用语法 (单表)
select 列
from 表
where 条件
group by 条件
having 条件
order by 条件
limit

2.3 学习环境说明
word数据库
city 城市表
country 国家表
countrylanguage 国家语言

db01 [world]>desc city;
+-------------+----------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO  | PRI | NULL    | auto_increment |
| Name        | char(35) | NO  |    |        |                |
| CountryCode | char(3)  | NO  | MUL |        |                |
| District    | char(20) | NO  |    |        |                |
| Population  | int(11)  | NO  |    | 0      |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

ID :城市序列号
name :城市名称
countrycode :国家代码
district :区域: 中国 省 美国 州
population :人口数

(作为DBA在前一个月必须熟悉怎么样查询,明白存储的是什么数据)

那么如何熟悉数据库的业务呢?
1.desc (查看表结构)或者 show create table
2.快速的和开发人员打好关系,了解习惯内容
3.可以看一下部分的表内容来判断具体是干什么的

select * from city limit 5;(就查看一下前五行)

4.找领导要ER图(一般都会有的)

2.4 select 配合 from 子句使用

select 列,列,列 from 表

例子:
1.查询表中所有的信息(生产中几乎没有这种需求)

USE world;
select id ,name ,disctrict ,population from city;
或着:
select * from city;

2.5 select 配合 where子句使用

select 列,列,列 from 表 where 过滤条件

例子:
1.查询中国所有的城市名和人口数

select name , population,from city
where countrycode='chn';

where 配合比较判断查询(< >= <=)
例子:
1.查询一下世界上人口数少于100的城市名和人口数

select name ,population from city
where population <100;

where配合 逻辑连接符号(and or)
例子:
1.查询中国人口数量大于1000w的城市名和人口数

select name ,population from city
where countrycode='chn' and population>1000000; 

2.查询中国或美国城市名和人口数

select name ,population from city
where countrycode='chn' or 'usa'

3.查询世界上人口数量在500w到600w之间的城市名和人数

select name,population from city
where population>5000000 and population<6000000;
或者
select name,population from city
where population between 5000000 and 6000000;

where配合like子句模糊查询
例子
1.查询一下countrycode中有c开头的城市信息

select * from city
where countrycode like 'ch%';

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

where配合in语句
例子
1.查询中国或美国的城市信息.

select name ,population from city
where countrycode='chn' or countrycode='usa';
或者:
select name ,population from city
where countrycode in ('chn'','usa');

2.5 select 配合 group by + 聚合函数应用

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

2.5.2 GROUP BY
将某一列有共同条件的数据行,分成一组,然后进行聚合函数操作.
例子:
1.统计每个国家的城市个数

select countrycode,count(id) from city (count函数内部要加上数数的名,以什么来计数)
group by countrycode;

2.统计每个国家的总人口数

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

3.统计每个国家省的个数

select countrycode,count(distinct district)  from city (distinct:去重.)
group by countrycode; 

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

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

5.统计中国每个省城市的个数

select district,count(id) from city
where countrycode='chn'
group by district;

6.统计中国每个省城市的名字列表

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

2.6 select配合having应用
例子
1.统计所有国家的总人口数量,将总人口大于1亿的过滤出来

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

(having和where的区别就在于做完分组聚合后再次做判断的时候用having)

2.7 select 配合order by子句 (order by也就是排序)
例子:
1.统计所有国家的总人口数量,将总人口大于5千万的,并且从大到小排序出来

select countrycode,sum(population) from city
group by countrycode
having sum(population)>50000000
order by sum(population) dest;

2.8 select 配合 limit 子句
例子:
1.统计所有国家的总人口数量,将总人口大于5千万的,并且从大到小排序出来,只显示前三行.

select countrycode,sum(population) from city
group by countrycode
having sum(population)>50000000
order by sum(population) dest
limit 3;

2.统计所有国家的总人口数量,将总人口大于5千万的,并且从大到小排序出来,只显示前4,5,6行.

select countrycode,sum(population) from city
group by countrycode
having sum(population)>50000000
order by sum(population) dest
limit 3,3;(意思为跳过前三行,显示后三行)

limit m,n;(跳过m行,显示后n行)
limit y offset x;(跳过x行,显示后y行)

1.统计中国每个省的总人口数,只打印总人口数小于100w的

select district ,sum(population) from city
where countrycode='chn'
group by district
having sum(population)<1000000;

2.查看中国所有城市,并按照人口数进行排序(从大到小)

select * from city
where countrycode='chn' 
order by population 
desc;

3.统计中国各个省的总人口数,按照总人口从大到小排序

select district, sum(population) from city
where countryscode='chn'
group by district
order by sum(population) desc;

4.统计中国每个省的总人口数,找出总人口大于500w的,
并按照人口从大到小排序,只显示前三名.

select district,sum(population) from city
where countrycode='chn'
group by district
having sum(population)>5000000
order by sum(population) desc
limit 3;

2.10 小结
select 列, 聚合函数 from 什么表
where (先拿where条件把整个表进行切割过滤) 比如 where countrycode=‘chn’ 用这个条件将表进行过滤
group by (进行分组,如果有分组条件的话) 显示出列以及函数统计的数值
having (将上面处理过的数据又进行一次条件匹配过滤)比如 having count(name) >10 将名字大于10的过滤出来
order by (以上处理完的结果在进行排序) order by count(name) desc 以name的个数进行排序.
limit 3;(只显示排名靠前的3位)

3.多表连接查询(内连接)

3.1作用
单表数据不能满族查询需求时.
例子:查询世界上小于100人的城市,所在的国家名,国土面积,城市名,人口数

city
select countrycode,name,population from city
where population<100;

3.2多表连接基本语法要求
1 . 最核心的是,找到多张表之前的关联条件列
2. 列书写时,必须是:表名.列
3. 所有涉及到的查询列,都放在select后
4.将所有的过滤,分组,排序等条件按照顺序写在on的后面.

例子:
查询世界上小于100人的城市,所在的国家名,国土面积,城市名,人口数

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

5.多张表

a
join b
on a.x=b.x
join c
on b.m=c.n

3.3 学生管理系统准备

use school
student ：学生表
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno：     教师编号
tname：教师名字

course ：课程表
cno：  课程编号
cname：课程名字
tno：  教师编号

score  ：成绩表
sno：  学号
cno：  课程编号
score：成绩

– 项目构建

drop database school;
CREATE DATABASE school CHARSET utf8;
USE school

CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED  NOT NULL COMMENT '年龄',
ssex  ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL  COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT  NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;

INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');

INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');

INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');


INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

例子:
1.统计zhang3,学习了几门课

select student.sname ,count(sc.cno)
student join sc
on student.sno=sc.sno(也就是关联条件,两张表关联上了)
where student.sname='zhang3';

2.查询zhang3,学习的课程名称有哪些?

select student.name,course.cname from student
join sc
on student.sno=sc.sno
join course
on sc.con=course.cno
where student.sname='zhang3';

3.查询oldguo老师教的学生名和个数.

select teacher.tname,group_count(student.sname),count(student.sname)   (group_count统计在一行上)
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;

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

select teacher.tname,avg(sc.score)
from teacher
join course
on teacher.ton=course.ton
join sc
on course.cno=sc.cno
where teacher.tname='oldguo';

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

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.tname,course.cname;
order by avg(sc.score)

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

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

8. 别名应用
   表别名
   意思就是用一个字母代替这个表名(代码量少了)(全局调用)
   列别名
   意思就是用一个字幕代替这个列名(列调用)