MySQL tips

1. no sub select
ok: select t1.* from t1 join t2 on t1.id = t2.id
no: select * from t1 where id (select id from t2 where name='anyname')
2. no function
ok: select * from t1 where d>='2019-01-11'
no: select * from t1 where YEAR(d) >= 2019
3.in better than or
ok: select * from t where col1 in (30,10,20)
no: select * from t where col1 =30 or col1=10 or col1=20
4.like no double %%
ok: select * from t where name like 'n%'
no: select * from t where name like '%n%'
5.select * from t where 1=1 limit 10;
6. select * from t where id=200
7.select  id,count(*) from t group by id ORDER BY NULL;
8. order by
ok: select * from t where id>=ceil(RAND()*1000) limit 6;
no: select * from t where 1=1 order by RAND() limit 6;
9. select count(*) from t left join t2 on t.id=t2.id
10.insert into t (id,name) values (1,'n1'),(2,'n2'),(3,'n3')
CONVERT(char(10), [created], 120) = CONVERT(char(10), GETUTCDATE(), 120)

mysql> use mysql
mysql>update user set password=password("newpwd") where user="root";
mysql>flush privileges;

https://dev.mysql.com/downloads/windows/installer/

https://c.runoob.com/front-end/854   it is for regual expressions.

desc tablename;
select now() ->;
show variables like '%time_zone%';
--system_time_zone : CST // China