• notice
  • Congratulations on the launch of the Sought Tech site

Mysql common command Daquan mysql common command summary

Common commands for creating and managing mysql database:

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 created database

mysql> USE MYSQLDATA; (When you press the Enter key and 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 the 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;

12, update the 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 in the form of a service in windows.Before using it, make sure that this service has been started.If it is not started, you can start it with the net start mysql command.In Linux, the "/etc/rc.d/init.d/mysqld start" command can be used when starting.Note that the starter should have administrator privileges.
The newly installed MySql contains a root account with a blank password and an anonymous account.This is a big security risk.For some important applications, we should improve the security as much as possible.Here, the anonymous account should be deleted., Root account to set the password, you can use the following command:

The code is as follows:

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 commands when logging in:

The code is as follows:

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

The above command parameters are part of the common parameters.For details, please refer to the document.Here mydb is the name of the database to be logged in.
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 the right to delete data.The user management of MySql is implemented through the User table.There are two common methods for adding new users.One is to insert the corresponding data row in the User table and set the corresponding permissions at the same time; the other is to create a certain permission through the GRANT command user.The common usage of GRANT is as follows:

The code 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 permissions on the corresponding object, you can add the WITH GRANT OPTION option after GRANT.For users added by inserting the User table, the Password field should be updated and encrypted with the PASSWORD function to prevent unscrupulous people from stealing the password.Those users who are no longer in use should be cleared, and users with over-permissions should reclaim their permissions in time.The reclaiming permissions can be done by updating the corresponding fields in the User table or using the REVOKE operation.

The following is an explanation of the common permissions that I obtained from other materials (www.cn-java.com):

Global management authority:

FILE: Read and write files on the MySQL server.
PROCESS: Display or kill service threads belonging to other users.
RELOAD: Reload the access control list, refresh the log, etc.
SHUTDOWN: Shut down the MySQL service.

Database/data table/data column permissions:

The code is as follows:

ALTER: Modify the existing data table (for example, add/Delete column) and index.
CREATE: Create a new database or data table.
DELETE: Delete the record of the table.
DROP: Delete the 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:

The code is as follows:

ALL: Allow to do anything (same as root).
USAGE: Only allow login

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+