[MySQL] common MySQL database operations

Common MySQL database operations

 

1. Start the MySQL database
In the above command window, enter the command: Net start mysql5
In this way, the MySQL service is started.

 

2. (local) log on to the MySQL database
In the preceding command window, run mysql-u root-P.
Enter the password.
The password for the first installation of the MySQL decompress version administrator root is blank, so you can directly press enter to log on to the MySQL database.

If you are not logging on to MySQL for the first time and you still have a network address, you can use the following command to log on to the MySQL server, which may be far away or local. This logon method is called "remote Logon". The command is as follows:
Mysql-H 192.168.3.143-u root-P
Mysql-H 192.168.3.143-u root-pleizhimin

-H indicates the logon IP address,-u indicates the user, and-P specifies the password. If no password is written after-P, the system will prompt you to enter the password, -P can also be used to directly write the password, so that you no longer need to enter the password.

 

3. operate databases and tables
After logging on to the MySQL database, you can run the command: Use Database Name
After specifying the database object for operation, you can operate the tables in the database. The operation method is of course an SQL command.

 

4. change the password of the MySQL database administrator root.
MySQL database has a MySQL database by default, which is a MySQL System database used to save a lot of information such as database users and permissions. To change the password, you must operate the MySQL database user table.

The password of the root user in MySQL is still empty, which is very insecure. Assume that you want to change the password to "leizhimin ".

In the preceding command window, run the following command:
Use MySQL;
Grant all on *. * to root @ '%' identified by 'leizhimin' with grant option;
Commit;

This command adds a root user with all permissions. The password is "leizhimin", which can be accessed locally or through the network. It is emphasized that the root user of the MySQL system can only access the database locally. Its @ character is followed by localhost. For details, you can view the uer table of Mysql Data. In this way, there are two root users, one is the original system, and the other is newly created, for the convenience of management, delete the MySQL Root User and keep the created root user because the user can access MySQL through the network.

Then, delete the user's command:
User MySQL;
Delete from user where user = 'root' and host = 'localhost ';
Commit;

In fact, the above method is the authorization command, which creates a database user while authorizing. MySQL also has a separate way to change the user password. Let's take a look at how to perform this operation.
First, create a user lavasoft with a password of 123456.
Grant all on *. * To lavasoft @ 'localhost' identified by '000000' with grant option;

Next, change the password of this user to leizhimin.
Update user SET Password = PASSWORD ('leizhimin') where user = 'lavasosft 'and host = 'localhost ';
Flush privileges;

Note: it is best to create a MySQL user using Grant. Especially for MySQL dBA, it is important to specify user permissions when creating a user.

This modification method is actually implemented using MySQL functions. I will not introduce it one by one.
Note that MySQL does not allow you to specify an alias for a table during password modification or other operations, but does not have this restriction for the first time.

 

5. Create a database
In fact, in addition to the MySQL database, the MySQL database also has an empty database test for testing.
Create a database testdb and execute a series of SQL statements to view the basic operations of the MySQL database.

Create a database testdb:
Create Database testdb;

Preventive database creation:
Create Database if not testdb

Create a table:
Use testdb;
Create Table Table1 (
Username varchar (12 ),
Password varchar (20 ));

Preventive table creation AAA:
Create Table if not exists AAA (SS varchar (20 ));

View the table structure:
Describe Table1;

Insert data to table 1:
Insert into Table1 (username, password) Values
('Leizhimin', 'lavasoft '),
('Hellokitty ', 'hahhahah ');
Commit;

Query table 1:
Select * From Table1;

Change Data:

Update Table1 SET Password = 'hei' where username = 'hellokitty ';
Commit;

Delete data:
Delete from Table1 where username = 'hellokitty ';
Commit;

Add a column to the table:
Alter table Table1 add column (
Sex varchar (2) Comment 'gender ',
Age date not null comment 'age'
);
Commit;

Create a table Table1 from the query:
Create Table TMP
Select * From Table1;

Delete table 1:
Drop table if exists Table1;
Drop table if exists TMP;

 

6. Back up the database testdb
Mysqldump-H 192.168.3.143-u root-pleizhimin-X -- default-character-set = GBK> C:/testdb. SQL

 

7. Delete the database testdb
Drop database testdb;

 

8. Restore the testdb Database
First, create the testdb database, and then use the following command to perform local recovery:
Mysql-u root-pleizhimin testdb <C:/testdb. SQL

 

9. Delete the MySQL Service
If you are tired of MySQL and need to uninstall it, you only need to do so.

Stop MySQL Service
Net stop mysql5

Delete MySQL Service
SC Delete mysql5