Sharing of commonly used commands in MySQL

Experiment in the command prompt.

1: Use the SHOW statement to find out what database currently exists on the server:

mysql> SHOW DATABASES;

2:2. Create a database MYSQLDATA

mysql> CREATE DATABASE MYSQLDATA;

3: Select the database you created

mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功！)

4: Check what tables exist 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 structure of the table:

mysql> DESCRIBE MYTABLE;

##7: Go Add records to the table

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

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

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

9: Import .sql file command (for example, 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 MYTABLE set sex=”f” where name=’hyq’;

2. In Windows, MySql exists as a service. Before using it, make sure that the service has been started. If it is not started, you can use the net start mysql command to start it. When starting in Linux, you can use the "/etc/rc.d/init.d/mysqld start" command. Note that the initiator should have administrator rights.

##3. The newly installed MySql contains A root account with an empty password and an anonymous account are a huge security risk. For some important applications, we should improve security as much as possible. Here we should delete the anonymous account and set a password for the root account. You can use the following command Proceed:

use mysql;
delete from User where User=”";
update User set Password=PASSWORD(’newpassword’) where User=’root’;

4. If you want to perform the login terminal used by the user Restrictions, 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 similar commands when logging in:

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

The above command parameters are part of the commonly used parameters. For details, please refer to the documentation. Mydb here is the name of the database to log in to.

5. In development and practical applications, users should not only use the root user to connect to the database. Although it is convenient to use the root user for testing, It will bring major security risks to the system and is not conducive to the improvement of management technology. We give the most appropriate database permissions to the users used in an application. For example, a user who only inserts data should not be given permission to delete data. MySql user management is implemented through the User table. There are two common methods for adding new users. One is to insert the corresponding data rows in the User table and set the corresponding permissions; the second is to create a user with certain permissions through the GRANT command. user. 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”;

6. For users added by inserting into the User table, the Password field applies The PASSWORD function updates the encryption to prevent unscrupulous people from peeking at the password. Those users who are no longer in use should be cleared, and users whose permissions have exceeded the limit should be promptly reclaimed. Permissions can be reclaimed by updating the corresponding fields of the User table, or by using the REVOKE operation.

Global management permissions:

##FILE: Read and write files on the MySQL server.

PROCESS: Display or kill service threads belonging to other users.

RELOAD: Reload access control lists, refresh logs, etc.

SHUTDOWN: Shut down the MySQL service.

Database/data table/data column permissions:

8.ALTER: Modify an existing data table (such as adding/deleting columns) and index.

CREATE: Create a new database or data table.

DELETE: Delete records from the table.

DROP: Delete a data table or database.

INDEX: Create or delete an index.

INSERT: Add records to the table.

SELECT: Display/search the records of the table.

UPDATE: Modify existing records in the table.

Special permissions:

ALL: Allowed to do anything (same as root).

USAGE: Only allowed to log in – nothing else is allowed.

Note

To give this user the ability to manage his permissions on the corresponding object, you can add WITH GRANT OPTION after GRANT options.

The above is the detailed content of Sharing of commonly used commands in MySQL. For more information, please follow other related articles on the PHP Chinese website!