关于数据库的一些基本命令（二）

四、数据表的详细查询操作

本节的数据表如下：

学生表如下：

课程表如下：

1.条件查询(使用where)

select 字段 from 表名 where 条件；

select * from student where age <18;

select * from student where age >18;

----不在什么范围内可以使用关键字not

select * from student where not ( age>18 and gender='女' )；

2.模糊查询（使用正则）

（1）查询名字以’小‘开头的

select * from student where name like '小%';

（2）查询名字中有’小‘字的

select * from student where name like '%小%'；

（3）查询只有两个字的名字

select name from student where name like '__';

（4）查询只有三个字的名字

select name from student where name like '___';

（5）查询至少有两个字的名字

select name from student where name like '__%';

（6）正则表达，查询以周开始的名字

select name from student where name rlike '^周.*';

（7）正在表达，查询以’周‘开始的并以’杰结尾的名字

select name from student where name rlike '^周.*杰$';

3.范围查询

范围查询的关键字有：In, not in , between ... and ... , not between ... and ...

（1）查询年龄为18,12,34的学生，只显示姓名和年龄

select name,age from student where age in (18,12,34);

（2）查询年龄不在18,12,34范围内的学生，只显示姓名和年龄

select name,age from student where age not in (18,12,34);

（3）查询年龄在18到34范围内的学生，只显示姓名和年龄

select name,age from student where age between 18 and 34;

（4）查询年龄不在18到34范围内的学生，只显示姓名和年龄

select name,age from student where age not between 18 and 34;

4.空判断

判断空为：where xxx is NULL或者not NULL;

（1）判断身高为空的学生

select * from student where height is NULL;

（2）判断身高为空的学生

select * from student where height is not NULL;

5.排序

排序使用order by，asc从小到大排序，desc从大到小排序

（1）查询年龄在18到34之间的女性，年龄按升序排列。

asc一般省略

select * from student where (age between 18 and 34) and gender='女' order by age asc;

（2）查询年龄在18到34之间的女性，年龄按降序排列。

select * from student where (age between 18 and 34) and gender='女' order by age desc;

（3）查询年龄在18到34之间的女性，年龄按降序排列，如果结果相同的情况下按照年龄降序排序。

select * from student where (age between 18 and 34) and gender='女' order by age desc,id desc;

（4）按照年龄从小到大（升序），身高从高到矮（降序）的排序

select * from student order by age ,height desc;

6.聚合函数

（1）count 总数

计算表中女性人数

select count(*) as 女性 from student where gender='女';

（2）最大值max()

求表中的最大年龄

select max(age) from student;

（3）求最小的年龄min()

select min(age) from student;

（4）计算所有人的年龄总和sum()

select sum(age) from student;

（5）计算表中的平均年龄avg()

select avg(age) from student;

（6）保留小数round(num, 1)，保留一位小数

select round(avg(age), 1) from student;

7.分组

（1）按gender将student表进行分组

select gender from student group by gender;

（2）按gender将student表进行分组，并统计每组的人数

select gender, count(*) from student group by gender;

（3）按gender将student表进行分组，并显示每组人的姓名

select gender,group_concat(name) from student group by gender;

（4）查询男性的人数

select gender,count(*) from student where gender='男' group by gender;

（5）group_concat()

查询同种性别中的姓名，年龄，id

select gender, group_concat(name, '_',age,'_',id ) from student group by gender;

（6）having

查询平均年龄超过30岁的性别，以及姓名

select gender as 性别,group_concat(name) as 姓名 from student group by gender having avg(age)>30;

注意：having 和where的区别？

where是对原始表中的字段进行的判断，而having是对查询出来的结果在进行判断

（7）查询每种性别中人数多于2的信息

select gender as 性别,group_concat(name, '_',age) from student group by gender having count(*)>2;

8.分页

使用limit关键字

（1）限制每次查询出来的记录数目

select * from student limit 2;

select * from student where gender=1 limit 2;

（2）limit start , count

查询下标从1开始， 只显示5行数据

select * from student limit 0,5;

select * from student limit 2,5;

注意：limit在查询中永远在最后，在sql语句中没有2*3之类的写法。

select * from student order by age asc limit 2;

（3）查询所有女性的信息，并且按照身高降序排列，只显示2行

select * from student where gender='女' order by height desc limit 2;

9.连接查询

内连接：inner join ... on，取交集

（1）查询能够对应班级的学生的信息

select * from student inner join classes on student.cls_id=classes.id;

（2）按照要求显示姓名和对应的班级

select student.name as 姓名,classes.name as 班级 from student inner join classes on student.cls_id=classes.id;

或者

select s.name as 姓名,c.name as 班级 from student as s inner join classes as c on s.cls_id=c.id;

（3）按照要求显示姓名和对应的班级，并按班级降序排列

select s.name as 姓名,c.name as 班级 from student as s inner join classes as c on s.cls_id=c.id order by c.name desc;

外连接：

1.左连接：left join,即以left join左边的表为基准对右边的表进行连接，存在则显示相应的信息，不存在则显示NULL。

（1）查询每位学生对应的班级信息

select * from student as s left join classes as c on s.cls_id=c.id;

（2）查询没有对应班级的学生

select * from student as s left join classes as c on s.cls_id=c.id having c.id=NULL;

2.右连接：right join ... on（很少使用）

select * from classes as c right join student as a on c.id=s.cls_id;

10.子查询

（1）查询最高的男生的信息

select * from student where height=(select max(height) from student where gender=1);