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

From Pathology Education Instructional Resource
Jump to: navigation, search
Line 47: Line 47:
 
| style="padding: 10px" | Assigns a character variable (allows ONLY 1 character)
 
| style="padding: 10px" | Assigns a character variable (allows ONLY 1 character)
 
|-
 
|-
| style="padding: 10px" | CREATE TABLE `''table''`
+
| style="padding: 10px" | CREATE TABLE
| style="padding: 10px" |  
+
| style="padding: 10px" | CREATE TABLE `''table''` VALUES(`''column_name1''` ''DATA_TYPE'', `''column_name2''` ''DATA_TYPE'', ''etc.'');
 
| style="padding: 10px" | Creates a table of the given name with the properly input variable (see below)
 
| 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
| style="padding: 10px" | CREATE TEMPORARY TABLE `''table''`
+
| style="padding: 10px" | CREATE TEMPORARY TABLE `''table''` VALUES(`''column_name1''` ''DATA_TYPE'', `''column_name2''` ''DATA_TYPE'', ''etc.'');
 
| style="padding: 10px" | Creates a temporary table of the given name
 
| style="padding: 10px" | Creates a temporary table of the given name
 
|-
 
|-

Revision as of 01:47, 27 March 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` VALUES(`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` VALUES(`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 * 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)