MySQL

最新推荐文章于 2024-04-06 08:53:40 发布
最新推荐文章于 2024-04-06 08:53:40 发布
阅读量847 收藏
点赞数
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45632660/article/details/107989887
版权

MySQL

1、初识MySQL

  • MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言–结构化查询语言（SQL）进行数据库管理。
  • MySQL是开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。
  • MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择。

数据库的介绍

MySQL这个名字，起源不是很明确。一个比较有影响的说法是，基本指南和大量的库和工具带有前缀“my”已经有10年以上，而且不管怎样，MySQL AB创始人之一的Monty Widenius的女儿也叫My。这两个到底是哪一个给出了MySQL这个名字至今依然是个迷，包括开发者在内也不知道。

MySQL的海豚标志的名字叫“sakila”，它是由MySQL AB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由来自非洲斯威士兰的开源软件开发者Ambrose Twebaze提供。根据Ambrose所说，Sakila来自一种叫SiSwati的斯威士兰方言，也是在Ambrose的家乡乌干达附近的坦桑尼亚的Arusha的一个小镇的名字。

MySQL，虽然功能未必很强大，但因为它的开源、广泛传播，导致很多人都了解到这个数据库。它的历史也富有传奇性。

在这里插入图片描述

1.1、什么是数据库

数据库(DB,DataBase)

概念: 数据仓库，软件，安装在操作系统之上,可以存储大量数据

作用: 存储数据，管理数据

1.2、数据库分类

关系型数据库: (SQL)

  • MySQL,Oracle,SqlServer,DB2,SQLlite
  • 通过表和表之间,行和列之间的关系进行数据的存储

非关系型数据库: (NoSQL) Not Only 不仅仅是SQL

  • Redis,MongDB
  • 非关系型数据库，对象存储，通过对象的自身属性绝对。

DBMS(数据库管理系统)

DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据。

DBMS -(执行)-> SQL -(操作)-> DB

数据库管理系统是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称 DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。

数据库管理系统是一个能够提供数据录入、修改、查询的数据操作软件，具有数据定义、数据操作、数据存储与管理、数据维护、通信等功能，且能够允许多用户使用。另外，数据库管理系统的发展与计算机技术发展密切相关。而且近年来，计算机网络逐渐成为人们生活的重要组成部分。为此，若要进一步完善计算机数据库管理系统，技术人员就应当不断创新、改革计算机技术，并不断拓宽计算机数据库管理系统的应用范围，从而真正促进计算机数据库管理系统技术的革新。

数据库管理系统概述图

在这里插入图片描述

1.3、MySQL官网下载

https://dev.mysql.com/downloads/mysql/

在这里插入图片描述

1.4、安装MySQL

https://blog.csdn.net/weixin_45764012/article/details/104570795

sc delete mysql, 清空服务/删除mysql

1.5、连接数据库

命令行连接

# 连接数据库	
	mysql -uroot -p 
	
# 查看数据库版本号
	select version()
	
# 所有的语句都用分号结尾	

# 查看所有的数据库
	show databases;
	
# 使用数据库
	use 数据库名;
	
# 查看所有的表
 	show tables;

# 显示表中得到所有信息
	describe 表名;

# 创建数据库
	create database 数据库名;

DDL 数据库定义语言
DML 数据库操作语言
DQL 数据库查询语言
DCL 数据库控制语言

2、操作数据库

2.1、简单操作数据库

# 创建数据库
	CREATE DATABASE  [IF  NOT EXISTS] test		IF  NOT EXISTS  即如果不存在

# 删除数据库
	DROP DATABASE [IF EXISTS] test				IF EXISTS 即如果存在

# 使用数据库
	USE bjpowernode	//如果你的表明或者字段名有特殊符号 	需要加上``

2.2、数据库的列类型

数值

  • tinyint 十分小的数据 1字节

  • smallint 较小的数据 2字节

  • mediumint 中等大小的数据 3字节

  • int 标准的整数 4字节 常用

  • bigint 较大数据 8字节

  • float 单精度浮点数 4字节

  • double 双精度浮点数 8字节

  • decimal 字符串形式的浮点数 金融计算的时候,一般是使用decimal

字符串

  • char 字符串固定大小的 0~255
  • varchar 可变字符串 0~65535
  • tinytext 微型文本
  • text 字符串

事件日期

  • date 日期格式
  • time 事件格式
  • datetime 日期格式 常用
  • timestamp 时间戳 常用
  • year 年份表示

null

  • 没有值,位置
  • 注意,不要使用NULL进行运算,结果为NULL

2.3、数据库的字段属性(重点)

Unsigned:

  • 无符号的整数

  • 声明了该列不能声明为负数

zerofill:

  • 0填充的

  • 不足的位数,使用0来填充

自增:（auto_increment）

  • 通常理解为自增，自动在上一条记录的基础上+1(默认)
  • 通常用来设计唯一的主键~ index,必须是整数类型
  • 可以自定义涉及主键自增的起始值和步长

非空: NULL not null

  • 假设设置为not null,如果不给它复制,就会报错!

  • Null,如果不填写值,默认就是null!

默认:

  • 设置默认的值

2.4、创建数据库表

# primary key 主键
# auto_increment 自增
# not null 非空
# comment 描述
# DEFAULT 默认值
# INNODB 引擎
CREATE DATABASE school

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

格式

create table 表名(
	字段名 列类型 [属性][索引][注释],
    字段名 列类型 [属性][索引][注释],
    字段名 列类型 [属性][索引][注释],
	........
    字段名 列类型 [属性][索引][注释]
)[表类型][字符集设置][注释]

常用命令

show create database 数据库名  --查看创建数据库的语句
show create table 表名		--查看创建表的语句
desc 表名						--查看表的结构

2.5、数据表的类型

--关于数据库引擎
	INNODB 默认使用~
	MYISAM 早些年使用
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大越2倍

常规使用操作:

  • MYISAM 节约空间，速度较快
  • INNODB 安全性高，事务的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下,一个文件夹就对应一个数据库

本质是文件的存储!

MySQL引擎在物理文件上的区别

  • InnoDB在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
  • MYISAM对应文件
    • *.frm 表结构的定义文件
    • *.MYD 数据文件(data)
    • *.MYI 索引文件(index)

设置数据库表的字符集编码

charset=utf8

不设置的话,会是mysql默认的字符集编码 (不支持中文)

MySQL的默认编码是Latin1,不支持中文

在my.ini中配置默认的编码(不建议使用)

character-set-server=utf8

2.6、修改删除表

修改

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

--修改字段
ALTER TABLE 表名 ADD 字段 类型

--修改表的字段(重命名,修改约束~)
ALTER TABLE 表名 MODIEY 字段 类型 --修改约束
ALTER TABLE 表名 CHANGE 旧字段 新字段 类型 --重命名

--删除表的字段
ALTER TABLE 表名 DROP 字段

删除

-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS 表名

3、MySQL数据管理

3.1、外键(了解即可)

* 关于外键约束的相关术语：
外键约束: foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值。

* 业务背景：
请设计数据库表，用来维护学生和班级的信息？
第一种方案：一张表存储所有数据
no(pk) name classno classname
-------------------------------------------------------------------------------------------
1 zs1 101 北京大兴区经济技术开发区亦庄二中高三1班
2 zs2 101 北京大兴区经济技术开发区亦庄二中高三1班
3 zs3 102 北京大兴区经济技术开发区亦庄二中高三2班
4 zs4 102 北京大兴区经济技术开发区亦庄二中高三2班
5 zs5 102 北京大兴区经济技术开发区亦庄二中高三2班
缺点：冗余。【不推荐】

​ 第二种方案：两张表（班级表和学生表）
​ t_class 班级表
​ cno(pk) cname
​ --------------------------------------------------------
​ 101 北京大兴区经济技术开发区亦庄二中高三1班
​ 102 北京大兴区经济技术开发区亦庄二中高三2班

​ t_student 学生表
​ sno(pk) sname classno(该字段添加外键约束fk)
​ ------------------------------------------------------------
​ 1 zs1 101
​ 2 zs2 101
​ 3 zs3 102
​ 4 zs4 102
​ 5 zs5 102
​
* 将以上表的建表语句写出来：

​ t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。

​ 顺序要求：

删除数据的时候，先删除子表，再删除父表。

​ 添加数据的时候，先添加父表，在添加子表。

创建表的时候，先创建父表，再创建子表。

删除表的时候，先删除子表，在删除父表。

​

     drop table if exists t_student;
     drop table if exists t_class;

​     create table t_class(
​       cno int,
​       cname varchar(255),
​       primary key(cno)
​     );

​     create table t_student(
​       sno int,
​       sname varchar(255),
​       classno int,
​       primary key(sno),
​       foreign key(classno) references t_class(cno)
​     );
​     insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
​     insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');

​     insert into t_student values(1,'zs1',101);
​     insert into t_student values(2,'zs2',101);
​     insert into t_student values(3,'zs3',102);
​     insert into t_student values(4,'zs4',102);
​     insert into t_student values(5,'zs5',102);
     insert into t_student values(6,'zs6',102);

* 外键值可以为NULL？
外键可以为NULL。

* 外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
注意：被引用的字段不一定是主键，但至少具有unique约束。

3.2、DML语言(全部记住)

添加
--第一种方式
insert into 表名(字段,字段,字段) values (值,值,值)

--第二种
insert into 表名(值,值,值..)  --注意要对应表的第一个字段开始写对应的值

--第三种
insert into 表名(字段,字段) values (值,值),(值,值).. --可以为对应的字段添加多个值 注意用逗号隔开
修改
update 表 set 字段 where 条件

--例子:
update teacher set name='哈拉少' where id = 1  --将id等于1的 name字段里面的值改为哈拉少

where 条件:

操作符

操作符含义
=等于
<>或!=不等于
<大于
>小于
<=大于等于
>=小于等于
between…and…闭合区间/在…之间
and&&(两个真，才可以查询)
or||(一个为真,就可以查询)
删除
# 语法
	delete from 表名 where 条件

TRUNCATE 命令

作用：完全清空一个数据库表,表的结构和索引约束不会变!

# 清空表数据
TRUNCATE 表名

delete 和 TRUNCATE 区别

  • 相同点: 都能删除数据，都不会删除表结构
  • 不同:
    • TRUNCATE 重新设置 自增列 计数器会归零
    • TRUNCATE 不会影响事务

4、DQL查询数据

4.1、DQL

(Data Qyery LANGUAGE ：数据查询语言)

  • 所有的查询操作都用它 select
  • 简单的查询,复杂的查询它都能做~
  • 数据库中最核心的语言，最重要的语句
  • 使用频率最高的语句

4.2、指定查询字段

# 查询所有
select * from 表名

# 查询所有的学生
SELECT * FROM student

在这里插入图片描述

# 查询指定的字段
select 字段 from 表名

# 查询student表里面学生的姓名
select studentname from student

在这里插入图片描述

# 起别名 as
SELECT studentname AS 姓名 FROM student

在这里插入图片描述

# 函数
	count 计数
    sum 求和
    avg 平均值
    max 最大值
    min 最小值
    
# count就是用来计数
select count(字段) from 表名

# 学生表里学生姓名一共有几个
SELECT COUNT(studentname)  新名字 FROM student

在这里插入图片描述

去重 distinct

# 语法
select distinct 字段 from 表名

数据库的列(表达式)

-- 学院考试成绩+1分查看
SELECT studentresult + 1 AS 成绩 FROM result

数据库中的表达式: 文本值,列,NULL，函数,计算表达式…

select 表达式 from 表名

4.3、where条件字句

作用：检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成! 结果布尔值

逻辑运算符

运算符描述
and / &&逻辑与,俩个都为真,结果为真
or / |逻辑或,其中一个为真,则结果为真
Not / !逻辑非,真为假,假为真!
-- 列子
-- 查询成绩在60到100之间
-- 第一种方法
SELECT * FROM result WHERE studentresult BETWEEN 60 AND 100;

-- 第二种方法
SELECT * FROM result WHERE studentresult>=60 AND studentresult<=100;

-- 第三种方法
SELECT * FROM result WHERE studentresult>=60 && studentresult<=100;

-- 查看学生编号1000以外的
-- 第一种方法
SELECT * FROM result WHERE studentno != 1000;

-- 第二种方法
SELECT * FROM result WHERE NOT studentno = 1000;

模糊查询: 比较运算符

运算符描述
is null结果为空
is not null结果不为空
between闭区间/ 在…之间
like匹配对应的数据
in查询in里面具体数据
-- 例子
-- 模糊查询like
-- like结合 %(代表0到任意个字符)  _(一个字符)
-- 查询学生姓是张的
SELECT * FROM student WHERE studentname LIKE '张%';

-- 查询学生姓张的同学 名字后面只有一个字
SELECT * FROM student WHERE studentname LIKE '张_';

-- 查询学生姓名最后一个字是丽
SELECT * FROM student WHERE studentname LIKE '%丽';

-- 查询学生姓名第二个字是国的
SELECT * FROM student WHERE studentname LIKE '_国%';

-- in(具体的一个或者多个值)
-- 查询1000,1001学生的信息
SELECT * FROM student WHERE studentno IN(1000,1001);

4.4、联表查询

根据表的连接方式来划分，包括：
    内连接：
    	等值连接
   	    非等值连接
	    自连接
    外连接：
       左外连接（左连接）
       右外连接（右连接）
       全连接（这个不讲，很少用！）
 
 内连接:
 内连接之等值连接：最大特点是：条件是等量关系。
 内连接之非等值连接：最大的特点是：连接条件中的关系是非等量关系。
 自连接：最大的特点是：一张表看做两张表。自己连接自己。
 
  
什么是外连接，和内连接有什么区别？

    内连接：
        假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
        AB两张表没有主副之分，两张表是平等的。

    外连接：
        假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中
        的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
    
    外连接的分类？
        左外连接（左连接）：表示左边的这张表是主表。
        右外连接（右连接）：表示右边的这张表是主表。

        左连接有右连接的写法，右连接也会有对应的左连接的写法。
操作描述
inner join如果表中至少有一个匹配,就返回值
left join会从左表中返回所有的值.即使右表中没有匹配
right join会从右表中返回所有的值.即使左表中没有匹配

自连接

自连接：最大的特点是：一张表看做两张表。自己连接自己。

-- 案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。
SELECT
    a.ename AS '员工名',b.ename AS '领导名'
FROM
    emp a
INNER JOIN
    emp b
ON
    a.mgr = b.empno;

4.5、分页和排序

 -- 排序 order by
 -- 升序 asc 降序 desc
 -- where后面不能直接写orderby  需要写 where 条件 orderby
 # 对工资进行降序排序
 SELECT sal FROM emp  ORDER BY sal DESC;

-- 分页 limit
-- 如果需要排序+分页,那么分页必须要写在排序的后面
-- limit (查询起始下标,页面大小pageSize)

4.6、子查询

-- select语句当中嵌套select语句，被嵌套的select语句是子查询。
-- 在where语句中嵌套一个子查询语句
--例子找出高于平均薪资的员工的信息
SELECT * FROM emp WHERE sal >(SELECT AVG(sal) FROM emp)

4…7、分组和过滤

--  group by ： 按照某个字段或者某些字段进行分组。
--  having : having是对分组之后的数据进行再次过滤。

--  注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
--  并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。
--  当一条sql语句没有group by的话，整张表的数据会自成一组。
--  where后面不能使用分组函数：
    select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno;    // 错误了。
    这种情况只能使用having过滤。

--  每个工作岗位的平均薪资？
SELECT AVG(sal),job FROM emp GROUP BY job

-- 找出每个部门的最高薪资，要求显示薪资大于2900的数据。
SELECT MAX(sal),job FROM emp GROUP BY job HAVING MAX(sal) > 2900

5、函数

5.1、常用函数（不常用）

mysql官方文档: https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html

-- ========= 常用函数 =====================

-- 数学运算
SELECT ABS(-20)  -- 绝对值
SELECT CEIL(9.5) -- 向上取整
SELECT FLOOR(9.3) -- 向下取整
SELECT RAND()  -- 返回一个0~1的随机数
SELECT SIGN(100) -- 判断一个数的符号  0返回0 负数返回-1 整数返回1

-- 字符串
SELECT CHAR_LENGTH('余香') -- 字符串长度
SELECT CONCAT('我','爱','你') -- 拼接字符串
SELECT INSERT('我爱你余香',1,2,'超级爱') -- 从某个位置开始替换某个长度
SELECT LOWER('YUXIANG') -- 将大写字母转化为小写字母
SELECT UPPER('yuxiang') -- 将小写字母转化为大写字母
SELECT INSTR('yuxiang','x') -- 返回第一次出现的索引 通过下标1开始
SELECT REPLACE('余香努力学习','努力','奋斗') -- 替换指定的字符串
SELECT SUBSTR('余香说即使再小的帆也能远航',3,5) -- 返回指定的字符串(通过下标截取长度)
SELECT REVERSE('余香') -- 反转

-- 时间和日期函数
SELECT CURRENT_DATE() -- 获取当前的日期
SELECT CURDATE() -- 获取当前的日期
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIME() -- 获取本地时间
SELECT SYSDATE() -- 获取系统时间

SELECT YEAR(NOW()) -- 获取当前的年份
SELECT MONTH(NOW()) -- 月份
SELECT DAY(NOW()) -- 日期
SELECT HOUR(NOW()) -- 小时
SELECT MINUTE(NOW()) -- 分钟
SELECT SECOND(NOW())  -- 秒

-- 系统
SELECT SYSTEM_USER() -- 本机用户名
SELECT USER() -- 用户名
SELECT VERSION() -- 版本号

5.2、聚合函数(常用)

函数名称描述
count()计数
sum()求和
avg()平均值
max()最大值
min()最小值
……
-- ================聚合函数==================

SELECT COUNT(comm) FROM emp -- count(字段) 会忽略所有的null值
SELECT COUNT(*) FROM emp -- count(*) 不会忽略null值,
SELECT COUNT(1) FROM emp -- count(1) 不会忽略所有的null值

SELECT SUM(sal) AS '求和' FROM emp
SELECT AVG(sal) AS '平均值' FROM emp
SELECT MAX(sal) AS '最大值' FROM emp
SELECT MIN(sal) AS '最小值' FROM emp

5.3、数据库界别的MD5加密(扩展)3

什么是MD5

“md5是一种信息摘要算法，它可以从一个字符串或一个文件中按照一定的规则生成一个特殊的字符串，并且一个文件所对应的MD5摘要是固定的，当文件内容变化后，其MD5值也会不一样，因此，在应用中经常使用MD5值来验证一段数据有没有被篡改。”

CREATE TABLE user2(
 id INT(4) AUTO_INCREMENT COMMENT 'id',
 PASSWORD VARCHAR(100) NOT NULL COMMENT '密码',
  PRIMARY KEY(id)
)ENGINE=INNODB CHARSET=utf8


-- 插入式加密
INSERT INTO user2 VALUE(1,MD5(123456))

在这里插入图片描述

6、事务

6.1、什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分。

比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：
update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';
   
以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

6.2、事务的特性

MySQL的四种事务隔离级别

# 事务包括四大特性：ACID

原子性(Atomic)

	整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性(Consist)

	 一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

隔离性(Isolated)

	隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性(Durable)

	 在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

6.3、事务的并发问题

# 事务的并发问题

　	　1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

　	　2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

　　	3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

　	　小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

6.4、关于事务之间的隔离性

 # 事务隔离性存在隔离级别，理论上隔离级别包括4个：
            第一级别：读未提交（read uncommitted）
                对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
                读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
            第二级别：读已提交（read committed）
                对方事务提交之后的数据我方可以读取到。
                这种隔离级别解决了: 脏读现象没有了。
                读已提交存在的问题是：不可重复读。
            第三级别：可重复读（repeatable read）
                这种隔离级别解决了：不可重复读问题。
                这种隔离级别存在的问题是：读取到的数据是幻象。
            第四级别：序列化读/串行化读（serializable）
                解决了所有问题。
                效率低。需要事务排队。
            
            oracle数据库默认的隔离级别是：读已提交。
            mysql数据库默认的隔离级别是：可重复读。

6.5、执行事务


-- ============================事务===========================

-- mysql是默认开启事务自动提交的
SET autocommit = 0 // 关闭
SET autocommit = 1 //开启

-- 手动处理事务
SET autocommit = 0

-- 事务开启
START TRANSACTION

INSERT xxxx 

-- 提交 持久化(成功)
COMMIT

-- 回滚 回到原来的样子(失败)
ROLLBACK

-- 事务结束
SET autocommit = 1

7、索引

MySQL官方对索引的定义为: 索引(Index) 是帮助MySQL高效获取数据的数据结构

7.1、索引的分类

  • 主键索引(primary key)
    • 唯一的标识,主键不可重复,不能为NULL，只能有一个列作为主键
  • 唯一索引(unique)
    • 不可以出现相同的值，可以有NULL值
  • 常规索引(key/index)
    • 默认的,index,key关键字来设置
  • 全文索引(fulltext index)
    • 在特定的数据库引擎下才有,MyISAM
    • 快速定位数据

基础语法

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引

-- 显示索引的所有信息
SHOW INDEX FROM student

-- 增加一个全文索引(索引名) 列名
-- 第二种方式
ALTER TABLE 表名 ADD 索引类型 （unique,PRIMARY KEY,FULLTEXT,index）[索引名]（字段名）
ALTER TABLE student ADD UNIQUE address(address)

-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM student

-- 第三种方式
创建索引对象：
       create index 索引名称 on 表名(字段名);
删除索引对象：
       drop index 索引名称 on 表名;
       
 例如 create index emp_sal_index on emp(sal)

7.2、什么时候考虑给字段添加索引

* 数据量庞大。（根据客户的需求，根据线上的环境）
* 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
* 该字段经常出现在where子句中。（经常根据哪个字段查询）

注意: 主键和具有unique约束的字段自动会添加索引。
根据主键查询效率较高。尽量根据主键检索。

7.3、索引原则

  • 索引不是越多越好
  • 不要对进程变动数据加索引
  • 小数据量的表不需要加索引
  • 索引一般都加载常用查询的字段上!

7.4、索引的实现原理

# 通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
    select ename from emp where ename = 'SMITH';
     通过索引转换为：
     select ename from emp where 物理地址 = 0x3;

8、权限管理和备份

8.1、用户管理

SQL yog 可视化管理

在这里插入图片描述

SQL命令

用户表: mysql.user

本质: 读这张表进行增删改查


-- 创建用户  CREATE USER 用户名 IDENTIFIED BY '密码'  
CREATE USER kuangshen IDENTIFIED BY '123456'  

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

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

-- 重命名
RENAME USER 旧名 TO 新名

-- 用户授权 全部的权限表
-- ALL PRIVILEGES 除了给别人授权，其它都可以干
GRANT ALL PRIVILEGES ON *.* TO 用户名

-- 查看权限
SHOW GRANTS FOR 用户名 -- 查看用户权限
SHOW GRANTS FOR root@localhost -- 查看主用户权限

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM 用户名

-- 删除用户
DROP USER 用户名

8.2、MySQL备份

为什么要备份:

  • 保证重要的数据不丢失
  • 数据转移

MySQL数据库备份的方式

  • 直接拷贝物理文件 data

  • 在Sqlyog这种可视化工具中手动导出

    • 在想要导出的表或者库中，右键，选择备份或导出
      在这里插入图片描述

  • 使用命令导出mysqldump 命令行使用

-- 不用登录mysql 直接导出
-- mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
-- 多张表
-- mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名
-- 选择一个数据库
-- mysqldump -h 主机 -u 用户名 -p 密码 数据库 > 物理磁盘位置/文件名


-- 导入数据库 需要登录mysql
-- source 物理磁盘位置/文件名

9、规范数据库设置

9.1、为什么需要设计

当数据库比较复杂的时候,我们就需要设计了

糟糕的数据库设计

  • 数据冗余，浪费空间
  • 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
  • 程序的性能差

良好的数据库设计

  • 节省内存空间
  • 保证数据库的完整性
  • 方便我们开发系统

软件开发中，关于数据库的设计

  • 分析需求: 分析业务和需求处理的数据库的需要
  • 概要设计: 设计关系图 E-R 图

9.2、三大规范

为什么需要数据规范化?

  • 信息重复
  • 更新异常
  • 插入异常
    • 无法正常显示信息
  • 删除异常
    • 丢失有效的信息

三大范式

博客介绍: https://www.jianshu.com/p/08d123026438

第一范式

原则性: 保证没一列不可再分

第二范式

前提: 满足第一范式

每张表只描述一件事情

第三范式

前提: 满足第一范式 和 第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关

Mls_Lucky
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
如何在MySQL中安装示例数据库sakila
wyxtx的博客
07-13 778
就像 SQLServer 示例数据库一样，MySQL 也有示例数据库，比如sakila；Sakila 数据库最初由 MySQL AB 文档团队的前成员 Mike Hillyer 开发，旨在提供一个标准模式，可用于书籍、教程、文章、示例等中的示例，它包含示例视图、存储过程和触发器。
MySql 8.0.37
最新发布
05-30
MySql 8.0.37
数据库实验一 基于Sakila的数据库操作
03-29
按要求完成对Sakila数据库中表的增、删、改、查操作，提交实验报告。 数据库 MySQL Sakila 问题1：请一边熟悉 sakila 数据库，一边回答以下问题： 1.sakila.mwb 模型中，表结构里每个字段前面的小标记分别表示什么意思？ 2.图中哪部分体现影片-演员关系？换句话说，如果要找出演某个影片的演员名 字，访问哪几张表可以获得信息？ 3.如果已知某个顾客姓名，要找到他租借的所有影片名，需要访问哪几张表？ 若已知某个顾客姓名，即可以得知其 customer_id，即可通过 rental 表得到其租 借对应的 inventory_id，并参考 inventory 中的 inventory_id 后通过 inventory 表中 的 film_id 找到 film 表，根据 film_id 找到 title 即是西安已知顾客名字找寻其租借 的所有影片名 4.. 为什么 film 和 language 表间会有 2 条线？ 问题2： 2.1请列出所有商店的详细地址，显示商店 id，商店地址，所在区域，所在城市，所在 国家； 问题3： 如果 insert 一条数据到 act
MySQL示例数据库(MySQL Sample Databases) 之 sakila数据库
SQLplusDB的小院
05-03 6531
MySQL 官方提供了多个示例数据库，在MySQL的学习、开发和实践中具有非常重要的作用，能够帮助初学者更好地理解和应用MySQL的各种功能和特性。这些数据库都可以通过如下官方网址进行下载和安装。
Sakila数据库和World数据库
热门推荐
桃李不言下自成蹊
12-03 4万+
安装MySQL8.2的时候多出两个样例数据库 Sakila数据库和World数据库
MySQL官方演示数据库Sakila 使用指南
m0_65012566的博客
04-06 1638
Sakila数据库是MySQL 官方提供的一个演示数据库，旨在模拟电影租赁业务。它包含了各种数据库表，如影片、顾客、租赁、支付等，以及关联这些表格的外键关系。：Sakila数据库包含了各种影片的信息，如标题、描述、发行年份、租赁价格等。这些影片可以被顾客租借。：数据库中存储了各个顾客的信息，如姓名、地址、联系方式等。顾客可以通过租赁影片来享受娱乐服务。：租赁表格记录了顾客租借影片的信息，包括租赁日期、归还日期、租赁费用等。租赁管理方便了顾客和影片之间的交互。
mysql8.0升级版本
12-12
MySQL 8.0 升级版本 在本文中，我们将详细介绍 MySQL 8.0 升级版本的步骤和注意事项。MySQL 是一个流行的开源关系数据库管理系统，广泛应用于各种Web 应用程序中。升级 MySQL 版本可以提高数据库性能、修复 bugs 和...
深入浅出MySQL全文
06-29
深入浅出MySQL全文 MySQL 是一种关系型数据库管理系统，广泛应用于各种行业和领域。本资源将从基础篇开始，详细讲解 MySQL 的安装、配置、启动和关闭服务、SQL 基础等知识点。 MySQL 的安装与配置 MySQL 的安装是...
MySQL5.5.62
11-06
MySQL 5.5.62 是 MySQL 数据库管理系统的一个重要版本，主要针对 Windows 操作系统提供了32位和64位的安装包。MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），由 Oracle 公司维护。它以其高效、稳定和...
mysql 5.7 perl
08-08
离线安装mysql文件包，包含依赖，以及peal全套安装包
sakila数据库触发器的理解 基于sakila的触发器的设计
03-29
sakila数据库触发器的理解 基于sakila的触发器的设计 PDF中包括： 1. 观察 sakila.mwb 并回答问题 1.1图中共有几个 View 1.2分析以下 3 个视图，回答以下问题： 2.观察 sakila-schema.sql 和 sakila-data.sql 并回答问题 3.观察数据库的触发器 customer_create_date 并回答问题 3.1customer_create_date 触发器建在哪个表上？ 3.2这个触发器实现什么功能？ 3.3在这个表上新增一条数据，验证一下触发器是否生效。（截图语句和执行结果） 4.根据应用场景，为 Sakila 数据库合理地设计并实现： （注意：请将创建语句、调用结果截图记录到实验报告里） 4.1. 设计 1 个视图，至少关联 3 个表； 4.2设计 1 个触发器，需要在报告里体现触发器生效。 PDF中内容仅包含个人意见，欢迎交流和讨论，如有错误还望指出
mysql省市区表完整版文件
10-07
MySQL数据库在许多应用中用于存储地理信息，如省市区数据，尤其在中国的网站和应用程序中很常见。"mysql省市区表完整版文件"提供了一个全面的数据库表结构，包含了中国所有省份、城市和区县的详细信息，这些信息通常...
mysql57驱动jar包
12-25
mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57...
mysql 5.7 中文文档
02-16
MySQL 5.7中文文档是针对这个特定数据库版本的详细技术参考，包含了丰富的信息，适合开发者、管理员和学习者使用。文档的翻译质量被描述为“还行”，尽管资源可能不易找到，但提供了全面的指南。 该文档涵盖了从...
Java Mysql驱动jar mysql5-mysql8
06-02
mysql-connector-java-5.1.9.jar mysql-connector-java-5.1.25.jar mysql-connector-java-6.0.2.jar mysql-connector-java-8.0.12.jar
kettle项目经验总结
liuzhiqiang66的博客
07-14 2822
kettle维度建模三样式与sakila数据仓库转换加载实操
ETL示例解决方案 —— Sakila下载和基本配置 （笔记一）
qq_38241583的博客
03-20 5218
本例子中，**系统定期从原数据库saklia抽取增量数据，然后转换成符合星型模型的数据，最后把数据加载到目标数据库的租凭业务星型模型中。**
ETL第八章实践项目——构建DVD租赁商店数据仓库（sakila样本数据库）
m0_52058217的博客
11-25 2827
数据清洗第八章案例
mysql中sakila介绍_MySQL Sakila样本数据库
weixin_34537864的博客
02-08 1557
Sakila样本数据库是MySQL官方提供的一个模拟DVD租赁信息管理的数据库，提供了一个标准模式，可作为书中例子,教程、文章、样品,等等，对学习测试来说是个不错的选择。1、下载地址：MySQL :: Other MySQL Documentation https://dev.mysql.com/doc/index-other.html(Example Databases --> sakil...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值