Shulou(Shulou.com)06/01 Report--

A complete Collection of commonly used MySQL commands

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 the MYSQL on the local computer

First open the DOS window, then enter the directory mysql\ bin, and then type the command mysql-u root-p. Enter and prompt you.

Enter the password.

Note that there can be spaces or no spaces before the user name, but there must be no spaces before the password, otherwise you will be asked to re-enter the password

Size.

If you have just installed MYSQL, the superuser root does not have a password, so you can enter MYSQL directly by pressing enter.

The prompt for MYSQL is: mysql >

2) Connect to the MYSQL on the remote host

Suppose the IP of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Type the following command:

Mysql-h210.110.110.110-u root-p 123; (note: there can be no spaces between u and root, and so do others)

3) exit the MYSQL command

Exit (enter)

2. Mysqladmin: change the user's password

The mysqladmin command is used to change the user's password.

Mysqladmin command format: mysqladmin-u username-p old password password new password

1) add a password to root ab12

First enter the directory mysql\ bin under 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) change the password of root to djg345

Mysqladmin-u root-p ab12 password djg345

3. Grant on: new users

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

Grant on command format: grant select on database. * to username @ login host identified by "password"

1) add a user test1 with a password of abc so that he can log in on any host and query all databases.

Permissions to insert, modify, and delete. First connect to MYSQL with the root user, and then type the following command:

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

But the increase in the number of users is very dangerous. If you want someone who knows the password of test1, he can use internet.

Log in to your mysql database on any computer and do whatever you want with your data. The solution is as follows.

2) add a user's test2 password to abc, so that he can only log in on localhost, and can mydb the database

Query, insert, modify and delete operations (localhost refers to the local host, that is, the master where the MYSQL database is located

Machine), so that even if the user knows the test2 password, he cannot access the database directly from the internet, only through the

The web page on the MYSQL host has come to visit.

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

"abc"

If you don't want test2 to have a password, you can issue another command to eliminate the password.

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

4. Create: create a database

The create command is used to create a database.

Create command format: create database

Note: connect to the Mysql server before creating the database.

1) set up a database named xhkdb:

Mysql > create database xhkdb

2) create a database and assign users:

A: CREATE DATABASE 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 three commands in turn to complete the database creation.

Note: Chinese "password" and "database" are set by users themselves.

5. Show databases: displays the database

The show databases command displays all databases.

Show databases command format: show databases; (Note: there is an s at the end)

For example: mysql > show databases

6. Drop database: delete the database

The drop command is used to delete the database.

Drop command format: drop database

For example, delete the 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 an uncertain database:

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 cannot be deleted. It does not exist.

Mysql > drop database if exists drop_database

Query OK, 0 rows affected, 1 warning (0.00 sec)

/ / generate a warning that the 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 to determine whether the database exists, does not exist and does not generate an error

Query OK, 0 rows affected (0.00 sec)

7. Use: working with databases

The use command allows us to use the database.

Use command format: use

For example, if the xhkdb database exists, try to access it:

Mysql > use xhkdb

Screen Tip: Database changed

1) the use statement can tell MySQL to use the db_name database as the default (current) database for follow-up

Sentence. The database remains the default database until the end of the paragraph 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 USE statements to mark a specific current database does not prevent you from accessing tables in other databases. Lower

For example, you can access the author table from the db1 database and 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

Use statements are set up to be compatible with Sybase.

Some netizens asked how to exit after the connection. In fact, there is no need to check out, after use database, use show databases

You can query all databases. If you want to jump to other databases, use the

Use other database names will be fine.

8. Select: 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. You can use it to display a word.

Strings, numbers, the results of mathematical expressions, and so on. How can I use the special features of the SELECT command in MySQL?

1) display the version of MYSQL

Mysql > select version ()

+-- +

| | version () |

+-- +

| | 6.0.4-alpha-community |

+-- +

1 row in set (0.02 sec)

2) display the current time

Mysql > select now ()

+-+

| | now () |

+-+

| | 2009-09-15 22:35:32 |

+-+

1 row in set (0.04 sec)

3) display year, month and 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) used as a calculator

Select ((4 * 4) / 10) + 25

+-+

| | (4 * 4) / 10) + 25 |

+-+

| | 26.60 |

+-+

1 row in set (0.00 sec)

6) concatenate 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 concatenate strings. In addition, we also use the AS we learned before.

Give the result column 'CONCAT (f_name, ", l_name) with a pseudonym.

9. Create table: create a data table

The data table belongs to the database. Before creating the data table, you should use the statement "USE" to specify where the operation is.

If no database is selected, a "No database selected" error will be thrown.

The statement to create the data table is CREATE TABLE, and the syntax rules are as follows:

CREATE TABLE

(

Field name 1, data type [column level constraint] [default]

Field name 2, data type [column level constraints] [default]

[table level constraints]

);

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

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

INSERT et al.

(2) the name and data type of each column (field) in the data table. If you create multiple columns, separate them with commas.

Create the employee table tb_emp1, with the structure shown in the following table.

Table tb_emp1 table structure

Field name data type remarks idINT (11) employee number nameVARCHAR (25) employee name deptIdINT (11) Department number salaryFLOAT salary

First, create the database with the following SQL statement:

CREATE DATABASE test_db

Select the database to create the table, and the SQL statement is as follows:

USE test_db

To create a tb_emp1 table, the SQL statement is:

CREATE TABLE tb_emp1

(

Id INT (11)

Name VARCHAR (25)

DeptId INT (11)

Salary FLOAT

);

After the statement is executed, a data table named tb_emp1 is created, and the data is viewed using the SHOW TABLES; statement

Whether the table is created successfully or not, 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, there is already a data table tb_tmp1 in the test_db database, and the data table is created successfully.

10. Desc: get the table structure

In MySQL, you can use DESCRIBE and SHOW CREATE TABLE statements to view the table structure.

The DESCRIBE/DESC statement can view the field information of the table, including: field name, field data type, whether it is primary or not

Key, whether there is a default value, etc. The grammar rules are as follows:

DESCRIBE table name

Or abbreviated as:

DESC table name

The SHOW CREATE TABLE statement can be used to display the CREATE TABLE statement when the table was created, and the syntax format

As follows:

SHOW CREATE TABLE

Using the SHOW CREATE TABLE statement, you can not only view the detailed statements when the table is created, but also look up the

Look at the storage engine and character encoding.

If the parameter'\ G' is not added, the display result may be very confusing. After adding the parameter'\ G', the display result can be more intuitive.

Easy to view.

Use SHOW CREATE TABLE to view the details of the table tb_emp1, and the SQL statement is as follows:

Mysql > SHOW CREATE TABLE tb_emp1

11. Drop table: delete data tables

In MySQL, you can use DROP TABLE to delete one or more data tables that are not associated with other tables at a time. Language

The format of the method 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, and then multiple tables can be deleted at the same time, as long as the names of the tables to be deleted are written in the

In the back, just separate each other with a comma. If the data table to be deleted does not exist, MySQL prompts an error message

"ERROR 1051 (42S02): Unknown table 'Table name'". The parameter "IF EXISTS" is used to determine that the deleted table is

If the table does not exist, the SQL statement can be executed smoothly, but it will issue

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. Let's delete the table using a delete statement.

Delete the data table tb_dept2. The SQL statement is as follows:

DROP TABLE IF EXISTS tb_dept2

12. Insert into: inserts data into the 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 column in which we want to insert the data:

INSERT INTO table_name (column 1, column 2.) VALUES (value 1, value 2pm.)

[example] create the data table tmp3, define the field y with the data type YEAR, and insert the values 2010, '2010 records, SQL into the table

The statement is as follows:

First create the table tmp3:

CREATE TABLE tmp3 (y YEAR)

Insert data into the table:

Mysql > INSERT INTO tmp3 values (2010), ('2010')

13. Select from: query the data in the table

The basic statement for MySQL to query data from a data table is the SELECT statement. The basic format of the SELECT statement is:

SELECT

{* |}

[

FROM,...

[WHERE

[GROUP BY]

[HAVING [{}...]]

[ORDER BY]

[LIMIT [,]]

]

SELECT [Field 1, Field 2, … , field n]

FROM [table or view]

WHERE [query criteria]

Among them, the meaning of each clause is as follows:

{* |} contains a list of asterisk wildcard selected fields, indicating the fields to be queried, where the field column contains at least one word

Segment name. If you want to query multiple fields, separate them with a comma. Do not add a comma after the last field.

FROM,..., tables 1 and 2 represent the sources of query data, which can be single or multiple.

The WHERE clause is optional, and if selected, defines the query conditions that the query row must meet.

GROUP BY, which tells MySQL how to display the queried data and divides it according to the specified fields

Group.

[ORDER BY], this clause tells MySQL in what order to display the queried data.

The order is: ascending order (ASC), descending order (DESC).

[LIMIT [,]], this clause tells MySQL to display the number of pieces of data that are queried each time.

14. Delete from: delete a record

Delete data from the data table using the DELETE statement, which allows the WHERE clause to specify the delete condition.

The basic syntax format of the DELETE statement is as follows:

DELETE FROM table_name [WHERE]

Table_name specifies the table to delete; "[WHERE]" 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, and the SQL statement is as follows:

Mysql > DELETE FROM person WHERE id = 11

Query OK, 1 row affected (0.02 sec)

15. Update set: modify the data in the table

Using UPDATE statements in MySQL to update records in a table, you can update specific rows or update all rows at the same time.

The basic grammatical 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 is the name of the field that specifies the update; value1

Value2,... Valuen is the update value of the corresponding specified field; condition specifies the conditions that the updated record needs to meet. Update

When there are multiple columns, each column-value pair is separated by a comma, and no comma is required after the last column.

[example] in the person table, update the record with an id value of 11, change the age field value to 15, and change the name field value to

LiMing, the SQL statement is as follows:

UPDATE person SET age = 15, name='LiMing' WHERE id = 11

16. Alter add: add field

The syntax format for adding fields is as follows:

ALTER TABLE ADD

[constraint] [FIRST | AFTER already exists field name]

The new field name is the name of the field to be added; "FIRST" is an optional parameter and its function is to set the newly added field to

The first field of the table; "AFTER" is an optional parameter that adds the newly added field to the specified "existing field"

After "first name".

Add an INT field of type managerId without integrity constraints to the datasheet tb_dept1 (department manager

Number), the SQL statement is as follows:

ALTER TABLE tb_dept1 ADD managerId INT (10)

17. Rename: modify the table name

MySQL modifies the table name through the ALTER TABLE statement. The specific syntax rules are as follows:

ALTER TABLE RENAME [TO]

TO is an optional parameter, and whether it is used or not does not affect the result.

[example] rename the data table tb_dept3 to tb_deptment3, and the SQL statement is as follows:

ALTER TABLE tb_dept3 RENAME tb_deptment3

18. Mysqldump: back up the database

The basic syntax format of the mysqldump backup database statement is as follows:

Mysqldump-u user-h host-ppassword dbname [tbname, [tbname...]] > filename.sql

User represents the user name; host represents the host name of the logged-in user; password is the login password; dbname is the required

Tbname is the data table that needs to be backed up in the dbname database, and you can specify multiple tables to be backed up; right

The arrow symbol ">" tells mysqldump to write the definition and data of the backup data table to the backup file; filename.sql is the backup file.

The name of the.

[example] use the mysqldump command to back up all tables in the database, as follows:

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 at the files you just backed up under the C:\ backup folder.

19. Mysql and source: restore the database

For text files that have been backed up that contain CREATE and INSERT statements, you can import them into the database using the mysql command

Medium.

The backed up sql file contains CREATE, INSERT statements (and sometimes DROP statements). The mysql command can

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

Mysql-u user-p [dbname]

< filename.sql user 是执行 backup.sql 中语句的用户名； -p 表示输入用户密码； dbname 是数据库名。如果 filename.sql 文件为 mysqldump 工具创建的包含创建数据库语句的文件，执行的时候不需要指定数 据库名。 【例 1】使用 mysql 命令将 C:\backup\booksdb_20130301.sql 文件中的备份导入到数据库中，输 入语句如下： mysql -u root -p booksDB < C:/backup/booksdb_20130301.sql 执行该语句前，必须先在 MySQL 服务器中创建 booksDB 数据库，如果不存在恢复过程将会 出错。命令执行成功之后 booksdb_20130301.sql 文件中的语句就会在指定的数据库中恢复以前的 表。 如果已经登录 MySQL 服务器，还可以使用 source 命令导入 sql 文件。 source 语句语法如下： source filename 【 例 2 】 使 用 root 用 户 登 录 到 服 务 器 ， 然 后 使 用 source 导 入 本 地 的 备 份 文 件 booksdb_20110101.sql，输入语句如下： --选择要恢复到的数据库 mysql>

Use booksDB

Database changed

-- Import backup files using the source command

Mysql > source C:\ backup\ booksDB_20130301.sql

After the command is executed, the execution result of each statement in the backup file booksDB_20130301.sql is listed. Source command

After successful execution, all statements in booksDB_20130301.sql are imported into the existing database.

20. Mysqlhotcopy: fast recovery of the database

The files backed up by mysqlhotcopy can also be used to restore the database. When the MySQL server stops running, the

Copy the database files to the location where MySQL stores the data (MySQL's data folder), and restart the MySQL service that is

But. If you do this as the root user, you must specify the owner of the database file, enter the following statement:

Chown-R mysql.mysql / var/lib/mysql/dbname

[example] to restore a database from a backup copied by mysqlhotcopy, enter the following statement:

Cp-R / usr/backup/test usr/local/mysql/data

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.