MySQL 的資料管理及 DML 語句

外來鍵

在school資料庫中再建立一年級grade表

CREATE TABLE `grade`(
    `gradeid` INT(10) not NULL auto_increment COMMENT '年級id',
    `gradename` VARCHAR(50) not null COMMENT '年級名稱',
    PRIMARY KEY (gradeid)
)ENGINE=INNODB DEFAULT charset=utf8

學生表的gradeid欄位要去引用年級表的gradeid
1.定義外來鍵key
2.給這個外來鍵新增約束(執行引用) references 引用
建立表的時候沒有外來鍵關係

ALTER TABLE student ADD KEY `FK_gradeid` (`gradeid`);

ALTER TABLE student DROP KEY `FK_gradeid`;

ALTER TABLE student ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

alter table 表名 add constraint 約束名 foreign key(作為外來鍵的列) references 主表(主表欄位)

相當於

create table if not exists `student`(
    `id` int(4) not null auto_increment comment '學號',
    `name` varchar(30) not null default '匿名' comment '姓名',
    `pwd` varchar(20) not null default '123456' comment '密碼',
    `sex` varchar(2) not null default '女' comment '性別',
    `birthday` datetime default null comment '出生日期',
    `address` varchar(100) default null comment '家庭住址',
    `email` varchar(50) default null comment '郵箱',
    primary key (`id`),
    key `FK_gradeid`(`gradeid`),
    constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`)
)engine=innodb default charset=utf8;

注意要使用外來鍵約束，兩個表中都需要有對應的欄位，並且能互相關聯上。
刪除有外來鍵關係的表，必須要先刪除引用別人的表（從表），再刪除被引用的表（主表）

以上的操作都是物理外來鍵，資料庫級別的外來鍵，我們不建議使用。(避免資料庫過多造成困擾)

最佳實踐

資料庫就是單純的表，只用來存資料，只有行(資料)和列(欄位)
我們想使用多張表的資料，想使用外來鍵（程式去實現）

DML(資料庫管理語言)

資料庫意義：資料儲存，資料管理。

insert
update
delete

新增

insert

insert into 表名(欄位名1,欄位2,欄位3...) values('值1','值2',...)

-- 如果不寫欄位，就會一一匹配，一般寫插入語句，一定要資料和欄位一一匹配
INSERT INTO student2(name,pwd,sex) VALUES('hudu','123123','男');
INSERT INTO student2(name,sex) VALUES('alex','男');

注意：
1.欄位和欄位之間用英文逗號隔開
2.欄位是可以省略的，但是後面的值必須要一一對應，不能少
3.可以同時插入多條資料，values後面的值，需要使用逗號隔開即可

修改

updata

update 表名 set column_name=value where 判斷條件

-- 修改學員的名字
update `student2` set `name`='Alex' where id=2;

-- 不指定條件的情況下會改動所有的表！
update `student` set `name`=`哈哈`;

-- 修改多個屬性
update `student` set `name`=`哈哈`,`email`='213123124@icloud.com';

條件：
where 子句運算子id等於，大於，小於某個值
操作符會返回boolean值

操作符	含義	範圍	結果
=	等於	5=6	false
<>或!	不等於	5<>6	true
>
<
<=
>=
between and	區間[]	[2,5]
and	&&	5>1 and 1>2	false
or	\|\|	5>1 or 1>2	true

-- 都表示當前時間
DATE(NOW())
current_time

UPDATE student2 SET birthday=DATE(NOW()) WHERE id = 4;

注意：
value可以是一個具體的值，也可以是一個變數。
多個設定屬性之間，使用逗號隔開

刪除

delete

語法：delete from 表名 where 條件

-- 避免這樣寫
delete from `student`

-- 刪除指定資料
delete from `student` where id = 1;

truncate 命令

作用：完全清空一個資料庫表，表的結構和索引約束不會變！

-- 清空表
truncate `student`

delete和truncate區別

相同點，都能刪除資料，而且都不會刪除表結構
不同：
- truncate 重新設定自增列計數器會歸零
- truncate 不會影響事務

測試

CREATE TABLE `test`(
    `id` INT(4) auto_increment,
    `coll` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');

MySQL 的資料管理
然後使用delete

delete from `student`
-- 再次插入資料
INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');

MySQL 的資料管理
發現delete是影響自增的

使用truncate

truncate table `test`;
INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');

MySQL 的資料管理
發現自增又是從1開始

delete刪除的問題，重啟資料庫，現象

innodb自增列會重新從1開始(存在記憶體當中，斷點即失，新版本好像修復了這個問題)
MYISAM 繼續從上一個自增量開始(存在檔案中)

本作品採用《CC 協議》，轉載必須註明作者和本文連結

MySQL 的資料管理及 DML 語句

外來鍵

DML(資料庫管理語言)

新增

修改

刪除

相關文章