MySQL

什么是數(shù)據(jù)庫、數(shù)據(jù)庫管理系統(tǒng)、SQL？

數(shù)據(jù)庫（DataBase），簡稱 DB: 存儲數(shù)據(jù)的倉庫，具有特定格式的數(shù)據(jù)文件。
數(shù)據(jù)庫管理系統(tǒng)（DataBaseManagement），簡稱 DBMS：管理數(shù)據(jù)庫的系統(tǒng)。
- MsSQL
- Oracle
- MS sqlServer
SQL：結構化查詢語言，管理數(shù)據(jù)庫的一套標準。
- SQL的分類：
  - 數(shù)據(jù)查詢語言（DQL-Data Query Language）： -- select
  - 數(shù)據(jù)操縱語言（DML-Data Manipulation Language） -- insert、delete、update
  - 數(shù)據(jù)定義語言（DDL-Data Definition Language） -- create、drop、alter
  - 事務控制語言（TCL-Transactional Control Language） -- commit、rollback
  - 數(shù)據(jù)控制語言（DCL-Data Control Language） -- grant、revoke
表（table）：數(shù)據(jù)庫以表的形式存儲數(shù)據(jù)。
- 行：每行（row）為一條數(shù)據(jù)/記錄。
- 列：每列（column）被稱為字段——每個字段有獨自的名、數(shù)據(jù)類型、約束等屬性。

MySQL 查詢

單表查詢

執(zhí)行順序： from => join => where => group by => having => select => order by => limit

# 查詢?nèi)浚实停勺x性差。
select * from dept;

# 查詢部分字段，及字段別名、運算
select dept_id id,dept_name as name，sal * 12 as sal_year from dept;

# 復雜查詢
# 執(zhí)行順序： from => join => where => group by => having => select => order by => limit
# 分頁公式：limit (currPageNo -1) * pageSize , pageSize
select 
	job,avg(sal) as avgSal
from emp 
where job <> 'MANAGER' 			-- 查詢條件
group by job					-- 分組
having avg(sal) > 2000			-- 過濾條件：過濾分組的數(shù)據(jù)、必須和group by 一起使用
order by avgSal desc			-- 排序：desc 倒序、asc 升序
limit 0 10;						-- 分頁

# having 
# 優(yōu)先使用 where ，如果Where 不能，再用having ,
# 		比如：where 無法過濾 平均薪資大于2000的部門，
# 			where avg(sal) > 2000 
#		以上語句是錯誤的，由sql執(zhí)行語句所決定，where 拿不到分組數(shù)據(jù)，因為分組在where后邊執(zhí)行
# 顯示每個部門的平均工資、并且平均工資大于2000；
select deptno,avg(sal) 
from 
	dept
group by 
	deptno
having
	avg(sal) > 2000;

常用函數(shù)

# 單行函數(shù)

lower							# 轉(zhuǎn)換小寫
upper							# 轉(zhuǎn)換大寫
substr							# 截取字符串
concat							# 拼接字符串
length							# 長度
trim							# 去空格
str_to_date 					# 字符串轉(zhuǎn)為日期
date_format						# 格式化日期
format							# 設置數(shù)字千分位
round							# 四舍五入
rand()							# 隨機數(shù)
ifnull							# 將Null轉(zhuǎn)為具體值
case...when..then..else..end	# if else

#偏移
lag								# 上偏移
lead							# 下偏移

# 分組函數(shù)/聚合函數(shù)，默認整張表為一組。
count							# 計數(shù)：* 記錄總數(shù)，具體字段（不為Null的總數(shù)）
sum								# 求和
avg								# 平均值
max								# 最大值
min								# 最小值

# 排序函數(shù)
rank()							# 跳躍排名：按分數(shù)排名時候，都是99分時并列第一名，第二名不存在，直接跳到第三名
row_number()					# 排序
dense_rank()					# 連續(xù)排名：按分數(shù)排名時候，都是99分時并列第一名，第二名依然存在		
ntile()							# 分區(qū)后排序：按指定數(shù)量進行分區(qū)

# 窗口函數(shù) over 開窗函數(shù)必須與聚合函數(shù)或排序函數(shù)一起使用。

## over + 聚合函數(shù)
SELECT *,
    COUNT(*) OVER(PARTITION BY Groupname) 每個組的個數(shù), 
    COUNT(*) OVER(PARTITION BY Groupname ORDER BY ID) 每個組的累積個數(shù), 
    COUNT(*) OVER(ORDER BY ID) 累積個數(shù),
    COUNT(*) OVER() 總個數(shù)
from Employee;


## over + 排序函數(shù)
SELECT *,
    ROW_NUMBER() OVER(ORDER BY SCORE DESC) AS 總排序,											# 總排序
    ROW_NUMBER() OVER(PARTITION BY ClassName ORDER BY SCORE DESC) 班內(nèi)排序,						# 班內(nèi)排序
    RANK() OVER(ORDER BY SCORE DESC) AS 跳躍排序,												# 跳躍排序
    DENSE_RANK() OVER(ORDER BY SCORE DESC) AS 連續(xù)排序,											# 連續(xù)排序
    NTILE(3) OVER(ORDER BY SCORE DESC) AS 分區(qū)后排序											    # 分成3個區(qū)后進行排序
FROM Scores;					

# 去重
distinct

子查詢

# where 中 子查詢
select 
	ef.ename,ef.sal 
from emp ef 
where ef.sal > (select min(es.sal) from emp es);

# from 中 子查詢，可以當作臨時表
select 
	t.*,s.grade
from
	(select es.job,avg(es.sal) as avgsal from emp es group by es.job) t
join salgrad s on t.avgsal between s.losal and s.hisal;

# select 中 子查詢
select 
	e.ename,(select d.dname from dept d where e.deptno = e.deptno)
from 
	emp e;

分組

# 多個分組: 所有數(shù)據(jù)一組 和 不同類型一組
select 
       case grouping(m.msg_type_id) when 1 then 'ALL' else m.msg_type_id end as msg_type_id,
       count(m.msg_type_id) as Counts
  from wmsg.message m
 where m.msg_type_id in ('MR_QC', 'CRITICAL_VALUE')
 group by rollup(m.msg_type_id);

# 即以下語句

select 
	m.msg_type_id,
	count(m.msg_type_id) as Counts 
from wmsg.message m 
group by (m.msg_type_id)

union all

select 
	null,
	count(*) as Counts 
from wmsg.message m

連接查詢

匹配次數(shù) = 笛卡爾積

內(nèi)連接

等值連接：等值條件

# sql 92 語法
select 
	e.ename,d.danme
from
	emp e,dept d
where 
	e.deptno = d.deptno;

# sql 99 語法
select 
	e.ename,d.danme
from
	emp e
   	inner join dept d on e.deptno = d.deptno;

非等值連接：條件不等
自連接：同一張表變成2張表使用。

select 
	e.ename,d.danme
from
	emp e
   	inner join emp d on e.deptno = d.deptno;

外連接

左連接：以join關鍵字的左邊的表為主表

select 
	e.ename,d.danme
from
	emp e
   	left join dept d on e.deptno = d.deptno;

右連接：以join關鍵字的右邊的表為主表

select 
	e.ename,d.danme
from
	emp e
   	right join dept d on e.deptno = d.deptno;

合并查詢

部分情況：比連接查詢，效率高——連接查詢匹配次數(shù)是笛卡爾積，合并查詢是相加。

select ename from emp e where e.deptno = 'one'
union
select ename from emp d where d.deptno = 'two'

MySQL 常用命令

# 注意：數(shù)據(jù)庫中字符串，以單引號為標準，oracle 不能用雙引號。

# 登錄數(shù)據(jù)庫
mysql -uroot -p1234556

# 查看數(shù)據(jù)庫
show databases;

# 查看數(shù)據(jù)庫版本
select version();

# 創(chuàng)建數(shù)據(jù)庫
create database soul_cloud;

# 使用具體數(shù)據(jù)庫
use soul_test;

# 查看表
show tables;
# 查看表結構
desc dept;

# 退出數(shù)據(jù)庫
exit

# 導出
mysqldump -uroot -p1q2w3e4r 數(shù)據(jù)庫名 > gt_shop_new.sql
# 如果沒有該命令
# 1) 查找：結果作為第二步入?yún)?find / -name mysqldump
# 2）建立軟鏈接
ln -s /usr/local/mysql/bin/mysqldump  /usr/bin

# 導入
source /usr/database.sql;

windows 指令

# 啟動 mysql
net start mysql

# 停止 mysql
net stop mysql

MySQL 安裝

Linux 安裝

# 1）卸載原有的 MySql 
rpm -qa | grep mysql
yum remove xxx

# 2）刪除相關配置
find / -name mysql
rm -rf /xx/mysql

# 3）上傳下載好的 Mysql，解壓、并重命名、創(chuàng)建數(shù)據(jù)目錄
tar -zxvf mysql-8.0.17-el7-x86_64.tar.gz -C /usr/local/
mv mysql-8.0.17-el7-x86_64 mysql
mkdir /usr/local/mysql/data

# 4）添加mysql用戶組及用戶，查看、設置權限
groupadd mysql
useradd -r -g mysql mysql
groups mysql
chown -R mysql:mysql /usr/local/mysql
chmod -R 755 /usr/local/mysql

# 5）MySql 配置文件
vim /etc/my.cnf

# ------------------------ 配置 start -------------------------------
[client]
port=3306
# mysql socket 文件存放地址 
socket=/tmp/mysql.sock
# 默認字符集
default-character-set=utf8
[mysqld]
#忽略大小寫
lower_case_table_names=1
server-id=1
# 端口
port=3306
# 運行用戶
user=mysql
# 最大連接
max_connections=200
socket=/tmp/mysql.sock
# mysql 安裝目錄（解壓后文件的目錄）
basedir=/usr/local/mysql
# 數(shù)據(jù)目錄
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysql.pid
init-connect='SET NAMES utf8'
character-set-server=utf8
# 數(shù)據(jù)庫引擎
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password
log_error=/usr/local/mysql/data/mysql-error.log
slow_query_log_file=/usr/local/mysql/data/mysql-slow.log
# 跳過驗證密碼
#skip-grant-tables
[mysqldump]
quick
max_allowed_packet=16Mssssssss
# ------------------------ 配置 end -------------------------------


# 6）初始化參數(shù)，注意生成的密碼，如果沒顯示，可在日志查看。
cd /usr/local/mysql/bin
./mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data  --initialize;

# 6.1）可選——初始化時可能需要以下配置
rpm -qa|grep libaio
yum install libaio-devel.x86_64
yum -y install numactl

# 7）初始化成功后
mysql -uroot -p自動生成的密碼

# 7.1）進入 MySql 的操作：創(chuàng)建root記錄，修改加密規(guī)則（使Navicat可登錄）、并更新
CREATE USER 'root'@'%' IDENTIFIED BY 'whWl1122..';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'whWl1122..';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION ;
flush privileges;

# 8）設置防火墻，開放3306端口，并刷新：同時服務器安全組也要開啟3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

Linux 自動啟動、環(huán)境變量、服務管理

# 復制mysql.server 到Linux 服務注冊目錄
cd /usr/local/mysql
cp -a ./support-files/mysql.server /etc/init.d/mysql

# 分配權限、并添加
chmod +x /etc/init.d/mysql
chkconfig --add mysql
chkconfig --list mysql
# 添加環(huán)境變量
vim /etc/profile
#-------------Mysql 環(huán)境變量 start --------------------------
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin
#-------------Mysql 環(huán)境變量 end --------------------------
# 刷新系統(tǒng)環(huán)境配置
source /etc/profile

# 配置環(huán)境變量：啟動、停止、狀態(tài)
service mysql start
service mysql stop
service mysql status

# 啟動、停止、重啟、狀態(tài)
service mysqld start
service mysqld stop
service mysqld restart
service mysqld status

# 非開箱即用的方式：啟動、停止。
./bin/mysqld_safe &
./bin/mysqladmin -uroot -p shutdown

Linux 主從配置

# 系統(tǒng)平臺一致，數(shù)據(jù)庫版本一致，同步的數(shù)據(jù)庫數(shù)據(jù)一致

# 1）添加 主從配置：主數(shù)據(jù)庫的參數(shù)
vim /etc/my.cnf

# ---------------------- 主從配置：主數(shù)據(jù)庫 start -----------------------
[mysqld]
#開啟二進制日志
log_bin = /usr/local/mysql/data/mysql-bin.log 
#設置server-id，必須唯一
server-id=91
#同步的數(shù)據(jù)庫名稱
binlog_do_db = test 
#忽略同步的數(shù)據(jù)庫
binlog_ignore_db = mysql
# ---------------------- 主從配置：主數(shù)據(jù)庫  end ----------------------------

# 2）進入主數(shù)據(jù)庫mysql配置同步的賬號
mysql -uroot -pwhWL1122..

CREATE USER 'repl_db'@'192.168.1.11' IDENTIFIED BY 'hzQ1122soul..';
GRANT REPLICATION SLAVE ON *.* TO 'repl_db'@'192.168.1.11';
flush privileges;
show master status;

# 3從數(shù)據(jù)庫(192.168.1.11)配置

vim /etc/my.cnf
# ---------------------- 主從配置：從數(shù)據(jù)庫 start -----------------------
[mysqld]
#開啟二進制日志
log_bin = /usr/local/mysql/data/mysql-bin.log 
#設置server-id，必須唯一
server-id=11
#同步的數(shù)據(jù)庫名稱
replicate_do_db = test 
#忽略同步的數(shù)據(jù)庫
replicate_ignore_db = mysql 
# ---------------------- 主從配置：從數(shù)據(jù)庫 end---------------------------

# 4）進入從數(shù)據(jù)庫mysql配置連接
mysql -uroot -p
CHANGE MASTER TO MASTER_HOST='192.168.1.91',MASTER_USER='repl_db',MASTER_PASSWORD='hzQ1122soul..',
MASTER_LOG_FILE='binlog.000007',MASTER_LOG_POS=155;
# 參數(shù)說明
# MASTER_HOST：主數(shù)據(jù)庫的ip地址
# ASTER_USER：主數(shù)據(jù)庫配置同步的賬號
# ASTER_PASSWORD：主數(shù)據(jù)庫配置同步賬號的密碼
# ASTER_LOG_FILE：在前面“show master status”的File的數(shù)據(jù)
# ASTER_LOG_POS：在前面“show master status”的Position的數(shù)據(jù)

MySQL 知識點

數(shù)據(jù)類型

char								# 固定長度的字符串，分配固定長度
varchar								# 可變長度的字符串、可根據(jù)實際數(shù)據(jù)動態(tài)分配空間
int									# 整數(shù)型
bigint								# 長整數(shù)型
float								# 單精度浮點數(shù)
double								# 雙精度浮點數(shù)
date								# 日期
datetime							# 長日期
clob								# 字符串大對象
blob								# 二進制大對象	：圖片、聲音、視頻

DDL

# 創(chuàng)建表
create table 表名(
	字段1 數(shù)據(jù)類型,
   	字段1 數(shù)據(jù)類型
);

# 刪除表結構
drop table t_student;

# 刪除表物理刪除 : 效率較高
truncate table dept_back;

# 導入
source /usr/local/data/dept.sql;

# 復制表
create table emp2 as select * from emp;

DML

# 添加數(shù)據(jù)
insert into t_student(字段1，字段2) values(值1，值2);

# 修改
update t_student set idnumber = '2012' where id = '1'

# 遷移表數(shù)據(jù)（表結構相同）
insert into dept_bak select * from dept;

# 刪除表數(shù)據(jù) : 效率較低
detele from dept;

表約束

非空約束：not null
唯一約束：unique
主鍵約束：primary key（PK）
外鍵約束：foreign key （FK）

檢查約束：check —— mysql 不支持、oracle 支持

create table dept(
	id int primary key,								-- 主鍵約束（列級）任何表都有主鍵
   	name varchar(255) not null,					-- 非空約束（只有 列 約束）
    id_number varchar(32) unique,					-- 唯一主鍵（mysql中設置 為 not null & unique  自動變?yōu)?主鍵約束，orale 不一樣）
    primary key(id),								-- 主鍵約束（表級）
    primary key(id,name),							-- 主鍵約束（復合主鍵）
    foreign key(cno) references t_class(classno)	-- 外鍵約束，必須具有 唯一性
);

存儲引擎

一個表存儲/組織數(shù)據(jù)的方式，MySQL有九個存儲引擎，默認存儲引擎是InnoDB，編碼方式是UTF-8。

對比項	MyISAM	InnoDB
主外鍵	不支持	支持
事務	不支持	支持
行表鎖	表鎖，不適合高并發(fā)	行鎖，適合高并發(fā)
緩存	緩存索引	緩存索引、數(shù)據(jù)
表空間	小	大
關注點	性能	事務
默認安裝	Y	Y
存儲	索引和數(shù)據(jù)分開	索引和數(shù)據(jù)不分開
應用場景	大量查詢

MyISAM：支持全文檢索、壓縮、空間函數(shù)。
InnoDB：基于聚族索引建立，以MVCC支持高并發(fā)。

事務

概念：訪問數(shù)據(jù)庫的一個操作序列，數(shù)據(jù)庫應用系統(tǒng)通過事務集來完成對數(shù)據(jù)庫的存取，事務必須服從ISO/IEC所制定的ACID原則。
四大特性：
- 原子性：事務最小的工作單元，不可再分。
- 一致性：同一個事務中，所有操作要么全部成功，要么全部失敗，以保證數(shù)據(jù)的一致性。
- 隔離性：事務之間具有一定的隔離性。
- 持久性：事務的操作持久到硬盤上，對事務的操作得以保障。

隔離級別：
- 讀未提交(read uncommitted)：事務A可以讀取事務B未提交的數(shù)據(jù)，存在臟讀。
- 讀已提交(read committed)：事務A只能讀取事務B提交之后的數(shù)據(jù)，避免臟讀，存在不可重復讀。
- 可重復讀(repeatable read)：事務A每次讀取的數(shù)據(jù)一致，避免不可重復讀，存在幻讀。
- 可串行化(serializable)：事務排隊，不能并發(fā)執(zhí)行。
并發(fā)問題：
- 丟失更新：一個事務的更新覆蓋了另一個事務的更新。
- 臟讀：事務A讀取了事務B未提交的數(shù)據(jù)。
- 不可重復讀：同一個事務，第一次讀取和第二次讀取的數(shù)據(jù)不一致。
- 幻讀：事務提交后，也讀不到最新的，依舊是讀剛開始事務時的數(shù)據(jù)。

# 事務
start transaction;									-- 開啟事務
insert into dept values('10','22');
update dept set name = '高一二班' where id = '10';
rollback;											-- 回滾事務
commit;												-- 提交事務

# 查看隔離級別
select @@tx_isolation;

# 設置全局事務的隔離級別：讀未提交
set global transaction isolation level read uncommitted;

索引

縮小搜索范圍、避免全表掃描，提高查詢的效率，mysql中PK、唯一約束的列都會自動設置為索引。
數(shù)據(jù)結構：B+樹 + Hash
索引分類
- 單一索引
- 復合索引
- 主鍵索引
- 唯一索引
注意：唯一性不強的字段上添加索引用處不大。

# 創(chuàng)建索引
create index emp_ename_index on emp(ename);
# 刪除索引
drop index emp_ename_index on emp;
# 查看sql是否使用索引檢索
explain select * from emp where ename = 'KING';

# 索引失效

# 索引失效一： 模糊查詢
explain select * from emp where ename like '%KING';

# 索引失效二： or條件，其一沒有建索引都會導致索引失效
explain select * from emp where ename = 'KING' or job = 'TEACHER';

# 索引失效三： 復合索引，沒有使用左側(cè)的列進行查詢會導致索引失效
# 復合索引
create index emp_ename_and_job_index on emp(ename,job);
explain select * from emp where job = 'TEACHER';

# 索引失效四：where 中 索引列參與運算

create index emp_sal_index on emp(sal);
explain select * from emp where sal + 1 = 2;

# 索引失效五：where 中 索引列使用了函數(shù)
explain select * from emp where lower(ename) = 'emith';

為什么MySql以B+樹作為索引的數(shù)據(jù)結構？

由于實際應用大多數(shù)以范圍查找為主，

Hash結構不適合范圍查找，排序，內(nèi)存耗費大；

紅黑樹（平衡二叉樹）、B樹隨著樹的深度越深導致I/O次數(shù)變多影響查詢效率，排序存在回旋問題。

? B+樹是基于B樹的基礎，降低了樹的深度從而減少了I/O次數(shù)，并提高了區(qū)間范圍。

視圖

# 創(chuàng)建或替換視圖
create or replace view vdept as select * from dept;

# 刪除視圖
drop view vdept;

變量

# 系統(tǒng)變量：全局變量
show global|seesion variables like '%ch%'
set global|seesion variables = te;

# 系統(tǒng)變量：會話變量
show seesion variables like '%cha%';
set session tx_tt = 'aba';

存儲過程

# 定義存儲過程
# in  入?yún)?# out 出參
# inout 入出參
create procedure update_visit(in accountId varchar(50),out curr_diag_desc varchar(60),inout result int)
begin
	
    -- 定義局部變量
    declare curr_pat varchar(50) default '';
	
end $

-- 變量
set @curr_pat_id;

# 調(diào)用存儲過程
call update_visit("2022-080520","鼻咽",0);

# 刪除存過過程
drop procedure update_visit;

# 查看存儲過程
show create procedure update_order;

-- 流程控制
-- 條件函數(shù)
if i > 20 then j = 5
end if;

-- case 
case 
when 條件 then
else
end

-- 循環(huán)

while(i<50) do

end while;

三范式

第一范式：任何表都必須有主鍵，每個字段的原子性不可再分。
第二范式：基于第一范式，要求所有非主鍵字段完全依賴主鍵，不要產(chǎn)生部分依賴。
第三范式：基于第二范式，要求所有非主鍵字段直接依賴主鍵，不要產(chǎn)生傳遞依賴。
口訣：
- 一對一，外鍵唯一。
- 一對多，兩張表，多的表加外鍵。
- 多對多，三張表，關系表兩個外鍵。

行表鎖

行級鎖：開銷小，鎖定粒度大，死鎖概率高，適合高并發(fā)。
表級鎖：開銷大，鎖定粒度小，死鎖概率低，不適合高并發(fā)。

posted @ 2023-06-08 01:20 Soul-Q 閱讀(28) 評論(0) 收藏舉報

刷新頁面返回頂部

Soul-Q

MySQL

MySQL

什么是數(shù)據(jù)庫、數(shù)據(jù)庫管理系統(tǒng)、SQL？

MySQL 查詢

單表查詢

常用函數(shù)

子查詢

分組

連接查詢

內(nèi)連接

外連接

合并查詢

MySQL 常用命令

MySQL 安裝

Linux 安裝

Linux 自動啟動、環(huán)境變量、服務管理

Linux 主從配置

MySQL 知識點

數(shù)據(jù)類型

DDL

DML

表約束

存儲引擎

事務

索引

視圖

變量

存儲過程

三范式

行表鎖

公告