【MySQL】常用命令

最新推荐文章于 2021-08-25 17:13:58 发布

Yangzhe1467

最新推荐文章于 2021-08-25 17:13:58 发布

阅读量204

点赞数

分类专栏： MySQL 基础知识文章标签： MySQL

本文链接：https://blog.csdn.net/lyz19921004/article/details/89228789

版权

基础知识同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

MySQL

10 篇文章 0 订阅

订阅专栏

注：windows系统，可以将MySQL安装目录下的bin目录设置在环境变量中，运行命令时就不必切换至该目录

1. 验证MySQL是否正常工作

mysqladmin --version

如果安装成功，则会打印

mysqladmin  Ver 8.0.15 for Win64 on x86_64 (MySQL Community Server - GPL)

如果没有输出，则说明MySQL没有正确安装。

2.启动数据库

以管理员身份打开cmd命令工具，切换到安装MySQL目录的bin文件夹目录：

mysql --initialize --console

输出：

...
2018-04-20T02:35:05.464644Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: APWCY5ws&hjQ
...

其中APWCY5ws&hjQ就是初始密码，登陆后可以修改。
执行如下命令：

mysqld install

然后就可以执行命令，来启动数据库：

net start mysqlname

注：mysqlname是安装时设置的数据库名称，有些教程中start后面跟随mysql字符，但笔者自己电脑启动时是填写数据库名称。

3.登陆MySQL

命令格式：

mysql -h 主机名 -u 用户名 -p

-h: 指定客户端所要登陆的MySQL主机名，登录本机(localhost或127.0.0.1)可以省略本参数；
-u: 登陆的用户名；
-p: 告诉服务器将会使用密码登陆，如果密码为空，可以忽略此选项。
所以，登陆本机的MySQL数据可以，只需要运行：

mysql -u root -p

然后显示如下，输入数据库密码就可以登陆到数据库：

Enter password:

4.创建数据库

CREATE DATABASE 数据库名;

5. 删除数据库

drop database 数据库名;

使用mysqladmin删除数据库

mysqladmin -u root -p drop 数据库名;

6.选择数据库

USE 数据库名;

输出

Database changed

7. 创建数据表

创建MySQL数据表需要：表名，表字段名，定义每个表字段：

CREATE TABLE 表名(字段名，字段类型);

例如：

CREATE TABLE IF NOT EXISTS `teacher`(
   `work_id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(32) NOT NULL,
   `course` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `work_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

8.删除数据表

DROP TABLE 表名;

9.插入数据

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

例：

INSERT INTO teacher
    (name, course, submission_date)
    VALUES
    ("张三", "语文", NOW());

10.查询语句

语法：

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

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件，Clause表示条件语句。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

(1) WHERE子句
语法：

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

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

SELECT * FROM teacher WHERE name='张三';

MySQL 的 WHERE 子句的字符串比较是不区分大小写的。你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

SELECT * FROM teacher WHERE BINARY name='LiSi';

(2) UPDATE查询
语法：

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

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

UPDATE teacher SET name = 'wangwu' WHERE work_id = 3;

(3) DELETE 语句
语法：

DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除.
你可以在 WHERE 子句中指定任何条件。
你可以在单个表中一次性删除记录。

DELETE FROM teacher WHERE work_id = 2;

(4) LIKE 语句
语句：

 SELECT field1,field2,field3...fieldN
 FROM table_name
 WHERE field1 LIKE condition1 [AND [OR]] field2 = 'somevalue'

你可以在 WHERE 子句中指定任何条件。
你可以在 WHERE 子句中使用LIKE子句。
你可以使用LIKE子句代替等号 =。
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
你可以使用 AND 或者 OR 指定一个或多个条件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。
SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *;
如果没有使用百分号%, LIKE 子句与等号=的效果是一样的。

SELECT * FROM teacher WHERE name LIKE '%Si';

(5) UNION操作符
UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中，多个 SELECT 语句会删除重复的数据。
语法：

SELECT  expression1,expression2,...expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1,expression2,...expression_n
FROM tables
[WHERE conditions];

expression1, expression2, … expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选，检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

mysql> SELECT * FROM websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 微博          | http://weibo.com/         | 20    | CN      |
| 4  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 5  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name   | url                     | country |
+----+------------+-------------------------+---------+
|  1 | QQ APP     | http://im.qq.com/       | CN      |
|  2 | 微博 APP | http://weibo.com/       | CN      |
|  3 | 淘宝 APP | https://www.taobao.com/ | CN      |
+----+------------+-------------------------+---------+

UNION举例：

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

输出：

+------------+
|  country  |
+------------+
|  CN		 |
|  IND		 |
|  USA		 |
+------------+

注释：UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值，如下：

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country

输出：

+------------+
|  country  |
+------------+
|  CN		 |
|  CN		 |
|  CN		 |
|  CN		 |
|  CN		 |
|  IND		 |
|  USA		 |
|  USA		 |
|  USA		 |
+------------+

带有WHERE的SQL UNION ALL

SELECT  country , name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country,app_name FROM apps
WHERE country='CN'
ORDER BY country;

输出：

+------------+------------+
|  country  |   name	  |
+------------+------------+
|  CN		 |   淘宝	  |
|  CN		 |   QQ APP	  |
|  CN		 |   淘宝	  |
|  CN		 |   淘宝	  |
|  CN		 |   淘宝	  |
+------------+

11.排序

语法：

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

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

SELECT * FROM teacher ORDER BY sebmission_date ASC;
SELECT * FROM teacher ORDER BY sebmission_date DESC;

12. GROUP BY 语句

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

GROUP BY 语句根据一个或多个列对结果集进行分组。
建表：

CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `employee_tbl` 
VALUES 
('1', '小明', '2016-04-22 15:25:33', '1'), 
('2', '小王', '2016-04-20 15:25:47', '3'), 
('3', '小丽', '2016-04-19 15:26:02', '2'), 
('4', '小王', '2016-04-07 15:26:14', '4'), 
('5', '小明', '2016-04-11 15:26:40', '4'),
('6', '小明', '2016-04-04 15:26:54', '2');

分组：

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+

使用WITH ROLLUP
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）:

SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+

13. 数据表连接

有两个数据表：

mysql> SELECT * FROM tcount_tbl;
+---------------+--------------+
| author 		| count 	   |
+---------------+--------------+
| 菜鸟教程  		| 10           |
| RUNOOB.COM    | 20           |
| Google        | 22           |
+---------------+--------------+
3 rows in set (0.01 sec)
 
mysql> SELECT * from course_tbl;
+-----------+---------------+---------------+---------+
| id 		| title  	  | author 	 |  date 		  |
+-----------+---------------+---------------+---------+
| 1         | 学习 PHP    | 张三  	| 2017-04-12      |
| 2         | 学习 MySQL  | 张三	 	| 2017-04-12      |
| 3         | 学习 Java   | John   	| 2015-05-01      |
| 4         | 学习 Python | John  	| 2016-03-06      |
| 5         | 学习 C      | FK       | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.01 sec)

(1) INNER JOIN

SELECT a.id,a.author,b.count FROM course_tbl a INNER JOIN tcount_tbl b ON a.author = b.author;
+------------+-----------+--------------+
| a.id 		  | a.author | b.count 		|
+-------------+---------+---------------+
| 1           | 张三    | 10             |
| 2           | 张三    | 10             |
| 3           | John    | 20             |
| 4           | John    | 20             |
+-------------+---------+----------------+

以上语句等价于：

SELECT a.id,a.author,b.count FROM course_tbl a,tcount_tbl b WHERE a.author=b.author;

(2) LEFT JOIN
该语句会读取左边的数据表 course_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中没有对应的author 字段值。

SELECT a.id, a.author, b.count FROM course_tbl a LEFT JOIN tcount_tbl b ON a.author = b.author;
+------------+-----------+--------------+
| a.id 		  | a.author | b.count 		|
+-------------+---------+---------------+
| 1           | 张三    | 10             |
| 2           | 张三    | 10             |
| 3           | John    | 20             |
| 4           | John    | 20             |
| 5           | FK       | NULL          |
+-------------+---------+----------------+

(2) RIGHT JOIN
该语句会读取右边的数据表 tcount_tbl的所有选取的字段数据，即便在左侧表 course_tbl中没有对应的author 字段值。

SELECT a.id, a.author, b.count FROM course_tbl a RIGHT JOIN tcount_tbl b ON a.author = b.author;
+------------+-----------+--------------+
| a.id 		  | a.author | b.count 		|
+-------------+---------+---------------+
| 1           | 张三    | 10             |
| 2           | 张三    | 10             |
| 3           | John    | 20             |
| 4           | John    | 20             |
| NULL        | NULL    | 22            |
+-------------+---------+----------------+

14. NULL值处理

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

mysql> create table test_tbl
    -> (
    -> author varchar(40) NOT NULL,
    -> count  INT
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO test_tbl (author, count) values ('Jim', 20);
mysql> INSERT INTO test_tbl (author, count) values ('Jobs', NULL);
mysql> INSERT INTO test_tbl (author, count) values ('Google', NULL);
mysql> INSERT INTO test_tbl (author, count) values ('FK', 20);

以下语句为正确用法，不能使用= NULL和!= NULL形式：

SELECT * FROM test_tbl WHERE count IS NULL;
SELECT * from test_tbl WHERE count IS NOT NULL;

15. 正则表达式

^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$ 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
. 匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用象 ‘[.\n]’ 的模式。
[...] 字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^...] 负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1|p2|p3 匹配 p1 或 p2 或 p3。例如，‘z|food’ 能匹配 “z” 或 “food”。’(z|f)ood’ 则匹配 “zood” 或 “food”。
* 匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。
+ 匹配前面的子表达式一次或多次。例如，‘zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
{n} n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m} m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

查找name字段中以st为开头的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以ok为结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含mar字符串的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以ok字符串结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

16. MySQL事务

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句。

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）

**原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
**一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
**隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
**持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句：

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

MYSQL 事务处理主要有两种方法：

用 BEGIN,ROLLBACK, COMMIT来实现
BEGIN开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1开启自动提交

mysql> CREATE TABLE transaction_test( id int(5)) engine=innoDB;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

17. ALTER命令

CREATE TABLE test_tbl( i INT, c CHAR(1));

SHOW COLUMNS FROM test_tbl;

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

(1) 删除或添加表字段

ALTER TABLE test_tbl DROP i; # 删除字段

ALTER TABLE test_tbl ADD i INT; #添加字段

如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER字段名（设定位于某个字段之后）:

ALTER TABLE test_tbl DROP i;
ALTER TABLE test_tbl ADD i INT FIRST;
ALTER TABLE test_tbl DROP i;
ALTER TABLE test_tbl ADD i INT AFTER c;

FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。
(2) 修改字段类型及名称
如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句。

ALTER TABLE test_tbl MODIFY c CHAR(10);

使用CHANGE ，CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型

ALTER TABLE test_tbl CHANGE i j BIGINT;

ALTER TABLE test_tbl CHANGE j j INT;

(3) 对NULL和默认值的影响

ALTER TABLE test_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;

(4) 修改字段默认值

ALTER TABLE test_tbl ALTER i SET DEFAULT 1000;

(5) 修改数据表类型

ALTER TABLE test_tbl ENGINE = MYISAM;

(6) 修改表名

ALTER TABLE test_tbl RENAME TO alter_tbl;

18. MySQL索引

(1) 普通索引

创建索引：
```
CREATE INDEX indexName ON mytable(username(length));
```
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length;
修改表结构（添加索引）

ALTER table tableName ADD INDEX indexName(columnName);

创建表的时候直接指定

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

删除索引

DROP INDEX [indexName] ON mytable;

(2) 唯一索引

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length));

创建表的时候直接指定：

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

(3) 使用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 ，用于全文索引。

ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引:

ALTER TABLE testalter_tbl DROP INDEX c;

(4) 使用ALTER命令添加和删除主键
主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）:

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

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

ALTER TABLE testalter_tbl DROP PRIMARY KEY;

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

SHOW INDEX FROM table_name; \G

19. 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
);

删除临时表

DROP TABLE SalesSummary;

20. MySQL复制表

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

SHOW CREATE TABLE test_tbl \G;     # 显示test_tbl 表创建命令

21. MySQL元数据

查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
数据库和数据表的信息： 包含了数据库及数据表的结构信息。
MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。

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

22. MySQL序列使用

使用AUTO_INCREMENT

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

获取AUTO_INCREMENT值
在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( )函数来获取最后的插入表中的自增列的值。

重置序列
如果你删除了数据表中的多条记录，并希望对剩下数据的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,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

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

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

23. MySQL处理重复数据

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

CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10)
);

如果你想设置表中字段first_name，last_name 数据不能重复，你可以设置双主键模式来设置数据的唯一性，如果你设置了双主键，那么那个键的默认值不能为NULL，可设置为 NOT NULL。

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。
INSERT IGNORE INTO 与 INSERT INTO的区别就是INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

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

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   UNIQUE (last_name, first_name)
);

统计重复数据

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;

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

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

删除重复数据

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
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);

24. MySQL及SQL注入

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

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

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

25. MySQL导出数据

使用 SELECT ... INTO OUTFILE 语句导出数据

mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/runoob.txt';

可以通过命令选项来设置数据输出的指定格式

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

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

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

导出表作为原始数据

mysqldump -u root -p --no-create-info \
            --tab=/tmp RUNOOB runoob_tbl

导出 SQL 格式的数据

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt

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

$ mysqldump -u root -p RUNOOB > database_dump.txt

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

mysqldump -u root -p RUNOOB > database_dump.txt

将数据表及数据库拷贝至其他主机
在源主机上执行以下命令，将数据备份至 dump.txt 文件中:

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

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

 mysql -u root -p database_name < dump.txt

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

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

注MySQL函数和运算符可以参考网站：http://www.runoob.com/mysql/mysql-functions.html