--為了方便做題創建了表--drop table if exists student;create table student(

s_id bigint,

s_name char(10),

s_birth date,

s_sex char(5));

drop table if exists course;

create table course(

c_id int,

c_name char(20),

t_id int);

drop table if exists teacher;

create table teacher(

t_id int,

t_name char(10));

drop table if exists score;

create table score(

s_id int,

c_id int,

s_score int);

insert into student values(20644101,'小明','1992-10-14','男');

insert into student values(20644102,'小紅','1990-11-15','女');

insert into student values(20644103,'小江','1993-12-16','女');

insert into student values(20644104,'小月','1994-12-17','女');

insert into student values(20644105,'小青','1996-07-14','女');

insert into student values(20644106,'小龍','1995-12-12','男');

insert into student values(20644107,'小磊','1997-12-18','男');

insert into student values(20644108,'小坤','1998-06-14','男');

insert into student values(20644109,'小強','1990-05-14','男');

insert into course values(01,'語文',101);

insert into course values(02,'數學',102);

insert into course values(03,'英語',103);

insert into course values(04,'體育',104);

insert into course values(05,'物理',105);

insert into course values(06,'化學',106);

insert into teacher values(101,'趙三');

insert into teacher values(102,'錢二');

insert into teacher values(103,'孫強');

insert into teacher values(104,'李紅');

insert into teacher values(105,'周五');

insert into teacher values(106,'鄭前');

insert into score values(20644101,01,98);

insert into score values(20644101,02,92);

insert into score values(20644101,03,96);

insert into score values(20644101,04,90);

insert into score values(20644102,01,98);

insert into score values(20644103,02,98);

insert into score values(20644104,03,98);

insert into score values(20644105,04,90);

insert into score values(20644106,05,60);

insert into score values(20644107,06,55);

insert into score values(20644108,01,45);

insert into score values(20644109,02,90);

insert into score values(20644102,03,98);

insert into score values(20644103,04,98);

insert into score values(20644104,05,98);

insert into score values(20644105,06,90);

insert into score values(20644106,01,60);

insert into score values(20644107,02,55);

insert into score values(20644108,03,45);

insert into score values(20644109,04,90);

常見的sql筆試題和面試題上

1、查詢課程編號為“001”的課程比“002”的課程成績高的所有學生的學號。

select a.s_id from

(select s_id,s_score from score where c_id=01) a

join

(select s_id,s_score from score where c_id=02) b

on a.s_id=b.s_id

where a.s_score > b.s_score

;

2、查詢平均成績大于60分的學生的學號和平均成績

select s_id

,avg(s_score)as avg_score

from score

group by s_id

having avg(s_score)>60;

3、查詢所有學生的學號、姓名、選課數、總成績

select a.s_id

,a.s_name

,b.co

,b.sum_score

from(select s_id,s_name from student) a

join (select s_id

,count(c_id) as co

,sum(s_score) as sum_score

from score

group by s_id) b

on a.s_id=b.s_id;

##簡便select a.s_id,a.s_name,count(b.c_id),sum(b.s_score)

from student a

join score b

on a.s_id=b.s_id

group by a.s_id,a.s_name;

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

select count(t_id) from teacher

where t_name like '趙%';

5.查詢沒學過“趙三”老師課的學生的學號、姓名

select a.s_id

,a.s_name

from student a

where s_id not in

(select s_id from score,course,teacher

where score.c_id=course.c_id

and teacher.t_id=course.t_id

and teacher.t_name='趙三' );

6、查詢學過“趙三”老師所教的所有課的同學的學號、姓名

select a.s_id

,a.s_name

from student a

where s_id in

(select s_id from score,course,teacher

where score.c_id=course.c_id

and teacher.t_id=course.t_id

and teacher.t_name='趙三' );

7、查詢學過編號為“01”的課程并且也學過編號為“02”的課程的學生的學號、姓名

select s.s_id

,s.s_name

from student s

join

(select s_id

,c_id

from score

where c_id=01

) b

on s.s_id=b.s_id

join

(select s_id

,c_id

from score

where c_id=02) c

on s.s_id=c.s_id ;

8、查詢課程編號為“02”的總成績

select sum(s_score) from score

where c_id=02;

9、查詢所有課程成績小于60分的學生的學號、姓名

select s.s_id

,s.s_name

from student s

join

(select s_id from socre

where s_score<60) b

on s.s_id=b.s_id;

便于檢查：

select s.s_id

,s.s_name

,b.s_score

from student s

join

(select s_id

,s_score

from score

where s_score<60) b

on s.s_id=b.s_id;

10、查詢沒有學全所有課的學生的學號、姓名

select s.s_id

,s.s_name

,b.co

from student s

join

(select s_id

,count(c_id) as co

from score

group by s_id

having count(c_id)

on s.s_id=b.s_id;

11、查詢至少有一門課與學號為“1001”的學生所學課程相同的學生的學號和姓名

select s.s_id

,s.s_name

from student s

join

(select s_id from score

where c_id in (select c_id from score where s_id=20644101)) b

on s.s_id=b.s_id;

--簡便寫法

select student.s_id, student.s_name

from student, score

where student.s_id = score.s_id

and c_id in (select c_id from score where s_id=20644101);

12、：查詢和“20644101”號同學所學課程完全相同的其他同學的學號

select s.s_id from student s

join

(select s_id from score

where c_id in (select c_id from score where s_id=20644101)) b

on s.s_id=b.s_id

group by s.s_id

having count(*)=(select count(*) from score where s_id=20644101);

13、把“SCORE”表中“趙三”老師教的課的成績都更改為此課程的平均成績

update score set s_score=(select c_id

,avg(s_score)

from score

where c_id in

(select c_id from course c

join

(select t_id,t_name from teacher

where t_name='趙三') t

on c.t_id=t.t_id)

group by c_id);

----趙三老師的課程

select c_id

,avg(s_score)

from score

where c_id in

(select c_id from course c

join

(select t_id,t_name from teacher

where t_name='趙三') t

on c.t_id=t.t_id)

group by c_id;

--14、查詢和“20644102”號的同學學習的課程完全相同的其他同學學號和姓名

select s_id from score where c_id in

(select c_id from score where s_id=20644102)

group by s_id

having count(*)=

(select count(*) from score where s_id=20644102);

select s.s_id

,s.s_name

from student s

join

(select s_id from score where c_id in

(select c_id from score where s_id=20644102)

group by s_id

having count(*)=

(select count(*) from score where s_id=20644102)) t

on s.s_id=t.s_id;

15、刪除學習“趙三”老師課的SC表記錄

select t_id from teacher

where t_name='趙三'

select c_id

,t_id

from course

select c_id

,s_score

from score

---delete score from course,teacherwhere course.c_id=socre.c_id

and course.t_id=teacher.t_id

and t_name='趙三';

16、向SC表中插入一些記錄這些記錄要求符合以下條件：沒有上過編號為“003”課程的學生的學號、編號為002的課程的平均成績

insert score select s_id

,(select avg(s_score) from score where s_id=20644102) avg_score

from score

where s_id not in

(select s_id from score where c_id=03);

--------------------------------------------select s_id,(select avg(s_score) from score where s_id=20644102) avg_score

from score

where s_id not in

(select s_id from score where c_id=03);

17、按平均成績從高到低顯示所有學生的“數據庫”(c_id='004')、“企業管理”(c_id='001')、“英語”(c_id='006')三門的課程成績，按如下形式顯示：學生ID，數據庫，企業管理，英語，有效課程數，有效平均分

select s_id as '學生ID'

,(select s_score from score where score.s_id=t.s_id and c_id=02) as dbm

,(select s_score from score where score.s_id=t.s_id and c_id=03) as mang

,(select s_score from score where score.s_id=t.s_id and c_id=04) as edg

,count(*) as '有效課程數'

,avg(t.s_score) as'平局成績'

from score as t

group by s_id

order by avg(t.s_score)

;

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

select c_id as '課程ID'

,max(s_score) as '最高分'

,min(s_score) as '最低分'

from score

group by c_id;

19、按各科平均成績從低到高和及格率的百分數從高到低排列，以如下形式顯示：課程號課程名平均成績及格百分數

---兩個字段排序處理

select s.c_id

,c.c_name

,avg( coalesce(s.s_score,0)) as avg_score

,100*sum(case when coalesce(s.s_score,0)>=60 then 1 else 0 end)/count(*) as percent

from score s

join course c

on s.c_id=c.c_id

group by s.c_id

,c.c_name

order by 100*sum(case when coalesce(s.s_score,0)>=60 then 1 else 0 end)/count(*) desc,avg( coalesce(s.s_score,0)) asc

;

20、查詢如下課程平均成績和及格率的百分數(用1行顯示)，其中企業管理為001，馬克思為002，UML為003，數據庫為004

select c_id

,avg(s_score)

,sum(case when coalesce(s_score,0)>=60 then 1 else 0 end)/count(*)

from score

where c_id in (01,02,03,04)

group by c_id

;

21、查詢不同老師所教不同課程平均分從高到低顯示

select c.c_id

,t.t_id

,avg(coalesce(s.s_score,0))

from course c,teacher t ,score s

where c.t_id=t.t_id

and c.c_id=s.c_id

group by c.c_id

,t.t_id

order by avg(coalesce(s.s_score,0)) desc

;

22、查詢如下課程成績第3名到第6名的學生成績單，其中企業管理為001，馬克思為002，UML為003，數據庫為004，以如下形式顯示：

select t.c_id

,t.s_score

from

(select row_number()over(partition by c_id order by s_score desc) r1

,s_score

,c_id

from score) t

where t.r1 >=3

and t.r1 <=6

group by t.c_id

,t.s_score

order by t.c_id asc,s_score desc

;

23、使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計各分數段人數：課程ID和課程名稱

select c.c_id

,c.c_name

,sum(case when s.s_score between 85 and 100 then 1 else 0 end ) as [85-100]

,sum(case when s.s_score between 70 and 85 then 1 else 0 end ) as [70-85]

,sum(case when s.s_score between 60 and 70 then 1 else 0 end)as [60-70]

,sum(case when s.s_score <60 then 1 else 0 end ) as [<60]

from course c, score s

group by c.c_id

,c.c_name

;

24、查詢學生平均成績及其名次

select t.s_id

,t.r1

,avg(t.s_score) as avg_score

from

(select s_id

,s_score

,row_number()over(partition by s_id order by avg(s_score)) as r1

from score

group by s_id

,s_score) t

group by t.s_id

,t.r1

25、查詢各科成績前三名的記錄(不考慮成績并列情況)

select t.s_id

,t.r1

,t.c_id

from

(select s_id

,c_id

,row_number()over(partition by c_id order by s_score desc) as r1

from score) t

where t.r1<3

group by t.s_id

,t.r1

,t.c_id

;

26、查詢每門課程被選修的學生數

select count(s_id) as co

,c_id

from score

group by c_id

order by c_id

;

27、查詢出只選修了一門課程的全部學生的學號和姓名

select s.s_id

,s.s_name

,count(c.c_id) as co

from score c ,student s

where c.s_id=s.s_id

group by s.s_id

,s.s_name

having count(c.c_id) =1

;

select s.s_id

,s.s_name

,count(c.c_id) as co

from score c ,student s

where c.s_id=s.s_id

group by s.s_id

,s.s_name

having count(c.c_id) =2

;

28、查詢男生、女生人數

select sum(case when s_sex='女' then 1 else 0 end) as '女生人數'

,sum(case when s_sex='男' then 1 else 0 end) as '男生人數'

from student

;

29、查詢姓“小”的學生名單

select * from student

where s_name like '小%'

;

30、查詢同名同性學生名單并統計同名人數///查詢姓“小”的學生人數

select s_name,count(*) from student

group by s_name

having count(*)>1

;

select count(*) from student

where s_name like '小%'

;

31、1981年出生的學生名單(注：Student表中s_birth列的類型是datetime)

select * from student

where year(s_birth)=1992

;

-----mysql里面沒有datepart

select s_name

,convert(char(11),datepart(year,s_birth))as age

from student

where convert(char(11),datepart(year,s_birth))='1992'

;

------日期函數

SELECT DATE_FORMAT( deteline, "%Y-%m-%d %H" ) , COUNT( * )

FROM test

GROUP BY DATE_FORMAT( deteline, "%Y-%m-%d %H" )

;

------------注意order by和having的順序--------------32、查詢平均成績大于85的所有學生的學號、姓名和平均成績select s.s_id

,s.s_name

,avg(r.s_score)

from student s,score r

where s.s_id=r.s_id

group by s.s_id,s.s_name

having avg(r.s_score) > 85

order by avg(r.s_score) desc

;

33、查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列

select c_id

,avg(s_score)

from score

group by c_id

order by avg(s_score),c_id desc

;

34、查詢課程名稱為“數據庫”且分數低于60的學生姓名和分數

select s.s_name

,coalesce(r.s_score,0)

,c.c_name

from student s,score r ,course c

where s.s_id=r.s_id

and c.c_id=r.c_id

and r.s_score <60

and c.c_name='語文'

;

35、查詢所有學生的選課情況

select s.s_id

,c.c_id

,s.s_name

,c.c_name

from student s,course c,score r

where s.s_id=r.s_id

and c.c_id=r.c_id

;

37、查詢不及格的課程并按課程號從大到小排列

select s_id

, c_id

,s_score

from score

where s_score<60

group by s_id,c_id,s_score

order by c_id desc

;

38、查詢課程編號為003且課程成績在80分以上的學生的學號和姓名

select s.s_id

,s.s_name

from student s,score r

where s.s_id=r.s_id

and r.s_score>80

and c_id=03

;

39、查詢選了課程的學生人數

select count(distinct s_id) from score;

---40、查詢選修“趙三”老師所授課程的學生中成績最高的學生姓名及其成績

select s.s_name

,r.s_score

from student s, course c,teacher t,score r

where s.s_id=r.s_id

and r.c_id=c.c_id

and c.t_id=t.t_id

and t.t_name='趙三'

and r.s_score=(select max(s_score) from score where c_id=c.c_id )

;

41、查詢各個課程及相應的選修人數

select count(distinct s_id)

,c_id

from score

group by c_id

;

--42、查詢有2門不同課程成績相同的學生的學號、課程號、學生成績

select distinct a.s_id,a.c_id,b.s_score from score a,score b

where a.s_score=b.s_score

and b.c_id<>a.c_id

;

43、查詢每門課程成績最好的前兩名

select s.s_id

,s.r1

,s.c_id

from

(select s_id

,c_id

,row_number()over(partition by c_id order by s_id) as r1

from score) s

where r1<3

;

44、統計每門課程的學生選修人數(超過10人的課程才統計)。要求輸出課程號和選修人數，查詢結果按人數降序排序，若人數相同，按課程號升序排序

select c_id

,count(*) as co

from score

group by c_id

having count(*) >=2

order by count(*) desc,c_id asc

;

45、查詢至少選修兩門課程的學生學號

select s_id

,count(distinct c_id) as co

from score

group by s_id

having count(distinct c_id)>=2

;

---46、查詢全部學生都選修的課程的課程號和課程名

select c.c_id,c.c_name from course c ,score r,student s

where s.s_id=r.s_id

and c.c_id=r.c_id

and (select count(*) from student) in (select count(*) from score group by c_id)

;

select c_id ,c_name from course

where c_id in (select c_id from score group by c_id);

47、查詢沒學過“趙三”老師講授的任一門課程的學生姓名

select s_name from student

where s_id not in

(select s.s_id from score s,course c,teacher t

where s.c_id=c.c_id

and c.t_id=t.t_id

and t.t_name='趙三')

;

48、查詢兩門以上不及格課程的同學的學號及其平均成績

select t.s_id,t.avg_score

from

(select s_id,avg(score) as avg_score

from score

group by s_id) t

where t.avg_score<60

having count(*)>1

;

select s_id,avg(s_score)from score

where s_id in (select s_id from score where s_score<60 group by s_id having count(*)>1)

group by s_id;

49、檢索課程編號為“04”且分數大于60的學生學號，結果按按分數降序排列

select s_id,s_score from score

where c_id=04

and s_score>60

order by s_score desc

;

50、刪除學生編號為“002”的課程編號為“001”的成績

delete from score

where s_id=20644102

and c_id =01

;

常見的sql筆試題和面試題下

1.SQL語言允許使用通配符進行字符串匹配的操作，其中‘%’可以表示： C

A.零個字符

B.1個字符

C.多個字符

D.以上都是

2.通過 SQL，如何從 "Persons" 表中選取 "FirstName" 列？B

A、SELECT Persons.FirstName

B、SELECT FirstName FROM Persons

C、EXTRACT FirstName FROM Persons

3.通過 SQL，如何從 "Persons" 表中選取所有的列？C

A、SELECT [all] FROM Persons

B、SELECT Persons

C、SELECT * FROM Persons

D、SELECT *.Persons

4.通過 SQL，如何從 "Persons" 表中選取 "FirstName" 列的值等于"Peter" 的所有記錄？D

A、SELECT [all] FROM Persons WHERE FirstName='Peter'

B、SELECT * FROM Persons WHERE FirstName LIKE 'Peter'

C、SELECT [all] FROM Persons WHERE FirstName LIKE 'Peter'

D、SELECT * FROM Persons WHERE FirstName='Peter'

5通過 SQL，如何從 "Persons" 表中選取 "FirstName" 列的值以 "a" 開頭的所有記錄？A

A、SELECT * FROM Persons WHERE FirstName LIKE 'a%'

B、SELECT * FROM Persons WHERE FirstName='a'

C、SELECT * FROM Persons WHERE FirstName LIKE '%a'

D、SELECT * FROM Persons WHERE FirstName='%a%'

6請判斷下列說法是否正確：當所列出的某個條件為 true 時，OR 運算符會顯示記錄。當列出的所有條件為 true 時，AND 運算符會顯示記錄。A

A、正確

B、錯誤C

7.通過 SQL，您如何在表 Persons 中選擇 FirstName 等于 Thomas 而 LastName 等于 Carter 的所有記錄？C

A、SELECT * FROM Persons WHERE FirstName LIKE 'Thomas' AND LastName LIKE 'Carter'

B、SELECT FirstName='Thomas', LastName='Carter' FROM Persons

C、SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'

8通過 SQL，您如何按字母順序選取 Persons 表中 LastName 介于 Adams 和 Carter 的所有記錄？A

A、SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'

B、SELECT * FROM Persons WHERE LastName>'Adams' AND LastName

C、SELECT LastName>'Adams' AND LastName

9哪條 SQL 語句可返回唯一不同的值？B

A、SELECT DIFFERENT

B、SELECT DISTINCT

C、SELECT UNIQUE

10哪個 SQL 關鍵詞用于對結果集進行排序？D

A、ORDER

B、SORT BY

C、SORT

D、ORDER BY

11通過 SQL，您如何根據 "FirstName" 列降序地從 "Persons" 表返回所有記錄？B

A、SELECT * FROM Persons SORT 'FirstName' DESC

B、SELECT * FROM Persons ORDER BY FirstName DESC

C、SELECT * FROM Persons ORDER FirstName DESC

D、SELECT * FROM Persons SORT BY 'FirstName' DESC

12通過 SQL，您如何在 "Persons" 表中刪除 "FirstName" 等于 "Fred" 的紀錄？A

A、DELETE FROM Persons WHERE FirstName = 'Fred'

B、DELETE FirstName='Fred' FROM Persons

C、DELETE ROW FirstName='Fred' FROM Persons

13通過 SQL，如何返回 "Persons" 表中記錄的數目？D

A、SELECT COLUMNS() FROM Persons

B、SELECT COLUMNS(*) FROM Persons

C、SELECT COUNT() FROM Persons

D、SELECT COUNT(*) FROM Persons

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的mysql例题_mysql练习题1的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。