MySQL common commands Summary of MySQL common commands

This article describes some of the commands commonly used in MySQL, including the use of creating and modifying databases, tables in databases, MySQL Rights management commands Grant, REVOKE, and so on.

Common commands for creating and managing MySQL Databases:
1, use the show statement to find out what database currently exists on the server:
Mysql> SHOW DATABASES;

2, create a database Mysqldata
mysql> CREATE DATABASE Mysqldata;

3. Select the database you created
mysql> use Mysqldata; (press ENTER to appear database changed the operation is successful!) )

4. See what tables exist in the current database
Mysql> SHOW TABLES;

5, create a database table
Mysql> CREATE TABLE MYTABLE (name VARCHAR), sex CHAR (1));

6, show the structure of the table:
Mysql> DESCRIBE MYTABLE;

7. Add a record to the table
mysql> INSERT INTO MYTABLE values ("HyQ", "M");

8. Load data into a database table (for example, d:/mysql.txt) in text mode
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 table
Mysql>drop TABLE MYTABLE;

11. Clear the Table
Mysql>delete from MYTABLE;

12. Update the data in the table
Mysql>update MYTABLE set sex= "F" where name= ' HyQ ';
Here are some of the management tips that you can inadvertently see on the web using MySQL,
In Windows, MySQL exists as a service and you should ensure that the service is started before use, and that the available net start MySQL command is not started. While Linux starts with the "/etc/rc.d/init.d/mysqld start" command, note that the initiator should have administrator privileges.
The newly installed MySQL contains a root account with a blank password and an anonymous account, which is a great security risk, for some important applications we should improve security as far as possible, the anonymous account should be deleted, the root account password, the following commands can be used:
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 users, you can update the user's host field in the user table, and you should restart the database service when you make the above changes, and you will be able to log in with a command like this:
Mysql-uroot-p;
Mysql-uroot-pnewpassword;
MySQL mydb-uroot-p;
MySQL Mydb-uroot-pnewpassword;
The above command parameters are part of the common parameters, which can be referenced in detail in the documentation. The mydb here is the name of the database to log in to.
In the development and the actual application, the user should not only use the root user to connect the database, although uses the root user to carry on the test to be convenient, but will bring the system the significant security hidden danger, also is not advantageous to the management technology enhancement. We give the most appropriate database permissions to the users used in an application. A user who only inserts data should not be given permission to delete the data. The user management of MySQL is implemented through the users table, there are two common methods for adding new users, one is to insert the corresponding data row in the user table, set the appropriate permissions, and the other is to create a user with some kind of permission through the grant command. The common usage of grant is as follows:
Grant all on mydb.* to [e-mail protected] identified by "password";
Grant Usage on * * to [e-mail protected] identified by "password";
Grant Select,insert,update on mydb.* to [e-mail protected] identified by "password";
Grant Update,delete on MyDB. TestTable to [e-mail protected] identified by "password";
To give this user the ability to manage the permissions on the object, you can add the WITH GRANT option after Grant. For users added with the Insert User table, the password field applies the password function to update the encryption to prevent the malicious person from stealing the password. For those who have not used the user should be given clearance, the permission of the user should be in a timely manner to reclaim permissions, recycling permissions can be updated by the user table corresponding fields, you can also use the revoke operation.
The following is an explanation of the common permissions I have obtained from other sources (www.cn-java.com):
Global Administrative permissions:
File: Read and write files on the MySQL server.
PROCESS: Displays or kills service threads belonging to other users.
RELOAD: Overloads the Access Control table, refreshes the log, and so on.
SHUTDOWN: Turn off the MySQL service.
Database/data Table/Data column permissions:
Alter: Modifies an existing data table (for example, add/Remove Columns) and index.
Create: Create a new database or data table.
Delete: Deletes the record for the table.
Drop: Deletes a data table or database.
Index: Establish or delete the indexes.
INSERT: Adds a table record.
SELECT: Displays/searches the table's records.
UPDATE: Modifies a record that already exists in the table.
Special permissions:
All: Allow to do anything (as root).
USAGE: Allow login only – nothing else is allowed.
Summary:
In Windows, MySQL exists as a service and you should ensure that the service is started before use, and that the available net start MySQL command is not started.
While Linux starts with the "/etc/rc.d/init.d/mysqld start" command, note that the initiator should have administrator privileges.
MySQL defaults to a root account with a blank password and an anonymous account, which should have the anonymous account removed and the root account password set.

MySQL common commands Summary of MySQL common commands