mysql

常用语法

在 windows 中不区分大小写，在 linux 中区分大小写。

1 基本命令

所有的语句都要以分号结尾

• show databases; –查看当前所有的数据库
• use 数据库名; –打开指定的数据库
• show tables; –查看所有的表
• describe/desc 表名; –显示表的信息
• create database 数据库名; –创建一个数据库
• exit –退出连接

单行注释：

• -- 或 #

多行注释：

• /*...*/

2. 操作数据库

2.1 操作数据库

1. 创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名;

2. 删除数据库

DROP DATABASE [IF EXISTS] 数据库名;

3. 使用数据库

如果表名或字段名是特殊字符，则需要带``。

use 数据库名;

4. 查看数据库

SHOW DATABASES;

2.2 数据库的列类型

数值类型

数据类型	描述	大小
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: 没有值，未知，不要用于计算
• UnSigned: 无符号的，不能为负数
• ZEROFILL: 0 填充的，不足位数用 0 填充
• Auto_InCrement: 自增，通常用于设计唯一主键

其他属性

• NULL 和 NOT NULL: 默认为 NULL，如果设置为 NOT NULL，则该列必须有值
• DEFAULT: 设置默认值

2.3 数据库的字段属性

• UnSigned: 无符号的
• ZEROFILL: 0 填充的
• Auto_InCrement: 自增
• NULL 和 NOT NULL: 默认为 NULL
• DEFAULT: 默认的

每一个表，都必须存在以下五个字段：

名称	描述
id	主键
version	乐观锁
is_delete	伪删除
gmt_create	创建时间
gmt_update	修改时间

2.4 创建数据库表

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;

注意点：

• 表名和字段尽量使用 飘号 括起来
• AUTO_INCREMENT 代表自增
• 所有的语句后面加逗号，最后一个不加
• 字符串使用单引号括起来
• 主键的声明一般放在最后，便于查看
• 不设置字符集编码的话，会使用 MySQL 默认的字符集编码 Latin1，不支持中文，可以在 my.ini 里修改

格式：

CREATE TABLE IF NOT EXISTS `表名`(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释]
) [表的类型] [字符集设置] [注释];

常用命令：

• SHOW CREATE DATABASE 数据库名; – 查看创建数据库的语句
• SHOW CREATE TABLE 表名; – 查看表的定义语句
• DESC 表名; – 显示表的具体结构

2.5 数据库存储引擎

INNODB

• 默认使用，安全性高，支持事务的处理，多表多用户操作

MYISAM

• 早些年使用，节约空间，速度较快

功能	INNODB	MYISAM
事务支持	支持	不支持
数据行锁定	支持	不支持
外键约束	支持	不支持
全文索引	支持	不支持
表空间大小	较大	较小

数据库文件存在的物理空间位置：

• MySQL 数据表以文件方式存放在磁盘中
• 包括表文件、数据文件、以及数据库的选项文件
• 位置：MySQL 安装目录/data（目录名对应数据库名，该目录下文件名对应数据表）

在 MySQL 中，不同的存储引擎会使用不同类型的文件来存储数据和元数据。下面是 INNODB 和 MYISAM 两种常见存储引擎在文件上的区别：

INNODB

1. .frm 文件：

   • .frm 文件存储了表的结构定义，包括字段名、字段类型、索引等信息。

2. .ibd 文件：

   • .ibd 文件是 INNODB 表的数据文件，它包含了实际的数据，以及与 INNODB 相关的索引和元数据。

3. ibdata1 文件：

   • ibdata1 文件是 INNODB 系统表空间文件，它包含了系统表和共享表空间的数据。在 INNODB 中，数据和索引并不是以单独的文件形式存在，而是存储在共享的表空间中。

MYISAM

1. .frm 文件：

   • .frm 文件同样存储了表的结构定义，包括字段名、字段类型、索引等信息。

2. .MYD 文件：

   • .MYD 文件是 MYISAM 表的数据文件，它包含了表中的实际数据。

3. .MYI 文件：

   • .MYI 文件是 MYISAM 表的索引文件，它包含了 MYISAM 表中的索引数据。

这些文件组成了 MySQL 数据库中不同存储引擎的基本结构。不同存储引擎的文件类型和组织方式会影响数据库的性能、事务支持、并发控制等方面的特性。

2.6 修改数据库

-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teachers;

-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teachers ADD age INT(11);

-- 修改表的字段(重命名，修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE teachers MODIFY age VARCHAR(11); -- 修改约束

-- ALTER TABLE 表名 CHANGE 旧名字 新名字 [列属性];
ALTER TABLE teachers CHANGE age age1 INT(1); -- 字段重命名

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teachers DROP age1;

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

MySQL 数据管理

外键

# 主表
CREATE TABLE IF NOT EXISTS `student_for`(
	`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 '邮箱',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	PRIMARY KEY (`id`), 
	KEY `FK_gradeid` (`gradeid`), # 创建索引
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) # 外键
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建年级表
CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

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

添加 Insert

普通用法

INSERT INTO `student`(`name`) VALUES ('zsr');
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),('gcc','000421','女');
# 省略字段
INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武汉','1412@qq.com',1); 

语法：

INSERT INTO 表名([字段1,字段2..]) VALUES ('值1','值2'..),[('值1','值2'..)..];

2. 修改 Update

修改学员名字, 指定条件

UPDATE `student` SET `name`='zsr204' WHERE id=1;
# 不指定条件的情况,会改动所有表
UPDATE `student` SET `name`='zsr204';
# 修改多个属性
UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;
# 通过多个条件定位数据
UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';
UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

3. 删除 Delete

DELETE FROM `student`;

DELETE FROM `student` WHERE id=1;

DELETE FROM 表名 [WHERE 条件]

关于 DELETE 删除的问题，重启数据库现象：

• INNODB 自增列会从 1 开始（存在内存当中，断电即失）
• MYISAM 继续从上一个子增量开始（存在磁盘当中，不会丢失）

TRUNCATE

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

• TRUNCATE 用于完全清空一个数据库表，但是表的结构和索引约束不会改变。
• DELETE 可以根据条件删除表中的数据，而 TRUNCATE 只能删除整个表。
• TRUNCATE 重新设置自增列的计数器并将其归零，而 DELETE 不会影响自增列。
• DELETE 是数据操作语言（DML），它将原数据放入回滚段中，可以回滚；而 TRUNCATE 是数据定义语言（DDL），它的操作不会被存储，也不能回滚。

---

DQL 查询数据

Data Query Language 数据查询语言

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条

-- 创建学校数据库
CREATE DATABASE IF NOT EXISTS `school`;

-- 使用 school 数据库
USE `school`;

-- 创建年级表 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;

-- 给 grade 表插入数据
INSERT INTO `grade`(`GradeID`,`GradeName`) 
VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四');

-- 创建成绩 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;

-- 给 result 表插入数据
INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) 
VALUES (1000,1,'2019-10-21 16:00:00',97),(1001,1,'2019-10-21 16:00:00',96),
(1000,2,'2019-10-21 16:00:00',87),(1001,3,'2019-10-21 16:00:00',98);

-- 创建学生表 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 '联系电话,允许为空,即可选输入',
    `Adress` 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`,`Adress`,`BornDate`,`Email`,`IdentityCard`) 
VALUES (1000,'1241','dsaf',1,2,'24357','unknow','2000-09-16 00:00:00','1231@qq.com','809809'),
(1001,'1321','dfdj',0,2,'89900','unknow','2000-10-16 00:00:00','5971@qq.com','908697');

-- 创建科目表
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;

-- 给科目表 subject 插入数据
INSERT INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) 
VALUES(1,'高数','96',2),(2,'大物','112',2),(3,'程序设计',64,3);

SELECT 字段 FROM 表;

4.1、基础查询

语法：

SELECT 查询列表 FROM 表名;

查询列表可以是：表中的（一个或多个）字段，常量，变量，表达式，函数。

查询结果是一个虚拟的表格。

• 查询全部学生：

  SELECT * FROM student;

• 查询指定的字段：

  SELECT LoginPwd, StudentName FROM student;

• 别名 AS（可以给字段起别名，也可以给表起别名）：

  SELECT StudentNo AS 学号, StudentName AS 学生姓名 FROM student AS 学生表;

• 函数 CONCAT(str1, str2, …)：

  SELECT CONCAT('姓名', StudentName) AS 新名字 FROM student;

• 查询系统版本（函数）：

  SELECT VERSION();

• 用来计算（计算表达式）：

  SELECT 100 * 53 - 90 AS 计算结果;

• 查询自增步长（变量）：

  SELECT @@auto_increment_increment;

• 查询有哪写同学参加了考试，重复数据要去重：

  SELECT DISTINCT StudentNo FROM result;

LIKE 运算符支持以下通配符：

• %：匹配零个或多个字符。
• _：匹配任何单个字符。
• []：匹配方括号内指定的任何单个字符。
• [^]：匹配方括号内未指定的任何单个字符。

排序和分页

SELECT 查询列表
FROM 表
WHERE 筛选条件
ORDER BY 排序列表 ASC/DESC

说明：

• ORDER BY 子句用于对查询结果进行排序。
• ASC 表示升序（从最小到最大）。
• DESC 表示降序（从最大到最小）。
• ORDER BY 的位置一般放在查询语句的最后（除 LIMIT 语句之外）。

SELECT `StudentNo`, `StudentName`, `GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID = g.GradeID
ORDER BY `StudentNo` DESC;

该查询将按 StudentNo 列降序排列结果。

SELECT 查询列表
FROM 表
LIMIT offset, pagesize;

说明：

• LIMIT 子句用于对查询结果进行分页。
• offset 代表起始的条目索引（默认从 0 开始）。
• pagesize 代表显示的条目数。
• offset = (n-1) * pagesize，其中 n 为当前页面。

示例：

• 第一页：LIMIT 0, 5
• 第二页：LIMIT 5, 5
• 第三页：LIMIT 10,5
• 第 n 页：LIMIT (n-1) * pagesize, pagesize

计算总页数：

• 总页数 = 数据总数 / 页面大小

函数

函数	作用
ABS(-8)	返回绝对值
CEIL(5.1)	向上取整
CEILING(5.1)	向上取整
RAND()	返回 0 到 1 之间的一个随机数
SIGN(-10)	返回一个数的符号；0 返回 0，正数返回 1，负数返回 -1
CHAR_LENGTH('我喜欢你')	返回字符串的长度
CONCAT('我','喜欢','你')	拼接字符串
INSERT('我喜欢',1,1,'超级')	从字符串的指定位置开始替换为指定长度的新字符串
UPPER('zsr')	将字符串转换为大写
LOWER('ZSR')	将字符串转换为小写
INSTR('zsrs','s')	返回第一次出现指定子字符串的索引位置
REPLACE('加油就能胜利','加油','坚持')	替换字符串中出现的指定子字符串
SUBSTR('坚持就是胜利',3,6)	返回指定位置开始的指定长度的子字符串
REVERSE('rsz')	反转字符串
CURRENT_DATE()	获取当前日期
CURDATE()	获取当前日期
NOW()	获取当前时间
LOCALTIME()	获取本地时间
SYSDATE()	获取系统时间
YEAR(NOW())	获取当前年份
MONTH(NOW())	获取当前月份
DAY(NOW())	获取当前日
HOUR(NOW())	获取当前小时
MINUTE(NOW())	获取当前分钟
SECOND(NOW())	获取当前秒
SYSTEM_USER()	获取当前登录的系统用户
USER()	获取当前登录的数据库用户
VERSION()	获取 MySQL 的版本信息

加密

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,‘zsr’,‘200024’),
(2,‘gcc’,‘000421’),(3,‘bareth’,‘123456’);

UPDATE testMD5 SET pwd=MD5(pwd) WHERE id=1;

UPDATE testMD5 SET pwd=MD5(pwd);

INSERT INTO testMD5 VALUES(4,‘barry’,MD5(‘654321’));

SELECT * FROM testMD5 WHERE name=‘barry’ AND pwd=MD5(‘654321’);

事务原则：ACID

名称	描述
原子性	原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性	事务前后数据的完整性必须保持一致。
隔离性	事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性	事务一旦被提交则不可逆，被持久化到数据库中，接下来即使数据库发生故障也不应该对其有任何影响。

事务并发导致的问题

名称	描述
脏读	指一个事务读取了另外一个事务未提交的数据。
不可重复读	在一个事务内读取表中的某一行数据，多次读取结果不同。
虚读(幻读)	是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别：

事务隔离级别	脏读	不可重复读	幻读
读未提交	是	是	是
读已提交	否	是	是
可重复读	否	否	是
串行化	否	否	否

执行事务的过程

1. 关闭自动提交：

   SET autocommit=0;

2. 事务开启：

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

3. 成功则提交，失败则回滚：

   • 提交：持久化（成功）

     COMMIT

   • 回滚：回到原来的状态（失败）

     ROLLBACK

4. 事务结束：

   SET autocommit=1; -- 开启自动提交

5. 其他操作：

   • 保存点：

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

7、索引

索引（Index）是帮助 MySQL 高效获取数据的数据结构。

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接，实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化

7.1、索引的分类

-- 创建学生表 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 '联系电话,允许为空,即可选输入',  
	`Adress` 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;

• 主键索引（PRIMARY KEY）

  主键索引是唯一的标识，主键不可重复，只有一个列作为主键。它是最常见的索引类型，不允许为空值，用于确保数据记录的唯一性，确定特定数据记录在数据库中的位置。

  -- 创建表的时候指定主键索引
  CREATE TABLE tableName (
      …
      PRIMARY INDEX (columnName)
  )
  
  -- 修改表结构添加主键索引
  ALTER TABLE tableName ADD PRIMARY INDEX (columnName)

• 普通索引（KEY / INDEX）

  普通索引是最默认的索引类型，用于快速定位特定数据。应该添加在查询条件的字段上。

  -- 直接创建普通索引
  CREATE INDEX indexName ON tableName (columnName)
  
  -- 创建表的时候指定普通索引
  CREATE TABLE tableName (
      …
      INDEX [indexName] (columnName)
  )
  
  -- 修改表结构添加普通索引
  ALTER TABLE tableName ADD INDEX indexName(columnName)

• 唯一索引（UNIQUE KEY）

  唯一索引与普通索引类似，但索引列的值必须唯一，允许有空值。与主键索引的区别在于，主键索引只能有一个，而唯一索引可以有多个。

  -- 直接创建唯一索引
  CREATE UNIQUE INDEX indexName ON tableName(columnName)
  
  -- 创建表的时候指定唯一索引
  CREATE TABLE tableName(  
      …...
      UNIQUE INDEX [indexName] (columnName)  
  );  
  
  -- 修改表结构添加唯一索引
  ALTER TABLE tableName ADD UNIQUE INDEX [indexName] (columnName)

• 全文索引（FULLTEXT）

  全文索引是一种用于快速定位特定数据的索引类型，通常用于大型数据集和全文搜索。

  -- 增加一个全文索引
  ALTER TABLE student ADD FULLTEXT INDEX StudentName(StudentName);

  使用 EXPLAIN 分析 SQL 执行的情况：

  • 非全文索引：

    EXPLAIN SELECT * FROM student;

  • 全文索引：

    EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d');
    # 数据库引擎会使用StudentName列上的全文索引来快速搜索包含'd'的记录。

三大范式

1. 第一范式（1NF）：所有字段值都是不可分解的原子值。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，

1. 第二范式（2NF）：

   也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

   比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。

1. 第三范式（3NF）：

每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

直接操作流程

这是一个创建名为 tb_user 的表的 DDL 语句，其中包含了表的结构和约束。以下是对该 DDL 语句的解释：

create table tb_user
(
    user_id     bigint auto_increment
        primary key,
    username    varchar(50) not null comment '用户名',
    mobile      varchar(20) not null comment '手机号',
    password    varchar(64) null comment '密码',
    create_time datetime    null comment '创建时间',
    constraint index_tb_user_username
        unique (username),
    constraint username
        unique (username)
)
    comment '用户';

解释：

• create table tb_user：创建名为 tb_user 的表。
• user_id：定义一个名为 user_id 的字段，类型为 bigint，并且具有自增特性，作为主键。
• username：定义一个名为 username 的字段，类型为 varchar(50)，不能为空，用于存储用户名。
• mobile：定义一个名为 mobile 的字段，类型为 varchar(20)，不能为空，用于存储手机号。
• password：定义一个名为 password 的字段，类型为 varchar(64)，可以为空，用于存储密码。
• create_time：定义一个名为 create_time 的字段，类型为 datetime，可以为空，用于存储创建时间。
• constraint index_tb_user_username unique (username)：创建一个名为 index_tb_user_username 的唯一索引，用于确保 username 的唯一性。
• constraint username unique (username)：创建一个名为 username 的唯一约束，也是为了确保 username 的唯一性。
• comment '用户'：给表添加注释，描述为“用户”。

该 DDL 语句创建了一个名为 tb_user 的表，包含了主键、唯一索引和唯一约束等约束，以及对各个字段的数据类型和注释进行定义。

主从复制

主从复制的原理

主从复制主要用于备份和提高读取性能。在主从复制中，有一个主服务器（Master）和一个或多个从服务器（Slave）。

1. 主服务器：主服务器处理写操作（INSERT, UPDATE, DELETE 等）。每当主服务器完成一个写操作，它都会在其二进制日志（Binary Log）中记录下这个操作。

• 二进制日志是一个记录主服务器所有写操作的日志文件，每个事件（写操作）在二进制日志中都有一个唯一的位置。

2. 从服务器：从服务器开始时会从主服务器复制其整个数据集（这个过程叫做快照）。之后，从服务器将持续读取主服务器的二进制日志，并在本地应用这些日志中的写操作，从而保持与主服务器的数据同步。

• 从服务器也可以配置成定期从主服务器取得二进制日志的更新。

使用

准备好 2 台服务器
实现主从复制的步骤大致如下：

1. 在主库上操作：
   首先，你需要在主库中打开二进制日志（binary log），这是 MySQL 的一种日志文件，用于记录数据的修改情况。你可以在 MySQL 配置文件（如 my.ini）中添加或修改以下配置：

   [mysqld]
   log-bin=mysql-bin # 开启二进制日志
   server-id=1       # 设置 server-id，主库和从库的 server-id 必须不同

   然后重启 MySQL 服务以应用配置更改。

   接着，你需要创建一个用于主从复制的用户，并给予该用户复制的权限。假设你想创建的用户名为 repl，密码为 password，可以运行以下 SQL 命令：

   GRANT REPLICATION SLAVE ON . TO 'repl'@'%' IDENTIFIED BY 'password';

   最后，你需要获取当前的二进制日志文件名和位置，可以通过运行 SHOW MASTER STATUS; 命令获得。这些信息在接下来设置从库的时候需要用到。

2. 在从库上操作：
   同样的，你需要在 MySQL 配置文件中添加或修改以下配置：

   [mysqld]
   server-id=2 # 设置 server-id，主库和从库的 server-id 必须不同

   然后重启 MySQL 服务以应用配置更改。

   接着，你需要配置从库连接到主库，并开始复制。这里假设主库的 IP 地址为 192.168.1.100，二进制日志文件名为 mysql-bin.000001，位置为 120，可以运行以下 SQL 命令：

   CHANGE MASTER TO
       MASTER_HOST='192.168.1.100',
       MASTER_USER='repl',
       MASTER_PASSWORD='password',
       MASTER_LOG_FILE='mysql-bin.000001',
       MASTER_LOG_POS=120;

   最后，启动复制过程，运行以下 SQL 命令：

   START SLAVE;

说明

写(增删改)操作在 master 库中，比如: update xxx set status = xxx
读(查询)操作在 slave 库中, 比如: select from xxx

Sharding-jdbc 增强版 JDBC

配置读写分离规则

spring:
  shardingsphere:
    datasource:
      names: ds0, ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0
        username: root
        password: password
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds1
        username: root
        password: password
    masterslave:
      # 负载均衡算法
      load-balance-algorithm-type: round_robin # 轮询
      # 最终数据库名称 这个库的名称主从数据库都要有
      name: dataSource
      master-data-source-name: ds0
      slave-data-source-names: ds1
    props:
      sql:
        # 可以打印执行的 sql 数据，方便调试
        show: true
  # 允许 bean 覆盖
  main:
    allow-bean-definition-overriding: true

基础学习

# 创建数据库
create database if not exists mydatabase;
# 选择数据库
use mydatabase;
# 建立表 要有主键 自增 唯一索引
-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY comment '这是主键，自增id',
    username VARCHAR(50) NOT NULL COMMENT '用户名字',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    age INT COMMENT '年龄'
);

# 删除表
DROP TABLE IF EXISTS users;

# 在建立另外一张表 要有外键
-- 订单表
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    -- 总位数 10 小数位数 2
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

# 测试表
CREATE TABLE IF NOT EXISTS datas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    -- 总位数 10 小数位数 2
    total_amount DECIMAL(10, 2),
    score INT,
    age INT
);


# 增加新数据
-- 插入用户数据
-- 单条插入用户数据
INSERT INTO users (id,username, email, age) VALUES (1,'Fcw', 'fcw@example.com', 66);# 主键可以修改

INSERT INTO users (username, email, age) VALUES ('Alice', 'alice@example.com', 28);
INSERT INTO users (username, email, age) VALUES ('Bob', 'bob@example.com', 35);
INSERT INTO users (username, email, age) VALUES ('David', 'david@example.com', 30);
INSERT INTO users (username, email, age) VALUES ('Eva', 'eva@example.com', 25);
INSERT INTO users (username, email, age) VALUES ('Frank', 'frank@example.com', 40);
INSERT INTO users (username, email, age) VALUES ('Grace', 'grace@example.com', 33);
INSERT INTO users (username, email, age) VALUES ('Hank', 'hank@example.com', 27);
INSERT INTO users (username, email, age) VALUES ('Ivy', 'ivy@example.com', 38);
INSERT INTO users (username, email, age) VALUES ('Jack', 'jack@example.com', 29);

-- 插入订单数据
-- 单条插入订单数据
INSERT INTO orders (user_id, total_amount) VALUES (1, 100.00);
INSERT INTO orders (user_id, total_amount) VALUES (2, 150.50);
INSERT INTO orders (user_id, total_amount) VALUES (3, 75.20);
INSERT INTO orders (user_id, total_amount) VALUES (4, 200.80);
INSERT INTO orders (user_id, total_amount) VALUES (5, 50.50);
INSERT INTO orders (user_id, total_amount) VALUES (6, 300.00);
INSERT INTO orders (user_id, total_amount) VALUES (7, 120.75);
INSERT INTO orders (user_id, total_amount) VALUES (8, 80.90);
INSERT INTO orders (user_id, total_amount) VALUES (9, 180.25);
INSERT INTO orders (user_id, total_amount) VALUES (10, 95.60);

-- 插入测试表数据
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 40.50, 95, 15);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 35.75, 88, 12);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 42.20, 92, 18);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 37.80, 91, 16);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 41.30, 94, 14);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 38.60, 89, 13);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 39.90, 93, 17);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 36.45, 87, 11);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 40.00, 96, 19);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 37.10, 90, 10);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 43.80, 98, 20);
INSERT INTO datas (user_id, total_amount, score, age) VALUES (22, 39.20, 91, 16);


# 删除数据
-- 方法一：由于有外键约束，需要先删除orders中的相关数据
DELETE FROM orders WHERE orders.user_id=1;
DELETE FROM users WHERE users.id=1;
-- 方法二：与一类似，不过在创建外键索引的时候，后面加上下面内容，删除users中的行数据时，有关表的数据也会被删除、
# FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

# 修改数据
UPDATE users SET age = 888 WHERE users.id = 2;

# 查找数据
SELECT username, email,age FROM users WHERE age > 35;

# 排序
-- 默认升序
SELECT  age
FROM datas
ORDER BY age;

-- 降序
SELECT username, age
FROM users
ORDER BY age DESC;


-- 多列排序
SELECT username, age
FROM users
ORDER BY age,email DESC;



# 单条数据插入
-- 插入一条用户数据
INSERT INTO users (username, email, age) VALUES
('John', 'john@example.com', 32);

# 接下来 我要学习where having 语句

-- 查询年龄大于 25 岁的用户
SELECT * FROM users WHERE age > 25;

-- 查询年龄大于 25 岁的用户，并计算其平均年龄
SELECT username, AVG(age) as average_age
FROM users
GROUP BY username
HAVING AVG(age) > 25;

# 写一个死锁案例

-- 事务1
START TRANSACTION;
UPDATE users SET age = 30 WHERE id = 1; -- 试图更新用户1的信息

-- 事务2
START TRANSACTION;
UPDATE users SET age = 25 WHERE id = 2; -- 试图更新用户2的信息

-- 事务1
-- 尝试获取用户2的锁
UPDATE users SET age = 35 WHERE id = 2;

-- 结束事务
COMMIT ;
-- 事务2
-- 尝试获取用户1的锁，但由于被事务1占用，造成死锁
UPDATE users SET age = 28 WHERE id = 1;

COMMIT ;

# alter 语句，修改数据库的表结构

-- 添加
ALTER TABLE datas
ADD COLUMN new_column INT;

-- 删除
ALTER TABLE datas
DROP COLUMN new_column;


-- 修改
ALTER TABLE datas
MODIFY COLUMN new_column char(20);


-- 添加主键
ALTER TABLE datas
ADD PRIMARY KEY (user_id);


-- 删除主键

ALTER TABLE datas
DROP PRIMARY KEY;

docker 配置 Mysql

 # 1.找镜像
 docker search mysql
 
 # 2.拉镜像
docker pull mysql:5.7
 
 # 3.建镜像
 # -p 端口映射 本机端口：容器端口
 # --name：给 MySQL 容器取的名字
 # -d：表示后台运行
 # -e MYSQL_ROOT_PASSWORD：设置 root 用户密码
 # -v：表示挂载路径，冒号左面的表示宿主机的挂载目录，冒号右边则表示容器内部的路径。
docker run -p 3306:3306 --name mysql \
-v /usr/local/docker/mysql/conf:/etc/mysql \
-v /usr/local/docker/mysql/logs:/var/log/mysql \
-v /usr/local/docker/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7


docker run -it --name mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysql:5.7

# 进入 bash 模式
docker exec -it c5b bash

# 最后在云服务器那里开放 3306 端口

数据类型	说明
VARCHAR(50)	VARCHAR 代表可变长度的字符类型，允许存储不超过指定长度的字符数据。
PRIMARY KEY (SNO, PNO, JNO)	PRIMARY KEY (SNO, PNO, JNO) 定义了一个复合主键，由三个字段组成：SNO, PNO, 和 JNO。这意味着这三个字段的组合必须唯一地标识表中的每一行，并且可以用来唯一地标识表中的每个记录。
FOREIGN KEY (SNO) REFERENCES S(SNO)	FOREIGN KEY (SNO) REFERENCES S(SNO) 定义了一个外键约束。当前表中的 SNO 列的值必须是表 S 中已经存在的 SNO 列的值之一，否则将会违反外键约束。
JNO VARCHAR(10)	JNO VARCHAR(10) PRIMARY KEY 这意味着 JNO 列将包含最大长度为 10 的字符串，并且这个列的值将唯一标识表中的每一行。主键约束确保表中的每条记录都具有唯一的 JNO 值，且不允许该列包含空值或重复值。