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
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
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
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
2
3
4
5
6
7
8
9
10
11
12
13
14
reference
- [1] w3c. SQL INSERT INTO 语句open in new window