A collection of Mysql common commands worth collecting _mysql

This article for everyone detailed collation of MySQL commonly used commands, to share with you for your reference

1, connect MySQL
Format: mysql-h host address-u user name-P user Password

1), connected to the MySQL on the computer.
First open the DOS window, and then enter the directory Mysql\bin, and then type the command Mysql-u root-p, enter after the prompt you to lose the password. Note that the user name can have spaces or no spaces, but the password must have no space before, otherwise let you re-enter the password.

If you have just installed MySQL, superuser root is no password, so direct return can enter into MySQL, the MySQL prompt is: mysql>

2), connect to the remote host MySQL. Assume the IP of the remote host is: 110.110.110.110, username is root, password is abcd123. Type the following command:
Mysql-h110.110.110.110-u Root-p 123; (Note: There is no space between u and Root, others are the same)

3), exit MySQL command: Exit (enter)

2, modify the password
Format: mysqladmin-u username-P Old password password new password

1), to root add a password ab12.
First enter the directory Mysql\bin in DOS, and then type the following command mysqladmin-u Root-password ab12
Note: Since Root does not have a password at the beginning, the-p old password can be omitted.

2), and then change the root password to djg345. mysqladmin-u root-p ab12 password djg345

3. Add new users
Note: Unlike the above, the following are the commands in the MySQL environment, followed by a semicolon as the command terminator

Format: Grant Select on database. * To User name @ login host identified by "password"

1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First connect to MySQL with root user, and then type the following command:

Copy Code code as follows:

Grant Select,insert,update,delete on *.* to [email=test1@ '%]test1@ '%[/email] ' identified by ' ABC ';

But the added user is very dangerous and you want someone who knows Test1 's password so that he can log on to your MySQL database on any computer on the Internet and get your data right, see 2.

2, add a user test2 password for ABC, so that he can only login on the localhost, and can query the database mydb, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database is the host), This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, only through a Web page on the MySQL host.

Copy Code code as follows:

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

If you do not want to test2 the password, you can make another command to eliminate the password.

Copy Code code as follows:

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

4. Database operation

4.1 Creating a Database

Note: You must first connect to the MySQL server before creating the database

Command: CREATE DATABASE < database name >

Example 1: Create a database named XHKDB
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 database name @localhost identified by ' password ';

③set PASSWORD for ' database name ' @ ' localhost ' = old_password (' password ');

Execute 3 commands sequentially to complete the database creation. Note: The Chinese "password" and "database" is the user needs to set.
4.2 Display Database

Command: Show databases (note: Last has an S)

mysql> show databases;

Note: In order to no longer display the garbled code, to modify the database default encoding. The following is an example of a GBK coded page:

1, modify the MySQL configuration file:My.ini inside Modify DEFAULT-CHARACTER-SET=GBK
2. Modify code when running:

①java Code: JDBC:MYSQL://LOCALHOST:3306/TEST?USEUNICODE=TRUE&CHARACTERENCODING=GBK
②php Code: Header ("content-type:text/html;charset=gb2312");
③c language code: int mysql_set_character_set (mysql * mysql, char * csname);
This function is used to set the default character set for the current connection. The string csname specifies 1 valid character set names. Connection proofing becomes the default collation for character sets. The function works like a set names statement, but it can also set the value of mysql-> CharSet, which affects the set of character sets by Mysql_real_escape_string ().

4.3 Deleting a database
Command: Drop Database < database name >
For example, to delete a database named Xhkdb

mysql> drop Database xhkdb;

Example 1: Delete a database that is already determined to exist

 mysql> drop database drop_database;
 Query OK, 0 rows Affected (0.00 sec)

Example 2: Delete a database with an indeterminate existence

 mysql> drop database drop_database;
 ERROR 1008 (HY000): Can ' t drop database ' drop_database '; DB doesn ' t exist
 //error, cannot delete ' drop_database ' database, 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 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 to determine whether the database exists, does not exist, and does not produce errors 
   query OK, 0 rows Affected (0.00 sec)

4.4 Connection Database
Command: Use < database name >

For example: If the XHKDB database exists, try to access it:

mysql> use XHKDB;

ScreenTip: Database changed

The USE statement informs MySQL that the Db_name database will be used as the default (current) database for subsequent statements. The database remains in the default database until the end of the paragraph, or until a different use statement is published:

 mysql> use DB1;
 Mysql> SELECT COUNT (*) from mytable; # selects from db1.mytable
 mysql> to 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 can access the author table from the DB1 database and access 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

The USE statement is set up to be compatible with Sybase.

Some netizens asked how to exit after the connection. In fact, do not have to quit, use the database, using show databases can query all databases, if you want to jump to other databases, with
Use other database names
It's OK.

4.5 The currently selected 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 () | 
+-----------------------+ 
| 6.0.4-alpha-community | 
+-----------------------+ 
1 row in Set (0.02 sec)

2. Show Current time

Mysql> Select Now (); 
+---------------------+ 
| Now () | 
+---------------------+ 
| 2009-09-15 22:35:32 | 
+---------------------+ 
1 row in Set (0.04 sec)

3. Show month and year date

SELECT DayOfMonth (current_date); 
+--------------------------+ 
| DayOfMonth (current_date) | 
+--------------------------+ | 
+--------------------------+ 
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) | 
+--------------------+ 
| 2009 | 
+--------------------+ 
1 row in Set (0.00 sec)

4. Display string

Mysql> Select "Welecome to my blog!"; 
+----------------------+ 
| welecome to my blog! | 
+----------------------+ 
| welecome to my blog! | 
+----------------------+ 
1 row in Set (0.00 sec)

5. When calculator with

Select ((4 * 4)/ten) +; 
+----------------------+ 
| ((4 * 4)/ten | 
+----------------------+ 
| 26.60 | 
+----------------------+ 
1 row in Set (0.00 sec)

6. String Connection Strings

Select CONCAT (F_name, "", L_name) as 
name from 
employee_data 
where title = ' Marketing Executive '; 
+---------------+ 
| Name | 
+---------------+ 
| Monica Sehgal | 
| Hal Simlai | 
| Joseph Irvine | 
+---------------+ 
3 rows in Set (0.00 sec) 

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

5. Data table operation

5.1 Creating data tables
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));

5.2 Deleting data tables
Command: DROP table < table name >

For example, to delete a table named MyClass

 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 will be canceled, so be careful with this statement!

Note: For a table with partitions, the DROP table permanently cancels the table definition, cancels the partitions, and cancels all data stored in those partitions. The DROP table also cancels the partition definition (. par) file associated with the canceled table.

For tables that do not exist, use if exists to prevent errors from occurring. When you use the if exists, a note is generated for each table that does not exist.

Restrict and cascade can make partitioning easier. At present, restrict and cascade do not work.

5.3 Table Insert Data
Command: INSERT into < table name > [< field name 1>[,.. < field name n >]] VALUES (value 1) [, (value N)]

For example: Insert two records into table MyClass, two records indicate that: Tom with a number 1 is 96.45, and the number 2 for Joan is 82.99, and the number 3 for Wang is 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 in the table at a time.

5.4 Data in a query table
1), Query all lines
Command: Select < Field 1, field 2,...> from < table name > where < expression >
For example: View all data in table MyClass

 Mysql> select * from MyClass;

2), query the first few lines of data
For example: View the first 2 rows of data in table MyClass

Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;

The select is typically used with where to query for more precise and complex data.

5.5 Deleting data in a table

Command: Delete from table name where expression

For example: Delete a record in table MyClass that is numbered 1

Mysql> Delete from MyClass where id=1;

The following is a comparison of the tables before and after the data is deleted.

The following is an example of a PHP code that deletes all lastname= ' Griffin ' records in the "Persons" table:

<?php 
 $con = mysql_connect ("localhost", "Peter", "abc123"); 
 if (! $con) 
 {
 die (' Could not connect: '. Mysql_error ()); 
 } 
 mysql_select_db ("my_db", $con); 
 mysql_query ("DELETE from Persons WHERE lastname= ' Griffin '"); Mysql_close ($con); 
? >

After this deletion, the table is like this:

5.6 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: Tanku's MySQL UPDATE statement:

Copy Code code 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:

Copy Code code as follows:

UPDATE [low_priority] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition]

The update syntax can update columns in existing table rows with new values. The SET clause indicates which columns to modify and what 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 sequence specified. The limit clause is used to limit the number of rows that can be updated, given a limit.

5.7 Add Field
Command: ALTER TABLE name add field type other;
For example, a field passtest is added to the table MyClass, the type is int (4), and the default value is 0

 Mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 '

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 Primary keyword
Mysql> ALTER TABLE name add primary key (field name);
Example: mysql> ALTER TABLE employee ADD primary key (ID);

Index with unique restriction criteria
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 index name;
Example: Mysql>alter table employee DROP index emp_name;

Add fields:
mysql> ALTER TABLE table_name ADD field_name Field_type;

To modify the original field name and type:
mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;

To delete a field:
MySQL ALTER TABLE table_name DROP field_name;

5.8 Modifying table names
Command: Rename table original table name to new name;

For example: In the table MyClass the name is changed to Youclass
Mysql> Rename table MyClass to Youclass;

When you perform 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 multiple table renaming, it will reverse the renaming of all renamed tables and return everything to its original state.

RENAME TABLE is added to the MySQL 3.23.23.

6. Backup Database
Command executes in DOS [Url=file://\\mysql\\bin]\\mysql\\bin[/url] Directory

1. Export the entire database
The export file defaults to the existence of the Mysql\bin directory
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. With language parameter export
mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name.sql

For example, the AAA library is backed up to file back_aaa:
[Root@test1 root]# Cd/home/data/mysql
[Root@test1 mysql]# mysqldump-u root-p--opt aaa > BACK_AAA

7.1 An instance of building and building a table 1

Drop database if exists school; If there is school, delete the
CREATE DATABASE school//build library school use
School;//Open Library school
CREATE TABLE teacher// CREATE TABLE teacher
(
 ID int (3) auto_increment NOT NULL primary key,
 name char (x) not NULL, address
 varchar D Efault ' Shenzhen ', year
 date
);//table End

//below Insert field inserts into
teacher values (", ' Allen ', ' Dalian One '," 1976-10-10′);
Insert into teacher values (", ' Jack ', ' Dalian II ', ' 1975-12-23′");

If you type the above command at the MySQL prompt, it's not easy to debug.
1, you can write the above command as the original text file, assumed to be school.sql, and then copied to c:\\, and in the DOS state into the directory [Url=file://\\mysql\\bin]\\mysql\\bin[/url], Then type the following command:
Mysql-uroot-p Password < C:\\school.sql
If successful, there is no display on a single line, and if there is an error, there is a hint. (The above command has been debugged and you can use it only if you remove the comment.)

2, or enter the command line after the use of mysql> source c:\\school.sql; You can also import the School.sql file into the database.

7.2 An instance of building and building a table 2

Drop database if exists school; If there is school, delete the
CREATE DATABASE school//build library school use
School;//Open Library school
CREATE TABLE teacher// CREATE TABLE teacher
(
 ID int (3) auto_increment NOT NULL primary key,
 name char (x) not NULL, address
 varchar D Efault ' Shenzhen ', year
 date
);//table End/

/below Insert field inserts into
teacher values (', ', ' Glchengang ', ' Shenzhen one "," "1976-10-10");
Insert into teacher values (', ', ' Jack ', ' ' Shenzhen One ', ' 1975-12-23 ');

Note: In the construction of the table
1. Set the ID to a number field of length 3: Int (3), and let it automatically add one to each record: auto_increment; not null; and let him be the main field primary key.

2, set name to a length of 10 character field

3, the address set to the length of 50 character field, and the default value is Shenzhen.

4. Set year as Date field.