MySQL查询语句

1.基本查询

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 BETWEEN 1 and 10
SELECT id,name,age ,sex ,favourite  from person where  id>5 and sex='男'   
SELECT id,name,age ,sex ,favourite  from person where  id>5 or 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 limit 0,5
SELECTid,name,age ,sex ,favourite from person limit 5,5
SELECT id,name,age,sex ,favourite from person limit 7,5

4.排序

排序 order by 字段名 （ASC 升序，DESC降序）

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.聚合函数

聚合函数 count() sum() avg() max() min()

select count(0) from person   查询有多少条记录
select sum(age) from person 
select avg(age) from person 
select min(age) from person 
select max(age) from person 
SELECT CAST(avg(age) as decimal(10,0)) as avg from person

总数

求和

平均值

最小值

最大值

6. 分组查询

分组查询 group by 字段

SELECT  sex,count(0),avg(age) from person GROUP BY sex;

having条件查询 where分组前，having分组后的条件

SELECT  sex,count(0),avg(age) from person where age>8 GROUP BY sex  having avg(age)>20;

7.子查询

把查询到的结果当作另一个查询的条件

select username,phonenumber from (select
id,username,password,age,sex,phonenumber from text where username like
“%张六%”) h

8.内连接

笛卡尔积 查询出来的结果没有意义

select * from person,dept

显示内连接 inner可以省略

select * from person INNER JOIN 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
注意：也可以给表名起别名，如果两个表中有相同的字段名称，那么需要用别名+点号来区别，否则报错。

外连接

左外连接 left [outer] join 查询的是左边表的所有部分和右边表的交集

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

右外连接 right [outer] join 查询的是右边表的所有部分和左边表的交集

select p.id,p.name,age,sex,favourite,birth,d.id  部门id,d.name as deptname,pid from person p right outer join dept d on p.id=d.pid

三表联查