Common mysql management commands that must be used by administrators in CenOS

Common mysql management commands that must be used by administrators in CenOS
Log on to mysql
Mysql-uroot-plvtao.net
Remote Login
Mysql-h110.110.110.110-uroot-plvtao.net;
Press enter to enter MYSQL. The MYSQL prompt is: mysql>
Exit MYSQL command: exit; (Press ENTER)

Change Password
Format: mysqladmin-u username-p old password new password. For example
Add a password ab12 to the root user. Enter the following command in the shell state:
Mysqladmin-uroot-passwordab12
Change the root password to djg345.
Mysqladmin-uroot-pab12passworddjg345

Create a database
1. CREATEDATABASE database name;
2. GRANTSELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTERON database name. * TO database name @ localhostIDENTIFIEDBY 'Password ';
3. SETPASSWORDFOR 'database name' @ 'localhost' = OLD_PASSWORD ('Password ');

Execute three commands in sequence to create a database. Note: You must set the Chinese "password" and "Database.

Common commands

1. display the list of databases on the current database server:

Mysql> SHOWDATABASES;

2. Create a database:

Mysql> CREATEDATABASE database name;

3. Create a data table:

Mysql> USE Database Name;

Mysql> CREATETABLE table name (field name: VARCHAR (20); field name: CHAR (1 ));

4. delete a database:

Mysql> DROPDATABASE database name;

5. delete a data table:

Mysql> DROPTABLE table name;

6. Clear the records in the table:

Mysql> DELETEFROM table name;

7. insert records into the table:

Mysql> INSERTINTO table name VALUES ("hyq", "M ");

8. Update table data:

Mysql-> UPDATE table name: SET field name: 1 = 'a'; field name: 2 = 'B' WHERE field name: 3 = 'C ';

9. load data into a data table in text mode:

Mysql> LOADDATALOCALINFILE "D:/mysql.txt" External table name;

10. Import the. SQL FILE command:

Mysql> USE Database Name;

Mysql> SOURCEd:/mysql. SQL;

11. Change the root password on the command line:

Mysql> UPDATEmysql. userSETpassword = PASSWORD ('new password') WHEREUser = 'root ';

Mysql> FLUSHPRIVILEGES;

An instance for creating a database, creating a table, and inserting data

Dropdatabaseifexistsschool; // Delete if sudu exists

Createdatabasesudu; // create a database sudu

Useschool; // open the sudu Library

Createtableteacher // create a table named TEACHER.

(

Idint (3) auto_incrementnotnullprimarykey,

Namechar (10) notnull,

Addressvarchar (50) default 'shenzhen ',

Yeardate

); // Table creation ends

// Insert fields as follows

Insertintoteachervalues ('', 'allen ', 'apsara 1', '2017-10-10 ');

Insertintoteachervalues ('', 'jack', 'apsara technology 2', '2017-12-23 '); if you type the above command at the mysql prompt, it is not convenient to debug it.

(1) You can write the above commands into a text file as they are, for example, sudu. SQL, then copy it to c :\\, and enter the \ mysql \ bin directory in DOS status, and then type the following command:

Mysql-uroot-p password <c: \ sudu. SQL

If it succeeds, no display is displayed for a blank row. If there is an error, a prompt is displayed. (The preceding command has been debugged. You only need to remove the // annotation to use it ).

(2) You can use mysql> sourcec: \ sudu. SQL after entering the command line, or import the sudu. SQL file to the database.

Convert text data to a database

1. Text data should conform to the format: field data is separated by the tab key, and the null value is replaced by \ n. For example:

3rose Apsara technology 11976-10-10

4mike Apsara technology 21975-12-23

Assume that you store these two sets of data as a sudu.txt file and put it under the c-drive root directory.

2. Data Import command loaddatalocalinfile "c: \ sudu.txt" External table name;

Note: You 'd better copy the file to the \ mysql \ bin directory and use the use command to create the database where the table is located.

Back up the database: (the command is executed in the \ mysql \ bin directory of DOS)

1. Export the entire database

The exported files are stored in the mysql \ bin directory by default.

Mysqldump-u username-p Database Name> exported file name

Mysqldump-uuser_name-p123456database_name> outfile_name. SQL

2. Export a table

Mysqldump-u user name-p Database Name> exported file name

Mysqldump-uuser_name-pdatabase_nametable_name> outfile_name. SQL

3. Export a database structure

Mysqldump-uuser_name-p-d -- add-drop-tabledatabase_name> outfile_name. SQL

-D no data -- add-drop-table adds a droptable before each create statement.

4. Export with language Parameters

Mysqldump-uroot-p -- default-character-set = latin1 -- set-charset = gbk-skip-optdatabase_name> outfile_name. SQL