Summary of commonly used commands in mysql

12: Update data in the table
mysql>UPDATE MYTABLE set sex=”f” where name=’hyq’;
The following is the management experience of using MySql that I accidentally saw on the Internet.
MySql exists as a service in Windows , you should make sure that this service has been started before use. 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.
The newly installed MySql contains a root account with an empty password and an anonymous account. This is a huge security risk. For some important applications, we should improve security as much as possible. Here, the anonymous account should be deleted and root To set the password for the account, you can use the following command:
use mysql;
delete from User where User=””;
UPDATE User set Password=PASSWORD('newpassword') where User='root';
If you want to log in to the user Terminal restrictions can be used to update the Host field of the corresponding user in the User table. After making the above changes, the database service should be restarted. At this time, the following similar commands can be used 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.
During 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 other 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”;
If you want to give this user the ability to manage his permissions on the corresponding object, you can Add WITH GRANT OPTION option after GRANT. For users added by inserting into the User table, the Password field should be updated and encrypted using the PASSWORD function 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 in the User table, or by using the REVOKE operation.
The following is an explanation of common permissions that I obtained from other information (www.cn-java.com):
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:
ALTER: Modify existing data tables (such as adding/deleting columns) and indexes.
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 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.
1: Use the SHOW statement to find out what databases currently exist 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; (Press Enter when Database changed appears, the operation is successful!)
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: 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 table
mysql>drop TABLE MYTABLE;
11: Clear the table
mysql>delete from MYTABLE;

The above is a summary of the commonly used commands in mysql. For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!