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
- Open phpMyAdmin and click on the full name of the database, including the account prefix. For example,
account_store. - Go to the SQL tab.
- Paste a single command. It must end with
;. - Replace the example names (
clientes,nombre,telefono) with those of your table. - 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
WHEREclause 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.
