mysql第六章聚合函数笔记_第六章 运算符和函数

回顾和概述#

子查询

出现在其他SQL语句中的SELECT子句，必须要出现在小括号内，子查询的外层可以是常见的SELECT,INSERT,UPDATE,DELETE语句，在子查询中可以包含多个关键字和条件，如：GROUP BY,ORDER BY,LIMIT以及相关的函数等

引发子查询的情况主要有三种：

1.由比较运算符引发的子查询

2.由IN和NOT IN引发的子查询

3.由EXIST或NOT EXIST引发的子查询

连接：在MySQL中的SELECT语句，包括多表更新，多表删除中，都可以使用连接

连接类型：

内连接：显示左表和右表都符合连接条件的记录

外连接：

左外连接：显示左表中的全部记录和右表中符合连接条件的记录

右外连接：显示右表中的全部记录和左表中符合连接条件的记录

CREATE...SELECT语句和INSERT...SELECT语句

这章主要学习MySQL数据库中的运算符和函数

根据功能可以分为：

1.字符函数

2.数值运算符与函数

3.比较运算符与函数

4.日期时间函数

5.信息函数

6.聚合函数

7.加密函数

字符函数

函数名称

描述

CONCAT()

字符连接

CONCAT)WS()

使用指定的分隔符进行字符连接

FORMAT()

数字格式化

LOWER()

转换成小写字母

UPPER()

转换成大写字母

LEFT()

获取左侧字符

RIGHT()

获取右侧字符

CONCAT()：连接两个或多个字符串

演示：

SELECT CONCAT('imooc','MySQL');#将'imooc'和'MySQL'连接起来SELECT CONCAT('imooc','-','MySQL');#将'imooc'和'-'和'MySQL'连接起来在实际应用中如果存在一张表，有两个字段first_name和last_name，现在要把这两个字段连接起来查询，可以这样：SELECT CONCAT(first_name,last_name) AS fullname FROM test;#从test表中查询first_name和last_name字段进行连接`

CONCAT_WS()：用分隔符连接两个或多个字符串。最少需要三个参数，第一个参数为分隔符，第二个，第三个以及后续的参数分别指的是需要连接的字符传

SELECT CONCAT_WS('|','A','B','C');采用'|'分隔符把ABC连接在一起

SELECT CONCAT_WS('-','imooc','MySQL','Functions');将imooc,MySQL,Function三个字符串用'|'连接起来

FORMAT():数字格式化，因为返回结果是字符型所以放到了字符函数里

示例演示：

SELECT FORMAT(12560.75,2);#格式化数字12560.75保留两位小数。得到的结果：12,560.75

SELECT FORMAT(12560.75,1);#保留1位小数。得到的结果：12,560.7

SELECT FORMAT(12560.75,0);#保留到整数位。得到的结果：12,560

LOWER()：转换成小写字母

UPPER()：转换成大写字母

示例演示：

LOWER('MySQL');#全部转换为小写，得到的结果为：mysql

UPPER('mysql');#全部转换为大写，得到的结果为：MYSQL

LEFT()：获取左侧字符串，需要两个参数，第一个参数是要获取哪个字符串的内容，第二个参数是要取几位

示例演示：

SELECT LEFT('MySQL',2);#获取字符串"MySQL的前两位。结果为：My"

获取字符串前两位并且转换为小写：函数嵌套

SELECT LOWER(LEFT('MySQL',2));#获取字符串"MySQL"的前两位，然后转换成小写。结果为：my

RIGHT()：获取右侧的字符串，需要两个参数

SELECT RIGHT('MySQL',3);#获取字符串'MySQL'的后三位。结果为：SQL

名称

描述

LENGTH()

获取字符串长度

LTRIM()

删除前导空格

RTRIN()

删除后续空格

TRIM()

删除前导和后续空格

SUBSTRING()

字符串截取

[NOT] LIKE

模式匹配

REPLACE()

字符串替换

LENGTH()：获取字符串的长度

示例演示：

SELECT LENGTH('MySQL');#获取字符串'MySQL'的长度，结果为5

如果字符串中间含有空格，空格也算一位：

SELECT LENGTH('My SQL');#结果为6

LREIM()：删除前导空格

RTRIM()：删除后续空格

TRIM()：删除前导和后续空格

示例演示：

SELECT LTRIM(' MySQL ');#字符串前边2个空格，后边有4个空格。看到前边的空格被删除了，但是看不到后边的空格有没有被删除

SELECT LENGTH(' MySQL ');#长度为11

SELECT LENGTH(LTRIM(' MySQL '));#长度为9

SELECT LENGTH(RTRIM(' MySQL '));#长度为7

SELECT LENGTH(TRIM(' MySQL '));#长度为5

TRIM()删除指定字符串：

示例演示：

删除指定前导字符串：

SELECT TRIM(LEADING '?' FROM '??MySQL???');#从字符串'??MySQL???'的前边删除?。结果为：'MySQL???'

删除指定后续字符串：

SELECT TRIM(TRAILING '?' FROM '??MySQL???');#从字符串'??MySQL???'的后边删除?。结果为：'??MySQL'

删除指定两边的字符串：

SELECT TRIM(LEADING '?' FROM '??MySQL???');#从字符串'??MySQL???'的两边删除?。结果为：'MySQL'

不能删除中间的，如：不能删除'??My??SQL???'中间的??

想要删除中间的字符，可以使用字符串替换函数REPLACE():有三个参数，第一个参数是源字符串，第二个参数是需要替换的字符串，第三个参数是替换成的字符串，为空就是替换成空(删除)

不仅仅是搜索到后就替换一次，只要源字符串中含有需要替换的字符串，都会进行替换

示例演示：

SELECT REPLACE('??My??SQL???','?','');#结果为'MySQL'

SUBSTRING()：字符串截取函数，三个参数，第一个是需要截取的字符串，第二个是从哪个位置开始截取(和数组不一样，第一个位置为1)，第三个是截取几个

示例演示：

SELECT SUBSTRING('MySQL',1,2);#结果为：'My'。从字符串'MySQL'中的第一个位置开始，截取两个字符

如果没有写第三个参数，默认为截取到字符串结尾

SELECT SUBSTRING('MySQL',3);#结果为：'SQL'。从字符串'MySQL'中的第3个位置开始，截取到最后

开始位置也可以是负值：表示从后边第几个字符开始：

SELECT SUBSTRING('MySQL',-1);#结果为：'L'。从字符串'MySQL'中的倒数第1个位置开始，截取到最后

第三个参数长度不能为负值，在某些编程语言中是可以的

[NOT] LIKE:模式匹配：

示例演示：

SELECT 'MySQL' LIKE 'M%';#结果为1(TRUE)在字符串'MySQL'中找到了'M%'的字符串，%代表0个或多个字符

在之前的test表中有一个first_name为tom%的名字

SELECT * FROM test WHERE frist_name LIKE '%o%';#查找test表中first_name含有o的记录

查找含有'%'的字符串

SELECT * FROM test WHERE frist_name LIKE '%%%';#错误，显示了所有结果，MySQL把中间那个%也当成了通配符

可以这样写：

SELECT * FROM test WHERE frist_name LIKE '%1%%' ESCAPE '1';#把1后边的字符当成普通字符而不是通配符

通配符中还有一个'_'代表一个任意字符

数值运算符和函数

数值运算符：+、-、*、/等

SELECT 3+4;#结果为7

函数

名称

描述

CEIL()

进一取整

FLOOR()

舍一取整

MOD()

取余数(取模)

POWER()

幂运算

ROUND()

四舍五入

TRUNCATE()

数字截取

CEIL()：进一取整，只要有小数位，整数位就会加一

SELECT CEIL(3.01);#结果为4

与CEIL()相反的：小数位有多少都会舍去

SELECT FLOOR(3.99);#结果为3

DIV：整数除法，得到的结果为整数，舍去余数

SELECT 3/4;#结果为0.7500

SELECT 3 DIV 4;#结果为0

MOD：取余数(取模)可以对整数取模，也可以对浮点数取模(除数不能是浮点数)，也可以写成%

SELECT 5 MOD 3;#结果为2

SELECT 5 % 3;#也可以用%

SELECT 5.3 MOD 3；#对浮点数取模，结果为2.3

POWER()：幂运算

SELECT POWER(3,2);#3的2次方，结果为9

ROUND()：四舍五入，两个参数，第一个是需要四舍五入的数字，第二个是保留的小数位是

SELECT ROUND(3.652,2);#对3.625四舍五入，保留两位小数，结果为3.5

SELECT ROUND(3.652,1);#保留一位小数，结果为3.7

位数如果写0，表示保留到整数位

SELECT ROUND(3.652,0);#结果为4

TRUNCATE()：数字截取，两个参数，第一个需要截取的位数，第二个为保留的小数位数(可以为0)。只截取，不做四舍五入。

SELECT TRUNCATE(125.89,2);#对125.89截取，保留两位小数，结果不变，还是125.89

SELECT TRUNCATE(125.89,1);#保留一位小数，结果为125.8

SELECT TRUNCATE(125.89,1);#保留0位小数，结果为125

也可以写成-1,把相对应的整数位变成0(实际应用中不多)

SELECT TRUNCATE(125.89,-1);#保留-1位小数，结果为120

比较运算符和函数

名称

描述

[NOT] BETWEEN...AND...

[不]在范围之内

[NOT] IN()

[不]在列出值范围内

IS [NOT] NULL

[不] 为空

例如：

SELECT 15 BETWEEN 1 AND 22;#数字15在1和22之间，结果为1

SELECT 35 BETWEEN 1 AND 22;#数字35在1和22之间，结果为0

SELECT 35 NOT BETWEEN 1 AND 22;#数字35不在1和22之间，结果为1

SELECT 10 IN(5,10,15,20);#10在(5,10,15,20)之中，结果为1

SELECT 13 IN(5,10,15,20);#10在(5,10,15,20)之中，结果为0

SELECT NULL IS NULL;#结果为1

SELECT '' IS NULL;#空的字符串为空，结果为0，空字符串不是空

SELECT 0 IS NULL;#0是空，结果为0，0不是空

在之前的数据表test中：

Paste_Image.png

最后一个first_name为空

SELECT * FROM test WHERE first_name IS NULL;#选择first_name为空的记录，结果为最后一条记录

SELECT * FROM test WHERE first_name IS NOT NULL;#选择first_name不为空的记录，结果为前三条记录

日期时间函数

名称

描述

NOW()

当前日期和时间

CURDATE()

当前日期

CURTIME()

当前时间

DATE_ADD()

日期变化

DATEDIFF()

日期差值

DATE_FORMAT()

日期格式化

SELECT NOW();#打印出当前日期时间

SELECT CURDATE();#打印当前日期

SELECT CURTIME();#打印当前时间

DATE_ADD()：日期变化。不仅仅是增加日期，还能减少

SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY);#在2014-3-12上加365天，结果是2015-3-12

SELECT DATE_ADD('2014-3-12',INTERVAL -365 DAY);#在2014-3-12上减365天，结果是2013-3-12

SELECT DATE_ADD('2014-3-12',INTERVAL 1 YEAR);#在2014-3-12上加1年，结果是2015-3-12

SELECT DATE_ADD('2014-3-12',INTERVAL 3 WEEK);#在2014-3-12上加3周，结果是2015-4-2

DATEDIFF()：日期差值

SELECT DATEDIFF('2013-3-12','2014-3-12');#'2013-3-12'和'2014-3-12'相差多少天，结果为365

DATE_FORMAT()：日期格式化

SELECT DATE_FORMAT('2014-3-2','%m/%d/%Y');#结果为：03/02/2014。将2014-3-2格式化成斜线分割的，月份在前，日期在中间，年在后边的格式，如果格式化前只有一位数字%m%d会在前边加上0

关于有哪些格式，可以去mysql手册中查看

还有一些日期函数很少用到，其实MySQL数据类型中就很少用到日期类型

信息函数

名称

描述

CONNECTION_ID()

连接ID

DATABASE()

当前数据库

LAST_INSERT_ID()

最后插入记录的ID号

USER()

当前用户

VERSION()

版本信息

CONNECTION_ID()：返回当前连接的ID，也是线程的ID

SELECT CONNECTION_ID();#结果为3，机器不同，返回结果不同

SELECT DATABASE();#返回当前数据库，结果为imooc

LAST_INSERT_ID()：返回最后插入的ID，表中必须含有一个自动增长字段

修改test表，添加自动增长的id字段，并设为主键

ALTER TABLE test ADD id SMALLINT UNSIGNED KEY AUTO_INCREMENT FIRST;#在test表中添加id字段，自动增长，设为主键

在表中增加记录：

INSERT test(first_name,last_name) VALUES('11','11');#插入一条记录

SELECT LAST_INSERT_ID();#得到最后插入的ID，结果为5

同时写入多条记录：

INSERT test(first_name,last_name) VALUES('AA','BB'),('CC','DD');#插入一条记录

SELECT LAST_INSERT_ID();#得到最后插入的ID，结果为6

同时写入多条记录时，只返回插入的第一条记录的id

SELECT USER();#当前登录的用户，结果为：root@localhost

SELECT VERSION();#打印MySQL的版本号，结果为5.5.37

聚合函数

聚合函数：只有一个返回值

名称

描述

AVG()

平均值

COUNT()

计数

MAX()

最大值

MIN()

最小值

SUM()

求和

SELECT AVG(3,4,5);#错误

这个聚合函数应该用于表中

SELECT AVG(id) FROM test;#没有实际意义，得到的结果是4

在tdb_goods表中存在商品价格，计算商品价格：

SELECT AVG(goods_price) AS avg_price FROM tdb_goods;#结果为5654.8095238

SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;#利用ROUND()函数进行四舍五入，结果为5654.81

SELECT COUNT(goods_id) AS counts FROM tdb_goods;#计算tdb_goods表中的goods_id数量

SELECT MAX(goods_price) AS counts FROM tdb_goods;#商品价格的最大值

SELECT MIN(goods_price) AS counts FROM tdb_goods;#商品价格的最小值

SELECT SUM(goods_price) AS counts FROM tdb_goods;#商品的价格和

加密函数

名称

描述

MD5()

信息摘要算法

PASSWORD()

密码算法

SELECT MD5('admin');#结果为一个32位十六进制数字

SELECT PASSWORD('admin');#进行密码计算

建议在开发中，使用MD5()

PASSWORD()：在修改MySQL用户密码时使用

SET PASSWORD=PASSWORD('admin');#把当前用户的密码改成admin

退出重新登陆，原来的密码就不能使用了，可以使用admin登录

Paste_Image.png