教程揭秘 | 動力節(jié)點內(nèi)部Java零基礎教學文檔第二篇：MySQL
接上期后續(xù)
本期分享第二章節(jié)-MySQL
大家已經(jīng)學完基礎的JavaSE篇章，內(nèi)容很多~
已經(jīng)入門了哦，現(xiàn)在繼續(xù)努力吧！
大家需保持耐心，慢慢的學~
爭取你們學習的速度！
跟上我更新的速度哦~?
今日新篇章
【MySQL】
【主要內(nèi)容】
1.?數(shù)據(jù)庫相關概念
2.?數(shù)據(jù)庫和操作工具的安裝
3.?創(chuàng)建數(shù)據(jù)庫、表、視圖、索引
4.?查詢、新增、刪除、修改數(shù)據(jù)
5.?條件查詢
6.?數(shù)據(jù)庫的高級查詢
7.?MySQL的常用函數(shù)
8.?MySQL版本新特性
9.?MySQL索引優(yōu)化
10.?MySQL事務
【學習目標】
1.?數(shù)據(jù)庫的相關概念
1.1??數(shù)據(jù)
? ?1，定義：描述事物的符號
? ?2，表達形式：多種表現(xiàn)形式：文本，圖形，音頻，視頻
1.2??數(shù)據(jù)庫（Database, DB）
1，糧庫 ?車庫
2，存放數(shù)據(jù)的倉庫 存放在計算機中，按照一定格式存放 ?可為用戶共享
1.3??數(shù)據(jù)庫管理系統(tǒng)（Database Management System, DBMS）
1，如何科學的組織和存儲數(shù)據(jù)，如何高效的獲取和維護數(shù)據(jù)，靠數(shù)據(jù)庫管理系統(tǒng)完成
2，Oracle ?MySQL SQL Server(微軟) ?DB2 FoxPro，Access
1.4??數(shù)據(jù)庫應用程序（DBAS）
1，在數(shù)據(jù)庫管理系統(tǒng)基礎上，使用數(shù)據(jù)庫管理系統(tǒng)的語法，開發(fā)的直接面對最終用戶的應用程序
2，學生管理系統(tǒng)、人事管理系統(tǒng)、圖書管理系統(tǒng)
1.5??數(shù)據(jù)庫管理員（Database Administrator, DBA）
? 1，數(shù)據(jù)庫管理系統(tǒng)的操作者
1.6??最終用戶
? 1，數(shù)據(jù)庫應用程序的使用者
1.7??數(shù)據(jù)庫系統(tǒng)
? 1，數(shù)據(jù)庫+數(shù)據(jù)庫管理系統(tǒng)+數(shù)據(jù)庫應用程序+最終用戶??
?
1.8??數(shù)據(jù)庫的發(fā)展階段
? ?1，網(wǎng)狀數(shù)據(jù)庫
? ?2，層次數(shù)據(jù)庫
? ?3，關系數(shù)據(jù)庫
?采用關系[二維表]結構存儲與管理數(shù)據(jù)
?采用結構化查詢語言(sql)作為客戶端數(shù)據(jù)庫服務器間溝通的橋梁
?目前主流的數(shù)據(jù)庫技術
??
4，對象數(shù)據(jù)庫
1.9??NOSQL數(shù)據(jù)庫
? ?1，Not only sql數(shù)據(jù)庫 泛指非關系數(shù)據(jù)庫。如MongoDB,Redis
? ?2，關系數(shù)據(jù)庫在超大規(guī)模和高并發(fā)的web2.0純屬動態(tài)網(wǎng)站已經(jīng)顯示力不從心，暴露了很多難以克服的問題。Nosql數(shù)據(jù)庫的產(chǎn)生就是為了解決大無框數(shù)據(jù)集合多重數(shù)據(jù)種類帶來的挑戰(zhàn)，尤其是大數(shù)據(jù)應用難題
1.10??市場上流行的關系型數(shù)據(jù)庫
數(shù)據(jù)庫? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?廠商
Oracle? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 甲骨文公司
MySQL? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?MySQL
SQLServer? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Microsoft
DB2? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?IBM
Sqlite? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? RichardHipp
2.?數(shù)據(jù)庫相關安裝
2.1?準備工作
?
msi文件：mysql數(shù)據(jù)庫的安裝文件
navicat:操作數(shù)據(jù)庫的工具
Navicat:破解navicat的工具
vcredis_x64.exe:安裝mysql環(huán)境檢查的通過的一個工具
2.2?安裝mysql
2.2.1?右鍵安裝
2.2.2?讀條中
2.2.3?安裝主頁面
2.2.4?這里選擇只安裝服務
2.2.5?下一步檢查環(huán)境【停住，別安裝】
上圖中環(huán)境有問題，說差一個Microsoft Visual C++2013
2.2.6?找到準備文件夾里面的工具包
vcredis_x64.exe ??----》雙擊安裝它
2.2.7?到2.2.5步回退一步，再下一步【發(fā)現(xiàn)沒有問題了-如下】
2.2.8?下一步
?
2.2.9?執(zhí)行安裝并下一步
?2.2.10?下一步
2.2.11?設置root用戶的密碼
2.2.12?設置安裝的服務名
2.2.13?下一步完成配置
?
2.2.14?執(zhí)行完成如果全部對鉤說明安裝成功
2.3?安裝navcat
2.3.1?右鍵以管理員方式運行
2.3.2?安裝主頁面
2.3.3?下一步
2.3.4?下一步
2.3.5?下一步到結束【安裝中】
2.3.6?安裝完成
2.4?破解navcat
2.4.1?解壓上圖的文件
2.4.2?再解壓進入
2.4.3?雙擊運行上面的文件如下
2.4.4?點擊patch后
2.4.5?生成注冊碼并復制這個注冊碼
2.4.6?打開navcat并點擊注冊
2.4.7?輸入注冊碼并點擊激活
2.4.8?輸入注冊碼并點擊激活選擇手動激活
2.4.9?按下圖操作
2.4.10?檢查激活狀態(tài)
2.4.11?連接mysql
?
2.5?配置MySQL環(huán)境變量
2.5.1?找到mysql的安裝目錄
2.5.2?復制這個目錄地址右鍵此電腦->屬性->高級系統(tǒng)設置
2.5.3?打開環(huán)境變量
2.5.4?新建
2.5.5?配置PATH并確定
2.5.6?測試環(huán)境變量是否生效
按win+r鍵輸入cmd
輸入 ?mysql -uroot -p123456
3.?數(shù)據(jù)庫相關基礎操作
3.1?啟動|停止
3.1.1?方式1
3.1.2?方式2
win+r ?cmd
net start mysql 啟動
net stop mysql 停止
3.2?使用root賬戶登陸
mysql ?-uroot ??-p123456
root是默認的用戶名---超級管理員
123456是之前我們安裝的時候設置的默認密碼
3.3?顯示所有數(shù)據(jù)庫
?show databases;
3.4?默認的幾個數(shù)據(jù)庫說明
3.4.1?information_schema
?information_schema數(shù)據(jù)庫是MySQL自帶的，它提供了訪問數(shù)據(jù)庫元數(shù)據(jù)的方式。什么是元數(shù)據(jù)呢？元數(shù)據(jù)是關于數(shù)據(jù)的數(shù)據(jù)，如數(shù)據(jù)庫名或表名，列的數(shù)據(jù)類型，或訪問權限等。有些時候用于表述該信息的其他術語包括“數(shù)據(jù)詞典”和“系統(tǒng)目錄”。
在MySQL中，把 information_schema 看作是一個數(shù)據(jù)庫，確切說是信息數(shù)據(jù)庫。其中保存著關于MySQL服務器所維護的所有其他數(shù)據(jù)庫的信息。如數(shù)據(jù)庫名，數(shù)據(jù)庫的表，表欄的數(shù)據(jù)類型與訪問權 限等。在INFORMATION_SCHEMA中，有數(shù)個只讀表。它們實際上是視圖，而不是基本表，因此，你將無法看到與之相關的任何文件
3.4.2?mysql
這個是mysql的核心數(shù)據(jù)庫，主要負責存儲數(shù)據(jù)庫的用戶、權限設置、關鍵字等mysql自己需要使用的控制和管理信息。不可以刪除，如果對mysql不是很了解，也不要輕易修改這個數(shù)據(jù)庫里面的表信息。
3.4.3?performance_schema
mysql 5.5 版本 新增了一個性能優(yōu)化的引擎： PERFORMANCE_SCHEMA這個功能默認是關閉的：
需要設置參數(shù)： performance_schema ?才可以啟動該功能，這個參數(shù)是靜態(tài)參數(shù)，只能寫在
my.cnf 中 不能動態(tài)修改。
windows里面叫 my.ini文件 ???C:\ProgramData\MySQL\MySQL Server 5.7 ??【默認在這個目錄】
linux里面叫my.cnf文件
3.4.4?sys
通過這個庫可以快速的了解系統(tǒng)的元數(shù)據(jù)信息
這個庫確實可以方便DBA發(fā)現(xiàn)數(shù)據(jù)庫的很多信息，解決性能瓶頸都提供了巨大幫助
3.4.5?test
這個庫就是一個測試庫而已
3.4.6?重要說明
以上的所有默認庫。只有、test庫能刪除，其它的都不要刪除
3.5?使用某一個數(shù)據(jù)庫
-- 語法
-- use ?<數(shù)據(jù)庫名稱>;
-- 例如：使用mysql數(shù)據(jù)庫
use mysql;
3.6?修改密碼
-- 1使用mysql數(shù)據(jù)庫
use mysql;
-- 2修改密碼命令(將root用戶的密碼修改為123456)
update mysql.user set authentication_string=PASSWORD('123456') where user='root;
-- 3刷新
flush privileges;
3.7?創(chuàng)建用戶
3.7.1?命令
CREATE ?USER ?'username'@'host' ?IDENTIFIED BY ?'password';
3.7.2?說明
username：你將創(chuàng)建的用戶名
host：指定該用戶在哪個主機上可以登陸，如果是本地用戶可用localhost，如果想讓該用戶可以從任意遠程主機登陸，可以使用通配符%
password：該用戶的登陸密碼，密碼可以為空，如果為空則該用戶可以不需要密碼登陸服務器
3.7.3?例子
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';
3.8?給用戶授權
3.8.1?命令
GRANT privileges ON databasename.tablename TO 'username'@'host'
3.8.2?說明
privileges：用戶的操作權限，如SELECT，INSERT，UPDATE等，如果要授予所的權限則使用ALL
databasename：數(shù)據(jù)庫名
tablename：表名，如果要授予該用戶對所有數(shù)據(jù)庫和表的相應操作權限則可用*表示，如*.*
3.8.3?例子
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';?
GRANT ALL ON maindataplus.* TO 'pig'@'%';
3.8.4?注意
用以上命令授權的用戶不能給其它用戶授權，如果想讓該用戶可以授權，用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
3.9?創(chuàng)建一個數(shù)據(jù)庫
-- 創(chuàng)建數(shù)據(jù)庫有兩種方式：
-- 第一種 語法如下
-- 注意：<>里面的內(nèi)容都是可變的
-- create database <數(shù)據(jù)庫名稱>;
-- 例如
create database whpowernode;
-- 第二種 語法如下
-- if not exists 創(chuàng)建之前會做check操作，如果數(shù)據(jù)庫名稱在MySQL數(shù)據(jù)庫管理系統(tǒng)中不存在才創(chuàng)建
-- charset default 為數(shù)據(jù)庫指定默認編碼
-- create database [if not exists]?<數(shù)據(jù)庫名稱> ?default charset ?<數(shù)據(jù)庫字符集編碼>;
-- 例如：
create database if not exists whpowernode?default charset UTF8;
create database if not exists whpowernode?default charset?UTF8MB4;
--第三種: create database <數(shù)據(jù)庫名稱>??[character set utf8];
-- UTF8和UTF8MB4區(qū)別？
-- 絕大多數(shù)情況下使用UTF8編碼的字符集一個漢字占據(jù)3個字符，但是有極少數(shù)情況下一個漢字占據(jù)4個字符
-- 占據(jù)4個字符的漢字不能使用UTF8存儲，必須使用UTF8MB4存儲
-- UTF8MB4字符集編碼表示一個漢字最多占據(jù)4個字符 ?M Most ?B Byte
-- 注意：創(chuàng)建數(shù)據(jù)庫指定的字符集編碼必須跟MySQL數(shù)據(jù)庫里面的my.ini字符集編碼保持一致
3.10?修改默認編碼[找到my.ini文件]
3.11?刪除一個數(shù)據(jù)庫
-- 第一種方式 語法：
-- drop database <數(shù)據(jù)庫名稱>;
-- 例如：
drop database?whpowernode;
-- 第二種方式 語法：
-- 刪除之前先判斷 存在才刪除
-- drop database if exists <數(shù)據(jù)庫名稱>;
-- 例如：
drop database if exists whpowernode;
3.12?修改數(shù)據(jù)庫
alter ??database ??<數(shù)據(jù)庫名稱> ??character set ?新編碼名;
3.13?3.13 查看庫
-- 查詢mysql管理系統(tǒng)軟件下所有的庫:
show databases;
-- 查詢指定庫的詳細信息
???show create database <數(shù)據(jù)庫名稱>;
-- 查看當前用戶使用的是具體哪個庫
??select database();
4.?什么是SQL
SQL是一個面向過程的結構化查詢語言，全稱Struct Query Language 。我們對MySQL做的任何操作(命令)都建立在結構化查詢語言中。
SQL由以下四部分組成：DDL、DML、DQL、TCL
4.1??DDL 數(shù)據(jù)定義語言
?全稱Data Definition Language，主要用于創(chuàng)建和刪除結構。
??1)創(chuàng)建結構相關操作：庫、表、視圖、索引 ??create
??2)刪除結構相關操作：庫、表、視圖、索引????drop
??3)改變結構相關操作：庫、表、視圖、索引???alter
4)查詢結構相關操作: 庫、表、視圖、索引 ?show
4.2??DML 數(shù)據(jù)操縱語言
DML對表和視圖進行插入、刪除、更新相關操作，全稱Data Manipulation Language。插入數(shù)據(jù)關鍵字：insert
??刪除數(shù)據(jù)關鍵字：delete
??修改數(shù)據(jù)關鍵字：update
4.3??DQL 數(shù)據(jù)查詢語言
?對表和視圖進行操作，全稱： Data Query Language
???DQL關鍵字 select
4.4??TCL 事務控制語言
?TCL全稱Transaction Control Language，使用事務管理DML操作。
???提交事務關鍵字 commit;
???回滾事務關鍵字 rollback;
???開啟事務關鍵字 start transaction;
5.?MySQL數(shù)據(jù)類型
5.1?整數(shù)類型
5.2?小數(shù)類型
5.3?定點數(shù)
浮點型在數(shù)據(jù)庫中存放的是近似值，而定點類型在數(shù)據(jù)庫中存放的是精確值。?decimal(m,d) 參數(shù)m是總個數(shù)，d是小數(shù)位。
5.4?字符串
5.4.1?char、varchar、text區(qū)別
char：存儲定長數(shù)據(jù)很方便，CHAR字段上的索引效率級高，必須在括號里定義長度，可以有默認值，比如定義char(10)，那么不論你存儲的數(shù)據(jù)是否達到了10個字節(jié)，都要占去10個字節(jié)的空間（自動用空格填充），且在檢索的時候后面的空格會隱藏掉，所以檢索出來的數(shù)據(jù)需要記得用什么trim之類的函數(shù)去過濾空格。
varchar：存儲變長數(shù)據(jù)，但存儲效率沒有CHAR高，必須在括號里定義長度，可以有默認值。保存數(shù)據(jù)的時候，不進行空格自動填充，而且如果數(shù)據(jù)存在空格時，當值保存和檢索時尾部的空格仍會保留。另外，varchar類型的實際長度是它的值的實際長度+1，這一個字節(jié)用于保存實際使用了多大的長度。
text：存儲可變長度的非Unicode數(shù)據(jù)，最大長度為2^31-1個字符。text列不能有默認值，存儲或檢索過程中，不存在大小寫轉換，后面如果指定長度，不會報錯誤，但是這個長度是不起作用的，意思就是你插入數(shù)據(jù)的時候，超過你指定的長度還是可以正常插入。
5.4.2?實戰(zhàn)結論
1、經(jīng)常變化的字段用varchar；
2、知道固定長度的用char；
3、超過255字節(jié)的只能用varchar或者text；
4、能用varchar的地方不用text；
5、能夠用數(shù)字類型的字段盡量選擇數(shù)字類型而不用字符串類型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接回逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了；
6、同一張表出現(xiàn)多個大字段，能合并時盡量合并，不能合并時考慮分表
5.5?日期時間類型
MySQL數(shù)據(jù)類型? ? ??含義
date? ? ? ? ? ? ? ? ? ? 日期 '2021-1-1'
time? ? ? ? ? ? ? ? ? ? 時間 '12:25:36'
datetime? ? ? ? ? ? ? ?日期時間 '2021-1-1?22:06:44'
timestamp? ? ? ? ? ? ? ?自動存儲記錄修改時間
若定義一個字段為timestamp，這個字段里的時間數(shù)據(jù)會隨其他字段修改的時候自動刷新，所以這個數(shù)據(jù)類型的字段可以存放這條記錄最后被修改的時間。
5.6?Logic類型
Logic類型使用bit表示，它只能存儲兩個值0或者1
5.7?數(shù)據(jù)類型的屬性
6.?MySQL表的相關操作DDL
6.1?創(chuàng)建表
6.1.1?語法
create table <表名稱>
(
???列名稱1 數(shù)據(jù)類型1,
???列名稱2 數(shù)據(jù)類型2,
???..........
???列名稱n 數(shù)據(jù)類型n
);
?
6.1.2?實例創(chuàng)建一個學生表tb_student
create table tb_student(
?????id int(11),
?????stu_name varchar(50),
?????stu_sex bit,
?????stu_hobby varchar(50),
?????stu_age smallint
);
-- 注意：最后一列沒有逗號
-- 列與列之間以半角逗號相隔 ?
6.1.3?實例創(chuàng)建一個學生表tb_student并指定編碼和存儲引擎
create table student(
?????id int(11),
?????stu_name varchar(50),
?????stu_sex bit,
?????stu_hobby varchar(50),
?????stu_age smallint
)default charset=UTF8, ENGINE=INNODB;
DEFAULT CHARSET 用于指定表中數(shù)據(jù)的字符集編碼方式
ENGINE 用于指定表的存儲引擎
6.2?數(shù)據(jù)存儲的問題
現(xiàn)在我們在數(shù)據(jù)庫中創(chuàng)建了一個表，那么如我們把電腦重啟，這個表還在不在呢？
測試結果為 ??在 ??那是為什么呢？
原因是當我們創(chuàng)建表個表之后，在我們數(shù)據(jù)庫的文件系統(tǒng)里面就會有一個對應的文件存在
?
.frm文件：保存了每個表的元數(shù)據(jù)，包括表結構的定義等；
.ibd文件：InnoDB引擎開啟了獨立表空間(my.ini中配置innodb_file_per_table = 1)產(chǎn)生的存放該表的數(shù)據(jù)和索引的文件。
6.3?查看表結構
創(chuàng)建表之后如何查看是否創(chuàng)建成功？可以通過desc命令來查看表結構
-- 第一種方式查看表結構
-- desc <表名稱>;
desc student;
-- 第二種方式查看表結構
-- show ?create??table ?<表名稱>;
show ?create??table ?tb_student;
6.4?修改表結構
基本語法如下：alter table <表名稱>;
6.4.1?案例1
添加列，例如：向student表新增加一列stu_height學生身高
-- 語法如下：
-- alter ?table ?<表名稱> ?add column ?<列名稱> ?<數(shù)據(jù)類型>;
alter table student add column stu_height int(11);
-- 查看表結構
desc student;
6.4.2?案例2
刪除某一個列，例如：刪除student表中的學生身高
-- 語法如下：
-- alter table <表名稱> drop column <列名稱>;
alter table student drop column stu_height;
6.4.3?案例3
修改列名稱和數(shù)據(jù)類型：例如修改stu_sex 為 student_sex 數(shù)據(jù)類型修改為 varchar(30)
-- 語法如下：
-- alter ?table ?<表名稱> ?change ?column <原列名稱> ?<新列名稱> ?<數(shù)據(jù)類型>;
-- 修改stu_sex 為 student_sex 數(shù)據(jù)類型修改為 varchar(30)
alter table student change column stu_sex ??student_sex ??varchar(30);
6.4.4?案例4
修改某一列的數(shù)據(jù)類型，例如：將學生年齡修改為int類型
-- 語法如下：
-- alter table ?<表名稱> ?modify ?column ?<原來的列名稱> ?<新的數(shù)據(jù)類型>;
-- 例如：
alter table student modify column stu_age int(11);
6.5?刪除表
6.5.1?直接刪除
-- drop table <表名稱>;
drop table EMP_bak;
6.5.2?刪除之前先判斷表是否存在
-- drop table if exists <表名稱>; ?
-- 刪除之前先做校驗，表在數(shù)據(jù)庫中存在才刪除
drop table if exists EMP_bak;
?
6.6?顯示當前庫的所有表
show tables;
?
7.?MySQL表里面數(shù)據(jù)的相關操作DML
通過我們上面的操作，存放數(shù)據(jù)的容器創(chuàng)建好了，那么現(xiàn)在接著就是要向這個容器里面添加修改刪除數(shù)據(jù)了
7.1?添加數(shù)據(jù)
7.1.1?單個添加
-- 往表中添加數(shù)據(jù)(insert，插入)
-- 插入方式1：insert into 表名(字段1,字段2,...字段N) values(值1,值2,...值N);
insert into students(stuNo,stuName) values('10086','王二麻子');
insert into students(stuNo,stuName,gender,age) values('10010','張三','男',20);
?
-- 插入方式2: insert into 表名 values(值1,值2,...值N);
insert into students values('10011','李四','男',28);
?
-- 插入方式3: insert into 表名 set 字段1=值1,字段2=值2,...字段n=值n;
insert into students set stuNo='10000',stuName='鳳姐';
?
7.1.2?批量添加
-- 插入方式4: insert into 表名(字段1,字段2,...字段N) values(值1,值2,...值N),(值1,值2,...值N),(值1,值2,...值N)
insert into students(stuNo,stuName,gender,age) values
('10010','劉濤','男',18),('10020','劉西陵','男',28),('10030','張文杰','男',38);
7.2?修改數(shù)據(jù)
7.2.1?批量修改
-- 修改所有數(shù)據(jù)
-- 語法
--?update 表名 set 字段1=新值1,字段2=值2,...字段N=值N [where 條件];
-- 例如：student表的年齡全部修改為50
update student set stu_age = 50;
7.2.2?單個修改
-- 如果只想修改某一行的數(shù)據(jù)，需要加上條件
-- 例如：我只想修改id為1031的年齡為60
-- where 表示行過濾
-- where id=1031 表示只選擇id為1031的行進行修改
-- 下面代碼執(zhí)行步驟：
-- 首先執(zhí)行 update student 確定修改那張表
-- 然后執(zhí)行 where 條件 確定修改表中的哪一行，不滿住條件的行都會被過濾掉
?update student set stu_age = 60 where id = 1031;
7.2.3?單個修改多個值
update student set stu_age = 60，name='習大大'??where id = 1031;
7.3?刪除數(shù)據(jù)
7.3.1?刪除一個
-- 刪除某一條數(shù)據(jù) 語法：
-- ?delete from 表名 [where 條件];
-- 例如：刪除id為108的student數(shù)據(jù)
delete from student where id = 108;
7.3.2?刪除全部
-- 語法
-- delete from <表名稱> ;
-- 例如：刪除student表所有數(shù)據(jù)
delete from student;
7.3.3?truncate關鍵字
truncate是DDL，刪除所有數(shù)據(jù)，不支持where，不能回滾
delete是DML，可以刪除部分數(shù)據(jù)，因為支持where，可以回滾
總體上truncate效率比delete高，他們都只能刪除數(shù)據(jù)不能刪除表結構，只有drop才能刪除表結構
-- truncate ?[table]??表名;
?
8.?數(shù)據(jù)完整性
為什么要學習數(shù)據(jù)完整性？我們上面創(chuàng)建的tb_student表缺乏安全性，還會有重復的id，這些都是臟數(shù)據(jù)。
什么是數(shù)據(jù)完整性？需要為MySQL數(shù)據(jù)庫中的表定義一個規(guī)則，確保表中數(shù)據(jù)的有效性、一致性、安全性、盡最大的可能減少臟數(shù)據(jù)，重復的數(shù)據(jù)(冗余)。
8.1?主鍵約束
什么是主鍵：主要的關鍵字，一個表如果創(chuàng)建了主鍵那么該行的所有數(shù)據(jù)必須在表中唯一。
身份證號就是身份證表的主鍵，能夠確保在表中的唯一性，并且身份證編號是不能為空的
身份證編號
姓名
地址
所屬公安局
性別
420193425324634256
張三
武漢
武昌分局
男
420193425324634258
李四
杭州
武昌分局
男
420193425324634251
王五
長沙
武昌分局
女
主鍵約束小結：非空并且唯一
8.1.1?主鍵設計原則
在開發(fā)中，一般的主鍵為數(shù)值類型，呈遞增趨勢
如果不想每次都插入主鍵，我們可以讓數(shù)據(jù)自動增長
語法：
create table tb_class(
?id int(11) auto_increment, ?-- id是主鍵列，不用顯示插入值 讓其自動增長
?class_name varchar(30) not null, -- not null 非空約束
?class_desc varchar(100), ?-- 班級描述
?primary key (id)???-- 為tb_class表的id列設置主鍵約束
)ENGINE=INNODB,DEFAULT CHARSET UTF8;
注意： MySQL 數(shù)據(jù)庫 auto_increment自動增長和主鍵primary key 是配套的不能單獨使用
8.2?非空約束
create table tb_class(
?id int(11) auto_increment, ?-- id是主鍵列，不用顯示插入值 讓其自動增長
?class_name varchar(30) not null, -- not null 非空約束
?class_desc varchar(100), ?-- 班級描述
?primary key (id) ??-- 為tb_class表的id列設置主鍵約束
)ENGINE=INNODB,DEFAULT CHARSET UTF8;
?
8.3?唯一約束
制定一個規(guī)則，讓表的某一列數(shù)據(jù)必須唯一
特征：某列數(shù)據(jù)可以為空，但是必須唯一
例如：學生的手機號碼
create table tb_1(
????phone int unique
)
?
8.4?默認約束
為某一列制定一個默認規(guī)則
例如：性別默認為 0
create table tb_1(
????phone int unique,
sex int DEFAULT ‘0’
)
?
8.5?外鍵約束
外部的關鍵字叫做外鍵，通常為多張表中建立聯(lián)系，確保表與表之間的數(shù)據(jù)安全性，一致性，能夠減少數(shù)據(jù)冗余(重復的數(shù)據(jù))。
前面幾個約束：在一張表建立約束(規(guī)則)
外鍵約束： 多張表之間建立約束(關聯(lián)，聯(lián)系)
場景：創(chuàng)建一個tb_student表，為其添加默認約束、唯一約束、非空約束、外鍵約束
create table tb_student(
??id ?int(11) auto_increment,
??stu_name varchar(50) not null,
??stu_mobile varchar(20) unique, -- unique 唯一混熟
??stu_sex bit default 1, -- 默認約束 性別默認為1 ?0女 ?1男
??class_id int(11) not null,
??primary key (id),
????-- 學生表的class_id建立外鍵去關聯(lián) tb_class表的主鍵
??foreign key (class_id) references tb_class(id)
)ENGINE=INNODB,DEFAULT CHARSET UTF8;
8.6?約束關鍵字總結
約束名稱? ? ? ? ? ? ? 關鍵字
主鍵約束? ? ? ? ? primary key
非空約束? ? ? ? ? ?not null
唯一約束? ? ? ? ? ?unique
默認約束? ? ? ? ? ?default
外鍵約束? ? ? ? ? ?foreign key.......references
約束分為兩類：
1 行級別約束：?主鍵約束、外鍵約束
2 列級別約束：?非空約束、唯一約束、默認約束 ?
外鍵約束小結：
foreign key(外鍵列) ?references ?主表 (主鍵列)
references 關鍵字的右邊是主表的主鍵列，左邊是從表的外鍵列，該關鍵字在主表和從邊之間建立了聯(lián)系。
創(chuàng)建表小結：沒有外鍵的表是主表，有外鍵的表是從表。先創(chuàng)建主表后創(chuàng)建從表(有外鍵的表是從表)
插入數(shù)據(jù)小結：先插入主表數(shù)據(jù)后插入從表數(shù)據(jù)
刪除數(shù)據(jù)小結：先刪除從表數(shù)據(jù)后刪除主表數(shù)據(jù)
9.?表關系
9.1?一對一（one-to-one）【了解】
一對一關系就如球隊與球隊所在地址之間的關系，一支球隊僅有一個地址，而一個地址區(qū)也僅有一支球隊。
數(shù)據(jù)表間一對一關系的表現(xiàn)有兩種，一種是外鍵關聯(lián)，一種是主鍵關聯(lián)。圖示如下：
一對一外鍵關聯(lián)：
?
9.2?一對多/多對一（many-to-one）【掌握】
存在最普遍的映射關系，簡單來講就如球員與球隊的關系；
一對多：從球隊角度來說一個球隊擁有多個球員 即為一對多
?
多對一：從球員角度來說多個球員屬于一個球隊 即為多對一
數(shù)據(jù)表間一對多關系如下圖：
?
9.2.1?案例：
-- 班級表
create table tb_class(
?id int(11)??auto_increment, ?-- id是主鍵列，不用顯示插入值 讓其自動增長
?class_name varchar(30) not null, -- not null 非空約束
?class_desc varchar(100), ?-- 班級描述
?primary key (id) ??-- 為tb_class表的id列設置主鍵約束
)ENGINE=INNODB,DEFAULT CHARSET UTF8;
-- 學生表
create?table tb_student(
??id ?int(11) auto_increment,
??stu_name varchar(50) not null,
??stu_mobile varchar(20) unique, -- unique 唯一混熟
??stu_sex bit default 1, -- 默認約束 性別默認為1 ?0女 ?1男
??class_id int(11) not null,
??primary key (id),
????-- 學生表的class_id建立外鍵去關聯(lián) tb_class表的主鍵
??foreign key (class_id) references tb_class(id)
)ENGINE=INNODB,DEFAULT CHARSET UTF8;
?
9.3?多對多（many-to-many）【掌握】
多對多關系也很常見，例如學生與選修課之間的關系，一個學生可以選擇多門選修課，而每個選修課又可以被多名學生選擇。
數(shù)據(jù)庫中的多對多關聯(lián)關系一般需采用中間表的方式處理，將多對多轉化為兩個一對多。
數(shù)據(jù)表間多對多關系如下圖：
?
9.3.1?案例：
-- 多對多
-- 學生表
create table tb_student(
??id ?bigint(20) primary key ?auto_increment comment '主鍵',
??stu_name varchar(50) comment '學生名',
??stu_mobile varchar(20) comment '手機號',
??stu_sex varchar(2) default '1' comment '性別默認為1 ?0女 ?1男'
);
?
-- 課程表
create table tb_course(
??id bigint(20) primary key auto_increment comment '主鍵',
??course_name varchar(30) not null comment '課程名稱'
);
?
-- 學生課程中間表
create table tb_student_course(
??stu_id bigint(20) comment '學生id',
??course_id bigint(11) comment '課程id',
??course_score float(4,1) comment '課程的成績',
FOREIGN KEY(course_id) REFERENCES tb_course(id),
FOREIGN KEY(stu_id) REFERENCES tb_student(id)
);
?
?
9.4?員工系統(tǒng)設計【自行完成】
現(xiàn)在要為一個公司設計一個系統(tǒng)
9.5?要求
1，可以記錄員工信息
2，可以記錄公司所有的部門信息
3，可以記錄工資等級信息
4，可以記錄公司的職位
9.6?表結構
9.6.1?部門表（dept）
9.6.2?員工表（emp）
9.6.3?工資等級表（salgrade）
9.6.4?表關系分析
?
9.6.5?創(chuàng)建表的腳本
-- ----------------------------
-- 創(chuàng)建部門表
-- ----------------------------
DROP TABLE IF EXISTS dept;
CREATE TABLE dept ?(
??DEPTNO int(11) NOT NULL COMMENT '部門編號',
??DNAME varchar(20) NOT NULL COMMENT '部門名稱',
??LOC varchar(20) ?COMMENT '部門所在的位置',
??PRIMARY KEY (DEPTNO)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 ?COMMENT = '部門表';
?
-- ----------------------------
-- 創(chuàng)建員工表
-- ----------------------------
DROP TABLE IF EXISTS emp;
CREATE TABLE emp ?(
??EMPNO int(11) NOT NULL COMMENT '雇員的編號',
??ENAME varchar(50) ?NOT NULL COMMENT '雇員的姓名',
JOB varchar(50) ?NOT NULL COMMENT '職位',
??MGR int(11) NULL DEFAULT NULL COMMENT '雇員對應的領導編號',
??HIREDATE date NULL DEFAULT NULL COMMENT '雇員的雇傭日期',
??SAL decimal(7, 2) NULL DEFAULT NULL COMMENT '基本工資',
??COMM decimal(7, 2) NULL DEFAULT NULL COMMENT '獎金，傭金',
??DEPTNO int(11) NULL DEFAULT NULL COMMENT '雇員所在的部門編號',
??PRIMARY KEY (EMPNO),
??CONSTRAINT FK_EMP_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES dept (DEPTNO) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = '員工表';
?
-- ----------------------------
-- 工資等級表
-- ----------------------------
DROP TABLE IF EXISTS salgrade;
CREATE TABLE salgrade ?(
??GRADE int(11) NOT NULL COMMENT '工資的等級 ?主鍵',
??LOSAL decimal(7, 2) NOT NULL COMMENT '此等級的最低工資',
??HISAL decimal(7, 2) NOT NULL COMMENT '此等級的最高工資',
??PRIMARY KEY (GRADE)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 ?COMMENT = '工資等級表' ;
9.6.6?初始化數(shù)據(jù)腳本
-- ----------------------------
-- 部門表插入數(shù)據(jù)
-- ----------------------------
INSERT INTO DEPT VALUES (10, '財務部', '武漢');
INSERT INTO DEPT VALUES (20, '研發(fā)部', '武漢');
INSERT INTO DEPT VALUES (30, '銷售部', '深圳');
INSERT INTO DEPT VALUES (40, '業(yè)務部', '上海');
-- ----------------------------
-- 員工表插入數(shù)據(jù)
-- ----------------------------
INSERT INTO EMP ?values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, null, 20);
INSERT INTO EMP ?values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO EMP ?values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO EMP ?values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, null, 20);
INSERT INTO EMP ?values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO EMP ?values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, null, 30);
INSERT INTO EMP ?values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, null, 10);
INSERT INTO EMP ?values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, null, 20);
INSERT INTO EMP ?values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000.00, null, 10);
INSERT INTO EMP ?values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO EMP ?values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, null, 20);
INSERT INTO EMP ?values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, null, 30);
INSERT INTO EMP ?values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, null, 20);
INSERT INTO EMP ?values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, null, 10);
-- ----------------------------
-- 工資等級表插入數(shù)據(jù)
-- ----------------------------
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) values (1, 700, 1200);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) values (2, 1201, 1400);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) values (3, 1401, 2000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) values (4, 2001, 3000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL) values (5, 3001, 9999);
?
10.?單表數(shù)據(jù)查詢
10.1?最簡單的查詢方式
select * from emp;
select * from dept;
select empno, ename,sal,job??from emp;
10.2?使用算術表達式
select empno, ename,sal,sal*1.08 from emp;
select empno, ename,sal, sal*12 from emp;
select empno, ename,sal, sal*12 +1000 from emp;
注：在Select語句中，對數(shù)值型數(shù)據(jù)可以使用算術運算符創(chuàng)建表達式
10.3?使用字段別名
select empno ?as 員工編號, ename 員工姓名, sal*12 年薪 ?from emp;
select empno, ename "Ename", sal*12 "Anual Salary" from emp;
select sal*12+5000 ?as "年度工資(加年終獎)" from emp;
10.3.1?字段別名說明
重命名查詢結果中的字段，以增強可讀性
別名如果含有空格或其他特殊字符或大小寫敏感，需用雙引號引起來。
AS可以省略
10.4?去重查詢
缺省情況下，查詢結果中包含所有符合條件的記錄行，包括重復行
select deptno from emp;
使用DISTINCT關鍵字可從查詢結果中清除重復行
select distinct deptno from emp;
select distinct ?job from emp;
DISTINCT的作用范圍是后面所有字段的組合
select, distinct deptno job from emp;
?
10.5?排序查詢
-- 排序: ?SELECT * FROM 表名 order by 排序字段1,排序字段2 desc(降序)/asc(升序)
使用order by 子句對查詢結果進行排序
排序方式包括升序（asc，缺省）和降序（desc）兩種：
select empno, ename, sal from emp order by sal;
select empno, ename, sal from emp order by sal desc ;
按多字段排序
select deptno, empno, ename, sal from emp order by deptno, sal;
使用字段別名排序
select empno, ename, sal*12 annsal from emp order by annsal;
?
10.6?分頁查詢
關鍵字：limit
10.6.1?案列1：查詢前3條客戶信息
-- select * from 表名 limit 起始下標,pageSize;
select * from emp limit 3
小結：limit 后面跟1個參數(shù) ，例如 limit 3 此時表示最多返回3行
10.6.2?案列2：查詢第三條3條到第6條的客戶信息
?limit 參數(shù)1 , 參數(shù)2?
?參數(shù)1：表示偽列(index)的偏移量(下標從0開始)
?參數(shù)2：表示返回最大限制的行數(shù)
select * from emp limit 3,6
10.7?合并查詢
10.7.1?作用
將多個select語句聯(lián)合(合并)為一個select語句，涉及的關鍵字union 和union all。
union all 不管是否重復，全部合并
union 如果有重復的，過濾掉重復的
10.7.2?案例
select * from emp ?where DEPTNO=10
union all
select * from emp ?where DEPTNO=20
select * from emp ?where DEPTNO=10
union
select * from emp ?where DEPTNO=20
11.?where子句
作用：指定查詢條件使用where子句
11.1?用法舉例
select * from emp where deptno=10;
select * from emp where ename = 'JACK';
select * from emp where hiredate = '2020-12-12';
注意：
字符串和日期值要用單引號擴起來 ?????????????????
字符串大小寫敏感
日期值格式敏感，缺省的日期格式是'YYYY-MM-DD HH:mm:ss'
11.2?查詢條件中可以使用比較運算符
MySQL支持如下比較運算符： > ?>= ??< ?<= ??!= ??<>
?
注意：MySQL使用=運算符來判斷表達式是否相等，它沒有==
??????Java使用!=表示不等于，MySQL也支持。但是我們不要使用，效率太低了
??????MySQL使用的不等于使用<>
select * from emp where sal > 2900;
select * from emp where deptno <> 30;
?
select * from emp where sal between 1600 and 2900;
select * from emp where ename in('SMITH','CLARK','KING');
?
11.3?使用LIKE運算符執(zhí)行模糊查詢（通配查詢）
?% 表示零或多個字符 ??????_ 表示一個字符
對于特殊符號可使用ESCAPE 標識符來查找
用法舉例
select * from emp where ename like 'S%';
select * from emp where ename like '_A%';
select * from emp where ename like '%\_%' escape '\';
11.4?使用IS NULL運算符進行空值判斷
用法舉例
select * from emp where comm is null;
select * from emp where comm is not null;
11.5?查詢條件中可以使用邏輯運算符
select * from emp where deptno = 10 and sal > 1000;
select * from emp where deptno = 10 or job = ‘CLERK’;
select * from emp where sal not in (800, 1500, 2000);
11.6?SQL優(yōu)化問題
AND: ?把檢索結果較少的條件放到后面
OR: ?把檢索結果較多的條件放到后面
11.7?四種運算符優(yōu)先級：算術>連接>比較>邏輯
?
可使用小括號強行改變運算順序
select * from emp where job='SALESMAN' or job='CLERK' and sal>=1280;
select * from emp where (job='SALESMAN' or job='CLERK') and sal>=1280;
?
12.?聚合函數(shù)
使用函數(shù)可以大大提高SELECT語句操作數(shù)據(jù)庫的能力。它給數(shù)據(jù)的轉換和處理提供了方便。
函數(shù)只是將取出的數(shù)據(jù)進行處理，不會改變數(shù)據(jù)庫中的值。
12.1?函數(shù)的分類
12.1.1?單行函數(shù)分類
??數(shù)學函數(shù)
??字符串函數(shù)
??日期和時間函數(shù)
??條件判斷函數(shù)
??系統(tǒng)信息函數(shù)
??加密函數(shù)
??格式化函數(shù)
12.1.2?多行函數(shù)（聚合函數(shù)）
??僅適用數(shù)值型的多行函數(shù) ?
n?sum() 求和?
n?avg()?求平均值???
??適用任何類型數(shù)據(jù)
n?count() 計數(shù)??
n?max()?求最大值 ???
n?min()?求最小值 ???
?
12.2?單行函數(shù)
12.2.1?數(shù)學函數(shù)
（1）ABS(x)?返回x的絕對值
（2）PI()??返回圓周率π，默認顯示6位小數(shù)
（3）SQRT(x)??返回非負數(shù)的x的二次方根
（4）MOD(x,y)??返回x被y除后的余數(shù)
（5）CEIL(x)、CEILING(x)??返回不小于x的最小整數(shù)
（6）FLOOR(x)??返回不大于x的最大整數(shù)
（7）ROUND(x)、ROUND(x,y)??前者返回最接近于x的整數(shù)，即對x進行四舍五入；后者返回最接近x的數(shù)，其值保留到小數(shù)點后面y位，若y為負值，則將保留到x到小數(shù)點左邊y位
（8）SIGN(x)??返回參數(shù)x的符號，-1表示負數(shù)，0表示0，1表示正數(shù)
（9）POW(x,y)和、POWER(x,y)??返回x的y次乘方的值
（10）EXP(x)??返回e的x乘方后的值
（11）LOG(x)??返回x的自然對數(shù)，x相對于基數(shù)e的對數(shù)
（12）LOG10(x)??返回x的基數(shù)為10的對數(shù)
（13）RADIANS(x)??返回x由角度轉化為弧度的值
（14）DEGREES(x)??返回x由弧度轉化為角度的值
（15）SIN(x)、ASIN(x)??前者返回x的正弦，其中x為給定的弧度值；后者返回x的反正弦值，x為正弦
（16）COS(x)、ACOS(x)??前者返回x的余弦，其中x為給定的弧度值；后者返回x的反余弦值，x為余弦
（17）TAN(x)、ATAN(x)??前者返回x的正切，其中x為給定的弧度值；后者返回x的反正切值，x為正切
（18）COT(x) ??返回給定弧度值x的余切
（19）RAND(x) ??返回0-1之間的隨機小數(shù)
?
12.2.2?字符串函數(shù)
（1）CHAR_LENGTH(str)??計算字符串字符個數(shù)
（2）CONCAT(s1,s2，...)??返回連接參數(shù)產(chǎn)生的字符串，一個或多個待拼接的內(nèi)容，任意一個為NULL則返回值為NULL
（3）CONCAT_WS(x,s1,s2,...)?返回多個字符串拼接之后的字符串，每個字符串之間有一個x
（4）INSERT(s1,x,len,s2)??返回字符串s1，其子字符串起始于位置x，被字符串s2取代len個字符
（5）LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)前兩者將str中的字母全部轉換成小寫，后兩者將字符串中的字母全部轉換成大寫
（6）LEFT(s,n)、RIGHT(s,n)??前者返回字符串s從最左邊開始的n個字符，后者返回字符串s從最右邊開始的n個字符
（7）LPAD(s1,len,s2)、RPAD(s1,len,s2)??前者返回s1，其左邊由字符串s2填補到len字符長度，假如s1的長度大于len，則返回值被縮短至len字符；前者返回s1，其右邊由字符串s2填補到len字符長度，假如s1的長度大于len，則返回值被縮短至len字符
（8）LTRIM(s)、RTRIM(s)?前者返回字符串s，其左邊所有空格被刪除；后者返回字符串s，其右邊所有空格被刪除
（9）TRIM(s)??返回字符串s刪除了兩邊空格之后的字符串
（10）TRIM(s1 FROM s)??刪除字符串s兩端所有子字符串s1，未指定s1的情況下則默認刪除空格
（11）REPEAT(s,n)??返回一個由重復字符串s組成的字符串，字符串s的數(shù)目等于n
（12）SPACE(n)??返回一個由n個空格組成的字符串
（13）REPLACE(s,s1,s2)??返回一個字符串，用字符串s2替代字符串s中所有的字符串s1
（14）STRCMP(s1,s2)??若s1和s2中所有的字符串都相同，則返回0；根據(jù)當前分類次序，第一個參數(shù)小于第二個則返回-1，其他情況返回1
（15）SUBSTRING(s,n,len)、MID(s,n,len)兩個函數(shù)作用相同，從字符串s中返回一個第n個字符開始、長度為len的字符串
（16）LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)三個函數(shù)作用相同，返回子字符串str1在字符串str中的開始位置（從第幾個字符開始）
（17）REVERSE(s)??將字符串s反轉
（18）ELT(N,str1,str2,str3,str4,...)?返回第N個字符串
12.2.3?日期和時間函數(shù)
（1）CURDATE()、CURRENT_DATE()將當前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具體格式根據(jù)函數(shù)用在字符串或是數(shù)字語境中而定
（2）CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()這四個函數(shù)作用相同，返回當前日期和時間值，格式為"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具體格式根據(jù)函數(shù)用在字符串或數(shù)字語境中而定
（3）MONTH(date)和MONTHNAME(date)前者返回指定日期中的月份，后者返回指定日期中的月份的名稱
（4）WEEK(d)、WEEKOFYEAD(d)前者計算日期d是一年中的第幾周，后者計算某一天位于一年中的第幾周
（5）DAYOFYEAR(d)、DAYOFMONTH(d)前者返回d是一年中的第幾天，后者返回d是一月中的第幾天
（6）EXTRACT(type FROM date)從日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND
（7）TimeStampDiff(間隔類型，前一個日期，后一個日期)?
例如距離現(xiàn)在差了多少年：TimeStampDiff(year, 前一個日期, now())
間隔類型有：second秒，minute分，hour時，day天，week周，month月，quarter季度，year年
（8）DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)返回將起始時間加上expr type之后的時間，比如DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND)表示的就是把第一個時間加1秒
12.2.4?條件判斷函數(shù)
（1）IF(expr,v1,v2)如果expr是TRUE則返回v1，否則返回v2
（2）IFNULL(v1,v2)函數(shù)??如果v1的值不為NULL，則返回v1，否則返回v2。
（2）CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END 如果expr等于某個vn，則返回對應位置THEN后面的結果，如果與所有值都不想等，則返回ELSE后面的rn
12.2.5?系統(tǒng)信息函數(shù)
（1）VERSION()查看MySQL版本號
（2）CONNECTION_ID()查看當前用戶的連接數(shù)
（3）USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()查看當前被MySQL服務器驗證的用戶名和主機的組合，一般這幾個函數(shù)的返回值是相同的
（4）CHARSET(str)查看字符串str使用的字符集
（5）COLLATION()查看字符串排列方式
12.2.6?加密函數(shù)
（1）PASSWORD(str)從原明文密碼str計算并返回加密后的字符串密碼，注意這個函數(shù)的加密是單向的（不可逆），因此不應將它應用在個人的應用程序中而應該只在MySQL服務器的鑒定系統(tǒng)中使用
（2）MD5(str)為字符串算出一個MD5 128比特校驗和，改值以32位十六進制數(shù)字的二進制字符串形式返回
（3）ENCODE(str, pswd_str)使用pswd_str作為密碼，加密str
（4）?DECODE(crypt_str,pswd_str)使用pswd_str作為密碼，解密加密字符串crypt_str，crypt_str是由ENCODE函數(shù)返回的字符串
12.2.7?格式化函數(shù)
（1）FORMAT(x,n)將數(shù)字x格式化，并以四舍五入的方式保留小數(shù)點后n位，結果以字符串形式返回
（2）CONV(N,from_base,to_base)不同進制數(shù)之間的轉換，返回值為數(shù)值N的字符串表示，由from_base進制轉換為to_base進制
（3）INET_ATON(expr)給出一個作為字符串的網(wǎng)絡地址的點地址表示，返回一個代表該地址數(shù)值的整數(shù)，地址可以使4或8比特
（4）INET_NTOA(expr)給定一個數(shù)字網(wǎng)絡地址（4或8比特），返回作為字符串的該地址的點地址表示
（5）BENCHMARK(count,expr)重復執(zhí)行count次表達式expr，它可以用于計算MySQL處理表達式的速度，結果值通常是0（0只是表示很快，并不是沒有速度）。另一個作用是用它在MySQL客戶端內(nèi)部報告語句執(zhí)行的時間
12.3?多行函數(shù)
對一組數(shù)據(jù)進行運算，針對一組數(shù)據(jù)（多行記錄）只返回一個結果，也稱分組函數(shù)。
12.3.1?看圖說話
?
12.3.2?常用的多行函數(shù)
sum() ?avg() ??僅適用數(shù)值型
count() ??max() ??min() ??適用任何類型數(shù)據(jù)
???????select avg(sal), max(sal), min(sal), sum(sal) from emp;
???????select max(hiredate), min(hiredate) from emp;
12.3.3?多行函數(shù)與空值
多行函數(shù)除了count(*)外，都跳過空值而處理非空值
select count(comm),sum(comm),avg(comm) from emp;
可使用IF()函數(shù)強制多行函數(shù)處理空值
select count(IF(comm is null,0,comm)),sum(IF(comm is null,0,comm)),avg(IF(comm is null,0 ,comm)) from emp;
12.3.4?count函數(shù)的說明
count(*)返回組中總記錄數(shù)目；
count(exp)返回表達式exp值非空的記錄數(shù)目；
count(distinct(exp))返回表達式exp值不重復的、非空的記錄數(shù)目。
select count(*) from emp;
select count(comm) from emp;
select count(distinct(deptno)) from emp;
select count(IF(comm?IS null,0,comm)) from emp;
12.4?GROUP BY子句
GROUP BY 子句將表中數(shù)據(jù)分成若干小組
12.4.1?語法格式
select column, group_function(column)
from table
[where condition]
[group by group_by_expression]
[order by column];
12.4.2?使用舉例
select deptno, avg(sal) from emp group by deptno;
select ?deptno,, count(*),avg(sal) from emp group by deptno；
12.4.3?注意事項
出現(xiàn)在SELECT列表中的字段，如果不是包含在多行函數(shù)中，那么該字段必須同時在GROUP BY子句中出現(xiàn)。
錯誤：select ename,deptno,sum(sal) from emp group by deptno;
|--以上的SQL在MYSQL中是不錯的，但是在oracle是錯的，在開發(fā)中避免這樣寫
包含在GROUP BY子句中的字段則不必須出現(xiàn)在SELECT列表中。 ?????????
?
如果沒有GROUP BY子句，SELECT列表中不允許出現(xiàn)字段（單行函數(shù)）與多行函數(shù)混用的情況
select empno, sal from emp; //合法
select avg(sal) from emp; //合法
select empno, avg(sal) from emp; //非法
不允許在WHERE 子句中使用多行函數(shù)
select deptno, avg(sal) ??from emp
where avg(sal) > 2000; ??//執(zhí)行where時尚未執(zhí)行groupby 及其他
group by deptno;
12.5?HAVING子句
對分組查詢的結果進行過濾，要使用having從句。
having從句過濾分組后的結果，它只能出現(xiàn)在group by從句之后，而where從句要出現(xiàn)在group by從句之前。
where過濾行，having過濾分組。having支持所有where操作符。
12.5.1?語法格式
select column, group_function(column)
from table
[where condition]
[group by ?group_by_expression]
[having group_condition]
[order by column];
執(zhí)行過程：from--where -- group ?by– having– select-- order ?by
12.5.2?用法舉例
列出平均工資大于8000元的部門ID
select deptno, avg(sal) ??from emp
group by deptno?having avg(sal) > 8000 ?order by deptno;
12.5.3?思考練習
統(tǒng)計人數(shù)小于4的部門的平均工資。
統(tǒng)計各部門的最高工資，排除最高工資小于8000的部門。
顯示部門編號大于101?的部門的人數(shù)，要求人數(shù)大于3
13.?連接查詢SQL92
13.1?連接查詢概述
??笛卡爾集
??等值連接
??非等值連接
??外連接
??自連接
說明。SQL92是1992年提出的查詢語法，向上兼容
13.2?連接查詢語法
13.2.1?語法規(guī)則：
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
13.2.2?特點
在 WHERE 子句中寫入連接條件
當多個表中有重名列時，必須在列的名字前加上表名作為前綴
13.2.3?連接的類型：
等值連接 -- Equijoin
非等值連接 -- Non-equijoin
左連接 ?--LEFT JOIN
右連接 ?--RIGHT JOIN
自連接 -- Self join
13.3?笛卡爾集
select * from dept;//4條記錄
select * from emp; ;//14條記錄
select * from dept,emp; ;//4*14=56條記錄
總結
檢索出的行的數(shù)目將是第一個表中的行數(shù)乘以第二個表中的行數(shù)
檢索出的列的數(shù)目將是第一個表中的列數(shù)加上第二個表中的列數(shù)
應該保證所有聯(lián)結都有where子句，不然數(shù)據(jù)庫返回比想要的數(shù)據(jù)多得多的數(shù)據(jù)?
?
13.4?等值查詢
select * from dept,emp where dept.deptno=emp.deptno;
select * from dept d,emp e where d.deptno=e.deptno;
select d.deptno,dname,loc,empno,ename,job from dept d,emp e where d.deptno=e.deptno;
select d.deptno,dname,loc,empno,ename,job from dept d,emp e where d.deptno=e.deptno and d.deptno=10
select d.deptno,dname,loc,empno,ename,job from dept d,emp e where d.deptno=e.deptno and loc='武漢';
?
注意點
當被連接的多個表中存在同名字段時，須在該字段前加上"表名."前綴
可使用AND 操作符增加查詢條件；
使用表別名可以簡化查詢
使用表名（表別名）前綴可提高查詢效率；
13.5?非等值查詢
要求：查詢員工的工資等級
select ?empno,ename,job,sal,grade?
from emp e,salgrade s
where e.sal<s.hisal and e.sal>s.losal;
?
select ?empno,ename,job,sal,grade?
from emp e,salgrade s
where e.sal<s.hisal and e.sal>s.losal and e.job='MANAGER';
13.6?自連接
特點：將一個表當兩個表使用
使用舉例：查詢每個員工的工號、姓名、直接領導姓名
特點：將一個表當兩個表使用
使用舉例：查詢每個員工的工號、姓名、經(jīng)理姓名
select e1.empno,e1.ename,e1.job,e2.ename
from emp e1 ,emp e2
where e1.mgr=e2.empno
order by e1.empno;
14.?連接查詢SQL99【重點掌握】
14.1?SQL99語法概述
SQL92的語法規(guī)則的缺點：
語句過濾條件和表連接的條件都放到了where子句中 。
當條件過多時，聯(lián)結條件多，過濾條件多時，就容易造成混淆
SQL99修正了整個缺點，把聯(lián)結條件，過濾條件分開來，包括以下新的TABLE JOIN的句法結構：
??交叉連接（Cross join）
??自然連接（Natural join）
??使用Using子句建立連接
??使用On子句建立連接
??連接（ Outer join ）
n?內(nèi)連接INNER JOIN
n?左連接LEFT JOIN
n?右連接RIGHT JOIN
14.2?語法
select 字段列表
from table1
[cross join table2] | ???????????????????????????????????//1：交叉連接
[natural join table2] | ?????????????????????????????????//2：自然連接
[join table2 using (字段名)] | ?????????????????????//3：using子句
[join table2 on (table1.column_name = table2.column_name)] | //4：on子句
[(left | right | full outer) join table2
on (table1.column_name = table2.column_name)]; //5：左/右/滿外連接
14.3?交叉連接
Cross join產(chǎn)生了一個笛卡爾集，其效果等同于在兩個表進行連接時未使用WHERE子句限定連接條件;
可以使用where條件從笛卡爾集中選出滿足條件的記錄。
用法舉例
select dept.deptno,dname,ename
from dept cross join emp;
等價于
select dept.deptno,dname,ename
from dept,emp;
14.4?自然連接
Natural join基于兩個表中的全部同名列建立連接
??從兩個表中選出同名列的值均對應相等的所有行
??如果兩個表中同名列的數(shù)據(jù)類型不同，則出錯
??不允許在參照列上使用表名或者別名作為前綴
??自然連接的結果不保留重復的屬性
舉例：
select empno, ename, sal, deptno, dname
from emp natural join dept
14.5?Using子句
如果不希望參照被連接表的所有同名列進行等值連接，自然連接將無法滿足要求，可以在連接時使用USING子句來設置用于等值連接的列（參照列）名。
using子句引用的列在sql任何地方不能使用表名或者別名做前綴
舉例：
select e.ename,e.ename,e.sal,deptno,d. dname
from emp e join dept d
????using(deptno)
where deptno=101
14.6?On子句
自然連接的條件是基于表中所有同名列的等值連接
為了設置任意的連接條件或者指定連接的列，需要使用ON子句
連接條件與其它的查詢條件分開書寫
使用ON 子句使查詢語句更容易理解
舉例：
1----select ename,dname
?? from emp inner join ?dept on ??emp.deptno=dept.deptno
?? where ?emp.deptno=30;
2---select empno, ename, sal, emp.deptno, dname
?????from emp inner ?join dept
??????on (emp.deptno = dept.deptno and sal>5000);
3---select *
???from dept, emp
???where dept.deptno = emp.deptno and sal>5000;
第三種是SQL92里面的查詢方法，和上面兩種等價
14.7?外連接
14.7.1?左外聯(lián)接
????兩個表在連接過程中除返回滿足連接條件的行以外，還返回左表中不滿足條件的行，這種連接稱為左外聯(lián)接。
14.7.2?右外聯(lián)接
????兩個表在連接過程中除返回滿足連接條件的行以外，還返回右表中不滿足條件的行，這種連接稱為右外聯(lián)接。
14.7.3?滿外聯(lián)接
????兩個表在連接過程中除返回滿足連接條件的行以外，還返回兩個表中不滿足條件的所有行，這種連接稱為滿外聯(lián)接。
內(nèi)連接:在SQL99規(guī)范中，內(nèi)連接只返回滿足連接條件的數(shù)據(jù)。
14.7.4?外連接舉例
左外連接
select deptno, dname,empno,ename from dept left join emp using(deptno);
右外連接
select deptno, dname,empno,ename from dept right join emp using(deptno);
滿外連接
select deptno, dname,empno,ename from dept full join emp using(deptno);
15.?子查詢
15.1?問題引入
如何查得所有比“CLARK”工資高的員工的信息
select * from emp
?????where sal>(select sal from emp where ename='CLARK');
思考：查詢工資高于平均工資的雇員名字和工資。
思考：查詢和Lucy同一部門且比他工資低的雇員名字和工資。
?
15.2?語法格式：
select 字段列表 ??from table
????where 表達式 ?operator ?(select 字段列表 ?from table);
15.3?特點
子查詢在主查詢前執(zhí)行一次
主查詢使用子查詢的結果
使用子查詢注意事項
在查詢是基于未知值時應考慮使用子查詢
子查詢必須包含在括號內(nèi)
建議將子查詢放在比較運算符的右側，以增強可讀性。
除非進行Top-N 分析，否則不要在子查詢中使用ORDER BY 子句。
如果子查詢返回單行結果，則為單行子查詢，可以在主查詢中對其使用相應的單行記錄比較運算符
如果子查詢返回多行結果，則為多行子查詢，此時不允許對其使用單行記錄比較運算符
?
15.4?單行子查詢
單行子查詢只返回一行記錄
對單行子查詢可使用單行記錄比較運算符
< 、 ?> 、 ?= 、 >=、 ?<= 、 <>
15.4.1?舉例：
select * from emp
where sal > (select sal from emp where empno = 101);
思考：查詢工資最高的雇員名字和工資。
查詢職務和SCOTT相同，比SCOTT雇傭時間早的雇員信息
SELECT ?empno, ename, job FROM emp
WHERE job =(SELECT job FROM emp WHERE empno=7788)
AND hiredate < (SELECT hiredate FROM emp WHERE empno=7788);
查詢工資比SCOTT高或者雇傭時間比SCOTT早的雇員的編號和名字
select empno,ename,sal,hiredate
from emp
where sal>(select sal from emp where ename='SCOTT') or hiredate<(select hiredate from emp where ename='SCOTT')
15.5?多行子查詢
多行子查詢返回多行記錄
對多行子查詢只能使用多行記錄比較運算符
ALL 和子查詢返回的所有值比較
ANY 和子查詢返回的任意一個值比較
IN 等于列表中的任何一個
15.5.1?案例
查詢工資低于任何一個'CLERK'的工資的雇員信息。
查詢工資比所有的 'SALESMAN'都高的雇員的編號、名字和工資。
查詢部門20中職務同部門10的雇員一樣的雇員信息。
15.5.2?案例答案
SELECT ?empno, ename, job,sal
FROM emp
WHERE ??sal < ANY (SELECT sal FROM emp WHERE job = 'CLERK') AND job <> 'CLERK';
?
SELECT ?empno, ename,sal
FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE job= 'SALESMAN');
?
SELECT ?empno, ename, job FROM emp
WHERE ??job IN (SELECT job FROM emp WHERE deptno=10)
AND deptno =20;
15.5.3?查詢在雇員中有哪些人是領導
分析：有哪些人的empno號在mgr這個字段中出現(xiàn)過，應首先查詢mgr中有哪些號碼，然后再看有哪些人的雇員號碼在此出現(xiàn)
select empno, ename from emp
where empno in (
select distinct mgr from emp
);
15.5.4?找出部門編號為20的所有員工中收入最高的職員
select * from emp
where sal >= all(
select sal from emp where deptno = 20)
and deptno = 20
15.5.5?查詢每個部門平均薪水的等級
分析：首先將每個部門平均薪水求出來，然后把結果當成一張表，再用這張結果表和salgrade表做連接，以此求得薪水等級
先求出每個部門平均薪水的表t。
將t和salgrade進行關聯(lián)查詢就可以了。
select * from
salgrade s, (select deptno,avg(sal) avg_sal
from emp group by deptno) t
where ?t.avg_sal between s.losal and s.hisal;
16.?視圖
16.1?視圖的定義及作用
16.1.1?定義
???????視圖是從若干基本表和（或）其他視圖構造出來的表。
在創(chuàng)建一個視圖時，只是存放的視圖的定義，也即是動態(tài)檢索數(shù)據(jù)的查詢語句，而并不存放視圖對應的數(shù)據(jù)
在用戶使用視圖時才去求相對應的數(shù)據(jù)。所以視圖被稱作“虛表”
16.1.2?作用
???????可以限制對數(shù)據(jù)的訪問，可以給用戶授予表的特定部分的訪問權限而不是整個表的訪問權限。
可以使復雜的查詢變的簡單。在編寫查詢后，可以方便地重用它而不必知道他的基本查詢細節(jié)。
提供了對相同數(shù)據(jù)的不同顯示
16.2?視圖的創(chuàng)建和刪除
16.2.1?創(chuàng)建單表視圖
CREATE VIEW MYVIEW1
AS
SELECT * FROM EMP;
16.2.2?創(chuàng)建或 修改一個單表視圖
CREATE 【OR REPLACE】?VIEW MYVIEW2
AS
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO
FROM EMP
WHERE DEPTNO >=102
16.2.3?使用視圖添加和修改數(shù)據(jù)
????INSERT INTO MYVIEW1 (EMPNO,ENAME,SAL)VALUES(9999,'AAAA',3000);
16.2.4?為視圖添加相關權限
CREATE OR REPLACE VIEW MYVIEW2
AS
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO
FROM EMP
WHERE DEPTNO >=20
16.2.5?刪除視圖
DROP VIEW MYVIEW4;
16.2.6?統(tǒng)計視圖
CREATE OR REPLACE VIEW MYVIEW4
AS
SELECT DEPTNO 部門編號,DNAME 部門名稱,AVG(SAL) 平均工資,MAX(SAL) 最高工資,COUNT(*)人數(shù)
FROM EMP E
JOIN DEPT D USING(DEPTNO)
WHERE DEPTNO IS NOT NULL
GROUP BY DEPTNO，DNAME
ORDER BY AVG(SAL)
16.2.7?基于其它視圖的視圖
CREATE OR REPLACE VIEW MYVIEW5
AS
SELECT * FROM MYVIEW4
WHERE 部門編號=10
?
16.3?視圖的總結
1.視圖對應一個查詢語句；視圖是（從若干基本表和（或）其他視圖構造出來的）表
2.視圖進行查詢，添加，修改，刪除，其實就是對背后的表進行相應操作
3 虛表 在創(chuàng)建一個視圖時，只是存放的視圖的定義，也即是動態(tài)檢索數(shù)據(jù)的查詢語句，而并不存放視圖對應的數(shù)據(jù)
4.視圖的好處
??????1.安全 可以只顯示部分行部分列的數(shù)據(jù);可以對添加的數(shù)據(jù)進行檢查；可以設置為只讀視圖
??????2.操作簡單
?列出薪金高于公司平均薪金的所有員工。
3.只顯示多個數(shù)據(jù)庫表的部分列，部分行的視圖
17.?MySQL存儲引擎
17.1?InnoDB 引擎(MySQL5.5以后默認使用)
MySQL 5.5 及以后版本中的默認存儲引擎，他的優(yōu)點如下：
災難恢復性好
支持事務
使用行級鎖
支持外鍵關聯(lián)
支持熱備份
對于InnoDB引擎中的表，其數(shù)據(jù)的物理組織形式是簇表（Cluster Table），主鍵索引和數(shù)據(jù)是在一起的，數(shù)據(jù)按主鍵的順序物理分布
實現(xiàn)了緩沖管理，不僅能緩沖索引也能緩沖數(shù)據(jù)，并且會自動創(chuàng)建散列索引以加快數(shù)據(jù)的獲取
支持熱備份
17.2?MyISAM引擎
特性如下：
不支持事務
使用表級鎖，并發(fā)性差
主機宕機后，MyISAM表易損壞，災難恢復性不佳
可以配合鎖，實現(xiàn)操作系統(tǒng)下的復制備份、遷移
只緩存索引，數(shù)據(jù)的緩存是利用操作系統(tǒng)緩沖區(qū)來實現(xiàn)的。可能引發(fā)過多的系統(tǒng)調(diào)用且效率不佳
數(shù)據(jù)緊湊存儲，因此可獲得更小的索引和更快的全表掃描性能
17.3?兩種存儲引擎的大致區(qū)別表現(xiàn)在：
1）InnoDB支持事務，MyISAM不支持，這一點是非常之重要。事務是一種高級的處理方式，如在一些列增刪改中只要哪個出錯還可以回滾還原，而MyISAM就不可以了。
2）MyISAM適合查詢以及插入為主的應用，InnoDB適合頻繁修改以及涉及到安全性較高的應用
3）InnoDB支持外鍵，MyISAM不支持
4）從MySQL5.5.5以后，InnoDB是默認引擎
5）InnoDB不支持FULLTEXT類型的索引
6）InnoDB中不保存表的行數(shù)，如select count(*) from table時，InnoDB需要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。注意的是，當count(*)語句包含where條件時MyISAM也需要掃描整個表。
7）對于自增長的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中可以和其他字段一起建立聯(lián)合索引。
8）清空整個表時，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會重建表。
9）InnoDB支持行鎖（某些情況下還是鎖整表，如 update table set a=1 where user like '%lee%'
有人說MYISAM只能用于小型應用，其實這只是一種偏見。
如果數(shù)據(jù)量比較大，這是需要通過升級架構來解決，比如分表分庫，讀寫分離，而不是單純地依賴存儲引擎。
現(xiàn)在一般都是選用InnoDB了，主要是MyISAM的全表鎖，讀寫串行問題，并發(fā)效率鎖表，效率低，MyISAM對于讀寫密集型應用一般是不會去選用的。
17.4?總結
1.MyISAM類型不支持事務處理等高級處理，而InnoDB類型支持。
2.MyISAM類型的表強調(diào)的是性能，其執(zhí)行速度比InnoDB類型更快，但是不提供事務支持，而InnoDB提供事務支持已經(jīng)外部鍵等高級數(shù)據(jù)庫功能。
17.5?其它說明
存儲引擎：數(shù)據(jù)在數(shù)據(jù)庫中的組織方式（存儲方式）。
例如：
create table test_table(
id int(11)
)ENGINE=INNODB ,DEFAULT CHARSET = UTF8;
創(chuàng)建表的時候指定存儲引擎為INNODB，這個存儲引擎在MySQL中用的非常廣泛，因為它支持外鍵、支持事務、支持行級鎖。
查看MySQL支持的存儲引擎命令如下：
SHOW ENGINES;
?
除了上述操作之外，還可以在數(shù)據(jù)庫安裝盤的my.ini文件可以配置數(shù)據(jù)庫表默認的存儲引擎。
default-storage-engine=INNODB ;
?
18.?MySQL事務
18.1?概念
完成一個事情需要的一系列步驟(操作)，這些操作要么同時成功，要么同時失敗
18.2?事務的基本操作
場景：有兩個賬戶分別為張三和李四，他們默認都有1000塊錢的余額。在這兩個賬戶之間進行轉賬。
-- 1 創(chuàng)建tb_account表
create table tb_account(
id int(11) auto_increment comment '主鍵',
uName varchar(20) comment '用戶名',
account varchar(30) ?comment '銀行卡賬號',
pwd varchar(30) comment '銀行卡密碼',
account_blance ?float(12,2) comment '賬戶余額',
primary key (id) ???
);
insert into tb_account values
(null,'寶強','110','123456',1000),
(null,'馬蓉','120','123456',1),
(null,'宋喆','911','123456',0.5);
?
-- 轉賬: 寶強轉賬1000萬給馬蓉
-- 操作1：娃娃
update tb_account set account_blance=account_blance-1000 where account='110' and pwd='123456';
-- 操作2: 打氣筒
update tb_account set account_blance=account_blance+1000 where account='120' and pwd='123456';
如何解決上面的問題呢？
使用事務來介入(管理)轉賬的操作
18.3?事務的四大特征(ACID)
原子性：事務每一步都是不可再分
一致性：張三和李四賬戶一共2000塊錢，不管轉賬多少次總金額不變
持久性：當一個事務執(zhí)行成功(完畢)，數(shù)據(jù)會持久化到磁盤的數(shù)據(jù)文件中。例如轉賬成功：張三余額變?yōu)?00，李四余額變?yōu)?500.
隔離性：A事務和B事務同時操作一份數(shù)據(jù)，相互之間不影響。
18.4?事務的提交方式
1 自動提交，MySQL默認為自動提交。
?不需要寫commit;就會自動將DML語句持久化提交
2 手動提交，Oracle默認手動提交。
如何在MySQL中查看提交方式：
-- 查詢結果為1表示自動提交，0表示手動提交
select @@autocommit;
-- 修改提交方式（自動提交修改為手動提交）
set @@autocommit = 0 ;
18.5?事務的基本操作
1 開啟事務
start transaction;
2 提交事務
commit;
3 回滾事務
rollback;
注意：一旦使用start transaction;開啟事務那么自動提交將失效
??如果所有操作都正常執(zhí)行使用commit;提交事務
??當發(fā)生異常情況回滾事務，數(shù)據(jù)(此時為tb_account表)通?；貪L到開啟事務之前的狀態(tài)
18.6?轉賬操作
-- 1 開啟事務
start transaction;
-- 2 執(zhí)行SQL語句
update tb_account set account_blance=account_blance-500 where id=1;
?手機轉賬異常情況：轉賬過程中手機沒電了
update tb_account set account_blance=account_blance+500 where id=2;
-- 3 如果SQL語句全部執(zhí)行成功就提交事務，如果其中任何一步執(zhí)行失敗，立刻回滾事務
-- 此時第一個update執(zhí)行成功，第二個update語句執(zhí)行失敗了,并沒有提交事務，查詢結果如下：
select * from tb_account;
/*
+----+-----------+----------------+
| id | user_name | account_blance |
+----+-----------+----------------+
| ?1 | ZS ???????| ???????????500 |
| ?2 | LS ???????| ??????????1000 |
+----+-----------+----------------+
*/
-- 問題：張三賬戶的余額減少了，李賬戶余額沒有增加。這就是臟數(shù)據(jù)
-- 此時需要將臟數(shù)據(jù)回滾到開啟事務之前
rollback;
-- 回滾完畢再次查詢
select * from tb_account;
-- 此時事務回滾到開啟之前的狀態(tài)
/*
+----+-----------+----------------+
| id | user_name | account_blance |
+----+-----------+----------------+
| ?1 | ZS ???????| ??????????1000 |
| ?2 | LS ???????| ??????????1000 |
+----+-----------+----------------+
*/
-- 一個事務一旦開啟了，在沒有執(zhí)行commit;或者rollback;之前事務不會結束
-- 相關面試題：工作中有沒有用到事務？請解釋事務的概念？不使用事務會發(fā)生什么問題？使用事務能夠解決什么問題？解釋事務的四大特征[隔離級別]？
18.7?事務隔離級別
18.7.1?ISO定義的四大隔離級別
查詢隔離級別select @@tx_isolation?
隔離級別1最低，4最高 。隔離級別越高就越安全，同時內(nèi)存資源消耗也越大。隔離級別越高效率越低下。
工作中：1和4都不用，只會在2和3之間切換
MySQL默認的事務隔離級別為3，Oracle默認隔離界別默認為2
18.7.2?并發(fā)下的臟讀，不可重復讀，幻讀的問題
問題：不使用事務隔離級別會引發(fā)啥問題？使用事務隔離級別能夠解決什么問題
18.7.2.1?臟讀問題
一個事務(A)讀取到另一個事務(B)沒有提交的數(shù)據(jù)（破壞了隔離性）.
??例如：事務A開啟事務做轉賬，DML語句執(zhí)行成功但是沒有commit；事務B在另一個窗口開啟了，執(zhí)行Select語句讀取tb_account數(shù)據(jù)，讀取到的結果是事務A沒有提交的數(shù)據(jù)。
18.7.2.2?不可重復讀問題
同一個事務中多次讀取到的數(shù)據(jù)不一致(破壞了一致性，update和delete)
??例如：事務A開啟事務做轉賬，DML語句執(zhí)行成功但是沒有commit；；事務B在另一個窗口開啟了，執(zhí)行Select語句讀取tb_account數(shù)據(jù)，讀取的結果正確（1000,1000）.
??事務A里面提交了事務。然后事務B再次做Select操作查詢結果也正確(500,1500)
??問題：事務B在一次事務中對tb_account表做了兩次select操作，兩次操作查詢的結果不一致。
18.7.2.3?幻讀問題
事務A插入一條數(shù)據(jù)，能夠使用select獲取結果，此時事務B幾乎同時插入了一條或者大量數(shù)據(jù)，此時事務A看不到事務B的更新（破壞了一致性，insert）。
查看事務隔離級別：
-- REPEATABLE-READ ?可重復讀
-- tx 表示事務的簡稱 ?transaction
-- isolation表示隔離性
select @@tx_isolation;
-- 設置事務的隔離級別 讀未提交
set session?transaction isolation level ?READ UNCOMMITTED;
18.7.3?read uncommitted臟讀測試
使用隔離級別為read uncommitted;完成轉賬，目的觀察會出現(xiàn)臟讀問題，如何解決？
-- 開啟事務
start transaction;
-- 執(zhí)行事務的轉賬操作
update tb_account set account_blance=account_blance-500 where id=1;
update tb_account set account_blance=account_blance+500 where id=2;
?
18.7.4?read uncommitted臟讀問題解決
如何解決臟讀問題？修改事務隔離級別：讀已提交
set session?transaction isolation level read committed;
進行轉賬操作
-- 開啟事務
start transaction;
-- 執(zhí)行事務的轉賬操作
update tb_account set account_blance=account_blance-500 where id=1;
update tb_account set account_blance=account_blance+500 where id=2;
?
?
18.7.5?如何解決不可重復讀的問題
將事務隔離級別設置為"可重復讀 " repeatable read , 解決不可重復讀的問題。
set session?transaction isolation level repeatable read;
-- 還原為默認值
update tb_account set account_blance=1000;
-- 開啟事務
start transaction;
-- 執(zhí)行事務的轉賬操作
update tb_account set account_blance=account_blance-500 where id=1;
update tb_account set account_blance=account_blance+500 where id=2;
?
18.7.6?幻讀問題演示
事務A插入一條數(shù)據(jù)，能夠使用select獲取結果，此時事務B幾乎同時插入了一條或者大量數(shù)據(jù)，此時事務A看不到事務B的更新（破壞了一致性，insert）。
18.7.7?串行化serializable測試
能夠解決所有的問題，但是效率低下，它類似Java的synchronized
Java使用synchronized用來鎖對象。MySQL使用serializable鎖表，事務A開啟事務，做了DML操作，但是沒有提交。此時事務B開啟事務，執(zhí)行select操作，沒有查詢到數(shù)據(jù)，因為此時tb_account表被事務A占用了(鎖住了)。
set session?transaction isolation level serializable;
?
?
19.?SQL優(yōu)化之索引
19.1?索引是啥
??MySQL索引的建立對于MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。
??打個比方，如果合理的設計且使用索引的MySQL是一輛蘭博基尼的話，那么沒有設計和使用索引的MySQL就是一個人力三輪車。
??拿漢語字典的目錄頁（索引）打比方，我們可以按拼音、筆畫、偏旁部首等排序的目錄（索引）快速查找到需要的字。
??索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索引包含多個列。
??創(chuàng)建索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。
??實際上，索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄。
??上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。
??建立索引會占用磁盤空間的索引文件。
19.2?準備工作-創(chuàng)建person表
-- ----------------------------
-- 創(chuàng)建Person表
-- ----------------------------
DROP TABLE IF EXISTS person;
CREATE TABLE person ?(
??PID int(11) NOT NULL AUTO_INCREMENT COMMENT '編號',
??PNAME varchar(50) ?NOT NULL COMMENT '姓名',
??PSEX varchar(10) NULL DEFAULT NULL COMMENT '性別',
PAGE int(11) ?NOT NULL COMMENT '年齡',
??SAL decimal(7, 2) NULL DEFAULT NULL COMMENT '工資',
??PRIMARY KEY (PID)
) ENGINE = InnoDB AUTO_INCREMENT = 7001 CHARACTER SET = utf8mb4 COMMENT = '人員表';
19.3?索引分類
19.3.1?普通索引
如何創(chuàng)建普通索引？
-- 語法
-- CREATE INDEX 索引名稱 on 表名稱(列名稱);
場景1：為person表的pname列建立普通索引
CREATE INDEX INDEX_PERSON_PNAME?ON PERSON(PNAME);
如何查看SQL語句是否使用索引？可以使用EXPLAIN關鍵字來查看
EXPLAIN select * from person where pname= 'Jack';
在MySQL中為表創(chuàng)建主鍵的同時默認也創(chuàng)建了一個索引
-- 下面的DQL語句使用了索引。
EXPLAIN
select * from?person
where id =1;
?場景2：where pname like 模糊查詢用到索引沒有
-- 前后模糊中間精確不會使用索引
EXPLAIN
select ?* from person where pname like '%e%';
-- 前面模糊后面精確也不會使用索引
EXPLAIN
select ?* from person where pname like '%e';
-- 只有前面精確后面模糊才會使用索引，工作中數(shù)據(jù)量大的表模糊查詢盡量不要使用 '%%',也不要使用'%a',他們都不支持索引
EXPLAIN
select ?* from person where pname like 'e%';
19.3.2?索引壓力測試
19.3.2.1?場景1
向person表中插入500W條數(shù)據(jù),為pname創(chuàng)建一個索引，觀察創(chuàng)建索引之前執(zhí)行耗時是多長時間，創(chuàng)建索引之后耗時多長時間。
刪除之前的pname的索引
ALTER TABLE PERSON DROP INDEX INDEX_PERSON_PNAME;
插入500W條數(shù)據(jù)
create procedure insert_person(in max_num int(10))
begin
declare i int default 0;
set autocommit = 0; -- 把autocommit設置成0，這樣可以只提交一次，否則。。。。。
repeat
set i = i +1;
insert into person (PID,PNAME,PSEX,PAGE,SAL) values (i,rand_string(5),IF(RAND()>0.5,'男','女'),FLOOR((RAND()*100)+10),FLOOR((RAND()*19000)+1000));
until i = max_num
end repeat;
commit;
end;
-- 調(diào)用
call insert_person(5000000);
-- 我查詢耗時??
不創(chuàng)建索引測試
select * from person where PNAME="oQlJN"; ??-- 耗時
查看索引文件
創(chuàng)建索引測試
CREATE INDEX INDEX_PERSON_PNAME?ON PERSON(PNAME);
創(chuàng)建耗時
查詢測試
select * from person where PNAME="oQlJN"; ??-- 耗時
再次查看索引文件
?
問題1：為person表的pname創(chuàng)建索引消耗了9.472秒，為什么耗時這么長？
在person.ibd數(shù)據(jù)文件中創(chuàng)建了一個“目錄”，目錄的結構是一個B+Tree，類似Java的TreeMap，表的數(shù)據(jù)越多，索引結構越大，創(chuàng)建時間越長。
問題2：沒有創(chuàng)建索引執(zhí)行WHERE PNAME耗時3.892秒，創(chuàng)建索引執(zhí)行耗時0.03秒，為什么這么快？
沒有創(chuàng)建索引，進行全文檢索(從person.ibd數(shù)據(jù)文件中檢索所有的輸入，然后在判斷是否滿足where條件)，創(chuàng)建索引不進行全文檢索，直接查詢索引
問題3：創(chuàng)建索引之前數(shù)據(jù)文件person.ibd大小229376，創(chuàng)建索引之后大小319488，為什么會有這么大的變化？
創(chuàng)建索引就相當于創(chuàng)建目錄，表中的數(shù)據(jù)越大，數(shù)據(jù)文件就越多。
問題4：INDEX_PERSON_PNAME索引的內(nèi)部結構是啥？
類似TreeMap的B+Tree
?
19.3.2.2?場景2
如何創(chuàng)建一個復合索引，例如：我要對年齡和性別兩個列創(chuàng)建復合索引
-- 為多個列創(chuàng)建復合索引
CREATE INDEX INDEX_PERSON_AGE_SEX ON PERSON(PAGE,PSEX);
-- 沒有創(chuàng)建索執(zhí)行下面DQL語句引耗時1.427秒，創(chuàng)建索引執(zhí)行耗時1毫秒
SELECT * FROM PERSON WHERE PAGE =22 AND PSEX='男';
-- 創(chuàng)建的復合索引，但是只對第二個索引列單獨進行where條件，也會使用索引
-- 如果創(chuàng)建復合索引，經(jīng)常使用的列放在前面
EXPLAIN
SELECT * FROM PERSON WHERE ?PSEX='男';
19.3.2.3?場景3
PERSON表的PSEX列只有2個有效值，為該列建立索引會提高查詢效率嗎？
CREATE INDEX INDEX_CUSTOMER_SEX?ON PERSON(PSEX);
-- 沒有建立索引耗時2.7秒
-- 建立索引耗時之后耗時12.5秒
SELECT * FROM PERSON WHERE ?PSEX='男';
19.3.2.4?小結
1如果為某個列創(chuàng)建索引，那么就會在數(shù)據(jù)文件中創(chuàng)建一個類似TreeMap的文件。如果一個表的數(shù)據(jù)很多，那么索引會大量的占據(jù)數(shù)據(jù)文件的磁盤空間。
2不是所有的列都適合建立索引，如果某個列的有效數(shù)據(jù)很少不要建立索引 。?
3可以為表的多個列創(chuàng)建復合索引，經(jīng)常使用的列放在前面。
4創(chuàng)建主鍵的同時默認也創(chuàng)建了一個索引。
19.3.3?唯一索引
唯一索引關鍵字：?unique
之前學習了唯一約束，當我們創(chuàng)建了一個唯一約束的時候就創(chuàng)建了一個唯一索引，唯一約束就是唯一索引。
場景：對CUSTOMER_BAK3表的CUST_MOBILE列創(chuàng)建唯一索引，先觀察沒有創(chuàng)建索引執(zhí)行WHERE CUST_MOBILE=XXX條件耗時多長時間，然后創(chuàng)建唯一索引，最后觀察創(chuàng)建索引執(zhí)行WHERE CUST_MOBILE=XXX條件耗時多長時間耗時多長時間。
前置條件：先刪除唯一約束
-- 刪除唯一索引
ALTER TABLE PERSON?DROP INDEX UNIQUE_INDEX_PERSON_MOBILE;
-- 創(chuàng)建唯一索引語法
-- CREATE UNIQUE INDEX 索引名稱 ON 表名稱(列名稱);
-- PERSON表有500W數(shù)據(jù)，創(chuàng)建唯一索引耗時：16.491秒
CREATE UNIQUE INDEX UNIQUE_INDEX_PERSON_MOBILE ON PERSON(MOBILE);
-- 沒有創(chuàng)建唯一索引耗時1.8秒，創(chuàng)建唯一索引查詢耗時2毫秒
EXPLAIN
select * from PERSON where mobile = '15004613234';
小結：工作中如果表中的某個列數(shù)據(jù)全部唯一，可以考慮創(chuàng)建唯一索引
19.3.4?組合索引
-- 為多個列創(chuàng)建復合索引
ALTER TABLE PERSON ?DROP ?INDEX INDEX_PERSON_AGE_SEX;
CREATE INDEX INDEX_PERSON_AGE_SEX ON PERSON(PNAME,PSEX);
-- 沒有創(chuàng)建索執(zhí)行下面DQL語句引耗時11.427秒，創(chuàng)建索引執(zhí)行耗時1毫秒
SELECT * FROM PERSON WHERE PNAME ="oQlJN" AND PSEX='男';
?EXPLAIN SELECT * FROM PERSON WHERE ?PSEX='男' -- AND PNAME ="oQlJN";
-- 創(chuàng)建的復合索引，但是只對第二個索引列單獨進行where條件，不會使用索引
-- 如果創(chuàng)建復合索引，經(jīng)常使用的列放在前面，并且查詢時一定要帶上第一列的條件
?
19.3.5?全文索引【了解】 ?
【概述】
通過數(shù)值比較、范圍過濾等就可以完成絕大多數(shù)我們需要的查詢，但是，如果希望通過關鍵字的匹配來進行查詢過濾，那么就需要基于相似度的查詢，而不是原來的精確數(shù)值比較。全文索引就是為這種場景設計的。
你可能會說，用 like + % 就可以實現(xiàn)模糊匹配了，為什么還要全文索引？like + % 在文本比較少時是合適的，但是對于大量的文本數(shù)據(jù)檢索，是不可想象的。全文索引在大量的數(shù)據(jù)面前，能比 like + % 快 N 倍，速度不是一個數(shù)量級，但是全文索引可能存在精度問題。
你可能沒有注意過全文索引，不過至少應該對一種全文索引技術比較熟悉：各種的搜索引擎。雖然搜索引擎的索引對象是超大量的數(shù)據(jù)，并且通常其背后都不是關系型數(shù)據(jù)庫，不過全文索引的基本原理是一樣的。
【版本支持】
開始之前，先說一下全文索引的版本、存儲引擎、數(shù)據(jù)類型的支持情況
??MySQL 5.6 以前的版本，只有 MyISAM 存儲引擎支持全文索引；
??MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存儲引擎均支持全文索引;
??只有字段的數(shù)據(jù)類型為 char、varchar、text 及其系列才可以建全文索引。
【使用】
工作中用的不多不是重點。
關鍵字: fulltext
-- 語法：
-- CREATE FULLTEXT INDEX 索引名稱 ON 表名稱(列名稱);
-- 示例：
-- 為EMP表的ENAME列創(chuàng)建全文索引
CREATE FULLTEXT INDEX FULLTEXT_EMP_ENAME?ON EMP(ENAME);
-- 測試
EXPLAIN select * from EMP WHERE ?MATCH(ENAME) ?AGAINST('JACK')
坑點：只有建立了全文索引后，才能使用?MATCH(ENAME) ?AGAINST('JACK') 的語法
19.4?刪除索引
-- 語法
-- ALTER TABLE 表名稱 DROP INDEX 索引名稱；
-- 示例
ALTER TABLE PERSON DROP INDEX INDEX_PERSON_PNAME;
20.?數(shù)據(jù)庫設計原則
20.1?引入三大范式
20.1.1?必須保證數(shù)據(jù)庫設計的合理性
數(shù)據(jù)庫設計關系整個系統(tǒng)的架構，關系到后續(xù)的開發(fā)效率和運行效率
數(shù)據(jù)庫的設計主要包含了設計表結構和表之間的聯(lián)系
20.1.2?如何是合理數(shù)據(jù)庫
結構合理
冗余較小
盡量避免插入刪除修改異常
20.1.3?如何才能保證數(shù)據(jù)庫設計水平
遵循一定的規(guī)則
在關系型數(shù)據(jù)庫中這種規(guī)則就稱為范式
20.1.4?什么是范式（NF= NormalForm）
范式是符合某一種設計要求的總結。
要想設計一個結構合理的關系型數(shù)據(jù)庫，必須滿足一定的范式。
20.1.5?范式的分類
第一范式
第二范式
第三范式
Boyce Codd范式=NCNF
由Boyce和Codd提出的,
比3NF又進了一步
通常認為是修正的第三范式.
第四范式
第五范式
?
各個范式是依次嵌套包含的
范式越高，設計質(zhì)量越高，在現(xiàn)實設計中也越難實現(xiàn)
一般數(shù)據(jù)庫設計，只要達到第三范式，即可避免異常的出現(xiàn)
?
?
?
20.2?第一范式
20.2.1?要求
最基本的范式
數(shù)據(jù)庫表每一列都是不可分割的基本數(shù)據(jù)項，同一列中不能有多個值
簡單說就是要確保每列保持原子性
?
第一范式的合理遵循需要根據(jù)系統(tǒng)的實際需求來定
?
20.2.2?示例
用戶表（用戶名，家庭地址）
????????????????|
用戶表（用戶名，省，城市，詳細地址）
?
系（系名稱，系主任，系高級職稱人數(shù)）
????????????????|
系（系名稱，系主任，系教授人數(shù)，系副教授人數(shù)）
20.3?第二范式
20.3.1?要求
第二范式需要確保數(shù)據(jù)庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯(lián)合主鍵而言）。
即在一個數(shù)據(jù)庫表中只能保存一種數(shù)據(jù)，不可以把多種數(shù)據(jù)保存在同一張數(shù)據(jù)庫表中。
20.3.2?示例
學號和課程編號作為聯(lián)合主鍵
課程名稱只依賴于課程編號，而和學號沒有關系
?
?分析以上的設計發(fā)現(xiàn)數(shù)據(jù)冗余
如何解決呢？
提取出學生表
提取成課程表
提取選課表，存放選課記錄
1，學生表
2，課程表
3，選課表
20.4?第三范式
20.4.1?要求
確保數(shù)據(jù)表中的每一列數(shù)據(jù)都和主鍵直接相關，而不能間接相關
屬性不依賴于其他非主屬性。
20.4.2?示例1：學生班級表
分析以上的表，發(fā)現(xiàn)有問題存在 ?班級名稱和班級信息出現(xiàn)了數(shù)據(jù)冗余
如何解決
學生表
班級表
1，學生表
?
2，班級表
20.5??范式的優(yōu)缺點
20.5.1?優(yōu)點
結構合理
冗余較小
盡量避免插入刪除修改異常
20.5.2?缺點
性能降低
多表查詢比單表查詢速度慢
?
20.5.3?數(shù)據(jù)庫的設計應該根據(jù)當前情況和需求做出靈活的處理。
在實際設計中，要整體遵循范式理論。
如果在某些特定的情況下還死死遵循范式也是不可取的，因為可能降低數(shù)據(jù)庫的效率，此時可以適當增加冗余而提高性能。
20.5.4?示例：
比如經(jīng)常購物車條目的中除了條目編號，商品編號，商品數(shù)量外，可以增加經(jīng)常使用的商品名稱，商品價格等
商品表
訂單明細表
其它這個設計在開發(fā)中還要優(yōu)化----思考哦
21.?設計一個商品入庫審批的數(shù)據(jù)庫
22.?設計一個商品購物車的數(shù)據(jù)庫
更多干貨我們下期再說！
下期會分享
第三章節(jié)
JDBC
相關知識~
下期見！
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
