MySQL （五）数据库的查询

【数据库查询的基础】

数据库查询建立在 “关系运算” 的基础之上，数据库定义了一套专门的关系运算规则：选择、投影、连接运算，这三种运算的运算对象和运算结果都是表；

1、选择（Selection）

选择是单目运算，其运算对象是一个表。该运算按给定的条件，从表中选出满足条件的行形成一个新表作为运算结果。

选择运算的记号为s_F（R）。其中s是选择运算符，下标F是一个条件表达式，R是被操作的表。

学生表

学号	姓名	专业名	性别	出生日期	总学分	备注
081101	王林	计算机	男	1990-02-10	50
081102	程明	计算机	男	1991-02-01	50
081103	王燕	计算机	女	1989-10-06	50

若要在学生情况表中找出学生表中性别为女的行形成一个新表，则运算式为：

s_F（学生），上式中F：性别=“女”，该选择运算的结果如表

学号	姓名	专业名	性别	出生日期	总学分	备注
081103	王燕	计算机	女	1989-10-06	50

2、投影（Projection）

投影也是单目运算，该运算从表中选出指定的属性值（即列）组成一个新表，记为：Õ_A(R)。

其中A是属性名（即列名）表，R是表名。若在表中对学号、姓名和总学分投影，运算式为：

Õ_{学号，姓名，总学分}（学生），该运算得到如表

学号	姓名	总学分
081101	王林	50
081102	程明	50
081103	王燕	50

3、连接（JOIN)

上面讲的两种运算分别是在行和列的方向上对表进行分割，而连接操作是对整张表的操作

① 等值连接

两个表连接最常用的条件是两个表的某些列值相等，这样的连接称为等值连接

②自然连接

数据库应用中最常用的是“自然连接”。进行自然连接运算要求两个表有共同属性（列），自然连接运算的结果表是在参与操作两个表的共同属性上进行等值连接后再去除重复的属性后所得的新表

【数据库查询语句——Select 语句语法】

1、使用数据库和表的主要目的是存储数据以便在需要时进行检索、统计或组织输出，通过SQL语句的查询可以从表或视图中迅速方便地检索数据。

2、SQL的SELECT语句可以实现对表的选择、投影及连接操作。

3、SELECT语句可以从一个或多个表中选取特定的行和列，结果是生成一个临时表。在执行过程中系统根据用户的标准从数据库中选出匹配的行和列，并将结果放到临时的表中，这就是实现选择和投影运算的一个形式。

下面介绍SELECT语句语法，它是SQL的核心。

语法格式：

SELECT

    [ALL | DISTINCT | DISTINCTROW ]

    [HIGH_PRIORITY]

    [STRAIGHT_JOIN]

    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

    select_expr, ...

    [INTO OUTFILE \'file_name\' export_options | INTO DUMPFILE \'file_name\']

    [FROM  table_reference [ , table_reference] …]    /*FROM子句*/

    [WHERE where_definition]  /*WHERE子句*/

    [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]  /* GROUP BY子句*/

    [HAVING where_definition]  /* HAVING 子句*/

    [ORDER BY {col_name | expr | position}  [ASC | DESC] , ...]  /*ORDER BY子句*/

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]  /*LIMIT子句*/

解释：

SELECT关键词的后面可以使用很多的选项：

● ALL | DISTINCT | DISTINCTROW：这几个选项指定是否重复行应被返回。如果这些选项没有被给定，则默认值为ALL（所有的匹配行被返回）。DISTINCT 和 DISTINCTROW是同义词，用于消除结果集合中的重复行。

● SELECT…INTO OUTFILE \'file_name\'：这个语句可以将表中的行导出到一个文件中，这个文件被创建在服务器主机中，file_name为文件名。

●以SQL_为开头的选项都是MySQL相对于标准SQL的扩展，这些选项在多数情况下可以选择不使用。

●所有被使用的子句必须按语法说明中显示的顺序严格地排序。例如，一个HAVING子句必须位于GROUP BY子句之后，并位于ORDER BY子句之前。

【常用的Select选择语句】

下面我们首先将几张表录入数据库中，之后的操作都在这几张表的基础上完成

一、【列操作】

1. 选择指定的列

使用select语句选择一个表中的某些列，各列名之间要以逗号分隔。

查询xscj数据库的xs表中各个同学的姓名、专业名和总学分。

use xscj

select 姓名,专业名,总学分

    from xs;

说明：执行结果是xs表中全部学生的姓名、专业名和总学分列上的信息。

注意：当在select语句指定列的位置上使用*号时，表示选择表的所有列。

2. 定义列别名

当希望查询结果中的某些列或所有列显示时且使用自己选择的列标题时，可以在列名之后使用as子句来更改查询结果的列别名。语法格式为：

select column_name [as] column_alias

查询xs表中计算机系同学的学号、姓名和总学分，结果中各列的标题分别指定为number、name和mark。

select 学号 as number, 姓名 as name, 总学分 as mark

    from xs

    where 专业名= \'计算机\';

注意，当自定义的列标题中含有空格时，必须使用引号将标题括起来。例如：

SELECT 学号 AS \'Student number\', 姓名 AS \'Student name\', 总学分 AS mark

    FROM XS

    WHERE 专业名= \'计算机\';

说明：不允许在WHERE子句中使用列别名。这是因为，执行WHERE代码时，可能尚未确定列值。例如，下述查询是非法的：

SELECT 性别 AS SEX 
    FROM XS 
    WHERE SEX=0;

3. 替换查询结果中的数据

在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。例如查询XS表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：

CASE

     WHEN 条件1 THEN 表达式1

     WHEN 条件2 THEN 表达式2

     ……

     ELSE 表达式

END

查询XS表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换：

若总学分为空值，替换为“尚未选课”；若总学分小于50，替换为“不及格”；若总学分在50～52之间，替换为“合格”；若总学分大于52，替换为“优秀”。列标题更改为“等级”。

 SELECT学号, 姓名,

           CASE

          WHEN 总学分 IS NULL THEN \'尚未选课\'

           WHEN 总学分 < 50 THEN \'不及格\'

           WHEN 总学分 >=50 and 总学分<=52 THEN \'合格\'

          ELSE \'优秀\'

          END    AS 等级

          FROM XS

          WHERE 专业名=\'计算机\';

4. 计算列值

使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果，格式为：

SELECT expression [ , expression ]

按120分计算成绩，显示XS_KC表中学号为081101的学生课程信息。

SELECT  学号, 课程号 ,  成绩*1.20  AS 成绩120

     FROM XS_KC

     WHERE 学号= \'081101\';

5. 消除结果集中的重复行

对表只选择其某些列时，可能会出现重复行。例如，若对XSCJ数据库的XS表只选择专业名和总学分，则出现多行重复的情况。可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行，其格式是：

SELECT DISTINCT | DISTINCTROW column_name [ , column_name…]

其含义是对结果集中的重复行只选择一个，保证行的唯一性。

对XSCJ数据库的XS表只选择专业名和总学分，消除结果集中的重复行。

SELECT DISTINCT 专业名，总学分

     FROM XS;

6. 聚合函数（aggregation function）

SELECT子句的表达式中还可以包含所谓的聚合函数。聚合函数常常用于对一组值进行计算，然后返回单个值。除COUNT函数外，聚合函数都会忽略空值。聚合函数通常与GROUP BY子句一起使用。如果SELECT语句中有一个GROUP BY子句，则这个聚合函数对所有列起作用，如果没有，则SELECT语句只产生一行作为结果。

下表列出了一些常用的聚合函数。

（1）COUNT函数

聚合函数中最经常使用的是COUNT()函数，用于统计组中满足条件的行数或总行数，

返回SELECT语句检索到的行中非NULL值的数目，若找不到匹配的行，则返回0。

语法格式为：

COUNT ( { [ ALL | DISTINCT ] expression } | * )

其中，expression是一个表达式，其数据类型是除BLOB或TEXT之外的任何类型。ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。使用COUNT(*)时将返回检索行的总数目，不论其是否包含 NULL值。

例如：求学生的总人数。

SELECT COUNT(*) AS \'学生总数\'

    FROM XS;

例如：统计备注不为空的学生数目。

SELECT COUNT（备注）AS  \'备注不为空的学生数目\'

    FROM XS;

例如：统计总学分在50分以上的人数。

SELECT COUNT（总学分） AS  \'总学分50分以上的人数\'

    FROM XS

    WHERE 总学分>50;

（2）MAX和MIN

MAX和MIN分别用于求表达式中所有值项的最大值与最小值，语法格式为：

MAX / MIN ( [ ALL | DISTINCT ] expression )

其中，expression是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期类型。

例如：求选修101课程的学生的最高分和最低分。

SELECT MAX（成绩）, MIN（成绩）

    FROM XS_KC

    WHERE 课程号 = \'101\';

注意：当给定列上只有空值或检索出的中间结果为空时，MAX和MIN函数的值也为空。

（3）SUM函数和AVG函数

SUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式为：

SUM / AVG ( [ ALL | DISTINCT ] expression )

其中，expression是常量、列、函数或表达式，其数据类型只能是数值型数据。

例如：求学号081101的学生所学课程的总成绩。

SELECT SUM(成绩) AS \'课程总成绩\'

    FROM XS_KC  

    WHERE 学号 = \'081101\';

例如：求选修101课程的学生的平均成绩。

SELECT AVG(成绩) AS \'课程101平均成绩\'

    FROM XS_KC  

    WHERE 课程号 = \'101\';

（4）VARIANCE和STDDEV（STD）函数

VARIANCE和STDDEV函数分别用于计算特定的表达式中的所有值的方差和标准差。语法格式：

VARIANCE / STDDEV ( [ ALL | DISTINCT ] expression )

例如：求选修101课程的成绩的方差。

SELECT VARIANCE（成绩)

  FROM XS_KC

  WHERE 课程号= \'101\';

（5）GROUP_CONCAT函数

MySQL支持一个特殊的聚合函数GROUP_CONCAT函数。该函数返回来自一个组指定列的所有非NULL值，这些值一个接着一个放置，中间用逗号隔开，并表示为一个长长的字符串。这个字符串的长度是有限制的，标准值是1024。

语法格式为：

GROUP_CONCAT ( { [ ALL | DISTINCT ] expression } | * )

例如：求选修了206课程的学生的学号。

SELECT GROUP_CONCAT(学号)

  FROM XS_KC

  WHERE 课程号= \'206\';

（6）BIT_AND、BIT_OR和BIT_XOR

与二进制运算符|（或）、&（与）和^（异或）相对应的聚合函数分别是BIT_OR 、BIT_AND、BIT_XOR。例如，函数BIT_OR在一列中的所有值上执行一个二进制OR。

语法格式为：

BIT_AND | BIT_OR | BIT_XOR( { [ ALL | DISTINCT ] expression } | * )

例如：有一个表BITS，其中有一列bin_value上有3个INTEGER值：1、3、7，获取在该列上执行BIT_OR的结果，使用如下语句：

SELECT BIN(BIT_OR(bin_value))

    FROM BITS;

说明：MySQL在后台执行如下表达式：（001|011）|111，结果为111。其中BIN函数用于将结果转换为二进制位。

秒客网

MySQL （五）数据库的查询

相关文章