MySQL資料庫基礎知識點總結

一、概念

• 資料庫：DataBase，簡稱DB。按照一定格式存儲數據的一些文件的組合顧名思義: 存儲數據的倉庫，實際上就是一堆文件。這些文件中存儲了具有特定格式的數據。

• 資料庫管理系統：DataBaseManagement，簡稱DBMS。資料庫管理系統是專門用來管理資料庫中數據的，資料庫管理系統可以對資料庫當中的數據進行增刪改查。

  • 常見的資料庫管理系統:
    MySQL、 OracleMS SqlServer、 DB2、 sybase等

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

• 以上三者關係：

  DBMS --(執行)--> SQL --(操作)--> DB

二、基礎

• 在Windows操作系統中，使用命令來啟動和停止MySQL服務：

  net stop MySQL;
  net start MySQL;
  #其他服務的啟停也可以使用以上命令，修改服務名稱即可
  

• 使用客戶端登錄MySQL資料庫：（前提：MySQL安裝了，服務啟動了）

  使用bin目錄下的mysql.exe命令來連接mysql資料庫伺服器

  1. 本地登錄（顯示編寫密碼）：

     mysql -uroot -p123  #root是用戶名，123是密碼
     

  2. 本地登錄（隱藏密碼）：

     mysql -uroot -p  #p後面不加密碼直接回車
     

• 表（table）

• 資料庫中是以表格的形式表示數據的

• 任何一張表都有行和列：

  行（row）：被稱為數據/記錄。

  列（column）：被稱為欄位。[每一個欄位都有：欄位名、數據類型、約束等屬性]

• SQL語句的分類

  1. DQL：數據查詢語言（凡是帶有select關鍵字的都是查詢語句）

     ​ select…

  2. DML：數據操作語言（凡是對表當中的數據進行增刪改的都是DML）

     ​ insert：增

     ​ delete：刪

     ​ update：改

     ​ ！註意：DML主要是操作表中的數據data。

  3. DDL：數據定義語言（凡是帶有create、drop、alter的都是DDL）

     ​ create：新建，等同於增

     ​ drop：刪除

     ​ alter：修改

     ​ ！註意：DDL主要是操作表結構。

  4. TCL：事務控制語言

     ​ commit：事務提交

     ​ rollback：事務回滾

  5. DCL：數據控制語言

     ​ grant：授權

     ​ revoke：撤銷許可權

     ​ ……

• 導入.sql數據：

  source D:\document\mysql\node.sql  #路徑中不要有中文
  

• MySQL常用命令

*以下命令不區分大小寫，且命令要有";"才能執行

退出mysql：

exit

查看mysql中有哪些資料庫：

show databases; #以英文分號結尾
#mysql預設自帶了4個資料庫

選擇使用某個資料庫：

use 資料庫名;

創建資料庫：

create database 資料庫名;

查看某個資料庫下的表：

show tables;

查看表中的數據：

select * from 表名;

查看表的結構，不看表的數據：

desc 表名;  # “describe 表名;” 的縮寫

查看MySQL資料庫的版本號：

select version();

查看當前使用的資料庫：

select database();

• 增刪改查又叫做CRUD

  Create

  Retrive

  Update

  Delete

三、查詢

（一）單表查詢

1、簡單查詢

• 查詢一個欄位：

select 欄位名 from 表名;

• 查詢多個欄位：

select 欄位1,欄位2,... from 表名;

• 查詢所有欄位：

select * from 表名;  #這種方式效率低、可讀性差，在實際開發中不建議使用
select 所有欄位名 from 表名;

• 給查詢的列起別名：

select 欄位名 (as) 別名 from 表名;
#as可以省略，原表列名不變，只是將查詢的欄位顯示為別名
#如果起的別名有空格，可以用單引號or雙引號將別名括起來

• 對查詢的欄位進行運算操作：

select 欄位表達式 from 表名;
#比如 “select sal*12 as '年薪' from emp;”  

註意：在所有的資料庫中，字元串統一使用單引號括起來。（單引號是標準，雙引號在Oracle資料庫中用不了，在MySQL中可以使用）

select後面可以跟某個表的欄位名(可以等同看做變數名)，也可以跟字面量/字面值(數據)。

2、條件查詢

條件查詢需要用到where語句，where必須放到from語句表的後面

select 欄位1，欄位2，... from 表名 where 條件;

and和or的優先順序：and > or。如果想讓or先執行，需要加()。

3、排序

• 單欄位排序：

  select 欄位 from 表名 order by 欄位;  #預設升序
  select 欄位 from 表名 order by 欄位 desc;  #指定降序
  select 欄位 from 表名 order by 欄位 asc;  #指定升序
  

• 多欄位排序：

  select 欄位 from 表名 order by 欄位a (desc/asc), 欄位b (desc/asc);  #先按照欄位a進行排序，序號相同的再按照欄位b進行排序
  

• 根據欄位位置進行排序：

  select 欄位 from 表名 order by 2;  #2表示第2列，按照第2列進行排序
  #不建議使用這種方式，列的順序會改變，不健壯
  

• 條件查詢+排序：

  select ... from ... where ... order by ...;  #關鍵字順序不能變
  #執行順序：from、where、select、order by
  

4、數據處理函數/單行處理函數

• 單行處理函數：一個輸入對應一個輸出

實例：

select lower(name) as name from student;
select substr(name,1,1) as name from student; #將會輸出所有name的第1個字元
select name from student where substr(name,1,1)='A';  #相當於：select name from student where name like 'A%';
select concat(upper(substr(name, 1, 1)), substr(name, 2, length(name)-1)) as result from student;  #將name欄位的數據首字母大寫
select round(1236.567, 2) as result form student;  #round(數據, 保留小數位數)，生成的結果行數為student表的行數
select name,job,sal as oldsal,(case job when 'MANAGER' then sal*1.1 when 'SALEMAN' then sal*1.5 else sal end) as newsal from emp;

*在所有資料庫中，只要有null參與的數學運算，結果就為null

• MySQL的日期格式

  符號	含義
  %Y	年
  %m	月
  %d	日
  %h	時
  %i	分
  %s	秒

5、分組函數/多行處理函數

• 多行處理函數：輸入多行，輸出一行。

實例：

select max(sal) from emp;

• 分組函數在使用的時候必須先進行分組，然後才能使用。如果沒有對數據進行分組，預設整張表為一組。

• 註意事項：

  1. 分組函數自動忽略NULL，你不需要提前對null進行處理。

  2. count(*)和count(具體欄位)的區別：

     ​ count(*)：統計表中的總行數。（只要一行數據中有一列不為NULL，則這行數據有效）

     ​ count(具體欄位)：表示統計該欄位下所有不為NULL的元素的總數。

  3. 分組函數不能直接使用在where子句中。

6、分組查詢（重點！！！）

• 分組查詢語法

  select ... from ... group by ...
  

實例：

#找出每個工作崗位的工資和
select job,sum(sal) from emp group by job;

#找出每個部門，不同工作崗位的最高薪資
#技巧：兩個欄位聯合成一個欄位看
select deptno,job,max(sal) from emp group by deptno,job;

• 重要結論：

  在一條select語句中，如果有group by語句的話，select後面只能跟：參加分組的欄位、分組函數。其他的不能跟。

• having

  • having可以對分完組後的數據再次進行過濾。

  • having不能單獨使用，不能代替where，只能和group by搭配使用。

    #找出每個部門最高薪資，並顯示最高薪資大於3000的
    select deptno,max(sal) from emp group by deptno having max(sal)>3000;
    #或者先使用where過濾
    select deptno,max(sal) from emp where sal>3000 group by deptno;
    

  • 優化策略：where和having，優先選擇where。

• 執行順序

select ... from ... where ... group by ... having ... order by ...
#以上關鍵字只能按照這個順序來，不能顛倒

以上語句的執行順序：from、where、group by、having、select、order by

實例：

#找出每個崗位的平均薪資，要求顯示平均薪資大於1500的，除MANAGER崗位之外，要求按照平均薪資降序排
select job,avg(sal) as avgsal
from emp
where job <> 'MANAGER'
group by job
having avg(sal)>1500
order by avgsal desc;

7、查詢結果去重：distinct

select distinct 欄位 from 表名;

• distinct只能出現在所有欄位的前面

• distinct出現在兩個欄位之前，則表示兩個欄位聯合起來去除

實例：

#統計工作崗位數量
select count(distinct job) from emp;

（二）連接查詢

*** 重點！！！**

1、連接查詢

• 連接查詢分類：

  根據語法的年代分類：SQL92、SQL99（重點學習SQL99）

  根據表連接的方式分類：內連接（等值連接+非等值連接+自連接）、外連接（左外連接/左連接+右外連接/右連接、全連接

• 笛卡爾積現象：當兩張表進行連接查詢，沒有任何條件限制時，最終查詢結果的條數是兩張表條數的乘積。（數學現象）

  如何避免？

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

  #各個員工對應的部門
  select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;  #SQL92語法
  #SQL99語法在‘內連接之等值連接’中
  

  • 此時最終查詢的結果條數變少了，匹配次數沒有減少，還是兩張表的條數的乘積。
  • 通過笛卡爾積現象得出，表的連接次數越多，效率越低，儘量避免表的連接次數。

2、內連接

等值連接

• SQL99語法（內連接）：

  select ...
  from a (inner) join b
  on a和b的連接條件
  where 篩選條件
  #inner可以省略，加上可讀性更強，表示內連接
  

  實例：

  #各個員工對應的部門
  select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;  #SQL99語法（將sql92中from後面的,換成join，where換成了on）
  

• SQL92和SQL99對比

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

  SQL99優點：表連接的條件時獨立的，連接之後，如果還需要進一步篩選，則再往後添加where

• 在on後面是一個等值條件，所以稱為等值連接

非等值連接

• 非等值連接：on後面的條件不是等值關係。

實例：

#找出每個員工的薪資等級，並顯示員工名、薪資、等級
select e.ename, e.sal, s.grade
from emp e (inner) join salgrade s
on e.sal between s.losal and s.hisal;

自連接

• 自連接技巧：一張表看作兩張表。

實例：

#查詢員工的上級領導，要求顯示員工名和對應的領導名
select a.ename as '員工名', b.ename as '領導名'
from emp a join emp b
on a.mgr=b.empno;
#將一張表看成兩張表

3、外連接

• 外連接與內連接的區別：內連接中，連接的兩張表沒有主次關係，平等的；在外連接中，兩張表連接，產生主次關係。（主要看join前面有無right/left來區分）

• 帶有right的是右外連接，又叫做右連接。

  帶有left的是左外連接，又叫做左連接。

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

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

• 外連接的查詢結果條數一定是>=內連接的查詢結果條數

實例：

#各個員工對應的部門，同時將沒有員工對應的部門也顯示出來
select e.ename,d.dname
from emp e right (outer) join dept d
on e.deptno=d.deptno; 
#右外連接

• 這裡的right：表示將join關鍵字右邊的這張表看成主表，主要是為了將這張主表的數據全部查詢出來，捎帶著關聯查詢左邊的表emp。
• outer可以省略，加上可讀性就強一些，表示外連接。

4、多表連接

• 語法：

  select ...
  from
  	a
  join
  	b
  on
  	a和b的連接條件
  join
  	c
  on
  	a和c的連接條件
  join
  	d
  on
  	a和d的連接條件
  ...
  

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

  實例：

  #找出每個員工的部門名稱以及工資等級，還有上級領導，要求顯示出員工名、領導名、部門名、薪資、薪資等級
  select e.ename, l.ename, e.sal, 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
  left join emp l on e.mgr=l.empno;
  

（三）子查詢

• 子查詢：select語句中嵌套select語句，配嵌套的select語句稱為子查詢。

在where語句中使用子查詢

實例：

# 找出比最低工資高的員工名字和工資
select ename，sal
from emp
where sal > (select min(sal) from emp);

在from語句中使用子查詢

• from後面的子查詢，可以將子查詢的查詢結果當作一張臨時表。

實例：

#找出每個崗位的平均工資的薪資等級
select t.*, s.grade 
from (select job, avg(sal) as avgsal from emp group by job) t
on t.avgsal between s.losal and s.hisal; #