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

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

mysql正则表达式

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

  • ^:匹配输入字符串的开始位置。如果设置了RegExp对象的Multiline属性,^也匹配’n’,’r’之后的位置。
  • RegExpMultiline也匹配’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:MySQLbin> 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:MySQLbin> 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

相关内容推荐