MySQL三部曲之初見端倪

Richard_i發表於2020-11-11
MySql

  1. sql、DB、DBMS分別是什麼，他們之間的關係？

    • DB：DataBase（資料庫，資料庫實際上在硬碟上以檔案的形式存在）

    • DBMS：DataBase Management System（資料庫管理系統，常見的有：MySQL Oracle DB2 Sybase SqlServer…）

    • SQL：結構化查詢語言，是一門標準通用的語言，標準的sql適合於所有資料庫產品。
      SQL屬於高階語言，只要能看懂英語單詞，寫出來的sql語句，可以讀懂什麼意思。
      SQL語句在執行的時候，實際上內部也會先進行編譯，然後再試下SQL（SQL語句的編譯由DBMS完成）

    • DBMS負責執行SQL語句，通過執行SQL語句來操作DB當中的資料。

    • DBMS–>(執行)-> SQl(操作)-> DB

  2. 什麼是表？

  • table是資料庫當中的基本組成單元，所有的資料都以表格的形式組織，目的是可讀性強。

  • 一個表包括行和列
    行：被稱為資料（data）
    列：被稱為欄位（column）

學號(int)姓名(varchar)年齡(int)
001張三18
002李四24
003王五16
  • 每一個欄位應該包括哪些屬性？
  • 欄位名、資料型別、相關的約束

  1. 學習MySQL主要還是學習通用的SQL語句，SQL語句包括增刪改查，SQL語句是如何分類的？

    • DQL（資料查詢語言）： 查詢語句，凡是select語句都是DQL。
    • DML（資料操作語言）：insert ， delete ， update 對錶當中的資料進行增刪改。
    • DDL（資料定義語言）：create，drop ，alter 對錶結構的增刪改。
    • TCL（事務控制語言）：commit提交事務，rollback回滾事務、（TCL中的T是Transaction）
    • DCL（資料控制語言）：grant授權、revoke撤銷許可權等。

  2. 匯入資料（後期練習時使用）

    • 第一步：登入MySQL資料庫 root

    • 第二步：檢視有哪些資料庫

      • show database（這個不是SQL語句，屬於MySQL的命令）

        mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

    • 第三步：建立屬於自己的資料庫

      • create database bjpowernode; （這個不是SQL語句，屬於MySQL的命令）

    • 第四步：使用***bjpowernode***資料庫

      • use bjpowernode;（這個不是SQL語句，屬於MySQL的命令）

    • 第五步：檢視資料庫中有哪些表？

      • show tables;（這個不是SQL語句，屬於MySQL的命令）

    • 第六步：初始化資料

      • mysql> source ( | source ) —> 檔案路徑
      • mysql> source
      • 注意：資料初始化完成之後，有三張表
      mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |
| emp                   |
| salgrade              |
+-----------------------+

  3. bjpowernode.sql,這個檔案以sql結尾，這樣的檔案被稱為"sql指令碼"。什麼是sql指令碼？

    • 當一個檔案的副檔名是".sql"，並且該檔案中編寫了大量的sql語句，這樣的檔案叫SQL指令碼。
    • 注意：直接使用source命令可以執行SQL指令碼。
    • SQL指令碼中的資料量太大時無法開啟，請使用source命令完成初始化。

  4. 刪除資料庫：drop database bjpowernode;

  5. 檢視錶結構

    mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |	(部門表)
| emp                   |	(員工表)
| salgrade              |	(工資登記表)
+-----------------------+

    mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |	部門編號
| DNAME  | varchar(14) | YES  |     | NULL    |       |	部門名稱
| LOC    | varchar(13) | YES  |     | NULL    |       |	部門位置
+--------+-------------+------+-----+---------+-------+

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |	員工編號
| ENAME    | varchar(10) | YES  |     | NULL    |       |	員工姓名
| JOB      | varchar(9)  | YES  |     | NULL    |       |	工作崗位
| MGR      | int(4)      | YES  |     | NULL    |       |	上級領導編號
| HIREDATE | date        | YES  |     | NULL    |       |	入職日期
| SAL      | double(7,2) | YES  |     | NULL    |       |	月薪
| COMM     | double(7,2) | YES  |     | NULL    |       |	補助/津貼
| DEPTNO   | int(2)      | YES  |     | NULL    |       |	部門編號
+----------+-------------+------+-----+---------+-------+

mysql> desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| GRADE | int(11) | YES  |     | NULL    |       |	等級
| LOSAL | int(11) | YES  |     | NULL    |       |	最低薪資
| HISAL | int(11) | YES  |     | NULL    |       |	最高薪資
+-------+---------+------+-----+---------+-------+

  6. 表中的資料

    mysql> select * from dept;
mysql> select * from emp;
mysql> select * from salgrade;

  7. MySQL常用命令？

    select database();	檢視當前正在使用的資料庫
select version();	檢視mysql的版本號
\c	命令，結束一條語句
exit	命令，退出MySQL

  8. 檢視建立表的語句

   mysql> show create table emp;
   +-------+-------------------------------
   | Table | Create Table
   +-------+-------------------------------
   | emp   | CREATE TABLE `emp` (
     `EMPNO` int(4) NOT NULL,
     `ENAME` varchar(10) DEFAULT NULL,
     `JOB` varchar(9) DEFAULT NULL,
     `MGR` int(4) DEFAULT NULL,
     `HIREDATE` date DEFAULT NULL,
     `SAL` double(7,2) DEFAULT NULL,
     `COMM` double(7,2) DEFAULT NULL,
     `DEPTNO` int(2) DEFAULT NULL,
     PRIMARY KEY (`EMPNO`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
   +-------+-------------------------------

簡單的查詢（DQL資料查詢）

  • 語法格式：select 欄位名1, 欄位名2, 欄位名3,… form 表名;
  • 任何一條SQL語句以";"結尾。
  • SQL與極具不區分大小寫
  • 注意：標準SQL語句中要求字串使用單引號括起來，雖然MySQL支援雙引號，儘量別用。
   查詢員工的年薪
   mysql> select ename,empno,sal * 12 from emp; (欄位可以參與數學運算)
   mysql> select ename,empno,sal * 12 as yearsal from emp;（給查詢結果的列重新命名 ‘as’ 關鍵字）
   mysql> select ename,empno,sal * 12 yearsal from emp;（‘as’ 關鍵字可以省略）
   mysql> select ename,empno,sal * 12 as '年薪' from emp; （別名中有中文，單引號括起來）
   mysql> select * from emp; （查詢全部欄位）
   實際開發中不建議使用 * ，效率較低。

條件查詢

  • 語法格式：select 欄位，欄位… from 表名 where 條件；執行順序： from --> where --> select
  • between…and…在使用時必須左小右大
  • between…and…也可以使用在字串方面
    mysql> select ename,sal from emp where sal = 3000; （查詢薪資等於3000的員工）
    mysql> select ename,sal from emp where sal <> 3000; （不等於3000，也可以用 != ）
    mysql> select ename,sal from emp where ename = 'smith'; （查詢員工姓名為Smith的工資）
    mysql> select ename,sal from emp where sal >= 1500 and sal <= 5000;(1500~5000薪資的員工)
    mysql> select ename,sal from emp where sal between 100 and 3100;（between...and...是閉區間）
    between..and..在使用時必須左小右大
    mysql> select ename from emp where ename between 'A' and 'D'; （左閉右開）

is null (is not null) , and , or , in，not in , like

  • 在資料庫當中null不是一個值，代表什麼也沒有，為空。
  • 空不是一個值，不能用等號衡量。
  • 必須使用 is null 或者 is not null
  • 注意：當運算子的優先順序不確定的時候加小括號。
mysql> select ename,sal,comm from emp where comm is null or comm = 0;(找出無津貼員工)
    mysql> select ename,sal,comm from emp where comm is not null;（找出有津貼員工）

(找出工作崗位是salasman和manager的員工)
mysql> select ename,job,hiredate from emp where job='salesman' or job ='manager';
（and和or聯合起來使用：找出薪資大於1000的並且部門編號是20或者30部門的員工）
mysql> select ename,sal,deptno from emp where sal > 1000 and deptno = 20 or deptno =30; //錯誤
mysql> select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno =30); // 正確
  • in 等同於 or，找出工作崗位是salasman和manager的員工(in後面的值不是區間，是具體的值)
mysql> select ename,job,hiredate from emp where job='salesman' or job ='manager';
mysql> select ename,sal,job from emp where job in ('salesman','manager');
mysql> select ename,sal from emp where sal in (800, 3000); //in後面的值不是區間，是具體的值
  • not in : 不在這幾個值當中
mysql> select ename, sal from emp where sal not in(800,5000); (查詢不是800，5000的值)

  • 模糊查詢 like

    • 在模糊查詢中，必須掌握的兩個特殊的符號，一個是 % ，一個是 _
    • % 代表任意多個字元， _ 代表任意一個字元。
    mysql> select ename from emp where ename like '%A%'; (找出名字中含有A字母的)
mysql> select ename from emp where ename like '_A%'; (找出第二個字母是A的)
(找出名字中有下劃線的，可以使用反斜槓轉義，表示一個普通的下劃線)
mysql> select ename from emp where	 ename like '%\_%';
(找出最後一個字母是S的名字)
mysql> select ename from emp where ename like '%S';

排序（升序、降序）Order by

  • —> 預設是升序

  • 指定升降序，asc 表示升序，desc 表示降序

    mysql> select ename , sal from emp order by sal; （預設升序）

mysql> select ename ,sal from emp order by sal desc; (指定降序)

mysql> select ename ,sal from emp order by sal asc; (指定升序)

（按照工資的降序排列，當工資相同的時候再按照名字的升序排列）
mysql> select ename , sal from emp order by sal desc , ename asc;
(找出工作崗位是SALESMAN的員工，並且要求按照薪資的降序排列)
mysql> select ename,sal,job from emp
    -> where job = 'salesman'
    -> order by sal desc;
    
    執行順序  from---> where ---> select ---> order by...
    • 注意：越靠前的欄位越能起到主導作用，只有當前面的欄位無法完成排序的時候，才會啟用後面的欄位。

分組函式

  • count（計數），sum（求和）， avg（平均值）， max（最大值）， min（最小值）
    • 所有的分組函式都是對“某一組”資料進行操作的
    • 分組函式又稱：多行處理函式
    • 多行處理函式特點：輸入多行，最終輸出的結果是一行。
    • 分組函式有一個特點：自動忽略null。
    • 在資料庫中只要有null進行了運算，最後結果一定是null，所有資料庫都是這樣規定的
    • 分組函式不可直接使用在where子句當中
    • 分組函式可以組合使用
mysql> select sum(sal) from emp;  （找出工資總和）
mysql> select sum(sal) from emp;   （平均工資）
...
（找出工資高於平均工資的員工）
mysql> select ename,sal from emp where sal > avg(sal);
ERROR 1111 (HY000): Invalid use of group function
錯誤：無效的使用的了分組函式
mysql> select ename, sal  from emp where sal > (select avg(sal) from emp);
	因：SQL語句當中有一個語法規則，分組函式不可直接使用在where子句當中。why?
	怎麼解釋？
		因為 group by 是在 where 執行之後才會執行的（分組函式用不了，是因為還沒有分組）
		而分組函式必須要 group by 分組才能執行
		select...	5.選擇資料
		from...		1.先從表取出資料
		where...	2.條件過濾，哪個資料
		group by...	3.資料分組
		having...	4.資料過濾
		order by... 6.排序輸出
count (*) 和 count(具體欄位)有什麼區別？
count(*) : 不是統計某個欄位中資料的個數，而是統計總記錄條數。（和某個欄位無關）
count(comm) : 表示統計comm欄位中不為null的資料總數量。

分組函式可以組合使用
mysql> select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
+----------+----------+-------------+----------+----------+
| count(*) | sum(sal) | avg(sal)    | max(sal) | min(sal) |
+----------+----------+-------------+----------+----------+
|       14 | 29025.00 | 2073.214286 |  5000.00 |   800.00 |
+----------+----------+-------------+----------+----------+
1 row in set (0.00 sec)

單行處理函式

  • 輸入一行，輸出一行
  • ifnull(comm,0) —> 如果comm為null，則使其等同於0。
  • ifnull(可能為null的資料，被當做什麼處理) 空處理函式
計算每個員工的年薪？
mysql> select ename, (sal+comm) * 12 yearsal from emp;  // comm為null值時，員工薪資為null，不正確
使用ifnull函式;
mysql> select ename, (sal + ifnull(comm,0)) * 12 yearsal from emp;

Group by || Having

  • group by ：按照某個欄位或者某些欄位進行分組；

    • 注意：分組函式一般都會和 group by 聯合使用，這也是為什麼被稱為分組函式的原因。
    • 任何一個分組函式(count，sum，avg，max，min)都是在group by 語句執行結束後才執行的。
    • 當一條SQL語句沒有group by的話，整張表的資料會自成一組。
    • 記住：當一條語句中有 group by 的話，select 後面只能跟分組函式和參與分組的欄位。
    • 找出每個工作崗位的最高薪資；
    mysql> select job,max(sal) from emp group by job;	//找出每個工作崗位的最高薪資；
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| ANALYST   |  3000.00 |
| CLERK     |  1300.00 |
| MANAGER   |  2975.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  1600.00 |
+-----------+----------+

mysql> select job,max(sal) from emp group by job;
以上在mysql當中，有查詢結果，但結果沒有意義，在oracle資料庫中會報錯，語法錯誤。
oracle的語法規則比MySQL語法規則嚴謹。
記住：當一條語句中有 group by 的話，select 後面只能跟分組函式和參與分組的欄位。

找出每個工作崗位的平均薪資。
mysql> select job, avg(sal) from emp group by job;

多個欄位能不能聯合起來一塊分組？
案例：找出每個不同部門不同工作崗位的最高薪資；
mysql> select deptno,job,max(sal) from emp group by deptno,job;

    • having：對分組之後的資料進行再次過濾；

      • 找出每個部門的最高薪資，要求顯示薪資大於2900的資料。
        • sql語句一般多層邏輯建議拆分思考編寫
    mysql> select deptno, max(sal) from emp group by deptno;（找出每個部門的最高薪資）
    +--------+----------+
    | deptno | max(sal) |
    +--------+----------+
    |     10 |  5000.00 |
    |     20 |  3000.00 |
    |     30 |  2850.00 |
    +--------+----------+
    （找出每個部門的最高薪資，要求顯示薪資大於2900的資料）
    //這種方式效率低,因前面算出的最大值，後面又刪掉
    select deptno, max(sal) from emp group by deptno having max(sal) > 2900; 
    +--------+----------+
    | deptno | max(sal) |
    +--------+----------+
    |     10 |  5000.00 |
    |     20 |  3000.00 |
    +--------+----------+
    // 效率較高， where語句，可以從一開始就過濾掉2900以下的
    mysql> select deptno,max(sal) from emp where sal > 2900 group by deptno;
    +--------+----------+
    | deptno | max(sal) |
    +--------+----------+
    |     10 |  5000.00 |
    |     20 |  3000.00 |
    +--------+----------+
    2 rows in set (0.00 sec)
    
    （找出每個部門的平均薪資，要求顯示薪資大於2900的資料）

去重 distinct

  • distinct 只能出現在所有欄位的最前面
mysql> select distinct deptno,job from emp;      (聯合去重多個欄位)
mysql> select count(distinct job),job from emp; （統計所有崗位數量，去重）

相關文章