SQL记录

插入记录

通常只插入一行记录，插入多行可用insert select into（可包含where子句用来过滤）

• insert给表插入有指定列值的行
• insert将select结果插入表中

replace select into先尝试插入数据到表中，根据主键或唯一索引进行判断，若已有此行数据，则删掉再重新填入。
同上已存在情况也可先delete

删除

删除表内所有数据行 （实现对表结构中自增列的重置

• truncate table
  truncate 删除表中的所有行，但表的结构及其列，约束，索引等保持不变
• DROP TABLE　
  清除数据并且销毁表

表的创建、修改、删除

DEFAULT CURRENT_TIMESTAMP
表示当插入数据的时候，该字段默认值为当前时间

ON UPDATE CURRENT_TIMESTAMP
表示每次更新这条数据的时候，该字段都会更新成当前时间

1.添加列到第一列or某一列之后
alter table 表名 add *(column) * 列名 类型 【first|after 字段名】;
2.修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;
3.修改列名
alter table 表名 change column 旧列名 新列名 类型;
4 .删除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename 【to】 新表名;
6.将某一列放到第一列
alter table 表名 modify column 列名 类型 first;
7.DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
DELETE t1表示要删除t1的一些记录，具体删哪些，就看WHERE条件，满足就删；
这里删的是t1表中，跟t2匹配不上的那些记录。

行列转换

列转行 用unionall
1795. 每个产品在不同商店的价格

把店名列转换为价格列，本身列头没有了，使用字符来填充列头，代表本身列具备的含义。

单个示例：select ‘store1’ as store, store1 as price from producets

select product_id,'store1' store,store1 price from Products where store1 is not null
union all
select product_id,'store2' store,store2 price from Products where store2 is not null
union all
select product_id,'store3' store,store3 price from Products where store3 is not null

行转列
pivot unpivot

创建索引

删除同理
create index 普通
create unqiue index 唯一
create fulltext index 全文
ALTER TABLE 【表明】 ADD INDEX

聚集 统计次数

• count x
  count (*)
  count（字段）不计null ,count（1)，count（ *）计null
• 条件限制1：sum(case when x is not null then 1 else null end) as
• 条件限制2：select xx, count(if ))
• 去重 加distinct,同时去重 distinct两个条件

count（distinct if(筛选条件,v1,null)
count (case when 筛选条件 then v1 end)

分组 过滤数据

group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’])

HAVING WHERE区别

• WHERE过滤行，HAVING过滤分组

• WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

• 使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。

函数

• left right
  LEFT：返回字符串中从左边开始指定个数字符
  RIGTH：返回字符串从右边开始指定个数字符

SELECT LEFT(‘abcedf’,3) as leftResult; – 返回从左侧数前 3 个字符，第二个参数不接收负数，会报错

SELECT RIGHT(‘abcedf’,3) as rightResult; – 返回从右侧数前 3 个字符

• group_contact 1484. 按日期分组销售产品

select sell_date,count(distinct product) as num_sold,GROUP_CONCAT(distinct product order by product SEPARATOR ',') products  
from activities
group by sell_date
order by sell_date

窗口函数

ROW_NUMBER 函数

• 为行分配序号
• 找到每组的前N行 SQL27 每类试卷得分前X名
• 分页：为结果集中的每一行指定一个唯一的数字，所以可以将其用于分页。between……and

NTH_VALUE()函数
从结果集中的第N行获取值
SQL28 第二快/第二慢

RANK、DENSE_RANK函数
RANK1224 ，是跳跃的
DENSE_RANK
与RANK区别于1223，是连续可重复的
SQL30 近三个月用户某某情况
近三月：连续、可重复（一个月份内有多次答题记录）
把符合答题状态的月份记录并逆序（近）然后取前三

函数的使用

常用文本处理函数

常用数值处理函数

对非数值数据使用MIN()，在用于文本数据时，MIN()返回该列排序后最前面的行。

DISTINCT不能用于COUNT(＊)如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(＊)。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

• 两个时间差计算方式：

• UNIX_TIMESTAMP(submit_time)-UNIX_TIMESTAMP(start_time) 返回秒

• timestampdiff(minute, start_time, submit_time) < x 返回分钟

• PRIMARY KEY AUTO_INCREMENT 主键和自增结合使用

• ROUND() 函数 ROUND 函数用于把数值字段舍入为指定的小数位数。

• DATE_FORMAT(date,format)
  %a 缩写星期名
  %b 缩写月名
  %c 月，数值
  %Y 年，4 位
  %y 年，2 位
  %M 月名
  %m 月，数值(00-12)

• 获取对应月份的天数，通过last_day()函数获取对应月的最后一天，再利用day()函数取出天数

TIPS

• 通过union all 可以添加汇总行
• with rollup 语法，用法是跟在group by 字段之后 ，可以在分组字段的基础上做一个汇总统计
• ORDER BY子句只能在最后一次使用。 如果想要在UNION之前分别单独排序，那么需要用select*外嵌，这样才能使排序生效

• datediff函数，返回值是相差的天数，不能定位到小时、分钟和秒。
  – 相差2天 select datediff(‘2018-03-22 09:00:00’, ‘2018-03-20 07:00:00’);
• TIMESTAMPDIFF函数，有参数设置，可以精确到天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND），使用起来比datediff函数更加灵活。对于比较的两个时间，时间小的放在前面，时间大的放在后面。
  select TIMESTAMPDIFF(SECOND/MINUTE/HOUR/DAY, ‘2018-03-20 09:00:00’, ‘2018-03-22 10:00:00’);
  
  

CASE表达式

• 在标准sql中gruop by子句中引用select子句里定义的别称是不被允许的
• 如果case表达式里没有明确指定else子句，执行结果会被默认地处理成else null
• case表达式在执行时会被判定为一个固定值，因此可写在聚合函数内部。（能写在列名和常量的地方，通常都可以写CASE表达式<区别表达式和语句>）
• 用HAVING对聚合函数做条件分支，也可用SELECT子句
  注：向声明式、面向集合思维转变

  MAX(CASE WHEN main_club_flg = 'Y'
                          THEN club_id
                          ELSE NULL END)

对于学生200 即max(null,3,null

• 转换行列结构