sql学习笔记（二）

1、插入记录的方式汇总：

• 普通插入（全字段）：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]
• 带更新的插入：REPLACE INTO table_name VALUES (value1, value2, ...) （注意这种原理是检测到主键或唯一性索引键重复就删除原记录后重新插入）

---

2、修改记录的方式汇总：

• 设置为新值：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]

---

3、删除记录的方式汇总：

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

4、时间差：

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

5、小知识点：

某一字段记录为空写成 is null， 而不是 = null

count(A)会忽略A的值为null的行

6、表的创建、修改与删除：

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]。

7、索引创建、删除与使用：

• 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上的索引

8、DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

语法：DATE_FORMAT(date,format)

%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

注意大小写，有不同的代表含义

9、hive sql处理空值 -->coalesce函数

coalesce(value1, value2)：返回第一个不为空的值，若value1位空，则返回value2。