![]() ![]() We see that the table now has only the three original entries. Let’s verify the result: $ mysql -user=sysadmin -password=mypassword -e "SELECT * FROM EmployeeDB.employees " We can also undo the data insertions using a here-document: $ mysql -user=sysadmin -password=mypassword DELETE FROM EmployeeDB.employees WHERE (ID=4 or ID=5) ![]() Inspecting the table, we see that it now has five entries: $ mysql -user=sysadmin -password=mypassword -e "SELECT * FROM EmployeeDB.employees " The two data records are inserted, one after the other. > INSERT INTO EmployeeDB.employees (ID,FIRST_NAME,LAST_NAME,DEPARTMENT) VALUES ('5','Val','Ash','R&D') This restores the table to its original form.Īlternatively, we can use a here-document to specify SQL statements to be executed by the mysql command: $ mysql -user=sysadmin -password=mypassword INSERT INTO EmployeeDB.employees (ID,FIRST_NAME,LAST_NAME,DEPARTMENT) VALUES ('4','Kim','Zed','Marketing') $ echo "INSERT INTO EmployeeDB.employees (ID,FIRST_NAME,LAST_NAME,DEPARTMENT) VALUES ('5','Val','Ash','R&D') " |Īdditionally, we can undo the data insertions using the same approach: $ echo "DELETE FROM EmployeeDB.employees WHERE (ID=4 or ID=5) " | Mysql -user=sysadmin -password=mypassword Piping SQL Statements to mysqlĪnother approach for inserting data into a MySQL table is to echo the INSERT INTO statement and pipe the output to the mysql command: $ echo "INSERT INTO EmployeeDB.employees (ID,FIRST_NAME,LAST_NAME,DEPARTMENT) VALUES ('4','Kim','Zed','Marketing') " | This way, we’ve restored the original table which has only three entries. Here, we deleted the rows where the ID is 4 or 5.Ĭonsequently, we can verify that we’ve deleted the newly inserted records: $ mysql -user=sysadmin -password=mypassword -e "SELECT * FROM EmployeeDB.employees " We notice that two more rows have been added to the table.įinally, let’s undo the data insertion using a DELETE FROM statement: $ mysql -user=sysadmin -password=mypassword -e "DELETE FROM EmployeeDB.employees WHERE (ID=4 or ID=5) " $ mysql -user=sysadmin -password=mypassword -e "SELECT * FROM EmployeeDB.employees " Notably, the table consists of only three initial records from the database and table creation. The -e option of mysql executes SQL statements from strings. | ID | FIRST_NAME | LAST_NAME | DEPARTMENT | We can now inspect the table by running an SQL query: $ mysql -user=sysadmin -password=mypassword -e "SELECT * FROM EmployeeDB.employees " Importantly, another approach is to save the password in a Bash variable and subsequently access it through parameter expansion, so that it isn’t exposed on the command line. The –user option of the mysql command specifies the user, while the –password switch specifies the user password. Next, we run the code by supplying the SQL file to the mysql command via stdin: $ mysql -user=sysadmin -password=mypassword < create_db.sql The table has four columns, and three records are already in it. In this case, the database name is EmployeeDB and the table within it is called employees. INSERT INTO employees VALUES ('3','Mat','Ray','Finance') INSERT INTO employees VALUES ('2','Ron','Lee','IT') Here we assume your username is me and your password is letmein.INSERT INTO employees VALUES ('1','Lin','Day','HR') ![]() Run your SQL commands by using the input redirection operator, <, to send the contents of your file to MySQL (this works on Windows, MacOS, and Linux). Let’s assume the file name is update.sql, and has some commands to alter tables in a database called mydb. Open a terminal and cd to the folder containing the file that you want to import. So let’s discuss exactly how to import a SQL file using the command line (also called the terminal) and how to avoid any of the common errors that can occur. But this would not allow you to automate the tasks - for instance, to schedule them to run at midnight daily. Since the files for the tasks mentioned above contain normal SQL statements, you could copy and paste the text into your favorite SQL database manager and run the query.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |