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>

IF EXISTS

If you drop a table and it does not exist, you will get an error message but, if you include the if exists clause, you will only get a warning:
  

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| andrews_table  |
+----------------+
1 row in set (0.00 sec)

mysql> drop table andrews_table;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> drop table andrews_table;
ERROR 1051 (42S02): Unknown table 'andrews_table'
mysql> drop table if exists
    -> andrews_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1051 | Unknown table 'andrews_table' |
+-------+------+-------------------------------+
1 row in set (0.00 sec)

mysql>

show tables

The show tables command lists the tables in the database you are using. In the example below:

1. The show tables command is run on an empty database.
2. A table is created.
3. The show tables command is run again and the new table appears in the output.
4. The table is dropped.
5. The show tables command is run showing that the database is empty again.

mysql> show tables;

Empty set (0.00 sec)

 

mysql> create table andrews_table

    -> (col1 varchar(10));

Query OK, 0 rows affected (0.07 sec)

 

mysql> show tables;

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

| Tables_in_Andrew |

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

| andrews_table    |

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

1 row in set (0.00 sec)

 

mysql> drop table andrews_table;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show tables;

Empty set (0.00 sec)

 

mysql>