数据库题目

下面表格的关系

班级表关联着学生表

创建班级表： create table class(cid int not null unique auto_increment,caption char(18));

教师表和班级表关联着课程表

创建教师表: create table teacher(tid int not null unique auto_increment, tname char(12) not null);

课程表关联着成绩表

创建学生表: create table student(sid int unique not null auto_increment,sname char(12) not null,gender enum("female","male"") not null,class_id int not null,foreign key(class_id) references class(cid));

创建课程表: create table course(cid int not null,cname char(12),teacher_id int,foreign key(cid) references class(cid),foreign key(teacher_id) references teacher(tid));

创建成绩表: create table score(sid int primary key auto_increment,student_id int,course_id int,number int not null,foreign key(s))

作业查看表
# 准备工作创建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);

# 创建数据
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
1.查出所有员工的名字，薪资,格式为<名字:egon> <薪资:3000>

2.查出所有的岗位（去掉重复）

3.查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year

1.查看岗位是teacher的员工姓名、年龄

答案： select emp_name,age from employee where post="teacher"; 2.查看岗位是teacher且年龄大于30岁的员工姓名、年龄

答案：select emp_name,age from employee where post="teacher"and age>30;

3.查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资

答案：select emp_name,age,salary from employee where post="teacher" and (salary between 9000 and 10000);

4.查看岗位描述不为NULL的员工信息

答案： select * from employee where post_comment is not null;

5.查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资

答案： select emp_name,age,salary from employee where post="teacher" and salary in(10000,9000,30000);

6.查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资

答案：select emp_name,age,salary from employee where salary not in(10000,9000,30000)and post="teacher";

7.查看岗位是teacher且名字是jin开头的员工姓名、年薪

答案：select emp_name,salary from employee where like "jin%";

分组聚合 group by
1.查询岗位名以及岗位包含的所有员工名字

答案：select post,group_concat(emp_name) from employee group by post; 2.查询岗位名以及各岗位内包含的员工个数

答案：select post,count(id) from employee group by post; 3.查询公司内男员工和女员工的个数

答案：select sex,count(id) from employee group by sex; 4.查询岗位名以及各岗位的平均薪资

答案：select post,avg(salary) from employee group by post; 5.查询岗位名以及各岗位的最高薪资

答案：select post,max(salary) from employee group by post; 6.查询岗位名以及各岗位的最低薪资

答案：select post,min(salary) from employee group by post; 7.查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

答案：select group_concat(emp_name),sex,avg(salary) from employee group by sex; 查询各岗位平均薪资大于一万的岗位名、平均工资

答案：select post,avg(salary) from employee group by post having avg(salary)>10000;

查询各岗位平均工资大于一万且小于两万的岗位名平均工资

答案： select post,avg(salary) from employee group by post having 10000<avg(salary)and avg(salary)<20000;

作业（having过滤）
1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数

答案：select post,group_concat(emp_name),count(id) from employee group by post having count(id)<2;

2.查询各岗位平均薪资大于10000的岗位名、平均工资

答案：select post,avg(salary) from employee group by post having avg(salary)>10000;

3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

答案：select post,avg(salary) from employee group by pos0t having avg(salary) > 10000 and avg(salary) < 20000;

作业order by查询排序
1.查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序

答案：select * from employee order by age,hire_date desc;

2.查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列

答案：select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) asc;

3.查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列

答案：select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc;

作业limit
分页显示，每页5条

连表查询
1.找到技术部所有人的姓名

答案：select employee.name from employee left join department on employee.dep_id=department.id where department.id=200;

2.找到人力资源部年龄大于40岁的人的名字。

select employee.name from employee left join department on employee.dep_id=department.id where department.id=201 and employee.age>40;

3.以内连接的方式查询联表，并以age字段进行升序排列

答案：select * from employee inner join department on employee.dep_id=department.id order by age asc;

4.求每一个部门有多少人

select department.name,count(employee.id) from employee inner join department on employee.dep_id=department.id group by department.name;

5.（子查询）查询平均年龄在25岁以上的部门名

答案：select department.name,avg(age) from employee inner join department on employee.dep_id=department.id group by department.name having avg(age)>25

6.(子查询)查看技术部员工姓名

先查看技术部部门id

select id from department where name="技术"

再根据部门id找到对应的员工名

select name from employee where dep_id in(select id from department where name="技术");

7.查看不足一人的部门名 对不同部门进行分组，计算每一个部门的人数 select dep_id from employee group by dep_id having count(name)<1

select name from department where id in (select dep_id from employee group by dep_id having count(name)<1);

8.查询大于所有人平均年龄的员工年龄与员工姓名 select avg(age) from employee

select age,name from employee where age>(select avg(age) from employee);

9.查询大于部门内平均年龄的员工姓名和年龄 这个题要使用连表查询来做 先算出部门的平均年龄，再将两张表连接在一起。

答案：select * from employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id=t2.dep_id where age>avg_age;

综合练习
1、查询男生、女生的人数；

答案：select gender,count(sid) from student group by gender;

2、查询姓“张”的学生名单；

答案：select * from student where sname like "张%";

3、课程平均分从高到低显示

答案：select cname,avg_num from (select course_id,avg(num) as avg_num from score group by course_id) as t1 inner join course on cid=t1.course_id order by avg_num desc;

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

答案：select student.sid,sname from student inner join score on student.sid=student_id where num<60;

5、查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；

答案：select student_id,sname from student inner join (select distinct student_id from score where course_id in (select course_id from score where student_id=1)) as t1 on student_id=student.sid;

6、查询出只选修了一门课程的全部学生的学号和姓名；

select student.sid,sname from (select * from score group by student_id having count(course_id)=1) as t1 inner join student on student.sid=student_id;

7、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

答案：select course_id,max(num),min(num) from score group by course_id having max(num) or min(num);

8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

思路：找到所有学生编号为一的成绩，找到编号为二的所有学生的成绩，将两张表连接起来。 select * from (select * from score where course_id=2) as t1 inner join (select * from score where course_id=1) as t2 on t1.student_id=t2.student_id where t1.num<t2.num;

9、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

答案： select t1.student_id from (select * from score where course_id=1) as t1 inner join (select * from score where course_id=2) as t2 on t1.student_id=t2.student_id where t1.num>t2.num;

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

select student_id,avg(num) from score group by student_id having avg(num)>60;

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

select student_id,sname,count(course_id),sum(num) from student inner join score on student.sid=score.student_id group by student_id;

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

select count(tid) from teacher where tname like "李%";

13、查询没学过“张磊老师”课的同学的学号、姓名；

思路：先找到张磊老师教什么课程，再将学过张磊老师的课程的学生找出来，再将学过张磊老师课程的学生去除

答案： select sid,sname from student where sid not in(select student_id from score where course_id=(select cid from teacher inner join course on teacher_id=tid where tname="张磊老师")); 14、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

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

select student_id,sname from student inner join (select distinct student_id from score where course_id in (select cid from course inner join teacher on tid=teacher_id where tname="李平老师") group by student_id having count(*)=2) as t1 on student_id=sid;