Difference between revisions of "This Is Your Brain On Informatics: MariaDB SQL Commands"

From Pathology Education Instructional Resource
Jump to: navigation, search
Line 3: Line 3:
  
 
{| class = "wikitable"
 
{| class = "wikitable"
−
|- align = "center"
+
|-  
−
! colspan = "3" | Common Commands in SQL
+
! colspan="3" align = "center" | Common Commands in SQL
−
|- align = "center"
+
|-
−
! colspan ="3" | Boolean Operators
+
! colspan="3" align = "center" | Boolean Operators
−
|- align = "center"
+
|-
−
| colspan ="3" | AND
+
| colspan="3" align = "center" | AND
−
|- align = "center"
+
|-
−
| colspan ="3" | OR
+
| colspan="3" align = "center" | OR
−
|- align = "center"
+
|-
−
| colspan ="3" | NOT
+
| colspan="3" align = "center" | NOT
 
|-
 
|-
 
! Command*
 
! Command*
Line 26: Line 26:
 
| Syntax for indicating a table
 
| Syntax for indicating a table
 
|-
 
|-
−
| AS ''var'' || || Sets an output to a variable  
+
| AS ''var''
 +
|
 +
| Sets an output to a variable  
 
|-
 
|-
−
| AUTO_INCREMENT || || Automatically adds +1 to an entry for a column for each successive entry
+
| 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
+
| AVG()
 +
|  
 +
| Gives an average of the values in the indicated column
 
|-
 
|-
−
| BIGINT || || 64-bit integer
+
| BIGINT
 +
|  
 +
| 64-bit integer
 
|-
 
|-
−
| CHAR || || Assigns a character variable (allows ONLY 1 character)
+
| 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 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
+
| CREATE TEMPORARY TABLE `[table]`
 +
|  
 +
| Creates a temporary table of the given name
 
|-
 
|-
−
| DATE || || Assigns a date variable of the form 00-00-0000
+
| 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''`
+
| 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
+
| 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
+
| 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''
+
| 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)
+
| LIMIT #  
 +
|  
 +
| Limits to the top # number of entries (not necessarily by rank)
 
|-
 
|-
−
| MAX() || || Gives the maximum 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
+
| MIN()
 +
|  
 +
| Gives the minimum of the values in the indicated column
 
|-
 
|-
−
| PRIMARY KEY || || Indicates which variable will differentiate each data entry
+
| PRIMARY KEY
 +
|  
 +
| Indicates which variable will differentiate each data entry
 
|-
 
|-
−
| SELECT || || Selects columns from a database for analysis
+
| SELECT
 +
|  
 +
| Selects columns from a database for analysis
 
|-
 
|-
−
| UNSIGNED || || only allows positive integers or float values, always assigned after BIGINT or INT value
+
| 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)
+
| 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
+
| USE ''database''
 +
|  
 +
| Selects database for manipulation and analysis
 
|-
 
|-
−
| VARCHAR(#) || || Assigns a character array variable of length #
+
| 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)
+
| WHERE
 +
|  
 +
| Limits the available values SELECT can pull from each column based on a logical statement (e.g. WHERE ''col1'' < 10)
 
|-
 
|-
 
|}
 
|}

Revision as of 01:34, 27 March 2014

MariaDB SQL Commands

The language of Maria DB.

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 `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

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 * or 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)
  • Commands are capitalized by convention.


Linux
NGINX
PHP-FPM
MariaDB
Pico
Code Examples
Previous Classes
Retrieved from "https://dev.peirmost.ifx.uab.edu/index.php?title=This_Is_Your_Brain_On_Informatics:_MariaDB_SQL_Commands&oldid=2884"