MySQL笔记

最新推荐文章于 2024-08-19 17:47:45 发布

hyacinth_W

最新推荐文章于 2024-08-19 17:47:45 发布

阅读量41

点赞数

文章标签： mysql 笔记

本文链接：https://blog.csdn.net/hyacinth_W/article/details/132907459

版权

1 MySQL基本命令

mysql -uroot -p123456  --连接数据库
flush privileges;	--刷新权限
----------------------------------------------------------
show databases; --查看所有数据库
use school	--切换数据库,use+数据库名
show tables;	--查看数据库中所有的表
describe student;	--显示数据库中表的所有信息

-- 单行注释
/*
多行注释
*/

数据库xx语言

DDL 定义
DML 操作
DQL 查询
DCL 控制

2 操作数据库

操作数据库

create database [if not exists] 数据库名;	--创建数据库

drop database [if exists] 数据库名;	--删除数据库

use `数据库名`;	--使用数据库,如果表名或者字段名是特殊字符,则需要加``

show databases;	--查看所有数据库

数据库的数据类型

数值
tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小数据 3个字节
int 整数 4个字节
bigint 较大数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式浮点数(金融计算使用)
字符串
char 字符串固定大小 0~255
varchar 可变字符串 0~65535
tinytext 微型文本 2^8-1
text 文本串 2^16-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来填充

自增
自动在上一条记录的基础上加一;
通常用来设计唯一的主键index,必须是整数类型
可以自定义主键自增的起始值

非空
不赋值就会报错

默认
设置默认值

拓展 --表的字段规范
id		主键
`version`		乐观锁
is_delete		伪删除
gmt_create	创建时间
gmt_update	修改时间

创建数据库表

create table if not exists `student` (	
`id` int(4) not null auto_increment comment '学号',
`name` varchar(10) not null default '匿名' comment '姓名',
`pwd` varchar(20) not null default '123456' comment '密码',
`gender` 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

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

关于数据库引擎:
innodb(默认使用)
myisam
在这里插入图片描述
物理空间存在的位置

修改删除表字段

--修改表名
--alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1	

--增加字段
--alter table 表名 add 字段名 列属性
alter table teacher1 add age int(11)

--修改表的字段(重命名,修改约束)
--alter table 表名 modify 字段名 列属性[]
alter table teacher1 modify age varchar(10) --修改约束
alter table teacher1 change age age1 varchar(10) --重命名字段

--删除表字段
alter table teacher1 drop age1

--删除表(如果表存在再删除)
drop table if exists teacher1

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

注意:
字段名使用这个包裹;
注释使用-- /**/
sql关键字大小写不敏感,建议使用小写
所有的符号使用英文

3 MySQL数据管理

外键

CREATE TABLE `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid字段，要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用），references引用

CREATE TABLE IF NOT EXISTS `student` (	
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`gender` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`gradeid` INT(10) NOT NULL COMMENT '年级',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表时,要删除引用别人的表(从表),再删除被引用的表(主表)
第二种方式:

-- alter table `表` add constraint `约束名` foreign key(`作为外键的列`) references `哪张表`(`哪个字段`)
ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

以上操作都是物理外键，数据库级别的外键，，不建议使用。（避免数据库过多造成困扰）

最佳方式
1、数据库就是单纯的表，只用来存数据，，只有行（数据）和列（字段）；
2、我们想使用多张表的数据，想使用外键，用程序去实现。

DML语言(全部记住)

**数据库意义:**数据存储，数据管理
DML语言：数据操作语言
insert、update、delete

添加

-- insert into `表名`(`字段名1`,`字段名2`,`字段名3`,...) values('值1'),('值2'),('值3'),...
INSERT INTO `grade` (`gradename`) VALUES ('大四');
-- 如果不写字段名,则默认添加所有字段
-- insert into `grade` values('1','大三');
-- = insert into `grade`(`gradeid`,`gradename`) values('1','大三');

-- 添加多个值
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES('2','大三');
-- 往同一个字段插入多个值时,要把值用括号隔开
INSERT INTO `student`(`name`,`pwd`,`gender`) 
VALUES('赵一','11111','男'),('钱二','22222','男'),('孙三','33333','女');

注意事项：
1、字段和字段之间使用英文逗号隔开
2、字段是可以省略的，但后面的值必须一一对应
3、可以同时插入多条数据，values后面的值，需要使用逗号隔开，values(),(),…

修改

-- 修改表，带条件
UPDATE `student` SET `name`='测试' WHERE id=1;
-- 修改表不带条件默认修改所有的值
UPDATE `student` SET `pwd`='000';
-- 修改多个字段
UPDATE `student` SET `name`='测试',`email`='234@www.com' WHERE id=1;

语法：update 表名 set colnum_name = value,[colnum_name = value] where [条件]

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5<>6	true
>	大于	6>4	true
<	小于	4<6	true
<=	小于等于	4<=6	true
>=	大于等于	6>=4	true
between…and…	在某个范围内	[2,5]	true
and	&&	5>1and1<2	false
or	或	5>1or1>2	true

注意:
1、colnum_name是数据库的列,尽量戴上```
2、条件,筛选的条件如果没有指定,则会修改所有的列
3、value是一个具体值，也可以是一个变量

UPDATE `student` SET `birthday`=CURRENT_TIME WHERE id BETWEEN 1 AND 3;

4、设置多个属性之间，用英文逗号隔开

删除

-- 删除数据（避免这样写）
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` WHERE id = 4;

-- 清空表
TRUNCATE TABLE `student`

delete和truncate都能删除表数据，但是truncate可以重置自增列切不会影响事务。

delete删除问题
innodb 自增列会从1开始（数据存在内存当中，断电即失）
myisam 继续从上一个自增量开始（存在文件当中，不会丢失）

DQL语言（查询语言）

DQL（Data Query Language）

1、所有的查询操作都用它 select
2、简单或复杂的查询都能做
3、数据库中最核心的语言，最重要的语句
4、使用频率最高的语句

select语法：
在这里插入图片描述

查询指定字段

-- 查询所有的学生
SELECT * FROM `student`

-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`

-- 别名，AS，可以给字段起也可以给表起
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM `student` AS 学生表

-- 拼接字符 concat
SELECT CONCAT('学生姓名：',studentname) FROM `student`

语法：select 字段,… from 表名

去重查询

-- 去重查询 distinct
SELECT `studentno` FROM `result` -- 有重复数据
SELECT DISTINCT `studentno` FROM `result` -- 去重查询

作用:去除重复的数据

数据库的列(表达式)

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

数据库中的表达式：文本值、列、null、函数、计算表达式、系统变量…
select 表达式 from 表

where条件字句

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

运算符	语法	描述
and &&	a and b a&&b	与，两个真则真
or	a or b	或，一个为真则真
not !	not a !a	非，真为假，假为真

-- 带where查询
--  查询subjectno在2到7之间,使用and &&
SELECT `subjectno`,`gradeid` FROM `subject` WHERE `subjectno`>=2 AND `subjectno`<=7
-- 使用模糊查询
SELECT `subjectno` FROM `subject` WHERE `subjectno` BETWEEN 2 AND 7
-- 查询除了1以外的subjectno
SELECT `subjectno` FROM `subject` WHERE `subjectno` != 1
SELECT `subjectno` FROM `subject` WHERE NOT `subjectno`=1

模糊查询(比较运算符)

运算符	语法	描述
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 (a1,a2,a3,…)	假设a在a1或a2…中的某一个值,则真

-- 查询C开头的学科 like结合%(表示0到任意个字符)和_(一个字符)
SELECT `subjectname` FROM `subject`
WHERE `subjectname` LIKE 'C%'
-- 查询5个字符的学科
SELECT `subjectname` FROM `subject`
WHERE `subjectname` LIKE '_____'
-- 查询名字中带有“数”的学科
SELECT `subjectname` FROM `subject`
WHERE `subjectname` LIKE '%数%'

-- =====in（具体的一个值或者多个值）=====
-- 查询课时110个小时的学科
SELECT `subjectname` FROM `subject`
WHERE `classhour` IN (110)
-- 查询课时110和230个小时的学科
SELECT `subjectname` FROM `subject`
WHERE `classhour` IN (110,230)
-- 查询课时为空的学科
SELECT `subjectname` FROM `subject`
WHERE `classhour` IS NULL
-- 查询课时不为空的学科
SELECT `subjectname` FROM `subject`
WHERE `classhour` IS NOT NULL

联表查询

请添加图片描述

操作	描述
Inner join	如果联表中至少有一个匹配,就返回结果
Left join	会从左表中返回所有的值,即使右表中没有匹配
Right join	会从右表中返回所有的值,即使左表中没有匹配

-- ============联表查询===============
-- 查询参加考试的学生信息（双表）
SELECT r.studentno,`studentname`,`subjectno`,`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno

SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student AS s
LEFT JOIN result AS r 
ON s.studentno = r.studentno

SELECT s.studentno,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno

-- 在学生考试信息中再加上科目（三表）
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`;

-- 查询学员所属的年级(学号，学生姓名，年级）
SELECT `studentno`,`studentname`,`gradename`
FROM student AS s
INNER JOIN grade AS g
ON s.`gradeid` = g.`gradeid`;

-- 查询科目所属的年级
SELECT `subjectno`,`subjectname`,`gradename`
FROM `subject` s
INNER JOIN grade g
ON s.`gradeid` = g.`gradeid`;

-- 查询参加了高等数学1考试的学生信息
SELECT s.`studentno`,`studentname`,`studentresult`,`subjectname`
FROM student s
RIGHT JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-1';

自连接

自己的表和自己连接，核心：一张表拆分成两张一样的表
父类：

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

子类：

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

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

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

-- =====自连接=========
-- 查询父子关系（把一张表看做两张表）
SELECT a.`categoryName`,b.`categoryName`
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`;

分页和排序

-- =========排序（ASC升序，DESC降序）===============
-- order by 要排序的字段 升序还是降序
-- 成绩升序排列
SELECT s.`studentno`,`studentname`,`studentresult`,`subjectname`
FROM student s
RIGHT JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY `studentresult` ASC;

-- =======分页==================
-- limit 起始值，页面大小
-- 页面大小：pagesize
-- 起始值：（n-1）* pagesize
-- 当前页：n
-- 总页数=数据总数/页面大小
SELECT s.`studentno`,`studentname`,`studentresult`,`subjectname`
FROM student s
RIGHT JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY `studentresult` ASC
LIMIT 0,1;

子查询

where（这个值是计算出来的）
本质：在where语句中嵌套一个子查询语句

-- ==========where=============
-- 1、查询数据库结构-1的所有考试结果（学号、科目编号、成绩），降序排列
-- 方式一、联接查询
SELECT s.`studentno`,r.`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '数据库结构-1'
ORDER BY studentresult DESC;

-- 方式二、子查询
SELECT `studentno`,`subjectno`,`studentresult`
FROM result
WHERE `subjectno` = (
	SELECT subjectno FROM `subject`
	WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC;

-- 查询高等数学-1分数大于等于80分的学生信息
-- 联表查询
SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-1' AND `studentresult` >= 80
-- 子查询
SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` >= 80 AND subjectno = (
	SELECT subjectno FROM `subject`
	WHERE subjectname = '高等数学-1'
)
-- 嵌套子查询
SELECT DISTINCT `studentno`,`studentname` FROM student WHERE studentno IN (
	SELECT studentno FROM result WHERE studentresult > 80 AND subjectno = (
		SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-1'
	)
)

分组和过滤

-- 查询不同课程的平均分,最高分,最低分,平均分大于80
SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY subjectname -- group by 要分组的字段
HAVING AVG(`studentresult`) > 80 -- having后面只能跟聚合函数

MySQL函数

常用函数（并不常用）

-- 数学运算
SELECT ABS(-8)	-- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回0-1的随机数
SELECT SIGN(-10) -- 判断一个数的符号,0返回0,负数返回-1,正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('字符串长度') -- 字符串长度
SELECT CONCAT('早','上') -- 拼接字符串
SELECT INSERT('helloworld',1,2,'中午午饭') -- 插入,替换(从某个位置替换某个长度)
SELECT LOWER('AJD') --  小写字母
SELECT UPPER('AJD') -- 大写字母
SELECT INSTR('abcdefg','d') -- 返回第一次出现的子串的索引
SELECT REPLACE('今天星期四','今天','123') -- 替换出现的指定字符串
SELECT SUBSTR('马上要国庆了',4,6) -- 返回指定的字符串(源字符串,截取的位置,截取的长度)
SELECT REVERSE('1234') -- 反转

-- 查询姓张的人,替换成姓赵
SELECT * FROM student
WHERE studentname LIKE ('张%')
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() -- 版本

聚合函数（常用）

函数名	描述
count（）	计数
sum（）	求和
avg（）	平均值
min（）	最小值
max（）	最大值
…	…

-- 区别:
-- 想查询一个表中有多少个记录,就用count()
 SELECT COUNT(studentname) FROM student; -- count(指定列),会忽略所有的null值
 SELECT COUNT(*) FROM student; -- count(*),不会忽略null值,本质计算行数
 SELECT COUNT(1) FROM student; -- count(1),不会忽略null值,本质计算行数

SELECT SUM(studentresult) AS 总和 FROM result;
SELECT AVG(studentresult) AS 平均 FROM result;
SELECT MIN(studentresult) AS 总和 FROM result;
SELECT MAX(studentresult) AS 总和 FROM result;

数据库级别MD5加密

什么是MD5?
MD5是“Message Digest Algorithm 5”的缩写。它是一个广泛使用的加密哈希函数，可以产生一个128位（16字节）的哈希值，通常以32字符的十六进制数字形式表示。

一些主要点：
不可逆性：理论上，从MD5的输出（哈希值）是无法恢复或得知其原始输入的。
唯一性：理论上，两个不同的输入不会有相同的输出哈希值。
快速计算：对任何给定的输入，很容易和快速地计算出其哈希值。

请注意以下几点：
安全性问题：近年来，MD5已经不再被视为加密标准，因为它容易受到所谓的“碰撞攻击”。这意味着存在两个不同的输入可以得到相同的输出哈希值。
用途：尽管存在安全性问题，但MD5仍然广泛用于一些不需要高度安全性的场景，如检查文件完整性。
如果您在进行涉及敏感数据或需要高度安全性的操作，建议使用更安全的哈希算法，如SHA-256。

-- 测试MD5
CREATE TABLE `testMD5`(
  `id` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` VARCHAR(20) NOT NULL COMMENT '名字',
  `pwd` VARCHAR(20) NOT NULL COMMENT 'pwd',
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO testMD5
VALUES(1,'aaa','111'),(2,'bbb','222'),(3,'ccc','333'),
(4,'ddd','444');

ALTER TABLE testMD5 MODIFY pwd VARCHAR(32)

-- MD5加密
UPDATE testMD5 SET pwd = MD5(pwd); --  全部加密

-- 添加数据时就进行加密
INSERT INTO testMD5 VALUES(5,'eee',MD5('123456'));

-- 效验：将用户传进来的值进行md5加密后进行比对
SELECT * FROM testMD5 WHERE `name` = 'eee' AND pwd = MD5('123456');

事务

数据库事务是一个作为单一逻辑工作单元执行的操作序列。这些操作要么全部完成，要么全部不完成，不会停留在中间某个环节。事务的存在是为了确保数据的完整性和一致性。

事务主要由以下四个属性定义，经常被称为ACID属性：

**原子性 (Atomicity)：**事务是一个原子操作单元，其对数据的修改要么全部执行，要么全部不执行。
**一致性 (Consistency)：**事务必须保证数据库从一个一致性状态转到另一个一致性状态。一致性与业务规则有关，例如“银行帐户余额不应为负值”。
**隔离性 (Isolation)：**在并发环境中，一个事务的执行不应由其他事务干扰。即事务对数据的修改在结束前对其他事务是不可见的。
**持久性 (Durability)：**事务完成后，对数据的修改是永久的，即使发生系统故障也不会丢失。
为了实现这些属性，数据库系统提供了一系列的事务控制命令，如COMMIT、ROLLBACK和SAVEPOINT：

COMMIT：保存所有事务所做的更改。
ROLLBACK：撤销事务所做的更改。
SAVEPOINT：在事务中设置一个临时的标记点，可以后续到达该点进行回滚。

隔离性导致的问题:
**脏读 (Dirty Read)：**一个事务读取了另一个未提交事务的数据。例如，事务A修改了一个值，但还没有提交；而事务B在此时读取了这个值。如果事务A最后决定回滚，那么事务B读到的数据就是无效的。

不可重复读 (Non-Repeatable Read)：在同一事务内的多次读操作之间，由于另一个事务的修改导致数据不一致。例如，事务A读取了一个值，然后事务B修改了这个值并提交。当事务A再次读取这个值时，它会发现值已经改变。

幻读 (Phantom Read)：在一个事务内，多次查询返回的结果集不一致，因为另一个事务插入或删除了一些行。例如，事务A查询了满足某些条件的所有行，然后事务B插入了一些新的满足这些条件的行并提交。当事务A再次查询时，会发现有些“幻影”行出现。

**丢失更新 (Lost Update)：**两个事务都读取了一个值，然后基于这个读到的值进行了修改并提交。后提交的事务会覆盖先提交的事务的修改，导致先提交的事务的修改丢失。

数据库隔离级别及其可能出现的并发问题的表格：

隔离级别	脏读	不可重复读	幻读
读未提交(Read Uncommitted)	可能	可能	可能
读已提交(Read Committed)	不可能	可能	可能
可重复读(Repeatable Read)	不可能	不可能	可能
串行化(Serializable)	不可能	不可能	不可能

模拟事务

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

-- 手动处理事务
SET autocommit = 0; -- 关闭自动提交事务

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

-- 提交（成功的话）
COMMIT

-- 回滚（失败的话）
ROLLBACK

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

SAVEPOINT -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT -- 回滚到某一个事务保存点
RELEASE SAVEPOINT -- 撤销保存点

-- 模拟场景
CREATE DATABASE bank CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE bank;

CREATE TABLE `account` (
  `account_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '账户id',
  `account_number` VARCHAR(20) NOT NULL COMMENT '账户号',
  `account_name` VARCHAR(50) NOT NULL COMMENT '账户名',
  `balance` DECIMAL(15,2) DEFAULT 0.00 COMMENT '余额', 
  -- decimal(15,2)表示精确数值的数据类型，
  -- 15：总共可以存储15位数字。这包括小数点左侧和右侧的数字。
  -- 2：小数点右侧可以存储的最大位数。
  `date_created` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `last_modified` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  PRIMARY KEY(`account_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

INSERT INTO account (account_number, account_name, balance, date_created)
VALUES 
('A0001', '主分行', 10000.00, '2023-09-28'),
('A0004', '南分行', 2500.25, '2023-06-10');

-- 模拟事务
 SET autocommit = 0; -- 关闭自动提交
 START TRANSACTION; -- 开启一个事务
 
 UPDATE account SET balance = balance - 5000 WHERE account_name = '主分行'; -- -5000
 UPDATE account SET balance = balance + 5000 WHERE account_name = '西分行'; -- +5000
 
 COMMIT; -- 提交，一旦提交则被持久化了
 ROLLBACK; -- 回滚
 
 SET autocommit = 1;

索引

索引（index）：是帮助MySQL高效获取数据的数据结构。提取句子主干就可以获得索引的本质：索引是数据结构。

索引的分类

**主键索引（primary key）：**每个表只能有一个主键索引。它保证表中每一行数据的唯一性。
**唯一索引（unique key）：**确保索引的所有值都是唯一的（避免重复列），但一个表可以有多个唯一索引。
**普通索引（key/index）：**没有任何限制。
**全文索引（fulltext）：**用于文本字段上，能够快速找到文本中的关键词。
**复合索引（Composite Index）：**在两个或多个列上的索引。

--  索引的使用
-- 1、创建表的时候给字段增加索引
-- 2、创建表完毕后，增加索引
-- 显示所有的索引信息
show index from student;

--增加一个全文索引
alter table school。student add fulltext index `studentname`(`studentname`);

--explain 分析sql执行情况
explain select * from student; == 非全文索引

select * from student where match(studentname) against('a');

测试索引

DELIMITER //

-- 创建一个名为InsertLotsOfUsers的存储过程
CREATE PROCEDURE InsertLotsOfUsers()
BEGIN
    -- 声明一个名为counter的整数变量，默认值为1，用于跟踪循环的迭代次数
    DECLARE counter INT DEFAULT 1;
    
    -- 声明一些变量来存储随机生成的用户信息
    DECLARE randomName VARCHAR(50);
    DECLARE randomEmail VARCHAR(50);
    DECLARE randomPhone VARCHAR(20);
    DECLARE randomGender TINYINT(4);
    DECLARE randomAge TINYINT(4);
    
    -- 使用WHILE循环来插入10万条数据
    WHILE counter <= 100000 DO
        -- 为每个用户生成一个独特的名称
        SET randomName = CONCAT('User', counter);
        -- 为每个用户生成一个独特的电子邮件地址
        SET randomEmail = CONCAT('user', counter, '@example.com');
        -- 生成随机电话号码
        SET randomPhone = CONCAT('139', LPAD(FLOOR(RAND() * 100000000), 8, '0'));
        -- 随机选择性别（0或1）
        SET randomGender = FLOOR(RAND() * 2);
        -- 生成随机年龄（0至99岁）
        SET randomAge = FLOOR(RAND() * 100);
        
        -- 使用INSERT INTO语句将随机生成的数据插入到app_user表中
        INSERT INTO app_user (name, email, phone, gender, pwd, age) 
        VALUES (randomName, randomEmail, randomPhone, randomGender, 'password', randomAge);
        
        -- 增加counter的值，继续下一次循环
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;
CALL InsertLotsOfUsers();
SELECT * FROM app_user WHERE `name` = 'User99999'; -- 0.130 sec
-- id_表名_字段名
-- create index 索引名 on 表(字段)
create index id_app_user_name on app_user(`name`);
SELECT * FROM app_user WHERE `name` = 'User99999';-- 0 sec

索引在小数据量的时候,用处不大,但在大数据量时,效果明显.

索引原则

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

索引的数据结构

Hash类型的索引
Btree Innodb的默认数据结构
[http://blog.codinglabs.org/articles/theory-of-mysql-index.html]

数据库权限管理和备份

用户管理

用户表：mysql.user
本质:对这张表进行增删改查

-- 创建用户
create user xxx identified by '123456'

-- 修改密码
set password = password('1111') -- 修改当前用户
set password for xxx = password('1111')

-- 重命名
rename user xxx to xxx1

-- 用户授权 grant all privilege 全部的权限 on 库.表
-- 所有权限都有,除了给别人授权
grant all privilege on *.*

-- 查看权限
show grant for xxx1 -- 查看指定用户权限
show grant for root@localhost
-- root用户的权限:GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限 revoke 哪些权限,在哪个库撤销,给谁撤销
revoke all privileges on *.* from xxx1

-- 删除用户
drop user xxx1

MySQL备份

为什么要备份:
1、保证重要的数据不丢失
2、数据转移

MySQL数据库备份的方式：
1、拷贝物理文件
2、在可视化工具中导出
3、使用命令行导出 mysqldump

-- 导出
# mysqldump -h主机 -u用户名 -p密码 表1 [表2 表3...] >物理磁盘位置/文件名
-- 导入(登录情况下,切换要导入的数据库)
# source 文件路径

规范数据库设计

为什么要设计数据库

糟糕的数据库设计:
1、数据冗余，浪费空间
2、数据插入和删除会很麻烦、异常
3、程序性能差

良好的数据库设计：
1、节省内存
2、保证数据完整性
3、便于开发

软件开发中，关于数据库的设计：
分析需求：分析业务和需要处理的数据库的需求
概要设计：设计关系图，E-R图

三大范式

第一范式 (1NF):
- 定义：一个关系处于第一范式当且仅当它的属性域不包
- 含任何集合、列表或多值属性。简而言之，每一列都是原子
- 性的，并且每一行都有一个唯一的标识。
- 实现方法：
  - 确保每一列都包含不可分割的数据值（原子性）。
  - 删除所有重复的数据行。
  - 为每一行定义一个唯一的标识，例如使用主键。
第二范式 (2NF):
- 定义：一个关系处于第二范式当且仅当它处于第一范
- 式，并且所有非键属性完全函数依赖于整个候选键。
- 实现方法：
  - 确保表满足第一范式。
  - 为复合主键设计的表，确保非键属性完全依赖于整个复合
  - 键，而不是仅依赖于复合键的某一部分。
  - 将部分依赖的属性移至新表，并将原主键与之相关联。
第三范式 (3NF):
- 定义：一个关系处于第三范式当且仅当它处于第二范
- 式，并且所有非键属性都不传递函数依赖于候选键。
- 实现方法：
  - 确保表满足第二范式。
  - 删除非键属性之间的传递依赖关系。例如，如果A依赖于
  - B，B依赖于C，那么A间接依赖于C，这种情况应该被消
  - 除。
  - 对于每一个传递依赖，创建新的表。

这些范式的目的是降低数据冗余、消除数据异常并确保数据的逻辑一致性。但是，对于某些实际应用来说，完全规范化的数据模型可能会导致性能问题，因此可能需要权衡范式与性能之间的关系。

JDBC（重点）

数据库驱动

不同的数据库需要不同的数据库驱动操作，为了方便，我们使用JDBC

JDBC

当然，主人～🌸。让我为您详细地解释一下JDBC：

JDBC (Java Database Connectivity) 是Java语言中的一个标准API，用于连接数据库、发送SQL查询和管理数据库结果。它允许Java程序与多种关系数据库进行交互，例如MySQL、Oracle、PostgreSQL等。

JDBC的主要组件和概念：

驱动程序 (Driver)：为特定的数据库提供的实现，用于与该数据库通信。例如，为了从Java程序访问MySQL数据库，您需要MySQL的JDBC驱动。
DriverManager：管理一组JDBC驱动程序。当与数据库建立连接时，DriverManager负责选择合适的驱动。
Connection：表示与数据库的物理连接。使用这个连接，Java应用程序可以向数据库发送SQL语句。
Statement：用于在已经建立的连接上执行SQL语句。
ResultSet：表示SQL查询的结果。它基本上是一个数据表，允许您从中检索值。
PreparedStatement：它是Statement的扩展，用于执行预编译的SQL语句。它更高效并有助于防止SQL注入攻击。

基本的JDBC工作流程：

加载数据库驱动。
使用DriverManager建立与数据库的连接。
使用Connection对象创建一个Statement或PreparedStatement对象。
使用Statement执行SQL查询。
如果查询返回任何结果，处理这些结果（使用ResultSet）。
关闭连接和其他相关资源。

示例：

// 1. 加载MySQL JDBC驱动
Class.forName("com.mysql.jdbc.Driver");

// 2. 建立数据库连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "username", "password");

// 3. 创建Statement
Statement statement = connection.createStatement();

// 4. 执行SQL查询
ResultSet resultSet = statement.executeQuery("SELECT * FROM mytable");

// 5. 处理查询结果
while (resultSet.next()) {
    String name = resultSet.getString("name");
    System.out.println(name);
}

// 6. 关闭资源
resultSet.close();
statement.close();
connection.close();

hyacinth_W

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL笔记

例如，事务A查询了满足某些条件的所有行，然后事务B插入了一些新的满足这些条件的行并提交。而事务B在此时读取了这个值。**丢失更新 (Lost Update)：**两个事务都读取了一个值，然后基于这个读到的值进行了修改并提交。后提交的事务会覆盖先提交的事务的修改，导致先提交的事务的修改丢失。**原子性 (Atomicity)：**事务是一个原子操作单元，其对数据的修改要么全部执行，要么全部不执行。**持久性 (Durability)：**事务完成后，对数据的修改是永久的，即使发生系统故障也不会丢失。
复制链接

扫一扫