sql语句自定义变量方法示例

发布时间:2019-07-17编辑:脚本学堂
有关mysql中使用sql语句自定义变量的方法与例子,mysql自定义函数的用法,掌握下mysql自定义变量以及赋值的方法,供大家学习参考。

sql语句自定义变量
 

复制代码 代码示例:

--将select列值赋予变量
select @class_id := id from t_classes where id = 2;
delete from t_student where classesid = @class_id;
delete from t_classes where id = @class_id;

--将表达式至赋予变量
select @max_limbs := max(arms+legs) from limbs;

--将含有auto_increment列的表插入新行之后的last_insert_id()的结果赋给变量
select @last_id := last_insert_id();

--如果查询返回值含有多行,最后一行的值赋予变量
select @name := thing from limbs where legs = 0;
select @name;

--如果语句没有返回任何行,变量保持先前值,如果先前没有赋值,则为null
select @name2 := thing from limbs where legs < 0;

--使用set显式为某个变量指定特定值
set @sum = 4 + 7;
select @sum;

set @sum2 := 3 + 2;
select @sum2;

--使用set将一个select结果赋给变量
set @max_limbs = (select max(arms+legs) from limbs);

--变量名不区分大小写
set @x = 1, @X = 2;
select @x, @X;

-----为查询输出行计数(sql语句自定义变量)-----------------
--linuxcmd/ target=_blank class=infotextkey>linux命令行--skip-column-names结合cat -n
mysql --skip-column-names -e "select thing, arms from limbs" cookbook | cat -n

--mysql命令行使用自定义变量
set @n = 0;
select @n := @n + 1 as rownum, thing, arms, legs from limbs;

------将mysql用作计算器-(sql语句自定义变量)----------------
select (17 + 24) / sqrt(64);
select "ABC" = "abc";
select "ABC" = "abcd";

set @daily_room_charge = 100.00;
set @num_of_nights = 3;
set @tax_percent = 8;
set @total_room_charge = @daily_room_charge * @num_of_nights;
set @tax = (@total_room_charge * @tax_percent) / 100;
set @total = @total_room_charge + @tax;
select @total;

----在unix下写shell/ target=_blank class=infotextkey>shell脚本-----------------
 

复制代码 代码示例:

--shell脚本查看mysql服务器正常运行时间
#!/bin/sh
# mysql_uptime.sh - report server uptime in seconds
mysql --skip-column-names -B -e "SHOW /*!50002 GLOBAL */ STATUS LIKE 'Uptime'"

#!/bin/sh
# mysql_uptime2.sh - report server uptime
mysql -e STATUS | grep "^Uptime"

#!/bin/sh
# mysql_uptime3.sh - report server uptime
echo STATUS | mysql | grep "^Uptime"

--mysql命令行查看mysql服务器正常运行时间
status

--win环境下安装类unix命令行环境
cygnus
uwin