《数据库基本操作》课件：掌握数据库核心技能

数据库基本操作掌握数据库核心技能课程概述课程目标学习路径核心技能本课程的目标是让学员理解数据库的我们将从数据库的基础概念入手，逐基本概念、熟练掌握语言、能够步深入到的安装配置、语SQL MySQL SQL进行数据库的设计和管理，并具备解言的学习、数据操作、高级查询技巧、决实际问题的能力通过学习，您将索引和视图、事务管理、存储过程和能够独立完成数据库的创建、查询、函数、数据库安全以及性能优化等方更新和删除等操作面第一部分数据库基础概念在深入学习数据库操作之前，我们需要先了解一些基础概念本部分将介绍什么是数据库、数据库的作用、数据库的重要性以及数据库管理系统（）的概念和功能，为后续的学习打下坚实的基础DBMS什么是数据库？定义作用重要性12数据库是按照数据结构来组织、数据库的主要作用是存储和管理存储和管理数据的仓库它是一数据，提供数据的查询、更新和种长期存储在计算机内的、有组删除等操作，保证数据的完整性织的、可共享的数据集合和安全性它能够帮助企业更好地管理和利用数据资源数据库管理系统（）DBMS概念常见功能DBMS数据库管理系统常见的包括的主要功能包DBMS DBMS（）是一种用、、括数据定义、数据操DBMS MySQLOracle于管理数据库的软件、作、数据控制和数据SQL Server系统它负责数据库等每种维护通过这些功能，PostgreSQL的创建、查询、更新都有其特点和能够有效地管DBMS DBMS和删除等操作，并提适用场景，选择合适理和利用数据资源，供数据的安全性和完的对于项目的提高数据处理的效率DBMS整性保障成功至关重要和质量关系型数据库非关系型数据库vs特点对比适用场景关系型数据库（）采用表格结构存储数据，数据之适用于数据结构稳定、数据一致性要求高的场景，RDBMS RDBMS间存在关联关系，支持语言非关系型数据库如金融、电商等适用于数据结构变化频繁、数据SQL NoSQL（）采用键值对、文档等方式存储数据，数据之间一致性要求不高的场景，如社交网络、大数据分析等NoSQL没有固定的关系，不支持语言SQL语言简介SQL定义重要性12（是与关系型数据库进SQL StructuredQuery SQL）是一种用于管行交互的标准语言掌握Language理关系型数据库的语言语言是进行数据库操SQL它包括数据查询、数据更作的基础，也是成为一名新、数据定义和数据控制合格的数据库管理员或开等功能发人员的必备技能基本语法结构3的基本语法结构包括、、、等SQL SELECTINSERT UPDATEDELETE语句通过这些语句，我们可以对数据库中的数据进行各种操作数据库设计基础三大范式图E-R数据库设计的三大范式是第一范式（）、第二范式图（）是一种用于描述实体1NF E-R Entity-Relationship Diagram（）和第三范式（）它们是数据库设计的规范，之间关系的图形工具它可以帮助我们更好地理解业务需2NF3NF能够减少数据冗余，提高数据的一致性和完整性求，设计出合理的数据库结构第二部分入门MySQL本部分将介绍数据库是一种流行的关系型数据库管理MySQL MySQL系统，具有易于使用、性能良好和开源免费等优点我们将学习的安装配置、命令行工具的使用以及基本的数据操作MySQL掌握的基本操作是进行数据库开发的基础，能够帮助我们更好MySQL地管理和利用数据资源让我们一起开始之旅！MySQL简介MySQL特点优势12是一种关系型数据的优势包括高性能、MySQL MySQL库管理系统，具有易于使高可靠性、易于管理和低用、性能良好和开源免费成本它能够满足各种规等特点它支持多种操作模的应用需求，并提供强系统和编程语言，被广泛大的数据管理功能应用于应用开发和企Web业级应用应用场景3被广泛应用于应用开发、企业级应用、数据仓库和MySQL Web嵌入式数据库等领域许多知名的互联网公司和企业都在使用作为其数据存储解决方案MySQL安装与配置MySQL下载首先，我们需要从官网下载适合自己操作系统的MySQL安装包选择合适的版本和安装方式安装步骤按照安装向导的提示，逐步完成的安装注意设MySQL置用户的密码和选择合适的安装目录root基本配置安装完成后，需要进行一些基本配置，如设置字符集、调整缓冲区大小等这些配置能够提高的性能和MySQL稳定性命令行工具MySQL登录基本命令使用命令行工具，通过常用的命令行工具包mysql MySQL输入用户名和密码登录到括、SHOW DATABASESUSE服务器可以使用、MySQL-u database_name SHOW参数指定用户名，参数指等这些命令能够帮-p TABLES定密码助我们查看数据库列表、选择数据库和查看表列表退出使用或命令退出命令行工具确保在退出前保存所exit quitMySQL有的修改和操作创建数据库语法CREATE DATABASE使用语句创建新的数据库语法如下CREATE DATABASE可以指定字符集和排CREATE DATABASEdatabase_name;序规则实例演示例如，创建一个名为的数据库mydatabase CREATE可以使用语DATABASE mydatabase;SHOW DATABASES句查看已创建的数据库列表选择和删除数据库语法DROP DATABASE语句USE使用语句删除数据DROP DATABASE使用语句选择要操作的数据库USE1库语法如下DROP DATABASE语法如下选USE database_name;删除数据库会删除2database_name;择数据库后，可以对其中的表进行其中的所有表和数据，请谨慎操作操作创建表语法数据类型约束CREATE TABLE使用语句创建新的表常用的数据类型包括、、常用的约束包括、CREATE TABLEINT VARCHARPRIMARY KEYNOT语法如下、等选择合适的数据类、等约束能够保证数CREATE TABLE table_name DATETEXT NULLUNIQUE型能够提高存储效率和查询性能据的完整性和一致性column_name data_type需要指定表名、列名、constraints,...;数据类型和约束修改表结构语法添加列ALTER TABLE12使用语句修改已存在的表结构可以添加列、修改使用语句添加新的列例如ALTER TABLEADD COLUMNALTER TABLE列和删除列语法如下ALTER TABLEtable_name action;table_name ADDCOLUMN column_name data_type;修改列删除列34使用语句修改列的数据类型或约束例如使用语句删除列例如MODIFY COLUMNDROP COLUMNALTER TABLEALTERTABLEtable_name MODIFYCOLUMN column_name table_name DROPCOLUMN column_name;data_type;删除表语法DROP TABLE使用语句删除已存在的表语法如下删除表会删除其中的所有数据，请谨慎操DROP TABLEDROP TABLEtable_name;作注意事项删除表之前，最好先备份表中的数据，以防止误操作导致数据丢失可以使用工具进行数据备份mysqldump第三部分数据操作本部分将介绍的数据操作我们将学习如何插入数据、查询数据、更新数据和删除数据这些是数据库操作的基本MySQL技能，掌握它们能够让我们更好地管理和利用数据资源让我们一起学习这些基本的数据操作，为后续的高级查询技巧打下基础！插入数据单行插入一次插入一行数据例如INSERT2语法INSERT INTOINTO table_name column1,column2VALUES value1,value2;使用语句向表中插入数INSERT INTO1据语法如下INSERT INTO多行插入table_name column_name1,column_name2,...VALUES value1,一次插入多行数据例如INSERTvalue2,...;INTOtable_name column1,column23VALUES value1,value2,value3,value4;查询数据语法基础查询所有列查询特定列SELECT使用语句从表中查询数据语使用通配符查询所有列例如指定要查询的列名例如SELECT*SELECT法如下会返回表SELECT column_name1,SELECT*FROM table_name;column1,column2FROM table_name;中的所有列的数据只会返回指定的列的数据column_name2,...FROM table_name;可以指定要查询的列和表名子句WHERE条件查询比较运算符12使用子句进行条件常用的比较运算符包括、WHERE=查询，可以根据指定的条、、、、等例==!=件过滤数据语法如下如SELECT*FROMSELECT column_name1,table_name WHEREcolumn_name2,...FROM column110;table_name WHEREcondition;逻辑运算符3常用的逻辑运算符包括、、等例如AND ORNOT SELECT*FROM table_name WHERE column110AND column220;排序查询结果子句升序和降序多列排序ORDER BY使用子句对使用关键字进行可以按照多列进行排ORDER BYASC查询结果进行排序升序排序，使用序例如DESC SELECT*语法如下关键字进行降序排序SELECT FROM table_name例如column_name1,SELECT*ORDER BY column1column_name2,...FROM table_name ASC,column2DESC;先按照升序FROM table_name ORDER BY column1column1排序，再按照ORDERBYASC;降序排序column_name;column2限制查询结果子句LIMIT使用子句限制查询结果的数量语法如下LIMITSELECT column_name1,column_name2,...FROMtable_name LIMITnumber;分页查询可以使用子句进行分页查询例如LIMIT SELECT*表示偏移FROM table_name LIMIToffset,number;offset量，表示查询的数量number更新数据更新单行更新符合条件的单行数据例如2语法UPDATE table_name SETcolumn1=UPDATEvalue1WHERE column2=value2;使用语句更新表中的数据UPDATE1语法如下UPDATE table_name SETcolumn_name=value WHERE更新多行condition;更新符合条件的多行数据例如3UPDATE table_name SETcolumn1=value1WHEREcolumn210;删除数据语法删除所有行条件删除DELETE使用语句删除表中的数据语删除表中的所有行例如删除符合条件的行例如DELETE DELETEDELETE法如下删除所有行会清空DELETE FROM table_name FROM table_name;FROM table_name WHEREcolumn1表中的数据，请谨慎操作只会删除符合条件的行WHERE condition;10;第四部分高级查询技巧本部分将介绍的高级查询技巧我们将学习聚合函数、分组查MySQL询、子查询和连接查询这些技巧能够让我们更灵活地查询和分析数据，提高数据处理的效率和质量掌握这些高级查询技巧，能够让我们在实际工作中更好地应用数据库技术！聚合函数COUNT SUMAVG函数用于统计表中函数用于计算表中或列函数用于计算表中或列COUNT SUMAVG或列中数据的数量例如中数据的总和例如中数据的平均值例如SELECT COUNT*FROM SELECTSUMcolumn_name SELECTAVGcolumn_name统计表中的总行计算指定计算指定table_name;FROM table_name;FROM table_name;数列的总和列的平均值和MAX MIN函数用于查找表中或列MAX中数据的最大值，函数MIN用于查找表中或列中数据的最小值例如SELECTMAXcolumn_name,MINcolumn_name FROMtable_name;分组查询子句GROUP BY使用子句对查询结果进行分组语法如下GROUP BYSELECT column_name1,column_name2,...FROMtable_name GROUPBY column_name;子句HAVING使用子句对分组后的结果进行过滤语法如下HAVINGSELECT column_name1,column_name2,...FROMtable_name GROUPBYcolumn_name HAVINGcondition;子查询概念单行子查询多行子查询子查询是指嵌套在其他查询中的返回单行数据的子查询例如返回多行数据的子查询例如SQL查询它可以帮助我们更灵活地查询SELECT*FROM table_name WHERESELECT*FROM table_name WHERE和分析数据column1=SELECTcolumn1FROM column1IN SELECTcolumn1FROMtable_name WHEREcolumn2=table_name WHEREcolumn2=value2;value2;连接查询内连接左外连接12使用关键字进行内连使用关键字进INNER JOINLEFT OUTERJOIN接，返回两个表中符合连接条行左外连接，返回左表中的所件的行语法如下有行和右表中符合连接条件的SELECT*行如果右表中没有符合条件FROM table1INNER JOIN table2的行，则返回值语法如ON table

1.column_name=NULL下table

2.column_name;SELECT*FROM table1LEFTOUTER JOINtable2ONtable

1.column_name=table

2.column_name;右外连接3使用关键字进行右外连接，返回右表中的所有行和左表RIGHT OUTERJOIN中符合连接条件的行如果左表中没有符合条件的行，则返回值NULL语法如下SELECT*FROMtable1RIGHT OUTERJOINtable2ONtable

1.column_name=table

2.column_name;第五部分索引和视图本部分将介绍的索引和视图索引能够提高查询性能，视图能MySQL够简化查询操作掌握它们能够让我们更高效地管理和利用数据资源让我们一起学习索引和视图，为后续的性能优化打下基础！索引简介定义优势使用场景索引是一种用于提高索引的主要优势是提索引适用于经常需要查询性能的数据结构高查询性能它可以进行查询的列例如，它可以帮助数据库系减少数据库系统的经常需要根据用户IO ID统快速定位到符合查操作，加快数据的检查询用户信息，可以询条件的数据，减少索速度为用户列创建索引ID查询的时间创建索引语法CREATE INDEX使用语句创建索引语法如下CREATE INDEXCREATEINDEX index_name ON table_name column_name;单列索引为单列创建索引例如CREATE INDEXindex_name ONtable_name column_name;组合索引为多列创建索引例如CREATE INDEXindex_name ON组合索引可table_name column_name1,column_name2;以提高多列查询的性能查看和删除索引语法DROP INDEX语法SHOW INDEX使用语句删除索引语DROP INDEX使用语句查看表的索SHOW INDEX1法如下DROP INDEXindex_name引信息语法如下SHOW INDEX删除索引会降低查2ONtable_name;可以查看索引的FROMtable_name;询性能，请谨慎操作名称、类型、列等信息视图简介定义优势使用场景123视图是一种虚拟的表，它基于视图的主要优势是简化查询操视图适用于需要频繁进行复杂查询结果生成视图不存储实作它可以将复杂的查询封装查询的场景例如，经常需要际的数据，而是存储查询的定成一个视图，用户只需要查询查询用户的订单信息，可以创义视图即可获取所需的数据建一个包含用户和订单信息的视图创建和使用视图语法CREATE VIEW使用语句创建视图语法如下CREATE VIEWCREATE VIEWview_name ASSELECT statement;查询视图查询视图与查询表类似例如查询视图会执行视图定义的查询，并返回结果SELECT*FROM view_name;修改和删除视图语法语法ALTER VIEWDROP VIEW使用语句修改视图的定使用语句删除视图语ALTER VIEWDROP VIEW1义语法如下法如下删ALTER VIEWDROP VIEWview_name;2修除视图会删除视图的定义，但不会view_name ASSELECT statement;改视图会更新视图的查询定义影响实际的数据第六部分事务管理本部分将介绍的事务管理事务是数据库操作的逻辑单元，能MySQL够保证数据的一致性和完整性掌握事务管理能够让我们更好地控制数据库操作的执行过程，防止数据错误和丢失让我们一起学习事务管理，为后续的数据库安全打下基础！事务概念定义特性ACID事务是数据库操作的逻辑单元特性是事务的四大特性，包ACID它包含一系列的操作，要么全部括原子性（）、一致性Atomicity执行成功，要么全部执行失败（）、隔离性Consistency事务能够保证数据的一致性和完（）和持久性Isolation整性（）它们保证了事务Durability的可靠性和正确性事务控制语句BEGIN COMMIT12使用语句开始一个使用语句提交一BEGIN COMMIT事务例如开始个事务例如BEGIN;COMMIT;一个新的事务提交事务，将事务中的所有操作永久保存到数据库中ROLLBACK3使用语句回滚一个事务例如回滚事务，ROLLBACK ROLLBACK;撤销事务中的所有操作，将数据库恢复到事务开始之前的状态事务隔离级别读未提交读已提交可重复读串行化读未提交（读已提交（可重复读（串行化（）是Read ReadRepeatable Serializable）是最低的）是常用的隔）是默认的隔最高的隔离级别事务会Uncommitted CommittedRead MySQL隔离级别事务可以读取离级别事务只能读取其离级别事务可以保证在锁定所有读取的数据，可其他事务未提交的数据，他事务已提交的数据，可同一个事务中多次读取的以防止脏读、不可重复读可能会导致脏读以防止脏读，但可能会导数据是一致的，可以防止和幻读，但会降低并发性致不可重复读脏读和不可重复读，但可能能会导致幻读第七部分存储过程和函数本部分将介绍的存储过程和函数存储过程和函数是一组预编MySQL译的语句，可以提高数据库操作的效率和安全性掌握存储过程和SQL函数能够让我们更好地组织和管理数据库代码，提高开发效率和代码复用性让我们一起学习存储过程和函数，为后续的数据库安全打下基础！存储过程简介定义1存储过程是一组预编译的语句，可以像调用函数一样调用SQL它存储在数据库中，可以被多个应用程序共享优势2存储过程的主要优势是提高数据库操作的效率和安全性它可以减少网络传输的数据量，防止注入攻击，提高代码的复SQL用性创建存储过程语法CREATE PROCEDURE使用语句创建存储过程语法如下CREATE PROCEDURECREATE PROCEDURE procedure_name parameter1data_type,parameter2data_type,...BEGIN SQLstatements;END;参数类型存储过程可以有输入参数和输出参数常用的参数类型包括、和表示输入参数，表示输出参数，IN OUTINOUT INOUT表示既是输入参数又是输出参数INOUT调用和删除存储过程语法DROP PROCEDURE语句CALL使用语句删除存DROP PROCEDURE1使用语句调用存储过程语法储过程语法如下CALL DROP如下删除CALL procedure_name2PROCEDUREprocedure_name;存储过程会删除存储过程的定义，parameter1,parameter2,...;请谨慎操作函数简介定义与存储过程的区别函数是一组预编译的语句，可以像调用函数一样调用函数和存储过程的主要区别在于函数必须返回一个值，而SQL它存储在数据库中，可以被多个应用程序共享函数必须存储过程可以不返回任何值函数通常用于计算和处理数返回一个值据，而存储过程通常用于执行复杂的操作创建和使用函数语法CREATE FUNCTION使用语句创建函数语法如下CREATE FUNCTIONCREATEFUNCTION function_name parameter1data_type,parameter2data_type,...RETURNS data_type BEGINSQLstatements;RETURN value;END;调用函数调用函数与调用存储过程类似例如SELECT可以将函数function_name parameter1,parameter2,...;的结果作为查询的一部分第八部分数据库安全本部分将介绍的数据库安全数据库安全是保证数据完整性和机密性的重要措施我们将学习用户管理、权限管理、MySQL备份与恢复等技术，提高数据库的安全性和可靠性让我们一起学习数据库安全，为后续的性能优化打下基础！用户管理创建用户删除用户使用语句创建新的用户语法如下使用语句删除已存在的用户语法如下CREATE USERCREATE DROPUSER DROP可以删除用户会删除用户的权限和USER username@hostname IDENTIFIEDBY password;USER username@hostname;指定用户名、主机名和密码账户信息，请谨慎操作权限管理语句GRANT使用语句授予用户权限语法如下GRANT GRANTprivilege1,privilege2,...ON database_name.table_name可以指定要授予的权限、数TO username@hostname;据库名、表名和用户名语句REVOKE使用语句撤销用户的权限语法如下REVOKE REVOKEprivilege1,privilege2,...ON database_name.table_name撤销权限会删除用户的FROM username@hostname;权限，请谨慎操作备份与恢复工具数据导入导出mysqldump使用工具进行数据库备份例如使用命令行工具或命令导入文件例如mysqldump mysqldump-mysql sourceSQL可以将数据库可以将备u username-p database_namebackup.sql;mysql-u username-p database_namebackup.sql;的数据和结构备份到文件中份的数据和结构导入到数据库中SQL第九部分性能优化本部分将介绍的性能优化性能优化是提高数据库运行效率和MySQL响应速度的重要措施我们将学习查询优化、服务器参数调优等技术，提高数据库的性能和稳定性让我们一起学习性能优化，为后续的数据库设计实践打下基础！查询优化语句索引优化EXPLAIN使用语句分析查询的执行通过创建合适的索引，可以提高EXPLAIN计划例如查询性能需要根据实际的查询EXPLAIN SELECT*需求选择合适的索引类型和列FROMtable_name WHEREcolumn1可以查看查询的执行顺避免创建过多的索引，因为索引=value1;序、使用的索引等信息会降低写入性能服务器参数调优内存配置调整服务器的内存配置，可以提高性能常用的MySQL参数包括、等innodb_buffer_pool_size key_buffer_size需要根据服务器的内存大小和实际的负载情况进行调整并发配置调整服务器的并发配置，可以提高并发处理能力MySQL常用的参数包括、等max_connections table_open_cache需要根据服务器的数量和实际的并发连接数进行调CPU整第十部分数据库设计实践本部分将介绍数据库设计实践数据库设计是构建高效、可靠数据库应用的关键环节我们将学习需求分析、概念设计、逻辑设计和物理设计等步骤，掌握数据库设计的核心技能让我们一起学习数据库设计实践，为实际的项目开发打下基础！需求分析业务流程1分析业务流程，了解数据的产生、流转和使用情况例如，分析电商平台的订单流程，了解订单的创建、支付、发货和确认等环节数据流程2分析数据流程，了解数据的类型、格式和关系例如，分析电商平台的用户数据、商品数据和订单数据之间的关系概念设计实体关系图属性定义使用实体关系图（图）描述实定义实体的属性属性是实体的E-R体之间的关系实体是现实世界特征，如用户的、姓名、年龄ID中的事物，如用户、商品、订单等需要为每个属性选择合适的等关系是实体之间的联系，如数据类型用户和订单之间存在下单关“”系逻辑设计表结构设计根据实体关系图设计表结构每个实体对应一个表，每个属性对应表中的一个列需要为每个列选择合适的数据类型和约束关系定义定义表之间的关系常用的关系包括一对

一、一对多和多对多需要使用外键约束维护表之间的关系物理设计索引设计存储优化根据查询需求设计索引需要为经1常需要进行查询的列创建索引，提根据数据量和访问频率优化存储高查询性能避免创建过多的索引，可以采用分区表、归档等技术，提2因为索引会降低写入性能高存储效率和管理效率课程总结核心知识回顾学习资源推荐12回顾本课程的核心知识点，推荐一些学习资源，包括包括数据库的基本概念、官方文档、教MySQLSQL语言、操作、程网站、数据库设计书籍SQL MySQL高级查询技巧、索引和视等可以根据自己的需求图、事务管理、存储过程选择合适的学习资源和函数、数据库安全以及性能优化等实践建议3建议多进行实践，通过实际的项目开发巩固所学的知识可以从简单的数据库应用开始，逐步挑战更复杂的项目祝您在数据库学习的道路上取得更大的进步！。