mysql常用命令

 

1、  连结mysql

<span>mysql –h </span><span style="font-family:宋体;">主机地址</span> <span>–u </span><span style="font-family:宋体;">用户名</span> <span>–p </span><span style="font-family:宋体;">用户密码</span>

2、  修改密码

<span>mysqladmin –u </span><span style="font-family:宋体;">用户名</span> <span>–p </span><span style="font-family:宋体;">旧密码</span><span> password </span><span style="font-family:宋体;">新密码</span>

3、  增加新用户

<span>grant <</span><span style="font-family:宋体;">权限</span><span>> on </span><span style="font-family:宋体;">数据库</span><span>.* to </span><span style="font-family:宋体;">用户名</span><span>@</span><span style="font-family:宋体;">登陆主机</span><span> identified by “</span><span style="font-family:宋体;">密码</span><span>”</span>



<span>4、查看现有所有用户</span>



<span><span style="white-space:nowrap;">SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;</span><br />

<span style="font-family:宋体;">数据库</span><span>/</span><span style="font-family:宋体;">数据表</span><span>/</span><span style="font-family:宋体;">数据列权限</span><span>:</span>



<span>ALTER:</span><span style="font-family:宋体;">修改已存在的数据库(例如增加</span><span>/</span><span style="font-family:宋体;">删除列)和索引,</span>



<span>CREATE:</span><span style="font-family:宋体;">建立新的数据库或数据表</span>



<span>DELETE:</span><span style="font-family:宋体;">删除表的记录</span>



<span>DROP:</span><span style="font-family:宋体;">删除数据表或数据库</span>



<span>INDEX:</span><span style="font-family:宋体;">建立或删除索引</span>



<span>SELECT:</span><span style="font-family:宋体;">显示</span><span>/</span><span style="font-family:宋体;">搜索表的记录</span>



<span>UPDATE:</span><span style="font-family:宋体;">修改表中已存在的记录</span>



<span style="font-family:宋体;">全局管理权限:</span>



<span>FILE:</span><span style="font-family:宋体;">在</span><span>mysql</span><span style="font-family:宋体;">服务器上读写文件</span>



<span>PROCESS:</span><span style="font-family:宋体;">显示或杀死属于其他用户的服务线程</span>



<span>RELOAD:</span><span style="font-family:宋体;">重载访问控制表、刷新日志等</span>



<span>SHUTDOWN:</span><span style="font-family:宋体;">关闭</span><span>mysql</span><span style="font-family:宋体;">服务</span>



<span style="font-family:宋体;">特别的权限:</span>



<span>ALL:</span><span style="font-family:宋体;">允许做任何事(和</span><span>root</span><span style="font-family:宋体;">一样)</span>



<span>USAGE:</span><span style="font-family:宋体;">只允许登陆,其他什么也不允许做</span>



<span>grant select,insert,update,delete on *.* to test1@”%” Identified by “abc”;</span>



<span style="font-family:'Verdana','sans-serif';color:#555555;font-size:9pt;">FLUSH PRIVILEGES;</span>



<span>%</span><span style="font-family:宋体;">指所有主机</span>



<span>use mysql ;</span><span style="font-family:宋体;"> </span><span>show tables;</span><span style="font-family:宋体;"> </span> <span style="font-family:宋体;">显示</span><span>mysql</span><span style="font-family:宋体;">中的表 </span>



<span>show databases;</span><span style="font-family:宋体;"> </span> <span style="font-family:宋体;">显示所有数据库 </span>



<span>desc </span><span style="font-family:宋体;">表名</span><span>;</span><span style="font-family:宋体;"> 显示表结构</span>



<span>create database </span><span style="font-family:宋体;">数据库名</span><span>;</span><span style="font-family:宋体;"> 建库   </span>



<span>create table </span><span style="font-family:宋体;">表名</span><span>;</span><span style="font-family:宋体;"> 建表</span>



<span>drop database </span><span style="font-family:宋体;">数据库名</span><span>;</span><span style="font-family:宋体;"> </span>



<span>drop table </span><span style="font-family:宋体;">表名</span><span>;<span>&nbsp; </span></span><span style="font-family:宋体;">删库删表</span>



<span>delete from </span><span style="font-family:宋体;">表名</span><span>;</span><span style="font-family:宋体;"> 清空表中记录</span>



<span>mysqldump –uroot -p</span><span style="font-family:宋体;">数据库名</span><span>>test.sql<span>&nbsp; </span></span><span style="font-family:宋体;">备份数据库</span>



<span>mysqldump –uroot -p</span><span style="font-family:宋体;">数据库名</span><span><test.sql<span>&nbsp; </span></span><span style="font-family:宋体;">导入数据</span>



<span>select @@innodb_version; </span><span style="font-family:宋体;">查看</span><span>innodb</span><span style="font-family:宋体;">版本信息</span>



<span>show plugins;</span><span style="font-family:宋体;">查看插件信息</span>



<span>status ;</span><span style="font-family:宋体;">查看数据库状态</span>



<span style="font-family:宋体;">不知道命令的情况下可以通过</span><span>help</span><span style="font-family:宋体;">显示所有的基础命令,然后再</span><span>help </span><span style="font-family:宋体;">基础命令显示详细的说明。如</span><span>help show ;</span>



<span></span>



<span></span>

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注