MySQL数据库管理系统.ppt

MySQL数据库管理系统 1 提纲 2020 2 9 DatabaseTechnology GUCAS 2 MySQL数据库简介MySQL的安装与配置MySQL基本工具MySQL数据库操作建库建表存储过程触发器备份与恢复 MySQL数据库简介 MySQL数据库的特点主要有以下几个方面 使用核心线程的完全多线程服务 这意味着可以采用多CPU体系结构 可运行在不同平台 使用C和C 语言编写 并使用多种编译器进行测试 保证了源代码的可移植性 支持AIX FreeBSD HP UX Linux MacOS NovellNetware OpenBSD OS 2Wrap Solaris Windows等多种操作系统 为多种编程语言提供了API 这些编程语言包括C C Eiffel Java Perl PHP Python Ruby和Tcl等 支持多线程 充分利用CPU资源 优化的SQL查询算法 可有效地提高查询速度 既能够作为一个单独的应用程序应用在客户端服务器网络环境中 也能够作为一个库嵌入其他的软件中 提供多语言支持 常见的编码如中文的GB2312 BIG5 日文的Shift JIS等 都可以用做数据表名和数据列名 提供TCP IP ODBC和JDBC等多种数据库连接途径 提供可用于管理 检查 优化数据库操作的管理工具 可以处理拥有上千万条记录的大型数据库 2020 2 9 DatabaseTechnology GUCAS 3 MySQL的安装与配置 下载地址 2020 2 9 DatabaseTechnology GUCAS 4 运行下载的 mysql 5 1 31 win32 出现如下界面 MySQL的安装与配置 续 Mysql安装向导启动 按Next继续 MySQL的安装与配置 续 选择安装类型 有 Typical 默认 Complete 完全 Custom 用户自定义 三个选项 我们选择 Custom 有更多的选项 也方便熟悉安装过程 MySQL的安装与配置 续 确保DeveloperComponents全部选上 点选 Change 手动指定安装目录 MySQL的安装与配置 续 填上安装目录 我的是 d ProgramFiles MySQL MySQLServer5 1 也建议不要放在与操作系统同一分区 这样可以防止系统备份还原的时候 数据被清空 按 OK 继续 MySQL的安装与配置 续 返回刚才的界面 按 Next 继续 MySQL的安装与配置 续 确认先前的设置 如果有误 按 Back 返回重做 按 Install 开始安装 MySQL的安装与配置 续 正在安装中 安装过程中会出现MySQL企业版的信息提示 点Next继续安装 MySQL的安装与配置 续 MySQL的安装与配置 续 至此 软件安装完成 出现mysql配置向导 将 ConfiguretheMysqlServernow 前面的勾打上 点 Finish 结束软件的安装并启动mysql配置向导 MySQL的安装与配置 续 mysql配置向导启动界面 按 Next 继续 MySQL的安装与配置 续 选择配置方式 DetailedConfiguration 手动精确配置 StandardConfiguration 标准配置 我们选择 DetailedConfiguration 方便熟悉配置过程 MySQL的安装与配置 续 选择服务器类型 DeveloperMachine 开发测试类 mysql占用很少资源 ServerMachine 服务器类型 mysql占用较多资源 DedicatedMySQLServerMachine 专门的数据库服务器 mysql占用所有可用资源 一般选 ServerMachine 不会太少 也不会占满 MySQL的安装与配置 续 选择mysql数据库的大致用途 MultifunctionalDatabase 通用多功能型 好 TransactionalDatabaseOnly 服务器类型 专注于事务处理 一般 Non TransactionalDatabaseOnly 非事务处理型 较简单 主要做一些监控 记数用 对MyISAM数据类型的支持仅限于non transactional 随自己的用途而选择 我这里选择 TransactionalDatabaseOnly 按 Next 继续 MySQL的安装与配置 续 对InnoDBTablespace进行配置 就是为InnoDB数据库文件选择一个存储空间 这里没有修改 使用用默认位置 直接按 Next 继续 MySQL的安装与配置 续 选择您的应用的一般mysql访问量 同时连接的数目 DecisionSupport DSS OLAP 20个左右 OnlineTransactionProcessing OLTP 500个左右 ManualSetting 手动设置 自己输一个数 这里选 OnlineTransactionProcessing OLTP 一般够用了 按 Next 继续 MySQL的安装与配置 续 是否启用TCP IP连接 设定端口 如果不启用 就只能在自己的机器上访问mysql数据库了 这里启用 把前面的勾打上 PortNumber 3306 按 Next 继续 MySQL的安装与配置 续 对mysql默认数据库语言编码进行设置 第一个是西文编码 第二个是多字节的通用utf8编码 都不是我们通用的编码 这里选择第三个 然后在CharacterSet那里选择或填入 gbk 当然也可以用 gb2312 按 Next 继续 MySQL的安装与配置 续 选择是否将mysql安装为windows服务 还可以指定ServiceName 服务标识名称 是否将mysql的bin目录加入到WindowsPATH 我们这里全选 ServiceName不变 按 Next 继续 询问是否要修改默认root用户 超级管理 的密码 默认为空最后 CreateAnAnonymousAccount 新建一个匿名用户 匿名用户可以连接数据库 不能操作数据 包括查询 一般就不用选了 设置完毕 按 Next 继续 MySQL的安装与配置 续 确认设置无误 如果有误 按 Back 返回检查 按 Execute 使设置生效 MySQL的安装与配置 续 设置完毕 按 Finish 结束mysql的安装与配置 MySQL的安装与配置 续 MySQL的安装与配置 续 检验MySQL是否安装成功的方法 进入cmd输入 mysql uroot p命令 并在随后的提示中输入密码如果出现以下提示则表示MySQL安装成功 MySQL工具 MySQL命令行实用程序MySQL管理器MySQL查询浏览器 MySQL命令行实用程序 进入方法 mysql uroot p退出方法 quit命令以 或 g结束 即仅按enter不执行命令mysql命令不区分大小写一条命令可以分为多行输入 直到遇到分号为止一行可以输入多条命令 命令之间用分号隔开 MySQL管理器 下载地址 下载该文件即可 MySQL管理器 启动MySQL管理器 MySQL管理器 登录 填写数据库服务器地址 用户名和密码 MySQL管理器 登录后的界面 MySQL查询浏览器 登录后的界面 创建数据库创建数据库可以使用CREATEDATABASE语句 该语句的基本格式如下 CREATE DATABASE SCHEMA IFNOTEXISTS db name说明 中内容为可选项 DATABASE与SCHEMA同义 db name 要创建的数据库的名称 在文件系统中 MySQL的数据存储区将以目录方式表示MySQL数据库 因此 命令中的数据库名字必须符合操作系统文件夹命名规则 值得注意的是 在MySQL中是不区分大小写的 IFNOTEXISTS 在创建数据库前进行判断 只有该数据库目前尚不存在时才执行CREATEDATABASE操作 用此选项可以避免出现数据库已经存在而再新建的错误 创建数据库 例子 创建学生管理系统的数据库 名为PXSCJ 打开 开始 程序 MySQL MySQLServer5 1 MySQLCommandLineClient 菜单项 进入MySQL客户端 在客户端窗口输入密码 在命令提示行输入以下语句 CREATEDATABASEPXSCJ 创建了数据库之后使用USE命令可指定当前数据库 语法格式如下 USEdb name 创建数据库 已经创建的数据库如要删除 使用DROPDATABASE命令 语法格式如下 DROPDATABASE IFEXISTS db name其中 db name是要删除的数据库名 可以使用IFEXISTS子句以避免删除不存在的数据库时出现的MySQL错误信息 示例如下 删除数据库 创建表使用CREATETABLE语句 基本格式如下 CREATE TEMPORARY TABLE IFNOTEXISTS tbl name TEMPORARY 该关键字表示用CREATE命令新建的表为临时表 不加该关键字创建的表通常称为持久表 在数据库中持久表一旦创建将一直存在 多个用户或者多个应用程序可以同时使用持久表 有时需要临时存放数据 例如 临时存储复杂的SELECT语句的结果 此后 可能要重复地使用这个结果 但这个结果又不需要永久保存 这时 可以使用临时表 用户可以像操作持久表一样操作临时表 只不过临时表的生命周期较短 而且只能对创建它的用户可见 当断开与该数据库的连接时 MySQL会自动删除它们 创建表 IFNOTEXISTS 建表前加上一个判断 只有该表目前尚不存在时才执行CREATETABLE操作 用此选项可以避免出现表已经存在无法再新建的错误 列选项 列选项主要有以下几种 NULL或NOTNULL 表示一列是否允许为空 NULL表示可以为空 NOTNULL表示不可以为空 如果不指定 则默认为NULL DEFAULTdefault value 为列指定默认值 默认值default value必须为一个常量 AUTO INCREMENT 设置自增属性 只有整型列才能设置此属性 当插入NULL值或0到一个AUTO INCREMENT列中时 列被设置为value 1 value是此前表中该列的最大值 AUTO INCREMENT顺序从1开始 每个表只能有一个AUTO INCREMENT列 并且它必须被索引 UNIQUEKEY PRIMARYKEY UNIQUEKEY和PRIMARYKEY都表示字段中的值是唯一的 PRIMARYKEY表示设置为主键 一个表只能定义一个主键 主键必须为NOTNULL COMMENT string 对于列的描述 string是描述的内容 创建表 表选项 在定义列选项的时候 可以将某列定义为PRIMARYKEY 但是当主键是由多个列组成的多列索引时 定义列时无法定义此主键 这时就必须在语句最后加上一个PRIMARYKEY col name 子句定义的表选项 另外 表选项中还可以定义索引和外键 例 使用命令行方式在PXSCJ数据库中创建学生管理系统中的三个表XSB KCB和CJB 创建XSB表使用如下语句 USEPXSCJ CREATETABLEXSB 学号CHAR 6 NOTNULLPRIMARYKEY 姓名CHAR 8 NOTNULL 性别TINYINT 1 NULLDEFAULT1 出生时间DATENULL 专业CHAR 12 NULL 总学分INT 4 NULLDEFAULT0 备注TEXTNULL 创建表 创建KCB表使用如下语句 USEPXSCJ CREATETABLEKCB 课程号CHAR 3 NOTNULLPRIMARYKEY 课程名CHAR 16 NOTNULL 开课学期TINYINT 1 NULLDEFAULT1 学时TINYINT 1 NULL 学分TINYINT 1 NOTNULL 创建CJB表使用如下语句 USEPXSCJ CREATETABLECJB 学号CHAR 6 NOTNULL 课程号CHAR 3 NOTNULL 成绩INT 4 NULL PRIMARYKEY 学号 课程号 创建表 ALTERTABLE用于更改原有的表结构 例如 可以增加或删减列 创建或取消索引 更改原有列的类型 重新命名列或表 还可以更改表的描述和表的类型 ALTERTABLE语句的基本格式如下 ALTERTABLEtable nameADD 添加列 ALTER SETDEFAULTdefault value DROPDEFAULT 修改默认值 CHANGE 对列重命名 MODIFY 修改列类型 DROP 删除列 RENAME 重命名该表 其他 修改表 其中 table name为要修改表的表名 ALTERTABLE语句中的修改子句可以包含以下几类 ADD子句 向表中增加新列 例如 在表t1中增加新的一列a ALTERTABLEt1ADDCOLUMNaTINYINTNULL ALTER子句 修改表中指定列的默认值 CHANGE子句 修改列的名称 重命名时 需给定旧列名 新列名和列当前的类型 例如 要把一个INTEGER列的名称从a变更到b ALTERTABLEt1CHANGEabINTEGER MODIFY子句 修改指定列的类型 例如 把一个列的数据类型改为BIGINT ALTERTABLEt1MODIFYbBIGINTNOTNULL 注意 若表中该列所存数据的数据类型与将要修改的列的类型冲突 则发生错误 例如 原来CHAR类型的列要修改成INT类型 而原来列值中有字符型数据 a 则无法修改 DROP子句 从表中删除列或约束 RENAME子句 修改该表的表名 例如 将表a改名为b ALTERTABLEaRENAMEb 修改表 删除一个表可以使用DROPTABLE语句 语法格式如下 DROP TEMPORARY TABLE IFEXISTS tbl name tbl name 例如 删除XSB表可以使用如下语句 USEPXSCJ DROPTABLEXSB 删除表 SELECT语句可以从一个或多个表中选取特定的行和列 结果通常是生成一个临时表 在执行过程中系统根据用户的要求从数据库中选出匹配的行和列 并将结果存放到临时的表中 SELECT语句的语法格式如下 SELECT ALL DISTINCT select expr FROMtable1 table2 FROM子句 WHEREwhere definition WHERE子句 GROUPBY col name expr position ASC DESC GROUPBY子句 HAVINGwhere definition HAVING子句 ORDERBY col name expr position ASC DESC ORDERBY子句 LIMIT offset row count LIMIT子句 查询表数据 创建存储过程存储过程可以由声明式SQL语句 如CREATE UPDATE和SELECT等语句 和过程式SQL语句 如IF THEN ELSE语句 组成 创建存储过程使用CREATEPROCEDURE语句 语法格式如下 CREATEPROCEDUREsp name IN OUT INOUT param nametype 存储过程 是存储过程的主体部分 也叫做存储过程体 它包含了在过程调用的时候必须执行的语句 这个部分总是以BEGIN开始 以END结束 当然 当存储过程体中只有一个SQL语句时可以省略BEGIN END标志 另外 BEGIN END复合语句还可以嵌套使用 在开始创建存储过程之前 先介绍一个很实用的命令 DELIMITER命令 在MySQL中 服务器处理语句的时候是以分号为结束标志的 但是在创建存储过程的时候 存储过程体中可能包含多个SQL语句 每个SQL语句都是以分号为结尾的 这时服务器处理程序遇到第一个分号的时候就会认为程序结束 这肯定是不行的 所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号 例如 DELIMITER 执行完这条命令后 程序结束的标志就换成两个美元符 了 要想恢复使用分号 作为结束符 运行下面命令即可 DELIMITER 存储过程 存储过程体中可以包含所有类型的SQL语句 另外还可以包含以下内容 1 局部变量使用DECLARE语句声明局部变量 例如 声明一个整型变量和两个字符变量 DECLAREnumINT 4 DECLAREstr1 str2VARCHAR 6 说明 局部变量只能在BEGIN END语句块中声明 2 使用SET语句赋值要给局部变量赋值 可以使用SET语句 例如 SETnum 1 str1 hello 说明 这条语句无法单独执行 只能在存储过程和存储函数中使用 3 SELECT INTO语句使用SELECT INTO语句可以把选定的列值直接存储到变量中 因此 返回的结果只能有一行 例如 SELECT姓名 专业INTOname projectFROMXSBWHERE学号 081101 存储过程 4 流程控制语句在MySQL中 常见的过程式SQL语句可以用在一个存储过程体中 例如 IF语句 CASE语句 WHILE语句等 IF语句 IF THEN ELSE语句可根据不同的条件执行不同的操作 语法格式为 IFsearch conditionTHENstatement list ELSEIFsearch conditionTHENstatement list ELSEstatement list ENDIF说明 search condition是判断的条件 statement list中包含一个或多个SQL语句 当search condition的条件为真时 就执行相应的SQL语句 CASE语句 语法格式为 CASEcase valueWHENwhen valueTHENstatement list WHENwhen valueTHENstatement list ELSEstatement list ENDCASE 存储过程 或者 CASEWHENsearch conditionTHENstatement list WHENsearch conditionTHENstatement list ELSEstatement list ENDCASE第一种格式中case value是要被判断的值或表达式 接下来是一系列的WHEN THEN块 每一块的when value参数指定要与case value比较的值 如果为真 就执行statement list中的SQL语句 如果前面的每一块都不匹配就执行ELSE块指定的语句 CASE语句最后以ENDCASE结束 第二种格式中CASE关键字后面没有参数 在WHEN THEN块中 search condition指定了一个比较表达式 表达式为真时执行THEN后面的语句 与第一种格式相比 这种格式能够实现更为复杂的条件判断 使用起来更方便 存储过程 WHILE语句 语法格式为 WHILEsearch conditionDOstatement listENDWHILE说明 语句首先判断search condition是否为真 为真则执行statement list中的语句 然后再次进行判断 为真则继续循环 不为真则结束循环 例 创建一个存储过程 实现的功能是删除一个特定学生的信息 DELIMITER CREATEPROCEDUREDELETE STUDENT INXHCHAR 6 BEGINDELETEFROMXSBWHERE学号 XH END DELIMITER 存储过程 例 创建一个存储过程 有两个输入参数XH和KCM 要求当某学生某门课程的成绩小于60分时将备注修改为 有课程没过 大于等于60分时将该成绩修改为60分 DELIMITER CREATEPROCEDUREDO UPDATE INXHCHAR 6 INKCMCHAR 16 BEGINDECLAREKCHCHAR 3 DECLARECJTINYINT SELECT课程号INTOKCHFROMKCBWHERE课程名 KCM SELECT成绩INTOCJFROMCJBWHERE学号 XHAND课程号 KCH IFCJ 60THENUPDATEXSBSET备注 有课程没过 WHERE学号 XH ELSEUPDATECJBSET成绩 60WHERE学号 XHAND课程号 KCH ENDIF END DELIMITER 存储过程 2 调用存储过程存储过程创建完后 可以使用CALL语句来调用 语法格式如下 CALLsp name 参数 例 创建存储过程 实现查询XSB表中学生人数的功能 该存储过程不带参数 CREATEPROCEDUREDO QUERY SELECTCOUNT FROMXSBORDERBY学号 调用该存储过程 CALLDO QUERY 存储过程 3 删除存储过程存储过程创建后需要删除时使用DROPPROCEDURE语句 例如 删除存储过程DELETE STUDENT可以使用如下语句 DROPPROCEDUREIFEXISTSDELETE STUDENT 创建触发器使用CREATETRIGGER语句 语法格式如下 CREATETRIGGERtrigger nametrigger timetrigger eventONtbl nameFOREACHROWtrigger stmttrigger name 触发器的名称 trigger time 触发器触发的时刻 有两个选项AFTER和BEFORE 以表示触发器是在激活它的语句之前还是之后触发 trigger event 触发事件 指明激活触发程序的语句的类型 trigger event可以是INSERT UPDATE和DELETE 分别表示插入 修改 删除数据时激活触发器 tbl name 与触发器相关的表名 在该表上发生触发事件才会激活触发器 同一个表不能拥有两个具有相同触发时刻和事件的触发器 trigger stmt 触发器动作 包含触发器激活时将要执行的语句 如果要执行多个语句 可使用BEGIN END复合语句结构 这样就能使用存储过程中允许的相同语句 触发器 例 创建一个触发器 当删除表XSB中某个学生的信息时 同时将CJB表中与该学生有关的数据全部删除 DELIMITER CREATETRIGGERXS DELETEAFTERDELETEONXSBFOREACHROWBEGINDELETEFROMCJBWHERE学号 OLD 学号 END DELIMITER 现在验证一下触发器的功能 DELETEFROMXSBWHERE学号 081101 使用SELECT语句查看CJB表中的情况 SELECT FROMCJB 删除触发器也是使用DROP语句 例如 DROPTRIGGERXS DELETE 触发器 创建数据库和表1 创建数据库以创建PXSCJ2数据库为例 创建数据库的方法如下 1 打开 开始 程序 MySQL MySQLAdministrator 菜单项 ServerHost为 localhost Username为 root 输入密码 单击 OK 按钮 使用界面工具操作MySQL 2 在MySQLAdministrator窗口中展开Catalogs选项栏 出现如图所示的数据库列表 在任意一个数据库名上右击鼠标 选择 CreateNewSchema 子选项 使用界面工具操作MySQL 3 出现如图所示的界面 输入需要创建的数据库名PXSCJ2 单击 OK 按钮 这样数据库PXSCJ2就创建成功了 使用界面工具操作MySQL 2 创建表若要在数据库PXSCJ2中创建XSB表 步骤如下 1 在MySQLAdministrator窗口中展开Catalogs选项栏 在数据库PXSCJ2上右击鼠标 在弹出的快捷单上选择 CreateNewTable 选项 或者直接单击 CreateTable 按钮 2 在弹出的 MySQLTableEditor 窗口中输入表名XSB 在 ColumnsandIndices 子选框中输入表的各列及数据类型 如图所示 单击 ApplyChanges 按钮 在弹出的 ConfirmTableEditor 对话框中单击 Execute 按钮 即可成功创建表XSB 使用界面工具操作MySQL 如果要在数据库中删除表 步骤如下 1 在MySQLAdministrator窗口中展开Catalogs选项栏 选择数据库 如PXSCJ2 在快捷选单上右击要删除的表 选择 DropTable 选项 2 在弹出对话框中单击 DropTable 按钮 即删除了表 如果要修改表 可以在步骤 1 中选择 EditTable 选项 在弹出的窗口中进行修改 修改后单击 ApplyChanges 按钮即可完成 使用界面工具操作MySQL 启动MySQLAdministrator 在Catalogs选项栏中选中数据库PXSCJ2 再选中需要操作的表 XSB 右击 在弹出的快捷选单中选择 EditTableData 选项 进入操作所选择的表数据窗口 如图所示 添加记录的方法是 单击图中下方工具栏的 Edit 选项 双击需要输入的地方 将数据写入 每输入一个值 按回车键 每输入完一行值 将光标移到下