Mysql查詢練習

xbhog發表於2020-11-14

MySql

MySQL查詢練習

導讀：

以下是MySQL中查詢練習題，該練習題是個人整理的，如果哪些地方有錯誤或者疑問，歡迎指出；

個人使用navicate版本是15，mysql版本5.7.31

如果有些語句顯示group by的問題，建議檢視MySQL版本：

如果是mysql5.7.x版本，預設是開啟了 only_full_group_by 模式，會導致程式碼報錯；

解決方法：

1、檢視sql_mode：

select @@global.sql_mode;

查詢出來的值為：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2、去掉ONLY_FULL_GROUP_BY，重新設定值。

 set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

3、上面是改變了全域性sql_mode，對於新建的資料庫有效。對於已存在的資料庫，則需要在對應的資料下執行：

set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

資源問題：

將student各表，以及study表放到百度網盤中，自取；

連結：https://pan.baidu.com/s/1CxZA_pb9k_4UKZDKMQovNw
提取碼：1234

一、實驗目的

1、掌握查詢語句的基本組成和使用方法。

2、掌握常用查詢技巧。

二、實驗預習

1、 SQL中查詢語句的語句格式：

Select 屬性名 from 表名;

2、 SQL中建立資料表的語句格式：

Create table 表名(

	欄位名 欄位型別，………

);

三、實驗內容及要求

1、資料庫*db_student*中基本表的資料如下，輸入下列資料。

學生表：Student

Sno	Sname	Ssex	Sage	Sdept
9512101	李勇	男	19	計算機系
9512103	王敏	女	20	計算機系
9521101	張莉	女	22	資訊系
9521102	吳賓	男	21	資訊系
9521103	張海	男	20	資訊系
9531101	錢小平	女	18	數學系
9531102	王大力	男	19	數學系

課程表：Course

Cno	Cname	Ccredit	Semster	Period
C01	計算機導論	3	1	3
C02	VB	4	3	4
C03	計算機網路	4	7	4
C04	資料庫基礎	6	6	4
C05	高等數學	8	1	8

選課表：SC

Sno	Cno	Grade
9512101	C03	95
9512103	C03	51
9512101	C05	80
9512103	C05	NULL
9521101	C05	NULL
9521102	C05	80
9521103	C05	45
9531101	C05	81
9531101	C01	67
9531102	C05	94
9521103	C01	80
9512101	C01	NULL
9531102	C01	NULL
9512101	C02	87
9512101	C04	76

2、根據db_student中的資料，完成下列查詢，將查詢語句寫在下方。

（1）查詢全體學生的資訊。

Select * from student;

（2）查詢“資訊系”學生的學號，姓名和出生年份。

Select sno,sname,YEAR(NOW())-sage from student 

WHERE sdept='資訊系';

（3）查詢考試不及格的學生的學號。

Select distinct sno from sc where grade<60;

（4）查詢無考試成績的學生的學號和相應的課程號。

Select sno,cno from sc where grade is null;

（5）將學生按年齡升序排序。

Select * from student order by sage;

（6）查詢選修了課程的學生的學號和姓名。

（要求：分別使用連線查詢、巢狀子查詢完成）

連線查詢：

Select distinct student.sname,sc.sno from student,sc where student.sno=sc.sno ;

巢狀子查詢：

select sno,sname from student where sno in (
select distinct sno from sc);

補充：

=any-->等於子查詢結果中的某個值。

Select sno,sname from student where sno=any (select distinct sno from sc);

（7）查詢年齡在20-23歲之間的學生的系，姓名，年齡，按照系升序排序。

Select sname,sage,sdept from student where sage between 20 and 23 order by sdept;

補充：

注意：utf8預設的校隊集是utf-8-general-ci,他不是按照中文來的，需要強制讓mysql按照中文來排序，gbk包含全部的中文字元，utf-8則包含全世界所有國家需要用到的字元；

Select sname,sage,sdept from student where sage between 20 and 23 order by convert(sdept using gbk);

（8）查詢選修了“計算機網路”或者“資料庫基礎”課程的學生的學號，姓名。

（要求：分別使用連線查詢、巢狀子查詢完成）

連線查詢：

select distinct student.sno,sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and (cname='計算機網路' or cname='資料庫基礎');

巢狀查詢：

select student.sno,sname 

from student

where sno in 

(

	select sno From sc

	where cno in (select cno from course where cname = '計算機網路' or cname = '資料庫基礎' ));

補充：

聯合查詢：

Select sno,sname from student where sno in (select sno from course,sc where course.cno =sc.cno and cname ='計算機網路')
Union
Select sno,sname from student where sno in (select sno from course,sc where course.cno =sc.cno and cname ='資料庫基礎');

（9）查詢姓“張”的學生的基本資訊。

select * from student where sname like'張%';

（10）查詢學生的選課情況，要求輸出學號，姓名，課程門數，課程名列表（用逗號分隔），按照學號升序排序。

SELECT student.sno,sname,COUNT(*),

GROUP_CONCAT(cname ORDER BY cname SEPARATOR ',')'課程列表'

FROM student,sc,course

WHERE student.sno=sc.sno AND sc.cno=course.cno

GROUP BY student.sno

ORDER BY student.sno;

（11）查詢選修了課程的學生的總人數。

（要求：分別使用巢狀子查詢的謂詞IN和EXISTS完成）

謂詞IN：

SELECT count(DISTINCT sno) FROM sc WHERE sno in (select sno from sc);

謂詞EXISTS：

SELECT COUNT(DISTINCT sno) FROM sc WHERE  EXISTS (SELECT sno FROM sc);

（12）統計各門課程選修人數，要求輸出課程代號，課程名，選修人數，有成績人數（grade不為NULL），最高分，最低分，平均分，按課程號排序。

Select c.cno,cname,count(*),MAX(grade),MIN(grade),AVG(grade),COUNT(grade)

From student s,sc,course c where s.sno=sc.sno and sc.cno=c.cno GROUP BY c.cno;

（13）統計各門課程的重修人數（包括grade為NULL），要求輸出課程代號，重修人數。

SELECT cno,COUNT(*) from sc WHERE grade<60 OR grade is NULL GROUP BY cno;

（14）查詢選修通過2門（包括2門）以上的學生的資訊，輸出學號、選修通過門數、平均成績，按門數降序排序，若門數相同，按照成績降序。

select sno, count(*),avg(grade)

from sc	where grade >= 60

group by sno

having count(*) >= 2 order by count(*) DESC,avg(grade) desc;

（15）查詢與“王大力”同一個系的學生的基本資訊。

SELECT * FROM student WHERE sname !='王大力' and sdept in (

SELECT distinct sdept FROM student WHERE sname='王大力');

（16）查詢每個學生高於他自己選修平均分的那門課程的成績，輸出學號，課程號，課程成績，他所有課程的平均分，並按學號升序排列。

（要求：使用基於子查詢派生表的查詢方法）

SELECT sc.sno,cno,grade, avggrade FROM sc,

(SELECT sno, AVG(grade) avggrade FROM sc GROUP BY sno) AS  avg_sc

WHERE sc.sno=avg_sc.sno AND

 sc.grade>avg_sc.avggrade

ORDER BY sc.sno;

（17）查詢沒有同時選修“計算機導論”和“計算機網路”兩門課的學生的學號，姓名。

Select sno,sname from student where sno not in(

Select sno from sc,course where sc.cno=course.cno and cname='計算機導論'

And sno in (

Select sno from sc,course where sc.cno=course.cno and cname='計算機網路'

));(包含了沒有任何選課的同學的資訊)

Select sno,sname from student where sno not in (select sno from student where not exists(

Select * from course where cname in ('計算機網路','計算機導論') and not exists(

Select * from sc where sno=student.sno and cno=course.cno)

));(包含了沒有任何選課的同學資訊)

Select distinct sc.cno,sname from student,sc where student.sno=sc.sno and

sc.sno not in(select sno from sc,course where sc.cno=course.cno and cname='計算機導論' and sno in (select sno from sc,course where sc.cno=course.cno and cname='計算機網路'));

（18）查詢選修了全部課程的學生的學號，姓名，系名。

select student.sno,sname,sdept from student where NOT exists     

(select  * from course where NOT exists    

(select * from sc where sc.sno = student.sno  and sc.cno = course.cno));

補充：

Select sno,sname,sdept from student where sno in(

Select sno from sc group by sno having count(*)=(select count(*) from course)

);

（19）輸出“高等數學”課程成績前三名的學生的學號，姓名，系名

SELECT s.sno,sname,sdept from student s,sc,course c WHERE s.sno=sc.sno and sc.cno=c.cno AND cname='高等數學'

ORDER BY grade DESC LIMIT 3;(不考慮成績有重複值的情況)

補充：

Select student.sno,sname,sdept from student,sc,course,(select distinct grade from sc,course where sc.cno=course.cno and cname='高等數學'order by grade
desc limit 3) as g where student.sno=sc.sno and sc.cno=course.cno and sc.grade=g.grade and cname='高等數學';

3、匯入資料庫study，完成下列查詢，將查詢語句寫在下方。

（1）查詢總經理、經理以下的職員資訊，包括NULL值記錄。

select * from employee where job_title is null or job_title not in (
select job_title from employee where job_title ='總經理' or job_title='經理'
)；

（2）查詢“聯榮資產”的客戶資訊。

select * from customer where customer_name like '%聯榮資產%'；

3、匯入資料庫study，完成下列查詢，將查詢語句寫在下方。

（1）查詢總經理、經理以下的職員資訊，包括NULL值記錄。

SELECT * FROM employee WHERE job_title is NULL

OR job_title not IN(

SELECT job_title from employee WHERE job_title='總經理'

OR job_title='經理'

);

（2）查詢“聯榮資產”的客戶資訊。

SELECT * from customer WHERE customer_name LIKE '%聯榮資產%';

（3）查詢價格5000-6000的“聯想”品牌和價格在5000以下的“小米”品牌的產品資訊。

select * FROM product WHERE description LIKE '%聯想%' AND price BETWEEN 5000 AND 6000 

UNION 

SELECT * FROM product WHERE description LIKE '%小米%' AND price < 5000;

（4）查詢如“GTX950M”/“GTX960M”系列的產品資訊。

SELECT * FROM product WHERE description LIKE '%GTX950M%' OR description LIKE '%GTX960M%';

（5）統計各年份訂單總數，訂單總額，按年份降序排列。

SELECT YEAR(pay_time),count(*) as number,sum(total_money) as money FROM payment GROUP BY YEAR(pay_time) ORDER BY YEAR(pay_time) DESC;

（6）統計2016年各產品的訂購數量（降序排列），輸出5-10名的統計資訊，包括產品ID，訂購總數。

select product_id,payment.order_id, count(*), payment_id

from payment, order_detail where year(pay_time) = 2016 

and payment.order_id = order_detail.order_id group by order_id

order by buy_number desc limit 4,6;