SQL 思维导图模板_ProcessOn思维导图、流程图

SELECT

SELECT column_name,column_name FROM table_name;

SELECT DISTINCT column_name,column_name FROM table_name;

DISTINCT

去重

SELECT column_name,column_name FROM table_name WHERE column_name operator value;

AND OR

OREDR BY 

ASC

DESC

SELECT TOP number|percent column_name(s) FROM table_name;

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

%

0或多个字符

_

一个字符

[]

[^]   [!]

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);

BETWEEN

AS

别名

JOIN

INNER

LEFT

RIGHT

FULL

UINON

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

合并多个select语句

SELECT * INTO newtable [IN externaldb] FROM table1;

表复制

INSERT INTO table2 SELECT * FROM table1;

复制插入

INSERT

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

UPDATE

UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;

DELETE

DELETE FROM table_name WHERE some_column=some_value;

CREATE

CREATE DATABASE dbname;

CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... );

CREATE INDEX index_name ON table_name (column_name)

SHOW

SHOW databases

SHOW tables

SHOW full columns from table

desc column

DROP

DROP DATABASE database_name

DROP TABLE table_name

ALTER TABLE table_name DROP INDEX index_name

TRUNCATE TABLE table_name

只删数据

ALTER

改变

SOURCE

AUTO_INCREMENT

NULL

IS NULL

IS NOT NULL

IFNULL()

约束

规定表中的数据规则，违反则终止

CREATE TABLE或ALTER TABLE时添加

NOT NULL

UNIQUE

PRIMARY KEY

FOREIGN KEY

CHECK

DEFAULT

数据类型

Text

CHAR(size)

固定长度字符串 255

VARCHAR(size)

可变长度字符串 255

TINYTEXT

255

TEXT

65535

MEDIUMTEXT

16,777,215

LONGTEXT

4,294,967,295

BLOB

Binary Large OBjects  65535

MEDIUMBLOB

LONGBLOB

ENUM(x,y,z,etc.)

SET

Number

TINYINT(size)

-128，127

SMALLINT(size)

-32768，32767

MEDIUMINT(size)

-8388608 ，83886087

INT(size)

-2147483648，2147483647

BIGINT(size)

-9223372036854775808，9223372036854775807

FLOAT(size,d)

size 最大位数

d 小数点右边最大位数

DOUBLE(size,d)

DECIMAL(size,d)

作为字符串存储的DOUBLE

Date

DATE()

TIMESTAMP

DATETIME()

YYYY-MM-DD HH:MM:SS

TIMESTAMP()

Unix 时间戳

TIME()

HH:MM:SS

YEAR()

子主题

函数

Aggregate函数

聚合函数

一列返回一个单一的值

AVG()

COUNT()

FIRST()

LAST()

MAX()

MIN()

SUM()

Scale函数

标量函数

每个输入返回一个单一的值

UCASE()

LCASE()

MID()

LEN()

ROUND()

NOW()

FORMAT()

连接池