mysql基礎知識總結「MySQL數據庫原理、設計與應用」

mysql是啥？數據庫又是啥？MySQL：

MySQL 是最流行的關系型數據庫管理系統，在 WEB 應用方面 MySQL 是最好的 RDBMS(Relational Database Management System：關系數據庫管理系統)應用軟件之一。

數據庫：

數據庫（Database）是按照數據結構來組織、存儲和管理數據的倉庫。

每個數據庫都有一個或多個不同的 API 用于創建，訪問，管理，搜索和復制所保存的數據。

我們也可以將數據存儲在文件中，但是在文件中讀寫數據速度相對較慢。

所以，我們使用關系型數據庫管理系統一般是用來存儲和管理大數據量。

關系型數據庫（SQL）：關系型數據庫指的是使用關系模型（二維表格模型）來組織數據的數據庫。

非關系型數據庫（NoSQL）：非關系型數據庫又被稱為 NoSQL（Not Only SQL )，意為不僅僅是 SQL。通常指數據以對象的形式存儲在數據庫中，而對象之間的關系通過每個對象自身的屬性來決定，常用于存儲非結構化的數據。

MySQL安裝及配置首先先下載到一個文件夾中下載后在環境變量中Path路徑 添加MySQL的bin目錄的路徑在 mysql-5.7.19-winx64 文件夾中 創建一個 my.ini 文件，內容為：[client] port=3306 default-character-set=utf8 [mysqld] #設置為自己MYSQL的安裝目錄 basedir=D:hspmysqlmysql-5.7.19-winx64 #設置為MYSQL的數據目錄 datadir=E:mysql-5.7.19-winx64data port=3306 character_set_server=utf8 ##跳過安全檢查，注銷后，需要輸入正確的用戶名和密碼才能啟動 skip-grant-tables使用管理員的身份打開cmd，并切換到安裝的MySQL的bin目錄底下，執行 mysqld-install初始化數據庫：mysqld --initialize-insecure --user=MySQL 此句執行后就會生成一個 data 目錄啟動服務 net start mysql （要看MySQL服務是否啟動可以去任務管理器的服務欄看）關閉服務 net stop mysql進入MySQL管理終端 mysql -u root -p使用MySQL數據庫 ：use mysql; 然后 UPDATE user SET authentication_string = PASSWORD('新的密碼') WHERE user = 'dbadmin' AND host = 'localhost'; FLUSH PRIVILEGES; 這句表示刷新一下權限quit 指退出管理終端

如果真在 5-10步驟中哪里出了非錯，可刪除MySQL后重新配置服務 (sc delete mysql 該語句工作時一定要慎用！！)

4~7步一定要以管理員身份運行控制臺才行

MySQL的服務，默認是“啟動”的狀態，只有啟動了mysql才能用。默認情況下是“自動”啟動，自動啟動表示下一次重啟操作系統的時候自動啟動該服務。

可以在服務上點擊右鍵： 啟動 重啟服務 停止服務 ...

還可以改變服務的默認配置： 服務上點擊右鍵，屬性，然后可以選擇啟動方式： 自動（延遲啟動） 自動 手動 禁用

在windows操作系統當中，怎么使用命令來啟動和關閉mysql服務呢？ 語法： net stop 服務名稱; net start 服務名稱;

其它服務的啟停都可以采用以上的命令。使用命令行窗口連接MySQL數據庫：

首先，MySQL是一種服務(可在任務管理器服務欄中查看到MySQL)，而只要是一個服務都會監聽一個端口

mysql -h 主機IP -P 端口 -u 用戶名 -p密碼 ( p和密碼間不要有空格，如果-p后面沒有寫密碼，回車后會要求輸入密碼 )

如果不寫 -h 主機IP，默認就是連接到本地如果不寫-P 端口，默認就是3306在實際工作中往往會修改端口號而不是使用此默認端口號，此處為了方便就不做改動了MySQL數據庫的三層結構數據在數據庫中的存儲形式

以表格的形式存儲數據

任何一張表都有行和列：

行（row）：被稱為數據/記錄。 列（column）：被稱為字段。

每一個字段都有：字段名、數據類型、約束等屬性。 字段名可以理解是一個普通的名字，見名知意就行。 數據類型：字符串，數字，日期等。

什么是SQL語句？

SQL：結構化查詢語言 程序員需要學習SQL語句，程序員通過編寫SQL語句，然后DBMS負責執行SQL 語句，最終來完成數據庫中數據的增刪改查操作。

SQL語句分類

DQL：數據查詢語言[select…… ]

DML：數據操作語言[增加insert ，修改update，刪除delete] 操作的是表中的數據

DDL：數據定義語言[create、drop、alter] 操作的是表的結構

DCL：數據控制語言[管理數據庫，比如：授權grant、撤銷權限revoke.... ]

TCL：事務控制語言 [如：事務提交：commit; 事務回滾：rollback;]

數據庫、數據庫管理系統、SQL之間的關系

三者之間的關系？ DBMS通過執行SQL來操作DB

先安裝數據庫管理系統MySQL，然后學習SQL語句怎么寫，編寫SQL語句之后，DBMS 對SQL語句進行執行，最終來完成數據庫的數據管理。

常用命令注意：這些命令不區分大小寫都行。注意：mysql是不見“;”不執行，“;”表示結束！

退出exit; 或者 quit;

查看mysql數據庫的版本號： select version();

查看mysql中有哪些數據庫？ show databases; 注意：以分號結尾，分號是英文的分號。

mysql默認自帶了4個數據庫。

怎么選擇使用某個數據庫呢？ mysql> use test; 表示使用一個名字叫做test的數據庫。

查看當前使用的是哪個數據庫？ mysql> select database();

導入一下提前準備好的數據： bjpowernode.sql 是提前為練習準備的數據庫表。 怎么將sql文件中的數據導入呢？(可以將文件拖入source空格 后) 要在使用某個數據庫后才能使用該語句 mysql> source D:course03-MySQLdocumentbjpowernode.sql

注意：路徑中不要有中文！！！！

不看表中的數據，只看表的結構，有一個命令： desc 表名;

簡單查詢查詢一個字段？

select 字段名 from 表名;

查詢兩個字段，或者多個字段怎么辦？ 使用逗號隔開“,”

查詢所有字段？

使用：select * from 表名;

別把把這種寫法寫到 Java 程序中，因為到時還要把 * 先轉換成字段

缺點：①效率低 ②可讀性差。

在實際開發中不建議，可以自己玩沒問題。 你可以在DOS命令窗口中想快速的看一看全表數據可以采用這種方式。

給查詢的列起別名？

使用 as 關鍵字，可以省略 select deptno,dname as deptname from dept;

假設起別名的時候，別名里面有空格，怎么辦？ mysql> select deptno,dname dept name from dept; DBMS看到這樣的語句，進行SQL語句的編譯，不符合語法，編譯報錯。 怎么解決？ select deptno,dname 'dept name' from dept; //加單引號 select deptno,dname "dept name" from dept; //加雙引號

注意：在所有的數據庫當中，字符串統一使用單引號括起來， 單引號是標準，雙引號在oracle數據庫中用不了。但是在mysql 中可以使用。

再次強調：數據庫中的字符串都是采用單引號括起來。這是標準的。 雙引號不標準。字段可以使用數學表達式

select ename,sal*12 from emp;

顯示時字段名是 sal*12 這樣時一般取個別名 select ename,sal*12 year_sal from emp;

條件查詢什么是條件查詢？

查詢出來符合條件的 ? 語法格式： ? select ? 字段1,字段2,字段3.... ? from ? 表名 ? where ? 條件;

都有哪些條件？

= 等于查詢薪資等于800的員工姓名和編號？ select empno,ename from emp where sal = 800;

<> 或 != 不等于查詢薪資不等于800的員工姓名和編號？ select empno,ename from emp where sal != 800;

同理還有：<=，<，>=，>

between … and …. 兩個值之間, 等同于 >= and <=

查詢薪資在2450和3000之間的員工信息？包括2450和3000？

注意： 使用between and的時候，必須遵循左小右大。 between and是閉區間，包括兩端的值。is null 表示為 null（is not null 不為空）

查詢哪些員工的津貼/補助為null？ select empno,ename,sal,comm from emp where comm = null;（ 像這樣用＝是查不到的 ） Empty set (0.00 sec)

注意：在數據庫當中null不能使用等號進行衡量。需要使用is null 因為數據庫中的null代表什么也沒有，它不是一個值，所以不能使用 等號衡量。

正確的：select empno,ename,sal,comm from emp where comm is null;

and 并且查詢工作崗位是MANAGER并且工資大于2500的員工信息？

select empno,ename,job,sal from emp where job = 'MANAGER' and sal > 2500;

or 或者

查詢工作崗位是MANAGER或SALESMAN的員工？

select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

and和or同時出現的話，有優先級問題嗎？（開發中如不記得優先級，加小括號）查詢工資大于2500，并且部門編號為10或20部門的員工？ select * from emp where sal > 2500 and deptno = 10 or deptno = 20; 分析以上語句的問題？ and優先級比or高。 以上語句會先執行and，然后執行or。 以上這個語句表示什么含義？ 找出工資大于2500并且部門編號為10的員工，或者20部門所有員工找出來。 正確做法：select * from emp where sal > 2500 and (deptno = 10 or deptno = 20);

in 包含，相當于多個 or （not in 不在這個范圍中）查詢工作崗位是MANAGER和SALESMAN的員工？ select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN'; select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');

注意：in不是一個區間。in后面跟的是具體的值。

查詢薪資是800和5000的員工信息？ select ename,sal from emp where sal = 800 or sal = 5000; select ename,sal from emp where sal in(800, 5000); //這個不是表示800到5000都找出來。

not in 表示不在這幾個值當中的數據。select ename,sal from emp where sal not in(800, 5000, 3000);

not 可以取非，主要用在 is 或 in 中 is null is not null in not inlike 稱為模糊查詢，支持%或下劃線匹配 %匹配任意多個字符 下劃線：任意一個字符。 （%是一個特殊的符號，_ 也是一個特殊符號）

找出名字中含有O的？ mysql> select ename from emp where ename like '%O%';

找出名字以T結尾的？ select ename from emp where ename like '%T';

找出名字以K開始的？ select ename from emp where ename like 'K%';

找出第二個字每是A的？ select ename from emp where ename like '_A%';

找出第三個字母是R的？ select ename from emp where ename like '__R%';

找出名字中有“_”的？ select name from t_student where name like '%_%'; //這樣不行。

正確： select name from t_student where name like '%_%'; // 轉義字符。

對查詢結果排序排序 order by（ 默認是升序！！！）

查詢所有員工薪資，排序？ select ename,sal from emp order by sal;

怎么降序（后面加上desc）？

指定降序： select ename,sal from emp order by sal desc;

可以兩個字段排序嗎？或者說按照多個字段排序？ 查詢員工名字和薪資，要求按照薪資升序，如果薪資一樣的話， 再按照名字升序排列。 select ename,sal from emp order by sal desc, ename asc; // sal在前，起主導，只有sal相等的時候，才會考慮啟用ename排序。

了解內容：根據字段的位置也可以排序 select ename,sal from emp order by 2; // 2表示第二列。第二列是sal 按照查詢結果的第2列sal排序。

了解一下，不建議在開發中這樣寫，因為不健壯。 因為列的順序很容易發生改變，列順序修改之后，2就廢了

綜合一點的案例： 找出工資在1250到3000之間的員工信息，要求按照薪資降序排列。 select ename,sal from emp where sal between 1250 and 3000 order by sal desc;

以上語句的執行順序必須掌握： 第一步：from 第二步：where 第三步：select 第四步：order by數據處理函數

數據處理函數又被稱為單行處理函數

單行處理函數的特點：一個輸入對應一個輸出。

和單行處理函數相對的是：多行處理函數。（多行處理函數特點：多個輸入，對應1個輸出！）

常見的單行處理行數

lower 轉換小寫select lower(ename) as ename from emp;

-------- | ename | -------- | smith || allen || ward || jones || martin || blake || clark || scott || king || turner || adams || james || ford || miller | -------- 14個輸入，最后還是14個輸出。這是單行處理函數的特點。

upper 轉換大寫select upper(name) as name from t_student;

substr 取子串（substr( 被截取的字符串, 起始下標,截取的長度)）select substr(ename, 1, 1) as ename from emp; 注意：起始下標從1開始，沒有0.

LEFT (string2 ,length )從 string2 中的左邊起取 length 個字符

RIGHT (string2 ,length ) 從 string2 中的右邊起取 length 個字符

找出員工名字第一個字母是A的員工信息？ 第一種方式：模糊查詢 select ename from emp where ename like 'A%'; 第二種方式：substr函數 select ename from emp where substr(ename,1,1) = 'A';

學生名字首字母大寫？

select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student;

注意：字符串拼接一定要用concat而不能用

像這樣不行：select upper(substr(name,1,1)) substr(name,2,length(name) - 1) from t_student;

concat函數進行字符串的拼接select concat(empno,ename) from emp;

可以拼接多個concat(……，……，……)

length 取長度select length(ename) enamelength from emp;

trim 去兩邊空格

select * from emp where ename = trim(' KING');

RTrim切除右邊的空格，LTrim切除左邊的空格

str_to_date 將字符串轉換成日期 date_format 格式化日期 format 設置千分位

（后面講）

一個詭異的現象

select 'abc' as bieming from emp; // select后面直接跟“字面量/字面值”

--------- | bieming | --------- | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | ---------

select abc from emp; ERROR 1054 (42S22): Unknown column 'abc' in 'field list' 這樣肯定報錯，因為會把abc當做一個字段的名字，去emp表中找abc字段去了。

結論：select后面可以跟某個表的字段名（可以等同看做變量名），也可以跟字面量/字面值（數據）。select 21000 as num from dept;// 21000也是被當做一個字面量/字面值。

------- | num | ------- | 21000 | | 21000 | | 21000 | | 21000 | ------- round(要操作的數， 保留幾位小數) 四舍五入

select round(21000.4, 0) as num from dept; //結果和上面一樣

select round(1236.567, -1); // 保留到十位。

rand() 生成隨機數 在 0 ≤ 隨機數 ≤ 1.0 select floor(rand()*100); // 100以內的隨機數

如果使用 rand(seed) 返回隨機數, 范圍 0 ≤ 隨機數 ≤ 1.0, 如果 seed 不變，多次執行返回的結果也是不變的

ifnull是空處理函數。專門處理空的，可以將 null 轉換成一個具體值

在所有的數據庫當中，只要有null參與了數學運算，最終結果就是NULL

計算每個員工的年薪？

select ename, (sal ifnull(comm, 0))*12 year_sal from emp;

case..when..then..when..then..else..end 當員工的工作崗位是MANAGER的時候，工資上調10%，當工作崗位是SALESMAN的時候，工資上調50%,其它正常。 （注意：不修改數據庫，只是將查詢結果顯示為工資上調） select ename, job, sal as oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;

select 后可以不接from 當作簡單的測試工具

等同于接了from dual;

dual為 亞元表, 系統表 可以作為測試表使用

select round(3.14, 3);

---------------- | round(3.14, 3) | ---------------- | 3.140 | ----------------

select concat('13','57','9');

----------------------- | concat('13','57','9') | ----------------------- | 13579 | ----------------------- 多行處理函數五個分組函數

多行處理函數也叫分組函數

多行處理函數的特點：輸入多行，最終輸出一行。

5個： count 計數 sum 求和 avg 平均值 max 最大值 min 最小值

注意： 分組函數在使用的時候必須先進行分組，然后才能用。 如果你沒有對數據進行分組，整張表默認為一組。

找出最高工資？ mysql> select max(sal) from emp;

找出最低工資？ mysql> select min(sal) from emp;

計算工資和： mysql> select sum(sal) from emp;

計算平均工資： mysql> select avg(sal) from emp;

計算員工數量？ mysql> select count(ename) from emp;

計算員工數量？ mysql> select count(ename) from emp;

注意：如果count中填的是表達式的話，要加上 or null， 因為 Mysql 中 count() 函數的一般用法是統計字段非空的記錄數，利用這個特點來進行條件統計，如果字段是 NULL 就不會統計，但是 false 是會被統計到的分組函數在使用的時候需要注意哪些？分組函數自動忽略NULL，你不需要提前對NULL進行處理。

mysql> select sum(comm) from emp;

----------- | sum(comm) | ----------- | 2200.00 | ----------- 分組函數中count(*) 和 count(具體字段) 有什么區別？

count(具體字段)：表示統計該字段下所有不為NULL的元素的總數。 count(*)：統計表當中的總行數。（只要有一行數據count則 ） 每一行記錄不可能都為NULL(不存在一行全為null的表)，一行數據中有一列不為NULL，則這行數據就是有效的。

mysql> select count(*) from emp; ---------- | count(*) | ---------- | 14 | ---------- ?mysql> select count(comm) from emp; ------------- | count(comm) | ------------- | 4 | ------------- 分組函數不能直接使用在where子句中

找出比最低工資高的員工信息。 select ename,sal from emp where sal > min(sal); 表面上意思感覺是沒問題，運行一下發現： ERROR 1111 (HY000): Invalid use of group function ????????????????????????????????????????????????????????????????????? 分組函數在使用的時候必須先進行分組，而分組的group by 執行順序是在where后的，也就是讓分組函數 min 先于分組了，所以會報錯

所有的分組函數可以組合起來一起用

select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;

---------- ---------- ---------- ------------- ---------- | sum(sal) | min(sal) | max(sal) | avg(sal) | count(*) | ---------- ---------- ---------- ------------- ---------- | 29025.00 | 800.00 | 5000.00 | 2073.214286 | 14 | ---------- ---------- ---------- ------------- ---------- 分組查詢

在實際的應用中，可能有這樣的需求， 需要先進行分組，然后對每一組的數據進行操作。 這個時候我們需要使用分組查詢，怎么進行分組查詢呢？ select ... from ... group by ... 計算每個部門的工資和？ 計算每個工作崗位的平均薪資？ 找出每個工作崗位的最高薪資？ ....

將之前的關鍵字全部組合在一起，他們的執行順序？ select ... from ... where ... group by ... order by ...

以上關鍵字的順序不能顛倒，需要記住。執行順序是什么？ 1.from 2.where 3.group by 4.select 5.order by

為什么分組函數不能直接使用在where后面？ select ename,sal from emp where sal > min(sal);//報錯。 因為分組函數在使用的時候必須先分組之后才能使用。 where執行的時候，還沒有分組。所以where后面不能出現分組函數。

select sum(sal) from emp; ? 這個沒有分組，為啥sum()函數可以用呢？ ? 因為select在group by之后執行。

找出每個工作崗位的工資和？ 實現思路：按照工作崗位分組，然后對工資求和。 select job,sum(sal) from emp group by job;

----------- ---------- | job | sum(sal) | ----------- ---------- | ANALYST | 6000.00 || CLERK | 4150.00 || MANAGER | 8275.00 || PRESIDENT | 5000.00 || SALESMAN | 5600.00 | ----------- ---------- 以上這個語句的執行順序？先從emp表中查詢數據。根據job字段進行分組。然后對每一組的數據進行sum(sal)重點結論： 在一條select語句當中，如果有group by語句的話， select后面只能跟：參加分組的字段，以及分組函數。 其它的一律不能跟。找出“每個部門，不同工作崗位”的最高薪資？

兩個字段聯合分組select deptno, job, max(sal) from emp group by deptno, job;

使用having對分組后的數據進一步過濾

having不能單獨使用，having不能代替where，having必須和group by聯合使用。

找出每個部門最高薪資，要求顯示最高薪資大于3000的？

select deptno, max(sal) from emp group by deptno having max(sal) > 3000;

單表查詢大總結

select ? ... ? from ? ... ? where ? ... ? group by ? ... ? having ? ... ? order by ? ...

執行順序？

1.from

2.where

3.group by

4.having

5.select

6.order by

找出除MANAGER崗位之外，每個崗位的平均薪資，要求顯示平均薪資大于1500的， 要求按照平均薪資降序排。

select job, avg(sal) from emp where job != 'MANAGER' group by job having avg(sal) > 1500 order by avg(sal);

distinct 關鍵字

把查詢結果去除重復記錄【distinct】 注意：原表數據不會被修改，只是查詢結果去重。 去重需要使用一個關鍵字：distinct

distinct只能出現在所有字段的最前方，表示對各字段聯合起來的查詢結果去重

這樣編寫是錯誤的，語法錯誤。

select ename,distinct job from emp;

統計一下工作崗位的數量？select count(distinct job) from emp;

連接查詢什么是連接查詢？

從一張表中單獨查詢，稱為單表查詢。 ? 多張表聯合起來查詢數據，被稱為連接查詢。

根據語法的年代分類： SQL92：1992年的時候出現的語法 SQL99：1999年的時候出現的語法 我們這里重點學習SQL99.(這個過程中簡單演示一個SQL92的例子)

根據表連接的方式分類： 內連接： 等值連接 非等值連接 自連接

外連接： ? 左外連接（左連接） ? 右外連接（右連接）

什么是笛卡爾積現象

當兩張表進行連接查詢，沒有任何條件限制的時候，最終查詢結果條數，是 兩張表條數的乘積，這種現象被稱為：笛卡爾積現象。（笛卡爾發現的，這是一個數學現象。）

怎么避免笛卡爾積現象

連接時加條件，滿足這個條件的記錄被篩選出來！

表起別名。很重要。效率問題。select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;

注意：通過笛卡爾積現象得出，表的連接次數越多效率越低，盡量減少表的連接次數。內連接兩張表連接時沒有主次關系的連接稱為內連接等值連接

案例：查詢每個員工所在部門名稱，顯示員工名和部門名？ emp e和dept d表進行連接。條件是：e.deptno = d.deptno

SQL92語法： select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;

sql92的缺點：結構不清晰，表的連接條件，和后期進一步篩選的條件，都放到了where后面。

SQL99語法： inner 可以省略，不省略可讀性會好一些 select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;

sql99優點：表連接的條件是獨立的，連接之后，如果還需要進一步篩選，再往后繼續添加where非等值連接

案例：找出每個員工的薪資等級，要求顯示員工名、薪資、薪資等級？

select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

自連接

內連接之自連接 案例：查詢員工的上級領導，要求顯示員工名和對應的領導名？

emp a 員工表、emp b 領導表

select a.ename 員工, b.ename 領導 from emp a join emp b on a.mgr = b.empno;

外連接

兩張表連接時有主次關系

右(外)連接

// outer是可以省略的，帶著可讀性強。 select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;

right代表什么：表示將join關鍵字右邊的這張表看成主表，主要是為了將 這主表的數據全部查詢出來，捎帶著關聯查詢左邊的表。(如果此表沒有對應和主表滿足 on 后條件的 ,那次表會自動補充 NULL ) 在外連接當中，兩張表連接，產生了主次關系。

左(外)連接

左外連接同理

任何一個右連接都有左連接的寫法。 任何一個左連接都有右連接的寫法。

案例：查詢每個員工的上級領導，要求顯示所有員工的名字和領導名？

select a.ename '員工', b.ename '領導' from emp a left join emp b on a.mgr = b.empno;

三張表連接如下(四張表同理)

語法： select ... from a join b on a和b的連接條件 join c on a和c的連接條件

案例：找出每個員工的部門名稱以及工資等級， 要求顯示員工名、部門名、薪資、薪資等級？

select e.ename, d.dname, s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;

一條SQL中內連接和外連接可以混合。都可以出現！

案例：找出每個員工的部門名稱以及工資等級，還有上級領導， 要求顯示員工名、領導名、部門名、薪資、薪資等級？

select e.ename '員工', u.ename '領導', d.dname, e.sal, s.grade from emp e left join emp u on e.mgr = u.empno join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;

什么是子查詢？

select語句中嵌套select語句，被嵌套的select語句稱為子查詢。

子查詢都可以出現在哪里呢？

select ? ..(select). ? from ? ..(select). ? where ? ..(select).

where子句中的子查詢

案例：找出比最低工資高100的員工姓名和工資？

select ? ename,sal ? from ? emp ? where ? sal > min(sal);

ERROR 1111 (HY000): Invalid use of group function ? where子句中不能直接使用分組函數。

正確：select e.ename, e.sal from emp e where e.sal > (select min(sal) 1000 from emp);

from子句中的子查詢

案例：找出每個崗位的平均工資的薪資等級。

select a.*, s.grade from (select job, avg(sal) avg_sal from emp group by job) a join salgrade s on a.avg_sal between s.losal and s.hisal;

子查詢中的avg(sal)一定要起個別名才能在內連接中使用select后面出現的子查詢(了解即可)

案例：找出每個員工的部門名稱，要求顯示員工名，部門名？ select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

select e.ename,e.deptno,(select dname from dept) as dname from emp e; //錯誤：ERROR 1242 (21000): Subquery returns more than 1 row

注意：對于select后面的子查詢來說，這個子查詢只能一次返回1條結果， 多于1條，就報錯了。！

Union關鍵字

union作用：合并查詢結果集

案例：查詢工作崗位是MANAGER和SALESMAN的員工？

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

select ename,job from emp where job in('MANAGER','SALESMAN');

select ename,job from emp where job = 'MANAGER' union select ename,job from emp where job = 'SALESMAN';

像下面這樣是錯誤的：union在進行結果集合并的時候，要求兩個結果集的列數相同。 select ename,job from emp where job = 'MANAGER' union select ename from emp where job = 'SALESMAN';

// MYSQL可以結果集合并時列和列的數據類型不一致 ，oracle語法嚴格 ，不可以，其結果集合并時列和列的數據類型也要一致。 select ename,job from emp where job = 'MANAGER' union select ename,sal from emp where job = 'SALESMAN';

limit關鍵字

limit作用：將查詢結果集的一部分取出來。通常使用在分頁查詢當中。 百度默認：一頁顯示10條記錄。 分頁的作用是為了提高用戶的體驗，因為一次全部都查出來，用戶體驗差。 可以一頁一頁翻頁看。

完整用法：limit startIndex, length startIndex是起始下標，length是長度。 limit起始下標從0開始。substr的起始下標從1開始

缺省用法：limit 5; 這是取前5.

select ename,sal from emp order by sal desc limit 0,5;

注意：mysql當中limit在order by之后執行！！！！！！

取出工資排名在[3-5]名的員工？ select ename,sal from emp order by sal desc limit 2, 3; 2表示起始位置從下標2開始，就是第三條記錄。 3表示長度。

通用分頁方法：

每頁顯示3條記錄 第1頁：limit 0,3 [0 1 2] 第2頁：limit 3,3 [3 4 5] 第3頁：limit 6,3 [6 7 8] 第4頁：limit 9,3 [9 10 11]

每頁顯示pageSize條記錄 第pageNo頁：limit (pageNo - 1) * pageSize , pageSize

DQL語句總結

關于DQL語句的大總結： select ... from ... where ... group by ... having ... order by ... limit ...

執行順序？ 1.from 2.where 3.group by 4.having 5.select 6.order by 7.limit..

DDL及常見數據類型create

建表的語法格式：

create table 表名(字段名1 數據類型, 字段名2 數據類型, 字段名3 數據類型);

create table 表名( 字段名1 數據類型, 字段名2 數據類型, 字段名3 數據類型 );

表名：建議以t_ 或者 tbl_開始，可讀性強。見名知意。 字段名：見名知意。 表名和字段名都屬于標識符。

drop

刪除一個數據庫

drop database [if exists] 庫名; #if exists 如果庫存在才刪除

刪除一張表：

drop table 表名;

Alter

對表結構的修改

什么是對表結構的修改？ 添加一個字段，刪除一個字段，修改一個字段！！！

第一：在實際的開發中，需求一旦確定之后，表一旦設計好之后，很少的 進行表結構的修改。因為開發進行中的時候，修改表結構，成本比較高。 修改表的結構，對應的java代碼就需要進行大量的修改。成本是比較高的。 這個責任應該由設計人員來承擔！

第二：由于修改表結構的操作很少，所以我們不需要掌握，如果有一天 真的要修改表結構，你可以使用工具！！！！

修改表結構的操作是不需要寫到java程序中的。實際上也不是java程序員的范疇。

常見數據類型

varchar(最長255) 可變長度的字符串 比較智能，節省空間。 會根據實際的數據長度動態分配空間。

優點：節省空間 ? 缺點：需要動態分配空間，速度慢。

char(最長255) 定長字符串 不管實際的數據長度是多少。 分配固定長度的空間去存儲數據。 使用不恰當的時候，可能會導致空間的浪費。

優點：不需要動態分配空間，速度快。 ? 缺點：使用不當可能會導致空間的浪費。

varchar和char我們應該怎么選擇？ 性別字段你選什么？因為性別是固定長度的字符串，所以選擇char。 姓名字段你選什么？每一個人的名字長度不同，所以選擇varchar。

int(最長11) 數字中的整數型。等同于java的int。

bigint 數字中的長整型。等同于java中的long。

float(數字a, 數字b) 其中數字a表示有效數字，數字b表示小數位 單精度浮點型數據

double(數字a, 數字b) 其中數字a表示有效數字，數字b表示小數位 雙精度浮點型數據

date 短日期類型

datetime 長日期類型

clob 字符大對象 最多可以存儲4G的字符串。 比如：存儲一篇文章，存儲一個說明。 超過255個字符的都要采用CLOB字符大對象來存儲。 Character Large OBject:CLOB

blob 二進制大對象 Binary Large OBject 專門用來存儲圖片、聲音、視頻等流媒體數據。 往BLOB類型的字段上插入數據的時候，例如插入一個圖片、視頻等，需要使用IO流才行。

案例：

創建一個學生表？ 學號、姓名、年齡、性別、郵箱地址 create table t_student( no int, name varchar(32), sex char(1), age int(3), email varchar(255) );

數據類型后括號內的數字表示內容的長度,int(3) 表示最多3位數

刪除表： drop table t_student; // 當這張表不存在的時候會報錯！

像下面這樣刪就不會報錯 drop table if exists t_student;

DML插入數據insert （DML）

語法格式： insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);

注意：字段名和值要一一對應。

注意：insert語句但凡是執行成功了，那么必然會多一條記錄。 沒有給其它字段指定值的話，默認值是NULL。

注意：字段名可以省略，省略了的話等于都寫了且按順序寫的

注意：數據庫中的有一條命名規范： 所有的標識符都是全部小寫，單詞和單詞之間使用下劃線進行銜接。修改update

語法格式： update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 條件;

注意：沒有條件限制會導致所有數據全部更新。

刪除數據 delete

語法格式？ ? delete from 表名 where 條件;

注意：沒有條件，整張表的數據會全部刪除！

delete from t_user where id = 2;

delete from t_user; // 刪除所有！

快速創建一張表

mysql> create table emp2 as select * from emp;

原理： 將一個查詢結果當做一張表新建，這個可以完成表的快速復制。表創建出來，同時表中的數據也存在了。

create table mytable as select empno,ename from emp where job = 'MANAGER';

將查詢結果插入到一張表當中？insert相關的

create table dept_bak as select * from dept;

insert into dept_bak select * from dept; //很少用！

快速刪除表中數據

快速刪除表中的數據？【truncate比較重要，必須掌握】

//刪除dept_bak表中的數據 delete from dept_bak; //這種刪除數據的方式比較慢。

mysql> select * from dept_bak; Empty set (0.00 sec)

delete語句刪除數據的原理？(DML操作) 表中的數據被刪除了，但是這個數據在硬盤上的真實存儲空間不會被釋放！！！(相當于把表中格子內容擦掉，但不會把格子刪掉，還占了空間) 這種刪除缺點是：刪除效率比較低 這種刪除優點是：支持回滾，后悔了可以再恢復數據！！！

truncate語句刪除數據的原理？ 這種刪除效率比較高，表被一次截斷，物理刪除。(相當于把占的那片空間給截了) 這種刪除缺點：不支持回滾。 這種刪除優點：快速

用法：truncate table dept_bak; （這種操作屬于DDL操作。）

大表非常大，上億條記錄？？？？ 刪除的時候，使用delete，也許需要執行很久才能刪除完！效率較低。 可以選擇使用truncate刪除表中的數據。只需要不到1秒鐘的時間就刪除結束。效率較高。 但是使用truncate之前，必須仔細詢問客戶是否真的要刪除，并警告刪除之后不可恢復！

truncate是刪除表中的數據，表還在！

刪除表操作drop table 表名; 會把表結構和表中的數據一起都刪了

四大約束什么是約束？

約束對應的英語單詞：constraint 在創建表的時候，我們可以給表中的字段加上一些約束，來保證這個表中數據的 完整性、有效性！！！

約束的作用就是為了保證表中的數據有效！！約束包括哪些？

非空約束：not null ? 唯一性約束: unique ? 主鍵約束: primary key （簡稱PK） ? 外鍵約束：foreign key（簡稱FK） ? 檢查約束：check（mysql不支持，oracle支持）

非空約束：not null

非空約束not null約束的字段不能為NULL。 drop table if exists t_vip; create table t_vip( id int, name varchar(255) not null );

insert into t_vip(id) values(3); ERROR 1364 (HY000): Field 'name' doesn't have a default value

唯一性約束: unique

唯一性約束unique約束的字段不能重復，但是可以為NULL。 drop table if exists t_vip; create table t_vip( id int, name varchar(255) unique, email varchar(255) ); insert into t_vip(id,name,email) values(3,'wangwu',' [email protected] ');

insert into t_vip(id,name,email) values(4,'wangwu',' [email protected] '); ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'

name字段雖然被unique約束了，但是可以為NULL。且可以有多個人的是null

新需求：name和email兩個字段聯合起來具有唯一性！！！！ drop table if exists t_vip; create table t_vip( id int, name varchar(255) unique, // 約束直接添加到列后面的，叫做列級約束。 email varchar(255) unique ); 這張表這樣創建是不符合我以上“新需求”的。 這樣創建表示：name具有唯一性，email具有唯一性。各自唯一。

正確做法：

drop table if exists t_vip; ? create table t_vip( ? id int, ? name varchar(255), ? email varchar(255), ? unique(name,email) // 約束沒有添加在列的后面，這種約束被稱為表級約束。 ? );

需要給多個字段聯合起來添加某一個約束的時候，需要使用表級約束

not null只有列級約束，沒有表級約束！在mysql當中，如果一個字段同時被not null和unique約束的話，該字段自動變成主鍵字段。（注意：oracle中不一樣！）主鍵約束（primary key，簡稱PK）主鍵約束的相關術語？

主鍵約束：就是一種約束。 ? 主鍵字段：該字段上添加了主鍵約束，這樣的字段叫做：主鍵字段 ? 主鍵值：主鍵字段中的每一個值都叫做：主鍵值。

什么是主鍵？有啥用？

主鍵值是每一行記錄的唯一標識。? 主鍵值是每一行記錄的身份證號！！！

記住：任何一張表都應該有主鍵（且僅有一個），沒有主鍵，表無效！！主鍵的特征：not null unique（主鍵值不能是NULL，同時也不能重復！）主鍵可以使用表級約束

表級約束主要是給多個字段聯合起來添加約束

幾個字段聯合起來的主鍵叫 復合主鍵

在實際開發中不建議使用復合主鍵。建議使用單一主鍵

主鍵值建議使用： int bigint char 等類型。

不建議使用：varchar來做主鍵。主鍵值一般都是數字，一般都是定長的！

主鍵除了：單一主鍵和復合主鍵之外，還可以這樣進行分類？

自然主鍵：主鍵值是一個自然數，和業務沒關系。 ? 業務主鍵：主鍵值和業務緊密關聯，例如拿銀行卡賬號做主鍵值。這就是業務主鍵！

在實際開發中使用業務主鍵多，還是使用自然主鍵多一些？ ? 自然主鍵使用比較多，因為主鍵只要做到不重復就行，不需要有意義。 ? 業務主鍵不好，因為主鍵一旦和業務掛鉤，那么當業務發生變動的時候， ? 可能會影響到主鍵值，所以業務主鍵不建議使用。盡量使用自然主鍵。

在mysql當中， 有一種機制，可以幫助我們自動維護一個主鍵值 drop table if exists t_vip; create table t_vip( id int primary key auto_increment, name varchar(255) );

auto_increment 表示自增，從1開始，以1遞增！外鍵約束（foreign key，簡稱FK）

外鍵約束涉及到的相關術語： 外鍵約束：一種約束（foreign key） 外鍵字段：該字段上添加了外鍵約束 外鍵值：外鍵字段當中的每一個值。

業務背景： 請設計數據庫表，來描述“班級和學生”的信息？? 第一種方案：班級和學生存儲在一張表中？？？ t_student no(pk) name classno classname ------------------------------------------------------------------------------- 1 jack 100 北京市大興區亦莊鎮第二中學高三1班 2 lucy 100 北京市大興區亦莊鎮第二中學高三1班 3 lilei 100 北京市大興區亦莊鎮第二中學高三1班 4 hanmeimei 100 北京市大興區亦莊鎮第二中學高三1班 5 zhangsan 101 北京市大興區亦莊鎮第二中學高三2班 6 lisi 101 北京市大興區亦莊鎮第二中學高三2班 7 wangwu 101 北京市大興區亦莊鎮第二中學高三2班 8 zhaoliu 101 北京市大興區亦莊鎮第二中學高三2班 分析以上方案的缺點： 數據冗余，空間浪費！！！！ 這個設計是比較失敗的！ 第二種方案：班級一張表、學生一張表？？ t_class 班級表 classno(pk) classname ------------------------------------------------------ 100 北京市大興區亦莊鎮第二中學高三1班 101 北京市大興區亦莊鎮第二中學高三1班 t_student 學生表 no(pk) name cno(FK引用t_class這張表的classno) ---------------------------------------------------------------- 1 jack 100 2 lucy 100 3 lilei 100 4 hanmeimei 100 5 zhangsan 101 6 lisi 101 7 wangwu 101 8 zhaoliu 101? 當cno字段沒有任何約束的時候，可能會導致數據無效。可能出現一個102，但是102班級不存在。 所以為了保證cno字段中的值都是100和101，需要給cno字段添加外鍵約束。 那么：cno字段就是外鍵字段。cno字段中的每一個值都是外鍵值。注意：理解即記住

刪除表的順序？ 先刪子，再刪父。

創建表的順序？ 先創建父，再創建子。

刪除數據的順序？ 先刪子，再刪父。

插入數據的順序？ 先插入父，再插入子。

create table t_class ( classno int primary key, classname varchar(255););create table t_student ( no int primary key auto_increment, name varchar(255), cno int, foreign key(cno) references t_class(classno));

思考：子表中的外鍵引用的父表中的某個字段，被引用的這個字段必須是主鍵嗎？ 不一定是主鍵，但至少具有unique約束。

即：外鍵引用的字段不一定是主鍵，但一定要有unique約束

測試：外鍵值可以為NULL嗎？ 外鍵只要滿足至少含有unique約束即可，而有unique修飾的字段字段可以為NULL(而且可以有多個NULL)，所以外鍵值當然可以為NULL。

存儲引擎什么是存儲引擎，有什么用呢？

存儲引擎是MySQL中特有的一個術語，其它數據庫中沒有。（Oracle中有，但是不叫這個名字） ? 實際上 存儲引擎是一個 表存儲/組織數據 的方式 ? 不同的存儲引擎，表存儲數據的方式不同。

怎么給表添加/指定“存儲引擎”呢？

在建表的時候可以在最后小括號的")"的右邊使用： ENGINE來指定存儲引擎。 CHARSET來指定這張表的字符編碼方式。

結論： mysql默認的存儲引擎是：InnoDB mysql默認的字符編碼方式是：utf8

建表時指定存儲引擎，以及字符編碼方式。 create table t_product( id int primary key, name varchar(255) )engine=InnoDB default charset=gbk;

怎么查看mysql支持哪些存儲引擎呢？

命令： show engines G

MySQL支持9大存儲引擎，不同版本支持引擎不同，通過上面指令可以看出當前版本哪些支持

關于mysql常用的存儲引擎的介紹

MyISAM存儲引擎？ 它管理的表具有以下特征： 使用三個文件表示每個表： 格式文件 — 存儲表結構的定義（mytable.frm） 數據文件 — 存儲表行的內容（mytable.MYD） 索引文件 — 存儲表上索引（mytable.MYI）：索引用于縮小掃描范圍，提高查詢效率的一種機制。 可被轉換為壓縮、只讀表來節省空間

注意： 對于一張表來說， 主鍵或者加有unique約束的字段上會自動創建索引 。

MyISAM存儲引擎特點： 可被轉換為壓縮、只讀表來節省空間！

MyISAM不支持事務機制，安全性低。

InnoDB存儲引擎 這是mysql默認的存儲引擎，同時也是一個重量級的存儲引擎。 InnoDB支持事務，支持數據庫崩潰后自動恢復機制。 InnoDB存儲引擎最主要的特點是：非常安全。

它管理的表具有下列主要特征： – 每個 InnoDB 表在數據庫目錄中以.frm 格式文件表示 – InnoDB 表空間 tablespace 被用于存儲表的內容（表空間是一個邏輯名稱。表空間存儲數據和索引。）

– 提供一組用來記錄事務性活動的日志文件 ? – 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滾)支持事務處理 ? – 提供全 ACID 兼容 ? – 在 MySQL 服務器崩潰后提供自動恢復 ? – 多版本（MVCC）和行級鎖定 ? – 支持外鍵及引用的完整性，包括級聯刪除和更新

InnoDB最大的特點就是：支持事務，以保證數據的安全。

效率不是很高，并且也不能壓縮，不能轉換為只讀，不能很好的節省存儲空間。

MEMORY存儲引擎？ 使用 MEMORY 存儲引擎的表，其數據存儲在內存中，且行的長度固定， 這兩個特點使得 MEMORY 存儲引擎非常快。

MEMORY 存儲引擎管理的表具有下列特征： – 在數據庫目錄內，每個表均以.frm 格式的文件表示。 – 表數據及索引被存儲在內存中。（目的就是快，查詢快！） – 表級鎖機制。 – 不能包含 TEXT 或 BLOB 字段。

MEMORY 存儲引擎以前被稱為HEAP 引擎。

MEMORY引擎優點：查詢效率是最高的。不需要和硬盤交互。 MEMORY引擎缺點：不安全，關機之后數據消失。因為數據和索引都是在內存當中。

事務

一個事務其實就是一個完整的業務邏輯。是一個最小的工作單元。不可再分。

說到本質上，一個事務其實就是多條DML語句同時成功，或者同時失敗！

什么是一個完整的業務邏輯？ 假設轉賬，從A賬戶向B賬戶中轉賬10000. 將A賬戶的錢減去10000（update語句） 將B賬戶的錢加上10000（update語句） 這就是一個完整的業務邏輯。? 以上的操作是一個最小的工作單元，要么同時成功，要么同時失敗，不可再分。 這兩個update語句要求必須同時成功或者同時失敗，這樣才能保證錢是正確的。

只有DML語句才會有事務這一說，其它語句和事務無關！！！insert delete update 只有以上的三個語句和事務有關系，其它都沒有關系。

因為 只有以上的三個語句是數據庫表中數據進行增、刪、改的。 只要你的操作一旦涉及到數據的增、刪、改，那么就一定要考慮安全問題。

數據安全第一位！！

事務是怎么做到多條DML語句同時成功和同時失敗的呢？

InnoDB存儲引擎：提供一組用來記錄事務性活動的日志文件

事務開啟了： insert insert delete update update 事務結束了！

在事務的執行過程中，每一條DML的操作都會記錄到“事務性活動的日志文件”中。 在事務的執行過程中，我們可以提交事務，也可以回滾事務。

提交事務？ 清空事務性活動的日志文件，將數據全部徹底持久化到數據庫表中。 提交事務標志著，事務的結束。并且是一種全部成功的結束。

回滾事務？ 將之前所有的DML操作全部撤銷，并且清空事務性活動的日志文件 回滾事務標志著，事務的結束。并且是一種全部失敗的結束。

怎么提交事務，怎么回滾事務？

提交事務：commit; 語句 回滾事務：rollback; 語句（回滾永遠都是只能回滾到上一次的提交點！）

事務對應的英語單詞是：transaction

測試一下，在mysql當中默認的事務行為是怎樣的？ mysql默認情況下是支持自動提交事務的。（自動提交） 什么是自動提交？ 每執行一條DML語句，則提交一次！

這種自動提交實際上是不符合我們的開發習慣，因為一個業務通常是需要多條DML語句共同執行才能完成的，為了保證數據的安全，必須要求同時成功之后再提交，所以不能執行一條 就提交一條。

怎么將mysql的自動提交機制關閉掉呢？ 開啟事務時先執行這個命令：start transaction;

事務的四個特性

事務包括4個特性？

A：原子性 說明事務是最小的工作單元。不可再分。所有操作必須同時成功，或者同時失敗。

C：一致性 一個事務在執行前后，數據庫都必須處于正確的狀態，滿足完整性約束。也就是說事務不能破壞數據庫的完整性以及業務邏輯的一致性。

例如：

業務邏輯一致性：A給B轉賬，無論是否操作成功，兩者的賬戶余額之和應該是不變的。

數據庫完整性：數據庫的約束關系應該是正確的，例如唯一索引，主鍵等。

I：隔離性 同時執行的事務是相互隔離的，它們不能相互冒犯。

D：持久性 事務最終結束的一個保障。事務提交，就相當于將沒有保存到硬盤上的數據 保存到硬盤上！

4個隔離級別讀未提交：read uncommitted（最低的隔離級別，沒有提交就讀到了）

什么是讀未提交？ ? 事務A可以讀取到事務B未提交的數據。 ? 這種隔離級別存在的問題就是： ? 臟讀現象 ！(Dirty Read) ? 我們稱讀到了臟數據。 ? 這種隔離級別一般都是理論上的，大多數的數據庫隔離級別都是二檔起步！

讀已提交：read committed（提交之后才能讀到）

什么是讀已提交？ ? 事務A只能讀取到事務B提交之后的數據。 ? 這種隔離級別解決了什么問題？ ? 解決了臟讀的現象。 ? 這種隔離級別存在什么問題？ ? 不可重復讀取數據。 ? 什么是不可重復讀取數據呢？ ? 在事務開啟之后，第一次讀到的數據是3條，當前事務還沒有 ? 結束，可能第二次再讀取的時候，讀到的數據是4條，3不等于4 ? 稱為不可重復讀取。

這種隔離級別是比較真實的數據，每一次讀到的數據是絕對的真實。 ? oracle數據庫默認的隔離級別是：read committed

可重復讀：repeatable read（提交之后也讀不到，永遠讀取的都是剛開啟事務時的數據）

什么是可重復讀取？ ? 事務A開啟之后，不管是多久，每一次在事務A中讀取到的數據 ? 都是一致的。即使事務B將數據已經修改，并且提交了，事務A ? 讀取到的數據還是沒有發生改變，這就是可重復讀。 ? 可重復讀解決了什么問題？ ? 解決了不可重復讀取數據。 ? 可重復讀存在的問題是什么？ ? 會出現幻影讀。 ? 每一次讀取到的數據都是幻象。不夠真實！

早晨9點開始開啟了事務，只要事務不結束，到晚上9點，讀到的數據還是那樣！ ? 讀到的是假象。不夠絕對的真實。

mysql中默認的事務隔離級別就是這個！！序列化/串行化：serializable（最高的隔離級別）

這是最高隔離級別，效率最低。解決了所有的問題。 ? 這種隔離級別表示事務排隊，不能并發！ ? synchronized，線程同步（事務同步） ? 每一次讀取到的數據都是最真實的，并且效率是最低的。

什么是索引？

索引是在數據庫表的字段上添加的，是為了提高查詢效率存在的一種機制。一張表的一個字段可以添加一個索引，當然，多個字段聯合起來也可以添加索引。 索引相當于一本書的目錄，是為了縮小掃描范圍而存在的一種機制。

MySQL在查詢方面主要就是兩種方式： 第一種方式：全表掃描 第二種方式：根據索引檢索。

在mysql數據庫當中索引也是需要排序的，并且這個索引的排序和TreeSet 數據結構相同。TreeSet（TreeMap）底層是一個自平衡的二叉樹！在mysql 當中索引是一個B-Tree數據結構。

遵循左小右大原則存放。采用中序遍歷方式遍歷取數據。

提醒1：在任何數據庫當中主鍵上都會自動添加索引對象。另外在mysql當中，一個字段上如果有unique約束的話，也會自動創建索引對象。

提醒2：在任何數據庫當中，任何一張表的任何一條記錄在硬盤存儲上都有 一個硬盤的物理存儲編號。

提醒3：在mysql當中，索引是一個單獨的對象，不同的存儲引擎以不同的形式 存在，在MyISAM存儲引擎中，索引存儲在一個.MYI文件中。在InnoDB存儲引擎中 索引存儲在一個邏輯名稱叫做tablespace的當中。在MEMORY存儲引擎當中索引 被存儲在內存當中。不管索引存儲在哪里，索引在mysql當中都是一個樹的形式 存在。（自平衡二叉樹：B-Tree）

什么條件下，我們會考慮給字段添加索引呢？

條件1： 數據量龐大 （到底有多么龐大算龐大？這個需要測試，因為每一個硬件環境不同） ? 條件2： 該字段經常出現在where的后面 ，以條件的形式存在，也就是說這個字段總是被掃描。 ? 條件3： 該字段很少的DML (insert delete update)操作。（因為DML之后，索引需要重新排序。）

建議不要隨意添加索引，因為索引也是需要維護的，太多的話反而會降低系統的性能。 建議通過主鍵查詢，建議通過unique約束的字段進行查詢，效率是比較高的。

索引怎么創建？怎么刪除？語法是什么？

創建索引： mysql> create index emp_ename_index on emp(ename); 給emp表的ename字段添加索引，起名：emp_ename_index

刪除索引： mysql> drop index emp_ename_index on emp; 將emp表上的emp_ename_index索引對象刪除。

查看是否使用索引

mysql> explain select * from emp where ename = 'KING';

type=ALL 說明沒有使用索引，如果是 typr=ref 則使用了索引

索引失效

失效的第1種情況： select * from emp where ename like '%T';

ename上即使添加了索引，也不會走索引，為什么？ 原因是因為模糊匹配當中以“%”開頭了！ 盡量避免模糊查詢的時候以“%”開始。 在MySQL8開始好像會走索引了 這是一種優化的手段/策略。

失效的第2種情況： 使用or的時候會失效，如果使用or那么 要求or兩邊的條件字段都要有 索引，才會走索引 ，如果其中一邊有一個字段沒有索引，那么另一個 字段上的索引也會失效。所以這就是為什么不建議使用or的原因。

失效的第3種情況： 使用復合索引的時候，沒有使用左側的列查找，索引失效 什么是復合索引？ 兩個字段，或者更多的字段聯合起來添加一個索引，叫做復合索引。

create index emp_job_sal_index on emp(job,sal);

explain select * from emp where sal = 800; 沒走索引

失效的第4種情況： 在where當中索引列參加了運算，索引失效。

create index emp_sal_index on emp(sal);

explain select * from emp where sal 1 = 800; 沒走索引

失效的第5種情況： 在where當中索引列使用了函數 explain select * from emp where lower(ename) = 'smith';

索引的類別

單一索引：一個字段上添加索引。 復合索引：兩個字段或者更多的字段上添加索引。

主鍵索引：主鍵上添加索引。 唯一性索引：具有unique約束的字段上添加索引。 .....

注意：唯一性比較弱的字段上添加索引用處不大。什么是視圖？

view:站在不同的角度去看待同一份數據。

怎么創建視圖對象？怎么刪除視圖對象？

表復制：mysql> create table dept2 as select * from dept;?dept2表中的數據：mysql> select * from dept2; -------- ------------ ---------- | DEPTNO | DNAME | LOC | -------- ------------ ---------- | 10 | ACCOUNTING | NEW YORK || 20 | RESEARCH | DALLAS || 30 | SALES | CHICAGO || 40 | OPERATIONS | BOSTON | -------- ------------ ----------

創建視圖對象： create view dept2_view as select * from dept2;

刪除視圖對象： drop view dept2_view;

注意：只有DQL語句才能以view的形式創建。create view view_name as 這里的語句必須是DQL語句;

視圖作用

我們可以面向視圖對象進行增刪改查，對視圖對象的增刪改查，會導致 原表被操作！（視圖的特點：通過對視圖的操作，會影響到原表數據。）

//面向視圖查詢 select * from dept2_view;

// 面向視圖插入 insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');

select * from dept2;

視圖對象在實際開發中到底有什么用？方便，簡化開發，利于維護

假設有一條非常復雜的SQL語句，而這條SQL語句需要在不同的位置上反復使用。 每一次使用這個sql語句的時候都需要重新編寫，很長，很麻煩，怎么辦？ 可以 把這條復雜的SQL語句以視圖對象的形式新建。 在需要編寫這條SQL語句的位置直接使用視圖對象，可以大大簡化開發。 并且利于后期的維護，因為修改的時候也只需要修改一個位置就行，只需要 修改視圖對象所映射的SQL語句。

我們以后面向視圖開發的時候，使用視圖的時候可以像使用table一樣。 可以對視圖進行增刪改查等操作。視圖不是在內存當中，視圖對象也是 存儲在硬盤上的，不會消失。

如下面這樣，就是將一條SQL語句以視圖的形式創建出來

create view emp_dept_view as select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno;注意： 創建視圖對應的語句只能是DQL語句。 但是視圖對象創建完成之后，可以對視圖進行增刪改查等操作。

CRUD： 增刪改查，又叫做：CRUD。 CRUD是在公司中程序員之間溝通的術語。一般我們很少說增刪改查。 一般都說CRUD。

C:Create（增） R:Retrieve（查：檢索） U:Update（改） D:Delete（刪）

Java程序員必須會的DBA常用命令：

數據的導入和導出（數據的備份）? 其它命令了解一下即可。（這個B站嫖的培訓日志文檔留著，以后忘了，可以打開文檔復制粘貼。）

數據導出？ 注意：在windows的dos命令窗口中： mysqldump bjpowernode>D:bjpowernode.sql -uroot -p123456

可以導出指定的表嗎？ mysqldump bjpowernode emp>D:bjpowernode.sql -uroot -p123456

數據導入？ 注意：需要先登錄到mysql數據庫服務器上。 然后創建數據庫：create database bjpowernode; 使用數據庫：use bjpowernode 然后初始化數據庫：source D:bjpowernode.sql 該語句后不需要跟分號

如果備份的是表則不需要創建數據庫，直接使用需要導入表的數據即可數據庫設計范式共有3個

第一范式：要求任何一張表必須有主鍵，每一個字段原子性不可再分。

第二范式：建立在第一范式的基礎之上，要求所有非主鍵字段完全依賴主鍵， 不要產生部分依賴。

第三范式：建立在第二范式的基礎之上，要求所有非主鍵字段直接依賴主鍵， 不要產生傳遞依賴。

設計數據庫表的時候， 按照以上的范式進行，可以避免表中數據的冗余，空間的浪費。

第一范式

最核心，最重要的范式，所有表的設計都需要滿足。 必須有主鍵，并且每一個字段都是原子性不可再分。

學生編號 學生姓名 聯系方式------------------------------------------1001 張三 [email protected],13599999991002 李四 [email protected],136999999991001 王五 [email protected],13488888888?以上是學生表，滿足第一范式嗎？ 不滿足，第一：沒有主鍵。第二：聯系方式可以分為郵箱地址和電話?學生編號(pk) 學生姓名 郵箱地址 聯系電話----------------------------------------------------1001 張三 [email protected] 13599999991002 李四 [email protected] 136999999991003 王五 [email protected] 13488888888第二范式

4.4、第二范式： 建立在第一范式的基礎之上， 要求所有非主鍵字段必須完全依賴主鍵，不要產生部分依賴。

學生編號 學生姓名 教師編號 教師姓名

1001 張三 001 王老師 1002 李四 002 趙老師 1003 王五 001 王老師 1001 張三 002 趙老師

這張表描述了學生和老師的關系：（1個學生可能有多個老師，1個老師有多個學生） 這是非常典型的：多對多關系！

分析以上的表是否滿足第一范式？ 不滿足第一范式。

怎么滿足第一范式呢？修改

學生編號 教師編號(pk) 學生姓名 教師姓名

1001 001 張三 王老師 1002 002 李四 趙老師 1003 001 王五 王老師 1001 002 張三 趙老師

學生編號 教師編號，兩個字段聯合做主鍵，復合主鍵（PK: 學生編號 教師編號） 經過修改之后，以上的表滿足了第一范式。但是滿足第二范式嗎？ 不滿足，“張三”依賴1001，“王老師”依賴001，顯然產生了部分依賴。 產生部分依賴有什么缺點？ 數據冗余了。空間浪費了。“張三”重復了，“王老師”重復了。

為了讓以上的表滿足第二范式，你需要這樣設計： 使用三張表來表示多對多的關系！！！！ 學生表

學生編號(pk) 學生名字

1001 張三 ? 1002 李四 ? 1003 王五 ?

教師表

教師編號(pk) 教師姓名

001 王老師

002 趙老師

學生教師關系表

id(pk) 學生編號(fk) 教師編號(fk)

1 1001 001 2 1002 002 3 1003 001 4 1001 002

背口訣： 多對多怎么設計？ 多對多，三張表，關系表兩個外鍵！

第三范式第三范式建立在第二范式的基礎之上 ? 要求所有非主鍵字典必須直接依賴主鍵，不要產生傳遞依賴。

學生編號（PK） 學生姓名 班級編號 班級名稱

1001 張三 01 一年一班 1002 李四 02 一年二班 1003 王五 03 一年三班 1004 趙六 03 一年三班

以上表的設計是描述：班級和學生的關系。很顯然是1對多關系！ 一個教室中有多個學生。

分析以上表是否滿足第一范式？ 滿足第一范式，有主鍵。

分析以上表是否滿足第二范式？ 滿足第二范式，因為主鍵不是復合主鍵，沒有產生部分依賴。主鍵是單一主鍵。

分析以上表是否滿足第三范式？ 第三范式要求：不要產生傳遞依賴！ 一年一班依賴01，01依賴1001，產生了傳遞依賴。 不符合第三范式的要求。產生了數據的冗余。

那么應該怎么設計一對多呢？

班級表：一

班級編號(pk) 班級名稱

01 一年一班 02 一年二班 03 一年三班

學生表：多

學生編號（PK） 學生姓名 班級編號(fk)

1001 張三 01 1002 李四 02 1003 王五 03 1004 趙六 03

背口訣： 一對多，兩張表，多的表加外鍵！！

表的設計總結

總結表的設計？

一對多： 一對多，兩張表，多的表加外鍵

多對多： 多對多，三張表，關系表兩個外鍵

一對一： 一對一放到一張表中不就行了嗎？為啥還要拆分表？ 在實際的開發中，可能存在一張表字段太多，太龐大。這個時候要拆分表。 一對一怎么設計？ 沒有拆分表之前：一張表 t_user id login_name login_pwd real_name email

1 zhangsan 123 張三 zhangsan@xxx ? 2 lisi 123 李四 lisi@xxx

這種龐大的表建議拆分為兩張： t_login 登錄信息表 id(pk) login_name login_pwd

1 zhangsan 123 ? 2 lisi 123

t_user 用戶詳細信息表

id(pk) real_name email login_id(fk unique)

100 張三 zhangsan@xxx 1 200 李四 lisi@xxx 2

口訣：一對一，外鍵唯一

數據庫設計實際中要注意的

數據庫設計三范式是理論上的。

實踐和理論有的時候有偏差。

最終的目的都是為了滿足客戶的需求， 有的時候會拿冗余換執行速度。

因為在sql當中，表和表之間連接次數越多，效率越低。（笛卡爾積）

有的時候可能會存在冗余，但是為了減少表的連接次數，這樣做也是合理的， 并且對于開發人員來說，sql語句的編寫難度也會降低。

原文鏈接：https://blog.csdn.net/qq_61557294/article/details/126925880?utm_source=tuicool&utm_medium=referral