1.基本查询
select 字段列表 from 表名
2.条件查询
selec id,name,age ,sex ,favourite from person where id BETWEEN 1 and 10
3.分页查询
selec id,name,age ,sex ,favourite from person limit 0,5
分页查询第m页,每页展示n 条数据 limit (m-1)*n
4.排序
升序
select id,name,age ,sex ,favourite from person ORDER BY age ASC;
降序
select id,name,age ,sex ,favourite from person ORDER BY age DESC;
5.聚合函数
聚合函数
sum() 求和
avg() 平均值
max() 最大值
min()最小值
count() 查询有多少条记录
6.分组查询
selecat sex,count(0),avg(age) from person GROUP BY sex;
7.子查询
SELECT id,name,age ,sex ,favourite from person where id in (SELECT pid from dept where name=‘鼓励部’)
把查询到的结果作为另一个查询的条件
8.内连接
select * from person INNER JOIN dept on person.id=dept.pid
select * from person JOIN dept on person.id=dept.pid
9.外连接
select p.id,p.name,age,sex,favourite,birth,d.id 部门id,d.name as deptname,pid from person p left outer join dept d on p.id=d.pid
select p.id,p.name,age,sex,favourite,birth,d.id 部门id,d.name as deptname,pid from person p left join dept d on p.id=d.pid