mysql> create table if not exists `my_datas`(
`id` int(20) not null auto_increment comment '數(shù)據(jù)id',
`name` varchar(30) default null comment '姓名',
`address` varchar(45) default null comment '地址',
`time` datetime default null comment '創(chuàng)建時(shí)間',
primary key(`id`)
)engine=innodb auto_increment=1 default charset=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.33 sec)

mysql> show columns from `my_datas`;
+---------+-------------+------+-----+-------------------+-------------------+
| Field   | Type        | Null | Key | Default           | Extra             |
+---------+-------------+------+-----+-------------------+-------------------+
| id      | int         | NO   | PRI | NULL              | auto_increment    |
| name    | varchar(30) | YES  |     | NULL              |                   |
| address | varchar(45) | YES  |     | NULL              |                   |
| time    | datetime    | YES  |     | NULL              | DEFAULT_GENERATED |
+---------+-------------+------+-----+-------------------+-------------------+
4 rows in set (0.00 sec)

mysql> delimiter //    #定義結(jié)束符
mysql> drop procedure if exists addMyData;
    -> create procedure addMyData()  #創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，名為：addMyData
    -> begin
    -> declare num int;
    -> set num =1;
    -> while num <=50   #插入50條數(shù)據(jù)
    -> do
    -> insert into `my_datas`(id,name,address,time) 
    -> values(null,concat('數(shù)據(jù)_',num,'號(hào)'),concat('北京四 合院',round(rand()*10),'號(hào)'),concat(current_timestamp()));  #concat函數(shù)拼接信息
    -> set num =num +1;
    -> end
    -> while;
    -> end //;
Query OK, 0 rows affected, 1 warning (0.03 sec)
 
mysql> delimiter;　　#將語(yǔ)句的結(jié)束符號(hào)恢復(fù)為分號(hào)

mysql> call addMyData();
Query OK, 1 row affected (0.58 sec)
 
mysql> select * from `my_datas`;
+----+--------------+----------------------+---------------------+
| id | name         | address              | time                |
+----+--------------+----------------------+---------------------+
|  1 | 數(shù)據(jù)_1號(hào)     | 北京四合院3號(hào)        | 2022-08-24 14:21:17 |
|  2 | 數(shù)據(jù)_2號(hào)     | 北京四合院8號(hào)        | 2022-08-24 14:21:17 |
|  3 | 數(shù)據(jù)_3號(hào)     | 北京四合院4號(hào)        | 2022-08-24 14:21:17 |
|  4 | 數(shù)據(jù)_4號(hào)     | 北京四合院3號(hào)        | 2022-08-24 14:21:17 |
|  5 | 數(shù)據(jù)_5號(hào)     | 北京四合院3號(hào)        | 2022-08-24 14:21:17 |
|  6 | 數(shù)據(jù)_6號(hào)     | 北京四合院7號(hào)        | 2022-08-24 14:21:17 |
|  7 | 數(shù)據(jù)_7號(hào)     | 北京四合院7號(hào)        | 2022-08-24 14:21:17 |
|  8 | 數(shù)據(jù)_8號(hào)     | 北京四合院5號(hào)        | 2022-08-24 14:21:17 |
|  9 | 數(shù)據(jù)_9號(hào)     | 北京四合院1號(hào)        | 2022-08-24 14:21:17 |
| 10 | 數(shù)據(jù)_10號(hào)    | 北京四合院1號(hào)        | 2022-08-24 14:21:17 |
| 11 | 數(shù)據(jù)_11號(hào)    | 北京四合院3號(hào)        | 2022-08-24 14:21:17 |
| 12 | 數(shù)據(jù)_12號(hào)    | 北京四合院1號(hào)        | 2022-08-24 14:21:17 |
| 13 | 數(shù)據(jù)_13號(hào)    | 北京四合院6號(hào)        | 2022-08-24 14:21:17 |
| 14 | 數(shù)據(jù)_14號(hào)    | 北京四合院8號(hào)        | 2022-08-24 14:21:17 |
| 15 | 數(shù)據(jù)_15號(hào)    | 北京四合院3號(hào)        | 2022-08-24 14:21:17 |
| 16 | 數(shù)據(jù)_16號(hào)    | 北京四合院9號(hào)        | 2022-08-24 14:21:17 |
| 17 | 數(shù)據(jù)_17號(hào)    | 北京四合院7號(hào)        | 2022-08-24 14:21:17 |
| 18 | 數(shù)據(jù)_18號(hào)    | 北京四合院8號(hào)        | 2022-08-24 14:21:17 |
| 19 | 數(shù)據(jù)_19號(hào)    | 北京四合院1號(hào)        | 2022-08-24 14:21:17 |
| 20 | 數(shù)據(jù)_20號(hào)    | 北京四合院9號(hào)        | 2022-08-24 14:21:17 |
| 21 | 數(shù)據(jù)_21號(hào)    | 北京四合院2號(hào)        | 2022-08-24 14:21:17 |
| 22 | 數(shù)據(jù)_22號(hào)    | 北京四合院2號(hào)        | 2022-08-24 14:21:17 |
| 23 | 數(shù)據(jù)_23號(hào)    | 北京四合院3號(hào)        | 2022-08-24 14:21:17 |
| 24 | 數(shù)據(jù)_24號(hào)    | 北京四合院10號(hào)       | 2022-08-24 14:21:17 |
| 25 | 數(shù)據(jù)_25號(hào)    | 北京四合院1號(hào)        | 2022-08-24 14:21:17 |
| 26 | 數(shù)據(jù)_26號(hào)    | 北京四合院5號(hào)        | 2022-08-24 14:21:17 |
| 27 | 數(shù)據(jù)_27號(hào)    | 北京四合院1號(hào)        | 2022-08-24 14:21:17 |
| 28 | 數(shù)據(jù)_28號(hào)    | 北京四合院3號(hào)        | 2022-08-24 14:21:17 |
| 29 | 數(shù)據(jù)_29號(hào)    | 北京四合院10號(hào)       | 2022-08-24 14:21:17 |
| 30 | 數(shù)據(jù)_30號(hào)    | 北京四合院10號(hào)       | 2022-08-24 14:21:17 |
| 31 | 數(shù)據(jù)_31號(hào)    | 北京四合院0號(hào)        | 2022-08-24 14:21:17 |
| 32 | 數(shù)據(jù)_32號(hào)    | 北京四合院2號(hào)        | 2022-08-24 14:21:17 |
| 33 | 數(shù)據(jù)_33號(hào)    | 北京四合院8號(hào)        | 2022-08-24 14:21:17 |
| 34 | 數(shù)據(jù)_34號(hào)    | 北京四合院6號(hào)        | 2022-08-24 14:21:17 |
| 35 | 數(shù)據(jù)_35號(hào)    | 北京四合院5號(hào)        | 2022-08-24 14:21:17 |
| 36 | 數(shù)據(jù)_36號(hào)    | 北京四合院7號(hào)        | 2022-08-24 14:21:17 |
| 37 | 數(shù)據(jù)_37號(hào)    | 北京四合院1號(hào)        | 2022-08-24 14:21:17 |
| 38 | 數(shù)據(jù)_38號(hào)    | 北京四合院3號(hào)        | 2022-08-24 14:21:17 |
| 39 | 數(shù)據(jù)_39號(hào)    | 北京四合院3號(hào)        | 2022-08-24 14:21:17 |
| 40 | 數(shù)據(jù)_40號(hào)    | 北京四合院6號(hào)        | 2022-08-24 14:21:17 |
| 41 | 數(shù)據(jù)_41號(hào)    | 北京四合院9號(hào)        | 2022-08-24 14:21:17 |
| 42 | 數(shù)據(jù)_42號(hào)    | 北京四合院7號(hào)        | 2022-08-24 14:21:17 |
| 43 | 數(shù)據(jù)_43號(hào)    | 北京四合院9號(hào)        | 2022-08-24 14:21:17 |
| 44 | 數(shù)據(jù)_44號(hào)    | 北京四合院5號(hào)        | 2022-08-24 14:21:17 |
| 45 | 數(shù)據(jù)_45號(hào)    | 北京四合院8號(hào)        | 2022-08-24 14:21:17 |
| 46 | 數(shù)據(jù)_46號(hào)    | 北京四合院3號(hào)        | 2022-08-24 14:21:17 |
| 47 | 數(shù)據(jù)_47號(hào)    | 北京四合院1號(hào)        | 2022-08-24 14:21:17 |
| 48 | 數(shù)據(jù)_48號(hào)    | 北京四合院7號(hào)        | 2022-08-24 14:21:17 |
| 49 | 數(shù)據(jù)_49號(hào)    | 北京四合院10號(hào)       | 2022-08-24 14:21:17 |
| 50 | 數(shù)據(jù)_50號(hào)    | 北京四合院9號(hào)        | 2022-08-24 14:21:17 |
+----+--------------+----------------------+---------------------+
50 rows in set (0.00 sec)

mysql> delimiter !!
mysql> create procedure delete_data(in ids int)  #定義一個(gè)輸入的參數(shù)
    -> begin
    -> delete from `my_datas` where id=ids;
    -> end !!
Query OK, 0 rows affected (0.05 sec)
mysql> delimiter ;

mysql> call delete_data(3);  #輸入指定參數(shù)
   
Query OK, 1 row affected (0.04 sec)
 
mysql> select * from `my_datas` limit 3;
 
+----+-------------+---------------------+---------------------+
| id | name        | address             | time                |
+----+-------------+---------------------+---------------------+
|  1 | 數(shù)據(jù)_1號(hào)    | 北京四合院3號(hào)       | 2022-08-24 14:21:17 |
|  2 | 數(shù)據(jù)_2號(hào)    | 北京四合院8號(hào)       | 2022-08-24 14:21:17 |
|  4 | 數(shù)據(jù)_4號(hào)    | 北京四合院3號(hào)       | 2022-08-24 14:21:17 |
+----+-------------+---------------------+---------------------+
3 rows in set (0.00 sec)
 

mysql> create procedure sums(a int ,b int ,out c int)
    -> begin
    -> set c = a+b;
    -> end //
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;

mysql> call sums(11,25,@s);
Query OK, 0 rows affected (0.01 sec)
mysql> select @s;  #正常輸出c的值
+------+
| @s   |
+------+
|   36 |
+------+

mysql> delimiter //
mysql> create procedure inout_test(inout test int)
    -> begin
    -> select test;
    -> set test=100;
    -> select test;
    -> end
    -> //
Query OK, 0 rows affected (0.07 sec)
 
mysql> delimiter ;

mysql> set @test=52
    -> ;
Query OK, 0 rows affected (0.00 sec)
 
mysql> call inout_test(@test);
+------+
| test |
+------+
|   52 |
+------+
1 row in set (0.00 sec)
 
+------+
| test |
+------+
|  100 |
+------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
#調(diào)用了inout_param存儲(chǔ)過(guò)程，接受了輸入的參數(shù)，也輸出參數(shù)，改變了變量
mysql> select @test;
+-------+
| @test |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

mysql > SELECT '黎治躍在內(nèi)卷' into @l;  
Query OK, 0 rows affected (0.00 sec)
mysql > SELECT @l;  
+-------------+  
|   @l        |  
+-------------+  
| 黎治躍在內(nèi)卷 |  
+-------------+  
1 row in set (0.00 sec)
 
mysql> set @z='做個(gè)人吧，黎治躍';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @z;
+--------------------------+
| @z                       |
+--------------------------+
| 做個(gè)人吧，黎治躍         |
+--------------------------+
1 row in set (0.00 sec)
 
mysql> SET @y=5+2+52; 
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @y;
+------+
| @y   |
+------+
|   59 |
+------+
1 row in set (0.00 sec)

mysql> create procedure see() select concat(@lzy,'Java全能選手');
Query OK, 0 rows affected (0.02 sec)
 
mysql> set @lzy ='黎治躍：';
Query OK, 0 rows affected (0.00 sec)
 
mysql> call see();
+---------------------------------+
| concat(@lzy,'Java全能選手')     |
+---------------------------------+
| 黎治躍：Java全能選手            |
+---------------------------------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE p1()   SET @last_procedure='l1';  
Query OK, 0 rows affected (0.02 sec)
 
mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);  
Query OK, 0 rows affected (0.03 sec)
 
mysql> CALL p1( );  
mysql> CALL p2( );  
+-----------------------------------------------+  
| CONCAT('Last procedure was ',@last_proc       |  
+-----------------------------------------------+  
| Last procedure was l1                         |  
+-----------------------------------------------+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)

mysql > delimiter //  
mysql > create prcedure test2(in s int)  
     -> begin 
     -> declare num int;  
     -> set num=s+1;  
     -> if num=0 then 
     -> insert into `new_table` values(555);  
     -> end if;  
     -> if s=0 then 
     -> update `new_table` set s1=s1+1;  
     -> else 
     -> update `new_table` set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
Query OK, 0 rows affected (0.07 sec)
 
mysql > delimiter ;

mysql > delimiter //  
mysql > create procedure test(in sb int)  
     -> begin 
     -> declare num int;  
     -> set num=sb+1;  
     -> case num  
     -> when 0 then   
     -> insert into `new_table` values(23);  
     -> when 1 then   
     -> insert into `new_table` values(24);  
     -> else   
     -> insert into `new_table` values(25);  
     -> end case;  
     -> end;  
     -> //  
Query OK, 0 rows affected (0.06 sec)
 
mysql > delimiter ; 

mysql > delimiter //  
mysql > create procedure test()  
     -> begin 
     -> declare num int;  
     -> set num=0;  
     -> while num<6 do  
     -> insert into `new_tables` values(num);  
     -> set num=num+1;  
     -> end while;  
     -> end;  
     -> //  
Query OK, 0 rows affected (0.03 sec)
 
mysql > delimiter ;

mysql > delimiter //  
mysql > create procedure test()  
     -> begin   
     -> declare num int;  
     -> set num=0;  
     -> repeat  
     -> insert into `new_table` values(num);  
     -> set num=num+1;  
     -> until num>=5  #循環(huán)條件
     -> end repeat;  
     -> end;  
     -> //  
 
Query OK, 0 rows affected (0.04 sec)
 
mysql > delimiter ;

mysql > delimiter //  
mysql > create procedure test5()  
     -> begin 
     -> declare num int;  
     -> set num=0;  
     -> LOOP1:loop  
     -> insert into `new_table` values(num);  
     -> set num=num+1;  
     -> if num >=5 then 
     -> leave LOOP1;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
Query OK, 0 rows affected (0.04 sec)
 
mysql > delimiter ;

mysql> show columns from user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(30) | YES  |     | NULL    |                |
| address  | varchar(45) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)
 
mysql> alter table user add time datetime; #添加一個(gè)time字段
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
#在address后面添加一個(gè)sex字段
mysql> alter table user add sex tinyint(1) after address;Query OK, 0 rows affected, 1 warning (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 1
 
mysql> show columns from user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(30) | YES  |     | NULL    |                |
| address  | varchar(45) | YES  |     | NULL    |                |
| sex      | tinyint(1)  | YES  |     | NULL    |                |
| time     | datetime    | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

#使用change僅修改字段名
mysql> alter table user change address address varchar(40); 
Query OK, 499 rows affected (0.38 sec)
Records: 499  Duplicates: 0  Warnings: 0
 
#使用modify僅修改字段屬性
mysql> alter table user modify address varchar(45);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
#使用change同時(shí)修改字段名和字段屬性
mysql> alter table user change address u_address varchar(40));
Query OK, 499 rows affected (0.26 sec)
Records: 499  Duplicates: 0  Warnings: 0
 
#查看更改后的表結(jié)構(gòu)
mysql> show columns from user;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int         | NO   | PRI | NULL    | auto_increment |
| username  | varchar(30) | YES  |     | NULL    |                |
| u_address | varchar(40) | YES  |     | NULL    |                |
| sex       | tinyint(1)  | YES  |     | NULL    |                |
| time      | datetime    | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> show table status like 'user' \G;
*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 499
 Avg_row_length: 131
    Data_length: 65536
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 500
    Create_time: 2022-08-24 17:32:27
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.03 sec)
 
ERROR: 
No query specified