sql

0 执行顺序
FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY --> LIMIT

1 创建数据库
1.1 方式一：如果数据库已存在会报异常
CREATE DATABASE 【数据库名】
[DEFAULT] CHARACTER SET utf8mb4
[DEFAULT] COLLATE utf8mb4_general_ci;
1.2 方式二：如果数据库已存在不会报异常
CREATE DATABASE IF NOT EXISTS 【数据库名】
[DEFAULT] CHARACTER SET utf8mb4
[DEFAULT] COLLATE utf8mb4_general_ci;

2 删除数据库
2.1 方式一：如果数据库不存在会报异常
DROP DATABASE 【数据库名】;
2.2 方式二：如果数据库不存在不会报异常
DROP DATABASE IF EXISTS 【数据库名】;

3 创建数据表
3.1 方式一：如果数据表已存在会报异常
CREATE TABLE 【数据表名】(
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
column1 VARCHAR(20) COMMENT 'xxx',
column2 VARCHAR(20) COMMENT 'xxx',
...
is_delete VARCHAR(1) COMMENT '删除标志',
update_time DATETIME COMMENT '修改时间',
create_time DATETIME COMMENT '创建时间',
index idx_xxx (xxx), -- 单列索引
index idx_xxx (xxx, xxx) -- 多列索引
) COMMENT='xxx';
3.2 方式二：如果数据表已存在不会报异常
CREATE TABLE IF NOT EXISTS【数据表名】(
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
column1 VARCHAR(20) COMMENT '备注xxx',
column2 VARCHAR(20) COMMENT '备注xxx',
...
is_delete VARCHAR(1) COMMENT '删除标志',
update_time DATETIME COMMENT '修改时间',
create_time DATETIME COMMENT '创建时间',
index idx_xxx (xxx), -- 单列索引
index idx_xxx (xxx, xxx) -- 多列索引
) COMMENT='备注xxx';

4 修改数据表

-- 修改表名
ALTER TABLE 【要修改的数据表名】 RENAME 【要修改为的数据表名】;
-- 修改表备注
ALTER TABLE 【数据表名】 COMMENT '【备注xxx】';

-- 修改列名和列类型和默认值和备注（*表示必填）
ALTER TABLE 【数据表名】
CHANGE 【要修改的列名1 *】 【要修改为的列名1 *】 【要修改为的列类型1】 DEFAULT '【要修改为的默认值1】' COMMENT '【要修改为的备注1】',
CHANGE 【要修改的列名2 *】 【要修改为的列名2 *】 【要修改为的列类型2】 DEFAULT '【要修改为的默认值2】' COMMENT '【要修改为的备注2】';
-- 修改列类型和默认值和备注（*表示必填）
ALTER TABLE 【数据表名】 MODIFY COLUMN 【列名】
【要改为的类型 *】 DEFAULT '【要修改为的默认值】' COMMENT '【要修改为的备注】';
-- 修改列的默认值
ALTER TABLE 【数据表名】 ALTER 【列名】 SET DEFAULT 【默认值】;

-- 增加列（*表示必填）
ALTER TABLE 【数据表名】
ADD 【列名1 *】 【列名1的类型 *】 DEFAULT '【列名1的默认值】' COMMENT '【列名1的备注】',
ADD 【列名2 *】 【列名2的类型 *】 DEFAULT '【列名2的默认值】' COMMENT '【列名2的备注】';
-- 增加列（*表示必填）
ALTER TABLE 【数据表名】
ADD (
【列名1 *】 【列名1的类型 *】 DEFAULT '【列名1的默认值】' COMMENT '【列名1的备注】,
【列名2 *】 【列名2的类型 *】 DEFAULT '【列名2的默认值】' COMMENT '【列名2的备注】
);

-- 删除列
ALTER TABLE 【数据表名】 DROP 【列名1】, DROP 【列名2】;

-- 添加索引
ALTER TABLE 【数据表名】
ADD INDEX 【索引名】 (【列名1】),
ADD INDEX 【索引名】 (【列名1,列名2】);

-- 删除索引
ALTER TABLE 【数据表名】 DROP INDEX 【索引名1】, DROP INDEX 【索引名2】;

5 删除数据表
5.1 方式一：
DROP TABLE 【数据表名】;
5.2 方式二：
DROP TABLE IF EXISTS 【数据表名】;

6 查询数据
SELECT 列名1, 列名2 FROM 【表名】 WHERE 列名3 = xxx;

7 添加数据
INSERT INTO 【表名】 (列名1, 列名2) VALUES (xxx, xxx), (xxx, xxx); -- 可同时插入多行数据

8 删除数据
DELETE FROM 【表名】 WHERE 列名1 = xxx;

9 修改数据
UPDATE 【表名】 set 【列名1】 = xxx, 【列名2】 = xxx WHERE 列名3 = xxx;

10 索引与约束
10.1 索引：
INDEX
10.2 约束：
UNIQUE -- 唯一
NOT NULL -- 不为null
AUTO_INCREMENT -- 自增
DEFAULT xxx
10.3 约束+索引：
PRIMARY KEY --主键(非空，自增，唯一，索引)
UNIQUE INDEX -- 唯一，索引
UNIQUE KEY -- 唯一，索引

11 运算符

=
!=  <>
<
<=
>
>=
in(...)
not in(...)
is null
is not null
between ... and ...
not between ... and ...
like  -- %（0个或多个）  _（一个）  [...]（正则）  [^...]（正则）
not like  -- %  _  [...]  [^...]

12 其他关键字
GROUP BY 【列名】 -- 分组
ORDER BY 【列名】 [ASC] -- 排序（DESC ... 9、8、7 ...）
LIMIT 【行数】 OFFSET【偏移数】 -- 截取行数
DISTINCT 【列名1】, 【列名2】 -- 去重（当列名1和列名2同时相同时保留一条记录）
INNER JOIN 【表名】 ON 【连表条件】 -- 内连接
LEFT JOIN 【表名】 ON 【连表条件】 -- 左外连接（左全保留：内连接+左表未连接成功数据且不足数据补null）
RIGHT JOIN 【表名】 ON 【连表条件】 -- 右外连接（右全保留：内连接+右表未连接成功数据且不足数据补null）
FULL JOIN 【表名】 ON 【连表条件】 -- 全连接 （左右全保留：内连接+左右表未连接成功数据且不足数据补null）

13 数据类型
13.1 整数
TINYINT -- 1个字节（-128~127）
SMALLINT -- 2个字节（-32768~32767）
MEDIUMINT -- 3个字节（-8388608~8388607）
INT -- 4个字节（-2147483648~2147483647）
BIGINT -- 8个字节（±9.22×10^18）
BOOL -- 1个字节（-128~127 true（1） false（0））
13.2 小数
13.2.1 浮点数（有精度问题）
FLOAT -- 4个字节
FLOAT(【数字1】,【数字2】) -- 4个字节（整数部分位数为【数字1】-【数字2】超出就报错，小数部分为【数字2】超出就截取或四舍五入）
DOUBLE -- 8个字节
DOUBLE(【数字1】,【数字2】) -- 8个字节（整数部分位数为【数字1】-【数字2】超出就报错，小数部分为【数字2】超出就截取或四舍五入）
13.2.2 定点数（可以精确存储）
DECIMAL(【数字1】,【数字2】) -- ((【数字1】/9)去掉小数部分+1)×4 个字节（整数部分位数为【数字1】-【数字2】超出就报错，小数部分为【数字2】超出就截取或四舍五入）
13.3 字符串

• utf8编码下汉字占3个字节

CHAR -- CHAR(255)
CHAR(【1到255之间的数字】) -- 占用字节数与字符编码有关：utf8编码下占用【1到255之间的数字】×1个字节 ~ 【1到255之间的数字】×3个字节 之间（可以存储【1到255之间的数字】个字符）
VARCHAR(【数字】) -- 占用字节数与字符编码和实际存储字符数有关：utf8编码下占用 实际存储字符数×1个字节 ~ 实际存储字符数×3个字节 之间（可以存储【数字】个字符）
13.4 长文本
TEXT -- 占用字节数与字符编码和实际存储字符数有关：utf8编码下占用 实际存储字符数×1个字节 ~ 实际存储字符数×3个字节 之间（理论上不限长度，实际上不同数据库限制不同）
13.5 日期时间
DATETIME(【数字】) -- 8个字节（【数字】表示精确到秒后几位：DATETIME(3)表示精确到毫秒 DATETIME等于DATETIME(0) 可以存储2038年内的日期时间）
DATESTAMP(【数字】) -- 4个字节（【数字】表示精确到秒后几位：DATESTAMP(3)表示精确到毫秒 DATESTAMP等于DATESTAMP(0) 可以存储9999年内的日期时间）
DATE -- 3个字节（可以存储日期）
TIME -- 3个字节（可以存储时间）
13.6 二进制（用来存储视频、图片等）
https://cloud.tencent.com/developer/article/1955508


14 常用函数
14.1 聚合函数
sum() -- 计算总值
count() -- 计算总行
max() --计算最大值
min() --计算最小值
avg() --计算平均值
14.2 日期时间函数

CURRENT_TIMESTAMP(【数字】)  -- 等于NOW(【数字】)  等于SYSDATE(【数字】)  【数字】表示精确到秒后几位  例子：CURRENT_TIMESTAMP(3) 获取当前日期时间精确到毫秒
curdate() -- 获取当前日期
curtime() -- 获取当前时间
year(【日期】) -- 获取入参中的年
month(【日期】) -- 获取入参中的月
day(【日期】) -- 获取入参中的日
hour(【时间】) -- 获取入参中的小时
minute(【时间】) -- 获取入参中的分钟
second(【时间】) -- 获取入参中的秒

14.3 字符串函数

concat('【字符串1】','【字符串2】', ......) -- 字符串拼接
insert('这是一个某某数据库', 5, 2, 'MYSQL') -- 字符串替换 结果为：这是一个MYSQL数据库
upper('MySql') -- 字符串转大写 结果为：MYSQL
lower('MySql') -- 字符串转小写 结果为：mysql
substring('这是一个数据库', 3, 2) -- 获取子串 结果为：一个

14.4 窗口函数（mysql8.0 pgsql 放在select后不允许放在where前）

row_number() OVER(PARTITION BY 【分组字段】 ORDER BY 【依据排号字段】 ASC) -- 组内排号（组内相同名次号数不一致，组内排号无间隔号数 1 2 3 4 ......）
rank() OVER(PARTITION BY 【分组字段】 ORDER BY 【依据排号字段】 ASC) -- 组内排号（组内相同名次号数一致，组内排号有间隔号数 1 1 3 4 ......）
dense_rank() OVER(PARTITION BY 【分组字段】 ORDER BY 【依据排号字段】 ASC) -- 组内排号（组内相同名次号数一致，组内排号无间隔号数 1 1 2 3 ......）

first_value(【字段】) OVER(PARTITION BY 【分组字段】 ORDER BY 【依据排号字段】 ASC) -- 获取组内第一行指定字段的值
...
nth_value(【字段】,【行号】) OVER(PARTITION BY 【分组字段】 ORDER BY 【依据排号字段】 ASC) -- 获取组内指定行指定字段的值

sum(【字段】) OVER(PARTITION BY 【分组字段】 ORDER BY 【依据排序字段】 ASC)  -- 获取组内当前名次和小于当前名次的所有指定字段的值的和
...