未完,持续更新中...

1. 行去重

show databases;

create database test;

use test;

show tables;

create table employe(
    id int unsigned auto_increment,
    name varchar(20) not null,
    sex tinyint(1) not null,
    age int(100) not null,
    primary key(id)
)ENGINE=InnoDB default charset=utf8;

alter table test.employe engine=InnoDB;

insert into employe(name, sex, age) values("wy", 1, 18);
insert into employe(name, sex, age) values("wy", 1, 18);
insert into employe(name, sex, age) values("sg", 0, 20);
insert into employe(name, sex, age) values("sg", 0, 20);
insert into employe(name, sex, age) values("ws", 1, 28);

select * from employe;

delete from employe where id not in (
    select t.id from ( 
        select name, id, count(*) from employe group by name) as t);

2. 动态视图

# shell执行mysql脚本 mysql -uroot -p123456 -e "source /root/temp.sql" 

# 删除已存在表
drop table if exists employe;

# 建测试表
create table employe(
    id int unsigned auto_increment,
    name varchar(20) not null,
    sex tinyint(1) not null,
    age int(100) not null,
    primary key(id)
)ENGINE=InnoDB default charset=utf8;

# 删除已存在函数
drop function if exists getTime;

# 创建函数,获取时间
create function getTime()
    returns date
    return @mtime;
    
# 设置返回时间
set @mtime=now();

# 删除已存在存储过程
drop procedure if exists while_insert;
# create procedure if not exists while_insert

delimiter #
create procedure while_insert()
begin
    declare i int default 0;
    while i < 10 do
        insert into employe(name, sex, age, ctime) values("wy", 1, 18, date_sub(curdate(), interval i-5 day));
        set i = i+1;
    end while;
end #

# 调用存储过程,循环插入测试数据
delimiter ;
call while_insert();

# 设置表明
set @tableName=concat("employe",date_format(now(),'%Y%m%d'));

# 设置 删除已存在视图的语句
set @DV=concat("drop view if exists ",@tableName,";");

# 执行 删除已存在视图的语句
prepare DV from @DV;
execute DV;

# 设置 设置动态创建视图的语句
set @STMT=concat("create view ",@tableName," as select * from employe where str_to_date(ctime, '%Y-%m-%d') >= getTime();");

# 执行 创建视图的语句
prepare STMT from @STMT; 
execute STMT;

# 测试查询视图
set @SV=concat("select * from ",@tableName,";");
prepare SV from @SV;
execute SV;