外键/mysql语句

Posted on Posted in mysql
Tips: 本文创建于2014年4月28日,已超过 2 年,内容或图片可能已经失效!

目录

外键/增加级联

drop table if exists classinfo;
create table classinfo(
classid int unsigned auto_increment primary key,
classname varchar(32) not null default '' unique key,
classno tinyint unsigned not null default 0
)engine=innodb charset=utf8 comment="外键测试";

drop table if exists studentinfo;
create table studentinfo(
studentid int unsigned auto_increment primary key,
studentname varchar(32) not null default '',
gender enum('male','female') not null,
classid int unsigned default 0,
foreign key (classid) references classinfo (classid) on update cascade on delete set null
)engine=innodb charset=utf8 comment="外键测试";

insert into classinfo values
(null,'php1','100'),
(null,'php2','101'),
(null,'php3','102'),
(null,'php4','103');

insert into studentinfo values
(null,'张三',1,2),
(null,'李四',2,3),
(null,'王五',2,1),
(null,'赵六',1,1),
(null,'钱七',1,3);

如果出现级联空冲突
删除外键,外键的索引会保留
alter table studentinfo drop foreign key studentinfo_ibfk_1;

alter table studentinfo
modify classid int unsigned,
add foreign key (classid) references classinfo(classid)
on update cascade
on delete set null;

select

select studentid,studentname,now() from studentinfo;
select studentid as sid,studentname as sname,now() as time from studentinfo;

dual表

虚拟表,为了形式而出现的
select now();

where语句

select * from classinfo where classname = 'php1';
select * from classinfo where classno >= 1;
select * from studentinfo where studentname like '张%';
select * from studentinfo where studentname like '%张%';
select * from studentinfo where studentname not like '张%';
select * from classinfo where classid in (select classid from studentinfo);
//distinct 去除重复字段
select distinct(classid) from studentinfo where classid>0;
select * from classinfo where classid in (1,8);
select * from classinfo where classid not in (1,8);
select * from student_infomation where student_id between 3 and 8;
select * from student_infomation where student_id not between 3 and 8;
select * from student_infomation where student_ud>3 and student_id<=8;
select * from studentinfo where classid is null;
select * from studentinfo where classid is not null;
select * from studentinfo where gender='male' and classid is not null;
select * from studentinfo where gender='female' or classid=1;

» 转载请注明来源:若我若鱼 » 外键/mysql语句

Leave a Reply

Your email address will not be published. Required fields are marked *

four × 4 =