数据库编程

数据库编程

数据类型

MySQL所支持的数据类型主要有数值型、日期/时间类型和字符串类型。

数值类型

整数类型

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件。

类型名称	说明	存储需要	有符号	无符号
TINYINT	很小的整数	1字节	-128~127	0~255
SMALLINT	小的整数	2字节	32768~32767	0~65535
MEDIUMINT	中等大小的整数	3字节	-8388608~8388607	0~16777215
INT(INTEGER)	普通大小的整数	4字节	-2147483648~2147483647	0~4294967295
BIGINT	大整数	8字节	-9223372036854775808~9223372036854775807	0~18446744073709551615

浮点数类型AND定点数类型

单精度浮点类型(FLOAT)

双精度浮点类型(DOUBLE)

定点数类型：DECIMAL

浮点数类型和定点数类型都可以用（M，N）来表示。其中，M称为精度，表示总共的位数；N称为标度，表示小数的位数。

类型名称	说明	存储需求	有符号	无符号
FLOAT	单精度浮点数	4字节	-3.402823466E+38~-1.175494351E-38	0和1.175494351E-38 ~ 3.402823466E+38
DOUBLE	双精度浮点数	8字节	-1.7976931348623157E+308 ~ -2.2250738585072014E-308	0和2.2250738585072014E-308 ~ 1.7976931348623157E+308
DECIMAL(M,D)，DEC	压缩的“严格”定点数	M+2字节

不论是定点数还是浮点数类型，如果用户指定的精度超出精度范围，则会四舍五入。

在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据等）使用DECIMAL的类型比较好，另外两个浮点数进行减法和比较运算时容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

日期/时间类型

MySQL中表示日期的数据类型主要有DATETIME、DATE、TIMESTAMP、TIME和YEAR

数据类型	日期格式	日期范围	存储需求
YEAR	YYYY	1901~2155	1字节
TIME	HH:MM:DD	-838:59:59~838:59:59	3字节
DATE	YYYY-MM-DD	1000-01-01~9999-12-31	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 UTC~2038-01-1903:14:07 UTC	4字节

字符串类型

文本字符串

文本字符串类型是指CHAR、VARCHAR、TEXT、ENUM和SET

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M字节，1≤M≤255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此L≤M 和1≤M≤255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此L<2^8
TEXT	小的非二进制字符串	L+2字节，在此L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此L<2 ^ 24
LONGTEXT	大的非二进制字符串	L+4字节，在此L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2字节，取决干枚举值的数目（最大值为65535）
SET	一个设置，字符串对象可以有零个或多个SET成员	1、2、3、4或8字节，取决于集合成员的数量(最多为64个成员)

二进制字符串

二进制数据类型有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

类型名称	说明	存储需求
BIT(M)	位字段类型	大约(（M+7)/8字节
BINARY(M)	固定长度二进制字符串	M字节
VARBINARY(M)	可变长度二进制字符串	M+1字节
TINYBLOB(M)	非常小的 BLOB	L+1 字节，在此 L<2^8
BLOB(M)	小BLOB	L+2字节，在此 L<2^16
MEDIUMBLOB(M)	中等大小的BLOB	L+3字节，在此 L<2^24
LONGBLOB(M)	非常大的 BLOB	L+4字节，在此 L<2^32

运算符

MySQL运算符主要有四大类，分别是算数运算符、比较运算符、逻辑运算符、位运算符。

算数运算符

算数运算符在两个表达式上执行数学运算，这两个表达式可以时任何数值数据类型。算数运算符有+（加）、-（减）、*（乘）、/（除）、和%（求模）5种运算。

比较运算符

比较运算符（又称关系运算符），用于比较两个表达式的值，其运算结果为逻辑值，可以为1（真）、0（假）及NULL（不能确定）中一种。

运算符	含义
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
<>、!=	不等于
<=>	相等或都等于空

（1）=运算符

=运算符用于比较表达式的两边是否相等，也可以对字符串进行比较。在默认情况下MySQL以不区分大小写的方式比较字符串。如果向执行区分大小写的比较，可以添加BINARY关键字，这意味着对字符串以二进制方式处理

（2）<>运算符

与=运算符相对立，用来检测表达式的两边是否不相等，如果不相等则返回真值，相等则返回假值。

当有NULL值参与比较时

SELECT NULL<>NULL,0<>NULL;

NULL<>NULL	0<>NULL
NULL	NULL

（3）<=、>=、< 和 >运算符

用来比较表达式的左边是小于或等于、大于或等于、小于还是大于它的右边

逻辑运算符

逻辑运算符用于对某个条件进行测试，运行结果为TRUE（1）或FALSE（0）。常见的逻辑运算符：

运算符	运算规则
NOT或！	逻辑非
AND或&&	逻辑与
OR或||	逻辑或
XOR	逻辑异或

（1）NOT运算符

对跟在其后的逻辑测试判断取反，把真变假，假变真

（2）AND运算符

AND 运算符用于测试两个或更多的值（或表达式求值） 的有效性，如果它的所有成分为真，并且不是 NULL，结果为真值，否则为假值。

（3）OR运算符

如果包含的值或表达式有一个为真并且不是 NULL （不需要所有成分为真 ），结果为真值，若全为假则结果为假值。

（4）XOR运算符

如果包含的值或表达式一个为真而另一个为假并且不是 NULL，那么结果为真值，否则为假值。

位运算符

位运算符参与运算的操作数按二进制位进行运算，包括位与(&)、位或(|)、位非(~)、位异或(^)、左移(<<)、右移（>>)

（1）位或(|)

位或运算的实质是将参与运算的几个数据按照对应的二进制数逐位进行逻辑或运算。对应的二进制位有一个或两个为1则该位的运算结果为1，否则为0。

（2）位与(&)

位与运算的实质是将参与运算的几个操作数按照对应的二进制数逐位进行逻辑与运算。对应的二进制位都为1则该位的运算结果为1，否则为0。

（3）位异或(^)

位异或运算的实质是将参与运算的两个数据按照对应的二进制数逐位进行逻辑异或运算。对应位的二进制数不同时，对应位的结果才为1。如果两个对应位数都为0或者都为1，则对应位的结果为0。

（4）左移(<<)

位左移运算符<<使指定的二进制值的所有位都左移指定的位数。左移指定位数之后，左边高位的数值将被移出并丢弃，右边低位空出的位置用0补齐。语法格式为：expr<<n。其中，n指定值expr要移位的位数。

（5）右移（>>)

位右移运算符>>使指定的二进制值的所有位都右移指定的位数。右移指定位数之后，右边低位的数值将被移出并丢弃，左边高位空出的位置用0补齐。语法格式为：expr>>n。其中，n指定值expr要移位的位数。

（6）位非(~)

位取反运算的实质是将参与运算的数据按照对应的二进制数逐位反转，即1取反后变为0、0取反后变为1。

运算符的优先级

运算符	优先级	运算符	优先级
+（正）、-（负）	1	NOT	5
*（乘）、/（除）、%（模）	2	AND	6
+（加）、-（减）	3	OR	7
=,>,<,>=,<=,<>,!=,!>,!<	4	=(赋值)	8

MySQL函数

MySQL中的函数包括数学函数、字符串函数、日期和时间函数、加密函数、控制流函数、类型转换函数、系统信息函数等其他函数

函数运用

数学函数

数学函数用于执行一些比较复杂的算术操作。若发生错误，所有的数学函数都会返回 NULL。下面对一些常用数学函数进行说明举例。
(1)GREATEST()和 LEAST()函数
GREATEST()和LEAST()是数学函数中经常使用的函数数，它们的功能是获得一组数中的最大值和最小值。 例如：

SELECT GREATEST(1,2,3),LEAST(1,2,3);

注意：MySQL不允许函数名和括号之间有空格

(2)FLOOR()和 CEILING()函数
FLOOR()用于获得小于一个数的最大整数值，CEILING()用于获得大于一个数的最小整数值，例如:

SELECT FLOOR(1.1),CEILING(1.1);

（3）ROUND()和 TRUNCATE()函数
ROUND()函数用于获得一个数的四舍五入的整数值，例如：

SELECT ROUND(5.1), ROUND(25.501), ROUND(9.8);

TRUNCATE()函数用于把一个数字截取为一个指定小数个数的数字，逗号后面的数字表示指定小数的位数，例如：

SELECT TRUNCATE(1.54578, 2), TRUNCATE(-76.12, 5);

（4）ABS()函数
ABS()函数用来获得一个数的绝对值，例如：

SELECT ABS(-878), ABS(-8.345);

（5）SIGN()函数
SIGN()函数返回数值的符号，返回的结果是正数（1）、负数（-1）或者零 (0)，例如:

SELECT SIGN(-2), SIGN(2), SIGN(O);

（6）SQRT()函数
SQRT()函数返回一个数的平方根，例如：

SELECT SQRT(25), SQRT(15), SQRT(1);

字符串函数

（1）ASCII()函数

ASCLL(char)

返回字符表达式最左端字符的 ASCII值。参数char 的类型为字符型的表达式，返回值为整型。

（2）CHAR()函数

CHAR(x1,x2,x3,...)

将x1、x2、x3……的 ASCII 码转换为宇符并将结果组合成一个字符串。参数x1、x2、x3……为介于0~255之间的整数，返回值为字符型。

（3）LEFT()、RIGHT()函数

LEFT|RIGHT(str,x)

分别返回从字符串 str 左边和右边开始指定x 个字符。

（4）TRIM(s)、LTRIM(s)、RTRIM(s)函数

TRIM(s)删除字符串s两侧的空格。

LTRIM(s)返回字符串s，字符串左侧空格字符被删除。

RTRIM(s)返回字符串s，字符串右侧空格字符被删除。

（5）REPLACE(s,n)函数

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

（6）SUBSTRING(s,n,len)函数

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

日期和时间函数

（1）NOW()函数

使用NOW()函数可以获得当前的日期和时间，它以 YYYY-MM-DD HH : MM: SS的格式返回当前的日期和时间。例如：

SELECT NOW(): 

（2）CURTIME()、CURDATE()函数

CURTIME()、CURDATE()函数比NOW()函数更为具体化，它们分别返回的是当前的时间和日期，没有参数。例如：

SELECT CURTIME(),CURDATE(); 

（3）YEAR(dstr)函数

YEAR(dstr)函数分析日期值 dstr 并返回其中关于年的部分。例如：

SELECT YEAR(20080512142800), YEAR('1982-11-02'); 

（4）MOTNTH()、MONTHNAME()函数

MOTNTH()、MONTHNAME()函数分别以数值和字符串的格式返回参数中月的部分。例如：

SELECT MONTH(20080512142800), MONTHNAME('1982-11-02'); 

（5）DAYNAME()函数

和 MONTHNAME()相似，DAYNAME()以字符串形式返回星期名。例如：

SELECT DAYNAME('2008-06-01'); 

加密函数

（1）AES_ENCRYPT()、AES_DECRYPT()函数

AES_ENCRYPT|AES_DECRYPT(str,key)

AES_ENCRYPT()返回的是密钥key 对字符串str 利用高级加密标准( AES )算法加密后的结果，结果是一
个二进制的字符串，以BLOB 类型存储。而 AES_DECRYPT()函数用于对用高级加密方法加密的数据进行解密。若检测到无效数据或不正确的填充，函数会返回 NULL。AES_ENCRYPT()和 AES_DECRYPT()函数可以被看做 MySQL 中普遍使用的最安全的加密函数。

（2）ENCODE()、DECODE()函数

 ENCODE | DECODE (str, key) 

ENCODE()函数用来对字符串 str 进行加密，返回的结果是一个二进制字符串，以BLOB 类型存储。DECODE()函数使用正确的密钥对加密后的结果进行解密。与AES_ENCRYPT()、AES_DECRYPT()函数相比，这两个函数加密程度相对较弱。

（3）PASSWORD()函数

 PASSWORD(str) 

返回字符串 str 加密后的密码字符串，适合于插入到 MySQL 的安全系统。该加密过程不可逆，和UNIX 密码加密过程使用不同的算法，主要用于 MySQL 的认证系统。例如：

SELECT PASSWORD('MySQL');

返回字符串 MySOL 的加密版本。

控制流函数

IF()函数

和许多脚本语言提供的 IF()函数一样，MySQL 的IF()函数也可以建立一个简单的条件测试：

IF(expr1, expr2, expr3) 

该函数有3个参数，第1个是要被判断的表达式，如果表达式为真，IF()将会返回第2个参数；如果为假，IF()将会返回第3 个参数。例如：

SELECT IF(2*4>9-5,'是','否'); 

先判断2*4 是否大于（9-5），是则返回 “是”，否则返回 “否”。

类型转换函数

CAST()函数

把一个值转换位指定的数据类型。

CAST(expr,AS type)

expr是 CAST()函数要转换的值，type 是转换后的数据类型。
在CAST()函数中 MySQL 支持以下数据类型：BINARX、CHAR、 DATE、 TIME、DATETIME、 SIGNED 和 UNSIGNED 类型 。

系统信息函数

用来获取系统本身的信息

函数	功能
DATABASE()	返回当前数据库名
BENCHMARK(n, expr)	将表达式expr 重复运行n次
CHARSET(str)	返回字符串 sr 的字符集
CONNECTION_ID()	返回当前客户的连接ID
FOUND_ROWS()	将最后一个 SELECT 查询（没有以 LIMIT 语句进行限制）返回的记录行数返回
GET_LOCK(str, dur)	获得一个由字符串 str 命名的并且有dur 秒延时的锁定
IS_FREE_LOCK(str)	检查以 str 命名的锁定是否释放
LAST_INSERT_ID()	返回由系统自动产生的最后一个AUTOINCREMENT ID 的值
MASTER_POS_WAIT(log, pos, dur)	锁定主服务器 dur 秒直到从服务器与主服务器的日志 log 指定的位置pos 同步
RELEASE_LOCK(str)	释放由字符串 str 命名的锁定
USER()或 SYSTEM_USER()	返回当前登录用户名
VERSION()	返回 MySOL 服务器的版本

存储过程和存储函数

创建存储过程

语法：

CREATE PROCEDURE 存储过程名([参数[,...]])存储过程体

查看存储过程

查看当前数据库的存储过程：

SHOW PROCEDURE STATUS;

查看存储过程的创建代码：

SHOW CREATE PROCEDURE 存储过程名;

调用存储过程

存储过程创建完后，被调用时必须使用CALL语句。

语法格式：

CALL 存储过程名([参数[,...]])

删除存储过程

删除时使用DROP PROCEDURE语句。删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

语法格式：

DROP PROCEDURE [IF EXISTS] 存储过程名;

创建存储函数

使用CREATE FUNCTION语句创建存储函数。

语法格式：

CREATE FUNCTION 存储函数名([参数[,...]])
	RETURNS 类型
	函数体

调用存储函数

使用SELECT关键字调用存储函数

语法格式:

SELECT 存储函数名([参数[,...]]);

触发器

触发器是用于保护表中数据的，触发器不需要调用，当有操作影响到触发器保护的数据时，触发器自行执行。

创建触发器

使用CREATE TRIGGER语句创建触发器。

语法格式：

CREATE TRIGGER 触发器名 触发事件 触发事件
ON 表名 FOR EACH ROW 触发器动作

在触发器中调用存储过程

删除触发器

使用DROP语句可以将触发器从数据库中删除：

语法格式：

DROP TRIGGER 触发器名;