|
|
| (5 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| − | <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. |
| − | MariaDB is an opensource project that is an offshoot of MySQL because the developers felt that Oracle was doing a poor job managing MySQL (actually, the developers came from Oracle). Because MariaDB 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 to make it operate in the context of an NGINX server. | |
| | | | |
| | * [[This Is Your Brain On Informatics: User Interfaces|User Interfaces]] | | * [[This Is Your Brain On Informatics: User Interfaces|User Interfaces]] |
| Line 6: |
Line 5: |
| | * [[This Is Your Brain On Informatics: Q&A|In-Class Questions and Answers]] | | * [[This Is Your Brain On Informatics: Q&A|In-Class Questions and Answers]] |
| | | | |
| − | </p>
| |
| | | | |
| − | ==Installing a User Interface (SQL Buddy)==
| + | {{This Is Your Brain On Informatics}} |
| − | | |
| − | ===Installation===
| |
| − | | |
| − | <p>
| |
| − | The user interface that we installed is sqlbuddy, which can be acquired from http://sqlbuddy.com/. This site will allow the user to download a zip file of the user interface. Move this file to the server using an ftp client (such as filezilla) and then use [[This Is Your Brain On Informatics: Linux|mv]] to move the zip file to directory shown below and remain there for the rest of installation.
| |
| − | | |
| − | <pre>/usr/share/nginx/html</pre>
| |
| − | | |
| − | In order to unzip the file, unzip must first be installed on the server. If it is not type:
| |
| − | | |
| − | <pre>aptidude install unzip</pre>
| |
| − | | |
| − | Then, unzip the file by typing:
| |
| | | | |
| − | <pre>
| + | [[Category:This Is Your Brain On Informatics]] |
| − | unzip sqlbuddy.zip
| |
| − | rm -rf sqlbuddy.zip
| |
| − | </pre>
| |
| − | | |
| − | This method of download should have all of the files located directly under the ''sqlbuddy'' directory; however, this needs to be confirmed first. Type:
| |
| − | | |
| − | <pre>ll sqlbuddy</pre>
| |
| − | | |
| − | If there is a single directory listed named ''src'' then the files, which are embedded in ''sqlbuddy/src'' need to be moved to ''sqlbuddy''. Do this by typing:
| |
| − | | |
| − | <pre>mv sqlbuddy/src/* sqlbuddy</pre>
| |
| − | | |
| − | If there are multiple files in the ''sqlbuddy'' directory, ignore the above command.
| |
| − | </p>
| |
| − | <p>
| |
| − | Now, a final configuration must be done in the ''www'' file so that the index.php will be recognized as the primary site. Go to the file shown below using [[This Is Your Brain On Informatics: Pico|pico]] (or any other text editor of your choice).
| |
| − | | |
| − | <pre>/etc/nginx/sites-available/www</pre>
| |
| − | | |
| − | In this file add ''index.php'' to the line shown below (shown with ''index.php'' added already).
| |
| − | | |
| − | <pre>index index.html index.htm index.php</pre>
| |
| − | | |
| − | Sqlbuddy should now be able to be accessed by typing in ''yourservername/sqlbuddy'' into the browser.
| |
| − | </p>
| |
| − | | |
| − | ===Troubleshooting===
| |
| − | <p>
| |
| − | There are still some quirks that may need to be worked out at this point.
| |
| − | </p>
| |
| − | ====Firefox====
| |
| − | <p>
| |
| − | Firefox is excellent at tracking history; however, because of this, anything that was done incorrectly during the setup of sqlbuddy, mariadb, or even php will be remembered by the browser. If firefox is your browser of choice, it will be necessary to clear any history, after doing any major troubleshooting in order to allow those changes to take effect in firefox.
| |
| − | </p>
| |
| − | | |
| − | ==MariaDB SQL Commands==
| |
| − | | |
| − | ===SQL===
| |
| − | | |
| − | <p>The language of Maria DB.</p>
| |
| − | | |
| − | {| 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
| |
| − | |-
| |
| − | | mysql || mysql -u ''username'' -p || Command line command where ''username'' should be replaced by the user's account name
| |
| − | |-
| |
| − | | `''table''` || || Syntax for indicating a table
| |
| − | |-
| |
| − | | AS ''var'' || || Sets an output to a variable
| |
| − | |-
| |
| − | | AUTO_INCREMENT || || Automatically adds +1 to an entry for a column for each successive entry
| |
| − | |-
| |
| − | | AVG() || || Gives an average of the values in the indicated column
| |
| − | |-
| |
| − | | BIGINT || || 64-bit integer
| |
| − | |-
| |
| − | | CHAR || || Assigns a character variable (allows ONLY 1 character)
| |
| − | |-
| |
| − | | CREATE TABLE `''table''` || || Creates a table of the given name with the properly input variable (see below)
| |
| − | |-
| |
| − | | CREATE TEMPORARY TABLE `[table]` || || Creates a temporary table of the given name
| |
| − | |-
| |
| − | | DATE || || Assigns a date variable of the form 00-00-0000
| |
| − | |-
| |
| − | | FROM || || Denotes which table SELECT pulls the columns from (e.g. SELECT [col1],[col2] FROM `''table''`
| |
| − | |-
| |
| − | | 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
| |
| − | |-
| |
| − | | INT || || 32-bit integer
| |
| − | |-
| |
| − | | 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''
| |
| − | |-
| |
| − | | LIMIT # || || Limits to the top # number of entries (not necessarily by rank)
| |
| − | |-
| |
| − | | MAX() || || Gives the maximum of the values in the indicated column
| |
| − | |-
| |
| − | | MIN() || || Gives the minimum of the values in the indicated column
| |
| − | |-
| |
| − | | PRIMARY KEY || || Indicates which variable will differentiate each data entry
| |
| − | |-
| |
| − | | SELECT || || Selects columns from a database for analysis
| |
| − | |-
| |
| − | | UNSIGNED || || only allows positive integers or float values, always assigned after BIGINT or INT value
| |
| − | |-
| |
| − | | 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)
| |
| − | |-
| |
| − | | USE ''database'' || || Selects database for manipulation and analysis
| |
| − | |-
| |
| − | | VARCHAR(#) || || Assigns a character array variable of length #
| |
| − | |-
| |
| − | | WHERE || || Limits the available values SELECT can pull from each column based on a logical statement (e.g. WHERE ''col1'' < 10)
| |
| − | |-
| |
| − | |}
| |
| − | | |
| − | <p>*Commands are capitalized by convention.</p>
| |
| − | | |
| − | ==MariaDB Examples==
| |
| − | | |
| − | ===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}}
| |
