MySQL示例講解數(shù)據(jù)庫約束以及表的設(shè)計(jì)

更新時(shí)間：2022年06月16日 09:58:08 作者：亞太地區(qū)百大最帥面孔第101名

約束主要完成對數(shù)據(jù)的檢驗(yàn)，保證數(shù)據(jù)庫數(shù)據(jù)的完整性；如果有相互依賴數(shù)據(jù)，保證該數(shù)據(jù)不被刪除，本篇文章教你如何給表設(shè)置約束及設(shè)計(jì)

數(shù)據(jù)庫約束

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

保證某列必須有唯一的值，插入重復(fù)的值就會報(bào)錯

default

規(guī)定給列賦值時(shí)的默認(rèn)值

create table student(id int,name varchar(20) default '匿名');

primary key 主鍵

主鍵約束，是not null 與unique的結(jié)合，確保某列的賦值不能為null，并且是唯一的

auto_increment 自增特點(diǎn)：

1.如果表中沒有記錄，自增從1開始

2.如果有數(shù)據(jù)，從上一條記錄往下自增

3.插入再刪掉數(shù)據(jù)，自增的值不會重復(fù)利用，會按刪掉的那條開始自增

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 外鍵

外鍵約束，在表一中的數(shù)據(jù)必須在表二中存在，要參照完整性準(zhǔn)則

外鍵約束描述的是兩張表的兩個(gè)列之間的“依賴關(guān)系”

外鍵約束會影響表的刪除，例如下面的實(shí)例的class表被關(guān)聯(lián)，所以它不能被輕易刪除

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

指定一個(gè)條件，通過條件來對值進(jìn)行判定

但是mysql并不支持

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

表的設(shè)計(jì)

一對一

一對一設(shè)計(jì)表就比如學(xué)生表和賬戶表，一個(gè)賬戶對應(yīng)到一個(gè)學(xué)生，一個(gè)學(xué)生也只有一個(gè)賬戶

表示方法

1.可以把這兩個(gè)實(shí)體用一張表來表示

2.可以用兩張表來表示，其中一張表包含了另一個(gè)表的id

一對多

一個(gè)學(xué)生應(yīng)該處于一個(gè)班級中，一個(gè)班級可以包含多個(gè)學(xué)生

表示方法：

1.在班級表中，新增一列，表示這個(gè)班級里的學(xué)生id都有啥(mysql沒有數(shù)組類型，redis可以)

2.班級表不變，學(xué)生表中，新增一列classId

多對多

多對多設(shè)計(jì)表就好比學(xué)生表和課程表,一個(gè)學(xué)生可以選多個(gè)課程,一個(gè)課程也可以被多個(gè)學(xué)生選擇

表示方法：

使用一個(gè)關(guān)聯(lián)表，來表示兩個(gè)實(shí)體之間的關(guān)系

多對多建表實(shí)例

-- 學(xué)生表
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)

插入數(shù)據(jù)到實(shí)例實(shí)現(xiàn)多對多

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

mysql> insert into test_course values (1, '數(shù)學(xué)');
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, '數(shù)學(xué)');
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 | 數(shù)學(xué)   |
|  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)

到此這篇關(guān)于MySQL示例講解數(shù)據(jù)庫約束以及表的設(shè)計(jì)的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)庫約束內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: