SQL知识学习总结分析题.doc

《SQL知识学习总结分析题.doc》由会员分享，可在线阅读，更多相关《SQL知识学习总结分析题.doc（38页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、例题1、写出一条、写出一条 Sql 语句：取出表语句：取出表 A 中第中第 31 到第到第 40 记录记录 （Mysql）select * from A limit 30, 10MS-SQLServer解 1：select top 10 * from A where id not in (select top 30 id from A)解 2：select top 10 * from A where id (select max(id) from (select top 30 id from A )as A)解 3：select * from (select *, Row_Number() O

2、VER (ORDER BY id asc) rowid FROM A) as A where rowid between 31 and 40Oracleselect *from (select A.*,row_number() over (order by id asc) rankFROM A) where rank =31 AND rank803、学生表、学生表 如下如下:自动编号 学号 姓名 课程编号 课程名称 分数1 2005001 张三 0001 数学 692 2005002 李四 0001 数学 893 2005001 张三 0001 数学 69删除除了自动编号不同, 其他都相同的学

3、生冗余信息A: delete tablename where 自动编号 not in(select min( 自动编号) from tablename group by 学号, 姓名, 课程编号, 课程名称, 分数)4、请用请用 SQL 语句实现：语句实现：从 TestDB 数据表中查询出所有月份的发生额都比 101 科目相应月份的发生额高的科目。请注意：TestDB 中有很多科目，都有 1 12 月份的发生额。AccID ：科目代码，Occmonth ：发生额月份，DebitOccur ：发生额。数据库名：JcyAudit ，数据集：Select * from TestDB答：select

4、a.*from TestDB a ,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID=101 group by Occmonth) bwhere a.Occmonth=b.Occmonth and a.DebitOccurb.Debit101ccur5、一个叫、一个叫 team 的表，的表，里面只有一个字段 name, 一共有 4 条纪录，分别是 a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条 sql 语句显示所有可能的比赛组合.你先按你自己的想法做一下，看结果有我的这个简单吗？select

5、 a.name, b.name from team a, team b where a.name 1 (where1=1，拷贝表结构和数据内容)Oracle:create table bAsSelect * from a where 1=2512. 说明：两张关联表，删除主表中已经在副表中没有的信息说明：两张关联表，删除主表中已经在副表中没有的信息SQL: Delete from info where not exists (select * from infobz where info.infid=infobz.infid )13.有两个表有两个表 A 和和 B ，均有，均有 key 和和

6、value 两个字段，如果两个字段，如果 B 的的 key在在 A 中也有，就把中也有，就把 B 的的 value 换为换为 A 中对应的中对应的 value这道题的 SQL 语句怎么写？update b set b.value=(select a.value from a where a.key=b.key) where b.id in(select b.id from b,a where b.key=a.key);高级 sql 面试题14.原表原表:courseid coursename score-1 Java 702 oracle 903 xml 404 jsp 305 servlet

7、 80-为了便于阅读, 查询此表后的结果显式如下( 及格分数为 60):courseid coursename score mark-1 Java 70 pass2 oracle 90 pass3 xml 40 fail4 jsp 30 fail5 servlet 80 pass-写出此查询语句select courseid, coursename ,score ,decode（sign(score-60),-1,fail,pass) as mark from course(SQL: select courseid, coursename ,score ,（case when score de

8、sc course_vName Null? Type- - -COURSEID NUMBERCOURSENAME VARCHAR2(10)SCORE NUMBERSQL select * from course_v;COURSEID COURSENAME SCORE- - -1 java 702 oracle 903 xml 404 jsp 305 servlet 80SQL select courseid, coursename ,score ,decode(sign(score-60),-1,fail,pass) as mark from course_v;COURSEID COURSEN

9、AME SCORE MARK- - - -1 java 70 pass2 oracle 90 pass3 xml 40 fail4 jsp 30 fail5 servlet 80 pass15.SQL 面试题（面试题（1）create table testtable1(id int IDENTITY,department varchar(12) )select * from testtable1insert into testtable1 values(设计)insert into testtable1 values(市场)insert into testtable1 values(售后)/*

10、结果id department1 设计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

11、 department name1 1 设计 张三2 1 设计 李四3 2 市场 王五4 3 售后 彭六5 4 黑人 陈七*/答案：SELECT testtable2.* , ISNULL(department,黑人)FROM testtable1 right join testtable2 on testtable2.dptID = testtable1.ID也做出来了可比这方法稍复杂。/16.sql 面试题（面试题（2）有表 A，结构如下： A: p_ID p_Num s_id 1 10 01 1 12 02 2 8 01 3 11 01 3 8 03 其中：p_ID 为产品 ID，p_N

12、um 为产品库存量，s_id 为仓库 ID。请用 SQL 语句实现将上表中的数据合并，合并后的数据为： p_ID s1_id s2_id s3_id 1 10 12 0 2 8 0 0 3 11 0 8 其中：s1_id 为仓库 1 的库存量，s2_id 为仓库 2 的库存量，s3_id 为仓库 3 的库存量。如果该产品在某仓库中无库存量，那么就是 0 代替。结果：select p_id ,sum(case when s_id=1 then p_num else 0 end) as s1_id,sum(case when s_id=2 then p_num else 0 end) as s2_

13、id,sum(case when s_id=3 then p_num else 0 end) as s3_idfrom myPro group by p_id什么是相关子查询什么是相关子查询?如何使用这些查询如何使用这些查询?经验更加丰富的开发人员将能够准确地描述这种类型的查询。相关子查询是一种包含子查询的特殊类型的查询。查询里包含的子查询会真正请求外部查询的值，从而形成一个类似于循环的状况。19、为管理业务培训信息，建立、为管理业务培训信息，建立 3 个表：个表：S(S#,SN,SD,SA)S#,SN,SD,SA 分别代表学号，学员姓名，所属单位，学员年龄C(C#,CN)C#,CN 分别代表

14、课程编号，课程名称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的学员姓名和所

15、属单位?答：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)18、SQL 面试题（面试题（4）1. 查询 A(ID,Name)表中第 31 至 40 条记录，ID 作为主

16、键可能是不是连续增长的列，完整的查询语句如下：select top 10 * from A where ID (select max(ID) from (select top 30 ID from A order by A ) T) order by A2.查询表 A 中存在 ID 重复三次以上的记录,完整的查询语句如下：select * from(select count(ID) as count from table group by ID)T where T.count3简答题部分简答题部分1触发器的作用？答：触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来

17、维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。2。什么是存储过程？用什么来调用？答：存储过程是一个预编译的 SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次 SQL ，使用存储过程比单纯 SQL 语句执行要快。可以用一个命令对象来调用存储过程。3。索引的作用？和它的优点缺点是什么？答：索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可

18、以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。3。什么是内存泄漏？答：一般我们所说的内存泄漏指的是堆内存的泄漏。堆内存是程序从堆中为其分配的，大小任意的，使用完后要显示释放内存。当应用程序用关键字 new 等创建对象时，就从堆中为它分配一块内存，使用完后程序调用 free 或者 delete 释放该内存，否则就说该内存就不能被使用，我们就说该内存被泄漏了。4。维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？答：我是这样做的，尽可能使用约束，如 check, 主键，外键，非空字段等来约束，这

19、样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。5。什么是事务？什么是锁？答：事务就是被绑定在一起作为一个逻辑工作单元的 SQL 语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过 ACID 测试，即原子性，一致性，隔离性和持久性。锁：在所以的 DBMS 中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它

20、可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。6。什么叫视图？游标是什么？答：视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。SQL 数据库面试题以及答案（50 例题）https:/ a.sid from (select s

21、id,score from sc where cid=001)a,(select sid,score from sc where cid=002)b where a.sid = b.sid and a.scoreb.score;2、查询平均成绩大于、查询平均成绩大于 60 分的同学的学号和平均成绩分的同学的学号和平均成绩select sid,avg(score) from scgroup by sid having avg(score)60;3、查询所有同学的学号、姓名、选课数、总成绩、查询所有同学的学号、姓名、选课数、总成绩select s.sid,s.sname,count_cid as

22、选课数, sum_score as 总成绩from student sleft join (select sid,count(cid) as count_cid,sum(score) as sum_score from sc group by sid )scon s.sid = sc.sid;4、查询姓、查询姓李李的老师的个数：的老师的个数：select count(tname)from teacher where tname like 李%;5、查询没有学过、查询没有学过“叶平叶平”老师可的同学的学号、姓名：老师可的同学的学号、姓名：select s.sid,s.sname from stu

23、dent as s where s.sid not in (select DISTINCT sid from sc as sc where sc.cid in (select cid from course as c left join teacher as t on c.tid = t.tid where t.tname = 叶平);6、查询学过、查询学过“叶平叶平”老师所教的所有课的同学的学号、姓名：老师所教的所有课的同学的学号、姓名：select s.sid,s.sname from student as s where s.sid in (select distinct sc.sid

24、from sc as sc where sc.cid in (select cid from course as c left join teacher as t on c.tid = t.tid where t.tname = 叶平)group by sc.sid HAVING count(cid)=(select count(cid) from course as c left join teacher as t on c.tid = t.tid where t.tname = 叶平);7、查询学过、查询学过“011”并且也学过编号并且也学过编号“002”课程的同学的学号、姓名：课程的同学

25、的学号、姓名：SELECT s.sid,s.sname from student as s left join sc as sc on s.sid = sc.sidwhere sc.cid = 001and EXISTS(select * from sc as sc_2 where sc.sid = sc_2.sid and sc_2.cid=002);select s.sid,s.snamefrom student as s left join sc as sc on sc.sid = s.sidwhere sc.cid = 001and s.sid in (select sid from

26、sc as sc_2 where sc_2.cid=002 and sc_2.sid = sc.sid);8、查询课程编号、查询课程编号“002”的成绩比课程编号的成绩比课程编号“001”课程低的所有同学的课程低的所有同学的学号、姓名：学号、姓名：select sid,snamefrom (select student.sid,student.sname,score,(select score from sc as sc_2 where sc_2.sid = student.sid and sc_2.cid = 002) as score2 from student,sc where stud

27、ent.sid=sc.sid and cid = 001) s_2where score260 );select sid,snamefrom student swhere not EXISTS (select s.sid from sc where sc.sid = s.sid and sc.score60);10、查询没有学全所有课的同学的学号、姓名：、查询没有学全所有课的同学的学号、姓名：select s.sid,s.snamefrom student s ,sc sc where s.sid = sc.sidgroup by s.sid,s.snamehaving count(sc.ci

28、d)1002 group by sc_1.sid having count(sc_1.cid) = (select count(cid) from sc where sid=1002);select a.sid,s.sname from (select sid,GROUP_CONCAT(cid order by cid separator ,) as cid_str from sc where sid=1002)b,(select sid,GROUP_CONCAT(cid order by cid separator ,) as cid_str from sc group by sid)ale

29、ft join student s on a.sid = s.sidwhere a.cid_str = b.cid_str and a.sid=60 then 1 else 0 END)/count(*) as 及格百分数from sc tleft join course c on t.cid = c.cidgroup by t.cidorder by 100*sum(case when COALESCE(score,0)=60 then 1 else 0 END)/count(*);20、查询如下课程平均成绩和及格率的百分数、查询如下课程平均成绩和及格率的百分数(用用”1 行行”显示显示):

30、 企业管理企业管理 （001） ，马克思（，马克思（002） ，OO22、查询如下课程成绩第、查询如下课程成绩第 3 名到第名到第 6 名的学生成绩单：企业管理名的学生成绩单：企业管理(001)， 马克思马克思(002)，UML(003)，数据库，数据库(004)：23、统计下列各科成绩，各分数段人数：课程、统计下列各科成绩，各分数段人数：课程 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

31、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 scoret2.平均成绩) as 名次,sid as 学生学号,平均成绩 from (select sid,avg(score) 平均成绩 from sc group by sid) as t2order by 平均成绩 desc;25、查询各科成绩前三名的记录（

32、不考虑成绩并列情况）：、查询各科成绩前三名的记录（不考虑成绩并列情况）：select sid,cid,scorefrom sc sc_1where (select count(3) from sc sc_2 where sc_1.cid = sc_2.cid and sc_2.score=sc_1.score)1;31、1981 年出生的学生名单（注：年出生的学生名单（注：student 表中表中 sage 列的类型是列的类型是 datet ime）:32、查询平均成绩大于、查询平均成绩大于 85 的所有学生的学号、姓名和平均成绩：的所有学生的学号、姓名和平均成绩：select s.sname

33、,sc.sid,avg(sc.score) as 平均成绩from sc as scleft join student as s on sc.sid = s.sidgroup by sc.sid having avg(sc.score)85;33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同 时，按课程号降序排列：时，按课程号降序排列：select cid,avg(score)from sc group by cidorder by avg(score),cid desc;34、查询课程名称为、查询课程名称为“数据库

34、数据库”，且分数低于，且分数低于 60 的学生名字和分数：的学生名字和分数：select ame,s.sid,s.sname,sc.scorefrom course cleft join sc on sc.cid = c.cidLEFT JOIN student s on s.sid = sc.sidwhere ame = 数据库 and sc.score70;37、查询不及格的课程，并按课程号从大到小的排列：、查询不及格的课程，并按课程号从大到小的排列：select cidfrom sc where score80;39、求选了课程的学生人数：、求选了课程的学生人数：select count

35、(2) from (select distinct sid from sc)a;40、查询选修、查询选修“叶平叶平”老师所授课程的学生中，成绩最高的学生姓名及老师所授课程的学生中，成绩最高的学生姓名及其成绩：其成绩：select s.sname,sc.scorefrom sc sc left join student s on sc.sid = s.sidleft join course c on sc.cid = c.cidleft join teacher t on c.tid = t.tidwhere t.tname = 叶平and sc.score = (select max(scor

36、e) from sc sc_1 where sc.cid = sc_1.cid);41、查询各个课程及相应的选修人数：、查询各个课程及相应的选修人数：select cid,count(*) from sc group by cid;42、查询不同课程成绩相同的学生和学号、课程号、学生成绩：、查询不同课程成绩相同的学生和学号、课程号、学生成绩：select DISTINCT a.sid,a.cid,a.scorefrom sc as a ,sc as b where a.score = b.scoreand a.cid 10order by count(8) desc,cid;45、检索至少选修

37、两门课程的学生学号：、检索至少选修两门课程的学生学号：select sidfrom scgroup by sidhaving count(8)=2;46、查询全部学生选修的课程和课程号和课程名：、查询全部学生选修的课程和课程号和课程名：select cid,cnamefrom course where cid in (select cid from sc group by cid);47、查询没学过、查询没学过”叶平叶平”老师讲授的任一门课程的学生姓名：老师讲授的任一门课程的学生姓名：select sname from student where sid not in (select sid

38、from sc,course,teacher where course.tid = teacher.tid and sc.cid = course.cid and teacher.tname=叶平);48、查询两门以上不及格课程的同学的学号以及其平均成绩：、查询两门以上不及格课程的同学的学号以及其平均成绩：select sid,avg(COALESCE(score,0)from scwhere sid in (select sid from sc where score2)group by sid;49、检索、检索“004”课程分数小于课程分数小于 60，按分数降序排列的同学学号：，按分数降序

39、排列的同学学号：select sid,scorefrom scwhere cid=004and score60order by score desc;50、删除、删除“002”同学的同学的“001”课程的成绩：课程的成绩：delete from scwhere sid = 002and cid = 001;基本 SQL 操作l SELECT * FROM table ORDER BY field DESC; (ASC|DESC)SELECT DISTINCT field from table where 范围l INSERT INTO table_name (column1,column2,c

40、olumn3,.)VALUES (value1,value2,value3,.);l UPDATE table_name SET column1=value1,column2=value2,.WHERE some_column=some_value;l DELETE FROM table_name WHERE some_column=some_value;LIKE 操作符SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;IN 操作符SELECT column_name(s) FROM table_name

41、WHERE column_name IN (value1,value2,.);BETWEEN 操作符SELECT column_name(s) FROM table_name WHERE column_name BETWEEN JOIN左连接，右连接，内连接left join(左联接)： 返回包括左表中的所有记录和右表中联结字段相等的记录。right join(右联接)： 返回包括右表中的所有记录和左表中联结字段相等的记录。inner join(等值连接)： 只返回两个表中联结字段相等的行。（默认）UNION 操作符UNION 操作符用于合并两个或多个 SELECT 语句的结果集。SELECT

42、 country, name FROM Websites WHERE country=CNUNION SELECT country, app_name FROM apps WHERE country=CNORDER BY country;创建视图CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE conditionSQL 函数函数Avg() Count() Max() Min() Sum()Group By():GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。SELECT colum

43、n_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name;HAVING 子句可以让我们筛选分组后的各组数据。SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name HAVING aggregate_function(column_name) operator

44、 value;如何查询数据库表结构，主键desc tabl_name;建表CREATE TABLE 表名称(列名称 1 数据类型,.)Sql 优化1) 只返回需要的数据a) 不要写 SELECT *的语句b) 合理写 WHERE 子句，不要写没有 WHERE 的 SQL 语句。2) 尽量少做重复的工作可以合并一些 sql 语句3) 适当建立索引（不是越多越好）但以下几点会进行全表扫描a) 左模糊查询%.b) 使用了不等操作符！=c) Or 使用不当，or 两边都必须有索引才行d) In 、not ine) Where 子句对字段进行表达式操作f) 对于创建的复合索引（从最左边开始组合），查询条

45、件用到的列必须从左边开始不能间隔。否则无效，复合索引的结构与电话簿类似g) 全文索引：当于对文件建立了一个以词库为目录的索引（文件大全文索引比模糊匹配效果好）能在 char、varchar、text 类型的列上面创建全文索引MySQL 5.6 Innodb 引擎也能进行全文索引搜索语法：MATCH (列名 1, 列名 2,) AGAINST (搜索字符串 搜索修饰符)如果列类型是字符串，但在查询时把一个数值型常量赋值给了一个字符型的列名 name，那么虽然在name 列上有索引，但是也没有用到。4) 使用 join 代替子查询5) 使用 union 代替手动创建临时表索引优化一、 创建索引，以

46、下情况不适合建立索引l 表记录太少l 经常插入、删除、修改的表l 数据重复且分布平均的表字段二、 复合索引如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引索引索引是对数据库表中一列或多列的值进行排序的一种结构。优点：l 大大加快数据的检索速度l 创建唯一性索引，保证数据库表中每一行数据的唯一性l 可以加速表和表之间的连接缺点：l 索引需要占物理空间。l 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。索引分类：l 普通索引create index zjj_temp_index_1 on zjj_temp_1(first_name);drop index zjj_temp_index_1;l 唯一索引，索引列的值必须唯一，但允许有空值create unique index zjj_temp_1 on zjj_temp_1(id);l 主键索引，它是一种特殊的唯一索引，不允许有空值。l 组合索引