8.22 数据库 查询

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