(完整)Mysql燕十八学习笔记.pdf

《(完整)Mysql燕十八学习笔记.pdf》由会员分享，可在线阅读，更多相关《(完整)Mysql燕十八学习笔记.pdf（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 字节。类型 字节 最小值 最大值 （带符号/无符号）（带符号/无符号）Tinyint 1-128 1

2、27 0 255 Smallint 2-32768 32767 0 65535 Mediumint 3-223 223-1 0 224-1 Int 4-231 231-1 0 232-1 Bigint 8-263 263-1 0 264-1 通过占用字节和所存范围分关系合理的选择类型。例：Tinyint 经测试，Tinyint 的默认为有符号-128127 之间，当越界时自动取相应的边界值。若要存 0255 无符号时：列的可选属性：Tinyint(M)unsigned zerofill.M：宽度（在 0 填充的时候才有意义）Unsigned：无符号类型（非负），影响存储范围 Zerofill：

3、0 填充（默认无符号）列可以声明默认值，而且推荐声明默认值 not null default 0；二 小数型/浮点型、定点型 Float(M,D):M 代表总位数，D 代表小数位（6,2）为例：-9999.99 9999.99 Decimal(D,M):三 字符型列 Char：定长，char(M),M 代表宽度，即：可容纳的字符数 0=M=255 Varchar：变长,0=M=65535(约 2W6W 个字符受字符集影响)Text 文本串，(约 2W6W 个字符受字符集影响)区别在哪呢？char 定长：M 个字符，如果存的小于 M 个字符，实占 M 个字符 varchar：M 个字符，存的小于

4、 M 个字符，设为 N，NM，实占 N 个字符 因此，char 定长若存空格，读取时会丢失。而变长不会。Char 利用率小于等于 100%，而 varchar 永远小于 100%，1-2 个字节用于标志实存字符长度。Char(M)如何占据 M 个字符宽度？答：如果实际存储内容不足 M 个，则后面加空格补齐，取出来的时候再把后面的空格去掉，（所以如果内容后面有空格，将会被清除）选择原则：1.空间利用效率（M 固定选 Char）2.速度 速度上：定长速度快些，四 日期时间列类型 Date 日期 Time 时间 Datatime 时间类型 Year 年类型 1.Year 类型：1 个字节表示 190

5、1-2155 年【0000 年表示错误时】如果输入两位，“00-69”表示 20002069，如果输入两位“70-99”表示 19701999 如果记得麻烦，输入四位就行了、2.Date 类型：典型格式,1992-08-12 1000-01-01 9999-12-31 3.Time 类型，典型格式 hh:mm:ss-838:59:59+838:59:59 4.Datetime 典型格式：1989-05-06 12:23:34 1000-01-01 00:00:00 9999-12-31 23:59:59 注意：在开发中很少使用日期时间类型来表示一个需要精确到秒的列，原因：虽然日期时间类型能够精

6、确到秒，而且方便查看。但是计算不便。用时间戳来表示。时间戳:1970-01-01 00:00:00 到当前的秒数 面试 1：当表示性别时，可用 1 或 0 表示男女，如，可能会问到为什么不用 enum 枚举，原因：enum 不符合关系型数据库设计理念，而且字节上不比 tinyint 少。实例：增删改查之案例过程分析 用户注册表单收集，提交数据，注册页面收集到表单的数据后，形成insert 语句，user 表插入该条数据，用户注册完成 前台用户中心，用户新昵称，根据新昵称和用户 id，形成 update 语句，完成昵称修改 管理后台点击会员列表，此时，网页形成 select 语句，查询出所有会员

7、的数据，完成会员的查看 管理员后台选中某用户并且删除，捕捉此用户的 id，根据用户 id 形成相应的 delete 语句 执行 delete 语句，完成用户的删除。阶段总结（一）列类型的概念 数值型 整型 tinyint smallint mediumint int bigint 整型的 unsigned 代表无符号，zerofill 代表 0 填充，M 代表宽度（在 0 填充）浮点型/定点型 float(M,D)unsigned M精度，即总位数，D代表小数位 decimal 比 float 更精确。字符型 Char（M）定长，可存储的字符数，M=255 Varchar(M),变长，可存储的

8、字节数，M=65535 Char 与 varchar 的不同点 Char(M),实占 M 个字符，不够 M 个右侧补空格，取出时，在去除右侧空格，导致右侧真有空格时会丢失。Varchar（M），有 1-2 个字节来标记真实的长度，日期时间型 Year 1901-2155，如果输 2 位，00-69之间+2000，70-99之间+1900 Date YYYY-MM-DD，范围在 1000-01-019999-12-31 Time HH:ii:ss,范围在-838:59:59838:59:59 Datetime YY-MM-DD HH:ii:ss 1000-01-01 00:00:009999-1

9、2-31 23:59:59 开发中的一个问题精确到秒的时间表示方式，不是用datetime，而是用 int 来表示时间戳 用时间戳方便计算，而且方便格式化成不同的显示样式。建表语句 Create table 表明(列名称，列类型列属性默认值，.)engine 引擎名 charset 字符集 增：insert 答：往哪张表增，增那几列，各为什么值？Insert into 表名(列 1，列 2，.列 N)Values（值 1，值 2.值 N）*如果不声明插入的列，则默认插入所有列。改：update 答：修改哪张表，修改那几列，修改成什么值？在哪几行上生效？Update 表名 Set 列 1=值 1

10、，列 2=值 2，.列 N=值 N Where 表达式；删：delete 删除哪张表的数据，删除哪些行？Delete from 表名 Where 表达式 查：select*from 表名 查询的五种语句：where，group，having，order by，limit 一 Where 条件查询 比较运算符 1.In，等于 1N 任意一个。例：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

11、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 where goods_name like 诺基亚%；Select goods_id from goods where goods_name like 诺基亚_；二 Group 分组查询 Group by 作用：把 行按 字段分组 语法：group b

12、y col1,col2colN 运用场合：常见于统计场合，如按栏目计算帖子数，统计每个人的平均成绩等。Group 与统计函数 Max：求最大，min：求最小，sum：求总和，avg：求平均，count：求总行数 练习：1.2.3.4.5.6.7.8.9.10.11.12.13.三 Having *例题：只用一个 select，不用子查询和左连接。四 Order by Order by 排序功能 接一个或多个字段对查询结果进行排序 知识点在本项目案例的运用 对栏目的商品按价格由高到低或由低到高排序 知识点的运用场合描述 各种排序场合，如取热点新闻，发帖状元等。多重排序：Limit 在语句的最后起

13、到了限制条目的作用。Limit offset,N offset：偏移量，N：条目；如果不写，则从头开始写。即：limit 0,N;Truncate table 清空表 思考：取出每个栏目下最贵的商品 解法一：首先建个临时表 g2，将 goods 表导入 g2，此时，g2 中每个栏目的第一个 goods_id 就是该栏目下最贵的那个，再取出每个栏目下的第一个。解法二：思想类似解法一，将 select 后的结果看作是一张表。五 良好的理解模型 1 Where 表达式：把表达式放在行中，看表达式是否为真，2 列：理解成变量，可以运算 3 取出结果：可以理解成一张临时表 六 子查询 1.Where 型

14、子查询 2.From 型子查询 3.Exist 型子查询 一 Where 型子查询 是指把内层的查询结果作为外层查询的比较条件。二 From 型子查询 把内层的查询结果当成表供外层继续查询 使用了 from+where 子查询。三 Exists 子查询 把外层的查询结果拿到内层，看内层的查询是否成立。阶段总结二 查 select Where 表达式 表达式在哪一行成立，哪一行就取出来=，!=/,=,5 就是认为恶意订单，强制把所订的商品数量改成 5 查看所有的触发器 Show triggers;存储引擎 表里的数据存储在硬盘上，具体是如何存储的？存储的方式有很多，同样的一张表的数据对于用户来说

15、，无论什么样的存储引擎，用户看到的数据是一样的，对于服务器来说是不同的。数据库对同样的数据，有不同的存储方式和管理方式，在 mysql中，称为存储引擎。常用的表的引擎有 myisam 和 innodb Myisam：批量插入速度快，不支持事务，锁表 innoDB：批量插入速度相对较慢，支持事务，锁行。全文索引，目前 mysql5.5 都已经支持。讨论 innoDB 支持事务，myisam 不支持事务。事务：事务的 acid 特性 通俗的说，一组操作要么都成功执行，要么都不执行。原子性（atomicity）在所有操作都没有执行完毕之前，其他会话不能够看到中间改变的过程。隔离性（isolation

16、）事务发生之前和发生之后数据的总额依然匹配。一致性（consistency）事务产生的影响不能够撤销持久性（durability）如果出现了错误，事务也不允许撤销，只能够”补偿事务”原子性体现的是不可分割性，不可见性。转账 李三 支出 500，李三-500 赵四 收到 500，赵四+500 关于事务的引擎：选用 innoDB/bdb 查看 mysql 服务器的模式：Show variables like zmodez;语法：开启事务：Sql Sql Commit 提交/rollback 回滚 注意：当一个事务 commit 或者 roolback 之后，事务就已经结束。只要事务没有提交或回滚，

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

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

19、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 中，存储过程和函数的区别：一是名称不同 二是存储过程没有返回值