Welcome to this comprehensive guide on SQL commands, designed to serve as your cheat sheet and quick reference. Whether you're a beginner dipping your toes into the world of databases, or an experienced professional needing a refresher, this guide has you covered.
SQL, or Structured Query Language, is a standard language for managing and manipulating databases. It's used to create, retrieve, update, and delete records in a database. Let's dive into the most commonly used SQL commands, complete with examples.
Database Operations
At the core of any database interaction are the commands that create, manage, and delete databases and tables.
Let's start with creating a database. In many SQL systems, you can create a new database using the following command:
Create Database
The syntax for creating a database varies slightly by SQL system. For example, in MySQL, you might use:
CREATE DATABASE myDatabase;
Here, 'myDatabase' is the name you want to give to your database. Once created, you can use the following command in MySQL to select this database for use:
USE myDatabase;
Now, let's create a table within our database. SQL systems typically use the following general syntax:
Create Table
In MySQL, for example, you might create a simple 'users' table like this:
CREATE TABLE users ( id INT AUTO_INCREMENT, firstName VARCHAR(50), lastName VARCHAR(50), email VARCHAR(100), PRIMARY KEY (id) );
Data Manipulation
Now that we have a database and a table, let's look at how to insert, update, retrieve, and delete data.
To insert a new record into a table, you can use the INSERT command. Here's an example in MySQL:
Insert Data
To add a new user to the 'users' table created earlier, you might use:
INSERT INTO users (firstName, lastName, email)
Update Data
To modify an existing record, SQL typically uses the UPDATE command. For instance, to update John Doe's email:
UPDATE users SET email = 'john_new@example.com' WHERE id = 1;
Retrieve Data
To retrieve data from a table, you'll most commonly use the SELECT command. Here are a few examples:
To get all records from the 'users' table:
SELECT * FROM users;
To select specific fields (columns) and filter records:
SELECT firstName, lastName FROM users WHERE id > 10;
Delete Data
To remove a record from a table, SQL uses the DELETE command. For example, to delete John Doe:
DELETE FROM users WHERE id = 1;
Remember, caution should be exercised when using DELETE, as it permanently removes data from your table.
Advanced SQL
SQL offers many more commands and features for more complex operations, such as joining tables, aggregating data, and handling null values.
Let's look at a simple example of joining two tables. Suppose we have another table named 'orders' with a 'user_id' field referring to the 'id' field in our 'users' table:
Join Tables
To retrieve a list of users along with the number of orders they've made, you might use the JOIN command:
SELECT users.firstName, users.lastName, COUNT(orders.id) AS numOrders FROM users LEFT JOIN orders ON users.id = orders.user_id GROUP BY users.id;
Handle Null Values
SQL often deals with null values (missing or unknown data). The IFNULL function in MySQL, for example, lets you replace null values with a specified default value:
SELECT firstName, IFNULL(lastName, 'Unknown') AS last_name FROM users;
This command will replace any null values in the 'lastName' field with 'Unknown'.
That's a wrap on our cheat sheet! You now have a quick reference for the most commonly used SQL commands. With practice, these commands will become second nature, enabling you to efficiently manage and manipulate databases.
Happy coding!