1 SHOW INDEX FROM AdvDoc; 显示这张表中的所有索引
2
3 CREATE INDEX IDX01_AdvDoc ON AdvDoc(F_OrderID); 添加这张表的索引
4
5 CREATE INDEX IDX01_AdvDoc ON AdvDoc(F_OrderID, F_Size_ID, F_Color_ID, F_Width, F_Height, F_Content);
6
7 SHOW INDEX FROM AdvDoc; 显示所有索引
8
9 DROP INDEX IDX01_AdvDoc ON AdvDoc; 删除索引，根据索引名称
10
11
12
13 alter table advitem ADD PRIMARY KEY (
14 `SYS_DOCUMENTID` ASC
15 ) 添加主键约束，升序
16 alter table advitem drop primary key 删除主键约束
17
18 alter table advitem ADD PRIMARY KEY (
19 `SYS_DOCUMENTID` ASC
20 );
21
22
23 DELETE FROM AdvColor; 删除表中的所有行；
24
25 SELECT table_name, table_type, engine
26 FROM information_schema.tables
27 WHERE table_schema = 'test'
28 ORDER BY table_name DESC;
29
30
31
32
33 mysql判断表是否存在：
34 if (select table_name from `INFORMATION_SCHEMA`.`TABLES` where table_name ='AdvssItem' and TABLE_SCHEMA='oms') = NULL
35
36 mysql function中不能用select
37
38 show variables like 'version' 查看版本
39
40
41 SELECT NAME FROM mysql.proc WHERE db = 'oms'
42
43 SHOW CREATE PROCEDURE usp_CopyTemplatePage 查看存储过程
44 有定义declare就要有begin end
45
46 while 的用法：
47 while do
48 end while
49
50 if:
51 if then;
52 else
53
54 end if
55
56 while 例子：
57 DROP PROCEDURE if EXISTS test_while;
58 CREATE PROCEDURE test_while(in in_count INT)
59 BEGIN
60 DECLARE count int DEFAULT 0;
61 WHILE count<10 do
62 set count = count +1;
63 end WHILE;
64 SELECT count;
65 END
66
67
68 return的例子：
69
70 DROP PROCEDURE IF EXISTS `sp_test_return`;
71
72 CREATE PROCEDURE `sp_test_return`(In num integer)
73 label_pro:
74 begin
75 DECLARE aa INT;
76 if num > 3 then
77 leave label_pro;
78 else
79 select num as exeuted;
80 end if;
81 end;
82
83
84 临时表实例
85
86 CREATE PROCEDURE sp_test_tt(IN i_chars VARCHAR(50),OUT o_counts BIGINT)
87 BEGIN
88 create temporary table if not exists tmpTable
89 (
90 objChk varchar(255) primary key,
91 ModelName varchar(50),
92 Operator varchar(500),
93 PModelName varchar(50)
94 );
95 truncate TABLE tmpTable; -- 使用前先清空临时表。
96
97 insert into tmpTable values(i_chars,i_chars,i_chars,i_chars);
98 insert into tmpTable values(i_chars,i_chars,i_chars,i_chars); -- 语句1
99 select * from tmpTable; -- 语句2
100 select count(*) into o_counts from tmpTable; -- 语句3
101 END;
102
103 异常捕获例子
104 CREATE DEFINER=`abandonship`@`%` PROCEDURE `P_TestException`()
105 BEGIN
106 declare _var,_err int default 0;
107 declare continue handler for sqlexception, sqlwarning, not found set _err=1;
108 insert into _t1(val1, val2) value(2012,'abandonship');
109 if _err=1 then
110 set _var = 2;
111 end if;
112
113 select case when _var = 2 then '出错了' else _var end;
114 END
115
116 limit 0; mysql 以被用于强制 SELECT 语句返回指定的记录数。
117 -- set nocount on
118
119
120 set 要放在declare后面
121
122
123
124 判断是否存在的小例子：
125
126
127 DELIMITER $$
128 DROP PROCEDURE IF EXISTS dd;
129 CREATE PROCEDURE dd()
130 BEGIN
131 IF (NOT EXISTS (SELECT * FROM tm_order_goods WHERE order_sn='149507122391385')) THEN
132 SELECT '找不到订单149507122391385';
133 ELSE
134 SELECT '订单149507122391385已经存在';
135 END IF;
136 END$$
137 DELIMITER ;
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
