不可不知的 MySQL Client Program_mysql client programs

 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>