MySQL操作数据库的命令

青旅半醒 2022-02-01 06:17 257阅读 0赞

### MySQL操作数据库的命令 ###

*   *  操作数据库
     *  存储引擎
     *  数据类型
     *  操作表
     *  表的约束
     *  索引操作
     *   *  普通索引
 *  
 *   *   *  查看创建结果
         *  唯一索引
         *  全文索引
         *  多列索引
         *  删除索引
     *  操作视图
     *   *  视图特点
         *  创建视图
         *  查询视图
         *  查看视图
         *  删除视图
         *  修改视图
     *  触发器
     *   *  创建
         *  查看
         *  删除
     *  操作数据表
     *   *  增加数据
         *  更新数据
         *  删除数据
         *  查询数据
         *   *  简单查询
             *  外连接查询
             *  合并查询
     *  常用函数
     *   *  字符串函数
         *  数值函数
         *  日期和时间函数
         *  系统信息函数
         *  功能函数
     *  存储过程

## 操作数据库 ##

1.显示当前数据库

> show databases;

2.创建数据库

> create database database\_name;

3.进入数据库

> use database\_name;

4.删除数据库

> drop database database\_name;

## 存储引擎 ##

1.查看数据库支持的存储引擎

> show engines;  
> show variables like ‘have%’;

2.查询默认存储引擎

> show variables like ‘storage\_engine%’;

3.修改默认存储引擎

> \#修改配置文件my.ini中的default-storage-engine  
> \#重启MySQL服务

## 数据类型 ##

1.整数类型

<table> 
 <thead> 
  <tr> 
   <th>整数类型</th> 
   <th>字节</th> 
   <th>最小值</th> 
   <th>最大值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>tinyint</td> 
   <td>1</td> 
   <td>有符号：-2<sup>7</sup>，无符号：0</td> 
   <td>有符号：2<sup>7</sup>，无符号：2<sup>8</sup></td> 
  </tr> 
  <tr> 
   <td>smallint</td> 
   <td>2</td> 
   <td>有符号：-2<sup>15</sup>，无符号：0</td> 
   <td>有符号：2<sup>15</sup>，无符号：2<sup>16</sup></td> 
  </tr> 
  <tr> 
   <td>mediumint</td> 
   <td>3</td> 
   <td>有符号：-2<sup>23</sup>，无符号：0</td> 
   <td>有符号：2<sup>23</sup>，无符号：2<sup>24</sup></td> 
  </tr> 
  <tr> 
   <td>int 和 integer</td> 
   <td>4</td> 
   <td>有符号：-2<sup>31</sup>，无符号：0</td> 
   <td>有符号：2<sup>31</sup>，无符号：2<sup>32</sup></td> 
  </tr> 
  <tr> 
   <td>bigint</td> 
   <td>8</td> 
   <td>有符号：-2<sup>63</sup>，无符号：0</td> 
   <td>有符号：2<sup>63</sup>，无符号：2<sup>64</sup></td> 
  </tr> 
 </tbody> 
</table>

2.浮点数类型、定点数类型、位类型

<table> 
 <thead> 
  <tr> 
   <th>浮点数类型</th> 
   <th>字节</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>float</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>double</td> 
   <td>8</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>定点数类型</th> 
   <th>字节</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>dec(m,d)和decimal(m,d)</td> 
   <td>m+2</td> 
  </tr> 
 </tbody> 
</table>

3.时间和日期类型

<table> 
 <thead> 
  <tr> 
   <th>日期和时间类型</th> 
   <th>字节</th> 
   <th>最小值</th> 
   <th>最大值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>date</td> 
   <td>4</td> 
   <td>1000-01-01</td> 
   <td>9999-12-31</td> 
  </tr> 
  <tr> 
   <td>datetime</td> 
   <td>8</td> 
   <td>1000-01-01 00:00:00</td> 
   <td>9999-12-31 23:59:59</td> 
  </tr> 
  <tr> 
   <td>timestamp</td> 
   <td>4</td> 
   <td>19700101080001</td> 
   <td>2038年的某个时刻</td> 
  </tr> 
  <tr> 
   <td>time</td> 
   <td>3</td> 
   <td>-835:59:59</td> 
   <td>835:59:59</td> 
  </tr> 
  <tr> 
   <td>year</td> 
   <td>1</td> 
   <td>1901</td> 
   <td>2155</td> 
  </tr> 
 </tbody> 
</table>

4.字符串类型

<table> 
 <thead> 
  <tr> 
   <th>char系类类型</th> 
   <th>字节</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>char(M)</td> 
   <td>M</td> 
   <td>M为0~255之间的整数,适合存储固定长度的数据</td> 
  </tr> 
  <tr> 
   <td>varchar(M)</td> 
   <td>M</td> 
   <td>M为0~255之间的整数，适合存储长度不固定的数据</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>text系类类型</th> 
   <th>字节</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>tinytext</td> 
   <td>0~2<sup>8</sup></td> 
   <td>值的长度为+2个字节</td> 
  </tr> 
  <tr> 
   <td>text</td> 
   <td>0~2<sup>16</sup></td> 
   <td>值的长度为+2个字节</td> 
  </tr> 
  <tr> 
   <td>mediumtext</td> 
   <td>0~2<sup>24</sup></td> 
   <td>值的长度为+3个字节</td> 
  </tr> 
  <tr> 
   <td>longtext</td> 
   <td>0~2<sup>32</sup></td> 
   <td>值的长度为+4个字节</td> 
  </tr> 
 </tbody> 
</table>

存储二进制数据（例如：图片、音乐、视频文件）

<table> 
 <thead> 
  <tr> 
   <th>binary系类类型</th> 
   <th>字节</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>binary(M)</td> 
   <td>M</td> 
   <td>允许长度0~M,适合存储固定长度的数据</td> 
  </tr> 
  <tr> 
   <td>varbinary(M)</td> 
   <td>M</td> 
   <td>允许长度0~M，适合存储长度不固定的数据</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>blob系类类型</th> 
   <th>字节</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>tinyblob</td> 
   <td>0~2<sup>8</sup></td> 
  </tr> 
  <tr> 
   <td>blob</td> 
   <td>0~2<sup>16</sup></td> 
  </tr> 
  <tr> 
   <td>mediumblob</td> 
   <td>0~2<sup>24</sup></td> 
  </tr> 
  <tr> 
   <td>longblob</td> 
   <td>0~2<sup>32</sup></td> 
  </tr> 
 </tbody> 
</table>

## 操作表 ##

*  查看当前数据库下的所有表

> show tables;

\-创建表

> create table table\_name(  
> 属性名 数据类型,  
> .  
> .  
> );

*  查看表结构

> desc table\_name;

*  删除表

> drop table table\_name;

*  修改表名

> alter table old\_table\_name rename new\_table\_name;

*  增加字段

> alter table table\_name add 属性名 属性类型;

*  删除字段

> alter table table\_name drop 属性名;

*  修改字段

> alter table table\_name modify 属性名 数据类型;

*  修改字段的名字

> alter table table\_name change 旧属性名 新属性名 数据类型;

## 表的约束 ##

<table> 
 <thead> 
  <tr> 
   <th>约束关键字</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>not null</td> 
   <td>约束字段的值不能为空</td> 
  </tr> 
  <tr> 
   <td>default</td> 
   <td>设置字段的默认值</td> 
  </tr> 
  <tr> 
   <td>unique key (UK)</td> 
   <td>字段值是唯一</td> 
  </tr> 
  <tr> 
   <td>primary key (PK)</td> 
   <td>字段为表的主键，作为在该表的唯一标识</td> 
  </tr> 
  <tr> 
   <td>auto_increment</td> 
   <td>字段的值是自动递增</td> 
  </tr> 
  <tr> 
   <td>foreign key</td> 
   <td>字段为表的外键</td> 
  </tr> 
 </tbody> 
</table>

## 索引操作 ##

目的：为了提高从表中检索数据的速度。  
对象：数据库的表上。  
实现：由表中的一个或多个字段生成的键组成，存储在数据结构（B-树(BTREE)或哈希表(HASH)）中。  
功能：提高数据库系统的查找速度。保证字段的唯一性，从而实现数据库表的完整性。  
类型：Mysql支持六种索引：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引。  
注意：InnDB和MyISAM存储引擎支持B-树类型索引，MEMORY存储引擎支持哈希类型索引。

### 普通索引 ###

普通索引就是在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上；

*  创建普通索引
    
     *  创建表时添加索引
    
    > create table table\_name(  
    > 属性名 数据类型,  
    > …  
    > index|key \[索引名\] (属性名1 \[(长度)\] \[ASC | DESC\])  
    > )
    
    #  #
    
    使用index或key参数来指定字段为索引，“索引名”参数用来指定创建索引名，“属性名1”参数用来指定索引所关联的字段名称
    
     *  在已经存在的表上创建普通索引
    
    > create index 索引名 on 表名 (属性名 \[(长度)\] \[asc|desc\])
    
     *  alter table创建索引
    
    > alter table table\_name add index|key 索引名 (属性名 \[(长度)\]) \[asc|desc\]

### 查看创建结果 ###

> show create table 表名;

### 唯一索引 ###

唯一索引的值必须是唯一的。  
\- 创建表时创建唯一索引  
>create table table\_name(  
属性名 数据类型,  
…  
unique index|key \[索引名\] (属性名1 \[(长度)\] asc|desc)  
);

*  在已经创建的表上创建唯一索引

> create unique index 索引名 on 表名 ( 属性名1 \[(长度)\] \[asc| desc\])

*  通过alter创建  
    alter table table\_name add unique index|key 索引名(属性名 \[(长度)\] \[asc|desc\])

### 全文索引 ###

全文索引主要关联在数据类型为char、varchar和 text的字段上，以便能更快速地查询数据量较大的字符串类型的字段。

*  创建
    
    > 方法一：  
    > create table table\_name(  
    > 属性名 数据类型,  
    > …  
    > fulltext index|key \[索引名\] (属性名1 \[(长度)\] \[asc| desc\])  
    > );  
    > 方法二：  
    > create fulltext index 索引名 on 表名 (属性名 \[(长度)\] \[asc|desc\])  
    > 方法三：  
    > alter table table\_name add fulltext index|key \[索引名\] (属性名1 \[(长度)\] \[asc| desc\])；

### 多列索引 ###

多列索引在创建时所关联的字段是多个字段，查询时只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。

*  创建
    
    > 方法一：  
    > create table table\_name(  
    > 属性名 数据类型,  
    > …  
    > index|key \[索引名\] (属性名1 \[(长度)\] \[asc| desc\])  
    > …  
    > (属性名n \[(长度)\] \[asc| desc\])  
    > );  
    > 方法二：  
    > create index 索引名 on 表名 (属性名 \[(长度)\] \[asc|desc\],…,属性名n \[(长度)\] \[asc|desc\])  
    > 方法三：  
    > alter table table\_name add index|key \[索引名\] (属性名1 \[(长度)\] \[asc| desc\]  
    > …  
    > 属性名n \[(长度)\] \[asc| desc\]  
    > )；

### 删除索引 ###

> drop index index\_name on table\_name;

## 操作视图 ##

### 视图特点 ###

*  视图的列来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
 *  视图是由基本表产生的虚表。
 *  视图的建立和删除不影响基本表。
 *  对视图内容的更新（添加、删除和修改）直接影响基本表。
 *  当视图来自多个基本表时，不允许添加和删除数据。

### 创建视图 ###

> create view view\_name as 查询语句;

### 查询视图 ###

> select \* from view\_name;

### 查看视图 ###

> //查询所有视图  
> 进入数据库  
> show tables;  
> 查询视图的详细信息  
> show table status \[from db\_name\] \[like ‘pattern’\];//db\_name 数据名，pattern是视图名

### 删除视图 ###

> drop view view\_name , \[view\_name\] …;

### 修改视图 ###

> create or replace view view\_name as 查询语句;  
> aler view viewname as 查询语句;

## 触发器 ##

触发触发器的表事件有：delete、insert、update

### 创建 ###

> create trigger trigger\_name  
> before | after trigger\_event  
> on table\_name  
> for each row  
> begin  
> trigger\_stmt  
> end  
> //trigger\_name 触发器名称  
> //before | after 触发器执行时间  
> trigger\_event 触发的事件delete、update、insert  
> for each row 表上任何一条记录上的操作都触发触发器  
> trigger\_stmt 激活触发器后执行的语句

### 查看 ###

> show triggers;

### 删除 ###

> drop trigger trigger\_name:

## 操作数据表 ##

### 增加数据 ###

insert into table_name[(field1,field2......)] values(value1,value2,.....) [,(value1,value2,....)];
    //在插入的时候除了“自动增加”约束的字段不需要插入数据外，具有默认值约束的字段也可以不插入数据。

### 更新数据 ###

update table_name set field1=value1,fielid2=value2,.... where condition;
    //condition 为更新的约束条件

### 删除数据 ###

delete from table_name where condition;
    //condition为删除的约束条件

### 查询数据 ###

#### 简单查询 ####

select [distinct] field1,field2,... from table_name [where search_condition] [group by group_by_expression] 
    [hiving search_condition] [order by  order_expression [asc| desc]] [limit offset,row ]

*  distinct 去除指定字段的重复数据
 *  search\_condition:查询条件
    
     *  多个查询条件可以用 and | or
     *  not 取反
    
    > not age<30; //age>=30;
    
     *  between…and… 和not between… and…
    
    > age between 12 and 20;  
    > age not between 12 and 30;
    
     *  is (not) null关键字
     *  in (v1,v2,…,vn)
     *  like  
        用于模糊查询，通配符有%、\_、\[ \]、\[^\]  
        %:后面可以跟零个或多个字符  
        \_:匹配任意单个字符  
        \[ \]:查询一定范围内的单个字符，包括两端数据  
        \[^\]:表示不在一定范围内的单个字符，包括两端数据
 *  group\_by\_expression:分组条件
    
    > group by field1,field2,…; //根据自定字段分组
 *  search\_condition：聚合查询条件，只能与select一起使用

> //例：相同年龄的人数大于2  
> hiving count(age)>2

*  order\_expression :排序条件

> order by field1 \[desc|asc\] ,field2 \[desc|asc\] ,…

*  limit offset,row：限制查询数量  
    offset :偏移位置  
    row:显示数量

#### 外连接查询 ####

select field1,field2,... from table_name1 left | right 
    join table_name2
    on condition;
    //field来源于table1和table2中，condition表示连接查询条件

#### 合并查询 ####

把多个查询结果合并在一起

select field1,field2,... from table_name1
    union | union all
    select field1,field2,... from table_name2
    .....

## 常用函数 ##

### 字符串函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>concat(str1,str2,…,strn)</td> 
   <td>连接字符串str1、str2、…、strn为一个完整字符串</td> 
  </tr> 
  <tr> 
   <td>insert(str,x,y,instr)</td> 
   <td>将字符串str从第x位置开始，长度为y个字符长的字符替换为instr</td> 
  </tr> 
  <tr> 
   <td>lower(str)</td> 
   <td>将字符串str中所有字符变为小写</td> 
  </tr> 
  <tr> 
   <td>uppe(str)</td> 
   <td>将字符串str中所有字符变成大写</td> 
  </tr> 
  <tr> 
   <td>left(str,x)</td> 
   <td>返回str最左边的x个字符</td> 
  </tr> 
  <tr> 
   <td>right(str,x)</td> 
   <td>返回str最右边的x个字符</td> 
  </tr> 
  <tr> 
   <td>lpad(str,n,pad)</td> 
   <td>对str最左边填充n个字符的pad</td> 
  </tr> 
  <tr> 
   <td>rpad(str,n,pad)</td> 
   <td>对str最右边填充n个字符的pad</td> 
  </tr> 
  <tr> 
   <td>ltrim(str)</td> 
   <td>去掉str左边的空格</td> 
  </tr> 
  <tr> 
   <td>rtrim(str)</td> 
   <td>去掉str右边的空格</td> 
  </tr> 
  <tr> 
   <td>replace(str,a,b)</td> 
   <td>使用字符串b替换掉str中所有字符串a</td> 
  </tr> 
  <tr> 
   <td>strcmp(str1,str2)</td> 
   <td>比较str1和str2</td> 
  </tr> 
  <tr> 
   <td>trim(str)</td> 
   <td>去掉str前后的空格</td> 
  </tr> 
  <tr> 
   <td>substring(str,x,y)</td> 
   <td>返回str中从x开始，长为y的字符串</td> 
  </tr> 
  <tr> 
   <td>length(str)</td> 
   <td>字符串字节长度；例: length(“mysql”) = 5 length(“你好”) = 4</td> 
  </tr> 
  <tr> 
   <td>char_length(str)</td> 
   <td>str中字符的个数 ；例：char_length(“mysql”)=5,char_length(“你好”) = 2</td> 
  </tr> 
  <tr> 
   <td>find_in_set(str1,str2)</td> 
   <td>在str2中查找str1,返回位置</td> 
  </tr> 
 </tbody> 
</table>

### 数值函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>abs(x)</td> 
   <td>返回数值x绝对值</td> 
  </tr> 
  <tr> 
   <td>ceil(x)</td> 
   <td>返回&gt;=x的最小整数值</td> 
  </tr> 
  <tr> 
   <td>floor(x)</td> 
   <td>返回&lt;=x的最大整数值</td> 
  </tr> 
  <tr> 
   <td>mod(x,y)</td> 
   <td>返回 x/y 的余数</td> 
  </tr> 
  <tr> 
   <td>rand()</td> 
   <td>返回0~1内的随机数</td> 
  </tr> 
  <tr> 
   <td>round(x,y)</td> 
   <td>返回x四舍五入之后，有y位小数点的值</td> 
  </tr> 
  <tr> 
   <td>truncate()</td> 
   <td>返回x截断为y位小数的值</td> 
  </tr> 
 </tbody> 
</table>

### 日期和时间函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>curdate()</td> 
   <td>获取当前日期</td> 
  </tr> 
  <tr> 
   <td>curtime()</td> 
   <td>获取当前时间</td> 
  </tr> 
  <tr> 
   <td>now(),current_timestamp(),localtime(),systemdate()</td> 
   <td>获取当前日期和时间</td> 
  </tr> 
  <tr> 
   <td>unix_timestamp(date)</td> 
   <td>获取日期date的unix时间戳</td> 
  </tr> 
  <tr> 
   <td>from_unixtime()</td> 
   <td>获取unix时间戳的日期值</td> 
  </tr> 
  <tr> 
   <td>week(date)</td> 
   <td>返回日期date为一年中的第几周</td> 
  </tr> 
  <tr> 
   <td>year(date)</td> 
   <td>返回date的年份</td> 
  </tr> 
  <tr> 
   <td>hour(time)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>minute(time)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>monthname(date)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>adddate(date,n)</td> 
   <td>日期date加上n天后的日期</td> 
  </tr> 
  <tr> 
   <td>subdate(date,n)</td> 
   <td>日期date减去n天后的日期</td> 
  </tr> 
 </tbody> 
</table>

### 系统信息函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>database()</td> 
   <td>当前数据库名</td> 
  </tr> 
  <tr> 
   <td>user()</td> 
   <td>当前用户</td> 
  </tr> 
 </tbody> 
</table>

### 功能函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>password(str)</td> 
   <td>对str加密</td> 
  </tr> 
  <tr> 
   <td>format(x,n)</td> 
   <td>将数字x格式化，保留n位小数</td> 
  </tr> 
  <tr> 
   <td>inet_aton(ip)</td> 
   <td>将ip地址转换成数字</td> 
  </tr> 
  <tr> 
   <td>inet_ntoa(x)</td> 
   <td>将数字转换成IP</td> 
  </tr> 
  <tr> 
   <td>get_loct(name,time)</td> 
   <td>创建一个持续时间为time名为name的锁</td> 
  </tr> 
  <tr> 
   <td>release_loct(name)</td> 
   <td>为名为name的锁进行解锁</td> 
  </tr> 
  <tr> 
   <td>convert(x,type)</td> 
   <td>将x变成type类型</td> 
  </tr> 
 </tbody> 
</table>

## 存储过程 ##