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

修改表结构
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）

转载于:https://www.cnblogs.com/fuzhongfaya/p/8952778.html