SQL语句记录

-- 判断 dev_info 表是否存在 plateIDNO 字段，不存在则添加; 存在则修改字段类型
DELIMITER ??
DROP PROCEDURE IF EXISTS schema_change??
CREATE PROCEDURE schema_change()
BEGIN
IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema = DATABASE()  AND table_name = 'dev_info' AND column_name = 'plateIDNO') THEN
    ALTER TABLE dev_info ADD plateIDNO varchar(64) ;
-- ELSE    
    -- ALTER TABLE dev_info MODIFY COLUMN plateIDNO  varchar(64) ;
END IF; 
END??
DELIMITER ;


CALL schema_change();


-- 把Account表的车辆添加到dev_info表中
update dev_info inner join Account on dev_info.AccountID =Account.ID set dev_info.plateIDNO = Account.name;




-- 判断 dev_info 表是否存在 DevID 字段，不存在则添加; 存在则修改字段类型
DELIMITER ??
DROP PROCEDURE IF EXISTS schema_change??
CREATE PROCEDURE schema_change()
BEGIN
IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema = DATABASE()  AND table_name = 'dev_info' AND column_name = 'DevID ') THEN
    ALTER TABLE dev_info ADD plateIDNO varchar(40) ;
-- ELSE