Difference between revisions of "This Is Your Brain On Informatics: MariaDB"

From Pathology Education Instructional Resource
Jump to: navigation, search
Line 67: Line 67:
  
 
===In-Class Example of Manually Creating a Table===
 
===In-Class Example of Manually Creating a Table===
 
+
<p>This data table was uploaded as a TSV (tab separated values) file.</p>
<p>LOAD DATA INFILE '/home/tikenn/meddata_tab.txt' INTO TABLE `test` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINE</p>
+
<p>LOAD DATA INFILE '[data table pathname]' INTO TABLE `test` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINE</p>

Revision as of 04:20, 23 October 2013

mysql -u [username] -p

The above command will give access to the MariaDB for a username that has previously been created. The next line will prompt for a password.

SQL

The language of Maria DB.

Common Commands in SQL
Command* Description
`[table]` Syntax for indicating a table
USE [database] Selects database for manipulation and analysis
SELECT Selects columns from a database for analysis
FROM Denotes which table SELECT pulls the columns from (e.g. SELECT [col1],[col2] FROM `[table]`
WHERE Limits the available values SELECT can pull from each column based on a logical statement (e.g. WHERE [col1] < 10)
AS [var] Sets an output to a variable
Boolean Operators
AND
OR
NOT
CREATE TABLE `[table]` Creates a table of the given name with the properly input variable (see below)
BIGINT 64-bit integer
INT 32-bit integer
UNSIGNED only allows positive integers or float values, always assigned after BIGINT or INT value
AUTO_INCREMENT Automatically adds +1 to an entry for a column for each successive entry
PRIMARY KEY Indicates which variable will differentiate each data entry
VARCHAR(#) Assigns a character array variable of length #
CHAR Assigns a character variable (allows ONLY 1 character)
DATE Assigns a date variable of the form 00-00-0000
AVG() Gives an average of the values in the indicated column
MAX() Gives the maximum of the values in the indicated column
MIN() Gives the minimum of the values in the indicated column
LIMIT # Limits to the top # number of entries (not necessarily by rank)
CREATE TEMPORARY TABLE `[table]` Creates a temporary table of the given name

*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