Basic MySQL queries

MySQL is a widely used relational database management system that allows data to be stored and retrieved efficiently. Below, we present the basic MySQL queries so that you can interact with your database using, as an example, a database with employees.

Database Connection

Before performing any query, you must connect to the database. Make sure you have the access credentials (username, password, database name) before executing this step.

mysql -u username -p password -h host -D database_name

You also have the option to connect using a program, such as HeidiSQL. If you want more information about this program and how to install it, you can follow this manual: Manuale: How to Import an SQL file to my Database in SWPanel with HeidiSQL.

Create a Table

To store data, you must first create a table in the database. A table is a structure that defines the data schema you wish to store.

CREATE TABLE table_name (
  column1 data_type1,
  column2 data_type2,
  ...
);

Example:

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  salary FLOAT
);

Insert Data into the Table

Once the table is created, you can add data using the INSERT INTO statement.

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Example:

INSERT INTO employees (name, age, salary) VALUES ('Juan Pérez', 30, 2500.00);
INSERT INTO employees (name, age, salary) VALUES ('María Gómez', 25, 2200.50);
INSERT INTO employees (name, age, salary) VALUES ('Pedro Ramirez', 35, 2800.75);

Query Data

To retrieve data from the table, you can use the SELECT statement.

SELECT column1, column2, ... FROM table_name;

In case you want to retrieve all the data from the table, you can use the following query.

SELECT * FROM table_name;

Example:

SELECT name, salary FROM employees;

Filter Data with WHERE

If you want to obtain records that meet certain conditions, you can use the WHERE clause.

SELECT column1, column2, ... FROM table_name WHERE condition;

Example:

SELECT * FROM employees WHERE age > 28;

Update Data

To modify existing data in a table, use the UPDATE statement along with the WHERE clause to specify which records to update.

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Example:

UPDATE employees SET salary = 3000.00 WHERE id = 1;

Delete Data

To delete records from a table, use the DELETE statement along with the WHERE clause to specify which records to delete.

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM employees WHERE age < 25;

Limit Results

If you want to obtain only a specific number of records, you can use the LIMIT clause.

SELECT column1, column2, ... FROM table_name LIMIT quantity;

Example:

SELECT * FROM employees LIMIT 2;

Sort Results

To sort the results based on one or more columns, use the ORDER BY clause.

SELECT column1, column2, ... FROM table_name ORDER BY column1 ASC/DESC, column2 ASC/DESC, ...;

Example:

SELECT * FROM employees ORDER BY salary DESC;

Group Results

If you want to perform aggregate operations, such as sums or counts, you can use the GROUP BY clause.

SELECT column1, SUM(column2) as sum_column2 FROM table_name GROUP BY column1;

Example:

SELECT age, COUNT(*) as quantity FROM employees GROUP BY age;

These are some of the basic queries that will allow you to begin interacting with MySQL. As you become more familiar with the SQL language, you will be able to perform more complex queries and make the most of MySQL's potential to manage your data.