Day04-mysql基础篇之查询DQL

现在我创建了两张表

students表

 classes表

 基本查询

查询students表中所有行

-- 查询所有 --
select * from `students`

select id,class_id,name,gender,score from `students`

select 还可以用于计算， 例如select 100+200;但是这并不是sql的强项。select语句中不带from有一个非常有用的用途，就是判断当前到数据库的连接是否有效。

条件查询

通常我们并不会查询表中所有的记录，而是根据条件进行选择性的得到表中某些记录。

 下面我们根据条件查询进行案例查询

select * from 表名 where 查询条件
-- 查询表中所有score<80的记录 --
select * from `students` where score < 80;
-- 查询表中所有name=abc的记录 --
select * from `students` where name='abc';
-- 字符串的比较 --
select * from `students` where name>'abc';
-- 不相等比较 --
select * from `students` where name<>'abc';
-- 两个条件查询 --
select * from `students` where name='abc' and score=80;
select * from `students` where name='abc' or score=80;
-- 两个以上的条件查询 --
select * from `students` where name='abc' and (score=80 or score=95);
-- not --
select * from `students` where not name='abc';
-- 测试not and or () 四者之间的优先级问题 --
select * from `students` where not name='abc' and (score=80 or score=95);
-- 测试模糊查询 --
select * from `student` where name like '小%';

从以上查询中，我总结出了以下几点

• 查询条件中的字符串需要使用 '' 包裹起来，如果不包裹起来，会报1054，不知道这个字段在哪里
• not、and、or、（）优先级问题，（）优先级最高，其次not，然后是and，最后是or。
• 查询条件中字符串的比较使用的是ascll码，中文字符比较要看数据库的具体设置
• 模糊查询中，条件要用''包裹起来，'%c'表示以c结尾的都要作为查询条件，'a%' 表示以a开头的都要作为查询条件，'%bc%'表示只要是包含bc的都要作为查询条件

投影查询

名字很高大上，但是一个案例就很容易看明白，就是我们只查询数据表中某些字段。

-- 投影查询举例 --
select id,name from `students`;
-- 投影查询和条件查询结合起来 --
select id,name from `students` where score < 95;
-- 设置别名 --
select id student_id,name student_id from `students` where score < 95;

 唯一一个需要注意的就是为表字段设置别名。查询结果的字段也是我们设置的别名。

排序

不知道你们有没有注意到，当我们使用select进行查询的时候，查询结果集默认是根据主键id进行排序的，如果我们想要为结果集设置别的排序方式呢？那么order by就应运而生了。

-- 默认升序排序 --
select id,name from `students` order by score ASC;
select id,name from `students` order by score;

-- 降序排序 --
select id,name from `students` order by id desc;
-- 列中存在相同的数据 --
select id,name from `students` order by score desc , gender ;

-- 和条件查询进行结合 --
select id,name from `students` where id = 1 order by score ;

需要注意的是：结果集如果没有指定排序方式那么默认使用升序排序（ASC）。如果使用order by 字段名 （排序方式） 进行指定的话，结果集就会按照指定的排序规则进行展示。和条件查询一起使用的时候，order by 必须放在where 条件之后。并且注意这里使用的是mysql数据库管理系统。不同的数据库管理系统查询结果可能有差异。

分页查询

对于结果集数据量非常大，我们需要对结果集进行分页展示才能符合日常的使用习惯。

-- 分页查询 --
select * from `students` limit 3 offset 0;
-- 结合排序进行分页查询 --
select * from `students` order by score limit 3 offset 0;

如果存在数据丢失的问题，那么可能需要额外添加一个条件。limit 后面需要确定每页查询的记录数量pagesize，offset后面需要确定从哪个记录开始查pageIndex(默认从1开始)。offset = pagesize * (pageIndex - 1)。

如果超出了查询的最大数量，会不会报错呢？并不会，而是会返回一个空的结果集。需要注意的是随着pageIndex越来越大，查询效率也会变得越来越低。那么怎么在分页查询之前确定一共分多少页呢？个人想法是使用总记录数除以每页记录数，如果存在余数的话那么总页数加一。

 聚合查询

所谓的聚合查询就是使用聚合函数进行查询，那么什么是聚合函数呢，简单举个例子，就是如果查询总记录数，那么我们难道要查出来所有的数据然后一个一个数吗？显然这是一种很傻x的行为，我们只需要使用sql为我们提供的聚合函数就可以轻松查询出来我们需要的数据。

常见的聚合函数有

count(字段名) 查询一共有多少条记录。

我们能不能根据数据表中某个字段的值进行聚合查询呢？可以使用group by 字段名，字段名 。。。。。 来进行查询，这种查询也叫分组聚合，需要注意的是，聚合查询的列中，只能放入分组的列，否则会报语法错误，因为不能把多个name的值放入到一行中去

-- 请使用一条SELECT查询查出每个班级男生和女生的平均分 --
select avg(score),class_id from `students` group by class_id,gender;
-- 请使用一条SELECT查询查出每个班级的平均分 --
select avg(score) ,class_id from `students` group by class_id;

 其实分组查询就是按照我们想要进行分组的条件，把结果集进行分组。方便阅读。

多表查询

这里的多表查询是查询的两张表记录数相乘的结果，需要注意的多表查询的结果集可能非常巨大，

-- 多表查询 --
select * from `students`,`classes`;
-- 为表设置别名,为表中字段设置别名 --
select s.name sname,c.name cname  from `students` s,`classes` c;
-- 使用条件约束查询结果 --
select s.name ,c.name cname from `students` s,`classes` c where s.id=1;

但是实际开发中我们并不提倡使用这种方式进行查询。真正有意义的查询是需要多表之间的主键外键相互关联。

连接查询

这里的连接查询其实指的是另一种类型的多表查询，连接查询对多个表进行join运算，我的理解就是选定一个主表作为结果集，然后把另一个表中的数据有选择性的拼接到主表上。具体拼接什么要看我们使用的是什么连接。

 基本语法是join后跟表名，on后跟查询条件。

左外连接 = 左表中除了两个表共有的 + 两个表中共有的数据，

右外连接 = 右表中除了两个表共有的 + 两个表中共有的数据，

全外连接 = 左右两个表中除了共有的 + 两个表中共有的。

。理解拼接的思想。

-- 内连接 --
select c.name cname, s.name sname from `students` s inner join `classes` c on s.class_id = c.id;
-- 全外连接 mysql中不支持全外，需要将左外和右外的查询结果联合起来--
select c.name cname, s.name sname from `students` s outer join `classes` c on s.class_id = c.id;
-- 左外连接 --
select c.name cname, s.name sname from `students` s left outer join `classes` c on s.class_id = c.id;
-- 右外连接 --
select c.name cname, s.name sname from `students` s right outer join `classes` c on s.class_id = c.id;

-- mysql中左外右外联合查询 --
select c.name cname, s.name sname from `students` s left outer join `classes` c on s.class_id = c.id 
UNION
select c.name cname, s.name sname from `students` s right outer join `classes` c on s.class_id = c.id;