MYSQL插入脚本

例子1

SET @tabGuid = '01cf0fb7-cc7c-c9c4-9dc5-39dbea65dace';
set @companyGuid = '00000000-0000-0000-000000000000';
SET @tabName = 'Book';

INSERT INTO Tab 
(Guid, CompanyGuid,TabName)
VALUES
(@tabGuid , @companyGuid,@tabName)
ON DUPLICATE KEY UPDATE 
CompanyGuid=@companyGuid,tabName=@tabName

例子2

-- 流程控制
set @tabName = (SELECT COUNT(*) from Tab t where Guid ='01cf0fb7-cc7c-c9c4-9dc5-39dbea65dace2');
SELECT if (@tabName>0, 1, 2);

声明变量

一、局部变量（用于存储过程）

局部变量一般用在sql语句块中，比如存储过程的begin/end。其作用域仅限于该语句块，在该语句块执行完毕后，局部变量就消失了。declare语句专门用于定义局部变量，可以使用default来说明默认值。set语句是设置不同类型的变量，包括会话变量和全局变量。

declare var_name [, var_name]... data_type [ DEFAULT value ];

二、用户变量（用于寻常语句）

在客户端链接到数据库实例整个过程中用户变量都是有效的。

MySQL中用户变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。

第一种用法：set @num=1; 或set @num:=1; //这里要使用set语句创建并初始化变量，直接使用@num变量

第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……，

select语句一般用来输出用户变量，比如select @变量名，用于输出数据源不是表格的数据。

注意上面两种赋值符号，使用set时可以用“=”或“:=”，但是使用select时必须用“:=赋值”

用户变量与数据库连接有关，在连接中声明的变量，在存储过程中创建了用户变量后一直到数据库实例接断开的时候，变量就会消失。

在此连接中声明的变量无法在另一连接中使用。

用户变量的变量名的形式为@varname的形式。

名字必须以@开头。

声明变量的时候需要使用set语句，比如下面的语句声明了一个名为@a的变量。

set @a = 1;

声明一个名为@a的变量，并将它赋值为1，MySQL里面的变量是不严格限制数据类型的，它的数据类型根据你赋给它的值而随时变化 。（SQL SERVER中使用declare语句声明变量，且严格限制数据类型。）

我们还可以使用select语句为变量赋值。

比如：

set @name = '';
select @name:=password from user limit 0,1;#从数据表中获取一条记录password字段的值给@name变量。在执行后输出到查询结果集上面。

（注意等于号前面有一个冒号，后面的limit 0,1是用来限制返回结果的，表示可以是0或1个。相当于SQL SERVER里面的top 1）

如果直接写：select @name:=password from user;

如果这个查询返回多个值的话，那@name变量的值就是最后一条记录的password字段的值。

用户变量可以作用于当前整个连接，但当当前连接断开后，其所定义的用户变量都会消失。

用户变量使用如下（我们无须使用declare关键字对用户变量进行定义，可以直接这样使用）定义，变量名必须以@开始：

#定义
select @变量名  或者 select @变量名:= 字段名 from 表名 where 过滤语句;
set @变量名;
#赋值 @num为变量名，value为值
set @num=value;或select @num:=value;

对用户变量赋值有两种方式，一种是直接用”=”号，另一种是用”:=”号。其区别在于使用set命令对用户变量进行赋值时，两种方式都可以使用；当使用select语句对用户变量进行赋值时，只能使用”:=”方式，因为在select语句中，”=”号declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量。

begin
#Routine body goes here...
#select c as c;
declare c int default 0;
set @var1=143;  #定义一个用户变量，并初始化为143
set @var2=34;
set c=a+b;
set @d=c;
select @sum:=(@var1+@var2) as sum, @dif:=(@var1-@var2) as dif, @d as C;#使用用户变量。@var1表示变量名

set c=100;
select c as CA;
end

#在查询中执行下面语句段
call `order`(12,13);  #执行上面定义的存储过程
select @var1;  #看定义的用户变量在存储过程执行完后，是否还可以输出，结果是可以输出用户变量@var1,@var2两个变量的。
select @var2;

在执行完order存储过程后，在存储过程中新建的var1，var2用户变量还是可以用select语句输出的，但是存储过程里面定义的局部变量c不能识别。

系统变量：

系统变量又分为全局变量与会话变量。

全局变量在MySQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。

会话变量在每次建立一个新的连接的时候，由MySQL来初始化。MySQL会将当前所有全局变量的值复制一份。来做为会话变量。

（也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。）

全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。

我们可以利用

show session variables;

语句将所有的会话变量输出（可以简写为show variables，没有指定是输出全局变量还是会话变量的话，默认就输出会话变量。）如果想输出所有全局变量：

show global variables

有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的。

对于那些可以更改的系统变量，我们可以利用set语句进行更改。

系统变量在变量名前面有两个@；

如果想要更改会话变量的值，利用语句：

set session varname = value;
或者
set @@session.varname = value;

比如：

mysql> set session sort_buffer_size = 40000;
Query OK, 0 rows affected(0.00 sec)
用select @@sort_buffer_size;输出看更改后的值是什么。
如果想要更改全局变量的值，将session改成global：
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000;

不过要想更改全局变量的值，需要拥有super权限。

（注意，root只是一个内置的账号，而不是一种权限，这个账号拥有了MySQL数据库里的所有权限。任何账号只要它拥有了名为super的这个权限，就可以更改全局变量的值，正如任何用户只要拥有file权限就可以调用load_file或者into outfile，into dumpfile，load data infile一样。）

利用select语句我们可以查询单个会话变量或者全局变量的值：

select @@session.sort_buffer_size
select @@global.sort_buffer_size
select @@global.tmpdir

凡是上面提到的session，都可以用local这个关键字来代替。

比如：　　

select @@local.sort_buffer_size
local是session的近义词。

无论是在设置系统变量还是查询系统变量值的时候，只要没有指定到底是全局变量还是会话变量。都当做会话变量来处理。

比如：

set @@sort_buffer_size = 50000; 
select @@sort_buffer_size;

上面都没有指定是blobal还是session，所以全部当做session处理。

三、会话变量

服务器为每个连接的客户端维护一系列会话变量。在客户端连接数据库实例时，使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。会话变量的作用域与用户变量一样，仅限于当前连接。当当前连接断开后，其设置的所有会话变量均失效。

设置会话变量有如下三种方式更改会话变量的值：

set session var_name = value;
set @@session.var_name = value;
set var_name = value;  #缺省session关键字默认认为是session

查看所有的会话变量
show session variables;
查看一个会话变量也有如下三种方式：
select @@var_name;
select @@session.var_name;
show session variables like "%var%";

凡是上面提到的session，都可以用local这个关键字来代替。

比如：

select @@local.sort_buffer_size 
local是session的近义词。

四、全局变量

全局变量影响服务器整体操作。当服务器启动时，它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。要想更改全局变量，必须具有super权限。全局变量作用于server的整个生命周期，但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续生效，需要更改相应的配置文件。

要设置一个全局变量，有如下两种方式：

set global var_name = value; //注意：此处的global不能省略。根据手册，set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION
set @@global.var_name = value; //同上

查看所有的全局变量

show global variables;

要想查看一个全局变量，有如下两种方式：

select @@global.var_name;
show global variables like “%var%”;

不存在则插入，存在则更新

1.1 on duplicate key update

如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则执行UPDATE语句，例：

INSERT` `INTO` ``student`(```name```, `age`) ``VALUES``(``'Jack'``, 19)
 ``ON` `DUPLICATE ``KEY
 ``UPDATE` ``age`=19; ``-- If will happen conflict, the update statement is executed
 
 -- 2 row(s) affected

1.2 replace into

如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则先删除旧数据再插入最新的数据，例：

REPLACE INTO student(name, age) VALUES('Jack', 18);

-- 2 row(s) affected

这里受影响的行数是2，因为数据库中存在name='Jack'的数据，并且id的值会变成2，因为它是先删除旧数据，然后再插入数据

避免重复插入

关键字/句：insert ignore into，如果插入的数据会导致UNIQUE索引或PRIMARY KEY发生冲突/重复，则忽略此次操作/不插入数据，例：

INSERT IGNORE INTO student(name, age) VALUES('Jack', 18);

-- 0 row(s) affected

这里已经存在name='Jack'的数据，所以会忽略掉新插入的数据，受影响行数为0，表数据不变。

流程控制

常用的流程控制结构

CASE 语句

基本语法：

CASE value 
    WHEN [compare-value] THEN result 
    [WHEN [compare-value] THEN result ...] 
    [ELSE result] 
    END

或

CASE WHEN [condition] THEN result 
     [WHEN [condition] THEN result ...]
     [ELSE result] 
     END

第一种写法，表示等值比较（value 和 compare-value）。如果没有匹配，则返回 ELSE 后的结果，如果没有 ELSE 部分，则返回 NULL。

mysql> SELECT CASE 1 WHEN 1 THEN 'one' END;
+------------------------------+
| CASE 1 WHEN 1 THEN 'one' END |
+------------------------------+
| one                          |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE 2 WHEN 1 THEN 'one' END;
+------------------------------+
| CASE 2 WHEN 1 THEN 'one' END |
+------------------------------+
| NULL                         |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE 2 WHEN 1 THEN 'one' ELSE 'none' END;
+------------------------------------------+
| CASE 2 WHEN 1 THEN 'one' ELSE 'none' END |
+------------------------------------------+
| none                                     |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END AS res;
+-----+
| res |
+-----+
| two |
+-----+

第二种写法，更为灵活，可以是任意条件。

SELECT
	CASE
WHEN 2 > 1 THEN
	'greater'
WHEN 2 = 1 THEN
	'equal'
ELSE
	'less'
END;

IF 语句

基本语法：

IF(expr1, expr2, expr3)

如果 expr1 为 true （不为 0 和 NULL），则 IF() 的返回值为 expr2；否则，返回值为 expr3 。

IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

mysql> SELECT IF(1<2, 'yes ', 'no');
+-----------------------+
| IF(1<2, 'yes ', 'no') |
+-----------------------+
| yes                   |
+-----------------------+
mysql> SELECT IF(STRCMP('test','test1'), 'no', 'yes');
+-----------------------------------------+
| IF(STRCMP('test','test1'), 'no', 'yes') |
+-----------------------------------------+
| no                                      |
+-----------------------------------------+

IFNULL

基本语法：

IFNULL(expr1, expr2)

如果 expr1 为 NULL，则 IFNULL() 的返回值为 expr2；否则其返回值为 expr1 。

mysql> SELECT IFNULL(null, 6);
+-----------------+
| IFNULL(null, 6) |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT IFNULL(0, 6);
+--------------+
| IFNULL(0, 6) |
+--------------+
|            0 |
+--------------+

NULLIF

基本语法：

NULLIF(expr1, expr2)

如果 expr1 等于 expr2 成立，那么返回值为 NULL ；否则，返回值为 expr1 。

mysql> SELECT NULLIF(1, 1);
+--------------+
| NULLIF(1, 1) |
+--------------+
|         NULL |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT NULLIF(1, 2);
+--------------+
| NULLIF(1, 2) |
+--------------+
|            1 |
+--------------+

流程控制的常见用法

流程控制结构经常会结合聚合函数（如 count、sum 等）一起使用。

比如，有一张 student 表。数据如下：

id	name	class	sex	age
1	jack01	G01	male	18
2	jack02	G01	male	19
3	jack03	G02	male	19
4	lily01	G02	female	17
5	lily02	G02	female	null
6	lily03	G03	null	20
7	rose01	G01	female	null
8	rose02	G01	null	19

现在，要统计每个班级的学生人数、男生的人数、女生的人数、性别未知的人数、男生的年龄之和、女生的年龄之和。

SQL 语句至少有以下三种写法。

第一种写法：采用 CASE 语句。

SELECT
	class,
	COUNT(*) AS student_num,
	COUNT(
		CASE
		WHEN sex = 'male' THEN
			1
		ELSE
			NULL
		END
	) AS count_male,
	COUNT(
		CASE
		WHEN sex = 'female' THEN
			1
		END
	) AS count_female,
	COUNT(
		CASE
		WHEN sex is NULL THEN
			1
		END
	) AS count_secret,
	SUM(
		CASE
		WHEN sex = 'male' THEN
			age
		END
	) AS sum_male_age,
	SUM(
		CASE
		WHEN sex = 'female' THEN
			age
		END
	) AS sum_female_age
FROM
	student
GROUP BY
	class

第二种写法：采用 IF 语句。

SELECT
	class,
	COUNT(*) AS student_num,
	COUNT(IF(sex = 'male', 1, NULL)) AS count_male,
	COUNT(IF(sex = 'female', 1, NULL)) AS count_female,
	COUNT(IF(sex is NULL, 1, NULL)) AS count_secret,
	SUM(IF(sex = 'male', age, NULL)) AS sum_male_age,
	SUM(IF(sex = 'female', age, NULL)) AS sum_female_age
FROM
	student
GROUP BY
	class

第三种写法：对于 count() 函数来说，里面的 IF 语句有一种简写形式（非主流写法）。

SELECT
	class,
	COUNT(*) AS student_num,
	COUNT(sex = 'male' OR NULL) AS count_male,
	COUNT(sex = 'female' OR NULL) AS count_female,
	COUNT(sex is NULL OR NULL) AS count_secret,
	SUM(IF(sex = 'male', age, NULL)) AS sum_male_age,
	SUM(IF(sex = 'female', age, NULL)) AS sum_female_age
FROM
	student
GROUP BY
	class

上面的三种写法，得到的结果都相同。

+-------+-------------+------------+--------------+--------------+--------------+----------------+
| class | student_num | count_male | count_female | count_secret | sum_male_age | sum_female_age |
+-------+-------------+------------+--------------+--------------+--------------+----------------+
| G01   |           4 |          2 |            1 |            1 |           37 |           NULL |
| G02   |           3 |          1 |            2 |            0 |           19 |             17 |
| G03   |           1 |          0 |            0 |            1 |         NULL |           NULL |
+-------+-------------+------------+--------------+--------------+--------------+----------------+

推荐使用第二种写法，它非常简洁明了。

MySQL 中关于 count() 函数的说明：

count() 只要记录存在，就会进行计数。
count(col_name) 只有当字段 col_name 的值不为 null 时，才会进行计数。
count(1) 只要记录存在，就会进行计数，执行速度比 count() 要快。数值 1 可以用其他的任意数值替代，比如 0 或者 9 。
count(null) 的结果为 0，等同于 count(NULL) 。
count() 函数的结果不可能为 null，只会大于或等于 0。

MySQL 中关于 sum() 函数的说明：

sum(col_name) 对字段 col_name 进行求和。如果 col_name 的值为 null ，就会忽略；如果记录不存在，结果为 null ；如果所有记录的值都是 null ，结果为 null 。
sum(null) 的结果为 null 。
sum() 函数的结果可能为 null 。

加减乘除运算符的注意事项

在 MySQL 中，对 null 值进行加减乘除运算符的操作时，结果都为 null。

mysql> select 2+null;

+--------+
| 2+null |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

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

mysql> select 2*null;
+--------+
| 2*null |
+--------+
|   NULL |
+--------+
1 row in set (0.01 sec)

mysql> select 2/null;
+--------+
| 2/null |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

参考：

mysql中各种类型变量的定义以及赋值使用：https://blog.csdn.net/JQ_AK47/article/details/52087484

MySQL 中的流程控制和用法示例：https://blog.csdn.net/lamp_yang_3533/article/details/80588889

posted @ 2021-10-11 11:25 【唐】三三阅读(282) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

【唐】三三

ConFused Road, ConFused People, All In [Tang]San.

MYSQL插入脚本

声明变量

一、局部变量（用于存储过程）

二、用户变量（用于寻常语句）

三、会话变量

四、全局变量

不存在则插入，存在则更新

1.1 on duplicate key update

1.2 replace into

避免重复插入

流程控制

CASE 语句

IF 语句

IFNULL

NULLIF

流程控制的常见用法

MySQL 中关于 count() 函数的说明：

MySQL 中关于 sum() 函数的说明：

加减乘除运算符的注意事项

参考：

公告

【唐】三三

ConFused Road, ConFused People, All In [Tang]San.

MYSQL插入脚本

声明变量

一、局部变量 （用于存储过程）

二、用户变量（用于寻常语句）

三、会话变量

四、全局变量

不存在则插入，存在则更新

1.1 on duplicate key update

1.2 replace into

避免重复插入

流程控制

CASE 语句

IF 语句

IFNULL

NULLIF

流程控制的常见用法

MySQL 中关于 count() 函数的说明：

MySQL 中关于 sum() 函数的说明：

加减乘除运算符的注意事项

参考：

公告

一、局部变量（用于存储过程）