mysql一次重构

drop table if exists student;
 create table student(
    id int(11)  not null PRIMARY key AUTO_INCREMENT,
    name VARCHAR(255),
    sex boolean
 )engine= innodb default charset= utf8;

 desc student;

 select * from student;
/* 增 */
 INSERT into student VALUES(1,"xin",true),(2,"jiang",false);
INSERT into student VALUES(2,"jiang",false);

/* 改 */
 update student set name= "xinjiang" where id= 2;
/* 删 */
 delete from student where id = 2;
/* 增字段 */
 alter table student add adr VARCHAR(255);
/* 删字段 */
 alter table student drop adr;
/* 修改字段数据类型 */
 alter table student modify column name varchar(220);
/* 修改字段名 */
 alter table student change  column name nnname varchar(50);
 /* 添加字段 */
 alter table student add adr VARCHAR(56);

 create table banji(
banjino int(20),
banjiname VARCHAR(200),
unique(banjiname)
 )ENGINE= INNODB default CHARSET= utf8;

insert into banji values(101,"yiban"),(102,"erban");
/* 去重 */
select distinct name from student;

select * from student s join banji b on s.banjino =b.banjino;
/* having 筛选 */
select s.*,b.banjiname from student s join banji b on s.banjino =b.banjino having s.id=2;
/* 排序 */
select * from student order by id desc;
select * from student order by id asc;
select * form student  order by id LIMIT 0,1;
/* 分组 */
select * from student group by sex;
/* 表连接 */
select * from student left join banji;
select * from stuent  RIGHT join banji;
/* 创建视图 */
create view fg as select id,name from student;
/* 删除视图 */
drop view fg;
/* 查看表状态 */
show table status;
/* 查看所有视图 */
show table status where comment = "view";
/* MYSQL提供的搜索引擎 */
show engines;
/* 配置参数 */
show variables;
/* 当前搜索引擎 */
 show variables where Variable_name like "default_storage_engine";

/* 创建视图 */
create index cc on student(name);
/* 显示视图 */
show index from student;
drop index cc on student;
/* 查询结果集相加 */
select name from student union select banjiname from banji;

/* 导出 */
mysqldupm;
/* 事务 */
start transaction
insert into student VALUES(3,"ssss")
commit;
START TRANSACTION;
insert into student (id,name) VALUES(4,"skkks");
rollback;

START TRANSACTION;
insert into student (id,name) VALUES(4,"skkks");
rollback;