MySQL三部曲之初見端倪

Richard_i發表於2020-11-11

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
什麼是表？

table是資料庫當中的基本組成單元，所有的資料都以表格的形式組織，目的是可讀性強。
一個表包括行和列
行：被稱為資料（data）
列：被稱為欄位（column）

學號(int)	姓名(varchar)	年齡(int)
001	張三	18
002	李四	24
003	王五	16

每一個欄位應該包括哪些屬性？

欄位名、資料型別、相關的約束

學習MySQL主要還是學習通用的SQL語句，SQL語句包括增刪改查，SQL語句是如何分類的？
- DQL（資料查詢語言）：查詢語句，凡是select語句都是DQL。
- DML（資料操作語言）：insert ， delete ， update 對錶當中的資料進行增刪改。
- DDL（資料定義語言）：create，drop ，alter 對錶結構的增刪改。
- TCL（事務控制語言）：commit提交事務，rollback回滾事務、（TCL中的T是Transaction）
- DCL（資料控制語言）：grant授權、revoke撤銷許可權等。
匯入資料（後期練習時使用）
- 第一步：登入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              |
+-----------------------+
```
bjpowernode.sql,這個檔案以sql結尾，這樣的檔案被稱為"sql指令碼"。什麼是sql指令碼？
- 當一個檔案的副檔名是".sql"，並且該檔案中編寫了大量的sql語句，這樣的檔案叫SQL指令碼。
- 注意：直接使用source命令可以執行SQL指令碼。
- SQL指令碼中的資料量太大時無法開啟，請使用source命令完成初始化。
刪除資料庫：drop database bjpowernode;

檢視錶結構

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    |       |	最高薪資
+-------+---------+------+-----+---------+-------+

表中的資料

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

MySQL常用命令？

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

檢視建立表的語句

   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; （統計所有崗位數量，去重）

ROS之初見Hello World
2021-05-21
ROS
MySQL複製之初體驗
2014-07-18
MySql
未來零售模式已開啟，雙11上見端倪
2016-11-09
模式
【實驗】【MySQL】MySQL 5.0 windows版本之初探
2009-04-03
MySqlWindows
[資料庫]MySQL總結三部曲（一）
2019-02-21
資料庫MySql
Swift4 異常處理Try_Catch的使用之初見
2018-10-26
Swift
Promise 之初探
2020-12-21
Promise
Redis之初探
2024-05-29
Redis
SLG新動態：小生態產品現端倪
2021-02-02
php下操作mysql詳解之初級！（物件導向，程式導向）
2017-11-12
PHPMySql物件
MQTT之初體驗
2019-03-07
MQQT
Chrome之初體驗
2011-11-22
Chrome
MySQL-初見
2021-08-12
MySql
mysql常見操作
2018-03-11
MySql
【MySQL】常見的mysql 程式state
2015-02-11
MySql
MySQL 常見錯誤
2019-06-04
MySql
Mysql 常見面試題
2021-11-18
MySql面試題
MySQL常見索引概念
2018-07-09
MySql索引
mysql常見問題
2013-07-17
MySql
MySQL 常見問題
2010-07-20
MySql
Mysql:常見問題
2024-11-06
MySql
synchronized已經不在臃腫了，放下對他的成見之初識輕量級鎖
2022-03-28
synchronized
微服務之初瞭解（一）
2019-07-27
微服務
Flutter之初識iOS外掛
2019-06-12
FlutteriOS
設計模式之初體驗
2020-11-17
設計模式
《JavaScript 闖關記》之初探
2016-10-21
JavaScript
IOS學習之初識KVC
2016-05-17
iOS
IOS學習之初識KVO
2016-05-17
iOS
系統開發之初識
2015-11-18
職場智慧之初入江湖薦
2009-07-15
mysql面試常見題目
2018-05-20
MySql面試
mysql replication常見錯誤
2013-04-11
MySql
three.js之初探骨骼動畫
2020-07-30
JS動畫
Android DataBinding之初體驗
2019-02-21
Android
js筆記一之初識JS
2021-09-09
JS筆記
Spring Ioc之初始化
2018-08-10
Spring
OpenStack入門之初步認識
2016-04-19
Python爬蟲之初體驗
2017-12-13
Python爬蟲

MySQL三部曲之初見端倪

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

條件查詢

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

排序（升序、降序）Order by

分組函式

單行處理函式

Group by || Having

去重 distinct

相關文章