MySQL Common commands

MySQL Common Command Daquan

1. mysql: Connect to the database

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 the prompt you

The password is lost. 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 into MYSQL,

The 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 use no space between the root and the other)

3) Exit MYSQL command Exit (Enter)

2. Mysqladmin: Modify user Password

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. Grant on: New user

The grant command is used to add new users and control their permissions.

Grant on command 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 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 be like someone to know test1 password, then he can on the Internet

Log on to your MySQL database on any computer and can do whatever you like with your data, the solution is as follows.

2) Add a user test2 password to ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MYSQL database is the host), so that users will use to know T Est2 password, he also cannot access the database directly from the Internet, only through the Web page on the MYSQL host to access.

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 [[E-mail Protected]][email protected][/email] identified by "";

4. Create: Creating a Database

The Create command is used for creating the database.

Create command format: CREATE DATABASE < DB 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:

A:create database name;

B:grant select,insert,update,delete,create,drop,alter on database name. * To

Database name @localhost identified by ' password '; C: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.

5. Show databases: Display database

The show Databases command is used to display all databases.

Show databases command format: show databases; (Note: Finally there is a s) such as:mysql> show databases;

6. Drop DATABASE: Deleting databases

The drop command is used to delete a database.

Drop command format: Drop databases < database name >; For example, delete a database named XHKDB:

mysql> drop Database xhkdb;

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

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

[Example 2] Delete a database that is not determined to exist:

mysql> drop Database drop_database; ERROR 1008 (HY000): Can ' t drop database ' drop_database '; Database doesn ' 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 mysql> CREATE DATABASE Drop_database; Create a database Query OK, 1 row Affected (0.00 sec) Mysql> drop database if exists drop_database; If exists determines whether the database exists, does not exist or produces an error Query OK, 0 rows Affected (0.00 sec)

7.use: Using the database

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;

Screen tip: Database changed

1) 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 the next different use statement appears:

mysql> use DB1;

Mysql> SELECT COUNT (*) from mytable; # selects from db1.mytable mysql> use DB2;

Mysql> SELECT COUNT (*) from mytable; # selects from db2.mytable

2) using the USE statement to mark a particular 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 don't have to quit. Use the database after using show databases

Can query all databases, if you want to jump to another database, use the other database name.

8. Select: the currently connected database

The Select command represents the currently selected (connected) database.

Select command format: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) 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.

9. CREATE TABLE: Creating a data table

The data table belongs to the database, and the statement "use < database name >" should be used before creating the data table to specify the database in which the operation is to be made, and if no database is selected, the error "No database selected" will be thrown. The statement creating the data table is create table with the following syntax rules:

CREATE table < table name > ( Field name 1, data type [column-level constraint] [default], field name 2, data type [column-level constraint] [default], ?? [table-level constraints] );

When you create a table using a CREATE TABLE, you must specify the following information:

(1) The name of the table to be created is case-insensitive and cannot be used with keywords in the SQL language, such as DROP, ALTER,

INSERT, and so on.

(2) The name and data type of each column (field) in the datasheet, separated by commas if multiple columns are created.

Create the Employee table TB_EMP1, as shown in the following table.

Table TB_EMP1 Table Structure

Field name	Data type	Note
Id	INT (11)	Employee number
Name	VARCHAR (25)	Employee Name
DeptID	INT (11)	Department number
Salary	FLOAT	Wages

First create the database, the SQL statement is as follows:

CREATE DATABASE test_db;

Select the database that created the table, and the SQL statements are as follows:

Use test_db;

To create the TB_EMP1 table, the SQL statement is:

CREATE TABLE TB_EMP1

(

ID Int (one), name VARCHAR (+), DeptID Int (one), salary FLOAT );

After the statement is executed, a data table named TB_EMP1 is created, using the show TABLES statement to view the data

The table is created successfully, and the SQL statement is as follows:

Mysql> SHOW TABLES;

+-----------------------+

| Tables_in_ test_db |

+----------------------+

| TB_EMP1 |

+----------------------+

1 row in Set (0.00 sec)

As you can see, the data table TB_TMP1 is already in the TEST_DB database and the data table is created successfully.

Desc: Get Table structure

In MySQL, the view table structure can use the DESCRIBE and show CREATE table statements.

The DESCRIBE/DESC statement can view field information for a table, including: Field name, field data type, whether it is a primary key, whether it has a default value, and so on. The syntax rules are as follows:

DESCRIBE table name;

or shorthand for:

DESC table name;

The show CREATE TABLE statement can be used to display the CREATE TABLE statement when creating tables with the syntax in the following format:

SHOW CREATE table < table name \g>;

Using the show CREATE TABLE statement, you can see not only the detailed statement when the table was created, but also

Look at the storage engine and character encoding.

If you do not add the ' \g ' parameter, the displayed result can be very confusing, plus the parameter ' \g ', to make the display results more intuitive,

Easy to see. Using the show CREATE table to view the details of the table TB_EMP1, the SQL statements are as follows:

mysql> SHOW CREATE TABLE tb_emp1;

drop TABLE: Delete data table

In MySQL, you can use drop table to delete one or more data tables that are not associated with another table at a time. Language

The legal format is as follows:

DROP table [IF EXISTS] table 1, table 2, ... Table N;

where "table n" refers to the name of the table to be deleted, you can delete more than one table at a time, simply write the name of the table you want to delete

Behind, separated from each other by commas. If the data table you want to delete does not exist, MySQL will prompt an error message,

"ERROR 1051 (42S02): Unknown table Name '". The parameter "if EXISTS" is used to determine if the deleted table exists before deleting it, adding the parameter, and then deleting the table, if the table does not exist, the SQL statement can execute smoothly, but will issue a warning (warning).

In the previous example, a data table named Tb_dept2 has been created. If not, the reader can enter a statement to create the

Table, the SQL statement is shown in Example 4.8. Use the DELETE statement below to delete the table. Delete the data table Tb_dept2,sql statement as follows:

DROP TABLE IF EXISTS tb_dept2;

INSERT into: Inserting data into a table

The INSERT INTO statement is used to insert a new row into the table. The syntax is as follows:

INSERT into table name values (value 1, value 2,....)

We can also specify the columns for which you want to insert data:

INSERT into table_name (column 1, column 2,...) Values (value 1, value 2,....)

"Example" creates a data table Tmp3, defines a field y with a data type of year, inserts a value in the table, ' "," the SQL statement is as follows: first CREATE TABLE Tmp3:

CREATE TABLE Tmp3 (y year);

To insert data into a table:

Mysql> INSERT into Tmp3 values (2010), (' 2010 ');

Select from: Querying Data in a table

MySQL The basic statement that queries data from a data table is a SELECT statement. The basic format of the SELECT statement is:

SELECT

{* | < field list;}

[

From < table 1>,< table 2> .....

[WHERE < expressions >

[GROUP by <group by Definition>]

[Having <expression> [{<operator> <expression>} ...]

[ORDER by <order by Definition>]

[LIMIT [<offset>,] <row Count>]

]

SELECT [Field 1, Field 2,..., field N]

From [table or view]

WHERE [query condition];

Among them, the meaning of the sentence is as follows:

{* | < Field List} contains the asterisk wildcard Check field list, which represents the field of the query, where the field column contains at least one field name, if you want to query multiple fields, multiple fields are separated by commas, and the last field is not followed by commas.

From < table 1>,< table 2>.., Table 1 and table 2 represent the source of the query data, which can be single or multiple.

The WHERE clause is optional and, if selected, qualifies the query criteria that the query row must satisfy.

Group by < Field; This clause tells MySQL how to display the queried data and group them according to the specified fields.

[Order by < Field;], which tells MySQL what order to display the queried data, the sort that can be sorted by ascending (ASC), descending (DESC).

[LIMIT [<offset>,] <row count>], this clause tells MySQL to display the number of data bars each time the query comes out.

Delete from: Delete a record

Delete data from a data table using the DELETE statement, the DELETE statement allows the WHERE clause to specify the delete condition. The basic syntax format for the DELETE statement is as follows:

DELETE from table_name [WHERE <condition>];

TABLE_NAME Specifies the table to perform the delete operation; "[WHERE <condition>]" is an optional parameter that specifies the delete condition,

If there is no WHERE clause, the DELETE statement deletes all records in the table.

"Example" in the person table, delete the record with ID equal to 11, the SQL statement is as follows:

Mysql> DELETE from the person WHERE id = 11; Query OK, 1 row affected (0.02 sec)

Update set: Modifying data in a table

MySQL updates the records in the table using the UPDATE statement to update specific rows or to update all rows at the same time.

The basic syntax structure is as follows:

UPDATE table_name

SET column_name1 = value1,column_name2=value2,......, column_namen=valuen WHERE (condition);

Column_name1,column_name2,......, Column_namen The name of the field for the specified update; value1,

value2,...... Valuen The updated value for the specified field, condition specifies the condition that the updated record needs to meet. When you update multiple columns, each column-value pair is separated by commas and no commas are required after the last column.

"Example" in the person table, update the record with ID value 11, change the Age field value to 15, and change the Name field value to

The Liming,sql statement is as follows:

UPDATE person SET-age = Name= ' liming ' WHERE id = 11;

Alter ADD: Add Field

The syntax format for adding fields is as follows:

ALTER table < table name > ADD < new field name > < data type > [Constraint conditions] [First | After existing field name];

The new field name is the name of the field you want to add, and "first" is an optional parameter that sets the newly added field to the field in the table, and "after" is an optional parameter that adds the newly added field to the specified existing field name.

"Example" adds a field ManagerID (department manager number) with an INT type that does not have an integrity constraint in the data table TB_DEPT1, and the SQL statement is as follows:

ALTER TABLE tb_dept1 ADD managerid INT (10);

Rename: Modifying table names

MySQL uses the ALTER TABLE statement to implement the table name modification, with the following syntax rules:

ALTER table < old table name > RENAME [to] < new table name >;

Where to is an optional parameter, the use or not affects the result.

"Example" Rename the data table TB_DEPT3 to the Tb_deptment3,sql statement as follows:

ALTER TABLE tb_dept3 RENAME tb_deptment3;

Mysqldump: Backing Up the database

The basic syntax format for MYSQLDUMP BACKUP database statements is as follows:

Mysqldump–u user–h Host–ppassword dbname[tbname, [tbname ...] > Filename.sql

The user represents the username, the host name of the logged-on user, the login password, dbname the name of the database that needs to be backed up, Tbname is the data table in the DBName database that needs to be backed up, and the right arrow symbol ">" Password Tells Mysqldump to write the definition and data of the backup data table to the backup file; Filename.sql is the name of the backup file.

"Example" uses the mysqldump command to back up all the tables in the database, performing the following procedure: Open the operating system command Line Input window and enter the backup command as follows:

C \ >mysqldump-u root-p booksdb > C:/backup/booksdb_20130301.sql

Enter Password: * *

After entering the password, MySQL backs up the database and looks under the C:\backup folder for the files you just backed up.

MySQL and source: Restoring a Database

For a text file that has been backed up that contains CREATE, INSERT statements, you can use the MySQL command to import to the database

In

The backup SQL file contains CREATE, INSERT statements (and sometimes DROP statements). The MySQL command can

Execute these statements directly in the file. Its syntax is as follows:

Mysql–u user–p [dbname] < Filename.sql

User is the username that executes the statement in Backup.sql, and-p indicates the user password; dbname is the database name. If the Filename.sql file is created for the Mysqldump tool that contains the file that created the database statement, you do not need to specify the database name when executing.

Example 1 uses the MySQL command to import the backup from the C:\backup\booksdb_20130301.sql file into the database, with the following input statement:

Mysql–u Root–p Booksdb < C:/backup/booksdb_20130301.sql

Before executing the statement, the BOOKSDB database must be created in the MySQL server and an error will occur if no recovery process exists. After the command executes successfully, the statement in the Booksdb_20130301.sql file restores the previous table in the specified database. If you are already logged in to the MySQL server, you can also import the SQL file using the source command. The source statement syntax is as follows:

SOURCE filename

"Example 2" uses the root user to log on to the server and then uses source to import the local backup file

Booksdb_20110101.sql, enter the following statement:

--Select the database to be restored mysql> use BOOKSDB; Database changed --Import the backup file using the source command Mysql> Source C:\backup\booksDB_20130301.sql

When the command executes, it lists the results of the execution of each statement in the backup file Booksdb_20130301.sql. After the source command executes successfully, the statements in Booksdb_20130301.sql are all imported into the existing database.

Mysqlhotcopy: Fast Database recovery

Mysqlhotcopy backup files can also be used to restore the database, when the MySQL server stopped running, the backup database files copied to the location of MySQL storage data (MySQL Data folder), restart the MySQL service. If you do this with the root user, you must specify the owner of the database file and enter the following statement:

Chown-r Mysql.mysql/var/lib/mysql/dbname

"Example" to recover a database from a backup copy of Mysqlhotcopy, enter the following statement:

Cp-r/usr/backup/test Usr/local/mysql/data

After executing the statement, restart the server, and MySQL will revert back to the backup state.

MySQL Common commands