Difference between revisions of "This Is Your Brain On Informatics: MariaDB SQL Commands"
(11 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
== MariaDB SQL Commands == | == MariaDB SQL Commands == | ||
− | + | ||
+ | Note that the commands are capitalized by convention. | ||
{| class = "wikitable" | {| class = "wikitable" | ||
− | |- | + | |- |
− | ! colspan = "3" | Common Commands in SQL | + | ! colspan="3" | Common Commands in SQL |
− | |- | + | |- |
− | ! colspan ="3" | Boolean Operators | + | ! colspan="3" align = "center" | Boolean Operators |
− | |- | + | |- |
− | + | | style="padding: 10px" colspan="3" align = "center" | AND | |
− | |- | + | |- |
− | + | | style="padding: 10px" colspan="3" align = "center" | OR | |
− | |- | + | |- |
− | + | | style="padding: 10px" colspan="3" align = "center" | NOT | |
|- | |- | ||
! Command* | ! Command* | ||
Line 18: | Line 19: | ||
! Description | ! Description | ||
|- | |- | ||
− | | mysql | + | | style="padding: 10px" | mysql |
− | | mysql -u ''username'' -p | + | | style="padding: 10px" | mysql -u ''username'' -p |
− | | Command line command where ''username'' should be replaced by the user's account name | + | | style="padding: 10px" | Command line command where ''username'' should be replaced by the user's account name |
|- | |- | ||
− | | `''table''` | + | | style="padding: 10px" | `''table''` |
− | | | + | | style="padding: 10px" | |
− | | Syntax for indicating a table | + | | style="padding: 10px" | Syntax for indicating a table |
|- | |- | ||
− | | AS ''var'' || || Sets an output to a variable | + | | style="padding: 10px" | AS ''var'' |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | Sets an output to a variable | ||
|- | |- | ||
− | | AUTO_INCREMENT || || Automatically adds +1 to an entry for a column for each successive entry | + | | style="padding: 10px" | AUTO_INCREMENT |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | Automatically adds +1 to an entry for a column for each successive entry | ||
|- | |- | ||
− | | AVG() || || Gives an average of the values in the indicated column | + | | style="padding: 10px" | AVG() |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | Gives an average of the values in the indicated column | ||
|- | |- | ||
− | | BIGINT || || 64-bit integer | + | | style="padding: 10px" | BIGINT |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | 64-bit integer | ||
|- | |- | ||
− | | CHAR || || Assigns a character variable (allows ONLY 1 character) | + | | style="padding: 10px" | CHAR |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | Assigns a character variable (allows ONLY 1 character) | ||
|- | |- | ||
− | | CREATE TABLE `''table''` | | + | | 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) | ||
|- | |- | ||
− | | CREATE TEMPORARY TABLE ` | + | | 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 | ||
|- | |- | ||
− | | DATE || || Assigns a date variable of the form 00-00-0000 | + | | style="padding: 10px" | DATE |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | 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''` | + | | 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''` | ||
|- | |- | ||
− | | INSERT || INSERT INTO ''table_name'' | + | | style="padding: 10px" | INSERT |
+ | | style="padding: 10px" | INSERT INTO ''table_name''<br />VALUES (''value1, value2, value3,...''); <br><br> INSERT INTO ''table_name'' (''column1, column2, column3,...'')<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 | ||
|- | |- | ||
− | | INT || || 32-bit integer | + | | style="padding: 10px" | INT |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | 32-bit integer | ||
|- | |- | ||
− | | INTO || SELECT | + | | style="padding: 10px" | INTO |
+ | | style="padding: 10px" | SELECT ''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'' | ||
|- | |- | ||
− | | LIMIT # || || Limits to the top # number of entries (not necessarily by rank) | + | | style="padding: 10px" | LIMIT # |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | Limits to the top # number of entries (not necessarily by rank) | ||
|- | |- | ||
− | | MAX() || || Gives the maximum of the values in the indicated column | + | | style="padding: 10px" | MAX() |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | Gives the maximum of the values in the indicated column | ||
|- | |- | ||
− | | MIN() || || Gives the minimum 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 | ||
|- | |- | ||
− | | PRIMARY KEY || || Indicates which variable will differentiate each data entry | + | | style="padding: 10px" | PRIMARY KEY |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | Indicates which variable will differentiate each data entry | ||
|- | |- | ||
− | | SELECT || || Selects columns from a database for analysis | + | | style="padding: 10px" | SELECT |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | Selects columns from a database for analysis | ||
|- | |- | ||
− | | UNSIGNED || || only allows positive integers or float values, always assigned after BIGINT or INT value | + | | style="padding: 10px" | UNSIGNED |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | 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) | + | | 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) | ||
|- | |- | ||
− | | USE ''database'' || || Selects database for manipulation and analysis | + | | style="padding: 10px" | USE ''database'' |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | Selects database for manipulation and analysis | ||
|- | |- | ||
− | | VARCHAR(#) || || Assigns a character array variable of length # | + | | style="padding: 10px" | VARCHAR(#) |
+ | | style="padding: 10px" | | ||
+ | | style="padding: 10px" | 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) | + | | 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) | ||
|- | |- | ||
|} | |} | ||
− | |||
− | |||
Latest revision as of 04:43, 12 April 2014
MariaDB SQL Commands
Note that the commands are capitalized by convention.
Common Commands in SQL | ||
---|---|---|
Boolean Operators | ||
AND | ||
OR | ||
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 | CREATE TABLE `table` ( `column_name1` DATA_TYPE, `column_name2` DATA_TYPE, etc. ); |
Creates a table of the given name with the properly input variable (see below) |
CREATE TEMPORARY TABLE | CREATE TEMPORARY TABLE `table` ( `column_name1` DATA_TYPE, `column_name2` DATA_TYPE, etc. ); |
Creates a temporary table of the given name |
DATE | Assigns a date variable of the form 00-00-0000 | |
FROM | FROM `table` | Denotes which table SELECT pulls the columns from (e.g. SELECT [col1],[col2] FROM `table` |
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 |
INT | 32-bit integer | |
INTO | SELECT column_name(s) INTO newtable [IN externaldb] 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 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 | |
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) |
|