Please enable JavaScript.
Coggle requires JavaScript to display documents.
SQL - Coggle Diagram
DDL
(Data Definition Language)
use for DBS and Table
Create Table
Create Table Table_name
(Column_name1 Data Type1 [Column_Constraint],
Column_name1 Data Type1 [Column_Constraint],
...)
Data Type:
Numeric: Int, Float, Double,...
String: Char, Varchar, Nvarchar,...
Date and Time: Date, Datetime,Time...
AlterTable
Add column: Alter Table Table_name
Add Column_name Data Type
Delete column: Alter Table Table_name
Drop Column Column_name
Rename column: Alter Table Table_name
Rename Column Old_column_name
to New_column_name
Change data type of column:
Alter Table Table_name
Modify Column Column_name Data Type
DropTable
Delete table (include format):
Drop Table Table_name
Delete only record in table:
Truncate Table Table_name
DML
(Data Manipulation Language)
uses for Record
Insert
Insert Into Table_name
(List of column_name)
Values(List of value1),
(List of value2),
(List of valueN);
:star: Insert values from one or more tables:
Insert data into an available table
Insert Into Table_name_receive_value
(List of Column_name)
Select List of Column_name
From Table_name_provide_value
Insert data into an unavailable table
Select(List of Column_name)
Into Table_name_receive_value
From Table_name_provide_value
Update
Update Table_name
Set Column_name1=New_value1,
Column_nameN=New_valueN;
Where Conditions
Delete
Delete From Table_name
[Using Conditions Ex: Table1 Join Table2]
[Where Conditions]
Read (Select)
:star: Subquery
Select List of Column_name1
From Table_name1
Where Condition1
(Select List of Column_name2
From Table_name2
Where Condition2)...
The subqueries are combined by the logical conjunctions
All,Any:
Select Column_name1
From Table_name1
Where Column_name operator Any/All
(Select Column_name2
From Table_name2
Where Condition)
:fountain_pen: The operator must be a standard comparison operator: =,!=,<>,>,<,>=,<=
:fountain_pen: Where Condition is only related to Table_name2
:fountain_pen: ''In" is an alias for ''=Any''
:fountain_pen: ''Not In'' is an alias for ''<>All"
Exists: Select Column_name1
From Table_name1
Where Column_name Exists/Not Exists
(Select Cloumn_name2
From Table_name2
Where Condition)
:fountain_pen: Where Condition is related to Table_name1 and Table_name2
:star: Common Table Expression
(CTE)
Create the temporary tables and apply conditions for them
With CTE_name1 As
(CTE body: Select Query1),
CTE_name2 As
(CTE body: Select Query2),
...
CTE_nameN As
(CTE body: Select QueryN)
CTE Usage: Select Query
Can have one or more CTEs in a query
The CTE N can apply conditions for CTE N-1
Select Distinct
used to return only distinct (different) values
Select Distinct List of Column_name
From Table_name
If putting more than one expression: the query will retrieve unique combinations for the expression listed
The Distinct clause doesn't ignore NULL values: the result set will include NULL as a distinct value
Select Where
Basic Select Where
Used to extract only those records that fulfill a specified condition
Select */List of Column_name
From Table_name
Where Conditions
Conditions is used with:
:check: Comparison Operator: =,!=,<>,>,<,>=,<=,!<,!>
:check: Logical Operator: And,Between,In/Not In,Like/Not Like,Not,Or,Is Null
Select Where Between
The Between operator selects values within a given range
Select Column_name
From Table_name
Where Column_name
Between Value1 and Value2
The values can be numbers, text, or dates
:check: Text: Select Column_name From Table_name
Where Column_name Between Text1 And Text2
Order By Column_name
:check: Date: Select Column_name From Table_name
Where Column_name Between #mm/dd/yyyy1#
And #mm/dd/yyyy2#
Select Where In
The In operator is similar to multiple Or conditions
Select Column_name From Table_name
:check: Where Column_name In (value1,value2,...)
:check: Where Column_name In (Select Statement)
Select Where Like/Not Like/And
used in a Where clause to search a specified pattern in a column
displays a record if All conditions separated by And are true
Select List of Column_name
From Column_name
:check: Where Cloumn_name Like Pattern
:check: Where Condition1 And Condition2
Two wildcards often used in conjunction with the Like operator:
:check: %: represents zero, one, or multiple characters
:check: _: represents for one, single character
Select Order By
Used to sort the result set in ascending or descending order
Select List of Column_name
From Table_name
Order By Column_name Asc/Desc
The default for Order By is Ascending if not write Asc or Desc
Return the result set randomly by using Order By: Select Column_name
From Table_name
Order By Rand()
Limit Row_count
Can write the order of a column instead of the name of a column
Ex: Order By 1,2
Select Limit
Used to constrain the number of rows returned by the Select statement
Select List of Column_name
From Table_name
Limit Row_count/Offset,Row_count
Offset: specifies the offset of the first row to return
Row_count: specifies the maximum number of rows to return
Basic Select
used to select data from a database
Select */List of Column_name
From Table_name
Intersect,Union,Except
Union: used to combine the result set of two or more Select statements
Intersect: Return only the distinct row that is common in two or more Select statements
Except: Return all distinct rows in the first Select statement that are not returned by the second Select statement
Select List of Column_name1
From Table_name1
Where Condition1
Union/Intersect/Except[All]
Select List of Column_name2
From Table_name2
Where Condition2
Every Select statement must have the same number of columns
The columns must have similar data types
The columns in every Select statement must be in the same order
The name of the column in the first Select statement will be the column names of the result set
Add All to not remove the duplicate values from the result set
Join
Inner Join
selects records that have matching values in both tables
Select List of Column_name
From Table_name t1
Inner Join Table_name t2
On t1.Column_name=t2.Column_name
To write the Join clause quicker, you can use the alias
Concat function: use to combine two or many strings
Concat(String1, String2,...)
Left Join
returns all records from the left table and the matching records from the right table
Select List of Column_name
From Table_name t1
Left Join Table_name t2
On t1.Column_name=t2.Column_name
Right Join
returns all records from the right table and the matching records from the left table
Select List of Column_name
From Table_name t1
Right Join Table_name t2
On t1.Column_name=t2.Column_name
Self Join
is a regular Join, but the table join with itself
Select List of Column_name
From Table_name t1
Right/Left/Inner Table_name t2
On t1.Column_name=t2.Column_name
Tích Descartes
Select List of Column_name
From Table_name1,Table_name2
Where Join_condition
The result from tích Descartes and Inner Join are the same but the speed for using Inner Join is much quicker than using tích Descartes
Function
Aggregation Function
Group By
Group By: Group rows that have the same values into summary rows, are often used with the numeric functions to group the result set
Group By-Having: Put after the Where clause to apply conditions for the result set after perform Where clause
Select List of Column_name,
Numeric_Function(Column_name)
From Table_name[Where Condition(s)]
Group By Column_name(s)
[Having Condition(s)]
:fountain_pen: Sumarize for multiple columns:
Sum(Column_name1+Column_name2)
Partition By
If we want to aggregate data over a small set of rows instead of an entire table, we will use Partition By clause
Usually combine with the Over clause to specify the column on which we need to perform aggregation
Select List of Column_name,
Numeric_Function(Column_name)
Over(Partition By Column_name)
From Table_name
String Functions
Concat: Add several strings together
Lower: Convert the text to lower-case
Ltrim: Remove leading spaces from a string
Rtrim: Remove trailing spaces from a string
Trim: Remove leading and trailing spaces from a string
Upper: Convert the text to upper-case
Replace: Replace all occurrences of a substring within a string, with a new substring
Concat(string1,string2,...)
Lower(text);Upper(text)
Ltrim(string);Rtrim(string);Trim(string)
Replace(String,From_string,New_string)
:check:String: Original string
:check:From_string: The substring to be replaced
:check: The new replacement substring
Numeric Functions
Avg: Return the average value of an expression
Sum: Calculate the sum of an expression
Min: Return the minimum value in an expression
Max: Return the maximum value in an expression
Count: Return the number of records returned by a Select query
Round: round a number to a specified number of decimal places
Avg(Column_name)
:fountain_pen: Null values are ignored
Sum(Column_name)
:fountain_pen: Null values are ignored
Min(Column_name)
Max(Column_name)
Count(Column_name)
:fountain_pen: Null values are not counted
Round(Column_name,decimals)
Date Functions
Date_Add: Add a time/date interval to the date and then return a date
DatedIff: Return the number of days between two date values
Day: Return the day of the month for a date
Month: Return the month part of a date
Year: Return the year part of a date
Str_to_date: convert the string into the date based on the string format
Date_Add(Date,Interval Value Addunit)
:fountain_pen: Addunit:The type of interval to add (Second, Minute, Hour, Day, Week, Month,Quarter, Year)
DatedIff(Date1,Date2)
Day(Date)
Month(Date)
Year(Date)
Str_to_date(String,Format)
Ex: Str_to_date(Column_name,"%d/%m/%Y")
Advanced Functions
Convert: Convert a value into the specified data type or character set
Case: go through conditions and return a value when the first condition is met
If: Return a value if a condition is true or another value if a condition is false
IsNull: Test whether an expression is Null
NullIf: compare 2 expressions and return Null if they are equal. Otherwise, return the first expression
Convert(Value,Type)
Convert(Value Using Charset)
Case When Condition1 Then Result1
When Condition2 Then Result2
When ConditionN Then ResultN
Else Result End
If(Condition,Value_if_true,Value_if_false)
IsNull(Expression)
:check: Is Null: Return 1
:check: Is not Null: Return 0
NullIf(Expr1,Expr2)
TERM
Database (DBS):
an organized collection
of structured information
Table: a collection of related data organized in a row-and-column format
Record: each row in a table is a record
Primary Key (PK)
a special relational database table column (or combination of columns) that's distinctive for each record
=Unique+Not Null
Create PK: Create Table_name
Primary Key(Column_name)
Add PK: Alter Table Table_name
Add Primary Key (Column_name)
Change PK: Alter Table Table_name
Drop Primary Key
Add Primary Key(Column_name)
Delete PK: Alter Table Table_name
Drop Primary Key
PK for multiple column:
Primary Key(Column_name1,Column_name2)
Foreign Key (FK)
a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table
Create FK: Create Table Table_name
Foreign Key(Column_name) References
Table_name(Column_name)
Add FK: Alter Table Table_name
Add Foreign Key(Column_name) References Table_name(Column_name)
Change FK: Alter Table Table_name
Drop Foreign Key FK_symbol
FK when update or delete:
Foreign Key(Column_name) References
Table_name(Column_name)
On Update reference_options
On Delete reference_options
:warning: Records in child data will be changed when delete or update records in parent table:
Cascade: delete or update data in child table
Set Null: Return NULL value for data in child table
Set Default: Return default value for data in child table
:warning: Records in child data will not be changed when delete or update records in parent table: Restrict, No Action
UniqueKey
UNIQUE constraint ensures that all values in a column are different
can have many UNIQUE constraints per table
Can appear NULL value only one time
Create UK: Create Table Table_name
Unique Key(Column_name)
Add UK: Alter Table Table_name
Add Unique Key(Column_name)
Drop UK: Alter Table Table_name
Drop Index UK_Symbol
Null
a missing unknown value. A field with NULL value is a field with no value
Test for NULL value:
Select column_name
From table_name
Where column_name is NULL/NOTNULL
