SELECT INTO variable to store query result in variables.
SELECT
c1, c2, c3, ...
INTO
@v1, @v2, @v3,...
FROM
table_name
WHERE
condition;
-----------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use classicmodels;
Database changed
mysql> select * from customers limit 2 \g
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
| 103 | Atelier graphique | Schmitt | Carine | 40.32.2555 | 54, rue Royale | NULL | Nantes | NULL | 44000 | France | 1370 | 21000.00 |
| 112 | Signal Gift Stores | King | Jean | 7025551838 | 8489 Strong St. | NULL | Las Vegas | NV | 83030 | USA | 1166 | 71800.00 |
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
2 rows in set (0.00 sec)
mysql> select * from customers limit 2 \g;
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
| 103 | Atelier graphique | Schmitt | Carine | 40.32.2555 | 54, rue Royale | NULL | Nantes | NULL | 44000 | France | 1370 | 21000.00 |
| 112 | Signal Gift Stores | King | Jean | 7025551838 | 8489 Strong St. | NULL | Las Vegas | NV | 83030 | USA | 1166 | 71800.00 |
+----------------+--------------------+-----------------+------------------+------------+-----------------+--------------+-----------+-------+------------+---------+------------------------+-------------+
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> select * from customers limit 2 /g;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/g' at line 1
mysql> select * from customers limit 1 /G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> select * from customers limit 1 \g;
+----------------+-------------------+-----------------+------------------+------------+----------------+--------------+--------+-------+------------+---------+------------------------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit |
+----------------+-------------------+-----------------+------------------+------------+----------------+--------------+--------+-------+------------+---------+------------------------+-------------+
| 103 | Atelier graphique | Schmitt | Carine | 40.32.2555 | 54, rue Royale | NULL | Nantes | NULL | 44000 | France | 1370 | 21000.00 |
+----------------+-------------------+-----------------+------------------+------------+----------------+--------------+--------+-------+------------+---------+------------------------+-------------+
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select * from customers limit 1 \G;
*************************** 1. row ***************************
customerNumber: 103
customerName: Atelier graphique
contactLastName: Schmitt
contactFirstName: Carine
phone: 40.32.2555
addressLine1: 54, rue Royale
addressLine2: NULL
city: Nantes
state: NULL
postalCode: 44000
country: France
salesRepEmployeeNumber: 1370
creditLimit: 21000.00
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select phone from customers limit 10;
+-------------------+
| phone |
+-------------------+
| 40.32.2555 |
| 7025551838 |
| 03 9520 4555 |
| 40.67.8555 |
| 07-98 9555 |
| 4155551450 |
| (26) 642-7555 |
| +49 69 66 90 2555 |
| 6505555787 |
| 2125557818 |
+-------------------+
10 rows in set (0.00 sec)
mysql> select customername,phone from customers limit 10;
+------------------------------+-------------------+
| customername | phone |
+------------------------------+-------------------+
| Atelier graphique | 40.32.2555 |
| Signal Gift Stores | 7025551838 |
| Australian Collectors, Co. | 03 9520 4555 |
| La Rochelle Gifts | 40.67.8555 |
| Baane Mini Imports | 07-98 9555 |
| Mini Gifts Distributors Ltd. | 4155551450 |
| Havel & Zbyszek Co | (26) 642-7555 |
| Blauer See Auto, Co. | +49 69 66 90 2555 |
| Mini Wheels Co. | 6505555787 |
| Land of Toys Inc. | 2125557818 |
+------------------------------+-------------------+
10 rows in set (0.00 sec)
mysql> select customername,phone into @cname,@cohone from customers where SELECT ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT' at line 1
mysql> c1, c2, c3, ...
-> INTO
-> @v1, @v2, @v3,...
-> FROM
-> table_name
-> WHERE
-> condition;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'c1, c2, c3, ...
INTO
@v1, @v2, @v3,...
FROM
table_name
WHERE
cond' at line 1
mysql> select customername,phone into @cname,@cohone from customers where customernumber=103;
Query OK, 1 row affected (0.04 sec)
mysql> select @name;
+--------------+
| @name |
+--------------+
| NULL |
+--------------+
1 row in set (0.00 sec)
mysql> select @cname;
+-------------------+
| @cname |
+-------------------+
| Atelier graphique |
+-------------------+
1 row in set (0.00 sec)
mysql> select @cname,@cohone;
+-------------------+------------+
| @cname | @cohone |
+-------------------+------------+
| Atelier graphique | 40.32.2555 |
+-------------------+------------+
1 row in set (0.00 sec)
mysql> select customername,phone into @cname,@cohone from customers;
ERROR 1172 (42000): Result consisted of more than one row
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.