MySQL基礎

本單元目标

一、為什麼要學習資料庫
二、資料庫的相關概念      
    DBMS、DB、SQL
三、資料庫存儲資料的特點
四、初始MySQL
    MySQL産品的介紹        
    MySQL産品的安裝                  
    MySQL服務的啟動和停止     
    MySQL服務的登入和退出           
    MySQL的常見指令和文法規範      
五、DQL語言的學習                 
    基礎查詢                     
    條件查詢                     
    排序查詢                         
    常見函數                       
    分組函數                      
    分組查詢                       
    連接配接查詢                     
    子查詢       √                  
    分頁查詢                     
    union聯合查詢    √            
    
六、DML語言的學習                 
    插入語句                        
    修改語句                        
    删除語句                        
七、DDL語言的學習  
    庫和表的管理     √                
    常見資料類型介紹  √          
    常見限制        √            
八、TCL語言的學習
    事務和事務處理                 
九、視圖的講解           √
十、變量                      
十一、存儲過程和函數   
十二、流程控制結構       
           

資料庫的好處

1.持久化資料到本地
2.可以實作結構化查詢，友善管理

           

資料庫相關概念

1、DB：資料庫，儲存一組有組織的資料的容器
2、DBMS：資料庫管理系統，又稱為資料庫軟體（産品），用于管理DB中的資料
3、SQL:結構化查詢語言，用于和DBMS通信的語言
           

資料庫存儲資料的特點

1、将資料放到表中，表再放到庫中
2、一個資料庫中可以有多個表，每個表都有一個的名字，用來辨別自己。表名具有唯一性。
3、表具有一些特性，這些特性定義了資料在表中如何存儲，類似java中 “類”的設計。
4、表由列組成，我們也稱為字段。所有表都是由一個或多個列組成的，每一列類似java 中的”屬性”
5、表中的資料是按行存儲的，每一行類似于java中的“對象”。
           

MySQL産品的介紹和安裝

MySQL服務的啟動和停止

方式一：計算機——右擊管理——服務
方式二：通過管理者身份運作
net start 服務名（啟動服務）
net stop 服務名（停止服務）
           

MySQL服務的登入和退出

方式一：通過mysql自帶的用戶端
隻限于root使用者

方式二：通過windows自帶的用戶端
登入：
mysql 【-h主機名 -P端口号 】-u使用者名 -p密碼

退出：
exit或ctrl+C
           

MySQL的常見指令

1.檢視目前所有的資料庫
show databases;
2.打開指定的庫
use 庫名
3.檢視目前庫的所有表
show tables;
4.檢視其它庫的所有表
show tables from 庫名;
5.建立表
create table 表名(

    列名 列類型,
    列名 列類型，
    。。。
);
6.檢視表結構
desc 表名;
           

7.檢視伺服器的版本
方式一：登入到mysql服務端
select version();
方式二：沒有登入到mysql服務端
mysql --version
或
mysql --V
           

MySQL的文法規範

1.不區分大小寫,但建議關鍵字大寫，表名、列名小寫
2.每條指令最好用分号結尾
3.每條指令根據需要，可以進行縮進 或換行
4.注釋
    單行注釋：#注釋文字
    單行注釋：-- 注釋文字
    多行注釋：/* 注釋文字  */



           

SQL的語言分類

DQL（Data Query Language）：資料查詢語言
    select 
DML(Data Manipulate Language):資料操作語言
    insert 、update、delete
DDL（Data Define Languge）：資料定義語言
    create、drop、alter
TCL（Transaction Control Language）：事務控制語言
    commit、rollback

           

SQL的常見指令

show databases； 檢視所有的資料庫
use 庫名； 打開指定 的庫
show tables ; 顯示庫中的所有表
show tables from 庫名;顯示指定庫中的所有表
create table 表名(
    字段名 字段類型,    
    字段名 字段類型
); 建立表

desc 表名; 檢視指定表的結構
select * from 表名;顯示表中的所有資料
           

DQL語言的學習

進階1：基礎查詢

文法：
SELECT 要查詢的東西
【FROM 表名】;

類似于Java中 :System.out.println(要列印的東西);
特點：
①通過select查詢完的結果 ，是一個虛拟的表格，不是真實存在
② 要查詢的東西 可以是常量值、可以是表達式、可以是字段、可以是函數
           

進階2：條件查詢

條件查詢：根據條件過濾原始表的資料，查詢到想要的資料
文法：
select 
    要查詢的字段|表達式|常量值|函數
from 
    表
where 
    條件 ;

分類：
一、條件表達式
    示例：salary>10000
    條件運算符：
    > < >= <= = != <>

二、邏輯表達式
示例：salary>10000 && salary<20000

邏輯運算符：

    and（&&）:兩個條件如果同時成立，結果為true，否則為false
    or(||)：兩個條件隻要有一個成立，結果為true，否則為false
    not(!)：如果條件成立，則not後為false，否則為true

三、模糊查詢
示例：last_name like 'a%'
           

進階3：排序查詢

文法：
select
    要查詢的東西
from
    表
where 
    條件

order by 排序的字段|表達式|函數|别名 【asc|desc】

           

進階4：常見函數

一、單行函數
1、字元函數
    concat拼接
    substr截取子串
    upper轉換成大寫
    lower轉換成小寫
    trim去前後指定的空格和字元
    ltrim去左邊空格
    rtrim去右邊空格
    replace替換
    lpad左填充
    rpad右填充
    instr傳回子串第一次出現的索引
    length 擷取位元組個數
    
2、數學函數
    round 四舍五入
    rand 随機數
    floor向下取整
    ceil向上取整
    mod取餘
    truncate截斷
3、日期函數
    now目前系統日期+時間
    curdate目前系統日期
    curtime目前系統時間
    str_to_date 将字元轉換成日期
    date_format将日期轉換成字元
4、流程控制函數
    if 處理雙分支
    case語句 處理多分支
        情況1：處理等值判斷
        情況2：處理條件判斷
    
5、其他函數
    version版本
    database目前庫
    user目前連接配接使用者
           

二、分組函數

sum 求和
    max 最大值
    min 最小值
    avg 平均值
    count 計數

    特點：
    1、以上五個分組函數都忽略null值，除了count(*)
    2、sum和avg一般用于處理數值型
        max、min、count可以處理任何資料類型
    3、都可以搭配distinct使用，用于統計去重後的結果
    4、count的參數可以支援：
        字段、*、常量值，一般放1

       建議使用 count(*)
           

進階5：分組查詢

文法：
select 查詢的字段，分組函數
from 表
group by 分組的字段


特點：
1、可以按單個字段分組
2、和分組函數一同查詢的字段最好是分組後的字段
3、分組篩選
        針對的表    位置            關鍵字
分組前篩選：    原始表        group by的前面        where
分組後篩選：    分組後的結果集    group by的後面        having

4、可以按多個字段分組，字段之間用逗号隔開
5、可以支援排序
6、having後可以支援别名
           

進階6：多表連接配接查詢

笛卡爾乘積：如果連接配接條件省略或無效則會出現
解決辦法：添加上連接配接條件
           

一、傳統模式下的連接配接 ：等值連接配接——非等值連接配接

1.等值連接配接的結果 = 多個表的交集
2.n表連接配接，至少需要n-1個連接配接條件
3.多個表不分主次，沒有順序要求
4.一般為表起别名，提高閱讀性和性能
           

二、sql99文法：通過join關鍵字實作連接配接

含義：1999年推出的sql文法
支援：
等值連接配接、非等值連接配接 （内連接配接）
外連接配接
交叉連接配接

文法：

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  連接配接條件
【inner|left outer|right outer|cross】join 表3 on  連接配接條件
【where 篩選條件】
【group by 分組字段】
【having 分組後的篩選條件】
【order by 排序的字段或表達式】

好處：語句上，連接配接條件和篩選條件實作了分離，簡潔明了！

           

三、自連接配接

案例：查詢員工名和直接上級的名稱

sql99

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;
           

sql92

SELECT e.last_name,m.last_name
FROM employees e,employees m 
WHERE e.`manager_id`=m.`employee_id`;
           

進階7：子查詢

含義：

一條查詢語句中又嵌套了另一條完整的select語句，其中被嵌套的select語句，稱為子查詢或内查詢
在外面的查詢語句，稱為主查詢或外查詢
           

特點：

1、子查詢都放在小括号内
2、子查詢可以放在from後面、select後面、where後面、having後面，但一般放在條件的右側
3、子查詢優先于主查詢執行，主查詢使用了子查詢的執行結果
4、子查詢根據查詢結果的行數不同分為以下兩類：
① 單行子查詢
    結果集隻有一行
    一般搭配單行操作符使用：> < = <> >= <= 
    非法使用子查詢的情況：
    a、子查詢的結果為一組值
    b、子查詢的結果為空
    
② 多行子查詢
    結果集有多行
    一般搭配多行操作符使用：any、all、in、not in
    in： 屬于子查詢結果中的任意一個就行
    any和all往往可以用其他查詢代替
           

進階8：分頁查詢

應用場景：

實際的web項目中需要根據使用者的需求送出對應的分頁查詢的sql語句
           

文法：

select 字段|表達式,...
from 表
【where 條件】
【group by 分組字段】
【having 條件】
【order by 排序的字段】
limit 【起始的條目索引，】條目數;
           

1.起始條目索引從0開始

2.limit子句放在查詢語句的最後

3.公式：select * from  表 limit （page-1）*sizePerPage,sizePerPage
假如:
每頁顯示條目數sizePerPage
要顯示的頁數 page
           

進階9：聯合查詢

引入：

union 聯合、合并
           

select 字段|常量|表達式|函數 【from 表】 【where 條件】 union 【all】
select 字段|常量|表達式|函數 【from 表】 【where 條件】 union 【all】
select 字段|常量|表達式|函數 【from 表】 【where 條件】 union  【all】
.....
select 字段|常量|表達式|函數 【from 表】 【where 條件】
           

1、多條查詢語句的查詢的列數必須是一緻的
2、多條查詢語句的查詢的列的類型幾乎相同
3、union代表去重，union all代表不去重
           

DML語言

插入

insert into 表名(字段名，...)
values(值1，...);
           

1、字段類型和值類型一緻或相容，而且一一對應
2、可以為空的字段，可以不用插入值，或用null填充
3、不可以為空的字段，必須插入值
4、字段個數和值的個數必須一緻
5、字段可以省略，但預設所有字段，并且順序和表中的存儲順序一緻
           

修改

修改單表文法：

update 表名 set 字段=新值,字段=新值
【where 條件】           

修改多表文法：

update 表1 别名1,表2 别名2
set 字段=新值，字段=新值
where 連接配接條件
and 篩選條件
           

删除

方式1：delete語句

單表的删除：

delete from 表名 【where 篩選條件】
           

多表的删除：

delete 别名1，别名2
from 表1 别名1，表2 别名2
where 連接配接條件
and 篩選條件;
           

方式2：truncate語句

truncate table 表名
           

兩種方式的差別【面試題】

#1.truncate不能加where條件，而delete可以加where條件

#2.truncate的效率高一丢丢

#3.truncate 删除帶自增長的列的表後，如果再插入資料，資料從1開始
#delete 删除帶自增長列的表後，如果再插入資料，資料從上一次的斷點處開始

#4.truncate删除不能復原，delete删除可以復原
           

DDL語句

庫和表的管理

庫的管理：

一、建立庫
create database 庫名
二、删除庫
drop database 庫名           

表的管理：

#1.建立表

CREATE TABLE IF NOT EXISTS stuinfo(
    stuId INT,
    stuName VARCHAR(20),
    gender CHAR,
    bornDate DATETIME
    

);

DESC studentinfo;
#2.修改表 alter
文法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 【字段類型】;

#①修改字段名
ALTER TABLE studentinfo CHANGE  COLUMN sex gender CHAR;

#②修改表名
ALTER TABLE stuinfo RENAME [TO]  studentinfo;
#③修改字段類型和列級限制
ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;

#④添加字段

ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;
#⑤删除字段
ALTER TABLE studentinfo DROP COLUMN email;


#3.删除表

DROP TABLE [IF EXISTS] studentinfo;


           

常見類型

整型：
    
小數：
    浮點型
    定點型
字元型：
日期型：
Blob類型：
           

常見限制

NOT NULL
DEFAULT
UNIQUE
CHECK
PRIMARY KEY
FOREIGN KEY
           

資料庫事務

含義

通過一組邏輯操作單元（一組DML——sql語句），将資料從一種狀态切換到另外一種狀态
           

特點

（ACID）
原子性：要麼都執行，要麼都復原
一緻性：保證資料的狀态操作前和操作後保持一緻
隔離性：多個事務同時操作相同資料庫的同一個資料時，一個事務的執行不受另外一個事務的幹擾
持久性：一個事務一旦送出，則資料将持久化到本地，除非其他事務對其進行修改
           

相關步驟：

1、開啟事務
2、編寫事務的一組邏輯操作單元（多條sql語句）
3、送出事務或復原事務
           

事務的分類：

隐式事務，沒有明顯的開啟和結束事務的标志

比如
insert、update、delete語句本身就是一個事務
           

顯式事務，具有明顯的開啟和結束事務的标志

1、開啟事務
    取消自動送出事務的功能
    
    2、編寫事務的一組邏輯操作單元（多條sql語句）
    insert
    update
    delete
    
    3、送出事務或復原事務           

使用到的關鍵字

set autocommit=0;
start transaction;
commit;
rollback;

savepoint  斷點
commit to 斷點
rollback to 斷點
           

事務的隔離級别:

事務并發問題如何發生？

當多個事務同時操作同一個資料庫的相同資料時           

事務的并發問題有哪些？

髒讀：一個事務讀取到了另外一個事務未送出的資料
不可重複讀：同一個事務中，多次讀取到的資料不一緻
幻讀：一個事務讀取資料時，另外一個事務進行更新，導緻第一個事務讀取到了沒有更新的資料
           

如何避免事務的并發問題？

通過設定事務的隔離級别
1、READ UNCOMMITTED
2、READ COMMITTED 可以避免髒讀
3、REPEATABLE READ 可以避免髒讀、不可重複讀和一部分幻讀
4、SERIALIZABLE可以避免髒讀、不可重複讀和幻讀
           

設定隔離級别：

set session|global  transaction isolation level 隔離級别名;           

檢視隔離級别：

select @@tx_isolation;

           

視圖

含義：了解成一張虛拟的表

視圖和表的差別：

使用方式    占用實體空間

視圖    完全相同    不占用，僅僅儲存的是sql邏輯

表    完全相同    占用
           

視圖的好處：

1、sql語句提高重用性，效率高
2、和表實作了分離，提高了安全性
           

視圖的建立

文法：
CREATE VIEW  視圖名
AS
查詢語句;           

視圖的增删改查

1、檢視視圖的資料 

SELECT * FROM my_v4;
SELECT * FROM my_v1 WHERE last_name='Partners';

2、插入視圖的資料
INSERT INTO my_v4(last_name,department_id) VALUES('虛竹',90);

3、修改視圖的資料

UPDATE my_v4 SET last_name ='夢姑' WHERE last_name='虛竹';


4、删除視圖的資料
DELETE FROM my_v4;           

某些視圖不能更新

包含以下關鍵字的sql語句：分組函數、distinct、group  by、having、union或者union all
常量視圖
Select中包含子查詢
join
from一個不能更新的視圖
where子句的子查詢引用了from子句中的表           

視圖邏輯的更新

#方式一：
CREATE OR REPLACE VIEW test_v7
AS
SELECT last_name FROM employees
WHERE employee_id>100;

#方式二:
ALTER VIEW test_v7
AS
SELECT employee_id FROM employees;

SELECT * FROM test_v7;           

視圖的删除

DROP VIEW test_v1,test_v2,test_v3;           

視圖結構的檢視

DESC test_v7;
SHOW CREATE VIEW test_v7;
           

存儲過程

含義：一組經過預先編譯的sql語句的集合

好處：

1、提高了sql語句的重用性，減少了開發程式員的壓力
2、提高了效率
3、減少了傳輸次數
           

分類：

1、無傳回無參
2、僅僅帶in類型，無傳回有參
3、僅僅帶out類型，有傳回無參
4、既帶in又帶out，有傳回有參
5、帶inout，有傳回有參
注意：in、out、inout都可以在一個存儲過程中帶多個           

建立存儲過程

create procedure 存儲過程名(in|out|inout 參數名  參數類型,...)
begin
    存儲過程體

end
           

類似于方法：

修飾符 傳回類型 方法名(參數類型 參數名,...){

    方法體;
}
           

注意

1、需要設定新的結束标記
delimiter 新的結束标記
示例：
delimiter $

CREATE PROCEDURE 存儲過程名(IN|OUT|INOUT 參數名  參數類型,...)
BEGIN
    sql語句1;
    sql語句2;

END $

2、存儲過程體中可以有多條sql語句，如果僅僅一條sql語句，則可以省略begin end

3、參數前面的符号的意思
in:該參數隻能作為輸入 （該參數不能做傳回值）
out：該參數隻能作為輸出（該參數隻能做傳回值）
inout：既能做輸入又能做輸出
           

調用存儲過程

call 存儲過程名(實參清單)           

函數

建立函數

學過的函數：LENGTH、SUBSTR、CONCAT等

CREATE FUNCTION 函數名(參數名 參數類型,...) RETURNS 傳回類型
BEGIN
    函數體

END
           

調用函數

SELECT 函數名（實參清單）
           

函數和存儲過程的差別

關鍵字        調用文法    傳回值            應用場景
函數        FUNCTION    SELECT 函數()    隻能是一個        一般用于查詢結果為一個值并傳回時，當有傳回值而且僅僅一個
存儲過程    PROCEDURE    CALL 存儲過程()    可以有0個或多個        一般用于更新
           

流程控制結構

系統變量

一、全局變量

作用域：針對于所有會話（連接配接）有效，但不能跨重新開機

檢視所有全局變量
SHOW GLOBAL VARIABLES;
檢視滿足條件的部分系統變量
SHOW GLOBAL VARIABLES LIKE '%char%';
檢視指定的系統變量的值
SELECT @@global.autocommit;
為某個系統變量指派
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
           

二、會話變量

作用域：針對于目前會話（連接配接）有效

檢視所有會話變量
SHOW SESSION VARIABLES;
檢視滿足條件的部分會話變量
SHOW SESSION VARIABLES LIKE '%char%';
檢視指定的會話變量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
為某個會話變量指派
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';
           

自定義變量

一、使用者變量

聲明并初始化：

SET @變量名=值;
SET @變量名:=值;
SELECT @變量名:=值;           

指派：

方式一：一般用于賦簡單的值
SET 變量名=值;
SET 變量名:=值;
SELECT 變量名:=值;
           

方式二：一般用于賦表 中的字段值
SELECT 字段名或表達式 INTO 變量
FROM 表;
           

使用：

select @變量名;
           

二、局部變量

聲明：

declare 變量名 類型 【default 值】;           

方式一：一般用于賦簡單的值
SET 變量名=值;
SET 變量名:=值;
SELECT 變量名:=值;
           

方式二：一般用于賦表 中的字段值
SELECT 字段名或表達式 INTO 變量
FROM 表;
           

select 變量名
           

二者的差別：

作用域            定義位置        文法           

使用者變量 目前會話 會話的任何地方 加@符号，不用指定類型

局部變量 定義它的BEGIN END中 BEGIN END的第一句話 一般不用加@,需要指定類型

分支

一、if函數

文法：if(條件，值1，值2)
特點：可以用在任何位置
           

二、case語句

情況一：類似于switch
case 表達式
when 值1 then 結果1或語句1(如果是語句，需要加分号) 
when 值2 then 結果2或語句2(如果是語句，需要加分号)
...
else 結果n或語句n(如果是語句，需要加分号)
end 【case】（如果是放在begin end中需要加上case，如果放在select後面不需要）

情況二：類似于多重if
case 
when 條件1 then 結果1或語句1(如果是語句，需要加分号) 
when 條件2 then 結果2或語句2(如果是語句，需要加分号)
...
else 結果n或語句n(如果是語句，需要加分号)
end 【case】（如果是放在begin end中需要加上case，如果放在select後面不需要）
           

可以用在任何位置
           

三、if elseif語句

if 情況1 then 語句1;
elseif 情況2 then 語句2;
...
else 語句n;
end if;
           

隻能用在begin end中！！！！！！！！！！！！！！！
           

三者比較：

應用場合
if函數        簡單雙分支
case結構    等值判斷 的多分支
if結構        區間判斷 的多分支
           

循環

【标簽：】WHILE 循環條件  DO
    循環體
END WHILE 【标簽】;
           

隻能放在BEGIN END裡面

如果要搭配leave跳轉語句，需要使用标簽，否則可以不用标簽

leave類似于java中的break語句，跳出所在循環！！！