记录几个基础的SQL开发题
1. 表A有5行数据，表B有7行数据，问Inner Join最多返回几行数据，Left Join最多返回几行数据，分别在什么情况下？
Inner Join 是返回关联表的Cartesian product，然后根据On条件剔除掉不符合的行。这样的话，返回最多行的情况就是保留整个Cartesian product，On对每一行都为True.
即最多返回5*7=35行。
Outer Join 是返回关联表的Cartesian product，然后根据On条件剔除掉不符合的行，再将添加外部行。
外部行是指保留表中根据On条件在非保留表中找不到与之匹配行的行，非保留表的行数据用NULL值占位。
返回最多行的情况也是返回整个Cartesian product=35行。
最大返回行的示例代码：
CodeCREATE TABLE tb1 (id INT,val NVARCHAR(10))
CREATE TABLE tb2 (id INT,val NVARCHAR(10))
GO
INSERT INTO tb1 VALUES(1,'a'),(1,'b'),(1,'c'),(1,'d'),(1,'e')
INSERT INTO tb2 VALUES(1,'a'),(1,'b'),(1,'c'),(1,'d'),(1,'e'),(1,'f'),(1,'g')
GO
SELECT * FROM tb2 a JOIN tb1 b
ON a.id=b.id;
SELECT * FROM tb2 a LEFT OUTER JOIN tb1 b
ON a.id=b.id
GO
2. 有表Tb如下，写出SELECT COUNT(*),COUNT(col1),COUNT(col2),COUNT(DISTINCT col1),COUNT(DISTINCT col2),COUNT(col1+col2),COUNT(col1-col2) FROM Tb的返回结果。
| col1 | col2 |
| 1 | 1 |
| 1 | NULL |
| Null | 1 |
| Null | Null |
这是一个非常基础和细节性的问题，如果能用电脑，试一下就知道答案了，但是试题上碰到，很少人能全写对。定义问题，COUNT函数的定义如下：
返回组中的项。
COUNT(*) 返回组中的项数。包括 NULL 值和重复项。
COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。
COUNT(DISTINCT expression) 对组中的每一行都计算 expression 并返回唯一非空值的数量。
同时，NULL参与的运算，结果始终为NULL。这样结果就比较明显了：4 2 2 1 1 1 1
3.如何得到一个1~9之间的随机整数(包含1和9)？表Tb只有一列col,包含很多个这种整数，查询Tb得到如下结果,Range随机整数的范围，Count表示计数：
| Range | Count |
| 1~3 | |
| 4~5 | |
| 6~9 |
1~9之间随机整数：SELECT cast(ceiling(rand() * 9) AS INT )
rand()返加始终会是大于0且小于1的float,ceiling取大于或者等于给定表达式的最小整数，所以会得1~9之间的随机整数。
CodeCREATE TABLE tb4 (id INT)
GO
DECLARE @i INT =100
WHILE @i>0
BEGIN
INSERT INTO tb4 VALUES(cast(ceiling(rand() * 9) AS INT ));
SET @i=@i-1
END
GO
--先根据不同区段分组统计并给数据打上flag,再根据flag去sum得到总数
;WITH cte AS (
SELECT ID,COUNT(id)AS counts,
(
CASE WHEN id BETWEEN 1 AND 3 THEN 1
WHEN id BETWEEN 4 AND 5 THEN 2
WHEN id BETWEEN 6 AND 9 THEN 3
END
) as flag
FROM tb4 GROUP BY id
)
SELECT
(
CASE flag WHEN 1 THEN '1~3'
WHEN 2 THEN '4~5'
WHEN 3 THEN '6~9'
END
) as [Range],
SUM(counts)AS [Count]
FROM cte
GROUP BY flag
4. Server1上有数据库A,其镜像数据库是服务器Server2上的AM。Server2上定时生成AM的Snapshot库AS。Server2上有一个库AU，这个库中没有表，全是指向的AS的视图。
用户只能通过AU库的视图去访问AS的数据。请问该如何实现这种安全性要求。
这其实是一个Ownership chain的问题。参考资料：Ownership Chains
分析:
a. 必需有一个login(就叫tb吧)对于数据库A,AS和AU具有访问权限.假设某个用户的login叫做vw,它必需是库A和AS的public成员,同时还要对库AU中视图具有查询权限.
b. login tb在库A和AU中必需相应表和视图的Owner
c. 由于Mirroring db和Snapshot的安全配置继承自主库A且不可修改,所以在Server2上必需创建同名和同SID的login.
d. 在Server2上启用cross db ownership chaining,允许跨库的所有权链接.
测试代码:
测试代码中主库为MirrorTest,镜像库也是MirrorTest,快照库是MT_SS,视图库为MT_VIEW
a. 在Server1上创建库和相关配置
Code--create the testing database and tables
use master
go
CREATE DATABASE [MirrorTest]
go
use [MirrorTest]
go
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
go
select * into dbo.tb1 from sys.objects
select * into dbo.tb2 from sys.indexes
go --create login and configure security
use master
go
create login tb with password ='joe123',check_policy=off
create login vw with password ='joe123',check_policy=off
--select name,sid from sys.server_principals where name in('vw','tb')
--tb 0xEF38C47530A81041A4F0455F7DCE71E9
--vw 0x1652B3E456CAE549B263C6C06D6D61B1
go
use [MirrorTest]
go
create user tb
create user vw
--set USER tb as the owner of tb1&tb2
ALTER AUTHORIZATION ON dbo.tb1 TO tb
ALTER AUTHORIZATION ON dbo.tb2 TO tb
go
b. 在成功配置镜像会话之后,再在SERVER2上配置
Codeuse master
go
create login tb with password ='joe123',check_policy=off,sid=0xEF38C47530A81041A4F0455F7DCE71E9
create login vw with password ='joe123',check_policy=off,sid=0x1652B3E456CAE549B263C6C06D6D61B1
go
--drop database MT_SS
--create Snapshot of Mirroring db MirrorTest
create database MT_SS on
(Name='MirrorTest',filename='F:\SQL-DATA\MT.mdf')
as snapshot of MirrorTest
go --create view
use master
go
create database MT_View
go
use MT_View
go
create user tb
create user vw
go
create view dbo.v1
as
select * from MT_ss.dbo.tb1
go
create view dbo.v2
as
select * from MT_ss.dbo.tb2
go --set USER tb as the owner of v1&vv2
ALTER AUTHORIZATION ON dbo.v1 TO tb
ALTER AUTHORIZATION ON dbo.v2 TO tb
grant select on v1 to vw
grant select on v2 to vw
go --select * from sys.configurations where name='cross db ownership chaining'
exec sp_configure 'cross db ownership chaining',1
reconfigure with override
这个时候以vw登录SERVER2就可以实现题目所要求的.没将USER tb和vm添加到某个role中,但是tb做为对象的owner,具有较高权限.vm只是相关库public和视图的查询权限,不能直接访问主库和快照中的数据.
小结:
基础知识很重要,不然就会用时方恨少.
最新文章
- 学会使用Spring注解
- CSS3 Notes: -webkit-box-reflect实现倒影
- 解读ASP.NET 5 & MVC6系列（5）：Configuration配置信息管理
- Scrum Guide - Scrum指南中文版
- SSRS 报表点击 Preview 显示失败
- Knockoutjs 实践入门 (3) 绑定数组
- Spring3 MVC入门示例
- oracle 查看锁表情况并处理锁表
- JavaScript--Date函数
- 正则表达式（即：匹配模式Pattern）
- SPOJ 7258 Lexicographical Substring Search [后缀自动机 DP]
- SQL大数据查询优化
- 【redis 学习系列】安装-配置-卸载Redis
- 【mongoDB高级篇③】综合实战(1): 分析国家地震数据
- Python socket粘包解决
- sdn的相关学习系列之一mininet的安装
- Unity3D之物理射线
- [漏洞分析]thinkphp 5.x全版本任意代码执行分析全记录
- node-webkit学习（1）hello world
- 记一次python爬虫实战，豆瓣电影Top250爬虫