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語句,跳出所在迴圈!!!