MySQL必知必会 | Growth Log

type

status

date

slug

summary

数据类型

目的

数据类型允许限制可存储在列中的数据。例如，数值数据类型列只能接受数值。

数据类型允许在内部更有效地存储数据。可以用一种比文本串更简洁的格式存储数值和日期时间值。

数据类型允许变换排序顺序。如果所有数据都作为串处理，则1位于10之前，而10又位于2之前（串以字典顺序排序，从左边开始比较，一次一个字符）。作为数值数据类型，数值才能正确排序。

串数据

定长串接受长度固定的字符串，其长度是在创建表时指定的。例如，名字列可允许30个字符，而社会安全号列允许11个字符（允许的字符数目中包括两个破折号）。定长列不允许多于指定的字符数目。它们分配的存储空间与指定的一样多。因此，如果串Ben存储到30个字符的名字字段，则存储的是30个字符，CHAR属于定长串类型。变长串存储可变长度的文本。有些变长数据类型具有最大的定长，而有些则是完全变长的。不管是哪种，只有指定的数据得到保存（额外的数据不保存）TEXT属于变长串类型。

既然变长数据类型这样灵活，为什么还要使用定长数据类型？回答是因为性能。MySQL处理定长列远比处理变长列快得多。此外，MySQL不允许对变长列（或一个列的可变部分）进行索引。这也会极大地影响性能。

数据类型	说明
CHAR	1～255个字符的定长串它的长度必须在创建时指定，否则MySQL假定为CHAR(1)
ENUM	接受最多64K个串组成的一个预定义集合的某个串
LONGTEXT	与TEXT相同，但最大长度为4GB
MEDIUMTEXT	与TEXT相同，但最大长度为16K
SET	接受最多64个串组成的一个预定义集合的零个或多个串
TEXT	最大长度为64K的变长文本
TINYTEXT	与TEXT相同，但最大长度为255字节
VARCHAR	长度可变，最多不超过255字节。如果在创建时指定为VARCHAR(n),则可储存0到n个字符长度n<=255

日期时间数据

数据结构	说明
DATE	表示1000-01-01～9999-12-31的日期格式为YYYY-MM-DD
DATETIME	DATE和TIME的组合
TIMESTAMP	功能和DATETIME相同（但范围较小）
TIME	格式为HH:MM:SS
YEAR	用2位数字表示范围是70（1970年）~69(2069年),用4位数字表示是1901～2155年

数值数据

数据类型	说明
BIT	位字段 1～64位
BIGINT	整数值 -9223372036854775808～9223372036854775807，UNSIGNED 0～18446744073709551615
BOOLEAN	布尔标志 0/1
DECIMAL	精度可变的浮点值
DOUBLE	双精度浮点值
FLOAT	单精度浮点值
INT	整数值 -2147483648～2147483647，UNSIGNED 0～4294967295
MEDIUMINT	整数值支持-8388608～8388607，UNSIGNED 0～16777215
REAL	4字节浮点值
SMALLINT	整数值支持-32768～32767，UNSIGNED 0～65535
TINYINT	整数值支持-128～127， UNSIGNED 0～255

二进制数据

数据类型	说明
BLOB	Blob最大长度为64KB
MEDIUMBLOB	Blob最大长度为16MB
LONGBLOB	Blob最大长度为4GB
TINYBLOB	Blob最大长度为255字节

检索数据

排序检索数据

过滤数据

NULL与不匹配

在通过过滤选择出不具有特定值的行时，你可能希望返回具有NULL值的行。但是，不行。因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行。

where子语句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定两个值之间

数据过滤

为什么要使用IN操作符？

在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。
在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。
IN操作符一般比OR操作符清单执行更快。
IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。
MySQL支持使用NOT 对IN 、BETWEEN 和EXISTS子句取反。

通配符过滤

通配符

通配符	说明
`%`	替代 0 个或多个字符
`_`	替代一个字符

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

在确实需要使用通配符时，除非绝对有必要，否则不要把它们用

在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据

使用正则表达式过滤数据

正则表达式

符号	说明
`^`	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
`$`	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
`.`	匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。
`[...]`	字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
`[^...]`	负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
`p1	p2
`*`	匹配前面的子表达式零次或多次。
`+`	匹配前面的子表达式一次或多次。
`{n}`	n 是一个非负整数。匹配确定的 n 次。
`{n,m}`	m 和 n 均为非负整数，其中n <= m。

创建计算字段

Concat()拼接串，即把多个串连接起来形成一个较长的串。

Concat()需要一个或多个指定的串，各个串之间用逗号分隔。

操作符	说明
`+`	加
`-`	减
`*`	乘
`/`	除

数据处理函数

文本处理函数

常用的文本处理函数

函数	说明
`Left()`	返回串左边的字符
`Length()`	返回字符串的长度
`Locate()`	找出串的一个子串
`Lower()`	将串转换为小写
`LTrim()`	去掉串左边的空格
`Right()`	返回串右边的字符
`RTrim()`	去掉串右边的空格
`Soundex()`	返回SOUNDEX值
`SubString()`	返回子串的字符
`Upper()`	将串转换为大写

日期时间处理函数

常用的日期时间处理函数

函数	说明
`AddDate()`	增加一个日期（天、周等）
`AddTime()`	增加一个时间（时、分等）
`CurDate()`	返回当前日期
`CurTime()`	返回当前时间
`Date()`	返回日期时间的日期部分
`DateDiff()`	计算两个日期之差
`Date_Add()`	高度灵活的日期运算函数
`Date_Format()`	返回一个格式化的日期或时间串
`Day()`	返回一个日期的天数部分
`DayOfWeek()`	对于一个日期，返回对应的星期几
`Hour()`	返回一个时间的小时部分
`Minute()`	返回一个时间的分钟部分
`Month()`	返回一个日期的月份部分
`Now()`	返回当前日期和时间
`Second()`	返回一个时间的秒部分
`Time()`	返回一日期时间的时间部分
`Yesr()`	返回一个日期的年份部分

日期必须为格式yyyy-mm-dd。因此，2005年9月1日，给出为2005-09-01

数值处理函数

常用的数值处理函数

函数	说明
`Abs()`	返回一个树的绝对值
`Cos()`	返回一个角度的余弦
`Exp()`	返回一个数的指数值
`Mod()`	返回除操作的余数
`Pi()`	返回圆周率
`Rand()`	返回一个随机数
`Sin()`	返回一个角度的正弦
`Sqrt()`	返回一个数的平方根
`Tan()`	返回一个角度的正切

汇总数据

聚集函数

函数	说明
`AVG()`	返回某列的平均值
`COUNT()`	返回某列的行数
`MAX()`	返回某列的最大值
`MIN()`	返回某列的最小值
`SUM()`	返回某列值之和

基本使用

聚集不同值

如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

组合聚集

数据分组

group by 子句规则

GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。

过滤分组

HAVING支持所有WHERE操作符

WHERE子句的条件（包括通配符条件和带多个操作符的子句）。所学过的有关WHERE的所有这些技术和选项都适用于HAVING。它们的句法是相同的，唯一的差别是WHERE过滤行，而HAVING过滤分组。

分组排序

不要忘记ORDER BY 一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

SELECT语句顺序

子句	说明	是否必须使用
`select`	要返回的列或表达式	是
`from`	从中检索数据的表	仅在从表选择数据时使用
`where`	行级过滤	否
`group by`	分组说明	仅在按组计算聚集时使用
`having`	组级过滤	否
`order by`	输出排序顺序	否
`limit`	要检索的行数	否

子查询

先分析问题将问题拆解成单条SELECT语句根据需求进行合并

使用子查询过滤数据

作为计算字段使用子查询

连接表

内连接

内连接又称等值连接

使用inner join关键字

使用where做等值连接

自联结

其实就是同一张表自己连接自己

自然连接

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。

内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

外部连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

左外连接（left join / left outer join）: 满足on条件表达式，左外连接是以左表为准，返回左表所有的数据，与右表匹配的则有值，没有匹配的则以空（null）取代。

右外连接（right join / right outer join）：满足on条件表达式，右外连接是以右表为准，返回右表所有的数据，与左表匹配的则有值，没有匹配的则以空（null）取代。

全外连接（full join / full outer join）：满足on条件表达式，返回两个表符合条件的所有行，a表没有匹配的则a表的列返回null，b表没有匹配的则b表的列返回null，即返回的是左连接和右连接的并集。

交叉连接（cross join）：交叉连接将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。

组合查询

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。

默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中。

使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。

视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变。

视图具有如下好处：

简化复杂的 SQL 操作，比如复杂的连接；

只使用实际表的一部分数据；

通过只给用户访问视图的权限，保证数据的安全性；

更改数据格式和表示。

视图与表的区别

表要占用磁盘空间，视图不需要

视图不能添加索引

使用视图可以简化查询

视图可以提高安全性

储存过程

存储过程可以看成是对一系列 SQL 操作的批处理。

使用存储过程的好处：

代码封装，保证了一定的安全性；

代码复用；

由于是预先编译，因此具有很高的性能。

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量赋值都需要用 select into 语句。

每次只能给一个变量赋值，不支持集合的操作。

优点

由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。

简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。

提高性能。因为使用存储过程比使用单独的SQL语句要快。

存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码

游标

在存储过程中使用游标可以对一个结果集进行移动遍历。游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

声明游标，这个过程没有实际检索出数据；

打开游标；

取出数据；

关闭游标；

触发器

基本术语：

事务（transaction）指一组 SQL 语句；

回退（rollback）指撤销指定 SQL 语句的过程；

提交（commit）指将未存储的 SQL 语句结果写入数据库表；

保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

创建表

修改表

添加列

删除列

删除表

插入

普通插入

插入检索出来的数据

将一个表的内容插入到一个新表

更新

删除

清空表数据保留表结构

使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。

权限管理

MySQL 的账户信息保存在 mysql 这个数据库中。

创建账户

新创建的账户没有任何权限。

修改账户名

删除账户

查看权限

授予权限

账户用 username@host 的形式定义，username@% 使用的是默认主机名。

删除权限

GRANT 和 REVOKE 可在几个层次上控制访问权限：

整个服务器，使用 GRANT ALL 和 REVOKE ALL；

整个数据库，使用 ON database.*；

特定的表，使用 ON database.table；

特定的列；

特定的存储过程。

更改密码

必须使用 Password() 函数进行加密。