SqlCollections - 数据管理

  1 --==============================数据管理===============================
  2 --插入记录
  3 INSERT INTO 订单明细(折扣,数量,单价,产品ID,订单ID)
  4 VALUES (0.8,3,26,2,10248)
  5 
  6 --允许插入标识列
  7 SET IDENTITY_INSERT 类别 ON
  8 GO
  9 
 10 --INSERT INTO批量插入数据
 11 --格式：INSERT INTO 表1(字段1,字段2...) + 返回结果集的语句（如SELECT * FROM 表2、EXEC sp_who）
 12 --表1必须是事先已经存在的表！
 13 CREATE TABLE 雇员通讯录
 14 (
 15     雇员ID int PRIMARY KEY,
 16     姓氏 nvarchar(20),
 17     名字 nvarchar(20)
 18 )
 19 GO
 20 INSERT INTO 雇员通讯录    --INSERT INTO 表1 SELECT 字段1,字段2 FROM 表2
 21 SELECT 雇员ID,姓氏,名字
 22 FROM 雇员
 23 GO
 24 SELECT * FROM 雇员通讯录
 25 GO
 26 TRUNCATE TABLE 雇员通讯录
 27 GO
 28 INSERT INTO 雇员通讯录(雇员ID,姓氏,名字)    --INSERT INTO 表1(字段1,字段2) SELECT 字段1,字段2 FROM 表2
 29 SELECT 雇员ID,姓氏,名字
 30 FROM 雇员
 31 GO
 32 SELECT * FROM 雇员通讯录
 33 GO
 34 DROP TABLE 雇员通讯录
 35 GO
 36 
 37 --SELECT INTO批量插入数据
 38 --格式：SELECT 字段1,字段2 INTO 表2 FROM 表1 
 39 --表2必须是事先不存在的表！
 40 SELECT 产品ID,产品名称
 41 INTO #缺货记录
 42 FROM 产品
 43 WHERE 库存量 = 0
 44 
 45 --复制表结构的方法
 46 --使用SELECT INTO,但让WHERE返回空结果集
 47 SELECT *
 48 INTO #订单2
 49 FROM 订单
 50 WHERE 0 = 1
 51 
 52 --更新记录
 53 UPDATE 产品
 54 SET 单价 = 单价 * 1.5
 55 
 56 --关联多表批量更新
 57 UPDATE b
 58 SET b.单价 = a.单价
 59 FROM 产品 a
 60 JOIN 订单明细 b
 61 ON a.产品ID = b.产品ID
 62 WHERE a.产品名称 = N‘牛奶‘
 63 
 64 --删除记录
 65 DELETE 订单 WHERE 订购日期 < ‘1996.8.1‘
 66 
 67 --删除所有记录
 68 --TRUNCATE删的快，删除后不能用事务日志恢复
 69 --TRUNCATE删除后，自增长字段会重新开始编号
 70 --若该表是其他表外键指向的表，则不能用TRUNCATE删除数据
 71 TRUNCATE TABLE 订单明细
 72 
 73 --关联多表批量删除
 74 DELETE 订单
 75 FROM 雇员 a
 76 JOIN 订单 b
 77 ON a.雇员ID = b.雇员ID
 78 WHERE a.姓氏 = N‘李‘ AND a.名字 = N‘芳‘
 79 
 80 --查看最前记录
 81 SELECT TOP 8 *    --查询出8条记录
 82 FROM 订单明细
 83 ORDER BY 数量 DESC
 84 
 85 SELECT TOP 8 WITH TIES *    --WITH TIES显示排名并列的记录，共查询出9条
 86 FROM 订单明细
 87 ORDER BY 数量 DESC
 88 
 89 SELECT TOP 10 PERCENT *        --查询出前百分之多少的记录
 90 FROM 订单明细
 91 ORDER BY 数量 DESC
 92 
 93 --查询不重复的记录
 94 SELECT DISTINCT 货主城市 FROM 订单
 95 
 96 --查询标识列
 97 SELECT $IDENTITY FROM 类别
 98 
 99 --查询GUID列
100 SELECT $ROWGUID FROM 类别
101 
102 --多表联合查询
103 --[Inner] Join:只显示符合条件的记录；
104 --Left [Outer] Join:显示左表中所有的记录，以及右表中符合条件的记录；
105 --Right [Outer] Join:显示右表中所有的记录，以及左表中符合条件的记录；
106 --Full [Outer] Join:显示所有表中的记录，包括符合条件和不符合条件的记录；
107 --Cross Join:将一个表的每条记录与另一个表的每条搭配，不需要On设置条件。
108 
109 --Join自身
110 SELECT 雇员.雇员ID,雇员.姓氏,雇员.名字,主管.姓氏,主管.名字
111 FROM 雇员
112 LEFT JOIN 雇员 AS 主管
113 ON 雇员.上级 = 主管.雇员ID
114 
115 --查询字段为NULL
116 SELECT *
117 FROM 雇员
118 WHERE 上级 IS NULL
119 
120 --替换NULL值
121 SELECT 类别ID,类别名称,ISNULL(说明,‘暂无说明‘) AS 说明
122 FROM 类别
123 
124 --模糊查询
125 --通配符：%:0个或多个字符；_:1个字符，可重用；[]:指定范围中的任意单个字符；[^]不属于指定范围内的任意单个字符。
126 --查询包含通配符的文本，只需将通配符用[]包起来
127 SELECT * 
128 FROM 产品
129 WHERE 产品名称 LIKE ‘%奶酪‘    --以奶酪结尾的
130 --WHERE 产品名称 LIKE ‘__奶酪‘    --以奶酪结尾，且必须是4个字的
131 --WHERE 产品名称 LIKE ‘[麻酱]油‘    --麻油或酱油
132 
133 --有关子查询
134 SELECT * FROM 
135 (
136 SELECT * FROM 订单
137 --ORDER BY 订单ID    --子查询不允许排序操作（例外：TOP、FOR XML）
138 )a    --子查询必须取别名
139 
140 --分组 GROUP BY
141 SELECT 货主城市,COUNT(订单ID) AS 订单总数,YEAR(订购日期) AS 订购年份
142 FROM 订单
143 GROUP BY 货主城市,YEAR(订购日期)
144 --WITH CUBE    --可选：对GROUP BY列出的所有分组字段进行汇总运算
145 WITH ROLLUP    --可选：对GROUP BY列出的第一个字段进行汇总运算
146 ORDER BY 货主城市,YEAR(订购日期)
147 
148 --HAVING子句
149 --HAVING与WHERE区别：汇总函数只能在HAVING子句中使用
150 --形式上：WHERE必定在GROUP BY之前，HAVING必定在GROUP BY之后
151 SELECT 货主城市,COUNT(订单ID) AS 订单总数
152 FROM 订单
153 GROUP BY 货主城市
154 HAVING COUNT(订单ID) > 20
155 
156 --UNION合并结果集
157 SELECT 联系人姓名,地址,电话
158 FROM 供应商
159 UNION ALL    --合并所有记录，包括重复记录
160 SELECT 联系人姓名,地址,电话
161 FROM 客户
162 UNION    --去除重复数据
163 SELECT ‘张三‘,‘aaa‘,‘123‘    --可以使用UNION加入临时数据