mySql学习笔记（三）

1.视图

定义：虚拟表，多个表提取数据的集合；

用途：数据自由组合；

1.1 创建视图

-- 创建视图
CREATE VIEW v1
AS
SELECT * FROM tb1

-- 查看视图
SELECT * FROM v1

1.2 更新视图

注：更新视图的值，基表的值也会随之更新；修改基表的值，同理；

UPDATE v1 SET name ='CHENJIE' WHERE `name` ='JIE'

1.3 显示视图列结构

DESC v1

1.4 限制通过视图向基表写入数据

当向视图写入不满足where条件的数据，结果如下

1.5 替换视图

新视图数据为 SELECT NOW();

-- 删除已经存在的同名视图，创建新的视图
CREATE OR REPLACE VIEW v3
AS
SELECT NOW()

1.6 修改视图结构

ALTER VIEW v3
	AS
SELECT * FROM tb1

1.7 删除视图

-- 没有v4会报错
DROP VIEW v4
-- 没有视图v4 也不会报错
DROP VIEW IF EXISTS v4

2. 存储过程

2.1什么是存储过程

用途：创建一系列操作批量运行；

定义：将多个SQL语句组合成一个只需要使用命令“CALL xx” 就能执行的集合；“存储”（stored）表示保存，“过程”（procedure）表示步骤；如下图：

2.2 创建存储过程

delimiter //
CREATE PROCEDURE proc1()
BEGIN
	SELECT * FROM tb1;
	SELECT * FROM tb1a;
END
//

2.3 使用存储过程

CALL proc1

2.4 显示存储过程内容

SHOW CREATE PROCEDURE proc1;

2.5 删除存储过程

DROP PROCEDURE proc1;

3. 存储函数

3.1 什么是存储函数

定义：自定义函数，同javaScript

存储过程用法和存储过程基本相同，不同点：存储函数在执行后会返回一个值；

 3.2 创建存储函数

delimiter //
CREATE FUNCTION calc_weight(height INT) RETURNS DOUBLE
BEGIN
-- 定义变量
DECLARE result DOUBLE
result = height * height * 22 / 10000;
RETURN result
END
//
delimiter;

3.3 显示和删除储存函数

-- 删除
DROP FUNCTION calc_weight;

-- 显示存储函数的内容
SHOW CREATE FUNCTION calc_weight;

4. 触发器

4.1 什么是触发器

定义：触发器（trigger）对表执行操作后触发执行其他命令的机制；

当执行INSERT、UPDATE和DELETE等命令时，作为触发器提前设置好的操作也会被执行。例如，创建一个触发器，当某表的记录发生更新时，就以此为契机将更新的内容记录到另一个表中，如下图：

注：触发器也常作为处理的记录或者处理失败时的备份使用。

4.2 创建触发器

触发器会在对表执行INSERT、UPDATE和DELETE等命令之前（BEFORE）或之后（AFTER）被调用和执行；

delimiter //
CREATE TRIGGER tr1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
INSERT INTO tb_from VALUES(OLD.empid,OLD.name,OLD.age,OLD.remark);
END
//

4.3 确认触发器

SHOW TRIGGERS

4.4 删除触发器

DROP TRIGGER tr1;

5. 事务

定义：将一系列操作作为单个逻辑工作单元处理；

使用事务之前，先了解一下存储引擎；

5.1 什么是存储引擎

MySql的功能大致分为两种，一个是连接客户端和提前检查SQL语句内容的功能，即数据库处理的“前台”部分；另一个是执行SQL语句,完成查询和文件操作等工作的功能，即“后台”部分；这个后台称为存储引擎。如下图：

5.2 存储类型的种类

 5.3 修改存储引擎

-- 将存储引擎修改为 MyISAM
ALTER TABLE tb1a ENGINE = MyISAM

5.4 确认存储引擎

SHOW CREATE TABLE tb1a

5.5 什么是事务

定义：将多个操作作为单个逻辑工作单元处理的功能称为事务（transaction）。将事务开始之后的处理结果反映到数据库中的操作称为提交（commit），不反映到数据库中而是恢复为原来状态的操作称为回滚（rollback）。

举例：a给b 转账10万元，有两个步骤，首先从a的账户扣除10万元，然后给b的账户增加10万元；

假设a的账户扣除10万元后，b的账户并没有增加10万元，a的10万元不翼而飞，这种情况绝对不允许。因此，“扣除10万元”和“增加10万元”的操作应该作为一个不可分割的整体来处理，如果过程中失败就恢复原状（回滚）。

注意：DROP命令不能执行回滚操作；

5.6 开启事务

-- 开启事务
START TRANSACTION

-- 删除tb1a表数据
DELETE FROM tb1a

-- 回滚(恢复数据）
ROLLBACK

-- 在上面的示例中如果执行“COMMIT;”代替“ROLLBACK;”，
-- 删除记录的结果就会提交（反映）到数据库中，所有的记录都会被永久删除。
COMMIT;

注：当执行“ROLLBACK;”时，事务会被关闭。下次再练习时，务必使用“START TRANSACTION;”开启事务。

5.6 事务使用范围

启用事务之后，并不是所有操作都可以通过回滚复原。例如，下面这些命令就会被自动提交。

DROP DATABASE

DROP TABLE

DROP VIEW

ALTER TABLE

即使开启了事务也有不能复原的情况，这一点需要注意；

6.使用文件进行交互

6.1 从文本文件（CSV）中读取数据（导入）

LOAD DATA INFILE 'D:/csv/tb1a.csv' 
INTO TABLE tb1a 
FIELDS TERMINATED BY ',';

出现错误

查找资料解决

1.首先在MySQL中输入 show variables like ‘%secure%’;查看 secure-file-priv 当前的值是什么

有些人是null，有些人是默认路径。null代表不能导出，默认路径代表只能在这个路径导出，我的是默认路径。

 2.找到配置路径修改文件导入的地址

 修改my.ini的配置文件，更改secure-file-priv 

 3. 重启MySQL服务

windows + r 输入services.msc 打开服务，找到MySQL服务右击重新启动

4.重新执行（成功）

-- tb1a.csv在数据库所在路径
LOAD DATA INFILE 'tb1a.csv' 
INTO TABLE tb1a
FIELDS TERMINATED BY ',';

 tb1a.csv

 

 查询结果

 

 6.2 导出文件

SELECT * INTO OUTFILE 'D:/csv/out.csv' 
FIELDS TERMINATED 
BY ',' FROM tb1