mysql命令2

来源：互联网发布：网络方案的初步设计编辑：程序博客网时间：2024/05/22 07:04

系统表

存放表信息：information_schema.tables

存放字段信息：information_schema.columns

查看库db_dawn中名称包含time的字段名：

select table_name,column_name from information_schema.columns where table_schema like 'db_dawn' and COLUMN_NAME like '%time%';

删除一个库的所有表，库名为mica

SET FOREIGN_KEY_CHECKS = 0; SET @tables = NULL;SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables  FROM information_schema.tables   WHERE table_schema = 'mica'; -- specify DB name here.SET @tables = CONCAT('DROP TABLE ', @tables);PREPARE stmt FROM @tables;EXECUTE stmt;DEALLOCATE PREPARE stmt;SET FOREIGN_KEY_CHECKS = 1;

查看某个库的所有存储过程：

select `name` from mysql.proc where db = 'db_dawn' and `type` = 'PROCEDURE';

查看本库可用存储过程：

SHOW PROCEDURE STATUS;

查看某个存储过程（比如 init_tab）内容：

SHOW CREATE PROCEDURE init_tab;

使用存储过程变量+find_in_set+group_concat

delimiter //drop procedure if exists update_dawn_abcde;//create procedure update_dawn_abcde(stime_hzq varchar(19), etime_hzq varchar(19), pg_source_hzq varchar(30))begin    #使用group_concat时，拼接起来的串的长度，任务数量较多，因设置为足够大    SET @@GROUP_CONCAT_MAX_LEN = 4000000;    # @plan_done_ids_hzq 限定时间内完成+限定库别的plan    set @plan_done_ids_hzq = NULL;    select group_concat(plan_id) into @plan_done_ids_hzq    from guoke_dawn.plan    where plan_update_time between stime_hzq and etime_hzq        and plan_process_status = 3        and pg_source = pg_source_hzq; # find_in_set(pg_source, @pg_source_hzq);    #6 a005 xx总人数    update dawn_a a           inner join (select p.plan_type,                              count(distinct task_executor_id) as cnt                       from   guoke_dawn.plan p,                              guoke_dawn.section s,                              guoke_dawn.task t,                              guoke_dawn.user_work_range u                       where  p.plan_id = s.plan_id                              and s.section_id = t.section_id                              and find_in_set(p.plan_id,@plan_done_ids_hzq)                              and u.user_id = task_executor_id                              and u.task_type not in ( 10011, 10021, 10031, 10057,                                                       10072, 10075, 10078, 10175 )                       group  by p.plan_type) t                   on t.plan_type = a.plan_type    set    a.a005 = cnt;end;//delimiter ;

0 0