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 left join 多表关联更新
<pre>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;</pre>
5.MYSQL使用mysqldump导出某个表的部分数据
<pre>#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:\tableName.sql</pre>
用法参考：https://blog.csdn.net/xin_yu_xin/article/details/7574662
6.ubuntu下mysql远程访问
<pre>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;</pre>
7.mysql默认latin字符集改成utf-8
<pre>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</pre>
修改完保存后先执行：service mysql stop
service mysql start
然后进入mysql>ststus;
8.如何快速复制一张表
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....
解决方案:
<pre>show variables like '%time_zone%'; set global time_zone='+8:00';</pre>
10.limit 与max(min)同时使用查询无结果问题
<pre>#有结果
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;</pre>
11.解决mysql中limit和in不能同时使用的问题
<pre>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' </pre>
<pre>#解决方案1：通过使用伪表的方式，进行表连接操作。
SELECT a.,b. FROM tableName a INNER JOIN (SELECT id FROM tableName LIMIT 0,5) b ON a.id = b.id</pre>
<pre>#解决方案2：将in语句里面的查询再包装一层
SELECT * FROM tableName WHERE id IN (SELECT sc.id FROM (SELECT id FROM tableName LIMIT 0,5)AS sc);</pre>
