首页 > 数据库技术 > 详细

MySQL第二天

时间:2018-05-11 23:15:32      阅读:134      评论:0      收藏:0      [点我收藏+]

标签:MySQL   个人笔记   学习过程   

查询

模糊查询(like)

范围查询

非连续 select * from students where id in(1,3,8);

连续 select * from students where id between 3 and 8 and gender=1;

聚合

快速得到统计数据

  1. count(*)
  2. sum()
  3. max()
  4. min()
  5. avg()

示例: select count(*) from students
select sum(id) from students where isDelete=0 ;
子查询:select * from students where id=(select min(id) from students where idDelete=0);

分组(group by)

select gender as xb,count(*) as rs from students group by gender;
分组后筛选
select gender,count(*) from students group by gender having gender=1;
having&where:

where是对原始数据的筛选,针对from

having是对分组后结果进行筛选,针对group by 技术分享图片

排序(order by)

默认升序

select * from 表名 order by 列1 asc|desc,列2 asc|desc,……    

分页

select * from 表名 limit start,count   
  • 从start开始(0),获取count条数据
  • 第n页,显示m条数据
    select * from students<br/>where isdelete=0<br/>limit (n-1)*m,m;

    总结

    select (distinct) * 
    from 表名 
    where .... 
    group by ... 
    having ... 
    order by ... 
    limit start,count;  

    连接查询

    表与表之间建立关系

    select students.name,subjects.title,scores.score
    from scores
    inner join students on scores.stuid=students.id
    inner join subjects on scores.subid=subjects.id;


通用模板

select distinct 列 *
from 表名1 
inner|left|right join 表名2 on 表之间的关系
group by ……having……
order by ……asc|desc
limit start,count

5/11/2018 6:35:33 PM

MySQL第二天

标签:MySQL   个人笔记   学习过程   

原文:http://blog.51cto.com/13736429/2115364

(0)
(0)
   
举报
评论 一句话评论(0
0条  
登录后才能评论!
© 2014 bubuko.com 版权所有 鲁ICP备09046678号-4
打开技术之扣,分享程序人生!
             

鲁公网安备 37021202000002号