MySQL第二天

標籤：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|desclimit start,count

5/11/2018 6:35:33 PM

MySQL第二天