1、SQL 欄位數據類型

2、查看資料庫信息語句

3、SQL 語句

創建、刪除資料庫數據表
向表中添加、刪除記錄、查詢記錄
修改表名，添加、修改、刪除欄位，添加唯一約束
查看、刪除、添加表中的索引
擴展功能，欄位值自增等

4、數據表高級操作

克隆表，將數據表的數據記錄生成到新的表中
刪除記錄後主鍵記錄重頭開始自增
創建臨時表

5、創建外鍵約束，保證數據的完整性和一致性

6、MySQL 六種約束

資料庫中有數據表，數據表中有一條一條的記錄。

可以用Navicat 等遠程連接工具鏈接資料庫，不過資料庫需要開啟授權。

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語句用於維護管理資料庫，包括數據查詢、訪問控制、數據更新、對象管理、備份等功能。

創建、刪除資料庫數據表

創建新的資料庫

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

表中添加欄位

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 ;