This Is Your Brain On Informatics: MariaDB

Installing a User Interface (SQL Buddy)

Installation

The user interface that we installed is sqlbuddy, which can be acquired from http://sqlbuddy.com/. This site will allow the user to download a zip file of the user interface. Move this file to the server using an ftp client (such as filezilla) and then use mv to move the zip file to directory shown below and remain there for the rest of installation.

/usr/share/nginx/html

In order to unzip the file, unzip must first be installed on the server. If it is not type:

aptidude install unzip

Then, unzip the file by typing:

unzip sqlbuddy.zip
rm -rf sqlbuddy.zip

The files, which are now embedded in sqlbuddy/src need to be moved to sqlbuddy. Do this by typing:

mv sqlbuddy/src/* sqlbuddy

Now, a final configuration must be done in the www file so that the index.php will be recognized as the primary site. Go to the file shown below using pico (or any other text editor of your choice).

/etc/nginx/sites-available/www

In this file add index.php to the line shown below (shown with index.php added already).

index index.html index.htm index.php

Sqlbuddy should now be able to be accessed by typing in yourservername/sqlbuddy into the browser.

Troubleshooting

There are still some quirks that may need to be worked out at this point.

Firefox

Firefox is excellent at tracking history; however, because of this, anything that was done incorrectly during the setup of sqlbuddy, mariadb, or even php will be remembered by the browser. If firefox is your browser of choice, it will be necessary to clear any history, after doing any major troubleshooting in order to allow those changes to take effect in firefox.

MariaDB SQL Commands

SQL

The language of Maria DB.

Common Commands in SQL
Boolean Operators
AND
OR
NOT
Command*	Syntax	Description
mysql	mysql -u username -p	Command line command where username should be replaced by the user's account name
`table`		Syntax for indicating a table
AS var		Sets an output to a variable
AUTO_INCREMENT		Automatically adds +1 to an entry for a column for each successive entry
AVG()		Gives an average of the values in the indicated column
BIGINT		64-bit integer
CHAR		Assigns a character variable (allows ONLY 1 character)
CREATE TABLE `table`		Creates a table of the given name with the properly input variable (see below)
CREATE TEMPORARY TABLE `[table]`		Creates a temporary table of the given name
DATE		Assigns a date variable of the form 00-00-0000
FROM		Denotes which table SELECT pulls the columns from (e.g. SELECT [col1],[col2] FROM `table`
INSERT	INSERT INTO table_name VALUES (value1,value2,value3,...); INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...); -- specifies the columns the values go into	Used with INTO to insert new data entries into the specified columns
INT		32-bit integer
INTO	SELECT * or column_name(s) INTO newtable [IN externaldb] FROM table1;	Used with SELECT command to copy column_names from table1 into newtable
LIMIT #		Limits to the top # number of entries (not necessarily by rank)
MAX()		Gives the maximum of the values in the indicated column
MIN()		Gives the minimum of the values in the indicated column
PRIMARY KEY		Indicates which variable will differentiate each data entry
SELECT		Selects columns from a database for analysis
UNSIGNED		only allows positive integers or float values, always assigned after BIGINT or INT value
UPDATE	UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;	Changes column1 to value1 based on the WHERE statement (use something unique to update only one row)
USE database		Selects database for manipulation and analysis
VARCHAR(#)		Assigns a character array variable of length #
WHERE		Limits the available values SELECT can pull from each column based on a logical statement (e.g. WHERE col1 < 10)

*Commands are capitalized by convention.

In-Class Example of Manually Creating a Table

This data table was uploaded as a TSV (tab separated values) file.

LOAD DATA INFILE '[data table pathname]' INTO TABLE `test` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINE

Answer to In-Class Question

Q: Find the maximums in column 2 of quintiles based on column 1

The following will create a temporary table that will order [var1], hold [var2] and also create a column with a counter variable named "id".

A: CREATE TEMPORARY TABLE `rank` (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY) SELECT [var1],[var2] FROM `test` WHERE [var3] = "string" AND [var1] <10000000 ORDER BY [var1];

Then the use of the next command will need to be repeated for each quintile to find the maximum for each one.

A: SELECT MAX([var2]) FROM `rank` WHERE id BETWEEN [calculated lower bound of quintile] AND [calculated upper bound of quintile];