数据库学习-数据查询

1 简单查询

1.1 列出表中所有的字段

-- 查出表中所有的字段，两种方式
select * from tableName;
select field1,field2,...,fieldn from tableName;

1.2 查询指定字段数据

select field1,field2 from tableName;

1.3 DISTINCT查询

select 后加上 distinct，查询到的数据会不重复显示

select DISTINCT field1,field2 from tableName;

1.4 IN查询

当需要查询的记录在某个集合中时，可以在 where 后的条件上加上in()

select field1,field2 from tableName where field IN(value1,value2,value3);

in 前加上not，则查询不在集合中的记录

1.5 BETWEEN AND查询

当需查询的一个范围内的记录时，可以使用between and（如需查询分数从60到90的学生信息）

select * from student where grade BETWEEN 60 AND 90;

between前加上not，查询不在范围内的记录

1.6 LIKE模糊查询

想模糊查询某些数据，可以在where后的条件中加上like

select * from tableName where field like '%m%';

like后的模糊的写法可以使用% 和 _，其中%可以匹配多个字符，
_ 为匹配单个字符

例：查询姓名中第二个字符为三的信息

select * from student where name like '_三';

例：查询姓名中有三的信息

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

1.7 对查询结果排序

对查询的结果进行排序可以使用order by

select field1,field2 from tableName where condition order by field [ASC|DESC]

其中[] 表示可以省略，省略不写默认为ASC升序排序，写DESC表示降序排序

1.8 简单分组查询

通过分组，可以对数据进行一些计算，
格式：

select function() from tableName where condition GROUP BY field;

① 统计数量

count(*) 统计表中的记录数量，不管表中的字段包含null或非null
count(field) 统计表中field字段的数量，会忽略null值

② 计算平均值

AVG(field) 对指定字段求平均值

③ 计算和

SUM(field) 对指定字段求和

④ 统计最大值

MAX(field) 对指定字段求最大值

⑤ 统计最小值

MIN(field) 对指定字段求最小值

1.9 统计分组查询

当分组查询的时候可能不能显示这个组中所有的记录，可以加上GROUP_CONCAT()

select GROUP_CONCAT(field) from tableName where condition GROUP BY field;

2 联合查询

2.1 内连接查询

2.1.1 自连接

自连接是同一张表，自己与自己相连接，例：查询学生张三所在班级的其他学生

select t1.id,t1.name from student t1 inner join student t2
on t1.class_id = t2.class_id and t1.name='张三';

2.1.2 等值连接

通过两张表中某个字段，该字段值相同的记录进行相连接，例：将学生表与班级表相连

select * from student t1 inner join class t2
on t1.class_id = t2.id;

2.1.3 不等值连接

不等值连接就是将两张表中的记录通过不相等的值进行连接，运算符有：>，>=，<，<=，!=

select * from student t1 inner join class t2
on t1.class_id != t2.id;

2.2 外连接查询

2.2.1 左外连接

左外连接，以左边的表基准，右边的表连接到左表上，若左表中的某条记录在右表中没有，则右表中的列显示为空值

select * from student t1 left join class t2
on t1.class_id != t2.id;

2.2.2 右外连接

右外连接，以右边的表基准，左边的表连接到右表上，若右表中的某条记录在左表中没有，则左表中的列显示为空值

select * from student t1 right join class t2
on t1.class_id != t2.id;

2.3 合并查询

合并查询是将多张表组合起来形成一张新的表，所有记录结合在一起。
使用方法：第一个select后加上UNION或UNION ALL，其中union会做distinct操作，不会有重复的记录，union all会显示所有记录。

例：将学生表与班级表结合，查出所有学校中所有的师生

select name as people from student UNION
select teacher from class;

2.4 子查询

① 带比较运算符的子查询

运算符包括：>，>=，<，<=，!=(<>)，=
例：查询张三的老师是谁

select teacher from class where id = (select class_id from student
where name='张三';

② 带IN的子查询

例：查询张三的老师是谁

select teacher from class where id IN (select class_id from student
where name='张三';

③ 带EXISTS的子查询

exists后的子查询语句若能查询到数据则会进行exists前语句的查询，若查询不到数据，则不会进行exists前语句的查询

例：若2班存在学生记录则查询2班的班级信息

select * from class where id=2 and exists(select * from student where class_id=2);

④ 带ANY的子查询

any表示任意满足内层语句中任意一条结果，则将内层语句中的结果作为条件给外层查询，any一般和比较运算符结合使用

例：查询学生表里可获取奖学金的学生信息

select * from student where chinses+english+math >= 
any(select score from grade);

⑤ 带ALL的子查询

all表示任意满足内层语句中所有结果，才将内层语句中的结果作为条件给外层查询，否则不进行外层的查询，同样一般也和比较运算符结合使用

例：查询学生表里可获取一等奖学金的学生信息

select * from student where chinses+english+math >= 
all(select score from grade);

当学生的总成绩大于所有能获得奖学金的成绩时，表示能获得一等奖学金