MySQL相关知识总结

MySQL相关知识总结

1. 显示所有表

show tables;

还有information_schema数据库里面有tables表,记录了所有表信息

use information_schema; select * from tables;

2. MySQL联合查询更新

update  a , b set a.name= b.username where a.id= b.id;

3. 如果某一字段不为空,就参与条件判断,为空就算了。

(z.ouid is null or b.ouid = z.ouid)

2017-10-15

启动mysql服务 

service mysql start 或 ./mysqld --defaults-file=/etc/my.cnf --user=root 

4.用 grant授权后,如果你在Navicat里打包过,那你要断开重新连接才会生效。

2018-09-28

group by 之后的排序并不是内排序,而是外排序。

所以,如果你想选分组里面的特定一条,要么,先排好序,再分组,要么用聚合函数,比如max或是min

网上偷来两个例子:(分别对应我说的两点)

selet tt.id,tt.newsID,tt.comment,tt.theTime from(  
select id,newsID,comment,theTime from comments order by theTime desc) as tt group by newsID 

select id,newsID,comment,theTime from comments as tt group by id,newsID,comment,theTime having
 theTime=(select max(theTime) from comments where newsID=tt.newsID)