MySQL数据库应用与开发技术

MySQL数据库应用与开发技术第1章MySQL数据库基础本单元教学目标了解数据库的功能作用认识数据库发展历程了解数据库类型及分类理解数据库组成结构掌握数据库的数据组织形式3数据库概念按照某类型结构形式来组织、存储和管理数据的存储空间存在大量的数据集合体可以长期存储在计算机内以实现信息共享、高效存取等操作受数据库系统机制的相关约束4数据库发展历程人工管理阶段使用穿孔纸带来管理数据文件系统阶段使用专门管理数据的软件

数据库系统阶段使用统一管理数据的专门软件系统5数据库系统组成数据：存储在计算机内有组织、可共享的数据集合硬件：包括计算机的主机、键盘、显示器和外围设备软件：数据库管理系统及应用程序人员：数据库设计人员、应用程序员、最终用户、数据库管理员6SQL语言SQL结构化查询语言StructuredQueryLanguage是一种数据库查询和程序设计语言用于存取数据以及查询、更新和管理关系数据库系统是一个综合的、通用的、功能极强的关系数据库语言1974年Boyce和Chamberlin提出SQL语言实现思想在IBM公司研制的关系数据库系统上实现功能丰富、使用方便灵活、语言简洁易学深受计算机工业界和计算机用户的欢迎7SQL六大语言模块（1）数据查询语言（DataQueryLanguage，DQL）用以从表中获得数据，并确定怎样在应用程序给出命令WHERE、ORDERBY、GROUPBY、HAVING等数据操作语言（DataManipulationLanguage，DML）规定了对数据表的写操作语句动词INSERT、UPDATE、DELETE事务控制语言（TransactionControlLanguage，TCL）定义了对数据表的事务控制操作语句命令COMMIT、SAVEPOINT、ROLLBACK等8SQL六大语言模块（2）数据控制语言（DataControlLanguage，DCL）规定了对表的权限控制操作语句动词GRANT、REVOKE数据定义语言（DataDefinitionLanguage，DDL）规定了对表的构建操作语句动词CREATE、ALTER、DROP指针控制语言（CursorControlLanguage，CCL）规定了SQL语句在宿主语言程序中的使用规则动词DECLARECURSOR、FETCHINTO等9数据表数据表是数据库中主要的数据存储容器表中的数据被组织成⾏和列表中的每一列代表一种属性数据，称为字段表中每列均有⼀个名称为数据表的字段名称每个列都具有⼀个指定的数据类型和容量⼤⼩表中的⼀⾏代表⼀条信息数据10数据类型数据类型是数据表中数据种类的定义代表了不同的信息类型数据类型决定了数据在磁盘中的存储格式数据库中的数据类型数值类型日期类型字符类型MySQL数据库应用与开发技术第2章数据表操作本单元教学目标了解数据表的功能作用认识数据库构建语句掌握数据表的构建过程及操作掌握数据表的各种约束条件MySQL自带库节点（1）information_schemaMySQL系统库存储数据库的元数据信息如数据库，表的名称，列的数据类型或者访问权限performance_schemaMySQL系统库记录MySQL服务进程对资源的消耗、进程等待等信息mysqlMySQL系统库记录数据库服务器运行时所需要的系统信息如数据文件夹、字符集、约束检查、系统用户等MySQL自带库节点（2）sakilaMySQL数据库的样例节点供参考如何进行数据库、数据表设计，以及提供视图、存储过程开发worldMySQL数据库的样例节点比“sakila”简单得多，只有四张数据表帮助操作人员快速在本地建立MySQL数据库节点环境testMySQL的测试库可在此库任意创建自己所需要的数据表以及其它各类型实体如视图、存储过程、触发器等数据库创建数据库节点创建语法：CREATEDATABASE数据库节点名称CREATEDATABASEHELLO创建了一个名称为“HELLO”的库节点CREATEDATABASEDEMO创建了一个名称为“DEMO”的库节点CREATEDATABASEABC创建了一个名称为“ABC”的库节点数据库查询数据库节点查询语句SHOWDATABASES数据库删除数据库节点删除语法DROOP

DATABASE

数据库节点名称DROOPDATABASEHELLO删除了一个名称为“HELLO”的库节点DROOPDATABASEDEMO删除了一个名称为“DEMO”的库节点DROOPDATABASEABC删除了一个名称为“ABC”的库节点数据表主键主键（PrimaryKey）数据表中非常重要的一个核心元素用于标识数据表中记录的唯一性通过主键能在数据表中找到唯一的数据记录主键由数据表中的一个或多个字段承担主键举例在一个学生表中，每学生的学号都是唯一，这个属性就可以作为学生表的主键字段通过主键值“20000136”可以在表中找到对应标号为①的唯一记录通过主键值“20000153”可以在表中找到对应标号为②的唯一记录数据表外键外键（ForeignKey）是数据表中另外一个非常重要的核心元素用于传递数据表之间的关联性一个数据表的业务字段去关联另一个数据表的唯一性字段就构成主外键引用关系去引用关联其它数据表的字段则声明为外键外键举例如存在一个教师表，其中教工号(teacher_id)字段的值在表中唯一，不重复学生表中有班主任标识(teacher_id)字段，记录表中每个学生所对应的班主任是哪一位老师这样学生表与教师表便形成外键关联引用关系数据类型分类整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT小数类型FLOAT、DOUBLE、DECIMAL字符串类型CHAR、VARCHAR、TEXT日期类型DATE、DATETIME、TIMESTAMP、TIME其他数据类型BLOB创建数据表数据表存在于数据库节点中创建数据表之前先创建好相关数据库节点可以直接在已存在的数据库节点中创建数据表创建数据表的过程定义数据列的属性过程约束数据完整性的过程进入库节点进入库节点语法USE数据库节点名称USEDEMO进入名称为“demo”的库节点USEHELLO进入名称为“hello”的库节点USEABC进入名称为“abc”的库节点数据表创建语法CREATETABLE数据表名称(字段1数据类型(长度约束),字段2数据类型(长度约束),…字段n数据类型(长度约束))数据表创建｜注意事项SQL语句不区分大小写每条语句结尾一般用英文状态下的分号“;”表示语句的结束创建数据表过程中，数据表名称可以任意，但不能使用SQL语句中的关键字表中每个字段之间用英文状态下的逗号“,”隔开案例应用用SQL语句创建一个顾客(customer)数据表数据表中的相关数据项如下字段1：顾客标识（id）INT字段2：顾客姓名（name）VARCHAR(45)字段3：顾客年龄（age）SMALLINT字段4：顾客生日（birthday）DATE字段5：顾客身高（height）FLOAT字段6：顾客职业（work）VARCHAR(45)字段7：顾客薪酬（salry）INT字段8：顾客地址（address）VARCHAR(45)SQL参考实现CREATETABLEcustomer(idint,namevarchar(45),agesmallint,birthdaydate,heightfloat,workvarchar(45),salaryvarchar(45),addressint);查看库节点数据表库节点数据表查看语句SHOWTABLES查看数据表结构（1）语法格式DESC数据表名称DESCuser查看“user”表结构DESCorder查看“order”表结构DESCmember查看“member”表结构查看数据表结构（2）语法格式DESCRIBE数据表名称DESCRIBEstudent查看“student”表结构DESCRIBEschool查看“school”表结构DESCRIBEcollege查看“college”表结构数据表添加字段添加字段语法：ALTERTABLE数据表名称ADDCOLUMN新字段数据类型AFTER表中某个字段ALTERTABLEuser_orderADD

COLUMNorder_addressVARCHAR(45)AFTERorder_time;数据表修改字段修改字段语法：ALTERTABLE数据表名称MODIFY字段名称新数据类型或约束条件ALTER

TABLE

user_orderMODIFYmoneyDECIMAL(6,2);数据表删除字段删除字段语法：ALTERTABLE数据表名称DROP字段名称ALTER

TABLEuser_orderDROPorder_address;数据表名称修改修改数据表名称语法：ALTERTABLE原数据表名称RENAMETO新数据表名称ALTER

TABLEuser_orderRENAME

TOnew_user_order;删除数据表结构删除表结构语法：DROPTABLE数据表名称DROP

TABLEnew_user_order;数据表约束数据完整性约束实体完整性主键约束参照完整性外键约束用户自定义完整性额外添加的约束项单字段主键单字段主键语法：字段名称数据类型PRIMARYKEYCREATETABLEbook(idintPRIMARYKEY,namevarchar(45),pricefloat,isbnvarchar(45));联合主键联合主键语法：PRIMARYKEY(字段1，字段2…，字段n)CREATETABLEstudent_course(studentvarchar(45),coursevarchar(45),scoreint,statuschar(1),PRIMARYKEY(student,course));外键声明外键声明语法：CONSTRAINT外键名称FOREIGNKEY(引用字段)REFERENCES被关联数据表(被引用字段)主表声明：CREATETABLEcountry(idintPRIMARYKEY,namevarchar(45),populationfloat);从表外键声明：CREATETABLEmember(idintPRIMARYKEY,namevarchar(45),country_idint,

CONSTRAINTfk_country_memberFOREIGN

KEY(country_id)REFERENCEScountry(id));非空约束一种对数据表中字段值不允许置为空的约束非空约束语法：字段名称数据类型NOTNULLCREATETABLEcomputer(idintPRIMARYKEY,cpuvarchar(45)NOTNULL,pricefloatNOTNULL,productvarchar(45)NOTNULL);唯一性约束要求数据表中相关字段的值不能重复允许空值存在，但空值最多只能一个可以为多个字段添加唯一性约束唯一性约束语法：字段名称数据类型UNIQUECREATETABLEemployee(idintPRIMARYKEY,work_numvarchar(45)UNIQUE,card_numvarchar(45)UNIQUE,birthdaydate,departmentvarchar(45));自定义约束使用CHECK()函数来实现在MySQL8.0.16之前不支持CHECK约束CREATETABLEuser_salary(idintprimarykey,uservarchar(50)notnull,salaryintnotnullcheck(salary<10000));主键自增也叫字段自增，专为主键字段服务的约束类型主键字段能自动填充数据，并以序列的形式自增只适用于主键为整型类型字段，不适用其它类型主键自增语法：字段名称数据类型PRIMARYKEYAUTO_INCREMENTCREATETABLEexam(idintPRIMARYKEYAUTO_INCREMENT,stu_numvarchar(45),reg_serint,exam_timedate,exam_scoresmallint);MySQL数据库应用与开发技术第3章数据检索操作本单元教学目标了解数据SQL语句基础语法认识数据查询的条件过滤类型熟悉数据表的多条件检索熟悉子查询的类型及相关语法掌握数据表的分组、排序语法掌握数据表检索中聚合函数的应用掌握多表连接查询操作SQL语句流转

47查询检索SQL基本语法：SELECT字段1,字段2,…,字段n,FROM数据表数据检索各类条件按字段检索数据SELECTUSER_ID,USER_NAME,USER_AGEFROMUSER;给检索字段及数据表添加别名SELECTUSER_IDASID,USER_NAMEASNAME,USER_AGEASAGEFROMUSERASU;全字段检索数据表SELECT*FROMUSER;48案例应用｜基本检索某地区有若干的百货超市，每个百货超市有固定的营业地址，每个百货超市有固定的员工数，每个百货超市有固定的负责人，上半年（1月-6月）每个百货超市均有相应零售销售额度。49百货超市数据表｜supermarket字段1：主键标识（shop_id）INTPrimaryKey字段2：百货超市名称（shop_name）VARCHAR(45)字段3：百货超市地址（shop_address）VARCHAR(45)字段4：百货超市的员工数（shop_employee）INT字段5：百货超市的门店经理（shop_manager）VARCHAR(45)字段6：月度销售额（sale_money）INT字段7：销售月份（sale_month）INT50案例应用｜SQL脚本DROPTABLEIFEXISTSsupermarket;CREATETABLEsupermarket(shop_idint(10)unsignedNOTNULLauto_increment,shop_namevarchar(45)NOTNULL,shop_addressvarchar(45)NOTNULL,shop_employeeint(10)unsignedNOTNULL,shop_managervarchar(45)NOTNULL,sale_moneyint(10)unsignedNOTNULL,sale_monthint(10)NOTNULL,PRIMARYKEY(shop_id))ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8;INSERTINTOsupermarket(shop_id,shop_name,shop_address,shop_employee,shop_manager,sale_money,sale_month)VALUES(1,'百佳','沿江路32号',300,'刘金华',600000,1),(2,'万佳','建国路78号',250,'陈恩利',700000,1),(3,'人人乐','越王路20号',280,'黄志军',540000,1),(4,'新一佳','兴华路15号',230,'陈新丽',400000,1),(5,'百佳','沿江路32号',300,'刘金华',650000,2),(6,'万佳','建国路78号',250,'陈恩利',720000,2),(7,'人人乐','越王路20号',280,'黄志军',520000,2),(8,'新一佳','兴华路15号',230,'陈新丽',430000,2),(9,'百佳','沿江路32号',300,'刘金华',630000,3),(10,'万佳','建国路78号',250,'陈恩利',750000,3),(11,'人人乐','越王路20号',280,'黄志军',500000,3),(12,'新一佳','兴华路15号',230,'陈新丽',450000,3),(13,'百佳','沿江路32号',300,'刘金华',610000,4),(14,'万佳','建国路78号',250,'陈恩利',680000,4),(15,'人人乐','越王路20号',280,'黄志军',550000,4),(16,'新一佳','兴华路15号',230,'陈新丽',460000,4),(17,'百佳','沿江路32号',300,'刘金华',580000,5),(18,'万佳','建国路78号',250,'陈恩利',740000,5),(19,'人人乐','越王路20号',280,'黄志军',490000,5),(20,'新一佳','兴华路15号',230,'陈新丽',380000,5),(21,'百佳','沿江路32号',300,'刘金华',620000,6),(22,'万佳','建国路78号',250,'陈恩利',760000,6),(23,'人人乐','越王路20号',280,'黄志军',560000,6),(24,'新一佳','兴华路15号',230,'陈新丽',470000,6);51案例应用｜业务检索检索出各百货超市各个月份的销售额度SQL语句1：SELECTshop_id,shop_name,sale_money,sale_monthFROMsupermarket;SQL语句2：SELECTshop_idASid,shop_nameASname,sale_moneyASmoney,sale_monthASmonthFROMsupermarkets;52数据检索条件过滤｜WHEREWHERE条件过滤语法：SELECT检索字段FROM数据表WHERE数据条件1AND/OR数据条件2AND/OR数据条件3…数据条件nAND连接数据条件SELECT*FROMUSERWHEREUSER_ROLE='admin'ANDUSER_STATUS='success';OR连接数据条件SELECT*FROMUSERWHEREUSER_ROLE='manager'ORUSER_RANK='vip';AND与OR同时连接数据条件SELECT*FROMUSERWHERE(USER_ROLE='admin'ANDUSER_STATUS='success')OR(USER_RANK='vip');53数据筛选规则｜等于等于匹配规则：比较数值相等或字符内容相同的数据筛选规则适用于数字类型或字符类型的数据项使用符号“=”左边为筛选的数据项右边为所筛选的数字值或字符值规则格式：数据项=筛选数值或字符如：USER_AGE=20如：USER_ROLE='admin'54数据筛选规则｜大于大于、大于或等于匹配规则一种比较数据筛选方式只适用于数值类型的数据项进行数据过滤大于匹配规则使用符号“>”表示相关值要大于指定的数值才符合数据筛选条件大于或等于匹配规则使用符号“>=”表示相关值如果大于或等于指定的数值就符合数据筛选条件符号左边为筛选的数据项，右边为所筛选的数字值规则格式：数据项>筛选数字值如：EMPLOYEE_SALARY>6000数据项>=筛选数字值如：ONLINE_HOURS>=1255数据筛选规则｜小于小于、小于或等于匹配规则一种比较数据筛选方式只适用于数值类型的数据项进行数据过滤小于匹配规则使用符号“<”表示相关值要大于指定的数值才符合数据筛选条件小于或等于匹配规则使用符号“<=”表示相关值如果小于或等于指定的数值就符合数据筛选条件符号左边为筛选的数据项，右边为所筛选的数字值规则格式：数据项<筛选数字值如：COURSE_SCORE<90数据项<=筛选数字值如：STUDENT_PERSONS<=4056数据筛选规则｜介于介于匹配规则一种在某个范围内进行筛选的数据过滤方式只适用于类型为数字的数据项表示数据要大于或等于某个边界数值同时要小于或等于某个边界数值的数据才符合筛选条件使用关键词“BETWEEN”与“AND”关键词“BETWEEN”后面跟数值较小的边界值关键词“AND”后面跟数值较大的边界值规则格式：数据项BETWEEN数字值1AND数字值2如：TV_PRICEBETWEEN4500AND5000如：MEMBER_AMOUNTBETWEEN600AND80057数据筛选规则｜指定指定匹配规则：一种让数据项与给定的数据元素列表相吻合的数据筛选方式适用于数字类型或字符类型的数据项命中了给定的数据列表中的元素即符合数据筛选条件指定匹配规则要使用关键词“IN”关键词左边为筛选的数据项右边为所给定的数据元素列表数据元素在小括号内，各元素用英文状态下的逗号隔开规则格式：数据项IN(元素1,元素2,…,元素n)如：USER_IDIN(10,16,20,25,29,32,40)如：USER_NAMEIN('Kerry','Rose','Honey','Wendy','Tomy')58数据筛选规则｜类似类似是一种模糊的字符配置规则需要使用关键词“LIKE”按照某种模糊的方式来配匹数据使用百分号“%”来通配字符三种类型：前模糊匹配后模糊匹配全模糊匹配59前模糊匹配前模糊匹配是指：前半部分使用模糊匹配符号“%”与指定字符相组合通配符“%”表示任意字符前半部分字符任意，能与后半部分字符匹配即可整个匹配表达式需用英文状态下的单引号引起来规则格式：LIKE'%指定过滤字符'如：LIKE'%BMW'只要以“BMW”结尾的数据项就符合筛选条件如：LIKE'%Bus'只要以“Bus”结尾的数据项就符合筛选条件60后模糊匹配后模糊匹配是指：后半部分使用模糊匹配符号“%”与指定字符相组合通配符“%”表示任意字符后半部分字符任意，能与前半部分字符匹配即可整个匹配表达式需用英文状态下的单引号引起来规则格式：LIKE'指定过滤字符%'如：LIKE'Book%'只要以“Book”开头的数据项就符合筛选条件如：LIKE'Order%'只要以“Order”开头的数据项就符合筛选条件61全模糊匹配全模糊匹配：将匹配字符内容分成：前半部分、中间部分、后半部分前半部分内容与后半部分内容使用模糊匹配符号“%”中间部分指定具体字符整个匹配表达式的含义：所筛选数据项只要包含中间部分字符，即符合数据筛选条件整个匹配表达式需用英文状态下的单引号引起来规则格式：LIKE'%指定过滤字符%'如：LIKE'%VIP%'只要包含“VIP”字符的数据项就符合筛选条件如：LIKE'%OK%'只要包含“OK”字符的数据项就符合筛选条件62数据检索条件过滤｜HAVINGHAVING条件过滤语法：SELECT数据项1，数据项2，…

数据项nFROM数据表HAVING数据条件1AND/OR数据条件2AND/OR数据条件3…

数据条件n关键词“HAVING”作条件数据筛选时，其不针对表中的字段，而是针对关键词“SELECT”后面所列出的数据项如果数据项没有出现在“SELECT”关键词的后面，则不能作为筛选的条件数据项63HAVIGN与WHERE的区别针对目标对象不同：“WHERE”子句针对表中的列发挥作用，检索数据时，只能对数据表中的列设置筛选条件“HAVING”子句则对查询结果中的数据项发挥作用，检索数据时，只能对查询结果的返回数据项设置筛选条件SELECTgoods_name,(market_price-shop_price)asdif_priceFROMgoodsHAVING

dif_price=20对聚合函数适用性不同：“WHERE”子句中不能使用聚合函数“HAVING”子句中可以使用聚合函数若“HAVING”子句与“GROUPBY”子句同时期出现，则“HAVING”子句需位于“GROUPBY”子句的后面SELECTuser_name,user_rank,user_addressFROMorderGROUPBYuser_nameHAVINGSUM(order_money)=1500案例应用｜数据条件过滤以本章百货超市（supermarket）数据表为例，实现以下相关业务查询检索需求。检索出各百货超市1月份的销售额度。检索出“万佳”百货超市3月份以及“人人乐”百货超市5月份的销售额度。检索出百货超市名称（shop_name）中包含“佳”字符的相关数据信息。检索出月销售度介于600000-700000之间的百货超市的名称、月度、相关月销售额信息。65案例应用｜业务检索（1）检索出各百货超市1月份的销售额度SQL语句1：SELECTshop_id,shop_name,shop_address,shop_employee,shop_manager,sale_money,sale_monthFROMsupermarketWHEREsale_month=1;SQL语句2：SELECT*FROMsupermarketWHEREsale_month=1;66案例应用｜业务检索（2）检索出“万佳”百货超市3月份以及“人人乐”百货超市5月份的销售额度SELECT*FROMsupermarketWHERE(shop_name='万佳'ANDsale_month=3)OR(shop_name='人人乐'ANDsale_month=5);67案例应用｜业务检索（3）检索出百货超市名称（shop_name）中包含“佳”字符的相关数据信息SELECT*FROMsupermarketWHEREshop_nameLIKE'%佳%';68案例应用｜业务检索（4）检索出月销售度介于600000-700000之间的百货超市的名称、月度、相关月销售额信息SELECTshop_name,sale_money,sale_monthFROMsupermarketWHEREsale_moneyBETWEEN600000AND700000;69案例应用｜业务检索（5）检索各百货超市中出1-6月份，每月的最低销售额在400000元以上的百货超市的名称、门店地址、员工数、门店主管相关信息。SELECTshop_name,shop_address,shop_persons,shop_managerFROMsupermarketGROUPBYshop_nameHAVINGMIN(sale_month_money)>400000;70数据检索分组对同一系列数据做分类处理要使用关键词“GROUPBY”可以对多个数据项进行分组操作分组从句与条件过滤从句同时出现“WHERE”关键词在前“GROUPBY”关键词在后71数据分组语法语法格式：SELECT检索字段FROM数据表WHERE数据条件GROUPBY分组数据项1,分组数据项2,…,分组数据项n单维度分组SELECTUSER_BUY、SUM(ORDER_MONEY)、USER_RANKFROMORDERGROUPBYUSER_BUY;多维度分组SELECTCOLLEGE、MAJOR、CLASS、AVG(SCORE)FROMUNIVERSITY_SCHOOLGROUPBYCOLLEGE,MAJOR,CLASS;72案例应用｜数据分组以本章百货超市（supermarket）数据表为例，实现以下相关业务查询检索需求。检索出各百货超市的名称、所在地、负责人、职工人数检索出各百货超市1-3月份的销售额度检索出各百货超市1月-6月的销售总额73案例应用｜业务检索（1）检索出各百货超市的名称、所在地、负责人、职工人数SELECTshop_name,shop_address,shop_manager,shop_employeeFROMsupermarketGROUPBYshop_name;74案例应用｜业务检索（2）检索出各百货超市1-3月份的销售额度SELECTshop_name,sale_month,sale_moneyFROMsupermarketWHEREsale_month<=3GROUPBYshop_name,sale_month;75案例应用｜业务检索（3）检索出各百货超市1月-6月的销售总额SELECTshop_name,SUM(sale_money)AStotal_moneyFROMsupermarketGROUPBYshop_name;76数据检索排序数据检索操作要使用关键词“ORDERBY”关键词后面直接跟检索排序的数据项可以指定多个排序的数据项最前面为第一排序数据项依次为第二排序数据项、第三排序数据项指定的第一排序数据项为排序的依据第一数据项无法确定先后顺序，则依据第二数据项第二数据项无法确定先后顺序，则依据第三数据项77数据排序语法排序语法：SELECT检索字段FROM数据表WHERE数据条件ORDERBY排序数据项1,排序数据项2,…,排序数据项nASC/DESC单数据项排序SELECT*FROMSTUDENT_CLASSORDERBYEXAM_SCOREDESC;多数据项排序SELECT*FROMSTUDENTORDERBYCOLLEGE,MAJOR,CLASS,NAMEASC;78案例应用｜数据排序以本章百货超市（supermarket）数据表为例，实现以下相关业务查询检索需求。检索出各百货超市５月份的销售数据，并按销售金额降序排序。检索出全表所有数据信息，并按百货超市名称、营业地址、月销售额、销售月份四个数据项进行排序。79案例应用｜业务检索（1）检索出各百货超市５月份的销售数据，并按销售金额降序排序SELECT*FROMsupermarketWHEREsale_month=5ORDERBYsale_moneyDESC;80案例应用｜业务检索（2）检索出全表所有数据信息，并按百货超市名称、营业地址、月销售额、销售月份四个数据项进行排序SELECT*FROMsupermarketORDERBYshop_name,shop_address,sale_money,sale_month;81数据分页默认检索：查询检索默认情况返回所有符合条件的数据记录分页检索：当符合条件的数据集非常大的情况下，一次返回所有数据记录，不但会加重数据库服务器的负载，同时也会影响客户端工具的数据展现信息系统中一次返回全部数据会影响WEB前端页面的数据展现风格分页检索使用“LIMIT”关键词数据分页语法SQL分页语法1：SELECT检索字段FROM数据表LIMITn数据分页的关键词“LIMIT”必须放在所有其他关键词最后面关键词“LIMIT”后面只有一个分页参数n表示返回查询检索数据集中排列在最前面的n条数据记录SQL分页语法2：SELECT检索字段FROM数据表LIMITn1,n2关键词“LIMIT”后面有两个分页参数时第一个参数n1，表示从n1条记录后面开始检索数据第二个参数n2，表示一共检索n2条数据前后两个参数之间且英文状态下的逗号分隔开来案例应用｜数据分页以本章百货超市（supermarket）数据表为例，实现以下相关业务查询检索需求。检索出各百货超市全部销售数据，并做分页处理，返回最前面8条数据记录检索出各百货超市的1-5月份的全部销售数据，按月销售额降序排列，并做分页处理，返回第11-15条数据记录84案例应用｜业务检索（1）检索出各百货超市全部销售数据，并做分页处理，返回最前面8条数据记录SELECT*FROMsupermarketWHEREsale_month<=5ORDERBYsale_month_moneyDESCLIMIT10,5;85案例应用｜业务检索（2）检索出各百货超市的1-5月份的全部销售数据，按月销售额降序排列，并做分页处理，返回第11-15条数据记录SELECT*FROMsupermarketWHEREsale_month<=5ORDERBYsale_month_moneyDESCLIMIT10,5;86聚合函数（1）SUM函数：求和函数对检索数据集进行求和运算传入需要求和的数据项只对类型为数值的数据项有效函数应用格式：SUM(数据项)如：SUM(SALARY)对工资（SALARY）数据项求和如：SUM(MILE)对里程（MILE）数据项求和函数应用语句：SELECTUSER_NAME,SUM(ORDER_MONEY)FROMUSER_ORDERGROUPBYUSER_NAME;87聚合函数（2）AVG函数：求平均值函数对检索数据集进行求平均值运算传入需要求平均值的数据项只对类型为数值的数据项有效函数应用格式：AVG(数据项)如：AVG(SALARY)对工资（SALARY）数据项求平均值如：AVG(SCORE)对成绩（SCORE）数据项求平均值函数应用语句：SELECTDEPARTMENT,AVG(SALARY)FROMEMPLOYEEGROUPBYDEPARTMENT;88聚合函数（3）MAX函数：求最大值函数对检索数据集进行求最大值运算传入需要求最大值的数据项只对类型为数值的数据项有效函数应用格式：MAX(数据项)如：MAX(SALARY)对工资（SALARY）数据项求最大值如：MAX(SCORE)对成绩（SCORE）数据项求最大值函数应用语句：SELECTDEPARTMENT,MAX(SALARY)FROMEMPLOYEEGROUPBYDEPARTMENT;89聚合函数（4）MIN函数：求最小值函数对检索数据集进行求最小值运算传入需要求最小值的数据项只对类型为数值的数据项有效函数应用格式：MIN(数据项)如：MIN(SALARY)对工资（SALARY）数据项求最小值如：MIN(SCORE)对成绩（SCORE）数据项求最小值函数应用语句：SELECTDEPARTMENT,MIN(SALARY)FROMEMPLOYEEGROUPBYDEPARTMENT;90聚合函数（5）COUNT函数：对符合某种条件的数据集的数量作统计运算传入数据表中的某个数据项或符号“*”传入数据项时表示统计此项不为空记录的数量传入符号“*”时表示所统计出来记录数量包含空记录数据函数应用格式：COUNT(数据项)如：COUNT(SALARY)统计(SALARY)数据项不为空(NULL)的记录数如：COUNT(*)统计的记录数量包含空(NULL)记录函数应用语句：SELECTCOUNT(*)FROMUSER_EMALLWHEREMAIL_ID='CS2023@163.com';91聚合函数（6）NOW函数：获取当前时间函数使用NOW函数不需要传入任何参数时间格式包含年、月、日、时、分、秒信息函数应用格式：NOW()函数应用语句：SELECTNOW();92案例应用｜聚合函数以本章百货超市（supermarket）数据表为例，实现以下相关业务查询检索需求。统计出各百货超市第二季度的销售总额。统计出各百货超市上半年的最高月销售额。统计出各百货超市上半年的最低月销售额。统计出各百货超市上半年的月平均销售额。统计出业务数据表中各百货超市的业务记录数量。93案例应用｜业务检索（1）统计出各百货超市第二季度的销售总额SELECTshop_name,SUM(sale_money)AStotal_moneyFROMsupermarketWHEREsale_monthBETWEEN4AND6GROUPBYshop_name;94案例应用｜业务检索（2）统计出各百货超市上半年的最高月销售额SELECTshop_name,MAX(sale_money)ASmax_moneyFROMsupermarketGROUPBYshop_name;95案例应用｜业务检索（3）统计出各百货超市上半年的最低月销售额SELECTshop_name,MIN(sale_money)ASmin_moneyFROMsupermarketGROUPBYshop_name;96案例应用｜业务检索（4）统计出各百货超市上半年的月平均销售额SELECTshop_name,AVG(sale_money)ASavg_moneyFROMsupermarketGROUPBYshop_name;97案例应用｜业务检索（5）统计出业务数据表中各百货超市的业务记录数量SELECTshop_name,COUNT(*)FROMsupermarketGROUPBYshop_name;98多表连接针对业务数据分布在不同的数据表上数据查询检索时使用表连接的方式进行数据组合最关键的一环是多表连接条件的设置典型的应用场景是主外键的关联操作99双表连接双表连接语法：SELECT字段1,字段2,…,字段n,FROM数据表1,数据表2WHERE表1连接字=表2连接字段SQL语句1：SELECT*FROMBANK_ACCOUNT,BANK_USERWHEREBANK_ACCOUNT.USER_ID=BANK_USER.USER_ID;SQL语句2：SELECT*FROMBANK_ACCOUNTBA,BANK_USERBUWHEREBA.USER_ID=BU.USER_ID;100三表以上连接多表连接语法：SELECT字段1,字段2,…,字段n,FROM数据表1,…,数据表nWHERE表p连接字=表q连接字段AND…AND表y连接字段=表z连接字段SQL语句：SELECT*FROMTAB1T1,TAB2T2,TAB3T3,TAB4T4WHERET1.UID=T2.UIDANDT2.UID=T3.UIDANDT3.UID=T4.UID;101案例应用｜多表连接数据库环境中有三张数据表：学生表（student）院系表（college）获奖表（prize）存储了学生参加各项竞赛的相关信息获奖表与学生表之间通过学号(sn)字段进行主外键关联院系表与学生表之间通过专业标识(major_id)字段进行业务关联102数据表结构及基础数据学生表：student字段1：学号（sn）VARCHAR(45)PrimaryKey字段2：姓名（name）VARCHAR(45)字段3：专业标识（major_id）VARCHAR(45)院系表：college字段1：院系标识（id）VARCHAR(45)PrimaryKey字段2：院系名称（college_name）VARCHAR(45)字段3：专业标识（major_id）VARCHAR(45)字段4：专业名称（major_name）VARCHAR(45)获奖表：prize字段1：主键标识（id）INTPrimaryKey字段2：学号（sn）VARCHAR(45)字段3：获奖项目（prize_name）VARCHAR(45)字段4：获奖等级（rank）VARCHAR(45)103SQL数据库脚本DROPTABLEIFEXISTScollege;CREATETABLEcollege(idvarchar(45)NOTNULL,college_namevarchar(45)NOTNULL,major_idvarchar(45)NOTNULL,major_namevarchar(45)NOTNULL,PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8;INSERTINTOcollege(id,college_name,major_id,major_name)VALUES('1001','计算机学院','s_0001','应用电子'),('1002','教育学院','t_0002','语文教育'),('1003','工商管理学院','x_0005','应用会计'),('1004','计算机学院','v_0003','网络技术'),('1005','教育学院','u_0004','英语教育');DROPTABLEIFEXISTSprize;CREATETABLEprize(idint(10)unsignedNOTNULLauto_increment,snvarchar(45)NOTNULL,prize_namevarchar(45)NOTNULL,rankvarchar(45)NOTNULL,PRIMARYKEY(id))ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8;INSERTINTOprize(id,sn,prize_name,rank)VALUES(1,'201600000001','数学建模大赛','二等奖'),(2,'201600000003','软件设计大赛','一等奖'),(3,'201600000004','挑战杯网络攻防大赛','三等奖'),(4,'201600000006','百花争鸣文学大赛','二等奖'),(5,'201600000009','高校杯国防安全知识大赛','三等奖'),(6,'201600000010','新时空创业大赛','一等奖'),(7,'201600000011','唐宋诗词大赛','三等奖'),(8,'201600000012','大学生创意大赛','二等奖'),(9,'201600000014','全民健身运动大赛','三等奖'),(10,'201600000015','全国英语专业技能大赛','三等奖');DROPTABLEIFEXISTSstudent;CREATETABLEstudent(snvarchar(45)NOTNULL,namevarchar(45)NOTNULL,major_idvarchar(45)NOTNULL,PRIMARYKEY(sn))ENGINE=InnoDBDEFAULTCHARSET=utf8;INSERTINTOstudent(sn,name,major_id)VALUES('201600000001','张小拴','s_0001'),('201600000002','孙明活','t_0002'),('201600000003','陈京楚','v_0003'),('201600000004','王意昌','s_0001'),('201600000005','刘太展','u_0004'),('201600000006','何原凡','t_0002'),('201600000007','王彬华','s_0001'),('201600000008','伍鲸念','s_0001'),('201600000009','陆顺引','x_0005'),('201600000010','吴香桐','t_0002'),('201600000011','钟钟英','t_0002'),('201600000012','温季凤','v_0003'),('201600000013','胡金称','s_0001'),('201600000014','黄蕾莘','v_0003'),('201600000015','苗千英','u_0004');104案例应用｜业务检索（1）检索出各项获奖的学生信息SELECTs.sn,,p.prize_name,p.rankFROMstudents,prizepWHEREs.sn=p.sn;105案例应用｜业务检索（２）检索出所有学生的专业、院系信息SELECTs.sn,,c.major_name,c.college_nameFROMstudents,collegecWHEREs.major_id=c.major_id;106案例应用｜业务检索（３）检索出各学院的获奖学生信息SELECTs.sn,,c.major_name,c.college_name,p.prize_name,p.rankFROMstudents,collegec,prizepWHEREs.major_id=c.major_idANDs.sn=p.snORDERBYc.college_name,c.major_name;107子查询一种进行查询语句嵌套SQL语句类型为实现更加灵活、强大的数据检索功能子查询包含内层查询与外层查询适用于复杂的数据检索场景下使用子查询从类型上来说可分为两种，WHERE类型子查询FROM类型子查询108WHERE类型子查询把内层查询结果作为外层查询的数据筛选条件以条件值的形式参与外层查询语句格式：SELECT检索字段FROM数据表WHERE数据项条件比较操作(SELECT单字段FROM数据表WHERE数据检索条件)SQL语句：SELECT*FROMSTUDENT_COURSEWHEREMATH_SCORE>(SELECTAVG(CHINESE_SCORE)FROMSTUDENT_COURSEWHERECOURSE='MATH');109FROM类型子查询把内层子查询的结果作为一个虚表外层查询再从这个虚表中检索相关数据虚表与外层查询主表或其它数据表作连接语句格式：SELECT检索字段FROM(SELECT检索字段FROM数据表)AS子查询别名SQL语句：110SELECT*FROM(SELECT*FROMSTUDENT_COURSEWHEREGRADE='六年级')ASSONWHERESON.MATH_SCORE>=60;案例应用｜子查询在一个汽车俱乐部会不定期吸收各种车辆入会，现要有一张数据表专门记录入会车辆和相关信息，以方便日后对会员车辆的管理。111数据表结构及基础数据汽车俱乐部表：car_club字段1：车辆编号（car_no）VARCHAR(45)PrimaryKey字段2：车辆品牌（car_brand）VARCHAR(45)字段3：入会日期（enter_date）DATE字段4：车辆价格（car_price）INT字段5：出厂日期（product_date）DATE字段6：行驶里程（drive_mile）INT112SQL数据库脚本DROPTABLEIFEXISTScar_club;CREATETABLEcar_club(car_novarchar(45)NOTNULL,car_brandvarchar(45)NOTNULL,enter_datedateNOTNULL,car_priceint(10)unsignedNOTNULL,product_datedateNOTNULL,drive_mileint(10)unsignedNOTNULL,PRIMARYKEY(car_no))ENGINE=InnoDBDEFAULTCHARSET=utf8;INSERTINTOcar_club(car_no,car_brand,enter_date,car_price,product_date,drive_mile)VALUES('C101','Hondal','2021-06-05',150000,'2020-05-01',100000),('C102','BMW','2022-08-05',500000,'2020-05-01',60000),('C103','BenZ','2021-05-10',300000,'2020-05-01',110000),('C104','Hondal','2022-04-12',200000,'2020-05-01',50000),('C105','BMW','2023-02-02',400000,'2020-05-01',4000),('C106','BenZ','2023-01-05',420000,'2020-05-01',5000),('C107','BMW','2022-02-03',450000,'2020-05-01',55000),('C108','Hondal','2021-10-04',180000,'2020-05-01',90000),('C109','BenZ','2022-07-01',290000,'2020-05-01',30000),('C110','Hondal','2021-09-18',220000,'2020-05-01',85000),('C111','BenZ','2022-04-01',380000,'2020-05-01',40000),('C112','BMW','2023-02-01',600000,'2020-05-01',10000),('C113','BenZ','2022-03-15',350000,'2020-05-01',70000),('C114','Hondal','2022-06-26',140000,'2020-05-01',20000),('C115','BMW','2023-02-20',550000,'2020-05-01',9000);113案例应用｜业务检索（1）用WHERE子查询检索出车辆里程数在所有车辆平均里程数之下的相关车辆信息SELECT*FROMCAR_CLUBWHEREDRIVE_MILE<(SELECTAVG(DRIVE_MILE)FROMCAR_CLUB);114案例应用｜业务检索（2）用FROM子查询检索出价格在36万以下，品牌为“BenZ”的相关车辆信息SQL语句：SELECT*FROM(SELECT*FROMCAR_CLUBWHERECAR_BRAND='BenZ')ASSWHERES.CAR_PRICE<360000;115案例应用｜业务检索（3）用FROM子查询检索出各品牌中，车辆价格在品牌内平均价格之上的相关车辆信息SQL语句：SELECT*FROMCAR_CLUBCC,(SELECTCAR_BRAND,AVG(CAR_PRICE)ASAVG_PRICEFROMCAR_CLUBGROUPBYCAR_BRAND)ASSWHERECC.CAR_BRAND=S.CAR_BRANDANDCC.CAR_PRICE>S.AVG_PRICE;116MySQL数据库应用与开发技术第4章数据增删改操作本单元教学目标

认识数据写操作的类型了解TRUNCATE操作掌握数据表中插入数据语法掌握数据表中更新数据语法掌握数据表中删除数据语法数据插入操作把数据库应用管理系统外的信息数据按照相关格式与规范写入到数据表中可以往数据表中插入一行或若干行数据，可以对数据表中所有字段插入数据也可以只对数据表中的部分字段插入数据未插入数据字段的相应值置为空数据插入操作要使用关键词INSERTINTOVALUES数据插入操作是SQL语句四种基本操作类型之一属于SQL操作中的写操作类型写操作会影响、改变数据库环境中的数据信息数据插入语法语法格式：INSERTINTO数据表(字段1,字段2,…,字段n)VALUES(插入值1,插入值2,…,插入值n)部分表字段插入数据INSERTINTOSTUDENT(SN,STUDENT_NAME,STUDENT_MAJOR,STUDENT_CLASS,ENTER_YEAR)VALUES('20000123','张小明','信息技术',3,2022);全表字段插入数据INSERTINTOSTUDENTVALUES('20000123','张小明','信息技术',3,2022,'刘志军',560,'是');应用案例｜数据插入在数据库环境中有一张图书信息数据表，表中存储了图书的基础数据信息：书名、出版日期、出版社、ISBN号等，以及基础业务信息：入库时间、借出时间等。图书信息表｜book字段1：主键标识（book_id）INTPrimaryKey字段2：图书名称（book_name）VARCHAR(45)非空字段3：出版日期（publish_date）VARCHAR(45)非空默认值：2023年1月1日字段4：出版社（publish_factory）VARCHAR(45)非空字段5：ISBN号（book_isbn）VARCHAR(45)非空字段6：图书价格（book_price）FLOAT非空字段7：存放区域（book_area）VARCHAR(45)字段8：入库时间（enter_time）TIMESTAMP非空默认值：当前时间字段9：借出时间（borrow_time）DATETIMESQL建表脚本DROPTABLEIFEXISTSbook;CREATETABLEbook(book_idintNOTNULLAUTO_INCREMENT,book_namevarchar(45)NOTNULL,publish_datedateNOTNULLDEFAULT'2023-01-01',publish_factoryvarchar(45)NOTNULL,book_isbnvarchar(45)NOTNULL,book_pricefloatNOTNULL,book_areavarchar(45),enter_timetimestampNOTNULLDEFAULTnow(),borrow_timedatetime,PRIMARYKEY(book_id))ENGINE=InnoDBDEFAULTCHARSET=utf8;案例应用｜业务操作（1）请按下表的初始图书数据清单，把相关业务数据插入到图书信息表中。案例应用｜业务操作（1）SQL语句1：插入《UML建模技术》图书数据INSERTINTObook(book_id,book_name,publish_date,publish_factory,book_isbn,book_price,book_area,enter_time,borrow_time)VALUES(1,'UML建模技术','2022-06-08','清华大学出版社','7125693245612',35,'F3','2023-03-0214:38:10','2023-03-0511:45:30');SQL语句2：插入《软件框架开发技术》图书数据INSERTINTObook(book_id,book_name,publish_date,publish_factory,book_isbn,book_price,book_area,enter_time,borrow_time)VALUES(2,'软件框架开发技术','2022-08-09','北京大学出版社','5896593245626',32,'A2','2023-03-0214:38:10','2023-03-0613:35:30');SQL语句3：插入《Java程序设计》图书数据INSERTINTObook(book_id,book_name,publish_date,publish_factory,book_isbn,book_price,book_area,enter_time,borrow_time)VALUES(3,'Java程序设计','2023-01-08','电子工业出版社','5635693245635',30,'D4','2023-03-0214:38:10','2023-03-0815:22:30');SQL语句4：插入《Web应用与开发技术》图书数据INSERTINTObook(book_id,book_name,publish_date,publish_factory,book_isbn,book_price,book_area,enter_time,borrow_time)VALUES(4,'Web应用与开发技术','2021-11-04','人民邮电出版社','4259569324483',45,'C3','2023-03-0214:38:10','2023-03-0916:15:30');SQL语句5：插入《数据库入门原理教程》图书数据INSERTINTObook(book_id,book_name,publish_date,publish_factory,book_isbn,book_price,book_area,enter_time,borrow_time)VALUES(5,'数据库入门原理教程','2022-10-18','机械工业出版社','5895693245469',40,'E1','2023-03-0214:38:10','2023-03-1017:07:30');案例应用｜业务操作（2）请按下表的新购入图书数据清单，把相关业务数据插入到图书信息表中案例应用｜业务操作（2）SQL语句1：插入《系统分析与设计》图书数据INSERTINTObook(book_name,publish_factory,book_isbn,book_price)VALUES('系统分析与设计','中山大学出版社','8596693245632',38);SQL语句2：插入《软件工程概述》图书数据INSERTINTObook(book_name,publish_factory,book_isbn,book_price)VALUES('软件工程概述','吉林大学