MySQL鍵值的示例分析

這篇文章將為大家詳細講解有關(guān)MySQL鍵值的示例分析，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

創(chuàng)新互聯(lián)服務(wù)項目包括扶綏網(wǎng)站建設(shè)、扶綏網(wǎng)站制作、扶綏網(wǎng)頁制作以及扶綏網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，扶綏網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到扶綏省份的部分城市，未來相信會繼續(xù)擴大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

MySQL 鍵值概述

鍵值類型

• 根據(jù)數(shù)據(jù)存儲要求，選擇鍵值

• index               普通索引

• unique             唯一索引

• fulltext             全文索引

• primary key     主鍵

• foreign key      外鍵

索引介紹

• 索引是什么？

—— 類似于書的目錄

—— 對表中字段進行排序

—— 索引類型包括：Btree(二叉樹)、B+tree、hash

索引優(yōu)缺點

• 索引有點

—— 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性

—— 可以加快數(shù)據(jù)的查詢速度

• 索引缺點

—— 但對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的調(diào)整，降低了數(shù)據(jù)的維護速度

—— 索引需要占物理空間

MySQL 鍵值使用

Index 普通索引

• 使用規(guī)則

—— 一個表中可以有多個 index 字段

—— 字段的值允許重復(fù)，且可以賦 NULL 值

—— 通常把做為查詢條件的字段設(shè)置為 index 字段

—— index 字段標志為 MUL

• 建表時創(chuàng)建索引

—— index(字段名), index(字段名) .. ..

mysql> create table 庫名.表名(
             字段列表,
             index(字段名),
             index(字段名)
             );

創(chuàng)建一個信息表，指定字段中的name和age作為索引（字段name和age中的值要進行排序）

mysql> create table db2.t2(
-> name char(10),
-> age int,
-> class char(7),
-> email char(30),
-> index(name),index(age)
-> );
mysql> desc db2.t2;

• 查看索引信息

• 基本查看

—— desc 庫名.表名;      //查看key那一列

• 詳細查看

—— show index from 表名 \G;
mysql> show index from db2.t2 \G     //因為有兩個字段作為索引，所以下面有兩列，\G以列的形式展現(xiàn)輸出的結(jié)果

mysql> show index from db2.t2;      //下圖是不加 \G 的效果

• 刪除索引

—— drop index 索引名 on 庫名.表名;

mysql> drop index age on db2.t2; //刪除索引age
mysql> desc db2.t2;

mysql> show index from db2.t2 \G //只剩下索引name，索引age已被刪除

向庫db2中的表t2中存入數(shù)據(jù)：

mysql> insert into db2.t2 values("bob",19,"B180601","stu1@163.com");
mysql> insert into db2.t2 values("tom",19,"B180602","stu2@163.com");
mysql> insert into db2.t2 values("lucy",19,"B180603","stu3@163.com");
mysql> insert into db2.t2 values("jack",19,"B180604","stu4@163.com");
mysql> select * from db2.t2;       //表中的數(shù)據(jù)顯示是按照插入表記錄的順序排進行排序的

索引排序的信息在 t2.frm 和 t2.ibd 文件中

[root@DB ~]# ls /var/lib/mysql/db2

注：沒有設(shè)置索引的時，查詢表數(shù)據(jù)是按照表順序逐行進行匹配的；設(shè)置索引后，是按照索引的排序來進行查找的（如：BTree、B+Tree、hash）

• 在已有的表里創(chuàng)建索引

—— create index 索引名 on 表名(字段名);

mysql> use db2;
mysql> show tables;

mysql> desc db2.stuinfo;

mysql> create index name on db2.stuinfo(name);     //索引名一般情況和字段名一樣，索引名也可以用別的名字
mysql> desc db2.stuinfo;

mysql> show index from db2.stuinfo \G;      //查看索引的詳細信息

primary key主鍵

• 使用規(guī)則

—— 字段值不允許重復(fù)，且不允許賦NULL值

—— 一個表中只能有一個primary key字段

—— 多個字段都作為主鍵，稱為復(fù)合主鍵，必須一起創(chuàng)建

—— 主鍵字段的標志是PRI

—— 主鍵通常與 auto_increment 連用

—— 通常把表中唯一標志記錄的字段設(shè)置為主鍵

[記錄編號字段]

• 建表時創(chuàng)建主鍵

—— primary key(字段名)

mysql> create table t8(
             name char(5) primary key,
             id int
             );

格式一：

mysql> create table db2.t3(name char(10) primary key,age int);     //設(shè)置name字段為主鍵，但設(shè)置完主鍵后，自動將約束條件是否為空設(shè)置為NO（即不允許為空）
mysql> desc db2.t3;

格式二：

mysql> create table db2.t4(name char(10),age int,primary key(name));     //也可以先將字段創(chuàng)建出來，最后在指定哪個字段為做主鍵
mysql> desc db2.t4;

向表t3中插入數(shù)據(jù)

mysql> insert into db2.t3 values("bob",19);
mysql> select * from t3;

mysql> insert into db2.t3 values("bob",21);      //錯誤原因，由于字段name是主鍵，所以字段name中的值不允許重復(fù)，表中name字段里面已經(jīng)有個值為bob了，所以在向字段name賦值時就不能再賦bob值了
ERROR 1062 (23000): Duplicate entry 'bob' for key 'PRIMARY'
mysql> insert into db2.t3 values(null,21);     //報錯原因，由于字段name是主鍵，所以字段name不允許賦NULL值
ERROR 1048 (23000): Column 'name' cannot be null

• 在已有表里創(chuàng)建主鍵

—— 格式

mysql> alter table 庫名.表名 add primary key(字段名);
mysql> desc db2.t2;

mysql> select * from db2.t2;

//我們要將字段name設(shè)置為主鍵，所以我們要檢查原表中name字段的值，確保沒有空（NULL）值和重復(fù)的值，如上圖所示，表t2中name字段沒有空值和重復(fù)的值，也可以通過下面的方法
mysql> select name from db2.t2 where name is null;      //查看name字段中的哪些值為空值，輸出結(jié)果顯示沒有
Empty set (0.01 sec)
mysql> alter table db2.t2 add primary key(name);      //設(shè)置字段name為主鍵
mysql> desc db2.t2;

• 刪除主鍵

—— alter table 表名 drop primary key;

注：移除主鍵前，如果有自增屬性，必須先去掉

• 創(chuàng)建復(fù)合主鍵

—— alter table 表名 add primary key(字段名列表);

注：多個字段都作為主鍵時，稱為復(fù)合主鍵，作為主鍵的這幾個字段里的值，在插入記錄時不能同時重復(fù)。比如字段class有兩個值都為classA，字段name的兩個值不能都為tom，即字段name中不能有一樣的名字，可以為tom和lucy，但不能都為tom

創(chuàng)建復(fù)合主鍵，將class字段和name字段都設(shè)置為主鍵

mysql> create table db2.t5(
    -> class char(7),
    -> name char(10),
    -> money enum("no","yes"),
    -> primary key(class,name)
    -> );
mysql> desc db2.t5;

mysql> insert into db2.t5 values("B180601","bob","yes");
mysql> select * from db2.t5;

mysql> insert into db2.t5 values("B180601","bob","yes");     //報錯原因，因為字段class和字段name都為主鍵，所以這兩個字段的值不能同時重復(fù)
ERROR 1062 (23000): Duplicate entry 'B180601-bob' for key 'PRIMARY'
mysql> insert into db2.t5 values("B180602","bob","yes");     //兩個主鍵字段，只要有一個字段的值沒有重復(fù)，另外的主鍵字段不管重復(fù)沒重復(fù)都可以插入
mysql> insert into db2.t5 values("B180602","tom","yes");
mysql> select * from db2.t5;

注：此時出現(xiàn)一個問題，若同一個班出現(xiàn)名字相同的兩個人，然而class字段和name字段都為主鍵，由于這兩個主鍵字段的值不能同時重復(fù)，此時數(shù)據(jù)是不能插入的。解決方案就是先刪除這兩個字段的主鍵，再刪除表數(shù)據(jù)，然后在創(chuàng)建一個stu_num(學(xué)號)字段，設(shè)置stu_num、class、name字段一起做主鍵。

1）刪除主鍵：

mysql> alter table db2.t5 drop primary key(name);     //錯誤原因，由于字段class和字段name一起作為主鍵，所以刪除時不能只刪除一個，必須同時刪除
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(name)' at line 1      
mysql> desc db2.t5;      //發(fā)現(xiàn)主鍵已被刪除

2）創(chuàng)建一個新字段stu_num：

mysql> alter table db2.t5 add stu_num char(9) first;     //添加一個新字段stu_num，并放在表的第一列

mysql> select * from db2.t5;     //由于要設(shè)置字段stu_num為主鍵，所以字段stu_num的值不能為NULL，所以我們要清空表數(shù)據(jù)

3）清空表數(shù)據(jù)：

mysql> delete from db2.t5;
mysql> select * from db2.t5;     //輸出結(jié)果顯示t5表為空表
Empty set (0.00 sec)

4）在已有表里創(chuàng)建復(fù)合主鍵：

mysql> alter table db2.t5 add primary key(stu_num,class,name);
mysql> desc db2.t5;

mysql> insert into db2.t5 values("B18060101","B180601","bob","yes");
mysql> insert into db2.t5 values("B18060102","B180601","bob","yes");
mysql> select * from db2.t5;      //三個字段都為主鍵，只要其中一個主鍵的值不重復(fù)，那么數(shù)據(jù)就能插入成功

//通過加入學(xué)號stu_num字段為，讓三個字段都為主鍵，解決同班同名數(shù)據(jù)插入的問題

• 主鍵與auto_increment(自增) 連用

mysql> create table t8(
             id int primary key auto_increment,
             name char(5)
             );

注：要設(shè)置自增的前提是，字段必須為主鍵才可以設(shè)置自增；設(shè)置自增的字段必須是數(shù)值類型的，字符類型是不能自增的。

示例：創(chuàng)建一個自增主鍵的表

mysql> create table db2.t6(
    -> stu_num int primary key auto_increment,
    -> name char(10),
    -> age int
    -> );
mysql> desc db2.t6;

mysql> insert into db2.t6(name,age) values("bob",19);
mysql> select * from db2.t6;

mysql> insert into db2.t6(name,age) values("tom",20);
mysql> insert into db2.t6(name,age) values("lucy",19);
mysql> select * from db2.t6;

mysql> insert into db2.t6 values(3,"lucy",19);     //錯誤原因，因為字段stu_num是主鍵，所以值是不能重復(fù)的
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> insert into db2.t6 values(9,"lucy",19);     //可以是除了1~3以外的別的數(shù)字，可以按順序，也可以不按順序
mysql> select * from db2.t6;

mysql> insert into db2.t6(name,age) values("jack",25);     //如果在插入一條數(shù)據(jù)，這條數(shù)據(jù)不給stu_num賦值，那么字段stu_num的默認值，是從最大數(shù)開始自增賦值，即字段stu_num默認值為10，而不是4
mysql> select * from db2.t6;

示例：如果清空表記錄，在插入數(shù)據(jù)，那么字段stu_num的值是從1開始自增，還是從10開始自增呢？

mysql> delete from db2.t6;     //清空表記錄
mysql> select * from db2.t6;      //輸出結(jié)果顯示為空表
Empty set (0.00 sec)
mysql> insert into db2.t6(name,age) values("bob",18);      //插入一個數(shù)據(jù)
mysql> select * from db2.t6;     //雖然刪除了表中的全部數(shù)據(jù)，但是會記錄上一次自增到哪個數(shù)字了，然后插入的數(shù)據(jù)會從上一次所記錄的數(shù)字開始自增

mysql> insert into db2.t6(name,age) values(null,"tom",18);      //主鍵不能為空，但此處我們給主鍵字段stu_num賦值為NULL，卻不報錯，原因是空就相當于與沒有給字段stu_num賦值，沒賦值所以就會以自增的方式進行賦值，所以此處賦值NULL不會報錯

mysql> select * from db2.t6;

示例：通常把表中唯一標識記錄的字段設(shè)置為主鍵，如記錄編號字段

mysql> alter table db2.stuinfo add id int primary key auto_increment first;
mysql> desc db2.stuinfo;

mysql> select * from db2.stuinfo;

foreign key外鍵

• 外鍵功能

—— 插入記錄時，字段值在另一個表字段值范圍內(nèi)選擇

• 使用規(guī)則

—— 表存儲引擎必須是innodb

—— create table 庫名.表名(... ...) engine=innodb;      //指定表存儲的引擎

—— 插入記錄的表的字段與被參照的表的字段類型要一致

—— 被參照字段的值要唯一且不能為空，所以必須要是索引類型的主鍵(primary key)

• 創(chuàng)建外鍵

—— create table 表名(
        字段名列表,
        foreign key(字段名) references 庫名.表名(字段名)       //指定外鍵
        on update cascde                                               //同步更新
        on delete cascade                                               //同步刪除
         ) engine=innodb;                                               //指定存儲引擎                                                      -5

創(chuàng)建一個員工表作為參考表：

mysql> create table db2.yuangong(
    -> id int primary key auto_increment,
    -> name char(20),
    -> sex enum("boy","girl")
    -> )engine=innodb;
mysql> desc db2.yuangong;

mysql> insert into db2.yuangong(name,sex) values("bob","boy");
mysql> insert into db2.yuangong(name,sex) values("lucy","girl");
mysql> select * from db2.yuangong;

創(chuàng)建一個工資表設(shè)置外鍵，以員工表作為參考表：

mysql> create table db2.gongzi(
    -> id int,
    -> salary float(7,2),
    -> foreign key(id) references db2.yuangong(id)
    -> on update cascade
    -> on delete cascade
    -> ) engine=innodb;
mysql> desc db2.gongzi;

//如圖所示，外鍵創(chuàng)建成功后會自動創(chuàng)建索引，對數(shù)據(jù)進行排序，所以標志就是普通索引的標志，我們可以通過另一種方法進行查看
查看是不是外鍵的方法：
mysql> show create table db2.gongzi;      //查看創(chuàng)建表的命令

mysql> insert into db2.gongzi values(1,10000);
mysql> insert into db2.gongzi values(2,20000);
mysql> select * from db2.gongzi;

mysql> insert into db2.gongzi values(3,20000);     //給id為3員工發(fā)工資報錯，原因：給gongzi表里字段id賦值3，但由于gongzi表設(shè)置了外鍵，將gongzi表的id字段參考了yuangong表的id字段，所以給gongzi表id字段賦值時，這個值必須要在yuangong表的id字段里面有，才能進行賦值。

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`gongzi`, CONSTRAINT `gongzi_ibfk_1` FOREIGN KEY (`id`) REFERENCES `yuangong` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

• 同步更新

將yuangong表中id字段值為2的改為8：

mysql> select * from db2.yuangong;

mysql> update db2.yuangong set id=8 where id=2;      //將yuangong表中id字段的2改為8，同步更新表gongzi表id字段為2的記錄

mysql> select * from db2.yuangong;

mysql> select * from db2.gongzi;     //發(fā)現(xiàn)yuangong表的id字段值從2改為8后，gongzi表的id字段的值也從2變?yōu)?

• 同步刪除

將yuangong表中id字段值為8的刪除掉：

mysql> delete from db2.yuangong where id=8;       //將yuangong表中id字段為8刪除掉，同步刪除表gongzi表id字段為8的記錄
mysql> select * from db2.yuangong;

mysql> select * from db2.gongzi;        //發(fā)現(xiàn)yuangong表的id字段值為8被刪除后，gongzi表的id字段值為8的也被刪除

• 注意事項

mysql> insert into db2.yuangong(name,sex) values("jack","boy");
mysql> select * from db2.yuangong;

mysql> insert into db2.gongzi values(3,30000);
mysql> select * from db2.gongzi;

mysql> insert into db2.gongzi values(3,30000);)
mysql> insert into db2.gongzi values(3,30000);
mysql> insert into db2.gongzi values(null,65000);
mysql> select * from db2.gongzi;

注：如上所示，字段id為3的被重復(fù)賦值，且字段id也被賦空值。因為參考表yuangong表里的字段id里面的值有1和3，所以gongzi表中的字段id就可以重復(fù)賦值1或3。這樣賦值在生產(chǎn)環(huán)境中很不合理，所以我們通過將gongzi表中的id字段設(shè)置為主鍵，這樣就能解決重復(fù)賦值和賦值空值的問題。

1）刪除gongzi表的數(shù)據(jù)

mysql> delete from db2.gongzi;     //刪除gongzi表中的所有記錄
mysql> select * from db2.gongzi;       //查詢表記錄為空，說明表記錄已被刪除
Empty set (0.00 sec)

2）將字段id設(shè)置為主鍵

mysql> desc db2.gongzi;

mysql> alter table db2.gongzi add primary key(id);      //將gongzi表中的id字段設(shè)置為主鍵
mysql> desc db2.gongzi;

3）進行賦值測試

mysql> insert into db2.gongzi values(1,10000);
mysql> insert into db2.gongzi values(3,30000);
mysql> insert into db2.gongzi values(1,10000);      //再次重復(fù)賦值，出現(xiàn)錯誤，原因是主鍵不允許有重復(fù)的值
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into db2.gongzi values(3,30000);      //再次重復(fù)賦值，出現(xiàn)錯誤，原因是主鍵不允許有重復(fù)的值
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> insert into db2.gongzi values(null,30000);        //賦空值，出現(xiàn)錯誤，原因是主鍵不允許賦空值
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into db2.gongzi values(2,30000);      //參考表yuangong表中的id字段沒有值為2的記錄，不在參考表范圍內(nèi)的值不能進行賦值
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`gongzi`, CONSTRAINT `gongzi_ibfk_1` FOREIGN KEY (`id`) REFERENCES `yuangong` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

• 刪除外鍵

—— alter table 庫名.表名 drop foreign key 外鍵名;

mysql> show create table db2.gongzi;

//注：紅線圈起來的地方就是外鍵的名稱

mysql> alter table db2.gongzi drop foreign key gongzi_ibfk_1; //刪除外鍵

mysql> show create table db2.gongzi; //查看創(chuàng)建表的命令，發(fā)現(xiàn)沒有創(chuàng)建外鍵的記錄了

此時我們就可以插入除了yuangong表id字段的值范圍以外的值了

mysql> insert into db2.gongzi values(2,30000);
mysql> insert into db2.gongzi values(9,30000);
mysql> select * from db2.gongzi;

由于gongzi表的id字段還是主鍵，所以不能賦重復(fù)的值和空值

mysql> insert into db2.gongzi values(9,30000);
ERROR 1062 (23000): Duplicate entry '9' for key 'PRIMARY'
mysql> insert into db2.gongzi values(null,30000);
ERROR 1048 (23000): Column 'id' cannot be null

關(guān)于“MySQL鍵值的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。

網(wǎng)站題目：MySQL鍵值的示例分析
分享網(wǎng)址：http://bm7419.com/article4/jdiiie.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站改版、網(wǎng)站內(nèi)鏈、網(wǎng)站導(dǎo)航、App開發(fā)、Google、網(wǎng)站設(shè)計公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)