欢迎光临
我们一直在努力

Mysql常用语句

1、查询该库所有表注释:

<span class="kwd">select</span><span class="pln"> table_name</span><span class="pun">,</span><span class="pln">table_comment </span><span class="kwd">from</span><span class="pln"> information_schema</span><span class="pun">.</span><span class="pln">tables </span><span class="kwd">where</span><span class="pln"> table_schema </span><span class="pun">=</span> <span class="str">'databases'</span> <span class="kwd">and</span><span class="pln"> table_comment like </span><span class="str">"%任务%"</span><span class="pun">;</span>

<span class="kwd">select</span><span class="pln"> table_name </span><span class="kwd">from</span><span class="pln"> information_schema</span><span class="pun">.</span><span class="pln">tables </span><span class="kwd">where</span><span class="pln"> table_schema</span><span class="pun">=</span><span class="str">'databases'</span> <span class="kwd">and</span><span class="pln"> table_name like </span><span class="str">'%settings%'</span><span class="pun">;</span>

2、查找数据表

<span class="kwd">select</span><span class="pln"> table_name</span><span class="pun">,</span><span class="pln">table_comment </span><span class="kwd">from</span><span class="pln"> information_schema</span><span class="pun">.</span><span class="pln">tables </span><span class="kwd">where</span><span class="pln"> table_schema </span><span class="pun">=</span> <span class="str">'databases'</span> <span class="kwd">and</span><span class="pln"> table_name like </span><span class="str">"%ims_ewei_shop%"</span><span class="pun">;</span>

3、查看该表结构:

<span class="pln">show create table testing \G</span><span class="pun">;</span>

4、查看线程:

<span class="pln">show processlist</span><span class="pun">;</span>

5、设置数据库编码:

<span class="pln">charset utf8</span><span class="pun">;</span>

6、新增用户与数据库:

<span class="pln">CREATE USER </span><span class="str">'bbhsky'</span><span class="pun">@</span><span class="str">'%'</span><span class="pln"> IDENTIFIED BY </span><span class="str">'password'</span><span class="pun">;</span><span class="pln">
GRANT SELECT</span><span class="pun">,</span><span class="pln">INSERT</span><span class="pun">,</span><span class="pln">UPDATE</span><span class="pun">,</span><span class="pln">DELETE</span><span class="pun">,</span><span class="pln">CREATE</span><span class="pun">,</span><span class="pln">ALTER ON blog</span><span class="pun">.*</span><span class="pln"> TO </span><span class="str">'bbhsky'</span><span class="pun">@</span><span class="str">'%'</span><span class="pun">;</span>

<span class="com">#如果是全权限,那么就写ALL PRIVILEGES</span><span class="pln">
GRANT ALL PRIVILEGES ON blog</span><span class="pun">.*</span><span class="pln"> TO </span><span class="str">'bbhsky'</span><span class="pun">@</span><span class="str">'%'</span><span class="pun">;</span><span class="pln">

CREATE DATABASE blog</span><span class="pun">;</span><span class="pln">
flush privileges</span><span class="pun">;</span>

<span class="com">//创建删除用户</span><span class="pln">
DROP USER </span><span class="str">'bbhsky'</span><span class="pun">@</span><span class="str">'%'</span><span class="pun">;</span>

7、删除数据库

<span class="pln">DROP DATABASE blog</span><span class="pun">;</span>

8、导出数据:

<span class="com">#导出库数据</span><span class="pln">
mysqldump </span><span class="pun">-</span><span class="pln">h192</span><span class="pun">.</span><span class="lit">168.140</span><span class="pun">.</span><span class="lit">95</span> <span class="pun">-</span><span class="pln">ublog </span><span class="pun">-</span><span class="pln">p phpcms </span><span class="pun">></span><span class="pln">phpcms</span><span class="pun">.</span><span class="pln">sql
</span><span class="com">#导出表数据</span><span class="pln">
mysqldump phpcms </span><span class="pun">-</span><span class="pln">h192</span><span class="pun">.</span><span class="lit">168.140</span><span class="pun">.</span><span class="lit">95</span> <span class="pun">-</span><span class="pln">ublog </span><span class="pun">-</span><span class="pln">p </span><span class="pun">--</span><span class="pln">tables phpcms_section </span><span class="pun">></span><span class="pln">phpcms_section</span><span class="pun">.</span><span class="pln">sql</span>
赞(0)
未经允许不得转载:798VPS » Mysql常用语句
分享到: 更多 (0)