MySQL SQL笔记

查询MySQL版本

SELECT VERSION();

1
2
3
4
5
6
7
mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.01 sec)

查看当前库正在使用的表

SHOW OPEN TABLES WHERE in_use > 0 ;

1
2
mysql> SHOW OPEN TABLES WHERE in_use > 0 ;
Empty set (0.13 sec)
1
2
3
4
5
6
7
mysql>  SHOW OPEN TABLES WHERE in_use > 0 ;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test | t | 1 | 0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

查看正在运行的线程

SHOW PROCESSLIST ;

1
2
3
4
5
6
7
8
9
mysql> SHOW PROCESSLIST ;
+------+------+-----------------+------------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------------+------------+---------+------+----------+------------------+
| 2622 | root | localhost:55861 | dataserver | Sleep | 38 | | NULL |
| 2623 | root | localhost:55862 | NULL | Sleep | 59 | | NULL |
| 2628 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+------+------+-----------------+------------+---------+------+----------+------------------+
3 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
mysql> SHOW TABLE STATUS ;
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+
| columns_priv | MyISAM | 10 | Fixed | 0 | 0 | 0 | 241505530017742847 | 4096 | 0 | NULL | 2019-07-05 10:31:46 | 2019-07-05 10:31:46 | NULL | utf8_bin | NULL | | Column privileges |
| db | MyISAM | 10 | Fixed | 2 | 488 | 976 | 137359788634800127 | 5120 | 0 | NULL | 2019-07-05 10:31:46 | 2019-07-05 10:31:48 | NULL | utf8_bin | NULL | | Database privileges |
| engine_cost | InnoDB | 10 | Dynamic | 2 | 8192 | 16384 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | |
| event | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 2048 | 0 | NULL | 2019-07-05 10:31:46 | 2019-07-05 10:31:46 | NULL | utf8_general_ci | NULL | | Events |
| func | MyISAM | 10 | Fixed | 0 | 0 | 0 | 162974011515469823 | 1024 | 0 | NULL | 2019-07-05 10:31:46 | 2019-07-05 10:31:46 | NULL | utf8_bin | NULL | | User defined functions |
| general_log | CSV | 10 | Dynamic | 2 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | General log |
| gtid_executed | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | latin1_swedish_ci | NULL | | |
| help_category | InnoDB | 10 | Dynamic | 41 | 399 | 16384 | 0 | 16384 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | help categories |
| help_keyword | InnoDB | 10 | Dynamic | 728 | 135 | 98304 | 0 | 81920 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | help keywords |
| help_relation | InnoDB | 10 | Dynamic | 1276 | 51 | 65536 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | keyword-topic relation |
| help_topic | InnoDB | 10 | Dynamic | 618 | 2571 | 1589248 | 0 | 81920 | 4194304 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | help topics |
| innodb_index_stats | InnoDB | 10 | Dynamic | 4895 | 297 | 1458176 | 0 | 0 | 4194304 | NULL | 2019-07-05 10:31:51 | 2019-11-26 16:57:16 | NULL | utf8_bin | NULL | stats_persistent=0 | |
| innodb_table_stats | InnoDB | 10 | Dynamic | 418 | 235 | 98304 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | 2019-11-26 16:57:16 | NULL | utf8_bin | NULL | stats_persistent=0 | |
| ndb_binlog_index | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2019-07-05 10:31:46 | 2019-07-05 10:31:46 | NULL | latin1_swedish_ci | NULL | | |
| plugin | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | MySQL plugins |
| proc | MyISAM | 10 | Dynamic | 48 | 6261 | 300528 | 281474976710655 | 4096 | 0 | NULL | 2019-07-05 10:31:46 | 2019-07-05 10:31:49 | NULL | utf8_general_ci | NULL | | Stored Procedures |
| procs_priv | MyISAM | 10 | Fixed | 0 | 0 | 0 | 266275327968280575 | 4096 | 0 | NULL | 2019-07-05 10:31:46 | 2019-07-05 10:31:46 | NULL | utf8_bin | NULL | | Procedure privileges |
| proxies_priv | MyISAM | 10 | Fixed | 1 | 837 | 837 | 235594555506819071 | 9216 | 0 | NULL | 2019-07-05 10:31:48 | 2019-07-05 10:31:48 | NULL | utf8_bin | NULL | | User proxy privileges |
| server_cost | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | |
| servers | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | MySQL Foreign Servers table |
| slave_master_info | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Master Information |
| slave_relay_log_info | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Relay Log Information |
| slave_worker_info | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Worker Information |
| slow_log | CSV | 10 | Dynamic | 2 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | Slow log |
| tables_priv | MyISAM | 10 | Fixed | 2 | 947 | 1894 | 266556802944991231 | 9216 | 0 | NULL | 2019-07-05 10:31:46 | 2019-07-05 10:31:48 | NULL | utf8_bin | NULL | | Table privileges |
| time_zone | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zones |
| time_zone_leap_second | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Leap seconds information for time zones |
| time_zone_name | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zone names |
| time_zone_transition | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zone transitions |
| time_zone_transition_type | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2019-07-05 10:31:51 | NULL | NULL | utf8_general_ci | NULL | stats_persistent=0 | Time zone transition types |
| user | MyISAM | 10 | Dynamic | 5 | 123 | 616 | 281474976710655 | 4096 | 0 | NULL | 2019-07-05 10:31:46 | 2019-07-23 20:26:04 | NULL | utf8_bin | NULL | | Users and global privileges |
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+
31 rows in set (0.00 sec)

MySQL删除重复数据

一不小心犯了一个错误,导致数据库中的数据重复了,想把数据库中重复的数据删除,发现时间长了,mysql语句都忘了,复习一遍。

id name age email
1 lisi 12 lisi@gmail.com
2 lihua 24 lisi@gmail.com
3 wang3 36 lihua@gmail.com
4 zhao4 32 lihua@gmail.com
5 zhao5 22 lihua@gmail.com
6 zhao6 22 zhao6@gmail.com

数据如上,想删除邮箱重复的数据,并且只保留一条。

建表语句

1
2
3
4
5
6
7
8
9
10
11
12
DROP TABLE IF EXISTS `test_table`;
CREATE TABLE `test_table` (
`id` tinyint(4) NOT NULL AUTO_INCREMENT ,
`name` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ,
`age` tinyint(2) NULL DEFAULT NULL ,
`email` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
AUTO_INCREMENT=7
;

插入数据语句

1
2
3
4
5
6
INSERT INTO `test_table` (`id`, `name`, `age`, `email`) VALUES (1, 'lisi', 12, 'lisi@gmail.com');
INSERT INTO `test_table` (`id`, `name`, `age`, `email`) VALUES (2, 'lihua', 24, 'lisi@gmail.com');
INSERT INTO `test_table` (`id`, `name`, `age`, `email`) VALUES (3, 'wang3', 36, 'lihua@gmail.com');
INSERT INTO `test_table` (`id`, `name`, `age`, `email`) VALUES (4, 'zhao4', 22, 'lihua@gmail.com');
INSERT INTO `test_table` (`id`, `name`, `age`, `email`) VALUES (5, 'zhao5', 32, 'lihua@gmail.com');
INSERT INTO `test_table` (`id`, `name`, `age`, `email`) VALUES (6, 'zhao6', 22, 'zhao6@gmail.com');

查看数据重复次数

1
select id, email, count(email) as count from test_table group by email having count(email) > 1 order by count desc;
1
select id, 字段, count(字段) as count from 表名 group by 字段 having count(字段) > 1 order by count desc;

查询邮箱重复的数据(所有的,比较耗时)

1
2
3
# id是主键
select id, email from test_table where
email in (select email from test_table group by email having count(email) > 1)
1
select id, 字段 from 表名  where 字段 in (select 字段 from 表名 group by 字段 having count(字段) > 1)

删除邮箱重复的数据,保留id最小的一条

oracle sqlserver 可以用下面这条语句,但是mysql用这条语句会报错

1
2
3
delete from test_table  where 
email in (select email from test_table group by email having count(email) > 1)
and id not in (select min(id) as id from test_table group by email having count(email) > 1)

执行报错:1093 - You can’t specify target table ‘student’ for update in FROM clause
原因是:更新数据时使用了查询,而查询的数据又做了更新的条件,mysql不支持这种方式。oracel和msserver都支持这种方式。

怎么办,再加一层封装。如下:

1
2
3
4
5
delete from test_table  
-- 重复的数据
where email in (select email from (select email from test_table group by email having count(email) > 1) a )
-- 重复数据里去除id最小的
and id not in (select id from (select min(id) as id from test_table group by email having count(email) > 1) b )
1
2
3
4
5
delete from 表名  
-- 重复的数据
where 重复字段 in (select 重复字段 from (select 重复字段 from 表名 group by 重复字段 having count(重复字段) > 1) a )
-- 重复数据里去除id最小的
and 主键 not in (select 主键 from (select min(主键) as id from 表名 group by 重复字段 having count(重复字段) > 1) b )
1
2
3
delete from 表名 where 主键ID not in (select * from (select max(主键ID) from 表名 group by 重复字段 having count(重复字段) > 1) as b);

delete from un_visited_url where id not in ( select * from (select max(id) from un_visited_url group by unique_key having count(unique_key)>1 ) as b );

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
2019-11-06T09:12:00.044126Z	  850 Connect	root@localhost on  using TCP/IP
2019-11-06T09:12:00.114183Z 850 Query SET NAMES utf8mb4
2019-11-06T09:12:00.139959Z 850 Query show variables like 'profiling'
2019-11-06T09:12:00.224086Z 850 Query SHOW DATABASES
2019-11-06T09:12:00.248403Z 850 Query show variables like 'lower_case_table_names'
2019-11-06T09:12:00.266839Z 850 Query select SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA
2019-11-06T09:12:02.202479Z 851 Connect root@localhost on using TCP/IP
2019-11-06T09:12:02.213393Z 851 Query SET NAMES utf8mb4
2019-11-06T09:12:02.220314Z 851 Query USE `test`
2019-11-06T09:12:02.223651Z 851 Query select @@character_set_database
2019-11-06T09:12:02.242049Z 851 Query SET NAMES utf8mb4
2019-11-06T09:12:02.256033Z 851 Query select @@collation_database
2019-11-06T09:12:02.280248Z 851 Query USE `test`
2019-11-06T09:12:02.290210Z 851 Query SET NAMES utf8mb4
2019-11-06T09:12:02.293509Z 851 Query SHOW FULL TABLES WHERE Table_Type != 'VIEW'
2019-11-06T09:12:02.766408Z 851 Query SHOW TABLE STATUS

2019-11-06T09:18:52.613285Z 854 Connect root@localhost on using TCP/IP
2019-11-06T09:18:52.618798Z 854 Query SET NAMES utf8mb4
2019-11-06T09:18:52.626539Z 854 Query USE `test`
2019-11-06T09:18:52.645761Z 854 Query select * from `test`.`student` limit 0,100
2019-11-06T09:18:52.707339Z 854 Query show columns from `test`.`student`
2019-11-06T09:18:52.823659Z 854 Query show index from `test`.`student`
2019-11-06T09:18:52.848332Z 854 Query show create table `test`.`student`

2019-11-06T09:19:32.386415Z 851 Query show engines
2019-11-06T09:19:32.390662Z 851 Query SELECT DISTINCT(TABLESPACE_NAME) FROM information_schema.FILES WHERE NOT ISNULL(TABLESPACE_NAME)
2019-11-06T09:19:32.429012Z 851 Query SHOW CHARACTER SET
2019-11-06T09:19:32.432583Z 851 Query SHOW COLLATION
2019-11-06T09:19:32.470973Z 855 Connect root@localhost on using TCP/IP
2019-11-06T09:19:32.474990Z 855 Query SET NAMES utf8mb4
2019-11-06T09:19:32.479401Z 855 Query SHOW DATABASES
2019-11-06T09:19:32.479732Z 851 Query show columns from `test`.`student`
2019-11-06T09:19:32.482259Z 851 Query show index from `test`.`student`
2019-11-06T09:19:32.485231Z 851 Query select column_name, column_default from information_schema.columns where table_schema='test' and table_name='student'
2019-11-06T09:19:32.487267Z 851 Query SHOW CREATE TABLE `student`
2019-11-06T09:19:32.493814Z 851 Query show index from `test`.`student`
2019-11-06T09:19:32.494839Z 851 Query show create table `test`.`student`
2019-11-06T09:19:32.497089Z 851 Query select trigger_name, action_timing, event_manipulation, event_object_schema, event_object_table, action_statement from information_schema.triggers where event_object_schema = 'test' and event_object_table = 'student'
2019-11-06T09:19:32.498658Z 851 Query show table status like 'student'
2019-11-06T09:19:32.511728Z 851 Query SELECT *FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'student'
2019-11-06T09:19:32.513854Z 851 Query show create table `student`
2019-11-06T09:19:32.549343Z 851 Query SHOW DATABASES

# 刷新表
2019-11-06T09:20:32.824748Z 851 Query USE `test`
2019-11-06T09:20:32.825658Z 851 Query SET NAMES utf8mb4
2019-11-06T09:20:32.825941Z 851 Query SHOW FULL TABLES WHERE Table_Type != 'VIEW'
2019-11-06T09:20:32.956968Z 851 Query SHOW TABLE STATUS

# 刷新数据库
2019-11-06T09:20:58.442659Z 851 Query USE `test`
2019-11-06T09:20:58.444201Z 851 Query SET NAMES utf8mb4
2019-11-06T09:20:58.444490Z 851 Query select @@character_set_database
2019-11-06T09:20:58.444837Z 851 Query SET NAMES utf8mb4
2019-11-06T09:20:58.445188Z 851 Query select @@collation_database
2019-11-06T09:21:04.888733Z 850 Query SHOW DATABASES
2019-11-06T09:21:04.891149Z 850 Query select SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME from INFORMATION_SCHEMA.SCHEMATA

mysql in()查询结果按in集合顺序显示的方法

mysql in()查询结果按in集合顺序显示的方法
order by field (id, 2, 1, 3)

References

[1] Mysql删除重复记录,保留id最小的一条