MySQL instance: Creating a temporary table in a stored procedure

The example of creating a temporary table in a stored procedure in MySQL is a good example of learning MySQL temp table operations.

Operation Steps:

Mysql>mysql>mysql> CREATE TABLE Employee (//creating normal table, id int, first_name varc HAR (), last_name VARCHAR (start_date date, end_date date,-&gt     ; Salary FLOAT (8,2), City varchar (+), description varchar (); Query OK, 0 rows affected (0.03 sec) mysql>mysql>mysql> insert into Employee (Id,first_name, last_name, start_date , End_date, salary, city, Description), values (1, ' Jason ', ' Martin ', ' 19960725 ', ' 20060 725 ', 1234.56, ' Toronto ', ' Programmer ');    Query OK, 1 row Affected (0.00 sec) mysql>mysql> INSERT into Employee (Id,first_name, last_name, start_date, End_date, Salary, city, Description), Values (2, ' Alison ', ' Mathews ', ' 19760321 ', ' 19860221 ', 6661. , ' Vancouver ', ' Tester '); Query OK, 1 row Affected (0.00 sec) mysql>mysql> INSERT into Employee (id,fiRst_name, last_name, start_date, end_date, salary, city, Description), values (3, ' James ', ' Smith ', ' 19781212 ', ' 19900315 ', 6544.78, ' Vancouver ', ' Tester ');    Query OK, 1 row Affected (0.00 sec) mysql>mysql> INSERT into Employee (Id,first_name, last_name, start_date, End_date, Salary, city, Description) and values (4, ' Celia ', ' rice ', ' 19821024 ', ' 19990421 ', 2344. , ' Vancouver ', ' Manager ');    Query OK, 1 row Affected (0.00 sec) mysql>mysql> INSERT into Employee (Id,first_name, last_name, start_date, End_date, Salary, city, Description), values (5, ' Robert ', ' Black ', ' 19840115 ', ' 19980808 ', 2334. , ' Vancouver ', ' Tester ');    Query OK, 1 row affected (0.01 sec) mysql>mysql> insert into Employee (Id,first_name, last_name, start_date, End_date, Salary, city, Description), values (6, ' Linda ', ' Green ', ' 19870730 ', ' 19960104 ', 4322. , ' New York ', ' Tester ');    Query OK, 1 row Affected (0.00 sec) mysql>mysql> INSERT into Employee (Id,first_name, last_name, start_date, End_date, Salary, city, Description), values (7, ' David ', ' Larry ', ' 19901231 ', ' 19980212 ', 7897. ' New York ', ' Manager ');    Query OK, 1 row Affected (0.00 sec) mysql>mysql> INSERT into Employee (Id,first_name, last_name, start_date, End_date, Salary, city, Description), values (8, ' James ', ' Cat ', ' 19960917 ', ' 20020415 ', 1232. , ' Vancouver ', ' Tester '); Query OK, 1 row Affected (0.00 sec) mysql>mysql> select * from Employee; Query data +------+------------+-----------+------------+------------+---------+-----------+-------------+| ID | first_name | last_name | start_date | end_date | Salary | City |    Description |+------+------------+-----------+------------+------------+---------+-----------+-------------+| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | ProgramMer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver |    Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver |    Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver |    Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver |    Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York |    Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York |    Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |+------+------------+-----------+------------+------------+---------+-----------+-------------+8 rows in Set (0.00 sec) mysql>mysql>mysql>mysql> delimiter $ $mysql >mysql> create PROCEDURE MyProc ()//Create stored procedure,    and create a temporary table----and DROP temporary table IF EXISTS employeetemp; CREATE TEMporary TABLE employeetemp As, SELECT id,start_date, from employee; end$ $Query OK, 0 rows Affected (0.00 sec) mysql>mysql> delimiter;mysql> call MyProc (); Call stored procedure query OK, 0 rows affected, 1 Warning (0.00 sec) mysql> SELECT * from employeetemp;+------+------------+| ID |    start_date |+------+------------+| 1 |    1996-07-25 | | 2 |    1976-03-21 | | 3 |    1978-12-12 | | 4 |    1982-10-24 | | 5 |    1984-01-15 | | 6 |    1987-07-30 | | 7 |    1990-12-31 | | 8 | 1996-09-17 |+------+------------+8 rows in Set (0.00 sec) mysql> drop temporary TABLE employeetemp; Delete temp Table query OK, 0 rows Affected (0.00 sec) Mysql> drop procedure MyProc; Delete stored procedure query OK, 0 rows Affected (0.00 sec) mysql>mysql>mysql>mysql>mysql> drop table Employee; Delete Plain table query OK, 0 rows Affected (0.00 sec) mysql>mysql>

/*******************************/

One, create a database

<textarea class="crayon-plain print-no" style="-moz-tab-size: 4; font-size: 12px ! important; line-height: 15px ! important; z-index: 0; opacity: 0; overflow: hidden;" readonly="" data-settings="dblclick">mysql>create database Db_proc;</textarea>

1	MySQL>create database db_proc;

Second, create a table

<textarea class="crayon-plain print-no" style="-moz-tab-size: 4; font-size: 12px ! important; line-height: 15px ! important; z-index: 0; opacity: 0; overflow: hidden;" readonly="" data-settings="dblclick">mysql>create TABLE ' proc_test ' (' id ' tinyint (4) NOT NULL auto_increment, #ID, autogrow ' username ' varchar () NOT NULL , #用户名 ' password ' varchar (not NULL), #密码 PRIMARY KEY (' id ') #主键) Engine=myisam auto_increment=50 DEFAULT Charset=utf8; #设置表引擎和字符集</textarea>

123456

mysql>CREATE TABLE 'proc_test' ( ' id ' tinyint (4 not null auto_increment,  #ID, auto-grow ' username varchar (20 not Null, #用户名 'password' varchar(not NULL) , #密码 PRIMARY KEY ('ID') #主键 ) ENGINE=MyISAM auto_increment= DEFAULT CHARSET= UTF8; #设置表引擎和字符集

Iii. Creating Stored Procedures

<textarea class="crayon-plain print-no" style="-moz-tab-size: 4; font-size: 12px ! important; line-height: 15px ! important; z-index: 0; opacity: 0; overflow: hidden;" readonly="" data-settings="dblclick">CREATE PROCEDURE mytest (in name varchar (a), in PWD varchar) #定义传入的参数 begin INSERT INTO proc_test (username, Password) values (name,pwd); #把传进来的参数name和pwd插入表中, don't forget the semicolon end; #注意这个分号别忘记了</textarea>

12345

Create procedure mytest( in name varchar(a),in pwd varchar())#定义传入的参数 begin insert into proc_test(username,password) values(name, pwd); #把传进来的参数name和pwd插入表中, don't forget the semicolon. end; #注意这个分号别忘记了

Iv. Test Call stored procedure
Usage: Call stored procedure name (parameters passed in)

Call <textarea class="crayon-plain print-no" style="-moz-tab-size: 4; font-size: 12px ! important; line-height: 15px ! important; z-index: 0; opacity: 0; overflow: hidden;" readonly="" data-settings="dblclick">Proc_test ("Heart Is Cheng", "www.ttlsa.com")</textarea>

1	Call proc_test("Heart is Cheng","www.ttlsa.com")

Username is "Cheng" in the incoming database, password "www.ttlsa.com"

V. View data in the database that has no affiliation

<textarea class="crayon-plain print-no" style="-moz-tab-size: 4; font-size: 12px ! important; line-height: 15px ! important; z-index: 0; opacity: 0; overflow: hidden;" readonly="" data-settings="dblclick">SELECT * from proc_test where username= ' absolute heart is Cheng '; #如果有内容说明成功了</textarea>

1	SELECT * from proc_test where username= ' absolute Heart is Cheng '; #如果有内容说明成功了

Vi. Deleting stored procedures
Drop procdure stored procedure name;

MySQL instance: creating a temporary table in a stored procedure