--表达式不包含字段select id, sn,10from student;--表达式包含一个字段select id, sn, sn +10from student;--表达式包含多个字段select id, sn, sn + id from student;--微查询结果中的列指定别名select id, sn, sn + id si from student;
去重distinct
--使用distinct关键字对某列数据进行去重selectdistinct id from student;
排序order by
--查询id和sn,按照id升序显示select id, sn from student orderby id asc;--使用表达式及别名降序排序select id, sn + id from student orderby sn + id desc;select id, sn +id si from student orderby si desc;--对多个字段进行排序,排序优先级随书写顺序select id, sn from student orderby sn desc, id;
分页查询limit
--第一页select id from student orderby id limit3;select id from student orderby id limit3offset0;--第二页select id from student orderby id limit3offset3;--第三页,如果结果不足三个不会影响select id from student orderby id limit3offset6;
条件查询where
--基本查询select*from student where id <102;select*from student where id < sn;select id, id + sn si from student where id + sn >1000;--AND与OR(AND优先级大于OR)select*from student where id <200and sn <800;select*from student where id <200or sn <800;--范围查询select name from student where id between90and110;select name from student where id in(101,102);--模糊查询select name from student where name like'张%';select name from student where name like'张__';--NULL查询select name from student where id isnotNULL;select name from student where id isNULL;
修改(update)
--将张三的id改为1update student set id =1where name ='张三';--将id倒数前三的同学加上50update student set id = id +50orderby id limit3;--将所有同学的sn更新为原来的两倍update student set sn = sn *2;
删除(delete)
--删除张三的信息deletefrom student where name ='张三';--删除整张表droptable student;