前端小册

Appearance

On this page

自定义变量和语句结束分隔符 ​

存储程序 ​

可以封装一些语句

  • 存储例程
    • 存储函数
    • 存储过程
  • 触发器
  • 事件

自定义变量 ​

变量的定义

sql
mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)

mysql>

查询

sql
mysql> select @a;
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

变量赋值

sql
mysql> set @a=@b;
Query OK, 0 rows affected (0.00 sec)

mysql> select @b;
+------------+
| @b         |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql>

将某个查询的结果赋值给一个变量，前提是这个查询的结果只有一个值

sql
mysql> set @a=(select n1 from t1 limit 1);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql>

也可以使用另一种方式

sql
mysql> select n1 from t1 limit 1 into @b;
Query OK, 1 row affected (0.00 sec)

mysql> select @b;
+------+
| @b   |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql>

一条查询记录有多个列

sql
mysql> select m1,n1 from t1 limit 1 into @b,@a;
Query OK, 1 row affected (0.00 sec)

mysql> select @b, @a;
+------+------+
| @b   | @a   |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

mysql>

语句结束分隔符 ​

多条语句同时执行

sql
mysql> select * from t1 limit 1;select * from t2 limit 1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

mysql>

使用 delimiter 自定义语句分隔符

sql
mysql> delimiter $
mysql> select * from t1 limit 1;
    -> select * from t2 limit 1;
    -> $
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.01 sec)

mysql>

这个只是为了方便我们一次性输入多个语句，在输入完成之后最好还是改回我们常用的分号;

sql
mysql> delimiter ;
mysql>