下面表格的关系
班级表关联着学生表
创建班级表： 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;
标签：题目,数据库,student,employee,post,where,id,select From： https://www.cnblogs.com/socialism/p/17090314.html