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

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;//开启自动提交

自增及其问题

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.sqlmysqldump是备份的命令-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