|
|
| (16 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| − | ===mysql -u [username] -p===
| + | MariaDB is an opensource project that is an offshoot of MySQL Oracle. The developers of this database actually came from Oracle, and created MariaDB in response to what they considered Oracle's poor management of MySQL. Because this database was built to be a better managed version of MySQL, many of the resources on the internet for MySQL will be applicable for MariaDB. Below are several topics on MariaDB in the context of running on an NGINX server. |
| | | | |
| − | <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.</p>
| + | * [[This Is Your Brain On Informatics: User Interfaces|User Interfaces]] |
| | + | * [[This Is Your Brain On Informatics: MariaDB SQL Commands|MariaDB SQL Commands]] |
| | + | * [[This Is Your Brain On Informatics: Q&A|In-Class Questions and Answers]] |
| | | | |
| − | ===SQL===
| |
| | | | |
| − | <p>The language of Maria DB.</p>
| + | {{This Is Your Brain On Informatics}} |
| − | | |
| − | {| class = "wikitable" | |
| − | |- align = "center"
| |
| − | ! colspan = "3" | Common Commands in SQL
| |
| − | |- align = "center"
| |
| − | ! colspan ="3" | Boolean Operators
| |
| − | |- align = "center"
| |
| − | | colspan ="3" | AND
| |
| − | |- align = "center"
| |
| − | | colspan ="3" | OR
| |
| − | |- align = "center"
| |
| − | | colspan ="3" | NOT
| |
| − | |-
| |
| − | ! 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
| |
| − | |-
| |
| − | | 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
| |
| − | |-
| |
| − | | INSERT || INSERT INTO ''table_name''<br /><br />VALUES (''value1,value2,value3,...'');<br /><br />INSERT INTO ''table_name'' (''column1,column2,column3,...'')<br /><br />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)''<br />INTO ''newtable'' [IN ''externaldb'']<br />FROM ''table1'';|| Used with SELECT command to copy ''column_names'' from ''table1'' into ''newtable''
| |
| − | |-
| |
| − | | UPDATE || UPDATE ''table_name''<br />SET ''column1=value1,column2=value2,...''<br />WHERE ''some_column''=''some_value;'' || Changes ''column1'' to ''value1'' based on the WHERE statement (use something unique to update only one row)
| |
| − | |-
| |
| − | |}
| |
| − | | |
| − | <p>*Commands are capitalized by convention.</p>
| |
| − | | |
| − | ===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 '[data table pathname]' INTO TABLE `test` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINE</p>
| |
| − | | |
| − | ===Answer to In-Class Question===
| |
| − | | |
| − | <p>Q: Find the maximums in column 2 of quintiles based on column 1 </p>
| |
| − | | |
| − | <p>
| |
| − | The following will create a temporary table that will order [var1], hold [var2] and also create a column with a counter variable named "id".
| |
| − | <br /><br />
| |
| − | 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];
| |
| − | <br /><br />
| |
| − | Then the use of the next command will need to be repeated for each quintile to find the maximum for each one.
| |
| − | <br /><br />
| |
| − | A: SELECT MAX([var2]) FROM `rank` WHERE id BETWEEN [calculated lower bound of quintile] AND [calculated upper bound of quintile];
| |
| − | </p>
| |
| | | | |
| − | {{This Is Your Brain On Informatics}}
| + | [[Category:This Is Your Brain On Informatics]] |
