创建数据库db6

create database db6 charset=utf8;

 user db6;

# 创建班级表

mysql> create table class(cid int primary key auto_increment,caption varchar(50) not null unique);Query OK, 0 rows affected (0.15 sec)mysql> select * from class;Empty set (0.00 sec)mysql> insert into class(caption) values(‘三年二班‘),(‘一年三班‘),(‘三年一班‘);Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from class;+-----+--------------+| cid | caption |+-----+--------------+| 2 | 一年三班 || 3 | 三年一班 || 1 | 三年二班 |+-----+--------------+3 rows in set (0.00 sec)

# 创建老师表

mysql> create table teacher(tid int primary key auto_increment,tname varchar(50) not null);Query OK, 0 rows affected (0.11 sec)mysql> insert into teacher(tname) values(‘李老师‘),(‘吴老师‘),(‘陈老师‘);Query OK, 3 rows affected (0.12 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from teacher;+-----+-----------+| tid | tname |+-----+-----------+| 1 | 李老师 || 2 | 吴老师 || 3 | 陈老师 |+-----+-----------+3 rows in set (0.00 sec)

多对一关系建表

# 学生表与班级表建立关系

创建学生表

mysql> create table student(sid int primary key auto_increment,sname varchar(20) not null,gender enum(‘男‘,‘女‘) not null,class_id int not null,foreign key(class_id) references class(cid) on delete cascade on update cascade);Query OK, 0 rows affected (0.06 sec)

mysql> insert into student(sname,gender,class_id) values(‘小红‘,‘女‘,1),(‘小李‘,‘女‘,1),(‘小刚‘,‘男‘,2);Query OK, 3 rows affected (0.36 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from student;+-----+--------+--------+----------+| sid | sname | gender | class_id |+-----+--------+--------+----------+| 1 | 小红 | 女 | 1 || 2 | 小李 | 女 | 1 || 3 | 小刚 | 男 | 2 |+-----+--------+--------+----------+3 rows in set (0.00 sec)

# 老师表和课程表建立关系

创建课程表

mysql> create table course(cid int primary key auto_increment,cname varchar(20) not null,tearch_id int not null,
foreign key(tearch_id) references teacher(tid) 
on delete cascade on update cascade);Query OK, 0 rows affected (0.12 sec

插入记录 并查询

mysql> insert into course(cname,tearch_id) values(‘生物‘,1),(‘体育‘,1),(‘物理‘,2);Query OK, 3 rows affected (0.11 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from course;+-----+--------+-----------+| cid | cname | tearch_id |+-----+--------+-----------+| 1 | 生物 | 1 || 2 | 体育 | 1 || 3 | 物理 | 2 |+-----+--------+-----------+3 rows in set (0.00 sec)

建立多对多关系

# 建立一张score表 与 学生表 课程表建立关系

mysql> create table score(sid int not null unique auto_increment,student_id int not null,corse_id int not null,number char(16) not null,foreign key(student_id) references student(sid) on delete cascade on update cascade,foreign key(corse_id) references course(cid) on delete cascade on update cascade,primary key(student_id,corse_id)
);

插入记录

mysql> insert into score(student_id,corse_id,number) values(1,1,‘60‘),(1,2,‘59‘),(2,2,‘100‘);Query OK, 3 rows affected (0.70 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from score;+-----+------------+----------+--------+| sid | student_id | corse_id | number |+-----+------------+----------+--------+| 1 | 1 | 1 | 60 || 2 | 1 | 2 | 59 || 3 | 2 | 2 | 100 |+-----+------------+----------+--------+3 rows in set (0.00 sec)