MySQL Database Basics: A Summary of Basic Commands

1. Use help information
2. Create, delete, and view databases
3. Connect to the database
4. Create users, authorize, and revoke permissions
5. Create and delete tables

This article uses examples to explain the basic commands for getting started with MySQL database. Share with you for your reference, the details are as follows:

In daily work and study, whether it is development, operation and maintenance, or testing, learning about database is inevitable, and it is also one of the essential technologies for daily work. In Internet companies, there are many open source product lines, and MySQL is still the most widely used database in Internet companies.

In the just-released database popularity ranking for March 2019, the top three database products in the first tier all achieved significant additional growth.

Among them, Oracle increased by 15.12 points, MySQL increased by 30.96 points, and SQL Server increased by 7.79 points. Here is the top 20 database list:

Therefore, I, Brother Migong, have organized all the articles on MySQL database pushed by the official account into a collection of advanced learning, and share it with all readers here. Dear readers, if you find this article helpful in your study of MySQL database, please take the time to forward and share it. Let more friends participate in learning, grow together, and run on the road of technology together.

Readers who need to join the technical exchange group organized by Migrant Brother can reply in the background of the official account: "Join the group"

Data is the lifeline of a company. Therefore, most Internet companies are using open source database products. As a result, MySQL has a very high attention and usage rate. Therefore, as a loser in operation and maintenance, it is necessary to master some basic operations of it. So today, let's talk about some commonly used basic commands. Don't miss them if you have passed by them, and don't throw eggs at the migrant workers if you have mastered them or not, hehe...

From this article, I will bring you another series of articles: MySQL database series, which is divided into the following four categories:
1. Basics
2. Intermediate
3. Optimization
4. Enterprise Architecture

1. Use help information

I won’t introduce how to log in to the database. For example, if you want to operate the created database but don’t know the command, you can check the help information.

mysql> help create;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
 CREATE DATABASE #The simplest creation command CREATE EVENT
 CREATE FUNCTION
 CREATE FUNCTION UDF
 CREATE INDEX
 CREATE LOGFILE GROUP
 CREATE PROCEDURE
 CREATE SERVER
 CREATE TABLE
 CREATE TABLESPACE
 CREATE TRIGGER
 CREATE USER
 CREATE VIEW
 SHOW
 SHOW CREATE DATABASE
 SHOW CREATE EVENT
 SHOW CREATE FUNCTION
 SHOW CREATE PROCEDURE
 SHOW CREATE TABLE
 SPATIAL

2. Create, delete, and view databases

mysql> create database test_data;
#Create a database with the default character set (the default is Latin character set)
Query OK, 1 row affected (0.02 sec)
mysql> show databases like "test%";
+------------------+
| Database (test%) |
+------------------+
| test_data |
+------------------+
1 row in set (0.00 sec)

Create a database with the gbk character set

mysql> create database test_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.04 sec)
mysql> show create database test_gbk; 
#View the statement to create a database+----------+----------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test_gbk | CREATE DATABASE `test_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+---------------------------------------------+
1 row in set (0.00 sec)

Deleting a Database

mysql> drop database test_data;
Query OK, 0 rows affected (0.07 sec)
mysql> show databases;
+----------------------------+
| Database |
+----------------------------+
| information_schema |
| test_gbk |
+----------------------------+

3. Connect to the database

mysql> use test_gbk;
#Equivalent to the cd command, switch to the database for operation Database changed
mysql> select database();
#View the currently connected database, equivalent to pwd
+------------+
| database() |
+------------+
| test_gbk |
+------------+
1 row in set (0.00 sec)
mysql> select user();

#View the user currently connected to the database, equivalent to whoami
+--------------------+
| user() |
+-------------------+
| root@localhost |
+--------------------+
1 row in set (0.00 sec)

4. Create users, authorize, and revoke permissions

After the database is created, you need to create users for people who need to connect to the database to use and operate the database. It is impossible for everyone to log in as root, so permission settings are also very important.

mysql> grant all on test_gbk.* to 'testuser'@'localhost' identified by '123456';
#Create a user and give all permissions to all tables in the test_gbk database, password '123456'
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
#Refresh permissions to make them effective Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'testuser'@'localhost';
#Check what permissions the user has+-----------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost |
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `test_gbk`.* TO 'testuser'@'localhost' |
+---------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Revoke permissions

mysql> revoke insert,update,select,delete on test_gbk.* from 'testuser'@'localhost';
# Revoke the above permissions Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'testuser'@'localhost';    
+----------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test_gbk`.* TO 'testuser'@'localhost' |
+------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Note: I didn’t know what all permissions were before, but after using this method, it should be clear

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER

5. Create and delete tables

mysql> create table test(id int(4)not null,name char(20)not null);
#Create a table and create two fields Query OK, 0 rows affected (0.06 sec)
mysql> show tables;#View the table+--------------------+
| Tables_in_test_gbk |
+--------------------+
| test |
+--------------------+
1 row in set (0.00 sec)
mysql> desc test; #View the table structure+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> create table test1(id int(4)not null,name char(20)not null);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------------+
| Tables_in_test_gbk |
+------------------------+
| test |
| test1 |
+--------------------+
2 rows in set (0.00 sec)

Delete a table

mysql> drop tables test;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------------+
| Tables_in_test_gbk |
+--------------------+
| test1 |
+--------------------+
1 row in set (0.00 sec)

View Create Table

mysql> show create table test1\G
*************************** 1. row ***************************
  Table: test1
Create Table: CREATE TABLE `test1` (
 `id` int(4) NOT NULL,
 `name` char(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

That’s all about the basic operation commands, which are all commonly used and necessary commands.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in: