mysql笔记

1、数据库概念：一台数据库服务器下有多个库，一个库下有一到多张表，表有多行多列的数据；对于数据库存储数据来说有sql标准，很多软件开发遵守sql标准来开发软件用来存储和管理数据，这套软件就可以称为数据库管理系统；postgresql也是一个开源数据库，sql标准也更严格，可以接触；命令行窗口何mysql服务器的关系是c/s的关系即客户端/服务器，其他mysql客户端navcat，mysqlfront，phpMyadmin等；王大爷（数据库管理系统）管理装着档案（表）的档案袋（库）；mysql的信息都在配置文件里；


2、最基本的sql语句：mysql中表/列可以改名但是database不能改名；链接服务器mysql -uusername -ppassword；查看所有数据库show databases；创建库create database 库名 [charset 字符集名]；删除库drop database 库名；选择数据库use 库名；查看表show tables；创建简单表create table 表名(字段1 类型1[]，字段2 类型2[]....字段n 类型n[])engine myisam charset utf8;改表名rename table 旧名字 to 新名字；清空表数据truncate 表名；删除表drop table 表名；查看表结构desc 表名；查看建表语句show create table 表名；
注意：truncate和delete的区别：truncate相当于重新建一张相同结构的新表，速度快，而delete还是在该表中操作即是从删除所有的层面来操作。


3、增（insert，into，values；列与值必须严格对应）：往哪张表添加行，添加哪几列，分别添加什么值；若插入所有列，则可以不声明插入的列，则理解为依次插入所有列，注意尽管id可能自增型也要必须写；eg：insert into class(name,gender,salary) values('JanU','nan',6767),('zhangsan','nv',909098),('lisi','nan',898);


4、改（update，set，字段名=改后的值，where加条件表达式（and，or）为真的改；）：改哪张表的“哪一/些行”哪几列，分别改成什么值；eg：update class set fanbu=99,company='qiandu' where gender='nan' and salary>1000;


5、删（delete，from，where加条件表达式（and，or）为真的改；删除只能删除整行）：删除哪张表的数据，删哪几行；


6、查（select，选列，from，where加条件表达式（and，or）为真的改；）：查哪张表的数据，查哪些列[,查哪些行]；*且不加条件语句则查所有行所有列；取部分列所有行（选列不加条件）；查全部列部分行（*加条件）；


7、建表过程和列类型意义：建表的过程就是一个声明字段的过程；建列时自然想到既能容纳放置的内容但是又不很浪费，故为列分配合适的空间，存储同样的数据，不同的列类型所占据的空间和效率是不一样的，这就是我们建表前要学列类型的意义；所以--重点学列类型的存储范围与占据的字节及二者关系；
列类型（三大列类型） （注意出了整型和浮点型之外都要用单引号引起来）：
(1.数值型（存的范围越大，占内存也越大；注意有符号和无符号，设某类型N字节--N字节，8N位，存储范围0--2^8N-1，-2^(8N-1)--2^(8N-1)-1）：（1）整型：Tinyint，占据空间1个字节（8个位），存储范围-128--127，0--255；Smallint，2字节，存储范围-32768--32767，0--65535；Mediumint，3字节，存储范围-8388608--8388607，0--16777215；Int，4字节，存储范围-2147483648--2147483647，0--4294967295；Bigint，8字节，存储范围-9223372036854775808--9223372036854775807，0--18446744073709551615；int系列不加特殊说明默认为有符号，int系列声明时的参数（M）unsigned zerofill（0填充必须和M配合才有意义，补0的宽度不够用0填充，而且zerofill自动且必为unsigned类型）（2）小数型（浮点型Float(M,D),定点型Decimal
(M,D),M叫“精度”-->代表“总位数”，而D是“标度”-->代表小数位（小数点右边的位数））:用M和D定义小数的存储范围；float最大能存10^38.10^-38范围的数，而且如果M<=24占4个字节，否则占8个字节；定点Decimal把整数部分和小数部分分开存储的，比float精确，float有时会损失；银行都是用整型存分的；
(2.字符串型：char,varchar,text,blob；char和varchar分别称为定长和变长类型，对于char(M)，M代表宽度，0<=M<=255，不够N个长度，用空格在尾部补够N个长度（取出的时候再把右侧的空格删除，有个空格失效问题，而varchar不会），浪费了尾部空间，但是查找数据时，直接可以用数学计算快速查找到数据实占的空间，查找效率高：M字符，而对于varchar(M)，0<=M<=65535(以ascii字符为例，utf822000左右)不用空格补齐，但列内容前，有1-2个字节来标志该列的内容长度，实占空间：i字符+(1--2)字节，注意char(M)和varchar(M)限制的是字符而不是字节，使用哪种编码的字符集就最大存M个字符；text(M)文本类型，最大长度65535个字符，存比较大段的文本，搜索速度稍慢，可以给出可选长度M，不必加默认值；blob是二进制类型用来存储图像音频等二进制信息，意义是2进制0--255都有可能出现，blob在于防止因为字符集的问题导致信息丢失；
(3.日期时间类型（相比字符串型占的空间少）：date 日期 YYYY-MM-DD '1000-01-01'到'9999-12-31' 3个字节 （写入的若系统不识别则自动为'0000-00-00'）；time 时间 HH:MM:SS '-838:59:59'到'838:59:59' 3个字节；datetime 日期时间 YYYY-MM-DD HH:MM:SS '1000-01-01 00:00:00'到'9999-12-31 23:59:59' 8个字节；year 年份类型 YYYY和YY '1901'到'2155'还有个特殊的0000 1个字节；timestamp列的显示格式与datetime列相同，若不给此列插入值，则可以设置默认的系统常量相当于自动赋值；


8、建表及其优化：先分析建表，分析分析到最优后在建表，比如如何选取最合适的列类型；同类的表列在一张表里；比如开发中，会员的信息优化往往是把频繁用到的信息，优先考虑效率，存储到一张表中，不常用的信息和比较占据空间的信息，优先考虑空间占用，存储到辅表；建表语法：建表过程就是一个声明列的过程
create 表名(
列名1 列1类型 列1参数,
列名2 列2类型 列2参数,
.......
.......
列名nn 列n类型 列n参数,
)engine myisam/innodb/bdb charsetutf8/gbk/..;


9、修改表语法之列的增删改（alter table 表名 add/drop/modify .....）：增加列：alter table 表名 add 列名称 列类型 列参数;[加的新列在表最后]，alter table 表名 add 列名称 列类型 列参数 after 某列;[加的新列指定加在某列后]，alter table 表名 add 列名称 列类型 列参数 first;[把新列加在表的最前面]；删除列：alter table 表名 drop 列名；修改列：alter table 表名 modify 旧列名 新类型 新参数；修改列名及列类型：alter table 表名 change 旧列名 新列名 新类型 新参数；


10、表中的查：（as可以让显示的列换成另一个名字，查询可以在结果表 中广义投影出本来没有的列，而where是对原表中数据发挥作用，若相对结果表中的列发挥作用则用having；）


理解查询模型：数据库中把列看成变量(参与运算甚至调用函数处理)，把where后面看成php中if(exp)里的exp表达式，哪一行能让exp为真，哪一行就能取出来，查询结果集在结构上可以当成表看；过程：原始表select--where-->where条件引用的列（或列的运算）只能是原始表中的列，取行，得到一张行数与原始表可能一样，但列与原始表的列一样的中间结果表，此时还不是结果集，此过程是计算机内部执行的--group by-->而后列之间的计算以及分组统计得到结果集表（广义投影），此时得到结果集，此时列可能就不是原始表中的列了--having-->若想对结果集筛选去得到最终结果集表则用having--order by-->对最终结果集排序（即order by要放在where/group by/having后面）--limit-->限制条目；故五个子句的顺序：where---group by---having---order by---limit；解释：先where把行拿出来做投影运算或者是分组运算，然后再having筛选，筛选完了才是拿到最终结果集，此后才能进行排序，排完序才能用limit限制条目；


select 5种子句（where 条件查询，group by 分组，having 筛选，order by 排序，limit 限制结构条数），可以用圆括号；
where：对原表查询；常用的运算符（比较运算符：<,<=,=,in在某散点集合内,!=,<>不等于,>=,>,between..and..在某区间范围内，逻辑运算符：not或!,or或||,and或&&）,where cat_id in (4,11,5)意思是取出cat_id是4,5或者11的商品，不是4到11之间，(4,11)包含的数的集合,不能用or因为逻辑或执行结果为真或假则4or11只能结果为真；between 4 and 11 则表示取出4到11这个区间范围而且包括边界值；and的优先级比or高，模糊查询：列名 like 'xx%'开头为xx的该列的值，%通配任意数量的任意字符，_通配单一字符；
group by子句与聚合函数：group_concat(col)返回属于一组的列值连接组合而成的结果，max()求最大，min()求最小，sum()求总和，avg()求平均，count()求总行数,mysql> select sum(goods_number) as sum from goods;,若count(*)则数出所有行数，若count(某列名),该列不为null的所有行的行数；这些函数与分组配合使用作用巨大；group by 字段名，以哪一列分组；严格来讲，select的a，b列必须在group by a，b，c的列里出现，语义上才没有矛盾；group by取每个分组下第一次出现的行；用时多加小心，否则会得到行的列结果不对应；
having：针对结果表做筛选；
order by：当最终结果集出来后针对最终结果集可以进行排序；排序语法：order by 结果集中的列名1 desc降/asc升,结果集中的列名2 desc降/asc升,....；多字段排序，先以列1排序，再列2排...用逗号隔开；列名也可以是原始表中的列mysql不报错；
limit：限制条目；；limit [offset,] N，offset是跳过的行数（默认为零），N是取的行数；


子查询：如果返回的是一张表必须用别名；
where型子查询：指把内层查询的结果作为外层查询的比较条件；如果where 列=(内层 sql)则内层sql返回的必是单行单列即单个值，如果where 列 in (内层sql)则内层sql只返回单列，可以多行；典型题：查询最大商品 select goods_id,cat_id,goods_name from goods where goods_id=(select max(goods_id) from goods);
from型子查询：因为查询结果集在结构上可以当成表看，故可以把内层的查询结果当成临时表，供外层sql再次查询；注意当成表看必须取个别名；语法：
SELECT ... FROM (subquery) AS name ...典型题：查询每个栏目下最新/最贵商品；
exists子查询：是指把外层sql的结果拿到内层sql去测试，如果内层sql成立，则该行取出；语法结构：SELECT ... FROM table WHERE EXISTS (subquery)，EXISTS (subquery) 只返回 TRUE 或 FALSE；select cat_id,cat_name from category where exists(select * from goods where goods.cat_id=category.cat_id);


11、全/左/右/内连接（最后结果效果是增加了列数）：（解决用1+N笨方法查询多个关联表的操作；）（集合，唯一性无序性，求并集交集笛卡儿积（*），笛卡儿积结果是集合的集合，表就是集合，每行就是一个元素，mysql内部每一行都有个rowid保证了每行都不会一样）---->全连接/全相乘：数据库操作上得到集合相乘的效果即直接用“，”隔开表名查询即可；两表做相乘：从行的角度（2表每一行两两组合），从列的角度（结果集的列，是两表的列相加）；如果两张表连查时，某一列在2张或者2张以上表都有，则需要在列名前指定表名即表名.列名；--全连接+限制条件=耗内存的左连接；-->左连接，语法理解：假设A表在左不动，B表在A表右边滑动，A表与B表通过一个关系来筛选B表对应的行，语法格式：A left join B on 条件（查询数据以A表为准），条件为真则取出B表对应的行，形成的也是一个表，可以看成一张表设为C，可以对C表做查询，自然where，group，having，order by，limit照常使用；
左连接右连接内连接区别：未查到补NULL，查找到多个则多行表示；右连接B left join A on 条件（查询数据以B表为准）=A rigth join B on 条件--->>左右连接可与互换，所以尽量用左连接，出于移植兼容性考虑；内连接：A inner join B on 条件，内连接特点：A表B表都对应，没有不对应的，从集合的角度看，内连接是左右连接的交集；


12、union（最后结果效果是增加了行数）：合并2条或者多条语句结果；语法，sql1 union sql2；union可以从2张表再union....，因为union合并的是结果集，不区分来自于哪一张表；若取出的结果列名不取一样的别名即列名不一致，列的数目一致，则取出的最终列名以第一条sql为准，当表的字段数量不一致时不能用union，故只要表的列的数目一致union就可以用，类型不一样也可以用虽然意义不大；把union出来的看成结果集；如果union后结果有重复（即某2行或n行，所有列值都一样），默认会去重，若不想去重则用union all；


13、mysql数学函数与字符串函数：查手册；数学：floor()；字符串：right；日期时间：now()；控制流函数：if()；加密函数：decode()，aes_encrypt(str,ley)；类型转化函数：cast()；系统信息函数：database()，found_rows()；格式化函数：format()；聚合函数：max()。根据这些找到对应的函数块去查去学。注意：如果mysql函数和php函数都实现某个功能，优先使用哪一个：1.mysql函数肯定是要影响查询速度，应该在建表时通过合理的表结构减少函数的使用，比如email，按@前后拆分，2.如果确实要用函数，应该优先放在业务逻辑层，即php层处理，3.在查询时使用了函数，最大的一个坏处，以date_format(A)则A列的索引将无法使用即如果你针对某列查询而此列用上了函数来判断，此列将不再使用索引，总之where条件中，对某列使用了函数，则此列的索引不发挥作用，故查询速度会变慢。


14、视图 view：可以看成一张虚拟的表，是表通过某种运算得到的一个投影；建视图时，不要指定视图的列名与列类型，因为它是一个影子一种关系继承了上面的字段，；创建视图语法:主要在于查询表，create view 视图名 as select 语句；视图一旦创建完毕就可以像表一样查询；作用：可以简化查询（比如复杂的统计时，先用视图生成一个中间结果，再查询视图），更精细的权限控制（只开放视图权限给别人），数据多分表时可以用到（create view articel as select title from articel1 union select title from articel2...union select title from articel3）；表与视图数据变化时的相互影响问题：表的数据变化将会影响到视图变化，若视图和表是一一对应（根据select关系，从表中取出的行，只能计算出视图中确定的一行，反之，视图中任意抽出一行，反推出表中的确定的一行）的关系时则视图的变化才可以影响表否则出错；视图定义是一直存在的，视图不会占用空间，视图只是和表的一种对应关系-->视图文件只生成了表的结构文件，而没有数据文件和索引文件；对视图的操作和表一样；用order by limit得到的结果不是一一对应的；
algorithm：algorithm=merge(合并查询语句)/temptable(临时表)/undefined(未定义)（eg：create algorithm=temptable view as select语句）


15、乱码问题：
字符集的发展：ascii(0--127，仅英文字符)---->GB2312(组合来自于[129--255][129--255]，6000多个字，仅仅是中文字符)---->GBK(<127的也能用，碰到>128的，就再往后找一个字节，2个字节理解成中文，提供了两万多个字符，英文加中文，日本的字符集jis，ANSI代表本地字符集中文操作GBK日文JIS)---->unicode(世界通用码表，全世界范围的字符统一分配一个标号，这样就不会乱了，用4个字节来编码，2^32，40多亿，但我们常用的集中在前65535个标号里，2个字节就够了，unicode只负责分配编号用的，而且都用4个字节来分配编号)--在不改变你编号的基础上简化字节，把高位浪费的0值用一定的规则舍弃，形成的编码方式-->UTF(负责在网络上传数据，最出名的转换方式为UTF-8一种变长的1--6字节，最高位有几个1就截取几个字节如为0就截取一个字节如为N就截取N个字节，)；乱码的形成：只有两种可能（解码时与实际编码不一致(可修复)，传输过程中编码不一致导致字节丢失(不可修复)）。
mysql字符集参数：（html文件：编码，如存文件时保存为是utf-8，解码，如在文件中告诉浏览器的解码方式<meta http-equiv="Content-type" content="text/html;charset=utf-8">；）；mysql中客户端（client）以GBK为例，服务端（server）以UTF8/GBK为例（在这两个端之间有个连接器connection用来转码）：存入服务器端数据过程：客户端存入数据到服务端存入数据有两个阶段，client--1-->connection--2-->server（阶段1：连接器接收客户端阶段，阶段2：连接器往服务器传送数据），其中阶段1，若连接器字符集设置为GBK则这一阶段不用转字符集，第二阶段时，发现服务端字符集编码为utf8时第二阶段把GBK转成utf8，或，连接器端字符集设置为utf8时，则第一阶段字符集就要转成utf8适应连接器的字符集，而发现服务端也为utf8，则第二阶段不转，总之不管哪种情况都要转一次最终存入服务端为utf8（其中注意乱码的两种情况不要发生），取出数据时反过来即可；服务器端>=connection>=client；client，connection，server都是GBK，此时可以简写成set names GBK;；要想不乱码，需要指定客户端的编码，让连接器不理解错误，这样就不会存入错误，往回取的时候，还要告诉连接器，如果你从服务器端返回，应该给我转什么格式，一共3个参数，客户端发送的编码，连接器使用的编码，获取的返回数据的编码，（明确告诉服务器我的客户端是GBK，set character_set_client=gbk;，告诉连接器使用utf8，set character_set_connection=utf8;，再告诉如果返回值请返回GBK的结果，set character_set_results=gbk;）；网页是utf8，因此client是utf8，表是utf8，因此连接器是utf8，返回值也是utf8，因此set names utf8；牵扯到数据库不想乱码思考三方面：正确指定客户端编码，合理选择连接器编码，正确使用返回内容编码；整个网站不乱码考虑：“网页本身编码，meta信息，client/connection/result的指定”若这些保持一致必无乱码。


16、存储引擎：engine引擎就是mysql存储数据的不同方式；引擎种类：Myisam(速度快，但不支持事务安全)，InnoDB(速度慢，但事务安全))，BDB，Memory，Archive；
事务：属性：原子性（多步操作可分隔，各步操作要么都成功要么都不成功，start transaction;开启事务；commit;提交整个事务；rollback;回滚；理解，a给b转钱，b加了，而a的减除问题了，则b的加回滚回去），一致性（操作前后，值的变化，逻辑上成立，理解，a给b转100元，一定是a减去100元，b加上100元，不能一个100一个不是100），隔离性（事务结束前，每一步的操作带来的影响，别的额会话看不见，理解，x窗口没点击确定，其他窗口看不到x窗口对数据的改变），持久性（事务一旦完成无法撤销，转完前后不可以撤回）










































*注意：客户端传过来的字符集要与mysql服务器字符集对应防止乱码，设置mysql字符集编码用set names 字符集名；mysql遇到;时语句结束；mysql用\c结束语句执行；mysql中#是注释；增删改查针对表来说的；列决定表结构；英文模式输入半角输入；tee D:\1010.sql;意思是把敲的sql语句及其结果输出到一个.sql文件里；计算机里时间与空间是一对矛盾，优化也不过是时间换空间，空间换时间；delete是针对表中的数据而言；注重知识点和技术不要脱节；网站中查写比为20:1；建表时加not null default ‘’/0，不让表中出现null，因为null的比较需要用特殊的运算符（is null，isnot null不可用=或!=，）故不好比较，碰到运算符一律返回null，效率不高影响索引效果；create table minigoods like goods;得到的minigoods结构和goods一样；insert into minigoods select * from goods limit 3；取出来的就叫结果集；可以用圆括号强制各个关键字的优先级；内层order by语句单独使用，不会影响结果集，仅排序，在执行期间，就被mysql的代码分析器给优化掉了，内层的order by必须能够影响结果集时才有意义，比如配合limit使用（此问题在union中发现）；mysql服务器端使用字符集为utf8；使用session时的bom头问题，将编辑软件编码该为不要加bom的utf8；mysql中utf8不加-，其他场景里都是utf-8；








计算机的负数：计算机为了表示一个数是负数，会把最高位（左侧）的0/1，当成符号来看，如为0则是正数1为负数，七位范围为0-127，计算机中的负数不是按照“后面绝对值直接乘-1得到的”，而是用补码规则换算的即负数=绝对值位-128；因此一个字节有符号的范围为-128--127，无符号范围为0--255；


把一个表中的数据导入另一个表中：
mysql> insert into text.goods
    -> select
    -> goods_id,cat_id,goods_sn,goods_name,click_count,goods_number,market_price
,shop_price,add_time,is_best,is_new,is_hot
    -> from eshop.goods;