2020年1月 – 那啥快看

SQLServer面试题总结

一、Sql常用语法

下列语句部分是Mssql语句，不可以在access中使用。

SQL分类：

DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)

DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)

DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 首先,简要介绍基础语句：

1、说明：创建数据库

CREATE DATABASE database-name

2、说明：删除数据库

drop database dbname

3、说明：备份sql server

— 创建备份数据的 device

USE master

EXEC sp_addumpdevice ‘disk’, ‘testBack’, ‘c:mssql7backupMyNwind_1.dat’

— 开始备份

BACKUP DATABASE pubs TO testBack

4、说明：创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)根据已有的表创建新表：

A：create table tab_new like tab_old (使用旧表创建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

5、说明：删除新表drop table tabname

6、说明：增加一个列

Alter table tabname add column col type注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

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

8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname注：索引是不可更改的，想更改必须删除重新建。

9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname

10、说明：几个简单的基本的sql语句选择：select * from table1 where 范围插入：insert into table1(field1,field2) s(1,2)删除：delete from table1 where 范围更新：update table1 set field1=1 where 范围查找：select * from table1 where field1 like ’%1%’ —like的语法很精妙，查资料!排序：select * from table1 order by field1,field2 [desc]总数：select count * as totalcount from table1求和：select sum(field1) as sum from table1平均：select avg(field1) as avg from table1最大：select max(field1) as max from table1最小：select min(field1) as min from table1

11、说明：几个高级查询运算词

A： UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

C： INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。注：使用运算词的几个查询结果行必须是一致的。

12、说明：使用外连接

A、left outer join：左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right outer join: 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

C：full outer join：全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

不错的sql语句

1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)法一：select * into b from a where 1<>1法二：select top 0 * into b from a

2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)

insert into b(a, b, c) select d,e,f from b;

3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件例子：..from b in ‘”&Server.MapPath(“.”)&”data.mdb” &”‘ where..

4、说明：子查询(表名1：a 表名2：b)

select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

5、说明：显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

6、说明：外连接查询(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、说明：在线视图查询(表名1：a )

select * from (SELECT a,b,c FROM a) T where t.a > 1;

8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

9、说明：in 的使用方法

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

10、说明：两张关联表，删除主表中已经在副表中没有的信息

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

11、说明：四表联查问题：

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where …..

12、说明：日程安排提前五分钟提醒

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

13、说明：一条sql 语句搞定数据库分页

select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

14、说明：前10条记录

select top 10 * form table1 where 范围

15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(select a from tableA ) except (select a from tableB) except (select a from tableC)

17、说明：随机取出10条数据

select top 10 * from tablename order by newid()

18、说明：随机选择记录

select newid()

19、说明：删除重复记录

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,…)

20、说明：列出数据库里所有的表名

select name from sysobjects where type=’U’

21、说明：列出表里的所有的

select name from syscolumns where id=object_id(‘TableName’)

22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。

select type,sum(case vender when ‘A’ then pcs else 0 end),sum(case vender when ‘C’ then pcs else 0 end),sum(case vender when ‘B’ then pcs else 0 end) FROM tablename group by type显示结果：

type vender pcs电脑 A 1电脑 A 1光盘 B 2光盘 A 2手机 B 3手机 C 3

23、说明：初始化表table1

TRUNCATE TABLE table1

24、说明：选择从10到15的记录

select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

二、sql技巧

如何删除一个表中重复的记录？

create table a_dist(id int,name varchar(20))

insert into a_dist values(1,’abc’)

exec up_distinct ‘a_dist’,’id’

select * from a_dist

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))

–f_key表示是分组字段﹐即主键字段

begin

declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer

select @sql = ‘declare cur_rows cursor for select ‘+@f_key+’ ,count(*) from ‘ +@t_name +’ group by ‘ +@f_key +’ having count(*) > 1′

exec(@sql)

open cur_rows

fetch cur_rows into @id,@max

while @@fetch_status=0

begin

select @max = @max -1

set rowcount @max

select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key

if @type=56

select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+ @id

if @type=167

select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+””+ @id +””

exec(@sql)

fetch cur_rows into @id,@max

end

close cur_rows

deallocate cur_rows

set rowcount 0

end

select * from systypes

select * from syscolumns where id = object_id(‘a_dist’)

查询数据的最大排序问题（只能用一条语句写）

CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))

insert into hard values (‘A’,’1′,3)

insert into hard values (‘A’,’2′,4)

insert into hard values (‘A’,’4′,2)

insert into hard values (‘A’,’6′,9)

insert into hard values (‘B’,’1′,4)

insert into hard values (‘B’,’2′,5)

insert into hard values (‘B’,’3′,6)

insert into hard values (‘C’,’3′,4)

insert into hard values (‘C’,’6′,7)

insert into hard values (‘C’,’2′,3)

要求查询出来的结果如下：

qu co je

———– ———– —–

A 6 9

A 2 4

B 3 6

B 2 5

C 6 7

C 3 4

就是要按qu分组，每组中取je最大的前2位！！

而且只能用一句sql语句！！！

select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)

求删除重复记录的sql语句？

怎样把具有相同字段的纪录删除，只留下一条。

例如，表test里有id,name字段

如果有name相同的记录只留下一条，其余的删除。

name的内容不定，相同的记录数不定。

有没有这样的sql语句？

==============================

A:一个完整的解决方案：

将重复的记录记入temp1表:

select [标志字段id],count(*) into temp1 from [表名]

group by [标志字段id]

having count(*)>1

2、将不重复的记录记入temp1表:

insert temp1 select [标志字段id],count(*) from [表名] group by [标志字段id] having count(*)=1

3、作一个包含所有不重复记录的表：

select * into temp2 from [表名] where 标志字段id in(select 标志字段id from temp1)

4、删除重复表:

delete [表名]

5、恢复表：

insert [表名] select * from temp2

6、删除临时表:

drop table temp1

drop table temp2

================================

create table a_dist(id int,name varchar(20))

insert into a_dist values(1,’abc’)

exec up_distinct ‘a_dist’,’id’

select * from a_dist

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))

–f_key表示是分组字段﹐即主键字段

begin

declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer

select @sql = ‘declare cur_rows cursor for select ‘+@f_key+’ ,count(*) from ‘ +@t_name +’ group by ‘ +@f_key +’ having count(*) > 1′

exec(@sql)

open cur_rows

fetch cur_rows into @id,@max

while @@fetch_status=0

begin

select @max = @max -1

set rowcount @max

select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key

if @type=56

select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+ @id

if @type=167

select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+””+ @id +””

exec(@sql)

fetch cur_rows into @id,@max

end

close cur_rows

deallocate cur_rows

set rowcount 0

end

select * from systypes

select * from syscolumns where id = object_id(‘a_dist’)

行列转换–普通

假设有张学生成绩表(CJ)如下

Name Subject Result

张三语文 80

张三数学 90

张三物理 85

李四语文 85

李四数学 92

李四物理 82

想变成

姓名语文数学物理

张三 80 90 85

李四 85 92 82

declare @sql varchar(4000)

set @sql = ‘select Name’

select @sql = @sql + ‘,sum(case Subject when ”’+Subject+”’ then Result end) [‘+Subject+’]’

from (select distinct Subject from CJ) as a

select @sql = @sql+’ from test group by name’

exec(@sql)

行列转换–合并

有表A,

id pid

1 1

1 2

1 3

2 1

2 2

3 1

如何化成表B:

id pid

1 1,2,3

2 1,2

3 1

创建一个合并的函数

create function fmerg(@id int)

returns varchar(8000)

begin

declare @str varchar(8000)

set @str=”

select @str=@str+’,’+cast(pid as varchar) from 表A where id=@id

set @str=right(@str,len(@str)-1)

return(@str)

End

–调用自定义函数得到结果

select distinct id,dbo.fmerg(id) from 表A

如何取得一个数据表的所有列名

方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。

SQL语句如下：

declare @objid int,@objname char(40)

set @objname = ‘tablename’

select @objid = id from sysobjects where id = object_id(@objname)

select ‘Column_name’ = name from syscolumns where id = @objid order by colid

或

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =’users’

通过SQL语句来更改用户的密码

修改别人的,需要sysadmin role

EXEC sp_password NULL, ‘newpassword’, ‘User’

如果帐号为SA执行EXEC sp_password NULL, ‘newpassword’, sa

怎么判断出一个表的哪些字段不允许为空？

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=’NO’ and TABLE_NAME=tablename

如何在数据库里找到含有相同字段的表？

a. 查已知列名的情况

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=’你的字段名字’

未知列名查所有在不同表出现过的列名

Select o.name As tablename,s1.name As columnname

From syscolumns s1, sysobjects o

Where s1.id = o.id

And o.type = ‘U’

And Exists (

Select 1 From syscolumns s2

Where s1.name = s2.name

And s1.id <> s2.id

)

查询第xxx行数据

假设id是主键：

select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)

如果使用游标也是可以的

fetch absolute [number] from [cursor_name]

行数为绝对行数

SQL Server日期计算

a. 一个月的第一天

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

b. 本周的星期一

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

c. 一年的第一天

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

d. 季度的第一天

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

e. 上个月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

f. 去年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

g. 本月的最后一天

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))

h. 本月的第一个星期一

select DATEADD(wk, DATEDIFF(wk,0,

dateadd(dd,6-datepart(day,getdate()),getdate())

), 0)

i. 本年的最后一天

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。

获取表结构[把 ‘sysobjects’ 替换成 ‘tablename’ 即可]

SELECT CASE IsNull(I.name, ”)

When ” Then ”

Else ‘*’

End as IsPK,

Object_Name(A.id) as t_name,

A.name as c_name,

IsNull(SubString(M.text, 1, 254), ”) as pbc_init,

T.name as F_DataType,

CASE IsNull(TYPEPROPERTY(T.name, ‘Scale’), ”)

WHEN ” Then Cast(A.prec as varchar)

ELSE Cast(A.prec as varchar) + ‘,’ + Cast(A.scale as varchar)

END as F_Scale,

A.isnullable as F_isNullAble

FROM Syscolumns as A

JOIN Systypes as T

ON (A.xType = T.xUserType AND A.Id = Object_id(‘sysobjects’) )

LEFT JOIN ( SysIndexes as I

JOIN Syscolumns as A1

ON ( I.id = A1.id and A1.id = object_id(‘sysobjects’) and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) )

ON ( A.id = I.id AND A.name = index_col(‘sysobjects’, I.indid, A1.colid) )

LEFT JOIN SysComments as M

ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, ‘IsConstraint’) = 1 )

ORDER BY A.Colid ASC

提取数据库内所有表的字段详细说明的SQL语句

SELECT

(case when a.colorder=1 then d.name else ” end) N’表名’,

a.colorder N’字段序号’,

a.name N’字段名’,

(case when COLUMNPROPERTY( a.id,a.name,’IsIdentity’)=1 then ‘√’else ”

end) N’标识’,

(case when (SELECT count(*)

FROM sysobjects

WHERE (name in

(SELECT name

FROM sysindexes

WHERE (id = a.id) AND (indid in

(SELECT indid

FROM sysindexkeys

WHERE (id = a.id) AND (colid in

(SELECT colid

FROM syscolumns

WHERE (id = a.id) AND (name = a.name))))))) AND

(xtype = ‘PK’))>0 then ‘√’ else ” end) N’主键’,

b.name N’类型’,

a.length N’占用字节数’,

COLUMNPROPERTY(a.id,a.name,’PRECISION’) as N’长度’,

isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0) as N’小数位数’,

(case when a.isnullable=1 then ‘√’else ” end) N’允许空’,

isnull(e.text,”) N’默认值’,

isnull(g.[value],”) AS N’字段说明’

FROM syscolumns a

left join systypes b

on a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id and d.xtype=’U’ and d.name<>’dtproperties’

left join syscomments e

on a.cdefault=e.id

left join sysproperties g

on a.id=g.id AND a.colid = g.smallid

order by object_name(a.id),a.colorder

快速获取表test的记录总数[对大容量表非常有效]

快速获取表test的记录总数:

select rows from sysindexes where id = object_id(‘test’) and indid in (0,1)

update 2 set KHXH=(ID+1)\2 2行递增编号

update [23] set id1 = ‘No.’+right(‘00000000’+id,6) where id not like ‘No%’ //递增

update [23] set id1= ‘No.’+right(‘00000000’+replace(id1,’No.’,”),6) //补位递增

delete from [1] where (id%2)=1

奇数

替换表名字段

update [1] set domurl = replace(domurl,’Upload/Imgswf/’,’Upload/Photo/’) where domurl like ‘%Upload/Imgswf/%’

截位

SELECT LEFT(表名, 5)

截位

SELECT LEFT(表名, 5)

三、（MS SQL Server）SQL语句导入导出大全

/******* 导出到excel

EXEC master..xp_cmdshell ‘bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S”GNETDATA/GNETDATA” -U”sa” -P””‘

/*********** 导入Excel

SELECT *

FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,

‘Data Source=”c:\test.xls”;User ID=Admin;Password=;Extended properties=Excel 5.0′)…xactions

SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+’　’ 转换后的别名

FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,

‘Data Source=”c:\test.xls”;User ID=Admin;Password=;Extended properties=Excel 5.0’)…xactions

select * from OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0′,’Excel 5.0;HDR=YES;DATABASE=c:\Book1.xls’,Sheet1$)

HDR=YES;Excel第一行当成标题行

HDR=NO;第一行不当成标题行

/** 导入文本文件

EXEC master..xp_cmdshell ‘bcp “dbname..tablename” in c:\DT.txt -c -Sservername -Usa -Ppassword’

/** 导出文本文件

EXEC master..xp_cmdshell ‘bcp “dbname..tablename” out c:\DT.txt -c -Sservername -Usa -Ppassword’

或

EXEC master..xp_cmdshell ‘bcp “Select * from dbname..tablename” queryout c:\DT.txt -c -Sservername -Usa -Ppassword’

导出到TXT文本，用逗号分开

exec master..xp_cmdshell ‘bcp “库名..表名” out “d:\tt.txt” -c -t ,-U sa -P password’

BULK INSERT 库名..表名

FROM ‘c:\test.txt’

WITH (

FIELDTERMINATOR = ‘;’,

ROWTERMINATOR = ‘\n’

)

–/* dBase IV文件

select * from

OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0′

,’dBase IV;HDR=NO;IMEX=2;DATABASE=C:\’,’select * from [客户资料4.dbf]’)

–*/

–/* dBase III文件

select * from

OPENROWSET(‘MICROSOFT.JET.OLEDB.4.0′

,’dBase III;HDR=NO;IMEX=2;DATABASE=C:\’,’select * from [客户资料3.dbf]’)

–*/

–/* FoxPro 数据库

select * from openrowset(‘MSDASQL’,

‘Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\’,

‘select * from [aa.DBF]’)

–*/

/**************导入DBF文件****************/

select * from openrowset(‘MSDASQL’,

‘Driver=Microsoft Visual FoxPro Driver;

SourceDB=e:\VFP98\data;

SourceType=DBF’,

‘select * from customer where country != “USA” order by country’)

/***************** 导出到DBF ***************/

如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句

insert into openrowset(‘MSDASQL’,

‘Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\’,

‘select * from [aa.DBF]’)

select * from 表

说明:

SourceDB=c:\ 指定foxpro表所在的文件夹

aa.DBF 指定foxpro表的文件名.

/*************导出到Access********************/

insert into openrowset(‘Microsoft.Jet.OLEDB.4.0’,

‘x:\A.mdb’;’admin’;”,A表) select * from 数据库名..B表

/*************导入Access********************/

insert into B表 selet * from openrowset(‘Microsoft.Jet.OLEDB.4.0’,

‘x:\A.mdb’;’admin’;”,A表)

********************* 导入 xml　文件

DECLARE @idoc int

DECLARE @doc varchar(1000)

–sample XML document

SET @doc =’

<root>

<Order oid=”O2″ date=”4/30/1997″ amount=”13.4″>Customer was very satisfied

</Order>

</Customer>

<Order oid=”O3″ date=”7/14/1999″ amount=”100″ note=”Wrap it blue

white red”>

<Urgency>Important</Urgency>

Happy Customer.

</Order>

</Customer>

</root>

‘

— Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

— Execute a SELECT statement using OPENXML rowset provider.

SELECT *

FROM OPENXML (@idoc, ‘/root/Customer/Order’, 1)

WITH (oid char(5),

amount float,

comment ntext ‘text()’)

EXEC sp_xml_removedocument @idoc

/********************导整个数据库*********************************************/

用bcp实现的存储过程

实现数据导入/导出的存储过程

根据不同的参数,可以实现导入/导出整个数据库/单个表

调用示例:

–导出调用示例

—-导出单个表

exec file2table ‘zj’,”,”,’xzkh_sa..地区资料’,’c:\zj.txt’,1

—-导出整个数据库

exec file2table ‘zj’,”,”,’xzkh_sa’,’C:\docman’,1

–导入调用示例

—-导入单个表

exec file2table ‘zj’,”,”,’xzkh_sa..地区资料’,’c:\zj.txt’,0

—-导入整个数据库

exec file2table ‘zj’,”,”,’xzkh_sa’,’C:\docman’,0

if exists(select 1 from sysobjects where name=’File2Table’ and objectproperty(id,’IsProcedure’)=1)

drop procedure File2Table

create procedure File2Table

@servername varchar(200) –服务器名

,@username varchar(200) –用户名,如果用NT验证方式,则为空”

,@password varchar(200) –密码

,@tbname varchar(500) –数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表

,@filename varchar(1000) –导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt

,@isout bit –1为导出,0为导入

declare @sql varchar(8000)

if @tbname like ‘%.%.%’ –如果指定了表名,则直接导出单个表

begin

set @sql=’bcp ‘+@tbname

+case when @isout=1 then ‘ out ‘ else ‘ in ‘ end

+’ “‘+@filename+'” /w’

+’ /S ‘+@servername

+case when isnull(@username,”)=” then ” else ‘ /U ‘+@username end

+’ /P ‘+isnull(@password,”)

exec master..xp_cmdshell @sql

end

else

begin –导出整个数据库,定义游标,取出所有的用户表

declare @m_tbname varchar(250)

if right(@filename,1)<>’\’ set @filename=@filename+’\’

set @m_tbname=’declare #tb cursor for select name from ‘+@tbname+’..sysobjects where xtype=”U”’

exec(@m_tbname)

open #tb

fetch next from #tb into @m_tbname

while @@fetch_status=0

begin

set @sql=’bcp ‘+@tbname+’..’+@m_tbname

+case when @isout=1 then ‘ out ‘ else ‘ in ‘ end

+’ “‘+@filename+@m_tbname+’.txt ” /w’

+’ /S ‘+@servername

+case when isnull(@username,”)=” then ” else ‘ /U ‘+@username end

+’ /P ‘+isnull(@password,”)

exec master..xp_cmdshell @sql

fetch next from #tb into @m_tbname

end

close #tb

deallocate #tb

end

/**********************Excel导到Txt****************************************/

想用

select * into opendatasource(…) from opendatasource(…)

实现将一个Excel文件内容导入到一个文本文件

假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位)

且银行帐号导出到文本文件后分两部分，前8位和后8位分开。

如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2

然后就可以用下面的语句进行插入

注意文件名和目录根据你的实际情况进行修改.

insert into

opendatasource(‘MICROSOFT.JET.OLEDB.4.0′

,’Text;HDR=Yes;DATABASE=C:\’

)…[aa#txt]

–,aa#txt)

–*/

select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)

from

opendatasource(‘MICROSOFT.JET.OLEDB.4.0′

,’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls’

–,Sheet1$)

)…[Sheet1$]

如果你想直接插入并生成文本文件,就要用bcp

declare @sql varchar(8000),@tbname varchar(50)

–首先将excel表内容导入到一个全局临时表

select @tbname='[##temp’+cast(newid() as varchar(40))+’]’

,@sql=’select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)

into ‘+@tbname+’ from

opendatasource(”MICROSOFT.JET.OLEDB.4.0”

,”Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls”

)…[Sheet1$]’

exec(@sql)

–然后用bcp从全局临时表导出到文本文件

set @sql=’bcp “‘+@tbname+'” out “c:\aa.txt” /S”(local)” /P”” /c’

exec master..xp_cmdshell @sql

–删除临时表

exec(‘drop table ‘+@tbname)

用bcp将文件导入导出到数据库的存储过程:

/*–bcp-二进制文件的导入导出

支持image,text,ntext字段的导入/导出

image适合于二进制文件;text,ntext适合于文本数据文件

注意:导入时,将覆盖满足条件的所有行

导出时,将把所有满足条件的行也出到指定文件中

此存储过程仅用bcp实现

邹建 2003.08—————–*/

/*–调用示例

–数据导出

exec p_binaryIO ‘zj’,”,”,’acc_演示数据..tb’,’img’,’c:\zj1.dat’

–数据导出

exec p_binaryIO ‘zj’,”,”,’acc_演示数据..tb’,’img’,’c:\zj1.dat’,”,0

–*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_binaryIO]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)

drop procedure [dbo].[p_binaryIO]

Create proc p_binaryIO

@servename varchar (30),–服务器名称

@username varchar (30), –用户名

@password varchar (30), –密码

@tbname varchar (500), –数据库..表名

@fdname varchar (30), –字段名

@fname varchar (1000), –目录+文件名,处理过程中要使用/覆盖:@filename+.bak

@tj varchar (1000)=”, –处理条件.对于数据导入,如果条件中包含@fdname,请指定表名前缀

@isout bit=1 –1导出((默认),0导入

declare @fname_in varchar(1000) –bcp处理应答文件名

,@fsize varchar(20) –要处理的文件的大小

,@m_tbname varchar(50) –临时表名

,@sql varchar(8000)

–则取得导入文件的大小

if @isout=1

set @fsize=’0′

else

begin

create table #tb(可选名 varchar(20),大小 int

,创建日期 varchar(10),创建时间 varchar(20)

,上次写操作日期 varchar(10),上次写操作时间 varchar(20)

,上次访问日期 varchar(10),上次访问时间 varchar(20),特性 int)

insert into #tb

exec master..xp_getfiledetails @fname

select @fsize=大小 from #tb

drop table #tb

if @fsize is null

begin

print ‘文件未找到’

return

end

–生成数据处理应答文件

set @m_tbname='[##temp’+cast(newid() as varchar(40))+’]’

set @sql=’select * into ‘+@m_tbname+’ from(

select null as 类型

union all select 0 as 前缀

union all select ‘+@fsize+’ as 长度

union all select null as 结束

union all select null as 格式

) a’

exec(@sql)

select @fname_in=@fname+’_temp’

,@sql=’bcp “‘+@m_tbname+'” out “‘+@fname_in

+'” /S”‘+@servename

+case when isnull(@username,”)=” then ”

else ‘” /U”‘+@username end

+'” /P”‘+isnull(@password,”)+'” /c’

exec master..xp_cmdshell @sql

–删除临时表

set @sql=’drop table ‘+@m_tbname

exec(@sql)

if @isout=1

begin

set @sql=’bcp “select top 1 ‘+@fdname+’ from ‘

+@tbname+case isnull(@tj,”) when ” then ”

else ‘ where ‘+@tj end

+'” queryout “‘+@fname

+'” /S”‘+@servename

+case when isnull(@username,”)=” then ”

else ‘” /U”‘+@username end

+'” /P”‘+isnull(@password,”)

+'” /i”‘+@fname_in+'”‘

exec master..xp_cmdshell @sql

end

else

begin

–为数据导入准备临时表

set @sql=’select top 0 ‘+@fdname+’ into ‘

+@m_tbname+’ from ‘ +@tbname

exec(@sql)

–将数据导入到临时表

set @sql=’bcp “‘+@m_tbname+'” in “‘+@fname

+'” /S”‘+@servename

+case when isnull(@username,”)=” then ”

else ‘” /U”‘+@username end

+'” /P”‘+isnull(@password,”)

+'” /i”‘+@fname_in+'”‘

exec master..xp_cmdshell @sql

–将数据导入到正式表中

set @sql=’update ‘+@tbname

+’ set ‘+@fdname+’=b.’+@fdname

+’ from ‘+@tbname+’ a,’

+@m_tbname+’ b’

+case isnull(@tj,”) when ” then ”

else ‘ where ‘+@tj end

exec(@sql)

–删除数据处理临时表

set @sql=’drop table ‘+@m_tbname

end

–删除数据处理应答文件

set @sql=’del ‘+@fname_in

exec master..xp_cmdshell @sql

/** 导入文本文件

EXEC master..xp_cmdshell ‘bcp “dbname..tablename” in c:\DT.txt -c -Sservername -Usa -Ppassword’

改为如下，不需引号

EXEC master..xp_cmdshell ‘bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword’

/** 导出文本文件

EXEC master..xp_cmdshell ‘bcp “dbname..tablename” out c:\DT.txt -c -Sservername -Usa -Ppassword’

此句需加引号

四、sql常见面试题

sql理论题

1．触发器的作用？

答：触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

2。什么是存储过程？用什么来调用？

答：存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。

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

答：索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

3。什么是内存泄漏？

答：一般我们所说的内存泄漏指的是堆内存的泄漏。堆内存是程序从堆中为其分配的，大小任意的，使用完后要显示释放内存。当应用程序用关键字new等创建对象时，就从堆中为它分配一块内存，使用完后程序调用free或者delete释放该内存，否则就说该内存就不能被使用，我们就说该内存被泄漏了。

4。维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？

答：我是这样做的，尽可能使用约束，如check,主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。

5。什么是事务？什么是锁？

答：事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

锁：在所以的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

6。什么叫视图？游标是什么？

答：视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

7。为管理业务培训信息，建立3个表：

S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号，学员姓名，所属单位，学员年龄

C(C#,CN)C#,CN分别代表课程编号，课程名称

SC(S#,C#,G) S#,C#,G分别代表学号，所选的课程编号，学习成绩

（1）使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?

答案：select s# ,sn from s where S# in(select S# from c,sc where c.c#=sc.c# and cn=’税收基础’)

(2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?

答：select sn,sd from s,sc where s.s#=sc.s# and sc.c#=’c2’

(3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?

答：select sn,sd from s where s# not in(select s# from sc where c#=’c5’)

(4)查询选修了课程的学员人数

答：select 学员人数=count(distinct s#) from sc

(5) 查询选修课程超过5门的学员学号和所属单位?

答：select sn,sd from s where s# in(select s# from sc group by s# having count(distinct c#)>5)

是查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列，完整的查询语句如下：

select top 10 * from A where ID >(select max(ID) from (select top 30 ID from A order by A

) T) order by A

要求是查询表A中存在ID重复三次以上的记录,完整的查询语句如下：

select * from(select count(ID) as count from table group by ID)T where T.count>3

create table testtable1

(

id int IDENTITY,

department varchar(12)

)

select * from testtable1

insert into testtable1 values(‘设计’)

insert into testtable1 values(‘市场’)

insert into testtable1 values(‘售后’)

结果

id department

1 设计

2 市场

3 售后

create table testtable2

(

id int IDENTITY,

dptID int,

name varchar(12)

)

insert into testtable2 values(1,’张三’)

insert into testtable2 values(1,’李四’)

insert into testtable2 values(2,’王五’)

insert into testtable2 values(3,’彭六’)

insert into testtable2 values(4,’陈七’)

用一条SQL语句，怎么显示如下结果

id dptID department name

1 1 设计张三

2 1 设计李四

3 2 市场王五

4 3 售后彭六

5 4 黑人陈七

答案是：

SELECT testtable2.* , ISNULL(department,’黑人’)

FROM testtable1 right join testtable2 on testtable2.dptID = testtable1.ID

在面试应聘的SQL Server数据库开发人员时，我运用了一套标准的基准技术问题。下面这些问题是我觉得能够真正有助于淘汰不合格应聘者的问题。它们按照从易到难的顺序排列。当你问到关于主键和外键的问题时，后面的问题都十分有难度，因为答案可能会更难解释和说明，尤其是在面试的情形下。

你能向我简要叙述一下SQL Server 2000中使用的一些数据库对象吗?

你希望听到的答案包括这样一些对象:表格、视图、用户定义的函数，以及存储过程;如果他们还能够提到像触发器这样的对象就更好了。如果应聘者不能回答这个基本的问题，那么这不是一个好兆头。

NULL是什么意思?

NULL(空)这个值是数据库世界里一个非常难缠的东西，所以有不少应聘者会在这个问题上跌跟头您也不要觉得意外。 NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。假设您的SQL Server数据库里有ANSI_NULLS，当然在默认情况下会有，对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个 UNKNOWN值进行比较，并在逻辑上希望获得一个答案。您必须使用IS NULL操作符。

什么是索引?SQL Server 2000里有什么类型的索引?

任何有经验的数据库开发人员都应该能够很轻易地回答这个问题。一些经验不太多的开发人员能够回答这个问题，但是有些地方会说不清楚。简单地说，索引是一个数据结构，用来快速访问数据库表格或者视图里的数据。在SQL Server里，它们有两种形式:聚集索引和非聚集索引。聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表格的哪个(或哪些)字段，这些字段都会按顺序被保存在表格。由于存在这种排序，所以每个表格只会有一个聚集索引。非聚集索引在索引的叶级有一个行标识符。这个行标识符是一个指向磁盘上数据的指针。它允许每个表格有多个非聚集索引。

什么是主键?什么是外键?

主键是表格里的(一个或多个)字段，只用来定义表格里的行;主键里的值总是唯一的。外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表格(尽管可能是同一个表格)里的一系列相连的字段。那么这些相连的字段就是外键。

什么是触发器?SQL Server 2000有什么不同类型的触发器?

让未来的数据库开发人员知道可用的触发器类型以及如何实现它们是非常有益的。触发器是一种专用类型的存储过程，它被捆绑到SQL Server 2000的表格或者视图上。在SQL Server 2000里，有INSTEAD-OF和AFTER两种触发器。INSTEAD-OF触发器是替代数据操控语言(Data Manipulation

Language，DML)语句对表格执行语句的存储过程。例如，如果我有一个用于TableA的INSTEAD-OF-UPDATE

触发器，同时对这个表格执行一个更新语句，那么INSTEAD-OF-UPDATE触发器里的代码会执行，而不是我执行的更新语句则不会执行操作。

AFTER触发器要在DML语句在数据库里使用之后才执行。这些类型的触发器对于监视发生在数据库表格里的数据变化十分好用。

您如何确一个带有名为Fld1字段的TableB表格里只具有Fld1字段里的那些值，而这些值同时在名为TableA的表格的Fld1字段里?

这个与关系相关的问题有两个可能的答案。第一个答案(而且是您希望听到的答案)是使用外键限制。外键限制用来维护引用的完整性。它被用来确保表格里的字段只保存有已经在不同的(或者相同的)表格里的另一个字段里定义了的值。这个字段就是候选键(通常是另外一个表格的主键)。

另外一种答案是触发器。触发器可以被用来保证以另外一种方式实现与限制相同的作用，但是它非常难设置与维护，而且性能一般都很糟糕。由于这个原因，微软建议开发人员使用外键限制而不是触发器来维护引用的完整性。

对一个投入使用的在线事务处理表格有过多索引需要有什么样的性能考虑?

你正在寻找进行与数据操控有关的应聘人员。对一个表格的索引越多，数据库引擎用来更新、插入或者删除数据所需要的时间就越多，因为在数据操控发生的时候索引也必须要维护。

你可以用什么来确保表格里的字段只接受特定范围里的值?

这个问题可以用多种方式来回答，但是只有一个答案是“好”答案。您希望听到的回答是Check限制，它在数据库表格里被定义，用来限制输入该列的值。

触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。因此，微软建议使用Check限制而不是其他的方式来限制域的完整性。

如果应聘者能够正确地回答这个问题，那么他的机会就非常大了，因为这表明他们具有使用存储过程的经验。

返回参数总是由存储过程返回，它用来表示存储过程是成功还是失败。返回参数总是INT数据类型。

OUTPUT参数明确要求由开发人员来指定，它可以返回其他类型的数据，例如字符型和数值型的值。(可以用作输出参数的数据类型是有一些限制的。)您可以在一个存储过程里使用多个OUTPUT参数，而您只能够使用一个返回参数。

什么是相关子查询?如何使用这些查询?

经验更加丰富的开发人员将能够准确地描述这种类型的查询。相关子查询是一种包含子查询的特殊类型的查询。查询里包含的子查询会真正请求外部查询的值，从而形成一个类似于循环的状况。

什么是SQL注入式攻击？

所谓SQL注入式攻击，就是攻击者把SQL命令插入到Web表单的输入域或页面请求的查询字符串，欺骗服务器执行恶意的SQL命令。在某些表单中，用户输入的内容直接用来构造（或者影响）动态SQL命令，或作为存储过程的输入参数，这类表单特别容易受到SQL注入式攻击。常见的SQL注入式攻击过程类如：

⑴ 某个ASP.NET Web应用有一个登录页面，这个登录页面控制着用户是否有权访问应用，它要求用户输入一个名称和密码。

⑵ 登录页面中输入的内容将直接用来构造动态的SQL命令，或者直接用作存储过程的参数。下面是ASP.NET应用构造查询的一个例子：

System.Text.StringBuilder query = new System.Text.StringBuilder(
   “SELECT * from Users WHERE login = ‘”)
   .Append(txtLogin.Text).Append(“‘ AND password='”)
   .Append(txtPassword.Text).Append(“‘”);

⑶ 攻击者在用户名字和密码输入框中输入”‘或’1’=’1″之类的内容。

⑷ 用户输入的内容提交给服务器之后，服务器运行上面的ASP.NET代码构造出查询用户的SQL命令，但由于攻击者输入的内容非常特殊，所以最后得到的SQL命令变成：SELECT * from Users WHERE login = ” or ‘1’=’1′ AND password = ” or ‘1’=’1’。

⑸ 服务器执行查询或存储过程，将用户输入的身份信息和服务器中保存的身份信息进行对比。

⑹ 由于SQL命令实际上已被注入式攻击修改，已经不能真正验证用户身份，所以系统会错误地授权给攻击者。

如果攻击者知道应用会将表单中输入的内容直接用于验证身份的查询，他就会尝试输入某些特殊的SQL字符串篡改查询改变其原来的功能，欺骗系统授予访问权限。

系统环境不同，攻击者可能造成的损害也不同，这主要由应用访问数据库的安全权限决定。如果用户的帐户具有管理员或其他比较高级的权限，攻击者就可能对数据库的表执行各种他想要做的操作，包括添加、删除或更新数据，甚至可能直接删除表

如何防范SQL注入式攻击？

好在要防止ASP.NET应用被SQL注入式攻击闯入并不是一件特别困难的事情，只要在利用表单输入的内容构造SQL命令之前，把所有输入内容过滤一番就可以了。过滤输入内容可以按多种方式进行。

⑴ 对于动态构造SQL查询的场合，可以使用下面的技术：

第一：替换单引号，即把所有单独出现的单引号改成两个单引号，防止攻击者修改SQL命令的含义。再来看前面的例子，”SELECT * from Users WHERE login = ”’ or ”1”=”1′ AND password = ”’ or ”1”=”1′”显然会得到与”SELECT * from Users WHERE login = ” or ‘1’=’1′ AND password = ” or ‘1’=’1′”不同的结果。

第二：删除用户输入内容中的所有连字符，防止攻击者构造出类如”SELECT * from Users WHERE login = ‘mas’ — AND password =””之类的查询，因为这类查询的后半部分已经被注释掉，不再有效，攻击者只要知道一个合法的用户登录名称，根本不需要知道用户的密码就可以顺利获得访问权限。

第三：对于用来执行查询的数据库帐户，限制其权限。用不同的用户帐户执行查询、插入、更新、删除操作。由于隔离了不同帐户可执行的操作，因而也就防止了原本用于执行SELECT命令的地方却被用于执行INSERT、UPDATE或DELETE命令。

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

⑶ 限制表单或查询字符串输入的长度。如果用户的登录名字最多只有10个字符，那么不要认可表单中输入的10个以上的字符，这将大大增加攻击者在SQL命令中插入有害代码的难度。

⑷ 检查用户输入的合法性，确信输入的内容只包含合法的数据。数据检查应当在客户端和服务器端都执行——之所以要执行服务器端验证，是为了弥补客户端验证机制脆弱的安全性。

在客户端，攻击者完全有可能获得网页的源代码，修改验证合法性的脚本（或者直接删除脚本），然后将非法内容通过修改后的表单提交给服务器。因此，要保证验证操作确实已经执行，唯一的办法就是在服务器端也执行验证。你可以使用许多内建的验证对象，例如 RegularExpressionValidator，它们能够自动生成验证用的客户端脚本，当然你也可以插入服务器端的方法调用。如果找不到现成的验证对象，你可以通过CustomValidator自己创建一个。

⑸ 将用户登录名称、密码等数据加密保存。加密用户输入的数据，然后再将它与数据库中保存的数据比较，这相当于对用户输入的数据进行了”消毒”处理，用户输入的数据不再对数据库有任何特殊的意义，从而也就防止了攻击者注入SQL命令。 System.Web.Security.FormsAuthentication类有一个 HashPasswordForStoringInConfigFile，非常适合于对输入数据进行消毒处理。

⑹ 检查提取数据的查询所返回的记录数量。如果程序只要求返回一个记录，但实际返回的记录却超过一行，那就当作出错处理

五、Sql常见题目

为管理岗位业务培训信息，建立3个表:

S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

C (C#,CN ) C#,CN 分别代表课程编号、课程名称

SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名

–实现代码:

Select SN,SD FROM S Where [S#] IN( Select [S#] FROM C,SC Where C.[C#]=SC.[C#] AND CN=N’税收基础’)

2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位

–实现代码:

Select S.SN,S.SD FROM S,SC Where S.[S#]=SC.[S#] AND SC.[C#]=’C2′

3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位

–实现代码:

Select SN,SD FROM S Where [S#] NOT IN( Select [S#] FROM SC Where [C#]=’C5′)

4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位

–实现代码:

Select SN,SD FROM S Where [S#] IN( Select [S#] FROM SC RIGHT JOIN C ON SC.[C#]=C.[C#] GROUP BY [S#] HAVING COUNT(*)=COUNT([S#]))

5. 查询选修了课程的学员人数

–实现代码:

Select 学员人数=COUNT(DISTINCT [S#]) FROM SC

6. 查询选修课程超过5门的学员学号和所属单位

–实现代码:

Select SN,SD FROM S Where [S#] IN( Select [S#] FROM SC GROUP BY [S#] HAVING COUNT(DISTINCT [C#])>5)

题目2:

问题描述:

S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名

C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师

SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩

1. 找出没有选修过“李明”老师讲授课程的所有学生姓名

–实现代码:

Select SNAME FROM S Where NOT EXISTS( Select * FROM SC,C Where SC.CNO=C.CNO AND CNAME=’李明’ AND SC.SNO=S.SNO)

2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

–实现代码:

Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE) FROM S,SC,( Select SNO FROM SC Where SCGRADE<60 GROUP BY SNO HAVING COUNT(DISTINCT CNO)>=2 )A Where S.SNO=A.SNO AND SC.SNO=A.SNO GROUP BY S.SNO,S.SNAME

3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名

–实现代码:

Select S.SNO,S.SNAME FROM S,( Select SC.SNO FROM SC,C Where SC.CNO=C.CNO AND C.CNAME IN(‘1′,’2′) GROUP BY SNO HAVING COUNT(DISTINCT CNO)=2 )SC Where S.SNO=SC.SNO

4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号

–实现代码:

Select S.SNO,S.SNAME FROM S,( Select SC1.SNO FROM SC SC1,C C1,SC SC2,C C2 Where SC1.CNO=C1.CNO AND C1.NAME=’1′ AND SC2.CNO=C2.CNO AND C2.NAME=’2′ AND SC1.SCGRADE>SC2.SCGRADE )SC Where S.SNO=SC.SNO

5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

–实现代码:

Select S.SNO,S.SNAME,SC.[1号课成绩],SC.[2号课成绩] FROM S,( Select SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE FROM SC SC1,C C1,SC SC2,C C2 Where SC1.CNO=C1.CNO AND C1.NAME=’1′ AND SC2.CNO=C2.CNO AND C2.NAME=’2’ AND SC1.SCGRADE>SC2.SCGRADE )SC Where S.SNO=SC.SNO

求其中同一个号码的两次通话之间间隔大于10秒的通话记录ID

例如：6，7，8，9，10条记录均符合

ID 主叫号码被叫号码通话起始时间通话结束时间通话时长

1 98290000 0215466546656 2007-02-01 09:49:53.000 2007-02-01 09:50:16.000 23

2 98290000 021546654666 2007-02-01 09:50:29.000 2007-02-01 09:50:41.000 12

3 98290000 021546654666 2007-02-01 09:50:58.000 2007-02-01 09:51:12.000 14

4 68290900 0755133329866 2007-02-01 10:04:31.000 2007-02-01 10:07:13.000 162

5 78290000 0755255708638 2007-02-01 10:48:26.000 2007-02-01 10:49:23.000 57

6 78290000 0755821119109 2007-02-01 10:49:39.000 2007-02-01 10:52:55.000 196

7 78290000 035730928370 2007-02-01 11:30:45.000 2007-02-01 11:31:58.000 73

8 78290000 0871138889904 2007-02-01 11:33:47.000 2007-02-01 11:35:00.000 73

9 68290000 035730928379 2007-02-01 11:52:20.000 2007-02-01 11:54:56.000 156

10 68290000 0298521811199 2007-02-01 12:44:45.000 2007-02-01 12:45:04.000 19

答案：

SELECT DISTINCT a.* FROM dbo.hc a left join dbo.hc b

ON a.主叫号码=b.主叫号码

WHERE a.id<>b.id AND (DATEDIFF(second,a.通话起始时间,b.通话结束时间)>10 AND

DATEDIFF(second,b.通话起始时间,a.通话结束时间)>10)

Sql Server关于按周统计的问题

统计Sql Server里一个销售明细表里某个时间段的销售额，而且要按周进行比较，以下是该语句的写法：

select sum(销售金额), datename(week, 销售日期-1) from sales where 销售日期 betwee begindate and enddate group by datename(week, 销售日期-1)

注意：这里之所以要把销售日期-1是因为sql server默认的一周的第一天是星期天，而我们习惯的统计是以星期一到星期天计算的，所以减一。

六、临时表

临时表与永久表相似，但临时表存储在 tempdb 中，当不再使用时会自动删除。

临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。

例如，如果创建了 employees 表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了本地临时表 #employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了 ##employees 全局临时表，则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该表，则 SQL Server 将在您断开连接并且所有其他会话不再使用该表时将其删除

七、视图

视图可以被看成是虚拟表或存储查询。除非是索引视图，否则视图的数据不会作为非重复对象存储在数据库中。数据库中存储的是 SELECT 语句。SELECT 语句的结果集构成视图所返回的虚拟表。用户可以采用引用表时所使用的方法，在 Transact-SQL 语句中引用视图名称来使用此虚拟表

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图在数据库中并不是以数据值存储集形式存在，除非是索引视图。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。分布式查询也可用于定义使用多个异类源数据的视图。例如，如果有多台不同的服务器分别存储您的单位在不同地区的数据，而您需要将这些服务器上结构相似的数据组合起来，这种方式就很有用。

通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少

视图种类：索引视图和索引视图

SQL Server 2005 查询处理器对索引视图和非索引视图将区别对待：

索引视图的行以表的格式存储在数据库中。如果查询优化器决定使用查询计划的索引视图，则索引视图将按照基表的处理方式进行处理。

只有非索引视图的定义才存储，而不存储视图的行。查询优化器将视图定义中的逻辑纳入执行计划，而该执行计划是它为引用非索引视图的 SQL 语句生成的。

SQL Server 查询优化器用于决定何时使用索引视图的逻辑与用于决定何时对表使用索引的逻辑相似。如果索引视图中的数据包括所有或部分 SQL 语句，而且查询优化器确定视图的某个索引是低成本的访问路径，则不论查询中是否引用了该视图的名称，查询优化器都将选择此索引

当 SQL 语句引用非索引视图时，分析器和查询优化器将分析 SQL 语句的源和视图的源，然后将它们解析为单个执行计划。没有单独用于 SQL 语句或视图的计划

创建视图前请考虑如下准则：

只能在当前数据库中创建视图。但是，如果使用分布式查询定义视图，则新视图所引用的表和视图可以存在于其他数据库甚至其他服务器中。

视图名称必须遵循标识符的规则，且对每个架构都必须唯一。此外，该名称不得与该架构包含的任何表的名称相同。

您可以对其他视图创建视图。Microsoft SQL Server 2005 允许嵌套视图。但嵌套不得超过 32 层。根据视图的复杂性及可用内存，视图嵌套的实际限制可能低于该值。

不能将规则或 DEFAULT 定义与视图相关联。

不能将 AFTER 触发器与视图相关联，只有 INSTEAD OF 触发器可以与之相关联。

定义视图的查询不能包含 COMPUTE 子句、COMPUTE BY 子句或 INTO 关键字。

定义视图的查询不能包含 ORDER BY 子句，除非在 SELECT 语句的选择列表中还有一个 TOP 子句。

定义视图的查询不能包含指定查询提示的 OPTION 子句。

定义视图的查询不能包含 TABLESAMPLE 子句。

不能为视图定义全文索引定义。

不能创建临时视图，也不能对临时表创建视图。

不能删除参与到使用 SCHEMABINDING 子句创建的视图中的视图、表或函数，除非该视图已被删除或更改而不再具有架构绑定。另外，如果对参与具有架构绑定的视图的表执行 ALTER TABLE 语句，而这些语句又会影响该视图的定义，则这些语句将会失败。

尽管查询引用一个已配置全文索引的表时，视图定义可以包含全文查询，仍然不能对视图执行全文查询。

下列情况下必须指定视图中每列的名称：

视图中的任何列都是从算术表达式、内置函数或常量派生而来。

视图中有两列或多列原应具有相同名称（通常由于视图定义包含联接，因此来自两个或多个不同表的列具有相同的名称）。

希望为视图中的列指定一个与其源列不同的名称。（也可以在视图中重命名列。）无论重命名与否，视图列都会继承其源列的数据类型。

若要创建视图，必须获得数据库所有者授予您创建视图的权限，并且如果使用架构绑定创建视图，您必须对视图定义中所引用的表或视图具有适当权限

CREATE VIEW [ schema_name . ] view_name [ (column [ ,…n ] ) ]

[ WITH <view_attribute> [ ,…n ] ]

AS select_statement [ ; ]

[ WITH CHECK OPTION ]

<view_attribute> ::=

{

[ ENCRYPTION ]

[ SCHEMABINDING ]

[ VIEW_METADATA ] }

运维必会的MySQL企业面试题及答案（开发也可以看看）

面试题1 ：MySQL集群和高可用方案有哪些，再生产场景你都用过哪些方案？
常用mysql集群可以使用双主一从，一主多从；高可用方案：MMM、MHA、mysql+ha+drbd 读写分离：MySQL+proxy 、Amoeba

生产中我使用的是双主3从，其中从库两台对外提供读服务，一台对内给开发或者DBA提供服务

面试题2：MySQL数据库如何实现读写分离，你的公司是怎么实现的（请答出200服务器以上规模企业数据库的解决方案）

我们公司使用的是mysql+proxy进行读写分离。在生产中，可以有这样两种方法

1.主库：web oldboy123 10.0.0.% 3306 (slect,insert,delete,update)，主库忽略mysql同步授权【binlog-ignore-db=mysql replicate-ignore-db=mysql】

从库：主库的web用户同步到从库，然后回收权限insert,delete,update,下面具体是回收权限方法

mysql> REVOKE insert,update,delete ON oldboy.* FROM ‘web1’@’10.0.0.%’;

Query OK, 0 rows affected (0.00 sec)mysql> show grants for web@’10.0.0.%’;+————————————————————————————————————+| Grants for web1@10.0.0.% |+————————————————————————————————————+| GRANT USAGE ON *.* TO ‘web1’@’10.0.0.%’ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257’ || GRANT SELECT ON oldboy.* TO ‘web1’@’10.0.0.%’ |+————————————————————————————————————+2 rows in set (0.00 sec)

2.主库：web oldboy123 10.0.0.% 3306 (select,insert,delete,update)

从库：web oldboy123 10.0.0.% 3306 (select)

设置 read-only确保从库为只读

面试题3：开发有一堆数据插入，如何防止插入的中文数据产生乱码？

1.询问开发应用程序支持什么字符集 2.将数据转换为相应应用程序字符集保存 3.设置服务器端字符集(修改配置文件my.cnf，使用命令 set names utf-8)

面试题4：如何批量更改数据库表的引擎，例如：myisam改成innodb。

方法一：

1.通过mysqldump -uroot -poldboy -S /data/3306/mysql.sock -x -B oldboy > /opt/oldboy.sql

2.使用sed替换，sed -i ‘s/myisam/innodb/g’ /opt/oldboy.sql

3.mysql -uroot -poldboy -S /data/3306/mysql.sock </opt/oldboy.sql

方法二:

1.登录数据库，执行下面的脚本

mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e “alter table oldboy.test engine=MyISAm;”

ALTER TABLE oldboy ENGINE = INNODB;

#!/bin/sh

myuser=root

mypasswd=lixiang123

socket=/tmp/mysql.sock

mycmd=”mysql -u$myuser -p$mypasswd -S $socket”

mydump=”mysqldump -u$myuser -p$mypasswd -S $socket”

for database in $mycmd -e "show databases;"|sed '1,2d'|egrep -v "mysql|performance_schema"

for tables in mydump -e "show tables from $databses;"|sed '1d'

$mycmd “alter table $database.$tables engine = innodb;”

done

面试题5：如何批量更改数据库字符集，例如;GBK更改为UTF8。

方法一：可以通过mysqldump命令备份出一个sql文件，再使用sed命令替换sed -i ‘s/GBK/UTF8/g’

方法二：通过shell脚本循环，过滤出数据库结合 alter database test set character utf8

mysql> help alter database

Name: ‘ALTER DATABASE’

Description:

Syntax:

ALTER {DATABASE | SCHEMA} [db_name]

alter_specification …

ALTER {DATABASE | SCHEMA} db_name

UPGRADE DATA DIRECTORY NAME

alter_specification:

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name

面试题6 ：你的数据库是如何备份的（请答出200服务器以上规模企业数据库备份的解决方案）

我公司的服务器采用的一主多从的方案，然后单独拿出一台服务器作为数据备份，同时采用inotify监控软件实时将从库产生的mysql-bin.******文件同步到本地。如果采用更安全的方法可以使用xtrabackup工具进行物理备份，将数据备份到备份服务器

面试题7：key_buffer_size参数作用，如何修改这个参数不重启数据库就可以生效？

key_buffer_size是数据库索引缓存

set global key_buffer_size = 26777216;不过这个只是临时生效，想要彻底生效可以在配置文件中修改

面试题8：delete from test和truncate table test区别？

delete from test是逻辑删除

truncate table test 是物理删除

面试题9：你维护的企业里MySQL服务架构能说下么？

我们企业采用的是一主三从，当主挂掉时能够自动切换到从上面(这个采用的是MMM 、keepalived实现的)，读写分离Amoba

面试题10：网站打开慢，请给出排查方法，如果是因为数据库慢导致，如何排查并解决，请分析并举例？

网站打开慢的具体分析方法为：使用top，vmstat，free等命令帮助分析系统性能瓶颈

如果发现网站打开慢是由数据库造成的，那么可以采用下面的方法进行分析：1.修改配置文件#log-slow-queries = /data/3307/slow.log，开启mysql满日志查询，人为观察或是使用第三方工具mysqlsla分析慢查询最多的语句 2.通过explain 分析慢查询语句 3.如果上述得出是结果为下面内容表示改语句并未走索引，而系统给出的可以创建的索引是primary(主键)，但是具体创建索引可以根据具体条件判断，即 where后面跟随的语句

possible_keys: PRIMARY

key: NULL

面试题11：什么是数据库的事务，事务有哪些特性？

数据库事务是指逻辑上的一组sql语句，组成这组操作的各个sql语句，执行时要么成功要么失败

数据库事务：具有原子性、隔离性、持久性、一致性

面试题12：MySQL有哪些常用引擎？企业中如何批量更改表的引擎？

企业中常用的引擎有：MyISAM InnoDB Cluster Memory

mysql> show engines;

+——————–+———+—————————————————————-+————–+——+————+

+——————–+———+—————————————————————-+————–+——+————+

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

+——————–+———+—————————————————————-+————–+——+————+

8 rows in set (0.00 sec)

面试题13： MyISAM与Innodb数据库引擎有什么特点与区别，企业中如何选择？

MyISAM引擎：1.不支持事务 2.表级锁定 3.读写互相阻塞，但是读的时候并不阻塞另外的读 4.只会缓存索引 5.读取速度快，占用服务器资源相对较少 6.不支持外键约束

Innodb引擎：1.支持事务 2.支持行级锁定 3.读写阻塞与事务隔离级别相关 4.具有非常高的缓存性能 5. 5.5版本以后已经支持全文索引 6支持外键 7.占用资源比较多

InnoDB引擎在企业使用场景

1.需要事务 2.行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成 3.数据读写及更新较为频繁的场景，如微信微博 SNS BBS等 4.数据一致性要求较高的业务，如银行 5.硬件设备内存较大，可以利用InnoDB较好的缓存能力来提供内存的利用率，尽可能减少磁盘IO 6.相比MyISAM引擎，InnoDB引擎更消耗资源，速度没有MyISAM引擎快

MyISAM引擎企业中使用场景

1.不支持事务 2.读数据比较多的应用 3.硬件资源较差的机器 4.对数据的一致性要求不高的业务 5.采用主从复制，从库可以使用MyISAM 6.数据修改相对较少的业务

面试题14：MySQL binlog的工作模式有哪些？各有什么特点，企业中如何选择？

1.Row(行模式)；

将一条语句变成多条语句，例如：删除一个表delete from test，里面有五条数据它会生成3条语句，delete 张三 from test where id=1；delete 李四 from test where id=2；delete 王五 from test where id=3；

2.Statement(语句模式)

每一条修改的数据都会完整的记录到主库master的binlog里面，在slave上完整执行在master执行的sql语句；例如：主库操作delete from test;那么在从可也会执行delete from test;

3.mixed(混合模式)

结合前面的两种模式，如果在工作中有使用函数或者触发器等特殊功能需求的时候，使用混合模式

例如当输出的数据达到100万条的时候，它就会选择 statement模式，而不会选择Row Level行模式

面试题15：如何分表分库备份及批量恢复（口述脚本实现过程）？

#!/bin/sh

myuser=root

mypasswd=lixiang123

socket=/tmp/mysql.sock

mycmd=”mysql -u$myuser -p$mypasswd -S $socket”

mydump=”mysqldump -u$myuser -p$mypasswd -S $socket”

for database in $mycmd -e "show databases;"|sed '1,2d'|egrep -v "mysql|performance_schema"

for tables in mydump -e "show tables from $databses;"|sed '1d'

$mydump $database $tables|gzip >/data/backup/${database}/${databse}_${tables}_$(date +%F)_sql.gz

done

面试题16：企业中MySQLDUMP备份时使用了-A -B参数，请问此时如何进行单表单库恢复？

寻找另外一台mysql备份机器，将先前mysqldump产生的sql文件导入；在按照面试题033的方法进行分表分库备份，将产生的结果再次导入线上服务器

面试题17：生产场景不停不锁主库，不夜里操作，如何白天从容批量创建从库？

mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B -F –master-data=1 -x >/3306.sql

从库在change master to master_host = ‘10.0.0.101’, master_port=3306, master_user=’rep’, master_password=’oldboy123′;

start slave;

面试题18：生产场景，一主多从环境，从库宕机，请问你如何恢复？

从库宕机不太影响主机业务，然后查看从库是服务上的宕机还是服务器宕机，如果是应用程序的问题，查看从库的具体状态再另外分析

面试题19：生产场景，一主多从环境，主库宕机，请问你如何恢复，注意要说完整过程。

当1主多从时，留一台从库专门用来做数据备份

如果主库能够ssh连接上，mysql_bin.0000**文件没有丢失，将其补全到所有从库

选定一个从库提升为主库，开启binlog并将其他所有从库change master to

面试题20：解释下有关数据库的ACID是什么意思？

原子性：一个事务(transaction)中的所有操作，要么成功要么全部失败，并不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精准度、串联性以及后续数据库可以自发性的完成预设工作

隔离性：当两个或多个事务并发访问(此处访问指的是查询和修改操作)数据库的同一数据时所表现出来的相互关系。事务隔离分为不同级别，包括读未提交(read uncommitted)、读提交（read committed）、可重复读（repeated read）和串行化(Serializable)

持久化：在事务完成以后，该事务对数据所做的更改便持久地保存在数据库之中，并且是完全的

面试题21：企业生产MySQL 如何优化？

1.选择好的硬件，做raid10

2.开启慢查询，做索引

3.设置sleep超时时间

4.开启mysql慢查询日志，通过mysqlsla分析日志定时发送给开发，做sql语句的优化

面试题22：企业生产MySQL 如何设计备份方案？你是怎么做的？

我们企业采用的双主三从，实际是双主单写，两台主之间做keepalived高可用，另外两台从库实现读写分离，还有一台从库用来做备份用

面试题23：企业中MySQL root密码忘了怎么解决，多实例密码忘了又如何解决？

首先停止mysql，kill mysqld

mysqld_safe –defaults-file=/data/3306/my.cnf –skip-grant-table &

mysql -uroot -S /data/3306/mysql.sock

update mysql.user set password=password(“123456”) where user=’root’ and host=’localhost’;

面试题24：什么是MySQL引擎？ MyISAM与Innodb数据库引擎特点与区别？
mysql数据库引擎是表的一种存储机制，不同的存储引擎可能在存取空间大小，存储性能以及引擎功能存在不同。常见的数据库引擎有MyISAM，Innodb，MEMORY， Cluster(集群)

MyISAM：

1.不支持事务 2.表级锁定 3.读写互相阻塞 4.只会缓存索引 5.读取速度较快，占用资源较少 6.不支持外键约束，但是支持全文索引

InnoDB：

1.支持事务 2.行级锁定(5.6版本以后支持全文索引) 3.读写阻塞与事务隔离级别相关 4.高速缓存(DATA caches) 5.占用系统资源较多 6.支持外键约束，支持全文索引

面试题25：如何配置描述MySQL多实例？

面试题26：工作中数据库被误操作执行了一个删除的SQL语句，你如何完整恢复丢失的数据？

1.首先得刷新mysql-binlong文件，查看具体mysql-bin.*****文件

mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -F –master-data=2 -B oldboy > /opt/oldboy.sql

2.得到出问题时候具体的文件mysql-bin.000006

mysqlbinlog -d oldboy mysql-bin.000006 >/bin.sql (在mysql_bin文件导出oldboy库的sql语句到bin.sql文件中)

3.查看oldboy.sql文件，过滤出刚刚误操作的语句删除掉，再次导入sql文件即可

mysql -urtoo -poldboy -S /data/3306/mysql.sock -B oldboy </opt/oldboy.sql

[如果忘记之前执行了什么语句，那么就定位到那个时候的时间，然后再次按照上述操作执行，尽量减少数据的丢失]

在恢复数据的时候尽量停止掉mysql，不然一边恢复数据又一边写入数据【如果可以容忍一段时间的数据丢失，那么可以不关闭数据库

【备注：全量备份到出问题只产生了一个 mysql-bin.000021 文件，可是使用下面方法恢复，如果产生多个文件可以使用追加的方法】

面试题27：MySQL出现同步延迟有哪些原因？如何解决？
1.从库太多

2.从库的硬件性能差会导致同步延迟
3.慢sql语句过多

4.主从复制的设计问题(例如主从采用单线程，而主库并发太大，来不及传入从库)

5.网络问题

面试题28：详细MySQL主从同步的原理及配置主从的完整步骤。
主库开启bin_log，这时主库就会产生mysql_bin.000***文件和IO线程，如果从库想要连接主库，必须在主库上面进行授权grant replication on *.* to rep@10.0.0.% indentified by ‘123456’;这个时候在从库要change master to ……到主库，与主库建立IO线程的连接同时在从库要开启同步开关start slave，此时从库就会产生一份really_log和sql线程，sql线程负责把数据写入磁盘

面试题29：MySQL如何实现双向互为主从复制A<==>B?

主库从库都需要开启bin_log日志文件

主库：#________m-m m1 start________

auto_increment_increment = 2 #自增ID的间隔
auto_increment_offset = 1 #ID的初始位置
log-slave-updates = 1
log-bin = /data/3306/mysql-bin
expire_logs_days = 7
#________m-m m1 end________

从库：

#________m-m m1 start________

auto_increment_increment = 2 #自增ID的间隔
auto_increment_offset = 2 #ID的初始位置
log-slave-updates = 1
log-bin = /data/3306/mysql-bin
expire_logs_days = 7
#________m-m m1 end________

面试题30：MySQL如何实现级联同步A–>B–>C？
从库B开启bin_log日志

在配置文件my.cnf下面模块添加如下内容：

log-bin = /data/3307/mysql-bin

log-slave-updates = 1

expire_logs_days = 7

面试题31：MySQL主从复制故障如何解决？如何监控主从复制是否故障?
在从库使用show slave status\G;查看从库的IO线程和SQL线程是否为yes，如果遇上具体问题具体分析。一般会出现创建库的问题，语句的问题。我们只需要在修改从库配置文件my.cnf

slave-skip-errors = 1032,1062,1005,1007

查看从库Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0，这三个状态是否正常，想要查看主从同步到底延迟多少，可以在主库插入时间戳

时间戳设置：

主库：

CREATE TABLE test (‘id’ INT (3) UNSIGNED AUTO_INCREMENT, ‘date1’ TIMESTAMP (8) PRIMARY KEY(‘id’));

insert into test values(1,null);

mysql> select * from test;

+—-+———————+

| id | date1 |

+—-+———————+

| 2 | 2015-07-30 05:04:02 |

+—-+———————+

1 row in set (0.00 sec)

从库

mysql> select * from test;

+—-+———————+

| id | date1 |

+—-+———————+

| 1 | 2015-07-30 04:59:22 |

| 2 | 2015-07-30 05:04:02 |

+—-+———————+

2 rows in set (0.00 sec)

可以看到从库id=2的记录与主库基本无差异

面试题32：MySQL Sleep线程过多如何解决？

在配置文件修改超时时间

[mysqld]
interactive_timeout = 120 ；此参数设置后wait_timeout自动生效。
wait_timeout = 120

php程序不要使用长连接；java程序调整连接池

打开mysql慢查询

面试题33：如何调整已有MySQL数据库的字符集，例如：从UTF8改成GBK，请给出完整步骤。

面试题34：请描述MySQL里中文数据乱码的背后原理，以及工作中如何防止数据库数据乱码？

1.linux客户端字符集 UTF8(应用程序) 2.linux服务器端字符集 LANG=”zh_CN.UTF-8″ 3.数据库客户端字符集 UTF8

面试题35：MySQL库授权oldboy用户管理bbs库的所有表，10.0.0.0整个网段访问，密码是123456，请给出详细命令(不低于2种方法)?

方法一：

1	grant all on bbs.* to oldboy@10.0.0.% identified by ‘123456’;

方法二:

登录mysql，可以用>help create user;查看帮助

CREATE USER ‘jeffrey’@’localhost’ IDENTIFIED BY PASSWORD ‘*90E462C37378CED12064BB3388827D2BA3A9B689’;

1 2	create user ‘oldboy’@’10.0.0.%’ identified by password ‘123456’; grant all on bbs.* to ‘oldboy’@’10.0.0%’;

使用Retrofit + RxJava 实现优雅的处理服务器返回异常、错误

异常&错误

实际开发经常有这种情况，比如登录请求，接口返回的

信息包括请求返回的状态：失败还是成功，错误码，User对象等等。如果网络等原因引起的登录失败可以归结为异常，如果是用户信息输入错误导致的登录失败算是错误。

假如服务器返回的是统一数据格式：

/**

* 标准数据格式

* @param <T>

public class Response<T> {

public int state;

public String message;

public T data;

}

网络异常导致的登录失败，在使用Retrofit+RxJava请求时都会直接调用subscribe的onError事件；

密码错误导致的登录失败，在使用Retrofit+RxJava请求时都会调用subscribe的onNext事件；

无论是异常还是错误，都要在subscribe里面处理异常信息，如下代码：

APIWrapper.getInstance().login(“username”, “password”)

.subscribe(new Observer<Response<User>>() {

@Override

public void onCompleted() {

}

@Override

public void onError(Throwable e) {

}

@Override

public void onNext(Response<User> data) {

if(data.state == 1001){

//…..

}else if(data.state == 1002){

}

});

现在我希望在发生任何错误的情况下，都会调用onError事件，并且由model来处理错误信息。那么，此时我们就应该有一个ExceptionEngine来处理事件流中的错误信息了。

在工作流中处理异常

在正常情况下，我们获取网络数据的流程通常如下：

请求接口->解析数据->更新ＵＩ

整个数据请求过程都是发生在Rx中的工作流之中。当有异常产生的时候，我们要尽量不在ui层里面进行判断，换句话说，我们没有必要去告诉ui层具体的错误信息，只需要让他弹出一个信息（Toast或者Dialog）展示我们给它的信息就行。

请求接口和数据解析都可能出错，所以在这两层进行错误处理。为了更好的解耦，我们通过拦截器拦截错误，然后根据错误类型分发信息。

拦截器

数据解析层的拦截器

这个拦截器主要是为了获取具体的错误信息，分发给上层的UI，给用户以提示，增强用户体验。

public Observable<Weather> getWeather(String cityName){

return weatherService.getWeather(cityName)

//拦截服务器返回的错误

.map(new ServerResponseFunc<Weather>())

//HttpResultFunc（）为拦截onError事件的拦截器，后面会讲到，这里先忽略

.onErrorResumeNext(new HttpResponseFunc<Weather>());

}

//拦截固定格式的公共数据类型Response<T>,判断里面的状态码

private class ServerResponseFunc<T> implements Func1<Response<T>, T> {

@Override

public T call(Response<T> reponse) {

//对返回码进行判断，如果不是0，则证明服务器端返回错误信息了，便根据跟服务器约定好的错误码去解析异常

if (reponse.state != 0) {

//如果服务器端有错误信息返回，那么抛出异常，让下面的方法去捕获异常做统一处理

throw new ServerException(reponse.state,reponse.message);

}

//服务器请求数据成功，返回里面的数据实体

return reponse.data;

}

所以整个逻辑是这样的：

所以在前三步的过程中，只要发生异常(服务器返回的错误也抛出了)都会抛出，这时候就触发了RxJava的OnError事件。

处理onError事件的拦截器

这个拦截器主要是将异常信息转化为用户”能看懂”的友好提示。

private class HttpResponseFunc<T> implements Func1<Throwable, Observable<T>> {

@Override

public Observable<T> call(Throwable throwable) {

//ExceptionEngine为处理异常的驱动器

return Observable.error(ExceptionEngine.handleException(throwable));

}

两个拦截器以前使用，代码如下：

public Observable<Weather> getWeather(String cityName){

return weatherService.getWeather(cityName)

//拦截服务器返回的错误

.map(new ServerResponseFunc<Weather>())

//HttpResponseFunc（）为拦截onError事件的拦截器

.onErrorResumeNext(new HttpResponseFunc<Weather>());

}

调用：

APIWrapper.getInstance().getWeather(“北京”)

.subscribe(new SampleProgressObserver<Weather>(MainActivity.this) {

@Override

public void onNext(WeatherBean weatherBean) {

tv.setText(weatherBean.toString());

}

});

MySQL初级考试试面试题及答案

一、单项选择题

1．以下聚合函数求最大值的是( )

A．MAX

B．IF

C．CASE

D．AVG

答案：A

2．SQL语句中的条件用以下哪一项来表达( )

A．THEN

B．WHILE

C．WHERE

D．IF

答案：C

3．在视图上不能完成的操作是( )

A．查询

B．在视图上定义新的视图

C．更新视图

D．在视图上定义新的表

答案：D

4．可以用( )来声明游标

A．CREATE TABLE

B．ALTER CURSOR

C．SET GLOBAL

D．DECLARE CURSOR

答案：D

5．使用CREATE TABLE语句的( )子句，在创建基本表时可以启用全文本搜索

A．FULLTEXT

B．ENGINE

C．FROM

D．WHRER

答案：A

6．以下能够删除一列的是( )

A．alter table emp remove addcolumn

B．alter table emp drop column addcolumn

C．alter table emp delete column addcolumn

D．alter table emp delete addcolumn

答案：B

7．若要撤销数据库中已经存在的表S，可用（）。

A．DELETE TABLE S

B．DELETE S

C．DROP S

D．DROP TABLE S

答案：D

8．查找表结构用以下哪一项( )

A．FIND

B．SELETE

C．ALTER

D．DESC

答案：D

9．要得到最后一句SELECT查询到的总行数，可以使用的函数是( )

A．FOUND_ROWS

B．LAST_ROWS

C．ROW_COUNT

D．LAST_INSERT_ID

答案：A

10．SELECT语句的完整语法较复杂，但至少包括的部分是( )

A．仅SELECT

B．SELECT，FROM

C．SELECT，GROUP

D．SELECT，INTO

答案：B

11．UNIQUE惟一索引的作用是( )

A．保证各行在该索引上的值都不得重复

B．保证各行在该索引上的值不得为NULL

C．保证参加惟一索引的各列，不得再参加其他的索引

D．保证惟一索引不能被删除

答案：A

12．用于将事务处理写到数据库的命令是( )

A．insert

B．rollback

C．commit

D．savepoint

答案：C

13．查找条件为：姓名不是NULL的记录( )

A．WHERE NAME ! NULL

B．WHERE NAME NOT NULL

C．WHERE NAME IS NOT NULL

D．WHERE NAME!=NULL

答案：C

14．主键的建立有( )种方法

A．一

B．四

C．二

D．三

答案：D

15．在视图上不能完成的操作是( )

A．更新视图数据

B．在视图上定义新的基本表

C．在视图上定义新的视图

D．查询

答案：B

16．在SQL语言中，子查询是（）。

A．选取单表中字段子集的查询语句

B．选取多表中字段子集的查询语句

C．返回单表中数据子集的查询语言

D．嵌入到另一个查询语句之中的查询语句

答案：D

17．向数据表中插入一条记录用以下哪一项( )

A．CREATE

B．INSERT

C．SAVE

D．UPDATE

答案：B

18．在select语句的where子句中，使用正则表达式过滤数据的关键字是( )

A．like

B．against

C．match

D．regexp

答案：D

19．SQL语言的数据操纵语句包括SELECT、INSERT、UPDATE、DELETE等。其中最重要的，也是使用最频繁的语句是( )。

A．UPDATE

B．SELECT

C．DELETE

D．INSERT

答案：B

20．以下哪种操作能够实现实体完整性( )

A．设置唯一键

B．设置外键

C．减少数据冗余

D．设置主键

答案：B

21．SQL语言中，删除一个视图的命令是( )

A．REMOVE

B．CLEAR

C．DELETE

D．DROP

答案：D

22．修改数据库表结构用以下哪一项( )

A．UPDATE

B．CREATE

C．UPDATED

D．ALTER

答案：D

23．在全文本搜索的函数中，用于指定被搜索的列的是( )

A．MATCH()

B．AGAINST()

C．FULLTEXT()

D．REGEXP()

答案：A

24．以下语句错误的是( )

A．select sal+1 from emp;

B．select sal*10,sal*deptno from emp;

C．不能使用运算符号

D．select sal*10,deptno*10 from emp;

答案：C

25．下列( )不属于连接种类

A．左外连接

B．内连接

C．中间连接

D．交叉连接

答案：C

26．若用如下的SQL语句创建了一个表SC：( )

CREATE TABLE SC （S# CHAR（6） NOT NULL，C# CHAR（3） NOT NULL，SCORE INTEGER，NOTE CHAR（20））；向SC表插入如下行时，（）行可以被插入。

A．（NULL，’103’，80，’选修’）

B．（’200823’，’101’，NULL，NULL）

C．（’201132’，NULL，86，’ ’）

D．（’201009’，’111’，60，必修）

答案：B

27．删除用户账号命令是( )

A．DROP USER

B．DROP TABLE USER

C．DELETE USER

D．DELETE FROM USER

答案：A

28．以下语句错误的是( )

A． alter table emp delete column addcolumn;

B． alter table emp modify column addcolumn char(10);

C．alter table emp change addcolumn addcolumn int;

D． alter table emp add column addcolumn int;

答案：A

29．组合多条SQL查询语句形成组合查询的操作符是( )

A．SELECT

B．ALL

C．LINK

D．UNION

答案：D

30．创建数据库使用以下哪项( )

A．create mytest

B．create table mytest

C．database mytest

D．create database mytest

答案：D

31．以下哪项用来分组( )

A．ORDER BY

B．ORDERED BY

C．GROUP BY

D．GROUPED BY

答案：D

32．SQL是一种( )语言。

A．函数型

B．高级算法

C．关系数据库

D．人工智能

答案：C

33．删除数据表用以下哪一项( )

A．DROP

B．UPDATE

C．DELETE

D．DELETED

答案：A

34．若要在基本表S中增加一列CN（课程名），可用( )

A．ADD TABLE S ALTER（CN CHAR（８））

B．ALTER TABLE S ADD（CN CHAR（８））

C．ADD TABLE S（CN CHAR（８））

D．ALTER TABLE S （ADD CN CHAR（８））

答案：B

35．下列的SQL语句中，( )不是数据定义语句。

A．CREATE TABLE

B．GRANT

C．CREATE VIEW

D． DROP VIEW

答案：B

36．以下删除记录正确的( )

A．delete from emp where name=’dony’;

B．Delete * from emp where name=’dony’;

C．Drop from emp where name=’dony’;

D．Drop * from emp where name=’dony’;

答案：A

37．删除经销商1018的数据记录的代码为( ) from distributors where distri_num=1018

A．drop table

B．delete *

C．drop column

D．delete

答案：D

38．按照姓名降序排列( )

A．ORDER BY DESC NAME

B．ORDER BY NAME DESC

C．ORDER BY NAME ASC

D．ORDER BY ASC NAME

答案：B

39．可以在创建表时用( )来创建唯一索引，也可以用( )来创建唯一索引

A．Create table，Create index

B．设置主键约束，设置唯一约束

C．设置主键约束，Create index

D．以上都可以

答案：C

40．在SELECT语句中，使用关键字( )可以把重复行屏蔽

A．TOP

B．ALL

C．UNION

D．DISTINCT

答案：D

41．以下聚合函数求平均数的是( )

A．COUNT

B．MAX

C．AVG

D．SUM

答案：C

42．返回当前日期的函数是( )

A．curtime()

B．adddate()

C．curnow()

D．curdate()

答案：D

43．用来插入数据的命令是( )，用于更新的命令是( )

A．INSERT，UPDATE

B．CREATE，INSERT INTO

C．DELETE，UPDATE

D．UPDATE,INSERT

答案：A

44．SELECT COUNT(SAL) FROM EMP GROUP BY DEPTNO;意思是( )

A．求每个部门中的工资

B．求每个部门中工资的大小

C．求每个部门中工资的综合

D．求每个部门中工资的个数

答案：D

45．以下表达降序排序的是( )

A．ASC

B．ESC

C．DESC

D．DSC

答案：C

46．以下哪项不属于数据模型( )

A．关系模型

B．网状模型

C．层次模型

D．网络模型

答案：A

47．有三个表，它们的记录行数分别是10行、2行和6行，三个表进行交叉连接后，结果集中共有( )行数据

A．18

B．26

C．不确定

D．120

答案：D

48．从GROUP BY分组的结果集中再次用条件表达式进行筛选的子句是( )

A．FROM

B．ORDER BY

C．HAVING

D．WHERE

答案：C

49．为数据表创建索引的目的是( )

A．提高查询的检索性能

B．归类

C．创建唯一索引

D．创建主键

答案：A

50．如果要回滚一个事务，则要使用( )语句。

A．commit transaction

B． begin transaction

C． revoke 

D．rollback transaction

答案：D

51．查找数据表中的记录用以下哪一项( )

A．ALTRE

B．UPDATE

C．SELECT

D．DELETE

答案：C

52．在MySQL中，建立数据库用( )

A．CREATE TABLE命令

B．CREATE TRIGGER命令

C．CREATE INDEX命令

D．CREATE DATABASE命令

答案：D

53．MySQL中，预设的、拥有最高权限超级用户的用户名为( )

A．test

B．Administrator

C．DA

D．root

答案：D

54．以下插入记录正确的( )

A．insert into emp(ename,hiredate,sal) values (value1,value2,value3);

B．insert into emp (ename,sal)values(value1,value2,value3);

C．insert into emp (ename)values(value1,value2,value3);

D．insert into emp (ename,hiredate,sal)values(value1,value2);

答案：A

55．在SQL语言中的视图VIEW是数据库的( )

A．外模式

B．存储模式

C．模式

D．内模式

答案：A

56．以下哪项用来排序( )

A．ORDERED BY

B．ORDER BY

C．GROUP BY

D．GROUPED BY

答案：B

57．以下聚合函数求个数的是( )

A．AVG

B．SUM

C．MAX

D．COUNT

答案：D

58．在select语句中，实现选择操作的子句是( )

A．select

B．group by

C．where

D．from

答案：C

59．查找数据库中所有的数据表用以下哪一项( )

A．SHOW DATABASE

B．SHOW TABLES

C．SHOW DATABASES

D．SHOW TABLE

答案：B

60．触发器不是响应以下哪一语句而自动执行的Mysql语句

A．select

B．insert

C．delete

D．update

答案：A

61．( )表示一个新的事务处理块的开始

A．START TRANSACTION

B．BEGIN TRANSACTION

C．BEGIN COMMIT

D．START COMMIT

答案：A

62．以下语句不正确的是( )

A．select * from emp;

B．select ename,hiredate,sal from emp;

C．select * from emp order deptno;

D．select * from where deptno=1 and sal<300;

答案：C

63．delete from employee语句的作用是( )

A．删除当前数据库中整个employee表，包括表结构

B．删除当前数据库中employee表内的所有行

C．由于没有where子句，因此不删除任何数据

D．删除当前数据库中employee表内的当前行

答案：B

64．按照班级进行分组( )

A．ORDER BY CLASSES

B．DORDER CLASSES

C．GROUP BY CLASSES

D．GROUP CLASSES

答案：C

65．格式化日期的函数是( )

A．DATEDIFF()

B．DATE_FORMAT()

C．DAY()

D．CURDATE()

答案：B

66．例如数据库中有A表，包括学生，学科，成绩，序号四个字段 , 数据库结构为

学生学科成绩序号

张三语文 60 1

张三数学 100 2

李四语文 70 3

李四数学 80 4

李四英语 80 5

上述哪一列可作为主键列( )

A．序号

B．成绩

C．学科

D．学生

答案：A

67．学生关系模式 S（ S＃，Sname，Sex，Age），S的属性分别表示学生的学号、姓名、性别、年龄。要在表S中删除一个属性“年龄”，可选用的SQL语句是（）。

A． UPDATE S Age

B．DELETE Age from S

C．ALTER TABLE S ‘Age’

D． ALTER TABLE S DROP Age

答案：D

68．以下哪项用于左连接( )

A．JOIN

B．RIGHT JOIN

C．LEFT JOIN

D．INNER JOIN

答案：C

69．一张表的主键个数为( )

A．至多3个

B．没有限制

C．至多1个

D．至多2个

答案：C

70．SQL语言是( )的语言，轻易学习。

A．导航式

B．过程化

C．格式化

D．非过程化

答案：D

71．在正则表达式中，匹配任意一个字符的符号是( )

A．.

B．*

C．?

D．-

答案：A

72．条件“BETWEEN 20 AND 30”表示年龄在20到30之间，且( )

A．包括20岁不包括30岁

B．不包括20岁包括30岁

C．不包括20岁和30岁

D．包括20岁和30岁

答案：D

73．以下表示可变长度字符串的数据类型是( )

A．TEXT

B．CHAR

C．VARCHAR

D．EMUM

答案：C

74．以下说法错误的是( )

A．SELECT max(sal),deptno,job FROM EMP group by sal;

B．SELECT max(sal),deptno,job FROM EMP group by deptno;

C．SELECT max(sal),deptno,job FROM EMP;

D．SELECT max(sal),deptno,job FROM EMP group by job;

答案：C

75．以下匹配’1 ton’和’2 ton’及’3 ton’的正则表达式是( )

A．’123 ton’

B．’1,2,3 ton’

C．'[123] ton’

D．’1|2|3 ton’

答案：C

76．拼接字段的函数是( )

A．SUBSTRING()

B．TRIM()

C．SUM（）

D．CONCAT()

答案：D

77．以下删除表正确的( )

A．Delete * from emp

B．Drop database emp

C．Drop * from emp

D．delete database emp

答案：B

78．下列说法错误的是( )

A．GROUP BY 子句用来分组 WHERE 子句的输出

B．WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。

C．聚合函数需要和group by 一起使用

D．HAVING 子句用来从FROM的结果中筛选行

答案：D

79．条件年龄BETWEEN 15 AND 35表示年龄在15至35之间，且( )

A．不包括15岁和35岁

B．包括15岁但不包括35岁

C．包括15岁和35岁

D．包括35岁但不包括15岁

答案：C

80．创建视图的命令是( )

A．alter view

B．alter table

C．create table

D．create view

答案：D

81．存储过程是一组预先定义并( )的Transact-SQL语句

A．保存

B．编写

C．编译

D．解释

答案：C

82．返回字符串长度的函数是( )

A．len()

B．length()

C．left()

D．long()

答案：B

83．从数据表中查找记录用以下哪一项( )

A．UPDATE

B．FIND

C．SELECT

D．CREATE

答案：C

84．SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，其中，CREATE、DROP、ALTER语句是实现哪种功能( )

A．数据操纵

B．数据控制

C．数据定义

D．数据查询

答案：C

85．以下哪项不属于DML操作( )

A．insert

B．update

C．delete

D．create

答案：D

86．按照姓名升序序排列( )

A．ORDER BY NAME ASC

B．ORDER BY ASC NAME

C．ORDER BY NAME DESC

D．ORDER BY DESC NAME

答案：A

87．有关系S（S＃，SNAME，SAGE），C（C＃，CNAME），SC（S＃，C＃，GRADE）。其中S＃是学生号，SNAME是学生姓名，SAGE是学生年龄， C＃是课程号，CNAME是课程名称。要查询选修“ACCESS”课的年龄不小于20的全体学生姓名的SQL语句是SELECT SNAME FROM S，C，SC WHERE子句。这里的WHERE子句的内容是（）。

A．SAGE>=20 and CNAME=’ ACCESS’

B．S.S# = SC.S# and C.C# = SC.C# and SAGE in>=20 and CNAME in ‘ACCESS’

C．SAGE in>=20 and CNAME in ‘ACCESS’

D．S.S# = SC.S# and C.C# = SC.C# and SAGE>=20 and CNAME=‘ACCESS’

答案：D

88．以下哪项属于DDL操作( )

A．update

B．create

C． insert

D．delete

答案：B

89．查找条件为：姓名为NULL的记录( )

A．WHERE NAME NULL

B．\WHERE NAME IS NULL

C．WHERE NAME=NULL

D．\WHERE NAME ==NULL

答案：B

90．条件“IN(20,30,40)”表示( )

A．年龄在20到40之间

B．年龄在20到30之间

C．年龄是20或30或40

D．年龄在30到40之间

答案：C

91．正则表达式的转义符是( )

A．\\

B．\

C．;

D．$$

答案：A

92．更新数据表中的记录用以下哪一项( )

A．DELETE

B．ALTRE

C．UPDATE

D．SELECT

答案：C

93．关系数据库中，主键是( )

A．创建唯一的索引，允许空值

B．只允许以表中第一字段建立

C．允许有多个主键的

D．为标识表中唯一的实体

答案：D

94．使用SELECT语句随机地从表中挑出指定数量的行，可以使用的方法是( )

A．在LIMIT子句中使用RAND()函数指定行数，并用ORDER BY子句定义一个排序规则

B．只要使用LIMIT子句定义指定的行数即可，不使用ORDER BY子句

C．只要在ORDER BY子句中使用RAND()函数，不使用LIMIT子句

D．在ORDER BY子句中使用RAND()函数，并用LIMIT子句定义行数

答案：D

95．进入要操作的数据库TEST用以下哪一项( )

A．IN TEST

B．SHOW TEST

C．USER TEST

D．USE TEST

答案：D

96．例如数据库中有A表，包括学生，学科，成绩三个字段 , 数据库结构为

学生学科成绩

张三语文 80

张三数学 100

李四语文 70

李四数学 80

李四英语 80

如何统计每个学科的最高分( )

A．select 学生,max(成绩) from A group by 学生;

B．select 学生,max(成绩) from A group by学科;

C．select 学生,max(成绩) from A order by学生;

D．select 学生,max(成绩) from A group by 成绩;

答案：B

97．下列哪些语句对主键的说明正确( )

A．主键可重复

B．主键不唯一

C．在数据表中的唯一索引

D．主键用foreign key修饰

答案：C

98．数据库服务器、数据库和表的关系，正确的说法是( )

A．一个数据库服务器只能管理一个数据库，一个数据库只能包含一个表

B．一个数据库服务器可以管理多个数据库，一个数据库可以包含多个表

C．一个数据库服务器只能管理一个数据库，一个数据库可以包含多个表

D．一个数据库服务器可以管理多个数据库，一个数据库只能包含一个表

答案：B

99．例如数据库中有A表，包括学生，学科，成绩三个字段 , 数据库结构为

学生学科成绩

张三语文 60

张三数学 100

李四语文 70

李四数学 80

李四英语 80

如何统计最高分>80的学科( )

A．SELECT MAX(成绩) FROM A GROUP BY学科 HAVING MAX(成绩)>80;

B．SELECT学科 FROM A GROUP BY学科 HAVING成绩>80;

C．SELECT学科 FROM A GROUP BY学科 HAVING MAX(成绩)>80;

D．SELECT学科 FROM A GROUP BY学科 WHERE MAX(成绩)>80;

答案：C

100．统计每个部门中人数( )

A．SELECT SUM(ID) FROM EMP GROUP BY DEPTNO;

B．SELECT SUM(ID) FROM EMP ORDER BY DEPTNO;

C．SELECT COUNT(ID) FROM EMP ORDER BY DEPTNO;

D．SELECT COUNT(ID) FROM EMP GROUP BY DEPTNO;

答案：D

101．DECIMAL是( )数据类型

A．可变精度浮点值

B．整数值

C．双精度浮点值

D．单精度浮点值

答案：A

102．视图是一种常用的数据对象，它是提供( )和( )数据的另一种途径，可以简化数据库操作

A．插入，更新

B．查看，检索

C．查看，存放

D．检索，插入

答案：C

103．删除数据表中的一条记录用以下哪一项( )

A．DELETED

B．DELETE

C．DROP

D．UPDATE

答案：B

二、多项选择题

1．触发器是响应以下任意语句而自动执行的一条或一组MYSQL语句( )

A．UPDATE

B．INSERT

C．SELECT

D．DELETE

答案：B,A,D

2．对于删除操作以下说法正确的是（）

A．drop database 数据库名：删除数据库

B．delete from 表名；删除表中所有记录条

C．delete from 表名 where 字段名=值；删除符合条件的记录条

D．drop table 表名；删除表

答案：B,C,D,A

3．下面正确的说法是( )

A．关键字只能由单个的属性组成

B．在一个关系中，关键字的值不能为空

C．一个关系中的所有候选关键字均可以被指定为主关键字

D．关键字是关系中能够用来惟一标识元组的属性

答案：D,B,C

4．以下说法正确的是( )

A．字符型既可用单引号也可用双引号将串值括起来

答案：A,D,B

5．关于主键下列说法正确的是（）

A．可以是表中的一个字段，

B．是确定数据库中的表的记录的唯一标识字段，

C．该字段不可为空也不可以重复

D．可以是表中的多个字段组成的。

答案：B,A,D,C

6．mySQL支持哪些逻辑运算符

A．&&

B．||

C．NOT

D．AND

答案：D,C

7．以下不属于浮点型的是（）

A．smallint

B．mediumint

C．float

D．int

答案：A,B,D

8．下列正确的命令是( )

A．show tables;

B．show columns;

C．show columns from customers;

D．show databases;

答案：D,A,C

9．正则表达式中，重复元字符“*”表示( )

A．无匹配

B．只匹配1个

C．0个匹配

D．多个匹配

答案：C,D

10．下面对 union 的描述正确的是( )

A．union 只连接结果集完全一样的查询语句

B．union 可以连接结果集中数据类型个数相同的多个结果集

C．union 是筛选关键词，对结果集再进行操作

D．任何查询语句都可以用 union 来连接

答案：D,A,C

11．下列哪一个逻辑运算符的优先级排列不正确( )

A．AND/NOT/OR

B．NOT/AND/OR

C．OR/NOT /AND

D．OR/AND/NOT

答案：A,C,D

12．对某个数据库进行筛选后 , ( )。

A． B）可以选出符合某些条件组合的记录

B．D）不能选择出符合条件组合的记录

C．A）可以选出符合某些条件的记录

D．C）只能选择出符合某一条件的记录

答案：C,A

13．下列语句错误的是( )

A．select * from orders where ordername is not null;

B．select * from orders where ordername<>null;

C．select * from orders where ordername is null;

D．select * from orders where ordername not is null;

答案：D,B

14．在下列关于关系的叙述中，正确的是( )

A．C）行在表中的顺序无关紧要

B． A）表中任意两行的值不能相同

C． D）列在表中的顺序无关紧要

D．B）表中任意两列的值不能相同

答案：B,A,C

15．下面系统中属于关系数据库管理系统的是( )

A． B）MS_SQL SERVER

B．A）Oracle

C． C）IMS

D． D）DB2

答案：B,A,C

16．下列是MYSQL比较运算符的是( )

A．!=

B．<>

C．==

D．>=

答案：D,B,A

17．Excel 中有关数据库内容 , 描述正确的有( )。

A．每一个 Excel 数据库对应一个工作簿文件

B．一列为一个字段 , 描述实体对象的属性

C．Excel 数据库属于“关系数据模型”, 又称为关系型数据库

D．一行为一个记录 , 描述某个实体对象

答案：D,B,C

18．下面关于使用UPDATE语句，正确的是( )

A．被定义为NOT NULL的列不可以被更新为NULL

B．不能在一个子查询中更新一个表，同时从同一个表中选择

C．不能把ORDER BY或LIMIT与多表语法的UPDATE语句同时使用

D．如果把一列设置为其当前含有的值，则该列不会更新

答案：D,C,B

19．关于Excel 数据库应用的描述正确的有( )。

A．是一个数据清单

B．是按一定组织方式存储在一起的相关数据的集合

C．是一个数组

D．是程序化的电子表格

答案：B,A

20．关于DELETE和TRUNCATE TABLE的说法，正确的是( )

A．两者都可以删除指定条目的记录

B．前者可以删除指定条目的记录，后者不能

C．两者都反回被删除记录的数目

D．前者返回被删除记录数目，后者不返回

答案：B,D

21．关于游标，下列说法正确的是( )

A．声明后必须打开游标以供使用

B．结束游标使用时，必须闭游标

C．使用游标前必须声明它

D．游标只能用于存储过程和函数

答案：D,C,A,B

22．下列说法正确的是( )

A．在MySQL中，不允许有空表存在，即一张数据表中不允许没有字段。

B．在MySQL中，对于存放在服务器上的数据库，用户可以通过任何客户端进行访问。

C．数据表的结构中包含字段名、类型、长度、记录。

D．字符型数据其常量标志是单引号和双引号，且两种符号可以混用。

答案：B,A

23．下面数据库名称合法的是( )

A．db1/student

B．db1.student

C．db1_student

D．db1&student

答案：D,C

24．下面语句中，表示过虑条件是vend_id=1002或vend_id=1003的是( )

A．select * from products where vend_id=1002 or vend_id=1003

B．select * from products where vend_id in (1002,1003);

C．select * from products where vend_id not in (1004,1005);

D．select * from products where vend_id=1002 and vend_id=1003

答案：B,A

25．下列哪些列类型是数值型的数据( )。

A．DOUBLE

B．INT

C．SET

D．FLOAT

答案：B,A,D

26．以下否定语句搭配正确的是（）

A．not in

B．in not

C．not between and

D．is not null

答案：C,A,D

27．下面检索结果一定不是一行的命令是( )

A．select distinct * from orders ;

B．select * from orders limit 1,2;

C．select top 1 * from orders;

D．select * from orders limit 1;

答案：C,A,B

28．以下哪些是mySQL数据类型（）

A．BIGINT

B．TINYINT

C．INTEGER

D．INT

答案：D,C,A,B

29．关于group by 以下语句正确的是( )

A．SELECT store_name FROM Store_Information GROUP BY store_name

B． SELECT SUM(sales)　FROM Store_Information GROUP BY sales

C．SELECT store_name, price SUM(sales)　FROM Store_Information GROUP BY store_name，price

D．SELECT store_name, SUM(sales)　FROM Store_Information GROUP BY store_name

答案：D,C,B

30．在数据库系统中，有哪几种数据模型？( )

A． D）实体联系模型

B． C）关系模型

C． A）网状模型

D． B）层次模型

答案：C,D,B

31．关于CREATE语句下列说法正确的是（）

A．create table 表名(字段名1 字段类型,字段名2 字段类型,…..)

B． create tables 表名(字段类型,字段名1 字段类型,字段名2…..)

C．create tables 表名(字段名1 字段类型,字段名2 字段类型,…..)

D． create table 表名(字段类型,字段名1 字段类型,字段名2…..)

答案：A,D

32．以下说法正确的是( )

A．一个服务器只能有一个数据库

B．一个服务器可以有多个数据库

C．一个数据库只能建立一张数据表

D．一个数据库可以建立多张数据表

答案：B,D

33．下列说法正确的是( )

A．一张数据表一旦建立完成，是不能修改的。

B．在MySQL中，用户在单机上操作的数据就存放在单机中。

C．在MySQL中，可以建立多个数据库，但也可以通过限定，使用户只能建立一个数据库。

D．要建立一张数据表，必须先建数据表的结构。

答案：C,B,D

34．“show databases like ‘student%’”命令可以显示出以下数据库( )

A．student_my

B．studenty

C．mystudent

D．student

答案：D,A,B

35．下面的选项是关系数据库基本特征的是（）。

A．与列的次序无关

B．不同的列应有不同的数据类型

C．不同的列应有不同的列名

D．与行的次序无关

答案：C,D,A

36．在mysql提示符下，输入____命令，可以查看由mysql自己解释的命令( )

A．\?

B．?

C．help

D．\h

答案：C,D,B

37．下列哪些数据是字符型数据。( )

A．中国

B．“1+2”

C．”can’t”

D．”张三－李四”

答案：B,D,C

38．关于语句limit 5,5，说法正确的是

A．表示检索出第5行开始的5条记录

B．表示检索出行6开始的5条记录

C．表示检索出第6行开始的5条记录

D．表示检索出行5开始的5条记录

答案：C,D

39．SQL语言集几个功能模块为一体，其中包括（）

A．C. DCL

B． B. DML

C． D. DNL

D．A. DDL

答案：D,B,A

40．下列说法正确的是（）

A．alter table user drop column sex;

B．alter table user add sex varchar(20);

C．alter table user drop sex;

D．alter table user modify id int primary key;

答案：D,B,A,C

41．视图一般不用于下列哪些语句( )

A．DELETE

B．SELECT

C．INSERT

D．UPDATE

答案：C,D,A

42．在算术运算符、比较运算符、逻辑运算符，这三种符号中，它们的优先级排列不正确的是( )

A．算术/逻辑/比较

B．比较/逻辑/算术

C．比较/算术/逻辑

D．算术/比较/逻辑

答案：A,C,B

43．对同一存储过程连续两次执行命令DROP PROCEDURE IF EXISTS，将会( )

A．第一次执行删除存储过程，第二次产生一个错误

B．第一次执行删除存储过程，第二次无提示

C．存储过程不能被删除

D．最终删除存储过程

答案：B,D

44．关于检索结果排序，正确的是( )

A．关键字DESC表示降序，ASC表示升序

B．如果指定多列排序，只能在最后一列使用升序或降序关键字

C．如果指定多列排序，可以在任意列使用升序或降序关键字

D．关键字ASC表示降序，DESC表示升序

答案：A,C

45．以下语句错误的是（）

;

SELECT rank, AVG(salary) FROM people HAVING AVG(salary) > 1000 GROUP BY rank;

A．SELECT rank, AVG(salary) FROM people GROUP BY rank HAVING AVG(salary) > 1000

B．SELECT rank, AVG(salary) FROM people HAVING AVG(salary) > 1000 GROUP BY rank;

C．SELECT AVG(salary) FROM people GROUP BY rank HAVING AVG(salary) > 1000;

D．SELECT rank, AVG(salary) FROM people GROUP BY rank WHERE AVG(salary) > 1000;

答案：D,B

46．创建数据表时，下列哪些列类型的宽度是可以省略的。 ( )

A．DATE

B．INT

C．CHAR

D．TEXT

答案：B,D,A

47．关于主键下列说法正确的是（）

A．主键的值对用户而言是没有什么意义

B．主键的主要作用是将记录和存放在其他表中的数据进行关联。

C．一个主键是唯一识别一个表的每一记录，

D．主键是不同表中各记录之间的简单指针。

答案：C,B,D,A

48．您需要显示从2009年1月1日到2009年12月31日雇佣的所有职员的姓名和雇佣日期。职员信息表tblEmployees包含列Name和列HireDate，下面哪些语句能完成该功能 ( )

A．SELECT Name, HireDate FROM tblEmployees

B．SELECT Name, HireDate FROM tblEmployees WHERE HireDate =’2009-01-01’ OR ‘2009-12-31’

C．SELECT Name, HireDate FROM tblEmployees WHERE HireDate BETWEEN ’2008-12-31’ AND ‘2010-01-01’

D．SELECT Name, HireDate FROM tblEmployees WHERE substring(HireDate,1,4)=2009;

答案：C,D

49．以下哪项是事务特性( )

A．独立性

B．持久性

C．原子性

D．一致性

答案：C,D,A,B

50．对于显示操作以下说法正确的是（）

A．show database；显示所有数据库

B．show table；显示所有表

C．show tables；显示所有表

D．show databases；显示所有数据库

答案：D,B

51．语句select * from products where prod_name like ‘%se%’结果集包括( )

A．检索products表中prod_name字段以’se’结尾的数据

B．检索products表中prod_name字段以’se’开关的数据

C．检索products表中prod_name字段包含’se’的数据

D．检索products表中prod_name字段不包含’se’的数据

答案：C,B,A

52．在mysql提示符下可以输入一个SQL语句，并以( )结尾，然后按回车执行该语句( )

A．“\G”

B．“。”

C．“\g”

D．“;”

答案：D,C,A

53．关于insert语句下列说法正确的是（）

A．insert into 表名values(字段名1对应的值);

B．insert into 表名 values(字段名1对应的值，字段名2对应值);

C．insert into 表名(字段名1) value (字段名1对应的值);

D． insert into 表名(字段名1，字段名2) values(字段名1对应的值，字段名2对应值);

答案：D,B

54． 23 关系数据模型哪些优点？( )

A．A）结构简单

B． C）有标准语言

C． B）适用于集合操作

D． D）可表示复杂的语义

答案：A,C,B

55．对某个数据库使用记录单 , 可以进行的记录操作有( )

A．删除

B．新建

C．还原

D．插入

答案：B,A,C

56．关于select语句下列说法正确的是（）

A． select (name) from table person: 所有记录的name字段的值

B．select (name) from person where age=12 or name=”aa”; or 或者

C． select (name) from table person where age=12; 查找age=12的记录的那个字段的值

D． select (name，age) from person where age=12 and name=”aa”; and 并且

答案：B,D

57．在字符串比较中，下列哪些是不正确的( )

A．所有标点符号比数字大

B．所有数字都比汉字大

C．所有英文比数字小

D．所有英文字母都比汉字小

答案：B,C,A

58．数据库信息的运行安全采取的主措施有( )。

A．备份与恢复

B．应急

C．风险分析

D．审计跟踪

答案：C,D,A,B

三、填空题

1．select 9/3;的结果为_____。

答案：3.0000

2．补全语句:select vend_id,count(*) as num_prods from products group by ____;

答案：vend_id

3．用SELECT进行模糊查询时，可以使用匹配符，但要在条件值中使用____或%等通配符来配合查询。

答案：-

4．当所查询的表不在当前数据库时，可用___________________________格式来指出表或视图对象

答案：数据库.所有者名称.对象名称

5．语句SELECT　“1+2”；的显示结果是＿＿＿＿

答案：“1+2”

6．如果MySQL服务名为MySQL5，则在Windows的命令窗口中，启动MySQL服务的指令是____

答案：net start mysql5

7．MySQL是一种＿＿＿＿＿＿（多用户、单用户）的数据库管理系统

答案：多用户

8．select ‘2.5a’+3;的结果为_____。

答案：5.5

9．select (NULL<=>NULL) is NULL;的结果为_____。

答案：0

10．创建数据表的命令语句是＿＿＿＿＿＿＿＿＿

答案：create table

11．____语句可以修改表中各列的先后顺序

答案：ALTER TABLE

12．当某字段要使用AUTO＿INCREMENT的属性时，该字段必须是＿＿＿＿类型的数据。

答案：INT

13．当某字段要使用AUTO＿INCREMENT的属性时，除了该字段必须是指定的类型外，该字段还必须是＿＿＿＿

答案：关键字段或索引字段

14．在SELECT语句的FROM子句中最多可以指定_______个表或视图

答案：256

15．ODBC是一种____________

答案：开放式数据库连接

16．在SELECT语句的FROM子句中可以指定多个表或视图，相互之间要用________分隔

答案：逗号

17．Table ‘a1’ already exists这个错误信息的含义是＿＿＿＿＿

答案：表a1已经存在

18．对一个超过200个汉字的内容，应用一个＿＿＿＿型的字段来存放

答案：text

19．在INSERT触发器中，可以引用一个名为____的虚拟表，访问被插入的行

答案：NEW

20．语句SELECT　“张三\n李四”的显示结果是＿＿＿＿＿

答案：”张三\n李四”

21．smallint数据类型占用的字节数分别为

答案：1

22．在DELETE触发器中，可以引用一个名为____的虚拟表，访问被删除的行

答案：OLD

23．察看当前数据库中表名语句是_____。

答案：show tables

24．删除表命令是：___________________________________________。

答案：drop table

25．select ‘Abc’=’abc’;的结果为_____。

答案： 1

26．select -2.0*4.0;的结果为_____。

答案：-8.00

27．tinyint数据类型占用的字节数为___

答案：2

28．补全语句：select vend_id,count(*) from products where prod_price>=10 group by vend_id ____ count(*)>=2;

答案：having

29．计算字段的累加和的函数是：_______

答案：sum()

30．用SELECT进行模糊查询时，可以使用________匹配符

答案：like

四、判断题

1．( )主键被强制定义成NOT NULL和UNIQUE。

答案：Y

2．( )select语句的过滤条件既可以放在where子句中，也可以放在from子句中。

答案：N

3．( )逻辑值的“真”和“假”可以用逻辑常量TRUE和FALSE表示。

答案：Y

4．( )如果在排序和分组的对象上建立了索引，可以极大地提高速度。

答案：Y

5．( )建立索引的目的在于加快查询速度以及约束输入的数据。

答案：Y

6．( )UPDATE语句可以有WHERE子句和LIMIT子句。

答案：Y

7．( )x between y and z等同于x>y && x<z。

答案：N

8．( )MySQL数据库管理系统只能在Windows操作系统下运行。

答案：N

9．( )对于字符串型数据，空字符串’’就是NULL，对于数值型数据0就是NULL。

答案：N

10．( )LTRIM、RTRIM、TRIM函数既能去除半角空格，又能去除全角空格。

答案：N

11．( )NULL和Null都代表空值。

答案：Y

12．( )关系型数据库管理系统简称为RDBMS。

答案：Y

13．( )用union上下连接的各个select都可以带有自己的order by子句。

答案：N

14．( )ALTER TABLE语句可以修改表中各列的先后顺序。

答案：Y

15．( )!=和<>都代表不等于。

答案：Y

16．( )所创建的数据库和表的名字，都可以使用中文。

答案：Y

17．( )SELECT语句的ORDER BY子句定义的排序表达式所参照的列甚至可以不出现在输出列表中。

答案：Y

18．( )在C/S模式中，客户端不能和服务器端安装在同一台机器上。

答案：N

19．( )UPDATE语句修改的是表中数据行中的数据，也可以修改表的结构。

答案：N

20．( )create table 语句中有定义主键的选项。

答案：Y

21．( )结构化查询语言只涉及查询数据的语句，并不包括修改和删除数据的语句。

答案：N

22．( )一句delete语句能删除多行。

答案：Y

23．( )字符串“2008-8-15”

答案：Y

24．( )INSERT语句所插入的数据行数据可以来自另外一个SELECT语句的结果集。

答案：Y

25．( )所有TIMESTAMP列在插入NULL值时，自动填充为当前日期和时间。

答案：Y

26．( )带有GROUP BY子句的SELECT语句，结果集中每一个组只用一行数据来表示。

答案：Y

27．( )UNION中ALL关键字的作用是在结果集中所有行全部列出，不管是否有重复行。

答案：Y

28．( )为了让MySQL较好地支持中文，在安装MySQL时，应该将数据库服务器的缺省字符集设定为gb2312。

答案：N

29．( )只能将表中的一个列定义为主键，不能将多个列定义为复合的主键。

答案：N

30．( )当一个表中所有行都被delete语句删除后，该表也同时被删除了。

答案：N

五、简答题

1．什么是数据库镜像？它有什么用途？

答案：

答：

数据库镜像即根据DBA的要求，自动把整个数据库或者其中的部分关键数据复制到另一个磁盘上。每当主数据库更新时，DBMS自动把更新后的数据复制过去，即DBMS自动保证镜像数据与主数据的一致性。

数据库镜像的用途有：

一是用于数据库恢复。当出现介质故障时，可由镜像磁盘继续提供使用，同时DBMS自动利用镜像磁盘数据进行数据库的恢复，不需要关闭系统和重装数据库副本。

二是提高数据库的可用性。在没有出现故障时，当一个用户对某个数据加排它锁进行修改时，其他用户可以读镜像数据库上的数据，而不必等待该用户释放锁。

2．为什么事务非正常结束时会影响数据库数据的正确性

答案：答：

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。

3．什么是物理设计：

答案：对一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程，称为数据库的物理设计。物理结构，主要指数据库在物理设备上的存储结构和存取方法。

4．什么是日志文件？为什么要设立日志文件？

答案：答：

（1）日志文件是用来记录事务对数据库的更新操作的文件。

（2）设立日志文件的目的是：进行事务故障恢复；进行系统故障恢复；协助后备副本进行介质故障恢复。

5．在数据库系统生存期中，生存期的总开销可分为几项：

答案：规划开销、设计开销、实现与测试开销、操作开销、维护开销。

6．数据库中为什么要有恢复子系统？它的功能是什么？

答案：答：

因为计算机系统中硬件的故障、软件的错误、操作员的失误以及恶意的破坏是不可避免的，这些故障轻则造成运行事务非正常中断，影响数据库中数据的正确性，重则破坏数据库，使数据库中全部或部分数据丢失，因此必须要有恢复子系统。

恢复子系统的功能是：把数据库从错误状态恢复到某一已知的正确状态（亦称为一致状态或完整状态）。

7．数据库运行中可能产生的故障有哪几类？哪些故障影响事务的正常执行？哪些故障破坏数据库数据？

答案：答：数据库系统中可能发生各种各样的故障，大致可以分以下几类：

（1）事务内部的故障；

（2）系统故障；

（3）介质故障；

（4）计算机病毒。

事务故障、系统故障和介质故障影响事务的正常执行；介质故障和计算机病毒破坏数据库数据。

8．登记日志文件时为什么必须先写日志文件，后写数据库？

答案：答：

把对数据的修改写到数据库中和把表示这个修改的日志记录写到日志文件中是两个不同的操作。有可能在这两个操作之间发生故障，即这两个写操作只完成了一个。

如果先写了数据库修改，而在运行记录中没有登记这个修改，则以后就无法恢复这个修改了。如果先写日志，但没有修改数据库，在恢复时只不过是多执行一次UNDO操作，并不会影响数据库的正确性。所以一定要先写日志文件，即首先把日志记录写到日志文件中，然后写数据库的修改。

9．数据库转储的意义是什么？

答案：

答：

数据转储是数据库恢复中采用的基本技术。所谓转储即DBA定期地将数据库复制到磁带或另一个磁盘上保存起来的过程。当数据库遭到破坏后可以将后备副本重新装入，将数据库恢复到转储时的状态。

静态转储：在系统中无运行事务时进行的转储操作。静态转储简单，但必须等待正运行的用户事务结束才能进行。同样，新的事务必须等待转储结束才能执行。显然，这会降低数据库的可用性。

动态转储：指转储期间允许对数据库进行存取或修改。动态转储可克服静态转储的缺点，它不用等待正在运行的用户事务结束，也不会影响新事务的运行。但是，转储结束时后援副本上的数据并不能保证正确有效。因为转储期间运行的事务可能修改了某些数据，使得后援副本上的数据不是数据库的一致版本。

为此，必须把转储期间各事务对数据库的修改活动登记下来，建立日志文件（log file）。这样，后援副本加上日志文件就能得到数据库某一时刻的正确状态。

转储还可以分为海量转储和增量转储两种方式。

海量转储是指每次转储全部数据库。增量转储则指每次只转储上一次转储后更新过的数据。从恢复角度看，使用海量转储得到的后备副本进行恢复一般说来更简单些。但如果数据库很大，事务处理又十分频繁，则增量转储方式更实用更有效。

10．试述事务的概念及事务的四个特性。

答案：答：

事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做,是一个不可分割的工作单位。

事务具有四个特性：原子性、一致性、隔离性）和持续性。

11．数据库恢复的基本技术有哪些？

答案：答：

数据转储和登录日志文件是数据库恢复的基本技术。

当系统运行过程中发生故障，利用转储的数据库后备副本和日志文件就可以将数据库恢复到故障前的某个一致性状态。

12．数据库设计中的规划阶段的主要任务：

答案：是进行建立数据库的必要性及可行性分析，确定数据库系统在组织中和信息系统中的地位，以及各个数据库之间的联系。

六、编程题

1．表名User

Name Tel Content Date

(b) 请用sql语句把张三的时间更新成为当前系统时间

(b) update user set date=time() where name=’张三’;

2．当前数据库是testdb，在该数据库中，有students、scores、courses、majors四个表，其结构及数据如下所列：

students

学号

int(11) name

姓名

char(4) sex

性别

char(1) bofd

生日

date mid

专业号

tinyint

1 张三男 1980-12-03 1

2 王武女 1980-09-22 3

3 李四女 1981-03-04 2

4 赵六女 1981-05-24 1

5 张建国男 1980-06-02 4

6 赵娟女 1980-08-30 2

scores

学号

char(10) term

学期

tinyint cid

课程编号

smallint score

分数

numerirc(4,1)

1 1 2 80.0

1 2 2 76.0

2 1 3 60.0

2 2 3 65.0

3 4 1 66.0

3 4 2 NULL

3 4 4 81.0

3 4 6 70.0

5 1 2 67.0

6 1 2 50.0

6 2 2 87.0

6 2 3 86.0

courses

cid

课程编号smallint cname

课程名称chr(24)

1 电子商务概论

2 c语言程序设计

3 MySQL数据库

4 php程序设计

5 FoxPro数据库

6 会计原理

majors

mid

专业号tinyint mname

专业名称chr(24)

1 电子商务

2 商务英语

3 计算机硬件

4 计算机软件

5 社区管理

6 日语

不考虑学号、考试科目和学期，计算并列出所有考试成绩中，成绩为优的分数的累加值，和成绩为良的分数的平均值。优和良的界线是90分和80分。（使用一句语句）

答案：select Sum(if(score>90,score,0)) AS 成绩为优的分数累加,

avg(if(score between 80 and 89,score,null)) AS 成绩为良的平均成绩

3．当前数据库是testdb，在该数据库中，有students、scores、courses、majors四个表，其结构及数据如下所列：

students

学号

int(11) name

姓名

char(4) sex

性别

char(1) bofd

生日

date mid

专业号

tinyint

1 张三男 1980-12-03 1

2 王武女 1980-09-22 3

3 李四女 1981-03-04 2

4 赵六女 1981-05-24 1

5 张建国男 1980-06-02 4

6 赵娟女 1980-08-30 2

scores

学号

char(10) term

学期

tinyint cid

课程编号

smallint score

分数

numerirc(4,1)

1 1 2 80.0

1 2 2 76.0

2 1 3 60.0

2 2 3 65.0

3 4 1 66.0

3 4 2 NULL

3 4 4 81.0

3 4 6 70.0

5 1 2 67.0

6 1 2 50.0

6 2 2 87.0

6 2 3 86.0

courses

cid

课程编号smallint cname

课程名称chr(24)

1 电子商务概论

2 c语言程序设计

3 MySQL数据库

4 php程序设计

5 FoxPro数据库

6 会计原理

majors

mid

专业号tinyint mname

专业名称chr(24)

1 电子商务

2 商务英语

3 计算机硬件

4 计算机软件

5 社区管理

6 日语

将students表中的结构（主键和索引）和数据复制到一个新的students1表中。（分两个步骤两句语句）

答案：create table students1 like students;

insert into students1 select * from students;

4．现有一销售表，表名是sale，它的结构如下：

id int (标识号)

codno char(7) (商品编码)

codname varchar(30) (商品名称)

spec varchar(20) （商品规格）

price numeric(10,2) （价格）

sellnum int （销售数量）

deptno char(3) （售出分店编码）

selldate datetime （销售时间）

要求：写出查询销售时间段在2002-2-15日到2002-4-29之间，分店编码是01的所有记录。

答案：SELECT * FROM sale

WHERE (selldate >= ‘2002-2-15′ AND selldate <=’2002-4-29′)

AND deptno = ’01’

5．编写一个返回表products中prod_price字段平均值且名称为productpricing的存储过程

答案：CREATE PROCEDURE productpricing()

BEGIN

Select avg(prod_price) from products;

End;

6．创建一张学生表，表名stu，包含以下信息:

学号，姓名(8位字符），年龄，性别（4位字符），家庭住址（50位字符），联系电话

答案：Create table stu (学号 int ,

姓名 varchar(8),

年龄 int,

性别 varchar(4),

家庭地址 varchar(50),

联系电话 int

);

基于Netty高并发物联网服务器研究与设计

随着更多的物联网设备接入，给提供高并发物联网服务器带来了挑战。基于此本文首先研究目前常用方法找出不足。然后，提出使用JavaNIO库Netty解决高并发问题。以可配置方式解决物联网数据传输过程的拆包及粘包问题。并提出三种级别数据通道，以满足物联网数据传输的不同需求。最后，简要分析物联网应用服务器及用户终端展现方式。提出目前系统不足及今后的工作重点。

1 引言

随着物联网技术的快速发展，传统设备加入了传感器及网络能力，这使得它们能够实时感知环境信息，如：智能家居或家庭、智能城市、环境监测、智能医疗、食品溯源、国防军事、智能交通和智能环境等，客户端的规模正在快速增长，在某一时刻向服务器并发地发送数据请求的客户端随之增加，如何在短时间内提高服务器并发处理能力是数据通信服务系统开发面临的一个重要问题。针对数据通信特性要求，文献[2]提出使用Linux下基于epoll+线程池高并发服务器方式，此种方式对系统依赖过高。文献[3]提出使用Java基础Socket套接字编程+socket，此方式解决了对操作系统依赖问题，但对于高并发传输时生成过多线程，使服务器性能下降。文献[4]使用服务器应用软件、网关模块和各种医疗终端，医疗终端采用WIFI、蓝牙或者Lora将数据传输到网关模块。此方式问题一是如果网关模块出现故障，所有的采集模块将处于瘫痪状态。二是每一个采集模块对于服务器来说无法直接了解模块状态，较难管理。文献[5]中提出使用Tomcat+JSP+servlet+MVC的Web服务方式，此方法对于终端设备有嵌入式操作系统有HTTP协议栈，但大多数终端低成本要求无法使用嵌入式操作系统。目前较常用的网络模块使用TCP方式传输数据，此方法无法满足需求。本文提出使用基于Netty实现JavaNIO方式开发高并发物联网服务器，物联网前端采集使用GPRS、3 G/5G及NB-IOT数据通信方式。提供了一整套前端数据采集与后端数据整理、采集及展示设计方案。

2 系统架构

系统由物联网终端、物联网并发服务器、物联网业务服务器、数据存储服务器、物联网应用服务器以及用户展示控制端（智能手机、微信、网页端）如图1。物联网终端模块数据主要为两类：一是定时从物联网采集数据，此数据通常由物联网终端发起。二是从用户或其它控制模块发送给物联网终端，此方式也是目前物联网实现相对困难的技术。物联网业务服务器主要解决，物联网并发服务器接收数据后续处理，防止数据进入并发服务器后因数据存储时间长而影响系统整体性能，数据分发到多台业务服务器中进行数据库操作。数据存储服务器分为硬件数据与内存数据库，硬盘数据库主要存储持久化的数据，内存数据库存储实时性要求高但不持久化存储的数据。物联网应用服务器为用户提供智能手机、微信小程序及网页端展示数据与控制。本系统数据通道级别分为三级：第一级数据直接通过物联网并发服务器转发两端（用户终端、物联网终端）的实时数据。第二级是两端通过内存型数据库进行转发的准实时数据。第三级是两端的数据通过硬盘数据保存与转发。

3 系统设计

2007年JDKl.7发布，升级后的NI02.0提供了异步文件通道和异步套接字通道的实现，文件处理能力有了进一步的提升。尽管NIO的吞吐量和可靠性相对于传统的BIO（同步阻塞式IO）有了质的飞跃，但其类库和API十分繁杂，使用起来非常困难。再加上粘包拆包、断线重连等可靠性处理的工作量和复杂度都非常大，因此直接使用NIO开发复杂度增大，使用开发周期及成本上升，给后期物联网服务器升级维护带来困难。为了简化NIO网络编程，一些开源项目发布供用户使用、学习、修改。其中Netty、Mina是两个常用框架。其中Mina由于目前不再持续更新，使用风险升高。Netty的功能、性能、健壮性、可定制和可扩展性已经得到了大量商业项目的成功验证。包括阿里巴巴等知名公司使用Netty进行业务开发及扩展。本文基于Netty框架，开发并发高性能物联网服务器，使通信系统的性能和可靠性均得到了极大的提高。

TCP粘包拆包问题无法在传输层处理，需要通过用户自定义方式进行处理。依据网络传输数据特性常常使用的解决方法有三种：一是消息保持固定长度;二是在每一帧消息的尾部使用特殊字符如$$等类型的字符串;三是将消息分为消息头和消息体，在消息头中存储包括消息长度字段。文献[6]比较三种方式的特点，由于受到物联网终端成本、技术开发能力限制，有可能采用不同方式。本系统考虑适配性能，设计采用可配置的方式支持三种方式。

public fin alstaticbooleanU SE_FIXEDLENGTH= false;//消息定长

publicfinalsraricbooleanUSE_DELIMITER= true，//特殊分割符

public finalstaticbooleanUSE_LENGTHFIELD= false;//消息头与消息体

以上三者是互斥，只能使用一种。每一种类型对应一种解码器，特殊分割符及消息头类型要继承LengthFieldBasedFrameDecoder和DelimiterBasedFrameDecoder并重写Decode方法。

并发服务器类图如图2。

以LengthFieldBasedFrameDecoder的子类为例：协议头设计如表l。

代码如下：

@Override

protected Object decode（ChannelHandlerContextctx， ByteBufin） throws Exception{

if （in==null）{

returnnull，

大数据框架Hadoop、Storm、Samza、Spark和Flink对比分析

简介

大数据是收集、整理、处理大容量数据集，并从中获得见解所需的非传统战略和技术的总称。虽然处理数据所需的计算能力或存储容量早已超过一台计算机的上限，但这种计算类型的普遍性、规模，以及价值在最近几年才经历了大规模扩展。

在之前的文章中，我们曾经介绍过有关大数据系统的常规概念、处理过程，以及各种专门术语，本文将介绍大数据系统一个最基本的组件：处理框架。处理框架负责对系统中的数据进行计算，例如处理从非易失存储中读取的数据，或处理刚刚摄入到系统中的数据。数据的计算则是指从大量单一数据点中提取信息和见解的过程。

下文将介绍这些框架：

仅批处理框架：

Apache Hadoop

仅流处理框架：

Apache Storm

Apache Samza

混合框架：

Apache Spark

Apache Flink

大数据处理框架是什么？

处理框架和处理引擎负责对数据系统中的数据进行计算。虽然“引擎”和“框架”之间的区别没有什么权威的定义，但大部分时候可以将前者定义为实际负责处理数据操作的组件，后者则可定义为承担类似作用的一系列组件。

例如Apache Hadoop可以看作一种以MapReduce作为默认处理引擎的处理框架。引擎和框架通常可以相互替换或同时使用。例如另一个框架Apache Spark可以纳入Hadoop并取代MapReduce。组件之间的这种互操作性是大数据系统灵活性如此之高的原因之一。

虽然负责处理生命周期内这一阶段数据的系统通常都很复杂，但从广义层面来看它们的目标是非常一致的：通过对数据执行操作提高理解能力，揭示出数据蕴含的模式，并针对复杂互动获得见解。

为了简化这些组件的讨论，我们会通过不同处理框架的设计意图，按照所处理的数据状态对其进行分类。一些系统可以用批处理方式处理数据，一些系统可以用流方式处理连续不断流入系统的数据。此外还有一些系统可以同时处理这两类数据。

在深入介绍不同实现的指标和结论之前，首先需要对不同处理类型的概念进行一个简单的介绍。

批处理系统

批处理在大数据世界有着悠久的历史。批处理主要操作大容量静态数据集，并在计算过程完成后返回结果。

批处理模式中使用的数据集通常符合下列特征…

· 有界：批处理数据集代表数据的有限集合

· 持久：数据通常始终存储在某种类型的持久存储位置中

· 大量：批处理操作通常是处理极为海量数据集的唯一方法

批处理非常适合需要访问全套记录才能完成的计算工作。例如在计算总数和平均数时，必须将数据集作为一个整体加以处理，而不能将其视作多条记录的集合。这些操作要求在计算进行过程中数据维持自己的状态。

需要处理大量数据的任务通常最适合用批处理操作进行处理。无论直接从持久存储设备处理数据集，或首先将数据集载入内存，批处理系统在设计过程中就充分考虑了数据的量，可提供充足的处理资源。由于批处理在应对大量持久数据方面的表现极为出色，因此经常被用于对历史数据进行分析。

大量数据的处理需要付出大量时间，因此批处理不适合对处理时间要求较高的场合。

Apache Hadoop

Apache Hadoop是一种专用于批处理的处理框架。Hadoop是首个在开源社区获得极大关注的大数据框架。基于谷歌有关海量数据处理所发表的多篇论文与经验的Hadoop重新实现了相关算法和组件堆栈，让大规模批处理技术变得更易用。

新版Hadoop包含多个组件，即多个层，通过配合使用可处理批数据：

HDFS：HDFS是一种分布式文件系统层，可对集群节点间的存储和复制进行协调。HDFS确保了无法避免的节点故障发生后数据依然可用，可将其用作数据来源，可用于存储中间态的处理结果，并可存储计算的最终结果。

YARN：YARN是Yet Another Resource Negotiator（另一个资源管理器）的缩写，可充当Hadoop堆栈的集群协调组件。该组件负责协调并管理底层资源和调度作业的运行。通过充当集群资源的接口，YARN使得用户能在Hadoop集群中使用比以往的迭代方式运行更多类型的工作负载。

MapReduce：MapReduce是Hadoop的原生批处理引擎。

批处理模式

Hadoop的处理功能来自MapReduce引擎。MapReduce的处理技术符合使用键值对的map、shuffle、reduce算法要求。基本处理过程包括：

从HDFS文件系统读取数据集

将数据集拆分成小块并分配给所有可用节点

针对每个节点上的数据子集进行计算（计算的中间态结果会重新写入HDFS）

重新分配中间态结果并按照键进行分组

通过对每个节点计算的结果进行汇总和组合对每个键的值进行“Reducing”

将计算而来的最终结果重新写入 HDFS

优势和局限

由于这种方法严重依赖持久存储，每个任务需要多次执行读取和写入操作，因此速度相对较慢。但另一方面由于磁盘空间通常是服务器上最丰富的资源，这意味着MapReduce可以处理非常海量的数据集。同时也意味着相比其他类似技术，Hadoop的MapReduce通常可以在廉价硬件上运行，因为该技术并不需要将一切都存储在内存中。MapReduce具备极高的缩放潜力，生产环境中曾经出现过包含数万个节点的应用。

MapReduce的学习曲线较为陡峭，虽然Hadoop生态系统的其他周边技术可以大幅降低这一问题的影响，但通过Hadoop集群快速实现某些应用时依然需要注意这个问题。

围绕Hadoop已经形成了辽阔的生态系统，Hadoop集群本身也经常被用作其他软件的组成部件。很多其他处理框架和引擎通过与Hadoop集成也可以使用HDFS和YARN资源管理器。

总结

Apache Hadoop及其MapReduce处理引擎提供了一套久经考验的批处理模型，最适合处理对时间要求不高的非常大规模数据集。通过非常低成本的组件即可搭建完整功能的Hadoop集群，使得这一廉价且高效的处理技术可以灵活应用在很多案例中。与其他框架和引擎的兼容与集成能力使得Hadoop可以成为使用不同技术的多种工作负载处理平台的底层基础。

流处理系统

流处理系统会对随时进入系统的数据进行计算。相比批处理模式，这是一种截然不同的处理方式。流处理方式无需针对整个数据集执行操作，而是对通过系统传输的每个数据项执行操作。

流处理中的数据集是“无边界”的，这就产生了几个重要的影响：

完整数据集只能代表截至目前已经进入到系统中的数据总量。

工作数据集也许更相关，在特定时间只能代表某个单一数据项。

处理工作是基于事件的，除非明确停止否则没有“尽头”。处理结果立刻可用，并会随着新数据的抵达继续更新。

流处理系统可以处理几乎无限量的数据，但同一时间只能处理一条（真正的流处理）或很少量（微批处理，Micro-batch Processing）数据，不同记录间只维持最少量的状态。虽然大部分系统提供了用于维持某些状态的方法，但流处理主要针对副作用更少，更加功能性的处理（Functional processing）进行优化。

功能性操作主要侧重于状态或副作用有限的离散步骤。针对同一个数据执行同一个操作会或略其他因素产生相同的结果，此类处理非常适合流处理，因为不同项的状态通常是某些困难、限制，以及某些情况下不需要的结果的结合体。因此虽然某些类型的状态管理通常是可行的，但这些框架通常在不具备状态管理机制时更简单也更高效。

此类处理非常适合某些类型的工作负载。有近实时处理需求的任务很适合使用流处理模式。分析、服务器或应用程序错误日志，以及其他基于时间的衡量指标是最适合的类型，因为对这些领域的数据变化做出响应对于业务职能来说是极为关键的。流处理很适合用来处理必须对变动或峰值做出响应，并且关注一段时间内变化趋势的数据。

Apache Storm

Apache Storm是一种侧重于极低延迟的流处理框架，也许是要求近实时处理的工作负载的最佳选择。该技术可处理非常大量的数据，通过比其他解决方案更低的延迟提供结果。

　流处理模式

Storm的流处理可对框架中名为Topology（拓扑）的DAG（Directed Acyclic Graph，有向无环图）进行编排。这些拓扑描述了当数据片段进入系统后，需要对每个传入的片段执行的不同转换或步骤。

　拓扑包含：

Stream：普通的数据流，这是一种会持续抵达系统的无边界数据。

Spout：位于拓扑边缘的数据流来源，例如可以是API或查询等，从这里可以产生待处理的数据。

Bolt：Bolt代表需要消耗流数据，对其应用操作，并将结果以流的形式进行输出的处理步骤。Bolt需要与每个Spout建立连接，随后相互连接以组成所有必要的处理。在拓扑的尾部，可以使用最终的Bolt输出作为相互连接的其他系统的输入。

Storm背后的想法是使用上述组件定义大量小型的离散操作，随后将多个组件组成所需拓扑。默认情况下Storm提供了“至少一次”的处理保证，这意味着可以确保每条消息至少可以被处理一次，但某些情况下如果遇到失败可能会处理多次。Storm无法确保可以按照特定顺序处理消息。

为了实现严格的一次处理，即有状态处理，可以使用一种名为Trident的抽象。严格来说不使用Trident的Storm通常可称之为Core Storm。Trident会对Storm的处理能力产生极大影响，会增加延迟，为处理提供状态，使用微批模式代替逐项处理的纯粹流处理模式。

为避免这些问题，通常建议Storm用户尽可能使用Core Storm。然而也要注意，Trident对内容严格的一次处理保证在某些情况下也比较有用，例如系统无法智能地处理重复消息时。如果需要在项之间维持状态，例如想要计算一个小时内有多少用户点击了某个链接，此时Trident将是你唯一的选择。尽管不能充分发挥框架与生俱来的优势，但Trident提高了Storm的灵活性。

　Trident拓扑包含：

流批（Stream batch）：这是指流数据的微批，可通过分块提供批处理语义。

操作（Operation）：是指可以对数据执行的批处理过程。

　优势和局限

目前来说Storm可能是近实时处理领域的最佳解决方案。该技术可以用极低延迟处理数据，可用于希望获得最低延迟的工作负载。如果处理速度直接影响用户体验，例如需要将处理结果直接提供给访客打开的网站页面，此时Storm将会是一个很好的选择。

Storm与Trident配合使得用户可以用微批代替纯粹的流处理。虽然借此用户可以获得更大灵活性打造更符合要求的工具，但同时这种做法会削弱该技术相比其他解决方案最大的优势。话虽如此，但多一种流处理方式总是好的。

Core Storm无法保证消息的处理顺序。Core Storm为消息提供了“至少一次”的处理保证，这意味着可以保证每条消息都能被处理，但也可能发生重复。Trident提供了严格的一次处理保证，可以在不同批之间提供顺序处理，但无法在一个批内部实现顺序处理。

在互操作性方面，Storm可与Hadoop的YARN资源管理器进行集成，因此可以很方便地融入现有Hadoop部署。除了支持大部分处理框架，Storm还可支持多种语言，为用户的拓扑定义提供了更多选择。

　总结

对于延迟需求很高的纯粹的流处理工作负载，Storm可能是最适合的技术。该技术可以保证每条消息都被处理，可配合多种编程语言使用。由于Storm无法进行批处理，如果需要这些能力可能还需要使用其他软件。如果对严格的一次处理保证有比较高的要求，此时可考虑使用Trident。不过这种情况下其他流处理框架也许更适合。

　Apache Samza

Apache Samza是一种与Apache Kafka消息系统紧密绑定的流处理框架。虽然Kafka可用于很多流处理系统，但按照设计，Samza可以更好地发挥Kafka独特的架构优势和保障。该技术可通过Kafka提供容错、缓冲，以及状态存储。

Samza可使用YARN作为资源管理器。这意味着默认情况下需要具备Hadoop集群（至少具备HDFS和YARN），但同时也意味着Samza可以直接使用YARN丰富的内建功能。

　流处理模式

Samza依赖Kafka的语义定义流的处理方式。Kafka在处理数据时涉及下列概念：

Topic（话题）：进入Kafka系统的每个数据流可称之为一个话题。话题基本上是一种可供消耗方订阅的，由相关信息组成的数据流。

Partition（分区）：为了将一个话题分散至多个节点，Kafka会将传入的消息划分为多个分区。分区的划分将基于键（Key）进行，这样可以保证包含同一个键的每条消息可以划分至同一个分区。分区的顺序可获得保证。

Broker（代理）：组成Kafka集群的每个节点也叫做代理。

Producer（生成方）：任何向Kafka话题写入数据的组件可以叫做生成方。生成方可提供将话题划分为分区所需的键。

Consumer（消耗方）：任何从Kafka读取话题的组件可叫做消耗方。消耗方需要负责维持有关自己分支的信息，这样即可在失败后知道哪些记录已经被处理过了。

由于Kafka相当于永恒不变的日志，Samza也需要处理永恒不变的数据流。这意味着任何转换创建的新数据流都可被其他组件所使用，而不会对最初的数据流产生影响。

　优势和局限

乍看之下，Samza对Kafka类查询系统的依赖似乎是一种限制，然而这也可以为系统提供一些独特的保证和功能，这些内容也是其他流处理系统不具备的。

例如Kafka已经提供了可以通过低延迟方式访问的数据存储副本，此外还可以为每个数据分区提供非常易用且低成本的多订阅者模型。所有输出内容，包括中间态的结果都可写入到Kafka，并可被下游步骤独立使用。

这种对Kafka的紧密依赖在很多方面类似于MapReduce引擎对HDFS的依赖。虽然在批处理的每个计算之间对HDFS的依赖导致了一些严重的性能问题，但也避免了流处理遇到的很多其他问题。

Samza与Kafka之间紧密的关系使得处理步骤本身可以非常松散地耦合在一起。无需事先协调，即可在输出的任何步骤中增加任意数量的订阅者，对于有多个团队需要访问类似数据的组织，这一特性非常有用。多个团队可以全部订阅进入系统的数据话题，或任意订阅其他团队对数据进行过某些处理后创建的话题。这一切并不会对数据库等负载密集型基础架构造成额外的压力。

直接写入Kafka还可避免回压（Backpressure）问题。回压是指当负载峰值导致数据流入速度超过组件实时处理能力的情况，这种情况可能导致处理工作停顿并可能丢失数据。按照设计，Kafka可以将数据保存很长时间，这意味着组件可以在方便的时候继续进行处理，并可直接重启动而无需担心造成任何后果。

Samza可以使用以本地键值存储方式实现的容错检查点系统存储数据。这样Samza即可获得“至少一次”的交付保障，但面对由于数据可能多次交付造成的失败，该技术无法对汇总后状态（例如计数）提供精确恢复。

Samza提供的高级抽象使其在很多方面比Storm等系统提供的基元（Primitive）更易于配合使用。目前Samza只支持JVM语言，这意味着它在语言支持方面不如Storm灵活。

总结

对于已经具备或易于实现Hadoop和Kafka的环境，Apache Samza是流处理工作负载一个很好的选择。Samza本身很适合有多个团队需要使用（但相互之间并不一定紧密协调）不同处理阶段的多个数据流的组织。Samza可大幅简化很多流处理工作，可实现低延迟的性能。如果部署需求与当前系统不兼容，也许并不适合使用，但如果需要极低延迟的处理，或对严格的一次处理语义有较高需求，此时依然适合考虑。

　混合处理系统：批处理和流处理

一些处理框架可同时处理批处理和流处理工作负载。这些框架可以用相同或相关的组件和API处理两种类型的数据，借此让不同的处理需求得以简化。

如你所见，这一特性主要是由Spark和Flink实现的，下文将介绍这两种框架。实现这样的功能重点在于两种不同处理模式如何进行统一，以及要对固定和不固定数据集之间的关系进行何种假设。

虽然侧重于某一种处理类型的项目会更好地满足具体用例的要求，但混合框架意在提供一种数据处理的通用解决方案。这种框架不仅可以提供处理数据所需的方法，而且提供了自己的集成项、库、工具，可胜任图形分析、机器学习、交互式查询等多种任务。

　Apache Spark

Apache Spark是一种包含流处理能力的下一代批处理框架。与Hadoop的MapReduce引擎基于各种相同原则开发而来的Spark主要侧重于通过完善的内存计算和处理优化机制加快批处理工作负载的运行速度。

Spark可作为独立集群部署（需要相应存储层的配合），或可与Hadoop集成并取代MapReduce引擎。

批处理模式

与MapReduce不同，Spark的数据处理工作全部在内存中进行，只在一开始将数据读入内存，以及将最终结果持久存储时需要与存储层交互。所有中间态的处理结果均存储在内存中。

虽然内存中处理方式可大幅改善性能，Spark在处理与磁盘有关的任务时速度也有很大提升，因为通过提前对整个任务集进行分析可以实现更完善的整体式优化。为此Spark可创建代表所需执行的全部操作，需要操作的数据，以及操作和数据之间关系的Directed Acyclic Graph（有向无环图），即DAG，借此处理器可以对任务进行更智能的协调。

为了实现内存中批计算，Spark会使用一种名为Resilient Distributed Dataset（弹性分布式数据集），即RDD的模型来处理数据。这是一种代表数据集，只位于内存中，永恒不变的结构。针对RDD执行的操作可生成新的RDD。每个RDD可通过世系（Lineage）回溯至父级RDD，并最终回溯至磁盘上的数据。Spark可通过RDD在无需将每个操作的结果写回磁盘的前提下实现容错。

流处理模式

流处理能力是由Spark Streaming实现的。Spark本身在设计上主要面向批处理工作负载，为了弥补引擎设计和流处理工作负载特征方面的差异，Spark实现了一种叫做微批（Micro-batch）*的概念。在具体策略方面该技术可以将数据流视作一系列非常小的“批”，借此即可通过批处理引擎的原生语义进行处理。

Spark Streaming会以亚秒级增量对流进行缓冲，随后这些缓冲会作为小规模的固定数据集进行批处理。这种方式的实际效果非常好，但相比真正的流处理框架在性能方面依然存在不足。

　优势和局限

使用Spark而非Hadoop MapReduce的主要原因是速度。在内存计算策略和先进的DAG调度等机制的帮助下，Spark可以用更快速度处理相同的数据集。

Spark的另一个重要优势在于多样性。该产品可作为独立集群部署，或与现有Hadoop集群集成。该产品可运行批处理和流处理，运行一个集群即可处理不同类型的任务。

除了引擎自身的能力外，围绕Spark还建立了包含各种库的生态系统，可为机器学习、交互式查询等任务提供更好的支持。相比MapReduce，Spark任务更是“众所周知”地易于编写，因此可大幅提高生产力。

为流处理系统采用批处理的方法，需要对进入系统的数据进行缓冲。缓冲机制使得该技术可以处理非常大量的传入数据，提高整体吞吐率，但等待缓冲区清空也会导致延迟增高。这意味着Spark Streaming可能不适合处理对延迟有较高要求的工作负载。

由于内存通常比磁盘空间更贵，因此相比基于磁盘的系统，Spark成本更高。然而处理速度的提升意味着可以更快速完成任务，在需要按照小时数为资源付费的环境中，这一特性通常可以抵消增加的成本。

Spark内存计算这一设计的另一个后果是，如果部署在共享的集群中可能会遇到资源不足的问题。相比Hadoop MapReduce，Spark的资源消耗更大，可能会对需要在同一时间使用集群的其他任务产生影响。从本质来看，Spark更不适合与Hadoop堆栈的其他组件共存一处。

　总结

Spark是多样化工作负载处理任务的最佳选择。Spark批处理能力以更高内存占用为代价提供了无与伦比的速度优势。对于重视吞吐率而非延迟的工作负载，则比较适合使用Spark Streaming作为流处理解决方案。

Apache Flink

Apache Flink是一种可以处理批处理任务的流处理框架。该技术可将批处理数据视作具备有限边界的数据流，借此将批处理任务作为流处理的子集加以处理。为所有处理任务采取流处理为先的方法会产生一系列有趣的副作用。

这种流处理为先的方法也叫做Kappa架构，与之相对的是更加被广为人知的Lambda架构（该架构中使用批处理作为主要处理方法，使用流作为补充并提供早期未经提炼的结果）。Kappa架构中会对一切进行流处理，借此对模型进行简化，而这一切是在最近流处理引擎逐渐成熟后才可行的。

　流处理模型

Flink的流处理模型在处理传入数据时会将每一项视作真正的数据流。Flink提供的DataStream API可用于处理无尽的数据流。Flink可配合使用的基本组件包括：

Stream（流）是指在系统中流转的，永恒不变的无边界数据集

Operator（操作方）是指针对数据流执行操作以产生其他数据流的功能

Source（源）是指数据流进入系统的入口点

Sink（槽）是指数据流离开Flink系统后进入到的位置，槽可以是数据库或到其他系统的连接器

为了在计算过程中遇到问题后能够恢复，流处理任务会在预定时间点创建快照。为了实现状态存储，Flink可配合多种状态后端系统使用，具体取决于所需实现的复杂度和持久性级别。

此外Flink的流处理能力还可以理解“事件时间”这一概念，这是指事件实际发生的时间，此外该功能还可以处理会话。这意味着可以通过某种有趣的方式确保执行顺序和分组。

　批处理模型

Flink的批处理模型在很大程度上仅仅是对流处理模型的扩展。此时模型不再从持续流中读取数据，而是从持久存储中以流的形式读取有边界的数据集。Flink会对这些处理模型使用完全相同的运行时。

Flink可以对批处理工作负载实现一定的优化。例如由于批处理操作可通过持久存储加以支持，Flink可以不对批处理工作负载创建快照。数据依然可以恢复，但常规处理操作可以执行得更快。

另一个优化是对批处理任务进行分解，这样即可在需要的时候调用不同阶段和组件。借此Flink可以与集群的其他用户更好地共存。对任务提前进行分析使得Flink可以查看需要执行的所有操作、数据集的大小，以及下游需要执行的操作步骤，借此实现进一步的优化。

优势和局限

Flink目前是处理框架领域一个独特的技术。虽然Spark也可以执行批处理和流处理，但Spark的流处理采取的微批架构使其无法适用于很多用例。Flink流处理为先的方法可提供低延迟，高吞吐率，近乎逐项处理的能力。

Flink的很多组件是自行管理的。虽然这种做法较为罕见，但出于性能方面的原因，该技术可自行管理内存，无需依赖原生的Java垃圾回收机制。与Spark不同，待处理数据的特征发生变化后Flink无需手工优化和调整，并且该技术也可以自行处理数据分区和自动缓存等操作。

Flink会通过多种方式对工作进行分许进而优化任务。这种分析在部分程度上类似于SQL查询规划器对关系型数据库所做的优化，可针对特定任务确定最高效的实现方法。该技术还支持多阶段并行执行，同时可将受阻任务的数据集合在一起。对于迭代式任务，出于性能方面的考虑，Flink会尝试在存储数据的节点上执行相应的计算任务。此外还可进行“增量迭代”，或仅对数据中有改动的部分进行迭代。

在用户工具方面，Flink提供了基于Web的调度视图，借此可轻松管理任务并查看系统状态。用户也可以查看已提交任务的优化方案，借此了解任务最终是如何在集群中实现的。对于分析类任务，Flink提供了类似SQL的查询，图形化处理，以及机器学习库，此外还支持内存计算。

Flink能很好地与其他组件配合使用。如果配合Hadoop 堆栈使用，该技术可以很好地融入整个环境，在任何时候都只占用必要的资源。该技术可轻松地与YARN、HDFS和Kafka 集成。在兼容包的帮助下，Flink还可以运行为其他处理框架，例如Hadoop和Storm编写的任务。

目前Flink最大的局限之一在于这依然是一个非常“年幼”的项目。现实环境中该项目的大规模部署尚不如其他处理框架那么常见，对于Flink在缩放能力方面的局限目前也没有较为深入的研究。随着快速开发周期的推进和兼容包等功能的完善，当越来越多的组织开始尝试时，可能会出现越来越多的Flink部署。

总结

Flink提供了低延迟流处理，同时可支持传统的批处理任务。Flink也许最适合有极高流处理需求，并有少量批处理任务的组织。该技术可兼容原生Storm和Hadoop程序，可在YARN管理的集群上运行，因此可以很方便地进行评估。快速进展的开发工作使其值得被大家关注。

结论

大数据系统可使用多种处理技术。

对于仅需要批处理的工作负载，如果对时间不敏感，比其他解决方案实现成本更低的Hadoop将会是一个好选择。

对于仅需要流处理的工作负载，Storm可支持更广泛的语言并实现极低延迟的处理，但默认配置可能产生重复结果并且无法保证顺序。Samza与YARN和Kafka紧密集成可提供更大灵活性，更易用的多团队使用，以及更简单的复制和状态管理。

对于混合型工作负载，Spark可提供高速批处理和微批处理模式的流处理。该技术的支持更完善，具备各种集成库和工具，可实现灵活的集成。Flink提供了真正的流处理并具备批处理能力，通过深度优化可运行针对其他平台编写的任务，提供低延迟的处理，但实际应用方面还为时过早。

最适合的解决方案主要取决于待处理数据的状态，对处理所需时间的需求，以及希望得到的结果。具体是使用全功能解决方案或主要侧重于某种项目的解决方案，这个问题需要慎重权衡。随着逐渐成熟并被广泛接受，在评估任何新出现的创新型解决方案时都需要考虑类似的问题。

SQL经典练习题面试题集

表（MYSQL）

Student(sid,Sname,Sage,Ssex) 学生表

CREATE TABLE student (

sid varchar(10) NOT NULL,

sName varchar(20) DEFAULT NULL,

sAge datetime DEFAULT ‘1980-10-12 23:12:36’,

sSex varchar(10) DEFAULT NULL,

PRIMARY KEY (sid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Course(cid,Cname,tid) 课程表

CREATE TABLE course (

cid varchar(10) NOT NULL,

cName varchar(10) DEFAULT NULL,

tid int(20) DEFAULT NULL,

PRIMARY KEY (cid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SC(sid,cid,score) 成绩表

CREATE TABLE sc (

sid varchar(10) DEFAULT NULL,

cid varchar(10) DEFAULT NULL,

score int(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Teacher(tid,Tname) 教师表
CREATE TABLE taacher (

tid int(10) DEFAULT NULL,

tName varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据：（MySQL）

insert into taacher(tid,tName) values (1,’李老师’),(2,’何以琛’),(3,’叶平’);

insert into student(sid,sName,sAge,sSex) values (‘1001′,’张三丰’,’1980-10-12 23:12:36′,’男’),(‘1002′,’张无极’,’1995-10-12 23:12:36′,’男’),(‘1003′,’李奎’,’1992-10-12 23:12:36′,’女’),(‘1004′,’李元宝’,’1980-10-12 23:12:36′,’女’),(‘1005′,’李世明’,’1981-10-12 23:12:36′,’男’),(‘1006′,’赵六’,’1986-10-12 23:12:36′,’男’),(‘1007′,’田七’,’1981-10-12 23:12:36′,’女’);

insert into sc(sid,cid,score) values (‘1′,’001’,80),(‘1′,’002’,60),(‘1′,’003’,75),(‘2′,’001’,85),(‘2′,’002’,70),(‘3′,’004’,100),(‘3′,’001’,90),(‘3′,’002’,55),(‘4′,’002’,65),(‘4′,’003’,60);

insert into course(cid,cName,tid) values (‘001′,’企业管理’,3),(‘002′,’马克思’,3),(‘003′,’UML’,2),(‘004′,’数据库’,1),(‘005′,’英语’,1);

ORACLE(表+数据)

CREATE TABLE student (

sid varchar2(10) NOT NULL,

sName varchar2(20) DEFAULT NULL,

sAge date ,

sSex varchar2(10) DEFAULT NULL,

PRIMARY KEY (sid)

)

CREATE TABLE course (

cid varchar2(10) NOT NULL,

cName varchar2(10) DEFAULT NULL,

tid number(20) DEFAULT NULL,

PRIMARY KEY (cid)

)

CREATE TABLE sc (

sid varchar2(10) DEFAULT NULL,

cid varchar2(10) DEFAULT NULL,

score number(10) DEFAULT NULL

)

CREATE TABLE teacher (

tid number(10) DEFAULT NULL,

tName varchar2(10) DEFAULT NULL

)

insert into course(cid,cName,tid) values (‘001′,’企业管理’,3);

insert into course(cid,cName,tid) values (‘002′,’马克思’,3);

insert into course(cid,cName,tid) values (‘004′,’数据库’,1);

insert into course(cid,cName,tid) values (‘005′,’英语’,1);

insert into sc(sid,cid,score) values (‘1001′,’001’,80);

insert into sc(sid,cid,score) values (‘1001′,’002’,60);

insert into sc(sid,cid,score) values (‘1001′,’003’,70);

insert into sc(sid,cid,score) values (‘1002′,’001’,85);

insert into sc(sid,cid,score) values (‘1002′,’002’,70);

insert into sc(sid,cid,score) values (‘1003′,’004’,90);

insert into sc(sid,cid,score) values (‘1003′,’001’,90);

insert into sc(sid,cid,score) values (‘1003′,’002’,99);

insert into sc(sid,cid,score) values (‘1004′,’002’,65);

insert into sc(sid,cid,score) values (‘1004′,’003’,50);

insert into sc(sid,cid,score) values (‘1005′,’005’,80);

insert into sc(sid,cid,score) values (‘1005′,’004’,70);

insert into sc(sid,cid,score) values (‘1003′,’003’,10);

insert into sc(sid,cid,score) values (‘1003′,’005’,10);

insert into student(sid,sName,sAge,sSex) values (‘1001′,’张三丰’,to_date(‘1980-10-12 23:12:36′,’YYYY-MM-DD HH24:MI:SS’),’男’);

insert into student(sid,sName,sAge,sSex) values (‘1002′,’张无极’,to_date(‘1995-10-12 23:12:36′,’YYYY-MM-DD HH24:MI:SS’),’男’);

insert into student(sid,sName,sAge,sSex) values (‘1003′,’李奎’,to_date(‘1992-10-12 23:12:36′,’YYYY-MM-DD HH24:MI:SS’),’女’);

insert into student(sid,sName,sAge,sSex) values (‘1004′,’李元宝’,to_date(‘1980-10-12 23:12:36′,’YYYY-MM-DD HH24:MI:SS’),’女’);

insert into student(sid,sName,sAge,sSex) values (‘1005′,’李世明’,to_date(‘1981-10-12 23:12:36′,’YYYY-MM-DD HH24:MI:SS’),’男’);

insert into student(sid,sName,sAge,sSex) values (‘1006′,’赵六’,to_date(‘1986-10-12 23:12:36′,’YYYY-MM-DD HH24:MI:SS’),’男’);

insert into student(sid,sName,sAge,sSex) values (‘1007′,’田七’,to_date(‘1981-10-12 23:12:36′,’YYYY-MM-DD HH24:MI:SS’),’女’);

insert into teacher(tid,tName) values (1,’李老师’);

insert into teacher(tid,tName) values (2,’何以琛’);

insert into teacher(tid,tName) values (3,’叶平’);

问题：

1.查询“001”课程比“002”课程成绩高的所有学生的学号;
select a.sid from (select sid,score from SC where cid=’001′) a,(select sid,score
from SC where cid=’002′) b
where a.score>b.score and a.sid=b.sid;
2、查询平均成绩大于60分的同学的学号和平均成绩;
select sid,avg(score)
from sc
group by sid having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩;
select Student.sid,Student.Sname,count(SC.cid),sum(score)
from Student left Outer join SC on Student.sid=SC.sid
group by Student.sid,Sname
4、查询姓“李”的老师的个数;
select count(distinct(Tname))
from Teacher
where Tname like ‘李%’;
5、查询没学过“叶平”老师课的同学的学号、姓名;
select Student.sid,Student.Sname
from Student
where sid not in (select distinct( SC.sid) from SC,Course,Teacher where SC.cid=Course.cid and Teacher.tid=Course.tid and Teacher.Tname=’叶平’);
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
A:select Student.sid,Student.Sname from Student,SC where Student.sid=SC.sid and SC.cid=’001’and exists( Select * from SC as SC_2 where SC_2.sid=SC.sid and SC_2.cid=’002′);

B:SELECT s.sid,s.sName

FROM student s, (SELECT sid,COUNT(cid) FROM sc WHERE cid IN (‘001′,’002′) GROUP BY sid HAVING COUNT(cid)>=2) t WHERE s.sid = t.sid
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
select sid,Sname
from Student
where sid in (select sid from SC ,Course ,Teacher where SC.cid=Course.cid and Teacher.tid=Course.tid and Teacher.Tname=’叶平’ group by sid having count(SC.cid)=(select count(cid) from Course,Teacher where Teacher.tid=Course.tid and Tname=’叶平’));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
1>Select sid,Sname from (select Student.sid,Student.Sname,score ,(select score from SC SC_2 where SC_2.sid=Student.sid and SC_2.cid=’002′) score2
from Student,SC where Student.sid=SC.sid and cid=’001′) S_2 where score2 <score;

2>SELECT s.sid,s.sName FROM student s,

(SELECT sid,score FROM sc WHERE cid = ‘001’) sc_1,

(SELECT sid,score FROM sc WHERE cid = ‘002’) sc_2

WHERE sc_1.sid = sc_2.sid AND s.sid = sc_2.sid AND sc_2.score < sc_1.score
9、查询所有课程成绩小于60分的同学的学号、姓名;
select sid,Sname
from Student
where sid not in (select Student.sid from Student,SC where S.sid=SC.sid and score>60);
10、查询没有学全所有课的同学的学号、姓名;
1>

select Student.sid,Student.Sname
from Student,SC
where Student.sid=SC.sid group by Student.sid,Student.Sname having count(cid) <(select count(cid) from Course);

SELECT s.sid,s.sname FROM student s,

(SELECT sid,COUNT(cid) FROM sc GROUP BY sid HAVING COUNT(cid) < (SELECT COUNT(cid) FROM course) )t

WHERE s.sid = t.sid
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
select sid,Sname from Student,SC where Student.sid=SC.sid and cid in (select cid from SC where sid=’1001′);
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
UPDATE sc,(SELECT c.cid,AVG(score) avgs FROM sc,course c,teacher t WHERE sc.cid = c.cid AND

c.tid = t.tid AND t.tName = ‘叶平’ GROUP BY c.cid)sc_2 SET sc.score = sc_2.avgs WHERE sc.cid = sc_2.cid

14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
select sid from SC where cid in (select cid from SC where sid=’1002′)
group by sid having count(*)=(select count(*) from SC where sid=’1002′);

15、删除学习“叶平”老师课的SC表记录;
DELETE FROM sc WHERE sc.cid IN (SELECT sc.cid FROM course c ,teacher t WHERE sc.cid = c.cid AND c.tid = t.tid AND t.tName = ‘叶平’)

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
SELECT sid as 学生ID
,(SELECT score FROM SC WHERE SC.sid=t.sid AND cid=’004′) AS 数据库
,(SELECT score FROM SC WHERE SC.sid=t.sid AND cid=’001′) AS 企业管理
,(SELECT score FROM SC WHERE SC.sid=t.sid AND cid=’005′) AS 英语
,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
FROM SC AS t
GROUP BY sid
ORDER BY avg(t.score)
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cid “课程ID”,max(score) “最高分”,min(score) “最低分” from sc group by cid

按各科平均成绩从低到高和及格率的百分数从高到低排序
oracle>

SELECT t.cid AS 课程号,MAX(course.Cname)AS 课程名,nvl(AVG(score),0) AS 平均成绩

,100 * SUM(CASE WHEN nvl(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数

FROM SC T,Course

WHERE t.cid=course.cid

GROUP BY t.cid

ORDER BY 100 * SUM(CASE WHEN nvl(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

Mysql>

SELECT t.cid AS 课程号,MAX(course.Cname)AS 课程名,IFNULL(AVG(score),0) AS 平均成绩

,100 * SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数

FROM SC T,Course

WHERE t.cid=course.cid

GROUP BY t.cid

ORDER BY 100 * SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC

20、查询如下课程平均成绩和及格率的百分数(用”1行”显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）
SELECT SUM(CASE WHEN cid =’001′ THEN score ELSE 0 END)/SUM(CASE cid WHEN ‘001’ THEN 1 ELSE 0 END) AS 企业管理平均分
,100 * SUM(CASE WHEN cid = ‘001’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = ‘001’ THEN 1 ELSE 0 END) AS 企业管理及格百分数
,SUM(CASE WHEN cid = ‘002’ THEN score ELSE 0 END)/SUM(CASE cid WHEN ‘002’ THEN 1 ELSE 0 END) AS 马克思平均分
,100 * SUM(CASE WHEN cid = ‘002’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = ‘002’ THEN 1 ELSE 0 END) AS 马克思及格百分数
,SUM(CASE WHEN cid = ‘003’ THEN score ELSE 0 END)/SUM(CASE cid WHEN ‘003’ THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN cid = ‘003’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = ‘003’ THEN 1 ELSE 0 END) AS UML及格百分数
,SUM(CASE WHEN cid = ‘004’ THEN score ELSE 0 END)/SUM(CASE cid WHEN ‘004’ THEN 1 ELSE 0 END) AS 数据库平均分
,100 * SUM(CASE WHEN cid = ‘004’ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = ‘004’ THEN 1 ELSE 0 END) AS 数据库及格百分数
FROM SC
21、查询不同老师所教不同课程平均分从高到低显示
SELECT MAX(t.tid) “教师ID”,MAX(t.tName) “教师姓名”,c.cid “课程ID”, MAX(c.cName) “课程名称” ,AVG(sc.score) “平均成绩”

FROM sc,course c,teacher t WHERE sc.cid = c.cid AND c.tid = t.tid GROUP BY c.tid,c.cid

ORDER BY AVG(sc.score) DESC

统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT SC.cid as 课程ID, Cname as 课程名称
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 – 85]
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 – 70]
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 – 60]
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM SC,Course
where SC.cid=Course.cid
GROUP BY SC.cid,Cname;

26、查询每门课程被选修的学生数
select cid,count(sid) from sc group by cid;
27、查询出只选修了一门课程的全部学生的学号和姓名
select SC.sid,Student.Sname,count(cid) AS 选课数
from SC ,Student
where SC.sid=Student.sid group by SC.sid ,Student.Sname having count(cid)=1;
28、查询男生、女生人数
Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex=’男’;
Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex=’女’;
29、查询姓“张”的学生名单
SELECT Sname FROM Student WHERE Sname like ‘张%’;
30、查询同名同性学生名单，并统计同名人数
SELECT sName,sSex ,COUNT(*) FROM student GROUP BY sName,sSex HAVING COUNT(*) > 1
31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

Mysql>
select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age
from student
where CONVERT(char(11),DATEPART(year,Sage))=’1981′;

Oracle>

select * from student where substr(to_char(sage,’yyyy-MM-dd’),1,4)= ‘1981’
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
Select cid,Avg(score) from SC group by cid order by Avg(score),cid DESC ;
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select Sname,SC.sid ,avg(score)
from Student,SC
where Student.sid=SC.sid group by SC.sid,Sname having avg(score)>85;
34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
Select Sname,isnull(score,0)
from Student,SC,Course
where SC.sid=Student.sid and SC.cid=Course.cid and Course.Cname=’数据库’and score <60;
35、查询所有学生的选课情况;
SELECT SC.sid,SC.cid,Sname,Cname
FROM SC,Student,Course
where SC.sid=Student.sid and SC.cid=Course.cid ;
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
SELECT distinct student.sid,student.Sname,SC.cid,SC.score
FROM student,Sc
WHERE SC.score>=70 AND SC.sid=student.sid;
37、查询不及格的课程，并按课程号从大到小排列
select cid from sc where scor e <60 order by cid ;
38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
select SC.sid,Student.Sname from SC,Student where SC.sid=Student.sid and Score>80 and cid=’003′;
39、求选了课程的学生人数
select count(*) from sc;
40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select Student.Sname,score
from Student,SC,Course C,Teacher
where Student.sid=SC.sid and SC.cid=C.cid and C.tid=Teacher.tid and Teacher.Tname=’叶平’ and SC.score=(select max(score)from SC where cid=C.cid );
41、查询各个课程及相应的选修人数
select count(*) from sc group by cid;
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select distinct A.sid,B.score from SC A ,SC B where A.Score=B.Score and A.cid <>B.cid ;
43、查询每门功课成绩最好的前两名
SELECT *

FROM sc t1

WHERE (

SELECT COUNT(*)

FROM sc t2

WHERE t1.cid=t2.cid

AND t2.score>=t1.score

) <=2 ORDER BY t1.cid

44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT cid “课程号”,COUNT(*) “选修人数” FROM sc GROUP BY cid HAVING COUNT(*) >10 ORDER BY COUNT(*) DESC,cid

45、检索至少选修两门课程的学生学号
select sid
from sc
group by sid
having count(*) > = 2
46、查询全部学生都选修的课程的课程号和课程名
SELECT s.sName,c.cName, COUNT(*) FROM student s,course c, sc WHERE s.sid = sc.sid AND sc.cid = c.cid GROUP BY sc.cid HAVING COUNT(*) = (SELECT COUNT(*) FROM student)
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
SELECT DISTINCT Sname FROM Student WHERE sid NOT IN (SELECT sid FROM Course,Teacher,SC WHERE Course.tid=Teacher.tid AND SC.cid=course.cid AND Tname=’叶平’);
48、查询两门以上不及格课程的同学的学号及其平均成绩
select sid,avg(ifnull(score,0)) from SC where sid in (select sid from SC where score <60 group by sid having count(*)>2)group by sid;
49、检索“004”课程分数小于60，按分数降序排列的同学学号
select sid from SC where cid=’004’and score <60 order by score desc;
50、删除“1002”同学的“001”课程的成绩
delete from Sc where sid=’1002′ and cid=’001′;

月度归档： 2020年1月

SQLServer面试题总结

一、Sql常用语法

二、sql技巧

三、（MS SQL Server）SQL语句导入导出大全

四、sql常见面试题

五、Sql常见题目

六、临时表

七、视图

运维必会的MySQL企业面试题及答案（开发也可以看看）

使用Retrofit + RxJava 实现优雅的处理服务器返回异常、错误

MySQL初级考试试面试题及答案

基于Netty高并发物联网服务器研究与设计

大数据框架Hadoop、Storm、Samza、Spark和Flink对比分析

SQL经典练习题面试题集

表（MYSQL）

数据：（MySQL）

ORACLE(表+数据)

问题：

SpringBoot里MyBatis打印SQL语句到控制台的方法总结