Basic commands in MySQL

This article gathers basic MySQL commands to run in the SQL tab of phpMyAdmin. They are useful for viewing tables, querying records, updating them, and modifying columns of an existing database on your hosting.

What is it for?

With these commands you can:

  • View the tables and fields of your database.
  • Search, count, sort, and filter records.
  • Update or delete rows.
  • Add, rename, or drop columns.
  • Create a new table inside the database.

Access: log in to cPanel from the client portal → Log in to cPanel, or at https://yourdomain.com/cpanel. Then go to Databases → phpMyAdmin, click on your database, and open the SQL tab.

How to run a command

  1. Open phpMyAdmin and click on the full name of the database, including the account prefix. For example, account_store.
  2. Go to the SQL tab.
  3. Paste a single command. It must end with ;.
  4. Replace the example names (clientes, nombre, telefono) with those of your table.
  5. Click on Go.

Important: UPDATE, DELETE, DROP, and ALTER apply immediately. If an UPDATE or a DELETE does not have a WHERE clause, it affects all rows. Export a backup copy from the Export tab before modifying the database. Texts go inside single quotes, like 'Bob'.

View tables and fields

Tables of the open database

SHOW TABLES;

Fields of a table

DESCRIBE clientes;

This other way shows the same thing:

SHOW COLUMNS FROM clientes;

SHOW DATABASES; lists only the databases that your user can access. To create or delete a database, use cPanel → Databases → MySQL Databases.

Query records

All rows

SELECT * FROM clientes;

Rows with a specific value

SELECT * FROM clientes WHERE nombre = 'Bob';

Two conditions at once, ordered by phone

SELECT * FROM clientes
WHERE nombre = 'Bob' AND telefono = '3444444'
ORDER BY telefono;

A value different from another

SELECT * FROM clientes WHERE nombre != 'Bob';

Texts that start with Bob

SELECT * FROM clientes WHERE nombre LIKE 'Bob%';

Texts that start with the letter a

SELECT * FROM clientes WHERE nombre REGEXP '^a';

To distinguish uppercase from lowercase, use REGEXP BINARY instead of REGEXP.

Unique values of a column

SELECT DISTINCT nombre FROM clientes;

Chosen columns, from highest to lowest

SELECT nombre, telefono FROM clientes ORDER BY telefono DESC;

ASC orders them from lowest to highest.

Row count

SELECT COUNT(*) FROM clientes;

Sum of a numeric column

SELECT SUM(importe) FROM pedidos;

Data from two related tables

SELECT pedidos.id, clientes.nombre
FROM pedidos
LEFT JOIN clientes ON pedidos.cliente_id = clientes.id;

Modify records

Change a piece of data

UPDATE clientes
SET telefono = '3444444'
WHERE nombre = 'Bob';

Delete rows that meet a condition

DELETE FROM clientes WHERE nombre = 'Bob';

Modify tables

Delete a table and all its content

DROP TABLE clientes;

Drop a column

ALTER TABLE clientes DROP COLUMN telefono;

Add a column

ALTER TABLE clientes ADD COLUMN ciudad VARCHAR(50);

Rename a column

ALTER TABLE clientes CHANGE telefono celular VARCHAR(50);

Change the length of a column. The number is the new maximum number of characters.

ALTER TABLE clientes MODIFY nombre VARCHAR(100);

Prevent repeated values in a column

ALTER TABLE clientes ADD UNIQUE (email);

Remove that constraint. The index name usually matches the column name.

ALTER TABLE clientes DROP INDEX email;

Create a table

CREATE TABLE clientes (
  id INT NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(50),
  email VARCHAR(100),
  telefono VARCHAR(25),
  alta DATE,
  PRIMARY KEY (id)
);

Example with a default value:

CREATE TABLE personas (
  id INT NOT NULL AUTO_INCREMENT,
  nombre VARCHAR(35),
  apellido VARCHAR(50) DEFAULT '',
  PRIMARY KEY (id)
);

Users, privileges, and backups

The database user and their privileges are managed in cPanel → MySQL Databases: create the user, add them to the database, and save the privileges. The database backup is downloaded in phpMyAdmin, Export tab, and reloaded in Import.

Frequent issues

  • Access denied when creating the database, user, or privileges: do it from MySQL Databases in cPanel. These changes are not applied with server administrator commands.
  • Table doesn't exist: check the name in SHOW TABLES;. You must have clicked on the correct database before opening SQL.
  • The query returns no rows: the value must match the saved one, including single quotes. LIKE 'Bob%' searches for those starting that way; = 'Bob' searches for that exact value.
  • More rows were modified than expected: the WHERE clause was missing. Re-upload the exported backup.
  • #1142 or command not allowed: your MySQL user does not have that permission. For a site database, the commands in this guide are sufficient.

If a command returns an error, you don't see the table, or a change affected the site, open a ticket from the client portal or consult via the site's online chat.

  • comandos basicos para ejecutar en mysql, phpmyadmin, panel cpanel
  • 37 Users Found This Useful
Was this answer helpful?

Related Articles

How to access a MySQL database remotely (home or work)

This article explains how to enable remote access to MySQL to connect from your computer, or...

MySQL databases

A MySQL database stores information for applications like WordPress, online stores, or forums. In...

Using Perl to access your MySQL database

This document contains information for connecting a Perl script with a hosting MySQL database...

How to create a MySQL database

This article explains how to create a MySQL database in cPanel, how to import a .sql file, and...

How to manage MySQL databases

This article explains how to manage a MySQL database with phpMyAdmin from cPanel: searching data,...