孙某某的运维之路

孙富阳， 江湖人称没人称。多年互联网运维工作经验，曾负责过孙布斯大规模集群架构自动化运维管理工作。擅长Web集群架构与自动化运维，曾负责国内某大型博客网站运维工作。

1.查询select

1.select单独使用

1.查询系统变量（配置参数）
#查端口
mysql> select @@port;
#查看工作目录
mysql> select @@basedir;
#查询数据目录
mysql> select @@datadir;
#太麻烦了还不如使用show来查询
#查询所有变量show variables 
mysql> show variables;
#模糊查询变量
mysql> show variables like '%safe%';
2.调用函数
#查询系统当前时间
mysql> select now();
#查询用户
mysql> select user();
#查询当前所在库
mysql> select database();

2.标准select应用

select 列… from 表
where
group by
having
order by
limit

3.select+from使用

#导入一个world库（mysql官方的文件）
下载地址https://downloads.mysql.com/docs/world-db.tar.gz
mysql> source /root/world.sql
##查询表中所有数据
mysql> select * from world.city;
##查询表中部分列的数据
mysql> select name,population from world.city;
###注意这是全表扫描，性能非常差，小表还好，大表会很耗内存

4.select+from+where使用

###where+等值查询
   #查询中国所有的城市
   mysql> select * from world.city where countrycode='chn';
   #查询中国城市名和人口数
   mysql> select name,population from world.city where countrycode='chn';
###where+不等值查询（条件判断符（<> <= >= !=））
   #查询少于100人的城市信息
   mysql> select * from world.city where population<'100';
### where+与或非查询（AND OR）
   ###查询中国人口数超过500万的人数
   mysql> select * from world.city where population>'500' and countrycode='chn';
   ##查询城市名字为yantai或者tianjin的信息
   mysql> select * from world.city where name='yantai' or name='tianjin';
###where+like模糊查询
   #查询国家代号是ch开头的城市信息
   mysql> select * from world.city where  countrycode like 'ch%';
###where+not in + like取反
   mysql> select * from world.city where countrycode not in (select countrycode from world.city where countrycode like 'ch%');
### where+between and
   ##查询人口数在100W到200万之间的城市信息
   select * from city where population between 1000000 and 2000000;
   等价与select * from city where population >=1000000 and population <=2000000;

5.select的group by+聚合函数子句使用

select + from + where + group by +聚合函数（count（），sum（），avg（），max（），min（），group_concat()）

1.group by 作用

按照固定条件进行分组
##例如查询city表没个国家出现的次数
mysql> select CountryCode,count(Name) from world.city group by CountryCode;

2.sql_mode及group by用法

##算一下每个城市的人口数，正确写法，
mysql> select countrycode ,sum(population) from world.city group by countrycode;
###如果有group by ，select后的列，要么是group by条件，要么是在聚合函数里处理

###查一下中国每个省的人口总数
mysql> select countrycode , district,sum(population) from world.city where countrycode='chn' group by countrycode,district ;

##查询中国每个省城市的个数
mysql> select countrycode , district,count(population) from world.city  where countrycode='chn'   group by countrycode,district ;
###查询中国每个省的城市个数，并显示出城市名
mysql> select district,group_concat(name),count(population) from world.city where countrycode='chn' group by district ;

6.select的having  group by（后过滤）

##查询中国每个省的城市个数，显示出城市名，过滤出城市数小于5并且城市以ch开头的省
mysql> select district,group_concat(name),count(population) from world.city where countrycode='chn' group by district having count(population)<5 and group_concat(name) like 'Ch%' ;
#####注意尽量不要使用这个子句，对性能影响十分大

7.select的order by（排序）

#按中国的人口数进行排名
mysql> select * from world.city where countrycode='chn' order by population;
#按中国的人口数从大到小进行排名
mysql> select * from world.city where countrycode='chn' order by population desc ;
#统计中国每个省的总人口，过滤输出总人口超过1000w，从大到小排序
mysql> select District,sum(Population),group_concat(Name) from world.city  where CountryCode='chn' group by District having sum(Population)>10000000 order by sum(Population) desc;

8.select的limit（限制输出）

#统计中国每个省的总人口，过滤输出总人口超过1000w，从大到小排序，然后只输出前5行
mysql> select District,sum(Population) from world.city  where CountryCode='chn' group by District having sum(Population)>5000000 order by sum(Population) desc limit 5 ;
#统计中国每个省的总人口，过滤输出总人口超过1000w，从大到小排序，然后只输出前6-10行
mysql> select District,sum(Population) from world.city  where CountryCode='chn' group by District having sum(Population)>5000000 order by sum(Population) desc limit 5,5;
####上面一条命令等同于下面的命令，意思是跳过5行，显示5行
mysql> select District,sum(Population) from world.city  where CountryCode='chn' group by District having sum(Population)>5000000 order by sum(Population) desc limit 5 offset 5;

9.select的union（将多个查询合并）

#统计中国每个省的总人口，过滤输出总人口超过1000w，从大到小排序，然后只输出前3和后3行
mysql> (select District,sum(Population) from world.city  where CountryCode='chn' group by District having sum(Population)>5000000 order by sum(Population) desc limit 3) union all (select District,sum(Population) from world.city  where CountryCode='chn' group by District having sum(Population)>5000000 order by sum(Population) limit 3) ;

2.多表连接

1.笛卡尔乘积连接方式

创建两个表
create table a (id int not null primary key auto_increment,name varchar(20) not null ,age tinyint);
create table b (aid int not null,addr varchar(20) not null ,telnum char(11));
分别插入数据
Insert into a values(1,’zs’,’18’),(2,’ls’,’19’),(3,’w5’,’20’),(5,’chunchun’,’25’);
Insert into b values(1,’bj’,’110’),(2,’bj’,’120’),(3,’sh’,’119’),(4,’sz’,’114’);
笛卡尔乘积示例
mysql> select * from test.a,test.b;

2.内连接

内连接示例
mysql> select * from test.a,test.b where test.a.id=test.b.aid;
下面效果与上面的结果一样
mysql> select * from test.a join test.b on test.a.id=test.b.aid;

3外连接

外连接分为左外连和右外连
取的就不是集合了，左连接取的是左边的值和他们的集合，右连接取的是右边的值和他们的集合
###左连接，左连接就是左面的全部打印出来，右面的符合条件的打印出来，右连接同理
mysql> select * from test.a left join test.b on test.a.id=test.b.aid;
###右连接
mysql> select * from test.a right join test.b on test.a.id=test.b.aid;
###三个表怎么查询呢？
mysql> select * from a left join b  on  a.id=b.aid left join c on b.aid=c.cid;
取差积
mysql> select * from test.a left join test.b on test.a.id=test.b.aid where aid is null;
PS
mysql> select * from world.city where Name in ('yantai','tianjin');
等效于where name=yantai or name=tianjin

4.多表连接之case语句

mysql> select course.cname,group_concat(case when score.num>80 then student.sname end)'优秀' from course join score on course.cid=score.course_id join student on score.student_id=student.sid group by course.cname ;

3.别名应用

1.给列设置别名

mysql>  select student.sname,avg(score.num) from student join score on student.sid=score.student_id group by student.sname ;

mysql>  select student.sname,avg(score.num) as '平均分' from student join score on student.sid=score.student_id group by student.sname ;

##注意，列别名要放在group by之后的子句才能引用，where无法使用列的别名
mysql>  select student.sname as '姓名',avg(score.num) as '平均分' from student join score on student.sid=score.student_id  group by student.sname having 平均分>60;

2.给表设置别名

from 或者join后定义的表的别名，而且表别名支持所有地方调用
mysql>  select student.sname as '姓名',avg(score.num) as '平均分' from student join score on student.sid=score.student_id  group by student.sname having 平均分>60;

mysql> select a.sname as '姓名',avg(b.num) as '平均分' from student as a join score as b on a.sid=b.student_id group by a.sname having 平均分>60;