【一】视图
(1)视图须知概念
1.什么是视图？
视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接使用
2.为什么要用视图？
如果要频繁操作一张虚拟表(拼表组成)，就可以制作成视图，后续直接操作
注意：视图所获得的虚拟表与原表数据无关
(2)视图相关语法(和表的语法一致)
1.创建视图
create view 视图名(表名) as 虚拟表的查询SQL语句
eg:
CREATE VIEW my_view AS SELECT column1, column2 FROM my_table WHERE condition;
2.查
SELECT * FROM my_view;
3.改
UPDATE my_view SET column1 = value1 WHERE condition;
4.删
DROP VIEW my_view;
【二】触发器
(1)触发器概念
1.什么是触发器
满足对表数据进行增删改的情况下，自动触发的功能，称为触发器
2.触发器的六种使用情况
● 增前
● 增后
● 删前
● 删后
● 改前
● 改后
(2)语法结构
1.
create trigger 触发器的名字
before/after insert/update/delete
on 表名 for each row
begin
SQL语句
end
2.查看当前库下所有的触发器信息
show triggers\G;
3.删除当前库下指定的触发器信息
drop trigger 触发器名称;
4.触发器的名字一般情况下建议采用下列布局形式
tri_after_insert_t1
tri_before_update_t2
tri_before_delete_t3
(3)使用实例
表：
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #提交时间
success enum ('yes', 'no') #0代表执行失败
);
需求：cmd表插入数据的success如果值为no 则去errlog表中插入一条记录
delimiter $$ # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了
【三】事务(掌握)
(1)事务概念
"""当让多条SQL语句保持一致性的时候(要么同时成功，要么同事失败)，可以考虑使用事务"""
1.什么是事务
事务可以包含诸多SQL语句并且这些SQL语句
要么同时执行成功 要么同时执行失败 这是事务的原子性特点
事务的作用
2.四大特性(ACID)
ACID
A:原子性
一个事务是一个不可分割的整体 里面的操作要么都成立要么都不成立
C:一致性
事务必须使数据库从一个一致性状态变到另外一个一致性状态
I:隔离性
并发编程中 多个事务之间是相互隔离的 不会彼此干扰
D:持久性
事务一旦提交 产生的结果应该是永久的 不可逆的
(2)事务的使用
1.创建表及录入数据
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);
2.事务操作
开启一个事务的操作
start transaction;
编写SQL语句(同属于一个事务)
update user set balance=900 where name='jason';
update user set balance=1010 where name='kevin';
update user set balance=1090 where name='tank';
事务回滚(返回执行事务操作之前的数据库状态)
rollback; # 执行完回滚之后 事务自动结束
事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)
commit; # 执行完确认提交之后 无法回滚 事务自动结束
【四】存储过程
(1)存储过程的概念
1.什么是存储过程
存储过程就类似于Python中的自定义函数
(2)如何使用存储过程
1.定义
# 相当于定义函数
delimiter $$
create procedure 存储过程的名字(形参1,形参2...)
begin
sql 代码
end $$
delimiter ;
2.相当于调用函数
call p1()
3.查看存储过程具体信息
show create procedure pro1;
4.查看所有存储过程
show procedure status;
5.删除存储过程
drop procedure pro1;
(3)使用实例
类似于有参函数
delimiter $$
create procedure p1(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select tname from userinfo where id > m and id < n;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 针对res需要先提前定义
set @res=10; 定义
select @res; 查看
call p1(1,5,@res) 调用
select @res 查看
【五】函数
(1)函数概念
1.什么是函数
跟存储过程是有区别的，存储过程是自定义函数，函数就类似于内置函数
(2)语法
# 1.移除指定字符
Trim、LTrim、RTrim
# 2.大小写转换
Lower、Upper
# 3.获取左右起始指定个数字符
Left、Right
# 4.返回读音相似值(对英文效果)
Soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
where Soundex(name)=Soundex('J.Lie')
"""
# 5.日期格式:date_format
'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
【六】流程控制
# python if判断
if 条件:
子代码
elif 条件:
子代码
else:
子代码
# js if判断
if(条件){
子代码
}else if(条件){
子代码
}else{
子代码
}
# MySQL if判断
if 条件 then
子代码
elseif 条件 then
子代码
else
子代码
end if;
# MySQL while循环
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT num ;
SET num = num + 1 ;
END WHILE ;
【七】索引
(1)索引概念
1.什么是索引
索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构，这也是索引
2.MySQL中索引的类型
创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建 对应列的索引。
3.索引的本质
通过不断的缩小想要的数据范围筛选出最终的结果
4.id name pwd post_comment addr age
基于id查找数据很快 但是基于addr查找数据就很慢
解决的措施可以是给addr添加索引
'''索引虽然好用 但是不能无限制的创建!!!'''
5.**索引的影响:**
* 在表中有大量数据的前提下，创建索引速度会很慢
* 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
6.索引的底层数据结构是b+树
b树 红黑树 二叉树 b*树 b+树
上述结构都是为了更好的基于树查找到相应的数据
(2)索引语法用法
1.查看索引
show index from 表名;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 8 | NULL | NULL | | BTREE | | |
| student | 0 | sn | 1 | sn | A | 8 | NULL | NULL | YES | BTREE | | |
| student | 1 | classes_id | 1 | classes_id | A | 2 | NULL | NULL | YES | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)
2.创建索引
对于非主键、非唯一约束、非外键的字段，可以创建普通索引
create index 索引名 on 表名(字段名);
# 索引名的命名规则一般是：index_表名_列名
3.删除索引
drop index 索引名 on 表名
(3)聚集索引(主键索引)
【1】什么是聚集索引
● 聚集索引（Clustered Index）是关系型数据库中的一种索引类型，它决定了表中数据的物理存储顺序。
##### ● 在一个表中，只能有一个聚集索引。
● 聚集索引对表进行了重新组织，使得数据按照聚集索引的键值顺序存储在磁盘上。
● 由于聚集索引决定了数据的物理存储顺序，因此通过聚集索引可以快速地找到特定范围内的数据
● MySQL的聚簇索引是基于B+树的数据结构实现的，它会把数据存储在索引的叶子节点上，叶子节点之间按顺序链接，使得按主键进行搜索时速度最快。
● 如果没有主键，如果按主键搜索，速度是最快的。
【2】聚集索引的特点
● 数据的逻辑顺序和物理顺序是一致的，通过聚集索引可以直接访问特定行，因此聚集索引的查询速度很快。
##### ● 聚集索引的键值必须是唯一的，不允许重复值存在。
● 当表中的数据发生插入、删除或更新操作时，聚集索引需要进行相应的调整以保持数据的有序性，这可能会对性能产生一定影响。
##### ● 如果表中没有定义聚集索引，那么表的数据存储顺序将按照物理地址来存储。
● 表不建立主键，也会有个隐藏字段是主键，是主键索引
● 主键索引对于按照主键进行查询的性能非常高。
创建：
ALTER TABLE table_name ADD PRIMARY KEY (column);
(4)辅助索引(普通索引)
查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引
ALTER TABLE table_name ADD INDEX index_name (column);
(5)唯一索引（unique）
【1】什么是唯一索引
● 唯一索引是指该索引的所有值都是唯一的，不允许出现重复值。
【2】语法
● MySQL中可以通过以下语法创建唯一索引
ALTER TABLE table_name ADD UNIQUE KEY index_name (column);
【3】注意事项
● 与普通索引不同的是，如果尝试向包含唯一索引的列中插入重复的值，则会引发错误。
● 唯一索引可以用于确保数据的一致性和完整性，并且可以帮助提高查询性能。
(6)全文索引
【1】什么是全文索引
● 全文索引是一种特殊的索引，它可以用来存储和检索文本数据。
● 全文索引可以包含单词、短语和其他类型的文本内容，并支持模糊匹配和近似匹配。
【2】语法
● MySQL中可以通过以下语法创建全文索引
CREATE FULLTEXT INDEX index_name ON table_name (column);
【3】注意事项
● 需要注意的是，只有MyISAM和InnoDB存储引擎支持全文索引。
● 此外，创建全文索引可能会增加索引维护的成本，并且可能会降低其他类型的查询性能。
● 因此，在创建全文索引时需要权衡其利弊。
(7)覆盖索引
只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
select name from user where name='jason';
(8)非覆盖索引
虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找
select age from user where name='jason';
【八】隔离机制
(1)脏读
1.脏读是指当一个事务读取了其他事务尚未提交的数据时发生的现象。
2.意思就是比如说你一个事务，没有commit前，读的就是脏数据，只要回滚就不存在了
(2)不可重复读
1.不可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况
2.就是在每次读同一个数据时，可能数据被修改读出来就不一样
(3)幻读
1.幻读的本质在于某一次select操作得到的结果无法支撑后续的业务操作。
2.就是你第一次读的时候是这么多数据，但是当你插入数据，发现不能插入了，因为已经存在了
(4)解决办法
[1]Read uncommitted（读未提交）
最低的隔离级别，在这个级别下，一个事务可以读取到另一个事务尚未提交的数据
可能导致脏读（Dirty Read）问题，即读取到未经验证的数据。
[2]Read committed（读已提交）
在这个级别下，一个事务只能读取到已经提交的数据，避免了脏读问题。
但是可能会出现不可重复读（Non-repeatable Read）问题
即同一事务中，两次读取相同的记录可能得到不同的结果，因为其他事务修改了这些记录。
[3]Repeatable read（可重复读取）
在这个级别下，事务开始读取数据后，其他事务无法修改这些数据，保证了同一个事务内两次读取相同记录的一致性。
但是可能会出现幻读（Phantom Read）问题，
即同一查询在同一事务中两次执行可能返回不同的结果，因为其他事务插入或删除了符合查询条件的记录。
[4]Serializable（串行化）
最高级别的隔离级别，要求事务串行执行，事务之间完全隔离，避免了脏读、不可重复读和幻读问题。
但是这会牺牲并发性能，因为并发事务被限制为顺序执行。
【九】锁机制
目的：提升数据安全性
分类：按粒度分细---》粗
行级锁
表级锁
页级锁
(1)行级锁
1.⾏级锁是Mysql中锁定粒度最细的⼀种锁
○ 表示只针对当前操作的⾏进⾏加锁。
2.⾏级锁能⼤⼤减少数据库操作的冲突。
○ 其加锁粒度最⼩，但加锁的开销也最⼤。
3. ⾏级锁分为共享锁和排他锁。
4.通俗的说就是在你写的时候必须要加锁，只能一个一个写入，如果一起写数据就会错乱
5.行级锁锁的是索引
命中索引以后才会锁行
如果没有命中索引
会把整张表都锁起来。
6.流程：
命中主键索引就锁定这条语句命中的主键索引
命中辅助索引就会先锁定这条辅助索引
再锁定相关的主键索引
考虑到性能，innodb默认支持行级锁
但是只有在命中索引的情况下才锁行，
否则锁住所有行
本质还是行锁
但是此刻相当于锁表了
【十】三大范式
三大范式是数据库设计的基础，用于确保数据的准确性、完整性和一致性，避免数据的冗余和不一致性。以下是三大范式的详细解释：
1.第一范式（1NF）
定义：数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值或重复的属性。
特点：
强调列的原子性，即列不可再分。
每一列都是独立的、不可分割的。
保证了数据的准确性，避免了数据的冗余和不一致性。
实际应用：
通常用于记录简单、固定的实体关系。
2.第二范式（2NF）
完全依赖”指的是非主属性不能仅依赖于主键的一部分
定义：在满足第一范式的基础上，非主属性必须完全依赖于整个主键，而不是主键的一部分。
特点：
表必须有一个主键。
没有包含在主键中的列必须完全依赖于主键。
保证了数据的完整性，避免了数据的冗余和不一致性。
实际应用：
通常用于记录复杂、动态的实体关系。
3.第三范式（3NF）
定义：在满足第二范式的基础上，任何非主属性不依赖于其他非主属性（即消除传递依赖）。
特点：
非主键列必须直接依赖于主键。
不能存在非主键列A依赖于非主键列B，非主键列B依赖于主键的情况（即传递依赖）。
进一步保证了数据的完整性，避免了数据的冗余和不一致性。
实际应用：
通常用于记录复杂、动态的实体关系，尤其是当数据表中存在大量数据冗余时。
4.总结：
三大范式理论是数据库设计的基础，它们通过确保数据的原子性、完整性和一致性，避免了数据的冗余和不一致性。在实际应用中，根据数据的复杂性和需求，可以选择不同的范式进行数据库设计。随着数据库技术的发展，新的范式理论也在不断涌现，为数据库设计提供了更多的选择。
扫一扫
2978
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
