MySql - Basic MySQL commands

Basic MySQL Commands

MySQL is one of the most popular relational database management systems (RDBMS) used for storing, managing, and retrieving data efficiently. Understanding the basic MySQL commands is essential for anyone looking to work with databases, whether you are a developer, database administrator, or analyst. This document covers fundamental MySQL commands that help you interact with the database system, including creating databases, tables, inserting data, querying data, updating records, and deleting data.

1. Introduction to MySQL Commands

MySQL commands are used to perform operations on databases and tables within those databases. Commands are written in SQL (Structured Query Language), a standardized language for managing relational databases. MySQL extends standard SQL with additional features.

Commands can be executed in various ways:

• Using the MySQL command-line client
• Using GUI tools like MySQL Workbench
• Embedding SQL commands within programming languages like PHP, Python, Java, etc.

Before diving into commands, you need access to a MySQL server and a client tool.

2. Connecting to MySQL Server

To start working with MySQL, you first connect to the MySQL server using the command-line interface or any MySQL client.

2.1 Using MySQL Command Line

mysql -u username -p

Explanation:

• -u username: specifies the MySQL username
• -p: prompts for the password

Example:

mysql -u root -p

Once you enter the password, you will be inside the MySQL shell where you can execute commands.

3. Managing Databases

3.1 Creating a Database

The CREATE DATABASE command is used to create a new database.

CREATE DATABASE database_name;

Example:

CREATE DATABASE mydatabase;

This command creates a new database named mydatabase.

3.2 Listing Databases

To see all databases on the server, use the SHOW DATABASES; command.

SHOW DATABASES;

This displays a list of available databases.

3.3 Selecting a Database

Before working with tables, you need to select a database using the USE command.

USE database_name;

Example:

USE mydatabase;

3.4 Deleting a Database

If you want to delete a database and all its contents, use the DROP DATABASE command.

DROP DATABASE database_name;

Example:

DROP DATABASE mydatabase;

Warning: This action is irreversible and will delete all data.

4. Managing Tables

4.1 Creating a Table

Tables store data in rows and columns. Use the CREATE TABLE command to create a new table within a database.

Syntax:

CREATE TABLE table_name (
  column1 datatype constraints,
  column2 datatype constraints,
  ...
);

Example:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Explanation:

• id: Integer column with auto-increment and primary key constraint
• username: Variable-length string, maximum 50 characters, cannot be null
• email: Variable-length string, optional
• created_at: Timestamp, defaults to current time when a record is created

4.2 Showing Tables

To view all tables in the current database:

SHOW TABLES;

4.3 Viewing Table Structure

To see the structure of a table (columns, types, keys):

DESCRIBE table_name;

or

SHOW COLUMNS FROM table_name;

4.4 Deleting a Table

Use DROP TABLE to delete a table and all its data:

DROP TABLE table_name;

5. Inserting Data into Tables

5.1 Basic Insert

The INSERT INTO command adds new rows to a table.

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

Example:

INSERT INTO users (username, email)
VALUES ('john_doe', 'john@example.com');

5.2 Inserting Multiple Rows

You can insert multiple rows in a single query:

INSERT INTO users (username, email)
VALUES 
  ('alice', 'alice@example.com'),
  ('bob', 'bob@example.com');

5.3 Inserting Data Without Specifying Columns

If you insert values for all columns in order, you can omit column names:

INSERT INTO users VALUES (NULL, 'charlie', 'charlie@example.com', NOW());

Note: Use NULL for auto-increment or default columns if you want MySQL to fill them automatically.

6. Querying Data (SELECT Command)

6.1 Basic SELECT

The SELECT command is used to retrieve data from one or more tables.

SELECT column1, column2 FROM table_name;

Example:

SELECT username, email FROM users;

6.2 Selecting All Columns

Use * to select all columns:

SELECT * FROM users;

6.3 Filtering Results with WHERE

To retrieve rows matching specific conditions, use WHERE.

SELECT * FROM users WHERE username = 'alice';

6.4 Using Comparison Operators

• = equal
• != or <> not equal
• < less than
• > greater than
• <= less than or equal
• >= greater than or equal

Example:

SELECT * FROM users WHERE id > 10;

6.5 Logical Operators

• AND – both conditions true
• OR – either condition true
• NOT – negates a condition

Example:

SELECT * FROM users WHERE username = 'alice' AND email LIKE '%@example.com';

6.6 Pattern Matching with LIKE

Use LIKE for wildcard pattern matching:

• % matches any number of characters
• _ matches a single character

Example:

SELECT * FROM users WHERE email LIKE '%@gmail.com';

6.7 Sorting Results with ORDER BY

Sort query results by one or more columns.

SELECT * FROM users ORDER BY username ASC;

Use DESC for descending order:

SELECT * FROM users ORDER BY created_at DESC;

6.8 Limiting Results with LIMIT

Restrict the number of rows returned:

SELECT * FROM users LIMIT 5;

6.9 Combining ORDER BY and LIMIT

Get top N rows based on order:

SELECT * FROM users ORDER BY created_at DESC LIMIT 3;

7. Updating Data

7.1 Basic UPDATE Syntax

Use the UPDATE command to modify existing records in a table.

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

Example:

UPDATE users
SET email = 'alice_new@example.com'
WHERE username = 'alice';

7.2 Updating Multiple Columns

UPDATE users
SET email = 'bob_new@example.com', username = 'bobby'
WHERE id = 2;

7.3 Important Notes on UPDATE

• Always use WHERE to avoid updating all rows.
• Omitting WHERE updates every row in the table.

8. Deleting Data

8.1 Basic DELETE Syntax

The DELETE command removes rows from a table.

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM users WHERE username = 'charlie';

8.2 Deleting All Rows

Remove all rows but keep the table:

DELETE FROM users;

Warning: This deletes all records and cannot be undone.

8.3 Truncating a Table

TRUNCATE TABLE quickly removes all rows and resets auto-increment counters.

TRUNCATE TABLE users;

Unlike DELETE, TRUNCATE is faster and uses fewer system resources but cannot be rolled back in most cases.

9. Additional Useful Commands

9.1 Showing Current Database

SELECT DATABASE();

9.2 Showing Server Version

SELECT VERSION();

9.3 Viewing Running Processes

SHOW PROCESSLIST;

9.4 Checking Table Status

SHOW TABLE STATUS LIKE 'users';

9.5 Describing Indexes

SHOW INDEX FROM users;

10. Working with NULL Values

In MySQL, NULL represents an unknown or missing value.

10.1 Inserting NULL Values

INSERT INTO users (username, email) VALUES ('dave', NULL);

10.2 Checking for NULL

Use IS NULL and IS NOT NULL in queries:

SELECT * FROM users WHERE email IS NULL;

10.3 Updating NULL Values

UPDATE users SET email = 'dave@example.com' WHERE email IS NULL;

11. Using Aliases

Aliases provide temporary names for columns or tables to improve readability.

11.1 Column Alias

SELECT username AS user, email AS user_email FROM users;

11.2 Table Alias

SELECT u.username, u.email FROM users AS u;

12. Combining Queries

12.1 UNION Operator

Combine results of two or more SELECT statements:

SELECT username FROM users
UNION
SELECT username FROM archived_users;

Note: The number and type of columns must match.

12.2 Subqueries

A query within another query:

SELECT username FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

13. Comments in MySQL

Comments are used to explain SQL code and ignored during execution.

13.1 Single Line Comments

-- This is a comment
SELECT * FROM users;  -- Select all users

13.2 Multi-line Comments

/*
  This is a multi-line comment
  It spans multiple lines
*/
SELECT * FROM users;

Understanding and mastering basic MySQL commands is essential for effective database management. These commands form the foundation for creating, reading, updating, and deleting data—commonly abbreviated as CRUD operations. With these basics, users can start interacting with databases confidently and build more complex queries and database structures over time.

This guide covered how to connect to MySQL, manage databases and tables, insert and manipulate data, perform queries with filtering and sorting, and use advanced commands such as subqueries and unions. Practice using these commands regularly, and you will gain proficiency in working with MySQL databases.