Nested source Command

In an earlier post, I showed how you can run SQL commands by storing them in a file and running it with the source command. You can run nested source commands too, as shown below. Here are 3 SQL files:

 

UNIX > cat nested_source.sql

use test;

source count_star.sql

source select_database.sql

UNIX > cat count_star.sql

select count(*) from mysql.user;

UNIX > cat select_database.sql

select database();

UNIX >

 

So if you use source on nested_source.sql, it will run the SQL contained in the files count_star.sql and select_database.sql. You can see what I mean in the example below:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 53432

Server version: 5.0.67 Source distribution

 

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

 

mysql> source nested_source.sql

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

+----------+

| count(*) |

+----------+

|       18 |

+----------+

1 row in set (0.00 sec)

 

+------------+

| database() |

+------------+

| test       |

+------------+

1 row in set (0.00 sec)

 

mysql>

MySQL source Command

The source command runs SQL commands from an operating system file. I created the file below:

 

UNIX > cat my_first_script

create database fred;

select database();

use fred;

select database();

create table freds_table

(col1 varchar(10));

desc freds_table;

drop table freds_table;

show tables;

drop database fred;

UNIX >

 

... and it produced the following output when I ran it from within MySQL:

 

mysql> source my_first_script

Query OK, 1 row affected (0.00 sec)

 

+------------+

| database() |

+------------+

| NULL       |

+------------+

1 row in set (0.00 sec)

 

Database changed

+------------+

| database() |

+------------+

| fred       |

+------------+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.01 sec)

 

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| col1  | varchar(10) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

1 row in set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

Empty set (0.00 sec)

 

Query OK, 0 rows affected (0.00 sec)

 

mysql>

select database()

The select database() command lets you see which database you are using. When you first login to mysql, you are not using a database so the query returns a null:

 

mysql> select database ();
+-------------+
| database () |
+-------------+
| NULL        |
+-------------+
1 row in set (0.01 sec)

mysql>

You can choose a database with the use command. Unlike other commands, it seems to work equally well with or without a semi colon at the end:
  

mysql> use test
Database changed
mysql> use test;
Database changed
mysql>

After that, if you run the query again, it shows which database you have chosen:

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.02 sec)

mysql>