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;

-- 执行计划 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 dormitorNo='002';
explain select * from student2 where city="活死人墓" and dormitoryNo='002';
 
-- 删除索引
alter table student drop index studentname;
-- 创建索引
create index studentname on student(studentname);

-- 事务
create table user(
    u_name varchar(20);
    unique(u_name)
);

insert into user values("小龙女");

select * from user;

begin;
insert into user values("李莫愁");
commit;

begin; -- 事务的开始
insert into user values("尹志平");
commit; -- 事务的结束

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;