MySQL 資料庫增刪查改、克隆、外來鍵等操作總結

2022-05-13 21:50:44

SQL 欄位資料型別

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;

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_INFOG;
6. 檢視當前所在資料庫
select database()

SQL 語句

SQL語句用於維護管理資料庫，包括資料查詢、存取控制、資料更新、物件管理、備份等功能。

SQL語言	功能
DDL	資料定義語言，用於建立、刪除資料庫物件，如庫、表、索引等
DML	資料操縱語言，用於對錶中的資料進行管理
DQL	資料查詢語言，用於從資料表中查詢符合條件的資料記錄
DCL	資料控制語言，用於設定或者更改資料庫使用者或角色許可權

建立、刪除資料庫資料表

建立新的資料庫

CREATE DATABASE 資料庫名;
例如：CREATE DATABASE SCHOOL;

在資料庫中新建表

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));

刪除表

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

刪除資料庫

DROP DATABASE 資料庫名;   #會連庫中的表一起刪除
刪除操作要小心，刪除前記得被備份

向表中新增、刪除記錄、查詢記錄

#先建立 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`) 
)

向表中新增記錄

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');

SELECT 查詢語句

SELECT 欄位名1,欄位名2[,...] FROM 表名 [WHERE 條件表示式];
#從0行開始共幾行
SELECT * FROM 表名 limit 行數 
#根據行數範圍取記錄數
SELECT * FROM 表名 limit 開始行數（不包括）,從開始行數開始的共幾行

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

UPDATE 表名 SET 欄位名1=欄位值1[,欄位名2=欄位值2] [WHERE 條件表示式];
如 UPDATE USER_INFO SET TYPE='儀器' WHERE ID=1001;

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

DELETE FROM 表名 [WHERE 條件表示式];
例：delete from class2 where id=4;

修改表名，新增、修改、刪除欄位，新增唯一約束

修改表名

ALTER TABLE 舊錶名 RENAME 新表名;
例：ALTER TABLE USER_INFO RENAME USER_MESG;
SHOW TABLES;

表中新增欄位

ALTER TABLE 表名 ADD 欄位名 varchar(50) default '預設值';
#default 表示此欄位設定預設值；可與 NOT NULL 配合使用，預設值立即生效
例：
ALTER TABLE USER_MESG ADD STATUS VARCHAR(20) DEFAULT '在用';

刪除表中欄位

ALTER TABLE 表名 DROP 欄位名;

修改欄位（列）名，新增唯一鍵約束

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，

檢視、刪除、新增表中的索引

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

單獨新增去除唯一鍵約束

#給欄位 DEV_NAM 增加唯一鍵約束
ALTER TABLE USER_MESG ADD UNIQUE INDEX (DEV_NAME);
#給欄位 DEV_NAM 去除唯一鍵約束
#檢視索引表中，欄位 DEV_NAME 對應的 索引名 Key_name，根據索引名刪除索引
ALTER TABLE USER_MESG DROP INDEX 索引名;

擴充套件功能，欄位值自增等

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

資料表高階操作

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

方法一：使用 like

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

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

方法二：直接克隆

#直接將源表的查詢結果給建立的新表
CREATE TABLE 克隆表名(SELECT * FROM 源表名);
SHOW CREATE TABLE 克隆表名G;					#獲取資料表的表結構、索引等資訊

注意：直接克隆無法複製原表的主鍵、自動增長、約束、索引等設定。 用 LIKE 可以。

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

DELETE FROM 表名;
#DELETE清空表後，返回的結果內有刪除的記錄條目；DELETE工作時是一行一行的刪除記錄資料的；如果表中有自增長欄位，使用DELETE FROM 刪除所有記錄後，再次新新增的記錄會從原來最大的記錄 ID 後面繼續自增寫入記錄。
truncate table 表名;
#TRUNCATE 清空表後，沒有返回被刪除的條目；TRUNCATE 工作時是將表結構按原樣重新建立，因此在速度上 TRUNCATE 會比 DELETE 清空錶快；使用 TRUNCATE TABLE 清空表內資料後，ID 會從 1 開始重新記錄。
#相當於直接格式化表

建立臨時表

臨時表是在當前連線中生效的表。

臨時表建立成功之後，使用 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;

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

建立外來鍵約束，保證資料的完整性和一致性

外來鍵的定義：如果同一個屬性欄位X 在表一中是主鍵，而在表二中不是主鍵，則欄位X稱為表二的外來鍵。
主鍵表和外來鍵表的理解：
（1）以公共關鍵字作主鍵的表為主鍵表（父表、主表）
（2）以公共關鍵字作外來鍵的表為外來鍵表（從表、外表）
注意：
1. 與外來鍵關聯的主表的欄位必須設定為主鍵。
2. 要求從表不能是臨時表，主從表的欄位具備相同的資料型別、字元長度和約束。

#建立主表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);

#刪數資料記錄時，要先從表再主表，也就是說刪除主鍵表時必須先刪除其他與之關聯的表。
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 ;