存储过程，视图简单使用

存储过程使用

/**使用变量**/
SELECT 'helloword' INTO @hello;
SELECT @hello;


SET @goodbye='goodbyebye 886';
SELECT @goodbye


SET @number = 100/2;
SELECT @number;

/**拼接下字符串**/
CREATE PROCEDURE pinkWord() SELECT CONCAT(@first,'world');
SET @first = 'hello';
CALL pinkWord();

/**变量名以@开头,此处删除q1(),q2()发生错误。直接删除名字**/
DROP PROCEDURE IF EXISTS q1;
DROP PROCEDURE IF EXISTS q2;
CREATE PROCEDURE q1() SET @word = 'luf';
CREATE PROCEDURE q2() SELECT CONCAT('my name is ',@word);
CALL q1();
CALL q2();

/**存储过程中的注释 -- ***/
DROP PROCEDURE IF EXISTS method;
DELIMITER //
CREATE PROCEDURE method	
	(IN param INTEGER)
	BEGIN
		DECLARE var CHAR(10);
		IF param = 11 THEN
		SET var = '=11';
		ELSE
		SET var = '!=11';
		END IF;
		INSERT INTO TEMPTABLE VALUES (var);
	END //
DELIMITER ;


/**查询用户表count**/
DROP PROCEDURE IF EXISTS selectAllUserCount;
DELIMITER //
CREATE PROCEDURE selectAllUserCount	
	(OUT param INTEGER)
	BEGIN
		SELECT COUNT(*) FROM fourserver.`user`;
	END //
DELIMITER ;
SET @allUserCount;
CALL selectAllUserCount(@allUserCount);
SELECT @allUserCount;

drop PROCEDURE if EXISTS checkstr2;

/**根据条件执行查询**/
create PROCEDURE checkstr2 (in str  VARCHAR(11))
BEGIN
declare var VARCHAR(11);
	set @var = '';
	if @var = @str THEN
	select count(*) from mt_20200401;
	else
  select count(*) from mt_20200402;
	end IF;
end;
	
call checkstr2('')

视图简单使用

DROP VIEW IF EXISTS lufview;
/**新建视图
	create view 视图名字 as ...
**/
CREATE VIEW lufview AS SELECT userid,username,userphone FROM USER;
SELECT * FROM lufview;
/**修改视图也会对表中数据进行修改**/
UPDATE lufview SET username = 'sb' WHERE userid = 1;
SELECT * FROM USER WHERE userid = 1;

/**修改视图**/
ALTER	VIEW lufview AS SELECT * FROM user2;
SELECT * FROM lufview;

/**视图重命名**/
RENAME TABLE lufview TO newlufview;
SELECT * FROM newlufview;