MySQL Basic command Learning notes

This will not involve some knowledge of MySQL database installation, but will summarize yourself and use MySQL's most commonly used MySQL statements as your own learning record and notes. The basic environment for the CentOS 6.5 database is mysql5.6.30.

1. Start and stop the MySQL service

1,/etc/init.d/mysql Restar #重启2,/etc/init.d/mysql stop #停止3,/etc/init.d/mysql start #启动4,/etc/init.d/mysq L Reload #平滑重启5, service MySQL reload #平滑重启6, service MySQL stop #停止7, service MySQL start #启动

2. Join the boot start

Chkconfig MySQL on #加入开机自启动chkconfig--list |grep mysql check settings for boot from start

3. Connect to the database

Mysql-h-p-u root-p-E parameter:-H the port to which the host-P database is to be connected defaults to 3306. You can not write the user name to connect to the database-P  Password can be added directly after the-p parameter, but this method, when others view your history command, will get your password is not too safe, usually connected, enter the password. -E You can enter the MySQL statement but will not enter the client.

4. View basic information

Select User (), version (), database (), now (); # View current user, version, current database, current time and other information mysql> Select User (), version (), database (), now (), +----------------+-----------+------- -----+---------------------+| User () | Version () | Database () | Now () |+----------------+-----------+------------+---------------------+| [Email protected] | 5.6.30 | NULL | 2016-06-16 10:08:01 |+----------------+-----------+------------+---------------------+1 row in Set (0.11 sec)

5. Setting password and setting password for root

MySQL database is the default to root does not set the password, the experiment database RPM package installed, with the initial password, mariadb at the time of initialization prompts us to enter the password.

Cat/root/.mysql_secret # The random password set for the root user at Sun June 22:02:31 (local time): Nr7pkqyh5du 2zjKM This part is the initial password,

mysqladmin-u root password ' ****** ' #设置密码

  Change Password  select  host,user,password  from  mysql.user ;+------------ -----------+------------+-------------------------------------------+| host                   | user        | password                                    |+-----------------------+------------+----------------------------------------- --+| localhost             | root        | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | |  localhost.localdomain | root       | * 47f6bc7f709c7ccfcb1eaf451ffe6d89f1377d84 | |  127.0.0.1             | root        | *47f6bc7f709c7ccfcb1eaf451ffe6d89f1377d84 | |  ::1                    | root       | * 23ae809ddacaf96af0fd78ed04b6a265e05aa257 | |  192.168.1.%           | tomcat      | *6fdd34ee54803b8cc847cc5d7158702bcc21fcf6 | |  %                      | winnerlook | *23ae809ddacaf96af0fd78ed04b6a265e05aa257 |+------- ----------------+------------+-------------------------------------------+   (1) mysqladmin -u username  -p old password  password  "******"   Example:  mysqladmin   -u  winner  password  "123"  -p   [[email  protected] ~]# mysqladmin   -u  winner   password   "123"  -p   Enter password: Warning: Using a password  on the command line interface can be insecure.  (2) log in to the database with set  Password command format: set  password    for   [email protected] = Password (""); note the cryptographic function for example:  set   password for   [email protected] ':: 1 '  =password ("123");           query ok, 0  rows affected  (0.05 sec) mysql> flush   privileges; query ok, 0 rows affected  (0.00 sec) mysql>  set    Password for   [email&nBsp;protected] ' 192.168.1.% '  =password ("123123");     query ok, 0  rows affected  (0.00 sec) mysql> flush   privileges; query ok, 0 rows affected  (0.00 SEC) (3) Use update to directly manipulate the user table after login note: The use of cryptographic functions and constraints, It is possible to change all user passwords without paying attention to the throttling conditions. Change all content and clear text password directly as below. Update   user  set  password= ("123123"); query ok, 6 rows affected  (0.03 sec),rows matched: 6   Changed: 6  warnings: 0mysql> select  host,user,password  from   mysql.user ;+-----------------------+------------+----------+| host                   | user        | password |+-----------------------+------------+----------+|  localhost             | root       |  123123   | |  localhost.localdomain | root       | 123123    | |  127.0.0.1             | root        | 123123   | |  ::1                    | root       | 123123   | |  192.168.1.%           | tomcat      | 123123   | |  %                      | winnerlook | 123123   |+-----------------------+------------+----------+ the correct way to change: update   user  set   Password=password ("123123"); query ok, 6 rows affected  (0.02 sec) Rows  matched: 6  changed: 6  warnings: 0mysql>  select   host,user,password  from  mysql.user ;+-----------------------+------------+--------- ----------------------------------+| host                   | user       |  password                                   |+------ -----------------+------------+-------------------------------------------+| localhost              | root       | * e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | |  localhost.localdomain | root       | * e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | |  127.0.0.1             | root        | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | |  ::1                    | root       | * e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | |  192.168.1.%           | tomcat      | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | |  %                     | winnerlook | * e56a114692fe0de073f9a1dd68a00eeb9703f3f1 |+-----------------------+------------+------------------------- ------------------+6 rows in set  (0.00 sec) Use the WHERE clause   add restrictions mysql>   Update   user  set  password=password ("123")  where user= ' Tomcat ';  Query OK, 1 row affected  (0.05 sec) rows matched: 1   Changed: 1  warnings: 0mysql> select  host,user,password  from   mysql.user ;+-----------------------+------------+------------------------------------------- +| host                   | user       | password                                    |+-----------------------+------------+-------------------------------------------+|  localhost             | root        | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | |  localhost.localdomain | root       | * e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | |  127.0.0.1             | root        | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | |  ::1                    | root       | * e56a114692fe0de073f9a1dd68a00eeb9703f3f1 | |  192.168.1.%           | tomcat      | *23ae809ddacaf96af0fd78ed04b6a265e05aa257 | |  %                      | winnerlook | *e56a114692fe0de073f9a1dd68a00eeb9703f3f1 |+------- ----------------+------------+-------------------------------------------+6 rows in set  ( 0.00 SEC)

6. Refresh Permissions

mysql> flush Privileges; Query OK, 0 rows affected (0.14 sec)

This article is from the "Keep Dreaming" blog, please be sure to keep this source http://dreamlinux.blog.51cto.com/9079323/1837470

MySQL Basic command Learning notes