# select 字段列表 from 表名# 基本查询select*from person
* 通配符查询以后去了公司不能用。
SELECT id,name,age,sex,favourite from person
可以用as给字段起别名,as也可以省略。
2.条件查询
# 用where表示查询的条件SELECT id,name,age ,sex ,favourite from person where id BETWEEN1and10SELECT id,name,age ,sex ,favourite from person where id>5and sex='男'SELECT id,name,age ,sex ,favourite from person where id>5or sex='男'# %匹配0或多个字符,一般不用左模糊(%放在左边,导致索引失效,降低查询效率)SELECT id,name,age ,sex ,favourite from person where name like'%豪'# _代表匹配一个字符SELECT id,name,age ,sex ,favourite from person where name like'_豪'SELECT id,name,age ,sex ,favourite from person where name like'%二%'
3.分页查询
# 语法 limit 起始索引,个数# 分页查询第m页,每页展示n 条数据 limit (m-1)*n, n SELECT id,name,age ,sex ,favourite from person limit0,5SELECT id,name,age ,sex ,favourite from person limit5,5SELECT id,name,age ,sex ,favourite from person limit10,5
4.排序
# 排序 order by 字段名 (ASC 升序,DESC降序)SELECT id,name,age ,sex ,favourite from person ORDERBY age ASC;SELECT id,name,age ,sex ,favourite from person ORDERBY age DESC;
5.聚合函数
# 聚合函数 count() sum() avg() max() min()selectcount(0)from person 查询有多少条记录
selectsum(age)from person
selectavg(age)from person
selectmin(age)from person
selectmax(age)from person
SELECT CAST(avg(age)asdecimal(10,0))as avg from person
6.分组查询
# 分组查询 group by 字段SELECT sex,count(0),avg(age)from person GROUPBY sex;# having条件查询 where分组前,having分组后的条件SELECT sex,count(0),avg(age)from person where age>8GROUPBY sex havingavg(age)>20;
7.子查询
# 把查询到的结果当作另一个查询的条件SELECT id,name,age ,sex ,favourite from person where id in(SELECT pid from dept where name='鼓励部')
8.内连接
# 笛卡尔积 查询出来的结果没有意义select*from person,dept
# 显示内连接 inner可以省略select*from person INNERJOIN dept on person.id=dept.pid
select*from person JOIN dept on person.id=dept.pid
select p.id,p.name,age,sex,favourite,birth,d.id deptid,d.name deptname,pid from person p JOIN dept d on p.id=d.pid
# 隐式内连接select*from person,dept where person.id=dept.pid
注意:也可以给表名起别名,如果两个表中有相同的字段名称,那么需要用别名+点号来区别,否则报错。
9.外连接
# 左外连接 left [outer] join 查询的是左边表的所有部分和右边表的交集select p.id,p.name,age,sex,favourite,birth,d.id 部门id,d.name as deptname,pid from person p leftouterjoin 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 leftjoin dept d on p.id=d.pid
# 右外连接 right [outer] join 查询的是右边表的所有部分和左边表的交集select p.id,p.name,age,sex,favourite,birth,d.id 部门id,d.name as deptname,pid from person p rightouterjoin dept d on p.id=d.pid