MySQL复合语句用法

楚天乐 1416 0 条
  1. update和select联用, 将advid=2记录的数据同步更新到advid=14

    update advertisement A 
    inner join advertisement B 
    set A.title = B.title, 
    A.targets = B.targets, 
    A.parameters= B.parameters, 
    A.code = B.code
    where A.advid=14 and B.advid=2
  2. insert和select联用, 将advid=4记录的数据拷贝出来,定义type字段为xxx,作为新纪录插入

    INSERT INTO advertisement (available, type,displayorder, title, targets,parameters, code,starttime,endtime)
    SELECT available, "xxxx" as "type",displayorder, title, targets,parameters, code,starttime,endtime
    FROM   advertisement 
    WHERE  advid = 4
  3. mysql拷贝root用户,改名,并修改允许登录为%

    INSERT INTO user (
    Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked, Create_role_priv, Drop_role_priv, Password_reuse_history, Password_reuse_time, Password_require_current
    ) 
    SELECT 
    "%" as "Host",    /* 修改host */
    "<Username>" as "User",   /* 修改user */
    Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked, Create_role_priv, Drop_role_priv, Password_reuse_history, Password_reuse_time, Password_require_current FROM user WHERE user = "root";


发表我的评论
'
昵称 (必填)
邮箱 (必填)
网址