MySQL数据库管理常用命令

傷城~ 2023-08-17 17:07 140阅读 0赞

location为库名，Contact为表名

<table style="width:649px;">  MySQL数据库管理常用命令&nbsp;  
 <tbody> 
  <tr> 
   <td>用途</td> 
   <td>操作</td> 
   <td>命令</td> 
  </tr> 
  <tr> 
   <td>安装</td> 
   <td>利用RPM包安装MySQL</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>设置TCP 3306端口的iptables</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>root密码管理</td> 
   <td>设置root用户的密码</td> 
   <td>mysqladmin -uroot password ‘password’</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>修改root用户的密码</td> 
   <td>mysqladmin -uroot -p password<br>‘password’</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>数据库，表管理</td> 
   <td>进入mysql</td> 
   <td>mysql -h hostname -uroot -p</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>创建数据库</td> 
   <td>mysql&gt; create database location;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>导入数据结构</td> 
   <td>mysql -uroot -p location<br>&lt;./location.sql</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>查看数据库</td> 
   <td>mysql&gt; show databases;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>进入某个库</td> 
   <td>mysql&gt; use location;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>查看表信息</td> 
   <td>mysql&gt; show tables;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>查看表结构</td> 
   <td>mysql&gt; desc Contact;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;改表名</td> 
   <td>mysql&gt; rename table Contact to<br>Contact_new;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;删除库</td> 
   <td>mysql&gt; drop database location;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;删除表</td> 
   <td>mysql&gt; drop table Contact;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;授权部分</td> 
   <td>&nbsp;建立用户并授权</td> 
   <td>mysql&gt; grant all on location.* to<br>gk1020@’10.1.11.71′ identified by ‘gk1020’;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;取消授权</td> 
   <td>mysql&gt; revoke all on location.*<br>from gk1020@’10.1.11.71′;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>刷新权限&nbsp;</td> 
   <td>mysql&gt; flush privileges;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;操作语句</td> 
   <td>查询</td> 
   <td> <p>mysql&gt; select * from Contact;</p> </td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td> <p>mysql&gt; select count(*) from Contact;</p> </td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>修改</td> 
   <td>mysql&gt; update Contact set<br>RegTime=‘2008-01-01 00：00：00’ where id=1;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>mysql&gt; update Contact set<br>RegTime=‘2008-01-01 00：00：00’,CID=1 where id=1;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>插入</td> 
   <td>mysql&gt; insert into Contact<br>values(”,”…);</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>mysql&gt; insert into Contact(ID,CID,Contact)<br>values(”,”…);</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>删除&nbsp;</td> 
   <td>mysql&gt; delete from Contact where<br>id=1;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>导出</td> 
   <td>导出数据库location</td> 
   <td>mysqldump -uroot -p location<br>&gt;./location.sql</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>导出某个表</td> 
   <td>mysqldump -uroot -p –database<br>location –table Contact &gt;./contact.sql</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>导出数据库location的数据结构</td> 
   <td>mysqldump -d -uroot -p location<br>&gt;./location.sql</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>复制表</td> 
   <td>复制表Contact为Contact_bak</td> 
   <td>mysql&gt; create table Contact_bak as<br>select * from Contact;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>复制表Contact的结构</td> 
   <td>mysql&gt; create table Contact_bak as<br>select * from Contact where 1=2;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>查看正在执行的任务</td> 
   <td>&nbsp;</td> 
   <td>mysql&gt; show processlist;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

转载于:https://www.cnblogs.com/yulia/p/11382164.html