MySQL Basic statement

Set Change root password

The default is no password after installing MySQL (mysql5.7 will randomly generate a root password), you need to manually create a password for the root administrator of MySQL
When I first use MySQL, there is no MySQL command, because the path path is not added to the MySQL bash path, we need to use the export path to add a bit, here I wrote to the/etc/profile file, This will automatically load this path every time the boot starts, and the command under this path takes effect
Here you specify all the paths, which contain duplicates, but this will not affect the normal use of the system

[[email protected] ~]# vim /etc/profileexport PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/python3/bin:/root/bin:/usr/local/mysql/bin/

To modify the default no password MySQL, default does not have the password of MySQL, do not need a password to login to its SQL command line, you can specify the user identity update password on the command line, you can also use Mysqladmin to update the password, this method is relatively simple
Write a password directly on the command line to log in to MySQL, there will be a warning, warning users to try not to use the command line to enter a password to log in, because its password is clear text display

[[email protected] ~]# mysqladmin -uroot -password ‘[email protected]‘[[email protected] ~]# mysql -uroot [email protected]mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.6.12 Source distributionCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql>

Modify the MY.CNF to change the password of MySQL, add skip-grand This configuration in the MY.CNF configuration file, and then restart the MySQL service, adding skip-grant in mysql5.7 will cause MySQL to fail to start, and mysql5.7 will output an initial value at initialization. Password, you need to pay attention to this password at the time of installation, or find it in the root directory. Mysql_secret This hidden file, the second line is the default password, if you can use special permissions to apply scripts to modify the default password for MySQL in bulk deployment.

[[email protected] ~]# vim /etc/my.cnf[mysqld]basedir = /usr/local/mysql/datadir = /usr/local/mysql/dataport = 3306character-set-server = utf8explicit_defaults_for_timestamp = true#skip-grant[[email protected] ~]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS!Starting MySQL. SUCCESS!

mysql5.7 to view the default initialized password

[[email protected] ~]# cat .mysql_secret #Password set for user ‘[email protected]‘ at 2018-08-10 18:01:37 aiivsz&#du_.

Change Password in mysql5.7
Log in to MySQL with no password after using the default password, modify the contents of the MySQL table to modify the root administrator password

[[email protected] ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.22 Source distributionCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> SET PASSWORD FOR ‘root‘@localhost = PASSWORD(‘[email protected]‘);Query OK, 0 rows affected, 1 warning (0.00 sec)

Link MySQL
After MySQL password is created to work properly, we will perform operations on MySQL in some remote tools or some local environment (such as the Shell), for which we directly use the MySQL command to telnet to this database and perform operations
mysql Specifies the IP address to link, you can log in without specifying a password, if it is in a program such as script operation, you need the-p password? To log in to MySQL directly,-p is the specified port number

Using the sock file communication method to log in to MySQL, this and the IP difference is not to read the data through the TCP/IP protocol, that is, the data will not be sent to the network card and then read, can improve the reading speed of the data query,-s after the specified sock file directory,-S and path after no space

The command line query MySQL data, generally used in the script, MySQL after logging in, the execution of the statement will have a carriage return interactive action, relying on the scripting language to achieve more trouble, here is the use of MySQL-e to directly output the results of SQL query,-E The subsequent SQL statements need to be quoted using single quotes.

[[email protected] ~]# mysql -uroot [email protected] -e ‘show databases‘mysql: [Warning] Using a password on the command line interface can be insecure.+--------------------+| Database ? ? ? ? ? |+--------------------+| information_schema || mysql ? ? ? ? ? ?  || performance_schema || sys ? ? ? ? ? ? ?  |+--------------------+

MySQL Common statements

Query mysql for all libraries?? show databases;

mysql> show databases;+--------------------+| Database ? ? ? ? ? |+--------------------+| information_schema || mysql ? ? ? ? ? ?  || performance_schema || sys ? ? ? ? ? ? ?  |+--------------------+4 rows in set (0.00 sec)

Enter or switch to a library, here to enter the library named MySQL??? Use MySQL;
and query what tables are in the library?? Show tables;

mysql> use mysql;Database changedmysql> show tables;+---------------------------+| Tables_in_mysql |+---------------------------+| columns_priv || db ? ? ? ? ? || engine_cost  || event ? ? ?  || func ? ? ? ? || general_log  |-----------------省略部分

View the fields in the table? Desc. tb_name (table name);

mysql> desc user;+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Field | Type | Null | Key | Default | Extra |+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Host ? ? ? ? ? ? ? ? | char(60) ? ? ? ? ? ? ? ? | NO | ? ? PRI| ? ? ?  | ? ? ? ? ? ? ? ? ?  || User ? ? ? ? ? ? ? ? | char(32) ? ? ? ? ? ? ? ? | NO | ? ? PRI| ? ? ?  | ? ? ? ? ? ? ? ? ?  || Select_priv ? ? ? ?  | enum(‘N‘,‘Y‘) ? ? ? ? ?  | NO | ? ? ?  | ? ?  N | ? ? ? ? ? ? ? ? ?  || Insert_priv ? ? ? ?  | enum(‘N‘,‘Y‘) ? ? ? ? ?  | NO | ? ? ?  | ? ?  N | ? ? ? ? ? ? ? ? ?  || Update_priv ? ? ? ?  | enum(‘N‘,‘Y‘) ? ? ? ? ?  | NO | ? ? ?  | ? ?  N | ? ? ? ? ? ? ? ? ?  | ------------------省略部分字段

View the statement that built the table show create table tb_name\g;?
You can view the statement rules used when creating a table by show create TABLE, plus \g to make the output more visible, or it will be more output symbols, not easy to understand

mysql> show create table user\G;*************************** 1. row *************************** ? ? ? Table: userCreate Table: CREATE TABLE `user` (`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT ‘‘,-------省略部分-----------------------------------

See who is currently logged on to MySQL? Select User ();
See what is logged in to MySQL, and how to log in (local or IP login), here is the local login mysql, remote login mysql will show the login IP or resolved host name, if you do not want to let MySQL resolve the remote IP, You need to disable the parsing of IP operations in MY.CNF

mysql> select user();+----------------+| user() |+----------------+| [email protected] |+----------------+1 row in set (0.00 sec)

View the currently used database?? Select Database ();
After viewing the login database to perform some entry into the library, see which library you are currently operating under

mysql> select database();+------------+| database() |+------------+| mysql ? ?  |+------------+1 row in set (0.00 sec)

Creating a library Create database Holle;
Create library (database) specifies the name of the library that is created

mysql> create database holle;Query OK, 1 row affected (0.00 sec)

Creating tables? CREATE table TB1 ( id int (4), name char (+)) Engine=innodb DEFAULT Charset=utf8;
The contents of the data table are created here; a table with an ID length of 4 and a character length of 40 that specifies the query engine and character set used by the data table (engine and default CHARSET)

mysql> create table tb1(`id` int(4),`name`char (40))ENGINE=innodb DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.02 sec)mysql> show create table tb1\G;*************************** 1. row *************************** ? ? ? Table: tb1Create Table: CREATE TABLE `tb1` ( ?`id` int(4) DEFAULT NULL, ?`name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

View Database version?mysql> select version ();

mysql> select version();+-----------+| version() |+-----------+| 5.7.22 ?  |+-----------+1 row in set (0.00 sec)

View database status? Show status;
To view commonly used data states

mysql> show status;+-----------------------------------------------+--------------------------------------------------+| Variable_name ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | Value ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?  |+-----------------------------------------------+--------------------------------------------------+| Aborted_clients ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | 0 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?  || Aborted_connects ? ? ? ? ? ? ? ? ? ? ? ? ? ?  | 3 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?  |----------------------------省略部分

view MySQL setup parameters? show Variables\g;? ??

mysql> show variables\G;*************************** 462. row ***************************Variable_name: sql_log_off ? ? ?  Value: OFF*************************** 463. row ***************************Variable_name: sql_mode ? ? ?  Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION*************************** 464. row ***************************Variable_name: sql_notes ? ? ?  Value: ON

View the wildcard of a parameter?? Show variables like ' max_connect% ';
To find the corresponding configuration parameter, you can find the name of the configuration parameter that is not quite confirmed, as shown in:

mysql> show variables like ‘max_connect%‘;+--------------------+-------+| Variable_name | Value ? ?  |+--------------------+-------+| max_connect_errors ? | 100 || max_connections ? ?  | 151 |+--------------------+-------+2 rows in set (0.00 sec)mysql> show variables like ‘slave_parallel%‘;+------------------------+----------+| Variable_name ? ? ? ?  | Value ?  |+------------------------+----------+| slave_parallel_type ?  | DATABASE || slave_parallel_workers | 0 ? ? ?  |+------------------------+----------+2 rows in set (0.00 sec)

Modify the parameters of the MySQL configuration?? Set global max_connect_errors=1000;
Change the value of max_connect_errors from 100 to 1000, but this modification only takes effect in memory, which means that if a shutdown reboot operation occurs, these parameters will change back to the default configuration in the configuration file, and the contents of the MY.CNF should be configured permanently.

mysql> show variables like ‘max_connect%‘;+--------------------+-------+| Variable_name | Value ? ?  |+--------------------+-------+| max_connect_errors ? | 100 || max_connections ? ?  | 151 |+--------------------+-------+2 rows in set (0.00 sec)mysql> set global max_connect_errors=1000;Query OK, 0 rows affected (0.00 sec)mysql> show variables like ‘max_connect%‘;+--------------------+-------+| Variable_name ? ?  | Value |+--------------------+-------+| max_connect_errors | ?1000 || max_connections ?  | ? 151 |+--------------------+-------+2 rows in set (0.00 sec)

View MySQL processing queue? show Processlist;
View all information on MySQL processing queue show full processlist;
These two statements mainly look at the processing of MySQL execution statements, to determine the working status of MySQL

mysql> show processlist;+----+------+-----------+---------+---------+-------+----------+------------------+| Id | User | Host | db | Command | Time ?  | State | Info ? ? | ? ? ? ? ? ? ? ?  |+----+------+-----------+---------+---------+-------+----------+------------------+| 9  | root | localhost | holle ? | Query ? | 0 ? ? | starting | show processlist |+----+------+-----------+---------+---------+-------+----------+------------------+1 row in set (0.00 sec)mysql> show full processlist;+----+------+-----------+-------+---------+------+----------+-----------------------+| Id | User | Host ? ?  | db ?  | Command | Time | State ?  | Info ? ? ? ? ? ? ? ?  |+----+------+-----------+-------+---------+------+----------+-----------------------+| 9  | root | localhost | holle | Query ? | 0 ?  | starting | show full processlist |+----+------+-----------+-------+---------+------+----------+-----------------------+1 row in set (0.00 sec)

MySQL Basic statement