MySQL学习笔记（十）

【案例】：展示自动维护主键值的机制
  drop table if exists t_vip ;
  create table t_vip (
     id int primary key auto_increment ,
     name varchar(255)
  );
  insert into t_vip (id , name) values (2 ，'zhangsan');
  insert into t_vip (name) values ('zhangsan');
  insert into t_vip (name) values ('zhangsan');
  insert into t_vip (name) values ('zhangsan');
  insert into t_vip (name) values ('zhangsan');
  insert into t_vip (name) values ('zhangsan');
  insert into t_vip (name) values ('zhangsan');
  insert into t_vip (name) values ('zhangsan');
  insert into t_vip (name) values ('zhangsan');
  select * from t_vip ;

【案例】：请设计数据库表，来描述“班级和学生”的信息
  // 第一种方案：同表存储
     缺点：数据冗余，空间浪费
  // 第二种方案：异表存储
  班级表：t_class        学生表：t_student
  drop table if exists t_student ;
  drop table if exists t_class ;
  create table t_class (
     classno int primary key ,
     classname varchar(255) 
  );
  create table t_student (
      no int primary key auto_increment ,
      name varchar(255),
      cno int ,
      foreign key (cno) references t_class(classno)
  );
  insert into t_class (classno,classname) values (100 , '山东省潍坊市昌邑一中3年1班');
  insert into t_class (classno,classname) values (101 , '山东省潍坊市昌邑一中3年2班');
  insert into t_student(name , cno) values ('jack' , 100);
  insert into t_student(name , cno) values ('lucy' , 100);
  insert into t_student(name , cno) values ('lilei' , 100);
  insert into t_student(name , cno) values ('hanmeimei' , 100);
  insert into t_student(name , cno) values ('zhangsan' , 101);
  insert into t_student(name , cno) values ('lisi' , 101);
  insert into t_student(name , cno) values ('wangwu' , 101);
  insert into t_student(name , cno) values ('zhaoliu' , 101);
  select * from t_class ;
  select * from t_student ;
  【案例】：通过sql语句添加一条新的信息 (songqi ,102);
  insert into t_student (name,cno) values ('songqi' , 102) ;
  //ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)) 
  【分析】: 因为在classno之中没有102这个选项，所以在外键约束下的t_student 表中的cno字段无法添加。
  // 正确的做法：
  insert into t_class (classno,classname) values (102 , '山东省潍坊市昌邑一中3年3班');
  insert into t_student (name, cno) values ('songqi' , 102) ;
  select * from t_class ;
  select * from t_student ;