MySQL Commands

[itwebxpert]

Hi,

I am very and very new just yesterday, I started to learn MySQL online, one of RDBMSs. I use MySQL Client Software version 4.0 for Windows.

I use the following client line interface command to copy data from Dat file into MySQL table.

mysql mytable_data < mytable_data.dat

with or without semi colon. It does not work.

Did I use the wrong command for version 4.0?

Any advice is very appreciated.

Cheers

[ergophobe]

check out

[dev.mysql.com...]

and

[dev.mysql.com...]

Basically, you need to specify a host name (e.g. "localhost"), your username, possibly a password flag, and a database name

You do NOT need to specify a table name. That is in your SQL command.

shell> mysql -h host -u user -p dbname < batch-file

[itwebxpert]

Thanks for your prompt response.

I run MySQL client software. How can I find username, password or hostname? Or they exist only for server?

Any advice would be appreciated.

Cheers

[ergophobe]

They exist for your server. If you haven't created any users and you are running locally,

host: localhost
user: root
pass: (blank is the default)

[itwebxpert]

Hi ergophobe,

Thank for your advice. I have tried to use username, hostname and pwd you gave, but I can't copy data from ASCII file (i.e. dat file) into MySQL table? I have used the following commands as above:

mysql -h localhost -u root MySQL_table < table.dat

I don't want to go to PHP yet, as I would like to be farmilar with MySQL commands first.

Please advise how to do the above operation?

Cheers

[jatar_k]

What does the actual data you are trying to import look like?

[itwebxpert]

The ASCII file (dat file) contain the following information:

INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Monica", "Sehgal", "Marketing Executive", 30, 3, 90000, 25000, "[e-mail]");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Hal", "Simlai", "Marketing Executive", 27, 2, 70000, 18000, "[e-mail]");
INSERT INTO employee_data (f_name, l_name, title, age, yos, salary, perks, email) values ("Joseph", "Irvine", "Marketing Executive", 27, 2, 72000, 18000, "[e-mail]");

etc.

Thank you in advance.

Cheers

[jatar_k]

did you get an error when attempting the command?

try just logging into mysql first, once you can do that then you can worry about getting the file in there.

in re the file
is that what the table structure is like?
have you created a database or is that in the file too?
do the tables already exist or is that in the file?

[StupidScript]

Once you have logged in to mySQL, you may also use this command:

source table.dat

If (a) the database and tables already exist or (b) you include CREATE DATABASE, USE, CREATE TABLE instructions in your .dat file AND (c) you have permission to do all of those things, you shouldn't have any trouble getting the data in.

i.e.

Here's what might be in "table.dat":

CREATE DATABASE newdatabase;
USE newdatabase;
CREATE TABLE newtable (
id int(11) auto_increment,
email varchar(35));
INSERT INTO newtable (id,email) VALUES (NULL,"[e-mail]");
...etc...

Then, logging in to mySQL and running the command:

mysql -u root -prootpassword
source table.dat

will create the db, switch to using the db, create the table, and import the data...if the code is tight.

[itwebxpert]

Thanks you for eveyone who contribute ideas for me. Yes, I created an MySQL table which has similar structure (colunm number and name are the same, etc.).

There are not error after I issued the command like below:

mysql -h localhost -u root MySQL_table < table.dat

without semi colon; MySQL expect more commands to be put. The same above command line with semi colon, there are error.

Any advice would be very appreciated.

Cheers

[StupidScript]

Why treat your initialization file as a batch file?

Try this simple procedure:

mysql -h localhost -u root MySQL_table
source table.dat;

The first line connects you to the database and selects MySQL_table. The second line dumps the INSERT data from your source file.

You will receive more error messages doing it this way than by running the batch file, so you may be able to figure out what's wrong more easily.

If you would tell us exactly what errors you are receiving, we could help you better.

[itwebxpert]

Hi StupidScript,

Thank alots for your advice. It worked! by using your script command:

source table_data.dat;

I have a couple of questions:

1. Why "mysql" command does not work in my system?
2. Let's say we want to import data from .dat file into an MySQL table already exists in a database. But that database has a number of MySQL table (more than one). How can we specify the name of MySQL table in the database before importing? in order to make sure that we import data from .dat file into the right MySQL Table?

Thank in advance for your help.

Cheers

[ergophobe]

The name of the table is in each and every mysql command, so it is not necessary to have it in the import command

UPDATE table1 SET.....

will go into table1.