mysql实例保存查询结果到变量

2023-08-21 11:11:40

本文介绍下，一个mysql的例子，将查询到的数据结果保存到一个变量中。有需要的朋友可以参考下。

本代码演示：

将mysql查询结果保存到变量中的方法。

代码：

view source

print ?

001 mysql> CREATE TABLE Employee( //创建数据表

002 -> id int,

003 -> first_name VARCHAR(15),

004 -> last_name VARCHAR(15),

005 -> start_date DATE,

006 -> end_date DATE,

007 -> salary FLOAT(8,2),

008 -> city VARCHAR(10),

009 -> description VARCHAR(15)

010 -> );

011 Query OK, 0 rows affected (0.03 sec)

012

013 mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

014 -> values (1,'Jason', 'Martin', '19960725', '20060725', 1234.56,'Toronto', 'Programmer');

015 Query OK, 1 row affected (0.00 sec)

016

017 mysql>

018 mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

019 -> values(2,'Alison', 'Mathews', '19760321', '19860221', 6661.78,'Vancouver','Tester');

020 Query OK, 1 row affected (0.00 sec)

021

022 mysql>

023 mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

024 -> values(3,'James', 'Smith', '19781212', '19900315', 6544.78,'Vancouver','Tester');

025 Query OK, 1 row affected (0.02 sec)

026

027 mysql>

028 mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

029 -> values(4,'Celia', 'Rice', '19821024', '19990421', 2344.78,'Vancouver','Manager');

030 Query OK, 1 row affected (0.00 sec)

031

032 mysql>

033 mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

034 -> values(5,'Robert', 'Black', '19840115', '19980808', 2334.78,'Vancouver','Tester');

035 Query OK, 1 row affected (0.01 sec)

036

037 mysql>

038 mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

039 -> values(6,'Linda', 'Green', '19870730', '19960104', 4322.78,'New York', 'Tester');

040 Query OK, 1 row affected (0.00 sec)

041

042 mysql>

043 mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

044 -> values(7,'David', 'Larry', '19901231', '19980212', 7897.78,'New York', 'Manager');

045 Query OK, 1 row affected (0.00 sec)

046

047 mysql>

048 mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

049 -> values(8,'James', 'Cat', '19960917', '20020415', 1232.78,'Vancouver', 'Tester');

050 Query OK, 1 row affected (0.00 sec)

051

052 mysql>

053 mysql> select * from Employee;

054 +------+------------+-----------+------------+------------+---------+-----------+-------------+

056 +------+------------+-----------+------------+------------+---------+-----------+-------------+

057 | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |

058 | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |

059 | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |

060 | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |

061 | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |

062 | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |

063 | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |

064 | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |

065 +------+------------+-----------+------------+------------+---------+-----------+-------------+

066 8 rows in set (0.00 sec)

067

068 mysql> delimiter $$

069 mysql>

070 mysql> CREATE PROCEDURE myProc() //创建mysql存储过程

071 -> READS SQL DATA

072 -> BEGIN

073 -> DECLARE winner_id INT;

074 -> DECLARE max_employee_id INT;

075 -> DECLARE winner_name VARCHAR(70);

076 ->

077 -> SELECT MAX(id)

078 -> INTO max_employee_id

079 -> FROM employee;

080 ->

081 -> SET winner_id=FLOOR(RAND()*max_employee_id)+1;

082 ->

083 -> SELECT CONCAT_WS(' ','Employee of the week is',first_name,last_name)

084 -> FROM employee

085 -> WHERE id=winner_id;

086 -> END$$

087 Query OK, 0 rows affected (0.00 sec)

088

089 mysql> delimiter ;

090 mysql> call myProc(); //调用mysql存储过程

091 +---------------------------------------------------------------+

092 | CONCAT_WS(' ','Employee of the week is',first_name,last_name) |

093 +---------------------------------------------------------------+

094 | Employee of the week is James Smith |

095 +---------------------------------------------------------------+

096 1 row in set (0.00 sec)

097

098 Query OK, 0 rows affected (0.00 sec)

099

100 mysql> drop procedure myProc; //删除mysql存储过程

101 Query OK, 0 rows affected (0.00 sec)

102

103 mysql> drop table Employee; //删除mysql数据表

104 Query OK, 0 rows affected (0.02 sec)

本文原始链接：http://www.jbxue.com/db/11778.html

码农公寓

相关文章