Mysql个人总结

前言

又来水字数啦，这次主要讲一下MySQL的常用概念，难点的就必须上项目讲解了，而且比较基础面试基本都会问一些，用的话，不少优化都从这里入手，执行命令中括号多为可选内容，不要直接中括号

操作数据库

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名;
2、删除数据库

DROP DATABASE [if EXISTS] 数据库名;
3、使用数据库

--如果表名或者字段名是特殊字符，则需要带``
use 数据库名;
4、查看数据库

SHOW DATABASES;

表操作

建表：

CREATE TABLE IF NOT EXISTS `student`( `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别', `birthday` DATETIME DEFAULT NULL COMMENT '出生日期', `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8

括号内，分别对应列名‘id’，类型INT，括号内是定最大长度（字节），

默认属性【

字段属性
UnSigned，无符号的，声明了该列不能为负数
ZEROFILL，0填充，不足位数的用0来填充 , 如int(3),5则为005
Auto_InCrement，通常理解为自增，自动在上一条记录的基础上默认+1，通常用来设计唯一的主键，必须是整数类型

可定义起始值和步长，当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表，
SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
NULL 和 NOT NULL，默认为NULL , 设置为NOT NULL , 则该列必须有值
DEFAULT，默认的，用于设置默认值
例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

】

，主键PRIMARY KEY('id')，快速搜索创建索引使用【另外，在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)也在此定义,另一个表class

KEY `FK_birthday` (`birthday`),//起别名

CONSTRAINT `FK_birthday` FOREIGN KEY (`birthday`) REFERENCES `class`(`stu_birth`)。

-- 创建表的时候没有外键关系

ALTER TABLE `student` ADD CONSTRAINT `FK_birthday` FOREIGN KEY(`birthday`) REFERENCES `class`(`stu_birth`);

都是物理外键，数据库级别的外键，不建议使用！避免数据库过多造成困扰！】，

最后comment别名,到这里基本结束，但面试会问你引擎ENGINE=INNODB（

Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

）

DEFAULT CHARSET=utf8是识别中文字符集

创建完，查看是否正确

SHOW TABLES;-- 查看所有表

DESC 表名;-- 显示表的具体结构

需要修改：

修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名

添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]

数据库的表操作，基础中的基础，大家记得增删改查四字真言就行，其中查询是关键，称为DQL（Data QueryLanguage数据查询语言），其他三称为DML（Data Manipulation Luaguge：数据操作语言）

DML

1、添加（增）：INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];
2、删除（删）：DELETE FROM 表名 [WHERE 条件];

3、修改（改）：UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

DQL

单表查询

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表asc升序/desc降序】

like “ ”

【模糊查询

• 百分号（%）：表示任意长度的任意字符序列。
• 下划线（_）：表示任意单个字符】

limit offset,pagesize;//offset偏移量展示结果行数，页面大小行数

lag(column_name,offset,default_value)行前

lead(column_name,offset,default_value)行后;
字段可加聚合函数

窗口函数over (partition by... order by )

多表查询

直接join是去掉符合where条件项多表，合成一张表

self join

一张表里列相关，例如上下级

SELECT
e.name AS 员工姓名,
m.name AS 经理姓名
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
结果：

集合查询

union和union all，都是两个查询结果合并，但后者不去重

子查询

在where子句中嵌套一个子查询语句，

前接exists或者not exists判断子条件是否有结果

SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
接any和all，是逻辑判断

SELECT ProductName FROM Products
WHERE ProductID = ANY (
SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10
);

<>相当于不等于不太常用

补充说明：

可查询后结果直接dml，

INSERT INTOtarget_table
SELECT * FROMsource_table
WHEREcondition;

条件也有其他写法：

SELECT ProductName, Price,

CASE
WHEN Price < 20 THEN 'Low Cost'
WHEN Price BETWEEN 20 AND 50 THEN 'Medium Cost'
ELSE 'High Cost'
END AS PriceCategory
FROM Products;

加密（md5，全数据型，不建议，一般是在后台对单表字段加密）

UPDATE `testMD5` SET `pwd`=MD5(pwd) WHERE id=1;

UPDATE `testMD5` SET `pwd`=MD5(pwd);

前些天发现的一个小程序，可以练习一下：InsCode - 让你的灵感立刻落地

事务

1、特性（ACID）：

原子性（Atomicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency） 事务前后数据的完整性必须保持一致。
隔离性（Isolation） 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability） 事务一旦被提交则不可逆，被持久化到数据库中，接下来即使数据库发生故障也不应该对其有任何影响

2、隔离

串行化读写都要加锁，可重复读仅写操作（DML）加锁

读已提交（Read Committed）
MySQL的读已提交实际是语句级别快照。

与可重复读级别主要有两点不同：
1）获得ReadView的时机。每个语句开始执行时，获得ReadView，可见性判断是基于语句级别的ReadView。读的策略与可重复读类似。

2）写锁的使用方式。这里不需要GAP LOCK，只使用记录锁。并且事务只持有被UPDATE/DELETE记录的写锁（可重复读需要保留全部写锁直到事务结束，而读已提交只保留真正更改的）。

读未提交（Read Uncommitted）
读最新的数据，不管这条记录是不是已提交。不会遍历版本链，少了查找可见的版本的步骤。这样可能会导致脏读。

对写仍需要锁定，策略和读已提交类似，避免脏写。

3、事务执行流程

1️⃣ 关闭自动提交

SET autocommit=0;
2️⃣ 事务开启

START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
3️⃣ 成功则提交，失败则回滚

-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
4️⃣ 事务结束

SET autocommit=1; -- 开启自动提交
​5️⃣ 其他操作

SAVEPOINT 保存点名; -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
RELEASE SAVEPOINT 保存点名; -- 撤销保存点

索引

可以提升批量操作的检索效率

主键索引

-- 创建表的时候指定主键索引
CREATE TABLE tableName(
......
PRIMARY INDEX (columeName)
)

-- 修改表结构添加主键索引
ALTER TABLE tableName ADDPRIMARY INDEX(columnName)

普通索引

-- 直接创建普通索引
CREATEINDEXindexName ON tableName (columnName)

-- 创建表的时候指定普通索引
CREATE TABLE tableName(
......
INDEX [indexName] (columeName)
)

-- 修改表结构添加普通索引
ALTER TABLE tableName ADD INDEX indexName(columnName)
唯一索引（说是唯一但可多个）

-- 直接创建唯一索引
CREATEUNIQUE INDEXindexName ON tableName(columnName)

-- 创建表的时候指定唯一索引
CREATE TABLE tableName(
......
UNIQUE INDEX [indexName] (columeName)
);

-- 修改表结构添加唯一索引
ALTER TABLE tableName ADD UNIQUE INDEX [indexName] (columnName)
全文索引

-- 增加一个全文索引
ALTER TABLE `student` ADDFULLTEXT INDEX`StudentName`(`StudentName`);

-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d');

权限

-- 创建用户
CREATE USER zsr IDENTIFIED BY '123456'

-- 删除用户
DROP USER zsr

-- 修改当前用户密码
SET PASSWORD = PASSWORD('200024')

-- 修改指定用户密码
SET PASSWORD FOR zsr = PASSWORD('200024')

-- 重命名
RENAME USER zsr to zsr2

-- 用户授权(授予全部权限,除了给其他用户授权)
GRANT ALL PRIVILEGES on *.* TO zsr2

-- 查询权限
SHOW GRANTS FOR zsr
-- 查看root用户权限
SHOW GRANTS FOR root@localhost

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM zsr

备份

保证重要的数据不丢失、数据转义，最好留个备份

直接拷贝物理文件.sql，MySQL数据表以文件方式存放在磁盘中,每次对应数据库右键转储为sql文件，需要时右键执行即可

当然，自带的备份功能也行

表间关系（范式）

逐级加深，逐级包含

1NF:列属性间，原子性符合即可

2NF:不存在部分依赖，（例子：产品和订单，订单包含所有产品符合2NF，否则不符合）

3NF：不存在传递依赖，主字段不冗余（例子：每个订单对应一种商品，唯一依赖）

BCNF：不传递依赖，也不部分依赖

数仓：（保密数据，复制到仓库中操作）

创建：

DELIMITER //

CREATE PROCEDURE GetCustomersByCity
IN City VARCHAR(50),
IN PostalCode VARCHAR(10)

/*

sqlserver是参数前加@使用也一样

*/
BEGIN
SELECT * FROM Customers
WHERE City = City AND PostalCode = PostalCode;
END //

DELIMITER;

使用：

CALL GetCustomersByCity('London');

分层：

命名规范及作用

操作层

表名通常为ods_[业务域]_[表名]_inc(增量，基本是天ds==yyymmmddd) 或ods_[业务域]_[表名]_all(全量)

明细层

数据清洗： 处理空值（NULL）、异常值、错误数据。
码值转换： 将业务系统的数字码值转换为可读的中文或英文描述（如 status=1 -> status_desc='待支付'）。
维度退化 (Dimensional Degeneration)： 将常用的维度属性（如用户姓名、商品名称、类目）直接关联到事实表中，生成宽表，减少上层JOIN。
字段规范化： 统一日期格式、金额单位、枚举值标准。
数据整合： 将来自不同源的同一实体的数据合并（如App订单和Web订单合并为统一订单表）。
适度聚合： 可以做一些非常轻度的、不可逆的聚合（如计算单条订单的实付金额 = 原价 - 折扣），但不涉及跨记录的统计。
————————————————
版权声明：本文为CSDN博主「走过冬季」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/winterPassing/article/details/154397376

表名通常为dwd_[业务域]_[主题]_[子主题]_inc。

服务层

公共指标： 计算一些高频使用的、跨业务的指标，如：
用户日行为汇总：dws_user_daily_agg
商品销售日汇总：dws_sku_daily_agg
区域销售月汇总：dws_region_monthly_agg
维度组合： 按 user_id, ds；或 category_id, province, ds 等组合进行GROUP BY。
避免过度汇总： 不做过于复杂的、特定场景的聚合，保持一定的灵活性。
————————————————
版权声明：本文为CSDN博主「走过冬季」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/winterPassing/article/details/154397376

表名通常为dws_[业务域]_[统计粒度]_[汇总周期]_agg。

应用层

表名为业务含义：ads_gmv_daily_report,

连接：（这里讲java，其余语言引入对应库）

import java.sql.*;

DriverManager
DriverManager：驱动管理

//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");

本质上执行DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());

//3.连接,获得数据库对象connection
Connection connection = DriverManager.getConnection(url, username, password);

connection代表数据库，因此可以设置事务自动提交，事务回滚等

然后执行就行

statement.executeQuery();//查询操作,返回结果 statement.execute();//执行sql statement.executeUpdate();//用于增删改,返回受影响的行数

ResultSet：查询的结果集，封装了所有查询的结果

结语

本人开发水平不够，其实就比小白好不多少了，遇到问题还是靠搜索引擎，专门独立开发一个整体应用，想想都头疼，毕竟看我这种新手的项目，还不如直接根据课题找大佬们的项目，先拿来走流程，而且代码从零开始太恐怖了，我现在基本都是在做附加功能，前端还好，后端尤其是java这块真的是没彻底由自己上手过，毕竟各种依赖和注解就够呛了，前端只要找准框架，然后找好UI库，再逐个功能实现，然后css调节样式，最后缓存和路由管理优化就行了，下篇文章估计会从这个角度出发。万一哪天被赶鸭子上架，学了和做了后端项目就再试着讲讲，现在这环境后端新人是真难入啊，而且工作后发现，沟通才是大头，所以万一哪天兴起做视频，烦请大家不要嫌弃看看。