指定某列的存储不能为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)
保证某列必须有唯一的值,插入重复的值就会报错
规定给列赋值时的默认值
create table student(id int,name varchar(20) default '匿名');
主键约束,是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)
外键约束,在表一中的数据必须在表二中存在,要参照完整性准则
外键约束描述的是两张表的两个列之间的“依赖关系”
外键约束会影响表的删除,例如下面的实例的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)
指定一个条件,通过条件来对值进行判定
但是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 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数据库约束及表的设计实例分析的详细内容。更多信息请关注PHP中文网其他相关文章!