数据库原理、技术与应用-MySQL(视频教学+题库+AI赋能版) 课件 第1-4章-数据库技术概论 -使用SQL进行数据库操作

数据库原理、技术与应用——MySQL（视频教学+题库+AI赋能版)

第1章数据库技术概论1.4关系数据库基础知识1.3数据模型数据库技术的产生与发展1.2数据库系统1.5关系的规范化理论CONTENTS目录

1.11.1数据库技术的产生与发展1.1.1数据与数据处理数据（Data）：对客观事物特征及联系的抽象化、符号化表示信息（Information）：经过加工处理并对决策有影响的数据数据处理：将数据转换成信息的过程数据管理：数据的收集、组织、存储、检索和维护（数据处理的中心环节）1.1.2数据管理技术的发展①人工管理阶段（50年代中期以前）：数据不保存、无共享、无独立性②文件管理阶段（50~60年代）：数据可长期保存，但共享性差、冗余度大③数据库管理阶段（60年代后期以后）：统一管理，数据共享、低冗余、高独立性三个阶段反映了数据管理技术从低级到高级的发展过程1.2数据库系统1.2.1数据库系统的组成①硬件：主机、存储设备、I/O设备、网络环境②软件：操作系统、DBMS、数据库应用系统③数据库（DB）：按一定方式组织、可共享的数据集合④人员：最终用户、应用开发人员、数据库管理员（DBA）1.2.2三级模式结构与系统特点三级模式：概念模式（全局逻辑结构）、外模式（用户视图）、内模式（物理存储）二级映射：概念模式/内模式映射（保证物理独立性）外模式/概念模式映射（保证逻辑独立性）系统特点：数据结构化、共享性高冗余度低、较高的数据独立性、统一的数据控制1.3数据模型1.3.3概念模型——E-R模型实体（Entity）：可相互区分的客观事物（如教师、学生）属性（Attribute）：实体的特征（如姓名、性别、职称）实体间联系：一对一（1:1）、一对多（1:n）、多对多（m:n）E-R图：矩形表示实体、菱形表示联系、椭圆表示属性1.3.4逻辑模型①层次模型：树形结构，每个结点只有一个父结点②网状模型：有向图结构，可表示多对多联系③关系模型：二维表格表示实体及联系，结构简单、有严格数学基础关系模型是目前最流行的数据模型，MySQL即采用关系模型1.4关系数据库基础1.4.1~1.4.2关系数据库基础与关系运算基本概念：关系（表）、元组（行）、属性（列）、关键字（主键）、外键传统关系运算（集合运算）：并、差、交、笛卡尔积专门关系运算：选择、投影、连接1.4.3关系的完整性约束①实体完整性：主属性不能取空值，不允许两个元组关键字值相同②参照完整性：外键取值必须取空值或等于被参照关系的主键值③用户定义完整性：针对具体应用的数据约束（如性别只能取'男'或'女'）1.5关系的规范化理论1.5.1不好的关系模式——数据冗余与操作异常【例】商品供应关系模式：商品供应（供应商名称，供应商地址，联系人，商品名称，订货数量，单价）该模式中，一个供应商供应多种商品，同一商品可由多个供应商供应（多对多联系）问题：供应商名称、地址、联系人对每种商品都要重复输入，数据冗余大1.5.1操作异常问题（1）更新异常：供应商地址在多个元组中重复，更新时必须修改所有元组，否则数据不一致（2）插入异常：新发展了供应商但尚未订货时，无法插入该供应商信息（主关键字不能为空）（3）删除异常：删除过期订货记录时，可能把供应商的全部信息一并删除结论：这是一个不好的关系模式，需要通过模式分解来消除上述问题1.5.1模式分解示例将商品供应关系模式分解为两个关系模式：供应商（供应商名称，供应商地址，联系人）供应（供应商名称，商品名称，订货数量，单价）分解后：每个供应商信息只存储一次，消除数据冗余改变地址只需修改一个元组，消除更新异常新供应商信息可直接插入供应商表，消除插入异常删除订货记录不影响供应商信息，消除删除异常1.5.2函数依赖的基本概念定义1（函数依赖）：设R(U)，X、Y是U的子集，若X值相等则Y值必相等，记为X->Y定义2（完全/部分函数依赖）：若X->Y且X的任意真子集都不决定Y，称完全依赖；否则为部分依赖例：学生R(学号，姓名，出生年月，班号，班长姓名，课程号，成绩)（学号，课程号）->成绩是完全依赖；（学号，班号，课程号）->成绩是部分依赖定义3（传递函数依赖）：若X->Y（Y->X不成立），Y->Z，则Z传递函数依赖于X例：学号->班号，班号->班长姓名，则班长姓名传递依赖于学号1.5.3第1范式（1NF）定义6：当关系模式R的所有属性都不能分解为更基本的数据元素时，即所有属性均满足原子特征，称R满足1NF【例】员工关系模式R(员工号，姓名，工资)，其中工资由基本工资和岗位工资组成不满足1NF，因为工资属性可再分解分解为R_NEW(员工号，姓名，基本工资，岗位工资)，满足1NF1NF是关系模式规范化的最低要求满足1NF仍可能存在插入、删除、修改异常，需满足更高范式1.5.3第2范式（2NF）定义7：若R满足1NF，且所有非主属性都完全函数依赖于每一个候选关键字，称R满足2NF【例】借书关系模式R(读者编号，工作单位，图书编号，借阅日期，归还日期)候选关键字：（读者编号，图书编号）工作单位只依赖于读者编号（候选关键字的子集），部分函数依赖，不满足2NF问题：读者调动工作单位时需修改多条借书记录，产生更新异常分解：R1(读者编号，工作单位)+R2(读者编号，图书编号，借阅日期，归还日期)1.5.3第3范式（3NF）定义8：若R满足1NF，且所有非主属性都不传递函数依赖于每一个候选关键字，称R满足3NF【例】公司关系模式R(公司注册号，法人代表，注册城市，所在省)候选关键字：公司注册号（单属性，不存在部分依赖，满足2NF）但：公司注册号->注册城市，注册城市->所在省所以：公司注册号->所在省（传递函数依赖），不满足3NF分解：R1(公司注册号，法人代表，注册城市)+R2(注册城市，所在省)定理：满足3NF的关系一定满足2NF1.5.3BCNF范式定义9：若R满足1NF，且R的所有属性（含主属性）都不传递函数依赖于每一个候选关键字，称R满足BCNFBCNF是比3NF更强的规范：满足BCNF一定满足3NF，但反之不一定【例】R(书号，书名，作者名)，约定：每个书号只有一个书名，不同书号可有相同书名函数依赖：书号->书名，（书名，作者名）->书号候选关键字：（书号，作者名）和（书名，作者名）所有属性都是主属性，满足3NF但书名传递依赖于（书名，作者名），不满足BCNF1.5.4关系模式的分解——示例【例】员工奖金分配表R(员工号，姓名，部门，月份，月度奖)候选关键字：（员工号，月份），由两个属性组成姓名、部门只依赖于员工号，部分函数依赖，不满足2NF分解方法：R1(员工号，月份，月度奖)PrimaryKey(员工号，月份)R2(员工号，姓名，部门)PrimaryKey(员工号)分解后R1、R2均满足BCNF和3NF，且分解是无损的（可恢复原关系）1.5.43NF分解方法总结Heath定理：若R(A,B,C)中A->B且A->C，则R与投影(A,B)、(A,C)的连接等价（无损分解）分解步骤：（1）不满足1NF：将复合属性分解为基本属性（2）不满足2NF：消除非主属性对候选关键字的部分函数依赖设K=(K1,K2)，K1->X，则分解为：R1(K1,K2,X2)+R2(K1,X1)（3）不满足3NF：消除非主属性对候选关键字的传递函数依赖1.5.43NF分解方法总结（续）设K->X1，X1->X2，则分解为：R1(K,X1)+R2(X1,X2)关键原则：将候选关键字分解到每个子关系中，保证无损分解1.6数据库设计1.6.1数据库设计的6个阶段①需求分析：调查用户要求，明确系统功能，画出数据流图，建立数据字典②概念设计：将需求抽象为概念模型（E-R模型），是整个设计的关键③逻辑设计：将概念模型（E-R图）转换为逻辑模型（关系模式），进行规范化处理④物理设计：确定存储结构和存取方法，评价时间和空间效率⑤数据库实施：用DDL定义数据库结构，组织数据入库，编码调试应用程序⑥运行和维护：数据库转储与恢复、安全性与完整性控制、性能改造、重组织与重构造1.6.2E-R模型转化——1:1联系转化规则：在两个实体转化的关系模式中，任一个增加另一方的关键属性和联系的属性【例】校长与学校（1:1联系）E-R图：校长1:1学校转化为两个关系模式：校长（校长姓名，性别，出生日期，职称，任职年月，学校名称）学校（学校名称，所在地，网址）说明：在校长关系中增加学校关系的关键属性'学校名称'作为外键1.6.2E-R模型转化——1:n联系转化规则：在n方实体的关系模式中增加1方实体的关键属性和联系的属性【例】仓库与产品（1:n联系）E-R图：仓库1:n产品转化为两个关系模式：仓库（仓库号，地点，面积）产品（产品号，产品名称，价格，数量，仓库号）说明：在产品关系（n方）中增加仓库关系（1方）的关键属性“仓库号”作为外键，并增加联系属性“数量”。1.6.2E-R模型转化——m:n联系转化规则：除对两个实体分别转化外，还要为联系单独建立一个关系模式，其属性为两方实体的关键属性加上联系属性，关键属性是两方关键属性的组合【例】供应商与货物（m:n联系）转化为三个关系模式：供应商（供应商号，供应商名，电话，地址）货物（货物代码，货物名称，型号，库存量）采购（供应商号，货物代码，数量）——独立关系模式说明：采购关系的关键字为（供应商号，货物代码）的组合1.6.3数据库设计实例——大学教学管理系统需求描述：对学生选课、教师授课等教学活动进行管理规定：每名学生可同时选修多门课程，每门课程可由多位教师讲授每位教师可讲授多门课程，各学院对教师实行聘任，学生属于某一专业5个实体：学生（学号，姓名，性别，出生年月）课程（课程编号，课程名称，课程类别，学分）1.6.3数据库设计实例——大学教学管理系统（续）教师（教师号，姓名，性别，职称）专业（专业名称，成立年份，专业简介）学院（学院名称，网址，教师人数）1.6.3设计实例——E-R图与联系分析4个实体间联系：①学生—课程：多对多（m:n）②专业—学生：一对多（1:n）③教师—课程：多对多（m:n）④学院—教师：一对多（1:n）E-R图绘制：5个实体（矩形）+4个联系（菱形）+各自属性（椭圆）1.6.3设计实例——E-R图与联系分析（续）根据E-R图，将5个实体和2个m:n联系转化为7个关系模式1.6.3设计实例——关系模式转化结果7个关系模式：①学生(学号，姓名，性别，出生年月，专业名称)含专业名称外键②课程(课程编号，课程名称，课程类别，学分)③选课(学号，课程编号，成绩)m:n联系转化的独立关系④教师(教师号，姓名，性别，职称，学院名称，聘任时间)含学院名称外键⑤授课(教师号，课程编号，上课教室)m:n联系转化的独立关系1.6.3设计实例——关系模式转化结果（续）⑥学院(学院名称，网址，教师人数)⑦专业(专业名称，成立年份，专业简介)转化要点：1:n联系在n方加入1方主键；m:n联系建立独立关系模式本章小结数据库系统由硬件、软件、数据库和人员组成，采用三级模式结构保证数据独立性数据模型三要素：数据结构、数据操作、完整性约束；常用E-R图建立概念模型关系运算包括传统集合运算和专门运算（选择、投影、连接）规范化理论：通过模式分解消除数据冗余和操作异常（1NF->2NF->3NF->BCNF）数据库设计6个阶段：需求分析->概念设计->逻辑设计->物理设计->实施->运行维护E-R模型转化：1:1和1:n联系在n方加外键，m:n联系建立独立关系模式第2章MySQL数据库基础2.4SQL概述2.3MySQL管理工具MySQL数据库简介2.2MySQL服务器的安装与配置CONTENTS目录

2.12.1MySQL数据库简介2.1.1MySQL的特点MySQL是一种开源的关系型数据库管理系统，由瑞典MySQLAB公司开发现在属于Oracle公司，广泛应用于Web应用和企业级应用主要特点：①开源免费（社区版），体积小、读写速度快、使用方便②跨平台支持Linux、Windows、MacOS、Solaris等操作系统2.1.1MySQL的特点（续）③支持多种编程语言API（C、C++、Python、Java、PHP等）④支持多线程，充分利用CPU资源⑤优化的SQL查询算法，有效提高查询速度⑥提供多语言支持（GB2312、BIG5、UTF8等）⑦提供TCP/IP、ODBC和JDBC等多种数据库连接方式⑧可以处理拥有上千万条记录的大型数据库⑨支持多种存储引擎（InnoDB、MyISAM等）2.1.2MySQL8.0的新特性1.性能优化：MySQL8.0性能峰值相对于5.7提高了近两倍2.默认字符集：从latin1改为utf8mb4，支持完整的UTF-8编码3.DDL的原子化：DDL操作支持事务完整性，要么成功要么回滚4.计算列：列的值可以通过其它列计算得到5.JSON功能增强：新增->>运算符、JSON_ARRAYAGG()、JSON_OBJECTAGG()等函数2.1.2MySQL8.0的新特性（续）6.窗口函数：类似分组但不聚合结果，将结果置于每一条数据记录中7.公用表表达式（CTE）：命名的临时结果集，可以理解为可复用的子查询8.支持降序索引：InnoDB存储引擎真正支持降序索引（8.0开始）9.隐藏索引：将待删除的索引设置为隐藏，观察对性能的影响后再决定是否删除，隐藏索引不会被查询优化器使用，方便安全地测试索引删除的影响2.2MySQL服务器的安装与配置2.2.1安装MySQL服务器安装步骤：（1）访问/downloads/mysql/下载安装程序（2）选择DeveloperDefault选项，安装开发者常用功能组件（3）检查安装所需组件，单击Next开始安装（4）组件安装完成后，准备安装数据库系统2.2.1安装MySQL服务器（续）（5）选择配置类型为DevelopmentComputer（占用内存较少）（6）选择网络协议为TCP/IP，端口默认为3306（7）选择强密码认证方式（推荐第一种）（8）设置root用户密码（要满足安全规则）（9）将数据库服务器配置为Windows服务，开机自动启动（10）单击Next完成安装与初始化2.2.2配置MySQL服务器Windows系统下配置文件：C:\ProgramData\MySQL\MySQLServer8.0\my.ini主要配置项：①port=3306：MySQL服务器默认监听端口②basedir：MySQL安装根目录路径③datadir：MySQL存放数据文件的目录2.2.2配置MySQL服务器（续）④character_set_server=utf8mb4：服务端默认编码（8.0默认）⑤default_authentication_plugin=caching_sha2_password：默认认证插件⑥default-storage-engine=INNODB：默认存储引擎⑦sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"：SQL模式⑧max_connections=300：允许同时连接的客户端数量注意：修改配置文件后需重启MySQL服务生效2.2.3启动MySQL数据库服务方法一：通过服务管理工具启动桌面此电脑右键→管理→服务→找到MySQL80→右键启动，可设置启动类型为自动，实现开机自动启动方法二：通过命令行启动启动：netstartMySQL80停止：netstopMySQL80MySQL80为MySQL在Windows服务中的名称2.2.4登录MySQL数据库当MySQL服务启动完成后，可通过客户端登录数据库登录命令格式：mysql-hhostname-uusername-ppassword参数说明：-h：服务器名称（localhost或表示本机）-u：登录用户名（root为超级用户）-p：用户密码（紧跟在-p后面，不要加空格）2.2.4登录MySQL数据库（示例）【例】登录到服务器00，用户名为root，密码为test123456命令：mysql-h00-uroot-ptest123456注意：①-p和密码之间不要加空格②为避免每次登录都输入完整路径，可将MySQL的bin目录添加到PATH环境变量③登录成功后，出现mysql>提示符，表示已进入MySQL交互环境2.3MySQL管理工具2.3.1workbench介绍MySQLWorkbench是官方提供的图形化管理工具，主要功能：①数据库设计与建模：可视化设计ER图，支持正向和逆向工程②SQL开发：提供颜色语法高亮、SQL片段复用、执行历史③数据库管理：管理用户、查看数据库运行状态、配置服务器④数据迁移：支持从SQLServer、PostgreSQL等迁移到MySQL2.3.2workbench使用——创建连接（1）启动MySQLWorkbench，单击MySQLConnections后面的加号（2）设置连接参数：①ConnectionName：连接名称（用于标识这个连接）②ConnectionMethod：连接方法（默认TCP/IP）③Hostname：服务器IP地址（表示本机）④Port：端口号（默认3306）

⑤Username：用户名（root为超级用户）2.3.2workbench使用——用户管理修改用户密码和登录主机：单击UsersandPrivileges→选择用户→修改密码LimittoHostsMatching：设置用户可以从哪些主机登录localhost表示只能从本机登录；%表示可以从任何主机登录添加新用户：单击AddAccount按钮→输入用户名和密码→设置权限2.3.2workbench使用——权限管理授予用户角色和权限：DBA角色：数据库管理员，拥有所有权限常用权限：ALTER：修改表结构CREATE：创建数据库/表DELETE：删除数据DROP：删除数据库/表SELECT：查询数据INSERT：插入数据UPDATE：更新数据CREATEUSER：创建用户Schema权限设置：设置用户可以访问哪些数据库2.4SQL概述2.4.1SQL语言的发展与特点SQL（StructuredQueryLanguage）：结构化查询语言1970年代由IBM公司开发，应用于DB2关系数据库系统1986年10月，ANSI批准SQL作为关系数据库语言的美国标准目前流行的关系数据库（Oracle、SQLServer、MySQL等）都采用SQL标准SQL语言特点：功能丰富、使用灵活、语言简洁易学2.4.1SQL语言的分类按照实现的功能，SQL划分为4类：（1）数据查询语言（DQL）：检索符合条件的数据（SELECT）（2）数据定义语言（DDL）：定义数据的逻辑结构（CREATE、ALTER、DROP）（3）数据操纵语言（DML）：更改数据库数据（INSERT、UPDATE、DELETE）（4）数据控制语言（DCL）：控制对数据的操作（GRANT、REVOKE）SQL是一种数据库子语言，不是完整的程序设计语言（无流程控制语句）2.4.2MySQL对SQL语言的扩展MySQL在ANSISQL92标准基础上进行了扩展，主要包含3个方面：（1）增加了流程控制语句：块语句（BEGIN...END）、分支判断（IF...THEN...ELSE）循环语句（WHILE、LOOP、REPEAT）使得可以编写存储过程、函数、触发器等程序2.4.2MySQL对SQL语言的扩展（续）（2）加入了局部变量、全局变量等新概念可以写出更复杂的查询语句（3）增加了新的数据类型（如JSON类型）数据处理能力更强本章小结MySQL是一种开源的关系型数据库管理系统，现在属于Oracle公司MySQL8.0新特性：默认字符集utf8mb4、DDL原子化、窗口函数、CTE、隐藏索引等MySQL服务器的安装与配置：端口3306、配置文件my.ini、Windows服务管理MySQLWorkbench是官方图形化管理工具，用于数据库设计、SQL开发、用户权限管理SQL是结构化查询语言，分为DQL、DDL、DML、DCL四类MySQL对SQL进行了扩展：增加流程控制语句、变量、新的数据类型第3章数据库和表3.4设计数据表3.3管理数据库数据库介绍3.2创建数据库3.5表的创建与维护CONTENTS目录

3.13.1数据库介绍3.1.1MySQL的数据库类型MySQL服务器安装成功后，就创建了一个实例一个实例包含1个系统数据库和若干个用户自定义数据库1.系统数据库：由MySQL系统创建和维护，名为mysql记录系统配置、任务情况和用户数据库等管理信息，控制MySQL和用户数据库的运行3.1.1MySQL的数据库类型（续）2.用户数据库：包括系统提供的示例数据库和用户创建的数据库（1）系统提供sakila和world数据库，供测试之用（2）用户根据实际需求自行创建的数据库MySQL实例的组成：MySQL实例=系统数据库(mysql)+用户数据库(sakila,world,自定义)3.1.2数据库文件MySQL中所有数据、对象和事务日志以文件形式保存在磁盘上文件分为两类：数据文件和事务日志文件1.数据文件（在data目录中，每张表创建三个文件）：.frm文件：存储表结构定义.MYD文件：存储表数据.MYI文件：存储表索引3.1.2数据库文件（续）2.事务日志文件（Transactionlogfile）：记录对数据库的INSERT、ALTER、DELETE、UPDATE等操作必要时可利用日志文件进行数据库恢复MySQL的日志文件类型：错误日志、查询日志、慢查询日志、二进制日志等3.2创建数据库3.2.1使用workbench创建数据库创建步骤：（1）单击Schemas选项卡，单击鼠标右键（2）选择CreateSchema菜单（3）输入数据库名称（如student），选择字符集utf8mb4（4）单击Apply按钮，显示创建数据库的SQL语句（5）再次单击Apply，创建数据库成功3.2.2使用SQL语句创建数据库语法格式：CREATEDATABASE数据库名;示例：CREATEDATABASEstudent;数据库名称命名规则：（1）不能与其他数据库重名（2）由字母、数字、下划线(_)和$组成，不能以单独数字开头（3）名称最长可为64个字符（4）不能使用MySQL关键字作为数据库名（5）建议使用小写定义数据库名（便于跨平台移植）3.3管理数据库3.3.1查看数据库方法一：在Schemas选项卡中查看当前数据库系统中的数据库方法二：在查询窗口中执行SHOWDATABASES命令SHOWDATABASES;可查看当前数据库系统中已创建的所有数据库3.3.2删除数据库方法一：使用workbench在要删除的数据库上单击右键→选择DropSchema→单击DropNow方法二：使用SQL语句语法：DROPDATABASE数据库名;示例：DROPDATABASEstudent;注意：当前正在使用的数据库不能删除，MySQL系统数据库无法删除3.4设计数据表3.4.1字符串类型MySQL字符串类型：CHAR、VARCHAR、BLOB、TEXT、ENUM、SET1.CHAR和VARCHAR类型：CHAR(n)：定长字符串，长度1~255，存储时右侧用空格填补VARCHAR(n)：变长字符串，长度1~255，只存储所需字符+1字节记录长度检索时CHAR尾部空格被删除，VARCHAR保留注意：同一表中不建议混用CHAR和VARCHAR3.4.1字符串类型（续）2.BLOB和TEXT类型：BLOB：二进制大对象，可保存图片等内容分为TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOBTEXT：文本类型，分为TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXTBLOB和TEXT不能有DEFAULT值应定期运行OPTIMIZETABLE进行优化减少碎片3.4.1字符串类型——ENUM和SET3.ENUM类型（枚举）：语法：col_nameENUM('值1','值2','值3','值4')只能从枚举列表中取一个值，按索引排序允许NULL时NULL为默认值，否则第一个元素为默认值4.SET类型（集合）：语法：col_nameSET('值1','值2','值3','值4')最多64个成员，可从定义的列值中选择多个，成员间用逗号隔开3.4.2日期和时间类型MySQL日期时间类型：（1）DATE：日期，格式YYYY-MM-DD，范围1000-01-01~9999-12-31，3字节（2）DATETIME：日期+时间，格式YYYY-MM-DDHH:MM:SS，8字节（3）TIMESTAMP：时间戳，4字节，可自动记录INSERT/UPDATE操作时间（4）TIME：时间，格式HH:MM:SS，范围-838:59:59~838:59:59（5）YEAR：年份，1字节，范围1901~2155（4位格式）3.4.2TIMESTAMP类型示例【例】创建表时使用TIMESTAMP自动记录时间：CREATETABLEmy_test(idINTprimarykey,tsTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP);插入数据时，ts自动取当前时间；更新记录时，ts自动更新3.4.3数字类型1.整数类型：TINYINT：1字节，范围-128~127（无符号0~255）SMALLINT：2字节INT(INTEGER)：4字节，最常用BIGINT：8字节，用于大数字3.4.3数字类型（续）2.浮点类型：FLOAT：4字节，单精度浮点数DOUBLE：8字节，双精度浮点数DECIMAL(M,D)：精确小数，M最大65，D为小数位数3.4.4JSON数据类型MySQL支持JSON数据类型，优势：（1）存储在JSON列中的文档会被自动验证，无效文档产生错误（2）文档被转换为允许快速读取的内部格式（3）MySQL8优化器可执行JSON列的局部就地更新JSON类型列的值被写为字符串如果字符串不符合JSON格式，则会产生错误3.5表的创建与维护3.5.1使用workbench工具创建表展开数据库→在Tables节点上单击右键→选择CreateTable列属性设置说明：PK：主键NN：非空UQ：唯一B：二进制UN：无符号ZF：零填充AI：自动增长G：计算列Default：默认值Comments：注释设置完成后单击Apply创建表3.5.2使用SQL语句创建表语法格式：CREATETABLE数据表名(列名数据类型[NOTNULL][DEFAULT默认值][AUTO_INCREMENT][PRIMARYKEY],...);注意：创建表前应使用USE数据库名;指定当前数据库否则会报Nodatabaseselected错误3.5.2创建表——例3-1创建major表【例3-1】创建major(专业)表：USEstudent;CREATETABLEmajor(namevarchar(40)NOTNULL,create_yearintNOTNULL,brieftext,PRIMARYKEY(name));说明：3列，varchar/date/text三种类型，专业名称和成立年份非空3.5.2创建表——例3-2创建student表【例3-2】创建student(学生信息)表：CREATETABLEstudent(idchar(10)NOTNULL,namevarchar(20)NOTNULL,gendervarchar(10),birthdaydate,markint,majorvarchar(20),

photolongblob,resumetext,is_bonusintDEFAULT'0',PRIMARYKEY(id));photo用longblob存照片，resume用text存简历3.5.2创建表——例3-3~3-5【例3-3】创建student_course(选课)表：CREATETABLEstudent_course(student_idchar(10)NOTNULL,course_idchar(10)NOTNULL,markfloat(5,2)DEFAULTNULL,PRIMARYKEY(student_id,course_id),KEYFK_course_id(course_id));复合主键(student_id,course_id)，mark为float(5,2)3.5.2创建表——例3-3~3-5【例3-4】创建course(课程)表：CREATETABLEcourse(idchar(10)NOTNULL,namevarchar(20)DEFAULTNULL,categoryvarchar(20)DEFAULTNULL,markintDEFAULTNULL,PRIMARYKEY(id));3.5.2创建表——例3-3~3-5（续）【例3-5】创建score(成绩)表：CREATETABLEscore(idintNOTNULLAUTO_INCREMENT,markjsonNOTNULL,createdtimestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(id))3.5.3使用SQL语句复制表1.创建和已知表结构一致的表：CREATETABLEnewTableLIKEoldTable;【例3-6】CREATETABLEcourse_bakLIKEcourse;2.创建和已知表结构相似的表：使用SHOWCREATETABLEcourse;显示建表语句在此基础上修改表名、添加或删除字段，快速创建新表3.5.4查看表结构1.DESCRIBE语句（查看字段信息）：DESCRIBE表名;【例3-7】DESCRIBEstudent;显示：Field(列名)、Type(类型)、Null(是否允许空值)、Key(是否索引)、Default(默认值)、Extra(附加信息)3.5.4查看表结构（续）2.SHOWCREATETABLE语句（显示建表SQL语句）：SHOWCREATETABLE表名;【例3-8】SHOWCREATETABLEstudent;还可查看存储引擎和字符编码3.5.5使用workbench维护表（1）在表名上单击右键→选择AlterTable（修改表）（2）在要删除的列上单击右键→选择Deleteselected删除列（3）单击列的名称，可对列进行修改（4）在空白行输入新的列名称，可添加列（5）操作完成后单击Apply保存（6）在表名上单击右键→选择DropTable→单击DropNow删除表3.5.6使用SQL语句维护表——修改表ALTERTABLE语句修改表结构：添加列：ALTERTABLE表名ADD列名类型[FIRST|AFTER列名];修改列：ALTERTABLE表名MODIFY列名新类型;删除列：ALTERTABLE表名DROP列名;改表名：ALTERTABLE表名RENAME新表名;3.5.6使用SQL语句维护表——修改表（续）【例3-9】添加email列，修改name列类型：ALTERTABLEstudentADDemailvarchar(30)NOTNULL,MODIFYnamevarchar(40);3.5.6使用SQL语句维护表——示例【例3-10】在第一列添加memo字段：ALTERTABLEstudentADDmemovarchar(10)NOTNULLFIRST;【例3-11】在gender列后添加age列：ALTERTABLEstudentADDageintNOTNULLAFTERgender;3.5.6使用SQL语句维护表——示例（续）【例3-12】删除memo列：ALTERTABLEstudentDROPmemo;【例3-13】修改表名：ALTERTABLEstudentRENAMEstudent_bak;3.5.6使用SQL语句维护表——删除表删除表语法：DROPTABLE数据表名;注意：删除表后表中数据全部清除，没有备份则无法恢复删除不存在的表会产生错误，可加入IFEXISTS避免：DROPTABLEIFEXISTSstudent;如果表student存在则删除，不存在也不会报错3.6使用workbench管理表中数据3.6使用workbench管理表中数据（1）查看数据：在表名上单击右键→选择selectrows（2）插入数据：在数据下方空行输入数据→单击Apply（3）修改数据：双击要修改的单元格→输入数据→单击Apply（4）删除数据：在数据行左边三角形右键单击→选择DeleteRow(s)→单击Apply3.7表的分区3.7表的分区——概述当表中数据量较大时，可采用分区存储提高性能分区优势：（1）数据分布在不同物理设备上，高效利用硬件（2）分区数据更容易维护（批量删除、优化、检查）（3）避免某些瓶颈（如InnoDB单个索引的互斥访问）3.7表的分区——概述（续）（4）可独立备份和恢复分区（5）查询优化：只搜索相关分区而非全表扫描MySQL分区规则：范围(RANGE)、列表(LIST)、哈希(HASH)、键(KEY)分区3.7表的分区——范围分区范围分区(RANGE)：按列值范围分区，使用VALUESLESSTHAN定义用于分区的列必须包含在主键中【例3-14】按入学成绩进行范围分区：PARTITIONBYRANGE(mark)(PARTITIONp0VALUESLESSTHAN(500),3.7表的分区——范围分区（续）PARTITIONp1VALUESLESSTHAN(600),PARTITIONp2VALUESLESSTHANmaxvalue);低于500存p0，500~600存p1，600以上存p23.7表的分区——列表分区列表分区(LIST)：按一组离散值分区，使用VALUESIN定义【例3-15】按是否有奖学金分区：PARTITIONBYLIST(is_bonus)(PARTITIONp0VALUESIN(0),PARTITIONp1VALUESIN(1)

);3.7表的分区——列表分区（续）【例3-16】按专业分区（使用LISTCOLUMNS）：PARTITIONBYLISTCOLUMNS(major)(PARTITIONp0VALUESIN('交通运输','土木工程'),PARTITIONp1VALUESIN('工商管理','市场营销'));3.7表的分区——哈希分区和键分区哈希分区(HASH)：确保数据在分区中平均分布MySQL根据表达式自动完成分区，用户只需指定表达式和分区数【例3-17】按生日年份hash分区：PARTITIONBYHASH(year(birthday))PARTITIONS4;键分区(KEY)：与哈希分区类似，但支持非数字类型列使用系统提供的哈希函数，不支持用户自定义表达式【例3-18】按专业名称键分区：PARTITIONBYKEY(major)PARTITIONS4;本章小结MySQL实例包含系统数据库(mysql)和用户数据库，数据以文件形式存储CREATEDATABASE创建数据库，DROPDATABASE删除数据库数据类型：字符串(CHAR/VARCHAR/TEXT/BLOB/ENUM/SET)、日期时间、数字、JSONCREATETABLE创建表，ALTERTABLE修改表，DROPTABLE删除表DESCRIBE查看表结构，SHOWCREATETABLE查看建表语句表的分区：范围(RANGE)、列表(LIST)、哈希(HASH)、键(KEY)四种分区方式第4章使用SQL进行数据库操作4.4基本查询4.3数据删除数据插入4.2数据更新4.5嵌套查询CONTENTS目录

4.1连接查询

4.6窗口函数查询

4.74.1数据插入4.1.1INSERT语句语法INSERT语句向表添加新行，基本语法：INSERT[INTO]table_name[(column_list)]VALUES(value_list)table_name：接收数据的表或视图名称column_list：列的列表，用圆括号括起，逗号分隔VALUES：引入要插入的数据值列表省略column_list时，默认包含表中所有列并按定义顺序排列4.1.2基本插入操作（例4-1~4-3）例4-1简单INSERT（省略列名，按顺序插入）：INSERTcourseVALUES('C903','大学物理','必修',4);例4-2按指定列顺序插入数据：INSERTcourse(name,code,category,mark)VALUES('艺术欣赏','C606','选修',2);例4-3显式指定列插入（未给值的列应允许为空）：INSERTcourse(name,code,category)VALUES('绘画技巧','C607','选修');4.1.3特殊插入操作（例4-4~4-5）例4-4将数据插入到带有自增列的表：CREATETABLEtest01(idintAUTO_INCREMENTPRIMARYKEY,namevarchar(30));INSERTtest01(name)VALUES('a01');--系统自动生成标识值INSERTtest01(id,name)VALUES(99,'a02');--手动指定值4.1.3特殊插入操作（例4-4~4-5）（续）例4-5插入数据到score表（JSON类型）：INSERTscoreVALUES('{"chinese":90,"math":86,"english":85}');

id列自增、created列为timestamp类型，无需提供值4.1.4批量插入与查询结果插入（例4-6~4-7）例4-6一次性插入多条记录（效率更高）：INSERTINTOcourseVALUES('C907','Java程序设计','选修',2),('C908','Python程序设计','选修',3);例4-7将查询结果插入到表中：INSERTINTOcourse_bak(id,name,category,mark)SELECTid,name,category,markFROMcourse;查询结果的字段类型必须与插入字段类型匹配4.2数据更新4.2数据更新（例4-8~4-9）UPDATE语句更改表或视图中单行、多行或所有行的数据语法：UPDATEtable_nameSETcolumn_name=expression[,..][WHEREcondition]例4-8将所有课程的学分加1（不带WHERE更新全部行）：UPDATEcourseSETmark=mark+1;4.2数据更新（例4-8~4-9）（续）

例4-9使用WHERE子句限定更新范围：UPDATEcourseSETmark=mark+1WHEREcategory='必修';注意：通常应通过WHERE限制被更新的记录4.3数据删除4.3.1DELETE语句（例4-10~4-11）DELETE语句删除表或视图中的一行或多行语法：DELETE[FROM]table_name[WHEREcondition]例4-10删除所有行（不带WHERE）：DELETEFROMtest01;例4-11删除特定行（带WHERE条件）：DELETEFROMcourseWHEREid='C607';DELETE删除数据但表结构保留，DROPTABLE则删除表本身4.3.2TRUNCATETABLE语句（例4-12）TRUNCATETABLE一次删除表中所有行，速度更快语法：TRUNCATETABLE表名;与DELETE的区别：DELETE逐行删除并记日志，TRUNCATE释放数据页只记页释放TRUNCATE重置自增列计数器为种子值TRUNCATE不能用于被外键约束引用的表TRUNCATE不激活触发器例4-12：TRUNCATETABLEcourse;4.4基本查询4.4.1简单查询（例4-13~4-15）语法：SELECT[ALL|DISTINCT]select_listFROMtable_name[LIMITn]例4-13查询student表所有记录：SELECT*FROMstudent;4.4.1简单查询（例4-13~4-15）（续）例4-14查询指定列并使用函数计算年龄：SELECTid,name,Year(curDate())-Year(birthday)ASageFROMstudent;例4-15使用COUNT函数查询学生人数：SELECTCOUNT(*)AStotalFROMstudent;4.4.1聚合函数（例4-16~4-18）常用聚合函数：COUNT()、AVG()、MAX()、MIN()、SUM()例4-16查询所有学生mark的平均值：SELECTAVG(mark)ASavg_markFROMstudent;例4-17查询所有学生mark的最大值：SELECTMAX(mark)ASmax_markFROMstudent;例4-18查询所有学生mark的总和：SELECTSUM(mark)ASsum_markFROMstudent;4.4.2带条件查询（例4-19~4-21）WHERE子句指定查询条件，比较符：=、!=、>、>=、<、<=例4-19查询mark>=600的学生：SELECT*FROMstudentWHEREmark>=600;例4-20查询所有选修课程：SELECTid,name,category,markFROMcourseWHEREcategory='选修';4.4.2带条件查询（例4-19~4-21）（续）例4-21使用BETWEEN查询学分在2~5之间的课程：SELECT*FROMcourseWHEREmarkBETWEEN2and5;等价于：WHEREmark>=2ANDmark<=54.4.2特殊运算符查询（例4-22~4-24）例4-22使用LIKE模糊查询（%匹配任意字符）：SELECT*FROMcourseWHEREnameLIKE'%数据库%';例4-23使用IN查询多个值之一：SELECTcourse_id,markFROMstudent_courseWHEREcourse_idIN('C606','C607');例4-24使用ISNULL查询空值：SELECT*FROMmajorWHEREbriefISNULL;注意：不能用'=null'，必须用'ISNULL'4.4.2逻辑运算符组合查询（例4-25~4-27）AND：两条件都为真；OR：之一为真；NOT：取反例4-25查询专业为会计学且性别为女的学生：SELECT*FROMstudentWHEREmajor='会计学'ANDgender='女';例4-26查询专业为金融学或会计学的学生：SELECT*FROMstudentWHEREmajor='金融学'ORmajor='会计学';例4-27查询JSON字段（score表成绩都及格）：4.4.2逻辑运算符组合查询（例4-25~4-27）（续）SELECT*FROMscoreWHEREmark->'$.chinese'>60ANDmark->'$.math'>60ANDmark->'$.english'>60;4.4.3查询结果排序与重定向（例4-28~4-29）ORDERBY子句排序输出：ASC升序（默认），DESC降序例4-28按专业升序，专业相同的按成绩降序：SELECTid,name,gender,major,markFROMstudentORDERBYmajorASC,markDESC;重定向输出：CREATETABLE新表SELECT查询结果例4-29将查询结果存入新表st_new：CREATETABLEst_new(SELECT*FROMstudentWHEREis_bonus=true);4.4.3联合查询（例4-30）UNION操作符将不同查询的数据组合起来UNION自动去除重复行，UNIONALL保留全部合并规则：两个SELECT必须输出同样的列数各相应列的数据类型必须相同仅最后一个SELECT可用ORDERBY4.4.3联合查询（例4-30）（续）例4-30查询工程管理或工程力学专业的学生：SELECTid,name,majorFROMstudentWHEREmajor='工程管理'UNIONSELECTid,name,majorFROMstudentWHEREmajor='工程力学';4.4.3分组统计与筛选（例4-31~4-33）GROUPBY分组，HAVING对分组结果筛选HAVING作用于组，WHERE作用于基本表或视图例4-31按category统计课程门数：SELECTcategory,COUNT(category)AScFROMcourseGROUPBYcategory;例4-32查询每门课程的最高分：SELECTcourse_id,max(mark)ASmax_mark4.4.3分组统计与筛选（例4-31~4-33）（续）FROMstudent_courseGROUPBYcourse_id;例4-33查询平均成绩>=80的课程：SELECTcourse_id,AVG(mark)ASavg_markFROMstudent_courseGROUPBYcourse_idHAVINGAVG(mark)>=80;4.4.3限制返回与去重（例4-34~4-37）LIMIT[偏移量,]行数--偏移量从0开始例4-34返回前3名（按mark降序）：SELECT*FROMstudentORDERBYmarkDESCLIMIT3;例4-35返回第5行开始的3条记录：SELECT*FROMstudentORDERBYmarkDESCLIMIT4,3;DISTINCT去除重复记录例4-37查询学生专业并去重：SELECTDISTINCT(major)FROMstudent;4.5嵌套查询4.5.1单值嵌套查询（例4-38）嵌套查询：在一个SELECT的WHERE中嵌入另一个SELECT处理方式：由里向外，先处理最内层子查询单值嵌套查询：子查询返回一个值可直接使用=、<>、>、<、>=、<=等运算符例4-38查询和李思思相同专业的同学：4.5.1单值嵌套查询（例4-38）（续）SELECTid,nameFROMstudentWHEREmajor=(SELECTmajorFROMstudentWHEREname='李思思');执行过程：先查李思思的专业，再查该专业所有学生4.5.2多值嵌套查询-ANY与ALL（例4-39~4-40）多值嵌套查询：子查询返回多个值需配合ANY、ALL、IN、EXISTS等运算符使用例4-39ANY运算符（比子查询任一值高即满足）：SELECTstudent_id,markFROMstudent_courseWHEREcourse_id='C901'ANDmark>ANY(SELECTmarkFROMstudent_courseWHEREcourse_id='C902');4.5.2多值嵌套查询-ANY与ALL（例4-39~4-40）（续）含义：比C902的最低成绩高例4-40ALL运算符（比子查询所有值高才满足）：...ANDmark>ALL(SELECTmark...WHEREcourse_id='C902');含义：比C902的最高成绩还高4.5.2多值嵌套查询-IN与EXISTS（例4-41~4-42）例4-41IN运算符（等价于=ANY）：SELECTid,nameFROMstudentWHEREidIN(SELECTstudent_idFROMstudent_courseWHEREcourse_id='C901'ORcourse_id='C902');含义：查询选修了C901或C902课程的学生例4-42EXISTS运算符（子查询返回行则为true）：4.5.2多值嵌套查询-IN与EXISTS（例4-41~4-42）（续）SELECTid,nameFROMcourseWHEREEXISTS(SELECTidFROMstudent_courseWHEREcourse_id='C901'ORcourse_id='C902');NOTEXISTS返回结果与EXISTS相反4.6连接查询4.6.1连接概述（例4-43）连接查询：根据表间逻辑关系从多个表中检索数据连接可在WHERE子句或FROM子句中建立FROM子句建立连接的语法：FROMjoin_table[join_type]JOINjoin_tableONjoin_condition连接类型：内连接(INNERJOIN)、外连接(OUTERJOIN)、交叉连接(CROSSJOIN)4.6.1连接