Difference between revisions of "This Is Your Brain On Informatics: MariaDB"
Line 9: | Line 9: | ||
{| class = "wikitable" | {| class = "wikitable" | ||
|- align = "center" | |- align = "center" | ||
− | ! colspan = " | + | ! colspan = "3" | Common Commands in SQL |
|- | |- | ||
− | ! Command* !! Description | + | ! Command* !! Syntax !! Description |
|- | |- | ||
− | | `[table]` || Syntax for indicating a table | + | | `[table]` || || Syntax for indicating a table |
|- | |- | ||
− | | USE [database] || Selects database for manipulation and analysis | + | | USE [database] || || Selects database for manipulation and analysis |
|- | |- | ||
− | | SELECT || Selects columns from a database for 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]` | + | | 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) | + | | 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 |
|- align = "center" | |- align = "center" | ||
− | ! colspan =" | + | ! colspan ="3" | Boolean Operators |
|- align = "center" | |- align = "center" | ||
− | | colspan =" | + | | colspan ="3" | AND |
|- align = "center" | |- align = "center" | ||
− | | colspan =" | + | | colspan ="3" | OR |
|- align = "center" | |- align = "center" | ||
− | | colspan =" | + | | colspan ="3" | NOT |
|- | |- | ||
| 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) | ||
|- | |- | ||
− | | BIGINT || 64-bit integer | + | | BIGINT || || 64-bit integer |
|- | |- | ||
− | | INT || 32-bit integer | + | | INT || || 32-bit integer |
|- | |- | ||
− | | UNSIGNED || only allows positive integers or float values, always assigned after BIGINT or INT value | + | | 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 | + | | 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 | + | | PRIMARY KEY || || Indicates which variable will differentiate each data entry |
|- | |- | ||
− | | VARCHAR(#) || Assigns a character array variable of length # | + | | VARCHAR(#) || || Assigns a character array variable of length # |
|- | |- | ||
− | | CHAR || Assigns a character variable (allows ONLY 1 character) | + | | CHAR || || Assigns a character variable (allows ONLY 1 character) |
|- | |- | ||
− | | DATE || Assigns a date variable of the form 00-00-0000 | + | | DATE || || Assigns a date variable of the form 00-00-0000 |
|- | |- | ||
− | | AVG() || Gives an average of the values in the indicated column | + | | AVG() || || Gives an average of the values in the indicated column |
|- | |- | ||
− | | MAX() || Gives the maximum 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 | + | | MIN() || || Gives the minimum of the values in the indicated column |
|- | |- | ||
− | | LIMIT # || Limits to the top # number of entries (not necessarily by rank) | + | | LIMIT # || || Limits to the top # number of entries (not necessarily by rank) |
|- | |- | ||
− | | CREATE TEMPORARY TABLE `[table]` || Creates a temporary table of the given name | + | | CREATE TEMPORARY TABLE `[table]` || || Creates a temporary table of the given name |
+ | |- | ||
+ | | INTO || INSERT INTO table_name<br />VALUES (value1,value2,value3,...);<br />INSERT INTO table_name (column1,column2,column3,...)<br />VALUES (value1,value2,value3,...); -- specifies the columns the values go into || Inserts new data entries into the specified columns | ||
|- | |- | ||
− | |||
|} | |} | ||
Revision as of 01:04, 25 October 2013
Contents
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* | Syntax | 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 | |
INTO | 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 |
Inserts new data entries into the specified columns |
*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];
|