MySQL单表查询

查询是指从数据库中获取所需要的数据。

一般将只涉及到一张数据表的查询称为单表查询。

SELECT语句

语法

SELECT  [DISTINCT]  { * | {select_expr1, select_expr2, …} }  FROM 数据表名

        [WHERE 条件表达式1]

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

        [ORDER BY 字段名 [ASC|DESC]]

        [LIMIT [OFFSET] 记录数]

上述语法格式中，SELECT语句由多个子句组成，各子句的含义如下。

(1)SELECT [DISTINCT] { * | {select_expr1, select_expr2, …} }：指定查询结果中需要返回的值。

        DISTINCT用于在查询结果中剔除重复的数据；

        * 查询执行后返回所有字段的值；

        {select_expr1, select_expr2, …} 要检索的列表，“select_expr”可以是字段名或者表达式

(2)FROM 数据表名：表示从指定名称的数据表中查询数据。

(3)WHERE 条件表达式1：WHERE子句，是可选项，用于指定查询条件。

(4)[GROUP BY 字段名 [HAVING 条件表达式2]]：对查询结果分组，对分组后的结果过滤。

        GROUP BY子句，用于将查询结果按照指定字段进行分组；

        HAVING子句，用于对分组后的结果进行过滤。

(5)[ORDER BY 字段名 [ASC|DESC]]：将查询结果按指定字段进行排序。

        ORDER BY子句，是可选项，用于将查询结果按指定字段进行排序；

        ASC|DESC：ACS代表升序，DESC代表降序，默认升序

(6)[LIMIT [OFFSET] 记录数]：限制查询结果的数量

        LIMIT子句，是可选项，用于限制查询结果的数量；

        OFFSET表示偏移量，偏移OFFSET条记录后返回查询结果，默认为0；

        记录数：代表返回多少条记录。

简单查询

查询所有字段

即返回符合条件的记录的所有字段的值。

• 列出所有字段名称进行查询

SELECT 字段名1,字段名2,… FROM  数据表名;

• 使用通配符*进行查询

SELECT * FROM 数据表名;

• 演示

如果员工表数据被全部删除，那么将员工表的原始数据重新插入

INSERT INTO emp 
VALUES
(9839,'刘一','董事长',NULL,6000,NULL,10),
(9982,'陈二','经理',9839,3450,NULL,10),
(9369,'张三','保洁',9902,900,NULL,20),
(9566,'李四','经理',9839,3995,NULL,20),
(9988,'王五','分析员',9566,4000,NULL,20),
(9902,'赵六','分析员',9566,4000,NULL,20),
(9499,'孙七','销售',9698,2600,300,30),
(9521,'周八','销售',9698,2250,500,30),
(9654,'吴九','销售',9698,2250,1400,30),
(9844,'郑十','销售',9698,2500,0,30),
(9900,'萧十一','保洁',9698,1050,NULL,30);

列出所有字段查询，字段顺序不需要与字段在表中的顺序一致：

SELECT empno,ename,job,mgr,sal,comm,deptno FROM emp;

使用通配符*查询员工表emp中的所有记录：

SELECT * from emp;

 查询指定字段

语法：SELECT 字段名1,字段名2,… FROM  数据表名;

 查询去重数据

语法：SELECT DISTINCT 字段名 FROM 数据表名;

示例：SELECT DISTINCT deptno FROM emp;

在SELECT语句中，DISTINCT关键字可以作用于多个字段：

SELECT DISTINCT 字段名1,字段名2,… FROM 表名;

示例：

SELECT DISTINCT ename,job,deptno FROM emp;

只有这3个字段的值都相同才被认为是重复记录。 

条件查询

带比较运算符的查询

MySQL常见的比较运算符

比较运算符	说明
=	比较运算符左右两侧的操作数是否相等
<>	比较运算符左右两侧的操作数是否不相等
!=	比较运算符左右两侧的操作数是否不相等
<	比较运算符左侧操作数是否小于右侧操作数
<=	比较运算符左侧操作数是否小于等于右侧操作数
>	比较运算符左侧操作数是否大于右侧操作数
>=	比较运算符左侧操作数是否大于等于右侧操作数
BETWEEN ... AND ...	比较数据是否存在于指定范围内
IN	比较数据是否存在于指定集合内
IS NULL	比较数据是否为NULL
IS NOT NULL	比较数据是否不为NULL
LIKE	通配符匹配，获取匹配到的数据

1、=运算符用于比较运算符左右两边的操作数，如果操作数的字段类型为字符串，需要使用单引号对操作数进行包裹。WHERE子句的条件表达式中，字符串以不区分大小写的方式进比较运算。

SELECT * FROM emp WHERE ename='张三';

 2、<>运算符和!=运算符都用于比较操作数是否不相等。

SELECT * FROM emp WHERE deptno<>30;

3、<运算符用于判断左侧操作数是否小于右侧的操作数。 

SELECT * FROM emp WHERE sal<1000;

4、>=运算符用于判断左侧操作数是否大于等于右侧操作数。

SELECT * FROM emp WHERE sal>=3000;

 5、[NOT] IN运算符用于判断某个值是否 [ 不 ] 在指定集合中

语法：

SELECT *|字段名1,字段名2,… FROM 数据表名 WHERE 值 [NOT] IN (元素1,元素2,…);

SELECT * FROM emp WHERE deptno IN(10,20);

 6、当操作数为NULL时，不能使用运算符=、<>、!=进行比较，这是因为NULL代表未指定或不可预知的值。如果需要判断数据是否为NULL的时候，可以使用IS NULL进行比较。

SELECT * FROM emp WHERE mgr IS NULL;

7、模糊查询：[NOT] LIKE关键字用于比较两个操作数是否 [ 不 ] 匹配

SELECT *|{字段名1,字段名2,…}
FROM 数据表名
WHERE 值 [NOT] LIKE 匹配的字符串;

LIKE关键字支持的通配符有两个，分别是百分号(%)和下画线(_)。

%通配符是模糊查询最常用的通配符，它可以匹配任意长度的字符串，包括空字符串。

_通配符用于匹配单个字符，如果要匹配多个字符，需要使用多个_通配符，每个_通配符代表一个字符。

#查询员工表中，姓名以“一”结尾的员工信息
SELECT * FROM emp WHERE ename LIKE '%一';

#查询员工表中，姓名以“萧”开头以“一”结尾的员工信息
SELECT * FROM emp WHERE ename LIKE '萧%一';

#查询员工表中，姓名包含字符“十”的员工信息
SELECT * FROM emp WHERE ename LIKE '%十%';

#查询员工表中，姓名长度为3，并且以字符“一”结尾的员工信息
SELECT * FROM emp WHERE ename LIKE '__一';

 通配字符串中通配符以普通字符存在怎么办？

使用右斜线(\)进行转义，"\%”代表普通字符“%”，“\_”代表普通字符“_”。

#查询姓名包含%的员工信息
SELECT * FROM emp WHERE ename LIKE '%\%%';

带逻辑运算符的查询

逻辑运算符又称为布尔运算符，用于判断表达式的真假。MySQL中常见的逻辑运算符如下表所示。

逻辑运算符	说明
NOT	逻辑非，返回和操作数相反的结果
!
AND	逻辑与，操作数全部为真，则结果为1，否则结果为0
&&
OR	逻辑或，操作数只要有一个为真，则结果为1，否则结果为0
||

 1、运算符NOT和!都表示逻辑非，返回和操作数相反的结果。

#查询员工表中，部门编号不是10和30的员工信息。
SELECT * FROM emp WHERE deptno NOT IN(10,30);

2、在MySQL中，可以使用AND运算符连接两个或者多个查询条件，只有满足所有条件的记录才会被返回。

语法：

SELECT *|{字段名1,字段名2,…}
FROM 表名
WHERE 条件表达式1 AND 条件表达式2 … AND 条件表达式n;

示例： 

#查询员工表中职位为经理，并且所属部门编号为20的员工信息。
SELECT * FROM emp WHERE job='经理' AND deptno=20;

AND运算符还可以结合 [NOT] BETWEEN运算符判断某个字段的值是否 [ 不 ] 在指定的范围之内。

语法：

SELECT *|{字段名1,字段名2,…}
FROM 数据表名
WHERE 字段名 [NOT] BETWEEN 值1 AND 值2;

 示例：

#查询员工表中，员工编号为9900至9935的员工信息
SELECT * FROM emp WHERE empno BETWEEN 9900 AND 9935;
#查询员工表中，员工编号不是9500至9900的员工信息
SELECT * FROM emp WHERE empno NOT BETWEEN 9500 AND 9900;

3、在使用OR运算符查询时，只要满足任意一个查询条件，对应的数据就会被查询出来。

语法：

SELECT *|{字段名1,字段名2,…}
FROM 数据表名
WHERE 条件表达式1 OR 条件表达式2 [… OR 条件表达式n];

 例如，查询员工表中，员工职位为经理或者所属部门编号为10的员工信息：

SELECT * FROM emp WHERE job='经理' OR deptno=10;

4、OR关键字和AND关键字也可以一起使用，需要注意的是，AND的优先级高于OR。

例如：查询emp表中，员工姓名为刘一，或者员工姓名为李四并且部门编号为30的员工信息：

SELECT * FROM emp WHERE ename='刘一' or ename='李四' AND deptno=30;

高级查询

聚合函数

实际开发中，经常需要做一些数据统计操作，例如统计某个字段的最大值、最小值、平均值等，像这样对一组值执行计算，并将计算后的值返回的操作称为聚合操作，聚合操作一般通过聚合函数实现。使用聚合函数实现查询的基本语法格式如下所示：

SELECT [字段名1,字段名2,…,字段名n}] 聚合函数 FROM 数据表名;

MySQL常用的聚合函数

1、COUNT(e)函数

COUNT(*)可以统计数据表中记录的总条数。

COUNT(字段的名称) 该字段值不为NULL的个数。

例如，查询员工表中有多少个员工的记录：

SELECT COUNT(*) FROM emp;

例如，查询员工表中，奖金不为NULL的员工个数：

SELECT COUNT(COMM) FROM emp;

 2、SUM()函数

SUM()函数会对指定字段中的值进行累加，并且在数据累加时忽略字段中的NULL值。

例如，查询员工表中员工奖金的总和：

SELECT SUM(COMM) FROM emp;

3、AVG()函数

AVG()函数会计算指定字段值的平均值，并且计算时会忽略字段中的NULL值。 

例如，查询员工表中员工的平均奖金：

SELECT AVG(COMM) FROM emp;

例如，查询所有员工的平均奖金。查询时可以调用AVG()函数和IFNULL()函数进行统计，先调用IFNULL()函数将COMM字段中所有的NULL值转换为0，再调用AVG()函数统计平均值：

SELECT AVG(IFNULL(COMM,0)) FROM emp;

 4、MAX()函数

MAX()函数用于计算指定字段中的最大值，如果字段的值是数值类型，则比较的是值的大小。

例如，查询员工表中最高的工资：

SELECT MAX(sal) FROM emp;

5、MIN()函数

MIN()函数用于计算指定字段中的最小值，如果字段的值是数值类型，则比较的是值的大小。

例如，查询员工表中最低的工资：

SELECT MIN(sal) FROM emp;

 分组查询

即对表里的记录按某种特征进行分组，主要目的是对每一组统计数据。

在MySQL中，可以使用GROUP BY根据指定的字段对结果集进行分组，如果某些记录的指定字段具有相同的值，那么分组后被合并为一条数据。使用GROUP BY分组查询的语法格式如下所示：

SELECT 字段名1,字段名2,[表达式]…
FROM 数据表名
GROUP BY 字段名1，字段名2，…[HAVING 条件表达式];

 单独使用GROUP BY分组

单独使用GROUP BY进行分组，将根据指定的字段合并数据行。

例如，查询员工表的部门编号有哪几种：

SELECT deptno FROM emp GROUP BY deptno;

GROUP BY和聚合函数一起使用

如果分组查询时要进行统计汇总，此时需要将GROUP BY和聚合函数一起使用。

例如，统计员工表各部门的薪资总和或平均薪资：

SELECT deptno,AVG(sal),SUM(sal) FROM emp GROUP BY deptno;

 GROUP BY和HAVING关键字一起使用

通常情况下GROUP BY和HAVING关键字一起使用，用于对分组后的结果进行条件过滤。

例如，查询员工表中平均工资小于3000的部门编号及这些部门的平均工资：

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)<3000;

排序查询

如果想要对查询结果按指定的方式排序，可以使用ORDER BY对查询结果进行排序。查询语句中使用ORDER BY的基本语法格式如下所示：

SELECT *|{ 字段名1,字段名2,…}
FROM 表名
ORDER BY 字段名1 [ASC | DESC]，字段名2 [ASC | DESC]…;

参数ASC（默认值）表示按照升序进行排序，DESC表示按照降序进行排序。

例如，查询员工表中，部门编号为30的员工信息，查询出的结果根据员工工资升序排列： 

SELECT * from emp WHERE deptno=30 ORDER BY sal;

 查询员工表中，部门编号为30的员工信息，查询出的结果根据工资降序排列：

SELECT * from emp WHERE deptno=30 ORDER BY sal DESC;

使用ORDER BY按照指定字段进行排序时，如果字段的值中包含NULL，NULL会被当做最小值进行排序。

例如，查询员工表中，部门编号为30的员工信息，并且根据奖金升序排序：

SELECT * from emp WHERE deptno=30 ORDER BY comm;

 ORDER BY可以对多个字段的值进行排序，并且每个排序字段可以有不同的排序顺序。

例如，查询员工表中，部门编号为30的所有记录，查询出的记录先按职位升序排序，职位相同的记录再按员工编号降序排序：

SELECT * from emp WHERE deptno=30 ORDER BY job,empno DESC;

限量查询

MySQL中提供了一个关键字LIMIT，可以指定查询结果从哪一条记录开始以及一共查询多少条信息：

SELECT 字段名1,字段名2,…
FROM 数据表名
LIMIT [OFFSET，] 记录数;

 在上面的语法格式中，LIMIT后面可以跟2个参数，第一个参数OFFSET为可选值，表示偏移量，如果偏移量为0则从查询结果的第一条记录开始，偏移量为1则从查询结果中的第二条记录开始，以此类推，如果不指定OFFSET的值，其默认值为0。第二个参数“记录数”表示返回查询记录的条数。 除了指定查询记录数，LIMIT还可以通过指定OFFSET的值，指定查询的偏移量，也就是查询时跳过几条记录。

例如，查询员工表中，工资最高的前5名的员工信息：

SELECT * FROM emp ORDER BY sal DESC LIMIT 5;

例如，查询员工表中，工资前2~5名的员工信息：

SELECT * FROM emp ORDER BY sal DESC LIMIT 1,4;

 内置函数

内置函数也可以称为系统函数，无需开发者定义，直接调用即可。从功能方面划分，可以分为数学函数、字符串函数、日期和时间函数、条件判断函数、加密函数等。

常见函数

数学函数

函数声明	功能描述
ABS(x)	返回x的绝对值
SQRT(x)	返回x的非负2次方根
MOD(x,y)	返回x被y除后的余数
CEILING(x)	返回不小于x的最小整数
FLOOR(x)	返回不大于x的最大整数
ROUND(x,y)	对x进行四舍五入操作，小数点后保留y位
TRUNCATE(x,y)	舍去x中小数点y位后面的的数
SIGN(x)	返回x的符号，x的值为负数、零和正数时依次返回 -1、0和1

字符串函数

函数声明	功能描述
LENGTH(str)	返回字符串str的长度
CONCAT(s1,s2,…)	返回一个或者多个字符串连接产生的新的字符串
TRIM(str)	删除字符串两侧的空格
REPLACE(str,s1,s2)	使用字符串s2替换字符串str中所有的字符串s1
SUBSTRING(str,n,len)	返回字符串str的子串，起始位置为n，长度为len
REVERSE(str)	返回字符串反转后的结果
LOCATE(s1,str)	返回子串s1在字符串str中的起始位置

 CONCAT()函数

执行CONCAT()函数会返回函数参数连接之后的字符串。如执行CONCAT('a','_'，'b')，会返回字符串'a_b'。

例如，查询员工信息时，将部门编号为30的员工姓名、员工职位及员工所属部门编号的信息，在一列中显示，各个字段值之间使用下画线“_”进行连接：

SELECT CONCAT(ename,'_',job,'_',deptno) FROM emp WHERE deptno=30;

条件判断函数

函数声明	功能描述
IF(expr,v1,v2)	如果expr表达式为true返回v1，否则返回v2
IFNULL(v1,v2)	如果v1不为NULL返回v1，否则返回v2
CASE expression     WHEN c1 THEN result1     WHEN c2 THEN result2    ...     WHEN cN THEN resultN      ELSE result END	CASE表示函数开始，END表示函数结束。 如果 c1成立，则返回result1；如果c2成立，则返回result2， 当全部不成立则返回 result。 在执行过程中，当有一个条件成立之后，后面的就不执行了

 IF()函数

IF()函数有3个参数，具体格式为IF(expr,v1,v2)，如果表达式expr成立，返回结果v1；否则，返回结果v2。

例如，查询员工表中，部门编号为30的员工姓名、员工奖金及员工部门编号的信息；如果奖金为NULL，返回“无奖金”：

SELECT ename,IF(ISNULL(comm),'无奖金',comm),deptno FROM emp WHERE deptno=30;

设置别名

在查询数据时，可以为数据表和字段取别名，可以使用这个别名代替原来的数据表名和字段名。

在查询操作时，当数据表名很长或者执行一些特殊查询的时候，为了方便操作，可以为数据表取一个别名，用这个别名代替数据表的名称。MySQL中为数据表起别名的基本语法格式如下所示。

SELECT * FROM 数据表名 [AS] 别名;

例如，查询员工信息时，为emp数据表起一个别名e，并且使用别名e查询部门编号为30的员工信息：

SELECT * FROM emp e WHERE e.deptno=30;

在前面的查询操作中，每条记录中的列名都是定义表时的字段名，有时为了将查询结果更加直观的显示，可以为查询的字段取一个别名。SELECT语句中为字段起别名的基本语法格式如下所示。

SELECT 字段名 [AS] 别名[,字段名 [AS] 别名,……] FROM 数据表名;

 例如，查询员工表中，部门编号为30的员工姓名、员工奖金及员工部门编号的信息，查询结果返回时，将字段ename的名称设置别名“姓名”，字段comm的名称设置别名“奖金”，字段deptno的名称设置别名“部门编号”：

SELECT ename AS '姓名', comm '奖金',deptno '部门编号' FROM emp WHERE deptno=30;

 上机实践：图书管理系统的单表查询

实践需求1：查询可借阅图书清单。查询当前书店中可借阅图书清单，清单中只显示图书名称和上架时间即可。state为0表示可借阅。

实践需求2：根据图书名称排序查询。根据图书名称升序排序图书信息，图书信息只需显示查询结果的前5条的图书名称、价格和状态即可。

实践需求3：根据图书价格查询，查询价格大于50的图书信息，图书信息只需显示图书名称和价格即可。

实践需求4：根据图书区间价格查询，查询当前价格大于等于30并且小于等于50的图书信息，图书信息只需显示图书名称和价格即可。

实践需求5：根据借阅状态查询，查询已借阅的图书信息，图书信息只需显示图书名称、借阅人编号和借阅时间即可。state=1表示可借阅

实践需求6：根据书名包含的关键字查询。查询书店中名称包含“Java”的图书信息，图书信息只需显示图书名称即可。

实践需求7：根据书名结尾关键字查询，查询以“入门”结尾的图书信息，图书信息只需显示图书名称即可。

实践需求8：根据多条件查询，查询“西游记”或者“红楼梦”的图书信息，图书信息只需显示图书名称和价格即可。