Mysql資料庫
一、資料庫
mysql服務啟動,在cmd輸入net start mysql#創建資料庫
CREATE DATABASE hsp_db01;
#創建一個使用 utf8 字符集的 hsp_db02 資料庫
CREATE DATABASE hsp_db02 CHARACTER SET utf8
#創建一個使用 utf8 字符集，并帶校對規則的 hsp_db03 資料庫
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
#校對規則 utf8_bin 區分大小 默認 utf8_general_ci 不區分大小寫
?
#查看當前資料庫服務器中的所有資料庫
SHOW DATABASES
#查看創建的 hsp_db01 資料庫的定義資訊
SHOW CREATE DATABASE `hsp_db01` #為了規避關鍵字，可以使用反引號
?
#洗掉前面創建的 hsp_db01 資料庫
DROP DATABASE hsp_db01資料庫的備份、恢復（匯入）（必須在dos下運行）
備份恢復資料庫
mysqldump -u 用戶名 -p 密碼 -B 資料庫名 > 路徑（例：d:\\database.sql）
備份恢復資料庫的表
備份資料庫的多個表不能帶-B，否則會被認為是多個資料庫
mysqldump -u 用戶名 -p 密碼 資料庫名 表1 表2 > 路徑（例：d:\\database.sql）
使用mysqldump命令來恢復一個叫data01資料庫的一個叫table01的表(已有備份檔案)，具體步驟如下：
-
打開cmd視窗，輸入mysql -u 用戶名 -p，然后輸入mysql密碼，進入mysql資料庫，
-
輸入use data01，選擇您要恢復的資料庫，
-
輸入source d:/table01.sql，執行您的備份檔案，恢復您的表，
恢復資料庫（必須在mysql命令列下運行）
先進入mysql命令列：mysql -u 用戶名 -p 密碼
恢復：source 檔案路徑.sql（例：d:\\database.sql）
左上角寫了mysql -u 用戶名 -p 的界面才叫mysql命令列
備份，恢復資料庫實踐
先用mysqldump -u 用戶名 -p 密碼 -B 資料庫名 > 路徑（例：d:\\database.sql）
從資料庫里備份一個到E盤里，然后在資料庫中用drop指令將其洗掉
然后通過mysql -u 用戶名 -p 密碼進入mysql命令列，輸入source d:\\database.sql將其備份
二、Mysql基本資料型別
基本資料型別的使用細節
數值型：
decimal（M,D)中的M默認為10（最大65），D默認為0（最大30），M是位數，D是小數點后有幾位
如果沒有指定 unsinged , 就是有符號
#創建一個無符號的tinyint型別資料，0~255
CREATE TABLE t4 (id TINYINT UNSIGNED)字串型：
VARCHAR雖然可以有65535個位元組，但是首先去掉3個位元組用于記錄欄位大小，所以最大為65532個位元組，
如果用的編碼是UTF-8的話，一個字符消耗3個位元組，最多存放65532 / 3 = 21844個字符，
GBK是2個位元組，最多存放65532 / 2 = 32766個字符，
#我們來創建一個資料型別為varchar，編碼為GBK的表
CREATE TABLE t10 (`name` VARCHAR(32766)) CHARSET gbk;
日期型別：
DATE / DATETIME / TIMESTAMP
CREATE TABLE t14 (
birthday DATE , -- 年月日
job_time DATETIME, -- 年月日 時分秒
login_time TIMESTAMP -- 如果希望 login_time 列自動更新, 需要配置這兩句
NOT NULL DEFAULT CURRENT_TIMESTAMP -- 不允許為空，默認為當前時間
ON UPDATE CURRENT_TIMESTAMP -- 更新當前時間戳
);三、表
創建表
CREATE TABLE `emp` (
id INT,
`name` VARCHAR(32),
sex CHAR(1),
brithday DATE,
entry_date DATETIME,
job VARCHAR(32),
salary DOUBLE,
`resume` TEXT
)
CHARSET utf8 COLLATE utf8_bin ENGINE INNODB
#charset是character set的簡寫，即字符集
#字符集，校驗規則，存盤引擎不寫的話就是默認的（默認的話跟隨當前資料庫）要用cmd在一個叫database的資料庫下新建一個表，你需要先用mysql命令登錄到資料庫，然后用use命令選擇database資料庫，再用create table命令創建表，具體的步驟如下：
-
打開cmd，輸入mysql -u root -p，回車后輸入密碼，登錄到資料庫，
-
輸入use database;，選擇database資料庫，
-
輸入如上代碼完成創建，
洗掉表
DROP TABLE `emp`修改表
舉例演示:
ALTER TABLE emp
-- 員工表 emp 的上增加一個 image 列，varchar 型別(要求在 birthday 后面)，
ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER birthday ;
-- DEFAULT ''默認為''這個字符
?
-- 修改 job 列，使其長度為 60，
ALTER TABLE emp
MODIFY job VARCHAR(60) NOT NULL DEFAULT '' ;
?
-- 洗掉 sex 列，
ALTER TABLE emp
DROP sex;
?
-- 表名改為 employee，
RENAME TABLE emp TO employee;
?
-- 修改表的字符集為 utf8
ALTER TABLE emp CHARACTER SET utf8;
?
-- 列名 name 修改為 user_name
ALTER TABLE emp;
?
-- 顯示表結構，可以查看表的所有列
DESC emp ;四、CRUD增刪改查
1. Insert
用法:
INSERT INTO 表名 (列名1,列名2...)
values (對應列名的值);INSERT INTO `emp` (id, birthday)
VALUES(1, '2003-05-15'); -- 注意date等日期型別一定要按照規則寫否則報錯或者錯位寫入insert使用細節
-
插入的資料應與欄位的資料型別相同
-
資料的長度應在列的規定范圍內
-
在 values 中列出的資料位置必須與被加入的列的排列位置相對應
-
字符和日期型資料應包含在單引號中
-
列可以插入空值[前提是該欄位允許為空]
-
可以用 values ( ),( ) ,( ) ; 的形式添加多條記錄
INSERT INTO `goods` (id, goods_name, price) VALUES(50, '三星手機', 2300),(60, '海爾手機', 1800); -
如果是給表中的所有欄位添加資料，可以不寫前面的欄位名稱
-
默認值的使用，當不給某個欄位值時，如果有默認值就會添加默認值，否則報錯
-- 如果某個列 沒有指定 not null ,那么當添加資料時，沒有給定值，則會默認給 null
-- 如果我們希望指定某個列的默認值，可以在創建表時通過 not null default xxx 指定
#表里有3個資料,id,goodname,price有默認值10
INSERT INTO `goods` (id, goods_name)
VALUES(80, '格力手機');
#最后會插進去一個(80,格力手機,10)進去
2. Update
用法:
UPDATE 表名 SET 列名 = 值 WHERE 布爾運算式，篩選滿足條件的行#將所有員工薪水修改為 5000 元，如果不帶 where 條件，會修改該列所有的記錄，因此要小心
UPDATE employee SET salary = 5000;
將姓名為 小妖怪 的員工薪水修改為 3000 元
UPDATE employee
SET salary = 3000
WHERE user_name = '小妖怪';
也可以修改多個列的值
UPDATE employee
SET salary = salary + 1000 , job = '出主意的'
WHERE user_name;
3. Delete
用法:
DELETE FROM 表名
where 布爾等值式; -- 如果不帶 where 條件，會修改所有記錄,小心刪庫跑路#洗掉表中名稱為’老妖怪’的記錄，
DELETE FROM employee
WHERE user_name = '老妖怪';delete使用細節
-
delete不能洗掉某一列的值，只能使用update將列設為空
-
如果不帶 where 條件，會修改所有記錄,小心刪庫跑路！！！
4. 單表Select
用法1：基礎查詢
SELECT [DISTINCT] * 或 指定列 FROM 表名;
-- distinct功能是去重(寫在from前的每一個列的值都相同才去)
-- *代表所有列,也可以寫多個列(用逗號隔開)
比如:
-- 查詢表中所有學生的資訊
SELECT * FROM student;
-- 查詢表中所有學生的姓名和對應的英語成績
SELECT name,english FROM student;
-- 過濾表中重復資料
SELECT DISTINCT name,english FROM student;
用法2：列名使用運算式進行運算
比如:
-- 統計每個學生的總分
SELECT `name`, (chinese + english + math ) FROM student;
#但是使用這種方法系統會自動命名第二列為chinese + english + math,所以我們可以采用下面的方式美化一下
-- 使用AS給列命名
SELECT name AS '名字', (chinese + english + math ) AS total
FROM student;
常用運算子表
用法3: 使用where子句運算子,進行過濾查詢
SELECT [DISTINCT] * 或 指定列 FROM 表名 WHERE 布爾運算式; -- select定最后顯示多少列,from定從哪個表查詢,where定最后顯示滿足條件的行
比如:
-- 查詢姓名為趙云的學生成績
SELECT * FROM student
WHERE name = '趙云';
-- 查詢 math 大于 60 并且(and) id 大于 4 的學生成績
SELECT * FROM student
WHERE math >60 AND id > 4;
-- 查詢英語成績大于語文成績的同學
SELECT * FROM student
WHERE english > chinese;
-- 查詢總分大于 200 分 并且 數學成績小于語文成績 并且 姓趙的學生, 趙% 表示名字以趙開頭
SELECT * FROM student
WHERE (chinese + english + math) > 200 AND
math < chinese AND name LIKE '趙%';
-- 查詢英語分數在 80－90 之間的同學，
SELECT * FROM student
WHERE english >= 80 AND english <= 90;
SELECT * FROM student
WHERE english BETWEEN 80 AND 90; -- between .. and .. 是 閉區間
-- 查詢數學分數為 89,90,91 的同學，
SELECT * FROM student
WHERE math = 89 OR math = 90 OR math = 91;
SELECT * FROM student
WHERE math IN (89,90,91);
用法4: 使用 order by子句排序查詢結果
SELECT * FROM 表名 ORDER BY 列名(排序的依據) [DESC]; -- 默認為升序,DESC改為降序
比如:
-- 對數學成績排序后輸出【升序】，
SELECT *
FROM student
ORDER BY math;
-- 對總分按從高到低的順序輸出 [降序] -- 使用別名排序
SELECT name , (chinese + english + math) AS '總分'
FROM student
ORDER BY total_score DESC;
-- 對姓韓的學生成績[總分]排序輸出(升序) where + order by
SELECT name, (chinese + english + math) AS '總分' FROM student
WHERE name LIKE '韓%'
ORDER BY total_score;
用法5：使用like模糊查詢
比如：
-- % 表示 0 到多個的任意字符
-- _ 表示單個任意字符 其中GBK中需要兩個_ _來表達一個字符
-- 顯示首字符為 S 的員工姓名和工資
SELECT name, salary FROM emp
WHERE name LIKE 'S%'
-- 顯示第三個字符為大寫 O 的所有員工的姓名和工資
SELECT name, salary FROM emp
WHERE name LIKE '__O%'
用法6:使用DESC查詢表結構
DESC 表名用法7:使用LIMIT分頁查詢
select * from table limit 引數1,引數2; -- 引數1 : 代表偏移量offset（從第offset + 1資料開始查），注意是從0開始，所以應該+1代表第一個資料
-- 引數2 : 取出的資料條數rows
/* 查詢第1-10條資料 */
SELECT * FROM Student LIMIT 0,10;
/* 查詢第11-20條資料 */
SELECT * FROM Student LIMIT 10 , 10; -- 講到這我們會想知道如何快速算出 引數1，接下來我們看一下計算公式公式
-
offset：(要查詢的頁數 - 1) * 每頁顯示的資料條數
-
rows：顯示的資料條數
在進行分頁之前，要注意一下是否超出了最大頁數，所以我們需要先根據資料總量來得出總頁數，這需要用到統計函式COUNT和向上取整函式CEIL，SQL操作如下：
#獲得資料總條數
SELECT COUNT(*) FROM Student;
#假設每頁顯示10條，則直接進行除法運算，然后向上取整，這樣我們就獲得了總頁數，在實際操作中，要注意一下是否超出了最大頁數
SELECT CEIL(COUNT(*) / 10) AS '總頁數' FROM Student;5. 多表查詢Select
多表查詢的條件不能少于 要查詢的表個數 - 1, 否則會出現笛卡爾集
自連接
自連接的特點
1. 把同一張表當做兩張表使用
2. 需要給表取別名 如果from同一張表兩次，會報錯，所以需要對表重命名，無需使用 AS，直接表加別名就好
3. 列名有時不明確，可以通過 AS 指定列的別名
舉例：
顯示公司員工名字和他上級的名字，員工名字和上級的名字都在emp表
其中員工和上級是通過 emp 表的 manager列 關聯
SELECT worker.name AS '職員名' ,
boss.name AS '上級名'
FROM emp worker, emp boss
WHERE worker.manager = boss.empno
子查詢（嵌套查詢）
-
單行子查詢：只回傳一行資料
-
多行子查詢：回傳多行資料 ，因為子查詢的結果是不確定的，所以使用比較運算子 IN （用于確定IN前面的值是否與IN的括號中的值或子查詢中的任何值匹配）
#多行子查詢中使用in運算子
#查詢和10號部門的作業相同的雇員的 名字、崗位、工資、部門號, 但是不含10號部門的員工
select ename, job, sal, deptno
from emp
where job in (
SELECT DISTINCT job
FROM emp
WHERE deptno = 10
) and deptno != 10
多行子查詢中使用 all 運算子
查詢工資比部門 30 的所有員工的工資高的員工的姓名、工資和部門號
SELECT ename, sal, deptno
FROM emp
WHERE sal > ALL(
SELECT sal
FROM emp
WHERE deptno = 30
)
多行子查詢中使用 any 運算子
查詢工資比部門 30 的任意/其中一個員工的工資高的員工的姓名、工資和部門號
SELECT ename, sal, deptno
FROM emp
WHERE sal > any(
SELECT sal
FROM emp
WHERE deptno = 30
)
-
把子查詢當做臨時表使用 : 避免mysql重復計算表，提高查詢性能，并且可以解決很多復雜的查詢
#查找每個部門工資 高于 他們自己部門平均工資的人的資料 -- 1. 先得到每個部門的 部門號和 對應的平均工資 SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno -- 當聚合列和非聚合列出現在一起時必須使用group by陳述句-- 2. 把上面的結果當做子查詢, 和 emp 進行多表查詢
SELECT ename, sal, temp.avg_sal, emp.deptno
FROM emp,（
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
) temp -- 把子查詢當作一個臨時表使用，重命名為temp
WHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal
合并查詢
-
union all 單純取并集不去重
-
union 去重
舉例： -- union all 就是將兩個查詢結果合并，不會去重 SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5條 UNION ALL SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3條 -- 最后有8條
6.連接
自然連接
通常用于將兩個關系表中具有相同屬性的行組合在一起，以便我們可以從兩個表中同時獲取資訊，
自然連接是一種特殊的等值連接（區別在于等值連接需要指定值），他要求兩個表中進行連接的必須是相同的屬性列（名字相同），無須添加連接條件，并且在結果中消除重復的屬性列，
natural表1：
natural表2：
select 列名 from 左表 natural join 右表
-- 將2個表自然連接一下
SELECT * FROM `natural` NATURAL JOIN `natural2` 說人話，直接先把兩張表的列名去重后列出來
然后開始對比他們兩相同的列（id）的值，我們就找到id = 2時相同，直接把左表和右表連接起來，結果：
內連接
內連接基本與自然連接相同，不同之處在于自然連接的是同名屬性列的連接，而內連接則不要求兩屬性列同名，可以用using或on來指定某兩列欄位相同的連接條件，
select 列名 from 左表 inner join 右表 on 條件 -- inner可以省略外連接
通常用于查詢在一張表中存在但在另一張表中不存在的記錄
-
左外：只保留左表的所有元素，右表沒有則置null
-
右外：同理
-
全外連接 ：mysql不支持
用法： select 列名 from 左表 left join 右表 on 條件 select 列名 from 左表 right join 右表 on 條件下面是一個簡單的例子，演示如何使用外連接查詢兩張表中的資料，
假設我們有兩張表：員工表和部門表，員工表包含員工的姓名和所屬部門，部門表包含部門名稱和部門編號，
員工表：
姓名 部門編號
張三 1
李四 2
王五 3
趙六 NULL部門表：
部門編號 部門名稱
1 銷售部
2 人事部
如果我們想要查詢所有員工及其所屬部門，可以使用以下SQL陳述句：SELECT 員工姓名, 部門名稱 FROM 員工表 LEFT JOIN 部門表 ON 員工的部門編號 = 部門的編號;
執行上述陳述句后，將得到以下結果：
姓名 部門名稱
張三 銷售部
李四 人事部
王五 NULL
趙六 NULL
可以看到，上述查詢結果中包含了員工表中所有記錄，并且顯示了每個員工所屬的部門，對于那些在部門表中沒有匹配記錄的員工（即王五和趙六），其所屬部門顯示為NULL，
五、函式
1.聚簇函式（合計、統計）
count：
SELECT COUNT(*) 或 (列)FROM 表名 WHERE 運算式;
-- count(*) 和 count(列) 的區別
-- count(*) 回傳滿足條件的行數
-- count(列): 統計滿足條件的某列個數，會排除為 null 的sum:
SELECT SUM(列) FROM 表名 WHERE 運算式;
比如:
-- 統計一個班級語文、英語、數學各科的總成績并使用別名
SELECT SUM(math) AS math_total_score,
SUM(english) AS english_total_score,
SUM(chinese) AS chinese_total_score
FROM student;
-- 統計一個班級語文成績平均分
SELECT SUM(chinese)/ COUNT(*) FROM student;
-- 注意有些資料型別不能統計比如字串之類
SELECT SUM(name) FROM student; #不報錯但無意義
avg:
SELECT AVG(列) FROM 表名 WHERE 運算式;
比如:
-- 求一個班級數學平均分？
SELECT AVG(math) FROM student;
-- 求一個班級總分平均分
SELECT AVG(math + english + chinese) FROM student;
-- 求班級最高分和最低分
SELECT MAX(math + english + chinese), MIN(math + english + chinese)
FROM student;
-- 求出班級數學最高分和最低分并使用別名
SELECT MAX(math) AS math_high_socre,
MIN(math) AS math_low_socre
FROM student;
使用group by 對列進行分組 ：
注意：當聚合列和非聚合列出現在一起時必須使用group by陳述句
SELECT 列名 FROM 表名 group by 列名(從select后面的幾個列名之中挑);
比如:
-- 顯示每個部門的每種崗位的平均工資和最低工資
SELECT AVG(salary), MIN(salary) , department, job
FROM emp GROUP BY department, job;
-- GROUP BY也可以對查詢結果進行去重
使用 having 對group by 分組后的結果進行過濾
SELECT 列名 FROM 表名 group by 列名 having 布爾運算式;
比如:
-- 顯示平均工資低于 2000 的部門編號和它的平均工資 并使用別名
SELECT AVG(salary) AS '平均工資',
deptno AS '部門編號'
FROM emp
GROUP BY deptno
HAVING AVG(salary) < 2000; # 注意HAVING 子句中不能使用別名，只能使用原始的運算式2.字串函式
3.數學函式
4.日期函式
5.加密函式
MySQL有一些加密函式，可以用來對字串進行加密和解密，常用的有以下幾種：
AES_ENCRYPT(str,key)：回傳用密鑰key對字串str利用高級加密標準演算法加密后的結果，呼叫AES_ENCRYPT的結果是一個二進制字串，以BLOB型別存盤，
AES_DECRYPT(crypt_str,key)：回傳用密鑰key對字串crypt_str利用高級加密標準演算法解密后的結果1，
DECODE(str,key)：使用key作為密鑰解密加密字串str2，
ENCODE(str,key)：使用key作為密鑰加密字串str2，
MD5(str)：回傳str的MD5校驗和，是一個32位十六進制數2，
演示MD5使用
-- 插入
INSERT INTO users (username, password) VALUES ('admin', MD5('123456'));
-- 這樣就可以把用戶名和密碼加密后存盤在users表中，你也可以使用MD5函式來驗證用戶輸入的密碼是否正確，例如：
SELECT * FROM users WHERE username = 'admin' AND password = MD5('123456');
這樣就可以根據用戶名和密碼查詢users表中是否有匹配的記錄，你明白了嗎？
六、約束
MySQL約束陳述句是用來限制表中資料的條件，MySQL支持以下幾種約束：
-
主鍵（PRIMARY KEY）：唯一標識表中的每一行，
-
外鍵（FOREIGN KEY）：參考另一個表中的主鍵或唯一鍵，
-
唯一（UNIQUE）：保證列中的值不重復，
-
非空（NOT NULL）：保證列中的值不為空，
-
檢查（CHECK）：保證列中的值滿足指定的條件，
-
默認（DEFAULT）：為列提供默認值，
可以在創建表或修改表時添加或洗掉約束，
我來介紹一下這6種約束的用法，
主鍵（PRIMARY KEY）
不能重復而且不能為 null
在創建表時，可以使用PRIMARY KEY關鍵字來指定一個或多個列作為主鍵，例如：
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(20),
age INT
);
#復合主鍵
CREATE TABLE t(
id INT ,
`name` VARCHAR(32),
PRIMARY KEY (id, `name`) -- 這里就是復合主鍵
);也可以在創建表后，使用ALTER TABLE陳述句來添加或洗掉主鍵，例如：
ALTER TABLE student ADD PRIMARY KEY (id);
ALTER TABLE student DROP PRIMARY KEY;外鍵（FOREIGN KEY）
外鍵是用于建立和加強兩個表資料之間的鏈接的列， 外鍵的作用是保持資料的一致性、完整性，主要體現在下面兩個方面
-
約束作用：外鍵可以防止在外鍵所在表中插入無效的資料，或者洗掉外鍵指向表中參考的資料，
-
連接作用：外鍵可以通過連接操作實作兩個表之間的聯合查詢，
在創建表時，可以使用FOREIGN KEY關鍵字來指定一個或多個列作為外鍵，并參考主表中的主鍵(primary key)或唯一鍵(unique)
用法:FOREIGN KEY (外鍵所在表的外鍵列列名) REFERENCES 主表名(主表中的主鍵或唯一鍵)
比如:
-- 創建 主表 my_class (外鍵指向表)
CREATE TABLE my_class (
id INT PRIMARY KEY , -- 班級編號
name VARCHAR(32) NOT NULL DEFAULT ''
);
-- 創建 從表 my_stu (外鍵所在表)
CREATE TABLE my_stu (
id INT PRIMARY KEY , -- 學生編號
name VARCHAR(32) NOT NULL DEFAULT '',
class_id INT , -- 學生所在班級的編號
FOREIGN KEY (class_id) REFERENCES my_class(id) -- 外鍵
);
唯一（UNIQUE）
-
在創建表時，可以使用UNIQUE關鍵字來指定一個或多個列的值不重復，例如：
CREATE TABLE user (
id INT PRIMARY KEY,
username VARCHAR(20) UNIQUE NOT NULL, -- unique + not null = primary key
password VARCHAR(20) NOT NULL
);非空（NOT NULL）
在創建表時，可以使用NOT NULL關鍵字來指定一個列的值不為空，例如：
CREATE TABLE book (
id INT PRIMARY KEY,
title VARCHAR(50) NOT NULL,
);檢查（CHECK）
-
在創建表時，可以使用CHECK關鍵字來強制指定一個列的值滿足指定的條件，（注意MySQL目前不支持檢查約束）
CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
salary DECIMAL(10,2),
CHECK (salary >0)
);默認（DEFAULT）
-
在創建表時，可以使用DEFAULT關鍵字來為列提供默認值，在插入資料時如果沒有指定該列的值，則會自動填充默認值，默認值必須是常量，如果要設定當前日期時間作為默認值，則需要用CURRENT_TIMESTAMP函式，例如：
CREATE TABLE post (
id INT PRIMARY KEY,
title VARCHAR(50) NOT NULL,
content TEXT,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);七、自增長
目的：當我們希望一列每插一個資料進去會自動增長時可以使用自增長,這樣可以避免手動輸入主鍵值，也可以保證主鍵值的唯一性
要實作MySQL自增長，需要滿足以下條件：
-
只能有一個欄位使用AUTO_INCREMENT約束
-
該欄位必須有唯一索引（主鍵或主鍵的一部分）
-
該欄位必須具備NOT NULL屬性（要么直接primary key 要么就加 not null ）
-
該欄位只能是整型（TINYINT、SMALLINT、INT、BIGINT)
用法：
欄位名 整型 primary key auto_increment
-- 創建一個名為student的表，包含id、name和age三個欄位
CREATE TABLE student (
id INT NOT NULL AUTO_INCREMENT, -- id欄位為自增長欄位，必須為整數型別且非空
name VARCHAR(20) NOT NULL, -- name欄位為字串型別，非空
age INT NOT NULL, -- age欄位為整數型別，非空
PRIMARY KEY (id) -- id欄位為主鍵，保證唯一性
);
-- 插入兩條記錄，不需要指定id值，資料庫會自動賦值
INSERT INTO student (name, age) VALUES ('Alice', 18);
INSERT INTO student (name, age) VALUES ('Bob', 19);
-- 查詢表中的資料，可以看到id值自動遞增了
SELECT * FROM student;
輸出結果如下：
id name age
1 Alice 18
2 Bob 19
-- 如果插入的時候插入一個不是自增長的值時，再插入資料則從上一個開始自增
INSERT INTO student (id ,name, age) VALUES (666,'Tom', 18);
INSERT INTO student (name, age) VALUES ('John', 18);
-- 查詢
SELECT * FROM student;
輸出結果如下：
id name age
1 Alice 18
2 Bob 19
666 TOM 18
667 John 18
八、索引
MySQL索引是一種幫助MySQL高效獲取資料的資料結構，它可以根據一列或多列的值進行排序，從而加快資料庫的查詢速度
MySQL中常用的索引結構有B-TREE，B+TREE，HASH等，不同的索引結構適用于不同的查詢場景，例如：
-
B-TREE索引適用于全鍵值、鍵值范圍和前綴查找
-
B+TREE索引是B-TREE索引的改進版，具有更高的查詢效率和空間利用率
-
HASH索引適用于等值查找，但不支持范圍查找和排序
用法：
CREATE INDEX 索引名 ON 表名 (列名);
ALTER TABLE 表名 ADD INDEX 索引名 （列名）;
在創建表時，可以使用 PRIMARY KEY，UNIQUE或INDEX關鍵字來定義主鍵、唯一或普通索引12，例如：
-- 創建一個名為student的表，包含id、name和age三個欄位
CREATE TABLE student (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- id欄位為主鍵索引
name VARCHAR(20) NOT NULL UNIQUE, -- name欄位為唯一索引
age INT NOT NULL INDEX -- age欄位為普通索引
);
在已有的表上，可以使用 ALTER TABLE 或 CREATE INDEX陳述句來創建索引
-- 在student表上創建一個名為idx_name_age的復合索引，包含name和age兩個欄位
ALTER TABLE student ADD INDEX idx_name_age (name, age);
-- 或者
CREATE INDEX idx_name_age ON student (name, age);
單獨創建主鍵索引，例如：
ALTER TABLE student ADD PRIMARY KEY (id);
洗掉主鍵索引，例如：
ALTER TABLE student DROP PRIMARY KEY;
在已有的表上，可以使用 DROP INDEX 或 ALTER TABLE陳述句來洗掉索引，
-- 洗掉student表上的idx_name_age索引
DROP INDEX idx_name_age ON student;
-- 或者
ALTER TABLE student DROP INDEX idx_name_age;
資料庫索引的優點與缺點
-
優點：
-
索引可以提高資料檢索的效率，降低資料庫的I/O成本
-
索引可以通過索引列對資料進行排序，降低資料排序的成本，降低CPU的消耗
-
索引可以加快表與表之間的連接，提高關聯查詢的性能
-
索引可以保證行或列的唯一性，生成唯一的row
-
-
缺點：
-
索引需要占用額外的存盤空間，增加資料庫的負擔
-
索引會降低資料更新（插入、洗掉、修改）的速度，因為每次更新都需要維護索引結構
-
不恰當地使用索引可能導致查詢性能下降或者無法使用索引
-
創建索引的條件
九、事務
事務是指一組操作量大，復雜度高的資料操作，它們要么都執行成功，要么都執行失敗，不會出現中間狀態，
-
原子性（Atomicity）：事務中的所有操作要么全部完成，要么全部不完成，不會停滯在中間某個環節，
-
一致性（Consistency）：事務在完成時，必須使所有資料恢復到一致性狀態，在一致性狀態下，所有相關的資料規則都得到滿足，
-
隔離性（Isolation）：事務的隔離性是指多個用戶并發訪問資料庫時，一個用戶的事務不能被其他用戶的事務所干擾，多個并發事務之間要相互隔離，
-
持久性（Durability）：持久性是指一個事務一旦被提交了，那么對資料庫中的資料的改變就是永久性的，
MySQL默認每條DML（增刪改）操作都是一個事務，可以通過 SHOW VARIABLES LIKE 'autocommit’ 查看自動提交模式是否開啟，如果關閉自動提交模式，則需要手動使用COMMIT或ROLLBACK來提交或回滾事務
MySQL只有使用了InnoDB資料庫引擎的資料庫或表才支持事務，
START TRANSACTION; / SET autocommit = off; -- 開啟一個新的事務
SAVEPOINT 保存點名; -- 設定保存點
ROLLBACK TO 保存點名; -- 回滾到某事務,
-- (注意)假如有按順序的3個保存點A,B,C 如果回滾到A時,B和C保存點就被洗掉
ROLLBACK; -- 回滾到最開始的狀態
COMMIT; -- 提交當前事務,一旦執行,就不能再回滾了,保存點也被洗掉,也只有提交事務之后,其他客戶端才(可能)看見資料庫的更新,為什么是可能,參考到后面的隔離級別知識點十、事務隔離級別
MySQL事務的隔離級別，事務的隔離級別是指多個并發事務之間如何相互影響的問題，不同的隔離級別會導致不同的并發問題，
四個事務隔離級別
-
讀未提交（read uncommitted）：最低的隔離級別，一個事務可以讀取另一個未提交事務的修改資料，可能會出現臟讀，不可重復讀，幻讀
-
讀已提交（read committed）：一個事務只能讀取另一個已提交事務的修改資料，可以避免臟讀，但可能會出現不可重復讀、幻讀
-
可重復讀（repeatable read）：MySQL默認隔離級別，一個事務在執行期間看到的資料始終和這個事務在啟動時看到的資料是一致的，可以避免臟讀和不可重復讀，但可能會出現幻讀，不過InnoDB實作的RR通過mvcc機制避免了這種幻讀現象，對于MVCC以及如何在RR上解決幻讀的理解 - 知乎 (zhihu.com)面試重點之一，核心就是采用了一個間隙鎖（Next-key lock），行鎖只能鎖一行，所以對于幻讀這種范圍查詢的資料不能避免資料的插入，
-
可串行化（serializable）：最高的隔離級別，所有事務都必須按照順序執行，可以避免所有并發問題，但性能最差，
那么臟讀、不可重復讀、幻讀都是什么意思呢?
-
臟讀（dirty read）：一個事務讀取了另一個還未提交事務的修改資料，如果后者回滾了操作，則前者所讀取的資料就是不正確的
-
不可重復讀（non-repeatable read）：一個事務在執行期間多次讀取同一份資料(比如某行某列的一個值)，但由于另一個事務對該資料進行了update并提交，導致前者兩次讀取的結果不一致
-
幻讀（phantom read）：一個事務在執行期間多次查詢某段資料(比如一張表)，但由于另一個事務對該段資料進行了delete，insert并提交，導致前者查詢的結果不一致
-
不可重復讀的重點是修改:
同樣的條件, 你讀取過的資料, 再次讀取出來發現值不一樣了
幻讀的重點在于新增或者洗掉
同樣的條件, 第1次和第2次讀出來的記錄數不一樣
當然, 從總的結果來看, 似乎兩者都表現為兩次讀取的結果不一致.
但如果你從控制的角度來看, 兩者的區別就比較大
-
對于前者, 只需要鎖住滿足條件的記錄
-
對于后者, 要鎖住滿足條件及其相近的記錄
-
設定事務隔離級別
在MySQL中，可以使用以下命令設定事務的隔離級別：
set session transaction isolation level 隔離級別; -- read committed /read uncommitted / repeatable read / serializable
例如，設定隔離級別為讀取未提交：
set session transaction isolation level read uncommitted;
設定隔離級別為讀取已提交：
set session transaction isolation level read committed;
你可以使用以下陳述句查看當前資料庫的隔離級別：
SELECT @@global.tx_isolation; -- 查看全域事務隔離級別
SELECT @@tx_isolation; -- 會話事務當前級別
十一、視圖
用法： CREATE VIEW 視圖名 AS
SELECT 列名... FROM 表名(基表);
-- 創建一個視圖 emp_view01，只能查詢 emp 表的(empno、ename, job 和 deptno ) 資訊
CREATE VIEW emp_view01 AS
SELECT empno, ename, job, deptno FROM emp;
-- 1. 創建視圖后，到資料庫去看，對應視圖只有一個視圖結構檔案(形式: 視圖名.frm)
-- 2. 視圖的資料變化會影響到基表，基表的資料變化也會影響到視圖[insert update delete]
十二、用戶管理
#創建用戶
CREATE USER '用戶名' @ '允許登錄的IP地址' IDENTIFIED BY '密碼'
-- 創建用戶 shunping 密碼 123 , 從本地登錄
CREATE USER 'shunping'@ 'localhost' IDENTIFIED BY '123'
洗掉用戶
DROP USER '用戶名'@ '登錄地址'
DROP USER 'shunping'@ 'localhost'
修改密碼
-- 改自己的
set password = password('密碼');
-- 改別人的
set password for '用戶名'@ '登錄地址' = password('密碼');
-- '用戶名' @ '登錄地址'也叫用戶賬戶
給用戶授權
GRANT 權限串列.. -- 比如增刪改查等權限
ON 庫.物件名.. -- 比如賦予物件訪問student資料庫的(表,視圖等物件)的權限
TO 用戶賬戶 [WITH GRANT OPTION]; -- 加上最后的[WITH.. ]代表允許該用戶將權限授予給其他用戶
-- 給 shunping 分配查看 (SELECT) test庫下的 news表 的權限 和 添加 (INSERT) 的權限
GRANT SELECT , INSERT
ON test.news
TO 'shunping'@'localhost';
回收用戶權限
REVOKE 權限串列
ON 庫.物件
FROM 用戶賬戶
-- 回收 shunping 用戶在 testdb.news 表的增改查權限
REVOKE SELECT , UPDATE, INSERT
ON testdb.news
FROM 'shunping'@'localhost'
-- 回收 shunping 用戶在 testdb.news 表的所有權限
REVOKE ALL
ON testdb.news
FROM 'shunping'@'localhost
細節說明
比如:
-- xxx 用戶可以在任何ip登錄 mysql
create user 'xxx';
-- xxx 用戶在 192.168.1.*的 ip 可以登錄 mysql
create user 'xxx'@ '192.168.1.%' ;
DROP USER jack -- 不填host默認就是 DROP USER 'jack'@ '%'
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/548260.html
標籤：MySQL
上一篇：P3 創建資料庫