可以接项目做的网站,营销型建设,备案网站怎么做,凌源网站优化创建多表连接查询
表准备
CREATE TABLE 员工信息 (员工号 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,姓名 VARCHAR(50) NOT NULL,性别 ENUM(男, 女) NOT NULL,出生日期 DATE NOT NULL,部门 VARCHAR(50) NOT NULL,手机号码 VARCHAR(20) NOT NULL,-- 根据数据库不同#x…创建多表连接查询
表准备
CREATE TABLE 员工信息 (员工号 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,姓名 VARCHAR(50) NOT NULL,性别 ENUM(男, 女) NOT NULL,出生日期 DATE NOT NULL,部门 VARCHAR(50) NOT NULL,手机号码 VARCHAR(20) NOT NULL,-- 根据数据库不同可能需要为手机号码字段添加UNIQUE约束以确保唯一性UNIQUE KEY unique_phone (手机号码)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插入数据
INSERT INTO 员工信息 (姓名, 性别, 出生日期, 部门, 手机号码) VALUES
(张三, 男, 1985-06-12, 技术部, 13800138001),
(李四, 女, 1990-11-7, 市场部, 13912345678),
(王五, 男, 1988-09-22, 人力资源部, 13711112222),
(赵六, 女, 1992-04-15, 财务部, 13600001111),
(陈七, 男, 1995-12-25, 技术部, 13522223333),
(周八, 女, 1987-07-18, 市场部, 13433334444),
(吴九, 男, 1991-08-28, 销售部, 13344445555),
(郑十, 女, 1986-03-10, 研发部, 13255556666),
(钱十一, 男, 1993-05-20, 人力资源部, 13166667777),
(孙十二, 女, 1989-12-2, 客服部, 13077778888),
(朱十三, 男, 1994-07-13, 财务部, 12988889999),
(马十四, 女, 1990-09-25, 技术部, 12899990000),
(唐十五, 男, 1987-02-19, 市场部, 12700001112),
(曹十六, 女, 1992-11-11, 销售部, 12611112223);CREATE TABLE 销售表 (销售员ID INT(11) NOT NULL,-- 假设销售员ID为外键引用员工信息表的员工号FOREIGN KEY (销售员ID) REFERENCES 员工信息(员工号),销售业绩金额 DECIMAL(10, 2) NOT NULL,部门 VARCHAR(50) NOT NULL,-- 可以根据需要添加其他字段如销售日期、产品信息等销售日期 DATE,产品名称 VARCHAR(100)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插入数据
INSERT INTO 销售表 (销售员ID, 销售业绩金额, 部门, 销售日期, 产品名称) VALUES
(1, 8500.50, 技术部, 2023-04-01, 全麦面包),
(2, 1200.75, 市场部, 2023-04-02, 低脂牛奶),
(3, 4500.00, 人力资源部, 2023-04-03, 红富士苹果),
(4, 9800.25, 财务部, 2023-04-04, 新鲜鸡蛋),
(5, 7200.10, 技术部, 2023-04-05, 即食燕麦片),
(6, 5500.60, 市场部, 2023-04-06, 巧克力棒),
(7, 15000.00, 销售部, 2023-04-07, 混合坚果),
(8, 6800.30, 研发部, 2023-04-08, 绿茶饮料),
(9, 3200.90, 人力资源部, 2023-04-09, 冷冻海鲜拼盘),
(10, 8000.45, 客服部, 2023-04-10, 意大利面),
(11, 7000.80, 财务部, 2023-04-11, 橄榄油),
(12, 5000.20, 技术部, 2023-04-12, 黑咖啡豆),
(13, 9500.15, 市场部, 2023-04-13, 酸奶),
(14, 4000.55, 销售部, 2023-04-14, 薯片);CREATE TABLE 进货表 (进货记录ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,-- 假设收货人为公司员工因此收货人ID为外键引用员工信息表的员工号收货人ID INT(11) NOT NULL,FOREIGN KEY (收货人ID) REFERENCES 员工信息(员工号),产品名称 VARCHAR(20) NOT NULL,货号 VARCHAR(50) NOT NULL,总数量 INT(11) NOT NULL,总金额 DECIMAL(10, 2) NOT NULL,-- 可以根据需要添加其他字段如进货日期、供应商信息等进货日期 DATE,供应商名称 VARCHAR(100)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插入数据
INSERT INTO 进货表 (收货人ID, 货号, 总数量, 总金额, 进货日期, 供应商名称) VALUES
(1, GR001, 200, 8500.00, 2023-04-01, 绿叶食品有限公司),
(2, DR002, 150, 4500.00, 2023-04-02, 大洋乳制品厂),
(3, FR003, 300, 9800.00, 2023-04-03, 田园鲜果农场),
(4, EG004, 500, 7200.00, 2023-04-04, 金鸡饲养场),
(5, MT005, 250, 5500.00, 2023-04-05, 美味甜品工坊),
(6, CH006, 180, 6800.30, 2023-04-06, 可可巧克力工厂),
(7, NM007, 120, 15000.00, 2023-04-07, 坚果世界),
(8, TE008, 220, 9500.15, 2023-04-08, 茶源饮品集团),
(9, FS009, 350, 4000.55, 2023-04-09, 海之鲜冷冻食品),
(10, SP010, 280, 8000.45, 2023-04-10, 意面之家),
(11, OL011, 160, 7000.80, 2023-04-11, 橄榄庄园),
(12, CF012, 200, 5000.20, 2023-04-12, 咖啡原产地),
(13, YD013, 190, 9800.25, 2023-04-13, 酸奶乐园),
(14, CP014, 230, 4500.90, 2023-04-14, 脆皮薯片厂);1.交叉连接查询
在MySQL中交叉连接Cross Join又称为笛卡尔积它返回两个表中所有行的组合。如果一个表有m行另一个表有n行那么交叉连接会产生m*n行的结果集。这种类型的查询并不常用除非你确实需要所有可能的组合。
格式一
select 字段名 from 表1 cross join 表2;案例select * from 员工信息 cross join 销售表;
省略写法select 字段名 from 表1,表2;2.内连接查询
内连接INNER JOIN是关系型数据库中常用的查询方式之一它用来返回两个或多个表中满足连接条件的所有记录。内连接基于一个共同列或多个共同列只显示那些在所有参与连接的表中都匹配的行。
格式一
select 字段名 from 表名1 join 表名2 ON 表名1.关系字段表名2.关系字段;案例select 姓名,销售业绩金额 from 员工信息 join 销售表 on 员工信息.员工号销售表.销售员ID;JOIN关键字指定了内连接操作。
FROM 员工信息指定了查询的第一个表左表。
INNER JOIN 销售表指定了查询的第二个表右表。
ON 员工信息.员工号销售表.销售员ID;是连接条件表示只有当员工信息表的员工号与销售表的销售员ID相等时才会从这两个表中选择相应的行进行匹配。
执行这个查询后结果将只包含那些在员工信息表和销售表中都有对应销售业绩金额的记录显示员工姓名和他们的销售额。 外连接Outer Join在数据库查询中用于返回两个或更多表中匹配的行以及左表或右表中没有匹配项的行。外连接有三种主要类型左外连接LEFT JOIN、右外连接RIGHT JOIN和全外连接FULL OUTER JOIN。在MySQL中不直接支持FULL OUTER JOIN但可以通过UNION或其他方法模拟。
左外连接LEFT JOIN
左外连接会返回左表LEFT JOIN关键字左边的表的所有记录即使右表中没有匹配。如果右表中没有匹配则结果集中右表的部分将为NULL。
示例假设我们想列出所有员工及其销售业绩包括那些没有销售记录的员工。
SELECT 员工信息.员工号, 员工信息.姓名, 销售表.销售业绩金额
FROM 员工信息
LEFT JOIN 销售表 ON 员工信息.员工号 销售表.销售员ID;右外连接RIGHT JOIN
右外连接与左外连接相反它会返回右表RIGHT JOIN关键字右边的表的所有记录即使左表中没有匹配。如果左表中没有匹配则结果集中左表的部分将为NULL。
示例假设我们想列出所有销售记录及其对应的员工信息包括那些员工信息可能不存在的销售记录这种情况较少见但理论上可以这样查询。
SELECT 员工信息.员工号, 员工信息.姓名, 销售表.销售业绩金额
FROM 员工信息
RIGHT JOIN 销售表 ON 员工信息.员工号 销售表.销售员ID;全外连接模拟
MySQL不直接支持FULL OUTER JOIN但可以通过UNION来模拟以合并左外连接和右外连接的结果从而获取两边都不缺失的记录。
示例展示所有员工和销售记录的组合无论是否有匹配。
(SELECT 员工信息.员工号, 员工信息.姓名, 销售表.销售业绩金额FROM 员工信息LEFT JOIN 销售表 ON 员工信息.员工号 销售表.销售员ID
) UNION (SELECT 员工信息.员工号, 员工信息.姓名, 销售表.销售业绩金额FROM 员工信息RIGHT JOIN 销售表 ON 员工信息.员工号 销售表.销售员ID
) ORDER BY 员工号;注意最后一个示例中的ORDER BY语句是为了排序结果以便查看更清晰。在实际应用中根据需求决定是否需要排序。 做前须知
IFNULL()的用法
select 收货人ID,ifnull(总数量,0) from 进货表;在MySQL中正确的函数是IFNULL()但实际上正确的函数名是IFNULL()应为IFNULL()。IFNULL(expression, value_if_null)函数用于判断第一个表达式expression是否为NULL如果是NULL则返回第二个参数value_if_null否则返回第一个表达式的值。这是一个条件函数用于处理NULL值的简单替换场景。
作用 IFNULL()函数在SQL中的作用主要是处理NULL值。当我们在查询数据库时有时会遇到某些字段的值为NULL这可能会影响到后续的数据处理或展示。IFNULL()函数允许我们为这些NULL值提供一个默认的替换值。 练习题
1.列出所有员工的姓名以及他们销售的商品名称对于没有销售记录的员工商品名称显示为未销售。
2.编写一个SQL查询使用右外连接RIGHT JOIN来整合“进货表”和“员工信息表”确保所有员工都被列出无论他们是否有关联的进货记录。查询应展示员工的姓名以及他们负责的商品货号如果有的话。对于没有进货记录的员工货号显示为NULL。
3.列出每个员工的姓名以及他们销售的任意一款产品名称如果员工没有销售记录则显示无销售记录。
4.展示每种进货的商品名称及其对应的收货员工姓名如果商品尚未被指派收货员工则显示待指派。
