MySQL 常用命令

发表于 | 分类于 ，
字数统计: 1.9k | 阅读时长 ≈ 9

在日常的维护和管理中，会用到非常多的SQL语句，熟练使用对日常工作有很多好处。

下面列一些常用的命令供以后查看。

1、显示数据库

1
mysql> show databases;

列出数据库下所有表

1
mysql> show tables;

2、创建用户

创建root用户密码为123

1
2
3
mysql> use mysql; 
grant all on *.* to root@'%' identified by '123' with grant option; 
mysql> commit;

新增超级权限并允许远程访问：

1
2
mysql> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;   
mysql> FLUSH   PRIVILEGES;

用户能读取和修改已有表的内容，但又不允许创建新表或删除表，可按如下授权：

1
mysql> GRANT SELECT,UPDATE ON samp_db.* TO 'user'@'%' IDENTIFIEDBY "pass";

设置该用户只有show database权限

1
mysql> grant SHOW DATABASES on *.* to test@"%" identified by "pass";

增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。

1
2
mysql> grant select,insert,update,delete on *.* to test1@"%" Identified by "abc"; 
mysql> flush privileges;

增加一个用户test2密码为abc, 让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作。

1
mysql> grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";

增加一个用户test3密码为abc, 让他只可以在localhost上登录，并可以对数据库mydb下的表进行创建、删除操作。

1
mysql> grant create,drop on mydb.* to test3@localhost identified by "abc";

增加一个用户test对testDB库有所有权限

1
mysql> grant all privileges on testDB.* to test@'%' identified by '1234';

查看用户的登录权限

1
mysql> show grants for test@'192.168.1.23';

查看用户具体权限

1
mysql> select * from mysql.user where user='test'\G;

修改用户权限

1
2
3
4
5
6
7
8
9
10
11
12
13
#创建库
mysql> create database registry;

#创建用户
mysql> CREATE USER 'registry'@'%' IDENTIFIED BY 'R12$%34qw';

#为用户授权
mysql> GRANT ALL PRIVILEGES ON registry.* TO 'registry'@'%' WITH GRANT OPTION ;
mysql> commit;

#修改权限为只读
mysql> GRANT select ON registry.* TO 'registry'@'%' WITH GRANT OPTION ;
mysql> commit;

3、修改密码

1
2
3
mysql> grant all on *.* to xing@'localhost' identified by '123456' with grant option; 
mysql> update user set password = password('newpwd') where user = 'xing' and host='localhost'; 
mysql> flush privileges;

修改密码

1
2
3
4
5
6
mysql> update mysql.user set authentication_string=password('root') where user='root' and host='localhost';
或
mysql> set password for 'root'@'localhost' =password('password');
或：
mysql> use mysql;
mysql> update user set password=password('123456') where user='root';

当前用户登录下修改密码

1
mysql> SET PASSWORD = PASSWORD("newpassword");

4、删除用户

删除用户cvxcloud

1
mysql> drop user cvxcloud@'192.168.0.0/255.255.0.0';

5、创建数据库testdb

1
mysql> create database testdb;

6、预防性创建数据库

1
mysql> create database if not testdb;

7、创建表

1
2
3
4
mysql> use testdb; 
mysql> create table table1( 
username varchar(12), 
password varchar(20));

创建后表的修改
alter table 语句用于创建后对表的修改, 基础用法如下:
添加列

alter table 表名 add 列名 列数据类型 [after 插入位置];

在表的最后追加列

1
mysql> address: alter table students add address char(60);

在名为 age 的列后插入列 birthday:

1
mysql> alter table students add birthday date after age;

重命名表
基本形式:

1
mysql> alter table 表名 rename 新表名;

重命名 students 表为 workmates:

1
mysql> alter table students rename workmates;

8、预防性创建表aaa

1
mysql> create table if not exists aaa(ss varchar(20));

9、查看表结构

1
mysql> describe table1;

10、插入数据到表table1

1
2
3
4
mysql> insert into table1(username,password) values 
('leizhimin','lavasoft'), 
('hellokitty','hahhahah'); 
mysql> commit;

更新表中的数据
update 语句可用来修改表中的数据, 基本的使用形式为:

1
mysql> update 表名称 set 列名称=新值 where 更新条件;

将id为5的手机号改为默认的”-“:

1
mysql> update students set tel=default where id=5;

将所有人的年龄增加1:

1
mysql> update students set age=age+1;

将手机号为 13288097888 的姓名改为 “张伟鹏”, 年龄改为 19:

1
mysql> update students set name="张伟鹏", age=19 where tel="13288097888";

11、查询表table1

1
mysql> select * from table1;

查询表数据并保存为文件

1
# mysql -h 127.0.0.1 -u vbox -p -e " use vboxserver;SELECT * from live_radio where radio_source  = 'QT' LIMIT 0,100" > /work/admin/downloads/20180312/live_radio.xls

登录mysql中查询导出为文件

1
mysql> select * FROM action where create_time < '2016-0-1 00:00:00' into outfile '/tmp/file.xls';

12、更改数据

1
2
mysql> update table1 set password='hehe' where username='hellokitty'; 
mysql> commit;

13、删除数据

1
2
mysql> delete from table1 where username='hellokitty'; 
mysql> commit;

14、给表添加一列

1
2
3
4
5
mysql> alter table table1 add column( 
 sex varchar(2) comment '性别', 
 age date not null comment '年龄' 
); 
mysql> commit;

15、修改表结构

从查询创建一个表table1

1
2
mysql> create table tmp as table1;
mysql> select * from table1;

基本格式:

alter table 表名 change 列名称 列新名称 新数据类型;

将表 tel 列改名为 telphone:

1
mysql> alter table students change tel telphone char(13) default "-";

将 name 列的数据类型改为 char(16):

1
mysql> alter table students change name name char(16) not null;

删除列
基本形式:

alter table 表名 drop 列名称;

删除 birthday 列:

1
mysql> alter table students drop birthday;

16、删除表table1

1
2
mysql> drop table if exists table1; 
mysql> drop table if exists tmp;

delete 语句用于删除表中的数据, 基本用法为:

delete from 表名称 where 删除条件;

删除id为2的行:

1
mysql> delete from students where id=2;

删除所有年龄小于21岁的数据:

1
mysql> delete from students where age<20;

删除表中的所有数据:

1
mysql> delete from students;

17、备份数据库testdb

备份时使用--master-data 参数便于回滚时查找pos点

1
# mysqldump -h 192.168.3.143 -u root -p pwd --master-data=2 -x --default-character-set=gbk > /testdb.sql

备份表
导出db1中的a1、a2表

1
# mysqldump -uroot -proot --master-data=2 --databases db1 --tables a1 a2  >/tmp/db1.sql

导出整个数据库结构和数据

1
# mysqldump -h localhost -uroot --master-data=2 -p123456 database > dump.sql

导出单个数据表结构和数据

1
# mysqldump -h localhost -uroot -p123456 --master-data=2  database table > dump.sql

导出整个数据库结构（不包含数据）

1
# mysqldump -h localhost -uroot -p123456 --master-data=2 -d database > dump.sql

导出单个数据表结构（不包含数据）

1
# mysqldump -h localhost -uroot -p123456 --master-data=2 -d database table > dump.sql

备份表中的数据到另一个表

1
mysql> create table biz_column_info_bak select * from biz_column_info;

复制ifly_cti_music库中的数据到xy_cti_music库中

1
# mysqldump ifly_cti_music -u root -p******** --add-drop-table | mysql xy_cti_music -u root -p********

18、删除数据库testdb

1
mysql> drop database testdb;

19、恢复testdb数据库

首先先建立testdb数据库，然后用下面命令进行本地恢复

1
# mysql -u root -pleizhimin testdb /testdb.sql

对于超大的sql文件导入source

1
mysql> source /work/admin/data/mysql_backup/back.sql;

20、查看数据库连接信息

显示mysql连接列表

1
mysql> show processlist;

查询数据库当前设置的最大连接数

1
mysql> show variables like '%max_connections%';

查看当前连接

1
mysql> show status like 'Threads%';

显示连接状态

1
mysql> SHOW STATUS LIKE '%connect%';

21、数据库密码找回

mysql密码忘了如何重设密码：
方法一：

1
2
3
4
5
6
7
8
9
10
11
12
13
# /etc/init.d/mysqld stop 
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking & 
# mysql -u root mysql 

mysql> UPDATE user SET Password=PASSWORD(’newpassword’) where USER=’root’; 
mysql> FLUSH PRIVILEGES; 
mysql> quit 

# /etc/init.d/mysqld restart 
# mysql -uroot -p 

Enter password: <输入新设的密码newpassword> 
mysql>

方法二：
直接使用/etc/mysql/debian.cnf文件中[client]节提供的用户名和密码:

1
2
3
4
5
6
7
8
9
10
11
# mysql -udebian-sys-maint -p 

Enter password: <输入[client]节的密码> 
mysql> UPDATE user SET Password=PASSWORD(’newpassword’) where USER=’root’; 
mysql> FLUSH PRIVILEGES; 
mysql> quit 

mysql -uroot -p 

Enter password: <输入新设的密码newpassword> 
mysql>

mysql 5.7
如果忘记root密码，则按如下操作恢复：
在[mysqld]的段中加上一句：skip-grant-tables 保存并且退出vi。

1
2
3
# mysql  -u root
mysql> update mysql.user set authentication_string=password('123qwe') where user='root' and Host = 'localhost';
mysql> flush privileges
-------------本文结束感谢您的阅读-------------
分享到： 收藏夹 复制网址 邮件 微信 QQ空间 腾讯微博 豆瓣 一键分享 更多