MySQL学习记录（5）

学习内容：数据表的简单查询

---

一、SELECT语句

语法格式：

select 字段名
from 表名和视图列表
{where 条件表达式1}
{group by 属性名1}
{having 条件表达式2}
{order by 属性名2 asc|desc }

• 表名和视图列表：表示从此处指定的表或视图中查询数据，表和视图可以有多个。
• 条件表达式1：表示指定查询条件。
• 属性名1：指按该字段中的数据进行分组。
• 条件表达式2：表示满足该表达式的数据才能输出。
• 属性名2：指按该字段中的数据进行排序，排序方式有 ASC（升序）和 DESC（降序）两种方式，默认情况下是升序。

二、数据的简单查询

1.查询表中所有数据

两种方法：一是列出表中的所有字段，二是使用“ * ”号查询所有的字段。

use 表名;
select 字段名1,字段名2,···,字段名N from 表名;

select * from 表名;

2.查询表中个别数据

只需在select后面指定要查询的字段的名称即可。

use 表名;
select 字段名1,字段名2,···,字段名N from 表名;

3.对查询结果进行计算

select 学号,课程号,分数 原来的分数,分数+1 加一后的分数值 from score;
红色部分（在查询项后空格直接写）是为结果取的别名
为字段取别名语法格式：属性名 [as] 别名
AS关键字可有可无。

select 学号,课程号,分数,format(学号*分数,2) as totle from score;

4.在查询时去除重复项

语法格式：select distinct 字段名 from 表名;
使用DISTINCT 可以在查询结果中避免重复项。

5.在查询结果中给表取别名

语法格式：表名 [AS] 表的别名
当表的名字很长或者执行一些特殊查询时，为了方便操作或者需要多次使用相同的表时，可以为表指定别名，以替代表原来的名称。
在为表取别名时，要保证不能与数据库中的其他表的名称冲突。
表的别名只在执行查询的时候使用，并不在返回结果中显示；而列别名定义之后，将返回给客户端显示，显示的结果字段为字段列的别名。

6.使用Limit 限制查询数据

当数据表中包含大量的数据时，可以通过指定显示记录数限制返回的结果集中的行数。
limit可以用来指定查询结果从哪条记录开始显示，还可以用来指定一共显示多少条记录。
（1）不指定初始位置，限制记录显示条数。
记录从第1条开始显示。
语法格式：LIMIT 记录数

select *from score limit 2;
-- 显示前两条记录

（2）指定初始位置
语法格式：LIMIT 初始位置,记录数
第1条记录的位置是0，第2条记录的位置是1。

select * from score limit 1,3;
--从第二条记录开始显示，共显示3条

三、使用WHERE子句进行条件查询

WHERE子句的筛选条件由一个或多个条件表达式组成。

查询条件	符号或关键字
比较	=, < , <= , >, >= , != , <> , !> , !<
指定范围	BETWEEN AND , NOT BETWEEN AND
指定集合	IN , NOT IN
匹配字符	LIKE , NOT LIKE
是否为空值	IS NULL , IS NOT NULL
多个查询条件	AND , OR

1.带BETWEEN AND关键字

BETWEEN AND , 需要两个参数，范围的开始值和结束值。

select * from score
where 分数 between 70 and 80;
--查询表中分数在70到80之间的数据记录，包括端点值。
select * from score
where 分数 not between 70 and 80;
--查询表中分数在小于70和大于80的数据记录。

2.带IN关键字

IN 用来查询满足指定条件范围的记录，将所有检索条件用括号括起来，检索条件用逗号分开，只要满足条件范围中的一个值即为匹配项。

USE School
SELECT *FROM student
WHERE 学号 IN (101,102);
--查询学号为101和102的学生数据记录
USE School
SELECT *FROM student
WHERE 学号 NOT IN (101,102);
--查询学号不等于101也不等于102的学生数据记录

3.带LIKE关键字

LIKE关键字可以匹配字符串是否相等。
语法格式：[NOT] LIKE '字符串'
字符串：用来匹配的字符串必须加上单引号或双引号。字符串参数的值可以是一个完整的字符串，也可以是包含百分号（%）或下画线（_)的通配符

• 百分号（%）：可以代表任意长度的字符串，长度可以是0。
  例如：b%k表示以字母b开头，以字母k结尾的任意长度字符串，可以是bk、book、break 等字符串。
• 下画线（_)：只能表示单个字符，_可以代表任意一个字符。
  例如：b_k表示以字母b开头，以字母k结尾的3个字符，可以是bok、buk、bak 等字符串。

4.带AND、OR的多条件查询

AND只有同时满足所有查询条件才会被查询出来。
OR只需满足一个条件就会被查询出来。
OR关键字可以与AND关键字一起使用，同时使用时，AND 的优先级比OR高。
语法格式：

条件表达式1 AND 条件表达式2 [···AND 条件表达式n]
条件表达式1 OR 条件表达式2 [···OR 条件表达式n]

USE School
SELECT *FROM student
WHERE 学号=101 AND 性别 LIKE '男';
--使用AND关键字来查询student表中学号为“101”，而且“性别”为“男”的记录
USE School
SELECT *FROM student
WHERE 学号 IN (101,102,103) AND 年龄 BETWEEN 17 AND 21 AND 班号 LIKE '09031';
--使用AND关键字来查询student表，查询条件为学号取值在{101,102,103}这个集合之中，年龄范围从17~21，而且班号为“09031”。
USE School
SELECT *FROM student
WHERE 学号 IN (101,102,103) AND 年龄=18 OR 性别 LIKE '男';
(WHERE 性别 LIKE '男' OR 学号 IN (101,102,103) AND 年龄=18;)
--两句效果相同。AND关键字先后条件先结合，再与OR关键字条件相结合。

四、操作查询的结果

可以对查询结果进行排序，还可以对查询结果分组显示或分组过滤显示。

1.排序ORDER BY

语法格式：

ORDER BY 字段名[ASC|DESC]
--默认按升序排序

若要排序的字段为空值（NULL) 时，升序排序时将显示为第一条记录，降序时为最后一条。
多字段排序方式

USE School
SELECT * FROM student ORDER BY 学号 ASC,年龄 DESC;
--先按照“学号”字段进行升序排序，当遇到“学号”字段值相同的情况时，再把“学号”相等的记录按照“年龄”字段进行排序。

2.分组ORDER BY

语法结构：

GROUP BY 字段名 [HAVING 条件表达式] [WITH ROLLUP]

• HAVING 表达式：用来限制分组后的显示，满足条件表达式的结果将被显示。
• WITH ROLLUP: 将会在所有记录的最后加上一条记录，该记录是上面所有记录的总和。
  GROUP BY可以与GROUP_CONCAT()函数一起使用，也可与集合函数包括MAX(), MIN(), COUNT(), SUM(), AVG()一起使用。
  （1）与GROUP_CONCAT()函数
  每个分组中指定字段值都显示出来。
  
  （2）与集合函数一起使用
  可以通过集合函数计算分组中的总记录，最大值、最小值等。
  例如：下面语句按“性别”进行分组查询，对每一组使用集合函数COUNT()进行计算，求出每一组中的记录数。
  （3）按多字段进行分组
  根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，然后在第1个字段相同的记录中，再根据第2个字段的值进行分组。
  （4）与WITH ROLLUP一起使用
  

3.对分组结果过滤查询

GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。

SELECT 性别, COUNT(*) AS 数量 FROM student
GROUP BY 性别 HAVING COUNT(*) > 3;
--根据学生“性别”字段对student表中的数据进行分组，并显示学生数量大于3的分组信息。

五、使用集合函数进行统计查询

函数	作用
AVG()	返回某列的平均数
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值的和

• COUNT(*):计算表中的总行数，不管某列有数值或者为空值都不会被忽略。
• COUNT(字段名）：计算指定列下总的行数，计算时将忽略字段值为空值的行。

SELECT COUNT(*) FROM student;
--使用COUNT()函数统计student表中的记录数
SELECT 学号,COUNT(*) FROM student GROUP BY 学号;
--使用COUNT()函数统计student表中不同学号的记录数
SELECT AVG(分数) AS 平均分
FROM score
WHERE 学号='101';
--在score表中，查询学号为‘101’的学生成绩平均值
SELECT 学号, MAX(分数) AS 最高分
FROM score
GROUP BY 学号;
--在score表中查找每个学生成绩中的最高分
SELECT 学号, MIN(分数) AS 最低分
FROM score
GROUP BY 学号;
--在score表中查找每个学生成绩中的最低分