學習MySQL的第十天

一、MySQL的數據類型

1.MySQL的數據類型

?2.常見的數據類型的屬性

二、整數類型

三、浮點類型

????????REAL默認就是DOUBLE。如果你把SQL模式設定為啟用“REAL_AS_FLOAT”,那么,MySQL就認為REAL是FLOAT。如果要啟用“REAL_AS_FLOAT”,可以通過以下SQL語句實現:
????????SET sql_mode = "REAL_AS_FLOAT";

四、約束

4.1?為什么需要約束

????????數據完整性(Data Integrity)是指數據的精確性(Accuracy)和可靠性(Reliability)。它是防止數據庫中存在不符合語義規定的數據和防止因錯誤信息的輸入輸出造成無效操作或錯誤信息而提出的，而約束就是為了保證數據的完整性
????????約束就是對表中字段的限制

實體完整性(Entity Integrity):例如,同一個表中,不能存在兩條完全相同無法區分的記錄

域完整性(Domain Integrity):例如:年齡范圍0-120,性別范圍“男/女”

引用完整性(Referential Integrity):例如:員工所在部門,在部門表中要能找到這個部門

用戶自定義完整性(User-defined Integrity):例如:用戶名唯一、密碼不能為空等,本部門經理的工資不得高于本部門職工的平均工資的5倍。

????????約束的分類：

1.從約束字段的字段的個數分為 ?單列約束和多列約束
2.約束的作用范圍分為 ?列級約束和表級約束
列級約束：將此約束聲明在對應字段的后面
表級約束：在表中所有字段

????????約束的作用：

NOT NULL 非空約束
UNIQUE 唯一性約束
PRIMARY KEY 主鍵約束
FOREIGN KEY 外鍵約束
CHECK 檢查約束
DEFAULT 默認值約束

????????添加約束的場景

?CREATE TABLE 時添加約束
?ALTER TABLE 時添加約束

????????如何查看表中的約束

SELECT * 
FROM information_schema.table_constraints # 數據庫內置的表
WHERE table_name = 'emp1';  # 之前的屬于固定查詢，這一句是進行匹配

4.2?非空約束

? ? ? ? 在創建表時添加約束

CREATE TABLE test1(
id INT NOT NULL,
name VARCHAR(255) NULL,
salary DECIMAL(10,2)
)

????????在 ALTER TABLE時添加約束

ALTER TABLE test1
MODIFY name VARCHAR(255) NOT NULL;

? ? ? ? 刪除非空約束

? ? ? ? 就相當于修改約束使其沒有限制

ALTER TABLE test1
MODIFY name VARCHAR(255) NOT NULL;

4.3?唯一約束性

????????UNIQUE 約束列中的內容不能重復

????????在創建時添加約束

CREATE TABLE test2(
id INT UNIQUE,  # 列級約束
name VARCHAR(255),
garden TINYINT,
# 表級約束
CONSTRAINT uk_test2_name UNIQUE(name)  # 或unique(name)
)

# 查看約束
SELECT * 
FROM information_schema.table_constraints # 數據庫內置的表
WHERE table_name = 'test2';

? ? ? ? 補充：
????????（1）表級約束：NOT NULL 沒有表級約束；CONSTRAINT 意思是約束
? ? ? ? （2）在創建唯一約束的時候,如果不給唯一約束命名,就默認和列名相同
????????（3）可以向聲明unique的字段上添加NULL值，而且可以多次添加

????????在 ALTER TABLE時添加約束

ALTER TABLE test2
ADD CONSTRAINT new UNIQUE(name) # 對約束條件進行約束，并對約束進行命名
# ADD UNIQUE(name) 直接添加
# 或
/*
ALTER TABLE test2
MODIFY name VARCHAR(255) UNIQUE
*/SELECT * 
FROM information_schema.table_constraints # 數據庫內置的表
WHERE table_name = 'test2';

????????復合的唯一性約束

? ? ? ? 對于復合的唯一性約束，只要這兩個不完全相同即可

CREATE TABLE USER(
id INT,
name VARCHAR(255),
password VARCHAR(255),
# 表級約束
UNIQUE(name,password)
)# 例:
INSERT INTO USER
VALUES(1,'a','abc'),
(2,'b','abc')

????????刪除唯一性

? ? ? ? (1) 添加唯一性約束的列上也會自動創建唯一索引
? ? ? ? (2) 刪除唯一約束只能通過刪除唯一索引的方式刪除
? ? ? ? (3) 刪除時需要指定唯一索引名,唯一索引名就和唯一約束名一樣。
? ? ? ? (4) 如果創建唯一約束時未指定名稱,如果是單列,就默認和列名相同;如果是組合列,那么默認和()中排在第一個的列名相同，也可以自定義唯一約束名

4.4?主鍵約束

????????PRIMARY KEY? 這就好使主鍵不能為空，必須有值

????????在 CREATE TABLE 時添加約束

CREATE TABLE test3(
id INT PRIMARY KEY,
name VARCHAR(255),
salary DECIMAL(10,2),
email VARCHAR(255)
);
# 或
CREATE TABLE test5(
id INT,
name VARCHAR(255),
PRIMARY KEY(id)
);SELECT * 
FROM information_schema.table_constraints # 數據庫內置的表
WHERE table_name = 'test5';

????????在 ALTER TABLE 時添加約束

? ? ? ? 此時只能是在這個表沒有主鍵約束時才可以

ALTER TABLE test5
ADD PRIMARY KEY (id)

????????刪除主鍵約束

????????因為一個表只有一個主鍵，所以刪除時，不需要指定名稱，所以刪除很好進行

ALTER TABLE test5
DROP PRIMARY KEY

五、自增變量的持久化

? ? ? ? 自增列：

? ? ? ? 首先我們要引入自增列的概念，就比如學號列，如果你設置為了自增列，那么每增加一個學生，它就會自動向后順延一個學號

CREATE TABLE test7(
id INT auto_increment PRIMARY KEY,  # 創建一個自增列
name VARCHAR(255) UNIQUE 
)
SELECT *FROM test7;
INSERT INTO test7(name)
VALUES('Tom'),('Mike')
SELECT *FROM test7;
INSERT INTO test7
VALUES(NULL,'people')
SELECT *FROM test7;

? ? ? ? 補充：

? ? ? ? （1）當我們沒有指明字段就進行添加時，含有auto_increment的列會自己進行自增；
? ? ? ? （2）當我們向含有auto_increment的字段添加0或null時，也會自動進行自增

? ? ? ? 隨后我們通過一個主表和從表來展現一下基本操作

外鍵約束
在create table 時添加
# 主表和從表：父表和子表
先創建主表
CREATE TABLE deep(
id INT,
name VARCHAR(155)
);
創建從表
CREATE TABLE dee(
dee_id INT PRIMARY KEY auto_increment,
dee_name VARCHAR(155),
department_id INT,
# 表級約束
FOREIGN KEY(dee_id) REFERENCES deep(id)
);
# 需要注意的是主表中的id也需要有主鍵約束或唯一性約束，才能與從表建立聯系
ALTER TABLE deep
ADD PRIMARY KEY (id);

? ? ? ? 我們再來了解一下約束的等級

????????cascade方式：在父表上更新刪除記錄，同步更新刪除子表的匹配記錄
????????Set null模式：父表上已經更新或刪除記錄時，將子表上匹配記錄的列的位置設為null
????????no action方式：如果子表中有匹配記錄，則不允許對主表進行更新刪除操作
????????restrict方式：如果子表中有匹配的記錄，則不允許對父表對應候選鍵進行更新刪除操作
????????restrict方式：同no action
????????set default方式：父表有變更時，子表相應的設置成一個默認的值，但是lnnodb不能識別

????????隨后我們以ON UPDATE CASCADE ON DELETE SET NULL來進行一次演示：

先創建主表
CREATE TABLE dept(
id INT PRIMARY KEY,
name VARCHAR(155)
);
創建從表
CREATE TABLE de(
de_id INT PRIMARY KEY auto_increment,
de_name VARCHAR(155),
department_id INT,
# 表級約束
FOREIGN KEY(department_id) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE SET NULL
# 把修改操作設置為級聯修改等級，把刪除操作設置為set null等級
# 注意：不能將不能為空的列作為修改對象
);INSERT INTO dept
VALUES(1001,'教學部'),
(1002,'學習部'),
(1003,'公關部')INSERT INTO de
VALUES(1,'張三',1001),
(2,'李四',1001),
(3,'王五',1003),
(4,'爾瑪',1002)

????????我們先建立起主表與從表的聯系，然后對主表進行修改，觀察從表的變化

UPDATE dept 
SET id = 1004
WHERE id = 1002
SELECT * 
FROM de,dept
where de.department_id = dept.id

? ? ? ? 當我們修改主表時，從表中與之關聯的列也同步發生變化

DELETE FROM dept
WHERE id = 1002

????????此時爾瑪的department_id 為null

? ? ? ? 最后我們還需要注意刪除外鍵約束，因為在當初建立約束時，就自動產生了約束索引

? ? ? ? 不過我們還需要注意：由于在當初建表的時候，沒有為這個外鍵約束起一個名稱，所以 MySQL 為此自動生成了一個名稱，這個自動生成的名稱不同于受到這個外鍵約束的列名。而刪除外鍵的時候，需要提供的是這個外鍵約束名，而不是列名，所以需要先查找出外鍵的名稱，然后才能將其刪除

? ? ? ? 為了查看外鍵的名稱，我們需要用到??show create TABLE de，并將其復制出來

CREATE TABLE `de` (`de_id` int NOT NULL AUTO_INCREMENT,`de_name` varchar(155) DEFAULT NULL,`department_id` int DEFAULT NULL,PRIMARY KEY (`de_id`),KEY `department_id` (`department_id`),CONSTRAINT `de_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `dept` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

? ? ? ? ?我們可以看到外鍵的名稱：CONSTRAINT `de_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `dept` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
? ? ? ? 被命名為了?de_ibfk_1 ，因此我們刪除時也要通過這個進行刪除

# 刪除外鍵約束
ALTER TABLE de
DROP FOREIGN KEY de_ibfk_1
# 檢查刪除的外鍵約束是否有對應的普通索引
SHOW INDEX FROM de # 通過這一步找到Column_name 中，對應的我們所刪除的字段名稱
# 刪除索引
ALTER TABLE de
DROP INDEX department_id # key name即外鍵的約束名

? ? ? ? 從而實現對外鍵及其索引的完整性刪除