一、一对一
create table employee ( eno int primary key auto_increment comment'员工编号', ename char(10) comment'用户名', password int comment'密码');
create table emp ( eno int primary key auto_increment comment'员工编号', ename char(10) comment'姓名', sex char(2) comment'性别', tele int comment'电话', email varchar(10) comment'邮箱', foreign key (eno) references employee (eno));
二、一对多
create table class( cno int primary key comment '班级编号', cname char(10) comment '班级名称' ); create table student( sno int primary key comment '学生编号', sname char(10) comment '学生名称', sex char(2) comment'性别', tele int comment'电话', cno int comment '班级编号', foreign key (cno) references class(cno) );
1、查询计算机和电子商务学的人数
select cname,count(sname) from student,class where student.cno = class.cno group by cname;+----------+--------------+ |
cname | count(sname) |
计算机 2
电子商务 2
三、多对多
create table uesr( uno int primary key comment '用户编号', uname char(10) comment '用户名', password int comment '密码' );
create table role( rno int primary key comment '角色编号', rname char(10) comment '角色名称' );
create table tee( id int primary key, uno int, rno int, foreign key (uno) references uesr(uno), foreign key (rno) references role(rno) );