第6章 MySQL函数

6.1 MySQL函数简介

6.2 数学函数

6.2.1 绝对值函数ABS(x)和返回圆周率的函数PI()

6.2.2 平方根函数SQRT(x)和求余函数MOD(x,y)

6.2.3 获取整数的函数 CEIL(x)、CEILING(x)和 FLOOR(x)

6.2.4 获取随机数的函数RAND()和RAND(x)

6.2.5 函数 ROUND(x)、ROUND(x，y)和 TRUNCATE(x,y)

6.2.6 符号函数SIGN(x)

6.2.7 幂运算函数 POW(x,y)、POWER(x,y)和 EXP(x)

6.2.8 对数运算函数 LOG(x)和 LOGlO(x)

6.2.9 角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)

6.2.10 正弦函数SIN(x)和反正弦函数ASIN(x)

6.2.11 余弦函数COS(x)和反余弦函数ACOS(x)

6.2.12 正切函数、反正切函数和余切函数

6.3 字符串函数

6.3.1 计算字符串字符数的函数和字符串长度的函数

6.3.2 合并字符串函数 CONCAT(sl，s2，...)、CONCAT_WS(x，sl，s2，...)

6.3.3 替换字符串的函数 INSERT(sl，x，len，s2)

6.3.4 字母大小写转换函数

6.3.5 获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)

6.3.6 填充字符串的函数 LPAD(sl,len,s2)和 RPAD(sl,len,s2)

6.3.7 删除空格的函数LTRIM(s)、RTRJM(s)和TRIM(s)

6.3.8 删除指定字符串的函数TRIM(sl FROM s)

6.3.9 重复生成字符串的函数REPEAT(s,n)

6.3.10 空格函数 SPACE(n)和替换函数 REPLACE(s,sl,s2)

6.3.11 比较字符串大小的函数STRCMP(sl,s2)

6.3.12 获取子串的函数 SUBSTRING(s,n,len)和 MID(s,n,len)

6.3.13 匹配子串开始位置的函数

6.3.14 字符串逆序的函数REVERSE(s)

6.3.15 返回指定位置的字符串的函数

6.3.16 返回指定字符串位置的函数FIELD(s,sl,s2,...)

6.3.17 返回子串位置的函数FIND_IN_SET(sl,s2)

6.3.18 选取字符串的函数MAKE_SET(x,sl,s2,...)

6.4 日期和时间函数

6.4.1 获取当前日期的函数和获取当前时间的函数

6.4.2 获取当前日期和时间的函数

6.4.3 UNIX时间戳函数

6.4.4 返回UTC日期的函数和返回UTC时间的函数

6.4.5 获取月份的函数 MONTH(date)和 MONTHNAME(date)

6.4.6 获取星期的函数 DAYNAME(d). DAYOFWEEK(d)和 WEEKDAY(d)

6.4.7 获取星期数的函数 WEEK(d)和 WEEKOFYEAR(d)

6.4.8 获取天数的函数 DAYOFYEAR(d)和 DAYOFMONTH(d)

6.4.9 获取年份、季度、小时、分钟和秒钟的函数

6.4.10 获取日期的指定值的函数EXTRACT(type FROM date)

6.4.11 时间和秒钟转换的函数

6.4.12 计算日期和时间的函数

6.4.13 将日期和时间格式化的函数

6.5 条件判断函数

6.5.1 IF(expr，vl，v2)函数

6.5.2 IFNULL(vl,v2)函数

6.5.3 CASE 函数

6.6 系统信息函数

6.6.1 获取MySQL版本号、连接数和数据库名的函数

6.6.2 获取用户名的函数

6.6.3 获取字符串的字符集和排序方式的函数

6.6.4 获取最后一个自动生成的ID值的函数

6.7 加/解密函数.

6.7.1 加密函数 PASSWORD(str)

6.7.2 加密函数 MD5(str)

6.7.3 加密函数ENCODE(str,pswd_str)

6.7.4 解密函数 DECODE(crypt_str,pswd一str)

6.8 其他函数

6.8.1 格式化函数FORMAT(x,n)

6.8.2 不同进制的数字进行转换的函数

6.8.3 IP地址与数字相互转换的函数

6.8.4 加锁函数和解锁函数

6.8.5 重复执行指定操作的函数

6.8.6 改变字符集的函数

6.8.7 改变数据类型的函数

6.9 综合案例——MySQL函数的使用

6.10 专家解惑

6.11 经典习题

第7章 查询数据

7.1 基本査询语句

7.2 单表查询

7.2.1 查询所有字段

7.2.2 査询指定字段

7.2.3 査询指定记录

7.2.4 带IN关键字的査询

7.2.5 带 BETWEEN AND 的范围查询

7.2.6 带LIKE的字符匹配査询

7.2.7 查询空值

7.2.8 带AND的多条件査询

7.2.9 带OR的多条件查询

7.2.10 查询结果不重复

7.2.11 对查询结果排序

7.2.12 分组査询

7.2.13 使用LIMIT限制査询结果的数量

7.3 使用聚合函数查询

7.3.1 COUNT()函数

7.3.2 SUM()函数

7.3.3 AVG()函数

7.3.4 MAX()函数

7.3.5 MIN()函数

7.4 连接査询

7.4.1 内连接查询

7.4.2 外连接査询

7.4.3 复合条件连接查询

7.5 子査询

7.5.1 带ANY、SOME关键字的子查询

7.5.2 带ALL关键字的子查询

7.5.3 带EXISTS关键字的子查询

7.5.4 带IN关键字的子查询

7.5.5 带比较运算符的子查询

7.6 合并查询结果

7.7 为表和字段取别名

7.7.1 为表取别名

7.7.2 为字段取别名

7.8 使用正则表达式查询

7.8.1 查询以特定字符或字符串开头的记录

7.8.2 查询以特定字符或字符串结尾的记录

7.8.3 用符号来替代字符串中的任意一个字符

7.8.4 使用■"和"+"来匹配多个字符

7.8.5 匹配指定字符串

7.8.6 匹配指定字符中的任意一个

7.8.7 匹配指定字符以外的字符

7.8.8 使用{nJ或者{n，ni}来指定字符串连续出现的次数

7.9 综合案例——数据表查询操作

7.10 专家解惑

7.11 经典习题

第8章 插入、更新与删除数据

8.1 插入数据

8.1.1 为表的所有字段插入数据

8.1.2 为表的指定字段插入数据

8.1.3 同时插入多条记录

8.1.4 将查询结果插入到表中

8.2 更新数据

8.3 删除数据

8.4 综合案例——记录的插入、更新和删除

8.5 专家解惑

8.6 经典习题

第6章 MySQL函数

6.1 MySQL函数简介

函数表示对输入参数值返回一个具有特定关系的值,MySQL提供了大量丰富的函数,在进行数据库管理以及数据的查询和操作时将会经常用到各种函数.通过对数据的处理,数据库功能可以变得更加强大,更加灵活地满足不同用户的需求.各类函数从功能方面主要分为以下几类:数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等. 本章将分类介绍不同函数的使用方法.

6.2数学函数

数学函数主要用来处理数值数据,主要的数学函数有:绝对值函数、三角函数(包括正弦 函数、余弦函数、正切函数、余切函数等)、对数函数、随机数函数等.在有错误产生时,数 学函数将会返回空值NULL.本节将介绍各种数学函数的功能和用法.

6.2.1绝对值函数ABS(x)和返回圆周率的函数Pl()

ABS(X)返回X的绝对值.
PI()返回圆周率n的值.默认的显示小数位数是6位.

6.2.2平方根函数SQRT(x)和求余函数MOD(x,y)

SQRT(x)返回非负数x的二次方根.
负数没有平方根,因此-49返回的结果为NULL.
MOD(x,y)返回x被y除后的余数,MOD()对于带有小数部分的数值也起作用,它返回除法运算后的精确余数.

6.2.3 获取整数的函数 CEIL(x)、CEILING(x)和 FLOOR(x)

CEIL(x)和CEILING(x)意义相同,返回不小于x的最小整数值,返回值转化为一个BIGINT.
FLOOR(x)返回不大于x的最大整数值,返回值转化为一个BIGINT.

6.2.4获取随机数的函数RANDOM RAND(x)

RAND(x)返回一个随机浮点值v,范围在0到1之间(即0 <= v <= 1.0).若已指定一个整数参数X,则它被用作种子值,用来产生重复序列.
注意点:

• 不带参数的RAND()每次产生的随机数值是不同的.
• 当RAND(x)的参数相同时,将产生相同的随机数,不同的x产生的随机数值 不同.

6.2.5 函数 ROUND(x)、ROUND(x,y)和 TRUNCATE(x.y)

ROUND(x)返回最接近于参数x的整数,对x值进行四舍五入.四舍五入处理之后,只保留了各个值的整数部分.
ROUND(x,y)返回最接近于参数x的数,其值保留到小数点后面y位,若y为负值,则将保留x值到小数点左边y位.
y值为负数时,保留的小数点左边的相应位数直接保存为0,不进行四舍五入.
TRUNCATE(x,y)返回被舍去至小数点后y位的数字x.若y的值为0,则结果不带有小数点或不带有小数部分.若y设为负数,则截去(归零)x小数点左起第y位开始后面所有低位 的值.

6.2.6符号函数SIGN(x)

SIGN(x)返回参数的符号,x的值为负、零或正时返回结果依次为-1、0或1.

6.2.7 幂运算函数 POW(x,y)、POWER(x,y)和 EXP(x)

POW(x,y)或者POWER(x,y)函数返回x的y次乘方的结果值.
EXP(x)返回e的x乘方后的值.

6.2.8对数运算函数LOG(x)和LOGIO(x)

LOG(x)返回x的自然对数,x相对于基数e的对数.
对数定义域不能为负数,因此LOG(-3)返回结果为NULL.
LOG 10(x)返回x的基数为10的对数.

6.2.9角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)

RADIANS(x)将参数x由角度转化为弧度.
DEGREES(x)将参数x由弧度转化为角度.

6.2.10 正弦函数SIN(x)和反正弦函数ASIN(x)

SIN(x)返回x正弦,其中x为弧度值.ASIN(x)返回x的反正弦,即正弦为x的值.若x不在-1到1的范围之内,则返回NULLo
函数ASIN和SIN互为反函数;ASIN(3)中的参数3超出了正弦值的范围,因此返回NULL.

6.2.11余弦函数COS(x)和反余弦函数ACOS(x)

COS(x)返回X的余弦,其中X为弧度值.
可以看到,COS(0)值为 1; COS(PI())值为-1:COS(1)值为 0.5403023058681398.
ACOS(x)返回x的反余弦,即余弦是x的值.若x不在-1~1的范围之内,则返回NULL.
函数ACOS和COS互为反函数.

6.2.12正切函数、反正切函数和余切函数

TAN(x)返回x的正切,其中x为给定的弧度值.
ATAN(x)返回x的反正切,即正切为x的值.
函数ATAN和TAN互为反函数.
COT(x)返回x的余切.
函数COT和TAN互为倒函数.

6.3字符串函数

字符串函数主要用来处理数据库中的字符串数据,MySQL中字符串函数有:计算字符串 长度函数、字符串合并函数、字符串替换函数、 字符串比较函数、查找指定字符串位置函数等. 本节将介绍各种字符串函数的功能和用法.

6.3.1计算字符串字符数的函数和字符串长度的函数

CHAR_LENGTH(str)返回值为字符串str所包含的字符个数.一个多字节字符算作一个单字符.
LENGTH(str)返回值为字符串的字节长度,使用utf8(UNICODE的一种变长字符编码,又称万国码)编码字符集时,一个汉字是3个字节,一个数字或字母算一个字节.
可以看到,计算的结果与CHAR_LENGTH相同,因为英文字符的个数和所占的字节相同,一个字符占一个字节.

6.3.2 合并字符串函数CONCAT(s1,s2,...)、CONCAT_WS(x,s1,s2,...)

CONCAT(sl,s2,...)返回结果为连接参数产生的字符串,或许有一个或多个参数.如有任何一个参数为NULL,则返回值为NULL.如果所有参数均为非二进制字符串,则结果为非二进制字符串.如果自变量中含有任一二进制字符串,则结果为一个二进制字符串.

CONCAT('My SQL', ’5.7’)返回两个字符串连接后的字符串;CONCAT(’My’,NULL,'SQL')中有一个参数为NULL,因此返回结果为NULL.
CONCAT_WS(x,s1,s2,...). CONCAT_WS 代表 CONCAT With Separator,是CONCAT()的特殊形式 .第一个参数x是其他参数 的分隔符,分 隔符的位置放在要连接的两个字符串之间. 分隔符可以是一个字符串,也可以是其他参数.如果分隔符为NULL,则结果为NULL.函数会忽略任何分隔符参数后的NULL值.

6.3.3替换字符串的函数INSERT(s1,x,len,s2)

INSERT(sl,x,len,s2)返回字符串si,其子字符串起始于x位置和被字符串s2取代的len字符.如果x超过字符串长度,则返回值为原始 字符串.假如 len 的长度大于其他字符串的长度,则从位置x开始替换.若任何一个参数为NULL,则返回值为NULL.

第一个函数INSERT('Quesf, 2, 4, ’What')将“Quest”第2个字符开始长度为4的字符串替换为What,结果为“QWhat” :第二个函数INSERT('Quest', -1, 4, ’What’)中起始位置-1超出了字符串长度,直接返回原字符;第三个函数INSERT('Quest', 3, 原字符串长度,则从第3个字符开始,截取后面所有的字符,并替换为指定字符What,结果 为 “QuWhat”.

6.3.4字母大小写转换函数

LOWER(str)或者LCASE(str)可以将字符串str中的字母字符全部转换成小写字母.
UPPER(str)或者UCASE(str)可以将字符串str屮的字母字符全部转换成大写字母.

6.3.5 获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)

LEFT(s.n)返回字符串s开始的最左边n个字符.
RIGHT(s,n)返回字符串中最右边n个字符.

6.3.6 填充字符串的函数 LPAD(s1,len,s2)和 RPAD(s1,len,s2)

LPAD(sl,len,s2)返回字符串si,其左边由字符串s2填补到len字符长度.假如si的长度大于len,则返回值被缩短至len字符.
RPAD(sl,len,s2)返回字符串si,其右边被字符串s2填补至len字符长度.假如字符串si的长度大于len,则返回值被缩短到len字符长度.

6.3.7 删除空格的函数 LTRIM(s)、RTRIM(s)和 TRIM(s)

LTRIM(s)返回字符串s.字符串左侧空格字符被删除.
RTRIM(s)返回字符串s,字符串右侧空格字符被删除.
TRIM(s)删除字符串s两侧的空格.

6.3.8 删除指定字符串的函数TRIM(s1 FROM s)

TRIM(s1 FROM s)删除字符串s中两端所有的子字符串sksl为可选项,在未指定情况下,删除空格.

6.3.9重复生成字符串的函数REPEAT(s.n)

REPEAT(s,n)返回一个由重复的字符串s组成的字符串,字符串s的数目等于n.若n<=0,则返回一个空字符串.若s或n为NULL,则返回NULL.

6.3.10 空格函数 SPACE(n)和替换函数 REPLACE(s,s1,s2)

SPACE(n)返回一个由n个空格组成的字符串.
REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1.

6.3.11比较字符串大小的函数STRCMP(s1 ,s2)

STRCMP(s1,s2)若所有的字符串均相同,则返回0;若根据当前分类次序,第一个参数小于第二个,则返回-1,其他情况返回1.

6.3.12 获取子串的函数 SUBSTRING(s,n,len)和 MID(s,n,len)

SUBSTRING(s,n,len)带有len参数的格式,从字符串s返回一个长度同len字符相同的子 字符串,起始于位置n.也可能对n使用一个负值.假若这样,则子字符串的位置起始于字符 串结尾的n字符,即倒数第n个字符,而不是字符串的开头位置.

MID(s,n,len)与 SUBSTRING(s,n,len)的作用相同.

如果对len使用的是一个小于1的值,则结果始终为空字符串.

6.3.13匹配子串开始位置的函数

LOCATE(strl,str)、POSITION(str1 IN str)和 INSTR(str,str1)3 个函数作用相同,返回子字符串strl在字符串str中的开始位置.

6.3.14 字符串逆序的函数REVERSE(s)

REVERSE(s)将字符串s反转,返回的字符串的顺序和s字符串顺序相反.

6.3.15返回指定位置的字符串的函数

ELT(N,字符串1,字符串2,字符串3,...,字符串N)若N= 1,则返回值为字符串1,若N=2,则返回值为字符串2,以此类推.若N小于1或大于参数的数目,则返回值为NULL.

6.3.16返回指定字符串位置的函数FIELD(s,s1,s2,...)

FIELD(s,sl,s2,...)返回字符串s在列表sl,s2,...中第一次出现的位置,在找不到s的情况下,返回值为9.如果s为NULL,则返回值为0,原因是NULL不能同任何值进行同等比较.

6.3.17返回子串位置的函数FIND_IN_SET(s1,s2)

FIND_IN_SET(sl,s2)返回字符串si在字符串列表s2中出现的位置,字符串列表是一个由多个逗号‘,'分开的字符串组成的列表.如果si不在s2或s2为空字符串,则返回值为0. 如果任意一个参数为NULL,则返回值为NULLo这个函数在第一个参数包含一个逗号',’ 时将无法正常运行.

虽然FIND_IN_SET()和FIELD()两个函数格式不同,但作用类似,都可以返回指定字符串 在字符串列表中的位置.

6.3.18 选取字符串的函数MAKE_SET(x,s1,s2,...)

MAKE_SET(x,sl,s2,...)返回由x的二进制数指定的相应位的字符串组成的字符串,s1对应比特1, s2对应比特01,以此类推.s1, s2...中的NULL值不会被添加到结果中.

6.4 曰期和时间函数

日期和吋间函数主要用来处理日期和时间值,一般的日期函数除了使用DATE类型的参数外,也可以使用DATETIME或者TIMESTAMP类型的参数,但会忽略这些值的时间部分.相同的,以TIME类型值为参数的函数,可以接受TIMESTAMP类型的参数,但会忽略日期部分,许多日期函数可以同时接受数和字符串类型的两种参数,本节将介绍各种日期和时间函数的功能和用法.

6.4.1获取当前日期的函数和获取当前时间的函数

CURDATE()和CURRENT_DATE()函数作用相同,将当前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回,具体格式根据函数在字符串或是数字语境中而定.两个函数作用相同,都返回了相同的系统当前日期,“CURDATE()+ 0”将当 前日期值转换为数值型.
CURTIME()和CURRENT_TIME()函数作用相同,将当前时间以*HH:MM:SS ’或HHMMSS 的格式返回,具体格式根据函数在字符串或是数字语境中而定.两个函数作用相同,都返回了相同的系统当前时间,“CURTIME()+ 0”将当 前时间值转换为数值型.

6.4.2获取当前日期和时间的函数

CURRENT_TIMESTAMP(). LOCALTIME()、NOW()和 SYSDATE()4 个函数的作用相同,均返回当前日期和时间值,格式为‘YYYY-MM-DDHH:MM:SS’或YYYYMMDDHHMMSS,具体格式根据函数在字符串或数字语境中而定.可以看到,4个函数返回的结果是相同的.

6.4.3 UNIX时间戳函数

UNIX TIMESTAMP(date)若无参数调用,则返回一个 Unix 时间戳(‘1970-01-01 00:00:00' GMT之后的秒数)作为无符号整数.其中,GMT(Greenwich mean time)'为格林尼治标准时间).若用 date 来调用 UNIX_TIMESTAMP(),它会将参数值以 41970-01-01 00:00:00* GMT 后的秒数的形式返回.date可以是一个DATE字符串、DATETIME字符串、TIMESTAMP或
一个当地吋间的YYMMDD或YYYYMMDD格式的数字.
FROM_UNIXTIME(date)函数把 UNIX 时间戳转换为普通格式的时间,与 UNIX_TIMESTAMP(date)函数互为反函数.

6.4.4 返回UTC日期的函数和返回UTC时间的函数

UTC_DATE()函数返回当前UTC(世界标准时间)日期值,其格式为‘YYYY-MM-DD’或YYYYMMDD,具体格式取决于函数是否用在字符串或数字语境中.
UTC_DATE()函数返回值为当前时区的日期值.
UTC_TIME()返冋当前UTC时间值,其格式为‘HH:MM:SS’或HHMMSS,具体格式取 决于函数是否用在字符串或数字语境中.

6.4.5 获取月份的函数 MONTH(date)和MONTHNAME(date)

MONTH(date)函数返回date对应的月份,范围值从1-12.
MONTHNAME(date)数返回日期date对应月份的英文全名.

6.4.6 获取星期的函数 DAYNAME(d),DAYOFWEEK(d)和WEEKDAY(d)

DAYNAME(d)函数返回d对应的工作日的英文名称,例如Sunday, Monday等.
DAYOFWEEK(d)函数返回d对应的一周中的索引(位置).1表示周日,2表示周一, 7表不周六).
WEEKDAY(d)返回d对应的工作日索引.0表示周一,1表示周二,...,6表示周日.
WEEKDAY()和DAYOFWEEK()函数都是返回指定日期在某一周内的位置,只 是索引编号不同.

6.4.7 获取星期数的函数WEEK(d)和WEEKOFYEAR(d)

WEEK(d)计算日期d是一年中的第几周.WEEK()的双参数形式允许指定该星期是否起始于周日或周一,以及返回值的范围是否为从0〜53或从1〜53.若Mode参数被省略,则使用 default week format系统自变量的值.

WEEK函数中Mode参数取值

WEEKOFYEAR(d)计算某天位于一年中的第几周,范围是从1~53.相当于WEEK(d,3).

可以看到,两个函数返回结果相同.

6.4.8 获取天数的函数 DAYOFYEAR(d)和 DAYOFMONTH(d)

DAYOFYEAR(d)函数返回d是一年中的第几天,范围是从1~366.
DAYOFMONTH(d)函数返回d是一个月中的第几天,范围是从1~31,

6.4.9获取年份、季度、小时、分钟和秒钟的函数

YEAR(date)返冋date对应的年份,范围是1970-2069.
QUARTER(date)返回date对应的一年中的季度值,范围是从1~4.
MINUTE(time)返回time对应的分钟数,范围是从0~59.
SECOND(time)返回time对应的秒数,范围是从0〜59.

6.4.10 获取日期的指定值的函数EXTRACT(type FROM date)

EXTRACT(type FROM date)函数所使用的时间间隔类型说明符同DATE_ADD()或DATE_SUB()的相同,但它从日期中提取一部分,而不是执行日期运算.

6.4.11时间和秒钟转换的函数

TIME_TO_SEC(time)返回已转化为秒的time参数.转换公式为:小时3600+分钟60+秒.
SEC_TO_TIME(seconds)返回被转化为小时、分钟和秒数的seconds参数值,其格式为 ‘HH:MM:SS’或HHMMSS,具体格式根据该函数是否用在字符串或数字语境中而定.

可以看到,SEC_TO_TIME函数返回值加上0值之后变成了小数值;TIME_TO_SEC正好和SEC_TO_TIME互为反函数.

6.4.12计算日期和时间的函数

计算曰期和时间的函数有:DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、 ADDTIME(), SUBTIME()和DATE_DIFF().
DATE_ADD(date,INTERVAL expr type)和 DATE_SUB(date,INTERVAL expr type),其中,date是一个DATETIME或DATE值,用来指定起始时间. expr是一个表达式,用来指定从起始日期添加或减去的时间间隔值.expr是一个字符串;对于负值的时间间隔,它可以以一个负号开头.type为关键词,它指示了表达式被解释的方式.

MySQL中计算日期和时间的格式

若date参数是一个DATE值,计算只会包括YEAR、MONTH和DAY部分(即没有时间 部分),其结果是一个DATE值.否则,结果将是一个DATETIME值.
DATE_ADD(date,INTERVAL expr type)和 ADDDATE(date,INTERVAL expr type)两个函数 作用相同,执行日期的加运算.
DATE_SUB(date,INTERVAL expr type)或者 SUBDATE(date,INTERVAL expr type)两个函 数作用相同,执行日期的减运算.
DATE_ADD和DATE_SUB在指定修改的时间段时,也可以指定负值,负值代表相减, 即返回以前的日期和时间.

ADDTIME(date,expr)函数将expr值添加到date,并返回修改后的值,date是一个日期或 者日期时间表达式,而expr是一个时间表达式.
SUBTIME(date,expr)函数中date减去expr值,并返回修改后的值,date是一个日期或者 日期时间表达式,而expr是一个时间表达式.
DATEDIFF(date1 ,date2)返回起始时间date1和结束时间date2之间的天数.date1和date2 为日期或date-and-time表达式.计算中只用到这些值的日期部分.

6.4.13将日期和时间格式化的函数

DATE_FORMAT(date, format)根据format指定的格式显示date值.

DATE_FORMAT时间日期格式

GET_FORMAT(val_type, fdrmat_type)返回日期时间字符串的显示格式,val type表示日期数据类型,包括DATE、DATETIME和TIME; format_type表示格式化显示类型,包括EUR、 INTERV AL. ISO、JIS、USA.

GET_FORMAT返回的格式字符串
值类型 格式化类型 显示格式字符串

6.5条件判断函数

条件判断函数也称为控制流程函数,根据满足的条件的不同,执行相应的流程.MySQL中进行条件判断的函数有IF、IFNULL和CASE.本节将分别介绍各个函数的用法.

6.5.1 IF(expr,v1,v2)函数

IF(expr, v1, v2),如果表达式 expr 是 TRUE(expr <> 0 and expr <> NULL),则 IF()的返回值为v1;否则返回值为v2.IF()的返回值为数字值或字符串值,具体情况视其所在语境而定.
如果v1或v2中只有一个明确是NULL,则1F0函数的结果类型为非NULL表达式的结果类型.

6.5.2 IFNULL(v1,v2)函数

IFNULL(v1,v2)假如v1不为NULL,则IFNULL()的返回值为v1;否则其返回值为v2.
IFNULL()的返回值是数字或是字符串,具体情况取决于其所在的语境.

6.5.3 CASE 函数

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE m] END
该函数表示,如果expr值等于某个vn,则返回对应位置THEN后面的结果.如果与所有值都不相等,则返回ELSE后面的rn.

一个CASE表达式的默认返回值类型是任何返回值的相容集合类型,但具体情况视其所在语境而定.如果用在字符串语境中,则返回结果为字符串.如果用在数字语境中,则返回结果为十进制值、实数值或整数值.

6.6 系统信息函数

本节将介绍常用的系统信息函数,MySQL中的系统信息有,数据库的版本号、当前用户名和连接数、系统字符集、最后一个自动生成的ID值等.本章将介绍各个函数的使用方法.

6.6.1获取MySQL版本号、连接数和数据库名的函数

SHOW VERSION()
SHOW CONNECTION_ID()
SHOW PROCESSLIST
SHOW DATABASE
SHOW SCHEMA
VERSION()返回指示MySQL服务器版本的字符串.这个字符串使用utf8字符集.
CONNECTION_ID()返回MySQL服务器当前连接的次数,每个连接都有各自唯一的ID.

processlist命令的输出结果显示了有哪些线程在运行,不仅可以查看当前所有的连接数, 还可以查看当前的连接状态,帮助识别出有问题的查询语句等.
如果是 root 账号,能看到所有用户的当前连接.如果是其他普通账号,则只能看到自己占用的连接.show processlist;只列出前100条,如果想全部列出可使用show full processlist命令.
使用SHOW PROCESSLIST命令输出当前用户的连接信息,各个列的含义和用途:
(1)Id列,用户登录MySQL时,系统分配的“connection id”.
(2)User列,显示当前用户.如果不是root,这个命令就只显示用户权限范围内的SQL 语句.
(3)Host列,显示这个语句是从哪个IP的哪个端口上发出的,可以用来追踪出现问题 语句的用户.
(4)db列,显示这个进程目前连接的是哪个数据库.
(5)Command列,显示当前连接执行的命令,一般取值为休眠(Sleep),查询(Query), 连接(Connect).
(6)Time列,显示这个状态持续的时间,单位是秒.
(7)State列,显示使用当前连接的SQL语句的状态,很重要的列,后续会有所有状态 的描述,State只是语句执行中的某一个状态.一个SQL语句,以查询为例,可能需要经过 Copying to tmp table,Sorting result,Sending data 等状态才可以完成.
(8)Info列,显示这个SQL语句,是判断问题语句的一个重要依据.

DATABASE()和SCHEMA()函数返回使用utf8字符集的默认(当前)数据库名.两个函数的作用相同.

6.6.2获取用户名的函数

select user(), current_user(), system_user();
USER(), CURRENTJJSER、SYSTEM USER()和 SESSION_USER()这儿个函数返回当前被MySQL服务器验证的用户名和主机名组合.这个值符合确定当前登录用户存取权限的MySQL账户.一般情况下,这几个函数的返回值是相同的.

6.6.3获取字符串的字符集和排序方式的函数

select charset('abc');
select charset(version());
select charset(convert('abc' using latin1));
CHARSET(str)返回字符串str自变量的字符集.
CHARSET('abc')返回系统默认的字符集 utfB; CHARSET(CONVERT('abc' USING latinl))返回的字符集为latinl ;前面介绍过,VERSION0返回的字符串使用utf8字符集,因此 CHARSET(VERSION())返回结果为 utfB.

select collation('abc');
select collation(convert('abc' using utf8));
COLLATION(str)返回字符串str的字符排列方式.

可以看到,使用不同字符集时字符串的排列方式不同.

6.6.4获取最后一个自动生成的ID值的函数

LAST_INSERT_ID()自动返回最后一个INSERT或UPDATE为AUTO_INCREMENT列设置的第一个发生的值.

一次插入一条记录时,返回值为最后一条插入记录的ID值.
一次插入多条记录时,LAST_INSERT_ID()只返回插入的第一行数据时产生的ID值.

LAST_INSERT)ID是与table无关的,如果向表a插入数据后,再向表b插入数据, LAST_INSERT_ID返回表b中的ID值.

6.7 加/解密函数

加密函数主要用来对数据进行加密和界面处理,以保证某些重要数据不被别人获取.这些函数在保证数据库安全时非常有用.本节将介绍各种加密和解密函数的作用和使用方法.

6.7.1 加密函数 PASSWORD(str)

PASSWORD(str)从原明文密码str计算并返回加密后的密码字符串,当参数为NULL时, 返回NULL.
MySQL将PASSWORD函数加密后的密码保存到用户权限表中.

PASSWORD()函数在MySQL服务器的鉴定系统中使用;不应将它用在个人的应用程序 中.PASSWORD0加密是单向的(不可逆).PASSWORD()执行密码加密与UNIX中密码被加密的方式不同.

6.7.2 加密函数 MD5(str)

MD5(str)为字符串算出一个MD5 128比特校验和.该值以32位十六进制数字的二进制字符串形式返回,若参数为NULL,则会返回NULL.

6.7.3 加密函数 ENCODE(str,pswd_str)

ENCODE(str,pswd_str)使用pswd_str作为密码,加密str.使用DECODE()解密结果,是一 个和str长度相同的二进制字符串.

6.7.4 解密函数 DECODE(crypt_str,pswd_str)

DECODE(crypt_str,pswd_str)使用 pswd str 作为密码,解密加密字符串 crypt str, crypt_str是由ENCODE()返回的字符串.
可以看到,使用相同解密字符串进行解密之后的结果,正好为ENCODE函数中被加密的字符串.DECODE函数和ENCODE函数互为反函数.

6.8其他函数

本节将要介绍的函数不能笼统地分为哪一类,但是这些函数也非常有用,例如重复指定操作函数、改变字符集函数、ip地址与数字转换函数等.本节将介绍这些函数的作用和使用方法.

6.8.1 格式化函数 FORMAT(x,n)

FORMAT(x,n)将数字x格式化,并以四舍五入的方式保留小数点后n位,结果以字符串的形式返回.若n为0,则返回结果函数不含小数部分.

6.8.2不同进制的数字进行转换的函数

CONV(N, from_base, to_base)S数进行不同进制数间的转换.返回值为数值N的字符串表示,由from_base进制转化为to_base进制.如有任意一个参数为NULL,则返回值为NULL. 自变量N被理解为一个整数,但是可以被指定为一个整数或字符串.最小基数为2,而最大基数则为36.

进制说明:

• 二进制,采用0和1两个数字来表示的数.它以2为基数,逢二进一.
• 八进制,采用0,1, 2, 3, 4, 5, 6, 7八个数字,逢八进一,以数字0开头.
• 十进制,采用0-9,共十个数字表示,逢十进一.
• 十六进制,它由0-9, A-F组成,与十进制的对应关系是;G-9对应0-9 A-F对应10-15.以数字Ox开头.

6.8.3 IP地址与数字相互转换的函数

INET_ATON(expr)给出一个作为字符串的网络地址的点地址表示,返回一个代表该地址数值的整数.地址可以是4或8bit地址.
INET_NTOA(expr)给定一个数字网络地址(4或8bit),返回作为字符串的该地址的点地址表示.
可以看到,INET_NTOA和INET_ATON互为反函数.

6.8.4加锁函数和解锁函数

GET_LOCK(str,timeout)设法使用字符串str给定的名字得到一个锁,持续时间timeout秒.
若成功得到锁,则返回1;若操作超时,则返回0;若发生错误,则返回NULL.假如有一个 用GET_LOCK()得到的锁,当执行RELEASE_LOCK()或连接断开(正常或非正常)时,这个锁就会解除.

RELEASE_LOCK(str)解开被GET_LOCK()获取的,用字符串str所命名的锁.若锁被解开,则返回1;若该线程尚未创建锁,则返回0(此时锁没有被解开);若命名的锁不存在,则返回NULL.若该锁从未被GET_LOCK()的调用获取,或锁已经被提前解开,则该锁不存在.

IS_FREE_LOCK(str)检查名为str的锁是否可以使用(换言之,没有被封锁).若锁可以使用,则返回1(没有人在用这个锁);若这个锁正在被使用,则返冋0:出现错误,则返冋 NULL(诸如不正确的参数).

lS_USED_LOCK(str)检查名为str的锁是否正在被使用(换言之,被封锁).若被封锁,则返回使用该锁的客户端的连接标识符(connections):否则,返回NULL.

6.8.5重复执行指定操作的函数

BENCHMARK(count,expr)函数重复count次执行表达式expr.它可以用于计算MySQL处理表达式的速度.结果值通常为0(0只是表示处理过程很快,并不是没有花费时间).另一个作用是它可以在MySQL客户端内部报告语句执行的时间.

BENCHMARK报告的时间是客户端经过的时间,而不是在服务器端的CPU时间,每次执行后报告的时间并不一定是相同的.

6.8.6改变字符集的函数

CONVERT(... USING ...)带有USING的CONVERT()函数被用来在不同的字符集之间转化数据.

6.8.7改变数据类型的函数

CAST(x,AS type)和CONVERT(x, type)函数将一个类型的值转换为另一个类型的值,可转换的 type 有:BINARY. CHAR(n)、DATE, TIME、DATETIME> DECIMAL> SIGNED, UNSIGNED.

6.9 综合案例——MySQL函数的使用

6.10 6.10专家解惑

疑问1:如何从日期时间值中获取年、月、曰等部分曰期或时间值?
MySQL中,日期时间值以字符串形式存储在数据表中,因此可以使用字符串函数分别截 取日期时间值的不同部分,例如某个名称为dt的字段有值“2010-10-01 12:00:30”,如果只需 要获得年值,可以输入LEFT(dt, 4),这样就获得了字符串左边开始长度为4的子字符串,即 YEAR部分的值;如果要获取月份值,可以输入MID(dt,6,2),字符串第6个字符开始,长度 为2的子字符串正好为dt中的月份值.同理,读者可以根据其他日期和时间的位置,计算并 获取相应的值.

疑问2:如何改变默认的字符集?
CONVERT0函数改变指定字符串的默认字符集,在开始的章节中,向读者介绍使用GUI 图形化安装配置工具进行MySQL的安装和配置,其中的一个步骤是可以选择MySQL的默认 字符集 .但是, 如果只改 变字符集 ,没有必 要把配置 过程重新 执行一遍 ,在这里 ,一个简单的方式是修改配置文件.在Windows中,MySQL配置文件名称为my.ini,该文件在MySQL的 安装目录下面.修改配置文件中的default-character-set和character-set-server参数值,将其改 为想要的字符集名称,如gbk、gb2312、latinl等,修改完之后重新启动MySQL服务,即可 生效.读者可以在修改字符集时使用SHOW V ARIABLES LIKE ’character_set_%’;命令查看当 前字符集,以进行对比.

第7章 查询数据

7.1 基本查询语句

MySQL从数据表中查询数据的基本语句为SELECT语句.SELECT语句的基本格式是:

select
    {* | <字段列表>}
    [
        from <表1>, <表2>, ...
        [ where <表达式>
        [GROUP BY <group by definition>]
        [HAVING <expression> [ {〈operator〉<expression>}...]] 
        [ORDER BY <order by definition>]
        [LIMIT [<offset>, ] <row count>]
    ]

SELECT [字段1,字段2,...,字段n] FROM [表或视图] WHERE [査询条件I;

其中,各条子句的含义如下:

• {*|<字段列表>}包含星号通配符选择字段列表,表示查询的字段,其中字段列至少包含一个字段名称,如果要查询多个字段,多个字段之间用逗号隔开,最后一个字段后 不要加逗号.
• FROM <表1>,<表2>...,表1和表2表示查询数据的来源,可以是单个或者多个.
• WHERE子句是可选项,如果选择该项,将限定查询行必须满足的查询条件.
• GROUP BY <字段>,该子句告诉MySQL如何显示查询出来的数据,并按照指定的字段分组.
• [ORDER BY <字段>],该子句告诉MySQL按什么样的顺序显示查询出来的数据,可以进行的排序有:升序(ASC)、降序(DESC).
• [LIMIT [<offset>,] <row count>],该子句告诉MySQL每次显示查询出来的数据条数.

7.2 单表查询

单表查询是指从一张表数据中查询所需的数据.本节将介绍单表查询中的各种基本的查询方式,主要有:查询所有字段 、查询指定字段、 查询指定记录、查询空值、多条件的查询、对查询结果进行排序等.

7.2.1查询所有字段

1.在SELECT语句中使用星号()通配符查询所有字段
SELECT 查询记录最简单的形式是从一个表中检索所有记录,实现的方法是使用星号()通配符指定查找所有列的名称.语法格式如下:
SELECT * FROM 表名;
可以看到,使用星号(*)通配符时,将返回所有列,列按照定义表时候的顺序显示.

2.在SELECT语句中指定所有字段

一般情况下,除非需要使用表中所有的字段数据,最好不要使用通配符.'*'使用通配符虽然可以节省输入查询语句的时间,但是获取不需要的列数据通常会降低查询和所使用 的应用程序的效率.通配符的优势是,当不知道所需要的列的名称时,可以通过它获取它们.

7.2.2查询指定字段

1.查询单个字段
查询表中的某一个字段,语法格式为:·SELECT 列名 FROM 表名;·

2.查询多个字段
使用SELECT声明,可以获取多个字段下的数据,只需要在关键字SELECT后面指定要 查找的字段的名称,不同字段名称之间用逗号(,)分隔开,最后一个字段后面不需要加逗号, 语法格式如下:
SELECT 字段名1, 字段名字段名n FROM 表名;

7.2.3查询指定记录

数据库中包含大量的数据,根据特殊要求,可能只需要查询表中的指定数据,即对数据进行过滤.在SELECT语句中,通过WHERE子句可以对数据进行过滤,语法格式为:
SELECT 字段名1,字段名字段名n FROM表名 WHERE 査询条件

WHERE条件判断符

7.2.4 带IN关键字的查询

IN操作符用来查询满足指定范围内的条件的记录,使用IN操作符,将所有检索条件用括号括起来,检索条件之间用逗号分隔开,只要满足条件范围内的一个值即为匹配项.

7.2.5 带BETWEEN AND的范围查询

BETWEEN AND用来查询某个范围内的值,该操作符需要两个参数,即范围的开始值和结束值,如果字段值满足指定的范围查询条件,则这些记录被返回.
BETWEEN AND操作符前可以加关键字NOT,表示指定范围之外的值,如果字段值不满足指定的范围内的值,则这些记录被返回.

7.2.6 带LIKE的字符匹配查询

通配符是一种在SQL的WHERE条件子句中拥有特殊意思的字符,SQL语句中支持多种通配符,可以和LIKE一起使用的通配符有‘%'和'_'.
1.百分号通配符'%',匹配任意长度的字符,甚至包括零字符
2.下划线通配符,一次只能匹配任意一个字符

7.2.7查询空值

数据表创建的时候,设计者可以指定某列中是否可以包含空值(NULL).空值不同于0,也不同于空字符串.空值-般表示数据未知、不适用或将在以后添加数据.在SELECT语句中使用用IS NULL子句,可以查询某字段内容为空的记录.
与IS NULL相反的是NOT IS NULL,该关键字查找字段不为空的记录.

7.2.8 带AND的多条件查询

使用SELECT查询时,可以增加查询的限制条件,这样可以使查询的结果更加精确. MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回. 可以使用AND连接两个甚至多个查询条件,多个条件表达式之间用AND分开.

7.2.9 带OR的多条件查询

与AND相反,在WHERE声明中使用OR操作符,表示只需要满足其中一个条件的记录即可返回.OR也可以连接两个甚至多个查询条件,多个条件表达式之间用OR分开.

OR操作符和IN操作符使用后的结果是-样的,它们可以实现相同的功能.但是使用IN操作符使得检索语句更加简洁明了,并且IN执行的速度要快于OR.更重要的是,使用IN操作符,可以执行更加复杂的嵌套查询

OR可以和AND-起使用,但是在使用时要注意两者的优先级,由于AND的优先级高于 OR,因此先对AND两边的操作数进行操作,再与OR中的操作数结合.

7.2.10查询结果不重复

在SELECT语句中,可以使 用DISTINCT关键字指示MySQL消除重复的记录值.语法格式为:
SELECT DISTINCT 字段名 FROM 表名;

7.2.11对查询结果排序

从前面的查询结果,读者会发现有些字段的值是没有任何顺序的,MySQL可以通过在SELECT语句中使用ORDER BY子句,对查询的结果进行排序.

1.单列排序
2.多列排序
3.指定排序方向
默认情况下,查询数据按字母升序进行排序(从A-Z),但数据的排序并不仅限于此, 还可以使用ORDER BY对查询结果进行降序排序(从Z〜A),这可以通过关键字DESC实现.
与 DESC 相反的是ASC(升序排序),将字段列中的数据,按字母表顺序升序排序.实际上,在 排序的时候ASC是作为默认的排序方式,所以加不加都可以.

DESC关键字只对其前面的列进行降序排列,在这里只对Cprice排序,而并没有对f_name 进行排序,因此,fjrice按降序排序,而fLname列仍按升序排序.如果要对多列都进行 降序排序,必须要在每一列的列名后面加DESC关键字.

7.2.12分组查询

分组查询是对数据按照某个或多个字段进行分组,MySQL中使用GROUP BY关键字对 数据进行分组,基本语法形式为:
[GROUP BY 字段][HAVING <条件表达式〉]
字段值为进行分组时所依据的列名称;“HAVING <条件表达式>”指定满足表达式限定条件的结果将被显示.

1.创建分组
GROUP BY关键字通常和集合函数一起使用,例如:MAX()、MIN()、COUNT()、SUM()、 AVG().

MySQL中可以在GROUP BY字节中使用GROUP_CONCAT()函数,将每个分组中各个字段的值显示出来.

以看到,GROUP_CONCAT()函数将每个分组中的名称显示出来了,其名称的个数与COUNT()数计算出来的相同.

2.使用HAVING过滤分组
GROUP BY可以和HAVING 一起限定显示记录所需满足的条件,只有满足条件的分组才会被显示.

HAVING关键字与WHERE关键字都是用来过滤数据什么区别呢?其中重要的一点是,HAVING在数据分组之后进行过滤来选择分组,而WHERE在分组之前用来选择记录.另外WHERE排除的记录不再包括在分组中.

3.在 GROUP BY 子句中使用 WITH ROLLUP
使用WITH ROLLUP关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量.

4.多字段分组
使用GROUP BY可以对多个字段进行分组,GROUP BY关键字后面跟需要分组的字段,MySQL根据多字段的值来进行层次分组,分组层次从左到右,即先按第1个字段分组,然后在第1个字段值相同的记录中,再根据第2个字段的值进行分组...依次类推.

5. GROUP BY和ORDER BY —起使用
   某些情况下需要对分组进行排序,在前面的介绍中,ORDER BY用來对查询的记录排序,如果和GROUP BY一起使用可以完成对分组的排序.

当使用ROLLUP时,不能同时使用ORDER BY子句进行结果排序.即ROLLUP和ORDER BY是互相排斥的.

7.2.13使用LIMIT限制查询结果的数量

SELECT返回所有匹配的行,有可能是表中所有的行,如仅仅需要返回第一行或者前儿行,使用LIMIT关键字,基本语法格式如下:
LIMIT [位置偏移量,] 行数
第一个“位置偏移量”参数指示MySQL从哪一行开始显示,是一个可选参数,如果不指定“位置偏移量”,将会从表中的第一条记录开始(第一条记录的位置偏移量是0,第二条记录的位置偏移量是1...依次类推):第二个参数“行数”指示返回的记录条数.

如果指定返回记录的开始位置,则返回结果为从“位置偏移量”参数开始的指定行数,"行 数”参数指定返回的记录条数.

MySQL 5.7中可以使用“LIMIT 4 OFFSET 3”,意思是获取从第5条记录开始后面的3条记录,和“LIMIT4,3;”返回的结果相同.

7.3使用聚合函数查询

有时候并不需要返回实际表中的数据,而只是对数据进行总结.MySQL提供一些查询功能,可以对获取的数据进行分析和报告.这些函数的功能有:计算数据表中记录行数的总数、计算某个字段列下数据的总和,以及计算表中某个字段下的最大值、最小值或者平均值.

MySQL聚合函数

7.3.1 COUNT()函数

COUNT()函数统计数据表中包含的记录行的总数,或者根据查询结果返回列中包含的数据行数.其使用方法有两种:

• COUNT(*)计算表中总的行数,不管某列有数值或者为空值.
• COUNT(字段名)计算指定列下总的行数,计算时将忽略空值的行.
  两种方式在计算总数的时候对待NULL值的方式不同.即指定列的值为空的行被COUNT()函数忽略,但是如果不指定列,而在COUNT()函数中使用星号,则所有记录都不忽略.

7.3.2 SUM()函数

SUMO是一个求总和的函数,返冋指定列值的总和.
SUM0可以与GROUP BY 一起使用,来计算每个分组的总和.
SUM0函数在计算时,忽略列值为NULL的行.

7.3.3 AVG()函数

AVG()函数通过计算返回的行数和每一行数据的和,求得指定列数据的平均值.
AVG()可以与GROUP BY 一起使用,来计算每个分组的平均值.
AVG()函数使用时,其参数为要计算的列名称,如果要得到多个列的多个平均值,则需要在每一列上使用AVG()函数.

7.3.4 MAX()函数

MAX()返回指定列中的最大值.
MAX()函数不仅适用于查找数值类型,也可应用于字符类型.
MAX()函数可以对字母进行大小判断,并返冋最大的字符或者字符串值.
MAX()函数除了用来找出最大的列值或日期值之外,还可以返回任意列中的最大值,包括返回字符类型的最大值.在对字符类型数据进行比较时,按照字符的ASCII码值大小进 行比较,从a~z,a的ASCII码最小,z的最大.在比较时,先比较第一个字母,如果相等,继续比较下一个字符,一直到两个字符不相等或者字符结束为止.

7.3.5 MIN()函数

MIN()返回查询列中的最小值.
MIN()也可以和GROUP BY关键字一起使用,求出每个分组中的最小值.
MIN()函数与MAX()函数类似,不仅适用于查找数值类型,也可应用于字符类型.

7.4 连接查询

连接是关系数据库模型的主要特点.连接查询是关系数据库中最主要的查询,主要包括内连接、外连接等.通过连接运算符可以实现多个表查询.在关系数据库管理系统中,表建立时各数据之间的关系不必确定,常把一个实体的所有信息存放在一个表中.当查询数据时,通过连接操作查询出存放在多个表中的不同实体的信息.当两个或多个表中存在相同意义的字段时,便可以通过这些字段对不同的表进行连接査询.本节将介绍多表之间的内连接查询、外连接查询以及复合条件连接查询.

7.4.1内连接查询

内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作,并列出这些表中与连接条件相匹配的数据行,组合成新记录,也就是说,在内连接查询中,只有满足条件的记录才能出现在结果关系中.

使用WHERE子句定义连接条件比较简单明了,而INNER JOIN语法是ANSI SQL的标准规范,使用INNER JOIN连接语法能够确保不会忘记连接条件,而且,WHERE子句在某些时候会影响查询的性能.

如果在一个连接查询中,涉及的两个表都是同一个表,这种查询称为自连接查询.自连接是一种特殊的内连接,它是指相互连接的表在物理上为同一张表,但可以在逻辑上分为两张表.

7.4.2外连接查询

外连接查询将查询多个表中相关联的行,内连接时,返冋查询结果集合中的仅是符合查询条件和连接条件的行.但有时候需要包含没有关联的行中数据,即返回查询结果集合中的不仅包含符合连接条件的行,而且还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个连接表(全外连接)中的所有数据行.外连接分为左外连接或左连接和右外连接或右连接:

• LEFT JOIN(左连接):返回包括左表中的所有记录和右表中连接字段相等的记录.
• RIGHT JOIN(右连接):返回包括右表中的所有记录和左表中连接字段相等的记录.

1.LEFT JOIN(左连接)
左连接的结果包括LEFT OUTER子句中指定的左表的所有行,而不仅仅是连接列所匹配的行.如果左表的某行在右表中没有匹配行,则在相关联的结果行中,右表的所有选择列表列均为空值.

2. RIGHT JOIN(右连接)
   右连接是左连接的反向连接,将返回右表的所有行.如果右表的某行在左表中没有匹配行,左表将返回空值.

7.4.3复合条件连接查询

复合条件连接查询是在连接查询的过程中,通过添加过滤条件,限制查询的结果,使查询的结果更加准确.

7.5 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从MySQL 4.1开始引入.在SELECT子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表.子查询中常用的操作符有ANY(SOME)、ALL、IN、EXISTS.子查询可以添加到SELECT, UPDATE和DELETE语句中,而且可以进行多层嵌套.子查询 中也可以使用比较运算符,如“<”、“<=”、“>”、“>=”和“!=”等.本节将介绍如何 在SELECT语句中嵌套子查询.

7.5.1 带ANY、SOME关键字的子查询

ANY和SOME关键字是同义词,表示满足其中任一条件,它们允许创建一个表达式对子查询的返回值列表进行比较,只要满足内层子查询中的任何一个比较条件,就返回一个结果作为外层查询的条件.

ANY关键字接在一个比较操作符的后面,表示若与子查询返回的任何值比较为TRUE, 则返回TRUE.

7.5.2 带ALL关键字的子查询

ALL关键字与ANY和SOME不同,使用ALL时需要同时满足所有内层查询的条件.
ALL关键字接在一个比较操作符的后面,表示与子査询返回的所有值比较为TRUE,则返回TRUE.

7.5.3 带EXISTS关键字的子查询

EXISTS关键字后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么EXISTS的结果为true,此吋外层查询语句将进行查询;如果子查询没有返冋任何行,那么EXISTS返回的结果是false,此时外层语句将不进行查询.
EXISTS关键字可以和条件表达式一起使用.
NOT EXISTS与EXISTS使用方法相同,返回的结果相反.子查询如果至少返回一行,那么NOT EXISTS的结果为false,此时外层查询语句将不进行查询;如果子查询没有返回任何行,那么NOT EXISTS返回的结果是true,此时外层语句将进行查询.

EXISTS和NOT EXISTS的结果只取决于是否会返回行,而不取决于这些行的内容,所以这个子查询输入列表通常是无关紧要的.

7.5.4 带IN关键字的子查询

IN关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句进行比较操作.

SELECT语句中可以使用NOT IN关键字,其作用与IN正好相反.

7.5.5带比较运算符的子查询

在前面介绍的带ANY、ALL关键字的子查询时使用了 “>”比较运算符,子査询时还可 以使用其他的比较运算符,如、“<=”、“=”、“>=”和“!=”等.

7.6合并查询结果

利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集.合并时,两个表对应的列数和数据类型必须相同.各个SELECT语句之间使用UNION或 UNION ALL关键字分隔.UNION不使用关键字ALL,执行的时候删除重复的记录,所有返回的行都是唯一的;使用关键字ALL的作用是不删除重复行也不对结果进行自动排序.
基本语法格式如下:
SELECT column,.. ..FROM tablel UNION [ALL] SELECT column,.‘ ..FROM table2
UNION从查询结果集中自动去除了重复的行,如果要返回所有匹配行,而不进行删 除,可以使用UNION ALL.

UNION和UNION ALL的区别:使用UNION ALL的功能是不删除重复行,加上ALL关键字语句执行时所需要的资源少,所以尽可能地使用它,因此知道有重复行但是想保留这些行,确定查询结果中不会有重复数据或者不需要去掉重复数据的时候,应当使用UNION ALL以提高查询效率.

7.7 为表和字段取别名

7.7.1为表取别名

当表名字很长或者执行一些特殊查询时,为了方便操作或者需要多次使用相同的表时,可以为表指定别名,用这个别名替代表原来的名称.为表取别名的基本语法格式为:
表名 [AS] 表别名
“表名”为数据库中存储的数据表的名称,“表别名”为查询时指定的表的新名称,AS 关键字为可选参数.

在为表取别名时,要保证不能与数据库中的其他表的名称冲突.

7.7.2为字段取别名

在使用SELECT语句显示查询结果时,MySQL会显示毎个SELECT后面指定的输出列,在有些情况下,显示的列的名称会很长或者名称不 够直观,MySQL可以指定列别名,替换字段或表达式.为字段取别名的基本语法格式为:
列名 [AS] 列别名
“列名”为表中字段定义的名称,‘‘列别名”为字段新的名称,AS关键字为可选参数.

表别名只在执行查询的时候使用,并不在返回结果中显示,而列别名定义之后,将返回给客户端显示,显示的结果字段为字段列的别名.

7.8 使用正则表达式查询

正则表达式通常被用來检索或替换那些符合某个模式的文本内容,根据指定的匹配模式匹配文本中符合要求的特殊字符串.
正则表达式强大而且灵活,可以应用于非常复杂的查询.
MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式.

正则表达式常用字符匹配列表
选项|说明|例子|匹配值示例
^{|匹配文本的开始字符|’}b’匹配以字母b开头的|book, big, banana, bike 字符串
'匹配以st结尾的字符串|test, resist, persist
.|匹配任何单个字符|'b.t’匹配任何b和t之间有一个字符|bit, bat, but,bite
|匹配零个或多个在它前面的字符|'fn’匹配字符n前面有任意个字符f|fn, fan,faan, fabcn
+|匹配前面的字符1次或多次|'ba+ ’匹配以b开头后面紧跟至少有一个a|ba, bay, bare, battle
<字符串>|匹配包含指定的字符串的文本|'fa'|fan,afa,faad
[字符集合]|匹配字符集合中的任何一个字符|'[xz]'匹配X或者z|dizzy, zebra, x-ray, extra
[^{]|匹配不在括号中的任何字符|'[}abc]'匹配任何不包含 a、b或c的字符串|desk, fox, fBke
字符串{n,}|匹配前面的字符串至少n次|b{2}匹配2个或更多的b|bbb,bbbb,bbbbbbb
字符串{n,m}|匹配前面的字符串至少n次,至多m 次.如果n为0,此参数为可选参数|b{2,4}匹配最少2个,最多4个b|bb,bbb,bbbb

7.8.1查询以特定字符或字符串开头的记录

字符匹配以特定字符或者字符串开头的文本.

7.8.2查询以特定字符或字符串结尾的记录

字符匹配以特定字符或者字符串结尾的文本.

7.8.3用符号来替代字符串中的任意一个字符

字符匹配任意一个字符.

7.8.4使用"*"和"+"来匹配多个字符

星号匹配前面的字符任意多次,包括0次.加号‘+’匹配前面的字符至少一次.

7.8.5匹配指定字符串

正则表达式可以匹配指定字符串,只要这个字符串在查询文本中即可,如要匹配多个字符串,多个字符串之间使用分隔符隔开.

7.8.6匹配指定字符中的任意一个

方括号“[]”指定一个字符集合,只匹配其中任何一个字符,即为所查找的文本.
方括号“[]”还可以指定数值集合.
匹配集合“[456]”也可以写成“[4-6]”即指定集合区间.例如“[a-z]”表示集合区间为 从a~z的字母,“[0-9]”表示集合区间为所有数字.

7.8.7匹配指定字符以外的字符

“[^字符集合]”匹配不在指定集合中的任何字符.

7.8.8使用{n,}或者{n,m}来指定字符串连续出现的次数

“字符串{n,}”表示至少匹配n次前面的字符;“字符串{n,m}w表示匹配前面的字符串 不少于n次,不多于m次.

7.9 综合案例一据表查询操作

7.10 专家解惑

疑问1 :DISTINCT可以应用于所有的列吗?
查询结果中,如果需要对列进行降序排序,可以使用DESC,这个关键字只能对;其前面的列进行降序排列.例如,要对多列都进行降序排序,必须要在每一列的列名后面加DESC关键字.而DISTINCT不同,DISTINCT不能部分使用.换句话说,DISTINCT关键字应用于所有列而不仅是它后面的第一个指定列.例如,查询3个字段s_id,f^name. f_price,如果不同 记录的这3个字段的组合值都不同,则所有记录都会被查询出来.

疑问2 :ORDER BY可以和LIMIT混合使用吗?
在使用ORDER BY子句时,应保证其位于FROM子句之后,如果使用LIMIT,则必须位于ORDER BY之后,如果子句顺序不正确,MySQL将产生错误消息.

疑问3 :什么时候使用引号?
在査询的时候,会看到在WHERE子句中使用条件,有的值加上了单引号,而有的值未加.单引号用来限定字符串,如果将值与字符串类型列进行比较,则需要限定引号;而用来与数值进行比较则不需要用引号.

疑问4 :在WHERE子句中必须使用圆括号吗?
任何时候使用具有AND和OR操作符的WHERE子句,都应该使用圆括号明确操作顺序.如果条件较多,即使能确定计算次序,默认的计算次序也可能会使SQL语句不易理解,因此 使用括号明确操作符的次序,是一个良好的习惯.

疑问5 :为什么使用通配符格式正确,却没有查找出符合条件的记录?
MySQL中存储字符串数据时,可能会不小心把两端带有空格的字符串保存到记录中,而在查看表中记录时,MySQL不能明确地显示空格,数据库操作者不能直观地确定字符串两端是否有空格.例如,使用LIKE‘%e’匹配以字母e结尾的水果的名称,如果字母e后面多了一个空格,则LIKE语句不能将该记录查找出来.解决的方法是使用TRIM函数,将字符串两端的空格删除之后再进行匹配.

第8章 插入、更新与删除数据

8.1 插入数据

在使用数据库之前,数据库中必须要有数据,MySQL中使用INSERT语句向数据库表中 插入新的数据记录.可以插入的方式有:插入 完整的记录、插入记录的一部分、插入多条记录、 插入另一个查询的结果,下面将分别介绍这些内容.

8.1.1为表的所有字段插入数据

使用基本的INSERT语句插入数据,要求指定表名称和插入到新记录中的值.基本语法格式为:
INSERT INTO table_name(column_list)VALUES(value_list);
table name指定要插入数据的表名,column list指定要插入数据的那些列,value list指定每个列应对应插入的数据.注意,使用该语句时字段列和数据值的数量必须相同.

向表中所有字段插入值的方法有两种:一种是指定所有字段名,另一种是完全不指定字段名.
插入数据时,不需要 按照表定义的顺序插入,只要保证值的顺序与列字段的顺序相同就可以.
使用INSERT插入数据时,允许列名称列表为空,此时,值列表中需要为表的每一个字段指定值,并且值的顺序必须和数据表中字段定义时的顺序相同.

虽然使用INSERT插入数据时可以忽略插入数据的列名称,但是值如果不包含列名称,那VALUES关键字后面的值不仅要求完整而且顺序必须和表定义时列的顺序相同.如果表的结构被修改,对列进行增加、删除或者位置改变操作,这些操作将使得用这种方式插入数据时的顺序也同时改变.如果指定列名称,则不会受到表结构改变的影响.

8.1.2为表的指定字段插入数据

为表的指定字段插入数据,就是在INSERT语句中只向部分字段中插入值,而其他字段的值为表定义时的默认值.

要保证每个插入值的类型和对应列的数据类型匹配,如果类型不同,将无法插入,并且MySQL会产生错误.

8.1.3同时插入多条记录

INSERT语句可以同时向数据表中插入多条记录,插入时指定多个值列表,每个值列表之间用逗号分隔开
基本语法格式如下:
INSERT INTO table_name(column_list)
VALUES(value_list1),(value_list2),.. .,(value_listn);
value list1,value_list2,...,value_listn;表示第1,2,...,n个插入记录的字段的值列表.

一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句,但是多行INSERT语句在处理过程中,效率更高.因为MySQL 执行单条INSERT语句插入多行数据,比使用多条INSERT语句快,所以在插入多条记录时,最好选择使用单条INSERT语句的方式插入.

8.1.4 将查询结果插入到表中

INSERT语句用来给数据表插入记录时,指定插入记录的列值.
INSERT还可以将SELECT语句查询的结果插入到表中,如果想要从另外一个表中合并个人信息到person表,不需要把每一条记录的值一个一个输入,只需要使用一条INSERT语句和一条SELECT语句组成的组合语句,即可快速地从一个或多个表中向一个表中插入多个行.
基本语法格式如下:
INSERT INTO table_name1(column_list1)
SELECT(column_list2)FROM table_name2 WHERE(condition)
table name1指定待插入数据的表;column list1指定待插入表中要插入数据的哪些列; table_name2指定插入数据是从哪个表中查询出来的;column_list2指定数据来源表的查询列,该列表必须和column listl列表中的字段个数相同,数据类型相同;condition指定SELECT语句的查询条件.

8.2更新数据

表中有数据之后,接下来可以对数据进行更新操作,MySQL中使用UPDA TE语句更新表 中的记录,可以更新特定的行或者同时更新所有的行.基本语法结构如下:
UPDATE table_name
SET column_name1=value1,column_name2=value2,...“.,column_namen=valuen
WHERE(condition);
column name 1 ,column_name2, ,column_namen 为指定更新的字段的名称;value1, value2,.....valuen为相对应的指定字段的更新值;condition指定更新的记录需要满足的条件.

保证UPDATE以WHERE子句结束,通过WHERE子句指定被更新的记录所需要满足的 条件,如果忽略WHERE子句,MySQL将更新表中所有的行.

8.3删除数据

从数据表中删除数据使用DELETE语句DELETE语句允许WHERE子句指定删除条件.
DELETE语句基本语法格式如下:
DELETE FROM table_name [WHERE <condition>];
table_name指定要执行删除操作的表;“[WHERE <condition>]”为可选参数,指定删除条件,如果没有WHERE子句,DELETE语句将删除表中的所有记录.

如果想删除表中的所有记录,还可以使用TRUNCATE TABLE语句,TRUNCATE将直接 删除原来的表,并重新创建一个表,其语法结构为TRUNCATE TABLE table_name. TRUNCATE直接删除表而不是删除记录,因此执行速度比DELETE快.

8.4 综合案例——记录的插入、更新和删除

8.5 专家解惑

疑问1:插入记录时可以不指定字段名称吗?
不管使用哪种INSERT语法,都必须给出VALUES的正确数目.如果不提供字段名,则必须给每个字段提供一个值,否则将产生一条错误消息.如果要在INSERT操作中省略某些字段,这些字段需要满足一定条件:该列定义为允许空值;或者表定义时给出默认值,如果不给出值,将使用默认值.

疑问2 :更新或者删除表时必须指定WHERE子句吗?
在前面章节中可以看到,所有的UPDATE和DELETE语句全都在WHERE子句中指定了条件.如果省略WHERE子句,则UPDATE或DELETE将被应用到表中所有的行.因此,除非确实打算更新或者删除所有记录,否则要注意使用不带WHERE子句的UPDATE或DELETE语句.建议在对表进行更新和删除操作之前,使用SELECT语句确认需要删除的记录,以免造成无法挽回的结果.