Mysql 筆記

• ---------------------------作業------------------------------

create table stuinfo (
    sno char(8) primary key not null comment '學號',
    sname char(10) unique comment '姓名',
    ssex char(2)  default'男'
);

create table stucorse (
    sno char(8) not null comment '學號',
    cno char(8) primary key not null comment '課程名',
    score int ,
    foreign key(sno)references stuinfo(sno)
);
create table tinfo
(
    tno   int primary key not null auto_increment comment '教師編號',
    tname varchar(10)     not null unique,
    txb   char(2),
    tdept varchar(30)
);
create table student1 (
    stuno varchar(11) primary key ,
    stuname varchar(8),
    stuage smallint,
    stusex char(1) check (stusex IN ('F', 'M')),
    schno char(10),
    FOREIGN KEY (schno) REFERENCES School(schno) ON DELETE CASCADE

);
create table School
(
    schno     char(11) primary key,
    schname   varchar(8),
    schstunum smallint
);
create table Club(
    clubno char(11) primary key ,
    clubname varchar(8),
    clubyear datetime,
    clubloc varchar(21)

                 );
create table JoinClub(stuno char(11),
                      clubno char(11),
                      joinyear datetime,
                      PRIMARY KEY (stuno, clubno),
    foreign key(stuno) references student1(stuno),
    foreign key(clubno) references club(clubno)
                     );

CREATE TABLE VendingMachine (
    VEMno VARCHAR(10) PRIMARY KEY,
    Location VARCHAR(50)
);


CREATE TABLE GOODS (
    Gno VARCHAR(10) PRIMARY KEY,
    Brand VARCHAR(50),
    Price DECIMAL(10, 2)
);


CREATE TABLE SALES (
    SNo INT PRIMARY KEY,
    VEMno VARCHAR(10),
    Gno VARCHAR(10),
    SDate DATE,
    STime TIME,
    FOREIGN KEY (VEMno) REFERENCES VendingMachine(VEMno),
    FOREIGN KEY (Gno) REFERENCES GOODS(Gno)
);


CREATE TABLE OOS (
    VEMno VARCHAR(10),
    Gno VARCHAR(10),
    SDate DATE,
    STime TIME,
    PRIMARY KEY (VEMno, Gno, SDate, STime),
    FOREIGN KEY (VEMno) REFERENCES VendingMachine(VEMno),
    FOREIGN KEY (Gno) REFERENCES GOODS(Gno)
);

-- --------------------------作業------------------------------

create table ygxx(
    Ygbh CHAR(8) NOT NULL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Sex CHAR(1) NOT NULL check (sex IN ('男', '女')) ,
    Zw VARCHAR(10) NOT NULL,
    Kl VARCHAR(20) NOT NULL,
    QxjB CHAR(1) NOT NULL,
    Sfzh CHAR(18) NOT NULL,
    ssbmbh CHAR(255) NOT NULL
);
ALTER TABLE ygxx
add COLUMN Sfzh VARCHAR(18);
-- 插入第一個員工資料
INSERT INTO ygxx (Ygbh, Name, Sex, Zw, Kl, QxjB, Sfzh, ssbmbh)
VALUES
('YG001', 'Alice', '女', '經理', 'A1', 'Y', '123456198001011234', 'BM001'),
('YG002', 'Bob', '男', '助理', 'B2', 'N', '123456198002022345', 'BM002'),
('YG003', 'Charlie', '男', '工程師', 'C3', 'Y', '123456198003033456', 'BM003'),
('YG004', 'David', '男', '設計師', 'D4', 'N', '123456198004044567', 'BM001'),
('YG005', 'Eve', '女', '銷售', 'E5', 'Y', '123456198005055678', 'BM002'),
('YG006', 'Frank', '男', '技術支援', 'F6', 'N', '123456198006066789', 'BM003'),
('YG007', 'Grace', '女', '市場', 'G7', 'Y', '123456198007077890', 'BM001'),
('YG008', 'Henry', '男', '人事', 'H8', 'N', '123456198008088901', 'BM002'),
('YG009', 'Ivy', '女', '財務', 'I9', 'Y', '123456198009099012', 'BM003'),
('YG010', 'Jack', '男', '採購', 'J10', 'N', '123456198010101123', 'BM001');
INSERT INTO ygxx (Ygbh, Name, Sex, Zw, Kl, QxjB, Sfzh, ssbmbh)
VALUES('YG011', 'Boby', '男', '助理', 'B2', 'N', null, 'BM002');
CREATE TABLE bmxx (
    bmbh CHAR(8) NOT NULL PRIMARY KEY COMMENT '部門編號',
    bmmc VARCHAR(4) NOT NULL COMMENT '部門名稱'
);
INSERT INTO bmxx (bmbh, bmmc)
VALUES
('BM001', '部門1'),
('BM002', '部門2'),
('BM003', '部門3');
CREATE TABLE Spxx (
    Spbh CHAR(8) NOT NULL PRIMARY KEY COMMENT '商品編號',
    Spmc VARCHAR(20) NOT NULL COMMENT '商品名稱',
    Sslb CHAR(8) NOT NULL COMMENT '所屬類別',
    Jg FLOAT(10,2) NOT NULL COMMENT '價格',
    sl INT NOT NULL COMMENT '數量'
);
INSERT INTO Spxx (Spbh, Spmc, Sslb, Jg, sl)
VALUES
('SP001', 'iPhone 12', '手機', 5999.00, 100 ),
('SP002', '華為Mate 40 Pro', '手機', 6999.00, 80),
('SP003', '小米10', '手機', 3299.00, 200),
('SP004', 'OPPO Find X3 Pro', '手機', 6999.00, 50),
('SP005', 'vivo X60 Pro+', '手機', 4999.00, 150),
('SP006', '三星Galaxy S21 Ultra', '手機', 10999.00, 30),
('SP007', '聯想ThinkPad X1 Carbon', '膝上型電腦', 9999.00, 50),
('SP008', '戴爾XPS 13', '膝上型電腦', 7999.00, 80),
('SP009', '華為MateBook X Pro', '膝上型電腦', 8999.00, 60),
('SP010', '蘋果MacBook Pro', '膝上型電腦', 12999.00, 40),
('SP011', '海爾冰箱', '家電', 3999.00, 100),
('SP012', '美的洗衣機', '家電', 2999.00, 150),
('SP013', '格力空調', '家電', 4999.00, 80),
('SP014', '海信電視', '家電', 5999.00, 120),
('SP015', '小米智慧電飯煲', '家電', 399.00, 300);

-- 建立供應商資訊表（supplier）
CREATE TABLE supplier (
    Sh VARCHAR(20) NOT NULL COMMENT '稅號',
    Khyh VARCHAR(20) NOT NULL COMMENT '開戶銀行',
    Lxr VARCHAR(20) NOT NULL COMMENT '聯絡人',
    beizhu VARCHAR(16) COMMENT '備註',
    Gysbh CHAR(8) NOT NULL PRIMARY KEY COMMENT '供應商編號',
    Gysmc VARCHAR(8) NOT NULL COMMENT '供應商名稱',
    Dz VARCHAR(20) NOT NULL COMMENT '地址',
    Yzbm CHAR(6) NOT NULL COMMENT '郵政編碼',
    Dhhm VARCHAR(15) NOT NULL COMMENT '電話號碼',
    Yhzh VARCHAR(20) NOT NULL COMMENT '銀行帳號'
    );

INSERT INTO supplier (Sh, Khyh, Lxr, beizhu, Gysbh, Gysmc, Dz, Yzbm, Dhhm, Yhzh)
VALUES
('100001', '中國銀行', '張三', '無', 'GYS001', '供應商1', '北京市朝陽區', '100000', '010-1234567', '123456789012345678'),
('100002', '工商銀行', '李四', '備註1', 'GYS002', '供應商2', '上海市浦東新區', '200000', '021-7654321', '987654321098765432'),
('100003', '建設銀行', '王五', '備註2', 'GYS003', '供應商3', '廣州市天河區', '300000', '020-1112222', '111222333444555666'),
('100004', '招商銀行', '趙六', '備註3', 'GYS004', '供應商4', '深圳市南山區', '518000', '0755-88888888', '555444333222111000'),
('100005', '交通銀行', '錢七', '備註4', 'GYS005', '供應商5', '杭州市西湖區', '310000', '0571-7777777', '777777777777777777'),
('100006', '農業銀行', '孫八', '備註5', 'GYS006', '供應商6', '南京市鼓樓區', '210000', '025-6666666', '666666666666666666'),
('100007', '郵政銀行', '周九', '備註6', 'GYS007', '供應商7', '成都市武侯區', '610000', '028-5555555', '888888888888888888'),
('100008', '中信銀行', '吳十', '備註7', 'GYS008', '供應商8', '重慶市渝北區', '400000', '023-4444444', '999999999999999999'),
('100009', '興業銀行', '鄭十一', '備註8', 'GYS009', '供應商9', '武漢市洪山區', '430000', '027-3333333', '222222222222222222'),
('100010', '浦發銀行', '趙十二', '備註9', 'GYS010', '供應商10', '南昌市東湖區', '330000', '0791-2222222', '444444444444444444');

-- 1、查詢bmxx（部門資訊表）中所有欄位資料。
select from bmxx;
-- 2、查詢當前日期及時間。
select CURDATE();
-- 3、查詢spxx（商品資訊表）中所屬類別為“手機”，且價格介於80到100之間的商品名稱、所屬類別、價格及數量。
select * from spxx where (jg between 6900 and 9000) and (sslb='手機') ;
-- 4、查詢spxx（商品資訊表）中所屬類別為“家電”，且價格不介於4000到6000之間的商品名稱、所屬類別、價格及數量。
selectfrom spxx where(jg not between 4000 and 6000)and(sslb='家電');
-- 5、查詢ygxx（員工資訊表）中姓名E開頭的，且所屬部門編號不等於BM001的員工編號、員工姓名、許可權級別及所屬部門編號。
selectfrom ygxx where(Name like'E%')and(ssbmbh !='BM001');
-- 6、查詢ygxx（員工資訊表）中姓名B開頭，且身份證號不為空的員工編號、員工姓名及身份證號。
selectfrom ygxx where(Name like'B%') and (ygxx.Sfzh is not null) ;
-- 7、查詢spxx（商品資訊表）中所屬類別，及每種類別所有商品的數量之和，且其數量之和大於12。
SELECT Sslb, SUM(sl) AS TotalQuantity
FROM spxx
GROUP BY Sslb
HAVING SUM(sl) > 12;
-- 8、查詢spxx（商品資訊表）中所屬類別為“手機”的商品名稱，價格及數量，且根據價格進行降序排序，並最終只顯示前面三條價格最大的商品資訊。
SELECT Spmc, Jg, sl
FROM spxx
WHERE Sslb = '手機'
ORDER BY Jg DESC
LIMIT 3;

---

---

create table characters(
id int comment'編號',
worknum varchar(10) comment'工號',
name varchar(10) comment'姓名',
gender char(2) comment'性別',
age tinyint unsigned comment'年齡',
idcards char(18) comment'身份證號',
workaddress varchar(50) comment'工作地址',
enterydate date comment'入職時間'
)comment'員工表';
insert into  characters(id,worknum,name,gender,age,idcards,workaddress,enterydate)
VALUES(1,'1','柳巖','女',20,'123456789012345678','北京','2000-01-01'),
      (2,'2','張無忌','男',18,'123456789012345670','北京','2005-09-01'),
      (3,'3','韋一笑','男','38','123456789712345670','上海','2005-08-01'),
      (4,'4','趙敏','女',18,'123456757123845670','北京','2009-12-01'),
      (5,'5','小昭','女',16,'123456769012345678','上海','2007-07-01'),
      (6,'6','楊道','男','28','12345678931234567X','北京','2006-01-01'),
      (7,'7','範瑤','男',40,'123456789212345670','北京','2005-05-01'),
      (8,'8','黛綺絲','女',38,'123456157123645670','天津','2015-05-01'),
      (9,'9','範涼涼','女','45','123156789012345678','北京','2010-04-01'),
      (10,'10','陳友諒','男',53,'123456789012345670','上海','2011-01-01'),
      (11,'11','張士誠','男',55,'123567897123465670','江蘇','2015-05-01'),
      (12,'12','常遇春','男',32,'123446757152345670','北京','2004-02-01'),
      (13,'13','張三丰','男',88,'123656789012345678','江蘇','2020-11-01'),
      (14,'14','滅絕','女',65,'123456719012345670','西安','2019-05-01'),
      (15,'15','胡青牛','男',70,'12345674971234567X','西安','2018-04-01'),
      (16,'16','周芷若','女',18,null,'北京','2012-06-01');


select name,worknum,age from characters;
select * from characters;
select id,worknum,name,gender,age,idcards,workaddress,enterydate from characters;
select workaddress as'工作地址' from characters;
select distinct workaddress from characters;
select* from characters where age = 88;
select *from characters where age<20;
select*from characters where age<=20;
select *from characters where idcards is null;
select *from characters where idcards is not null;
select *from characters where age <>88;

select*from characters where age  between 15 and 20;
select*from characters where age >=15 and age<=20;

select*from characters where gender='女' and age<20;
select*from characters where age=18 or age=20 or age=40;
select*from characters where age in(18,20,40);
select*from characters where name like'__';
select*from characters where idcards like'_________________X';
select*from characters where idcards like '%X';
select   count(id) from characters;
select avg(age) from characters;
select max(age) from characters;
select min(age) from characters;
select sum(age) from characters where workaddress='西安';
select gender,count(*)from  characters group by gender;
select gender,avg(age)from  characters group by gender;
select workaddress ,count(*) from characters where age<45  group by workaddress having count(*)>=3;

select*from characters order by age ;
select *from characters order by age desc;
select*from characters order by enterydate ;
select *from characters order by enterydate desc;
select *from characters order by age ,enterydate desc;

 -- （頁碼數-1）*每頁展示記錄數
select *from characters limit 0,10;
-- 查詢第2頁,展示數10
select*from characters limit 10,10;

-- 案例練習

-- 查詢年齡為20，21，22，23的女性員工資訊
select *from characters where age in (20,21,22,23) and gender='女';
#select *from characters where age=20 and age=21 and age=22 and age=23;錯誤❌
-- 查詢性別為 男,並且年齡在 20-40 歲(含)以內的姓名為三個字的員工。
select *from characters where gender='男' and age between 20 and 40 and name like'___';
-- 統計員工表中,年齡小於60歲的,男性員工和女性員工的人數。
select gender,count(*)from characters where age<60 group by gender;
-- 查詢所有年齡小於等於35歲員工的姓名和年齡,並對查詢結果按年齡升序排序,如果年齡相同按入職時間降序排序。
select name,age from characters where age<=35 order by age, enterydate desc;
select name,age,enterydate from characters where age<=35 order by age, enterydate desc;
select * from characters where age<=35 order by age, enterydate desc;
-- 查詢性別為男,且年齡在20-40 歲(含)以內的前5個員工資訊,對查詢的結果按年齡升序排序,年齡相同按入職時間升序排序。
select*from characters where age between 20 and 40  order by age ,enterydate  limit 5;

-- 建立使用者 itcast,只能夠在當前主機locathost訪問,密碼123456
create user 'itcast'@'localhost'identified by'123456';

-- 建立使用者 heima,可以在任意主機訪問該資料庫,密碼123456;
create user 'heima'@'%'identified by '123456';
-- 修改使用者 heima 的訪問密碼為 1234;
alter user'heima'@'%' identified with mysql_native_password by '1234';
-- 刪除itcast@localhost使用者
drop user 'itcast'@'localhost';
drop user 'heima'@'localhost';

create table stu(
    stuid int primary key,
    stuname varchar(21),
    stuage tinyint,
    stugender char(2)
);
insert into stu(stuid, stuname, stuage, stugender) VALUES (1,'柳巖',20,'女'),
      (2,'張無忌',18,'男'),
      (3,'韋一笑',20,'男'),
      (4,'趙敏',18,'女'),
      (5,'小昭',18,'女'),
      (6,'楊道',17,'男'),
      (7,'範瑤',16,'男'),
      (8,'黛綺絲',19,'女'),
      (9,'範涼涼',21,'女'),
      (10,'陳友諒',18,'男'),
      (11,'張士誠',17,'男'),
      (12,'常遇春',17,'男'),
      (13,'張三丰',18,'男'),
      (14,'滅絕',19,'女'),
      (15,'胡青牛',18,'男'),
      (16,'周芷若',17,'女');
select*from stu;
alter table stu add email varchar(50);
alter table stu modify column email char(30);
select*from stu;

字串函式

/*函式 功能
CONCAT(S1,S2.Sn) 字串拼接，將S1，S2,Sn拼接成一個字串
LOWER(str) 將字串str全部轉為小寫
UPPER(str) 將字串str全部轉為大寫
LPAD(str,n,pad) 左填充，用字串pad對str的左邊進行填充，達到n個字串長度
RPAD(str,n,pad) 右填充，用字串pad對str的右邊進行填充，達到n個字串長度
TRIM(str) 去掉字串頭部和尾部的空格
SUBSTRING(str,start,len) 返回從字串str從start位置起的len個長度的字串
*/

-- 函式演示

-- concat
select concat('hello','mysql','hello','xukur');
-- lower
select lower('HELLO XUKUR');
-- upper
select upper('hello mysql');
-- lpad
select lpad('1',4,'0');
-- rpad
select rpad('1',5,'0');
-- trim
select trim(' hello xukur ');
-- substring
select substring('hello xukur',3,5);

計算函式

/*
函式 功能
CEIL(x) 向上取整
FLOOR(x) 向下取事
MOD(x,y) 返回x/y的模
RAND() 返回0~1內的隨機數
ROUND(x,y) 求引數x的四捨五入的值，保留y位小數
*/

-- ceil
select ceil(2.5);
-- floor
select floor(2.6);
-- mod
select mod(10,4);
-- ran
select rand();
-- round
select round(4.57986,3);

日期函式

/*常見的日期函式如下:
函式 功能
CURDATE() 返回當前日期
CURTIME() 返回當前時間
NOW() 返回當前日期和時間
YEAR(date) 獲取指定date的年份
MONTH(date) 獲取指定date的月份
DAY(date) 獲取指定date的日期
DATE_ADD(date, INTERVAL expr type) 返回一個日期/時間值加上一個時間間隔expr後的時間值
DATEDIFF(date1,date2) 返回起始時間date1和結束時間date2之間的天數
*/

演示

-- curdate
select curdate();
-- curtime
select curtime();
-- now
select now();
-- year
select year(20230708);
-- DAY(date)
select DAY(20230708);
-- MONTH(date)
select MONTH(20230708);
-- DATE_ADD(date, INTERVAL expr type)
select date_add(now(),interval 1000 day);
-- DATEDIFF(date1,date2)
select datediff('2021-12-13','2020-12-13');

-- 1. 由於業務需求變更,企業員工的工號,統一為5位數,目前不足5位數的全部在前面補0。比如: 1號員工的工號應該為00001。
select lpad('1',5,'0');
update characters set worknum = lpad(worknum,5,'0');
-- 2. 透過資料庫的函式,生成一個六位數的隨機驗證碼。
select lpad(round(rand()*1000000,0),6,'0');
-- 3. 查詢所有員工的入職天數,並根據入職天數倒序排序。
select name,datediff(curdate(),enterydate) as 'enterydays' from characters order by enterydays desc ;

/*
流程函式
流程函式也是很常用的一類函式,可以在SQL語句中實現條件篩選,從而提高語句的效率。
函式																		                功能
IF(value, t, f)																如果value為true,則返回t,否則返回f
IFNULL(value1, value2)														如果value1不為空,返回value1,否則返回value2
CASE WHEN [value1 ] THEN [res1] ... ELSE [ default ] END						如果val1為true,返回res1,…否則返回default預設值
CASE [ expr ] WHEN [vall ] THEN [res1] ... ELSE [ default ] END				如果expr的值等於val1,返回res1,…否則返回default預設值
*/

-- if
select if(true,'direct','error');
-- ifnull
select ifnull(null,'default');
-- case when then else end
-- 需求:查詢emp表的員工姓名和工作地址(北京/上海 -->一線城市,其他-->二線城市)
select name,workaddress,
       (case workaddress when '北京' then '一線城市'when '上海' then'一線城市' else('二線城市') end) as '工作地址'
from characters;



-- 案例:統計班級各個學員的成績,展示的規則如下:
-- -->= 85,展示優秀
-- -->= 60,展示及格
-- 否則,展示不及格


create table score
(
    id      int comment 'ID',
    name    varchar(20) comment '姓名',
    math    int comment '數學',
    english int comment '英語',
    chinese int comment '語文'
)comment '學員成績表';
insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95 ), (2, 'Rose' , 23, 66, 90), (3, 'Jack', 56, 98, 76);
select id,name,math,english,chinese,
       (case when (math>=85) then '優秀' when (math >=60 and math<85) then '及格' else('不及格') end)'數學',
       (case when (english>=85) then '優秀' when (english>=60 and english<85) then '及格'else('不及格') end)'英語',
       (case when (chinese>=85) then '優秀' when (chinese >=60 and chinese<85) then '及格' else('不及格') end)'語文'

from score;

case (math,english,chinese) when (math,english,chinese)>=85 then '優秀' when ((math,english,chinese) >=60 and (math,english,chinese)<85) then '及格'
/*

條件約束

1. 概念:約束是作用於表中欄位上的規則,用於限制儲存在表中的資料。
2. 目的:保證資料庫中資料的正確、有效性和完整性。
3. 分類:
   約束 關鍵字 描述
   非空約束 NOT NULL 限制該欄位的資料不能為null
   唯一約束 UNIQUE 保證該欄位的所有資料都是唯一、不重複的
   主鍵約束 PRIMARY KEY 主鍵是一行資料的唯一標識,要求非空且唯一
   預設約束 DEFAULT 儲存資料時,如果未指定該欄位的值,則採用預設值檢查約束(8.0.16版本之後) CHECK 保證欄位值滿足某一個條件 外來鍵約束 FOREIGN KEY 保證欄位值滿足某一個條件
   */

/*

外來鍵約束

● 刪除/更新行為
行為 說明
NO ACTION 當在父表中刪除/更新對應記錄時,首先檢查該記錄是否有對應 外來鍵,如果有則不 允許刪除/更新。(與RESTRICT一致)
RESTRICT 當在父表中刪除/更新對應記錄時,首先檢查該記錄是否有對應 外來鍵,如果有則不允許刪除/更新。(與NO ACTION一致)
CASCADE 當在父表中刪除/更新對應記錄時,首先檢查該記錄是否有對應 外來鍵,如果有,則也刪除/更新外來鍵在子表中的記錄。
SET NULL 當在父表中刪除對應記錄時,首先檢查該記錄是否有對應外來鍵, 如果有則設定子表中該外來鍵值為null(要求該外來鍵允許取null)。
SET DEFAULT 父表有變更時,子表將外來鍵列設定成一預設的值(Innodb不支援)
ALTER TABLE 表名 ADD CONSTRAINT 外來鍵名稱 FOREIGN KEY(外來鍵欄位)
REFERENCES 主表名(主表欄位名)ON UPDATE CASCADE ON DELETE CASCADE;

*/

create table userinfo  (
    id int primary key auto_increment,
    name varchar(10)  not null unique,
    age int check (age>0 && age<=120),
    status char(1) default '1',
    gender char(1)
);

insert into userinfo(name,age,status,gender)values('Tom',20,'1','男'),('Rose',18,'0','女'),('jack',19,'1','男');
insert into userinfo(name,age,status,gender)values('Tomy',20,'1','男');
insert into userinfo(name,age,status,gender)values('Roser',20,'0','女');
insert into userinfo(name,age,status,gender)values('Toms',-1,'1','男');




-- 多對多關係在資料庫表中的實現
create table student(
id int auto_increment primary key comment 'tID',
name varchar(10) comment '姓名',
no varchar(10) comment '學號'
)comment'學生表';

insert into student values (null,'黛綺絲','2000100101'),(null,'謝遜','2000100102'),(null,'殷天正','2000100103'),(null,'韋一笑','2000100103');

create table course(
id int auto_increment primary key comment '主鍵ID',
name varchar(10) comment '課程名稱'
)comment'課程表';
insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');

create table student_course(
    id int auto_increment comment'編號' primary key ,
    studentid int not null comment'學生編號',
    courseid int not null comment '課程編號',
    constraint fk_courseid foreign key(courseid)references course(id),
    constraint fk_studenid foreign key(studentid)references student(id)
)comment'學生課程中間表';
insert into student_course values (null, 1, 1), (null, 1, 2), (null, 1, 3), (null, 2, 2), (null, 2, 3), (null, 3,4);

-- 一對一的關係
create table tb_user(
    id int auto_increment primary key,
    name char(10),
    age int,
    gender char(2),
    phone char(11)
);
create table tb_user_edu(
    id int auto_increment primary key,
    degree varchar(20),
    major varchar(50),
    primaryschool varchar(50),
    middlescholl varchar(50),
    university varchar(50),
    userid int unique,
    constraint fk_userid foreign key(userid)references tb_user(id)
);

insert into tb_user(id, name, age, gender, phone) values
(null,'黃渤',45,'1','18880001111'),
(null,'冰冰',35,'2','18800002222'),
(null,'碼雲',55,'1','18800008888'),
(null,'李彥宏',50, '1', '18800089999');

insert into tb_user_edu(id, degree, major, primaryschool, middlescholl, university, userid) values
(null,'本科' ,'舞蹈' ,'靜安區第一小學' ,'靜安區第一中學' ,'北京舞蹈學院' , 1),
(null,'碩士' ,'表演' ,'朝陽區第一小學' ,'朝陽區第一中學' ,'北京電影學院' ,2),
(null,'本科','英語' ,'杭州市第一小學' , '杭州市第一中學' , '杭州師範大學' , 3),
(null,'本科' ,'應用數學' ,'陽泉第一小學' ,'陽泉區第一中學' ,'清華大學' , 4);



create table emp (
id int auto_increment primary key ,
name char(20) unique ,
age tinyint(10),
job varchar(20) comment'職位',
salary int comment '薪資',
entrydate date comment '入職時間',
managerid int comment '直屬領導ID',
dept_id int comment'部門ID'
)comment '員工表';
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id) VALUES
(1,'金庸',66,'總裁',20000,'2000-01-01',null,5),
(2,'張無忌',20,'專案經理',12500,'2005-12-05',1,1),
(3,'楊逍',33,'開發',8400,'2000-11-03',2,1),
(4,'韋一笑',48,'開發',11000,'2002-02-05',2,1),
(5,'常遇春',43,'開發',10500,'2004-09-07',3,1),
(6,'小昭',19,'程式設計師鼓勵師',6600,'2004-10-12',2,1),
(7,'滅絕',60,'財務總監',8500,'2004-01-01',1,3),
(8,'周芷若',19,'會計',48000,'2003-02-01',7,3),
(9,'丁敏君',23,'出納',5200,'2005-10-12',7,3),
(10,'趙敏',20,' 市場部總監',12500,'2006-06-01',1,2),
(11,'鹿杖客',56,' 職員',3750,'2006-06-06',10,2),
(12,'鶴筆翁',19,'職員',3750,'2004-02-01',10,2),
(13,'方東白',19,'職員',5500,'2004-02-01',10,2),
(14,'張三丰',88,'銷售總監',14000,'2004-02-01',1,4),
(15,'俞蓮舟',38,'銷售',4600,'2004-02-01',14,4),
(16,'宋遠橋',40,'銷售',4600,'2004-02-01',14,4),
(17,'陳友諒',42,null,2000,'2000-10-12',1,null);


create table dept (
id int  primary key ,
name char(20) unique
);
insert into dept(id, name)
VALUES (1,'研發部'),
       (2,'市場部'),
       (3,'財務部'),
       (4,'銷售部'),
       (5,'總經辦'),
       (6,'人事部');


create table salgrade(
grade int,
losal int,
hisal int

)comment '薪資等級表';

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001, 20000);
insert into salgrade values (7,20001, 25000);
insert into salgrade values (8,25001, 30000);

-- 1. 查詢員工的姓名、年齡、職位、部門資訊 (隱式內連線)

select*from emp,dept;

---

---

-- 新增外來鍵

 alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

-- 刪除外來鍵

alter table emp drop foreign key fk_emp_dept_id;
-- 刪除外來鍵和更新行為
-- ALTER TABLE 表名 ADD CONSTRAINT 外來鍵名稱 FOREIGN KEY(外來鍵欄位)REFERENCES 主表名(主表欄位名)ON UPDATE CASCADE ON DELETE CASCADE;
alter table emp add constraint fk_emp_dept_id foreign key (dept_id)references dept(id) on update cascade on delete cascade;
alter table emp add constraint fk_emp_dept_id foreign key (dept_id)references dept(id) on update set null on delete set null;

/*

-- 內連線兩張表中交集的部分

隱式內連線

SELECT 欄位列表 FROM 表1,表2 WHERE 條件 …;

顯式內連線

SELECT 欄位列表 FROM 表1[INNER] JOIN表2 ON 連線條件 ...;
*/

*/

-- 內連線演示

-- 1. 查詢每一個員工的姓名 ,以及關聯的部門的名稱(隱式內連線實現)
selectfrom emp,dept where dept.id = emp.dept_id;
-- 2. 查詢每一個員工的姓名,以及關聯的部門的名稱(顯式內連線實現)
selectfrom emp join dept on dept.id = emp.dept_id;

-- 外連線

/*
連線查詢-外連線

外連線查詢語法:

左外連線

SELECT 欄位列表 FROM 表1 LEFT [OUTER] JOIN表2ON 條件 ...;
相當於查詢表1(左表)的所有資料 包含 表1和表2交集部分的資料

右外連線

SELECT 欄位列表 FROM 表1 RIGHT [OUTER] JOIN表2 ON 條件 ..;
相當於查詢表2(右表)的所有資料 包含 表1和表2交集部分的資料
*/
-- 外連線演示

-- 1. 查詢emp表的所有資料,和對應的部門資訊(左外連線)
select e.*,d.name from emp e left join dept d on e.dept_id = d.id;


-- 2. 查詢dept表的所有資料,和對應的員工資訊(右外連線)
select d.*,e.* from emp  e right join dept d on  e.dept_id=d.id ;

/*

連線查詢-自連線

自連線查詢語法:
SELECT 欄位列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ...;
自連線查詢,可以是內連線查詢,也可以是外連線查詢。
*/

-- 自連線

-- 1. 查詢員工 及其 所屬領導的名字

select a.name, b.name from emp a join emp b  on a.managerid=b.id;
-- 2. 查詢所有員工 emp 及其領導的名字 emp,如果員工沒有領導,也需要查詢出來
select a.name '員工',b.name '領導' from emp a left join emp b on a.managerid=b.id;

/*

聯合查詢-union,union all

對於union查詢,就是把多次查詢的結果合併起來,形成一個新的查詢結果集。
SELECT 欄位列表 FROM 表A ...
UNION [ ALL ]
SELECT 欄位列表 FROM 表B ....;
-- union all 是會把兩表的所有資料結合到一起，而union 是會去掉重複的一部分去結合
*/
-- 聯合查詢實踐
-- 1. 將薪資低於 5000 的員工,和 年齡大於 50 歲的員工全部查詢出來.

select*from emp where age>50
union all
select*from emp where salary<5000;
select*from emp where age>50
union
select*from emp where salary<5000;

/*

子查詢

● 概念:SQL語句中巢狀SELECT語句,稱為巢狀查詢,又稱子查詢。
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
子查詢外部的語句可以是INSERT/UPDATE/DELETE/SELECT的任何一個。
● 根據子查詢結果不同,分為:
標量子查詢(子查詢結果為單個值)
列子查詢(子查詢結果為一列)
行子查詢(子查詢結果為一行)
表子查詢(子查詢結果為多行多列)
● 根據子查詢位置,分為:WHERE之後、FROM之後、SELECT之後。
*/

-- 標量子查詢

-- 1. 查詢 "銷售部" 的所有員工資訊
select*from emp where dept_id=(select id from dept where name='銷售部');
-- 2. 查詢在“方東白”入職之後的員工資訊
select*from emp where entrydate>(select entrydate from emp where name='方東白');

/*

列子查詢

子查詢返回的結果是一列(可以是多行),這種子查詢稱為列子查詢。
常用的運算子:IN、NOT IN、ANY、SOME、ALL
運算子 描述
IN 在指定的集合範圍之內,多選一
NOT IN 不在指定的集合範圍之內
ANY 子查詢返回列表中,有任意一個滿足即可
SOME 與ANY等同,使用SOME的地方都可以使用ANY
ALL 子查詢返回列表的所有值都必須滿足
*/

-- 列子查詢

-- 1. 查詢“銷售部”和“市場部”的所有員工資訊
select*from emp where dept_id in (select id from dept where name=  '銷售部' or name = '市場部');
-- 2. 查詢比財務部所有人工資都高的員工資訊
select*from emp where salary>all(select salary from emp where dept_id=(select id from dept where name='財務部'));
-- 3. 查詢比研發部其中任意一人工資高的員工資訊
select*from emp where salary>any(select salary from emp where dept_id=(select id from dept where name='研發部'));
select*from emp where salary>some(select salary from emp where dept_id=(select id from dept where name='研發部'));

/*

行子查詢

子查詢返回的結果是一行(可以是多列),這種子查詢稱為行子查詢。
常用的運算子: = 、<>、IN、NOT IN
*/

-- 行子查詢

-- 1. 查詢與“張無忌”的薪資及直屬領導相同的員工資訊;
select*from emp where (salary,managerid) = (select salary,managerid from emp where name='張無忌');

-- 表子查詢

-- 1. 查詢與“鹿杖客”,“宋遠橋”的職位和薪資相同的員工資訊

select*from emp where(salary,job) in (select salary,job from emp where name='鹿杖客'or name='宋遠橋');

-- 2. 查詢入職日期是“2006-01-01”之後的員工資訊,及其部門資訊
select e.*,d.* from (select*from emp where entrydate>'2006-01-01') e left join dept d on e.dept_id =d.id;

-- 1. 查詢員工的姓名、年齡、職位、部門資訊。(隱式內連線)
select e.name,e.age,e.job,d.name,d.id from emp e,dept d where e.dept_id=d.id;

-- 2.查詢年齡小於30歲的員工姓名、年齡、職位、部門資訊。(顯式內連線)
select e.name,e.age,e.job,d.name from emp e join dept d  on d.id = e.dept_id and e.age<30;
-- 3. 查詢擁有員工的部門ID、部門名稱。
select distinct  d.name,d.id from emp e join dept d  on d.id = e.dept_id;
-- 4. 查詢所有年齡大於40歲的員工,及其歸屬的部門名稱;如果員工沒有分配部門,也需要展示出來。
select  e.*,d.name from emp e left join dept d on d.id= e.dept_id where age>40;
-- 5. 查詢所有員工的工資等級。
select e.*,s.* from emp e , salgrade s where (e.salary >= s.losal and e.salary<=s.hisal);
select e.*,s.* from emp e , salgrade s where (e.salary between s.losal and s.hisal);
-- 6. 查詢“研發部”所有員工的資訊及工資等級。
select e.*,s.*,d.name from emp e ,salgrade s ,dept d where (e.salary between s.losal and s.hisal )and (e.dept_id=d.id)and (d.name='研發部');
-- 7. 查詢“研發部”員工的平均工資。
select avg(e.salary) from emp e,dept d where (e.dept_id=d.id)and(d.name='研發部')  ;
-- 8. 查詢工資比“滅絕”高的員工資訊。
select*from emp where(salary) >(select salary from emp where name='滅絕');
-- 9. 查詢比平均薪資高的員工資訊。
select*from emp where(salary)>(select avg(e.salary)from emp e);
select avg(e.salary)from emp e;
-- 10. 查詢低於本部門平均工資的員工資訊。
select*,(select avg(e1.salary)from emp e1 where (e1.dept_id=e2.dept_id))from emp e2 where(e2.salary)<(select avg(e1.salary)from emp e1 where (e1.dept_id=e2.dept_id));

-- 11. 查詢所有的部門資訊,並統計部門的員工人數。
select dept.id ,dept.name ,( count(e.id)  ) '人數' from dept left join emp e on dept.id = e.dept_id group by dept.id, dept.name;

#SELECT id, name, (SELECT COUNT(*) FROM emp WHERE dept_id = id) AS '人數' FROM dept;錯誤❌
-- 12. 查詢所有學生的選課情況,展示出學生名稱,學號,課程名稱
select*from student;

-- 作業

create table teacher (
    tno char(10) primary key,
    tn varchar(20) unique comment'姓名',
    sex char(2) comment '性別' ,
    age int comment'年齡',
    prof char(10) comment '職稱',
    sal  int comment'工資',
    comm int comment '崗位津貼',
    dept char(20) comment '系別'
);
insert into teacher(tno, tn, sex, age, prof, sal, comm, dept)
values  ('t1','李力','男',47,'教授',1500,3000,'計算機'),
        ('t2','王平','女',28,'講師',800,1200,'資訊'),
        ('t3','劉偉','男',30,'講師',900,1200,'計算機'),
        ('t4','張雪','女',51,'教授',1600,3000,'自動化'),
        ('t5','張蘭','女',39,'副教授',1300,2000,'資訊');


create table students(
    sno char(10) primary key,
    sn varchar(20) unique comment'姓名',
    sex char(2) comment '性別' ,
    age int comment'年齡',
    dept char(20) comment '系別'
);
insert into students(sno, sn, sex, age, dept)
values  ('s1','趙亦','女',17,'計算機'),
        ('s2','錢爾','男',18,'資訊'),
        ('s3','孫珊','女',20,'自動化'),
        ('s4','李思','男',21,'自動化'),
        ('s5','周武','男',19,'計算機'),
        ('s6','吳麗','女',20,'資訊');
create table courses(
    cno char(10) primary key,
    cn varchar(20) unique comment'課程名',
    ct int comment'課程'
);
insert into courses(cno, cn, ct)
values  ('c1','程式設計',60),
        ('c2','微機原理',80),
        ('c3','數字邏輯',60),
        ('c4','資料結構',80),
        ('c5','資料庫',60),
        ('c6','編譯原理',60),
        ('c7','作業系統',60);
create table sc(
    sc_id int auto_increment primary key ,
    snos char(10) ,
    cnos char(10) ,
    scores int,
 constraint fk_sno foreign key(snos)references students(sno),
 constraint fk_cno foreign key(cnos)references courses(cno));
insert into sc(snos,cnos, scores)
values
    ('s1','c1',90),
    ('s1','c2',85),
    ('s2','c5',57),
    ('s2','c6',80),
    ('s2','c7',null),
    ('s2','c4',70),
    ('s3','c1',75),
    ('s3','c2',70),
    ('s3','c4',85),
    ('s4','c1',93),
    ('s4','c2',85),
    ('s4','c3',83),
    ('s5','c2',89);
create table tc(
    tnos char(10),
    cnos char(10),
    constraint fk_tnos foreign key(tnos)references teacher(tno),
    constraint fk_snos foreign key(cnos)references courses(cno)
    );
insert into tc(tnos, cnos)
VALUES ('t1','c1'),
       ('t1','c4'),
       ('t2','c5'),
       ('t3','c1'),
       ('t3','c5'),
        ('t4','c2'),
        ('t4','c3'),
        ('t5','c5'),
        ('t5','c7');
-- （1）	查詢年齡大於35歲的男老師的教師號、姓名及職稱。
select tno,tn, prof from teacher where age>35 and sex='男';
--  (2)查詢教師“王平”所教課程的課程號和課程名稱。
select*from teacher where tn='王平';
SELECT c.cno, c.cn FROM courses c JOIN tc ON c.cno = tc.cnos JOIN teacher t ON t.tno = tc.tnos WHERE t.tn = '王平';

create table R(
    a int ,
    b int ,
    c int
);
insert into R(a, b, c)
VALUES
        (1,2,3),
        (2,2,2),
        (5,2,6);
create table S(
    a int ,
    b int ,
    c int
);
insert into S(a, b, c)
VALUES
        (3,2,1),
        (2,1,3),
        (5,2,6);
create table T(
    a int ,
    d int
);
insert into T(a, d)
VALUES
        (1,3),
        (2,2),
        (3,1);

條件查詢

RuS

這個查詢 RuS 的目的是找出表 R 和表 S 中滿足特定條件的行，並將它們合併在一起。

SELECT * FROM R WHERE (a, b, c) IN ((3, 2, 1), (2, 1, 3), (5, 2, 6))
UNION
SELECT * FROM S WHERE (a, b, c) IN ((3, 2, 1), (2, 1, 3), (5, 2, 6));
#### -- R∩S

-- 這個查詢是一個內連線（INNER JOIN），目的是找出表 R 和表 S 中滿足特定條件的行，並返回表 R 中的所有列。
SELECT R.* FROM R INNER JOIN S ON R.a = S.a AND R.b = S.b AND R.c = S.c;

-- R-S 這個查詢是一個左連線（LEFT JOIN），目的是找出表 R 中存在但在表 S 中不存在的行，並返回表 R 中的所有列。

SELECT R.*FROM R
LEFT JOIN S ON R.a = S.a AND R.b = S.b AND R.c = S.c
WHERE S.a IS NULL;

-- RxS
-- 這個查詢是一個交叉連線（CROSS JOIN），也稱為笛卡爾積，目的是返回表 R 和表 S 中所有行的組合。

SELECT *
FROM R
CROSS JOIN S;

/*
交叉連線會返回兩個表中所有行的組合，即每一行與另一個表中的所有行進行組合。
在這個查詢中，表 R 中有3行，表 S 中有3行，因此結果中共有3x3=9行。
每一行都包含了表 R 和表 S 中對應行的所有列。
例如，第一行表示表 R 中的第一行 (1, 2, 3) 與表 S 中的第一行 (3, 2, 1) 的組合。
*/

-- R ⋈T
-- R ⋈ T 表示表 R 和表 T 的自然連線，自然連線是基於兩個表中具有相同值的列進行連線。

SELECT *
FROM R
NATURAL JOIN T;

-- ΠA,B(σC=6(R))
-- ΠA,B(σC=6(R)) 表示對錶 R 中滿足條件 C=6 的行進行投影，只保留列 A 和 B
SELECT DISTINCT R.a, R.b
FROM R
WHERE R.c = 6;
-- ΠB,C(σC=3(S⋈T))  ΠB,C(σC=3(S⋈T)) 表示對錶 S 和表 T 的自然連線結果中滿足條件 C=3 的行進行投影，只保留列 B 和 C。
SELECT DISTINCT S.b, S.c
FROM S
JOIN T ON S.a = T.a
WHERE S.c = 3;

create table rr(
    a char(5) ,
    b char(5) ,
    c char(5),
    d char(5)
);
insert into rr(a, b, c, d)
values ('a1','b2','c1','d1'),
       ('a1','b2','c2','d1'),
       ('a1','b1','c1','d2'),
       ('a2','b2','c3','d3');
create table ss(
    c char(5),
    d char(5),
    e char(5)
);
insert into ss(c, d, e)
values ('c1','d1','e1'),
       ('c2','d2','c2');

-- R÷S
-- 這個查詢 R ÷ S 的目的是找出表 rr 中有但表 ss 中沒有對應記錄的行。除運算，即找出在第一個表中存在但在第二個表中不存在的元組。根據具體的資料表結構和需求，選擇合適的查詢語句來執行除運算。
/*
在MySQL中，除運算可以透過不同的查詢語句來實現。以下是幾種常見的實現方式：
使用NOT IN子查詢：

SELECT *
FROM table1
WHERE (column1, column2) NOT IN (SELECT column1, column2 FROM table2);
使用LEFT JOIN和WHERE子句：
SELECT table1.*
FROM table1
LEFT JOIN table2 ON table1.column1 = table2.column1 AND table1.column2 = table2.column2
WHERE table2.column1 IS NULL AND table2.column2 IS NULL;
使用NOT EXISTS子查詢：
SELECT *
FROM table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM table2 t2
    WHERE t1.column1 = t2.column1 AND t1.column2 = t2.column2
);

*/
SELECT DISTINCT rr.a, rr.b, rr.c, rr.d
FROM rr
LEFT JOIN ss ON rr.c = ss.c AND rr.d = ss.d
WHERE NOT EXISTS (
    SELECT 1
    FROM ss
    WHERE rr.c = ss.c AND rr.d = ss.d
);

create table R_r(
    a1 int ,
    a2 int,
    a3 int
);
insert into R_r(a1,a2,a3)
values(1,2,3),
      (2,1,4),
      (3,4,4),
      (4,6,7);
create table S_s(
    a1 int,
    a2 int,
    a4 int
);
insert into S_s(a1,a2,a4)
values (1,9,1),
       (2,1,4),
       (3,4,4),
       (4,8,3);

-- Π_{a1,a2}(σ_{1<3}(S)))

SELECT DISTINCT a1, a2
FROM (
    SELECT *
    FROM S_s
    WHERE a1 < a4
) AS S_subset;

-- R÷（Π_{a1,a2}(σ_{1<3}(S)))

SELECT R_r.a1, R_r.a2
FROM R_r
LEFT JOIN (
    SELECT DISTINCT a1, a2
    FROM S_s
    WHERE a1 < a4
) AS S_subset
ON R_r.a1 = S_subset.a1 AND R_r.a2 = S_subset.a2
WHERE S_subset.a1 IS NULL;
--
SELECT r.*
FROM r
LEFT JOIN s ON r.a = s.a AND r.b = s.b
WHERE s.a IS NULL AND s.b IS NULL;

SELECT *
FROM r
WHERE (r.a, r.b,r.c) NOT IN (SELECT s.a, s.b,s.c FROM s);

-- 資料準備
create table account(
id int auto_increment primary key comment'主鍵ID',
    name varchar(10)comment'姓名',
money int comment'餘額')
comment'賬戶表';
insert into account(id, name, money)VALUES (null,'張三',2000),(null,'李四',2000);
-- 恢復資料
update account set money =2000 where name='張三'or name='李四';
-- 轉賬操作
-- 查詢張三賬戶餘額
select money,name  from account where name='張三';
select money,name  from account where name='李四';

-- 如果張三餘額大於一千餘額要減一千
update account set money =money -1000  where name='張三'and money>=1000 ;
-- 異常
#error#
update account set money = money +1000 where name='李四';

/*
事務操作
· 檢視/設定事務提交方式
SELECT @@autocommit ;
SET @@autocommit=0;
· 提交事務
COMMIT ;
回滾事務
ROLLBACK ;
/
select @@autocommit;
set @@autocommit=1;
set @@autocommit=0;
/
-- 方式2
事務操作
· 開啟事務
START TRANSACTION 或 BEGIN;
· 提交事務
COMMIT;
回滾事務
ROLLBACK ;
/
start transaction ;
-- 轉賬操作
-- 查詢張三賬戶餘額
/

select money,name  from account where name='張三';
select money,name  from account where name='李四';

*/

-- 如果張三餘額大於一千餘額要減一千

update account set money =money -1000  where name='張三'and money>=1000 ;

-- 異常

update account set money = money +1000 where name='李四';

-- 如果操作成功就提交提交事故

commit ;

-- 如果失敗那就回滾事務

rollback ;

/*
事務的四大特性
· 原子性(Atomicity):事務是不可分割的最小操作單元,要麼全部成功,要麼全部失敗。
· 一致性(Consistency):事務完成時,必須使所有的資料都保持一致狀態。
· 隔離性(Isolation):資料庫系統提供的隔離機制,保證事務在不受外部併發操作影響的獨立環境下執行。
·永續性(Durability):事務一旦提交或回滾,它對資料庫中的資料的改變就是永久的。
/
/
併發事務問題
問題 描述
髒讀 一個事務讀到另外一個事務還沒有提交的資料。
不可重複讀 一個事務先後讀取同一條記錄,但兩次讀取的資料不同,稱之為不可重複讀。
幻讀 一個事務按照條件查詢資料時,沒有對應的資料行,但是在插入資料時,又發現這行資料已經存在,好像出現了"幻影"
/
/
事務隔離級別
隔離級別 贓讀 幻讀 不可重複讀
Read uncommitted √ √ √
Read committed x √ √
Repeatable Read(預設) x x √
Serializable x x x
對為會出現併發問題，錯為併發問題不會出現；
併發級別從上到下依序升高；
serializable 安全性最高但是效能最差；
read uncommitted 安全性最差，但是效能最高；
Repeatable Read(預設) 是資料庫中的預設的隔離級別；
-- 檢視事務隔離級別

SELECT @@TRANSACTION_ISOLATION;

-- 設定事務隔離級別

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

select @@transaction_isolation;
set session transaction isolation level REPEATABLE READ;

create database arthur;
use arthur;
use xukurs;

/*
MySQL體系結構
● 連線層
最上層是一些客戶端和連結服務,主要完成一些類似於連線處理、授權認證、及相關的安全方案。伺服器也會為安全接入的每個客戶
端驗證它所具有的操作許可權。
● 服務層
第二層架構主要完成大多數的核心服務功能,如SQL介面,並完成快取的查詢,SQL的分析和最佳化,部分內建函式的執行。所有跨存
儲引擎的功能也在這一層實現,如過程、函式等。
● 引擎層
儲存引擎真正的負責了MySQL中資料的儲存和提取,伺服器透過API和儲存引擎進行通訊。不同的儲存引擎具有不同的功能,這樣我
們可以根據自己的需要,來選取合適的儲存引擎。
● 儲存層
主要是將資料儲存在檔案系統之上,並完成與儲存引擎的互動。
*/

show create table account;
show engines;

/*
儲存引擎特點
· InnoD
介紹
InnoDB是一種兼顧高可靠性和高效能的通用儲存引擎,在MySQL 5.5之後,InnoDB是預設的MySQL儲存引擎。
▶特點
DML操作遵循ACID模型,支援事務;
行級鎖,提高併發訪問效能;
支援外來鍵FOREIGN KEY約束,保證資料的完整性和正確性;
▶ 檔案
XXX.ibd:xxx代表的是表名,innoDB引擎的每張表都會對應這樣一個表空間檔案,儲存該表的表結構(frm、sdi)、資料和索引。
引數:innodb_file_per_table
/
/
儲存引擎特點
MyISAM
▷ 介紹
MylSAM是MySQL早期的預設儲存引擎。
特點
不支援事務,不支援外來鍵
支援表鎖,不支援行鎖
訪問速度
檔案
xxx.sdi: 儲存表結構資訊
XXX.MYD:儲存資料
XXX.MYI:儲存索引
/
/
儲存引擎特點
. Memory
▶ 介紹
Memory引擎的表資料時儲存在記憶體中的,由於受到硬體問題、或斷電問題的影響,只能將這些表作為臨時表或快取使用。
▶ 特點
記憶體存放
hash索引(預設)
▶ 檔案
xxx.sdi: 儲存表結構資訊
*/

/*
儲存引擎特點
特點 InnoDB MyISAM Memory
儲存限制 64TB 有 有
事務安全 支援 - -
鎖機制 行鎖 支援 表鎖
B+tree索引 支援 支援 支援
Hash索引 - - -
全文索引 - - 支援
空間使用 高 高 N/A
記憶體使用 高 低 中等
批次插入速度 低 高 高
支援外來鍵 支援 - -
/
/
儲存引擎選擇
在選擇儲存引擎時,應該根據應用系統的特點選擇合適的儲存引擎。對於複雜的應用系統,還可以根據實際情況選擇多種儲存引擎進行組合。
InnoDB:是Mysql的預設儲存引擎,支援事務、外來鍵。如果應用對事務的完整性有比較高的要求,在併發條件下要求資料的一致性,資料操作除了插入和查詢之外,還包含很多的更新、刪除操作,那麼InnoDB儲存引擎是比較合適的選擇。
MylSAM:如果應用是以讀操作和插入操作為主,只有很少的更新和刪除操作,並且對事務的完整性、併發性要求不是很高,那麼選擇這個儲存引擎是非常合適的。
MEMORY:將所有資料儲存在記憶體中,訪問速度快,通常用於臨時表及快取。MEMORY的缺陷就是對錶的大小有限制,太大的表無法快取在記憶體中,而且無法保障資料的安全性。
*/
索引結構
MySOL的索引是在儲存引擎層實現的，不同的儲存引警有不同的結構，主要包含以下幾種:

索引結構 描述
B+Tree索引 最常見的索引型別，大部分引擎都支援 B+樹索引
Hash索引 底層資料結構是用雜湊表實現的,只有精確匹配索引列的查詢才有效,不支援範圍查詢
R-tree(空間索引) 空間索引是MVISAM引擎的一個特殊索引型別，主要用於地理空間資料型別，通常使用較少
Full-text(全文索引) 是一種透過建立倒排索引,快速匹配文件的方式。類似於Lucene,Solr,ES

索引結構
索引 InnoDB MyISAM Memory
B+tree索引 支援 支援 支援
Hash 索引 不支援 不支援 支援
R-tree 索引 不支援 支援 不支援
Full-text 5.6版本之後支援 支援 不支援

索引結構
· Hash
Hash索引特點
Hash索引只能用於對等比較(=,in),不支援範圍查詢(between,>,<,....)
無法利用索引完成排序操作
查詢效率高,通常只需要一次檢索就可以了,效率通常要高於B+tree索引

為什麼InnoDB儲存引擎選擇使用B+tree索引結構?
相對於二叉樹,層級更少,搜尋效率高;
對於B-tree,無論是葉子節點還是非葉子節點,都會儲存資料,這樣導致一頁中儲存的鍵值減少,
指標跟著減少,要同樣儲存大量資料,只能增加樹的高度,導致效能降低;
相對Hash索引,B+tree支援範圍匹配及排序操作;

索引分類
分類 含義 關鍵字 特點
主鍵索引 針對於表中主鍵建立的索引 PRIMARY 預設自動建立,只能有一個 唯一索引 避免同一個表中某資料列中的值重複 UNIQUE 可以有多個 常規索引 快速定位特定資料 可以有多個
全文索引 全文索引查詢的是文字中的關鍵詞, FULLTEXT 可以有多個 而不是比較索引中的值
在InnoDB儲存引擎中,根據索引的儲存形式,又可以分為以下兩種:
分類 含義 特點 聚集索引(Clustered Index) 將資料儲存與索引放到了一塊, 必須有,而且只有一個 索引結構的葉子節點儲存了行資料
二級索引(Secondary Index) 將資料與索引分開儲存, 可以存在多個 索引結構的葉子節點關聯的是對應的主鍵 聚集索引選取規則:
如果存在主鍵,主鍵索引就是聚集索引。
如果不存在主鍵,將使用第一個唯一(UNIQUE)索引作為聚集索引。
如果表沒有主鍵,或沒有合適的唯一索引,則InnoDB會自動生成一個rowid作為隱藏的聚集索引。

1. 以下SQL語句,那個執行效率高?為什麼?
   select * from user where id = 10;
   select * from user where name ='Arm';
   備註:id為主鍵,name欄位建立的有索引;
   因為id是聚集索引要透過id之下的key值去查詢遍歷然後找到所需的id的值下的行資料查就可以了；
   若果是name欄位查詢先要在二級索引下找到指定的name值然後再返回到聚集索引下的id值然後查詢行資料；
   顯然主鍵的查詢效率較快效能較高。

InnoDB主鍵索引的B+tree高度為多高呢?
假設:一行資料大小為1k,一頁中可以儲存16行這樣的資料。InnoDB的指標佔用6
個位元組的空間,主鍵即使為bigint,佔用位元組數為8。

ex:高度為2:
n8+(n+1)6=161024,算出n約為 1170
117116=18736
ex:高度為3:
1171117116=21939856

索引語法

● 建立索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name, ... ) ;

● 檢視索引

SHOW INDEX FROM table_name ;

● 刪除索引

DROP INDEX index_name ON table_name ;

-----------------------------需求——————————————

-- 1. name欄位為姓名欄位,該欄位的值可能會重複,為該欄位建立索引。
create index idx_userinfo_name on user_info(name);
-- 2. phone手機號欄位的值,是非空,且唯一的,為該欄位建立唯一索引。
create unique index idx_userinfo_phone  on user_info(phone);
-- 3. 為profession、age、status建立聯合索引。
create index idx_userinfo_pro_age_sta on user_info (profession,age,status);
4. 為email建立合適的索引來提升查詢效率。
create index idx_userinfo_email on user_info (email);

● SQL執行頻率

MySQL客戶端連線成功後,透過show[session|global] status命令可以提供伺服器狀態資訊。透過如下指令,可以檢視當前資料庫的
INSERT、UPDATE、DELETE、SELECT的訪問頻次,(session是當前會話|global是全域性的會話);

SHOW GLOBAL STATUS LIKE 'Com______';

●慢查詢日誌

慢查詢日誌記錄了所有執行時間超過指定引數(long_query_time,單位:秒,預設10秒)的所有SQL語句的日誌。
MySQL的慢查詢日誌預設沒有開啟,需要在MySQL的配置檔案(/etc/my.cnf)中配置如下資訊:

show variables like 'slow_query_log';

開啟MySQL慢日誌查詢開關

slow_query_log=1

設定慢日誌的時間為2秒,SQL語句執行時間超過2秒,就會視為慢查詢,記錄慢查詢日誌
long_query_time=2
配置完畢之後,透過以下指令重新啟動MySQL伺服器進行測試,檢視慢日誌檔案中記錄的資訊/var/lib/mysql/localhost-slow.log;

//MySQL看密碼
sudo cat /etc/mysql/debian.cnf；
//直接登入MySQL
mysql -udebian-sys-maint -pUsMlG8ItZtu7JyZx;

-- 書寫新增 100 萬條資料的函式
SET GLOBAL log_bin_trust_function_creators=TRUE; -- 建立函式一定要寫這個
DELIMITER $$   -- 寫函式之前必須要寫，該標誌

CREATE FUNCTION mock_data()        -- 建立函式（方法）
RETURNS INT                         -- 返回型別
BEGIN                                -- 函式方法體開始
    DECLARE num INT DEFAULT 1000000; -- 定義一個變數num為int型別。預設值為1000000
    DECLARE i INT DEFAULT 0; 

    WHILE i < num DO                 -- 迴圈條件
         INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) 
         VALUES(CONCAT('使用者',i),'2548928007qq.com',CONCAT('18',FLOOR(RAND() * ((999999999 - 100000000) + 1000000000))),FLOOR(RAND()  *  2),UUID(),FLOOR(RAND()  *  100));
        SET i =  i + 1;    -- i自增    
    END WHILE;        -- 迴圈結束
    RETURN i;
END; 

SELECT mock_data(); -- 呼叫函式

root@xukur-virtual-machine:/home/xukur# cd /var/lib/mysql/
root@xukur-virtual-machine:/var/lib/mysql# ll
cat  xukur-virtual-machine-slow.log
tail -f xukur-virtual-machine-slow.log

profile詳情

show profiles 能夠在做SQL最佳化時幫助我們瞭解時間都耗費到哪裡去了。

show profiles;

-- 透過have_profiling引數,能夠看到當前MySQL是否支援profile操作:
SELECT @@have_profiling ;

預設profiling是關閉的,可以透過set語句在session/global級別開啟profiling:

SET profiling = 1;

檢視指定query_id的SQL語句各個階段的耗時情況

show profile for query query_id;

檢視指定query_id的SQL語句CPU的使用情況

show profile cpu for query query_id;

● explain執行計劃

EXPLAIN 或者DESC命令獲取MySQL如何執行SELECT語句的資訊,包括在SELECT語句執行過程中表如何連線和連線的順序。
語法:
直接在select語句之前加上關鍵字explain/desc

EXPLAIN SELECT 欄位列表 FROM 表名 WHERE 條件;

● explain執行計劃
EXPLAIN 執行計劃各欄位含義:
Id
select查詢的序列號,表示查詢中執行select子句或者是操作表的順序(id相同,執行順序從上到下;id不同,值越大,越先執行)。
select_type
表示SELECT的型別,常見的取值有SIMPLE(簡單表,即不使用表連線或者子查詢)、PRIMARY(主查詢,即外層的查詢)、UNION(UNION中的第二個或者後面的查詢語句)、SUBQUERY(SELECT/WHERE之後包含了子查詢)等
type
表示連線型別,效能由好到差的連線型別為NULL（一般在業務客戶系統中不會出現null這個情況）、system（一般出現在訪問系統表示時會出現）、const、eq_ref、ref(非唯一索引）、range、index、all。
例如：

explain select 'A';-- 連線型別為NULL，任何表都沒有用到。
explain select id from user_info;-- 連線型別為index效能有點差。
explain select name from user_info;--連線型別為all效能最差。
explain select id  from user_info where id ='1';-- 連線型別為const效能好。一般在唯一索引搜尋中或者是在主鍵中才會出現。

possible_key
顯示可能應用在這張表上的索引,一個或多個。
Key
實際使用的索引,如果為NULL,則沒有使用索引。
Key_len
表示索引中使用的學節數,該值為索引欄位最大可能長度,並非實際使用長度,在不損失精確性的前提下,長度越短越好。
rows
MySQL認為必須要執行查詢的行數,在innodb引擎的表中,是一個估計值,可能並不總是準確的。
filtered
表示返回結果的行數佔需讀取行數的百分比,filtered的值越大越好。

索引使用

● 最左字首法則

如果索引了多列(聯合索引),要遵守最左字首法則。最左字首法則指的是查詢從索引的最左列開始,並且不跳過索引中的列。最左邊的列一旦不出現他就會失效。如果跳躍某一列,索引將部分失效(後面的欄位索引失效)。跟欄位放的位置無關

explain select*from user_info where profession='軟體工程'and age=31 and status='0';
explain select*from  user_info where profession='軟體工程'and age=31;
explain select*from  user_info where profession='軟體工程';
explain select * from  user_info where age = 31 and slatus = '0';
explain select * from  user_info where status = '0';

explain select * from user_info where age = 31 and slatus = '0' ;這條語句中的索引失效了因為索引最左邊的列沒有出現這樣它就會全部失效。
explain select*from user_info where profession='軟體工程'and status='0';
這條語句中的索引部分失效了因為跳過了age欄位。

● 範圍查詢

聯合索引中,出現範圍查詢(>,<),範圍查詢右側的列索引失效
例如：

explain select *from user_info where profession='軟體工程' and age>30 and status='0'; -- 後面的status 索引會失效
explain select*from user_info where profession='軟體工程'and age>=30 and status='0';-- 在此表中索引沒有失效,因為大於等於範圍不會讓它失效，儘量在客戶需求允許下儘量使用大於等於範圍。

● 索引列運算

不要在索引列上進行運算操作,索引將失效。
例如：

explain select * from user_info where substring(phone,10,2) = '15';

使用字串時不加引號部分會使索引列失效
例如：

explain  select* from user_info where substring(phone,17799990001);
explain  select* from user_info where phone= 17799990001;
#雖然會出現可能使用到的的索引列（possible_keys）但是索引列依然會失效。
explain select*from user_info where profession='軟體工程'and age=31 and status=0;
-- 後面的索引列部分失效（status失效了）

使用模糊查詢時如果僅僅是尾部模糊匹配,索引不會失效。如果是頭部模糊匹配,索引失效（所以在大資料量的情況下要儘量規避模糊匹配的使用。

explain select*from user_info where profession like '%工程';
#頭部模糊匹配會使索引全部失效。
explain select*from user_info where profession like '軟體%';
#尾部模糊匹配不會使索引消失，但是使用的索引列中所查詢的那一列會失效（idx_userinfo_pro_age_sta，pro那一部分失效了）。
explain select*from user_info where profession like '%工%';
#模糊匹配頭尾都用時也會是索引全部失效

● or連線的條件

用or分割開的條件,如果or前的條件中的列有索引,而後面的列中沒有索引,那麼涉及的索引都不會被用到。

explain select * from user_info where id = 10 or age = 23;
explain select * from user_info where phone = '17799990017' or age = 23;
#雖然會出現可能使用到的的索引列（possible_keys中會出現primary），但是索引列依然會失效。

由於age沒有索引,所以即使id、phone有索引,索引也會失效。所以需要針對於age也要建立索引。

create index  idx_userinfo_age on user_info(age);
explain select * from user_info where phone = '17799990017' or age = 23;
#但是建立好針對的索引之後就可以解決這個問題了

● 資料分佈影響

如果MySQL評估使用索引比全表更慢,則不使用索引。

select * from tb_user where phone >='17799990005';
#他不會走索引
select * from tb_user where phone >='17799990015';
#它會走索引。
explain select *from user_info where profession is null;
#它是會走索引的即使表裡沒有null值，相當於查詢表中的某一個資料所以它會走索引。
explain select *from user_info where profession is not NULL;
#它不會走索引，因為對於mysql系統底層來說，已經相當於走全表掃描
#如果把表中所查詢的列設定為null值 相對於這個語句來說（select *from user_info where profession is null;）-- 系統會走全表掃描；相反select *from user_info where profession is not NULL；會走索引。

綜合來說我們查詢範圍資料時所查詢資料佔相對全表或者所有表比例較大它走全表掃描，但是資料佔比不大時它會走索引。因為走索引比掃描全表更大。

● SQL提示

SQL提示,是最佳化資料庫的一個重要手段,簡單來說,就是在SQL語句中加入一些人為的提示來達到最佳化操作的目的。

use index:（建議）

explain select*from user_info use index(idx_userinfo_pro) where profession='軟體工程';

use idex 對mysql提出一個建議提示，如果指定索引掃描速度快那麼它會選則指定索引但是如果在指定索引的掃描速度較慢它會選擇更快的索引。

ignore index:（忽略）

explain select * from tb_user ignore index(idx_userinfo_pro) where profession ='軟體工程';

ignore是對mysql提出一個忽略提示，在這個提示中不會在mysql內部不會進行評估，直接忽略你所指定的索引。

force index:（強制）

explain select * from tb_user force index(idx_userinfo_pro) where profession='軟體工程';

force index 是強制性的執行你指定的索引。

覆蓋索引

儘量使用覆蓋索引(查詢使用了索引,並且需要返回的列,在該索引中已經全部能夠找到),減少select*。

explain select id, profession from user_info where profession = '軟體工程' and age = 31 and status = '0';

explain select id,profession,age, status from user_info where profession = '軟體工程' and age=31 and status = '0';

explain select id,profession,age, status, name from user_info where profession ='軟體工程' and age = 31 and status = '0';

explain select * from user_info where profession = '軟體工程' and age = 31 and status = '0';

知識小貼士:

using index condition:查詢使用了索引,但是需要回表查詢資料。它的速度會較慢，回表查詢是二次查詢也就是它會取到id然後返回表中的聚集索引這一列取到我們想要的資料
using where; using index:查詢使用了索引,但是需要的資料都在索引列中能找到,所以不需要回表查詢資料。它的速度會較快

● 字首索引

當欄位型別為字串(varchar,text等)時,有時候需要索引很長的字串,這會讓索引變得很大,查詢時,浪費大量的磁碟IO,影響查詢效率。此時可以只將字串的一部分字首,建立索引,這樣可以大大節約索引空間,從而提高索引效率。

create index idx_xxxx on table_name(column(n));-- n代表前n個字元

字首長度

可以根據索引的選擇性來決定,而選擇性是指不重複的索引值(基數)和資料表的記錄總數的比值,索引選擇性越高則查詢效率越高,唯一索引的選擇性是1,這是最好的索引選擇性,效能也是最好的。

select count(distinct email) / count(*) from user_info;
select count(distinct substring(email,1,5)) / count(*) from user_info ;

索引使用

單列索引與聯合索引

單列索引:即一個索引只包含單個列。
聯合索引:即一個索引包含了多個列；單列索引可能會回表查詢降低查詢效能。
在業務場景中,如果存在多個查詢條件,考慮針對於查詢欄位建立索引時,建議建立聯合索引,而非單列索引。如果我們使用聯合索引時使用覆蓋索引雖然聯合索引是二級索引但是它是不會回表查詢，可以提高查詢的效能。值得注意的是聯合索引的欄位順序，它一定要遵守最左字首法則。

索引設計原則

• 針對於資料量較大,且查詢比較頻繁的表建立索引。
• 針對於常作為查詢條件(where)、排序(order by)、分組(group by)操作的欄位建立索引。
• 儘量選擇區分度高的列作為索引,儘量建立唯一索引,區分度越高,使用索引的效率越高。
• 如果是字串型別的欄位,欄位的長度較長,可以針對於欄位的特點,建立字首索引。
• 儘量使用聯合索引,減少單列索引,查詢時,聯合索引很多時候可以覆蓋索引,節省儲存空間,避免回表,提高查詢效率。
• 要控制索引的數量,索引並不是多多益善,索引越多,維護索引結構的代價也就越大,會影響增刪改的效率。
• 如果索引列不能儲存NULL值,請在建立表時使用NOT NULL約束它。當最佳化器知道每列是否包含NULL值時,它可以更好地確定哪個索引最有效地用於查詢。

1. 索引概述
   索引是高效獲取資料的資料結構。
2. 索引結構
   B+Tree
   Hash
3. 索引分類
   主鍵索引、唯一索引、常規索引、全文索引、聚集索引、二級索引。
4. 索引語法

create [unique ] index xxx on xxx(xxx);
show index from xxxx ;
drop index xxx on xxxx ;

5. SQL效能分析
   執行頻次、慢查詢日誌、profile、explain
6. 索引使用
   聯合索引
   索引失效
   SQL提示
   覆蓋索引
   字首索引
   單列/聯合索引
7. 索引設計原則
   表
   欄位
   索引

插入資料

● insert最佳化

批次插入

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

手動提交事務

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

主鍵順序插入

主鍵亂序插入:

8 1 9 21 88 2 4 15 89 5 7 35 21

主鍵順序插入:

1 2 3 4 5 7 8 9 15 21 88 89

客戶端連線服務端時,加上引數-- loca-infile

mysql -- local-infile -u root -p

設定全域性引數local_infile為1,開啟從本地載入檔案匯入資料的開關

set global local_infile = 1;

執行load指令將準備好的資料,載入到表結構中

load data local infile '/home/xukur/code_learner.sql' into table code_learner fields terminated by ',' lines terminated by '\\n';

create table`code_learner`(
`id` INT (11) NOT NULL AUTO_INCREMENT,
`sex` CHAR (1) DEFAULT NULL,
`learnername`  VARCHAR (50) NOT NULL,
`name` VARCHAR (20) NOT NULL,
`birthday` DATE DEFAULT NULL,
`password` VARCHAR (50) NOT NULL,
PRIMARY KEY ( id ),
UNIQUE KEY `unique_learner_learnername` (`learnername`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

主鍵最佳化

資料組織方式

在InnoDB儲存引擎中,表資料都是根據主鍵順序組織存放的,這種儲存方式的表稱為索引組織表(index organized table IOT)。
![[Pasted image 20240508211011.png]]

● 頁分裂

頁可以為空,也可以填充一半,也可以填充100%。每個頁包含了2-N行資料(如果一行資料多大,會行溢位),根據主鍵排列。
![[Pasted image 20240508211401.png]]
當資料亂序插入時，如果順序不對而且空間不夠的情況下它會開闢一個新的頁後把要插入頁的50%資料擷取出來和要插入的亂序資料插入到新的頁中之後更改頁的位置。

● 頁合併

![[Pasted image 20240508211703.png]]
當刪除一行記錄時,實際上記錄並沒有被物理刪除,只是記錄被標記(flaged)為刪除並且它的空間變得允許被其他記錄宣告使用。
當頁中刪除的記錄達到MERGE_THRESHOLD(預設為頁的50%),InnoDB會開始尋找最靠近的頁(前或後)看看是否可以將兩個頁合併以最佳化空間使用。
![[Pasted image 20240508212924.png]]
![[Pasted image 20240508213049.png]]

知識小貼士:

MERGE_THRESHOLD:合併頁的閾值,可以自己設定,在建立表或者建立索引時指定。

● 主鍵設計原則

1. 滿足業務需求的情況下,儘量降低主鍵的長度。
2. 插入資料時,儘量選擇順序插入,選擇使用AUTO_INCREMENT自增主鍵。
3. 儘量不要使用UUID做主鍵或者是其他自然主鍵,如身份證號。
4. 業務操作時,避免對主鍵的修改。

order by的最佳化

Using filesort:透過表的索引或全表掃描,讀取滿足條件的資料行,然後在排序緩衝區sort buffer中完成排序操作,所有不是透過索引直
接返回排序結果的排序都叫 FileSort排序。
Using index:透過有序索引順序掃描直接返回有序資料,這種情況即為using index,不需要額外排序,操作效率高。

• 做業務需求是時儘量要用using index 它是順序掃描所用的時間比較少但是using filesort是全表掃描他所需要的效能比較高

mysql> explain select id,name,age,phone from user_info order by age;
+----+-------------+-----------+------------+------+---------------+------+------------+------------+------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+------------+------------+------------+
| 1 | SIMPLE | user_info | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+------------+------------+------------+

#根據age,phone進行降序一個升序,一個降序
explain select id,age,phone from user_info order by age asc, phone desc;

mysql> explain select id,age,phone from user_info order by age asc ,phone desc ;
+----+-------------+-----------+------------+-------+---------------+------------------------+---------+------+------------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered| Extra |
+----+-------------+-----------+------------+-------+---------------+------------------------+---------+------+------------+------------------+
| 1 | SIMPLE | user_info | NULL | index | NULL | idx_userinfo_age_phone | 88 | NULL | 24 | 100.00 | Using index; Using filesort |
+----+-------------+-----------+------------+-------+---------------+------------------------+---------+------+------------+------------------+

#建立索引
create index idx_user_age_phone_ad on user_info(age asc ,phone desc);
#根據age,phone進行降序一個升序,一個降序
explain select id,age,phone from user_info order by age asc, phone desc;

+----+-------------+-----------+------------+-------+---------------+--------------------------+--------+-------+------+--------+------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+--------------------------+--------+-------+------+--------+------------+
| 1 | SIMPLE | user_info | NULL | index | NULL | indx_userinfo_age_phne | 88 | NULL | 24 | 100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+--------------------------+--------+
建立降序升序索引會把using filesort最佳化掉。
如果查詢表沒有走覆蓋索引時他還是會出現using filesort,全表掃描排序時它會回表查詢。

總結

• 根據排序欄位建立合適的索引,多欄位排序時,也遵循最左字首法則。
• 儘量使用覆蓋索引。
• 多欄位排序,一個升序一個降序,此時需要注意聯合索引在建立時的規則(ASC/DESC)。
• 如果不可避免的出現filesort,大資料量排序時,可以適當增大排序緩衝區大小sort_buffer_size(預設256k)。

#執行分組操作,根據profession欄位分組

explain select profession, count(*) from user_info group by profession ;

#建立索引

Create index idx_user_pro_age_sta on user_info(profession, age, status);
#執行分組操作,根據profession欄位分組

explain select profession, count(*) from user_info group by profession, age;

+--+-------------+---------+-----------+--------+--------------------------+--------------------------------+--------+-----+------+------+------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--+-------------+---------+--------+-------+--------------------+--------------+-------------------------------+---------+------+--------+------------+
| 1 | SIMPLE | user_info | NULL | index | idx_userinfo_pro_age_sta | idx_userinfo_pro_age_sta | 251 | NULL | 24 | 100.00 | Using index |
+----+-----------+-------+--------+-------+--------------------------+---------+-----+---+----+------+------------+

#執行分組操作,根據profession欄位分組
explain select profession, count(*) from user_infor group by profession;#覆蓋索引中

+----+-------------+-----------+------------+-------+--------------------------+-----------------------+---------+------+------+--------+---------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+--------------------------+-----------------------+---------+------+------+--------+---------------------
| 1 | SIMPLE | user_info | NULL | index | idx_userinfo_pro_age_sta | idx_userinfo_pro_age_sta | 251 | NULL | 24 | 100.00 | Using index; Using temporary|
+----+-------------+-----------+------------+-------+--------------------------+-----------------------+---------+------+------+--------+---------------------

 explain select profession,age, count(*) from user_info  group by profession, age;#滿足最左字首法則所以走的是索引

+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user_info | NULL | index | idx_userinfo_pro_age_sta | idx_userinfo_pro_age_sta | 251 | NULL | 24 | 100.00 | Using index |
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-------------+

count最佳化

explain select cunt(*) from user_info ;

MylSAM引擎把一個表的總行數存在了磁碟上,因此執行count()的時候會直接返回這個數,效率很高;
InnoDB 引擎就麻煩了,它執行count()的時候,需要把資料一行一行地從引擎裡面讀出來,然後累積計數。
最佳化思路：自己設計一個函式去技術，或者是更改引擎（InnoDB)

● count的幾種用法

count()是一個聚合函式,對於返回的結果集,一行行地判斷,如果count函式的引數不是NULL,累計值就加1,否則不加,最後
返回累計值。

用法:

count(*)、count(主鍵)、count(欄位)、count(1)

count(主鍵)

InnoDB引擎會遍歷整張表,把每一行的主鍵id值都取出來,返回給服務層。服務層拿到主鍵後,直接按行進行累加(主鍵不可能為null)。

count(欄位)

沒有not null約束:InnoDB引擎會遍歷整張表把每一行的欄位值都取出來,返回給服務層,服務層判斷是否為null,不為null,計數累加。
有not null 約束:InnoDB引擎會遍歷整張表把每一行的欄位值都取出來,返回給服務層,直接按行進行累加。

count (1)

InnoDB引擎遍歷整張表,但不取值。服務層對於返回的每一行,放一個數字“1”進去,直接按行進行累加。

count (*)

InnoDB引擎並不會把全部欄位取出來,而是專門做了最佳化,不取值,服務層直接按行進行累加。

按照效率排序的話,count(欄位)<count(主鍵id)<count(1)~count(),所以儘量使用count()。

總結

1. 插入資料

insert:批次插入、手動控制事務、主鍵順序插入
大批次插入:load data local infile

2. 主鍵最佳化

主鍵長度儘量短、順序插入 AUTO_INCREMENT

3. order by最佳化

using index:直接透過索引返回資料,效能高
using filesort:需要將返回的結果在排序緩衝區排序

4. group by最佳化

索引,多欄位分組滿足最左字首法則

5.limit最佳化

覆蓋索引+子查詢

6. count最佳化

count(欄位)<count(主鍵id)<count(1)~count(),所以儘量使用count()

7. update最佳化

儘量根據主鍵/索引欄位進行資料更新

檢視

檢視(View)是一種虛擬存在的表。檢視中的資料並不在資料庫中實際存在,行和列資料來自定義檢視的查詢中使用的表,這種表稱之為基表。並且是在使用檢視時動態生成的。
通俗的講,檢視只儲存了查詢的SQL邏輯,不儲存查詢結果。所以我們在建立檢視的時候,主要的工作就落在建立這條SQL查詢語句上。

● 建立

CREATE [OR REPLACE] VIEW 檢視名稱[(列名列表)] AS SELECT語句[WITH[CASCADED | LOCAL] CHECK OPTION]

● 查詢

#檢視建立檢視語句:
SHOW CREATE VIEW 檢視名稱;
#檢視檢視資料:
SELECT* FROM 檢視名稱 ……;

● 修改

#方式一:
CREATE [OR REPLACE] VIEW 檢視名稱[(列名列表)] AS SELECT語句 [WITH[CASCADED | LOCAL] CHECK OPTION]
#方式二:
ALTER VIEW 檢視名稱[(列名列表)] AS SELECT語句 [WITH[CASCADED |LOCAL] CHECK OPTION]

● 刪除

DROP VIEW [IF EXISTS] 檢視名稱 [檢視名稱] ...

●例項

-- 建立  
create or replace view characters_view as select*from characters where id<='12';  
-- 查詢  
show create view characters_view;-- 查詢建立檢視語句  
select*from characters_view; -- 查詢建立檢視資料  
-- 更改  
create or replace view characters_view as select id,name from characters where id<='17';  
alter view characters_view as select id,name,age from characters where id<='10';
-- 刪除
drop view characters_view;

-- casceded 查詢選項
create or replace view user_info_view as select id, name from user_info where id<=25 with cascaded check option; -- with cascaded check option插入不符合查詢條件的資料時就報錯
ex:insert into user_info_view (id,name) values (30,'tom');

檢視cascaded檢查選項

當使用WITH CHECK OPTION子句建立檢視時,MySQL會透過檢視檢查正在更改的每個行,例如 插入,更新,刪除,以使其符合檢視的定義。MySQL允許基於另一個檢視建立檢視,它還會檢查依賴檢視中的規則以保持一致性。為了確定檢查的範圍,mysql提供了兩個選項:CASCADED 和LOCAL,預設值為CASCADED。

create or replace view user_info_v1 as select id, name from user_info where id<=25 with cascaded check option;  
insert into user_info_v1 (id,name) values (30,'tom');  
create or replace view user_info_v2 as select id, name from user_info_v1 where id>=15 with local check option;  
insert into user_info_v2 (id,name) values (18,'yom');  
insert into user_info_v2 (id,name) values (10,'zom');  
insert into user_info_v2 (id,name) values (20,'xom');  
insert into user_info_v2 (id,name) values (30,'com');  
insert into user_info_v2 (id,name) values (40,'vom');   
create or replace view user_info_v3 as select id, name from user_info_v2 where id<=15 ;  
insert into user_info_v3 (id,name) values (7,'yom');  
insert into user_info_v3 (id,name) values (10,'zom'); -- 不能執行因為它不僅要滿足user_info_v3還要滿足user_info_v2更要滿足user_info_v1的查詢條件
insert into user_info_v3 (id,name) values (20,'xom');  
insert into user_info_v3 (id,name) values (30,'com');  
insert into user_info_v3 (id,name) values (40,'vom');

● 檢視的更新

要使檢視可更新,檢視中的行與基礎表中的行之間必須存在一對一的關係。如果檢視包含以下任何一項,則該檢視不可更新:

1. 聚合函式或視窗函式(SUM()、MIN()、MAX()、COUNT()等
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION 或者 UNION ALL

● 作用

簡單

檢視不僅可以簡化使用者對資料的理解,也可以簡化他們的操作。那些被經常使用的查詢可以被定義為檢視,從而使得使用者不必為以後的操作每次指定全部的條件。

安全

資料庫可以授權,但不能授權到資料庫特定行和特定的列上。透過檢視使用者只能查詢和修改他們所能見到的資料。

資料獨立

檢視可幫助使用者遮蔽真實表結構變化帶來的影響。

案例

-- 為了保證資料庫表的安全性,開發人員在操作tb_user表時,只能看到的使用者的基本欄位,遮蔽手機號和郵箱兩個欄位。

create or replace view user_info_v as select id,name,profession,age,gender,status,createtime from user_info;  

-- 查詢每個學生所選修的課程(三張表聯查),這個功能在很多的業務中都有使用到,為了簡化操作,定義一個檢視。

select s.name,s.id,c.name from student s ,student_course sc,course c where s.id=sc.studentid and sc.courseid=c.id;  
create or replace view student_course_v as select s.id student_id, s.name student_name,c.name course_name from student s ,student_course sc,course c where s.id=sc.studentid and sc.courseid=c.id;

儲存過程

● 介紹

儲存過程是事先經過編譯並儲存在資料庫中的一段SQL語句的集合,呼叫儲存過程可以簡化應用開發人員的很多工作,減少資料在資料庫和應用伺服器之間的傳輸,對於提高資料處理的效率是有好處的。儲存過程思想上很簡單,就是資料庫SQL語言層面的程式碼封裝與重用。

● 特點

封裝,複用
可以接收引數,也可以返回資料
減少網路互動,效率提升

語法

#● 建立
CREATE PROCEDURE 儲存過程名稱([引數列表])
BEGIN
		-- SQL語句
END ;
#● 呼叫
CALL 名稱([引數]);
#● 檢視

SELECT*FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='xxx' ;-- 查詢指定資料庫的儲存過程及狀態資訊

SHOW CREATE PROCEDURE 儲存過程名稱 ;-- 查詢某個儲存過程的定義

例項

create procedure user_info_p()  
begin  
    select*from user_info;  
end $$  
  
  
-- 呼叫  
call user_info_p();  
-- 檢視  
select*from information_schema.ROUTINES where ROUTINE_SCHEMA= 'xukurs';  
show create procedure user_info_p;  
-- 刪除

注意:在命令列中,執行建立儲存過程的SQL時,需要透過關鍵字delimiter指定SQL語句的結束符。

● 變數

系統變數 是MySQL伺服器提供,不是使用者定義的,屬於伺服器層面。分為全域性變數(GLOBAL)、會話變數(SESSION)。

系統變數

檢視

SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 檢視所有系統變數
SHOW [SESSION|GLOBAL] VARIABLES LIKE'.....'; -- 可以透過LIKE模糊匹配方式查詢變數
SELECT @@[SESSION|GLOBAL]系統變數名;-- 檢視指定變數的值

設定系統變數

SET[SESSION|GLOBAL] 系統變數名=值;
SET @@[SESSION|GLOBAL]系統變數名=值;

注意：如果沒有指定SESSION/GLOBAL，預設是SESSION，會話變數。所設定的全域性引數會失效要想不失效，mysql服務重新啟動之後，可以在 /etc/my.cnf 中配置.

自定義變數

使用者定義變數 是使用者根據需要自己定義的變數,使用者變數不用提前宣告,在用的時候直接用“@變數名”使用就可以。其作用域為當前連線。

賦值

#set賦值
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
#select賦值
SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 欄位名 INTO @var_name FROM 表名;

使用

SELECT @var_name ;

注意: 使用者定義的變數無需對其進行宣告或初始化,只不過獲取到的值為NULL。

區域性變數

區域性變數 是根據需要定義的在區域性生效的變數,訪問之前,需要DECLARE宣告。可用作儲存過程內的區域性變數和輸入引數,區域性變數的範圍是在其內宣告的BEGIN.END塊。

宣告

DECLARE 變數名 變數型別[DEFAULT ... ];

變數型別就是資料庫欄位型別:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

賦值

SET 變數名=值;
SET 變數名 := 值;
SELECT 欄位名 INTO 變數名 FROM 表名 …..;

create procedure p2()  
    begin  
declare stu_count int default 0;  
set stu_count :=100;  
select count(*) into stu_count from student;  
select stu_count;  
    end ;  
  
call p2();

儲存過程語法之if

語法:

IF 條件1 THEN
......
ELSEIF 條件2 THEN         --可選

......

ELSE                     --可選
......
END IF;
......

delimiter $$  
create procedure p3()  
begin  
declare score_level int default 58;  
declare result varchar(10);  
if score_level >=85 then  
    set result:='優秀';  
    elseif score_level >=60 then  
    set result:='及格';  
    elseif score_level<=60 then  
    set result:='不及格';  
END if ;  
select result;  
end $$  
delimiter ;  
call p3();

引數

![[Pasted image 20240520215321.png]]

用法:

CREATE PROCEDURE 儲存過程名稱([IN/OUT/INOUT 引數名 引數型別])

BEGIN

-- SQL語句

END ;

例項

score>=85分,等級為優秀。
score >= 60分 且 score<85分,等級為及格。
score< 60分,等級為不及格。

delimiter $$  
create procedure p4(in score_level int,out result varchar(10))  
begin  
  
if score_level >=85 then  
    set result:='優秀';  
    elseif score_level >=60 then  
    set result:='及格';  
    elseif score_level<=60 then  
    set result:='不及格';  
END if ;  
  
end $$  
delimiter ;  
call p4(18,@result);  
select @result;

case

語法一

CASE case_value
WHEN when_valuel THEN statement list1
[ WHEN when_value2 THEN statement_list 2] ...
[ ELSE statement_list ]
END CASE;

語法二

CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2] ...
[ELSE statement_list]
END CASE;

例項

根據傳入的月份,判定月份所屬的季節(要求採用case結構)。

1. 1-3月份,為第一季度
2. 4-6月份,為第二季度
3. 7-9月份,為第三季度
4. 10-12月份,為第四季度

delimiter $$  
create procedure p6(in month int ,out season varchar(10))  
begin  
  case 
    when month>=3 and month<=5 then set season:='春季';  
    when month>=6 and month<=8 then set season:='夏季';  
    when month>=9 and month<=11 then set season:='秋季';  
    when month in(12,1,2)  then set season:='冬季';  
    else set season:='非法引數';  
    end case;  
    select concat('您輸入的月份為:',month,',所屬的季度為:',season);  
end $$  
 call p6(11,@season);  
 select @season;

while

while迴圈是有條件的迴圈控制語句。滿足條件後,再執行迴圈體中的SQL語句。具體語法為:
先判定條件,如果條件為true,則執行邏輯,否則,不執行邏輯

WHILE 條件 DO

SQL邏輯 ….

END WHILE;

例項

計算從1累加到n的值,n為傳入的引數值。

create  
    definer = root@localhost procedure p7(IN n int)  
begin  
    declare total int default 0;  
    while n>=1 do  
      set total:= total+n;  
      set n:=n-1;  
        end while;  
    select total;  
end;

repeat

repeat是有條件的迴圈控制語句,當滿足條件的時候退出迴圈。具體語法為:
先執行一次邏輯,然後判定邏輯是否滿足,如果滿足,則退出。如果不滿足,則繼續下一次迴圈

REPEAT

SQL邏輯 …..

UNTIL 條件

END REPEAT;

例項

計算從1累加到n的值,n為傳入的引數值。

create  
    definer = root@localhost procedure p8(IN n int)  
begin  
    declare total int default 0;  
    repeat  
        set total:= total+n;  
      set n:=n-1;  
    until  n<1  
       end repeat ;  
    select total;  
end;

loop

LOOP實現簡單的迴圈,如果不在SQL邏輯中增加退出迴圈的條件,可以用其來實現簡單的死迴圈。LOOP可以配合一下兩個語句使用:
· LEAVE:配合迴圈使用,退出迴圈。
· ITERATE:必須用在迴圈中,作用是跳過當前迴圈剩下的語句,直接進入下一次迴圈。

[begin_label:] LOOP
SQL邏輯 …..
END LOOP [end_label];


LEAVE label ;-- 退出指定標記的迴圈體
ITERATE label; -- 直接進入下一次迴圈

例項

計算從1累加到n的值,n為傳入的引數值。

create  
    definer = root@localhost procedure p9(IN n int)  
begin  
    declare summarize int default 0;  
    sum:loop  
        if n<1 then leave sum;  
        end if;  
    set summarize:= summarize+n;  
    set n:=n-1;  
end loop sum;  
    select summarize;  
end;

-- Loop計算從1到n之間的偶數累加的值,n為傳入的引數值。

create  
    definer = root@localhost procedure p10(IN n int)  
begin  
    declare total int default 0;  
    sum:loop  
        if n<1 then leave  sum;  
        end if;  
        if n%2 =1 then  
            set n:=n-1;  
        ITERATE sum;  
         end if;  
        set total:= total+n;  
      set n:=n-1;  
end loop sum;  
    select total;  
end;

遊標

遊標(CURSOR)是用來儲存查詢結果集的資料型別,在儲存過程和函式中可以使用遊標對結果集進行迴圈的處理。遊標的使用包括遊標的宣告、OPEN、FETCH和CLOSE,其語法分別如下。

宣告遊標

DECLARE 遊標名稱 CURSOR FOR 查詢語句;

開啟遊標

OPEN 遊標名稱;

獲取遊標記錄

FETCH 遊標名稱 INTO 變數[變數];

關閉遊標

CLOSE 遊標名稱;

根據傳入的引數uage,來查詢使用者表tb_user中,所有的使用者年齡小於等於uage的使用者姓名(name)和專業(profession). 並將使用者的姓名和專業插入到所建立的一張新表(id,name,profession)中。
當出現一個需求之時我們要清楚需求的底層邏輯，更要會拆解需求並找尋方法去解決需求中的問題。
-- 邏輯:
-- A. 宣告遊標,儲存查詢結果集
-- B. 準備:建立表結構
-- C. 開啟遊標
-- D. 獲取遊標中的記錄
-- E. 插入資料到新表中

delimiter $$  
create procedure p12(in uage int)  
begin  
declare uname varchar(100); -- 宣告普通變數和遊標必須先宣告普通變數再宣告遊標。  
declare uprofession varchar(100);  
declare u_age cursor for select name ,profession from user_info where age<=uage; 
drop table if exists you_age;必須要有這句語句不然它會顯示 table is exists錯誤
 create table you_age  
 (  
id int primary key auto_increment,  
name varchar(10),  
profession varchar(10)  
 );  
 open u_age ;  
 while true  
 do    fetch u_age into uname,uprofession;  
    insert into you_age values(null,uname,uprofession);  
end while ;  
  
 close u_age;  
end $$  
call p12(40);
它會在後面顯示 [02000][1329] No data - zero rows fetched, selected, or processed
錯誤 我們要用條件處理語句這件問題

條件處理程式

條件處理程式(Handler)可以用來定義在流程控制結構執行過程中遇到問題時相應的處理步驟。具體語法為:

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;

handler_action
CONTINUE:繼續執行當前程式
EXIT:終止執行當前程式

condition_value
SQLSTATE sqlstate_value:狀態碼,如02000
SQLWARNING:所有以01開頭的SQLSTATE程式碼的簡寫
NOT FOUND:所有以02開頭的SQLSTATE程式碼的簡寫
SQLEXCEPTION:所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE程式碼的簡寫

create  
    definer = root@localhost procedure p12(IN uage int)  
begin  
declare uname varchar(100); -- 宣告普通變數和遊標必須先宣告普通變數再宣告遊標。  
declare uprofession varchar(100);  
declare u_age cursor for select name ,profession from user_info where age<=uage;  
declare exit handler for sqlstate '02000' close u_age;  
drop table if exists you_age;  
 create table you_age  
 (  
id int primary key auto_increment,  
name varchar(10),  
profession varchar(10)  
 );  
 open u_age ;  
 while true  
 do    fetch u_age into uname,uprofession;  
    insert into you_age values(null,uname,uprofession);  
end while ;  
 close u_age;  
end;

儲存函式

儲存函式是有返回值的儲存過程,儲存函式的引數只能是IN型別的。具體語法如下:

CREATE FUNCTION 儲存函式名稱([引數列表])
RETURNS type [characteristic ... ]
BEGIN
-- SQL語句
RETURN ...;
END; 

characteristic說明:
· DETERMINISTIC:相同的輸入引數總是產生相同的結果
· NO SQL:不包含SQL語句。
· READS SQL DATA: 包含讀取資料的語句,但不包含寫入資料的語句。

delimiter $$  
create function num_adder(n int)  
returns int deterministic  
begin  
   declare total int default 0;  
    while n>0 do  
           set total:= total+n;  
      set n:=n-1;  
   end while;  
return total;  
end $$    
select num_adder(1000);
characteristic如果不宣告在mysql 8.0版本中會顯示
[HY000)[1418] This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enablec (you "might" want to use the less safe log_bin_trust_function_creators variable;
這種錯誤讓我們去宣告charactistic 

觸發器

觸發器是與表有關的資料庫物件,指在insert/update/delete之前或之後,觸發並執行觸發器中定義的SQL語句集合。觸發器的這種特性可以協助應用在資料庫端確保資料的完整性,日誌記錄,資料校驗等操作。使用別名OLD和NEW來引用觸發器中發生變化的記錄內容,這與其他的資料庫是相似的。現在觸發器還只支援行級觸發,不支援語句級觸發。

![[Pasted image 20240527111525.png]]

● 語法
建立
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行級觸發器
BEGIN
trigger_stmt;
END;

▶ 檢視
SHOW TRIGGERS ;

▶ 刪除
DROP TRIGGER [schema_name.]trigger_name ; -- 如果沒有指定schema_name,預設為當前資料庫。

插入觸發器例項

  
-- 需求:透過觸發器記錄 user 表的資料變更日誌(user_logs),包含增加,修改,刪除;  
-- 準備工作:日誌表 user_logs  
create table user_logs(  
id int(11)  primary key not null auto_increment,  
operation varchar(20) not null comment'操作型別, insert/update/delete',  
operate_time datetime not null comment'操作時間',  
operate_id int(11) not null comment'操作的ID',  
operate_params varchar(500)comment'操作引數'  
)engine=innodb default charset=utf8;  
  
-- 插入資料觸發器  
delimiter $$  
create trigger user_insert  
after insert  
    on user_info for each row  
    begininsert into user_logs(id, operation, operate_time, operate_id, operate_params)  
    VALUES(null,'insert',now(),new.id, concat('插入內容:' ,'id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession));  
    end $$
show triggers ;  
drop trigger user_insert_trigger;

修改觸發器例項

  
 
delimiter $$  
create trigger user_update  
after update  
    on user_info for each row  
    begininsert into user_logs(id, operation, operate_time, operate_id, operate_params)  
    VALUES(null,'update',now(),new.id,  
           concat('更新之前的資料:' ,'id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession  
           ,'|更新之後的資料:' ,'id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession));  
    end $$  
  
    show triggers ;  
update user_info set age = 32 where id = 23;  
update user_info set age = 26 where id = 26;

-- 刪除觸發器  
delimiter $$  
create trigger user_delete  
after delete  
    on user_info for each row  
    begin        insert into user_logs(id, operation, operate_time, operate_id, operate_params)  
    VALUES(null,'delete',now(),old.id,  
           concat('更新之前的資料:' ,'id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession));  
    end $$  
  
    show triggers ;  
  
  
delete from user_info where id = 25;

鎖

鎖是計算機協調多個程序或執行緒併發訪問某一資源的機制。在資料庫中,除傳統的計算資源(CPU、RAM、I/O)的爭用以外,資料也是一種供許多使用者共享的資源。如何保證資料併發訪問的一致性、有效性是所有資料庫必須解決的一個問題,鎖衝突也是影響資料庫併發訪問效能的一個重要因素。從這個角度來說,鎖對資料庫而言顯得尤其重要,也更加複雜。

全域性鎖

全域性鎖就是對整個資料庫例項加鎖,加鎖後整個例項就處於只讀狀態,後續的DML的寫語句,DDL語句,已經更新操作的事務提交語句都將被阻塞。其典型的使用場景是做全庫的邏輯備份,對所有的表進行鎖定,從而獲取一致性檢視,保證資料的完性。
![[Pasted image 20240603215029.png]]
在資料庫正在更新時如果不進行鎖操作備份的話它會把表中的資料表中已插入的資料備份下來但是正在插入的資料漏備份從而導致資料的不完整。

語法

flush tables with read lock ; -- 應用全域性鎖
mysqldump -uroot -p1234 itcast > itcast.sql; -- 使用備份語句
unlock tables ; -- 釋放全域性鎖