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 ;