Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "what commands are commonly used in mysql". In the operation of practical cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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; (if Database changed appears by pressing enter key, 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 (20), sexCHAR (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 database tables in text (for example, D:/mysql.txt)

Mysql > LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLEMYTABLE

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 data in the table

Mysql > update MYTABLE set sex= "f" where name='hyq'

The following is the management experience of using MySql seen inadvertently on the network.

MySql exists as a service in windows. Before using it, make sure that the service has been started and that the net startmysql command is not available to start it. The "/ etc/rc.d/init.d/mysqldstart" command can be used when starting in Linux. Note that the initiator should have administrator privileges.

The newly installed MySql contains a root account with an empty password and an anonymous account, which is a big security risk. For some important applications, we should improve the security as much as possible. Here, delete the anonymous account and set the password for the root account. Use the following command:

Use mysql

Delete from User where User= ""

Update User set Password=PASSWORD ('newpassword') whereUser='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, and restart the database service after making the above changes. When logging in, you can use the following command:

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. Please refer to the documentation for details. Mydb here is the name of the database to be logged in.

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 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 realized through the User table. There are two common ways to add 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 users with certain permissions through the GRANT command. The common uses of GRANT are 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@HostNameidentified by "password"

Grant update,delete on mydb.TestTable to NewUserName@HostNameidentified by "password"

To give this user the administrative ability to give him permissions on the corresponding object, add the WITH GRANTOPTION option after GRANT. For users who are added by inserting the User table, the Password field is updated and encrypted using the PASSWORD function to prevent unscrupulous people from reading the password. Users who are no longer in use should be cleared, and those whose permissions are out of bounds should be reclaimed in time, which can be done by updating the corresponding fields in the User table or by using REVOKE operation.

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

Global administrative permissions:

FILE: read and write files on the MySQL server.

PROCESS: displays or kills service threads that belong to other users.

RELOAD: reload access control tables, refresh logs, etc.

SHUTDOWN: turn off 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: deletes the record of the table.

DROP: delete a data table or database.

INDEX: create or delete an index.

INSERT: add the record of the table.

SELECT: show / search the records of the table.

UPDATE: modifies records that already exist in the table.

Special permissions:

ALL: allow to do anything (like root).

USAGE: only login is allowed-nothing else is allowed.

This is the end of the content of "what are the common commands in mysql"? thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.