mysql学习总结

mysql学习笔记mysqldump 备份备份单独库下面的所有表mysqldump -uuname -ppassword db 地址+备份文件名称备份某库下的几个表mysqldump -uuname -ppassword db table1 table2 table3 等 地址+备份文件名称备份多个数据库mysqldump -uuname -ppassword -B db1 db2 地址+备份文件名称备份所有数据库mysqldump -uuname -ppassword -A导出的是 建表语句和 insert语句注意：恢复文件的 地址形式 如 e:library.sql文件名称 后没有分号！恢复1 登陆到 mysql命令行的状态下库级文件的恢复mysql source 备份文件地址表级文件的恢复mysql use 库名mysql source 备份文件地址2 不登陆的情况下库级的mysql -u用户名 -p密码 备份文件地址表级mysql -u用户名 -p密码 库名通配任意字符_ -通配一个字符练习：1 % -通配任意字符2 _ -通配一个字符笛卡尔积与连接查询连接查询 （左连接 右连接 内连接）笛卡尔乘积集合特性： 确定性 无序性 唯一性一张表可以看做是一个集合，每行数据相当于集合的一个元素Union时 去掉重复原理 就是集合元素的唯一性表中存在完全相同的两行 是因为表内部存在rowid进行区分笛卡尔积如果aA, bBA*B = ( a, b);例如A=(1,2,3,4,5);B=(11,12);那么A*B(1,11), (2,11), (3,11), (4,11), (5,11),(1,12), (2,12), (3,12), (4,12), (5,12);A有M个元素B有N个元素那么A*B有M*N个元素同理表A有M行 表B有N行那么A*B有M*N行例如：ta tb两表笛卡尔积通过分析可以看出tb表的ab cd每个分别和ta的a b c d组合一遍左连接1连上表2连接条件例如：selectgood_id,goods.cat_id,goods_name,shop_pricefromgoods left join categoryon good.cat_id = category.cat_id;字段名重复那么需要加表前缀，否则会报错；error 1052(23000) column * in field list is ambiguous最后两行可以看 作是一张表。左连接语法：select列1，列2，列Nfromtable 1left jointable 2ontable 1列= table 2列；on后面的条件只要是条件就可以 可以不是等于 但是常用是等于。将from后面的当做一个普通表看待。右连接语法：select列1，列2，列Nfromtable 1right jointable 2ontable 1列= table 2列；内连接innerselect列1，列2，列Nfromtable 1inner jointable 2ontable 1列= table 2列；左连接 右连接 内连接的区别和联系：左连接和右连接 是以左边的表为基准。通俗的讲，先将左边的表全部显示出来，然后右边的表id与左边表id相同的记录就“拼接”上去，比如说id为1的记录。如果没有匹配的id，比如说t1中id为2的t2中就没有。那边就以null显示。右外连接过程正好相反。内连接 只有一条记录。内连接就是只取出符合过滤条件的记录 也就是t1.id=t2.id 那么符合t1.id=t2.id的记录只有id=1这一条，所以只显示一条。 不像外连接，是将你作为基准的表（左外连接就是左边表为基准，右外连接就是右边表为基准）的所有行都显示出来。以下为网上的一点资料（粘贴的）LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即使在第二个（右边）表中并没有相符值的记录。语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2说明：table1, table2参数用于指定要将记录组合的表的名称。field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。compopr参数指定关系比较运算符：=， ， = 或 。如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误mysql新的学习模式1 通过备份数据库文件查看备份代码学习sql 语句！你会发现 备份的语句和你 原本 建表语句是 略有不同的 更加标准 和 详细2 备份的语句更加规范并且通过其文本明白备份原理；即 通过将建表 建库 inser 语句等 进行备份并且 可以表示文档注释 等等。子查询（完整）from把内层的查询结果作为外层查询比较查询例：查最新商品（以goods_id最大为最新）1 select * from goods where goods_id =最大的gooods_id;2 select * from goods where goods_id = (select max(goods_id) from goods);where表达式则取出哪一行表达式在哪一行成立=,!= ,=in ,between andor,and,nothaving表达式数据在表中，表在硬盘或者内存中以文件的形式存在where就是针对文件发生作用查询结果，也可以看做是一张表，去文件一般临时存在于缓冲区having就是针对查询结果发挥着作用group by分组一般和统计函数（sum ,avg,min,max）结合使用order by默认是升序的如果想显示声明升序排列可用asc来声明可以字段名 desc来声明降序排可以用来查询商品等价格以及新旧注：可以按照多条件排序,前面的条件优先！order by列1asc/desc,列2 asc/desc;exists把外层的查询结果代入内层，看内层是否成立查询商品栏目select * from category where exists(select * from goods where goods.cat_id =categor.cat_id);cat_id字段相同需要加上表前缀limit在语句的最后起到限制显示结果条目个数limit offset,N;offset表示偏移量（跳过几个）N表示取出的条目个数如果offset不写相当于0#在mysql里表示注释列操作（增加修改删除列）列操作（增加 修改 删除列）创建表单表单描述增加列1alter table列名add列声明；列声明列名称 列类型 【列属性】列默认值增加的列默认在表的的最后一列2alter table列名add列声明after列；声明 新增的列在原来哪个列后面3alter table列名add列声明first；如果放在第一列 那么 在列声明后加first即可发现问题auto_increment的问题插入id使用auto_increment时要求列声明里列必须被定义为key修改列alter table表名change被改变的列 列声明原来：修改列：删除列alter table表名drop列名 ；mysqlunion的用法union的用法（一般用于多张表的union）合并查询结果将两次或者两次以上的结果合并在一起要求：两次查询的列数一致推荐，查询每一列，相对应的列类型是一样的多次sql语句取出的列名可以不一致，此时以取第一个sql的列名为准。如果不同的语句中取出的行有每个列的值都相同，那么相同的行将被合并成一行（去重复）如果不想去重复union加上all创建表格create table ta(id char(1) default null,num int not null default 0) engine=InnoDBcharset=utf8;insert into ta(id,num)values(a,10),(b,20),(c,30),(d,40);create table tb(id char(1) default null,num int not null default 0) engine=InnoDBcharset=utf8;insert into tb(id,num)values(a,50),(b,60),(c,70),(d,80);1合并 两张表格2 合并两张表单把相同id下的数值sum求和发现问题使用聚合函数时不许有空格Sum(num)Sum (num)将会把sum识别为表报该数据库下不存在该表单错误Error 1630(42000)注意：如果子句里有order by | limit需要union两边加小括号例如（selectgood_id,cat_id,goods_name，shop_price from goods _id =where cat_id = 4 order byshop_price desc）union (selectgood_id,cat_id,goods_name，shop_price from goods _id =wherecat_id = 5 order by shop_price desc)；但是容易有误解决方案1order by尽量放在最后使用即岁最终合并后的结果进行排序（selectgood_id,cat_id,goods_name，shop_price from goods _id =where cat_id = 4）union(selectgood_id,cat_id,goods_name，shop_price from goods _id =where cat_id = 5order byshop_price desc；2每个子语句加上limitmysql视图视图的定义：视图 就是由查询结果组成的一张虚拟表视图的创建语法：create view视图名asselect 语句;显示表 将会被显示出来可以看到“表结构”和视图内容删除视图语法：drop view视图名;视图修改alter view视图名 as select语句；创建视图的原因：1、 简化查询2、可以进行权限控制比如：可以把 商品名称 等创建视图，把商品价格等“隐藏”。把表的权限保留，只把视图提供给别人。3、大的数据分表时，可以用到数据。比如:表的行数大于200 万行时，开始变慢，可以把一张表的数据拆成4张表（视图）来存放。4、可以将多张表合并成一张表（视图）。结合union .注意：1、可以完全把视图当作表看待。2、视图源于表，如果表的内容改变了，那么视图的内容也会跟着发生改变。3、视图的增删改也将会影响表，但是视图并不是总是可以增删改的。视图和表数据是一一对应时，可以修改。对于视图insert视图必须包含所有表中没有默认值的列。视图的algorithm(算法)Algorithm = merge/temptable/undefinedmerge当引用视图时，引用视图的语句和定义视图的语句合并。意味着，视图只是一个规则，当查询视图时，把查询视图的语句（比如where等）与创建时的语句where子句合并分析，形成一条select语句。相当于把语句效果叠加。temptable当引用视图时，根据视图的创建语句，建立一个临时表。是把 根据创建时候的语句瞬间创建临时表，查询视图的语句，从该临时表查询。undefined未定义自动，让系统帮你选择。mysql字符集（CHARACTERSET）和校对集（COLLATE）字符集（CHARACTER SET）和校对集（COLLATE）字符集：可以设置 服务器默认字符集数据库默认字符集表默认字符集甚至到列默认字符集某个级别没有指定，那么继承上级。mysql4.1及其之后的版本，对字符集的支持分为四个层次:服务器(server)，数据库(database)，数据表(table)和连接(connection)：character_set_server：这是设置服务器使用的字符集character_set_client：这是设置客户端发送查询使用的字符集character_set_connection：这是设置服务器需要将收到的查询串转换成的字符集character_set_results：这是设置服务器要将结果数据转换到的字符集，转换后才发送给客户端整个过程：-client(如php程序)发送一个查询；- 服务器收到查询，将查询串从character_set_client转换到character_set_connection，然后执行转换后的查询；- 服务器将结果数据转换到character_set_results字符集后发送回客户端。1、告诉服务器发送的数据是什么类型的。character_set_client2、告诉转换器，转换什么编码character_set_connection3、查询结果用什么编码character_set_results如果三者都是字符集N那么可以简写为set names N;例如：客户端字符集和连接字符集如果connection和服务器的字符集，比client小时，容易丢失数据。create table时的charset是服务器的字符编码校对集：可以理解为，排序规则等。一个字符集可能有多种校对集合；-官方文档-1、数据库字符集和校对CREATE DATABASEdb_nameDEFAULT CHARACTER SETcharset_nameDEFAULT COLLATEcollation_name例如：CREATE DATABASEdb_nameDEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;MySQL这样选择数据库字符集和数据库校对规则：如果指定了CHARACTER SET X和COLLATE Y，那么采用字符集X和校对规则Y。如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。否则，采用服务器字符集和服务器校对规则。2、表字符集和校对每一个表有一个表字符集和一个校对规则，它不能为空。为指定表字符集和校对规则，CREATE TABLE和ALTER TABLE语句有一个可选的子句：CREATE TABLEtbl_name(column_list)DEFAULT CHARACTER SETcharset_nameCOLLATEcollation_nameALTER TABLEtbl_nameDEFAULT CHARACTER SETcharset_name COLLATEcollation_nameMySQL按照下面的方式选择表字符集和校对规则：如果指定了CHARACTER SET X和COLLATE Y，那么采用CHARACTER SET X和COLLATE Y。如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。否则，采用服务器字符集和服务器校对规则。如果在列定义中没有指定列字符集和校对规则，则默认使用表字符集和校对规则。表字符集和校对规则是MySQL的扩展;在标准SQL中没有。3、列字符集和校对每一个“字符”列（即，CHAR、VARCHAR或TEXT类型的列）有一个列字符集和一个列校对规则，它不能为空。列定义语法有一个可选子句来指定列字符集和校对规则：col_nameCHAR | VARCHAR | TEXT (col_length)CHARACTER SETcharset_nameCOLLATEcollation_name例如：CREATE TABLE Table1(column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci);MySQL按照下面的方式选择列字符集和校对规则：如果指定了CHARACTER SET X和COLLATE Y，那么采用CHARACTER SET X和COLLATE Y。如果指定了CHARACTER SET X而没有指定COLLATE Y，那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。否则，采用表字符集和服务器校对规则。CHARACTER SET和COLLATE子句是标准的SQL。示例1：表和列定义CREATE TABLE t1(c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;在这里我们有一个列使用latin1字符集和latin1_german1_ci校对规则。是显式的定义，因此简单明了。需要注意的是，在一个latin2表中存储一个latin1列不会存在问题。示例2：表和列定义CREATE TABLE t1(c1 CHAR(10) CHARACTER SET latin1) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;这次我们有一个列使用latin1字符集和一个默认校对规则。尽管它显得自然，默认校对规则却不是表级。相反，因为latin1的默认校对规则总是latin1_swedish_ci，列c1有一个校对规则latin1_swedish_ci（而不是latin1_danish_ci）。示例3：表和列定义CREATE TABLE t1(c1 CHAR(10) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;我们有一个列使用一个默认字符集和一个默认校对规则。在这种情况下，MySQL查找表级别来确定列字符集和校对规则。因此，列c1的字符集是latin1，它的校对规则是latin1_danish_ci。示例4：数据库、表和列定义CREATE DATABASE d1DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;USE d1;CREATE TABLE t1(c1 CHAR(10);我们创建了一个没有指定字符集和校对规则的列。我们也没有指定表级字符集和校对规则。在这种情况下，MySQL查找数据库级的相关设置。（数据库的设置变为表的设置，其后变为列的设置。）因此，列c1的字符集为是latin2，它的校对规则是latin2_czech_ci。在SQL语句中使用COLLATE 使用COLLATE子句，能够为一个比较覆盖任何默认校对规则。COLLATE可以用于多种SQL语句中。下面是一些例子：使用ORDER BY：SELECT kFROM t1ORDER BY k COLLATE latin1_german2_ci;使用AS：SELECT k COLLATE latin1_german2_ci AS k1FROM t1ORDER BY k1;使用GROUP BY：SELECT kFROM t1GROUP BY k COLLATE latin1_german2_ci;使用聚合函数：SELECT MAX(k COLLATE latin1_german2_ci)FROM t1;使用DISTINCT：SELECT DISTINCT k COLLATE latin1_german2_ciFROM t1;使用WHERE：SELECT *FROM t1WHERE _latin1 Mller COLLATE latin1_german2_ci = k;SELECT *FROM t1WHERE k LIKE _latin1 Mller COLLATE latin1_german2_ci;使用HAVING：SELECT kFROM t1GROUP BY kHAVING k = _latin1 Mller COLLATE latin1_german2_ci;COLLATE子句优先COLLATE子句有较高的优先级（高于|），因此下面两个表达式是等价的：x | y COLLATE zx | (y COLLATE z)触发器（trigger）作用： 可以监视 增删改操作并触发 增删改操作创建触发器(delimiter $)create trigger 触发器名称after/before insert/update/delete on 表名for each rowbegin sql 语句一个或者多个语句，范围在 insert/update/delete内;end ($)在此需要重新设置结束符号delimiter $创建第一个简单触发器：效果g 表 羊自动减少 3只发现问题：设置结尾符号语句 不可带 分号否则设置不成功解决方案如何在触发器中引用行的值对于insert 而言 ，新增的行用new来表示。行中的每一列的值，用 new.列名来表示。对于delete而言，删去的行用 old来表示。行中的每一列的值，用old.列名来表示。对于update而言，更新前的行用 old来表示。old.列名表示更新前的引用。更新后的行，用new.列名来表示更新后的引用。触发器的删除drop trigger 触发器名称触发器的显示清空表单truncate 表名购买下单实例1、创建满足条件的新的触发器2、执行 insert（下第一笔订单 购买 4 号商品，数量为2）3、查看o订单4、g商品表中的 对应id号得商品减少 完整购买案例1、增加一个订单，库存相应减少2、删除一个订单，库存相应增加3、修改下单时，商品表数量相应发生变化触发器里 after 和 before的区别after 是先完成数据的操作，再出发时间，不能对前面的增删改作出影响。如果用的是 before,可以对数据进行审查。是先于触发再完成增删改，可以审查、判断、即将发生的增删改操作。before应用创建触发器时即可对插入数据进行影响create trigger buybefore insert on ofor each rowbegin if new.much 5 then set new.much=5; end if; update g set num = num -new.much where id = new.gid;end$索引注意:1、 不要过度索引2、 索引条件列（where后面最频繁的条件列）3、 尽量索引散列值，过于集中的值不要索引。索引类型1、普通索引（index）：仅仅是加快查询速度2、唯一索引(unique index)：行上的值不能重复3、主键索引(primary key)：主键不能索引主键索引必定是唯一的，唯一索引不一定是主键，一张表上只能一个主键，可以有一个或者多个唯一索引。4、 全文索引(fulltext index)：在mysql 默认情况下对于中文作用不大。查看 一张表上的索引show index from 表名 (/G可以横着显示)索引不知道名称默认以索引的列名作索引建立索引alter table 表名 add index/unique/fulltext 索引名(列名) 索引名可以不写 ，不写默认与列名相同。alter table 表名 add primary key （列名）创建索引示例：为 tel列 创建普通索引为email列 添加唯一索引为intro列添加全文索引为id列添加主键删除索引alter table drop index 索引名示例：删除email（唯一）索引删除主键索引全文索引使用查看匹配度select 列名, match (索引) against ( 索引词) from表名;新发现 as 支持 汉字全文停止字：全文索引，不针对非常频繁的词，做索引。针对汉语意义不大，因为因为英文单词可以依赖空格等标点来判断单词界限，进而对单词进行索引，而中文mysql无法识别单词界限。以下表列出了默认的全文停止字：asableaboutaboveaccordingaccordinglyacrossactuallyafterafterwardsagainagainstaintallallowallowsalmostalonealongalreadyalsoalthoughalwaysamamongamongstanandanotheranyanybodyanyhowanyoneanythinganywayanywaysanywhereapartappearappreciateappropriatearearentaroundasasideaskaskingassociatedatavailableawayawfullybebecamebecausebecomebecomesbecomingbeenbeforebeforehandbehindbeingbelievebelowbesidebesidesbestbetterbetweenbeyondbothbriefbutbycmoncscamecancantcannotcantcausecausescertaincertainlychangesclearlycocomcomecomesconcerningconsequentlyconsiderconsideringcontaincontainingcontainscorrespondingcouldcouldntcoursecurrentlydefinitelydescribeddespitediddidntdifferentdodoesdoesntdoingdontdonedowndownwardsduringeacheduegeighteitherelseelsewhereenoughentirelyespeciallyetetcevenevereveryeverybodyeveryoneeverythingeverywhereexexactlyexampleexceptfarfewfifthfirstfivefollowedfollowingfollowsforformerformerlyforthfourfromfurtherfurthermoregetgetsgettinggivengivesgogoesgoinggonegotgottengreetingshadhadnthappenshardlyhashasnthavehaventhavingheheshellohelphenceherherehereshereafterherebyhereinhereuponhersherselfhihimhimselfhishitherhopefullyhowhowbeithoweveridillimiveieifignoredimmediateininasmuchincindeedindicateindicatedindicatesinnerinsofarinsteadintoinwardisisntititditllitsitsitselfjustkeepkeepskeptknowknowsknownlastlatelylaterlatterlatterlyleastlesslestletletslikelikedlikelylittlelooklookinglooksltdmainlymanymaymaybememeanmeanwhilemerelymightmoremoreovermostmostlymuchmustmymyselfnamenamelyndnearnearlynecessaryneedneedsneitherneverneverthelessnewnextninenonobodynonnonenoonenornormallynotnothingnovelnownowhereobviouslyofoffoftenohokokayoldononceoneonesonlyontoorotherothersotherwiseoughtouroursourselvesoutoutsideoveroverallownparticularparticularlyperperhapsplacedpleasepluspossiblepresumablyprobablyprovidesquequiteqvratherrdrereallyreasonablyregardingregardlessregardsrelativelyrespectivelyrightsaidsamesawsaysayingsayssecondsecondlyseeseeingseemseemedseemingseemsseenselfselvessensiblesentseriousseriouslysevenseveralshallsheshouldshouldntsincesixsosomesomebodysomehowsomeonesomethingsometimesometimessomewhatsomewheresoonsorryspecifiedspecifyspecifyingstillsubsuchsupsuretstaketakentelltendsththanthankthanksthanxthatthatsthatsthetheirtheirsthemthemselvesthenthencetheretheresthereaftertherebythereforethereintheresthereuponthesetheytheydtheylltheyretheyvethinkthirdthisthoroughthoroughlythosethoughthreethroughthroughoutthruthustotogethertootooktowardtowardstriedtriestrulytrytryingtwicetwoununderunfortunatelyunlessunlikelyuntiluntoupuponususeusedusefulusesusingusuallyvaluevariousveryviavizvswantwantswaswasntwaywewedwellwerewevewelcomewellwentwerewerentwhatwhatswhateverwhenwhencewheneverwherewhereswhereafterwhereaswherebywhereinwhereuponwhereverwhetherwhichwhilewhitherwhowhoswhoeverwholewhomwhosewhywillwillingwishwithwithinwithoutwontwonderwouldwouldwouldntyesyetyouyoudyoullyoureyouveyouryoursyourselfyourselveszero注意！换服务器导入数据：大数据量时，先把索引去掉，导入后，统一加上索引。索引加快查询速度，降低增删改的速度。额外：show create table 表名 可以查看 创建表的语句发现问题使用全文索引时加上引擎存储引擎最常见的是:Mysam和 innoDB数据库的engine显示MyISAM ，批量插入速度快，不支持事务，锁表InnoDB 批量插入相对较慢，支持事务，锁行-官方文档-beginMySQL插件式存储引擎的体系结构下述存储引擎是最常用的：MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似