MySQL知识点总结_php mysql知识点

最近涉及到C语言MySQL数据库相关操作，故整理下来，方便自己日后查看

一、安装配置

1、安装

sudo apt-get install mysql-server mysql-client libmysqlclient-dev
1

mysql-server是必须的，除非你想连接其他MySQL服务器；
mysql-client是必须的
libmysqlclient-dev可选，需要使用MySQL C API时需要安装，链接时指定-lmysqlclient
如果是ubuntu18.04则不会提示输入密码，默认是没有密码

2、测试是否安装成功

sudo netstat -tap | grep mysql
1

安装成功如下图所示

3、初始化MySQL

mysqld --initialize
1

4、启动MySQL

sudo service mysql start
# or
sudo systemctl start mysql.service
1
2
3

5、查看MySQL运行状态

sudo service mysql status
# or
sudo systemctl status mysql.service
1
2
3

6、使用 mysqladmin 工具来获取服务器状态

mysqladmin --version
#执行该命令将会输出如下信息，该信息基于系统信息：
mysqladmin  Ver 8.42 Distrib 5.7.25, for Linux on x86_64
1
2
3

7、设置密码

root用户默认密码为空，可使用如下命令创建root用户密码

mysqladmin -u root password "new_password";
1

二、基本操作

1、登录MySQL

#登录本地MySQL
mysql -u root -p	#可使用任何用户名
#登录远程MySQL
mysql -h host -P port -u root -p	#host为远程mysql服务器IP地址，port为端口，默认为3306
1
2
3
4

-h 指定客户端要登录的MySQL主机名，本地可使用“localhost”或“127.0.0.1”，本地可省略该参数
-u 指定要登录的用户名
-p 告诉MySQL服务器会使用密码登录，若密码为空，可省略该参数

【注意】ubuntu18.04版本在操作数据库的时候需要sudo权限
ubuntu18.04一般以无密码的方式登录进去设置plugin及authentication_string，如下：

sudo mysql -u root -p
mysql>use mysql;
mysql>UPDATE user SET plugin="mysql_native_password", authentication_string=PASSWORD("123456") WHERE user="root";
# password() 加密函数已经在 8.0.11 中移除了，可以使用 MD5() 函数代替。
mysql>FLUSH PRIVILEGES;
1
2
3
4
5

2、用户设置

mysql -u root -p		#登录mysql
#show databases;			#查看已经存在的数据库
use mysql;				#选择使用
INSERT INTO user 		#在用户表user中插入用户数据
          (host, user, password, select_priv, insert_priv, update_priv) 
           VALUES ('localhost', 'guest', PASSWORD('guest123'), 'Y', 'Y', 'Y');
FLUSH PRIVILEGES;		#重新载入授权表
1
2
3
4
5
6
7

插入用户时使用PASSWORD() 函数来对密码进行加密
在 MySQL5.7 中 user 表的 password 已换成了authentication_string。
password() 加密函数已经在 8.0.11 中移除了，可以使用 MD5() 函数代替。
注意需要执行 FLUSH PRIVILEGES 语句。 这个命令执行后会重新载入授权表。
如果不使用该命令，你就无法使用新创建的用户来连接mysql服务器，除非你重启mysql服务器。

在创建用户时，为用户指定权限，在对应的权限列中，在插入语句中设置为 ‘Y’ 即可，用户权限列表如下：

Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv
1
2
3
4
5
6
7
8
9
10
11
12
13
14

也可以使用GRANT命令创建新用户

#以下命令会给指定数据库testdatabase添加用户 test，密码为 test123
mysql -u root -p
use mysql;
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON testdatabase.*
    -> TO 'test'@'localhost'
    -> IDENTIFIED BY 'test123';
    #以上命令会在mysql数据库中的user表创建一条用户信息记录。
1
2
3
4
5
6
7
8

注意: MySQL 的SQL语句以分号 “;” 作为结束标识。

若在mysql8.0之后的版本创建用户失败，可以尝试以下命令：

use mysql;
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'username'@'localhost';
flush privileges;
1
2
3
4

若需要远程登录，需要将localhost修改为%

3、基本命令

use 数据库名;		#选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。
show databases;		#列出 MySQL 数据库管理系统的数据库列表。
show tables;		#显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。
show columns from 数据表;		#显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。
show index from 数据表;			#显示数据表的详细索引信息，包括PRIMARY KEY（主键）。
show table status like [from db_name] [like 'pattern'] \G; #该命令将输出Mysql数据库管理系统的性能及统计信息。
mysql> show table status from testdatabase;		#显示数据库 testdatabase 中所有表的信息
mysql> show table status from testdatabase like 'test%';     # 表名以test开头的表的信息
mysql> show table status from testdatabase like 'test%'\G;   # 加上 \G，查询结果按列打印
1
2
3
4
5
6
7
8
9

4、创建数据库

create database <数据库名>;					#须先登录MySQL
或者
mysqladmin -u root -p create <数据库名>  			#该命令执行成功后会创建MySQL数据库
1
2
3

5、删除数据库

drop database <数据库名>;					#drop 命令删除数据库
或者
mysqladmin -u root -p drop <数据库名>
#执行以上删除数据库命令后，会出现一个提示框，来确认是否真的删除数据库：
#Dropping the database is potentially a very bad thing to do.
#Any data stored in the database will be destroyed.

#Do you really want to drop the 'test' database [y/N]
1
2
3
4
5
6
7
8

6、选择数据库

use <数据库名>;			#须先登录MySQL
1

7、创建数据表

create table table_name (column_name column_type);
例如：
create table if not exists `test_tbl`(
   `test_id` int unsigned auto_increment,
   `test_title` varchar(100) not null,
   `test_author` varchar(40) not null,
   `test_date` date,
   primary key ( `test_id` )
)engine=InnoDB default charset=utf8;
1
2
3
4
5
6
7
8
9

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

8、删除数据表

drop table table_name ;
1

9、插入数据

insert into table_name ( field1, field2,...fieldN )
                       values
                       ( value1, value2,...valueN );
例如：
insert into test_tbl 
     (test_title, test_author, test_date)
    VALUES
    ("study mysql", "tkly", NOW());
1
2
3
4
5
6
7
8

NOW() 是一个 MySQL 函数，该函数返回日期和时间。

10、查询数据

select * from <数据表名>;		#查询数据表中的所有信息
select column_name,column_name
from table_name
[where Clause]
[limit N] [offset M]
1
2
3
4
5

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

11、where子句

从 MySQL 表中使用 SQL SELECT 语句来读取数据。
如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

select field1, field2,...fieldN from table_name1, table_name2...
[where condition1 [and [or]] condition2.....
1
2

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

#例如：
select * from runoob_tbl where test_author='tkly';
1
2

12、update查询

修改或更新 MySQL 中的数据，我们可以使用 SQL UPDATE 命令来操作。

update table_name set field1=new-value1, field2=new-value2 [where Clause]
1

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

13、delete语句

可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

delete from table_name [where Clause]
1

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

14、like子句

select field1, field2,...fieldN 
from table_name
where field1 like condition1 [and [or]] filed2 = 'somevalue'
1
2
3

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

15、union操作符

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

select expression1, expression2, ... expression_n
from tables
[where conditions]
union [all | distinct]
select expression1, expression2, ... expression_n
from tables
[where conditions];
1
2
3
4
5
6
7

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

16、排序

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

select field1, field2,...fieldN table_name1, table_name2...
order by field1, [field2...] [asc [desc]]
1
2

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

17、分组（GROUP BY 语句）

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

select column_name, function(column_name)
from table_name
where column_name operator value
group by column_name;
1
2
3
4

18、连接（INNER JOIN、LEFT JOIN、RIGHT JOIN）

可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

1、inner join
#例如：
select a.test_id, a.test_author, b.test_count from test_tbl a 
inner join tcount_tbl b on a.test_author = b.test_author;
等价于
select a.test_id, a.test_author, b.test_count from test_tbl a, tcount_tbl b 
where a.test_author = b.test_author;

2、left join
#LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
select a.test_id, a.test_author, b.test_count from test_tbl a 
left join tcount_tbl b on a.test_author = b.test_author;

3、right join
#RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。
select a.test_id, a.test_author, b.test_count from test_tbl a 
right join tcount_tbl b on a.test_author = b.test_author;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

19、NULL 值处理

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 运算符。

20、正则表达式

MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
下表中的正则模式可应用于 REGEXP 操作符中。

#查找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$';
1
2
3
4
5
6
7
8

21、事务

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

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

在 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 U NCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

22、ALTER命令

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

1)、删除，添加或修改表字段
如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：
ALTER TABLE testalter_tbl DROP i;
如果数据表中只剩余一个字段则无法使用DROP来删除字段。
MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:
ALTER TABLE testalter_tbl ADD i INT;
指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）
FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

2)、修改字段类型及名称
如果需要修改字段类型及名称, 可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
例如：
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：
ALTER TABLE testalter_tbl CHANGE i j BIGINT;

3)、ALTER TABLE 对 Null 值和默认值的影响
修改字段时，可以指定是否包含值或者是否设置默认值。
以下实例，指定字段 j 为 NOT NULL 且默认值为100 。
ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;
如果不设置默认值，MySQL会自动设置该字段默认为 NULL。

4)、修改字段默认值
可以使用 ALTER 来修改字段的默认值：
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
可以使用 ALTER 命令及 DROP子句来删除字段的默认值：
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成。尝试以下实例，我们将表 testalter_tbl 的类型修改为 MYISAM ：
ALTER TABLE testalter_tbl ENGINE = MYISAM;
注意：查看数据表类型可以使用 SHOW TABLE STATUS 语句。
SHOW TABLE STATUS LIKE ‘testalter_tbl’\G

5)、修改表名
如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。
将数据表 testalter_tbl 重命名为 alter_tbl：
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
ALTER 命令还可以用来创建及删除MySQL数据表的索引

23、索引

普通索引:

创建索引:
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; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

唯一索引:

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引:
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))  
);  
1
2
3
4
5
6
7
8
9
10
11
12
13

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

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

例如：
ALTER TABLE testalter_tbl ADD INDEX (c);
#可以在 ALTER 命令中使用 DROP 子句来删除索引
ALTER TABLE testalter_tbl DROP INDEX c;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

使用 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;
删除主键时只需指定PRIMARY KEY，但在删除索引时，必须知道索引名。
1
2
3
4
5
6

显示索引信息

显示索引信息:
可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
SHOW INDEX FROM table_name; \G
1
2
3

24、临时表

创建临时表：
CREATE TEMPORARY TABLE tablename(...)
删除临时表：
默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。
当然也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。
1
2
3
4
5

25、复制表

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

26、元数据

获取服务器元数据
以下命令语句可以在 MySQL 的命令提示符使用：

27、序列使用

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

获取AUTO_INCREMENT值
可以使用 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);
1
2
3
4

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

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;
1
2
3
4
5
6
7
8

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

mysql> ALTER TABLE t AUTO_INCREMENT = 100;
1

28、处理重复数据

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

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

另一种设置数据的唯一性方法是添加一个 UNIQUE 索引

统计重复数据

以下我们将统计表中 first_name 和 last_name的重复记录数：

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

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

过滤重复数据

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

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl;
1
2

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

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

删除重复数据

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

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;
1
2
3
4

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

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

下一篇：MySQL C API的基本用法