6 July 2015

Linux – Databases

To use databases in Linux it is easiest to use mysql which can be installed using:

sudo apt-get install mysql

To configure the setup go to /etc/mysql/ and open my.cnf. As default only local access are allowed to the databases. This is specified as bind-adress = 127.0.0.1 in the my.cnf file.

To launch mysql simply type sudo mysql. Some basic commands you can use:

SHOW DATABASES; – Lists all the databases in the system
USE dbname; – Selects the database called dbname;
SHOW TABLES; – Shows all the tables in the selected database;
DESCRIBE tablename; – Show information about the table
exit; – Close mysql

Example creating a database, a table and inserting of values:

CREATE DATABASE MCH;

USE MCH;

CREATE TABLE lamps(
nr VARCHAR(9) PRIMARY KEY,
name VARCHAR(30),
watt varchar(6),
price DECIMAL(3,1)
);

INSERT INTO lamps VALUES (‘33-410-05’, ‘Lamp E10 10V 0,2A’, ‘0,2 W’,’13.8’);

You could also insert data from a file using:

LOAD DATA LOCAL INFILE 'path/to/file' INTO TABLE lamps;

This requires that mysql is launched using:

sudo mysql local-infile=1

To list all data in the table:

SELECT * FROM TABLE lamps;

To search for the one with the lowest price:

SELECT * FROM lamps WHERE price = (SELECT MIN(price) FROM lamps);

To search for names containing 10V in the name:

SELECT * FROM lamps WHERE name LIKE "%10V%";