满嘴都是糖果
字符串函数
- substring(str, pos, length) （被截取字段，从第几位开始截取，截取长度）省略length则默认截取至末尾，从1开始数位置，pos为负数就是从后向前数
select device_id, substring(blog_url,11) as user_name from user_submit
http:/url/dkittycc -> blog_url
dkittycc -> user_name
- substring_index(str,sep,n)（被分隔的字符串，分隔符，前几个字符串），n为负数则获取从后向前数几个字符串
select substring_index(substring_index(profile,',',3),',',-1)as age from user_submit group by age
180cm,75kg,27,male -> profile
substring_index(profile,',',3) -> 180cm,75kg,27
substring_index(substring_index(profile,',',3),',',-1) -> 27
- concat(str1,str2,…):字符串拼接
日期函数
-
DATE_SUB:
DATE_SUB('2023-12-29',INTERVAL 2 DAY)#2023-12-27 -
DATE_ADD:
DATE_ADD('2023-12-29',INTERVAL 2 DAY)#2023-12-31 -
DATEDIFF:
DATEDIFF(date1, date2)#date1-date2例如，date1=2023-4-26，date2=2023-4-25，则DATEDIFF(date1,date2)=1，DATEDIFF(date2,date1)=-1
-
TIMESTAMPDIFF:
TIMESTAMPDIFF(DAY,'2023-01-01', '2022-12-21') -
DAY:
DAY(date)获取当前日期的天 -
MONTH：
MONTH(date)取当前日期的月 -
YEAR：
YEAR(date)取当前日期的年 -
last_day:
last_day(date)返回参数该月日期的最后一天
膜函数
Mod(a,b) 在sql中的意思是 a / b 的余数(即 a % b )
窗口函数
用法如下，其中partition by可以省略，表示不分组
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
窗口函数可以放以下两种函数：
-
专用窗口函数，比如rank, dense_rank, row_number等
-
聚合函数，如sum, avg, count, max, min等
对于专用窗口函数
select *,
rank() over (order by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc) as dese_rank,
row_number() over (order by 成绩 desc) as row_num
from 班级表
对于聚合函数，计算结果是针对自身记录、以及自身记录之上的所有数据进行计算
select *,
sum(成绩) over (order by 学号) as current_sum,
avg(成绩) over (order by 学号) as current_avg,
count(成绩) over (order by 学号) as current_count,
max(成绩) over (order by 学号) as current_max,
min(成绩) over (order by 学号) as current_min
from 班级表
插入记录
插入记录的方式汇总：
- 普通插入（全字段）：
INSERT INTO table_name VALUES (value1, value2, ...) - 普通插入（限定字段）：
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) - 多条一次性插入：
INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ... - 从另一个表导入：
INSERT INTO table_name SELECT * FROM table_name2 where... - 带更新的插入：
REPLACE INTO table_name VALUES (value1, value2, ...)（注意这种原理是检测到主键或唯一性索引键重复就删除原记录后重新插入）
修改记录
- 设置为新值：
UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3] - 根据已有值替换：
UPDATE table_name SET key1=replace(key1, '查找内容', '替换成内容') [WHERE column_name3=value3]
删除记录
- 根据条件删除：
DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ] - 全部删除（表清空，包含自增计数器重置）：
TRUNCATE table tb_name
修改表
ALTER TABLE 表名
ADD COLUMN <列名> <类型> -- 增加列
| CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
| MODIFY COLUMN <列名> <类型> -- 修改列类型
| DROP COLUMN <列名> -- 删除列
| RENAME TO <新表名> -- 修改表名
| CHARACTER SET <字符集名> -- 修改字符集
| COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）
alter table user_info add school varchar(15) after level;
alter table user_info change job profession varchar(10);
alter table user_info modify achievement int(11) default 0;
删除表
DROP TABLE [IF EXISTS] 表名1 [, 表名2]
创建索引
CREATE
[UNIQUE -- 唯一索引
| FULLTEXT -- 全文索引
] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引
(column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引
create index idx_duration on examination_info(duration);
create unique index uniq_idx_exam_id on examination_info(exam_id);
create fulltext index full_idx_tag on examination_info(tag);