MySQL basic commands

Mysql installation method
There are three MySQL installation methods, one is RPM installation, one is source code compilation installation, and the other is universal binary format package installation.

Installation via RPM, you can directly use the yum command to install or download the RPM package and then install it

yum installation command:
yum -y install mysql-server
The system will automatically solve the problem dependencies and install the mysql client as well.

Command categories in mysql interactive mode:
Client commands: commands executed on the client

Server commands: executed on the server and the results are returned to the client. The statement terminator must be used, and the default is ";"

MySQL database:

information_schema : It is the information located in the memory during the running of mysql. It saves the data when mysql is running. The data is only available when mysql is started. It is usually empty.

test: The database used for testing

mysql: mysql database

MySQL database directory:

[root@host2 ~]# ls /var/lib/mysql/
ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test

MySQL common commands:

DDL: Define database objects:
CREATE:

ALTER:

DROP:

DML : Data Manipulation Language
INSERT

UPDATE

DELETE

DCL Data Control Language
GRANT：

REVOKE：

Create database:
CREATE DATABASE db_name;

CREATE DATABASES IF NOT EXISTS testdb;

mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| test_db            |
+--------------------+
4 rows in set (0.01 sec)

DROP DATABASE db_name;

mysql> DROP DATABASE test_db;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

CREATE TABLE tb_name(col1,col2,...) col1,col2 are field names

Note: The table depends on the database, so before creating the table, be sure to specify the default Database, use the command
USER DATABASE; to specify the database
Create a table named student, containing three fields, Name, Age, Gender

mysql> CREATE TABLE students(Name CHAR(20) NOT NULL, Age TINYINT UNSIGNED,Gender CHAR(1) NOT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| students          |
+-------------------+
1 row in set (0.00 sec)

SHOW TABLES FROM db_name;

View table structure:
DESC table_name;

mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name   | char(20)            | NO   |     | NULL    |       |
| Age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| Gender | char(1)             | NO   |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

DROP TABLE tb_name;

Modify table:
ALTER TABLE tb_name;

MODIFY: Modify a field, modify field attributes, the field name does not change

CHANGE: Change a field, change the field name

ADD ：Add a field

DROP：Delete a field

You can query the help information through the help command:

help CREATE TABLE;

Add a field
For example : Modify the student table created earlier and add a field course,

mysql> ALTER TABLE students ADD course VARCHAR(100);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name   | char(20)            | NO   |     | NULL    |       |
| Age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| Gender | char(1)             | NO   |     | NULL    |       |
| course | varchar(100)        | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE students CHANGE course Course VARCHAR(100);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name   | char(20)            | NO   |     | NULL    |       |
| Age    | tinyint(3) unsigned | YES  |     | NULL    |       |
| Gender | char(1)             | NO   |     | NULL    |       |
| Course | varchar(100)        | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

INSERT  INTO  tb_name (col1,col2,...) VALUES|VALUE ('STRING',NUM...);

For example: Insert two pieces of data into the students table, Zhang San and Li Si

mysql> INSERT INTO students (Name,Gender) VALUE ('ZhangSan','M'),('LiSi','F');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM students;
+----------+------+--------+--------+
| Name     | Age  | Gender | Course |
+----------+------+--------+--------+
| ZhangSan | NULL | M      | NULL   |
| LiSi     | NULL | F      | NULL   |
+----------+------+--------+--------+
2 rows in set (0.01 sec)

Note that all fields have values, there is no need to specify field names

mysql> INSERT INTO students VALUES ('ZengChengpeng',28,'M','IT');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students WHERE Name='ZengChengpeng';
+---------------+------+--------+--------+
| Name          | Age  | Gender | Course |
+---------------+------+--------+--------+
| ZengChengpeng |   28 | M      | IT     |
+---------------+------+--------+--------+
1 row in set (0.00 sec)

UPDATE tb_name SET column=value WHERE condition

For example: Change the name of ZengChengpeng’s Course course to Develop

mysql> UPDATE students SET Course='Develop' WHERE Name='ZengChengpeng';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM students WHERE Name='ZengChengpeng';
+---------------+------+--------+---------+
| Name          | Age  | Gender | Course  |
+---------------+------+--------+---------+
| ZengChengpeng |   28 | M      | Develop |
+---------------+------+--------+---------+
1 row in set (0.00 sec)

selete statement points For two situations,

选择：指定以某字段作为搜索码，做逻辑比较，筛选符合条件的行。WHERE指定选择条件
    
投影：指定以某字段作为搜索码，做逻辑比较，筛选符合条件的字段。
mysql> SELECT Name,Course FROM students WHERE Gender='M';   
+---------------+---------+
| Name          | Course  |
+---------------+---------+
| ZhangSan      | NULL    |
| ZengChengpeng | Develop |
+---------------+---------+

DELECT FROM tb_name WHERE condition;

Create user:
CREATE USER 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD' ;
Delete user:
DROP USER 'USERNAME'@'HOST';


##HOST:

IP:

HOSTNAME:

NETWORK:

Wildcard characters: Wildcard characters are enclosed in quotation marks

_: Underscore matches any single character: For example, 172.16.0._

%: matches Any characters:

jerry@'%'

User permissions:
Add permissions:
GRANT pri1,pri2,... ON DB_NAME.TB_NAME TO 'USERNAME'@' HOST' [IDENTIFIED BY 'PASSWORD'];
pri1 pri2 represents the permission name, and all permissions are represented by ALL PRIVILEGES
Cancel permissions:
REVOKE pri1, pri2,... ON DB_NAME.TB_NAME FROM 'USERNAME' @'HOST';

Create user example:

mysql> CREATE USER 'jerry'@'%' IDENTIFIED BY 'jerry';

mysql> SHOW GRANTS FOR jerry@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for jerry@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jerry'@'%' IDENTIFIED BY PASSWORD '*09FB9E6E2AA0750E9D8A8D22B6AA8D86C85BF3D0' |
+------------------------------------------------------------------------------------------------------+

mysql> GRANT ALL PRIVILEGES ON test_db.* TO 'jerry'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'jerry'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for jerry@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jerry'@'%' IDENTIFIED BY PASSWORD '*09FB9E6E2AA0750E9D8A8D22B6AA8D86C85BF3D0' |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 'jerry'@'%'                                                   |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ON test_db.* FROM jerry@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'jerry'@'%';                      
+------------------------------------------------------------------------------------------------------+
| Grants for jerry@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jerry'@'%' IDENTIFIED BY PASSWORD '*09FB9E6E2AA0750E9D8A8D22B6AA8D86C85BF3D0' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)