mysql
1、命令行链接
mysql -uroot -proot --连接数据库
---------------------------
show databases; --查看所有数据库
show tables; --查看数据库中所有的表
describe student; --显示数据库中所有的表的信息
create database test; --创建一个数据库
exit; --退出链接
DDL 定数据据义xxx语言 CRUD 增删改查
DML 操作
DQL 查询
DCL 控制
2、操作数据库
2.1、数据库
1、创建数据库
CREATE DATABASE [IF EXISTS] test;
2、删除数据库
DROP DATABASE [IF EXISTS] test;
3、使用数据库
-- 如果表明或者字段名是一个特殊字符，使用 ``
USE `test`
4、查看数据
SHOW DATABASE -- 所有数据库
2.2、数据库类型
数值
- tinyint 1个字节
- smallint 2个字节
- medulumint 3个字节
- int 4个字节
- bigint 8个字节
- float 4个字节
- double 8个字节(精度问题)
- decimal 字符串形式的浮点数 金融计算的时候，一般是使用decimal
字符串
-
char 字符串 0-255
-
varchar 可变字符串 0-65536 常用的变量 String
-
tinytest 2^8-1
-
text 文本串 2^16-1
时间日期
java.util.Date
-
date YYYY-MM-DD,日期
-
time HH: mm: ss 时分秒
-
datetime YYYY-MM-DD HH: mm: ss 最常用的时间格式
-
timestamp 时间戳, 1970.1.1 到现在的毫秒数!
-
year 年份表示
null
- 没有值，未知
2.3、数据库的字段属性（重点）
Unsigned:
-
无符号的整数
-
声明了该列不能声明负数
zerofill:
-
0填充
-
不足的位数，使用0来填充， int(3) , 5 ---005
自增:
-
通常理解自增，自动在上一条记录的基础上+1(默认)
-
通常用来设计唯一的主键-index,必须是整数类型
-
可以自定义设计主键自增的起始值和步长
非空：null not null
- 假设设置为not null ,如果不给他赋值，就会报错
- Null ，不过不填写值，默认就是null
默认：
- 设置默认的值！
- sex ，默认设置为男，如果不指定该列的值，则会有默认的值！
常用命令
show create database school --查看创建数据库语句
show create table student --查看student数据表的定义语句
desc student --显示表的结构
2.4创建数据库表
格式
create table [if not exists] `student`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
创建表student
CREATE TABLE if not exists `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '12345678' COMMENT '密码',
`sex` varchar(20) 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=utf8mb3
2.5、数据表的类型
2.6、修改删除表
修改表
--修改表名 alter table 旧表名 rename as 新表名;
alter table student1 rename as teacher
--增加表字段 alter table 表名 add 字段名，列属性；
alter table teacher add hobby varchar(50);
--修改表的字段 (重命名,修改约束)
alter table teacher modify hobby LONGTEXT; --修改约束
alter table teacher change hobby hobby1 varchar(50); --字段重新命名
--删除表字段
alter table teacher drop hobby;
删除表
--删除表 (如果表存在再删除) drop table [if exists] teacher;
3、MySQL数据管理
3.1、外键
create table [if not exists] `student`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
CREATE TABLE `student1` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '12345678' COMMENT '密码',
`sex` varchar(20) 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=utf8mb3
--修改表名 alter table 旧表名 rename as 新表名;
alter table student1 rename as teacher
--增加表字段 alter table 表名 add 字段名，列属性；
alter table teacher add hobby varchar(50);
--修改表的字段 (重命名,修改约束)
alter table teacher modify hobby LONGTEXT; --修改约束
alter table teacher change hobby hobby1 varchar(50); --字段重新命名
--删除表字段
alter table teacher drop hobby;
--删除表
drop table if exists teacher;
create table `grade`(
`gradeid` int(10) not null auto_increment comment '年级id',
`gradename` varchar(50) not null comment '年级名称',
primary key (`gradeid`)
)engine=innodb DEFAULT charset=utf8
drop table if exists student;
-- 学生表的gradeid 字段要去引用年级表的gradeid
--定义外键key
--给这个外键添加约束 (执行引用)
CREATE TABLE `student1` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '12345678' COMMENT '密码',
`sex` varchar(20) 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 `FK_gradeid` (`gradeid`),
constraint `FK_gradeid` FOREIGN key (`gradeid`) references`grade`(`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
删除有外键关系的表的时候，必须要先删除引用别人的表(从表)，再删除被引用的表(主表)
创建表的时候没有创建外键关系
alter table `student1`
add constraint `FK_grabeid` foreign key(`gradeid`) references `grade` (`gradeid`);
alter table 表 add constraint 约束名 foreign key(作为外键的列)references 哪个表(哪个字段)
3.2、DML语言
数据库的意义:数据储存，数据管理
DML语言:数据操作语言
-
insert
-
update
-
delete
3.2.1、添加
insert
--插入语句(添加)
--insert into 表名(字段名1，字段名2，字段名3)values('值1'),('值2'),('值3')
insert into `grade`(`gradename`) values('大四');
-- 一般写插入语句，我们一定要数据和字段一一对应
-- 插入多个字段
insert into `grade`(`gradename`)
values('大一'),
('大二');
insert into `student1`(`name`,`pwd`,`sex`,`birthday`,`gradeid`,`address`,`email`) values
('李四','123456','男','2020-2-2','2','湖北','23049340.com'),
('张三','123456','男','2020-2-2','2','湖北','23049340.com');
-- 语法 : insert into 表明[字段1，字段2，字段3....] values(值1),(值2)
3.2.2、修改
update 修改谁 (条件) set 原来的值=新值
select * from student1
-- 修改名字
update `student1` set `name` = '李武' where id = 1;
-- 不指定条件的情况下，会改动所有的表!
update `student1` set `name` = 'chw' ;
--修改多个属性
update `student1` set `name` = 'lsl',`pwd` = '12345345'where id = 1
-- 语法
-- update 表名 set colnum_name=value,[colnum_name=value,......] where [条件]
条件: where 子句 运算符 id 等于某个值，大于某个值，在某个区间内修改
操作符会返回 布尔值
特殊的
value 可以是一个变量
update `student1` set `birthday` = CURRENT_TIME where `name`='lsl' and `sex` = '男'
3.2.3、删除
delete 命令
语法 delete from 表明 where [条件]
-- 删除数据(避免这样写)
delete from `student1`
--删除数据 加条件
delete from `student1` where id = 1;
完全清空一张表,表结构和索引约束不会变
-- 清空一张表
truncate `student`
delete 和 truncate区别
- 相同点：都能删除数据，都不会删除表结构
- 不同
- truncate 重新设置 自增列 计数器会归零
- truncate 不会影响事物
4、DQL查询数据
4.1、DQL
(Data Query language:数据查询语言)
- 所有的查询操作都用它，Select
- 简单的查询，复杂的它都能做
- 数据库中最核心的语言，最重要的语言
Select 完整的语法
4.2、指定查询字段
语法: SELECT 字段 FROM 表
-- 查询全部学生 SELECT 字段 FROM 表
select * from student
-- 查询指定字段
select loginpwd,studentname from student
-- 别名
select loginpwd as 学号,studentname as 名字 from student
-- 函数 concat(a,b)
select concat('姓名: ',Studentname) as 新名字 from student
distinct 重复数据去重
-- 重复数据去重 distinct
select distinct studentno from result
数据库的列（表达式）
select VERSION() -- 查询系统版本 (函数)
SELECT 100*3-1 as 计算结果 -- 用来计算(表达式)
select @@auto_increment_increment -- 查询自增的步长 (变量)
-- 成绩加一分
select studentno,studentresult+1 as 提分后 from result
数据库中的表达式，文本值，列,NUll函数，计算表达式，系统变量.....
select 表达式 from 表
模糊查询
4.3、联表查询
-- ==============联表查询 join================
-- 查询参加了考试的同学(学号，姓名，科目，分数)
select * from student
select * from result
/* 思路
1，分析需求查询的字段来自那些表(连接查询)
2，确定使用哪种连接查询?
确定交叉点（这两个表中那个数据是相同的）
*/
-- inner join
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
-- righe 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
-- 多表连接
select s.studentno,studentname,subjectname,studentresult
from student as s
right join result as r
on r.studentno = s.studentno
left join `subject` as sub
on r.subjectno = sub.subjectno
4.4、分页和排序
分页
-- ==================分页 limit 和排序order by=========
-- order by
-- 排序: 升序 ASC ，降序 DESC
-- order by 通过那个字段 怎么排
select s.studentno,studentname,subjectname,studentresult
from student as s
right join result as r
on r.studentno = s.studentno
left join `subject` as sub
on r.subjectno = sub.subjectno
order by studentresult asc
排序
-- 分页
-- 语法: limit 起始页 ，页面大小
-- limit 0,5 1~5
-- limit 1,5 2~5
select s.studentno,studentname,subjectname,studentresult
from student as s
right join result as r
on r.studentno = s.studentno
left join `subject` as sub
on r.subjectno = sub.subjectno
order by studentresult asc
limit 2,5
-- 第N页 limit N,5 (n-1)*pageSize, pageSize
-- 【pageSize ,页面大小】
-- 【(n-1)*pageSize:起始值】
-- 【n:当前页】
-- 【数据总数/页面大小 = 总页数】
4.5、子查询
在where 语句中嵌套一个子查询语句
-- 联表查询
select s.studentno,studentname,studentresult
from student as s
inner join result as re
on s.studentno = re.studentno
left join subject as sub
on sub.subjectno = re.subjectno
where subjectname = '高等数学-1' and studentresult >80
-- 子查询
select s.studentno,studentname,studentresult
from student as s
inner join result as re
on s.studentno = re.studentno
where studentresult>80 and subjectno =(
select subjectno from `subject`
where subjectname = '高等数学-1'
)
4.6、分组过滤
-- 查询不同课程的平均分,最低分和最低分
select subjectname,avg(studentresult) as 平均分,max(studentresult) as 最高分,min(studentresult) as 最低分
from result r
inner join `subject` sub
on sub.subjectno = r.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING 平均分 >90
select小结
4、mysql函数
4.1、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('aaaaaa') -- 字符串长度
select concat('你','是','谁') -- 拼接字符串
select insert('你是谁',1,2,'李四') -- 查询，替换 从第一个开始到第二个替换 李四谁
select lower('ASJSJS') -- 把大写转换为小写
select upper('asjsjs') -- 把小写转换为大写
select instr('alalazll','z') -- 返回第一次出现的子串索引 6
select replace('人参','参','生') -- 替换出现的指定字符串 人生
select substr('人生总有一两风',3,5) -- 返回指定的字符串(源字符串，截取的位置，截取的长度) 总有一两风
select reverse('人生总有一两风') -- 反转
-- 时间和日期
select CURRENT_DATE() -- 获取当前日期
select CURDATE() -- 获取当前日期
select NOW() -- 获取当前的时间
select LOCALTIME() -- 获取本地的日期 和 时间
select SYSDATE() -- 系统时间
select CURRENT_TIME() -- 获取当前时间
select YEAR(now())
select MONTH(now())
select DAY(now())
select HOUR(now())
select MINUTE(now())
select SECOND(now())
4.2、聚合函数
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
| ...... | ...... |
select count(loginpwd) from student; -- count(字段) ，会忽略所有的null值
select count(*) from student; -- count(*),不会忽略null值计算行数
select count(1) from result; -- count(1)，不会忽略所有的bull值，本质 计算行数
select sum(`studentresult`) as 总分 from result
select avg(`studentresult`) as 平均分 from result
select max(`studentresult`) as 最高分 from result
select min(`studentresult`) as 最低分 from result
4.3、数据库级别的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,'lsl','123456'),
(2,'chw','123456'),
(3,'zzj','123456')
-- 加密
update testmd5 set pwd=MD5(pwd)
-- 插入的时候加密
insert into testmd5 values(4,'ql',md5('123456'))
-- 如何校验：将用户传递过来的密码，进行md5加密，然后对比加密后的值
select * from testmd5 where `name` ='lsl' and pwd=md5('123456')
5、事务
要么都成功，要么都失败
事务原则：ACID原则 原子性，一致性，隔离性，持久性
原子性(Acomicity)
要么都成功，要么都失败
一致性(Consistency)
事务前后数据完整性要保证一致
持久性(Durabllity) ---事务提交
事务一旦提交则不可逆，被持久化到数据库中！
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时，数据库为每个用户开启事务，不能被其他事务操作数据所干扰，事务之间要相互隔离
隔离所导致的一些问题
脏读：
指一个事务读取了另一个事务未提交的数据
不可重复读:
在一个事务内读取表中的某一行数据，多次读取结果不同(这个不一定是错误，只是某些场合不对)
虚读(幻读)
是指在一个事务内读到了别的事务插入的数据，导致前后读取不一致
执行事务
-- =================事务==================
-- mysql 是默认开启事务字段提交的
set autocommit = 0; /* 关闭*/
set autocommit = 1;/* 开启(默认的)*/
-- 手动处理事务
-- 事务开启
start TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事物内
insert
insert
-- 提交：持久化(成功)
COMMIT
-- 回滚: 回到原来的样子(失败)
ROLLBACK
-- 事务结束
set autocommit =1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
rollback to savepoint 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名
模拟
-- 转账
create database shop character set utf8 collate utf8_general_ci
use shop
select * from account
-- 模拟转账 ：事务
set autocommit = 0;-- 关闭自动提交
start transaction -- 开启一个事务
update account set money=money-500 where `name` = 'B' -- A减500
update account set money=money+500 where `name` = 'A' -- B加500
commit -- 提交事务
rollback -- 回滚事务
set autocommit = 1; -- 恢复默认值
6、索引
索引(index) 是帮助mysql高效率获取数据的数据结构
6.1、索引的分类
- 主键索引(primary key)
- 唯一标识，主键不可重复，只能有一个列作为主键
- 唯一索引(unique key)
- 避免重复的列出现。唯一索引可以重复。多个列都可以标识为唯一索引
- 常规索引(key/index)
- 默认的，index，key关键字来设置
- 全文索引(Fulltext)
- 在特定的数据库引擎下才有，myisam
- 快速定位数据
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用户表'
-- 默认用户不得创建或修改存储函数。
/*log_bin_trust_function_creators设为1即可。
log_bin_trust_function_creators控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。*/
set global log_bin_trust_function_creators=1;
-- 插入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),'1423423@qq.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.724s
explain select * from app_user where `name` = '用户9999'; -- row 993105
-- id_表名_字段名
-- create index 索引名 on 表(字段)
create index id_app_user_name on app_user(`name`);
select * from app_user where `name` = '用户9999'; -- 耗时 0.043s
explain select * from app_user where `name` = '用户9999'; -- rows 1
没索引
有索引
索引在小数据量的时候，用户体验不大，但是在大数据的时候，区别十分明显~
7.2、索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上!
索引的数据结构
Hash类型的
Btree :innodb的默认数据结构
7、规范数据库设计
7.1、为什么要设计
当数据库比较复杂的时候，我们就需要设计了
糟糕的数据库设计:
- 数据冗余，浪费空间
- 数据插入和删除都会麻烦，异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计
- 分析需求，分析业务和需要处理的数据库需求
- 概要设计：设计关系图E-R图
设计数据库的步骤(个人博客)
- 收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文字分类，谁创建的）
- 文章表（文章的信息）
- 友链表（友链信息）
- 标识实体（落地到每个字段）
9.2、三大范式
为什么需要数据规范化？
-
信息重复
-
更新异常
-
插入异常
- 无法正常显示信息
-
删除异常
- 丢失有效的信息
三大范式
第一范式
原子性：保证每一列不可再分
第二范式
前提：满足第一范式
每张表只描述一件事情
第三范式
前提：满足第一范式，第二范式
目标是确保每列都和主键列直接相关,而不是间接相关(另外非主键列必须直接依赖于主键，不能存在传递依赖
规范性和性能的问题
关联查询不得超过三张表
-
考虑是商业化的需求和目标（成本，用户体验！）数据库的性能更加重要
-
在规范性能的问题的时候，需要适当的考虑下规范性！
-
故意给某些表增加一些冗余的字段（从多表查询变为单表查询）
-
故意增加一些计算列(从大数据量降低为小数据量的查询;索引)
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？