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>

Renaming a Column in MySQL

You can rename a column as shown below. You need to include the column’s definition, even if you are not changing it, otherwise MySQL displays an error:

 

mysql> create table andrews_table

    -> (col1 varchar(5));

Query OK, 0 rows affected (0.09 sec)

 

mysql> desc andrews_table;

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

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

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

| col1  | varchar(5) | YES  |     | NULL    |       |

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

1 row in set (0.01 sec)

 

mysql> alter table andrews_table

    -> change col1 col2 varchar(5);

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> desc andrews_table;

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

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

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

| col2  | varchar(5) | YES  |     | NULL    |       |

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

1 row in set (0.06 sec)

 

mysql>

 

And you can change a column’s definition at the same time, if you wish:

 

mysql> alter table andrews_table

    -> change col2 col3 varchar(10);

Query OK, 0 rows affected (0.18 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> desc andrews_table;

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

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

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

| col3  | varchar(10) | YES  |     | NULL    |       |

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

1 row in set (0.00 sec)

 

mysql>

My SQL show create table command

You can see the layout of a table with the desc command but if you want to see how it was created, you should use the show create table command. I had to reformat the output below to fit it on the screen. If you try it yourself, you will see what I mean:

mysql> create table andrews_table
    -> (col1 varchar(10), primary key(col1));
Query OK, 0 rows affected (0.04 sec)

mysql> desc andrews_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col1  | varchar(10) | NO   | PRI |         |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.04 sec)

mysql> show create table andrews_table;
+---------------+-------------------------------------------+
| Table         | Create Table                              |
+---------------+-------------------------------------------+
| andrews_table | CREATE TABLE `andrews_table`              |
|               | (`col1` varchar(10) NOT NULL default '',  |
|               | PRIMARY KEY  (`col1`)) ENGINE=MyISAM      |
|               | DEFAULT CHARSET=latin1                    |
+---------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

MySQL create table ... like ...

If you want to create a table with the same description as another table, you can do this using like. In the example below, I create a table called andrews_table with 1 row of data. Then I use like to create a table called another_table with the same description. This table is empty to start with. To copy the data from andrews_table into another_table, I use a simple insert statement:

 

mysql> create table andrews_table

    -> (col1 varchar(10));

Query OK, 0 rows affected (0.03 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.12 sec)

 

mysql> create table another_table

    -> like andrews_table;

Query OK, 0 rows affected (0.00 sec)

 

mysql> desc another_table;

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

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

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

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

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

1 row in set (0.06 sec)

 

mysql> select * from another_table;

Empty set (0.00 sec)

 

mysql> insert into another_table

    -> (select * from andrews_table);

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> select * from another_table;

+------+

| col1 |

+------+

| Fred |

+------+

1 row in set (0.00 sec)

 

mysql>