摘要:

转自:http://www.maomao365.com/?p=6873

转载于:

 摘要:

SQL,sqlserver2008

数据说明:

+-----------+------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+------------+---------------+--------------+--------------+------------+
| 0001      | T恤        | 衣服          |         1000 |          500 | 2009-09-20 |
| 0002      | 打孔器      | 办公用品      |          500 |          320 | 2009-09-11 |
| 0003      | 运动T恤     | 衣服          |         4000 |         2800 | NULL       |
| 0004      | 菜刀        | 厨房用具      |         3000 |         2800 | 2009-09-20 |
| 0005      | 高压锅      | 厨房用具      |         6800 |         5000 | 2009-01-15 |
| 0006      | 叉子        | 厨房用具      |          500 |         NULL | 2009-09-20 |
| 0007      | 擦菜板      | 厨房用具      |          880 |          790 | 2008-04-28 |
| 0008      | 圆珠笔      | 办公用品      |          100 |         NULL | 2009-11-11 |
+-----------+------------+---------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

下文通过举例的方式讲述sqlserver中位运算的相关知识,如下所示:
实验环境:sqlserver 2008 R2

摘要:
下文通过案例分析in 关键字在值为null的应用举例,
分析出not in关键字在null值产生的异常信息
如下所示:

用2的n次方标识单个的权限 如

下文整理sqlserver优化中的常用知识点和注意事项

对表进行聚合查询

聚合函数:

COUNT:计算表中的记录数(行数)。

SUM:计算表中数值列的数据合计值。

AVG:计算表中数值列的数据平均值。

MAX:求出表中任意列中数据的最大值。

MIN:求出表中任意列中数据的最小值。

 

COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数,而COUNT(<列名>)会得到NULL之外的数据行数。

聚合函数会将NULL排除在外。但COUNT(*)例外,并不会排除NULL。

MAX/MIN函数几乎适用所有数据类型的列。SUM/AVG函数只适用数字类型的列。

想要计算值得种类时,可以在COUNT函数的参数中使用DISTINCT。

在聚合函数的参数中使用DISTINCT,可以删除重复数据。

 


CREATE TABLE testA(keyId INT)
CREATE TABLE testB(keyId INT)
GO
INSERT INTO testA(keyId) VALUES (1)
INSERT INTO testA(keyId) VALUES (2)
INSERT INTO testA(keyId) VALUES (3)
GO
INSERT INTO testB(keyId) VALUES (1)
INSERT INTO testB(keyId) VALUES (2)
INSERT INTO testB(keyId) VALUES (4)
INSERT INTO testB(keyId) VALUES (NULL)
GO
 SELECT * FROM testA WHERE keyId IN (SELECT keyId FROM testB)
-----输出------
/*
keyId
1
2
*/
 SELECT * FROM testA WHERE keyId not IN (SELECT keyId FROM testB)
------无输出记录-----
/*
keyId
*/
INSERT INTO testA VALUES (NULL) ---在testA表中插入空值
SELECT * FROM testA WHERE EXISTS(
    SELECT null  FROM testB WHERE testA.keyId=testB.keyId    
)
----输出----
/*
keyId
1
2
*/
SELECT * FROM testA WHERE not  EXISTS(
    SELECT null  FROM testB WHERE testA.keyId=testB.keyId    
)
/*
keyId
3
NULL
*/
SELECT * FROM testA WHERE testA.keyId NOT in (
    SELECT testB.keyId FROM testB WHERE testB.keyId IS NOT NULL    
)

drop table testA
drop table testB

0 无权限
1 可读(read)
2 可新增(insert)
4 可修改(update)
8 可删除(delete)
16 可审核

权限的组合


对表进行分组

SELECT shohin_bunrui, COUNT(*)
  FROM Shohin
 GROUP BY shohin_bunrui;


+---------------+----------+
| shohin_bunrui | COUNT(*) |
+---------------+----------+
| 办公用品      |        2 |
| 厨房用具      |        4 |
| 衣服          |        2 |
+---------------+----------+

GOOUP BY 就像切分表的一把刀。

子句的书写顺序:

SELECTàFROMàWHEREàGROUP BY

SQL子句的顺序不能改变,也不能互相替换。

 

SELECT shiire_tanka, COUNT(*)
  FROM Shohin
 GROUP BY shiire_tanka;

+--------------+----------+
| shiire_tanka | COUNT(*) |
+--------------+----------+
|         NULL |        2 |
|          320 |        1 |
|          500 |        1 |
|          790 |        1 |
|         2800 |        2 |
|         5000 |        1 |
+--------------+----------+

 

聚合键中包含NULL时,在结果中会以“不确定”行(空行)的形式表现出来。

 

GROUP BY 和WHERE并用时, SELECT语句的执行顺序如下:

FROM—WHERE—GROUP BY—SELECT

 

使用聚合函数时,SELECT字句中只能存在以下三种元素:

  • 常数
  • 聚合函数
  • GROUP BY字句中指定的列明(也就是聚合键)

 

GROUP BY子句中不能够使用SELECT子句中定义的别名。

因为SELECT子句在GROUP BY子句之后执行。

 

GROUP BY子句结果的显示是无序的。

只有SELECT和HAVING子句(以及ORDER BY字句)中能够使用聚合函数。

 

 

 

 

read +insert = 1+2=3 
read +insert +delete = 1+2+8=11
read + update+delete =1+4+8=13

 

为聚合函数结果指定条件

HAVING子句:

SELECT <列名1>, <列名2>, <列名3>, …

FROM <表名>

GROUP BY <列名1>, <列名2>, <列名3>, …

HAVING <分组结果对应的条件>

 

使用HAVING子句时SELECT语句的顺序:

SELECT—FROM—WHERE—GROUP BY—HAVING

 

HAVING子句要写在GROUP BY子句之后,在DBMS内部的执行顺序也排在GROUP
BY子句之后。

SELECT shohin_bunrui, COUNT(*)
  FROM Shohin
 GROUP BY shohin_bunrui
HAVING COUNT(*) = 2;

in_bunrui | COUNT(*) |
+---------------+----------+
| 办公用品      |        2 |
| 衣服          |        2 |
+---------------+----------+

SELECT shohin_bunrui, AVG(hanbai_tanka)
  FROM Shohin
 GROUP BY shohin_bunrui
HAVING AVG(hanbai_tanka) >= 2500;

ohin_bunrui | AVG(hanbai_tanka) |
+---------------+-------------------+
| 厨房用具      |         2795.0000 |
| 衣服          |         2500.0000 |
+---------------+-------------------+

 

HAVING字句中能够使用的3种要素如下:

  • 常数
  • 聚合函数
  • GROUP BY子句中指定的列名(即聚合键)

WHERE子句 = 指定行所对应的条件

HAVING子句 = 指定组所对应的条件

在sqlserver的权限设置,我们通常使用1、2、4、8、16、32、64、128等数值分别表示相关信息的某一状态供业务状态使用,通过字段值之间的组合形成一个状态值存储到数据库中,
设置一个角色拥有的权限
例:
1:代表“查看”按钮权限
2:代表”修改”按钮
4:代表”导出”按钮
8:代表”删除”按钮

通过以上测试,我们可以看出not in
查询关键字如果子表中存在空值,则无法查询出任何记录,会导致异常产生,

需使用not
exists获取相应的空值信息

(value = 2的n次方)
增加权限
power = power | value

1、表上未建立合理的索引

对查询结构进行排序

ORDER BY子句:

SELECT <列名1>, <列名2>, <列名3>, …

FROM <表名>

ORDER BY <排序基准列1>, <排序基准列1>,…

 

不论何种情况,ORDER
BY子句都需要写在SELECT语句的末尾。这是因为对数据进行排序的操作必须在结果即将返回时执行。

子句的书写顺序:

SELECT—FROM—WHERE—GROUP BY —HAVING—ORDER BY

 

未指定ORDER BY子句中排序时会默认使用升序进行排序。ASC升序,DESC降序。

 

排序键中包含NULL时,会在开头或末尾进行汇总。(MySQL中,升序会在开头汇总,降序会在末尾汇总)

 

使用HAVING子句时SELECT语句的执行顺序:

FROM—WHERE—GROUP BY—HAVING—SELECT—ORDER BY

SELECT子句的执行顺序在GROUP BY子句之后,ORDER
BY子句之前。因此在执行GROUP
BY子句时,SELECT语句中定义的别名无法别识别。对于SELECT子句之后执行GROUP
BY子句来说,就没有这样的问题了。

 

 

在ORDER BY子句中可以使用SELECT子句中为使用的列和聚合函数.

 

SELECT shohin_mei, hanbai_tanka, shiire_tanka
  FROM Shohin
ORDER BY shohin_id;

+------------+--------------+--------------+
| shohin_mei | hanbai_tanka | shiire_tanka |
+------------+--------------+--------------+
| T恤        |         1000 |          500 |
| 打孔器     |          500 |          320 |
| 运动T恤    |         4000 |         2800 |
| 菜刀       |         3000 |         2800 |
| 高压锅     |         6800 |         5000 |
| 叉子       |          500 |         NULL |
| 擦菜板     |          880 |          790 |
| 圆珠笔     |          100 |         NULL |
+------------+--------------+--------------+


SELECT shohin_bunrui, COUNT(*)
  FROM Shohin
 GROUP BY shohin_bunrui
ORDER BY COUNT(*);

+---------------+----------+
| shohin_bunrui | COUNT(*) |
+---------------+----------+
| 办公用品      |        2 |
| 衣服          |        2 |
| 厨房用具      |        4 |
+---------------+----------+

  

 

 

 

 

 

 

 

数据说明: + —
———+————+—————+————–+————–+————+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka…

--例:
--权限表如下:
create table userRole(userId varchar(36),
roleInfo int);
go

insert into userRole(userId,roleInfo)values('test',1) ---初始化测试账号"test",拥有查看权限
insert into userRole(userId,roleInfo)values('猫猫',2) 
--当我们需要给用户加入修改"2"权限,则可以使用以下运算
update userRole set roleInfo =roleInfo|2 where userId ='test'


--当我们需要给用户加入导出"4"权限,则可以继续使用以下运算
update userRole set roleInfo =roleInfo|4 where userId ='test'


--当我们需要给用户删除导出"4"权限,则可以继续使用以下运算
--删除权限前需判断用户是否存在此权限,此脚本不可多次运行
--if exists (select null from userRole where roleInfo &4 =4 and userId ='test')
---begin
update userRole set roleInfo =roleInfo^4 where userId ='test' and roleInfo&4 =4 
--end
---我们检索所有拥有权限"修改2"的用户信息
select * from userRole where roleInfo &2 = 2

go
truncate table userRole
drop table userRole

删除权限
power = power &~ value

2、服务器内存过小,导致缓存过少,数据库频繁的交互数据

 

btw:  可以一次增加或删除多个权限的组合
power = power | 13   同时增加 read + update+delete
power = power &~ 3   同时删除 read + insert

3、统计信息不准确,导致索引乱用

注意事项:

判断权限
可以同时判断单个权限,也可以同时判断多个组合权限

4、网络异常,数据交换慢

此处权限设置,主要使用二进制中每一位代表一个权限及位运算的特性进行权限的增减操作
由于”^异或运算”运算,左表达式1或0 时
右表达式为1时,将分别产生
0、1,所以剔除权限时,一定要判断是否存在此权限

 转自:http://www.maomao365.com/?p=7137

power & value = value

5、单次数据吞吐量大,导致显示慢

6、代码出现异常,导致死锁

7、select 查询返回过多的列或行

10、查询语句写法问题

11 、数据 日志需部署在不同的磁盘上

12、从业务上对表和库进行纵向或横向分割

13、索引碎片维护

14、少用游标,使用临时表加while代替循环

15、使用union all代替union

16、谨慎使用distinct ,它可能使查询变慢,可参阅执行计划

17、between速度高于in

18、合理使用临时表,避免多次获取数据

19、select into会锁系统表 sysobjects sysindexes 等,谨慎使用

20、判断行是否存在,使用exists效率会高于count

21、所有业务都按照一定的顺序依次访问表,可以避免出现死锁现象

22、连接表,需使用小表连大表,可加快脚本速度

23、使用连接替代子查询

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图