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?;
