MySQL command detailed

All MySQL commands are available through online documentation or through the help command.
Here are some of the MySQL operation commands that are often used in the work.

[MySQL Database]
[Descibe] Statement: Used to display the structure of a table, which is the information that makes up the fields (columns) of the table. You need to specify "library name. Table name" as a parameter

MySQL >use MySQL; MySQL >descibe user;

[Create creation]
Creating a user with Create

MySQL >create user ' Wangwu ' @ ' 192.168.1.% ' identified by ' 123456 '; "Create users Harry and restrict to 192.168.1.0 network segment" MySQL >create Database Xianshang Character Set UTF8 collate utf8_bin; "Create databases, set encoding format, set collation"

CREATE table [table name] (field 1 name type, field 2 name Type,....., primary key (main name))
For example
Create the Users table, which contains a string with field information of username not exceeding 16 bytes, userpassword A string of no more than 48 bytes, the default password string is set to NULL, and the primary key is username must be unique.

MySQL >use auth; MySQL >create talbe users (username char (not null,userpassword char) defautlt ", primary key (username));

[Delete a data table/library]
DROP TABLE statement: Used to delete a table from a library, specifying "library name. Table name" as the parameter

MySQL >drop table auth.users; MySQL >drop database auth;

[Insert data Record]

INSERT INTO inserts a new data record into the table
Format: INSERT into [table name] (field 1, Field 2, ....) Values (the Value of field 1, the value of field 2, ...). ）
[e.g.]
Insert a record into the users table of the Auth library.

MySQL >use auth; MySQL >insert into users (Username,userpassword) VALUES (' Zhangsan ', password (' 123456 '));

If you insert a record that includes all the fields in the table, you can omit the field section

MySQL >insert into users values (' Zhangsan ', password (' 123456 '));

[Query data record]
SELECT statement: When representing all fields, you can use "*" at which point the where expression can be omitted.
Select field 1, Field 2, ... from [table name] where conditional expression

MySQL >select * from Auth.users; MySQL >select username,userpassword from auth.users where username= ' Zhangsan ';

            
Span style= "Color:rgb (192,0,0); font-size:20px;" >[modify data record]
UPDATE statement:
        update table name set field Name 1 = field value 1 [, field Name 2 = field value 2] where conditional expression
            mysql >update auth.users Set Userpassword=password (') where username= ' Lisi ';
the various users in the MySQL database, including root, are stored in the MySQL user table, and you can use the UPDATE statement to directly modify the password field value of the user table of the MySQL library, or you can set the password using the Mysqladmin tool.

[Delete data Record "
DELETE statement:
        delete from table name where conditional expression

MySQL >delete from auth.users where username= ' Lisi '; MySQL >select * from Auth.users;

The MySQL database is added by default to the empty users (both the user name and password are empty) that are accessed from the native computer, and should be removed from these empty users based on security considerations.

MySQL >select host,user,password from mysql.user where user= '; MySQL >delete from mysql.user where user= ';

[Maintain MySQL database]

[User Authorization]
Grant statement: When a user is present, grant is used to modify the user information, otherwise grant will create a new user

Grant permission list on Library name. Table name to ' user name ' @ ' source address ' [identified by ' Password ';]        MySQL >grant select on auth.* to ' Wangfei ' @ ' localhost ' identified by ' 123 '; The user record authorized by the GRANT statement is saved to a related table such as User,db,host,tables_priv in the MySQL library and takes effect without a refresh.

Source address can be [(% represents any) (domain/ip/)]
For example: "%.benet.com", "192.168.88.%" and so on.
When [identified by] is omitted, the password is blank to log in.

[View Authorization]
Show Grants statement:
Show grants for user name @ Source Address

MySQL >show grants for ' Wangfei ' @ ' 192.168.88.109 ';

[Revoke permissions]
Revoke statement:
Revoke permissions list on database name. Table name from user name @ Source Address
Revoke all permissions that Zhang San users access from this computer as follows:

MySQL >revoke all on auth.* from ' zhangsan ' @ ' localhost '; MySQL >show grants for ' zhangsan ' @ ' localhost ';

[Backup and recovery of database]

There are many ways to backup, such as the ability to package database folders/var/local/mysql/var/or use a dedicated export tool mysqldump
Format 1: Export Some of the tables in the specified library
mysqldump [Options] Library name [table name 1] [table Name 2] ... >/backup path/backup file name
Export the user table in the MySQL library to Mysql-user.sql

MySQL >mysqldump-u root-p MySQL user〉mysql-user.sql

Format 2: Export one or more complete libraries {including all tables in them}
mysqldump [options]--databases library name 1 [library Name 2] ... >/backup path/backup file name
The entire Auth library is exported as a Auth.sql file and is authenticated as the root user.

MySQL >mysqldump-u root-p--databases auth >auth.sql

Format 3: Export all libraries
mysqldump [Options]--all-databases >/backup path/backup file name
When you need to back up all the libraries in the entire MySQL server, the amount of data is relatively large, you can add the--opt option to optimize execution speed, for example: The following will create a backup file All-data.sql which includes all the libraries of the MySQL server.

MySQL >mysqldump-u root-p--opt--all-databases > All.-data.sql

Common options include-u,-p for specifying a user name and password.

Cross-host Backup:
The following command copies the Sourcedb on Host1 to Targetdb on Host2, provided the HOST2 database has been created on Targetdb.

Mysqldump--host=host1--opt Sourcedb | MySQL--host=host2-c targetdb

To back up the table structure only:

Mysqldump--no-data--databases mydatabase1 mydatabase2 mydatabase3 > Test.sql

[View backup file contents]
The SQL script that is exported through the Mysqldump tool is a text file where the "/*..../" section or the line that begins with "--" represents the comment information. Use text tools such as Grep,less,cat to view the contents of the script. For example, you can filter out the database operation statements in the Auth.sql script by doing the following.

[[Email protected] ~] #grep-V "^--" Auth.sql |grep-v "^/" |grep-v "^$"

[Recover Database]
mysql [options] [library name] [table name] </backup path/backup file name
The following actions import a table into the library test

[[Email protected] ~] #mysql-u root-p test〈mysql-user.sql

The following actions will restore all the libraries

[[Email protected] ~] #mysql-U root-p〈~/all-data.sql

------------------------------------------------------------------------------------------

"Timed Backup with crontab command"
2:30 every day to back up all the databases on a host and compress the dump file to GZ format, CRONTAB-E
2 * * * root mysqldump-u Root-ppassword--all-databases | gzip >/mnt/disk2/database_ ' Date ' +%m-%d-%y '. sql.gz

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/5B/63/wKioL1UH_qDBD64FAACKdzFSmqw692.jpg "title=" 111. PNG "alt=" Wkiol1uh_qdbd64faackdzfsmqw692.jpg "/> First five parameters for minutes, hours, days, months, weeks, * means arbitrary, date ' +%m-%d-%y ' gets the current date mm-dd-yy YY format.

Not to be continued ...

This article is from the "Hello_world" blog, make sure to keep this source http://coward.blog.51cto.com/7599475/1621555

MySQL command detailed