MYSQL数据库语句之其他(四)

偏执的太偏执、 2022-07-18 05:25 197阅读 0赞

## MYSQL数据库语句之其他(四) ##

### mysql正则表达式 ###

mysql支持正则表达式的，操作符regexp/REGEXP来进行匹配。

*  ^：匹配输入字符串的开始位置。如果设置了RegExp对象的Multiline属性，^也匹配’\\n’,’\\r’之后的位置。
 *  ：匹配输入字符串的结束位置。如果设置了RegExp对象的Multiline属性，也匹配’\\n’,’\\r’之前的位置。
 *  .：匹配除’\\n’之外的任何单个字符。要匹配包括’\\n’在内的任何字符，请用’\[.\\n\]’。
 *  \[…\]：字符集合。匹配所包含的任意一个字符。例如：’\[abc\]’可以匹配“plain”中的’a’。
 *  \[^ …\]：负值字符集合。匹配未包含的任意字符。例如：’\[^abc\]’可以匹配”plain”中的’p’。
 *  p1|p2|p3：匹配p1或p2或p3。例如:’z|food’能匹配”z”或”food”；’(z|f)ood’则匹配”zood”和”food”。
 *  *：匹配前面的子表达式零次或多次。例如：’zo\*’能匹配”z”以及”zoo”。*等价于\{0,\}.
 *  \+：匹配前面的子表达式一次或多次。例如，’zo+’能匹配”zo”以及”zoo”,但不能匹配”z”。等价于\{1,\}。
 *  \{n\}：n是一个非负整数。匹配确定的n次。例如：’o\{2\}’不能匹配”Bob”中的’o’,但是能匹配”food”中的2个0。
 *  \{n,m\}：m和n均为非负整数，其中n<=m。最少匹配n次且最多匹配m次。

\[注：\]一般用的不多。

### MySql事务 ###

mysql事务主要用于处理操作量大，复杂度高的数据。  
\- 在mysql中，只有使用了Innodb数据库引擎的数据库或表才支持事务  
\- 事务处理可以用来维护数据库的完整性，保证成批的sql语句要么全部执行，要么全部不执行。  
\- 事务用来管理insert,update，delete。

**开始一个事务**

start transaction;
    或者
    begin;

**做保存点**

savepoint 保存点名称;

**提交事务**

commit;

**回滚**

rollback;
    rollback to savepoint 保存点名称;

**设置自动提交**

set autocommit = 0;//禁止自动提交
    set autocommit = 1;//开启自动提交

![image][]

### 自增及其问题 ###

auto_increment:自增
    
    mysql一张数据表中只能有一个自增的主键，会随着插入的数据增加而增加。所以在插入时无需指定id。
    
    自增的问题：在主键使用自增时，当我们进行删除操作时，发现id不是连续的，这是一个很头痛的问题。
    
    解决办法：
    重置序列：将自增的列删除，然后重新添加实现，这样列又是按序列排列的。
    alter table 表名 drop id;
    
    alter table 表名 add id int unsigned not null auto_increment first, add primary key(id);
    
    设置自增的序列开始值:
    alter table 表名 auto_increment = 100;//从100开始
    
    或者在创建表的时候设置
    create table 表名( id int auto_increment = 100, ...., primary key(id) );

### 复合主键 ###

复合主键就是通过2个或多个字段的共同作为主键，这样可以防止数据的重复。

eg:
    create table 表名( first_name char(20) not null, last_name char(20) not null, ...., primary key(first_name,last_name) );
    
    在primary key(...)，多个字段作为主键，主键的字段不能为null。

### 索引（index） ###

索引分为单列索引和组合索引  
\- 单列索引：即一个索引只包含单个列，一个表可以有多个单列索引。  
\- 组合索引：即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在sql查询语句的条件中(一般作为where子句的条件)

**实际上。索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。**

优点：mysql索引的建立对于mysql的高效运行是很重要的，可以大大提高检索速度。

缺点：虽然索引大大提高查询的速度，但同时降低了更新表的速度。因为更新表时，mysql不仅要保存数据，还要保存索引文件；建立索引也需要占用磁盘的空间。

**索引查询使用情况**

select \* from 表名 where name =’需要查询的数据’;刚开始，数据不多，执行效果还不错。随着数据量的增加，这个查询，执行起来，越来越慢了。然后在name 上面建立了索引。

create index 索引名 on 表名(name);

这样，可以加快前面那个查询的速度。

但是，某天，你执行了下面这个SQL，发现速度又慢了select \* from test\_tab where age = 25 为啥呢？

因为age 字段上面，没有索引，索引只在 name 上面有，换句话说，也就是 where里面的条件，会自动判断，有没有可用的索引，如果有， 该不该用。多列索引，就是一个索引，包含了2个字段。

例如：create index 索引名 on 表名(name, age);

*  创建索引

方式一：在创建表的时候给对应的列加上索引
    create table tb1( id int primary key auto_increment, name varchar(30), index id_index (id) );
    
    方式二：直接创建索引
    create index 索引名称 on 表名(列名(length));
    
    eg:
    create index name_index on tb1(name(30));//在name列添加一个索引
    
    方式三：修改表结构的方式添加索引
    alter table 表名 add index 索引名称 on (列名(length));
    eg:
    alter table tb1 add index name_index on (name(30));//

*  唯一索引  
    唯一索引是指索引的列的值必须是唯一的，允许有空值创建方式和普通创建一样，不过是在index前加上unique修饰，如：

1.create table 表名( id int primary key auto_increment, name varchar(30), unique 索引名 (列名(length)) );
    
    2.create unique index 索引名 on 表名 (列名(length));
    
    3.alter table 表名 add unique 索引名 on (列名(length));

*  删除索引

drop index 索引名称 on 表名;

*  全文索引
 *  fulltext索引仅用于MyISAM表，生成全文索引是一个非常耗时非常耗硬件的做法，一般不常用。

和唯一所以一样，将unique改为fulltext就好。

*  组合索引  
    如果平时用的sql查询语句都有比较多的限制，可以考虑使用组合索引，提交效率。

创建组合索引
    alter table 表名 add index 索引名 (列名1(length),(列名2(length)));
    
    其他创建方式类时普通创建方式。

*  主键索引：在主键上加上索引，这个索引是不为null的，而且是唯一的。

### 数据库元数据获取 ###

select version();//获取数据库版本
    select database();//获取当前数据库名称
    select user();//获取当前用户名
    show status;//获取数据库状态
    show variables;//获取数据库配置变量

### 数据库的备份与恢复 ###

数据备份：
    
    c:\MySQL\bin> mysqldump -h localhost -p3306 -u root -p my>c:/work/my.sql
    
    mysqldump是备份的命令
    
    -h 是连接的主机
    
    -p 端口号
    
    -u 用户名
    
    -p 密码
    
    my：指定是备份哪个数据库
    
    备份用>备份的地址
    
    注意：末尾不用加';'。这执行的不是sql语句。
    
    
    数据恢复：
    
    前提：必须先创建数据库名
    
    方式一：
    
    mysql>user my;//my是数据库名
    
    mysql>source c:/work/my.sql;
    
    方式二：
    
    c:\MySQL\bin> mysql -u root -p my<c:/work/my.sql;

### 数据库的导出与导入数据 ###

导出表数据
    
    select * from tb_name into outfile 'c:/work/tb_name.txt';
    
    导入表数据
    
    load data local infile 'c:/work/tb_name.txt' into table tb_name;
    
    (注意：只有表存在才能导入，不能回提示数据库中的表不存在)
    导入数据时注意表中的列与文件中的列是否一致，不一致会导致数据导入时丢失。
    如果出现这种情况，有2中方法解决。
    一是，调整表结构，与文件中数据的列保持一致；
    二是，在导入文件时，变换列(这个列表示是数据库的列，文件中数据的列不动的)。
    例如：
    load data local infile 'c:/work/tb_name.txt' into table tb_name(b,a,c);
    
    案例：
    先创建一个tb_name表（字段排列：id,name,age），导出表数据，通过命令删除表tb_name，在创建表tb_name表（字段排列：id，age,name），这是导入表数据，load data local infile 'c:/work/tb_name.txt' into table tb_name(id,name,age);
    因为文件保存的是之前表的结构，所以当前表需要变换一下列结构适应文件中的表数据结构。

### MyISAM与InnoDB简介 ###

*  MyISAM：是MySql默认的数据库引擎。
    
     *  强调性能，执行速度快，可压缩，存储空间小；
     *  不支持外键
     *  不支持事务；
     *  只支持表级锁；
     *  数据备份和恢复较快；
     *  可以和其他字段一起建立联合索引；
     *  引擎的自动增长列必须是索引；如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
     *  支持fulltext类型的全文索引；
     *  允许没有任何索引和主键的表存在，索引都是保存行的地址。
 *  InnoDB：是MySql的数据库引擎。
    
     *  支持事务处理；
     *  支持外键；
     *  支持事务和行级锁；
     *  执行速度慢，需要能更多的内存和存储，会在主存区建立专用的缓冲池用于高速缓冲数据和索引。
     *  InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
     *  不支持fulltext类型的全文索引；
     *  如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

参考：

[菜鸟教程之MySql][MySql]

[image]: http://i2.buimg.com/567571/1f58917abbe5e733.jpg
[MySql]: http://www.runoob.com/mysql/mysql-tutorial.html