1. update、delete 操作
【错误内容】：Error Code: 1093. Table '表名' is specified twice, both as a target for 'UPDATE' and as a separate source for data
【错产生经过】：update t_test1 set col1= col1_value where val1 in(select val1 from t_test2 group by val1);
【解决办法】：update t_test1 set col1= col1_value where val1 in(select * from (select val1 from t_test2 group by val1) as T);
2.tinyint 范围
tinyint使用1字节保存数字，带符号的范围是-128到127。无符号的范围是0到255。不要想当然的就以为999用tinyint能存下，使用之前，还是要好好调查下的。
3.delete ,trucate两种方式的区别
1.truncate删除后，如果再插入，标识列从1开始 vs delete删除后，如果再插入，标识列从断点开始
2.delete可以添加筛选条件 vs truncate不可以添加筛选条件
3.truncate效率较高
4.truncate没有返回值 vs delete可以返回受影响的行数
5.truncate不可以回滚 vs delete可以回滚
4.
UPDATE tableA ppa left join tableB csb on csb.supplier_id = ppa.supplier_id SET ppa.supplier_nature = csb.supplier_nature,ppa.opening_name = csb.opening_name, ppa.bank_addr = csb.bank_addr,ppa.agent_bank = csb.agent_bank,ppa.remark='update' where ppa.audit_status =0 and ppa.mark = 0;
#https://blog.csdn.net/xin_yu_xin/article/details/7574662 mysqldump -uroot -proot -h 127.0.0.1 --default-character-set=utf8 dbName tableName --where="type=2" > E: ableName.sql
用法参考：https://blog.csdn.net/xin_yu_xin/article/details/7574662
ubuntu下mysql远程访问 第一步： vim /etc/mysql/mysql.conf.d/mysqld.cnf找到bind-address = 127.0.0.1 注释掉这行，如：#bind-address = 127.0.0.1 或者改为： bind-address = 0.0.0.0 重启 MySQL：sudo sudo /etc/init.d/mysql restart 第二步： 授权用户能进行远程连接 >grant all privileges on . to root@"%" identified by "password" with grant option; grant all privileges on . to root@"%" identified by "cloudfort" with grant option; >flush privileges;
mysql-server 5.7.16 修改相关配置文件： /etc/mysql/mysql.conf.d gedit mysqld.cnf 文件加入如下内容： default-storage-engine=INNODB character-set-server=utf8 collation-server=utf8_general_ci
修改完保存后先执行：service mysql stop
service mysql start
然后进入mysql>ststus;
1、复制表的结构
create table 表名 like 旧表;
2、复制表的结构+数据
create table 表名 select 查询列表 from 旧表【where 筛选】;
9.mysql的时区错误问题
问题：The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone....
解决方案:
show variables like '%time_zone%'; set global time_zone='+8:00';
10.limit 与max(min)同时使用查询无结果问题
#有结果
select id from emp ORDER BY id limit 100,10;
#有结果 select max(id) from emp ORDER BY id limit 0,10;
#null 说明：https://stackoverflow.com/questions/36047033/mysql-min-and-max-with-limit select max(id) from emp ORDER BY id limit 100,10;
#解决方案：用子表嵌套查询。有结果 select max(id) from (select id from emp ORDER BY id limit 100,10) A;
11.解决mysql中limit和in不能同时使用的问题
SELECT * FROM tableName WHERE id IN (SELECT id FROM tableName LIMIT 0,5); #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
#解决方案1：通过使用伪表的方式，进行表连接操作。 SELECT a.*,b.* FROM tableName a INNER JOIN (SELECT id FROM tableName LIMIT 0,5) b ON a.id = b.id
#解决方案2：将in语句里面的查询再包装一层
SELECT * FROM tableName WHERE id IN (SELECT sc.id FROM (SELECT id FROM tableName LIMIT 0,5)AS sc);
