Mysql燕十八学习笔记.doc

《Mysql燕十八学习笔记.doc》由会员分享，可在线阅读，更多相关《Mysql燕十八学习笔记.doc（80页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Mysql三大列类型1. 数值型 整型 Tinyint、Smallint、Mediumint、Int、Bigint 小数型 Float(D,M),Decimal(D,M)2. 字符串型 Char(M) Varchar(M) Text 文本类型3. 日期时间类型Date 日期Time 时间Datetime 时间类型Year年类型一 整型列的字节与存储范围从数学上讨论tinyint，建表时既能足够存放，又不浪费空间。即：(1). 占据空间(2). 存储范围Tinyint微小的列类型，1字节。类型字节最小值最大值（带符号/无符号）（带符号/无符号）Tinyint1-1281270255Smallin

2、t2-3276832767065535Mediumint3-223223-10224-1Int4-231231-10232-1Bigint8-263 263-10264-1通过占用字节和所存范围分关系合理的选择类型。例：Tinyint经测试，Tinyint的默认为有符号-128127之间，当越界时自动取相应的边界值。若要存0255无符号时：列的可选属性：Tinyint(M) unsigned zerofill.M：宽度（在0填充的时候才有意义）Unsigned：无符号类型（非负），影响存储范围Zerofill： 0填充（默认无符号）列可以声明默认值，而且推荐声明默认值 not null def

3、ault 0；二 小数型/浮点型、定点型Float(M,D):M代表总位数，D代表小数位（6,2）为例：-9999.99 9999.99Decimal(D,M) :三 字符型列Char：定长，char(M),M代表宽度，即：可容纳的字符数0=M=255Varchar：变长,0=M=65535(约2W6W个字符受字符集影响)Text文本串，(约2W6W个字符受字符集影响)区别在哪呢？char定长：M个字符，如果存的小于M个字符，实占M个字符varchar：M个字符，存的小于M个字符，设为N，NM，实占N个字符因此，char定长若存空格，读取时会丢失。而变长不会。Char利用率小于等于100%，而

4、varchar永远小于100%，1-2个字节用于标志实存字符长度。Char(M)如何占据M个字符宽度？答：如果实际存储内容不足M个，则后面加空格补齐，取出来的时候再把后面的空格去掉，（所以如果内容后面有空格，将会被清除）选择原则：1. 空间利用效率（M固定选Char）2. 速度速度上：定长速度快些，四 日期时间列类型Date 日期Time 时间Datatime 时间类型Year 年类型1. Year类型：1个字节表示1901-2155年【0000年表示错误时】如果输入两位，“00-69”表示20002069，如果输入两位“70-99”表示19701999如果记得麻烦，输入四位就行了、2. Da

5、te类型：典型格式,1992-08-121000-01-01 9999-12-313. Time 类型，典型格式 hh:mm:ss-838:59:59+838:59:594. Datetime 典型格式：1989-05-06 12:23:341000-01-01 00:00:00 9999-12-31 23:59:59注意：在开发中很少使用日期时间类型来表示一个需要精确到秒的列，原因：虽然日期时间类型能够精确到秒，而且方便查看。但是计算不便。用时间戳来表示。时间戳: 1970-01-01 00:00:00 到当前的秒数面试1：当表示性别时，可用1或0表示男女，如，可能会问到为什么不用enum枚

6、举，原因：enum不符合关系型数据库设计理念，而且字节上不比tinyint少。实例：增删改查之案例过程分析用户注册表单收集，提交数据，注册页面收集到表单的数据后，形成insert语句，user表插入该条数据，用户注册完成前台用户中心，用户新昵称，根据新昵称和用户id，形成update语句，完成昵称修改管理后台点击会员列表，此时，网页形成select语句，查询出所有会员的数据，完成会员的查看管理员后台选中某用户并且删除，捕捉此用户的id，根据用户id形成相应的delete语句执行delete语句，完成用户的删除。阶段总结（一）列类型的概念数值型 整型 tinyint smallint mediu

7、mint int bigint 整型的unsigned代表无符号，zerofill代表0填充，M代表宽度（在0填充） 浮点型/定点型 float(M,D) unsigned M精度，即总位数，D代表小数位 decimal比float更精确。 字符型Char（M）定长，可存储的字符数，M=255Varchar(M) ,变长，可存储的字节数，M=65535Char与varchar的不同点Char(M),实占M个字符，不够M个右侧补空格，取出时，在去除右侧空格，导致右侧真有空格时会丢失。Varchar（M） ，有1-2个字节来标记真实的长度，日期时间型 Year 1901-2155，如果输2位，00

8、-69之间+2000，70-99之间+1900Date YYYY-MM-DD，范围在1000-01-019999-12-31Time HH :ii:ss,范围在-838:59:59838:59:59Datetime YY-MM-DD HH:ii:ss 1000-01-01 00:00:009999-12-31 23:59:59开发中的一个问题精确到秒的时间表示方式，不是用datetime，而是用int来表示时间戳用时间戳方便计算，而且方便格式化成不同的显示样式。建表语句Create table 表明( 列名称 ，列类型列属性默认值，.)engine 引擎名 charset 字符集增：inser

9、t答：往哪张表增，增那几列，各为什么值？Insert into 表名(列1，列2，.列N)Values（值1，值2.值N）*如果不声明插入的列，则默认插入所有列。改：update答：修改哪张表，修改那几列，修改成什么值？在哪几行上生效？Update表名Set列1 = 值1，列2 = 值2，. 列N =值NWhere 表达式；删：delete删除哪张表的数据，删除哪些行？Delete from 表名Where 表达式查：select * from 表名查询的五种语句：where，group，having，order by，limit一 Where 条件查询比较运算符1. In ，等于1N任意一个

10、。例：select goods_id from goods where cat_id in(4,5);2. Between 值1 and 值2，表示在值1和值2之间例：select goods_id from goods where cat_id between 1 and 5;逻辑运算符1. Not 逻辑非例：select good_id from goods where cat_id not in(4,5);2. Or 逻辑或3. And 逻辑与模糊查询：案例：想查找诺基亚开头的所有商品Like像，% 通配任意字符 _ 单个字符Select goods_id from goods wher

11、e goods_name like 诺基亚%；Select goods_id from goods where goods_name like 诺基亚_；二 Group分组查询Group by作用：把 行按 字段分组语法： group by col1,col2colN运用场合：常见于统计场合，如按栏目计算帖子数，统计每个人的平均成绩等。Group与统计函数Max：求最大，min： 求最小，sum：求总和，avg：求平均，count：求总行数练习：1.2.3.4.5.6.7.8.9.10.11.12.13.三 Having*例题：只用一个select，不用子查询和左连接。四 Order byOr

12、der by 排序功能接一个或多个字段对查询结果进行排序知识点在本项目案例的运用对栏目的商品按价格由高到低或由低到高排序知识点的运用场合描述各种排序场合，如取热点新闻，发帖状元等。多重排序：Limit在语句的最后起到了限制条目的作用。Limit offset,N offset：偏移量，N：条目；如果不写，则从头开始写。即：limit 0,N;Truncate table清空表思考：取出每个栏目下最贵的商品解法一：首先建个临时表g2，将goods表导入g2，此时，g2中每个栏目的第一个goods_id就是该栏目下最贵的那个，再取出每个栏目下的第一个。解法二：思想类似解法一，将select后的结果

13、看作是一张表。五 良好的理解模型1 Where 表达式：把表达式放在行中，看表达式是否为真，2 列：理解成变量，可以运算3 取出结果：可以理解成一张临时表六 子查询1. Where型子查询2. From型子查询3. Exist型子查询一 Where型子查询是指把内层的查询结果作为外层查询的比较条件。二 From型子查询把内层的查询结果当成表供外层继续查询使用了from + where子查询。三 Exists子查询把外层的查询结果拿到内层，看内层的查询是否成立。阶段总结二查selectWhere 表达式表达式在哪一行成立，哪一行就取出来=，!=/,=,5就是认为恶意订单，强制把所订的商品数量改成

14、5查看所有的触发器Show triggers;存储引擎表里的数据存储在硬盘上，具体是如何存储的？存储的方式有很多，同样的一张表的数据对于用户来说，无论什么样的存储引擎，用户看到的数据是一样的，对于服务器来说是不同的。数据库对同样的数据，有不同的存储方式和管理方式，在mysql中，称为存储引擎。常用的表的引擎有myisam和innodbMyisam：批量插入速度快，不支持事务，锁表innoDB：批量插入速度相对较慢，支持事务，锁行。全文索引，目前mysql5.5都已经支持。讨论innoDB支持事务，myisam不支持事务。事务：事务的acid特性通俗的说，一组操作要么都成功执行，要么都不执行。原

15、子性（atomicity）在所有操作都没有执行完毕之前，其他会话不能够看到中间改变的过程。隔离性（isolation）事务发生之前和发生之后数据的总额依然匹配。一致性（consistency）事务产生的影响不能够撤销持久性（durability）如果出现了错误，事务也不允许撤销，只能够”补偿事务”原子性体现的是不可分割性，不可见性。转账李三 支出500，李三-500赵四 收到500，赵四+500关于事务的引擎：选用innoDB/bdb查看mysql 服务器的模式：Show variables like zmodez;语法：开启事务：SqlSqlCommit提交/rollback回滚注意：当一个

16、事务commit或者roolback之后，事务就已经结束。只要事务没有提交或回滚，事务处理都会看不见.事物的基本原理（了解）数据库的备份和恢复Mysql索引设有N条随机记录，不用索引，平均查找N/2次，那么用了索引之后？Btree索引，Log2NHash索引，1索引与优化之索引的好处与坏处好处：加快了查询速度坏处:降低了增删改的速度增大了表的文件大小（索引文件甚至可能比数据文件还大）大数据量导入时，应先去掉索引，再导入，最后统一加索引。索引的使用原则：不过度索引、索引条件列（where后面最频繁的条件比较适合索引）、索引散列值，过于集中的值不要索引。例如：给性别男女加索引意义不大。索引类型：普

17、通索引，主键索引，唯一索引，全文索引。.frm是结构文件.myd是数据文件.myi是索引文件索引文件比数据文件大是一种很常见的事情1. 普通索引 index 仅仅是加快查询速度2. 唯一索引 unique index行上的值不能重复3. 主键索引 primary key不能重复，主键索引必唯一，但是唯一索引不一定是主键，一张表上只能有一个主键，但是可以用一个或者多个索引。4. 全文索引：fulltext index查看一张表上的所有索引：Show index from 表名;建立索引：(索引名和索引在哪个列上发挥作用)Alter table 表名 add index/unique/fullte

18、xt索引名(列名);主键索引有一点区别：Alter table 表名 primary key (列名); 删除索引：Alter table 表名 drop index 索引名；索引是根据数据建立的目录。全文索引在mysql的默认情况下，对中文的意义不大。这么查是一行一行查，效率低。针对每一个单词建立一个索引，停止词的问题：（一会讲）停止词是种非常常见的词，是不会加索引的。关于全文索引的用法：Match（全文索引名）against（keyword）；关于全文索引的停止词：全文索引不针对非常频繁的词做索引，如，this，is等全文索引对中文的意义不大因为英文有空格，标点符号来拆成单词，进而对单词进行索引，而对于中文，没有空格来隔开单词mysql无法识别每个中文词。存储过程：类似于函数，就是把一段代码封装起来，当要执行这一段代码时可以调用该存储过程来实现。在封装语句体里面，可以用if/else,case,while等控制结构，可以进行sql编程。查看现有的存储过程Show procedure status删除存储过程Drop procedure 存储过程的名字调用存储过程Call 存储过程的名字()；（括号内可以放参数）第一个存储过程，体会封装sql的用法。第二个存储过程，体会控制结构。在mysql中，存储过程和函数的区别：一是名称不同二是存储过程没有返回值