第515篇--Interview Summary (Sql Server)

在面试的时候，SQL也是一个重点，总结一下以前的面试题目：

 

1面试常见问题  SQL聚集索引和非聚集索引

 1) 我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

 2) 非聚集索引：我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。

我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。它也许同时有主题索引和作者索引。同样，一个表可以有多个非聚簇索引.

 

 

2 防止SQL注入的常用方式 ok

 

1：过滤掉特殊字符用户输入内容中的所有连字符，防止攻击者构造出类如.

2 用存储过程来执行所有的查询。SQL参数的传递方式将防止攻击者利用单引号和连字符实施攻击。此外，它还使得数据库权限可以限制到只允许特定的存储过程执行，所有的用户输入必须遵从被调用的存储过程的安全上下文，这样就很难再发生注入式攻击了。

3 限制表单或查询字符串输入的长度

4 检查提取数据的查询所返回的记录数量。如果程序只要求返回一个记录.

 

3大数据量查询？优化

1．合理使用索引

2 应用存储过程

3．避免或简化排序

4 使用临时表加速查询

5 任何地方都不要使用 select * from table ，用具体的字段列表代替“*”，不要返回用不到的任何字段.

6 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

 

4  大数据量并发？处理

 

5在SQL SERVER 中游标的声明的语句是（）释放的语句是（）。

Declare mycursor cursor for select * from AddSalary

CLOSE语句的功能是关闭一个打开的游标。关闭游标将完成以下工作：

 

6 Inner join, left join, right join, union, union all

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(等值连接) 只返回两个表中联结字段相等的行

full join：返回两个表

中的行：left join right join.

注释：在某些数据库中， FULL JOIN 称为 FULL OUTER JOIN。

select * from A

left join B

on A.aID = B.bID

 

7 UNION, UNION ALL

 UNION 的一个限制是两个 SQL 语句所产生的栏位需要是同样的资料种类。另外，当我们用 UNION这个指令时，我们只会看到不同的资料值 (类似 SELECT DISTINCT)。UNION 命令只会选取不同的值.

2 UNION ALL 这个指令的目的也是要将两个 SQL 语句的结果合并在一起。 UNION ALL 和 UNION 不同之处在于 UNION ALL 会将每一笔符合条件的资料都列出来，无论资料值有无重复。

select * from table a

union

select * from table b

 

8数据库系统表

2：获取某一个表的所有字段

select name from syscolumns where id=object_id('表名')

 

--两者等价

select id from sysobjects where name='UserOrders'

select object_id('UserOrders')

-----------------------------------------------------

 

-----------------------------------------------------

--两者等价

select name from sysobjects where id=389576426

select object_name(389576426)

-----------------------------------------------------

--例子如下：

select object_id('FK__B__ID__33D4B598','F')会取出约束名为fk_xxx_xx的ID值，如返回144444444这个值。

 

9看与某一个表相关的视图、存储过程、函数

1) select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

2) select * from sysobjects where id in (select id from syscomments where text like '%UserOrders%')

 

10 Group

GROUP BY 子句中定义的组对行进行分组。以其最简单的形式，组由称为分组列的列组成。 SELECT 子句中的列名必须为分组列或列函数, select id,sum(age) from student group by id

Having: filter满足条件的组

select id,sum(age) from student group by id

having sum(age)>100

 

11  用10代替为空的

select ISNULL(age,10) from student

 

12 Exist vs In?

在使用exists是，由于对返回结果并不关心，只关心是否有结果存在，从而使用 1 这个常量代替返回值，这种方式最大的好处在于不用关心所引述的表当中是否存在某个字段.

 

13 collate是一个子句.

可应用于数据库定义或列定义以定义排序规则.

6 说明：添加主键： Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col)

14 合并相关操作(Union,Except,Intersect)

union，Union all: 这个很简单，是把两个结果集水平合并起来

Except  就是两个集中不重复的部分,交集（intersect: 就是两个集中共同的部分

 

15 复制表：insert into test select * from student;

 

16 备份和还原：

backup database TestDBHome to disk='D:/Test.bak'

restore database TestDBHome from disk='D:/Test.bak'

 

17 新加列：

Alter table dbo.Test add TestColumn nvarchar

 

18 新加主键：

Alter table TestTable add primary key(id)

 

19 删除主键

Alter table TestTable drop primary key(id)

 

20 复制表(结构)：

select * into TargetTable from TestTable where 1<>1

select top 0 * into TargetTable2 from TestTable

 

21 复制表(数据)

insert into TargetTable2 select * from TestTable

select * into TargetTable from TestTable  (一步到位，复制表结构和数据)

 

22 两张关联表，删除主表中已经在副表中没有的信息

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

 

23 日程安排提前五分钟提醒

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

 

24 随机抽10条数据：

select top 10 * from Test order by newid()

 

25 删除重复行：

Delete from Test where Number not in

(select max(Number) from Test group by Number, Nameid, TestColumn)

 

26 删除重复数据

select distinct * into #Temp from Test2

drop table Test2

select * into Test2 from #Temp

drop table #Temp

 

27 列出数据库中所有表名：

select name from sysobjects where type='U'

 

28 查询特定表：

select * from sysobjects where [name] = 'Student3' and xtype='U'

 

29 列出数据库表中所有的列名：

select name from syscolumns where id=object_id('Test2')

特定列名是否存在：

select* from syscolumns where id=object_id('Student3') and name='id'

 

30 数据库是否存在？

select * From master.dbo.sysdatabases where name='TestDBHome'

22 查询存储过程是否存在：

select* from sysobjects where id = object_id(N'[存储过程名]')

and OBJECTPROPERTY(id, N'IsProcedure') = 1

 

31 视图是否存在：

SELECT * FROM sys.views WHERE name= 'vw_ActivityExecutionStatus'

 

32 判断function是否存在：

select* from dbo.sysobjects where id = object_id(N'[dbo].[Dateadd]')

and xtype in (N'FN', N'IF', N'TF')

 

33 查询表名，ID，创建信息：

SELECT [name],[id],crdate FROM sysobjects where xtype='U'.

 

34 drop>truncate>delete

1 delete语句是DML,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发.

 

truncate,drop是DDL, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

 

35 速度,一般来说: drop> truncate > delete

3 truncate 只能对TABLE, delete 可以是table,view,synonym

 

4 查询数据库中有相同列名的所有表：

SELECT b.name as TableName,a.name as columnname

From syscolumns a INNER JOIN sysobjects b

ON a.id=b.id

AND b.type='U'

AND a.name='id'

2）

select name from sysobjects where xtype = 'u' and id

 in(select id from syscolumns where name = 'id')

 

 

36 查询表中记录总数(大数据量很快)

select rows from sysindexes where id = object_id('Test2') and indid in (0,1)

 

37 索引的作用？和它的优点缺点是什么？

答：索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。索引是一个数据结构，用来快速访问数据库表格或者视图里的数据。在SQL Server里，它们有两种形式:聚集索引和非聚集索引。聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表格的哪个(或哪些)字段，这些字段都会按顺序被保存在表格。

 

由于存在这种排序，所以每个表格只会有一个聚集索引。非聚集索引在索引的叶级有一个行标识符。这个行标识符是一个指向磁盘上数据的指针。它允许每个表格有多个非聚集索引。微软建议开发人员使用外键限制而不是触发器来维护引用的完整性。

 

 

38 游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行.

 

39 删除数据库：

if exists (select * from sysobjects  where name='TestDB')

drop database TestDB

go

 

40 删除一个列：

Alter table TargetTable9 drop column TestColumn

 

41创建索引：

create  unique index idxname1 on TargetTable9(Nameid)

 

42查询表上是否存在索引？

select * from sysindexes where id=object_id('Student3') and name='索引名'

 

43 删除索引：drop index idxname1 on TargetTable9

 

44 计算年龄：select datediff(year,Birthday,'2013') as 年龄 from Student3

 

45 修改字段类型：ALTER TABLE Student3 ALTER COLUMN id varchar(30) NOT NULL

 

46 inner join: 这说明inner join并不以谁为基础,它只显示符合条件的记录.

 

47 删除主表没有的信息：

两张关联表delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1

 

48 随机选择记录：

select newid()

 

49  loop插入信息:

declare @i int

set @i=1

while @i<30

begin

insert into student3 values(@i,1985)

set @i=@i+1

end

 

50什么是索引，有什么优点？

答：索引象书的目录类似，索引使数据库程序无需扫描整个表，就可以在其中找到所需要的数据，索引包含了一个表中包含值的列表，其中包含了各个值的行所存储的位置，索引可以是单个或一组列，索引提供的表中数据的逻辑位置，合理划分索引能够大大提高数据库性能。

索引是一种数据结构，用来快速访问数据库表格或者视图里的数据.

 

51 游标对查询出来的结果集作为一个单元来有效的处理，游标可以定位在结果集的特定行、从结果集的当前位置检索一行或多行、可以对结果集中当前位置进行修改.

 

52 identity 约束

create table [table] (

　　[id] int primary key IDENTITY(100,1),

　　[name] text

) id从100开始自动序列增加，在插入数据的时候不需要填写ID的数据。如果不填写种子和自增量，默认从1开始。

 

53 select testtable3.*, isnull(department, '黑人')

from testtable1 right join testtable3 on testtable3.deptid=testtable1.id

查询超过3次的记录：

select * from

(select count(name) as count from testtable3 group by name) T where T.count>1

 

54 我们将主要探讨以下几种约束：

    NOT NULL

    UNIQUE

    PRIMARY KEY

    FOREIGN KEY

    CHECK

    DEFAULT

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

 

55增加unique 约束：

ALTER TABLE TableTest4

ADD UNIQUE (age)

 

 

56  primary key vs unique?

UNIQUE 约束用来标志一个数据表中的唯一一列。它与Primary key类似，但也有不同：

    Primary Key 约束自动就是 Unique 约束

    一个表中只能有一个Primary Key 约束，但可以有多个 Unique 约束.

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

 

57 引入外键：

create table Products

(

 id nvarchar(30),

 name nvarchar(40),

 foreign key (id) references Orders1(id)

)

 

58 增加check:

ALTER TABLE Orders1 ADD CHECK (id<>null) 30 CREATE INDEX 语句用于在表中创建索引。 在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据.

 

59 新加列，删除列：

ALTER TABLE Products1

drop column  column_name

 

删除列：

ALTER TABLE Products1

ADD column_name  datatype

 

修改列类型：

ALTER TABLE Products1

ALTER COLUMN id nvarchar(70)

 

60 36 SQL is null

无法使用比较运算符来测试 NULL 值，比如 =, <, 或者 <>。

我们必须使用 IS NULL 和 IS NOT NULL 操作符。

数据类型       描述       存储

char(n)   固定长度的字符串。最多 8,000 个字符。      n

varchar(n)      可变长度的字符串。最多 8,000 个字符。       

varchar(max)         可变长度的字符串。最多 1,073,741,824 个字符。         

text         可变长度的字符串。最多 2GB 字符数据。   

 

Unicode 字符串：

数据类型       描述       存储

nchar(n)         固定长度的 Unicode 数据。最多 4,000 个字符。          

nvarchar(n)    可变长度的 Unicode 数据。最多 4,000 个字符。          

nvarchar(max)       可变长度的 Unicode 数据。最多 536,870,912 个字符。       

ntext       可变长度的 Unicode 数据。最多 2GB 字符数据。        

 

61 COUNT(column)       返回某列的行数（不包括 NULL值）

select count( distinct id) from table2 返回不相同的行数

SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer

HAVING SUM(OrderPrice)<2000

 

62 提取部分字符：select substring(name,0,5) from table2

63 查找长度：select len(name) from table2

64 日期处理：select CONVERT(varchar(20), getdate(), 111 )

 

63 SQL触发器？

触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据

 

修改时执行。

触发器可通过数据库中的相关表实现级联更改；不过，通过级联引用完整性约束可以更有效地执行这些更改。

安全隐患：触发器主要是用来做历史记录的，当然可以把管理员更改密码和添加用户的历史记录下。放进一个管理员通常不会注意的、普通用户又可以看到的地方。

 

64索引？

字典的例子：字典前面的目录，可以按照拼音和部首去查询，我们想查询一个字，只需要根据拼音或者部首去查询，就可以快速的定位到这个汉字了，这个就是索引的好处，拼音查询法就是聚集索引，部首查询就是一个非聚集索引.

 

 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个，这个跟没问题没差别，一般人都知道。

  聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续，这个大家也都知道。

 

65 查询表中重复的记录(一个字段：peopleId).

select * from people where peopleId in (select  peopleId  from   people  group  by   peopleId  having  count

(peopleId) > 1)

 

2) 查找表中多余的重复记录（多个字段）

select * from vitae a

where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1)