Features
Features:

Product Tour >

Edraw AI >

Paid Plans:

Individuals >

Business >

Eduaction >
Resources
Blog

History

How-tos & Tips

Discovery

Biography

Business Analysis

Examples

AI concept Map

Free AI Mind Map Generator

Onenote Mind Map

Bcg Matrix Examples

Nike Marketing Strategy

Unilever SWOT Analysis

Make Mind Maps in Google Docs

Guide

FAQs

What's New

Resource Center
Templates
All Templates

Brain Storming Templates

Strategy and Planning Templates

Project Management Templates

Product Management Templates

Human Resources Templates

Agile Workflow Templates

Marketing Templates

Education Templates

Fun and Games Templates

User Gallery
Download
Pricing
Enterprise

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

PlotWizard

Recent works View more works>>

SQL mind map

PlotWizard

Recent works View more works>>

Recommended to you
Outline

SQL
- 1.4k
- 6
Study Smarter
SQL Command and Concept Guide
- 124
Vanh Pham
Roadmap to create course for Data analysis with SQL
- 84
- 3
- 1
Deepak Patra ICE
Database introduction
- 35
- 1
PlotWizard
SQL learning summary
- 151
- 3
PlotWizard
SQL脑图
- 47
Deu-Martina

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

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

/* */

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()

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;