Community Blog How to Manage MySQL Database with Commands

How to Manage MySQL Database with Commands

In this article, you will get some information on how to mange MySQL database with commands on a Command Line Interface.

Alibaba Cloud offers highly-available on-demand MySQL, SQL Server and PostgreSQL databases as part of their ApsaraDB for RDS (Relational Database Service), you can manage your Alibaba MySQL databases from a Command Line Interface (CLI) on an Alibaba Cloud instance.

First make sure you have created a whitelist group for the IP address of the computer if you are connecting to ApsaraDB for RDS. Once you are connected to your MySQL database, you can execute SQL (Structured Query Language) commands to manage your MySQL instance including creating; users, databases, tables, columns/fields. You can also perform CRUD (Create Read Update and Delete) operations on any database table.

MySQL Show databases command

You can run the command below to view the available databases on your Alibaba MySQL server;

mysql> show databases;
| Database           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
4 rows in set (0.01 sec)

MySQL Create Database Command

To create a database on your Alibaba MySQL server, use the command below:

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

Remember to replace 'sample2' with the preferred name of your database.

Switching Between Databases in MySQL

You can switch from one database to another one in MySQL via the 'use' command. For instance, to switch to the database that you created above, use the command below:

mysql> use test1
Database changed

MySQL Create Table Command

Once you switch to a database, you can create tables using the syntax below;

create table  (field1 datatype, field2 datatype)

MySQL supports a wide range of data types and you can refer to a complete list here

You should use the correct data type depending on the needs of your application.

For instance, to create a table named students with two fields (student_id and student_name), we can use the syntax below:

mysql> CREATE TABLE students (id INT NOT NULL, student_name VARCHAR (30) NOT NULL);
Query OK, 0 rows affected (0.01 sec)

MySQL Show Tables Command

To view all tables from the selected database, use the syntax below:

mysql> show tables;
| Tables_in_test1 |
| students        |
1 row in set (0.01 sec)

MySQL Describe Table Command

To get the structure of the table that you have created above, you can use the below SQL statement;

mysql> describe students;
| Field        | Type        | Null | Key | Default | Extra |
| id           | int(11)     | NO   |     | NULL    |       |
| student_name | varchar(30) | NO   |     | NULL    |       |
2 rows in set (0.02 sec)

You can manage records and MySQL database users on a MySQL database table via the command line. For details, you can go to this tutorial.

Related Blog Posts

How to Use MySQL Joins on Alibaba Cloud MySQL Database

MySQL is one of the most popular relational database management systems (RDBMS). It is secure, scalable and has high performance with around-the-clock uptime guarantee for many websites and applications.

In this tutorial, we will go over the different MySQL joins that you can implement on your application to craft better, faster, optimized and high performing databases on Alibaba Cloud.

A join occurs when two or more tables are joined in an SQL statement. We are going to discuss three types of MySQL joins in this guide:

  1. Inner join
  2. Left join
  3. Right join

The syntax of the join command is shown below:

from table1
join_type table2
on join_condition 

The join condition should follow after the ON keyword. To better understand how MySQL joins works, we will create a test database and populate it with some sample data. For the sake of simplicity, we are going to use a hypothetical store known as mystore.

How to Work with BLOB in MySQL Database Hosted on Alibaba Cloud

BLOB is an acronym for Binary Large OBjects. Sometimes, due to security and portability concerns, you may feel the need to save binary data on your MySQL database alongside other related facts. For instance, if you are running a students' database, you may store their photos and scanned PDF documents together with their registration details such as names and addresses.

In this guide, we will show you how you can store binary data on your MySQL database either hosted with Alibaba Cloud ApsaraDB or provisioned on an ECS instance.

MySQL supports 4 types of BLOB data type which only differ in the maximum length of data they can store. Here is a summary of the different types:

  1. TINYBLOB: Only supports up to 255 bytes.
  2. BLOB: Can handle up to 65,535 bytes of data.
  3. MEDIUMBLOB: The maximum length supported is 16,777,215 bytes.
  4. LONGBLOB: Stores up to 4,294,967,295 bytes of data.

Related Documentation

Command Line Interface - CLI

Log Service Command Line Interface (CLI) supports almost all operations as web. It also supports integrity check, automatic paging, multiple accounts, and cross-domain replication.

Connect to DLA through MySQL CLI tools

DLA supports Java Database Connectivity (JDBC) that is compatible with MySQL. You can use a MySQL CLI tool to connect to DLA through JDBC.

Related Products

Data Lake Analytics

Data Lake Analytics does not require any ETL tools. This service allows you to use standard SQL syntax and business intelligence (BI) tools to efficiently analyze your data stored in the cloud with extremely low costs.

Cloud native federation analytics across multiple data sources: OSS, PostgreSQL, MySQL (RDS), NoSQL (Table Store), etc.

Log Service

Log Service is a complete real-time data logging service that has been developed by Alibaba Group. Log Service supports collection, consumption, shipping, search, and analysis of logs, and improves the capacity of processing and analyzing large amounts of logs.

Fully supports real-time and offline computing, and seamlessly connects to Alibaba Cloud software, open-source software, and commercial software.

0 0 0
Share on

Alibaba Clouder

2,605 posts | 744 followers

You may also like