修改mysql表字段的内部值:
update cms_channel set alias=replace(alias,'ikfjk','icfjk') ;
alias:cms_channel字段名,ikfjk:被替换的字符串,icfjk:替换字符串
多表查询
SELECT DISTINCT m.id FROM cms_menu m INNER JOIN cms_role_menu rm ON m.id = rm.menu_id INNER JOIN cms_role r ON r.id = rm.role_id INNER JOIN cms_user_role ur ON ur.role_id = r.id INNER JOIN cms_user u ON u.id = ur.user_id WHERE u.id =1
等价于
SELECT DISTINCT m.id FROM (((cms_menu m INNER JOIN cms_role_menu rm ON m.id = rm.menu_id) INNER JOIN cms_role r ON r.id = rm.role_id) INNER JOIN cms_user_role ur ON ur.role_id = r.id) INNER JOIN cms_user u ON u.id = ur.user_id WHERE u.id =1
in数据量少效率还可以,数据量大就效率低
exists的效率依赖于匹配度。
inner join效率比较稳定。
修改链接
update user set Host='%' where User=root and Password=PASSWORD('123456');
update cms_channel set alias=replace(alias,'ikfjk','icfjk') ;
alias:cms_channel字段名,ikfjk:被替换的字符串,icfjk:替换字符串
多表查询
SELECT DISTINCT m.id FROM cms_menu m INNER JOIN cms_role_menu rm ON m.id = rm.menu_id INNER JOIN cms_role r ON r.id = rm.role_id INNER JOIN cms_user_role ur ON ur.role_id = r.id INNER JOIN cms_user u ON u.id = ur.user_id WHERE u.id =1
等价于
SELECT DISTINCT m.id FROM (((cms_menu m INNER JOIN cms_role_menu rm ON m.id = rm.menu_id) INNER JOIN cms_role r ON r.id = rm.role_id) INNER JOIN cms_user_role ur ON ur.role_id = r.id) INNER JOIN cms_user u ON u.id = ur.user_id WHERE u.id =1
in数据量少效率还可以,数据量大就效率低
exists的效率依赖于匹配度。
inner join效率比较稳定。
修改链接
update user set Host='%' where User=root and Password=PASSWORD('123456');