MySQL示例讲解数据库约束以及表的设计(2)-木庄网络博客

当前第2页返回上一页

表示方法

1.可以把这两个实体用一张表来表示

2.可以用两张表来表示，其中一张表包含了另一个表的id

一对多

一个学生应该处于一个班级中，一个班级可以包含多个学生

表示方法：

1.在班级表中，新增一列，表示这个班级里的学生id都有啥(mysql没有数组类型，redis可以)

2.班级表不变，学生表中，新增一列classId

多对多

多对多设计表就好比学生表和课程表,一个学生可以选多个课程,一个课程也可以被多个学生选择

表示方法：

使用一个关联表，来表示两个实体之间的关系

多对多建表实例

-- 学生表
mysql> create table test_student (
    -> id int primary key,
    -> name varchar(10) default 'unknow'
    -> );
Query OK, 0 rows affected (0.03 sec)
-- 选课表
mysql> create table test_course (
    -> id int primary key,
    -> name varchar(20) default 'unknow'
    -> );
Query OK, 0 rows affected (0.02 sec)
-- 成绩表
mysql> create table test_score (
    -> studentId int,
    -> courseId int,
    -> score int,
    -> foreign key (studentId) references test_student(id),
    -> foreign key (courseId) references test_course(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc test_student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | unknow  |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc test_coures;
ERROR 1146 (42S02): Table 'java_5_27.test_coures' doesn't exist
mysql> desc test_course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | unknow  |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc test_score;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| studentId | int(11) | YES  | MUL | NULL    |       |
| courseId  | int(11) | YES  | MUL | NULL    |       |
| score     | int(11) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

插入数据到实例实现多对多

mysql> insert into test_student values (1, 'listen');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_course values (1, '数学');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_student values (2, 'Faker');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_course values (2, '数学');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_score values(1, 1, 90);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_score values (1, 2, 99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_score values (2, 1, 50);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_score values (2, 2, 60);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_student;
+----+--------+
| id | name   |
+----+--------+
|  1 | listen |
|  2 | Faker  |
+----+--------+
2 rows in set (0.00 sec)

mysql> select * from test_course;
+----+--------+
| id | name   |
+----+--------+
|  1 | 数学   |
|  2 | 语文   |
+----+--------+
2 rows in set (0.00 sec)

mysql> select * from test_score;
+-----------+----------+-------+
| studentId | courseId | score |
+-----------+----------+-------+
|         1 |        1 |    90 |
|         1 |        2 |    99 |
|         2 |        1 |    50 |
|         2 |        2 |    60 |
+-----------+----------+-------+
4 rows in set (0.00 sec)

到此这篇关于MySQL示例讲解数据库约束以及表的设计的文章就介绍到这了,更多相关MySQL数据库约束内容请搜索

更多相关Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容