select * from 表名;
select id,name as t(name这一列的新列名) from t1 where id>10 or name='王五';
select name,age,1 from t;
select * from t where id in (1,5,12);
select * from t where id not in (1,5,12);
select * from t where id between 5 and 12;
select * from t where id in (select nid from t1);
select * from t where name like '%a'; -- 查询以a结尾的名字
--截取部分数据
select * from t limit 10; --查询前十条数据
select * from t limit 2,9;==select * from t limit 9 offset =2 --指从2开始，取出前九条数据
--排序
select * from t order by id desc; --降序排列
select * from t order by id asc; --升序排列
select * from t order by id desc limit 10;
select * from t order by 列1 desc ,列2 asc;
--分组
select min(id), max(id), sum(id), avg(id) , part_id from t group by part_id;
select count(id),part_id from t group by part_id;
select count(id) max(id),part_id from t group by part_id having count(id)>2; --筛选出count(id)大于2的所有数据行
--where后面不能加聚合函数
--连表操作
select * from t1,t2 where t1.part_id=t2.id;
select * from t1 left join t2 on t1.part_id=t2.id; --t1表的数据会全部显示
select * from t1 right join t2 on t1.part_id=t2.id; --t2表会全部显示
select * from t1 inner join t2 on t1.part_id=t2.id --显示两个表的共有部分
select sid from student union select student_id from score; --进行上下连表，会自动去重
select sid from student union all select student_id from score; --进行上下连表，但不自动去重
--零时表
select * from (select * from t where id>60) as b;
--笛卡尔积
select * from t as a,t as b;
--三目运算
select case when min(number) <60 then 0 else min(number) end as 't11' from score group by student_id;
select dinstinct student_id from t where number<60; ---dinstinct是为了去重
select avg(if(isnull(score.number),0,score.number)) from score;
--聚合函数
min,max,count,sun,avg
--筛选条件
in ,not in,between and ,!=,and,or
--通配符
a%:指以a开头的任意字符
a_:指以a开头的2长度的字符