Executing SQL Statements Interactively
[root@MaxwellDBA ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 184
Server version: 8.0.26 Source distribution
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> CREATE USER 'cbuser'@'localhost' IDENTIFIED BY 'cbpass';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT PROCESS ON *.* to `cbuser`@`localhost` ;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
[root@MaxwellDBA ~]#
[root@MaxwellDBA ~]# mysql -h localhost -u cbuser -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 185
Server version: 8.0.26 Source distribution
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> show databases;
+--------------------+
| Database |
+--------------------+
| cookbook |
| information_schema |
+--------------------+
2 rows in set (0.01 sec)
mysql> use cookbook;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_cookbook |
+-----------------------+
| actors |
| adcount |
| al_winner |
| app_log |
| artist |
| book_authors |
| book_vendor |
| booksales |
| catalog_list |
| cd |
| city |
| color |
| cow_color |
| cow_order |
| date_val |
| datetbl |
| datetime_val |
| die |
| doremi |
| drawing |
| driver_log |
| expt |
| formula1 |
| goods_characteristics |
| goods_shops |
| groceries |
| groceries_order_items |
| hitcount |
| hitlog |
| hostip |
| hostname |
| housewares |
| housewares2 |
| housewares3 |
| housewares4 |
| httpdlog |
| httpdlog2 |
| hw_category |
| image |
| ingredient |
| insect |
| inv_item |
| invoice |
| item |
| limbs |
| mail |
| marathon |
| mark_log |
| metal |
| money |
| movies |
| movies_actors |
| movies_actors_link |
| name |
| news |
| newsstaff |
| numbers |
| obs |
| occasion |
| painting |
| passtbl |
| passwd |
| patients |
| perl_session |
| person |
| php_session |
| phrase |
| player_stats |
| player_stats2 |
| poi |
| poll_vote |
| profile |
| profile_contact |
| rainfall |
| rand_names |
| rank |
| ranks |
| reviews |
| roster |
| ruby_session |
| sales_region |
| sales_tax_rate |
| sales_volume |
| sibling |
| standings1 |
| standings2 |
| states |
| str_val |
| sundays |
| testscore |
| testscore_withmisses |
| testscore_withmisses2 |
| time_val |
| tmp |
| tomcat_role |
| tomcat_session |
| tomcat_user |
| top_names |
| trip_leg |
| trip_log |
| tsdemo |
| weatherdata |
| weekday |
+-----------------------+
103 rows in set (0.00 sec)
mysql> select * from limbs;
+--------------+------+------+
| thing | legs | arms |
+--------------+------+------+
| armchair | 4 | 2 |
| centipede | 99 | 0 |
| fish | 0 | 0 |
| human | 2 | 2 |
| insect | 6 | 0 |
| Peg Leg Pete | 1 | 2 |
| phonograph | 0 | 1 |
| space alien | NULL | NULL |
| squid | 0 | 10 |
| table | 4 | 0 |
| tripod | 3 | 0 |
+--------------+------+------+
11 rows in set (0.00 sec)
mysql> select NOW();
+---------------------+
| NOW() |
+---------------------+
| 2022-10-13 17:04:33 |
+---------------------+
1 row in set (0.00 sec)
mysql> select
-> NOW()\g
+---------------------+
| NOW() |
+---------------------+
| 2022-10-13 17:05:00 |
+---------------------+
1 row in set (0.00 sec)
mysql> show full columns from limbs like 'thing'\G
*************************** 1. row ***************************
Field: thing
Type: varchar(20)
Collation: utf8mb4_0900_ai_ci
Null: NO
Key: PRI
Default: NULL
Extra:
Privileges: select,insert,update,references
Comment:
1 row in set (0.00 sec)
mysql>
2.Controlling mysql Output Destination and Format.
Producing tabular or tab-delimited output
[root@MaxwellDBA ~]# echo "select * from limbs where legs=0" | mysql -h localhost -u cbuser -p cookbook
Enter password:
thing legs arms
fish 0 0
phonograph 0 1
squid 0 10
[root@MaxwellDBA ~]# mysql -h localhost -u cbuser -p cookbook
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 199
Server version: 8.0.26 Source distribution
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> select * from limbs where legs=0;
+------------+------+------+
| thing | legs | arms |
+------------+------+------+
| fish | 0 | 0 |
| phonograph | 0 | 1 |
| squid | 0 | 10 |
+------------+------+------+
3 rows in set (0.00 sec)
mysql> quit
Bye
[root@MaxwellDBA ~]# mysql -h localhost -u cbuser -p cookbook -e "select * from limbs where legs=4"|sed -e "s/table/squid/g"
Enter password:
thing legs arms
armchair 4 2
squid 4 0
[root@MaxwellDBA ~]# mysql -h localhost -u cbuser -p cookbook -e "select * from limbs where legs=4"|sed -e "s/table/XXXXX/g"
Enter password:
thing legs arms
armchair 4 2
XXXXX 4 0
[root@MaxwellDBA ~]#
Producing HTML or XML output
mysql generate an HTML table from each query result set if you use the -H(or -html) option.
[root@MaxwellDBA ~]# mysql -h localhost -u cbuser -p -e "SELECT * FROM limbs where legs=0" cookbook
Enter password:
+------------+------+------+
| thing | legs | arms |
+------------+------+------+
| fish | 0 | 0 |
| phonograph | 0 | 1 |
| squid | 0 | 10 |
+------------+------+------+
[root@MaxwellDBA ~]# mysql -h localhost -u cbuser -p -H -e "SELECT * FROM limbs where legs=0" cookbook
Enter password:
<TABLE BORDER=1><TR><TH>thing</TH><TH>legs</TH><TH>arms</TH></TR><TR><TD>fish</TD><TD>0</TD><TD>0</TD></TR><TR><TD>phonograph</TD><TD>0</TD><TD>1</TD></TR><TR><TD>squid</TD><TD>0</TD><TD>10</TD></TR></TABLE>[root@MaxwellDBA ~]# mysql -h localhost -u cbuser -p -H -e "SELECT * FROM limbs where legs=0" cookbook > limbs.xhtml
Enter password:
[root@MaxwellDBA ~]# ll
total 4
-rw-r--r-- 1 root root 207 Oct 13 18:11 limbs.xhtml
[root@MaxwellDBA ~]# open -a safari limbs.xhtml
open: invalid option -- 'a'
Usage: open [OPTIONS] -- command
This utility help you to start a program on a new virtual terminal (VT).
Options:
-c, --console=NUM use the given VT number;
-e, --exec execute the command, without forking;
-f, --force force opening a VT without checking;
-l, --login make the command a login shell;
-u, --user figure out the owner of the current VT;
-s, --switch switch to the new VT;
-w, --wait wait for command to complete;
-v, --verbose print a message for each action;
-V, --version print program version and exit;
-h, --help output a brief help message.
[root@MaxwellDBA ~]# mysql -h localhost -u cbuser -p -X -e "SELECT * FROM limbs where legs=0" cookbook
Enter password:
<?xml version="1.0"?>
<resultset statement="SELECT * FROM limbs where legs=0
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="thing">fish</field>
<field name="legs">0</field>
<field name="arms">0</field>
</row>
<row>
<field name="thing">phonograph</field>
<field name="legs">0</field>
<field name="arms">1</field>
</row>
<row>
<field name="thing">squid</field>
<field name="legs">0</field>
<field name="arms">10</field>
</row>
</resultset>
[root@MaxwellDBA ~]#
Using User-Defined Variables in SQL Statements
[root@MaxwellDBA ~]# mysql -h localhost -u cbuser -p cookbook
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 210
Server version: 8.0.26 Source distribution
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> select database();
+------------+
| database() |
+------------+
| cookbook |
+------------+
1 row in set (0.00 sec)
mysql> select max(arms+legs) into @max_limbs from limbs;
Query OK, 1 row affected (0.00 sec)
mysql> select * from limbs where arms+legs=@max_limbs;
+-----------+------+------+
| thing | legs | arms |
+-----------+------+------+
| centipede | 99 | 0 |
+-----------+------+------+
1 row in set (0.00 sec)
mysql> select @last_id:=last_insert_id();
+----------------------------+
| @last_id:=last_insert_id() |
+----------------------------+
| 0 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select thing from limbs where legs=0;
+------------+
| thing |
+------------+
| fish |
| phonograph |
| squid |
+------------+
3 rows in set (0.00 sec)
mysql> select thing into @name from limbs where legs=0;
ERROR 1172 (42000): Result consisted of more than one row
mysql> select @name;
+-------+
| @name |
+-------+
| fish |
+-------+
1 row in set (0.00 sec)
mysql> select thing into @name2 from limbs where legs<0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select @name2;
+----------------+
| @name2 |
+----------------+
| NULL |
+----------------+
1 row in set (0.00 sec)
mysql> SET @sum= 4 + 7;
Query OK, 0 rows affected (0.00 sec)
mysql> select @sum;
+------+
| @sum |
+------+
| 11 |
+------+
1 row in set (0.00 sec)
mysql> SET @max_limbs=(SELECT MAX(arms+legs) FROM limbs);
Query OK, 0 rows affected (0.00 sec)
mysql> SET @x=1,@X=2;SELECT @x,@X;
Query OK, 0 rows affected (0.00 sec)
+------+------+
| @x | @X |
+------+------+
| 2 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql>
Using External Programs
mysql>
mysql> select @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
mysql> system ls /var/lib/mysql/
auto.cnf binlog.000003 ca-key.pem client-key.pem ib_buffer_pool ibtmp1 mysql.ibd mysqlx.sock public_key.pem undo_001
backup binlog.000004 ca.pem cookbook ibdata1 '#innodb_temp' mysql.sock mysqlx.sock.lock server-cert.pem undo_002
binlog.000001 binlog.000005 carrefour '#ib_16384_0.dblwr' ib_logfile0 menagerie mysql.sock.lock performance_schema server-key.pem
binlog.000002 binlog.index client-cert.pem '#ib_16384_1.dblwr' ib_logfile1 mysql mysql_upgrade_info private_key.pem sys
mysql>
Filtering and Processing Output
mysql>
mysql>
mysql> pager less -F -X
PAGER set to 'less -F -X'
mysql> SELECT * FROM city;
+----------------+----------------+----------------+
| state | capital | largest |
+----------------+----------------+----------------+
| Alabama | Montgomery | Birmingham |
| Alaska | Juneau | Anchorage |
| Arizona | Phoenix | Phoenix |
| Arkansas | Little Rock | Little Rock |
| California | Sacramento | Los Angeles |
| Colorado | Denver | Denver |
| Connecticut | Hartford | Bridgeport |
| Delaware | Dover | Wilmington |
| Florida | Tallahassee | Jacksonville |
| Georgia | Atlanta | Atlanta |
| Hawaii | Honolulu | Honolulu |
| Idaho | Boise | Boise |
| Illinois | Springfield | Chicago |
| Indiana | Indianapolis | Indianapolis |
| Iowa | Des Moines | Des Moines |
| Kansas | Topeka | Wichita |
| Kentucky | Frankfort | Louisville |
| Louisiana | Baton Rouge | New Orleans |
| Maine | Augusta | Portland |
| Maryland | Annapolis | Baltimore |
| Massachusetts | Boston | Boston |
| Michigan | Lansing | Detroit |
| Minnesota | St. Paul | Minneapolis |
| Mississippi | Jackson | Jackson |
| Missouri | Jefferson City | Kansas City |
| Montana | Helena | Billings |
| Nebraska | Lincoln | Omaha |
| Nevada | Carson City | Las Vegas |
| New Hampshire | Concord | Manchester |
| New Jersey | Trenton | Newark |
| New Mexico | Santa Fe | Albuquerque |
| New York | Albany | New York City |
| North Carolina | Raleigh | Charlotte |
| North Dakota | Bismarck | Fargo |
| Ohio | Columbus | Columbus |
| Oklahoma | Oklahoma City | Oklahoma City |
| Oregon | Salem | Portland |
| Pennsylvania | Harrisburg | Philadelphia |
| Rhode Island | Providence | Providence |
| South Carolina | Columbia | Columbia |
| South Dakota | Pierre | Sioux Falls |
| Tennessee | Nashville | Memphis |
| Texas | Austin | Houston |
| Utah | Salt Lake City | Salt Lake City |
| Vermont | Montpelier | Burlington |
| Virginia | Richmond | Virginia Beach |
| Washington | Olympia | Seattle |
| West Virginia | Charleston | Charleston |
| Wisconsin | Madison | Milwaukee |
| Wyoming | Cheyenne | Cheyenne |
+----------------+----------------+----------------+
50 rows in set (0.01 sec)
mysql> SELECT * FROM movies;
+----+------+----------------------------+
| id | year | movie |
+----+------+----------------------------+
| 1 | 1997 | The Fifth Element |
| 2 | 1999 | The Phantom Menace |
| 3 | 2001 | The Fellowship of the Ring |
| 4 | 2005 | Kingdom of Heaven |
| 5 | 2010 | Red |
| 6 | 2011 | Unknown |
+----+------+----------------------------+
6 rows in set (0.00 sec)
mysql> \P grep "History list length"
PAGER set to 'grep "History list length"'
mysql> SHOW ENGINE INNODB STATUS\G
History list length 0
1 row in set (0.00 sec)
mysql> SELECT SLEEP(60);
1 row in set (1 min 0.00 sec)
mysql> nopager
PAGER set to stdout
mysql> pager md5sum
PAGER set to 'md5sum'
mysql> select 'Output of this statement is a bash';
ecff0c81accc3be98d2190f8b9098052 -
1 row in set (0.00 sec)
mysql> pager cat> /dev/null
PAGER set to 'cat> /dev/null'
mysql> select 'Output of this statement goes to nowhere';
1 row in set (0.00 sec)
mysql> pager
Default pager wasn't set, using stdout.
mysql> select 'Output of this statement is visible';
+-------------------------------------+
| Output of this statement is visible |
+-------------------------------------+
| Output of this statement is visible |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select * from limbs;
+--------------+------+------+
| thing | legs | arms |
+--------------+------+------+
| armchair | 4 | 2 |
| centipede | 99 | 0 |
| fish | 0 | 0 |
| human | 2 | 2 |
| insect | 6 | 0 |
| Peg Leg Pete | 1 | 2 |
| phonograph | 0 | 1 |
| space alien | NULL | NULL |
| squid | 0 | 10 |
| table | 4 | 0 |
| tripod | 3 | 0 |
+--------------+------+------+
11 rows in set (0.00 sec)
mysql>