MySQL SQL笔记
(1) 查询MySQL版本
SELECT VERSION();
mysql> SELECT VERSION();
+------------+
| VERSION() |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.01 sec)(2) 查看当前库正在使用的表
SHOW OPEN TABLES WHERE in_use > 0 ;
mysql> SHOW OPEN TABLES WHERE in_use > 0 ;
Empty set (0.13 sec)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)(3) 查看正在运行的线程
SHOW PROCESSLIST ;
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)(4) mysql中如何查看某个数据库或表占用的磁盘空间
-- 查整个库的状态：
select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables where TABLE_SCHEMA = 'databasename';
-- 注意是**TABLE_SCHEMA** databasename查单表：
select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables where TABLE_NAME = 'tablename';
-- 注意是**TABLE_NAME**(4) 查看某个数据库所有表状态
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)
(5) MySQL删除重复数据
一不小心犯了一个错误，导致数据库中的数据重复了，想把数据库中重复的数据删除，发现时间长了，mysql语句都忘了，复习一遍。
| id | name | age | |
|---|---|---|---|
| 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 |
数据如上，想删除邮箱重复的数据，并且只保留一条。
建表语句
CREATE TABLE `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(8) NULL DEFAULT NULL ,
`age` tinyint(2) NULL DEFAULT NULL ,
`email` varchar(16) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 ;插入数据语句
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');查看数据重复次数
SELECT id, email, count(email) as count
FROM test_table
GROUP BY email
HAVING count(email) > 1
ORDER BY count DESC;查询邮箱重复的数据(所有的，比较耗时)
# id是主键
SELECT id, email
FROM test_table
WHERE email in (
SELECT email
FROM test_table
GROUP BY email
HAVING count(email) > 1
);删除邮箱重复的数据，保留id最小的一条
oracle sqlserver 可以用下面这条语句，但是mysql用这条语句会报错
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都支持这种方式。
怎么办，再加一层封装。如下：
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
);mysql in()查询结果按in集合顺序显示的方法
mysql in()查询结果按in集合顺序显示的方法
ORDER BY field (id, 2, 1, 3)
测试
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