MySQL数据库应用技术及实战-全套课件

初识MySQL

1.初识MySQL.pptx2.MySQL数据管理.pptx3.使用DQL命令查询数据(一).pptx4.使用DQL命令查询数据(二).pptx5.MySQL事务、索引、数据恢复和备份.pptx6.数据库设计.pptx7.银行ATM存取款机系统.pptx线上线下平台预习2/45本课目标学完本次课程后，你能够：了解MySQL数据库学会安装MySQL学会使用SQLyog数据库管理工具了解常用的数据库命令熟悉创建数据库表的语句熟悉常见的数据列属性、类型和索引3/45为什么学习数据库岗位技能需求现在的世界，得数据者得天下存储数据的方法程序、网站中大量数据需长久保存4/45什么是数据库数据库（Database,简称DB）概念长期存放在计算机内，有组织、可共享的大量数据的集合，是一个数据“仓库”作用保存、管理数据5/45数据库总览关系型数据库（SQL）MySQL、Oracle、SQLServer、SQLite、DB2非关系型数据库（NOSQL）Redis、MongoDB6/45什么是DBMS数据库管理系统(DatabaseManagementSystem）数据管理软件，科学组织和存储数据、高效地获取和维护数据

7/45MySQL简介概念是现流行的开源、免费的关系型数据库特点免费、开源数据库

小巧、功能齐全使用便捷可运行于Windows或Linux操作系统可适用于中小型甚至大型网站应用8/45MySQL的运行机制支持接口标准C的API、JDBC、CDBC、.NET、PHP、Python、Perl、Ruby、Cobol企业管理服务和工具备份与恢复安全复制群集分区管理事例管理数据模板管理工作台查询浏览合并工具连接池验证与授权–线程–连接限制–内存与缓存管理SQL接口数据管理语言和数据定义语言、存储过程、视图、触发器等解析器查询\事务对象优先级查询优化器访问路径统计缓存和缓冲池和具体引擎的缓存和缓冲池文件系统新技术文件；网络文件系统存储区域网络和网路附加存储文件和日志Redo、Undo、Data、Index、BinaryError、QueryandSlow可插式存储引擎内存\索引和存储管理MySQLServer9/45在Windows操作系统下安装2-1下载MySQLMySQL

5.5.40(Windows版)下载地址：/downloads/数据目录不要在系统盘,以免卸载时删除数据包启用安装向导，避免繁琐的my.ini配置文件数据库语言编码设置注意10/45在Windows操作系统下安装2-2安装关键步骤端口设置3306（默认）编码设置utf8密码设置11/45安装后my.ini配置path开启数据库服务12/45SQLyog管理工具可手动操作、管理MySQL数据库的软件工具特点易用简洁图形化13/45SQLyog管理工具创建课程数据表（subject）并添加数据使用SQLyog连接数据库创建MySchool数据库新建数据表（

subject）添加数据列

SubjectNo(int(11),PK)

SubjectName(varchar50)

ClassHour(int(4))

GradeID(int(4))添加数据演示：使用SQLyog创建数据表14/45练习1：SQLyog创建数据库需求说明使用SQLyog管理工具连接本地MySQL数据库新建MySQL数据库数据库名称MySQL新建数据库表（grade）字段GradeID:int(11)PrimaryKey(pk)GradeName:varchar(50)15/45

连接数据库打开MySQL命令窗口在DOS命令行窗口(cmd.exe)在窗口中进入安装目录\mysql\bin可设置环境变量连接数据库语句mysql-h服务器主机地址–u用户名-p用户密码演示示例1：命令行连接数据库16/45结构化查询语句SQL结构化查询语句分类名称解释命令DDL（数据定义语言)定义和管理数据对象，如数据库，数据表等CREATE、DROP、ALTERDML（数据操作语言）用于操作数据库对象中所包含的数据INSERT、UPDATE、DELETEDQL（数据查询语言）用于查询数据库数据SELECTDCL（数据控制语言）用来管理数据库的语言，包括管理权限及数据更改GRANT、COMMIT、ROLLBACK17/45命令行操作数据库创建数据库删除数据库查看数据库使用数据库CREATEDATABASE[IFNOTEXISTS]数据库名;DORPDATABASE[IFEXISTS]数据库名;SHOWDATABASES;USE数据库名;演示示例2：命令行操作数据库18/45对比工具操作数据库建库学习方法对照SQLyog工具自动生成的语句学习固定语法中的单词需要记忆19/45工具建表建表显示表结构desc表名显示表创建语句showcreatetable表名20/45创建数据表属于DDL的一种

CREATETABLE[IFNOTEXISTS]`表名`

(

`字段名1`列类型[属性][索引][注释]

,

`字段名2`列类型[属性][索引]

[注释]

,

……

`字段名n`列类型[属性][索引]

[注释]

)[表类型][表字符集][注释]

;

反引号（可选、区别于单引号）反引号用于区别MySQL保留字与普通字符而引入的演示示例3：数据表创建语法语法注意21/45列类型表列类型设置

CREATETABLE[IFNOTEXISTS]`表名`

(

`字段名1`列类型[属性][索引][注释]

, `字段名2`列类型[属性][索引]

[注释]

, ……

`字段名n`列类型[属性][索引]

[注释]

)[表类型][表字符集][注释]

;22/45语法数据值和列类型列类型规定数据库中该列存放的数据类型分为数值类型字符串类型日期和时间型数值类型NULL值23/45列类型分类4-1数值类型类型说明取值范围存储需求tinyint非常小的数据有符值：-27~27-1无符号值：0~28-11字节smallint较小的数据有符值：-215

~215-1无符号值：0~216-12字节mediumint中等大小的数据有符值：-223

~223-1无符号值：0~224-13字节int标准整数有符值：-231

~231-1无符号值：0~232-14字节bigint较大的整数有符值：-263

~263-1无符号值：0~264-18字节float单精度浮点数±1.1754351e-384字节double双精度浮点数±2.2250738585072014e-3088字节decimal字符串形式的浮点数decimal(m,d)m个字节24/45列类型分类4-2字符串类型类型说明最大长度char[(M)]固定长字符串，检索快但费空间，0<=M<=255M字符varchar[(M)]可变字符串0<=M<=65535变长度tinytext微型文本串28–1字节text文本串216–1字节25/45列类型分类4-3日期和时间型数值类型类型说明取值范围DATEYYYY-MM-DD，日期格式1000-01-01~9999-12-31TIMEHh:mm:ss，时间格式-838:59:59~838:59:59DATETIMEYY-MM-DDhh:mm:ss1000-01-0100:00:00至9999-12-3123:59:59TIMESTAMPYYYYMMDDhhmmss格式表示的时间戳197010101000000~2037年的某个时刻YEARYYYY格式的年份值1901~215526/45列类型分类4-4NULL值理解为“没有值”或“未知值”不要用NULL进行算术运算，结果仍为NULL选择数据类型整数和浮点日期类型char和varchar27/45数据字段属性表列类型设置CREATETABLE[IFNOTEXISTS]`表名`

(

`字段名1`列类型[属性][索引][注释]

, `字段名2`列类型[属性][索引]

[注释]

, ……

`字段名n`列类型[属性][索引]

[注释]

)[表类型][表字符集][注释]

;28/45语法数据字段属性2-1UNSIGNED无符号的声明该数据列不允许负数ZEROFILL0填充的不足位数的用0来填充，如

int(3),5则为005AUTO_INCREMENT自动增长的，每添加一条数据，自动在上一个记录数上加1通常用于设置主键，且为整数类型可定义起始值和步长29/45数据字段属性2-2NULL和NOTNULL默认为NULL,即没有插入该列的数值如果设置为NOTNULL，则该列必须有值DEFAULT默认的用于设置默认值例如，性别字段，默认为“男”，否则为“女”；若无指定该列的值，则默认为“男”的值30/45数据字段注释表列类型注释

CREATETABLE[IFNOTEXISTS]`表名`

(

`字段名1`列类型[属性][索引][注释]

, `字段名2`列类型[属性][索引]

[注释]

, ……

`字段名n`列类型[属性][索引]

[注释]

)[表类型][表字符集][注释]

;

CREATETABLE[IFNOTEXISTS]`test`

(`id`int(11)UNSIGNEDCOMMENT‘编码号’)COMMENT=‘测试表’;示例31/45语法练习2：创建数据表student需求说明使用语句新建student表具体设计如下32/45数据表的类型3-1表列类型设置CREATETABLE[IFNOTEXISTS]`表名`

(

`字段名1`列类型[属性][索引][注释]

, `字段名2`列类型[属性][索引]

[注释]

, ……

`字段名n`列类型[属性][索引]

[注释]

)[表类型][表字符集][注释]

;演示示例4：设置数据表的类型33/45语法数据表的类型3-2设置数据表的类型CREATETABLE表名( #省略一些代码)ENGINE=MyISAM或者CREATETABLE表名( #省略一些代码)ENGINE=InnoDB

MySQL注释：

1、#……

单行注释

2、/*……*/

多行注释示例34/45数据表的类型3-3MySQL的数据表的类型MyISAM、InnoDB、HEAP、BOB、CSV等常见的MyISAM与InnoDB类型名称MyISAMInnoDB事务处理不支持支持数据行锁定不支持支持外键约束不支持支持全文索引支持不支持表空间大小较小较大,约2倍使用MyISAM:节约空间及相应速度使用InnoDB:安全性，事务处理及多用户操作数据表技巧35/45设置数据表字符集表列类型设置

CREATETABLE[IFNOTEXISTS]`表名`

(

`字段名1`列类型[属性][索引][注释]

, `字段名2`列类型[属性][索引]

[注释]

, ……

`字段名n`列类型[属性][索引]

[注释]

)[表类型][表字符集][注释]

;36/45语法设置数据表字符集可为数据库、数据表、数据列设定不同的字符集设定方法创建时通过命令来设置，如

如无设定，则根据MySQL数据库配置文件my.ini(Windows系统）中的参数设定

如：character-set-sever=utf8

CREATETABLE表名( #省略一些代码)CHARSET=utf8;37/45语法练习3：新建subject表需求说明使用语句新建subject表设置数据表为MyISAM类型具体设计如下38/45创建表索引

CREATETABLE[IFNOTEXISTS]`表名`

(

`字段名1`列类型[属性

][索引][注释]

, `字段名2`列类型[属性][索引]

[注释]

, ……

`字段名n`列类型[属性][索引]

[注释]

)[表类型][表字符集][注释]

;39/45语法数据表的存储位置MySQL数据表以文件方式存放在磁盘中包括表文件、数据文件以及数据库的选项文件位置：MySQL安装目录\data下存放数据表。目录名对应数据库名，该目录下文件名对应数据表注意InnoDB类型数据表只有一个*.frm文件，数据文件为上一级目录的ibdata1文件MyISAM类型数据表对应三个文件：*.frm--表结构定义文件*.MYD--数据文件*.MYI--索引文件40/45修改数据表修改表（ALTERTABLE）修改表名添加字段修改字段删除字段

ALTERTABLE旧表名RENAMEAS新表名

ALTERTABLE表名ADD字段名

列类型[属性]

ALTERTABLE表名MODIFY字段名

列类型[属性]

ALTERTABLE表名DROP字段名

ALTERTABLE表名

CHANGE旧字段名新字段名

列类型[属性]演示示例5：修改数据表41/45语法删除数据表IFEXISTS为可选，判断是否存在该数据表如删除不存在的数据表会抛出错误

DROPTABLE[IFEXISTS]表名语法演示示例6：删除数据表42/45总结简单说说DBMS与DB的关系？MySQL数据库的特点是什么？如何使用SQLyog工具来创建数据库表？如何创建数据表？如何修改和删除数据表？43/45问题及作业集中问题&课后作业44/45扫我有更多精彩课程呦45/45

MySQL数据管理

线上线下平台预习47/19本课目标会使用MySQL语句添加数据会使用MySQL语句修改数据会使用MySQL语句删除数据48/19外键管理外键作用创建外键建表时指定外键约束建表后修改删除外键删除外键、删除表49/19演示示例1：外键管理数据库数据管理数据库意义数据存储数据管理管理数据库数据方法通过SQLyog等管理工具管理数据库数据通过DML语句管理数据库数据50/19DML语言DML（数据操作语言）用于操作数据库对象中所包含的数据包括INSERT

(添加数据语句

)UPDATE(更新数据语句)DELETE

(删除数据语句

)51/19添加数据INSERT命令字段或值之间用英文逗号隔开“字段1,字段2…”该部分可省略，但添加的值务必与表结构数据列顺序相对应,且数量一致可同时插入多条数据，values后用英文逗号隔开INSERTINTO表名[(字段1,字段2,字段3,…)]VALUES('值1','值2','值3',

…)52/19注意语法添加grade表数据使用INSERT语句向数据表grade添加数据向数据列GradeName中添加如下数据大一、大二、大三、大四53/19演示示例2：添加数据表grade数据添加student表数据使用INSERT语句向数据表student添加数据学号姓名性别年级手机号地址邮箱身份证1011郭靖男113500000001北京海淀区中关村大街1号guojing@4503231986121110001012李文才男213500000002河南洛阳liwencai@4503231981123110001013李梅女313500000015上海卢湾区limei@45032319861231100054/19演示示例3：添加数据表student数据练习1：添加数据需求说明使用INSERT语句为课程表subject添加数据SubjectNo

课程编号SubjectName

课程名称

ClassHour

学时

GradeID

年级编号1高等数学-112012高等数学-211023高等数学-310034高等数学-4130455/19修改数据UPDATE命令column_name为要更改的数据列value为修改后的数据，可以为变量、具体值、表达式或者嵌套的SELECT结果condition为筛选条件，如不指定则修改该表的所有列数据UPDATE表名

SET

column_name=value

[,column_name2=value2,

….]

[WHEREcondition];56/19注意语法WHERE条件子句简单理解为有条件地从表中筛选数据WHERE中的运算符运算符含义范例结果=等于5=6false<>或!=不等于5!=6true>大于5>6false<小于5<6true>=大于等于5>=6false<=小于等于5<=6trueBETWEEN在某个范围之间BETWEEN5AND10-AND并且5>1AND1>2falseOR或5>1OR1>2true57/19修改student表数据使用UPDATE语句修改student表数据修改学号（StudentNo）为1013的学生记录邮箱修改为student1013@密码（LoginPwd）修改为00000058/19演示示例4：修改数据练习2：修改数据需求说明将数据表subject中ClassHour大于110且GradeID为1的课时都减少1059/19删除数据2-1DELETE命令condition为筛选条件，如不指定则删除该表的所有列数据DELETEFROM

表名[WHEREcondition];60/19注意语法删除数据2-2TRUNCATE命令用于完全清空表数据，但表结构、索引、约束等不变区别于DELETE命令相同都能删除数据、不删除表结构，但TRUNCATE速度更快不同使用TRUNCATETABLE重新设置AUTO_INCREMENT计数器使用TRUNCATETABLE不会对事务有影响TRUNCATE[TABLE]table_name61/19注意语法演示示例5：删除数据总结什么是DML语句？INSERT语句可以一次插入多条数据吗？UPDATE语句可以同时修改多个数据列吗？UPDATE语句和DELETE语句必须与WHERE条件语句配合使用吗？62/19问题及作业集中问题&课后作业63/19扫我有更多精彩课程呦

使用DQL命令查询数据(一)

线上线下平台预习66/35本课目标学完本次课程后，你能够：掌握MySQL删除数据掌握MySQL查询语句掌握模糊查询理解连接查询原理掌握内连接查询掌握左外连接查询理解自连接67/35课程项目分析案例：MySchool数据库课程表成绩表学生信息表年级表68/35数据表设计了解表之间的业务逻辑关系69/35DQL语言DQL(DataQueryLanguage，数据查询语言)查询数据库数据，如SELECT语句简单的单表查询或多表的复杂查询和嵌套查询数据库语言中最核心、最重要的语句使用频率最高的语句70/35SELECT语法SELECT[ALL|DISTINCT]{*|table.*|[table.field1[asalias1][,

table.field2[asalias2]][,

…]]}FROMtable_name[astable_alias][

left|out|innerjointable_name2

]#联合查询 [WHERE…] #指定结果需满足的条件 [GROUPBY…] #指定结果按照哪几个字段来分组 [

HAVING…] #过滤分组的记录必须满足的次要条件 [ORDERBY…] #指定查询记录按一个或者多个条件排序 [LIMIT{[offset,]row_count|row_countOFFSEToffset}];#指定查询的记录从哪条至哪条[]括号代表可选的；{}括号代表必须的；#

MySQL语句中的注释符，也可以用/*该处为注释*/语法71/35SELECTstudent.StudentNo,StudentName,StudentResultFROMstudent,result;指定查询字段查询表中所有的数据列结果，采用“*”符号可指定查询的结果数据列如只查询student表中的学号、姓名、电话如区分连接查询时两个表有同名的字段指定该字段属于哪个表效率低，不推荐select*fromstudent;SELECTStudentNo,StudentName,PhoneFROMstudent;72/35AS子句AS子句作用可给数据列取一个新别名可给表取一个新别名可把经计算或总结的结果用另外一个新名称来代替AS子句用法SELECTStudentNoAS“学号”FROM

student;SELECT

a.StudentNoFROM

studentASa;SELECTPhone+1

AS

TelFROM

student;AS也可省略不写注意73/35DISTINCT关键字的使用作用去掉SELECT查询返回的记录结果中重复的记录（所有返回列的值都相同），只返回一条#查询成绩表中的所包含的课程ID

SELECTDISTINCTSubjectNoFROMresult;示例SELECTDISTINCT字段名1,字段名2...

FROM表名语法ALL关键字是默认的，返回所有的记录，与之相反注意演示示例1：简单查询语句74/35使用表达式的列2-1表达式一般由文本值、列值、NULL、函数和操作符等组成应用场景SELECT语句返回结果列中使用SELECT语句的ORDERBY、HAVING等子句中使用DML语句中的where条件语句中使用表达式75/35使用表达式的列2-2在SQL语句中使用表达式返回的列中使用，如

避免SQL返回结果中包含“.”,“*”和括号等干扰开发语言程序，如SELECTversion(),100*3#返回MySQL版本和计算结果SELECTSubjectName“课程名称”,ClassHour+10AS“新学时”FROMsubject;#给返回结果中的课时都加10个课时SELECTversion()asMySQL_V,123.44*100ASEXPRESSION;#返回结果不会与后台开发程序发生混淆演示示例2：使用表达式的列76/35练习1：查询课程表数据需求说明查询课程表（subject）的所有记录，返回数据要求返回字段名称使用别称返回课程名称

(SujectName)总课时(SubjectHour)返回10天上完课程的均课时（ClassHour/10）77/35SELECT语法SELECT[ALL|DISTINCT]{*|table.*|[table.field1[asalias1][,table.field2[asalias2]][,…]]}FROMtable_name[astable_alias][left|out|innerjointable_name2]#联合查询

[WHERE…] #指定结果需满足的条件 [GROUPBY…] #指定结果按照哪几个字段来分组 [HAVING…] #过滤分组的记录必须满足的次要条件 [ORDERBY…] #指定查询记录按一个或者多个条件排序 [LIMIT{[offset,]row_count|row_countOFFSEToffset}];#指定查询的记录从哪条至哪条[]括号代表可选的；{}括号代表必须的；#

MySQL语句中的注释符，也可以用/*该处为注释*/78/35语法where条件语句用于检索数据表中符合条件的记录搜索条件可由一个或多个逻辑表达式组成，结果一般为真或假搜索条件的组成逻辑操作符比较操作符查询在80-90分之间的所有成绩记录问题演示示例3：WHERE条件检索79/35逻辑操作符操作符名称语法描述AND或&&aANDb或a&&b逻辑与，同时为真，结果才为真OR或||aORb或a||b逻辑或，只要一个为真，则结果为真NOT或！NOTa或!a逻辑非，若操作数为假，结果则为真80/35比较操作符操作符名称语法描述ISNULLaISNULL若操作符为NULL，则结果为真ISNOTNULLaISNOTNULL若操作符不为NULL，则结果为真BETWEENaBETWEENbANDc若a范围在b与c之间则结果为真LIKEaLIKEbSQL模式匹配，若a匹配b，则结果为真INaIN(a1,a2,a3,….)若a等于a1，a2…中的某一个，则结果为真1、数值数据类型的记录之间才能进行算术运算2、相同数据类型的数据之间才能进行比较注意81/35BETWEENAND范围查询根据一个范围值来检索等同于>=和<=联合使用SELECT字段列1，字段2,…FROM表名WHERE字段x

BETWEEN值1AND值2#查询课程表中课时在110和120之间的所有记录SELECT*FROMsubjectWHEREClassHour

BETWEEN110AND120;等同于:SELECT*FROMsubjectWHEREClassHour>=110ANDClassHour<=120;示例语法演示示例4：BETWEENAND范围查询82/35LIKE模糊查询在WHERE子句中，使用LIKE关键字进行模糊查询与“%”一起使用，表示匹配0或任意多个字符与“_”一起使用，表示匹配单个字符#查询包含“数学”的所有课程SELECT*FROMsubjectWHERESubjectNameLIKE"%数学%";#查询所有姓名为“李**”三个字的学生信息SELECTStudentNo,StudentNameFROMstudent

WHEREStudentNameLIKE"李__";示例演示示例5：LIKE模糊匹配83/35使用IN进行范围查询在WHERE子句中使用IN进行范围查询查询的字段x的值，至少与括号中的一个值相同多个值之间用英文逗号隔开SELECT字段列1，字段2,…FROM表名WHERE字段x

IN(值1,值2,值3…)SELECT*FROMsubjectwhereClassHour=100ORClassHour=110ORClassHour=120;#普通处理方式SELECT*FROMsubjectwhereClassHourIN(100,110,120);#使用IN进行查询方式，更为简洁，效率更高示例语法演示示例6：使用IN进行范围查询84/35NULL空值条件查询NULLNULL代表“无值”区别于零值0和空符串“”只能出现在定义允许为NULL的字段须使用ISNULL或ISNOTNULL比较操作符去比较演示示例7：查找地址不为空的学生信息85/35练习2：查询“李”同学成绩需求说明查询所有姓“李”的学生所有成绩86/35数据表分析案例：MySchool数据库课程表成绩表学生信息表年级表87/35表关系88/35SELECT语法SELECT[ALL|DISTINCT]{*|table.*|[table.field1[asalias1][,table.field2[asalias2]][,…]]}FROMtable_name[astable_alias][left|out|innerjointable_name2]#联合查询

[WHERE…] #指定结果需满足的条件[GROUPBY…] #指定结果按照哪几个字段来分组

[HAVING…] #过滤分组的记录必须满足的次要条件

[ORDERBY…] #指定查询记录按一个或者多个条件排序

[LIMIT{[offset,]row_count|row_countOFFSEToffset}];

#指定查询的记录从哪条至哪条[]括号代表可选的{}括号代表必须的#

MySQL语句中的注释符，也可以用/*该处为注释*/89/35连接查询（多表查询）连接查询如需要多张数据表的数据进行查询，则可通过连接运算符实现多个查询分类包括内连接(innerjoin)等值和非等值的连接查询自身连接查询外连接(outjoin)左连接（LEFTJOIN）右连接

(RIGHTJOIN)90/35内连接查询2-1INNERJOIN内连接在表中至少一个匹配时，则返回记录SELECT字段1,字段2,…

FROMtable_1INNERJOINtable_2ONtable_1.字段x=table_2.字段y;#INNERJOIN与JOIN是相同的；#如table_1中的行在table_2中没有匹配，则不返回；#要求：从subject和grade数据表查询课程名称和所属年级名称SELECTSubjectName,GradeNameFROMsubjectINNERJOINgradeONsubject.GradeID=grade.GradeID;示例语法91/35内连接查询2-2等值和非等值的连接查询与单表查询类似，都是SELECT语句把多个表放到FROM后，并用逗号隔开可使用AS关键字取别名，便于引用如无重名查询字段则可省略数据表的指定#要求：从subject和grade数据表查询课程名称和所属年级名称#非等值连接查询SELECT

SubjectName,

GradeName

FROM

subject,

grade;#等值查询SELECT

SubjectName,

GradeName

FROM

subject,

gradeWHEREsubject.GradeID

=grade.GradeID;

返回记录数为两表记录数的乘积等效于内连接示例演示示例8：等值和非等值的连接查询92/35外连接左外连接（LEFTJOIN）从左表（table_1）中返回所有的记录，即便在右（table_2）中没有匹配的行右外连接(RIGHTJOIN)从右表（table_2）中返回所有的记录，即便在左（table_1）中没有匹配的行SELECT字段1,字段2,…FROMtable_1LEFT[OUTER]JOINtable_2ONtable_1.字段x=table_2.字段y;SELECT字段1,字段2,…FROMtable_1RIGHT[OUTER]JOINtable_2ONtable_1.字段x=table_2.字段y;语法语法演示示例9：外连接查询课程表93/35不同的SQLJOIN对比JOIN对比操作符名称描述INNERJOIN

(JOIN

)如果表中有至少一个匹配，则返回行LEFTJOIN不论右表是否有匹配，都会返回左表的所有行RIGHTJOIN不论左表是否有匹配，都会返回右表的所有行94/35自连接查询

数据表与自身进行连接从一个包含栏目ID,栏目名称和父栏目ID的表中，查询父栏目名称和其子栏目名称，表结构如下：#表结构语句CREATETABLEIFNOTEXISTScategory(

categoryIdint(10)auto_incrementprimarykey,categoryNamevarchar(32)notnull,pidint(10));示例演示示例10：自连接查询95/35练习3：连接查询需求说明查询学生表（student)的学号(studentNo),姓名(StudentName)和所在年级(GradeName)分别使用左连接查询方式右连接查询方式内连接查询方式Student表插入一条数据后比较三种查询方式区别

StudentNo,StudentName,Phone,Address,Email

对应：

2000,"无名","13400000000001","上海","unname@"96/35总结查询语句的基本语法是什么？模糊查询的关键字是什么？在什么场合下需要使用连接查询？有哪几种连接查询？97/35问题及作业集中问题&课后作业98/35扫我有更多精彩课程呦99/35

使用DQL命令查询数据(二)

线上线下平台预习101/18本课目标学完本次课程后，你能够：掌握排序查询结果掌握LIMIT的使用掌握MySQL子查询掌握MySQL聚合函数掌握MySQL分组查询102/18SELECT语法SELECT[ALL|DISTINCT]{*|table.*|[table.field1[asalias1][,table.field2[asalias2]][,…]]}FROMtable_name[astable_alias][left|out|innerjointable_name2]#联合查询 [WHERE…] #指定结果需满足的条件 [GROUPBY…] #指定结果按照哪几个字段来分组 [

HAVING…] #过滤分组的记录必须满足的次要条件

[ORDERBY…] #指定查询记录按一个或者多个条件排序 [LIMIT{[offset,]row_count|row_countOFFSEToffset}];#指定查询的记录从哪条至哪条[]括号代表可选的；{}括号代表必须的；#

MySQL语句中的注释符，也可以用/*该处为注释*/103/18语法ORDERBY排序ORDERBY排序查询对SELECT语句查询得到的结果，按某些字段进行排序与DESC或ASC搭配使用，默认为ASC查询《数据库结构-1》的所有考试结果，并按成绩由高到低排列示例演示示例1：ORDERBY排序104/18SELECT语法SELECT[ALL|DISTINCT]{*|table.*|[table.field1[asalias1][,table.field2[asalias2]][,…]]}FROMtable_name[astable_alias][left|out|innerjointable_name2]#联合查询 [WHERE…] #指定结果需满足的条件 [GROUPBY…] #指定结果按照哪几个字段来分组 [HAVING…] #过滤分组的记录必须满足的次要条件

[ORDERBY…] #指定查询记录按一个或者多个条件排序

[LIMIT{[offset,]row_count|row_countOFFSEToffset}];

#指定查询的记录从哪条至哪条[]括号代表可选的；{}括号代表必须的；#

MySQL语句中的注释符，也可以用/*该处为注释*/105/18语法MySQL的LIMITLIMIT[m,]n或LIMITnOFFSETm限制SELECT返回结果的行数m制定第一个返回记录行的偏移量n制定返回记录行的最大数目SELECT*FROM`result`LIMIT5#返回前5条记录SELECT*FROM`result`LIMIT5,10#返回6-15条记录

示例注意m不指定则偏移量为0，从第一条开始返回前n条记录LIMIT常用于分页显示106/18分页查询查询所有《数据库结构-2》的考试成绩，并按照由高到低显示，同时把该成绩对应的学生的学号、姓名打印出来要求第一次查询前5条记录第二次查询6-10条记录演示示例2：分页查询107/18练习：查询《JAVA第一学年》学生信息需求说明查询《JAVA第一学年》课程成绩前10名且分数大于80的学生信息（学号，姓名，课程名，分数）108/18子查询在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名

嵌套查询可由多个子查询组成，求解的方式是由里及外子查询返回的结果一般都是集合，故而建议使用

IN关键字注意示例演示示例3：子查询109/18案例查询《C语言-1》的前5名学生成绩信息演示示例4：查询前5名110/18MySQL函数数学函数字符串函数日期和时间函数系统信息函数111/18MySQL的统计函数函数名称描述COUNT()返回满足SELECT条件的记录总和数，如SELECTCOUNT(*)…SUM()返回数字字段或表达式列作统计，返回一列的总和AVG(

)通常为数值字段或表达列作统计，返回一列的平均值MAX()可以为数值字段、字符字段或表达式列作统计，返回最大的值MIN()可以为数值字段、字符字段或表达式列作统计，返回最小的值不建议使用*，效率低112/18SELECT语法SELECT[ALL|DISTINCT]{*|table.*|[table.field1[asalias1][,table.field2[asalias2]][,…]]}FROMtable_name[astable_alias][left|out|innerjointable_name2]#联合查询 [WHERE…] #指定结果需满足的条件

[GROUPBY…] #指定结果按照哪几个字段来分组 [

HAVING…] #过滤分组的记录必须满足的次要条件

[ORDERBY…] #指定查询记录按一个或者多个条件排序 [LIMIT{[offset,]row_count|row_countOFFSEToffset}];

#指定查询的记录从哪条至哪条[]括号代表可选的；{}括号代表必须的；#

MySQL语句中的注释符，也可以用/*该处为注释*/113/18语法GROUPBY分组使用GROUPBY关键字对查询结果分组对所有的数据进行分组统计分组的依据字段可以有多个，并依次分组与HAVING结合使用，进行分组后的数据筛选按照不同的课程分组，分别算出其平均分、最高分和最低分，对于低于60分平均分的不予显示示例演示示例5：分组查询114/18总结请举例说明在什么情况下需要使用分组查询？实现分页查询的关键字是什么?MySQL中的统计函数主要有哪几个，作用是什么？115/18问题及作业集中问题&课后作业116/18扫我有更多精彩课程呦117/18

MySQL事务、索引、

数据恢复和备份

线上线下平台预习119/32本课目标学完本次课程后，你能够：掌握MySQL的事务处理方法理解MySQL的ACID原则创建数据库索引了解MySQL数据库备份和恢复的几种方法掌握mysqldump命令导出数据掌握source命令导入数据120/32MySQL的事务处理事务就是将一组SQL语句放在同一批次内去执行如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行注意MySQL事务处理只支持InnoDB和BDB数据表类型121/32事务的特性事务必须具备以下四个属性，简称ACID属性原子性（Atomicity）一致性（Consistency）隔离性（Isolation）持久性（Durability）122/61MySQL的事务实现方法SETAUTOCOMMIT使用SET语句来改变自动提交模式MySQL中默认是自动提交使用事务时应先关闭自动提交

SETAUTOCOMMIT=0;#关闭自动提交模式SETAUTOCOMMIT=1;#开启自动提交模式示例注意123/32MySQL的事务实现方法STARTTRANSACTION开始一个事务,标记事务的起始点COMMIT提交一个事务给数据库

ROLLBACK将事务回滚，数据回到本次事务的初始状态

SETAUTOCOMMIT=1;开启MySQL数据库的自动提交124/32MySQL事务处理步骤125/32模拟网上支付2-1模拟网上支付顾客A在线购买一款商品，价格为500.00元，采用网上银行转账的方式支付假如顾客A银行卡的余额为2000.00元，且向卖家B支付购买商品费用500.00元，起始卖家B的账号金额10000.00元创建数据库shop和创建表account并插入2条数据+-------+--------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+-------+--------------+------+-----+---------+----------------+|id|int(11)|NO|PRI|NULL|auto_increment||name|varchar(32)|NO||NULL|||cash|decimal(9,2)|NO||NULL||+-------+--------------+------+-----+---------+----------------+

INSERTINTO`account`(`name`,`cash`)VALUES('A',2000.00);INSERTINTO`account`(`name`,`cash`)VALUES('B',10000.00);126/32模拟网上支付2-2设置场景A账户成功减少500元，B账户应该增加500元，但一些错误导致未增加成功，这时则需返回A账户的500元，达到账户总额的平衡要求：使用事务模拟以上过程演示示例1：网上支付127/32数据库索引作用提高查询速度确保数据的唯一性可以加速表和表之间的连接，实现表与表之间的参照完整性使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间全文检索字段进行搜索优化分类主键索引（PRIMARYKEY）唯一索引（UNIQUE）常规索引（INDEX）全文索引（FULLTEXT）128/32主键索引某一个属性或属性的组合能唯一标识一条记录如：学生表（学号，姓名，班级，性别等）

，学号就是唯一标识的，可作为主键特点最常见的索引类型确保数据记录的唯一性确定特定数据记录在数据库中的位置

CREATETABLE`Grade`(`GradeID`INT(11)

AUTO_INCREMENTPRIMARYKEY,

#省略代码……

#主键索引也可在字段字义之后，如

#

PRIMARYKEY(`GradeID`))示例129/32唯一索引（UNIQUE）作用避免同一个表中某数据列中的值重复与主键索引的区别主键索引只能有一个唯一索引可有多个

CREATETABLE`Grade`( `GradeID`INT(11)AUTO_INCREMENTPRIMARYKEY, `GradeName`VARCHAR(32)NOTNULLUNIQUE

#或

UNIQUE

KEY

`GradeID`

(`GradeID`))示例130/32常规索引（INDEX）作用：快速定位特定数据注意index和key关键字都可设置常规索引应加在查找条件的字段不宜添加太多常规索引，影响数据的插入、删除和修改操作CREATETABLE`result`(

//省略一些代码

INDEX/KEY

`ind`

(`studentNo`,`subjectNo`))ALTERTABLE`result`ADDINDEX

`ind`

(`studentNo`,`subjectNo`);创建表时添加创建后追加示例131/32作用：快速定位特定数据注意只能用于MyISAM类型的数据表只能用于

CHAR、

VARCHAR、TEXT数据列类型适合大型数据集

CREATETABLE`student`(

#省略一些SQL语句FULLTEXT(`StudentName`))ENGINE=MYISAM;ALTERTABLEemployeeADDFULLTEXT(`first_name`);全文索引（FULLTEXT）示例132/32创建表时添加创建后追加管理索引创建索引创建表时添加建表后追加删除索引查看索引ALERTTABLE表名ADD索引类型（数据列名）DROPINDEX索引名ON表名ALTERTABLE表名DROPINDEX索引名ALTERTABLE表名DROPPRIMARYKEYSHOW

INDEX(或KEYS)

FROM表名语法语法语法133/32索引准则索引不是越多越好不要对经常变动的数据加索引小数据量的表建议不要加索引索引一般应加在查找条件的字段134/32给数据库表添加索引给数据库表student添加索引学号StudentNo，添加主键索引身份证IdentityCard，添加唯一索引邮箱Email，添加常规索引姓名StudentName，添加全文索引(MyISAM类型数据表)演示示例2：添加索引135/32分析SQL语句的执行性能提示：使用\G结尾能竖排显示

查询中不包含子查询连接类型,从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL包含MySQL解决查询的详细信息演示示例3：EXPLAIN语句使用EXPLAIN表名（DESC表名）EXPLAINSELECT语句语法136/32添加正确的索引在WHERE、ORDERBY子句中经常使用的字段字段的值是多个（例如性别字段则不适合）字段内容不是经常变化的经常变化的字段，添加索引反而降低性能不宜过多添加索引每添加一条索引都会占用磁盘空间137/32MySQL的备份数据库备份必要性保证重要数据不丢失数据转移MySQL数据库备份方法mysqldump备份工具数据库管理工具，如SQLyog直接拷贝数据库文件和相关配置文件138/32mysqldump数据库备份作用转储数据库搜集数据库进行备份将数据转移到另一个SQL服务器(不一定是MySQL服务器)#备份myschool数据库如：

>mysqldump-uroot-pmyschool>d:/myschool.sqlEnterPassword:*****mysqldump-h主机名–u用户名–p[options]数据库名[table1table2

table3]>path/filename.sql预存文件目录，须有该目录读写权限语法示例139/32mysqldump常用选项2-1符号名称描述--add-drop-table导出sql脚本会加上DROPTABLEIFEXISTS语句默认是打开的，可以用--skip-add-drop-table来取消--add-locks该选项会在INSERT语句中捆绑一个LOCKTABLE和UNLOCKTABLE语句好处：防止记录被再次导入时，其他用户对表进行的操作，默认是打开的-t或--no-create-info忽略不写重新创建每个转储表的CREATETABLE语句140/32提示可通过mysqldump--help查看该命名的选项mysqldump常用选项2-2符号名称描述-c或--complete-insert在每个INERT语句的列上加上字段名在数据库导入另一个数据库时非常有用-d或--no-data不写表的任何行信息。对于只想转储表的结构很有用--where"where-condition",-w"where-condition"只转储给定的WHERE条件选择的记录--opt该选项是速记；等同于指定--add-drop-tables--add-locking--create-option--disable-keys--extended-insert--lock-tables

--quick--set-charset141/32mysqldump数据库备份备份MySchool数据库的subject课程表，保存为subject.sql脚本文件要求在每个INERT语句的列上加上字段名142/32MySQL数据库的恢复方法一用SOURCE语法

/path/是一个绝对路径，并且必须是mysql运行用户有权限读取的文件SOURCE在MySQL命令行里执行方法二用mysql客户端SOURCE/path/db_name.sql;mysql–uroot–p

dbname</path/db_name.sql;都要先创建或选择数据库143/32演示示例4：数据库的备份和恢复利用SQL语句导出、导入数据输出的文件不能先存在，否则报错SELECT

*INTOOUTFILE'file_name'FROMtbl_nameLOADDATAINFILE'file_name'INTO

TABLEtbl_name[FIELDS]语法演示示例5：数据的导出和导入144/32语法导出数据导入数据练习1：数据库备份和恢复需求说明使用mysqldump命令备份myschool数据库要求在每个INSERT语句上加上字段名导入备份的myschool数据库要求使用SOURCE命令和mysql命令两种方式恢复数据145/32练习2：查询综合练习查询密码长度不足6位的学员编号、姓名、密码、年级名查找郑环环所有考试的成绩（科目名称、考试成绩）使用表连接方式查询所有U1年级学员学号、姓名及年级名使用子查询方式查询所有U1年级学员学号、姓名、年级ID使用表连接方式查询“Java”课程至少一次考试刚好等于60分的学生姓名使用子查询方式查询“Java”课程至少一次考试刚好等于60分的学生使用子查询方式+表连接方式混合查询“Java”课程至少一次考试刚好等于60分的学生146/32总结MySQL数据库事务的ACID原则是什么？如何关闭和开启MySQL的自动提交？MySQL的事务处理实现流程是什么？MySQL数据库有哪些索引？什么时候使用索引？MySQL备份的几种方法？如何实现mysqldump的导出以及SOURCE导入？147/32问题及作业集中问题