MySQL基础3——常用命令

1.MySQL分组求TopN

分组求出成绩之和
SELECT sum(score),studentId from score GROUP BY studentId; 

两表联合
select * from student left join (SELECT sum(score) as score,studentId from score GROUP BY studentId) as stu on student.id =stu.studentId;

分组求TopN
select * from 
(select * from student left join 
(SELECT sum(score) as score,studentId from score GROUP BY studentId) as stu on student.id =stu.studentId) as s1 
where 3>
(select count(*) from
(select * from student left join (SELECT sum(score) as score,studentId from score GROUP BY studentId) as stu on student.id =stu.studentId)as s2 where s1.sex =s2.sex and s1.score <s2.score);

 

2.求每七天的平均数

时间相减
 SELECT DATEDIFF(datetime,'2018-6-1') from test;
序号以7做运算得到一个划分的范围,向下取整得到具体范围
  SELECT DATEDIFF(datetime,'2018-6-1')/7 from test;
    SELECT Floor(DATEDIFF(datetime,'2018-6-1')/7)*7 from test;
每一周的第一天和最后一天相加
 每一周的第一天
select DATE_ADD('2018-6-1',INTERVAL Floor(DATEDIFF(datetime,'2018-6-1')/7)*7 day)from test;

 每一周的最后一天
select DATE_ADD('2018-6-1',INTERVAL Floor(DATEDIFF(datetime,'2018-6-1')/7)*7+6 day)from test;
拼接
select CONCAT(
(select DATE_ADD('2018-6-1',INTERVAL Floor(DATEDIFF(datetime,'2018-6-1')/7)*7 day)),'~',
(select DATE_ADD('2018-6-1',INTERVAL Floor(DATEDIFF(datetime,'2018-6-1')/7)*7+6 day)))from test;

3.连表联查

3.1连表联查
　　　　union:结果集进行合并(纵向合并)
　　格式:
　　　　查询语句 union 查询语句
　　注意:
　　　　查询列数必须相同
　　　　字段为第一个sql语句的字段
　　　　union默认去重
　　　　union all不去重

3.2 left join(以左表为基准关联右表中的数据)
　　格式:
　　　　select * from 左表 left join 右表 on 关联条件;
　　注意:
　　　　左表匹配不到右表,以null不全
　　　　右表匹配不到左表,不显示

3.3right join(以右表为基准关联左表中的数据)
　　格式:
　　　　select * from 右表 left join 左表 on 关联条件;
　　注意:
　　　　左表匹配不到右表,不显示
　　　　右表匹配不到左表,以null不全

3.4 inner join(求两张表的交集)
　　格式:
　　　　select * from 右表 inner join 左表 on 关联条件;
　　注意:
　　　　关联条件可写可不写
　　　　如果不写可以写为select * from 表1,表2 where 子句;

 

4 case的使用

-- case
-- when 条件一 then 要显示的值1或者语句
-- when 条件一 then 要显示的值1或者语句
-- when 条件一 then 要显示的值1或者语句
-- ...
-- else 要显示的值1或者语句
-- end

 

5.MySQL视图：由查询结果得到的一张虚拟表(临时表,虚表),虚拟表和基本表有一对一和一对多的关系

5.1 创建视图

　　　　create view 视图名称 as 查询语句

5.2 视图可以实现的功能

　　　　一对一(数据和基本表一样):增删改查
　　　　一对一(数据由基本表聚合):查
　　　　一对多(数据由基本表聚合):查
　　　　一对多(数据由连表联查):查改
注意:虚拟表不存储任何数据,数据存储在基本表中,基本表当中数据发生改变虚拟表中的数据也能发生改变
创建视图,查询语句中不能出现子查询(不能作为一张表,可以作为结果进行筛选),把子查询作为视图之后再创建视图

5.3 查询视图结构:
　　　　desc 视图名称;
　　　　show create view/table 视图名称;

5.4 删除视图:
　　　　drop view 视图名称; 

 

6. MySQL执行计划：　　　　　

　　　　　　　　from--where--group by--select--having--order by
　　　　　　　　from:需要从哪个数据表检索数据
　　　　　　　　where:过滤表中数据的条件
　　　　　　　　group by:如何将上面过滤出的数据分组
　　　　　　　　having:对上面已经分组的数据进行过滤的条件
　　　　　　　　select:查看结果集中的哪个列，或列的计算结果
　　　　　　　　order by :按照什么样的顺序来查看返回的数据

7. 索引
　　　　添加:
　　　　　　alter table 表名 add index 索引名称(字段);
　　　　删除:
　　　　　　alter table 表名 drop index 索引名称;
　　　　注意:每个字段都可以添加索引
　　　　　　主键默认含有索引
　　　　　　不要每个字段都添加索引(索引也需要消耗资源),只需要给常用字段添加索引

8. 事务:批处理,保证多个sql语句一起执行成功或一起执行失败(回滚撤销当前操作)

　　开始事务:
　　　　　　begin;
　　回滚事务:
　　　　　　rollback;
　　提交事务:
　　　　　　commit;
　　结束事务:
　　　　　　end;

　　注意:没有commit或end之前,事务中的所有操作都是临时的(在缓存中的)
　　　　commit或end之后,把这些临时的(在缓存中的)数据提交到原始数据中进行修改
　　　　end默认执行commit;

 

9. shell操作mysql

#!/bin/sh
MYSQL="mysql -hmaster -uroot -p123456"
sql="select * from shujia.student where sex='0'"
result="$($MYSQL -e "$sql")"
echo "$result"