Summary of mysql common Commands

Common commands for creating and managing mysql databases:

1. Use 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 and Database changed indicates successful operation!)

4. See what tables exist in the current database

mysql > SHOW TABLES;

5, create 1 database table

mysql > CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

6. Display the structure of the table:

mysql > DESCRIBE MYTABLE;

7. Add records to the table

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

8, text the data into the database table (for example, D:/ mysql.txt)

mysql > LOAD DATA LOCAL INFILE "D:/mysql.txt"INTO TABLE MYTABLE;

9, import.sql file command (e.g. D:/ mysql.sql)

mysql > use database;
mysql > source d:/mysql.sql;

10, delete the table

mysql > drop TABLE MYTABLE;

11, Clear the table

mysql > delete from MYTABLE;

12. Update the data in the table

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

Here are some management tips for using MySql that I accidentally read on the Internet.

In windows, MySql exists as a service. Make sure this service is started before using it. If it is not started, it can be started with the net start mysql command. And startup "available in Linux/etc rc. d/init. d/mysqld start" command, pay attention to the starter should have administrator privileges.
The newly installed MySql contains 1 root account with empty password and 1 anonymous account, which is a great security hazard. For 1 important application, we should improve the security as much as possible. The anonymous account should be deleted here, and the password of root account can be set using 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 login terminal used by the user, you can update the Host field of the corresponding user in the User table. After making the above changes, you should restart the database service. At this time, you can use the following command when logging in:

mysql -uroot -p;
mysql -uroot -pnewpassword;
mysql mydb -uroot -p;
mysql mydb -uroot -pnewpassword;

The above command parameters are part 1 of the commonly used parameters. For details, please refer to the documentation. mydb here is the name of the database to log in to.
In the development and practical application, users should not only use root users to connect to the database. Although it is very convenient to use root users for testing, it will bring significant security risks to the system and is not conducive to the improvement of management technology. We give the users in an application the most appropriate database permissions. For example, a insert-only user should not be given permission to delete data. MySql's user management is realized through User table. There are two common ways to add new users. 2 is to create a user with some permissions through the GRANT command. 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 give this user the ability to manage his permissions on the corresponding object, add the WITH GRANT OPTION option after GRANT. For users added with the INSERTED User table, the Password field USES the PASSWORD function to update the encryption to prevent malicious people from stealing the password. Users whose privileges have been exceeded should be cleared. Users whose privileges have been exceeded should reclaim their privileges in a timely manner by updating the corresponding fields of the User table or by using the REVOKE operation.

The following is my explanation of common permissions obtained from other sources (ES173en. cn-ES175en. com) :

Global management authority:

FILE: Read and write files on the MySQL server.
PROCESS: Displays or kills service threads belonging to other users.
RELOAD: Overload access control table, refresh log, etc.
SHUTDOWN: Turn off MySQL service.

Database/table/column permissions:

ALTER:  Modify an existing data table ( Such as increasing / Delete the column ) And index. 
CREATE:  Create a new database or data table. 
DELETE:  Delete the table's records. 
DROP:  Deletes a table or database. 
INDEX:  Create or delete an index. 
INSERT:  Adds records to the table. 
SELECT:  According to / The record of the search table. 
UPDATE:  Modify records that already exist in the table. 

Special Privileges:

ALL:  Everything is allowed ( and root1 sample ) . 
USAGE:  Only login and nothing else is allowed. 

Summary:
In windows, MySql exists as a service. Make sure this service is started before using it. If it is not started, the net start mysql command can be started.
And startup "available in Linux/etc rc. d/init. d/mysqld start" command, pay attention to the starter should have administrator privileges.
By default, MySql contains 1 root account with an empty password and 1 anonymous account. The anonymous account should be deleted and the root account should have a password.