Published on

Getting Started with MySQL Queries

Authors
  • avatar
    Name
    Loc Truong
    Twitter

When you start working with databases, MySQL is often one of the first sytems you'll encounter. It's open-source, powerful, and widely used across both small projects and large-scale applications.

In this post, we'll walk through some of the most common MySQL commands - from connecting to your server, creating tables, and inserting data, to running queries and managing your database effectively.

Connecting to a MySQL Server

To access a MySQL database, you'll need to connect using your username and password. Here are a few common connection commands:

mysql -u [username] -p
mysql -u [username] -p [database]
mysqldump -u [username] -p [database] > data_backup.sql

Use quit or exit to close your session when you're done. The help command shows all available MySQL commands inside the terminal.

Creating and Displaying Databases

Once connected, you can create and epxlore databases:

CREATE DATABASE zoo;
SHOW DATABASES;
USE zoo;

The CREATE DATABASE command makes a new database. SHOW DATABASES lists all databases, and USE switches you into one so you can start creating tables inside it.

If you want to remove a database, simply use:

DROP DATABASE zoo;

Creating Tables

Tables are where your data lives. Let's say you're building a small zoo management system. You might want one table for animal habitats and another for the animals themselves:

CREATE TABLE HABITAT (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64)
);

CREATE TABLE animal (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64),
  species VARCHAR(64),
  age INT,
  habitat_id INT,
  FOREIGN KEY (habitat_id) REFERENCES habitat(id)
);

Here, we also defined a foreign key to link animals to their habitats - a common relational database practice.

Modifying Tables

Sometimes your data model evolves. MySQL provides flexible ways to change your table structure:

ALTER TABLE animal ADD COLUMN nickname varchar(64);
ALTER TABLE animal RENAME COLUMN id TO identifier;
ALTER TABLE animal MODIFY COLUMN name VARCHAR(128);
ALTER TABLE animal DROP COLUMN nickname;

And when a table is no logner needed:

DROP TABLE animal;

Querying Data

Now for the fun part - querying data. You can use SELECT statements to retrieve and organize information:

SELECT species, AVG(age) as average_age
FROM animal
WHERE id != 3
GROUP BY species
HAVING AVG(age) > 3
ORDER BY AVG(age) DESC;

This query finds the average age of each species (except those with ID 3) and lists only the ones with an average age greater than 3.

To combine data from multiple tables, you can use JOINs:

SELECT city.name, country.name
FROM city
INNER JOIN country
  ON city.country_id = country.id;

Aggregation and Grouping

Aggregation functions like COUNT, AVG, MIN, and MAX help summarize data:

SELECT COUNT(*) from animal;
SELECT COUNT(DISTINCT name) FROM animal;

To group results by a category:

SELECT species, COUNT(id)
FROM animal
GROUP BY species;

Inserting, Updateing, and Deleting Data

You can add data with INSERT, change it with UPDATE, and remove it with DELETE.

INSERT INTO habitat VALUES (1, 'River'), (2, 'Forest');
INSERT INTO habitat (name) VALUES ('Savanna');

UPDATE animal
SET species = 'Duck', name = 'Quack'
WHERE id = 2;

DELETE FROM animal WHERE id = 1;
TRUNCATE TABLE animal;

Conclusion

Mastering MySQL begins with understanding these fundamental commands. Whether you're managing a small project or working in a production environment, these queries form the backbone of database operations.

  • Practice these commands in local environment - repetition is the best way to truly understand SQL.
← Back to the top