MySQL Common statements

• Create a database

CREATE DATABASE IF not EXISTS $database DEFAULT CHARSET UTF8 COLLATE utf8_general_ci; CREATE DATABASE $database DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

• Create a user and add permissions

# just create user [email protected] ' localhost ' identified by ' yourpasswd '; # give permission Grant Select,update on phpergao.* to [em AIL protected] ' localhost '; Grant index on phpergao.* to [email protected] ' 192.168.0.% '; # Create user and give permission grant all privileges on phpergao.* to ' Phpergao ' @ ' L Ocalhost ' identified by ' yourpasswd '; # The opposite REVOKE is similar to Grant's syntax, just replace the keyword "to" with "from": REVOKE all privileges on Phperga o.* from ' Phpergao ' @ ' localhost '; # all privileges can be changed to Select,insert,update,delete,create,drop,index,alter,grant, References,reload,shutdown,process,file, etc. 14 permissions. # Delete users delete from user WHERE user= ' Phpergao ' and host= ' localhost '; # Modify user Password update username SET PASSWORD = PASSWORD (' newpasswd ' WHERE USER = ' Phpergao ' and HOST = ' localhost ';

　　

• Refresh Permissions

FLUSH privileges;

• To view your own permissions

# view your permissions show grants;# see other people's permissions show GRANTS for ' Phpergao ' @ '% ';

• Build table

DROP table IF EXISTS ' table ';  CREATE table ' table ' (  ' id ' int (one) unsigned not NULL auto_increment,  PRIMARY KEY (' id ')) Engine=myisam Auto_incre ment=2 DEFAULT Charset=utf8;

• Change Password

#修改配置 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # addskip-name-resolve skip-grant-tables# Restart Free login Service mysqld restartmysql# change root password: UPDATE mysql.user SET password=password (' 123456 ') WHERE user= ' root ';

#修改用户登录host
UPDATE MySQL.  SET host=user=' root ';  

　　

MySQL Common statements