【MySQL】【動力節點-郭鑫】【學習筆記】

02-24

北京動力節點MySQL講義

視頻鏈接：

【動力節點】高清MySQL視頻教程_Java入門到精通_演講?公開課_科技_bilibili_嗶哩嗶哩www.bilibili.com

演示環境建表語句：

DROP TABLE IF EXISTS EMP;DROP TABLE IF EXISTS DEPT;DROP TABLE IF EXISTS SALGRADE;CREATE TABLE DEPT (DEPTNO int(2) not null , DNAME VARCHAR(14) , LOC VARCHAR(13) , primary key (DEPTNO) );CREATE TABLE EMP (EMPNO int(4) not null , ENAME VARCHAR(10) , JOB VARCHAR(9) , MGR INT(4) , HIREDATE DATE DEFAULT NULL , SAL DOUBLE(7,2) , COMM DOUBLE(7,2) , primary key (EMPNO) , DEPTNO INT(2) );CREATE TABLE SALGRADE (GRADE INT , LOSAL INT , HISAL INT ); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (10, ACCOUNTING, NEW YOURK);INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (20, RESEARCH, DALLAS);INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (30, SALES, CHICAGO);INSERT INTO DEPT( DEPTNO, DNAME, LOC ) VALUES (40, OPERATIONS, BOSTON);commit;INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7396, SMITH, CLERK, 7902, 1980-12-17, 800, NULL, 20);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7499, ALLEN, SALESMAN, 7698, 1981-02-20, 1600, 300,30);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7521, WARD, SALESMAN, 7698, 1981-02-22, 1250, 500, 30);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7566, JONES, MANAGER, 7839, 1981-04-02, 2975, NULL, 20);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7654, MARTIN, SALESMAN, 7698, 1981-09-28, 1250, 1400, 30);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7698, BLAKE, MANAGER, 7839, 1981-05-01, 2850, NULL, 30);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7782, CLARK, MANAGER, 7839, 1981-06-09, 2450, NULL, 20);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7788, SCOTT, ANALYST, 7566, 1987-04-19, 3000, NULL, 20);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7839, KING, PRESIDENT, NULL, 1981-11-17, 5000, NULL, 10);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7844, TURNER, SALESMAN, 7698, 1981-09-08, 1500, 0, 30);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7876, ADAMS, CLERK, 7788, 1987-05-23, 1100, NULL, 20);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7900, JAMES, CLERK, 7698, 1981-12-03, 950, NULL, 30);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7902, FORD, ANALYST, 7566, 1981-12-03, 3000, NULL, 20);INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7934, MILLER, CLERK, 7782, 1982-01-23, 1300, NULL, 10);commit;INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (1, 700, 1200);INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (2, 1201, 1400);INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (3, 1401, 2000);INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (4, 2001, 3000);INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) VALUES (5, 3001, 9999);commit;

筆記內容：

2018年2月13日星期二 22:00

MySQL安裝目錄

章節：

1. 資料庫概述及安裝

2. MySQL的安裝及登錄

3. 常用命令

4. 演示數據結構

5. 簡單的查詢

6. 條件查詢

7. 數據排序 asc/desc

8. 處理函數

9. select語句總結

10. 連接查詢/跨表查詢

11. 子查詢

12. union合併(相加)集合

13. limit使用

14. 表

15. 存儲引擎

16. 事務Transaction

17. 索引（了解）

18. 視圖

19. DBA命令

20. 資料庫設計三大範式

21. 34題經典SQL試題

相關概念介紹：

資料庫管理系統 DBMS

資料庫/倉庫 DB

結構化查詢語言 SQL

系統概述：

1. 什麼是資料庫：一個或一組文件，保存了一些符合特定規格的數據，DataBase，資料庫軟體稱為資料庫管理系統，DBMS，DataBase Management System，例如：Oralce，MySQL，SQL Server，Sybase，informix、DB2等

2. MySQL發展歷史

3. SQL概述：Structured

Query Language，結構化查詢語言，是一套標準，使用SQL完成和資料庫的通信，每個不同的資料庫管理系統有不同的特性，有一些特定的SQL特性，大概佔10%

資料庫管理系統安裝：

默認埠號3306，會被攻擊，改成3366

添加系統環境變數：

C:Program

FilesMySQLMySQL Server 5.7in

登錄：

mysql -uroot -p123456

查詢資料庫版本：

mysql --version

mysql -V

select version();

常用基本命令：

查看有哪些資料庫：

show databases;

使用某個資料庫：

use world;

查看有哪些表：

行表示記錄

列表示欄位：欄位名稱，類型，長度，欄位約束

show tables;

查看自己在哪個資料庫下：

select database();

直接查看其他資料庫中有哪些表：

show tables from mysql;

退出資料庫：

exit;

SQL語句分類：

DQL：數據查詢語句，select

DML：數據操作語句，insert/delete/update

DDL：資料庫定義語句，create/drop/alter

TCL：事務控制語言，commit/rollback

創建資料庫：

create database

bjpowernode;

導入資料庫腳本

資料庫腳本：以.sql後綴結尾的文件

導入資料庫腳本：

1. 選定資料庫

2. source命令

mysql> use

bjpowernode;

Database changed

mysql> source

C:UserscopywangDesktopjpowernode.sql

後面不需要分號

資料庫表的介紹：

·show talbes;

desc dept;

欄位，欄位類型，欄位長度，欄位約束

以DEPTNO為例，欄位為DEPTNO，欄位類型為INT，欄位長度為2，約束是欄位不能為空且為主鍵

desc emp;

desc salgrade;

double(7,2) 表示七位數，包含2位小數，從0到99999.99

1、動力節點_MySQL_001_MySQL資料庫管理系統前言

2、動力節點_MySQL_002_MySQL資料庫管理系統相關概念介紹

3、動力節點_MySQL_003_MySQL資料庫管理系統概述

4、動力節點_MySQL_004_MySQL資料庫管理系統安裝過程介紹

5、動力節點_MySQL_005_MySQL資料庫管理系統安裝實際操作

6、動力節點_MySQL_006_資料庫常用命令_查看資料庫版本

7、動力節點_MySQL_007_資料庫常用命令_常用基本命令

8、動力節點_MySQL_008_SQL語句分類

9、動力節點_MySQL_009_導入資料庫腳本

10、動力節點_MySQL_010_資料庫表的介紹

11、動力節點_MySQL_011_簡單查詢_單個欄位和多個欄位查詢

簡單SQL查詢：

單個欄位和多個欄位

select ename from emp;

select sal from emp;

select ename,sal from emp;

select ename,sal,job from

emp;

查詢語句不會修改資料庫中的數據，只適用於顯示。

12、動力節點_MySQL_012_簡單查詢_查詢全部欄位

select

empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;

或者使用（不建議）：效率比前一個方法要低，因為要把*轉成所有具體欄位，而且語義不明確

select * from emp;

13、動力節點_MySQL_013_簡單查詢_查詢員工年薪及給欄位起別命

select sal*12 yearsal

from emp;

select ename,sal*12 as yearsal

from emp;

或者：

select ename,sal*12 yearsal

from emp;

不建議省略as

欄位名可以進行數學計算，起別名

14、動力節點_MySQL_014_條件查詢介紹

關鍵字：where

select 欄位 from 表名

where 條件;

15、動力節點_MySQL_015_條件查詢_條件查詢支持的運算符介紹

運算符

說明

等於

<>或!=

不等於

小於

小於等於

大於

大於等於

between ... and ...

兩個值之間，相當於>= and <=

is null

為null（is not null

不為空）

and

並且

或者

包含，相當於多個or（not in

不在這個範圍中）

not

Not可以取非，用於is和in中

Like為模糊查詢，支持%或者下劃線匹配

%匹配任意個字元

下劃線只匹配一個字元

16、動力節點_MySQL_016_條件查詢_等號操作運算符

select ename,sal from emp

where sal=5000;

MySQL支持自動類型轉換，比如（但是其他資料庫不支持，所以不要這麼寫）：

select ename,sal from emp

where sal=』5000』;

select job,ename from emp

where job=』MANAGER』;

這裡使用單引號是SQL的標準寫法，MySQL也支持使用雙引號，開發中只使用單引號

17、動力節點_MySQL_017_條件查詢_不等號操作運算符

select ename,sal from emp

where sal!=5000;

select job,ename from emp

where job!=』MANAGER』;

18、動力節點_MySQL_018_條件查詢_區間操作運算符

select ename,sal from emp

where sal>=1600 and sal<=3000;

select ename,sal from emp

where sal between 1600 and 3000;

select ename,sal from emp

where sal in (800,1600);

select ename,sal from emp

where sal not in (800,1600);

select ename,sal from emp

where job in (『MANAGER』,SALESMAN』);

select ename,sal from emp

where job not in (『MANAGER』,SALESMAN』);

19、動力節點_MySQL_019_條件查詢_and優先順序高於or

select deptno,ename,sal

from emp where sal>1800 and (deptno = 20 or deptno = 30);

20、動力節點_MySQL_020_條件查詢_is運算符

select ename,comm from

emp where comm is null;

null不是數字，是空值，不能進行數學計算，因此不能用=號

select ename,comm from

emp where comm is not null;

select ename,comm from

emp where comm=0;

21、動力節點_MySQL_021_條件查詢_like模糊查詢

select ename from emp

where ename like 『%s%』;

select ename from emp

where ename like 『s%』;

select ename from emp

where ename like 『_d%』;

select ename from emp

where ename like 『%n_』;

22、動力節點_MySQL_022_數據排序概述

單一欄位排序：order by

默認是是asc

降序是desc

order by 放在where之後

23、動力節點_MySQL_023_數據排序_通過欄位排序

select ename,sal from emp

order by sal;

select ename,sal from emp

order by sal desc;

select ename,sal,hiredate

from emp order by hiredate desc;

多個欄位排序

select deptno,ename,sal

from emp order by deptno,sal desc;

多個欄位排序的時候，首先按照第一個欄位排序，如果第一個欄位相同，那麼按照第二個欄位再進行排序，以此類推

select job,ename,sal from

emp where job=』MANAGER』 order by sal desc;

有條件查詢的時候，order by放在where之後

24、動力節點_MySQL_024_數據排序_通過欄位下標排序

欄位下標從1開始，這裡的下標指的是你查詢顯示的列的下邊，比如job,ename,sal，那麼job就是1，sal是3

select job,ename,sal from

emp where job=』MANAGER』 order by 3 asc;

25、動力節點_MySQL_025_回顧上午內容

複習回顧從01到24的內容

26、動力節點_MySQL_026_數據處理函數_單行處理函數概述

單行處理函數

lower

轉換小寫

upper

轉換大寫

substr

截取子串（substr(被截取的字元串，起始下標，截取的長度）

length

取長度

trim

去空格

str_to_date

將字元串轉換成日期

Date_format

格式化日期

Format

設置千分位

Round

四捨五入

Rand()

生成隨機數

Ifnull

可以把null轉換成一個具體值

注意：數據處理函數是該數據本身特有的，有些函數可能在其他資料庫不起作用

單行的意思：處理一行輸出一行

27、動力節點_MySQL_027_數據處理函數_單行處理函數_轉換大小寫函數

select lower(ename) as

lowername from emp;

select upper(ename) as

uppername from emp;

28、動力節點_MySQL_028_數據處理函數_單行處理函數_substr截取字元串函數

select substr(ename,1,1)

as firstchar from emp;

起始下標是從1開始的！

取3,4,5個字元：

select substr(ename,3,3)

as ename from emp;

29、動力節點_MySQL_029_數據處理函數_單行處理函數_length和trim函數

select length(ename) as

enameLength from emp;

去掉前後空格：一般在用戶輸入的時候，查詢條件裡面會用到（MySQL會自動去掉後面的空格）

select * from emp where ename=trim(『

king 『);

30、動力節點_MySQL_030_數據處理函數_單行處理函數_round四捨五入函數

select round(123.56);

select round(123.56,0);

上面兩個輸出都是124

select round(123.56,1);

輸出是123.6

select round(123.56,-1);

輸出是120

31、動力節點_MySQL_031_數據處理函數_單行處理函數_rand隨機數函數

select rand();

輸出0到1閉區間的隨機數

select

round(rand()*122,0);

輸出0到122閉區間的隨機數

32、動力節點_MySQL_032_數據處理函數_單行處理函數_case_when_then_else_end函數

匹配工作崗位，為MANAGER時，薪水上調10%，為SALESMAN的時候，薪水上調50%

select job,ename,

(case job

when 『MANAGER』 then sal*1.1

when 『SALESMAN』 then sal*1.5

else sal

end) as newsal from emp;

33、動力節點_MySQL_033_數據處理函數_單行處理函數_ifnull空值處理函數

select ename,(sal+ifnull(comm,0))*12

as yearsal from emp;

統計計算了補貼的所有員工年收入，如果沒有補貼，則comm為0

34、動力節點_MySQL_034_數據處理函數_單行處理函數_str_to_date函數

用法：str_to_date(『日期字元串』,』日期格式』)

日期格式包括：

序號

格式符

功能

格式符

功能

4位年份

2位年份

月，01...12

月,1...12

日

24小時制

12小時制

分鐘，格式00...59

%S或者%s

秒，格式00...59

查詢1981-12-03入職的員工

1.自動類型轉換

select ename,hiredate from emp wher

hiredate=』1981-12-03』;

2.標準格式

MySQL默認的日期格式為%y-%m-%d

下面這個例子把varchar轉換成DATE：

select ename,hiredate from emp where

hiredate = str_to_date(』12-03-1981』,』%m-%d-%Y』);

另外一種使用方式在insert中

35、動力節點_MySQL_035_數據處理函數_單行處理函數_str_to_date函數_總結

1、日期是資料庫本身的特使，也是資料庫本身機制中的一個重要內容；

2、每一個資料庫處理日期採用的機制都不一樣，所以在實際開發中將日期欄位定義為DATE類型的情況很少

3、如果使用日期類型，java程序則不能通用，實際開發中會使用「日期字元串」來表示日期

例子：

1、創建t_student表，插入含有日期的數據

create table t_student(

id int(10),

name varchar(32),

birth date);

2、插入數據

1980-01-18

insert into t_student(id,name,birth)

values (1,』jack』,』1980-01-18』);

執行成功，因為格式和MySQL資料庫默認的日期格式相同，會自動類型轉換

3、插入數據

01-18-1980

insert into t_student(id,name,birth)

values (1,』jack』,』01-18-1980』);

插入失敗

正確寫法：

insert into t_student(id,name,birth)

values (1,』jack』,str_to_date(』01-18-1980』,』%m-%d-%Y』));

36、動力節點_MySQL_036_數據處理函數_單行處理函數_dateformat函數

日期類型轉換成特定格式的日期字元串

data_format(日期類型數據，』日期格式』);

例子：

1、查詢員工的入職日期

select

ename,data_format(hiredate,』%m-%d-%Y』) as newhiredate from emp;

select ename,data_format(hiredate,』%m/%d/%Y』)

as newhiredate from emp;

MySQL日期默認格式示例：

1、 hiredate自動轉換成varchar類型，採用年月日格式

select ename,hiredate from emp;

2、使用date_format格式

select ename,date_format(hiredate,』%Y-%m-%d』) as hiredate

from emp;

date_format函數主要用於資料庫查詢操作時，實際工作中，客戶需要日期以特定格式展示的時候，需要使用該函數。

37、動力節點_MySQL_037_數據處理函數_多行處理函數概述

分組函數/聚合函數/多行處理函數

常用以下幾種：

sum

求和

avg

平均

max

最大值

min

最小值

count

記錄數

單行函數式一行輸入對應一行輸出

多行處理函數是多汗輸入對應一行輸出

注意：

1、分組函數會自動忽略空值，不需要手動增加where條件排除空值；

2、分組函數不能直接使用在where關鍵字後

38、動力節點_MySQL_038_數據處理函數_多行處理函數_sum求和函數

例子：

薪水總和：

select sum(sal) as sumsal

from emp;

select

sum(sal+ifnull(comm,0)) as sumsal from emp;

每個月的補助總和：

select

sum(ifnull(comm,0)) as sumcomm from emp;

或者

select sum(comm) as

sumcomm from emp;

空值無需做處理，但是還是建議加上ifnull

39、動力節點_MySQL_039_數據處理函數_多行處理函數_avg求平均值函數

求工資平均值：

select avg(sal) as avgsal

from emp;

select

avg(sal+ifnull(comm,0)) as avgsal from avg;

40、動力節點_MySQL_040_數據處理函數_多行處理函數_max和min求最大最小值函數

select max(sal) as maxsal

from emp;

select min(sal) as minsal

from emp;

41、動力節點_MySQL_041_數據處理函數_多行處理函數_count取得記錄數函數

select

count(*) from emp where comm is null;

count(*)表示多行記錄

如果寫成：

select

count(comm) from emp where comm is null;

結果為0，因為count()會自動忽略空值

count(*)統計的是結果集的總條數，count(欄位名)統計的是該欄位值不為null的總條數

select

count(comm) from emp where comm is not null;

等價於

select

count(comm) from emp;

42、動力節點_MySQL_042_數據處理函數_distinct去除重複記錄概述

distinct：將查詢結果中的某一個欄位的重複記錄去除

用法：distinct 欄位名或distinct

欄位名1，欄位名2 ...

distinct A 區處於欄位名A相同的記錄

distinct A,B 去除與欄位名A和欄位名B相同的記錄

注意：DISTINCT只能出現在所有欄位的最前面，後面接多個欄位為多欄位聯合去重

43、動力節點_MySQL_043_數據處理函數_distinct去除重複記錄_通過例子講解

select distinct job from

emp;

select count(distinct

job) from emp;

select distinct

deptno,job from emp;

44、動力節點_MySQL_044_數據處理函數_分組查詢_01

group by

語句格式：

select

欄位

from

表名

group by

欄位;

例子：

select

job,max(sal) as maxsal

from

emp

group by

job;

查找每個職業中，最高的薪水

select

job,sal from emp group by job;

這種語句，只取拿到的第一個記錄

45、動力節點_MySQL_045_數據處理函數_分組查詢_02

例子：

select

job,ename,max(sal) as maxsal

from

emp

group by

job;

查找每個職業中，最高的薪水，同時會取出每個職業的第一個名字（沒有意義，SCOTT不一定是3000收入的人）

在ORACLE中會直接報錯

46、動力節點_MySQL_046_數據處理函數_分組查詢_03

例子：

計算每個工作崗位的最高薪水，並且按照由低到高排序

先按照工作分組，然後排序

select job,max(sal) as

maxsal from emp group by job order by maxsal;

例子：

計算每個部門的平均薪水

按照部門編號分組，對每一組薪水求平均值

select deptno,avg(sal) as

avgsal from emp group by deptno;

select job,avg(sal) as

avgsal from emp group by job;

例子：

計算出不同部門不同崗位的最高薪水

select deptno,job,max(sal)

as maxsal from emp group by deptno,job;

用法和distinct類似，多個欄位聯合

例子：

找出每個工作崗位的最高薪水，除了MANAGER之外

select job,max(sal) as

maxsal from emp where job not in (『MANAGER』) group by job;

select job,max(sal) as

maxsal from emp where job != 『MANAGER』 group by job;

47、動力節點_MySQL_047_數據處理函數_having數據過濾用法

例子：找出每個工作崗位的平均薪水，要求顯示平均薪水大於2000的

select job,avg(sal) as

avgsal from emp group by job;

select job,avg(sal) as

avgsal from emp group by job having avgsal > 2000;

having 必須和 group

by搭配使用，分組後再篩選

48、動力節點_MySQL_048_數據處理函數_having與where數據過濾用法區別

where 在 group by之前

having 在 group by之後

49、動力節點_MySQL_049_select語句總結

一個完整的SQL語句如下：

select

xxx

from

xxx

where

xxx

group by

xxx

having

xxx

order by

xxx

1、 from 將硬碟上的表文件載入到內存

2、 where 將符合條件的數據行摘取出來，生成一張臨時表

3、 group by 根據列中的數據種類，將當前臨時表劃分成若干個新的臨時表

4、 having 可以過濾掉group by生成的不符合條件的臨時表

5、 select 對當前表進行整列讀取

6、 order by 對select生成的臨時表，進行重新排序，生成新的臨時表

7、 limit 對最終生成的臨時表的數據行進行截取

50、動力節點_MySQL_050_課程回顧

//複習回顧

51、動力節點_MySQL_051_跨表查詢_迪卡爾積現象

實際開發中，數據是存儲在多張表中，表與表之間存在聯繫，檢索數據的時候需要多表聯合檢索，稱為跨表查詢

查詢員工名稱和部門名稱

ename -> emp

dname -> dept

select ename,dname from

emp,dept;

這個語句沒有加條件限制，因此結果是兩個表的乘積

比如表A

表B

結果是

1 4

1 5

1 6

2 4

2 5

2 6

3 4

3 5

3 6

稱為笛卡爾積

52、動力節點_MySQL_052_跨表查詢_分類介紹

跨表查詢：

年代劃分：

SQL92

SQL99

連接方式劃分：

內連接

等值連接

非等值連接

自連接

外連接

左外連接

右外連接

全連接

53、動力節點_MySQL_053_跨表查詢_根據年代分類_SQL92語法

例子：

顯示每個員工信息，並顯示所屬部門名稱

select ename,dname from

emp as e,dept as d where e.deptno = d.deptno;

這個語句的匹配次數依然是笛卡爾積

54、動力節點_MySQL_054_跨表查詢_根據年代分類_SQL99語法

例子：

顯示每個員工信息，並顯示所屬部門名稱

select e.ename,d.dname

from emp as e join dept as d on e.deptno = d.deptno;

格式

開發使用SQL99，不使用SQL92

55、動力節點_MySQL_055_跨表查詢_根據連接方式分類_內連接_等值連接

例子：

顯示每個員工信息，並顯示所屬部門名稱

select e.ename,d.dname

from emp as e inner join dept as d on e.deptno = d.deptno;

56、動力節點_MySQL_056_跨表查詢_根據連接方式分類_內連接_非等值連接

查詢員工薪水對應的薪水等級

select

e.ename,e.sal,s.grade from emp as e inner join salgrade as s on e.sal between

s.losal and s.hisal;

總共執行了14*5=70次

57、動力節點_MySQL_057_跨表查詢_根據連接方式分類_內連接_自連接

例子：

查詢員工所對應的領導名稱，顯示員工名稱和領導名稱

select a.ename as

empename,b.ename as mgrename from emp a join emp b on a.mgr = b.empno;

這個語句相當於把emp表做了兩次別名處理，一張表看成兩張表

58、動力節點_MySQL_058_跨表查詢_根據連接方式分類_外連接概述

A表和B表能夠完全匹配的記錄查詢出來之外，將其中一張表的記錄無條件的完全查詢出來，對方表沒有匹配的記錄時，會自動模擬出null值與之匹配

注意：外連接的查詢結果條數>=內連接的查詢結果條數

59、動力節點_MySQL_059_跨表查詢_根據連接方式分類_外連接_右外連接

包含右邊表的全部行（不管左邊表是否存在與它們匹配的行），以及左邊表中全部匹配的行

例子：

select e.ename,d.dname

from emp e right join dept d on e.deptno = d.deptno;

等價於：省略了outer

select e.ename,d.dname

from emp as e right outer join dept as d on e.deptno = d.deptno;

60、動力節點_MySQL_060_跨表查詢_根據連接方式分類_外連接_左外連接

包含左邊表的全部行（不管右邊表是否存在與它們匹配的行），以及右邊表中全部匹配的行

例子：

select a.ename, b.ename as leadername from emp a left outer

join emp b on a.mgr = b.empno;

這裡KING是沒有領導的顯示為NULL

了解：全連接

61、動力節點_MySQL_061_跨表查詢_根據連接方式分類_通過哪此關鍵字區分內外連接

通過 right 或者 left 區分

62、動力節點_MySQL_062_跨表查詢_多表查詢語法介紹

例子：查詢員工部門名稱，領導名稱和薪水等級

ename -> emp

dname -> dept

grade -> salgrade

select

d.dname,

e.ename,

b.ename as leadername,

s.grade

from

emp e

join

dept d

e.deptno = d.deptno

join

emp b

e.mgr = b.empno

join

salgrade s

e.sal between s.losal and s.hisal;

語句分步完成

63、動力節點_MySQL_063_子查詢_where關鍵字後的嵌套查詢

子查詢：select語句嵌套select語句

注意：select子句可以出現在select/from/where關鍵字後面，例如

select

... (select) ... [很少時候用，了解即可]

from ...

(select) ...

where ...

(select) ...

例子：

找出薪水比公司平均薪水高的員工，要求顯示員工名和薪水

錯誤語句：分組函數不能直接使用在where關鍵字後面

select ename,sal from emp

where sal > avg(sal);

正確語句：

select ename,sal from emp

where sal > (select avg(sal) as avgsal from emp);

64、動力節點_MySQL_064_子查詢_from關鍵字後的嵌套查詢

例子：找出每個部門的平均薪水，並且要求顯示平均薪水的薪水等級

select e.deptno,avg(sal)

as avgsal from emp e group by e.deptno;

把上面作為臨時表t

select e.deptno,avg(sal)

as avgsal from emp e group by e.deptno;

select t.avgsal,s.grade

from t join salgarde s on t.avgsal between s.losal and s.hisal;

最終語句：

select t.avgsal,s.grade

from (select e.deptno,avg(sal) as avgsal from emp e group by e.deptno

) t join salgarde s on

t.avgsal between s.losal and s.hisal;

65、動力節點_MySQL_065_UNION合併查詢結果集

例子：查詢出工作崗位是manager和salesman的員工

select ename,job from emp

where job = 『MANAGER』 or job = 『SALESMAN』;

select ename,job from emp

where job in (『MANAGER』,』SALESMAN』);

//如果是not in的話，括弧里的值是and的關係

使用UNION

select ename,job from emp

where job = 『MANAGER』

union

select ename,job from emp

where job = 『SALESMAN』;

注意：

兩個查詢子句的欄位要一樣，ename/job

如果第二個ename改成empno

輸出結果是一串數字，沒有意義

66、動力節點_MySQL_066_limit用法概述

limit只在MySQL中有效，獲取一個表前幾行或者中間某幾行數據

用法：

limit起始下標m，長度n

m從0開始，表示第一行記錄

n表示從第m+1條開始，取N行記錄

下標從0開始，但是長度是從1開始

67、動力節點_MySQL_067_limit實例講解

例子：取得前5個員工信息

select * from emp limit

0,5;

或者

select * from emp limit

例子：取薪水前3名的員工

select ename,sal from emp

order by sal desc limit 3;

例子：取薪水排名第5到第8

select ename,sal from emp

order by sal desc limit 4,4;

68、動力節點_MySQL_068_limit實現分頁

例子：emp表，每頁顯示3條記錄

頁碼 pageNo

記錄數 pageSize

分析：

第1頁 1,2,3 limit 0,3

第2頁 4,5,6 limit 3,3

第3頁 7,8,9 limit 6,3

limit規律： limit

(pageNo-1)*pageSize,pageSize

69、動力節點_MySQL_069_表_表的定義

表：資料庫基本組成單元，行和列組成，行是記錄，列是欄位，欄位包括：欄位名稱，類型，長度，約束。

70、動力節點_MySQL_070_表_創建表的語法

屬於資料庫定義語言：

create table 表名(

欄位1 欄位類型(長度) 欄位約束,

欄位2 欄位類型(長度) 欄位約束,

欄位3 欄位類型(長度) 欄位約束,

...

欄位N 欄位類型(長度) 欄位約束,

primary key(欄位)

)engine=InnoDB default charset=utf8;

commit;

CREATE TABLE IF NOT EXISTS `runoob_tbl`(

`runoob_id`

INT UNSIGNED AUTO_INCREMENT,

`runoob_title` VARCHAR(100) NOT NULL,

`runoob_author` VARCHAR(40) NOT NULL,

`submission_date` DATE,

PRIMARY KEY

( `runoob_id` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

實例解析：

如果你不想欄位為 NULL 可以設置欄位的屬性為 NOT NULL，在操作資料庫時如果輸入該欄位的數據為NULL ，就會報錯。

AUTO_INCREMENT定義列為自增的屬性，一般用於主鍵，數值會自動加1。

PRIMARY KEY關鍵字用於定義列為主鍵。

您可以使用多列來定義主鍵，列間以逗號分隔。

ENGINE 設置存儲引擎，CHARSET 設置編碼。

71、動力節點_MySQL_071_表_MySQL常用數據類型介紹

數據類型

佔用位元組數

描述

char

char(n)

定長字元串，存儲空間大小固定，使用char(2)表示類型或狀態

varchar

varchar(n)

變長字元串，存儲空間等於實際數據空間

只包含英文字元的字元串

int

4個位元組

表示整型

比如自增ID和表示數量

bigint

8個位元組

長整型

比如自增ID，數據量比較大的情況下使用

float

float(有效數字位數，小數位)

數值型

double

double(有效數字位數，小數位)

數值型

date

8個位元組

表示日期和時間

BLOB

二進位大對象，Binary Large Object，視頻圖像等，開發中基本不用，只需要存文件地址

CLOB

字元大對象，Character Large Object，開發中很少用，最大存4G+的字元串

注意：

VARCHAR和CHAR對比

1、都是字元串

2、 VARCHAR可以根據實際數據長度分配空間，需要進行長度判斷，因此執行效率比較低

3、 CHAR不需要動態分配空間，執行效率高，可能會導致空間浪費

4、欄位中數據不具備伸縮性的時候，採用CHAR存儲，比如（狀態值）性別MALE和FEMALE，其他就是用VARCHAR，比如姓名等

72、動力節點_MySQL_072_表_實例講解創建表及刪除表

例子：創建學生表

create table t_student(

no int(4) not null,

name varchar(32) not null,

gender char(1) not null,

birth date,

email varchar(128),

primary key(no));

查看建表語句：

show create table

t_student;

刪除表：

drop table t_student;

如果存在就刪除表：

drop table if exists

t_student;

73、動力節點_MySQL_073_表_複製表語法

create table emp_bak as

select * from emp;

查看創建表語句：

show create table emp_bak;

74、動力節點_MySQL_074_表_數據操作語言DML語句_insert語法

DML:

insert/update/delete

insert

into 表名(欄位1，欄位2，...，欄位N) values （欄位1的值，欄位2 的值，...，欄位N的值）;

75、動力節點_MySQL_075_上午課程回顧

//複習回顧

76、動力節點_MySQL_076_表_數據操作語言DML語句_update語法

update 表名 set 欄位名稱 = 欄位值，欄位名稱 = 欄位值 where 限制條件；

必須加限制條件！！否則就是全表修改

update t_student set birth =

str_to_date(『1951-10-10』,』%Y-%m-%d』), email = 『lilei@126.com』 where name =

『lilei』;

77、動力節點_MySQL_077_表_數據操作語言DML語句_delete語法

語法：

delete from t_student; //刪除所有

delete from t_student

where no = 4; //加限制條件

注意：這屬於物理刪除，無法恢復

78、動力節點_MySQL_078_表_設置表中欄位默認值

關鍵字：default

79、動力節點_MySQL_079_表_MySQL-Front工具使用介紹

字符集改成gbk

再查一下

80、動力節點_MySQL_080_表_數據操作語言DML語句_快速向表中插入數據

insert

insert into emp_bak

select * from emp where job = MANAGER;

select count(*) from emp_bak;

注意：快速插入保證欄位類型和數量一致

81、動力節點_MySQL_081_表_修改表的結構

ALTER關鍵字

1、 新增：ALTER TABLE 表名 ADD 欄位名欄位類型（長度）；

2、 修改：ALTER TABLE 表名 MODIFY 欄位名欄位類型（長度）；

3、 刪除：ALTER TABLE 表名 DROP 欄位名；

drop table if exists

t_student;

create table t_student(

no int(10),

name varchar(32)

);

alter table t_student add

email varchar(128);

alter table t_student

modify no int(8);

alter table t_student

drop email;

不常用的修改欄位名稱方法：

alter table t_student

change name username varchar(32);

82、動力節點_MySQL_082_約束的概述

目的：保證表中數據的完整和有效

英語：constraint

定義：對表中數據的限制條件

83、動力節點_MySQL_083_約束_非空約束

not null

表明欄位必須由具體數據，不能為NULL

84、動力節點_MySQL_084_約束_唯一性約束_列級寫法

unique

表明欄位必須不能重複，保持唯一

列級約束：寫在欄位後面，保證郵箱唯一性

create table t_student(

no int(10),

name varchar(32) not null,

email varchar(128) unique

);

85、動力節點_MySQL_085_約束_唯一性約束_表級寫法

表級約束：

create table t_student(

no int(10),

name varchar(32) not

null,

email varchar(128),

unique(email)

);

效果和84一樣。

表級支持多個欄位聯合約束

create table t_student(

no int(10),

name varchar(32) not

null,

email varchar(128),

unique(name,email)

);

只有當name和email都一樣的時候，才是重複值，只要有一個值不一樣，就認為不是重複記錄

約束起別名：

create table t_student(

no int(10),

name varchar(32) not

null,

email varchar(128),

constraint

t_user_name_email_unique unique(name,email)

);

查詢約束：

select CONSTRAINT_NAME

from TABLE_CONSTRAINTS where table_name =t_user;

86、動力節點_MySQL_086_約束_非空約束與唯一性約束聯合使用

例子：手機號，不為空，不能重複

not null unique

87、動力節點_MySQL_087_約束_主鍵約束概述及相關概念

primary key 簡稱PK

例子：身份證

欄位為：ID/NAME/GENDER/AGE

ID就是身份證號，作為主鍵約束，不能重複

三個術語：

主鍵約束、主鍵欄位、主鍵值

表中某個欄位添加主鍵約束之後，該欄位成為主鍵欄位，主鍵欄位中出現的每一個數據都成為主鍵值。

88、動力節點_MySQL_088_約束_主鍵約束的作用

1、添加了主鍵 primary key

的欄位「不能重複也不能為空」，而且會自動添加「索引-index」，提高檢索效率。

2、一張表必須有主鍵，否則這張表就是無效的（資料庫設計第一範式），主鍵值是當行數據的唯一標識，就是表中兩行數據完全相同，但是由於主鍵不同，也可以認為是兩行完全不同的數據。

89、動力節點_MySQL_089_約束_主鍵約束_根據個數分類_單一主鍵和複合主鍵

單一主鍵：給一個欄位添加主鍵約束；

列級： id int(10) primary key

表級：在創建資料庫表的時候，最後一個加primary key(id)

上面兩種寫法效果一樣

複合主鍵：

constraint t_user_id_name_pk primary

key(id,name)

只有當id和name都一致的時候，才認為是重複的數據

無論是單一主鍵還是複合主鍵，一張表中有且只能有一個主鍵約束

90、動力節點_MySQL_090_約束_主鍵約束_根據性質分類_自然主鍵和業務主鍵

按照業務性質分類：

自然主鍵：主鍵值是一個自然數，與業務沒有任何關係

業務主鍵：主鍵值和表中業務緊密相關，如果業務發生變化，則主鍵也會受到影響，所以業務主鍵使用較少，大多數情況下使用自然主鍵，比如身份證號位數擴展

91、動力節點_MySQL_091_increment函數_自動生成主鍵值

auto_increment

自增數字auto_increment，用來自動生成主鍵值，是MySQL獨有的函數，默認從1開始，步進1遞增

例子：

id int(4) primary key

auto_increment

使用insert的時候就不需要填寫id的值了

92、動力節點_MySQL_092_課程回顧

//複習回顧

93、動力節點_MySQL_093_約束_外鍵約束概述及相關概念

foreign key

簡稱FK

外鍵約束，外鍵欄位，外鍵值

給某個欄位添加外鍵約束之後，這個欄位就是外鍵欄位，欄位中的數據就是外鍵值。

單一外鍵：給一個欄位添加外鍵約束

複合外鍵：給多個欄位聯合添加外鍵

同一張表中可以有多個外鍵存在

94、動力節點_MySQL_094_約束_外鍵約束_根據個數分類_單一外鍵和複合外鍵

95、動力節點_MySQL_095_約束_外鍵約束_實例創建外鍵約束

需求：設計資料庫表用來存儲學生和班級信息（給出兩種方案）

需求分析：

學生表t_student包含：sno,sname,classno,cname

學生和班級的關係，一個班級有多個學生，一個學生只能屬於一個班級，屬於一對多關係

第一種解決方案：

把學生和班級信息都存儲到一張表中

問題：高三一班重複出現，數據冗餘

第二種解決方案：

創建1張學生表，1張班級表

為了保證t_student表中的cno欄位的數據必須來之t_class中的cno，需要給t_student的cno欄位添加外鍵約束，cno成為外鍵欄位，100、200、300就是外鍵值，cno此處為單一外鍵

完整語句：

注意：

1、 外鍵欄位可以為NULL，空外鍵值為孤兒數據

2、 被引用的欄位必須unique約束（就是父表的主鍵）

3、 外鍵引用之後，就可以區分父表和子表，t_class為父表，t_student為子表

96、動力節點_MySQL_096_約束_外鍵約束_應該注意事項

先創建父表，再創建子表，先在父表插入數據，再在子表中插入數據

97、動力節點_MySQL_097_約束_外鍵約束_查詢出學生所對應的班級名稱

select ts.sname,tc.cname

from t_student as ts join t_class as tc on ts.cno = tc.cno;

注意：MySQL中沒有提供修改外鍵約束的語法

98、動力節點_MySQL_098_約束_外鍵約束_級聯更新與級聯刪除概述

用法：添加級聯更新與級聯刪除的時候，需要在外鍵約束後添加關鍵字

注意：級聯更新與極限刪除操作謹慎使用，因為級聯操作會導致數據改變或者刪除

99、動力節點_MySQL_099_約束_外鍵約束_級聯更新與級聯刪除_級聯刪除的操作

級聯刪除：on delete

cascade

先刪除外鍵約束：

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵欄位;

alter table t_student

drop foreign key t_student_classno_fk;

添加外鍵約束：

alter table t_student add

constraint t_student_classno_fk foreign key(classno) references t_class(cno) on

delete cascade;

100、動力節點_MySQL_100_約束_外鍵約束_級聯更新與級聯刪除_級聯更新的操作

級聯刪除：on update

cascade

先刪除外鍵約束：

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵欄位;

alter table t_student

drop foreign key t_student_classno_fk;

添加外鍵約束：

alter table t_student add

constraint t_student_classno_fk foreign key(classno) references t_class(cno) on

update cascade;

101、動力節點_MySQL_101_存儲引擎的概述

存儲引擎是MySQL特有，其他資料庫沒有

本質：

1、通過採用不同的技術將數據存儲在文件或內存中

2、每一種技術都有不同的存儲機制，提供不同的功能，具備不同的能力

3、選用不同的技術，可以獲得額外的速度和功能，改善應用

查看存儲引擎命令

show enginesG;

一共9種，默認是InnoDB

102、動力節點_MySQL_102_存儲引擎_存儲引擎的基本操作

1、創建表時，可以使用ENGINES=InnoDB指定引擎類型

create table

table_name(

no int(2)

)engine =

InnoDB;

2、如果創建表時沒有指定存儲引擎，會使用默認的存儲引擎

3、默認的存儲引擎可以在安裝目錄的my.ini中配置default-storage-engin指定

4、修改表的存儲引擎：alter

table table_name engine = new_engine_name;

5、查看錶使用的存儲引擎

show create

table empG;

或者

show table

status like 『emp』G;

103、動力節點_MySQL_103_存儲引擎_常用的存儲引擎_MyISAM

1、是MySQL資料庫最常用的

2、管理的表具備以下特性

格式文件存儲表的結構 mytable.frm

b) 數據文件存儲表的數據

mytable.MYD

c) 索引文件存儲表的索引

mytable.MYI

3、可壓縮、只讀表，節省空間

104、動力節點_MySQL_104_存儲引擎_常用的存儲引擎_InnoDB

1、是MySQL默認的存儲引擎

2、管理的表具備以下特性

a) 每個InnoDB表在資料庫目錄中以.frm格式文件表示

b) InnoDB表空間tablespace被用於存儲表的內容

c) 提供一組用來記錄事務性活動的日誌文件

d) 用commit/savepoint/roolback支持事務處理

e) 提供全部ACID兼容

f）在MySQL伺服器崩潰後提供自動回復

g）多版本（MVCC）和行級鎖定

h）支持外鍵及引用的完整性，包括級聯更新和刪除

105、動力節點_MySQL_105_存儲引擎_常用的存儲引擎_MEMORY

1、數據存儲在內存中，且行的長度固定，因此非常快

2、管理的表具備以下特性：

在資料庫目錄中，每個表以.frm格式文件表示

b) 表數據及索引被存儲在內存中

c) 表級鎖機制

d) 欄位屬性不能包含TEXT或者BLOB欄位

3、舊名HEAP引擎

106、動力節點_MySQL_106_存儲引擎_如何選擇合適的存儲引擎

1、MyISAM表適合於大量數據讀而少量數據更新的混合操作。MyISAM表的另一種適用情形是使用壓縮的只讀表

2、如果查詢中包含較多的數據更新操作，應該使用InnoDB，其行級鎖機制和多版本的支持為數據讀取和更新的混合提供了良好的並發機制（由事務控制）

3、使用MEMORY存儲引擎存儲非永久需要的數據，或者是能夠從基於磁碟的表中重新生成測數據。

107、動力節點_MySQL_107_索引的概述

index

相當於一本字典目錄，提高程序的檢索/查詢效率，表中的每一個欄位都可以添加索引

主鍵自動添加索引，能通過主鍵查詢的盡量通過主鍵查詢，效率較高

索引也是存儲在磁碟文件中

1、索引和表相同，是一個對象，表示存儲在硬碟文件中，索引是表的一部分，因此也存放在硬碟文件中

108、動力節點_MySQL_108_索引_索引的檢索方式及什麼情況下創建索引

MySQL資料庫有2中檢索方式：

1、全表掃描（效率較低）

例子：查詢ename=』KING』

select * from emp where ename=』KING』;

如果ename沒有添加索引，那麼通過ename過濾數據的時候，ename欄位會全表掃描

2、通過索引檢索（提高查詢效率）

創建索引的情況：

1、該欄位的數據量龐大

2、該欄位很少使用DML操作（索引需要維護，DML操作太多的時候，影響檢索效率）

3、該欄位經常出現在篩選條件where中

實際開發中根據項目需求或客戶需求綜合調整

109、動力節點_MySQL_109_索引_索引的應用

1、創建索引

語法：

create index 索引名

on 表名（列名）;

create unique

index 索引名 on 表名（列名）;

註：添加unique表示在該表中的該列添加一個唯一性約束

例如：create index dept_dname_index on

dept(dname);

2、查看索引

show

index from 表名;

3、刪除索引

drop

index 索引名on 表名;

110、動力節點_MySQL_110_視圖概述及應用

view

視圖在資料庫管理系統中也是一個對象，以文件形式存在

視圖也對應了一個查詢結果，只是從不同的角度查看數據

語法：

create view 視圖名稱 as

查詢語句;

例如：create view myview as select * from emp;

show tables;

可以查看到新建的myview視圖

視圖底層也是表

查看視圖的語句

show create view my view;

刪除：drop view if exists myview;

111、動力節點_MySQL_111_視圖的作用_隱藏表的實現細節

隱藏表的實現細節

例子

create view myview as select empno as a, ename

as b from emp;

select * from myview;

112、動力節點_MySQL_112_視圖的作用_提高檢索效率

提高檢索效率

例子

create view myview2 as

select e.ename,d.dname from emp e join dept

d on e.deptno = d.deptno;

113、動力節點_MySQL_113_DBA簡單的介紹

1、新建用戶

create

user username identified by 『password』;

例子：

create

user p361 identified by 『123』;

登錄後只能看見information_schema一個庫

2、授權

grant all privileges on

dbname.tbname to 『username』@』login ip』 identified by 『password』 with grant option

dbname = 資料庫，*表示所有資料庫

tbname = 表，*表示所有表

login ip = 登錄IP，%表示任意IP

password = 登錄密碼，空表示不需要密碼

with grant option：表示這個用戶可以授權其他用戶

細粒度授權

(1) root登錄mysql

(2) grant

select,insert,update,delete on *.* to p361 @localhost identified by 『123』;

(3) localhost改為%表示可以再任何機器上登錄mysql

3、 回收授權

4、 導入導出

114、動力節點_MySQL_114_資料庫設計三範式_第一範式

主鍵、欄位不能再分

要求有主鍵，資料庫中不能出現重複記錄，每一個欄位是原子性不能再分

實例：不符合第一範式：

上面存在的問題：

數據存在重複記錄，數據不唯一，沒有主鍵

聯繫方式可以再分為郵箱和手機號，不是原子性

修改方案：

結論：

1、每一行必須唯一，也就是每個表必須有主鍵，這是設計資料庫的最基本要求

2、主鍵主要通常採用數值型或者定長字元串表示

3、關於列不可再分，應該根據具體的情況來決定，如聯繫方式，為了開發商的遍歷有坑呢就採用一個欄位，不分為郵箱和手機號

115、動力節點_MySQL_115_資料庫設計三範式_第二範式

非主鍵欄位完全依賴主鍵

第二範式在第一範式的基礎上，要求資料庫中所有非主鍵欄位完全依賴主鍵（嚴格意義上說，盡量不要使用聯合主鍵）

示例1，數據仍然可能重複

示例2，確定主鍵，學生編號，教師編碼，出現冗餘

分析

1、上面例子雖然確定了主鍵，但是表會出現大量的冗餘，主要涉及到的冗餘欄位是

學生姓名和教師姓名

2、出現冗餘的原因是，學生姓名部分依賴了主鍵的一個欄位

學生編號，沒有依賴教師編號

教師姓名部分依賴了逐漸的一個欄位教師編號，這就是第二範式部分依賴

解決方案：

老師和學生是多對多的關係

學生信息表：學生編號PK，學生姓名

教師信息表：教師編號PK，教師姓名

多對多的關係，需要有1張關係表：

教師-學生關係表：

學生編號PK fk（學生表的學生編號），教師編號PK fk（教師表的教師編號）

結論：典型的多對多關係

116、動力節點_MySQL_116_資料庫設計三範式_第三範式

簡歷在第二範式基礎上，要求非主鍵欄位不能產生傳遞依賴與主鍵欄位

例子1，學生信息表

班級名稱存在冗餘，因為班級名稱沒有直接依賴主鍵（學生編號）

班級名稱依賴於班級編號，班級編號依賴於學生編號（學生調班之後班級編號就變了），這就是傳遞依賴

一對多的設計：在多的一方添加外鍵

解決方案：

學生信息表：學生編號PK，學生姓名，班級編號FK

班級信息表：班級編號PK，班級名稱

117、動力節點_MySQL_117_資料庫設計_資料庫表關係_一對一關係如何設計

三範式總結

1、一對一

方案1：分兩張表存儲，共享主鍵

t_husband t_wife兩張表

t_husband

hno(PK),hname

t_wife

wno(PK，同時也是外鍵FK，引用t_husband的主鍵),wname

方案2：兩張表存儲，外鍵唯一

t_husband

hno(PK),hname,wifeno(FK-unique)

t_wife

wno(FK),wname

118、動力節點_MySQL_118_資料庫設計_實際開發中如何做

1、盡量遵循三大範式

2、根據實際需求進行取捨，有時候冗餘換速度，最終目的，滿足需求

119、動力節點_MySQL_119_事務_什麼是事務

transaction

1. 一個最小的不可再分的工作單元

2. 通常一個事務對應一個完整的業務（如：銀行轉賬業務）

3. 一個完整的業務需要批量的DML語句共同完成

4. 事務只和DML語句有關係

5. 以上所描述的額批量DML語句，數量和業務邏輯相關

例子：銀行轉賬

示例，賬戶轉賬

分析：

1. 上面兩條DML語句必須都執行成功，要麼就不執行

2. 第一條DML執行成功之後，不能修改資料庫，而只是記錄操作，這個記錄是在內存中完成的

3. 第二條DML也成功之後，底層資料庫文件的數據此時完成同步

4. 如果第二條執行失敗，會清空所有的歷史操作記錄，資料庫不變

結論：上面的功能要藉助事務完成

120、動力節點_MySQL_120_事務_事務的四個特性

ACID

原子性（atomicity），事務是最小單位，不可再分

一致性（consistency），所有DML語句操作，必須保證全部成功或者全部失敗

隔離性（isolation），一個事務不會影響其他事務

持久性（durability），事務完成之後，對資料庫做的修改會持久的保存在資料庫中，不會被回滾

121、動力節點_MySQL_121_事務_事務的相關概念

開啟事務：start transaction

結束事務：end transaction

提交事務：commit transaction

回滾事務：rollback transaction

重點：

MySQL默認是自動提交的

show variables like 『%commit%』;

操作：

手動開始事務

start transaction;

DML

commit;

設置不要自動提交：

set autocommit = OFF;

122、動力節點_MySQL_122_事務_事務隔離級別概述

四個隔離級別

read uncommitted 讀未提交，級別最低

事務A和事務B，事務A未提交的數據，事務B可以讀取

這裡讀取到的數據可以叫做臟數據，或者，臟讀 Dirty Read

這種級別一般只是理論上存在，資料庫默認的隔離級別都高於這個

read committed 讀已提交

事務A每一次提交，事務B讀取到的資料庫都會變化

會導致：B一直都在讀取變化的資料庫

Oracle資料庫管理系統的默認隔離級別為可重複讀

repeatable read 可重複讀（MySQL的默認級別）

事務A每次提交都不影響事務B讀取的資料庫內容，也就是事務B讀取到的資料庫一直都不變化

會導致：B感知不到資料庫變化

serializable 串列化

事務排隊，一個完成後才能繼續下一個

這種級別很少使用，吞吐量小，用戶體驗不好

123、動力節點_MySQL_123_事務_演示事務隔離級別

包括會話級別，全局級別

查看當前會話級隔離級別

select @@tx_isolation;

select @@session.tx_isolation;

全局隔離級別

select @@global.tx_isolation;

設置伺服器預設隔離級別

第一種方法：修改my.ini配置文件

第二種方法，通過命令方式設置事務隔離級別

set transaction isolation level

isolation-level;

isolation-level可選4種隔離級別

設置作用範圍：

session

global

set global transaction isolation level READ

COMMITED;

set session transaction isolation level

READ COMMITED;

124、動力節點_MySQL_124_34道經典SQL試題_第1題

取得每個部門最高薪水的人員名稱

第一步：取得每個部門的最高薪水

select e.deptno,max(sal) as maxsal from emp

as e group by e.deptno;

第二步：把上面這個表作為臨時表t，和emp表做表連接

select e.deptno,e.ename,t.maxsal,e.sal

from (select e.deptno,max(sal) as maxsal

from emp as e group by e.deptno) as t

join emp as e

on t.deptno = e.deptno

where t.maxsal = e.sal

order by e.deptno;

125、動力節點_MySQL_125_34道經典SQL試題_第2題

那些人的薪水在部門平均薪水之上

mysql> select e.deptno,avg(e.sal) as

avgsal from emp e group by e.deptno;

+--------+-------------+

| deptno | avgsal |

+--------+-------------+

| 10 | 3150.000000 |

| 20 | 2220.833333 |

| 30 | 1566.666667 |

+--------+-------------+

3 rows in set (0.01 sec)

mysql> select

t.deptno,t.avgsal,e.ename,e.sal from (select e.deptno,avg(e.sal) as avgsal from

emp e group by e.deptno) t join emp e on t.deptno = e.deptno where e.sal >

t.avgsal order by e.deptno;

+--------+-------------+-------+---------+

+--------+-------------+-------+---------+

| 10 | 3150.000000 | KING | 5000.00

| 20 | 2220.833333 | JONES | 2975.00 |

| 20 | 2220.833333 | CLARK | 2450.00 |

| 20 | 2220.833333 | SCOTT | 3000.00 |

| 20 | 2220.833333 | FORD | 3000.00

| 30 | 1566.666667 | ALLEN | 1600.00 |

| 30 | 1566.666667 | BLAKE | 2850.00 |

+--------+-------------+-------+---------+

7 rows in set (0.00 sec)

126、動力節點_MySQL_126_34道經典SQL試題_第3題_第一種情況

取得部門中（所有人的）平均薪水的等級

取得部門的平均薪水：

select e.deptno,avg(e.sal) as avgsal from

emp e group by e.deptno;

根據平均薪水劃分等級：

select t.deptno,t.avgsal,s.grade from (select

e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno) t join salgrade s

on t.avgsal between s.losal and s.hisal order by t.deptno;

127、動力節點_MySQL_127_34道經典SQL試題_第3題_第二種情況

取得部門中（所有人的）平均的薪水等級

每個人的薪水等級

select e.deptno,e.ename,e.sal,s.grade from

emp e join salgrade s on e.sal between s.losal and s.hisal order by e.deptno;

再求平均：

select t.deptno,avg(t.grade) as avgGrade

from (select e.deptno,e.ename,e.sal,s.grade from emp e join salgrade s on e.sal

between s.losal and s.hisal order by e.deptno) group by t.deptno;

128、動力節點_MySQL_128_34道經典SQL試題_第4題

不用組函數MAX取得最高薪水

方案1：

select ename,sal from emp order by sal desc

limit 1;

方案2：

A表：select sal from

emp;

B表：select sal from

emp;

從A表中的sal取出小於B表sal的sal，只有5000沒被取出來

記得去重，默認會顯示所有對比記錄

select sal from emp where sal not in(

select distinct

a.sal from emp a

join emp b

where a.sal <

b.sal);

129、動力節點_MySQL_129_34道經典SQL試題_第5題

取得平均薪水最高的部門的部門編號（可能存在相同平均薪水的部門）

先獲取平均薪水：

select e.deptno,avg(e.sal) as avgsal from

emp e group by e.deptno order by e.deptno;

上面作為臨時表，獲取最大的平均薪水：

select max(t.avgsal) as maxAvgSal from (select

e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno order by e.deptno) t;

過濾：

select e.deptno,avg(e.sal) as avgsal

from emp e

group by e.deptno

having avgsal = (select max(t.avgsal) as

maxAvgSal from (select e.deptno,avg(e.sal) as avgsal from emp e group by

e.deptno order by e.deptno) t)

order by e.deptno;

130、動力節點_MySQL_130_34道經典SQL試題_第6題

取得平均薪水最高的部門的部門名稱

上面一題改一下

select e.deptno,avg(e.sal) as

avgsal,d.dname

from emp e

join dept d

on e.deptno = d.deptno

group by e.deptno

having avgsal = (select max(t.avgsal) as

maxAvgSal from (select e.deptno,avg(e.sal) as avgsal from emp e group by

e.deptno order by e.deptno) t)

order by e.deptno;

131、動力節點_MySQL_131_34道經典SQL試題_第7題

求平均薪水的等級最低的部門的部門名稱

1. 部門的平均薪水

select e.deptno,avg(e.sal) as avgsal,d.dname

from emp e

join dept d

on e.deptno = d.deptno

group by e.deptno,d.dname;

2. 平均薪水的等級，上面的作為臨時表

select t1.deptno,t1.avgsal,s.grade

from t1

join salgrade s

on t1.avgsal between s.losal and s.hisal;

select t1.deptno,t1.avgsal,s.grade

from (select e.deptno,avg(e.sal) as avgsal,d.dname

from emp e

join dept d

on e.deptno = d.deptno

group by e.deptno,d.dname

) t1

join salgrade s

on t1.avgsal between s.losal and s.hisal;

3. 上面再作為臨時表，拿出等級最低的等級是多少

select min(t2.grade) as minGrade from t2;

select min(t2.grade) as minGrade

from (select t1.deptno,t1.avgsal,s.grade

from (select e.deptno,avg(e.sal) as avgsal,d.dname

from emp e

join dept d

on e.deptno = d.deptno

group by e.deptno,d.dname

) t1

join salgrade s

on t1.avgsal between s.losal and s.hisal

) t2;

4. 再取出最終結果：

第二步的結果having做篩選

select t1.deptno,t1.avgsal,s.grade

from t1

join salgrade s

on t1.avgsal between s.losal and s.hisal

having s.grade = (select min(t2.grade) as

minGrade from t2);

132、動力節點_MySQL_132_34道經典SQL試題_第8題

取得比普通員工（員工代碼沒有在MGR上出現的）的最高薪水還要高的經理人姓名

1. 找出普通員工

1.1. 找出mgr

select distinct mgr from emp where mgr is not null;

select * from emp where empno not in (select distinct mgr

from emp where mgr is not null);

//注意一個問題：not in不會忽略NULL，如果直接寫select

* from emp where empno not in (select distinct mgr from emp); 結果就為空，in會忽略NULL，如果去掉not，會顯示記錄

1.2 找出這裡面的最高工資

select max(sal) as maxSal from emp where empno not in (select

distinct mgr from emp where mgr is not null);

普通員工的最高薪水為1600

2. 找出經理人：

select e.ename from emp e where sal > (select max(sal)

as maxSal from emp where empno not in (select distinct mgr from emp where mgr

is not null)) and e.mgr is null;

133、動力節點_MySQL_133_34道經典SQL試題_第9題

取得薪水最高的前五名員工

select * from emp order by sal desc limit

0,5;

134、動力節點_MySQL_134_34道經典SQL試題_第10題

取得薪水最高的第6到第10名員工

select * from emp order by sal desc limit

5,5;

135、動力節點_MySQL_135_34道經典SQL試題_第11題

取得最後入職的5名員工

select * from emp order by hiredate desc

limit 0,5;

136、動力節點_MySQL_136_34道經典SQL試題_第12題

取得每個薪水等級有多少員工

先獲得員工的薪水等級

select e.sal,s.grade

from emp e

join salgrade s

on e.sal between s.losal and s.hisal

order by s.grade;

上面作為臨時表，按照等級分組

select t.grade,count(t.grade) from

(select

e.sal,s.grade

from emp e

join salgrade s

on e.sal between s.losal and s.hisal

order by s.grade

) t

group by t.grade;

137、動力節點_MySQL_137_34道經典SQL試題_第13題

create table s(

sno int(4) primary key unique auto_increment,

sname varchar(32)

);

create table c(

cno int(4) primary key unique auto_increment,

cname varchar(32),

cteacher varchar(32)

);

create table sc(

sno int(4),

cno int(4),

scgrade double(3,1),

constraint sc_sno_cno_pk primary key(sno,cno),

constraint sc_sno_fk foreign key(sno) references s(sno),

constraint sc_cno_fk foreign key(cno) references c(cno)

);

insert into s(sname)

values(zhangsan);

insert into s(sname)

values(lisi);

insert into s(sname)

values(wangwu);

insert into s(sname)

values(zhaoliu);

insert into

c(cname,cteacher) values(java,wu);

insert into

c(cname,cteacher) values(c++,wang);

insert into c(cname,cteacher)

values(c#,zhang);

insert into

c(cname,cteacher) values(mysql,guo);

insert into

c(cname,cteacher) values(oracle,liming);

insert into

sc(sno,cno,scgrade) values(1,1,30);

insert into

sc(sno,cno,scgrade) values(1,2,50);

insert into

sc(sno,cno,scgrade) values(1,3,80);

insert into

sc(sno,cno,scgrade) values(1,4,90);

insert into

sc(sno,cno,scgrade) values(1,5,70);

insert into

sc(sno,cno,scgrade) values(2,1,30);

insert into

sc(sno,cno,scgrade) values(2,2,50);

insert into

sc(sno,cno,scgrade) values(2,3,80);

insert into

sc(sno,cno,scgrade) values(2,4,60);

insert into

sc(sno,cno,scgrade) values(2,5,60);

insert into

sc(sno,cno,scgrade) values(3,1,30);

insert into

sc(sno,cno,scgrade) values(3,2,50);

insert into sc(sno,cno,scgrade)

values(3,3,80);

insert into

sc(sno,cno,scgrade) values(3,4,70);

insert into

sc(sno,cno,scgrade) values(3,5,60);

insert into

sc(sno,cno,scgrade) values(4,1,30);

insert into

sc(sno,cno,scgrade) values(4,2,50);

insert into

sc(sno,cno,scgrade) values(4,3,80);

insert into

sc(sno,cno,scgrade) values(4,4,90);

insert into

sc(sno,cno,scgrade) values(4,5,70);

delete from sc where sno

= 3 and cno = 5;

/*liming老師的課程編號*/

select cno from c where

cteacher = liming;

/*選了liming老師課程的學生編號*/

select sno from sc where

cno = (select cno from c where cteacher = liming);

/*沒有選liming老師課程的學生名字*/

select sname from s where

sno not in (select sno from sc where cno = (select cno from c where cteacher =

liming));

/**

列出2門以上（含2門）不及格學生姓名及平均成績

/*按照學生編號分組，找出不及格學生的編號*/

select sc.sno,count(*) as

courseNum from sc where scgrade < 60

group by sno

having courseNum >= 2;

/*根據編號找學生姓名*/

select

sc.sno,s.sname,count(*) as courseNum

from sc

join s

on sc.sno = s.sno

where scgrade < 60

group by sc.sno,s.sname

having courseNum >= 2;

/*平均成績*/

select

sc.sno,avg(sc.scgrade) as avggrade

from sc

group by sc.sno;

/*兩張表關聯*/

select

t1.sno,t1.sname,t2.avggrade

from (select

sc.sno,s.sname,count(*) as courseNum

from sc

join s

on sc.sno = s.sno

where scgrade < 60

group by sc.sno,s.sname

having courseNum >= 2)

join (select

sc.sno,avg(sc.scgrade) as avggrade

from sc

group by sc.sno

) t2

on t1.sno = t2.sno;

/**

同時學1號課程和2號課程的所有學生姓名

select sno from sc where

cno = 1;

select sno from sc where

cno = 2;

select s.sno,s.sname

from sc

join s

on sc.sno = s.sno

where cno = 1 and sc.sno

in (select sno from sc where cno = 2);

138、動力節點_MySQL_138_34道經典SQL試題_第14題

列出所有員工和領導的名字

把一張表看成兩張表

select a.ename,b.ename as leaderName

from emp a

left join emp b

on a.mgr = b.empno;

139、動力節點_MySQL_139_34道經典SQL試題_第15題

列出僱傭日期早於其直接上級的所有員工編號，姓名，部門名稱

select a.empno,a.ename

from emp a

left join emp b

on a.mgr = b.empno

join dept d

where a.deptno = d.deptno

where a.hiredate < b.hiredate;

140、動力節點_MySQL_140_34道經典SQL試題_第16題

列出部門名稱和這些部門的員工信息，以及沒有員工的部門

select d.dname,e.*

from emp e

right join dept d

on e.deptno = d.deptno

141、動力節點_MySQL_141_34道經典SQL試題_第17題

列出至少有5個員工的所有部門

select e.deptno,count(e.ename) as totalEmp

from emp e

group by e.deptno;

select e.deptno,count(e.ename) as totalEmp

from emp e

group by e.deptno

having totalEmp >= 5;

142、動力節點_MySQL_142_34道經典SQL試題_第18題

列出薪水比smith多的員工信息

select sal from emp where ename = 『SMITH』;

select * from emp where sal > (select

sal from emp where ename = 『SMITH』);

143、動力節點_MySQL_143_34道經典SQL試題_第19題

列出所有CLERK的姓名，部門名稱，部門人數

###########T1##########

select d.detpno,e.ename,d.dname

from emp e

join dept d

on e.deptno = d.deptno

where e.job = 『CLERK』;

###########T2##########

select e.deptno,count(e.ename) as totalEmp

from emp e

group by e.deptno;

select t1.deptno,t1.dname,t1.ename,t2.totalEmp

from () t1

join () t2

on t1.deptno = t2.deptno;

144、動力節點_MySQL_144_34道經典SQL試題_第20題

列出最低薪水大於1500的各種工作及從事此工作的全部僱員人數

select e.job,min(e.sal) as minSal

from emp e

group by e.job;

select e.job,min(e.sal) as minSal

from emp e

group by e.job

having minSal > 1500;

select e.job,min(e.sal) as minSal,count(e.ename)

as totalEmp

from emp e

group by e.job

having minSal > 1500;

145、動力節點_MySQL_145_34道經典SQL試題_第21題

列出在部門SALES工作的員工的姓名，假設不知道SALES的部門編號

select deptno from dept where dname = 『SALES』;

select ename from emp where deptno = ();

146、動力節點_MySQL_146_34道經典SQL試題_第22題

列出薪水高於公司平均薪水的額所有員工，所在部門，上級領導，僱員的工資等級

select avg(sal) as avgsal from emp;

select

from emp e

join dept d

on e.deptno = d.deptno

where e.sal > 平均工資;

select d.dname,e.ename

from emp e

join dept d

on e.deptno = d.deptno

where e.sal > 平均工資;

select d.dname,e.ename,b.ename as leaderName

from emp e

join dept d

on e.deptno = d.deptno

join emp b

on e.mgr = b.empno

where e.sal > 平均工資;

select d.dname,e.ename,b.ename as leaderName,s.grade

from emp e

join dept d

on e.deptno = d.deptno

left join emp b

on e.mgr = b.empno

join salgrade s

on e.sal between s.losal and s.hisal

where e.sal > (select avg(sal) as avgsal

from emp);

147、動力節點_MySQL_147_34道經典SQL試題_第23題

列出與SCOTT從事相同工作的所有員工和部門名稱

select job

from emp

where ename = 『SCOTT』;

select d.dname,e.*

from emp e

join dept d

on e.deptno = d.deptno

where e.job = 工作;

select d.dname,e.*

from emp e

join dept d

on e.deptno = d.deptno

where e.job = (select job

from emp

where ename = 『SCOTT』);

148、動力節點_MySQL_148_34道經典SQL試題_第24題

列出薪金等於部門30中員工的薪金的其他員工的姓名和薪金

select distinct sal from emp where deptno =

30;

select ename,sal

from emp

where sal in(薪金)

and deptno != 30;

select ename,sal

from emp

where sal in(select distinct sal from emp

where deptno = 30) and deptno != 30;

149、動力節點_MySQL_149_34道經典SQL試題_第25題

列出薪金大於部門30中員工的薪金的其他員工的姓名、薪金、部門名稱

select max(sal) as maxSal from emp where

deptno = 30;

select e.ename,e.sal,d.dname

from emp e

join dept d

on e.deptno = d.deptno

where e.sal > (select max(sal) as maxSal

from emp where deptno = 30);

150、動力節點_MySQL_150_34道經典SQL試題_第26題

列出在每個部門工作的員工數量、平均工資、入職總天數

to_days(日期類型)

當前時間函數

now()

服務天數：

select ename,(to_days(now()) - to_days(hiredate))

as serviceDays from emp;

每個部門工作的員工

select e.deptno,count(e.ename) as totolEmp,avg(e.sal)

as avgsal,avg(to_days(now()) - to_days(hiredate)) as avgserveryear

from emp e

group by e.deptno;

151、動力節點_MySQL_151_34道經典SQL試題_第27題

列出所有員工的姓名，部門名稱，工資

select e.ename,d.dname,e.sal

from emp e

right join dept d

on e.deptno = d.deptno;

152、動力節點_MySQL_152_34道經典SQL試題_第28題

列出所有部門的詳細信息和人數

select d.*,count(e.ename) as totalEmp

from emp e

right join dept d

on d.deptno = e.deptno

group by e.deptno,d.dname,d.loc

order by d.deptno;

153、動力節點_MySQL_153_34道經典SQL試題_第29題

列出各種工作的額最低工資以及從事此工作的僱員姓名

select e.job,min(e.sal) as minsal

from emp e

group by e.job;

select e.ename

from emp e

join t

on e.job = t.job

where e.sal = t.minsal;

select e.ename

from emp e

join (select e.job,min(e.sal) as minsal

from emp e

group by e.job) t

on e.job = t.job

where e.sal = t.minsal;

154、動力節點_MySQL_154_34道經典SQL試題_第30題

列出各個部門的manager的最低薪水

select e.deptno,min(e.sal) as minsal

from emp e

where e.job = 『MANAGER』

group by e.deptno;

155、動力節點_MySQL_155_34道經典SQL試題_第31題

求出所有員工的年工資，按照年薪從低到高排序

select e.ename,12*(e.sal+ifnull(e.comm,0))

as yearSal

from emp e

order by yearSal asc;

156、動力節點_MySQL_156_34道經典SQL試題_第32題

求出員工領導的薪水超過3000的員工名稱和領導名稱

select e.ename,b.ename as leadername

from emp e

right join emp b

on e.mgr = b.empno

where b.sal > 3000;

157、動力節點_MySQL_157_34道經典SQL試題_第33題

求部門名稱中帶S字元的員工的工資合計，部門人數

select d.dname,sum(e.sal) as

sumSal,count(e.ename) as totalEmp

from emp e

join dept d

on e.deptno = d.deptno

where d.dname like 『%s%』

group by d.dname;

158、動力節點_MySQL_157_34道經典SQL試題_第34題

給任職日期超過30年的員工加薪10%

create table emp_bak1 as select * from emp;

update emp_bak1 set sal = sal*1.1 where (to_days(now())

- to_days(hiredate))/365 > 30;

2018年2月17日星期六 23:30