Using mysql to Run SQL Statements

<< Introduction of MySQL 5.0 Programs

<< MySQL Tutorials - Herong's Tutorial Notes

This section provides some tutorial examples on how to run SQL statements on a MySQL server with the client tool mysql. SQL statements can be executed one by one interactively, or in batch mode.

Once mysql is started, it will present to interactive prompt for you to run any SQL statements on the MySQL server or any mysql commands:

• To run any SQL statement, enter "sqlStatement;". The last character, ";", triggers mysql to execute the statement on the server.
• To run any mysql commands, enter "command".
• To quit from mysql, enter "quit".
• To get help, enter "help".

In the following example, I executed 4 SQL statements with mysql:

\mysql\bin\mysql --host=localhost --user=root test
......

mysql> create table hello (message varchar(80));
Query OK, 0 rows affected (0.41 sec)

mysql> insert into hello (message) values ('Hello world!');
Query OK, 1 row affected (0.03 sec)

mysql> select * from hello;
+--------------+
| message      |
+--------------+
| Hello world! |
+--------------+
1 row in set (0.00 sec)

mysql> drop table hello;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

Note that:

• mysql reports you back on the execution time of each statement.
• Result of the SELECT statement is nicely formatted.

In the previous example, SQL statements were executed interactively one by one. Another way to execute SQL statements is to put them into a file, and execute them in one command. First let's store all the statements we used in the previous into a file, hello.sql:

-- hello.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
create table hello (message varchar(80));
insert into hello (message) values ('Hello world!');
select * from hello;
drop table hello;

To execute the statements in hello.sql, you can use the "source" command inside mysql:

\mysql\bin\mysql --host=localhost test
......

mysql> source hello.sql
Query OK, 0 rows affected (0.05 sec)

Query OK, 1 row affected (0.00 sec)

+--------------+
| message      |
+--------------+
| Hello world! |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Or you can run mysqsl in batch mode:

\mysql\bin\mysql --host=localhost --user=root test < hello.sql
message
Hello world!

As you can see, this is a much better way to execute SQL statements.

Sections in This Chapter

List of MySQL Programs

mysqld - The MySQL Server Program

mysqladmin - The Client Tool for Administrators

mysql - The Client Tool for End Users

Using mysql to Run SQL Statements

mysqldump - Dumping Data to Files

mysqlimport - Loading Data from Files