Mysql系列：外鍵以及資料庫的增删改查你了解多少

前言

int：整型，預設長度是11
float：單精度浮點，4位元組32位
double：雙精度浮點，8位元組64位
char：不可變長的字元類型，讀取速度快，耗空間，長度不足會補空格。
varchar：可變長的字元類型，但讀取資料比 char 低，容易産生記憶體碎片
text ：文本
image：圖檔
decimal(6,2) ：定義6個有效長度數字，小數點後面有兩位

char 最多支援255個字元，char如果存入資料的實際長度比指定長度要小，會補空格至指定長度；如果存入的資料的實際長度大于指定長度，低版本的 Mysql 會被截取前255個字元，高版本會報錯。

注 varchar 類型：在4.0版本以下，varchar(20)，指的是20位元組；5.0版本以上，varchar(20)，指的是20字元。

檢視資料庫資訊語句

首先登陸MySQL，也可以用SQL工具如 Navicat 遠端連接配接，前提是要在Linux Mysql 中用 grant 進行授權，允許遠端登入。

#授予root使用者可以在所有終端遠端登入，使用的密碼是123456
grant all privileges on *.* to 'root'@'%' identified by '123456';

#對所有資料庫和所有表有操作權限 with grant option
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
1.2.3.4.5.6.7.

Navicat 連接配接參數

添加描述

為了練習在Linux 中操作，下面的都是在 Linux 指令框中直接敲的。

Linux 中登入Mysql
mysql -u root -p密碼

1.檢視目前伺服器中的資料庫  
SHOW DATABASES;

2.檢視資料庫中包含的表
USE 資料庫名    #注意：資料庫名區分大小寫
SHOW TABLES;

3.檢視表的結構（設計、字段）
DESCRIBE [資料庫名.]表名
或
DESC 表名

4\. 檢視建立表的指令
SHOW CREATE TABLE 表名

5.查詢結果行轉列檢視 \G
SELECT * FROM USER_INFO\G;

6\. 檢視目前所在資料庫
select database()
1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.

添加描述

SQL 語句

SQL語句用于維護管理資料庫，包括資料查詢、通路控制、資料更新、對象管理、備份等功能。

添加描述

建立、删除資料庫資料表

建立新的資料庫

CREATE DATABASE 資料庫名;

例如：CREATE DATABASE SCHOOL;
1.2.3.

在資料庫中建立表

CREATE TABLE 表名 (字段1 資料類型,字段2 資料類型[,...][,PRIMARY KEY (主鍵名)]);

#主鍵一般選擇能代表唯一性的字段不允許取空值（NULL），一個表隻能有一個主鍵。

例：USE SCHOOL;
CREATE TABLE STUDENT (ID int(10) NOT NULL,NAME varchar(20),AGE int(3),SEX char(2),PRIMARY KEY (ID));

1.2.3.4.5.6.

添加描述

删除表

DROP TABLE [資料庫名.]表名;               #如不用USE進入庫中，則需加上資料庫名
1.

删除資料庫

DROP DATABASE 資料庫名;   #會連庫中的表一起删除

删除操作要小心，删除前記得被備份
1.2.3.

添加描述

向表中添加、删除記錄、查詢記錄

#先建立 STARBUCKS 資料庫，再建立 USER_INFO 表，添加一些字段

CREATE DATABASE STARBUCKS;
USE STARBUCKS;

DROP TABLE IF EXISTS `USER_INFO`;
CREATE TABLE `USER_INFO`  (
  `ID` int(32) NOT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  `TYPE` varchar(32) DEFAULT NULL,
  `ADD_TIME` varchar(20) DEFAULT NULL,
  `IS_USED` char(5) DEFAULT NULL,
  PRIMARY KEY (`ID`) 
)
1.2.3.4.5.6.7.8.9.10.11.12.13.14.

添加描述

向表中添加記錄

INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);

例：
#一條一條全字段添加
INSERT INTO USER_INFO(ID,NAME,TYPE,ADD_TIME,IS_USED) VALUES (1001, '咖啡機', '機器齊全', '2021-06-22', 'D0001');
INSERT INTO USER_INFO VALUES (1002, '奶茶機', '裝置', '2021-06-23', 'D0001');

#或一條指令添加多條
INSERT INTO USER_INFO (ID,NAME,TYPE,ADD_TIME,IS_USED) VALUES (1003, '牛奶', '食品', '2021-06-24', 'D0002'),
(1004, '包裝機', '裝置', '2021-06-20', 'D0001'),
(1005, '糖塊', '食品', '2021-06-25', 'D0001'),
(1006, '吸管', '裝置', '2021-06-20', 'D0001'),
(1007, '奶油', '食品', '2021-06-25', 'D0002');

1.2.3.4.5.6.7.8.9.10.11.12.13.

添加描述

SELECT 查詢語句

SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 條件表達式];

#從0行開始共幾行
SELECT * FROM 表名 limit 行數 

#根據行數範圍取記錄數
SELECT * FROM 表名 limit 開始行數（不包括）,從開始行數開始的共幾行
1.2.3.4.5.6.7.

添加描述

UPDATE 更新注意WHERE 加更新的條件，不然會更新所有

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 條件表達式];

如 UPDATE USER_INFO SET TYPE='儀器' WHERE ID=1001;
1.2.3.

添加描述

DELETE 删除記錄注意WHERE 加删除記錄的條件，不然會删除所有

DELETE FROM 表名 [WHERE 條件表達式];

例：delete from class2 where id=4;
1.2.3.

添加描述

修改表名，添加、修改、删除字段，添加唯一限制

修改表名

ALTER TABLE 舊表名 RENAME 新表名;

例：ALTER TABLE USER_INFO RENAME USER_MESG;
SHOW TABLES;
1234
1.2.3.4.5.

添加描述

表中添加字段

ALTER TABLE 表名 ADD 字段名 varchar(50) default '預設值';
#default 表示此字段設定預設值；可與 NOT NULL 配合使用，預設值立即生效

例：
ALTER TABLE USER_MESG ADD STATUS VARCHAR(20) DEFAULT '在用';
1.2.3.4.5.

添加描述

删除表中字段

ALTER TABLE 表名 DROP 字段名;
1.

修改字段（列）名，添加唯一鍵限制

ALTER TABLE 表名 CHANGE 舊列名 新列名 資料類型 [unique key];

例：
ALTER TABLE USER_MESG CHANGE NAME DEV_NAME VARCHAR(30) UNIQUE KEY;

#驗證唯一鍵限制，添加重名的限制字段
INSERT INTO USER_MESG VALUES (1008,'牛奶','飲料','2021-06-28','D0001','已過期');
#觸發唯一鍵限制，新增失敗

UNIQUE KEY 唯一鍵：可以用有空值，不能出現重複值，也不能為 NULL，

1.2.3.4.5.6.7.8.9.10.

添加描述

檢視、删除、添加表中的索引

#檢視表有哪些索引
 SHOW INDEXES FROM 表名;
1.2.

添加描述

單獨新增去除唯一鍵限制

#給字段 DEV_NAM 增加唯一鍵限制
ALTER TABLE USER_MESG ADD UNIQUE INDEX (DEV_NAME);

#給字段 DEV_NAM 去除唯一鍵限制
#檢視索引表中，字段 DEV_NAME 對應的 索引名 Key_name，根據索引名删除索引

ALTER TABLE USER_MESG DROP INDEX 索引名;

1.2.3.4.5.6.7.

添加描述

擴充功能，字段值自增等

use school;
CREATE TABLE IF NOT EXISTS AREA_INFO (                                                                                                         -> AREA_ID int(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,    #指定主鍵的另一種方式
-> AREA_NAME VARCHAR(20) NOT NULL UNIQUE KEY,
->  IS_USED CHAR(5) NOT NULL);

#---------------指令解釋--------------------------------
#if not exists：表示檢測要建立的表是否已存在，如果不存在就繼續建立
#int(4) zerofill：表示若數值不滿4位數，則前面用“0”填充，例0001
#auto_increment：表示此字段為自增長字段，即每條記錄自動遞增1，預設從1開始遞增；自增長字段資料不可以重複；
#自增長字段必須是主鍵；字段類型必須是 int 類型，如添加的記錄資料沒有指定此字段的值且添加失敗也會自動遞增一次
#unique key：表示此字段唯一鍵限制，此字段資料不可以重複；一張表中隻能有一個主鍵, 但是一張表中可以有多個唯一鍵
#not null：表示此字段不允許為NULL

1.2.3.4.5.6.7.8.9.10.11.12.

添加描述

克隆表，将資料表的資料記錄生成到新的表中

方法一：使用 like

#先克隆表結構
#在插入所有資料
CREATE TABLE AREA_CLONE1 LIKE AREA_INFO;     #通過 LIKE 方法，複制 AREA_INFO 表結構生成 AREA_CLONE1 表
INSERT INTO AREA_CLONE1 SELECT * FROM AREA_INFO;
1.2.3.4.

添加描述

如果兩張表結構一樣，可将一張表的查詢結果集傳回給 另一張表
INSERT INTO 表1 SELECT * FROM 表2
1.2.

添加描述

方法二：直接克隆

#直接将源表的查詢結果給建立的新表

CREATE TABLE 克隆表名(SELECT * FROM 源表名);

SHOW CREATE TABLE 克隆表名\G;                   #擷取資料表的表結構、索引等資訊
1.2.3.4.5.

注意：直接克隆無法複制原表的主鍵、自動增長、限制、索引等配置。用 LIKE 可以。

添加描述

删除記錄後主鍵記錄重頭開始自增

DELETE FROM 表名;
#DELETE清空表後，傳回的結果内有删除的記錄條目；DELETE工作時是一行一行的删除記錄資料的；如果表中有自增長字段，使用DELETE FROM 删除所有記錄後，再次新添加的記錄會從原來最大的記錄 ID 後面繼續自增寫入記錄。

truncate table 表名;
#TRUNCATE 清空表後，沒有傳回被删除的條目；TRUNCATE 工作時是将表結構按原樣重建立立，是以在速度上 TRUNCATE 會比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内資料後，ID 會從 1 開始重新記錄。
#相當于直接格式化表
1.2.3.4.5.6.

建立臨時表

臨時表是在目前連接配接中生效的表。

臨時表建立成功之後，使用SHOW TABLES指令是看不到建立的臨時表的；
臨時表會在連接配接退出後被銷毀。如果在退出連接配接之前，也可以可執行增删改查等操作，比如使用 DROP TABLE 語句手動直接删除臨時表。

CREATE TEMPORARY TABLE 表名 (字段1 資料類型,字段2 資料類型[,...][,PRIMARY KEY (主鍵名)]);

例：
CREATE TEMPORARY TABLE TEM_TABLE (ID int(4) PRIMARY KEY, REMARK VARCHAR(20));

INSERT INTO TEM_TABLE VALUES(1001,'臨時表');
SELECT * from TEM_TABLE;
1.2.3.4.5.6.7.

添加描述

執行 quit 退出連接配接後，再次登入，就操作不了臨時表了
1.

添加描述

建立外鍵限制，保證資料的完整性和一緻性

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

主鍵表和外鍵表的了解：
（1）以公共關鍵字作主鍵的表為主鍵表（父表、主表）
（2）以公共關鍵字作外鍵的表為外鍵表（從表、外表）

注意：
1\. 與外鍵關聯的主表的字段必須設定為主鍵。
2\. 要求從表不能是臨時表，主從表的字段具備相同的資料類型、字元長度和限制。

1.2.3.4.5.6.7.8.9.

#建立主表HOBBY 
CREATE TABLE HOBBY (HOBID int(4),HOBNAME VARCHAR(50));

#建立從表HOBBY_DETAIL 
CREATE TABLE HOBBY_DETAIL (ID int(4) PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),AGE int(3),HOBID int(4));

#為主表HOBBY 添加一個主鍵限制。主鍵名建議以“PK_”開頭。
ALTER TABLE HOBBY ADD CONSTRAINT PK_HOBID PRIMARY KEY (HOBID);

#為從表HOBBY_DETAIL 表添加外鍵，并将 HOBBY_DETAIL 表的 hobid 字段和 HOBBY 表的 hobid 字段建立外鍵關聯。
#外鍵名建議以“FK_”開頭。
ALTER TABLE HOBBY_DETAIL ADD CONSTRAINT FK_HOB FOREIGN KEY (HOBID) REFERENCES HOBBY (HOBID);

DESC HOBBY_DETAIL ;

#插入新的資料記錄時，要先主表再從表
INSERT INTO HOBBY VALUES(1,'Football');
INSERT INTO HOBBY_DETAIL VALUES(1,'Messi',32,1);

1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.

添加描述

#删數資料記錄時，要先從表再主表，也就是說删除主鍵表時必須先删除其他與之關聯的表。
DROP TABLES HOBBY_DETAIL ;
DROP TABLES HOBBY ;

#檢視和删除外鍵限制
SHOW CREATE TABLE HOBBY_DETAIL ;
ALTER TABLES HOBBY_DETAIL DROP foreign key FK_HOB ;  #FK_HOB 是之前添加的外鍵名
ALTER TABLES HOBBY_DETAIL DROP key FK_HOB ;

DESC HOBBY_DETAIL ;
1.2.3.4.5.6.7.8.9.10.11.

添加描述

去除外鍵後就可以删除表了

DROP TABLES HOBBY_DETAIL ;

DROP TABLES HOBBY ;

MySQL 六種限制

主鍵限制（primary key）
外鍵限制（foreign key）
非空限制（not null）
唯一性限制（unique key|index）
預設值限制（default）
自增限制（auto_increment）

key 列值的含義：

如果鍵是 PRI，則列是主鍵或多列主鍵中的列之一。
如果鍵是 UNI，則該列是唯一索引的第一列。(唯一索引允許多個空值，但可以通過檢查NULL字段來判斷該列是否允許空。)
如果鍵為 MUL，則該列是非唯一索引的第一列，其中允許在列中多次出現給定值。

Mysql系列：外鍵以及資料庫的增删改查你了解多少

前言

目錄

最後

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述