MYSQL笔记

　　

 

2017-7-25 14:59:46

create table tb4(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(32) not null,
    extra text
) ENGINE=innodb default charset=utf8

insert into tb4 (name,email,extra) values('gogo','gogo@qq.com','gogegogegoge')

create index name on tb4

select * from  xxx where name ='xxx'
select * from  xxx  name ='xxx'

explain select * from tb4 where name='gege'
explain select * from  xxx where name ='xxx'     type= ref
explain select * from  xxx where email ='xxx'     type= all
==============================================================
唯一索引
create table tb4(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email var char(32) not null,
    extra text
    unique(name)
) engin=innodb default charset=utf8


explain select * from  xxx where name ='xxx'     type= const
explain select * from  xxx where email ='xxx'     type= all
==============================================================
主键索引

==============================================================
组合索引:最左前缀，如果有多条的时候，要从左到右的使用才会调用索引
    primary key(nid,name)

    unique(name,email)

    index(name,email)
    selelct * from 
    
==============================================================    
    测试
==============================================================    
 create table class(
 cid int not null auto_increment primary key,
 caption varchar(32) not null)
 engine=innodb default charset=utf8;
 
 insert into class(caption) values('三年二班','一年三班','三年一班');
 ==============================================================
create table teacher(
  tid int not null auto_increment primary key,
  tname varchar(32) not null
 )ENGINE=innodb default charset=utf8;
 
delete  from class where cid in(4,5,6);
delete  from class where cid >3;

insert into teacher(tname) values('波多野'),('苍空井'),('饭岛');
insert into class(caption) values('波多野'),('苍空井'),('饭岛');
==============================================================
select * from  ;
create table score(
  sid int not null auto_increment primary key,
  student_id int not null,
  corse_id  int not null,
  number int not null
 )ENGINE=innodb default charset=utf8;
 
insert into score(student_id,corse_id,number) values(1,1,60),(1,2,59),(2,2,100);

==============================================================
create table course(
  cid int not null auto_increment primary key,
  cname varchar(32) not null,
  teacher_id int not null
 )ENGINE=innodb default charset=utf8;
 
 insert into course(cname,teacher_id) values('生物',1),('体育',1),('物理',2);

 alter table course  column number;
 ==============================================================
 
 create table student(
  sid int not null auto_increment primary key,
  sname varchar(32) not null,
  gender varchar(32) not null,
  class_id int not null
 )ENGINE=innodb default charset=utf8;
 
insert into student(sname,gender,class_id) values('钢蛋','女',1),('铁锤','女',1),('山炮','男',2);

drop table student;

==============================================================
添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
        alter table 表名 modify column 列名 类型;  -- 类型
        alter table 表名 change 原列名 新列名 类型; -- 列名，类型
  
添加主键：
        alter table 表名 add primary key(列名);
删除主键：
        alter table 表名 drop primary key;
        alter table 表名  modify  列名 int, drop primary key;
  
添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段)references 主表(主键字段);

删除外键：alter table 表名 drop foreign key 外键名称
  
修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
==============================================================
改变外键
alter table course add constraint 'fk_course_teacher' foreign key (teacher_id) references teacher(tid);

ALTER TABLE `course` ADD CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`);
ALTER TABLE `course` DROP FOREIGN KEY `course_ibfk_1`;
ALTER TABLE `course` DROP FOREIGN KEY `fk_course_teacher`;


==============================================================


2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

思路：
    获取所有有生物课程的人（学号，成绩） - 临时表
    获取所有有物理课程的人（学号，成绩） - 临时表
    根据【学号】连接两个临时表：
        学号  物理成绩   生物成绩
 
    然后再进行筛选
 
        select A.student_id,sw,ty from
 
        (select student_id,num as sw from score left join course on score.course_id = course.cid where course.cname = '生物') as A
 
        left join
 
        (select student_id,num  as ty from score left join course on score.course_id = course.cid where course.cname = '体育') as B
 
        on A.student_id = B.student_id where sw > if(isnull(ty),0,ty);
        
select * from course 
生物--1
物理--2


select student_id,number from score where score.course_id=1
union 
select student_id,number from score where corse_id=2

create temporary table t1 (select student_id,number from score where corse_id=1)

select a.student_id,bio,phy from 
(select student_id,number as bio from score left join course on score.course_id = course.cid where course.cname = '生物') as a
left join
(select student_id,number as phy from score left join course on score.course_id = course.cid where course.cname = '物理') as b

on a.student_id=b.student_id where bio >  if (isnull(phy),0,phy);

select student_id,number as bio from 
+------------+-----+
| student_id | bio |
+------------+-----+
|          1 |  10 |
|          2 |   8 |
|          3 |  77 |
|          4 |  79 |
|          5 |  79 |
|          6 |   9 |
|          7 |   9 |
|          8 |   9 |
|          9 |  91 |
|         10 |  90 |
|         11 |  90 |
|         12 |  90 |
+------------+-----+
alter table score change corse_id course_id int; 改列名