MySQL

什么是数据库、数据库管理系统、SQL？

数据库（DataBase），简称 DB: 存储数据的仓库，具有特定格式的数据文件。
数据库管理系统（DataBaseManagement），简称 DBMS：管理数据库的系统。
- MsSQL
- Oracle
- MS sqlServer
SQL：结构化查询语言，管理数据库的一套标准。
- SQL的分类：
  - 数据查询语言（DQL-Data Query Language）： -- select
  - 数据操纵语言（DML-Data Manipulation Language） -- insert、delete、update
  - 数据定义语言（DDL-Data Definition Language） -- create、drop、alter
  - 事务控制语言（TCL-Transactional Control Language） -- commit、rollback
  - 数据控制语言（DCL-Data Control Language） -- grant、revoke
表（table）：数据库以表的形式存储数据。
- 行：每行（row）为一条数据/记录。
- 列：每列（column）被称为字段——每个字段有独自的名、数据类型、约束等属性。

MySQL 查询

单表查询

执行顺序： from => join => where => group by => having => select => order by => limit

# 查询全部，效率低，可读性差。
select * from dept;

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

# 复杂查询
# 执行顺序： 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			-- 过滤条件：过滤分组的数据、必须和group by 一起使用
order by avgSal desc			-- 排序：desc 倒序、asc 升序
limit 0 10;						-- 分页

# having 
# 优先使用 where ，如果Where 不能，再用having ,
# 		比如：where 无法过滤 平均薪资大于2000的部门，
# 			where avg(sal) > 2000 
#		以上语句是错误的，由sql执行语句所决定，where 拿不到分组数据，因为分组在where后边执行
# 显示每个部门的平均工资、并且平均工资大于2000；
select deptno,avg(sal) 
from 
	dept
group by 
	deptno
having
	avg(sal) > 2000;

常用函数

# 单行函数

lower							# 转换小写
upper							# 转换大写
substr							# 截取字符串
concat							# 拼接字符串
length							# 长度
trim							# 去空格
str_to_date 					# 字符串转为日期
date_format						# 格式化日期
format							# 设置数字千分位
round							# 四舍五入
rand()							# 随机数
ifnull							# 将Null转为具体值
case...when..then..else..end	# if else

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

# 分组函数/聚合函数，默认整张表为一组。
count							# 计数：* 记录总数，具体字段（不为Null的总数）
sum								# 求和
avg								# 平均值
max								# 最大值
min								# 最小值

# 排序函数
rank()							# 跳跃排名：按分数排名时候，都是99分时并列第一名，第二名不存在，直接跳到第三名
row_number()					# 排序
dense_rank()					# 连续排名：按分数排名时候，都是99分时并列第一名，第二名依然存在		
ntile()							# 分区后排序：按指定数量进行分区

# 窗口函数 over 开窗函数必须与聚合函数或排序函数一起使用。

## over + 聚合函数
SELECT *,
    COUNT(*) OVER(PARTITION BY Groupname) 每个组的个数, 
    COUNT(*) OVER(PARTITION BY Groupname ORDER BY ID) 每个组的累积个数, 
    COUNT(*) OVER(ORDER BY ID) 累积个数,
    COUNT(*) OVER() 总个数
from Employee;


## over + 排序函数
SELECT *,
    ROW_NUMBER() OVER(ORDER BY SCORE DESC) AS 总排序,											# 总排序
    ROW_NUMBER() OVER(PARTITION BY ClassName ORDER BY SCORE DESC) 班内排序,						# 班内排序
    RANK() OVER(ORDER BY SCORE DESC) AS 跳跃排序,												# 跳跃排序
    DENSE_RANK() OVER(ORDER BY SCORE DESC) AS 连续排序,											# 连续排序
    NTILE(3) OVER(ORDER BY SCORE DESC) AS 分区后排序											    # 分成3个区后进行排序
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;

分组

# 多个分组: 所有数据一组 和 不同类型一组
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

连接查询

匹配次数 = 笛卡尔积

内连接

等值连接：等值条件

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

合并查询

部分情况：比连接查询，效率高——连接查询匹配次数是笛卡尔积，合并查询是相加。

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

MySQL 常用命令

# 注意：数据库中字符串，以单引号为标准，oracle 不能用双引号。

# 登录数据库
mysql -uroot -p1234556

# 查看数据库
show databases;

# 查看数据库版本
select version();

# 创建数据库
create database soul_cloud;

# 使用具体数据库
use soul_test;

# 查看表
show tables;
# 查看表结构
desc dept;

# 退出数据库
exit

# 导出
mysqldump -uroot -p1q2w3e4r 数据库名 > gt_shop_new.sql
# 如果没有该命令
# 1) 查找：结果作为第二步入参
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，解压、并重命名、创建数据目录
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
# 数据目录
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysql.pid
init-connect='SET NAMES utf8'
character-set-server=utf8
# 数据库引擎
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）初始化参数，注意生成的密码，如果没显示，可在日志查看。
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 的操作：创建root记录，修改加密规则（使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 自动启动、环境变量、服务管理

# 复制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
# 添加环境变量
vim /etc/profile
#-------------Mysql 环境变量 start --------------------------
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin
#-------------Mysql 环境变量 end --------------------------
# 刷新系统环境配置
source /etc/profile

# 配置环境变量：启动、停止、状态
service mysql start
service mysql stop
service mysql status

# 启动、停止、重启、状态
service mysqld start
service mysqld stop
service mysqld restart
service mysqld status

# 非开箱即用的方式：启动、停止。
./bin/mysqld_safe &
./bin/mysqladmin -uroot -p shutdown

Linux 主从配置

# 系统平台一致，数据库版本一致，同步的数据库数据一致

# 1）添加 主从配置：主数据库的参数
vim /etc/my.cnf

# ---------------------- 主从配置：主数据库 start -----------------------
[mysqld]
#开启二进制日志
log_bin = /usr/local/mysql/data/mysql-bin.log 
#设置server-id，必须唯一
server-id=91
#同步的数据库名称
binlog_do_db = test 
#忽略同步的数据库
binlog_ignore_db = mysql
# ---------------------- 主从配置：主数据库  end ----------------------------

# 2）进入主数据库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从数据库(192.168.1.11)配置

vim /etc/my.cnf
# ---------------------- 主从配置：从数据库 start -----------------------
[mysqld]
#开启二进制日志
log_bin = /usr/local/mysql/data/mysql-bin.log 
#设置server-id，必须唯一
server-id=11
#同步的数据库名称
replicate_do_db = test 
#忽略同步的数据库
replicate_ignore_db = mysql 
# ---------------------- 主从配置：从数据库 end---------------------------

# 4）进入从数据库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;
# 参数说明
# MASTER_HOST：主数据库的ip地址
# ASTER_USER：主数据库配置同步的账号
# ASTER_PASSWORD：主数据库配置同步账号的密码
# ASTER_LOG_FILE：在前面“show master status”的File的数据
# ASTER_LOG_POS：在前面“show master status”的Position的数据

MySQL 知识点

数据类型

char								# 固定长度的字符串，分配固定长度
varchar								# 可变长度的字符串、可根据实际数据动态分配空间
int									# 整数型
bigint								# 长整数型
float								# 单精度浮点数
double								# 双精度浮点数
date								# 日期
datetime							# 长日期
clob								# 字符串大对象
blob								# 二进制大对象	：图片、声音、视频

DDL

# 创建表
create table 表名(
	字段1 数据类型,
   	字段1 数据类型
);

# 删除表结构
drop table t_student;

# 删除表物理删除 : 效率较高
truncate table dept_back;

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

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

DML

# 添加数据
insert into t_student(字段1，字段2) values(值1，值2);

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

# 迁移表数据（表结构相同）
insert into dept_bak select * from dept;

# 删除表数据 : 效率较低
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  自动变为 主键约束，orale 不一样）
    primary key(id),								-- 主键约束（表级）
    primary key(id,name),							-- 主键约束（复合主键）
    foreign key(cno) references t_class(classno)	-- 外键约束，必须具有 唯一性
);

存储引擎

一个表存储/组织数据的方式，MySQL有九个存储引擎，默认存储引擎是InnoDB，编码方式是UTF-8。

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，不适合高并发	行锁，适合高并发
缓存	缓存索引	缓存索引、数据
表空间	小	大
关注点	性能	事务
默认安装	Y	Y
存储	索引和数据分开	索引和数据不分开
应用场景	大量查询

MyISAM：支持全文检索、压缩、空间函数。
InnoDB：基于聚族索引建立，以MVCC支持高并发。

事务

概念：访问数据库的一个操作序列，数据库应用系统通过事务集来完成对数据库的存取，事务必须服从ISO/IEC所制定的ACID原则。
四大特性：
- 原子性：事务最小的工作单元，不可再分。
- 一致性：同一个事务中，所有操作要么全部成功，要么全部失败，以保证数据的一致性。
- 隔离性：事务之间具有一定的隔离性。
- 持久性：事务的操作持久到硬盘上，对事务的操作得以保障。

隔离级别：
- 读未提交(read uncommitted)：事务A可以读取事务B未提交的数据，存在脏读。
- 读已提交(read committed)：事务A只能读取事务B提交之后的数据，避免脏读，存在不可重复读。
- 可重复读(repeatable read)：事务A每次读取的数据一致，避免不可重复读，存在幻读。
- 可串行化(serializable)：事务排队，不能并发执行。
并发问题：
- 丢失更新：一个事务的更新覆盖了另一个事务的更新。
- 脏读：事务A读取了事务B未提交的数据。
- 不可重复读：同一个事务，第一次读取和第二次读取的数据不一致。
- 幻读：事务提交后，也读不到最新的，依旧是读刚开始事务时的数据。

# 事务
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、唯一约束的列都会自动设置为索引。
数据结构：B+树 + Hash
索引分类
- 单一索引
- 复合索引
- 主键索引
- 唯一索引
注意：唯一性不强的字段上添加索引用处不大。

# 创建索引
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';

# 索引失效三： 复合索引，没有使用左侧的列进行查询会导致索引失效
# 复合索引
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 中 索引列使用了函数
explain select * from emp where lower(ename) = 'emith';

为什么MySql以B+树作为索引的数据结构？

由于实际应用大多数以范围查找为主，

Hash结构不适合范围查找，排序，内存耗费大；

红黑树（平衡二叉树）、B树随着树的深度越深导致I/O次数变多影响查询效率，排序存在回旋问题。

B+树是基于B树的基础，降低了树的深度从而减少了I/O次数，并提高了区间范围。

视图

# 创建或替换视图
create or replace view vdept as select * from dept;

# 删除视图
drop view vdept;

变量

# 系统变量：全局变量
show global|seesion variables like '%ch%'
set global|seesion variables = te;

# 系统变量：会话变量
show seesion variables like '%cha%';
set session tx_tt = 'aba';

存储过程

# 定义存储过程
# in  入参
# 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;

# 调用存储过程
call update_visit("2022-080520","鼻咽",0);

# 删除存过过程
drop procedure update_visit;

# 查看存储过程
show create procedure update_order;

-- 流程控制
-- 条件函数
if i > 20 then j = 5
end if;

-- case 
case 
when 条件 then
else
end

-- 循环

while(i<50) do

end while;

三范式

第一范式：任何表都必须有主键，每个字段的原子性不可再分。
第二范式：基于第一范式，要求所有非主键字段完全依赖主键，不要产生部分依赖。
第三范式：基于第二范式，要求所有非主键字段直接依赖主键，不要产生传递依赖。
口诀：
- 一对一，外键唯一。
- 一对多，两张表，多的表加外键。
- 多对多，三张表，关系表两个外键。

行表锁

行级锁：开销小，锁定粒度大，死锁概率高，适合高并发。
表级锁：开销大，锁定粒度小，死锁概率低，不适合高并发。

posted @ 2023-06-08 01:20 Soul-Q 阅读(17) 评论(0) 编辑收藏举报

刷新页面返回顶部

Soul-Q

MySQL

MySQL

什么是数据库、数据库管理系统、SQL？

MySQL 查询

单表查询

常用函数

子查询

分组

连接查询

内连接

外连接

合并查询

MySQL 常用命令

MySQL 安装

Linux 安装

Linux 自动启动、环境变量、服务管理

Linux 主从配置

MySQL 知识点

数据类型

DDL

DML

表约束

存储引擎

事务

索引

视图

变量

存储过程

三范式

行表锁

公告