MySQL知识点总结

一、数据类型

1.1 选择优化的数据类型

更小的通常最好：更小的数据占用更少的系统资源
使用内建的类型来存储合适的内容，如使用DATETIME或TIMESTAMP存储时间，而不是VARCHAR
尽量避免使用null，因为这会降低性能
TIMESTAMP只使用DATETIME的一半空间，并且会根据时区来自动更新，当TIMESTAMP设置默认值为CURRENT_TIMESTAMP和NOW()时，TIMESTAMP的值并不会随着更改某一字段而自动更改。

1.2 数据类型需要注意的

1.2.1 数值型

INT(11)其实并没有什么意义，它并不会限制数值的合法范围。
DECIMAL类型主要用于存储精确的小数类型（如金额），因为需要额外的空间和计算开销，因此只在对小数进行精确计算时才使用DECIMAL。
浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。

1.2.2 字符串型

VARCHAR使用更少的存储空间，但索引速度低于CHAR，VARCHAR需要额外的信息来保存长度信息。
使用VARCHAR(5)和VARCHAR(20)来存储’hello’的空间开销是一样的，但VARCHAR(20)会消耗更长的列，因此我们只分配真正需要的空间。
使用枚举(ENUM)和SET代替字符串类型，在MySQL内部使用整数存储ENUM和SET类型，然后在做比较时转换为字符串。

1.2.3 日期时间型

MySQL能存储的最小时间粒度为秒。
DATETIME([fsp])``TIMESTAMP[(fsp)]``TIME[(fsp)]可设定一个0~6位的微秒时间。
DATETIME能保存大范围的值，精度为秒，可保存1001~9999年。该类型存储与时区无关，使用8个字节的存储空间。
TIMESTAMP只使用4个字节的存储空间，因此只能保存1970~2038年，MySQL中也提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数将日期转换为Unix时间戳。
MySQL 4.1及以上版本按照DATETIME的方式格式化显示TIMESTAMP的值，这只是显示上的区别，与4.1前的版本在存储上并没有区别。
TIMESTAMP默认为NOT NULL

1.2.4 位数据型

从MySQL 5.0开始，新增BIT数据类型，该类型可用于存储一个或多个true/false值，BIT(1)定义一个包含单个位的字段，BIT(2)存储2个位……BIT列的最大长度是64个位。但BIT类型应该谨慎使用，对于大部分应用，最好避免使用这种类型。

在数据表中，一旦选定了字段类型，就要确保在所有关联表中都使用同样的数据类型。

1.3 数据表设计需注意的

太多的列。过多的列（如数千个）会导致性能下降。
太多的关联。MySQL限制了每个关联操作最多只能有61张表。一个粗略的经验法则，如果希望查询执行得到快速且并发性好，单个查询最好在12个表以内做关联。
防止过度使用枚举（ENUM）。如：country enum(‘’,’0’,’1’,’2’,…,’31’)
尽量避免使用null，建议尽可能使用替代方案。即使需要存储一个事实上的‘空值’时，可以使用0，空字符串或其他特殊值来代替。但也不要走极端，当确实需要表示未知值时，使用null也比使用奇怪的值好，比如使用全0作日期时间会导致很多问题，可以配置MySQL的SQL_MODE来禁止不可能的日期。

二、常用命令

2.1 基础命令

查询数据：SELECT [DISTINCT] 字段 [AS 别名] FROM 表名 [JOIN 表名2 ON 条件] [WHERE 条件(LIKE|AND|OR|BETWEEN|IN|NOT IN|EXISTS|NOT EXISTS)] [GROUP BY 字段 [HAVING 条件]] [ORDER BY 字段 [DESC]] [LIMIT [OFFSET,]条数]；（注意使用GROUP BY时所有的SELECT的字段都要列出）

在处理SQL语句时，所有的空格都会被忽略，因此SQL语句可以换行以增强易读性。
一般而言，除非你确实需要表中的每一列，否则最好别使用*通配符。虽然使用通配符能让你省事，但检索不需要的列会降低检索和应用程序的性能。
DESC关键字只应用到直接位于其前面的列名。如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。
确定值是否为NULL时，不能简单的检查是否= NULL，应该使用IS NULL。注意NULL与''不同，NULL表示空值，而''是一个值。
IN操作符完成了与OR相同的功能。IN操作符的语法更清晰、直观。IN操作符一般比一组OR操作符执行的更快。
别名可以是一个单词或多个单词，多个单词时要在引号中，但别名最好用一个单词。表别名也可以实现自身的联表查询。
单条查询条件用WHERE，分组查询条件用HAVING，联表查询条件用ON。
联表查询时，联结的表越多，性能下降也越多。
组合查询（UNION）从查询结果集中自动去除了重复的行，使用UNION ALL，DBMS不取消重复的行。
UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL为UNION的一种形式，它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），就必须使用UNION ALL，而不是WHERE。
在某些SQL中，INSERT INTO中的INTO是可选的，但为了保证SQL的移植性，最好不要省略该关键字。
MySQL中可以进行算数运算，如常见的加、减、乘、除。
在数据表中，ID字段最好是自动生成的，以确保唯一、准确。
无论是在编写SQL还是普通代码时，应该多加注释，注释并不会影响性能，但能让自己和别人更容易理解和更安全的修改代码。
EXISTS|NOT EXISTS (从句)条件返回的是布尔值

插入数据：INSERT|REPLACE [IGNORE] INTO 表名(字段1，字段2) VALUES(值1，值2),(值1，值2) [ON DUPLICATE KEY UPDATE 字段=值];

如果某个字段是主键索引或唯一索引，普通的插入语句是会报错的，而使用关键词IGNORE则会警告，但不会影响正常的执行，重复数据仍然没有插入

更新数据：UPDATE 表名 SET 字段1=值1，字段2=值2 WHERE 条件;

在UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
如果DBMS支持禁止无WHERE的UPDATE和DELETE的执行，那最好启用它。

删除数据：DELETE FROM 表名 WHERE 条件;

DELETE删除的是行，要删除列，使用UPDATE语句。
如果想从表中删除所有行，可以使用TRUNCATE TABLE语句来代替DELETE，它完成了相同的工作，但速度更快（因为不记录数据的变动）。

2.2 数据库

创建数据库：CREATE DATABASE [IF NOT EXISTS] 数据库名;
删除数据库：DROP DATABASE [IF EXISTS] 数据库名;
查看建库语句：SHOW CREATE DATABASE 数据库名;
选择数据库：USE 数据库名;
查看数据库：SHOW DATABASES;
查看数据库信息：STATUS;
设置命令行字符集：SET NAMES UTF8;
查看数据表的最后修改时间：select TABLE_NAME,UPDATE_TIME from INFORMATION_SCHEMA.tables where TABLE_SCHEMA='数据库名';

2.3 数据表

创建数据表：CREATE TABLE [IF NOT EXISTS] 数据表名();

为每一列设置默认值，使用DEFAULT而不是NULL。

CREATE TABLE `user` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 COMMENT='用户表';

查看建表语句：SHOW CREATE TABLE 数据表名;
查看数据表：SHOW TABLES;
查看当前数据库下数据表的自增ID值：SHOW TABLE STATUS;
查看表结构：DESC 表名;或SHOW COLUMNS FROM 表名;
删除数据表：DROP TABLE [IF EXISTS] 数据表名;

-使用关系规则防止意外删除。许多DBMS允许强制实施有关规则，防止删除与其他表相关联的表。在实施这些规则时，如果对某个表发布一条DROP TABLE语句，且该表是某个关系的组成部分，则DBMS将阻止这条语句执行，直到该关系被删除为止。

修改表名：RENAME TABLE 旧表名 TO 新表名;
####2.3.1 复制表结构和数据
复制表结构：CREATE TABLE 新表名 LIKE 原表名;(与原表结构相同，建表语句相同)
**复制表结构及数据到新表：CREATE TABLE 表名 SELECT * FROM 表名;**（注意该方式与原表的结构会有差异，建表语句不同，如自增会消失）
复制数据到另一张表（结构相同）：INSERT INTO 表名 SELECT * FROM 表名;
复制数据到另一张表（结构不同）：INSERT INTO 表名(字段1，字段2) SELECT 字段1，字段2 FROM 表名;
查看字段编码：SHOW FULL COLUMNS FROM 表名;

2.4 其它

2.4.1 视图

创建视图：CREATE VIEW 视图名 AS SELECT ...;

视图是一张虚拟的表。

删除视图：DROP VIEW 视图名;

2.4.2 存储过程

创建存储过程：CREATE PROCEDURE 存储过程名();
创建函数：CREATE FUNCTION 函数名();

2.4.3 触发器

查看触发器：SHOW TRIGGERS;
删除触发器：DROP TRIGGER 触发器名

2.4.4 事务

查看事务开关状态：SHOW VARIABLES LIKE 'autocommit';
打开/关闭事务的自动提交：SET AUTOCOMMIT = 0|1;
创建临时表：CREATE TEMPORARY TABLE 表名();（临时表仅在当前连接有效，当关闭连接时，MySQL会自动删除表。临时表无法用SHOW TABLES命令查看）

2.4.5 系统信息

查看当前用户名：SELECT USER();
查看当前数据库：SELECT DATABASE();
查看MySQL版本：SELECT VERSION();

2.5 ALTER

ALTER操作有ALTER TABLE，ALTER DATABASE，ALTER EVENT，ALTER PROCEDURE，ALTER VIEW等命令

2.5.1 表的操作

使用ALTER TABLE要极为小心，应该在进行改动前做完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，也许无法删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

增加字段：ALTER TABLE 表名 ADD [COLUMN] 字段名字段类型 [FIRST|AFTER 字段名];
修改字段名及类型：ALTER TABLE 表名 CHANGE [COLUMN] 旧字段名新字段名字段类型;
修改字段类型：ALTER TABLE 表名 MODIFY 字段名类型 [NOT NULL];
删除字段：ALTER TABLE 表名 DROP [COLUMN] 字段名;
添加普通/唯一索引：ALTER TABLE 表名 ADD INDEX/UNIQUE 索引名(字段1[,字段2]);

请注意：对多个字段同时添加普通索引时，只对这些字段都进行检索时索引才会生效，仅对其中任何一个检索都不会有预期的效果。

创建短索引：CREATE INDEX 索引名 ON 表名(字段(长度)); 或KEY 索引名 (字段(长度)) 对于CHAR和VARCHAR列，只用它的一部分来创建索引，既可以节省索引空间，又可以加快索引速度。
添加主键：ALTER TABLE 表名 ADD PRIMARY KEY (字段名);

MySQL中不允许有多个主键
主键的特点：非空且唯一，按需添加自增
主键的名字就是PRIMARY

设置主键的自增起始值：ALTER TABLE 表名 AUTO_INCREMENT = 100;
添加外键：ALTER TABLE 表名 ADD [CONSTRAINT [约束名]] FOREIGN KEY (字段1[,字段2]) REFERENCES 表名(字段1[,字段2]) [ON DELETE|UPDATE CASCADE|RESTRICT];

CASCADE（级联）：如果删除父表中的数据，则子表中的相应数据也一同被删除或更新
RESTRICT（限制）：如果父表对应的子表中有相关的数据，则不允许删除或更新父表中的数据
外键约束用于子表中，而且父表与子表必须使用相同的存储引擎
当一张表中有多个外键时，可以使用约束名来进行区分，如果约束名省略，则由系统自动生成
创建外键约束的条件：
- 两张表都必须是InnoDB引擎（MyISAM不支持外键），且它们没有临时表
- 外键关联的字段必须有相似的数据类型
- 外键关联的字段必须建立了索引
外键的官方文档

删除普通/唯一索引：DROP INDEX 索引名 ON 表名;或ALTER TABLE 表名 DROP INDEX 字段名;
删除主键：ALTER TABLE 表名 DROP PRIMARY KEY;
删除外键：ALTER TABLE 表名 DROP FOREIGN KEY 约束名;
修改表名：ALTER TABLE 旧表名 RENAME TO 新表名;
修改字段默认值：ALTER TABLE 表名 ALTER 字段名 SET DEFAULT 默认值;
删除字段的默认值：ALTER TABLE 表名 ALTER 字段名 DROP DEFAULT;
修改表的字符集：ALTER TABLE 表名 DEFAULT CHARSET UTF8;

2.5.2 库的操作

修改库的字符集：ALTER TABLE 库名 DEFAULT CHARSET UTF8;

三、函数

3.1 字符串函数

拼接输出的内容：CONCAT(字段1，字段2，……)
从左开始截取字符串：LEFT(str, length)
从右开始截取字符串：RIGHT(str, length)
截取字符串：SUBSTRING(str, pos [,length] )（可截取中文）
字符串长度：LENGTH()（包含中文时返回字符集下中文所占长度）
返回字符个数：CHAR_LENGTH()
去除字符串左右的空格：TRIM()
转小写：LOWER()
转大写：UPPER()
字符串比较：STRCMP(字段1，字段2)（字段1<字段2返回-1，否则返回1，相等返回0）
SOUNDEX()是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。
加密函数：PASSWORD('加密内容')
MD5加密函数：MD5('加密内容')

3.2 数值函数

数据类型转换：CONVERT('转换内容'，目标类型)
取余数：MOD(x,y)
四舍五入：TRUNCATE(x,y)
进制转换：CONV('数值'，进制，目标进制)
格式化结果：FORMAT(数值，保留小数位数)
IP地址转数值：INET_ATON('IP地址')（Address TO Number）
数值转IP地址：INET_NTOA(数值)(Number TO Address)
- mysql用无符号整型存储IPv4

3.3 日期时间函数

获取当前日期时间：NOW()
获取当前日期：CURDATE()
获取UTC日期：UTC_DATE()
获取UTC时间：UTC_TIME()
获取季度：QUARTER('17-05-07')
获取分钟：MINUTE('10:23:21')
获取秒数：SECOND('10:23:21')

3.4 条件判断函数

IF(表达式，值1，值2)类似于三元运算，如果表达式结果为真取值1，否则取值2
IFNULL(值1，值2)优先返回非null的值，如IFNULL(8,NULL)和IFNULL(NULL,8)和IFNULL(8,7)都返回8

四、 MySQL权限

4.1 MySQL权限经验原则：

只授予满足需求的最小权限；
限制登录IP；
为每个用户设置高强度的密码；
定期清理不需要的用户，回收权限或者删除用户。

4.2 常用权限命令

创建用户：CREATE USER 'username'@'host' IDENTIFIED BY 'password'; host可为IP地址段或域名或%，%表示不限制登录IP
创建并给用户授权：
mysql> GRANT all|select,insert,update,delete ON dbname.tbname TO 'user'@'host' IDENTIFIED BY 'password' WITH GRANT OPTION;
- 例子：
  mysql> GRANT all ON *.* TO Jack@'localhost' IDENTIFIED BY "p@55w0rd" WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec)
  该命令表示：为本地登录的用户Jack赋予所有数据库与所有数据表的所有权限，并且将登录密码设置为‘p@55w0rd’，而且允许该用户将自己拥有的授予其他用户。
刷新权限：mysql> FLUSH PRIVILEGES;
查看权限：mysql> SHOW GRANTS [FOR 'jack'@'%'];
撤销权限：mysql> REVOKE delete ON *.* FROM 'jack'@'loaclhost';
删除用户：mysql> DROP USER 'jack'@'localhost';
账户重命名：mysql> RENAME USER 'jack'@'%' TO 'rose'@'%';
修改自己密码：mysql> SET PASSWORD='密码';
修改其他用户密码：mysql> SET PASSWORD FOR 'jack'@'localhost'=PASSWORD('123456');
注：在MySQL 5.7及以上版本，mysql.user的密码字段由password更换为了authentication_string

五、存储过程

查看所有存储过程：select name from mysql.proc;或select routine_name from information_schema.routines;
查看某个数据库下的存储过程：select name from mysql.proc where db='数据库名';或select routine_name from information_schema.routines where routine_schema='数据库名';
查看存储过程的详细内容：SHOW CREATE PROCEDURE 数据库.存储过程名;
修改存储过程：ALTER PROCEDURE 名称 [特性……]
- MySQL的存储过程只能修改其特性，不能修改过程体，若要修改过程体，只能删除后重新创建。
删除存储过程：DROP PROCEDURE [数据库.]存储过程名

-- 设置读取test表中的数据的存储过程
DROP PROCEDURE IF EXISTS sxy;
DELIMITER //
CREATE PROCEDURE sxy(IN var INT)
BEGIN
	SELECT * FROM `test` WHERE `id` = var;
END//
DELIMITER ;

-- 调用存储过程
CALL sxy(2);

六、触发器

触发器经常用于加强数据的完整性约束和业务规则等。
一般来说，约束的处理速度比触发器快，因此应该尽量使用约束。
在MySQL中，创建触发器的语法如下：

CREATE TRIGGER 触发器名称 触发时机(BEFORE|AFTER) 触发事件(INSERT|UPDATE|DELETE)  
  ON 表名 FOR EACH ROW
  BEGIN
    触发器程序体(SQL语句)
  END

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。
查看触发器

通过show语句查看触发器
SHOW TRIGGERS;

通过系统表查看触发器

1
2
3

use information_shcema;
select * from tirggers;
select * from triggers where TRIGGER_NAME = '触发器名称';

触发器中的new和old

针对update操作，new表示的是更新后的值，old表示的是更新前的数据。
针对insert操作，new表示的是新增的值。
针对delete操作，old表示的是被删除的值。

示例1：

-- 插入触发器
mysql> delimiter $$
mysql> create trigger tab1_after_insert after insert
    -> on tab1 for each row
    -> BEGIN
    ->   insert into tab2(tab2_id) values(new.tab1_id);
    -> END
    -> $$
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;

示例2

-- 删除触发器
mysql> delimiter $$
mysql> create trigger tab1_delete_after after delete
    -> on tab1 for each rows
    -> BEGIN
    ->   delete from tab2 where tab2_id = old.tab1_id;
    -> END
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

示例3：

-- 更新学生表的同时也更新记录表
delimiter $$
mysql> create trigger student1_update_after after update
    -> on student1 for each row
    -> BEGIN
    ->  if new.student_id != old.student_id then
    ->   update update_student1 set student_id = new.student_id, update_date = now()
    ->   where student_id = old.student_id;
    ->  end if;
    -> END
    -> $$
delimiter ;

示例4

-- 删除学生表的时候也删除记录表
mysql> delimiter $$
mysql> create trigger student1_delete_before before delete
    -> on student1 for each row
    -> BEGIN
    ->  delete from update_student1
    ->  where student_id = old.student_id;
    -> END
    -> $$
delimiter ;

七、事务

事务控制语句：

BEGIN或START TRANSACTION；显示地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier；把事务回滚到标记点；

SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

BEGIIN;-- 开启事务
	SQL语句1;
	SAVEPOINT sql1;
	IF(SQL语句1执行失败)
	  ROLLBACK;-- 撤销所有执行
	SQL语句2;
	SAVEPOINT sql2;
	IF(SQL语句2执行失败)
	  ROLLBACK TO sql1;-- 撤销部分执行
	RELEASE SAVEPOINT sql1;-- 删除保存点
		BEGIN;-- 嵌套事务
			SQL语句3;
			IF(SQL语句3执行失败)
				ROLLBACK TO sql2;
		COMMIT;  
COMMIT;-- 提交事务

参考文章：说说MySQL中的事务

八、 MySQL中的日志

共4种：

错误日志：记录mysql服务的启动、运行、停止时出现的问题
查询日志（通用日志）：记录用户的所有操作
二进制日志：记录所有更改数据的语句，可以用于数据复制
慢查询日志：记录所有执行时间超过long_query_time的所有查询。官方的慢查询阈值只能设置大于1秒的，使用microslow-patch可以查询毫秒级的。

除了错误日志默认开启外，其他日志都是默认关闭的。但日志既会影响mysql的性能，又会占用大量磁盘空间。因此，如果不必要，应尽可能少地开启日志。根据不同的使用环境，考虑开启不同的日志。例如开发环境中优化查询效率低的语句，可以开启慢查询日志，或者生产环境中发现某些SQL执行特别慢也可以开启。如果磁盘空间不是特充足可以在高峰期间开启，在捕获到查询慢的SQL之后再关闭慢查询日志。如果需要搭建复制环境，那么就一定要开启二进制日志，如果数据特别重要也建议开启二进制日志，以便数据库损坏的时候也可以通过二进制日志挽救一部分数据。通用日志无论在哪种情况下，一般不建议开启。

常用命令

查看日志信息：show variables like '%log_%';
查看错误日志的存储位置：show variables LIKE 'log_error';
删除错误日志：flush logs;
- MySQL 5.5.7版本前，flush logs可以将错误日志文件重命名为filename.err_old，并创建新的日志文件。但是从MySQL 5.5.7开始，flush logs只是重新打开日志文件，并不做日志备份和创建的操作。如果日志文件不存在，mysql启动或者执行flush logs时会创建新的日志文件

Mac使用HomeBrew安装的MySQL是没有my.cnf文件的，需要自己手动创建，具体信息请查看另一个笔记：在 Mac 下用 Homebrew 安装 MySQL配置文件my.cnf问题

九、PHP连接MySQL的方式

当考虑连接到MySQL数据库服务器的时候，有三种主要的API可供选择：

PHP的MySQL扩展
PHP的MySQLi扩展
PHP数据对象（PDO）

三者各有优缺点：

mysql扩展提供了一个面向过程的接口，并且是针对MySQL4.1.3或更早版本设计的。因此，这个扩展虽然可以与MySQL4.1.3或更新的数据库服务端进行交互，但并不支持后期MySQL服务端提供的一些特性。
MySQLi扩展可以用于使用 MySQL4.1.3或更新版本中新的高级特性。其主要优势有：

面向对象接口
prepared语句支持
多语句执行支持
事务支持
增强的调试能力
嵌入式服务支持

PDO提供了一个统一的API接口可以使得你的PHP应用不去关心具体要连接的数据库服务器系统类型。也就是说，如果你使用PDO的API，可以在任何需要的时候无缝切换数据库服务器，比如从Firebird 到MySQL，仅仅需要修改很少的PHP代码。
PDO的优点在于，它是一个干净的，简单的，可移植的API，它最主要的缺点是会限制让你不能使用后期MySQL服务端提供所有的数据库高级特性。比如，PDO不允许使用MySQL支持的多语句执行。

十、SQL注入

永远不要信任外界输入的数据，特别是来自于客户端的，包括选择框、表单隐藏域和 cookie。

永远不要使用超级用户或所有者帐号去连接数据库。要用权限被严格限制的帐号。
检查输入的数据是否具有所期望的数据格式。
使用数据库特定的敏感字符转义函数（比如 mysql_escape_string() 和 sql_escape_string()）把用户提交上来的非数字数据进行转义。如果数据库没有专门的敏感字符转义功能的话 addslashes() 和 str_replace() 可以代替完成这个工作。
尽量避免显示有关数据库的任何信息，尤其是数据库结构。
可以选择使用数据库的存储过程和预定义指针等特性来抽象数库访问，使用户不能直接访问数据表和视图。但这个办法又有别的影响。

mysqli没有提供一个特殊的方法用于打开持久化连接。需要打开一个持久化连接时，你必须在连接时在主机名前增加p:。

数据的导出及导入：
Linux下操作：
导出：mysqldump -h -u -p 数据库名1 [数据库名2]>本地地址
导入：mysql -h -u -p 数据库名<本地地址

十一、其他

11.1 避免重复插入记录

1、使用IGNORE关键字。对于PRIMARY KEY或UNIQUE的字段来说，如果数据不重复则插入，重复则忽略（报警告，数据不会插入，不影响正常执行，无此关键字会报错误）。如：INSERT IGNORE INTO test(id,name) VALUES(1,'jack');
2、使用REPLACE关键字。该SQL与IGNORE的区别在于，如果数据重复，则先将旧的数据删除，然后插入新的。所以，执行该语句的影响行可以看出是否有重复的数据（为1表示无重复，大于1为重复）。
3、使用ON DUPLICATE KEY UPDATE，该句子是在有重复数据的时候，用新数据替换旧数据。

11.2 统计重复数据

比如统计重名的学生数量：SELECT COUNT(name) AS deplication,name FROM student_info GROUP BY name HAVING deplication > 1;

11.3 数据导出

需有FILE权限。

方法一使用OUTFILE

SELECT 字段 FROM 表名 [WHERE等过滤条件] INTO OUTFILE '保存路径' [CHARACTER SET GBK] [FIELDS TERMINATED BY ',' [[ENCLOSED BY '"'] LINES TERMINATED BY '\n']];

注意：由于xls和xlsx的中文默认gbk编码，因此如果数据表编码不一致，需在导出时使用CHARACTER SET GBK指定导出编码；在使用csv（Comma-Separated Values, 逗号分隔值）格式时，需使用FIELDS TERMINATED BY ','指定字段分隔，否则会出现格式错误。

方法二使用mysqldump命令

$mysqldump -u user -p pwd 数据库名 [表名] > 文件;导出指定数据库或指定数据表
$mysqldump -u user -p pwd --all-databases > 文件;备份所有数据库

11.4 数据导入

需有FILE权限。

方法一使用LOAD DATA

LOAD DATA [LOCAL] INFILE '文件' INTO TABLE 表名 [CHARACTER SET GBK] [FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'];

注意数据表的编码，即使同为GBK编码，也会出现报错的情况，因此最好在导入前确定数据表与文件的编码，然后指定编码导入。
无论在导入还是导出csv格式文件时，都需要使用FIELDS TERMINATED BY ','
如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

方法二使用mysqldump命令

$mysqldump -u user -p pwd 数据库名 [表名] < 文件;

方法三使用mysqlimport

$mysqlimport -u user -p [--local] [--fields-terminated-by=":" --lines-terminated-by="\r\n" --columns=字段1，字段2] 数据库名文件名

更多数据导入/导出信息请点击：如何将 JSON, Text, XML, CSV 数据文件导入 MySQL

11.5 索引的性能

索引改善检索操作的性能，但降低数据插入、修改和删除的性能。因为在执行这些操作时，MySQL必须动态地更新索引。
索引数据可能要占用大量的存储空间。
数据量少的字段不适合做索引。并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓名），能通过索引得到那么多的好处。
用于排序的字段适合添加索引。索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
可以在索引中定义多个列（如，州和城市）。这样的索引仅在以州+城市的排序时有用，如果以城市排序，则该索引并没什么用处。
索引的效率随表数据的增加或改变而变化。许多DBA发现，过去创建的某个理想的索引经过几个月的数据处理后可能变得不再理想了。最好定期检查索引，并根据需要对索引进行调整。

11.6 一些术语释义

DDL(Data Definition Language)：数据定义语言
DML(Data Manipulation Language)：数据操作语言
DRL(Data Retrieval Language)：数据查询语言

更多术语解释：MySQL Glossary

一、 数据类型

1.1 选择优化的数据类型

1.2 数据类型需要注意的

1.2.1 数值型

1.2.2 字符串型

1.2.3 日期时间型

1.2.4 位数据型

1.3 数据表设计需注意的

二、常用命令

2.1 基础命令

2.2 数据库

2.3 数据表

2.4 其它

2.4.1 视图

2.4.2 存储过程

2.4.3 触发器

2.4.4 事务

2.4.5 系统信息

2.5 ALTER

2.5.1 表的操作

2.5.2 库的操作

三、函数

3.1 字符串函数

3.2 数值函数

3.3 日期时间函数

3.4 条件判断函数

四、 MySQL权限

4.1 MySQL权限经验原则：

4.2 常用权限命令

五、存储过程

六、触发器

七、事务

八、 MySQL中的日志

九、PHP连接MySQL的方式

十、SQL注入

十一、其他

11.1 避免重复插入记录

11.2 统计重复数据

11.3 数据导出

方法一 使用OUTFILE

方法二 使用mysqldump命令

11.4 数据导入

方法一 使用LOAD DATA

方法二 使用mysqldump命令

方法三 使用mysqlimport

11.5 索引的性能

11.6 一些术语释义

一、数据类型

方法一使用OUTFILE

方法二使用mysqldump命令

方法一使用LOAD DATA

方法二使用mysqldump命令

方法三使用mysqlimport