mysql查建表时间_MySQL CRUD 以及 查询建表时间

MySQL CRUD 以及 查询建表时间

1. CURD for table

1.1 Create

create table populations(province varchar(30),population int(20));

1.2 Insert

insert into populations values("jiangxi",100000000),("heilongjiang",50000000);

插入指定字段

MariaDB [scm]> desc CM_VERSION;

+---------------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------------------+--------------+------+-----+---------+-------+

| VERSION | varchar(256) | NO | | NULL | |

| GUID | varchar(36) | NO | | NULL | |

| LAST_UPDATE_INSTANT | bigint(20) | YES | | NULL | |

| TS | bigint(20) | YES | | NULL | |

+---------------------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

MariaDB [scm]> insert into CM_VERSION (VERSION, GUID) values('6.2', 'xxxxx');

Query OK, 1 row affected (0.01 sec)

1.3 Update

update populations set population=60000000 where province="heilongjiang";

1.4 Delete

delete from populations where province="heilongjiang";

使用 metricbeat 上报 MySQL 的状态到 Elasticsearch，通过 Kibana 查看 MySQL 的关键性能指标。

1.5 快速加数据

快速插入数据 (空密码登录)

for i in {1..10000}; do USERNAME=root; PASSWORD=""; mysql --user=$USERNAME --password=${PASSWORD} -e 'insert into db_a.populations values("jiangxi",100000000),("heilongjiang",50000000);'; done

查询数据库大小

mysql --user=$USERNAME --password=${PASSWORD} -e "select concat(round((sum(data_length)+sum(index_length))/1024/1024,2),'MB') as data from information_schema.tables"

2. 其他信息

2.1 新增用户

grant all on dba.* to query@'127.0.0.1' identified by "xxxxx" ;

2. 查询表创建信息

mysql> show create table t1;;

+-------+-------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+-------------------------------------------------------------------------------------------+

| t1 | CREATE TABLE `t1` (

`a` int(11) DEFAULT NULL

) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |

+-------+-------------------------------------------------------------------------------------------+

mysql> select create_time, update_time from information_schema.tables where table_name='t1';

+-------------+-------------+

| create_time | update_time |

+-------------+-------------+

| NULL | NULL |

+-------------+-------------+

2.2 查询建表时间、大小

MariaDB [information_schema]> select TABLE_NAME,TABLE_ROWS,DATA_LENGTH,CREATE_TIME from tables order by data_length desc;

+----------------------------------------------+------------+-------------+---------------------+

| TABLE_NAME | TABLE_ROWS | DATA_LENGTH | CREATE_TIME |

+----------------------------------------------+------------+-------------+---------------------+

| history | 20469 | 2113536 | 2017-10-16 13:17:37 |

| images | 188 | 1589248 | 2017-10-16 13:17:37 |

| items | 1538 | 1572864 | 2017-10-16 13:17:37 |

| help_topic | 508 | 450388 | 2017-10-15 14:28:31 |

| triggers | 518 | 311296 | 2017-10-16 13:17:37 |

| items_applications | 1627 | 114688 | 2017-10-16 13:17:37 |

| functions | 1255 | 98304 | 2017-10-16 13:17:37 |

| help_keyword | 464 | 91408 | 2017-10-15 14:28:31 |

| graphs_items | 613 | 81920 | 2017-10-16 13:17:37 |

| trends | 727 | 81920 | 2017-10-16 13:17:37 |

reference

Copyright © 一条鱼 2019 赣ICP备19001536号-2 all right reserved，powered by Gitbook最后修订日期：

2020-02-29 14:58:07