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

From Pathology Education Instructional Resource
Jump to: navigation, search
(SQL)
(SQL)
Line 21: Line 21:
 
! Command* !! Syntax !! Description
 
! Command* !! Syntax !! Description
 
|-
 
|-
| `[table]` || || Syntax for indicating a table
+
| `''table''` || || Syntax for indicating a table
|-
 
| 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  
 
| AS ''var'' || || Sets an output to a variable  
 
|-
 
|-
 
| CREATE TABLE `[table]` || Creates a table of the given name with the properly input variable (see below)
 
| CREATE TABLE `[table]` || Creates a table of the given name with the properly input variable (see below)
 +
|-
 +
| FROM || || Denotes which table SELECT pulls the columns from (e.g. SELECT [col1],[col2] FROM `''table''`
 
|-
 
|-
 
| BIGINT || || 64-bit integer
 
| BIGINT || || 64-bit integer
|-
+
|
 
| INT || || 32-bit integer
 
| INT || || 32-bit integer
 
|-
 
|-
Line 66: Line 64:
 
|-  
 
|-  
 
| USE ''database'' || || Selects database for manipulation and analysis
 
| USE ''database'' || || Selects database for manipulation and analysis
 +
|-
 +
| WHERE || || Limits the available values SELECT can pull from each column based on a logical statement (e.g. WHERE ''col1'' < 10)
 
|-
 
|-
 
|}
 
|}

Revision as of 02:03, 25 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
Boolean Operators
AND
OR
NOT
Command* Syntax Description
`table` Syntax for indicating a table
AS var Sets an output to a variable
CREATE TABLE `[table]` Creates a table of the given name with the properly input variable (see below)
FROM Denotes which table SELECT pulls the columns from (e.g. SELECT [col1],[col2] FROM `table`
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
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
INTO SELECT * or column_name(s)
INTO newtable [IN externaldb]
FROM table1;
Used with SELECT command to copy column_names from table1 into newtable
SELECT Selects columns from a database for analysis
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
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];