mysql三大列类型

整值型:

      Tinyint/ smallint/ mediumint/int/ bigint(M) unsigned zerofill

整型系列所占字节与存储范围的关系.

定性: 占字节越多,存储范围越大.

下图: 是具体的数字分析

为什么同一个类型会有两个范围:

因为通过添加unsigned可以设置为无符号的,直接从0开始计数,像我们建表的时候遇到订单号,学号,id等等这类字段是没有事负数的可能的,那么如果不设置为unsigned就白白浪费了一半!

Unsigned: 代表此列为无符号类型, 会影响到列的存储范围. (范围从0开始)

(不加unsinged, 则该列默认是有符号类型,范围从负数开始)

Zerofill: 代表0填充, 即: 如果该数字不足参数M位, 则自动补0, 补够M位.

1: 如果没有zerofill属性, 单独的参数M,没有任何意义.(不能通过设置M来限制类型的传入的最大数!)

2:如果设置某列为zerofill,则该列已经默认为 unsigned,无符号类型.

小数型:

Float(M,D),decimal(M,D)

  M叫"精度" ---->代表"总位数",而D是"标度",代表小数位.(小数右边的位数) 

比如:float(5,3)   可以为:12.345   不能为123.45

浮点数占多大的空间呢

答：　float　能存10＾３８　，10^-38

如果M<=24, 点4个字节,否则占8字节

用来表示数据中的小数,除了float---浮点.

还有一种叫定点decimal,定点是把整数部分, 和小数部分,分开存储的.

比float精确,他的长度是变化的.

会发现float会出现丢精度的问题!!

字符串类型:

Mysql 字符串类型

Char 定长类型

Char(M)  , M 代表宽度, 0<=M<=255之间

例:Char(10)  ,则能输入10个字符.

Varchar 变长类型

Varchar(M), M代表宽度, 0<=M<=65535(以ascii字符为例,utf822000左右)

Char(M)如何占据M个字符宽度?

答: 如果实际存储内容不足M个,则后面加空格补齐.

取出来的时候, 再把后面的空格去掉.(所以,如果内容最后有空格,将会被清除).

速度上: 定长速度快些

注意: char(M),varchar(M)限制的是字符,不是字节.

即 char(2) charset utf8, 能存2个utf8字符. 比如'中国'char与varchar型的选择原则:

1:空间利用效率, 四字成语表, char(4),

个人简介,微博140字, varchar(140)

 Char 与 varchar相关实验

Text : 文本类型,可以存比较大的文本段,搜索速度稍慢.

因此,如果不是特别大的内容,建议用char,varchar来代替.

Text 不用加默认值 (加了也没用)

Blob,是二进制类型,用来存储图像,音频等二进制信息.

意义: 2进制,0-255都有可能出现.

Blob在于防止因为字符集的问题,导致信息丢失.

比如:一张图片中有0xFF字节, 这个在ascii字符集认为非法,在入库的时候,被过滤了.

日期类型:

year 年(1字节)    95/1995,  [1901-2155],

在insert时,可以简写年的后2位,但是不推荐这样.

[00-69] +2000

[70-99] + 1900,  

即: 填2位,表示 1970 - 2069

Date 日期  1998-12-31

范围: 1000/01/01 ,9999/12/31

Time 时间  13:56:23

范围: -838:59:59 -->838:59:59

datetime 时期时间  1998-12-31 13:56:23

范围: 1000/01//01 00:00:00  ---> 9999:12:31 23:59:59

timestamp 

时间戳:

是1970-01-01 00:00:00 到当前的秒数.

一般存注册时间,商品发布时间等,并不是用datetime存储,而是用时间戳.

因为datetime虽然直观,但计算不便

union合并两条或多条SQL的结果:

语法:

Sql1 union sql2

能否从2张表查询再union呢?

答:可以,union 合并的是"结果集",不区分在自于哪一张表.

问:取自于2张表,通过"别名"让2个结果集的列一致.

那么,如果取出的结果集,列名字不一样,还能否union.

答:可以,如下图,而且取出的最终列名,以第1条sql为准

问:union满足什么条件就可以用了?

答:只要结果集中的列数一致就可以.

问: union后结果集,可否再排序呢?

答:可以的.

Sql1 union sql2 order by 字段

注意: order by 是针对合并后的结果集排的序(sql1 和sql2 使用order by 没有任何意义!!最终外层order by 还会排序!)

union会去除重复的数据,如果要保留全部数据需要使用unionall.

视图:

在查询中,我们经常把查询结果 当成临时表来看.

View是什么? View可以看一张虚拟表. 是表通过某种运算得到的一个投影.

既然视图只是表的某种查询的投影,所以主要步骤在于查询表上.

查询的结果命名为视图就可以了.

视图的定义:

视图是由查询结果形成的一张虚拟表.

视图的创建语法:

Create view 视图名 as  select 语句;

视图的删除语法:

Drop view 视图名

为什么要视图?

答:1:可以简化查询

2: 可以进行权限控制

把表的权限封闭,但是开放相应的视图权限,视图里只开放部分数据.

视图的修改

Alter view 视图名 as select xxxxxx

视图与表的关系

视图是表的查询结果,自然表的数据改变了,影响视图的结果.

视图改变了呢?

0: 视图增删改也会影响表

1: 但是,视图并是总是能增删改的.

视图某种情况下,是可以修改的.

要求: 视图的数据和表的数据 11对应. 就像函数的映射.

表-->推出视图对应的数据

视图-->推出表对应的数据

对于视图insert还应注意,

视图必须包含表中没有默认值的列. 

以这个例子而言,平均价来自于多行的的shop_price的计算结果.

如果pj列的值的变子,映射过去,到底修改哪几行shop_price?

视图的algorithm

Algorithm = merge/ temptable/ undefined

Merge: 当引用视图时,引用视图的语句与定义视图的语句合并.

Temptable:当引用视图时,根据视图的创建语句建立一个临时表

Undefined:未定义,自动,让系统帮你选.

Merge,意味着视图只是一个规则,语句规则, 当查询视图时,

把查询视图的语句(比如where那些)与创建时的语句where子句等合并,分析.

形成一条select 语句.

例: 创建视图的语句:

mysql> create view g2 as select goods_id,cat_id,goods_name,shop_price from goods where shop_price>2000

查询视图的语句:

select * from g2  where shop_price < 3000;

最终执行的语句:

select goods_id,cat_id,goods_name,shop_price from goods where shop_price > 2000 and shop_price < 3000

而temptable是根据创建语句瞬间创建一张临时表,

然后查询视图的语句从该临时表查数据.

create algorethm=temptable view g2 as select goods_id,cat_id,goods_name,shop_price from goods where shop_price > 2000

查询视图的语句:

 select * from g2  where shop_price < 3000;

最终执行的2句话: 取数据并放在临时表,然后去查临时表.

Select  goods_id,cat_id,goods_name,shop_price from goods where shop_price > 2000;

========> temptable

再次查临时表

Select * from temptable where shop_price< 3000

存储引擎:

一部电影, mp4,wmv,avi,rmvb,flv, 同样的一部电影,在硬盘上有不同的存储格式,

所占空间与清晰程度也不一样.

表里的数据存储在硬盘上,具体如何存储的?

存储的方式方法也有多种.

对于用户来说       同样一张表的数据,无论用什么引擎来存储,用户看到的数据是一样的.

但是对于服务器来说,有区别.

常用的表的引擎

Myisam ,批量插入速度快, 不支持事务,锁表

Innodb, 批量插入相对较慢,支持事务,锁行.

设置存储引擎:

create table category (

 cat_id smallint unsigned auto_increment primary key,

 cat_name varchar(90) not null default '',

 parent_id smallint unsigned

 )engine myisam charset utf8;

engine 设置存储引擎

charset 设置默认字符集

事务:

通俗的说事务: 指一组操作,要么都成功执行,要么都不执行.---->原子性

在所有的操作没有执行完毕之前,其他会话不能够看到中间改变的过程-->隔离性

事务发生前,和发生后,数据的总额依然匹配----->一致性

事务产生的影响不能够撤消------>持久性

如果出了错误,事务也不允许撤消, 只能通过"补偿性事务"

转账

李三: --->支出 500, 李三 -500

赵四: ---->收到500, 赵四 +500

关于事务的引擎:

选用innodb /bdb

开启事务:  start transaction;

Sql....

Sql....

Commit 提交

rollback 回滚

注意: 当一个事务commit,或者rollback就结束了

注意:有一些语句会造成事务的隐式的提交,比如 start transaction

触发器:

能监视: 增,删,改

触发操作: 增,删,改

四要素:

监视地点

监视事件

触发时间

触发事件

创建触发器的语法

Create trigger triggerName

After/before 

insert/update/delete  

on 表名

For each row #这句话是固定的

Begin

Sql语句;  # 一句或多句,insert/update/delete范围内

End;

删除触发器的语法:

Drop trigger 触发器名

查看触发器

Show triggers

如何在触发器引用行的值

对于insert而言, 新增的行 用new 来表示,

行中的每一列的值 ,用new.列名来表示.

对于 delete来说, 原本有一行,后来被删除,

想引用被删除的这一行,用old,来表示,  old.列名,就可以引用被删行中的值.

对于update来说,

被修改的行,

修改前的数据 ,用 old来表示, old.列名引用被修改之前行中的值

修改后的数据,用new 来表示, new.列名引用被修改之后行中的值

触发器里after 和before的区别

After 是先完成数据的增,删,改再触发,

触发的语句晚于监视的增,删,改,无法影响前面的增删改动作.

Before是先完成触发,再增删改,

触发的语句先于监视的增,删,改发生,我们有机会判断,修改即将发生的操作.

典型案例:

对于所下订单,进行判断,如果订单的数量 > 5 ,就认为是恶意订单,

强制把所订的商品数量改成5

存储过程:

概念类似于函数,就是把一段代码封装起来,

当要执行这一段代码的时候,可以通过调用该存储过程来实现.

在封装的语句体里面,可以用if/else, case,while等控制结构.

可以进行sql编程.

查看现有的存储过程:

Show procedure status

删除存储过程

Drop procedure 存储过程的名字

调用存储过程

Call 存储过程名字();

while循环:

case:

if

repeat:

存储过程的参数:

存储过程可以有0个或多个参数，用于存储过程的定义。

3种参数类型：

　　IN输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）

　　OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

　　INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

1、in输入参数

mysql> delimiter $$-- 告诉mysql结束标志更改为$$
mysql> create procedure in_param(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;

mysql> set @p_in=1;

mysql> call in_param(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+

+------+
| P_in |
+------+
|    2 |
+------+

mysql> select @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+

#以上可以看出，p_in在存储过程中被修改，但并不影响@p_id的值，因为前者为局部变量、后者为全局变量.

2、out输出参数

mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;

mysql> set @p_out=1;

mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
　　#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
|     2 |
+-------+

mysql> select @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+
　　#调用了out_param存储过程，输出参数，改变了p_out变量的值

3、inout输入参数

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;

mysql> set @p_inout=1;

mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+

+---------+
| p_inout |
+---------+
|       2 |
+---------+

mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+

#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量.

注意：

　　①如果过程没有参数，也必须在过程名后面写上小括号

　　　　例：CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……

　　②确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理

墙裂建议：

　　>输入值使用in参数；

　　>返回值使用out参数；

　　>inout参数就尽量的少用。