MySQL:sql语句强化练习

快来打我* 2022-05-14 12:42 294阅读 0赞

## MySQL：sql语句强化练习 ##

1、准备表：  
A：对应着每种水果；B：对应着每种水果的价格  
create table A(  
A\_ID int primary key auto\_increment,  
A\_NAME varchar(20) not null  
);  
insert into A values(1,’苹果’);  
insert into A values(2,’橘子’);  
insert into A values(3,’香蕉’);

create table B(  
A\_ID int primary key auto\_increment,  
B\_PRICE double  
);  
insert into B values(1,2.30);  
insert into B values(2,3.50);  
insert into B values(4,null);  
多表查询中的问题：  
笛卡尔积问题：把多张表放在一起，同时去查询，会得到一个结果，而这结果并不是我们想要的数据，但这个结果成为笛卡尔积。

笛卡尔积的数据，对程序是没有意义的， 我们需要对笛卡尔积中的数据再次进行过滤。  
对于多表查询操作，需要过滤出满足条件的数据，需要把多个表进行连接，连接之后需要加上过滤的条件。  
内连接：  
外链接查询：左外连接、右外连接、全连接、自连接  
关联子查询：  
需求：  
1、 查询价格最贵的水果名称  
2、 显示最贵的水果 以及 最贵水果的价格

2、准备工作：  
teacher 教师表  
student 学生表  
cource 课程表  
studentcource 选课表 学生和课程的关系表  
CREATE TABLE teacher (  
id int(11) NOT NULL primary key auto\_increment,  
name varchar(20) not null unique  
);  
CREATE TABLE student (  
id int(11) NOT NULL primary key auto\_increment,  
name varchar(20) NOT NULL unique,  
city varchar(40) NOT NULL,  
age int  
) ;  
CREATE TABLE course(  
id int(11) NOT NULL primary key auto\_increment,  
name varchar(20) NOT NULL unique,  
teacher\_id int(11) NOT NULL,  
FOREIGN KEY (teacher\_id) REFERENCES teacher (id)  
);

CREATE TABLE studentcourse (  
student\_id int NOT NULL,  
course\_id int NOT NULL,  
score double NOT NULL,  
FOREIGN KEY (student\_id) REFERENCES student (id),  
FOREIGN KEY (course\_id) REFERENCES course (id)  
);

insert into teacher values(null,’关羽’);  
insert into teacher values(null,’张飞’);  
insert into teacher values(null,’赵云’);

insert into student values(null,’小王’,’北京’,20);  
insert into student values(null,’小李’,’上海’,18);  
insert into student values(null,’小周’,’北京’,22);  
insert into student values(null,’小刘’,’北京’,21);  
insert into student values(null,’小张’,’上海’,22);  
insert into student values(null,’小赵’,’北京’,17);  
insert into student values(null,’小蒋’,’上海’,23);  
insert into student values(null,’小韩’,’北京’,25);  
insert into student values(null,’小魏’,’上海’,18);  
insert into student values(null,’小明’,’北京’,20);

insert into course values(null,’语文’,1);  
insert into course values(null,’数学’,1);  
insert into course values(null,’生物’,2);  
insert into course values(null,’化学’,2);  
insert into course values(null,’物理’,2);  
insert into course values(null,’英语’,3);

insert into studentcourse values(1,1,80);  
insert into studentcourse values(1,2,90);  
insert into studentcourse values(1,3,85);  
insert into studentcourse values(1,4,78);  
insert into studentcourse values(2,2,53);  
insert into studentcourse values(2,3,77);  
insert into studentcourse values(2,5,80);  
insert into studentcourse values(3,1,71);  
insert into studentcourse values(3,2,70);  
insert into studentcourse values(3,4,80);  
insert into studentcourse values(3,5,65);  
insert into studentcourse values(3,6,75);  
insert into studentcourse values(4,2,90);  
insert into studentcourse values(4,3,80);  
insert into studentcourse values(4,4,70);  
insert into studentcourse values(4,6,95);  
insert into studentcourse values(5,1,60);  
insert into studentcourse values(5,2,70);  
insert into studentcourse values(5,5,80);  
insert into studentcourse values(5,6,69);  
insert into studentcourse values(6,1,76);  
insert into studentcourse values(6,2,88);  
insert into studentcourse values(6,3,87);  
insert into studentcourse values(7,4,80);  
insert into studentcourse values(8,2,71);  
insert into studentcourse values(8,3,58);  
insert into studentcourse values(8,5,68);  
insert into studentcourse values(9,2,88);  
insert into studentcourse values(10,1,77);  
insert into studentcourse values(10,2,76);  
insert into studentcourse values(10,3,80);  
insert into studentcourse values(10,4,85);  
insert into studentcourse values(10,5,83);  
需求：  
1、查询不及格的学生  
2、查询获得最高分的学生信息  
3、查询编号2课程比编号1课程最高成绩高学生信息

根据上述的4张表强化练习：  
1、查询平均成绩大于70分的同学的学号和平均成绩  
2、查询所有同学的学号、姓名、选课数、总成绩  
3、查询学过赵云老师所教课的同学的学号、姓名  
4、查询没学过关羽老师课的同学的学号、姓名  
5、查询没有学三门课以上的同学的学号、姓名  
6、查询各科成绩最高和最低的分  
7、查询学生信息和平均成绩  
8、查询上海和北京学生数量  
9、查询不及格的学生信息和课程信息  
10、统计每门课程的学生选修人数（超过四人的进行统计）

3、部门员工查询练习

– 部门表  
create table dept(  
deptno int primary key auto\_increment, – 部门编号  
dname varchar(14) , – 部门名字  
loc varchar(13) – 地址  
) ;  
– 员工表  
create table emp(  
empno int primary key auto\_increment,– 员工编号  
ename varchar(10), – 员工姓名 -  
job varchar(9), – 岗位  
mgr int, – 直接领导编号  
hiredate date, – 雇佣日期，入职日期  
sal int, – 薪水  
comm int, – 提成  
deptno int not null, – 部门编号  
foreign key (deptno) references dept(deptno)  
);  
insert into dept values(10,’财务部’,’北京’);  
insert into dept values(20,’研发部’,’上海’);  
insert into dept values(30,’销售部’,’广州’);  
insert into dept values(40,’行政部’,’深圳’);  
insert into emp values(7369,’刘一’,’职员’,7902,’1980-12-17’,800,null,20);  
insert into emp values(7499,’陈二’,’推销员’,7698,’1981-02-20’,1600,300,30);  
insert into emp values(7521,’张三’,’推销员’,7698,’1981-02-22’,1250,500,30);  
insert into emp values(7566,’李四’,’经理’,7839,’1981-04-02’,2975,null,20);  
insert into emp values(7654,’王五’,’推销员’,7698,’1981-09-28’,1250,1400,30);  
insert into emp values(7698,’赵六’,’经理’,7839,’1981-05-01’,2850,null,30);  
insert into emp values(7782,’孙七’,’经理’,7839,’1981-06-09’,2450,null,10);  
insert into emp values(7788,’周八’,’分析师’,7566,’1987-06-13’,3000,null,20);  
insert into emp values(7839,’吴九’,’总裁’,null,’1981-11-17’,5000,null,10);  
insert into emp values(7844,’郑十’,’推销员’,7698,’1981-09-08’,1500,0,30);  
insert into emp values(7876,’郭靖’,’职员’,7788,’1987-06-13’,1100,null,20);  
insert into emp values(7900,’令狐冲’,’职员’,7698,’1981-12-03’,950,null,30);  
insert into emp values(7902,’张无忌’,’分析师’,7566,’1981-12-03’,3000,null,20);  
insert into emp values(7934,’杨过’,’职员’,7782,’1983-01-23’,1300,null,10);

练习：  
– 1．列出至少有一个员工的所有部门。

– 2．列出薪金比”刘一”多的所有员工。

– 3．**\*** 列出所有员工的姓名及其直接上级的姓名。

– 4．列出受雇日期早于其直接上级的所有员工。

– 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

– 6．列出所有job为“职员”的姓名及其部门名称。

– 7．列出最低薪金大于1500的各种工作。

– 8．列出在部门 “销售部” 工作的员工的姓名，假定不知道销售部的部门编号。

– 9．列出薪金高于公司平均薪金的所有员工。

– 10．列出与”周八”从事相同工作的所有员工。

– 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。(只要和部门30中任意一个员工的薪资相等即可)

– 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

– 13．列出在每个部门工作的员工数量、平均工资。

– 14．列出所有员工的姓名、部门名称和工资。

– 15．列出所有部门的详细信息和部门人数。

– 16．列出各种工作的最低工资。

– 17．列出各个部门的 经理 的最低薪金。

– 18．列出所有员工的年工资,按年薪从低到高排序。

– 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。

– 20.查询出所有薪水在’陈二’之上的所有人员信息。

– 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水

– 22.查询出emp表中所有的工作种类（无重复）

– 23.查询出所有奖金（comm）字段不为空的人员的所有信息。

– 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。

– 25.查询出员工号为7521，7900，7782的所有员工的信息。

– 26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。

– 27.查询出名字第三个汉字是“忌”的所有员工信息。

– 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。

– 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。 order by convert(name using gbk) asc;

– 30.查询出最早工作的那个人的名字、入职时间和薪水。

– 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.

– 32.显示出薪水最高人的职位。

– 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。

– 34.删除10号部门薪水最高的员工。

– 35.将薪水最高的员工的薪水降30%。

– 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)  
语法：case when … then … when … then … else … end

– 1、查询平均成绩大于70分的同学的学号和平均成绩  
– 1.1 请每个学生的平均成绩 分组  
select student\_id,truncate(avg(score),0)  
from studentcourse  
group by student\_id  
having avg(score)>70  
;

– 2、查询所有同学的学号、姓名、选课数、总成绩  
– 2.1 查询每个学生的选课数和总成绩 根据学生id分组  
select student\_id,count(course\_id),sum(score)  
from studentcourse  
group by student\_id;

– 2.2 在2.1基础上 查询 每个同学的学号、姓名、选课数、总成绩  
select student.id 学号,student.name 姓名,temp.courseCount 选课数,temp.sumScore 总成绩  
from student,(select student\_id,count(course\_id) as courseCount,sum(score) as sumScore  
from studentcourse  
group by student\_id) as temp  
where student.id=temp.student\_id;

– 3、查询学过赵云老师所教课的同学的学号、姓名  
– 3.1 找到赵云老师教的课程 id  
select id from teacher where name=’赵云’;

select id from course  
where teacher\_id = (select id from teacher where name=’赵云’);

– 3.2 在中间表 根据课程的id 查询哪些学生学了这些课程 学生的id  
select student\_id from studentcourse  
where course\_id in (select id from course  
where teacher\_id = (select id from teacher where name=’赵云’));

– 3.3 根据学生的id 查询学生的信息  
select \* from student  
where id in (select student\_id from studentcourse  
where course\_id in (select id from course  
where teacher\_id = (select id from teacher where name=’赵云’))  
);

– 4、查询没学过关羽老师课的同学的学号、姓名  
select \* from student  
where id not in (select student\_id from studentcourse  
where course\_id in (select id from course  
where teacher\_id = (select id from teacher where name=’关羽’))  
);

– 5、查询没有学三门课以上的同学的学号、姓名  
– 5.1 每个同学学了几门课 分组 根据学生的id  
select student\_id  
from studentcourse  
group by student\_id  
having count(\*)<=3  
;

– 5.2 找学号 和 姓名 ；哪？ 学生表  
select \*  
from student  
where id in (select student\_id  
from studentcourse  
group by student\_id  
having count(\*)<=3);

– 6、查询各科成绩最高和最低的分  
select course\_id,max(score),min(score)  
from studentcourse  
group by course\_id;

– 临时表  
select course.name 课程,temp.maxScore 最高分,temp.minScore 最低分  
from course,(select course\_id,max(score) maxScore,min(score) minScore  
from studentcourse  
group by course\_id) as temp  
where course.id=temp.course\_id;

– 7、查询学生信息和平均成绩  
– 7.1查询每个学生的平均成绩  
select student\_id,avg(score)  
from studentcourse  
group by student\_id;

– 7.2 关联查询 临时表  
select student.\*,temp.avgScore  
from student,(select student\_id,truncate(avg(score),0) avgScore  
from studentcourse  
group by student\_id) as temp  
where student.id=temp.student\_id;

– 8、查询上海和北京学生数量  
select city,count(\*) from student  
group by city  
having city in (‘北京’,’上海’)

;

select city,count(\*)  
from student  
where city in (‘北京’,’上海’)  
group by city  
;

– 9、查询不及格的学生信息和课程信息  
– 9.1 将不及格的人查询出来  
select course\_id,student\_id  
from studentcourse  
where score<60;

– 9.2 将9.1 作为临时表 和 学生表与课程表关联  
select student.*,course.*  
from student,course,(select course\_id,student\_id  
from studentcourse  
where score<60) as temp  
where student.id=temp.student\_id  
and course.id=temp.course\_id;

– 10、统计每门课程的学生选修人数（超过四人的进行统计）  
– 10.1 查询每门课程的学生人数  
select course\_id,count(\*)  
from studentcourse  
group by course\_id;

– 10.2 结果  
select course\_id,count(\*)  
from studentcourse  
group by course\_id  
having count(\*)>4

select \* from emp;

select \* from dept;

– 1．列出至少有一个员工的所有部门。  
select dept.deptno,count(empno) from dept  
left outer join emp  
on dept.deptno=emp.deptno  
group by dept.deptno  
having count(empno)>=1  
;

– 2．列出薪金比”刘一”多的所有员工。  
– 刘一的薪金  
select sal from emp where ename=’刘一’;

– 查询 > 刘一薪金 的员工信息  
select \* from emp where sal > (select sal from emp where ename=’刘一’);

– 3．**\*** 列出所有员工的姓名及其直接上级的姓名。  
select e1.ename, e2.ename  
from emp as e1, emp as e2  
where e1.mgr=e2.empno;

– 4．列出受雇日期早于其直接上级的所有员工。  
select e1.\*  
from emp e1,emp e2  
where e1.hiredate