首页 > 数据库 >约束.数据库设计.多表查询.事务

约束.数据库设计.多表查询.事务

时间：2025-01-16 20:21:10浏览次数：1  
标签：多表 -- 数据库 查询 dept job emp 员工 id

1.约束

  • 非空约束： 关键字是 NOT NULL
    保证列中所有的数据不能有null值。
  • 唯一约束：关键字是 UNIQUE
    保证列中所有数据各不相同。
  • 主键约束： 关键字是 PRIMARY KEY
    非空且唯一。
  • 默认约束： 关键字是 DEFAULT
    未指定值则采用默认值。
  • 外键约束： 关键字是 FOREIGN KEY
    练习
    根据需求，为表添加合适的约束
点击查看代码 
-- 员工表
CREATE TABLE emp (
id INT, -- 员工id，主键且自增长
ename VARCHAR(50), -- 员工姓名，非空且唯一
joindate DATE, -- 入职日期，非空
salary DOUBLE(7,2), -- 工资，非空
bonus DOUBLE(7,2) -- 奖金，如果没有将近默认为0
);

按照约束的关键字及需求对表进行修改，得到结果

点击查看代码 
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
joindate DATE NOT NULL , -- 入职日期，非空
salary DOUBLE(7,2) NOT NULL , -- 工资，非空
bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);


外键约束
外键让两个表之间建立链接
练习

点击查看代码 
-- 部门表
CREATE TABLE dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
CREATE TABLE emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);

-- 建完表后添加外键约束 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

2.数据库设计
1. 一对多
在多的一方建立外键，指向一的一方的主键

点击查看代码 
-- 部门表
CREATE TABLE tb_dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
CREATE TABLE tb_emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)
);
**2. 多对多** 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
点击查看代码 
-- 订单表
CREATE TABLE tb_order(
id int primary key auto_increment,
payment double(10,2),
payment_type TINYINT,
status TINYINT
);
-- 商品表
CREATE TABLE tb_goods(
id int primary key auto_increment,
title varchar(100),
price double(10,2)
);
-- 订单商品中间表
CREATE TABLE tb_order_goods(
id int primary key auto_increment,
order_id int,
goods_id int,
count int
);
-- 建完表后，添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES
tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES
tb_goods(id);

3.多表查询

点击查看代码 
# 创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);
# 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dep_id INT,
FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',null);
select * from emp , dept where emp.dep_id = dept.did;

  • 内连接
    查找两表交集
    -- 隐式内连接 SELECT 字段列表 FROM 表1,表2… WHERE 条件;
    -- 显示内连接 SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

  • 外连接
    -- 左外连接 SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
    -- 右外连接 SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

  • 子查询
    查询中嵌套查询
    例： 查询工资高于猪八戒的员工信息。
    第一步：先查询出来 猪八戒的工资
    select salary from emp where name = '猪八戒'
    第二步：查询工资高于猪八戒的员工信息
    select * from emp where salary > 3600;
    select * from emp where salary > (select salary from emp where name = '猪八戒');

  • 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断

  • 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断

  • 子查询语句结果是多行多列，子查询语句作为虚拟表
    案例

  • 环境准备：

点击查看代码 
-- 部门表
CREATE TABLE dept (
did INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 职务表，职务名称，职务描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
1. 查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
点击查看代码 
/*
分析：
1. 员工编号，员工姓名，工资 信息在emp 员工表中
2. 职务名称，职务描述 信息在 job 职务表中
3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
*/
-- 方式一 ：隐式内连接
SELECT emp.id,emp.ename,emp.salary,job.jname,job.descriptionFROM emp,job WHERE emp.job_id = job.id;
-- 方式二 ：显式内连接
SELECT emp.id,emp.ename,emp.salary,job.jname,job.description FROM emp INNER JOIN job ON emp.job_id = job.id;
2. 查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
点击查看代码 
/*
分析：
1. 员工编号，员工姓名，工资 信息在emp 员工表中
2. 职务名称，职务描述 信息在 job 职务表中
3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
4. 部门名称，部门位置 来自于 部门表 dept
5. dept 和 emp 一对多关系 dept.id = emp.dept_id
*/
-- 方式一 ：隐式内连接
SELECT emp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc FROM emp,job,dept WHERE emp.job_id = job.id and dept.id =emp.dept_id
;
-- 方式二 ：显式内连接
SELECT emp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc FROM emp 
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
3. 查询员工姓名，工资，工资等级
点击查看代码 
/*
分析：
1. 员工姓名，工资 信息在emp 员工表中
2. 工资等级 信息在 salarygrade 工资等级表中
3. emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
*/
SELECT emp.ename,emp.salary,t2.* FROM emp,salarygrade t2 WHERE emp.salary >= t2.losalary AND emp.salary <= t2.hisalary
4. 查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
点击查看代码 
/*
分析：
1. 员工编号，员工姓名，工资 信息在emp 员工表中
2. 职务名称，职务描述 信息在 job 职务表中
3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
4. 部门名称，部门位置 来自于 部门表 dept
5. dept 和 emp 一对多关系 dept.id = emp.dept_id
6. 工资等级 信息在 salarygrade 工资等级表中
7. emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
*/
SELECT
emp.id
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.loc,
t2.grade
FROM
emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;
5. 查询出部门编号、部门名称、部门位置、部门人数
点击查看代码 
/*
分析：
1. 部门编号、部门名称、部门位置 来自于部门 dept 表
2. 部门人数: 在emp表中 按照dept_id 进行分组，然后count(*)统计数量
3. 使用子查询，让部门表和分组后的表进行内连接
*/
-- 根据部门id分组查询每一个部门id和员工数
select dept_id, count(*) from emp group by dept_id;
SELECT
dept.id,
dept.dname,
dept.loc,
t1.count
FROM
dept,
(
SELECT
dept_id,
count(*) count
FROM
emp
GROUP BY
dept_id
) t1
WHERE
dept.id = t1.dept_id

标签：多表,--,数据库,查询,dept,job,emp,员工,id
From： https://www.cnblogs.com/hzy-rj/p/18675706

相关文章

  • JSP旅游网站的设计与实现3r2y1（程序+源码+数据库+调试部署+开发环境）
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表开题报告内容一、项目背景随着互联网技术的飞速发展，旅游业与信息技术的融合已成为行业趋势。旅游网站作为连接旅游者与旅游资源的桥梁，其设计与实现对于提升旅游......
  • JSP旅游网站36yc1程序+源码+数据库+调试部署+开发环境
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表开题报告内容一、项目背景与意义随着互联网的普及，旅游网站已成为人们获取旅游信息、预订旅游产品的重要渠道。本项目旨在开发一个功能全面、用户体验良好的旅游......
  • Day10-后端Web实战——Mysql多表操作&员工列表查询(分页查询)
    目录1.多表关系1.1一对多1.1.1关系实现1.1.2外键约束1.2一对一1.3多对多1.4案例2.多表查询2.1概述2.1.1数据准备2.1.2介绍2.1.3分类2.2内连接2.3外连接2.4子查询2.4.1介绍2.4.2标量子查询2.4.3列子查询2.4.4行子查询2.4.5表子查询2.5案例3.员......
  • JSP旅游景点管理系统3i37j--（程序+源码+数据库+调试部署+开发环境）
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表开题报告内容一、项目背景随着旅游业的快速发展，旅游景点作为旅游业的核心资源，其管理效率和服务质量直接影响到游客的旅游体验。为了提高景点管理效率，优化游客服......
  • JSP罗曼蒂克网上花店平台37xai（程序+源码+数据库+调试部署+开发环境）
    本系统（程序+源码+数据库+调试部署+开发环境）带论文文档1万字以上，文末可获取，系统界面在最后面。系统程序文件列表开题报告内容一、研究背景随着电子商务的蓬勃发展，线上购物已成为人们日常生活的重要组成部分。鲜花作为一种常见的礼品，广泛应用于各种场合，如生日、结婚、母亲节......
  • 图数据库测试要点
    ———————————————————-点击上方可订阅关注我公众号:蚂蚁质量！也可以加我微信进微信交流群。如果你觉得公众号对你有帮助，欢迎转发、推荐给他人。————————————————————一、引言    图数据库作为一种专门用于存储和查询图结构数......
  • 洛谷题单指南-线段树的进阶用法-P3168 [CQOI2015] 任务查询系统
    原题链接：https://www.luogu.com.cn/problem/P3168题意解读：一个任务管理系统，能够查询在某个时间点运行的任务中优先级最小的k个任务的优先级之和。解题思路：由于总时间n不超过100000，考虑针对所有时刻建立可持久化线段树，根节点为root[i]的线段树维护时刻i的任务情况，节点区间表示......
  • 如果在odoo模型中是一对多的字段，或者是计算字段，那么在查询的时候，怎么处理。
    在Odoo中，一对多字段（One2many）和计算字段（Computed）在查询时的处理与普通字段有所不同，因为它们并不直接映射到数据库表中的列。理解如何查询这些字段是非常重要的，下面我会分别介绍如何处理一对多字段和计算字段。1.一对多字段(One2many)一对多字段（One2many）在Odoo中是通过外键......
  • 媒体查询+雪碧图
    <媒体查询>----->媒体查询会根据设备的大小自动识别加载不同的样式     ------><metaname="viewport"content="width=device-width,initial-scale=1.0">这条属性为设置试图宽度，并禁止缩放，媒体查询实现统一网站在不同登录设备中实现不同效果，下面这段代码实现在设......
  • 在odoo中，为什么在模型中明明有这个字段值，但是在SQL查询中，却报错：没有这个字段？
    在odoo中为什么在模型中，有的字段，用SQL有的可以查到，有的字段却报错。selectsource_fact_no,confirmation_datefromsale_order_line[42703]ERROR:column"confirmation_date"doesnotexist位置：31这是什么原因，请详解一下。在Odoo中，字段与数据库表之间有一个映射关......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99