mysql相关知识(详细)
一、什么是数据库？
概念：数据库(Database,简称DB)，长期存放在计算机内，有组织，可共享的大量数据的集合，是一个数据"仓库"。
作用：存放管理数据
分类：关系型数据库、NoSQL数据库
二、为什么学习MySQL？
特点：
操作便捷
小巧、功能齐全
免费，开源的数据库
可运行windows或linux系统
三、DDL建库建表
1.数据库结构
数据库结构分为数据库、数据表、字段、索引、记录、SQL语句。
2.SQL语句的分类
SQL语句，即结构化查询语言(Structured Query Language)，是一种特殊目的的编程语言，是一 种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统，同时也是数据库 脚本文件的扩展名。
SQL语句的分类
名词 解释 命令
DDL(数据定义语言) 定义和管理数据对象， 如数据库，数据表等 CREATE、DROP、ALTER
DML(数据操作语言) 用于操作数据库对象中所包含的数据 INSERT、UPDATE、DELETE
DQL(数据查询语言) 用于查询数据库数据 SELECT
DCL(数据控制语言) 用来管理数据库的语言，包括管理权 限及数据更改 GRANT、COMMIT、ROLLBACK
3.DDL语句操作数据库
---- 创建数据库
CREATE DATABASE dbname;
— 删除数据库
DROP DATABASE dbname;
— 查看数据库
SHOW DATABASES;
— 选择数据库
USE dbname;
4.DDL创建数据库表
-- 创建表
-- create table 表名(
-- 字段名 类型 属性,
-- 字段名 类型 属性,
-- ...
-- 字段名 类型 属性 #最后一个不带“,”
-- );
create table student (
stu_name varchar(10),
stu_age tinyint,
stu_birthday datetime,
stu_id char(18),
stu_score decimal(4,1)
);
4.1 数据库表中的字段类型
(1) 字符串类型
(2) 数值类型
注意：1.Decimal适用于精细准确的场景(钱)；m指字节长度，d指小数位数。最多存储999.9个字节！2.设置当前 创建时间，或者是更新时间时给TIMESTAMP或者DATETIME字段 设置 DEFAULT CURRENT_TIMESTAMP 添加当前默认时间
(3) Null类型 !!!
理解为“没有值”或“未知值”；
不要用NULL进行算术运算，结果仍为NULL；
MySQL中，0或NULL都意味着为假，1为真
4.2 数据库表中的字段注释
5.DDL查看数据库表结构
方式一：desc 表名 或 desccrible 表名
方式二：show create table 表名
-- 方式1
-- desc 表名
desc student;
-- 方式2
-- show create table 表名;
show create table student;
6.DDL修改和删除数据库表
-- 表结构的修改
-- alter table 表名 关键词 数据
-- 关键词：rename as add drop modify change
-- 1.修改表名
-- alter table 旧表名 rename as 新表名;
alter table student rename as xuesheng;
-- 2.添加字段
-- 字符串表示 'XXX' "XXX"
-- comment 添加声明
-- alter table 表名 add 新的字段名 类型 属性;
alter table xuesheng add stu_qq varchar(20) comment '这是学生的QQ号'
desc xuesheng; -- 查表
show create table xuesheng;
-- 3. 删除字段(危险操作)
-- alter table 表名 drop 字段名;
alter table xuesheng drop stu_age;
CREATE TABLE `xuesheng` (
`stu_name` varchar(10) DEFAULT NULL,
`stu_birthday` datetime DEFAULT NULL,
`stu_id` char(18) DEFAULT NULL,
`stu_score` decimal(4,1) DEFAULT NULL,
`stu_qq` varchar(20) DEFAULT NULL COMMENT '这是学生的QQ号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-- 4.修改字段
-- 覆盖式修改，1.有一定的默认值 2.如果已经存在具体的数据--数据是可以做隐式转化的，不存在字符全是字符串
-- 方式1 modify
-- alter table 表名 modify 字段名 要修改的类型 要修改的属性
alter table xuesheng modify stu_name varchar(100);
xuesheng CREATE TABLE `xuesheng` (
`stu_name` varchar(100) DEFAULT NULL,
`stu_birthday` datetime DEFAULT NULL,
`stu_id` char(18) DEFAULT NULL,
`stu_score` decimal(4,1) DEFAULT NULL,
`stu_qq` varchar(20) DEFAULT NULL COMMENT '这是学生的QQ号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-- 方式2 change
-- 数据类型和属性 还可以修改字段名
-- alter table 表名 change 旧字段名 新字段名 要修改的类型 要修改的属性;
alter table xuesheng change stu_qq qq varchar(20) COMMENT '这是学生的QQ号';
xuesheng CREATE TABLE `xuesheng` (
`stu_name` varchar(100) DEFAULT NULL,
`stu_birthday` datetime DEFAULT NULL,
`stu_id` char(18) DEFAULT NULL,
`stu_score` decimal(4,1) DEFAULT NULL,
`qq` varchar(20) DEFAULT NULL COMMENT '这是学生的QQ号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-- 5.删除表(危险操作)
-- drop table 表名
drop table xuesheng;
7.数据库存储引擎
7.1 数据库存储引擎-InnoDB
(1) MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后，默认采用InnoDB引擎。
(2) InnoDB是MysQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交 (Commit)和回滚(Rollback)。
(3) 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。
(4) 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
(5) 数据文件结构:
• 表名.frm存储表结构(MySQL8.0时，合并在表名.ibd中)。
• 表名.ibd存储数据和索引
(6) InnoDB是为处理巨大数据量的最大性能设计。
• 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比 如: .frm,.par , .trn ,.isl, .db.opt等都在MySQL8.0中不存在了。
(7) 对比MylISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。
(8) MyISAM只缓存索引，不缓存真实数据;InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性 能有决定性的影响
7.2 数据库存储引擎-MyISAM
(1) MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外 键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
(2) 5.5之前默认的存储引擎
(3) 优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用
(4) 针对数据统计有额外的常数存储。故而count(*)的查询效率很高
(5) 数据文件结构:
• 表名.frm存储表结构。
• 表名.MYD存储数据(MYData)。
• 表名.M数据库存储引擎-MyISAM 和InnoDB区别 YI存储索引 (MYIndex)
(6) 应用场景:只读应用或者以读为主的业务
7.3 数据库存储引擎-MyISAM 和InnoDB区别
对比项 MyISAM InnoDB
外键 不支持 支持
事务 不支持 支持
行表锁 表锁，即使操作一条记录也会锁住整 个表，不适合高并发操作 行锁，操作时只锁某一行，不对其它行有 影响，适合高并发操作
缓存 只缓存索引，不缓存真实数据 不仅缓存索引还要缓存真实数据，对内存 要求较高，而且内存大小对性能有决定性 的影响
默认安装 Y Y
默认使用 N Y
关注点 性能：节省资源、消耗少、简单业务 事务：并发写，事务、更大资源
8.三大范式
8.1 什么是范式
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。 在关系型数据库中这种规则就叫做范式。
8.2 约束作用
数据库的设计范式是数据库设计所需要满足的规范，满足这些规范的数据库是简洁的、结 构明晰的，同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常。
8.3 三范式
第一范式：确保每列保持原子性
第二范式：确保表中的每列都和主键相关
第三范式：确保每列都和主键列直接相关，而不是间接相关
8.4 三大范式详解
第一范式（1NF）确保每列保持原子性。
每一列属性都是不可再分的属性值，确保每一列的原子性
两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据
原子性能拆就违反了第一范式。比如地址没有分省市县镇。邮箱后缀名有很多...
第二范式（2NF）属性完全依赖于主键
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的。即满足第二范式必须先满足第一范式。
第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表 加上一个列，以存储各个实例的惟一标识。这个唯一属性列被称为主键。
第三范式（3NF）属性不依赖于其它非主属性 属性直接依赖于主键
数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。
像：a–>b–>c 属性之间含有这样的关系，是不符合第三范式的。
比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话） 这样一个表结构，就存在上述关系。 学号–> 所在院校 --> (院校地址，院校电话) 这样的表结构，我们应该拆开来，如下。
（学号，姓名，年龄，性别，所在院校）–（所在院校，院校地址，院校电话）
总结：三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。 如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结 构。所以不能一味的去追求范式建立数据库。
9. 约束
9.1 什么是约束
约束实际上就是表中数据的限制条件
9.2 约束作用
表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效
9.3 约束种类
非空约束(not null)
唯一性约束(unique)
主键约束(primary key)
PK 外键约束(foreign key)
FK 检查约束(目前MySQL不支持、Oracle支持
9.4 约束的添加
添加非空约束
alter table 表名 modify test_student char(10) not null;
添加唯一约束
alter table 表名 add unique(表字段名，字段，字段，字段);
添加主键约束
alter table 表名 add primary key(表的字段名，字段，字段)；
添加外键约束
alter table 表名 add constraint N1 foreign key (表字段名) references 父表(父表字段名);
9.5 约束删除
删除not null约束
alter table 表名 modify 列名 类型;
删除unique约束
alter table 表名 drop index 唯一约束名;
删除primary key约束
alter table 表名 drop primary key;
删除foreign key约束
alter table 表名 drop foreign key 外键名;
#约束
-- 非空约束
create table tb1(
username varchar(10),
userage int
);
show create table tb1;
create table tb2(
username varchar(10) not null, -- 非空约束
userage int DEFAULT NULL
);
create table tb3(
username varchar(10) not null default '无名', -- 非空约束
userage int
);
-- 唯一约束
-- 唯一约束可以为NULL，并且可以有多个null。因为null是一个类型
-- 方法1：行级约束
create table tb4(
username varchar(10) unique, -- 唯一约束 行级约束
userage int
);
create table tb6(
username varchar(10) unique, -- 唯一约束 行级约束
userage int unique -- 每个字段都是各自的唯一约束
);
– 方法2：联合约束
– 字段是有关联的，只有两个字段的值一模一样时(同时满足)，才存不进去
create table tb5(
username varchar(10),
userage int,
unique(username,userage) – 联合唯一约束
);
-- 给约束起名字
create table tb7(
username varchar(10),
userage int,
CONSTRAINT name_age_unique unique(username,userage)
);
– 主键约束
– 每张表必须有且只有一个主键
– 主键的值是唯一的
– 主键是不能为NULL的
create table tb8(
username varchar(10) primary key,
userage int
);
create table tb9(
username varchar(10),
userage int,
primary key(username,userage) – 联合主键，联合约束
);
-- 一般逐渐建立方式
-- int bitint 自增
-- 主键的值不会回补的
create table tb10(
tid int primary key auto_increment,
username varchar(10),
userage int
);
– 外键约束
– 外键的值可以重复
– 外键得值可以为NULL
– 外键必须要写父表中的有的数据
– 父表的关联字段必须是一个具有唯一性的数据
– 父表的字段名和从表中的字段名不要一定相同，但是数据类型必须是一致的
– 一个表中可以有多个外键，也可以有多个外键约束，但是只能有一个主键
– 使用外键关系，不去建立外键约束
create table a( – 主表
aid int primary key auto_increment,
aname varchar(10)
);
drop table b;
create table b( -- 从表
bid int primary key auto_increment,
bname varchar(10),
aid int, -- aid就是表b的外键
FOREIGN key(aid) REFERENCES a(aid)
);
面试常问？
1.char() 和 varchar()区别？
答：
(1) char(字符串长度) ==== > 固定长度字符串
varchar(字符串长度) ==== > 可变长度字符串
(2)字符串长度是最大长度，超过最大长度谁都存不下。
(3)varchar()不浪费空间，效率低。适用于非char情况 ==》 存名字
char()浪费存储空间，效率高，适用于存储不大，速度要求要高。==》存电话、身份证号码
另外：varchar(50)中的50指字符串最大长度
2. MySQL中InnoDB与MyISAM的区别是什么？
答：
（1）InnoDB支持事务处理、外键、行级锁；擅长增删改查，适用于高并发以及事务处理的场景。
（2）MyISAM不支持事务处理、外键、行级锁。擅长读取，一旦崩坏将无法复原。优点是存的快，查的也快；节省资源，消耗少。适用于查询密集型的场景。
3.MySQL中nt(1)与int(1)的区别?int(1)中的1 代表的涵义?
答：在 MySQL 中，int(1)和int(11)的区别仅在于显示宽度。
int(1)中“1”表示显示宽度为 1 位，int(11)中“11”表示显示宽度为 11 位。但它们的存储范围和存储空间相同，都能存储 -2147483648 到 2147483647 的整数，占用 4 个字节。 显示宽度只影响显示效果，不影响存储内容。
4.三大范式是什么。都有哪些内容？
答：
第一范式：确保每列都必须保持原子性
第二范式：确保每列都与主键相关
第三范式：确保每列都与主键直接相关，而不是间接相关。
5.外键约束和外键关系是一回事儿？
答：
不是，外键关系指两个表通过外键建立的关联。
外键约束是保障外键关系完整性、一致性原则。例：确保外键值不被引用表的逐渐或唯一键中存在。
6.delete\truncate\drop的区别？
答：
如果只是想删除表中的数据，可以根据具体情况选择 DELETE 或 TRUNCATE。如果要彻底删除表本身，包括结构和数据，就使用 DROP