本文围绕数据库多表设计展开，介绍了多对多、一对多、一对一关系，阐述了外键约束的使用方法。通过学生成绩管理系统案例展示表创建，还讲解了多表查询，包括笛卡尔积、内连接、外连接等，最后给出学生成绩和部门员工查询的练习示例。
真实的数据表之间的关系：
多对多关系、一对多（多对一）、一对一(极少)。
-
- 多对多关系(开发最常用) 分析 设计
-- 创建程序员表
create table coder(
id int primary key auto_increment,
name varchar(50),
salary double
);
-- 创建项目表
create table project(
id int primary key auto_increment,
name varchar(50)
);
-- 创建中间关系表
create table coder_project(
coder_id int,
project_id int
);
-- 添加测试数据
insert into coder values(1,'张三',12000);
insert into coder values(2,'李四',15000);
insert into coder values(3,'王五',18000);
insert into project values(1,'QQ项目');
insert into project values(2,'微信项目');
insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);
使用中间表的目的是维护两表多对多的关系：
1 中间表插入的数据 必须在多对多的主表中存在。
2 如果主表的记录在中间表维护了关系，就不能随意删除。如果可以删除，中间表就找不到对应的数据了，这样就没有意义了。
创建第三张关系表，维护程序员和项目之间的关系
create table coder_project(
c_id int ,
p_id int
);
我们在创建第三张关系表时，表中的每一列，都是在使用其他表中的列，
这时我们需要对第三张表中的列进行相应的约束，
当前第三张表中的列由于都是引用其他表中的列，我们把第三张表中的这些列称为引用其他的外键约束。
给某个表中的某一列添加外键约束：
语法：foreign key( 当前表中的列名 ) references 被引用表名(被引用表的列名);
foreign key(coder_id) references coder(id);
给已经存在的表添加外键约束：
第一种方式：
alter table coder_project add foreign key(coder_id) references coder(id);
alter table coder_project add foreign key(project_id) references project(id);
第二种方式
create table coder_project(coder_id int,
project_id int,
foreign key(coder_id) references coder(id),
foreign key(project_id) references project(id)
);
-- 创建程序员表
create table coder(
id int primary key auto_increment,
name varchar(50),
salary double
);
-- 创建项目表
create table project(
id int primary key auto_increment,
name varchar(50)
);
-- 创建中间关系表
create table coder_project(
coder_id int,
project_id int
);
-- 添加测试数据
insert into coder values(1,'张三',12000);
insert into coder values(2,'李四',15000);
insert into coder values(3,'王五',18000);
insert into project values(1,'QQ项目');
insert into project values(2,'微信项目');
insert into coder_project values(1,1);
insert into coder_project values(1,2);
insert into coder_project values(2,1);
insert into coder_project values(2,2);
insert into coder_project values(3,2);
-- 中间表: 维护两张表的关系: 1 保证不能随便插 2 保证不能随便删
-- 缺点1: 能否向中间表 插入 不存在的项目编号和程序员编号
insert into coder_project values(30,20);
-- 缺点2 : 如果中间表存在程序员的编号, 能否删除程序员表对应的记录
delete from coder where id=1;
-- 清数据
truncate coder;
truncate project;
truncate coder_project;
-- 增加外键
alter table coder_project add foreign key(coder_id) references coder(id);
alter table coder_project add foreign key(project_id) references project(id);
外键约束：
A表引用B表的某一列，这时A表中的这一列需要使用外键约束。
一对多的关系表：其中也有2个实体，但是其中A实体中的数据可以对应另外B实体中的多个数据，反过来B实体中的多个数据只能对应A实体中的一个数据。
例如：作者和小说关系，老师和课程的关系等。
解释：
作者和小说：
一个作者可以写多部小说，但每一部小说，只能对应具体的一个作者。
一对一关系表在实际开发中使用的并不多，其中也是2个实体，其中A实体中的数据只能对应B实体中的一个数据，同时B实体中的数据也只能对应A实体中的一个数据。例如：人和身份证对应关系，公司CEO和公司对应关系。
而一对一在建表的时候，可以在任意一方的表中添加另外一方的主键作为外键即可。
需求：设计学生成绩管理系统数据表
1、每个教师可以教多门课程
2、每个课程由一个老师负责
3、每门课程可以由多个学生选修
4、每个学生可以选修多门课程
5、学生选修课程要有成绩
当我们拿到一个需求之后，首先应该分析这个需求中到底有多少名词，或者是当前这个需求中可以抽象出具体几个E-R图中的实体对象。
分析需求中存在的实体：
学生、课程、老师。
当分析清楚具体的实体之后，那么就要考虑实体和实体之间的关系问题：
学生和课程之间：多对多关系
课程和老师之间：一对多。
学生选课管理系统的表创建：
-- 1、教师表
-- 2、课程表
-- 3、学生表
-- 4、学生课程表
-- 1、教师表
create table teacher(
id int primary key auto_increment,
name varchar(50)
);
-- 2、课程表
create table course(
id int primary key auto_increment,
name varchar(50),
teacher_id int,
foreign key(teacher_id) references teacher(id)
);
-- 3、学生表
create table student(
id int primary key auto_increment,
name varchar(50)
);
-- 4、学生课程中间关系表
create table studentcourse(
student_id int,
course_id int,
score double,
foreign key(student_id) references student(id),
foreign key(course_id) references course(id)
);
多表查询(重点重点重点)
需求：查询学生的考试成绩，一张学生表可以查询到所有的信息吗？
一个项目中肯定会有多张数据表，而数据表之间会存在各种各样的关系。这时我们需要的数据，可能不会直接通过一张表全部获取到，这时就需要同时查询多张数据表，得到最后想要的数据。
准备工作：
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);
多表查询中的问题：
笛卡尔积问题：把多张表放在一起，同时去查询，会得到一个结果，而这结果并不是我们想要的数据，但这个结果称为笛卡尔积。
笛卡尔积的数据，对程序是没有意义的， 我们需要对笛卡尔积中的数据再次进行过滤。
对于多表查询操作，需要过滤出满足条件的数据，需要把多个表进行连接，连接之后需要加上过滤的条件。
内连接查询的结果：两表的公共部分。
内连接：
语法一：
select 列名 , 列名 .... from 表名1,表名2 where 表名1.列名 = 表名2.列名;
-- 需求：查询出每个水果的价格：
语法二：
select * from 表名1 inner join 表名2 on 条件
需求：查询出每个水果的价格：
外链接：左外连接、右外连接、全连接、自连接。
左外连接：用左边表去右边表中查询对应记录，不管是否找到，都将显示左边表中全部记录。
即：虽然右表没有香蕉对应的价格，也要把他查询出来。
语法：select * from 表1 left outer join 表2 on 条件;
-- 需求：不管能否查到水果对应的价格，都要把水果显示出来。
from user where id in (1,3,32)
用右边表去左边表查询对应记录，不管是否找到，右边表全部记录都将显示。
即：不管左方能够找到右方价格对应的水果，都要把左方的价格显示出来。
语法：select * from 表1 right outer join 表2 on 条件;
-- 需求：不管能否查到价格对应的水果，都要把价格显示出来。
全连接：
全外连接：左外连接和右外连接的结果合并，单会去掉重复的记录。
select * from 表1 full outer join 表2 on 条件
select * from a full outer join b on a.A_ID = b.A_ID; 但是mysql数据库不支持此语法。
在sql语句全连接，其实就是左外链接和右外连接之和，去掉重复的数据。这时可以使用union
-- 需求1：将左连接和右连接整合在一起显示出来。（使用union all）
-- 需求2：将左连接和右连接整合在一起 去除重复的 显示出来。（使用union）
-
- SQL关联子查询
子查询：把一个sql的查询结果作为另外一个查询的参数存在。
数据库中A水果表，B价格表。
-- 需求1：查询价格最贵的水果名称
准备工作：
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); |
-
-
- in和exists的用法：
-
关联子查询其他的关键字使用：
回忆：age=23 or age=24 等价于 age in (23,24)
in 表示条件应该是在多个列值中。
in：使用在where后面，经常表示是一个列表中的数据，只要被查询的数据在这个列表中存在即可。
-- 需求：查询不及格的学生(使用in完成)
exists：(扩展)
exists：表示存在，当子查询的结果存在，就会显示主查询中的 当前行的所有数据。
select *
from 表名
where exists (子查询语句);
where 列名=值;
使用exists完成：
-- 需求：查询不及格的学生(使用exists完成)
all、any和some的使用法
all 需要和 union 一起使用，如果在查询时， 单独使用union 可以把多个查询的结果进行合并， 会过滤掉重复的数据。如果union all 只会简单的把多个查询结果合并。
any 和 some用法一致：
SOME 是 SQL-92标准的ANY的等效物
any和some是没有区别的，some和any 效果一样 ，代表一部分记录。
any部分数据
问题1：>any(1,2,3)
问题2：<any(1,2,3) 等价于 <1 or <2 or <3 等价与 <3 等价于 <max(1,2,3)
all 所有数据
问题3: >all(1,2,3) 等价与 >1 and >2 and >3 等价与>3 等价与 >max(1,2,3)
问题4：< all(1,2,3) 等价于 <1 and <2 and <3 等价于 <1 等价与 <min(1,2,3)
-- 需求1：查询获得最高分的学生信息：
-- 需求2：查询编号2课程比编号1课程最高成绩高学生信息：
-- 需求2：查询编号2课程 比 编号1课程最高成绩高学生信息：
-- 2.1 在中间表 找编号1课程的最高成绩
select max(score)
from studentcourse
where course_id=1;
-- 2.2 在中间表 编号2的成绩 > 编号1最高成绩 的学生id
select student_id
from studentcourse
where course_id=2 and score>(select max(score)
from studentcourse
where course_id=1);
-- 2.3 在学生表 根据编号 找对应的学生信息
select *
from student
where id in (select student_id
from studentcourse
where course_id=2 and score>(select max(score)
from studentcourse
where course_id=1));
-- 需求3：查询编号2课程比编号1课程最高成绩高学生姓名和成绩 （临时表）
-- 需求3：查询编号2课程比编号1课程最高成绩高学生姓名和成绩 （临时表）
-- 2.1 在中间表 找编号1课程的最高成绩
select max(score)
from studentcourse
where course_id=1;
-- 2.2 在中间表 编号2的成绩 > 编号1最高成绩 的学生id,成绩
select student_id,score
from studentcourse
where course_id=2 and score>(select max(score)
from studentcourse
where course_id=1);
-- 2.3 将2.2作为临时表 和 学生表关联, 查询姓名和成绩
select student.name, temp.score
from student, (select student_id,score
from studentcourse
where course_id=2 and score>(select max(score)
from studentcourse
where course_id=1)) as temp
where student.id=temp.student_id;
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);
根据上述的4张表练习：
- 查询平均成绩大于70分的同学的学号和平均成绩
- 查询所有同学的学号、姓名、选课数、总成绩
- 查询学过赵云老师所教课的同学的学号、姓名
- 查询没学过关羽老师课的同学的学号、姓名
- 查询没有学三门课以上的同学的学号、姓名
- 查询各科成绩最高和最低的分
- 查询学生信息和平均成绩
- 查询上海和北京学生数量
- 查询不及格的学生信息和课程信息
- 统计每门课程的学生选修人数（超过四人的进行统计）
-- 1、查询平均成绩大于70分的同学的学号和平均成绩
-- 2、查询所有同学的学号、姓名、选课数、总成绩
|
-- 2、查询所有同学的学号、姓名、选课数、总成绩 -- 2.1 查询每个学生的选课数,总成绩 每个 --> 分组 --> 学号 select student_id,count(*),sum(score) from studentcourse group by student_id; -- 2.2 将2.1作为一个表来处理, 学号、姓名、选课数、总成绩 select student.id, student.name, temp.cou, temp.sumScore from student, (select student_id,count(*) as cou,sum(score) as sumScore from studentcourse group by student_id) as temp where student.id=temp.student_id;
|
-- 3、查询学过赵云老师所教课的同学的学号、姓名
|
-- 3、查询学过赵云老师所教课的同学的学号、姓名 -- 3.1 根据老师姓名 找 教师工号, 在教师表 select id from teacher where name='赵云' ; -- 3.2 根据教师工号 找 所教课程对应的 课程编号, 在课程表 select id from course where teacher_id=(select id from teacher where name='赵云') ; -- 3.3 根据课程编号 找 上课的学生编号, 在中间表 select student_id from studentcourse where course_id in (select id from course where teacher_id=(select id from teacher where name='赵云'));
-- 3.4 根据学生编号 找 对应的学号和姓名, 在学生表 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、查询没学过关羽老师课的同学的学号、姓名
|
-- 4、查询没学过关羽老师课的同学的学号、姓名 -- 4.1 根据老师姓名 找 教师工号, 在教师表 select id from teacher where name='关羽' ; -- 4.2 根据教师工号 找 所教课程对应的 课程编号, 在课程表 select id from course where teacher_id=(select id from teacher where name='关羽') ; -- 4.3 根据课程编号 找 上课的学生编号, 在中间表 select student_id from studentcourse where course_id in (select id from course where teacher_id=(select id from teacher where name='关羽'));
-- 4.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、查询没有学三门课以上的同学的学号、姓名 -- 5.1 查询每个学生选了几门课 每个 -- 分组 -- 学号,在中间表 select student_id, count(*) from studentcourse group by student_id; -- 5.2 增加条件 没有学三门课以上 等价于 count(*)<=3,查的是 学号 select student_id from studentcourse group by student_id having count(*)<=3; -- 5.3 根据学号 查询 学号和姓名, 学生表 select id,name from student where id in (select student_id from studentcourse group by student_id having count(*)<=3);
|
-- 6、查询各科成绩最高和最低的分
|
-- 6、查询各科成绩最高和最低的分 -- 6.1 查询每门课程的最高分 每门 --> 分组 --> 课程编号, 在中间表 select course_id,max(score),min(score) from studentcourse group by course_id; -- 6.2 将6.1作为临时表 和 课程表 根据课程编号 进行关联查询, -- 查询课程名称,最高分,最低分 select course.name, temp.maxScore, temp.minScore from course, (select course_id,max(score) as maxScore,min(score) as minScore from studentcourse group by course_id) as temp where course.id=temp.course_id; |
-- 7、查询学生信息和平均成绩
|
-- 7、查询学生信息和平均成绩 -- 7.1 查询每个学生的平均成绩 每个 --> 分组 --> 学号, 在中间表 select student_id, avg(score) from studentcourse group by student_id; -- 7.2 将7.1作为临时表 和 学生表 根据学号进行关联查询 学生信息和平均成绩 select student.*, temp.avgScore as 平均成绩 from student, (select student_id, avg(score) as avgScore from studentcourse group by student_id) as temp where student.id=temp.student_id
|
-- 8、查询上海和北京学生数量
|
-- 8、查询上海和北京学生数量 -- 8.1 查询每个城市的学生数量 每个 --> 分组 --> 城市city, 在学生表 select city, count(*) from student group by city; -- 8.2 方式一 上海 和 北京 select city, count(*) from student group by city having city in ('北京', '上海');
-- 8.2 方式二 上海 和 北京 select city, count(*) from student where city in ('北京', '上海') group by city ;
|
-- 9、查询不及格的学生信息和课程信息
|
-- 9、查询不及格的学生信息和课程信息 -- 9.1 查询不及格的学号和课程编号 在中间表 select student_id, course_id from studentcourse where score<60; -- 9.2 将9.1作为临时表根据学生表进行关联查询学生信息 select student.*, temp.* from student, (select student_id, course_id from studentcourse where score<60) as temp where student.id=temp.student_id -- 9.3 将9.2作为临时表根据课程表进行关联查询课程信息 select student.*, course.* from student, course, (select student_id, course_id from studentcourse where score<60) as temp where student.id=temp.student_id and course.id=temp.course_id
|
-- 10、统计每门课程的学生选修人数（超过四人的进行统计）
|
-- 10、统计每门课程的学生选修人数（超过四人的进行统计） -- 10.1 查询每门课程的选修人数 每门 ==> 分组 ===> 课程编号,在中间表 select course_id, count(*) from studentcourse group by course_id; -- 10.2 超过四人的进行统计 count(*)>4 -- select 6 -- from 1 -- where 2 -- group by 3 -- 注意: 聚合函数只能出现被分组之后 -- having 4 -- order by 5 select course_id, count(*) from studentcourse group by course_id having count(*)>4 |
-
- 部门员工查询练习oracle
准备工作：
-- 部门表
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中员工的薪金的所有员工的姓名和薪金。
-- 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之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）
-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
-- 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
|
-- 34.删除10号部门薪水最高的员工。 -- 34.1 查询10号部门最高薪水(1个) select max(sal) from emp where deptno=10; -- 34.2 查询最高薪水 对应 员工编号(多个) select empno from emp where sal=(select max(sal) from emp where deptno=10); -- 34.3 规定: 更新表 主表 和 从表不能是同一个表 -- 解决: 使用临时表 间接解决 select * from (select empno from emp where sal=(select max(sal) from emp where deptno=10)) as temp;
-- 34.4 根据员工编号 删除 员工信息 -- 规定: 更新表 主表 和 从表不能是同一个表 -- 解决: 使用临时表 间接解决 delete from emp where empno in (select * from (select empno from emp where sal=(select max(sal) from emp where deptno=10)) as temp);
|
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
