mysql索引类型、primary key 主键使用规则、mysql存储引擎、mysql服务的工作过程

淩亂°似流年 2022-05-21 12:19 154阅读 0赞

修改表结构  
alter table 表名 执行动作；

add 添加新字段  
add 字段 类型（宽度） 约束条件；

add 字段名 类型（宽度） 约束条件，add 字段 类型（宽度

） 约束条件；

add 字段名 类型（宽度） 约束条件 first；

add 字段名 类型（宽度） 约束条件 after 字段名；

alter table t17 add mail varchar(30) not null default

"student@tedu.cn";

alter table t17 add qq varchar(11) after sex ,add tel

char(11) after age,add stu\_id char(7) first;

删除已有字段  
drop 字段名;  
drop 字段名,drop 字段名;

alter table t17 drop stu\_id ,drop sex;

修改字段类型(当和字段已存储的数据冲突时，不允许修改)  
modify 字段名 类型（宽度） 约束条件;  
modify 字段名 类型（宽度） 约束条件,modify 字段名 类

型（宽度） 约束条件;

alter table t17 modify tel varchar(11);  
alter table t17 modify qq char(11) after name;

修改字段名  
change 原字段名 新字段名 类型（宽度） 约束条件；

change 原字段名 新字段名 类型（宽度） 约束条件，

change 原字段名 新字段名 类型（宽度） 约束条件；

alter table t17 change tel iphone varchar(11);

修改表名  
alter table 原表名 rename \[to\] 新表名；  
alter table t17 rename stuinfo;  
\++++++++++++++++++++++++++++  
  
一 mysql索引类型  
1 什么是索引？创建在字段上 相当于“书的目录”

1-5000页  
目录 1-350  
正文 351-5000

笔画  
一画 50 - 52

部首

拼音  
a 201-312  
b  
c

stuinfo ---> /var/lib/mysql/studb/stuinfo.\*  
name class sex age  
jim  
tom  
alic  
bob

2索引的优点缺点  
优点:加快查询记录的速度  
缺点：占用物理存储空间，会减慢写速度（update delete

insert）

3索引类型  
index 普通索引 \*  
unique 唯一索引  
primary key 主键 \*  
foreign key 外键 \*  
fulltext 全文索引

4使用索引  
查看  
desc 表名; key  
show index from 表名;

普通索引 index 使用规则？  
一个表中可以有多个INDEX字段  
字段的值允许有重复，切可以赋NULL值  
经常把做查询条件的字段设置为INDEX字段  
INDEX字段的KEY标志是MUL

创建 普通索引 index

建表是创建  
create table t21(  
name char(10),  
age tinyint(2) unsigned,  
sex enum("m","w") default "m",  
index(name),  
index(sex)  
);

show index from t21\\G;  
Table: t21  
Key\_name: name  
Column\_name: name  
Index\_type: BTREE B+tree hash  
二叉树  
1-10  
1-5 6-10  
1-2.5 2.6-5

在已有表里创建  
create index 索引名 on 表名(字段名)；  
create index aaa on t16(name);

删除索引  
drop index 索引名 on 表名;  
drop index aaa on t16;  
\++++++++++++++++++++++++  
primary key 主键 使用规则？  
一个表中只能有一个primary key字段  
对应的字段值不允许有重复，且不允许赋NULL值  
如果有多个字段都作为PRIMARY KEY，称为复合主键，必须一

起创建。  
通常与 AUTO\_INCREMENT 连用  
经常把表中能够唯一标识记录的字段设置为主键字段\[记录编号

字段\]  
主键字段的KEY标志是PRI

建表是创建主键  
create table t22(  
stu\_id char(9),  
name char(10),  
age tinyint(2) unsigned,  
sex enum("m","w") default "m",  
primary key(stu\_id)  
);  
create table t23(  
stu\_id char(9) primary key,  
name char(10),  
age tinyint(2) unsigned,  
sex enum("m","w") default "m"  
);

在已有表创建主键  
alter table 表名 add primary key(字段名)；  
alter table t16 add primary key(name);

删除主键  
alter table 表名 drop primary key;  
alter table t16 drop primary key;

复合主键 (多个字段同时做主键，字段值不同时相同就可以)

PRI PRI  
clientip serport status  
1.1.1.1 21 deny  
1.1.1.1 80 allow  
2.1.1.1 80 deny

create table t24(  
clientip varchar(15),  
serport smallint(2),  
status enum("allow","deny") default "deny",  
primary key(clientip,serport)  
);

insert into t24 values("1.1.1.1",21,"deny");  
insert into t24 values("1.1.1.1",80,"allow");  
insert into t24 values("2.1.1.1",80,"deny");

alter table t24 drop primary key;

在已有表里创建复合主键  
alter table t24 add primary key(字段名列表);  
\+++++++++++++++++++++++++++  
primary key 与 auto\_increment 一起使用  
自动增长 ++  
数值类型  
主键  
id name age pay  
1 jim 21 18000  
2 tom 29 20000

create table t26(  
id int(2) zerofill primary key auto\_increment,  
name char(10),  
age tinyint(2) unsigned,  
pay float(7,2) default 18800  
);  
desc t26;  
insert into t26(name,age,pay) values

("bob",29,20000);

insert into t26(name,age,pay) values

("bob",29,20000);

insert into t26(name,age,pay) values

("bob",29,20000);

insert into t26(id,name,age,pay) values

(9,"bob",29,20000);

alter table stuinfo add id int(2) primary key

auto\_increment first;  
  
desc stuinfo;

select \* from studb.stuinfo;

unique 索引使用规则？  
一个表中可以有多个UNIQUE字段  
对应的字段值不允许有重复  
UNIQUE字段的KEY标志是UNI  
UNIQUE字段的值允许为NULL，当将其修改为不允许为NULL

，则此字段限制与主键相同

姓名 护照 驾驶证  
NULL NULL  
jim xxxx cccc  
bob xxxx cccc

建表是创建unique  
create table t27(  
name char(10),  
sh\_id char(18),  
dirver char(10),  
unique(sh\_id),  
unique(dirver)  
);

insert into t27 values("bob","123456","abcdef");  
insert into t27 values("bob","123456",null);

在已有表里创建unique  
create unique index 索引名 on t27(字段名)；  
create unique index sh\_id on t27(sh\_id)

删除unique索引  
drop index 索引名 on 表名；  
\+++++++++++++++++++++++++++  
外键foreign key 使用规则？  
1 表的存储引擎必须是innodb  
2 字段的类型必须相同  
3 被参考字段必须是索引的一种（通常是primary key）

外键的功能： 给当前表的指定字段赋值时，字段的值在指定表中

字段值里选择。

cwb  
cwb\_id name pay  
1 tom 1.8w  
3 jerry 2.5w

xstab  
stu\_id name  
1 tom  
3 jerry

create table cwb(  
cwb\_id int(2) primary key auto\_increment,  
name char(10),  
pay float(7,2) default 18800  
)engine=innodb;  
insert into cwb(name)values("bob"),("jim"),("tom");  
select \* from cwb;

create table xstab(  
stu\_id int(2),  
name char(10),  
foreign key(stu\_id) references cwb(cwb\_id)  
on update cascade on delete cascade  
)engine=innodb;

desc xstab;  
show create table xstab;  
select \* from xstab;

insert into xstab values(9,"lucy");  
insert into xstab values(1,"bob");

update cwb set cwb\_id=8 where cwb\_id=1;  
delete from cwb where cwb\_id=3;

删除外键  
show create table xstab;  
alter table xstab drop foreign key 外键名;  
alter table xstab drop foreign key xstab\_ibfk\_1;

在已有表里添加外键  
alter table xstab add foreign key(stu\_id ) references

cwb(cwb\_id) on update cascade on delete cascade;  
\++++++++++++++++++++++++  
二、mysql存储引擎  
1 什么是存储引擎？  
是mysql软件自带功能程序  
是用来处理表的处理器  
不同的存储引擎有不同的功能和数据存储方式

2 使用存储引擎  
a 查看 修改 mysql服务使用的存储引擎  
mysql> show engines;  
InnoDB DEFAULT

vim /etc/my.cnf  
\[mysqld\]  
default-storage-engine=myisam  
:wq  
\#systemctl restart mysqld

b 查看 设置 修改 表的存储引擎  
show create table 表名;  
careate table 表名 （字段列表）;  
careate table 表名 （字段列表）engine=存储引擎名；  
alter table 表名 engine=存储引擎名；

c 常用存储引擎的特点  
innodb特点  
支持事务 、 事务回滚 、外键  
行级锁  
共享表空间  
t222.frm 表结构  
t222.idb 索引信息+数据

myisam特点  
不支持事务 、 事务回滚 、外键  
表级锁  
独享表空间  
t221.frm 表结构  
t221.MYI 索引信息  
t221.MYD 数据

事务？  
访问数据时从开始连接 ->操作记录->断开连接的过程。  
ATM  
1 插卡 输入密码 ->登录  
操作：  
转账 -> 对方卡号->金额 ->确认  
转账中......  
  
退卡

事务回滚? 在事务执行过程中，有任意一步没有执行成功，就恢

复之前的所有操作。

使用事务日志文件记录执行过操作命令  
cd /var/lib/mysql/  
ib\_logfile1 -----  
|----> sql命令  
ib\_logfile0 -----

ibdata1 sql命令产生的数据信息

insert into t1(name) values("bob"),("lucy"),("jim");

锁的作用：避免并发访问时的冲突问题

锁类型：  
读锁 又被称作 共享锁  
写锁 又被称作 排它锁或互斥锁

查看表记录 （相同表 不同表）  
select \* from t1;

写操作（insert delete update） （相同表 不同表）  
相同表 t1  
pc1 update t1 set name="jim" where name="tom";  
pc2 update t1 set name="bob" where age=21;  
5  
锁粒度：  
表级锁 ： 给一张表加锁 （存储引擎 myisam）  
行级锁 : 给行加锁 （存储引擎 innodb）

\+++++++++++++++++++++++++  
d 建表是如何决定表使用那种存储引擎  
执行写操作多的表适合使用innodb存储引擎，因为并发访问量

大。

执行读操作多的表适合使用myisam存储引擎，因为节省资源。

\+++++++++++++++++++++++++++++++++  
mysql服务的工作过程：  
连接池  
sql接口  
分析器  
优化器  
查询缓存 （空间是从机器的物理内存划分出来的，存储的是曾经

查找过的记录）  
存储引擎  
文件系统  
管理工具（提供数据库服务的软件自带的命令）

\+++++++++++++++++++++++++++++++++  
给studb库下的stuinfo表设置合理的索引（index primarkey

foreigin key）