Mysql common command lines

Mysql common command lines

Mysql common command lines

First, start and stop the mysql Service

Net stop mysql

Net start mysql

Second, log on to mysql

Syntax: mysql-u user name-p User Password

Enter the mysql-uroot-p command, press enter and prompt you to enter the password, enter 12345, and then press enter to enter mysql. The mysql prompt is:

Mysql>

Note: If you are connecting to another machine, you need to add a parameter-h Machine IP address.

Third, add new users

Format: grant permission on database. * to username @ login host identified by "password"

For example, you can add a user user1 with the password password1 so that the user can log on to the machine and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to mysql, and then type the following command:

Grant select, insert, update, delete on *. * to user1 @ localhost Identified by "password1 ";

If you want the user to log on to mysql on any machine, change localhost to "% ".

If you do not want user1 to have a password, you can run another command to remove the password.

Grant select, insert, update, delete on mydb. * to user1 @ localhost identified "";

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;

By default, two databases are available: mysql and test. Mysql inventory contains the mysql system and user permission information. We change the password and add users, in fact, this database is actually operated.

2. display the data tables in the database:

Use mysql;

Show tables;

3. display the data table structure:

Describe table name;

4. Create and delete databases:

Create database name;

Drop database name;

5. Create a table:

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:

Mysqldump -- opt test> mysql. test

Export the database test database to the mysql. test file, which is a text file

For example, mysqldump-u root-p123456 -- databases dbname> mysql. dbname

Export the database dbname to the mysql. dbname file.

2. import data:

Mysqlimport-u root-p123456 <mysql. dbname.

No need to explain it.

3. Import text data to the database:

Field data of text data is separated by the tab key.

Use test;

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

1: Use the SHOW statement to find out the current database on the server:

Mysql> show databases;

2. Create a database named MYSQLDATA

Mysql> create database mysqldata;

3: select the database you created

Mysql> use mysqldata; (when you press the Enter key to see Database changed, the operation is successful !)

4: view the tables in the current database

Mysql> show tables;

5. Create a database table

Mysql> create table mytable (name VARCHAR (20), sex CHAR (1 ));

6: display the table structure:

Mysql> describe mytable;

7. Add records to the table

Mysql> insert into MYTABLE values ("hyq", "M ");

8: load data into database tables in text mode (for example, D:/mysql.txt)

Mysql> load data local infile "D:/mysql.txt" into table mytable;

9: import the. SQL FILE command (for example, D:/mysql. SQL)

Mysql> use database;

Mysql> source d:/mysql. SQL;

10: delete a table

Mysql> drop table mytable;

11: Clear the table

Mysql> delete from MYTABLE;

12: Update table data

Mysql> update MYTABLE set sex = "f" where name = 'hyq ';

Posted on happytian read (6) Comments (0) EDIT favorites to 365Key

13. Back up the database

Mysqldump-u root Database Name> xxx. data

14: Example 2: connect to MYSQL on the remote host

Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Enter the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: you do not need to add spaces for u and root. The same applies to others)

3. exit MYSQL command: exit (Press ENTER)

========================================================== ==================================

1: Use the SHOW statement to find out the current database on the server:

Mysql> show databases;

2. Create a database named MYSQLDATA

Mysql> create database mysqldata;

3: select the database you created

Mysql> use mysqldata; (when you press the Enter key to see Database changed, the operation is successful !)

4: view the tables in the current database

Mysql> show tables;

5. Create a database table

Mysql> create table mytable (name VARCHAR (20), sex CHAR (1 ));

6: display the table structure:

Mysql> describe mytable;

7. Add records to the table

Mysql> insert into MYTABLE values ("hyq", "M ");

8: load data into database tables in text mode (for example, D:/mysql.txt)

Mysql> load data local infile "D:/mysql.txt" into table mytable;

9: import the. SQL FILE command (for example, D:/mysql. SQL)

Mysql> use database;

Mysql> source d:/mysql. SQL;

10: delete a table

Mysql> drop table mytable;

11: Clear the table

Mysql> delete from MYTABLE;

12: Update table data

Mysql> update MYTABLE set sex = "f" where name = hyq;

The following are the management experiences of using MySql on the Internet,

From: http://www1.xjtusky.com/article/htmldata/2004_12/3/57/article_1060_1.html

In windows, MySql exists as a service. Before using mysql, make sure that the service has been started and the net start MySql command is not enabled. In Linux, the "/etc/rc. d/init. d/mysqld start" command is available. Note that the initiator must have administrator privileges.

The newly installed MySql contains a root account with a blank password and an anonymous account, which poses a major security risk. For some important applications, we should improve the security as much as possible, here, you should delete anonymous accounts and Set passwords for root accounts. You can run the following command:

Use mysql;

Delete from User where User = "";

Update User set Password = PASSWORD (newpassword) where User = root;

If you want to restrict the logon terminal used by the User, you can update the Host field of the corresponding User in the User table. After making the above changes, restart the database service, at this time, the following commands can be used for Logon:

Mysql-uroot-p;

Mysql-uroot-pnewpassword;

Mysql mydb-uroot-p;

Mysql mydb-uroot-pnewpassword;

The preceding command parameters are part of common parameters. For details, refer to the documentation. Here, mydb is the name of the database you want to log on.

In development and practical applications, users should not only use root users to connect to the database. Although it is convenient to use root users for testing, it will bring significant security risks to the system, it is not conducive to the improvement of management technology. We grant the most appropriate database permissions to the users used in an application. For example, a user who only inserts data should not be granted the permission to delete data. MySql User management is implemented through the User table. There are two common methods to add new users. One is to insert the corresponding data rows in the User table and set the corresponding permissions; the second is to use the GRANT command to create a user with certain permissions. The common usage of GRANT is as follows:

Grant all on mydb. * to NewUserName @ HostName identified by "password ";

Grant usage on *. * to NewUserName @ HostName identified by "password ";

Grant select, insert, update on mydb. * to NewUserName @ HostName identified by "password ";

Grant update, delete on mydb. TestTable to NewUserName @ HostName identified by "password ";

To GRANT the user the ability to manage permissions on the corresponding object, you can add the with grant option after GRANT. For users inserted into the User table, use the Password function to update and encrypt the PASSWORD field to prevent unauthorized users from stealing the Password. Users who do not need permissions should be cleared, and those who pass the permissions should be revoked in a timely manner. To REVOKE permissions, you can update the corresponding fields in the User table or use the REVOKE operation.

Mysql common command lines

The following is my interpretation of common permissions from other information (www.cn-java.com:

Global Management permissions:

FILE: read and write files on the MySQL server.

PROCESS: displays or kills service threads of other users.

RELOAD: RELOAD Access Control tables and refresh logs.

SHUTDOWN: Shut down the MySQL service.

Database/data table/data column permissions:

ALTER: Modify existing data tables (such as adding/deleting columns) and indexes.

CREATE: CREATE a new database or data table.

DELETE: DELETE table records.

DROP: delete a data table or database.

INDEX: Create or delete an INDEX.

INSERT: Add Table records.

SELECT: displays/searches for table records.

UPDATE: Modify existing records in the table.

Special permissions:

ALL: allow anything (same as root ).

USAGE: Only logon is allowed. Other operations are not allowed.

Common MYSQL commands

Start: net start mySql;

Enter: mysql-u root-p/mysql-h localhost-u root-p databaseName;

List databases: show databases;

Select Database: use databaseName;

List tables: show tables;

CREATE a data TABLE: mysql> create table mytable (name VARCHAR (20), sex CHAR (1 ),

-> Birth DATE, birthaddr VARCHAR (20 ));

Show columns from tableName;

Modify the table structure: DESCRIBE mytable;

Create a database: source fileName.txt;

Matching character: wildcard _ can be used to represent any character, and % represents any string;

Add a field: alter table tabelName add column fieldName dateType;

Add multiple fields: alter table tabelName add column fieldName1 dateType, add columns fieldName2 dateType;

Multi-line command input: note that words cannot be broken; when data is inserted or changed, the field strings cannot be expanded into multiple rows; otherwise, the hard press enter will be stored in the data;

Add an Administrator Account: grant all on *. * to user @ localhost identified by "password ";

After each statement is entered, enter the plus sign ';' At the end, or add '\ G;

Query time: select now ();

Query the current user: select user ();

Query the database version: select version ();

Query the currently used database: select database ();

Load data into a database table in text mode

It is troublesome to input data one by one. We can add all records to your database table using text files. CREATE an example file named "“mysql.txt". Each line contains a record. Use a tab to separate the values and give them in the order of the columns listed in the create table statement. For example:

Abccs f 1977-07-07 china mary f 1978-12-12 usa tom m 1970-09-02 usa

Use the following command to LOAD the parent file named mytable.txt to the mytable TABLE: mysql> load data local infile "mytable.txt" into table pet;

Run the following command to check whether the data has been input to the database table: mysql> select * from mytable;

(E129)

1. Delete the students data table in the student_course database:

Rm-f student_course/students .*

2. Back up the database: (back up the database test)

Mysqldump-u root-p test> c: \ test.txt

Backup table: (back up the mytable table under the test database)

Mysqldump-u root-p test mytable> c: \ test.txt

Import the backup data to the database: (import back to the test database)

Mysql-u root-p test

3. Create a temporary table: (create a temporary table zengchao)

Create temporary table zengchao (name varchar (10 ));

4. To create a table, first determine whether the table exists.

Create table if not exists students (......);

5. Copy the table structure from an existing table

Create table table2 select * from table1 where 1 <> 1;

6. Copy a table

Create table table2 select * from table1;

7. Rename the table

Alter table table1 rename as table2;

8. Modify the column type