MySQL学习笔记（2）高级操作

MySQL UNION：

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

语法：MySQL UNION 操作符语法格式：

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 student1;
+----+-----+--------+-----+-----+
| id | num | name   | sex | age |
+----+-----+--------+-----+-----+
|  1 | 100 | 小明   | 男  |  20 |
|  2 | 101 | 小红   | 女  |  24 |
|  3 | 102 | 小刘   | 女  |  23 |
|  4 | 103 | 程兴旺 | 男  |  21 |
+----+-----+--------+-----+-----+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+------+------+-----+------+----------+
| id | num  | name | age | sex  | class    |
+----+------+------+-----+------+----------+
|  1 | NULL | 张三 |  19 | 男   | NULL     |
|  2 | NULL | 李四 |  25 | 男   | NULL     |
|  6 |  200 | 刘慧 |  23 | 女   | 对口一班 |
+----+------+------+-----+------+----------+
3 rows in set (0.00 sec)

实验1：从两张表中选取不同的年龄：（默认情况下会删除重复的数据）

mysql> select age from student
    -> union
    -> select age from student1
    -> order by age;
+-----+
| age |
+-----+
|  19 |
|  20 |
|  21 |
|  23 |
|  24 |
|  25 |
+-----+
6 rows in set (0.00 sec)

注释：UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

实验2：选择所有的年龄，包括重复的。

mysql> select age from student
    -> union ALL
    -> select age from student1
    -> order by age;
+-----+
| age |
+-----+
|  19 |
|  20 |
|  21 |
|  23 |
|  23 |
|  24 |
|  25 |
+-----+
7 rows in set (0.00 sec)

mysql>

实验3：选择出两张表中所有男性的信息。使用where语句的union。

mysql> select num, name, age ,sex from student
    -> where sex='男'
    -> union all
    -> select num, name, age ,sex from student1
    -> where sex='男'
    -> ;
+------+--------+-----+------+
| num  | name   | age | sex  |
+------+--------+-----+------+
| NULL | 张三   |  19 | 男   |
| NULL | 李四   |  25 | 男   |
|  100 | 小明   |  20 | 男   |
|  103 | 程兴旺 |  21 | 男   |
+------+--------+-----+------+
4 rows in set (0.00 sec)

MySQL排序：

如果需要对读取的数据进行排序，就可以使用 MySQL 的 ORDER BY 子句来设定想按哪个字段哪种方式来进行排序，再返回搜索结果。

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

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

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

使用示例：

按年龄升序进行排序：

mysql> select * from student order by age;
+----+------+------+-----+------+----------+
| id | num  | name | age | sex  | class    |
+----+------+------+-----+------+----------+
|  1 | NULL | 张三 |  19 | 男   | NULL     |
|  6 |  200 | 刘慧 |  23 | 女   | 对口一班 |
|  2 | NULL | 李四 |  25 | 男   | NULL     |
+----+------+------+-----+------+----------+
3 rows in set (0.00 sec)

mysql> select * from student order by age desc;
+----+------+------+-----+------+----------+
| id | num  | name | age | sex  | class    |
+----+------+------+-----+------+----------+
|  2 | NULL | 李四 |  25 | 男   | NULL     |
|  6 |  200 | 刘慧 |  23 | 女   | 对口一班 |
|  1 | NULL | 张三 |  19 | 男   | NULL     |
+----+------+------+-----+------+----------+
3 rows in set (0.00 sec)

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;

使用示例：

将student1表中男女分组，并统计男女各有多少人。

mysql> select sex, count(*) from student1 group by sex;
+-----+----------+
| sex | count(*) |
+-----+----------+
| 女  |        2 |
| 男  |        2 |
+-----+----------+
2 rows in set (0.00 sec)

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> select * from student where num is null;
+----+------+------+-----+------+-------+
| id | num  | name | age | sex  | class |
+----+------+------+-----+------+-------+
|  1 | NULL | 张三 |  19 | 男   | NULL  |
|  2 | NULL | 李四 |  25 | 男   | NULL  |
+----+------+------+-----+------+-------+
2 rows in set (0.00 sec)

mysql> select * from student where num is not null;
+----+------+------+-----+------+----------+
| id | num  | name | age | sex  | class    |
+----+------+------+-----+------+----------+
|  6 |  200 | 刘慧 |  23 | 女   | 对口一班 |
+----+------+------+-----+------+----------+
1 row in set (0.00 sec)

MySQL 正则表达式：

MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

下表中的正则模式可应用于 REGEXP 操作符中。

模式	描述
^	匹配输入字符串的开始位置。如果设置了 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 次。

使用示例：

mysql> select name from student1 where name regexp '^小';
#匹配以name中以“小”字开头的姓名。
+------+
| name |
+------+
| 小明 |
| 小红 |
| 小刘 |
+------+
3 rows in set (0.00 sec)

MySQL事务：

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

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

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

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

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

• 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。

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

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

MySQL索引：

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

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

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

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

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

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

普通索引：

创建索引：

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

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;

唯一索引：

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

创建索引

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

使用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 命令添加和删除主键

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

mysql> alter table student add primary key (id);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

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

mysql> alter table student drop primary key;
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

删除主键时只需指定PRIMARY KEY，但在删除索引时，必须知道索引名。

显示索引信息

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

尝试以下实例:

mysql> SHOW INDEX FROM table_name; \G

MySQL临时表：

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

临时表在MySQL 3.23版本中添加，如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。

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

使用示例：

create temporary Table test（`id` int not null）;
#创建临时表

drop Table test；
#手动删除临时表，默认当断开与数据库的连接后，临时表就会自动销毁。

当你使用 SHOW TABLES命令显示数据表列表时，将无法看到 临时表。

如果退出当前MySQL会话，再使用 SELECT命令来读取原先创建的临时表数据，那会发现数据库中没有该表的存在，因为在退出时该临时表已经被销毁了。

MySQL 复制表：

如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE … SELECT 命令，是无法实现的。

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

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

使用示例：

1. 获取数据表完整的结构：

   mysql> show create table student \G;
   *************************** 1. row ***************************
          Table: student
   Create Table: CREATE TABLE `student` (
     `id` int(11) NOT NULL,
     `num` int(11),
     `name` varchar(20) NOT NULL,
     `age` int(11) NOT NULL,
     `sex` varchar(10) DEFAULT NULL,
     `class` varchar(20) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `index_1` (`num`)
   ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
   1 row in set (0.00 sec)
   
   ERROR:
   No query specified

2. 修改sql数据表表明，并执行sql语句。

   mysql> CREATE TABLE `student2` (
       ->   `id` int(11) NOT NULL,
       ->   `num` int(11),
       ->   `name` varchar(20) NOT NULL,
       ->   `age` int(11) NOT NULL,
       ->   `sex` varchar(10) DEFAULT NULL,
       ->   `class` varchar(20) DEFAULT NULL,
       ->   PRIMARY KEY (`id`),
       ->   KEY `index_1` (`num`)
       -> ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
   Query OK, 0 rows affected (0.01 sec)

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

其他复制表的方式：

第一、只复制表结构到新表

create table 新表 select * from 旧表 where 1=2 #这也可以按条件复制一部分数据

或者

create table 新表 like 旧表 #只复制表的结构

第二、复制表结构及数据到新表

create table新表 select * from 旧表

MySQL中\G的意思：查询结果按列打印

\G 放到sql语句后,可以使每个字段打印到单独的行。

\g相当于；，结尾号。

mysql> select * from student1;
+----+-----+--------+-----+-----+
| id | num | name   | sex | age |
+----+-----+--------+-----+-----+
|  1 | 100 | 小明   | 男  |  20 |
|  2 | 101 | 小红   | 女  |  24 |
|  3 | 102 | 小刘   | 女  |  23 |
|  4 | 103 | 程兴旺 | 男  |  21 |
+----+-----+--------+-----+-----+
4 rows in set (0.00 sec)

mysql> select * from student1 \G;
*************************** 1. row ***************************
  id: 1
 num: 100
name: 小明
 sex: 男
 age: 20
*************************** 2. row ***************************
  id: 2
 num: 101
name: 小红
 sex: 女
 age: 24
*************************** 3. row ***************************
  id: 3
 num: 102
name: 小刘
 sex: 女
 age: 23
*************************** 4. row ***************************
  id: 4
 num: 103
name: 程兴旺
 sex: 男
 age: 21
4 rows in set (0.00 sec)

ERROR:
No query specified

mysql> select * from student1 \g
+----+-----+--------+-----+-----+
| id | num | name   | sex | age |
+----+-----+--------+-----+-----+
|  1 | 100 | 小明   | 男  |  20 |
|  2 | 101 | 小红   | 女  |  24 |
|  3 | 102 | 小刘   | 女  |  23 |
|  4 | 103 | 程兴旺 | 男  |  21 |
+----+-----+--------+-----+-----+
4 rows in set (0.00 sec)

MySQL 元数据：

如果想知道MySQL以下三种信息：

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

在MySQL的命令提示符中，可以很容易的获取以上服务器信息。

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

MySQL 序列使用：

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

使用aoto_increament：

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

获取aoto_increament的值：

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

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                9 |
+------------------+
1 row in set (0.00 sec)

重置序列：

如果删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。

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

设置序列的开始值：

一般情况下序列的开始值为1，但如果需要指定一个开始值100，可以通过以下语句来实现：

mysql> CREATE TABLE tablename
    -> (
    -> 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;

或者创建表后指定。

ALTER TABLE tablename AUTO_INCREMENT = 100;

MySQL 处理重复数据：

有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。

防止表中出现重复的数据：

• 可以在MySQL数据表中设置指定的字段为PRIMARY KEY（主键） 或者UNIQUE（唯一）索引来保证数据的唯一性。
• 当有想要两个字段都不能有重复时，可以设置双主键模式来保证数据的唯一性。如果设置了双主键，那么那个键的默认值不能为NULL，可设置为NOT NULL。
• 如果设置了唯一索引，那么在插入重复数据时，SQL语句将无法执行成功,并抛出错。
• NSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
• INSERT IGNORE INTO当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而REPLACE INTO into如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。

另一种设置数据的唯一性方法是添加一个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)
);

统计重复数据：

般情况下，查询重复的值，请执行以下操作：

• 确定哪一列包含的值可能会重复。
• 在列选择列表使用COUNT(*)列出的那些列。
• 在GROUP BY子句中列出的列。

mysql> select * from test;
+----+
| id |
+----+
| 10 |
| 20 |
| 20 |
| 20 |
| 30 |
| 30 |
+----+
6 rows in set (0.00 sec)

mysql> select id, count(*) as counts from test
    -> group by id;
+----+--------+
| id | counts |
+----+--------+
| 10 |      1 |
| 20 |      3 |
| 30 |      2 |
+----+--------+
3 rows in set (0.00 sec)

过滤重复数据：

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

mysql> select distinct id from test;
+----+
| id |
+----+
| 10 |
| 20 |
| 30 |
+----+
3 rows in set (0.00 sec)

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

mysql> select id from test group by id;
+----+
| id |
+----+
| 10 |
| 20 |
| 30 |
+----+
3 rows in set (0.00 sec)

删除重复数据：

mysql> create table test1 select id from test group by id;
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+
| id |
+----+
| 10 |
| 20 |
| 30 |
+----+
3 rows in set (0.00 sec)

相当于创建一个新的表，复制不重复的数据。然后可以删除原来的表，并将新创建的表更名为原来的表。从而达到删除重复数据的目的。

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

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

MySQL 及 SQL注入：

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

我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

MySQL导出数据：

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

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

以下实例中我们将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中:

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

可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：

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

在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

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

使用 mysqldump 导出数据需要使用 –tab 选项来指定导出文件指定的目录，该目标必须是可写的。

以下实例将数据表 runoob_tbl 导出到 /tmp 目录中：

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

---

导出 SQL 格式的数据

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

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

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

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

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

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

–all-databases 选项在 MySQL 3.23.12 及以后版本加入。

该方法可用于实现数据库的备份策略。

---

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

如果需要将数据拷贝至其他的 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 *****
#你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：</p>
$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

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

MySQL导入数据：

1、mysql 命令导入

使用 mysql 命令导入语法格式为：

mysql -u用户名    -p密码    <  要导入的数据库数据(runoob.sql)

实例：

# mysql -uroot -p123456 < runoob.sql

以上命令将将备份的整个数据库 runoob.sql 导入。

---

2、source 命令导入

source 命令导入数据库需要先登录到数库终端：

mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

---

3、使用 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);

---

4、使用 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 *****

---

mysqlimport的常用选项介绍

选项	功能
-d or –delete	新数据导入数据表中之前删除数据数据表中的所有信息
-f or –force	不管是否遇到错误，mysqlimport将强制继续插入数据
-i or –ignore	mysqlimport跳过或者忽略那些有相同唯一 关键字的行， 导入文件中的数据将被忽略。
-l or -lock-tables	数据被插入之前锁住表，这样就防止了， 你在更新数据库时，用户的查询和更新受到影响。
-r or -replace	这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。
–fields-enclosed- by= char	指定文本文件中数据的记录时以什么括起的， 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。
–fields-terminated- by=char	指定各个数据的值之间的分隔符，在句号分隔的文件中， 分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符（Tab）
–lines-terminated- by=str	此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符： 一个新行或者一个回车。

mysqlimport命令常用的选项还有-v 显示版本（version）， -p 提示输入密码（password）等。

---