Working with scripts in MySQL

Some resources are considered facilitators and database scripts are one of those resources. Its main function is to run a series of commands in the database in a linear way, using a DBMS interface (database management system).

With a script you can perform any action within the database. Creating tables, creating procedures, queries, updates and records of deletions are some practical examples of using this type of resource. There are cases where a complete database can be created using a single script.

In MySQL, scripts are mainly used to restore databases and tables. This type of use is very common, once creating a database from scratch takes a lot of time and effort by the DBA.

Next, we show a sample script that can be created and run on MySQL:

USE testdatabase;

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (id INT, first_name VARCHAR(20), last_name VARCHAR(30));

INSERT INTO employees (id, first_name, last_name) VALUES (1, ‘John’, ‘Doe’);

INSERT INTO employees (id, first_name, last_name) VALUES (2, ‘Bob’, ‘Smith’);

INSERT INTO employees (id, first_name, last_name) VALUES (3, ‘Jane’, ‘Doe’);

SELECT * FROM employees;

The entire script will run in a structured way. Each line is processed in the order in which it was written. Analyzing the sample script we have:

• The first line indicating which database will be used;

• The second using a conditional that will exclude the table, if it exists;

• In the third, the table creation instruction;

• In the following the inclusion of three records in the table created;

• At last, a query showing all the persisted data in the database.

When using a script, you need to be aware of one important feature: the entire command is executed directly in the database, there is no dependency between them. This means that if a particular row has an error, the following lines will still be performed. To be sure, then, that all commands take effect only if no row has an error, a transaction must be open.

The commands can be saved in files with extension “.sql” and later rescued, according to the user’s needs. Understanding resource usage assumptions, the script becomes a useful tool in solving everyday situations inherent to MySQL.

Check out more content on our blog!
Learn all about Scriptcase.