一、案例擴展

use kgc;
create table if not exists info (
id int(4) zerofill primary key auto_increment,				#指定主鍵的第二種方式
name varchar(10) not null default '匿名',
cardid int(18) not null unique key,
hobby varchar(50));

• if not exists：表示檢測要創(chuàng)建的表是否已存在，如果不存在就繼續(xù)創(chuàng)建
• int(4) zerofill：表示若數(shù)值不滿4位數(shù)，則前面用“0”填充，例0001
• auto_increment：表示此字段為自增長字段，即每條記錄自動遞增1，默認從1開始遞增；自增長字段數(shù)據(jù)不可以重復；自增長字段必須是主鍵；如添加的記錄數(shù)據(jù)沒有指定此字段的值且添加失敗也會自動遞增一次
• unique key：表示此字段唯一鍵約束，此字段數(shù)據(jù)不可以重復；一張表中只能有一個主鍵, 但是一張表中可以有多個唯一鍵
• not null：表示此字段不允許為NULL

二、克隆表

克隆表就是將數(shù)據(jù)表的數(shù)據(jù)記錄生成到新的表中

1、方法一

create table test01 like KY08;		#通過 LIKE 方法，復制 KY08 表結(jié)構(gòu)生成 test01 表
insert into test01 select * from KY08;
#此方法能保證 新表的表結(jié)構(gòu)、表數(shù)據(jù) 跟舊表都是一致的

2、方法二

CREATE TABLE test02 (SELECT * from KY08);
#此方法創(chuàng)建的新表的表數(shù)據(jù)和舊表是一樣的，但可能會出現(xiàn)新表的表結(jié)構(gòu)和舊表的不一致
show create table test02\G					#獲取數(shù)據(jù)表的表結(jié)構(gòu)、索引等信息
SELECT * from test02;

三、清空表

刪除表內(nèi)的所有數(shù)據(jù)

方法一：
delete from test01;
方法二：
truncate table test01;

• DELETE清空表后，返回的結(jié)果內(nèi)有刪除的記錄條目；DELETE工作時是一行一行的刪除記錄數(shù)據(jù)的；如果表中有自增長字段，使用DELETE FROM 刪除所有記錄后，再次新添加的記錄會從原來最大的記錄 ID 后面繼續(xù)自增寫入記錄。
• TRUNCATE 清空表后，沒有返回被刪除的條目；TRUNCATE 工作時是將表結(jié)構(gòu)按原樣重新建立，因此在速度上 TRUNCATE 會比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表內(nèi)數(shù)據(jù)后，ID 會從 1 開始重新記錄。

四、創(chuàng)建臨時表

臨時表創(chuàng)建成功之后，使用SHOW TABLES命令是看不到創(chuàng)建的臨時表的，臨時表會在連接退出后被銷毀。 如果在退出連接之前，也可以可執(zhí)行增刪改查等操作，比如使用 DROP TABLE 語句手動直接刪除臨時表。

CREATE TEMPORARY TABLE 表名 (字段1 數(shù)據(jù)類型,字段2 數(shù)據(jù)類型[,...][,PRIMARY KEY (主鍵名)]);

五、創(chuàng)建外鍵約束，保證數(shù)據(jù)的完整性和一致性。

外鍵的定義：如果同一個屬性字段X在表一中是主鍵，而在表二中不是主鍵，則字段X稱為表二的外鍵。

主鍵表和外鍵表的理解：

• 以公共關(guān)鍵字作主鍵的表為主鍵表（父表、主表）
• 以公共關(guān)鍵字作外鍵的表為外鍵表（從表、外表）

注意：與外鍵關(guān)聯(lián)的主表的字段必須設(shè)置為主鍵。要求從表不能是臨時表，主從表的字段具備相同的數(shù)據(jù)類型、字符長度和約束。

#創(chuàng)建主表 profession
create table profession (pid int(4),proname varchar(50));

#創(chuàng)建從表 student
create table student (id int(4) primary key auto_increment,name varchar(10),age int(3),proid int(4));

#為主表 profession 添加一個主鍵約束。主鍵名建議以“PK_”開頭。
alter table profession add constraint PK_pid primary key (pid);

#為從表 student 表添加外鍵，并將 student 表的 proid 字段和 profession 表的 pid 字段建立外鍵關(guān)聯(lián)。外鍵名建議以“FK_”開頭。
alter table student add constraint FK_pro foreign key (proid) references profession (pid);

desc student;

#插入新的數(shù)據(jù)記錄時，要先主表再從表
insert into profession values(1,'通信工程');
insert into profession values(2,'軟件工程');
insert into student values(1,'zhangsan',18,1);
insert into student values(2,'lisi',19,1);
insert into student values(3,'wangwu',20,2);

#刪數(shù)數(shù)據(jù)記錄時，要先從表再主表，也就是說刪除主鍵表的記錄時必須先刪除其他與之關(guān)聯(lián)的表中的記錄。
delete from student where proid=1;
delete from profession where pid=1;

#查看和刪除外鍵約束
show create table student;
desc student;
alter table student drop foreign key FK_pro;
alter table student drop key FK_pro;

六、MySQL中6種常見的約束

• 主鍵約束（primary key）：字段的值不能重復，不能為null，一個表只能有一個主鍵
• 外鍵約束（foreign key）： 保證相關(guān)聯(lián)表數(shù)據(jù)的完整性和一致性
• 非空約束（not null）：字段的值不能為null
• 唯一性約束（unique [key|index]）：字段的值不能重讀，能為null，一個表可有多個唯一鍵
• 默認值約束（default）：字段的值如果沒有設(shè)置則使用默認值自動填充
• 自增約束（auto_increment）：字段的值如果沒有設(shè)置，默認會從1開始遞增1，要求自增字段必須為主鍵字段

總結(jié)

到此這篇關(guān)于MySQL數(shù)據(jù)庫高級操作的文章就介紹到這了,更多相關(guān)MySQL高級操作內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論