版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
项目4客户关系管理系统数据库设计与实现《MySQL数据库技术项目化教程》目录任务1数据库需求分析任务2
客户关系管理系统数据库概念结构设计任务3创建数据库和表任务4设计和创建查询任务5
设计和制作索引任务6
设计和制作触发器任务7
数据备份与恢复项目导学·系统概述
●项目核心:客户关系管理系统(CRM)是企业运营的“增长引擎”,通过集中管理客户信息、销售跟进、服务记录与商机转化等核心数据,驱动整个销售与服务体系的协同运转,让客户管理从碎片化的手工记录转向系统化、数据驱动的精准经营。●技术栈选择:本项目将以MySQL作为核心技术栈,构建一个精简且功能完备的CRM系统,依托MySQL成熟的关系型数据库特性,保障客户数据的一致性、完整性与高效查询能力,同时为后续扩展数据分析与报表功能奠定坚实基础。●核心实践路径:我们将从需求分析与数据库表结构设计入手,逐步落地客户信息建档、销售机会追踪、合同订单管理、跟进记录沉淀等核心业务模块,同时深入探索索引优化、事务控制、存储过程与触发器等数据库进阶技巧,解决实际业务中的并发操作、数据安全与性能瓶颈问题。●核心学习目标:通过本项目的实战演练,您将深刻理解“客户即资产”的核心内涵,学会把复杂的销售与服务业务场景转化为严谨的数据库模型,体会数据库如何将零散的客户交互记录沉淀为企业精准营销、留存提效、科学决策的核心依据。01知识目标:深研核心原理,搭建系统认知了解数据库管理系统在客户关系管理系统中的作用;了解客户关系管理系统数据库的设计原则、架构和存储结构;掌握E-R模型;掌握数据的分类、存储和访问方式;了解
数据范式;了解数据挖掘在客户关系管理系统中的应用。02技能目标:锤炼实操本领,锻造工程能力掌握数据查询、插入、更新和删除等基本操作;掌握数据库的备份、恢复和性能优化;参与设计客户关系管理系统数据库的实现过程;应用数据库事务处理、并发控制和安全性等;掌握数据接口的设计。03素养目标:涵养职业品格,激发创新潜能着力培养学生系统分析与设计的能力、解决问题的能力,激发学生持续学习与创新能力。项目导学·知识与素养目标任务1:数据库需求分析洞察业务脉络,厘清数据边界任务1必备知识●创建数据库:有效地管理和维护数据,实现数据的共享和持久化存储,保障数据的安全性和一致性,减少数据冗余,支持决策制定,并为应用程序的开发提供基础支持。●创建数据表:数据表是数据库中存储数据的地方,它们由行(记录)和列(字段)组成。●使用SQL语句设置约束条件:主要包括:主键约束、自增约束、非空约束、唯一约束、无符号约束、默认约束、外键约束等。●多表数据记录查询:多表查询是指通过联合两个或多个表,将它们的关联字段进行匹配,从而获取更全面的查询结果。在关系型数据库管理系统中,一张表通常只存储一个实体的相关信息,而多表查询允许用户跨越多个表来获取相关联的数据。●索引:索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍,因此,要掌握索引的设计原则。根据实际需求,创建、查看和删除普通索引、组合索引、唯一索引以及全文索引。●触发器:触发器可以检查插入、更新或删除操作是否符合定义的规则和约束,如主键约束、外键约束或唯一约束等,从而维护数据的完整性和一致性。●数据的备份与恢复:数据库用户需建立周期性的数据备份机制,通过定期对数据库核心数据进行完整或增量备份形成可追溯的历史数据副本,以便在意外事件导致数据异常时依托备份快速恢复、降低业务中断影响。任务1客户关系管理(CRM)核心模块模块功能描述客户信息管理企业名称、联系人、电话、地址的增删改查销售机会管理机会创建→分配→跟进→关闭的完整生命周期交易管理订单、合同、报价等交易历史留存服务管理服务请求的创建、分配、处理及反馈权限管理基于角色的访问控制,保障数据安全任务1业务实体识别序号实体名称说明1客户(customers)企业客户的基本信息2销售机会(opportunities)潜在销售项目跟踪3联系人(contacts)客户方的具体联系人4服务请求(service_requests)客户服务工单5商品(products)企业销售的产品6订单(orders)客户购买订单7销售人员(salespersons)销售团队成员8用户(users)系统登录账户9角色(roles)权限角色定义任务2:客户关系管理系统数据库概念结构设计抽象业务蓝图,绘制ER模型任务2概念结构设计——E-R图1.E-R图核心三要素要素图形表示示例实体矩形框客户、订单属性椭圆框客户姓名、订单金额联系菱形框客户“提交”订单实体间联系类型:1:1(一对一)——如:用户↔角色(一个用户一个角色)1:N(一对多)——如:客户↔订单(一个客户多个订单)M:N(多对多)——如:订单↔商品(通过订单详情表关联)任务2概念结构设计——E-R图2.系统E-R图4.1数据范式理论范式要求解决的问题1NF每个字段都是原子性(不可再分)消除重复组2NF满足1NF+所有非主键字段完全依赖主键消除部分依赖3NF满足2NF+非主键字段之间无传递依赖消除传递依赖核心原则:
一个表只描述一件事,减少数据冗余,避免更新异常。1.三大范式4.1数据范式理论
例如,有一个CRM系统,用于管理客户信息、订单信息和产品信息等。在系统中会设计多个表来存储这些数据,其中一些表可能会因为设计不当而违反第二范式。假设我们有一个名为“订单详情”的表,其设计如下:2.范式案例对比订单编号(主键)产品编号产品名称产品数量订单日期客户信息IDOD001PR001笔记本电脑22024/5/1CI001OD002PR002手机12024/5/2CI002
在这个表中,“订单编号”和“产品信息”(包括“产品编号”“产品名称”)之间存在某种依赖关系,但这种设计可能并不满足第二范式,因为“产品名称”实际上更直接地依赖于“产品编号”,而不是直接依赖于“订单编号”。4.1数据范式理论
为了满足第二范式,我们需要重新设计这个表,将依赖于不同主键的字段分离到不同的表中。在这个案例中,我们可以将“订单详情”表拆分为两个表:“订单表”和“产品表”,并通过外键关联它们。2.范式案例对比订单编号订单日期客户信息IDOD0012024/5/1CI001OD0022024/5/2CI002产品编号产品名称PR001笔记本电脑PR002手机订单表产品表每个订单都有一个唯一的订单编号作为主键。每个产品都有一个唯一的产品编号作为主键。4.1数据范式理论
如果需要记录每个订单中购买的具体产品及其数量,可以创建一个额外的表来存储这些信息。这个表通过“订单编号”和“产品编号”作为联合主键(或外键分别指向“订单表”和“产品表”),记录了订单与产品之间的多对多关系以及购买数量。2.范式案例对比订单产品关联表
通过应用第二范式,我们将原始的“订单详情”表拆分为多个更小的、更专注于特定数据类客户关系管理系统。这种设计减少了数据冗余,提高了数据的一致性和可维护性。同时,它也使得查询操作更加灵活和高效,因为可以根据需要连接不同的表来获取所需的信息。订单编号产品编号产品数量OD001PR0012OD002PR0021任务3:创建数据库和表搭建物理载体,夯实存储基座4.2创建数据库
根据实际需求创建客户关系管理系统数据库(CRM_DB)。
创建数据库的关键字为CREATE,语法形式如下:
CREATEDATABASE[IFNOTEXISTS]database_name;
注意:1.一般由字母和下划线组成,不允许有空格,仅限a~z、A~Z、0~9、$、_字符。2.不允许是MySQL关键字。3.最大长度为64个字符。4.不能与其他数据库同名,添加IFNOTEXISTS条件,防止因同名数据库存在导致创建失败。
【例4-1】使用CREATE关键字创建客户关系管理数据库CRM_DB。
登录MySQL之后,输入创建数据库的语句,并按回车键确认,执行语句如下:
CREATEDATABASECRM_DB;4.3创建数据表
创建数据表,实际上是规定列属性和实现数据完整性约束的过程,基本语法形式如下:CREATETABLE[IFNOTEXISTS]table_name(col_name1data_type[Constraints],col_name2data_type[Constraints],...col_namendata_type[Constraints]);数据表命名应遵循以下原则:1.长度最好不超过64个字符。2.多个单词之间使用下划线“_”分隔,不允许有空格。3.不允许是MySQL关键字。4.不允许与同一数据库中的其他数据表同名,添加IFNOTEXISTS条件,防止因同名数据表存在导致创建失败。4.4使用SQL语句设置约束条件序号约束关键字1主键约束PRIMARYKEY2自增约束AUTO_INCREMENT3非空约束NOTNULL4唯一约束UNIQUE5无符号约束UNSIGNED6默认约束DEFAULT默认值7外键约束CONSTRAINT...FOREIGNKEY...REFERENCES...8限制约束CHECK9设置存储引擎ENGINE=存储引擎名称任务3任务实施字段数据类型约束注释idINT(11)主键、自增客户IDnameVARCHAR(255)非空客户名称genderENUM('M','F')非空性别contactnameVARCHAR(255)
联系人姓名telVARCHAR(20)唯一联系电话emailVARCHAR(255)唯一电子邮件地址addressTEXT非空地址add_timeDATETIME非空添加时间【例4-2】根据下表的数据信息创建客户表(Customers),并插入相应的数据。(1)创建客户表(Customers)USECRM_DB;CREATETABLECustomers(idINT(11)PRIMARYKEYAUTO_INCREMENT,nameVARCHAR(255)NOTNULL,genderENUM('M','F')NOTNULL,contactnameVARCHAR(255),telVARCHAR(20)UNIQUE,emailVARCHAR(255)UNIQUE,addressTEXTNOTNULL,add_timeDATETIMENOTNULL);任务3任务实施(2)执行以下SQL语句,向customers表中插入多条数据记录。INSERT
INTO
customers(id,name,gender,contact_name,tel,email,address,add_time)VALUES(1,'张三','M','李四',,'zhangsan@','北京市朝阳区建国路100号','2024-08-2223:53:31'),(2,'王武','M','王武',,'wangwu@','上海市浦东新区世纪大道888号','2024-08-2223:55:25'),(3,'赵柳','F','小赵',,'liuzhao@','广州市天河区珠江新城华夏路1号','2024-08-2223:56:56'),(4,'刘蜜','F','刘七',,'liuqi@','深圳市南山区粤海街道科技园中区','2024-08-2223:57:38'),(5,'陈坝','M','陈新',,'chenba@','深圳市南山区粤海街道科技园中区','2024-08-2223:58:12'),(6,'周玖','M','周晶',,'zhoujiu@','杭州市西湖区文三路99号','2024-08-2223:59:02'),(7,'武适','M','王含',,'wushi@','武汉市江汉区建设大道707号','2024-08-2300:02:14'),(8,'郑诗意','F','周江',,'zhengshiyi@','西安市雁塔区科技路2号','2024-08-2300:02:20'),(9,'王石尔','M','汪意',,'wangshier@','重庆市渝北区金开大道1000号','2024-08-2300:03:12'),(10,'冯世伞','F','冯枫',,'fengshisan@','天津市河西区南京路10号','2024-08-2300:04:13');任务3任务实施字段数据类型约束注释idINT(11)主键、自增联系人IDcustomers_idINT(11)外键客户IDnameVARCHAR(255)非空姓名positionVARCHAR(255)
职位telVARCHAR(20)
联系电话emailVARCHAR(255)唯一电子邮件【例4-3】根据下表的数据信息创建联系人表(Contacts),并插入相应的数据。(1)创建联系人表(Contacts)USECRM_DB;CREATETABLEContacts(idINT(11)PRIMARYKEYAUTO_INCREMENT,customers_idINT(11),nameVARCHAR(255)NOTNULL,positionVARCHAR(255),telVARCHAR(20),emailVARCHAR(255)UNIQUE,CONSTRAINTcus_conFOREIGNKEY(customers_id)REFERENCEScustomers(id)ONDELETESETNULLONUPDATECASCADE);在FOREIGNKEY约束中,我们指定了当customers表中的相应记录被删除时(ON
DELETESETNULL),contacts表中的customers_id将设置为NULL。这有助于保持数据的完整性,因为即使客户记录被删除,联系人信息仍然可以保留在数据库中。另外,如果客户记录的customers_id发生变化(ONUPDATECASCADE),则所有相关的contacts记录中的customers_id也会自动更新,以保持数据的一致性。任务3任务实施(2)执行以下SQL语句,向contacts表中插入多条数据记录。INSERTINTOcontacts(id,customers_id,name,position,tel,email)VALUES(1,1,'张翔','市场专员','55501234','zhangxiang@'),(2,3,'史振','客服','55505678','shizhen@'),(3,7,'卓一',NULL,NULL,'zhuoyi@'),(4,8,'吕滢',NULL,NULL,'lying@'),(5,10,'查朵','客户经理','55507890','zhaduo@');任务3任务实施字段数据类型约束注释idINT(11)主键、自增请求IDcustomers_idINT(11)
客户IDtypeVARCHAR(255)非空请求类型desTEXT
描述timeTIMESTAMP默认创建时间,默认为当前时间stateVARCHAR(50)非空,默认状态,默认状态为“Open”,即服务请求已创建但尚未处理resolution_dateDatetime
请求解决时间【例4-4】根据下表的数据信息创建服务请求表(Service_Requests),并插入相应的数据。(1)创建服务请求表(ServiceRequests)USECRM_DB;CREATETABLEService_Requests(idINT(11)PRIMARYKEYAUTO_INCREMENT,customers_idINT(11),typeVARCHAR(255)NOTNULL,desTEXT,timeTIMESTAMPDEFAULTCURRENT_TIMESTAMP,stateVARCHAR(50)NOTNULLDEFAULT'Open',resolution_dateDATETIME,CONSTRAINTcus_serFOREIGNKEY(customers_id)REFERENCEScustomers(id)ONDELETESETNULLONUPDATECASCADE);任务3任务实施(2)执行以下SQL语句,向service_requests表中插入多条数据记录。INSERTINTOservice_requests(id,customers_id,type,des,time,state,resolution_date)VALUES(1,2,'咨询','客户表示想了解手机','2024-08-3121:36:18','Close','2024-09-0110:43:05'),(2,4,'购买','客户想购买一部平板','2024-08-0821:36:59','Open',NULL),(3,9,'售后','客户电脑需要重装系统','2024-08-2021:37:48','Close','2024-08-2109:20:24'),(4,5,'咨询','客户表示想了解笔记本','2024-09-1414:38:49','Open',NULL);任务3任务实施字段数据类型约束注释idINT(11)主键、自增商品IDnameVARCHAR(255)非空商品名称desTEXT非空商品描述priceDECIMAL(10,2)非空商品价格numINT(11)非空、默认商品库存【例4-5】根据下表的数据信息创建商品表(Products),并插入相应的数据。(1)创建商品表(Products)USECRM_DB;CREATETABLEProducts(idINT(11)PRIMARYKEYAUTO_INCREMENT,nameVARCHAR(255)NOTNULL,desTEXTNOTNULL,priceDECIMAL(10,2)NOTNULL,numINT(11)NOTNULLDEFAULT0);任务3任务实施(2)执行以下SQL语句,向products表中插入多条数据记录。INSERTINTOproducts(id,name,des,price,num)VALUES(1,'智能手环','轻薄舒适睡眠监测心律失常提示运动手',229,199),(2,'蓝牙耳机','40小时长续航,快速充电,蓝牙连接',139,485),(3,'无线鼠标','双模办公,人体工学,设备切换',79,357),(4,'nova','前后双高清摄像手机',1290,769),(5,'mateview','全面屏,广色域,低蓝光无频闪',549,150),(6,'matepad','双层OLED云晰柔光屏',4299,80),(7,'matebook','笔记本电脑,OLED悬浮屏,轻薄商务办公',8199,50);任务3任务实施字段数据类型约束注释idINT(11)主键、自增订单IDcustomers_idINT(11)外键客户IDProducts_idINT(11)非空商品IDorder_dateDATE非空订单日期totalDECIMAL(10,2)非空总金额stateVARCHAR(50)非空、默认订单状态salespersons_idINT(11)非空销售人员IDamountINT(11)非空订单商品数量【例4-6】根据下表的数据信息创建订单表(Orders),并插入相应的数据。(1)创建订单表(Orders)USECRM_DB;CREATETABLEOrders(idINT(11)PRIMARYKEYAUTO_INCREMENT,customers_idINT(11),products_idINT(11)NOTNULL,order_dateDATENOTNULL,totalDECIMAL(10,2)NOTNULL,stateVARCHAR(50)NOTNULLDEFAULT'Pending',salespersons_idINT(11)NOTNULL,amountINT(11)NOTNULL,CONSTRAINTcus_ordFOREIGNKEY(customers_id)REFERENCEScustomers(id)ONDELETESETNULLONUPDATECASCADE);任务3任务实施(2)执行以下SQL语句,向orders表中插入多条数据记录。INSERTINTOorders(id,customers_id,products_id,order_date,total,state,salespersons_id,amount)VALUES(1,1,1,'2024-02-07',229,'Pending',101,1),(2,5,6,'2024-04-09',4299,'Pending',201,1),(3,7,5,'2024-06-19',549,'Pending',202,1),(4,9,7,'2024-08-13',8199,'Pending',101,1),(5,4,6,'2024-10-22',4299,'Pending',101,1),(6,2,4,'2024-10-22',1290,'Pending',201,1),(7,3,7,'2024-10-22',8199,'Pending',201,1);任务3任务实施字段数据类型约束注释idINT(11)主键、自增销售人员IDnameVARCHAR(100)非空销售人员姓名positionVARCHAR(50)非空职位sectionVARCHAR(50)非空销售小组emailVARCHAR(255)非空、唯一电子邮件telVARCHAR(20)非空联系电话department_idINT(11)非空部门ID【例4-7】根据下表的数据信息创建销售人员表(Salespersons),并插入相应的数据。(1)创建销售人员表(Salespersons)USECRM_DB;CREATETABLESalespersons(idINT(11)PRIMARYKEYAUTO_INCREMENT,nameVARCHAR(100)NOTNULL,positionVARCHAR(50)NOTNULL,sectionVARCHAR(50)NOTNULL,emailVARCHAR(255)NOTNULLUNIQUE,telVARCHAR(20)NOTNULL,department_idINT(11)NOTNULL);任务3任务实施(2)执行以下SQL语句,向salespersons表中插入多条数据记录。INSERTINTOsalespersons(id,name,position,section,email,tel,department_id)VALUES(101,'季庆奇','组长','销售一组','jqq@',,3),(102,'张长富','员工','销售一组','zcf@',,3),(103,'李星','员工','销售一组','lx@',,3),(201,'李思佳','组长','销售二组','lsf@',,3),(202,'王雯雯','员工','销售二组','www@',,3),(203,'蒋丽','员工','销售二组','jl@',,3);任务4:设计和创建查询灵活检索数据,赋能业务决策任务4任务描述
在客户关系管理系统(CRM)的数据库设计中,创建查询的任务通常旨在提取、分析和报告与客户、销售、市场活动、服务请求等相关的关键信息。这些查询对于理解客户行为、评估销售绩效、优化市场策略以及提升客户服务质量至关重要。以下是一个简单的步骤描述,用于在CRM数据库中设计和创建查询。1.确定查询目的2.设计查询逻辑3.编写SQL查询4.测试查询4.5连接查询1.内连接查询(1)普通内连接语法如下:SELECT{*|col_list}FROMtable_name1INNERJOINtable_name2ONcondition;此外,WHERE子句可用于指定连接条件,但在特定情境下可能会削弱查询效率。相比之下,采用INNERJOIN语法不仅更加直观,还能有效避免遗漏连接条件的风险,从而提升查询性能与准确性。(2)自连接查询语法如下:SELECTtable_alias1.*,table_alias2.*FROMtable_nameAStable_alias1INNERJOINtable_nameAStable_alias2ONcondition;自连接主要用于处理那些表内部存在层次或递归关系的数据。4.5连接查询2.外连接查询(1)左连接查询左连接查询以左表为基表,会将左表中的每一行记录与右表(LEFTJOIN右侧的表)的记录按连接条件进行匹配:若匹配成功,则返回左表与右表中符合条件的行数据;若匹配失败,仅返回左表中的行数据,右表对应列的值为空值(NULL)。语法如下:SELECT{*|col_list}FROMtable1LEFT[OUTER]JOINtable2ONcondition;(2)右连接查询右连接查询以右表为基表,会将右表中的每一行记录与左表(RIGHTJOIN左侧的表)的记录按连接条件进行匹配:若匹配成功,则返回右表与左表中符合条件的行数据;若匹配失败,仅返回右表中的行数据,左表对应列的值为空值(NULL)。语法如下:SELECT{*|col_list}FROMtable1RIGHT[OUTER]JOINtable2ONcondition;3.复合条件连接查询复合条件连接查询是通过在连接查询中添加过滤条件,以达到限制查询结果和筛选数据的目的。4.6子查询1.FROM子句在SQL语句中,当子查询出现在FROM子句中时,系统会将其执行结果生成一个临时表(又称派生表)。语法如下:SELECT{*|col_list}FROM(SELECT*FROMtable1)AStable2[WHEREcondition];2.WHERE子句(1)使用IN关键字在SQL查询中,当子查询返回的是一个单列数据集合,且主查询需要筛选出符合集合中任一条件的记录时,可使用IN关键字实现高效匹配。语法如下:SELECT{*|col_list}FROMtable1WHEREcol_name1IN(SELECTcol_name2FROMtable2[WHEREcondition]);另外,子查询还可以和NOTIN配合使用。4.6子查询(2)使用ANY、SOME关键字ANY与SOME是功能完全等价的关键字,均表示“满足子查询返回值列表中的任一条件”。语法如下:SELECT{*|col_list}FROMtable1WHEREcol_name1<any(SELECTcol_name2FROMtable2[WHEREcondition]);ANY关键字前面可以使用的条件判断符还有“<”“>”“<=”“>=”,根据实际情况进行选择。(3)使用ALL关键字当子查询中使用ALL时,主查询仅会返回完全满足子查询结果集中所有条件的记录。其核心逻辑是将主查询的字段值与子查询返回的所有数据项进行比较,只有当该值与每一个子查询结果都满足比较条件时,才会被纳入结果集。语法如下:SELECT{*|col_list}FROMtable1WHEREcol_name1>All(SELECTcol_name2FROMtable2[WHERE条件]);提示:ALL关键字之前可以使用的条件判断符有“<”“>”“<=”“>=”,但一般不会使用“=”。4.6子查询(4)使用EXISTS关键字EXISTS关键字用于判断子查询是否返回有效数据,其核心逻辑是校验子查询结果集是否非空。语法如下:SELECT{*|col_list}FROMtable1WHEREEXISTS(SELECT*FROMtable2);(5)使用条件判断符在子查询中,还可以单独使用条件判断符。语法如下:SELECT{*|col_list}FROMtable1WHEREcol_name1operators(SELECTcol_name2FROMtable2);4.7合并查询在MySQL中,UNION关键字用于将多条查询语句的结果集进行纵向合并,其核心功能是整合来自不同查询的记录。根据去重策略的不同,UNION分为两种使用方式,UNION(默认去重,过滤合并结果中的重复记录)和UNIONALL(保留全部,保留所有查询结果)。1.单独使用UNION关键字的合并操作,查询结果集会合并在一起,并将重复的记录删除。语法如下:SELECT{*|col_list}FROMtable1UNIONSELECT{*|col_list}FROMtable2;2.使用UNIONALL关键字的合并操作,查询结果集会直接合并在一起,并不会删除重复记录。语法如下:SELECT{*|col_list}FROMtable1UNIONALLSELECT{*|col_list}FROMtable2;任务4任务实施【例4-8】客户信息查询。查找特定条件的客户信息,如客户名称、联系方式等。SELECTcustomers.id,,customers.tel,customers.emailFROMcustomersWHERELIKE'%张三%'ANDemailISNOTNULL;任务4任务实施【例4-9】查询每个销售人员的销售总额。SELECTs.id,,SUM(o.total)AStotal_salesFROMsalespersonssINNERJOINordersoONs.id=o.salespersons_idGROUPBYs.id,;任务4任务实施【例4-10】查询有未解决服务请求的客户信息。SELECTDISTINCTc.id,,c.tel,c.emailFROMcustomerscJOINservice_requestssrONc.id=sr.customers_idWHEREsr.resolution_dateISNULL;任务4任务实施【例4-11】查询每个销售机会的联系人信息。SELECTo.id,,,c.emailFROMopportunitiesoINNERJOINcontactscONo.customer_id=c.customers_id;任务4任务实施【例4-12】查询购买“mateview”商品的客户及其订单详情。SELECT'顾客姓名',o.id'订单编号','商品'FROMcustomerscJOINordersoONc.id=o.customers_idJOINproductspONducts_id=p.idWHERE='mateview';任务4任务实施【例4-13】(自连接查询)查询所有的销售员工以及其所在部门的组长姓名。SELECTS1.name'员工姓名',S1.position,S1.section,S2.nameAS'组长'FROMsalespersonsASS1INNERJOINsalespersonsASS2ONS1.section=S2.sectionWHERES1.position='员工'ANDS2.position='组长';任务4任务实施【例4-14】查询每个客户及其联系人。SELECTc.id,'客户姓名','联系人姓名',c2.tel'联系人电话'FROMcustomerscJOINcontactsc2ONc.id=c2.customers_id;任务4任务实施【例4-15】(子查询)获取销售总额超过特定值的销售人员的姓名和总销售额。第一步:查询销售总额超过特定值的销售人员编号及销售总额。SELECTsalespersons_id,SUM(total)ASsales_amountFROMordersGROUPBYsalespersons_idHAVINGSUM(total)>10000;--假设10000是销售总额的阈值任务4任务实施【例4-15】(子查询)获取销售总额超过特定值的销售人员的姓名和总销售额。第二步:获取销售总额超过特定值的销售人员的姓名和总销售额。SELECT'销售姓名',total_sales.sales_amount'销售金额'FROMsalespersonssJOIN(SELECTsalespersons_id,SUM(total)ASsales_amountFROMordersGROUPBYsalespersons_idHAVINGSUM(total)>10000--假设10000是销售总额的阈值)total_salesONs.id=total_sales.salespersons_id;提示:在这个例子中,子查询total_sales计算了每个销售人员的总销售额,并过滤出那些销售额超过10000的销售人员。然后,外部查询将这些销售人员的姓名和总销售额连接起来。任务4任务实施【例4-16】(子查询)获取每个客户的最新销售机会(使用FROM子查询)。假设opportunities表有一个created_date字段来记录销售机会的创建时间。第一步:查询最新销售机会。SELECTcustomer_id,MAX(created_date)ASlatest_dateFROMopportunitiesGROUPBYcustomer_id;任务4任务实施【例4-16】】(子查询)获取每个客户的最新销售机会(使用FROM子查询)。第二步:获取每个客户的最新销售机会。SELECT,FROMcustomerscJOIN(SELECTcustomer_id,MAX(created_date)ASlatest_dateFROMopportunitiesGROUPBYcustomer_id)latest_oppsONc.id=latest_opps.customer_idJOINopportunitiesoONlatest_opps.customer_id=o.customer_idANDlatest_opps.latest_date=o.created_date;任务4任务实施【例4-17】(子查询)使用SQL语句,查询orders表中字段products_id的所有值,然后使用子查询结果拼接WHERE条件,查询products_id值对应的所有商品的详细信息。第一步:查询订单表中的商品编号。SELECTproducts_idFROMorders;任务4任务实施【例4-17】(子查询)使用SQL语句,查询orders表中字段products_id的所有值,然后使用子查询结果拼接WHERE条件,查询products_id值对应的所有商品的详细信息。第二步:查询所有商品的详细信息。SELECT*FROMproductsWHEREidIN(SELECTproducts_idFROMorders);任务4任务实施【例4-18】(子查询)使用SQL语句,在products表中查询2024年6月1日以后订单中的商品信息。第一步:查询2024年6月1日以后订单的商品编号。SELECTproducts_idFROMordersWHEREorder_date>'2024-6-1';任务4任务实施【例4-18】(子查询)使用SQL语句,在products表中查询2024年6月1日以后订单中的商品信息。第二步:查询2024年6月1日以后订单中的商品信息。SELECT*FROMproductsWHEREid=ANY(SELECTproducts_idFROMordersWHEREorder_date>'2024-6-1');任务4任务实施【例4-19】(子查询)使用SQL语句,查询products表中id值比子查询结果中的最大数据还要大的记录。第一步:查询订单日期在2024年6月19日前的商品编号。SELECTproducts_idFROMordersWHEREorder_date<'2024-06-19';任务4任务实施【例4-19】第二步:查询id值比子查询结果中的最大数据还要大的记录。SELECT*FROMproductsWHEREid>ALL(SELECTproducts_idFROMordersWHEREorder_date<'2024-06-19');任务4任务实施【例4-20】查询products表中products_id值等于7的商品的详细信息。第一步:查询products_id值等于7的商品编号,并去除重复项。SELECTDISTINCTproducts_idFROMordersWHEREproducts_id=7;任务4任务实施【例4-20】第二步:查询products表中products_id值等于7的商品的详细信息。SELECT*FROMproductsWHEREid=(SELECTDISTINCTproducts_idFROMordersWHEREproducts_id=7);任务4任务实施【例4-21】合并查询结果。1.合并salespersons表的销售人员编号及opportunities表中的销售人员编号,并去除重复项。SELECTidFROMsalespersonsUNIONSELECTsalespersons_idFROMopportunities;任务4任务实施【例4-21】合并查询结果。2.合并salespersons表的销售人员编号及opportunities表中的销售人员编号,保留所有结果。SELECTidFROMsalespersonsUNIONALLSELECTsalespersons_idFROMopportunities;任务4任务实施【例4-22】使用SQL语句,查询每个客户的最近一次订单信息。SELECTc.id,,o.id,o.order_dateFROMcustomerscLEFTJOINordersoONc.id=o.customers_idINNERJOIN(SELECTcustomers_id,MAX(order_date)ASlatest_order_dateFROMordersGROUPBYcustomers_id)latest_ordersONo.customers_id=latest_orders.customers_idANDo.order_date=latest_orders.latest_order_date;任务4任务实施【例4-23】查询未分配给销售人员的销售机会。SELECTo.id,FROMopportunitiesoLEFTJOINsalespersonssONo.salespersons_id=s.idWHEREs.idISNULL;任务4任务实施【例4-24】获取尚未解决的服务请求数量最多的前五个客户。SELECT,COUNT(sr.id)ASopen_requestsFROMcustomerscLEFTJOIN(SELECTcustomers_id,idFROMservice_requestsWHEREresolution_dateISNULL)srONc.id=sr.customers_idGROUPBYORDERBYopen_requestsDESCLIMIT5;任务4任务实施【例4-24】获取尚未解决的服务请求数量最多的前五个客户。虽然这个查询在FROM子句中并没有直接使用子查询(因为子查询是作为LEFTJOIN的一部分),但它展示了如何结合使用子查询和连接来解决问题。如果要直接在FROM子句中使用子查询,可以稍做调整,具体如下:SELECTcustomer_,customer_info.open_requestsFROM(SELECT,COUNT(sr.id)ASopen_requestsFROMcustomerscLEFTJOINservice_requestssrONc.id=sr.customers_idANDsr.resolution_dateISNULLGROUPBY)customer_infoORDERBYcustomer_info.open_requestsDESCLIMIT5;任务5:设计和制作索引优化检索路径,提速数据访问任务5任务描述在客户关系管理系统数据库中,数据库存储了大量的客户信息、销售数据、营销数据等关键业务信息。随着数据量的增加,查询性能可能会逐渐下降,影响系统的整体响应速度。因此,合理设计和制作索引对于提升系统性能至关重要。1.首先,需要分析CRM系统中的主要查询需求,确定哪些字段经常出现在查询条件中。2.根据查询需求选择合适的索引类型,如B-树索引、哈希索引等。在关系数据库中,B-树索引是最常用的索引类型之一。3.创建索引。4.创建索引后,需要测试索引的效果,确保它能够提高查询性能。可以使用查询分析器或执行计划等工具来评估索引的性能。5.根据测试结果,对索引进行优化。例如,如果某个索引的使用频率很低,可以考虑删除它以节省存储空间;如果某个查询的性能仍然不佳,可以考虑添加新的索引或调整现有索引的结构。4.8索引的设计原则描索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。以下为索引的设计原则:1.当表数据量较小时,不建议创建索引,否则通过索引查询记录可能比查询整张表还要慢。2.索引并非越多越好,单表索引数建议不超过5个。过多的索引会占用大量磁盘空间,并且会影响插入、修改和删除等语句的性能。3.对于经常执行修改操作的表不要创建过多索引,优先为主键和外键创建索引,并且索引中的列应尽可能少。可为高频查询字段创建索引,允许适当增加索引数量。4.在条件表达式中经常会在不同值较多的列上创建索引,不同值较少的列不建议单独创建索引。因为这样不仅不会提高查询效率,反而可能低于全表扫描。5.对频繁使用ORDERBY或GROUPBY的列创建索引,索引顺序应与排序方向一致。如果待排序的列有多个,可以在这些列上创建组合索引,避免数据库额外排序操作。4.8索引的设计原则描MySQL在单个或多个列上创建索引的方式有很多种,可以在创建表的同时创建索引,也可以使用ALTERTABLE或CREATEINDEX语句在已有的表上创建索引。语法如下:CREATETABLEtable_name([col_namedata_type,]INDEX|KEYindex_name(col_name1[(length)])[ASC|DESC],...(col_namen[(length)])[ASC|DESC]));4.9普通索引普通索引是MySQL数据库中最基本的索引类型,它没有任何限制,主要用于加速查询。普通索引由KEY或INDEX关键字定义,可以创建在任何数据类型中,其值是否唯一和非空由字段本身的约束条件所决定。1.在创建表时指定索引。语法如下:CREATETABLEtable_name(column1datatype,column2datatype,...INDEXindex_name(column_name));2.使用CREATEINDEX语句创建索引。语法如下:CREATEINDEXindex_nameONtable_name(column_name);3.使用ALTERTABLE语句创建索引。语法如下:ALTERTABLEtable_nameADD[INDEX|KEY]index_name(column_name);4.10组合索引索引还可以创建在多个字段上,这种索引称为组合索引。组合索引(也称为复合索引)在MySQL中是一个重要的索引类型,它基于多个列创建索引结构,以提高在这些列上进行查询的效率。组合索引语法参照普通索引,在数据库表的两个或多个列上创建索引。提示:在创建组合索引时,需要仔细考虑索引列的选择和顺序。通常,应该将查询条件中经常出现的列、过滤性强的列放在前面。4.11唯一索引唯一索引(UNIQUEINDEX)是MySQL数据库中一种重要的索引类型,它用于确保表中某列或列组合的值具有唯一性,从而避免数据重复。创建索引的主要目的是减少查询索引列操作的执行时间。1.在创建表时指定索引。语法如下:CREATETABLEtable_name(column1datatype,column2datatype,...UNIQUEindex_name(column1,column2,...));2.使用CREATEINDEX语句创建索引。语法如下:CREATEUNIQUEINDEXindex_nameONtable_name(column1,column2);3.使用ALTERTABLE语句创建索引。语法如下:ALTERTABLEtable_nameADDUNIQUEINDEXindex_name(column1,column2);4.12全文索引全文索引(FULLTEXT)是一种专门优化文本搜索的索引技术,可快速定位包含特定关键词的文本内容。只能在数据类型为CHAR、VARCHAR和TEXT的列上创建,并且只有MyISAM存储引擎支持全文索引。1.在创建表时指定索引。语法如下:CREATETABLEtable_name(column1datatype,column2datatype,...FULLTEXTindex_name(column1,column2));2.使用CREATEINDEX语句创建索引。语法如下:CREATEFULLTEXTINDEXindex_nameONtable_name(column1,column2);3.使用ALTERTABLE语句创建索引。语法如下:ALTERTABLEtable_nameADDFULLTEXTINDEXindex_name(column1,column2);4.13查看索引1.使用SHOWINDEX语句查看索引。语法如下:SHOWINDEXFROMtable_name\G2.使用SHOWCREATETABLE语句查看表结构。语法如下:SHOWCREATETABLEtable_name\G4.14删除索引如果要在MySQL中删除索引,可以使用ALTERTABLE或DROPINDEX语句。1.使用ALTERTABLE语句删除索引。语法如下:ALTERTABLEtable_nameDROPINDEXindex_name;2.使用DROPINDEX语句删除索引。语法如下:DROPINDEXindex_nameONtable_name;提示:删除主键索引时,语法形式与前面有所不同,语法如下:ALTERTABLEtable_namedropPRIMARYKEY;任务5任务实施【例4-25】创建唯一索引。客户表(customers):主键索引。索引名称:idx_customer_id。字段:id(主键)。说明:每个客户都有一个唯一的ID,作为主键的索引是必须的。1.创建索引。CREATEUNIQUEINDEXidx_customer_idONcustomers(id);2.使用SHOWINDEX语句查看索引。SHOWINDEXFROMcustomers;任务5任务实施【例4-26】创建普通索引。客户表(customers):名称索引。索引名称:idx_customer_name。字段:Name。说明:经常需要根据客户名称进行搜索,因此为名称字段创建索引。1.创建索引。CREATEINDEXidx_customer_nameONcustomers(name);2.使用SHOWINDEX语句查看索引。SHOWINDEXFROMcustomers;任务5任务实施【例4-27】创建普通索引。销售机会表(opportunities):客户ID索引。索引名称:idx_opportunity_customer_id。字段:Customer_ID。说明:快速检索属于特定客户的所有销售机会。1.创建索引。CREATEINDEXidx_opportunity_customer_idONopportunities(Customer_ID);2.使用SHOWINDEX语句查看索引。SHOWINDEXFROMopportunities;任务5任务实施【例4-28】创建复合索引。销售机会表(opportunities):状态和日期复合索引。索引名称:idx_opportunity_status_date。字段:present_stage,created_date。说明:复合索引,用于查询特定状态且在特定日期后修改的销售机会。1.创建索引。CREATEINDEXidx_opportunity_status_dateONopportunities(present_stage,created_date);2.使用SHOWINDEX语句查看索引。SHOWINDEXFROMopportunities;任务5任务实施【例4-29】创建复合索引。联系人表(contacts):客户ID和销售人员姓名复合索引。索引名称:idx_contact_customer_id_lastname。字段:customers_id,name。说明:复合索引,用于快速找到属于特定客户及对应的销售人员。1.创建索引。CREATEINDEXidx_contact_customer_id_lastnameONcontacts(customers_id,name);2.使用SHOWINDEX语句查看索引。SHOWINDEXFROMcontacts;任务5任务实施【例4-30】创建复合索引。服务请求表(service_requests):状态和请求日期复合索引。索引名称:idx_service_request_status_date。字段:Status,RequestDate。说明:复合索引,用于查询按状态和时间排序的服务请求。1.创建索引。CREATEINDEXidx_service_request_status_dateONservice_requests(State,resolution_date);2.使用SHOWINDEX语句查看索引。SHOWINDEXFROMservice_requests;任务5任务实施【例4-31】创建普通索引。商品表(products):商品名称索引。索引名称:idx_product_name。字段:Name。说明:快速检索商品名称。1.创建查询。CREATEINDEXidx_product_nameONproducts(name);2.使用SHOWINDEX语句查看索引。SHOWINDEXFROMproducts;任务5任务实施【例4-32】创建普通索引。订单表(orders):客户ID和订单日期复合索引。索引名称:idx_order_customer_id_orderdate。字段:customers_id,order_date。说明:用于查找特定客户在特定日期下的订单。1.创建查询。CREATEINDEXidx_order_customer_id_orderdateONorders(customers_id,order_date);2.使用SHOWINDEX语句查看索引。SHOWINDEXFROMorders;任务5任务实施【例4-33】删除索引。商品表(products):商品名称索引。索引名称:idx_product_name。1.删除索引。ALTERTABLEproductsDROPINDEXidx_product_name;任务5任务实施【例4-34】删除索引。客户表(customers):名称索引。索引名称:idx_customer_name。1.删除索引。DROPINDEXidx_customer_nameONcustomers;任务6:设计和制作触发器自动响应事件,保障业务联动任务6任务描述在客户关系管理系统数据库中,触发器的任务可以多样化,目的是自动化数据处理、维护数据一致性、记录数据变更历史以及实现复杂的业务逻辑。1.数据同步与更新(1)当CRM系统中的某个表(如客户表)的数据发生变化时,触发器可以自动更新其他相关表(如订单表、联系人表)中的相应字段。这有助于保持数据的一致性和完整性。(2)根据业务需求,触发器可以在数据插入或更新时自动填充或更新特定字段的值,如时间戳、状态码等。任务6任务描述2.强制数据完整性(1)触发器可以在数据插入或更新前检查数据的合法性,如检查客户ID是否唯一、电话号码格式是否正确等。如果数据不符合要求,触发器可以阻止操作并返回错误信息。(2)虽然数据库通常通过外键约束来维护表之间的关系,但触发器可以进一步补充这些约束,确保在删除或更新数据时不会违反业务规则。3.记录数据变更历史(1)触发器可以记录每次数据变更的详细信息,包括变更时间、变更前后的数据值以及执行变更的用户等。这对于后续的审计和追踪非常有用。(2)在某些情况下,当关键数据发生变更时,CRM系统可能需要向相关人员发送通知。触发器可以自动触发这些通知的发送。4.15触发器概述触发器(Trigger)是数据库管理系统中的一种特殊对象或存储过程,用于在指定的事件(如INSERT、UPDATE或DELETE)发生时自动执行。触发器通常与表相关联,当表中的数据发生变化时,触发器会根据预定义的规则自动执行相应的操作,无需人工调用,当程序满足定义条件时就会被MySQL自动调用。触发器的目的是帮助维护数据的完整性、实现复杂的业务规则以及自动化管理任务。根据触发器定义在触发事件之前或之后,分为:AFTER触发器和BEFORE触发器,分别在触发完成后和触发完成前触发执行触发器函数。4.15触发器概述触发器是数据库管理中的一个强大工具,通过自动化复杂的业务逻辑、维护数据完整性、实现引用完整性、记录审计日志、执行级联操作、同步数据以及发送通知等功能,为数据库管理提供了极大的便利和灵活性。触发器的主要用途:(1)自动执行复杂业务规则。(2)维护数据的完整性。(3)实现复杂的引用完整性。(4)自动化审计和日志记录。(5)实现自动级联操作。(6)同步数据。(7)自动发送通知。4.16创建触发器创建触发器,语法如下:CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtable_nameFOREACHROWtrigger_body提示:触发器只能创建在永久表(PermanentTable)上,不能创建在临时表(TemporaryTable)上。1.创建AFTER触发器AFTER触发器是在触发事件执行完成后激活的触发器,其执行操作无法影响触发事件本身,常用于日志记录、统计更新等后续处理场景。AFTER触发器可以根据所监视的事件分为三种,分别是INSERT型、UPDATE型和DELETE型。2.创建BEFORE触发器BEFORE触发器是在触发事件执行完成前激活的触发器,激活后执行的操作先于监视的事件,这样就有机会进行一些判断,或修改即将发生的操作。BEFORE触发器也可以根据监视事件分为三
种,分
别
是INSERT型、UPDATE型
和DELETE型。4.17查看触发器在MySQL中,查看触发器有两种方法,一种是使用SHOWTRIGGERS语句,可快速列出当前数据库中所有触发器的基本信息;一种是查询information_schema数据库的triggers表,可获取触发器的完整元数据。在创建触发器之前,最好能够查看MySQL中是否已经存在该触发器。1.使用SHOWTRIGGERS语句可以查看MySQL中已经存在的触发器。语法如下:SHOWTRIGGERS\G2.使用SHOWTRIGGERS语句不仅可以查看所有触发器,也可以查看某个表上创建的触发器。语法如下:SHOWTRIGGERSFROMdb_nameLIKE'table_name'\G另外,如果用户需要精确查看某一个触发器,也可以使用SHOWTRIGGERS语句。语法如下:SHOWTRIGGERSWHERETRIGGERLIKE'trigger_name%'\G3.MySQL中所有触发器的定义都存储在系统数据库information_
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 喉癌的放疗护理课件
- 盾构机操作工成果考核试卷含答案
- 不锈钢真空容器制作工标准化考核试卷含答案
- 浴池服务员安全文明水平考核试卷含答案
- 景区运营管理师安全技能知识考核试卷含答案
- 药品购销员班组安全强化考核试卷含答案
- 丁二酸装置操作工安全技能模拟考核试卷含答案
- 2026年黑龙江生态工程职业学院高职单招笔试职业适应性测验历年参考题库含答案解析
- 2026年黑龙江信息技术职业学院高职单招笔试数学历年参考题库含答案解析
- 2026年首都经济贸易大学密云分校高职单招笔试语文历年参考题库含答案解析
- 2026年植物保护(植物病理学基础)试题及答案
- 中国融通笔试题及答案
- 2026重庆保税港区集团校园招聘20人笔试参考题库及答案解析
- 2026年消防宣传月全员消防安全专题培训
- 2026年北师大版中考数学基础训练模拟试卷及答案
- 中国皮肤型红斑狼疮诊疗指南 2026 年版
- 2026年济南市检察院书记员考试题(附答案)
- 职业病危害现状评价任勇
- (正式版)DB11∕T 2046.2-2022 《智慧停车系统技术要求 第2部分:停车场(库)外场设备》
- 医疗器械经营质量管理制度工作程序手册
- 多重耐药菌感染诊疗与防控指南(2025 版)
评论
0/150
提交评论