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

Editor to share with you the problem analysis of MYSQL command line mode management, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

MySql database is the first choice for backend databases of small and medium-sized websites because it is free for non-commercial applications. Website developers can build a "Linux+Apache+PHP+MySql" platform, which is the most cost-effective platform. When developing with MySql, the documentation that comes with MySql is a good reference for beginners. This article is a little experience I learned from using MySql.

At present, most of the general users' development environment is Windows or Linux. Users can download the relevant version of http://www.codepub.com/software/index.html to install it. MySql exists as a service in windows. Before using it, you should make sure that this service has been started, and the net start mysql command is not available to start. 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 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@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.

Finally, a demonstration of MySql operation under RedHat9.0 is given.

Select the root user of the database to log in

[weiwen@weiwenlinux] $mysql-uroot-p Enter password:MyPassword mysql > create database mydb; Query OK, 1 row affected (0.02 sec) mysql > use mydb; Database changed mysql > create table TestTable (Id int aut_increment primary key, UserName varchar (16) not null, Address varchar (255)); Query OK, 0 rows affected (0.02 sec) mysql > grant all on mydb.* to test@localhost identified by "test"; Query OK, 0 rows affected (0.01 sec) mysql > quit Bye [weiwen@weiwenlinux] $mysql mydb-utest-ptest

Where test.sql is a SQL script edited with vi, which contains:

Insert into TestTable (UserName,Address) values ('Tom','shanghai')

Insert into TestTable (UserName,Address) values ('John','beijing')

Select * from TestTable

You can run the edited SQL script with source filename or.\ filename.

The above is just a simple exercise for beginners. If you want to be a good database player, you should pursue knowledge tirelessly and keep thinking, trying and rethinking.

. .

Summary of common commands in MySql

These two days to set up a website and use MySql, but I can't remember any of the orders, so take advantage of this opportunity to sort these out and take them as notes so that you can consult them later.

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'; is all the content of the article "problem Analysis of MYSQL Command Line Mode Management". 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.