Notes on some common MySQL command and script

Record mysql commands and scripts that are frequently used in development for backup. I haven't written SQL for a while recently, and it's a little unfamiliar.

(The following code is developed in winxp. Open a command prompt ).

First, start and stop the mysql Service

Net stop mysql

Net start mysql

Second, log on to mysql

The syntax is mysql-h host-u username-p secret

The example is mysql-hlocalhost-uroot-p123456.

Make sure that the remote connection is selected during mysql installation. If you log on to the local computer,-h can be omitted. type the mysql-uroot-p command, press enter, and prompt you to enter the password, enter 123456, and then press enter to enter mysql.

Third, add new users

The syntax is grant permission on database. Table to user name @ login host identified by "password"

Example:

All permissions grant all privalleges on zf. * to guqin @ localhost identified by "123456 ″

Select permission grant all select on zf. * to guqin @ localhost identified by "123456 ″

Step 4: operate databases

Log on to mysql and run the following commands at the mysql prompt. Each Command ends with a semicolon.

1. display the Database List.

Show databases;

2. display the data tables in the database.

Use mysql;

Show tables;

3. display the data table structure.

Desc indicates;

4. Create and delete Databases

Create database name;

Drop database name;

5. Create and Delete tables.

Use Database Name;

Create table Name (Field List );

Drop table name;

6. Clear the table records.

Delete from table name;

7. display the records in the table.

Select * from table name;

Step 5: export and import data

1. Export data.

The syntax is mysqldump-opt database name. Table Name> c: \ data. SQL

The example is mysqldump-hlocalhost-uroot-p123456 zf. user> c: \ data. SQL.

2. import data:

The syntax is mysqldump-opt database name <c: \ data. SQL

The example is mysqldump-hlocalhost-uroot-p123456 zf or

Mysqlimport-hlocalhost-u root-p123456 <c: \ data. SQL

3. Import text data to the database:

Use Database Name;

Load data local infile "file name" into table name;

Step 6: Create a database table

Create table mytable (name VARCHAR (20), sex CHAR (1 ));

7. Add records to the table

Insert into table name values ("1", "2 ″);

8. Use text to load data into database tables

Load data local infile "c:/data. SQL" INTO TABLE name;

9. Import the. SQL FILE command (for example, c:/data. SQL)

Use database;

Source c:/data. SQL

10. Update table data

Update MYTABLE set sex = "f" where name = 'hyq ';

11th repair table

Repair table name

12th view the table size

Show table name status

13th Change Password

Mysqladmin-u username-p old password "new password"

14th Modify Table Structure

Alter table t1 MODIFY B BIGINT NOT NULL;

14th quit MYSQL Command

Exit or quit (Press ENTER)

The above is my mysql Operation Command, which is very convenient. I feel much better than using gui.