自学内容网 自学内容网
自学内容网 > 正文

【重生之我在B站学MySQL】

🕗 发布于 2024-12-11 13:30 mysql  数据库  经验分享  笔记  

MySQL笔记

文章目录

MySQL的三层结构

image-20241203223227568

SQL语句分类

DDL:数据定义语句[create表，库…]
DML:数据操作语句[增加insert,修改update,删除delete]
DQL:数据查询语句[select]

image-20241205232118415image-20241205232538782image-20241205232307272

DCL:数据控制语句[管理数据库：比如用户权限grant revoke]

TCL:不是王牌电视。是事务控制语言包括：事务提交：commit;事务回滚：rollback;

sql语句

image-20241203230922869

数据库操作

创建数据库

在创建数据库，表的时候，为了规避关键字，可以使用反引号解决
CREATE DATABASE `CREATE`

#创建一个名称为hsp_db01的数据库
CREATE DATABASE hsp_db01;

#创建一个utf8字符集的hsp_db02数据库
CREATE DATABASE hsp_db02 CHARACTER SET utf8;

#创建一个使用utf8字符集，并带校对规则的hsp_db03数据库
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin;

查看、删除数据库

#显示数据库
SHOW DATABASES;

#显示数据库创建语句
SHOW CREATE DATABASE db_name;

#数据库删除语句
DROP DATABASE [IF EXISTS] db_name;

表操作

创建表

image-20241204200249685

image-20241204201916348

image-20241204200856914image-20241204201200798

mysql常用数据类型(列类型)

枚举类型

image-20241208172734863

字符串使用细节

char(4) //这个4表示字符数（最大255），不是字节数，不管是中文还是字母都
是放四个，按字符计算.

varchar(4) //这个4表示字符数，不管是字母还是中文都以定义好的表的编码
来存放数据
不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的，

char(4)和 varchar(4)这个4表示的是字符，而不是字节，不区分字符是汉字还是字母

什么时候使用char,什么时候使用varchar

1.如果数据是定长，推荐使用char,比如md5的密码，邮编，手机号，身份证号码等.char(32)
2.如果一个字段的长度是不确定，我们使用varchar,比如留言，文章
查询速度：char > varchar

在存放文本时，也可以使用Text数据类型.可以将TEXT列视为 VARCHAR列，注意Tet不能有默认值.大小0-216字节如果希望存放更多字，可以选择 MEDIUMTEXT 0-2^24或者LONGTEXT 0~2^32

image-20241204205707294

查询表、插入值

# 查询所有从t1表条件是name=tom
SELECT * FROM t1 WHERE NAME = 'tom';

# 给t3表添加一个值
INSERT INTO t3 VALUES(127);

# 显示表的结构和约束
desc 表名

创建表练习

创建一个员工表emp
image-20241204224836677

image-20241204230431429

修改表

image-20241205192715062

image-20241205195932498

image-20241205200432852

image-20241205200658076

mysql约束

image-20241208151625004
primary key(主键)

primary key不能重复而且不能为null

一张表最多只能有一个主键，但可以是复合主键（比如id+name)

image-20241208152729172

复合主键

id+name同时相同才不允许添加

image-20241208154051430

image-20241208154519623

not null(非空)
image-20241208155344519
unique(唯一)
image-20241208155416122
foreign key(外键)

1.外键指向的表的字段，要求是primary key或者是 unique
2.表的类型是innodb,这样的表才支持外键
3.外键字段的类型要和主键字段的类型一致（长度可以不同）
4.外键字段的值，必须在主键字段中出现过，或者为null[前提是外键字段允许为null]
5.一旦建立主外键的关系，数据不能随意删除了

image-20241208160543551

外键示意图

image-20241208161541550
check

MySQL从8.0.16版本开始支持CHECK约束，并使其生效。在MySQL 8.0.16之前的版本中，虽然允许使用CHECK (expr)形式的检查约束语法，但实际上解析之后会忽略该子句，CHECK约束并不会对数据进行检查，而是仅仅作为注释存在。

CHECK约束是一种完整性约束，用于限制表中数据的取值范围，可以确保插入或更新的数据满足特定的条件。它有助于维护数据的完整性，防止非法数据的插入或更新，并且通过在数据库层面进行数据验证，可以减少应用程序中的错误处理逻辑，提高性能。

在MySQL中，CHECK约束可以分为基于列的CHECK约束和基于表的CHECK约束。基于列的CHECK约束应用于单个列，限制该列的取值范围；而基于表的CHECK约束则应用于整个表，可以涉及多个列的组合条件。

因此，如果需要使用CHECK约束来限制MySQL表中数据的取值范围，请确保使用的MySQL版本为8.0.16或更高版本。

image-20241208164716335

商店表设计

image-20241208184813397

CREATE DATABASE shop_db;


-- 商品goods
CREATE TABLE goods (
  goods_id INT PRIMARY KEY,
  goods_name VARCHAR (64) NOT NULL DEFAULT '',
  unitprice DECIMAL (10, 2) NOT NULL DEFAULT 0 CHECK (unitprice >= 1.0 AND unitprice <= 9999.99),
  category INT NOT NULL DEFAULT 0,
  provider VARCHAR (64) NOT NULL DEFAULT ''
);

-- 客户customer
CREATE TABLE customer (
  -- 客户
  customer_id VARCHAR (8) PRIMARY KEY,
  -- 姓名
  `name` VARCHAR (64) NOT NULL DEFAULT '',
  -- 地址
  address VARCHAR (64) NOT NULL DEFAULT '',
  -- 邮箱
  email VARCHAR (64) UNIQUE NOT NULL,
  -- 性别
  sex ENUM ('男', '女') NOT NULL,
  -- 身份证
  card_Id CHAR(18)
); 

-- 购买
CREATE TABLE purchase (
  -- 订单号
  order_id INT UNSIGNED PRIMARY KEY,
  -- 客户号
  customer_id CHAR(8) NOT NULL DEFAULT '',
  -- 商品号
  goods_id INT NOT NULL DEFAULT 0,
  -- 购买数量
  nums INT NOT NULL DEFAULT 0,
  FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
  FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);
自增长

1.一般来说自增长是和primary key配合使用的
2.自增长也可以单独使用[但是需要配合一个unique]
3.自增长修饰的字段为整数型的（虽然小数也可以但是非常非常少这样使用)
4.自增长默认从1开始，你也可以通过如下命令修改alter table 表名 auto increment = xxx;
5.如果你添加数据时，给自增长字段（列）指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据

image-20241208185210637

索引

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql,查询速度就可能提高百倍干倍。

索引的代价

1.磁盘占用
2.对dml(update delete insert)语句的效率影响（会对索引进行维护，对速度有影响）

索引原理

没有索引为什么会慢？因为全表扫描
使用索引为什么会快？形成一个索引的数据结构，比如二叉树

image-20241208212649767image-20241208213106577

哪些列上适合使用索引

1.较频繁的作为查询条件字段应该创建索引
select from emp where empno 1
2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select from emp where sex = ‘男’
3.更新非常频繁的字段不适合创建索引
select from emp where logincount = 1
4.不会出现在WHERE子句中字段不该创建索引

image-20241208204331392

image-20241208204354894

主键索引

主键自动的为主索引(类型Primary key)

image-20241208222627860
唯一索引(UNIQUE)

如果某列的值，是不会重复的，则优先考虑使用unique索引，否则使用普通索引

image-20241208222841806
普通索引(INDEX)

image-20241208222418549

全文索引(FULLTEXT)[适用于MylSAM]

一般开发，不使用mysql自带的全文索引，而是使用：全文搜索Solr ElasticSearch (ES)

索引使用

查询表是否有索引

image-20241208223709359

添加、删除索引

image-20241208221000352

事务

什么是事务
事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dl语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性。

事务和锁
当执行事务操作时(dml语句)，mysql:会在表上加锁，防止其它用户改表的数据.这对用户来讲是非常重要的

引出事务

image-20241208231951058

基本操作

回退事务
在介绍回退事务前，先介绍一下保存点(savepoint).保存点是事务中的点.用于取消部分事务，当结束事务时(commit),会自动的删除该事务所定义的所有保存点.当执行回退事务时，通过指定保存点可以回退到指定的点。

提交事务
使用commiti语句可以提交事务.当执行了commiti语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话【其他连接】将可以查看到事务变化后的新数据【所有数据就正式生效】

image-20241208230328519

image-20241208234026626
事务细节

1.如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
2.如果开始一个事务，你没有创建保存点.你可以执行rollback,默认就是回退到你事务开始的状态
3.你也可以在这个事务中（还没有提交时），创建多个保存点.
比如：
savepoint aaa;
执行dml
savepoint bbb;
4.你可以在事务没有提交前，选择回退到哪个保存点
5.mysql的事务机制需要lnnoDB的存储引擎才可以使用，MyISAM不支持
6.开始一个事务start transaction 或者 SET autocommit=off;

image-20241209200123520

事务隔离级别

事务隔离级别介绍
1.多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。（通俗解释）
2.如果不考虑隔离性，可能会引发如下问题：
脏读
不可重复读
幻读

image-20241209212550147

image-20241209204252031

查看和设置事务隔离级别

image-20241209215138361

全局修改事务隔离级别

image-20241209215850240

事务的acid特性

image-20241209220318484

数据库CRUD语句

image-20241205201229719 image-20241205201520428 image-20241205205823371 image-20241205211218034

1.如果不使用where子句，将删除表中所有数据。
2.Delete语句不能删除某一列的值（可使用update设为null或者"）
3.使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。drop table 表名；

单表查询

image-20241205214201147
表达式与as语句
image-20241205215906764
运算符
image-20241205222156523
排序
image-20241205225856390
使用group by子句对列进行分组
image-20241206200442246
使用having子句对分组后的结果进行过滤
image-20241206200511742
关键字
DISTINCT 去重
image-20241207172738557
函数
统计函数
统计 - count
image-20241205231601646

count(*)和count(列)的区别
*解释：count(*)返回满足条件的记录的行数
count(列)：统计满足条件的某列有多少个，但是会排除为null的情况

求和函数 - sum
image-20241206192706341

■统计一个班级数学总成绩？

■统计一个班级语文、英语、数学各科的总成绩

■统计一个班级语文、英语、数学的成绩总和

■统计一个班级语文成绩平均分

注意：sum仅对数值起作用，否则没有意义。

注意：对多列求和，“，”号不能少。

平均值 - avg
image-20241206194953274
最大值 max
image-20241206195346459
最小值 min

image-20241206195938395

字符串函数

image-20241206204514682

image-20241206221329507

数学函数

image-20241206222558429

rand()返回一个随机浮点值y,范围在0到1之间（即，其范围为0≤V≤1.0)。若已指定一个整数参数W,则它被用作种子值，用来产生重复序列。

1.如果使用rand()每次返回不同的随机数，在0≤v≤1.0

2.如果使用 rand(seed)返回随机数，范围0≤v≤1.0，如果seed不变，该随机数也不变了

时间日期函数

image-20241206225052346

image-20241206234154200

image-20241206233204753

image-20241206233835114

加密和系统函数

image-20241207131441917

流程控制函数

image-20241207135008211

image-20241207161008590

单表查询加强

image-20241207162525588

image-20241207162714260

image-20241207162939639

image-20241207163641412

分页查询

image-20241207164210291

image-20241207164825833

image-20241207165006573

多表查询

笛卡尔集
image-20241207185339200

image-20241207185925493

自连接

自连接是指在同一张表的连接查询

image-20241207192023840

子查询

单行子查询
image-20241207195131998
多行子查询

image-20241207200304380
多行子查询中使用all与any操作符

image-20241207213653911

image-20241207214824009

多列子查询

请思考如何查询与allen的部门和岗位完全相同的所有雇员（并且不含allen本人）

(字段1，字段2…)=(select 字段1，字段2 from…)

image-20241207220547636

image-20241207220630854 image-20241207221354846
子查询临时表

image-20241207201414288

image-20241207202021937

image-20241207222837575

image-20241207222852425

image-20241207223610673

查询每个部门的信息（包括：部门名，编号，地址）和人员数量

image-20241207224548449

表复制与去重

image-20241208000301623

image-20241208125008064

image-20241208125608909

合并查询union,union all

image-20241208135844428 image-20241208140007638

外连接

1.左外连接(如果左侧的表完全显示我们就说是左外连接)

image-20241208144421837 image-20241208144515984

2.右外连接(如果右侧的表完全显示我们就说是右外连接)

image-20241208150642089

存储引擎

基本介绍

InnoDB存储引擎：支持事务、行级锁定和外键
MRG_MYISAM存储引擎：收集相同的MyISAM表
MEMORY存储引擎：基于哈希的，存储在内存中，对临时表有用
BLACKHOLE存储引擎：写入到这的数据会被销毁
MYISAM存储引擎：不支持事务
CSV存储引擎：CSV存储引擎
ARCHIME存储引擎：存档类型的存储引擎

image-20241209221725006

显示当前数据库支持的存储引擎

show engines;

主要的存储引擎/表类型特点

1.MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求

2.InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起
MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留
数据和索引。

3.MEMORY?存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应
一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，
并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉，表的结构还
在。

image-20241210114901372

如何选择表的存储引擎

image-20241210174314807

修改存储引擎

-- 指令修改存储引擎
ALTER TABLE `t29` ENGINE = INNODB;

视图

看一个需求

image-20241210175632204

基础概念

1.视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
2.视图和基表关系的示意图

对视图的总结

1.视图是根据基表来创建的视图是虚拟的表
2.视图也有列，数据来自基表
3.通过视图可以修改基表的数据
4.基表的改变，也会影响到视图的数据

视图的基本使用

  1. create view 视图名 as select 语句

创建视图

  1. alter view 视图名 as select 语句

修改视图结构

  1. SHOW CREATE VIEW 视图名

查看创建视图的指令

  1. drop view 视图名1，视图名2

删除视图

image-20241210182009122

视图中可以在使用视图

image-20241210183924455

视图最佳实践

image-20241210184212872

image-20241210185722997

image-20241210190300840

用户管理

原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的mysql操作权限所以，mysql数据库管理人员(root)，根据需要创建不同的用户，赋给相应的权限，供人员使用

不同的数据库用户，登录到DBMS后，根据相应的权限，可以操作的数据库和数据对象（表，视图，触发器）都不一样

image-20241210191006059

user表的重要字段说明：

image-20241210191530442

创建、删除用户

1.在创建用户的时候，如果不指定Host,则为%，%表示表示所有IP都有连接权限
create user xxx;
2.你也可以这样指定
create user’xx’@‘192.168.1.%‘表示Xxx用户在192.168.1.*的ip可以登
录mysql
3.在删除用户的时候，如果host不是%，需要明确指定"用户’@‘host值
DROP USER jack; – 默认就是DROP USER ‘jack’@’%’

image-20241210214009084

用户修改密码

image-20241210220004429

mysql中的权限

image-20241210221305911

给用户授权

image-20241210222123811

image-20241210223746692

回收用户权限

image-20241210222622250 image-20241210224237721

权限生效指令

FLUSH PRIVILEGES;

mysql命令

备份数据库

要在Dos下执行mysqldump指令其实在mysql安装目录\bin

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > d:\\文件名.sql

备份库的表

mysqldump -u 用户名 -p 数据库 表1 表2 表n > d:\\文件名.sql

恢复数据库

进入Mysql命令行再执行

source 文件名.sql

原文地址：https://blog.csdn.net/QQ2861104332/article/details/144385860

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024