Best place for .sql data and method for retrieving them in mysql database?

Question

I have some confusion with .bash_profile, terminal, MAMP and MySQL Workbench.

My goal is to load .sql data files into a database from terminal. Working on tutorials from Paul Dubois' book on MySQL. The Appendix A says to add a PATH setting to .bash_profile:

PATH=/usr/local/mysql/bin:/bin:/usr/bin:/usr/local/bin

I added the above path to the .bash_profile. Although in the first chapter it says a folder of sample .sql files called (sampdb) can be located anywhere. So I tried:

export PATH="/Users/trevorpan/Documents/MySQL/sampdb:$PATH"

Adding this PATH did not find the file with this command:

mysql> sampdb < create_grade_event.sql;

It gave me this error. And I feel it must be related to the path, because all over stack overflow, the book, and the online MySQL manual say the above is the proper syntax.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sampdb < create_grade_event.sql' at line 1

I also tried:

mysql> source create_grade_event.sql;
ERROR: 
Failed to open file 'create_grade_event.sql', error: 2

This didn't work either.

Now, I'm thinking the files should just go in the bin folder. But I'm not sure if they should stay in the folder (sampdb) or what.

Here's the .bash_profile:

export PATH=/Applications/MAMP/bin/php/php7.1.12/bin:/usr/local/opt/gnu-tar/lib$
export MAMP_PHP=/Applications/MAMP/bin/php/php7.1.12/bin
export PATH="$MAMP_PHP:$PATH"
ICU_DIR=/usr/local/Cellar/icu4c/60.2/
EXT_DIR=/Applications/MAMP/bin/php/php7.1.12/lib/php/extensions/no-debug-non-zt$

if [ -f ~/.bashrc ]; then
        source ~/.bashrc
fi

export PATH="/usr/local/opt/m4/bin:$PATH"
PATH=/usr/local/mysql/bin:/bin:/usr/bin:/usr/local/bin

Thank you.

EDIT:

I tried dragging a file to terminal and had success.

mysql> source /Applications/MAMP/bin/sampdb/insert_member.sql 
Query OK, 0 rows affected (0.05 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.04 sec)

Query OK, 1 row affected (0.01 sec) etc. etc.

EDIT #2

aha. Michael,

I tried changing the directory and received this error. cd is not able to point to a directory while mysql is loaded in terminal, so I'm unsure the method you use to load it from a current directory:

mysql> cd /Applications/MAMP/bin/sampdb;
ERROR 1064 (42000): You have an error in your SQL syntax; check the   manual that corresponds to your MySQL server version for the right syntax     to use near 'cd /Applications/MAMP/bin/sampdb' at line 1

My goal is to point to a folder location and be able to upload .sql data files without having to enter the path each time. The following post had a similar issue, but it never seemed to resolve the simple import command of:

mysql> source create_grade_event.sql

How to import an SQL file using the command line in MySQL?

My original post name was trying to get to that. Should the .sql data files be placed in bin? If so, how do I direct mysql to load them with a query like above? Is there a better place you use to store project .sql files?

Thank you.

Answer 1

Loading a .sql file into an existing database is done like this:

shell> mysql sampdb < create_grade_event.sql

That is very different than this:

mysql> sampdb < create_grade_event.sql;

...which is indeed wrong.

This issue is not related to your path.

mysql> source create_grade_event.sql

...should have worked, but you did not indicate whether you did that, or this:

shell> source create_grade_event.sql;

...which would also be incorrect. In any event, "didn't work" is not enough detail.

Here, as in the MySQL documentation, shell> refers to your system shell prompt, which typically ends with $.