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