mysql 查询语句

select * from school;

指定查询列

select id,name from school;

起别名

select id,name as 姓名,sex as 性别,age as 年龄 from student;

去重查询

select distinct sex from student;

where查询过滤

select * from student where sex='男' and age>18;

比较运算符 < <= > >= !=

select * from student where age<18;
select * from student where age<=20;
select * from student where age>18;
select * from student where age>=18;
select * from student where age!=18;

逻辑运算符 (AND，OR, NOT)

select * from student where age>18 and age<20;

select * from student where age<19 or age>20;

select * from student where `name` not like "王五";

范围运算符 BETWEEN

select * from student where age between 18 and 20;

列表运算符 IN

select * from student where age in(19,20);

非条件（!=）

select * from student where `name` != "王五";

模糊查询 LIKE

通配符: %任意多个字符,_单个字符,[]指定范围的单个字符,[^]不在指定范围的单个字符

select * from student where name like '%小%';

order by排序语句

asc升序(默认)，desc降序

select *from student order by age desc;

group by语句

统计函数

COUNT    求组中项数
SUM    求和
AVG    求平均值
MAX    求最大值
MIN    求最小值

select class_id,avg(age),max(age) from student group by class_id;

操作多表数据

join语句

内连接

select * from student join class on class.id=student.class_id;

左外连接

select * from student left outer join class on class.id=student.class_id;

右外连接

select * from student right outer join class on class.id=student.class_id;

left join语句

select student.id, student.name, class.name as class_name from student left join class on student.class_id=class.id;