蒙国造博客

MySQL常用命令大全

为方便查询,特整理MySQL常用命令,如下。所有命令都亲手检验过,请放心使用:)

约定:$后为Shell环境命令,>后为MySQL命令。

1 常用命令

第一步,连接数据库。

$ mysql -u root -p                           # 进入MySQL bin目录后执行,回车后输入密码连接。
                                             # 常用参数:-h 服务器地址,-u 用户名,-p 密码,-P 端口

然后是一些常用的命令。 数据库操作:

> create database dbname;                     # 创建数据库,数据库名为dbname
> CREATE DATABASE `todo` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;  # 创建todo数据库,并指定字符集
> show databases;                                   # 显示所有数据库
> alter database character set utf8;         # 修改数据库字符集
> use dbname;                                        # 选择数据库
> status;                                                 # 查看当前选择(use)的数据库
> drop database dbname;                       # 删除数据库

数据表操作:

> show tables;                                           # 显示所有表
> describe tablename;                               # 表结构详细描述
> desc tablename;                                     # 同 describe 命令一样
> create table newtable like oldtable;        # 复制表结构
> insert into newtable select * from oldtable;       #复制表数据
> rename table tablelname to new_tablelname    # 重命名表,同时命名多个表用逗号“,”分割
> drop table tablename;                                      # 删除表

# 更改表的字符集
> alter table post char set utf8mb4 collate utf8mb4_unicode_520_ci;
# 更改字段的字符集
> alter table post modify title varchar(500) char set utf8mb4 collate utf8mb4_unicode_520_ci;

界面操作:

> select version(),current_date;             # 显示当前mysql版本和当前日期
> exit                                                    # 返回Shell命令行

2 创建数据表

create table命令用来创建数据表,格式:

create table <表名> (<字段名1> <类型1> [,..<字段名n> <类型n>]);

数据的类型之后还可以包含:数据宽度 + 是否为空 + 是否主键 + 自动增加 + 默认值,它们书写时不限顺序。但是字段名和字段类型必须是第一和第二位。例如,建立一个名为user的表:

mysql> create table user(
    -> id int(4) not null primary key auto_increment,
    -> name char(20) not null,
    -> sex int(4) not null default '0',
    -> degree double(16,2)
    -> );

3 修改root的密码

$ mysqladmin -u root password                         # 原始密码为空的情况
New password: <输入新的密码>
Confirm new password: <再次输入新密码>

$ mysqladmin -u root -p password                      # 原始密码不为空的情况
Enter password: <输入旧的密码>
New password: <输入新的密码>
Confirm new password: <再次输入新密码>

$ mysqladmin -uroot -p123456 password             # 原始密码不为空的情况,效果和第二种方法一样,只是显式的输入了原始密码
New password: <输入新的密码>
Confirm new password: <再次输入新密码>

4 备份和恢复

$ mysqldump -u root -p dbname > dbname.sql                                # 备份整个数据库(包含表结构和数据),如果导出库是主从结构,导出时关闭GTID:--set-gtid-purged=OFF
$ mysqldump -u root -p -d dbname > dbname.sql                            # 备份数据库表结构,不包含数据,
$ mysqldump -u root -p --no-create-info dbname > dbname.sql       # 备份数据库数据,不包含表结构
$ mysqldump -u root -p dbname tablename > tablename.sql           # 备份数据库中的某张数据表(包含表结构和数据)
$ mysqldump -u root -p dbname tablename1 tablename2 > tables.sql    # 备份数据库中2张数据表
$ mysqldump -u root -p -d dbname tablename > tablename.sql        # 备份数据库中的某张数据表的表结构(不含数据)

$ mysqladmin -u root -p create dbname       # 恢复数据库步骤1:创建数据库
$ mysql -u root -p dbname < dbname.sql      # 恢复数据库步骤2:恢复数据

如果是网络上的服务器,可以在mysqldump之后用-h指定服务器地址,例如:

$ mysqldump -h sql.domain.com -u root -p dbname > dbname.sql

导出查询结果到本地计算机:

mysql -h127.0.0.1 -P3306 -uroot -proot -Ae use test;select * from user where status=4 order by id desc; > C:\Users\Gary\user.txt

5 用户和权限管理

MySQL 默认有个root用户,但是这个用户权限太大,一般只在管理数据库时候才用。如果在项目中要连接 MySQL 数据库,则建议新建一个权限较小的用户来连接。在 MySQL 命令行模式下输入如下命令可以为 MySQL 创建一个新用户:

> CREATE USER username IDENTIFIED BY '123456';        # 其中,username是用户名,123456是用户密码

新用户创建完成,但是此刻如果以此用户登陆的话,会报错,因为我们还没有为这个用户分配相应权限,分配权限的命令是grant,格式为:

GRANT <权限> ON <数据库>.<表名> TO <用户名>@<登录主机> IDENTIFIED BY <密码>

其中,权限可以是:allselectinsertdeleteupdatedrop等值。 于是,用下面的命令就可以执行特定的功能:

> GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY '123456';                  # 给用户 username 分配所有数据库的所有权限
> REVOKE ALL ON *.* FROM 'username'@'localhost';                                      # 如果觉得上面的权限太大,用 REVOKE 删除原来权限
> GRANT ALL ON dbname.* TO 'username'@'localhost' IDENTIFIED BY '123456';             # 重新授予仅在dbname数据库上的权限
> GRANT SELECT, UPDATE ON dbname.* TO 'username'@'localhost' IDENTIFIED BY '123456';  # 仅授予select、update权限,无法执行insert、delete等命令
> FLUSH PRIVILEGES;                                                                   # 每当调整权限后,通常需要用这个命令刷新权限
> DROP USER username@localhost;                                                       # 删除用户

仔细观察上面几个命令,可以发现不管是授权,还是删除授权,都要指定响应的host(即@符号后面的登录主机,主机也可以用%通配符表示所有主机,或者192.168.1.% 表示特定主机段)。因为以上及格命令实际上都是在操作 mysql 数据库中的user表,可以用如下命令查看相应用户及对应的host:

SELECT user, host FROM user;

当然,这个表中还包含很多其它例如用户密码、权限设置等很多内容,操作时候尤其需要小心。

参考资料:

  1. 如何重设 MySQL 的 root 密码
  2. MySQL 数据库常用命令小结
  3. 使用mysqldump备份、恢复mysql数据库
  4. MySQL 用户管理及权限管理
  5. mysql用户与权限管理笔记
退出移动版