MySQL procedure

1
2
3
4
5
6
7
8
9
10
11

DELIMITER #
create procedure test_two1()
     begin
        declare i int default 0;
       while i < 3000 do
            INSERT into tbl_test VALUES(i,'AAAAA','BBB','asdasd','asdasdsad','asdasdasd','asdasdasd','asdasdasd','asdasdsad','asdasdasdasdsad','564564a6s4da4sd','asdasd','asdalilil','zxczxc','zxcxczxc','zxczxc','zxczxc','zxczxczxc','asdasdxczxc','asdasdasd','asdasdasd');
            set i = i + 1;
        end while;
    end #

call test_two1();

1
2
3
4
5
6
7
8
9
10
11

DROP PROCEDURE IF EXISTS proc_initData;--如果存在此存储过程则删掉
DELIMITER $    //#使用delimiter后，将不把分号当做语句结束，会将该段整个提交
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=10000 DO
        INSERT INTO data(user_id) VALUES(i);
        SET i = i+1;
    END WHILE;
END $
CALL proc_initData();

mysql> desc game;
±--------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±--------±------------±-----±----±--------±------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| type_id | int(11) | YES | | NULL | |
±--------±------------±-----±----±--------±------+
3 rows in set (0.00 sec)

mysql> desc game_type;
±----------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±----------±------------±-----±----±--------±------+
| type_id | int(11) | YES | | NULL | |
| type_name | varchar(20) | YES | | NULL | |
±----------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

mysql> select g.name, g.type_id, t.type_name from game g left join game_type t on g.type_id=t.type_id;
±-----±--------±----------+
| name | type_id | type_name |
±-----±--------±----------+
| A1 | 1 | A |
| A2 | 2 | B |
| A3 | 3 | C |
| A4 | 4 | NULL |
| A5 | 5 | NULL |
±-----±--------±----------+
5 rows in set (0.00 sec)

mysql> select g.name, g.type_id, t.type_name from game g left join game_type t on g.type_id=t.type_id where t.type_name is NOT NULL;
±-----±--------±----------+
| name | type_id | type_name |

mysql> select g.name, g.type_id, t.type_name from game g left join game_type t on g.type_id=t.type_id where isNULL(t.type_name);
±-----±--------±----------+
| name | type_id | type_name |
±-----±--------±----------+
| A4 | 4 | NULL |
| A5 | 5 | NULL |±-----±--------±----------+
2 rows in set (0.00 sec)

mysql> select g.name, g.type_id, t.type_name from game g left join game_type t on g.type_id=t.type_id where isnotNULL(t.type_name);
ERROR 1305 (42000): FUNCTION haha.isnotNULL does not exist
mysql> select g.name, g.type_id, t.type_name from game g left join game_type t on g.type_id=t.type_id where !isNULL(t.type_name);
±-----±--------±----------+
| name | type_id | type_name |±-----±--------±----------+
| A1 | 1 | A |
| A2 | 2 | B || A3 | 3 | C |
±-----±--------±----------+
3 rows in set (0.00 sec)

mysql>