计算机专业就业指导(Java)之基础技术测试篇（二）

基础技术测试篇（二）

  1.2  SQL语言

例题12  请写出SQL四条最基本的数据操作语句

答案：SELECT * FROM 表名        INSERT INTO 表名 (字段，字段，……)      UPDATE 表名 SET(字段=值，字段=值，……) WHERE (条件)

DELETE FROM 表名 WHERE(条件)

Tips：数据库操作语言(Data Manipulation Language，DML)用于实现对数据库的操作。基本的数据库操作有两类：检索(查询)和更新(包括插入、删除、更新)。这两类操作也是使用编程语言在数据库外部对数据库进行得最多的操作，因此在很多的面向程序员职位的面试中，此类问题会经常被提到。

 例题13 试解释COMMIT操作和ROLLBACK操作的语义

答案：COMMIT语句表示事务执行成功地结束（提交），此时告诉系统，数据库要进入一个新的正确状态，该事务对数据库的所有更新都已交付实施（写入 磁盘），

ROLLBACK语句表示事务执行不成功地结束，此时告诉系统，已发生错误，数据库可能处在不正确的状态，该事务对数据库的所有更新必须被撤销，数据库应该恢复该事务到初始状态。

Tips：数据库中的事务具有ACID属性：原子性、一致性、隔离性、永久性。其中，原子性表示事务应作为一个工作单元，事务处理完成，所有的工作要么都在数据库 中保存下来，这就需要COMMIT语句进行提交，要么就撤销所有进行过的操作，全都不保留，这就需要ROLLBACK语句进行回滚。

例题14 谈谈JOIN语句的使用，考虑到性能的优化，你有何建议？

答案：join语句分为以下几种：

（1）left join：左连接，返回左表中所有的记录以及右表中与连接字段相等的记录。也就是显示符合条件的数据行，同时显示左边数据表不符合条件的数据行，右边没有对应的条目显示NULL。

（2）right join：右连接，返回右表中所有的记录以及左表中与连接字段相等的记录。也就是显示符合条件的数据行，同时显示右边数据表不符合条件的数据行，左边没有对应的条目显示NULL。

（3）inner join：内连接，又叫等值连接，只返回两个表中连接字段相等的行。

（4）full join：外连接，显示符合条件的数据行，同时显示左右不符合条件的数据行，相应的左右两边显示NULL，即显示左连接、右连接和内连接的并集。

（5）cross join：交叉连接，结果是笛卡尔积，就是 第一个表的行数乘以第二个表的行数。

交叉连接如果不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。因此，若两个需要求交集的表太大，将会非常慢，不建议使用。   内连接如果没有指定连接条件的话，和笛卡尔积的交叉结果一样，但是没有笛卡尔那么复杂，不需要首先生成行数乘积的数据表，因此内连接的效率要高于笛卡尔积的交叉连接。但是通常情况下，使用内连接需要指定连接条件。    指定连接条件的内连接，仅仅返回符合条件的条目。而外连接不同，返回的结果不仅包含符合连接条件的行，还包括左表（左外连接），右表（右外连接）或者两边连接（全外连接）的所有数据行。因此，在涉及到多表查询时，外连接是最经常被使用到的形式。

Tips：下面3条连接查询语句执行结果相等，但是效率不一样。

SELECT  a.*,b.* FROM table1 a,table2 b WHERE a.id=b.id

SELECT * FROM table1 a CROSS JOIN table2 b WHERE a.id=b.id

SELECT * FROM table1 a INNRE JOIN table2 b ON a.id=b.id

 

一般不建议前两种方式，因为如果有WHERE子句的话，往往会首先生成两个表行数乘积的行数的数据表然后再根据WHERE子句条件从中选择。

例题15 设教学数据库中有三个基本表：

学生表S（S#，SNAME,AGE,SEX）,其属性表示学生的学号、姓名、年龄和性别；

选课表SC(S#,C#,GRADE),其属性表示学生的学号、所学课程号和成绩；

课程表C（C#，CNAME,TEACHER）,其属性表示课程号、课程名称和任课教师姓名。

按要求写出对三个基本表的操作语句

（1）写出下列插入操作的SQL语言

把SC表中每门课程的平均成绩插入到另一个已存在的表SC_C(C#,CNAME,AVG_GRADE)中，其中AVG_GRADE是每门课程的平均成绩

答案：

INSERT INTO SC_C (C#,CNAME,AVG_GRADE)SELECT SC.C#,C.CNAME,AVG(GRADE)FROM SC,C WHERE SC.C#=C.C#

Tips:我们常用的插入语句的格式是INSERT INTO...VALUES...，但将一个已经存在的表中的数据插入到另一个表时，可以使用INSERT INTO...SELECT ...WHERE...的形式
（2）写出下列删除操作的SQL语句
从SC表中把吴老师的女学生选课元组删掉

答案：

DELETE FROM SC,S,C WHERE SC.S#=S.S# AND SC.C#=C.C# AND S.SEX='女' AND C.TEACHER='吴老师'

Tips:当要把满足某个条件的元组从多个表中删除时，需要进行多表连接查询
（3）用SQL来表达下列完整性约束
规定女同学选修刘老师的课程成绩都应该在70分以上

答案：

ALTER TABLE SC,S,C
ADD CONSTRAINT GRADE CHECK(GRADE>70)
WHERE SC.C#=C.C# AND SC.S#=S.S# AND S.SEX='女' AND C.TEACHER='刘老师'

Tips:约束适用于限制列的值域，在数据类型限制的基础上对输入的数据进行进一步限制。通过逻辑表达式来定义列的有效值，在SQL语句中使用ADD CONSTRAINT 来为数据表添加完整性约束

例题16：编写SQL语句，找出数据表test中num的最小的数

答案：

select * from test where num<=all(select num from test)
或者
select top 1 num from test orderm by num

或者
select * from test where num=(select min(num)from test)

Tips：在实现SQL语句的查找功能时，可以使用不同的实现方式。不同的实现方式在查找时的效率是不一样的。上题中第一和第三种方式使用了嵌套查询，这将大大增加查询的时间。此时灵活运用SQL中top()等内置函数就显得尤为重要。

例题17：写出一条Sql语句：取出表A中第31到第40记录（以自动增长的ID作为主键,注意：ID可能不是连续的）
 

答案：

select * from A where ID >=31 and ID <= 40
select top 10 * from (select top 40 * from A order by ID) order by ID desc 
select top 10 * from A   where  ID not in (select  ID from top  30 from  A)

例题18：连接下面两个数据表，得到按区域划分的销售信息。

          

Store_Information数据表                     

Store_Name	Sales	Date
Los Angeles	￥1500	Jan-10-2000
SanDiego	￥250	Jan-11-2000
Los Angeles	￥300	Jan-12-2000
Boston	￥700	Jan-12-2000

 

Region数据表

Region_Name	Store_Name
East	Boston
East	NewYork
West	Los Angeles
West	San Diego

 

 

答案：

SELECT A1.region_name REGION,SUM(A2.Sales) SALES

FORM Region A1,Store_Infomation A2

WHERE A1.store_name =A2.store_name

GROUP BY A1.region_name

Tips:为了简化SQL语句，这里使用数据表的别名。SQL语言中一般使用两种类型的别名，分别为字段别名和数据表别名。对于数据表别名，可以通过将别名放置在FROM从句中数据表名称的后面设定。数据表别名在连接多个数据表进行查询的操作中极为有用。

例题19：请使用一个SQL语句从table1和table2中取出如table3所列的数据

 

                                                                                                       

table1数据表

mon	dep	yj
一月份	01	10
一月份	02	10
一月份	03	5
二月份	02	8
二月份	04	9
三月份	03	8

table2数据表

dep	dname
01	国内业务一部
02	国内业务二部
03	国内业务三部
04	国内业务四部

table3数据表

dep	一月份	二月份	三月份
01	10	null	null
02	10	8	null
03	null	5	8
04	null	null	9

 答案：

select a.dname,b.yj as'一月份',c.yj as '二月份',d.yj as '三月份'

from table1 a,table2 b,table2 c,table2 d

where a.dep=b.dep and b.mon='一月份' and 

a.dep=c.dep and c.mon='二月份' and

a.dep=d.dep and d.mon='三月份'

或者

select a.dep,

sum(case when b.mon=1 then b.yj else 0 end)as'一月份',

sum(case when b.mon=2 then b.yj else 0 end)as'二月份',

sum(case when b.mon=3 then b.yj else 0 end)as'三月份',

from table2 a left join table1 b on a.dep=b.dep

Tips:SQL语句中的CASE具有两种格式。简单ＣＡＳＥ函数和ＣＡＳＥ搜索函数

简单CASE函数的格式如下：

CASE sex

                 WHEN '1' THEN '男'

                 WHEN '2' THEN '女'

ELSE '其他' END

CASE 搜索函数的格式如下：

CASE WHEN sex='1' THEN '男'

            WHEN sex='2' THEN '女'

ELSE '其他' END 

这两种方式都可以实现相同的功能。简单CASE函数的写法比较简洁，和ＣＡＳＥ搜索函数相比，功能方面会有些限制，例如写判断式。还有一个需要注意的问题，ＣＡＳＥ函数只返回第一个符合条件的值，剩下的CASE部分将会被自动忽略。