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 )

多表查询

子查询

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

加密（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 ADD PRIMARY INDEX (columnName)

普通索引

-- 直接创建普通索引
CREATE INDEX indexName ON tableName (columnName)

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

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

-- 直接创建唯一索引
CREATE UNIQUE INDEX indexName ON tableName(columnName)

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

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

-- 增加一个全文索引
ALTER TABLE `student` ADD FULLTEXT 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：不传递依赖，也不部分依赖

连接：（这里讲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调节样式，最后缓存和路由管理优化就行了，下篇文章估计会从这个角度出发。万一哪天被赶鸭子上架，学了和做了后端项目就再试着讲讲，现在这环境后端新人是真难入啊，而且工作后发现，沟通才是大头，所以万一哪天兴起做视频，烦请大家不要嫌弃看看。