mysql笔记

1、mysql 判断字符串是否为数字：

CHAR_LENGTH(字符串) = CHAR_LENGTH(CAST(字符串 AS UNSIGNED INTEGER))

2、mysql UNION使用：

select1 union select2

select1和select2 的字段名称及字段的顺序必须保持一致，并且结果会默认去掉重复的记录

select1 union all select2

select1和select2 的字段名称及字段的顺序必须保持一致，结果不会去重。

3、mysql 使用外键实现级联删除和更新（当删除主表的一条记录时，从表中与之对应的记录也会被删除）

以获取京东订单的数据结构举例：

主表：jd_order

CREATE TABLE `jd_order` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_id` varchar(45) DEFAULT NULL,
`order_source` varchar(45) DEFAULT NULL,
`customs` varchar(45) DEFAULT NULL,
`customs_model` varchar(45) DEFAULT NULL,
`vender_id` varchar(45) DEFAULT NULL,
`pay_type` varchar(45) DEFAULT NULL,
`order_total_price` varchar(45) DEFAULT NULL,
`order_seller_price`varchar(45) DEFAULT NULL,
`order_payment` varchar(45) DEFAULT NULL,
`freight_price` varchar(45) DEFAULT NULL,
`seller_discount` varchar(45) DEFAULT NULL,
`order_state` varchar(45) DEFAULT NULL,
`order_state_remark` varchar(45) DEFAULT NULL,
`delivery_type` varchar(45) DEFAULT NULL,
`invoice_info` varchar(45) DEFAULT NULL,
`order_remark` varchar(45) DEFAULT NULL,
`order_start_time` varchar(45) DEFAULT NULL,
`order_end_time` varchar(45) DEFAULT NULL,
`modified` varchar(45) DEFAULT NULL,
`vender_remark` varchar(45) DEFAULT NULL,
`balance_used` varchar(45) DEFAULT NULL,
`payment_confirm_time` varchar(45) NOT NULL,
`waybill` varchar(45) DEFAULT NULL,
`logistics_id` varchar(45) DEFAULT NULL,
`vat_invoice_info` varchar(45) DEFAULT NULL,
`parent_order_id` varchar(45) DEFAULT NULL,
`pin` varchar(45) DEFAULT NULL,
`return_order` varchar(45) DEFAULT NULL,
`order_type` varchar(45) DEFAULT NULL,
`store_order` varchar(45) DEFAULT NULL,
`flag1` tinyint(4) NOT NULL DEFAULT '0',
`flag2` tinyint(4) NOT NULL DEFAULT '0',
`flag3` tinyint(4) NOT NULL DEFAULT '0',
`REMARK1` varchar(255) DEFAULT NULL,
`REMARK2` varchar(255) DEFAULT NULL,
`REMARK3` varchar(255) DEFAULT NULL,
`REMARK4` varchar(255) DEFAULT NULL,
`NEWUID` int(10) unsigned DEFAULT NULL,
`NEWTID` varchar(45) DEFAULT NULL,
`NEWTIME` char(17) DEFAULT NULL,
`UPDUID` int(10) unsigned DEFAULT NULL,
`UPDTID` varchar(45) DEFAULT NULL,
`UPDTIME` char(17) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=260251 DEFAULT CHARSET=utf8;

从表：jd_ordercoupon

CREATE TABLE `jd_ordercoupon` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`order_id` varchar(45) DEFAULT NULL,
`sku_id` varchar(45) DEFAULT NULL,
`coupon_type` varchar(45) DEFAULT NULL,
`coupon_price` varchar(45) DEFAULT NULL,
`flag1` tinyint(4) NOT NULL DEFAULT '0',
`flag2` tinyint(4) NOT NULL DEFAULT '0',
`flag3` tinyint(4) NOT NULL DEFAULT '0',
`REMARK1` varchar(255) DEFAULT NULL,
`REMARK2` varchar(255) DEFAULT NULL,
`REMARK3` varchar(255) DEFAULT NULL,
`REMARK4` varchar(255) DEFAULT NULL,
`NEWUID` int(10) unsigned DEFAULT NULL,
`NEWTID` varchar(45) DEFAULT NULL,
`NEWTIME` char(17) DEFAULT NULL,
`UPDUID` int(10) unsigned DEFAULT NULL,
`UPDTID` varchar(45) DEFAULT NULL,
`UPDTIME` char(17) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `order_id` (`order_id`),
CONSTRAINT `jd_ordercoupon` FOREIGN KEY (`order_id`) REFERENCES `jd_order` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=260251 DEFAULT CHARSET=utf8;

以上是创建两个表jd_order和jd_ordercoupon，

CONSTRAINT `jd_ordercoupon` FOREIGN KEY (`order_id`) REFERENCES `jd_order` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE

这段sql语句表示的就是将主表jd_order的order_id字段做为从表jd_ordercoupon的外键，以达到级联删除和更新的效果。

4、insert into:

1)插入新行时使用如下语句插入，字段与值必须一 一对应。表中未列出的字段值为其默认值。

INSERT INTO table_name (字段1，字段2，字段3) VALUES(值1，值2，值3)

2)插入新行时使用如下语句插入，必须将表中所有值都列出。

INSERT INTO table_name VALUES(值1，值2，值3，...)

5、update：

1)、UPDATE table_name SET 字段1=新值1,字段2=新值2，字段3=新值3 WHERE 字段1=值1

2)、update test  set 字段1=case when 条件1 then 值1   when 条件2 then 值2  end 

6、delete

DELETE FROM 表名称 WHERE 列名称=值

 7、存储过程

创建存储过程

create procedure P_LoadPageData(in pageIndex int,in pageSize int,out total int)
begin
declare iPageIndex int;  //mysql中在存储过程中声明变量
set iPageIndex=(pageIndex-1)*pageSize;  //给变量赋值
select * from t_game order by ID limit iPageIndex,pageSize;   //mysql中一定范围条数数据用limit,sql server中用top。limit关键字后面只能跟整数，不能跟表达式（如果需要用到表达式就将表达式赋值给一个变量）
select count('a') into total from t_game;   //mysql中将查询结果赋值给变量，sql server 中select total=count('a') from t_game;
end

调用存储过程

call P_LoadPageData(1,2,@a);
select @a;

 存储过程优点：

1）、比Donet直接写sql脚本执行少了一块解析编译的过程，效率更快一点（不是很明显）

2）、使用存储过程，业务逻辑改变的时候只需要改变存储过程，然后业务逻辑就发生变化了，不需要修改C#代码

3）、传递sql脚本数据相对小

缺点：

1）、使用存储过程，数据库可移植性差

2）、把业务放到存储过程里面，相当于业务处理的压力放到数据库里面去

存储过程的使用注意：

1）、因为存储过程是让数据库来处理逻辑，这就增加了数据库的负担，而数据库达到瓶颈之后进行集群分流的难度比起在业务层来做集群，做分流的难度要大得多，所以建议

除非在非常有必要的时候用存储过程，其余时候能不用存储过程就不用。在开发的过程中最好都使用sql脚本的方式来实现逻辑，避免使用存储过程。