MySQL教程

1 数据库的基本操作

1.1 创建数据库

查看当前所有存在的数据库：

show databases;

创建数据库：

create database database_name;

1.2 删除数据库

drop database database_name；

1.3 数据库存储引擎

1.3.1 存储引擎简介

DBMS使用数据库存储引擎进行创建、查询、更新、删除等操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。针对具体要求可以对每一个表使用不同的存储引擎。

查看系统所支持的引擎：

show engines；

1.3.2 InooDB存储引擎

1.3.3 MyISAM存储引擎

1.3.4 MEMORY存储引擎

1.3.5 存储引擎的选择

2 数据表的基本操作

2.1 创建数据表

2.1.1 创建表的语法形式

create table <表名>

(

字段名1，数据类型 [列级别约束条件] [默认值],

字段名2，数据类型 [列级别约束条件] [默认值],

......

[表级别的约束条件]

)

必须遵循以下约束：不区分大小写，不能使用SQL中的关键字

2.1.2 使用主键约束

1 单字段主键

（1） 在定义表的同时定义主键

create table tb_demo1

(

id INT(11) PRIMARY KEY,

name varchar(25)

)

（2）在定义完所有列之后定义主键

[CONSTRINT <约束名>] PRIMARY KEY [字段名]

**2 多字段主键 **

PRIMARY KEY [字段1,字段2,...,字段n]

2.1.3 使用外键进行约束

外键用来在两个表之间建立链接。

参照完整性：一个表的外键可以为空，若不为空，则必须对应另一个表中主键的某个值。

定义外键后，不允许删除在另一个表中具有关联关系的行

主表：主键所在的表

从表：外键所在的表

创建外键的语法规则如下：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [,字段名2,…] REFERENCES <主表名> 主键列1 [,主键列2,…]

外键名：为定义的外键约束的名称

字段名：字表需要添加外键约束的字段名

主表名：被子表外键所依赖的表的名称

主键列：主表中定义的主键列

2.1.4 使用非空约束

非空约束指字段的值不能为空：

字段名 数据类型 not null

2.1.5 唯一性约束

要求该列唯一：

字段名 数据类型 unique

在定义完所有列后指定唯一性约束

[CONSTRAINT <约束名>] unique(<字段名>)

2.1.6 使用默认约束

字段名 数据类型 default 默认值

2.1.7 设置表的属性值自动增加

字段名 数据类型 AUTO_INCREMENT

2.2 查看数据库表结构

2.2.1 查看表基本结构语句

describle 表名;

或者简写为：

desc 表名;

2.2.2 查看表详细结构语句

show create table <表名>

2.3 修改数据表

2.3.1 修改表名

alter table tb_name rename tb_newname

2.3.2 修改字段的数据类型

alter table tb_name modify 字段名 数据类型

2.3.3 修改字段名

alter table <表名> change <旧字段名> <新字段名> <新数据类型>

2.3.4 添加字段

(1) 添加无完整性约束的字段

alter table tb_name add <字段名> <数据类型>

(2) 添加有完整性约束的字段

alter table tb_name add <字段名> <数据类型> not null

(3) 在表的第一列添加字段

alter table tb_name add <字段名> <数据类型> first

(4) 在表的指定列添加字段

alter table tb_name add <字段名> <数据类型> alter <字段名>

添加到字段名的后面

2.3.5 删除字段

alter table <表名> drop <字段名>

2.3.6 修改字段的排列位置

alter table tb_name modify <字段名> <数据类型> first|after <字段2>

2.3.7 更改表的存储引擎

alter table <表名> engine=<更改后的引擎>

2.3.8 删除表的外键约束

alter table <表名> drop foreign key <外键约束>

2.4 删除数据库

2.4.1 删除没有被关联的表

drop table [if exists]表1，表2，…，表n;

2.4.2 删除被其他表关联的主表

需要先解除关联子表的外键约束

3 数据类型和运算符

3.1 MySQL数据类型介绍

3.1.1 整数类型

类型名称	说明	存储需求
TINYINT	很小的整数	1个字节
SMALLINT	小的整数	2个字节
MEDIUMINT	中等的整数	3个字节
INT(INTEGER)	普通的整数	4个字节
BIGINT	大整数	8个字节

数据类型	有符号	无符号
TINYINT	-128～127	0～255
SMALLINT	-327768～32767	0～65535
MEDIUMINT	-8388608～8388607	0～16777215
INT(INTEGER)	-2147483648～2147483647	0～4294967295
BIGINT	-9223372036854775808～9223372036854775807	0～18446744073709551615

INT(11) 此处的11表示显示宽度，显示宽度和数据类型的取值范围无关，数值的位数小于指定的宽度时，会由空格填充，如果插入了大于显示宽度的值，只要不超过该类型的取值范围，数值依然可以插入，且能够显示。

如果不指定显示宽度，则系统为每一种类型指定默认的宽度值：

tinyint(4)、 smallint(6)、 mediumint(9)、 int(11)、 bigint(20)

3.1.2 浮点数类型和定点数类型

类型名称	说明	存储需求
FLOAT	单精度浮点数	4个字节
DOUBLE	双精度浮点数	8个字节
DECIMAL(M,D)，DEC	压缩的“严格”定点数	M+2个字节

float(M,N) double(M,N) decimal(M,N)

M称为精度，表示总共的位数，N称为标度，表示小数的位数

float和double在不指定精度的时候，默认按照实际的精度

decimal不指定精度，默认为(10,0)

浮点数优点在于在长度一定的情况下，浮点数能表示更大的数据范围，缺点是会引起精度问题

3.1.3 日起与时间类型

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1902~2155	1字节
TIME	HH:MM:SS	-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-19 03:14:07 UTC	4字节

(1)YEAR

• 以4位字符串或者4位数字格式表示

• 以2位字符串表示的YEAR，‘00’～‘69’和‘70’～‘99’分别转换为2000到2069和1970到1999

• 以2位数字表示的YEAR，1～69和70到99转换为2001～2069和1970～1999。此处0被转换为2000

(2)TIME

• ‘D HH:MM:SS’格式的字符串，还可以使用以下任何一种“非严格”的语法：‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、‘D HH’、‘SS’。在插入数据库时，D被转换为小时数，格式为D*24+HH。
• ‘HHMMSS’格式，没有间隔符的字符串或HHMMSS格式的数值

(3)DATE

• 以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串歌手表示的日期，取值范围为‘1000-01-01’～‘9999-12-3’
• 以‘YY-MM-DD’或者‘YYMMDD’字符串格式表示的日期YY部分表示年值，参考YEAR部分
• 以YY-MM-DD或者YYMMDD数字格式表示的日期
• 使用CURRENT_DATE或者NOW(),输入当前系统日期

(4)DATETIME

• 以‘YYYY-MM-DD HH:MM:SS’或者‘YYYYMMDDHHMMSS’字符串格式表示的值，取值范围为‘1000-01-01 00:00:00’～‘9999-12-3 23:59:59’
• 以‘YY-MM-DD HH:MM:SS’或者‘YYMMDDHHMMSS’字符串格式表示的值，YY部分参考YEAR
• 以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间

(5)TIMESTAMP

• TIMESTAMP的显示格式与DATETIME相同，显示宽度固定在19个字符，取值范围小于DATETIME为‘1970-01-01 00:00:01 UTC‘～‘2038-01-19 03:14:07 UTC’，其中UTC为世界标准时间
• TIMESTAMP和DATETIME除了存储字节和支持的范围不同外，DATETIME存储日期数据按实际输入的格式存储，与时区无关；而TIMESTAMP的存储以UTC格式保存，存储时对当前时区进行转换，检索时再转换为当前时区。即查询时根据当前时区不同，显示的时间值时不同的

3.1.4 文本字符串类型

字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据。MySQL支持两类字符串数据，文本字符串和二进制字符串

类型名称	说明	存储需求
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个字节，取决于枚举值的数目
SET	一个设置，字符串对象可以有零个或多个SET对象	1，2，3，4或8个字节，取决于集合成员的数量

1 、CAHR和VARCHAR类型

• CHAR(M)为固定长度字符串，在定义时指定字符串列长。当保存时在右侧填充空格以达到指定的长度，M表示列长度，M的范围为0～255个字符

• VARCHAR(M)是长度可变的字符串，M表示最大列长度。M的范围为0～65535。实际占用空间为字符串的实际长度加1

2、TEXT类型

TEXT列保存非二进制字符串。当保存或查询TEXT列的值时，不删除尾部空格。最大长度见上表

3、ENUM类型

ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值，语法格式如下：

字段名 ENUM('值1','值2',...'值n')

ENUM类型在取值时只能是在指定的枚举列表中取，而且一次只能取一个。最多可以枚举65535个元素

4、SET类型

SET是一个字符串对象，可以有零个或者多个值，最多有64个成员，其值为表创建时规定的一列值

SET('值1','值2',...'值n')

3.1.5 二进制字符串类型

类型名称	说明	存储需求
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

1、BIT类型

BIT为每个值的位数，范围为1～64，如果M被省略，默认为1。如果BIT(M)列分配的长度小于M位，在值的左边用0填充。如果大于M位，则不能插入

2、BINARY和VARBINARY类型

列名称 BINARY(M) 或者 VARBINARY(M)

• BINARY长度固定，指定长度后，不足最大长度的在右边填充‘\0’

• VARBINARY类型的长度时可变的，指定长度后，其长度可以是0到最大值之间

3、BLOB类型

BLOB是一个二进制大对象，用来存储可变数量的数据。

用来存储二进制字符串(字节字符串)，没有字符集，并且排序和比较根据列值字节的数值

3.2 如何选择数据类型

1、整数和浮点数

• 若无需小数部分，则用整数表示，否则，用浮点数
• 若存储精度要求较高，则用DOUBLE

2、浮点数和定点数

• 在长度一定的情况下，浮点数可以表示更大的数据范围
• 但浮点数容易产生误差，因而对精度要求比较高时，使用DECIMAL
• DECIMAL在MySQL中所以字符串存储的，两个浮点数减法和比较运算也容易出现问题，进行比较，最好使用DECIMAL

3、日期与时间类型

• 如果只记录年份，用YEAR
• 如果只记录时间，用TIME
• 如果同时需要记录时间和日期，用DATETIME或者TIMESTAMP，存储较大日期使用DATETIME
• 如果需要插入记录同时记录当前时间，用TIMESTAMP，且比DATETIME空间上更有效

4、CHAR与VARCHAR之间的特点与选择

• CHAR固定长度，VARCHAR可变长度
• CAHR会自动删除插入数据的尾部空格，VARCHAR不会删除尾部空格
• CHAR因为固定长度，因而处理速度快，但缺点时浪费空间
• 存储引擎对选择的影响

​ — 对于MyISAM：使用CHAR，使得整个表静态化，从而使数据检索更快

​ — 对于InnoDB：使用VARCAHR，因为InnoDB数据表不区分固定长度和可变长度

5、ENUM和SET

• ENUM只能取单值，例如存储性别
• SET可取多值，例如存储兴趣爱好

6、BLOB和TEXT

• BLOB存储图片，音频等信息
• TEXT存储纯文本文件

3.3 常见运算符介绍

3.3.1 算术运算符

加+、减-、乘*、除/、求余%

3.3.2 比较运算符

运算符	作用
=	等于
<=>	安全的等于
<>(!=)	不等于
<=	小于等于
>=	大于等于
>	大于
IS NULL	判断一个值是否为NULL
IS NOT NULL	判断一个值是否不为NULL
LEAST	在两个或多个参数时，返回最小值
GREATEST	在两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否在两个值之间
ISNULL	与IS NULL作用相同
IN	判断一个值是否是IN列表中的任意一个值
NOT IN	判断一个值是否不是IN列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

1、等于运算符（=）

• 若一个或两个参数为NULL，则比较结果为NULL
• 将字符串和整数进行比较，MySQL可以自动转换

2、安全等于运算符（<=>）

• <=>可以对NULL进行判断，两者都为NULL，返回1

3、不等于运算符<>(!=)

用于数字、字符串、表达式不相等的判断

4、IS NULL(ISNULL)，IS NOT NULL运算符

• NULL IS NULL

• ISNULL(NULL)

• NULL IS NOT NULL

5、BETWEEN AND

• 取端点，同样返回1

6、LEAST

least(10,5)

7、GREATEST

greatest(value1,value2)

8、IN、NOT IN运算符

2 IN (1,3,5."thks")

9、LIKE

• LIKE在匹配时，可以使用以下两种通配符

  — ‘%’，匹配任意数目的字符

  — ‘_’，只能匹配一个字符

10、REGEXP

• ‘^’匹配以该字符后面的字符开头的字符串
• ‘$’匹配以该字符后面的字符结尾的字符串
• ‘.’匹配任意一个单字符
• ‘[…]’匹配在方括号内的任意字符
• ‘*’匹配零个或多个在它前面的字符，“x*”匹配任何数量的‘x’字符串，“[0-9]*”匹配任何数量的数字

3.3.4 逻辑运算符

运算符	作用
NOT或者！	逻辑非
AND或者&&	逻辑与
OR或者||	逻辑或
XOR	逻辑异或

3.3.5 位运算符

运算符	作用
|	位或
&	位与
^	位异或
<<	位左移
>>	位右移
~	位取反

3.3.6 运算符的优先级

最低	=（赋值运算）
	||，OR
	XOR
	&&，AND
	NOT
	BETWEEN,CASE,WHEN,THEN,ELSE
	=(比较),<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
	|
	&
	<<,>>
	-,+
	*,/,%
	^
	-(负号),~
最高	!

4 MySQL函数

4.1 数学函数

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

abs(x)返回x的绝对值

pi()返回圆周率

4.1.2 平方根函数和求余函数

SQRT(x)返回非负数x的二次方根

MOD(x,y)返回x被y除后的余数

4.1.3 获取整数的函数

CEIL(x)和CEILING(x)返回不小于x的最小整数值，返回值转化为一个BIGINT

FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT

4.1.4 获取随机数的函数

rand()返回一个随机浮点数，范围在0~1之间，每次产生的随机数是不同的

rand(x)当参数x相同时，产生的随机数也相同

4.1.5 ROUND和TRUNCATE

round(x)返回最接近x的整数，四舍五入

round(x,y)返回最接近x的数，其值保留到小数点之后y位

truncate(x,y)返回被舍去至小数点后y位的数字x

4.1.6 符号函数

sign(x)返回参数的符号，x为负、零、正时，返回结果依次为-1、0、1

4.1.7 幂运算函数

pow(x,y)和power(x,y)返回x的y次方的结果

exp(x)返回e的x次方的结果

4.1.8 对数运算函数

log(x)返回x的自然对数，x相对于基数e的对数

log10(x)返回x的基数为10的对数

4.1.9 角度与弧度相互转换的函数

Radians(x)将参数x由角度转换为弧度

degrees(x)将参数有弧度转化为角度

4.1.10 正弦函数和反正弦函数

sin(x)返回x的正弦

asin(x)返回x的反正弦

4.1.11 余弦函数和反余弦函数

cos(x)返回x的余弦

acos(x)返回x的反余弦

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

tan(x)返回x的正切

atan(x)返回x的反正切

cot(x)返回x的余切

4.2 字符串函数

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

char_length(str)返回值为str所包含的字符个数

length(str)返回str的字节长度

4.2.2 合并字符串函数

concat(s1,s2,...)返回结果为连接参数产生的字符串。

• 若有一个参数为NULL，则返回NULL
• 若连接参数都为非二进制字符串，则返回非二进制字符串
• 若存在一个二进制字符串，则返回二进制字符串

concat_ws(x,s1,s2,...)第一个参数x是其他参数的分隔符，若分隔符为NULL，则结果为NULL

4.2.3 替换字符串的函数

insert(s1,x,len,s2)返回s1从x开始长度为len，替换为s2

若x超出字符串长度，则返回原字符串

4.2.4 字母大小写转换

lower(str)和lcase(x)可以将字符串str中的字母字符全部转换为小写字母

upper(str)和ucase(str)可以将字符串str中的字母字符全部转换为大写字母

4.2.5 获取指定长度的字符串的函数

left(s,n)返回字符串s开始的最左边的n个字符

right(s,n)返回字符串中最右边n个字符

4.2.6 填充字符串的函数

lpad(s1,len,s2)返回s1，其左边由s2填补到len字符长度

rpad(s1,len,s2)返回s1，其右边由s2填补到len字符长度

4.2.7 删除空格的函数

ltrim(s)删除左边的空格

rtrim(s)删除右边的空格

trim(s)删除两侧的空格

4.2.8 删除指定字符串的函数

trim(s1 from s)删除字符串s中两端所有的子字符串s1

4.2.9 重复生成字符串的函数

repeat(s,n)返回重复的字符串s，字符串s的数目为n

4.2.10 空格字符串和替换函数

space(n)返回一个由n个空格组成的字符串

replace(s,s1,s2)使用字符串s2替换字符串s中的所有s1

4.2.11 比较字符串大小的函数

strcmp(s1,s2)若所有字符串均相同，返回0，若根据当前分类次序，第一个参数小于第二个，则返回-1，反之，返回1

4.2.12 获取子串的函数

substring(s,n,len)和mid(s,n,len)返回截取s，从n位置开始，长度为len的字符串

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

locate(str1,str)、position(str1 IN str)和instr(str,str1)3个函数作用相同，返回子字符串s1在s中的开始位置

4.2.14 字符串逆序的函数

reverse(str)将字符串s反转

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

elt(N,s1,s2,s3,...sn)返回第N个字符串

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

field(s,s1,s2,…)返回s在s1,s2,…中第一次出现的位置

4.2.17 返回子串位置的函数

find_in_set(s1,s2)s2字符串列表是由“，”隔开的字符串组成的列表，若s1不在s2中，返回0，返回s1在s2中的位置

4.2.18 选取字符串的函数

make_set(x,s1,s2,…)返回由x的二进制数指定的相应位的字符串组成的字符串

4.3 日期和时间函数

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

curdate()和current_date()将当前日期按照‘YYYY-mm-DD’和’YYYYMMDD’的格式返回，具体格式根据函数在字符串中的语境而定

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

current_timestamp()、localtime()、now()、sysdate()四个函数作用相同，返回当前日期和时间值，格式为’YYYY-MM-DD HH:MM:SS’或’YYYYMMDDHHMMSS’,具体格式根据语境而定

4.3.3 UNIX 时间戳函数

unix_timestamp(date)若无参数调用，则返回一个unix时间戳，若用date调用，将会返回‘1970-01-01 00:00:00’后的秒数

from_unixtime(date)把unix时间戳转换为普通格式的时间,与unix_timestamp互为反函数

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

utc_date()函数返回当前UTC日期值

utc_time()函数返回当前UTC时间

4.3.5 获取月份的函数

month(date)返回date对应的月份

Monthname(date)返回date对应的月份的英文

4.3.6 获取星期的函数

dayname(d)返回d对应的工作日的英文名称，如Sunday、Monday

dayofweek返回d对应的一周的索引，1表示周日，2表示周一

weekday()返回日期对应的工作日索引

4.3.7 获取星期数的函数

week(d)计算d是一年中的第几周

weekofyear(d)计算某天是一年中的第几周，相当于week(d,3)

4.3.8 获取天数的函数

dayofyear(d)返回d是一年中的第几天

dayofmonth(d)返回d是这个月的第几天

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

year(d)返回d对应的年份

quarter(d)返回d对应的季度

minute(d)返回d对应的分钟数

second(d)返回d对应的秒数

4.3.10 获取日期的指定值得函数

extract(type from date)从日期中提取一部分

extract(YEAR from '2016-07-02 01:02:03')返回2016，类似的，YEAR_MONTH返回201607，DAY_MINUTE返回120102

4.3.11 时间和秒钟转换的函数

timr_to_sec(time)将时间转换为秒数

sec_to_time(seconds)将秒数转换为时间

4.3.12 计算日期和时间的函数

date_add(date,INTERVAL expr type)和adddate()

date是一个DATETIME或DATE值，expr是一个表达式，type为关键词，指示表达式解释的方式

例如date_add('2010-12-31 23:59:59',INTERVAL 1 SECOND) as coll，返回’2011-01-01 00:00:00’

date_sub()和subdate()

addtime(date,expr)

函数将expr值添加到date，并返回修改后的值，expr是一个时间表达式

subtime(date,expr)执行时间减操作

datediff(date1,date2)返回时间date1和date2之间的天数，用date1减date2

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

date_format(date,format)根据format显示date的值

4.4 条件判断函数

4.4.1 IF

if(expr,v1,v2)若expr为true，返回v1，否则返回v2

4.4.2 IFNULL

IFNULL(V1,V2)若v1不为null，返回v1,否则返回v2

4.4.3 CASE

case expr when v1 then r1 when v2 then r2 else r3 end

4.5 系统信息函数

4.5.1 获取MySQL的版本号，连接数和数据库名的函数

version()查看MySQL的版本号

connection_id()返回MySQL服务器当前连接的次数

database()和schema()返回数据库名

4.5.2 获取用户名的函数

user()、current_user()、system_user()、session_user()返回当前被MySQL服务器验证的用户名和主机名的组合

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

charset(str)返回字符串str自变量的字符集

collation(str)返回字符串str的字符排列方式

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

4.6 加/解密函数

4.6.1 加密函数

password(str)从原明文密码str计算并返回加密后的密码字符串

MD5(str)为字符串计算出一个MD5 128比特校验和

encode(str，pswd_str)使用pswd_str作为密码，加密str

4.6.2 解密函数

decode(crypt_str,pswd_str)使用pswd_str作为密码解密加密字符串crypt_str

4.7 其他函数

4.7.1 格式化函数

format(x,n)将数字x格式化，并四舍五入保留小数点后n位

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

conv(N,from_base,to_base)将数字N由from_base转换为to_base函数

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

inet_aton(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以是4或者8bit

inet_ntoa给定一个数字网络地址，返回作为字符串的该地址的点地址

4.7.4 加锁函数和解锁函数

get_lock(str,timeout)设法使用字符串str给定的名字得到一个锁，持续时间timeout秒。若成功得到锁，返回1，操作超时，返回0，发生错误，返回null

release_lock(str)解开被get_lock获取的，用于str所命名的锁，若解开了，返回1，若该线程未创建锁，返回0，若命名的锁不存在，则返回null

is_free_lock(str)检查名为str的锁是否可以被使用，可以，返回1，否则返回0，出现错误，返回null

is_user_lock(str)检查名为str的锁是否正在被使用，是，返回使用该锁的客户端的连接标识符，否则，返回null

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

benchmark(count,expr)函数重复count次执行表达式expr

4.7.6 改变字符集的函数

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

4.7.7 改变数据类型的函数

cast(x,As type)和convert(x,type)函数将一个类型的值转换为另一个类型的值

可转换的type有BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGEND、UNSIGEND

5 查询数据

5.1 单表查询

5.1.1 查询所有字段

使用“*”通配符指定查询所有列的名称

select * from 表名

5.1.2 查询指定字段

select 列名 from 表名

select 字段名1，字段名2，...，字段名n form 表名

5.1.3 查询指定记录

select 字段名1，字段名2，...，字段名n

form 表名

where 查询条件

5.1.4 带IN关键字的查询

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

where 字段名 in （value1,value2...）

5.1.5 范围查询

where 字段名 (not) between value1 and value2

5.1.6 带LIKE的字符匹配查询

where 字段名 like '_b%'

'%'匹配任意长度的字符，包括零字符

’_‘一次只能匹配一个字符

5.1.7 查询空值

where 字段名 (not) is null

5.1.8 带AND的多条件查询

增加查询的限制条件

where 字段名 查询条件1 and 查询条件2

5.1.9 带OR的多条件查询

where 字段名 查询条件1 or 查询条件2

5.1.10 查询结果不重复

select distinct 字段名 from 表名

5.1.11 对查询结果排序

• select 字段名 from 表名 order by 字段名

• select 字段名 from 表名 order by 字段名1,字段名2先按字段1排序，再按字段2排序

• select 字段名 from 表名 order by 字段名 DESC（DESC降序排序，ASC升序排序）

5.1.12 分组查询

[group by 字段] [having <条件表达式>]

• 创建分组

​ group by通常和集合函数一起使用，例如MAX()、MIN()、COUNT()、SUM()、AVG()

​ select s_id,COUNT(*) as Total form fruits group by s_id;

​ GROUP_CONCAT(字段名)将每个分组中的各个字段的值显示出来

• 使用HAVING过滤分组

  select s_id,CRGOU_CONCAT(f_name) as Names

  from fruits

  group by s_id having count(f_name)>1;

• 在GROUP BY子句中使用WITH ROLLUP

  使用with rollup，在所有查询出的分组记录之后增加一条记录，该记录统计查询出的记录的数量

• 多字段分组

  select * from 表名 group by 字段名1,字段名2,...

• GROUP BY 和ORDER BY一起使用

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

limit [位偏移量n]，行数m

返回从第n+1个记录开始，行数为m的记录

5.2 使用聚合函数查询

5.2.1 COUNT()函数

COUNT()统计数据表中包含的记录行的总数，根据查询结果返回列中包含的数据行数

COUNT(*)计算表中总的行数，不管某列是否为空

COUNT(字段名)计算指定列下总的行数，忽略空值的行

5.2.2 SUM()函数

sum()统计查询结果的总和

5.2.3 AVG()函数

avg()计算返回的行数，和每行数据的和，求得指定列数据的平均值

5.2.4 MAX()函数

返回指定列的最大值

5.2.5 MIN()函数

返回指定列的最小值

5.3 连接查询

5.3.1 内连接查询

select suppliers.s_id,s_name,f_name,f_price

from fruits,suppliers

where fruits.s_id = suppliers.s_id;

内连接查询语句，使用INNER JOIN语法进行内连接查询

select suppliers.s_id,s_name,f_name,f_price

from fruits inner join suppliers

on fruits.s_id = suppliers.s_id;

如果一个连接查询中，涉及的两个表都是同一个表，这种查询称为自连接查询

select f1.f_id,f1.f_name

from fruits as f1,fruits as f2

where f1.s_id=f2.s_id and f2.f_id ='a1';

5.3.2 外连接查询

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

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

  select customers.c_id,order.o_num

  from customers left outer join orders

  on customers.c_id =orders.c_id;

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

  与左连接相反

5.3.3 复合条件连接查询

在连接查询的过程中，通过添加过滤条件，限制查询结果，使查询结果更加准确

5.4 子查询

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

any和some关键字是同义词，表示满足其中任一条件，只要满足内层子查询的任何一个比较条件，就返回一个作为外层查询的条件

select num1 from tb1 where num1> any(select num2 from tb2)

5.4.2 带ALL关键字的子查询

需要满足所有内层查询条件

select num1 from tb1 where num1> all(select num2 from tb2)

5.4.3 带exists关键字的子查询

exists关键字后面的参数是一个任意的子查询，如果子查询至少返回一行，则为true

select * from fruits where exists (select s_name from suppliers where s_id =107);

not exists与exists使用方法相同，返回结果相反

5.4.4 带IN关键字的子查询

IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列提供给外层查询语句进行比较

select c_id from orders where o_num in (select o_num from orderitems where f_id ='c0');

5.4.5 带比较运算符的子查询

5.5 合并查询结果

select column,... from table1

union [ALL]

select column,... from table2

5.6 为表和字段取别名

表名 [as] 表别名

列名 [as] 列别名

5.7 使用正则表达式查询

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	’^b‘匹配以b开头的字符	book,big,banana
$	匹配文本的结束字符	’st$‘匹配以st结尾的字符	test, resist
.	匹配文本的单个字符	'b.t’匹配任何b和t之间有一个字符	bit,bat,but
*	匹配零个或多个在它前面的字符	'f*n’匹配字符f和n之间有任意个字符	fn,fan,faan
+	匹配前面的字符1次或多次	’ba+‘匹配以b开头，后面至少一个a	ba,bay,bare
<字符串>	匹配包含指定的字符串的文本	‘fa’	fan,afa,faad
[字符合集]	匹配字符集合中的任何一个字符	’[xz]‘匹配x或z	dizzy,zebra,extra
[^]	匹配不在括号中的任何一个字符	'^abc’匹配不包含a、b、c的字符串	desk,fox
字符串{n,}	匹配前面的字符串至少n次	b{2}匹配2个或更多b的字符串	bbb,bbbb,bbbbb
字符串{n,m}	匹配前面的字符串至少n次，至多m次	b{2,4}匹配最少2个b，最多4个b的字符串	bb,bbb,bbbb

6 插入、更新与删除数据

6.1 插入数据

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

insert into table_name (column_list) values (value_list);

column_list可以为空，此时value_list需要和数据表中字段定义的顺序相同

6.1.2 为表指定字段插入数据

未指定字段，若有默认值，则插入默认值，无，则插入NULL

6.1.3 同时插入多条记录

insert into table_name (column_list)

values (value_list1),(value_list2)...,(value_listn);

6.1.4 将查询结果插入到表中

insert into table_name (column_list)

select (column_list) from table_name2 where (condition);

6.2 更新数据

update table_name

set column_name1 = value1,column_name2=value2,...,column_namen=valuen

where (condition)

6.3 删除数据

delete from table_name [wher <condition>]

delete from table_name

7 索引

7.1 创建索引

7.1.1 创建表时创建索引

create table table_name [col_name data_type]

[unique|fulltext|spatial] [index|key] [index_name] (col_name [length]) [ASC|DESC]

unique、fulltext和spatial为可选参数，分别表示唯一索引、全文索引和空间索引；

index和key为同义词，两者作用相同，用来指定创建索引

col_name为需要创建索引的字段列，该列必须是从数据表中定义的多个列中选择

index_name指定索引的名称，为可选参数，若不指定，则默认col_name为索引值

length为可选参数，表示索引长度，只有字符串才能指定索引长度

ASC或DESC指定升序或降序的索引存储

• 创建普通索引

  最基本的索引类型，没有唯一性约束，作用只是加快对数据的访问速度

  index(col_name)

  可以用show create table查看表结构

  使用explain语句查看索引是否正在使用，见p267

• 创建唯一索引

  创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表，索引值必须唯一，但允许空值，若是组合索引，则列值得组合必须唯一

  unique index UniqIdx(id)

  在id字段上已经成功建立了一个名为UniqIdx的唯一索引

• 创建单列索引

  在数据表的某一个字段上创建的索引，前两个都是单列索引

  index Single(name(20))

  索引长度为20

• 创建组合索引

  index MultiIdx(id,name,age(100))

  查询字段时遵循“最左前缀”，此处可以查询(id,name,age)、(id,name)、(id)

• 创建全文索引

  FULLTEXT可以用于全文索引，只有MyISAM存储引擎支持全文索引，并且只为char、varchar、text列创建

  fulltext index FullTxtIdx(info)

  此处因为MySQL的默认引擎为InnoDB，需要修改表的存储引擎为MyISAM

• 创建空间索引

  空间索引也必须在MyISAM类型的表中创建，且字段必须非空

  spatial index SpatIdx(column_name)

7.1.2 在已存在的表上创建索引

• 使用ALTER TABLE创建索引

alter table table_name add [unique|fulltext|spatial] [index|key] [index_name] (column_name[length],...) [ASC|DESC]

• 使用create table创建索引

create [unique|fulltext|spatial] index index_name

on table_name (column_name[length],...) [ASC|DESC]

7.2 删除索引

• 使用ALTER TABLE删除索引

  alter table table_name drop index index_name;

• 使用drop index删除索引

  drop index index_name on table_name;

8 存储过程和函数

存储过程就是多条SQL语句的集合

8.1 创建存储函数和函数

8.1.1 创建存储过程

create procedure sp_name ([proc_parameter]) [characteristics...] routine body

• sp_name为存储函数的名称，proc_parameter指定存储过程的参数列表，列表形式如下：

​ [in|out|inout] param_name type

• in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出

• param_name表示参数名称 type表示参数类型

• characteristics指定存储过程的特性,见p286

• routine_body是SQL代码的内容，可以用begin…end来表示SQL代码的开始与结束

例：

create procedure AvgFruitsPrice()

begin

select AVG(f_price) as avgprice from fruits;

end;

create procedure CountProc (Out param1 INT)

begin

select COUNT(*) into param1 from fruits;

end;

8.1.2 创建存储函数

create function func_name([func_parameter])

returns type

[characteristic...] routine_body

func_parameter为存储过程的参数列表

[in|out|inout] param_name type

例：

create function NameByZip()

returns cahr(50)

return (select s_name from suppliers where s_call='48076');

8.1.3 变量的使用

变量可以在子程序中声明并使用，变量的作用范围在begin…end程序中

• 定义变量

  declare var_name[,varname]... date_type [default value]

  var_name为局部变量的名称，default value子句给变量提供一个默认值，值可以是一个常数，也可以是一个表达式

• 为变量赋值

  set var_name =expr [,var_name=expr]...;

  例：

  declare var1,var2,var3;

  set var1=10,var2=20;

  set var3=var1+var2;

  还可以通过select…into为变量赋值

  select col_name[,...] into var_name[,...] table_expr;

  例：

  declare fruitname char(50);

  declare fruitprice decimal(8,2);

  select f_name,f_price into fruitname,fruitprice from fruits where f_id='a1'

8.1.4 定义条件和处理程序

• 定义条件

  declare condition_name condition for [condition_type]

  [condition_type]:

  sqlstate [value sqlstate_value |mysql_error_code]

  sql_state和MySQL_error_code可以表示MySQL错误，sql_state为长度为5的字符串类型的错误代码，

  MySQL_error_code为数值类型错误代码

  例：

  declare command_not_allowed condition for sqlstate '42000';

  declare command_not_allowed condition for 1148

• 定义处理程序

  declare handler_type handler for condition_value[,...] sp_statement

  handler_type:

  ​ continue|exit|undo

  conditon_value:

  ​ sqlstate [value] sqlstate_value|condition_name|sqlwarning|not found|

  ​ sqlexception|mysql_error_code

  例：

  declare continue handler for sqlstate '42S02' set @info='no_such_table'

  declare exit handler for sqlwaring set @info='error'

  具体见p291

8.1.5 光标的使用

• 声明光标

  declare cursor_name cursor for select_statement

  例：

  declare cursor_fruit cursor for select f_name,f_price from fruits;

• 打开光标

  open cursor_name

• 使用光标

  fetch cursor_name into var_name[,var_name]...

  var_name表示将SELECT语句查询的信息存入该参数中

  例：

  fetch cursor_fruit into fruit_name,fruit_price;

• 关闭光标

  close cursor_name

8.1.6 流程控制的使用

• IF语句

  if expr_condition then statement_list

  ​ [elseif expr_condition then statement_list]...

  ​ [else statement_list]

  end if

  例：

  if val is null

  ​ then select 'val is null'

  ​ else select 'val is not null'

  end if;

• CASE语句

  case case_expr

  ​ when when_value then statement_list ...

  ​ else statement_list

  end case

  case

  ​ when expr_condition then statement_list

  ​ else statement_list

  end case

• LOOP语句

  [loop_label:] loop

  ​ statement_list

  end loop [loop_label]

  例：

  declare id int default 0

  add_loop:loop

  set id=id+1

  ​ if id>=10 then leave add_loop

  ​ end if;

  end loop add_loop;

• LEAVE语句

  用来退出任何被标注的流程控制构造

  leave label

• ITERATE语句

  将执行顺序转到语句段开头

  iterate label

  只可出现在loop、repeat、while语句内

  例：

  create peocedure doiterator()

  begin

  declare p1 INT default 0;

  my_loop:LOOP

  ​ set p1=p1+1;

  ​ if p1<10 then iterate my_loop;

  ​ elseif p1>20 then leave my_loop;

  ​ end if

  ​ select 'p1 is between 10 and 20';

  end loop my_loop

  end

• REPEAT语句

  [repeat_label:] REPEAT

  ​ statement_list

  until expr_condition

  end repeat[repeat_label]

• WHILE语句

  [while_label:]while expr_condition DO

  ​ statement_list

  end while[while_label]

8.2 调用存储过程和函数

8.2.1 调用存储过程

call sp_name([parameter[,...]])

8.2.3 调用存储函数

与调用MySQL内部函数的使用方法一样

8.3 查看存储过程和函数

8.3.1 查看存储过程和函数的状态

show [procedure|function] status [like 'pattern']

8.3.2 查看存储过程和函数的定义

show create [procedure|function] sp_name

8.3.3 从information_schema.Routines表查看存储过程和函数信息

select * from information_schema.Routinues

where routine_name ='sp_name'

8.4 修改存储过程和函数

alter procedure CountProc

modifies sql data

sql security invoker;

8.5 删除存储过程和函数

drop (procedure|function) [if exists] ap_name

9 视图

视图是从一个或者多个表中导出的，视图的行为和表非常相似，但视图是一个虚拟表

9.1 创建视图

9.1.1 创建视图的语法格式

create [or replace] [algorithm ={undefined|merge|temptable}]

view view_name [(column_list)]

as select_statement

[with [cascaded|local] check option]

• REPLACE表示替换已创建的视图
• ALGORITHM表示视图选择的算法，undefined表示MySQL自动选择算法，merge表示将使用的视图与视图定义合并起来，temptable表示将视图的结果存入临时表
• view_name为视图名称
• column_list为属性列
• select_statement为SELECT语句
• [with [cascaded|local] check option]表示视图更新时在权限范围内，cascaded为默认值，表示更新视图时要满足所有相关视图和表的条件，local表示更新视图时满足该视图本身定义的条件即可

9.1.2 在单表上创建视图

例：create view view_t(qty,price,total) as select quantity,price,quantity*price from t;

9.1.3 在多表上创建视图

例：create view stu_glass(id,name,glass) as select student.s_id,student.name,stu_info.glass

​ from student,stu_info where student.s_id=stu_infop.s_id;

9.2 查看视图

9.2.1 使用DESCRIBLE语句查看视图

describle 视图名；

9.2.2 使用SHOW TABLE STATUS语句查看视图的基本信息

show table status like '视图名';

9.2.3 使用SHOW CREATE VIEW语句查看视图详细信息

show create view 视图名;

9.2.4 在views表中查看视图信息

select * from information_schema.views

9.3 修改视图

• create or replace

  create [or replace] [algorithm ={undefined|merge|temptable}]

  view view_name [(column_list)]

  as select_statement

  [with [cascaded|local] check option]

• 使用ALTER语句

  alter [algorithm ={undefined|merge|temptable}]

  view view_name [(column_list)]

  as select_statement

  [with [cascaded|local] check option]

9.4 更新视图

• update view_t set colmun_name=value
• insert into t value(3,5)更新基本表以更新视图
• delete from view_t2 where price=5

9.5 删除视图

drop view [if exists]

​ view_name [,view_name]...

​ [restrict|cascade]

10 MySQL触发器

触发器是一个特殊的存储过程，不同的是不需要CALL调用，值要当一个预定义的事件发生，就会被MySQL自动调用

10.1 创建触发器

create trigger trigger_name trigger_time trigger_event

on tb1_name for each row trigger_stmt

• trigger_name触发器名称
• trigger_time出发时机，before或者after
• trigger_event标识触发事件
• trigger_stmt触发器执行语句

例：

create trigger ins_sum before insert on account

for each row set @sum=@sum+NEW.amount;

10.1.2 创建有多个执行语句的触发器

create trigger trigger_name trigger_time trigger_event

​ on tb1_name for each row

​ begin

​ 语句执行列表

​ end

10.2 查看触发器

• show triggers;
• select * from INFORMATION_SCHEMA.TRIGGERS where condition

10.3 触发器的使用

触发器与表相关，当对表进行insert、delete、update语句时，将激活触发程序

10.4 删除触发器

drop trigger [schema_name.]trigger_name

11 MySQL用户管理

MySQL是一个多用户数据库，具有功能强大的访问控制系统。分为root用户和普通用户，root用户拥有所有权限，普通用户只拥有被root用户授予的权限

11.1 权限表

11.1.1 user表

user是MySQL中最重要的一个权限表，记录允许连接到服务器的账户信息，有42个字段，被分为以下几种：

• 用户列

  包括Host、User、Passowrd，只有此三种均匹配才能与MySQL建立连接

• 权限列

  决定用户的权限，普通权限用户操作数据库，高级权限用于数据库管理

• 安全列

  有6个字段，两个ssl相关的用于加密，两个x509标准用于识别用户，Plugin相关用于验证用户身份

• 资源控制列

  包含4个字段

  • max_questions— 用户每小时允许执行的查询操作次数
  • max_uodates— 用户每小时允许执行的修改操作次数
  • max_connections— 用户每小时允许执行的连接操作次数
  • max_user_connections— 用户允许同时建立的连接次数

11.1.2 db表和host表

• db表存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库

• host表存储某个主机对数据库的操作权限

11.1.3 tables_priv表和columns_priv表

• tables_priv用来对表设置操作权限

• columns_priv用来对某一列设置操作权限

11.1.4 procs_priv表

• 对存储过程和存储函数设置操作权限

11.2 账户管理

11.2.1 登录和退出MySQL服务器

mysql -h localhost -u root -p test

11.2.2 新建普通用户

• create user user_specification[,user_specification]...

  user_specification:

  user@host

  [

  ​ indentified by [password] 'password'

  ​ | identified with auth_plugin [AS 'auth_string']

  ]

  • user表示创建的用户名称
  • host表示允许登录的主机
  • identified by表示设置用户密码，[password]表示使用哈希值设置密码
  • identified with为用户指定一个身份

  例：create user 'jeffrey'@'localhost'inentified by 'mypass'

• grant peivileges ON db.table

  TO user@host [identified by 'password'][,user[identified by 'password']]

  [with grant option];

• 直接操作用户数据表

  insert into MySQL.user(Host,user,password,[privilegelist])

  values('host,'username',PASSOWRD('passowrd'),privilegevaluelist)

11.2.3 删除普通用户

drop user user[,user]

delete from MySQL.user where host='hostname' and user='username'

11.2.4 root用户修改自己的密码

mysqladmin -u username -h localhost -p password "new pwd"

set password =password('rootpwd')

11.2.5 root 用户修改普通用户的密码

set password for 'user'@'host' =Password('somepwd')

11.2.6 普通用户修改密码

set password =password('rootpwd')

11.2.7 root用户密码丢失的解决办法

第一步，使用–skip-grant-tables选项启动MySQL服务

mysql --skip-grant-tables;

第二步，使用root登录，重新设置密码

mysql -u root;

第三步，加载权限表

flush privileges;

11.3 权限管理

见p366

11.4 访问控制

见p372

12 数据库备份与恢复

12.1 数据备份

12.1.1 使用MySQLdump命令备份

mysqldump -u user -h host -p password dbname [tbname,...]>filename.sql

">"告诉MySQLdump将备份数据表的定义和数据写入备份文件

• 使用MySQLdump备份单个数据库中所有表

​ mysqldump -u user -h host -p password dbname>filename.sql

• 使用MySQLdump备份数据库中的某个表

  mysqldump -u user -h host -p password dbname [tbname,...]>filename.sql

12.1.2 直接复制整个数据库目录

因为MySQL表保存为文件的形式，所有可以直接复制数据库的存储目录以及文件进行备份

12.1.3 使用MySQLhotcopy工具进行快速备份

MySQLhotcopy是备份数据库或单个表最快的途径，但只能运行在数据库目录所在机器上，且只能备份MyISAM表

mysqlhotcopy dn_name_1,...,db_name_n /path/to/new_directory

12.2 数据恢复

12.2.1 使用MySQL命令恢复

mysql -u user -p [dbname] < filename.sql

如果已经登录MySQL服务器，还可以使用source导入SQL文件

source filename

12.2.2 直接复制到数据库目录

12.2.3 MySQLhotcopy快速恢复

chown -R mysql.mysql /var/lib/mysql/dbname

12.3 数据库迁移

见p389

12.4 表的导入导出

见p390