Wondershare EdrawMind
EdrawMindSQL mind map
MindMap Gallery SQL mind map
- 164
- 4
SQL mind map
A picture to understand the basic syntax of SQL, including DDL, DML, DCL, TCL, functions... If you like it, you can like it and save it~
Edited at 2023-10-19 17:04:04- cáncer de pulmón
El cáncer de pulmón es un tumor maligno que se origina en la mucosa bronquial o las glándulas de los pulmones. Es uno de los tumores malignos con mayor morbilidad y mortalidad y mayor amenaza para la salud y la vida humana.
- diabetes
La diabetes es una enfermedad crónica con hiperglucemia como signo principal. Es causada principalmente por una disminución en la secreción de insulina causada por una disfunción de las células de los islotes pancreáticos, o porque el cuerpo es insensible a la acción de la insulina (es decir, resistencia a la insulina), o ambas cosas. la glucosa en la sangre es ineficaz para ser utilizada y almacenada.
- sistema digestivo
El sistema digestivo es uno de los nueve sistemas principales del cuerpo humano y es el principal responsable de la ingesta, digestión, absorción y excreción de los alimentos. Consta de dos partes principales: el tracto digestivo y las glándulas digestivas.
SQL mind map
- cáncer de pulmón
El cáncer de pulmón es un tumor maligno que se origina en la mucosa bronquial o las glándulas de los pulmones. Es uno de los tumores malignos con mayor morbilidad y mortalidad y mayor amenaza para la salud y la vida humana.
- diabetes
La diabetes es una enfermedad crónica con hiperglucemia como signo principal. Es causada principalmente por una disminución en la secreción de insulina causada por una disfunción de las células de los islotes pancreáticos, o porque el cuerpo es insensible a la acción de la insulina (es decir, resistencia a la insulina), o ambas cosas. la glucosa en la sangre es ineficaz para ser utilizada y almacenada.
- sistema digestivo
El sistema digestivo es uno de los nueve sistemas principales del cuerpo humano y es el principal responsable de la ingesta, digestión, absorción y excreción de los alimentos. Consta de dos partes principales: el tracto digestivo y las glándulas digestivas.
- Recommended to you
- Outline
SQL
DROP DATABASE / TABLE IF EXISTS name
USE DATABASE / TABLE name; SHOW DATABASE / TABLE name; DESC DATABASE / TABLE name;
DDL
CREATE
Create a new database
CREATE DATABASE database name;
Create new table
CREATE TABLE table name (field 1 data type [whether it is empty, default value, auto-increment or not, remarks], Field 1 data type [whether it is empty, default value, remarks]);
Set fields
Set primary key
CONSTRAINT key name PRIMARY KEY (field name)
Set foreign keys
CONSTRAINT key name FOREIGN KEY (field name) REFERENCES primary key table (primary key field)
Set whether it is empty
Default NULL / NOT NULL
Is the setting unique?
UNIQUE
Setup check
CHECK(field name judgment condition)
If the CHECK condition is not met, the value cannot be entered.
Set default value
DEFAULT
Set auto-increment or not
AUTO_INCREMENT
A table can only set one
Set notes
COMMENT 'comment name'
New view
CREATE VIEW view name AS SELECT * FROM table;
Create new index
Create a new unique index
CREATE UNIQUE INDEX index name ON table name(field);
Create a new single index
CREATE INDEX index name ON table name(field name);
Create a new combined index
CREATE INDEX index name ON table name (field 1, field 2);
ALTER
Change table name
ALTER TABLE table name RENAME TO new table name;
Add field
ALTER TABLE table name ADD (field 1 data type [whether it is empty, default value, auto-increment or not, remarks]);
Change fields
ALTER TABLE table name MODIFY (field 1 data type [whether it is empty, default value, increment or not, remarks]);
Delete field
ALTER TABLE table name DROP COLUMN field name;
RENAME
DROP
Delete table
Delete table only
DROP TABLE table name;
Related reference relationships are deleted together
DROP TABLE table name CASCADE CONSTRAINT;
Delete view
DROP VIEW view name;
DML
INSERT
INSERT INTO table name[(field)] VALUES(first instance value),(second instance value);
INSERT TNTO table 2 people SELECT * FROM table 1 name;
UPDATE
UPDATE table name SET field='new value' WHERE field name='value';
When there is no WHERE restriction, all data in the field will be replaced.
DELETE
DELETE FROM table name;
Delete table content only
TRUNCATE
TRUNCATE FROM table name;
Delete the table content and initialize the table
SELECT
FROM
AS alias
If an alias is set in the FROM statement, the SELECT statement must use the alias
JION
[INNER] JOIN
Nested loop
LEFT / RIGHT [OUTER] JOIN
FULL [OUTER] JOIN
CROSS JOIN
Cartesian product
UNION
UNION
Merge and sort, removing duplicate values
UNION ALL
Only merge without removing duplicate values
WHERE
=, <, >, <=, >=
<>, !=, !<, !>, !<=, !>=
BETWEEN AND
WHERE field name BETWEEN n1 AND n2
IS NULL / IS NOT NULL
IN, AND, OR, NOT
LIKE wildcard
%
multiple characters
-
single character
[]
WHERE field name LIKE '[Character 1 Character 2]%'; satisfy both
^
WHERE field name LIKE '[^ character 1 character 2]%'; not satisfied at the same time
ROWNUM
WHERE ROWNUM <= 3
ROWID
unique
Object_num(1-6) file_num(7-9) block_num(10-15) data_num(16-18)
conditional judgment
EXISTS
If EXISTS is TRUE, return the SELECT statement query content.
ANY
SELECT * FROM table name WHERE field > ANY(subquery); As long as the field is greater than any subquery, the SELECT * content is returned.
SOME
SELECT * FROM table name WHERE field > ALL (subquery); If the field is greater than all values of the subquery, the SELECT * content will be returned.
GROUP BY
COUNT() / SUM() / AVG() / MAX() / MIN() / STDDEV()Standard deviation / VARIAN()
MySQL
HAVING
Combination function
ROLLUP
SELECT field 1, field 2, SUM (numeric field) FROM table name GROUP BY ROLLUP(Field1,[Field2]);
Group by field 1 and field 2 and sum, then group by field 2 and sum
GROUPING
SELECT field 1, GROUPING(field 1), field 2, ROUPING(field 2), SUM(numeric field) FROM table name GROUP BY ROLLUP(Field1,[Field2]);
Determine whether values are grouped by ROLLUP
GROUPING SETS
SELECT field 1, field 2, SUM (numeric field) FROM table name GROUP BY GROUPING SETS(Field 1,[Field 2]);
Group by field 1 and sum, then group by field 2 and sum
CUBE
SELECT field 1, field 2, SUM (numeric field) FROM table name GROUP BY CUBE(Field1,[Field2]);
Group by field 1 and field 2 and sum, then group by field 1 and sum, then group by field 2 and sum
ORDER BY
ASC
DESC
limit
LIMIT
FROM table name LIMIT n1 [OFFSET n2] (take n2 data starting from row n1)
MySQL
TOP
SELECT TOP(num) FROM table name;
SQL Server
FETCH
FETCH FRIST n ROWS ONLY;
DISTINCT
DCL
GRANT
GRANT permission name ON project TO username;
SELECT / INSERT / UPDATE / DELETE / REFERENCES / ALTER / INDEX / ALL
ORACLE
WITH GRANT OPTION / WITH ADMIN OPTION
REVOKE
REVOKE permission name ON project TO username;
TCL
COMMIT
ROLLBACK
SAVEPOINT
SAVEPOINT saves roll call;
SAVE TRANSACTION save roll call;
SELECT DISTINCT field name (exclude duplicates)
RELEASE SAVEPOINT Save roll call;
Transaction
characteristic
Atomictiy: A transaction is an indivisible whole
Consistency: Relevant data remains consistent before and after transaction execution (for example: when moving a database, the total inventory must be consistent)
Isolation: Transactions are isolated from each other
Read Uncommitted
Read Committed
Repeatable Read
Serializable
Durability: After the transaction is executed, the data is permanent, that is, it cannot be recovered
step
SET AUTOCOMMIT = 0
BEGIN ... COMMIT
SET / START / BEGIN TRANSACTION ... COMMIT
Comment
-- space
Single line comments
#
Single line comments
/* */
Multi-line comments
function
Data format interchange
format
CAST(field AS data type)
ORACLE
SQL Server
text processing
text formatting
TO_CHAR()
TO_CHAR(numeric field/date field, [FORMAT])
Convert numbers and dates to text
SQL Server None
Splicing
Operator
field 'symbol'/field 'symbol'/field
|| operator
Field||'Symbol'/Field||'Symbol'/Field
CONCAT operator
Concat(field,'symbol'/field,'symbol'/field)
MySQL
ORACLE
SQL Server
Remove specified characters Default space
both sides
TRIM()
TRIM(field,'specified character')
SQL Server
ORACLE None
TRIM('Specific character' FROM 'Specified string')
TRIM(BOTH...FROM...)
TRIM(BOTH 'specified character' FROM field)
ORACLE
SQL Server None
left
LTRIM()
LTRIM(field,'specified character');
ORACLE None
SQL Server
TRIM(LEADING...FROM...)
TRIM(LEADING 'specified character' FROM field)
ORACLE
SQL Server None
right
RTRIM()
RTRIM(field,'specified character')
ORACLE None
SQL Server
TRIM(TRAILING...FROM...)
TRIM(TRAILING 'specified character' FROM field)
ORACLE
SQL Server None
Fill with specified characters Default space
LPAD()
LPAD(field, total string length, 'padding characters')
ORACLE None
SQL Server
RPAD()
RPAD(field, total string length, 'padding characters')
ORACLE None
SQL Server
Swap case
UPPER()/LOWER()
ORACLE
SQL Server
Extract characters
LEFT() / RIGHT()
ORACLE
SQL Server
SUBSTR()
SUBSTR (field, starting character position, output character length)
ORACLE None
SQL Server
SUBSTRING()
SUBSTRING (field, starting character position, output character length)
ORACLE
SQL Server
SUBSTRING_INDEX()
SUBSTRING_INDEX(field,'index',retrieve the number of occurrences)
ORACLE None
SQL Server
Replace character
REPLACE()
REPLACE(field,'original character','replacement character')
ORACLE
SQL Server
TRANSLATE()
TRANSLATE(field,'original character combination','replacement character combination')
SQL Server None
Find character location
INSTR()
INSTR(field,'specified character', starting character position, occurrence sequence number)
MySQL
SQL Server
string length
LENGTH()
MySQL
SQL Server
DATALENGTH()
SQL Server None
ORACLE None
LEN()
SQL Server None
NULL
IFNULL()
IFNULL(field,'display string')
SQL Server
ORACLE
NULLIF()
NULLIF(Field 1, Field 2)
If field 1 and field 2 are the same, NULL is returned. If different, return field 1
SQL Server
ORACLE
NVL()
NVL(field,'replacement value for null')
ORACLE
SQL Server None
NVL2()
NVL2(field,1,0) returns 0 if the field is NULL, otherwise returns 1
ORACLE
SQL Server None
COALESCE()
COALESCE(field1,field2,field3,...) returns the first non-null value
MySQL
SQL Server
ASCII()
ASCII('character') returns the American Standard Code for Information Interchange of the leftmost character of the string
SQL Server
ORACLE
phonetic text
SOUNDEX()
SOUNDEX value of the feedback string (voice representation value) Output data by similar pronunciation
SQL Server
Date processing
date formatting
TO_DATE()
TO_DATE(text field,[FORMAT])
MySQL
ORACLE
TO_DATETIME()
TO_DATETIME(text field,[FORMAT])
SQL Server
ORACLE
STR_TO_DATE()
STR_TO_DATE('date format string','parsing format')
SQL Server
DATE_TO_FORMAT()
DATE_TO_FORMAT('date format string','parsing format')
SQL Server None
DATE_FORMAT()
DATE_FORMAT(field,'%Y%M')
SQL Server
Extract year, month and day
YEAR() / MONTH() / DAY()
MySQL
SQL Server
DATE()
MySQL
SQL Server
DATETIME()
MySQL
SQL Server None
TIMESTAMP()
MySQL
SQL Server
DATEPART()
DATEPART(time_type,field name)
SQL Server
DATE_PART()
DATE_PART('time_type',field name)
PostgreSQL
SQL Server None
EXTRACT()
EXTRACT(time_type FROM field name)
MySQL
ORACLE
SQL Server
TODATE()
TODATE (field name, time_type)
ORACLE
STRFTIME()
STRFTIME('%Y' / '%M' / '%D', field name)
Current date and time
date
GETDATE()
SQL Server
CURDATE() CURRENT_DATE()
MySQL
SQL Server
time
CURTIME() CURRENT_TIME()
MySQL
SQL Server
date and time
NOW()
MySQL
SQL Server
SYSDATE()
MySQL
SQL Server
ORACLE
SYSDATETIME()
SQL Server
time apart
DATEDIFF()
DATEDIFF(time_type, date1 field, date2 field)
date2-date1 interval
MySQL
SQL Server
future time
increase time
ADD_MONTHS()
ADD_MONTHES(field name, num)
ORACLE
DATEADD()
DATEADD(time_type, num, field name)
MySQL
DATE_ADD() / DATE_SUB()
DATE_ADD(field name, INTERVAL num time_type) DATE_SUB(field name, INTERVAL num time_type)
MySQL
SQL Server
specified time in the future
EOMONTH()
EOMONTH(field,[num]) The last day of the month, num can be adjusted to the month num 1, which is the next month, and -1 which is the previous month.
SQL Server
specific date or time
DAYNAME()
MONTHNAME()
YEARNAME()
MySQL
SQL Server
DAYOFWEEK() DAYOFMONTH() DAYOFYEAR()
MySQL
SQL Server
Number crunching
Numeric formatting
TO_NUMBER()
TO_NUMBER (text field)
Convert text to NUMBER
CEIL/CEILING FLOOR()
CEIL/FLOOR(x)
Returns the value of x rounded up/down
ROUND()
ROUND(x, y)
Returns the rounded value of x, y represents the number of digits
TRUNC()
TRUNC(x, y)
Intercept numbers, y represents the number of digits
Calculation function
SIGN()
SIGH(x)
Returns -1 for negative numbers, 1 and 0 for positive numbers
MOD()
MOD(x,y)
Returns the remainder of x/y
SQRT()
SQRT(x)
Returns the square root of x
ABS()
ABS(x)
Returns the absolute value of x
PI()
PI()
Returns the value of pi, type FLOAT floating point type
Power function
EXP()
EXP(x)
Returns e raised to the x power
POWER()
POWER(x,y)
Returns the result of x^y power
exponential function
LOG()
LOGe(x)
Logarithm of x with base e
LOG10()
LOG10(x)
Base 10 logarithm of x
Trigonometric functions
SIN()
SIN (angle)
Returns the sine of an angle
TAN()
TAN (angle)
Returns the tangent of an angle
COS()
COS (angle)
Returns the cosine of an angle
Common data types
FLOAT
floating point value
CHAR
1~255 fixed-length strings
INT
4-byte integer value, supports numbers from -2147483648 to 2147483647
DECIMAL/NUMBERIC
Fixed-point or variable-precision floating-point values
DATE
date value
TIME
time value
DATETIME/TIMESTAMP
datetime value
SELECT window function([field]) OVER (PARTITION BY field ORDER BY WINDOWING clause)
Very common data types
NCHAR
Special form of CHAR, supporting multibyte or Unicode characters
NVARCHAR
Special form of TEXT, supporting multibyte or Unicode characters
TEXT(LONG,MEMO,VARCHAR)
variable length text
BIT
Binary bit value, 0 or 1
REAL
4-byte floating point value
SMALLINT
2-byte integer value, supports -32768~32767
TINYINT
1-byte integer value, supports numbers from 0 to 255
SAMLLDATETIME
Datetime value, accurate to minutes (no seconds or milliseconds)
BINARY
Fixed-length binary data (maximum length from 255B to 8000B)
LONG RAW
Variable length binary data, up to 2GB
RAW.BINARY
Variable length binary data, up to 255B
VARBINARY
Become binary data (the maximum length is generally between 255B and 8000B)
Other functions
conditional decision function
IF()
IF (condition, return value if the condition is TRUE, return value if the condition is FALSE)
CASE
CASE WHEN condition 1 THEN value 1 WHEN condition 2 THEN value 2 ... ELSE valuen END
DECODE()
When comparing multiple parameters
DECODE(field,'Character 1','Translation value 1','Character 2','Translation value 2'...,'Other values')
SQL Server None
When comparing individual parameters
DECODE(field,'character',return value when field=character, return value when field!=character)
SQL Server None
window function (Window Function)
window function
Sorting function
RANK
The same order is represented by the same number, and omitted orders are not counted. Example: 1, 2, 2, 4, 5
DENSE_RANK
The same order is represented by the same number, and the order is recorded as usual without omission. Example: 1, 2, 2, 3, 4
ROW_NUMBER
Return fixed row number
Aggregation function
SUM/AVG/COUNT/MAX/MIN
special value function
FIRST_VALUE
The first value that appears in the group after grouping
LAST_VALUE
The last value that appears in the group after grouping
LAG
Use the value 1 position above the left column as the fill value
LEAD
Use the value 1 digit below the left column as the fill value
ratio function
CUME_DIST
The cumulative percentage after grouping is less than or equal to the current value
PRECENT_RANK
The first value that appears after grouping is 0, the last value is 1 (100%), and the percentages are assigned in order of appearance
NTILE
SELECT department, payroll NTILE(4) OVER (ORDER BY salary DESC) AS N_tile FROM employee table;
4 groups of 1 marks after sorting by salary
RATIO_TO_REPORT
Calculate the percentage of each row/sum after grouping (data type is floating point)
WINDOWING
ROWS
RANGE
BETWEEN~AND
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW
SELECT SUM(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) total FROM employee table;