MySQL命令行
用法举例
database=数据库
删除database数据库
drop database xxx;
table=表
查看当前已打开的表
show open tables;
查看当前已打开的,正在使用的表
show open tables where in_use>0;
查看表结构
describe table_name;
举例
MySQL [xxx]> describe keyword;
+------------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(100) | NO | MUL | NULL | |
| type | char(20) | NO | | NULL | |
| createTime | datetime | YES | | NULL | |
| modifyTime | datetime | YES | | NULL | |
+------------+-----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
查看已有表的创建create的命令
SHOW CREATE TABLE tbl_name
举例
MySQL [xxx]> SHOW CREATE TABLE keyword;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| keyword | CREATE TABLE `keyword` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(100) NOT NULL,
`type` char(20) NOT NULL,
`createTime` datetime DEFAULT NULL,
`modifyTime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `Multi` (`name`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=7529 DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
record=记录=数据=值
插入数据
对于自增ID的数据插入可以省略ID
对于表结构是:
CREATE TABLE `keyword` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(100) NOT NULL,
`type` char(20) NOT NULL,
`createTime` datetime DEFAULT NULL,
`modifyTime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `Multi` (`name`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
其中id
是AUTO_INCREMENT
插入值时,可以省略ID:
INSERT INTO `keyword`(`name`,`type`,`createTime`,`modifyTime`) VALUES("topic2","topic","2018-07-13 10:09:27","2018-07-13 10:09:27");
更新数据
UPDATE keyword SET name="animal" WHERE type="sectorTopic" AND id=5792;
UPDATE keyword SET name="ocean animal" WHERE type="topic" AND name="ocean animals";
删除特定数据
SELECT * FROM keyword WHERE type="topic" AND name="basketball";
id=7426
DELETE FROM keyword_rel WHERE keyword2=7426;
删除多个数据
比如id在4
到13
之间,包括4
和13
DELETE FROM user WHERE id>=4 AND id<=13;
DELETE FROM user WHERE BETWEEN 4 AND 13;
mysql全局
查看当前进程
查看当前活跃的进程的列表:
show processlist;
举例
mysql> show processlist;
+----+------+-----------------+-----------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+-----------+---------+------+----------+------------------+
| 2 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 28 | root | localhost:53199 | nxxxg | Sleep | 404 | | NULL |
+----+------+-----------------+-----------+---------+------+----------+------------------+
2 rows in set (0.01 sec)
杀死进程
kill process_id;
查看当前的mysql最大连接
show variables like 'max_connections';
查看当前active活跃的连接数
show status where `variable_name` = 'Threads_connected';
查看字符编码
SHOW VARIABLES LIKE "character_set_server";
SHOW VARIABLES LIKE "collation_server";