MySQL复杂查询

MySQL复杂查询

分组查询

Group by having

1. 一旦按某字段分组了,那么select子句后面就只能出现分组的字段+聚合函数

2. 如果有where 那么一定在group by 前面

3. 如果有order by 那么一定在group by 后面

4. 分组后,如果需要继续筛选,那么要在group by 后面加having

Select->from->where->group by->having->order by->limit

子查询

就是查询嵌套,查询语句里又嵌套了若干条查询

如:查询课程为Java的所有学生的平均成绩

Select avg(cj) from cjb where kch in(select kch from kcb where kch=’Java’);

注意点:

1. 当用到子查询时,用小括号括起来

2. 常在where后

3. 如果子查询返回多值,那么要用in,(返回一个值,可以用=)

表连接

需求:要将不在一张表的信息显示在同一查询结果中

笛卡尔积

Select * from 表1,表2;

笛卡尔积运算就是将表1的每一行都与表2组合,生成一张大表

如果是3张表,那么前两张表先作笛卡尔积,生成的大表再和第三张表作笛卡尔积

比如表1有3行4列,表2有2行3列,将表1与表2作笛卡尔积得到的新表就有6行7列

缺点:

会产生很多无用的垃圾数据

解决:

通过添加筛选条件过滤重复的数据

简写

Select * from 表1,表2 where 表1.name=表2.name;

标准写法

Select * from 表1 inner join 表2 on 表1.name=表2.name;

注意事项

1. 对于同名字段,需要在字段名前加表前缀

2. 对非同名字段,表前缀可加可不加

3. 只要from后面有2张及以上表,第一反应就是要想到笛卡尔积

4. 可以在表名后面+一个空格+别名,给表取别名(字段别名也如此)

例:select * from 课程表 kcb,成绩表 cjb where kcb.kch=cjb.kch;

1. 如果除了表的关联之外还有其他条件,可以接and

2. 表连接使用的关键字都放在2张表名之间

3. 在使用表连接时,select后面要么用*查询所有,要么用2张表中不重名字段查询,千万不能用重名字段(就是2张表都有的同名字段)查询,select重名字段会报错(模棱两可)

例:表a有姓名,年龄,地址3个字段,表b有姓名,电话2个字段

使用select * from a,b where a.姓名=b.姓名;

或者select 年龄,地址 from a,b where a.姓名=b.姓名;

就是不能用select 姓名 from a,b ……select后面带了重名字段姓名就会报错

表的连接方式

内连接

Inner join

外连接

放在2张表名之间

1. 左(外)连接

Left join…on/left out join…on on后面接筛选条件

表示除了返回满足筛选条件的结果集,还会把left join左边那张表的信息完整展示,

右边那张表不满足筛选条件的字段位置补空值null

1. 右(外)连接

right join…on/right out join…on

1. 全(外)连接

full join…on/full out join…on

展示左右2张表的全部信息

注意:

SQL支持full join 但MySQL不支持,但可以通过union集合在MySQL中实现全外连接

例:

Select * from a left join b on a.name=b.name

Union

Select * from a right join b on a.name=b.name;

三表连接

1. select * from a,b,c where a.name=b.name and b.age=c.age;

2. select * from a inner join b on a.name=b.name inner join c on b.age=c.age;

补充说明

Limit 限制显示的记录数

Limit m 等效于 limit 0,m 表示从第一行开始显示m行记录

Limit n,m 表示从记录编号n开始的m条记录(从n+1行开始显示)

Limit m offset n 表示从0偏移n,也就是从第n行开始显示m条记录

小结

关键字使用顺序

select->distinct->column_name->from->innerjoin…on/left(right,full)join…on->where->

group by->having->order by->limit