SQL第一天：插入；更新；删除；表与索引操作；聚合分组查询

一：插入

1.replace into 跟 insert into功能类似，不同点在于：replace into 首先尝试插入数据到表中，

1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据；
2. 否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

• 链接：插入记录（三）_牛客题霸_牛客网

2.INSERT INTO tab_name (col_name) VALUES (要插入的数据,这里是第一行数据), (要插入的数据,这里是第二行数据)...(要插入的数据，第n行数据);

链接：插入记录（一）_牛客题霸_牛客网

3.插入记录的方式汇总：

• 普通插入（全字段）：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 key=value]
• 链接：插入记录（二）_牛客题霸_牛客网

二：更新

1.明确考点：

修改记录的方式汇总：

• 设置为新值：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]
• 链接：更新记录（一）_牛客题霸_牛客网

三：删除

1.明确考点：

删除记录的方式汇总：

• 根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
• 全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

时间差：

• TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
  • SECOND 秒
  • MINUTE 分钟（返回秒数差除以60的整数部分）
  • HOUR 小时（返回秒数差除以3600的整数部分）
  • DAY 天数（返回秒数差除以3600*24的整数部分）
  • MONTH 月数
  • YEAR 年数

链接：1.删除记录（一）_牛客题霸_牛客网

2.删除记录（二）_牛客题霸_牛客网

2.明确考点：

删除记录的方式汇总：

• 根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]
• 全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

---

细节剖析：

• 删除exam_record表中所有记录；
• 并重置自增主键；

---

思路实现：

• 本题采用第二种删除方式，满足条件1或条件2就删除，但只删除3条记录:

1	TRUNCATE exam_record;

• 也可采用第一种，不过需要手动重置自增ID，不过效率角度考虑，还是第二种方式效率更高：

复制代码

1 2	DELETE FROM exam_record; ALTER TABLE exam_record auto_increment=1;

链接：删除记录（三）_牛客题霸_牛客网

四：表与索引操作

1.明确考点：

表的创建、修改与删除：

• 1.1 直接创建表：

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE [IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过 (column_name1 data_type1 -- 列名和类型必选   [ PRIMARY KEY -- 可选的约束，主键    | FOREIGN KEY -- 外键，引用其他表的键值    | AUTO_INCREMENT -- 自增ID    | COMMENT comment -- 列注释（评论）    | DEFAULT default_value -- 默认值    | UNIQUE -- 唯一性约束，不允许两条记录该列值相同    | NOT NULL -- 该列非空   ], ... ) [CHARACTER SET charset] -- 字符集编码 [COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

• 1.2 从另一张表复制表结构创建表： CREATE TABLE tb_name LIKE tb_name_old

• 1.3 从另一张表的查询结果创建表： CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

• 2.1 修改表：ALTER TABLE 表名 修改选项 。选项集合：

1 2 3 4 5 6 7 8

{ ADD COLUMN <列名> <类型>  -- 增加列  | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型  | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值  | MODIFY COLUMN <列名> <类型> -- 修改列类型  | DROP COLUMN <列名> -- 删除列  | RENAME TO <新表名> -- 修改表名  | CHARACTER SET <字符集名> -- 修改字符集  | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

• 3.1 删除表：DROP TABLE [IF EXISTS] 表名1 [ ,表名2]。

---

细节剖析：

• 自增ID：AUTO_INCREMENT；
• 设置主键：PRIMARY KEY；
• 唯一性约束：UNIQUE
• 非空约束：NOT NULL
• 设置默认值：DEFAULT 0
• 当前时间戳：CURRENT_TIMESTAMP
• 评论/注释：COMMENT
• 如果该表已创建过，正常返回：IF NOT EXISTS
• 链接：创建一张新表_牛客题霸_牛客网

2.

alter table user_info add school varchar(15) after level;
增加列在某列之后
alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)

alter table user_info change job profession varchar(10);
更换列的名称及数据类型
alter table user_info change 原列名 修改列名 修改数据类型

alter table user_info modify achievement int(11) default 0;
更改数据类型
alter table 表名 modify 修改列名称 数据类型 默认值等
链接：修改表_牛客题霸_牛客网

3. 删除表：DROP TABLE [IF EXISTS] 表名1 [, 表名2]

链接：删除表_牛客题霸_牛客网

4.

明确考点：

索引创建、删除与使用：

• 1.1 create方式创建索引：

1 2 3 4 5

CREATE   [UNIQUE -- 唯一索引   | FULLTEXT -- 全文索引   ] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引   (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引

• 1.2 alter方式创建索引：ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)

• 2.1 drop方式删除索引：DROP INDEX <索引名> ON <表名>

• 2.2 alter方式删除索引：ALTER TABLE <表名> DROP INDEX <索引名>

• 3.1 索引的使用：

  • 索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；
  • 索引不包含有NULL值的列
  • 一个查询只使用一次索引，where中如果使用了索引，order by就不会使用
  • like做字段比较时只有前缀确定时才会使用索引
  • 在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引
  • 链接：创建索引_牛客题霸_牛客网

5.删除索引

明确考点：

索引创建、删除与使用：

• 1.1 create方式创建索引：

1 2 3 4 5

CREATE   [UNIQUE -- 唯一索引   | FULLTEXT -- 全文索引   ] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引   (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引

• 1.2 alter方式创建索引：ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)

• 2.1 drop方式删除索引：DROP INDEX <索引名> ON <表名>

• 2.2 alter方式删除索引：ALTER TABLE <表名> DROP INDEX <索引名>

• 3.1 索引的使用：

  • 索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；
  • 索引不包含有NULL值的列
  • 一个查询只使用一次索引，where中如果使用了索引，order by就不会使用
  • like做字段比较时只有前缀确定时才会使用索引
  • 在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引
  • 链接：删除索引_牛客题霸_牛客网

五：聚合分组查询

1.平均活跃天数和月活人数

根据月份来选择时间。知识点：date_format() 通过这个函数匹配'%Y%m'年份和月份

保留两位小数。 知识点：round(x,2)

链接：平均活跃天数和月活人数_牛客题霸_牛客网

2.未完成试卷数大于1的有效用户

• 关联作答记录和试卷信息：left join examination_info on using(exam_id)；（题中exam_record中的exam_id在examination_info均存在，所以用left join和inner join效果一样）
• 筛选2021年的记录：where year(start_time)=2021
• 获取各用户的tag,start_time及未完成标记和已完成标记，如果该作答记录交卷了则已完成标记为1，未完成标记为0，否则相反：if(submit_time is null, 1, null) as incomplete
• 按用户分组：group by uid
• 统计未完成试卷作答数和已完成试卷作答数：count(incomplete) as incomplete_cnt
• 统计作答过的tag集合：
  • 对于每条作答tag，用:连接日期和tag：concat_ws(':', date(start_time), tag)
  • 对于一个人（组内）的多条作答，用;连接去重后的作答记录：group_concat(distinct concat_ws(':', date(start_time), tag) SEPARATOR ';')
• 筛选未完成试卷作答数大于1的有效用户：having complete_cnt >= 1 and incomplete_cnt BETWEEN 2 and 4
  • 完成试卷作答数至少为1：complete_cnt >= 1
  • 未完成数小于5：incomplete_cnt < 5
  • 未完成试卷作答数大于1：incomplete_cnt > 1
  • 按未完成试卷数量由多到少排序：order by incomplete_cnt DESC

链接：未完成试卷数大于1的有效用户_牛客题霸_牛客网