大数据学习之路

MySQL CRUD 以及 查询建表时间

1. CURD for table

1.1 Create

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

1.2 Insert

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

插入指定字段

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
2
3
4
5
6
7
8
9
10
11
12
13

1.3 Update

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

1.4 Delete

delete from populations where province="heilongjiang";
1

使用 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
1
  • 查询数据库大小
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"
1

2. 其他信息

2.1 新增用户

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

2. 查询表创建信息

mysql> show create table t1;;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table                                                                              |
+-------+-------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 | 
+-------+-------------------------------------------------------------------------------------------+
1
2
3
4
5
6
7
8
mysql> select create_time, update_time from information_schema.tables where table_name='t1';
+-------------+-------------+
| create_time | update_time |
+-------------+-------------+
| NULL        | NULL        | 
+-------------+-------------+
1
2
3
4
5
6

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 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14

reference