MySQL Common Basic command Summary and default account permissions and modifications

has been just plain use of database storage data, also by Windows spoiled, a lot of commands used when not remember, so, replace Linux system! No longer use GUI to manage database! Also want to learn more about MySQL, from the Rights Management start! It was also the birth of this study note.

Modify the default password

MySQL default root password is empty when new installation, very insecure! It is recommended to modify it immediately after installation. The method of modification is attached:

Mysqladmin-u root-p Password

Then press the prompt to enter the new password!

Detailed command:

Mysqladmin-u Root-p[oldpass] Password newpass

Note Oldpass Optional, if the root default password is empty, you do not need to enter, if you need to change the previous password, please note that there is no space between the previous password and-p, otherwise it will be error, in addition password and newpass are separated by a space. This is of course nonsense, for beginners to see!

Basic command login into database

This is simple:

Mysql-u root-p

You will see the following command prompt, indicating that the correct entry

You can enter Help to view assistance information. Remember all the commands added ";" This is a grammar rule.

Command explanation:

If you do not enter password, the Enter password prompt will appear after the carriage return. If the root password is empty, you do not need to enter password, the Enter password prompt and enter.

"You can specify the host name and the database name at logon. This host is the address of the other person when you remotely log in to someone else's database! 】

Attach a link to those who have forgotten the password: the solution to MySQL forgot root password under windows

View Database

The first must be to view the default database, using the command:

mysql> show databases;

The result is:

+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema |+--------------------+

Working with databases

To use a database, such as MySQL, using the use command:

mysql> use MySQL;

The result indicates that the database has changed to run correctly.

Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changed

Library table Basic Operations view tables in the library

Enter the database, then look at the table, or the show command:

Mysql> Show tables;

The results are as follows:

+---------------------------+| Tables_in_mysql           |+---------------------------+| columns_priv              | | db                        | | event                     | | Func                      | | general_ Log               | | help_category             | | Help_keyword              | | help_relation             | | help_topic                | | host                      | | ndb_binlog_ Index          | | plugin                    | | proc                      | | Procs_priv                | | Proxies_priv              | | servers                   | | Slow_log                  | | Tables_priv               | | time_zone                 | | Time_zone_leap_second     | | time_zone_name            | | time_zone_transition      | | | time_zone_transition_type | | user                      |+---------------------------+24 rows in Set (0.00 sec)

Well, we've learned some simple database operations, and then we're going to start creating our own databases, datasheets!

Create a database

Simple command:

Create Database MyData;

The result is:

Query OK, 1 row Affected (0.00 sec)

The prompt has been successful. Next we use the View Table command, show:

Mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | MyData             | | mysql              | | performance_schema |+-------- ------------+5 rows in Set (0.00 sec)

Well, your own database is created successfully, first look at the database is what? The answer must have been nothing! Let's take a look:

mysql> use mydata;database changedmysql> show tables; Empty Set (0.00 sec)

Create a table

So, let's create a simple table (note that we've already used the Mydada database above, and the next table we created in this database, we can only create tables in the database):

Mysql> CREATE TABLE table_name (name VARCHAR (), age int); Query OK, 0 rows affected (0.02 sec) mysql> Show tables;+------------------+| Tables_in_mydata |+------------------+| table_name       |+------------------+1 row in Set (0.00 sec)

View the structure of a table

After success, we look at the structure of the table (using describe):

Mysql> describe table_name;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| Name  | varchar (20) | YES  |     | NULL    |       | | age   | int |     YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+2 rows in Set (0.00 sec)

Inserting data into a table

There is no data in the table, insert data (INSERT INTO): "Description: Here is just a simple show, more detailed command please crossing Web document (3000+ page)"

Mysql> INSERT INTO table_name values ("The_third_wave", 100); Query OK, 1 row affected (0.02 sec) mysql> select * from table_name;+----------------+------+| Name           | Age  |+----------------+------+| The_third_wave |  |+----------------+------+1 row in Set (0.00 sec)

Updating data in a table

mysql> UPDATE table_name set age=188 where name= "The_third_wave"; Query OK, 1 row Affected (0.00 sec) Rows matched:1  changed:1  warnings:0mysql> select * FROM table_name;+----- -----------+------+| Name           | Age  |+----------------+------+| The_third_wave |  

Import data to a table

"Waiting to be added"

Clear Table (delete)

Mysql> DELETE from table_name; Query OK, 1 row Affected (0.00 sec) mysql> SELECT * FROM table_name; Empty Set (0.00 sec)

Delete a database or table (drop)

mysql> DROP TABLE table_name; Query OK, 0 rows Affected (0.00 sec) mysql> Show tables; Empty Set (0.00 sec) mysql> drop database MyData; Query OK, 0 rows Affected (0.00 sec) mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema |+--------------------+ 4 rows in Set (0.00 sec)

Rights Management

The following is the default user group after installation:

mysql> use MySQL; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> Select Host,user,password from user;+-----------------------------+----------------- -+-------------------------------------------+| Host | User | Password |+-----------------------------+------------------+----------------------------- --------------+| localhost | Root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | | xxxxxxxxxxxxxxxxxxxxxxxxxxx |                                           Root | || 127.0.0.1 |                                           Root | || :: 1 |                                           Root | || localhost | Debian-sys-maint | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |+-----------------------------+------------------+-------------------------------------------+5 rows in Set (0.00 sec) 

The root account password for the middle three host is empty. Note: Different versions of MySQL are not the same, some will set the password at the same time, not the same as above. 】

It appears that we only set the password for the root account of host:localhost, we use mysql> Select Current_User (), and view the username under the current login:

Mysql> select Current_User (); +----------------+| Current_User () |+----------------+| [Email protected] |+----------------+1 row in Set (0.00 sec)

Note [email protected]_name This user_name is the user name, HOST_NAME is the address of the client you choose to connect to the server. And you're using mysql-h host_name this host_name is the server address (noted above, note the difference).

In other words: For example you have a MSYQL database in db.csdn.com, then you mysql-u root-h db.csdn.com, which is not related to your own machine, your login account or [email protected]

This may not be a good idea, for example, and practice creating user actions.

Create a new user and specify an IP to establish a connection

The actual operation is to create a remote access user account in the data on my test machine (the reason is: this machine security first, I do not want to set up the next firewall alone, and my test machine does not have a firewall! ) to provide access to the database of my computer (my test machine) from a remote host (the one I use)! "This is a bit of a detour", because the default account is not accessible by non-native login, I use the default root account to connect to my trial opportunity to report the following error:

Mysql-u root-h My test machine IP address-penter password:error 1045 (28000): Access denied for user ' root ' @ ' Here is my native IP address ' (using PASSWO Rd:yes)

So you can understand the above passage?

Next learn the user rights settings, the following reference is: MySQL database management Rights Management---Feihong no Trace

The commands are detailed below:

Instructions for using the GRANT command

Let's take a look at an example, create a super user Feihong that only allows logging on locally, and allow permissions to be assigned to other users, with the password [email protected]

GRANT all privileges on * * to [e-mail protected] ' localhost ' identified by ' [e-mail protected] ' with GRANT OPTION;

Description of the GRANT command:

All privileges is a representation of all permissions, and you can also use permissions mentioned by SELECT, Update, and so on.

on to specify which libraries and tables the permissions are for.

the * in front of * * is used to specify the database name, followed by the * number to specify the table name.

to indicates that the permission is assigned to a user.

[email protected] ' localhost ' means the Feihong user, at the end of the limited host, can be IP, IP segment, domain name, and%,% represent anywhere. Note: There are some versions of this is not included in the local, previously encountered to a user set the% allowed to log anywhere, but not on the local login, this and the version has a relationship with this problem plus a localhost user can be.

identified by specifies the user's login password.

With GRANT option This option means that the user can delegate the permissions they own to others. Note: It is often not specified with the WITH Grant option when creating an operation user that the user cannot use the grant command to create a user or authorize another user.

Note: You can use grant to repeatedly add permissions to the user, permissions overlay, such as when you first add a SELECT permission to the user, and then add an INSERT permission to the user, then the user has both select and insert permissions.

The following are operating on my lab machine !

Mysql> GRANT usage,select On * * to [email protected] ' My native IP address ' identified by ' Pbpass ' with GRANT OPTION; Query OK, 0 rows Affected (0.00 sec)

In my test machine, use the following command to view the user table:

Select Host,user,password from Mysql.user;

Be sure to add the correct after I can log on to the test machine in this machine!

The following on the native operation!

Again, we try to connect to my test machine database remotely, using the Access account we added on the test machine to connect!

<span style= "color: #000000;" >mysql-u public-h My test machine IP address-penter password:welcome to the MySQL monitor.  

successfully landed in my lab database, the next action depends on your permissions, such as the public account I created only access permissions, then this account will not becreated deleted!

Let's check out which users are on my test machine:

mysql> show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Sakila | | Test | | World |+--------------------+7 rows in Set (0.00 sec) mysql> Select Host,user,password from mysql.user;+--- --------------+--------+-------------------------------------------+| Host | User | Password |+-----------------+--------+-------------------------------------------+| localhost | Root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | | 127.0.0.1 | Root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | | :: 1 | Root | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | | Native IP Address | Public | *5067452449a119b7fa902eedf47385af750c6297 |+-----------------+--------+----------------------------------------- --+5 rows in Set (0.00 sec) Mysql> 

If you are also pbpass this password, you will find common ground. So, security is important, database user permissions are important, which reminds us, MySQL such a special table, do not give anyone else to see!

Security-oriented, so, delete which do not need the user, and so on later need to add no later!

Delete User

Note Delete users do not use Delete to delete directly, because the user's permissions are not deleted after using Delete, and the new user with the same name inherits the previous permissions. The correct approach is to use the drop user command to delete users!

Drop user ' public ' @ my native IP address; Query OK, 0 rows Affected (0.00 sec)

Indicates success and can be viewed again.

Update user Password

Other users do not set a password or delete it. How do I set a password?

Method One: Mysqladmin

The beginning already says a method, even if used to command:

Mysqladmin-u Root-p[oldpass] Password newpass

Method Two: Use the table Update command

mysql> UPDATE mysql.user SET password=password (' New password ') WHERE user= ' root ' and host= ' 127.0.0.1 '; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0mysql> FLUSH privileges;

Be aware that you must use the password () function! Or what the consequences of their own thinking.

Method Three: Set password

Note: The update is complete using FLUSH privileges; command Refresh permissions, otherwise it is the original password!

This article by @the_third_wave (blog address: http://blog.csdn.net/zhanh1218) original. There are not covered, will be updated periodically, there are errors please correct me.

If you see this blog post is not incomplete, that is I to prevent the crawler to release the first half of the reason, please see the original author blog.

If this blog post is helpful to you, for a good network environment, do not recommend reprint, Recommended Collection! If you must reprint, please bring the suffix and the address of this document.