MySQL学习笔记

58 阅读17分钟

数据库分类

image.png


🍍一. 启动mysql服务器流程

安装: 狂神说MySQL01:初识MySQL (qq.com)

🍉1. 先终端启动

  1. 以管理员身份打开终端
  2. 进入mysql文件的bin目录cd /d D:\Environment\mysql-5.7.19\bin
  3. 启动服务器net start mysql

image.png

  1. mysql -u root -p
  2. 输入密码(默认123456)

image.png

🍉2. 然后打开SQLyog可视化工具

  • 可以在历史记录里查看你刚刚可视化操作的sql语句样式
  1. 新建数据库

image.png

  1. 新建表

image.png

  1. 查看表,新建记录

image.png

添加完需要点刷新,就添加上了

  1. 连接数据库

首先确保上面启动了数据库了(上面写了)

在这个基础上

🫐几个基本的命令行操作

每句后面必须加分号
update user set password=password('123456')where user='root'; -- 修改密码
flush privileges;  -- 刷新数据库
show databases; -- 显示所有数据库
use dbname;-- 打开某个数据库
show tables; --显示数据库mysql中所有的表
describe user; -- 显示表mysql数据库中user表的列信息
create database name; -- 创建数据库
use databasename;   -- 选择数据库

`exit;` 退出Mysql
? 命令关键词 : 寻求帮助
-- ,/* */ 表示注释

🫐数据库语言

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

image.png

🍍二. 操作数据库

参考文档: mp.weixin.qq.com/s/a4jA3B2j4…


mysql关键词不区分大小写

  • 创建数据库
CREATE DATABASE [IF NOT EXISTS] ... -- 加了一个判断条件,括号里面是可选的,可写可不写,是为了防止重复创建报错的
  • 删除数据库
DROP DATABASE [IF EXISTS] ...
  • 使用数据库
USE ...
-- 如果数据库的名字是个关键词(特殊字符,比如user),就用反引号``括住
  • 查看数据库
SHOW DATABASES -- 查看所有数据库

🍉2.1 数据库的数据类型字段属性

详情见上面参考文档

  • 数值
  • 字符串
  • 时间日期
  • null

TIP: 需要这几个字段来保证它的健壮性和安全性

image.png

🍉2.2 创建数据库表

🫐2.1.1 自己写

image.png

-- 所有语句后面加逗号,最后一句可以不加
-- 表名和字段用反引号括
-- comment是注释,后面字符串用单引号括起来
create table if not exists `students` (
 `id` int(4) not null auto_increment comment '学号', -- id列,int类型,四个字段,非空,自增,注释
 `name` varchar(30) 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 '邮箱',
 -- 把id设置为主键,一般一个表只有一个唯一的主键
 primary key (`id`)
)engine=innodb default charset=utf8

🫐2.1.2 根据已经有的表,复制

  • ———————— 懒人版
  • SHOW CREATE DATABASE school -- 查看创建数据库的语句
  • SHOW CREATE TABLE students -- 查看student数据表的定义语句
  • DESC students -- 显示表的结构

👇

image.png


🫐2.1.3 TIP

关于数据库引擎

  • INNOB 默认使用
  • MYISAM

image.png

(所有的数据库文件都存在data目录下

本质还是文件的存储)

文档: mysql的默认字符编码的设置(my.ini设置字符编码) - 简书 (jianshu.com)

🍉2.3. 修改删除表

image.png

-- 与表相关——alter
ALTER TABLE students RENAME teacher -- 修改表的名称
ALTER TABLE teacher ADD hobby VARCHAR(100) -- 增加表的字段

-- 修改表的字段(分为两种:1. 重命名change 2. 修改约束modify)
ALTER TABLE teacher MODIFY hobby INT(11) -- 修改约束
ALTER TABLE teacher CHANGE hobby hobby1 VARCHAR(100) -- 重命名

ALTER TABLE teacher DROP hobby1 -- 删除表的字段

ALTER TABLE teacher DROP hobby1 -- 删除表的字段

TIP:

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

🍍三. MYSQL数据管理

🍉3.1 数据库级别的外键(约束)

image.png

TIP:

  • 删除有外键关系的表的时候,必须先删除从表,再删除主表(被引用的表)

🫐方式一: 在创建表的时候,增加约束(麻烦)

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

-- 学生表的 gradeid 字段要去引用年级表的 gradeid
-- 1. 定义外键key
-- 2. 给这个外键添加约束(执行引用)
CREATE TABLE IF NOT EXISTS `students` (
 `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号', -- id列,int类型,四个字段,非空,自增,注释
 `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
 `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
 `gender` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
 `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
 -- 👇这个
 `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
 
 `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
 `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
 PRIMARY KEY (`id`),
 -- 定义了一个key,代表想把gradeid作为外键
 KEY `FK_gradeid` (`gradeid`),
 -- 约束
 CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

其实就是这两句👇

(
...
 `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
...
 -- 定义了一个key,代表想把gradeid作为外键
 KEY `FK_gradeid` (`gradeid`),
 -- 约束
 CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
 ...
 )

🫐方式二:建表的时候不加约束,额外在外面单独写

在创建表成功后,再添加约束

(
...
 `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
 ...
 )

ALTER TABLE `students`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);
  • 以上的操作都是物理外键,数据库级别的外键,不建议使用(避免数据库过多,删除造成困扰)
  • 所以最佳实践是:
  • 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
  • 而我们想用多张表的数据,想使用外键(之后用程序来实现)

🍉3.2 DML语言(数据操作语言,增删改,全部记住,背下来)

参考文档: mp.weixin.qq.com/s/F3fPma7y8…

  • insert
  • update
  • delete

🫐3.2.1 添加insert

关键:"一一对应"

INSERT INTO 表名([字段名1,字段名2,字段名3])VALUES('值1'),('值2'),('值3'),(...)
-- 可以同时给一个字段,插入多个值,相当于插入多条数据

-- 如果主键自增,那么我们就可以省略主键
-- 一般写插入语句,一定要数据和字段一一对应

这种是省略主键的正确写法👇

image.png

这种是错的👇 image.png

🫐3.2.2 修改update

逻辑

  • update 修改谁(哪个表) set字段=新值 修改的哪一条
UPDATE `students` SET `name`='小明' WHERE id=1; -- 改变一条
UPDATE `students` SET `name`='小美' -- 不指定条件,改变所有的数据
UPDATE `students` SET `name`='小石',`email`='123456@qq.com' WHERE id=1;-- 修改多的属性,逗号隔开

where 条件子句

image.png

🫐3.2.3 删除delete

delete逻辑

  • DELETE FROM 表名 WHERE 条件
  • 条件可写可不写,但是如果不写,就相当于删除了表内所有数据,不建议
  • 因为删除全部数据有别的方法👇
-- 删除最后一个数据
DELETE FROM grade WHERE gradeid = 5

TRUNCATE命令——清空一个数据库表

  • 清空一个数据库表,表的结构和索引约束不会变
TRUNCATE [TABLE] 表名;

-- 清空年级表
TRUNCATE `grade`

注意:区别于DELETE命令

  • 相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
  • 不同 :
    • 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
    • 而delete删除完,不影响原来的自增记录
    • 使用TRUNCATE TABLE不会对事务有影响 (事务后面会说)

但是delete也有下面的情况:

  • 同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后
    • InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
    • MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)

🍍四. DQL查询数据SELECT

参考文档: mp.weixin.qq.com/s/0UnSJDRpd…

DQL( Data Query Language 数据查询语言 )

  • 所有查询操作都用它 , 如SELECT语句
  • 简单的单表查询或多表的复杂查询和嵌套查询
  • 是数据库语言中最核心,最重要的语句
  • 使用频率最高的语句

完整代码: image.png

测试源码:

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
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;

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

-- 创建科目表
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 = 19 DEFAULT CHARSET = utf8;

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

-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成绩数据  这里仅插入了一组,其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

🍉4.1 查询字段

-- 查询所有学生信息
SELECT * FROM student;

-- 查询指定列(学号 , 姓名)
SELECT `studentno`,`studentname` FROM student;

🍉4.2 别名AS——给查出来的列的表头起名字

-- 查询指定列(学号 , 姓名)
-- 可以给字段起别名,也可以给表起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS s;

image.png

-- 函数 Concat(a,b)

-- CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

image.png

🍉4.3 去重DISTINCT

比如一个同学考了多次,所以会有多条相同学号的数据,但是我们现在想统计有多少人来考试,所以就需要用到去重

基础:👇

-- # 查看哪些同学参加了考试(学号) 去除重复项
SELECT * FROM result-- 查看考试成绩
SELECT studentno FROM result-- 查看哪些同学参加了考试


👇
SELECT DISTINCT studentno FROM result-- 了解:DISTINCT 去除重复项 , (默认是ALL)

拓展:其他一些查询语句👇

SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式

-- 学员考试成绩集体提分一分查看
SELECT `studentno`,`StudentResult`+1 AS '提分后' FROM result;

🍉4.4 where条件子句

逻辑操作符👇 image.png

模糊查询:比较查询符👇 image.png 通配符:

  • %
  • _
-- 模糊查询
-- LIKE
-- =============================================
-- 查询姓刘的同学的学号及姓名
-- 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 '%嘉%';

-- 查询姓名中含有特殊字符的需要使用转义符号 ''
-- 自定义转义符关键字: ESCAPE ':'

-- =============================================
-- IN
-- =============================================
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');

-- =============================================
-- NULL 空
-- =============================================
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- 查询出生日期填写的同学
SELECT studentname FROM student
WHERE BornDate IS NOT NULL;

-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

🍉4.5 联表查询(连接查询

狂神说MySQL04:使用DQL查询数据 (qq.com)

关键词:JOIN

  • JOIN对比
  • image.png

image.png

应用场景:

  • 就是你需要的数据,不止在一个表中,所以需要用到联表查询

思路:

  1. 分析需求,分析查询的字段分别来自哪些表
  2. 确定使用哪种连接查询——有七种
  3. 确定交叉点(这两个表中哪些数据是相同的)
    • 判断条件比如:学生表中studentNov=成绩表中studentNov

🫐1. 左右中

image.png

上面是左连接查询👆

  • 以左表为主根据条件(from后面为左表,也就是students)去查询右表数据(result),如果根据条件查询右表数据不存在,就使用null填充

  • 如果把上面变成右连接查询,就变成了返回右表result中的所有数据,而kuangshen这条信息只在students里有,所以就不会显示了

image.png

怎么区分哪个是左表右表

  • 不管左右连接,都是这样看的
  • 以哪个表查,结果就以哪个表为基准 联想截图_20240529171909.png
  • join on 连接查询
  • where 等值查询

🫐2. 自连接

自己和自己连接,核心:一张表拆成两张一样的表

比如: image.png 子类的pid和父类的categoryid连起来了

对应关系也就是:

image.png

代码表示就是:

SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

练习:

image.png

🫐3. 分页limit和排序order by

  • 排序:升序ASC,降序DESC

    • 语法:order by 用什么来排序 怎么排
  • 分页

    • 注意:limit是select语句的最后一句
    • 语法:limit 起始数据的位置(下标从0开始),页面的大小
    • 公式:
    • image.png

🍉4.6 子查询

查询时不想写具体的数,比如WHERE studentresult>80 ,而希望是计算的值

本质: 在where语句中嵌套一个子查询语句select()

由里及外 image.png

🍉4.7 嵌套查询

不喜欢这个 image.png

🍉4.8 MySQL函数

🫐4. 分组和过滤

group by ,having

 -- 查询不同课程的平均分,最高分,最低分
 -- 前提:根据不同的课程进行分组
 
 SELECT subjectname,AVG(studentre#sult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING 平均分>80;
 
 /*
 where写在group by前面.
 要是放在分组后面的筛选
 要使用HAVING..
 因为having是从前面筛选的字段再筛选,而where是从数据表中的>字段直接进行的筛选的
 */

🍉4.9 数据库级别的MD5加密

MD5不可逆

-md5()

🍍五. 事务


要么都成功,要么都失败

联想截图_20240601140122.png

  • 事务就是:将一组SQL放在一个批次中去执行
  • 事务有ACID原则——原子性,一致性,隔离性,持久性

  1. MySQL中默认是自动提交
  2. 使用事务时应先关闭自动提交
-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

流程👇

image.png

——————————————————————————————————————
-- 开始一个事务,标记事务的起始点
START TRANSACTION  


....


-- 成功就提交,失败就回滚

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
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'
UPDATE account SET money=money+500 WHERE `name`='B'

COMMIT;-- 事务一旦提交,就被持久化了
ROLLBACK;
SET autocommit=1;

🍍六. 索引

分类:👇

  • 主键索引 (Primary Key) ——只能有一个
  • 唯一索引 (Unique) ——可以有多个
  • 常规索引 (key/Index)
  • 全文索引 (FullText)

作用: 加速用户查询的速度

索引准则:👇

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表建议不要加索引
  • 索引一般应加在查找条件的字段

🍍七. 权限管理和备份

🍉7.1 用户管理

可视化操作

image.png

SQL命令行操作

-- 创建用户 CREATE USER用户名IDENTIFIED BY'密码'
CREATE USER kuangshen IDENTIFIED BY '123456'

-- 修改密码(修改当前用户密码)
SET PASSWORD=PASSWORD ('123456')

-- 修改密码(修改指定用户密码)
SET PASSWORD FOR kuangshen = PASSWORD ('123456')

-- 重命名 RENAME USER 原来名字 T0 新的名字
RENAME USER kuangshen TO kuangshen2

-- 用户授权ALL PRIVILEGES全部的权限,库.表
-- 除了给别人授权(GRANT)
GRANT ALL PRIVILEGES ON *.* TO kuangshen2

-- 查询权限
SHOW GRANTS FOR kuangshen2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost


-- 撤销权限 REVOKE 哪些权限 在哪个库撤销 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2

-- 删除用户
DROP USER kuangshen

🍉7.2 MySQL备份

数据库备份必要性

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

MySQL数据库备份方法

  • 直接拷贝数据库文件data和相关配置文件
  • 数据库管理工具(右键备份/导出——SQL转储),如SQLyog
  • 使用命令行导出 mysqldump备份工具
    • 比如:(管理员模式)
    • image.png
    • 其他看文档

🍍八. 如何设计一个项目的数据库和数据库的三大范式

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

image.png