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:

##After the mysql installation is completed, there are 3 databases by default

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

You can see that there is no information_schema database. Because it is stored in memory.

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)

Delete database:

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:

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)

View the table in a database:

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)

Delete table:

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)

Modify the field name, for example, change the name of the course field added above to Course

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)

Delete a field: DROP col_name;

Insert data:

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

The above command means: insert data into a table, the field names are col1, col2,... If the inserted value is a string, Enclose it in quotation marks. If it is a numeric value, use numbers directly. If each field is given a value, there is no need to write the field name

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

View data

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

Insert a user, all fields have values:

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)

Modify data:

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:

selete statement points For two situations,

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

Delete data:

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';

View user authorization:

SHOW GRANTS FOR 'USERNAME'@'HOST';

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

Example: Give All permissions for all tables in the database jerry user test_db

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)

以上就是MySQL基本命令的内容，更多相关内容请关注PHP中文网（www.php.cn）！