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)行后;
字段可加聚合函数

函数描述
max最大值
min最小值
sum
avg平均值
count计数
rank排名

窗口函数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、隔离

事务隔离级别脏读不可重复读幻读
读未提交(read-uncommitted)
读已提交(read-committed)
可重复读(repeatable-read)
串行化(serializable)

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

  • 23
    点赞
  • 17
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值