create?database?two20210611?default?charset=utf8;

use?two20210611;

create?table?student?(

????studentid?char(10)?PRIMARY?key,

????studentname?varchar(10)?not?null,

????gender?varchar(2)?,

????age?int?,

????index(studentname)

);

show?create?table?student;

insert?into?student?values?('1'?,?'丘處機'?,?'男'?,?46?);

select?*?from?student;

--?執(zhí)行計劃?explain

explain?select?*?from?student?where?studentname="丘處機";

--?組合索引

create?table?student2?(

????studentid?char(10)?PRIMARY?key,

????studentname?varchar(10)?not?null,

????age?int?,

????city?varchar(10),

????dormitoryNo?varchar(5),?????#?宿舍編號

????index?MultiIndex(city,dormitoryNo)??#?組合索引

);

show?create?table?student2;

insert?into?student2?values?('1'?,?'楊過'?,?19?,?'活死人墓'?,?'002')?;

select?*?from?student2?where?studentname="楊過";

explain?select?*?from?student2?where?studentname="楊過";

select?*?from?student2?where?city="活死人墓"?and?dormitoryNo?='002';

explain?select?*?from?student2?where?city="活死人墓"?and?dormitoryNo?='002';

--?刪除索引

alter?table?student?drop?index?studentname;

--?創(chuàng)建索引

create?index?studentname?on?student(studentname)?;

--?事務(wù)

create?table?user(

????u_name?varchar(20)?,

????unique(u_name)

);

insert?into?user?values?("小龍女");

select?*?from?user;

begin;

insert?into?user?values?("李莫愁");

commit;

begin;??--?事務(wù)的開始

insert?into?user?values?("尹志平");

commit;??--?事務(wù)的結(jié)束

begin?;

insert?into?user?values?("郭靖");

rollback;?--?回滾

--?回滾點

begin;

insert?into?user?values?("A");

savepoint?first_point;

insert?into?user?values?("B");

rollback?to?savepoint?first_point;

insert?into?user?values("C");

commit;

select?*?from?user?;