Mysql(繼續更新)

INnoDB? ?三特性? ?事務? ?外鍵? ? 行級鎖(開啟事務時,查詢后加FOR UPDATE)

MySQL 使用 InnoDB,在 默認隔離級別 —— REPEATABLE READ(可重復讀) 下? 開啟事務,執行 UPDATE 時默認會加行鎖? 只要事務沒有提交? 這條數據會鎖住

Mysql概述

數據庫(DataBase),簡稱:DB.存儲數據的倉庫,數據是有組織的進行存儲

數據庫管理系統(DataBase Management System),簡稱:DBMS.操作和管理數據庫的大型軟件

SQL(Structured Query Language):操作關系型數據庫的編程語言,定義了一套操作關系型數據庫統一標準

Windows中Mysql的啟動命令

net start mysql80

net stop mysql80

Mysql客戶端連接

mysql [-h 127.0.0.1] [-P 3306] -u root -p

系統管理類的 SQL 命令 元數據查詢

查詢所有數據庫:SHOW DATABASES;

查詢當前使用的數據庫: SELECT DATABSES();

  • SHOW DATABASES;:查看所有數據庫

  • SHOW TABLES;:查看當前數據庫中的所有表

  • SHOW COLUMNS FROM 表名;:查看表結構

  • SHOW INDEX FROM 表名;:查看索引

  • SHOW GRANTS FOR 用戶;:查看權限

查詢當前數據庫所有表

SHOW TABLES;

查詢表結構

DESC 表名;

查詢指定表的建表語句

SHOW CREATE TABLE?表名

SQL分類

DDL(Data Definition Language):數據定義語言,用來定義數據庫對象(數據庫,表,字段)

常用關鍵字

  • CREATE:創建數據庫、表、視圖等

  • DROP:刪除數據庫、表、視圖等

  • ALTER:修改表結構(增加/刪除列、修改列類型等)

  • TRUNCATE:清空表數據(比 DELETE 快,不可回滾)

  • RENAME:重命名數據庫對象

創建數據庫:

CREATE DATABASES [ IF NOT EXISTS] 數據庫名 [DEFAULT CHARSET 字符集] [ COLLATE 排序規則];

刪除數據庫

DORP DATABASE [IF EXISTS] 數據庫名;

使用數據庫

USE 數據庫名

例如

create database itcast;

創建表

CREATE TABLE [IF NOT EXISTS] 表名(

? ? ? ? 字段1 字段1類型 [COMMENT 字段1注釋],

????????字段2 字段2類型 [COMMENT 字段2注釋]

)[COMMENT 表注釋]

例如:

CREATE TABLE tb_user(id int comment '編號',name varchar(50) comment '姓名',age int comment '年齡',gender varchar(1) comment '性別') comment '用戶表';

查看表結構:DESC tb_user;

查看指定表的建表語句:SHOW CREATE TABLE tb_user;

Mysql的數據類型
1.數值類型

有符號:指出現負數的范圍? ? 無符號:指沒有負數的范圍

DECIMAL(10, 2) 第一個參數10代表精度? ?第二個參數代表標度

精度表示最多可存儲 10 位數字

標度表示其中 2 位是小數位

例如:12345678.11

默認情況下在Navicat中即使不顯示指定精度和標度

針對于Decimal類型需要顯示指定精度和標度? 不然無法存小數

Doubel即使0,0也可以存小數? 應該是Navicat的顯示誤區

例如你希望用double表示分數? 可以用double(4,1) 最多4位? 小數點后保留1位

默認都是有符號的

指定無符號示例

CREATE TABLE users (
? ? id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
? ? age TINYINT UNSIGNED,
? ? score INT,
? ? balance DECIMAL(10,2) UNSIGNED
);
?

2.字符串類型

3.日期時間類型

練習

CREATE TABLE emp (
? ? id INT COMMENT '編號',
? ? workno VARCHAR(10) COMMENT '工號',
? ? name VARCHAR(10) COMMENT '姓名',
? ? gender CHAR(1) COMMENT '性別',
? ? age TINYINT UNSIGNED COMMENT '年齡',
? ? idcard CHAR(18) COMMENT '身份證號',
? ? entrydate DATE COMMENT '入職時間'
) COMMENT='員工表';
?

DDL-表操作-修改和刪除
添加字段

ALTER TABLE 表名 ADD 字段名 類型 [UNSIGNED] [NOT NULL] [DEFAULT 值] [COMMENT '說明'];

修改數據類型

ALTER TABLE 表名 MODIFY 字段名 新數據類型(長度) [UNSIGNED] [NOT NULL | NULL] [DEFAULT 值] [COMMENT '注釋'];

修改字段名和字段類型? 注意用CHANGE的話必須寫“舊字段名”和“新字段名”,哪怕名字不變!

ALTER TABLE 表名?
CHANGE 舊字段名 新字段名 新數據類型(長度) [UNSIGNED] [NOT NULL | NULL] [DEFAULT 值] [COMMENT '注釋'];

刪除字段

ALTER TBALE 表名 DROP 字段名;

修改表名

ALTER TABLE 表名 RENAME 新表名;

刪除表

刪除整張表(連結構都沒了)

DROP TABLE [IF EXISTS] 表名;

清空表數據,但保留表結構??重置表的自增計數器(如果有)不是逐行刪除(像?DELETE?那樣)

對于大表,TRUNCATE?比?DELETE FROM 表名?快得多 DELETE是DML操作

TRUNCATE TABLE 表名;

DDL-數據庫操作總結

SHOW DATABASES; ? ? ? ? ? ? ? ? ? ? ? ? ? ?-- 查看所有數據庫
CREATE DATABASE 數據庫名; ? ? ? ? ? ? ? ? ?-- 創建數據庫
USE 數據庫名; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?-- 切換當前數據庫
SELECT DATABASE(); ? ? ? ? ? ? ? ? ? ? ? ? -- 查看當前所用數據庫
DROP DATABASE [IF EXISTS] 數據庫名; ? ? ? ?-- 刪除數據庫(推薦加 IF EXISTS)

DDL-表操作總結

SHOW TABLES; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? -- 查看當前數據庫中所有表
CREATE TABLE 表名 (
? ? 字段名 數據類型 [約束] [COMMENT '注釋'],
? ? ...
); ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?-- 創建表

DESC 表名; ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?-- 查看表結構(字段+類型+約束)
SHOW CREATE TABLE 表名; ? ? ? ? ? ? ? ? ? ? -- 查看完整建表語句

-- 表結構變更(操作不能混用在一條 ALTER 中):
ALTER TABLE 表名 ADD 列名 類型(長度) ... ; ? ? ? ? -- 添加字段
ALTER TABLE 表名 MODIFY 列名 新類型(長度) ... ; ? ? -- 修改字段類型
ALTER TABLE 表名 CHANGE 舊名 新名 類型(長度) ... ; ?-- 改字段名+類型
ALTER TABLE 表名 DROP COLUMN 列名; ? ? ? ? ? ? ? ?-- 刪除字段
ALTER TABLE 表名 RENAME TO 新表名; ? ? ? ? ? ? ? ? -- 修改表名

DROP TABLE [IF EXISTS] 表名; ? ? ? ? ? ? ? ? ? ? ?-- 刪除表
?

DML(Data Manipulation Language):數據操作語言,用來對數據庫表中的數據進行增刪改

常用關鍵字

  • INSERT:插入數據

  • UPDATE:更新數據

  • DELETE:刪除數據

添加數據INSERT

全量字段插入

INSERT INTO T_USER VALUES(1,"張三",18);

批量插入

INSERT INTO t_user VALUES(1,"張三",18),(2,"李四",19);

指定字段插入

INSERT INTO t_user(name,age)VALUES("王五",17),("趙六",19);

一些特殊用法

整張表數據復制

INSERT INTO 表B
SELECT * FROM 表A;
?

指定字段復制

INSERT INTO 表B(字段1, 字段2, ...)
SELECT 字段A1, 字段A2, ...
FROM 表A;

復制時加條件

INSERT INTO 表B
SELECT * FROM 表A
WHERE 條件;

跨數據庫復制

INSERT INTO 新庫.表B
SELECT * FROM 舊庫.表A;
?

備份原表數據

CREATE TABLE emp_bak LIKE emp;
INSERT INTO emp_bak SELECT * FROM emp;

注意:插入數據時,指定字段順序需要與值得順序對應

字符串和日期型數據應該包含在引號中

插入的數據大小應該在字段的規定范圍內
?

修改數據UPDATE

UPDATE 表名 SET 字段名1=值,字段名2=值2,...[WHERE 條件];

如果沒有條件則修改整張表數據

刪除數據DELETE

DELETE FROM 表名 [WHERE 條件];

DQL(Data Query Language):數據查詢語言,用來查詢數據庫中表的記錄

常用關鍵字

  • SELECT:查詢數據

  • FROM:指定數據來源的表

  • WHERE:設置查詢條件

  • GROUP BY:分組

  • HAVING:過濾分組后的結果

  • ORDER BY:排序

  • JOIN:多表連接查詢

SELECT

? ? ? ? 字段列表

FROM?

? ? ? ? 表名列表

WHERE

? ? ? ? 條件列表

GROUP BY

? ? ? ? 分組字段列表

HAVING

? ? ? ? 分組后條件列表

ORDER BY?

? ? ? ? 排序字段列表

LIMIT

? ? ? ? 分頁參數

執行順序

from ---?join ---?on --- where --- group by --- having --- select --- distinct --- order by ---- limit

CREATE TABLE emp (
? ? id INT PRIMARY KEY AUTO_INCREMENT COMMENT '編號',
? ? workno VARCHAR(10) COMMENT '工號',
? ? name VARCHAR(10) COMMENT '姓名',
? ? gender CHAR(1) COMMENT '性別',
? ? age TINYINT UNSIGNED COMMENT '年齡',
? ? idcard CHAR(18) COMMENT '身份證號',
? ? workaddress VARCHAR(50) COMMENT '工作地址',
? ? entrydate DATE COMMENT '入職時間'
) COMMENT='員工表';

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
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, '123456789012345678', '上海', '2007-07-01'),
(6, '6', '楊逍', '男', 28, '12345678931234567X', '北京', '2006-01-01'),
(7, '7', '范遙', '男', 40, '123456789212345670', '北京', '2005-05-01'),
(8, '8', '黛綺絲', '女', 38, '123456157123465670', '天津', '2015-05-01'),
(9, '9', '范涼涼', '女', 45, '123156789012345678', '北京', '2010-04-01'),
(10, '10', '陳友諒', '男', 53, '123456789012345670', '上海', '2011-01-01'),
(11, '11', '張士誠', '男', 55, '12356789712345670', '江蘇', '2015-05-01'),
(12, '12', '常遇春', '男', 32, '12346475712345670', '北京', '2004-02-01'),
(13, '13', '張三豐', '男', 88, '12365678712345678', '江蘇', '2020-11-01'),
(14, '14', '滅絕', '女', 65, '123456791297123456', '西安', '2019-05-01'),
(15, '15', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01'),
(16, '16', '周芷若', '女', NULL, '123456789012345670', '北京', '2012-06-01');
基本查詢

SELECT 字段1,字段2,字段3,...FROM 表名;

SELECT * FROM 表名;

設置別名

SELECT 字段1 [AS 別名1],字段2 [AS 別名2],.... FROM 表名;

去除重復記錄

SELECT DISTINCT 字段列表 FROM 表名;

基本查詢練習

1.查詢指定字段 name,workno,age

SELECT name,workno,age FROM emp;

2.查詢所有字段

SELECT * FROM emp; #最好列出所有字段 不要用*

3.查詢所有員工的工作地址,起別名

SELECT workaddress AS address FROM emp;

4.查詢公司員工的上班地址(不要重復)

SELECT DISTINCT workaddress AS address FROM emp;

條件查詢(WHERE)

SELECT 字段列表 FROM 表名 WHERE 條件列表;

條件:

條件查詢練習

查詢年齡等于88的員工

SELECT * FROM emp where age=88;

查詢年齡小于20的員工信息

SELECT * FROM emp WHERE age<20;

查詢年齡小于等于20的員工

SELECT * FROM emp WHERE age<=20;

查詢沒有身份證號的員工信息

SELECT * FROM emp WHERE idcard IS NULL;?

查詢有身份證號的員工信息

SELECT * FROM emp WHERE idcard IS NOT NULL;?

查詢年齡不等于88的員工信息

SELECT * FROM emp WHERE age!=88;

SELECT * FROM emp WHERE age<>88;

查詢年齡在15歲(包含)到20歲(包含的)員工信息;

SELECT * FROM emp
WHERE age >= 15 AND age <= 20;

SELECT * FROM emp
WHERE age >= 15 &&age <= 20;

SELECT * FROM emp
WHERE age BETWEEN 15 AND 20;等價于age >= 15 AND age <= 20

注意:使用BETWEEN 參數1 AND 參數2? 時候 參數1<=參數2

查詢性別為女且年齡小于25歲員工信息

SELECT * FROM emp WHERE gender='女' AND age<25;

SELECT * FROM emp WHERE gender='女' && age<25;

查詢年齡等于18或20或40的員工信息

SELECT * FROM emp WHERE age = 18 OR age=20 OR age=40;

SELECT * FROM emp WHERE age IN(18,20,40);

查詢姓名為2個字的員工信息

SELECT * FROM emp WHERE name like '__'; #兩個下劃線? ?一個_代表一個字符

SELECT * FROM emp
WHERE CHAR_LENGTH(name) = 2;

查詢身份證最后一位是X的員工信息

SELECT * FROM emp
WHERE idcard LIKE '%X';

SELECT * FROM emp
WHERE idcard LIKE '_________________X'; #前面寫17個下劃線也行

聚合函數(COUNT MAX MIN AVG SUM)

將一列數據做為一個整體,進行縱向計算

常見聚合函數

COUNT? ?統計數量

MAX? ? ? ? 最大值

MIN? ? ? ? ? 最小值

AVG? ? ? ? ?平均值

SUM? ? ? ? 求和

語法:

SELECT 聚合函數(字段列表) FROM 表名;

SELECT COUNT(*) FROM emp;?

SELECT COUNT(1) FROM emp; #效率上和COUNT(*)幾乎等價

SELECT COUNT(age) FROM emp; #會自動排除age為null的行

注意:如果指定列名,為null的列不參與統計,會自動排除NULL的列

聚合函數練習

統計該企業員工數量

SELECT COUNT(*) FROM emp;

統計該企業員工平均年齡

SELECT AVG(age) FROM emp;

統計該企業員工最大年齡

SELECT MAX(age) FROM emp;

統計該企業員工的最小年齡

SELECT MIN(age) FROM emp;

統計西安地區員工年齡之和

SELECT * FROM emp WHERE workaddress='西安';

分組查詢(GROUP BY)

語法:

SELECT 字段列表 FROM 表名 [WHERE 條件] GROUP BY 分組字段名 [HAVING 分組后過濾條件];

WHERE與HAVING的區別

執行時機不同:WHERE執行在GROUP BY之前,不滿足WHERE條件的不參與GROUP BY,HAVING是GROUP BY之后對結果進行過濾

判斷條件不同:WHERE不能對聚合函數進行判斷,而HAVING可以

分組查詢練習

根據性別分組,統計男性員工和女性員工數量

SELECT gender, COUNT(*) AS total
FROM emp
GROUP BY gender;? #分組字段不寫入查詢? 查詢變得沒有意義

根據性別分組,統計男性員工和女性員工的平均年齡

SELECT gender, AVG(age) AS total
FROM emp
GROUP BY gender;

查詢年齡小于45的員工,并根據工作地址分組,獲取員工數量大于等于3的工作地址

SELECT workaddress,COUNT(*) total FROM emp where age<45 GROUP BY workaddress HAVING total>3;

SELECT workaddress,COUNT(*) total FROM emp where age<45 GROUP BY workaddress HAVING COUNT(*)>3; #select 之后起了別名? 除了ORDER BY之后 HAVING之后也是可以的

注意:

執行順序? WHERE --- GROUP BY ---?聚合函數 ---HAVING

分組之后,查詢的一般為聚合函數和分組字段,查詢其他字段無任何意義,一般來說如果用了分組查詢,分組的字段不寫入查詢也會變得無意義(因為不知道這是個什么) 除非就是寫死,只有自己知道這是個啥

1. FROM
2. JOIN
3. ON
4. WHERE
5. **GROUP BY** ? ← 把數據分組
6. **聚合函數計算**(如 COUNT、SUM)
7. HAVING
8. SELECT
9. ORDER BY
10. LIMIT

排序查詢(ORDER BY)

語法:

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

ASC 代表升序? 默認值

DESC 降序

如果多字段排序,當第一個字段值相同時,才會根據第二個字段進行排序

排序查詢練習

根據年齡對公司的員工進行升序排序

SELECT * FROM emp ORDER BY age; #默認ASC 可以不寫

根據入職時間,對員工進行降序排序

SELECT * FROM emp ORDER BY?entrydate DESC;

根據年齡對公司員工進行升序排序,年齡相同按入職時間進行降序排序

SELECT * FROM emp ORDER BY age,entrydate DESC;

分頁查詢(LIMIT)

語法:

SELECT 字段列表 FROM emp LIMIT 起始索引,查詢記錄數;

如果將pageNum當頁數? ?pageSize當每頁顯示條數

注意:該公式不能直接當SQL寫入? 需要計算好(pageNum-1)*pageSize,pageSize

SELECT 字段列表 FROM emp LIMIT (pageNum-1)*pageSize,pageSize;

注意:分頁查詢,不同數據庫有不同實現,Mysql中是LIMIT

DQL練習

查詢年齡為20,21,22,23歲的員工信息

SELECT * FROM emp WHERE age=20 OR age=21 OR age=22 OR age=23;

SELECT * FROM emp WHERE age BETWEEN 20 AND 23;

SELECT * FROM emp WHERE age IN(20,21,22,23);

查詢性別為男,并且年齡在20-40歲(都包含)以內的姓名為三個字的員工信息

SELECT * FROM emp WHERE gender='男' AND age BETWEEN 20 AND 40 AND name LIKE '___';

統計員工表中,年齡小于60歲的,男性員工共和女性員工的人數

SELECT gender,COUNT(*) FROM emp WHERE age<60 GROUP BY gender;

查詢所有年齡小于35歲員工的姓名,年齡,并對查詢結果按年齡升序排序,如果年齡相同按入職時間降序排序

SELECT name, age?
FROM emp?
WHERE age <= 35?
ORDER BY age, entrydate DESC;

查詢性別為男,且年齡在20-40歲(包含)以內的前5個員工信息,對查詢的結果按年齡升序排序,年齡相同按入職時間升序排序

SELECT * FROM emp WHERE gender='男' AND age BETWEEN 20 AND 40 ORDER BY age,entrydate LIMIT 5;

DCL(Data Control Language):數據控制語言,用來創建數據庫用戶,控制數據庫的訪問權限

常用關鍵字

  • GRANT:授予權限

  • REVOKE:撤銷權限

  • DENY:拒絕權限(部分數據庫支持,如 SQL Server)

DCL管理用戶

1.查詢用戶

USE mysql;? ?

SELECT * FROM user;? ?#這個user是表名

2.創建用戶

CREATE USER '用戶名'@'主機名' IDENTIFIED BY '密碼';? ?#這里的USER是關鍵字

3修改用戶密碼

ALTER USER?'用戶名'@'主機名' IDENTIFIED WITH mysql_native_password BY '新密碼';

4.刪除用戶

DROP USER?'用戶名'@'主機名';

創建用戶root1 可以從任何地址來連接數據庫? ?密碼是abc123456

CREATE USER 'root2'@'%' IDENTIFIED BY 'abc123456';

修改用戶登錄密碼

ALTER USER 'root1'@'%' IDENTIFIED WITH mysql_native_password BY 'abc1234561';

刪除用戶root1

DROP USER?'root1'@'%';

TCL管理練習

?創建用戶'hrui',只能夠在當前主機localhost訪問,密碼'123456'

#該用戶創建之后沒有權限 只能看到information_schema一張表

CREATE USER 'hrui'@'localhost' IDENTIFIED BY '123456';

修改用戶登錄密碼

ALTER USER 'root1'@'%' IDENTIFIED WITH mysql_native_password BY 'abc1234561';

刪除用戶root1

DROP USER?'root1'@'%';

權限設置? 默認創建用戶后? 該用戶登錄之后? 沒有權限 只能看到information_schema和performance_schema兩張表

查詢用戶權限

SHOW GRANTS FOR '用戶名'@'主機名';

新創建的用戶只能看到

授予權限

GRANT 權限列表 ON 數據庫名.表名 TO '用戶名'@'主機名';? #如果? *.* 表示所有數據庫,所有表

GRANT ALL ON *.* TO 'root1'@'%'; #授予root1所有權限? ?注意:RDS上不能這么做 不能給與非root用戶所有的權限? 可以設置具體的庫和具體的表

GRANT ALL PRIVILEGES ON testdb.* TO 'root1'@'%';和GRANT ALL ON testdb.* TO 'root1'@'%';效果事一樣的

撤銷權限

REVOKE all on *.* FROM 'root1'@'%'; 撤銷所有權限

注意:多個權限用逗號分隔,授權時數據庫和表名可以使用 * 進行統配 *.* 就是所有庫所有表

阿里云RDS不允許創建非root用戶 的 *.* 全庫權限

重點

1.用戶管理

CREATE USER '用戶名'@'主機名' IDENTIFIED BY '密碼'; #可以在任何庫執行? 不需要指定庫

ALTER USER '用戶名'@'主機名' IDENTIFIED WITH mysql_native_password BY '新密碼';

DROP USER '用戶名'@'主機名';#可以在任何庫執行? 不需要指定庫 就是不用use xxx

2.權限控制

GRANT 權限列表 ON 數據庫名.表名 TO '用戶名'@'主機名';

REVOKE 權限列表 ON 數據庫名.表名 FROM '用戶名'@'主機名';

select * from user; #需要指定數據庫 use mysql;

TCL(Transaction Control Language):事務控制語句,用于控制事務

常用關鍵字

  • BEGIN / START TRANSACTION:開始事務

  • COMMIT:提交事務

  • ROLLBACK:回滾事務

  • SAVEPOINT:設置保存點

  • SET TRANSACTION:設置事務屬性

函數

指一段可以直接被另一段程序調用的程序或代碼

字符串函數

CONCAT 參數可以是1個或者多個? 依次拼接參數 模糊查詢時候經常用到

注意SUBSTRING? ?start是從1開始的? ? TRIM 無法去掉str中間的空格

數值函數

常用的數值函數

日期函數

流程函數

常用示例

END是為了結束語法? 必寫? 然后取個別名? ELSE 可以不寫? 不寫的話都沒有匹配到就返回NULL

約束

約束:作用于表中字段上的規則,用于限制存儲在表中的數據

目的:保證數據庫中的數據的正確,有效性完整性

各種約束示例

DEFAULT NULL,? ?DEFAULT 0, DEFAULT 'XXX'

CREATE TABLE users (
? ? id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID唯一標識',
? ? name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
? ? age INT CHECK (age > 0 AND age <= 120) COMMENT '年齡',
? ? status CHAR(1) DEFAULT '1' COMMENT '狀態',
? ? gender CHAR(1) COMMENT '性別'
) COMMENT='用戶表';
?

約束是作用于表中字段上的,可以在創建表/修改表的時候添加約束

外鍵約束

當一張表中的字段引用了另一張表的主鍵時,這個字段稱為“外鍵”(foreign key),這個關系稱為外鍵約束

這樣 如果要刪除dept表中數據前,先要刪除emp表中關聯數據

示例

后面加ON

多表查詢

關于sql92和sql99寫法

sql92寫法? 兩張表用","分割,關聯關系放在where后面

SELECT emp.name, dept.name
FROM emp, dept
WHERE emp.dept_id = dept.id;
?

sql99語法

SELECT emp.name, dept.name
FROM emp
INNER JOIN dept ON emp.dept_id = dept.id;
?

多表關系

在關系型數據庫中,常見的表與表之間關系有這 三種:一般現在都不建議外鍵

1.一對一

CREATE TABLE user (
? ? id INT PRIMARY KEY AUTO_INCREMENT,
? ? username VARCHAR(50)
);

CREATE TABLE user_detail (
? ? id INT PRIMARY KEY AUTO_INCREMENT, ? -- 自己的主鍵
? ? user_id INT UNIQUE, ? ? ? ? ? ? ? ? ?-- 外鍵 + 唯一約束實現一對一
? ? address VARCHAR(100),
? ? phone VARCHAR(20),
? ? FOREIGN KEY (user_id) REFERENCES user(id)
);
查詢示例

SELECT u.username, d.phone
FROM user u
LEFT JOIN user_detail d ON u.id = d.user_id
WHERE u.id = 1;
?

A 表的一條記錄只能對應 B 表的一條記錄,反之亦然

2.一對多

A 表一條記錄可以對應 B 表的多條記錄

CREATE TABLE dept (
? ? id INT PRIMARY KEY,
? ? name VARCHAR(50)
);

CREATE TABLE emp (
? ? id INT PRIMARY KEY,
? ? name VARCHAR(50),
? ? dept_id INT,
? ? FOREIGN KEY (dept_id) REFERENCES dept(id)
);
查詢示例,查某個部門下的員工

SELECT d.name AS 部門名, e.name AS 員工名
FROM dept d
LEFT JOIN emp e ON d.id = e.dept_id
WHERE d.id = 1;
查某個員工在哪個部門

SELECT e.name AS 員工名, d.name AS 部門名
FROM emp e
JOIN dept d ON e.dept_id = d.id
WHERE e.id = 1;

3.多對多

A 表中的記錄可以對應 B 表的多條記錄,B 表中的記錄也能對應 A 表的多條記錄? 需要一張中間表保存A表ID和B表ID

CREATE TABLE student (
? ? id INT PRIMARY KEY,
? ? name VARCHAR(50)
);

CREATE TABLE course (
? ? id INT PRIMARY KEY,
? ? name VARCHAR(50)
);

CREATE TABLE student_course ( -- 中間表
? ? student_id INT,
? ? course_id INT,
? ? PRIMARY KEY (student_id, course_id), #可以用這個做主鍵 因為student_id和course_id? ? ? ? 一定唯一,也可以單獨創建主鍵? 自行維護? 外鍵現在一般都不建議
? ? FOREIGN KEY (student_id) REFERENCES student(id),
? ? FOREIGN KEY (course_id) REFERENCES course(id)
);

查詢示例:查某個員工選了哪些課程

SELECT s.name AS 學生, c.name AS 課程
FROM student s
JOIN student_course sc ON s.id = sc.student_id
JOIN course c ON sc.course_id = c.id
WHERE s.id = 1;

多表查詢創建幾張關聯表

#創建部門表并插入數據

CREATE TABLE dept (
? ? id INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
? ? name VARCHAR(50) NOT NULL COMMENT '部門名稱'
) COMMENT '部門表';

INSERT INTO dept (id, name) VALUES
(1, '研發部'),
(2, '市場部'),
(3, '財務部'),
(4, '銷售部'),
(5, '總經辦'),
(6, '人事部');

#創建員工表并插入數據
CREATE TABLE emp (
? ? id INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
? ? name VARCHAR(50) NOT NULL COMMENT '姓名',
? ? age INT COMMENT '年齡',
? ? 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, '2002-09-12', 1, 3),
(8, '周芷若', 19, '會計', 48000, '2006-06-02', 7, 3),
(9, '丁敏君', 23, '出納', 5250, '2009-05-13', 7, 3),
(10, '趙敏', 20, '市場部總監', 12500, '2004-10-12', 1, 2),
(11, '鹿杖客', 56, '職員', 3750, '2006-10-03', 10, 2),
(12, '鶴筆翁', 19, '職員', 3750, '2007-05-09', 10, 2),
(13, '方東白', 19, '職員', 5500, '2009-02-12', 10, 2),
(14, '張三豐', 88, '銷售總監', 14000, '2004-10-12', 1, 4),
(15, '俞蓮舟', 38, '銷售', 4600, '2004-10-12', 14, 4),
(16, '宋遠橋', 40, '銷售', 4600, '2004-10-12', 14, 4),
(17, '陳友諒', 42, NULL, 2000, '2011-10-12', 1, NULL);
?

#在員工表中創建外鍵

ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept_id
FOREIGN KEY (dept_id) REFERENCES dept(id);

笛卡爾積演示

SELECT * FROM emp,dept;

沒有 WHERE 條件或 JOIN 連接條件,數據庫就會把 emp 表中的每一行,與 dept 表中的每一行都組合一遍。

  • emp 有 17 條數據

  • dept 有 6 條數據

  • 17 × 6 = 102 行

需要寫連接條件

多表查詢分類?

多表查詢分類:

1.連接查詢

? ? ? ? 1.1)內連接:相當于查詢A,B交集部門數據

? ? ? ? 1.2)外連接:

? ? ? ? ? ? ? ? 2.1)左外連接:查詢左表所有數據,以及兩張表交集部門數據

? ? ? ? ? ? ? ? 2.2)右外連接:查詢右表所有數據,以及兩張表交集部門數據

? ? ? ? 1.3)自連接:當前表與自身的連接查詢,自連接必須使用表別名

2.子查詢:把單表分為兩張表,需要有連接條件

內連接

返回的是兩張表交集部門的數據

sql92寫法(隱式寫法)

SELECT 字段列表 FROM 表1,表2 WHERE 表1.字段=表2.字段;

sql99寫法(顯式寫法)

SELECT 字段列表
FROM 表1 [INNER] JOIN 表2 ON 表1.字段 = 表2.字段;

內連接練習

查詢每一個員工的姓名,及關聯的部門的名稱(隱式內連接實現)

SELECT a.name,b.name deptName FROM emp a,dept b WHERE a.dept_id=b.id;

查詢每一個員工的姓名,及關聯的部門名稱(顯式內連接實現)

SELECT a.name,b.name deptName FROM emp a JOIN dept b ON a.dept_id=b.id;

外連接

左外連接

語法

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2? ON 連接條件;

以左表為主,查左表所有數據,及交集數據 交集沒有的以null填充

右外連接

語法

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN? 表2 ON 連接條件;?

以右表為主,查右表所有數據,及交集數據 交集沒有的以null填充

自連接

自連接查詢可以是內連接查詢也可以是外連接查詢

SELECT 字段列表 FROM 表1 別名A JOIN 表2 別名B ON 連接條件;

連接條件

員工的manage_id=老板表.id

聯合查詢UNION,UNION ALL

UNION查詢,就是把多次查詢的結果合并起來,形成一個新的查詢結果集

語法

SELECT 字段1, 字段2, ...
FROM 表1
WHERE 條件
UNION [ALL]
SELECT 字段1, 字段2, ...
FROM 表2
WHERE 條件;


注意:需要保證查詢出來的字段名 或者別名是相同的并且列數是相同的

子查詢

定義:SQL語句中嵌套SELECT語句,稱為嵌套查詢,又稱為子查詢

SELECT * FROM 表1 WHERE column1=(SELECT column1 FROM 表2)

子查詢的寫法很多

子查詢外部的語句可以是INSERT/UPDATE/DELETE/SELECT的任何一個

① SELECT *?
? ?FROM A?
? ?WHERE EXISTS (
? ? ? ?SELECT id?
? ? ? ?FROM B?
? ? ? ?WHERE B.aid = A.id
? ?)

#為什么用DISTINCT? ?如果A和B是1對多情況 A表數據匹配B表多條會出現重復

② SELECT DISTINCT A.*
? ?FROM A
? ?JOIN B ON A.id = B.aid

③ SELECT *?
? ?FROM A?
? ?WHERE A.id IN (
? ? ? ?SELECT B.aid?
? ? ? ?FROM B
? ?)
三種寫法結果是等價的

子查詢根據結果不同,可以分為

1.標量子查詢(子查詢結果為單個值)

2.列子查詢(子查詢結果為一列)

3.行子查詢(子查詢結果為一行)

4.表子查詢(子查詢結果為多行多列)

根據子查詢位置,分為:WHERE之后,FROM 之后,SELECT之后.

標量子查詢

子查詢結果為單個值

常用的操作符: =? ? <>? ?>? ?>=? ? <? ? <=

查詢銷售部的所有的員工信息

SELECT * FROM emp WHERE dept_id=(SELECT id FROM dept WHERE name='銷售部')

查詢在方東白入職之后入職的員工信息

SELECT * FROM emp WHERE entrydate>(SELECT entrydate FROM emp WHERE name='方東白')

列子查詢

子查詢結果為一列

常用操作符:IN? ? ? NOT IN? ? ANY? ? SOME? ?ALL

查詢銷售部和市場部的所有員工信息

SELECT * FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE name IN('銷售部','市場部'))

查詢比財務部所有員工工資都高的員工 (這里將財務部周芷若的工資改為8000)插入數據時候數據有問題

SELECT * FROM emp WHERE salary >(SELECT MAX(salary) FROM emp WHERE dept_id=(SELECT id FROM dept WHERE name='財務部'))

SELECT * FROM emp WHERE salary >all(SELECT salary?FROM emp WHERE dept_id=(SELECT id FROM dept WHERE name='財務部'))

查詢比研發部其中任意一人工資高的員工信息

SELECT * FROM emp WHERE salary>any(SELECT salary FROM emp WHERE dept_id=(SELECT id FROM dept WHERE name='研發部'))

行子查詢

子查詢結果為一行(可以是多列)

常用操作符: =? ? ?<>? ? IN? ? NOT IN

查詢與張無忌的薪資及直屬領導相同的員工信息

薪資相同

SELECT salary FROM emp WHERE salary=(SELECT salary FROM emp WHERE name='張無忌')

直屬領導相同

SELECT manageid?FROM emp ?WHERE name='張無忌'?

SELECT *?
FROM emp?
WHERE salary = (
? ? SELECT salary FROM emp WHERE name = '張無忌'
)
AND managerid = (
? ? SELECT managerid FROM emp WHERE name = '張無忌'
);

可以這么寫

SELECT *?
FROM emp?
WHERE (salary, managerid) = (
? ? SELECT salary, managerid?
? ? FROM emp?
? ? WHERE name = '張無忌'
);

表子查詢

子查詢結果為多行多列

常用操作符:IN

查詢與鹿杖客 宋遠橋的職位和薪資相同的員工信息

鹿杖客 宋遠橋的職位和薪資

SELECT job,salary FROM emp WHERE name IN('鹿杖客','宋遠橋')

SELECT * FROM emp WHERE (job,salary) IN(SELECT job,salary FROM emp WHERE name IN('鹿杖客','宋遠橋'))

查詢入職日期是2006-01-01之后的員工信息,及其部門信息

SELECT
? ? A.*,
? ? B.name
FROM emp A
LEFT JOIN dept B ON A.dept_id = B.id
WHERE A.entrydate > '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;
?

多表查詢案例

新增下面這張表? 工資等級表(媽了個巴子資本主義啊) 孫中山先生是偉大的'理想家'......

CREATE TABLE salgrade (
? ? grade INT COMMENT '等級',
? ? losal INT COMMENT '最低工資',
? ? hisal INT COMMENT '最高工資'
) COMMENT='薪資等級表';

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

查詢員工的姓名,年齡,職位,部門信息

SELECT a.name,a.age,a.job,b.name deptName FROM emp a LEFT JOIN dept b ON a.dept_id=b.id

查詢年齡小于30歲的員工姓名,年齡,職位,部門信息

SELECT a.name,a.age,a.job,b.name deptName FROM emp a LEFT JOIN dept b ON a.dept_id=b.id WHERE a.age<30

查詢擁有員工的部門ID,部門名稱? ?(查詢交集部分去重)

SELECT id,name FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE dept_id is not null)

SELECT DISTINCT d.id,d.name FROM emp e,dept d WHERE e.dept_id=d.id

查詢所有年齡大于40歲的員工,及其歸屬的部門名稱,如果員工沒有分配部門,也需要展示出來

SELECT a.name,b.name deptName FROM emp a LEFT JOIN dept b ON a.dept_id=b.id WHERE a.age>40

查詢所有員工的工資等級

SELECT
?? ?a.NAME,
?? ?b.grade?
FROM
?? ?emp a LEFT JOIN salgrade b?
ON?
? a.salary BETWEEN b.losal AND b.hisal

或者

SELECT a.name, b.grade?
FROM emp a?
LEFT JOIN salgrade b?
? ? ON a.salary >= b.losal AND a.salary <= b.hisal;

查詢研發部所有員工的信息及工資等級

SELECT
?? ?a.NAME,
?? ?c.grade,
?? ?b.NAME deptName??
FROM
?? ?emp a
?? ?JOIN dept b ON a.dept_id = b.id JOIN salgrade c ON a.salary BETWEEN c.losal?
?? ?AND c.hisal?
WHERE
?? ?b.NAME = '研發部'

查詢研發部員工的平均工資

SELECT
? ? a.name AS deptName,
? ? AVG(b.salary) AS avgSalary
FROM dept a
LEFT JOIN emp b ON a.id = b.dept_id
WHERE a.name = '研發部'
GROUP BY a.name;

查詢工資比滅絕高的員工信息

SELECT * FROM emp WHERE salary>(SELECT salary FROM emp WHERE name='滅絕')

查詢比平均薪資高的員工信息

SELECT * FROM emp WHERE salary>(SELECT AVG(salary) FROM emp)

查詢低于本部門平均工資的員工信息

SELECT a.*
FROM emp a
JOIN (
? ? SELECT dept_id, AVG(salary) AS avg_salary
? ? FROM emp
? ? GROUP BY dept_id
) AS b ON a.dept_id = b.dept_id
WHERE a.salary < b.avg_salary;
?

查詢所有的部門信息,并統計部門的員工人數

SELECT deptName, COUNT(*)?
FROM (SELECT a.*, b.name deptName?
? ? ? FROM emp a?
? ? ? JOIN dept b ON a.dept_id = b.id) t
GROUP BY deptName

事務

事務是一組操作的集合,它是一個不可分割的工作單位,事務會把所有的操作作為一個整體一起向系統提交或撤銷操作請求,這些操作要么同時成功,要么同時失敗

默認情況下,使用Mysql默認自動提交事務的

創建事務演示表

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);

事務操作

以下兩種都是臨時會話級別設置事務手動提交

方式1:

開啟事務: START TRANSACTION? 或者? BEGIN

提交事務: COMMIT??

回滾事務: ROLLBACK

方式2

可以通過上面方式? 也可以設置

SELECT @@autocommit;? ?#如果是1 自動提交? ?0手動提交
SET @@autocommit = 0;

方式2不需要START TRANSACTION? 或者? BEGIN? ?可以直接用COMMIT? ?ROLLBACK

一般只需要了解會話級別事務即可

方式1

方式2

事務四大特性
1. 原子性(Atomicity

事務是不可分割的最小操作單元,要么全部成功,要么全部失敗

💡 比如轉賬:扣錢成功但收錢失敗,就要全部回滾。

2. 一致性(Consistency

事務執行后,必須使數據庫從一個一致狀態變為另一個一致狀態

💡 比如轉賬后,張三減少的錢和李四增加的錢總數不變,前后一致。

3. 隔離性(Isolation

多個事務并發執行時,要互不干擾,互不影響,數據庫提供隔離機制保障這一點。

💡 比如你在查庫存時,別人的下單操作不會影響你看到的結果。

4. 持久性(Durability

事務一旦提交,對數據庫的修改就是永久性的,即使系統崩潰也不會丟失

💡 比如提交轉賬后,即使斷電重啟,數據也已經改了。

?

并發事務問題

兩個或多個事務在“同時”對同一份數據(同一條)進行操作或讀取,從而引發數據不一致、錯亂等問題。

例如張三的銀行卡

在一個事務中

SELECT money FROM account WHERE name = '張三'; ?-- 得到 1000
UPDATE account SET money = 1000 - 100 WHERE name = '張三'; ?-- 更新為 900

?在另外一個事務中

SELECT money FROM account WHERE name = '張三'; ?-- 也看到 1000!
UPDATE account SET money = 1000 - 200 WHERE name = '張三'; ?-- 更新為 800

結果誰最后提交就覆蓋了另一個,導致少減了一筆!?

臟讀

一個事務 讀取了另一個事務尚未提交的數據

Read Uncommitted(讀未提交)發生

舉例

當A事務開啟,執行 但是還沒有提交

UPDATE account SET money = 0 WHERE name = '張三';


此時事務 B 執行

SELECT money FROM account WHERE name = '張三'; ?-- 查到 0(但其實沒提交)

然后事務 A 回滾了,張三余額還是原來的,但事務 B 卻讀到了“臟數據”

不可重復讀

同一個事務中,前后兩次讀取同一條數據,結果不一樣(因為別的事務修改了數據并提交了)

Read Committed(讀已提交)會發生

舉例

當事務A開啟

SELECT money FROM account WHERE name = '張三'; ?-- 第一次查到 1000

此時事務B對該數據進行了操作

UPDATE account SET money = 500 WHERE name = '張三';
COMMIT;

當A事務再次查詢時候

SELECT money FROM account WHERE name = '張三'; ?-- 查到 500(發生了不可重復讀)
?

幻讀

幻讀是指:同一個事務內,使用相同查詢條件進行多次查詢時,結果集的“行數”或“記錄”發生了變化原因是其他事務在期間插入或刪除了“符合條件的新記錄”。

Repeatable Read(可重復讀)

舉例

事務A開啟

SELECT * FROM account WHERE money > 1000; ?-- 查到 2 條記錄
?

事務B插入

INSERT INTO account(name, money) VALUES ('王五', 2000);
COMMIT;

當事務A再次執行相同查詢

SELECT * FROM account WHERE money > 1000; ?-- 現在查到 3 條記錄(多了一條“幻影”)
?

事務隔離級別

默認自己安裝的Mysql是可重復讀? ? ?阿里云RDS是讀已提交

查看事務隔離級別

SELECT @@transaction_isolation;
-- 或舊版本:
SELECT @@tx_isolation;

會話級別設置

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

全局設置

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

可選級別

READ UNCOMMITTED? ? ?讀未提交

READ COMMITTED? ? 讀已提交

REPEATABLE READ? ?可重復讀

SERIALIZABLE? ?序列化讀

Mysql進階篇

存儲引擎

在Java(編程語言)中,"引擎"(Engine)就是某一類核心功能的實現模塊/組件,它封裝了復雜的底層邏輯,開發者只需要調用接口就能使用。

MySQL 的存儲引擎是數據庫內部用于管理表數據的核心模塊,負責實現數據的存儲、索引的建立與維護、數據的讀取、更新、刪除等底層功能。存儲引擎是基于表的,而不是基于庫的,所以存儲引擎也被稱表類型

就像 Java 引擎對外暴露接口供你使用一樣,MySQL 把數據管理的復雜邏輯封裝在“引擎”里,不同引擎有不同的能力,比如是否支持事務、是否支持全文索引等。

  • 決定數據怎么存(數據結構、磁盤布局)

  • 決定索引怎么建(B+ 樹、哈希等)

  • 決定數據怎么讀寫(是否緩存、是否支持并發控制)

  • 決定是否支持事務、外鍵、崩潰恢復等高級功能

在創建表時如果沒有顯式指定存儲引擎(ENGINE),MySQL 會使用系統設置的“默認引擎”,Mysql的默認引擎是InnoDB,并不是基于庫的

default_storage_engine 是 MySQL 中用于指定“默認存儲引擎”的系統變量
當你創建一張表時沒有顯式寫 ENGINE=xxx,就會自動使用這個變量指定的存儲引擎。

查看默認引擎

SHOW VARIABLES LIKE 'default_storage_engine';

創建表時候指定存儲引擎

CREATE TABLE 表名 (
? ? 字段1 字段1類型 COMMENT '字段1注釋',
? ? 字段2 字段2類型 COMMENT '字段2注釋',
? ? ...
? ? 字段n 字段n類型 COMMENT '字段n注釋'
) ENGINE = INNODB COMMENT = '表注釋';
?

MySQL 內置已“注冊”的所有存儲引擎類型

SHOW ENGINES;?

InnoDB,MyISAM,MEMORY三種引擎介紹
InnoDB介紹

Mysql5.5之后,InnoDB是默認的Mysql存儲引擎

InnoDB特點

DML操作遵循ACID模型,支持事務;

行級鎖,提高并發訪問性能

支持外鍵FOREIGN KEY約束,保證數據的完整性和正確性

每張使用 InnoDB 存儲引擎的表,在磁盤上會有一個 .ibd 文件,這個文件就是該表的**“獨立表空間”**,用來存儲表的:

  • 結構(配合 .sdi?文件)

  • 數據

  • 索引

這個行為是由參數 innodb_file_per_table 控制的。

參數:innodb_file_per_table

SHOW VARIABLES LIKE 'innodb_file_per_table';

該文件是二進制的 普通記事本打開看不到啥

可以通過cmd命令

ibd2sdi emp.ibd? ? ? ? ? ? ? ? ? ? ?ibd2sdi是命令? ?emp.ibd是表空間? 好比表

我這里出現這個原因可能是文件損壞了? 因為安裝過不同版本的

換了一個庫的表.ibd就好了

MyISAM介紹

MyISAM是Mysql早期的默認存儲引擎

特點

不支持事務,不支持外鍵

支持表鎖,不支持行鎖

訪問速度快(具體說和InnoDB比,看具體場景分析)

文件結構 .myd? ?.myi? ?.sdi

MEMORY介紹

Memory引擎的表數據存儲在內存中,受到硬件問題,或者斷電的影響,只能做為臨時或者緩存使用

特點

內存中存放

hash索引(默認)

文件? xxx.sdi存儲表結構信息無具體數據

主要用的是InnoDB? ? MyISAM被MongoDB取代? ? MEMORY被Redis取代

Linux中Mysql安裝?

https://www.mysql.com/


mkdir /usr/local/develop

cd /usr/local/develop

上傳到服務器

rpm -ivh mysql84-community-release-el7-1.noarch.rpm

yum install -y mysql-community-server

systemctl start mysqld

systemctl status mysqld

查看初始密碼

grep 'temporary password' /var/log/mysqld.log

mysql -u root -p

修改密碼

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';

use mysql;

select host,user from user;

其實是建議再創建一個root? ?Mysql支持同一個用戶名有多個不同的登錄規則

在Mysql中? 用戶是通過 user@host的組合來做唯一標識的而不是單純看用戶名

我是直接Update了? 如果需要重新創建? 執行下面三跳命令

CREATE USER 'root'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;這個好比刷新 好像不需要執行

UPDATE mysql.user SET host='%' WHERE user='root' AND host='localhost';

exit

vim /etc/my.cnf

bind-address = 0.0.0.0?

systemctl restart mysqld

安全組打開3306

其他安裝方式

你也可以下載整個RPM包

https://dev.mysql.com/downloads/mysql/

mkdir /usr/local/develop

cd /usr/local/develop

將下載的

mysql-8.4.4-1.el7.aarch64.rpm-bundle.tar? 上傳到服務器

tar -xzf?mysql-8.4.4-1.el7.aarch64.rpm-bundle.tar

這種方式沒有試驗? 使用的是上面的? ?解壓之后? 安裝是有順序的? 大致如下

安裝順序

systemctl start mysqld

systemctl restart mysqld

systemctl stop mysqld

啟動之后? 查詢密碼

grep 'temporary password' /var/log/mysqld.log

mysql -u root -p

ALTER ?USER ?'root'@'localhost' ?IDENTIFIED BY '1234';

如果需要的話

索引

索引是 MySQL 中用于提高數據查詢效率的數據結構,它類似于書的目錄或字典的檢索頁,通過有序的數據結構,快速定位到目標數據。

什么是索引:索引是用于提高數據查詢效率有序的數據結構

沒有索引的查詢是全表查詢:每條數據都需要查一遍

MySQL 的索引數據結構不是二叉樹,而是多路搜索樹——最常見的是 B+ 樹。

以二叉樹為例子介紹索引? 注意 二叉樹不是索引數據結構? 就是說為什么快了

索引的好處是提高了查詢,因索引需要維護,降低了增刪改的效率

索引的好處是:提高查詢效率
索引的代價是:增刪改操作時要維護索引結構,導致效率下降

索引數據結構

Mysql的索引實在存儲引擎層實現的,不同的存儲引擎有不同的結構,主要包含以下幾種

我們平常所說的索引,如果沒有特別指明,都是指B+樹結構的索引

注意二叉樹不是索引的數據結構? 下圖講的是為什么二叉樹不作為索引數據結構的原因

紅黑樹面對大數據時候還是無法避免層級較深,導致檢索速度慢

B-TREE

B+TREE?

索引的分類?

總結一句話: 主鍵索引一張表只能有一個? 其他索引都可以有多個

MySQL 索引分為:主鍵索引(唯一 + 非空)、唯一索引(值不能重復)、普通索引(僅加速查詢)、全文索引(文本匹配)。使用時根據數據特征和查詢需求選擇合適的類型。

?

創建索引
索引語法

CREATE [UNIQUE FULLTEXT] INDEX index_name ON table_name(列1,列2);

1.主鍵索引(PRIMARY KEY)

每個表只能有一個主鍵索引

主鍵值不能重復,不能為NULL(非空且唯一),實際是一個特殊的唯一索引

2.唯一索引(UNIQUE)

保證某一列或多列的值唯一

可以有多個唯一索引,允許NULL值(但只能有一個NULL)

CREATE UNIQUE INDEX inx_email ON users(email);

或者建表時

CREATE TABLE users (?
? ? id INT PRIMARY KEY,
? ? email VARCHAR(100) UNIQUE,
? ? username VARCHAR(100) UNIQUE
);

CREATE TABLE tasks (
? ? user_id INT,
? ? project_id INT,
? ? task_name VARCHAR(100),
? ? UNIQUE KEY uniq_user_project (user_id, project_id)
);?

3.普通索引

即B-TREE索引

普通索引,沒有唯一性限制,用于提高查詢速度

CREATE INDEX inx_name ON users(name);??

普通索引(非唯一索引)是可以為 NULL 的!

4.復合索引

包含多個列的索引

只有查詢條件命中從最左邊開始的一段,才能用到索引

CREATE INDEX inx_name_age ON users(name,age);

5.全文索引(FULLTEXT)

用于全文搜索,主要針對CHAR VARCHAR TEXT

CREATE FULLTEXT INDEX idx_content ON articles(content)

MyISAM 和 InnoDB 引擎都支持 FULLTEXT(MySQL 5.6 及以上 InnoDB 開始支持)。

6.空間索引

用于地理空間數據(如點,多邊形),依賴GIS

必須在使用MyISAM引擎時有效

CREATE SPATIAL INDEX idx_location ON places(location);

MySQL 復合索引的使用原則 —— 特別是 最左前綴原則

例如創建了 復合索引或者多列聯合唯一索引

例如 A列? B列? C列? ?D列

A? B? C是有索引的

只要查詢條件中有A? 都會使用到索引? ?即使查詢條件中有D

查看索引

SHOW INDEX FROM table_name

刪除索引

DROP INDEX index_name ON table_name;

注意:

練習

CREATE TABLE tb_user (
?? ?id INT PRIMARY KEY auto_increment COMMENT '主鍵',
?? ?NAME VARCHAR ( 50 ) NOT NULL COMMENT '用戶名',
?? ?phone VARCHAR ( 11 ) NOT NULL COMMENT '手機號',
?? ?email VARCHAR ( 100 ) COMMENT '郵箱',
?? ?profession VARCHAR ( 11 ) COMMENT '專業',
?? ?age TINYINT UNSIGNED COMMENT '年齡',
?? ?gender CHAR ( 1 ) COMMENT '性別 , 1: 男, 2: 女',
?? ?STATUS CHAR ( 1 ) COMMENT '狀態',
createtime datetime COMMENT '創建時間'?
) COMMENT '系統用戶表';

INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('呂布', '17799990000', 'lvbu666@163.com', '軟件工程', 23, '1',
'6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通訊工程', 33,
'1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('趙云', '17799990002', '17799990@139.com', '英語', 34, '1',
'2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('孫悟空', '17799990003', '17799990@sina.com', '工程造價', 54,
'1', '0', '2001-07-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('花木蘭', '17799990004', '19980729@sina.com', '軟件工程', 23,
'2', '1', '2001-04-22 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('大喬', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2',
'0', '2001-02-07 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '應用數學', 24,
'2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38,
'1', '5', '2001-05-23 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('項羽', '17799990008', 'xiaoyu666@qq.com', '金屬材料', 43,
'1', '0', '2001-09-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '機械工程及其自動
化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('韓信', '17799990010', 'hanxin520@163.com', '無機非金屬材料工
程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('荊軻', '17799990011', 'jingke123@163.com', '會計', 29, '1',
'0', '2001-05-11 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('蘭陵王', '17799990012', 'lanlinwang666@126.com', '工程造價',
44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狂鐵', '17799990013', 'kuangtie@sina.com', '應用數學', 43,
'1', '2', '2001-04-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('貂蟬', '17799990014', '84958948374@qq.com', '軟件工程', 40,
'2', '3', '2001-02-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '軟件工程', 31,
'2', '0', '2001-01-30 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('羋月', '17799990016', 'xiaomin2001@sina.com', '工業經濟', 35,
'2', '0', '2000-05-03 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1',
'1', '2001-08-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '國際貿易',
30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市規劃', 51,
'2', '0', '2001-08-15 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('典韋', '17799990020', 'ycaunanjian@163.com', '城市規劃', 52,
'1', '2', '2000-04-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('廉頗', '17799990021', 'lianpo321@126.com', '土木工程', 19,
'1', '3', '2002-07-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市園林', 20,
'1', '0', '2002-03-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造價', 29,
'1', '4', '2003-05-26 00:00:00');

1.name字段為姓名字段,該字段的值可能會重復,為該字段創建索引

CREATE INDEX inx_user_name ON tb_user(name);

2.phone手機號字段的值,是非空,且唯一的,為該字段創建唯一索引

CREATE UNIQUE INDEX inx_user_phone ON tb_user(phone);

3.為profession,age,status創建聯合索引

CREATE INDEX inx_user_pro_age_sta ON tb_user(profession,age,status);

4.為email建立合適的索引來提升查詢效率

CREATE INDEX inx_user_email ON tb_user(email);

SQL性能分析(SQL優化)
SQL執行頻率

SHOW [SESSION|GLOBAL] STATUS查看服務狀態信息

SHOW SESSION STATUS

SHOW GLOBAL STATUS

SHOW GLOBAL STATUS LIKE 'Com_______';

SHOW GLOBAL STATUS LIKE 'Com_select'

慢查詢日志

慢查詢日志記錄了所有執行時間超過指定參數(long_query_time,單位:秒,默認10秒)的所有SQL語句的日志

Mysql的慢查詢日志默認沒有開啟,需要在Mysql的配置文件(/etc/my.cnf)中配置

SHOW VARIABLES LIKE 'SLOW_QUERY_LOG'

這里用的是阿里云RDS? 默認是開啟的

沒有開啟是OFF

開啟Profile詳情

show profiles能夠在做SQL優化時幫助我們了解時間都耗費到哪里了.通過have_profiling參數,可以查看當前Mysql是否支持profile操作

SELECT @@have_profiling

以下是阿里云RDS配置? 默認是關閉的

SELECT @@profiling

可以通過SET? 開啟profiling

SET profiling=1

開啟profile詳情之后

通過指令可以查看執行耗時情況

開啟profile之后,當你執行SQL操作? 通過

SHOW profiles查看執行情況

SHOW profile for query 16;? 查看指定sql各階段耗時情況

Explain執行計劃

開啟profile可以看到SQL的執行時間,但是單純看到執行時間還是不能精確定位分析SQL的優化

explain執行計劃可以看到包括SELECT語句執行過程,例如如果連接和連接順序,是否用到索引

通過EXPLAIN或者DESC命令獲取Mysql如何執行SELECT語句信息

直接在SELECT語句之前加上關鍵字expain/desc

EXPLAN SELECT 字段列表 FROM 表名 WHERE 條件;

索引使用與失效

SELECT * FROM tb_sku WHERE sn = '10000003145004';

如果一張表的數據量很大 那么可以對sn創建索引

SELECT * FROM tb_sku WHERE sn = '10000003145004' AND other_field = 'xxx';

只要 sn 是查詢條件的一部分,MySQL 仍然會使用 sn 的索引來過濾第一步的結果集。

然后,它會在命中的 sn 結果基礎上,繼續判斷其他字段(如 other_field)的條件是否滿足

最左前綴法則

如果索引了多列(聯合索引),要遵守最左前綴法則,最左前綴法則指的是查詢從索引的最左列開始,并且不跳過索引中的列.如果跳躍某一列,索引將部分失效(后面的字段索引失效)

例如A列? B列? C列? D列? ?ABC是復合索引? D沒有索引

注意只要WHERE條件中存在? ?和位置在前或者在后沒關系

范圍查詢

聯合索引中,出現范圍查詢,范圍查詢,右側的列索引將失效

索引列運算

不要在索引列上進行運算操作,否則索引將失效

字符串不加引號?

字符串類型字段使用時,不加引號,索引將失效

模糊查詢

如果僅僅是尾部模糊匹配,索引不會失效.如果是頭部模糊匹配,索引失效

OR連接條件?

用OR分割開的條件,如果OR前的條件中的列有索引,而后面的列中沒有索引,那么涉及的索引都不會被用到

索引使用原則

數據分布影響:如果Mysql評估使用索引比全表掃描慢,則不適用索引

當大部分需要查的時候也可能會走全表掃描

當查詢的數據量占表中“大部分”時,MySQL 很可能放棄使用索引,選擇🔁全表掃描,因為它認為這樣反而更快!? ? 注意是可能

由查詢優化器決定

主要由 MySQL 查詢優化器(Query Optimizer) 根據多個因素動態評估查詢成本,決定是否使用索引。

索引的選擇(SQL提示)

當你創建了聯合索引, 例如 A? B? C 三個字段? ? ?而又對A也創建了一個索引

當你用A字段進行查詢時候

Mysql查詢優化器會動態選擇用哪一個索引

我們也可以指定使用哪個索引,稱為SQL提示

簡單來說,就是SQL語句中加入一些認為的提示來達到優化操作的目的

SQL提示

1.USE INDEX 告訴Mysql用哪個索引

例如:

SELECT * FROM tb_user USE INDEX(inx_use_pro) WHERE profession='軟件工程';

2.IGNORE INDEX? 告訴Mysql不要用哪個索引

SELECT * FROM tb_user IGNORE INDEX(inx_use_pro) WHERE profession='軟件工程';

3.FORCE INDEX 告訴Mysql必須用這個索引

SELECT * FROM tb_user FORCE INDEX(inx_use_pro) WHERE profession='軟件工程';

覆蓋索引

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

查詢條件是索引并且返回的字段也是索引就是覆蓋索引,使用SELECT * 容易出現回表查詢 除非所有字段都創建了索引

前綴索引

當字段類型為字符串(VARCHAR,TEXT等)時,有時候需要索引很長的字符串.會使索引變得很大,影響查詢效率.可以將字符串的一部分前綴,建立索引,可以節約索引空間.提高索引效率

前綴索引:指的是 只對字符串字段的前 N 個字符建立索引,而不是整列。

創建前綴索引

CREATE INDEX inx_table_xxx ON table_name(CLOLUMN(n))

例如

CREATE INDEX idx_email_prefix ON users(email(10));

單例索引和聯合索引的選擇

單例索引:即一個索引只包含單個列

聯合索引:即一個索引包含多個列

索引設計原則

SQL優化

創建索引可以當作查詢數據的優化,以下介紹其他操作的優化

插入數據優化

?在已經有sql腳本的情況下? 可以使用load命令

主鍵優化

主鍵順序插入的性能高于主鍵亂序插入----------->即為主鍵優化

order by優化

group by優化

limit優化

LIMIT查詢時候問題出在哪里

SELECT * FROM tb_user LIMIT 0,10;

SELECT * FROM tb_user LIMIT 1000000000,10;? 越往后查詢越慢

一個非常常見且代價昂貴的問題是使用 LIMIT 2000000, 10,這會導致 MySQL 必須掃描并排序 前 2000010 條記錄,然后丟棄前 2000000 條,僅返回后 10 條。因此,隨著 offset 越大,查詢性能會指數級下降,排序和掃描成本非常高。

通過覆蓋索引加子查詢來提高效率

count優化

當數據量大的時候? 使用InnoDB? COUNT(*)查詢是比較耗時的

暫時沒有好的優化,如果非要優化,自己計數

例如使用Redis等內存級別數據庫? 每插入一條數據就+1? ? 刪除一條數據-1? ? 比較繁瑣

COUNT(*)效率最高 可以說約等于 COUNT(1)>COUNT(主鍵ID)>COUNT(其他字段)

update優化

INnoDB? ?三特性? ?事務? ?外鍵? ? 行級鎖

就是說更新時候通過索引更新效率高

?不是“一定”加表鎖,而是“可能”會加表鎖
默認情況下,InnoDB 仍然嘗試加行鎖」,即便沒有命中索引,它會全表掃描并對每一行加行鎖
但在某些情況下,為了提升性能或避免死鎖沖突,可能自動退化成表鎖(鎖升級)。?

視圖/存儲過程/觸發器

視圖

視圖(View)是一種虛擬存在的表.視圖中的數據并不在數據庫中實際存在,行和列數據來自定義視圖的查詢中使用的表,并且是在使用視圖時動態生成的。

視圖(View)本質上就是一個被命名的查詢語句

創建視圖

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


舉例? ?SELECT后面查詢的表稱為基表

CREATE VIEW active_users AS
SELECT id, username FROM users WHERE status = 'active';

?

創建視圖?

CREATE VIEW stu_v_1 AS SELECT id,name FROM student WHERE id<=10;

查詢視圖

查看創建視圖語句:SHOW CREATE VIEW 視圖名稱;

查看視圖數據:SELECT * FROM 視圖名稱 ....;

修改視圖

注意修改視圖加上:OR REPLACE

CREATE OR REPLACE VIEW stu_v_1 AS?
SELECT id, name, age?
FROM student?
WHERE id <= 20;
?

或者

ALTER VIEW?stu_v_1 AS SELECT .......;

刪除視圖

DROP VIEW [IF EXISTS] 視圖名稱1 [,視圖名2];

視圖的創建增刪改查

創建視圖

CREATE OR REPLACE VIEW stu_v_1 AS SELECT id,name FROM student WHERE id<20;

查詢視圖中的數據

SELECT * FROM stu_v_1;

往視圖中插入數據

INSERT INTO stu_v_1 VALUES(6,'Tom');? ?注意插入的數據實際是插入到基表中的

視圖的檢查選項

當創建視圖時候使用WITH CHECK OPTION子句創建時,Mysql會通過視圖檢查正在更改的每個,

新增,更新,刪除是否符合視圖定義.Mysql允許基于另一個視圖創建視圖.會檢查視圖規則

Mysql提供兩個規則選項

1.CASCADED(默認)

例如

CREATE VIEW v1 AS SELECT id,name FROM student WHERE id<20;

如果后面不加

WITH [CASEADED|LOCAL]?CHECK OPTION?

這樣在增刪改時候視圖是不會檢查條件的 條件就是id<20

CREATE VIEW v2 AS SELECT id,name FROM v1 WHERE id<20?WITH CASEADED CHECK OPTION;

如果這樣的話

在增刪改時候視圖會檢查條件,不光檢查V2條件 還會檢查V1的條件,相當于在創建視圖V1時候后面也加了WITH CASEADED CHECK OPTION;

2.LOCAL

WITH LOCAL CHECK OPTION的作用是如果視圖本身有條件需要滿足條件,不會強制父視圖滿足(如果有WITH [CASEADED|LOCAL ] CHECK OPTION需要滿足,沒有拉到)

觸發器

觸發器是與表有關的數據庫對象,在INSERT/UPDATE/DELETE之前或之后,觸發并執行觸發器中定義的SQL語句集合.自動執行一段你提前定義好的 SQL 代碼(這些代碼叫“觸發器體”)。

所謂OLD和NEW 是觸發器給了我們一個定義:用來引用新的數據和老的數據

在Mysql中現在只支持行級觸發器,還不支持語句級觸發器

觸發器的創建查看刪除

不支持修改觸發器,可以先刪除再重新創建

觸發器練習

通過觸發器記錄tb_user表的數據變更日志,將變更日志插入到日志表user_logs中,包含增加修改,刪除

tb_user表上面有記錄

注意:user_logs和tb_user可以在同一個Mysql實例中

跨庫需要 數據庫.表? ? 不同實例間就需要應用代碼層去處理

CREATE TABLE user_logs (
? id INT(11) AUTO_INCREMENT PRIMARY KEY,
? 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;

INSERT觸發器

應該對字段進行NULL判斷,不然只要插入時候有一個是NULL,導致operate_params整個為NULL

CREATE TRIGGER tb_user_insert_trigger
AFTER INSERT ON tb_user
FOR EACH ROW
BEGIN
? INSERT 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;

?修改之后? ?看是否有需要? 如果你在代碼層面已經控制的話就不需要了

CREATE TRIGGER tb_user_insert_trigger
AFTER INSERT ON tb_user
FOR EACH ROW
BEGIN
? INSERT INTO user_logs (id, operation, operate_time, operate_id, operate_params)
? VALUES (
? ? NULL,
? ? 'insert',
? ? NOW(),
? ? NEW.id,
? ? CONCAT(
? ? ? '插入的數據內容為: id=', IFNULL(NEW.id, ''),
? ? ? ', name=', IFNULL(NEW.name, ''),
? ? ? ', phone=', IFNULL(NEW.phone, ''),
? ? ? ', email=', IFNULL(NEW.email, ''),
? ? ? ', profession=', IFNULL(NEW.profession, '')
? ? )
? );
END;
?

查看觸發器指令

SHOW TRIGGERS;

刪除觸發器

DROP TRIGGER?tb_user_insert_trigger;

?

當往tb_user插入數據之后

INSERT INTO tb_user
VALUES (NULL, 'HRUI', '12345', 'HRUI@163.COM', '不務正業', 13, '男', 1, NOW());

UPDATE觸發器?

也需要對NULL的判斷? 這里就不寫了? ?參考INSERT

CREATE TRIGGER tb_user_update_trigger
AFTER UPDATE ON tb_user
FOR EACH ROW
BEGIN
? INSERT INTO user_logs (id, operation, operate_time, operate_id, operate_params)
? VALUES (
? ? NULL,
? ? 'insert',
? ? 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;

DELETE觸發器

CREATE TRIGGER tb_user_delete_trigger
AFTER DELETE ON tb_user
FOR EACH ROW
BEGIN
? INSERT INTO user_logs (id, operation, operate_time, operate_id, operate_params)
? VALUES (
? ? NULL,
? ? 'insert',
? ? NOW(),
? ? OLD.id,? ? ? 注意是OLD.id
? ? CONCAT(
? ? ? '刪除之前的數據: id=', OLD.id,
? ? ? ', name=', OLD.name,
? ? ? ', phone=', OLD.phone,
? ? ? ', email=', OLD.email,
? ? ? ', profession=', OLD.profession
? ? )
? );
END;

鎖是計算機協調多個進程或線程并發訪問某一資源的機制.

在數據庫中,這里的“資源”主要是指 數據本身,也可能包括元數據等。

數據庫除了傳統的資源競爭(比如 CPU、內存、I/O),還會面臨一個更核心的問題:

多個用戶同時訪問或修改相同數據,如何保證數據的一致性與正確性?

在數據庫中,鎖是保障數據一致性的重要機制,也是并發性能調優中必須面對的復雜問題。?

全局鎖

全局鎖是對整個數據庫實例加鎖,加鎖后整個實例進入只讀狀態

  • 所有 DML(如 INSERTUPDATEDELETE)和 DDL(如 ALTERDROP)語句都會被阻塞

  • 已經提交的事務可以讀,但寫入、結構變更都會被鎖住

?FLUSH TABLES WITH READ LOCK?鎖會在連接斷開時自動釋放? ?是會話級別

DML和DDL操作會掛起,只能進行DQL

事務可以開啟但不能提交

掛起可以理解為卡住狀態,當應用程序對數據庫進行增刪改操作時候,應用長時間得不到回應會報錯

通過全局鎖進行數據庫備份

在mysql客戶端 FLUSH TABLES WITH READ LOCK? ? ?對數據庫庫實例加全局鎖

在Windows命令窗口? ?mysqldump [-h120.0.0.1 -P6666]?-uroot -p123456 數據庫名 > D:/db01.sql

在mysql客戶端??UNLOCK TABLES;? 解鎖? ? 加鎖和解鎖可以在同一窗口會話中進行

不加鎖進行備份?

表級鎖

表級鎖,每次操作鎖住整張表.鎖定粒度大,發生沖突概率高,并發度最低.

MyISAM,INNODB,BDB等存儲引擎中可以使用表級鎖

-- 給表加讀鎖(其他連接不能寫)
LOCK TABLES table_name READ;

-- 給表加寫鎖(其他連接不能讀也不能寫)
LOCK TABLES table_name WRITE;
?

解鎖? 注意不用加表名? 并且斷開會話連接會自動解鎖

UNLOCK TABLES;
?

行級鎖

行級鎖,每次操作鎖住對應的行數據.鎖定粒度最小,發生鎖沖突的概率最低,并發度最高.應用在INnoDB引擎中

默認InnoDB核心講解

Mysql管理

Mysql運維篇

日志

主從復制

概述?

主從復制是指將主數據庫的DDL和DML操作通過二進制日志傳到從數據庫服務器中,然后在從庫上對這些日志重新執行(也叫重做),從而使得從庫和主庫的數據保持同步

Mysql支持一臺主庫同時向多臺從庫進行復制,從庫同時也可以做為其他從庫的主庫,實現鏈狀復制

主庫(Master)

從庫(Slave)

Mysql主從復制的優點

1.主庫出現問題,可以快速切換到從庫提供服務(這個可以想想代碼層面如何實現)

2.實現讀寫分離,降低主庫的訪問壓力

3.可以在從庫中執行備份,以避免備份期間影響主庫服務

原理

Mysql主從復制原理是基于二進制日志

SHOW VARIABLES LIKE 'log_bin';
-- 正常情況下返回 Value = ONSHOW VARIABLES LIKE 'binlog_format';
-- 通常返回 ROWSHOW BINARY LOGS;
-- 顯示現有的binlog文件列表

Mysql8中? ?binlog默認開啟? 如果需要顯示關閉(找死)

搭建

1.兩個Mysql實例

2.配置主從

主庫配置

配置完成之后重啟Mysql

systemctl restart mysqld;

主庫創建用于復制的用戶

CREATE USER '用戶名'@'%' IDENTIFIED WITH mysql_native_password BY '密碼';

  • @'%' 表示允許任意主機(即從庫)使用該用戶連接主庫

  • 使用 mysql_native_password 插件認證(兼容性最好)

授權該用戶具備主從復制的權限

GRANT REPLICATION SLAVE ON *.* TO '用戶名'@'%';

  • REPLICATION SLAVE 權限是必須的

  • 它允許該用戶從主庫 讀取 binlog 并進行同步

當然如果你使用root用戶 那么上面這些可以不進行配置? use mysql; select * from user看下root用戶是否允許本機或者所有IP來進行連接? ?Mysql 以 用戶名/主機? 來定義用戶? ?例如可以多個root 但是host不一樣

看下二進制坐標

SHOW master status;

主庫配置就完畢了

從庫配置

注意: read-only=1 針對的普通用戶? 對root用戶就不針對了

如果連超級用戶也要針對? ? 注意 中劃線和下劃線無所謂

# 保證 server-id 唯一
server-id=2

# 普通只讀保護
read_only=1

# 限制超級用戶寫入(更安全)
super_read_only=1
?

重啟從庫

systemctl restart mysqld;

主庫和從庫進行關聯? 在從數據庫執行命令

如果用的是root

CHANGE MASTER TO
? MASTER_HOST='主庫IP',
? MASTER_USER='root',
? MASTER_PASSWORD='你的密碼',
? MASTER_LOG_FILE='xxx',
? MASTER_LOG_POS=xxx;

查看版本

SELECT VERSION();
?

MySQL 8.0.23 及以后(新語法):

CHANGE REPLICATION SOURCE TO
? SOURCE_HOST='xxx.xxx.xxx.xxx',
? SOURCE_USER='用戶名',
? SOURCE_PASSWORD='密碼',
? SOURCE_LOG_FILE='binlog文件名',
? SOURCE_LOG_POS=位置;

MySQL 8.0.22 及以前(舊語法):

CHANGE MASTER TO
? MASTER_HOST='xxx.xxx.xxx.xxx',
? MASTER_USER='用戶名',
? MASTER_PASSWORD='密碼',
? MASTER_LOG_FILE='binlog文件名',
? MASTER_LOG_POS=位置;

SOURCE_LOG_FILE='binlog文件名', ? SOURCE_LOG_POS=位置; 可以在主庫中

SHOW MASTER STATUS;? 來查看

登錄主庫? ?SHOW SLAVE STATUS\G;可以查看從庫狀態? ? ?\G只是一種格式,沒什么特別含義

在主庫進行建表? ? 增刪改? ?都會在從庫同步

分庫分表

讀寫分離

介紹

讀寫分離是基于主從復制的? ? 都可以了解下

就是把對數據的讀和寫操作分開.減輕單臺數據庫的壓力

通過MyCat即可輕易實現,MyCat不僅支持Mysql,也支持Oracle和SQL Server

注意:Spring Boot + dynamic-datasource 的讀寫分離是代碼層面控制讀寫分離?屬于應用層的讀寫分離

MyCat是通過中間件層實現的,Spring Boot 的應用只連接一個邏輯數據源(Mycat),它背后再去路由主從數據庫。屬于中間件層的讀寫分離??端口 8066?

一主一從

一主一從的搭建上面已經有相關介紹

登錄主庫

SHOW SLAVE status\G;? 查看從庫狀態

一主一從讀寫分離

雙主雙從

雙主雙從讀寫分離

本文來自互聯網用戶投稿,該文觀點僅代表作者本人,不代表本站立場。本站僅提供信息存儲空間服務,不擁有所有權,不承擔相關法律責任。
如若轉載,請注明出處:http://www.pswp.cn/pingmian/76198.shtml
繁體地址,請注明出處:http://hk.pswp.cn/pingmian/76198.shtml
英文地址,請注明出處:http://en.pswp.cn/pingmian/76198.shtml

如若內容造成侵權/違法違規/事實不符,請聯系多彩編程網進行投訴反饋email:809451989@qq.com,一經查實,立即刪除!

相關文章

[IOI 1994] 數字三角形 Number Triangles

題目鏈接 思路&#xff08;上到下&#xff09;&#xff1a; ①從上往下遞推&#xff1a; f[i][j] max(f[i-1][j] g[i][j], f[i-1][j-1]g[i][j]) ②對最后一層&#xff0c;遍歷一下&#xff0c;找到最大的答案。 代碼&#xff08;上到下&#xff09;&#xff1a; #inclu…

基于Qt的串口通信工具

程序介紹 該程序是一個基于Qt的串口通信工具&#xff0c;專用于ESP8266 WiFi模塊的AT指令配置與調試。主要功能包括&#xff1a; 1. 核心功能 串口通信&#xff1a;支持串口開關、參數配置&#xff08;波特率、數據位、停止位、校驗位&#xff09;及數據收發。 AT指令操作&a…

第5篇:Linux程序訪問控制FPGA端LEDR<三>

Q&#xff1a;如何具體設計.c程序代碼訪問控制FPGA端外設&#xff1f; A&#xff1a;以控制DE1-SoC開發板的LEDR為例的Linux .C程序代碼。頭文件fcntl.h和sys/mman.h用于使用/dev/mem文件&#xff0c;以及mmap和munmap內核函數&#xff1b;address_map_arm.h指定了DE1-SoC_Com…

【學生管理系統升級版】

學生管理系統升級版 需求分析&#xff1a;注冊功能:登錄功能&#xff1a;驗證碼規則&#xff1a;忘記密碼&#xff1a; 實操&#xff1a;系統主頁面注冊功能登錄功能忘記密碼效果演示 需求 為學生管理系統書寫一個登陸、注冊、忘記密碼的功能。 ? ? 只有用戶登錄成功之后&…

CSS Grid布局:從入門到放棄再到真香

Flexbox 與 Grid 布局&#xff1a;基礎概念與特點 Flexbox Flexbox&#xff08;Flexible Box Layout&#xff09;&#xff0c;即彈性盒布局模型&#xff0c;主要用于創建一維布局&#xff0c;能夠輕松實現元素在一行或一列中的排列、對齊與分布。通過display: flex屬性啟用 Fl…

C++怎么調用類中的函數

1. 棧上對象 調用普通成員方法 普通成員方法需要通過類的對象實例&#xff08;或指針、引用&#xff09;來調用。 示例&#xff1a; class MyClass { public:void normalMethod() {std::cout << "普通成員方法被調用" << std::endl;} };int main() {M…

go游戲后端開發31:麻將游戲的碰牌與胡牌邏輯

以下是潤色后的版本&#xff1a; 1. 碰牌邏輯 1.1 觸發碰牌 當一個玩家棄牌后&#xff0c;其他玩家可以選擇碰牌。如果當前玩家決定碰牌&#xff0c;系統需要通知所有玩家這一操作。碰牌操作完成后&#xff0c;當前玩家需要出一張牌&#xff0c;系統同樣需要通知所有玩家。 …

十分鐘機器學習之--------------線性回歸

線性回歸&#xff08;linear regression&#xff09;是一種基于數學模型的算法&#xff0c;首先假設數據集與標簽之間存在線性關系&#xff0c;然后簡歷線性模型求解參數。在實際生活中&#xff0c;線性回歸算法因為其簡單容易計算&#xff0c;在統計學經濟學等領域都有廣泛的應…

學透Spring Boot — 017. 處理靜態文件

這是我的《學透Spring Boot》專欄的第17篇文章&#xff0c;了解更多內容請移步我的專欄&#xff1a; Postnull CSDN 學透 Spring Boot 目錄 靜態文件 靜態文件的默認位置 通過配置文件配置路徑 通過代碼配置路徑 靜態文件的自動配置 總結 靜態文件 以前的傳統MVC的項目…

深入理解 JavaScript 數組查找:如何高效獲取特定元素

深入理解 JavaScript 數組查找&#xff1a;如何高效獲取特定元素 深入理解 JavaScript 數組查找&#xff1a;如何高效獲取特定元素引言問題場景解決方案1. 使用 Array.prototype.find()2. 處理 Proxy 對象的情況3. 備選方案&#xff1a;Array.prototype.filter()4. 傳統 for 循…

HTML5+CSS3小實例:純CSS繪制七巧板

實例:純CSS繪制七巧板 技術棧:HTML+CSS 效果: 源碼: 【HTML】 <!DOCTYPE html> <html lang="zh-CN"> <head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale…

[electron]自動注冊IPC的解決方案

前言 主進程和渲染進程通過IPC進行通信&#xff0c;每次需要定義名稱并注冊&#xff0c;很多代碼都是重復書寫&#xff0c;并且如果主進程和渲染進程開發人員是同一個的話&#xff0c;很多東西都可以簡化。 渲染進程通過ipcRenderer.invoke與主進程通信&#xff0c;主進程通過i…

JS—防抖和節流:1分鐘掌握防抖和節流

個人博客&#xff1a;haichenyi.com。感謝關注 一. 目錄 一–目錄二–防抖三–節流四–進階應用五–總結 二. 防抖&#xff08;Debounce&#xff09; 防抖&#xff08;Debebounce&#xff09;和節流&#xff08;Throttle&#xff09;都是前端開發中用于優化高頻事件性能的兩…

測試模板1

本篇技術博文摘要 &#x1f31f; 引言 &#x1f4d8; 在這個變幻莫測、快速發展的技術時代&#xff0c;與時俱進是每個IT工程師的必修課。我是盛透側視攻城獅&#xff0c;一名什么都會一丟丟的網絡安全工程師&#xff0c;也是眾多技術社區的活躍成員以及多家大廠官方認可人員&a…

Nginx配置Http響應頭安全策略,未設置X-Content-Type-Options響應頭【原理掃描】

文章目錄 前言一、漏洞掃描問題二、漏洞描述三、解決方法3.1、Nginx配置概覽3.2、注意事項 四、感謝 前言 第三方安全檢測機構甩過來一篇漏洞掃描報告&#xff0c;需要我們整改。 一、漏洞掃描問題 漏洞掃描問題如下&#xff1a; 未設置X-Content-Type-Options響應頭【原理掃…

Gerapy二次開發:用戶管理專欄新增與編輯頁面開發

用戶管理專欄新增與編輯頁面開發 寫在前面Vue表單設計與開發Vue的this.$refs功能實現前端Create.vueEdit.vueSubstance.vue效果預覽后端urls.pyviews.py整體效果預覽新增編輯總結歡迎加入Gerapy二次開發教程專欄! 本專欄專為新手開發者精心策劃了一系列內容,旨在引領你深入探…

HOW - 實現 useClickOutside 或者 useClickAway

場景 在開發過程中經常遇到需要點擊除某div范圍之外的區域觸發回調&#xff1a;比如點擊 dialog 外部區域關閉。 手動實現 import { useEffect } from "react"/*** A custom hook to detect clicks outside a specified element.* param ref - A React ref object…

SpringBoot整合sa-token,Redis:解決重啟項目丟失登錄態問題

SpringBoot整合sa-token&#xff0c;Redis&#xff1a;解決重啟項目丟失登錄態問題 &#x1f525;1. 痛點直擊&#xff1a;為什么登錄狀態會消失&#xff1f;2.實現方案2.1.導入依賴2.2.新增yml配置文件 3.效果圖4.結語 &#x1f600;大家好&#xff01;我是向陽&#x1f31e;&…

Redis 持久化+性能管理+緩存

目錄 一.Redis 持久化 1.持久化概述 2.持久化分類 3.RDB和AOF持久化 1.RDB持久化 2.RDB觸發條件 &#xff08;1&#xff09;手動觸發 &#xff08;2&#xff09;自動觸發 &#xff08;3&#xff09; 執行流程? &#xff08;4&#xff09;啟動時加載 3.AOF持久化 &…

進程間通訊(IPC)

進程間通訊&#xff08;IPC&#xff09;詳解&#xff1a;Linux 中的幾種實現方式 在計算機操作系統中&#xff0c;進程間通訊&#xff08;IPC, Inter-Process Communication&#xff09;是一個至關重要的概念&#xff0c;尤其是在多進程操作系統中&#xff0c;進程間需要通過一…