MySQL Command collection

1. mysql commands the user to connect to the database

MySQL command format: mysql-h host address-u user name-P user password

(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>

(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 spaces between the root and the other) (3) exit the MySQL command
Exit (Enter)

2. The mysqladmin command is used to modify the user password.
mysqladmin command format:mysqladmin-u User Name----old password password new password
1) Add a password to root ab12
First enter directory Mysql\bin under DOS, and then type the following command:
Mysqladmin-u Root-password AB12
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.
2) then change the root password to djg345
Mysqladmin-u root-p ab12 Password djg345

3. The grant on command is used to add new users and control their permissions.
Grant on command format:grantselect 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 the root user, and then type the following command:
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 into your MySQL database and your data can do whatever you like, the solution is as follows.
2) Add a user test2 password for ABC, so that he can only log on on localhost, and can query, insert, modify, delete the database mydb (localhost refers to the local host, that is, the MySQL database is located in 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.

Grant Select,insert,update,delete on Mydb.*to [[E-mail 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.
Grant Select,insert,update,delete on mydb.* to[[email protected]][email Protected][/email] identified by "";

4. The create command is used for creating the database.

Create command format:CREATE DATABASE < databases name >;
Note: Connect to the MySQL server before creating the database.
1) Create a database named XHKDB:
mysql> CREATE DATABASE xhkdb;
2) Create a database and assign users:

CREATE database name;

Grantselect,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 in turn to complete the database creation.

5. The show databases command is used to display all databases.
Show databases command format: showdatabases; (Note: There is a last s)
For example:mysql> show databases;
Note: To modify the default encoding of the database in order not to be garbled at the time of display. The following is an example of the GBK encoding page.

1) Modify the MySQL configuration file: My.ini inside Modify DEFAULT-CHARACTER-SET=GBK
2) Code Runtime Modification:

Java code: JDBC:MYSQL://LOCALHOST:3306/TEST?USEUNICODE=TRUE&CHARACTERENCODING=GBK

PHP Code: Header ("content-type:text/html;charset=gb2312");

C Language code: Intmysql_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 proofing for character sets. This function works similarly to the set names statement, but it also sets the value of mysql-> CharSet, which affects the character sets set by the Mysql_real_escape_string ().

The

6, Drop command is used to delete the database.
Drop command format: dropdatabase < database name;;
For example, to delete a database named xhkdb:
mysql> drop db xhkdb;
[Example 1] Delete a database that has been determined to exist:
   mysql> drop db drop_database;
   query OK, 0 rows Affected (0.00 sec)
[Example 2] Delete an indeterminate database:
    mysql> drop DB Drop_da Tabase;
    ERROR 1008 (HY000): Can ' t drop database ' drop_database '; databasedoesn ' t exist
    // An error occurred and the ' drop_database ' database could not be deleted, and 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
    MYSQ l> CREATE DATABASE Drop_database;  //Create a database
    Query OK, 1 row Affected (0.00 sec)
    mysql> drop database if exists D Rop_database;  //ifexists to determine if the database exists, does not exist or produces an error
    Query OK, 0 rows Affected (0.00 sec)

7, the use command allows us to work with the database.
Use command format: use< database name;;
For example, if the XHKDB database exists, try to access it:
   mysql> use XHKDB;
ScreenTip: Database changed
1) 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 the next different use statement appears:
   mysql> use DB1;
   mysql> SELECT COUNT (*) from mytable;   # selects fromdb1.mytable
   mysql> use DB2;
   mysql> SELECT COUNT (*) from mytable;   # selects fromdb2.mytable

2) 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 editor 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 quit after the connection. In fact, you do not have to quit, use the database, using show databases can query all the database, if you want to jump to another database, with
    use other database name
on it.

8. The Select command represents the currently selected (connected) database.
Select command Format:mysql> selectdatabase ();
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) 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) |
+--------------------+
| 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 the calculator uses
Select ((4 * 4)/10) + 25;
+----------------------+
| ((4 * 4)/10) + 25 |
+----------------------+
| 26.60 |
+----------------------+
1 row in Set (0.00 sec)
6) Threaded string
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 used the previously learned as to give the result column ' CONCAT (f_name, "", L_name) ' a pseudonym.

The

9, CREATE TABLE command is used for creating data tables.
CREATE TABLE command format: createtable < table name > (< Field name 1>< type 1> [,.. < field name n>< type n>]);
For example, create a table named MyClass:

4

Field name	Numeric type	Data width	Whether null	Whether primary key	Automatically add	Default
ID	int	4	No	Primary key	auto_increment
Name	Char	$	No	&N BSP;
Sex	int	No			0
Degree	Double	+	Yes

Mysql> CREATE TABLE MyClass (
> ID int (4) NOT null primary key auto_increment,
> Name char () NOT NULL,
> Sex int (4) NOT null default ' 0 ',
> Degree double (16,2));

10. The desc command is used to get the data table structure.
DESC command format:desc table name;
Same
Show columns from table name;
The data table structure can also be obtained.

Examples are as follows:
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 '.

If the key is empty, the column value can be duplicated, indicating that the column has no index, or is a non-leading column of a non-unique composite index;

If key is a PRI, then the column is part of the primary key;

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);

If key is Mul, then 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 satisfies many of the above 4 cases, for example, if a column is both a PRI and a UNI, then the key value shown in the "DESC table name" is displayed as a priority for 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.

the drop TABLE command is used to delete a data table.
DROP TABLE command format:drop table < table name >;
For example, delete a table with a table named MyClass:
mysql> drop table MyClass;
Drop table is used to delete 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 also cancels the partition definition (. par) file that is associated with the canceled table.

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

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

12. The insert INTO command is used to insert data into the table.
INSERT INTO command format: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 for the named Joan, and 82.99 for the number 3.
mysql> INSERT INTO Myclassvalues (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.

13. The Select from command is used to query the data in the table.

1) Query All rows
Command format: select< field 1, Field 2, ...> from < table name > where < expression >;

For example, view all the data in the table MyClass:
Mysql> select * from MyClass;

2) Query the first few rows of data
For example, look at the first 2 rows of data in 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.

14. The delete from command is used to delete data from the table.
Delete from command format: Delete from table name where expression
For example, delete the record numbered 1 in table MyClass:
Mysql> Delete from MyClass where id=1;
Please compare the table changes before and after deleting the data.

FirstName	LastName	Age
Peter	Griffin	35
Glenn	Quagmire	33

The following is an example of PHP code to delete 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 ("Deletefrom Persons WHERE lastname= ' Griffin '"); Mysql_close ($con);

?>

After this deletion, the table is like this:

FirstName	LastName	Age
Glenn	Quagmire	33

15. The update set command is used to modify the data in the table.
Update Set command format: Update table name SET field = new value,... where condition;

Examples are as follows:
mysql> Update MyClass set name= ' Mary ' where id=1;

Example 1, a single-table MySQL UPDATE statement:
UPDATE [low_priority] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition] [ORDER by ...] [LIMIT Row_count];

Example 2, the UPDATE statement for multiple tables:
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.

16. The Alter ADD command is used to increase the table field.
alter addcommand format: ALTER TABLE name add field type other;

For example, a field passtest is added to table MyClass, the type is int (4), and the default value is 0:
Mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 ';

1) 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);

2) Index of the plus primary keyword
Mysql> ALTER TABLE name add primary key (field name);

Example: mysql> ALTER TABLE employee ADD primary key (ID);

3) index with unique restriction conditions
Mysql> ALTER TABLE name add unique index name (field name);

Example: mysql> ALTER TABLE employee add uniqueemp_name2 (cardnumber);

4) Delete an index
mysql> ALTER TABLE name DROP INDEX name;

Example: Mysql>alter table employee DROP index emp_name;

5) Add Field
mysql> ALTER TABLE table_name ADD field_name Field_type;

6) Modify the original field name and type
mysql> ALTER TABLE table_name change old_field_namenew_field_name field_type;

7) Delete Field
MySQL ALTER TABLE table_name DROP field_name;

17. The rename command is used to modify the table name.
Rename command format: Rename table name to the new table name;

For example, change the name of the table MyClass to Youclass:
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.

The

18, mysqldump command is used to back up the database. The
mysqldump command is executed under the DOS [Url=file://\\mysql\\bin]\\mysql\\bin[/url] directory .

1) Export the entire database (the export file by default exists in the Mysql\bin directory)
    MYSQLDUMP-U user name-p database name > exported filename
    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-tabledatabase_name > Outfile_ Name.sql
    D No data –add-drop-table add a drop table

4) with language parameter export before each CREATE statement
     Mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-optdatabase_name > Outfile_name.sql

For example, to back up the AAA library to file back_aaa:
[[email protected] root]# cd/home/data/mysql
[[email protected] mysql]# mysqldump-u root-p--opt aaa > BACK_AAA

Example 1,

Drop database if exists school; Delete if school is present
Create Database School; Building a library School
Use school; Open Library School
CREATE TABLE Teacher (//Build tables Teacher
ID int (3) auto_increment NOT null primary key,
Name Char (TEN) is not NULL,
Address varchar (+) Default ' Shenzhen ',
Year Date
); End of Build table

The following is the Insert field
Insert into teacher values (", ' Allen ', ' Dalian One ', ' 1976-10-10′ ');
Insert into teacher values (", ' Jack ', ' Dalian II ', ' 1975-12-23′ ');

It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug. There are two ways to solve this problem:

You can write the above command as-is to a text file, assume that it is school.sql, then copy it to c:\\, and enter the directory in the DOS state [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).

Either use mysql> source c:\\school.sql after entering the command line, or you can import the School.sql file into the database.

Example 2,

Drop database if exists school; Delete if school is present
Create Database School; Building a library School
Use school; Open Library School
CREATE TABLE Teacher (//Build tables Teacher
ID int (3) auto_increment NOT null primary key,
Name Char (TEN) is not NULL,
Address varchar (+) Default ' Shenzhen ',
Year Date
); End of Build table

The following is the Insert field
Insert into teacher values (' ' ', ' Glchengang ', ' Shenzhen One ', ' 1976-10-10 ');
Insert into teacher values (' ' ', ' Jack ', ' Shenzhen One ', ' 1975-12-23 ');

Note that in the table under construction:

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.

Set name to a character field of length 10

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

Set year as the Date field.

MySQL Command collection