SQL语句-各种查询

查询数量

select count(*) from gq_user where account =222 and password =222;

1.基本查询，这里以student表为例

SELECT * FROM student;

解释：

SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询

注意：查询结果也是一个二维表，它包含列名和每一行的数据

SELECT语句其实并不要求一定要有FROM子句。我们来试试下面的SELECT语句：

SELECT 100+200;

2.条件查询

查询班级号为 20201001 的学生

SELECT * FROM student where class_num = '20201001';

WHERE关键字后面的class_num= '20201001'就是条件。class_num是列名，该列存储了学生的班级，因此，class_num= '20201001'就筛选出了指定条件的记录：

查询班级号为 20201001 的学生姓名

SELECT name FROM student where class_num = '20201001';

查询班级号为 20201001 的学生姓名和性别

SELECT name,sex FROM student where class_num = '20201001';

当我们需要查询多列的时候我们需要使用 '，' 来进行分割

查询班级号为 20201001 或者性别为男的学生姓名

SELECT name FROM student where class_num = '20201001' or sex = '男';

查询班级号为 20201001 并且性别为男的学生姓名

SELECT name,sex FROM student where class_num = '20201001' and sex = '男';

当我们需要以多列作为查询条件的时候我们需要使用 'or' 或者 ‘and’来进行区分

查询班级不在 20201001 班的同学的姓名

SELECT name FROM student where not class_num = '20201001' ;

NOT <条件>，表示“不符合该条件”的记录

查询性别为女的同学姓名并且不在20201001班

SELECT name FROM student where sex ='女' and not class_num = '20201001';

where in的用法

IN 操作符允许我们在 WHERE 子句中规定多个值。

查询出用户id为1和3的用户记录

select * from student where id in (1,3)

<foreach>元素

3去重查询

SELECT DISTINCT sex from student

4.模糊查询

模糊查询 like 通配符(% 任意多个字符 _单个字符)

查询出所有姓王的同学

SELECT * FROM student WHERE name like '王%';

查询姓王且名字只有两个字的学生

SELECT * FROM student WHERE name like '王_';

查询出所有含有花子的同学的名称

SELECT * FROM student WHERE name like '%花%';

5.排序

我们使用SELECT查询时，细心的读者可能注意到，查询结果集通常是按照id排序的，也就是根据主键排序。这也是大部分数据库的做法。如果我们要根据其他条件排序怎么办？可以加上ORDER BY子句。例如按照年龄从低到高进行排序：

SELECT * FROM student ORDER BY age;

如果要反过来，按照年龄从高到底排序，我们可以加上DESC表示“倒序”：

SELECT * FROM student ORDER BY age DESC;

如果想按照年龄降序，并且按照学号升序怎么弄？

SELECT * FROM student ORDER BY age DESC,sno;

默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略，即ORDER BY score ASC和ORDER BY score效果一样。

6.分页查询（限制查询）

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

要实现分页功能，实际上就是从结果集中显示第1~100条记录作为第1页，显示第101~200条记录作为第2页，以此类推。

因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <M> OFFSET <N>子句实现。我们先把所有学生按照成绩从高到低进行排序：

现在，我们把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0：

SELECT * FROM student LIMIT 3 OFFSET 0;

上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。

如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：

SELECT * FROM student LIMIT 3 OFFSET 3;

可见，分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

• LIMIT总是设定为pageSize；
• OFFSET计算公式为pageSize * (pageIndex - 1)。

小结

使用LIMIT <M> OFFSET <N>可以对结果集进行分页，每次查询返回结果集的一部分；

分页查询需要先确定每页的数量和当前页数，然后确定LIMIT和OFFSET的值。

7.聚合查询

如果我们要统计一张表的数据量，例如，想查询students表一共有多少条记录，难道必须用SELECT * FROM students查出来然后再数一数有多少行吗？

这个方法当然可以，但是比较弱智。对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

仍然以查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT()函数查询：

SELECT COUNT(*) FROM student;

COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。

通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果：

SELECT COUNT(*) num FROM student;

例如查询男生的平均年龄

SELECT avg(age) num FROM student;

分组

分组查询的原理:先对数据进行分组，在对分组后的数据进行汇总，分组查询通常用于配合聚合函数，达到分类汇总统计信息的目的。而分类汇总的本质实际上就是先将数组进行分组，分组后相同类别的信息会聚在一起，然后通过需求进行计算。

比如我们要查询 各个班的男生的平均身高

SELECT avg(age) num FROM student GROUP BY class_num;

查询出各个班的人数

SELECT count(*) FROM student GROUP BY class_num;

8.多表查询

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：SELECT * FROM <表1> <表2>。

例如我们可以同时查询student，和sc两张表

SELECT * FROM student,scoure;

那如果我们想要某一个表当中的特定的列呢？比如student表当中的name和age,以及source表当中的cno列

SELECT s.NAME, s.age, c.cno FROM student s, scoure c;

9.连接查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

假如我们想要查询出每一和学生所在的班级名称，该如何去写呢？

这时，连接查询就派上了用场。我们先使用最常用的一种内连接——INNER JOIN来实现：

SELECT student.name,class.class_name from student INNER JOIN class on student.class_num = class.class_num;

注意INNER JOIN查询的写法是：

1. 先确定主表，仍然使用FROM <表1>的语法；
2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；
3. 然后确定连接条件，使用ON <条件...>，这里的条件是student.class_num = class.class_num，表示student表的class_num 列与class表的class_num列相同的行需要连接；
4. 可选：加上WHERE子句、ORDER BY等子句。

使用别名不是必须的，但可以更好地简化查询语句。

除了内连接以外还有外连接，那么内连接和外连接有什么区别呢？

左连接

SELECT student.name,class.class_name from student LEFT JOIN class on student.class_num = class.class_num;

右连接

SELECT student.name,class.class_name from student RIGHT JOIN class on student.class_num = class.class_num;

 

10.嵌套查询

一般在子查询中，程序先运行在嵌套在最内层的语句，再运行外层。因此在写子查询语句时，可以先测试下内层的子查询语句是否输出了想要的内容，再一层层往外测试，增加子查询正确率。否则多层的嵌套使语句可读性很低。

找到所有选选择课程号为1001的同学的名称

select t1.name FROM (SELECT student.name,relationship.cno FROM student INNER JOIN relationship on student.sno = relationship.sno) t1 WHERE t1.cno = '1001';

在这里我们一定要理解，我们嵌套查询最侧产生的是一个新的表

找到所有选选择课程号为 数学 的同学的名称

SELECT t2.name FROM (select t1.name,scoure.gradeName from (SELECT student.name,relationship.cno FROM student INNER JOIN relationship on student.sno = relationship.sno) t1 INNER JOIN scoure on t1.cno = scoure.cno) t2 where gradeName = '数学';

作业

1. 查询出所有班级的同学的名字

提示，不是所有的同学都有班级

SELECT student.name,class.class_name from student INNER JOIN class on student.class_num = class.class_num;

2.查询出年龄最高的同学的信息

提示：可以使用limit

select * FROM student ORDER BY age DESC LIMIT 1 OFFSET 0;

3.查询出在学生当中那个年龄段的学生最多，并统计其出现的次数

select age,count(age) num FROM student GROUP BY age LIMIT 1 OFFSET 0;

4.查询出年龄超过21岁，并且选择 语文 的同学名字

SELECT t3.name FROM (select t2.sno,student.name,student.age FROM student right JOIN (select t1.sno FROM (select relationship.sno,relationship.cno,scoure.gradeName FROM relationship INNER JOIN scoure on relationship.cno = scoure.cno) t1 WHERE t1.gradeName = '语文') t2 on student.sno = t2.sno) t3 where age > 21;

5.查询出软件工程班年龄最大，所选择的课程名称及该同学的姓名。

SELECT t3.NAME, scoure.gradeName FROM ( SELECT t2.NAME, relationship.cno FROM ( SELECT t1.sno, t1.NAME, t1.age FROM ( SELECT student.sno, student.NAME, student.age, class.class_name FROM student INNER JOIN class ON student.class_num = class.class_num ) t1 WHERE t1.class_name = '软件工程' ORDER BY age DESC LIMIT 1 OFFSET 0 ) t2 INNER JOIN relationship ON t2.sno = relationship.sno ) t3 INNER JOIN scoure ON t3.cno = scoure.cno;