MySQL basic operation and operation to the user

1. Login/Exit Basic operation
Login: MySQL [-H server address]-U login name-P port number-P
or login: MySQL [--host= server address]--user= user name--port= port--password
Exit: Quit; or exit;
Note: After logging into the database system, you need to use "Set names encoded name;" To set the "environment variable" of the current connection database, which is the encoding of the "client" itself that is currently dealing with the database. Generally speaking:
The Ocmd client is fixed by using GBK encoding,
O and the PHP page is the encoding of the page file (now the mainstream is UTF8).

Mysql-u root-ppassword:****set names UTF8;

Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:

Mysql-h110.110.110.110-u Root-p 123;

2, modify the user password to log in
Modify your password: set password = password (' new password ');

Set Password = password ("* * *");

Modify someone else's password (you must have Modify permission):
Set password for ' username ' @ ' allow its login address ' = password (' new password ');

Set password for ' root ' @ ' localhost ' = password (' * * * ');

3. Users in MySQL
(1) Create a user
Grammatical form:
Create user ' username ' @ ' allows its login address ' identified by ' password ';
Description
1, the created user needs to specify at which address the user can log on at the same time.
Where "%" stands for "any address".
2, after the user is created, a record is automatically added to the MySQL user table, but that person does not yet have permission.
(2) Delete user
Drop user ' username ' @ ' allow its login address ';
(3) Permission assignment
Add Permissions:
Grant permission name 1, permission Name 2, .... on database name. The object name to ' user name ' @ ' allows its login address ' identified by ' password ';
Description
The 1 permission names are: ' Select ', ' Update ', ' delete ', and so on. Where all means "all permissions", or all privileges is the same
2 Object name: is a database "loaded" things, the table is the most common, can also be views, stored procedures, storage functions and so on. Now ()
Which: *. * represents all objects in all data
A database name. * represents all objects in the database-this is commonly used commercially.
3,identified by ' password ' is used for a user to change the password at this time, do not write, then do not change the password.
4, but the statement can also create a user (if it does not exist), but at this point identified by ' password ' must be written.

Grant all on z_0705.* to ' root ' @ ' localhost ' identified by ' * * *;

(4) Delete permissions
Revoke permission name 1, permission Name 2, .... on database name. Object name from ' username ' @ ' allow it to log in address ';

Revoke all on z_0705.* from ' z_0705 ' @ ' localhost ';

4, the other operation of the table:
(1) Show all databases show databases;
(2) Access database use database name;
In general, you must first "enter" the database for the data tables and data in the data.
Problem: Use set names UTF8 in cmd, then get garbled?
1,cmd, you must use GBK
2,php file, according to the encoding of the file can be determined: UTF8 encoding is UTF8,ANSI encoding (GBK) with GBK
(3) Show all tables show tables:
(4) Display table structure desc table name;
(5) Display table creation statement: Show create table table name;
(7) Copy table structure from existing table: CREATE TABLE [if not EXISTS] new table name like original table name;
Copy table structure from an existing table: CREATE TABLE [if not EXISTS] new table name select * FROM original table name where 1<>1; (not recommended)

5. Backup and restore the database
Backing Up the database: (outside of MySQL service)
Mysqldump-h server address-u login name-Name of the database to be backed up > file to save As
To recover a database:
Mysql-h server address-u login name-P port number-p database name < file name
Note: Typically the database name needs to be established first (present):

mysqldump-h localhost-u root-p z_0705>d:mysql.sqlmysql-h localhost-u root-p z_0705 < D : Mysql.sql

MySQL basic operation and operation to the user