四、mysql操作

JSON数据的操作

CREATE TABLE IF NOT EXISTS table_json (id BIGINT auto_increment,json JSON,PRIMARY KEY(id));

ALTER TABLE table_json add COLUMN name VARCHAR(128)  GENERATED ALWAYS AS (JSON_EXTRACT(json, "$.name")) virtual;
ALTER TABLE table_json add COLUMN name VARCHAR(128)  GENERATED ALWAYS AS (JSON_EXTRACT(json, "$.name")) stored;
ALTER TABLE table_json ADD FULLTEXT INDEX idx_fulltext_name(name_store)
ALTER TABLE table_json ADD INDEX name(name); 
ALTER TABLE table_json DROP INDEX name; 
CREATE TABLE IF NOT EXISTS table_json (id BIGINT auto_increment,json JSON,name VARCHAR(128) GENERATED ALWAYS AS (json->"$.name") virtual,PRIMARY KEY(id),INDEX name_index(name));

SELECT JSON_EXTRACT(json, "$.name") from table_json;
SELECT name FROM table_json;

 外键约束

　　外键约束更新https://www.cnblogs.com/yzuzhang/p/5174720.html

sql语句

select * from a left join b on a.a=b.a  where b.a is null;#找出a表中有，b表中没有的数据
SELECT * FROM employees LEFT JOIN dept_manager ON employees.emp_no = dept_manager.emp_no WHERE dept_manager.emp_no IS NOT NULL;#找出两个表中共有的数据

按每一周查询sql

SELECT emp_no,
ADDDATE('1970-01-05',INTERVAL FLOOR(DATEDIFF(to_date,'1970-01-05')/7)*7 Day) AS start,
ADDDATE('1970-01-05',INTERVAL FLOOR(DATEDIFF(to_date,'1970-01-05')/7)*7+6 Day) AS end,
COUNT(1) as total FROM salaries GROUP BY start,end;

排名问题（分数一样的排名应该一样）

set @pre_value = NULL;
set @rank_count = 0;
SELECT id , score,CASE
	WHEN @pre_value = score THEN @rank_count
	WHEN @pre_value := score THEN @rank_count := @rank_count + 1
END AS rank;
FROM rank_table
ORDER BY score DESC;

　

触发器

#创建测试表 stu
CREATE table IF NOT EXISTS stu (
id BIGINT AUTO_INCREMENT,
name VARCHAR(50),
course varchar(50),
score int(11),
PRIMARY KEY(id),
INDEX name_key(name)
);

#创建更新的触发器
CREATE TRIGGER trg_upd_score BEFORE UPDATE ON stu FOR EACH ROW
BEGIN
IF NEW.score<0 THEN SET NEW.score = 0;
ELSEIF NEW.score>100 THEN SET NEW.score = 100;
END IF;
END


#创建插入的触发器
CREATE TRIGGER trg_insert_score BEFORE INSERT ON stu FOR EACH ROW
BEGIN
IF NEW.score<0 THEN SET NEW.score = 0;
ELSEIF NEW.score>100 THEN SET NEW.score = 100;
END IF;
END

#删除触发器
DROP TRIGGER  mydb.trg_upd_score;

#插入，修改数据 测试触发器是否生效
INSERT INTO stu (name,course,score) VALUES("刘勇","web","1000");
INSERT INTO stu (name,course,score) VALUES("test","web","-10");
UPDATE stu set score = 2000 WHERE name="刘勇";