MySQL資料庫學習筆記
資料庫：按照資料結構來組織、儲存和管理資料的倉庫。是一個長期儲存在計算機內的、有組織的、有共享的、統一管理的資料集合。
資料庫的分類：
網狀結構資料庫：美國通用電氣公司IDS，以節點形式儲存和訪問。
層次結構資料庫：IBM公司IMS定向有序的樹狀結構實現儲存和訪問。
關係結構資料庫：Oracle、DB2、MySQL、SQL server，以表格（table）儲存，多表間簡歷關聯關係，通過分類、合併、連線、選取等運算實現訪問。
非關係型資料庫：ElastecSearch、MongoDB、Redis，多數使用雜湊表，表中以鍵值(key-value)的方式實現特地的鍵和一個指標指向的特定資料。
資料庫管理系統：指一種操作和管理資料庫的大型軟體，用於建立、使用和維護資料庫，對資料庫進行統一管理和控制，以保證資料庫的安全性和完整性。使用者通過資料庫管理系統訪問資料庫中的資料。
常見資料庫管理系統：
Oracle：被認為是業界目前比較成功的關係型資料庫管理系統，Oracle資料庫可以執行在UNIX、Windows等主流作業系統平臺，完全支援所有的工業標準，並獲得最高階別的ISO標準安全性認證。
DB2:IBM公司的產品，DB2資料庫系統採用多程式多線索體系結構，其功能足以滿足大中公司的需要，並可靈活得服務與中小型電子商務解決方案。
SQL Server：Microsoft公司推出的關係型資料庫管理系統。具有使用方便可伸縮好與相關軟體整合度高等優點。
SQLLite：應用在手機端的資料庫。
MySQL：MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB公司開發，屬於Oracle旗下產品。MySQL是最流行的關係型資料庫管理系統之一。
一、MySQL資料庫
1、MySQL目錄結構
bin：可執行的命令檔案
lib：庫檔案
include：標頭檔案
share：字符集、語言等資訊
2、MySQL配置檔案
在MySQL安裝目錄中找到my.ini檔案，並開啟my.ini檔案檢視幾個常用配置引數
default-character-set= 客戶端預設字符集
character-set-server= 服務端預設字符集
port 客戶端和伺服器端的埠號
default-storage-engine=INNODB MySQL預設儲存引擎INNODB
3、SQL語言
3.1 概念
SQL結構化查詢語言，用於存取資料、更新、查詢和管理資料庫系統的程式設計語言。
（經驗:通常執行對資料庫的“增刪改查”，簡稱C(Create)R(Read)U(Update)D(delete)。）
3.2 MySQL應用
對於資料庫的操作，需要在MySQL環境下進行指令輸入，並在一句指令的末尾使用;結束。
3.3 基本命令
建立自定義資料庫
mysql> create database mydb1; 建立mydb1資料庫
mysql> create database mydb2 character set gbk; 建立資料庫並設定編碼位gbk
mysql> create database if not exists mydb3; 如果mydb3資料庫不存在，建立mydb3資料庫
檢視資料庫建立資訊
mysql> show create database mydb2;檢視建立資料庫時的基本資訊
修改資料庫
mysql> alter database mydb2 character set utf8;
刪除資料庫
mysql> drop database mydb2; 刪除資料庫mydb2；
檢視當前所使用的資料庫
mysql> select database();
使用資料庫
mysql> use mydb3; 使用mydb3資料庫
4、客戶端工具
Navicate
Navicate是一套快速、可靠並價格相宜的資料庫管理工具，轉為簡化資料庫的管理及降低系統管理成本而設。
它的設計符合資料庫管理員、開發人員及中小企業的需要。Navicate是以直覺化的圖形使用者介面而建的，讓你
可以以安全並且簡單的方式建立、組織、訪問並共用資訊。
SQLyog
MySQL可能是世界上最流行的開源資料庫引擎，但是使用基於文字的工具和配置檔案可能很難進行管理。
SQLyog提供了完整的圖形介面，即使初學者也可以輕鬆使用MySQL的強大功能。其擁有廣泛的預定於工具
和查詢、友好的視覺介面、類似Excel的查詢結果編輯介面等優點。
5、資料查詢
5.1 資料庫表的基本結構
關係型資料庫是以表格(table)進行資料儲存，表格由行和列組成
經驗：執行查詢語句返回的結果集是一張虛擬表。
5.2 基本查詢
語法：SELECT 列名 FROM 表名
SELECT 指定要查詢的列
FROM 指定要查詢的表
5.2.1 查詢部分列
#查詢員工表中所有員工的編號、名字、郵箱
SELECT employee_id,first_name,email FROM t_employees;
5.2.2 查詢所有列
#查詢員工表中所有員工的所有資訊（所有列）
SELECT 所有列的列名 FROM t_employees;
SELECT * FROM t_employees;
注意：生產環境下，優先使用列名查詢。*的方式需轉換成全列名，效率低，可讀性差。
5.2.3 對列中的資料進行運算
#查詢員工表中所有員工的編號、姓名、年薪
SELECT employee_id,first_name,salary*12 FROM t_employees;
算數運算子 描述
+ 兩列做加法運算
- 兩列做減法運算
* 兩列做乘法運算
/ 兩列做除法運算
注意：%是佔位符，而非模運算子
5.2.4 列的別名
#查詢員工表中所有員工的編號、名字、年薪（別名均為英文）
SELECT employee_id as "編號", first_name as "名字" , salary*12 as "年薪"
FROM t_employees;
5.2.5 查詢結果去重
#查詢員工表中所有經理的ID
SELECT distinct manager_id FROM t_employees;
5.3 排序查詢
語法：SELECT 列名 FROM 表名 ORDER BY 排序列 [排序規則]
排序規則 描述
ASC 對前面排序做升序排序
desc 對前面排序做降序排序
5.3.1 依據單列排序
查詢員工的編號、名字、薪資。按照工資高低進行排序
SELECT employee_id,first_name,salary FROM t_employees ORDER BY salary+0 DESC;
5.3.2 依據多列排序
查詢員工的編號，名字，薪資。按照工資高低進行升序排序（薪資相同時，按照編號進行升序排序。）
SELECT employee_id,first_name,salary FROM t_employees ORDER BY salary+0 DESC,
employee_id ASC;
5.4 條件查詢
語法：SELECT 列名 FROM 表名 WHERE 條件
關鍵字 描述
WHERE條件 在查詢結果中，篩選符合條件的查詢結果，條件為布林表示式
5.4.1 等值判斷（=）
查詢薪資是11000的員工資訊（編號、名字、薪資）
SELECT employee_id,first_name,salary FROM t_employees WHERE salary=11000;
注意：與java不同（==），mysql中等值判斷使用=
5.4.2 邏輯判斷（and、or、not）
查詢薪資是11000並且提成是0.30的員工資訊（編號、名字、薪資）
SELECT employee_id , first_name , salary FROM t_employees WHERE salary=11000 AND
commission_pct=0.30;
5.4.3 不等值判斷(>、<、>=、<=、!=、<>)
#查詢員工的薪資在6000~10000之間的員工資訊（編號，名字，薪資）
SELECT employee_id , first_name , salary FROM t_employees WHERE salary >= 6000 AND
salary <= 10000;
5.4.4 區間判斷（between and）
#查詢員工的薪資在6000~10000之間的員工資訊（編號，名字，薪資）
SELECT employee_id , first_name , salary FROM t_employees WHERE salary between 6000
and 10000;
注：在區間判斷語句中，小值在前，大值在後，反之則得不到結果
5.4.5 NULL值判斷（IS NULL、IS NOT NULL）
IS NULL
列名 IS NULL
IS NOT NULL
列名 IS NOT NULL
#查詢沒有提成的員工資訊（編號、名字、薪資、提成）
SELECT employee_id , first_name , salary, commission_pctFROM t_employees WHERE
commission_pct IS NULL;
5.4.6 列舉查詢（IN（值1，值2，值3））
#查詢部門編號為70、80、90的員工資訊（編號、名字、薪資、部門資訊）
SELECT employee_id , first_name , salary, department_idFROM t_employees WHERE
department_id IN (70,80,90);
5.4.7 模糊查詢
LIKE _ (單個任意字元)
列名 LIKE ‘張_’
LIKE % (任意長度的任意字元)
列名 LIKE '張%'
注意：模糊查詢只能和LIKE關鍵字結合使用
#查詢名字以‘L’開頭的員工資訊（名字、編號、薪資、部門資訊）、
SELECT employee_id , first_name , salary, department_idFROM t_employees WHERE
first_name LIKE 'L%';
#查詢名字以'L'開頭並且長度為4的員工資訊（編號，名字，薪資，部門資訊）
SELECT employee_id , first_name , salary, department_idFROM t_employees WHERE
first_name LIKE 'L___';
5.4.8 分支結構查詢
CASE
WHEN 條件1 THEN 結果1
WHEN 條件2 THEN 結果2
WHEN 條件3 THEN 結果3
ELSE 結果
END
注意：通過使用CASE END 進行條件判斷，每條資料對應生成一個值。
經驗：類似Java中的switch
#查詢員工資訊（編號、名字、薪資、薪資級別（對應條件表示式生成））
SELECT employee_id , first_name , salary,
CASE
WHEN salary>=10000 THEN 'A'
WHEN salary>=8000 AND salary<10000 THEN 'B'
WHEN salary>=6000 AND salary<8000 THEN 'C'
WHEN salary>=4000 AND salary<6000 THEN 'D'
ELSE '不及格'
END AS '級別'
FROM t_employees ;
5.5 時間查詢
語法：SELECT 時間函式（[引數列表]）
經驗：執行時間函式查詢，會自動生成一張虛表（一行一列）
函式 語法
SYSDATE() 當前系統時間(年、月、日、時、分、秒)
CURDATE() 獲取當前日期
CURTIME() 獲取當前時間
WEEK() 獲取指定日期為一年中的第幾周
YEAR() 獲取指定日期的年份
HOUR() 獲取指定時間的小時值
MINUTE() 獲取指定時間的分鐘值
DATEDIFF(DATE1,DATE2) 獲取DATE1和DATE2之間相隔的天數
ADDDATE(DATE,N) 計算DATE加上N天之後的日期
5.5.1 獲得當前系統時間
#查詢當前時間
SELECT SYSDATE();
#查詢當前時間
SELECT NOW();
#獲取當前日期
SELECT CURDATE();
#獲取當前時間
SELECT CURTIME();
5.6 字串查詢
語法： SELECT 字串函式 （[引數列表]）
字串函式 說明
CONCAT(str1,str2,str3...) 將多個字串連線
INSERT(str,pos,len,newStr) 將str中指定pos位置開始len長度的內容替換為newStr
LOWER(str) 將指定字串轉換為小寫
UPPER（str） 將指定字串轉換為大寫
SUBSTRING(str,num,len) 將str字串指定num位置開始擷取len個內容
5.6.1 字串應用
#拼接內容
SELECT CONCAT(first_name,last_name) AS '姓名' FROM t_employees;
#字串替換
SELECT INSERT("這是mysql資料庫",3,5,'一個') AS '內容';
#字串轉小寫
SELECT LOWER('MYSQL');
#字串轉大寫
SELECT UPPER('mysql');
#指定內容擷取
SELECT SUBSTRING('javamysqloracle',5,5);
5.7 聚合函式
語法： SELECT 聚合函式（列名）FROM 表名;
經驗：對多條資料的單列進行統計，返回統計後的一行結果。
聚合函式 說明
SUM() 求所有行中單列結果的總和
AVG() 平均值
MAX() 最大值
MIN() 最小值
COUNT() 求總行數
#求單列所有資料的和
SELECT SUM(salary) FROM t_employees;
#求單列所有資料的平均值
SELECT AVG(salary) FROM t_employees;
#求單列所有資料的最大值
SELECT MAX(salary) FROM t_employees;
#求單列所有資料的最小值
SELECT MIN(salary) FROM t_employees;
#求總行數 員工總數
SELECT COUNT(salary) FROM t_employees;
#統計有提成的人數 會自動忽略NULL
SELECT COUNT(COMMISSION_PCT) FROM t_employees;
注意：聚合函式自動忽略NULL值，不進行統計。
5.8 分組查詢
語法： SELECT 列名 FROM 表名 WHERE 條件 GROUP BY 分組依據（列）;
關鍵字 說明
GROUP BY 分組依據，必須在where後生效
5.8.1 查詢各部門的總人數
#思路
#1、按照部門編號進行分組（分組依據是departmen_id）
#2、再針對各部門的人數進行統計（count）
SELECT department_id,COUNT(employee_id)
FROM t_employees
GROUP BY department_id;
5.8.2 查詢各部門的平均工資
#思路
#按照部門編號進行分組（分組依據是departmen_id）
#針對每個部門進行平均工資統計
SELECT department_id,AVG(salary)
FROM t_employees
GROUP BY department_id;
5.8.3 查詢各個部門、各個崗位的人數
#思路
#按照部門編號進行分組（分組依據是departmen_id）
#按照崗位名稱進行分組（分組依據是job_id）
#針對每個部門中的每個崗位進行人數統計（count）
SELECT department_id,job_id,COUNT(employee_id)
FROM t_employees
GROUP BY department_id,job_id;
5.8.4 常見問題（錯誤示範）
#查詢各個部門id、總人數、first_name
SELECT department_id,COUNT(*),first_name
FROM t_employees
GROUP BY department_id;
注：分組查詢中，select顯示的列只能是分組依據列，或者聚合函式列，不能出現其他列。
5.9 分組過濾規則
語法：SELECT 列名 FROM 表名 WHERE 條件 GROUP BY 分組列 HAVING 過濾規則
關鍵詞 說明
HAVING過濾規則 過濾規則定義對分組後的資料進行過濾
5.9.1 統計部門的最高工資
#統計60、70、90號部門的最高工資
#思路：
#1、確定分組依據（department_id）
#2、對分組後的資料，過濾出部門編號是60、70、90的資訊
#3、max()函式處理
SELECT department_id AS '部門',MAX(salary) AS '工資'
FROM t_employees
GROUP BY department_id
HAVING department_id IN (60,70,90);
#group確定分組依據department_id
#having過濾出60.70.90
#select檢視部門編號和max函式
5.10 限定查詢
SELECT 列名 FROM 表名 LIMIT 起始行，查詢行數
關鍵字 說明
LIMIT offset_start, row_count 限定查詢結果的起始行和總行數
5.10.1 查詢前5行記錄
#查詢表中前5名員工的所有資訊
SELECT * FROM t_employees LIMIT 0,5;
注意：起始行是從0開始，代表了第一行，第二個引數代表的是從指定行開始查詢幾行
5.10.2 查詢範圍記錄
#查詢表中從第四條開始，查詢10行
SELECT * FROM t_employees LIMIT 3,10;
5.10.3 LIMIT典型應用
分頁查詢：一頁顯示10條，一共查詢三頁
#思路：第一頁是從0開始，顯示10條
SELECT * FROM t_employees LIMIT 0,10;
#第二頁是從第十條開始，顯示10條
SELECT * FROM t_employees LIMIT 10,10;
#第三頁是從第二十條開始，顯示10條
SELECT * FROM t_employees LIMIT 20,10;
經驗：在分頁應用場景中，起始行是變化的，但是一頁顯示的條數是不變的
5.11 查詢總結
5.11.1 SQL語句執行順序
SELECT 列名 FROM 表名 WHERE 條件 GROUP BY 分組 HAVING 過濾條件 ORDER BY 排序列
（asc|desc） LIMIT 起始行，總行數
5.11.2 SQL 語句執行順序
1、FROM:指定資料來源表
2、WHERE:對查詢資料做第一次過濾
3、GROUP BY:分組
4、HAVING:對分組後的資料進行第二次過濾
5、SELECT:查詢個欄位的值
6、ORDER BY:排序
7、LIMIT:限定查詢結果
相關文章
- 資料庫mysql學習筆記記錄資料庫MySql筆記
- MySQL學習筆記-使用Navicat操作MySQL資料庫MySql筆記資料庫
- 資料庫學習筆記資料庫筆記
- 資料庫學習筆記 - MySQL基礎知識資料庫筆記MySql
- MySQL學習筆記--資料型別MySql筆記資料型別
- MySQL資料庫學習筆記02（事務控制，資料查詢）MySql資料庫筆記
- Redis學習筆記（七） 資料庫Redis筆記資料庫
- 達夢資料庫學習筆記資料庫筆記
- python學習筆記：資料庫Python筆記資料庫
- 資料庫原理學習筆記——引言資料庫筆記
- MySQL學習筆記12：資料型別MySql筆記資料型別
- swoft 學習筆記之資料庫操作筆記資料庫
- 1029學習筆記 資料庫筆記資料庫
- Java學習筆記--資料庫初識Java筆記資料庫
- 資料庫學習筆記1(資料管理歷史)資料庫筆記
- 資料庫學習筆記——20 使用遊標資料庫筆記
- 資料庫學習筆記之查詢表資料庫筆記
- Activiti學習筆記一：建立資料庫表筆記資料庫
- 08年在大學學習資料庫的筆記資料庫筆記
- 資料庫的正規化學習筆記資料庫筆記
- MySql學習筆記MySql筆記
- MySQL學習筆記之資料型別詳解MySql筆記資料型別
- MySQL資料庫基礎筆記MySql資料庫筆記
- MySQL學習筆記之SQL語句建立、修改和刪除資料庫MySql筆記資料庫
- 資料庫學習與複習筆記--資料庫概念和不同類資料庫CRUD操作(1)資料庫筆記
- 飛機的 PHP 學習筆記八：資料庫PHP筆記資料庫
- 資料庫學習筆記（四）2017.9.19資料庫筆記
- Symfony2學習筆記之資料庫操作筆記資料庫
- 規劃關聯式資料庫學習筆記資料庫筆記
- 【記錄】MySQL 學習筆記MySql筆記
- [記錄] MySQL 學習筆記MySql筆記
- 《Python入門與資料科學庫》學習筆記Python資料科學筆記
- MYSQL學習筆記4: DML資料操作(增刪改)MySql筆記
- MySQL學習筆記2MySql筆記
- MySQL學習筆記：鎖MySql筆記
- MySql學習筆記06MySql筆記
- 學習Mysql筆記（一）MySql筆記
- mysql學習筆記-1MySql筆記