Chapter 18 using Database

You can process data with a script and store it in a variable to use it, but when the script ends, all the processed data is deleted. In order to use persistent data, a database must be used in the script. In this chapter, we will learn how to use the database.

​

1. MySQL Database

First, let's look at how to install MySQL.

1) Connect to the www.mysql.com site.

2) Click the Download Link and download the file that matches your server version from Community.

3) After logging in to Oracle, download the file.

4) Unzip the file using the TAR command.

5) Setting up MySQL environment. : In case of automatic installation, you do not need to set this step, but in case of manual installation, you need to set the environment.

​

- /usr/local/mysql/scripts/mysql_install_db --user=mysql ==>create default DB file

- cd /usr/local/mysql

- chown -R root .

- chown -R mysql data

​

6) Now that the installation is complete, MySQL starts automatically when the server reboots, so let's see how to check and run it.

​

- Check if it works

ps ax | grep mysql

2084 ? S 0:00 /bin/sh /usr/bin/mysqld_safe -- datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --logerror=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid 2141 ? Sl 0:03 /usr/libexec/mysqld --basedir=/usr -- datadir=/var/lib/mysql --user=mysql --pidfile=/var/run/mysqld/mysqld.pid --skip-external-locking -- socket=/var/lib/mysql/mysql.sock 8825 pts/1 S+ 0:00 grep mysql

#

​

- Run it if it is not running

/usr/local/mysql/bin/mysqld_safe --user=mysql &

​

- There is usually a script that runs automatically in Linux distributions. It is located in the following location.

/usr/local/mysql/support-files/mysql.server

​

7) Setting MySQL root user password: There is a master account called root to control DB access. After installation, you need to set the password for the master account.

- $ mysqladmin -u root password newpasswd

​

MySQL user interface

The default user interface for MySQL is the mysql command line. The mysql command allows you to connect to any mysql server on your network. If you enter the mysql command without any other parameters, it tries to connect to the mysql server in the local system by default. When trying to connect to mysql, by default, it tries with your own account, but if the account is not registered in mysql, you will not be able to connect. When logging in with a different account, you can connect using the -u option as shown below.​

$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.0.45 Source distribution Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the buffer. mysql>

​The table below shows the parameters available in mysql.

MySQL command

The mysql program uses the following two types of commands.

■ Special mysql commands

■ Standard SQL syntax ​ The table below shows the parameters that can be used in mysql.

​

The table below shows the parameters that can be used in mysql.

Below are mysql commands. When using, you can use the full name or abbreviated command on the mysql prompt.

One of the SQL and other commands is the SHOW command and several others. Let's run some of those commands.

mysql> SHOW DATABASES; ==> retrieve DB +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.04 sec) ​ ​ mysql> USE mysql; ==> use mysql Database changed mysql> SHOW TABLES; ==>retieve tanles of mysql +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | proc | | procs_priv | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 17 rows in set (0.00 sec) mysql>

MySQL create DB Object

Before writing data to MySQL DB using a script, you must first create a DB object. A summary of the main necessary tasks is given below.

​

■ A unique database to store your application data

■ A unique user account to access the database from your scripts

■ One or more data tables to organize your data

​

​1) Creating DB

In general, DB is created for each application, and the data for each application is separated and then stored. The command to create DB is as follows

​

■ CREAT DATABASE name: In the name part, you can input the name of the DB you want to create.

​

In order to create a DB, a user must have appropriate privileges given to the ID, and usually start by creating a root. Let's see how to create it with the example below.

$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. ​ Your MySQL connection id is 15 Server version: 5.0.45 Source distribution Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the buffer. ​ mysql> CREATE DATABASE test; Query OK, 1 row affected (0.00 sec) ​ mysql> ​ now retrieve created DB mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | ==> you can see test DB is created +--------------------+ 3 rows in set (0.01 sec) ​ mysql> ​ mysql> USE test; ==> Let's connect to the test DB using the USE command. Database changed; mysql> SHOW TABLES; Empty set (0.00 sec) ​ mysql>

2) Create a user account

In the above example, DB was created using the root account. However, using the root account is quite risky. The root account has all the setver accounts, and if root's password is exposed, there is an irreversible risk.

​

Let's see how to create a user account and grant DB access rights.

Let's see how to give permission to the test account ​ mysql> GRANT SELECT,INSERT,DELETE,UPDATE ON test.* TO test IDENTIFIED by ’test’; Query OK, 0 rows affected (0.35 sec) ​ mysql> ​ The syntax is long, but if you go through them one by one, GRANT means you want to grant permission. Second, SELECT, INSERT, DELETE, and UPDATE are the privileges to be granted. i.e. select, insert, delete, update It means to give permission. ​ ON test.* means to grant the above privileges to all tables (*) in the test DB. TO test means to grant the above privileges to the account called test, and if there is no account called test, the account is created.​ ​ ​ identified by test allows you to create a default password for the newly created account. ​ $ mysql test -u test -p ==> Now, let's connect to the DB with the test account. Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.45 Source distribution ​ Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the buffer. mysql> ​

PostgreSQL DataBase

Let's look at how to use scripts using PostgraSQL. ​

1) To install. There is an automatic installation method using the package of your system to install with MySQL, and there is a manual installation method through download. The automatic installation method here is not different from MySQL, so you can proceed as described above without mentioning it. The manual installation method is as follows.

​

A. Connect to www.postgresql.org.

B. Click the link for the latest PostgreSQL.

C. Select the file to download and start downloading.

D. Unzip the downloaded file in the downloaded directory. (usually the HOEM directory)

: tar -zxvf postgresql-8.2.6.tar.gz (loaded file name)

​

E. After unzipping, a directory is created with the name of the extracted file. Move to that directory.

: $ cd postgresql-8.2.6 ​

​

F. Execute the setup script.

: ./configure ​

​

G. Execute the gmake utility to compile the source.

: gmake ​

​

H. Change to the root user and run the installation.

$ su

password

: # gmake install

After installation, binary files are installed in the path of /usr/local/postgre/data

​

I. Create a user for Postgre, create a directory to store DB files, and set the user as the owner of the directory set

# adduser postgres

# mkdir /usr/local/postgres/data

# chown postgres /usr/local/postgres/data

​

J. Initialize PostgreeSQL DB to use DB.​

# su postgres

$ /usr/local/postgres/bin/initdb -D /usr/local/postgres/data

​

K. Now let's try running the DB in the background. If you want to automatically run the DB at boot time In this case, you can add the following command to the startup script.

# su postgres

# cd /usr/local/postgres/bin

# ./postgres -D /usr/local/postgres/data >logfile 2>&1 &

​

​

PostgreSQL Interface

​

The Postgre interface is a program called psql. Let's see how to manage and connect to DB using this interface.

​

1) Connect to the server.

Postgre uses a valid Linux user account, so you must log in with the postgre Linux account. Below is the procedure to connect to the server.

$ su

Password:

# su postgres

bash-3.2$ psql

Welcome to psql 8.2.6, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help with psql commands

\g or terminate with semicolon to execute query

\q to quit

​

postgres=#

​

And the table below is the command options available in postgre.​

​psql command

psql supports standard SQL syntax, and separate meta-commands can be used.

■ Standard SQL statements

■ PostgreSQL meta-commands

​

meta commandi

There are many supported meta-commands, but below are the most commonly used commands. And if you don't know the command or need help, just type \?. ■ \l : List of available DBs

■ \c : Connect to DB

■ \dt : Display table in DB

■ \du : List Postgre users

■ \z : List up table privileges

■ \? : List all meta commands

■ \h : List all sql commands

■ \q : Exit db ​​

​

2) Create DB Object

Create DB in the same way as MySQL. However, you must log in with postgre first.

$ su Password: # su postgres bash-3.2$ psql Welcome to psql 8.2.6, the PostgreSQL interactive terminal. postgres=# CREATE DATABASE test; ==>create test DB CREATE DATABASE postgres=# ​ Let's list up the created DB postgres=# \l List of databases Name | Owner | Encoding -----------+----------+---------- postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 test | postgres | UTF8 (4 rows) ​ Now, let's connect to the test DB.​ postgres=# \c test You are now connected to database "test". test=#

3) Create a user account.

After creating the DB, you need to create an account that can access the shell script. As mentioned earlier, Postgre's user account is different from MySQL's. The Postgre account is called a login role, and the Postgre server must set the login role the same as the Linux account. So, there are two ways to create an account to access Postgre as follows.​

​

■ How to create a Linux account that matches Postgre's Login Role that can run shell scripts ■ Create Postgre account for Linux account that needs DB access

​

Here, we will look at the method using the second method.

test=# CREATE ROLE rich login; ==>create Login Role을 CREATE ROLE test=#

​

2. Working with tables

1) Creating a table

Earlier, I created a DB in MySQL and Postgre, created an account, and tried to access it. Now let's learn how to create and manage tables. First, let's find out the difference between Data Field and Record.

​

Components of DATA

■ Data Filed: A piece of information, for example, the employee name in the employee table. ■ Record : A collection of related data fields. Employee information in the employee table

■ Table : A collection of records of all employees

​

Creating a table using standard SQL statements

Let's create a table like this:

$ mysql test -u root -p Enter password: mysql> CREATE TABLE employees ( ==> Create an employee table. -> empid int not null, ==> Define each column in the table and define attributes. -> lastname varchar(30), ==> Creates a variable-size character column.. -> firstname varchar(30), -> salary float, -> primary key (empid)); Query OK, 0 rows affected (0.14 sec) mysql> ​ Let's look up the created table. mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | employees | +----------------+ 1 row in set (0.00 sec) mysql> ​ ​ In Postgre, let's use the \dt command to query. test=# \dt List of relations Schema | Name | Type | Owner --------+-----------+-------+---------- ㅂpublic | employees | table | postgres (1 row) test=# ​ As explained earlier, in the case of Postgre, you need to grant permission at the table level. # su postgres ==> Switch to Postgre User $ psql test ==> access to test table test=# GRANT SELECT,INSERT,DELETE,UPDATE ON public.employees TO rich; GRANT test=# ​

Let's look at Data Types in MySQL and PostgreSQL here.

​

​

Inserting data

The INSERT statement is used to insert data. How to use it is as follows.

​

​

How to use

INSERT INTO table VALUES (...) ==> Values ​​are separated by commas.

​ Let's look at an example using INSERT. ​ $ mysql test -u test -p Enter password: ​ mysql> Query OK, 1 row affected (0.35 sec) ​ in case of Postgre [rich@testbox ~]$ psql test Welcome to psql 8.2.6, the PostgreSQL interactive terminal. test=> INSERT INTO employees VALUES (1, ’Blum’, ’Rich’, 25000.00); INSERT 0 1 test=> ​ ​

Deleting data

Let's try to delete data from the table. The usage command is DELETE, and the usage syntax is as follows.

​

usage phrase

DELETE FROM employees WHERE COLUMN = VALUE;

mysql> DELETE FROM employees WHERE empid = 2; Query OK, 1 row affected (0.29 sec) ​

​

Querying data

I tried inserting and deleting. Now let's see how to retrieve data from a table. The syntax used is SELECT, and the syntax used is as follows.

​

usage phrase

SELECT datafields FROM table

​

query all info in table of Empployee ​ mysql> SELECT * FROM employees; +-------+----------+------------+--------+ | empid | lastname | firstname | salary | +-------+----------+------------+--------+ | 1 | Blum | Rich | 25000 | | 2 | Blum | Barbara | 45000 | | 3 | Blum | Katie Jane | 34500 | | 4 | Blum | Jessica | 52340 | +-------+----------+------------+--------+ 4 rows in set (0.00 sec) mysql> ​ ​

Filter your data

There is a way to filter data in a query statement to query only the necessary information. You can use the following three syntaxes.

​

■ WHERE: Shows the subtotals of records that satisfy a specific condition.

■ ORDER BY: Displays records in a specific order.

■ LIMIT: Shows only subtotals of records.

​

Let's look at a usage example

mysql> SELECT * FROM employees WHERE salary > 40000; ==> Display only records with a salary of 40000 or higher +-------+----------+-----------+--------+ | empid | lastname | firstname | salary | +-------+----------+-----------+--------+ | 2 | Blum | Barbara | 45000 | | 4 | Blum | Jessica | 52340 | +-------+----------+-----------+--------+ 2 rows in set (0.01 sec) mysql>

3. Using DB in script

Now, let's find out how to use the DB we studied earlier with scripts.

​

Connect to DB

1) Finding the location of mysql and psql programs: You need to use which to find the dentures of the programs in the Linux system you are currently using.

$ which mysql /usr/bin/mysql $ which psql /usr/bin/psql $

​

It is convenient to use in the script by putting the above values ​​in the environment variable as follows.

MYSQL=`which mysql` PSQL=`which psql` ​

2) Log in to the server

After finding the locations of the programs to be used above, we use them in the script to log in. Let's look at an example we used in our script.

[rich@testbox]$ cat ptest1 ==>Let's look at the contents of the ptest script. #!/bin/bash # test connecting to the PostgreSQL server PSQL=`which psql`==> Save the location of the psql program in the PSQL variable. $PSQL test ​ [rich@testbox]$ ./ptest1 ===> If you run the script, you do not need to connect to the postgre account You will be directly connected to the DB. ​ Welcome to psql 8.2.6, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test=> ​ ​Let's take a look at MySQL as well. $ cat mtest1 ==> Let's look at the script content. #!/bin/bash # test connecting to the MySQL server ​ MYSQL=`which mysql` ==> store location of mysql program in mysql $MYSQL test -u test -p==> Command to connect to test DB as test account ​ $ ./mtest1==> If you run the script, you will be connected automatically. Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 5.0.45 Source distribution ​ Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the buffer. mysql> ​

3) Sending commands to the server

After connecting to the server, you will want to send commands like delete, insert, lookup to the server to get results. There are two ways to do this:

​

■ Send one command and then exit

■ Sending multiple commands

​

Send one command

$ cat mtest2 ==> Let's take a look at the script contents. #!/bin/bash # send a command to the MySQL server ​ MYSQL=`which mysql` ==> Save the program location in mysql. $MYSQL test -u test -e ’select * from employees’ ==> Retrieve all employee information in the test table with the test account ​ If you run the script, $ ./mtest2 +-------+----------+------------+---------+ | empid | lastname | firstname | salary | +-------+----------+------------+---------+ | 1 | Blum | Rich | 25000 | | 2 | Blum | Barbara | 45000 | | 3 | Blum | Katie Jane | 34500 | | 4 | Blum | Jessica | 52340 | +-------+----------+------------+---------+ $ ​ This time, let's see how to use it in Postgre. $ cat ptest2 #!/bin/bash # send a command to the PostgreSQL server PSQL=`which psql` $PSQL test -c ’select * from employees’ ​ $ ./ptest2 empid | lastname | firstname | salary -------+----------+------------+-------- 1 | Blum | Rich | 25000 2 | Blum | Barbara | 45000 3 | Blum | Katie Jane | 34500 4 | Blum | Jessica | 52340 (4 rows) $

Sending multiple commands

$ cat mtest3 #!/bin/bash # sending multiple commands to MySQL ​ MYSQL="$(which mysql)" $MYSQL test -u test ‹‹EOF ==> Defines the command to be transmitted using file redirection. show tables; select * from employees where salary › 40000; EOF ​ If you run it $ ./mtest3 Tables_in_test employees empid lastname firstname salary 2 Blum Barbara 45000 4 Blum Jessica 52340 $ ​ Let's see how to use it in psql. $ cat ptest3 #!/bin/bash # sending multiple commands to PostgreSQL PSQL="$(which psql)" ​ $PSQL test ‹‹EOF \dt select * from employees where salary › 40000; EOF ​ $ ./ptest3 List of relations Schema | Name | Type | Owner --------+-----------+-------+---------- public | employees | table | postgres (1 row) ​ empid | lastname | firstname | salary -------+----------+-----------+-------- 2 | Blum | Barbara | 45000 4 | Blum | Jessica | 52340 (2 rows) $ ​ ​

Let's look at an example of inserting data this time..

$ cat mtest4 #!/bin/bash # send data to the table in the MySQL database ​ MYSQL=`which mysql` ==> Save mysql program location if [ $# -ne 4 ] ==> If the input data is not equal to 4, then output the statement after the then, otherwise, move to else statement then echo "Usage: mtest4 empid lastname firstname salary" else statement="INSERT INTO employees VALUES ($1, ’$2’, ’$3’, $4)" ​==> Save input data to employee table ​ $MYSQL test -u test ‹‹ EOF $statement EOF if [ $? -eq 0 ] then echo Data successfully added else echo Problem adding data fi fi ​ 실행을 시켜 보면 $ ./mtest4 ==> The number of input data is not 4, but the following sentence is output Usage: mtest4 empid lastname firstname salary ​ $ ./mtest4 5 Blum Jasper 100000 ==> There are 4 data and it is entered normally Data added successfully ​ $ $ ./mtest4 5 Blum Jasper 100000 ==> An error message is displayed because the user id is duplicated ERROR 1062 (23000) at line 1: Duplicate entry ’5’ for key 1 Problem adding data $

Form the data

In the example below, let's take a look at various useful examples when working with an actual DB.

Storing the output in a variable. If you store the output in a variable, you can use the output for other tasks. $ cat mtest5 #!/bin/bash # redirecting SQL output to a variable ​ MYSQL=`which mysql` dbs=`$MYSQL test -u test -Bse ’show databases’` ==> B mean Bash mode, s mean silent mode, for db in $dbs do echo $db done ​ $ ./mtest5 information_schema test $ ​ Using format tag: Display the output result in HTML format. (-H option), display -x option in XML format $ psql test -H -c ’select * from employees where empid = 1’ ‹table border="1"› ‹tr› ‹th align="center"›empid‹/th› ‹th align="center"›lastname‹/th› ‹th align="center"›firstname‹/th› ‹th align="center"›salary‹/th› ‹/tr› ‹tr valign="top"› ‹td align="right"›1‹/td› ‹td align="left"›Blum‹/td› ‹td align="left"›Rich‹/td› ‹td align="right"›25000‹/td› ‹/tr› ‹/table› ‹p›(1 row)‹br /› ‹/p› $ ​ ​

​