Summary of basic mysql usage and mysql usage

Summary of basic mysql usage and mysql usage
1. Download and install

Download from the official website:

Http://www.mysql.com/

Note that you need an Oracle account to download it.

2. Start mysql

Run the following command to install mysql:

<MYSQL_HOME>

Set it as an environment variable and set:

<MYSQL_HOME> \ bin

Add the directory to the <PATH> environment variable.

Start the command line and enter:

Mysqld

To start the mysql daemon.

 

3. Enter mysql

Mysql-u root-p
Password: enter the password

By default, the root user does not have a password. You can press enter directly.

4. View Basic Information

View version:

View the databases:

View Current User:

5. Create a database

Create a database:

Create database jeecms;

Delete database:

Drop database jeecms;

Import data:

Source D: \ testdb. SQL

6. Use the database

Use a database, such as the test database:

7. Create a user

View all users in the current database:

Select User, Password, Host from mysql. user;

For example:

You can create a user in the following ways.

1) create user aa @ localhost identified by '20140901 ';

Users created in this way can only log on to the 'localhost', that is, they can only log on to the server and must use a password.

• Local login:

2) create user tt identified by '20160301 ';

Users created in this way can log on to the machine of any ip address. Use the '-H' parameter to specify the Server ip address if you log on to a non-server host, for example:

• Remote login:

Mysql-u tt-p123-h 192.168.16.23

• Local login:

If you log on directly to the server, you do not need to use the password:

Mysql-u tt

In this way, a logon failure is prompted when you use the password:

This is because there are empty users in the system, that is, anonymous accounts. For details, see 8-2 (delete anonymous accounts ).

3) create user bb;

Users created in this way can log on to the machine of any ip address without a password. Of course, the '-H' parameter must be specified during Remote Logon:

• Remote login:

Mysql-u bb-h 192.168.16.23

• Local login:

Mysql-u bb

8. Authorize the user
1) Authorization

Grant the following permissions to all tables in the database jeecms:

Grant all privileges on jeecms. * to bb;

2) Delete anonymous accounts

Then, log on to the user bb and view the database of the user. If no jeecms is found, an error is returned for some operations:

Access denied for user'' @ 'localhost' to database jeecms

The reason is that, despite using a bb user to log on to the system, there is still an account with a blank username, that is, an anonymous account. As a result, although bb is used for logon, it is actually logged on anonymously, you can see the '@ 'localhost' in the error message.

Log on to the mysql database with root:

Use mysql

Delete the anonymous account:

Delete from user where user = '';

Refresh permission:

Flush privileges;

Next login with user bb, you can see the authorized database:

9 view and set database Encoding
1) view database Encoding

Use the status command:

2) data encoding Modification

Under the mysql installation directory <MYSQL_HOME>, back up the original my-default.ini file and rename it to my. ini. Modify the my. ini file and add the following content to it:

[Mysqld]

Character-set-server = utf8

 

[Client]

Default-character-set = utf8

 

[Mysql]

Default-character-set = utf8

Restart the mysql database service and check the encoding again: