MySQL Common commands

Reprint please indicate the source:
http://blog.csdn.net/ouyida3/article/details/46573387
This article is from: "Ouyida3 's Blog"

Accustomed to Oracle, the first time with MySQL will not adapt. The command line is used before the MySQL client is selected.

1. Login

-P31306-u-puacuser1-P3020-h130.51.23.246-uroot-proot-P3020-h130.51.23.246-uroot-p

MySQL commands can be used after MySQL is installed.

• -P: Port. There may be spaces, or not.
• -u: User name. There may be spaces, or not. Root is the highest-privileged user. Other users can be created from root.
• -h:ip. MySQL server is located on the host, if there is no default-that is, this machine must be the MySQL user.
• -P: Password. There must be no space, immediately following-P. Can also be left blank after-p, prompting you to enter the password.

To query the current user:

select user();

2. Create user

CREATE USER ‘uacuser1‘@‘localhost‘ IDENTIFIED BY ‘123456‘;CREATE USER ‘uacuser1‘@‘aipaas03‘ IDENTIFIED BY ‘123456‘;CREATE USER ‘uacuser1‘@‘%‘ IDENTIFIED BY ‘123456‘;

Query users

select user,host from mysql.user order by user;

To delete a user:

 Delete  from MySQL. User Where User=' uacdb '  and host=' localhost '; Delete  from MySQL. User Where User=' uacdb '  and host=' Aipaas03 '; Delete  from MySQL. User Where User=' uacdb '  and host='% ';Flush privileges;

3. Create a Database

 Create database uacdb; GRANT  all privileges  the uacdb.*  to [email protected]"%" Identified  by "123456";  GRANT all privileges  in uacdb.*  to [email protected  ]" localhost " identified  by " 123456 ";  GRANT  all privileges  the uacdb.*  to [email protected]"AIPAAS03" identified  by "123456";Flush privileges;

• MySQL new setup user or change password after use flush privileges refresh the MySQL system permission related table, otherwise there will be denied access, there is another way, is to restart the MySQL server for the new settings to take effect.
• MySQL build repository must be built with root, then empower users.

Querying the database:

show databases;

Enter a database (database_name is the specific library name):

usedatabase_name;

To delete a database:

drop database if exists database_name;

4. Delete Table/Build table

You must enter a library before you can proceed.

 DROP TABLE IF EXISTS ' auth_center ';CREATE TABLE ' Auth_center '(' auth_id 'bigint -) not NULLAuto_increment,' Auth_password ' varchar( -)COLLATEUtf8_bin not NULL,' Auth_source ' varchar( -)COLLATEUtf8_bin not NULL DEFAULT "',' Auth_param ' varchar(2048)COLLATEUtf8_binDEFAULT NULL,' Auth_state ' varchar(2)COLLATEUtf8_bin not NULL,' Auth_register_time ' timestamp  not NULL,' Auth_active_time ' timestamp NULL DEFAULT NULL,PRIMARY KEY(' auth_id ')) Engine=innodb auto_increment=617 DEFAULTCharset=utf8COLLATE=utf8_bin;

Query table:

show tables;

To view the table structure:

desc table_name;

Dml:
Use the standard DML. Select, insert, UPDATE, delete.

5. Check if the transaction is automatically committed

show variables like ‘%autocommit%‘;

6. View MySQL version four ways

mysql–Vmysql--help|grepDistrib

statusselect version();

7. Exit

quitexit

8, error resolution

ERROR 1044 (42000): Access denied for user ' portaluser1 ' @ ' aipaas03 ' to database ' Protaldb '

Solution: Use PROTALDB encountered, protaldb write wrong, create database encountered, must be root user.

ERROR 1045 (28000): Access denied for user ' root ' @ ' Aipaas03 ' (using Password:yes)

Resolution: The user name or password is wrong.

ERROR 1046 (3d000): No Database selected

Workaround: Use the library name first.

ERROR 2002 (HY000): Can ' t connect to local MySQL server through socket '/var/lib/mysql/mysql.sock ' (2)

Resolution: encountered while logging in. Requires-h MySQL host. or log in to the host with a MySQL user.

ERROR 2006 (HY000): MySQL server has gone away

FIX: Grant all privileges encountered, can be ignored.

2015.6.20

MySQL Common commands