英文字体展示网站推荐,php商城网站开发实例视频教程,变身wordpress,网站建设计什么费用多表关系#xff1a; 项目开发中#xff0c;在进行数据库表结构设计时#xff0c;会根据业务需求及业务模块之间的关系#xff0c;分析并设计表结构。由于业务之间相互关联#xff0c;所以各个表结构之间也存在着各种联系#xff0c;基本上分为三种。 外键#xff1a; 创…
多表关系 项目开发中在进行数据库表结构设计时会根据业务需求及业务模块之间的关系分析并设计表结构。由于业务之间相互关联所以各个表结构之间也存在着各种联系基本上分为三种。 外键 创建表时添加外键 -- 创建表时指定 create table 表名( 字段名 数据类型, ... [constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名) ); 示例SQL #创建一个用户表
create table tb_user(id int unsigned primary key auto_increment comment ID,name varchar(10) not null comment 姓名,gender tinyint unsigned not null comment 性别, 1 男 2 女
) comment 用户信息表;# 创建一个 用户详细信息表
create table tb_user_card(id int unsigned primary key auto_increment comment ID,nationality varchar(10) not null comment 民族,birthday date not null comment 生日,idcard char(18) not null comment 身份证号,issued varchar(20) not null comment 签发机关,expire_begin date not null comment 有效期限-开始,expire_end date comment 有效期限-结束,user_id int unsigned not null unique comment 用户ID,constraint fk_user_id foreign key (user_id) references tb_user(id) //创建外键
) comment 用户信息表; 创建完表之后添加外键 -- 建完表后添加外键 alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名); # 给emp表添加一个外键 一个部门对应多个员工一个员工所属一个部门dept表是父表emp表是子表
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id); 多表操作中保证数据的一致性完整性和正确性。 物理外键 概念使用foreign key定义外键关联另外一张表。 缺点 影响增删改的效率需要检查外键关系 仅用于单节点数据库不适用于分布式集群场景 容易引发数据库死锁问题消耗性能 逻辑外键 概念在业务逻辑层中解决外键关联。 通过逻辑外键就可以很方便的解决上述问题。 一对多多对一 部门和员工的关系一个部门有多个员工 在数据库表中多的一方添加字段来关联一的一方的主键。 emp表 dept表 SQL代码示例
CREATE TABLE dept (id int unsigned PRIMARY KEY AUTO_INCREMENT COMMENT ID, 主键,name varchar(10) NOT NULL UNIQUE COMMENT 部门名称,create_time datetime DEFAULT NULL COMMENT 创建时间,update_time datetime DEFAULT NULL COMMENT 修改时间
) COMMENT 部门表;INSERT INTO dept VALUES (1,学工部,2023-09-25 09:47:40,2023-09-25 09:47:40),(2,教研部,2023-09-25 09:47:40,2023-10-09 15:17:04),(3,咨询部2,2023-09-25 09:47:40,2023-11-30 21:26:24),(4,就业部,2023-09-25 09:47:40,2023-09-25 09:47:40),(5,人事部,2023-09-25 09:47:40,2023-09-25 09:47:40),(15,行政部,2023-11-30 20:56:37,2023-11-30 20:56:37);create table emp(id int unsigned primary key auto_increment comment ID,主键,username varchar(20) not null unique comment 用户名,password varchar(50) default 123456 comment 密码,name varchar(10) not null comment 姓名,gender tinyint unsigned not null comment 性别, 1:男, 2:女,phone char(11) not null unique comment 手机号,job tinyint unsigned comment 职位, 1 班主任, 2 讲师 , 3 学工主管, 4 教研主管, 5 咨询师,salary int unsigned comment 薪资,image varchar(300) comment 头像,entry_date date comment 入职日期,dept_id int unsigned comment 部门ID,create_time datetime comment 创建时间,update_time datetime comment 修改时间
) comment 员工表;INSERT INTO emp VALUES
(1,shinaian,123456,施耐庵,1,13309090001,4,15000,5.png,2000-01-01,2,2023-10-20 16:35:33,2023-11-16 16:11:26),
(2,songjiang,123456,宋江,1,13309090002,2,8600,01.png,2015-01-01,2,2023-10-20 16:35:33,2023-10-20 16:35:37),
(3,lujunyi,123456,卢俊义,1,13309090003,2,8900,01.png,2008-05-01,2,2023-10-20 16:35:33,2023-10-20 16:35:39),
(4,wuyong,123456,吴用,1,13309090004,2,9200,01.png,2007-01-01,2,2023-10-20 16:35:33,2023-10-20 16:35:41),
(5,gongsunsheng,123456,公孙胜,1,13309090005,2,9500,01.png,2012-12-05,2,2023-10-20 16:35:33,2023-10-20 16:35:43),
(6,huosanniang,123456,扈三娘,2,13309090006,3,6500,01.png,2013-09-05,1,2023-10-20 16:35:33,2023-10-20 16:35:45),
(7,chaijin,123456,柴进,1,13309090007,1,4700,01.png,2005-08-01,1,2023-10-20 16:35:33,2023-10-20 16:35:47),
(8,likui,123456,李逵,1,13309090008,1,4800,01.png,2014-11-09,1,2023-10-20 16:35:33,2023-10-20 16:35:49),
(9,wusong,123456,武松,1,13309090009,1,4900,01.png,2011-03-11,1,2023-10-20 16:35:33,2023-10-20 16:35:51),
(10,linchong,123456,林冲,1,13309090010,1,5000,01.png,2013-09-05,1,2023-10-20 16:35:33,2023-10-20 16:35:53),
(11,huyanzhuo,123456,呼延灼,1,13309090011,2,9700,01.png,2007-02-01,2,2023-10-20 16:35:33,2023-10-20 16:35:55),
(12,xiaoliguang,123456,小李广,1,13309090012,2,10000,01.png,2008-08-18,2,2023-10-20 16:35:33,2023-10-20 16:35:57),
(13,yangzhi,123456,杨志,1,13309090013,1,5300,01.png,2012-11-01,1,2023-10-20 16:35:33,2023-10-20 16:35:59),
(14,shijin,123456,史进,1,13309090014,2,10600,01.png,2002-08-01,2,2023-10-20 16:35:33,2023-10-20 16:36:01),
(15,sunerniang,123456,孙二娘,2,13309090015,2,10900,01.png,2011-05-01,2,2023-10-20 16:35:33,2023-10-20 16:36:03),
(16,luzhishen,123456,鲁智深,1,13309090016,2,9600,01.png,2010-01-01,2,2023-10-20 16:35:33,2023-10-20 16:36:05),
(17,liying,12345678,李应,1,13309090017,1,5800,01.png,2015-03-21,1,2023-10-20 16:35:33,2023-10-20 16:36:07),
(18,shiqian,123456,时迁,1,13309090018,2,10200,01.png,2015-01-01,2,2023-10-20 16:35:33,2023-10-20 16:36:09),
(19,gudasao,123456,顾大嫂,2,13309090019,2,10500,01.png,2008-01-01,2,2023-10-20 16:35:33,2023-10-20 16:36:11),
(20,ruanxiaoer,123456,阮小二,1,13309090020,2,10800,01.png,2018-01-01,2,2023-10-20 16:35:33,2023-10-20 16:36:13),
(21,ruanxiaowu,123456,阮小五,1,13309090021,5,5200,01.png,2015-01-01,3,2023-10-20 16:35:33,2023-10-20 16:36:15),
(22,ruanxiaoqi,123456,阮小七,1,13309090022,5,5500,01.png,2016-01-01,3,2023-10-20 16:35:33,2023-10-20 16:36:17),
(23,ruanji,123456,阮籍,1,13309090023,5,5800,01.png,2012-01-01,3,2023-10-20 16:35:33,2023-10-20 16:36:19),
(24,tongwei,123456,童威,1,13309090024,5,5000,01.png,2006-01-01,3,2023-10-20 16:35:33,2023-10-20 16:36:21),
(25,tongmeng,123456,童猛,1,13309090025,5,4800,01.png,2002-01-01,3,2023-10-20 16:35:33,2023-10-20 16:36:23),
(26,yanshun,123456,燕顺,1,13309090026,5,5400,01.png,2011-01-01,3,2023-10-20 16:35:33,2023-11-08 22:12:46),
(27,lijun,123456,李俊,1,13309090027,2,6600,8.png,2004-01-01,2,2023-10-20 16:35:33,2023-11-16 17:56:59),
(28,lizhong,123456,李忠,1,13309090028,5,5000,6.png,2007-01-01,3,2023-10-20 16:35:33,2023-11-17 16:34:22),
(30,liyun,123456,李云,1,13309090030,NULL,NULL,01.png,2020-03-01,NULL,2023-10-20 16:35:33,2023-10-20 16:36:31),
(36,linghuchong,123456,令狐冲,1,18809091212,2,6800,1.png,2023-10-19,2,2023-10-20 20:44:54,2023-11-09 09:41:04);
给emp表添加外键约束 # 添加外键约束 alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id); 一对一 案例用户和身份证的信息 关系一对一关系多用于单表拆分将一张表的基础字段放在一张表中其他字段放在另一张表中以提升操作效率。 实现在任意一方加入外键关联另一方的主键并且设置外键为唯一的unique -- 一对一
create table tb_user(id int unsigned primary key auto_increment comment ID,name varchar(10) not null comment 姓名,gender tinyint unsigned not null comment 性别, 1 男 2 女,phone char(11) comment 手机号,degree varchar(10) comment 学历
) comment 用户信息表;insert into tb_user values (1,白眉鹰王,1,18812340001,初中),(2,青翼蝠王,1,18812340002,大专),(3,金毛狮王,1,18812340003,初中),(4,紫衫龙王,2,18812340004,硕士);create table tb_user_card(id int unsigned primary key auto_increment comment ID,nationality varchar(10) not null comment 民族,birthday date not null comment 生日,idcard char(18) not null comment 身份证号,issued varchar(20) not null comment 签发机关,expire_begin date not null comment 有效期限-开始,expire_end date comment 有效期限-结束,user_id int unsigned not null unique comment 用户ID,constraint fk_user_id foreign key (user_id) references tb_user(id)
) comment 用户信息表;insert into tb_user_card values (1,汉,1960-11-06,100000100000100001,朝阳区公安局,2000-06-10,null,1),(2,汉,1971-11-06,100000100000100002,静安区公安局,2005-06-10,2025-06-10,2),(3,汉,1963-11-06,100000100000100003,昌平区公安局,2006-06-10,null,3),(4,回,1980-11-06,100000100000100004,海淀区公安局,2008-06-10,2028-06-10,4); 多对多 案例学生与课程的关系 关系一个学生可以选修多门课程一门课程可以供多个学生选择。 实现建立第三张表中间表至少包含两个外键分别关联两方的主键。 -- 多对多
create table tb_student(id int auto_increment primary key comment 主键ID,name varchar(10) comment 姓名,no varchar(10) comment 学号
) comment 学生表;
insert into tb_student(name, no) values (黛绮丝, 2000100101),(谢逊, 2000100102),(殷天正, 2000100103),(韦一笑, 2000100104);create table tb_course(id int auto_increment primary key comment 主键ID,name varchar(10) comment 课程名称
) comment 课程表;
insert into tb_course (name) values (Java), (PHP), (MySQL) , (Hadoop);create table tb_student_course(id int auto_increment comment 主键 primary key,student_id int not null comment 学生ID,course_id int not null comment 课程ID,constraint fk_courseid foreign key (course_id) references tb_course (id),constraint fk_studentid foreign key (student_id) references tb_student (id)
)comment 学生课程中间表;insert into tb_student_course(student_id, course_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,4); 表关系设计案例 根据下图需求设计一个员工的工作经历表 分析的知一个员工可以有多段的工作经历所以员工和工作经历对应的表关系是一对多。 create table emp_expr(id int unique primary key auto_increment comment ID,begin_date date comment 开始时间,end_date date comment 结束时间,name varchar(50) comment 公司名称,job varchar(50) comment 职位,emp_id int unique comment 员工ID #这个字段绑定的是员工表的主键ID
)comment 工作经历表;
添加外键SQL语句 #给员工的工作经历表添加一个外键绑定的是 员工表的主键id
alter table emp_expr add constraint fk_emp_expr_emp_id foreign key(emp_id) references dept(id); 多表查询
准备多表查询的数据
-- 部门管理
create table dept(id int unsigned primary key auto_increment comment ID, 主键,name varchar(10) not null unique comment 部门名称,create_time datetime comment 创建时间,update_time datetime comment 修改时间
) comment 部门表 ;insert into dept (id, name, create_time, update_time) values(1,学工部,now(),now()),(2,教研部,now(),now()),(3,咨询部,now(),now()),(4,就业部,now(),now()),(5,人事部,now(),now());-- 员工管理
create table emp(id int unsigned primary key auto_increment comment ID,主键,username varchar(20) not null unique comment 用户名,password varchar(32) not null comment 密码,name varchar(10) not null comment 姓名,gender tinyint unsigned not null comment 性别, 1:男, 2:女,phone char(11) not null unique comment 手机号,job tinyint unsigned comment 职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师,salary int unsigned comment 薪资,image varchar(300) comment 头像,entry_date date comment 入职日期,dept_id int unsigned COMMENT 关联的部门ID,create_time datetime comment 创建时间,update_time datetime comment 修改时间
) comment 员工表;-- 准备测试数据
INSERT INTO emp VALUES (1,shinaian,123456,施耐庵,1,13309090001,4,15000,01.png,2000-01-01,2,2023-10-27 16:35:33,2023-10-27 16:35:35),(2,songjiang,123456,宋江,1,13309090002,2,8600,01.png,2015-01-01,2,2023-10-27 16:35:33,2023-10-27 16:35:37),(3,lujunyi,123456,卢俊义,1,13309090003,2,8900,01.png,2008-05-01,2,2023-10-27 16:35:33,2023-10-27 16:35:39),(4,wuyong,123456,吴用,1,13309090004,2,9200,01.png,2007-01-01,2,2023-10-27 16:35:33,2023-10-27 16:35:41),(5,gongsunsheng,123456,公孙胜,1,13309090005,2,9500,01.png,2012-12-05,2,2023-10-27 16:35:33,2023-10-27 16:35:43),(6,huosanniang,123456,扈三娘,2,13309090006,3,6500,01.png,2013-09-05,1,2023-10-27 16:35:33,2023-10-27 16:35:45),(7,chaijin,123456,柴进,1,13309090007,1,4700,01.png,2005-08-01,1,2023-10-27 16:35:33,2023-10-27 16:35:47),(8,likui,123456,李逵,1,13309090008,1,4800,01.png,2014-11-09,1,2023-10-27 16:35:33,2023-10-27 16:35:49),(9,wusong,123456,武松,1,13309090009,1,4900,01.png,2011-03-11,1,2023-10-27 16:35:33,2023-10-27 16:35:51),(10,lichong,123456,林冲,1,13309090010,1,5000,01.png,2013-09-05,1,2023-10-27 16:35:33,2023-10-27 16:35:53),(11,huyanzhuo,123456,呼延灼,1,13309090011,2,9700,01.png,2007-02-01,2,2023-10-27 16:35:33,2023-10-27 16:35:55),(12,xiaoliguang,123456,小李广,1,13309090012,2,10000,01.png,2008-08-18,2,2023-10-27 16:35:33,2023-10-27 16:35:57),(13,yangzhi,123456,杨志,1,13309090013,1,5300,01.png,2012-11-01,1,2023-10-27 16:35:33,2023-10-27 16:35:59),(14,shijin,123456,史进,1,13309090014,2,10600,01.png,2002-08-01,2,2023-10-27 16:35:33,2023-10-27 16:36:01),(15,sunerniang,123456,孙二娘,2,13309090015,2,10900,01.png,2011-05-01,2,2023-10-27 16:35:33,2023-10-27 16:36:03),(16,luzhishen,123456,鲁智深,1,13309090016,2,9600,01.png,2010-01-01,2,2023-10-27 16:35:33,2023-10-27 16:36:05),(17,liying,12345678,李应,1,13309090017,1,5800,01.png,2015-03-21,1,2023-10-27 16:35:33,2023-10-27 16:36:07),(18,shiqian,123456,时迁,1,13309090018,2,10200,01.png,2015-01-01,2,2023-10-27 16:35:33,2023-10-27 16:36:09),(19,gudasao,123456,顾大嫂,2,13309090019,2,10500,01.png,2008-01-01,2,2023-10-27 16:35:33,2023-10-27 16:36:11),(20,ruanxiaoer,123456,阮小二,1,13309090020,2,10800,01.png,2018-01-01,2,2023-10-27 16:35:33,2023-10-27 16:36:13),(21,ruanxiaowu,123456,阮小五,1,13309090021,5,5200,01.png,2015-01-01,3,2023-10-27 16:35:33,2023-10-27 16:36:15),(22,ruanxiaoqi,123456,阮小七,1,13309090022,5,5500,01.png,2016-01-01,3,2023-10-27 16:35:33,2023-10-27 16:36:17),(23,ruanji,123456,阮籍,1,13309090023,5,5800,01.png,2012-01-01,3,2023-10-27 16:35:33,2023-10-27 16:36:19),(24,tongwei,123456,童威,1,13309090024,5,5000,01.png,2006-01-01,3,2023-10-27 16:35:33,2023-10-27 16:36:21),(25,tongmeng,123456,童猛,1,13309090025,5,4800,01.png,2002-01-01,3,2023-10-27 16:35:33,2023-10-27 16:36:23),(26,yanshun,123456,燕顺,1,13309090026,5,5400,01.png,2011-01-01,3,2023-10-27 16:35:33,2023-10-27 16:36:25),(27,lijun,123456,李俊,1,13309090027,5,6600,01.png,2004-01-01,3,2023-10-27 16:35:33,2023-10-27 16:36:27),(28,lizhong,123456,李忠,1,13309090028,5,5000,01.png,2007-01-01,3,2023-10-27 16:35:33,2023-10-27 16:36:29),(29,songqing,123456,宋清,1,13309090029,NULL,5100,01.png,2020-01-01,NULL,2023-10-27 16:35:33,2023-10-27 16:36:31),(30,liyun,123456,李云,1,13309090030,NULL,NULL,01.png,2020-03-01,NULL,2023-10-27 16:35:33,2023-10-27 16:36:31); 多表查询就是从多张表中查询数据 笛卡尔积笛卡尔乘积笛卡尔乘积是指在数学中两个集合A集合与B集合的所有组合情况。 内连接 相当于查询 AB交集部分的数据 上面的案例中给员工表添加了一个外键要是有的员工表中的dept_id 为null那么就不会显示这些员工的dept_id 为null 的数据。 语法
内连接查询的是两张表交集部分的数据。 1 隐式内连接 select 字段列表 -- 内连接
-- A. 查询所有员工的ID, 姓名 , 及所属的部门名称 (隐式、显式内连接实现)# 隐式
select emp.id,emp.name,dept.name from emp,dept where emp.dept_id dept.id;#显式
select emp.id,emp.name,dept.name from emp join dept on emp.dept_id dept.id;-- B. 查询 性别为男, 且工资 高于8000 的员工的ID, 姓名, 及所属的部门名称 (隐式、显式内连接实现)#隐式
select emp.id,emp.name,dept.name from emp,dept where emp.dept_id dept.id and emp.gender 1 and emp.salary 8000;#显式
select emp.id,emp.name,dept.name from emp join dept on emp.dept_id dept.id where emp.gender 1 and emp.salary 8000;# 给表起别名# 使用as关键字
select e.id,e.name,d.name from emp as e join dept as d on e.dept_id d.id;# as关键字可以省略
select e.id,e.name,d.name from emp e join dept d on e.dept_id d.id; 外连接 左外连接查询左表所有数据包括两张表交集部分数据 如下图红圈选中的数据。 语法 -- 左外连接常用 select 字段列表 from 表1 left [outer] join 表2 on 连接条件.... 右外连接查询右表所有数据包括两张表交集部分数据 如下图红圈选中的数据。 语法 -- 右外连接 select 字段列表 from 表1 right [outer] join 表2 on 连接条件 示例 -- 外连接
-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)select e.name,d.name from emp e left join dept d on e.dept_id d.id;-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)select e.name,d.name from emp e right join dept d on e.dept_id d.id;# 可以改成左外连接select e.name,d.name from dept d left join emp e on e.dept_id d.id;
-- C. 查询工资 高于8000 的 所有员工的姓名, 和对应的部门名称 (左外连接)select e.name,d.name from emp e left join dept d on e.dept_id d.id where e.salary 8000;
温馨提示 对于外连接常用的是左外连接因为右外连接的SQL也可以改造成为左外连接两张表调换个顺序。 子查询
介绍 SQL语句中嵌套select语句称为嵌套查询又称子查询。 形式 select * from t1 where column1 (select column1 from t2 …); 说明 子查询外部的语句可以是insert / update / delete / select 的任何一个最常见的是 select。 分类
标量子查询 子查询返回的结果是单个值 列子查询 子查询返回的结果是一列可以有多行。 行子查询 子查询返回的结果是一行可以是多列。 表子查询 子查询返回的结果是多行多列。 示例 -- 子查询
# 在写子查询SQL的时候我们一般先拆分需求最后合并成一条SQL语句即可
-- 标量子查询
-- A. 查询 最早入职 的员工信息#先查询 最早入职的时间select min(entry_date) from emp;# 根据上面的到的最早入职时间查询员工的信息select * from emp where entry_date 2000-01-01;# 合并SQL语句select * from emp where entry_date ( select min(entry_date) from emp);-- B. 查询在 阮小五 入职之后入职的员工信息# 先查询 ‘阮小五的入职时间’select entry_date from emp where name 阮小五; -- 2015-01-01# 根据阮小五的入职时间查询之后入职的员工select * from emp where entry_date 2015-01-01;# 拼接SQLselect * from emp where entry_date (select entry_date from emp where name 阮小五);-- 列子查询
-- A. 查询 教研部 和 咨询部 的所有员工信息# 先查询到教研部和咨询部的部门idselect id from dept where name in (教研部,咨询部); -- 3,2#根据上面查到的部门id 查询里面包含的员工信息select * from emp where dept_id in (3,2);#合并select * from emp where dept_id in (select id from dept where name in (教研部,咨询部));-- 行子查询
-- A. 查询与 李忠 的薪资 及 职位都相同的员工信息 ;# 先查询 李忠的薪资和职位select salary,job from emp;# 在根据李忠的薪资和职位 查询与此相同的员工信息select * from emp where (salary,job) in (5000,5);# 合并select * from emp where (salary,job) in (select salary,job from emp);-- 表子查询
-- A. 查询入职日期是 2006-01-01 之后的员工信息 , 及其部门信息# 先查询入职日期是‘2006-01-01’之后的员工信息select * from emp where entry_date 2006-01-01;#根据上面的入职信息 查询部门信息select e.*,d.name from ( select * from emp where entry_date 2006-01-01) e,dept d where e.dept_id d.id;
连接查询和子查询的案例
-- 1. 查询 教研部 的 男性 员工且在 2011-05-01 之后入职的员工信息 。select * from emp e,dept d where e.dept_id d.id and d.name 教研部 and e.gender 1 and e.entry_date 2011-05--1;-- 2. 查询工资 低于公司平均工资的 且 性别为男 的员工信息 。# 查询公司的平均工资
select avg(salary) from emp;select * from emp p where p.salary (select avg(salary) from emp);-- 3. 查询工资 低于本部门平均工资的员工信息 。select * from emp e where e.salary (select avg(salary) from emp where dept_id e.dept_id);-- 4. 查询部门人数超过 10 人的部门名称 。select d.name,count(*) from dept d,emp e where d.id e.dept_id group by d.name having count(*) 10;再来一波猛的练习
提供查询的数据表
-- 部门管理
create table tb_dept(id int unsigned primary key auto_increment comment 主键ID,name varchar(10) not null unique comment 部门名称,create_time datetime not null comment 创建时间,update_time datetime not null comment 修改时间
) comment 部门表;insert into tb_dept (id, name, create_time, update_time) values(1,学工部,now(),now()),(2,教研部,now(),now()),(3,咨询部,now(),now()), (4,就业部,now(),now()),(5,人事部,now(),now());-- 员工管理
create table tb_emp (id int unsigned primary key auto_increment comment ID,username varchar(20) not null unique comment 用户名,password varchar(32) default 123456 comment 密码,name varchar(10) not null comment 姓名,gender tinyint unsigned not null comment 性别, 说明: 1 男, 2 女,image varchar(300) comment 图像,job tinyint unsigned comment 职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师,entrydate date comment 入职时间,dept_id int unsigned comment 部门ID,salary int unsigned comment 工资,create_time datetime not null comment 创建时间,update_time datetime not null comment 修改时间
) comment 员工表;INSERT INTO tb_emp(id, username, password, name, gender, image, job, entrydate,dept_id,salary, create_time, update_time) VALUES(1,jinyong,123456,金庸,1,1.jpg,4,2000-01-01,2,20000,now(),now()),(2,zhangwuji,123456,张无忌,1,2.jpg,2,2015-01-01,2,18000,now(),now()),(3,yangxiao,123456,杨逍,1,3.jpg,2,2008-05-01,2,16800,now(),now()),(4,weiyixiao,123456,韦一笑,1,4.jpg,2,2007-01-01,2,12000,now(),now()),(5,changyuchun,123456,常遇春,1,5.jpg,2,2012-12-05,2,9000,now(),now()),(6,xiaozhao,123456,小昭,2,6.jpg,3,2013-09-05,1,6000,now(),now()),(7,jixiaofu,123456,纪晓芙,2,7.jpg,1,2005-08-01,1,6500,now(),now()),(8,zhouzhiruo,123456,周芷若,2,8.jpg,1,2014-11-09,1,7200,now(),now()),(9,dingminjun,123456,丁敏君,2,9.jpg,1,2011-03-11,1,5300,now(),now()),(10,zhaomin,123456,赵敏,2,10.jpg,1,2013-09-05,1,12000,now(),now()),(11,luzhangke,123456,鹿杖客,1,11.jpg,5,2007-02-01,3,8900,now(),now()),(12,hebiweng,123456,鹤笔翁,1,12.jpg,5,2008-08-18,3,7800,now(),now()),(13,fangdongbai,123456,方东白,1,13.jpg,5,2012-11-01,3,6800,now(),now()),(14,zhangsanfeng,123456,张三丰,1,14.jpg,2,2002-08-01,2,15800,now(),now()),(15,yulianzhou,123456,俞莲舟,1,15.jpg,2,2011-05-01,2,11500,now(),now()),(16,songyuanqiao,123456,宋远桥,1,16.jpg,2,2007-01-01,2,8300,now(),now()),(17,chenyouliang,123456,陈友谅,1,17.jpg,NULL,2015-03-21,NULL,4500,now(),now());-- 薪资等级表
create table tb_salgrade(grade int comment 等级,losal int comment 该等级最低薪资,hisal int comment 该等级最高薪资
) comment 薪资等级表;insert into tb_salgrade values (1,0,3000);
insert into tb_salgrade values (2,3001,5000);
insert into tb_salgrade values (3,5001,8000);
insert into tb_salgrade values (4,8001,10000);
insert into tb_salgrade values (5,10001,15000);
insert into tb_salgrade values (6,15001,20000);
insert into tb_salgrade values (7,20001,25000);
insert into tb_salgrade values (8,25001,30000);
全部习题代码
-- 1. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)select te.name 员工姓名,td.name 部门名称 from tb_emp te,tb_dept td where te.dept_id td.id;-- 2. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)select te.name 员工姓名,td.name 部门名称 from tb_emp te join tb_dept td on te.dept_id td.id;-- 3. 查询员工的 姓名、性别、职位、部门名称 隐式内连接select te.name 姓名,te.gender 性别,te.job 职位,td.name 部门名称 from tb_emp te,tb_dept td where te.dept_id td.id;-- 4. 查询 薪资 高于 10000 的员工的姓名、性别、职位、部门名称显式内连接select te.name 姓名,te.gender 性别,te.job 职位,td.name 部门名称 from tb_emp te join tb_dept td on te.dept_id td.id where te.salary 10000;-- 5. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)select te.name 员工姓名,td.name 部门名称 from tb_emp te left join tb_dept td on te.dept_id td.id;-- 6. 查询员工表 所有 员工的姓名, 和对应的部门名称 (右外连接)select te.name 员工姓名,td.name 部门名称 from tb_dept td right join tb_emp te on te.dept_id td.id;-- 7. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)select td.name 部门名称,te.name 员工姓名 from tb_emp te right join tb_dept td on te.dept_id td.id;-- 8. 查询 教研部 的所有员工信息 标量子查询select * from tb_emp where dept_id (select id from tb_dept where tb_dept.name 教研部);-- 9. 查询在 方东白 入职之后的员工信息 标量子查询select * from tb_emp where entrydate (select entrydate from tb_emp where name 方东白);-- 10. 查询 教研部 和 咨询部 的所有员工信息 列子查询select * from tb_emp where dept_id in (select id from tb_dept where tb_dept.name in (教研部,咨询部));-- 11. 查询与 韦一笑 的入职日期 及 职位都相同的员工信息 行子查询select * from tb_emp where (entrydate,job) in (select entrydate,job from tb_emp where name 韦一笑);-- 12. 查询入职日期是 2006-01-01 之后的员工信息 , 及其部门信息 表子查询select * from (select * from tb_emp where entrydate 2006-01-01) e,tb_dept td where e.dept_id td.id;-- 13. 查询 拥有员工的 部门ID、部门名称 (没有员工的部门无需展示)select te.dept_id 部门id,td.name 部门名称 from tb_emp te,tb_dept td where te.dept_id td.id;-- 14. 查询所有 在 2010-01-01 之后入职的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来select te.*,td.name 部门名称 from tb_emp te left join tb_dept td on te.dept_id td.id where te.entrydate 2010-01-01;-- 15. 查询 教研部 员工的平均工资select avg(salary) from tb_emp where dept_id (select id from tb_dept where tb_dept.name 教研部);-- 16. 查询工资比 俞莲舟 高的员工信息。select * from tb_emp te where te.salary (select salary from tb_emp where tb_emp.name 俞莲舟);-- 17. 查询 工资 比该企业员工的平均薪资 还要高的员工信息select * from tb_emp te where te.salary (select avg(salary) from tb_emp);-- 18. 查询所有的部门信息, 并统计部门的员工人数select count(*) 员工数量,td.name 部门名称 from tb_dept td,tb_emp te where te.dept_id td.id group by td.name;
-- 19. 查询所有员工的 姓名, 工资 , 及 工资等级 (有难度的哦)
select te.name 姓名,te.salary 工资,ts.grade 工资等级 from tb_emp te left join tb_salgrade ts on te.salary between ts.losal and ts.hisal;select te.name 姓名,te.salary 工资,(casewhen te.salary 3000 then 1when te.salary 5000 then 2when te.salary 8000 then 3when te.salary 10000 then 4when te.salary 15000 then 5when te.salary 20000 then 6when te.salary 25000 then 7when te.salary 30000 then 8else 未知等级end) 薪资等级
from tb_emp te;-- 20. 查询 教研部 所有员工的信息 及 工资等级 (有难度的哦)
select te.*,ts.grade from tb_emp teleft join tb_dept td on te.dept_id td.idleft join tb_salgrade ts on te.salary between ts.losal and ts.hisalwhere td.name 教研部;select e.*,(casewhen e.salary 3000 then 1when e.salary 5000 then 2when e.salary 8000 then 3when e.salary 10000 then 4when e.salary 15000 then 5when e.salary 20000 then 6when e.salary 25000 then 7when e.salary 30000 then 8else 未知等级end) 薪资等级
from (select * from tb_emp te where te.dept_id (select id from tb_dept td where td.name 教研部)) e ;-- 21. 查询 工资 低于 本部门平均工资的员工信息 (有难度的哦)select * from tb_emp te where te.salary (select avg(salary) from tb_emp where tb_emp.dept_id te.dept_id);-- 22. 列出所有部门的详细信息(包括部门ID, 部门名称)和人数select td.id 部门id,td.name 部门名称,count(*) 部门人数 from tb_dept td left join tb_emp te on td.id te.dept_id group by td.name;-- 23、取得每个薪资等级有多少员工 (有难度的哦)
select te.name 姓名,te.salary 工资,ts.grade 工资等级 ,count(ts.grade) 等级数量 from tb_emp teleft join tb_salgrade ts on te.salary between ts.losal and ts.hisal group by ts.grade;selectcount(if(salary 0 and salary 3000,1,null)) as 1等级薪资人数,count(if(salary 3001 and salary 5000,1,null)) as 2等级薪资人数,count(if(salary 5001 and salary 8000,1,null)) as 3等级薪资人数,count(if(salary 8001 and salary 10000,1,null)) as 4等级薪资人数,count(if(salary 10001 and salary 15000,1,null)) as 5等级薪资人数,count(if(salary 15001 and salary 20000,1,null)) as 6等级薪资人数,count(if(salary 20001 and salary 25000,1,null)) as 7等级薪资人数,count(if(salary 25001 and salary 30000,1,null)) as 8等级薪资人数
from tb_emp te;-- 24. 取得每个部门最高薪水的人员名称select * from tb_emp where (dept_id,salary) in (select dept_id,max(salary) from tb_emp group by dept_id);
