视图、存储过程和Json

Json

JSON是一种轻量级的数据交换格式，全称为JavaScript Object Notation（JavaScript对象表示法）。它以易于阅读和编写的文本格式来表示结构化数据，常用于Web应用程序之间的数据传输。

JSON由键值对组成

• 对象（Object）：用花括号 {} 表示一个对象，对象由多个键值对组成，键值对之间用逗号分隔。

{
  "key1": "value1",
  "key2": "value2",
  "key3": "value3"
}

• 数组（Array）：用方括号 [] 表示一个数组，数组是由多个值组成，值之间用逗号分隔。

["value1", "value2", "value3"]

操作

插入数据

• 数组：json_array(值1，值2，…)
• 对象：json_object(键1，值1，键2，值2)

-- 向json字段插入数据
INSERT into  jsontest(username,hobby,friend) VALUES ('吊毛',JSON_ARRAY('嫖娼'),JSON_OBJECT('name','大吊毛'));

查询

• json_extract(字段名，“$.key”)
• -> 字段名->“$.key”
• json_keys(字段) 查询key

-- 查询json字段数据
SELECT JSON_EXTRACT(friend,'$.name')FROM jsontest;
SELECT friend->'$.name'FROM jsontest;
SELECT JSON_KEYS(friend) FROM jsontest;

修改

• 修改或添加字段json_set(“字段”,“$.key”,值)
• 删除json_remove(字段，key)

-- 修改json字段数据
UPDATE jsontest set friend=JSON_SET(friend, '$.name', '小吊毛')WHERE id =1;
SELECT JSON_EXTRACT(friend,'$.name')FROM jsontest;
-- 向json字段中添加属性
UPDATE jsontest set friend=JSON_SET(friend, '$.age', 23)WHERE id = 1;
-- 删除json字段中属性
UPDATE jsontest set friend=JSON_REMOVE(friend, '$.age') WHERE id=1;

函数搜索

• JSON_CONTAINS (字段，值)
• JSON_CONTAINS (字段，值，‘$.key’)

-- 函数搜索
SELECT username,hobby,friend FROM jsontest WHERE JSON_CONTAINS(hobby, '"嫖娼"');
SELECT username,hobby,friend FROM jsontest WHERE JSON_CONTAINS(friend, '"大吊毛"','$.name');

视图

• 视图概念：
  • 视图可以由数据库的一张或者多张表中的数据组成的，可以对视图中的数据进行增删改查操作，视图可以理解成一张虚拟表，当数据库表中的数据发生变化的时候，视图中的数据也跟着发生变化
• 视图优点
  • 我们可以把经常用的数据放到视图中，无需关心视图对应的数据库表的结构，表之间的关联关系，也不需要关心表之间的业务逻辑，简化开发人员的操作
  • 增加数据的安全性，根据权限对用户进行数据访问的限制，将用户的访问之间限制在视图上，不用访问真实的数据表
• 缺点：
  • 占用内存空间，并不是绝对安全只是相对安全，通过对试图修改可以对真实表中内容参数一定的影响

使用

创建视图

create view  视图名称  as  sql语句(可以是多表查询)
-- 按照jsontest表创建pv0视图
CREATE VIEW pv0 as SELECT id,username,hobby,friend from jsontest;
--  按照jsontest表的username，friend创建pv1视图
CREATE	VIEW pv1(uname,gf) as SELECT username,friend FROM jsontest;
--  按照JSON_CONTAINS查出符合要求的数据创建pv2视图
CREATE VIEW pv2 as SELECT id,username,hobby,friend FROM jsontest WHERE JSON_CONTAINS(HOBBY, '"嫖娼"');

修改视图

• create or replace view 视图名称 as sql语句
  如果存在视图名称则修改视图，如果视图名称不存在，则创建视图

-- 将jsontest中的username,friend,hobby提取出来将pv1改为username,girlfriend,hb
CREATE or REPLACE VIEW pv1 (username,girlfriend,hb) as SELECT  FROM jsontest; 
SELECT * FROM pv1;

• alter view 视图名称 as sql语句

-- 将pv1中的内容改为username
alter VIEW pv1(username) as SELECT username FROM jsontest;

插入数据

• 和普通表插入的格式一样,但是视图改变也会将影响原来的表
• insert into 视图名 （） vlues ()

-- 给pv1插入数据
INSERT into pv1(username,girlfriend,hb) VALUES('sds',JSON_OBJECT("name","a"),JSON_ARRAY("放屁"));

删除数据

• 和普通表删除数据做法一样
• delete from 视图名 where 条件

-- 删除数据
DELETE FROM pv1 where username='sds';

删除视图

• drop view if exists 视图名称;

-- 删除视图
drop view if EXISTS pv0,pv1,pv2;

存储过程

存储过程是一组预先编译的SQL语句，被命名并存储在数据库中。它们通常由数据库管理员或开发人员编写，并可以包含控制结构（例如条件、循环等）和输入/输出参数。存储过程可以像函数一样被调用，但它可能不返回值，也可以返回多个结果集。

创建存储过程

DELIMITER //
CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter_name data_type, ...)
BEGIN
    -- 存储过程的SQL语句
END //
DELIMITER ;

• procedure_name是你给存储过程起的名字；
• parameter_name是存储过程参数的名字；
• data_type是参数的数据类型。
• DELIMITER: DELIMITER是一个特殊的命令，用于改变语句分隔符的默认值。SQL语句的默认分隔符是分号(;)，这意味着在执行多个语句时，每个语句都必须以分号结束。然而，在创建存储过程语句时，可能包含了多个分号，这会导致MySQL在遇到第一个分号时就认为语句结束，从而产生错误。
• IN：表示参数是输入参数，用于接收调用存储过程时传递的值。
• OUT：表示参数是输出参数，用于将存储过程的计算结果返回给调用者。
• INOUT：表示参数既是输入参数，又是输出参数，用于接收值并返回结果

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE getname(in person_id int, out person_name VARCHAR(255))
BEGIN
	SELECT username into person_name FROM jsontest WHERE id = person_id;
END// 
DELIMITER ;

存储过程的调用
可以使用CALL语句来调用存储过程。调用存储过程时，需要提供传递给存储过程的参数。如果存储过程有输出参数，需要在调用存储过程之前声明这些参数。

格式： CALL procedure_name([parameter_value, …]);

• procedure_name是要调用的存储过程的名称；
• parameter_value是传递给存储过程的参数值。

-- 存储过程使用
Call getname(2,@name);
-- 查看存储过程返回的结果
SELECT @name;

查看存储过程

• SHOW PROCEDURE STATUS WHERE db = ‘数据库名称’;
• SHOW CREATE PROCEDURE 存储过程的名称;

-- 查看存储过程
SHOW PROCEDURE STATUS WHERE db = 'test';
SHOW CREATE PROCEDURE getname;