MySql database is the first choice for Small and Medium-sized website back-end databases 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. mySql documentation is a good reference for beginners when using MySql for development. this article is my little experience in using MySql.
Currently, most of your development environments are Windows or Linux. You can run the restart start mysql command to start them. In Linux, the "/etc/rc. d/init. d/mysqld start" command is available. Note that the initiator must have administrator privileges.
The newly installed MySql contains a root account with a blank password and an anonymous account, which poses a major security risk. For some important applications, we should improve the security as much as possible, here, you should delete anonymous accounts and Set passwords for root accounts. You can run 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 logon terminal used by the User, you can update the Host field of the corresponding User in the User table. After making the above changes, restart the database service, at this time, the following commands can be used for Logon:
Mysql-uroot-p;
Mysql-uroot-pnewpassword;
Mysql mydb-uroot-p;
Mysql mydb-uroot-pnewpassword;
The preceding command parameters are part of common parameters. For details, refer to the documentation. Here, mydb is the name of the database you want to log on.
In development and practical applications, users should not only use root users to connect to the database. Although it is convenient to use root users for testing, it will bring significant security risks to the system, it is not conducive to the improvement of management technology. We grant the most appropriate database permissions to the users used in an application. For example, a user who only inserts data should not be granted the permission to delete data. MySql User management is implemented through the User table. There are two common methods to add new users. One is to insert the corresponding data rows in the User table and set the corresponding permissions; the second is to use the GRANT command to create a user with certain permissions. 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 GRANT the user the ability to manage permissions on the corresponding object, you can add the with grant option after GRANT. For users inserted into the User table, use the Password function to update and encrypt the PASSWORD field to prevent unauthorized users from stealing the Password. Users who do not need permissions should be cleared, and those who pass the permissions should be revoked in a timely manner. To REVOKE permissions, you can update the corresponding fields in the User table or use the REVOKE operation.
The following is my interpretation of common permissions from other information (www.cn-java.com:
Global Management permissions:
FILE: read and write files on the MySQL server.
PROCESS: displays or kills service threads of other users.
RELOAD: RELOAD Access Control tables and refresh logs.
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 table records.
Drop: delete a data table or database.
INDEX: Create or delete an INDEX.
Insert: Add Table records.
Select: displays/searches for table records.
Update: Modify existing records in the table.
Special permissions:
ALL: allow anything (same as root ).
USAGE: Only logon is allowed. Other operations are not allowed.
Finally, I will give a MySql operation demonstration under RedHat9.0:
Select Database root User Login
[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 is an SQL script edited by vi. Its content is as follows:
Insert into TestTable (UserName, Address) values ('Tom ', 'shanghai ');
Insert into TestTable (UserName, Address) values ('john', 'beijinging ');
Select * from TestTable;
Run the edited SQL script using source filename or. \ filename.
The above is just a simple exercise for new users. To become a good database player, When you pursue knowledge tirelessly, you will constantly think, try, and think again.
...............................
Summary of common MySql commands
I have used MySql for a website over the past two days, but I cannot think of any command. So I will take this opportunity to sort it out and take the right to take notes, for future reference!
1: Use the SHOW statement to find out the current database on the server:
Mysql> show databases;
2. Create a database named MYSQLDATA
Mysql> Create database mysqldata;
3: select the database you created
Mysql> use mysqldata; (when you press the Enter key to see Database changed, the operation is successful !)
4: view the tables 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 table structure:
Mysql> describe mytable;
7. Add records to the table
Mysql> insert into MYTABLE values ("hyq", "M ");
8: load data into database tables in text mode (for example, D:/mysql.txt)
Mysql> load data local infile "D:/mysql.txt" into table mytable;
9: import the. SQL FILE command (for example, D:/mysql. SQL)
Mysql> use database;
Mysql> source d:/mysql. SQL;
10: delete a table
Mysql> drop table mytable;
11: Clear the table
Mysql> delete from MYTABLE;
12: Update table data
Mysql> update MYTABLE set sex = "f" where name = 'hyq ';
