FreeBSD Cookbook

Sponsored links

Your first interaction with MySQL database server

Ok you got MySQL server installed. Let us communicate with our server using mysql command line client. The mysql program provides a curses-based interface to the database server. Please note that following commands also ensures that your MySQL server installation is successful and working fine.

a) To start just type mysql at shell prompt:

$ mysql

Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.9-beta

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

mysql> is prompt where you need to type sql commands.

b) List database :

mysql> show databases;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.06 sec)

You can see three database (test, mysql) in above example.

c) Let us use test database:

mysql> use test

Database changed

d) Let us use mysql database:

mysql> use mysql

Database changed

Display list of tables:

mysql> show tables;

Outut:

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
15 rows in set (0.01 sec)

Display/select all rows from user table:

mysql> select * from user;

e) Display server status:

mysql> status

Output:

mysql  Ver 14.11 Distrib 5.0.9-beta, for portbld-freebsd6.0 (i386) using  5.0

Connection id:          14
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.9-beta
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 1 hour 47 min 42 sec

Threads: 1  Questions: 53  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 15  Queries per second avg: 0.008
--------------

f) Display mysql client help:

mysql> help

Output:

For the complete MySQL Manual online, visit:
http://www.mysql.com/documentation

For info on technical support from MySQL developers, visit:
http://www.mysql.com/support

For info on MySQL books, utilities, consultants, etc., visit:
http://www.mysql.com/portal

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear command.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute a SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

g) Quit/exit mysql:

mysql> quit

0 User Contributed Notes:

<< Home

© 2006 nixCraft & Chirag, All rights reserved - Disclaimer - Privacy policy