数据库--视图，事物，范式等-CSDN博客

本文链接：https://blog.csdn.net/raoxurou/article/details/121365792

系列文章目录

视图

将SQL语句的查询结果当作虚拟表实体化保存起来。方便后续反复使用

#创建语句：创建完成之后，只有表结构，且不能修改表中的数据
create view teacher2course as select * from teacher inner join course on teacher.tid = course.teacher_id;

#删除视图
drop view teacher2course;

触发器trigger

满足特定条件之后自动执行

#在MySQL只有三种情况下可以触发
1、针对表的增
	增前 增后
2、针对表的改
	改前 改后
3、针对表的删
	删前 删后

语法结构

create ttigger 触发器名字 before/after insert/update on 表名 for each row  begin 
	sql语句
end

触发器命名

tri_after_insert_t1、tri_before_delete_t1

临时修改SQL语句的结束符

delimiter $$

在MySQL中NEW

特指数据对象可以通过点的方式获取字段对应的数据

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代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

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 ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('tony','0755','ls -l /etc',NOW(),'yes'),
    ('tony','0755','cat /etc/passwd',NOW(),'no'),
    ('tony','0755','useradd xxx',NOW(),'no'),
    ('tony','0755','ps aux',NOW(),'yes');

#查询errlog表记录
select * from errlog;

查看触发器

show triggers;

删除触发器

drop triggers 触发器名称

事物四大特性（重点）

原子性

每个事物都是不可分割的最小单位（同一个事物内的多个操作y要么同时成功要么同时失败）

一致性

事物必须是使数据从一个一致性状态变到另一个一致性状态，一致性与原子性是密切相关的

隔离性

事物与事物之间彼此不干扰

持久性

一个事物一旦提交，他对数据中数据的改变就应该是永久性的

开启事物

start transaction;

事物回滚

rollback;

事物确认

commit;

存储过程

类似于python中定义函数

无参存储过程

delimiter $$
create procedure p1()
begin 
	select * from user;
end $$
delimiter ;

#调用
call P1()

有参存储过程

delimiter $$
create procedure p2(
in m int,
in n int,
out res int
)
begin
	select * from user where id >m and id < n;
	set res=0;
end $$
delimiter ;

代码操作存储过程

import pymysql
conn =pymysql.connect(
	host='',
	port=3306,
	user='',
	passwd='',
	db='',
	charset='utf8',
	autocommit=Ture)
	
cursor=conn.cursor(cursor=pymysql.cursor.DicCursor)
cursor.callproc('p2',(1,3,10))

print(cursor.fetchall())

函数

相当于python中的内置函数
通过help 函数名。查看帮助信息

移除指定符

Trim,LTrim,RTrim

大小写转换

Lower Upper

获取左右起始指定个数字符

Left、Right

返回读音相似值(对英文效果)

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');

1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;


#更新日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
dateiff 计算两个日期的差值

流程控制

if判断

if i = 1 THEN
       SELECT 1;
ELSEIF i = 2 THEN
       SELECT 2;
ELSE
       SELECT 7;
END IF;

while循环

SET num = 0 ;
WHILE num < 10 DO
    SELECT
        num ;
    SET num = num + 1 ;
END WHILE ;

索引和慢查询优化

可以帮助加宽数据查询速度的工具

算法

解决事物的办法

数据结构
	二叉树(只能分两个叉)
	b树 b+树(叶子节点添加了指针) b*树(枝节点也添加了指针)
    # 添加指针是为了加快范围查询的速度
 
将某个字段添加成索引就相当于依据该字段建立了一颗b+树从而加快查询速度
如果某个字段没有添加索引 那么依据该字段查询数据会非常的慢(一行行查找

索引的分类

primary key :主键索引除了有加速查询的效果之外还具有一定的约束条件
unique key:唯一索引除了有加速查询的效果之外还具有一定的约束条件
index key ：普通索引只有加速查询的效果没有额外约束

数据库设计三大范式

第一范式

用来确保每列的原子性，要求每列（或者每个属性值）都是不可再分的最小数据单元（也称为最小的原子单元）

第二范式

在第一范式的基础上更进一层，要求表中的每列都和主键相关，即要求实体的唯一性。如果一个表满足第一范式，并且除了主键以外的其他列全部都依赖于该主键，那么该表满足第二范式。

第三范式

在第二范式的基础上更进一层，第三范式是确保每列都和主键列直接相关，而不是间接相关，即限制列的冗余性。如果一个关系满足第二范式，并且除了主键以外的其他列都依赖于主键列，列和列之间不存在相互依赖关系，则满足第三范式。