mysql parititon 索引_MySQL 存储引擎的基本常识和一些命令

1、MySQL键值(限制如何给字段赋值)

1)

索引:类似与“书的目录”树型目录

缺点:减慢写的速度(insert update delete)

占用物理存储空间

优点:加快查询速度

2)

使用索引

使用规则

一个表中可以由多个index字段

字段的值允许由重复，切可以赋NULL

经常把做查询条件的字段设置为index字段

index字段的key标志是MUL

查看索引

desc 表名；

show index for 表名；

#查看索引详细信息

创建索引

create table 表名(

字段 类型，

index(索引名)；

#建表时创建索引

#默认和字段名同名

-> create table t21(

-> name char(10),

-> age int,

-> sex enum('boy','girl'),

-> index(name),

-> index(sex));

#####################################################################

create index 索引名 on 表名(字段);

#已有表创建索引

#索引名可以和字段名相同

#默认使用的索引类型:BRREE(二叉树)

create index name on t3(name)

drop index 索引名 on 表名；

#删除索引

###################################################################

主键 parimary key

使用规则

一个表中只能有一个 parimary key 字段

对应的字段值不允许有重复，且不允许赋值

如果有多个字段都作为 parimary key ，称为复合主键，必须一个创建。

主健字段的KEY标志死PRI

通常与 auto_increment 连用

经常把表中能够唯一标识记录的字段位主键

1)

-> create table t22( -> create table t22(

-> name char(10), -> name char(10)primary key ,

-> age int, -> age int,

-> likes set('a','b','c'), -> likes set('a','b','c')0;

-> primary key(name));

#创建表时添加主键

2)

alter table 表名 add primary key(字段)；

#已有表添加主键

3)

alter table 表名 drop primary key;

#删除主键

##########################################################################

复合主键

多个字段一起做主键，字段值无法同时重复。

1)

-> create table 表名(

-> cip char(15),

-> port smallint,

-> status enum('allow','deny') default 'deny',

-> primary key(cip,port));

#创建复合主键，必须一起创建

2)

alter table 表名 drop primary key;

#删除复合主键，必须同时删除

3)

alter table t23 add primary key(cip,port);

#已有表创建复合主键

#############################################################################

主键与 auto_increment 连用，让字段的最大值自动增长 +1 ,并且位数值类型

1)

-> create table t24(

-> id int(2) zerofill primary key auto_increment,

-> name char(10),

-> class char(4),

-> index(name));

#创建表

insert into t24(name,class) values('tom','1709');

#添加信息 name class ，此时表中 id 字段自动 +1 。

2)

alter table t24 modify id int(2) unsigned zerofill not null;

alter table t24 drop primary key;

#删除主键要先把 aotu_increment

############################################################################

unique唯一索引

一个表中可以有多个unique字段

对应的字段值不允许有重复

unique字段的key标志是uni

unique字段的值允许为null，当将其修改为不允许为null，则此字段限制与主键相同

############################################################################

外键：foreign key

作用：

限制给字段赋值的。

值必须在指定表中指定字段值的范围内选择。

条件：

表的存储引擎必须是innodb

字段类型要一致

被参照字段必须要是索引类型的一种(primary key)

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

1)

foreign key(字段) references 被参考表(字段)

on update cascade on delete cascade

#使用方法，同步更新同步删除

练习：

参考表

mysql> create table jfb(

-> id int(2) primary key auto_increment,

-> name char(10),

-> pay float(7,2)

-> )engine=innodb;

insert into jfb(name,pay) values('bob',20000),('lucy',15000);

+-------+----------+-----------------+

| id | name | pay |

+-------+----------+-----------------+

| 1 | bob | 20000.00 |

| 2 | lucy | 15000.00 |

+-------+----------+-----------------+

外键表

mysql> create table xsb(

-> num int(2),

-> name char(10),

-> class char(9),

-> foreign key(num) references jfb(id)

-> on update cascade

-> on delete cascade

-> )engine=innodb;

#num字段参考jfb(id)

insert into xsb values(1,'bob','nsd1709'),(2,'lucy','nsd1709');

#将xsb写入数据

+----------+----------+----------------+

| num | name | class |

+----------+-----------+---------------+

| 1 | bob | nsd1709 |

| 2 | lucy | nsd1709 |

+----------+----------+----------------+

insert into xsb values(3,'tom','nsd1709');

#因为参考表jfb(id)没有3，所以无法创建

insert into xsb values(2,'jerry','nsd1709');

+----------+------------+----------------+

| num | name | class |

+----------+------------+----------------+

| 1 | bob | nsd1709 |

| 2 | lucy | nsd1709 |

| 2 | jerry | nsd1709 |

+----------+------------+----------------+

参考表jfb(id)只有两条数据，但是还是添加成功因为

参数 2 存在所以添加成功，但是参考表只有两条数据

为了避免这种情况我们要让 xsb(num) 也具有唯一性

在上面添加主键

delete from xsb where name='jerry';

#删除重复数据

alter table xsb add primary key(num);

#为 xsb(num) 设置主键

jfb xfb

+-------+----------+-----------------+ +----------+----------+----------------+

| id | name | pay | | num | name | class |

+-------+----------+-----------------+ +----------+-----------+---------------+

| 1 | bob | 20000.00 | | 1 | bob | nsd1709 |

| 2 | lucy | 15000.00 | | 2 | lucy | nsd1709 |

+-------+----------+-----------------+ +----------+----------+----------------+

update jfb set id=8 where id=2;

#修改 jfb id=2 改为 id=8

jfb xfb

+-------+----------+-----------------+ +----------+----------+----------------+

| id | name | pay | | num | name | class |

+-------+----------+-----------------+ +----------+-----------+---------------+

| 1 | bob | 20000.00 | | 1 | bob | nsd1709 |

| 8 | lucy | 15000.00 | | 8 | lucy | nsd1709 |

+-------+----------+-----------------+ +----------+----------+----------------+

delete from jfb where id=1;

#删除 jfb id 为1的数据

jfb xfb

+-------+----------+-----------------+ +----------+----------+----------------+

| id | name | pay | | num | name | class |

+-------+----------+-----------------+ +----------+-----------+---------------+

| 8 | lucy | 15000.00 | | 8 | lucy | nsd1709 |

+-------+----------+-----------------+ +----------+----------+----------------+

delete from jfb;

error:被参考的表不能随便被删除的

删除外键

show create table 表名

#外键名称自动生成。

alter table 表名 drop foreign key 外键名

#删除外键名字

###########################################################################

存储引擎

MySQL 数据库服务软件自带的程序。

不同的存储引擎有不同的功能和数据存储方式

是处理表的处理器

表名.frm 存放数据库结构

1)

show engines;

#查看数据库服务支持的存储引擎

2)

存储引擎的特点

MyISAM：

表名.MYI 索引信息

表名.MYI 数据

表名.frm 表结构

功能：

支持表级锁:锁一张表

不支持事务 事务回滚

InnoDB

表名.MYI 表结构

表名.idb 数据+索引信息

功能：

支持行级锁：只给当前被访问的行加锁

支持事务 事务回滚

锁类型：读锁、 写锁

select insert delete update

接收写操作的表适合使用 InnoDB 存储引擎

接收读操作的表适合使用 MyISAM 存储引擎

锁粒度：表级锁 行级锁

事务：一次数据访问从开始访问到访问结束的过程

事务回滚：一次数据访问过程中任意一步操作错误，都会恢复所有操作

事务特性：一致性 原子性 隔离性

事务日志文件：记录InnoDB存储引擎的表执行过的操作。

3)

在配置文件中写入 /etc/my.cnf

default_storage_engine=存储引擎名

#修改默认引擎

alter table 表名 engine=存储引擎名

#修改表引擎

create table 表名(。。。。)engine=innodb;

#设置表的存储引擎

因当在表中没有存储数据时设置。

###########################################################################################

MySQL 服务体系结构：(8个功能模块)

连接池

sql接口

分析器：分析命令语法

优化器：以最小消耗执行命令

查询缓存：存放曾经查找过的数据

存储引擎

文件系统：硬盘

管理工具：装包后提供的命令