Open main menu

Pathology Education Instructional Resource β

Changes

This Is Your Brain On Informatics: MariaDB SQL Commands

2,018 bytes added, 04:43, 12 April 2014
no edit summary
==MariaDB SQL Commands==
<p>The language of Maria DBNote that the commands are capitalized by convention.</p>
{| class = "wikitable"
|- align = "center"! colspan = "3" | Common Commands in SQL|- align = "center"! colspan ="3" align = "center" | Boolean Operators|- align | style= "centerpadding: 10px"| colspan ="3" align = "center" | AND|- align | style= "centerpadding: 10px"| colspan ="3" align = "center" | OR|- align | style= "centerpadding: 10px"| colspan ="3" align = "center" | NOT
|-
! Command*
! Description
|-
| style="padding: 10px" | mysql| style="padding: 10px" | mysql -u ''username'' -p| style="padding: 10px" | Command line command where ''username'' should be replaced by the user's account name
|-
| style="padding: 10px" | `''table''`| style="padding: 10px" | | style="padding: 10px" | Syntax for indicating a table
|-
| style="padding: 10px" | AS ''var'' |style="padding: 10px" | |style="padding: 10px" | Sets an output to a variable
|-
| style="padding: 10px" | AUTO_INCREMENT |style="padding: 10px" | |style="padding: 10px" | Automatically adds +1 to an entry for a column for each successive entry
|-
| style="padding: 10px" | AVG() |style="padding: 10px" | |style="padding: 10px" | Gives an average of the values in the indicated column
|-
| style="padding: 10px" | BIGINT |style="padding: 10px" | |style="padding: 10px" | 64-bit integer
|-
| style="padding: 10px" | CHAR |style="padding: 10px" | |style="padding: 10px" | Assigns a character variable (allows ONLY 1 character)
|-
| style="padding: 10px" | CREATE TABLE| style="padding: 10px" | CREATE TABLE `''table''` (<br />`''column_name1''` ''DATA_TYPE'',<br />`''column_name2''` ''DATA_TYPE'',<br />''etc.''<br />);|| |style="padding: 10px" | Creates a table of the given name with the properly input variable (see below)
|-
| style="padding: 10px" | CREATE TEMPORARY TABLE| style="padding: 10px" | CREATE TEMPORARY TABLE `[''table]''` (<br />`''column_name1''` ''DATA_TYPE'',<br />`''column_name2''` ''DATA_TYPE'',<br />''etc.''<br />);|| |style="padding: 10px" | Creates a temporary table of the given name
|-
| style="padding: 10px" | DATE |style="padding: 10px" | |style="padding: 10px" | Assigns a date variable of the form 00-00-0000
|-
| style="padding: 10px" | FROM |style="padding: 10px" | FROM `''table''`|style="padding: 10px" | Denotes which table SELECT pulls the columns from (e.g. SELECT [col1],[col2] FROM `''table''`
|-
| style="padding: 10px" | INSERT |style="padding: 10px" | 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 |style="padding: 10px" | Used with INTO to insert new data entries into the specified columns
|-
| style="padding: 10px" | INT |style="padding: 10px" | |style="padding: 10px" | 32-bit integer
|-
| style="padding: 10px" | INTO |style="padding: 10px" | SELECT * or ''column_name(s)''<br />INTO ''newtable'' [IN ''externaldb'']<br />FROM ''table1'';|style="padding: 10px" | Used with SELECT command to copy ''column_names'' from ''table1'' into ''newtable''
|-
| style="padding: 10px" | LIMIT # |style="padding: 10px" | |style="padding: 10px" | Limits to the top # number of entries (not necessarily by rank)
|-
| style="padding: 10px" | MAX() |style="padding: 10px" | |style="padding: 10px" | Gives the maximum of the values in the indicated column
|-
| style="padding: 10px" | MIN() |style="padding: 10px" | |style="padding: 10px" | Gives the minimum of the values in the indicated column
|-
| style="padding: 10px" | PRIMARY KEY |style="padding: 10px" | |style="padding: 10px" | Indicates which variable will differentiate each data entry
|-
| style="padding: 10px" | SELECT |style="padding: 10px" | |style="padding: 10px" | Selects columns from a database for analysis
|-
| style="padding: 10px" | UNSIGNED |style="padding: 10px" | |style="padding: 10px" | only allows positive integers or float values, always assigned after BIGINT or INT value
|-
| style="padding: 10px" | UPDATE |style="padding: 10px" | UPDATE ''table_name''<br />SET ''column1=value1,column2=value2,...''<br />WHERE ''some_column''=''some_value;'' |style="padding: 10px" | Changes ''column1'' to ''value1'' based on the WHERE statement (use something unique to update only one row)
|-
| style="padding: 10px" | USE ''database'' |style="padding: 10px" | |style="padding: 10px" | Selects database for manipulation and analysis
|-
| style="padding: 10px" | VARCHAR(#) |style="padding: 10px" | |style="padding: 10px" | Assigns a character array variable of length #
|-
| style="padding: 10px" | WHERE |style="padding: 10px" | |style="padding: 10px" | 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>
{{Template: This Is Your Brain On Informatics}}
{{Template[[Category: This Is Your Brain On Informatics}}]]
347
edits