工作中经常用会遇到这种情况,可以访问mysql所在的服务器,但是服务器端口不对外暴露(通常因为安全原因)。这时,操作数据库只能通过命令行和 mysql client
窗口来实现。我对这些操作进行一些汇总,方便以后随时查阅。
一、登录登出mysql client
登录 mysql client
比较简单,执行下面的命令回车,输出root用户密码,即可进入。
mysql -u root -p
登出只需执行 退出命令 exit
或使用快捷键 CTRL + C
。
二、操作数据
登录至 mysql client
后,可以操作常见的 DDL
、 DML
、 DQL
和 DCL
语句。
创建数据库,执行以下语句。
CREATE DATABASE your_db_name DEFAULT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
上述语句创建了名为 your_db_name
的数据库,且设置了该数据库默认的字符集为 uft8mb4
。这里注意 mysql
数据库的命名不支持短中杠( -
),多个单词的拼接用下划线( _
)即可。
删除数据库,只需执行 DROP DATABASE
命令。
DROP DATABASE your_db_name;
展示当前用户下,全量的数据库列表。
SHOW DATABASES;
如果要查询特定的数据,可以使用 LIKE
进行过滤查询。
SHOW DATABASE LIKE 'your_db%';
要进入刚才创建的数据库 your_db_name
,执行下面的语句。
USE your_db_name;
我们尝试创建一个表 your_table_name
。假如该表不存在,则创建;如果存在,则不执行。
CREATE TABLE IF NOT EXISTS your_table_name
(
id
BIGINT NOT NULL COMMENT '主键' AUTO_INCREMENT,
your_field_name1
VARCAHR(10) NOT NULL DEFAULT '' COMMENT '你创建的字段1',
your_field_name2
CHAR(1) NOT NULL DEFAULT '0' COMMENT '你创建的字段2',
PRIMARY KEY (id
) USING BTREE
)
ENGINE = InnoDB
COLLATE = 'utf8mb4_unicode_ci'
COMMENT = '你的表名'
;
如果想看某张表的建表语句,执行下面的语句:
SHOW CREATE TABLE your_table_name;
通过 drop table
语句删除表。
DROP TABLE your_table_name
;
如果想快速备份当前的表(your_table_name
),可以使用 rename table
命令,把当前表重命名为其他表(例如 your_table_name_bak
)。
RENAME TABLE your_table_name TO your_table_name_bak;
注意:
重命名的表名最好不要带上.
符号,比如your_table_name.bak
。这是因为,sql 解析器有可能误认为.
符号前面的your_table_name
是数据库名。
它会报下面的错误:
ERROR 1049 (42000): Unknown database ‘your_table_name’
已有一张表(A)(your_table_name
),如果想快速复制一张表(A_2)(your_table_name2
),表(A_2)与表(A)完全相同,但是不含(A)表中的数据。我们可以这么执行:
CREATE TABLE your_table_name2 SELECT * FROM your_table_name LIMIT 0;
如果只是想快速复制表(A_3)(your_table_name3
),(A_3)表只包含(A)表的部分字段,且同(A_2)一样不包含(A)表中的数据。可以这么执行:
CREATE TABLE your_table_name3 SELECT id FROM your_table_name LIMIT 0;
如果想快速复制表(A_4)(your_table_name4
),(A_4)表包含(A)表的所有字段,并且包含(A)表的所有数据。可以这么执行:
CREATE TABLE your_table_name4 SELECT * FROM your_table_name;
如果想快速复制表(A_5)(your_table_name5
),(A_5)表包含(A)表的所有字段,并且包含 A
表的若干条数据,比如 1
条。可以这么执行:
CREATE TABLE your_table_name5 SELECT * FROM your_table_name LIMIT 1;
但是 注意,上述执行方式创建的复制表,都只保留原表的字段相关信息,但是会丢弃掉原表的主键、索引等信息,如果要复制一张完全相同表结构的表,可以使用 LIKE
来执行。
CREATE TABLE your_table_same_strut_name LIKE your_table_name;
假设有两张表,表(A)( your_table_name
)和表(B)( another_table_name
),两者字段完全相同,或部分相同,或字段含义接近,表(A)有全量数据,把表(A)的数据一次性同步到表(B),可以通过下面的步骤实现。
初始条件,
已知表A (your_table_name
)
CREATE TABLE IF NOT EXISTS your_table_name
(
id
BIGINT NOT NULL COMMENT '主键' AUTO_INCREMENT,
your_field_name1
VARCHAR(10) NOT NULL DEFAULT '' COMMENT '你创建的字段1',
your_field_name2
CHAR(1) NOT NULL DEFAULT '0' COMMENT '你创建的字段2',
PRIMARY KEY (id
) USING BTREE
)
ENGINE = InnoDB
COLLATE = 'utf8mb4_unicode_ci'
COMMENT = '你的表名'
;
表B(another_table_name)
CREATE TABLE another_table_name
(
id
BIGINT(20) NOT NULL COMMENT '主键' AUTO_INCREMENT,
field_name1
VARCHAR(10) NOT NULL DEFAULT '' COMMENT '字段1' COLLATE 'utf8mb4_unicode_ci',
field_name2
CHAR(1) NOT NULL DEFAULT '0' COMMENT '字段2' COLLATE 'utf8mb4_unicode_ci',
field_name3
INT(11) NULL DEFAULT NULL COMMENT '字段3',
PRIMARY KEY (id
) USING BTREE
)
COMMENT='另外一张表'
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;
又已知表(A)( your_table_name
)和表(B)( another_table_name
)前3个字段含义相同。那么同步表(A)的数据至表(B),可以按以下方式执行:
INSERT INTO another_table_name
(id
, field_name1
, field_name2
)
SELECT id
, your_field_name1
, your_field_name2
FROM your_table_name
;
同样,同步表(A)( your_table_name
)的部分数据至表(B)( another_table_name
),比如只同步前 2
条数据。执行语句如下:
INSERT INTO another_table_name
(id
, field_name1
, field_name2
)
SELECT id
, your_field_name1
, your_field_name2
FROM your_table_name
LIMIT 2;
如果表中数据没有用了,表又比较大,那么执行下面的语句可以快速清空并重置当前表。为什么说该操作有重置的作用,假设你的主键是自增的( AUTO INCREMENT
),全量删除( DELETE *
)数据后,再添加数据,主键不会从 1
开始递增,而是接继之前的主键值继续递增;但是执行 TRUNCATE
命令后,主键值会重新从 1
开始递增。
TRUNCATE TABLE your_table_name
;
注意:
生产环境 慎用。
SHOW TABLES;
如果要列出特定的表,则可以用 LIKE
进行过滤查询。
SHOW TABLES LIKE '%your_table_name%';
SHOW TABLE STATUS WHERE NAME
= 'your_table_name';
这是可以查询到表当前的相关信息,比如表名、表引擎、版本、数据行格式、数据行数、表创建时间、表更新时间、字符集、表注释等等。
除了创建表时添加字段外,还可以通过 ALTER ADD COLUMN
语句添加字段。
-- 添加一个字段
ALTER TABLE your_table_name
ADD COLUMN your_field_name3
DECIMAL(8,2) NOT NULL DEFAULT 0 COMMENT '字段3' AFTER your_field_name2
;
-- 添加多个字段 以半角逗号分割
ALTER TABLE your_table_name
ADD COLUMN your_field_name4
BIT NOT NULL DEFAULT FALSE COMMENT '字段4' AFTER your_field_name3
,
ADD COLUMN your_field_name5
TIMESTAMP NULL COMMENT '字段5' AFTER your_field_name4
;
如果不指定字段在特定字段后面,则该字段默认在表的末尾处添加。
变更表字段,使用 ALTER CHANGE COLUMN
语句。
-- 变更一个字段
ALTER TABLE your_table_name
CHANGE COLUMN your_field_name3
your_field_new_name3
DECIMAL(8,3) NOT NULL DEFAULT 0 COMMENT '新字段3';
-- 变更多个字段 以半角逗号分割
ALTER TABLE your_table_name
CHANGE COLUMN your_field_name4
your_field_new_name4
TINYINT NOT NULL DEFAULT 0 COMMENT '新字段4',
CHANGE COLUMN your_field_name5
your_field_new_name5
DATETIME NULL COMMENT '新字段5';
删除字段,使用 ALTER DROP COLUMN
语句。
-- 删除一个字段
ALTER TABLE your_table_name
DROP COLUMN your_field_new_name3
;
-- 删除多个字段 以半角逗号分割
ALTER TABLE your_table_name
DROP COLUMN your_field_new_name4
,
DROP COLUMN your_field_new_name5
;
有时要查看表中有哪些字段,可以通过 SHOW COLUMNS
语句实现。
SHOW COLUMNS FROM your_table_name
;
查询后的效果如下所示。我们可以得到字段名、字段类型、是否为Null,Key值,默认值和额外信息。
mysql> SHOW COLUMNS FROM your_table_name
;
+------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| your_field_name1 | varchar(10) | NO | | | |
| your_field_name2 | char(1) | NO | | 0 | |
+------------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
上述语句同下面的语句等价。
SHOW FIELDS FROM your_table_name
;
如果要展示字段的更多信息,可以使用下面的语句。相较于上面的语句,增加了字符集信息,权限信息和注释信息。
SHOW FULL FIELDS FROM your_table_name
;
如果要列出特定字段,可以使用 LIKE
关键字进行过滤查询。
SHOW FULL FIELDS FROM your_table_name
LIKE 'id';
使用 ADD INDEX
执行添加索引操作:
还可以添加联合索引:
ALTER TABLE your_table_name
ADD INDEX your_composite_index_name
(your_field_name1
, your_field_name2
);
如果要添加唯一索引,则可以增加 UNIQUE
(唯一性)关键字执行:
ALTER TABLE your_table_name
ADD UNIQUE INDEX your_index_name
(your_field_name1
);
如果要添加主键,那么可以执行:
ALTER TABLE your_table_name
ADD PRIMARY KEY (id
);
使用 DROP INDEX
执行索引的丢弃操作。
ALTER TABLE your_table_name
DROP INDEX your_index_name
;
删除主键,执行:
ALTER TABLE your_table_name
DROP PRIMARY KEY;
SHOW INDEX FROM
命令可以用来查看某张表下所有索引信息。
SHOW INDEX FROM your_table_name
;
三、备份和还原数据
备份和还原数据,通常使用 mysqldump
命令和 source
命令(在 mysql client
内执行)。
备份操作特别适合于数据的全量导出的情况,特别是数据存在二进制( binary
或 blob
)的情况。
linux
环境下,可以执行:
mysqldump -u root -p your_db_name > /home/your_db_name.sql
windows
环境下,需要指定具体盘符:
mysqldump -u root -p your_db_name > E:/your_db_name.sql
查看 mysqldump
操作帮助( mysqldump --help
),可知,添加 -d
操作符,可以导出不包含数据的库表信息。
-d, --no-data No row information.
于是,备份单库但不要库内数据的执行语句如下:
mysqldump -u root -p -d your_db_name > /home/your_db_name_without_data.sql
使用 -B
操作符,可以指定多个库。
使用 --tables
操作符,可以指定多个表。
mysqldump -u root -p your_db_name --tables your_table_name your_table_name2 > /home/your_table_names.sql
mysqldump -u root -p -d your_db_name --tables your_table_name your_table_name2 > /home/your_table_names_without_data.sql
使用 --ignore-table=name
操作符,可以在导出库的同时,忽略特定的表。
mysqldump -u root -p your_db_name --ignore-table=your_db_name.another_table_name --ignore-table=your_db_name.your_table_name_bak > /home/your_db_name_ignore_tables.sql
注意:
要忽略的表名一定要书写正确。否则会出现执行了明明忽略操作,但是对应的表仍会导出的错觉。
该操作需要在 mysql client
中执行。
假设要还原某个库里面的数据,可以通过下面简单的两步实现还原库中数据的操作。
首先,切换到要还原的库。
USE your_db_name;
接着,在库内执行 SOURCE
命令。 SOURCE
后跟的是导出的或者你已准备好的数据的实际路径。
SOURCE /home/your_db_name.sql
mysqldump
同样支持把备份的数据还原回去,只需执行的时候,把指向箭头掉个方向。
mysqldump -u root -p your_db_name < /home/your_db_name.sql
注意:
通常 不推荐使用这种方式,因为这种方式会机械的执行库表的还原。如果库名不对应,执行的预期不是我们想要的。
四、授权和收回
使用 GRANT TO
命令进行库表的授权。
我们先尝试创建一个本地用户 custom_user
, 并为其设置密码 customPassword
。
CREATE USER 'custom_user'@'localhost' IDENTIFIED BY 'customPassword';
授权本地用户 custom_user
拥有 your_db_name
的所有权限。
GRANT ALL PRIVILEGES ON your_db_name.* TO 'custom_user'@'localhost';
FLUSH PRIVILEGES;
授权本地用户 custom_user
拥有 your_db_name
的特定表(your_table_name
、 your_table_name2
)的所有权限。
GRANT ALL PRIVILEGES ON your_db_name.your_table_name TO 'custom_user'@'localhost';
GRANT ALL PRIVILEGES ON your_db_name.your_table_name2 TO 'custom_user'@'localhost';
FLUSH PRIVILEGES;
可以授权本地用户 custom_user
只拥有 your_db_name
的特定表(your_table_name
、 your_table_name2
)的查询权限。
GRANT SELECT ON your_db_name.your_table_name TO 'custom_user'@'localhost';
GRANT SELECT ON your_db_name.your_table_name2 TO 'custom_user'@'localhost';
FLUSH PRIVILEGES;
注意:
这里授权查询操作需保证,对应库表存在。
收回权限使用 REVOKE FROM
命令。
收回所有之前赋给本地用户 custom_user
对于特定库 your_db_name
的权限。
REVOKE ALL PRIVILEGES ON your_db_name.* FROM 'custom_user'@'localhost';
FLUSH PRIVILEGES;
注意:
授权是可以累计的。因此,权限收回要与授权保持 一一对应。否则,会出现权限收回了,但仍能看到被授权相关信息的错觉。
REVOKE SELECT ON your_db_name.your_table_name FROM 'custom_user'@'localhost';
REVOKE SELECT ON your_db_name.your_table_name2 FROM 'custom_user'@'localhost';
FLUSH PRIVILEGES;
五、其他
查看磁盘空间占用的目的,是方便在数据导出等操作时做出合理选择,比如,避免导出不重要但很大的数据。
SELECT
table_schema as '数据库',
SUM(table_rows) as '记录数',
SUM(TRUNCATE(data_length/1024/1024, 2)) as '数据容量(MB)',
SUM(TRUNCATE(index_length/1024/1024, 2)) as '索引容量(MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length) DESC, SUM(index_length) DESC;
SELECT
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
TRUNCATE(data_length/1024/1024, 2) as '数据容量(MB)',
TRUNCATE(index_length/1024/1024, 2) as '索引容量(MB)'
FROM information_schema.tables
WHERE table_schema='your_db_name'
ORDER BY data_length DESC, index_length DESC;
通常在执行一些大表数据的删除之后,数据库并不会立即释放磁盘空间。这时,执行 OPTIMIZE
命令可以用来释放相应的磁盘空间。
OPTIMIZE TABLE your_table_name;
Original: https://www.cnblogs.com/lunyu/p/16630611.html
Author: 论语
Title: mysql常用操作汇总
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/611904/
转载文章受原作者版权保护。转载请注明原作者出处!