导图社区 一张图掌握SQL常用语句
- 256
- 13
- 1
- 举报
一张图掌握SQL常用语句
一张图掌握SQL常用语句，并且按照数据库进性区分整理，有助于帮助您熟悉知识要点，加强记忆。有需要的同学，可以收藏下哟。
编辑于2021-12-01 16:17:56- 相似推荐
- 大纲
SQL
DROP DATABASE / TABLE IF EXISTS 名
USE DATABASE / TABLE 名； SHOW DATABASE / TABLE 名; DESC DATABASE / TABLE 名;
DDL
CREATE
新建数据库
CREATE DATABASE 数据库名;
新建表
CREATE TABLE 表名(字段1 数据类型 [是否为空 默认值 自增与否 备注], 字段1 数据类型 [是否为空 默认值 备注]);
设置字段
设置主键
CONSTRAINT 键名 PRIMARY KEY(字段名)
设置外键
CONSTRAINT 键名 FOREIGN KEY(字段名) REFERENCES 主键表(主键字段)
设置是否为空
默认NULL / NOT NULL
设置是否唯一
UNIQUE
设置检查
CHECK(字段名 判定条件)
如果不满足CHECK条件，则值不能被录入
设置默认值
DEFAULT
设置自增与否
AUTO_INCREMENT
一个表格只能设置一个
设置备注
COMMENT '备注名'
新建视图
CREATE VIEW 视图名 AS SELECT * FROM 表;
新建索引
新建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(字段);
新建单索引
CREATE INDEX 索引名 ON 表名(字段名);
新建组合索引
CREATE INDEX 索引名 ON 表名(字段1,字段2);
ALTER
更改表名
ALTER TABLE 表名 RENAME TO 新表名;
添加字段
ALTER TABLE 表名 ADD (字段1 数据类型 [是否为空 默认值 自增与否 备注]);
变更字段
ALTER TABLE 表名 MODIFY (字段1 数据类型 [是否为空 默认值 自增与否 备注]);
删除字段
ALTER TABLE 表名 DROP COLUMN 字段名;
RENAME
DROP
删除表
仅删除表
DROP TABLE 表名;
相关参照关系一同删除
DROP TABLE 表名 CASCADE CONSTRAINT;
删除视图
DROP VIEW 视图名;
DML
INSERT
INSERT INTO 表名[(字段)] VALUES(第一个instance值),(第二个instance值);
INSERT TNTO 表2名 SELECT * FROM 表1名;
UPDATE
UPDATE 表名 SET 字段='新值' WHERE 字段名='值';
无WHERE限制时字段全部数据都被替换
DELETE
DELETE FROM 表名;
仅删除表格内容
TRUNCATE
TRUNCATE FROM 表名;
删除表格内容,并初始化表格
SELECT
FROM
AS 别名
如果FROM语句中设置了别名，SELECT语句就必须使用别名
JION
[INNER] JOIN
嵌套循环(Nested loop)
LEFT / RIGHT [OUTER] JOIN
FULL [OUTER] JOIN
CROSS JOIN
笛卡尔积(Cartesian product)
UNION
UNION
合并并排序，剔除重复值
UNION ALL
仅合并不剔除重复值
WHERE
=, <, >, <=, >=
<>, !=, !<, !>, !<=, !>=
BETWEEN AND
WHERE 字段名 BETWEEN n1 AND n2
IS NULL / IS NOT NULL
IN, AND, OR, NOT
LIKE 通配符
%
多个字符
-
单个字符
[]
WHERE 字段名 LIKE '[字符1字符2]%'; 同时满足
^
WHERE 字段名 LIKE '[^字符1字符2]%'; 同时不满足
ROWNUM
WHERE ROWNUM <= 3
ROWID
具有唯一性
Object_num(1-6)+file_num(7-9)+block_num(10-15)+data_num(16-18)
条件判定
EXISTS
如果EXISTS后为TRUE返回SELECT语句查询内容
ANY
SELECT * FROM 表名 WHERE 字段 > ANY(子查询); 只要字段大于任一子查询则返回SELECT *内容。
SOME
SELECT * FROM 表名 WHERE 字段 > ALL(子查询); 字段大于子查询所有值则返回SELECT *内容。
GROUP BY
COUNT() / SUM() / AVG() / MAX() / MIN() / STDDEV()标准差 / VARIAN()
MySQL
HAVING
组合函数
ROLLUP
SELECT 字段1, 字段2, SUM(数字字段) FROM 表名 GROUP BY ROLLUP(字段1,[字段2]);
按字段1+字段2分组后求和，再按字段2分组后求和
GROUPING
SELECT 字段1, GROUPING(字段1), 字段2, ROUPING(字段2), SUM(数字字段) FROM 表名 GROUP BY ROLLUP(字段1,[字段2]);
判定值是否通过ROLLUP分组
GROUPING SETS
SELECT 字段1, 字段2, SUM(数字字段) FROM 表名 GROUP BY GROUPING SETS(字段1,[字段2]);
按字段1分组后求和，再按字段2分组后求和
CUBE
SELECT 字段1, 字段2, SUM(数字字段) FROM 表名 GROUP BY CUBE(字段1,[字段2]);
按字段1+字段2分组后求和，再按字段1分组后求和, 再按字段2分组后求和
ORDER BY
ASC
DESC
限制
LIMIT
FROM 表名 LIMIT n1 [OFFSET n2]（从第n1行开始取n2个数据）
MySQL
TOP
SELECT TOP(num) FROM 表名;
SQL Server
FETCH
FETCH FRIST n ROWS ONLY;
DISTINCT
DCL
GRANT
GRANT 权限名 ON 项目 TO 用户名;
SELECT / INSERT / UPDATE / DELETE / REFERENCES / ALTER / INDEX / ALL
ORACLE
WITH GRANT OPTION / WITH ADMIN OPTION
REVOKE
REVOKE 权限名 ON 项目 TO 用户名;
TCL
COMMIT
ROLLBACK
SAVEPOINT
SAVEPOINT 保存点名;
SAVE TRANSACTION 保存点名;
SELECT DISTINCT 字段名（排除重复）
RELEASE SAVEPOINT 保存点名;
事务 TRANSATION
特性
原子性(Atomictiy): 事务是一个整体不可分割
一致性(Consistency): 事务执行前后相关数据保持一致性(例: 移库时，库存总量需一致)
隔离型(Isolation): 事务于事务之间具有隔离性
1、丢失更新(Lost update) 两个事务都同时更新一行数据,撤销一个事务时，把其他事务已提交的更新数据覆盖(A和B事务并发执行，A事务执行更新后，提交；B事务在A事务更新后，B事务结束前也做了对该行数据的更新操作，然后回滚，则两次更新操作都丢失了) 2、脏读(Dirty Reads) 一个事务读到另一个事务未提交的更新数据(A和B事务并发执行，B事务执行更新后，A事务查询B事务没有提交的数据，B事务回滚，则A事务得到的数据不是数据库中的真实数据。也就是脏数据，即和数据库中不一致的数据)。 3、非重复读(Non-repeatable Reads) 一个事务对同一行数据重复读取两次，但是却得到了不同的结果(A和B事务并发执行，A事务查询数据，然后B事务更新该数据，A再次查询该数据时，发现该数据变化了) 4、二类丢失更新(覆盖更新 Second lost updates problem) 无法重复读取的特例。有两个并发事务同时读取同一行数据，然后其中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成第一次写操作失效(即A事务更新数据，然后B事务更新该数据，A事务查询发现自己更新的数据变了)。 5、幻读(Phantom Reads) 事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据(A和B事务并发执行，A事务查询数据，B事务插入或者删除数据，A事务再次查询发现结果集中有以前没有的数据或者以前有的数据消失了)。  ———————————————— 版权声明：本文为CSDN博主「cm_chenmin」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/cm_chenmin/article/details/53106174
未提交读(Read Uncommitted)
提交读(Read Committed)
可重复读(Repeatable Read)
串行读(Serializable)
永久性(Durability): 事务执行完后数据具有永久性，即不能恢复
步骤
SET AUTOCOMMIT = 0
BEGIN ... COMMIT
SET / START / BEGIN TRANSACTION ... COMMIT
注释
-- 空格
单行注释
#
单行注释
/* */
多行注释
函数
数据格式互换
格式化
CAST(字段 AS 数据类型)
ORACLE
SQL Server
文本处理
文本格式化
TO_CHAR()
TO_CHAR(数字字段/日期字段, [FORMAT])
将数字和日期转化为文本
SQL Server 无
拼接
+ 操作符
字段+'符号'/字段+'符号'/字段
|| 操作符
字段||'符号'/字段||'符号'/字段
CONCAT 操作符
Concat(字段,'符号'/字段,'符号'/字段)
MySQL
ORACLE
SQL Server
去除指定字符 默认空格
两边
TRIM()
TRIM(字段,'指定字符')
SQL Server
ORACLE 无
TRIM('特定字符' FROM '指定字符串')
TRIM(BOTH...FROM...)
TRIM(BOTH '指定字符' FROM 字段)
ORACLE
SQL Server 无
左边
LTRIM()
LTRIM(字段,'指定字符');
ORACLE 无
SQL Server
TRIM(LEADING...FROM...)
TRIM(LEADING '指定字符' FROM 字段)
ORACLE
SQL Server 无
右边
RTRIM()
RTRIM(字段,'指定字符')
ORACLE 无
SQL Server
TRIM(TRAILING...FROM...)
TRIM(TRAILING '指定字符' FROM 字段)
ORACLE
SQL Server 无
填充指定字符 默认空格
LPAD()
LPAD(字段,总字符串长度,'填充字符')
ORACLE 无
SQL Server
RPAD()
RPAD(字段,总字符串长度,'填充字符')
ORACLE 无
SQL Server
互换大小写
UPPER() / LOWER()
ORACLE
SQL Server
提取字符
LEFT() / RIGHT()
ORACLE
SQL Server
SUBSTR()
SUBSTR(字段,开始字符位置,输出字符长度)
ORACLE 无
SQL Server
SUBSTRING()
SUBSTRING(字段,开始字符位置,输出字符长度)
ORACLE
SQL Server
SUBSTRING_INDEX()
SUBSTRING_INDEX(字段,'索引',检索出现次数位置)
ORACLE 无
SQL Server
替换字符
REPLACE()
REPLACE(字段,'原字符','替换字符')
ORACLE
SQL Server
TRANSLATE()
TRANSLATE(字段,'原字符组合','替换字符组合')
SQL Server 无
查找字符所在位置
INSTR()
INSTR(字段,'指定字符', 开始字符位置,出现序数 )
MySQL
SQL Server
字符串长度
LENGTH()
MySQL
SQL Server
DATALENGTH()
SQL Server 无
ORACLE 无
LEN()
SQL Server 无
NULL
IFNULL()
IFNULL(字段,'显示字符串')
SQL Server
ORACLE
NULLIF()
IFNULL(字段1,字段2)
字段1和字段2如果相同，返回NULL, 如果不同，返回字段1
SQL Server
ORACLE
NVL()
NVL(字段,'字段为null的替换值')
ORACLE
SQL Server 无
NVL2()
NVL2(字段,1,0) 字段为NULL返回0，否则返回1
ORACLE
SQL Server 无
COALESCE()
COALESCE(字段1,字段2,字段3,...) 返回第一个非null值
MySQL
SQL Server
ASCII()
ASCII('字符')返回字符串最左侧字符的美国信息交换标准码
SQL Server
ORACLE
音似文本
SOUNDEX()
反馈字符串的SOUNDEX值 (语音表示值) 通过相似的发音输出数据
原: SELECT cust_name, cust_contact FROM Customers WHERE cust_contact = 'Michael Green' 结果 空白 修改: SELECT cust_name, cust_contact FROM Customers WHERE SOUNDEX(cust_contact) = 'Michael Green' 结果 : cust_name cust_contact kids place michelle green
SQL Server
日期处理
日期格式化
TO_DATE()
TO_DATE(文本字段,[FORMAT])
MySQL
ORACLE
TO_DATETIME()
TO_DATETIME(文本字段,[FORMAT])
SQL Server
ORACLE
STR_TO_DATE()
STR_TO_DATE('日期格式字符串',‘解析格式')
SQL Server
DATE_TO_FORMAT()
DATE_TO_FORMAT('日期格式字符串',‘解析格式')
SQL Server 无
DATE_FORMAT()
DATE_FORMAT(字段,'%Y%M')
SQL Server
提取年月日
YEAR() / MONTH() / DAY()
MySQL
SQL Server
DATE()
MySQL
SQL Server
DATETIME()
MySQL
SQL Server 无
TIMESTAMP()
MySQL
SQL Server
DATEPART()
DATEPART(time_type,字段名)

SQL Server
DATE_PART()
DATE_PART('time_type',字段名)
 https://blog.csdn.net/quan278905570/article/details/115129664?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163826270816780366594738%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=163826270816780366594738&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~rank_v31_ecpm-2-115129664.first_rank_v2_pc_rank_v29&utm_term=date_part+pgsql&spm=1018.2226.3001.4187
PostgreSQL
SQL Server 无
EXTRACT()
EXTRACT(time_type FROM 字段名)
MySQL
ORACLE
SQL Server
TODATE()
TODATE(字段名,time_type)

ORACLE
STRFTIME()
STRFTIME('%Y' / '%M' / '%D',字段名)
当前日期和时间
日期
GETDATE()
SQL Server
CURDATE() CURRENT_DATE()
MySQL
SQL Server
时间
CURTIME() CURRENT_TIME()
MySQL
SQL Server
日期和时间
NOW()
MySQL
SQL Server
SYSDATE()
MySQL
SQL Server
ORACLE
SYSDATETIME()
SQL Server
相隔时间
DATEDIFF()
DATEDIFF(time_type,date1字段,date2字段)

date2-date1相隔时间
MySQL
SQL Server
未来时间
增加时间
ADD_MONTHS()
ADD_MONTHES(字段名, num)
ORACLE
DATEADD()
DATEADD(time_type, num, 字段名)

MySQL
DATE_ADD() / DATE_SUB()
DATE_ADD(字段名, INTERVAL num time_type) DATE_SUB(字段名, INTERVAL num time_type)

MySQL
SQL Server
未来的指定时间
EOMONTH()
EOMONTH(字段,[num]) 当月的最后一天，num可调整月份 num 1，为下月，-1为上月
SQL Server
具体日期或时间
DAYNAME()
MONTHNAME()
YEARNAME()
MySQL
SQL Server
DAYOFWEEK() DAYOFMONTH() DAYOFYEAR()
MySQL
SQL Server
数字处理
数值格式化
TO_NUMBER()
TO_NUMBER(文本字段)
将文本转化为NUMBER
CEIL/CEILING FLOOR()
CEIL/FLOOR(x)
返回x向上/向下取整的值
ROUND()
ROUND(x, y)
返回x四舍五入值，y代表位数
TRUNC()
TRUNC(x, y)
截取数字，y代表位数
计算函数
SIGN()
SIGH(x)
返回 负数 -1，正数 1和 0
MOD()
MOD(x,y)
返回x/y的余数
SQRT()
SQRT(x)
返回x的平方根
ABS()
ABS(x)
返回x的绝对值
PI()
PI()
返回圆周率的值，类型FLOAT浮点型
幂函数
EXP()
EXP(x)
返回e的x次幂
POWER()
POWER(x,y)
返回x^y幂结果
指数函数
LOG()
LOGe(x)
e为底的x的对数
LOG10()
LOG10(x)
10为底的x的对数
三角函数
SIN()
SIN(角度)
返回一个角度的正弦
TAN()
TAN(角度)
返回一个角度的正切
COS()
COS(角度)
返回一个角度的余弦
常用数据类型
FLOAT
浮点值
CHAR
1~255个 定长字符串
INT
4字节整数值，支持-2147483648～2147483647的数
DECIMAL/NUMBERIC
定点或精度可变的浮点值
DATE
日期值
TIME
时间值
DATETIME/TIMESTAMP
日期时间值
SELECT 窗口函数([字段]) OVER (PARTITION BY 字段 ORDER BY WINDOWING子句)
非常用数据类型
NCHAR
CHAR的特殊形式，支持多字节或Unicode字符
NVARCHAR
TEXT的特殊形式，支持多字节或Unicode字符
TEXT(LONG,MEMO,VARCHAR)
变长文本
BIT
二进制位值，0或1
REAL
4字节浮点值
SMALLINT
2字节整数值，支持-32768～32767
TINYINT
1字节整数值，支持0～255的数
SAMLLDATETIME
日期时间值，精确到分（无秒或毫秒）
BINARY
定长二进制数据（最大长度从255B到8000B）
LONG RAW
变长二进制数据，最长2GB
RAW.BINARY
变长二进制数据，最多255B
VARBINARY
变成二进制数据（最大长度一般在255B到8000B间）
其他函数
条件判定函数
IF()
IF(条件, 条件为TRUE返回值, 条件为FALSE返回值)
CASE
CASE WHEN 条件1 THEN 值1 WHEN 条件2 THEN 值2 ... ELSE 值n END
DECODE()
比较多个参数时
DECODE(字段,'字符1','翻译值1','字符2','翻译值2'...,'其他值')
SQL Server 无
比较单个参数时
DECODE(字段,'字符',字段=字符时返回值, 字段!=字符时返回值)
SQL Server 无
窗口函数 (Window Function)
窗口函数
排序函数
RANK
相同顺位用相同数字表示，省略顺位不计。例：1，2，2，4，5
DENSE_RANK
相同顺位用相同数字表示，顺位不省略照常记录。例：1，2，2，3，4
ROW_NUMBER
返回固定的行号码
集聚函数
SUM / AVG / COUNT / MAX / MIN
特殊值函数
FIRST_VALUE
分组后组内出现的第一个值
LAST_VALUE
分组后组内出现的最后一个值
LAG
将位于左列上方1位的值作为填充值
LEAD
将位于左列下方1位的值作为填充值
比率函数
CUME_DIST
分组后小于等于当前值的累计百分率
PRECENT_RANK
分组后出现的第一个值为0，最后一个值为1(100%)，按出现顺序分配百分比
NTILE
SELECT 部门, 工资 NTILE(4) OVER (ORDER BY 工资 DESC) AS N_tile FROM 职工表;
按工资排序后4个1组标示
RATIO_TO_REPORT
分组后计算各行/总和的百分率(数据类型为浮点型)
WINDOWING
ROWS
RANGE
BETWEEN~AND
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW
SELECT SUM(工资) OVER (PARTITION BY 部门 ORDER BY 工资 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) total FROM 职工表;