网站的跳出率,wordpress视屏,外贸网站建设560元,保定专业网站建设开发公司文章目录 1.初识MySQL1.1数据库1.2查看数据库1.3创建数据库1.4字符集编码和排序规则1.5修改数据库1.6删除数据库 2.MySQL常用数据类型和表的操作2.(一)常用数据类型1.数值类2.字符串类型3.二进制类型4.日期类型 2.(二)表的操作1查看指定库中所有表2.创建表 3.查看表结构和查看表… 文章目录 1.初识MySQL1.1数据库1.2查看数据库1.3创建数据库1.4字符集编码和排序规则1.5修改数据库1.6删除数据库 2.MySQL常用数据类型和表的操作2.(一)常用数据类型1.数值类2.字符串类型3.二进制类型4.日期类型 2.(二)表的操作1查看指定库中所有表2.创建表 3.查看表结构和查看表的创建语句4.修改表5.删除表 2.(三)总代码 3.增删改查(CRUD)操作3.1CRUD简介3.2Create(创建)2.1单行数据全列插入2.2 单行数据指定插入2.3 多⾏数据指定列插⼊ 3.3Retrieve(读取)3.1 Select查询3.1.1 全列查询3.1.2 指定列查询3.1.3 查询字段为表达式都是临时表不会对原有表数据产生影响3.1.4为查询结果指定别名3.1.5查询结果去重 3.2 where 条件查询比较运算符:逻辑运算符:3.2.1 where 基础查询3.2.2where 范围查询3.2.3 where 模糊查询3.2.4 where NULL查询3.2.5where AND,OR查询 3.3 Order by查询3.3.1ASC查询3.3.2 desc查询3.3.3 ASC与desc查询 3.4 分页查询 3.4.Update更新4.1.1 改写指定单列数据4.1.2 改写指定多列数据 3.5Delete(删除)5.1.1指定单列删除5.1.2删除整张表插入一张演示表 6.总代码:3.6截断表3.7插入查询结果3.8聚合函数3.9Group by 分组查询3.10内置函数10.1日期函数10.2字符串处理函数10.3数字函数10.4 其他常⽤函数 4.数据库约束4.1了解数据库约束4.2NOT NULL 非空约束4.3 DEFAULT 默认值约束4.5UNIQUE 唯一约束4.6PRIMARY KEY 主键约束4.7FOREIGN KEY 外键约束4.8CHECK 约束 5.SQL联合查询5.1内连接5.2外连接5.3自连接5.4子查询5.5合并查询5.6插入查询 6.MySQL视图6.1 视图的核心概念6.2 视图的用途6.3创建视图6.4修改数据6.5删除视图6. 6视图的优点 7.MySQL索引7.1了解索引7.2以B树为索引数据结构的原因2.1 Hash 索引2.2二叉搜索树 (BST) 索引2.3数据结构 N 叉树例如 B 树2.4数据结构 B 树2.5为什么 B 树是数据库索引的首选 7.3MySQL页3.1使用页的原因3. 2页的结构概览3.3页文件头和页文件尾3.4页主体3.5 页目录:3.6数据页头 7.4索引分类4.1 主键索引Primary Key Index4.2 普通索引Secondary Index / Non-Unique Index4.3 唯一索引Unique Index4.4 全文索引Full-Text Index4.5 聚集索引Clustered Index4.6 非聚集索引Non-Clustered Index4.7 索引覆盖Covering Index4.8总结4.9索引设计建议 5.使用索引5.1⾃动创建5.2手动创建5.2.1主键索引5.2.2唯一索引5.2.3 普通索引5.2.4创建复合索引 5.3 查看索引5.4 删除索引5.4.1删除主键索引5.4.2删除其它索引 6.MySQL事务6.1了解事务6.2自动/手动创建事务6.3使用事务3.1查看存储引擎3.2 语法3.3开始一个事务后进行回滚3.4设置保存点 6.4事务的隔离性和隔离级别4.1什么是隔离性4.2 隔离级别4.3查看和设置隔离级别 6.5不同隔离级别存在的问题5.1 READ UNCOMMITTED - 读未提交与脏读5.2 READ COMMITTED - 读已提交与不可重复读5.3REPEATABLE READ - 可重复读与幻读 6.6不同隔离级别的性能与安全 1.初识MySQL
1.1数据库
MySQL是一款使用率高且免费的数据库使用率仅仅低于Oracle 关系数据库和 NoSQL 数据库管理系统知识库(DB-Engines Ranking -) (此图数据于2025-1-25)
数据库主要是数据处理数据处理包含数据的储存、查询、统计、排序、删除等。
数据库的优势:
数据持久化数据库可以将数据保存在存储介质中即使应⽤程序关闭或服务器重启数据也不会 丢失。数据结构化数据库能够以结构化的⽅式存储数据使得数据易于管理和查询。数据完整性数据库管理系统DBMS提供了数据完整性的保障确保数据的准确性和⼀致性。并发控制数据库可以处理多个⽤⼾或进程同时访问和修改数据同时确保数据的⼀致性。安全性数据库提供了多种安全机制如访问控制、加密等保护数据不被未授权访问。可扩展性随着数据量的增⻓数据库可以⽔平或垂直扩展以适应不断增⻓的数据需求。备份和恢复数据库⽀持数据的备份和恢复以防数据丢失或损坏。查询优化数据库系统提供了⾼效的查询优化器可以快速执⾏复杂的查询操作。事务管理⼤部分关系型数据库都⽀持事务确保⼀系列操作要么完全成功要么完全失败提⾼ 了操作的可靠性。多⽤⼾⽀持数据库允许多个⽤⼾同时访问和操作数据适合多⽤⼾环境。
接下来使用的是MySQL 8.0 Command Line Client - Unicode来进行操作和演示大小写不敏感
1.2查看数据库
语法show databases; MySQL自带的6个基础数据库 sakia和world是示例库用于练习可以修改。 information_schema mysql performance_schema sys是系统级别的数据库不要随便修改和删除
1.3创建数据库
语法: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] create_option: [DEFAULT] { CHARACTER SET [] charset_name | COLLATE [] collation_name | ENCRYPTION [] {‘Y’ | ‘N’} } 1.大写部分代表关键字 2.{}表示必须选 3.|表示二选一 4.[]表示可以选 5.CHARACTER SET指定数据库采⽤的字符集编码 6.COLLATE指定数据库字符集的校验规则 7. ENCRYPTION数据库是否加密MySQL 8.0.16中引⼊的新选项 创建成功一个名为java的数据库 当使用[]中的if no exists 去判断 如果没有名java的数据库则进行创建存在就会报错
原则上当命名数据库时不能用关键字但加上时就可以
1.4字符集编码和排序规则
查看字符集编码: show charset; MySQL8.0默认的字符集编码是 utf8mb4,MySQL5.7默认的字符集latin1 Maxlen代表:例如utf8mb4为字符编码时最多以4个字节构成字符 查看排序规则: show collation;
MySQL8.0默认是ut8mb4_0900_ai_ciMySQL5.7默认是ut8mb4_general_ci。 ai是Accent-insensitive的缩写表⽰⼝声不敏感 • ci是Case-insensitive的缩写表⽰⼤⼩写不敏感 • as是Accent-sensitive的缩写表⽰⼝声敏感 • cs是Case-sensitive的缩写表示大写敏感 •bin表示二进制
1.5修改数据库
主要是修改字符集和排序规则 语法:ALTER {DATABASE | SCHEMA} [db_name] alter_option … alter_option: { [DEFAULT] CHARACTER SET [] charset_name | [DEFAULT] COLLATE [] collation_name | [DEFAULT] ENCRYPTION [] {‘Y’ | ‘N’} | READ ONLY [] {DEFAULT | 0 | 1} }
1.6删除数据库
语法:DROP {DATABASE | SCHEMA} [IF EXISTS] db_name 注意不要随意删除数据库噢
2.MySQL常用数据类型和表的操作
2.(一)常用数据类型
1.数值类
BIT([M]) 大小:bit M表示每个数的位数取值范围为1~64,若M忽略则默认为1。
TINYINT([M]) 大小byte 取值范围为-2^7 ~2^7-1 无符号取值范围为0~2^8-1。
INT([M]) 大小:4byte 取值范围为-2^31 ~2^31-1 无符号取值范围为:2^32-1
BIGINT([M]) 大小:8byte 取值范围为-2^63 ~2^63-1 无符号取值范围为0~2^64-1
DECIMAL[(M,D)] 大小:动态 M为总位数D为小数点后面的位数DECIMAL最大位数M为65D最大为30, 如果省略M则默认为10,如果省略D则默认为0,
DECIMAL大小为动态可以由其他数值类组成例如一个数123456789123456789123456789.12345678963就可以由4个INT与1个BIT组成(INT可以储存10位数这里储存38位数就可以由4个INT与1个BIT组成减少空间浪费)
2.字符串类型
CHAR[(M)] 固定长度字符串M表示长度以字符为单位取值范围为0~255若M省略则默认为1
VARCHAR[(M)] 可变长度字符串M表示最大长度范围为0~65535(2^16-1)个字节,有效字符个数取决于实际字符个数与使用的其字符集例如使用utf8mb4字符集最多可以保存65535/416383个字符
TEXT[(M)] 文本类型,最大长度为2^16-1个字节有效字符个数为使用的字符集
3.二进制类型
BINARY[(M)] 固定长度二进制字节与CHAR类似 储存二进制字节取值范围为0~255若M省略则默认为1
VARBINARY[(M)] 可变长度二进制字节与VARCHAR类似 M表示长度
BLOB[(M)] 二进制字节类型(存储二进制文件的字段类型), 最大长度为0~2^16-1
4.日期类型
(Byte 是计算机存储和处理信息的基本单位而 Bytes 通常用来表示多个字节。 Byte字节一个字节由8个二进制位bit组成可以存储一个英文字符或半个汉字。在数据存储和传输中字节是最常用的单位
Bytes这个词通常用来表示多个字节比如在描述数据大小时我们可能会说 “这个文件有128 Bytes”意思是文件大小为128字节)
DATETIME[(fsp)] 8bytes 日期类型与时间类型结合⽀持范围 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.499999 • 显⽰格式为 YYYY-MM-DD hh:mm:ss[.fraction] • 0值为 0000-00-00 00:00:00
DATE[(fsp)] 3bytes ⽇期类型 • ⽀持范围 1000-01-01 ~ 9999-12-31 • 显⽰格式为 YYYY-MM-DD • 0值为 0000-00-00
2.(二)表的操作
1查看指定库中所有表
语法: show tables;
2.创建表
语法:CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name field datatype [约束] [comment ‘注解内容’] [, field datatype [约束] [comment ‘注解内容’]] … ) [engine 存储引擎] [character set 字符集] [collate 排序规则];
TEMPORARY 表⽰创建的是⼀个临时表 • field列名 • datatype数据类型 • comment对列的描述或说明 • engine存储引擎不指定则使⽤默认存储引擎 • character set字符集不指定则使⽤默认字符集 • collate排序规则不指定则使⽤默认排序规则 详细的创建表语法参考官⽅⽹站: MySQL :: MySQL 8.0 Reference Manual :: 15.1.20 CREATE TABLE Statement 展示表在磁盘上的扩展文件 创建⼀个存储引擎为 InnoDB 的表时会在对应的数据库⽬录下⽣成⼀个⽤来存储真实数据的物 理⽂件命名格式为 表名.ibd 以当前为例会在 java ⽬录下⽣成⼀个 t_student.ibd 的数据 ⽂件 • 创建⼀个存储引擎为 MyISAM 的表时会在对应的数据库⽬录下分别⽣成三个以不同后缀名结尾 的⽂件分别是 表名.MYD ( MYData )的数据⽂件 表名.MYI ( MYIndex ) 的索引⽂件以表名.sdi 的表信息描述⽂件(JSON格式) 在8.0以前的版本中表信息描述⽂件是以.frm为后缀的⼆进制⽂件
3.查看表结构和查看表的创建语句
查看表结构语法:desc 表名; 在这里插入图片描述 查看表的创建语句语法show create table 表名;
4.修改表
语法:5.1 语法 ALTER TABLE tbl_name [alter_option [, alter_option] …]; alter_option: { table_options | ADD [COLUMN] col_name column_definition [FIRST | AFTER注释FIRST指表中的第一列AFTER指定列的后一列 col_name] | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | RENAME COLUMN old_col_name TO new_col_name | RENAME [TO | AS] new_tbl_name
• tbl_name要修改的表名 • ADD向表中添加列 • MODIFY修改表中现有的列 • DROP删除表中现有的列 • RENAME COLUMN重命名表中现有的列 • RENAME [TO | AS] new_tbl_name重命名当前的表
详细的创建表语法参考官⽅⽹站MySQL :: MySQL 8.0 Reference Manual :: 15.1.9 ALTER TABLE Statement
ADD示例演示:
MODIFY实例演示 DROP实例演示: – 删除最后表中age列 alter table t_student drop age; RENAME COLUMN实例演示: RENAME实例演示:
5.删除表
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [,tbl_name]
TEMPORARY表⽰临时表 tbl_name将要删除的表名 • 删除表是⼀个危险操作执⾏删除语句时⼀定要谨慎 • 删除表成功后磁盘上对应的数据⽂件也会被删除 • ⼀次可以删除多个表表与表之间⽤逗号隔开
2.(三)总代码
create table if not exists t_student (id bigint comment 编号,name varchar(15) comment 姓名,age int comment 年龄)character set utf8mb4 collate utf8mb4_0900_ai_ci;#单行注释-- 单行注释--后需要加空格/**/多行注释#添加表中第一列来演示alter table t_student add first int comment添加第一行 FIRST;#往id后一列添加alter table t_student add ids bigint comment添加到id后一行 after id;#修改name这行来演示alter table t_student modify name varchar(25) comment 姓名 after ids; -- 删除最后表中age列 alter table t_student drop age;#将表中第一列的first重命名为onealter table t_student rename column first to one;#修改表名alter table t_student tbl_name t_newstudent;create table if not exists t_engine(id bigint comment 编号,engine varchar(20) comment 引擎)engine MyISAM;#删除t_engine这个表drop table t_engine;3.增删改查(CRUD)操作
3.1CRUD简介
CRUD是对数据库中的记录进⾏基本的增删改查操作 • Create (创建) • Retrieve (读取) • Update (更新) • Delete (删除)
3.2Create(创建)
语法: INSERT [INTO] table_name [(column [, column] …)] VALUES (value_list) [, (value_list)] … value_list: value, [, value] … 创建一个用于演示的表
2.1单行数据全列插入
value_list 中值的数量必须和定义表的列的数量及顺序⼀致
2.2 单行数据指定插入
value_list 中值的数量必须和指定列数量及顺序⼀致
2.3 多⾏数据指定列插⼊
在⼀条insert语句中也可以指定多个value_list实现⼀次插⼊多⾏数据 在单行数据插入时推荐使用指定插入当表数据多时可以更清楚的知道插入的内容. 指定位置去插入表属性的顺序也可以调换 注意插入时表属性不能缺少或者不赋值
Create(创建)代码: #2.1单行数据全列插入
-- 插入第一行数据
insert into user values (1,刘1);
-- 插入第二行
insert into user values (2,刘2);#2.2单行数据指定插入
insert into user(id,name) values (3,张三);insert into user(name,id) values (赵六,6);#2.3 多行数据指定列插入
insert into user(id,name) values (4,李四),(5,王五);
3.3Retrieve(读取)
语法:
SELECT[DISTINCT]select_expr [, select_expr] ...[FROM table_references][WHERE where_condition][GROUP BY {col_name | expr}, ...][HAVING where_condition][ORDER BY {col_name | expr } [ASC | DESC], ... ][LIMIT {[offset,] row_count | row_count OFFSET offset}]创建一个用于演示的表: 3.1 Select查询
3.1.1 全列查询
查询全部数据
3.1.2 指定列查询
查询所有人的编号、姓名和数学成绩 查询的顺序没有要求:
3.1.3 查询字段为表达式都是临时表不会对原有表数据产生影响
常量表达式: 也可以是常量的运算 表达式中包含⼀个字段列于常量运算 表达式中多个字段列于列运算:
3.1.4为查询结果指定别名
语法:
1 SELECT column [AS] alias_name [, ...] FROM table_name;AS可以省略别名如果包含空格必须⽤单引号包裹
将每个人的总分展示出来
3.1.5查询结果去重
查看两条math为98的数据进行去重 去重的条件是所要求值全部相同 以下math相同但id分别为1,3 注意 • 查询时不加限制条件会返回表中所有结果如果表中的数据量过⼤会把服务器的资源消耗殆尽 • 在⽣产环境不要使用不加限制条件的查询
Retrieve(Select )代码:CREATE TABLE if not exists exam(id BIGINT,name VARCHAR(20) COMMENT 同学姓名,chinese float COMMENT 语文成绩,math float COMMENT 数学成绩,english float COMMENT 英语成绩
);
-- 插入测试数据
INSERT INTO exam (id,name, chinese, math, english) VALUES
(1, 唐三藏, 67, 98, 56),
(2, 孙悟空, 87, 78, 77),
(3, 猪悟能, 88, 98, 90),
(4, 曹孟德, 82, 84, 67),
(5, 刘孟德, 55, 85, 45),
(6, 孙权, 70, 73, 78),
(7, 宋公明, 75, 65, 30);
#3.1.1 全列查询
#使用*可以查询表中所有列的值
select * from exam;
#3.1.2 指定列查询
#• 查询所有人的编号、姓名和数学成绩
select id,name,math from exam;
#查询的顺序没有要求:
select name,english,id from exam;# 查询字段为表达式
#常量表达式:
select id,name,1 from exam;
#也可以是常量的运算
select id,name,11 from exam;
# 表达式中包含一个字段
select id,name,math10 from exam;
#表达式中多个字段:
select id,name,chinesemathenglish from exam;
# 3.1.3为查询结果指定别名
#将每个人的总分展示出来
select id,name,chinesemathenglish as 总分 from exam;
# 3.1.4查询结果去重
#查看两条math为98的数据进行去重
select math from exam;3.2 where 条件查询
语法 SELECT
select_expr [, select_expr] ... [FROM table_references]WHERE where_condition比较运算符: , , , 小于,大于大于等于小于等于 MySQL中同时代表赋值和判断 对于NULL不安全,NULLNULL还是NULL 代表等于 对于NULL相对安全 NULLNULL 结果为TRUE(1)
! , 代表不等于
IS NULL 是NULL
IS NOT NULL 不是NULL value BETWEEN a0 AND a1 范围匹配[a0, a1]如果a0 value a1返回TRUE或1NOT BETWEEN则取反
value IN (option, …) 如果value 在optoin列表中则返回TRUE(1)NOT IN则取反
LIKE 模糊匹配% 表⽰任意多个(包括0个)字符_ 表⽰任意⼀个字符NOT LIKE则取反
逻辑运算符:
AND 多个条件必须都为 TRUE(1)结果才是 TRUE(1)
OR 任意⼀个条件为 TRUE(1), 结果为 TRUE(1)
NOT 条件为 TRUE(1)结果为 FALSE(0)
3.2.1 where 基础查询
1查询语文70的
2查询数学高于语文的
3查询总分低于250的 这里我们需要了解select与from与where之间的优先级
首先执行的是from找到这个表然后执行符合where条件的, 最后执行select返回在符合条件的要显示的列
所以是错误的当whiere执行时 total还没有被定义select执行完后chinesemathenglish as total 执行 total才定义完成
3.2.2where 范围查询
查询英语成绩在60~80之间的 查询数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩
3.2.3 where 模糊查询
%表示任意多个(包括0个)字符;
%表示所有,等于没有指定条件 %xxx,表示以xxx结束,前面可以包含任意多个字符 xxx%,表示以xxx开头,后面可以包含任意多个字符 %xxx%,前面和后面可以包含任意多个字符,中间必须有xxx
表示任意一个字符,
严格匹配 写多少个_就表示多少个字符
是一个占位符 表示只有一个字符 _ xxx,表示以xxx结束,前面可以包含一个字符 xxx _,表示以xxx开头,后面可以包含一个字符 _XXX _,前面和后面可以包含一个字符,中间必须是xxx
%系列 _系列:
3.2.4 where NULL查询 对NULLL与其他值进行运算结果为NULL
3.2.5where AND,OR查询 观察AND与OR的优先级
AND的优先级高于OR
Retrieve(where )代码
#3.2.1基础查询
#查询语文70的
#查询数学高于语文的
#查询总分低于250的
select id,name,chinese from exam where chinese70;
select id,name,chinese,math from exam where mathchinese;
select id,name, chinesemathenglish as total from exam where (chinesemathenglish)250;
#3.2.2范围 查询英语成绩在60~80之间的 查询数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩
select id,name,english from exam where english between 60 and 80;
select id,name,math from exam where math in(78,79,98,99);
#3.2.3 where 模糊查询
select id,name from exam where name like %孟%;
select id,name from exam where name like 孙%;
select id,name from exam where name like %德;
#_系列
select id,name from exam where name like 孙_;
select id,name from exam where name like 孙__;
select id,name from exam where name like _孟_;
#3.2.3 where NULL查询
#插入一条null
insert into exam values (8,张飞,88,98,NULL);
select *from exam where english is null;
select *from exam where english is not null;
#对NULLL与其他值进行运算结果为NULL
select id,name,chinesemathenglish as total from exam;3.3 Order by查询
语法:
-- ASC 为升序从⼩到⼤
-- DESC 为降序从⼤到⼩
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...] ORDER BY {col_name | expr } [ASC | DESC], ... ;3.3.1ASC查询
对语文进行ASC
3.3.2 desc查询
对数学进行desc
3.3.3 ASC与desc查询
改一下数据观察同时对语文成绩进行asc数学进行desc 来观察是否可以使⽤列的别名进⾏排序 注意在排序时NULL比任何值都小, 改一负数进行观察
Retrieve(Order by)代码
#Order by查询
#对语文进行ASC
select id,name,chinese from exam order by chinese asc;
#对数学进行desc
select id,name,math from exam order by math desc;
#改一下数据观察同时对语文成绩进行asc数学进行desc
select id,name,chinese,math from exam order by chinese asc, math desc;
#来观察是否可以使⽤列的别名进⾏排序
select id,name,chinesemathenglish as total from exam order by chinesemathenglish desc;
select id,name,chinesemathenglish as total from exam order by total desc;
#注意在排序时NULL比任何值都小, 改一负数进行观察
select id,name,chinesemathenglish as total from exam order by total desc;3.4 分页查询
语法
-- 起始下标为 0
-- 从 0 开始筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY...] LIMIT num;
-- 从 start 开始筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY...] LIMIT start, num;
-- 从 start 开始筛选 num 条结果⽐第⼆种⽤法更明确建议使⽤
SELECT ... FROM table_name [WHERE ...] [ORDER BY...] LIMIT num OFFSET start;分页查询主要掌握查询页数与每页查询多少列之间的关系 插入一列数据 insert into exam(id,name,chinese,math,english) values (9,‘李白’,94,91,77); 接下来将数据增加到9条分5页第一条为0下标 num2; start页数-1*num 进行分页查询; Retrieve(分页查询)代码
#3.4 分页查询
#插入一列
insert into exam(id,name,chinese,math,english) values (9,李白,94,91,77);select * from exam order by id desc limit 0,2;select * from exam order by id desc limit 2,2;select * from exam order by id desc limit 4,2;select * from exam order by id desc limit 6,2;select * from exam order by id desc limit 8,2;3.4.Update更新
语法:
UPDATE [LOW_PRIORITY] [IGNORE] table_referenceSET assignment [, assignment] ...[WHERE where_condition][ORDER BY ...][LIMIT row_count]4.1.1 改写指定单列数据
将孙悟空的语文数学成绩都是加10
4.1.2 改写指定多列数据
将所有英语成绩*2
注意• 不加where条件时会导致全表数据被列新谨慎操作 Update(更新代码
# 4.Update更新# 4.1.1 改写指定单行数据select name,chinese,math from exam where name 孙悟空;update exam set chinesechinese10,mathmath10 where name 孙悟空;select name,chinese,math from exam where name 孙悟空;# 4.1.2 改写指定多行数据,将所有英语成绩*2id,name,english from exam order by english asc ;update exam set englishenglish*2;id,name,english from exam order by english asc ;3.5Delete(删除)
语法: DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
5.1.1指定单列删除
删除名为张飞的数据
5.1.2删除整张表插入一张演示表 注意Delete操作非常危险执⾏Delete时不加条件会删除整张表的数据谨慎操作 Delete(删除)代码
#5.Delete# 5.1.1指定单列删除,删除名为张飞的数据
delete from exam where name张飞;
select name from exam where name张飞;
#5.1.2删除整张表插入一张演示表
create table if not exists t_student(
id bigint not null comment编号,
name varchar(20) not null comment用户名
);
insert into t_student values (1,小明),(2,小龙),(3,小兰);6.总代码:
#2Create(创建)
#创建一个用于演示的表
create table if not exists user(
id bigint not null comment编号,
name varchar(20) not null comment用户名
);
#2.1单行数据全列插入
-- 插入第一行数据
insert into user values (1,刘1);
-- 插入第二行
insert into user values (2,刘2);#2.2单行数据指定插入
insert into user(id,name) values (3,张三);
insert into user(name,id) values (赵六,6);#2.3 多行数据指定列插入
insert into user(id,name) values (4,李四),(5,王五);insert into user(name) values (宋七);
#3.Retrieve(读取)
CREATE TABLE if not exists exam(id BIGINT,name VARCHAR(20) COMMENT 同学姓名,chinese float COMMENT 语文成绩,math float COMMENT 数学成绩,english float COMMENT 英语成绩
);
-- 插入测试数据
INSERT INTO exam (id,name, chinese, math, english) VALUES
(1, 唐三藏, 67, 98, 56),
(2, 孙悟空, 87, 78, 77),
(3, 猪悟能, 88, 98, 90),
(4, 曹孟德, 82, 84, 67),
(5, 刘孟德, 55, 85, 45),
(6, 孙权, 70, 73, 78),
(7, 宋公明, 75, 65, 30);
#3.1.1 全列查询
#使用*可以查询表中所有列的值
select * from exam;
#3.1.2 指定列查询
#• 查询所有人的编号、姓名和数学成绩
select id,name,math from exam;
#查询的顺序没有要求:
select name,english,id from exam;
# 查询字段为表达式
#常量表达式:
select id,name,1 from exam;
#也可以是常量的运算
select id,name,11 from exam;
# 表达式中包含一个字段
select id,name,math10 from exam;
#表达式中多个字段:
select id,name,chinesemathenglish from exam;
# 3.1.3为查询结果指定别名
#将每个人的总分展示出来
select id,name,chinesemathenglish as 总分 from exam;
# 3.1.4查询结果去重
#查看两条math为98的数据进行去重
select math from exam;#3.2.1基础查询
#查询语文70的
#查询数学高于语文的
#查询总分低于250的
select id,name,chinese from exam where chinese70;
select id,name,chinese,math from exam where mathchinese;
select id,name, chinesemathenglish as total from exam where (chinesemathenglish)250;
#3.2.2范围 查询英语成绩在60~80之间的 查询数学成绩是 78 或者 79 或者 98 或者 99 分的同学及数学成绩
select id,name,english from exam where english between 60 and 80;
select id,name,math from exam where math in(78,79,98,99);
#3.2.3 where 模糊查询
select id,name from exam where name like %孟%;
select id,name from exam where name like 孙%;
select id,name from exam where name like %德;
#_系列
select id,name from exam where name like 孙_;
select id,name from exam where name like 孙__;
select id,name from exam where name like _孟_;
#3.2.3 where NULL查询
#插入一条null
insert into exam values (8,张飞,88,98,NULL);
select *from exam where english is null;
select *from exam where english is not null;
#对NULLL与其他值进行运算结果为NULL
select id,name,chinesemathenglish as total from exam;#Order by查询
#对语文进行ASC
select id,name,chinese from exam order by chinese asc;
#对数学进行desc
select id,name,math from exam order by math desc;
#改一下数据观察同时对语文成绩进行asc数学进行desc
select id,name,chinese,math from exam order by chinese asc, math desc;
#来观察是否可以使⽤列的别名进⾏排序
select id,name,chinesemathenglish as total from exam order by chinesemathenglish desc;
select id,name,chinesemathenglish as total from exam order by total desc;
#注意在排序时NULL比任何值都小, 改一负数进行观察
select id,name,chinesemathenglish as total from exam order by total desc;#3.4 分页查询
#插入一列
insert into exam(id,name,chinese,math,english) values (9,李白,94,91,77);select * from exam order by id desc limit 0,2;select * from exam order by id desc limit 2,2;select * from exam order by id desc limit 4,2;select * from exam order by id desc limit 6,2;select * from exam order by id desc limit 8,2# 4.Update更新# 4.1.1 改写指定单行数据select name,chinese,math from exam where name 孙悟空;update exam set chinesechinese10,mathmath10 where name 孙悟空;select name,chinese,math from exam where name 孙悟空;# 4.1.2 改写指定多行数据,将所有英语成绩*2id,name,english from exam order by english asc ;update exam set englishenglish*2;id,name,english from exam order by english asc ;#5.Delete# 5.1.1指定单列删除,删除名为张飞的数据
delete from exam where name张飞;
select name from exam where name张飞;
#5.1.2删除整张表插入一张演示表
create table if not exists t_student(
id bigint not null comment编号,
name varchar(20) not null comment用户名
);
insert into t_student values (1,小明),(2,小龙),(3,小兰);3.6截断表
语法:TRUNCATE [TABLE] tbl_name
准备一个测试的新表来演示 直接在删除后再插入可以观察到id序号不会重置 所以当此时再插入一列序号为1 Truncate: •只能对整表操作不能像 DELETE ⼀样针对部分数据 • 不对数据操作所以⽐DELETE更快TRUNCATE在删除数据的时候不经过真正的事物所以⽆法回滚 • 会重置 AUTO_INCREMENT 项
使用到的代码: #1.截断表
create table t_data(
id int primary key auto_increment comment 序号,
name varchar(20)
);
insert into t_data(name) values(a),(b),(c),(d);
select * from t_data;
insert into t_data(name) values(d);
show create table t_data;
truncate table t_data;3.7插入查询结果
语法:INSERT INTO table_name [(column [, column ...])] SELECT ... 实现思路原始表中的数据⼀般不会主动删除但是真正查询时不需要重复的数据如果每次查询都使⽤DISTINCT进⾏去重操作会严重效率。可以创建⼀张与 t_use表结构相同的表把去重的记录写⼊到新表中以后查询都从新表中查这样真实的数据不丢失同时⼜能保证查询效率 相关代码:
create table if not exists t_use(
id bigint,
name varchar(20)
);
insert into t_use values(1,aaa),(1,aaa),(2,bbb),(2,bbb),(3,ccc),(3,ccc);
create table t_use_new like t_use;
insert into t_use_new select distinct * from t_use;
select * from t_use_new;3.8聚合函数
COUNT([DISTINCT] expr) 返回查询到的数据的数量 SUM([DISTINCT] expr) 返回查询到的数据的总和不是数字没有意义 AVG([DISTINCT] expr) 返回查询到的数据的平均值不是数字没有意义 MAX([DISTINCT] expr) 返回查询到的数据的最⼤值不是数字没有意义 MIN([DISTINCT] expr) 返回查询到的数据的最⼩值不是数字没有意义
这里使用的是前面一篇博文的表exam count: sum: avg:
max,min: 相关代码:
select count(*) from exam;
select count(1) from exam;
select count(id) from exam;
select count(english) from exam;
select sum(chinese) from exam;
select sum(english) from exam;
select sum(name) from exam;
select avg(english) from exam;
select avg(chinesemathenglish) 总平均分 from exam; select Round(avg(chinesemathenglish),1) 总平均分 from exam;
select max(chinesemathenglish) from exam;
select min(chinesemathenglish) 总分 from exam;
select max(chinesemathenglish) 总分最多 ,min(chinesemathenglish) 总分最少 from exam;3.9Group by 分组查询
分组查询的基本语法如下
SELECT 列, 聚合函数(列) FROM 表 WHERE 筛选条件 GROUP BY 分组的列 ORDER BY 子句;
在使用GROUP BY时需要注意以下几点除了GROUP BY后面的字段如果要在SELECT后查询其他字段必须用聚合函数进行聚合。 分组查询中的筛选条件分为两类分组前筛选和分组后选分组前筛选使用WHERE子句数据源是原始表分组后筛选使用HAVING子句数据源是分组后的结果集。
创建一个新表来观察 having 子句: 使用Grounp by 对其处理后不能使用where进行过滤要使用having 子句 Having 与Where 的区别 • Having ⽤于对分组结果的条件过滤 • Where ⽤于对表中真实数据的条件过滤
相关代码:
drop table if exists emp;
create table emp (id bigint primary key auto_increment,name varchar(20) not null,role varchar(20) not null,salary decimal(10, 2) not null
);
insert into emp values (1, 小胖, 老板, 1500000.00);
insert into emp values (2, 小腾, 老板, 1800000.00);
insert into emp values (3, 小鑫, 主管, 13000.00);
insert into emp values (4, 小亮, 主管, 12000.00);
insert into emp values (5, 小平, 员工, 9000.00);
insert into emp values (6, 小莹, 员工, 8000.00);
insert into emp values (7, 孙悟空, 游戏人物, 956.8);
insert into emp values (8, 猪悟能, 游戏人物, 700.5);
insert into emp values (9, 沙和尚, 游戏人物, 333.3);
select * from emp;
select role,avg(salary) from emp group by role;
select role,avg(salary) from emp group by role;
select role,avg(salary) from emp group by role order by avg(salary) asc;
select role,ROUND(avg(salary),2) 平均工资 from emp group by role having avg(salary)10000;3.10内置函数
10.1日期函数
CURDATE() 返回当前日期 同义词:CURRENT_DATE,CURRENT_DATE() CURTIME() 返回当前时间 同义词 CURRENT_TIME CURRENT_TIME([fsp]) NOW() 返回当前日期和时间 :CURRENT_TIMESTAMP DATE(date) 提取date或datetime表达式的⽇期部分
ADDDATE(date,INTERVAL expr unit) 向⽇期值添加时间值(间隔)同义词 DATE_ADD() SUBDATE(date,INTERVAL expr unit) 向⽇期值减去时间值(间隔)同义词 DATE_SUB() DATEDIFF(expr1,expr2) 两个⽇期的差以天为单位expr1 - expr2 参考链接14.7 Date and Time Functions 11.5 Expressions 相关代码
#5.1日期函数
select CURDATE();
select CURRENT_DATE;
select CURRENT_DATE();
select CURTIME();
select CURRENT_TIME;
select CURRENT_TIME();
select NOW();
select CURRENT_TIMESTAMP;
select DATE(2000-2-17 10:15:23);
select DATE(2000-222-17 10:15:23);
select ADDDATE(2025_2_7 10:10:10,interval 10 minute);
select ADDDATE(2025_2_7 10:10:10,interval 10 day);
select ADDDATE(2025_2_7 10:10:10,interval 10 month);
select SUBDATE(2025_2_7 10:10:10,interval 10 minute);
select DATEDIFF(2025_2_7 10:10:10,2026_2_7 10:10:10);
select DATEDIFF(2027_2_7 10:10:10,2024_2_7 10:10:10);10.2字符串处理函数
CHAR_LENGTH(str) 返回给定字符串的⻓度同义词 CHARACTER_LENGTH() LENGTH(str) 返回给定字符串的字节数与当前使⽤的字符编码集有关 CONCAT(str1,str2,…) 返回拼接后的字符串 CONCAT_WS(separator,str1,s tr2,…)返回拼接后带分隔符的字符串 LCASE(str) 将给定字符串转换成⼩写同义词 LOWER() UCASE(str) 将给定字符串转换成⼤写同义词 UPPER() HEX(str), HEX(N) 对于字符串参数str, HEX()返回str的⼗六进制字符串表⽰形式对于数字参数N, HEX()返回⼀个⼗六进制字符串表⽰形式 INSTR(str,substr) 返回substr第⼀次出现的索引
INSERT(str,pos,len,newstr)在指定位置插⼊⼦字符串最多不超过指定的字符数 SUBSTR(str,pos) SUBSTR(str FROM pos FOR len) 返回指定的⼦字符串同义词 SUBSTRING(str,pos) SUBSTRING(str FROM pos FOR len) PLACE(str,from_str,to_str)把字符串str中所有的from_str替换为to_str区分⼤⼩写 STRCMP(expr1,expr2) 逐个字符⽐较两个字符串返回 -1 0 1 LEFT(str,len) RIGHT(str,len) 返回字符串str中最左/最右边的len个字符 LTRIM(str) RTRIM(str) TRIM(str) 删除给定字符串的前导、末尾、前导和末尾的空格
TRIM([{LEADING | TRAILING | BOTH } [remstr] FROM] 删除给定符串的前导、末尾或前导和末尾的指定字符串 参考14.8 String Functions and Operators 相关代码 #5.2字符串处理函数
select concat(name,的语文成绩:,chinese, 数学成绩:,math, 英语成 绩:,english) 成绩汇总 from exam;
select concat_ws(,,chinese,math,english) as语文数学英语from exam;
select lcase(ABC);
select ucase(abc);
select HEX(abc);
select HEX(100);
select instr(abcdef,b);
select insert(Hello, Database,8,0, MySQL);
select insert(Hello, Database,8,20, MySQL);
select insert(Hello, Database,8,2, MySQL);
select SUBSTR(Hello, Database,8);
select SUBSTR(Hello, Database from 8 for 1);select SUBSTR(Hello, Database from 8 for 100);
select REPLACE(Hello Database, ,Database,,MySQL );select REPLACE(Hello Database ,Database,MySQL );
select strcmp(abc,ac);
select strcmp(abc,abc);
select strcmp(abc,ab);
select left(abcdef,3);
select right(abcdef,3);
select trim(xxx from xxxabcxxx);
select trim(leadingxxx from xxxabcxxx);
select trim(trailingxxx from xxxabcxxx);10.3数字函数
ABS(X) 返回X的绝对值 CEIL(X) 返回不⼩于X的最⼩整数值同义词是 CEILING(X) FLOOR(X) 返回不⼤于X的最⼤整数值 CONV(N,from_base,to_base) 不同进制之间的转换 RAND([N]) 返回⼀个随机浮点值取值范围 [0.0, 1.0) FORMAT(X,D) 将数字X格式化为“#######”的格式。##四舍五⼊到⼩数点后D位并以字符串形式返回 ROUND(X), ROUND(X,D) 将参数X舍⼊到⼩数点后D位 CRC32(expr) 计算指定字符串的循环冗余校验值并返回⼀个32位⽆符号整数 参考14.6 Numeric Functions and Operators 相关代码:
#5.3数字函数
select abs(-22);
select ceil(11.11);
select floor(11.11);
select conv(15,10,16);
select conv(15,10,8);
select rand();
select format(123456789,3);
select round(rand(),3);
select crc32(abc);
select version();10.4 其他常⽤函数
version() 显⽰当前数据库版本 database() 显⽰当前正在使⽤的数据库 user() 显⽰当前⽤户
md5(str) 对⼀个字符串进⾏md5摘要摘要后得到⼀个32位字符串 ifnull(val1, val2) 如果val1为NULL返回val2否则返回val1
select version();
select database();
select user();
select md5(abc);
select ifnull(1,2);
select ifnull(null,2);4.数据库约束
4.1了解数据库约束
数据库约束是指为保证数据库中数据的完整性和一致性数据库管理系统DBMS在创建表时所定义的一系列规则。这些规则作用于表中列上用于限制加入表的数据的类型、格式、范围以及与其他数据值的关系。
数据库约束的核心作用是确保数据的准确性、一致性和有效性。通过设定这些规则数据库系统可以自动防止错误数据的输入提升数据管理的质量和效率。
4.2NOT NULL 非空约束
添加NOT NULL的列不可以储存NULL值
4.3 DEFAULT 默认值约束
DEFAULT 约束⽤于向列中插⼊默认值如果没有为列设置值那么会将默认值设置到该列 4.5UNIQUE 唯一约束
添加unique的列不可以储存重复数据 4.6PRIMARY KEY 主键约束
创建一个新student表来观察 id重复出现主键冲突通常把主键列设置为⾃动增⻓让数据库维护主键值 自行插入id可能会改变auto_increment 下⼀次⾃增从主键的最⼤值开始 删除表再重新创建 一个表中不能有多个主键, 可以设置多个列为主键 4.7FOREIGN KEY 外键约束
外键⽤于定义主表和从表之间的关系 外键约束主定义在从表的列上主表关联的列必须是主键或唯⼀约束
创建一个主键的班级表 创建student副表 删除主表的限制
4.8CHECK 约束
相当于给列直接定义限制
5.SQL联合查询
#班级表
drop table if exists class;
create table class(
id bigint primary key auto_increment,
name varchar(20)
);
#学生,课程与成绩的多对多关系
drop table if exists student;
create table student(
id bigint primary key auto_increment,
name varchar(20)not null,
sno varchar(10) not null,
age int default 18,
gender tinyint(1),
enroll_date date,
class_id bigint,
foreign key(class_id) references class(id)
);
#课程表
drop table if exists course;
create table course(
id bigint primary key auto_increment,
name varchar(20)
);
#成绩表
drop table if exists score;
create table score(
id bigint primary key auto_increment,
score float,
student_id bigint,
course_id bigint,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);# 课程表
insert into course (name) values (Java), (C), (MySQL), (操作系统), (计算机网络), (数据结构);
# 班级表
insert into class(name) values (Java001班), (C001班), (前端001班);
# 学生表
insert into student (name, sno, age, gender, enroll_date, class_id) values
(唐三藏, 100001, 18, 1, 1986-09-01, 1),
(孙悟空, 100002, 18, 1, 1986-09-01, 1),
(猪悟能, 100003, 18, 1, 1986-09-01, 1),
(沙悟净, 100004, 18, 1, 1986-09-01, 1),
(宋江, 200001, 18, 1, 2000-09-01, 2),
(武松, 200002, 18, 1, 2000-09-01, 2),
(李逹, 200003, 18, 1, 2000-09-01, 2),
(不想毕业, 200004, 18, 1, 2000-09-01, 2);# 成绩表
insert into score (score, student_id, course_id) values
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
(60, 2, 1),(59.5, 2, 5),
(33, 3, 1),(68, 3, 3),(99, 3, 5),
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
(81, 5, 1),(37, 5, 5),
(56, 6, 2),(43, 6, 4),(79, 6, 6),
(80, 7, 2),(92, 7, 6); 5.1内连接
语法: 1 select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件; 2 select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;
查询所有学生的考试成绩:
select s.name,c.name 课程,sc.score 分数 from score sc,student s,course cwhere c.idsc.course_idand s.idsc.student_id;select s.name,c.name as course,sc.score
from student s
join score sc on s.idsc.student_id
join course c on sc.course_idc.id;5.2外连接
外连接分为左外连接、右外连接和全外连接三种类型MySQL不⽀持全外连接。 • 左外连接返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录则结果集中对 应字段会显⽰为NULL。 • 右外连接与左外连接相反返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记 录则结果集中对应字段会显⽰为NULL。 • 全外连接结合了左外连接和右外连接的特点返回左右表中的所有记录。如果某⼀边表中没有匹 配的记录则结果集中对应字段会显⽰为NULL。
SELECT c.name AS course,COUNT(sc.student_id) AS student_count
FROM score sc
RIGHT JOIN course c ON sc.course_idc.id
GROUP BY c.name;SELECT s.name,COUNT(sc.course_id) AS course_count
FROM student s
LEFT JOIN score sc ON s.idsc.student_id
GROUP BY s.name;左外连接:
select s.name,sc.score from student s left join score sc on sc.student_ids.id;统计所有学生的选课情况含未选课学生 右连接不含未选课的学生
select s.name,sc.score from student s right join score sc on sc.student_ids.id;5.3自连接
⾃连接是⾃⼰与⾃⼰取笛卡尔积可以把⾏转化成列在查询的时候可以使⽤where条件对结果进⾏过滤或者说实现⾏与⾏之间的⽐较。在做表连接时为表起不同的别名
再查询成绩表中JAVA成绩⽐MySQL成绩好的信息
select st.name,c.name 班级,s1.score MySQL成绩,s2.score JAVA成绩 from score s1,score s2,course c1,course c2,student st, class cwhere s1.student_ids2.student_idand s2.course_idc2.idand s1.course_idc1.idand c1.nameMySQLand c2.namejavaand st.ids1.student_idand st.class_idc.idand s1.scores2.score;5.4子查询
单⾏⼦查询 查询唐三藏的同班同学 select * from student where class_id(select class_id from student s where s.name唐三藏);多行子查询: 查询MySQL或Java课程的成绩信息
select * from score where course_id in(select c.id from course c where c.nameMySQl or c.nameJAVA);多列子查询
insert into score(score,student_id,course_id) values(70.5,1,1),(98.5,1,3),(33,1,5);select * from score where(score,student_id,course_id)in(select score ,student_id ,course_id from score group by score,student_id,course_id having count(*)1);在from⼦句中使⽤⼦查询 select * from score sc1,(select avg(sc2.score)score from score sc2, student s,class c where sc2.student_ids.idand s.class_idc.idand c.namejava001班)temp where sc1.scoretemp.score;5.5合并查询 create table student1 like student;insert into student1(name,sno,age,gender,enroll_date,class_id) values (张三,100008,18,1,2001-09-01,2),(孙悟空, 100002, 18, 1, 1986-09-01, 1),(李四,100009,18 ,1,2001-09-01,2),(王五,100010,18,1,2001-09-01,3);Union: 该操作符⽤于取得两个结果集的并集。当使⽤该操作符时会⾃动去掉结果集中的重复⾏。 Union all 该操作符⽤于取得两个结果集的并集。当使⽤该操作符时不会去掉结果集中的重复⾏
5.6插入查询
语法:INSERT INTO table_name [(column [, column ...])] SELECT ..将student表中C001班的学⽣复制到student1表中:
insert into student1 (name,sno,age,gender,enroll_date,class_id) select s.name,s.sno, s.age,s.gender,s.enroll_date,s.class_id from student s,class c where s.class_idc.idand c.nameC001班;6.MySQL视图
6.1 视图的核心概念
虚拟表视图的结构和内容由SELECT语句定义不独立存储数据。 动态数据视图的数据在查询时实时生成反映基表的最新状态。 逻辑抽象隐藏底层表的复杂性提供简化的数据访问接口。
6.2 视图的用途
简化复杂查询将多表连接、子查询等复杂操作封装成视图用户直接查询视图即可。 数据安全通过视图限制用户访问敏感字段如隐藏薪资列。 逻辑独立性修改基表结构时可调整视图定义以保持应用层兼容。 统一数据视角为不同用户或应用提供定制化的数据子集。
create table student(
id bigint primary key auto_increment,
name varchar(20)not null,
sno varchar(10) not null,
age int default 18,
gender tinyint(1),
enroll_date date,
class_id bigint,
foreign key(class_id) references class(id)
);
insert into student (name, sno, age, gender, enroll_date, class_id) values
(唐三藏, 100001, 18, 1, 1986-09-01, 1),
(孙悟空, 100002, 18, 1, 1986-09-01, 1),
(猪悟能, 100003, 18, 1, 1986-09-01, 1),
(沙悟净, 100004, 18, 1, 1986-09-01, 1),
(宋江, 200001, 18, 1, 2000-09-01, 2),
(武松, 200002, 18, 1, 2000-09-01, 2),
(李逹, 200003, 18, 1, 2000-09-01, 2),
(不想毕业, 200004, 18, 1, 2000-09-01, 2); 6.3创建视图
语法:
CREATE VIEW view_name [(column_list)] AS select_statementcreate view v_student as select * from student s where s.id4;6.4修改数据
update student set student.name李白 where student.id1;修改真实表的数据会影响视图 修改视图数据也会影响真实表
6.5删除视图
语法:
drop view view_name;6. 6视图的优点
简单性视图可以将复杂的查询封装成⼀个简单的查询。例如针对⼀个复杂的多表连接查询可 以创建⼀个视图⽤⼾只需查询视图⽽⽆需了解底层的复杂逻辑。安全性通过视图可以隐藏表中的敏感数据。例如⼀个系统的⽤⼾表中可以创建⼀个不包含 密码列视图普通⽤⼾只能访问这个视图⽽不能访问原始表。逻辑数据独⽴性视图提供了⼀种逻辑数据独⽴性即使底层表结构发⽣变化只需修改视图定 义⽽⽆需修改依赖视图的应⽤程序。使⽤到应⽤程序与数据库的解耦重命名列视图允许⽤⼾重命名列名以增强数据可读性
7.MySQL索引
7.1了解索引
在MySQL中索引Index是一种帮助数据库高效获取数据的数据结构类似于书籍的目录能够快速定位到目标数据从而避免全表扫描提升查询效率
本质与结构: 索引的本质是有序的数据结构通常采用B树BTree实现。这种结构使得数据按特定列排序并维护列值与数据行位置的映射关系从而支持高效的等值查询、范围查询及排序操作。
作用: 加速查询通过索引可直接定位到符合条件的记录减少磁盘I/O次数。例如若对name字段创建索引查询WHERE nameAlice’时无需逐行扫描表。 优化排序与连接索引的天然有序性可加速ORDER BY、GROUP BY及表连接操作。 存储方式: 索引是独立存储在磁盘中的数据结构包含索引字段的值和指向数据行的指针如主键或磁盘地址。例如非聚集索引二级索引存储索引列和主键值需通过主键回表查询完整数据。
优缺点 优点显著提升查询速度尤其在处理海量数据时效果明显。 缺点 占用额外存储空间。 增删改操作需维护索引可能降低写性能。
7.2以B树为索引数据结构的原因
2.1 Hash 索引
±------±------ | Key | Value | ±------±------ | “John”| 0x123| // 指向 John 对应的数据行 | “Jane”| 0x456| // 指向 Jane 对应的数据行 | “Mike”| 0x789| // 指向 Mike 对应的数据行 ±------±------
数据结构 Hash 表 原理 通过 Hash 函数将键索引列的值映射到 Hash 表中的一个位置该位置存储了指向实际数据行的指针。
优点查找速度快 在理想情况下Hash 查找的时间复杂度为 O(1)。 缺点不支持范围查询 Hash 表是无序的无法有效地支持范围查询例如 WHERE age 20。 不支持排序 同样因为无序性无法用于排序操作。 Hash 冲突 不同的键可能映射到同一个位置需要额外的冲突解决机制这会降低性能。不支持前缀查找 无法利用 Hash 索引进行前缀查找例如 WHERE name LIKE ‘John%’。 适用场景 等值查询且不需要范围查询、排序或前缀查找。 例如在 Memory 存储引擎中Hash 索引可以用于快速查找。
2.2二叉搜索树 (BST) 索引 50/ \
30 70/ \ / 20 40 60 80
数据结构 二叉搜索树 原理 每个节点包含一个键左子树的所有键小于该节点右子树的所有键大于该节点。
优点支持范围查询 可以通过中序遍历进行范围查询。 支持排序 中序遍历可以得到排序后的结果。 缺点可能退化成链表 在最坏情况下例如键按顺序插入二叉搜索树会退化成链表导致查找时间复杂度变为 O(n)。 树的高度较高 对于大量数据树的高度会很高导致查找效率降低。 适用场景 数据量较小且需要范围查询和排序。 N 叉树 索引
2.3数据结构 N 叉树例如 B 树 [50, 80]/ | \[20, 30] [60, 70] [90, 100]/ | \ / | \ / | \… … … … . … … . … … …
原理 每个节点可以包含多个键和多个子节点从而降低树的高度。
优点降低树的高度 相比于二叉搜索树N 叉树可以显著降低树的高度减少磁盘 I/O 次数。 支持范围查询 可以通过遍历进行范围查询。 支持排序 可以通过遍历进行排序。 缺点节点大小 需要合理选择节点大小以平衡内存占用和 I/O 性能。 实现复杂度 实现和维护比二叉搜索树更复杂。 适用场景 数据量较大需要范围查询和排序。 B 树是一种常见的 N 叉树被广泛用于数据库索引 B 树 索引
2.4数据结构 B 树 原理是 B 树的变种。 所有数据都存储在叶子节点中非叶子节点只存储键用于索引。 叶子节点之间通过链表连接方便范围查询。
优点 更稳定的性能 所有查询都需要访问叶子节点因此查询性能更稳定。 更高的扇出 非叶子节点只存储键可以存储更多的键进一步降低树的高度。 更好的范围查询性能 叶子节点之间的链表连接使得范围查询非常高效。 更适合磁盘存储 B 树的设计更符合磁盘的 I/O 特性可以减少磁盘 I/O 次数。 缺点实现复杂度 实现和维护比 B 树更复杂。 适用场景 几乎所有需要索引的场景特别是数据量较大需要范围查询和排序的场景。 MySQL 的 InnoDB 存储引擎默认使用 B 树索引
叶子节点链表 叶子节点之间通过双向链表连接方便范围查询。 2.5为什么 B 树是数据库索引的首选
1.磁盘 I/O 优化 数据库的数据通常存储在磁盘上而磁盘 I/O 是一个非常耗时的操作。 B 树通过降低树的高度和将数据存储在叶子节点中可以减少磁盘 I/O 次数从而提高查询性能。
2.范围查询优化 叶子节点之间的链表连接使得范围查询非常高效只需要遍历链表即可。 稳定的性能 所有查询都需要访问叶子节点因此查询性能更稳定。
3.更高的扇出 非叶子节点只存储键可以存储更多的键进一步降低树的高度
7.3MySQL页
3.1使用页的原因
提高 I/O 效率 磁盘 I/O 是数据库操作中最耗时的部分之一。 每次从磁盘读取或写入数据都需要花费大量时间。 如果数据库每次只读取或写入少量数据例如单个字节或单个记录那么 I/O 开销将非常高。
页的批量读写 通过将数据组织成页数据库可以一次性读取或写入整个页从而减少了 I/O 操作的次数。 即使只需要访问页中的一小部分数据也需要读取整个页但由于减少了 I/O 次数整体性能仍然可以提高。
内存管理 数据库系统通常会将一部分数据缓存在内存中以便快速访问。 使用页作为内存管理的基本单元可以方便地将整个页加载到内存中并在需要时将其写回磁盘。
数据一致性 页可以作为事务的基本单元确保数据的一致性。 在事务提交之前对页的修改可以先缓存在内存中然后在事务提交时一次性写入磁盘从而避免了数据不一致的问题。
空间管理 页可以作为磁盘空间分配和回收的基本单元方便管理磁盘空间。
页 书的一页 页目录 书的目录 数据行 页面中的文字
3. 2页的结构概览
一个 InnoDB 页的完整结构如下以 数据页 为例
plaintext ±---------------------- | File Header | -- 页的元数据如页类型、前后页指针 ±---------------------- | Page Header | -- 数据页的专用信息如记录数、空闲空间 ±---------------------- | Infimum Supremum | -- 虚拟的“最小”和“最大”记录链表边界 ±---------------------- | User Records | -- 实际存储的数据行按主键顺序链表链接 ±---------------------- | Free Space | -- 未使用的空间用于插入新记录 ±---------------------- | Page Directory | -- 页目录槽/Slot加速记录定位 ±---------------------- | File Trailer | -- 页的校验信息确保数据完整性 ±----------------------
3.3页文件头和页文件尾 上页号和下页号可以使页与页之间连接起来形成双向链表
3.4页主体
⻚主体部分是保存真实数据的主要区域每当创建⼀个新⻚都会⾃动分配两个⾏⼀个是⻚内最 ⼩⾏ Infimun 另⼀个是⻚内最⼤⾏ Supremun 这两个⾏并不存储任何真实信息⽽是做为 数据⾏链表的头和尾第⼀个数据⾏有⼀个记录下⼀⾏的地址偏移量的区域 next_record 将⻚ 内所有数据⾏组成了⼀个单向链表此时新⻚的结构如下所示 当向⼀个新⻚插⼊数据时将 Infimun 连接第⼀个数据⾏最后⼀⾏真实数据⾏连接 Supremun 这样数据⾏就构建成了⼀个单向链表更多的⾏数据插⼊后会按照主键从⼩到⼤ 的顺序进⾏链接如下图所示 3.5 页目录:
页目录是数据页中的一个重要组成部分用于加速在页内查找记录。
作用 在一个数据页中记录并不是按照主键顺序排列的。 如果要查找某个记录需要遍历整个页。 为了提高查找效率MySQL 使用页目录来对页中的记录进行分组并为每个组创建一个槽Slot槽中存储了该组中最大记录的地址偏移量。 查找过程 使用二分查找在页目录中找到包含目标记录的槽。 在槽对应的组中顺序查找目标记录。 优点 通过页目录可以将页内的查找范围缩小到组内从而提高查找效率。 3.6数据页头
数据页头存储关于数据页的元数据 7.4索引分类
4.1 主键索引Primary Key Index
定义唯一标识表中每一行数据的索引每个表只能有一个主键。 特点值唯一且不允许重复。列值不能为 NULL。 在 InnoDB 存储引擎中主键索引是聚集索引直接关联数据物理存储顺序。 使用场景用于快速定位唯一记录如通过 WHERE id 1 查询。
4.2 普通索引Secondary Index / Non-Unique Index
定义最基本的索引类型无唯一性约束。 特点允许重复值和 NULL。仅用于加速查询不强制数据唯一性。 使用场景频繁作为查询条件的非唯一字段如 WHERE name ‘Alice’。
4.3 唯一索引Unique Index
定义确保列值唯一的索引但允许 NULL只能有一个 NULL。 特点值唯一但列可以为 NULL。 与主键索引的区别主键索引不允许 NULL且每个表只能有一个主键。 使用场景需要唯一性但非主键的字段如身份证号、邮箱等。
4.4 全文索引Full-Text Index
定义针对文本内容如 VARCHAR、TEXT的搜索优化索引。 特点支持自然语言搜索和关键词匹配如 MATCH(column) AGAINST(‘keyword’)。 适用于大文本字段的高效模糊查询。 使用场景文章内容搜索、评论关键词过滤等。
4.5 聚集索引Clustered Index
定义索引结构与数据物理存储顺序一致如 InnoDB 的主键索引。 特点一个表只能有一个聚集索引。 数据直接存储在索引的叶子节点查询效率高。 使用场景主键查询或范围查询如 WHERE id BETWEEN 100 AND 200。
4.6 非聚集索引Non-Clustered Index
定义索引结构与数据物理存储分离叶子节点存储指向数据行的指针。 特点一个表可以有多个非聚集索引。 查询时需要回表通过指针查找数据效率低于聚集索引。 使用场景非主键字段的查询优化如 WHERE age 20。
4.7 索引覆盖Covering Index
定义查询所需字段全部包含在索引中无需回表查数据。 特点通过联合索引Composite Index实现如索引 (name, age) 覆盖查询 SELECT age FROM users WHERE name ‘Bob’。 减少 I/O 操作显著提升查询性能。 使用场景高频查询字段的优化避免回表开销。
4.8总结 4.9索引设计建议
主键选择优先使用自增整数作为主键避免页分裂。 联合索引根据查询频率和字段区分度设计字段顺序。 避免冗余删除未使用的索引减少写操作开销。 权衡性能索引越多查询越快但插入/更新越慢。
5.使用索引
5.1⾃动创建
• 当我们为⼀张表加主键约束(Primary key)外键约束(Foreign Key)唯⼀约束(Unique)时 MySQL会为对应的的列⾃动创建⼀个索引 • 如果表不指定任何约束时MySQL会⾃动为每⼀列⽣成⼀个索引并⽤ ROW_ID 进⾏标识
5.2手动创建
5.2.1主键索引 5.2.2唯一索引 5.2.3 普通索引 5.2.4创建复合索引
创建语法与创建普通索引相同只不过指定多个列列与列之间⽤逗号隔开
5.3 查看索引 5.4 删除索引
语法alter table 表名 drop primary key
5.4.1删除主键索引 5.4.2删除其它索引
语法alter table 表名 drop index 索引名
6.MySQL事务
6.1了解事务
在MySQL中事务是指一组操作的集合这些操作要么全部成功执行要么全部不执行。事务的主要目的是确保数据库操作的完整性和一致性。事务通常用于处理多个相关的数据库操作以确保在发生错误或故障时数据库能够保持一致的状态。
create table t_account(
id bigint primary key auto_increment,
name varchar(50) not null,
balance decimal(12,2)not null
);
insert into t_account values(1,张三,1000),(2,李四,1000);select * from t_account;
#张三余额减少100
update t_account set balancebalance-100 where name张三;
#李四余额增加100
update t_account set balancebalance100 where name李四;• 如果转账成功应该有以下结果
张三的账⼾余额减少 100 变成 900 李四的账⼾余额增加了 100 变成 1100 不能出现张 三的余额减少⽽李四的余额没有增加的情况张三和李四在发⽣转账前后的总额不变也就是说转账前张三和李四的余额总数为 100010002000 转账后他们的余额总数为 90011002000 转账后的余额结果应当保存到存储介质中以便以后读取还有⼀点需要要注意在转账的处理过程中张三和李四的余额不能因其他的转账事件⽽受到⼲扰 以上这四点在事务的整个执⾏过程中必须要得到保证这也就是事务的 ACID 特性
6.2自动/手动创建事务
默认情况下MySQL是⾃动提交事务的也就是说我们执⾏的每个修改操作⽐如插⼊、更新和删 除都会⾃动开启⼀个事务并在语句执⾏完成之后⾃动提交发⽣异常时⾃动回滚。 • 查看当前事务是否⾃动提交可以使⽤以下语句
show variables like autocommit;通过以下语句可以设置事务为手动/自动
#自动:
set autocommit1;
set autocommiton;
#手动
set autocommit0;
set autocommitoff;6.3使用事务
3.1查看存储引擎
要使⽤事务那么数据库就要⽀持事务在MySQL中⽀持事务的存储引擎是InnoDB可以通过 show engines; 语句查看
3.2 语法
#开始一个新的事务:
start transaction;
begin;
#提交当前事务,并对更改持久化保存
commit;
#回滚当前事务,取消更改
rollback;START TRANSACTION 或 BEGIN 开始⼀个新的事务 • COMMIT 提交当前事务并对更改持久化保存 • ROLLBACK 回滚当前事务取消其更改 • ⽆论提交还是回滚事务都会关闭
3.3开始一个事务后进行回滚
beginl;
update t_account set balancebalance100 where name张三;
select * from t_account;
rollback;
select * from t_account;3.4设置保存点
start transaction;
update t_account set balancebalance-100 where name张三;
update t_account set balancebalance100 where name李四;
savepoint savepoint1;
select * from t_account; update t_account set balancebalance-100 where name张三;
update t_account set balancebalance100 where name李四;
savepoint savepoint2;
select * from t_account;update t_account set balance0 where name张三;
update t_account set balance0 where name李四;
savepoint savepoint3;
select * from t_account;rollback to savepoint3;
select * from t_account;
rollback to savepoint1;
select * from t_account;
rollback to savepoint2;
select * from t_account;重新创建后再观察
rollback to savepoint3;
select * from t_account;
rollback to savepoint2;
select * from t_account;
rollback to savepoint1;
select * from t_account;rollback;
select * from t_account;
commit;注意: 只要使⽤ START TRANSACTION 或 BEGIN 开启事务必须要通过 COMMIT 提交才会持久 化与是否设置 SET autocommit ⽆关。 • ⼿动提交模式下不⽤显⽰开启事务执⾏修改操作后提交或回滚事务时直接使⽤ commit 或 rollback • 已提交的事务不能回滚
6.4事务的隔离性和隔离级别
4.1什么是隔离性
MySQL服务可以同时被多个客⼾端访问每个客⼾端执⾏的DML语句以事务为基本单位那么不同的客⼾端在对同⼀张表中的同⼀条数据进⾏修改的时候就可能出现相互影响的情况为了保证不同的事务之间在执⾏的过程中不受影响那么事务之间就需要要相互隔离这种特性就是隔离性。
4.2 隔离级别
事务具有隔离性那么如何实现事务之间的隔离隔离到什么程度如何保证数据安全的同时也要兼顾性能这都是要思考的问题。事务间不同程度的隔离称为事务的隔离级别不同的隔离级别在性能和安全⽅⾯做了取舍有的隔离级别注重并发性有的注重安全性有的则是并发和安全适中在MySQL的InnoDB引擎中事务 的隔离级别有四种分别是 • READ UNCOMMITTED 读未提交 • READ COMMITTED 读已提交 • REPEATABLE READ 可重复读(默认) • SERIALIZABLE 串⾏化
4.3查看和设置隔离级别
事务的隔离级别分为全局作用域和会话作用域 查看不同作用域事务隔离级别
# 设置全局事务隔离级别为串⾏化后续所有事务⽣效不影响当前事务
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
# 设置会话事务隔离级别为串⾏化当前会话后续的所有事务⽣效不影响当前事务可以在任何时候执行
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
# 如果不指定任何作⽤域设置只针对下⼀个事务随后的事务恢复之前的隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;set globl transaction_isolationread-committed;
select GLOBAL.transaction_isolation;
set session transaction_isolationread-committed;
select session.transaction_isolation;6.5不同隔离级别存在的问题
5.1 READ UNCOMMITTED - 读未提交与脏读
存在问题: 出现在事务的 READ UNCOMMITTED 隔离级别下由于在读取数据时不做任何限制所以并发性能很⾼但是会出现⼤量的数据安全问题⽐如在事务A中执⾏了⼀条 INSERT 语句在没有执⾏COMMIT 的情况下会在事务B中被读取到此时如果事务A执⾏回滚操作那么事务B中读取到事务A写⼊的数据将没有意义我们把这个理象叫做 “脏读” 。
问题重现
set global transaction isolation level read uncommitted;
select global.treansaction_isolation;在不同的客服端:
start transaction;
insert into t_account values(3,王五,3000);
select * from t_account;rollback;
select * from t_account;• 由于 READ UNCOMMITTED 读未提交会出现脏读现象在正常的业务中出现这种问题会产⽣⾮ 常危重后果所以正常情况下应该避免使⽤ READ UNCOMMITTED 读未提交这种的隔离级别
5.2 READ COMMITTED - 读已提交与不可重复读
存在问题 为了解决脏读问题可以把事务的隔离级别设置为 READ COMMITTED 这时事务只能读到了其他事务提交之后的数据但会出现不可重复读的问题⽐如事务A先对某条数据进⾏了查询之后事务B对这条数据进⾏了修改并且提交( COMMIT )事务事务A再对这条数据进⾏查询时得到了事务B修改之后的结果这导致了事务A在同⼀个事务中以相同的条件查询得到了不同的值这个现象要不可重复读。 问题重现 • 在⼀个客⼾端A中先设置全局事务隔离级别为 READ COMMITTED 读已提交
use java;
insert into t_account values(null,王五,3000);
select * from t_account;
start transaction;
select global.transaction_isolation;
update t_account set balance0 where name王五;
rollback;
select * from t_account;5.3REPEATABLE READ - 可重复读与幻读
存在问题 为了解决不可重复读问题可以把事务的隔离级别设置为 REPEATABLE READ 这时同⼀个事务中读取的数据在任何时候都是相同的结果但还会出现⼀个问题事务A查询了⼀个区间的记录得到结果集A事务B向这个区间的间隙中写⼊了⼀条记录并提交事务A再查询这个区间的结果集时会查到事务B新写⼊的记录得到结果集B两次查询的结果集不⼀致这个现象就是幻读。MySQL的InnoDB存储引擎使⽤了Next-Key锁解决了⼤部分幻读问题。 问题重现
set global transaction isolation level read committed;
select global.transaction_isolation;use java;
start transaction;
update t_account set balance888 where name王五;
select * from t_account;
insert into t_account values(3,赵六,5000);
commit;###3 5.4SERIALIZABLE - 串⾏化 进⼀步提升事务的隔离级别到 SERIALIZABLE 此时所有事务串⾏执⾏可以解决所有并发中的安 全问题。
6.6不同隔离级别的性能与安全 InnoDB存储引擎事务隔离性以及相关的隔离级别是由锁和MVCC机制配合实现的。
