MySQL_Table_Select

原创
2016/05/04 16:20
阅读数 21

    1. Select field

select id, stuName, sex from t_student;
select * from t_student;

    2. Select + where

select * from t_student where id=7;
select * from t_student where age>22;

 

    3. Select + in

select * from t_student where age in (21, 23);
select * from t_student where age not in (21, 23);

    4. Select + between ... and ...

select * from t_student where age between 21 and 24;
select * from t_student where age not between 21 and 24;

    5. Select + like

select * from t_student where stuName like '张三';
select * from t_student where stuName like '张三%';
select * from t_student where stuName like '张三_';
select * from t_student where stuName like '%张三%';

  

   ps: %: any word;

           _: only one word;

    6. Select + is null

select * from t_student where sex is null;
select * from t_student where sex is not null;

    7. Select + and

select * from t_student where gradeName='一年级' and age=23;

    8. Select + or

select * from t_student where gradeName='一年级' or age=23;

    9. Select + distinct

select distinct gradeName from t_student;

    10. Select + order by

select * from t_student order by age;
select * from t_student order by age asc;
select * from t_student order by age desc;

    11. Select + group by

select * from t_student group by gradeName;        /* Useless for only 'group by' */
select gradeName, group_concat(stuName) from t_student group by gradeName;    
                                                   /* group_concat */
select gradeName, count(stuName) from t_student group by gradeName;    
                                                   /* Aggregate function: count() */
select gradeName, count(stuName) from t_student group by gradeName having count(stuName)>3;
                                                   /* With having condition */
select gradeName, count(stuName) from t_student group by gradeName with rollup;
select gradeName, group_concat(stuName) from t_student group by gradeName with rollup;
                                                   /* With rollup *

    12. Select + limit

select * from t_student limit 0, 5;
select * from t_student limit 5, 5;
select * from t_student limit 10, 5;

展开阅读全文
打赏
1
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
1
分享
返回顶部
顶部