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

This article mainly shows you the "what are the commonly used mysql commands in the database", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "what are the commonly used mysql commands in the database" this article.

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), 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 database tables in text (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 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 start mysql command is not available to start it. The "/ etc/rc.d/init.d/mysqld start" 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') 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, 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 the use of root users for testing is very convenient, but it will bring major security risks to the system, and is not conducive to the improvement of management skills. 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@HostName identified by "password"

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

To give this user the administrative ability to give him permissions on the corresponding object, add the WITH GRANT OPTION 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.

The above is all the contents of the article "what are the mysql commands commonly used in the database?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.