9、select 语句

基础语句

select * from tb1;select * from tb1 limit 3;select name,age from tb1;select name,age from tb1 where age = 25;select * from tb1 where age != 28;select * from tb1 where age >= 25 and age <=28;select name,age from tb1 where age between 25 and 28;select * from tb1 where age = 25 or age = 28;select * from tb1 where age not between 25 and 28;select * from tb1 where age < 25 or age > 28;select * from tb1 where name like 'j%';select * from tb1 where name like 't__';select * from tb1 where name rlike '^t.*';select * from tb1 where age in (22,23,24,25);select * from tb1 where age not in (28,33,43);select * from tb1 order by age;select * from tb1 order by age asc;select * from tb1 order by age desc;select * from tb1 order by age desc,name asc;select distinct age from students; #去重查询select name as StuName,age from tb1;

分组与聚合

select gender,sum(age) from students group by gender;select classid,avg(age) as avgage from students group by classid having avgage > 25;select sum(age) from students where age > 19 group by gender;

多表查询

# 交叉连接select * from t1 cross join t2 cross join t3;select * from t1,t2,t3;# 内连接select * from t1 inner join t2 on t1.t1id=t2.t2id;select * from t1,t2 where t1.t1id=t2.t2id;# 外连接select * from t1 right outer join  t2 on t1id=t2id;select * from t1 left outer join  t2 on t1id=t2id;select * from t1 right  join  t2 on t1id=t2id;select * from t1 left join  t2 on t1id=t2id;#联合查询select * from t1 union select * from t2;     #重复数据合并select * from t2 union all select * from t4; #显示重复记录#全连接>