資料庫sql語句常見面試題

轉載：本文轉載自：https://blog.csdn.net/woshinidedege/article/details/78659202

一．有以下幾張表及表結構

Student(Sid,Sname,Sage,Ssex) 學生表

Course(Cid,Cname,Tid) 課程表

SC(Sid,Cid,Score) 成績表

Teacher(Tid,Tname) 教師表

題目：

1.查詢“某1”課程比“某2”課程成績高的所有學生的學号；

select a.Sid from(select Sid,Score from SC where Cid=1) a,  (select Sid,Score from SC where Cid=3) 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.Cid group by Student.Sid,Sname;

4.查詢姓“李”的老師的個數；

select count(Teacher.Tid)from Teacher where Teacher.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 = '和平' );

distinct:去重複

6.查詢學過“```”并且也學過編号“```”課程的同學的學号、姓名；

select a.Sid,a.Sname from(select Student.Sname,Student.Sid from Student,Course,SC where Cname='數學'and SC.Sia=Student.Sid and SC.Cid = Course.Cid) a ,

                                       (select Student.Sname,Student.Sid from Student,Course,SC where Cname='毛概' and SC.Sid = Student.Sid and SC.Cid=Course.Cid) b where a.Sid=b.Sid;

7.查詢學過“葉平”老師所教的所有課的同學的學号、姓名；

//select a.Sid,a.Sname from(select Student.Sid,Student.Sname from Student,Teacher,Course,SC where Teacher.Tname='葉平'and Teacher.Tid=Course.Tid and Course.Cid=SC.Cid andStudent.Sid=SC.Sid)a;

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 = '葉平' grop by Sid having

count (SC.Cid)=(select count(Cid) from Course,Teacher where Teacher.Tid = Course.Tid and Tname=‘葉平))                                     

8.查詢課程編号“”的成績比課程編号“”課程低的所有同學的學号、姓名；

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=1) Score2 from Student,SC

where Student.Sid=SC.Sid and Cid=1 )S_2 where Score2<Score;

9.查詢所有課程成績小于60分的同學的學号、姓名；

SELECT sid,Sname FROM Student WHERE sid not in (SELECT Student.sid FROM Student,SC WHERE Student.sid=SC.sid AND score>60);

10.查詢沒有學全所有課的同學的學号、姓名；

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);

11.查詢至少有一門課與學号為“”的同學所學相同的同學的學号和姓名；

12.查詢至少學過學号為“”同學所有一門課的其他同學學号和姓名；

13.把“SC”表中“葉平”老師教的課的成績都更改為此課程的平均成績；

update sc set score=(select avg(score) from sc,course,teacher where course.cid=sc.cid and course.tid=teacher.tid and teacher.tname='楊巍巍')

14.查詢和“”号的同學學習的課程完全相同的其他同學學号和姓名；

SELECT sid FROM SC WHERE cid in (SELECT cid FROM SC WHERE sid=6) GROUP BY sid having count(*)=(SELECT count(*) FROM SC WHERE sid=6);

15.删除學習“葉平”老師課的SC表記錄；?

delete from sc s where s.cid in (select c.cid from teacher t,course c where t.tid = c.tid and tname='葉平')

16.向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編号“”課程的同學學号、課程的平均成績；

Insert into SC SELECT sid,2,(SELECT avg(score) FROM SC WHERE cid=2) FROM Student WHERE sid not in (SELECT sid FROM SC WHERE cid=2);

17.按平均成績從高到低顯示所有學生的“資料庫”、“企業管理”、“英語”三門的課程成績，按如下形式顯示：學生ID,,資料庫,企業管理,英語,有效課程數,有效平均分；

18.查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分；

19.按各科平均成績從低到高和及格率的百分數從高到低順序

20.查詢每門課程被選修的學生數

select sc.cid,count(sc.sid) from sc,course where sc.cid=course.cid group by sc.cid

21.查詢出隻選修了一門課程的全部學生的學号和姓名

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;

22.查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程号降序排列

SELECT Cid,Avg(score) FROM SC GROUP BY cid ORDER BY Avg(score),cid DESC ;

23.查詢不及格的課程，并按課程号從大到小排列?

SELECT cid,sid FROM sc WHERE score <60 ORDER BY cid

24.查詢課程編号為且課程成績在60分以上的學生的學号和姓名；

select student.sid,student.sname from sc,student where sc.cid=1 and sc.score>60 and sc.sid=student.sid

25.查詢選修“葉平”老師所授課程的學生中，成績最高的學生姓名及其成績

select student.sname,sc.score from sc,student,teacher,course c where teacher.tname='李子'

and teacher.tid=c.tid and c.cid=sc.cid and sc.sid=student.sid and sc.score=(select max(score)from sc where sc.cid=c.cid)

26.查詢各個課程及相應的選修人數

select sc.cid ,count(sc.sid)from sc,student where sc.sid=student.sid group by sc.cid

27.查詢每門功成績最好的前兩名

28.統計每門課程的學生選修人數（超過人的課程才統計）。要求輸出課程号和選修人數，查詢結果按人數降序排列，查詢結果按人數降序排列，若人數相同，按課程号升序排列

select sc.cid,count(sc.cid)from sc,course where sc.cid=course.cid group by sc.cid  order by sc.cid desc

29.檢索至少選修兩門課程的學生學号

SELECT sid FROM  sc group  by  sid having  count(*)  >  =  2  

30.查詢沒學過“葉平”老師講授的任一門課程的學生姓名

select distinct sid from sc where sid not in(select sc.sid from sc,course,teacher where sc.cid=course.cid and course.tid=teacher.tid and

teacher.tname='楊巍巍')

31.查詢兩門以上不及格課程的同學的學号及其平均成績

32.檢索“”課程分數小于90，按分數降序排列的同學學号

select sc.sid from sc,course where sc.cid=course.cid and course.cname='java' and sc.score<90

33.删除“”同學的“”課程的成績

delete from sc where sid=1 and cid=1

二．下面的題目和上面的表沒有任何關系

34、列出全部學生的資訊。

35、列出軟體專業全部學生的學号及姓名。

36、列出所有必修課的課号。

37、求1号課成績大于80分的學生的學号及成績，并按成績由高到低列出。

38、列出非軟體專業學生的名單。

39、查詢成績在70~80分之間的學生選課得分情況

不在此範圍内的查詢：（注意寫出和以下語句等價的語句）

40、列出選修1号課或3号課的全體學生的學号和成績。  

簡答題

(一).什麼是事務？事務的送出和復原什麼意思？

答：

a>事務是應用程式中一系列嚴密的操作，所有操作必須成功完成，否則在每個操作中所作的所有更改都會被撤消。也就是事務具有原子性，一個事務中的一系列的操作要麼全部成功，要麼一個都不做。

事務具有四個特征：原子性（ Atomicity ）、一緻性（ Consistency ）、隔離性（Isolation ）和持續性（ Durability ）。

事務是為了保證對同一資料表操作的一緻性。

即多條語句放在事務中執行的時候，要麼一起成功，要麼全不成功。我的了解：資料庫中的事務就是需要捆綁在一起執行的操作集合，他們應不能被部分的完成。

b>事務送出是送出事務的所有操作：具體來說就是将事務中所有對資料庫的更新寫回到磁盤上的實體資料庫中，事務正常結束；

c>事務復原是資料庫傳回到事務開始的狀态：事務在運作過程中發生某種故障，事務不能繼續執行，系統将事務中對資料庫的所有已完成的更新操作全部撤銷，使資料庫復原到事務開始時的狀态。

（二）、sql語句應該考慮哪些安全性？()https://www.cnblogs.com/usa007lhy/p/5976673.html

（1）少使用root賬戶，應該為不同的動作配置設定不同的賬戶；

（2）sql執行出錯後，不能把資料庫中顯示的出錯資訊，直接展示給使用者。防止洩露伺服器和資料庫相關資訊；

（3）防止sql注入，對特殊字元進行轉義、過濾或者使用預編譯的sql語句綁定變量。

（三）、簡單描述MySQL中，索引，主鍵，唯一索引，聯合索引的差別，對資料庫的性能有什麼影響。

--索引是一種特殊的檔案（InnoDB資料表上的索引是表空間的一個組成部分），它們包含着對資料表裡所有記錄的引用指針。

--普通索引（由關鍵字KEY或INDEX定義的索引）的唯一任務是加快對資料的通路速度。

--普通索引：允許被索引的資料列包含重複的值，如果能确定某個資料列隻包含彼此各不相同的值，在為這個資料索引建立索引的時候就應該用關鍵字UNIQE把它定義為一個唯一是以，唯一索引可以保證資料記錄的唯一性。

--主鍵：一種特殊的唯一索引，在一張表中隻能定義一個主鍵索引，逐漸用于唯一辨別一條記錄，是用關鍵字PRIMARY KEY來建立。

--索引可以覆寫多個資料列，如像INDEX索引，這就是聯合索引。

--索引可以極大的提高資料的查詢速度，但是會降低插入删除更新表的速度，因為在執行這些寫操作時，還要操作索引檔案。    

（四）、一張表,裡面有ID自增主鍵,當insert了17條記錄之後,删除了第15,16,17條記錄,再把Mysql重新開機,再insert一條記錄,這條記錄的ID是18還是15 ？

如果表的類型是MyISAM，那麼是18。

因為MyISAM表會把自增主鍵的最大ID記錄到資料檔案裡，重新開機MySQL自增主鍵的最大ID也不會丢失。

如果表的類型是InnoDB，那麼是15。

InnoDB表隻是把自增主鍵的最大ID記錄到記憶體中，是以重新開機資料庫或者是對表進行OPTIMIZE操作，都會導緻最大ID丢失。

（五）、請簡述項目中優化sql語句執行效率的方法，從哪些方面。sql語句性能如何分析？(https://www.cnblogs.com/coderchuanyu/p/4065434.html)

(1).盡量選擇較小的列

(2).将where中用的比較頻繁的字段建立索引

(3).select子句中避免使用‘*’

(4).避免在索引列上使用計算，not，in和<>等操作

(5).當隻需要一行資料的時候使用limit 1

(6).保證表單資料不超過200w，适時分割表

　　針對查詢較慢的語句，可以使用explain來分析該語句具體的執行情況

（六）、MyISAM和InnoDB各有哪些特性？分别适用在怎樣的場景下？(https://blog.csdn.net/aaa123524457/article/details/54375341)

 主要差別：

    1).MyISAM是非事務安全型的，而InnoDB是事務安全型的。

    2).MyISAM鎖的粒度是表級，而InnoDB支援行級鎖定。

    3).MyISAM支援全文類型索引，而InnoDB不支援全文索引。

    4).MyISAM相對簡單，是以在效率上要優于InnoDB，小型應用可以考慮使用MyISAM。

    5).MyISAM表是儲存成檔案的形式，在跨平台的資料轉移中使用MyISAM存儲會省去不少的麻煩。

    6).InnoDB表比MyISAM表更安全，可以在保證資料不會丢失的情況下，切換非事務表到事務表（alter table tablename type=innodb）。

應用場景：

    1).MyISAM管理非事務表。它提供高速存儲和檢索，以及全文搜尋能力。如果應用中需要執行大量的SELECT查詢，那麼MyISAM是更好的選擇。

    2).InnoDB用于事務處理應用程式，具有衆多特性，包括ACID事務支援。如果應用中需要執行大量的INSERT或UPDATE操作，則應該使用InnoDB，這樣可以提高多使用者并發操作的性能。

（七）、在建立和使用索引時，有哪些要注意的地方，有什麼規則？

A.：使用索引的注意事項

使用索引時，有以下一些技巧和注意事項：

1.索引不會包含有NULL值的列

隻要列中包含有NULL值都将不會被包含在索引中，複合索引中隻要有一列含有NULL值，那麼這一列對于此複合索引就是無效的。是以我們在資料庫設計時不要讓字段的預設值為NULL。

2.使用短索引

對串列進行索引，如果可能應該指定一個字首長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字元内，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。

3.索引列排序

MySQL查詢隻使用一個索引，是以如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。是以資料庫預設排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列建立複合索引。

4.like語句操作

一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

5.不要在列上進行運算

select * from users where YEAR(adddate)<2007;

将在每個行上進行運算，這将導緻索引失效而進行全表掃描，是以我們可以改成:

select * from users where adddate<‘2007-01-01';

6.不使用NOT IN和<>操作。

B.：sql優化原則

常見的簡化規則如下：

1).不要有超過5個以上的表連接配接（JOIN）

2).考慮使用臨時表或表變量存放中間結果。

3).少用子查詢

4).視圖嵌套不要過深,一般視圖嵌套不要超過2個為宜。

5).連接配接的表越多，其編譯的時間和連接配接的開銷也越大，性能越不好控制。

6).最好是把連接配接拆開成較小的幾個部分逐個順序執行。

7).優先執行那些能夠大量減少結果的連接配接。

8).拆分的好處不僅僅是減少SQL Server優化的時間，更使得SQL語句能夠以你可以預測的方式和順序執行。

(八)，請簡潔地描述下MySQL中InnoDB支援的四種事務隔離級别名稱，以及逐級之間的差別？

1.InnoDB支援事物，而MyISAM不支援事物

2.InnoDB支援行級鎖，而MyISAM支援表級鎖

3.InnoDB支援MVCC, 而MyISAM不支援

4.InnoDB支援外鍵，而MyISAM不支援

5.InnoDB不支援全文索引，而MyISAM支援。

6.InnoDB不能通過直接拷貝表檔案的方法拷貝表到另外一台機器， myisam 支援

7.InnoDB表支援多種行格式， myisam 不支援

8.InnoDB是索引組織表， myisam 是堆表

轉載于:https://www.cnblogs.com/Tom-shushu/p/9852535.html