SQL必知必会

SQL基础知识点

1.插入记录

1.普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, …)

2.普通插入（限定字段）：INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …)

3.多条一次性插入：INSERT INTO table_name (column1, column2, …) VALUES (value1_1, value1_2, …), (value2_1, value2_2, …), …

4.从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]

5.带更新的插入：REPLACE INTO table_name VALUES (value1, value2, …) （replace into 跟 insert into功能类似，不同点在于：replace into 首先尝试插入数据到表中，如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据；否则，直接插入新数据。
要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。）

2.更新记录

1.设置为新值：UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3]

2.根据已有值替换：UPDATE table_name SET key1=replace(key1, ‘查找内容’, ‘替换成内容’) [WHERE column_name3=value3]

3.删除记录

1.根据条件删除：DELETE FROM tb_name [WHERE options] [ [ ORDER BY fields ] LIMIT n ]

2.全部删除（表清空，包含自增计数器重置）：TRUNCATE tb_name

时间差：
TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
SECOND 秒
MINUTE 分钟（返回秒数差除以60的整数部分）
HOUR 小时（返回秒数差除以3600的整数部分）
DAY 天数（返回秒数差除以3600*24的整数部分）
MONTH 月数
YEAR 年数。
（注意：timestampdiff 时间差函数，查询两个时间之间的差值，有参数设置， 可以精确到天(day) 小时(hour) 分钟(minute) 和秒(second) ；而datediff函数，返回值是相差的天数，不能定位到小时和分钟秒。）

3.MySQL删除数据常用的三种删除方式：通过 delete、truncate、drop 关键字进行删除
详见链接: link.

4.表的创建、修改与删除

1. 直接创建表：
   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] – 列排序和比较时的规则（是否区分大小写等）

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

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

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

5.删除表：DROP TABLE [IF EXISTS] (如果表存在就删除)表名1 [ ,表名2]。

5.索引的创建、删除和使用

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

2.alter方式创建索引：ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_name (column1)

3.drop方式删除索引：DROP INDEX <索引名> ON <表名>

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

5.索引的使用：

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

SQL中函数的使用

1.CAST()
CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。
语法：CAST (expression AS data_type)
expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。
可以转换的类型是有限制的。这个类型可以是以下值其中的一个：
二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED

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

3.date_format(date, format)
通过这个函数匹配’%Y%m’年份和月份
详见链接: link.