MySQL学习之基础篇（2）——常用命令2

1常见命令

前提是先登录客户端

1查询创建删除 -数据库
查询数据库

<code class="prism language-python has-numbering">SHOW DATABASES <span class="token punctuation">[</span>LIKE <span class="token string">'数据库名'</span><span class="token punctuation">]</span>；
</code>

不区分大小写
；代表要执行以上语句

语法说明：

LIKE是可选项，用于匹配特定的数据库名称，LIKE可以部分匹配，也可以完全匹配。这个LIKE 作用就是如果数据库庞大起来你指定搜索名字可以缩小搜索时间。
数据库名称是’’包围的。

SHOW DATABASES ;查看全部数据库

这是mysql自带的数据库
其中mysql 保存用户信息
information_schema 保存原数据信息
performance_schema 收集性能信息
test 测试数据库是空的，可以在里面建表修改等

创建数据库

<code class="prism language-python has-numbering">create database 数据库名<span class="token punctuation">;</span>
</code>

删除数据库

<code class="prism language-python has-numbering">DROP DATABASE  删除了名为huthon的数据库
</code>

2进入数据库

<code class="prism language-python has-numbering">use 数据库名<span class="token punctuation">;</span>
</code>

3查看表格
进入数据库后，要查看表格

a查看当前数据库的表

<code class="prism language-python has-numbering">SHOW TABLES<span class="token punctuation">;</span> 
</code>

b 查看其它数据库的表
如查看mysql中的表

<code class="prism language-python has-numbering">SHOW TABLES FROM <a href="http://www.itxz.com/?tag=mysql" title="查看更多关于mysql的文章" target="_blank">mysql</a><span class="token punctuation">;</span> 

</code>

4查看在哪个数据库

<code class="prism language-python has-numbering"> select database<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code>

5创建表格

<code class="prism language-python has-numbering">CREATE TABLE  表名<span class="token punctuation">(</span>
 列名，类型，COMMENT <span class="token string">'注释'</span><span class="token punctuation">,</span>
 l列名，类型<span class="token punctuation">,</span>COMMENT <span class="token string">'注释'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

</code>

解释

COMMENT英文翻译注释，可以不写注释，中间列用,号。末尾列先)再；号
回车显示Query OK,表示成功
一般第一列为id

不要注释

<code class="prism language-python has-numbering">CREATE TABLE  表名<span class="token punctuation">(</span>
 列名，类型<span class="token punctuation">,</span>
 l列名，类型<span class="token punctuation">)</span><span class="token punctuation">;</span>

</code>

查看表格，发现useinfo表格已创建，我们还未向表格里添加数据。

6查看表的结构

<code class="prism language-python has-numbering">desc 表名<span class="token punctuation">;</span>
</code>

FileID :属性
Type:类型;

7查看表的数据

<code class="prism language-python has-numbering">SELECT <span class="token operator">*</span> FROM  表名<span class="token punctuation">;</span>
</code>

数据为空，我们还未插入数据

8往表中插入数据

<code class="prism language-python has-numbering">insert into <span class="token operator"><</span>表名<span class="token operator">></span> <span class="token punctuation">[</span><span class="token punctuation">(</span><span class="token operator"><</span>字段名<span class="token number">1</span><span class="token operator">></span><span class="token punctuation">[</span><span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token operator"><</span>字段名n <span class="token operator">></span> <span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">]</span> values <span class="token punctuation">(</span> 值<span class="token number">1</span> <span class="token punctuation">)</span><span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token punctuation">(</span> 值n <span class="token punctuation">)</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
</code>

查看表中数据 ** 9修改表中数据**

<code class="prism language-python has-numbering">update 表名 <span class="token builtin">set</span> 列名<span class="token operator">=</span><span class="token string">'新值'</span> where <span class="token builtin">id</span><span class="token operator">=</span>原始值<span class="token punctuation">;</span>
</code>

10删除表中某一行数据

<code class="prism language-python has-numbering">delete <span class="token keyword">from</span> 表名 where <span class="token builtin">id</span> <span class="token operator">=</span><span class="token number">1</span>（<span class="token number">1</span>或者其他数值）；
</code>

11查看当前mysql版本

<code class="prism language-python has-numbering">select version<span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code>