（7.2.1）SQL之基础笔试题

1-写出复制表,拷贝表和四表联查的SQL语句。

答：复制表(只复制结构，源表名：A,新表名：B): select * into B from A where 1=0   拷贝表(拷贝结构，源表名：A,新表名：B):select * into B from A四表联查：select * from A,B,C,D where 关联条件

2-每一个部门的每个工种的工资最大值？

 select dept_id,job_cat,max(salary)from employee group by dept_id,job_cat;  

3-如果要从字符串“Hello world”中提取出“ello world”这样的结果

SQL语句： select lower(trim(‘H’from ‘Hello world’))from dual;

4-找到ppp表里面num最小的数，不能用min函数：

select top 1 num from ppp order by num;

5-选择表ppp2中num重复的记录。

select * from ppp2 where num in(select num from ppp2 group by num having(count(num)>1))。

6-在命令窗口执行SQL命令时，若命令要占用多行，续行符是D__。

A、冒号(:) B、分号(;) C、逗号(,) D、连字符(-)

7-SQL 创建临时表

create table #a(id int,name varchar(50))insert into #a(id,name) values(1,'123')select * from #adrop table #a

8-如果ID连续，查询31到40条记录的各种方法

select * from A where ID between 31 and 40

9-如果ID不连续，查询31到40条记录的各种方法

--两次对表A查询效率较低select top 10 * from A where ID not in (select top 30 ID from A)--外层查询没有对表A查询，效率大有提高select top 10 * from (select top 40 * from A) as t order by t.ID desc--ROW_NUMBER()函数效率更高，sqlserver2005以及以上版本中才可以使用select * from (select ROW_NUMBER() over(order by ID) as 'sequence',A.*  from A ) as t where t.sequence between 31 and 40

10-一个表a,里面有20条数据,这10条数据都是相同的，现在要删除其中的9条

（1）利用临时表+清空指令truncate select top 1 into #T from agotruncate table agoinsert a select * from t（2）重命名表+重命名指令exec sp_renameselect top 1 into tb_tmp from  agodrop table  agoexec sp_rename  ‘tb_tmp’,‘a’（3）循环declare @i intset @i=1while（@i<9）begin  delete from table A where id=@i  set @i=@i+1end

11-truncate 、drop、delete的区别

相同点：
1. truncate和不带where子句的delete、以及drop都会删除表内的数据。
2. drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

不同点：
1. truncate 和 delete 只删除数据不删除表的结构(定义)
drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
2. delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。
truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。
3.delete 语句不影响表所占用的 extent，高水线(high watermark)保持原位置不动
drop 语句将表所占用的空间全部释放。
truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage；truncate 会将高水线复位(回到最开始)。
4.速度，一般来说: drop> truncate > delete
5.安全性：小心使用 drop 和 truncate，尤其没有备份的时候.否则哭都来不及
使用上,想删除部分数据行用 delete，注意带上where子句. 回滚段要足够大.
想删除表,当然用 drop
想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。
如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。
6.delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。
7、TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
8、TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
9、对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
10、TRUNCATE TABLE 不能用于参与了索引视图的表。

12-更改数据库名字

sp_renamedb[@dbname=]  '旧数据库名字'            [@newname=]  ‘新数据库名字’

13 数据库出现数据不一致的根本原因是数据冗余

14、牛客网

• 下列哪一个不属于关系数据库的特点？

  • 数据冗余度小
  • 数据独立性高
  • 数据共享性好
  • 多用户访问

• 下列叙述正确的是：

  • 数据库是一个独立的系统,不需要操作系统的支持
  • 数据库设计是指设计数据库管理
  • 数据库技术的根本目标是要解决数据共享的问题
  • 数据库系统中,数据的物理结构必须与逻辑结构一致

• 关于数据完整性，以下说法正确的是?

  • 引用完整性通过主键和外键之间的引用关系实现
  • 引用完整性通过限制数据类型、检查约束等实现
  • 数据完整性是通过数据操纵者自身对数据的控制来实现的
  • 如果两个表中存储的信息相互关联，那么只要修改了一个表，另外一个表也要做出相应的修改，则称该这两个表中的数据具备完整性

• 一个关系模式为Y（X1，X2，X3，X4），假定该关系存在着如下函数依赖：（X1，X2）→X3，X2→X4，则该关系属于（ ）

  • 第一范式
  • 第二范式
  • 第三范式
  • 第四范式
    此关系模式的候选键为{X1,X2}，因为X2→X4,有非主属性X4部分依赖于候选键{X1,X2}，所以这个关系模式不为第二范式；又因为范式之间的关系满足1NF⊇2NF⊇3NF ⊇ BCNF。

• 下面有关sql绑定变量的描述，说法错误的是？

  • 绑定变量是指在SQL语句中使用变量，改变变量的值来改变SQL语句的执行结果
  • 使用绑定变量，可以减少SQL语句的解析，能减少数据库引擎消耗在SQL语句解析上的资源
  • 使用绑定变量，提高了编程效率和可靠性，减少访问数据库的次数
  • 使用绑定变量，查询优化器会预估的比字面变量更加真实
    绑定变量是为了减少解析的，比如你有个语句这样
    select aaa,bbb from ccc where ddd=eee;
    如果经常通过改变eee这个谓词赋值来查询，像如下
    select aaa,bbb from ccc where ddd=fff;
    select aaa,bbb from ccc where ddd=ggg;
    select aaa,bbb from ccc where ddd=hhh;
    每条语句都要被数据库解析一次，这样比较浪费资源，如果把eee换成“:1”这样的绑定变量形式，无论ddd后面是什么值，都不需要重复解析

• 已知表tbl中字段land_ID建有索引，字段cust_id建有唯一索引，下列语句查询逻辑相同，其中执行效率最优的是

  • SELECT * FROM tbl WHERE land_id > 750 or (cust_id=180 or cust_id=560)
  • SELECT * FROM tbl WHERE (cust_id=180 or cust_id=560) or land_id > 750
  • SELECT * FROM tbl WHERE land_id > 750
    UNION
    SELECT * FROM tbl WHERE cust_id = 180
    UNION
    SELECT * FROM tbl WHERE cust_id = 560
    
  • SELECT * FROM tbl WHERE land_id > 750
    UNION
    ( SELECT * FROM tbl WHERE cust_id = 180
    UNION ALL
    SELECT * FROM tbl WHERE cust_id = 560
    )
    1、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
    2、 union 与union all（效率高）。Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序； Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

• 某查询语句运行后返回的结果集为：
  1班 72
  2班 75
  3班 NULL
  则最有可能的查询语句是以下：

  • SELECT AVG(score) FROM test WHERE class<3
  • SELECT AVG(score) FROM test WHERE class<3 GROUP BY class
  • SELECT AVG(score) FROM test WHERE class<3 GROUP BY ALL class
  • SELECT AVG(score) FROM test GROUP BY class HAVING class<3
    Group by与group by all
    因为出现了3班 NULL
    条件限制了选择的class<3 所以只有1 ，2班有成绩。下面是帮助中group by +all的用法
    Transact-SQL 在 GROUP BY 子句中提供 ALL 关键字。只有在 SELECT 语句还包括 WHERE 子句时，ALL 关键字才有意义。
    如果使用 ALL 关键字，那么查询结果将包括由 GROUP BY 子句产生的所有组，即使某些组没有符合搜索条件的行。没有 ALL 关键字，包含 GROUP BY 子句的 SELECT 语句将不显示没有符合条件的行的组。

• 如下SQL语句中，__可能返回null值。

  • (1) select count(*) from t1;
  • (2) select max(col1) from t1;
  • (3) select concat(‘max=’,max(col1)) from t1;
  • (1)可能，(2)和(3)不可能
  • (2)可能，(1)和(3)不可能
  • (2)可能，(1)和(3)不可能
  • (1)不可能，(2)和(3)可能
  • 都不可能
  • 都可能
    (1)返回的是表的行数，如果没有记录，应该返回0，不会出现NULL，
    (2) 和（3） 正常情况下不会出现NULL，但是如果表里面没有记录，则会出现NULL
    MySQL concat函数使用方法：CONCAT(str1,str2,…)
    返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

• 在视图上不能完成的操作是（）

  • 更新视图
  • 查询
  • 在视图上定义新的表
  • 在视图上定义新的视图
    视图是基于数据表的一种查询窗口，不能在这种查询窗口中再建数据表。

• 下列关于视图与基本表的对比正确的是()

  • 视图的定义功能强于基本表
  • 视图的操作功能强于基本表
  • 视图的数据控制功能弱于基本表
  • 上面提到的三种功能二者均相当

• 如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素，如何做以尽量减小对应用的影响?

  • 增大sort_area_size（8i）/pga_aggregate_target（Arrayi）值
  • 如果表有分区（一般大表都要用到分区的），按分区逐个建索引，如果是本地索引的话
  • 系统空闲的时候建。
  • 把日志文件放到另一个地方

• 聚集索引的描述，说法正确的是？

  • 有存储实际数据
  • 没有存储实际数据
  • 物理上连续
  • 逻辑上连续
  • 可以用B树实现
  • 可以用二叉排序树实现
    聚集索引是一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。
    聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。
    聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。
    当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法，是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。

• 存储过程是存储在数据库中的代码，具有很多优点。下列陈述中不属于存储过程优点的是__。

  • 可通过预编译机制提高数据操作的性能
  • 可方便地按用户视图表达数据
  • 可减少客户端和服务器端的网络流量
  • 可实现一定的安全控制

• 关于ACID下面说法正确的是?

  • A是可用性。整个操作中的所有动作是保证高可用性，系统必须提供要求的稳定性，以保证事务的提交。
  • C一致性。在事务开始之前和结束后，数据库的约束保持不变。
  • I隔离性。两个同时运行的事务的执行是互不影响，中间结果不可交叉访问。
  • D持久性。在事务提交以后，该事务所作的更改持久保存在存储介质之中，不会被回滚。
    A是原子性
    C 能不能互相访问要看隔离等级。
    D 可能会回滚

• 下面有关数据ACID性质说法正确的是？

  • 原子性是指事务中包含的所有操作要么都做，要么都不做，保证数据库是一致的。
  • 一致性是指数据库在事务操作前和事务处理后，其中的数据必须都满足业务规则约束。
  • 隔离性是数据库允许多个并发事务同时对齐数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
  • 持久性表示为：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

• 数据库中事务隔离分为4个级别，其中允许“不可重复读”的有？

  • SERIALIZABLE
  • READ COMMITTED
  • READ UNCOMMITTED
  • REPEATABLE READ
    答案是B、C。 等级高低是 A， D， B， C
    C：出现脏读，幻读，不可重复读。
    B：出现幻读，不可重复读。
    D：出现幻读
    A：不出现上述问题

• spring的PROPAGATION_REQUIRES_NEW事务，下面哪些说法是正确的？

  • 内部事务回滚会导致外部事务回滚
  • 内部事务回滚了，外部事务仍然可以提交
  • 外部事务回滚了，内部事务也跟着回滚
  • 外部事务回滚了，内部事务仍然可以提交

• 关于回滚段的使用，下列哪种分配方法比较合适?

  • 给系统中每个事务分配回滚段
  • 给短事务分配小回滚段
  • 给长事务分配大回滚段
  • 给长事务分配小回滚段

• 9i中的数据保护模式包括有？

  • MAXIMIZE PROTECTION
  • MAXIMIZE AVAILABILITY
  • • MAXIMIZE QUANTITY
      MAXIMIZE PERFORMANCE

• 在一个采用（）数据库体系结构的网络数据库应用系统中，计算机C上运行着DBMS软件和应用程序，并存有所有用户数据，其余各节点作为终端通过通信线路向计算机C发出数据库应用请求。

  • 集中式
  • 主从式
  • 客户机/服务器
  • 分布式

• 下面有关ibatis 中的#与${S}的区别，描述错误的是？

  • #将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号
  • S方式能够很大程度防止sql注入。
  • S方式一般用于传入数据库对象，例如传入表名
  • S将传入的数据直接显示生成在sql中
    在Ibatis中我们使用SqlMap进行Sql查询时需要引用参数，在参数引用中遇到的符号#和S之间的区分为，#可以进行与编译，进行类型匹配，而S不进行数据类型匹配，例如： select * from table where id = #id# ，其中如果字段id为字符型，那么#id#表示的就是’id’类型，如果id为整型，那么#id#就是id类型。 select * from table where id = SidS ，如果字段id为整型，Sql语句就不会出错，但是如果字段id为字符型，那么Sql语句应该写成 select * from table where id = ‘SidS‘

• 用命令（）可以查看mysql数据库中user表的表结构？

  • desc user;
  • show create table user;
  • show columns for user;
  • describe user;
    查看Mysql表结构的命令，如下:
    desc 表名;
    show columns from 表名;
    describe 表名;
    show create table 表名;
    use information_schema
    select * from columns where table_name=’表名’;

• MySQL 主从结构的主数据库中不可能出现以下哪种日志？

  • 错误日志
  • 事务日志
  • 中继日志
  • Redo log
    relay-log中继日志是mysql备库从主库bin-log读取的log

• 下面哪些方法可以用来诊断oracle IO、CPU、性能状况。

  • v$sqlarea(disk_reads)
  • statspack
  • sql_trace
  • v$session_wait

• SQL Server支持哪几种备份？

  • 数据库备份
  • 事务日志备份
  • 差异备份
  • 文件和文件组备份