mysql常用操作（一）

【數據庫設計的三大范式】
1、第一范式（1NF）：數據表中的每一列，必須是不可拆分的最小單元。也就是確保每一列的原子性。
例如：userInfo:'山東省煙臺市 18865518189' 應拆分成 userAds='山東省煙臺市' userTel='18865518189'
2、第二范式（2NF）：滿足1NF后，要求：表中的所有列都必須依賴于主鍵，而不能有任何一列與主鍵沒有關系,也就是說，一個表只描述一件事情；
例如：訂單表，只能描述訂單相關的信息，所以所有的字段都必須與訂單ID相關
產品表，只能描述產品相關的信息，所以所有的字段都必須與產品ID相關
因此，不能在一張表中同時出現訂單信息與產品信息
3、第三范式（1NF）：滿足2NF后，要求：表中每一列都要與主鍵直接相關，而不是間接相關，（表中的每一列，
只能依賴于主鍵）
例如:訂單表中，需要有客戶相關信息，在分離出客戶表之后，訂單表中，只需要有一個客戶id即可。
而不能有其他的客戶信息。因為其他的用戶信息是直接關聯于用戶ID，而不是關聯于訂單ID

【第二范式與第三范式的本質區別】
在于有沒有分出兩張表，第二范式是說一張表中包含了多種不同實體的屬性，必須要分成多張表，
第三范式是要求已經分好了多張表的話，那么一張表中只能有另一張表的ID，而不能有其他的任何信息，（其他的任何信息，一律用主鍵在另一張表查詢）

創建表

CREATE TABLE IF NOT EXISTS tb1( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(255) NOT NULL, age SMALLINT UNIQUE,height DOUBLE(3,2) DEFAULT 1.2
-- PRIMARY KEY(id)
);

?

定義列：列名 數據類型 列定義關鍵字
name是系統關鍵字，所以使用反引號包裹
IF NOT EXISTS可以省略，省略后若表已存在，重復創建時會報錯
常見的列定義關鍵字：
UNSIGNED: 設置列 為 無符號列，只能設置類型為數字類型的列
PRIMARY KEY:設置主鍵約束
AUTO_INCREMENT:設置為自動增長列，自動增長列必須是主鍵

【主鍵】
1、主鍵的注意事項：主鍵默認非空！只有逐漸才能設置自動增長（逐漸不一定自增，自增一定是主鍵）
2、設置方式：① 在列定義是設置：id INT UNSIGNED PRIMARY KEY
② 在列定義完成后設置：PRIMARY KEY（id）
PRIMARY KEY:設置主鍵約束
NOT NULL：設置列為非空約束
UNIQUE:設置唯一性約束。該字段不能出現重復值
DEFAULT:設置默認值約束，height DOUBLE(3,2) DEFAULT 1.2 height如果不輸入，默認1.2
FOREIGN ?KEY:設置外鍵約束。

【外鍵】
1、設置外鍵有哪些注意事項？
① 只有INNODB的數據庫引擎支持外鍵，修改my.ini文件
② 外鍵與參照列的數據類型必須相同（數值型要求長度和無符號都相同，字符串要求類型相同，長度可不同）
③ 設置外鍵的字段必須要有索引，如果沒有索引，設置外鍵時會自動生成索引，刪除該外鍵時，需刪除索引
2、設置外鍵的語法
[CONSTRAINT 外鍵名] FOREIGN KEY(外鍵字段) REFERENCES 參照表(參照字段)[ON DELETE SET NULL ON UPDATE CASCADE]-- 設置參照完整性
3、外鍵約束的參照操作
參照操作：當對參照表的參照字段進行刪除或更新時，外檢表中的外鍵如何應對。
參照操作可選值：RESTRICT 拒絕參照表刪除或更新參照字段
NO ACTION 與 RESTRICT相同，但這個指令只在MySQL有效
CASCADE 刪除或更新參照表的參照字段時，外鍵表的記錄同步刪除更新
SET NULL 刪除或更新參照表的參照字段時，外鍵表的外鍵設為NULL

-- 修改表名 ALTER TABLE 舊表名 RENAME [TO] 新表名;
ALTER TABLE tb2 RENAME TO tb3;-- 同時修改多表 RENAME TABLE tb3 TO tb1[,`USER` to user1];
RENAME TABLE tb3 TO tb1,`USER` to user1;-- 修改字段 列
-- ALTER TABLE 表名 CHANGE 舊列名 新列名 列定義 [FIRST|AFTER 某一列];
-- FIRET:將這個字段調整為表格第一列  AFTER 某一列：將這個字段放到某一列后面
ALTER TABLE tb1 CHANGE `name` `username` VARCHAR(200) NOT NULL AFTER age;-- 只修改列定義，不能改名
ALTER TABLE tb1 MODIFY `username` VARCHAR(200) NOT NULL AFTER age;-- 刪除表中某一列
ALTER TABLE tb1 DROP height;-- 新增一列，default可選，往后可選
ALTER TABLE tb1 ADD height DOUBLE(8,2) DEFAULT 1.2 AFTER age;-- 新增多列,不能調整列的位置，只能插在最后
ALTER TABLE tb1 ADD(weight DOUBLE(3,2) UNSIGNED,school VARCHAR(255)
);-- 增加主鍵約束
ALTER TABLE tb1 ADD PRIMARY KEY(id);-- 刪除主鍵約束
ALTER TABLE tb1 DROP PRIMARY KEY;-- 添加唯一性約束
ALTER TABLE tb1 ADD UNIQUE KEY(username);-- 刪除唯一性約束:由于創建唯一性約束會默認創建索引，所以刪除時，需刪除索引
ALTER TABLE tb1 DROP INDEX username;-- 設置默認值約束
ALTER TABLE tb1 ALTER age SET DEFAULT 20;-- 刪除默認值約束
ALTER TABLE tb1 ALTER age DROP DEFAULT;-- 設置外鍵約束:ALTER TABLE tb1 ADD FOREIGN KEY(clsid) REFERENCES classes(id) [ON DELETE SET NULL ON UPDATE CASCADE];ALTER TABLE tb1 ADD FOREIGN KEY(clsid) REFERENCES classes(id) ON DELETE SET NULL ON UPDATE CASCADE;-- 刪除外鍵約束,由于創建外鍵時會默認創建索引，所以刪除外鍵后，要刪除索引
ALTER TABLE tb1 DROP FOREIGN KEY user_fk_classes;
ALTER TABLE tb1 DROP INDEX user_fk_classes;

?

【SQL語句的組成】
DML 數據操作語言（插入、刪除和修改數據庫中的數據） INSERT UPDATE DELETE
DQL 數據查詢語言（用來查詢數據庫中的數據） SELECT
DCL 數據控制語言（用來控制存取許可、存取權限等） GRANT REVOKE
DDL 數據定義語言（用來建立數據庫、數據庫對象和定義表的列） CREATE DROP

-- 數據插入語句 INSERT
-- INSERT [INTO] 表名 [列名] VALUES (列值)[,(列值),……]
-- 注意：如果省略列名，則后面的列值必須為所有的列賦值，包括自增列和默認列；如果不省略列名，則后面的列值需要與前面的列名一一對應；并且需要給所有非NULL列賦值
INSERT INTO tb1 (username,age,sex) VALUES ("張三",12,"女");
INSERT tb1 VALUES (7,"張三",12,"女");
INSERT INTO tb1 (username,age,sex) VALUES ("張三",12,"女"),("張三",12,"女");
INSERT INTO tb1 (username,age,sex) VALUES ("張張",12,"女");

?

-- 數據更新語句
-- UPDATE 表名 SET 列名=列值[,列名=列值,……] [WHILE 條件]
-- WHERE條件可以省略，但是表示修改表中所有的行
UPDATE tb1 SET sex="男"
UPDATE tb1 SET username="李二狗",age=18,sex="女" WHERE id=8

【常見where條件判斷】
1、關系運算符：> < <> = != >= <=
2、邏輯運算：NOT AND OR
3、ISNULL(字段) 檢測是否為空
SELECT * FROM tb1 WHERE ISNULL(age); --所有年齡為空的用戶
4、BETWEEN …… AND ……介于兩個值之間
SELECT * FROM tb1 WHERE age BETWEEN 10 AND 13;
5、IN 在某些值之間的數據
SELECT * FROM tb1 WHERE id in(1,3,5,7,9);
6、LIKE ：相似匹配
① 相似匹配時，使用%表示任意字符個數（0到多個）
SELECT * FROM tb1 WHERE username LIKE "張%"; 用張開頭
SELECT * FROM tb1 WHERE username LIKE "%張%"; （任意位置包含張）
SELECT * FROM tb1 WHERE username LIKE "%張"; 用張結尾
② 相似匹配時，使用_表示一個字符的個數
SELECT * FROM tb1 WHERE username LIKE "_張%"; 第二個字是張
7、EXISTS(子查詢語句)：如果子查詢語句返回數據>=1行，EXISTS返回true,否則返回false
SELECT * FROM tb1 WHERE EXISTS(SELECT * FROM tb2);--如果tb2返回至少1行數據，則條件成立，返回tb1
8、ALL(子查詢)：子查詢返回的數據，需要全部滿足，才能成立
SELECT * FROM tb1 WHERE username !=ALL(SELECT username FROM tb1 WHERE id>7);--用戶名 不能等于 子查詢返回用戶名列表中的任意一個
9、ALL(子查詢)：子查詢返回的數據，滿足其中一個，即可成立
SELECT * FROM tb1 WHERE username =ANY(SELECT username FROM tb1 WHERE id>7);--用戶名 等于 子查詢返回用戶名列表中的任意一個

刪除表中數據
DELETE [FROM] tb1 WHERE username LIKE "_張%";

數據查詢語句
SELECT 列名,[列名,……] FROM 表名 ORDER BY id 排序的列名 ASC/DESC;
SELECT * 表示查詢所有字段
ORDER BY表示對查出的數據進行排序，必須在where后面

-- 列表達式
-- 根據已有的列，查詢出來的結果，我們使用case結構虛擬出來的列，稱為列表達式-- AS關鍵字
-- 用于給列名起別名，AS關鍵字還可以省略
SELECT username AS '名字',sex AS '性別',age,( -- 給username列起別名“名字” ， sex列起別名“性別”CASE -- case表示判斷的開始WHEN sex="男" THEN 1 -- 如果查詢出sex是男，則虛擬的列表達式的值就是1WHEN sex="女" THEN 2ELSE 3 -- 上面所有都不成立時，列表達式值為3END -- END表示判斷的結束
) AS sexno FROM tb1; -- 給虛擬出的這一列，起別名叫sexno-- DISTINCT 對查詢后的結果去重（消除重復列）

?