如何在 MySQL Workbench 上针对 .sql 数据库运行脚本 [英] How to run a script against a .sql database on MySQL Workbench

问题描述

对不起，我是个新手，我知道这看起来是个愚蠢的问题.我正在尝试遵循 也没有多大帮助.我尝试按照示例数据库存储库上的

Sorry for me being a novice, I know this looks like a dumb question. I'm trying to follow this very beginner's tutorial. It seems the instructor is using the employees.sql database from this sample database. If I just run the command:

select * from employees;

I get the error

Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.

I tried opening the employees.sql on the side:

but then running it returns the error message:

Error Code: 1064. 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 'source load_departments.dump' at line 1

And this similar issue on their GitHub repository also doesn't help much. I tried following the installation instructions on the sample database repository:

mysql < employees.sql

however, I get another error:

ERROR 1045 (28000): Access denied for user 'foobar'@'localhost' (using password: NO)

I would appreciate it if you could help me know what are the prerequisites in the step by step form to be able to follow the rest of this tutorial.

P.S. I'm on macOS Catalina and my Workbench version is 8.0, and MySQL

mysql Ver 8.0.19 for osx10.15 on x86_64 (Homebrew)

following the instructions here.

解决方案

There is a small error in that repository.

You'll have to fix that.

Open up the employees.sql with an editor, like Notepad++ for example then go to the bottom of the file where you will find this:

SELECT 'LOADING departments' as 'INFO';
source load_departments.dump ;
SELECT 'LOADING employees' as 'INFO';
source load_employees.dump ;
SELECT 'LOADING dept_emp' as 'INFO';
source load_dept_emp.dump ;
SELECT 'LOADING dept_manager' as 'INFO';
source load_dept_manager.dump ;
SELECT 'LOADING titles' as 'INFO';
source load_titles.dump ;
SELECT 'LOADING salaries' as 'INFO';
source load_salaries1.dump ;
source load_salaries2.dump ;
source load_salaries3.dump ;

source show_elapsed.sql ;

Try replacing:

"SELECT 'LOADING departments' as 'INFO';
source load_departments.dump ;"

with

"SELECT 'LOADING departments' as 'INFO';
source C:/load_departments.dump ;

• Where C:/ should be the actual path to the file in question, on your computer.
• Do this for every line in the file there at the end, adding the appropriate path.

Now import employees.sql and run it and see if it works.

If it doesn't, then try doing it this way:

Delete only the following code and save the changes to the file.

SELECT 'LOADING departments' as 'INFO';
source load_departments.dump ;
SELECT 'LOADING employees' as 'INFO';
source load_employees.dump ;
SELECT 'LOADING dept_emp' as 'INFO';
source load_dept_emp.dump ;
SELECT 'LOADING dept_manager' as 'INFO';
source load_dept_manager.dump ;
SELECT 'LOADING titles' as 'INFO';
source load_titles.dump ;
SELECT 'LOADING salaries' as 'INFO';
source load_salaries1.dump ;
source load_salaries2.dump ;
source load_salaries3.dump ;

Now continue doing this:

• Open Workbench

• Add a new MySQL connection to your local MySQL server.

Make sure you are connecting/adding the root user or another user that has the following privileges, to the connection:

SELECT, INSERT, UPDATE, DELETE, 
CREATE, DROP, RELOAD, REFERENCES, 
INDEX, ALTER, SHOW DATABASES, 
CREATE TEMPORARY TABLES, 
LOCK TABLES, EXECUTE, CREATE VIEW

• Double click and open your newly formed connection

• Go to Left-Upper corner, click on File then click on Open SQL Script and select the employees.sql from the repository you downloaded and click the lightning icon.

After that:

• use the Open SQL Script again and open load_employees.dump then click the Lightning icon again in the newly opened file.

• use the Open SQL Script again and open load_departments.dumpthen click the Lightning icon again in the newly opened file.

• use the Open SQL Script again and open load_dept_emp.dumpthen click the Lightning icon again in the newly opened file.

• use the Open SQL Script again and open load_dept_manager.dumpthen click the Lightning icon again in the newly opened file.

• use the Open SQL Script again and open load_titles.dump then click the Lightning icon again in the newly opened file.

• use the Open SQL Script again and open load_salaries1.dumpthen click the Lightning icon again in the newly opened file.

• use the Open SQL Script again and open load_salaries2.dumpthen click the Lightning icon again in the newly opened file.

• use the Open SQL Script again and open load_salaries3.dump then click the Lightning icon again in the newly opened file.

Now you're done importing the database. You can now do your select * from employees query.

这篇关于如何在 MySQL Workbench 上针对 .sql 数据库运行脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！