为方便查询,特整理MySQL常用命令。
约定:$
后为Shell环境命令,>
后为MySQL命令。
1 常用命令
第一步,连接数据库。
$ sql>mysql -u root -p # 进入MySQL bin目录后执行,回车后输入密码连接。# 常用参数:-h 服务器地址,-u 用户名,-p 密码,-P 端口
然后是一些常用的命令。
数据库操作:
1 > create database dbname; # 创建数据库,数据库名为dbname 2 > CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; # 创建test数据库,并指定字符集 3 > show databases; # 显示所有数据库 4 > alter database character set utf8; # 修改数据库字符集 5 > use test; # 选择test数据库 6 > status; # 查看当前选择(use)的数据库 7 > drop database test; # 删除test数据库 8 > truncate 表名; # 快速清空表内容(数据量太大用deleter慢,可以用truncate) 9 > delete from 表名; # 清空表内容
数据表操作:
1 > show tables; # 显示所有表 2 > describe tablename; # 表结构详细描述 3 > desc tablename; # 同 describe 命令一样 4 > create table newtable like oldtable; # 复制表结构 5 > insert into newtable select * from oldtable; #复制表数据 6 > rename table tablelname to new_tablelname # 重命名表,同时命名多个表用逗号“,”分割 7 > drop table tablename; # 删除表
界面操作:
> select version(),current_date; # 显示当前sql>mysql版本和当前日期 > exit
2 创建数据表
create table
命令用来创建数据表,格式:
create table <表名> (<字段名1> <类型1> [,..<字段名n> <类型n>]);
数据的类型之后还可以包含:数据宽度 + 是否为空 + 是否主键 + 自动增加 + 默认值,它们书写时不限顺序。但是字段名和字段类型必须是第一和第二位。例如,建立一个名为user的表:
sql>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的密码
$ sql>mysqladmin -u root password # 原始密码为空的情况 New password: <输入新的密码> Confirm new password: <再次输入新密码>$ sql>mysqladmin -u root -p password # 原始密码不为空的情况 Enter password: <输入旧的密码> New password: <输入新的密码> Confirm new password: <再次输入新密码>$ sql>mysqladmin -uroot -p123456 password # 原始密码不为空的情况,效果和第二种方法一样,只是显式的输入了原始密码 New password: <输入新的密码> Confirm new password: <再次输入新密码>
4 备份和恢复
$ sql>mysqldump -u root -p dbname > dbname.sql # 备份整个数据库(包含表结构和数据) $ sql>mysqldump -u root -p -d dbname > dbname.sql # 备份数据库表结构,不包含数据, $ sql>mysqldump -u root -p dbname tablename > tablename.sql # 备份数据库中的某张数据表(包含表结构和数据) $ sql>mysqldump -u root -p dbname tablename1 tablename2 > tables.sql # 备份数据库中2张数据表 $ sql>mysqldump -u root -p -d dbname tablename > tablename.sql # 备份数据库中的某张数据表的表结构(不含数据)$ sql>mysqladmin -u root -p create dbname # 恢复数据库步骤1:创建数据库 $ sql>mysql -u root -p dbname < dbname.sql # 恢复数据库步骤2:恢复数据
如果是网络上的服务器,可以在sql>mysqldump
之后用-h
指定服务器地址,例如:
$ sql>mysqldump -h sql.domain.com -u root -p dbname > dbname.sql
导出查询结果到本地计算机:
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 "<密码>"
其中,权限可以是:all
、select
、insert
、delete
、update
、drop
等值。
于是,用下面的命令就可以执行特定的功能:
> 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.%
表示特定主机段)。因为以上及格命令实际上都是在操作 sql>mysql 数据库中的user表,可以用如下命令查看相应用户及对应的host
:
SELECT user, host FROM user;
当然,这个表中还包含很多其它例如用户密码、权限设置等很多内容,操作时候尤其需要小心。