数据库中的一些基本操作_sql group by with rollup 对未分组列设空值-CSDN博客

添加：

向数据库中添加值
语法：INSERT [INTO] table_name [(COLUMN1,COLUMN2,.....)] VALUES (VALUE1,VALUE2,.....)
注意：
1、VALUES需要与COLUMN对应
2、添加数据时，如果字段类型为varchar或datetime，否则必须使用单引号('')引起来。
3、虽然没有显示地添加IDENTIRY约束字段，但是数据库会自动按照IDENTITY规则添加数据

一次添加多行数据
语法:
INSERT [INTO] tabale_name([COLUMN1,COLUMN2,.....])
SELECT VALUE UNION
SELECT VALUE

删除：

删除表中的数据
语法：DELECT FROM table_name[WHERE <表达式>]
注意：
1、使用DELETE语句删除数据时，是以整条记录为单位的，不能只删除某个字段
删除整张表数据
语法：TRUNCATE TABLE table_name

           两种删除语句的优缺点
   语句                     优点                                        缺点
   DELETE                   选择性地删除，数据可恢复                    当删除整张表的数据时效率低
   TRUNCATE                 只能删除整张表的数据，但是效率高于          不能选择性地删除，数据可恢复
                            DELETE语句

修改表中的数据
语法：UPDATE table_name SET COLUMN1={},COLUMN2={},.... WHERE<表达式>

注意：分离数据库是将数据库文件从SQL Server中分离出来，而删除数据库是将数据库文件删除。

查询：
查询语句语法结构
语法：SELECT <COLUMN1,COLUMN2,.....> FROM <table_name> [表达式] [ORDER BY <COLUMN1,COLUMN2,.....> [ASC或DESC]]
ORDER BY 子句用于对查询结果进行排序。ASC表示升序，DESC表示降序排列，默认的排序是ASC.
注意：将查询得到的结果称为结果集(RecordSet).SQL Server对大小写不敏感，包括关键字在内。例如，SELECT、select和Select是等效的。

使用别名
1、语法：SELECT 字段名1 别名，字段名2 别名 FROM 表名
2、语法：SELECT 别名=字段名1，别名=字段名2 FROM
3、语法：SELECT 字段名1 AS 别名，字段名2 AS 别名 FROM 表名
注意：使用“=”更改查询结果列名时比较特殊，在SELECT语句中语法必须是“别名=字段名”；

单列排序：SELECT * FROM table_name ORDER BY
多列排序；SELECT * FROM table_name ORDER BY COLUMN1 DESC,COLUMN2 DESC....

查询限定行TOP N
语法：SELECT TOP N <COLUMN1,COLUMN2,.....> FROM <table_name> [WHERE <表达式>] [ORDER BY <COLUMN1,COLUMN2,.....> [ASC或DESC]]
查询百分比
语法：SELECT TOP N PERCENT <COLUMN1,COLUMN2,.....> FROM <table_name> [WHERE <表达式>] [ORDER BY <COLUMN1,COLUMN2,.....> [ASC或DESC]]

条件查询注意：
1、在一个WHERE子句中，可以同时使用多个AND运算符链接多个查询条件。
2、在“(表达式1)OR(表达式2)”中，如果表达式1为TRUE,将不再执行表达式2。

SQL Server内置函数(内置函数可作为where条件，聚合函数不能做为where条件)

                                      字符串函数
  函数名                       示例                                          函数功能
  CharIndex                  CharIndex('ab','cdab')结果返回3                 返回‘ab’在‘cdab’中的位置，将返回第一个字母的位置
  Len                        Len('adc')结果返回3                             返回字符串的长度
   Upper                      Upper('abc')结果ABC                             将字符串中的小写字母转换为大写
  Ltrim                      Ltrim(' adc')结果返回adc                       清楚左边空格
  Rtrim                      Rtrim('abc ')结果返回abc                       清除右边空格
  Replace                    Replace('abb','b','z')结果返回azz               替换指定的字符
  Stuff                      Stuff('abcd',1,2,'mm')结果返回mmcd              替换指定范围的字符
  substring                  substring('abc','2','2')结果返回bc              从指定位置截取

实例：测试字符串长度SELECT Len(字段名) FROM table_name
将内置函数作为条件使用：SELECT * FROM table_name WHERE Len(字段名)>VALUE

                                    日期函数
        函数名                  示例                                                  函数功能
        GetDate                 GetDate结果返回“当前日期”                             返回当前日期
        DateAdd                 DateAdd(mm,2,'2009-10-08')结果返回‘2009-12-08’        向日期指定部分添加数字，其中，yy表示年、mm表示月、dd表示日
  DateDiff                DateDiff(dd,'2009-09-09','2010-09-09')结果返回‘365’   返回两个日期之间的间隔，其中，yy表示年、mm表示月、dd表示日
  DateName                DateName(DW,'2009-09-09')结果返回“星期三”             以字符串的形式返回某个日期指定的部分
  DatePart                DatePart(DW,'2009-09-09')结果返回“4”                  以整数形式返回某个日期指定的部分

实例：获取系统当前日期函数为GetDate()，表达式可以写为“COLUMN1<GetDate()”,SQL语句如下：
SELECT * FROM table_name WHERE COLUMN1<GetDate()
注意：在创建数据库时，如果对于时间的选择。例如声明类型YYYY-MM-DD则需要使用Date类型，如果精确到时、分、秒则需要使用Datetime类型。
如果需要使用默认时间则只需在类型后添加 default getdate()。在插入数据时，对应的时间字段只需使用default即可。

判断选课时间是否是星期三的函数为DateName(DW,COLUMN)，表达式可以写成“DateName(DW,COLUMN)=‘星期三’”，SQL语句如下：
SELECT * FROM table_name DateName(DW,COLUMN)=‘星期三’

                                    数学函数
  函数名                  示例                                                   函数功能
   Abs                     Abs(-1)结果返回“1”                                     求绝对值
  Ceiling                 Ceiling(24.1)结果返回“25”                              大于24.1的最小整数
  Floor                   Floor(24.1)结果返回“24”                                小于24.1的最大整数
  Power                   Power(2,3)结果返回“8”                                  计算2的3次方
  Round                   Round(748.32,1)结果返回“748.30”                        返回一个数值，舍入到指定的长度或精度
  Sign                    Sign(100)结果返回“1”                                   正整数返回1，负整数返回-1
  Sqrt                    Sqrt(4)结果返回“2”                                     开平方

                                  系统函数
  函数名                      示例                                                 函数功能
        Convert                 Convert(varchar(10),123)结果返回“123”                   装换数据类型
  DataLength              DataLength('12中国')结果返回6                           返回任何数据类型的字节数。“中国”包含4个字节，“12”包含两个字节，所以返回6.

开发过程中对于日期的操作：例如获得昨天、前天的数据。
SQL语句如下： select * from table_name where pudoctime>=SUBSTRING( Convert(varchar(20),DateAdd(DD,-1,GETDATE()),120),1,10)

模糊查询（匹配）：
在SQL Server中，通过使用通配符来实现LIKE运算，通配符“%”，“_”，“[]”。
注意：只有char、varchar和text类型的数据才能使用LIKE运算符和通配符。

1、“%”通配符：表示任意字符的匹配，且不计字符的多少。
(1)开头、结尾匹配
开头匹配(以字母“c”开头的所有数据)：SELECT * FROM table_name WHERE COLUMN LIKE 'c%'
结尾匹配(以字母“c”结尾的所有数据)：SELECT * FROM table_name WHERE COLUMN LIKE '%c'

(2)中间匹配
以“c”开头，以“b”结尾的所有数据：SELECT * FROM table_name WHERE COLUMN LIKE 'c%b'

(3)两端匹配
查询出包含字母“c”的所有数据：SELECT * FROM table_name WHERE COLUMN LIKE '%c%'

注意：“%”匹配一个或多个字符。

2、”_“通配符：”_“统配符的功能与”%“基本相同，只是它仅表示任意一个字符(区别)的匹配。若需要表示两个字符的匹配，则使用两个”_“通配符，即写成”_ _“。
(1)匹配一个字符
示例：从表中查询名称(name)为”t_fu“("_"代表任意一个字符)的所有数据，SQL语句如下：
SELECT * FROM table_name where name LIKE 't_fu' 匹配字符串有(tafu,tbfu,tcfu.....)

(2)匹配多个字符
查询名称(name)为3个字符的数据SQL语句如下：
SELECT * FROM table_name where name LIKE '_ _ _'
注意："_"只能匹配一个字符。

(3)"_"与"%"的综合应用
在模糊查询过程中，经常需要"_"和"%"一起使用才能完成查询功能。
示例：查询名称(name)第二个字母为"c"的所有数据，SQL语句如下：
SELECT * FROM table_name where name LIKE '_c%'

3、"[]"通配符："[]"通配符用于指定一系列的字符，只要满足这些字符其中之一且出现在"[]"通配符的位置的字符串就满足查询条件
(1)、各种通配符可以组合使用，必须自习分析它所表示的匹配条件

                                  匹配条件列表
   查询条件                                                      匹配条件
   LIKE '5[%]'                                                     5%
   LIKE '[_]n'                                                     _n
   LIKE '[a-f]'　　　　　　　　　　　　　　　　　　　　　　　　a,b,c,d,e,f
   LIKE '[[]'                                                       [
   LIKE '[]]'                                                       ]
   LIKE 'abc[def]'                                             abcd,abce,abcf
   LIKE 'abc[_]d%'                                             adbc_d...(省略号表示可以有任意字符)
   LIKE 'a[^a]d%'                                              不能为aad...([^]表示不能取[]内的字符)

示例：
查询名称(name)中包含"_"的所有数据:SELECT * FROM table_name WHERE name LIKE '%[_]%'
查询名称(name)中最后一个字符为"a","b","f","g"的所有数据：SELECT * FROM table_name WHERE name LIKE '%[a,b,f,g]'
查询名称(name)中最后一个字符不为"a","b","f","g"的所有数据：SELECT * FROM table_name WHERE name LIKE '%[^a,b,f,g]'

IN运算符
若只需要满足多个条件中的一个查询条件，则可以使用IN运算符。IN运算符允许根据一行记录中是否有一列包括在一系列值之中而选择改行。
所有的条件罗列在IN运算符之后，并以园括号括起来，各个条件之间使用逗号分开。
示例：
查询ID为1、3或4的数据：SELECT * FROM table_name WHERE ID IN (1,3,4)
注意：
在大多数情况下，OR运算符与IN运算符可以实现相同的功能。
示例：SELECT * FROM talbe_name WHERE ID=1 OR ID=3 OR ID=4

二者的结果完全一致，都是返回多条数据记录。但是，与OR运算符相比，IN运算符具有以下两个优点：
(1)当选择条件很多时，采用IN运算符将十分简捷，只需要在括号中用逗号间隔后一次罗列即可，运行效率高于OR运算符。
(2)IN运算符后面所列的条件可以是另一条SELECT语句，即子查询。

BETWEEN运算符
在WHERE子句中，可以采用BETWEEN运算符在两个值之间进行比较筛选。
1、用于数字类型数据比较
查询年龄(age)在6~10之前的数据：SELECT * FROM table_name WHERE age BETWEEN 6 AND 10
2、用于时间日期比较
查询日期(bridate)在"1952-01-01"到"1954-01-01"之间的数据：SELECT * FROM table_name WHERE bridate BETWEEN '1952-01-01' AND '1954-01-01'
注意：BETWEEN运算符筛选包含头和尾。例如数字类型比较，数据会包含6和10

                                  聚合函数(不能做为WHERE条件)
   函数名称                                                        函数功能
   SUM                                                             返回选取结果集所有值的和
   MAX                                                             返回选取结果集中所有值的最大值
   MIN                                                             返回选取结果集中所有值的最小值
   AVG                                                             返回选取结果集中所有值的平均值
      COUNT                                                           返回选取结果集中行的数目

使用实例：SELECT SUM(COLUMN) FROM table_name

分组查询
GROUP BY子句
原理：先通过GROUP BY后面的字段进行分组，合并相同的项。留下唯一的项，再执行SELECT后面的聚合函数。
SELET 后面只能有GROUP BY包含字段和聚合函数。

1、SELECT 后一个聚合函数情况
示例：从订单表Orders中查询各个员工最早的订单时间(OrderDate)。员工ID为(EmployeeID)
SELECT EmployeeID,min(OrderDate) FROM table_name GROUP BY EmployeeID
原理:当执行时首先在表中查询员工ID(EmployeeID),然后将相同的ID合并为一个。当所有的ID都唯一时，这时便开始通过聚合函数获得
员工的最早订单时间。

2、SELECT 后多个聚合函数情况
示例：从订单表Orders中查询各个员工最早的订单时间(OrderDate)和购买最便宜的价格(price)。员工ID为(EmployeeID)
SELECT EmployeeID,min(OrderDate),min(price) FROM table_name GROUP BY EmployeeID
原理:当执行时首先在表中查询员工ID(EmployeeID),然后将相同的ID合并为一个。当所有的ID都唯一时，这时便开始通过聚合函数获得
员工的最早订单时间，接着再通过聚合函数获得最便宜的价格。

3、GROUP BY后多个字段情况
实例：SELECT MAX(C) FROM table_name GROUP BY A,B
原理：当执行时先对A进行分组如果A中有相同项，则开始对B进行分组。如果A、B同时出现相同项。则将相同项合并，留下一个唯一项。
若A出现相同项，B为不同项。则此时不进行合并，保留此时的项。当A、B分组结束后，则开始通过聚合函数找出分组好后C的最大项。

错误情况：
SELECT EmployeeID,price FROM table_name GROUP BY EmployeeID
错误提示：选择列表中的列 'table_name.price' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
解决办法：
1、将price写在GROUP BY。(原因：没有被GROUP BY包含，Group By语句的后面，作为分组的依据)
2、将price写在聚合函数中 (例如：min(price))

错误原因：根据分组字段，将具有相同分组字段的记录归并成了一条记录。
这个时候剩下的那些不存在于Group By语句后面作为分组依据的字段就有可能出现多个值，
但是目前一种分组情况只有一条记录，一个数据格是无法放入多个数值的，
所以这里就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的数据格中，
那么完成这个步骤的就是聚合函数。这就是为什么这些函数叫聚合函数(aggregate functions)了。

总结：GROUP BY 主要是用于分组之后，再通过聚合函数获得相应的值。GROUP BY后面的字段主要是分组的依据，
当获得唯一项之后再执行SELECT后的聚合函数获得相应的值。GROUP BY 可以通过条件来获得指定的组，
例如：SELECT EmployeeID,min(OrderDate) FROM table_name WHEER price =value GROUP BY EmployeeID
另一种用法：Group By All [COLUMN]
SELECT EmployeeID,min(OrderDate) FROM table_name WHEER price =value GROUP BY All EmployeeID
分组好之后显示对应的项
聚合函数会根据返回值的类型用默认值0或者NULL来代替聚合函数的返回值。
注意：当GROUP BY子句中用于分组的列中出现了NULL值时，会将所有的NULL值分别在同
一组，即认为他们是“相等”的

HAVING子句
GROUP BY子句只是简单地依据所选列的数据进行分组，将该列具有相同值的行划为一组。而实际应用中，
往往还需要删除不满足条件的组，SQL提供HAVING子句来实现该功能。
示例：SELECT EmployeeID,MIN(price) FROM table_name GROUP BY EmployeeID HAVING MIN(price)
查询重复字段：
--查找重复记录
select * from personnel
where name in (select name from personnel group by name having count(name) > 1)
可以看出，虽然聚合函数不能直接做为where的条件。但是通过group by 和 having 组合，便可以将其作为条件进行查询

HAVING与WHERE的区别
HAVING子句与WHERE子句的相似之处在于定义了搜索条件，但与WHERE子句不同，HAVING子句与组有关，而不是与单个的行有关。
WHERE子句不能直接以聚合函数作为搜索条件。在SELECT语句中，WHERE和HAVING子句的执行顺序不同。
添加限制条件
1、SELECT EmployeeID,MIN(price) FROM table_name GROUP BY EmployeeID HAVING MIN(price)>20 AND EmployeeID>2

错误写法：SELECT EmployeeID,MIN(price) FROM table_name GROUP BY EmployeeID HAVING MIN(price)>100 AND OrderID>5
错误提示：选择列表中的列 'table_name.OrderID' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
产生错误的原因：不能将单个的OrderID的值应用于组，包括在HAVING子句中的列必须是祖列。此时，WHERE子句不能用HAVING子句代替。
为了获得所需的结果，需要需改如下：
SELECT EmployeeID,MIN(price) FROM table_name WHERE OrderID>5 GROUP BY EmployeeID HAVING MIN(price)>100

表的基本链接
1、两表链接：SELECT * FROM table_name1 [AS] a,table_name2 [AS] b WHERE a.ID=b.ID
2、多表链接：SELECT * FROM table_name1 [AS] a,table_name2 [AS] b,table_name3 [AS] c WHERE a.ID=b.ID AND b.ID=c.ID
注意：
1、使用SQL Server关键字作为表名、列名时，需要使用"[]"括起来。例如Order为SQL Server关键字，若需要创建一张名伟Order的表，则写为"CREATE TABLE [Order]"
2、SELECT语句首先执行FROM子句，由于定义表别名是在FROM子句中执行，而在其他子句中使用，所以在SELECT语句的任何子句中都可以使用表的别名。

表的连接类型
1、内链接：内连接也称等同连接，返回的结果集是两个表中所有相匹配的数据，并舍弃不匹配的数据(类似于上面的表的基本连接)。
语法：SELECT * FROM table_name1 [INNER] JOIN table_name2 ON <表达式> [WHERE] [<表达式>]
示例：SELECT * FROM Categories INNER　JOIN Prouducts ON Categories.CategorID=Prouducts.CategorID
注意：INNER关键字可以省略。

2、外连接：无论是内连接还是带WHERE子句的多表查询，都只返回相匹配的数据(内连接和外连接的区别)。即如果任何一个源表中的行
在另一个源表中没有匹配，则系统不会将该行放在最后的结果中，而在外连接系统生成的结果表中，不仅包含符合条件的行，还包括左表、
右表、或两个表中所有的数据行。
(1)左外连接(LEFT [OUTER] JOIN)
左外连接除了包括匹配的行之外，还包括JOIN关键字左表中不匹配的行，其中，右表中缺少的属性值用NULL值表示。左表连接示意图如下：


      TB_A                                     TB_B
                A      B      C                                C       D
    a1     b1     c1                               c1      d1
    a2     b2     c2                               c2      d2
    a3     b3     c3

           TB_A LEFT [OUTER] JOIN TB_B ON TB_A.C=TB_B.C
      A      B      C       C       D
         a1     b1     c1      c1      d1
         a2     b2     c2      c2      d2
         a3     b3     c3      NULL     NULL

UNION运算符
UNION运算符用于执行集合的“并”运算。
语法：SELECT * FROM table_name1 WHERE <表达式> UNION SELECT * FROM table_name1 WHERE <表达式>
内连接实现多表连接
语法：SELECT * FROM table_name1 Orders o INNER　JOIN Details od ON o.OrderID=od.OrderID INNER JOIN Employees e ON e.EmployeeID=o.EmployeeID