SQL优化

时间:2021-6-12 作者:qvyue
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 dormitoryNo ='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 ;
声明:本文内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:qvyue@qq.com 进行举报,并提供相关证据,工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。