MySql database is the first choice for the backend database 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 and efficient platform. When using When developing MySql, the documentation that comes with MySql is a good reference for novices. This article is my little experience in using MySql. MySql database is the first choice for the backend database 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 and efficient platform. When using When developing MySql, the documentation that comes with MySql is a good reference for novices. This article is my little experience in using MySql.
At present, the development environment of ordinary users is mostly Windows or Linux. In Windows, MySql exists as a service. You should make sure that the service has been started before using it. 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. Here we should delete the anonymous account and the root account. To set a password, 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.
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 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 second 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";
To give this user the ability to manage his permissions on the corresponding object, 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.
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 records from 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: Allows to do anything (same as root).
USAGE: Only allowed to log in - nothing else allowed.
Finally, I give 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
Among them, test.sql is a SQL script edited with vi, and its content is:
Insert into TestTable(UserName,Address)values('Tom','shanghai');
Insert into TestTable(UserName,Address)values('John','beijing');
select * from TestTable;
To run the edited SQL script, you can use source filename or . filename.
The above are just simple exercises for novices. To become a database expert, you should tirelessly pursue knowledge and constantly think, try, and think again.