2019大部分同學只是理論上知道tomcat幾個目錄的作用，具體需要在項目實踐中慢慢體會 本學期需要掌握的內容： 基礎知識的：Mysql數據庫、Maven、JDBC基礎、DBUtils、JDBC項目。 跑得快的同學：另外JavaWeb、SSM、SpringBoot快速入門 本學期學習目標： 最起碼的：能夠獨立開發一個使用Mysql的Java B/S管理系統，能夠對數據進行增刪改查操作。 跑得快的同學：不僅能使用B/S，還能使用JavaWeb、SSM、SpringBoot完成B/S項目開發。畢竟JavaEE才是Java的主力。 MySQL版本選擇： 主要有兩個版本： * [8.0](https://dev.mysql.com/downloads/mysql/8.0.html) * [5.7](https://dev.mysql.com/downloads/mysql/5.7.html) * Mysql8發生了很大改變，以致于代碼層次都需要相應變化，不過性能有很大提升。 # MySQL8.0.11 MSI版 安裝圖解說明 官方下載地址：https://dev.mysql.com/downloads/windows/installer/ 第一步：  第二步：  下載完成，點擊打開 mysql-installer-community-8.0.11.0                   在使用Navicat連接數據庫的時候可能會報錯誤  上網搜索解決方案，網上說出現這種情況的原因是：mysql8 之前的版本中加密規則是mysql_native_password,而在mysql8之后,加密規則是caching_sha2_password,  2.然后輸入 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; #更新一下用戶的密碼，password為自定義密碼 FLUSH PRIVILEGES; #刷新權限 （溫馨提示：ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; #這行命令實際上應該為設置密碼永不過期，而不是修改加密規則，具體可參考下方截圖）  **3.輸入剛剛修改的密碼，再次測試連接，驚喜地發現連接成功**  解決問題方法有兩種： 方法1.升級navicat驅動； 方法2.把mysql用戶登錄密碼還原成mysql_native_password. 這里采用方法2解決，具體操作步驟如下： 1.打開命令行小黑屏，進入MySQL的bin目錄，然后輸入mysql -u root -p，輸入密碼 ### Mysql安裝教程 Mysql到現在已經有很多種安裝方式，選擇一種安裝即可，Mysql也朝向“默認大于配置”的方向發展，安裝越來越便利。 安裝前請閱讀一遍教程，安裝過程中請確保每一步進行成功，不要盲目下一步。如果出現問題，很難排查，建議重裝。 #### 1) 安裝 mysql安裝方式有如下幾種： 這里也收集了一些不錯的安裝教程： 1. MSI版本安裝 MySQL8.0.11 MSI版 安裝圖解說明 2. ZIP版本安裝 [mysql-8.0.11-winx64.zip安裝教程詳解](https://blog.csdn.net/zbajie001/article/details/80407944)[官方指南](https://dev.mysql.com/doc/refman/en/windows-start-service.html) 3. brew安裝（mac） 若不考慮版本直接執行命令`brew install mysql`。 **重要：安裝后請詳細查看輸出信息，這里會告訴你怎么啟動mysql，以及一些其他說明！！！** ~~~ # 通常這些輸出信息會告訴你如何啟動mysql。 To have launchd start mysql now and restart at login: brew services start mysql # 設置為自動啟動 Or, if you don't want/need a background service you can just run: mysql.server start # 手動啟動一次 ~~~ 4. scoop安裝（win） 使用[scoop(opens new window)](https://jiayaoo3o.github.io/2019/01/30/Windows%E4%B8%8B%E7%9A%84%E8%BD%AF%E4%BB%B6%E7%AE%A1%E7%90%86%E7%A5%9E%E5%99%A8-scoop/)直接安裝mysql:`scoop install mysql`。 **重要：安裝后請詳細查看輸出信息，這里會告訴你怎么啟動mysql，以及一些包含初始化密碼等其他說明！！！** 啟動命令`net start mysql`，也可在服務界面點擊鼠標啟動。 進入后請修改初始化密碼。 5. docker安裝 ~~~ # 拉取 MySQL 鏡像 docker pull mysql:latest # 運行容器 docker run -itd --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql # 參數說明： #-p 3306:3306 ：映射容器服務的 3306 端口到宿主機的 3306 端口，外部主機可以直接通過 宿主機ip:3306 訪問到 MySQL 的服務。 #MYSQL_ROOT_PASSWORD=123456：設置 MySQL 服務 root 用戶的密碼。 ~~~ ##### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#%E9%85%8D%E7%BD%AE%E6%96%87%E4%BB%B6%E8%AF%B4%E6%98%8E)配置文件說明： 關于my.ini或著my.etc配置文件，大部分教程都是直接丟給你，我們以下面這個一一說明。 如果你使用mysql8，則如果不需要修改什么，直接不用配置文件即可，“約定大于配置”。 如果你使用mysql5.7，建議修改字符集相關設置。 ~~~ [mysqld] # 設置3306端口 port=3306 # 設置mysql的安裝目錄 basedir=C:/Users/w/scoop/apps/mysql/current # 設置mysql數據庫的數據的存放目錄 datadir=C:/Users/w/scoop/persist/mysql/data # 允許最大連接數 max_connections=200 # 允許連接失敗的次數。這是為了防止有人從該主機試圖攻擊數據庫系統 max_connect_errors=10 # 服務端使用的字符集默認為UTF8 character-set-server=utf8 # 創建新表時將使用的默認存儲引擎 default-storage-engine=INNODB # 默認使用“mysql_native_password”插件認證 default_authentication_plugin=mysql_native_password [mysql] # 設置mysql客戶端默認字符集 default-character-set=utf8 [client] # 設置mysql客戶端連接服務端時默認使用的端口 port=3306 default-character-set=utf8 ~~~ 上面的配置文件并不是都需要設置！！！ ##### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#%E4%B8%BE%E4%BE%8B)舉例： 我們以mysql zip 5.7版本舉例說明， 這下面是我使用的配置文件以及安裝命令。 ~~~ [mysqld] port = 33066 explicit_defaults_for_timestamp=true character-set-server=utf8 [client] default-character-set=utf8 ~~~ ~~~ # 安裝命令 mysqld --initialize --console # 安裝服務命令 mysqld --install mysql57 # 啟動服務命令 net start mysql57 # 停止服務命令 net stop mysql57 # 刪除服務命令 sc delete mysql57 ~~~ Mysql客戶端 客戶端也有很多種。有收費的，像大名鼎鼎的Navicat、Navicat for MySQL、SQLyog等。也有免費，像HeidiSQL、[MySQL Workbench(opens new window)](https://dev.mysql.com/downloads/workbench/)等。 收費的我也不想出錢，也不想在破解道路上斗智斗勇了。 所以選擇免費的可以了。 直接使用[MySQL Workbench(opens new window)](https://dev.mysql.com/downloads/workbench/)，這屬于mysql官方出品，免費，功能強大，缺點英文界面。 #### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_2-%E6%B3%A8%E6%84%8F%E4%BA%8B%E9%A1%B9)2) 注意事項 1. windows下需要VC++依賴，如果你沒有安裝，請下載安裝。 Windows下可能提示缺少VC++201X，需要去[微軟網站(opens new window)](https://visualstudio.microsoft.com/zh-hans/downloads)進行下載，在網頁下面，不要傻乎乎的把VS2019下下來了。 2. 密碼沒有顯示或者沒看到。 在安裝目錄data文件中的.err文件中可以找到初始密碼。對于scoop來說是scoop的persist\\MySQL路徑下。 #### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_3-%E4%BD%BF%E7%94%A8%E8%AF%B4%E6%98%8E)3) 使用說明 1. 連接的驅動地址為"com.mysql.cj.jdbc.Driver" Mysql和mysql-connector都為8.0以上的版本時， 連接驅動就 不要再用: com.mysql.jdbc.Driver 要引用: com.mysql.cj.jdbc.Driver 2. 建立連接時url的一些參數 jdbc:mysql://localhost:3306/，這個東西后面跟的是數據庫的名字，8.0.X以上的版本還需在數據庫名后面加上這些東西 ?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true ### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_1-2-%E4%B9%B1%E7%A0%81%E4%B9%8B%E8%B0%9C)1.2. 亂碼之謎 一般由下面原因： 1）客戶機沒有正確地設置client字符集，導致原先的SQL語句被轉換成connection所指字符集，而這種轉換，是會丟失信息的，如果client是utf8格式，那么如果轉換成gb2312格式，這其中必定會丟失信息，反之則不會丟失。一定要保證connection的字符集大于client字符集才能保證轉換不丟失信息。 2）數據庫字體沒有設置正確，如果數據庫字體設置不正確，那么connection字符集轉換成database字符集照樣丟失編碼，原因跟上面一樣。 亂碼主要的原因就是編碼字符集不兼容，默認情況下，我們只需要統一使用UTF8就可以了。 #### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_1-%E8%83%8C%E6%99%AF%E7%9F%A5%E8%AF%86)1) 背景知識： 1. 字符集和校驗規則 字符集是一套符合和編碼，校驗規則（collation）是在字符集內用于比較字符的一套規則，即字符集的排序規則。MySQL可以使用對種字符集和檢驗規則來組織字符。 MySQL服務器可以支持多種字符集，在同一臺服務器，同一個數據庫，甚至同一個表的不同字段都可以指定使用不同的字符集。 每種字符集都可能有多種校對規則，并且都有一個默認的校對規則，并且每個校對規則只是針對某個字符集，和其他的字符集么有關系。 在MySQL中，字符集的概念和編碼方案被看做是同義詞，一個字符集是一個轉換表和一個編碼方案的組合。 Unicode（Universal Code）是一種在計算機上使用的字符編碼。Unicode 是為了解決傳統的字符編碼方案的局限而產生的，以滿足跨語言、跨平臺進行文本轉換、處理的要求。 Unicode存在不同的編碼方案，包括Utf-8，Utf-16和Utf-32。Utf表示Unicode Transformation Format。 2. 字符集選擇規則 (1)編譯MySQL 時，指定了一個默認的字符集; (2)安裝MySQL 時，可以在配置文件 (my.cnf) 中指定一個默認的的字符集，如果沒指定，這個值繼承自編譯時指定的; (3)啟動mysqld 時，可以在命令行參數中指定一個默認的的字符集，如果沒指定，這個值繼承自配置文件中的配置,此時character\_set\_server被設定為這個默認的字符集; (4)當創建一個新的數據庫時，除非明確指定，這個數據庫的字符集被缺省設定為character\_set\_server; (5)當選定了一個數據庫時，character\_set\_database被設定為這個數據庫默認的字符集; (6)在這個數據庫里創建一張表時，表默認的字符集被設定為character\_set\_database，也就是這個數據庫默認的字符集; (7)當在表內設置一欄時，除非明確指定，否則此欄缺省的字符集就是表默認的字符集; 3. MySQL數據庫中字符集轉換流程 1、MySQL Server收到請求時將請求數據從character\_set\_client轉換為character\_set\_connection； 2、進行內部操作前將請求數據從character\_set\_connection轉換為內部操作字符集，其確定方法如下： 使用每個數據字段的CHARACTER SET設定值； 若上述值不存在，則使用對應數據表的DEFAULT CHARACTER SET設定值(MySQL擴展，非SQL標準)； 若上述值不存在，則使用對應數據庫的DEFAULT CHARACTER SET設定值； 若上述值不存在，則使用character\_set\_server設定值。 3、將操作結果從內部操作字符集轉換為character\_set\_results。  #### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_2-%E5%B7%A5%E5%85%B7)2) 工具： 1. 查看當前數據庫的字符集命令：`show variables like '%char%';`這個命令會查看數據庫元數據的編碼信息，如果你看到存在latin1，則需要修改配置。  變量名解釋： character\_set\_client：客戶端請求數據的字符集 character\_set\_connection：客戶機/服務器連接的字符集 character\_set\_database：默認數據庫的字符集這個變量建議由系統自己管理，不要人為定義。 character\_set\_filesystem：把os上文件名轉化成此字符集，即把 character\_set\_client轉換character\_set\_filesystem， 默認binary是不做任何轉換的character\_set\_results：結果集，返回給客戶端的字符集 character\_set\_server：數據庫服務器的默認字符集 character\_set\_system：系統字符集，這個值總是utf8，不需要設置。這個字符集用于數據庫對象（如表和列）的名字，也用于存儲在目錄表中的函數的名字。 2. 查看當前數據庫的校對規則命令：`show variables like 'collation%';`  變量名解釋： collation\_connection 當前連接的字符集。 collation\_database 當前日期的默認校對。每次用USE語句來“跳轉”到另一個數據庫的時候，這個變量的值就會改變。如果沒有當前數據庫，這個變量的值就是collation\_server變量的值。 collation\_server 服務器的默認校對。 排序方式的命名規則為：字符集名字\_語言\_后綴，其中各個典型后綴的含義如下： 1）\_ci：不區分大小寫的排序方式 2）\_cs：區分大小寫的排序方式 3）\_bin：二進制排序方式，大小比較將根據字符編碼，不涉及人類語言，因此\_bin的排序方式不包含人類語言 #### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_3-%E6%9F%A5%E7%9C%8B%E6%95%B0%E6%8D%AE%E5%AD%97%E7%AC%A6%E9%9B%86)3) 查看數據字符集： 1. 確定數據database、table、column字符集是不是正確的： ~~~ SELECT character_set_name, collation_name FROM information_schema.columns WHERE table_schema = your_database_name AND table_name = your_table_name AND column_name = your_column_name; ~~~ 2. 確定數據庫存進去的數據是不是正確的： ~~~ SELECT HEX(column_name) FROM table_name; ~~~ 如果查出來3F，3F是字符'?'的16進制表示，這表明數據庫里儲存的是？。這種情況最常見的原因是在將特定字符從客戶端字符集轉換為目標字符集時出現了問題。 3. 確定往返是可能的。 ~~~ SELECT 'ペ' AS `ペ`; /* or SELECT _ucs2 0x30da; */ ~~~ 如果結果也不為ペ，則往返失敗。 4. 確保問題出在瀏覽器或其他應用程序上，而不是MySQL。 使用mysql自帶的命令行客戶端程序完成上述步驟。如果mysql正確顯示字符，但您的應用程序不能正確顯示，則可能是由于系統設置引起的。 使用SHOW VARIABLES LIKE 'char%';查看系統設置： ~~~ mysql> SHOW VARIABLES LIKE 'char%'; +--------------------------+----------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql/share/mysql/charsets/ | +--------------------------+----------------------------------------+ ~~~ character\_set\_client:我們要告訴服務器，我給你發送的數據是什么編碼？ character\_set\_connection:告訴字符集轉換器，轉換成什么編碼？ character\_set\_results:查詢的結果用什么編碼？ 如果以上三者都為字符集N,可簡寫為set names 'N'; 1. 臨時設置：SET NAMES 'utf8' 2. 永久設置： ~~~ # 修改數據的配置文件my.cnf [mysqld] character-set-server=utf8 [client] default-character-set=utf8 ~~~ * * * #### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_4-%E4%B9%B1%E7%A0%81%E7%BB%88%E6%9E%81%E8%A7%A3%E5%86%B3%E6%96%B9%E6%A1%88)4) 亂碼終極解決方案： 1）首先要明確你的客戶端時候何種編碼格式，這是最重要的，這里我們將代碼開發工具統一使用utf8 2）確保你的數據庫使用utf8格式，很簡單，所有編碼通吃。 3）一定要保證connection字符集大于等于client字符集，不然就會信息丟失，比如： latin1 < gb2312 < gbk < utf8，若設置set character\_set\_client = gb2312，那么至少connection的字符集要大于等于gb2312，否則就會丟失信息 4）以上三步做正確的話，那么所有中文都被正確地轉換成utf8格式存儲進了數據庫，為了適應不同的瀏覽器，不同的客戶端，你可以修改character\_set\_results來以不同的編碼顯示中文字體，由于utf8是大方向，因此web應用是我還是傾向于使用utf8格式顯示中文的。 ### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_1-3-%E5%88%9D%E8%AF%86mysql)1.3. 初識Mysql #### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_1-%E5%8F%82%E8%80%83%E8%B5%84%E6%96%99)1) 參考資料： [官網(opens new window)](https://dev.mysql.com/doc/refman/8.0/en/tutorial.html) [w3school(opens new window)](https://www.w3school.com.cn/sql/sql_syntax.asp) #### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_2-menagerie%E5%8A%A8%E7%89%A9%E5%9B%AD)2) menagerie動物園  1. **連接到服務器和從服務器斷開連接** 使用Navicat或者Workbench * * * 2. **輸入查詢** [SQL Function and Operator Reference(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html) ~~~ SELECT VERSION(), CURRENT_DATE; -- 該查詢說明了有關mysql的幾件事： -- - 查詢通常由一個SQL語句和一個分號組成。 -- - mysql以表格形式（行和列）顯示查詢輸出。第一行包含各列的標簽。下面的行是查詢結果。 -- - mysql顯示返回了多少行以及查詢執行了多長時間，這使您對服務器性能有了一個大概的了解。這些值不精確。 -- 可以在任何大寫字母中輸入關鍵字。 以下查詢是等效的： SELECT VERSION(), CURRENT_DATE; select version(), current_date; SeLeCt vErSiOn(), current_DATE; -- 可以輸入表達式 SELECT SIN(PI()/4), (4+1)*5; SELECT VERSION(); SELECT NOW(); ~~~ * * * 3. **創建和使用數據庫** [CREATE DATABASE Statement(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/create-database.html) * 創建和選擇數據庫 ~~~ -- 創建數據庫DATABASE 和SCHEMA都可以 CREATE DATABASE menagerie; -- 切換數據庫（Workbench需要，Navicat手動點擊即可） USE menagerie ~~~ [CREATE TABLE Statement(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/create-table.html) * 創建表 | 列類型 | 說明 | | --- | --- | | DATE | YYYY-MM-DD | | VARCHAR | 變長字符串 | | CHAR | 字符串 | ~~~ -- 創建表，默認引擎為InnoDB、字符集為utf8mb4 CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); -- 查看 DESCRIBE pet; ~~~ [INSERT Statement(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/insert.html) * 將數據加載到表中 ~~~ -- 使用insert into語句 insert into pet values('Fluffy','Hrold','cat','f','1993-02-04',null); -- 可以插入多條記錄 insert into pet values ('Fluffy','Hrold','cat','f','1993-02-04',null), ('Fluffy','Hrold','cat','f','1993-02-04',null); -- 可以指定插入的列 insert into pet(name) values ('Fluffy'); ~~~ * 練習 將下表中的數據插入創建的pet表中。 | name | owner | species | sex | birth | death | | --- | --- | --- | --- | --- | --- | | Fluffy | Harold | cat | f | 1993-02-04 | | | Claws | Gwen | cat | m | 1994-03-17 | | | Buffy | Harold | dog | f | 1989-05-13 | | | Fang | Benny | dog | m | 1990-08-27 | | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | | Chirpy | Gwen | bird | f | 1998-09-11 | | | Whistler | Gwen | bird | | 1997-12-09 | | | Slim | Benny | snake | m | 1996-04-29 | | * * * 4. **獲取有關數據庫和表的信息** [SELECT Statement(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/select.html) [DELETE Statement(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/delete.html) [UPDATE Statement(opens new window)](https://dev.mysql.com/doc/refman/5.7/en/update.html) * **查詢所有** ~~~ -- 查詢所有 select * from pet; ~~~ * **修改數據** ~~~ -- 至少有兩種方法可以解決此問題 -- 1. 刪除數據重新導入 DELETE FROM pet; -- 2. 使用update UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser'; ~~~ * **選擇特定行** ~~~ -- 如果想驗證對Bowser生日的更改，請選擇這樣的Bowser記錄： SELECT * FROM pet WHERE name = 'Bowser'; -- 查詢在1998年或之后出生的動物 SELECT * FROM pet WHERE birth >= '1998-1-1'; -- 您可以組合條件，例如查找雌狗： SELECT * FROM pet WHERE species = 'dog' AND sex = 'f'; -- 前面的查詢使用AND邏輯運算符。 還有一個OR運算符： SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'; -- AND的優先級高于OR。 如果同時使用這兩個運算符，則最好使用括號來明確指示應如何對條件進行分組： SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f'); ~~~ * **選擇特殊列** ~~~ -- 想知道動物何時出生 SELECT name, birth FROM pet; -- 找出誰養寵物 SELECT owner FROM pet; -- 上面查詢只是從每個記錄中檢索所有者列，并且其中一些出現多次。添加關鍵字DISTINCT去重： SELECT DISTINCT owner FROM pet; -- 可以使用WHERE子句來組合行選擇和列選擇。例如，要僅獲取狗和貓的出生日期，請使用以下查詢： SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat'; ~~~ * **排序行** ~~~ -- 按日期排序 SELECT name, birth FROM pet ORDER BY birth; ~~~ 與所有其他比較操作一樣，在字符類型列上，排序通常以不區分大小寫的方式執行。 這意味著除了大小寫相同以外，其他列均未定義順序。 可以通過使用BINARY強制對列進行區分大小寫的排序，如下所示：ORDER BY BINARY col\_name。 ~~~ -- 默認的排序順序是升序，先是最小值。 要以降序排序，需添加DESC關鍵字到要排序的列的名稱上： SELECT name, birth FROM pet ORDER BY birth DESC; -- 可以在多列上排序 SELECT name, species, birth FROM pet ORDER BY species, birth DESC; -- DESC關鍵字僅適用于緊接其前（birth）的列名； 它不影響species列的排序順序。 ~~~ * **日期計算** MySQL提供了一些函數，可用于對日期執行計算，例如，計算年齡或提取部分日期。 | 常見函數 | 說明 | | --- | --- | | [TIMESTAMPDIFF()(opens new window)](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestampdiff) | 從日期時間表達式中減去一個間隔 | | [DATE\_ADD()(opens new window)](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add) | 將時間值（INTERVAL ）添加到日期值 | | [CURDATE()(opens new window)](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_curdate) | 返回當前日期 | | [MONTH()(opens new window)](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_month) | 返回經過日期的月份 | ~~~ SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet; SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name; SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age; SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age; SELECT name, birth, MONTH(birth) FROM pet; SELECT name, birth FROM pet WHERE MONTH(birth) = 5; SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH)); SELECT '2018-10-31' + INTERVAL 1 DAY; SELECT '2018-10-32' + INTERVAL 1 DAY; SHOW WARNINGS; ~~~ * **使用NULL值** 要測試NULL，請使用IS NULL和IS NOT NULL運算符。不能使用算術比較運算符（例如=，）測試NULL。 ~~~ -- 如何判斷NULL。 SELECT 1 IS NULL, 1 IS NOT NULL; SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; -- 在MySQL中，0或NULL表示false，其他表示true。 布爾運算的默認真值是1。 SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL; ~~~ * **模式匹配** ~~~ SELECT * FROM pet WHERE name LIKE 'b%'; SELECT * FROM pet WHERE name LIKE '%fy'; SELECT * FROM pet WHERE name LIKE '%w%'; SELECT * FROM pet WHERE name LIKE '_____'; SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b') SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs); SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b'); SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c'); SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$'); SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w'); SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$'); ~~~ * **計數行** ~~~ SELECT COUNT(*) FROM pet; SELECT owner, COUNT(*) FROM pet GROUP BY owner; SELECT species, COUNT(*) FROM pet GROUP BY species; SELECT sex, COUNT(*) FROM pet GROUP BY sex; SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex; SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex; ~~~ * **使用多個表** 如果您想記錄有關他們的其他信息，例如他們生活中的事件，例如看獸醫或出生時產仔，則需要另一張table。 ~~~ -- 創建表 CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255)); -- 初始化數據 insert into event values ('Fluffy', '1995-05-15', 'litter', '4 kittens, 3 female, 1 male'), ('Buffy', '1993-06-23', 'litter', '5 puppies, 2 female, 3 male'), ('Buffy', '1994-06-19', 'litter', '3 puppies, 3 female'), ('Chirpy', '1999-03-21', 'vet', 'needed beak straightened'), ('Slim', '1997-08-03', 'vet', 'broken rib '), ('Bowser', '1991-10-12', 'kennel', null), ('Fang', '1991-10-12', 'kennel', null), ('Fang', '1998-08-28', 'birthday', 'Gave him a new chew toy'), ('Claws', '1998-03-17', 'birthday', 'Gave him a new flea collar'), ('Whistler', '1998-12-09', 'birthday', 'First birthday'); -- 測試數據，為了體現左右連接的區別 insert into pet(name,owner,species,birth) values ('小強','周星星','小強','1998-8-8'), ('小白','小新','dog','1998-8-8'); ~~~ | name | date | type | remark | | --- | --- | --- | --- | | Fluffy | 1995-05-15 | litter | 4 kittens, 3 female, 1 male | | Buffy | 1993-06-23 | litter | 5 puppies, 2 female, 3 male | | Buffy | 1994-06-19 | litter | 3 puppies, 3 female | | Chirpy | 1999-03-21 | vet | needed beak straightened | | Slim | 1997-08-03 | vet | broken rib | | Bowser | 1991-10-12 | kennel | | | Fang | 1991-10-12 | kennel | | | Fang | 1998-08-28 | birthday | Gave him a new chew toy | | Claws | 1998-03-17 | birthday | Gave him a new flea collar | | Whistler | 1998-12-09 | birthday | First birthday | * * * SQL 連接（JOIN） 子句用于把來自兩個或多個表的行結合起來，基于這些表之間的共同字段。連接的結果可以在邏輯上看作是由SELECT語句指定的列組成的新表。 左連接與右連接的左右指的是以兩張表中的哪一張為基準，它們都是外連接。外連接就好像是為非基準表添加了一行全為空值的萬能行，用來與基準表中找不到匹配的行進行匹配。假設兩個沒有空值的表進行左連接，左表是基準表，左表的所有行都出現在結果中，右表則可能因為無法與基準表匹配而出現是空值的字段。 在我們繼續講解實例之前，我們先列出您可以使用的不同的 SQL JOIN 類型： * INNER JOIN：如果表中有至少一個匹配，則返回行  * LEFT JOIN：即使右表中沒有匹配，也從左表返回所有的行  * RIGHT JOIN：即使左表中沒有匹配，也從右表返回所有的行  * FULL JOIN：只要其中一個表中存在匹配，則返回行（MySQL不支持FULL JOIN,可以使用union替代，將兩個查詢結果合起來。） ~~~ -- 內連接 -- INNER JOIN 與 JOIN 是相同的。 select * from pet,event where pet.name = event.name; select * from pet join event on pet.name = event.name; select * from pet inner join event on pet.name = event.name; -- 外連接 -- outer這個關鍵字 ，可以省略 。LEFT JOIN 等同 LEFT OUTER JOIN ，RIGHT JOIN 等同 RIGHT OUTER JOIN。 select * from pet left join event on pet.name = event.name; select * from pet left outer join event on pet.name = event.name; select * from pet right join event on pet.name = event.name; -- mysql不支持全連接，可使用left join + union+ right join替代，使用較少，不再啰嗦。 select * from A left join B on A.id = B.id （where 條件） union select * from A right join B on A.id = B.id （where條件); -- union 將兩個查詢結果并起來，列的數量以及類型需要一致 -- 默認地，UNION 操作符選取不同的值。如果允許重復的值，請使用 UNION ALL。 -- (select name from pet) union all (select name from event); -- 找出每個寵物產仔的年齡 SELECT pet.name, TIMESTAMPDIFF(YEAR,birth,date) AS age, remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter'; -- 該查詢使用INNER JOIN組合表。 當且僅當兩個表均符合ON子句中指定的條件時，INNER JOIN才允許其中一個表中的行出現在結果中。 -- 要在您的寵物中查找繁殖對，您可以將寵物表自身與寵物表結合起來，以產生相似物種的活體雄性和雌性候選對： SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f' AND p1.death IS NULL AND p2.sex = 'm' AND p2.death IS NULL; ~~~ * * * 5. **在批處理模式下使用mysql** ~~~ shell> mysql < batch-file > mysql.out mysql> source filename; mysql> \. filename ~~~ * * * 6. **常見查詢示例** * 列的最大值 * 保留某個列的最大值的行 * 每組的列的最大值 * 保留某個列的組的最大值的行 * 使用用戶定義的變量 * 使用外鍵 * 搜索兩個鍵 * 計算每天的訪問量 * 使用AUTO\_INCREMENT * * * ### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_1-4-mysql%E5%A4%8D%E6%9D%82%E6%9F%A5%E8%AF%A2)1.4. Mysql復雜查詢 現在你已經簡單了解mysql的使用。不過就像考察一個人會不會編程時，不會考察他能不能寫helloworld，盡管會寫helloworld也算是會寫程序。當筆試/面試時，遇到mysql通常會和復雜查詢聯系在一起，這些復雜查詢會讓你把mysql的參考手冊翻爛，然后寫出一段很長的sql語句，最后告訴你執行結果不正確。 #### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_1-%E7%BB%83%E4%B9%A01-spj%E6%95%B0%E6%8D%AE%E5%BA%93)1) 練習1-spj數據庫 我們以pta的一組經典題目開始這種復雜查詢的練習。 **題目集** [18級SQL練習1(opens new window)](https://pintia.cn/problem-sets/1247724379845836800/problems/type/10) Spj**數據庫** > 工欲善其事，必先利其器 ~~~ -- 在本地創建數據庫 CREATE TABLE `j` ( `jno` char(3) NOT NULL, -- 工程項目號 `jname` varchar(10) DEFAULT NULL, `city` varchar(10) DEFAULT NULL, PRIMARY KEY (`jno`) ); CREATE TABLE `p` ( `pno` char(3) NOT NULL, -- 零件號 `pname` varchar(10) DEFAULT NULL, `color` char(2) DEFAULT NULL, `weight` smallint(6) DEFAULT NULL, PRIMARY KEY (`pno`) ); CREATE TABLE `s` ( `sno` char(3) NOT NULL, -- 供應商號 `sname` varchar(10) DEFAULT NULL, `status` char(2) DEFAULT NULL, `city` varchar(10) DEFAULT NULL, PRIMARY KEY (`sno`) ); CREATE TABLE `spj` ( `sno` char(3) NOT NULL, `pno` char(3) NOT NULL, `jno` char(3) NOT NULL, `qty` smallint(6) DEFAULT NULL, PRIMARY KEY (`sno`,`pno`,`jno`), CONSTRAINT `fk_jno` FOREIGN KEY (`jno`) REFERENCES `j` (`jno`), CONSTRAINT `fk_pno` FOREIGN KEY (`pno`) REFERENCES `p` (`pno`), CONSTRAINT `fk_sno` FOREIGN KEY (`sno`) REFERENCES `s` (`sno`) ); INSERT INTO s VALUES ('s1','精益','20','天津'), ('s2','登錫','10','北京'), ('s3','東方紅','30','北京'), ('s4','豐泰盛','20','天津'), ('s5','為民','30','上海'); INSERT INTO p VALUES ('p1','螺母','紅','12'), ('p2','螺栓','綠','17'), ('p3','螺絲刀','藍','14'), ('p4','螺絲刀','紅','14'), ('p5','凸輪','藍','40'), ('p6','齒輪','紅','30'); INSERT INTO j VALUES ('j1','三建','北京'), ('j2','一-汽','長春'), ('j3','彈簧廠','天津'), ('j4','造船廠','天津'), ('j5','機車廠','唐山'), ('j6','無線電廠','南京'), ('j7','半導體廠','南京'), ('j8','電子元件廠','南京'); INSERT INTO spj VALUES ('s1','p1','j1','200'), ('s1','p1','j2','700'), ('s1','p1','j4','700'), ('s1','p2','j4','100'), ('s1','p2','j5','400'), ('s1','p3','j2','200'), ('s1','p3','j4','100'), ('s2','p3','j5','50'), ('s2','p3','j6','400'), ('s3','p1','j1','100'), ('s3','p3','j1','200'), ('s4','p1','j1','200'), ('s4','p2','j1','100'), ('s4','p3','j1','300'), ('s4','p5','j4','200'), ('s4','p6','j2','100'), ('s5','p2','j2','154'), ('s5','p3','j1','20'), ('s5','p4','j2','50'), ('s5','p6','j3','125'), ('s5','p6','j4','52'); ~~~ #### [#](https://wonderwhyy.gitee.io/wdocs/pages/fccc0e/#_2-%E7%BB%83%E4%B9%A02-%E5%AD%A6%E7%94%9F%E6%95%99%E5%B8%88%E7%A7%91%E7%9B%AE%E6%88%90%E7%BB%A9%E8%A1%A8)2) 練習2-學生教師科目成績表 > 謎語讓人著迷，不是嗎？ ~~~ -- 新建數據表—學生表 student： create table student( s_id varchar(10), s_name varchar(10), s_age date, s_sex varchar(10), PRIMARY KEY (s_id) ); insert into student values('01' , '趙雷' , '1990-01-01' , '男'); insert into student values('02' , '錢電' , '1990-12-21' , '男'); insert into student values('03' , '孫風' , '1990-12-20' , '男'); insert into student values('04' , '李云' , '1990-12-06' , '男'); insert into student values('05' , '周梅' , '1991-12-01' , '女'); insert into student values('06' , '吳蘭' , '1992-01-01' , '女'); insert into student values('07' , '鄭竹' , '1989-01-01' , '女'); insert into student values('09' , '張三' , '2017-12-20' , '女'); insert into student values('10' , '李四' , '2017-12-25' , '女'); insert into student values('11' , '李四' , '2012-06-06' , '女'); insert into student values('12' , '趙六' , '2013-06-13' , '女'); insert into student values('13' , '孫七' , '2014-06-01' , '女'); -- 新建數據表—教師表 teacher： create table teacher( t_id varchar(10), t_name varchar(10), PRIMARY KEY (t_id) ); insert into teacher values('01' , '張三'); insert into teacher values('02' , '李四'); insert into teacher values('03' , '王五'); -- 新建數據表—科目表 course： create table course( c_id varchar(10), c_name varchar(10), t_id varchar(10), PRIMARY KEY (c_id), CONSTRAINT `fk_c_t_id` FOREIGN KEY (`t_id`) REFERENCES `teacher` (`t_id`) ); insert into course values('01' , '語文' , '02'); insert into course values('02' , '數學' , '01'); insert into course values('03' , '英語' , '03'); -- 新建數據表—成績表 sc： create table sc( sc_id varchar(10), c_id varchar(10), score decimal(18,1), PRIMARY KEY (sc_id), CONSTRAINT `fk_sc_t_id` FOREIGN KEY (`c_id`) REFERENCES `course` (`c_id`) ); insert into sc values('01' , '01' , 80); insert into sc values('01' , '02' , 90); insert into sc values('01' , '03' , 99); insert into sc values('02' , '01' , 70); insert into sc values('02' , '02' , 60); insert into sc values('02' , '03' , 80); insert into sc values('03' , '01' , 80); insert into sc values('03' , '02' , 80); insert into sc values('03' , '03' , 80); insert into sc values('04' , '01' , 50); insert into sc values('04' , '02' , 30); insert into sc values('04' , '03' , 20); insert into sc values('05' , '01' , 76); insert into sc values('05' , '02' , 87); insert into sc values('06' , '01' , 31); insert into sc values('06' , '03' , 34); insert into sc values('07' , '02' , 89); insert into sc values('07' , '03' , 98); -- 練習 -- 1、查詢" 01 "課程比" 02 "課程成績高的學生的信息及課程分數 -- 1.1 查詢同時存在" 01 "課程和" 02 "課程的情況 -- 1.2 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時顯示為 null ) -- 1.3 查詢不存在" 01 "課程但存在" 02 "課程的情況 -- 2 查詢平均成績大于等于 60 分的同學的學生編號和學生姓名和平均成績 -- 3 查詢在 SC 表存在成績的學生信息 -- 4 查詢所有同學的學生編號、學生姓名、選課總數、所有課程的總成績(沒成績的顯示為 null ) -- 4.1 查有成績的學生信息 -- 5.查詢「李」姓老師的數量 -- 6.查詢學過「張三」老師授課的同學的信息 -- 7.查詢沒有學全所有課程的同學的信息 -- 8.查詢至少有一門課與學號為" 1 "的同學所學相同的同學的信息 -- 9.查詢和" 01 "號的同學學習的課程 完全相同的其他同學的信息 -- 10.查詢沒學過"張三"老師講授的任一門課程的學生姓名 -- 11.查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績 -- 12.檢索" 01 "課程分數小于 60，按分數降序排列的學生信息 -- 13.按平均成績從高到低顯示所有學生/的所有課程的成績以及平均成績 -- 14.查詢各科成績最高分、最低分和平均分： -- 以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優良率，優秀率 -- 及格為>=60，中等為：70-80，優良為：80-90，優秀為：>=90 -- 要求輸出課程號和選修人數，查詢結果按人數降序排列，若人數相同，按課程號升序排列 -- 15.按各科成績進行排序，并顯示排名， Score 重復時保留名次空缺 -- 數值的排名方法：#用sc中的score和自己進行對比，來計算“比當前分數高的分數有幾個”。# -- 16.查詢學生的總成績，并進行排名，總分重復時保留名次空缺 -- 17.統計各科成績各分數段人數：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比 -- 知識點：#group by 之后就不能在關聯其他表了，所以left join要寫在group by 之前# -- 18.查詢各科成績前三名的記錄 -- 知識點：#依然使用left join比較得出排名# -- 19.查詢每門課程被選修的學生數 -- 20.查詢出只選修兩門課程的學生學號和姓名 -- 21.查詢男生、女生人數 -- 22.查詢名字中含有「風」字的學生信息 -- 23.查詢同名同性學生名單，并統計同名人數 -- 24.查詢 1990 年出生的學生名單 -- 25.查詢每門課程的平均成績，結果按平均成績降序排列，平均成績相同時，按課程編號升序排列 -- 26.查詢平均成績大于等于 85 的所有學生的學號、姓名和平均成績 -- 知識點：#感覺left join 后，再加上group by 之后，select只能選擇一張表了，不能選在被join的那張表# -- 27.查詢課程名稱為「數學」，且分數低于 60 的學生姓名和分數 -- 28.查詢所有學生的課程及分數情況（存在學生沒成績，沒選課的情況） -- 29.查詢任何一門課程成績在 70 分以上的姓名、課程名稱和分數 -- 30.查詢不及格的課程 -- 知識點：#仔細閱讀題目，是要去掉重復的，使用distinct；# -- 31.查詢課程編號為 01 且課程成績在 80 分以上的學生的學號和姓名 -- 32.求每門課程的學生人數 -- 33.成績不重復，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績 -- 34.成績有重復的情況下，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績 -- 知識點：#這道題加了一個條件，就是如果有重復的情況下；答案給了一種思路：就是算出最高分數，然后查詢這個門課成績=最高分數的# -- 35.查詢不同課程成績相同的學生的學生編號、課程編號、學生成績 -- 36.查詢每門功成績最好的前兩名 -- 37.統計每門課程的學生選修人數（超過 5 人的課程才統計） -- 38.檢索至少選修兩門課程的學生學號 -- 39.查詢選修了全部課程的學生信息 -- 40.查詢各學生的年齡，只按年份來算 -- 知識點：#主要考察時間函數的使用# -- 41.按照出生日期來算，當前月日 < 出生年月的月日則，年齡減一 -- 知識點：#curdate():返回當前的日期# -- 42.查詢本周過生日的學生 -- 43.查詢下周過生日的學生 -- 44.查詢本月過生日的學生 -- 45.查詢下月過生日的學生 ~~~