How do I run an sql file?

I was looking througn the manual, trying to make sence out of all this sql
stuff, when I discovered that I could define my tables in a text file,
rather than typing them at the mysql> prompt. line by line, only to see if
fail because of a simple typo; So I wrote a file. Below is the contents of
my file, included only because I desire comments and corrections. I am
totally new at this, and I don't know if I'm doing it right. The database
the file creates is meant to keep track of people, places, and things. Does
it make any sence to you? Does anything not make sence? Please remember that
I'm still virtually clueless, so don't think twice about stating the ovious,
but thats not really my question. My question is,....

"How do I 'run' this file?"

Now that I've written the file, I can't find where I saw how to run it, and
I can't remember either. I do remember thinking "Oh, Thats so simple", but
that does me no good whatsoever. Please help.

heres my file............

Create Table Person
(
id integer unsigned not null auto_increment,
name integer unsigned not null,
sex enum('Male',Female','Other') not null,
home integer unsigned not null,
location integer unsigned not null,
index(id,home,location),
primary key(id),
foreign key(name) references name(id),
foreign key(home) references place(id),
foreign key(location) references place(id),
on update cascade on delete cascade
);

Create Table Place
(
id integer unsigned not null auto_increment,
name varchar not null,
display boolean default true,
within integer unsigned not null,
index(id,within),
primary key(id),
secondary key(within) references Place(id),
on delete cascade on update cascade
);

Create Table Thing
(
id integer unsigned not null auto_increment,
name varchar not null,
owner integer unsigned null,
location integer unsigned not null,
index(id,owner,location),
primary key(id),
secondary key (owner) references person(id),
secondary key (location) references place(id),
on update cascade on delete cascade
);

Create Table Name
(
id integer unsigned not null auto_increment,
prefix varchar(5) null default '',
first varchar(20) not null,
middle varchar(30) null default '',
last varchar(20) null default '',
suffix varchar(5) null default '',
index(id),
primary key (id)
);

..... end of file.


Thanks in advance,

Tony.

Re: How do I run an sql file?

Tony Peardon wrote:
> I was looking througn the manual, trying to make sence out of all this sql
> stuff, when I discovered that I could define my tables in a text file,
> rather than typing them at the mysql> prompt.
> "How do I 'run' this file?"

If you have managed to get into the mysql prompt, then you seen the following
message from the mysql:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 110 to server version: 4.0.18-log

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

Then you may should have typed that "\h", if you had done that you had got the
following list:

For information about MySQL products and services, visit:
http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
http://dev.mysql.com/
To buy MySQL Network Support, training, or other products, visit:
https://shop.mysql.com/

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'

If you had been this curious then you had seen the '\.' option and you would
have solved the problem yourself quite easily.

mysql> \. yourfile.sql


If you don't want to get into mysql prompt, then you can use following command
line:

mysql -uadmin -pXXXXX < yourfile.sql

change the "admin" to the username you use to login to your mysql, the "XXXXX"
part need to changed to your password.


//Aho

Re: How do I run an sql file?

Tony Peardon wrote:
>
> I was looking througn the manual, trying to make sence out of all this sql
> stuff, when I discovered that I could define my tables in a text file,
> rather than typing them at the mysql> prompt. line by line, only to see if
> fail because of a simple typo; So I wrote a file.
> ...
> "How do I 'run' this file?"

Use the command-line MySQL client:

mysql -u [user] -p[password] < [file_name]

Note that there is no space between -p and the password...

Cheers,
NC