回顾和概述#

子查询
出现在其他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()：连接两个或多个字符串
演示：
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()：获取字符串的长度
示例演示：
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()：进一取整，只要有小数位，整数位就会加一
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

比较运算符和函数

例如：
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不为空的记录，结果为前三条记录

日期时间函数

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，也是线程的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

聚合函数

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

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;#商品的价格和

加密函数

SELECT MD5('admin');#结果为一个32位十六进制数字
SELECT PASSWORD('admin');#进行密码计算

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

PASSWORD()：在修改MySQL用户密码时使用
SET PASSWORD=PASSWORD('admin');#把当前用户的密码改成admin
退出重新登陆，原来的密码就不能使用了，可以使用admin登录

Paste_Image.png