课程笔记-16mysql-讲义第二天it教程

MySQLMySQL是最流行的关系型数据库管理系统，在WEB应用方 MySQL是最好可以通过SQL对数据库中的数据进行增加，修改，删除及查询操作。数据库中的记录是有行有列的数据库就是关系型数据库，与之相反的就是NoSQLSystemDBMSMYSQL：开源免费OracleMySQL5.5都是由Oracle发布的版本。 Oracle ：IBM公司的数据库产品,的。常应用在银行系统中.的互联网公司，要求去IOE（IBM小型机、Oracle数据库、EMC设备） :嵌入式的小型数据库，应用在端我们要学习的数据MySQL1979MontyWideniusTcXBASIC4MHz16KB上运行。当时，这只是一个很底层的且仅面向报表的引擎，名叫Unireg。1990年，TcX公司的客户中开始有人要求为他的API提供SQL支持。Monty直接借助于mSQL的代码，将它集成到自己的引擎中。令人失望的是，效果并不太令人满意，决心自己重写一个SQL支持。1996年，MySQL1.0发布，它只面向一小拨人，相当于发布199610MySQL3.11.1MySQL2.xSolaris1999～2000MySQLAB公司在瑞典成立。MontySleepycatBerkeleyDB,由于BDBMySQL开始支持事务处理2000，MySQL不仅自己的源代码，并采用GPL(GNUGeneralPublicLicense)协议，正式进入开源世界。同年4月，MySQL对旧的引擎ISAM进行了整理，将其命名为MyISAM。2001年，集成HeikkiTuuri的引擎InnoDB，这个引擎不仅能持事务处理，并且支持行级锁。后来该引擎被证明是最为成功的MySQL事务引擎。MySQL与InnoDB的正式结合版本是4.02003年12月，MySQL5.0版本发布，提供了视图、过程等功能的NDB集群系统，同时修复了大量的Bug。2009年4月，Oracle公司以74亿收购Sun公司，自此MySQL数据库进入时代，而其第的引擎InnoDB早在2005年就被Oracle公司收购擎。MySQL5.5MySQL业级的特性。Oracle公司同时也承诺MySQL5.5和未来版本仍是采用GPL的开源产MySQLMySQLMysQL格格式1：cmd>mysqlu户名–p例如：mysql-uroot–proot2：cmd>mysql--user=用户名--host=ip地址--SQLyog安装与使用QuerySQLSQLF8SQLSQLStructuredQueryLanguageSQLmysqlSQL92或者SQL99这些国际SQL扩展了自己的一些SQL语句，比如MySQL中的limit关键字SQL数据定义语言：简称DDL(DataDefinitionLanguage)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等数据操作语言：简称DML(DataManipulationLanguage)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等数据控制语言：简称DCL(D ontrolLanguage)，用来定义数据库的权限和安全级别，及创建用户；关键字：grant等select，from，where等SQL语语法createdatabase数据库名createdatabase数据库名characterset字符集;查看数据库服务器中的所有的数据库:show查看数据库服务器中的所有的数据库:show查看某个数据库的定义的信息:showcreatedatabase数据库名dropdropdatabase数据库名称;切换数据切换数据库：use数据库名;查看正在使用的数据库:selectcreatedatabaseday06;useboolean类型：不支持 YYYY-MM-DDHH:MM:SS1000-01-019999-12-31YYYY-MM-DD 1970~01~01M0~255M0~655350~255允许长度 字0~2550~65535允许长度 字createcreatetable表名字段名类型(长度)约束,字段名类型(长度)约*主键约束：primary非空约束：not注意：主键约束=唯一约束+非空约束CREATETABLEsortsidINTPRIMARYKEY分类查看数据库中的所有表：show查看数据库中的所有表：show查看表结构：desc表名dropdroptable表名alteraltertable表名add列名类型(长度)约束 altertable表名modify列名类型(长度)约束; altertable表名change旧列名新列名类型(长度)约束; altertable表名drop列名; renametable表名to新表名; altertable表名characterset字符集; insertinsertinto表(列名1,列名2,列名3..)values(值1,值2,值3..);--向表 insertinto表values(值1,值2,值3..);--向表 insertinto表(列名1,列名23valuesselect列名1,列名2,列名3from表insertinto表valuesselect*from表INSERTINSERTINTOsort(sid,snameVALUES('s001INSERTINTOsort(sid,snameVALUES('s002INSERTINTOsortVALUES('s003','化妆品');updateupdate表名set字段名=值,字段名=值update表名set字段名=值,字段名=值where条件deletedeletefrom表名[where条件];删除表中所有记录删除表中所有记录使用deletefrom表名;还是用truncatetable表名;删除方式：delete一条一条删除，不清空auto_increment记录数。直接将表删除，重新建表，将CMDSQLpidINTPRIMARYKEYAUTO_INCREMENT,#自增加priceDOUBLE,#商品价格pdateDATE,#日sidVARCHAR(20分类INSERTINTOproductsVALUES(NULL,' 大榴莲',98,NULL,'s001');INSERTINTOproductsVALUES(NULL,' 大枣',38,NULL,'s002');INSERTINTOproductsVALUES(NULL,' 切糕',68,NULL,'s001');INSERTINTOproductsVALUES(NULL,'十三香',10,NULL,'s002');INSERTINTOproductsVALUES(NULL,'老干妈',20,NULL,'s002');SELECTSELECT[DISTINCT]*[列名,列名FROM表[WHEREselect*from查询商品名和商品价格.selectpname,pricefrom别名查询，使用的asas表别名:select*from 列别名：selectpnameaspnfrom去掉重复值.selectdistinctpricefromselectselectpname,price+10fromselect*fromproductwherepnameselect*fromproductwhereprice>>like使用占位符_和 _代表一个字符%代表任意个字符select*fromproductwherepnamelikeselect*fromproductwherepidin =inLIKE模糊查询，Like符，_代表一个字符，例first_namelike‘IS不成立，例：whereORDERASC（升序）DESC（降序select*fromproductorderbyselect*fromproductwherepnamelike新orderbyprice获得所有商品的价格的总和：select获得所有商品的价格的总和：selectsum(pricefrom获得所有商品的平均价格：selectavg(pricefrom获得所有商品的个数：selectcount(*fromGROUP根根 selectcid,count(*)fromproductgroupby根 selectcid,avg(price)fromproductgroupbycidhavinggroupbyselectselecthavingwhereunion集合的并集（不包含重复记录LEFTGROUPORDER示例SQL：SELECT*FROMuserLEFTJOINorderONuser.id=order.uidWHEREorder.price>GROUPBYHAVINGcount(1)>5ORDERBYLIMITON（将VT1）---对ORDERBY（对VT7）结论：写WHEREcreatetablecidvarchar(32)primarycnamecreatetablepidvarchar(32)primarypnamepricecreatetableoidvarchar(32)primarytotalpricecreatetableoidoidvarchar(50),pid表与表之间的关系，说的就是表与表之间数据从表是：分类表。从表中，应该有一个字段去关联主表，而这个关联字altertable表名add[constraint][约束名称]foreignkey(主表外键字段)references从altertable表名dropforeignkey外键约束名idINTPRIMARYKEY,wnameVARCHAR(20),sexCHAR(1)idINTPRIMARYKEY,hnameVARCHAR(20),sexCHAR(1)之添加外键列wid，指定该列的约束为唯一（不加唯一约束就是一对多关系）ALTERTABLEhusbandADDwidINT altertablehusbandaddforeignkey(wid)references之（总结：有外键的就是多的一方一对多关系和一对一关系的创建很类似，唯一区别就是外键不唯一本章节向大家介绍如何使用MySQL的JOIN在两个或多个表中查询数据你可以在SELECTUPDATE和DELETE语句中使用MySQL的JOIN来联合多表查询。JOIN按照功能OUTERJOIN（外连接insertinsertintocategoryamevaluesc001insertintocategoryamevaluesc002insertintocategoryamevaluesc003insertintoproduct(pid,pname,price,cidvaluesp001insertintoproduct(pid,pname,price,cidvaluesp002','海尔',3000,'c001');insertintoproduct(pid,pname,price,cidvaluesp003insertintoproduct(pid,pname,price,cidvaluesp004insertintoproduct(pid,pname,price,cidvaluesp005','耐克',1200,'c002');insertintoproduct(pid,pname,price,cid)values('p006','NB',800,'c002');insertintoproduct(pid,pname,price,cidvaluesp007insertintoproduct(pid,pname,price,cidvaluesp008)('交叉连接也叫积连product第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。交叉连接的表现：行数相乘、列数相 A, 内连接的关键字：INNER A.id= INNER BONA.id=在FROM左外连接：LEFTJOIN或者LEFTOUTER BONA.id=案例：查询分类信息，关联查询商品右外连接:：RIGHTJOIN或者RIGHTOUTER BONA.id=：FULL JOIN或FULLOUTER FULL BONA.id=如果使用LEFTJOIN如果使用RIGHTJOINLimitMySQLSELECTSELECT*FROMtableLIMIT[offset,]1.分页查询商品表，每页3 selectdistinct、groupby、orderbylimit、joinunionselect中、from后、where中groupby和orderby中无实用意义。1.查询“化妆品”分类下的商品信息WHERE（这个中间表对用户是不可见的；然后根据SELECT的选择列选择相应的列进行返回最终结果。然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。SQLSQLMySQLManagementServeices&ConnectionPool:连接池***负责对接上MySQLServer的客户端请求都会被分配（或创建*而连接线程的主要工作就是负责MySQLServer与客户端的通Servercache等。SQLInterface:SQLSQLselectfromSQLParser:解析器SQLSQL验证和解析。SQL然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。Optimizer:查询优化器用一个例子就可以理解：selectuid,namefromuserwheregenderselect查询先根据whereselectuidname滤Cache和Buffer：查询缓存他的主要功能是将客户端提交给MySQL的select请求的返回结果集cache到内存中，与该query的一个hash值做一个对应。该Query所取数据的基表发生任何数据的变化之后，MySQL会自动使该query的Cache失效。在读写比例非常高的应用系统中，QueryCache对系列小缓存组成的。比如表缓存，记录缓存，key引擎接口模块可以说是引擎接口模块可以说是只有MySQL可以实现其底层数据正是因为它成功地将各种数据处理高度抽象化，才成就了今天MySQL可插拔从图还可以看出，MySQL区别于其他数据库的最重要的特点就是从图还可以看出，MySQL区别于其他数据库的最重要的特点就是引擎MySQL插件式的** **MySQL5.5*查看引擎：show.frm表定义文件.ibd数据文件.frm表定义文件 .myi索引文件锁表锁、专门的地B+索引 +MySQL错误日志、二进制日志、查询日志、慢查询日志和InnoDB引擎Redo日志、中继日志等。错误日志（errlog：记录了运行过程中遇到的所有严重的错误信息,以及MySQL每次启动和关闭的详细信息log-errorlog-warningslog-err二进制日志（binlog：*binlogddldmlselect**如果是DDL语句，则直接记录到binlog日志，而DML*binlogmysql。通用查询日志（generalquerylog：慢查询日志（slowquerylog：和“ib_lgfil1。*undo日志，默认在**是在主从*SQLMySQLSHOWVARIABLESLIKE文件：.ibd和.ibdata文件：用 .myi文件：主要用 MySQL MyISAM引擎BTREE，也就是说BTREE够更 HASH和BTREE*普通索引：重ALTERTABLEALTERTABLEtable_nameADDINDEXindex_nameONCREATEINDEXindex_nameONCREATECREATEUNIQUEINDEXindex_nameONALTERTABLEarticleADDINDEXindex_titme_timeCREATEFULLTEXTINDEXindex_nameONDROPDROPINDEXindex_nameON索引的结得先找到需要优化的SQL。*MySQL数据库有一个“慢查询日志”功能，用来记录查询时间SQL，这将*** ：是否开启慢查询日志，ON为开启，OFF*log-slow- *long_query_time在在MySQL执行SQL语句设置，但是如果重启MySQL的话将失效setglobalslow_query_log=ON;setgloballong_query_time=1;f配置文件，重启MySQLf配置文件，重启MySQL,这种slow_query_log=slow_query_log_file=/var/log/mysql/slow.loglong_query_time=1第二个#MySQL-ttopnmysqldumpslow-st-t10-g“leftjoin”mysqlsla不过此工具已停止，项目介绍页面推荐使用percona-toolkit，下面有介绍mysqlslamysqlsla-lt perlmakeCan'tCan'tlocateExtUtils/MakeMaker.pmin@INCyuminstall-yperl-ExtUtils-CBuilderperl-ExtUtils-Can'tlocateTime/HiRes.pminyuminstall-yperl-Time-MySQL性能优化之查看执行计划MySQL提供了一个EXPLAIN命令,它可以对SELECT语句进行分析,并输出SELECT执行的详细信息,以供开发针对性优化.使用explain这个命令来查看一个这些SQLSQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。可以通过explainMySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。EXPLAIN命令用法十分简单,在SELECT语句前加上explain就可以了,例如:idSELECT查询的标识符.每个SELECTselect_typeSELECT查询的类型.table:查询的是哪个表partitions:匹配的分区typejoin类型possible_keyskeyref:哪个字段或常数与key一起被使用rowsfilteredextra:额外的信息SIMPLE,表示此查询不包含UNION查询或子查询PRIMARY,UNION,表示此查询是UNION的第二或随后的查询DEPENDENTUNION,UNIONUNIONRESULTUNION的结果SUBQUERY,子查询中的第一个DEPENDENTSUBQUERY:子查询中的第一个SELECT,取决于外面的查询.即子查type通常来说,不同的type类型的性能关系如下:ALL<index<range~index_merge<ref<eq_ref<const<每次与之前的表合并行都只在该表一行，这是除了system，const之joinref类似，但