2.MYSQL数据库的操作(二)

数据库的查询

条件查询where

1. 比较运算符 > 、<、=，！=，<>, >=, <=,is null, is not null.例如：
select * from student where age > 30;

2.逻辑运算符and、or、not.例如：
select * from student where age > 30 and id > 1;

3.模糊查询like
select * from student where class like '19%';
select * from student where class like '19_9'

4.范围查询
4.1连续范围
select * from student where age between 18 and 38;

4.2间隔范围
select * from student where age in (18, 28, 38,48);

排序

select * from student order by age [asc/desc];

限制

select * from student limit count/start,count;

去重

select distinct * from student

聚合与分组

1.常用聚合函数
统计个数count,求平均值avg,求和sum,最大值max,最小值min,列出字段全部值group_concat
例如:
select count(age) from student; （可以用as取别名）

2.分组
select sex from student group by sex;

3. 聚合分组一同使用(具有实际意义)
select sex,group_concat(id) from student group by sex; (注意选择与分组的字段必须一致)
select class,avg(age) from student group by class;（求每个班学生的平均年龄）

4.聚合分组下的条件筛选
select sex,group_concat(id) from student group by sex having sex = 'M';

子查询(内层查询的结果是外层查询的条件)

select * from student where age > (select avg(age) from student);

连接查询

1.内连接(inner join)
select * from student join other_table; (相当于乘法一样，笛卡尔连接会多出很多无意义的项)

加上限制条件如下：
select * from student inner join other_table on student.id = other_table.id; (这个时候id会重复)

select student.id,age,sex,class from student inner join other_table on student.id = other_table.id;

2.外连接
2.1左外连接left(左表为主表)
select student.id,age,sex,class from student left join other_table on student.id = other_table.id;

2.2右外连接right(右表为主表)
select student.id,age,sex,class from student right join other_table on student.id = other_table.id;