sql面试题
--1.
CREATE TABLE TableXml
(
id INT IDENTITY(1, 1)
PRIMARY KEY ,
x XML
)
Go
INSERT INTO TableXml
SELECT '<root>
<Info id="1" name="Name1" City="City1"/>
<Info id="2" name="Name2" City="City2"/>
<Info id="3" name="Name3" City="City3"/>
</root>'
UNION
SELECT '<root>
<Info id="4" name="Name4" City="City4"/>
<Info id="5" name="Name5" City="City5"/>
<Info id="6" name="Name6" City="City6"/>
</root>'
Go
--问题：如何把<Info id="1" name="Name1" City="City1"/> 更新成 <Info id="1" name="NewName" City="NewCity"/>
--方法比较笨
--应该有比较好的吧，可是我没想到
DECLARE @NewName VARCHAR(20)
DECLARE @NewCity VARCHAR(20)
DECLARE @ID INT
SELECT @NewName = 'NewName' ,
@NewCity = 'NewCity' ,
@id = 1
UPDATE TableXml
SET x.modify('
replace value of (/root/Info[@id=sql:variable("@ID")]/@name)[1]
with sql:variable("@NewName")
') ;
UPDATE TableXml
SET x.modify('
replace value of (/root/Info[@id=sql:variable("@ID")]/@City)[1]
with sql:variable("@NewCity")
')
SELECT *
FROM TableXml
--------------------------------------------------------
--2.
/*
在SQL查询语句(Select Distinct... From ... Join ... On ... Where ... Group By ... With ... Having ... Order By ...)中，
各个步骤是按什么先后顺序执行的？同時，Where 条件中三个关键字(Or, And, Not)，它们搜索的順序是什么？
*/
--执行顺序
/*
1、FROM
2、on
3、join--做笛卡尔乘积
4、WHERE
5、group BY
6、with(cube | rollup )
7、HAVING
8、select 列表
9、DISTINCT
10、order by
*/
--当然后很多特殊的。
--------------------------------------------------------
--3.
/*
如何比较两个存储过程是否一致？
*/
;
WITH t AS ( SELECT COUNT(b.definition) AS cnt
FROM sys.objects AS a
INNER JOIN sys.sql_modules AS b ON b.object_id = a.object_id
WHERE a.is_ms_shipped = 0
AND a.type = 'P'
AND name IN ( 'p1', 'p2' )
)
SELECT CASE WHEN cnt = 1 THEN '一致'
ELSE '不一致'
END
FROM t
--这题主要是自己想象，比如：除內容外，有时间等信息的判斷
--------------------------------------------------------
--4.
/*
根据某个字段，如何查詢到对应的表和字段及数据库?
如，給出值"798DACD1-8160-45FF-B3E4-9F4840578CDB",
要求查詢出"798DACD1-8160-45FF-B3E4-9F4840578CDB"所在的表名，字段名，数据库名.
*/
--用游标遍历
DECLARE @sql VARCHAR(800)
SET @sql = '798DACD1-8160-45FF-B3E4-9F4840578CDB'
--要搜索的字符串
DECLARE @sql VARCHAR(8000)
DECLARE tablecursor CURSOR local FOR
SELECT sql='if exists ( select 1 from ['+ s.name + '].['+o.name+'] where ['+c.name+'] like ''%'+@sql+'%'' )
print ''所在的表及字段：['+o.name+'].['+c.name+']'''
FROM sys.syscolumns c
JOIN sys.objects o ON c.id=o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name != 'sys' AND o.type='u' AND c.status>=0 AND c.xusertype IN (167,175, 239, 231)
OPEN tablecursor
FETCH NEXT FROM tablecursor INTO @sql
WHILE @@fetch_status = 0
BEGIN
EXEC( @sql )
FETCH NEXT FROM tablecursor INTO @sql
END
CLOSE tablecursor
DEALLOCATE tablecursor
--------------------------------------------------------
--5.
/*
查詢与更新执行缓慢，常見原因有哪些？
这个有很多吧，简单说下
*/
/*
1、内存不足，硬件不行，或者没有足够的内存供 SQL Server 使用。
2、是否为查询优化器提供了优化复杂查询的最有利条件
3、考虑使用磁盘条带化。使用 0 级 RAID（独立磁盘冗余阵列）可实现磁盘条带化，在这种 RAID 上，数据分布在多个磁盘驱动器上。
4、网络通讯不好
5、缺少有用的统计信息、索引、索引视图、数据条带化、分区等，当然这些东西滥用则会成为累赘
6、磁盘配置了，如tempdb 的配置--这个只是了解些，呵呵
*/
--------------------------------------------------------
--6.
/*
如何通过SQL脚本來，变更某一个数据库文件名(物理文件)？
修改数据库名称
*/
sp_renamedb '老名字','新名字'
--or
alter database 老数据库名字 modify name=新数据库名字
--修改文件名称以及文件位置，大小等使用 alter database 语句
alter database 数据库名称modify file(name='旧文件名',newname='新文件名',filename='新路径')
--写一个例子
use master
go
create database db
go
sp_renamedb 'db','new_db'
go
use new_db
go
--这时候文件名是
select name from sysfiles
/*
db
db_log
*/
--只是数据库名字被修改了，如果想要修改.mdf和.ldf文件名则需要使用下面的语句
alter database new_db modify file(name='db',newname='new_db')
alter database new_db modify file(name='db_log',newname='new_db_log')
select name from sysfiles
/*
new_db
new_db_log
*/
--------------------------------------------------------
CREATE TABLE TableXml
(
id INT IDENTITY(1, 1)
PRIMARY KEY ,
x XML
)
Go
INSERT INTO TableXml
SELECT '<root>
<Info id="1" name="Name1" City="City1"/>
<Info id="2" name="Name2" City="City2"/>
<Info id="3" name="Name3" City="City3"/>
</root>'
UNION
SELECT '<root>
<Info id="4" name="Name4" City="City4"/>
<Info id="5" name="Name5" City="City5"/>
<Info id="6" name="Name6" City="City6"/>
</root>'
Go
--问题：如何把<Info id="1" name="Name1" City="City1"/> 更新成 <Info id="1" name="NewName" City="NewCity"/>
--方法比较笨
--应该有比较好的吧，可是我没想到
DECLARE @NewName VARCHAR(20)
DECLARE @NewCity VARCHAR(20)
DECLARE @ID INT
SELECT @NewName = 'NewName' ,
@NewCity = 'NewCity' ,
@id = 1
UPDATE TableXml
SET x.modify('
replace value of (/root/Info[@id=sql:variable("@ID")]/@name)[1]
with sql:variable("@NewName")
') ;
UPDATE TableXml
SET x.modify('
replace value of (/root/Info[@id=sql:variable("@ID")]/@City)[1]
with sql:variable("@NewCity")
')
SELECT *
FROM TableXml
--------------------------------------------------------
--2.
/*
在SQL查询语句(Select Distinct... From ... Join ... On ... Where ... Group By ... With ... Having ... Order By ...)中，
各个步骤是按什么先后顺序执行的？同時，Where 条件中三个关键字(Or, And, Not)，它们搜索的順序是什么？
*/
--执行顺序
/*
1、FROM
2、on
3、join--做笛卡尔乘积
4、WHERE
5、group BY
6、with(cube | rollup )
7、HAVING
8、select 列表
9、DISTINCT
10、order by
*/
--当然后很多特殊的。
--------------------------------------------------------
--3.
/*
如何比较两个存储过程是否一致？
*/
;
WITH t AS ( SELECT COUNT(b.definition) AS cnt
FROM sys.objects AS a
INNER JOIN sys.sql_modules AS b ON b.object_id = a.object_id
WHERE a.is_ms_shipped = 0
AND a.type = 'P'
AND name IN ( 'p1', 'p2' )
)
SELECT CASE WHEN cnt = 1 THEN '一致'
ELSE '不一致'
END
FROM t
--这题主要是自己想象，比如：除內容外，有时间等信息的判斷
--------------------------------------------------------
--4.
/*
根据某个字段，如何查詢到对应的表和字段及数据库?
如，給出值"798DACD1-8160-45FF-B3E4-9F4840578CDB",
要求查詢出"798DACD1-8160-45FF-B3E4-9F4840578CDB"所在的表名，字段名，数据库名.
*/
--用游标遍历
DECLARE @sql VARCHAR(800)
SET @sql = '798DACD1-8160-45FF-B3E4-9F4840578CDB'
--要搜索的字符串
DECLARE @sql VARCHAR(8000)
DECLARE tablecursor CURSOR local FOR
SELECT sql='if exists ( select 1 from ['+ s.name + '].['+o.name+'] where ['+c.name+'] like ''%'+@sql+'%'' )
print ''所在的表及字段：['+o.name+'].['+c.name+']'''
FROM sys.syscolumns c
JOIN sys.objects o ON c.id=o.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name != 'sys' AND o.type='u' AND c.status>=0 AND c.xusertype IN (167,175, 239, 231)
OPEN tablecursor
FETCH NEXT FROM tablecursor INTO @sql
WHILE @@fetch_status = 0
BEGIN
EXEC( @sql )
FETCH NEXT FROM tablecursor INTO @sql
END
CLOSE tablecursor
DEALLOCATE tablecursor
--------------------------------------------------------
--5.
/*
查詢与更新执行缓慢，常見原因有哪些？
这个有很多吧，简单说下
*/
/*
1、内存不足，硬件不行，或者没有足够的内存供 SQL Server 使用。
2、是否为查询优化器提供了优化复杂查询的最有利条件
3、考虑使用磁盘条带化。使用 0 级 RAID（独立磁盘冗余阵列）可实现磁盘条带化，在这种 RAID 上，数据分布在多个磁盘驱动器上。
4、网络通讯不好
5、缺少有用的统计信息、索引、索引视图、数据条带化、分区等，当然这些东西滥用则会成为累赘
6、磁盘配置了，如tempdb 的配置--这个只是了解些，呵呵
*/
--------------------------------------------------------
--6.
/*
如何通过SQL脚本來，变更某一个数据库文件名(物理文件)？
修改数据库名称
*/
sp_renamedb '老名字','新名字'
--or
alter database 老数据库名字 modify name=新数据库名字
--修改文件名称以及文件位置，大小等使用 alter database 语句
alter database 数据库名称modify file(name='旧文件名',newname='新文件名',filename='新路径')
--写一个例子
use master
go
create database db
go
sp_renamedb 'db','new_db'
go
use new_db
go
--这时候文件名是
select name from sysfiles
/*
db
db_log
*/
--只是数据库名字被修改了，如果想要修改.mdf和.ldf文件名则需要使用下面的语句
alter database new_db modify file(name='db',newname='new_db')
alter database new_db modify file(name='db_log',newname='new_db_log')
select name from sysfiles
/*
new_db
new_db_log
*/
--------------------------------------------------------
