mysql导入导出选择题_mysql----导入导出、练习题

一、导入导出

1、Navicat软件使用

2、命令

导出现有数据库数据：

mysqldump -u用户名 -p密码 数据库名称 >导出文件路径           # 结构+数据

mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径       # 结构

导入现有数据库数据：

mysqldump -uroot -p密码  数据库名称 < 文件路径

二、练习题

1、查询平均成绩大于60分的同学的学号和平均成绩；

思路：根据学生分组，使用avg获取平均值，通过having对avg进行筛选

SELECT student_id, AVG(num) FROM score GROUP BY student_id HAVING AVG(num) > 60

思考题：显示学生名字

SELECT

score.student_id,

student.sname,

AVG(score.num)

FROM

score

LEFT JOIN student on score.student_id=student.sid

GROUP BY

student_id

HAVING

AVG(num)> 60

2、查询所有同学的学号、姓名、选课数、总成绩；

SELECT

score.student_id,

student.sname,

COUNT(score.course_id),

SUM(score.num)

FROM

score

LEFT JOIN student ON score.student_id=student.sid

GROUP BY

student_id

3、查询姓“李”的老师的个数；

SELECT

COUNT(tname)

FROM

teacher

WHERE

tname LIKE '李%'

4、查询没学过“李平”老师课的同学的学号、姓名；

5、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

SELECT

b.student_id,

student.sname,

COUNT(b.course_id) AS course_num

FROM

(

SELECT

student_id,

course_id

FROM

score

WHERE

course_id = 1

OR course_id = 2

) AS b

LEFT JOIN student on b.student_id=student.sid

GROUP BY

b.student_id

HAVING

COUNT(b.course_id)>1

6、查询学过“李平”老师所教的所有课的同学的学号、姓名；

知识点：in

SELECT

student_id as choice_liping

FROM

score

WHERE

course_id IN (

SELECT

course.cid

FROM

course

LEFT JOIN teacher ON course.teacher_id = teacher.tid

WHERE

teacher.tname = '李平老师'

)GROUP BY student_id

7、查询有课程成绩小于60分的同学的学号、姓名；

SELECT

student.sname,

a.student_id

FROM

(

SELECT

student_id,

course_id,

num

FROM

score

WHERE

num < 60

) AS a

LEFT JOIN student ON a.student_id = student.sid

GROUP BY

a.student_id

或(DISTINCT：自动删除重复的)：

SELECT

student.sname,

a.student_id

FROM

(

SELECT DISTINCT

student_id

FROM

score

WHERE

num < 60

) AS a

LEFT JOIN student ON a.student_id = student.sid

8、查询没有学全所有课的同学的学号、姓名；

SELECT

student_id,

COUNT(student_id)

FROM

score

GROUP BY

student_id

HAVING

COUNT(student_id) < (

SELECT

COUNT(course.cid)

FROM

course

)

9、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

SELECT

score.student_id,

student.sname

FROM

score

LEFT JOIN student ON score.student_id = student.sid

WHERE

score.student_id != 1

AND score.course_id IN (

SELECT

course_id

FROM

score

WHERE

student_id = 1

)

GROUP BY

score.student_id

10、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名

思路：1、课程数量与2号学生相同

2、课程与2号同学有交集

3、交集的课程数量与2号学生的相同

SELECT

student_id,

student.sname

FROM

score

LEFT JOIN student ON student_id = student.sid

WHERE

student_id IN (

SELECT

student_id

FROM

score

WHERE

student_id != 2

GROUP BY

student_id

HAVING

COUNT(course_id) = (

SELECT

COUNT(course_id)

FROM

score

WHERE

student_id = 2

)

)

AND course_id IN (

SELECT

course_id

FROM

score

WHERE

student_id = 2

)

GROUP BY

student_id

HAVING

COUNT(course_id) = (

SELECT

COUNT(course_id)

FROM

score

WHERE

student_id = 2

)

11、删除学习“叶平”老师课的score表记录；

DELETE

FROM

score

WHERE

course_id IN (

SELECT

cid

FROM

course

WHERE

teacher_id IN (

SELECT

tid

FROM

teacher

WHERE

tname = '李平老师'

)

)

12、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；

INSERT INTO score(student_id,course_id,num)

SELECT

student_id,

2,

(SELECT AVG( num) FROM score WHERE course_id = 2)

FrOM

score

WHERE

student_id NOT IN (

SELECT

student_id

FROM

score

WHERE

course_id = 2

GROUP BY

student_id

)

GROUP BY

student_id

13、按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分

SELECT

student_id as '学号',

(SELECT num FROM score as innerdb LEFT JOIN course on course.cid=innerdb.course_id WHERE course.cname='生物' and innerdb.student_id=oterdb.student_id)as '生物',

(SELECT num FROM score as innerdb LEFT JOIN course on course.cid=innerdb.course_id WHERE course.cname='物理' and innerdb.student_id=oterdb.student_id)as '物理',

(SELECT num FROM score as innerdb LEFT JOIN course on course.cid=innerdb.course_id WHERE course.cname='体育' and innerdb.student_id=oterdb.student_id)as '体育',

COUNT(course_id)as '选修课程数',

AVG(num)as '平均分'

FROM

score AS oterdb

GROUP BY

student_id

ORDER BY

AVG(num) DESC

14、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

SELECT

course_id,

COUNT(course_id),

AVG(num),

SUM(CASE WHEN score.num > 60 THEN 1 ELSE 0 end)/COUNT(course_id)*100 as persent

FROM

score

GROUP BY

course_id

ORDER BY persent DESC

15、课程平均分从高到低显示(显示任课老师)

SELECT

cid,

AVG(

IF (

ISNULL(score.num),

0,

score.num

)

) AS avg,

teacher.tname

FROM

course

LEFT JOIN score ON score.course_id = cid

LEFT JOIN teacher ON teacher.tid = course.teacher_id

GROUP BY

cid

ORDER BY

avg DESC

16、查询每个科目前三科成绩

SELECT *FROM (

SELECT course_id,

(SELECT num FROM score as s1 WHERE s1.course_id = s2.course_id ORDER BY num desc LIMIT 0,1) as first_num,

(SELECT num FROM score as s1 WHERE s1.course_id = s2.course_id ORDER BY num desc LIMIT 1,1) as second_num,

(SELECT num FROM score as s1 WHERE s1.course_id = s2.course_id ORDER BY num desc LIMIT 1,1) as third_num

FROM score as s2)as t GROUP BY course_id

三、知识点

1、where与having的区别：

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，也就是在分组之前过滤数据，条件中不能包含聚和函数，使用where条件限制特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数，使用having 条件过滤特定的组，也可以使用多个分组标准进行分组。

例子：

#where和having都可以使用的场景：

select price,name from goods where price > 100

select price,name from goods having price > 100

#只可以用where，不可以用having的情况

select name from goods where price> 100

select name from goods having price> 100 //报错！！！因为select没有筛选出price 字段，having不能用,而where是对表进行检索price。100

#只可以用having，不可以用where情况

select id, avg(price) as agprice from goods group by id having agprice > 100

select id, avg(price) as agprice from goods where agprice>100 group by id //报错！！因为from goods这表里面没有agprice这个字段

2、in

3、DISTINCT

4、insetinto tb1(xx,xx)select x1,x2from tb2;

5、三元操作

case when ..then... else...

6、if语句

IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。

7、三张表之间互相有联系，可将中间联系的那张表作为主表，再用连续 left join 剩余的两张表