Mysql：MySQL數據管理

外鍵管理

外鍵概念

　　如果公共關鍵字在一個關係中是主關鍵字，那麼這個公共關鍵字被稱為另一個關係的外鍵。由此可見，外鍵表示了兩個關係之間的相關聯繫。以另一個關係的外鍵作主關鍵字的表被稱為主表，具有此外鍵的表被稱為主表的從表。

　　在實際操作中，將一個表的值放入第二個表來表示關聯，所使用的值是第一個表的主鍵值(在必要時可包括複合主鍵值)。此時，第二個表中保存這些值的屬性稱為外鍵(foreign key)。

外鍵作用

　　保持數據一致性，完整性，主要目的是控制存儲在外鍵表中的數據,約束。 使兩張表形成關聯，外鍵只能引用外表中的列的值或使用空值。

創建外鍵

• 建表時指定外鍵約束

• 

  # 創建外鍵的方式一 : 創建子表同時創建外鍵
  
  # 年級表(id\年級名稱)
  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
  
  # 學生信息表
  #(學號,姓名,性別,年級,手機,地址,出生日期,郵箱,身份證號)
  
  CREATE TABLE `student` (
    `studentno` INT(4) NOT NULL COMMENT '學號',
    `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `sex` TINYINT(1) DEFAULT '1' COMMENT '性別',
    `gradeid` INT(10) DEFAULT NULL COMMENT '年級',
    `phoneNum` VARCHAR(50) NOT NULL COMMENT '手機',
    `address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
    `borndate` DATETIME DEFAULT NULL COMMENT '生日',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '郵箱',
    `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份證號',
    PRIMARY KEY (`studentno`),
    KEY `FK_gradeid` (`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
  ) ENGINE=INNODB DEFAULT CHARSET=utf8

  

• 建表後修改

• # 創建外鍵方式二 : 創建子表完畢後,修改子表添加外鍵
  ALTER TABLE student
  ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

刪除外鍵

註意 : 刪除具有主外鍵關係的表時 , 要先刪子表 , 後刪主表

# 刪除外鍵
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
# 發現執行完上面的,索引還在,所以還要刪除索引
# 註:這個索引是建立外鍵的時候預設生成的
ALTER TABLE student DROP INDEX FK_gradeid;

資料庫數據管理

資料庫意義

• 數據存儲
• 數據管理

管理資料庫數據方法

• 通過SQLyog等管理工具管理資料庫數據
• 通過DML語句管理資料庫數據

DML語言

• DML(數據操作語言)
• 用於操作資料庫對象中所包含的數據
• 包括 :
  • INSERT (添加數據語句)
  • UPDATE (更新數據語句)
  • DELETE (刪除數據語句)

添加數據

INSERT命令

• 語法 :

  INSERT INTO 表名[(欄位1,欄位2,欄位3,...)] VALUES('值1','值2','值3')

註意 :

• 欄位或值之間用英文逗號隔開.
• '欄位1,欄位2...'該部分可省略 , 但添加的值務必與表結構,數據列,順序相對應,且數量一致.
• 可同時插入多條數據 , values 後用英文逗號隔開.

# 使用語句如何增加語句?
# 語法 : INSERT INTO 表名[(欄位1,欄位2,欄位3,...)] VALUES('值1','值2','值3')
INSERT INTO grade(gradename) VALUES ('大一');

# 主鍵自增,那能否省略呢?
INSERT INTO grade VALUES ('大二');

# 查詢:INSERT INTO grade VALUE ('大二')錯誤代碼： 1136
# Column count doesn`t match value count at row 1

# 得出結論:'欄位1,欄位2...'該部分可省略 , 但添加的值務必與表結構,數據列,順序相對應,且數量一致.

# 一次插入多條數據
INSERT INTO grade(gradename) VALUES ('大三'),('大四');

練習題目

　　自己使用INSERT語句為課程表subject添加數據 . 使用到外鍵.

修改數據

UPDATE命令

• 語法 :

  UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

註意 :

• column_name 為要更改的數據列
• value 為修改後的數據 , 可以為變數 , 具體指 , 表達式或者嵌套的SELECT結果
• condition 為篩選條件 , 如不指定則修改該表的所有列數據

WHERE條件子句

可以簡單的理解為 : 有條件地從表中篩選數據

刪除數據

DELETE命令

• 語法

DELETE FROM 表名 [WHERE condition];

註意 : condition為篩選條件 , 如不指定則刪除該表的所有列數據

TRUNCATE命令

• 用於完全清空表數據 , 但表結構 , 索引 , 約束等不變 ;
• 語法 : TRUNCATE [TABLE] table_name;

註意 : 區別於DELETE命令

• 相同 : 都能刪除數據 , 不刪除表結構 , 但TRUNCATE速度更快

• 不同 :

  • 使用TRUNCATE TABLE 重新設置AUTO_INCREMENT計數器
  • 使用TRUNCATE TABLE不會對事務有影響

• 

  # 創建一個測試表
  CREATE TABLE `test` (
    `id` INT(4) NOT NULL AUTO_INCREMENT,
    `coll` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=INNODB DEFAULT CHARSET=utf8
  
  # 插入幾個測試數據
  INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');
  
  # 刪除表數據(不帶where條件的delete)
  DELETE FROM test;
  # 結論:如不指定Where則刪除該表的所有列數據,自增當前值依然從原來基礎上進行,會記錄日誌.
  
  # 刪除表數據(truncate)
  TRUNCATE TABLE test;
  # 結論:truncate刪除數據,自增當前值會恢復到初始值重新開始;不會記錄日誌.
  
  # 同樣使用DELETE清空不同引擎的資料庫表數據.重啟資料庫服務後
  # InnoDB : 自增列從初始值重新開始 (因為是存儲在記憶體中,斷電即失)
  # MyISAM : 自增列依然從上一個自增數據基礎上開始 (存在文件中,不會丟失)