MySQL笔记

初始数据库

什么是数据库

数据库（DB database）

概念：数据仓库，软件，安装在操作系统上的，SQL,可以存储大量的数据，500万

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

数据库分类

关系数据库(SQL)

• mysql,oracle,sqlserver
• 通过表和表之间，行和列之间的关系进行数据的存储

非关系数据库（NOSQL）

• Redis，mongDB
• 非关系数据库，对象存储，通过对象的自身的属性来决定

DBMS(数据库管理系统)

• 数据库管理软件，科学有效的管理我们的数据。维护和获取数据

连接数据库

命令行链接

mysql -u用户名 -p密码 --连接数据库
flush privileges；--刷新权限
----------------------------------------
--所有的语句都是用分号结束；
show databases;--查看所有数据库
use school;--切换数据库 use 数据库名
 show tables;--展示库中所有表
 describe student;--显示数据库中所有的表的信息，可用缩写desc 表名
  create database study;--创建数据库
exit;--退出
--单行注释
/*
多行注释
*/

操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据

mysql不区分大小写

操作数据库

1.创建数据库

CREATE DATABASE IF NOT EXISTS study;

2.删除数据库

DROP DATABASE  IF EXISTS study;

3.使用数据库

--如果你的表名或字段名是一个特殊符号，既需要带``(英文状态下的小波浪)
USE `study`;

4.查看数据库

SHOW DATABASES;

列的类型

数值

• tinyint 十分小的数据 1个字节
• smallint 比较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节
• bigint 比较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数 金融计算

字符串

• char 字符串固定大小的 0~255
• varchar 可变字符串 0~65535 常用的变量 String
• tinytext 微型文本 2^8-1
• text 文本串 2^6-1 保存大文本

时间日期

• date YYYY-MM-DD 日期格式
• time HH:mm：ss 时间格式
• datetime YYYY-MM-DD HH:mm：ss 常用的时间格式
• timestamp 时间戳，1970 1，1到现在的毫秒数！
• year 年份

null

• 没有值，未知
• 注意：不要使用null进行运算，结果为null

字段属性

unsigned:

• 无符号的整数
• 声明了该列不能声明为负数

zerofill：

• 0填充的
• 不足的位数，使用0来填充，int（3） 6—006

自增：

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

非空： NULL not null

• 假设设置为not null，如果不给它赋值，就会报错
• null，如果不填写值，就默认为null

默认：

• 设置默认的值
• sex 默认为男，如果不指定该列的值，则会有默认的值

扩展：

/*
每一个表，都必须存在以下五个字段！表示一个记录存在的意义
id 主键
`version` 乐观锁
is_deletee 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/

创建数据库表

--目标创建一个school数据库
--创建学生表（列，字段）使用SQL创建
--学号int 登陆密码varchar，姓名，性别varchar，出生日期datatime，家庭住址，email
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 '1234' 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[if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

常用命令

SHOW CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student --查看student数据表的定义语句
DESC student --查看表的结构

数据表的类型

--关于数据库引擎
/*
INNODB 默认使用
MYISAM 早年使用
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	比较小	比较大约为2倍

常规使用操作

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

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库本质还是文件的存储

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

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

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

CHARSET=utf8

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

MySQL的默认编码是latin1，不支持中文

在my.ini中配置默认的编码

CHARSET-set-server=utf8

修改删除表

修改

USE `school`
-- 修改表名：alter table 就表名 rename as 新表名
ALTER TABLE student RENAME AS stu
-- 增加表的字段:alter table 表名 add 
ALTER TABLE stu ADD age INT(11)
-- 修改表的字段（重命名，修改约束）
-- alter table 表名 modify 字段名 列属性
ALTER TABLE stu MODIFY age VARCHAR(6)-- 修改约束
-- alter table 表名 change 旧名字 新名字 列属性
ALTER TABLE stu CHANGE age age1 INT(11) -- 字段重名
-- 删除表的字段：alter table 表名 drop 字段名
ALTER TABLE stu DROP sex

删除

-- 删除表（如果存在在删除）
DROP TABLE IF EXISTS stu

所有的创建和删除操作尽量加上判断，以免报错

MYSQL数据管理

外键

方式一，在创建表的时候，增加约束

-- 学生表的gradeid字段，要去引用年纪表的gradeid
-- 定义 外键key
--给外键添加约束 references引用
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 '1234' COMMENT'密码', `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT'性别', `birthday` DATETIME DEFAULT NULL COMMENT'出生日期',gradeid INT(10) NOT NULL, `address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址', `email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱', PRIMARY KEY(`id`),KEY fk_gradeid(`gradeid`),CONSTRAINT fy_gradeid FOREIGN KEY(`gradeid`) REFERENCES grade(`gradeid`) )ENGINE=INNODB DEFAULT CHARSET=utf8; 

CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
PRIMARY KEY(`gradeid`) 
)

删除有外键关系的表的时候，必须先删除引用别人的表，再删除被引用的表

方式二：创建表成功后，添加外键约束

--创建表时没有外键关系
ALTER	TABLE `student`
ADD CONSTRAINT `fk_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
-- alter table 表名 add constraint 约束名 foreign key（作为外键的例）references 那个表（那个字段）

DML语言

• 数据库的意义：存储数据，数据管理
• DML语言：数据操作语言
  • insert
  • delete
  • update

添加

insert

-- 插入语句
-- insert into 表名 （`字段名`,`字段名`,`字段名`）values(`值1`,`值1`,`值1`),(`值1`,`值1`,`值1`),(`值1`,`值1`,`值1`)
INSERT INTO `grade`(`name`) VALUES('藏十三'),('十三')
-- 由于主键自增我们可以省略（如果不写表的字段，他会一一匹配）
INSERT INTO `grade` VALUES('藏十三'),('十三')
-- 插入语句，我们一定要数据和字段一一对应

语法：insert into 表名 （字段名,字段名,字段名）values(值1,值1,值1),(值1,值1,值1),(值1,值1,值1)

注意事项：

1. 字段和字段之间使用逗号隔开
2. 字段可以省略的，但是后面的值必须要一一对应，不能少
3. 可以同时插入多条数据，values后面的值，需要使用，逗号隔开
4. 用英文状态下的单引号括住

修改

update

-- 带条件，进行修改
UPDATE `grade` SET `name`='学柯君' WHERE `gradeid`=1
-- 模糊修改
UPDATE `grade` SET `name`='学柯君'
-- 修改多个属性，逗号隔开
UPDATE `grade` SET `name`='学',`age`=1
-- 语法
UPDATE 表名 SET 字段名=值,字段名=值 WHERE [条件]

条件：where子句运算符id等于某个值，大于某个值，在某个区间内修改

操作符返回值

操作符	含义	范围	结果
=	等于	5=6	false
<>或者！=	不等于	5<>6	true
>
<
<=
>=
BETWEEN…AND…	在某个范围	[2.5]
and	我和你
or	我或你

语法：UPDATE 表名 SET 字段名=值,字段名=值 WHERE [条件]

注意：

• 字段名尽量带上``

• 条件，筛选的条件，如果没有指定。则会修改所有的列

• value，是一个具体的值，也可以是一个变量

• 多个设置的属性之间，使用英语逗号隔开

  UPDATE `grade` SET `date`=CURRENT_TIME
  

删除

delete 命令

语法：delete from 表名 [where 条件]

-- 删除数据（避免这样写，会全部删除）
DELETE FROM `grade`
-- 删除指定数据
DELETE FROM `grade` WHERE gradeid=1;

truncate命令

作用：完全清空一个数据库表，表结构和索引约束不会变，自动增列归零

-- 清空表
TRUNCATE `grade`

delete的 trunncate区别

相同点：都能删除数据，不会删除表结构

• 不同点：
  • trunncate 重新设置自动增列归零
  • delete在删除表数据完，重启数据库自动增值才会归零
    • innoDB: 自增列会重1开始（存在内存中的，断电即失）
    • myisam：继续从上一个自增量开始（存在文件中的，不会丢失）
  • trunncate不会影响事务

DQL查询数据

DQL

(Data Query LANGUAGE:数据查询语言)

• 所有的查询操作都用它：select
• 简单查询，复杂查询
• 数据库中最核心的语言

SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]

查询指定字段

-- 查询全部的学生， select 字段 from表
SELECT * FROM `student`
-- 查询指定字段
SELECT `studentno`,`studentname` FROM`student`
-- 别名，给结果起一个名字，as 可以给字段起别名，也可以给表起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS s
-- 函数 concat（a,b) 
SELECT CONCAT('姓名:',studentname) AS 新名字 FROM `student`

语法：selete 字段… from 表

有时候，列名不是那么的见名之一，我们起别名，AS 字段名as别名

去重 distinct

作用：去除select查询出来的结果中重复的数据

-- 查询成绩
SELECT * FROM result-- 全部成绩
SELECT `studentno` FROM result-- 查询那些学生有成绩
SELECT DISTINCT `studentno` FROM `result`-- 发现重复数据，去重

数据库的列 (表达式)

SELECT VERSION() -- 查询系统版本号（函数）
SELECT 100*2-1 AS 结果 -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）
SELECT `studentno`,`studentresult`+1 AS '提分后' FROM result

数据库中的表达式：文本值，列，null等等

select 表达式 from 表

where条件子句

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

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

逻辑运算符

运算符	语法	描述
and&&		逻辑与，两个都为真，结果为真
or||		逻辑或，其中一个为真，结果为真
not !=		逻辑非，真为假，假为真

SELECT `studentno`,`studentresult` FROM result
-- 查询成绩在95到100之间键的
SELECT * FROM result WHERE `studentresult`>=95 AND `studentresult`<=100
-- and &&
SELECT * FROM result WHERE `studentresult`>=95 && `studentresult`<=100
-- 某糊查询
SELECT * FROM result WHERE `studentresult` BETWEEN 5 AND 100
-- 查询除了1000好的学生 !=
SELECT * FROM `student` WHERE `studentno`!=1000
-- 查询除了1000好的学生 not
SELECT * FROM `student` WHERE NOT `studentno` != 1000

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null，结果为真
is not null	a is not null	如果操作符不为null，结果为真
between	a between b and c	若a在b和c之间则为真
like	a like b	sql查询，如果a匹配b，则结果为真
in	a in(a,s,d,)	如果a在a,s,d之中则为真

-- 查询姓刘的同学
-- like结合 %（任一个字符） _(一个字符)
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(一个值或者多个值)
SELECT * FROM `student` WHERE `studentno` IN(1001,1000)
-- 查询在北京的
SELECT * FROM `student` WHERE `address` IN('北京朝阳')
-- 查询地址为空的学生
SELECT * FROM `student` WHERE `address` IS NULL OR `address`=''
-- 查询生日不为空的
SELECT * FROM `student` WHERE `borndate` IS NOT NULL
-- 查询生日为空的
SELECT * FROM `student` WHERE `borndate` IS NULL

联表查询

查询方式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jr7bg9Jr-1627029003225)(C:\Users\surface\Desktop\学习\截图\ss.jpg)]

-- 查询参加考试的同学的（学号，姓名，科目编号，分数）
/*思路
1.分析需求：分析查询字段来自那些表（连接查询）
2.使用哪种连接查询
确认交叉点（两个表有哪些相同字段）
判断条件：学生表中的studentno和成绩表中的studentno
*/
-- inner join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student`AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
-- right join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student`AS s
RIGHT JOIN `result` AS r
ON s.`studentno`=r.`studentno`
-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student`AS s
LEFT JOIN `result` AS r
ON s.`studentno`=r.`studentno`

操作	描述
inner join	如果表中至少有一个匹配，就会返回行
left join	会从左表中返回所有的值，即使右表没有匹配
right join	会从右表中返回所有的值

语法：select 字段名 from 表名 [inner,left,right] join [on,where]

where:等值查询

join （连接表）on（判断条件）连接查询

-- 三表查询
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON r.studentno=s.studentno
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
-- 我要查询哪些数据
-- 从哪几个表中查 from 表 xx join 连接的表 on 交叉条件
-- 假设存在一种多表查询，慢慢来，先查两张表然后在慢慢增加

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

父类：

​

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryname
	4	数据库
2	8	办公信息
3	6	web技术
5	7	ps技术

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息：把一张表看成两个一模一样的表
SELECT a.`categoryname` AS '父类目',b.`categoryname` AS '子类目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

分页和排序

排序

-- 排序，升序asc 降序desc
-- order by通过那个字段排序
-- 查询结果根据成绩排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON r.studentno=s.studentno
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
ORDER BY subjectname DESC

分页

--为什么要分页？
-- 缓解数据库压力，给人更好的体验
 
 -- 分页，每页只显示五条数据
 --语法：limit 起始值，页面大小
 --网页应用：当前，总页数，页面大小
 --limit (n-1)*5（初始数）,5(页面大小)
 --总页数=总数据数/页面大小
 SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN result r
ON r.studentno=s.studentno
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
ORDER BY subjectname DESC
LIMIT 4,5

子查询

where（这个值是计算出来的）

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

where（select * from）

/*使用子查询（由里及外）
查询所有高等数学-3的学生学号
*/
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno`=(
SELECT `subjectno` FROM `subject`
WHERE `subjectname`='高等数学-3'
)
ORDER BY studentresult DESC

分组和过滤

-- 查询不同的课程的平均值，最高分，最低分，平均分大于80
-- 核心：课程分组
SELECT `subjectname`,AVG(`studentresult`)AS 平均值,MAX(`studentresult`),MIN(`studentresult`)AS 最低分 
FROM result r
INNER JOIN `subject` s
ON r.`subjectno`=s.`subjectno`
GROUP BY r.`subjectno`
HAVING 平均值>80

MySQL函数

常用函数

参考网站：MySQL函数大全，MySQL常用函数汇总 (biancheng.net)

MySQL 数值型函数
函数名称	作 用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号
POW 和 POWER	两个函数的功能相同，都是所传参数的次方的结果值
SIN	求正弦值
ASIN	求反正弦值，与函数 SIN 互为反函数
COS	求余弦值
ACOS	求反余弦值，与函数 COS 互为反函数
TAN	求正切值
ATAN	求反正切值，与函数 TAN 互为反函数
COT	求余切值
MySQL 字符串函数
函数名称	作 用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
MySQL 日期和时间函数
函数名称	作 用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW 和  SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP	获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME	将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH	获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR	获取年份，返回值范围是 1970〜2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引

聚合函数

MySQL 聚合函数
函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值
-- 都能统计，表中数据（查询一个表中有多少记录）
SELECT COUNT(`studentno`) FROM `result`-- count(字段) 会忽略所有的null值
SELECT COUNT(*) FROM `result`-- count(*),不会忽略null值，本质计算行数
SELECT COUNT(1) FROM `result`-- count(*),不会忽略所有的null值，本质计算行数
SELECT SUM(`studentresult`) AS 总和 FROM `result`
SELECT AVG(`studentresult`) AS 平均值 FROM `result`
SELECT MAX(`studentresult`) AS 最大值 FROM `result`
SELECT MIN(`studentresult`) AS 最小值 FROM `result`
MySQL 流程控制函数
函数名称	作用
IF	判断，流程控制
IFNULL	判断是否为空
CASE	搜索语句

MD5加密

什么是MD5

主要增强算法复杂度和不可逆性。

MD5不可逆，具体的值的MD5一样的

MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值

-- 测试MD5 加密
CREATE TABLE test(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO test VALUES(1,'sdad','dfdsfsd'),(2,'sdad','dfdsfsd'),(3,'sdad','dfdsfsd')
-- 加密
UPDATE test SET pwd=MD5(pwd) WHERE id=1
UPDATE test SET pwd=MD5(pwd)-- 全部加密

事务

什么是事务

要么都成功，要么都失败

---

1.sql执行 a给b转账 a1000 ->200 b200

2.sql执行 b收到a的钱 a800 -》b400

---

将一组sql放在一批次中执行

事务原则：ACID原则 ：

**原子性：**要么都成功要么都失败

**一致性：**事务前后的数据完整性要保证一致

持久性（事务提交）：事务一旦提交则不可逆，被持久化到数据库中

**隔离性：**事务隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的任务干涉之间相互隔离

隔离所导致的一些问题

**脏读：**指一个事务读取了另外一个事务未提交的数据。

**不可重复读：**在一个事务内读取表中的某一行数据，多次读取结果不同。

**虚读：**是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

命令

-- mysql是默认开启事务自动提交的
SET autocommit=0 -- 关闭
SET autocommit=1 -- 开启默认
-- 手动提交事务
SET autocommit=0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开启，从这个之后的sql都在同一个事物内
-- sql语句
-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET autocommit=1 -- 开启自动提交

模拟场景

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `acount`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `acount`(`name`,`money`) VALUES('a',1000.00),('b',200.00)


SET autocommit=0
START TRANSACTION -- 开启事务
UPDATE acount SET  money=money-500 WHERE `name`='a'
UPDATE acount SET  money=money+500 WHERE `name`='b'
COMMIT
ROLLBACK
SET autocommit=1

索引

什么是索引

它是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。

索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引（primary key）
  • 唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引（UNIQUE KEY）
  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识位唯一索引
• 常规索引（KEY/INDEX）
  • 默认的，index。key关键字来设置
• 全文索引（FullText）
  • 在特定的数据库引擎下才有，MyiSAM
  • 快速定位数据

基本语法

/* 索引的使用
1.在创建表的时候给字段增加索引
2.创建完毕后，增加索引
*/
-- 显示所有的索引信息
 SHOW INDEX FROM `student`
-- 增加一个全文索引（索引名） 列名
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`studentname`);
  
-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM `student`-- 非全文索引
EXPLAIN SELECT * FROM  `student` WHERE MATCH(`studentname`) AGAINST('周')
  --create index 索引名 on表名 （字段名）

索引在小数据量的时候，用处不大，但在大数据的时候区别十分大

索引原则

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

索引的数据结构

hash类型的索引

btree：innoDB的默认数据结构

存储过程

介绍
存储过程：用于数据库中存储复杂程序，方便外部调用的一种数据库对象。
是特定功能的SQL语句集，经编译创建并保存在数据库中，用户通过存储过程名称来调用，需要参数在名称后的括号里加参数，（相当于）

权限管理和备份

用户管理

SQL操作命令

用户表：MySQL：user

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

-- 创建用户CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER lj IDENTIFIED BY '123'

-- 修改密码（修改当前用户密码）
SET PASSWORD =PASSWORD('1234')
-- 修改指定用户的密码
SET PASSWORD FOR lj=PASSWORD('456')
-- 重命名RENAME USER 旧名字 TO 新名字
RENAME USER lj TO ljj
-- 用户授权 GRANT ALL PRIVILEGES ON 权限 TO 库名
-- ALL PRIVILEGES 除了给别人授权，其他什么都能干
GRANT ALL PRIVILEGES ON *.* TO ljj
-- 查询权限
SHOW GRANTS FOR ljj -- 普通用户权限 GRANT ALL PRIVILEGES ON *.* TO 'ljj'@'%'
SHOW GRANTS FOR root@localhost -- root用户权限  GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
-- 撤消权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM ljj
-- 删除用户
DROP USER ljj

MySQL备份

为什么要备份：

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

mysql备份方式

• 直接拷贝物理文件
• 在sqlyog这种可视化工具中手动导出
  • 在想要导出的表或者库中，右键选择备份或导出[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0IMe9pJY-1627029003236)(C:\Users\surface\Desktop\学习\截图\存储.JPG)]
• 使用命令行导出 mysqldump 命令使用

# mysqldump -h主机 -u用户名 -p密码 数据库 [表名1 表名1 表名1] >物理位置/文件名
C:\Users\surface>mysqldump -hlocalhost -uroot -p1234 school >C:\Users\surface\Downloads\aaa.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#导入
#登陆情况下，切换到指定数据库
#source 备份文件
source C:\Users\surface\Downloads\aaa.sql
-- 二种方式
Mysql -u用户 -p密码 库名<备份文件

规范数据库设计

当数据库比较复杂时，我们需要设计了

糟糕的数据库设计：

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

良好的数据库设计：

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

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

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

为什么需要数据规范

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

三大范式

第一范式：

原子性：保证每一列不可再分

第二范式:

前提：满足第一范式

每一张表只描述一件事情

第三范式：

前提：满足第一范式和第二范式

第三范式需要确保数据表中的每一列数据和主键直接相关，而不能间接相关

（规范数据库设计）

规范性和性能问题：

关联查询的表不得超过三张表

• 考虑商业化的需求和目标，（成本，用户体验）数据库的性能更加重
• 在规范性能的问题的时候，需要适当考虑一下规范性
• 故意给某些表增加一些冗余的字段。（从多表查询变为单表查询）
• 故意增加一些计算列（从大数据量降为小数据量的查询：索引）