您好,登錄后才能下訂單哦!
這篇“MySQL數(shù)據(jù)庫約束及表的設(shè)計(jì)實(shí)例分析”文章的知識點(diǎn)大部分人都不太理解,所以小編給大家總結(jié)了以下內(nèi)容,內(nèi)容詳細(xì),步驟清晰,具有一定的借鑒價值,希望大家閱讀完這篇文章能有所收獲,下面我們一起來看看這篇“MySQL數(shù)據(jù)庫約束及表的設(shè)計(jì)實(shí)例分析”文章吧。
指定某列的存儲不能為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)
保證某列必須有唯一的值,插入重復(fù)的值就會報錯
規(guī)定給列賦值時的默認(rèn)值
create table student(id int,name varchar(20) default '匿名');
主鍵約束,是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)
外鍵約束,在表一中的數(shù)據(jù)必須在表二中存在,要參照完整性準(zhǔn)則
外鍵約束描述的是兩張表的兩個列之間的“依賴關(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)
指定一個條件,通過條件來對值進(jìn)行判定
但是mysql并不支持
create table test_user ( id int, name varchar(20), sex varchar(1), check (sex ='男' or sex='女') );
一對一設(shè)計(jì)表就比如學(xué)生表和賬戶表,一個賬戶對應(yīng)到一個學(xué)生,一個學(xué)生也只有一個賬戶
表示方法
1.可以把這兩個實(shí)體用一張表來表示
2.可以用兩張表來表示,其中一張表包含了另一個表的id
一個學(xué)生應(yīng)該處于一個班級中,一個班級可以包含多個學(xué)生
表示方法:
1.在班級表中,新增一列,表示這個班級里的學(xué)生id都有啥(mysql沒有數(shù)組類型,redis可以)
2.班級表不變,學(xué)生表中,新增一列classId
多對多設(shè)計(jì)表就好比學(xué)生表和課程表,一個學(xué)生可以選多個課程,一個課程也可以被多個學(xué)生選擇
表示方法 :
使用一個關(guān)聯(lián)表,來表示兩個實(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ì)實(shí)例分析”這篇文章的內(nèi)容,相信大家都有了一定的了解,希望小編分享的內(nèi)容對大家有幫助,若想了解更多相關(guān)的知識內(nèi)容,請關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。