This Is Your Brain On Informatics: MariaDB
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 | ||||||
|---|---|---|---|---|---|---|
| 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];
| |||||||||||||||||||||||
