Questions about MYSQL command line mode management

This article mainly introduces the issues about MYSQL command line mode management. It has certain reference value. Now I share it with everyone. Friends in need can refer to it

MySql database is a back-end database for small and medium-sized websites. The first choice because it is free for non-commercial applications. Website developers can build a "Linux Apache PHP MySql" platform, which is the most cost-effective and efficient platform. When using MySql for development, the documentation that comes with MySql is useful It is a good reference material for novices. This article is my little experience in using MySql.
Currently, the development environment for general users is mostly Windows or Linux. Users can go to http://www.codepub.com/software/index.html to download the relevant version for installation. In Windows, MySql exists as a service. When using You should make sure that this service has been started before. 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. The anonymous account should be deleted here. , To set the password for the root 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 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 similar commands 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 To manage the permissions on the corresponding object, you can add the 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 the records of the table.
Drop: Delete a 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:
ALL: Allowed to do anything (same as root).
USAGE: Only allowed to log in - nothing else is allowed.
Finally, I will give you my MySql operation demonstration under RedHat9.0:
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

其中test.sql是用vi编辑好的SQL脚本，其内容为：
Insert into TestTable(UserName,Address)values('Tom','shanghai');
Insert into TestTable(UserName,Address)values('John','beijing');
select * from TestTable;
运行已经编辑好的SQL脚本可以用source filename 或 .\ filename。
以上只是对新手的简单练习，要成为一个数据库好手，当以孜孜不倦地追求知识，不断地思考、尝试、再思考。

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
MySql常用命令总结
这两天搞个网站，又用到MySql,可是命令却一个都想不起来，所以，趁这次机会，把这些整理一下，权当作笔记吧，以便自己以后查阅！
1:使用SHOW语句找出在服务器上当前存在什么数据库：

 mysql> SHOW DATABASES;

2:2、创建一个数据库MYSQLDATA

  mysql> Create DATABASE MYSQLDATA;

3:选择你所创建的数据库

  mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功！)

4:查看现在的数据库中存在什么表

  mysql> SHOW TABLES;

5:创建一个数据库表

mysql> Create TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

6:显示表的结构：

  mysql> DESCRIBE MYTABLE;

7:往表中加入记录

 mysql> insert into MYTABLE values ("hyq","M");

8:用文本方式将数据装入数据库表中（例如D:/mysql.txt）

 mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE;

9:导入.sql文件命令（例如D:/mysql.sql）

mysql>use database;  
  mysql>source d:/mysql.sql;

10:删除表

 mysql>drop TABLE MYTABLE;

11:清空表

  mysql>delete from MYTABLE;

12:更新表中数据

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

以上就是本文的全部内容，希望对大家的学习有所帮助，更多相关内容请关注PHP中文网！

相关推荐：

mysql 中use mysql denied_MySQL

The above is the detailed content of Questions about MYSQL command line mode management. For more information, please follow other related articles on the PHP Chinese website!