有关MySQL 常用sql操作语句

小小编

2 年前

获取数据库里所有表

SELECT TABLE_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA='数据库名'

获取表里所有字段

select column_name from information_schema.columns where 
table_schema='数据库名' and table_name='表名'

判断数据库里某个表是否存在

show tables like '表名';

存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `hp_proc_orgstructure_delete`(IN `iid` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT '级联删除'
BEGIN
    #定义变量
    declare orgid INT;   
    declare porgid INT;      
    declare cur1 cursor for select id,pid from hp_orgstructure where pid=iid;
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET orgid = null,porgid=null; 
    SET @@max_sp_recursion_depth = 10; 
    update hp_orgstructure set flag=-1 where id=iid;
    open cur1;
        fetch cur1 into orgid,porgid;
        while(orgid is not null)
            do
            call hp_proc_orgstructure_delete(orgid);
            fetch cur1 into orgid,porgid;
        end while;
    close cur1;
END