MySQL - Soul

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;

三范式

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

行表锁

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

原文地址:https://www.cnblogs.com/soul-Q/p/17465014.html