MySQL学习笔记(上)

一、MySQL 基础入门（1-8）

1.数据库概述

数据库在编程中非常重要

数据库是是长期存储、统一管理、共享数据的集合。

关系型数据库（SQL）

• MySQL、Oracle、DB2、SQLite、Sql Sever

• 以表、行、列存储数据，表与表之间有关系

非关系型数据库(NoSQL) not only SQL

• Redis、MongDB

• 以对象、键值对存储，灵活扩展

DBMS（数据库管理系统）

2.环境部署（MySQL 、SQLyog ）

MySQL观看主页文章

对于ASQLyog需要13.2.0最新版才能适配MySQL8.4

一直下一步无脑安装

连接SQL主机注意用户名、密码填对

新建数据库注意字符集

3.表结构设计

a.表的字段类型

数值 （括号内是显示宽度，不影响实际存储范围）

tinyint 十分小的数据 1 个字节

smallint 较小的数据 2 个字节

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

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

bigint 较大的数据 8 个字节

float 浮点数 4 个字节

double 浮点数 8 个字节 (精度问题！)

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

---

字符串（括号内是存储字符数上限）

char 字符串中字符个数固定的 0~255

varchar 可变字符串 0~65535 常用的变量 String

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 年份表示

---

b.表的字段属性

字段	含义
PRIMARY KEY	主键（唯一标识）
UNSIGNED	无符号整数，不能为负
ZEROFILL	不足的位数用0填充
NOT NULL	非空,不赋值会报错
DEFAULT	默认值
AUTO_INCREMENT	自增+1，通常用来设置唯一的主键

c.企业开发（拓展）

表设计五字段规范速记

• id：唯一标识表中每一条记录，是表的核心标识。

• version：乐观锁，解决并发更新冲突问题，避免 “覆盖更新”

• is_delete：伪删除保留数据历史，方便恢复和审计

• gmt_create：记录创建时间

• gmt_update：追踪修改时间

4.基础命令行

MySQL属于关系型数据库管理系统，使用的是SQL语言

1.  mysql -u root -p       登录
2.  show databases;         查看所有库
3.  use 库名;               使用库
4.  show tables;           查看表
5.  describe 表名;         查看表结构
6.  create database xxx;   创建库
7.  exit;                   退出

复制代码

SQL 四种语言

DDL:定义语言

DML操作语言

DQL查询语言

DCL控制语言

5.数据库的 CRUD

• 创建数据库

1.  CREATE DATABASE [IF NOT EXISTS] xxx

复制代码

• 删除数据库

1.  DROP DATABASE [IF EXISTS] xxx

复制代码

• 使用数据库

1.  USE `xxx`    
2.  加``是为了避免命名与 MySQL 关键字 / 保留字冲突，还支持含空格 / 特殊字符 / 数字开头的特殊命名

复制代码

• 查看所有数据库

1.  SHOW DATABASES

复制代码

二、表的创建修改删除（9-11）

法一：创建一个学生表

-- 注意点，使用英文 () ，表的名称 和 字段 尽量使用 `` 括起来

-- AUTO_INCREMENT 自增

-- 字符串使用 单引号括起来！

-- 所有的语句后面加英文逗号

-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键！

1.  CREATE TABLE IF NOT EXISTS`student` (
2.   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生ID',
3.   `name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT '姓名' ,
4.     `pwd` VARCHAR(30) NOT NULL DEFAULT'123456' COMMENT'密码',
5.     `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT'性别',
6.     `birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
7.     `address` VARCHAR(100) DEFAULT NULL COMMENT'家庭住址',
8.     `email`VARCHAR(30)DEFAULT NULL COMMENT'电子邮件',
9.     PRIMARY KEY(`id`)-- 必加主键（自增字段必须设主键）
10.     UNIQUE KEY `uk_name` (`name`) -- 加唯一索引（避免姓名重复）
11.    
12.  )ENGINE=INNODB DEFAULT CHARSET=utf8

复制代码

InnoDB对比MyISAM

	MyISAM	InnoDB
事务支持	不支持	支持（保证操作原子性，扣钱和生成订单必须同时成功，或同时失败）
数据行锁定	不支持（修改一行数据时会锁住整张表）	支持
外键约束	不支持	支持（可在数据库层面强制表间关联，“成绩必须关联真实学生”）
全文索引	支持	不支持（在 MySQL 5.6+ 也支持全文索引）
需要内存	较小	较大，约为2倍

常规使用操作:

• MYISAM 节约空间，速度较快，仅适合静态、只读场景。

• INNODB 安全性高，事务的处理，多表多用户操作，是 99% 业务场景的首选。

法二：创建表后显示创建语句

1.  SHOW CREATE DATABASE school --查看创建数据库的语句
2.  SHOW CREATE TABLE student --查看创建表的语句
3.  DESC student   --显示表的结构

复制代码

字段的修改删除

1.  --重命名（rename as）
2.  ALTER TABLE 表 RENAME AS 新名
3.  ​
4.  --增加字段（add）
5.  ALTER TABLE students ADD `work` VARCHAR(20) DEFAULT NULL
6.  ​
7.  --修改字段（modify、change）
8.  ALTER TABLE students MODIFY `work` VARCHAR(33)   --modify修改约束
9.  ALTER TABLE students CHANGE `work` `age`         --change重命名字段
10.  ​
11.  --删除字段（drop）
12.  ALTER TABLE [IF EXISTS] students DROP `字段名`

复制代码

注意：所有创建和删除最好加上条件判断

三、MySQL数据管理

3.1外键(了解即可,不建议使用)

法一：建表时创建外键

1.  CREATE TABLE `grade`(
2.     `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
3.     `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
4.     PRIMARY KEY (`gradeid`)
5.  ) ENGINE=INNODB DEFAULT CHARSET=utf8
6.  ​
7.  -- 学生表的 gradeid 字段 要去引用年级表的 gradeid
8.  -- 定义外键 key
9.  -- 给这个外键添加约束 （执行引用） references 引用
10.  CREATE TABLE IF NOT EXISTS `student` (
11.     `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
12.   ......
13.     `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
14.     PRIMARY KEY(`id`),
15.      
16.     KEY `FK_gradeid` (`gradeid`),
17.     CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
18.  ) ENGINE=INNODB DEFAULT CHARSET=utf8

复制代码

法二、事后创建外键

1.   ALTER TABLE `student` --改变表
2.   ADD CONSTRAINT `KF_grade_id`   --添加约束
3.   FOREIGN KEY(`grade_id`) --外键是字段（`grade_id`）
4.   REFERENCES `grade`(`grade_id`);--引用的是`grade`表的(`grade_id`)字段

复制代码

• 删除有外键关系的表时，先删「引用表（子表）」→ 再删「被引用表（父表）」

• 如果想建立外键，两个表之间的字段也有要求

  • 字段类型完全一致：比如父表grade.grade_id是INT(10)，子表student.grade_id也必须是INT(10)（不能一个是 INT，一个是 VARCHAR）；

  • 被引用字段（父表）必须是主键 / 唯一索引,否则无法作为外键被引用；

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

3.2DML语言，很重要！！！

DML语言：数据操作语言

添加Insert

1.  语法：INSERT INTO 表(字段,...) VALUES (值,...)

复制代码

1.  --插入一行数据
2.  INSERT INTO grade(grade_id,grade_name)VALUES('90','sda')
3.  ​
4.  --插入多行数据
5.  INSERT INTO grade(grade_name)VALUES('diyi'),('dier'),('disan')
6.  ​
7.  --注意：字段可以省略，但是值必须要一一对应
8.  INSERT INTO gradeVALUES('90','sda','男','aaa','2009-1-1','aaa')

复制代码

修改update

1.  UPDATE 表名 SET 字段名=值 WHERE 条件;

复制代码

1.  --修改单个属性
2.  UPDATE `student`SET `name`='狂神'WHERE id=1;
3.  ​
4.  --修改多个属性，逗号隔开
5.  UPDATE `student`SET `name`='dnaid',pwd=21312 WHERE id=1;
6.  ​
7.  <>、!= 不等于  
8.  BETWEEN 1 AND 3 1~3之间
9.  AND、OR  

复制代码

删除delete、清空truncate

1.  DELETE FROM 表 WHERE 条件
2.  TRUNCATE 表

复制代码

两者区别

• truncate会清空自增计数

• truncate不会影响计数

四、SQL 核心查询，很重要！！！（16-27）

核心：从单表查询到复杂多表操作

1.基础查询（Select、别名、拼接函数、去重、表达式、Where 子句）

1.  --查询所有
2.  SELECT * FROM 表
3.  ​
4.  --查询指定字段+别名（表和字段都可以起别名）
5.  SELECT 字段 AS 别名 FROM 表
6.  ​
7.  --拼接函数
8.  SELECT CONCAT('拼接',字段)FROM 表
9.  ​
10.  --去重
11.  SELECT DISTINCT 字段 FROM 表
12.  ​
13.  --表达式
14.  SELECT VERSION()
15.  SELECT score+1,score FROM `result`
16.  ​
17.  --Where子句
18.  WHERE 条件
19.  ​

复制代码

2.高级查询

2.1模糊查询（比较运算符）

运算符	语法	描述
IS NULL	WHERE a IS NULL OR a = ''	若值为 NULL，则结果为真
IS NOT NULL	WHERE a IS NOT NULL	若值不为 NULL，则结果为真
BETWEEN	WHERE a BETWEEN b AND c	若 a 在 b 和 c 之间，则结果为真
LIKE	WHERE a LIKE ‘b’	SQL 模糊匹配，结合%_使用（代表任意、一个字符）
IN	WHERE a IN (a1,a2,a3...)	若 a 等于 a1,a2... 中的某一个，则结果为真

2.2联表查询join

有三种最基础的

操作	描述
inner join交集	同时存在于 A 表和 B 表的记录（有成绩的学生）
left join左表全集	A 表所有记录 + B 表匹配的记录（所有学生）
right join右表全集	B 表所有记录 + A 表匹配的记录（所有成绩）

针对inner join的理解

表数据情况	是否出现在 INNER JOIN 结果中？
有学生 + 有成绩（ID 1-50）	✅ 是（交集）
有学生 + 无成绩（ID 51-100）	❌ 否（只有左表有）
无学生 + 有成绩（无此情况）	❌ 否（只有右表有）

思路：1.先确定查询的字段来自那些表，防止模棱两可报错

2.确定使用哪种连接查询？以上7种

3.on确定交叉点（多个表中哪些数据是相同的）

判断的条件：表一id字段==表二id字段

4.筛选条件→WHERE

1.  -- 模板：所有 JOIN 都按这个结构写，永不犯错
2.  SELECT 要查的字段
3.  FROM 左表 别名1
4.  JOIN 右表 别名2 ON 别名1.关联字段 = 别名2.关联字段 -- 关联条件→ON
5.  WHERE 筛选条件（比如 分数>60、姓名含'张'）;         -- 筛选条件→WHERE

复制代码

1.  --内连接只保留 “有成绩的学生”,筛选包含有高分成绩的学生
2.  SELECT s.`id`,`name`,`subject`,`score`FROM `student` AS s
3.  INNER JOIN `result` AS r
4.  ON s.id=r.student_id
5.  WHERE score>=90
6.  ​
7.  --左连接查询所有学生（含无成绩）
8.  SELECT s.`id`,`name`,`subject`,`score`FROM `student` AS s
9.  LEFT JOIN `result` AS r
10.  ON s.id=r.student_id
11.  ​
12.  --右连接保留所有成绩记录，仅保留 r.subject（成绩表的科目名）和（科目表的科目名）匹配的记录
13.  SELECT s.`id`,`name`,`subject`,`score`FROM `student` AS s
14.  RIGHT JOIN `result` AS r
15.  ON s.id=r.student_id
16.  INNER JOIN`subject`
17.  ON r.subject=`subject`.subject_name

复制代码

注：一个查询中可以多个on，只能有一个where

2.3自连接

自连接含义是同一张表与自身进行连接查询的特殊 JOIN 方式，是为了处理「树形结构 / 层级数据」的存储与查询，本质非常类似 ' 树的双亲表示法 '

数组下标（category_id）	parent(pid)	data(name)
0	-1	R
1	0	A
2	0	B
3	0	C
4	1	E
...每个元素对象都有唯一的id

在进行查询时，把一张表作为两张表查询，利用AS的重命名查询两个相同的字段

1.  ​
2.  SELECT
3.   b.`categoryname` AS '父栏目',
4.   a.`categoryname`AS '子栏目'
5.  FROM `category` AS a,category AS b
6.  WHERE a.pid=b.categoryid

复制代码

2.4分页和排序

在前端的分页----->是由于数据库的隔离

升序：ASC 降序：DESC

1.  ORDER BY `字段名` DESC

复制代码

分页使网页只加载一部分，缓解数据库压力

1.  LIMIT 0,5   从第一页起始，五条数据 1~5
2.  LIMIT 2,6 从第二页起始，六条数据 2~7
3.  ​
4.  LIMIT 0,5     1~5 第一页
5.  LIMIT 5,5 6~10 第二页
6.  LIMIT 10,5   11~15 第三页
7.  第N页 起始值=(n-1)*pagesize页面大小

复制代码

3.子查询（嵌套查询）

本质：在where语句中再嵌套一个子查询语句，实现where(select语句)

注意：1.子查询只适合查询字段都为一个表的，但是可以多表查

1. in =

1.  只查询语文成绩大于80的学生的学号姓名
2.  --join查询：连接查两张表
3.  SELECT s.`id`,`name` FROM`student` AS s
4.  INNER JOIN `result` AS r
5.  ON r.`student_id`=s.id
6.  WHERE r.subject='语文'AND score>80
7.  ORDER BY score DESC
8.  ​
9.  先查语文成绩大于80的学生的学号，再去另一张表查姓名
10.  --嵌套查询：查一张表由里及外
11.  SELECT `id`,`name` FROM`student`
12.  WHERE id IN(SELECT `student_id`FROM result WHERE SUBJECT='语文'AND score>80)
13.  ORDER BY id DESC

复制代码

子查询可以和join查询结合用

4.函数与聚合

4.1常用函数

常用函数MySQL ：： MySQL 8.4 参考手册 ：： 14.1 内置函数与作符参考

1.  一、数学函数（对数字进行运算）
2.  SELECT ABS(-4);-- 取绝对值
3.  ​
4.  SELECT CEILING(9.3);-- 向上取整
5.  ​
6.  SELECT FLOOR(9.3);-- 向下取整
7.  ​
8.  SELECT RAND();-- 生成 0~1 之间的随机小数
9.  ​
10.  SELECT SIGN(-5);-- 返回数字符号：正数=1，负数=-1，零=0
11.  ​
12.  =================================
13.  二、字符串函数（处理文字）
14.  SELECT CHAR_LENGTH('dafawfa发放');-- 获取字符串长度（汉字、字母都算1）
15.  ​
16.  SELECT CONCAT('拼接','二','三');-- 字符串拼接
17.  ​
18.  SELECT INSERT('再左侧插入再右侧插入',6,0,'在第五个位置替换0个字符为本条语句');-- 指定位置插入替换字符串
19.  ​
20.  SELECT LOWER('daA');-- 转小写
21.  ​
22.  SELECT UPPER('daA');-- 转大写
23.  ​
24.  SELECT INSTR('uuudadadadad','ad');-- 查找子字符串第一次出现的位置
25.  ​
26.  SELECT SUBSTR('123456789',5,3);-- 截取字符串（从第5位开始，截取3个字符）
27.  ​
28.  SELECT REVERSE('12345');-- 字符串反转
29.  ​
30.  SELECT REPLACE(`name`,'大','周周') FROM `student` WHERE `name` LIKE '大%'; -- 实战替换字符串内容（把姓名中的“大”换成“周周”）
31.  ​
32.  =================================
33.  三、时间函数（获取日期、时间）
34.  SELECT CURDATE();-- 获取当前日期（年-月-日）
35.  ​
36.  SELECT NOW();-- 获取当前日期+时间
37.  ​
38.  SELECT SYSDATE();-- 获取系统当前时间
39.  ​
40.  --提取时间中的部分YEAR MONTH DAY HOUR MINUTE SECOND
41.  SELECT DAY(NOW());
42.  SELECT HOUR(NOW());
43.  ​
44.  =================================
45.  四、系统函数（数据库信息）
46.  ​
47.  SELECT SYSTEM_USER();-- 获取系统登录用户
48.  ​
49.  SELECT USER();-- 获取当前数据库用户
50.  ​
51.  SELECT VERSION();-- 获取MySQL版本号

复制代码

4.2聚合函数、分组过滤

聚合函数

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
...	...

1.  都能统计表的数据
2.  SELECT COUNT(score) FROM result--字段名 会忽略NULL值
3.  SELECT COUNT(1) FROM result--计算所有NULL值，忽略所有列，每一行记一个 1
4.  SELECT COUNT(*) FROM result--计算所有NULL值，包括了所有的列，统计表总行数
5.  --所以速度：字段名< 1 = *
6.  ​
7.  SELECT MAX(score) AS 最高分 FROM result
8.  SELECT SUM(score) AS 总和 FROM result
9.  SELECT AVG(score) AS 均值 FROM result

复制代码

分组过滤

1.  --查询一张表中不同课程的平均分、最高分、最低分，需要使用到分组
2.  SELECT `subject`,AVG(score),MAX(score),MIN(score) FROM result
3.  GROUP BY `subject` --分组
4.  HAVING AVG(score)>20 --过滤分组后要满足的次要条件
5.  ​

复制代码

注意HAVING student_id >100是错的，因为

WHERE：过滤原始行，可以用任何字段（包括 student_id、name、score 等） HAVING：过滤分组后的结果，只能用：GROUP BY 的字段，聚合函数字段

5.拓展与小结（数据库 MD5 加密）

MD5 是把任意一串内容，变成固定长度的 32 位字符串。所以INT 字段不能存字符串

MD5 不可逆，只能加密，不能解密

网上所谓破解 = 查表，不是解密

id 是主键，绝对不能加密！只能加密：密码、姓名、内容等字符串字段

1.  --修改字段存储使其可以存储32位字符串
2.  ALTER TABLE `text` MODIFY `name`VARCHAR(32) NOT NULL COMMENT'姓名'
3.  ​
4.  --加密
5.  UPDATE `text` SET `name`=MD5(`name`);
6.  ​
7.  --插入数据时加密
8.  INSERT INTO `text` VALUES (MD5('莫大我觉得见到我'),19,32891)
9.  ​
10.  --用法：当用火输入密码转为MD5加密后的，再比对存储的32加密串
11.  SELECT *FROM `text` WHERE `name`=MD5('莫大我觉得见到我')

复制代码

五.小结