MySQL command Daquan (full version)

Original: http://www.jb51.net/article/74564.htm

First, connect the database
Format: mysql-h host address-u user name-P user Password
1.1. Connect to MySQL on this computer.
First open the DOS window, then enter the directory Mysql\bin, and then type the command Mysql-u root-p, enter after you are prompted to lose the password.
Note that you can have a space before the user name, but there must be no space before the password, or let you re-enter the password.
If you have just installed MySQL, superuser root is no password, so the direct return to enter the MySQL, MySQL prompt is: mysql>

1.2 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; (Note: You can not add a space between the root and the other)
1.3 exit MySQL command: exit (Enter)

Second, increase the user
Format: Grant Select on database. * To User name @ login host identified by "password"
2.1 Add a user test1 password to ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions.

First connect to MySQL with the root user, and then type the following command:

Copy CodeThe code is as follows: Grant Select,insert,update,delete on * * to [[email protected] "%][email protected]"%[/email] "identified by" ABC ";

But the added user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log on your MySQL database and to your data can do whatever, solution see 2.2.
2.2 Add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can be queried, inserted, modified, deleted operations (localhost refers to the local host, that is, the MySQL database is located on the host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.

Copy CodeThe code is as follows: Grant Select,insert,update,delete on mydb.* to [[email Protected]][email protected][/email] identified by "ABC";

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

Copy CodeThe code is as follows: Grant Select,insert,update,delete on mydb.* to [[email Protected]][email protected][/email] identified by ""

Third, the operation of the database

3.1 Creating a Database
Note: Before you create a database, connect to the MySQL server
Command: Create databases < database name >
Example 1: Establishing a database named XHKDB

Copy CodeThe code is as follows:mysql> CREATE database xhkdb;

Example 2: Creating a database and assigning users
①create database name;
②grant select,insert,update,delete,create,drop,alter on database name. * To user name @localhost identified by ' password ';
③set PASSWORD for ' database name ' @ ' localhost ' = old_password (' password ');
Execute 3 commands in turn to complete the database creation.
Note: The Chinese "password" and "database" are the user's own needs to set.

3.2 Displaying the database
Command: Show databases (note: There is a last s)

Copy CodeThe code is as follows:mysql> show databases

3.3 Deleting a database
Command: Drop databases < database name >
Example: Delete a database named Xhkdb

Copy CodeThe code is as follows:mysql> drop database xhkdb;

Example 1: Delete a database that has been determined to exist

Copy CodeThe code is as follows:mysql> drop database drop_database;
Query OK, 0 rows Affected (0.00 sec)

Example 2: Deleting a database with an indeterminate existence

mysql> drop Database drop_database; ERROR 1008 (HY000): Can ' t drop database ' drop_database '; Database doesn ' t exist//error occurred, cannot delete ' drop_database ' databases, 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 stating that this 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 determine if the databases exist, do not exist, and do not produce errors query OK , 0 rows Affected (0.00 sec)

3.4 Connecting the Database
Command: Use < database name >
For example: If the XHKDB database exists, try to access it:mysql> use XHKDB;
Screen tip: Database changed
The USE statement can advertise that MySQL uses the Db_name database as the default (current) database for subsequent statements. The database remains as the default database until the end of the segment, or until a different use statement is published:

mysql> use db1;mysql> SELECT COUNT (*) from mytable; # selects from db1.mytablemysql> use db2;mysql> SELECT COUNT (*) from mytable; # selects from db2.mytable

Using the USE statement to mark a specific current database does not prevent you from accessing tables in other databases. The following example accesses the author table from the DB1 database and accesses the edit table from the DB2 database:

mysql> use db1;mysql> SELECT author_name,editor_name from Author,db2.editor,  WHERE author.editor_id = DB2 . editor.editor_id;

You can exit the database or connect to another database directly with the user ' other database name '.

3.5 Current Selection Database
Command:mysql> Select Database ();
The select command in MySQL is similar to print or write in other programming languages, and you can use it to display the results of a string, a number, a mathematical expression, and so on. How do I use the special features of the Select command in MySQL?

(1). Show MySQL version

Mysql> select version (); +-----------------------+ | Version ()    

(2). Show Current Time

Mysql> Select Now (); +---------------------+ | Now ()    

(3). Display Month Day

SELECT DayOfMonth (current_date); +--------------------------+ | DayOfMonth (current_date) | +--------------------------+ |      15 | +--------------------------+ 1 row in Set (0.01 sec)  SELECT MONTH (current_date); +---------------------+ | MONTH (current_date) | +---------------------+ |     9 | +---------------------+ 1 row in Set (0.00 sec)  SELECT Year (current_date); +--------------------+ | Year (current_date) | +--------------------+ |    

(4). Display string

(5). When the calculator uses

Select ((4 * 4)/10) + 25; +----------------------+ | ((4 * 4)/10) + 25 | +----------------------+ |    

(6). String strings

Select CONCAT (F_name, "", L_name) as name from Employee_data where title = ' Marketing Executive '; +---------------+ | Name   

Note: the concat () function is used here to string strings together. In addition, we used the previously learned as to give the result column ' CONCAT (f_name, "", L_name) ' a pseudonym

Iv. operation of the table
4.1 Creating a Table
Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]);
For example, create a table named MyClass

Mysql> CREATE TABLE MyClass (> ID int (4) NOT null primary key auto_increment,> name char (a) not null,> sex int (4) Not null default ' 0 ',> degree double (16,2));

4.2 Getting the table structure
Command: DESC table name, or Show columns from table name

Mysql> desc myclass;mysql> Show columns from MyClass;

Using the MySQL database desc table name, we see the key column, there may be 4 kinds of values, namely ', ' PRI ', ' UNI ', ' MUL '.
(1). If key is empty, then the value of the column can be duplicated, indicating that the column has no index, or a non-leading column of a non-unique composite index;
(2). If key is a PRI, then the column is part of the primary key;
(3). If key is uni, then the column is the first column (the leading column) of a unique value index and must not contain a null value (NULL);
(4). If key is Mul, the value of the column can be repeated, which is a leading column of a non-unique index (the first column) or a component of a unique index but can contain null values.
If the definition of a column to meet the above 4 cases, such as a column is both a PRI and UNI, then the "DESC table name", the display of the key value according to priority, Pri->uni->mul. So at this point, the PRI is displayed.
A unique index column can be displayed as a PRI, and the column cannot contain null values, and the table does not have a primary key.
A unique index column can be displayed as Mul if multiple columns constitute a unique composite index, because although the multiple-column combination of indexes is unique, such as Id+name is unique, no single column can still have duplicate values, as long as Id+name is unique.

4.3 Deleting a table
Command: DROP table < table name >
Example: Deleting a table with a table named MyClass

Copy CodeThe code is as follows:mysql> drop table MyClass;

DROP table is used to cancel one or more tables. You must have drop permissions for each table. All table data and table definitions are canceled, so use this statement with caution!
Note: For a partitioned table, the DROP table permanently cancels the tables definition, cancels the partitions, and cancels all the data stored in those partitions. DROP table will also be canceled and canceled

The table has an associated partition definition (. par) file.
For tables that do not exist, use the if exists to prevent errors from occurring. When using the if exists, a note is generated for each table that does not exist.
Restrict and cascade can make partitioning easier. Currently, restrict and cascade do not work.

4.4 Inserting data into a table
Command: INSERT into < table name > [(< Field name 1>[,.. < field name n >])] VALUES (value 1) [, (value N)]
For example: Insert two records into table MyClass, these two records indicate that: 1 is named Tom with a score of 96.45, 2 is named Joan, the result is 82.99, and the name of 3 is named

Wang's score was 96.5.
mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);
Note: INSERT into only one record can be inserted into the table at a time.

4.5 Enquiry Form
(1), query all rows
Command: Select < Field 1, field 2,...> from < table name > where < expression >
Example: View all data in table MyClass
Mysql> select * from MyClass;
(2), query the first few rows of data
Example: Viewing the first 2 rows of data in a table MyClass
Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;
Select is typically used with where to query for more accurate and complex data.

4.6 Deleting a table
Command: Delete from table name where expression
Example: Deleting a record with number 1 in table MyClass
Mysql> Delete from MyClass where id=1;

4.7 Modifying data in a table
Syntax: Update table name SET field = new value,... WHERE condition
mysql> Update MyClass set name= ' Mary ' where id=1;

Example 1: MySQL UPDATE statement for single table:

Copy CodeThe 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: Multi-table UPDATE statement:

Copy CodeThe code is as follows: UPDATE [low_priority] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition]

The update syntax can update the columns in the original table row with the new values. The SET clause indicates which columns to modify and which values to give. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. If an ORDER BY clause is specified, the row is updated in the order specified. The limit clause is used to limit the number of rows that can be updated, given a limit value.

4.8 Adding a table field
Command: ALTER TABLE name add field type other;
For example: Added a field passtest in table MyClass, type int (4), default value of 0
mysql> ALTER TABLE MyClass add passtest int (4) Default ' 0 ' [/code]
Add Index:Mysql> ALTER TABLE name add index index name (field name 1[, field Name 2 ...]);
Example:mysql> ALTER TABLE employee ADD index emp_name (name);
index of the plus primary key:Mysql> ALTER TABLE name add primary key (field name);
Example:Mysql> ALTER TABLE employee ADD primary key (ID);
indexes with unique restrictions:mysql> ALTER TABLE name add unique index name (field name);
Example: mysql> ALTER TABLE employee add unique emp_name2 (cardnumber);
Delete an index:mysql> ALTER TABLE name DROP INDEX name;
Example: mysql>alter Table employee DROP index emp_name;
add field:mysql> ALTER TABLE table_name ADD field_name Field_type;
Modify the original field name and type:mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;
Modify field type : Mysql>alter TABLE table_name MODIFY colum_name field_type New_type
To delete a field: MySQL ALTER TABLE table_name DROP field_name;

4.9 Modifying table names
Command: Rename table name to new table name;
For example, change the name of the table MyClass to Youclass

Copy CodeThe code is as follows:mysql> Rename table MyClass to Youclass;

When you execute RENAME, you cannot have any locked tables or active transactions. You must also have ALTER and DROP permissions on the original table, as well as CREATE and INSERT permissions on the new table.
If MySQL encounters any errors in the multi-table renaming, it reverses all renamed tables, returning everything to its original state.
RENAME TABLE was added to MySQL 3.23.23.

Five, backup data

Command executed in DOS [Url=file://\\mysql\\bin]\\mysql\\bin[/url] Directory
(1). Export the entire database
The export file is present in the Mysql\bin directory by default
Mysqldump-u user name-p database name > exported file name
Mysqldump-u user_name-p123456 database_name > Outfile_name.sql
(2). Export a table
Mysqldump-u user name-P database name Table name > exported file name
MYSQLDUMP-U USER_NAME-P database_name table_name > OUTFILE_NAME.SQL
(3). Export a database structure
Mysqldump-u user_name-p-d–add-drop-table database_name > Outfile_name.sql
-D No data –add-drop-table add a drop table before each CREATE statement
(4). Export with language parameters
mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name.sql
For example, back up the AAA library to file Back_aaa:

Copy CodeThe code is as follows: [[email protected] root]# Cd/home/data/mysql
[Email protected] mysql]# mysqldump-u root-p--opt aaa > BACK_AAA

六、一个 A complete database creation instance

Drop database if exists school; Delete Create Database school if school is present; Establish library Schooluse School; Open the Library schoolcreate table teacher//Set up tables teacher (ID int (3) auto_increment NOT null primary key, name Char (TEN) not NULL, Addre SS varchar (+) Default ' Shenzhen ', year date); Build table End//following inserts into teacher values (' ' ', ' ' Glchengang ', ' ' Shenzhen One ', ' 1976-10-10 '); insert into teacher values (' ', ' Jack ', ' The one in Shenzhen ', ' 1975-12-23 ');

Note: In the table under construction
(1), set the ID to a number field of length 3: Int (3), and let it automatically add one to each record: auto_increment; and cannot be empty: not null; and let him be the main field primary key.
(2), set name to a character field of length 10
(3), the address is set to a character field of length 50, and the default value is Shenzhen.

It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug.
(1), you can write the above command as is written in a text file, assuming that school.sql, and then copied to c:\\, and in the DOS state into the directory [url=file://\\mysql\\bin]\

\mysql\\bin[/url], and then type the following command:mysql-uroot-p Password < c:\\school.sql
If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove//comment).
(2), or enter the command line after using mysql> source C:\\school.sql; You can also import the School.sql file into the database.

The above is the full version of the MySQL command Daquan, I hope that you are skilled use of MySQL command helpful.

MySQL command Daquan (full version)