01-mysql必知必會

XXXTaye發表於2021-09-21

概述

主鍵：唯一區分表中每個行（不可重複，不允許null

mysql：基於客戶-伺服器的資料庫系統

使用mysql

mysql是部署在主機上的，可以通過ssh建立mysql連線

# 顯示資料庫
show databases;
# 選擇資料庫
use databases;

# 顯示庫中的表
show tables;
# 顯示錶中的列
show columns from [table];

# 顯示狀態資訊
show status;
# 顯示使用者
show grants;
# 顯示錯誤和警告
show errors;
show warnings;

關於表

default可以給列賦一個預設值
- 這個預設值不允許是函式
not或者not null表明插入資料時是否允許該列為null值，預設情況下為null
預設的儲存引擎為innoDB

# 建立表
create table student(
student_id int primary key auto_increment,
name varchar(20) not null default '張大炮'    
)engine=innoDB;

# 使用複合主鍵
create test(
test_id int auto_increment,
math int,
chinese int,
primary key(math,test_id)
);

外來鍵約束

外來鍵經常應用的地方為以下兩種情況：

父表更新時子表也更新，父表刪除時如果子表有匹配的項，刪除失敗；
父表更新時子表也更新，父表刪除時子表匹配的項也刪除。

注意：只有innoDB支援外來鍵，MyISAM不支援外來鍵

# 基本格式
constraint (key_name) foreign key(column) references table(column)
on delete/update (觸發語句)

on delete和on update表示事件觸發，觸發操作有以下幾種：

RESTRICT（限制外表中的外來鍵改動，預設值）
CASCADE（跟隨外來鍵改動）
SET NULL（設空值）
SET DEFAULT（設預設值）
NO ACTION（無動作，預設的）

# 建立外來鍵約束
# 外來鍵關聯的表的元素改地，則這個表裡面的關聯元素也會改動
create table test(
test_id int auto_increment,
subject varchar(10),
date date,
student_id int,
constraint stu_fk foreign key(student_id) references student(student_id)
on update cascade,
primary key(test_id)
)engine=InnoDB;

關於引擎

InnoDB：可靠事務處理引擎，但不支援全文字搜尋

MyISAM：效能高，但不支援事務處理

Memory：功能等同MyISAM，但資料儲存在記憶體，很快但不能永久儲存，適合臨時表

修改表

使用alter table來修改修改表，一般是add操作和rename操作

add：可以新增新列和鍵約束

# 新增列
alter table student add
class int not null;

# 新增外來鍵約束
alter table student add
constraint st_fk foreign key(student_id) references test(student_id)
on update cascade;

刪除表

drop table student;

資料檢索

基本格式如下

select [column...] from [table];

column可以選擇多個列，也可用萬用字元*表示所有列

去除重複行：使用distinct關鍵字，distinct作用於所有列

# 兩個列都一樣才會認為是重複
select distinct student_id,student_name form student;

使用limit限制結果

注意：行號從0開始

# 返回前5行
# 相當於 limit 0,5
select student_id form student limit 5;

# 返回行5開始的後5行（包括第5行
select student_id form student limit 5,5;

使用.來表示全限名

# 表示從school庫的student表查student_name這個列
select student.student_name from school.student;

資料排序

使用order by字句選擇按照哪個列來進行排序

預設順序：A—>Z，0—>∞，使用DESC指定為降序排列（必須指定在每個列上

可指定多個列

# 結果通過名字排序
select name from student order by name;

# 優先按照name排序，相同則按照number排序；降序
select name from student order by name DESC,number DESC;

資料過濾

使用where字句

使用where字句限定條件

運算子	效果
=	等於
!=或<>	不等於
>	大於
<	小於
>=	大於等於
<=	小於等於
between	限定在兩個值之間

匹配字串需要單引號，匹配數值則不需要
使用and和or連線多個條件，and運算優先順序高於or
使用is null檢查空值
使用in來限定範圍，範圍是一個離散值的集合，離散值用,隔開
使用not來否定限定範圍

# 按照id和grade兩個條件進行限定
select name from student where id='0000' and grade=100;

# 空值檢查
select name from student where id is null;

# 使用between限定範圍，下面2個語句等價
select name from student where id between '0000' and '1000';
select name from student where id >= '0000' and id <= '1000';

# 使用in限定範圍，下面2個語句等價
select name from student where id in (1,2);
select name from student where id = 1 or id = 2;

# 使用not取限定範圍以外的值
select name from student where id not in (1,2);

使用萬用字元

使用like操作符來進行模糊匹配

不能匹配null
注意：萬用字元時間開銷大，能採用其他操作符儘量採用其他操作符

操作符	效果
%	匹配任意多個字元
_	匹配單個字元

# 多字元匹配
select name from student where id like '%00%';

# 單字元匹配
select name from student where id like '_00';

建立計算欄位

欄位：多個列的值合成而成

拼接欄位

使用Concat()來拼接多個列

# 拼接name、=以及grade
select Concat(name,'=',grade) from student;

使用RTrim()和LTrim()分別去除左右多餘空格，Trim()直接去除兩邊空格

使用as為列賦別名用以顯示

# 賦name別名為姓名
select name as 姓名 from student;

算數運算

操作符	意義
+	加
-	減
*	乘
/	除

# 計算學號000開頭的學生總成績
select Chinese+Math+English as 總成績 from student where id = '000_';

函式

sql支援以下函式型別：

處理文字串
算術操作
處理日期
系統函式（返回登入資訊，檢查版本等

文字處理函式

函式	效果
Left()	返回左邊的字元
Length()	返回串長度
Locate(subStr,string)	找出字串出現的位置
Lower()	轉換小寫
LTrim()	去除左邊空格
RTrim()	去除右邊空格
Right()	返回右邊的字元
Soundex()	匹配發音類似的值
SubString(string,position,length)	返回字串
Upper()	轉換大寫

# 匹配發音類似Y Lei的值
select grade from student where Soundex(name) = Soundex('Y Lei');

日期處理函式

日期時間滿足yyyy-MM-dd HH:mm:SS形式

函式	說明
AddDate()	增加一個日期（天、周
AddTime()	增加一個時間（時、分
CurDate()	返回當前日期
CurTime()	返回當前時間
Date()	返回日期時間的日期部分
DateDiff()	計算日期之差
Date_Add()	日期運算
Date_Format()	返回格式化的日期和時間串
Day()	返回日期的天數
DayOfWeek()	返回日期對應的星期幾
Hour()	返回時間的小時部分
Minute()	返回時間的分鐘部分
Month()	返回日期的月份
Now()	返回當前日期和時間
Second()	返回一個時間的秒部分
Time()	返回一個日期時間的時間部分
Year()	返回日期的年份

常見情況：Date資料型別的欄位值存入了yyyy-MM-dd HH:mm:SS形式的欄位值，但匹配時只想要日期或者時間

# 只需要日期值進行比較
select id from student where Date(register_time)='2001-2-3';

# 取出某年某月的資料
select id from student where Year(register_time)=2005 and Month(register_time)=6

數值處理函式

函式	說明
Abs()	返回絕對值
Cos()	返回餘弦值
Exp()	返回指數值
Mod()	返回餘數
Pi()	返回圓周率Π
Rand()	返回隨機數
Sin()	返回正弦值
Sqrt()	返回數的平方根
Tan()	返回角度的正切值

# 返回一個餘數值
select Mod(55,33);

資料彙總函式

聚焦函式：確定行數、列數或者找其中某特定值（如最大值、最小值...）

使用聚焦函式處理列不能離開group by字句

函式	效果
AVG()	返回平均值
COUNT()	返回某列的行數
MAX()	返回該列最大值
MIN()	返回該列最小值
SUM()	列求和

注意：count(*)多所有行進行計數，count(column)對該列為null的行不計數

可以和distinct關鍵詞搭配使用，去除重複值

使用distinct必須指定列名，也就是說不能和count(*)搭配使用

# 求平均值，每個值只計算一次
select AVG(DISTINCT grade) from student;

# 都整一遍
select AVG(grade) as 平均成績,
MAX(grade) as 最大值,
MIN(grade) as 最小值
from student;

資料分組

涉及group by和having兩個字句

group by：按照某個屬性分組計算，相當於是對每個分組單獨進行一次sql語句的執行

必須出現在where之後，order by之前
可指定多個，但最後會彙總到最後指定的屬性進行分組
除去聚焦函式(子句中不允許包含聚焦函式)，select語句中出現的所有列都必須在group by中給出

# 求業務員分別賣出了多少物品
select id,name,count(*) from kpi
group by id;

having：起過濾作用，和where類似。但where過濾的是行，having過濾的是分組

# 求除了id為1的業務員分別賣出了多少物品
select id,name,count(*) from kpi
group by id
having id!=1;

不要依賴group by產生的順序

要正確排序還是應該使用order by字句，該字句一般放在最後

# 順序求除id為1的每個業務員賣出物品的總價值
select id,sum(price) from kpi
group by id
having id!=1
order by sum(price);

select字句順序

|	select
|	from 
|	where
|	group by
|	having
↓	limit

多表查詢

子查詢

主要用途：查詢多個表

子查詢的執行順序是從內向外的

原理：select語句的查詢結果可以提供給其他select語句作為條件

可以巢狀，但一般不建議超過3層

# 聯結兩個表，查詢數學成績
select student_id from student where grade in(
	select grade from test where subject='數學');
	
# 對每個學生單獨計算成績總和
select student_id,(select sum(grade) from test where test.student_id=student.student_id) as sum
from student order by grade DESC;

聯結表

外來鍵：將一個表的主鍵放在其他表作為外來鍵，加強資料一致性

外來鍵的欄位值依賴於所連線的表的主鍵，會對插入、刪除等操作做檢查

聯結表設定的條件一般是外來鍵

# 聯結多表進行查詢
select name,math
from student,test
where student.student_id=test.student_id;

# 使用表別名縮短sql語句
select name,math
from student as s,test as t
where s.student_id=t.student_id;

不使用where字句做限定的話會返回一個笛卡爾積

內部聯結

聯結表這種基於兩個表之間相等測試的方法也稱為內部聯結

可以使用join...on...達到相同目的

inner/outer：表示內聯結或者外聯結

join：使用join加入表

on：條件

# JOIN...ON...
select name,math
from student inner join test
on student.student_id=test.student_id;

高階聯結

自聯結

一張表自己聯結自己

使用場景：前一次查到這張表的資料作為後一次查詢這張表的條件的時候

為了避免二義性，列採用全限定名

# 首先根據test_id=4查到student_id，再根據student_id查到語文成績
select t2.chinese from
test as t1,test as t2
where t1.student_id=t2.student_id
and t1.test_id=4;

# 等效子查詢
select chinese from test
where student_id = (select student_id from test where test_id=4);

自然聯結

避免多表查詢中相同的列多次出現

注意：這是一種規範，不是某種特定的用法

實現：自己刻意避免出現相同列

外部聯結

雖然是兩個有關聯的表進行聯結，但其中一個表不一定所有資料在另外一個表都有對應資料

left outer join：左邊的表的所有行加入進來，若右邊沒有對應的則顯示null

right outer join：右邊的表的所有行加入進來，若左邊沒有對應的則顯示null

# 使用左聯結
select s.student_id,t.math  
from student as s left join test as t
on s.student_id = t.student_id

student_id=5在右邊表中是沒有對應資料的，但還是會予以顯示

組合查詢

含義：執行多條sql語句，但將結果作為一個結果集返回

每個查詢中的內容相同（列、函式
預設返回的是並集，使用union all會出現重複結果
使用order by排序時只需要寫在最後一條sql語句上即可
相當於where子句中使用or

# 簡單測試
select student_id,math,chinese from test
where math > 20
union
select student_id,math,chinese from test
where chinese >20

全文字搜尋

不是所有儲存引擎都支援全文字搜尋

InnoDB不支援，MyISAM支援

啟動全文字查詢支援

在建立表的時候使用fulltext()索引可以被全文字搜尋的列

同時指定引擎為MyISAM

create table article(
article_id int primary key auto_increment,
name varchar(20),
student_id int,
component text,
fulltext(component) 
)engine=MyISAM;

進行全文字搜尋

match()：指定被搜尋的列

against()：指定搜尋表示式

# 搜尋帶有When的文字
select component from article
where match(component) against('When');

資料插入

插入完整行

使用insert into來插入資料

# 基本格式
insert into [table](columns1,columns2,...) values(...);

可以不帶column，但是就需要記住列的順序按序插入

資料約束為null的列可以插入null值

使用;分隔多行插入，如果資料一致也可以使用,分隔多行資料

# 插入多行
insert into student(name) values('袁瑞通過');
insert into student(name) values('贖金小松');

# 如果插入是相同列的資料，也可以這樣
insert into student(name) values('打字話'),('大選呀'),('大批及')

插入搜尋行

可以插入其他行中搜尋出來的結果

插入資料時通過select字句返回的順序來匹配

# 插入select結果
insert into article(name,student_id)
select name,student_id from student where student_id=3;

資料更新

使用update...set...來更新資料

ignore關鍵字：預設更新多行資料如果發生錯誤，則之前更新過的資料恢復原來值；使用ignore後不會恢復

# 更新多個資料
update ignore article set name='大花衣',
				   component='bilibili'
where article_id=3;

資料刪除

delete關鍵字刪除整行的資料

# 刪除一行資料
delete from article where article_id=3;

檢視

檢視是虛擬的表，包含的是動態的sql查詢的結果

建立檢視後，可以進行和表一樣的查詢操作

檢視不能索引，不能有關聯觸發器

建立

# 建立檢視
create view exam as
select student.student_id,name,subject,date
from student,test;

檢視直接組合各個表中的列，在某些特定情況下非常方便

# 和查詢一般的表無異
select * from exam;
select * from exam where date between '2020-1-3' and '2021-3-4';

檢視的更新

檢視是可以更新的，但有很多的限制。一般只用檢視來做資料檢索。

儲存過程

方便後續使用的一條或多條sql語句集合

變數必須以@開頭

使用call來呼叫儲存過程

# 括號裡面的是引數
call product(@low,@high);

建立儲存過程

# 遵頊以下格式
create procedure grade_avg()
begin
	select avg(grade) as 平均成績
	from test;
end;

注意：mysql應用程式中會出現;衝突，使用delimiter來定義一個分隔符使用

# 定義分隔符為 //
DELIMITER //
create procedure grade_avg()
begin
	select avg(grade) as 平均成績
	from test;
end //

使用drop刪除儲存過程

drop procedure grade_avg();

使用引數

引數：

IN: 表示輸入引數，可以攜帶資料帶儲存過程中

OUT: 表示輸出引數，可以從儲存過程中返回結果

INOUT: 表示輸入輸出引數，兩者結合

使用set為變數賦值

使用into把查詢結果賦給變數

# 攜帶輸入引數的儲存過程
DELIMITER //
create procedure search(in sid int)
begin
	select * from student where student_id=sid;
end	//
# 使用帶內部引數的儲存過程：直接傳入引數值
call search(10);


# 攜帶輸出引數的儲存過程
DELIMITER //
create procedure stu_out(out str varchar(20))
begin
	set str='123123';
end //
# 使用外部引數的儲存過程：先獲得這個外部變數值，再進行操作
call stu_out(@name);
select @name;

# 使用select into
delimiter //
create procedure stu_into(in id int,out str varchar(20))
begin
	select name into str
    from student where student_id=id;
end //
# 使用該儲存過程
call stu_into(5,@name);
select @name as 名字;

使用分支控制語句

if...then...elseif...else...end if的格式

# 使用分支控制
delimiter //
create procedure weekday(in input int,out output varchar(10))
begin
	if input=1 then set output='星期一';
    elseif input=2 then set output='星期二';
    else set output='其他日子';
    end if;
end //

# 使用該儲存過程
call weekday(2,@day);
select @day；

使用迴圈控制語句

通過declare定義儲存過程的內部變數（也可叫區域性變數、臨時變數

採用while...do...end while的格式

# 使用迴圈控制
# 計算1到某個數值的和
delimiter //
create procedure stu_while(in input int,out output int)
begin
	declare i int default 0;
    declare result int default 0;
    while i<=input 
    do
        set result=result+i;
        set i=i+1;
	end while;
    set output=result;
end //

# 使用該儲存過程
call stu_while(4,@res);
select @res as 和;

還可以採用repeat...until...end repeat語句

# 修改上面例子的迴圈部分
repeat
	set result=result+i;
	set i=i+1;
until i>input end repeat;

遊標

場景：為了在檢索出來的行中前進後者後退幾行

只能用在儲存過程

在儲存過程中用declare宣告遊標，遊標的變數名是cursor
使用for來使遊標關聯select語句
建立好過後，還要使用open來開啟遊標；並且不使用的話還要使用close關閉遊標釋放資源
一般遊標用在迴圈語句，為了使遊標可以正常退出，一般需要繫結一個對應的錯誤處理

異常處理

exit handler：出現某種異常後退出

continue handler：出現某種異常後繼續

應用場景：事務出現錯誤時進行回滾

遊標中的使用：遊標檢索完時設定退出標誌

使用遊標

fetch...into...將遊標值賦給變數

# 使用遊標
delimiter //
create procedure cursor_test()
begin
	declare done boolean default 0;
    declare str varchar(20);
    
	declare ct cursor for
    select name from student;
    declare continue handler for not found set done=1;
    
    open ct;
    
    repeat
		fetch ct into str;
	until done end repeat;
    
    close ct;
end //

一個使用場景

涉及到資料遷移，使用遊標迴圈將資料轉移非常的方便

# 將多行資料轉移建立一個新的表
delimiter //
create procedure new_test()
begin
	declare name varchar(20);
    declare id int;
    declare grade int;
    declare subject varchar(20);
    
    declare done boolean default 0;
	declare cs cursor for
    select student.name,student.student_id,test.grade,test.subject from student,test where student.student_id=test.student_id;
    declare continue handler for not found set done=1;
    
    create table if not exists grades(
    grades_id int primary key auto_increment,
    name varchar(20),
    student_id int,
    grade int,
    subject varchar(20)
    );
    
    open cs;
    repeat
		fetch cs into name,id,grade,subject;
        insert into grades(name,student_id,grade,subject) values(name,id,grade,subject);
	until done end repeat;
    close cs;
end //

觸發器

觸發器是某張表執行某一型別的語句後，表自動去執行某一儲存過程

比如：某一張表的資料有修改，另一張表跟著也要修改

響應語句型別：delete，insert，update

觸發器只能繫結到表，不能繫結到檢視
mysql的觸發器不能呼叫儲存過程

刪除和建立

建立觸發器給出四條資訊：

觸發器名
關聯表名
響應活動（delete、insert、update）
響應時間（before、after）

# 建立觸發器
create trigger ct after insert on student
for each row
select 'balbala';

# 刪除觸發器
drop trigger ct;

insert觸發器

new可以訪問最新插入的行
before insert觸發器中new的值可以更改，即把將要插入的值更改了再插入

# 建立一個insert觸發器
create trigger ct after insert on student
for each row
select new.student_id into @id;

# 執行insert語句後可以訪問@id
insert into student(name) values('asduh');
select @id;

delete觸發器

old訪問被刪除的行，裡面的資料是隻讀的

# 建立一個delete觸發器
create trigger ct after delete on grades
for each row
select old.student_id,old.name into @id,@name;

# 執行delete語句後可以訪問引數
delete from grades where grades_id=1;
select @id as id,@name as name;

update觸發器

old訪問該行更新前的數值，new訪問該行更新後的數值
before update觸發器可以更改new中的值
old都是隻讀的

# 建立update觸發器,將更新值全部換成大寫
create trigger ct4 before update on choice
for each row
set new.choice01=upper(new.choice01);

# 檢視結果
update choice set choice01='wasdfg' where choice_id=1;
select * from choice;

事務處理

使用InnoDB,MyISAM不支援事務處理
mysql預設的事務處理是每條sql語句自動提交

# autocommit為0：不自動提交
set autocommit=0;

事務：一組sql語句

回滾：撤銷指定sql語句

提交：將未儲存的sql語句提交到資料庫，也就是執行

保留點：臨時佔位符，可以釋出回退

開始事務處理

使用start transaction標誌事務的開始

使用commit提交事務，事務中的多條sql語句是被一起提交的

使用rollback撤銷事務，只能在一個事務內使用

# 提交事務
start transaction;
delete from student where name='打字話';
insert into student(name) values('asd');
commit;

# 撤銷事務
start transaction;
delete from student where name='打字話';
insert into student(name) values('asd');
rollback;

使用保留點

rollback事務回滾是直接回滾到transaction還沒開始的地方

保留點用在部分回滾的場景

使用savepoint...設定保留點

# 設定保留點，部分回退
start transaction;
delete from student where name='打字話';
savepoint sv1;
insert into student(name) values('asd');
rollback to sv1;

安全管理

管理使用者

名為mysql的資料庫中儲存了資料庫設定等資訊，可以在裡面的user表檢視資訊

# 檢視使用者
use mysql;
select user from user;

建立和刪除使用者

identified by設定密碼

# 建立使用者
create user xt identified by '123456';

# 修改使用者名稱
rename user xt to xtbro;

# 刪除使用者
drop user xtbro;

# 更新密碼，需要加密入表
set password for xt = Password('1234567');

賦予許可權

show grants for...檢視許可權
使用grant賦予許可權

# 賦予school資料庫下的所有表的insert許可權
grant insert on school.* to xt;

# 檢視許可權
show grant for xt;

# 撤銷許可權
revoke insert on school.* from xt;

許可權的層次：

整個伺服器：grant all

整個資料庫：on database.*

特定的表：on database.table

特定的列：on database.table.column

特定的儲存過程

git必會必知
2019-04-08
Git
Activity 必知必會
2019-03-04
Redis 必知必會
2019-03-13
Redis
MySQL 必知必會
2019-04-13
MySql
ThreadLocal必知必會
2020-05-22
thread
Linux必會必知
2019-01-19
Linux
JSON 必知必會
2018-04-04
JSON
Megaraid 必知必會
2010-12-06
AI
notion database 必知必會
2024-03-13
Database
Linux shell必知必會
2020-03-14
Linux
Redis 必知必會之 API
2020-01-07
RedisAPI
mysql必知必會筆記
2020-02-10
MySql筆記
Linux 程式必知必會
2020-07-10
Linux
HTTP 必知必會的那些
2019-03-04
HTTP
Mysql必知必會練習
2020-09-26
MySql
webpack入門必知必會
2017-01-16
Web
Java必知必會之socket
2016-05-15
Java
IT運維技能必知必會！
2016-05-23
運維
【必知必會的MySQL知識】①初探MySQL
2023-05-01
MySql
【必知必會的MySQL知識】②使用MySQL
2023-05-02
MySql
Redis 必知必會之持久化
2020-02-08
Redis持久化
SQL必知必會筆記（上）
2019-05-02
SQL筆記
SQL必知必會筆記（下）
2019-05-14
SQL筆記
Java必知必會之註解
2018-10-03
Java
Flutter 外掛使用必知必會
2018-12-24
Flutter
MYSQL中的鎖必知必會
2020-12-21
MySql
面試：Redis必知必會20問
2021-10-18
面試Redis
必知必會Java命令-jps
2023-12-10
Java
ElasticSearch必知必會-進階篇
2023-01-17
Elasticsearch
JavaScript必知必會+理解總結
2013-06-17
JavaScript
【必知必會的MySQL知識】③DML語言
2023-05-02
MySql
【必知必會的MySQL知識】④DCL語言
2023-05-03
MySql
【必知必會的MySQL知識】⑤DQL語言
2023-05-04
MySql
《MySQL必知必會》萬用字元 ( like , % , _ ,)
2020-08-16
MySql字元
MySQL必知必會》正規表示式
2020-08-23
MySql
Android 中高階面試必知必會
2019-12-02
Android面試
Flutter佈局詳解，必知必會
2018-12-27
Flutter
Flutter 外掛編寫必知必會
2018-12-26
Flutter