Mysql command Daquan (full version) _ MySQL

This article mainly introduces Mysql commands. the commands shared are the most basic and recommended to you. if you are interested, refer to them. 1. connect to the database
Format: mysql-h host address-u user name-p user password
The code is as follows:

Grant select, insert, update, delete on *. * to [email = test1 @ "%] test1 @" % [/email] "Identified by" abc ";

However, the added users are very dangerous. if someone knows the password of test1, then he can log on to your mysql database on any computer on the internet and do whatever he wants for your data,For the solution, see 2.2..
The code is as follows:

Grant select, insert, update, delete on mydb. * to [email = test2 @ localhost] test2 @ localhost [/email] identified by "abc ";

If you do not want test2 to have a password, you can run another command to remove the password.

The code is as follows:

Grant select, insert, update, delete on mydb. * to [email = test2 @ localhost] test2 @ localhost [/email] identified by ""

III. database operations

The code is as follows:

Mysql> create database xhkdb;

Example 2: create a database and assign users
① Create database name;
② Grant select, INSERT, UPDATE, DELETE, CREATE, DROP, alter on database name. * TO username @ localhost identified by 'password ';
③ Set password for 'database name' @ 'localhost' = OLD_PASSWORD ('password ');
Execute three commands in sequence to create a database.
Note: You must set the Chinese "password" and "database.

The code is as follows:

Mysql> show databases

The code is as follows:

Mysql> drop database xhkdb;

Example 1: Delete a database that already exists

The code is as follows:

Mysql> drop database drop_database;
Query OK, 0 rows affected (0.00 sec)

Example 2: delete an uncertain database

Mysql> drop database drop_database; ERROR 1008 (HY000): Can't drop database 'drop _ database'; database doesn' t exist // an ERROR occurs, the 'drop _ database' database cannot be deleted. the database does not exist. Mysql> drop database if exists drop_database; Query OK, 0 rows affected, 1 warning (0.00 sec) // generates a warning indicating that the database does not exist mysql> create database drop_database; Query OK, 1 row affected (0.00 sec) mysql> drop database if exists drop_database; // if exists determines whether the database exists and does not exist, there is no error Query OK, 0 rows affected (0.00 sec)

The code is as follows:

Mysql> drop table MyClass;

Drop table is used to cancel one or more tables. You must have the DROP permission for each table. All table data and table definitions will be canceled, so be careful when using this statement!
Note: For a TABLE with partitions, drop table permanently cancels TABLE definitions, cancels partitions, and removes all data stored in these partitions. Drop table will be canceled and canceled

The table has an associated partition definition (. par) file.
For tables that do not exist, use if exists to prevent errors. When if exists is used, a NOTE is generated for each table that does not exist.
RESTRICT and CASCADE make partitioning easier. Currently, RESTRICT and CASCADE do not work.

The code is as follows:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1 = expr1 [, col_name2 = expr2...] [WHERE where_definition] [order by...] [LIMIT row_count]

Example 2: UPDATE statements for multiple tables:

The code is as follows:

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1 = expr1 [, col_name2 = expr2...] [WHERE where_definition]

The UPDATE syntax uses the new value to UPDATE columns in the original table rows. The SET clause indicates the columns to be modified and the values to be given. The WHERE clause specifies the rows to be updated. If there is no WHERE clause, all rows are updated. If the order by clause is specified, the row is updated in the specified ORDER. The LIMIT clause is used to specify a LIMIT to LIMIT the number of rows that can be updated.

The code is as follows:

Mysql> rename table MyClass to YouClass;

When you execute RENAME, you cannot have any locked table or activity transactions. You must also have the ALTER and DROP permissions for the original table and the CREATE and INSERT permissions for the new table.
If MySQL encounters any error in renaming multiple tables, it will rename all renamed tables and return everything to the initial state.
Rename table is added to MySQL 3.23.23.

V. back up data

The command is executed in the DOS [url = file: // \ mysql \ bin] \ mysql \ bin [/url] Directory.
The code is as follows:

[Root @ test1 root] # cd/home/data/mysql
[Root @ test1 mysql] # mysqldump-u root-p -- opt aaa> back_aaa

6. create a complete database instance

Drop database if exists school; // if SCHOOL exists, delete create database school; // create a database SCHOOLuse school; // open the database SCHOOLcreate table teacher // create the table TEACHER (id int (3) auto_increment not null primary key, name char (10) not null, address varchar (50) default ''shenzhen '', year date); // end of table creation // insert into teacher values (''', ''glchengang '', ''shenzhen No. 1 Middle ''', ''2010-10-10 ''); insert into teacher values (''', ''jack'', ''shenzhen No. 1 Middle '', ''1975-12-23 '');

Note: Table creation
(1) set the ID to a numeric field with a length of 3: int (3), and make it automatically add auto_increment for each record; it cannot be blank: not null; in addition, set the primary key as the primary key.
(2) set NAME to a character field with a length of 10
(3) set ADDRESS to a 50-character field, and the default value is Shenzhen.

If you type the preceding command at the mysql prompt, debugging is not convenient.
(1) you can write the above commands into a text file as they are, for example, school. SQL, then copy it to c :\\, and enter the directory [url = file: // \ mysql \ bin] \ in DOS status

\ Mysql \ bin [/url], and then type the following command:Mysql-uroot-p password <c: \ school. SQL
If it succeeds, no display is displayed for a blank row. if there is an error, a prompt is displayed. (The preceding command has been debugged. you only need to remove the // annotation to use it ).
(2) or use mysql> source c: \ school. SQL after entering the command line. you can also import the school. SQL file to the database.

The above is the full version of the Mysql command book, hope to help you use the Mysql command skillfully.