数据库学习笔记04 事务+索引+视图+PLSQL+练习

最新推荐文章于 2022-10-03 18:07:17 发布

guyueCT

最新推荐文章于 2022-10-03 18:07:17 发布

阅读量280

点赞数

分类专栏： Java全栈02 数据库学习笔记文章标签：数据库触发器编程语言 mysql

本文链接：https://blog.csdn.net/qq_40670344/article/details/115699825

版权

Java全栈02 数据库学习笔记专栏收录该内容

5 篇文章 0 订阅

订阅专栏

这篇博客详细介绍了MySQL数据库中的事务管理，包括ACID特性、事务操作与注意事项。接着讨论了索引的概念、类型及其优缺点。此外，还涵盖了视图的基础知识。最后，重点讲解了PL/SQL，包括函数、存储过程和触发器的使用。

摘要由CSDN通过智能技术生成

一、事务

1.1 事务（transaction）

事务是作为单个逻辑工作单元执行的一系列操作。

事务是一个不可分割的工作逻辑单元。

事务处理可以用来维护数据库的完整性，保证成批的SQL语句作为一个整体一起向系统提交，要么全部执行，要么全部不执行。

事务用来管理insert、update、delete语句。

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。

1.2 事务的特性——ACID

1）原子性(Atomicity)

一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。

事务在执行中发生错误，会回滚到事务开始前的状态。

2）一致性(Consistency)

在事务开始前和结束后，数据库的完整性没有被破坏，数据处于一致状态。

3）隔离性(Isolation)

数据库允许多个并发事务同时对其数据进行读写和修改的能力。

并发事务是彼此隔离，防止多个事务并发执行时由于交叉执行而导致数据的不一致。

4）永久性(Durability)

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

1.3 事务管理

MySQL使用下列语句来管理事务

start transaction | begin 开始一个事务

savepoint aa 设置保存点aa

rollback to 事务回滚

commit 事务提交

START TRANSACTION; #开启事务
SAVEPOINT aa; #设置保存点aa
#添加一条记录
INSERT INTO MS_CLASS(CLASS_NAME,CLASS_DESC,CREATE_DATE)
VALUES('1907H5','7月份开的H5的班级','2019-07-01');
SELECT * FROM MS_CLASS; # 查看表
ROLLBACK TO aa;
SELECT * FROM MS_CLASS; # 查看表，更改被回滚

1.4 事务操作注意事项

没有设置保存点，也可以回滚，只能回滚到事务的开始。直接使用rollback即可，但是要取消自动提交。

在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行commit操作。

因此需要显示地开启一个事务必须使用命令begin 或start transaction，或者执行命令set autocommit=0，用来禁止使用当前会话的自动提交。

一个事务被提交了（commit），则不可以回退（rollback）。

可以选择回退到的保存点--rollback to 保存点。

数据库引擎：InnoDB支持事务(mysql 5.5开始默认使用)，MyISAM不支持事务。

二、索引

2.1 概念

索引是一个"内置表"，该表的数据是对某个真实表的某个(些)字段的数据做了"排序"之后的存储形式。

作用：极大的提高查询速度。

2.2 注意：索引的优缺点

创建索引时，需要确保该索引是应用在SQL查询语句的条件(where子句的条件) --查询效率高

滥用索引会造成(insert、update、detect)效率慢。--不仅要保存数据，还有保存索引文件

2.3 索引分类

单列索引：一个索引只包含单个列，一个表可以有多个单列索引。

组合索引：一个索引包含多个列。

2.3.1 索引---普通索引 index

2.3.2 索引--唯一索引 unique

三、视图

实例

# 视图
CREATE VIEW VI_STUDENT AS  # 创建学生视图
SELECT  # 查看学生的班级名称
  S.ID,
  S.STU_NAME,
  C.CLASS_NAME
FROM
  MS_STUDENT S
  LEFT JOIN MS_CLASS C
    ON S.CLASS_ID = C.ID #条件
# 创建视图的select语句查询结果不能有重复的列名    
# 视图可以查询，但是不能CUD
SELECT * FROM VI_STUDENT;
# 视图可以提高开发效率
#create or replace 创建或更新视图
CREATE OR REPLACE VIEW VI_STUDENT AS  # 创建学生视图
SELECT  # 查看学生的班级名称
  S.ID,
  S.STU_NAME,
  C.CLASS_NAME
FROM
  MS_STUDENT S
  LEFT JOIN MS_CLASS C
    ON S.CLASS_ID = C.ID #条件

四、PL SQL

使用SQL语言编程

PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。

PL/SQL是Oracle数据库对SQL语句的扩展。

在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，

通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。

4.1 函数

实例：

#函数--FN_TEST1
DELIMITER $$
CREATE
    FUNCTION `SSMS`.`FN_TEST1`()
    RETURNS INT
DETERMINISTIC   
    BEGIN
	DECLARE max_age INT; #查询最大年龄并返回
	SELECT MAX(STU_AGE) INTO max_age FROM MS_STUDENT;
	RETURN max_age;
    END$$
DELIMITER ;

#函数FN_TEST2
# FN_TEST2--带入参，返回className
DELIMITER $$
CREATE
    FUNCTION `SSMS`.`FN_TEST2`(classId BIGINT)
    RETURNS VARCHAR(30)
    BEGIN
	DECLARE className VARCHAR(30);
	SELECT CLASS_NAME INTO className FROM MS_CLASS WHERE ID = classId;
	RETURN className;
    END$$
DELIMITER ;

# 使用函数
SELECT FN_TEST1();
DROP FUNCTION FN_TEST1;
SELECT FN_TEST2(1);

4.2 存储过程

# 存储过程
#创建存储过程 p_test();
DELIMITER $$
CREATE
    PROCEDURE `SSMS`.`p_test`()
DETERMINISTIC
    BEGIN
	SELECT * FROM MS_CLASS;
    END$$
DELIMITER ;

#创建存储过程 p_count(入参);
DELIMITER $$
CREATE
    PROCEDURE `SSMS`.`p_count`(IN classId BIGINT)
    BEGIN
	DECLARE stuCount INT;
	SELECT COUNT(ID) INTO stuCount FROM MS_STUDENT WHERE CLASS_ID=classId;
	UPDATE MS_CLASS SET STU_COUNT=stuCount WHERE ID=classId;
    END$$
DELIMITER ;

#创建存储过程p_testIO(入参,出参);
DELIMITER $$
CREATE
    PROCEDURE `SSMS`.`p_testIO`(IN stuId BIGINT,OUT className VARCHAR(20))
    BEGIN
	SELECT CLASS_NAME INTO className FROM MS_CLASS WHERE ID=
	(SELECT CLASS_ID FROM MS_STUDENT WHERE ID=stuId);
    END$$
DELIMITER ;

#调用无参的存储过程
CALL p_test(); 
#调用带入参的存储过程
CALL p_count(2); 
#调用带入参、出参的存储过程
SET @stuClass=NULL; #定义一个全局变量
CALL p_testIO(1,@stuClass);  #call存储过程
SELECT @stuClass; #查询全局变量

4.3 触发器

#触发器
#创建触发器
DELIMITER $$
USE `SSMS`$$
DROP TRIGGER /*!50032 IF EXISTS */ `trg_test`$$
CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `trg_test` AFTER INSERT ON `MS_STUDENT` 
    FOR EACH ROW BEGIN
	UPDATE MS_CLASS SET STU_COUNT=STU_COUNT+1 WHERE ID=NEW.CLASS_ID;
    END;
$$
DELIMITER ;

#触发器测试使用
INSERT INTO MS_STUDENT (CLASS_ID,STU_NAME,STU_AGE,STU_BIRTHDAY)VALUES(1,'李白',20,'1999-01-01');

五、练习

CREATE DATABASE sales;
USE sales;

# 客户表
CREATE TABLE tb_customer(
	row_id BIGINT PRIMARY KEY AUTO_INCREMENT,
	cus_no CHAR(5),
	cus_name VARCHAR(20),
	cus_address VARCHAR(20),
	cus_tel VARCHAR(20)
);
INSERT INTO tb_customer(cus_no,cus_name,cus_address,cus_tel)
VALUES('c001','杨婷','北京','010-5328953'),
('c002','李和平','上海','021-62359651'),
('c003','叶欣','成都','024-3222781'),
('c004','冯辰诚','上海','021-87235965');

# 产品表
CREATE TABLE tb_product(
	row_id BIGINT PRIMARY KEY AUTO_INCREMENT,
	pro_no CHAR(6),
	pro_name VARCHAR(20),
	pro_price DOUBLE,
	pro_stocks BIGINT
);
INSERT INTO tb_product(pro_no,pro_name,pro_price,pro_stocks)
VALUES('p0001','液晶电视',5600.00,800),
('p0002','空调',2390.00,460),
('p0003','洗衣机',3700.00,600),
('p0004','电热水器',890.00,120);

# 销售表
CREATE TABLE tb_po(
	row_id BIGINT PRIMARY KEY AUTO_INCREMENT,
	po_date DATE,
	po_cusno CHAR(5),
	po_prono CHAR(6),
	po_quantiy BIGINT
);
INSERT INTO tb_po(po_date,po_cusno,po_prono,po_quantiy)
VALUES('2007-10-27','c001','p0001',3),
('2007-11-06','c004','p0003',40),
('2007-12-27','c002','p0003',5),
('2008-3-15','c002','p0002',12),
('2008-05-02','c003','p0002',21),
('2008-05-02','c003','p0001',9),
('2008-09-21','c004','p0001',30),
('2008-11-21','c004','p0001',73);

#1)创建一自定义函数sumMoney，要求能够利用该函数计算出销售金额，
#并进行测试，利用该函数计算出每种产品(ProNo)的销售金额。
#函数部分
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION `sales`.`sumMoney`(prono CHAR(6))
    RETURNS BIGINT
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
DETERMINISTIC
    BEGIN
	DECLARE sum_money BIGINT;
	DECLARE price BIGINT;
	DECLARE quantiy BIGINT;
	SELECT pro_price INTO price FROM tb_product WHERE pro_no=prono;
	SELECT SUM(po_quantiy) INTO quantiy FROM tb_po WHERE po_prono=prono;
	#sum_money = (price*quantiy);
	RETURN (price*quantiy);
    END$$

DELIMITER ;
# 函数部分结束
# 测试
SELECT sumMoney('p0001')

#2)创建视图viewPro，要求显示每种产品的销售量和销售金额。
CREATE VIEW viewPro AS
SELECT  SUM(po_quantiy),sumMoney(po_prono) FROM tb_po GROUP BY po_prono;


#3)创建存储过程p_Pro, 要求能够根据指定的客户编号，统计客户购买每种产品的产品号、数量。
# 存储过程代码
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `sales`.`p_Pro`(cusno CHAR(5))
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
	SELECT po_cusno,po_prono,SUM(po_quantiy) FROM tb_po WHERE po_cusno=cusno GROUP BY po_prono;
    END$$

DELIMITER ;
# 存储过程代码结束
#调用存储过程
CALL p_Pro('c004')

#4)创建一个触发器t_Stocks，要求当插入销售表(tp_po)的销售记录时，
#根据销售数量(Quantity)的变化，能更新产品表(tp_product)中相应的库存数量(Stocks)。
# 触发器代码
DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `SSMS`.`trg_test` AFTER INSERT
    ON `SSMS`.`MS_STUDENT`
    FOR EACH ROW BEGIN
	UPDATE MS_CLASS SET STU_COUNT=STU_COUNT+1 WHERE ID=NEW.CLASS_ID;
    END$$
DELIMITER ;
#触发器代码结束

#测试
INSERT INTO tb_po(po_date,po_cusno,po_prono,po_quantiy)VALUES('2008-12-12','c005','p0001',10);
# 对应的p0001产品的数量减了10（po_quantiy）