MySQL 13 元数据、处理重复数据

1 元数据

// 获取服务器版本信息
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.39    |
+-----------+
1 row in set (0.00 sec)

// 获取当前数据库名
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| my_db      |
+------------+
1 row in set (0.00 sec)

// 获取当前用户名
mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

// 查看服务器状态
SHOW STATUS;

// 查看服务器配置变量
SHOW VARIABLES;

2 处理重复数据

2.1 防止表中出现重复数据

// 创建表并插入数据
mysql> create table person(
    ->     firstName char(20),
    ->     lastName char(20),
    ->     sex char(10)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into person values('li','si','male');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person values('zhang','san','male');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person values('li','si','male');
Query OK, 1 row affected (0.00 sec)

// 有重复数据
mysql> select * from person;
+-----------+----------+------+
| firstName | lastName | sex  |
+-----------+----------+------+
| li        | si       | male |
| zhang     | san      | male |
| li        | si       | male |
+-----------+----------+------+
3 rows in set (0.01 sec)

1）使用主键来保证数据的唯一性

mysql> create table person_1(
    ->     firstName char(20) not null,
    ->     lastName char(20) not null,
    ->     sex char(10),
    ->     primary key (firstName,lastName)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> insert into person_1 values('li','si','male');
Query OK, 1 row affected (0.00 sec)

mysql> insert into person_1 values('zhang','san','male');
Query OK, 1 row affected (0.00 sec)

// 插入重复数据出错
mysql> insert into person_1 values('li','si','male');
ERROR 1062 (23000): Duplicate entry 'li-si' for key 'PRIMARY'

// 使用ignore可以忽略数据库中已经存在的数据
// 当数据库中已经存在数据，则不会插入
mysql> insert ignore into person_1 values('li','si','male');
Query OK, 0 rows affected (0.00 sec)

2）使用UNIQUE来设置数据唯一性

mysql> create table person_2(
    ->     firstName char(20) not null,
    ->     lastName char(20) not null,
    ->     sex char(10),
    ->     UNIQUE (firstName,lastName)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> insert into person_2 values('li','si','male');
Query OK, 1 row affected (0.01 sec)

mysql> insert into person_2 values('zhang','san','male');
Query OK, 1 row affected (0.00 sec)

// 插入重复数据出错

mysql> insert into person_2 values('li','si','male');
ERROR 1062 (23000): Duplicate entry 'li-si' for key 'firstName'

mysql> insert ignore into person_2 values('li','si','male');
Query OK, 0 rows affected (0.00 sec)

2.2 统计重复数据

mysql> SELECT count(*) AS repetitions,firstName,lastName FROM person GROUP BY lastName,firstName HAVING repetitions > 1;
+-------------+-----------+----------+
| repetitions | firstName | lastName |
+-------------+-----------+----------+
|           2 | li        | si       |
+-------------+-----------+----------+
1 row in set (0.01 sec)

2.3 过滤重复数据

mysql> SELECT DISTINCT lastName,firstName FROM person;
+----------+-----------+
| lastName | firstName |
+----------+-----------+
| si       | li        |
| san      | zhang     |
+----------+-----------+
2 rows in set (0.00 sec)

2.4 删除重复数据

mysql> ALTER IGNORE TABLE person ADD PRIMARY KEY (lastName,firstName);
Query OK, 3 rows affected, 1 warning (0.03 sec)
Records: 3  Duplicates: 1  Warnings: 1

mysql> select * from person;
+-----------+----------+------+
| firstName | lastName | sex  |
+-----------+----------+------+
| zhang     | san      | male |
| li        | si       | male |
+-----------+----------+------+
2 rows in set (0.00 sec)