MySQL教程

转载自： W3Cschool MySQL 教程

验证MySQL安装
[root@host]# mysqladmin --version

创建密码
MySQL安装成功后，默认的root用户密码为空，你可以使用以下命令来创建root用户的密码：
[root@host]# mysqladmin -u root password "new_password";

链接MySQL服务器与退出
现在你可以通过以下命令来连接到MySQL服务器：
[root@host]# mysql -u root -p
Enter password:*******
退出 mysql> 命令提示窗口可以使用 exit 命令，如下所示：

mysql> exit
Bye

跟随Linux启动，启动MySQL服务器
如果你需要在Linux系统启动时启动 MySQL 服务器，你需要在 /etc/rc.local 文件中添加以下命令：
/etc/init.d/mysqld start

需要注意的是 MySQL 的SQL语句以分号 ; 作为结束标识。

管理MySQL的命令

USE 数据库名						->->->-> 选择要操作的MySQL数据库，使用该命令后所有MySQL命令都只针对该数据库。
SHOW DATABASES					->->->->  列出 MySQL 数据库管理系统的数据库列表。
SHOW TABLES						->->->->  显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。
SHOW COLUMNS FROM 数据表			->->->->  显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
SHOW INDEX FROM 数据表			->->->->  显示数据表的详细索引信息，包括PRIMARY KEY（主键）。
SHOW TABLE STATUS LIKE 数据表\G	->->->->  该命令将输出MySQL数据库管理系统的性能及统计信息。

添加用户操作(GRANT命令)：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';

添加用户的操作建议采用GRANT命令
参考：mysql如何添加用户及1364错误处理

创建与删除数据库
以下命令简单的演示了创建数据库的过程，数据名为 W3CSCHOOL:

[root@host]# mysqladmin -u root -p create W3CSCHOOL
Enter password:******

以下实例删除数据库TUTORIALS(该数据库在前一章节已创建)：

[root@host]# mysqladmin -u root -p drop W3CSCHOOL
Enter password:******

创建与删除数据表
通过 mysql> 命令窗口可以很简单的创建MySQL数据表。你可以使用 SQL 语句 CREATE TABLE 来创建数据表。

以下为创建数据表 w3cschool_tbl 实例:

root@host# mysql -u root -p
Enter password:*******
mysql> use W3CSCHOOL;
Database changed
mysql> CREATE TABLE w3cschool_tbl(
   -> w3cschool_id INT NOT NULL AUTO_INCREMENT,
   -> w3cschool_title VARCHAR(100) NOT NULL,
   -> w3cschool_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( w3cschool_id )
   -> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.16 sec)
mysql>

在mysql>命令提示窗口中删除数据表SQL语句为 DROP TABLE ：

以下实例删除了数据表w3cschool_tbl:

root@host# mysql -u root -p
Enter password:*******
mysql> use W3CSCHOOL;
Database changed
mysql> DROP TABLE w3cschool_tbl
Query OK, 0 rows affected (0.8 sec)
mysql>

插入、查询数据

以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

如果数据是字符型，必须使用单引号或者双引号，如：“value”。

以下为在MySQL数据库中查询数据通用的 SELECT 语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]

WHERE子句

以下是SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
你可以在WHERE子句中指定任何条件。
你可以使用AND或者OR指定一个或多个条件。
WHERE子句也可以运用于SQL的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的if条件，根据 MySQL 表中的字段值来读取指定的数据。

UPDATE子句与DELETE语句

以下是 UPDATE 命令修改 MySQL 数据表数据的通用SQL语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。

以下是SQL DELETE 语句从MySQL数据表中删除数据的通用语法：
DELETE FROM table_name [WHERE Clause]
如果没有指定 WHERE 子句，MySQL表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。

LIKE子句

SQL LIKE 子句中使用百分号(%)字符来表示任意字符，类似于UNIX或正则表达式中的星号 (*)。
如果没有使用百分号(%), LIKE 子句与等号（=）的效果是一样的。
以下是SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

你可以在WHERE子句中指定任何条件。
你可以在WHERE子句中使用LIKE子句。
你可以使用LIKE子句代替等号(=)。
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
你可以使用AND或者OR指定一个或多个条件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

排序与分组

以下是SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升排列。
你可以添加 WHERE…LIKE 子句来设置条件。

MySQL GROUP BY 语句
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

GROUP BY 语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

使用 WITH ROLLUP
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)

其中记录 NULL 表示所有人的登录次数。

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：
select coalesce(a,b,c);
参数说明：如果anull,则选择b；如果bnull,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

以下实例中如果名字为空我们使用总数代替：

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)

MySQL链接

你可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）->->->	获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）	         ->->->	获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）	         ->->->	 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

MySQL NULL 值处理

MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

为了处理这种情况，MySQL提供了三大运算符:

IS NULL: 当列的值是NULL,此运算符返回true。
IS NOT NULL: 当列的值不为NULL, 运算符返回true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。

MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
事务用来管理insert,update,delete语句
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

1、事务的原子性：一组事务，要么成功；要么撤回。
2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候把事务保存到日志里。

在MySQL控制台使用事务来操作
1，开始一个事务
start transaction
2, 做保存点
savepoint 保存点名称
3, 操作
4，可以回滚，可以提交，没有问题，就提交，有问题就回滚。

MySQL ALTER命令

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

查看字段详细信息：
mysql> SHOW COLUMNS FROM testalter_tbl;
查看数据表详细信息：
DESC 表名;

删除，添加或修改表字段

mysql> ALTER TABLE testalter_tbl  DROP i;

mysql> ALTER TABLE testalter_tbl ADD i INT;
//如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

//把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

//使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段的类型及名称。尝试如下实例：
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

ALTER TABLE 对 Null 值和默认值的影响
当你修改字段时，你可以指定是否包含只或者是否设置默认值。

//以下实例，指定字段 j 为 NOT NULL 且默认值为100 。
mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

修改字段默认值

//使用 ALTER 来修改字段的默认值，尝试以下实例：
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

//使用 ALTER 命令及 DROP子句来删除字段的默认值，如下实例：
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

//修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成。尝试以下实例，我们将表 testalter_tbl 的类型修改为 MYISAM ：
mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;

//查看数据表类型可以使用 SHOW TABLE STATUS 语句。
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G

修改表名
如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。

//尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl：
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

MySQL索引

索引分单列索引和组合索引
单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引，即一个索包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

普通索引

创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构
ALTER mytable ADD INDEX [indexName] ON (username(length))
创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);  

删除索引的语法
DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);  

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

以下实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

此处在Ubuntu16.04版本的终端控制器下运行删除索引，需要去掉索引外的括号

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除指定时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

尝试以下实例:

mysql> SHOW INDEX FROM table_name\G
........

MySQL 临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。

MySQL临时表只在当前连接可见，如果你使用PHP脚本来创建MySQL临时表，那每当PHP脚本执行完成后，该临时表也会自动销毁。

如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);

MySQL 复制表

复制MySQL数据表，步骤如下：

使用SHOW CREATE TABLE命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。

步骤一：

获取数据表的完整结构。

mysql> SHOW CREATE TABLE w3cschool_tbl \G;
*************************** 1. row ***************************
       Table: w3cschool_tbl
Create Table: CREATE TABLE `w3cschool_tbl` (
  `w3cschool_id` int(11) NOT NULL auto_increment,
  `w3cschool_title` varchar(100) NOT NULL default '',
  `w3cschool_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`w3cschool_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`w3cschool_author`)
) ENGINE=InnoDB
1 row in set (0.00 sec)

ERROR:
No query specified

步骤二：

修改SQL语句的数据表名，并执行SQL语句。

mysql> CREATE TABLE `clone_tbl` (
  -> `w3cschool_id` int(11) NOT NULL auto_increment,
  -> `w3cschool_title` varchar(100) NOT NULL default '',
  -> `w3cschool_author` varchar(40) NOT NULL default '',
  -> `submission_date` date default NULL,
  -> PRIMARY KEY  (`w3cschool_id`),
  -> UNIQUE KEY `AUTHOR_INDEX` (`w3cschool_author`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.80 sec)

步骤三：

执行完第二步骤后，你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO… SELECT 语句来实现。

mysql> INSERT INTO clone_tbl (w3cschool_id,
    ->                        w3cschool_title,
    ->                        w3cschool_author,
    ->                        submission_date)
    -> SELECT w3cschool_id,w3cschool_title,
    ->        w3cschool_author,submission_date
    -> FROM w3cschool_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

执行以上步骤后，你将完整的复制表，包括表结构及表数据。

MySQL 元数据

你可能想知道MySQL以下三种信息：
1.查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
2.数据库和数据表的信息： 包含了数据库及数据表的结构信息。
3.MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。

SELECT VERSION( )	->->->->	服务器版本信息
SELECT DATABASE( )	->->->->	当前数据库名 (或者返回空)
SELECT USER( )		->->->->	当前用户名
SHOW STATUS			->->->->	服务器状态
SHOW VARIABLES		->->->->	服务器配置变量

MySQL 序列使用

MySQL序列是一组整数：1, 2, 3, …，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。

使用AUTO_INCREMENT
MySQL中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。

重置序列

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

设置序列的开始值

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);

//或者可以在表创建成功后，通过以下语句来实现：

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

MySQL 处理重复数据

防止表中出现重复数据

可以在MySQL数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。

INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

NSERT IGNORE INTO当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而REPLACE INTO如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。

查询重复记录

select user_name,count(*) as count from user_table group by user_name having count>1;

统计重复数据

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下，查询重复的值，请执行以下操作：
确定哪一列包含的值可能会重复。
在列选择列表使用COUNT(*)列出的那些列。
在GROUP BY子句中列出的列。
HAVING子句设置重复数大于1。

过滤重复数据

如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl
    -> ORDER BY last_name;

你也可以使用 GROUP BY 来读取数据表中不重复的数据：

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

删除重复数据

果你想删除数据表中的重复数据，你可以使用以下的SQL语句：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
    ->                  FROM person_tbl;
    ->                  GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

当然你也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

MySQL及SQL注入

所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

在PHP中的 mysql_query() 是不允许执行多个SQL语句的，但是在 SQLite 和 PostgreSQL 是可以同时执行多条SQL语句的，所以我们对这些用户的数据需要进行严格的验证。

防止SQL注入，我们需要注意以下几个要点：

1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

防止SQL注入

PHP的MySQL扩展提供了mysql_real_escape_string()函数来转义特殊的输入字符。

Like语句中的注入

like查询时，如果用户输入的值有"“和”%"，则会出现这种情况：用户本来只是想查询"abcd"，查询结果中却有"abcd_"、“abcde”、“abcdf"等等；用户要查询"30%”（注：百分之三十）时也会出现问题。

在PHP脚本中我们可以使用addcslashes()函数来处理以上情况，如下实例：

$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");
// $sub == \%something\_
mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

addcslashes() 函数在指定的字符前添加反斜杠。

语法格式:

addcslashes(string,characters)

MySQL 导出数据

MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。

SELECT … INTO OUTFILE 语句有以下属性:

1.LOAD DATA INFILE是SELECT … INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT … INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。
2.SELECT…INTO OUTFILE 'file_name’形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。
3.输出不能是一个已存在的文件。防止文件数据被篡改。
4.你需要有一个登陆服务器的账号来检索文件。否则 SELECT … INTO OUTFILE 不会起任何作用。
5.在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。

导出表作为原始数据

mysqldump是MySQL用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。

使用mysqldump导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。
以下实例将数据表 tutorials_tbl 导出到 /tmp 目录中：

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp W3CSCHOOL w3cschool_tbl
password ******

导出SQL格式的数据
导出SQL格式的数据到指定文件，如下所示：

$ mysqldump -u root -p W3CSCHOOL w3cschool_tbl > dump.txt
password ******

如果你需要导出整个数据库的数据，可以使用以下命令：

$ mysqldump -u root -p W3CSCHOOL > database_dump.txt
password ******

如果需要备份所有数据库，可以使用以下命令：

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

将数据表及数据库拷贝至其他主机

如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。

在源主机上执行以下命令，将数据备份至 dump.txt 文件中:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

如果完整备份数据库，则无需使用特定的表名称。

如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：

$ mysql -u root -p database_name < dump.txt password ***** 

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

MySQL导入数据

使用 LOAD DATA 导入数据
MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。

两个命令的 FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。

如果用户指定一个 FIELDS 子句，它的子句 （TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。

如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
    -> INTO TABLE mytbl (b, c, a);

使用 mysqlimport 导入数据

mysqlimport客户端提供了LOAD DATA INFILEQL语句的一个命令行接口。mysqlimport的大多数选项直接对应LOAD DATA INFILE子句。

从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：

$ mysqlimport -u root -p --local database_name dump.txt
password *****

mysqlimport命令可以指定选项来设置指定格式,命令语句格式如下：

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  database_name dump.txt
password *****

mysqlimport 语句中使用 --columns 选项来设置列的顺序：

$ mysqlimport -u root -p --local --columns=b,c,a \
    database_name dump.txt
password *****

MySQL函数

//DATE_ADD() 函数向日期添加指定的时间间隔。
DATE_ADD(date,INTERVAL expr type)

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate FROM Orders

//DATE_SUB() 函数从日期减去指定的时间间隔。
DATE_SUB(date,INTERVAL expr type)

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 5 DAY) AS SubtractDate FROM Orders

//DATEDIFF() 函数返回两个日期之间的天数。
DATEDIFF(date1,date2)

SELECT DATEDIFF('2008-11-30','2008-11-29') AS DiffDate

//DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。 
DATE_FORMAT(date,format)

select DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')

//NOW() 返回当前的日期和时间。
NOW()

//CURDATE() 返回当前的日期。
CURDATE()

//CURTIME() 返回当前的时间。
CURTIME()

SELECT NOW(),CURDATE(),CURTIME()

//DATE() 函数提取日期或日期/时间表达式的日期部分。
DATE(date)

SELECT ProductName, DATE(OrderDate) AS OrderDate FROM Orders WHERE OrderId=1

//EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
EXTRACT(unit FROM date)

SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay,
FROM Orders
WHERE OrderId=1 


//CONCAT()返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
CONCAT(str1,str2,…)

//如果所有参数均为非二进制字符串，则结果为非二进制字符串。 
//如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。
//一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如：

SELECT CONCAT(CAST(int_col AS CHAR), char_col)


//CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
CONCAT_WS(separator,str1,str2,...)

//如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

select concat_ws(',','11','22',NULL);


//group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

select id,group_concat(name order by name desc) from aa group by id;

//repeat()函数用来复制字符串,如下'ab'表示要复制的字符串，2表示复制的份数

select repeat('ab',2);

//mysql字符串截取相关函数

//left（被截取字段，截取长度） 
left（str, length） 

//right（被截取字段，截取长度） 
right（str, length） 

//substring（被截取字段，从第几位开始截取） 
//substring（被截取字段，从第几位开始截取，截取长度） 
substring（str, pos） 
substring（str, pos, length）

select substring(content,5) as abstract from my_content_t 
select substring(content,5,200) as abstract from my_content_t  

//substring_index（被截取字段，关键字，关键字出现的次数） 
substring_index（str,delim,count） 

select substring_index("www.w3cschool.cn",".",2) as abstract from wiki_user 

MySQL数学函数

所有的数学函数在发生错误的情况下，均返回 NULL。

-元减。改变参数的符号
ABS(X)返回 X 的绝对值
SIGN(X)以 -1、0 或 1方式返回参数的符号，它取决于参数 X 是负数、0 或正数。
MOD(N,M) % 取模 (就如 C 中的 % 操作符)。返回 N 被 M 除后的余数
FLOOR(X)返回不大于 X 的最大整数值
CEILING(X)返回不小于 X 的最小整数
ROUND(X,D)将参数 X 四舍五入到最近的整数，然后返回。两个参数的形式是将一个数字四舍五入到 D 个小数后返回。
DIV 整除。类似于 FLOOR()，但是它可安全地用于 BIGINT 值。
PI()返回 PI 值(圆周率)。缺少显示 5 位小数，但是在 MySQL 内部，为 PI 使用全部的双精度。
RAND(N)返回一个范围在 0 到 1.0 之间的随机浮点值。如果一个整数参数 N 被指定，它被当做种子值使用(用于产生一个可重复的数值)
LEAST(X,Y,...)有两个或更多个参数，返回最小(最小值)的参数。
GREATEST(X,Y,...)返回最大(最大值)参数。

mysql substr()函数

substr(string string,num start,num length);
string为字符串；start为起始位置；length为长度。