MySQL数据库约束及表的设计实例分析

MySQL数据库约束及表的设计实例分析

数据库约束

not null

指定某列的存储不能为null值

create table student (id int not null,name varchar(20));Query OK, 0 rows affected (0.01 sec)mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)登录后复制

unique

保证某列必须有唯一的值，插入重复的值就会报错

default

规定给列赋值时的默认值

create table student(id int,name varchar(20) default '匿名');登录后复制

primary key 主键

主键约束，是not null 与unique的结合，确保某列的赋值不能为null，并且是唯一的

auto_increment 自增特点：

1.如果表中没有记录，自增从1开始

2.如果有数据，从上一条记录往下自增

3.插入再删掉数据，自增的值不会重复利用，会按删掉的那条开始自增

create table student (id int primary key auto_increment,name varchar(20));Query OK, 0 rows affected (0.01 sec)mysql> desc student;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> insert into student values(null,'张三');Query OK, 1 row affected (0.00 sec)mysql> select * from student;+----+--------+| id | name |+----+--------+| 1 | 张三 |+----+--------+1 row in set (0.00 sec)登录后复制

foreign key 外键

外键约束，在表一中的数据必须在表二中存在，要参照完整性准则

外键约束描述的是两张表的两个列之间的“依赖关系”

外键约束会影响表的删除，例如下面的实例的class表被关联，所以它不能被轻易删除

mysql> create table class ( -> id int primary key, -> name varchar(20) not null -> );Query OK, 0 rows affected (0.04 sec)mysql> create table student ( -> id int primary key, -> name varchar(20) not null, -> email varchar(20) default 'unknow', -> QQ varchar(20) unique, -> classId int , foreign key (classId) references class(id) -> );Query OK, 0 rows affected (0.03 sec)mysql> desc class;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | varchar(20) | NO | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.02 sec)mysql> desc student;+---------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | varchar(20) | NO | | NULL | || email | varchar(20) | YES | | unknow | || QQ | varchar(20) | YES | UNI | NULL | || classId | int(11) | YES | MUL | NULL | |+---------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)登录后复制

check

指定一个条件，通过条件来对值进行判定

但是mysql并不支持

create table test_user ( id int, name varchar(20), sex varchar(1), check (sex ='男' or sex='女'));登录后复制

表的设计

一对一

一对一设计表就比如学生表和账户表，一个账户对应到一个学生，一个学生也只有一个账户

表示方法

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 existmysql> 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)登录后复制

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。