MySQL common commands

  1. 使用root创建新用户
    insert into
    mysql.user(host, user, password, select_priv, insert_priv, update_priv)
    VALUES (‘localhost’, ‘wangyu’, PASSWORD(‘xxxxxx’), ‘Y’, ‘Y’, ‘Y’);
  2. 更新表记录,以及使更新生效
    UPDATE table SET columnA = ‘Fred’ WHERE columnB = ‘Wilson’
    flush privileges;
  3. 数据库层面
    1. 查看已有数据库
    show databases;
    2. 授权某个用户操作某个数据库
    GRANT privileges ON databasename.tablename TO ‘username’@’host’
    3. 导入数据库
    source filename.sql
    4.查看指定数据库所占空间大小 (Mb)
    SELECT (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024 FROM  information_schema.TABLES where TABLE_SCHEMA=’servicer’;
    5. 查看所有表
    show tables;
  4. 表层面
    1. 打印表的内容
    select * from table
    2. 修改表内容
    UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
    3. 打印指定模式的表内容
    select field from table where field like “pattern”;
    %: 通配符,0-N个字符;
    _: 通配符,1个字符;

Leave a Reply

Your email address will not be published. Required fields are marked *