【狂神说Java】阶段三笔记2. MySQL-02

最新推荐文章于 2022-06-08 21:33:54 发布

Yy_sushine

最新推荐文章于 2022-06-08 21:33:54 发布

阅读量238

点赞数 1

分类专栏：【狂神说java】学习笔记文章标签：数据库 mysql

本文链接：https://blog.csdn.net/Yy_sushine/article/details/113947967

版权

【狂神说java】学习笔记专栏收录该内容

49 篇文章 10 订阅

订阅专栏

文章目录

4 DQL查询数据（最重点）

4.1 DQL

select完整语句

SELECT [ALL | DISTINCT]
{* | talbe.* | [table.field1[AS alias1][,[table.field2[AS alias2]][,...]]}
FROM table_name [as table_alias]  -- 以下内容顺序不能变
	[left | right | inner join table_name2]  -- 联合查询
	[WHERE…]  -- 指定结果需要满足的条件
	[GROUP BY…]  -- 指定结果按照哪几个字段来分组
	[HAVING…]  -- 过滤分组的记录必须满足的次要条件（条件和where一样，只是位置不同）
	[ORDER BY…]  -- 指定查询记录按一个或多个条件排序
	[LIMIT {[offset,]row_count | row_countOFFSET offset}];  -- 指定查询的记录从哪条至哪条

查询：跨表，跨数据库（业务层面）

（Data Query Language：数据查询语言）

所有的查询操作都用它 Select；简单的查询、复杂的查询都用它；数据库中最核心的语言；使用频率最高的语言
p16用到的数据表

CREATE DATABASE `school`;
USE `school`;
DROP TABLE IF EXISTS `grade`;

CREATE TABLE `grade`(
 `GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
 `GradeName` VARCHAR(50) NOT NULL COMMENT '年级名称',
 PRIMARY KEY (`GradeID`)
)ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `grade`(`GradeID`, `GradeName`) VALUES (1, '大一'), (2, '大二'), (3, '大三'), (4, '大四'), (5, '预科班');

DROP TABLE IF EXISTS `result`;

CREATE TABLE `result`(
 `StudentNo` INT(4) NOT NULL COMMENT '学号',
 `SubjectNo` INT(4) NOT NULL COMMENT '课程编号',
 `ExamDate` DATETIME NOT NULL COMMENT '考试日期',
 `StudentResult` INT(4) NOT NULL COMMENT '考试成绩',
 KEY `SubjectNo` (`SubjectNo`)
)ENGINE INNODB DEFAULT CHARSET=utf8;

INSERT INTO `result`(`StudentNo`, `SubjectNo`, `ExamDate`, `StudentResult`) VALUES (1000, 1, '2013-11-11 16:00:00', 80),
(1001, 1, '2013-11-11 16:00:00', 81), (1002, 1, '2013-11-11 16:00:00', 82), (1003, 1, '2013-11-11 16:00:00', 83), (1004, 1, '2013-11-11 16:00:00', 84),(1005, 1, '2013-11-11 16:00:00', 85), (1006, 1, '2013-11-11 16:00:00', 86), (1007, 1, '2013-11-11 16:00:00', 87), (1008, 1, '2013-11-11 16:00:00', 88),(1000, 1, '2013-11-11 16:00:00', 80);

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student`(
 `StudentNo` INT(4) NOT NULL COMMENT '学号',
 `LoginPwd` VARCHAR(20) DEFAULT NULL,
 `StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
 `Sex` TINYINT(1) DEFAULT NULL COMMENT '性别，取值0或1',
 `GradeId` INT(11) DEFAULT NULL COMMENT '年级编号',
 `Phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空，即可选输入',
 `Address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空，及可选输入',
 `BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
 `Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号，允许为空，即可选输入',
 `IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
 PRIMARY KEY (`StudentNo`),
 UNIQUE KEY `IdentityCard` (`IdentityCard`),
 KEY `Email`(`Email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

INSERT INTO `student` (`StudentNo`, `LoginPwd`, `StudentName`, `Sex`, `GradeId`, `Phone`, `Address`, `BornDate`,`Email`,
`IdentityCard`) VALUES 
(1000, '123', '张三', 1, 1, '123456789', '西安', '2013-11-11 16:00:00', '123@qq.com','12345113674489#'), 
(1001, '123', '李四', 1, 2, '123456789', '北京', '2013-11-11 16:00:00', '123@qq.com', '123453674489#'),
(1002, '123', '王五', 0, 3, '123456789', '西安', '2013-11-11 16:00:00', '123@qq.com', '12341256789#'), 
(1003, '123', '张六', 0, 4, '123456789', '西安', '2013-11-11 16:00:00', '123@qq.com', '1234560041789#'),
(1004, '123', '李七', 1, 5, '123456789', '重庆', '2013-11-11 16:00:00', '123@qq.com', '123124125655789#'), 
(1005, '123', '王八', 0, 1, '123456789', '西安', '2013-11-11 16:00:00', '123@qq.com', '123451164789#'),
(1006, '123', '张久', 0, 2, '123456789', '西安', '2013-11-11 16:00:00', '123@qq.com', '12345678029#'), 
(1007, '123', '李式', 1, 3, '123456789', '西安', NULL, '123@qq.com', '1234524672289#'),
(1008, '123', '赵四', 1, 4, '123456789', '西安', '2013-11-11 16:00:00', '123@qq.com', '12345106789#'),
(1009, '123', '狂神', 1, 5, '123456789', '深证', NULL, '123@qq.com', '789456123');
 
DROP TABLE IF EXISTS `subject`;
 
CREATE TABLE `subject`(
 `SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
 `SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程编号',
 `ClassHour` INT(4) DEFAULT NULL COMMENT '学时',
 `GradeId` INT(4) DEFAULT NULL COMMENT '年级编号',
 PRIMARY KEY (`SubjectNo`)
)ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

INSERT INTO `subject` (`SubjectNo`, `SubjectName`, `ClassHour`, `GradeId`) VALUES (1, 'Java', 110, 1), 
(2, 'JavaScript', 110, 2), (3, 'Python', 110, 3),(4, 'Sql', 110, 4), (5, 'Mysql', 110, 5), (6, 'C', 110, 1), 
(7, 'C++', 110, 2), (8, 'C#', 110, 3), (9, 'PHP', 110, 4);

4.2 查询所有的字段

-- 查询全部的学生  SELECT * FROM 表名
SELECT * FROM student
SELECT * FROM result

-- 查询指定字段  SELECT 字段 FROM 表名
SELECT `StudentName`, `Address` FROM student

-- 别名：给结果（字段、表）起一个名字 AS（不用加''）
SELECT `StudentName` AS 姓名, `Address` AS 地址 FROM student AS s

-- 函数：拼接字符串CONCAT(a, b)
SELECT CONCAT('姓名：', StudentName) AS 新名字 FROM student

去重distinct

作用：去除select查询出来的结果中重复的数据，只显示一条

-- 去重：查询一下有哪些同学参加了考试，有成绩
SELECT * FROM result

-- 查询有哪些同学参加了考试
SELECT `StudentNo` FROM result
-- 发现重复数据，去重
SELECT DISTINCT `StudentNo` FROM result

数据库的列

SELECT VERSION()  -- 查询系统的版本（函数）
SELECT 100 * 3 - 1 AS 计算结果  -- 用来计算（表达式）
SELECT @@auto_increment_increment  -- 查询自增的步长（变量）

-- 学院考试成绩 + 1分
SELECT `StudentNo`, `StudentResult` + 1 AS '提分' FROM result

数据库中的表达式：文本值，列，NULL，计算表达式，系统变量…

select 表达式 from 表名

4.3 where条件子句

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

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

逻辑运算符

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

逻辑运算符尽量用英文

-- 查询考试成绩再85~90分之间的（需要一起选中 select ... where ...）
SELECT `studentNo`, `StudentResult` FROM result
WHERE `StudentResult`>=85 AND `StudentResult`<=90

SELECT `studentNo`, `StudentResult` FROM result
WHERE `StudentResult`>=85 && `StudentResult`<=90

-- 模糊查询（区间）
SELECT `studentNo`, `StudentResult` FROM result
WHERE `StudentResult` BETWEEN 85 AND 90

-- 除了1000号学生之外的同学的成绩
SELECT `studentNo`, `StudentResult` FROM result
WHERE `StudentNo` != 1000;

-- != not
SELECT `studentNo`, `StudentResult` FROM result
WHERE NOT `StudentNo` = 1000;

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为null，则结果为真
IS NOT NULL	a is not null	如果操作符为not null，则结果为真
BETWEEN	a between b and c	若a在b和c之间，则结果为真
LIKE	a like b	SQL匹配，如果a匹配b，则结果为真
IN	a in (a1, a2, a3, …)	假设a在a1、a2、a3…中其中一个值，则结果为真

-- 模糊查询
-- 查询姓刘的同学：like结合 %（代表0到任意个字符）、_（代表一个字符）
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `StudentName` LIKE '张%'  -- 张后面任意个字符

SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `StudentName` LIKE '张_'  -- 张后面一个字符

SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `StudentName` LIKE '张__'  -- 张后面两个字符

-- 查询名字中间有三字的同学 %三%
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `StudentName` LIKE '%三%'

-- ===========in（具体的一个或多个值）===========
-- 查询指定的学生
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `StudentNo` IN (1001, 1002, 1003);

-- 查询在西安的学生
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `Address` IN ('西安', '北京');

-- ===========null、not null===========
-- 查询地址为空的学生
SELECT `StudentNo`, `StudentName` FROM `student`
WHERE `Address` = '' OR `Address` IS NULL

-- 查询有出生日期的同学 不为空
SELECT `StudentNo`, `StudentName`, `BornDate` FROM `student`
WHERE `BornDate` IS NOT NULL

4.4 联表查询

JOIN对比

在这里插入图片描述

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

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
/*思路
1.分析需求，分析查询的字段来自那些表（连接查询）
2.确定使用那种连接查询？共7种
3.确定交叉点（这两个表中那个数据是相同的）
4.判断的条件：学生表中的StudenNo = 成绩表中的StudentNo
join on 连接查询；where 等值查询
*/
SELECT s.`StudentNo`, `StudentName`, `SubjectNo`, `StudentResult` FROM `student` AS s INNER JOIN `result` AS r ON s.`StudentNo` = r.`StudentNo`;  -- on防止值重复出现

-- 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` WHERE `StudentResult` IS NULL;

-- 查询参加了考试的同学信息：学号，学生姓名，科目名，分数
SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult` FROM `student` AS s RIGHT JOIN `result` AS r ON s.`StudentNo` = r.`StudentNo` INNER JOIN `subject` AS sub ON 
r.`SubjectNo` = sub.`SubjectNo`

-- 假设存在一种多张表查询，先查询两张表然后再慢慢增加

自连接（了解）

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

p21自连接用到的数据表

CREATE TABLE `school`.`category`( 
 `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id', 
 `pid` INT(10) NOT NULL COMMENT '父id', 
 `categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字', 
 PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; 

INSERT INTO `category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术'), ('3', '1', '软件开发'), 
('4', '3', '数据库'), ('5', '1', '美术设计'), ('6', '3', 'web开发'), ('7', '5', 'ps技术'), ('8', '2', '办公信息');

父类

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

子类

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

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

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

-- 查询父子信息（不能用ON）
SELECT a.`categoryname` AS '父栏目', b.`categoryname` AS '子栏目' FROM `category` AS a, `category` AS b 
WHERE a.`categoryid` = b.`pid`

练习

-- 查询学员所属的年级（学号，学生的姓名，年级名称）
SELECT `StudentNo`, `StudentName`, `GradeName` FROM `student` AS s INNER JOIN `grade` AS g ON s.`GradeId` = g.`GradeID`

-- 查询科目所属的年级（科目名称、年级名称）
SELECT `SubjectName`, `GradeName` FROM `grade` AS g INNER JOIN `subject` AS s ON g.`GradeID` = s.`GradeId`

-- 查询参加了java考试的同学信息（学号，学生姓名，科目名，分数）
SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult` FROM `student` AS s INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo` INNER JOIN `subject` AS sub ON r.`SubjectNo` = sub.`SubjectNo` WHERE `SubjectName` = 'java'

4.5 分页和排序

排序

-- 排序：升序asc，降序desc
-- 根据查询出的考试成绩降序
SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult` FROM `student` AS s INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo` INNER JOIN `subject` AS sub ON r.`SubjectNo` = sub.`SubjectNo` WHERE `SubjectName` = 'java'
ORDER BY `StudentResult` ASC | DESC

分页

-- 分页：缓解数据库压力，给人的体验更好，瀑布流
-- 分页，每页只显示5条数据；  语法：limit 起始的位置 页面的大小
-- LIMIT 0, 5; 0~4   LIMIT 1, 5; 1~5   LIMIT 5, 5; 5~9  
SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult` FROM `student` AS s INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo` INNER JOIN `subject` AS sub ON r.`SubjectNo` = sub.`SubjectNo` WHERE `SubjectName` = 'java'
ORDER BY `StudentResult` ASC LIMIT 6, 5

4.6 子查询

where（从值是固定的，变为这个值是计算出来的）

本质： 在where语句中嵌套一个子查询语句 where （select * from）

-- 1. 查询数据库结构的所有考试结果（学号，科目编号，成绩）降序排列
-- 方式一：使用连接查询
SELECT `StudentNo`, r.`SubjectNo`, `StudentResult` FROM `result` AS r INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo` WHERE `SubjectName` = 'java' ORDER BY `StudentResult` DESC

-- 方式二： 使用子查询(由里及外)
SELECT `StudentNo`, `SubjectNo`, `StudentResult` FROM `result` WHERE `StudentNo` = (
	SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = 'java')

-- 查询所有Java学生的学号
SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = 'java'


-- 2. 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`, `StudentName`, `StudentResult` FROM `student` AS s INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo` WHERE `StudentResult` >= 80

-- 在这个基础上增加一个科目（子查询）
SELECT DISTINCT s.`StudentNo`, `StudentName`, `StudentResult` FROM `student` AS s INNER JOIN `result` AS r
ON s.`StudentNo` = r.`StudentNo` WHERE `StudentResult` >= 80 AND `SubjectNo` = (
SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = 'java')

 -- 查询课程为java且分数不小于80的同学的学号和姓名（连表查询）
 SELECT s.`StudentNo`, `StudentName`, `SubjectName`, `StudentResult` FROM `student` AS s INNER JOIN
 `result` AS r ON s.`StudentNo` = r.`StudentNo` INNER JOIN `subject` AS sub ON 
 sub.`SubjectNo` = r.`SubjectNo` WHERE `SubjectName` = 'java' AND `StudentResult` >= 80
 
 -- 改造（由里及外）
 SELECT `StudentNo`, `StudentName` FROM `student` WHERE `StudentNo` IN (
	SELECT `StudentNo` FROM `result` WHERE `StudentResult` > 80 AND `SubjectNo` =(
		SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = 'java'
	)
 )
 
-- 查询java前五名同学的信息（学号，姓名，分数）使用子查询
 SELECT s.`StudentNo`, `StudentName`, `StudentResult` FROM `student` AS s INNER JOIN `result` AS r
 ON s.`StudentNo` = r.`StudentNo` WHERE r.`SubjectNo` = (
	SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = 'java'
) ORDER BY `StudentResult` DESC LIMIT 0, 5

4.7 分组和过滤

-- 查询不同课程的平均分，最高分，最低分，核心：根据不同课程分组
SELECT `SubjectName`, AVG(`StudentResult`) AS 平均分, MAX(`StudentResult`)AS 最高分, MIN(`StudentResult`) AS 最低分
FROM `result` AS r INNER JOIN `subject` AS sub ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.`SubjectNo`  -- 通过什么字段分组
HAVING AVG(`StudentResult`) >= 80  -- 分组过滤

5 MySQL函数

官网：https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html

5.1 常用函数

-- 常用函数
SELECT ABS(-8)  -- 绝对值
SELECT CEILING(9.4)  -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND()  -- 返回一个0~1的随机数
SELECT SIGN(10)  -- 判断一个数的符号 正数返回1，0返回0，负数返回-1
 
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航')  -- 字符串长度
SELECT CONCAT('我', '爱', '你们')  -- 拼接字符串
SELECT INSERT('我爱编程hellworld', 1, 2, '超级热爱')  -- 查询，从某个位置开始替换
SELECT LOWER('KUANGSHEN')  -- 转小写
SELECT UPPER('kuangshen')  -- 转大写
SELECT INSTR('kuangshen', 'n')  -- 返回第一次出现的子串索引
SELECT REPLACE('坚持就能成功', '坚持', '努力')  -- 替换出现的指定字符串
SELECT SUBSTR('坚持就能成功', 4, 6)  -- 返回指定位置长度的字符串（源字符串，截取的位置，截取的长度）
SELECT REVERSE('kuangshen')  -- 反转字符

-- 查询姓赵的同学，将他的名字改为邹
SELECT REPLACE(`StudentName`, '赵', '邹') FROM `student` WHERE `StudentName` LIKE '赵%'

-- 时间和日期函数（重要）
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（）	最小值
…	…

-- 聚合函数
-- count()都能统计表中的数据
SELECT COUNT(`BornDate`) FROM `student`;  -- count(指定列(字段))：会忽略所有的null值
SELECT COUNT(*) FROM `student`;  -- count(*)：不会忽略null值，本质计算所有的行数
SELECT COUNT(1) FROM `result`;  -- count(1)：不会忽略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`

5.3 数据库级别的MD5加密（扩展）

主要增强算法复杂度和不可逆性，MD5不可逆，具体值的MD5是一样的，MD5破解网站的原理背后有一个字典（MD5加密前后的值）

-- 测试MD5 加密
CREATE TABLE `testmd5`(
  `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 `testmd5` VALUES(1, '张三', '123456'), (2, '李四', '123456'), (3, '王五', '123456')

-- 加密
UPDATE testmd5 SET `pwd` = MD5(`pwd`) WHERE `id` = 1
UPDATE testmd5 SET `pwd` = MD5(`pwd`)  -- 加密全部的密码

-- 插入时加密
INSERT INTO `testmd5` VALUES(4, '小明', MD5('123456'))

-- 如何较验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name` = '小明' AND pwd = MD5('123456')

6 事务

6.1 什么是事务

SQL 执行 A给B转账 A 1000 → B 200 B 200
SQL 执行 B收到A的钱 A 800 → B 200

事务：将一组SQL放在一个批次中去执行

事务原则：ACID原则，包括原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）
- 原子性：两个步骤一起成功，或者一起失败，不能只发生其中一个动作
- 一致性：事务前后的数据完整性要保持一致
- 隔离性：针对多个用户同时操作，主要是排除其他事务对本次事务的影响
  - 脏读：一个事务读取了另一个事务未提交的数据
  - 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同（不一定是错误，某些场合不对）
  - 虚读（幻读）：在一个事务内读取到了别的事务插入的数据，导致前后读取不一致（一般是多了一行）
- 持久性：表示事务结束后的数据不随着外界原因导致数据丢失（事务没有提交会恢复到原状，一旦提交会持久到数据库中）
执行事务

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

-- 手动开启事务
SET autocommit = 0  -- 关闭自动提交

-- 事务开启
START TRANSACTION  -- 标记一个事务的开始，从这个之后的sql都在一个事务内
INSERT xx
INSERT xx

-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK  -- 类似Java中 try...catch 的catch中的语句

-- 事务结束
SET autocommit = 1 -- 开启自动提交

-- 保存点（了解）
SAVEPOINT 保存点名 -- 这是一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名  -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销保存点

模拟场景

-- 转账
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `account` (
  `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 `account` (`name`, `money`) VALUES ('A', 2000.00), ('B', 10000.00)

-- 模拟转账
SET autocommit = 0;  -- 关闭自动提交
START TRANSACTION  -- 开启一个事务（一组事务）

UPDATE `account` SET `money` = `money` - 500 WHERE `name` = 'A'  -- A减500
UPDATE `account` SET `money` = `money` + 500 WHERE `name` = 'B'  -- B加500

COMMIT;  -- 提交事务
ROLLBACK;  -- 回滚
SET autocommit = 1;  -- 恢复默认值

7 索引

MySQL 官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

MySQL 索引背后的数据结构及算法原理：https://blog.codinglabs.org/articles/theory-of-mysql-index.html

7.1 索引的分类

主键索引：PRIMARY KEY
- 唯一的标识，不可重复，只能有一个列作为主键
唯一索引：UNIQUE KEY
- 避免重复的列值出现，可以有多个唯一索引
常规索引：KEY / INDEX
- 默认的
全文索引：FULLTEXT
- 在特定的数据库引擎下才有（MyISAM），快速定位数据
基础语法

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

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

-- 增加一个索引：索引名（列名）
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentName` (`studentName`);

-- 分析sql执行的状况
-- explain：https://blog.csdn.net/jiadajing267/article/details/81269067
EXPLAIN SELECT * FROM student;  -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(`studentName`) AGAINST('赵')

7.2 测试索引

p31所用的表

CREATE TABLE `app_user` (
 `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(50) DEFAULT'' COMMENT '用户昵称',
 `email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
 `phone` VARCHAR(20) DEFAULT'' COMMENT '手机号',
 `gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0：男;1:女）',
 `password` VARCHAR(100) NOT NULL COMMENT '密码',
 `age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
 `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
 `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

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

-- 插入100万条数据
DELIMITER $$  -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i<num DO
	-- 插入语句
	INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`) 
	VALUES(CONCAT('用户', i), '24736743qq.com', 
		CONCAT('18', FLOOR(RAND() * ((999999999-100000000) + 100000000))),
		FLOOR(RAND() * 2), UUID(), FLOOR(RAND() * 100));	
	SET i = i+1;
  END WHILE;
  RETURN i;
END;

SELECT mock_data();

SELECT * FROM app_user WHERE `name` = '用户9999';  -- 0.847 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';  -- rows: 993105

-- id_表名_字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = '用户9999';  -- 0.004 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';  -- rows: 1

7.3 索引原则

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

Hash类型的索引 Btree：InnoDB的默认数据结构

PS：学习自狂神说java

Yy_sushine

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
【狂神说Java】阶段三笔记2. MySQL-02

文章目录4 DQL查询数据（最重点）4.1 DQL4.2 查询所有的字段4.3 where条件子句4.4 联表查询4.5 分页和排序4.6 子查询4.7 分组和过滤5 MySQL函数5.1 常用函数5.2 聚合函数（常用）5.3 数据库级别的MD5加密（扩展）4 DQL查询数据（最重点）4.1 DQLselect完整语句SELECT [ALL | DISTINCT]{* | talbe.* | [table.field1[AS alias1][,[table.field2[AS alias2]
复制链接

扫一扫