1.增
## 1插入全部字段
1.1所有的字段名都写出来
INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3);
1.2不写字段名
INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…);
## 2插入部分数据
```sql
INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...);
2.删
2.1不带条件删除数据
DELETE FROM 表名；
2.2带条件删除数据
DELETE FROM 表名 WHERE 字段名=值；
3.改
3.1不带条件修改数据
UPDATE 表名 SET 字段名=值; （--修改所有的行）
3.2带条件的修改
UPDATE 表名 SET 字段名=值 WHERE 字段名=值；
4.查
4.1.普通查询
1使用*表示所有列 SELECT * FROM 表名； 2查询指定列的数据，多个列之间以逗号分隔 SELECT 字段名1，字段名2，字段名3，... FROM 表名； 3条件查询 SELECT 字段名 FROM 表名 WHERE 条件； （条件中可以进行简短逻辑运算and，or，not和比较预算符>,<,<=,>=,=,<>(不等于)，IS null（查询某一列为NULL的值）） 4模糊查询 SELECT 字段名 FROM 表名 WHERE LIKE '通配符字符串'； （通配符% 匹配任意多个字符串 通配符-匹配一个字符）
对于别名的使用 使用别名的好处： 显示的时候使用新的名字，并不修改表的结构。 1对列指定别名 SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名; 2对列和表同时指定别名 SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;
4.2.多表连接查询
4.2.1.左外连接
左外连接会返回左表的所有记录和右表中匹配记录的组合(如果右表中无匹配记录, 来自于右表的所有列的值设为 NULL). 如果左表的一行在右表中存在多个匹配行, 那么左表的行会复制和右表匹配行一样的数量, 并进行组合生成连接结果.
select a.*,b.* from a left join b on a.id=b.parent_id
4.2.2.右外连接
右外连接, 亦简称右连接, 它与左外连接完全类似, 只不过是作连接的表的顺序相反而已.右连接操作返回右表的所有行和这些行在左表中匹配的行(没有匹配的, 来源于左表的列值设为 NULL).
select a.*,b.* from a right join b on a.id=b.parent_id
4.2.3.完全外连接
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
select a.*,b.* from a full join b on a.id=b.parent_id
4.2.4.内连接
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。
select a.*,b.* from a inner join b on a.id=b.parent_id
4.3.INSERT INTO SELECT语句与SELECT INTO FROM语句区别
1.INSERT INTO SELECT语句 语句形式为：Insert into Table2(field1,field2,…) select value1,value2,… from Table1 或者：Insert into Table2 select * from Table1 2.SELECT INTO FROM语句 语句形式为：SELECT vale1, value2 into Table2 from Table1 或者 SELECT table into Table2 from table 区别，A情况对于存在表table2，然后查询出的数据放入table2 B不存在table2 ，查询出结果后新建表放入table2 由于查询会锁表，推荐对于条件建立所以
5.聚集函数分组
5.1Mysql
GROUP BY （分组）
GROUP BY我们可以先从字面上来理解，GROUP表示分组，BY后面写字段名，就表示根据哪个字段进行分组，如果有用Excel比较多的话，GROUP BY比较类似Excel里面的透视表。 GROUP BY必须得配合聚合函数来用，分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）等。
select a.name,sum(a.number) from a where a.PBID="XXXX" GROUP BY a.number
- count() 计数
- sum() 求和
- avg() 平均数
- max() 最大值
HAVING（筛选）
HAVING相当于条件筛选，但它与WHERE筛选不同，HAVING是对于GROUP BY对象进行筛选。HAVING里面还可以写聚集函数
select a.name,sum(a.number) from a where a.PBID="XXXX" GROUP BY a.number HAVING a.number>="100"
ORDER BY
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照升序对记录进行排序。
1.asc 升序，可以省略，是数据库默认的排序方式 2.desc 降序，跟升序相反。
6.分页查询
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
7.常用函数
7.1.mysql
7.1.1.时间处理类
date_format(date, format) 函数，MySQL日期格式化函数date_format() 时间转字符串 unix_timestamp() 函数 时间转时间戳 字符串转时间戳 str_to_date(str, format) 函数 字符串转时间 from_unixtime(unix_timestamp, format) 函数，MySQL时间戳格式化函数from_unixtime **时间戳转字符串 ** 时间戳转时间
7.1.2.时间转字符串
- select date_format(now(), '%Y-%m-%d');
- #结果：2016-01-05
时间转时间戳
- select unix_timestamp(now());
- #结果：1452001082
字符串转时间
- select str_to_date('2016-01-02', '%Y-%m-%d %H');
- #结果：2016-01-02 00:00:00
字符串转时间戳
- elect unix_timestamp('2016-01-02');
- #结果：1451664000
时间戳转时间
- select from_unixtime(1451997924);
- #结果：2016-01-05 20:45:24
时间戳转字符串
- select from_unixtime(1451997924,'%Y-%d');
- //结果：2016-01-05 20:45:24
7.2.数据处理类
7.2.1.保留小数
1.round(x,d) :用于数据的四舍五入,round(x) ,其实就是round(x,0),也就是默认d为0；
SELECT ROUND(100.3465,2),ROUND(100,2),ROUND(0.6,2),ROUND(114.6,-1);
结果分别：100.35,100，0.6,110
2.TRUNCATE(x,d)：函数返回被舍去至小数点后d位的数字x。若d的值为0，则结果不带有小数点或不带有小数部分。若d设为负数，则截去（归零）x小数点左起第d位开始后面所有低位的值。
SELECT TRUNCATE(100.3465,2),TRUNCATE(100,2),TRUNCATE(0.6,2),TRUNCATE(114.6,-1);
结果分别：100.34,100，0.6,110
FORMAT（X,D）：强制保留D位小数，整数部分超过三位的时候以逗号分割，并且返回的结果是string类型的
SELECT FORMAT(100.3465,2),FORMAT(100,2),FORMAT(,100.6,2);
结果分别：100.35,100.00，100.60
convert（value，type）;类型转换，相当于截取`` type:
CAST(xxx AS 类型), CONVERT(xxx,类型)。
二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
SELECT CONVERT(100.3465,DECIMAL(10,2)), CONVERT(100,DECIMAL(10,2)),CONVERT(100.4,DECIMAL(10,2));
结果分别：100.35,100，100.4
7.2.2.数据类型转换
结果分别：100.34,100，0.6,110
7.2.3.控制或其他情况处理类
1.if函数
IF(expr1,expr2,expr3)
--如果expr1是TRUE(expr1<>0且expr1<>NULL)，那么IF()返回expr2，否则它返回expr3。IF()返回一个数字或字符串值，取决于它被使用的上下文。
IF(xxx,xxx,yyy)
--如果xxx为null，则返回yyy；不为null，则返回xxx
2.ifNull函数
IFNULL(expr1,expr2)
--如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。
IFNULL(xxx,yyy)
--如果xxx为null，返回yyy；不为null，返回xxx
7.2.4.三元运算
case when 条件 then (条件为true时执行) else(条件为false时执行) end /*end不可少*/
select *,if(expr1,expr2,expr3) from 表名
7.3.SqlServer
7.3.1.时间处理类
1.时间格式转换对照表，常用23（保留年月日） 和120（保留到秒） （此函数可以做到日期和字符串互转 ）
日期转字符串
CONVERT(varchar(100), GETDATE(), 23):
字符串转日期
SELECT CONVERT(datetime,'2020-03-04',23)
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
Sql Server中的日期与时间函数
1. 当前系统日期、时间
select getdate()
2. dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值
例如：向日期加上2天
select dateadd(month,1,'2004-10-15') --返回：2004-10-17 00:00:00.000
3. datediff 返回跨两个指定日期的日期和时间边界数。
select datediff(day,'2004-09-01','2004-09-18') --返回：17
4. datepart 返回代表指定日期的指定日期部分的整数。
select DATEPART(month, '2004-10-15') --返回 10
5. datename 返回代表指定日期的指定日期部分的字符串
select datename(weekday, '2004-10-15') --返回：星期五
6. day(), month(),year() --可以与datepart对照一下
select 当前日期=convert(varchar(10),getdate(),120)
,当前时间=convert(varchar(8),getdate(),114)
select datename(dw,'2004-10-15')
select 本年第多少周=datename(week,'2004-10-15')
,今天是周几=datename(weekday,'2004-10-15')
| 函数 | 参数/功能 |
|---|---|
| GetDate( ) | 返回系统目前的日期与时间 |
| DateDiff (interval,date1,date2) | 以interval 指定的方式，返回date2 与date1两个日期之间的差值 date2-date1 |
| DateAdd (interval,number,date) | 以interval指定的方式，加上number之后的日期 |
| DatePart (interval,date) | 返回日期date中，interval指定部分所对应的整数值 |
| DateName (interval,date) | 返回日期date中，interval指定部分所对应的字符串名称 |
| 值 | 缩 写（Sql Server） | Access 和 ASP | 说明 |
|---|---|---|---|
| Year | Yy | yyyy | 年 1753 ~ 9999 |
| Quarter | q | 季 1 ~ 4 | |
| Month | Mm | m | 月1 ~ 12 |
| Day of year | Dy | y | 一年的日数,一年中的第几日 1-366 |
| Day | Dd | d | 日，1-31 |
| Weekday | Dw | w | 一周的日数，一周中的第几日 1-7 |
| Week | Wk | ww | 周，一年中的第几周 0 ~ 51 |
| Hour | Hh | h | 时0 ~ 23 |
| Minute | Mi | n | 分钟0 ~ 59 |
| Second | Ss | s | 秒 0 ~ 59 |
| Millisecond | Ms | - | 毫秒 0 ~ 999 |
7.3.2.数据精度处理类
1.保留小数
- 使用 Round() 函数，如 Round(@num,2) 参数 2 表示 保留两位有效数字。
- 更好的方法是使用 Convert(decimal(18,2),@num) ) 实现转换，decimal(18,2) 指定要保留的有效数字。 decimal(18,4) 总共能存16位数字，末尾4位是小数（小数点不算在长度内）
这两个方法有一点不同：使用 Round() 函数，如果 @num 是常数，如 Round(2.3344,2) 则 会在把有效数字后面的 变为0 ，成 2.3300。但 Convert() 函数就不会。
3.select cast(round(56.19191,0) as int)使用四舍五入，然后保留整数部分；
2.数据类型转换
1.convert(int,字段名) 例如：select convert(int,'3')
2.cast(字段名 as int) 例如：select cast('3' as int)
二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
SELECT CONVERT(100.3465,DECIMAL(10,2)), CONVERT(100,DECIMAL(10,2)),CONVERT(100.4,DECIMAL(10,2));
结果分别：100.35,100，100.4
7.3.4.控制或其他情况处理类
1.空值替换
SELECT COALESCE('',0) 结果0
SELECT COALESCE(' ',0) 结果0
SELECT COALESCE(null,0) 结果0
SELECT COALESCE(123,0) 结果123
SELECT ISNULL('',0) 结果''
SELECT ISNULL(null,0) 结果0
SELECT ISNULL(123,0) 结果123
---由结果结果可以看出COALESCE函数对于空值处理和NULL值都起作用。
7.3.5.选择CASE的用法和CHECK 约束
1.CASE的用法
--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
--in用法
CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'
WHEN col_1 IN ('c') THEN '第二类'
ELSE'其他' END
SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class, -- 别名命名
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;
7.3.6.CHECK 约束
select
CHECK(xxx>0)
from xxx
select
CHECK(xxx=1 or xxx=2)
from xxx
7.3.7.sql server字符替换
生成UUID
select newid()
B1A1253D-0C27-4B7C-B9B3-55AC032239BD
select cast(REPLACE(NEWID(), '-', '') as varchar(32)) as uuid REPLACE把'-'替换为空字符串
962FE0EDE23043B8AD0E913B306A7696
7.3.8.结果集
ISNULL只作用于查询出数据后字段为NULL可以替换生效、如果查询的时候这条数据本身为NULL、字段替换时ISNULL则不生效。 解决方法：先用 if exists（结果集）判断这条数据是否存在（也就是说这条select这条sql有没有结果集），BEGIN 表示语句块的开始；END 表示语句块的结束（加不加 BEGIN END 代码块都可以）。如果满足条件直接在下方写 SQL。不满足条件时，则在 ELSE 下方写 SQL。如此，数据进行了替换。
IF EXISTS (
SELECT
ISNULL(historyval, 0.0)
FROM
ce_bf_l_energyh_t_2
WHERE
tagname = '123'
)
BEGIN
--如果存在
SELECT
ISNULL(historyval, 0.0)
FROM
ce_bf_l_energyh_t_2
WHERE
tagname = '123'
END
ELSE
BEGIN
--如果不存在
SELECT
0.0
END
7.3.9.sql的正则
PATINDEX ( '%pattern%' , expression )
返回pattern字符串在表达式expression里第一次出现的位置，起始值从1开始算。 pattern字符串在expression表达式里没找就返回0，对所有有效的文本和字符串就是有效的数据类型。 select patindex('%[d]%','rcrdsddddaadadffdr') 返回4，[]中d在字符串rcrdsddddaadadffdr的第一次出现的位置。 select patindex('%[cd]%','rcrdsdddrdaadadffdr') 返回2，[]中c,d在其中一个的位置，返回最先出现的这个位置，c在此字符串里第一次出现位置是2，而d是4，结果取最先的那个。 select patindex('%[sd]%','rcrdsdddrdaadadffdr') 返回4，[]中c,d在其中一个的位置，返回最先出现的这个位置，s在此字符串里第一次出现位置是5，而d是4，结果取最先的那个。 select patindex('%[^r]%','rrrdsdddrdaadadffdr') 返回4，除[]中的字符串的匹配字符，第一次出现d不在[^r]里，所以就找到第一次这位。 select patindex('%[^rd]%','rrrdsdddrdaadadffdr') 返回5，除[]中的字符串的匹配字符，第一次出现s不在[^rd]里，所以就找到第一次这位。 select patindex('%[^rsd]%','rrrdsdddrdaadadffdr') 返回11，除[]中的字符串的匹配字符，第一次出现a不在[^rsd]里，所以就找到第一次这位。
7.3.10 字符串截取 SUBSTRING（str，por,len）
从str中的por开始截取长度为len的字符串
7.3.11.STUFF函数的用法
1官方解释
STUFF 函数将字符串插入到另一个字符串中。 它从第一个字符串的开始位置删除指定长度的字符；然后将第二个字符串插入到第一个字符串的开始位置。
2 语法格式
STUFF ( character_expression , start , length ,replaceWith_expression )
3 参数详解
1、character_expression 字符数据的表达式。character_expression 可以是常量、变量，也可以是字段或二进制字段。 2、start start用来指定删除和插入开始位置的数值。 如果 start 值为负或为零，则返回空字符串。 如果 start 的长度大于第一个 character_expression，则返回空字符串。start 的类型也可以是 bigint。注意：start 值1 表示第一个字符。 3、length length用来指定要删除的字符个数。 如果 length值 为负，则返回空字符串。 如果 length 的长度大于第一个 character_expression，则最多可以删除到最后一个 character_expression 中的最后一个字符。 如果 length 为零，则插入在 start 位置发生，并且不会删除任何字符。length 的类型也可以是 bigint。 4、replaceWith_expression 字符数据的表达式。character_expression 可以是常量、变量，也可以是字段或二进制字段。 此表达式从 start 开始替换 length 个字符的 character_expression。 如果 replaceWith_expression 为 NULL，则在不插入任何内容的情况下删除字符。 5、返回类型 如果 character_expression 是支持的字符数据类型之一，则返回字符数据。如果 character_expression 是支持的二进制数据类型之一，则返回二进制数据。 6、备注 如果开始位置或长度值是负数，或者开始位置大于第一个字符串的长度，则返回 Null 字符串。 如果开始位置为 0，则返回 Null 值。 如果要删除的长度大于第一个字符串的长度，则删除到第一个字符串中的第一个字符。 如果结果值大于返回类型支持的最大值，则会引发错误。
4 STUFF实例
select STUFF('hello',1,1,'') as 删除字符列 -- 删除第一个字符
select STUFF('hello',4,2,'aa') as 替换字符列 -- 将lo 替换为 aa
7.3.12 FOR XML PATH 函数用法
一.FOR XML PATH 简单介绍
**
SELECT * FROM @hobby FOR XML PATH
<row>
<hobbyID>1</hobbyID>
<hName>爬山</hName>
</row>
<row>
<hobbyID>2</hobbyID>
<hName>游泳</hName>
</row>
<row>
<hobbyID>3</hobbyID>
<hName>美食</hName>
</row>
由此可见FOR XML PATH 可以将查询结果根据行输出成XML各式！ 那么，如何改变XML行节点的名称呢？代码如下：
SELECT * FROM @hobby FOR XML PATH('MyHobby')
<MyHobby>
<hobbyID>1</hobbyID>
<hName>爬山</hName>
</MyHobby>
<MyHobby>
<hobbyID>2</hobbyID>
<hName>游泳</hName>
</MyHobby>
<MyHobby>
<hobbyID>3</hobbyID>
<hName>美食</hName>
</MyHobby>
这个时候细心的朋友一定又会问那么列节点如何改变呢？还记的给列起别名的关键字AS吗？对了就是用它!代码如下：
SELECT hobbyID as 'MyCode',hName as 'MyName' FROM @hobby FOR XML PATH('MyHobby')
<MyHobby>
<MyCode>1</MyCode>
<MyName>爬山</MyName>
</MyHobby>
<MyHobby>
<MyCode>2</MyCode>
<MyName>游泳</MyName>
</MyHobby>
<MyHobby>
<MyCode>3</MyCode>
<MyName>美食</MyName>
</MyHobby>
噢！ 既然行的节点与列的节点我们都可以自定义，我们是否可以构建我们喜欢的输出方式呢？还是看代码： SELECT '[ '+hName+' ]' FROM @hobby FOR XML PATH('') 没错我们还可以通过符号+号，来对字符串类型字段的输出格式进行定义。结果如下： [ 爬山 ][ 游泳 ][ 美食 ] 那么其他类型的列怎么自定义？ 没关系，我们将它们转换成字符串类型就行啦！例如：
SELECT '{'+STR(hobbyID)+'}','[ '+hName+' ]' FROM @hobby FOR XML PATH('')
SELECT hobby+',' FROM student
WHERE sName=A.sName
FOR XML PATH('')
查询的hobby用逗号连接，吧表情换成空字符
7.3.13.LEFT(ARG,LENGTH)、RIGHT(ARG,LENGTH)
LEFT、RIGHT函数返回ARG最左边、右边的LENGTH个字符串，ARG可以是CHAR或BINARY STRING。
