-
Notifications
You must be signed in to change notification settings - Fork 5
/
test.sql
60 lines (55 loc) · 1.33 KB
/
test.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- TEST CREATE TABLE
CREATE TABLE TEST02
( SN_KEY INT,
SN VARCHAR(20) COMMENT 'CMT COL SN',
PN VARCHAR(20),
MD VARCHAR(20),
DESCP VARCHAR(20),
INDEX (SN),
KEY (PN),
CONSTRAINT UNIQUE KEY(MD)
);
-- TEST ERROR SYNTAX
CREATE TABLE TEST03
( SN_KEY INT,
SN VARCHAR(20),
PN VARCHAR(20) COMMENT 'COL PN',
MD VARCHAR(20),
DESCP VARCHAR(20),
);
-- TEST PARTITION TABLE
CREATE TABLE TABLE01 (
id INT PRIMARY KEY COMMENT 'PK',
year_col INT
)
PARTITION BY RANGE (year_col) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (1999),
PARTITION p3 VALUES LESS THAN (2003)
);
-- TEST ADD COLUMNS
ALTER TABLE t1 ADD COLUMN (c2 INT,C3 VARCHAR(10));
-- 测试增加partition
ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));
-- 测试索引和约束
ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);
ALTER TABLE TEST13 ADD FOREIGN KEY(C_10) REFERENCES TEST02(SN_KEY);
ALTER TABLE TEST13 ADD UNIQUE INDEX(C);
ALTER TABLE TEST13 add PRIMARY KEY(A);
-- 测试创建proc function
delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END;//
delimiter ;
delimiter $$
CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT)
RETURNS varchar(255)
BEGIN
IF ISNULL(S) THEN
RETURN '';
END IF;
END;$$
delimiter ;