《傳智播客PHP培訓.mysql.ppt》由会员分享，可在线阅读，更多相关《傳智播客PHP培訓.mysql.ppt（83页珍藏版）》请在三一办公上搜索。
1、mysql数据库,学习目录(1):认识数据库和mysqlmysql连接入门语句详解列类型增删改查查的5种子语句详解,mysql数据库,学习目录(2):连接查询子查询表引擎与字符集索引与索引优化触发器事务备份与恢复,1:认识数据库&mysql(1),1:数据库服务器为我们提供数据存储服务2:常用的数据库服务器:商业:oracle,SQLserver,DB2 开源:MySQL,postgreSQL,SQLite,1:认识数据库&mysql(2),MySQL是一个小型关系型数据库管理系统，开发者为瑞典MySQL AB公司。在2008年1月16号被Sun公司收购。而2009年,SUN又被Oracle收
2、购.对于Mysql的前途,没有任何人抱乐观的态度.目前 MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网 站总体拥有成本而选择了MySQL作为网站数据库。,2:mysql连接,mysql服务器,MySQL客户端,mysql是服务器,因此,满足myqsl通信规范的工具都可以做客户端,3:入门语句,查看所有库创建库删除库选择数据库查看表创建表删除表改表名,4:详解列类型 之3大列类型,数值型(整型列,浮点型列).字符型列日期/时间类型,4.1.1 整型列存储范围与所占空间,4.1.2 整型列的可选属性,
3、tinyint(M)unsigned zerofill,M:宽度(在0填充的时候才有意义)unsigned:无符号类型(非负)zerofill:0填充,(默认无符号),4.1.3 整型列案例练习,为下列变量用选一种合理的存储列班级同学们的年龄乌龟的年龄中国的社保登记号新浪微博的主键,4.1.2 浮点型与定点型,float(M,D)decimal(M,D)6,2M:精度(总位数,不包含点)D:标度(小数位),float:浮点型decimal:定点型decimal更精确,4.2 字符串类型,4.3 建表练习,给咱们班的同学建个档案表有如下信息:姓名年龄email手机号简介毕业薪水入学日期,4.3
4、时期时间类型,4.4 特殊的NULL类型,NULL 不是假,也不是真,而是空任何运算符,判断符碰到NULL,都得NULLNULL的判断只能用is null,is not nullNULL 影响查询速度,一般避免使值为NULL,回头再看建表语句,create table 表名(列1 列属性 默认值,列2 列属性 默认值,.列n 列属性 默认值)engine=存储引擎 charset=字符集,5 增删改查 之案例引入,用户管理模块:用户注册昵称修改后台管理员查看用户后台管理员删除某用户,5 增删改查 之案例过程分析,用户注册表单收集,提交数据,注册页面收集到表单的数据后,形成insert语句,us
5、er表插入该条数据,用户注册完成.前台用户中心,用户新昵称,根据新昵称和用户id,形成update语句完成昵称修改.管理员后台点击会员列表,此时,网页形成select 语句,查询出所有会员的数据,完成会员的查看.管理员后台选中某用户并且删除,捕捉此用户的id,根据用户id形成相应的delete语句,执行delete语句,完成用户的删除.,5 增删改查 之增 insert 图解,往哪张表添加?给哪几列添加值?分别是什么值?,往薪资登记表姓名,公司,工资3列添加值分别是张三,百度,5000,5 增删改查 之增 insert 语法,INSERT INTO 表名（列1,列n）VALUES(值 1,值
6、n);,允许不写,如果不写,则默认插入所有列见下例:,INSERT INTO 表名 VALUES(值 1,值 n);,VALUES 对应的值的输入顺序与表中字段顺序一致,5 增删改查 之改 update 图解,改哪张表?你需要给改哪几列的值?分别改为什么值?在哪些行生效?,update 表名 set 列1=新值1,列2=新值2,where expr,5 增删改查 之改 delete 语法,你要删哪张表的数据?你要删掉哪些行?,delete from 表名where expr,5 增删改查 之改 select 语法,查哪张表的数据?你要选择哪些列来查询?要选择哪些行?,select 列1,列2,
7、.列n from 表名where expr,6 select 5种子句介绍,Where 条件查询group by 分组having 筛选order by 排序limit 限制结果条数,where expression用法:expression为真,则该行取出运用场合各种条件查询场合,如按学号查学生,按价格查商品,按发布时间查新闻等,6.1 select 5种子句 之where介绍,6.1 select 5种子句 之where常用运算符,6.1 select 5种子句 之where 匹配,取出:诺基亚系列手机?取出诺基亚Nxx系列手机?,like 模糊匹配%通配任意字符_ 通配单一字符,6.1
8、select 5种子句 之where 练习,以ecshop库为基础练习 mysql复习.html 文档中的第三部分:查询知识 1.1-1.14题,6.2 select 5种子句 之group与统计函数,max:求最大min:求最小sum:求总和avg:求平均count:求总行数,6.2 select 5种子句 之group介绍,group by作用:把行 按 字段 分组语法:group by col1,col2,.colN运用场合常见于统计场合,如按栏目计算帖子数,统计每个人的平均成绩等.,6.2 select 5种子句 之group练习,以ecshop库为基础练习 mysql复习.html
9、文档中的第三部分:查询知识 2.1-2.8题,6.3 select 5种子句 之having介绍,having与where类似,可筛选数据where后的表达式怎么写,having就怎么写,where针对表中的列发挥作用,查询数据having针对查询结果中的列发挥作用,筛选数据,having与where异同点,6.3 select 5种子句 之having练习(1),以ecshop库为基础练习 mysql复习.html 文档中的第三部分:查询知识 3.1-3.6题,设有成绩表stu 如下：姓名科目分数张三数学90张三语文50张三地理40李四语文55李四政治45王五政治30试查询两门及两门以上不及
10、格同学的平均分,6.3 select 5种子句 之having练习(2),Order by 排序功能按一个或多个字段对查询结果进行排序知识点在本项目案例的运用对栏目的商品按价格由高到低或由低到高排序知识点的运用场合描述各种排序场合,如取热点新闻,发帖状元等,6.3 select 5种子句 之order by,6.4 select 5种子句 之order排序,Order by 排序功能按一个或多个字段对查询结果进行排序用法:order by col1,col2,col3知识点的运用场合描述各种排序场合,如新闻按点击量排序,商品按价格排序等默认排序:升续排列,Limit 限制条数limit off
11、set,N,限制结果取N条用法:limit 偏移量,取出条目知识点的运用场合描述分页应用中最为典型,如第1页取1-20条,第2页取21-40条.,6.5 select 5种子句 之limit 介绍,6.5 select 5种子句 order,limit练习,以ecshop库为基础练习 mysql复习.html 文档中的第三部分:查询知识 4.1-4.5题,7.0 连接查询,学习内容:左连接右连接内连接,应用案例非常广泛如,取文章及所在栏目名称取个人信息及所发布的文章等,作用:从2张或多张表中,取出有关联的数据.,7.1 连接查询 之笛卡尔积,7.2 连接查询 之什么是左连接,7.3 连接查询
12、之连接查询语法,左连接的语法.Select Ltable.*,Rtable.*from Ltable left join Rltable on Ltable.colName=Rtable.colName 内连接的语法.Select Ltable.*,Rtable.*from Ltable inner join Rltable on Ltable.colName=Rtable.colName,7.4 连接查询 之左右连接与内连接的区别,1:左右连接可互换A left join B 等价于B right join A,2:内连接是左右连接的交集,3:mysql没有外连接,7.5 连接查询 之练习,
13、以ecshop库为基础练习 mysql复习.html 文档中的第三部分:查询知识 5.1-5.3题,8.0 子查询,子查询就是在原有的查询语句中，嵌入新的查询,来得到我们想要的结果集。一般根据子查询的嵌入位置分为，where型子查询,from型子查询,8.1 where型子查询,where型子查询即是:把内层sql语句查询的结果作为外层sql查询的条件.,典型语法:select*from tableName where colName=(select colName from tbName where.)where colName in(select colName from tbName w
14、here.),典型案例:1:查询最新的一条商品2:查询出某大栏目下的所有商品,8.2 exists型子查询,exists即:外层sql查询所查到的行代入内层sql查询,要使内层查询能够成立查询可以与in型子查询互换,但效率要高.,典型语法:select*from tablenamewhere exists(select*from tableName where.),典型案例:1:查询出某大栏目下的所有商品,8.3 from型子查询,from型子查询即:把内层sql语句查询的结果作为临时表供外层sql语句再次查询.,典型语法:select*from(select*from tableName w
15、here.)where.,典型案例:1:查询出每个栏目下的最新商品2:BBS中查询每个栏目下的最新帖子,8.4 子查询练习,以ecshop库为基础练习 mysql复习.html 文档中的第三部分:查询知识 7.1-7.4题,9.0 存储引擎与字符集,学习目的:出于速度和安全性的要求,选取合理的存储引擎.考虑多语言与移植的问题,选取合理的字符集.,9.1 存储引擎是什么概念?,设有张马虎,李小心两人,都是地铁口的自行车管理员.每天都有很多人来存取自行车,张马虎的管理方式是:来存自己存,不记录存的是什么车,取时交5毛,也不检查取的是否是自己的车.李小心呢,则在存取自己车时,记录存车人的特征与自行车
16、的特征,当人来取车,还要小心核一下,人与车的特征是否对应思考:张马虎和李小心谁对业务的处理速度更高?二者谁对自行车管理更安全?,数据库对同样的数据,有着不同的存储方式和管理方式在mysql中,称为存储引擎,9.2 存储引擎与其特点,9.3 存储引擎的选择,文章,新闻等安全性要求不高的,选myisam,订单,资金,账单,火车票等对安全性要求高的,可以选用innodb,对于临时中转表,可以用memory型,速度最快,9.4 字符集(charset),一句话说字符集字符集就是一个字符二进制字节的映射表,字符集是一套符号和编码的规则,不论是在 oracle 数据库还是在 mysql 数据库,都存在字符
17、集的选择问题,而且如果在数据库创建阶段没有正确选择字符集,那么可能在后期需要更换字符集,而字符集的更换是代价比较高的操作,也存在一定的风险,所以,我们推荐在应用开始阶段,就按照需求正确的选择合适的字符集,避免后期不必要的调整。,9.5 字符集的选择,1:节省空间建议在能够完全满足应用的前提下,尽量使用小的字符集。因为更小的字符集意味着能够节省空间、减少网络传输字节数,同时由于存储空间的较小间接的提高了系统的性能。有很多字符集可以保存汉字,比如 utf8、gb2312、gbk、gb18030 等等,但是常用的是gb2312 和 gbk。2:兼容性因为 gb2312 字库比 gbk 字库小,有些偏
18、僻字(例如:洺)不能保存,因此在选择字符集的时候一定要权衡这些偏僻字在应用出现的几率以及造成的影响,3:在互联网上,国际化的趋势不可避免,且存储空间已经越来海量化,因此推荐用utf8,如果开发内网系统,如内部OA等,可以考虑GBK。,9.6 校对规则collate(了解即可),一句话说校对规则校对规则就是对字符集中的字符的座次表,请你给 A a B b C c D d按升序排列,9.7 字符集专题-彻底搞定乱码,客户端字符集client,服务器存储,查询,比较时用的字符集(connection),查询结果字符集results,9.8 字符集专题-新旧网站并存案例,旧数据库GBK字符集,旧网站系
19、统GBK编码,新网站utf8编码,思考:数据库不准动,新站又是utf8,如何保持新网站与旧数据的兼容?,10.0 索引与优化 之索引是什么?,该字典前面的目录全部丢失请找出华这个字来,如何快速找到某个字可以给字典加目录对数据库来说,索引的作用即是给数据加目录,10.1 索引与优化 之索引算法(了解),设有N条随机记录,不用索引,平均查找N/2次,那么用了索引之后呢,btree(二叉树)索引,hash(哈希)索引1,10.2 索引与优化 之索引的好处与坏处,好外:加快了查询速度(select)坏处:降低了增,删,改的速度(update/delete/insert)增大了表的文件大小(索引文件甚至
20、可能比数据文件还大),10.3 索引与优化 之索引的使用原则,不过度索引索引条件列(where后面最频繁的条件比较适宜索引)索引散列值,过于集中的值不要索引例如:给性别男,女加索引,意义不大,10.4 索引与优化 之索引类型,普通索引(index)主键索引(primary key)唯一索引(unique)全文索引(fulltext),10.5 索引与优化 之索引创建语法(1),建表时直接声明索引:create table tableName(列1 列类型 列属性,.列N 列类型 列属性,primary key(列名),index(列名),unique(列名),fulltext(列名)engin
21、e xxxxx charset xxxx,10.6 索引与优化 之索引创建语法(2),通过修改表建立索引alter table add index(列名);alter table add unique(列名);alter table add primary key(列名);alter table add fulltext(列名);,10.6 索引与优化 之索引删除语法,删除主键:alter table drop primary key删除其他索引:alter table drop index 索引名注:索引名一般是列名,如果不是,可通过show index from tableName查看索引
22、,10.7 索引与优化 之索引练习,1:创建右图所示的表并为各列建立索引,分别为id,primary key,主键索引name,unique index 唯一索引school index 普通索引intro fulltext 全文索引2:通过alter table drop index删掉所有索引3:通过alter table add index 再分别给4个列增加索引,11.0 触发器,学习目标:触发器的定义触发器的应用场合掌握触发器的创建语法会创建简单触发器,进行数据库应用软件的开发时，我们有时会碰到表中的某些数据改变，希望同时引起其他相关数据改变的需求，利用触发器就能满足这样的需求。它能
23、在表中的某些特定数据变化时自动完成某些查询。运用触发器不仅可以简化程序，而且可以增加程序的灵活性。,触发器是一类特殊的事务,可以监视某种数据操作(insert/update/delete),并触发相关操作(insert/update/delete),11.1 触发器定义,11.2 触发器应用场合,1.当向一张表中添加或删除记录时，需要在相关表中进行同步操作。比如，当一个订单产生时,订单所购的商品的库存量相应减少。2.当表上某列数据的值与其他表中的数据有联系时。比如，当某客户进行欠款消费，可以在生成订单时通过设计触发器判断该客户的累计欠款是否超出了最大限度。3.当需要对某张表进行跟踪时。比如，当
24、有新订单产生时，需要及时通知相关人员进行处理，此时可以在订单表上设计添加触发器加以实现,11.3 触发器创建语法 之4要素,监视地点(table),监视事件insert/update/delete,触发事件insert/update/delete,触发时间after/before,11.4 触发器创建语法,创建触发器的语法create trigger 触发器名称after/befor(触发时间)insert/update/delete(监视事件)on 表名(监视地址)for each rowbeginsql1;.sqlN;end,11.5 触发器引用行变量(1),insert,delete,1
25、1.5 触发器引用行变量(2),update,11.6 触发器的删除,drop trigger triggerName,11.5 触发器案例实战,设计一张商品表 一张订单表创建3个触发器,作用分别是:当下订单购买商品时,相应商品减少库存修改订单中的商品数量时,相应商品修改库存当取消某订单时,相应商品增加库存,12.0 事务 之事务的概念,思考:我去银行给朋友汇款,我卡上有1000元,朋友卡上500元,我给朋友转账50元(无手续费),如果,我的钱刚扣,而朋友的钱又没加时,网线断了,怎么办?,12.1 事务 之事务的ACID特性,原子性(Atomicity)：原子意为最小的粒子，或者说不能再分的事
26、物。数据库事务的不可再分的原则即为原子性。组成事务的所有查询必须：要么全部执行，要么全部取消（就像上面的银行例子）。一致性(Consistency)：指数据的规则,在事务前/后应保持一致隔离性(Isolation)：简单点说，某个事务的操作对其他事务不可见的.持久性(Durability)：当事务完成后，其影响应该保留下来，不能撤消,12.2 事务 之事务的用法,开启事务(start transaction)执行sql操作(普通sql操作)提交/回滚(commit/rollback),注意:建表的时候,选择 innodb引擎,ecshop的购物流程中的订单付款操作含有以下3步子操作从用户的余额
27、中扣除订单价格在用户的账户变动记录里记下流水修改订单状态为已付款.这三步需保证原子性,请用事务来控制此过程.,12.3 事务 之案例练习,13.0 备份与恢复,作为一个成熟的电子商城项目,常规的增删改查当然是最常规最频繁的应用,但备份技术也必不可少,否则可能会带来灾难性的后果.通过操作系统的定时任务功能,可以自动调用备份命令进行备份操作.在必要时可以进行数据的恢复,13.1 备份命令,备份单独库下面的所有表的方法mysqldump-uuname-ppasswd dbname/dir/filename 备份某一库下面的几个表的方法mysqldump-uuname-ppasswd dbname table1 table2.tableN/dir/filename 备份多个库的方法mysqldump-uname-ppasswd-B db1 db2/dir/filename 备份所有库的方法 mysqldump-uname-ppasswd-A/dir/filename,13.2 恢复命令,1:在命令行操作mysql-uuname-ppasswd databaseName use dbname;mysql source/dir/filename;,
