燕十八公益讲堂mysql.ppt.ppt

燕十八公益讲堂mysql.ppt.ppt

《燕十八公益讲堂mysql.ppt.ppt》由会员分享，可在线阅读，更多相关《燕十八公益讲堂mysql.ppt.ppt（83页珍藏版）》请在冰点文库上搜索。

zixue.it燕十八精品PHP教程,MySQL系统讲解,主讲:

燕十八课时:

24课时官方论坛:

http:

/zixue.it老师博客:

http:

/老师微博:

http:

/,zixue.it燕十八精品PHP教程,mysql数据库,学习目录

（1）:

认识数据库和mysqlmysql连接入门语句基本增删改查详解列类型表的创建与修改查的5种子语句详解,zixue.it燕十八精品PHP教程,mysql数据库,学习目录

（2）:

子查询连接查询表引擎与字符集索引与索引优化触发器事务备份与恢复,zixue.it燕十八精品PHP教程,1:

认识数据库&mysql

（1）,1:

数据库服务器为我们提供数据存储服务2:

常用的数据库服务器:

商业:

oracle,SQLserver,DB2开源:

MySQL,postgreSQL,SQLite,zixue.it燕十八精品PHP教程,1:

认识数据库&mysql

（2）,MySQL是一个小型关系型数据库管理系统，开发者为瑞典MySQLAB公司。

在2008年1月16号被Sun公司收购。

而2009年,SUN又被Oracle收购.对于Mysql的前途,没有任何人抱乐观的态度.目前MySQL被广泛地应用在Internet上的中小型网站中。

由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

zixue.it燕十八精品PHP教程,2:

mysql连接,mysql服务器,MySQL客户端,mysql是服务器,因此,满足myqsl通信规范的工具都可以做客户端,zixue.it燕十八精品PHP教程,3:

入门语句,连接服务器查看所有库创建库删除库选择数据库查看表创建简单表删除表改表名清空表,zixue.it燕十八精品PHP教程,4增删改查之案例过程分析,用户注册表单收集,提交数据,注册页面收集到表单的数据后,形成insert语句,user表插入该条数据,用户注册完成.前台用户中心,用户新昵称,根据新昵称和用户id,形成update语句完成昵称修改.管理员后台点击会员列表,此时,网页形成select语句,查询出所有会员的数据,完成会员的查看.管理员后台选中某用户并且删除,捕捉此用户的id,根据用户id形成相应的delete语句,执行delete语句,完成用户的删除.,zixue.it燕十八精品PHP教程,4.1增删改查之增insert图解,往哪张表添加行?

给哪几列添加值?

分别是什么值?

往薪资登记表姓名,公司,工资3列添加值分别是张三,百度,5000,zixue.it燕十八精品PHP教程,4.2增删改查之增insert语法,INSERTINTO表名（列1,列n）VALUES（值1,值n）;,允许不写,如果不写,则默认插入所有列见下例:

INSERTINTO表名VALUES（值1,值n）;,VALUES对应的值的输入顺序与表中字段顺序一致,zixue.it燕十八精品PHP教程,4.3增删改查之改update图解,改哪张表?

你需要给改哪几列的值?

分别改为什么值?

在哪些行生效?

update表名set列1=新值1,列2=新值2,whereexpr,zixue.it燕十八精品PHP教程,4.4增删改查之删delete语法,你要删哪张表的数据?

你要删掉哪些行?

deletefrom表名whereexpr,zixue.it燕十八精品PHP教程,4.5增删改查之查select语法,查哪张表的数据?

你要选择哪些列来查询?

要选择哪些行?

select列1,列2,.列nfrom表名whereexpr,zixue.it燕十八精品PHP教程,5:

详解列类型之3大列类型,数值型（整型列,浮点型列）.字符型列日期/时间类型,zixue.it燕十八精品PHP教程,5.1.1整型列存储范围与所占空间,zixue.it燕十八精品PHP教程,5.1.2整型列的可选属性,tinyint（M）unsignedzerofill,M:

宽度（在0填充的时候才有意义）unsigned:

无符号类型（非负）zerofill:

0填充,（默认无符号）,zixue.it燕十八精品PHP教程,5.1.3整型列案例练习,为下列变量用选一种合理的存储列班级同学们的年龄乌龟的年龄中国的社保登记号新浪微博的主键,zixue.it燕十八精品PHP教程,5.1.4浮点型与定点型,float（M,D）decimal（M,D）6,2M:

精度（总位数,不包含点）D:

标度（小数位）,float:

浮点型decimal:

定点型decimal更精确,floatM=24时占4个字节,大于时占8个decimal的存储空间变长,注意范围不要选过大,zixue.it燕十八精品PHP教程,5.2字符串类型,zixue.it燕十八精品PHP教程,5.3时期时间类型,列类型存储需求DATE3个字节DATETIME8个字节TIMESTAMP4个字节TIME3个字节YEAR1个字节,还可以存0000年,zixue.it燕十八精品PHP教程,5.4特殊的NULL类型,NULL不是假,也不是真,而是空任何运算符,判断符碰到NULL,都得NULLNULL的判断只能用isnull,isnotnullNULL影响查询速度,一般避免使值为NULL,zixue.it燕十八精品PHP教程,5.5增删改查之案例引入,用户管理模块:

用户注册昵称修改后台管理员查看用户后台管理员删除某用户,zixue.it燕十八精品PHP教程,6建表语句,createtable表名（列1列属性默认值,列2列属性默认值,.列n列属性默认值）engine=存储引擎charset=字符集,zixue.it燕十八精品PHP教程,7select5种子句介绍,Where条件查询groupby分组having筛选orderby排序limit限制结果条数,zixue.it燕十八精品PHP教程,whereexpression用法:

expression为真,则该行取出运用场合各种条件查询场合,如按学号查学生,按价格查商品,按发布时间查新闻等,7.1select5种子句之where介绍,zixue.it燕十八精品PHP教程,7.1select5种子句之where常用运算符,zixue.it燕十八精品PHP教程,7.1select5种子句之where匹配,取出:

诺基亚系列手机?

取出诺基亚Nxx系列手机?

like模糊匹配%通配任意字符_通配单一字符,zixue.it燕十八精品PHP教程,7.1select5种子句之where练习,以ecshop库为基础练习mysql复习.html文档中的第三部分:

查询知识1.1-1.14题,zixue.it燕十八精品PHP教程,7.2select5种子句之group与统计函数,max:

求最大min:

求最小sum:

求总和avg:

求平均count:

求总行数,zixue.it燕十八精品PHP教程,7.2select5种子句之group介绍,groupby作用:

把行按字段分组语法:

groupbycol1,col2,.colN运用场合常见于统计场合,如按栏目计算帖子数,统计每个人的平均成绩等.,zixue.it燕十八精品PHP教程,7.2select5种子句之group练习,以ecshop库为基础练习mysql复习.html文档中的第三部分:

查询知识2.1-2.8题,zixue.it燕十八精品PHP教程,7.3select5种子句之having介绍,having与where类似,可筛选数据where后的表达式怎么写,having就怎么写,where针对表中的列发挥作用,查询数据having针对查询结果中的列发挥作用,筛选数据,having与where异同点,zixue.it燕十八精品PHP教程,7.3select5种子句之having练习

（1）,以ecshop库为基础练习mysql复习.html文档中的第三部分:

查询知识3.1-3.6题,zixue.it燕十八精品PHP教程,设有成绩表stu如下：

姓名科目分数张三数学90张三语文50张三地理40李四语文55李四政治45王五政治30试查询两门及两门以上不及格同学的平均分,7.3select5种子句之having练习

（2）,zixue.it燕十八精品PHP教程,Orderby排序功能按一个或多个字段对查询结果进行排序知识点在本项目案例的运用对栏目的商品按价格由高到低或由低到高排序知识点的运用场合描述各种排序场合,如取热点新闻,发帖状元等,7.3select5种子句之having练习

（2）,zixue.it燕十八精品PHP教程,7.4select5种子句之order排序,Orderby排序功能按一个或多个字段对查询结果进行排序用法:

orderbycol1,col2,col3知识点的运用场合描述各种排序场合,如新闻按点击量排序,商品按价格排序等默认排序:

升续排列,zixue.it燕十八精品PHP教程,Limit限制条数limitoffset,N,限制结果取N条用法:

limit偏移量,取出条目知识点的运用场合描述分页应用中最为典型,如第1页取1-20条,第2页取21-40条.,7.5select5种子句之limit介绍,zixue.it燕十八精品PHP教程,7.5select5种子句order,limit练习,以ecshop库为基础练习mysql复习.html文档中的第三部分:

查询知识4.1-4.5题,zixue.it燕十八精品PHP教程,8.0子查询,子查询就是在原有的查询语句中，嵌入新的查询,来得到我们想要的结果集。

一般根据子查询的嵌入位置分为，where型子查询,from型子查询,zixue.it燕十八精品PHP教程,8.1where型子查询,where型子查询即是:

把内层sql语句查询的结果作为外层sql查询的条件.,典型语法:

select*fromtableNamewherecolName=（selectcolNamefromtbNamewhere.）wherecolNamein（selectcolNamefromtbNamewhere.）,典型案例:

1:

查询最新的一条商品2:

查询出某大栏目下的所有商品,zixue.it燕十八精品PHP教程,8.2exists型子查询,exists即:

外层sql查询所查到的行代入内层sql查询,要使内层查询能够成立查询可以与in型子查询互换,但效率要高.,典型语法:

select*fromtablenamewhereexists（select*fromtableNamewhere.）,典型案例:

1:

查询出某大栏目下的所有商品,zixue.it燕十八精品PHP教程,8.3from型子查询,from型子查询即:

把内层sql语句查询的结果作为临时表供外层sql语句再次查询.,典型语法:

select*from（select*fromtableNamewhere.）where.,典型案例:

1:

查询出每个栏目下的最新商品2:

BBS中查询每个栏目下的最新帖子,zixue.it燕十八精品PHP教程,8.4子查询练习,以ecshop库为基础练习mysql复习.html文档中的第三部分:

查询知识7.1-7.4题,zixue.it燕十八精品PHP教程,9.0连接查询,学习内容:

左连接右连接内连接,应用案例非常广泛如,取文章及所在栏目名称取个人信息及所发布的文章等,作用:

从2张或多张表中,取出有关联的数据.,zixue.it燕十八精品PHP教程,9.1连接查询之笛卡尔积,zixue.it燕十八精品PHP教程,9.2连接查询之什么是左连接,zixue.it燕十八精品PHP教程,9.3连接查询之连接查询语法,左连接的语法.SelectLtable.*,Rtable.*fromLtableleftjoinRltableonLtable.colName=Rtable.colName内连接的语法.SelectLtable.*,Rtable.*fromLtableinnerjoinRltableonLtable.colName=Rtable.colName,zixue.it燕十八精品PHP教程,9.4连接查询之左右连接与内连接的区别,1:

左右连接可互换AleftjoinB等价于BrightjoinA,2:

内连接是左右连接的交集,3:

mysql没有外连接,zixue.it燕十八精品PHP教程,9.5连接查询之练习,以ecshop库为基础练习mysql复习.html文档中的第三部分:

查询知识5.1-5.3题,zixue.it燕十八精品PHP教程,10.0存储引擎与字符集,学习目的:

出于速度和安全性的要求,选取合理的存储引擎.考虑多语言与移植的问题,选取合理的字符集.,zixue.it燕十八精品PHP教程,10.1存储引擎是什么概念?

设有张马虎,李小心两人,都是地铁口的自行车管理员.每天都有很多人来存取自行车,张马虎的管理方式是:

来存自己存,不记录存的是什么车,取时交5毛,也不检查取的是否是自己的车.李小心呢,则在存取自己车时,记录存车人的特征与自行车的特征,当人来取钱车,还要小心核一下,人与车的特征是否对应思考:

张马虎和李小心谁对业务的处理速度更高?

二者谁对自行车管理更安全?

数据库对同样的数据,有着不同的存储方式和管理方式在mysql中,称为存储引擎,zixue.it燕十八精品PHP教程,10.2存储引擎与其特点,zixue.it燕十八精品PHP教程,10.3存储引擎的选择,文章,新闻等安全性要求不高的,选myisam,订单,资金,账单,火车票等对安全性要求高的,可以选用innodb,对于临时中转表,可以用memory型,速度最快,zixue.it燕十八精品PHP教程,10.4字符集（charset）,一句话说字符集字符集就是一个字符二进制字节的映射表,字符集是一套符号和编码的规则,不论是在oracle数据库还是在mysql数据库,都存在字符集的选择问题,而且如果在数据库创建阶段没有正确选择字符集,那么可能在后期需要更换字符集,而字符集的更换是代价比较高的操作,也存在一定的风险,所以,我们推荐在应用开始阶段,就按照需求正确的选择合适的字符集,避免后期不必要的调整。

zixue.it燕十八精品PHP教程,10.5字符集的选择,1:

节省空间建议在能够完全满足应用的前提下,尽量使用小的字符集。

因为更小的字符集意味着能够节省空间、减少网络传输字节数,同时由于存储空间的较小间接的提高了系统的性能。

有很多字符集可以保存汉字,比如utf8、gb2312、gbk、gb18030等等,但是常用的是gb2312和gbk。

2:

兼容性因为gb2312字库比gbk字库小,有些偏僻字（例如:

洺）不能保存,因此在选择字符集的时候一定要权衡这些偏僻字在应用出现的几率以及造成的影响,3:

在互联网上,国际化的趋势不可避免,且存储空间已经越来海量化,因此推荐用utf8,如果开发内网系统,如内部OA等,可以考虑GBK。

zixue.it燕十八精品PHP教程,10.6校对规则collate（了解即可）,一句话说校对规则校对规则就是对字符集中的字符的座次表,请你给AaBbCcDd按升序排列,zixue.it燕十八精品PHP教程,10.7字符集专题-彻底搞定乱码,客户端字符集client,连接器字符集（connection）,查询结果字符集results,utf8,zixue.it燕十八精品PHP教程,10.0索引与优化之索引是什么?

该字典前面的目录全部丢失请找出华这个字来,如何快速找到某个字可以给字典加目录对数据库来说,索引的作用即是给数据加目录,zixue.it燕十八精品PHP教程,10.8字符集专题-新旧网站并存案例,旧数据库GBK字符集,旧网站系统GBK编码,新网站utf8编码,思考:

数据库不准动,新站又是utf8,如何保持新网站与旧数据的兼容?

zixue.it燕十八精品PHP教程,11.1索引与优化之索引算法（了解）,设有N条随机记录,不用索引,平均查找N/2次,那么用了索引之后呢,btree（二叉树）索引,hash（哈希）索引1,zixue.it燕十八精品PHP教程,11.2索引与优化之索引的好处与坏处,好外:

加快了查询速度（select）坏处:

降低了增,删,改的速度（update/delete/insert）增大了表的文件大小（索引文件甚至可能比数据文件还大）,zixue.it燕十八精品PHP教程,11.3索引与优化之索引的使用原则,不过度索引索引条件列（where后面最频繁的条件比较适宜索引）索引散列值,过于集中的值不要索引例如:

给性别男,女加索引,意义不大,zixue.it燕十八精品PHP教程,11.4索引与优化之索引类型,普通索引（index）主键索引（primarykey）唯一索引（unique）全文索引（fulltext）,zixue.it燕十八精品PHP教程,11.5索引与优化之索引创建语法

（1）,建表时直接声明索引:

createtabletableName（列1列类型列属性,.列N列类型列属性,primarykey（列名）,index（列名）,unique（列名）,fulltext（列名）enginexxxxxcharsetxxxx,zixue.it燕十八精品PHP教程,11.5索引与优化之索引创建语法

（2）,通过修改表建立索引altertableaddindex（列名）;altertableaddunique（列名）;altertableaddprimarykey（列名）;altertableaddfulltext（列名）;,zixue.it燕十八精品PHP教程,11.6索引与优化之索引删除语法,删除主键:

altertabledropprimarykey删除其他索引:

altertabledropindex索引名注:

索引名一般是列名,如果不是,可通过showindexfromtableName查看索引,zixue.it燕十八精品PHP教程,11.7索引与优化之索引练习,1:

创建右图所示的表并为各列建立索引,分别为id,primarykey,主键索引name,uniqueindex唯一索引schoolindex普通索引introfulltext全文索引2:

通过altertabledropindex删掉所有索引3:

通过altertableaddindex再分别给4个列增加索引,zixue.it燕十八精品PHP教程,12.0触发器,学习目标:

触发器的定义触发器的应用场合掌握触发器的创建语法会创建简单触发器,zixue.it燕十八精品PHP教程,进行数据库应用软件的开发时，我们有时会碰到表中的某些数据改变，希望同时引起其他相关数据改变的需求，利用触发器就能满足这样的需求。

它能在表中的某些特定数据变化时自动完成某些查询。

运用触发器不仅可以简化程序，而且可以增加程序的灵活性。

触发器是一类特殊的事务,可以监视某种数据操作（insert/update/delete）,并触发相关操作（insert/update/delete）,12.1触发器定义,zixue.it燕十八精品PHP教程,12.2触发器应用场合,1.当向一张表中添加或删除记录时，需要在相关表中进行同步操作。

比如，当一个订单产生时,订单所购的商品的库存量相应减少。

2.当表上某列数据的值与其他表中的数据有联系时。

比如，当某客户进行欠款消费，可以在生成订单时通过设计触发器判断该客户的累计欠款是否超出了最大限度。

3.当需要对某张表进行跟踪时。

比如，当有新订单产生时，需要及时通知相关人员进行处理，此时可以在订单表上设计添加触发器加以实现,zixue.it燕十八精品PHP教程,12.3触发器创建语法之4要素,监视地点（table）,监视事件insert/update/delete,触发事件insert/update/delete,触发时间after/before,zixue.it燕十八精品PHP教程,12.4触发器创建语法,创建触发器的语法createtrigger触发器名称after/befor（触发时间）insert/update/delete（监视事件）on表名（监视地址）foreachrowbeginsql1;.sqlN;end,zixue.it燕十八精品PHP教程,12.5触发器引用行变量

（1）,insert,delete,zixue.it燕十八精品PHP教程,12.5触发器引用行变量

（2）,update,zixue.it燕十八精品PHP教程,12.6触发器的删除,droptriggertriggerName,zixue.it燕十八精品PHP教程,12.7触发器案例实战,设计一张商品表一张订单表创建3个触发器,作用分别是:

当下订单购买商品时,相应商品减少库存修改订单中的商品数量时,相应商品修改库存当取消某订单时,相应商品增加库存,zixue.it燕十八精品PHP教程,13.0事务之事务的概念,思考:

我去银行给朋友汇款,我卡上有1000元,朋友卡上500元,我给朋友转账50元（无手续费）,如果,我的钱刚扣,而朋友的钱又没加时,网线断了,怎么办?

zixue.it燕十八精品PHP教程,13.1事务之事务的ACID特性,原子性（Atomicity）：

原子意为最小的粒子，或者说不能再分的事物。

数据库事务的不可再分的原则即为原子性。

组成事务的所有查询必须：

要么全部执行，要么全部取消（就像上面的银行例子）。

一致性（Consistency）：

指数据的规则,在事务前/后应保持一致隔离性（Isolation）：

简单点说，某个事务的操作对其他事务不可见的.持久性（Durability）：

当事务完成后，其影响应该保留下来，不能撤消,zixue.it燕十八精品PHP教程,13.2事务之事务的用法,开启事务（starttransaction）执行sql操作（普通sql操作）提交/回滚（commit/rollback）,注意:

建表的时候,选择innodb引擎,zixue.it燕十八精品PHP教程,ecshop的购物流程中的订单付款操作含有以下3步子操作从用户的余额中扣除订单价格在用户的账户变动记录里记下流水修改订单状态为已付款.这三步需保证原子性,请用事务来控制此过程.,13.3事务之案例练习,zixue.it燕十八精品PHP教程,14.0备份与恢复,作为一个成熟的电子商城项目,常规的增删改查当然是最常规最频繁的应用,但备份技术也必不可少,否则可能会带来灾难性的后果.通过操作系统的定时任务功能,可以自动调用备份命令进行备份操作.在必要时可以进行数据的恢复,zixue.it燕十八精品PHP教程,14.1备份命令,备份单独库下面的所有表的方法mysqldump-uuname-ppasswddbname/dir/filename备份某一库下面的几个表的方法mysqldump-uuname-ppasswddbnametable1table2.tableN/dir/filename备份多个库的方法mysqldump-uname-ppasswd-Bdb1db2/dir/filename