解决MySQL Got a packet bigger than 'max_allowed_packet' bytes
问题在一次性向mysql插入的数据过大，可在my.ini中修改max_allowed_packet的配置项
修改my.ini如下：
max_allowed_packet=16M
重启mysql：
电脑->右键管理->服务->mysql重启
mysql排序按照中文文字的首字母进行排序
例如：下面使用字段common_producer_herb_info_name按照首字母a-z进行排序
SELECT * FROM e_common_producer_product_grade_info ORDER BY CONVERT(common_producer_herb_info_name USING gbk ) COLLATE gbk_chinese_ci ASC
mysql索引
1.适合创建索引情况
a.字表和父表之间的依赖关系字段, 即主键和外键
b.表中经常出现在where字句中的字段
c.经常出现在GROUP BY字句中的分组字段
2.创建索引
a.普通索引
创建表示添加
CREATE TABLE t_name(
INDEX|KEY index_name(c_name)
);
修改表时添加
ALTER TABLE t_name ADD INDEX|KEY index_name(c_name);
直接创建
CREATE INDEX|KEY ON t_name(c_name);
b.唯一索引
创建表示添加
CREATE TABLE t_name(
UNIQUE INDEX|KEY index_name(c_name)
);
修改表时添加
ALTER TABLE t_name ADD UNIQUE INDEX|KEY index_name(c_name);
直接创建
CREATE UNIQUE INDEX|KEY ON t_name(c_name);
c.全文索引
创建表示添加
CREATE TABLE t_name(
FULLTEXT INDEX|KEY index_name(c_name)
);
修改表时添加
ALTER TABLE t_name ADD FULLTEXT INDEX|KEY index_name(c_name);
直接创建
CREATE FULLTEXT INDEX|KEY ON t_name(c_name);
d.多列索引
创建表示添加
CREATE TABLE t_name(
INDEX|KEY index_name(c_name1, c_name2)
);
修改表时添加
ALTER TABLE t_name ADD INDEX|KEY index_name(c_name1, c_name2);
直接创建
CREATE INDEX|KEY ON t_name(c_name1, c_name2);
3.删除索引
DROP INDEX index_name ON t_name;
4.查看索引
SHOW CREATE TABLE t_name;
mysql多表查询
1.内连接查询
a.自然内连接=笛卡尔积-不等值条件记录-重复字段
SELECT field1, field2 FROM t1 INNER JOIN t2 ON condition INNER JOIN t3 ON condition
b.等值内连接=笛卡尔积-不等值条件记录
SELECT field1, field2 FROM t1 INNER JOIN t2 ON condition INNER JOIN t3 ON condition
c.不等值内连接=笛卡尔积-等值条件记录
SELECT field1, field2 FROM t1 INNER JOIN t2 ON condition INNER JOIN t3 ON condition
2.外连接查询
a.左外连接=笛卡尔积-不等值条件记录+左表未匹配记录
SELECT field1, field2 FROM t1 LEFT OUTER JOIN t2 ON condition
b.右外连接=笛卡尔积-不等值条件记录+右表未匹配记录
SELECT field1, field2 FROM t1 RIGHT OUTER JOIN t2 ON condition
c.全外连接=笛卡尔积-不等值条件记录+左表未匹配记录+右表未匹配记录
SELECT field1, field2 FROM t1 FULL OUTER JOIN t2 ON condition
例如：原料出库和成品出库的查询过程中使用了左连接和统计分组
代码如下所示：
/**
* 根据基地和时间段获取原料出库列表
* @param $common_producer_info_id
* @param int $wms_check_begin_at
* @param int $wms_check_end_at
* @return array
*/
public static function getWmsMaterialOutSumList($common_producer_info_id, $wms_check_begin_at=0, $wms_check_end_at=0){
$wms_check_end_at = !empty($wms_check_end_at) ? $wms_check_end_at : time();
//注意，一个出库单可以出库多个入库单的部分药材，即出库单不记录入库单号，只有出库详情才记录入库单号
//分组时不应该使用入库单号，而应该使用出库单号，因为一个入库单可以分多次出库
$sql = "SELECT
modet.wms_stock_detail_info_relation_good_in_sheet_number in_sheet_number,
mo.common_producer_info_id producer_id,
mo.common_producer_info_name producer_name,
mo.common_producer_herb_info_id info_id,
mo.common_producer_herb_info_name info_name,
mo.common_producer_herb_grade_info_id grade_id,
mo.common_producer_herb_grade_name grade_name,
SUM(
IFNULL(
modet.wms_material_out_sheet_detail_out_number,
0
)
) AS sum_out_package_number,
SUM(
IFNULL(
modet.wms_material_out_sheet_detail_out_weight,
0
)
) AS sum_out_weight,
SUM(
IFNULL(
modet.wms_material_out_sheet_detail_storage_loss_weight,
0
)
) AS sum_out_loss_weight
from {{%wms_material_out_sheet_detail}} modet
LEFT JOIN {{%wms_material_out_sheet}} mo ON mo.id = modet.wms_material_out_sheet_id
WHERE
modet.is_del = 0
AND mo.is_del = 0
AND mo.wms_material_out_sheet_status = 1
AND mo.common_producer_info_id = :common_producer_info_id
AND mo.wms_material_out_sheet_outgoing_date >= :wms_check_begin_at
AND mo.wms_material_out_sheet_outgoing_date <= :wms_check_end_at
GROUP BY
mo.common_producer_herb_info_id,
mo.common_producer_herb_grade_info_id,
modet.wms_material_out_sheet_number,
modet.wms_stock_detail_info_relation_good_in_sheet_number";
$list = Yii::$app->db->createCommand($sql)
->bindValue(':common_producer_info_id', $common_producer_info_id)
->bindValue(':wms_check_begin_at', $wms_check_begin_at)
->bindValue(':wms_check_end_at', $wms_check_end_at)->queryAll();
return $list;
}
/**
* 获取成品出库相关信息
* @param $common_producer_info_id
* @param int $wms_check_begin_at
* @param int $wms_check_end_at
* @return array
*/
public static function getWmsProductOutSumList($common_producer_info_id, $wms_check_begin_at=0, $wms_check_end_at=0){
$wms_check_end_at = !empty($wms_check_end_at) ? $wms_check_end_at : time();
//注意：成品出库详情没有成品出库ID的关联字段，只能使用成品出库单号关联
//分组时不应该使用入库单号，而应该使用出库单号，因为一个入库单可以分多次出库
$sql = "SELECT
po.wms_product_in_sheet_number in_sheet_number,
po.common_producer_info_id producer_id,
po.common_producer_info_name producer_name,
po.common_producer_herb_info_id_product info_id,
po.common_producer_herb_info_name_product info_name,
po.common_producer_herb_grade_info_id_product grade_id,
po.common_producer_herb_grade_name_product grade_name,
SUM(
IFNULL(
podet.wms_product_out_detail_info_out_number,
0
)
) AS sum_out_package_number,
SUM(
IFNULL(
podet.wms_product_out_detail_info_out_weight,
0
)
) AS sum_out_weight,
SUM(
IFNULL(
podet.wms_product_out_detail_info_storage_loss_weight,
0
)
) AS sum_out_loss_weight
FROM {{%wms_product_out_detail_info}} podet
LEFT JOIN {{%wms_product_out_sheet}} po ON po.wms_product_out_sheet_number = podet.wms_product_out_sheet_number
WHERE
podet.is_del = 0
AND po.is_del = 0
AND po.wms_product_out_sheet_status = 1
AND po.common_producer_info_id = :common_producer_info_id
AND po.wms_product_out_sheet_product_out_date >= :wms_check_begin_at
AND po.wms_product_out_sheet_product_out_date <= :wms_check_end_at
GROUP BY
po.common_producer_herb_info_id_product,
po.common_producer_herb_grade_info_id_product,
po.wms_product_out_sheet_number,
podet.wms_product_in_sheet_number";
$list = Yii::$app->db->createCommand($sql)
->bindValue(':common_producer_info_id', $common_producer_info_id)
->bindValue(':wms_check_begin_at', $wms_check_begin_at)
->bindValue(':wms_check_end_at', $wms_check_end_at)->queryAll();
return $list;
}
3.子查询
a.单行单列子查询(用于where语句中)
例：
SELECT * FROM t1 WHERE f1 >(SELECT f2 FROM t2 WHERE f3='a');
b.单行多列子查询(用于where语句中)
例：
SELECT * FROM t1 WHERE (f1,f3) =(SELECT f1,f2 FROM t2 WHERE f3='a');
c.多行单列子查询(用于where语句中)
(IN, NOT IN, =ANY, >ANY, >=ANY, <ANY, <=ANY, >ALL, >=ALL, <ALL, <=ALL, EXISTS, NOT EXISTS)
例：
SELECT * FROM t1 WHERE f1 IN (SELECT f1 FROM t2);
d,多行多列子查询(用于from语句中, 作为临时表)
例：
SELECT * FROM t1 INNER JOIN (SELECT f1, COUNT(), AVG(f2)) t2 ON condition;
4.合并查询
a.去重合并查询
SELECT f1, f2, fn FROM t1 UNION SELECT f1, f2, fn FROM t2 UNION SELECT f1, f2, fn FROM t3
b.不去重合并查询
SELECT f1, f2, fn FROM t1 UNION ALL SELECT f1, f2, fn FROM t2 UNION ALL SELECT f1, f2, fn FROM t3
注意：
mysql统计函数对于NULL值得处理
COUNT(*)包含NULL的数量, COUNT(field)忽略NULL值得数量, COUNT(NULL)为0
SUM(field), AVG(field), MAX(field), MIN(field)如果有一条记录中field的值为NULL，则结果为NULL
GROUP BY将NULL作为新行
解决方法为将NULL值处理为0，使用IFNULL(field, 0)
例如：获取成品出库与成品入库的差额即为库存
SELECT vms.info_name AS info_name, vms.grade_name AS grade_name, ( vms.sum_in_weight - vms.sum_out_weight ) AS weight, ( vms.sum_in_price - vms.sum_out_price ) AS price FROM ( SELECT pi.common_producer_herb_info_name_product info_name, pi.common_producer_herb_grade_name_product grade_name, SUM( IFNULL( pi.wms_product_in_sheet_in_weight, 0 ) ) AS sum_in_weight, SUM( IFNULL( pi.wms_product_in_sheet_unit_price, 0 ) ) AS sum_in_price, SUM( IFNULL( po.wms_product_out_sheet_out_weight, 0 ) ) AS sum_out_weight, SUM( IFNULL( po.wms_product_out_sheet_total_price, 0 ) ) AS sum_out_price FROM e_wms_product_in_sheet AS pi LEFT JOIN e_wms_product_out_sheet AS po ON pi.common_producer_herb_info_id_product = po.common_producer_herb_info_id_product AND pi.common_producer_herb_grade_info_id_product = po.common_producer_herb_grade_info_id_product AND po.is_del = 0 WHERE pi.is_del = 0 GROUP BY pi.common_producer_herb_info_id_product, pi.common_producer_herb_grade_info_id_product ) vms
mysql 常用函数
1.字符串函数
lower(str)
upper(str)
trim(str)
ltrim(str)
rtrim(str)
left(str, n)
right(str, n)
substring(str, n, m)
lpad(str, n, pad)
rpad(str, n, pad)
repeat(str, n)
replace(str, old, new)
insert(str, n, m, insstr)
cancat(str1, str2, str3,)
strcmp(str1, str2)
2.数值函数
abs(x)
ceil(x)
floor(x)
round(x,y)
truncate(x,y)
mod(x,y)
rand()
3.日期函数
curdate()
curtime()
now()
unix_timestamp(date)
from_unixtime()
year(date)
week(date)
hour(time)
minute(time)
4.系统信息函数
version()
database()
user()
last_insert_id()
mysql运算符
1.算术运算符
+
-
*
/
%
2.比较运算符
>
<
=
!=(<>)
>=
<=
is null, is not null
between and, not between and
in, not in
like, not like
regexp
3.逻辑运算符
not, !
&&, and
||, or
xor
4.位运算符
~
&
|
^
<<
>>
mysql事务
1.事务的性质(ACID)
a.原子性(Automicity)
事务中所有的操作只能是完全提交或者完全回滚
b.一致性(Consistency)
事务在完成时，数据表必须从一种状态变更为另外一种状态
c.隔离性(Isolation)
事务中的操作语句所做的修改必须与其他事务所做膝盖相隔离
d.持久性(Durability)
事务完成后，所做的修改对数据是永久的
2.事务控制语句
SET AUTOCOMMIT = 0
START TRANSACTION | BEGIN
COMMIT
ROLLBACK
3.事务隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE
4.锁机制
a.锁的类型
b.锁粒度
mysql存储过程与函数
1.为什么使用存储过程和函数
区别：
存储过程没有返回值，函数必须有返回值
优点：
a.允许标准组件式编程，提高了sql语句的重用性，共享性和可移植性
b.执行速度快，减少网络流量
c.本身是一种安全机制
2.存储过程和函数的创建
a.存储过程的创建
CREATE PROCEDURE p_name(
IN|OUT|INOUT param_name1 type,
IN|OUT|INOUT param_name2 type
) LANGUAGE SQL
|DETERMINISTIC
|NOT DETERMINISTIC
|CONTAINS SQL
|NO SQL
|READS SQL DATA
|MODIFIES SQL DATA
|SQL SECURITY DEFINER
|SQL SECURITY INVOKER
|COMMIT 'string'
b.函数的创建
CREATE FUNCTION f_name(
param_name1 type,
param_name2 type
) LANGUAGE SQL
|DETERMINISTIC
|NOT DETERMINISTIC
|CONTAINS SQL
|NO SQL
|READS SQL DATA
|MODIFIES SQL DATA
|SQL SECURITY DEFINER
|SQL SECURITY INVOKER
|COMMIT 'string'
例：
DELIMITER $
CREATE FUNCTION f_employee(empo INT(11)) RETURNS DOUBLE(10,2)
COMMIT '查询雇员工资'
BEGIN
RETURN (SELECT sal FROM t_employee WHERE t_employee.empno=empno);
END
$
DELIMITER ;
3.存储过程和函数的删除
DROP PROCEDURE p_name
DROP FUNCTION f_name
4.存储过程和函数的修改
a.存储过程的修改
ALTER PROCEDURE p_name(
IN|OUT|INOUT param_name1 type,
IN|OUT|INOUT param_name2 type
) LANGUAGE SQL
|DETERMINISTIC
|NOT DETERMINISTIC
|CONTAINS SQL
|NO SQL
|READS SQL DATA
|MODIFIES SQL DATA
|SQL SECURITY DEFINER
|SQL SECURITY INVOKER
|COMMIT 'string'
b.函数的修改
ALTER FUNCTION f_name(
param_name1 type,
param_name2 type
) LANGUAGE SQL
|DETERMINISTIC
|NOT DETERMINISTIC
|CONTAINS SQL
|NO SQL
|READS SQL DATA
|MODIFIES SQL DATA
|SQL SECURITY DEFINER
|SQL SECURITY INVOKER
|COMMIT 'string'
5.存储过程和函数的查询
SHOW CREATE PROCEDURE p_name
SHOW CREATE FUNCTON f_name
mysql触发器的操作
1.为什么使用触发器
能加强数据表中数据的完整性约束和业务规则
2.触发器的创建
CREATE TRIGGER t_name
BEFORE|AFTER INSERT|DELATE|UPDATE
ON table_name FOR EACH ROW
trigger_sql
例：
DELIMITER $
CREATE TRIGGER tri_diarytime2 AFTER INSERT ON t_dept for each row
BEGIN
INSERT INTO t_diary VALUES(NULL, `t_dept`, now())
INSERT INTO t_diary VALUES(NULL, `t_dept`, now())
END
$
DELIMITER ;
3.触发器的删除
DROP TRIGGER t_name
4.触发器的查看
SHOW TRIGGERS
mysql视图操作
1.为什么使用视图
原因：
a.实现查询的简化
b.提高安全性
特点：
a.是不同的表的在逻辑意义上的抽象
b.视图本身的建立和删除和修改不影响基本表
c.视图内容的添加，删除，修改影响基本表
d.视图来自多表时，不允许添加和删除数据
2.创建视图
create view v_name as [select语句]
a.封装使用常量
b.封装使用聚合函数
c.封装使用排序语句
d.封装使用多表查询语句
3.删除视图
drop view v_name1, v_name2
4.修改视图
create or replace view v_ name as [select语句]
alter view v_name as [select语句]
5.查看视图
show table status from db_name 查看视图详细信息
show create view v_nmae 查看视图定义信息
desc v_name 查看视图定义信息
6.利用视图操作基本表
和操作数据表的方法一样
mysql中if语句的使用
, wms_product_in_sheet_in_weight, ORDER BY id ASC;
第十 mysql导出数据库
生产环境
mysqldump -hrm-2ze8mpi5i65429l1qo.mysql.rds.aliyuncs.com -uchgg_erp_prod -pChggERPProd2017 -P3306 chgg_erp_prod >e:/中国汉广集团/生产环境数据/chgg_erp_prod.sql
mysqldump -hrm-2ze8mpi5i65429l1qo.mysql.rds.aliyuncs.com -uchgg_dev -pchgg_dev -P3306 chgg_erp_dev >~/Documents/hanguang/workdata/chgg_erp_dev.sql
mysql -hrm-2ze8mpi5i65429l1qo.mysql.rds.aliyuncs.com -uchgg_dev -pchgg_dev -P3306 chgg_erp_dev < ~/Documents/hanguang/workdata/chgg_erp_prod.sql
本地
mysql -hlocalhost -uroot -p123456 -P3306 chgg_erp_local < ~/Documents/hanguang/workdata/chgg_erp_prod.sql