sql工作记录

SHOW INDEX FROM AdvDoc;                显示这张表中的所有索引

CREATE INDEX IDX01_AdvDoc ON AdvDoc(F_OrderID);            添加这张表的索引

CREATE INDEX IDX01_AdvDoc ON AdvDoc(F_OrderID, F_Size_ID, F_Color_ID, F_Width, F_Height, F_Content);

SHOW INDEX FROM AdvDoc;                    显示所有索引

DROP INDEX IDX01_AdvDoc ON AdvDoc;            删除索引，根据索引名称



alter table advitem ADD PRIMARY KEY (                
`SYS_DOCUMENTID`  ASC
)                                                                                        添加主键约束，升序
alter table advitem drop primary key                删除主键约束

alter table advitem ADD PRIMARY KEY (                
`SYS_DOCUMENTID`  ASC
    );
    
    
DELETE FROM AdvColor;            删除表中的所有行；

SELECT table_name, table_type, engine
  FROM information_schema.tables
    WHERE table_schema = 'test'
  ORDER BY table_name DESC;
  
  
  
  
  mysql判断表是否存在：
   if (select table_name from `INFORMATION_SCHEMA`.`TABLES` where table_name ='AdvssItem' and TABLE_SCHEMA='oms') = NULL
  
mysql function中不能用select

show variables like 'version'        查看版本


SELECT NAME FROM mysql.proc WHERE db = 'oms'

SHOW CREATE PROCEDURE  usp_CopyTemplatePage                查看存储过程
有定义declare就要有begin end

while 的用法：
while do
end while

if:
if then;
else
 
end if
  
 while 例子：
DROP PROCEDURE if EXISTS test_while;
CREATE PROCEDURE test_while(in in_count INT)
BEGIN
DECLARE count int DEFAULT 0;
WHILE count<10    do 
set count = count +1;
end WHILE;
SELECT count;
END


return的例子：

DROP PROCEDURE IF EXISTS `sp_test_return`;

CREATE PROCEDURE `sp_test_return`(In num integer)
label_pro:
begin 
DECLARE aa INT;
if num > 3 then
   leave label_pro;
else
   select num as exeuted;
end if;
end;


临时表实例

CREATE PROCEDURE sp_test_tt(IN i_chars VARCHAR(50),OUT o_counts BIGINT)  
BEGIN  
         create temporary table if not exists tmpTable 
         (  
           objChk varchar(255) primary key,  
           ModelName varchar(50),  
           Operator varchar(500),  
           PModelName varchar(50)  
         );  
         truncate TABLE tmpTable;  -- 使用前先清空临时表。  
   
         insert into tmpTable values(i_chars,i_chars,i_chars,i_chars);  
         insert into tmpTable values(i_chars,i_chars,i_chars,i_chars); -- 语句1  
         select * from tmpTable; -- 语句2  
         select count(*) into o_counts from tmpTable; -- 语句3  
END; 

异常捕获例子
CREATE DEFINER=`abandonship`@`%` PROCEDURE `P_TestException`()  
BEGIN  
    declare _var,_err int default 0;  
    declare continue handler for sqlexception, sqlwarning, not found set _err=1;  
    insert into _t1(val1, val2) value(2012,'abandonship');  
    if _err=1 then  
        set _var = 2;  
    end if;  
      
    select case when _var = 2 then '出错了' else _var end;  
END 

limit 0;            mysql        以被用于强制 SELECT 语句返回指定的记录数。
--  set nocount on


set 要放在declare后面



判断是否存在的小例子：


DELIMITER $$
DROP PROCEDURE IF EXISTS dd;
CREATE PROCEDURE dd()
BEGIN
IF (NOT EXISTS (SELECT * FROM tm_order_goods WHERE order_sn='149507122391385')) THEN
SELECT '找不到订单149507122391385';
ELSE
SELECT '订单149507122391385已经存在';
END IF;
END$$
DELIMITER ;